Edit Report:SalonCentric POS Sales Report
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- SalonCentric POS Sales Report
-- From AA Website
-- ^receivables
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of this month", end: "today" }
-- }
-- VARIABLE: {
-- name: "ppd",
-- display: "PPD Vendor(s)",
-- type: "select",
-- database_options: { table: "suppliers", display: "suppname", column: "supplierid", order:{ column: "suppname" , order: "ASC"}, where: "supplierid in ('biola', 'lorea', 'matri', 'pulpr', 'pureo', 'redke')" },
-- multiple:true,
-- empty:true,
-- }
-- VARIABLE: {
-- name: "partner",
-- display: "Partner Vendor(s)",
-- type: "select",
-- database_options: { table: "suppliers", display: "suppname", column: "supplierid", order:{ column: "suppname" , order: "ASC"}, where: "supplierid in ('amika', 'joico', 'kenra', 'malib', 'k18', 'olapl', 'its10', 'abc', 'igk', 'kcomp', 'mizan', 'sexyh')" },
-- multiple:true,
-- empty:true,
-- }
-- VARIABLE: {
-- name: "independant",
-- display: "Independant Vendor(s)",
-- type: "select",
-- database_options: { table: "suppliers", display: "suppname", column: "supplierid", order:{ column: "suppname" , order: "ASC"}, where: "supplierid in ('gamap', 'surfa', 'moroc', 'loma', 'lanza', 'kerat', 'difia', 'color', 'bbb')" },
-- multiple:true,
-- empty:true,
-- }
-- VARIABLE: {
-- name: "misc",
-- display: "Misc Vendor(s)",
-- type: "select",
-- database_options: { table: "suppliers", display: "suppname", column: "supplierid", order:{ column: "suppname" , order: "ASC"}, where:"not supplierid in ('gamap', 'surfa', 'moroc', 'loma', 'lanza', 'kerat', 'difia', 'color', 'bbb', 'amika', 'joico', 'kenra', 'malib', 'k18', 'olapl', 'its10', 'abc', 'igk', 'kcomp', 'mizan', 'sexyh', 'biola', 'lorea', 'matri', 'pulpr', 'pureo', 'redke')" },
-- multiple:true,
-- empty:true,
-- }
-- VARIABLE: {
-- name: "location",
-- display: "Branches",
-- type: "select",
-- database_options: { table: "locations", display: "locationname", column: "loccode", order:{ column: "locationname" , order: "ASC"} },
-- multiple:true,
-- empty:true,
-- }
--
SELECT YEAR(stocktrans.trandate) as `Year`, stocktrans.trandate as `Invoice Date`, fromstkloc AS `Store Code`,
(case when enteredby = 'WO' then '00 - WEBSITE ORDER' else locationname end) AS `Store Location`,
(case when fromstkloc = '00' then salesperson else '' end) as `DSC`,
supplierno as `Brand`, stocktrans.lineid as `Material Division`, stockmaster.stockid as `SKU`,
suppliers_partno as `Vendor Part`,
barcode as `UPC`, itemdesc as `Description`,
gl_class_desc.name as `Category`,debtorno as CUSTOMER_NUMBER,
round(stocktrans.price * qty * -1,2) as `$ Sales`, -1 * qty as `Qty Sold` FROM stocktrans INNER JOIN stockmaster
ON stockmaster.stockid = stocktrans.stockid
INNER JOIN gl_class_desc
ON stockmaster.glclass = gl_class_desc.code
LEFT JOIN purchdata ON stockmaster.stockid = purchdata.stockid
inner join locations on stocktrans.fromstkloc = locations.loccode
WHERE stocktrans.trandate >= '{{range.start}}' AND stocktrans.trandate <= '{{range.end}}'
{% if location[0] != '' %}
{% if location|length > 0 %}
and fromstkloc in (
{% for l in location %}
'{{ l }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
{% endif %}
{% if misc[0] != '' %}
{% if misc|length > 0 %}
and (supplierno in (
{% for v in misc %}
'{{ v }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
{% endif %}
{% if independant[0] != '' %}
{% if independant|length > 0 %}
{% if misc|length > 0 and misc[0] != '' %}
or
{% endif %}
{%if misc|length > 0 and misc[0] == '' %}
and (
{% endif%}
supplierno in (
{% for v in independant %}
'{{ v }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
{% endif %}
{% if ppd[0] != '' %}
{% if ppd|length > 0 %}
{% if (misc|length > 0 and misc[0] != '') or (independant|length > 0 and independant[0] != '')%}
or
{% endif %}
{%if misc|length > 0 and misc[0] == '' and independant|length > 0 and independant[0] == '' %}
and (
{% endif%}
supplierno in (
{% for v in ppd %}
'{{ v }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
{% endif %}
{% if partner[0] != '' %}
{% if partner|length > 0 %}
{% if (misc|length > 0 and misc[0] != '') or (independant|length > 0 and independant[0] != '') or (ppd|length > 0 and ppd[0] != '')%}
or
{% endif %}
{%if misc|length > 0 and misc[0] == '' and independant|length > 0 and independant[0] == '' and ppd|length > 0 and ppd[0] == ''%}
and (
{% endif%}
supplierno in (
{% for v in partner %}
'{{ v }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
{% endif %}
)