class extractGeempre():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
self._columns = []
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo['ExtractCompanies']
def getCompanies(self):
try:
self._cursor = self._connection.cursor()
sql = "SELECT codi_emp, nome_emp FROM bethadba.geempre ORDER BY codi_emp"
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
data = json.loads(df.to_json(orient='records', date_format='iso'))
return data
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
def exportData(self):
try:
today = datetime.today() - relativedelta.relativedelta(months=1)
month = today.month
year = today.year
companies = self.getCompanies()
print('- Exportando dados de movimentação geral empresas:')
for companie in companies:
self._cursor = self._connection.cursor()
sql = readSql(os.path.dirname(os.path.abspath(__file__)), 'geempre_users_acess.sql', companie['codi_emp'], year, month)
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
data = {
'codi_emp': companie['codi_emp'],
f'{year}-{month:0>2}': json.loads(df.to_json(orient='records', date_format='iso'))
}
self._collection.update_one( { "codi_emp": data['codi_emp'] }, { "$set": data}, upsert=True )
print(f"\t -{companie['codi_emp']} - {companie['nome_emp']}")
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
self._DB.closeConnection()
self._connectionMongo.closeConnection()
class extractExportFoAll():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
self._columns = []
def getTables(self):
try:
self._cursor = self._connection.cursor()
sql = readSql(os.path.dirname(os.path.abspath(__file__)),
'tables_db.sql')
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
data = json.loads(df.to_json(orient='records', date_format='iso'))
return data
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
def exportData(self):
cursor = None
try:
tables = self.getTables()
print('- Exportando dados das tabelas:')
for table in tables:
table_name = table['table_name']
print(f'\t- Tabela {table_name}')
if (table['exist_codi_emp'] is not None):
sql = f"SELECT * FROM bethadba.{table_name} WHERE codi_emp IN (1693,1695,1696,1848,1849)"
else:
sql = f"SELECT * FROM bethadba.{table_name}"
cursor = self._connection.cursor()
cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
df.to_csv(os.path.join(wayDefaultToSave, f"{table_name}.csv"),
index=False,
date_format='iso',
sep=';',
decimal='.',
escapechar=None,
quotechar='"')
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if cursor is not None:
cursor.close()
self._DB.closeConnection()
class extractEfentradaspar():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
self._wayCompanies = os.path.join(wayDefaultToSave, 'empresas.json')
def exportData(self,
filterCompanie=0,
filterMonthStart=1,
filterYearStart=2013):
with open(self._wayCompanies) as companies:
data = json.load(companies)
try:
for companie in data:
self._wayToSave = os.path.join(wayDefaultToSave)
if os.path.exists(self._wayToSave) is False:
os.makedirs(self._wayToSave)
self._wayToSave = os.path.join(
self._wayToSave,
f"entradas_parcelas/{companie['codi_emp']}-efentradaspar.json"
)
# only companies actives
if companie[
'codi_emp'] == filterCompanie or filterCompanie == 0:
print(
f"- Exportando parcelas das NFs de entrada da empresa {companie['codi_emp']} - {companie['nome_emp']}"
)
self._cursor = self._connection.cursor()
sql = (
f"SELECT par.codi_emp, par.codi_ent, par.parc_entp, ent.nume_ent, forn.codi_for, ent.ddoc_ent, ent.dent_ent, par.vcto_entp, par.vlor_entp "
f" FROM bethadba.efentradaspar AS par "
f" INNER JOIN bethadba.efentradas AS ent "
f" ON ent.codi_emp = par.codi_emp "
f" AND ent.codi_ent = par.codi_ent "
f" INNER JOIN bethadba.effornece AS forn "
f" ON forn.codi_emp = ent.codi_emp "
f" AND forn.codi_for = ent.codi_for "
f" WHERE ent.codi_emp = {companie['codi_emp']}"
f" AND year(ent.dent_ent) >= {filterYearStart}"
f" AND month(ent.dent_ent) >= {filterMonthStart}"
f"ORDER BY par.codi_emp, ent.dent_ent DESC, par.parc_entp"
)
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
df.to_json(self._wayToSave,
orient='records',
date_format='iso')
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
self._DB.closeConnection()
class extractCtlancto():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
self._today = datetime.date.today()
self._currentMonth = self._today.month
self._currentYear = self._today.year
self._geempre = ExtractGeempre()
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo['ExtractContasContabeis']
def exportData(self, filterCompanie=0):
try:
companies = self._geempre.getCompanies()
for companie in companies:
codi_emp = companie['codi_emp']
if filterCompanie != 0 and filterCompanie != codi_emp:
continue # ignora as empresas que não estão no filtro
print(
f"- Exportando as contas contábeis da empresa {codi_emp} - {companie['nome_emp']}"
)
self._collection.delete_many({'codi_emp': codi_emp})
self._cursor = self._connection.cursor()
sql = readSql(os.path.dirname(os.path.abspath(__file__)),
'ctcontas.sql', codi_emp)
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
data = json.loads(
df.to_json(orient='records', date_format='iso'))
if len(data) > 0:
self._collection.insert_many(data)
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
self._DB.closeConnection()
self._connectionMongo.closeConnection()
class extractGeempre():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
self._wayToSave = 'C:\\Programming\\api-sieg\\backend\\exportData\\companies.json'
self._data = []
self._columns = []
self._dataExport = {}
def exportaDados(self):
try:
self._cursor = self._connection.cursor()
sql = (
f"SELECT codi_emp, nome_emp, cgce_emp, stat_emp, tins_emp, dina_emp "
f"FROM bethadba.geempre "
f"WHERE /*codi_emp IN (1,1403,1444) "
f" AND ( dina_emp >= YMD(year(today()), month(today())-5, 1) OR ( stat_emp NOT IN ('I') ) )"
f" AND*/ tins_emp IN (1,2)"
f" AND cgce_emp IS NOT NULL"
f" AND TRIM( COALESCE(cgce_emp, '')) <> '' "
f"ORDER BY codi_emp")
self._cursor.execute(sql)
row = self._cursor.fetchone()
for t in row.cursor_description:
self._columns.append(t[0])
df = pd.read_sql_query(sql, self._connection)
for column in self._columns:
if df[column].dtype == 'int64':
df[column] = df[column].astype('int64')
elif df[column].dtype == 'float64':
df[column] = df[column].astype('float64')
else:
df[column] = df[column].astype(str).str.replace(
'\\r\\n', '')
df[column] = df[column].replace('\\n', '').replace(
'\\r', '').replace('\\t', '')
df.to_json(self._wayToSave, orient='records')
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
self._DB.closeConnection()
class ExtractGeempre():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._wayToSave = os.path.join(wayDefaultToSave, 'empresas.json')
self._columns = []
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo['ExtractCompanies']
def getCompanies(self):
try:
sql = "SELECT codi_emp, nome_emp FROM bethadba.geempre ORDER BY codi_emp"
df = pd.read_sql_query(sql, self._connection)
data = json.loads(df.to_json(orient='records', date_format='iso'))
return data
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
self._DB.closeConnection()
def exportData(self):
try:
sql = readSql(os.path.dirname(os.path.abspath(__file__)),
'geempre.sql')
df = pd.read_sql_query(sql, self._connection)
data = json.loads(df.to_json(orient='records', date_format='iso'))
print('- Exportando empresas:')
for companie in data:
self._collection.update_one({"codi_emp": companie['codi_emp']},
{"$set": companie},
upsert=True)
print(f"\t-{companie['codi_emp']} - {companie['razao_emp']}")
df.to_json(self._wayToSave, orient='records', date_format='iso')
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
self._DB.closeConnection()
self._connectionMongo.closeConnection()
class TitulosDao:
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
def consultaPagamentoTitulo(self, codi_emp, i_faturamento_e_parcela):
try:
self._cursor = self._connection.cursor()
self._cursor.execute("SELECT SUM(COALESCE(rec.valor_recebido, 0)) AS valor_recebido,"
f" MAX(rec.data_recebimento) as data_recebimento, "
f" SUM( COALESCE(par.valor_original, 0) - COALESCE(par.valor_desconto, 0) - COALESCE(par.valor_desconto_adimplente, 0) ) AS valor_parcela, "
f" MAX(CASE WHEN par.vencimento <> par.vencimento_original THEN 'alterado' ELSE 'nao_alterado' END) AS alterado_venc, "
f" MAX(CASE WHEN rec.valor_refaturado > 0 OR rec.valor_reparcelado > 0 THEN 'renegociado' ELSE 'nao_renegociado' END) AS renegociado "
f" FROM bethadba.hrrecebimento AS rec "
f" INNER JOIN bethadba.hrfaturamento_parcela AS par"
f" ON par.codi_emp = rec.codi_emp "
f" AND par.i_faturamento = rec.i_faturamento "
f" AND par.i_parcela = rec.i_parcela "
f" LEFT OUTER JOIN bethadba.hrrenegociacao_faturamento AS renfat "
f" ON renfat.codi_emp = par.codi_emp "
f" AND renfat.i_faturamento_destino = par.i_faturamento "
f" AND renfat.i_parcela_destino = par.i_parcela "
f" LEFT OUTER JOIN bethadba.hrrenegociacao AS ren "
f" ON ren.codi_emp = renfat.codi_emp "
f" AND ren.i_renegociacao = renfat.i_renegociacao "
f" LEFT OUTER JOIN bethadba.hrfaturamento_parcela AS par_ant "
f" ON par_ant.codi_emp = renfat.codi_emp "
f" AND par_ant.i_faturamento = renfat.i_faturamento_origem "
f" AND par_ant.i_parcela = renfat.i_parcela_origem "
f" WHERE rec.codi_emp = {codi_emp}"
f" AND CASE WHEN ren.i_renegociacao IS NOT NULL THEN STRING(renfat.i_faturamento_origem) || STRING(renfat.i_parcela_origem) "
f" ELSE STRING(par.i_faturamento) || STRING(par.i_parcela) "
f" END = {i_faturamento_e_parcela}")
self._data = self._cursor.fetchall()
return self._data
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
self._DB.closeConnection()
class extractGeConexoesAtivas():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
self._columns = []
self._hourProcessing = datetime.now()
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo[
'ExtractConnectionsDominioActiveTotal']
def exportData(self):
try:
self._cursor = self._connection.cursor()
# sql = ("SELECT * FROM bethadba.geconexoesativas ORDER BY usuario")
sql = ("SELECT count(usuarios.usuario) AS qtd"
" FROM ( SELECT DISTINCT usuario, estacao"
" FROM bethadba.geconexoesativas ) AS usuarios")
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
data = json.loads(df.to_json(orient='records', date_format='iso'))
for connectionActive in data:
connectionActive['hourProcess'] = self._hourProcessing
self._collection.insert_one(connectionActive)
print(f'Dados exportados - {self._hourProcessing}')
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
self._DB.closeConnection()
self._connectionMongo.closeConnection()
class extractEffornece():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
self._wayCompanies = os.path.join(wayDefaultToSave, 'empresas.json')
def exportData(self, filterCompanie=0):
with open(self._wayCompanies) as companies:
data = json.load(companies)
try:
for companie in data:
self._wayToSave = os.path.join(wayDefaultToSave, 'fornecedores')
if os.path.exists(self._wayToSave) is False:
os.makedirs(self._wayToSave)
self._wayToSave = os.path.join(self._wayToSave, f"{companie['codi_emp']}-effornece.json")
# only companies actives
if companie['codi_emp'] == filterCompanie or filterCompanie == 0:
print(f"- Exportando fornecedores da empresa {companie['codi_emp']} - {companie['nome_emp']}")
self._cursor = self._connection.cursor()
sql = ( f"SELECT forn.codi_for, forn.nome_for, forn.nomr_for, forn.cgce_for, forn.codi_cta, forn.insc_for, forn.imun_for, "
f" forn.codigo_municipio, forn.sigl_est, forn.conta_cliente_for, forn.conta_compensacao_for "
f" FROM bethadba.effornece AS forn "
f" WHERE forn.codi_emp = {companie['codi_emp']}"
f"ORDER BY forn.codi_emp, forn.codi_for")
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
df.to_json(self._wayToSave, orient='records', date_format='iso' )
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
self._DB.closeConnection()
class extractAccountBalance():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
self._wayCompanies = os.path.join(wayDefault['wayDefaultToSaveFiles'], 'empresas.json')
self._dataCompanies = readJson(self._wayCompanies)
self._baseWayToSave = os.path.join(wayDefault['wayDefaultToSaveFiles'], 'entradas')
if os.path.exists(self._baseWayToSave) is False:
os.makedirs(self._baseWayToSave)
self._today = datetime.date.today()
self._currentMonth = self._today.month
self._currentYear = self._today.year
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo['AccountBalance']
def exportData(self, filterCompanie=0, filterMonthStart=1, filterYearStart=2015, filterMonthEnd=0, filterYearEnd=0):
filterMonthEnd = self._currentMonth if filterMonthEnd == 0 else filterMonthEnd
filterYearEnd = self._currentYear if filterYearEnd == 0 else filterYearEnd
try:
for companie in self._dataCompanies:
codi_emp = companie['codi_emp']
if filterCompanie != 0 and filterCompanie != codi_emp:
continue # ignora as empresas que não estão no filtro
print(f"- Exportando saldos das contas contábeis da empresa {codi_emp} - {companie['nome_emp']}")
wayToSaveCompanie = os.path.join(self._baseWayToSave, str(codi_emp))
if os.path.exists(wayToSaveCompanie) is False:
os.makedirs(wayToSaveCompanie)
competenceStartEnd = extractFunctions.returnCompetenceStartEnd(companie, filterMonthStart, filterYearStart, filterMonthEnd, filterYearEnd)
startYear = competenceStartEnd['filterYearStart']
endYear = competenceStartEnd['filterYearEnd']
year = startYear
while year <= endYear:
print(f'\t - {year}', end='')
self._cursor = self._connection.cursor()
sql = ( "SELECT emp.codi_emp,"
" emp.nome_emp,"
" emp.cgce_emp,"
" con.codi_cta,"
" con.nome_cta,"
" con.clas_cta,"
" con.tipo_cta,"
" deb = IF con.tipo_cta = 'S' THEN"
" IsNull((SELECT sum(l.vlor_lan)"
" FROM bethadba.ctlancto AS l"
" INNER JOIN bethadba.ctcontas AS c"
" ON c.codi_emp = l.codi_emp"
" WHERE l.codi_emp = con.codi_emp"
f" AND l.data_lan > DATE('1900-01-01') AND YEAR(l.data_lan) <= {year}"
" AND Trim(con.clas_cta) = substr(c.clas_cta, 1, length(con.clas_cta))"
" AND c.codi_cta = l.cdeb_lan), 0)"
" ELSE"
" IsNull((SELECT sum(l.vlor_lan)"
" FROM bethadba.ctlancto AS l"
" INNER JOIN bethadba.ctcontas AS c"
" ON c.codi_emp = l.codi_emp"
" WHERE l.codi_emp = con.codi_emp"
f" AND l.data_lan > DATE('1900-01-01') AND YEAR(l.data_lan) <= {year}"
" AND c.codi_cta = con.codi_cta"
" AND c.codi_cta = l.cdeb_lan), 0) ENDIF,"
" cred = IF con.tipo_cta = 'S' THEN "
" IsNull((SELECT sum(l.vlor_lan) "
" FROM bethadba.ctlancto AS l"
" INNER JOIN bethadba.ctcontas AS c"
" ON c.codi_emp = l.codi_emp "
" WHERE l.codi_emp = con.codi_emp"
f" AND l.data_lan > DATE('1900-01-01') AND YEAR(l.data_lan) <= {year}"
" AND Trim(con.clas_cta) = substr(c.clas_cta, 1, length(con.clas_cta)) "
" AND c.codi_cta = l.ccre_lan), 0)"
" ELSE "
" IsNull((SELECT sum(l.vlor_lan) "
" FROM bethadba.ctlancto AS l"
" INNER JOIN bethadba.ctcontas AS c"
" ON c.codi_emp = l.codi_emp "
" WHERE l.codi_emp = con.codi_emp"
f" AND l.data_lan > DATE('1900-01-01') AND YEAR(l.data_lan) <= {year}"
" AND c.codi_cta = con.codi_cta"
" AND c.codi_cta = l.ccre_lan), 0) ENDIF,"
" saldo = deb - cred"
" FROM bethadba.ctcontas AS con"
" INNER JOIN bethadba.geempre AS emp"
" ON emp.codi_emp = con.codi_emp"
f" WHERE emp.codi_emp = {codi_emp}"
" AND saldo <> 0"
" ORDER BY emp.codi_emp, con.clas_cta, con.nome_cta" )
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
data = json.loads(df.to_json(orient='records', date_format='iso'))
for accountBalance in data:
accountBalance['year'] = year
self._collection.update_one( {
"codi_emp": accountBalance['codi_emp'],
"codi_cta": accountBalance['codi_cta'],
"year": accountBalance['year']
},
{ "$set": accountBalance },
upsert=True
)
print('')
year += 1
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
self._DB.closeConnection()
class CompaniesMonthsIntegrated():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo[
'CompaniesMonthsIntegrated'] # vai adicionar na tabela de empresas os dados
def getCompaniesMonthsIntegrated(self, codiEmp):
try:
sql = readSql(os.path.dirname(os.path.abspath(__file__)),
'companies_months_integrated.sql', codiEmp)
df = pd.read_sql_query(sql, self._connection)
data = json.loads(df.to_json(orient='records', date_format='iso'))
return data
except Exception as e:
print(e)
def checkMonthsIntegrated(self, companieSettingView,
companieMonthsIntegrated):
statusAccountPaid = treatTextField(
companieSettingView['statusAccountPaid'])
isCompanyBranch = treatTextField(
companieSettingView['isCompanyBranch'])
dateAccountPaid = retornaCampoComoData(
analyzeIfFieldIsValid(companieSettingView, 'dateAccountPaid',
'01/01/1900'))
dateStart = retornaCampoComoData('01/01/2021')
dateNow = datetime.today() - relativedelta(months=1)
year = dateStart.year
startYear = dateStart.year
startMonth = dateStart.month
endYear = dateNow.year
endMonth = dateNow.month
while year <= endYear:
months = extractFunctions.returnMonthsOfYear(
year, startMonth, startYear, endMonth, endYear)
print('\t\t - ', end='')
for month in months:
monthYearFormated = f'{month:0>2}/{year}'
competence = retornaCampoComoData(f"01/{monthYearFormated}")
print(f'{monthYearFormated}, ', end='')
integrated = list(
filter(
lambda companieMonths: companieMonths['comp'][:10] ==
f"{year}-{month:0>2}-01", companieMonthsIntegrated))
try:
companieSettingView['monthIntegratedMandatory'] = True if dateAccountPaid < competence and \
statusAccountPaid.find('CONCLUIDA') >= 0 and statusAccountPaid.find('ANTIGO') < 0 and \
isCompanyBranch == "NAO" else False
except Exception:
companieSettingView['monthIntegratedMandatory'] = False
if len(integrated) > 0:
integrated = integrated[0]
companieSettingView[
'existIntegrated'] = True if integrated[
'qtd_lan_ti_importado_dlan'] > 0 else False
companieSettingView[
'qtd_lan_ti_importado_dlan'] = analyzeIfFieldIsValid(
integrated, 'qtd_lan_ti_importado_dlan', 0)
companieSettingView[
'qtd_lan_ti_importado_dori'] = analyzeIfFieldIsValid(
integrated, 'qtd_lan_ti_importado_dori', 0)
companieSettingView[
'qtd_lan_operacao'] = analyzeIfFieldIsValid(
integrated, 'qtd_lan_operacao', 0)
companieSettingView[
'qtd_lan_operacao_dori'] = analyzeIfFieldIsValid(
integrated, 'qtd_lan_operacao_dori', 0)
companieSettingView['cgce_matriz'] = analyzeIfFieldIsValid(
integrated, 'cgce_matriz')
companieSettingView['competence'] = f'{year}-{month:0>2}'
self._collection.update_one(
{
"codi_emp": companieSettingView['codi_emp'],
"competence": companieSettingView['competence']
}, {"$set": companieSettingView},
upsert=True)
print('')
year += 1
def exportData(self):
print('- Exportando dados de integração:')
try:
companiesSettingsView = list(
self._dbMongo['CompaniesSettingsView'].find({}))
for companieSettingView in companiesSettingsView:
del companieSettingView['_id']
codiEmp = companieSettingView['codi_emp']
print(
f"\t- Processando empresa {codiEmp} - {companieSettingView['nome_emp']}"
)
companieMonthsIntegrated = self.getCompaniesMonthsIntegrated(
codiEmp)
self.checkMonthsIntegrated(companieSettingView,
companieMonthsIntegrated)
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
self._connectionMongo.closeConnection()
self._DB.closeConnection()
class extractCtlancto():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
self._today = datetime.date.today()
self._currentMonth = self._today.month
self._currentYear = self._today.year
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo['ExtractLancContabeis']
def getCompanies(self):
try:
self._cursor = self._connection.cursor()
sql = "SELECT codi_emp, nome_emp FROM bethadba.geempre WHERE stat_emp = 'A' ORDER BY codi_emp"
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
data = json.loads(df.to_json(orient='records', date_format='iso'))
return data
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
def exportData(self, filterCompanie=0, filterMonthStart=5, filterYearStart=2015, filterMonthEnd=0, filterYearEnd=0):
filterMonthEnd = self._currentMonth if filterMonthEnd == 0 else filterMonthEnd
filterYearEnd = self._currentYear if filterYearEnd == 0 else filterYearEnd
try:
companies = self.getCompanies()
for companie in companies:
codi_emp = companie['codi_emp']
if filterCompanie != 0 and filterCompanie != codi_emp:
continue # ignora as empresas que não estão no filtro
print(f"- Exportando os lançamentos contábeis da empresa {codi_emp} - {companie['nome_emp']}")
competenceStartEnd = extractFunctions.returnCompetenceStartEnd(companie, filterMonthStart, filterYearStart, filterMonthEnd, filterYearEnd)
startMonth = competenceStartEnd['filterMonthStart']
startYear = competenceStartEnd['filterYearStart']
endMonth = competenceStartEnd['filterMonthEnd']
endYear = competenceStartEnd['filterYearEnd']
year = startYear
while year <= endYear:
months = extractFunctions.returnMonthsOfYear(year, startMonth, startYear, endMonth, endYear)
print('\t - ', end='')
for month in months:
print(f'{month:0>2}/{year}, ', end='')
# tem que deletar os dados mensais, pois não dá pra atualizar as informações, visto que o codi_ent que seria
# o item a ser atualizado pode mudar na domínio. Antes uma nota que era 100 pode ser excluída, e a 100 ser
# outra nota totalmente diferente
self._collection.delete_many( {"$and": [{'codi_emp': companie['codi_emp']}, {'monthFilter': month}, {'yearFilter': year}] } )
self._cursor = self._connection.cursor()
sql = readSql(os.path.dirname(os.path.abspath(__file__)), 'ctlancto.sql', year, month, companie['codi_emp'], year, month)
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
data = json.loads(df.to_json(orient='records', date_format='iso'))
if len(data) > 0:
self._collection.insert_many( data )
print('')
year += 1
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
self._DB.closeConnection()
self._connectionMongo.closeConnection()
class extractEfsaidas():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
self._wayCompanies = os.path.join(wayDefault['wayDefaultToSaveFiles'],
'empresas.json')
self._dataCompanies = readJson(self._wayCompanies)
self._baseWayToSave = os.path.join(wayDefault['wayDefaultToSaveFiles'],
'saidas')
if os.path.exists(self._baseWayToSave) is False:
os.makedirs(self._baseWayToSave)
self._today = datetime.date.today()
self._currentMonth = self._today.month
self._currentYear = self._today.year
def exportData(self,
filterCompanie=0,
filterMonthStart=1,
filterYearStart=2013,
filterMonthEnd=0,
filterYearEnd=0):
filterMonthEnd = self._currentMonth if filterMonthEnd == 0 else filterMonthEnd
filterYearEnd = self._currentYear if filterYearEnd == 0 else filterYearEnd
try:
for companie in self._dataCompanies:
codi_emp = companie['codi_emp']
if filterCompanie != 0 and filterCompanie != codi_emp:
continue # ignora as empresas que não estão no filtro
print(
f"- Exportando NF de saídas da empresa {codi_emp} - {companie['nome_emp']}"
)
wayToSaveCompanie = os.path.join(self._baseWayToSave,
str(codi_emp))
if os.path.exists(wayToSaveCompanie) is False:
os.makedirs(wayToSaveCompanie)
competenceStartEnd = extractFunctions.returnCompetenceStartEnd(
companie, filterMonthStart, filterYearStart,
filterMonthEnd, filterYearEnd)
startMonth = competenceStartEnd['filterMonthStart']
startYear = competenceStartEnd['filterYearStart']
endMonth = competenceStartEnd['filterMonthEnd']
endYear = competenceStartEnd['filterYearEnd']
year = startYear
while year <= endYear:
months = extractFunctions.returnMonthsOfYear(
year, startMonth, startYear, endMonth, endYear)
print('\t - ', end='')
for month in months:
print(f'{month:0>2}/{year}, ', end='')
self._wayToSave = os.path.join(
wayToSaveCompanie, f'{str(year)}{month:0>2}.json')
self._cursor = self._connection.cursor()
sql = (
f"SELECT nf.codi_emp, nf.codi_sai, nf.nume_sai, nf.codi_cli, nf.codi_esp, nf.codi_acu, nf.codi_nat, nf.segi_sai, "
f" nf.seri_sai, nf.dsai_sai, nf.ddoc_sai, nf.vcon_sai, nf.situacao_sai, nf.chave_nfe_sai "
f" FROM bethadba.efsaidas AS nf "
f" WHERE nf.codi_emp = {codi_emp}"
f" AND year(nf.ddoc_sai) = {year}"
f" AND month(nf.ddoc_sai) = {month}"
f"ORDER BY nf.codi_emp, nf.codi_sai")
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
df.to_json(self._wayToSave,
orient='records',
date_format='iso')
print('')
year += 1
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
self._DB.closeConnection()
class CompaniesMonthsIntegrated():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo['CompaniesMonthsAmountNotes']
def getCompaniesMonthsAmountNotes(self, cgceMatriz):
try:
sql = readSql(os.path.dirname(os.path.abspath(__file__)), 'companies_months_fiscal_lancamentos.sql', cgceMatriz, cgceMatriz, cgceMatriz)
df = pd.read_sql_query(sql, self._connection)
data = json.loads(df.to_json(orient='records', date_format='iso'))
return data
except Exception as e:
print(e)
def saveMongo(self, codiEmp, cgceMatriz, companieMonthsAmountNotes):
dateStart = retornaCampoComoData('01/01/2021')
dateNow = datetime.today() - relativedelta(months=1)
year = dateStart.year
startYear = dateStart.year
startMonth = dateStart.month
endYear = dateNow.year
endMonth = dateNow.month
while year <= endYear:
months = extractFunctions.returnMonthsOfYear(year, startMonth, startYear, endMonth, endYear)
print('\t\t - ', end='')
for month in months:
companieDataToSave = {}
monthYearFormated = f'{month:0>2}/{year}'
print(f'{monthYearFormated}, ', end='')
amountNotaSaida = list(filter(lambda companieMonths: companieMonths['comp'][:10] == f"{year}-{month:0>2}-01" \
and companieMonths['tipo'] == 'SAIDA', companieMonthsAmountNotes))
amountNotaSaida = analyzeIfFieldIsValidMatrix(amountNotaSaida, 1, [])
amountNotaEntrada = list(filter(lambda companieMonths: companieMonths['comp'][:10] == f"{year}-{month:0>2}-01" \
and companieMonths['tipo'] == 'ENTRADA', companieMonthsAmountNotes))
amountNotaEntrada = analyzeIfFieldIsValidMatrix(amountNotaEntrada, 1, [])
amountNotaServico = list(filter(lambda companieMonths: companieMonths['comp'][:10] == f"{year}-{month:0>2}-01" \
and companieMonths['tipo'] == 'SERVICO', companieMonthsAmountNotes))
amountNotaServico = analyzeIfFieldIsValidMatrix(amountNotaServico, 1, [])
companieDataToSave['qtd_notas_saidas_operacao'] = analyzeIfFieldIsValid(amountNotaSaida, 'qtd_notas_operacao', 0)
companieDataToSave['qtd_notas_saidas_operacao_dori'] = analyzeIfFieldIsValid(amountNotaSaida, 'qtd_notas_operacao_dori', 0)
companieDataToSave['qtd_notas_entradas_operacao'] = analyzeIfFieldIsValid(amountNotaEntrada, 'qtd_notas_operacao', 0)
companieDataToSave['qtd_notas_entradas_operacao_dori'] = analyzeIfFieldIsValid(amountNotaEntrada, 'qtd_notas_operacao_dori', 0)
companieDataToSave['qtd_notas_servicos_operacao'] = analyzeIfFieldIsValid(amountNotaServico, 'qtd_notas_operacao', 0)
companieDataToSave['qtd_notas_servicos_operacao_dori'] = analyzeIfFieldIsValid(amountNotaServico, 'qtd_notas_operacao_dori', 0)
companieDataToSave['codi_emp'] = codiEmp
companieDataToSave['cgce_matriz'] = cgceMatriz
companieDataToSave['competence'] = f'{year}-{month:0>2}'
self._collection.update_one(
{
"codi_emp": companieDataToSave['codi_emp'],
"competence": companieDataToSave['competence']
},
{ "$set": companieDataToSave },
upsert=True
)
print('')
year += 1
def exportData(self):
print('- Exportando dados de integração:')
try:
companiesSettingsView = list(self._dbMongo['CompaniesSettingsView'].find({}))
for companieSettingView in companiesSettingsView:
del companieSettingView['_id']
codiEmp = companieSettingView['codi_emp']
print(f"\t- Processando empresa {codiEmp} - {companieSettingView['nome_emp']}")
cgceEmp = str(treatNumberField(companieSettingView['cgce_emp']))
cgceMatriz = cgceEmp[:8]
isCompanyBranch = treatTextField(companieSettingView['isCompanyBranch'])
if isCompanyBranch == 'NAO':
companieMonthsAmountNotes = self.getCompaniesMonthsAmountNotes(cgceMatriz)
else:
companieMonthsAmountNotes = self.getCompaniesMonthsAmountNotes(cgceEmp)
self.saveMongo(codiEmp, cgceMatriz, companieMonthsAmountNotes)
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
self._connectionMongo.closeConnection()
self._DB.closeConnection()
class extractEfmvspro():
def __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
self._wayCompanies = os.path.join(wayDefault['wayDefaultToSaveFiles'],
'empresas.json')
self._dataCompanies = readJson(self._wayCompanies)
self._baseWayToSave = os.path.join(wayDefault['wayDefaultToSaveFiles'],
'saidas_produtos')
if os.path.exists(self._baseWayToSave) is False:
os.makedirs(self._baseWayToSave)
self._today = datetime.date.today()
self._currentMonth = self._today.month
self._currentYear = self._today.year
def exportData(self,
filterCompanie=0,
filterMonthStart=1,
filterYearStart=2019,
filterMonthEnd=0,
filterYearEnd=0):
filterMonthEnd = self._currentMonth if filterMonthEnd == 0 else filterMonthEnd
filterYearEnd = self._currentYear if filterYearEnd == 0 else filterYearEnd
try:
for companie in self._dataCompanies:
codi_emp = companie['codi_emp']
if filterCompanie != 0 and filterCompanie != codi_emp:
continue # ignora as empresas que não estão no filtro
print(
f"- Exportando produtos das NF de saídas da empresa {codi_emp} - {companie['nome_emp']}"
)
wayToSaveCompanie = os.path.join(self._baseWayToSave,
str(codi_emp))
if os.path.exists(wayToSaveCompanie) is False:
os.makedirs(wayToSaveCompanie)
competenceStartEnd = extractFunctions.returnCompetenceStartEnd(
companie, filterMonthStart, filterYearStart,
filterMonthEnd, filterYearEnd)
startMonth = competenceStartEnd['filterMonthStart']
startYear = competenceStartEnd['filterYearStart']
endMonth = competenceStartEnd['filterMonthEnd']
endYear = competenceStartEnd['filterYearEnd']
year = startYear
while year <= endYear:
months = extractFunctions.returnMonthsOfYear(
year, startMonth, startYear, endMonth, endYear)
print('\t - ', end='')
for month in months:
print(f'{month:0>2}/{year}, ', end='')
self._wayToSave = os.path.join(
wayToSaveCompanie, f'{str(year)}{month:0>2}.json')
self._cursor = self._connection.cursor()
sql = (
f"SELECT pro.codi_emp, codigo_nota = pro.codi_sai, numero = sai.nume_sai, cli_for = cli.nome_cli, chave_nfe = sai.chave_nfe_sai, "
f" emissao = sai.ddoc_sai, saida_entrada = sai.dsai_sai, codi_pdi = pro.codi_pdi, desc_pdi = procad.desc_pdi, "
f" cfop = pro.cfop_msp, qtd = pro.qtde_msp, vunit = pro.valor_unit_msp, vtot = pro.vpro_msp /*, pro.vipi_msp, pro.bcal_msp, "
f" pro.cst_msp, pro.vdes_msp, pro.bicms_msp, pro.bicmsst_msp, pro.aliicms_msp, pro.valor_icms_msp, pro.valor_subtri_msp, "
f" pro.vfre_msp, pro.vseg_msp, pro.vdesace_msp */ "
f" FROM bethadba.efmvspro AS pro "
f" INNER JOIN bethadba.efsaidas AS sai "
f" ON sai.codi_emp = pro.codi_emp "
f" AND sai.codi_sai = pro.codi_sai "
f" INNER JOIN bethadba.efclientes AS cli "
f" ON cli.codi_emp = sai.codi_emp "
f" AND cli.codi_cli = sai.codi_cli "
f" INNER JOIN bethadba.efprodutos AS procad "
f" ON procad.codi_emp = pro.codi_emp "
f" AND procad.codi_pdi = pro.codi_pdi "
f" WHERE sai.codi_emp = {codi_emp}"
f" AND year(sai.ddoc_sai) = {year}"
f" AND month(sai.ddoc_sai) = {month}"
f"ORDER BY pro.codi_emp, pro.codi_sai, pro.nume_msp"
)
self._cursor.execute(sql)
df = pd.read_sql_query(sql, self._connection)
df.to_json(self._wayToSave,
orient='records',
date_format='iso')
print('')
year += 1
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
if self._cursor is not None:
self._cursor.close()
self._DB.closeConnection()