# -*- coding: utf-8 -*-
from ppms import ppms
from ppms.migration import ChangeDTPPacket, PacketCategory, MinVersionDependency, MaxVersionDependency

class RelationProjectToPortfolio(ChangeDTPPacket):
    '''Inserts a relation between project and portfolio report


    An update, which inserts a new relation in DT093 to DT461. 
    The relation prevents a project for deletion when it's assigned 
    to a portfolio report. Otherwise the portfolio report can't 
    fetch the needed data, because assigned project reports are 
    deleted together with the project.
    '''
    wi_number = 13418  
    version = 1
    done_after_success = True  
    run_on_startup = True  
    category = PacketCategory.CATEGORY_MANDATORY  
    
    def get_dependencies(self):
        return (MinVersionDependency(version='39.5.5'),
                MaxVersionDependency(version='39.5.5'))

    def fix(self, runner):
        # Insert a new relation for dt461 into dt093
        self.log("Insert a new relation for dt461 into dt093")
        
        errors = False
        relation_dict = {'dt_id': '093',
                         'index': '461',
                         'id_from_di': '062206',
                         'id_to_di': '062206',
                         'relationship': 'N:1',
                         'delete_flag': 2,
                         'implicit_creation': 0,
                         'recursion_type': 0,
                         'customizer_class': '*',
                         'variable_name': 'ID461',
                         'delete_flag_save': 0}
                         
        relation_dtp = ppms.search_record(416, [relation_dict['dt_id'], relation_dict['index'], relation_dict['id_from_di']], ['delete_flag'])
        if relation_dtp:
            if relation_dtp.delete_flag.get_value() == 2:
                self.log("Relation is already set. Nothing to do.")
                return self.success()

            else:
                self.log("Relation already exists, but the delete flag isn't set yet.")
                relation_dtp.delete_flag.set_value(2)
        else:
            relation_dtp = ppms.create_record(416, relation_dict)
        
        if relation_dtp:
            check_record = relation_dtp.save()
            if check_record:
                self.log("Relation was successfully saved.")
                return self.success()

        errors = True
        error_msg = "Relation wasn't created. Migration package failed."
        
        if errors:
            return self.fail(error_msg)

        return self.success()
        
class ExtendDeleteProjectFunction(ChangeDTPPacket):
    '''Extends the function for deleting a project


    An update, which extends the function for deleting a project
    by inserting a check, if the project is dedicated to a portfolio.
    Projects which are assigned to portfolios or their reports 
    must not be deleted. 
    '''
    wi_number = 13418  
    done_after_success = True
    run_on_startup = True  
    category = PacketCategory.CATEGORY_MANDATORY 
    
    def get_dependencies(self):
        return (MinVersionDependency(version='39.5.5'),
                MaxVersionDependency(version='39.5.5'))

    def fix(self, runner):
        procedure_id = "000919"
        sql_code = "Select Count(*) From DT093 Where DI062206 = '{0}'"
        errors = 0
        macro_id = "009A9W"
        dialog_id = "1056"
        
        sql_procedure_dict = {"created_on": ppms.uvar_get("@15"),
                              "modified_on": ppms.uvar_get("@15"),
                              "rwdu": 7,
                              "created_by": "MIGRATION",
                              "modified_by": "MIGRATION",
                              "own_lic": "011",
                              "opc": "",
                              "owner": 7,
                              "group": 7,
                              "any": 7,
                              "name": "Delete projects: Check for projects in portfolio reports"}
                              
        sql_code_dict = {"counter_id": "-",
                         "procedure_id": procedure_id,
                         "created_on": ppms.uvar_get("@15"),
                         "modified_on": ppms.uvar_get("@15"),
                         "rwdu": 7,
                         "created_by": "MIGRATION",
                         "modified_by": "MIGRATION",
                         "own_lic": "011",
                         "opc": "",
                         "owner": 7,
                         "group": 7,
                         "any": 7,
                         "source_text_clob": sql_code}
                         
        dialog_dict = {"created_on": ppms.uvar_get("@15"),
                       "modified_on": ppms.uvar_get("@15"),
                       "rwdu": 7,
                       "created_by": "MIGRATION",
                       "modified_by": "MIGRATION",
                       "own_lic": "011",
                       "opc": "",
                       "owner": 7,
                       "group": 7,
                       "any": 7,
                       "output_type": 0,
                       "button_type": 1,
                       "default_button": 1,
                       "beep": 0,
                       "help": 0,
                       "item": 0,
                       "group_2": "0",
                       "dialog_message": """Ein Statusbericht dieses Planungsobjekts wird in einem Portfolio-Statusbericht verwendet. Löschen nicht möglich!""",
                       "status": 0,
                       "programming_use": 0,
                       "dialog_message_2": "0",
                       "password_field": 0,
                       "output_type_2": 1,
                       "ole_symbol": "000205",
                       "no_longer_display": 0,
                       "use_in_source": 0}
        
        # Insert a new SQL statement
        self.log("Insert a new SQL statement")
        
        procedure_dtp = ppms.search_record(342, [procedure_id], ['procedure_id'])
        
        if procedure_dtp:
            self.log("Found an existing SQL statement with id: {0}".format(procedure_id))
            procedure_code_list = procedure_dtp.get_children(343, di_list=["counter_id", "source_text_clob"])
            
            for code_dtp in procedure_code_list:
                code_source = code_dtp.source_text_clob.get_value()

                if code_source == sql_code:
                    self.log("SQL statement already exists. Nothing to do.")

                else:
                    self.log("SQL statement id is used by another statement or the statement was edited.") 
                    self.log("Creating a new SQL statement.")
                    new_sql_dtp = ppms.create_record(342, sql_procedure_dict)

                    if new_sql_dtp:
                        check_save = new_sql_dtp.save()

                        if check_save:
                            procedure_id = new_sql_dtp.procedure_id.get_value()
                            self.log("The new SQL statement is: {0}".format(procedure_id))

                        else:
                            message = "Error while saving the new SQL statement. Task was aborted!"
                            return self.fail(message)

                    else:
                        message = "Error while creating a new SQL statemnt dtp record. Task was aborted!"
                        return self.fail(message)
                        
                    if procedure_id:                
                        new_code_dtp = ppms.create_record(343, sql_code_dict)

                        if new_code_dtp:
                            check_save = new_code_dtp.save()

                            if check_save:
                                self.log("The SQL code for the new SQL statement was inserted successfully")

                            else:
                                message = "Error while saving the new SQL code. Task was aborted!"
                                return self.fail(message)

                        else:
                            message = "Error while creating a new SQL code dtp record. Task was aborted!"
                            return self.fail(message)
        else:
            self.log("SQL statement does not yet exist.")
            
            sql_procedure_dict["procedure_id"] = procedure_id
            new_sql_dtp = ppms.create_record(342, sql_procedure_dict)

            if new_sql_dtp:
                check_save = new_sql_dtp.save()

                if check_save:
                    self.log("Successfully inserted new SQL statement.")

                else:
                    message = "Error while saving new SQL statement. Task was aborted!"
                    return self.fail(message)

            else:
                message = "Error while creating new SQL statement DTP record. Task was aborted!"
                return self.fail(message)
                
            new_code_dtp = ppms.create_record(343, sql_code_dict)

            if new_code_dtp:
                check_save = new_code_dtp.save()

                if check_save:
                    self.log("Successfully inserted new SQL code.")

                else:
                    message = "Error while saving new SQL code. Task was aborted!"
                    return self.fail(message)

            else:
                message = "Error while creating new SQL code DTP record. Task was aborted!"
                return self.fail(message) 
                
        # Insert new dialog 
        self.log("Insert a new dialog message for the delete function")
        
        dialog_dtp = ppms.search_record(428, [dialog_id], ['dialog_message_id', 'dialog_message'])
        
        if dialog_dtp:
            dialog_message = dialog_dtp.dialog_message.get_value()

            if dialog_message == dialog_dict["dialog_message"]:
                self.log("Dialog message already exists. Nothing to do!")

            else:
                self.log("Dialog id is already in use by another dialog or the dialog was edited.")
                self.log("Creating a new dialog message record.")
                
                new_dialog_dtp = ppms.create_record(428, dialog_dict)
                if new_dialog_dtp:
                    check_save = new_dialog_dtp.save()

                    if check_save:
                        dialog_id = new_dialog_dtp.dialog_message_id.get_value()
                        self.log("Successfully inserted a new dialog message!")

                    else:
                        message = "Error while saving the new dialog message. Task was aborted!"
                        return self.fail(message)

                else:
                    message = "Error while creating a new dialog message. Task was aborted!"
                    return self.fail(message)
        else:
            dialog_dict["dialog_message_id"] = dialog_id
            
            new_dialog_dtp = ppms.create_record(428, dialog_dict)
            if new_dialog_dtp:
                check_save = new_dialog_dtp.save()
                if check_save:
                    self.log("Successfully inserted the dialog message!")
                else:
                    message = "Error while saving the dialog message. Task was aborted!"
                    return self.fail(message)
            else:
                message = "Error while creating the dialog message dtp record. Task was aborted!"
                return self.fail(message) 

        # Edit macro 
        self.log("Edit macro for deleting projects.")
        
        macro_list = ["""def user_has_permission_to_delete(pr_list):
    user_rec = ppms.search_record(511, [ppms.uvar_get("@1")], [10624], True)
    if user_rec:
        user_rights = user_rec.user_rights.get_value() 
    
    counter=0
    
    for pr_id in pr_list:
        rec461=ppms.search_record(461, [pr_id], [59206], True)
        if rec461:
            if rec461.no_of_actual_hours.get_value() > 0:
                counter+=1
    
    if counter > 0:
        if user_rights in (32,35):
            return True
        else:
            return False
    else:
        return True

def delete_project():
    ppms.echo_off()
    da_names=["delete_tasks"]
    mark_records_with_childs(mod_obj,da_names)
    
    ppms.msg_off()
    mod_obj.menu(40)
        
    da_names=["delete_project","delete_simulation","structure_tasks","assign_programm"]
    mark_records_with_childs(mod_obj,da_names)
    mod_obj.menu(12)
    mod_obj.menu(40)
    
    ppms.msg_on()

def mark_records_with_childs(mod_obj,da_names=[]):
    da_cu_id_list=[]
    if len(da_names) >0:
        for da_name in da_names:
            da_cu_id_list.append(mod_obj.__getattr__(da_name).get_customizing().get_id())
            da_childs=mod_obj.__getattr__(da_name).get_customizing().get_children()
            while len(da_childs) >0:
                for da_child in da_childs:
                    da_childs=da_child.get_children()
                    da_cu_id_list.append(da_child.get_id())

        for da_obj in mod_obj.get_das():
            if da_obj.get_customizing().get_id() in da_cu_id_list:
                [da_rec.mark() for da_rec in da_obj.get_records()]
   
def invoke_deletion():  
    if mod_obj.assign_programm_report.count_records():
        ppms.ui_message_id("1021")
        return

    # Message delete?
    ppms.ui_message_id("0438")
    reply=ppms.msg_pop().get_reply()
    if reply == 1:
        #Delete Single project
        if ppms.get_context_df().get_customizing_name() == "delete_single_project":
            inv_pr_id=inv_rec.pr_id.get_raw_value()
            parent_pr_id=inv_rec.parent_pr_id.get_raw_value()
            parent_task_id=inv_rec.parent_task_id.get_raw_value()
            
            mod_obj.set_current_L_var(17, [inv_rec.pr_id.get_raw_value()])
            mod_obj.set_current_L_var(18, [parent_pr_id])
            mod_obj.set_current_L_var(33, [parent_task_id])
            # Check sub projects
            sql=ppms.get_query("000270").format(inv_rec.pr_id.get_raw_value())
            sub_res=ppms.db_select(sql)

            if sub_res[0][0] > 0:
                ppms.ui_message_id("0176")
            else:
                if user_has_permission_to_delete([inv_rec.pr_id.get_raw_value()]) == True:
                    mod_obj.menu(12)
                    if inv_rec.parent_task_id.get_raw_value() != "-":
                        inv_rec.parent_task_id.set_raw_value("-")
                    if inv_rec.parent_pr_id.get_raw_value() != "-":
                        inv_rec.parent_pr_id.set_raw_value("-")
                    mod_obj.menu(34)
                    delete_project()
                    if mod_obj.get_panel().get_main_module().pr_id == inv_pr_id: mod_obj.get_panel().get_main_module().menu(49)
                else:
                    ppms.ui_message_id("0051")
                

        #Delete project structure
        elif ppms.get_context_df().get_customizing_name() == "delete_project_structure":
            pr_list=[]
            [pr_list.append(pr_rec.pr_id.get_raw_value()) for pr_rec in mod_obj.projects.get_records()]
            mod_obj.set_current_L_var(17, pr_list)
            
            if user_has_permission_to_delete(pr_list) == True:
                #get project by level
                pr_structure_by_level=ppms.db_select(ppms.get_query("000271").format(mod_obj.projects.get_records()[0].main_pr_id.get_raw_value()))
                for pr_id in pr_structure_by_level:
                    mod_obj.set_current_L_var(17, [pr_id[0]])
                    mod_obj.menu(12)
                    for pr_rec in mod_obj.get_da("delete_project").get_records():
                        if pr_rec.parent_task_id.get_raw_value() != "-":
                            pr_rec.parent_task_id.set_raw_value("-")
                        if pr_rec.parent_pr_id.get_raw_value() != "-":
                            pr_rec.parent_pr_id.set_raw_value("-")
                    mod_obj.menu(34)
                delete_project()
                mod_obj.get_panel().get_main_module().menu(49)
            else:
                ppms.ui_message_id("0051")

        ppms.echo_on()
        
def check_portfolios():
    pr_list=[]
    if ppms.get_context_df().get_customizing_name() == "delete_project_structure":
        for pr_rec in mod_obj.projects.get_records():
            pr_list.append(pr_rec.pr_id.get_raw_value())
        
    if ppms.get_context_df().get_customizing_name() == "delete_single_project":
        pr_id = inv_rec.pr_id.get_raw_value()
        pr_list.append(pr_id)
        
    for pr_id in pr_list:
        pr_select = ppms.get_query("000919").format(pr_id)
        pr_answer = ppms.db_select(pr_select)
        
        if pr_answer:
            pr_count = pr_answer[0][0]
            if pr_count:
                ppms.ui_message_id("1056")
                return False
                
    return True
        
# Moduldaten
mod_obj=ppms.get_target_module()
mod_obj.set_current_L_var(17, [])
inv_rec=ppms.get_context_df().get_record()
if check_portfolios():
    invoke_deletion()""", """def user_has_permission_to_delete(pr_list):
    user_rec = ppms.search_record(511, [ppms.uvar_get("@1")], [10624], True)
    if user_rec:
        user_rights = user_rec.user_rights.get_value() 
    
    counter=0
    
    for pr_id in pr_list:
        rec461=ppms.search_record(461, [pr_id], [59206], True)
        if rec461:
            if rec461.no_of_actual_hours.get_value() > 0:
                counter+=1
    
    if counter > 0:
        if user_rights in (32,35):
            return True
        else:
            return False
    else:
        return True

def delete_project():
    ppms.echo_off()
    da_names=["delete_tasks"]
    mark_records_with_childs(mod_obj,da_names)
    
    ppms.msg_off()
    mod_obj.menu(40)
        
    da_names=["delete_project","delete_simulation","structure_tasks","assign_programm"]
    mark_records_with_childs(mod_obj,da_names)
    mod_obj.menu(12)
    mod_obj.menu(40)
    
    ppms.msg_on()

def mark_records_with_childs(mod_obj,da_names=[]):
    da_cu_id_list=[]
    if len(da_names) >0:
        for da_name in da_names:
            da_cu_id_list.append(mod_obj.__getattr__(da_name).get_customizing().get_id())
            da_childs=mod_obj.__getattr__(da_name).get_customizing().get_children()
            while len(da_childs) >0:
                for da_child in da_childs:
                    da_childs=da_child.get_children()
                    da_cu_id_list.append(da_child.get_id())

        for da_obj in mod_obj.get_das():
            if da_obj.get_customizing().get_id() in da_cu_id_list:
                [da_rec.mark() for da_rec in da_obj.get_records()]
   
def invoke_deletion():  
    if mod_obj.assign_programm_report.count_records():
        ppms.ui_message_id("1021")
        return

    # Message delete?
    ppms.ui_message_id("0438")
    reply=ppms.msg_pop().get_reply()
    if reply == 1:
        #Delete Single project
        if ppms.get_context_df().get_customizing_name() == "delete_single_project":
            inv_pr_id=inv_rec.pr_id.get_raw_value()
            parent_pr_id=inv_rec.parent_pr_id.get_raw_value()
            parent_task_id=inv_rec.parent_task_id.get_raw_value()
            
            mod_obj.set_current_L_var(17, [inv_rec.pr_id.get_raw_value()])
            mod_obj.set_current_L_var(18, [parent_pr_id])
            mod_obj.set_current_L_var(33, [parent_task_id])
            # Check sub projects
            sql=ppms.get_query("000270").format(inv_rec.pr_id.get_raw_value())
            sub_res=ppms.db_select(sql)

            if sub_res[0][0] > 0:
                ppms.ui_message_id("0176")
            else:
                if user_has_permission_to_delete([inv_rec.pr_id.get_raw_value()]) == True:
                    mod_obj.menu(12)
                    if inv_rec.parent_task_id.get_raw_value() != "-":
                        inv_rec.parent_task_id.set_raw_value("-")
                    if inv_rec.parent_pr_id.get_raw_value() != "-":
                        inv_rec.parent_pr_id.set_raw_value("-")
                    mod_obj.menu(34)
                    delete_project()
                    if mod_obj.get_panel().get_main_module().pr_id == inv_pr_id: mod_obj.get_panel().get_main_module().menu(49)
                else:
                    ppms.ui_message_id("0051")
                

        #Delete project structure
        elif ppms.get_context_df().get_customizing_name() == "delete_project_structure":
            pr_list=[]
            [pr_list.append(pr_rec.pr_id.get_raw_value()) for pr_rec in mod_obj.projects.get_records()]
            mod_obj.set_current_L_var(17, pr_list)
            
            if user_has_permission_to_delete(pr_list) == True:
                #get project by level
                pr_structure_by_level=ppms.db_select(ppms.get_query("000271").format(mod_obj.projects.get_records()[0].main_pr_id.get_raw_value()))
                for pr_id in pr_structure_by_level:
                    mod_obj.set_current_L_var(17, [pr_id[0]])
                    mod_obj.menu(12)
                    for pr_rec in mod_obj.get_da("delete_project").get_records():
                        if pr_rec.parent_task_id.get_raw_value() != "-":
                            pr_rec.parent_task_id.set_raw_value("-")
                        if pr_rec.parent_pr_id.get_raw_value() != "-":
                            pr_rec.parent_pr_id.set_raw_value("-")
                    mod_obj.menu(34)
                delete_project()
                mod_obj.get_panel().get_main_module().menu(49)
            else:
                ppms.ui_message_id("0051")

        ppms.echo_on()
        
def check_portfolios():
    pr_list=[]
    if ppms.get_context_df().get_customizing_name() == "delete_project_structure":
        for pr_rec in mod_obj.projects.get_records():
            pr_list.append(pr_rec.pr_id.get_raw_value())
        
    if ppms.get_context_df().get_customizing_name() == "delete_single_project":
        pr_id = inv_rec.pr_id.get_raw_value()
        pr_list.append(pr_id)
        
    for pr_id in pr_list:
        pr_select = ppms.get_query("{0}").format(pr_id)
        pr_answer = ppms.db_select(pr_select)
        
        if pr_answer:
            pr_count = pr_answer[0][0]
            if pr_count:
                ppms.ui_message_id("{1}")
                return False
                
    return True
        
# Moduldaten
mod_obj=ppms.get_target_module()
mod_obj.set_current_L_var(17, [])
inv_rec=ppms.get_context_df().get_record()
if check_portfolios():
    invoke_deletion()""".format(procedure_id, dialog_id)]
        
        module_dtp = self.get_record(405, [macro_id], ['py_macro_text'])
        check_value = self.change_value(module_dtp, 'py_macro_text', macro_list[0], macro_list[1])
        if not check_value:
            errors += 1
        
        if errors:
            return self.fail("Encountered errors while editing the delete macro. Task was aborted!")

        return self.success()