Untitled

SET DATEFORMAT ymd;
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;


DECLARE @DATE datetime = '2019-01-18 00:00:00.000';
DECLARE @filter_id INT = 5;
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))
  )


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,
       zt.kor * cmap.box_gross AS brut,
       zt.kor * cmap.box_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
       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
       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;


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
       LEFT JOIN loading_points_time lpt ON lpt.trip_id = tct.id AND lpt.point_id = rt.warehouse_id
       JOIN @info_xml i2 ON i2.trip_id = tct.id
WHERE rt.warehouse_id = @warehouse_id
  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_come IS NOT NULL)
    OR
    (@filter_id = 5 AND lpt.end_load IS NULL)
  )
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
              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
              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;