/*
SUMMARY: Sales tax collected by day
DATABASE: bi-test, referencing magento
AUTHOR: Aria Stewart
NOTES ON WHAT THIS DOES, BUSINESS-WISE:
    Monthly sales tax reporting, should tie out with Vertex
TECHNICAL NOTES:
    Expected runtime is 5 minutes and does not drop with date range.
*/

DELIMITER ;;

SET @@sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

REPLACE INTO view_sql_modes (table_name, sql_mode) VALUES ('v_tax_collected', @@sql_mode);;

CREATE OR REPLACE VIEW v_tax_collected AS
    WITH
        invoices AS (
            SELECT
                i.store_id
                    AS store_id,
                i.created_at
                    AS created_at,
                IF((COALESCE(i.grand_total, 0) = 0 AND t.created_at IS NULL), i.created_at, t.created_at)
                    AS captured_at,
                iad.est_activity_date
                    AS est_activity_date,
                CASE p.method
                    WHEN 'paypal_express' THEN 'paypal'
                    WHEN 'creditcard' THEN IF(p.cc_type = 'AE','amex','litle')
                    ELSE 'other'
                END
                    AS payment_source,
                t.txn_id
                    AS payment_trans_id,
                p.cc_type,
                p.method
                    AS payment_method,
                iad.order_increment_id
                    AS order_increment_id,
                i.increment_id
                    AS invoice_id,
                NULL
                    AS creditmemo_id,
                COALESCE(a.region, b.region)
                    AS region,
                COALESCE(a.postcode, b.postcode)
                    AS postcode,
                ROUND(COALESCE(ii.taxable_product_amount, 0), 2)
                    AS taxable_product_amount,
                ROUND(COALESCE(ii.taxable_product_discount, 0), 2)
                    AS taxable_product_discount,
                ROUND(COALESCE(ii.nontaxable_product_amount, 0), 2)
                    AS nontaxable_product_amount,
                ROUND(COALESCE(ii.nontaxable_product_discount, 0), 2)
                    AS nontaxable_product_discount,
                ROUND(COALESCE(ii.total_product_amount_star, 0), 2)
                    AS total_product_amount_star,
                ROUND(COALESCE(ii.total_product_tax_amount, 0), 2)
                    AS total_product_tax_amount,
                ROUND(COALESCE(i.shipping_amount, 0), 2)
                    AS shipping_amount,
                ROUND(-1 * COALESCE(o.shipping_discount_amount, 0), 2)
                    AS shipping_discount_amount,
                ROUND(COALESCE(i.shipping_tax_amount, 0), 2)
                    AS shipping_tax_amount,
                ROUND(COALESCE(i.tax_amount, 0), 2)
                    AS total_tax_collected_star,
                ROUND(-1 * COALESCE(i.customer_balance_amount, 0), 2)
                    AS store_credit,
                ROUND(-1 * COALESCE(i.gift_cards_amount, 0), 2)
                    AS gift_card,
                ROUND(-1 * COALESCE(i.reward_currency_amount, 0), 2)
                    AS reward_points,
                ROUND(0, 2)
                    AS adjustment,
                ROUND(COALESCE(i.grand_total, 0), 2)
                    AS grand_total,
                ROUND(
                    COALESCE(i.grand_total, 0)
                    + COALESCE(i.customer_balance_amount, 0)
                    + COALESCE(i.gift_cards_amount, 0)
                    + COALESCE(i.reward_currency_amount, 0), 2
                )
                    AS grand_total_paid
            FROM mv_invoice_est_activity_date AS iad
            JOIN magento.sales_flat_invoice AS i ON i.entity_id = iad.invoice_id
            LEFT JOIN (
                SELECT
                        ii2.parent_id
                            AS parent_id,
                        SUM(IF(ii2.tax_amount > 0, COALESCE(ii2.row_total, 0), 0))
                            AS taxable_product_amount,
                        -1 * SUM(IF(COALESCE(ii2.tax_amount, 0) > 0, COALESCE(ii2.discount_amount,0), 0))
                            AS taxable_product_discount,
                        SUM(IF(COALESCE(ii2.tax_amount, 0) > 0, 0, COALESCE(ii2.row_total, 0)))
                            AS nontaxable_product_amount,
                        -1 * SUM(IF(COALESCE(ii2.tax_amount, 0) > 0, 0, COALESCE(ii2.discount_amount, 0)))
                            AS nontaxable_product_discount,
                        SUM(COALESCE(ii2.row_total, 0))
                            AS total_product_amount_star,
                        SUM(COALESCE(ii2.tax_amount, 0))
                            AS total_product_tax_amount
                    FROM magento.sales_flat_invoice_item AS ii2
                    GROUP BY ii2.parent_id
            ) AS ii ON i.entity_id = ii.parent_id
            LEFT JOIN magento.sales_flat_order o ON o.entity_id = i.order_id
            LEFT JOIN magento.sales_flat_order_address a ON i.order_id = a.parent_id AND a.address_type = 'shipping'
            LEFT JOIN magento.sales_flat_order_address b ON i.order_id = b.parent_id AND b.address_type = 'billing'
            LEFT JOIN magento.sales_payment_transaction t ON t.order_id = o.entity_id AND t.txn_type = 'capture' AND i.transaction_id = t.txn_id
            LEFT JOIN magento.sales_flat_order_payment p ON p.parent_id = o.entity_id
            WHERE iad.invoice_state = 2
        ),
        creditmemos AS (
            SELECT
                c.store_id
                    AS store_id,
                c.created_at
                    AS created_at,
                IF((COALESCE(c.grand_total, 0) = 0 AND t2.created_at IS NULL), c.created_at, t2.created_at)
                    AS captured_at,
                cad.est_activity_date
                    AS est_activity_date,
                CASE p2.method
                    WHEN 'paypal_express' THEN 'paypal'
                    WHEN 'creditcard' THEN IF(p2.cc_type = 'AE', 'amex', 'litle')
                    ELSE 'other'
                END
                    AS payment_source,
                t2.txn_id
                    AS payment_trans_id,
                p2.cc_type,
                p2.method
                    AS order_payment_method,
                cad.order_increment_id
                    AS order_increment_id,
                NULL
                    AS invoice_id,
                c.increment_id
                    AS creditmemo_id,
                COALESCE(a2.region, b2.region)
                    AS region,
                COALESCE(a2.postcode, b2.postcode)
                    AS postcode,
                ci.taxable_product_amount
                    AS taxable_product_amount,
                ci.taxable_product_discount
                    AS taxable_product_discount,
                ci.nontaxable_product_amount
                    AS nontaxable_product_amount,
                ci.nontaxable_product_discount
                    AS nontaxable_product_discount,
                ci.total_product_amount_star
                    AS total_product_amount_star,
                ci.total_product_tax_amount
                    AS total_product_tax_amount,
                ROUND(-1 * COALESCE(c.shipping_amount, 0), 2)
                    AS shipping_amount,
                ROUND(LEAST(COALESCE(o2.shipping_discount_amount, 0), COALESCE(c.shipping_amount, 0)), 2)
                    AS shipping_discount_amount,
                ROUND(-1 * COALESCE(c.shipping_tax_amount, 0), 2)
                    AS shipping_tax_amount,
                ROUND(-1 * COALESCE(c.tax_amount, 0), 2)
                    AS total_tax_collected_star,
                ROUND(COALESCE(c.customer_bal_total_refunded, 0), 2)
                    AS store_credit,
                ROUND(COALESCE(c.refunded_to_giftcard, 0), 2)
                    AS gift_card,
                ROUND(COALESCE(c.reward_points_balance_refund, 0), 2)
                    AS reward_points,
                ROUND(COALESCE(c.adjustment_negative, 0) - COALESCE(c.adjustment_positive, 0), 2)
                    AS adjustment,
                ROUND(-1 * COALESCE(c.grand_total, 0), 2)
                    AS grand_total,
                ROUND((-1 * COALESCE(c.grand_total, 0)) - COALESCE(c.customer_bal_total_refunded, 0) - COALESCE(c.refunded_to_giftcard, 0) - COALESCE(c.reward_points_balance_refund, 0), 2)
                    AS grand_total_paid
            FROM mv_creditmemo_est_activity_date AS cad
            JOIN magento.sales_flat_creditmemo c ON c.entity_id = cad.creditmemo_id
            LEFT JOIN (
                SELECT
                        ci.parent_id
                            AS parent_id,
                        ROUND(-1 * SUM(IF(COALESCE(ci.tax_amount, 0), COALESCE(ci.row_total, 0), 0)),2)
                            AS taxable_product_amount,
                        ROUND(SUM(IF(COALESCE(ci.tax_amount, 0), COALESCE(ci.discount_amount, 0), 0)),2)
                            AS taxable_product_discount,
                        ROUND(-1 * SUM(IF(COALESCE(ci.tax_amount, 0), 0, COALESCE(ci.row_total, 0))), 2)
                            AS nontaxable_product_amount,
                        ROUND(SUM(IF(COALESCE(ci.tax_amount, 0), 0, COALESCE(ci.discount_amount, 0))), 2)
                            AS nontaxable_product_discount,
                        ROUND(-1 * SUM(COALESCE(ci.row_total, 0)), 2)
                            AS total_product_amount_star,
                        ROUND(-1 * SUM(COALESCE(ci.tax_amount, 0)), 2)
                            AS total_product_tax_amount
                    FROM magento.sales_flat_creditmemo_item AS ci
                    GROUP BY parent_id
            ) ci ON c.entity_id = ci.parent_id
            LEFT JOIN magento.sales_flat_order o2 ON o2.entity_id = c.order_id
            LEFT JOIN magento.sales_flat_order_address a2 ON c.order_id = a2.parent_id AND a2.address_type = 'shipping'
            LEFT JOIN magento.sales_flat_order_address b2 ON c.order_id = b2.parent_id AND b2.address_type = 'billing'
            LEFT JOIN magento.sales_payment_transaction t2 ON t2.order_id = o2.entity_id AND t2.txn_type = 'refund' AND c.transaction_id = t2.txn_id
            LEFT JOIN magento.sales_flat_order_payment p2 ON p2.parent_id = o2.entity_id
            WHERE cad.creditmemo_state = 2
        )
        SELECT * FROM invoices UNION ALL SELECT * FROM creditmemos
;;

DELIMITER ;
