test.sql

use kcmod

Declare
  @OtchMes smalldatetime='20210401',
  @IdSpis int=null,
  @Client int=null,
  @Vrp int=null
  

  declare @VRP_INT Table (Vrp int)
  --Фильтр по врп или клиенту, врп в приоритете
  if @Vrp is not null
  begin
    insert into @VRP_INT values (@Vrp)
  end
  else
  begin
    if @Client=10
      insert into @VRP_INT (Vrp) select Vrp_int from nsTovPr_ForOsv where Vrk is not null
    if @Client=26
      insert into @VRP_INT (Vrp) select Vrp_int from nsTovPr_ForOsv where CDI is not null
    if @Client=28
      insert into @VRP_INT (Vrp) select Vrp_int from nsTovPr_ForOsv where IdStampDep=5
    if @Client=29
      insert into @VRP_INT (Vrp) select Vrp_int from nsTovPr_ForOsv where IdStampDep=3
    if @Client=27
      insert into @VRP_INT (Vrp) select Vrp_int from nsTovPr_ForOsv where  Vrk is not null or CDI is not null
    if @Client=25
      insert into @VRP_INT (Vrp) select Vrp_int from nsTovPr_ForOsv where  Vrk is null and CDI is null and IdStampDep is null
    if @Client in (1,2,3)
      insert into @VRP_INT (Vrp) select Vrp_int from nsTovPr_ForOsv where  Vrk = @Client
  end
--Загружаем исходные данные
  select
    ROW_NUMBER() over (ORDER BY R.Nom_vag asc) as [npp],
    R.IdZap,
    R.Nom_Vag,
    R.Date_Rem,
    Osv.SNAME as 'Vrp_rem_Name',
    cast(R.AS_Stoim as decimal(15,2))/100 as 'AS_Stoim',
    R.Or_Kompl_Buh,
    isnull(V.Source,1) as 'Source',
    Left(convert(char(8), R.OtchMonth, 12), 4) as 'arx',
    cast(null as int) as 'IdzapArx',
    cast(null as smalldatetime) as 'DateInsArx',
    R.Depo_Rem,
    R.Vid_Rem
  into #tmp
  from DRVagRem R
  left join DRVagRemVrk V on r.IdZap=V.IdZap
  left join nsTovPr_ForOsv Osv on r.Depo_Rem=Osv.VRP_int
  where R.PO_Month=@OtchMes and isnull(R.Korr_pr,0)=0 and
      ((@Client is null and @Vrp is null) or (R.Depo_Rem in (select Vrp from @VRP_INT))) and
      (@IdSpis is null or (R.Nom_Vag in (select NVag from dbo.THP_SpisVag where IdSpis=@IdSpis)))
      and r.Nom_Vag in (29067394,29202793,29202892,26690297)
      --and r.Nom_Vag in (26690297)
---------------------------------------------------------------------------------  
  --Кешируем PakVersion для ускорения работы
  select ver.*
    into #T_PakVersion
  from #tmp t
    join arxthp..PakVersion ver on ver.IdZap = t.IdZap and ver.Vid_Rem = t.Vid_Rem
----------------------------------------------------------------------------------  
--Работа с архивами
--Определяем IdZap по которому будем искать в архиве
  declare @Arx Table (arx char(4))
  insert into @Arx (arx) select distinct arx from #tmp where Source<>5

declare @Ar char(4)
  declare @cSQL varchar(max)
  create table #t1 (IdZap int,Nom_Vag char(8),DateIns smalldatetime)
  
  while (select count(*) from @Arx)>0
  begin
    Select top 1 @Ar=Arx from @Arx
    set @cSQL ='insert into #t1 (IdZap,Nom_Vag) select IdZap,Nom_Vag from arx'+@Ar+'.dbo.DrVrkVagArx where PrDelete=0 and Nom_Vag in (select distinct Nom_Vag from #tmp where source <>5 and arx='''+@Ar+''')'
    PRINT @csql
    Execute(@cSQL)
    update t Set IdZapArx=t1.IdZap
    from #tmp t
    inner join #t1 t1 on t.Nom_Vag=t1.Nom_Vag
    where t.arx=@Ar
    
    delete from #T1
    delete from @Arx where Arx=@Ar
  end
  --Определяем дату первой вставки комментария
  insert into @Arx (arx) select distinct arx from #tmp where Source<>5
  while (select count(*) from @Arx)>0
  begin
    Select top 1 @Ar=Arx from @Arx
    set @cSQL ='insert into #t1 (DateIns,Nom_Vag) select min(DateIns),Nom_Vag from arx'+@Ar+'.dbo.DrVrkVagArx where Nom_Vag in 
            (select distinct Nom_Vag from #tmp where source <>5 and arx='''+@Ar+''') group by Nom_Vag'
    PRINT @csql
    Execute(@cSQL)
    update t Set DateInsArx=t1.DateIns
    from #tmp t
    inner join #t1 t1 on t.Nom_Vag=t1.Nom_Vag
    where t.arx=@Ar
    
    delete from #T1
    delete from @Arx where Arx=@Ar
  end
  --Выбираем коментарии из архива, интересуют комментарии типа 5 и 999, а так же все содержащие в себе текст - комментарий собственника
  --таким присваиваем тип 1
  create table #t2 (Npp int,Pr tinyint,IdZap int,Dat smalldatetime,TipComment smallint,[Column] tinyint)
  create table #t2_tmp (IdZap int,Dat smalldatetime,TipComment smallint)

  insert into @Arx (arx) select distinct arx from #tmp where Source<>5
  while (select count(*) from @Arx)>0
  begin
    Select top 1 @Ar=Arx from @Arx
    set @cSQL ='insert into #t2_tmp (IdZap,Dat,TipComment)
          SELECT 
            comm.IdZap,
            comm.DataCom, 
            case when RaschTip like ''%комментарий собственника%'' then 1 else comm.TipComment end as ''TipComment''
          FROM arx'+@Ar+'.dbo.DrVrkCommentArx comm 
          WHERE IdZap in (select idzapArx from #Tmp where arx='''+@Ar+''') and (TipComment in (5,999) or RaschTip like ''%комментарий собственника%'') '
    
    PRINT @csql
    Execute(@cSQL)
    
    delete from @Arx where Arx=@Ar
  end
  
  --Поскольку данные далеки от идеальных приходится их корректировать
  --После кода 999 не должно быть записей, так как пакет подписан
  --Перед кодом 5 не должно быть записей, так как это первое сообщение на подписание пакета
  --Не может отсутствовать 5-ка, вместо нее добавляем запись с датой первой вставки
  --удаляем 5-ку следующую за 999
  delete t
  from #t2_tmp t
  inner join #t2_tmp t2 on t2.IdZap=t.IdZap and t.Dat>t2.Dat
  where t.TipComment=5 and t2.TipComment=999

  --удаляем все перед первой 5-кой
  delete t
  from #t2_tmp t
  outer apply(select top 1 dat from #t2_tmp t2 where t.IdZap=t2.IdZap and t2.TipComment=5 order by t2.Dat asc) Dat
  where t.Dat<Dat.Dat

--вставляем запись если есть только 999 или 1
  insert into #t2_tmp (IdZap,Dat,TipComment)
    select 
      t1.IdzapArx,
      case when t1.dateinsarx<t.dat then t1.dateinsarx else DATEADD(minute,-1,t.dat) end,
      5
    from #t2_tmp t
    outer apply (select count(*) as kol from #t2_tmp t2 where t2.Idzap=t.Idzap) ins
    left join #tmp t1 on t1.IdzapArx=t.IdZap
    where Ins.Kol=1 and t.TipComment<>5
  -------------------------------------------------------------------------------------------
  --Вставляем подготовленные данные в основную таблицу для сортировки
  insert into #t2 (Npp,Pr,IdZap,Dat,TipComment,[Column])
          SELECT 
            ROW_NUMBER() over (PARTITION BY IdZap ORDER BY IdZap, comm.Dat asc) as [npp],
            1,
            comm.IdZap,
            comm.Dat, 
            TipComment,
            0
          FROM #t2_tmp comm 
  ------------------------------------------------------------------        
  --Выбираем данные для записей из Диадока   
  create table #t3 (IdZap int,Dat smalldatetime,TipComment smallint)
  --Отдельно 5-й тип
  insert into #t3
    select t.IdZap,
        --Дату появления пакета берём из журнала, но если вдруг журнал почищен, то берём дату создания пакета у нас.
        isnull(case when wlog.Date_Sys < wlog.Date_Detection then wlog.Date_Sys else wlog.Date_Detection end, ver_first.RecCreationDate) as Date_Detection,
        5
    from #tmp t
    --Поиск всех пакетов по ремонту.
    outer apply (
      select distinct ver.PakRecId, ver.PakArxYear
      from #T_PakVersion ver
      where ver.IdZap = t.IdZap and ver.Vid_Rem = t.Vid_Rem) ver_pak
    --Привязываем пакет, если нужно проверить PakSrc.
    --left join arxthp..Pak pak on pak.RecId = ver_pak.PakRecId
    --Поиск стартовых версий пакетов.
    outer apply (
      select top 1 *
      from #T_PakVersion ver
      where ver.PakRecId = ver_pak.PakRecId
      order by ver.RecCreationDate asc) ver_first
    --Поиск финальных версий пакетов.
    outer apply (
      select top 1 *
      from #T_PakVersion ver
      where ver.PakRecId = ver_pak.PakRecId
      order by ver.Actual desc, ver.RecCreationDate desc) ver_last
    --Поиск первого упоминания о первой версии в журнале.
    left join Drt1DiadocWebLog (nolock) wlog on wlog.Request = ver_first.Ref
    left join arxthp..PakVersionSign sig on sig.PakRecId = ver_pak.PakRecId and sig.PakArxYear = ver_pak.PakArxYear
    where t.IdzapArx is null
    
  --отдельно 1 и 999
  insert into #t3 
  select t.IdZap,sig.SignDate, case when ver_last.Pakstate=3 then 999 else 1 end as TipComment
    from #tmp t
    --Поиск всех пакетов по ремонту.
    outer apply (
      select distinct ver.PakRecId, ver.PakArxYear
      from #T_PakVersion ver
      where ver.IdZap = t.IdZap and ver.Vid_Rem = t.Vid_Rem) ver_pak
    --Привязываем пакет, если нужно проверить PakSrc.
    --left join arxthp..Pak pak on pak.RecId = ver_pak.PakRecId
    --Поиск стартовых версий пакетов.
    outer apply (
      select top 1 *
      from #T_PakVersion ver
      where ver.PakRecId = ver_pak.PakRecId
      order by ver.RecCreationDate asc) ver_first
    --Поиск финальных версий пакетов.
    outer apply (
      select top 1 *
      from #T_PakVersion ver
      where ver.PakRecId = ver_pak.PakRecId
      order by ver.Actual desc, ver.RecCreationDate desc) ver_last
    --Поиск первого упоминания о первой версии в журнале.
    left join Drt1DiadocWebLog (nolock) wlog on wlog.Request = ver_first.Ref
    left join arxthp..PakVersionSign sig on sig.PakRecId = ver_pak.PakRecId and sig.PakArxYear = ver_pak.PakArxYear
    where t.IdzapArx is null and sig.SignDate is not null
  --Считаем что из Диадока приходят только хорошие данные, а потому сразу вставляем все в таблицу сортировки
  
  insert into #t2 (Npp,Pr,IdZap,Dat,TipComment,[Column])
    SELECT 
      ROW_NUMBER() over (PARTITION BY IdZap ORDER BY IdZap, comm.Dat asc) as [npp],
      2,
      comm.IdZap,
      comm.Dat, 
      TipComment,
      0
    FROM #t3 comm 

--select 111 as [__], * from #t2;
  
  -------------------------------------------------------------------
  --Блок преобразования

update #t2 set [Column] = 6 where npp = 1 and TipComment = 5
  update #t2 set [Column] = 7 where npp = 2 and TipComment in (1, 999)
  update #t2 set [Column] = 8 where npp > 1 and TipComment = 5

  update t set [Column] = 9
  from #t2 t
  inner join #t2 t2 on t.IdZap=t2.IdZap and t.npp=t2.npp+1 and t.Pr=t2.Pr
  where t.TipComment=1 and t2.[Column]=8

  update #t2 set [Column] = 10 where TipComment in (999)

  select '__' as [__], * from #t2

  select ROW_NUMBER() over (PARTITION BY t6.IdZap ORDER BY (select null) asc) as npp
  , t6.IdZap, 
    t6.Dat as [Col6],
    t7.Dat as [Col7],
    t8.Dat as [Col8],
    t9.Dat as [Col9],
    t10.Dat as [Col10],
  t6.Pr
  into #tmp55
  from #t2 t6
  left join #t2 t7 on t7.IdZap = t6.IdZap and t6.Pr=t7.Pr and t7.[Column] = 7
  left join #t2 t8 on t6.IdZap = t8.IdZap and t8.Pr=t6.Pr and t8.[Column] = 8
  left join #t2 t9 on t9.IdZap = t8.IdZap and t8.Pr=t9.Pr and t9.[Column] in (9,10) and t9.npp = t8.npp+1
  left join #t2 t10 on t10.idZap = t6.Idzap and t6.Pr=t10.pr and t10.[Column]=10 --and t10.npp = coalesce(t9.npp,t8.npp,t7.npp,t6.npp)+1
  where t6.[Column] = 6

  select * from #tmp55

  select 
    ROW_NUMBER() over (PARTITION BY IdZap ORDER BY IdZap, npp asc) [idx],
    ROW_NUMBER() over (PARTITION BY IdZap ORDER BY IdZap, npp desc) [idx2],
    *
  into #tmp2
  from #tmp55 order by IdZap, npp


  select * from #tmp2
--------------------------------------------------------------------------
--Формирование итогового отчета
  select 
    t.Npp,
    T.IdZap,
    t.Nom_Vag,
    convert(varchar,t.Date_Rem,104) as 'Date_Rem',
    t.Vrp_rem_Name,
    t.AS_Stoim,
    idx, idx2,
    case when idx = 1 then convert(varchar,[Col6],104) end as [Col6],
    case when idx = 1 then convert(varchar,[Col7],104) end as [Col7],
    [Col8],
    [Col9],
    case when idx2 = 1 then case when [Col10] is null then 'не согласован' else convert(varchar,[Col10],104) end end as [Col10],
    case when idx2 = 1 then case when t.Or_Kompl_Buh is null then 'не подписан' else convert(varchar,t.Or_Kompl_Buh,104) end end as Or_Kompl_Buh,
    case when idx=1 then DATEDIFF(day,t.Date_Rem,t2.[Col6]) else 0 end as [Col12], 
    t.Source,
    T.IdzapArx,
    t.Depo_Rem,
    cast(null as int) as [Col13]
  into #tmp3
  from  #tmp T 
  left JOIN #tmp2 T2  ON (t2.pr=1 and t.IdzapArx=t2.IdZap) or (t2.Pr=2 and t.IdZap=t2.IdZap)
  order by IdZap, idx
  --Вычисляем значения для последней колоноки
  update t set  Col13=case when t.idx=1 then DATEDIFF(day,t.col8,t.col9) else DATEDIFF(day,t2.col9,t.col8) end
  from #tmp3 t
  left join #tmp3 t2 on t.IdZap=t2.IdZap and t.Source=t2.Source and t.Idx-1=t2.Idx
  --Причесываем итоговый отчет
  update #tmp3 Set Col10=null, Col12=null
  where isnull(idx,1)=1 and isnull(idx2,1)=1 and col6 is null
  ----------------------------------------------------

  --Финальная выборка
  select 
    case when isnull(idx,1)=1 then t.Npp end as [Npp1],
    case when isnull(idx,1)=1 then T.IdZap end as [IdZap],
    case when isnull(idx,1)=1 then t.Nom_Vag end as [Nom_Vag1],
    case when isnull(idx,1)=1 then t.Date_Rem end as [Date_Rem],
    case when isnull(idx,1)=1 then t.Vrp_rem_Name end as [Vrp_rem_Name],
    case when isnull(idx,1)=1 then t.AS_Stoim end as [AS_Stoim],
    idx,idx2,
    Col6,Col7,
    convert(varchar,Col8,104) as [Col8],
    convert(varchar,Col9,104) as [Col9],
    Col10,Or_Kompl_Buh,Col12,Col13,Source,IdzapArx,Depo_Rem
  from #tmp3 t
  order by Nom_Vag, idx

  drop table #t1
  drop table #tmp
  drop table #t2
  drop table #tmp2
  drop table #tmp55
  drop table #t3
  drop table #tmp3
  drop table #t2_tmp
  drop table #T_PakVersion