orders_products_2

CREATE OR REPLACE ALGORITHM = MERGE VIEW orders_products_categories_view_1c AS
-- EXPLAIN
-- Товары по акции
SELECT
  opn.id AS id
, NULL 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
, IF(opl.object_id IS NOT NULL AND opl.orders_products_losses_status = 1, 1, 0) 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(opu.orders_products_id IS NULL, 0, 1) AS is_undelivered
, IF(opd.defects_binds_id IN (199,200,201),0, IF(opu.orders_products_id IS NULL, IF(opn.is_deleted, 0, IF(opd.defects_binds_id IS NULL, opn.in_stock, 0)), 0)) AS is_sold
, 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
, opn.products_id AS products_id
, opn.products_id AS current_products_id
, opn.products_attributes_id AS products_attributes_id
, opn.products_attributes_id AS current_products_attributes_id
, opn.products_price AS products_price
, pp.products_purchase_price AS products_price_purchase
, pp.products_VAT AS products_price_vat
, opn.products_categories_id AS categories_id
, opn.products_categories_id AS current_categories_id
, p.entities_types_id
, NULL AS incomes_attributes_id -- Статичный товар
, NULL AS incomes_id -- Поставка статичного товара
, opn.orders_products_id AS orders_products_id
, opn.INDEX

, IFNULL(opt.noncash,0)             AS noncash
, IFNULL(opt.credit,0)              AS credit
, IFNULL(opt.discount,0)            AS discount
, IFNULL(opt.compensation,0)        AS compensation
, IFNULL(opt.shipping,0)            AS shipping
, IFNULL(opt.shipping_original,0) AS shipping_original
, IFNULL(opt.shipping_empty,0)  AS shipping_empty
, IFNULL(opt.service_cost,0)        AS service_cost
, IFNULL(opt.is_global_sale, 0)     AS is_global_sale

, IF(fop.fulfilments_outgoing_packages_id is null,0,1) AS is_turkish

, IFNULL(cs.agency_contract, 'N') = 'Y' AS is_agency
, c.suppliers_id                                 AS suppliers_id
, c.suppliers_contracts_id               AS suppliers_contracts_id

, opn.op_date_added AS date_added
, opn.date_deleted AS date_deleted
, opn.last_date_update AS last_date_update

, cus.sites_id as sites_id

FROM orders_products_new AS opn
    JOIN products as p on p.products_id = opn.products_id AND p.entities_types_id = 46 -- Обычный товар
    LEFT JOIN orders_products_total AS opt ON opt.orders_products_id = opn.id
    JOIN categories AS c ON opn.products_categories_id = c.categories_id
    JOIN suppliers_contracts AS cs ON cs.suppliers_contracts_id = c.suppliers_contracts_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 incomes_attributes AS ia ON ia.products_attributes_id = opn.products_attributes_id and opt.is_global_sale = 0
    -- Складская акция
    LEFT JOIN orders_products_stock_binds AS opsb_x ON opsb_x.orders_products_id = opn.id AND c.categories_types_id IN (2, 3)

    LEFT JOIN orders_products_losses AS opl ON opn.id = opl.object_id and opl.entities_id = 33

    LEFT JOIN products_prices AS pp ON pp.products_attributes_id = opn.products_attributes_id

    LEFT JOIN orders_products_undelivered AS opu ON opu.orders_products_id = opn.id AND opu.is_deleted = 0

    -- Товары с coolprice

    join orders as o on o.orders_id = opn.orders_id
    LEFT JOIN customers as cus on cus.customers_id = o.customers_id

    LEFT JOIN fulfilments_income_packages AS fip ON fip.orders_products_id = opn.id
    LEFT JOIN fulfilments_outgoing_packages as fop on fop.fulfilments_outgoing_packages_id = fip.fulfilments_outgoing_packages_id and fop.fulfilments_id = 3

WHERE opsb_x.orders_products_stock_bind_id IS NULL
    AND ia.incomes_attributes_id IS NULL
    AND fop.fulfilments_outgoing_packages_id IS NULL -- is_turkish
;

CREATE OR REPLACE ALGORITHM = MERGE VIEW orders_products_incomes_view_1c AS
-- EXPLAIN
-- Товары по предпоставке
SELECT
  opn.id AS id
, NULL AS id_old -- Только для складских акций и перепродажи
, opn.orders_id AS orders_id
, opn.is_deleted AS is_deleted
, opn.in_supply AS in_supply
, IF(iis.incomes_items_sold_id IS NULL, 0, 1) AS in_stock
, opn.is_return AS is_return
, IF(opl.object_id IS NOT NULL AND opl.orders_products_losses_status = 1, 1, 0) AS is_lost
  -- 199 - Недостача по заказу ,200, 201 - недостача по ТОРГ-12
, IF(opd.defects_binds_id IN (199,200,201),0, IF(opd.defects_binds_id IS NULL, IF(ia.incomes_id IS NULL,IF(iis.incomes_items_sold_id IS NULL, 0, 1), 0), 1)) AS is_stock
, IF(opu.orders_products_id IS NULL, 0, 1) AS is_undelivered
, IF(opd.defects_binds_id IN (199,200,201),0, IF(opu.orders_products_id IS NULL, IF(opd.defects_binds_id IS NULL, IF(iis.incomes_items_sold_id IS NULL, 0, 1), 1), 0)) AS is_sold
, 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, IF(iis.incomes_items_sold_id IS NULL, 0, 1), 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
, opn.products_id AS products_id
, opn.products_id AS current_products_id
, opn.products_attributes_id AS products_attributes_id
, opn.products_attributes_id AS current_products_attributes_id
, opn.products_price AS products_price
, ia.price_purchase AS products_price_purchase
, ia.vat AS products_price_vat
, opn.products_categories_id AS categories_id
, opn.products_categories_id AS current_categories_id
, p.entities_types_id
, ia.incomes_attributes_id -- Статичный товар
, ia.incomes_id -- Поставка статичного товара

, opn.orders_products_id AS orders_products_id
, opn.INDEX

, IFNULL(opt.noncash,0) AS noncash
, IFNULL(opt.credit,0) AS credit
, IFNULL(opt.discount,0) AS discount
, IFNULL(opt.compensation,0) AS compensation
, IFNULL(opt.shipping,0) AS shipping
, IFNULL(opt.shipping_original,0) AS shipping_original
, IFNULL(opt.shipping_empty,0) AS shipping_empty
, IFNULL(opt.service_cost,0) AS service_cost
, IFNULL(opt.is_global_sale, 0) AS is_global_sale

, IF(fop.fulfilments_outgoing_packages_id is null,0,1) AS is_turkish

, IFNULL(sc.agency_contract, 'N') = 'Y' AS is_agency
, i.suppliers_id AS suppliers_id
, i.suppliers_contracts_id AS suppliers_contracts_id
, opn.op_date_added AS date_added
, opn.date_deleted AS date_deleted
, opn.last_date_update AS last_date_update

FROM orders_products_new AS opn
    JOIN products AS p ON p.products_id = opn.products_id AND p.entities_types_id = 47 -- Статичный товар
    LEFT JOIN orders_products_total AS opt ON opt.orders_products_id = opn.id
    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
    -- Статичный товар
    JOIN orders_products_to_incomes_attributes AS optia ON optia.orders_products_id = opn.id
    JOIN incomes_attributes AS ia ON ia.incomes_attributes_id = optia.incomes_attributes_id
    JOIN incomes AS i ON i.incomes_id = ia.incomes_id
    JOIN suppliers_contracts AS sc ON sc.suppliers_contracts_id = i.suppliers_contracts_id

    LEFT JOIN incomes_items_sold AS iis ON iis.orders_products_id = opn.id AND iis.was_in_order = 1

    LEFT JOIN orders_products_stock_binds AS opsb_x ON opsb_x.orders_products_id = opn.id AND c.categories_types_id IN(2,3)

    LEFT JOIN orders_products_losses AS opl ON opn.id = opl.object_id and opl.entities_id = 46

    LEFT JOIN products_prices AS pp ON pp.products_attributes_id = opn.products_attributes_id

    LEFT JOIN orders_products_undelivered AS opu ON opu.orders_products_id = opn.id AND opu.is_deleted = 0

    LEFT JOIN fulfilments_income_packages AS fip ON fip.orders_products_id = opn.id
    LEFT JOIN fulfilments_outgoing_packages as fop on fop.fulfilments_outgoing_packages_id = fip.fulfilments_outgoing_packages_id and fop.fulfilments_id = 3

WHERE opsb_x.orders_products_stock_bind_id IS NULL
    AND fop.fulfilments_outgoing_packages_id IS NULL -- is_turkish
;

CREATE OR REPLACE ALGORITHM = MERGE VIEW orders_products_stock_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
, IF(opl.object_id IS NOT NULL AND opl.orders_products_losses_status = 1, 1, 0) 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(opu.orders_products_id IS NULL, 0, 1) AS is_undelivered
, IF(opd.defects_binds_id IN (199,200,201),0, IF(opu.orders_products_id IS NULL, IF(opd.defects_binds_id IS NULL, opn.in_stock, 1), 0)) AS is_sold
, 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
, opn_1.products_id AS products_id
, opn.products_id AS current_products_id
, opn_1.products_attributes_id AS products_attributes_id
, opn.products_attributes_id AS current_products_attributes_id
, opn.products_price AS products_price
, pp.products_purchase_price AS products_price_purchase
, pp.products_VAT AS products_price_vat
, opn_1.products_categories_id AS products_categories_id
, opn.products_categories_id AS categories_id

, p.entities_types_id
, NULL AS incomes_attributes_id -- Статичный товар
, NULL AS incomes_id -- Поставка статичного товара
, opn.orders_products_id AS orders_products_id
, opn.INDEX

, IFNULL(opt.noncash,0)             AS noncash
, IFNULL(opt.credit,0)              AS credit
, IFNULL(opt.discount,0)            AS discount
, IFNULL(opt.compensation,0)        AS compensation
, IFNULL(opt.shipping,0)            AS shipping
, IFNULL(opt.shipping_original,0) AS shipping_original
, IFNULL(opt.shipping_empty,0)  AS shipping_empty
, IFNULL(opt.service_cost,0)        AS service_cost
, IFNULL(opt.is_global_sale, 0)     AS is_global_sale

-- , IF(fop.fulfilments_outgoing_packages_id is null,0,1) AS is_turkish

, IFNULL(sc.agency_contract, 'N') = 'Y' AS is_agency
, c.suppliers_id                                 AS suppliers_id
, c.suppliers_contracts_id               AS suppliers_contracts_id

, opn.op_date_added AS date_added
, opn.date_deleted AS date_deleted
, opn.last_date_update AS last_date_update

FROM orders_products_new opn
    JOIN products AS p ON p.products_id = opn.products_id AND p.entities_types_id = 46 -- Обычный товар
    LEFT JOIN orders_products_total AS opt ON opt.orders_products_id = opn.id
    JOIN categories AS cc ON opn.products_categories_id = cc.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
    -- Складская акция
    JOIN orders_products_stock_binds AS opsb_x ON opsb_x.orders_products_id = opn.id  AND cc.categories_types_id IN(2,3)
    JOIN orders_products_stock_binds AS opsb_1 ON opsb_1.orders_products_stock_bind_id = opsb_x.orders_products_stock_bind_min_id
    JOIN orders_products_new AS opn_1 ON opn_1.id = opsb_1.old_orders_products_id
    JOIN categories AS c ON opn_1.products_categories_id = c.categories_id
    LEFT JOIN suppliers_contracts AS sc ON sc.suppliers_contracts_id = c.suppliers_contracts_id
    LEFT JOIN products_prices AS pp ON pp.products_attributes_id = opn_1.products_attributes_id
    LEFT JOIN orders_products_losses AS opl ON opn.id = opl.object_id
    LEFT JOIN orders_products_undelivered AS opu ON opu.orders_products_id = opn.id AND opu.is_deleted = 0
    LEFT JOIN fulfilments_income_packages AS fip ON fip.orders_products_id = opn.id
    LEFT JOIN fulfilments_outgoing_packages as fop on fop.fulfilments_outgoing_packages_id = fip.fulfilments_outgoing_packages_id and fop.fulfilments_id = 3

WHERE fop.fulfilments_outgoing_packages_id IS NULL -- is_turkish
;

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
, IF(i.incomes_id IS NULL, opn.in_stock, IF(iis.incomes_items_sold_id IS NULL, 0, 1)) AS in_stock
, opn.is_return AS is_return
, IF(opl.object_id IS NOT NULL AND opl.orders_products_losses_status = 1, 1, 0) AS is_lost
  -- 199 - Недостача по заказу ,200, 201 - недостача по ТОРГ-12
, IF(opd.defects_binds_id IN (199,200,201),0, IF(opd.defects_binds_id IS NULL, IF(ia.incomes_id IS NULL,opn.in_stock, 0), 1)) AS is_stock
, IF(opu.orders_products_id IS NULL, 0, 1) AS is_undelivered
, IF(opd.defects_binds_id IN (199,200,201),0, IF(opu.orders_products_id IS NULL, IF(opd.defects_binds_id IS NULL, IF(i.incomes_id IS NULL, opn.in_stock, IF(iis.incomes_items_sold_id IS NULL, 0, 1)), 1), 0)) AS is_sold
, 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, IF(i.incomes_id IS NULL, opn.in_stock, IF(iis.incomes_items_sold_id IS NULL, 0, 1)), 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
, IF(i.incomes_id IS NULL, pp.products_purchase_price, ia.price_purchase) AS products_price_purchase
, IF(i.incomes_id IS NULL, pp.products_VAT, ia.vat) AS products_price_vat
, IFNULL(opn_1.products_categories_id,opn.products_categories_id) AS categories_id
, opn.products_categories_id AS current_categories_id
, ia.incomes_attributes_id -- Статичный товар
, ia.incomes_id -- Поставка статичного товара
, opn.orders_products_id AS orders_products_id
, opn.INDEX

, IFNULL(opt.noncash,0) AS noncash
, IFNULL(opt.credit,0) AS credit
, IFNULL(opt.discount,0) AS discount
, IFNULL(opt.compensation,0) AS compensation
, IFNULL(opt.shipping,0) AS shipping
, IFNULL(opt.shipping_original,0) AS shipping_original
, IFNULL(opt.shipping_empty,0) AS shipping_empty
, IFNULL(opt.service_cost,0) AS service_cost
, IFNULL(opt.is_global_sale, 0) AS is_global_sale

, IF(ia.incomes_id IS NULL, IFNULL(csc.agency_contract, 'N'), IFNULL(isc.agency_contract, 'N')) = 'Y' AS is_agency
, IF(i.incomes_id IS NULL, c.suppliers_id,  i.suppliers_id) AS suppliers_id
, IF(i.incomes_id IS NULL, c.suppliers_contracts_id, i.suppliers_contracts_id) AS suppliers_contracts_id
-- , IF(fop.fulfilments_outgoing_packages_id is null,0,1) AS is_turkish
, opn.op_date_added AS date_added
, opn.date_deleted AS date_deleted
, opn.last_date_update AS last_date_update

, IFNULL(cus.sites_id,1) as sites_id
FROM orders_products_new AS opn
    LEFT JOIN orders_products_total AS opt ON opt.orders_products_id = opn.id
    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_to_incomes_attributes AS optia ON optia.orders_products_id = opn.id
    LEFT JOIN incomes_attributes AS ia ON ia.incomes_attributes_id = optia.incomes_attributes_id
    LEFT JOIN incomes AS i ON i.incomes_id = ia.incomes_id
    LEFT JOIN suppliers_contracts AS sc ON sc.suppliers_contracts_id = i.suppliers_contracts_id
    LEFT JOIN incomes_items_sold AS iis ON iis.orders_products_id = opn.id AND iis.was_in_order = 1

    LEFT JOIN orders_products_stock_binds AS opsb_x ON opsb_x.orders_products_id = opn.id AND c.categories_types_id IN(2, 3)
    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
    LEFT JOIN orders_products_losses AS opl ON opn.id = opl.object_id
    LEFT JOIN categories AS cc ON IFNULL(opn_1.products_categories_id,opn.products_categories_id) = cc.categories_id
    LEFT JOIN suppliers_contracts AS isc ON isc.suppliers_contracts_id = i.suppliers_contracts_id
    LEFT JOIN suppliers_contracts AS csc ON csc.suppliers_contracts_id = cc.suppliers_contracts_id

    LEFT JOIN products_prices AS pp ON pp.products_attributes_id = opn.products_attributes_id

    LEFT JOIN orders_products_undelivered AS opu ON opu.orders_products_id = opn.id AND opu.is_deleted = 0

    join orders as o on o.orders_id = opn.orders_id
    LEFT JOIN customers as cus on cus.customers_id = o.customers_id

    LEFT JOIN fulfilments_income_packages AS fip ON fip.orders_products_id = opn.id
    LEFT JOIN fulfilments_outgoing_packages as fop on fop.fulfilments_outgoing_packages_id = fip.fulfilments_outgoing_packages_id and fop.fulfilments_id = 3
WHERE fop.fulfilments_outgoing_packages_id IS NULL -- is_turkish
;