Custom Report For invoice Paid

Support Developer
1

Hi,

I want to create a report that give me information about invoices paid in certain time.

I have the follow code

class IncomeReport(ModelSQL, ModelView):
    "Income Report"
    __name__ = 'income.reporting.main'

    date = fields.Date('Date')
    amount = fields.Numeric('Total Amount')

    @staticmethod
    def table_query():
        pool = Pool()

        Invoice = pool.get('account.invoice')
        InvoiceLine = pool.get('account.invoice.line')

        invoice = Invoice.__table__()
        invoice_line = InvoiceLine.__table__()

        context = Transaction().context
        company = context.get('company')
        start_date = context.get('from_date')
        end_date = context.get('to_date')
        period = context.get('period')

        period_map = {
            'year': DateTrunc('year', invoice.invoice_date),
            'month': DateTrunc('month', invoice.invoice_date),
            'day': DateTrunc('day', invoice.invoice_date),
        }
        date_field = period_map.get(period, period_map['day'])

        wheredate = invoice.state == 'paid'
        wheredate &= invoice.company == company

        if start_date:
            wheredate &= invoice.invoice_date >= start_date
        if end_date:
            wheredate &= invoice.invoice_date <= end_date

        query = invoice.join(invoice_line,
            condition=invoice.id == invoice_line.invoice).select(
            Max(invoice.id * 1000).as_('id'),
            Max(invoice.create_uid).as_('create_uid'),
            Max(invoice.create_date).as_('create_date'),
            Max(invoice.write_uid).as_('write_uid'),
            Max(invoice.write_date).as_('write_date'),
            date_field.as_('date'),
            Sum(invoice_line.unit_price * invoice_line.quantity).as_('amount'),
            where=wheredate,
            group_by=[invoice.id, date_field]
        )

        return query

But it doesn’t display the correct info. Any suggest?

Best.

2

What are you expected and what do you get?

3

I want a report that tells me the amount of invoices paid in a given period of time, and that I can group them by day, month, and year.

4

Why do not you use the “Journals Cash” reports?

5

I don’t know how to use them, usually the report with balance 0.

6

I do not understand your comment.

Anyway I’m wondering now if your requirements are not indeed just the Account Tax Cash Module.

7

I’m not sure how to use Cash Reports

image
image1390×167 8.69 KB

I have account moves in that date range, but the credits and debits appear as zero.

8

The account_tax_cash module can only work after its activation.

  • Did you do those moves before the activation of the module?
  • Did you configure the necessary tax groups on the fiscal year?
  • Did you configure the necessary tax groups on the accounting period (in case the periods were created before the activation of the module)?
9

Are they using this journal?

10

I haven’t installed the module yet, I don’t think it’s necessary for what I’m working on.

11

Yes, I’ve already reviewed the account moves and they’re journaled. I’ve also already reviewed the jounral account and they’re cash equivalents.

I think it’s a configuration issue, but I’m not sure what I should check, since I’ve already reviewed the journal settings.

12

The accounts tracked must of course not be payable nor receivable.

13

I’ve already checked the settings, and the accounts I use aren’t payable or receivable, but the report shows zero.
What else could I check?

14

After debugging and testing, I found that the types for payable and receivable are not recognized if they are null and were excluded

I change this


where = ((move.date >= context.get('start_date'))
            & (move.date <= context.get('end_date'))
            & ~account_type.receivable
            & ~account_type.payable
            & (move.company == company.id))

For this one

where = (
            (move.date >= context.get('start_date')) &
            (move.date <= context.get('end_date')) &
            ((account_type.receivable == False) | (account_type.receivable == None)) &
            ((account_type.payable == False) | (account_type.payable == None)) &
            (move.company == company.id)
        )

After this the Journals Cash report works fine.

15

I filled Journal debit/credit balance does not work when receivable or payable account type are NULL (#13967) · Issues · Tryton / Tryton · GitLab