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()
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 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 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,
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()
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()