Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Запрос создает новыую поставку и и привязывает к ней товары которое отсутсуют в продписанном документе ЭДО
- SET @parent_incomes_id = 10647;
- SET @digital_documents_1c_id = '30bb3e49-575f-4d54-9064-2c05960feb93';
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement