--liquibase formatted sql

--changeset lewis.stone:creating-weight-report-view --runOnChange:true
DROP VIEW IF EXISTS weight_report;
DROP VIEW IF EXISTS weight_report_view;
CREATE VIEW `weight_report_view` AS
    SELECT
        `o`.`id` AS `pw_number`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.quote.carrier_name')) AS `carrier_name`,
        `o`.`organization_id` AS `organization_id`,
        `org`.`name` AS `org_name`,
        `o`.`pro_number` AS `pro_number`,
        `i`.`id` AS `invoice_id`,
        `i`.`arActual` AS `actual_total_ar`,
        DATE_FORMAT(`o`.`delivery_date`,'%Y-%m-%d') AS `delivery_date`,
        COALESCE((SELECT
                        SUM(`invoice_items`.`actual_ar`)
                    FROM
                        `invoice_items`
                    WHERE
                        ((`invoice_items`.`invoice_id` = `i`.`id`)
                            AND (`invoice_items`.`category` = 'base_freight'))),
                0) AS `actual_base_freight_ar`,
        COALESCE((SELECT
                        SUM(`invoice_items`.`actual_ar`)
                    FROM
                        `invoice_items`
                    WHERE
                        ((`invoice_items`.`invoice_id` = `i`.`id`)
                            AND (`invoice_items`.`category` = 'fuel_surcharge'))),
                0) AS `actual_fuel_surcharge_ar`,
        COALESCE((SELECT
                        SUM(`invoice_items`.`actual_ar`)
                    FROM
                        `invoice_items`
                    WHERE
                        ((`invoice_items`.`invoice_id` = `i`.`id`)
                            AND (`invoice_items`.`category` = 'insurance'))),
                0) AS `actual_insurance_ar`,
        COALESCE((SELECT
                        SUM(`invoice_items`.`actual_ar`)
                    FROM
                        `invoice_items`
                    WHERE
                        ((`invoice_items`.`invoice_id` = `i`.`id`)
                            AND (`invoice_items`.`category` = 'brokerage'))),
                0) AS `actual_brokerage_ar`,
        COALESCE((SELECT
                        SUM(`invoice_items`.`actual_ar`)
                    FROM
                        `invoice_items`
                    WHERE
                        ((`invoice_items`.`invoice_id` = `i`.`id`)
                            AND (`invoice_items`.`category` = 'accessorial'))),
                0) AS `actual_accessorial_ar`
    FROM
        (((`orders` `o`
        JOIN `invoices` `i` ON (((`i`.`swNumber` = `o`.`id`)
            AND (`i`.`is_all_in_quickbooks` = 1))))
        JOIN `ordersrr` `orr` ON (((`o`.`id` = `orr`.`order_id`)
            AND (`orr`.`type` = 2))))
        JOIN `organization` `org` ON ((`o`.`organization_id` = `org`.`id`)))
