BDDS GetItems

CREATE PROCEDURE [dbo].[crud_bdds_getItems]
    @filters CRUDFilterParameter READONLY,
    @sort sql_variant,
    @direction nvarchar(8),
    @page int,
    @pageSize int,
    @username nvarchar(32)
AS
BEGIN
    declare @langID int
    select @langID = try_cast(Value as int) from @filters where [Key]='langID'
    -- от этих суммы и даты мы считаем начало Остатков на период
    declare @beginSum int = 0
    declare @beginDate datetime = '2010-06-01'



    -- спецполя для нее: icon_age, prev_age, prevcolor_age, color_age, backcolor_age, desc_name, color, barPercent_age, barClass_age, badge_age
    declare @result TABLE(
        id nvarchar(max),
        name nvarchar(max),
        p1 decimal(18,0),
        p2 decimal(18,0),
        p3 decimal(18,0),
        p4 decimal(18,0),
        ptotal decimal(18,0),
        colTitle_p1 nvarchar(max),
        colTitle_p2 nvarchar(max),
        colTitle_p3 nvarchar(max),
        colTitle_p4 nvarchar(max),
        colTitle_ptotal nvarchar(max)
    )




    -- извлекаем доп параметры из URL
    declare @filterPeriodType nvarchar(128) = ''  -- years, months, weeks
    declare @filterProjectType nvarchar(128) = ''  -- falcon, other
    declare @itemID nvarchar(128) = '', @subType nvarchar(128) = '', @curType nvarchar(128) = '', @curItemID int = 0

    select @filterPeriodType = Value from @filters where [Key] = 'hide_periodType'
    select @filterProjectType = Value from @filters where [Key] = 'hide_projectType'
    if(isnull(@filterProjectType, '')='') set @filterProjectType = 'all'
    if(isnull(@filterPeriodType, '')='') set @filterPeriodType = 'months'


    select @itemID = Value from @filters where [Key] = 'itemID'
    set @subType = iif(isnull(@itemID, '')='', 'contragents', '')
    set @curType = iif(isnull(@itemID, '')<>'', dbo.str_splitPart(@itemID, '_', 1), '')
    set @curItemID =try_cast(iif(isnull(@itemID, '')<>'', dbo.str_splitPart(@itemID, '_', 2), '') as int)



    declare @projectIDs table (id int)
    insert into @projectIDs
    select id from tst_projects
    where (@filterProjectType='all' or @filterProjectType='falcon' and typeID = 1 or @filterProjectType<>'falcon' and typeID <> 1)
        and id not in (85,52,204)
    union
    select NULL id

    set datefirst 1;

    declare @date date = dateadd(month, -1, getdate()), @date2 date, @date3 date, @date4 date, @date5 date
     if(isnull(@filterPeriodType, 'months')='months' ) begin
        set @date = dateadd(month, -3, getdate())
        set @date =  DATEADD(month, DATEDIFF(month, 0, @date), 0)
        set @date2 =  DATEADD(month, 1, @date)
        set @date3 =  DATEADD(month, 2, @date)
        set @date4 =  DATEADD(month, 3, @date)
        set @date5 =  DATEADD(month, 4, @date)


    end
    if(@filterPeriodType='quarters') begin
        set @date = dateadd(qq, -3, getdate())
        set @date =  DATEADD(qq, DATEDIFF(qq, 0,@date), 0)
        set @date2 =  DATEADD(qq, 1, @date)
        set @date3 =  DATEADD(qq, 2, @date)
        set @date4 =  DATEADD(qq, 3, @date)
        set @date5 =  DATEADD(qq, 4, @date)
    end
    if(@filterPeriodType='years') begin
        set @date = dateadd(year, -2, getdate())
        set @date = DATEADD(yy, DATEDIFF(yy, 0, @date) - 1, 0)
        set @date2 =  DATEADD(year, 1, @date)
        set @date3 =  DATEADD(year, 2, @date)
        set @date4 =  DATEADD(year, 3, @date)
        set @date5 =  DATEADD(year, 4, @date)

    end

    if(isnull(@curType, '')='') begin
      insert into @result
      select id, name,
          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,
          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,
          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,
          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,
          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,
          [dbo].[as_periodName] (@filterPeriodType, @date) colTitle_p1,
          [dbo].[as_periodName] (@filterPeriodType, @date2) colTitle_p2,
          [dbo].[as_periodName] (@filterPeriodType, @date3) colTitle_p3,
          [dbo].[as_periodName] (@filterPeriodType, @date4) colTitle_p4,
          iif(@langID=1, 'TOTAL', 'ИТОГО') colTitle_ptotal
      from tst_finTypes t where id <3
    end
    if(@curType='contragents') begin
      insert into @result
      select id, name,
          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,
          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,
          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,
          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,
          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,
          [dbo].[as_periodName] (@filterPeriodType, @date) colTitle_p1,
          [dbo].[as_periodName] (@filterPeriodType, @date2) colTitle_p2,
          [dbo].[as_periodName] (@filterPeriodType, @date3) colTitle_p3,
          [dbo].[as_periodName] (@filterPeriodType, @date4) colTitle_p4,
          'ИТОГО' colTitle_ptotal
      from tst_customers ctr
      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))
    end

    -- 1 SELECT - сами данные
    select *,
        p1 prev_p2,
        p2 prev_p3,
        p3 prev_p4,
        '<b>'+cast(ptotal as nvarchar)+'</b>' ptotal,
        iif(@subType='', '','<div class="as-table" data-code="bdds" data-itemID="'+@subType+ '_'+cast(id as nvarchar)+'"></div>') sub_name


    from @result
    order by
        case when @sort = 'name' and @direction = 'down' then name end desc,
        case when @sort = 'name' and @direction = 'up' then name end asc,
        case when @sort = 'p1' and @direction = 'down' then p1 end desc,
        case when @sort = 'p1' and @direction = 'up' then p1 end asc,
        case when @sort = 'p2' and @direction = 'down' then p2 end desc,
        case when @sort = 'p2' and @direction = 'up' then p2 end asc,
        case when @sort = 'p3' and @direction = 'down' then p3 end desc,
        case when @sort = 'p3' and @direction = 'up' then p3 end asc,
        case when @sort = 'p4' and @direction = 'down' then p4 end desc,
        case when @sort = 'p4' and @direction = 'up' then p4 end asc,
        case when @sort = 'ptotal' and @direction = 'down' then ptotal end desc,
        case when @sort = 'ptotal' and @direction = 'up' then ptotal end asc
    OFFSET @PageSize * (@Page - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    -- 2 SELECT - кол-во в таблице
    select count(*) from @result

    -- 3 SELECT Дополнительные настройки таблицы
    select 1 HideTitleCount, iif(@curType='contragents', iif(@curItemID=1,iif(@langID=1, 'Incomes by contragents', 'Приходы по контрагентам'),
                                                             iif(@langID=1, 'Outcomes by contragents', 'Расходы по контрагентам')),
                                 iif(@langID=1, 'Cashflow report', 'Денежный поток (БДДС)')) Title,
        '' TitleTooltip, 'h3' headerTag

    -- 4 SELECT Данные для подвала страницы или данные для Ганта/Канбана (если установлен ViewType в 3 SELECT)
    if(isnull(@curType, '')='') begin
        select x1,
        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,
        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,
        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,
        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,
        pTotal,
        isHead
        from (
        select iif(@langID=1, 'At period start', 'НА НАЧАЛО ПЕРИОДА') x1,
              @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,
              @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,
              @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,
              @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,
              '' ptotal,
              1 ord,
              1 isHead
        union
        select iif(@langID=1, 'Saldo', 'САЛЬДО') x1,
              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,
              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,
              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,
              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,
             '' ptotal,
              2 ord,
              0 isHead
        union
        select iif(@langID=1, 'At period end', 'НА КОНЕЦ ПЕРИОДА') x1,
              @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,
              @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,
              @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,
              @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,
            '' ptotal,
              3 ord,
              0 isHead
        ) t1 order by ord
    end
END