ALTER PROCEDURE [dbo].[raport_palety] AS BEGIN set nocount on; create table #tmp_raport_palety ( ID_ int , Sekcja varchar(100) collate database_default , ID_TRANSPORT int , NR_TRANSPORTU varchar(100) collate database_default , ZAMOWIENIE varchar(100) collate database_default , ILOSC_PALET_DOKUMENT int , ILOSC_SEKCJI int , OSTATNIA_SEKCJA_LIN_D int , SUMA_WAGA_PALET int , WAGA_PALETY int , DATA_ODBIORU varchar(10) collate database_default , DATA_DOSTARCZENIA varchar(10) collate database_default , DATA_BIEZACA varchar(10) collate database_default , GODZINA_BIEZACA varchar(10) collate database_default , NAZWA_ODBIORCY varchar(100) collate database_default , MIASTO_ODBIORCY varchar(100) collate database_default , KRAJ_DOSTAWCY varchar(100) collate database_default , NR_DOSTAWCY int , KRAJ_DOCELOWY varchar(100) collate database_default , NR_ODBIORCY nvarchar(100) collate database_default , NR_PALETY nvarchar(100) collate database_default ) INSERT #tmp_raport_palety (ID_, Sekcja, ID_TRANSPORT, NR_TRANSPORTU, ZAMOWIENIE,ILOSC_PALET_DOKUMENT, ILOSC_SEKCJI, OSTATNIA_SEKCJA_LIN_D, SUMA_WAGA_PALET, WAGA_PALETY, DATA_ODBIORU, DATA_DOSTARCZENIA, DATA_BIEZACA, GODZINA_BIEZACA, NAZWA_ODBIORCY, MIASTO_ODBIORCY, KRAJ_DOSTAWCY, NR_DOSTAWCY, KRAJ_DOCELOWY, NR_ODBIORCY, NR_PALETY) SELECT [ID_] = ROW_NUMBER() OVER( ORDER BY TR_ID ), [Sekcja] = 'LIN_B', [ID_TRANSPORT] = TR_ID, [NR_TRANSPORTU] = max(TR_NUMBER), [ZAMOWIENIE] = max(doc.DOC_NUMBER), [ILOSC_PALET_DOKUMENT] = isnull(case when TR_DD_STATUS in (3,104) then ceiling(sum(isnull(docd.DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(docd.DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(docd.DOCD_HALF_PALLET_QTY,0)) + sum(isnull(docd.DOCD_PACKAGE_QTY,0))) else isnull(convert(int,round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0)), 0)end ,0), [ILOSC_SEKCJI] = isnull(case when TR_DD_STATUS in (3,104) then (case when (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) = 0 then 0 when (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) < 1 then 1 when (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) > 1 and (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) < 2 then 2 when (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) > 2 and (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) < 3 then 3 when (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) > 3 and (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) < 4 then 4 when (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) > 4 and (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) < 5 then 5 when (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) > 5 and (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) < 6 then 6 when (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) > 6 and (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0))) / 9) < 7 then 7 else (ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0)) + sum(isnull(DOCD_CUSTOM_PALLET_QTY,0)) + sum(isnull(DOCD_HALF_PALLET_QTY,0)) + sum(isnull(DOCD_PACKAGE_QTY,0)))) / 9 end) else isnull(convert(int,case when (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) = 0 then 0 when (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) < 1 then 1 when (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) > 1 and (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) < 2 then 2 when (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) > 2 and (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) < 3 then 3 when (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) > 3 and (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) < 4 then 4 when (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) > 4 and (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) < 5 then 5 when (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) > 5 and (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0) / 9) < 6 then 6 else (round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0)) / 9 end),0)end,0), [OSTATNIA_SEKCJA_LIN_D] = isnull(case when TR_DD_STATUS in (3,104) then ceiling(sum(isnull(DOCD_STANDARD_PALLET_QTY,0) + isnull(DOCD_CUSTOM_PALLET_QTY,0) + isnull(DOCD_HALF_PALLET_QTY,0) + isnull(DOCD_PACKAGE_QTY,0))) % 9 else isnull(convert(int,round(sum(doc.DOC_PALLET_QTY) + sum(doc.DOC_PACKAGE_QTY),0)), 0) % 9 end ,0), [SUMA_WAGA_PALET] = isnull(convert(int,round(sum(sumwagapal.SUMA_WAGA_PALET),0)),0), [WAGA_PALETY] = CAST(np_wp.WAGA_PALETY AS decimal (10,3)), [DATA_ODBIORU] = max(LEFT(ISNULL(CONVERT(VARCHAR,plad.PLAD_TIME_FROM,112),''),8)), [DATA_DOSTARCZENIA] = max(CONVERT (varchar,doc.DOC_DELIVERY_PLAN_DATE,112)), [DATA_BIEZACA] = max(replace(convert(varchar(10), GETDATE(), 120),'-','')), [GODZINA_BIEZACA] = max(LEFT(replace(convert(TIME(0), GETDATE(), 120),':',''), 4)), [NAZWA_ODBIORCY] = max(case when sklepnr.ADDR_NAME like '%(%)%' then (SUBSTRING (sklepnr.ADDR_NAME, CHARINDEX ('(',sklepnr.ADDR_NAME)+1,((CHARINDEX (')',sklepnr.ADDR_NAME)) - (CHARINDEX ('(',sklepnr.ADDR_NAME)))-1)) else '' end), [MIASTO_ODBIORCY] = max(doc.DOC_CUST_REC_CITY), [KRAJ_DOSTAWCY] = max(supkraj.KRAJ_DOSTAWCY), [NR_DOSTAWCY] = max(supnr.NR_DOSTAWCY), [KRAJ_DOCELOWY] = max(sklepkraj.KRAJ_DOCELOWY), [NR_ODBIORCY] = max(doc.DOC_CUST_GLN), [NR_PALETY] = max(isnull(np_wp.NR_PALETY,'')) FROM TRANSPORT with (nolock) join TRANSPORT_DETAIL with (nolock) on TRD_TR = TR_ID outer apply( select top 1 DOC_ID, DOC_NUMBER, DOC_CUST_GLN, DOC_PACKAGE_QTY, DOC_ADDR_ORG_DST, DOC_PALLET_QTY, DOC_TOTAL_WEIGHT, DOC_DELIVERY_PLAN_DATE, DOC_CUST_REC_CITY, DOC_ADDR_TARGET, DOC_ORG_DST, DOC_DATE_C from DOCUMENT with (nolock) where DOC_ID = TRD_DOC )doc outer apply( select DOCD_STANDARD_PALLET_QTY = sum(ISNULL(DOCD_STANDARD_PALLET_QTY ,0)), DOCD_CUSTOM_PALLET_QTY = sum(ISNULL(DOCD_CUSTOM_PALLET_QTY, 0)), DOCD_PACKAGE_QTY = sum(ISNULL(DOCD_PACKAGE_QTY,0)), DOCD_HALF_PALLET_QTY = sum(isnull(DOCD_HALF_PALLET_QTY,0)) from DOCUMENT_DETAIL with(nolock) where DOC_ID = DOCD_DOC )docd outer apply( select top 1 PLAD_TIME_FROM from PLATFORM_ADVICE with (nolock) where PLAD_TR = TR_ID )plad outer apply( select top 1 KRAJ_DOSTAWCY = case ADDR_COUNTRY when 'Austria' then 'AT' when 'Bośnia I Hercegowina' then 'BA' when 'Szwajcaria' then 'CH' when 'Czechy' then 'CZ' when 'Niemcy' then 'DE' when 'Węgry' then 'HU' when 'Węgry' then 'HU' when 'Chorwacja' then 'HR' when 'Włochy' then 'IT' when 'Polska' then 'PL' when 'Rumunia' then 'RO' when 'Rosja' then 'RU' when 'Słowenia' then 'SI' when 'Ukraina' then 'UA' end from ADDRESS with (nolock) where TR_ADDR_ORG = ADDR_ID )supkraj outer apply( select top 1 [NR_DOSTAWCY] = case when OA_24 = '02' then '14003600' when OA_24 = '46' and ADDR_COUNTRY = 'Austria' then '1532' when OA_24 = '46' and ADDR_COUNTRY = 'Niemcy' then '2547' when OA_24 = '46' and ADDR_COUNTRY = 'Szwajcaria' then '3211' end from OBJECT_ATTRIBUTE with (nolock) join DOCUMENT with (nolock) on OA_OBJ_ID = DOC_ID join TRANSPORT_DETAIL with (nolock) on TRD_DOC = DOC_ID join ADDRESS with (nolock) on DOC_ADDR_ORG_DST = ADDR_ID where TR_ID = TRD_TR )supnr outer apply( select top 1 KRAJ_DOCELOWY = case ADDR_COUNTRY when 'Austria' then 'AT' when 'Bośnia I Hercegowina' then 'BA' when 'Szwajcaria' then 'CH' when 'Czechy' then 'CZ' when 'Niemcy' then 'DE' when 'Węgry' then 'HU' when 'Chorwacja' then 'HR' when 'Włochy' then 'IT' when 'Polska' then 'PL' when 'Rumunia' then 'RO' when 'Rosja' then 'RU' when 'Słowenia' then 'SI' when 'Ukraina' then 'UA' end from ADDRESS with (nolock) where DOC_ADDR_ORG_DST = ADDR_ID )sklepkraj outer apply( select top 1 ADDR_NAME from ADDRESS with (nolock) where case when doc.DOC_ADDR_TARGET is NULL then doc.DOC_ADDR_ORG_DST else doc.DOC_ADDR_TARGET end = ADDR_ID )sklepnr outer apply( select top 1 SUMA_WAGA_PALET = case when DOC_TOTAL_WEIGHT is NULL then DOCD_WEIGHT else DOC_TOTAL_WEIGHT end from DOCUMENT with (nolock) join DOCUMENT_DETAIL with (nolock) on DOCD_DOC = DOC_ID where DOC_ID = TRD_DOC )sumwagapal outer apply( select top 1 [WAGA_PALETY] = LU_WEIGHT, [NR_PALETY] = LU_IDENTIFIER from LOGUNIT with (nolock) where doc.DOC_ORG_DST = LU_ORG_DST and LU_TR = TR_ID )np_wp where DOC_DATE_C between GETDATE()-7 and GETDATE() group by sklepnr.ADDR_NAME, TR_ID, TR_DD_STATUS, np_wp.WAGA_PALETY order by 1 declare @licznik as int = (select count(1) from #tmp_raport_palety); declare @licznik_sekcja as int = 0; declare @licznik_ost_sekcja as int = 0; declare @licznik9 as int = 9; declare @waga_palet as int = 0; declare @sort as int = 1; select a.*,LIN_C_IL_PALET = null, LIN_D_WAGA_PALETY = null, SORT = 1 into #tmp_raport_palety_2 from #tmp_raport_palety a where 1 = 0 while (@licznik > 0) begin insert #tmp_raport_palety_2 select a.*,LIN_C_IL_PALET = null, LIN_D_WAGA_PALETY = null, SORT = 1 from #tmp_raport_palety a where ID_ = @licznik set @licznik_sekcja = (select ILOSC_SEKCJI from #tmp_raport_palety where ID_ = @licznik) set @licznik_ost_sekcja = (select case when ILOSC_PALET_DOKUMENT = 9 and OSTATNIA_SEKCJA_LIN_D = 0 then 9 else OSTATNIA_SEKCJA_LIN_D end from #tmp_raport_palety where ID_ = @licznik) set @waga_palet = (select SUMA_WAGA_PALET from #tmp_raport_palety where ID_ = @licznik) while(@licznik_sekcja > 0) begin insert #tmp_raport_palety_2 (ID_, Sekcja, ID_TRANSPORT, NR_TRANSPORTU, ZAMOWIENIE, ILOSC_PALET_DOKUMENT, ILOSC_SEKCJI, OSTATNIA_SEKCJA_LIN_D, SUMA_WAGA_PALET, WAGA_PALETY, DATA_ODBIORU, DATA_DOSTARCZENIA, DATA_BIEZACA, GODZINA_BIEZACA, NAZWA_ODBIORCY, MIASTO_ODBIORCY, KRAJ_DOSTAWCY, NR_DOSTAWCY, KRAJ_DOCELOWY, NR_ODBIORCY, NR_PALETY, LIN_C_IL_PALET, LIN_D_WAGA_PALETY, SORT) select ID_, Sekcja = 'LIN_C', ID_TRANSPORT, NR_TRANSPORTU, ZAMOWIENIE,ILOSC_PALET_DOKUMENT, @licznik_sekcja, OSTATNIA_SEKCJA_LIN_D, SUMA_WAGA_PALET, WAGA_PALETY, DATA_ODBIORU, DATA_DOSTARCZENIA, DATA_BIEZACA, GODZINA_BIEZACA, NAZWA_ODBIORCY, MIASTO_ODBIORCY, KRAJ_DOSTAWCY, NR_DOSTAWCY, KRAJ_DOCELOWY, NR_ODBIORCY, NR_PALETY, LIN_C_IL_PALET = case when @licznik_sekcja > 1 then 9 else @licznik_ost_sekcja end, LIN_D_WAGA_PALETY = null, SORT = @sort from #tmp_raport_palety a where ID_ = @licznik set @sort = @sort + 1; IF (@licznik_sekcja > 1) begin while(@licznik9 > 0) begin insert #tmp_raport_palety_2 (ID_, Sekcja, ID_TRANSPORT, NR_TRANSPORTU, ZAMOWIENIE, ILOSC_PALET_DOKUMENT, ILOSC_SEKCJI, OSTATNIA_SEKCJA_LIN_D, SUMA_WAGA_PALET, WAGA_PALETY, DATA_ODBIORU, DATA_DOSTARCZENIA, DATA_BIEZACA, GODZINA_BIEZACA, NAZWA_ODBIORCY, MIASTO_ODBIORCY, KRAJ_DOSTAWCY, NR_DOSTAWCY, KRAJ_DOCELOWY, NR_ODBIORCY, NR_PALETY, LIN_C_IL_PALET, LIN_D_WAGA_PALETY, SORT) select ID_, Sekcja = 'LIN_D', ID_TRANSPORT, NR_TRANSPORTU, ZAMOWIENIE,ILOSC_PALET_DOKUMENT, @licznik_sekcja, OSTATNIA_SEKCJA_LIN_D, SUMA_WAGA_PALET, WAGA_PALETY, DATA_ODBIORU, DATA_DOSTARCZENIA, DATA_BIEZACA, GODZINA_BIEZACA, NAZWA_ODBIORCY, MIASTO_ODBIORCY, KRAJ_DOSTAWCY, NR_DOSTAWCY, KRAJ_DOCELOWY, NR_ODBIORCY, NR_PALETY, LIN_C_IL_PALET = null, LIN_D_WAGA_PALETY = WAGA_PALETY, SORT = @sort from #tmp_raport_palety a where ID_ = @licznik set @sort = @sort + 1; set @licznik9 = @licznik9 - 1; end end else begin while(@licznik_ost_sekcja > 0) begin insert #tmp_raport_palety_2 (ID_, Sekcja, ID_TRANSPORT, NR_TRANSPORTU, ZAMOWIENIE, ILOSC_PALET_DOKUMENT, ILOSC_SEKCJI, OSTATNIA_SEKCJA_LIN_D, SUMA_WAGA_PALET, WAGA_PALETY, DATA_ODBIORU, DATA_DOSTARCZENIA, DATA_BIEZACA, GODZINA_BIEZACA, NAZWA_ODBIORCY, MIASTO_ODBIORCY, KRAJ_DOSTAWCY, NR_DOSTAWCY, KRAJ_DOCELOWY, NR_ODBIORCY, NR_PALETY, LIN_C_IL_PALET, LIN_D_WAGA_PALETY, SORT) select ID_, Sekcja = 'LIN_D', ID_TRANSPORT, NR_TRANSPORTU, ZAMOWIENIE,ILOSC_PALET_DOKUMENT, @licznik_sekcja, OSTATNIA_SEKCJA_LIN_D, SUMA_WAGA_PALET, WAGA_PALETY, DATA_ODBIORU, DATA_DOSTARCZENIA, DATA_BIEZACA, GODZINA_BIEZACA, NAZWA_ODBIORCY, MIASTO_ODBIORCY, KRAJ_DOSTAWCY, NR_DOSTAWCY, KRAJ_DOCELOWY, NR_ODBIORCY, NR_PALETY, LIN_C_IL_PALET = null, LIN_D_WAGA_PALETY = WAGA_PALETY, SORT = @sort from #tmp_raport_palety a where ID_ = @licznik set @sort = @sort + 1; set @licznik_ost_sekcja = @licznik_ost_sekcja -1 end end set @licznik_sekcja = @licznik_sekcja - 1; set @licznik9 = 9; end set @licznik = @licznik - 1 end select Lp = ROW_NUMBER() OVER( ORDER BY ID_ ) , a.* from #tmp_raport_palety_2 a order by ID_, SORT drop table #tmp_raport_palety; drop table #tmp_raport_palety_2; end