SELECT *,
       (`o`.`total_inc_vat` - `o`.`__total_paid`) AS `total_remaining`,
       `o`.`__total_paid` AS `total_amount_paid`,
       IF((`o`.`total_inc_vat` - `o`.`__total_paid`) = 0, 1, 0) AS `order_fully_paid`,
       IF(`o`.`recurring_id` IS NOT NULL, 1, 0) AS `is_recurring_order` FROM
(
    SELECT *,
            ROUND((`o`.`total` * (`o`.`vat` / 100)), 2) AS `total_vat`,
            ROUND(`o`.`total` + (`o`.`total` * (`o`.`vat` / 100)), 2) AS `total_inc_vat`,
            ROUND(`o`.`real_total` + (`o`.`real_total` * (`o`.`vat` / 100)), 2) AS `total_before_discounts`,
            ROUND(`o`.`total` + (`o`.`total` * (`o`.`vat` / 100)), 2) AS `_total_remaining`,
            SUM(`o`.`trays_left`) AS `tray_count`,
            IF(`o`.`_total_paid` > 0, `o`.`_total_paid`, 0) AS `__total_paid`
    FROM
    (
         SELECT `o`.`order_id`,
                `o`.`order_number`,
                `o`.`customer_id`,
                `o`.`ordered_date`,
                `o`.`delivery_date`,
                `o`.`discount`,
                `o`.`discount_type`,
                `o`.`delivery_notes`,
                `o`.`bakers_notes`,
                `o`.`alt_address`,
                `o`.`invoice_address`,
                `o`.`payment_type`,
                `o`.`additional_charges`,
                `o`.`vat`,
                `o`.`priority`,
                `o`.`trays_left`,
                `os`.`status` AS `order_status`,
                IF(`os`.`status` = 3, 1, 0) AS `has_dispatched`,
                IF(`os`.`status` = 3, `os`.`date_changed`, 0) AS `dispatched_date`,
                `o`.`dispatch_email_sent`,

                IF(`os`.`status` = 2 OR `os`.`status` = 3, 1, 0) AS `is_completed`,
                IF(`os`.`status` = 2 OR `os`.`status` = 3, `os`.`date_changed`, 0) AS `completed_date`,

                `o`.`invoice_last_sent`,
                `o`.`ref_order_number`,
                `o`.`duplicate_order_id`,
                `ro`.`recurring_id`,
                `ro`.`legacy_id` AS `legacy_recurring_id`,
                `ro`.`repeat_date`,
                `ro`.`repeat_day`,
                `ro`.`repeat_week`,
                `ro`.`repeat_month`,
                `ro`.`repeat_from`,
                `ro`.`repeat_till`,
                `ro`.`last_run`,
                `ro`.`next_run`,
                `ro`.`is_finished`,
                `o`.`recurred_from`,
                `o`.`recur_identifier`,
                `o`.`is_deleted`,

                `oi`.`total` AS `real_total`,
                `op`.`_total_paid`,

                IF(`o`.`discount_type` = 2,
                    `oi`.`total` - `o`.`discount`,

                    IF(`o`.`discount_type` = 1,
                        ROUND(`oi`.`total` - ((`oi`.`total` / 100) * `o`.`discount`), 2),
                        `oi`.`total`
                    )
                ) AS `total`,

                IF(`o`.`discount_type` = 2,
                    `o`.`discount`,

                    IF(`o`.`discount_type` = 1,
                        ROUND(((`oi`.`total` / 100) * `o`.`discount`), 2),
                        0
                    )
                ) AS `discounted_amount`,

                `oi`.`total_items`,
                `c`.`first_name`,
                `c`.`last_name`,
                `c`.`business`,
                `c`.`address_1`,
                `c`.`address_2`,
                `c`.`address_3`,
                `c`.`town`,
                `c`.`county`,
                `c`.`postcode`,

                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`first_name`,
                    `c`.`first_name`) AS `delivery_first_name`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`last_name`,
                    `c`.`last_name`) AS `delivery_last_name`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`business`,
                    `c`.`business`) AS `delivery_business`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`address_1`,
                    `c`.`address_1`) AS `delivery_address_1`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`address_2`,
                    `c`.`address_2`) AS `delivery_address_2`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`address_3`,
                    `c`.`address_3`) AS `delivery_address_3`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`town`,
                    `c`.`town`) AS `delivery_town`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`county`,
                    `c`.`county`) AS `delivery_county`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`postcode`,
                    `c`.`postcode`) AS `delivery_postcode`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`email`,
                    `c`.`email`) AS `delivery_email`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`phone`,
                    `c`.`phone`) AS `delivery_phone`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`mobile`,
                    `c`.`mobile`) AS `delivery_mobile`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`latitude`,
                    `c`.`latitude`) AS `delivery_latitude`,
                IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = 'D',
                    `ca`.`longitude`,
                    `c`.`longitude`) AS `delivery_longitude`,

                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`first_name`,
                    `c`.`first_name`) AS `invoice_first_name`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`last_name`,
                    `c`.`last_name`) AS `invoice_last_name`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`business`,
                    `c`.`business`) AS `invoice_business`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`address_1`,
                    `c`.`address_1`) AS `invoice_address_1`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`address_2`,
                    `c`.`address_2`) AS `invoice_address_2`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`address_3`,
                    `c`.`address_3`) AS `invoice_address_3`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`town`,
                    `c`.`town`) AS `invoice_town`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`county`,
                    `c`.`county`) AS `invoice_county`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`postcode`,
                    `c`.`postcode`) AS `invoice_postcode`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`email`,
                    `c`.`email`) AS `invoice_email`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`phone`,
                    `c`.`phone`) AS `invoice_phone`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = 'I',
                    `ca2`.`mobile`,
                    `c`.`mobile`) AS `invoice_mobile`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca`.`address_type` = 'I',
                    `ca`.`latitude`,
                    `c`.`latitude`) AS `invoice_latitude`,
                IF(`o`.`invoice_address` IS NOT NULL AND `ca`.`address_type` = 'I',
                    `ca`.`longitude`,
                    `c`.`longitude`) AS `invoice_longitude`,

                `c`.`sage_account_code`,

                `o`.`retail_order`,
                `c`.`is_deleted` AS `deleted_customer`
     FROM
     (
         `orders` `o`

         INNER JOIN
         (
             SELECT `oi`.`order_id`,
                    `oi`.`total_items`,
                    `oi`.`_total` AS `total`
             FROM
             (
                 SELECT SUM(`oi`.`quantity` * `oi`.`price`) + `o`.`additional_charges` AS `_total`,
                        COUNT(`oi`.`item_id`) AS `total_items`,
                        `oi`.`order_id`,
                        `oi`.`item_id`
                 FROM
                 (
                     `order_items` `oi`

                     INNER JOIN `orders` `o` ON `oi`.`order_id` = `o`.`order_id`
                 )
                 WHERE `oi`.`is_deleted` = 0
                 GROUP BY `oi`.`order_id`
             ) AS `oi`
             GROUP BY `oi`.`order_id`
         ) AS `oi` ON `oi`.`order_id` = `o`.`order_id`

         LEFT OUTER JOIN
         (
             SELECT SUM(`amount`) AS `_total_paid`,
                    `order_id`
             FROM `order_payments`
             WHERE `is_deleted` = 0
             GROUP BY `order_id`
         ) AS `op` ON `op`.`order_id` = `o`.`order_id`

         LEFT OUTER JOIN `customers` `c` ON `c`.`customer_id` = `o`.`customer_id`

         LEFT OUTER JOIN
         (
             SELECT `address_id`,
                    `customer_id`,
                    `address_type`,
                    `first_name`,
                    `last_name`,
                    `business`,
                    `address_1`,
                    `address_2`,
                    `address_3`,
                    `town`,
                    `county`,
                    `postcode`,
                    `email`,
                    `phone`,
                    `mobile`,
                    `latitude`,
                    `longitude`
             FROM `customer_addresses`
         ) AS `ca` ON `ca`.`address_id` = `o`.`alt_address`

         LEFT OUTER JOIN
         (
             SELECT `address_id`,
                    `customer_id`,
                    `address_type`,
                    `first_name`,
                    `last_name`,
                    `business`,
                    `address_1`,
                    `address_2`,
                    `address_3`,
                    `town`,
                    `county`,
                    `postcode`,
                    `email`,
                    `phone`,
                    `mobile`,
                    `latitude`,
                    `longitude`
             FROM `customer_addresses`
         ) AS `ca2` ON `ca2`.`address_id` = `o`.`invoice_address`

         LEFT OUTER JOIN
         (
             SELECT IF(`parent_id` IS NULL, `id`, `parent_id`) AS `recurring_id`,
                    `legacy_id`,
                    `order_id`,
                    GROUP_CONCAT(`repeat_date`) AS `repeat_date`,
                    GROUP_CONCAT(`repeat_day`) AS `repeat_day`,
                    GROUP_CONCAT(`repeat_week`) AS `repeat_week`,
                    GROUP_CONCAT(`repeat_month`) AS `repeat_month`,
                    GROUP_CONCAT(`repeat_from`) AS `repeat_from`,
                    GROUP_CONCAT(`repeat_till`) AS `repeat_till`,
                    GROUP_CONCAT(`last_run`) AS `last_run`,
                    GROUP_CONCAT(`next_run`) AS `next_run`,
                    GROUP_CONCAT(`is_finished`) AS `is_finished`
             FROM `recurring_orders`
             WHERE `is_deleted` = 0
             GROUP BY `order_id`
         ) AS `ro` ON `ro`.`order_id` = `o`.`order_id`

         LEFT OUTER JOIN
         (
             SELECT `os`.`order_id`,
                    `os`.`status`,
                    `os`.`date_changed`
             FROM
             (
                 `order_status` `os`

                 INNER JOIN `status_types` `st` ON `st`.`status_id` = `os`.`status`
             )
             ORDER BY `os`.`date_changed` DESC
         ) AS `os` ON `os`.`order_id` = `o`.`order_id`
     )
     WHERE `o`.`is_deleted` = 0  AND `o`.`retail_order` = 0
  ) AS `o`
  GROUP BY  `o`.`order_id`
) AS `o`
ORDER BY `o`.`ordered_date` DESC
LIMIT 0, 150