Edit Report:Cust Item Sales History (SHP) (Summary with cost)
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Cust Item Sales History (SHP) (Summary with cost)
-- Customer Sales History Details with cost
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "last day of last month" }
-- }
-- VARIABLE: {
-- name: "debtorno",
-- display: "Cust Number",
-- type: "text",
-- }
-- ROLLUP: {
-- columns: {
-- "Sales Amt": "{{ sum }}",
-- "Cost": "{{ sum }}",
-- "Qty Sld": "{{ sum }}",
-- "Profit": "{{ sum }}",
-- "stockid": "{{ count }}"
-- }
--}
select
s.debtorno as `CustNo`,
s.branchcode as Branch,
d.name as `Cust Name`,
s.stockid,
sm.description,
CAST(sum(s.qty*-1) as DECIMAL(10,2)) as `Qty Sld`,
CAST(sum(s.discountedlinetotal*-1) as DECIMAL(10,2)) as `Sales Amt`,
CAST(sum(s.costtotal*-1) as DECIMAL(10,2)) as `Cost`,
CAST(sum( (s.discountedlinetotal-s.costtotal) * - 1 ) as DECIMAL(10,2)) AS Profit,
CAST(((sum(s.discountedlinetotal-s.costtotal)/sum( s.discountedlinetotal)) * 100 ) as DECIMAL(10,2))AS `Profit%`
from stocktrans s
left join debtorsmaster d on s.debtorno = d.debtorno
left join stockmaster sm on s.stockid = sm.stockid
where s.trandate between "{{ range.start }}" AND "{{ range.end }}"
{% if debtorno != '' %}
and s.debtorno = '{{ debtorno }}'
{% endif %}
group by s.branchcode, s.stockid
order by s.debtorno, s.branchcode, s.stockid