"""Gera o modelo de planilha (.xlsx) para preencher e importar (`importacao.py`). Abas: Instruções, Referência (Plano de Contas e Atividades atuais do sistema) e uma por cadastro, com cabeçalhos, comentários por coluna, listas suspensas, formatos e uma linha de exemplo marcada com `#` (ignorada na importação). """ from io import BytesIO from openpyxl import Workbook from openpyxl.comments import Comment from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter from openpyxl.worksheet.datavalidation import DataValidation from sqlalchemy.orm import Session from custos_precificacao.importacao import LIMITE_BYTES, LIMITE_LINHAS from custos_precificacao.importacao_abas import ( ABAS, CRIAR, DECIMAL, INTEIRO, MES, OPCAO, PCT, SEMPRE, Aba, Coluna, rotulos_da_coluna, ) from custos_precificacao import arvore, impostos, planejamento_schema, vinculos from custos_precificacao.importacao_abas import ROTULOS_OPCAO from custos_precificacao.models import ( AliquotaImposto, Ambiente, Area, Atividade, CenarioPrecificacao, CentroDeCusto, ClassificacaoCredito, Despesa, Direcionador, Funcionario, IcmsDesenvolve, Insumo, Macroprocesso, Maquina, Planejamento, PlanoDeContas, Premissa, Processo, Produto, RoteiroInsumo, VolumeProjetado, ) LINHAS_FORMATADAS = 2000 _PRETO = "111111" _VERDE = "A7F3D0" _CABECALHO_CHAVE = PatternFill("solid", fgColor=_PRETO) _CABECALHO = PatternFill("solid", fgColor="374151") _EXEMPLO = Font(italic=True, color="9CA3AF") _OBRIGATORIEDADE = {SEMPRE: "Obrigatória em toda linha.", CRIAR: "Obrigatória só ao criar um registro novo.", "": "Opcional."} def _formato(coluna: Coluna) -> str | None: return {PCT: "0.00%", DECIMAL: "#,##0.00####", INTEIRO: "0", MES: "@"}.get(coluna.tipo) def _montar_aba(livro: Workbook, aba: Aba, linhas: list[dict] | None = None) -> None: folha = livro.create_sheet(aba.nome) for numero, coluna in enumerate(aba.colunas, start=1): letra = get_column_letter(numero) titulo = coluna.rotulo + (" *" if coluna.obrigatoria == SEMPRE else "") celula = folha.cell(row=1, column=numero, value=titulo) celula.font = Font(bold=True, color="FFFFFF") celula.fill = _CABECALHO_CHAVE if coluna.obrigatoria == SEMPRE else _CABECALHO celula.alignment = Alignment(vertical="center", wrap_text=True) extra = f"\nValores: {', '.join(rotulos_da_coluna(coluna))}." if coluna.tipo == OPCAO else "" celula.comment = Comment(f"{coluna.ajuda}\n{_OBRIGATORIEDADE[coluna.obrigatoria]}{extra}", "Versus") folha.column_dimensions[letra].width = max(16, min(40, len(titulo) + 6)) formato = _formato(coluna) if formato: folha.column_dimensions[letra].number_format = formato for linha in range(2, LINHAS_FORMATADAS + 2): folha.cell(row=linha, column=numero).number_format = formato if coluna.tipo == OPCAO: validacao = DataValidation( type="list", formula1='"' + ",".join(rotulos_da_coluna(coluna)) + '"', allow_blank=True, showErrorMessage=True, errorTitle="Valor inválido", error="Escolha um valor da lista.", ) validacao.add(f"{letra}2:{letra}{LINHAS_FORMATADAS + 1}") folha.add_data_validation(validacao) if linhas is not None: # modelo preenchido: os registros atuais no lugar da linha de exemplo for indice, registro in enumerate(linhas, start=2): valor = _valor_da_celula(coluna, registro.get(coluna.chave)) if valor is not None: folha.cell(row=indice, column=numero, value=valor) continue # linha de exemplo: o 1º campo começa com '#', então a importação a ignora exemplo = coluna.exemplo if exemplo is not None: if numero == 1: exemplo = "#" + str(exemplo).lstrip("#") exemplo_celula = folha.cell(row=2, column=numero, value=exemplo) exemplo_celula.font = _EXEMPLO elif numero == 1: folha.cell(row=2, column=numero, value="#exemplo").font = _EXEMPLO folha.row_dimensions[1].height = 32 folha.freeze_panes = "A2" def _instrucoes(livro: Workbook) -> None: folha = livro.active folha.title = "Instruções" folha.column_dimensions["A"].width = 28 folha.column_dimensions["B"].width = 110 titulo = folha.cell(row=1, column=1, value="Modelo de importação — Versus Custos e Precificação") titulo.font = Font(bold=True, size=14) linhas = [ ("Como usar", "Preencha só as abas que quiser atualizar: aba vazia (só o cabeçalho) ou apagada da planilha é ignorada, e o que ela cadastra não muda. " "Salve como .xlsx e envie na tela Importação. A tela mostra uma prévia (o que será criado/atualizado e os erros por aba e linha) " "e só grava se estiver tudo certo."), ("Tudo ou nada", "Se qualquer linha tiver erro, nada é gravado. Corrija a planilha e valide de novo."), ("Criar e atualizar", "Código novo cria o registro; código que já existe no sistema atualiza os campos preenchidos " "(campo vazio = não mexe). A importação nunca apaga nada — a única troca é a aba Mix de Destinos: as linhas de um Centro de Resultados " "substituem o destino das vendas dele (e precisam somar 100%)."), ("Créditos de imposto", "Nas abas Insumos, Gastos, Despesas e Produtos (revenda), as colunas Crédito ICMS/CBS/IR-CSLL: Sim marca, Não desmarca, vazio não mexe. " "A alíquota própria e a origem da compra (ICMS) são opcionais; as alíquotas padrão ficam nas abas Alíquotas, ICMS Destinos e ICMS Compras."), ("Planejamento", "A planilha trabalha no Planejamento selecionado no cabeçalho do sistema: despesas, alíquotas, volumes e demais dados vão para ele."), ("Referências", "Códigos de outras abas (Centro de Custo, Instalação, Produto, Cenário…) podem existir no sistema ou estar na própria planilha. " "Plano de Contas e Atividades vêm do sistema — veja a aba Referência."), ("Atualizado em", "Mês (AAAA-MM, por exemplo 2026-10) a partir do qual um custo vale. Em registro novo, vazio = 'desde o início'. " "Em registro existente, vazio = mês atual, e o valor antigo fica guardado no histórico."), ("Números e %", "Use ponto ou vírgula (1.234,56 ou 1234.56). Porcentagens: digite 18% (ou formate a célula como %)."), ("Linhas de exemplo", "A segunda linha de cada aba é um exemplo e começa com '#': linhas cujo primeiro campo começa com '#' são ignoradas. " "Pode apagá-la ou escrever por cima."), ("Cabeçalhos", "Não altere os nomes das colunas. As marcadas com * são obrigatórias em toda linha; passe o mouse no cabeçalho para ver a ajuda."), ("Limites", f"Somente .xlsx (sem macros), até {LIMITE_BYTES // 1024 // 1024} MB e {LIMITE_LINHAS:,} linhas de dados.".replace(",", ".")), ("Ordem", "A importação segue a ordem das abas abaixo, então um cadastro pode usar o de uma aba anterior."), ] linha = 3 for rotulo, texto in linhas: folha.cell(row=linha, column=1, value=rotulo).font = Font(bold=True) celula = folha.cell(row=linha, column=2, value=texto) celula.alignment = Alignment(wrap_text=True, vertical="top") linha += 1 linha += 1 folha.cell(row=linha, column=1, value="Abas").font = Font(bold=True, size=12) linha += 1 for aba in ABAS: obrigatorias = ", ".join(c.rotulo for c in aba.colunas if c.obrigatoria == SEMPRE) folha.cell(row=linha, column=1, value=aba.nome).font = Font(bold=True) folha.cell(row=linha, column=2, value=f"{aba.descricao} Obrigatórias: {obrigatorias}.").alignment = Alignment(wrap_text=True, vertical="top") linha += 1 def _referencia(livro: Workbook, session: Session | None) -> None: folha = livro.create_sheet("Referência") folha.sheet_properties.tabColor = _VERDE cabecalhos = [("Plano de Contas — código", 34), ("nome", 40), ("tipo", 12), ("nível", 12), ("", 4), ("Atividade — código", 22), ("nome", 40)] for numero, (texto, largura) in enumerate(cabecalhos, start=1): celula = folha.cell(row=1, column=numero, value=texto or None) celula.font = Font(bold=True) folha.column_dimensions[get_column_letter(numero)].width = largura if session is None: return contas = sorted(session.query(PlanoDeContas), key=lambda c: arvore.chave_de_ordem(c.codigo)) for linha, plano in enumerate(contas, start=2): folha.cell(row=linha, column=1, value=plano.codigo) folha.cell(row=linha, column=2, value=plano.nome) folha.cell(row=linha, column=3, value=plano.tipo) folha.cell(row=linha, column=4, value="analítica" if plano.aceita_lancamento else "sintética (só agrupa)") for linha, atividade in enumerate(session.query(Atividade).order_by(Atividade.codigo), start=2): folha.cell(row=linha, column=6, value=atividade.codigo) folha.cell(row=linha, column=7, value=atividade.nome) folha.freeze_panes = "A2" def _valor_da_celula(coluna: Coluna, valor): if valor is None or valor == "": return None if coluna.tipo == OPCAO: return ROTULOS_OPCAO.get(valor, valor) if coluna.tipo in (DECIMAL, PCT): return float(valor) return valor def _codigo(objeto, campo="codigo"): return getattr(objeto, campo) if objeto is not None else None def _classes_de_credito(session: Session) -> dict[tuple[str, int], dict[str, impostos.ClasseMarcada]]: saida: dict[tuple[str, int], dict[str, impostos.ClasseMarcada]] = {} for c in session.query(ClassificacaoCredito): saida.setdefault((c.origem, c.origem_id), {})[c.classe] = impostos.ClasseMarcada(c.aliquota_pct, c.faixa) return saida def _creditos(classes: dict[tuple[str, int], dict[str, impostos.ClasseMarcada]], origem: str, item_id: int) -> dict: """As colunas de crédito de um item: Sim/Não por classe, a alíquota própria e a origem da compra (ICMS).""" marcadas = classes.get((origem, item_id), {}) saida: dict = {} for classe in impostos.CLASSES: marcada = marcadas.get(classe) saida[f"credito_{classe}"] = "sim" if marcada is not None else "nao" saida[f"aliquota_{classe}"] = marcada.aliquota if marcada is not None else None icms = marcadas.get("icms") saida["origem_icms"] = (icms.faixa or "BA") if icms is not None else None return saida def linhas_do_sistema(session: Session) -> dict[str, list[dict]]: """Os cadastros atuais, no layout das abas do modelo (chave da aba -> registros por chave de coluna). Valores de custo saem como a versão **atual**; 'Atualizado em' fica vazio (mudar um valor vale a partir do mês da importação).""" por_id = lambda modelo: {o.id: o for o in session.query(modelo)} # noqa: E731 classes_gasto = _classes_de_credito(session) centros, ambientes, areas, macros, processos, atividades = ( por_id(CentroDeCusto), por_id(Ambiente), por_id(Area), por_id(Macroprocesso), por_id(Processo), por_id(Atividade), ) funcionarios = por_id(Funcionario) def em_ordem(modelo): return sorted(session.query(modelo), key=lambda o: arvore.chave_de_ordem(o.codigo)) saida: dict[str, list[dict]] = { "planos": [{"codigo": p.codigo, "nome": p.nome, "tipo": p.tipo if p.parent_id is None else None} for p in em_ordem(PlanoDeContas)], "centros": [{"codigo": c.codigo, "nome": c.nome, "produtivo": "sim" if c.produtivo else "nao"} for c in em_ordem(CentroDeCusto)], "ambientes": [ {"codigo": a.codigo, "nome": a.nome, "centro_de_custo": _codigo(centros.get(a.centro_de_custo_id)), "classificacao": a.classificacao, "horas_uteis_mensais": a.horas_uteis_mensais} for a in em_ordem(Ambiente) ], "areas": [{"codigo": a.codigo, "nome": a.nome} for a in sorted(areas.values(), key=lambda o: o.codigo)], "macroprocessos": [ {"codigo": m.codigo, "nome": m.nome, "area": _codigo(areas.get(m.area_id))} for m in sorted(macros.values(), key=lambda o: o.codigo) ], "processos": [ {"codigo": p.codigo, "nome": p.nome, "macroprocesso": _codigo(macros.get(p.macroprocesso_id))} for p in sorted(processos.values(), key=lambda o: o.codigo) ], "atividades": [ {"codigo": a.codigo, "nome": a.nome, "processo": _codigo(processos.get(a.processo_id))} for a in sorted(atividades.values(), key=lambda o: o.codigo) ], "direcionadores": [ {"codigo": d.codigo, "nome": d.nome, "atividade": _codigo(atividades.get(d.atividade_id)), "base": d.base} for d in session.query(Direcionador).order_by(Direcionador.codigo) ], "insumos": [ {"codigo": i.codigo, "nome": i.nome, "tipo": i.tipo, "unidade": i.unidade, "valor_compra_unitario": i.valor_compra_unitario, "frete": i.frete, "ipi_pct": i.ipi_pct, "icms_pct": i.icms_pct, **_creditos(classes_gasto, "insumo", i.id)} for i in session.query(Insumo).order_by(Insumo.codigo) ], "funcionarios": [ {"codigo_fopag": f.codigo_fopag, "nome": f.nome, "cargo": f.cargo, "centro_de_custo": _codigo(centros.get(f.centro_de_custo_id)), "classificacao": f.classificacao, "subordinado_a": _codigo(funcionarios.get(f.subordinado_a_id), "codigo_fopag"), "horas_semanais": f.horas_semanais, "horas_contratadas_mensais": f.horas_contratadas_mensais, "absenteismo_pct": f.absenteismo_pct} for f in session.query(Funcionario).order_by(Funcionario.codigo_fopag) ], "maquinas": [ {"codigo": m.codigo, "nome": m.nome, "ambiente": _codigo(ambientes.get(m.ambiente_id)), "centro_de_custo": _codigo(centros.get(m.centro_de_custo_id)), "classificacao": m.classificacao, "horas_uteis_mensais": m.horas_uteis_mensais} for m in session.query(Maquina).filter_by(categoria="maquina").order_by(Maquina.codigo) ], "outros": [ {"codigo": m.codigo, "nome": m.nome, "ambiente": _codigo(ambientes.get(m.ambiente_id)), "centro_de_custo": _codigo(centros.get(m.centro_de_custo_id)), "classificacao": m.classificacao, "horas_uteis_mensais": m.horas_uteis_mensais} for m in session.query(Maquina).filter_by(categoria="outro").order_by(Maquina.codigo) ], } planos = por_id(PlanoDeContas) gastos: list[dict] = [] for tipo, config in vinculos.GASTO_RECURSOS.items(): recursos = por_id(config.recurso_model) coluna_fk = getattr(config.gasto_model, config.campo_fk) for g in session.query(config.gasto_model).order_by(coluna_fk, config.gasto_model.id): recurso = recursos.get(getattr(g, config.campo_fk)) if tipo == "maquina" and recurso.categoria == "outro": tipo_exibido = "outros" else: tipo_exibido = tipo gastos.append({ "tipo": tipo_exibido, "recurso": getattr(recurso, config.chave_recurso), "plano_de_contas": _codigo(planos.get(g.plano_de_contas_id)), "desembolso": g.desembolso, "retencao": g.retencao, **_creditos(classes_gasto, impostos._ORIGEM_DO_MODELO[config.gasto_model], g.id), }) saida["gastos"] = gastos _completar(session, saida, centros, atividades, classes_gasto) return saida def _completar(session: Session, saida: dict[str, list[dict]], centros: dict, atividades: dict, classes: dict) -> None: """As abas além do cadastro de custeio: itens, estruturas, Centros de Resultados, Planejamento, despesas e impostos.""" planos = {p.id: p for p in session.query(PlanoDeContas)} produtos = {p.id: p for p in session.query(Produto)} direcionadores = {d.id: d for d in session.query(Direcionador)} cenarios = {c.id: c for c in session.query(CenarioPrecificacao)} saida["produtos"] = [ {"codigo": p.codigo, "nome": p.nome, "tipo": p.tipo, "rendimento_kg": p.rendimento_kg, "rendimento_un": p.rendimento_un, "qtd_estimada_producao_mes": p.qtd_estimada_producao_mes, "unidade": p.unidade, "base_quantidade": p.base_quantidade, "perda_pct": p.perda_pct, "custo_compra": p.custo_compra, "tabela_de_precos": "sim" if p.tabela_de_precos else "nao", **(_creditos(classes, "produto_revenda", p.id) if p.tipo == "revenda" else {})} for p in sorted(produtos.values(), key=lambda o: o.codigo) ] roteiros: list[dict] = [] for tipo, cfg in vinculos.ROTEIRO_RECURSOS.items(): recursos = {r.id: r for r in session.query(cfg.recurso_model)} for linha in session.query(cfg.roteiro_model): recurso = recursos[getattr(linha, cfg.campo_fk)] exibido = "outros" if tipo == "maquina" and recurso.categoria == "outro" else tipo roteiros.append({ "produto": produtos[linha.produto_id].codigo, "atividade": _codigo(atividades.get(linha.atividade_id)), "tipo": exibido, "recurso": getattr(recurso, cfg.chave_recurso), "quantidade": linha.tempo_horas, "direcionador": _codigo(direcionadores.get(linha.direcionador_id)), "ordem": linha.ordem, }) insumos = {i.id: i for i in session.query(Insumo)} for linha in session.query(RoteiroInsumo): e_insumo = linha.insumo_id is not None roteiros.append({ "produto": produtos[linha.produto_id].codigo, "atividade": _codigo(atividades.get(linha.atividade_id)), "tipo": "insumo" if e_insumo else "semiacabado", "recurso": insumos[linha.insumo_id].codigo if e_insumo else produtos[linha.insumo_produto_id].codigo, "quantidade": linha.quantidade_kg, "unidade": linha.unidade, "perda_pct": linha.perda_pct, "ordem": linha.ordem, }) saida["roteiros"] = sorted(roteiros, key=lambda r: (r["produto"], r["atividade"] or "", r["tipo"], r["recurso"])) saida["cenarios"] = [ {"codigo": c.codigo, "nome": c.nome, "descricao": c.descricao, "percentual_desconto": c.percentual_desconto} for c in sorted(cenarios.values(), key=lambda o: o.codigo) if not c.a_alocar ] selecionado = session.info.get("planejamento_codigo") if planejamento_schema.e_postgres(session.get_bind()) else None planos_de_trabalho = {p.id: p for p in session.query(Planejamento) if selecionado is None or p.codigo == selecionado} saida["planejamentos"] = [{"codigo": p.codigo, "nome": p.nome} for p in sorted(planos_de_trabalho.values(), key=lambda o: o.codigo)] saida["volumes"] = sorted( ( {"planejamento": planos_de_trabalho[v.planejamento_id].codigo, "cenario": cenarios[v.cenario_id].codigo, "produto": produtos[v.produto_id].codigo, "quantidade": v.quantidade} for v in session.query(VolumeProjetado) if v.planejamento_id in planos_de_trabalho and not cenarios[v.cenario_id].a_alocar ), key=lambda r: (r["planejamento"], r["cenario"], r["produto"]), ) saida["premissas"] = [ {"codigo": p.codigo, "planejamento": planos_de_trabalho[p.planejamento_id].codigo, "tipo": p.tipo, "texto": p.texto} for p in session.query(Premissa).order_by(Premissa.codigo) if p.planejamento_id in planos_de_trabalho ] saida["despesas"] = [ {"codigo": d.codigo, "titulo": d.titulo, "tipo": d.tipo, "plano_de_contas": _codigo(planos.get(d.plano_de_contas_id)), "centro_de_custo": _codigo(centros.get(d.centro_de_custo_id)), "direcionador": _codigo(direcionadores.get(d.direcionador_id)), "valor": d.valor if d.tipo == "fixa" else None, "percentual_receita": d.percentual_receita, "cenario": _codigo(cenarios.get(d.cenario_id)), "descricao": d.descricao, **_creditos(classes, "despesa", d.id)} for d in sorted(session.query(Despesa), key=lambda o: (o.cenario_id or 0, o.codigo)) ] saida["aliquotas"] = [ {"imposto": a.classe, "vigencia": f"{a.vigencia:%Y-%m}", "aliquota": a.aliquota_pct, "nota": a.nota} for a in session.query(AliquotaImposto).filter(AliquotaImposto.classe.in_(("cbs", "ir_csll"))).order_by(AliquotaImposto.classe, AliquotaImposto.vigencia) ] saida["icms_destinos"] = [{"uf": d.uf, "aliquota": d.aliquota_pct, "reducao_base": d.reducao_base_pct} for d in impostos.listar_destinos(session)] saida["icms_compras"] = [{"origem": c.faixa, "aliquota": c.aliquota_pct} for c in impostos.listar_compras(session)] saida["mix"] = [ {"cenario": c.codigo, "uf": uf, "percentual": pct} for c in sorted(cenarios.values(), key=lambda o: o.codigo) for uf, pct in sorted(impostos.obter_mix(session, c.id).items()) ] desenvolve = session.query(IcmsDesenvolve).first() saida["desenvolve"] = [] if desenvolve is None else [{ "ativo": "sim" if desenvolve.ativo else "nao", "pct_dilatado": desenvolve.pct_dilatado, "pct_pago_a_vista": desenvolve.pct_pago_a_vista, "pct_fundo_pobreza": desenvolve.pct_fundo_pobreza, "plano_de_contas": _codigo(planos.get(desenvolve.plano_de_contas_id)), "observacao": desenvolve.observacao, }] def gerar_modelo(session: Session | None = None, preenchido: bool = False) -> bytes: """Bytes do modelo .xlsx. Com `session`, a aba Referência traz o Plano de Contas e as Atividades atuais; com `preenchido`, as abas de cadastro de custeio (Plano de Contas, Centros, Ambientes, Direcionadores, Insumos, Funcionários, Máquinas e Gastos) já vêm com os registros atuais, para editar e subir de volta.""" livro = Workbook() _instrucoes(livro) _referencia(livro, session) atuais = linhas_do_sistema(session) if (preenchido and session is not None) else {} for aba in ABAS: _montar_aba(livro, aba, atuais.get(aba.chave)) saida = BytesIO() livro.save(saida) return saida.getvalue()