CORE-POS/IS4C

use translog;
DROP VIEW IF EXISTS `screendisplay`;

CREATE view screendisplay as 
select 
    
    CASE

    WHEN (voided = 5 or voided = 11 or voided = 17 or trans_type = 'T')
        THEN ''
    ELSE
        description
    END

as description,

    CASE

    WHEN(discounttype = 3 and trans_status = 'V')
        THEN CONCAT(ItemQtty,' /',UnitPrice)
    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 (SUBSTRING(upc, 1, 3) = '002')
        THEN CONCAT( itemQtty,' @ ',regPrice)
    WHEN (abs(itemQtty) > 1 and abs(itemQtty) > abs(quantity) and discounttype <> 3 and quantity = 1)
        THEN CONCAT(volume,' for ',unitPrice)
    WHEN (abs(itemQtty) > 1 and abs(itemQtty) > abs(quantity) and discounttype <> 3 and quantity <> 1)
        THEN CONCAT(Quantity,' @ ',Volume,' for ',unitPrice)
    WHEN (abs(itemQtty) > 1 and discounttype = 3)
        THEN CONCAT(ItemQtty,' /',UnitPrice)
    WHEN (abs(itemQtty) > 1)
        THEN CONCAT(quantity,' @ ',unitPrice)    
    WHEN (voided = 3)
        THEN 'Total '
    WHEN (voided = 5)
        THEN 'Discount '
    WHEN (voided = 7)
        THEN ''
    WHEN (voided = 11 or voided = 17)
        THEN upc
    WHEN (matched > 0)
        THEN '1 w/ vol adj'
    WHEN (trans_type = 'T')
        THEN description
    ELSE
        ''
    END

as comment,

    CASE

    WHEN (voided = 3 or voided = 5 or voided = 7 or voided = 11 or voided = 17)
        THEN unitPrice
    WHEN (trans_status = 'D')
        THEN ''
    ELSE
        total
    END

as total,

    CASE

    WHEN (trans_status = 'V')
        THEN 'VD'
    WHEN (trans_status = 'R')
        THEN 'RF'
    WHEN (trans_status = 'C')
        THEN 'MC'
    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 ''
    ELSE
        ''
    END

as status,

    CASE

    WHEN (trans_status = 'V' or trans_type = 'T' or trans_status = 'R' or trans_status = 'C' or trans_status = 'M' or voided = 17 or trans_status = 'J')
        THEN '800000'
    WHEN (discounttype <> 0 or voided = 2 or voided = 6 or voided = 4 or voided = 5 or voided = 10 or voided = 22)
        THEN '408080'
    WHEN (voided = 3 or voided = 11)
        THEN '000000'
    WHEN (voided = 7)
        THEN '800080'
    ELSE
        '004080'
    END

as lineColor,

discounttype,
trans_type,
trans_status,
voided,
trans_id

from localtemptrans
order by trans_id