from ppms.migration import SQLPacket, ChangeDTPPacket, PacketCategory, MinVersionDependency, MaxVersionDependency, VersionType


CHANGES = ({'table': 405,
            'primary_key': ['009A64'],
            'dataitem': 'py_macro_text',
            'code': {'old': '''from ppms.exits import exit_change_id as eci
from ppms import ppms_cu
from ppms import project_rights

#Get macro module
macro_mod = ppms.get_macro_module()

#Get invoker module
inv_rec = macro_mod.get_invoker_rec()

#Get invoker datafield
inv_df = inv_rec.get_df('button_exec')

old_value = [inv_rec.res_id.get_text_value()]

new_value = [inv_rec.new_res_id.get_text_value()]

source_dt = inv_rec.source_dt.get_text_value()

target_dt = inv_rec.target_dt.get_text_value()

target_di = inv_rec.target_di.get_text_value()

df_config_value = inv_df.get_df_config()

exec(df_config_value.replace("\\r\\n","\\n"))

#Check DI000075/KR momentan aktiv at DT401
KR_akt = project_rights.check_for_active_planing()

if old_value == [""] or new_value == [""]:
    #Show Message "Bitte zuerst die Ressourcen auswaehlen!" if no ressource has been set
    ppms.ui_message_id('0122')    

elif KR_akt == False:
    
    #Set DI000075 to -1 --> Lock KR for other users
    active_license_model=ppms_cu.Helper.get_active_license_model()
    if active_license_model:
        active_license_model.cs_currently_active.set_value() = -1
        active_license_model.save()

    #Initialize list of update statements
    sql_dt_updates = []

    if source_dt == "" and target_dt == "" and target_di== "":
        #Start exchange
        for dt, dataset in dt_dict.items():
            cri = eci.change_id(dataset, dt, old_value, new_value)
            sql_dt_updates = sql_dt_updates + cri.sql_dt_updates
    else:
        #Start specific exchange
        cri = eci.change_id(target_dt + " " + target_di, source_dt, old_value, new_value)
        sql_dt_updates = cri.sql_dt_updates
    
    #Execute sql statements
    ac = ppms_cu.Action()
    dict_result = ac.exec_sql_modify_transaction(sql_dt_updates)


    #Preparing dict_result for report message
    message = """Table        affected rows
--------    ------------------"""

    for dt, result in dict_result.items():
        message = message + "\\n" + str(dt) + "      " + str(result)
    
    #Set DI000075 to 0 --> Unlock KR for other users
    if active_license_model:
        active_license_model.cs_currently_active.set_value() = 0
        active_license_model.save()
    #Commit UPDATE statement
    ppms.db_commit()
        
    #Show report message    
    ppms.ui_message_box("Report Ress.-ID switch: " + old_value[0] + " --> " + new_value[0], message)

else:
    #Show Message "Terminrechnung ist gerade aktiv" if DI000075 is not 0
    ppms.ui_message_id('0200')''',
                     'new': '''from ppms.exits import exit_change_id as eci
from ppms import ppms_cu
from ppms import project_rights
from ppms.constants import *

#Get macro module
macro_mod = ppms.get_macro_module()

#Get invoker module
inv_rec = macro_mod.get_invoker_rec()

#Get invoker datafield
inv_df = inv_rec.get_df('button_exec')

old_value = [inv_rec.res_id.get_text_value()]

new_value = [inv_rec.new_res_id.get_text_value()]

source_dt = inv_rec.source_dt.get_text_value()

target_dt = inv_rec.target_dt.get_text_value()

target_di = inv_rec.target_di.get_text_value()

df_config_value = inv_df.get_df_config()

DBMS = ppms.db_get_dbms_name()

exec(df_config_value.replace("\\r\\n","\\n"))

#Check DI000075/KR momentan aktiv at DT401
KR_akt = project_rights.check_for_active_planing()


def start_id_change():
    if old_value == [""] or new_value == [""]:
        #Show Message "Bitte zuerst die Ressourcen auswaehlen!" if no ressource has been set
        ppms.ui_message_id('0122')    

    elif KR_akt == False:
        
        #Set DI000075 to -1 --> Lock KR for other users
        active_license_model=ppms_cu.Helper.get_active_license_model()
        if active_license_model:
            active_license_model.cs_currently_active.set_value(-1)
            active_license_model.save()

        #Initialize list of update statements
        sql_dt_updates = []

        if source_dt == "" and target_dt == "" and target_di== "":
            #Start exchange
            for dt, dataset in dt_dict.items():
                cri = eci.change_id(dataset, dt, old_value, new_value)
                sql_dt_updates = sql_dt_updates + cri.sql_dt_updates
        else:
            #Start specific exchange
            cri = eci.change_id(target_dt + " " + target_di, source_dt, old_value, new_value)
            sql_dt_updates = cri.sql_dt_updates
        
        #Set all constraints deferred, so foreign key updates won't fail
        #ppms.db_modify(ppms.get_query("000892"))
        
        #Execute sql statements
        ac = ppms_cu.Action()
        dict_result = ac.exec_sql_modify_transaction(sql_dt_updates)

        #Set all constraints immediate again
        #ppms.db_modify(ppms.get_query("000893"))
        
        #Preparing dict_result for report message
        message = """Table        affected rows
    --------    ------------------"""

        for dt in sorted(dict_result.keys()):
            result = str(dict_result[dt])
            dt = str(dt)
            message = "{message}\\n{dt}      {result}".format(**locals())
        
        #Set DI000075 to 0 --> Unlock KR for other users
        if active_license_model:
            active_license_model.cs_currently_active.set_value(0)
            active_license_model.save()
        
        #Show report message    
        ppms.ui_message_box("Report Ress.-ID switch: " + old_value[0] + " --> " + new_value[0], message)

    else:
        #Show Message "Terminrechnung ist gerade aktiv" if DI000075 is not 0
        ppms.ui_message_id('0200')
        
if DBMS == DBMS_ORACLE:
    start_id_change()
else:
    ppms.ui_message_id('1080')'''}},
            {'table': 405,
            'primary_key': ['009A84'],
            'dataitem': 'py_macro_text',
            'code': {'old': '''from ppms import ppms
from ppms.exits import exit_change_id as eci
from ppms import ppms_cu

#Get macro module
macro_mod = ppms.get_macro_module()

#Get invoker module
inv_rec = macro_mod.get_invoker_rec()

#Get invoker datafield
inv_df = inv_rec.get_df('button_exec')

old_value = [inv_rec.res_id.get_text_value()]

new_value = [inv_rec.new_res_id.get_text_value()]

source_dt = inv_rec.source_dt.get_text_value()

target_dt = inv_rec.target_dt.get_text_value()

target_di = inv_rec.target_di.get_text_value()

df_config_value = inv_df.get_df_config()

exec(df_config_value.replace("\\r\\n","\\n"))

#Initialize list of update statements
sql_dt_updates = []

if old_value == [""] or new_value == [""]:
    #Show Message "Bitte zuerst die Ressourcen auswaehlen!" if no ressource has been set
    ppms.ui_message_id('0122')    

else:
    if source_dt == "" and target_dt == "" and target_di== "":
        #Start exchange
        for dt, dataset in dt_dict.items():
            cri = eci.change_id(dataset, dt, old_value, new_value)
            sql_dt_updates = sql_dt_updates + cri.sql_dt_updates
    else:
        #Start specific exchange
        cri = eci.change_id(target_dt + " " + target_di, source_dt, old_value, new_value)
        sql_dt_updates = cri.sql_dt_updates

    #Execute sql statements
    ac = ppms_cu.Action()
    dict_result = ac.exec_sql_modify_transaction(sql_statement_list=sql_dt_updates, force_rollback=True)

    #Preparing dict_result for report message
    message = """Table        affected rows
--------    ------------------"""

    for dt, result in dict_result.items():
        message = message + "\\n" + str(dt) + "      " + str(result)
        
    #Show report message    
    ppms.ui_message_box("Preview Ress.-ID switch: " + old_value[0] + " --> " + new_value[0], message)
''',
                     'new': '''from ppms import ppms
from ppms.exits import exit_change_id as eci
from ppms import ppms_cu
from ppms.constants import *

#Get macro module
macro_mod = ppms.get_macro_module()

#Get invoker module
inv_rec = macro_mod.get_invoker_rec()

#Get invoker datafield
inv_df = inv_rec.get_df('button_exec')

old_value = [inv_rec.res_id.get_text_value()]

new_value = [inv_rec.new_res_id.get_text_value()]

source_dt = inv_rec.source_dt.get_text_value()

target_dt = inv_rec.target_dt.get_text_value()

target_di = inv_rec.target_di.get_text_value()

df_config_value = inv_df.get_df_config()

DBMS = ppms.db_get_dbms_name()

exec(df_config_value.replace("\\r\\n","\\n"))

def preview_id_change():
    #Initialize list of update statements
    sql_dt_updates = []

    if old_value == [""] or new_value == [""]:
        #Show Message "Bitte zuerst die Ressourcen auswaehlen!" if no ressource has been set
        ppms.ui_message_id('0122')    

    else:
        if source_dt == "" and target_dt == "" and target_di== "":
            #Start exchange
            for dt, dataset in dt_dict.items():
                cri = eci.change_id(dataset, dt, old_value, new_value)
                sql_dt_updates = sql_dt_updates + cri.sql_dt_updates
        else:
            #Start specific exchange
            cri = eci.change_id(target_dt + " " + target_di, source_dt, old_value, new_value)
            sql_dt_updates = cri.sql_dt_updates

        #Set all constraints deferred, so foreign key updates won't fail
        #qry = ppms.get_query("000892")
        #ppms.ui_message_box('qry', qry)
        #ppms.db_modify(ppms.get_query("000892"))
        
        #Execute sql statements
        ac = ppms_cu.Action()
        dict_result = ac.exec_sql_modify_transaction(sql_statement_list=sql_dt_updates, force_rollback=True)

        #Set all constraints immediate again
        #ppms.db_modify(ppms.get_query("000893"))
        
        #Preparing dict_result for report message
        message = """Table        affected rows
    --------    ------------------"""

        for dt in sorted(dict_result.keys()):
            result = str(dict_result[dt])
            dt = str(dt)
            message = "{message}\\n{dt}      {result}".format(**locals())
        
        #Show report message    
        ppms.ui_message_box("Preview Ress.-ID switch: " + old_value[0] + " --> " + new_value[0], message)

if DBMS == DBMS_ORACLE:
    preview_id_change()
else:
    ppms.ui_message_id('1080')'''}},
           {'table': 411,
            'primary_key': ['000468', 101039],
            'dataitem': 'df_script_settings',
            'code': {'old': '''#conftype RESS_CHANGE

#Set dictionary for Ress.-ID change
#Key   --> target_dt (467, 511, 525, 504, 995, others)
#Value --> string dataset of datatables which relate to target_dt
dt_dict = {}

#Check all datatables which relate to datatable 'DT467:Ressource'
dt_dict['467'] = """
006 040088
007 025831
015 025213
017 025281
025 027275 027278
026 027309 027312
461 005448 005449 005450 005451 005452 001062
466
467 009753
468
469
472 001492 026859 026859
479 002680
483 026802 026858 002763
485
486 002816
488
526 026640 027849
528 026662
800 005280 008854 005298
801 028211 028215
802 028237
804
823 008604
826
827
833
840 023867 023868 023869 023870 023871 010083 023923
843
844 024060
845 009938
946 040091
997 026832 026849
998 027194 027197
999 027227 027230
"""

#Check all datatables which relate to datatable 'DT511:Benutzer'
dt_dict['511'] = """
419
443 001606
455
461 009311
492 028895
500 024149
518
524
818
835 029286
836 029394
840 023837
861 028557
868 028969
"""

#Check all datatables which relate to datatable 'DT525:Mitglied'
dt_dict['525'] = """
527
"""

#Check all datatables which relate to datatable 'DT504:Mitarbeiter'
dt_dict['504'] = """
006
007
024
499 041353
505
516 027020
946
989
993
"""

#Check all datatables which relate to datatable 'DT995:Verkaeufer'
dt_dict['995'] = """
969 027540
967
959
"""''',
                     'new': '''#conftype RESS_CHANGE

#Set dictionary for Ress.-ID change
#Key   --> target_dt (467, others(custom))
#Value --> string dataset of datatables which relate to target_dt
dt_dict = {}

#List of all standard datatables and dis which relate to datatable 'DT467:Ressource'
dt_dict['467'] = """
006 026265 040088
007 026279 025831
014 025191 
015 025213
016 026376
017 025281
401 002353
461 005448 005449 005450 005451 005452 001062 009311 041032 041686 057855
463 000293 001156
466 001392
467 009753 001675 001218 022077
468 001326 
469 001301 001300
470 001372 001371
472 001492 026859 001517
480 041702 029519 029521 029523
485 002787 
487 002753 002746
492 028895 
499 056944 056951 041353 056950
500 024149 
504 024930 
511 010633 010618 010323
516 027020 
518 010697 
525 026520 
526 026640 027849 
527 026560 
528 026662 
800 005280 008854 005298 
801 028211 028215 
802 028237 
804 006453 
805 040155
811 028265
813 029610
814 007881
818 007944 007945 007936
823 008604
824 059319
826 008499
827 008518
832 028289
833 008914
835 029286
836 029394
840 023867 023868 023869 023870 023871 010083 023923 023837
844 024060
845 009938
861 028557
870 028679
873 029675
877 059064 059062 059068
886 041347
887 056938
946 040091
992 021786
995 024569
"""'''}})

class FixResourceIdentChange(ChangeDTPPacket, SQLPacket):
    """Fixes the the resource ident change"""
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 4094
    
    FILENAME = 'WI_4094_fix_ressource_ident_change.sql'

    def get_dependencies(self):
        return (MinVersionDependency(version='39.4', version_type=VersionType.CUSTOMIZING),
                MaxVersionDependency(version='39.5.0', version_type=VersionType.CUSTOMIZING))
            
    def fix(self, runner):
        self.apply_dml_file(self.sql_planta_de_dbms, self.FILENAME)
        for mapping in CHANGES:
            table = mapping['table']
            primary_key = mapping['primary_key']
            dataitem = mapping['dataitem']
            code = mapping['code']
            old_code = code['old']
            new_code = code['new']

            rec = self.get_record(table, primary_key, [dataitem])

            if not self.change_value(rec, dataitem, old_code, new_code):
                return self.fail()

        return self.success()