Skip to content

Instantly share code, notes, and snippets.

@nickdotht

nickdotht/ops-report.sql

Created March 6, 2018 18:12
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save nickdotht/54bb48cfe7fea05315e51f769cefb12a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save nickdotht/54bb48cfe7fea05315e51f769cefb12a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. nickdotht created this gist Mar 6, 2018.
    113 changes: 113 additions & 0 deletions ops-report.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,113 @@
    SELECT
    kiosk.name AS ContactName,
    '' AS 'EmailAddress',
    '' AS 'POAddressLine1',
    '' AS 'POAddressLine2',
    '' AS 'POAddressLine3',
    '' AS 'POAddressLine4',
    '' AS 'POCity',
    'Saintard' AS 'PORegion',
    '' AS 'POPostalCode',
    '' AS 'POCountry',
    CASE
    WHEN
    kiosk.name = 'Saintard'
    THEN
    CONCAT('K1',
    DATE_FORMAT(receipt.created_date, '%y%m%d'))
    WHEN
    kiosk.name = 'Corail'
    THEN
    CONCAT('K2',
    DATE_FORMAT(receipt.created_date, '%y%m%d'))
    WHEN
    kiosk.name = 'Cabaret'
    THEN
    CONCAT('K4',
    DATE_FORMAT(receipt.created_date, '%y%m%d'))
    WHEN
    kiosk.name = 'Santo19'
    THEN
    CONCAT('K5',
    DATE_FORMAT(receipt.created_date, '%y%m%d'))
    WHEN
    kiosk.name = 'Bois9'
    THEN
    CONCAT('K6',
    DATE_FORMAT(receipt.created_date, '%y%m%d'))
    WHEN
    kiosk.name = 'Quartier Morin'
    THEN
    CONCAT('K7',
    DATE_FORMAT(receipt.created_date, '%y%m%d'))
    WHEN
    kiosk.name = 'Limonade'
    THEN
    CONCAT('K8',
    DATE_FORMAT(receipt.created_date, '%y%m%d'))
    WHEN
    kiosk.name = 'Ouanaminthe'
    THEN
    CONCAT('K9',
    DATE_FORMAT(receipt.created_date, '%y%m%d'))
    END AS 'InvoiceNumber',
    '' AS 'Reference',
    DATE_FORMAT(receipt.created_date, '%c/%e/%y') AS InvoiceDate,
    DATE_FORMAT(receipt.created_date, '%c/%e/%y') AS DueDate,
    '' AS 'Total',
    receipt_line_item.sku AS 'InventoryItemCode',
    product.description AS 'Description',
    receipt_line_item.quantity AS 'Quantity',
    product.price_amount AS 'UnitAmount',
    '' AS 'Discount',
    CASE
    WHEN receipt_line_item.sku = 'OB5G' THEN 1208
    WHEN receipt_line_item.sku = 'MP' THEN 4010
    WHEN receipt_line_item.sku = 'PPP' THEN 4011
    WHEN receipt_line_item.sku = 'M5GALON' THEN 4020
    WHEN receipt_line_item.sku = 'R5GALON' THEN 4021
    WHEN receipt_line_item.sku = 'RSP' THEN 4025
    WHEN receipt_line_item.sku = 'DLM' THEN 4023
    WHEN receipt_line_item.sku = 'DLM4' THEN 4023
    WHEN receipt_line_item.sku = 'DLM5' THEN 4024
    WHEN receipt_line_item.sku = 'RPIYAY' THEN 4016
    WHEN receipt_line_item.sku = 'DTGP' THEN 4150
    WHEN receipt_line_item.sku = 'B1G' THEN 4045
    WHEN receipt_line_item.sku = 'B10L' THEN 4046
    WHEN receipt_line_item.sku = 'TMP' THEN 4151
    WHEN receipt_line_item.sku = 'FIN10' THEN 4144
    WHEN receipt_line_item.sku = 'P140' THEN 4016
    END AS 'AccountCode',
    'Tax on Sales' AS 'TaxType',
    '' AS 'TaxAmount',
    'Kiosk' AS 'TrackingName1',
    kiosk.name AS 'TrackingOption1',
    'Funding Source' AS 'TrackingName2',
    'Revenue' AS 'TrackingOption2'
    FROM
    receipt
    INNER JOIN
    kiosk ON kiosk.id = receipt.kiosk_id
    INNER JOIN
    receipt_line_item ON receipt.id = receipt_line_item.receipt_id
    INNER JOIN
    customer_account ON receipt.customer_account_id = customer_account.id
    INNER JOIN
    product ON receipt_line_item.sku = product.sku
    WHERE
    receipt.created_date BETWEEN '2018-01-01' AND '2018-02-01'
    AND receipt_line_item.sku IN ('MP' , 'PPP',
    'DLM',
    'DLM4',
    'DLM5',
    'RSP',
    'M5GALON',
    'R5GALON',
    'DTGP',
    'OB5G',
    'B1G',
    'B10L',
    'TMP',
    'FIN10',
    'P140')
    ORDER BY kiosk.name , receipt.created_date;