jupter_generator.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Links para download das tabelas:\n",
    "# TIPI: https://www.gov.br/receitafederal/pt-br/acesso-a-informacao/legislacao/documentos-e-arquivos/tipi-em-excel.xlsx/view\n",
    "# uTrib: http://www.nfe.fazenda.gov.br/portal/exibirArquivo.aspx?conteudo=qXcw9P1RW80=\n",
    "\n",
    "\n",
    "import pandas as pd\n",
    "from decimal import Decimal\n",
    "import csv\n",
    "\n",
    "file_tipi_gov = \"tipi_gov.xlsx\"\n",
    "wb_tipi_gov = pd.ExcelFile(file_tipi_gov)\n",
    "df_tipi = wb_tipi_gov.parse(header=7)\n",
    "df_tipi.columns = ['ncm', 'exception', 'desc', 'ipi']\n",
    "\n",
    "df_tipi = df_tipi.astype({'exception':'str'})\n",
    "df_tipi[\"exception\"] = df_tipi[\"exception\"].str.zfill(2)\n",
    "\n",
    "df_old = pd.read_csv('old_l10n_br_fiscal.ncm.csv')\n",
    "\n",
    "for i, row in df_tipi.iterrows():\n",
    "    elements = str(row[\"ncm\"]).split(\".\")\n",
    "    if len(elements[0]) % 2 != 0:\n",
    "        elements[0] = \"0\" + elements[0]\n",
    "    df_tipi.at[i,\"ncm\"] = \"\".join(elements)\n",
    "\n",
    "    if row[\"exception\"] == \"nan\":\n",
    "        df_tipi.at[i,\"exception\"] = \"\"\n",
    "\n",
    "for i, row in df_tipi.iterrows():\n",
    "    ncm = row[\"ncm\"]\n",
    "    if len(ncm) != 8:\n",
    "        continue\n",
    "\n",
    "    concat_desc = \"\"\n",
    "    parts = [ncm[0:4],ncm[0:5],ncm[0:6],ncm[0:7],ncm[0:8]]\n",
    "    for part in parts:\n",
    "        result = df_tipi.query(f\"ncm =='{part}'\")\n",
    "        if not result.empty:\n",
    "            part_name = result[\"desc\"].iloc[0]\n",
    "            concat_desc = f\"{concat_desc} {part_name}\"\n",
    "    df_tipi.at[i, \"concat_desc\"] = concat_desc[1:-1]\n",
    "\n",
    "df_tipi = df_tipi.loc[df_tipi['ncm'].str.len() == 8]\n",
    "\n",
    "df = pd.DataFrame()\n",
    "\n",
    "file_tipi_gov = \"tabela_nfe_um.xlsx\"\n",
    "wb_tipi_gov = pd.ExcelFile(file_tipi_gov)\n",
    "df_um = wb_tipi_gov.parse()\n",
    "df_um = df_um.iloc[:, [0,3]]\n",
    "df_um.columns = ['ncm', 'um']\n",
    "df_um = df_um.astype({'ncm':'str'})\n",
    "df_um[\"ncm\"] = df_um[\"ncm\"].str.zfill(8)\n",
    "\n",
    "rep = {\n",
    "    \"UN\": \"uom.product_uom_unit\",\n",
    "    \"KG\": \"uom.product_uom_kgm\",\n",
    "    \"DUZIA\": \"uom.product_uom_dozen\",\n",
    "    \"G\": \"uom.product_uom_gram\",\n",
    "    \"TON\": \"uom.product_uom_ton\",\n",
    "    \"LT\": \"uom.product_uom_litre\",\n",
    "    \"1000UN\": \"UOM_UN1000\",\n",
    "    \"M3\": \"UOM_M3\",\n",
    "    \"MWHORA\": \"UOM_MWH\",\n",
    "    \"QUILAT\": \"UOM_QUILATE\",\n",
    "    \"M2\": \"UOM_M2\",\n",
    "    \"METRO\": \"uom.product_uom_meter\",\n",
    "    \"PARES\": \"UOM_PARES\",\n",
    "}\n",
    "\n",
    "df_um[\"um\"] = df_um[\"um\"].replace(rep)\n",
    "\n",
    "for i, row in df_tipi.iterrows():\n",
    "    odoo_id = f\"ncm_{row['ncm']}\"\n",
    "    if row['exception'] != \"\":\n",
    "        ncm_exception = row['exception'].replace(\" \", \"_\").lower()\n",
    "        odoo_id = f\"{odoo_id}_{ncm_exception}\"\n",
    "    df.at[i, \"id\"] = odoo_id\n",
    "    df.at[i, \"code\"] = f\"{row['ncm'][:4]}.{row['ncm'][4:6]}.{row['ncm'][6:8]}\"\n",
    "    df.at[i, \"exception\"] = row['exception']\n",
    "    df.at[i, \"name\"] = row['concat_desc']\n",
    "\n",
    "    ipi_value = row['ipi']\n",
    "    if isinstance(ipi_value, float):\n",
    "        ipi_value = Decimal(round(ipi_value, 2)).normalize()\n",
    "        ipi_value = float(ipi_value)\n",
    "    ipi = str(ipi_value).lower().replace(\".\",\"_\")\n",
    "    df.at[i, \"tax_ipi_id:id\"] = f\"tax_ipi_{ipi}\"  \n",
    "\n",
    "    result = df_old.query(f\"id =='{odoo_id}'\")\n",
    "    if not result.empty:\n",
    "        ii = result[\"tax_ii_id:id\"].iloc[0]\n",
    "        df.at[i, \"tax_ii_id:id\"] = ii\n",
    "    \n",
    "    ncm = row['ncm']\n",
    "    result = df_um.query(f\"ncm =='{ncm}'\")\n",
    "    if not result.empty:\n",
    "        um = result[\"um\"].iloc[0]\n",
    "        df.at[i, \"uoe_id:id\"] = um\n",
    "\n",
    "df[\"active\"] = True\n",
    "\n",
    "df.loc[-1] = \"ncm_00000000\",\"0000.00.00\",\"\",\"Sem NCM\",\"\",\"\",\"\",\"True\"\n",
    "df.index = df.index + 1  # shifting index\n",
    "df.sort_index(inplace=True) \n",
    "\n",
    "df2 = df_old.loc[~df_old['id'].isin(df['id'])].copy()\n",
    "df2[\"active\"] = False\n",
    "\n",
    "df_final = pd.concat([df, df2], join=\"inner\")\n",
    "\n",
    "# A NCM 94032000 está vindo com valor do ipi sem nenhuma informação na tabela TIPI, então estou forçando a mesma informação das NCMS do mesmo grupo.\n",
    "index = df_final.loc[df_final['id'] == 'ncm_94032000'].index\n",
    "df_final.loc[index, 'tax_ipi_id:id'] = 'tax_ipi_3_25'\n",
    " \n",
    "\n",
    "df_final.to_csv('l10n_br_fiscal.ncm.csv', quoting=csv.QUOTE_ALL,index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import csv\n",
    "\n",
    "# Carregue os dois arquivos CSV nas respectivas DataFrames\n",
    "ncm_df = pd.read_csv('l10n_br_fiscal.ncm.csv')\n",
    "tax_df = pd.read_csv('old_l10n_br_fiscal.tax.csv')\n",
    "\n",
    "# Extraia os IDs de imposto IPI da tabela NCM\n",
    "ipi_ids_ncm = ncm_df['tax_ipi_id:id'].unique()\n",
    "\n",
    "# Verifique se os IDs de imposto IPI existem na tabela de impostos\n",
    "for ipi_id in ipi_ids_ncm:\n",
    "    if isinstance(ipi_id, str) and ipi_id not in tax_df['id'].values:\n",
    "        # Se não existir, adicione-o à tabela de impostos\n",
    "        ipi_percentage = ipi_id.split(\"_\")[2:]\n",
    "        ipi_percentage = \".\".join(ipi_percentage).replace('_', '')\n",
    "\n",
    "        new_row = pd.DataFrame({\n",
    "            'id': [ipi_id],\n",
    "            'name': [f'IPI {ipi_percentage}%'],\n",
    "            'tax_base_type': ['percent'],\n",
    "            'percent_amount': [float(ipi_percentage)],\n",
    "            'percent_reduction': [0.00],\n",
    "            'tax_group_id:id': ['tax_group_ipi'],\n",
    "            'cst_in_id:id': ['cst_ipi_00'],\n",
    "            'cst_out_id:id': ['cst_ipi_50'],\n",
    "            'value_amount': [''],\n",
    "            'currency_id:id': [''],\n",
    "            'uot_id:id': [''],\n",
    "            'percent_debit_credit': [''],\n",
    "            'icms_base_type': ['0'],\n",
    "            'icmsst_base_type': ['4'],\n",
    "            'icmsst_mva_percent': [''],\n",
    "            'icmsst_value': ['']\n",
    "        })\n",
    "        tax_df = pd.concat([tax_df, new_row], ignore_index=True)\n",
    "        \n",
    "tax_df = tax_df.drop(tax_df.loc[tax_df['id'] == 'tax_ipi_nan'].index)  \n",
    "tax_df[['percent_amount', 'percent_reduction']] = tax_df[['percent_amount', 'percent_reduction']].applymap(lambda x: f'{x:.2f}')\n",
    "tax_df = tax_df.sort_values('id')\n",
    "tax_df.to_csv('l10n_br_fiscal.tax.csv', index=False, quoting=csv.QUOTE_ALL)\n",
    "with open('l10n_br_fiscal.tax.csv', 'r') as file:\n",
    "    filedata = file.read()\n",
    "\n",
    "# Substituir todas as ocorrências de aspas vazias por vazios\n",
    "# É preciso atenção caso tenha algum caso que seja necessário duas aspas juntas '\"\"'\n",
    "filedata = filedata.replace('\"\"', '')\n",
    "\n",
    "with open('l10n_br_fiscal.tax.csv', 'w') as file:\n",
    "    file.write(filedata)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "916dbcbb3f70747c44a77c7bcd40155683ae19c65e1c03b4aa3499c5328201f1"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}