from ppms.migration import *
from ppms import ppms


class FixLicenceLevel(ChangeDTPPacket, SQLPacket):
    """Fix the Licence Level Module"""
    run_on_startup = True
    done_after_success = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 13590

    def fix(self, packet_runner):

        if not self.update_task_query():
            return self.fail('Updating the task query failed')

        if not self.update_valuerange():
            return self.fail('Updating valuerange of DI 059024 failed')

        if not self.update_itext():
            return self.fail('Update I-Text of textconstant failed')

        filename = 'wi13590_fix_lizenzstaffel.sql'
        self.log_heading('Inserting new SQL user query')
        self.apply_dml_file(self.sql_planta_de_dbms, filename)

        return self.success()

    def update_itext(self):
        succeed=True
        self.log_heading('Update I-Text of textconstant')

        constant = {
            'DE': """PLANTA Project:\n   {tasks} Vorgänge\n\nPLANTA Portfolio:\n   {projects} Projekte\n\nPLANTA Request:\n   {srqs} SRQs\n   {rsos} RSOs\n\nPLANTA Sales:\n   {leads} Leads:\n\nPLANTA Link:\n   {planta_link} Konfigurationen\n\nMSP Link:\n   {msp_link} Vorgänge\n\nAnzahl Benutzer:\n   {users} Benutzer""",
            'EN': """PLANTA Project:\n   {tasks} tasks\n\nPLANTA Portfolio:\n   {projects} projects\n\nPLANTA Request:\n   {srqs} SRQs\n   {rsos} RSOs\n\nPLANTA Sales:\n   {leads} leads:\n\nPLANTA Link:\n   {planta_link} configurations\n\nMSP Link:\n   {msp_link} Tasks\n\nNumber of users:\n   {users} users""",
            'FR': """PLANTA Project:\n {tasks} Tâches\n\nPLANTA Portfolio:\n {projects} Projets\n\nPLANTA Request:\n {srqs} SRQs\n {rsos} RSOs\n\nPLANTA Sales:\n {leads} Prospects\n\nPLANTA Link:\n {planta_link} Configurations\n\nMSP Link:\n {msp_link} Tâches\n\nNombre Utilisateurs:\n {users} Utilisateurs""",
            'PT': """PLANTA Project:\n   {tasks} tarefas\n\nPLANTA Portfolio:\n   {projects} projetos\n\nPLANTA Request:\n   {srqs} SRQs\n   {rsos} RSOs\n\nPLANTA Sales:\n   {leads} Leads:\n\nPLANTA Link:\n   {planta_link} configurações\n\nMSP Link:\n   {msp_link} tarefas\n\nNúmero de usuários:\n   {users} usuários"""}

        for language, itext in constant.items():
            rec435 = self.get_record(435, [754581, language], ['i_text'])
            if not self.change_value(rec435, 'i_text', None, itext):
                succeed = False
        return succeed


    def update_valuerange(self):
        self.log_heading('Update valuerange of DI059024')
        rec412 = self.get_record(412, ['059024'], ['valuerange'])
        return self.change_value(rec412, 'valuerange', None, """from ppms import ppms_cu
from ppms import text_constant

def get_amount(query,lic=None):
    query = ppms.get_query(query).format(**locals())
    results = ppms.db_select(query)

    try:
        return str(int(results[0][0]))
    except IndexError:
        return '0'

def computeOutput(di):
    lic = ppms.uvar_get('@3')
    projects = get_amount('000226')
    tasks = get_amount('000225')

    srqs = get_amount('000227')
    rsos = get_amount('000228')

    leads = get_amount('000229')
    planta_link = get_amount('000700')

    msp_link = get_amount('000701')
    users = get_amount('001000',lic)

    text = text_constant.get_formatted_text_constant('001562', **locals())

    return text.replace(r'\\n', '\\n')

computeOutput.deps = ('@3',)""")

    def update_task_query(self):
        self.log_heading('Update Task SQL')
        rec343 = self.get_record(343, ['-', '000225'], ['source_text_clob'])
        return self.change_value(rec343, 'source_text_clob', None,
                          """SELECT COUNT(*) FROM dt463, dt461 WHERE di001001 = di001097 AND NOT di001042 = 9""")