Edit Report:Sellout Report
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Sellout Report
-- Joe's Sellout Report
-- ^sales
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "yesterday", end: "yesterday" }
-- }
-- VARIABLE: {
-- name: "mfg",
-- display: "Manufacturer",
-- type: "select",
-- database_options: { table: "manufacturer", display: "name", column: "manufacturer_id", order:{ column: "name" , order: "ASC"} },
-- multiple:true,
-- empty:true,
-- }
-- VARIABLE: {
-- name: "plv",
-- display: "Price Level",
-- type: "select",
-- database_options: { table: "salestypes", display: "sales_type", column: "typeabbrev", order:{ column: "sales_type" , order: "ASC"} },
-- multiple:true,
-- empty:true,
-- }
-- VARIABLE: {
-- name: "customer",
-- display: "Customer Number (COMMA SEPERATED LIST FOR MULTIPLE)",
-- type: "text",
-- }
-- VARIABLE: {
-- name: "sumdet",
-- display: "Detail or Summary?",
-- type: "select",
-- multiple: false,
-- options: [{display: 'Detail', value:'0'},{display: 'Summary', value:'1'}],
-- }
-- ROLLUP: {
-- columns: {
-- "Total Sales":"{{sum}}",
-- "Qty Sold": "{{sum}}"
-- }
--}
-- FILTER: {
-- filter: "number",
-- column: "Customer Number",
-- params: {decimals: 2, decimal_sep: '.', thousands_sep: ''}
-- }
SELECT manufacturer.name as `Vendor Name`, mfgpart as `Vendor Part`,
{%if sumdet == 1 %}
sum(qty * -1.00) as `Qty Sold`,
CAST(
sum(discountedlinetotal * -1.00) as Decimal(10,2)) as `Total Sales`,
{% else %}
(qty * -1.00) as `Qty Sold`,
DATE_FORMAT(trandate, "%m/%d/%Y") AS `Invoice Date`,
CAST(( discountedlinetotal * -1.00)as Decimal(10,2)) as `Total Sales`,
transno as `Invoice Number`,
stocktrans.debtorno as `Customer Number`,
brname as `Customer Name`
{% endif %}
from stocktrans
inner join product_to_manufacturer on stocktrans.stockid = product_to_manufacturer.stockid
inner join manufacturer on manufacturer.manufacturer_id = product_to_manufacturer.manufacturer_id
inner join debtorsmaster on stocktrans.debtorno = debtorsmaster.debtorno
inner join stockmaster on stocktrans.stockid = stockmaster.stockid
left join tireinfo on stocktrans.stockid = tireinfo.stockid
where trandate >= '{{range.start}}' and trandate <= '{{range.end}}'
{% if customer != '' %}
and stocktrans.debtorno IN ({{customer}})
{% endif %}
{% if mfg[0] != '' %}
{% if mfg|length > 0 %}
and manufacturer.manufacturer_id in (
{% for m in mfg %}
'{{ m }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
{% endif %}
{% if plv[0] != '' %}
{% if plv|length > 0 %}
and debtorsmaster.salestype in (
{% for p in plv %}
'{{ p }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
{% endif %}
{%if sumdet == 1 %}
group by mfgpart
{% endif %}