WITH VendorCodes AS (
    SELECT DISTINCT vendorcode
    FROM products.vendor_map
),
OrderDetailsWithVertical AS (
    SELECT
        od.*,
        p.vertical,
        vm.vendorcode
    FROM
        custopayted.orderdetails od
        JOIN products.client_map cm ON od.productcode = cm.productcode AND cm.client = 'volusion'
        JOIN products.pins p ON p.PIN = cm.PIN
        JOIN products.vendor_map vm ON cm.PIN = vm.PIN
),
ValidVendorVertical AS (
    SELECT DISTINCT
        vm.vendorcode,
        odv.vertical
    FROM
        VendorCodes vm
        JOIN OrderDetailsWithVertical odv ON odv.vendorcode = vm.vendorcode
),
OrderSums AS (
    SELECT
        vm.vendorcode,
        odv.vertical,
        SUM(CASE
            WHEN o.orderdate >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
                 AND ((odv.productnote NOT LIKE '% | %' AND odv.productnote LIKE CONCAT(vm.vendorcode, ' %'))
                      OR odv.productnote LIKE CONCAT('% | ', vm.vendorcode, ' %'))
            THEN odv.totalprice
            ELSE 0
        END) AS "1_Month",
        SUM(CASE
            WHEN o.orderdate >= DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
                 AND o.orderdate < DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
                 AND ((odv.productnote NOT LIKE '% | %' AND odv.productnote LIKE CONCAT(vm.vendorcode, ' %'))
                      OR odv.productnote LIKE CONCAT('% | ', vm.vendorcode, ' %'))
            THEN odv.totalprice
            ELSE 0
        END) AS "2_Months",
        SUM(CASE
            WHEN o.orderdate >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
                 AND o.orderdate < DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
                 AND ((odv.productnote NOT LIKE '% | %' AND odv.productnote LIKE CONCAT(vm.vendorcode, ' %'))
                      OR odv.productnote LIKE CONCAT('% | ', vm.vendorcode, ' %'))
            THEN odv.totalprice
            ELSE 0
        END) AS "3_Months",
        SUM(CASE
            WHEN o.orderdate >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
                 AND o.orderdate < DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
                 AND ((odv.productnote NOT LIKE '% | %' AND odv.productnote LIKE CONCAT(vm.vendorcode, ' %'))
                      OR odv.productnote LIKE CONCAT('% | ', vm.vendorcode, ' %'))
            THEN odv.totalprice
            ELSE 0
        END) AS "6_Months",
        SUM(CASE
            WHEN o.orderdate >= DATE_SUB(CURDATE(), INTERVAL 9 MONTH)
                 AND o.orderdate < DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
                 AND ((odv.productnote NOT LIKE '% | %' AND odv.productnote LIKE CONCAT(vm.vendorcode, ' %'))
                      OR odv.productnote LIKE CONCAT('% | ', vm.vendorcode, ' %'))
            THEN odv.totalprice
            ELSE 0
        END) AS "9_Months",
        SUM(CASE
            WHEN o.orderdate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
                 AND o.orderdate < DATE_SUB(CURDATE(), INTERVAL 9 MONTH)
                 AND ((odv.productnote NOT LIKE '% | %' AND odv.productnote LIKE CONCAT(vm.vendorcode, ' %'))
                      OR odv.productnote LIKE CONCAT('% | ', vm.vendorcode, ' %'))
            THEN odv.totalprice
            ELSE 0
        END) AS "12_Months"
    FROM
        custopayted.orders o
        JOIN OrderDetailsWithVertical odv ON o.OrderID = odv.OrderID
        JOIN VendorCodes vm ON odv.vendorcode = vm.vendorcode
    WHERE
        o.orderstatus <> 'Cancelled'
        AND (odv.productnote LIKE '%ETA%' OR odv.productnote LIKE '%SPC%')
        AND odv.productnote NOT LIKE '%| %'
        AND LOWER(odv.productnote) NOT LIKE '%(c)%'
        AND LOWER(odv.productnote) NOT LIKE '%(r)%'
        AND odv.qtyonhold > 0
    GROUP BY vm.vendorcode, odv.vertical
)
SELECT
    Vendor,
    Vertical,
    "1 Month",
    "2 Months",
    "3 Months",
    "6 Months",
    "9 Months",
    "12 Months"
FROM (
    SELECT
        vendorcode AS Vendor,
        vertical AS Vertical,
        CONCAT('$', FORMAT("1_Month", 2)) AS "1 Month",
        CONCAT('$', FORMAT("2_Months", 2)) AS "2 Months",
        CONCAT('$', FORMAT("3_Months", 2)) AS "3 Months",
        CONCAT('$', FORMAT("6_Months", 2)) AS "6 Months",
        CONCAT('$', FORMAT("9_Months", 2)) AS "9 Months",
        CONCAT('$', FORMAT("12_Months", 2)) AS "12 Months",
        1 AS sort_order
    FROM OrderSums
    UNION ALL
    SELECT
        'Total' AS Vendor,
        NULL AS Vertical,
        CONCAT('$', FORMAT(SUM("1_Month"), 2)) AS "1 Month",
        CONCAT('$', FORMAT(SUM("2_Months"), 2)) AS "2 Months",
        CONCAT('$', FORMAT(SUM("3_Months"), 2)) AS "3 Months",
        CONCAT('$', FORMAT(SUM("6_Months"), 2)) AS "6 Months",
        CONCAT('$', FORMAT(SUM("9_Months"), 2)) AS "9 Months",
        CONCAT('$', FORMAT(SUM("12_Months"), 2)) AS "12 Months",
        2 AS sort_order
    FROM OrderSums
) AS final_result
ORDER BY sort_order, Vendor, Vertical;