Print accounts report on every 1st of month

This report print accounts report for previous month when closing cash on every 1st of month1.

Administrator Menu > Maintenance > Resources
1. Create a resource SQL.CloseCash and add the below SQL statements:

SELECT A.NAME, FORMAT(SUM(P.TOTAL) ,2)
FROM PAYMENTS P JOIN RECEIPTS R JOIN ACCOUNTHEADS A ON A.ID=R.ACCOUNTHEAD AND R.ID=P.RECEIPT 
WHERE DATE_FORMAT(CURDATE(), "%d")=1 AND
R.DATENEW>=DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), "%Y-%m-01 00:00:00") AND
R.DATENEW<=DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), "%Y-%m-%d 23:59:59")
GROUP BY P.PAYMENT;

2. Add below section in Printer.PartialCash/Printer.CloseCash/Printer.CloseCashMail

<line></line>
        <line>
            <text align ="center" length="48">ACCOUNTS REPORT OF PREVIOUS MONTH</text>
        </line>
        <line>
            <text align ="left" length="32">Head</text>
            <text align ="right" length="16">Amount</text>
        </line>
        <line>
            <text>------------------------------------------------</text>
        </line>
        #foreach ($line in $payments.getSQLResult().get(0))
        <line>
            <text align ="left" length="32">${line.printValue(0)}</text>
            <text align ="right" length="16">${line.printValue(1)}</text>
        </line>
        #end
        <line>
            <text>------------------------------------------------</text>
        </line>

Save & restart

0 Comments

Leave a Reply

Arrow-up