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

SQL_STMTS = {'000312': dict(old_value = """SELECT DISTINCT DI000215, (SELECT DI000215 FROM DT412 source_name WHERE 
                       DI000238=DT412.DI040837), 
                      (SELECT DI000239 FROM DT412 source_table WHERE DI000238=DT412.DI040837)
                      FROM DT412 
                      WHERE DT412.DI000239='{0}' AND DT412.DI040837 IS NOT NULL
                      AND DI040837!='AUTO' AND DI040837!='DATE' AND DI040837!='USER'
                      ORDER BY DT412.DI000215 asc""",
                new_value = """SELECT DISTINCT DI000215, 
(
    SELECT DI000215 
    FROM DT412 source_name 
    WHERE DI000238=DT412.DI040837
), (
    SELECT DI000239 
    FROM DT412 source_table 
    WHERE DI000238=DT412.DI040837
)
FROM DT412 
WHERE DT412.DI000239='{0}' 
AND DT412.DI040837 IS NOT NULL
AND DT412.DI040837 <> ''
AND DI040837!='AUTO' 
AND DI040837!='DATE' 
AND DI040837!='USER'
ORDER BY DT412.DI000215 asc"""),
'000336': dict(old_value = """SELECT DI000215,DI040837 FROM DT412
WHERE di000275=0 and di001700 <> 20
AND di000239='{0}' AND DI040837 is not NULL
""",
                new_value = """SELECT DI000215, DI040837 
FROM DT412
WHERE di000275=0 
AND di001700 <> 20
AND di000239='{0}' 
AND DI040837 is not NULL 
AND DI040837 <> ''""")
}

class FixCreationOfStatusReportInVoEMSSQL(ChangeDTPPacket):
    """Fixes a SQL-Exception when creating statusreports in VenusOnEarth under MSSQL"""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 14214
    
    def get_dependencies(self):
        return (MinVersionDependency(version='39.4', version_type=VersionType.CUSTOMIZING),
                MaxVersionDependency(version='39.4.4', version_type=VersionType.CUSTOMIZING))
    
    def fix(self, runner):
        for statement, values in SQL_STMTS.items():
            record = self.get_record(343, ['-', statement], ['source_text_clob'])
            if not self.change_value(record, 'source_text_clob', values.get("old_value"), values.get("new_value")):
                return self.fail('Could not change sql-statement - was it individualized?')
        return self.success()