Edit Report:Traffic Report By Hour
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Traffic Report By Hour
-- On Account vs Paid by hour
-- Select a date range
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "10 days ago", end: "today" }
-- }
-- VARIABLE: {
-- name: "location",
-- display: "Location",
-- type: "select",
-- database_options: { table: "locations", display: "locationname", column: "loccode" },
-- multiple:true,
-- empty: true,
-- }
-- CHART: {
-- "columns": ["HOUR","ONACCOUNT","PAID"],
-- "type": "AreaChart",
-- "title": "Traffic by hour Sales ",
-- }
-- ROLLUP: {
-- columns: {
-- "ONACCOUNT": "Total: {{sum}}",
-- "PAID": "Total: {{sum}}",
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "ONACCOUNT",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "PAID",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "HOUR",
-- params: {
-- class: "center"
-- }
-- }
SELECT
DAYNAME(trandate) AS 'Day',
inputdate as 'Hour',
debtorno,
SUM(
CAST(
(alloc ) AS DECIMAL ( 10, 2 ))) AS 'Total Sales',
SUM(
CASE WHEN
cast( (ovamount+ovfreight+ovgst+ovdiscount) as decimal(10,2)) <> CAST(alloc as DECIMAL(10,2))
THEN 1
ELSE 0 END )AS ONACCOUNT,
SUM(
CASE WHEN cast( (ovamount+ovfreight+ovgst+ovdiscount) as decimal(10,2)) = CAST(alloc as DECIMAL(10,2))
THEN 1
ELSE 0 END )AS PAID
FROM
debtortrans d
where trandate between "{{ range.start }}" AND "{{ range.end }}"
{% if location is not empty %}
and order_ in (
select orderno from salesorders where type in(10,11)
and orddate between "{{ range.start }}" AND "{{ range.end }}"
and fromstkloc in (
{% for loc in location %}
{% if loc != '' %}
'{{ loc }}'
{{ not loop.last ? ',' }}
{% else %}
select loccode from locations
{% endif %}
{% endfor %})
)
{% endif %}
and type in (10,11)
GROUP BY year(d.inputdate) , day( d.inputdate ), hour( d.inputdate ),debtorno
order by d.inputdate asc