-- Запрос создает новыую поставку и и привязывает к ней товары которое отсутсуют в продписанном документе ЭДО
SET @parent_incomes_id = 10648;
SET @digital_documents_1c_id = '64997bf6-372c-4541-90af-227037a6b451';

SELECT @digital_documents_id := dd.digital_documents_id
  , @parent_incomes_id := dd.objects_id
FROM digital_documents AS dd
WHERE 1 = 1
     -- AND dd.digital_documents_status = 229
    AND dd.entities_id = 41
    AND dd.objects_id = @parent_incomes_id
    AND dd.digital_documents_1c_id = @digital_documents_1c_id
LIMIT 1;


START TRANSACTION;

-- копирование поставки
INSERT INTO incomes (

    `incomes_id`
    , `suppliers_id`
    , `suppliers_contracts_id`
    , `suppliers_contracts_conditions_details_id`
    , `suppliers_contracts_volumes_id`
    , `invoice_exist`
    , `date`
    , `planned_supply_quantity_torg12`
    , `planned_supply_summa_torg12`
    , `customers_id`
    , `incomes_status`
    , `supply_date_planned`
    , `date_request_send`
    , `supply_date`
    , `date_start`
    , `date_end`
    , `photography_links`
    , `our_photography_links`
    , `is_editable_on_marking`
    , `entities_types_id`
    , `last_date_update`
    , `digital_documents_suppliers_settings_id`
    , `categories_id`)

    SELECT

         NULL AS incomes_id
        , i.suppliers_id
        , i.suppliers_contracts_id
        , i.suppliers_contracts_conditions_details_id
        , i.suppliers_contracts_volumes_id
        , i.invoice_exist
        , i.date
        , i.planned_supply_quantity_torg12
        , i.planned_supply_summa_torg12
        , i.customers_id
        , i.incomes_status
        , i.supply_date_planned
        , i.date_request_send
        , i.supply_date
        , i.date_start
        , i.date_end
        , i.photography_links
        , i.our_photography_links
        , i.is_editable_on_marking
        , i.entities_types_id
        , NULL AS last_date_update
        , i.digital_documents_suppliers_settings_id
        , i.categories_id

    FROM incomes AS i
    WHERE i.incomes_id =  @parent_incomes_id;

SET @incomes_id := LAST_INSERT_ID();
SELECT @incomes_id AS incomes_id;

-- Копирование истории статусов

INSERT INTO `incomes_status_history` (
    `incomes_status_history_id`
    , `object_id`
    , `customers_id`
    , `status_id`
    , `date_added`
    , `parent_id`)

SELECT
    NULL AS incomes_status_history_id
    , @incomes_id AS object_id
    , ish.customers_id
    , ish.status_id
    , ish.date_added
    , NULL AS parent_id
FROM incomes_status_history AS ish
WHERE ish.object_id = @parent_incomes_id;

-- Заполнение parent_id в истории статусов
UPDATE  incomes_status_history AS osh
LEFT JOIN (
        SELECT
            ish0.incomes_status_history_id
            , lag(ish0.incomes_status_history_id) OVER (PARTITION BY ish0.object_id ORDER BY ish0.incomes_status_history_id) AS parent_id
        FROM incomes_status_history AS ish0
        WHERE ish0.object_id = @incomes_id
    ) AS t ON osh.incomes_status_history_id = t.incomes_status_history_id
 SET osh.parent_id = t.parent_id
WHERE osh.object_id = @incomes_id
;

-- Смена поставки в ia

UPDATE incomes_attributes AS ia
    JOIN digital_documents_to_objects AS ddo ON ddo.object_id = ia.incomes_attributes_id AND ddo.entities_id = 83
SET ia.incomes_id = @incomes_id
WHERE ddo.digital_documents_id IN(@digital_documents_id)
AND ddo.digital_documents_to_objects_ordered_count > ddo.digital_documents_to_objects_in_document_count
AND ddo.document_row_number < 0;

-- Добавим поставку для обмена с 1С
INSERT INTO export_data_1c (`component`, `object_id`, `date_web`) VALUES (40, @incomes_id, NOW());

COMMIT;