alter table DT823 add SIMULATION_REPORT numeric(5,0);

GO

alter table DT823 add constraint DT823DI064518_DEFAULT DEFAULT 0 FOR [SIMULATION_REPORT];

GO

update DT823 set SIMULATION_REPORT = DEFAULT;

GO

-- add report type to portfolio status report
alter table DT090 add REPORT_TYPE numeric(5,0) DEFAULT 1;

GO

alter table DT090 add SIM_DATE_FROM datetime2 DEFAULT CONVERT(datetime2,'01.01.1970',104);

GO

alter table DT090 add SIM_DATE_TO datetime2 DEFAULT CONVERT(datetime2,'01.01.1970',104);

GO

-- simulation DIs for project per portfolio status report table
alter table DT093 add PR_PRIO numeric(5,0) DEFAULT 0;

GO

alter table DT093 add REQUESTED_START datetime2 DEFAULT CONVERT(datetime2,'01.01.1970',104);

GO

alter table DT093 add REQUESTED_END datetime2 DEFAULT CONVERT(datetime2,'01.01.1970',104);

GO

alter table DT093 add START_SIMU datetime2 DEFAULT CONVERT(datetime2,'01.01.1970',104);

GO

alter table DT093 add END_SIMU datetime2 DEFAULT CONVERT(datetime2,'01.01.1970',104);

GO

alter table DT093 add COLOR_ID_REM varchar(8);

GO

alter table DT093 add COLOR_ID_ACT varchar(8);

GO

alter table DT093 add CALC_START datetime2 DEFAULT CONVERT(datetime2,'01.01.1970',104);

GO

alter table DT093 add CALC_END datetime2 DEFAULT CONVERT(datetime2,'01.01.1970',104);

GO

alter table DT093 add ACT_START datetime2 DEFAULT CONVERT(datetime2,'01.01.1970',104);

GO

alter table DT093 add LAST_LOAD_DATE datetime2 DEFAULT CONVERT(datetime2,'01.01.1970',104);

GO

-- color columns per project
alter table DT858 add COLOR_ID_REM varchar(8);

GO

alter table DT858 add COLOR_ID_ACT varchar(8);

GO

update DT858 set COLOR_ID_ACT = '000127' where COLOR_ID_ACT is null;

GO

-- ole for overload per project
alter table DT093 add OLE_OVERLOAD varchar(20);

GO

-- ole for overload per resource
alter table DT467 add OLE_SIM_OVERLOAD varchar(20);

GO

create table PORTFOLIO_SIM (
        [UUID] uniqueidentifier not null,
        [STATUSREPORT_ID] varchar(10),
        [PORTFOLIO_ID] varchar(18),
        [TYPE] numeric(5,0),
        [OS_CREATED_ON] datetime2,
        [OS_MODIFIED_ON] datetime2,
        [OS_RWDU] numeric(5,0),
        [OS_CREATED_BY] varchar(20),
        [OS_MODIFIED_BY] varchar(20),
        [OS_LICENSE] varchar(4),
        [OS_OPC] varchar(8),
        [OS_OWNER] numeric(5,0),
        [OS_GROUP] numeric(5,0),
        [OS_ANY] numeric(5,0),
        [TIME_PERIOD] datetime2,
        [RESOURCE] varchar(20),
        [PR_ID] varchar(18),
        [VALUE] float,
        [PR_REPORT_ID] varchar(10),
        [OVERALL_ORDER] numeric(10,0),
        [PR_TYPE] varchar(18),
        [MAIN_PR_ID] varchar(18),
        [PR_FUNC_ID] varchar(18)
    )
	;
GO


    IF NOT EXISTS (SELECT * FROM SYS.INDEXES
       WHERE IS_PRIMARY_KEY = 1 AND OBJECT_ID IN
             (SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME = 'PORTFOLIO_SIM'))
       alter table PORTFOLIO_SIM add constraint PID095DT095 primary key ([UUID])
GO

    alter table PORTFOLIO_SIM with nocheck  
        add constraint FKPORTFOLIO_SIMnull 
        foreign key ([STATUSREPORT_ID], [PORTFOLIO_ID]) 
        references DT090        
        on delete no action
GO
    CREATE TRIGGER UFKPORTFOLIO_SIMnull ON DT090
        FOR DELETE
        AS
        BEGIN
            DELETE t
            FROM DELETED d
                INNER JOIN PORTFOLIO_SIM t ON d.STATUSREPORT_ID = t.STATUSREPORT_ID
                                  AND d.PORTFOLIO_ID = t.PORTFOLIO_ID
        END
GO
    alter table PORTFOLIO_SIM with check check constraint FKPORTFOLIO_SIMnull
GO

    create index IDX_PORTFOLIO_SIMnull on PORTFOLIO_SIM ([STATUSREPORT_ID], [PORTFOLIO_ID])
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095UUID_DEFAULT DEFAULT newid() FOR [UUID]
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095UUID_NOT_NULL check([UUID] is not null)
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095DI064291_DEFAULT DEFAULT 0 FOR [TYPE]
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095DI064292_DEFAULT DEFAULT CONVERT(datetime2,'01.01.1970',104) FOR [OS_CREATED_ON]
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095DI064293_DEFAULT DEFAULT CONVERT(datetime2,'01.01.1970',104) FOR [OS_MODIFIED_ON]
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095DI064294_DEFAULT DEFAULT 0 FOR [OS_RWDU]
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095DI064297_DEFAULT DEFAULT 0 FOR [OS_OWNER]
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095DI064298_DEFAULT DEFAULT 0 FOR [OS_GROUP]
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095DI064307_DEFAULT DEFAULT 0 FOR [OS_ANY]
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095DI064310_DEFAULT DEFAULT CONVERT(datetime2,'01.01.1970',104) FOR [TIME_PERIOD]
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095DI064317_DEFAULT DEFAULT 0 FOR [VALUE]
GO

    alter table PORTFOLIO_SIM 
        add constraint DT095DI064318_DEFAULT DEFAULT 0 FOR [OVERALL_ORDER]
GO

CREATE VIEW [dbo].[V_RES_PARENT] as (
SELECT
      res.DI001218 parent_res_id,
      isnull(sub_res.DI001218, res.DI001218) child_res
      FROM DT467 res
      LEFT JOIN DT467 sub_res ON res.DI000270 IS NOT NULL
                                 AND sub_res.DI000270 LIKE (res.DI000270 + '%')
      WHERE res.DI003414 = '2');

GO

CREATE VIEW [dbo].[V_RES_CAPA]
(
    sum_type,
	time_period,
	res_id,
	summ_avail_cap,
	summ_planned,
	summ_utilization,
	summ_base_load_w3,
	summ_value_4,
	summ_value_5,
	summ_used,
	summ_planned_prc1,
	summ_planned_prc2,
	summ_planned_prc3,
	summ_planned_prc4,
	summ_planned_prc5
)
as
SELECT
	sum_type,
	period,
	res_id,
	summ_avail_cap,
	summ_planned,
	CASE WHEN ISNULL(summ_avail_cap, 0) > 0
		THEN (summ_planned / summ_avail_cap) * 100
		ELSE 0
	END summ_utilization,
	summ_base_load_w3,
	summ_value_4,
	summ_value_5,
	summ_used,
	summ_planned_prc1,
	summ_planned_prc2,
	summ_planned_prc3,
	summ_planned_prc4,
	summ_planned_prc5
FROM (
	SELECT
		'M' sum_type,
	    dateadd(m, datediff(m, 0, di001327), 0) period,
		di001326 res_id,
		SUM(di002454) summ_avail_cap,
		SUM(di002483) summ_planned,
		SUM(di002640) summ_base_load_w3,
		SUM(di002641) summ_value_4,
		SUM(di002642) summ_value_5,
		SUM(di002464) summ_used,
		SUM(di002484) summ_planned_prc1,
		SUM(di002485) summ_planned_prc2,
		SUM(di002486) summ_planned_prc3,
		SUM(di002487) summ_planned_prc4,
		SUM(di002488) summ_planned_prc5
	FROM dt468
	GROUP BY dateadd(m, datediff(m, 0, di001327), 0), di001326
	UNION
	SELECT
		'W' sum_type,
	    dateadd(dd, 1 - datepart(dw, di001327), di001327) period,
		di001326 res_id,
		SUM(di002454) summ_avail_cap,
		SUM(di002483) summ_planned,
		SUM(di002640) summ_base_load_w3,
		SUM(di002641) summ_value_4,
		SUM(di002642) summ_value_5,
		SUM(di002464) summ_used,
		SUM(di002484) summ_planned_prc1,
		SUM(di002485) summ_planned_prc2,
		SUM(di002486) summ_planned_prc3,
		SUM(di002487) summ_planned_prc4,
	SUM(di002488) summ_planned_prc5
	FROM dt468
	GROUP BY dateadd(dd, 1 - datepart(dw, di001327), di001327), di001326
) a;

GO

CREATE VIEW [dbo].[V_RES_PR_LOAD_PF_SUB] AS
WITH
  portfolio_pr_reports AS (
    SELECT
      DI023102 pf_id,
      DI023101 pr_id,
	  isnull(DI062360, sim_reports.pr_report_id) pr_report_id
    FROM DT858
    LEFT JOIN (SELECT
               DI008413 pr_id,
               max(DI008416) pr_report_id
               FROM DT823
               where simulation_report = 1
               group by DI008413
               ) sim_reports on (DT858.DI023101 = sim_reports.pr_id)
  ),
  external_projects AS (
    SELECT
      pf.DI028667 pf_id,
      pf_pr.DI023102 other_pf_id,
      pf_pr.DI023101 pr_id,
	  pf_pr.DI062360 pr_report_id
    FROM DT870 pf
    LEFT JOIN
    DT858 pf_pr ON NOT EXISTS(
      SELECT 1
      FROM DT858 pf_pr_sub
      WHERE DI023102 = pf.DI028667
      AND pf_pr.DI023101 = pf_pr_sub.DI023101
    )
	JOIN DT461 on (DT461.DI001001=pf_pr.DI023101)
    where DI001042 = 1
    UNION
    SELECT
      pf.DI028667 pf_id,
      '-' other_pf_id,
      pr_report.pr_id pr_id,
	  pr_report.report_id report_id
    FROM DT870 pf
    LEFT JOIN (
      SELECT DI008413 pr_id,
             DI008416 report_id
      FROM DT823
      WHERE NOT EXISTS(
        SELECT 1
        FROM DT858
        WHERE DI023101 = DI008413
          and DI062360 = DI008416
      )
      AND DT823.SIMULATION_REPORT = 0 
      AND DI008413 != '-'
      AND DT823.DI029179 = 1
    ) pr_report ON 1=1
	JOIN DT461 on (DT461.DI001001=pr_report.pr_id)
    where DI001042 = 1
  ),
  res_pr_load AS (
    SELECT
      DATEADD(m, datediff(m, 0, load.DI008523), 0) period,
      res.DI001218 res_id,
      load.DI008516 pr_id,
	  report.DI008416 pr_report_id,
      SUM(load.DI008664) remaining,
      SUM(load.DI008662) actual
    FROM DT467 res
	JOIN DT827 load ON (res.DI001218 = load.DI008518)
	JOIN DT823 report on (load.DI008516 = report.DI008413
                      and load.DI008535 = report.DI008416)
    GROUP BY DATEADD(m, datediff(m, 0, load.DI008523), 0), res.DI001218, 
			 load.DI008516, report.DI008416
    )
  SELECT
    res_pr_load.period time_period,
    res_pr_load.res_id,
    portfolio_pr_reports.pf_id,
    res_pr_load.pr_id,
	res_pr_load.pr_report_id pr_report_id,
    res_pr_load.remaining,
    res_pr_load.actual,
    0 is_external
  FROM res_pr_load
  JOIN portfolio_pr_reports ON res_pr_load.pr_id = portfolio_pr_reports.pr_id
  UNION
  SELECT
    res_pr_load.period time_period,
    res_pr_load.res_id,
    external_projects.pf_id,
    NULL pr_id,
	NULL pr_report_id,
    SUM(res_pr_load.remaining),
    SUM(res_pr_load.actual),
    1 is_external
  FROM res_pr_load
  JOIN external_projects ON res_pr_load.pr_id = external_projects.pr_id
  GROUP BY res_pr_load.period, res_pr_load.res_id, external_projects.pf_id;

GO

CREATE VIEW [dbo].[V_RES_PR_LOAD_PORTFOLIO] AS
SELECT
  res_pr_pf_load.time_period,
  res_pr_pf_load.res_id,
  parent_res.parent_res_id,
  res_pr_pf_load.pf_id,
  res_pr_pf_load.pr_id,
  res_pr_pf_load.pr_report_id,
  pr_report.main_pr_id,
  pr_report.parent_pr_id,
  pr.pr_prio,
  pr_report.pr_req_end,
  pr_report.pr_calc_end,
  pr_report.pr_func_id,
  pr_report.pr_type,
  pr_report.pr_code,
  res_pr_pf_load.remaining,
  res_pr_pf_load.actual,
  res_cap.summ_avail_cap availability,
  res_cap.summ_base_load_w3 baseload,
  res_pr_pf_load.is_external
FROM V_RES_PR_LOAD_PF_SUB AS res_pr_pf_load
LEFT JOIN (
  SELECT
    DI008413 pr_id,
    DI008416 pr_report_id,
    DI008578 main_pr_id,
    DI008414 parent_pr_id,
    DI008589 pr_req_end,
    DI008591 pr_calc_end,
    DI057531 pr_func_id,
    DI062292 pr_type,
    DI029164 pr_code
  FROM DT823
) pr_report ON res_pr_pf_load.pr_id = pr_report.pr_id
LEFT JOIN (
   SELECT
     DI001001 pr_id,
     DI001015 pr_prio
   FROM DT461
) pr on (res_pr_pf_load.pr_id = pr.pr_id)
join (SELECT
        res.DI001218 parent_res_id,
        ISNULL(sub_res.DI001218, res.DI001218) child_res
      FROM DT467 res
      LEFT JOIN DT467 sub_res ON res.DI000270 IS NOT NULL AND sub_res.DI000270 LIKE (res.DI000270 + '%')
      WHERE res.DI003414 = '2') parent_res on (parent_res.child_res = res_pr_pf_load.res_id)
JOIN V_RES_CAPA res_cap ON res_pr_pf_load.time_period = res_cap.time_period 
					   AND res_pr_pf_load.res_id = res_cap.res_id 
					   AND res_cap.sum_type = 'M';

GO

CREATE VIEW V_RES_PR_LOAD_SIMULATION AS 
select 
pf_id, 
pr_id,
main_pr_id, 
parent_pr_id,
pr_func_id, 
pr_report_id,
pr_prio,
parent_res_id as res_id, 
time_period, 
pr_req_end, 
pr_calc_end, 
pr_type,
sum(remaining) remaining,
sum(actual) actual,
sum(availability) "availability",
sum(baseload) baseload
from
v_res_pr_load_portfolio v_res
group by pf_id, pr_id, main_pr_id, parent_pr_id, pr_func_id, 
		 pr_report_id, pr_prio, parent_res_id, time_period, 
		 pr_req_end, pr_calc_end, pr_type;

GO
