def __init__(self, inscricaoFederal: str):
self._inscricaoFederal = inscricaoFederal
self._connectMongo = ConnectMongoDB()
self._database = self._connectMongo.getConnetion()
self._listSumObj: List[Dict[str, str]] = []
self._sumObj: Dict[str, str] = {}
def __init__(self, inscricaoFederal: str, monthStart: int, yearStart: int,
monthEnd: int, yearEnd: int):
self._inscricaoFederal = inscricaoFederal
self._monthStart = monthStart
self._yearStart = yearStart
self._monthEnd = monthEnd
self._yearEnd = yearEnd
self._connectMongo = ConnectMongoDB()
self._database = self._connectMongo.getConnetion()
class AnalyseNcmXml():
def __init__(self, pathWithXmlFiles: str):
self._pathWithXmlFiles = pathWithXmlFiles
self._listNfeAlreadyRead: List[str] = []
self._folderSaveResult = os.path.join(dirNameSrc, '..', 'data', 'processed',
'resultado_analise', f'{getDateTimeNowInFormatStr()}.csv')
self._instancesOfClasses()
def _instancesOfClasses(self):
getNcms = GetNcms(envJson['rules_of_xml'], saveResultProcessInFile=False, returnOnlyValueNcm=True, silent=True)
self._listNcms = getNcms.processAll()
getNcmsName = GetNcmsName(saveResultProcessInFile=False, silent=True)
self._listNcmsName = getNcmsName.processAll()
listCfopTributado = ListCfopTributado()
self._ListCfopTributado = listCfopTributado.getList()
listCfopDevolucao = ListCfopDevolucao()
self._ListCfopDevolucao = listCfopDevolucao.getList()
self._connectMongo = ConnectMongoDB()
self._database = self._connectMongo.getConnetion()
def _process(self, pathFile):
indexNfe = IndexNfe(pathFile)
nfe = indexNfe.process()
if nfe is not None:
self._listNfeAlreadyRead.append(nfe['chave_nota'])
if self._listNfeAlreadyRead.count(nfe['chave_nota']) > 1:
return 'AlreadyProcessed'
compareXmlXNcmRules = CompareXmlXNcmRules(
self._database, nfe,
self._listNcmsName, self._listNcms,
self._ListCfopTributado, self._ListCfopDevolucao)
compareXmlXNcmRules.process()
# copy files that dont read to analyse whice is problem
else:
pathToSaveXmlsDontRead = os.path.join(self._pathWithXmlFiles, 'dont_read')
if os.path.exists(pathToSaveXmlsDontRead) is False:
os.mkdir(pathToSaveXmlsDontRead)
copy(pathFile, os.path.join(pathToSaveXmlsDontRead, os.path.basename(pathFile)))
def processAll(self):
for root, _, files in os.walk(self._pathWithXmlFiles):
lenFiles = len(files)
for indexFile, file in enumerate(files):
pathFile = os.path.join(root, file)
if file.lower().endswith(('.xml')) and pathFile.find('dont_read') < 0:
print(f'- Processing {pathFile} - {indexFile+1} of {lenFiles}.')
self._process(pathFile)
self._connectMongo.closeConnection()
def _instancesOfClasses(self):
getNcms = GetNcms(envJson['rules_of_xml'], saveResultProcessInFile=False, returnOnlyValueNcm=True, silent=True)
self._listNcms = getNcms.processAll()
getNcmsName = GetNcmsName(saveResultProcessInFile=False, silent=True)
self._listNcmsName = getNcmsName.processAll()
listCfopTributado = ListCfopTributado()
self._ListCfopTributado = listCfopTributado.getList()
listCfopDevolucao = ListCfopDevolucao()
self._ListCfopDevolucao = listCfopDevolucao.getList()
self._connectMongo = ConnectMongoDB()
self._database = self._connectMongo.getConnetion()
class ConsolidatesResultProductPerMonth():
def __init__(self, inscricaoFederal: str, monthStart: int, yearStart: int,
monthEnd: int, yearEnd: int):
self._inscricaoFederal = inscricaoFederal
self._monthStart = monthStart
self._yearStart = yearStart
self._monthEnd = monthEnd
self._yearEnd = yearEnd
self._connectMongo = ConnectMongoDB()
self._database = self._connectMongo.getConnetion()
def consolidate(self):
listSumObj = []
year = self._yearStart
while year <= self._yearEnd:
months = returnMonthsOfYear(year, self._monthStart,
self._yearStart, self._monthEnd,
self._yearEnd)
# print('\t - ', end='')
for month in months:
monthYearStr = f'{month:0>2}/{year}'
# print(monthYearStr, ' ', end='')
sumObj = {}
sumObj['emitente_inscricao_federal'] = self._inscricaoFederal
sumObj['competence'] = f'01/{monthYearStr}'
sumObj['cfop_nao_tributado'] = 0
sumObj['tributado'] = 0
sumObj['monofasico_varejo'] = 0
sumObj['bebida_fria'] = 0
sumObj['monofasico_atacado'] = 0
sumValueProduct = SumValueProductPerMonth(
self._database, f"notas_{self._inscricaoFederal}", year,
month)
getSums = sumValueProduct.getSum()
for getSum in getSums:
prodNcmRule = getSum['_id']['prod_ncm_rule']
if prodNcmRule == 'CFOPNaoTributado':
sumObj['cfop_nao_tributado'] = getSum['sumTotal']
elif prodNcmRule == '':
sumObj['tributado'] = getSum['sumTotal']
elif prodNcmRule == 'MonofasicoVarejo':
sumObj['monofasico_varejo'] = getSum['sumTotal']
elif prodNcmRule == 'BebidaFria':
sumObj['bebida_fria'] = getSum['sumTotal']
elif prodNcmRule == 'MonofasicoAtacadoSN_CST_4' or prodNcmRule == 'MonofasicoAtacadoSN_CST_6':
sumObj['monofasico_atacado'] += getSum['sumTotal']
listSumObj.append(sumObj.copy())
# print('')
year += 1
return listSumObj
def __init__(self, inscricaoFederal: str, monthStart: int, yearStart: int,
monthEnd: int, yearEnd: int):
self._inscricaoFederal = inscricaoFederal
self._monthStart = monthStart
self._yearStart = yearStart
self._monthEnd = monthEnd
self._yearEnd = yearEnd
self._connectMongo = ConnectMongoDB()
self._database = self._connectMongo.getConnetion()
self._workbook = Workbook(
os.path.join(
foderToSaveResult,
f'detalhado_{self._inscricaoFederal}_{getDateTimeNowInFormatStr()}.xlsx'
))
self._sheet = self._workbook.add_worksheet('Produtos')
self.__setSettingsOfWorkbook()
self.__writeReader()
self._row = 0
class ConsolidatesResultProductPerNcm():
def __init__(self, inscricaoFederal: str):
self._inscricaoFederal = inscricaoFederal
self._connectMongo = ConnectMongoDB()
self._database = self._connectMongo.getConnetion()
self._listSumObj: List[Dict[str, str]] = []
self._sumObj: Dict[str, str] = {}
def __groupByPerNcm(self) -> None:
findThisNcm = False
for index, sumObj in enumerate(self._listSumObj):
if sumObj['prod_ncm'] == self._sumObj['prod_ncm']:
findThisNcm = True
self._listSumObj[index]['cfop_nao_tributado'] += self._sumObj['cfop_nao_tributado']
self._listSumObj[index]['tributado'] += self._sumObj['tributado']
self._listSumObj[index]['monofasico_varejo'] += self._sumObj['monofasico_varejo']
self._listSumObj[index]['bebida_fria'] += self._sumObj['bebida_fria']
self._listSumObj[index]['monofasico_atacado'] += self._sumObj['monofasico_atacado']
if findThisNcm is False:
self._listSumObj.append(self._sumObj.copy())
def consolidate(self):
sumValueProduct = SumValueProductPerNcm(self._database, f"notas_{self._inscricaoFederal}")
getSums = sumValueProduct.getSum()
for getSum in getSums:
prodNcmRule = getSum['_id']['prod_ncm_rule']
self._sumObj['emitente_inscricao_federal'] = self._inscricaoFederal
self._sumObj['prod_ncm'] = getSum['_id']['prod_ncm']
self._sumObj['prod_name_ncm'] = getSum['_id']['prod_name_ncm']
self._sumObj['quantidade_deste_ncm'] = getSum['qtdTotal']
self._sumObj['cfop_nao_tributado'] = 0
self._sumObj['tributado'] = 0
self._sumObj['monofasico_varejo'] = 0
self._sumObj['bebida_fria'] = 0
self._sumObj['monofasico_atacado'] = 0
if prodNcmRule == 'CFOPNaoTributado':
self._sumObj['cfop_nao_tributado'] = getSum['sumTotal']
elif prodNcmRule == '':
self._sumObj['tributado'] = getSum['sumTotal']
elif prodNcmRule == 'MonofasicoVarejo':
self._sumObj['monofasico_varejo'] = getSum['sumTotal']
elif prodNcmRule == 'BebidaFria':
self._sumObj['bebida_fria'] = getSum['sumTotal']
elif prodNcmRule == 'MonofasicoAtacadoSN_CST_4' or prodNcmRule == 'MonofasicoAtacadoSN_CST_6':
self._sumObj['monofasico_atacado'] = getSum['sumTotal']
self.__groupByPerNcm()
self._sumObj.clear()
return self._listSumObj
class SaveProductsInExcel():
def __init__(self, inscricaoFederal: str, monthStart: int, yearStart: int,
monthEnd: int, yearEnd: int):
self._inscricaoFederal = inscricaoFederal
self._monthStart = monthStart
self._yearStart = yearStart
self._monthEnd = monthEnd
self._yearEnd = yearEnd
self._connectMongo = ConnectMongoDB()
self._database = self._connectMongo.getConnetion()
self._workbook = Workbook(
os.path.join(
foderToSaveResult,
f'detalhado_{self._inscricaoFederal}_{getDateTimeNowInFormatStr()}.xlsx'
))
self._sheet = self._workbook.add_worksheet('Produtos')
self.__setSettingsOfWorkbook()
self.__writeReader()
self._row = 0
def __setSettingsOfWorkbook(self):
self._cell_format_header = self._workbook.add_format({
'bold': True,
'font_color': 'black',
'bg_color': 'yellow',
'text_wrap': True
})
self._cell_format_money = self._workbook.add_format(
{'num_format': '##0.00'})
self._cell_format_date = self._workbook.add_format(
{'num_format': 'dd/mm/yyyy'})
def __writeReader(self):
self._sheet.write(0, 0, "CNPJ Emitente", self._cell_format_header)
self._sheet.write(0, 1, "Nome Emitente", self._cell_format_header)
self._sheet.write(0, 2, "Numero NF", self._cell_format_header)
self._sheet.write(0, 3, "Data Emissao", self._cell_format_header)
self._sheet.write(0, 4, "Modelo NF", self._cell_format_header)
self._sheet.write(0, 5, "Serie", self._cell_format_header)
self._sheet.write(0, 6, "CNPJ Destinatario", self._cell_format_header)
self._sheet.write(0, 7, "Nome Destinatario", self._cell_format_header)
self._sheet.write(0, 8, "Chave Nota", self._cell_format_header)
self._sheet.write(0, 9, "Num. Item Produto", self._cell_format_header)
self._sheet.write(0, 10, "Cod. Produto", self._cell_format_header)
self._sheet.write(0, 11, "Nome Produto", self._cell_format_header)
self._sheet.write(0, 12, "NCM", self._cell_format_header)
self._sheet.write(0, 13, "Nome NCM", self._cell_format_header)
self._sheet.write(0, 14, "Regra aplicada pra este NCM",
self._cell_format_header)
self._sheet.write(0, 15, "Unidade", self._cell_format_header)
self._sheet.write(0, 16, "CFOP", self._cell_format_header)
self._sheet.write(0, 17, "Quantidade", self._cell_format_header)
self._sheet.write(0, 18, "Valor Unitario", self._cell_format_header)
self._sheet.write(0, 19, "Valor Produto", self._cell_format_header)
self._sheet.write(0, 20, "Valor Desconto", self._cell_format_header)
self._sheet.write(0, 21, "Valor Frete", self._cell_format_header)
self._sheet.write(0, 22, "Valor Outros", self._cell_format_header)
self._sheet.write(0, 23, "Valor VSeg", self._cell_format_header)
self._sheet.write(0, 24, "Valor Total", self._cell_format_header)
def __writeRows(self, product: Dict[str, str]) -> None:
emitenteInscricaoFederal = treatsFieldAsTextInDictOrArray(
product, ['emitente_inscricao_federal'])
emitenteRazaoSocial = treatsFieldAsTextInDictOrArray(
product, ['emitente_razao_social'])
numeroNF = treatsFieldAsNumberInDictOrArray(
product, ['identificao_nfe_numero_nf'])
dataEmissaoNF = treatsFieldAsDateInDictOrArray(
product, ['identificao_nfe_data_emissao'], formatoData=2)
modeloNF = treatsFieldAsTextInDictOrArray(
product, ['identificao_nfe_modelo_nf'])
serieNF = treatsFieldAsTextInDictOrArray(product,
['identificao_nfe_serie_nf'])
destinatarioInscricaoFederal = treatsFieldAsTextInDictOrArray(
product, ['destinatario_inscricao_federal'])
destinatarioRazaoSocial = treatsFieldAsTextInDictOrArray(
product, ['destinatario_razao_social'])
chaveNota = treatsFieldAsTextInDictOrArray(product, ['chave_nota'])
numeroItemProduto = treatsFieldAsTextInDictOrArray(
product, ['prod_numero_item'])
codigoProduto = treatsFieldAsTextInDictOrArray(product,
['prod_codigo_produto'])
nomeProduto = treatsFieldAsTextInDictOrArray(product,
['prod_nome_produto'])
ncm = treatsFieldAsTextInDictOrArray(product, ['prod_ncm'])
nomeNCM = treatsFieldAsTextInDictOrArray(product, ['prod_name_ncm'])
ruleNCM = treatsFieldAsTextInDictOrArray(product, ['prod_ncm_rule'])
unidade = treatsFieldAsTextInDictOrArray(product, ['prod_unidade'])
cfop = treatsFieldAsNumberInDictOrArray(product, ['prod_cfop'])
quantidade = treatsFieldAsDecimalInDictOrArray(product,
['prod_quantidade'])
valorUnitario = treatsFieldAsDecimalInDictOrArray(
product, ['prod_valor_unitario'])
valorProduto = treatsFieldAsDecimalInDictOrArray(
product, ['prod_valor_produto'])
valorDesconto = treatsFieldAsDecimalInDictOrArray(
product, ['prod_valor_desconto'])
valorFrete = treatsFieldAsDecimalInDictOrArray(product,
['prod_valor_frete'])
valorOutros = treatsFieldAsDecimalInDictOrArray(
product, ['prod_valor_outros'])
valorVSeg = treatsFieldAsDecimalInDictOrArray(product, ['prod_vseg'])
valorTotal = treatsFieldAsDecimalInDictOrArray(product,
['prod_valor_total'])
self._sheet.write(self._row, 0, emitenteInscricaoFederal)
self._sheet.write(self._row, 1, emitenteRazaoSocial)
self._sheet.write(self._row, 2, numeroNF)
self._sheet.write(self._row, 3, dataEmissaoNF, self._cell_format_date)
self._sheet.write(self._row, 4, modeloNF)
self._sheet.write(self._row, 5, serieNF)
self._sheet.write(self._row, 6, destinatarioInscricaoFederal)
self._sheet.write(self._row, 7, destinatarioRazaoSocial)
self._sheet.write(self._row, 8, chaveNota)
self._sheet.write(self._row, 9, numeroItemProduto)
self._sheet.write(self._row, 10, codigoProduto)
self._sheet.write(self._row, 11, nomeProduto)
self._sheet.write(self._row, 12, ncm)
self._sheet.write(self._row, 13, nomeNCM)
self._sheet.write(self._row, 14, ruleNCM)
self._sheet.write(self._row, 15, unidade)
self._sheet.write(self._row, 16, cfop)
self._sheet.write(self._row, 17, quantidade)
self._sheet.write(self._row, 18, valorUnitario,
self._cell_format_money)
self._sheet.write(self._row, 19, valorProduto, self._cell_format_money)
self._sheet.write(self._row, 20, valorDesconto,
self._cell_format_money)
self._sheet.write(self._row, 21, valorFrete, self._cell_format_money)
self._sheet.write(self._row, 22, valorOutros, self._cell_format_money)
self._sheet.write(self._row, 23, valorVSeg, self._cell_format_money)
self._sheet.write(self._row, 24, valorTotal, self._cell_format_money)
def __closeWorkbook(self):
self._workbook.close()
def saveData(self):
year = self._yearStart
while year <= self._yearEnd:
months = returnMonthsOfYear(year, self._monthStart,
self._yearStart, self._monthEnd,
self._yearEnd)
print('\t\t\t- ', end='')
for month in months:
monthYearStr = f'{month:0>2}/{year}'
print(monthYearStr, ' ', end='')
getListProduct = GetListProduct(
self._database, f"notas_{self._inscricaoFederal}", year,
month)
listProducts: List[Dict[str, str]] = getListProduct.getList()
for product in listProducts:
self._row += 1
self.__writeRows(product)
print('')
year += 1
self.__closeWorkbook()
def getSum(self):
try:
sumPerNcmRule: Dict[str, str] = self._collection.aggregate([{
"$group": {
"_id": {
"prod_ncm": "$prod_ncm",
"prod_name_ncm": "$prod_name_ncm",
"prod_ncm_rule": "$prod_ncm_rule"
},
"sumTotal": {
"$sum": "$prod_valor_total"
},
"qtdTotal": {
"$sum": 1
}
}
}])
return sumPerNcmRule
except Exception as e:
print(f"** Error to save data. The message is: {e}")
if __name__ == '__main__':
from dao.connect_mongo import ConnectMongoDB
connectMongo = ConnectMongoDB()
connection = connectMongo.getConnetion()
main = SumValueProductPerNcm(connection, 'notas_14437943000271')
# [print(obj) for obj in main.getSum()]
class GenerateResult():
def __init__(self, inscricaoFederal: str, monthStart: int, yearStart: int,
monthEnd: int, yearEnd: int):
self._inscricaoFederal = inscricaoFederal
self._monthStart = monthStart
self._yearStart = yearStart
self._monthEnd = monthEnd
self._yearEnd = yearEnd
self._connectMongo = ConnectMongoDB()
self._database = self._connectMongo.getConnetion()
self.__instanceDatasToSave()
self.__folderToSaveData()
def __instanceDatasToSave(self):
self._consolidateResultProductPerMonth = ConsolidatesResultProductPerMonth(
self._inscricaoFederal, self._monthStart, self._yearStart,
self._monthEnd, self._yearEnd)
self._consolidateResultProductPerNcm = ConsolidatesResultProductPerNcm(
self._inscricaoFederal)
self._saveProductsInExcel = SaveProductsInExcel(
self._inscricaoFederal, self._monthStart, self._yearStart,
self._monthEnd, self._yearEnd)
def __folderToSaveData(self):
self._folderSaveResultResumeProductPerMonth = os.path.join(
foderToSaveResult,
f'resumo_por_competencia_{self._inscricaoFederal}_{getDateTimeNowInFormatStr()}.xlsx'
)
self._folderSaveResultResumeProductPerNcm = os.path.join(
foderToSaveResult,
f'resumo_por_ncm_{self._inscricaoFederal}_{getDateTimeNowInFormatStr()}.xlsx'
)
def _saveResult(self):
print('\t- Salvando resultados.')
print('\t\t- Consolidado por mês')
self._listSumResultProductPerMonth = self._consolidateResultProductPerMonth.consolidate(
)
dfListSumObjProductPerMonth = pd.DataFrame(
self._listSumResultProductPerMonth)
dfListSumObjProductPerMonth.to_excel(
self._folderSaveResultResumeProductPerMonth,
header=[
'CNPJ', 'Competência', 'CFOP Não Tributado', 'Tributado',
'Monofásico Varejo', 'Bebidas Frias', 'Monofásico Atacado'
],
index=False,
float_format="%.2f")
print('\t\t- Consolidado por NCM')
self._listSumResultProductPerNcm = self._consolidateResultProductPerNcm.consolidate(
)
dfListSumResultProductPerNcm = pd.DataFrame(
self._listSumResultProductPerNcm)
dfListSumResultProductPerNcm.to_excel(
self._folderSaveResultResumeProductPerNcm,
header=[
'CNPJ', 'NCM', 'Nome NCM', 'Quantidade deste Ncm',
'CFOP Não Tributado', 'Tributado', 'Monofásico Varejo',
'Bebidas Frias', 'Monofásico Atacado'
],
index=False,
float_format="%.2f")
print('\t\t- Lista de Produtos')
self._saveProductsInExcel.saveData()
def process(self):
# pass
self._saveResult()