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 __init__(self):
self._columns = []
self._geempre = ExtractGeempre()
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo['ExtractCompanies']
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()
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 __init__(self, dataToSave, collectionName):
self._dataTosave = dataToSave
self._dataTosave['date_save'] = datetime.datetime.now().strftime(
"%Y-%m-%d")
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo[collectionName]
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 __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 __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'] # vai adicionar na tabela de empresas os dados
def __init__(self):
self._namesProductsBase = leXls_Xlsx(os.path.join('C:/Programming/baymax/backend/extract/data/bkp', 'produtos_comparar.xlsx'))
self._namesProductsBase = funcoesUteis.removeAnArrayFromWithinAnother(self._namesProductsBase)
self._geempre = ExtractGeempre()
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['ExtractEntryNoteProducts']
class extractGeempre():
def __init__(self):
self._columns = []
self._geempre = ExtractGeempre()
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo['ExtractCompanies']
def process(self):
try:
companiesDominio = self._geempre.getCompanies()
companiesExcel = leXls_Xlsx(
'C:/Programming/baymax/backend/extract/data/empresas_integrattion_old.xlsx',
'Já Avaliadas - Hist')
print('- Lendo dados das integrações antigas:')
for companie in companiesExcel:
codi_emp = funcoesUteis.treatNumberField(companie[0],
isInt=True)
nome_emp = funcoesUteis.treatTextField(companie[1])
responsibleFinancialClientOld = funcoesUteis.treatTextField(
companie[4])
obsAccountPaidOld = funcoesUteis.treatTextField(companie[8])
dateAccountPaidOld = funcoesUteis.treatTextField(companie[7])
layoutsAccountPaidOld = funcoesUteis.treatTextField(
companie[9])
hasIntegrated = funcoesUteis.treatTextField(companie[10])
dataCompanie = {
'codi_emp': codi_emp,
'responsibleFinancialClientOld':
responsibleFinancialClientOld,
'obsAccountPaidOld': obsAccountPaidOld,
'dateAccountPaidOld': dateAccountPaidOld,
'layoutsAccountPaidOld': layoutsAccountPaidOld
}
existCompanieDominio = list(
filter(
lambda companieDominio: companieDominio['codi_emp'] ==
codi_emp, companiesDominio))
if len(existCompanieDominio) > 0 and hasIntegrated == "SIM":
self._collection.update_one({"codi_emp": codi_emp},
{"$set": dataCompanie},
upsert=True)
print(f"\t - {codi_emp} - {nome_emp}")
except Exception as e:
print(f"O erro é: {e}")
finally:
self._connectionMongo.closeConnection()
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 __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 __init__(self):
self._DB = DB()
self._connection = self._DB.getConnection()
self._cursor = None
# self._baseWayToSave = os.path.join(wayDefault['wayDefaultToSaveFiles'], 'entradas_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
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo['ExtractEntryNoteProducts']
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()
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']
class GetSettingsCompany(object):
def __init__(self, codiEmp):
self._codiEmp = codiEmp
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo.IntegrattionCompanies
def getSettingsLayout(self, layouts):
settingsLayouts = []
try:
collectionLayouts = self._dbMongo.IntegrattionLayouts
for layout in layouts:
idLayout = funcoesUteis.analyzeIfFieldIsValid(layout, 'idLayout', None)
settings = collectionLayouts.find_one( { "_id": ObjectId(idLayout) } )
bankAndAccountCorrelation = funcoesUteis.analyzeIfFieldIsValid(layout, 'bankAndAccountCorrelation', None)
validateIfDataIsThisCompanie = funcoesUteis.analyzeIfFieldIsValid(layout, 'validateIfDataIsThisCompanie', None)
if settings is not None:
settings['bankAndAccountCorrelation'] = bankAndAccountCorrelation
settings['validateIfDataIsThisCompanie'] = validateIfDataIsThisCompanie
settingsLayouts.append(settings)
except Exception:
pass
return settingsLayouts
def getSettingsFinancy(self):
try:
settings = self._collection.find_one( { "codi_emp": self._codiEmp } )
layouts = funcoesUteis.returnDataFieldInDict(settings, ['accountPaid', 'layouts'])
settingsLayouts = self.getSettingsLayout(layouts)
settings['settingsLayouts'] = settingsLayouts
except Exception:
settings = None
finally:
self._connectionMongo.closeConnection()
return settings
class extractGeempre():
def __init__(self):
self._columns = []
self._geempre = ExtractGeempre()
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo['ExtractCompanies']
def process(self):
try:
companiesDominio = list(self._collection.find({}))
companiesExcel = leXls_Xlsx(
'C:/Programming/baymax/backend/extract/data/empresas_externos.xlsx',
'GERAL')
print('- Processando empresas dos externos:')
for companie in companiesExcel:
codi_emp = funcoesUteis.treatNumberField(companie[0],
isInt=True)
nome_emp = funcoesUteis.treatTextField(companie[1])
existCompanieDominio = list(
filter(
lambda companieDominio: companieDominio['codi_emp'] ==
codi_emp, companiesDominio))
if len(existCompanieDominio) > 0:
dataCompanie = {
'codi_emp':
codi_emp,
'grupos_contabil':
f"{existCompanieDominio[0]['grupos_contabil']},EXTERNOS",
'grupos_fiscal':
f"{existCompanieDominio[0]['grupos_fiscal']},EXTERNOS"
}
self._collection.update_one({"codi_emp": codi_emp},
{"$set": dataCompanie},
upsert=True)
print(f"\t - {codi_emp} - {nome_emp}")
except Exception as e:
print(f"O erro é: {e}")
finally:
self._connectionMongo.closeConnection()
class ReplicateDataCompaniesSettingsToView(object):
def __init__(self):
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collectionCompaniesSettings = self._dbMongo.CompaniesSettings
self._collectionCompaniesSettingsView = self._dbMongo.CompaniesSettingsView
def makeSaveData(self, data):
obj = {
"codi_emp":
funcoesUteis.analyzeIfFieldIsValid(data, 'codi_emp'),
"statusAccountPaid":
funcoesUteis.analyzeIfFieldIsValid(data, 'statusAccountPaid'),
"responsibleFinancialClient":
funcoesUteis.analyzeIfFieldIsValid(data,
'responsibleFinancialClient'),
"layoutsAccountPaid":
funcoesUteis.analyzeIfFieldIsValid(data, 'layoutsAccountPaid'),
"dateAccountPaid":
funcoesUteis.analyzeIfFieldIsValid(data, 'dateAccountPaid'),
"analystReceivedTraining":
funcoesUteis.analyzeIfFieldIsValid(data,
'analystReceivedTraining'),
"emailAccountPaid":
funcoesUteis.analyzeIfFieldIsValid(data, 'emailAccountPaid'),
"telefoneAccountPaid":
funcoesUteis.analyzeIfFieldIsValid(data, 'telefoneAccountPaid'),
"obsAccountPaid":
funcoesUteis.analyzeIfFieldIsValid(data, 'obsAccountPaid')
}
return obj
def process(self):
try:
dataCompaniesSettings = self._collectionCompaniesSettings.find()
for data in dataCompaniesSettings:
dataCompaniesSettingsView = self._collectionCompaniesSettingsView.find_one(
{"codi_emp": data['codi_emp']})
self._collectionCompaniesSettings.update_one(
{"codi_emp": data['codi_emp']},
{"$set": self.makeSaveData(dataCompaniesSettingsView)})
except Exception as e:
print(e)
finally:
self._connectionMongo.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 extractGeempre():
def __init__(self):
self._columns = []
self._geempre = ExtractGeempre()
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo['ExtractCompanies']
def process(self):
try:
companiesDominio = self._geempre.getCompanies()
companiesExcel = leXls_Xlsx(
'C:/Programming/baymax/backend/extract/data/empresas_grupo.xlsm',
'EMPRESAS')
print('- Exportando dados de movimentação geral empresas:')
for companie in companiesExcel:
codi_emp = funcoesUteis.treatNumberField(companie[0],
isInt=True)
nome_emp = funcoesUteis.treatTextField(companie[1])
group = funcoesUteis.treatTextField(companie[2])
dataCompanie = {'codi_emp': codi_emp, 'groupCompanie': group}
existCompanieDominio = list(
filter(
lambda companieDominio: companieDominio['codi_emp'] ==
codi_emp, companiesDominio))
if len(existCompanieDominio) > 0:
self._collection.update_one({"codi_emp": codi_emp},
{"$set": dataCompanie},
upsert=True)
print(f"\t - {codi_emp} - {nome_emp}")
except Exception as e:
print(f"O erro é: {e}")
finally:
self._connectionMongo.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 SaveProcessDb():
def __init__(self, dataToSave, collectionName):
self._dataTosave = dataToSave
self._dataTosave['date_save'] = datetime.datetime.now().strftime(
"%Y-%m-%d")
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo[collectionName]
def save(self):
try:
self._collection.update_one(
{
"codi_emp": self._dataTosave['codi_emp'],
"competence": self._dataTosave['competence']
}, {"$set": self._dataTosave},
upsert=True)
except Exception as e:
print(f"Erro ao executar a consulta. O erro é: {e}")
finally:
self._connectionMongo.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 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['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()
def __init__(self):
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collectionCompaniesSettings = self._dbMongo.CompaniesSettings
self._collectionCompaniesSettingsView = self._dbMongo.CompaniesSettingsView
class AnalyzesFixedAssetsProducts(object):
def __init__(self):
self._namesProductsBase = leXls_Xlsx(os.path.join('C:/Programming/baymax/backend/extract/data/bkp', 'produtos_comparar.xlsx'))
self._namesProductsBase = funcoesUteis.removeAnArrayFromWithinAnother(self._namesProductsBase)
self._geempre = ExtractGeempre()
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['ExtractEntryNoteProducts']
def getProductsFixedAssets(self, codi_emp, month, year, products):
try:
collectionFixedAssets = self._dbMongo['EntryNoteProductsFixedAssets']
collectionFixedAssets.delete_many( {"$and": [{'codi_emp': codi_emp}, {'monthFilter': month}, {'yearFilter': year}] } )
for product in products:
if str(product['cfop']) in ('1551', '2551', '3551', '1406', '2406'):
product['classificado_corretamente_cfop'] = True
collectionFixedAssets.insert_one(product)
elif str(product['cfop']) in ('1556', '2556', '3556', '1407', '2407'):
vunit = funcoesUteis.treatDecimalField(product['vunit'])
if vunit >= 1200:
desc_pdi = funcoesUteis.treatTextField(product['desc_pdi'])
hasNameProductFixedAsset = list(filter(lambda name: desc_pdi.find(funcoesUteis.treatTextField(name)) >= 0, self._namesProductsBase))
if len(hasNameProductFixedAsset) > 0:
product['classificado_corretamente_cfop'] = False
collectionFixedAssets.insert_one(product)
else:
continue
else:
continue
except Exception:
pass
def process(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._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"- Procurando produtos imobilizados {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='')
products = self._collection.find({"$and": [{'codi_emp': codi_emp}, {'monthFilter': month}, {'yearFilter': year}] })
self.getProductsFixedAssets(codi_emp, month, year, products)
print('')
year += 1
except Exception as e:
print(f"Erro: {e}")
finally:
self._connectionMongo.closeConnection()
def __init__(self, codiEmp):
self._codiEmp = codiEmp
self._connectionMongo = ConnectMongo()
self._dbMongo = self._connectionMongo.getConnetion()
self._collection = self._dbMongo.IntegrattionCompanies
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()