# -*- coding: utf-8 -*-
from ppms import ppms
from ppms.migration import SQLPacket, PacketCategory    


class FixCostAggregation(SQLPacket):
    """Fix the cost+effort calculation to aggregate subproject data to the 
    project level. Also fix status reports customizing to show structured 
    cost+effort values"""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 13512

    FILENAME = 'wi13512_fix_cost_aggregation.sql'
    
    def fix(self, runner):
        
        m_cost_reg_ora = """MERGE INTO DT461
        USING (SELECT
                max('{pr_id}') pr_id,
                SUM(DI041635) kosten_soll_personal_intern,
                SUM(DI041640) kosten_soll_personal_extern,
                SUM(DI041645) kosten_soll_sachkosten,
                SUM(DI041650) kosten_soll_investitionen,
                SUM(DI041655) kosten_soll_sonstige,
                SUM(DI041636) kosten_ist_personal_intern,
                SUM(DI041641) kosten_ist_personal_extern,
                SUM(DI041646) kosten_ist_sachkosten,
                SUM(DI041651) kosten_ist_investitionen,
                SUM(DI041656) kosten_ist_sonstige,
                SUM(DI041637) kosten_rest_personal_intern,
                SUM(DI041642) kosten_rest_personal_extern,
                SUM(DI041647) kosten_rest_sachkosten,
                SUM(DI041652) kosten_rest_investitionen,
                SUM(DI041657) kosten_rest_sonstige,
                SUM(DI041638) obligo_personal_intern,
                SUM(DI041643) obligo_personal_extern,
                SUM(DI041648) obligo_sachkosten,
                SUM(DI041653) obligo_investitionen,
                SUM(DI041658) obligo_sonstige,
                SUM(nvl(DI041636,0)+nvl(DI041637,0)) kosten_gesamt_personal_intern,
                SUM(nvl(DI041641,0)+nvl(DI041642,0)) kosten_gesamt_personal_extern,
                SUM(nvl(DI041646,0)+nvl(DI041647,0)) kosten_gesamt_sachkosten,
                SUM(nvl(DI041651,0)+nvl(DI041652,0)) kosten_gesamt_investitionen,
                SUM(nvl(DI041656,0)+nvl(DI041657,0)) kosten_gesamt_sonstige,
                SUM(DI041660) erloese_soll,
                SUM(DI041661) erloese_ist,
                SUM(DI041662) erloese_rest,
                SUM(nvl(DI041661,0)+nvl(DI041662,0)) erloese_gesamt,
                --SPECIAL
                SUM(nvl(DI041635,0) + nvl(DI041640,0) + nvl(DI041645,0) + nvl(DI041650,0) + nvl(DI041655,0)) kosten_soll,
                SUM(nvl(DI041636,0) + nvl(DI041641,0) + nvl(DI041646,0) + nvl(DI041651,0) + nvl(DI041656,0)) kosten_ist,
                SUM(nvl(DI041637,0) + nvl(DI041642,0) + nvl(DI041647,0) + nvl(DI041652,0) + nvl(DI041657,0)) kosten_rest,
                SUM(nvl(DI041638,0) + nvl(DI041643,0) + nvl(DI041648,0) + nvl(DI041653,0) + nvl(DI041658,0)) obligo,
                SUM(nvl(DI041636,0) + nvl(DI041637,0) + nvl(DI041641,0) + nvl(DI041642,0) + nvl(DI041646,0) + nvl(DI041647,0) + nvl(DI041651,0) + nvl(DI041652,0) + nvl(DI041656,0) + nvl(DI041657,0)) kosten_gesamt,
                SUM(nvl(DI041629,0) + nvl(DI041633,0)) aufwand_rest,
                SUM(nvl(DI041627,0) + nvl(DI041631,0)) aufwand_soll,
                SUM(nvl(DI041628,0) + nvl(DI041632,0)) aufwand_ist
               FROM DT861 where DI023119 in {pr_tree})
        ON (pr_id = DT461.DI001001)
        WHEN MATCHED THEN UPDATE SET
            DI028310 = kosten_soll_personal_intern,
            DI028317 = kosten_soll_personal_extern,
            DI028320 = kosten_soll_sachkosten,
            DI028325 = kosten_soll_investitionen,
            DI028330 = kosten_soll_sonstige,
            DI028311 = kosten_ist_personal_intern,
            DI028316 = kosten_ist_personal_extern,
            DI028321 = kosten_ist_sachkosten,
            DI028326 = kosten_ist_investitionen,
            DI028331 = kosten_ist_sonstige,
            DI028312 = kosten_rest_personal_intern,
            DI028315 = kosten_rest_personal_extern,
            DI028322 = kosten_rest_sachkosten,
            DI028327 = kosten_rest_investitionen,
            DI028332 = kosten_rest_sonstige,
            DI041540 = obligo_personal_intern,
            DI041541 = obligo_personal_extern,
            DI041542 = obligo_sachkosten,
            DI041543 = obligo_investitionen,
            DI041544 = obligo_sonstige,
            DI028313 = kosten_gesamt_personal_intern,
            DI028314 = kosten_gesamt_personal_extern,
            DI028323 = kosten_gesamt_sachkosten,
            DI028328 = kosten_gesamt_investitionen,
            DI028333 = kosten_gesamt_sonstige,
            DI028338 = erloese_soll,
            DI028339 = erloese_ist,
            DI028340 = erloese_rest,
            DI028341 = erloese_gesamt,
             -- SPECIAL
            DI000661 = CASE WHEN ({man_cost_registration} = 1) THEN kosten_soll ELSE DI000661 END,
            DI000662 = CASE WHEN ({man_cost_registration} = 1) THEN kosten_ist ELSE DI000662 END,
            DI000673 = CASE WHEN ({man_cost_registration} = 1) THEN kosten_rest ELSE DI000673 END,
            DI041545 = CASE WHEN ({man_cost_registration} = 1) THEN obligo ELSE DI041545 END,
            DI000674 = CASE WHEN ({man_cost_registration} = 1) THEN kosten_gesamt ELSE DI000674 END,
            DI001039 = CASE WHEN ({man_cost_registration} = 1) THEN aufwand_rest ELSE DI001039 END,
            DI001037 = CASE WHEN ({man_cost_registration} = 1) THEN aufwand_soll ELSE DI001037 END,
            DI001038 = CASE WHEN ({man_cost_registration} = 1) THEN aufwand_ist ELSE DI001038 END"""

        m_cost_reg_mssql = """MERGE INTO DT461
            USING (SELECT
                    max('{pr_id}') pr_id,
                    SUM(DI041635) kosten_soll_personal_intern,
                    SUM(DI041640) kosten_soll_personal_extern,
                    SUM(DI041645) kosten_soll_sachkosten,
                    SUM(DI041650) kosten_soll_investitionen,
                    SUM(DI041655) kosten_soll_sonstige,
                    SUM(DI041636) kosten_ist_personal_intern,
                    SUM(DI041641) kosten_ist_personal_extern,
                    SUM(DI041646) kosten_ist_sachkosten,
                    SUM(DI041651) kosten_ist_investitionen,
                    SUM(DI041656) kosten_ist_sonstige,
                    SUM(DI041637) kosten_rest_personal_intern,
                    SUM(DI041642) kosten_rest_personal_extern,
                    SUM(DI041647) kosten_rest_sachkosten,
                    SUM(DI041652) kosten_rest_investitionen,
                    SUM(DI041657) kosten_rest_sonstige,
                    SUM(DI041638) obligo_personal_intern,
                    SUM(DI041643) obligo_personal_extern,
                    SUM(DI041648) obligo_sachkosten,
                    SUM(DI041653) obligo_investitionen, 
                    SUM(DI041658) obligo_sonstige,
                    SUM(ISNULL(DI041636,0)+ISNULL(DI041637,0)) kosten_gesamt_personal_intern,
                    SUM(ISNULL(DI041641,0)+ISNULL(DI041642,0)) kosten_gesamt_personal_extern,
                    SUM(ISNULL(DI041646,0)+ISNULL(DI041647,0)) kosten_gesamt_sachkosten,
                    SUM(ISNULL(DI041651,0)+ISNULL(DI041652,0)) kosten_gesamt_investitionen,
                    SUM(ISNULL(DI041656,0)+ISNULL(DI041657,0)) kosten_gesamt_sonstige,
                    SUM(DI041660) erloese_soll,
                    SUM(DI041661) erloese_ist,
                    SUM(DI041662) erloese_rest,
                    SUM(ISNULL(DI041661,0)+ISNULL(DI041662,0)) erloese_gesamt,
                    --SPECIAL
                    SUM(ISNULL(DI041635,0) + ISNULL(DI041640,0) + ISNULL(DI041645,0) + ISNULL(DI041650,0) + ISNULL(DI041655,0)) kosten_soll,
                    SUM(ISNULL(DI041636,0) + ISNULL(DI041641,0) + ISNULL(DI041646,0) + ISNULL(DI041651,0) + ISNULL(DI041656,0)) kosten_ist,
                    SUM(ISNULL(DI041637,0) + ISNULL(DI041642,0) + ISNULL(DI041647,0) + ISNULL(DI041652,0) + ISNULL(DI041657,0)) kosten_rest,
                    SUM(ISNULL(DI041638,0) + ISNULL(DI041643,0) + ISNULL(DI041648,0) + ISNULL(DI041653,0) + ISNULL(DI041658,0)) obligo,
                    SUM(ISNULL(DI041636,0) + ISNULL(DI041637,0) + ISNULL(DI041641,0) + ISNULL(DI041642,0) + ISNULL(DI041646,0) + ISNULL(DI041647,0) + ISNULL(DI041651,0) + ISNULL(DI041652,0) + ISNULL(DI041656,0) + ISNULL(DI041657,0)) kosten_gesamt,
                    SUM(ISNULL(DI041629,0) + ISNULL(DI041633,0)) aufwand_rest,
                    SUM(ISNULL(DI041627,0) + ISNULL(DI041631,0)) aufwand_soll,
                    SUM(ISNULL(DI041628,0) + ISNULL(DI041632,0)) aufwand_ist
                   FROM DT861
                   where DI023119 in {pr_tree}) AS DT861_TEMP
            ON DT861_TEMP.pr_id = DI001001
            WHEN MATCHED THEN UPDATE SET
                DI028310 = kosten_soll_personal_intern,
                DI028317 = kosten_soll_personal_extern,
                DI028320 = kosten_soll_sachkosten,
                DI028325 = kosten_soll_investitionen,
                DI028330 = kosten_soll_sonstige,
                DI028311 = kosten_ist_personal_intern,
                DI028316 = kosten_ist_personal_extern,
                DI028321 = kosten_ist_sachkosten,
                DI028326 = kosten_ist_investitionen,
                DI028331 = kosten_ist_sonstige,
                DI028312 = kosten_rest_personal_intern,
                DI028315 = kosten_rest_personal_extern,
                DI028322 = kosten_rest_sachkosten,
                DI028327 = kosten_rest_investitionen,
                DI028332 = kosten_rest_sonstige,
                DI041540 = obligo_personal_intern,
                DI041541 = obligo_personal_extern,
                DI041542 = obligo_sachkosten,
                DI041543 = obligo_investitionen,
                DI041544 = obligo_sonstige,
                DI028313 = kosten_gesamt_personal_intern,
                DI028314 = kosten_gesamt_personal_extern,
                DI028323 = kosten_gesamt_sachkosten,
                DI028328 = kosten_gesamt_investitionen,
                DI028333 = kosten_gesamt_sonstige,
                DI028338 = erloese_soll,
                DI028339 = erloese_ist,
                DI028340 = erloese_rest,
                DI028341 = erloese_gesamt,
                -- SPECIAL
                DI000661 = CASE WHEN ({man_cost_registration} = 1) THEN kosten_soll ELSE DI000661 END,
                DI000662 = CASE WHEN ({man_cost_registration} = 1) THEN kosten_ist ELSE DI000662 END,
                DI000673 = CASE WHEN ({man_cost_registration} = 1) THEN kosten_rest ELSE DI000673 END,
                DI041545 = CASE WHEN ({man_cost_registration} = 1) THEN obligo ELSE DI041545 END,
                DI000674 = CASE WHEN ({man_cost_registration} = 1) THEN kosten_gesamt ELSE DI000674 END,
                DI001039 = CASE WHEN ({man_cost_registration} = 1) THEN aufwand_rest ELSE DI001039 END,
                DI001037 = CASE WHEN ({man_cost_registration} = 1) THEN aufwand_soll ELSE DI001037 END,
                DI001038 = CASE WHEN ({man_cost_registration} = 1) THEN aufwand_ist ELSE DI001038 END;"""
        
        mssql_stmt_rec = ppms.search_record(343, ['000004','000368'],
                                            [40109], True)
        mssql_stmt_rec.source_text_clob.set_value(m_cost_reg_mssql)
        mssql_stmt_rec.save()
         
        ora_stmt_rec = ppms.search_record(343, ['000002','000368'],
                                            [40109], True)
        ora_stmt_rec.source_text_clob.set_value(m_cost_reg_ora)
        ora_stmt_rec.save()

        self.apply_dml_file(self.sql_planta_de_ansi, self.FILENAME)
        self.apply_dml_file(self.sql_planta_de_dbms, self.FILENAME)
        
        return self.success()