Untitled

IF (OBJECT_ID('tempdb..#logistic_rt') IS NOT NULL)
  DROP TABLE #logistic_rt;

IF (OBJECT_ID('tempdb..#detailing_flights_a_day_trip') IS NOT NULL)
  DROP TABLE #detailing_flights_a_day_trip;

IF (OBJECT_ID('tempdb..#detailing_flights_a_day_railway') IS NOT NULL)
  DROP TABLE #detailing_flights_a_day_railway;

IF (OBJECT_ID('tempdb..#detailing_flights_a_day_zakaz_zt') IS NOT NULL)
  DROP TABLE #detailing_flights_a_day_zakaz_zt;

IF (OBJECT_ID('tempdb..#detailing_flights_a_day_zakaz_lm') IS NOT NULL)
  DROP TABLE #detailing_flights_a_day_zakaz_lm;

IF (OBJECT_ID('tempdb..#detailing_flights_a_day_zakaz') IS NOT NULL)
  DROP TABLE #detailing_flights_a_day_zakaz;


DECLARE @DATE datetime = '22.01.2019 00:00:00.000';
DECLARE @filter_id INT = 1;
DECLARE @warehouse_id INT = 4;
DECLARE @todayStart datetime = @DATE;
DECLARE @todayEnd datetime = DATEADD(HOUR, 8, DATEADD(DAY, 1, @DATE));
DECLARE @todayEndCS datetime = DATEADD(DAY, 1, @DATE);
DECLARE @notShippedDateStart datetime = DATEADD(MONTH, -1, @DATE);


DECLARE @contractor_warehouse_id INT = (
  CASE @warehouse_id
    WHEN 0 THEN 14327
    WHEN 1 THEN 14329
    WHEN 2 THEN 14326
    WHEN 3 THEN 14328
    WHEN 4 THEN 18784
    WHEN 5 THEN 19533
    END
  );

DECLARE @table_trip_id AS table_trip_id;
INSERT INTO @table_trip_id
SELECT tct.id
FROM tc_trip tct
WHERE tct.factory_id = 1
  AND tct.TYPE <> 3
  AND tct.TYPE <> 5
  AND (
    (
          tct.date_load_plan_oper IS NOT NULL AND
          (tct.date_load_plan_oper < @todayEnd AND tct.date_load_plan_oper >= @todayStart)
        OR
          tct.date_load_plan_oper IS NULL AND (tct.date_load_plan < @todayEndCS AND tct.date_load_plan >= @todayStart)
      ) OR (
      tct.date_come < @todayEnd AND tct.date_come > @todayStart
      ) OR (
        tct.date_shipped_fact IS NULL AND
        (tct.date_load_plan_oper < @todayEnd AND tct.date_load_plan_oper >= @notShippedDateStart)
      )
    OR (tct.date_shipped_fact IS NULL AND (tct.date_come < @todayEnd AND tct.date_come >= @notShippedDateStart))
  )


SELECT a.trip_id,
       a.warehouse_id,
       a.pallet_id,
       a.korob_count AS pallet_count,
       a.p_brut      AS                               p_brut,
       a.p_net       AS                               p_net,
       a.city_order,
       a.zh_id       AS                               zh_id,
       a.material_id AS                               matertial_id
       INTO #detailing_flights_a_day_zakaz_lm
FROM (
       SELECT tct.trip_id,
              CASE
                WHEN zlw.contractor_id IS NOT NULL THEN 4 -- Логистический
                WHEN cmap.warehouse_id IN (14327) THEN 0 -- ЖМС
                WHEN cmap.warehouse_id IN (14329) THEN 1 -- СЧС
                WHEN cmap.warehouse_id IN (14326) THEN 2 -- СМС
                WHEN cmap.warehouse_id IN (14328) THEN 3 -- ТМ
                WHEN cmap.warehouse_id IN (19533) THEN 5 -- Аромат
                ELSE lcw.warehouse_id
                END                                                                 warehouse_id,
              CASE WHEN zlw.contractor_id IS NOT NULL THEN -1 ELSE h.address_id END city_order,
              lpp.pallet_id,
              lm.kor * cmap.box_gross AS                                            p_brut,
              lm.kor * cmap.box_net   AS                                            p_net,
              lm.kor AS korob_count,
              h.id                    AS                                            zh_id,
              m.id                    AS                                            material_id
       FROM loading_pallet_material lm
              INNER JOIN loading_pallet lp ON lm.pallet_id = lp.id
              INNER JOIN loading_palletz_pallet lpp ON lpp.pallet_id = lp.id
              INNER JOIN loading_palletz_zakaz_hat pz ON pz.pallet_zakaz_id = lpp.pallet_zakaz_id

              JOIN co_material AS m ON m.id = lm.material_id
              INNER JOIN co_material_attr_prod cmap ON lm.material_id = cmap.material_id
              INNER JOIN zakaz_hat h ON h.id = pz.zakaz_id

              INNER JOIN co_material_attr_details det
                         ON det.id = cmap.category AND det.material_attr_id = 21 AND det.factory_id = 1
              LEFT JOIN loading_category_warehouse lcw ON lcw.category_id = det.id
              INNER JOIN loading_palletz_transport pt ON pz.pallet_zakaz_id = pt.id
              INNER JOIN tc_trip_zakaz tcz ON tcz.zakaz_hat_id = h.id
              INNER JOIN @table_trip_id tct ON tct.trip_id = tcz.tc_trip_id
              INNER JOIN dbo.co_contractor_attr_customer ca ON h.saleman_id = ca.distr_id
              LEFT JOIN zakaz_logistic_warehouse zlw ON zlw.contractor_id = ca.contractor_id
     ) a
GROUP BY a.warehouse_id, a.korob_count, a.pallet_id, a.trip_id, a.city_order, a.p_net, a.p_brut, a.warehouse_id, city_order, p_brut, p_net, zh_id,
         material_id


SELECT b.trip_id,
       b.warehouse_id,
       0 AS pallet_id,
       CASE WHEN b.pallet_count = 0 THEN 1 ELSE b.pallet_count END AS pallet_count,
       b.p_brut,
       b.p_net,
       b.city_order,
       b.zh_id,
       b.material_id                                               AS material_id
       INTO #detailing_flights_a_day_zakaz_zt
FROM (
       SELECT a.trip_id,
              a.warehouse_id,
              round(SUM(a.brut) / (
                CASE a.warehouse_id -- средний вес паллеты на складе (рассчитывалось вручную)
                  WHEN 0 THEN 650
                  WHEN 1 THEN 650
                  WHEN 2 THEN 470
                  WHEN 3 THEN 750
                  WHEN 4 THEN 555
                  WHEN 5 THEN 500
                  END
                ), 0)       AS pallet_count,
              a.brut        AS p_brut,
              a.net         AS p_net,
              a.city_order  AS city_order,
              a.zh_id       AS zh_id,
              a.material_id AS material_id
       FROM (
              SELECT tct.trip_id,
                     CASE
                       WHEN zlw.contractor_id IS NOT NULL THEN 4 -- Логистический
                       WHEN cmap.warehouse_id IN (14327) THEN 0 -- ЖМС
                       WHEN cmap.warehouse_id IN (14329) THEN 1 -- СЧС
                       WHEN cmap.warehouse_id IN (14326) THEN 2 -- СМС
                       WHEN cmap.warehouse_id IN (14328) THEN 3 -- ТМ
                       WHEN cmap.warehouse_id IN (19533) THEN 5 -- Аромат
                       ELSE lcw.warehouse_id
                       END                                                                 warehouse_id,
                     zt.kor * cmap.box_gross AS                                            brut,
                     zt.kor * cmap.box_net   AS                                            net,
                     CASE WHEN zlw.contractor_id IS NOT NULL THEN -1 ELSE h.address_id END city_order,
                     h.id                    AS                                            zh_id,
                     m.id                    AS                                            material_id
              FROM @table_trip_id tct
                     JOIN tc_trip_zakaz tcz ON tcz.tc_trip_id = tct.trip_id
                     JOIN zakaz_hat h ON h.id = tcz.zakaz_hat_id
                     JOIN zakaz_tab zt ON zt.id = h.id
                     JOIN co_material m ON m.id = zt.material_id
                     JOIN co_material_attr_prod_db cmap ON cmap.material_id = m.id
                     JOIN co_material_attr_details det
                          ON det.id = cmap.category AND det.material_attr_id = 21 AND det.factory_id = 1
                     LEFT JOIN loading_category_warehouse lcw ON lcw.category_id = det.id
                     LEFT JOIN loading_palletz_zakaz_hat lpzh ON lpzh.zakaz_id = tcz.zakaz_hat_id
                     INNER JOIN dbo.co_contractor_attr_customer ccac ON h.saleman_id = ccac.distr_id
                     LEFT JOIN zakaz_logistic_warehouse zlw ON zlw.contractor_id = ccac.contractor_id
              WHERE lpzh.zakaz_id IS NULL -- не берём рейсы по которым уже есть карта погрузки
            ) a
       GROUP BY a.trip_id, a.warehouse_id, a.city_order, a.brut, a.net, zh_id, material_id
     ) b

SELECT * INTO #detailing_flights_a_day_zakaz
FROM (
       SELECT *
       FROM #detailing_flights_a_day_zakaz_lm
       UNION
       SELECT *
       FROM #detailing_flights_a_day_zakaz_zt
     ) a


DECLARE @info_xml TABLE
                  (
                    trip_id        INT,
                    zakaz_id       INT,
                    saleman_name   VARCHAR(MAX),
                    NAK_ID         VARCHAR(MAX),
                    zayavka        INT,
                    brut           FLOAT,
                    net            FLOAT,
                    korob_count    INT,
                    consignee_name VARCHAR(MAX),
                    shipping_id    VARCHAR(MAX),
                    material_name  VARCHAR(MAX),
                    box_name       VARCHAR(MAX),
                    material_id    VARCHAR(MAX),
                    box_id         VARCHAR(MAX),
                    pogruz_name    VARCHAR(MAX),
                    stack_name     VARCHAR(MAX)
                  );
INSERT INTO @info_xml
SELECT tct.id,
       zh.id,
       cc.name,
       o.NAK_ID,
       zh.num,
       z.p_brut         AS brut,
       z.p_net          AS net,
       zt.kor           AS korob_count,
       cc2.name         AS consignee_name,
       cmap.shipping_id AS shipping_id,
       m.name           AS material_name,
       box.box_name     AS box_name,
       m.id             AS material_id,
       box.box_id,
       pogruz.pogruz_name,
       ps.stack_name
FROM tc_trip tct
       LEFT JOIN loading_points_time lpt ON lpt.trip_id = tct.id AND lpt.point_id = @warehouse_id
       INNER JOIN tc_trip_zakaz tctz ON tct.id = tctz.tc_trip_id
       INNER JOIN zakaz_hat zh ON zh.id = tctz.zakaz_hat_id
       LEFT JOIN zakaz_tab zt ON zt.id = zh.id
       LEFT JOIN zakaz_order_id_arc o ON o.ZAKAZ_ID = zh.id
       INNER JOIN co_contractor_attr_customer ccac ON ccac.distr_id = zh.saleman_id
       INNER JOIN co_contractor cc ON cc.id = ccac.contractor_id
       LEFT JOIN co_material m ON m.id = zt.material_id
       LEFT JOIN co_material_attr_prod_db cmap ON cmap.material_id = m.id
       INNER JOIN @table_trip_id tt ON tt.trip_id = tct.id
       INNER JOIN #detailing_flights_a_day_zakaz z ON z.trip_id = tct.id AND z.zh_id = zh.id AND z.matertial_id = m.id
       LEFT JOIN co_contractor_attr_customer ccac2 ON ccac2.distr_id = zh.consignee_id
       LEFT JOIN co_contractor cc2 ON cc2.id = ccac2.contractor_id
       LEFT JOIN ver_zaivka_prod vzp
                 ON vzp.ver_id = zh.ver_id AND vzp.zaivka_id = zh.zaivka_id AND vzp.material_id = m.id
       LEFT JOIN prod_pogruz pp
                 ON pp.ver_id = vzp.ver_id AND pp.zaivka_id = vzp.zaivka_id AND pp.material_id = vzp.material_id
       LEFT JOIN pogruz ON pogruz.pogruz_id = pp.pogruz_id
       LEFT JOIN box ON box.box_id = pogruz.box_id
       JOIN prod_stacking ps ON ps.stack_id = vzp.stack_id
WHERE (@filter_id = 0)
   OR (@filter_id = 1 AND lpt.end_load IS NOT NULL)
   OR (@filter_id = 2 AND lpt.zakaz_ready IS NOT NULL AND lpt.end_load IS NULL)
   OR (@filter_id = 3 AND lpt.start_load IS NOT NULL AND lpt.end_load IS NULL)
   OR (@filter_id = 4 AND lpt.start_load IS NULL AND tct.date_come IS NOT NULL)
   OR (@filter_id = 5 AND lpt.end_load IS NULL);


SELECT * INTO #logistic_rt
FROM [f_get_logistic_factory_route_by_trips](@table_trip_id)


SELECT tct.id        trip_id,
       rt.warehouse_id,
       i2.saleman_name,
       i2.consignee_name,
       i2.zayavka AS zakaz_hat,
       i2.NAK_ID,
       i2.net,
       i2.brut,
       i2.shipping_id,
       i2.material_name,
       i2.korob_count,
       i2.box_name,
       i2.material_id,
       i2.box_id,
       i2.pogruz_name,
       i2.stack_name
       INTO #detailing_flights_a_day_trip
FROM [nefco].[dbo].[tc_trip] tct
       LEFT JOIN #logistic_rt rt ON rt.trip_id = tct.id
       JOIN @info_xml i2 ON i2.trip_id = tct.id
WHERE warehouse_id = @warehouse_id

GROUP BY tct.id,
         warehouse_id,
         saleman_name,
         consignee_name,
         zayavka,
         NAK_ID,
         net,
         brut,
         shipping_id,
         material_name,
         korob_count,
         box_name,
         material_id,
         box_id,
         pogruz_name,
         stack_name
ORDER BY trip_id, zakaz_hat


SELECT a.trip_id,
       a.warehouse_id,
       a.saleman_name,
       a.consignee_name,
       a.zakaz_hat,
       a.NAK_ID,
       SUM(a.net)  net,
       SUM(a.brut) brut,
       a.shipping_id,
       a.material_name,
       a.korob_count,
       a.box_name,
       a.material_id,
       a.box_id,
       a.pogruz_name,
       a.stack_name

       INTO #detailing_flights_a_day_railway
FROM (
       SELECT tct.id                             AS trip_id,
              h.num                              AS zakaz_hat,
              cmap.shipping_id                   AS shipping_id,
              NAK_ID =
              CAST(REPLACE(STUFF((
                                   SELECT CASE
                                            WHEN zoia.NAK_ID IS NOT NULL THEN (', ' + CAST(zoia.NAK_ID AS VARCHAR(11)))
                                            ELSE '' END,
                                          CASE
                                            WHEN COUNT(*) > 1 THEN '(' + CONVERT(VARCHAR(MAX), COUNT(*)) + ')'
                                            ELSE '' END
                                   FROM zakaz_order_id_arc zoia
                                   WHERE zoia.ZAKAZ_ID = h.id
                                     AND zoia.warehouse_id = @contractor_warehouse_id
                                   GROUP BY zoia.NAK_ID FOR
                                     XML PATH ('')
                                 ), 1, 1, ''), ' ,', ', ') AS VARCHAR(1000)
                ),
              CASE
                WHEN zlw.contractor_id IS NOT NULL THEN 4 -- Логистический
                WHEN cmap.warehouse_id IN (14327) THEN 0 -- ЖМС
                WHEN cmap.warehouse_id IN (14329) THEN 1 -- СЧС
                WHEN cmap.warehouse_id IN (14326) THEN 2 -- СМС
                WHEN cmap.warehouse_id IN (14328) THEN 3 -- ТМ
                WHEN cmap.warehouse_id IN (19533) THEN 5 -- Аромат
                ELSE lcw.warehouse_id
                END                                 warehouse_id,
              zt.kor * cmap.box_gross            AS brut,
              zt.kor * cmap.box_net              AS net,


              m.id                               AS material_id,
              m.name                             AS material_name,
              vzp.ver_id,
              vzp.zaivka_id,
              zt.kor                             AS korob_count,
              ps.stack_name,
              pp.pogruz_id,

              pogruz.pogruz_name,
              pogruz.hkor_norma,                                   -- Кол-во коробов в высоту
              pogruz.row_kor,                                      -- Кол-во коробов в ряду
              pogruz.hkor_norma * pogruz.row_kor AS pallet_korobs, -- Кол-во коробов в паллете
              box.box_id,
              box.box_name,
              cc.name                            AS saleman_name,
              cc2.name                           AS consignee_name
       FROM tc_trip_railways tct
              JOIN tc_trip_railways_zakaz tcz ON tcz.tc_trip_railways_id = tct.id
              JOIN loading_points_time_railways lpt ON lpt.tc_trip_railway_id = tct.id AND lpt.point_id = @warehouse_id
              INNER JOIN zakaz_hat h ON h.id = tcz.zakaz_hat_id AND h.warehouse_id = @warehouse_id
              JOIN zakaz_tab zt ON zt.id = h.id
              JOIN co_material m ON m.id = zt.material_id
              INNER JOIN co_material_attr_prod_db cmap ON cmap.material_id = m.id AND cmap.warehouse_id = @warehouse_id
              JOIN co_material_attr_details det
                   ON det.id = cmap.category AND det.material_attr_id = 21 AND det.factory_id = 1
              LEFT JOIN loading_category_warehouse lcw ON lcw.category_id = det.id AND lcw.warehouse_id = @warehouse_id
              INNER JOIN dbo.co_contractor_attr_customer ccac ON h.saleman_id = ccac.distr_id
              LEFT JOIN zakaz_logistic_warehouse zlw ON zlw.contractor_id = ccac.contractor_id

              JOIN ver_zaivka_prod vzp
                   ON vzp.ver_id = h.ver_id AND vzp.zaivka_id = h.zaivka_id AND vzp.material_id = m.id
              JOIN prod_stacking ps ON ps.stack_id = vzp.stack_id
              JOIN prod_pogruz pp
                   ON pp.ver_id = vzp.ver_id AND pp.zaivka_id = vzp.zaivka_id AND pp.material_id = vzp.material_id
              JOIN pogruz ON pogruz.pogruz_id = pp.pogruz_id
              JOIN box ON box.box_id = pogruz.box_id
              LEFT JOIN co_contractor cc ON cc.id = ccac.contractor_id
              LEFT JOIN co_contractor_attr_customer ccac2 ON ccac2.distr_id = h.consignee_id
              LEFT JOIN co_contractor cc2 ON cc2.id = ccac2.contractor_id
       WHERE ((CAST(tct.date_load_plan AS DATE) = @DATE AND
               (tct.transp_company IS NOT NULL AND tct.transp_company IS NULL)))
          OR (CAST(tct.date_load_plan AS DATE) = DATEADD(DAY, -1, @DATE))
          OR (CAST(tct.date_load_plan AS DATE) > DATEADD(DAY, -7, @DATE))
         AND (tct.transp_company IS NOT NULL)
         AND (tct.date_shipped IS NULL OR @DATE < @DATE)
         AND (
                 (@filter_id = 0)
                 OR
                 (@filter_id = 1 AND lpt.end_load IS NOT NULL)
                 OR
                 (@filter_id = 2 AND lpt.zakaz_ready IS NOT NULL AND lpt.end_load IS NULL)
                 OR
                 (@filter_id = 3 AND lpt.start_load IS NOT NULL AND lpt.end_load IS NULL)
                 OR
                 (@filter_id = 4 AND lpt.start_load IS NULL AND tct.date_shipped IS NULL AND tct.date_fact IS NOT NULL)
                 OR
                 (@filter_id = 5 AND lpt.end_load IS NULL)
               )
     ) a
WHERE a.warehouse_id = @warehouse_id
GROUP BY a.trip_id,
         a.warehouse_id,
         a.saleman_name,
         a.consignee_name,
         a.zakaz_hat,
         a.NAK_ID,
         net,
         brut,
         a.shipping_id,
         a.material_name,
         a.korob_count,
         a.box_name,
         a.material_id,
         a.box_id,
         a.pogruz_name,
         a.stack_name
ORDER BY trip_id, zakaz_hat


SELECT *
FROM #detailing_flights_a_day_trip
UNION
SELECT *
FROM #detailing_flights_a_day_railway;