CORE-POS/IS4C

use translog;
DROP VIEW IF EXISTS `lttsummary`;

CREATE view lttsummary as

select 

CASE WHEN (min(datetime) is null) THEN now() ELSE min(datetime) END as tdate,

case when (min(datetime) is null) then 0 else max(card_no) end as card_no,

case when (min(datetime) is null) then 0 else sum(total) end as runningTotal,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN (discounttype = 1) THEN discount ELSE 0 END)
end as discountTTL,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN discounttype = 2 THEN memDiscount ELSE 0 END)
end as memSpecial,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN discounttype = 4 THEN memDiscount ELSE 0 END)
end as staffSpecial,

case when (min(datetime) is null) then 0 else

    sum(CASE WHEN discountable = 0 THEN 0 ELSE total END)
end as discountableTTL,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN discountable = 7 THEN total ELSE 0 END) 
end as scDiscountableTTL,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN (trans_type = 'I' or trans_type = 'D') and tax = 1 THEN total ELSE 0 END)
end as Taxable,

case when (min(datetime) is null) then 0 else
sum(
    CASE     
        WHEN (trans_type = 'I' or trans_type = 'D') and tax = 1 and discountable <> 7 THEN total 
        WHEN (trans_type = 'I' or trans_type = 'D') and tax = 1 and discountable = 7 THEN (total * 0.9)
        ELSE 0 END)
end as scTaxable,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN trans_subtype = 'MI' or trans_subtype = 'CX'  THEN total ELSE 0 END)

end as chargeTotal,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN trans_subtype = 'CC'  THEN total ELSE 0 END)
end as ccTotal,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN department = 990  THEN total ELSE 0 END)
end as paymentTotal,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN trans_subtype = 'MI'  or department = 990 THEN total ELSE 0 END)
end as memChargeTotal,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN trans_type = 'T' THEN total ELSE 0 END)
end as tENDerTotal,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN (foodstamp = 1 and tax = 1) THEN total ELSE 0 END)
end as fsTaxable,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN trans_subtype = 'FS' or trans_subtype = 'EF' THEN total ELSE 0 END)
end as fsTENDered,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN (foodstamp = 1 or trans_subtype='FS' or trans_subtype='EF') THEN total ELSE 0 END)
end as fsTotal,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN foodstamp = 1 THEN total ELSE 0 END)
end as fsItems,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN trans_status = 'R' THEN total ELSE 0 END)
end as refundTotal,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN upc = '0000000008005' THEN total ELSE 0 END)
end as couponTotal,

case when (min(datetime) is null) then 0 else
    sum(CASE WHEN upc = 'MEMCOUPON' THEN unitPrice ELSE 0 END)
end as memCoupon,

case 
    when (min(datetime) is null) then 0 
    when (max(percentDiscount) is null) then 0
    when (max(percentDiscount) < 0) then 0
    else max(percentDiscount)
end as percentDiscount,

case when (min(datetime)) is null then 0 else 
    max(trans_id) 
end as LastID


from localtemptrans