def __init__(self):
from classes.mascara_data import MascaraData
import os
self.os = os
self.d = MascaraData()
self.today = self.d.get_today()
def __init__(self):
from pyxlsb import open_workbook as open_xlsb
from classes.mascara_data import MascaraData
from unicodedata import normalize
import pandas as pd
import os, re, traceback, csv
self.pc = ConfParameter()
self.path_conf = self.pc.get_py_configger('Path')
self.os = os
self.csv = csv
self.pd = pd
self.open_xlsb = open_xlsb
self.re = re
self.d = MascaraData()
self.today = self.d.get_today()
self.normalize = normalize
self.traceback = traceback
def __init__(self, file):
from classes.mascara_data import MascaraData
from classes.init_logger import InitLogger
from unicodedata import normalize
import os, csv, re
self.os = os
self.csv = csv
self.re = re
self.normalize = normalize
self.l = InitLogger(__name__)
self.logger = self.l.build_logger()
self.d = MascaraData()
self.today = self.d.get_today()
self.logger.info("Inicializando Conversão | Arquivo: " + file)
class LogControler():
def __init__(self):
from classes.mascara_data import MascaraData
self.d = MascaraData()
self.today = self.d.get_today()
def get_stage_load(self, stg_name, csv_file):
lines_stage_load = []
lines_stage_load.append('')
lines_stage_load.append('INSERT INTO tb_load_stage')
lines_stage_load.append('(')
lines_stage_load.append('anomesdia, stg_name, dt_load, nm_file')
lines_stage_load.append(')')
lines_stage_load.append('VALUES')
lines_stage_load.append('(')
lines_stage_load.append("'{}',".format(self.today))
lines_stage_load.append("'{}',".format(stg_name))
lines_stage_load.append("current_timestamp(),")
lines_stage_load.append("'{}'".format(csv_file))
lines_stage_load.append(");")
return lines_stage_load
def get_data_prep_load(self, table_insert_name, dp_table_name,
process_name):
lines_stage_load = []
lines_stage_load.append('')
lines_stage_load.append('INSERT INTO {}'.format(table_insert_name))
lines_stage_load.append('(')
lines_stage_load.append(
'anomesdia, dp_table_name, dt_load, process_name')
lines_stage_load.append(')')
lines_stage_load.append('VALUES')
lines_stage_load.append('(')
lines_stage_load.append("'{}',".format(self.today))
lines_stage_load.append("'{}',".format(dp_table_name))
lines_stage_load.append("current_timestamp(),")
lines_stage_load.append("'{}'".format(process_name))
lines_stage_load.append(");")
return lines_stage_load
class CsvToSQL():
def __init__(self, file):
from classes.mascara_data import MascaraData
from classes.init_logger import InitLogger
from unicodedata import normalize
import os, csv, re
self.os = os
self.csv = csv
self.re = re
self.normalize = normalize
self.l = InitLogger(__name__)
self.logger = self.l.build_logger()
self.d = MascaraData()
self.today = self.d.get_today()
self.logger.info("Inicializando Conversão | Arquivo: " + file)
def get_headers(self, csv_file):
with open(csv_file, newline='', encoding="utf8") as f:
reader = self.csv.reader(f)
header_row = next(reader)
headers = []
for i in header_row:
i = self.normalize('NFKD', i).encode('ASCII',
'ignore').decode('ASCII')
i = self.re.sub("\s+", "_", i)
i = self.re.sub('[^A-Za-z0-9_]+', '', i).lower()
headers.append(i)
headers_minus_last = headers[:-1]
last_header = headers[-1]
return headers, headers_minus_last, last_header
def create_table_lines(self, tbl_nm, headers_minus_last, last_header):
column_type = 'TEXT'
lines_to_write_table = []
lines_to_write_table.append(
'DROP TABLE IF EXISTS {};\n'.format(tbl_nm))
lines_to_write_table.append('CREATE TABLE {}\n('.format(tbl_nm))
for i in headers_minus_last:
lines_to_write_table.append(i + ' {},'.format(column_type))
lines_to_write_table.append(last_header + ' {}'.format(column_type) +
'\n) DEFAULT CHARSET=utf8; \n')
return lines_to_write_table
def create_data_lines(self, csv_file, tbl_nm, hdrs):
field_terminator = "','"
line_terminator = "'\\r\\n'"
enclosed_by = "'\"'"
ignore_lines = '1'
lines_to_write_data_load = []
lines_to_write_data_load.append(
'LOAD DATA INFILE "C:\\\\PROD\\\\tmp\\\\' + self.today +
'\\\\csv\\\\{}"'.format(csv_file))
lines_to_write_data_load.append('INTO TABLE {}'.format(tbl_nm))
lines_to_write_data_load.append('CHARACTER SET UTF8')
lines_to_write_data_load.append(
'FIELDS TERMINATED BY {}'.format(field_terminator))
lines_to_write_data_load.append('ENCLOSED BY {}'.format(enclosed_by))
lines_to_write_data_load.append(
'LINES TERMINATED BY {}'.format(line_terminator))
lines_to_write_data_load.append('IGNORE {} LINES'.format(ignore_lines))
lines_to_write_data_load.append('(')
for i in hdrs[:-1]:
lines_to_write_data_load.append(i + ',')
lines_to_write_data_load.append(hdrs[-1] + '\n);')
return lines_to_write_data_load
def write_lines_to_file(self, csv_file, tbl_nm, tbl_lines, data_lines,
log_lines):
source_sql = 'C:\\PROD\\tmp\\' + self.today + '\\sql'
source_stage = 'C:\\PROD\\tmp\\' + self.today + '\\sql\\stage'
if not self.os.path.exists(source_sql):
self.os.mkdir(source_sql)
if not self.os.path.exists(source_stage):
self.os.mkdir(source_stage)
self.os.chdir(source_stage)
with open(tbl_nm + '.sql', 'w') as f:
for i in tbl_lines:
f.write(i + '\n')
for i in data_lines:
f.write(i + '\n')
f.write('\n')
for i in log_lines:
f.write(i + '\n')
self.logger.info(
'Conversão Concluída! Arquivo SQL gerado: {}.sql'.format(tbl_nm))
class ExcelToCsv():
def __init__(self):
from pyxlsb import open_workbook as open_xlsb
from classes.mascara_data import MascaraData
from unicodedata import normalize
import pandas as pd
import os, re, traceback, csv
self.pc = ConfParameter()
self.path_conf = self.pc.get_py_configger('Path')
self.os = os
self.csv = csv
self.pd = pd
self.open_xlsb = open_xlsb
self.re = re
self.d = MascaraData()
self.today = self.d.get_today()
self.normalize = normalize
self.traceback = traceback
def m_remove(self, txt, month):
for m in month:
txt = txt.replace(m, "")
return txt
#incluir parametro para receber o nome da guia e gravar o nome da guia no csvfile
def excel_to_csv(self, excel_file, dest_csv, sheet_name):
try:
ext = self.os.path.splitext(excel_file)[1]
if ext in (".xls", ".xlsx"):
df = self.pd.DataFrame()
xl = self.pd.ExcelFile(excel_file)
for sheet in xl.sheet_names:
if sheet == sheet_name:
df_tmp = xl.parse(sheet)
df = df.append(df_tmp, ignore_index=True)
elif ext == ".xlsb":
df = []
with self.open_xlsb(excel_file) as wb:
with wb.get_sheet(sheet_name) as sheet:
for row in sheet.rows():
df.append([item.v for item in row])
df = self.pd.DataFrame(df[1:], columns=df[0])
else:
print("Formato não suportado")
rm_month_abv = [
"jan", "fev", "mar", "abr", "mai", "jun", "jul", "ago", "set",
"out", "nov", "dez"
]
rm_month = [
"janeiro", "fevereiro", "março", "marco", "abril", "maio",
"junho", "julho", "agosto", "setembro", "outubro", "novembro",
"dezembro"
]
csvfile = self.os.path.splitext(excel_file)[0]
csvfile = self.re.sub("\s+", "_", csvfile).lower()
csvfile = self.normalize('NFKD',
csvfile).encode('ASCII',
'ignore').decode('ASCII')
csvfile = self.m_remove(csvfile, rm_month)
csvfile = self.m_remove(csvfile, rm_month_abv)
csvfile = csvfile.replace("_v", "")
csvfile = csvfile.replace("sap", "")
csvfile = csvfile.replace("scf", "")
csvfile = csvfile.replace("consolidado", "")
csvfile = csvfile.replace("final", "")
csvfile = self.re.sub('[^A-Za-z]+', '', csvfile).lower()
csvfile = "stg_" + sheet_name.lower() + "_" + csvfile
arq_csv = dest_csv + csvfile + ".csv"
df.to_csv(arq_csv, index=False, encoding='utf-8')
file = open(arq_csv, newline='', encoding="utf8")
numline = len(file.readlines())
file.close()
if (numline == 1):
self.os.remove(arq_csv)
except Exception:
pass
log_ex = lc.get_data_prep_load("tb_load_dim", "DIM_EXECUTIVE", sys.argv[0])
log_ext = lc.get_data_prep_load("tb_load_dim", "DIM_EXT", sys.argv[0])
log_sq = lc.get_data_prep_load("tb_load_dim", "DIM_SQUAD", sys.argv[0])
log_un = lc.get_data_prep_load("tb_load_dim", "DIM_UN", sys.argv[0])
log_user = lc.get_data_prep_load("tb_load_dim", "DIM_USER", sys.argv[0])
dt.transform_prep_to_file(dp_dim, "DIM_EXECUTIVE", dim_ex, log_ex)
dt.transform_prep_to_file(dp_dim, "DIM_EXT", dim_ext, log_ext)
dt.transform_prep_to_file(dp_dim, "DIM_SQUAD", dim_sq, log_sq)
dt.transform_prep_to_file(dp_dim, "DIM_UN", dim_un, log_un)
dt.transform_prep_to_file(dp_dim, "DIM_USER", dim_user, log_user)
if __name__ == '__main__':
t = MascaraData()
today = t.get_today()
lc = LogControler()
dt = DataTransform()
ps = ExecSQL()
pc = ConfParameter()
path_conf = pc.get_py_configger('Path')
path_mysql_conn = pc.get_py_configger('MySql Connector')
data_prep_dir = path_conf['temp'] + today + "\\sql"
dp_prep = data_prep_dir + "\\prep"
dp_alt = data_prep_dir + "\\alt"
dp_dim = data_prep_dir + "\\dim"
table_name = file.split('.')[0]
table_name = re.sub('[^A-Za-z0-9_]+', '', table_name).lower() + '_tmp'
table_name = table_name.replace("__", "_")
c = CsvToSQL(file)
headers, headers_minus_last, last_header = c.get_headers(file)
lines_to_write_table = c.create_table_lines(table_name, headers_minus_last,
last_header)
lines_to_write_data_load = c.create_data_lines(file, table_name, headers)
lines_to_write_log = lc.get_stage_load(table_name, file)
c.write_lines_to_file(file, table_name, lines_to_write_table,
lines_to_write_data_load, lines_to_write_log)
if __name__ == '__main__':
t = MascaraData()
lc = LogControler()
pc = ConfParameter()
path_conf = pc.get_py_configger('Path')
dest = path_conf['temp'] + t.get_today() + '\\csv'
os.chdir(dest)
for g in glob.glob("*.csv"):
os.chdir(dest)
main(g)
class DataTransform():
def __init__(self):
from classes.mascara_data import MascaraData
import os
self.os = os
self.d = MascaraData()
self.today = self.d.get_today()
def set_wrk_prep_executive(self, tb_wrk_prep_name, stg_name):
wrk_prep_executive = []
wrk_prep_executive.append(
"DROP TABLE IF EXISTS {};".format(tb_wrk_prep_name))
wrk_prep_executive.append(
"CREATE TABLE {} AS".format(tb_wrk_prep_name))
wrk_prep_executive.append(" SELECT")
wrk_prep_executive.append(" {} AS ANOMESDIA,".format(self.today))
wrk_prep_executive.append(
" (@ROW_NUMBER:=@ROW_NUMBER + 1) AS ID_EXECUTIVE,")
wrk_prep_executive.append(" E.PROJECT_MANAGER_NAME AS NM_EXECUTIVE,")
wrk_prep_executive.append(" NULL AS ID_SAP,")
wrk_prep_executive.append(" NULL AS DT_INIT,")
wrk_prep_executive.append(
"STR_TO_DATE('31/12/2100', '%d/%m/%Y') AS DT_END")
wrk_prep_executive.append(" FROM")
wrk_prep_executive.append(
" (SELECT DISTINCT PROJECT_MANAGER_NAME FROM {}) AS E,".format(
stg_name))
wrk_prep_executive.append(
" (SELECT @ROW_NUMBER:=(SELECT IFNULL(MAX(ID_EXECUTIVE), 0) FROM DIM_EXECUTIVE)) AS RN;"
)
return wrk_prep_executive
def set_wrk_prep_un(self, tb_wrk_prep_name, stg_name):
wrk_prep_un = []
wrk_prep_un.append("DROP TABLE IF EXISTS {};".format(tb_wrk_prep_name))
wrk_prep_un.append("CREATE TABLE {} AS".format(tb_wrk_prep_name))
wrk_prep_un.append("SELECT")
wrk_prep_un.append(" {} AS ANOMESDIA,".format(self.today))
wrk_prep_un.append(" (@ROW_NUMBER:=@ROW_NUMBER + 1) AS ID_UN,")
wrk_prep_un.append(" U.UN AS NM_UN,")
wrk_prep_un.append(" NULL AS DT_INIT,")
wrk_prep_un.append(" STR_TO_DATE('31/12/2100', '%d/%m/%Y') AS DT_END")
wrk_prep_un.append("FROM")
wrk_prep_un.append(
"(SELECT DISTINCT UN FROM {}) AS U,".format(stg_name))
wrk_prep_un.append(
"(SELECT @ROW_NUMBER:=(SELECT IFNULL(MAX(ID_UN), 0) FROM DIM_UN)) AS RN;"
)
return wrk_prep_un
def set_wrk_prep_ext(self, tb_wrk_prep_name, stg_name):
wrk_prep_ext = []
wrk_prep_ext.append(
"DROP TABLE IF EXISTS {};".format(tb_wrk_prep_name))
wrk_prep_ext.append("CREATE TABLE {} AS".format(tb_wrk_prep_name))
wrk_prep_ext.append("SELECT")
wrk_prep_ext.append(" {} AS ANOMESDIA,".format(self.today))
wrk_prep_ext.append(" E.project AS ID_EXT,")
wrk_prep_ext.append(" NULL AS NM_EXT,")
wrk_prep_ext.append(" NULL AS DT_INIT,")
wrk_prep_ext.append(
" STR_TO_DATE('31/12/2100', '%d/%m/%Y') AS DT_END")
wrk_prep_ext.append(" FROM")
wrk_prep_ext.append(
" (SELECT DISTINCT project FROM {}) AS E;".format(stg_name))
return wrk_prep_ext
def set_wrk_prep_squad(self, tb_wrk_prep_name, stg_name):
wrk_prep_squad = []
wrk_prep_squad.append(
"DROP TABLE IF EXISTS {};".format(tb_wrk_prep_name))
wrk_prep_squad.append("CREATE TABLE {} AS".format(tb_wrk_prep_name))
wrk_prep_squad.append("SELECT")
wrk_prep_squad.append(" {} AS ANOMESDIA,".format(self.today))
wrk_prep_squad.append(" S.ID_SQUAD,")
wrk_prep_squad.append(" NULL AS NM_SQUAD,")
wrk_prep_squad.append(" NULL AS DT_INIT,")
wrk_prep_squad.append(" STR_TO_DATE('31/12/2100', '%d/%m/%Y') DT_END")
wrk_prep_squad.append("FROM")
wrk_prep_squad.append(
"(SELECT DISTINCT SUBSTR(agrupamento, 1, 5) AS ID_SQUAD")
wrk_prep_squad.append("FROM {}) AS S;".format(stg_name))
return wrk_prep_squad
def set_wrk_prep_user(self, tb_wrk_prep_name, stg_name):
wrk_prep_user = []
wrk_prep_user.append(
"DROP TABLE IF EXISTS {};".format(tb_wrk_prep_name))
wrk_prep_user.append("CREATE TABLE {} AS".format(tb_wrk_prep_name))
wrk_prep_user.append("SELECT")
wrk_prep_user.append(" {} AS ANOMESDIA,".format(self.today))
wrk_prep_user.append(" itau_employee_name, ")
wrk_prep_user.append(" SUBSTRING_INDEX(id_sap, '.', '1') as id_sap,")
wrk_prep_user.append(" SUBSTR(id_func, 1,8) as id_func,")
wrk_prep_user.append(" everis_employee_name,")
wrk_prep_user.append(" dt_init,")
wrk_prep_user.append(
" IF(ISNULL(dt_end),STR_TO_DATE('31/12/2100', '%d/%m/%Y'), dt_end) AS DT_END"
)
wrk_prep_user.append("FROM {};".format(stg_name))
return wrk_prep_user
def set_wrk_alt_executive(self, tb_wrk_alt_name, prep_name):
wrk_alt_executive = []
wrk_alt_executive.append("TRUNCATE TABLE {};".format(tb_wrk_alt_name))
wrk_alt_executive.append("INSERT INTO {}".format(tb_wrk_alt_name))
wrk_alt_executive.append("SELECT ")
wrk_alt_executive.append(" prp.ANOMESDIA,")
wrk_alt_executive.append(" prp.ID_EXECUTIVE,")
wrk_alt_executive.append(" prp.NM_EXECUTIVE,")
wrk_alt_executive.append(" prp.ID_SAP,")
wrk_alt_executive.append(" prp.DT_INIT,")
wrk_alt_executive.append(" prp.DT_END,")
wrk_alt_executive.append(" IF(ISNULL(dim.NM_EXECUTIVE),'I', ")
wrk_alt_executive.append(
" IF((prp.DT_END <> dim.DT_END),'E', 'N')) AS COD_INDCD_ALT")
wrk_alt_executive.append("FROM {} prp".format(prep_name))
wrk_alt_executive.append("LEFT JOIN DIM_EXECUTIVE dim")
wrk_alt_executive.append("ON prp.NM_EXECUTIVE = dim.NM_EXECUTIVE;")
return wrk_alt_executive
def set_wrk_alt_un(self, tb_wrk_alt_name, prep_name):
wrk_alt_un = []
wrk_alt_un.append("TRUNCATE TABLE {};".format(tb_wrk_alt_name))
wrk_alt_un.append("INSERT INTO {}".format(tb_wrk_alt_name))
wrk_alt_un.append("SELECT")
wrk_alt_un.append(" prp.ANOMESDIA,")
wrk_alt_un.append(" prp.ID_UN,")
wrk_alt_un.append(" prp.NM_UN,")
wrk_alt_un.append(" prp.DT_INIT,")
wrk_alt_un.append(" prp.DT_END,")
wrk_alt_un.append("IF(ISNULL(dim.NM_UN),'I', ")
wrk_alt_un.append(
"IF((prp.DT_END <> dim.DT_END),'E', 'N')) AS COD_INDCD_ALT")
wrk_alt_un.append("FROM {} prp".format(prep_name))
wrk_alt_un.append("LEFT JOIN DIM_UN dim")
wrk_alt_un.append("ON prp.NM_UN = dim.NM_UN;")
return wrk_alt_un
def set_wrk_alt_ext(self, tb_wrk_alt_name, prep_name):
wrk_alt_ext = []
wrk_alt_ext.append("TRUNCATE TABLE {};".format(tb_wrk_alt_name))
wrk_alt_ext.append("INSERT INTO {}".format(tb_wrk_alt_name))
wrk_alt_ext.append("SELECT ")
wrk_alt_ext.append(" prp.ANOMESDIA,")
wrk_alt_ext.append(" prp.ID_EXT,")
wrk_alt_ext.append(" prp.NM_EXT,")
wrk_alt_ext.append(" prp.DT_INIT,")
wrk_alt_ext.append(" prp.DT_END,")
wrk_alt_ext.append("IF(ISNULL(dim.ID_EXT),'I', ")
wrk_alt_ext.append("IF((prp.NM_EXT <> dim.NM_EXT),'A', ")
wrk_alt_ext.append(
"IF((prp.DT_END <> dim.DT_END),'E','N'))) AS COD_INDCD_ALT")
wrk_alt_ext.append("FROM {} prp".format(prep_name))
wrk_alt_ext.append("LEFT JOIN DIM_EXT dim")
wrk_alt_ext.append("ON prp.ID_EXT = dim.ID_EXT;")
return wrk_alt_ext
def set_wrk_alt_squad(self, tb_wrk_alt_name, prep_name):
wrk_alt_squad = []
wrk_alt_squad.append("TRUNCATE TABLE {};".format(tb_wrk_alt_name))
wrk_alt_squad.append("INSERT INTO {}".format(tb_wrk_alt_name))
wrk_alt_squad.append("SELECT ")
wrk_alt_squad.append(" prp.ANOMESDIA,")
wrk_alt_squad.append(" prp.ID_SQUAD,")
wrk_alt_squad.append(" prp.NM_SQUAD,")
wrk_alt_squad.append(" prp.DT_INIT,")
wrk_alt_squad.append(" prp.DT_END,")
wrk_alt_squad.append("IF(ISNULL(dim.ID_SQUAD),'I', ")
wrk_alt_squad.append("IF((prp.NM_SQUAD <> dim.NM_SQUAD),'A', ")
wrk_alt_squad.append(
"IF((prp.DT_END <> dim.DT_END),'E','N'))) AS COD_INDCD_ALT")
wrk_alt_squad.append("FROM {} prp".format(prep_name))
wrk_alt_squad.append("LEFT JOIN DIM_SQUAD dim")
wrk_alt_squad.append("ON prp.ID_SQUAD = dim.ID_SQUAD;")
return wrk_alt_squad
def set_wrk_alt_user(self, tb_wrk_alt_name, prep_name):
wrk_alt_user = []
wrk_alt_user.append("TRUNCATE TABLE {};".format(tb_wrk_alt_name))
wrk_alt_user.append("INSERT INTO {}".format(tb_wrk_alt_name))
wrk_alt_user.append("SELECT ")
wrk_alt_user.append(" prp.ANOMESDIA,")
wrk_alt_user.append(" prp.ITAU_EMPLOYEE_NAME,")
wrk_alt_user.append(" prp.ID_SAP,")
wrk_alt_user.append(" prp.ID_FUNC,")
wrk_alt_user.append(" prp.EVERIS_EMPLOYEE_NAME,")
wrk_alt_user.append(
" STR_TO_DATE(prp.DT_INIT, '%Y-%m-%d') as DT_INIT,")
wrk_alt_user.append(" STR_TO_DATE('2100-12-31', '%Y-%m-%d') DT_END,")
wrk_alt_user.append(" IF(ISNULL(dim.ID_SAP),'I',")
wrk_alt_user.append(
" IF((prp.ITAU_EMPLOYEE_NAME <> dim.ITAU_EMPLOYEE_NAME OR prp.EVERIS_EMPLOYEE_NAME <> dim.EVERIS_EMPLOYEE_NAME),'A', "
)
wrk_alt_user.append(
" IF((prp.DT_END <> dim.DT_END),'E','N'))) AS COD_INDCD_ALT")
wrk_alt_user.append("FROM {} AS prp".format(prep_name))
wrk_alt_user.append("LEFT JOIN DIM_USER dim")
wrk_alt_user.append("ON prp.ID_SAP = dim.ID_SAP;")
return wrk_alt_user
def set_dim_executive(self, tb_dim_name, alt_name):
prep_dim_executive = []
prep_dim_executive.append("INSERT INTO {}".format(tb_dim_name))
prep_dim_executive.append("SELECT DISTINCT ANOMESDIA,")
prep_dim_executive.append("ID_EXECUTIVE,")
prep_dim_executive.append("NM_EXECUTIVE,")
prep_dim_executive.append("ID_SAP,")
prep_dim_executive.append("DT_INIT,")
prep_dim_executive.append("DT_END")
prep_dim_executive.append("FROM {}".format(alt_name))
prep_dim_executive.append("WHERE COD_INDCD_ALT IN('I','A');")
return prep_dim_executive
def set_dim_squad(self, tb_dim_name, alt_name):
prep_dim_squad = []
prep_dim_squad.append("INSERT INTO {}".format(tb_dim_name))
prep_dim_squad.append("SELECT DISTINCT ANOMESDIA,")
prep_dim_squad.append("ID_SQUAD,")
prep_dim_squad.append("NM_SQUAD,")
prep_dim_squad.append("DT_INIT,")
prep_dim_squad.append("DT_END")
prep_dim_squad.append("FROM {}".format(alt_name))
prep_dim_squad.append("WHERE COD_INDCD_ALT IN('I','A');")
return prep_dim_squad
def set_dim_ext(self, tb_dim_name, alt_name):
prep_dim_ext = []
prep_dim_ext.append("INSERT INTO {}".format(tb_dim_name))
prep_dim_ext.append("SELECT DISTINCT ANOMESDIA,")
prep_dim_ext.append("ID_EXT,")
prep_dim_ext.append("NM_EXT,")
prep_dim_ext.append("DT_INIT,")
prep_dim_ext.append("DT_END")
prep_dim_ext.append("FROM {}".format(alt_name))
prep_dim_ext.append("WHERE COD_INDCD_ALT IN('I','A');")
return prep_dim_ext
def set_dim_un(self, tb_dim_name, alt_name):
prep_dim_un = []
prep_dim_un.append("INSERT INTO {}".format(tb_dim_name))
prep_dim_un.append("SELECT DISTINCT ANOMESDIA,")
prep_dim_un.append("ID_UN,")
prep_dim_un.append("NM_UN,")
prep_dim_un.append("DT_INIT,")
prep_dim_un.append("DT_END")
prep_dim_un.append("FROM {}".format(alt_name))
prep_dim_un.append("WHERE COD_INDCD_ALT ='I';")
return prep_dim_un
def set_dim_user(self, tb_dim_name, alt_name):
prep_dim_user = []
prep_dim_user.append("INSERT INTO {}".format(tb_dim_name))
prep_dim_user.append("SELECT DISTINCT ANOMESDIA,")
prep_dim_user.append("ITAU_EMPLOYEE_NAME,")
prep_dim_user.append("ID_SAP ,")
prep_dim_user.append("ID_FUNC ,")
prep_dim_user.append("EVERIS_EMPLOYEE_NAME,")
prep_dim_user.append("DT_INIT,")
prep_dim_user.append("DT_END")
prep_dim_user.append("FROM {}".format(alt_name))
prep_dim_user.append("WHERE COD_INDCD_ALT IN('I','A');")
return prep_dim_user
def transform_prep_to_file(self, source_sql, tbl_nm, tbl_wrk_prep,
log_lines):
sq = source_sql
if not self.os.path.exists(sq):
self.os.mkdir(sq)
self.os.chdir(sq)
with open(tbl_nm.lower() + '.sql', 'w') as f:
for i in tbl_wrk_prep:
f.write(i + '\n')
for i in log_lines:
f.write(i + '\n')