Advertisement
Ruslan_Rayanov

BDDS GetItems

Oct 18th, 2020
276
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 13.17 KB | None | 0 0
  1. CREATE PROCEDURE [dbo].[crud_bdds_getItems]
  2.     @filters CRUDFilterParameter READONLY,  
  3.     @sort sql_variant,
  4.     @direction nvarchar(8),
  5.     @page int,
  6.     @pageSize int,
  7.     @username nvarchar(32)
  8. AS
  9. BEGIN
  10.     declare @langID int
  11.     select @langID = try_cast(Value as int) from @filters where [Key]='langID'
  12.     -- от этих суммы и даты мы считаем начало Остатков на период
  13.     declare @beginSum int = 0
  14.     declare @beginDate datetime = '2010-06-01'
  15.    
  16.  
  17.  
  18.     -- спецполя для нее: icon_age, prev_age, prevcolor_age, color_age, backcolor_age, desc_name, color, barPercent_age, barClass_age, badge_age  
  19.     declare @result TABLE(
  20.         id nvarchar(max),
  21.         name nvarchar(max),        
  22.         p1 decimal(18,0),
  23.         p2 decimal(18,0),
  24.         p3 decimal(18,0),
  25.         p4 decimal(18,0),
  26.         ptotal decimal(18,0),
  27.         colTitle_p1 nvarchar(max),
  28.         colTitle_p2 nvarchar(max),
  29.         colTitle_p3 nvarchar(max),
  30.         colTitle_p4 nvarchar(max),
  31.         colTitle_ptotal nvarchar(max)      
  32.     )
  33.  
  34.  
  35.  
  36.  
  37.     -- извлекаем доп параметры из URL
  38.     declare @filterPeriodType nvarchar(128) = ''  -- years, months, weeks
  39.     declare @filterProjectType nvarchar(128) = ''  -- falcon, other
  40.     declare @itemID nvarchar(128) = '', @subType nvarchar(128) = '', @curType nvarchar(128) = '', @curItemID int = 0
  41.    
  42.     select @filterPeriodType = Value from @filters where [Key] = 'hide_periodType'
  43.     select @filterProjectType = Value from @filters where [Key] = 'hide_projectType'
  44.     if(isnull(@filterProjectType, '')='') set @filterProjectType = 'all'
  45.     if(isnull(@filterPeriodType, '')='') set @filterPeriodType = 'months'
  46.    
  47.    
  48.     select @itemID = Value from @filters where [Key] = 'itemID'
  49.     set @subType = iif(isnull(@itemID, '')='', 'contragents', '')
  50.     set @curType = iif(isnull(@itemID, '')<>'', dbo.str_splitPart(@itemID, '_', 1), '')
  51.     set @curItemID =try_cast(iif(isnull(@itemID, '')<>'', dbo.str_splitPart(@itemID, '_', 2), '') as int)
  52.  
  53.    
  54.    
  55.     declare @projectIDs table (id int)    
  56.     insert into @projectIDs
  57.     select id from tst_projects
  58.     where (@filterProjectType='all' or @filterProjectType='falcon' and typeID = 1 or @filterProjectType<>'falcon' and typeID <> 1)  
  59.         and id not in (85,52,204)
  60.     union
  61.     select NULL id
  62.  
  63.     set datefirst 1;
  64.    
  65.     declare @date date = dateadd(month, -1, getdate()), @date2 date, @date3 date, @date4 date, @date5 date
  66.      if(isnull(@filterPeriodType, 'months')='months' ) begin
  67.         set @date = dateadd(month, -3, getdate())
  68.         set @date =  DATEADD(month, DATEDIFF(month, 0, @date), 0)
  69.         set @date2 =  DATEADD(month, 1, @date)
  70.         set @date3 =  DATEADD(month, 2, @date)
  71.         set @date4 =  DATEADD(month, 3, @date)
  72.         set @date5 =  DATEADD(month, 4, @date)
  73.        
  74.        
  75.     end
  76.     if(@filterPeriodType='quarters') begin
  77.         set @date = dateadd(qq, -3, getdate())
  78.         set @date =  DATEADD(qq, DATEDIFF(qq, 0,@date), 0)                
  79.         set @date2 =  DATEADD(qq, 1, @date)
  80.         set @date3 =  DATEADD(qq, 2, @date)
  81.         set @date4 =  DATEADD(qq, 3, @date)
  82.         set @date5 =  DATEADD(qq, 4, @date)
  83.     end
  84.     if(@filterPeriodType='years') begin
  85.         set @date = dateadd(year, -2, getdate())
  86.         set @date = DATEADD(yy, DATEDIFF(yy, 0, @date) - 1, 0)
  87.         set @date2 =  DATEADD(year, 1, @date)  
  88.         set @date3 =  DATEADD(year, 2, @date)
  89.         set @date4 =  DATEADD(year, 3, @date)
  90.         set @date5 =  DATEADD(year, 4, @date)
  91.  
  92.     end
  93.    
  94.     if(isnull(@curType, '')='') begin
  95.       insert into @result
  96.       select id, name,
  97.           isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where typeID = t.id and factPayed>@date and factPayed < @date2 and projectID in (select id from @projectIDs) ), 0) p1,
  98.           isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where typeID = t.id and factPayed>@date2 and factPayed < @date3 and projectID in (select id from @projectIDs) ), 0) p2,
  99.           isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where typeID = t.id and factPayed>@date3 and factPayed < @date4 and projectID in (select id from @projectIDs) ), 0) p3,
  100.           isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where typeID = t.id and factPayed>@date4 and factPayed < @date5 and projectID in (select id from @projectIDs) ), 0) p4,
  101.           isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where typeID = t.id and factPayed>@date and factPayed < @date5 and projectID in (select id from @projectIDs) ), 0) ptotal,
  102.           [dbo].[as_periodName] (@filterPeriodType, @date) colTitle_p1,
  103.           [dbo].[as_periodName] (@filterPeriodType, @date2) colTitle_p2,
  104.           [dbo].[as_periodName] (@filterPeriodType, @date3) colTitle_p3,
  105.           [dbo].[as_periodName] (@filterPeriodType, @date4) colTitle_p4,
  106.           iif(@langID=1, 'TOTAL', 'ИТОГО') colTitle_ptotal    
  107.       from tst_finTypes t where id <3  
  108.     end
  109.     if(@curType='contragents') begin
  110.       insert into @result
  111.       select id, name,
  112.           isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where typeID = @curItemID and (fromID=ctr.id and @curItemID=1 or toID=ctr.id and @curItemID=2  ) and factPayed>@date and factPayed < @date2  and projectID in (select id from @projectIDs)), 0) p1,
  113.           isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where typeID = @curItemID and (fromID=ctr.id and @curItemID=1 or toID=ctr.id and @curItemID=2  ) and factPayed>@date2 and factPayed < @date3  and projectID in (select id from @projectIDs)), 0) p2,
  114.           isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where typeID = @curItemID and (fromID=ctr.id and @curItemID=1 or toID=ctr.id and @curItemID=2  ) and factPayed>@date3 and factPayed < @date4  and projectID in (select id from @projectIDs)), 0) p3,
  115.           isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where typeID = @curItemID and (fromID=ctr.id and @curItemID=1 or toID=ctr.id and @curItemID=2  ) and factPayed>@date4 and factPayed < @date5  and projectID in (select id from @projectIDs)), 0) p4,
  116.           isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where typeID = @curItemID and (fromID=ctr.id and @curItemID=1 or toID=ctr.id and @curItemID=2  ) and factPayed>@date and factPayed < @date5  and projectID in (select id from @projectIDs)), 0) ptotal,
  117.           [dbo].[as_periodName] (@filterPeriodType, @date) colTitle_p1,
  118.           [dbo].[as_periodName] (@filterPeriodType, @date2) colTitle_p2,
  119.           [dbo].[as_periodName] (@filterPeriodType, @date3) colTitle_p3,
  120.           [dbo].[as_periodName] (@filterPeriodType, @date4) colTitle_p4,
  121.           'ИТОГО' colTitle_ptotal    
  122.       from tst_customers ctr
  123.       where  exists (select id from tst_finances where (fromID = ctr.id and @curItemID=1 and typeID=1 or toID = ctr.id and @curItemID=2 and typeID =2) and factPayed > @date and factPayed < @date5  and projectID in (select id from @projectIDs))      
  124.     end
  125.  
  126.     -- 1 SELECT - сами данные    
  127.     select *,
  128.         p1 prev_p2,
  129.         p2 prev_p3,
  130.         p3 prev_p4,
  131.         '<b>'+cast(ptotal as nvarchar)+'</b>' ptotal,
  132.         iif(@subType='', '','<div class="as-table" data-code="bdds" data-itemID="'+@subType+ '_'+cast(id as nvarchar)+'"></div>') sub_name
  133.        
  134.      
  135.     from @result
  136.     order by  
  137.         case when @sort = 'name' and @direction = 'down' then name end desc,
  138.         case when @sort = 'name' and @direction = 'up' then name end asc,
  139.         case when @sort = 'p1' and @direction = 'down' then p1 end desc,
  140.         case when @sort = 'p1' and @direction = 'up' then p1 end asc,
  141.         case when @sort = 'p2' and @direction = 'down' then p2 end desc,
  142.         case when @sort = 'p2' and @direction = 'up' then p2 end asc,
  143.         case when @sort = 'p3' and @direction = 'down' then p3 end desc,
  144.         case when @sort = 'p3' and @direction = 'up' then p3 end asc,
  145.         case when @sort = 'p4' and @direction = 'down' then p4 end desc,
  146.         case when @sort = 'p4' and @direction = 'up' then p4 end asc,
  147.         case when @sort = 'ptotal' and @direction = 'down' then ptotal end desc,
  148.         case when @sort = 'ptotal' and @direction = 'up' then ptotal end asc
  149.     OFFSET @PageSize * (@Page - 1) ROWS
  150.     FETCH NEXT @PageSize ROWS ONLY;
  151.    
  152.     -- 2 SELECT - кол-во в таблице
  153.     select count(*) from @result   
  154.  
  155.     -- 3 SELECT Дополнительные настройки таблицы
  156.     select 1 HideTitleCount, iif(@curType='contragents', iif(@curItemID=1,iif(@langID=1, 'Incomes by contragents', 'Приходы по контрагентам'),
  157.                                                              iif(@langID=1, 'Outcomes by contragents', 'Расходы по контрагентам')),
  158.                                  iif(@langID=1, 'Cashflow report', 'Денежный поток (БДДС)')) Title,
  159.         '' TitleTooltip, 'h3' headerTag
  160.  
  161.     -- 4 SELECT Данные для подвала страницы или данные для Ганта/Канбана (если установлен ViewType в 3 SELECT)
  162.     if(isnull(@curType, '')='') begin
  163.         select x1,
  164.         iif(try_cast(p1 as float)>0, '<span class="text-success">'+cast(p1 as nvarchar)+'</span>', '<span class="text-danger">'+cast(p1 as nvarchar)+'</span>')  p1,
  165.         iif(try_cast(p2 as float)>0, '<span class="text-success">'+cast(p2 as nvarchar)+'</span>', '<span class="text-danger">'+cast(p2 as nvarchar)+'</span>')  p2,
  166.         iif(try_cast(p3 as float)>0, '<span class="text-success">'+cast(p3 as nvarchar)+'</span>', '<span class="text-danger">'+cast(p3 as nvarchar)+'</span>')  p3,
  167.         iif(try_cast(p4 as float)>0, '<span class="text-success">'+cast(p4 as nvarchar)+'</span>', '<span class="text-danger">'+cast(p4 as nvarchar)+'</span>')  p4,
  168.         pTotal,
  169.         isHead
  170.         from (
  171.         select iif(@langID=1, 'At period start', 'НА НАЧАЛО ПЕРИОДА') x1,
  172.               @beginSum + isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@beginDate and factPayed < @date and projectID in (select id from @projectIDs) ), 0) p1,
  173.               @beginSum + isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@beginDate and factPayed < @date2 and projectID in (select id from @projectIDs) ), 0) p2,
  174.               @beginSum + isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@beginDate and factPayed < @date3 and projectID in (select id from @projectIDs) ), 0) p3,
  175.               @beginSum + isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@beginDate and factPayed < @date4 and projectID in (select id from @projectIDs) ), 0) p4,
  176.               '' ptotal,
  177.               1 ord,
  178.               1 isHead
  179.         union
  180.         select iif(@langID=1, 'Saldo', 'САЛЬДО') x1,
  181.               isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@date and factPayed < @date2 and projectID in (select id from @projectIDs) ), 0) p1,
  182.               isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@date2 and factPayed < @date3 and projectID in (select id from @projectIDs) ), 0) p2,
  183.               isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@date3 and factPayed < @date4 and projectID in (select id from @projectIDs) ), 0) p3,
  184.               isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@date4 and factPayed < @date5 and projectID in (select id from @projectIDs) ), 0) p4,
  185.              '' ptotal,
  186.               2 ord,
  187.               0 isHead
  188.         union    
  189.         select iif(@langID=1, 'At period end', 'НА КОНЕЦ ПЕРИОДА') x1,
  190.               @beginSum + isnull((select cast(sum(isnull([sum], 0) * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@beginDate and factPayed < @date2 and projectID in (select id from @projectIDs) ), 0) p1,
  191.               @beginSum + isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@beginDate and factPayed < @date3 and projectID in (select id from @projectIDs) ), 0) p2,
  192.               @beginSum + isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@beginDate and factPayed < @date4 and projectID in (select id from @projectIDs) ), 0) p3,
  193.               @beginSum + isnull((select cast(sum([sum] * iif(typeID=1, 1, -1))/1000 as decimal(18,0)) from tst_finances where factPayed>@beginDate and factPayed < @date5 and projectID in (select id from @projectIDs) ), 0) p4,
  194.             '' ptotal,
  195.               3 ord,
  196.               0 isHead
  197.         ) t1 order by ord
  198.     end
  199. END
  200.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement