-- show cost values with structured aggrgegation in status reports
update dt411 set DI000213 = '028510', DI000288=0 where DI000307='715591' and DI000306 = '047622';
update dt411 set DI000213 = '028515', DI000288=0 where DI000307='715592' and DI000306 = '047622';
update dt411 set DI000213 = '028520', DI000288=0 where DI000307='715593' and DI000306 = '047622';
update dt411 set DI000213 = '028525', DI000288=0 where DI000307='715594' and DI000306 = '047622';
update dt411 set DI000213 = '028530', DI000288=0 where DI000307='715595' and DI000306 = '047622';
update dt411 set DI000213 = '008606', DI000288=0 where DI000307='715596' and DI000306 = '047622';

update dt411 set DI000213 = '028511', DI000288=0 where DI000307='715597' and DI000306 = '047622';
update dt411 set DI000213 = '028516', DI000288=0 where DI000307='715598' and DI000306 = '047622';
update dt411 set DI000213 = '028521', DI000288=0 where DI000307='715599' and DI000306 = '047622';
update dt411 set DI000213 = '028526', DI000288=0 where DI000307='715600' and DI000306 = '047622';
update dt411 set DI000213 = '028531', DI000288=0 where DI000307='715601' and DI000306 = '047622';
update dt411 set DI000213 = '008607', DI000288=0 where DI000307='715602' and DI000306 = '047622';

update dt411 set DI000213 = '028512', DI000288=0 where DI000307='715603' and DI000306 = '047622';
update dt411 set DI000213 = '028517', DI000288=0 where DI000307='715604' and DI000306 = '047622';
update dt411 set DI000213 = '028522', DI000288=0 where DI000307='715605' and DI000306 = '047622';
update dt411 set DI000213 = '028527', DI000288=0 where DI000307='715606' and DI000306 = '047622';
update dt411 set DI000213 = '028532', DI000288=0 where DI000307='715607' and DI000306 = '047622';
update dt411 set DI000213 = '008608', DI000288=0 where DI000307='715608' and DI000306 = '047622';

update dt411 set DI000213 = '028513', DI000288=0 where DI000307='715615' and DI000306 = '047622';
update dt411 set DI000213 = '028518', DI000288=0 where DI000307='715616' and DI000306 = '047622';
update dt411 set DI000213 = '028523', DI000288=0 where DI000307='715618' and DI000306 = '047622';
update dt411 set DI000213 = '028528', DI000288=0 where DI000307='715619' and DI000306 = '047622';
update dt411 set DI000213 = '028533', DI000288=0 where DI000307='715619' and DI000306 = '047622';
update dt411 set DI000213 = '008609', DI000288=0 where DI000307='715620' and DI000306 = '047622';

-- fix effort aggregations to include subprojects
-- Aufwand-Soll: Intern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc

def computeOutput(di):   
    if ppms_cu.Helper.get_active_license_model().manual_cost_registration.get_value() == 0:
        column="di001509"
        qry_id = "000052"
    else:
        column="di041627"
        qry_id = "000369"
        
    pr_id = di.get_dtp_record().pr_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query(qry_id).format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("DI028310",)'
where DI000238 = '041470';

-- Aufwand-Ist: Intern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc

def computeOutput(di):   
    if ppms_cu.Helper.get_active_license_model().manual_cost_registration.get_value() == 0:
        column="di001510"
        qry_id = "000052"
    else:
        column="di041628"
        qry_id = "000369"
        
    pr_id = di.get_dtp_record().pr_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query(qry_id).format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("DI028311",)'
where DI000238 = '041471';

-- Aufwand-Rest: Intern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc

def computeOutput(di):   
    if ppms_cu.Helper.get_active_license_model().manual_cost_registration.get_value() == 0:
        column="di001511"
        qry_id = "000052"
    else:
        column="di041629"
        qry_id = "000369"
        
    pr_id = di.get_dtp_record().pr_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query(qry_id).format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("DI028312",)'
where DI000238 = '041472';


-- Aufwand-Gesamt: Intern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc

def computeOutput(di):   
    if ppms_cu.Helper.get_active_license_model().manual_cost_registration.get_value() == 0:
        column="di001511+di001510"
        qry_id = "000052"
    else:
        column="di041630"
        qry_id = "000369"
        
    pr_id = di.get_dtp_record().pr_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query(qry_id).format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("DI028313",)'
where DI000238 = '041473';


-- Aufwand-Gesamt: Extern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    if ppms_cu.Helper.get_active_license_model().manual_cost_registration.get_value() == 0:
        column="di001511+di001510"
        qry_id = "000059"
    else:
        column="di041630"
        qry_id = "000369"
        
    pr_id = di.get_dtp_record().pr_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query(qry_id).format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("DI028314",)'
where DI000238 = '041474';


-- Aufwand-Rest: Extern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    if ppms_cu.Helper.get_active_license_model().manual_cost_registration.get_value() == 0:
        column="di001511"
        qry_id = "000059"
    else:
        column="di041630"
        qry_id = "000369"
        
    pr_id = di.get_dtp_record().pr_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query(qry_id).format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("DI028315",)'
where DI000238 = '041475';


-- Aufwand-Ist: Extern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    if ppms_cu.Helper.get_active_license_model().manual_cost_registration.get_value() == 0:
        column="di001510"
        qry_id = "000059"
    else:
        column="di041630"
        qry_id = "000369"
        
    pr_id = di.get_dtp_record().pr_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query(qry_id).format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("DI028316",)'
where DI000238 = '041476';


-- Aufwand-Soll: Extern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    if ppms_cu.Helper.get_active_license_model().manual_cost_registration.get_value() == 0:
        column="di001509"
        qry_id = "000059"
    else:
        column="di041630"
        qry_id = "000369"
        
    pr_id = di.get_dtp_record().pr_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query(qry_id).format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("DI028317",)'
where DI000238 = '041477';


-- fix report effort dis

-- Aufwand-Soll: Extern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    column="di008660" 
    pr_id = di.get_dtp_record().pr_id.get_value()
    report_id = di.get_dtp_record().report_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query("000083").format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("",)'
where DI000238 = '057691';


-- Aufwand-Ist: Extern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    column="di008662"
    pr_id = di.get_dtp_record().pr_id.get_value()
    report_id = di.get_dtp_record().report_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query("000083").format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("",)'
where DI000238 = '057692';


-- Aufwand-Rest: Extern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    column="di008664"        
    pr_id = di.get_dtp_record().pr_id.get_value()
    report_id = di.get_dtp_record().report_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query("000083").format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("",)'
where DI000238 = '057693';


-- Aufwand-Gesamt: Extern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    column="di008662+di008664"       
    pr_id = di.get_dtp_record().pr_id.get_value()
    report_id = di.get_dtp_record().report_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query("000083").format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("",)'
where DI000238 = '057694';


-- Aufwand-Gesamt: Intern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    column="di008662+di008664"       
    pr_id = di.get_dtp_record().pr_id.get_value()
    report_id = di.get_dtp_record().report_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query("000090").format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("",)'
where DI000238 = '057695';


-- Aufwand-Gesamt: Intern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    column="di008662+di008664"       
    pr_id = di.get_dtp_record().pr_id.get_value()
    report_id = di.get_dtp_record().report_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query("000090").format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("",)'
where DI000238 = '057695';


-- Aufwand-Rest: Intern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    column="di008664"       
    pr_id = di.get_dtp_record().pr_id.get_value()
    report_id = di.get_dtp_record().report_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query("000090").format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("",)'
where DI000238 = '057696';


-- Aufwand-Ist: Intern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    column="di008662"       
    pr_id = di.get_dtp_record().pr_id.get_value()
    report_id = di.get_dtp_record().report_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query("000090").format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("",)'
where DI000238 = '057697';


-- Aufwand-Soll: Intern
update dt412 set DI027352 = 'from ppms import ppms_cu
from ppms.project_rights import get_pr_tree_desc
        
def computeOutput(di):   
    column="di008660"       
    pr_id = di.get_dtp_record().pr_id.get_value()
    report_id = di.get_dtp_record().report_id.get_value()
    pr_tree = get_pr_tree_desc(pr_id, "db")
    aggregated_value = ppms.db_select(ppms.get_query("000090").format(**locals()))
    if aggregated_value:
        return aggregated_value[0][0]
    else:
        return 0
        
computeOutput.deps = ("",)'
where DI000238 = '057698';


-- add structured aggregation to effort statements 

update dt343 set DI040109 = 'select sum({column})
from dt827
inner join dt467 on di008518=di001218
where di028798=0
and di003414 not in (''8'',''9'')
and di008516 in {pr_tree}
and di008535= ''{report_id}'''
where DI024373 = '000090';


update dt343 set DI040109 = 'select sum({column})
from dt472
inner join dt467 on di001517=di001218
inner join dt470 on di001518=di001366
Where Di028462=1
and di003414 not in (''8'',''9'')
and di001395 in {pr_tree}'
where DI024373 = '000059';


update dt343 set DI040109 = 'Select sum({column})*(Select di059356 from dt401 where di026548=1)
from dt861
where DI023119 in {pr_tree}
group by di023119'
where DI024373 = '000369';


update dt343 set DI040109 = 'select sum({column})
from dt472
inner join dt467 on di001517=di001218
inner join dt470 on di001518=di001366
Where Di028462=0
and di003414 not in (''8'',''9'')
and di001395 in {pr_tree}'
where DI024373 = '000052';


update dt343 set DI040109 = 'select sum({column})
from dt827
inner join dt467 on di008518=di001218
where di028798=1
and di008516 in {pr_tree}
and di008535 = ''{report_id}'''
where DI024373 = '000083';