Adding new reports in Close Cash report

New report sections can be added in close cash report
Note: New reports in close cash report feature is available in Saleculator 3.8 u2 onwards only.

Using SQL statement, new reports can be added to close cash report. This example add 2 reports in close cash report. One for current stock and another for a list of top sold 10 items for the current sequence.
Note that each SQL statement has to be separated by a semicolon(;).
If you want to generate a report for current sequence, use a question mark(?) on an appropriate database field.
Result of each SQL statement is accessed by the method $payments.getSQLResult().get(x), giving its sequential number as x as given in SQL.CloseCash. Value of each field is accessed by the method ${line.printValue(x)}, giving its sequential number as x as given in SQL statment.

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

SELECT P.NAME, SUM(S.UNITS) FROM STOCKCURRENT S JOIN PRODUCTS P ON S.PRODUCT=P.ID GROUP BY S.PRODUCT ORDER BY NAME;

SELECT P.NAME, SUM(SD.UNITS) AS UNITS, FORMAT(SUM(SD.UNITS*PRICE),2) AS TOTAL FROM STOCKDIARY SD 
JOIN CLOSEDCASH ON CLOSEDCASH.MONEY = ? 
JOIN PRODUCTS P ON SD.PRODUCT=P.ID 
WHERE SD.REASON="-1" AND SD.DATENEW >=CLOSEDCASH.DATESTART AND IF(CLOSEDCASH.DATEEND IS NULL, 1, SD.DATENEW <=CLOSEDCASH.DATEEND) 
GROUP BY SD.PRODUCT ORDER BY UNITS, TOTAL LIMIT 10;

2. Add the below 2 sections in Printer.PartialCash/Printer.CloseCash/Printer.CloseCashMail

<line></line>
        <line>
            <text align ="center" length="48">CURRENT STOCK</text>
        </line>
        <line>
            <text align ="left" length="32">Name</text>
            <text align ="right" length="16">Units</text>
        </line>
        <line>
            <text>------------------------------------------------</text>
        </line>
        #if($payments.getSQLResult().get(0))
        #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
        #end
        <line>
            <text>------------------------------------------------</text>
        </line>
<line></line>
        <line>
            <text align ="center" length="48">TOP 10 PRODUCTS</text>
        </line>
        <line>
            <text align ="left" length="25">Name</text>
            <text align ="right" length="8">Units</text>
            <text align ="right" length="15">Total</text>
        </line>
        <line>
            <text>------------------------------------------------</text>
        </line>
        #if($payments.getSQLResult().get(1))
        #foreach ($line in $payments.getSQLResult().get(1))
        <line>
            <text align ="left" length="25">${line.printValue(0)}</text>
            <text align ="right" length="8">${line.printValue(1)}</text>
            <text align ="right" length="15">${line.printValue(2)}</text>
        </line>
        #end
        #end
        <line>
            <text>------------------------------------------------</text>
        </line>

Save & restart

0 Comments

Leave a Reply

Arrow-up