C11/Tasks/scripts/iqd_convert.py

from pathlib import Path
import re
import plac
from collections import defaultdict


class IqdConverter:
    commands = ['convert']
    target_schema_by_type = {
        'csv': 'staging',
        'ims': 'ims'
    }
    output_dir = 'C:\\GlobalCube\\System\\LOCOSOFT\\SQL\\schema\\LOCOSOFT\\views_imr'

    def convert(self, iqd_file, target_type='csv'):
        if not Path(iqd_file).exists():
            print(f"File {iqd_file} does not exist!")
            return
        query = self.get_query_from_iqdfile(iqd_file)
        query = self.cleanup_query(query)

        output_file = iqd_file[:-4] + '.sql'
        with open(output_file, 'w', encoding='latin-1') as fwh:
            fwh.write(query)

        table = Path(iqd_file).stem.lower()
        if target_type == 'csv':
            table += '_imr'
        schema = self.target_schema_by_type[target_type]

        create_view = "SET QUOTED_IDENTIFIER ON \nGO\nSET ANSI_NULLS ON \nGO\n" + \
            f"CREATE VIEW {schema}.{table} AS\n\n{query}\n" + \
            "GO\nSET QUOTED_IDENTIFIER OFF \nGO\nSET ANSI_NULLS OFF \nGO\n\nGO"

        view_file = f"{self.output_dir}\\{schema}.{table}.sql"
        with open(view_file, 'w', encoding='latin-1') as fwh:
            fwh.write(create_view)

    def get_query_from_iqdfile(self, iqd_file):
        if iqd_file[-4:].lower() == '.imr':
            iqd_file = iqd_file[:-4] + '.iqd'
        iqd = open(iqd_file, 'r', encoding='latin-1').read()
        res = re.findall(r'BEGIN SQL\n(.*)\n\nEND SQL', iqd, re.MULTILINE | re.DOTALL)
        query = ''
        if len(res) > 0:
            query = res[0]

        columns = re.findall(r'COLUMN,\d+,(.*)', iqd)
        col_keys = [f'c{i}' for i in range(len(columns), 0, -1)]
        col_names = [f'"{c}"' for c in reversed(columns)]

        used_cols = defaultdict(int)
        for col_key, col_name in zip(col_keys, col_names):
            used_cols[col_name] += 1
            if used_cols[col_name] > 1:
                col_name = col_name[:-1] + '_' + str(used_cols[col_name]) + '"'
            query = re.sub(col_key + r'([^\d])', col_name + r'\1', query)

        columns2 = re.findall(r'\s+(c\d+) as (".*")', query)
        used_cols = defaultdict(int)
        for col in columns2:
            used_cols[col[1]] += 1
            col_name = col[1]
            if used_cols[col[1]] > 1:
                col_name = col[1][:-1] + '_' + str(used_cols[col[1]]) + '"'
            query = re.sub(col[0] + r' as ' + col[1], col_name, query)
            query = re.sub(col[0] + r'([^\d])', col_name + r'\1', query)
        return query

    def cleanup_query(self, query):
        query = re.sub(r' from (\d+) for (\d+)\)', r', \1, \2)', query)
        query = query.replace(' || ', ' + ')
        query = query.replace('truncate(', 'rtrim(')
        query = query.replace('char_length(', 'len(')
        query = query.replace('database(', 'db_name(')
        query = query.replace('ascii(', 'convert(varchar(50), ')
        query = query.replace('extract(DAY FROM ', 'day(')
        query = query.replace('extract(MONTH FROM ', 'month(')
        query = query.replace('extract(YEAR FROM ', 'year(')
        query = query.replace('od_year(', 'year(')
        query = query.replace('od_month(', 'month(')
        query = query.replace('lastday(', 'eomonth(')
        query = query.replace('cdatetime(', 'convert(datetime, ')
        query = query.replace('cast_float(', 'convert(float, ')
        query = query.replace('sy_right(', 'right(')
        query = query.replace('od_left(', 'left(')
        query = query.replace('od_right(', 'right(')
        query = query.replace('length(', 'len(')
        query = query.replace('{hour}', 'hour')
        query = query.replace('{minute}', 'minute')
        query = query.replace('{weekday}', 'weekday')
        query = query.replace('dayofweek(', 'datepart(weekday, ')
        query = query.replace('cast_numberToString(cast_integer(', '((')
        query = query.replace('@CURRENT_DATE', 'getdate()')
        query = query.replace('now()', 'getdate()')
        query = query.replace("INTERVAL '001 10:00:00.000'", '1')
        query = query.replace("INTERVAL '001 00:00:00.000'", '1')
        
        query = query.replace('cdate(', '(')

        query = re.sub(r'intdiv\(([^\)]+)\,1\)', r'\1', query)
        query = re.sub(r'XCOUNT\(([^\)]+) for ', r'COUNT(\1) OVER (partition by ', query)
        query = re.sub(r'XSUM\(([^\)]+) for ', r'SUM(\1) OVER (partition by ', query)
        query = re.sub(r'RSUM\(([^\)]+) for ', r'SUM(\1) OVER (partition by ', query)
        query = re.sub(r'XMIN\(([^\)]+) for ', r'MIN(\1) OVER (partition by ', query)
        query = re.sub(r'XMAX\(([^\)]+) for ', r'MAX(\1) OVER (partition by ', query)
        query = re.sub(r'XRANK\(([^\)]+) for ([^\)]+)', r'RANK() OVER (partition by \2 order by \1', query)

        query = re.sub(r'QSS\.\"[^\"]+\\([^\\]+)\.ims\"', r'"ims"."\1"', query)
        query = re.sub(r"DATE '([\d-]+)'", r"convert(date, '\1')", query)
        query = re.sub(r"TIMESTAMP '([\d\s\-\:\.]+)'", r"convert(datetime, '\1')", query)

        query = re.sub(r'asciiz\(([^\,]*)\,\d+\)', r'convert(varchar(50), \1)', query)
        query = re.sub(r'asciiz\(([^\+]*)\)', r'convert(varchar(50), \1)', query)
        # query = re.sub(r'day\(([^\-\<\>\=]*) \- ([^\-\<\>\=]*)\)\) as', r'datediff(day, \2, \1)) as', query)
        # query = re.sub(r'day\(([^\-\<\>\=]*) \- ([^\-\<\>\=]*)\)\)\)', r'datediff(day, \2, \1)))', query)
        query = re.sub(r'day\(([^\-\,\']*) \- ', r'-1 * datediff(day, \1, ', query)
        query = re.sub(r'convert\(varchar\(50\)\, ([^,]+)\,\d+\)', r'convert(varchar(50), \1)', query)
        query = query.replace('cdate((convert(float, ', 'convert(datetime, ((')

        query = re.sub(r'[^ ](order by .*)', r'\n-- \1', query)
        return query

    def run_folder(self, base_dir: str):
        files = sorted([(f.stat().st_mtime, f) for f in Path(base_dir).rglob('*.iqd')])
        for timestamp, iqd in files:
            self.convert(str(iqd))


if __name__ == '__main__':
    # IqdConverter().convert('C:\\GlobalCube_LOCOSOFT\\System\\LOCOSOFT\\IQD\\Serv_Teile\\offene_Auftraege_Ums_ben_AW.iqd')
    # IqdConverter().run_folder('C:\\Projekte\\DWH\\CARLO\\IQD')
    plac.Interpreter.call(IqdConverter)