Suggest a feature
×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: bejoy balan
Added: Jan 7, 2022 2:11 PM
Views: 190
  1. INSERT INTO `sales_order_grid` (`csr_name`, `entity_id`, `status`, `store_id`, `store_name`, `customer_id`, `base_grand_total`, `base_total_paid`, `grand_total`, `total_paid`, `increment_id`, `base_currency_code`, `order_currency_code`, `shipping_name`, `billing_name`, `created_at`, `updated_at`, `billing_address`, `shipping_address`, `shipping_information`, `customer_email`, `customer_group`, `subtotal`, `shipping_and_handling`, `customer_name`, `payment_method`, `total_refunded`, `refunded_to_store_credit`) SELECT csr_order_placement.csr_name AS `csr_name`, sales_order.entity_id AS `entity_id`, sales_order.status AS `status`, sales_order.store_id AS `store_id`, sales_order.store_name AS `store_name`, sales_order.customer_id AS `customer_id`, sales_order.base_grand_total AS `base_grand_total`, sales_order.base_total_paid AS `base_total_paid`, sales_order.grand_total AS `grand_total`, sales_order.total_paid AS `total_paid`, sales_order.increment_id AS `increment_id`, sales_order.base_currency_code AS `base_currency_code`, sales_order.order_currency_code AS `order_currency_code`, TRIM(CONCAT_WS(' ', IF(`sales_shipping_address`.`firstname` <> '', `sales_shipping_address`.`firstname`, NULL), IF(`sales_shipping_address`.`lastname` <> '', `sales_shipping_address`.`lastname`, NULL))) AS `shipping_name`, TRIM(CONCAT_WS(' ', IF(`sales_billing_address`.`firstname` <> '', `sales_billing_address`.`firstname`, NULL), IF(`sales_billing_address`.`lastname` <> '', `sales_billing_address`.`lastname`, NULL))) AS `billing_name`, sales_order.created_at AS `created_at`, sales_order.updated_at AS `updated_at`, TRIM(CONCAT_WS(',', IF(`sales_billing_address`.`company` <> '', `sales_billing_address`.`company`, NULL), IF(`sales_billing_address`.`street` <> '', `sales_billing_address`.`street`, NULL), IF(`sales_billing_address`.`city` <> '', `sales_billing_address`.`city`, NULL), IF(`sales_billing_address`.`region` <> '', `sales_billing_address`.`region`, NULL), IF(`sales_billing_address`.`postcode` <> '', `sales_billing_address`.`postcode`, NULL))) AS `billing_address`, TRIM(CONCAT_WS(',', IF(`sales_shipping_address`.`company` <> '', `sales_shipping_address`.`company`, NULL), IF(`sales_shipping_address`.`street` <> '', `sales_shipping_address`.`street`, NULL), IF(`sales_shipping_address`.`city` <> '', `sales_shipping_address`.`city`, NULL), IF(`sales_shipping_address`.`region` <> '', `sales_shipping_address`.`region`, NULL), IF(`sales_shipping_address`.`postcode` <> '', `sales_shipping_address`.`postcode`, NULL))) AS `shipping_address`, sales_order.shipping_description AS `shipping_information`, sales_order.customer_email AS `customer_email`, sales_order.customer_group_id AS `customer_group`, sales_order.base_subtotal AS `subtotal`, sales_order.base_shipping_amount AS `shipping_and_handling`, TRIM(CONCAT_WS(' ', IF(`sales_order`.`customer_firstname` <> '', `sales_order`.`customer_firstname`, NULL), IF(`sales_order`.`customer_lastname` <> '', `sales_order`.`customer_lastname`, NULL))) AS `customer_name`, sales_order_payment.method AS `payment_method`, sales_order.total_refunded AS `total_refunded`, sales_order.customer_bal_total_refunded AS `refunded_to_store_credit` FROM `sales_order`
  2.  LEFT JOIN `csr_order_placement` ON sales_order.entity_id = csr_order_placement.order_id
  3.  LEFT JOIN `sales_order_address` AS `sales_shipping_address` ON sales_order.shipping_address_id = sales_shipping_address.entity_id
  4.  LEFT JOIN `sales_order_address` AS `sales_billing_address` ON sales_order.billing_address_id = sales_billing_address.entity_id
  5.  LEFT JOIN `sales_order_payment` ON sales_order.entity_id = sales_order_payment.parent_id
  6.  LEFT JOIN `sales_order_grid` ON sales_order.entity_id = sales_order_grid.entity_id WHERE (((sales_order.updated_at >= '0000-00-00 00:00:00') OR (sales_order_grid.entity_id IS NOT NULL AND sales_order_grid.status != sales_order.status))) ON DUPLICATE KEY UPDATE `csr_name` = VALUES(`csr_name`), `entity_id` = VALUES(`entity_id`), `status` = VALUES(`status`), `store_id` = VALUES(`store_id`), `store_name` = VALUES(`store_name`), `customer_id` = VALUES(`customer_id`), `base_grand_total` = VALUES(`base_grand_total`), `base_total_paid` = VALUES(`base_total_paid`), `grand_total` = VALUES(`grand_total`), `total_paid` = VALUES(`total_paid`), `increment_id` = VALUES(`increment_id`), `base_currency_code` = VALUES(`base_currency_code`), `order_currency_code` = VALUES(`order_currency_code`), `shipping_name` = VALUES(`shipping_name`), `billing_name` = VALUES(`billing_name`), `created_at` = VALUES(`created_at`), `updated_at` = VALUES(`updated_at`), `billing_address` = VALUES(`billing_address`), `shipping_address` = VALUES(`shipping_address`), `shipping_information` = VALUES(`shipping_information`), `customer_email` = VALUES(`customer_email`), `customer_group` = VALUES(`customer_group`), `subtotal` = VALUES(`subtotal`), `shipping_and_handling` = VALUES(`shipping_and_handling`), `customer_name` = VALUES(`customer_name`), `payment_method` = VALUES(`payment_method`), `total_refunded` = VALUES(`total_refunded`), `refunded_to_store_credit` = VALUES(`refunded_to_store_credit`)