ALTER PROCEDURE [dbo].[FinanceSites_GetCapacityOpenRequests]
(
    @site int, @cCenter int, @start datetime
)
AS
BEGIN
    IF (SELECT object_id('TempDB..#tmp')) IS NOT NULL DROP TABLE #tmp
    IF (SELECT object_id('TempDB..#res')) IS NOT NULL DROP TABLE #res
    IF (SELECT object_id('TempDB..#edgeOfsetForResReq')) IS NOT NULL DROP TABLE   #edgeOfsetForResReq

    declare @periods table
    (
        id int identity(1,1) primary key,
        name varchar(300),
        value date
    )

    declare @end datetime = DATEADD(mm,12,@start), -- default period 12 months
            @granularity nvarchar(10) = 'month';

    insert into @periods select name, value from fGetPeriodsInDateSpan(@start, @end, @granularity);
    --select * from @periods

    declare @tableSpecification HelperTableSpecification;
    create table #res (ptrrid int, person nvarchar(300), empid int,costcenter int, projectid int, projectname nvarchar (500), taskid int, startdate datetime, enddate datetime,  task nvarchar(300),ltid int, labortype nvarchar(300), frole int)

    insert into @tableSpecification select name,'float' from @periods
    declare @alterSql nvarchar(max) = dbo.fGetSqlByTableSpecification(@tableSpecification, '#res')
    exec(@alterSql)

    select
        fs.id as fsid,
        ptm.id as ptmid,
        pt.id as ptid, pt.taskname, pt.startdate, pt.schedulinggranularity, pt.enddate,
        p.id as pid, p.projname as pname,
        ptrr.id as ptrrid,
        rra.*,
        rrrr.id as rrrrid,
        e.name, e.id as empid,
        ra.id as raid,
        plt.labortypelabel,
        plt.id as pltid,
        plt.functionalrole as frole,
        plt.costcenter
    into
        #tmp
    from
        FinanceSites as fs
        inner join projectteams as ptm on fs.id = ptm.site
        inner join project as p on ptm.projectid = p.id
        inner join projecttasks as pt on pt.projectid = p.id
        inner join projectTasks_ResourceRequirements as ptrr on ptrr.projecttask = pt.id
        left join ResourceAssignments as ra on ra.resourceRequirement = ptrr.id
        left join ResourceRequirements_Allocations as rra on rra.resourcerequirement = ptrr.id
        left join ResourceRequirements_RequestedResources as rrrr on rrrr.ResourceRequirement = ptrr.id
        left join Employees as e on e.id = rrrr.Resource
        left join projectLaborTypes as plt on plt.id = ptrr.projectlabortype
    where
        (ra.id is null) and fs.id = @site and ((plt.costcenter = @cCenter) or (e.costcenter = @cCenter) or (@cCenter = -1)) and (rra.allocation > 0)
    order by pid

    alter table #tmp add nDate date
    update #tmp set nDate = dbo.fGetDateByDateAndOffset(startdate,schedulinggranularity, scheduleoffset);
    delete from #tmp where (nDate < @start) or (nDate > @end) or (schedulinggranularity = 0) -- for now delete all rows with task level granularity
    --select * from #tmp
    declare @tmpRowsCount int = (select count(*) from #tmp)
    if (@tmpRowsCount > 1)
    begin
        insert into #res (ptrrid, person, empid, costcenter, projectid, projectname, taskid, task, startdate, enddate, ltid, labortype, frole)
            values(0, null, null, null, null, null, null, null,null, null, null, 'Total', null) -- insert total row
    end

    insert into #res
        (ptrrid, person, empid, costcenter, projectid, projectname, taskid, task, startdate, enddate, ltid, labortype, frole)
        select distinct ptrrid, name, empid, costcenter, pid, pname, ptid, taskname, startdate, enddate, pltid, labortypelabel, frole  from #tmp

    declare @name nvarchar(100), @value date

    while (select Count(*) From @periods) > 0 -- loop through rows
        begin
            select top 1 @name = name From @periods order by id -- current columnName
            select top 1 @value = value From @periods order by id -- current columnType
            print(@value)
            exec('update #res set [' + @name + '] = 0');
            exec('update #res set [' + @name + '] = round(#tmp.allocation * 160, 0) from #res inner join #tmp on #tmp.ptrrid = #res.ptrrid where #tmp.nDate='''+@value+'''');
            exec('update #res set [' + @name + '] =  (select sum('+@name+') from #res) where ptrrid=0');
            delete @periods where value = @value;
        end

    ---- In this part we get start & end, excluding begin & end months with 0 allocation
    select
        rr.id as resReqId,
        dbo.fGetDateByDateAndOffset(startdate,planninggranularity, min(rra.scheduleoffset)) as start,
        dbo.fGetDateByDateAndOffset(startdate,planninggranularity, max(rra.scheduleoffset)) as 'end'
    into
        #edgeOfsetForResReq
    from
        #res as r
        left join projectTasks_ResourceRequirements as rr on r.ptrrid = rr.id
        left join ResourceRequirements_Allocations as rra on rra.resourcerequirement = rr.id
    where
        (r.ptrrid > 0) and (allocation > 0)
    group by
        rr.id, rr.planninggranularity, r.startdate --granularity & startdate should be the same for equal rr.id

    update #res set startdate = edge.start, enddate = edge.[end] from #edgeOfsetForResReq as edge where #res.ptrrid = edge.resReqId
    ----

    -- Start & enddate is NOT project task start/end date
    select * from #res order by ptrrid

    IF (SELECT object_id('TempDB..#tmp')) IS NOT NULL DROP TABLE #tmp
    IF (SELECT object_id('TempDB..#res')) IS NOT NULL DROP TABLE #res
    IF (SELECT object_id('TempDB..#edgeOfsetForResReq')) IS NOT NULL DROP TABLE #edgeOfsetForResReq
END