Printing reports in thermal receipt printer

Here is how to add custom reports in Saleculator which can be printed in thermal receipt printer. This feature is available only from version 4.0 u2 onwards.


Procedure #

Go to Administration Menu > Maintenance > Resources

1. Add the below line in Menu.Root:

group.addPanel("/com/posics/images/posreports.png", "Menu.POSReports", "com.posics.pos.reports.JPanelPOSReports");

2. Add below line in Roles:

<class name="com.posics.pos.reports.JPanelPOSReports"/>

3. Create a new text resource with the name POS.Reports and copy-paste below lines:

<?xml version="1.0" encoding="UTF-8"?>
<!--
        Posics Saleculator - Billing System.
        Copyright (C) 2009 Posics
        This file is part of Posics Saleculator.
 -->
<configuration>
<button key="button.print" titlekey="Sales Log" template="POS.Report.SalesLog"/>
<button key="button.print" titlekey="Product Sales" template="POS.Report.ProductSales"/>
<button key="button.print" titlekey="Current Inventory" template="POS.Report.CurrentInventory"/>
<button key="button.print" titlekey="Cash Closed" template="POS.Report.CashClosed"/>
<button key="button.print" titlekey="Payments" template="POS.Report.Payments"/>
<button key="button.print" titlekey="Payments" template="POS.Report.ProfitandLoss"/>
<button key="button.print" titlekey="Payments" template="POS.Report.Tax"/>
</configuration>

Save

Sales Log Report: #

Create a new text resource with the name POS.Report.SalesLog and copy-paste below lines:

<?xml version="1.0" encoding="UTF-8"?>
<!--
       Posics Saleculator - Billing System.
       Copyright (C) 2009 Posics
       This file is part of Posics Saleculator.
-->
<output>
    <sql><![CDATA[ 
    
    SELECT TICKETS.TICKETID, RECEIPTS.DATENEW, 
    SUM((TICKETLINES.PRICE + TICKETLINES.PRICE * TAXES.RATE) * TICKETLINES.UNITS), 
    SUM((TICKETLINES.PRICE * TAXES.RATE) * TICKETLINES.UNITS)
    FROM RECEIPTS
    JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID AND (TICKETS.TICKETTYPE=0 OR TICKETS.TICKETTYPE=1)
    JOIN TICKETLINES ON TICKETLINES.TICKET = RECEIPTS.ID
    JOIN TAXES ON TICKETLINES.TAXID = TAXES.ID 
    WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate
    GROUP BY RECEIPTS.ID 
    ORDER BY RECEIPTS.DATENEW
    
    ]]></sql>
    <ticket>
        <image>Printer.Ticket.Logo</image>
        <line></line>
        <line size="1">
            <text align ="center" length="48" bold="true">Sales Log</text>
        </line>
        <line></line>
        <line>
            <text length="12">Start Date:</text>
            <text length="36">$startDate</text>
        </line>
        <line>
            <text length="12">End Date:</text>
            <text length="36">$endDate</text>
        </line>
        <line>
        </line>
        <line>
            <text align ="left" length="5">#</text>
            <text align ="left" length="20">Date</text>
            <text align ="right" length="13">Total</text>
            <text align ="right" length="10">Tax</text>
        </line>
        <line>
            <text>------------------------------------------------</text>
        </line>
        #foreach ($line in $posreport.getResult())
        <line>
            <text align ="left" length="5">${line.printValue(0)}</text>
            <text align ="left" length="20">${line.printDate(1)}</text>
            <text align ="right" length="13">${line.printCurrency(2)}</text>
            <text align ="right" length="10">${line.printCurrency(3)}</text>
        </line>
        #end
        <line>
            <text>------------------------------------------------</text>
        </line>
        <line>
            <text align ="left" length="5">Total</text>
            <text align ="left" length="20"></text>
            <text align ="right" bold="true" length="13">$posreport.printTotal(2)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(3)</text>
        </line>
        </ticket>
</output>

Save

Payments Report: #

Create a new text resource with the name POS.Report.Payments and copy-paste below lines:

<?xml version="1.0" encoding="UTF-8"?>
<!--
       Posics Saleculator - Billing System.
       Copyright (C) 2009 Posics
       This file is part of Posics Saleculator.
-->
<output>
    <sql><![CDATA[ 
    
    SELECT RECEIPTS.DATENEW, TICKETS.TICKETID, PAYMENTS.PAYMENT, PAYMENTS.TOTAL, ACCOUNTHEADS.NAME, PAYMENTS.NOTES
    FROM PAYMENTS
    JOIN RECEIPTS ON PAYMENTS.RECEIPT = RECEIPTS.ID
    LEFT JOIN TICKETS ON TICKETS.ID=RECEIPTS.ID
    JOIN ACCOUNTHEADS ON ACCOUNTHEADS.ID = RECEIPTS.ACCOUNTHEAD
    WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate 
    ORDER BY RECEIPTS.DATENEW
        
    ]]></sql>
    <ticket>
        <image>Printer.Ticket.Logo</image>
        <line></line>
        <line size="1">
            <text align ="center" length="48" bold="true">Payments</text>
        </line>
        <line></line>
        <line>
            <text length="12">Start Date:</text>
            <text length="36">$startDate</text>
        </line>
        <line>
            <text length="12">End Date:</text>
            <text length="36">$endDate</text>
        </line>
        <line>
        </line>
        <line>
            <text align ="left" length="20">Date</text>
            <text align ="left" length="10">Receipt</text>
            <text align ="left" length="8">Type</text>
            <text align ="right" length="10">Total</text>
        </line>
        <line>
            <text>------------------------------------------------</text>
        </line>
        #foreach ($line in $posreport.getResult())
        <line>
            <text align ="left" length="20">${line.printDate(0)}</text>
            <text align ="left" length="10">${line.printValue(1)}</text>
            <text align ="left" length="8">${line.printValue(2)}</text>
            <text align ="right" length="10">${line.printCurrency(3)}</text>
        </line>
        #if(!$line.printValue(5).equals(""))
        <line>
            <text>${line.printValue(4)} - ${line.printValue(5)}</text>
        </line>
        #end
        #end
        <line>
            <text>------------------------------------------------</text>
        </line>
        <line>
            <text align ="left" length="20">Total</text>
            <text align ="left" length="10"></text>
            <text align ="left" length="8"></text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(3)</text>
        </line>
        </ticket>
</output>

Save

Cash Closed Report #

Create a new text resource with the name POS.Report.CashClosed and copy-paste below lines:

<?xml version="1.0" encoding="UTF-8"?>
<!--
       Posics Saleculator - Billing System.
       Copyright (C) 2009 Posics
       This file is part of Posics Saleculator.
-->
<output>
    <sql><![CDATA[ 
    
    SELECT CLOSEDCASH.MONEY AS ID, CLOSEDCASH.HOST, CLOSEDCASH.HOSTSEQUENCE, CLOSEDCASH.DATESTART, CLOSEDCASH.DATEEND, SUM(PAYMENTS.TOTAL) AS TOTAL 
    FROM CLOSEDCASH LEFT JOIN RECEIPTS ON RECEIPTS.MONEY = CLOSEDCASH.MONEY LEFT JOIN PAYMENTS ON PAYMENTS.RECEIPT = RECEIPTS.ID 
    WHERE CLOSEDCASH.DATEEND IS NOT NULL AND CLOSEDCASH.DATESTART >= $startDate AND CLOSEDCASH.DATESTART <= $endDate 
    GROUP BY CLOSEDCASH.MONEY ORDER BY CLOSEDCASH.HOSTSEQUENCE DESC
    
    ]]></sql>
    <ticket>
        <image>Printer.Ticket.Logo</image>
        <line></line>
        <line size="1">
            <text align ="center" length="48" bold="true">Cash Closed</text>
        </line>
        <line></line>
        <line>
            <text length="12">Start Date:</text>
            <text length="36">$startDate</text>
        </line>
        <line>
            <text length="12">End Date:</text>
            <text length="36">$endDate</text>
        </line>
        <line>
        </line>
        <line>
            <text align ="left" length="15">Host</text>
            <text align ="left" length="10">Sequence</text>
            <text align ="right" length="23">Total</text>
        </line>
        <line>
            <text>------------------------------------------------</text>
        </line>
        #foreach ($line in $posreport.getResult())
        <line>
            <text align ="left" length="15">${line.printValue(1)}</text>
            <text align ="left" length="10">${line.printValue(2)}</text>
            <text align ="right" length="23">${line.printCurrency(5)}</text>
        </line>
        <line>
            <text>${line.printDate(3)}  TO  ${line.printDate(4)}</text>
        </line>
        #end
        <line>
            <text>------------------------------------------------</text>
        </line>
        <line>
            <text align ="left" length="15">Total</text>
            <text align ="left" length="10"></text>
            <text align ="right" bold="true" length="23">$posreport.printTotal(5)</text>
        </line>
        </ticket>
</output>

Save

Current Inventory Report: #

Create a new text resource with the name POS.Report.CurrentInventory and copy-paste below lines:

<?xml version="1.0" encoding="UTF-8"?>
<!--
       Posics Saleculator - Billing System.
       Copyright (C) 2009 Posics
       This file is part of Posics Saleculator.
-->
<output>
    <sql><![CDATA[ 
    
    SELECT PRODUCTS.NAME, PRODUCTS.CODE, SUM(STOCKCURRENT.UNITS) AS UNITS, (PRODUCTS.PRICEBUY*SUM(STOCKCURRENT.UNITS)) AS COST 
    FROM STOCKCURRENT JOIN PRODUCTS ON STOCKCURRENT.PRODUCT = PRODUCTS.ID GROUP BY PRODUCTS.ID ORDER BY PRODUCTS.NAME
    
    ]]></sql>
    <ticket>
        <image>Printer.Ticket.Logo</image>
        <line></line>
        <line size="1">
            <text align ="center" length="48" bold="true">Current Inventory</text>
        </line>
        <line></line>
        <line>
            <text>Date: $posreport.printDate()</text>
        </line>
        <line>
        </line>
        <line>
            <text align ="left" length="20">Product</text>
            <text align ="right" length="10">Units</text>
            <text align ="right" length="18">Value</text>
        </line>
        <line>
            <text>------------------------------------------------</text>
        </line>
        #foreach ($line in $posreport.getResult())
        <line>
            <text align ="left" length="20">${line.printValue(0)} (${line.printValue(1)})</text>
            <text align ="right" length="10">${line.printValue(2)}</text>
            <text align ="right" length="18">${line.printCurrency(3)}</text>
        </line>
        #end
        <line>
            <text>------------------------------------------------</text>
        </line>
        <line>
            <text align ="left" length="30">${posreport.printCount()} Products</text>
            <text align ="right" bold="true" length="18">$posreport.printTotal(3)</text>
        </line>
        </ticket>
</output>

Save

Product Sales Report: #

Create a new text resource with the name POS.Report.ProductSales and copy-paste below lines:

<?xml version="1.0" encoding="UTF-8"?>
<!--
       Posics Saleculator - Billing System.
       Copyright (C) 2009 Posics
       This file is part of Posics Saleculator.
-->
<output>
    <sql><![CDATA[ 
    
    SELECT CATEGORIES.NAME, PRODUCTS.NAME, PRODUCTS.CODE, SUM(TICKETLINES.UNITS) AS UNITS, SUM(TICKETLINES.UNITS * TICKETLINES.PRICE) AS TOTAL 
    FROM RECEIPTS, TICKETS, TICKETLINES 
    LEFT JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID 
    LEFT JOIN CATEGORIES ON CATEGORIES.ID=PRODUCTS.CATEGORY 
    WHERE RECEIPTS.ID = TICKETS.ID AND TICKETS.ID = TICKETLINES.TICKET AND 
    RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate 
    GROUP BY PRODUCTS.ID ORDER BY CATEGORIES.NAME, PRODUCTS.NAME
    
    ]]></sql>
    <ticket>
        <image>Printer.Ticket.Logo</image>
        <line></line>
        <line size="1">
            <text align ="center" length="48" bold="true">Product Sales</text>
        </line>
        <line></line>
        <line>
            <text length="12">Start Date:</text>
            <text length="36">$startDate</text>
        </line>
        <line>
            <text length="12">End Date:</text>
            <text length="36">$endDate</text>
        </line>
        <line>
        </line>
        <line>
            <text align ="left" length="25">Product</text>
            <text align ="right" length="10">Units</text>
            <text align ="right" length="13">Total</text>
        </line>
        <line>
            <text>------------------------------------------------</text>
        </line>
        #foreach ($line in $posreport.getResult())
        <line>
            <text align ="left" length="25">${line.printValue(1)} (${line.printValue(2)})</text>
            <text align ="right" length="10">${line.printValue(3)}</text>
            <text align ="right" length="13">${line.printCurrency(4)}</text>
        </line>
        #end
        <line>
            <text>------------------------------------------------</text>
        </line>
        <line>
            <text align ="left" length="25">Total</text>
            <text align ="right" length="10"></text>
            <text align ="right" bold="true" length="13">$posreport.printTotal(4)</text>
        </line>
        </ticket>
</output>

Save

Profit and Loss Report: #

Create a new text resource with the name POS.Report.ProfitandLoss and copy-paste below lines:

<output>
    <sql><![CDATA[ 
    
	SELECT TICKETS.TICKETID, RECEIPTS.DATENEW, 
    SUM(TICKETLINES.PRICE * TICKETLINES.UNITS), 
    SUM((TICKETLINES.PRICE-PRODUCTS.PRICEBUY) * TICKETLINES.UNITS)
    FROM RECEIPTS
    JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID AND (TICKETS.TICKETTYPE=0 OR TICKETS.TICKETTYPE=1)
    JOIN TICKETLINES ON TICKETLINES.TICKET = RECEIPTS.ID
    JOIN PRODUCTS ON PRODUCTS.ID= TICKETLINES.PRODUCT
    WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate
    GROUP BY TICKETS.TICKETID, RECEIPTS.DATENEW 
    ORDER BY RECEIPTS.DATENEW;
    ]]></sql>
    <ticket>
        <image>Printer.Ticket.Logo</image>
        <line></line>
        <line size="1">
            <text align ="center" length="48" bold="true">Invoice Profit</text>
        </line>
        <line></line>
        <line>
            <text length="12">Start Date:</text>
            <text length="36">$startDate</text>
        </line>
        <line>
            <text length="12">End Date:</text>
            <text length="36">$endDate</text>
        </line>
        <line>
        </line>
        <line>
            <text align ="left" length="5">#</text>
            <text align ="left" length="20">Date</text>
            <text align ="right" length="13">Total</text>
            <text align ="right" length="10">Profit</text>
        </line>
        <line>
            <text>------------------------------------------------</text>
        </line>
        #foreach ($line in $posreport.getResult())
        <line>
            <text align ="left" length="5">${line.printValue(0)}</text>
            <text align ="left" length="20">${line.printDate(1)}</text>
            <text align ="right" length="13">${line.printCurrency(2)}</text>
            <text align ="right" length="10">${line.printCurrency(3)}</text>
        </line>
        #end
        <line>
            <text>------------------------------------------------</text>
        </line>
        <line>
            <text align ="left" length="5">Total</text>
            <text align ="left" length="20"></text>
            <text align ="right" bold="true" length="13">$posreport.printTotal(2)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(3)</text>
        </line>
        </ticket>
</output>

Save

Tax Report (SGST & CGST): #

Create a new text resource with the name POS.Report.Tax and copy-paste below lines:

<?xml version="1.0" encoding="UTF-8"?>
<!--
       Posics Saleculator - Billing System.
       Copyright (C) 2009 Posics
       This file is part of Posics Saleculator.
-->
<output>
    <sql><![CDATA[ 

SELECT RECEIPTS.ID AS RECEIPT, RECEIPTS.DATENEW AS DATE, TICKETS.TICKETID, SUM(TICKETLINES.UNITS) AS QTY, 
IFNULL(TAXLINES.BASE,0)+(2*IFNULL(TAXLINES.AMOUNT,0))
+IFNULL(CGST6.BASE,0)+(2*IFNULL(CGST6.TAX,0))
+IFNULL(CGST9.BASE,0)+(2*IFNULL(CGST9.TAX,0))
+IFNULL(CGST12.BASE,0)+(2*IFNULL(CGST12.TAX,0)) AS TOTAL, 
IFNULL(TAXLINES.BASE, 0) AS GST5BASE, IFNULL(TAXLINES.AMOUNT,0) AS CGST25TAX, IFNULL(SGST25.TAX,0) AS SGST25TAX,
IFNULL(CGST6.BASE, 0) AS GST12BASE, IFNULL(CGST6.TAX,0) AS CGST6TAX, IFNULL(SGST6.TAX,0) AS SGST6TAX,
IFNULL(CGST9.BASE, 0) AS GST18BASE, IFNULL(CGST9.TAX,0) AS CGST9TAX, IFNULL(SGST9.TAX,0) AS SGST9TAX,
IFNULL(CGST12.BASE, 0) AS GST24BASE, IFNULL(CGST12.TAX,0) AS CGST12TAX, IFNULL(SGST12.TAX,0) AS SGST12TAX,
IFNULL(EXEMPT.BASE, 0) AS EXEMPTBASE, IFNULL(EXEMPT.TAX,0) AS EXEMPTTAX 

FROM RECEIPTS 

JOIN TICKETS ON TICKETS.ID=RECEIPTS.ID
LEFT JOIN TICKETLINES ON TICKETS.ID=TICKETLINES.TICKET
LEFT JOIN TAXLINES ON RECEIPTS.ID=TAXLINES.RECEIPT AND TAXLINES.TAXID='5f864912-ae05-4594-9948-febe2fef454b'
LEFT JOIN TAXES ON TAXLINES.TAXID=TAXES.ID

LEFT JOIN ( 
SELECT RECEIPTS.ID AS RECEIPT, TICKETS.TICKETID, TAXES.NAME AS TAXNAME, TAXLINES.BASE, TAXLINES.AMOUNT AS TAX FROM RECEIPTS 
JOIN TICKETS ON TICKETS.ID=RECEIPTS.ID
JOIN TICKETLINES ON TICKETS.ID=TICKETLINES.TICKET
JOIN TAXLINES ON RECEIPTS.ID=TAXLINES.RECEIPT AND TAXLINES.TAXID='666249c8-b5a5-4fda-b7bf-296bc7ac4c80'
JOIN TAXES ON TAXLINES.TAXID=TAXES.ID
WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate
GROUP BY RECEIPTS.ID) SGST25 ON RECEIPTS.ID=SGST25.RECEIPT

LEFT JOIN ( 
SELECT RECEIPTS.ID AS RECEIPT, TICKETS.TICKETID, TAXES.NAME AS TAXNAME, TAXLINES.BASE, TAXLINES.AMOUNT AS TAX FROM RECEIPTS 
JOIN TICKETS ON TICKETS.ID=RECEIPTS.ID
JOIN TICKETLINES ON TICKETS.ID=TICKETLINES.TICKET
JOIN TAXLINES ON RECEIPTS.ID=TAXLINES.RECEIPT AND TAXLINES.TAXID='30f17067-3e48-4c76-a123-fc1da49ff3ae'
JOIN TAXES ON TAXLINES.TAXID=TAXES.ID
WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate
GROUP BY RECEIPTS.ID) CGST6 ON RECEIPTS.ID=CGST6.RECEIPT

LEFT JOIN ( 
SELECT RECEIPTS.ID AS RECEIPT, TICKETS.TICKETID, TAXES.NAME AS TAXNAME, TAXLINES.BASE, TAXLINES.AMOUNT AS TAX FROM RECEIPTS 
JOIN TICKETS ON TICKETS.ID=RECEIPTS.ID
JOIN TICKETLINES ON TICKETS.ID=TICKETLINES.TICKET
JOIN TAXLINES ON RECEIPTS.ID=TAXLINES.RECEIPT AND TAXLINES.TAXID='db386ab4-b837-47fd-894b-10ec0f433710'
JOIN TAXES ON TAXLINES.TAXID=TAXES.ID
WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate
GROUP BY RECEIPTS.ID) SGST6 ON RECEIPTS.ID=SGST6.RECEIPT

LEFT JOIN ( 
SELECT RECEIPTS.ID AS RECEIPT, TICKETS.TICKETID, TAXES.NAME AS TAXNAME, TAXLINES.BASE, TAXLINES.AMOUNT AS TAX FROM RECEIPTS 
JOIN TICKETS ON TICKETS.ID=RECEIPTS.ID
JOIN TICKETLINES ON TICKETS.ID=TICKETLINES.TICKET
JOIN TAXLINES ON RECEIPTS.ID=TAXLINES.RECEIPT AND TAXLINES.TAXID='249d73a7-b09e-4e4c-8863-a85957dd7191'
JOIN TAXES ON TAXLINES.TAXID=TAXES.ID
WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate
GROUP BY RECEIPTS.ID) CGST9 ON RECEIPTS.ID=CGST9.RECEIPT

LEFT JOIN ( 
SELECT RECEIPTS.ID AS RECEIPT, TICKETS.TICKETID, TAXES.NAME AS TAXNAME, TAXLINES.BASE, TAXLINES.AMOUNT AS TAX FROM RECEIPTS 
JOIN TICKETS ON TICKETS.ID=RECEIPTS.ID
JOIN TICKETLINES ON TICKETS.ID=TICKETLINES.TICKET
JOIN TAXLINES ON RECEIPTS.ID=TAXLINES.RECEIPT AND TAXLINES.TAXID='c564d077-0c3a-42f8-a4ad-f74437a5e89f'
JOIN TAXES ON TAXLINES.TAXID=TAXES.ID
WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate
GROUP BY RECEIPTS.ID) SGST9 ON RECEIPTS.ID=SGST9.RECEIPT

LEFT JOIN ( 
SELECT RECEIPTS.ID AS RECEIPT, TICKETS.TICKETID, TAXES.NAME AS TAXNAME, TAXLINES.BASE, TAXLINES.AMOUNT AS TAX FROM RECEIPTS 
JOIN TICKETS ON TICKETS.ID=RECEIPTS.ID
JOIN TICKETLINES ON TICKETS.ID=TICKETLINES.TICKET
JOIN TAXLINES ON RECEIPTS.ID=TAXLINES.RECEIPT AND TAXLINES.TAXID='27eefa5f-07ce-4437-93b3-1fa6cd5b7da6'
JOIN TAXES ON TAXLINES.TAXID=TAXES.ID
WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate
GROUP BY RECEIPTS.ID) CGST12 ON RECEIPTS.ID=CGST12.RECEIPT

LEFT JOIN ( 
SELECT RECEIPTS.ID AS RECEIPT, TICKETS.TICKETID, TAXES.NAME AS TAXNAME, TAXLINES.BASE, TAXLINES.AMOUNT AS TAX FROM RECEIPTS 
JOIN TICKETS ON TICKETS.ID=RECEIPTS.ID
JOIN TICKETLINES ON TICKETS.ID=TICKETLINES.TICKET
JOIN TAXLINES ON RECEIPTS.ID=TAXLINES.RECEIPT AND TAXLINES.TAXID='715e7d89-70df-4aed-be72-92c9a71bc32a'
JOIN TAXES ON TAXLINES.TAXID=TAXES.ID
WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate
GROUP BY RECEIPTS.ID) SGST12 ON RECEIPTS.ID=SGST12.RECEIPT

LEFT JOIN ( 
SELECT RECEIPTS.ID AS RECEIPT, TICKETS.TICKETID, TAXES.NAME AS TAXNAME, TAXLINES.BASE, TAXLINES.AMOUNT AS TAX FROM RECEIPTS 
JOIN TICKETS ON TICKETS.ID=RECEIPTS.ID
JOIN TICKETLINES ON TICKETS.ID=TICKETLINES.TICKET
JOIN TAXLINES ON RECEIPTS.ID=TAXLINES.RECEIPT AND TAXLINES.TAXID='000'
JOIN TAXES ON TAXLINES.TAXID=TAXES.ID
WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate
GROUP BY RECEIPTS.ID) EXEMPT ON RECEIPTS.ID=EXEMPT.RECEIPT

WHERE RECEIPTS.DATENEW >= $startDate AND RECEIPTS.DATENEW <= $endDate
GROUP BY RECEIPTS.ID
ORDER BY TICKETS.TICKETID
                
       ]]></sql>
       
         <ticket>
        <line></line>
         <line size="1">
            <text>Saleculator Point of Sale</text>
        </line>
        <line size="1">
            <text>GST Return</text>
        </line>
        <line></line>
        <line>
            <text length="12">Start Date:</text>
            <text length="36">$startDate</text>
        </line>
        <line>
            <text length="12">End Date:</text>
            <text length="36">$endDate</text>
        </line>
        <line>
        </line>
        <line>
            <text align ="left" length="20">DATE</text>
            <text align ="center" length="10">TICKET ID</text>
            <text align ="center" length="10">QTY</text>
            <text align ="right" length="10">TOTAL</text>
            <text align ="right" length="10">GST5BASE</text>
            <text align ="right" length="10">CGST2.5</text>
            <text align ="right" length="10">SGST2.5</text>
            <text align ="right" length="10">GST12BASE</text>
            <text align ="right" length="10">CGST6</text>
            <text align ="right" length="10">SGST6</text>
            <text align ="right" length="10">GST18BASE</text>
            <text align ="right" length="10">CGST9</text>
            <text align ="right" length="10">SGST9</text>
            <text align ="right" length="10">GST24BASE</text>
            <text align ="right" length="10">CGST12</text>
            <text align ="right" length="10">SGST12</text>
            <text align ="right" length="10">EXEMPT</text>
       </line>
         <line>
            <text></text>
        </line>
        #foreach ($line in $posreport.getResult())
        <line>
            <text align ="left" length="20">${line.printDate(1)}</text>
            <text align ="center" length="10">${line.printValue(2)}</text>
            <text align ="center" length="10">${line.printValue(3)}</text>
            <text align ="right" length="10">${line.printCurrency(4)}</text>
            <text align ="right" length="10">${line.printCurrency(5)}</text>
            <text align ="right" length="10">${line.printCurrency(6)}</text>
            <text align ="right" length="10">${line.printCurrency(7)}</text>
            <text align ="right" length="10">${line.printCurrency(8)}</text>
            <text align ="right" length="10">${line.printCurrency(9)}</text>
            <text align ="right" length="10">${line.printCurrency(10)}</text>
            <text align ="right" length="10">${line.printCurrency(11)}</text>
            <text align ="right" length="10">${line.printCurrency(12)}</text>
            <text align ="right" length="10">${line.printCurrency(13)}</text>
            <text align ="right" length="10">${line.printCurrency(14)}</text>
            <text align ="right" length="10">${line.printCurrency(15)}</text>
            <text align ="right" length="10">${line.printCurrency(16)}</text>
            <text align ="right" length="10">${line.printCurrency(17)}</text>
        </line>
        #end
        <line>
            <text></text>
        </line>
        <line>
            <text align ="left" length="20">Total</text>
            <text align ="left" length="10"></text>
            <text align ="center" bold="true" length="10">$posreport.printTotal(3)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(4)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(5)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(6)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(7)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(8)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(9)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(10)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(11)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(12)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(13)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(14)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(15)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(16)</text>
            <text align ="right" bold="true" length="10">$posreport.printTotal(17)</text>
        </line>
        </ticket>
</output>

Save

Note: Follow the below steps to find the Tax ID for different Tax Types

  1. Open MySQL prompt (Windows)
  2. Type SELECT * FROM TAXES; (Press Enter)
  3. This will show you an output listing all the available Taxes along with their IDs, double click on the ID to highlight and copy the same. (Refer to the below Image, Tax IDs are shown by the red tick marks)
The tax ID highlighted on this image is of the CGST 2.5


Methods used in POS Reports #

$posreport.getResult(): Get result of the executed query
$posreport.roundDouble(value, decimals): Get rounded value
$posreport.formatCurrency(value): Get currency format of the value
$posreport.getTotal(field_number): Get total of the field
$posreport.printTotal(field_number): Print currency formatted total of the field
$posreport.printCount(): Print number of records in the result
$posreport.printDate(): Print current date and time

$line.printValue(field_number): Print field value as text
$line.printCurrency(field_number): Print field value as currency
$line.printDate(field_number): Print field value as date
$line.getDouble(field_number): Get double value of the field for calculation
$line.getInteger(field_number): Get integer value of the field for calculation

0 Comments

Leave a Reply

Arrow-up