orders_products_view_1c

CREATE OR REPLACE ALGORITHM = MERGE
VIEW orders_products_view_1c AS
-- explain
SELECT opn.id AS id
,opn_1.id AS id_old
,opn.orders_id AS orders_id
,opn.is_deleted AS is_deleted
,opn.in_supply AS in_supply
,opn.in_stock AS in_stock
,opn.is_return AS is_return
,opn.is_lost AS is_lost
-- 199 - Недостача по заказу ,200, 201 - недостача по ТОРГ-12
,IF(opd.defects_binds_id IN (199,200,201),0, IF(opd.defects_binds_id IS NULL, opn.in_stock, 1)) AS is_stock
,IF(opd.defects_binds_id IN (199,200,201),1, 0) AS is_shortage
,IF(opd.defects_binds_id IS NULL, 0, IF(opd.defects_binds_id IN (199,200,201), 0, IF(IFNULL(dm.defects_mistakes_status, 2) = 2,1,0))) AS is_defects
,IF(opd.defects_binds_id IS NULL, opn.in_stock, 1) AS is_marking
,IF(IFNULL(dm.defects_mistakes_status, 2) = 2,opd.defects_binds_id, NULL) AS defects_binds_id
,IF(opd.defects_binds_id IS NOT NULL AND IFNULL(dm.defects_mistakes_status, 2) = 2,opd.defect_source, NULL) AS defect_source
,IF(opd.defects_binds_id IS NOT NULL AND IFNULL(dm.defects_mistakes_status, 2) = 2,d.defects_name, NULL) AS defects_name
,IFNULL(opn_1.products_id,opn.products_id) AS products_id
,opn.products_id AS current_products_id
,IFNULL(opn_1.products_attributes_id,opn.products_attributes_id) AS products_attributes_id
,opn.products_attributes_id AS current_products_attributes_id
,opn.products_price AS products_price
,IFNULL(opn_1.products_categories_id,opn.products_categories_id) AS categories_id
,opn.products_categories_id AS current_categories_id
,opn.orders_products_id AS orders_products_id
,opn.INDEX

FROM orders_products_new AS opn
LEFT JOIN categories AS c ON opn.products_categories_id = c.categories_id
LEFT JOIN orders_products_defects AS opd ON opd.orders_products_id = opn.id AND opd.is_deleted = 'N'
LEFT JOIN defects_mistakes AS dm ON opd.orders_products_defects_id = dm.orders_products_defects_id
LEFT JOIN defects_binds AS db ON db.defects_binds_id = opd.defects_binds_id
LEFT JOIN defects AS d ON db.defects_id = d.defects_id


LEFT JOIN orders_products_stock_binds AS opsb_x ON opsb_x.orders_products_id = opn.id  AND c.is_stock = 1
LEFT JOIN orders_products_stock_binds AS opsb_1 ON opsb_1.orders_products_stock_bind_id = opsb_x.orders_products_stock_bind_min_id
LEFT JOIN orders_products_new AS opn_1 ON opn_1.id = opsb_1.old_orders_products_id