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 exportData(self): try: 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__)), 'geempremovements.sql', companie['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'))[0] 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 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 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 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 get(self, codi_emp, end_date): try: sql = readSql(os.path.dirname(os.path.abspath(__file__)), 'get_has_zeramento.sql', end_date, 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(e)
def get(self): try: sql = readSql(os.path.dirname(os.path.abspath(__file__)), 'get_natureza_conta.sql', self._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(e) finally: self._DB.closeConnection()
def get(self, codi_emp, codi_emp_plano_contas, clas_cta, start_date, end_date): try: sql = readSql(os.path.dirname(os.path.abspath(__file__)), 'get_balances.sql', codi_emp, codi_emp_plano_contas, clas_cta, end_date, codi_emp, codi_emp_plano_contas, clas_cta, end_date) 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 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): 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()
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()