CORE-POS/IS4C

use translog;
DROP VIEW IF EXISTS `ltt_receipt`;

CREATE view ltt_receipt as 

select
description,
case 
    when voided = 5 
        then 'Discount'
    when trans_status = 'M'
        then 'Mbr special'
    when trans_status = 'S'
        then 'Staff special'
    when scale <> 0 and quantity <> 0 
        then concat(quantity, ' @ ', unitPrice)
    when abs(itemQtty) > 1 and abs(itemQtty) > abs(quantity) and discounttype <> 3 and quantity = 1
        then concat(volume, ' /', unitPrice)
    when abs(itemQtty) > 1 and abs(itemQtty) > abs(quantity) and discounttype <> 3 and quantity <> 1
        then concat(Quantity, ' @ ', Volume, ' /', unitPrice)
    when abs(itemQtty) > 1 and discounttype = 3
        then concat(ItemQtty, ' /', UnitPrice)
    when abs(itemQtty) > 1
        then concat(quantity, ' @ ', unitPrice)    
    when matched > 0
        then '1 w/ vol adj'
    else ''
        
end
as comment,
total,

case 
    when trans_status = 'V' 
        then 'VD'
    when trans_status = 'R'
        then 'RF'
    when tax <> 0 and foodstamp <> 0
        then 'TF'
    when tax <> 0 and foodstamp = 0
        then 'T' 
    when tax = 0 and foodstamp <> 0
        then 'F'
    when tax = 0 and foodstamp = 0
        then '' 
end
as Status,
trans_type,
unitPrice,
voided,
trans_id

from localtemptrans
where voided <> 5 and UPC <> 'TAX' and UPC <> 'DISCOUNT'
order by trans_id