Jpeg_processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from PIL import Image \n",
    "import pytesseract \n",
    "import sys \n",
    "from pdf2image import convert_from_path \n",
    "import os \n",
    "import cv2\n",
    "import numpy as np\n",
    "import re  \n",
    "import xlsxwriter \n",
    "import math\n",
    "import pandas as pd\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "black = (0,0,0)\n",
    "white = (255,255,255)\n",
    "threshold = (160,160,160)\n",
    "config = ('-l eng --oem 3 --psm 6 ')\n",
    "image = cv2.imread('invoice_image.jpg')\n",
    "mask = np.zeros(image.shape, dtype=np.uint8)\n",
    "gray = cv2.cvtColor(image, cv2.COLOR_BGR2GRAY)\n",
    "thresh = cv2.threshold(gray, 0, 255, cv2.THRESH_BINARY_INV + cv2.THRESH_OTSU)[1]\n",
    "cnts = cv2.findContours(thresh, cv2.RETR_TREE, cv2.CHAIN_APPROX_SIMPLE)\n",
    "cnts = cnts[0] if len(cnts) == 2 else cnts[1]\n",
    "for c in cnts:\n",
    "    area = cv2.contourArea(c)\n",
    "    if area < 10000:\n",
    "        cv2.drawContours(mask, [c], -1, (255,255,255), -1)\n",
    "mask = cv2.cvtColor(mask, cv2.COLOR_BGR2GRAY)\n",
    "result = cv2.bitwise_and(image,image,mask=mask)\n",
    "result[mask==0] = (255,255,255)\n",
    "width = int(result.shape[1] * 1.5)\n",
    "height = int(result.shape[0] * 1.5)\n",
    "dsize = (width, height)\n",
    "output = cv2.resize(result, dsize)\n",
    "cv2.imwrite(\"ne_image.jpg\", output)\n",
    "\n",
    "outfile =  'out_text.txt'\n",
    "f = open(outfile, \"a\") \n",
    "filename = \"ne_image.jpg\"\n",
    "text = str(((pytesseract.image_to_string(Image.open(filename),config=config)))) \n",
    "text = text.replace('-\\n', '')     \n",
    "f.write(text)\n",
    "os.remove(\"ne_image.jpg\")\n",
    "os.remove(\"invoice_image.jpg\")\n",
    "\n",
    "f.close() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#The code is specifically for India and can be extended to other countries as well . This is because Seller's state can be extracted from Gstn no then he list will be enormous .\n",
    "final_file=str(datetime.datetime.now().strftime(\"%d-%m-%Y_%I-%M-%S_%p\"))+'.'+'xlsx'\n",
    "workbook = xlsxwriter.Workbook(final_file) \n",
    "outfile='out3_text.txt'\n",
    "worksheet = workbook.add_worksheet() \n",
    "filename=outfile\n",
    "list={'01':'JAMMU AND KASHMIR','02':'HIMACHAL PRADESH','03':'PUNJAB','04':'CHANDIGARH','05':'UTTARAKHAND','06':'HARYANA','07':'DELHI','08':'RAJASTHAN','09':'UTTAR PRADESH','10':'BIHAR',\n",
    "     '11':'SIKKIM','12':'ARUNACHAL PRADESH','13':'NAGALAND','14':'MANIPUR','15':'MIZORAM','16':'TRIPURA','17':'MEGHALAYA','1/':'MEGHALAYA','18':'ASSAM','19':'WEST BENGAL','20':'JHARKHAND',\n",
    "     '21':'ODISHA','22':'CHATTISGARH','23':'MADHYA PRADESH','24':'GUJARAT','25':'DAMAN AND DIU','26':'DADRA AND NAGAR HAVELI','27':'MAHARASHTRA','28':'ANDHRA PRADESH (old)','29':'KARNATAKA',\n",
    "     '30':'GOA','31':'LAKSHWADEEP','32':'KERALA','33':'TAMIL NADU','34':'PUDUCHERRY','35':'ANDAMAN AND NICOBAR ISLANDS','36':'TELANGANA','37':'ANDHRA PRADESH (NEW)'}       \n",
    "\n",
    "merge_format = workbook.add_format({'bold': 0,'border': 1,'align': 'center','valign': 'bottom','fg_color': '#BDD7EE'})\n",
    "merge_format_1 = workbook.add_format({'bold': 0,'border': 1,'align': 'center','valign': 'vcenter'})\n",
    "merge_format_2 = workbook.add_format({'bold': 0,'border': 1,'align': 'center','valign': 'bottom','fg_color': '#FF9E01'})\n",
    "merge_format_3 = workbook.add_format({'bold': .5,'border': 1,'align': 'left','valign': 'bottom','fg_color': '#BDD7EE'})\n",
    "merge_format_4 = workbook.add_format({'bold': .5,'border': 1,'align': 'center','valign': 'bottom','fg_color': '#BDD7EE'})\n",
    "merge_format_5 = workbook.add_format({'bold': .5,'border': 1,'align': 'center','valign': 'vcenter'})\n",
    "\n",
    "worksheet.set_column( 'A:Z',14)\n",
    "worksheet.set_default_row(16)\n",
    "worksheet.merge_range('A1:S2', 'GST Invoice', merge_format_4)\n",
    "worksheet.merge_range('A3:D3', 'Seller State', merge_format)\n",
    "worksheet.merge_range('A4:D4', 'Seller ID', merge_format)\n",
    "worksheet.merge_range('A5:D5', 'Seller Name', merge_format)\n",
    "worksheet.merge_range('A6:D10', 'Seller Address', merge_format)\n",
    "worksheet.merge_range('A11:D11', 'Seller GSTIN Number', merge_format)\n",
    "worksheet.merge_range('A12:D12', 'Country of Origin', merge_format)\n",
    "worksheet.merge_range('A13:D13', 'Currency', merge_format)\n",
    "worksheet.merge_range('A14:D14', 'Description', merge_format)\n",
    "worksheet.merge_range('A15:D16', '', merge_format)\n",
    "worksheet.merge_range('J3:L3', 'Invoice Number', merge_format)\n",
    "worksheet.merge_range('J4:L4', 'Invoice Date', merge_format)\n",
    "worksheet.merge_range('J5:L5', 'Due Date', merge_format)\n",
    "worksheet.merge_range('J6:L6', 'Total Invoice amount entered by WH operator ', merge_format)\n",
    "worksheet.merge_range('J7:L7', 'Total Invoice Quantity entered by WH operator ', merge_format)\n",
    "worksheet.merge_range('J8:L8', 'Total TCS Collected', merge_format)\n",
    "worksheet.merge_range('J9:L9', 'Round Off Charges', merge_format)\n",
    "worksheet.merge_range('J10:L10', 'PO Number', merge_format_2)\n",
    "worksheet.merge_range('J11:L11', 'Invoice Items Total Amount', merge_format)\n",
    "worksheet.merge_range('J12:L12', 'Invoice Items Total Quantity', merge_format)\n",
    "worksheet.merge_range('J13:L13', 'Buyer GSTIN Number', merge_format_2)\n",
    "worksheet.merge_range('J14:L16', 'Ship to Address', merge_format_4)\n",
    "worksheet.write('A17', 'S.No',merge_format_3)\n",
    "worksheet.write('B17','Product ID',merge_format_3)\n",
    "worksheet.write('C17', 'SKU',merge_format_3)\n",
    "worksheet.write('D17', 'HSN',merge_format_3)\n",
    "worksheet.write('E17','Title',merge_format_3)\n",
    "worksheet.write('F17', 'Quantity',merge_format_3)\n",
    "worksheet.write('G17', 'Unit Price',merge_format_3)\n",
    "worksheet.write('H17','Excise Duty',merge_format_3)\n",
    "worksheet.write('I17', 'Discount Percent',merge_format_3)\n",
    "worksheet.write('J17','SGST Percent',merge_format_3)\n",
    "worksheet.write('K17','CGST Percent',merge_format_3)\n",
    "worksheet.write('L17', 'IGST Percent',merge_format_3)\n",
    "worksheet.write('M17', 'Cess Percent',merge_format_3)\n",
    "worksheet.write('N17','TCS Percent',merge_format_3)\n",
    "worksheet.write('O17','Total Amount',merge_format_3)\n",
    "worksheet.write('P17', 'APP %',merge_format_3)\n",
    "worksheet.write('J2','GST Invoice',merge_format_3)\n",
    "\n",
    "#Searching for line of @ for mail id . Then extracting mail id by specifying format of email id .\n",
    "j=0\n",
    "line_num=0\n",
    "specific_line=''\n",
    "file = open(filename, \"r\")\n",
    "search_phrase = '@'\n",
    "for line in file.readlines():\n",
    "    line_num += 1\n",
    "    if line.find(search_phrase) >= 0:\n",
    "        j=line_num\n",
    "        break\n",
    "file.close()\n",
    "file = open(filename)\n",
    "all_lines_variable = file.readlines()\n",
    "specific_line=all_lines_variable[j-1]\n",
    "email = re.findall('\\S+@\\S+', specific_line) \n",
    "is_email=bool(email)\n",
    "if is_email==True:\n",
    "     worksheet.merge_range('E4:I4',email[0],merge_format_1 )\n",
    "file.close()\n",
    "\n",
    "#GST number is a 15 digit number with first 2 digit as state code next 10 digit as PAN no .Second most GST number is Buyer's GST number. \n",
    "#Most of the GST number in given invoice is wrong so format is somewhat changed . \n",
    "#Second most GST number is buyer's GST number\n",
    "file = open(filename, \"r\")\n",
    "l=0\n",
    "inter_state=0\n",
    "content = file.read()\n",
    "#increaing accuracy\n",
    "pattern = \"[0-9O/]{2}[A-Z]{5,6}[0-9O/]{3,4}[A-Z]{1}[A-Z0-9/]{3,4}[\\s]\"\n",
    "GST = re.findall(pattern, content)\n",
    "for i in range(0,len(GST)):\n",
    "    GST[i]=re.sub('/','7',GST[i])\n",
    "for i in range(0,len(GST)):\n",
    "    GST[i]=re.sub('O','0',GST[i])\n",
    "if len(GST)>0:\n",
    "    seller_gst=GST[0]\n",
    "    worksheet.merge_range('E11:I11',seller_gst,merge_format_1 )\n",
    "    #Checking first 2 digits of GSTN No for Seller's state                    \n",
    "    if seller_gst[:2] in list.keys():\n",
    "        worksheet.merge_range('E3:I3',list[seller_gst[:2]],merge_format_1 )\n",
    "\n",
    "if len(GST)>1:\n",
    "    buyer_gst=GST[1]\n",
    "    worksheet.merge_range('M13:Q13', buyer_gst,merge_format_1)\n",
    "    if buyer_gst[:2]==seller_gst[:2]:\n",
    "         inter_state=inter_state+1\n",
    "file.close()\n",
    "\n",
    "#Seller's country of origin\n",
    "worksheet.merge_range('E12:I12', 'India',merge_format_1)\n",
    "\n",
    "#Finding Seller's description(PAN) . PAN no is a 10 digit number with combination of 5 letters , 4 digits , 1 letter \n",
    "file = open(filename, \"r\")\n",
    "content = file.read()\n",
    "pattern = \"[\\s][A-Z]{5}[0-9]{4}[A-Z]{1}[\\s]\"\n",
    "pan = re.findall(pattern, content)\n",
    "is_pan=bool(pan)\n",
    "if is_pan == True:\n",
    "    worksheet.merge_range('E14:I14',pan[0],merge_format_1 )\n",
    "file.close()\n",
    "\n",
    "#The occurance of first India signifies address of seller . The line above it signifies Name of seller .\n",
    "f = open(filename,'r')\n",
    "line_num = 0\n",
    "j=0\n",
    "search_phrase = \"India\"\n",
    "for line in f.readlines():\n",
    "    line_num += 1\n",
    "    if line.find(search_phrase) >= 0:\n",
    "        j=line_num\n",
    "        break\n",
    "file.close()\n",
    "s=''\n",
    "f = open(filename)\n",
    "all_lines_variable = f.readlines()\n",
    "s=s+all_lines_variable[j-1]\n",
    "s=s.split('India')[0]\n",
    "s=s+'India'\n",
    "worksheet.merge_range('E6:I10',s,merge_format_1)\n",
    "s=''\n",
    "for x in range(0, 4):\n",
    "    s=s+all_lines_variable[j-2][x]\n",
    "if s=='Page':\n",
    "    s=all_lines_variable[j-3] \n",
    "else:\n",
    "    s=all_lines_variable[j-2]\n",
    "file.close()\n",
    "pattern = \"\\d{1,2}[/.]\\d{1,2}[/.]\\d{4}\"\n",
    "file = open(filename)\n",
    "content = file.read()\n",
    "dates = re.findall(pattern,content )\n",
    "is_date=bool(dates)\n",
    "if is_date==True:\n",
    "    s=s.replace(dates[0],'')\n",
    "    worksheet.merge_range('E5:I5', s,merge_format_1)\n",
    "else:\n",
    "    worksheet.merge_range('E5:I5', s ,merge_format_1)\n",
    "file.close()\n",
    "\n",
    "#Line of last occurance of India and above line above it \n",
    "f = open(filename,'r')\n",
    "line_num = 0\n",
    "j=0\n",
    "i=0\n",
    "k=0\n",
    "search_phrase = \"India\"\n",
    "for line in reversed(f.readlines()):\n",
    "    line_num += 1\n",
    "    if line.find(search_phrase) >= 0:\n",
    "        j=line_num\n",
    "        break\n",
    "file.close()\n",
    "s=''\n",
    "with open(filename) as f:\n",
    "    for i, l in enumerate(f):\n",
    "        pass\n",
    "k=(i+1-j)\n",
    "f.close()\n",
    "f = open(filename)\n",
    "all_lines_variable = f.readlines()\n",
    "if all_lines_variable[k-1] == ' ':\n",
    "    s=s+all_lines_variable[k-1]\n",
    "else:\n",
    "    s=s+all_lines_variable[k-2]\n",
    "s=s+all_lines_variable[k]\n",
    "worksheet.merge_range('M14:Q16' , s ,merge_format_1 )\n",
    "f.close()\n",
    "    \n",
    "#As the code is for India the currency will be INR only\n",
    "worksheet.merge_range('E13:I13','INR',merge_format_1 )\n",
    "\n",
    "#Finding the line of \"Inv No\" then next number after it is invoice number\n",
    "file = open(filename,'r')\n",
    "line_num = 0\n",
    "j=0\n",
    "search_phrase1 = \"Inv\"\n",
    "search_phrase2=\"No\"\n",
    "for line in file.readlines():\n",
    "    line_num += 1\n",
    "    if line.find(search_phrase1) >= 0:\n",
    "        if line.find(search_phrase2) >= 0:\n",
    "            j=line_num\n",
    "            break\n",
    "file.close()\n",
    "file = open(filename)\n",
    "all_lines_variable = file.readlines()\n",
    "if j>0:\n",
    "    s=all_lines_variable[j-1]\n",
    "    s=s.split('Inv')\n",
    "    pattern = \"[\\s][0-9A-Z]{0,10}[/-]{0,1}[0-9A-Z]{0,10}[/-]{0,1}[0-9A-Z]{0,10}[/-]{0,1}[0-9]{1,10}[\\s]\"\n",
    "    number = re.findall(pattern, s[1])\n",
    "    is_number=bool(number)\n",
    "    if is_number==True:\n",
    "        worksheet.merge_range('M3:Q3',number[0],merge_format_1)\n",
    "    else:\n",
    "        s=all_lines_variable[j]\n",
    "        pattern = \"[\\s][0-9A-Z]{0,10}[0-9]{1,10}[\\s]\"\n",
    "        number = re.findall(pattern, s)\n",
    "        is_number=bool(number)\n",
    "        if is_number==True:\n",
    "            worksheet.merge_range('M3:Q3',number[0],merge_format_1)\n",
    "f.close()\n",
    "\n",
    "#Finding the line number of Inv Date. The immediate next date format after the word \"Inv Date\" will give the date \n",
    "file = open(filename,'r')\n",
    "line_num = 0\n",
    "j=0\n",
    "specific_line=''\n",
    "search_phrase1='Inv'\n",
    "search_phrase2=\"Date\"\n",
    "for line in file.readlines():\n",
    "    line_num += 1\n",
    "    if line.find(search_phrase1) >= 0:\n",
    "        if line.find(search_phrase2) >= 0:\n",
    "            j=line_num\n",
    "            break\n",
    "file.close()\n",
    "file = open(filename)\n",
    "all_lines_variable = file.readlines()\n",
    "if j>0:\n",
    "    specific_line=all_lines_variable[j-1]\n",
    "    specific_line=specific_line.split('Inv')\n",
    "    pattern = \"\\d{1,2}[/.-]\\d{1,2}[/.-]\\d{4}\"\n",
    "    invoice_dates = re.findall(pattern, specific_line[1])\n",
    "    is_invoice_dates=bool(invoice_dates)\n",
    "    if is_invoice_dates==True:\n",
    "        worksheet.merge_range('M4:Q4', invoice_dates[0],merge_format_1)\n",
    "    else:\n",
    "        specific_line=all_lines_variable[j]\n",
    "        pattern = \"\\d{1,2}[/.-]\\d{1,2}[/.-]\\d{4}\"\n",
    "        invoice_dates = re.findall(pattern, specific_line)\n",
    "        if bool(invoice_dates)==True:\n",
    "             worksheet.merge_range('M4:Q4', invoice_dates[0],merge_format_1)\n",
    "file.close()\n",
    "\n",
    "#Searching PO number . \n",
    "file = open(filename, \"r\")\n",
    "l=0\n",
    "content = file.read()\n",
    "pattern = \"[\\s]PO[\\d+]{1,100}[\\s]\"; \n",
    "PO = re.findall(pattern, content)\n",
    "l= len(PO)\n",
    "if len(PO)>0:\n",
    "    worksheet.merge_range('M10:Q10', PO[0],merge_format_1)\n",
    "file.close()\n",
    "\n",
    "#Finding line number of due date . The immediate next date after it will be due date \n",
    "file = open(filename,'r')\n",
    "line_num = 0\n",
    "j=0\n",
    "specific_line=''\n",
    "search_phrase1 = \"Due\"\n",
    "search_phrase2=\"Date\"\n",
    "for line in file.readlines():\n",
    "    line_num += 1\n",
    "    if line.find(search_phrase1) >= 0:\n",
    "        if line.find(search_phrase2) >= 0:\n",
    "            j=line_num\n",
    "            break\n",
    "file.close()\n",
    "file = open(filename)\n",
    "if j>0:\n",
    "    all_lines_variable = file.readlines()\n",
    "    specific_line=all_lines_variable[j-1]\n",
    "    specific_line=specific_line.split('Due')\n",
    "    pattern = \"\\d{1,2}[/.]\\d{1,2}[/.]\\d{4}\"\n",
    "    dates = re.findall(pattern, specific_line[1])\n",
    "    is_dates=bool(dates)\n",
    "    if is_dates==True:\n",
    "        worksheet.merge_range('M5:Q5', dates[0],merge_format_1)\n",
    "    else:\n",
    "        specific_line=all_lines_variable[j]\n",
    "        pattern = \"\\d{1,2}[/.]\\d{1,2}[/.]\\d{4}\"\n",
    "        dates = re.findall(pattern, specific_line)\n",
    "        is_dates=bool(dates)\n",
    "        if is_dates==True:\n",
    "            worksheet.merge_range('M5:Q5', dates[0],merge_format_1)\n",
    "file.close()\n",
    "\n",
    "file = open(filename,'r')\n",
    "line_num = 0\n",
    "j=0\n",
    "specific_line=''\n",
    "search_phrase1 = \"Days\"\n",
    "for line in file.readlines():\n",
    "        words = line.lower().split()\n",
    "        if 'days' in words[1:]:\n",
    "            due_days= words[words.index('days')-1]\n",
    "            j=j+1\n",
    "            break\n",
    "if j>0:\n",
    "    if is_invoice_dates==True:\n",
    "        startdate=invoice_dates[0]\n",
    "        startdate=startdate.replace(\".\", \"/\")\n",
    "        startdate=startdate.replace(\"-\", \"/\")\n",
    "        startdate=datetime.datetime.strptime(startdate, \"%d/%m/%Y\").strftime(\"%m-%d-%Y\")\n",
    "        due_days=int(due_days)\n",
    "        enddate = pd.to_datetime(startdate) + pd.DateOffset(days=due_days)\n",
    "        enddate=enddate.strftime('%d-%m-%Y')\n",
    "        worksheet.merge_range('M5:Q5', enddate,merge_format_1)\n",
    "file.close()\n",
    "\n",
    "# Finding the line where first price is written . Then finding the line where line is starting from total . Lines between them starting with a numbr give S_No\n",
    "file = open(filename,'r')\n",
    "line_num = 0\n",
    "j=0\n",
    "for line in file.readlines():\n",
    "    line_num += 1\n",
    "    pattern = \"[\\s][\\d*]{0,5}[,]{0,1}[\\d*]{0,5}[,]{0,1}[\\d*]{0,5}[,]{0,1}[\\d*]{0,5}[,]{0,1}[\\d+]{1,9}[.][0-9]{2,3}[\\s]\";\n",
    "    price=re.findall(pattern , line)\n",
    "    is_price=bool(price)\n",
    "    if is_price==True:\n",
    "        j=line_num\n",
    "        break\n",
    "file.close()\n",
    "file = open(filename,'r')\n",
    "line_num = 0\n",
    "k=0\n",
    "for line in file.readlines():\n",
    "    line_num+=1\n",
    "    is_empty=line.isspace()\n",
    "    if is_empty!=True:\n",
    "        first_word = line.split()[0]\n",
    "        if len(line.split())>1:\n",
    "            second_word=line.split()[1]\n",
    "            if second_word=='Total':\n",
    "                k=line_num\n",
    "                break\n",
    "        if first_word=='Total' :\n",
    "            k=line_num\n",
    "            break\n",
    "file.close()\n",
    "\n",
    "l=0\n",
    "list5=[]\n",
    "S_No=[]\n",
    "file = open(filename,'r')\n",
    "line=file.readlines()\n",
    "for i in range(j-1,k):\n",
    "    pattern = \"^[\\d]{1,15}[\\s]\";\n",
    "    S_No=re.findall(pattern , line[i])\n",
    "    is_S_No=bool(S_No)\n",
    "    if is_S_No==True:\n",
    "        list5.append(i)\n",
    "        l=l+1\n",
    "file.close()\n",
    "z=0\n",
    "A=1\n",
    "S_No=''\n",
    "for i in range(0,l):\n",
    "    worksheet.write('A'+str(i+18) , A,merge_format_1 )\n",
    "    A=A+1\n",
    "worksheet.merge_range('B'+str(i+18+1)+':'+'E'+str(i+18+1),'Line Total',merge_format_5)\n",
    "worksheet.write('A'+str(i+18+1),'',merge_format_1)\n",
    "\n",
    "#Finding the string in the line and next lines till line is strating from a number\n",
    "qty=0\n",
    "C=0\n",
    "Net_Amount=0.0\n",
    "Net_Unit_price=0.0\n",
    "Net_WH_Amount=0.0\n",
    "Total_TCS=0.0\n",
    "for b in list5:\n",
    "        pattern = \"[a-zA-Z]{1,30}[\\s][a-zA-Z]{0,30}[\\s]{0,1}[a-zA-Z]{0,30}[\\s]{0,1}[a-zA-Z]{0,30}[\\s]{0,1}[a-zA-Z]{0,30}[\\s]{0,1}[a-zA-Z]{0,30}[\\s]{0,1}[a-zA-Z]{0,30}[\\s]{0,1}\";\n",
    "        Name1=[]\n",
    "        Title=''\n",
    "        file = open(filename,'r')\n",
    "        line=file.readlines()\n",
    "        Name=re.findall(pattern,line[b])\n",
    "        is_Name=bool(Name)\n",
    "        if is_Name==True:\n",
    "            Title=Title+Name[0]\n",
    "        Range1=list5[C]\n",
    "        for i in range(Range1-1,Range1+3):\n",
    "            if line[i][0].isdigit()!=True:\n",
    "                Name1=re.findall(pattern,line[i])\n",
    "                is_Name1=bool(Name1)\n",
    "                if is_Name1==True:\n",
    "                    Title=Title+Name1[0]\n",
    "        worksheet.write('E'+str(C+18),Title,merge_format_1)\n",
    "        file.close()\n",
    "        \n",
    "        # In price table first is either S.No and Product ID before description . \n",
    "        # After description first is SKU and then HSN . We find the occurnce of them by searching the word name in index . \n",
    "        file = open(filename,'r')\n",
    "        pattern = \"[a-zA-Z]{1,30}[\\s][a-zA-Z]{0,30}[\\s]{0,1}[a-zA-Z]{0,30}[\\s]{0,1}[a-zA-Z]{0,30}[\\s]{0,1}[a-zA-Z]{0,30}[\\s]{0,1}[a-zA-Z]{0,30}[\\s]{0,1}[a-zA-Z]{0,30}[\\s]{0,1}\";        Name1=[]\n",
    "        file = open(filename,'r')\n",
    "        line=file.readlines()\n",
    "        SKU=[]\n",
    "        HSN=[]\n",
    "        Name1=re.findall(pattern,line[b])\n",
    "        s=line[b]\n",
    "        file.close()\n",
    "        if bool(Name1)==True:\n",
    "            s=s.split(Name1[0])\n",
    "        j=0\n",
    "        file = open(filename,'r')\n",
    "        specific_line=''\n",
    "        search_phrase1 = \"SKU\"\n",
    "        for line1 in file.readlines():\n",
    "            if line1.find(search_phrase1) >= 0:\n",
    "                j=j+1\n",
    "                break\n",
    "        if j>0:\n",
    "            pattern=\"[A-Z]{0,5}[\\d]{1,15}[A-Z]{0,5}\";\n",
    "            SKU=re.findall(pattern , s[1])\n",
    "            is_SKU=bool(SKU)\n",
    "            if is_SKU==True:\n",
    "                worksheet.write('C'+str(18+C),SKU[0],merge_format_1)\n",
    "                s[1]=s[1].replace(SKU[0],'')\n",
    "        file.close()\n",
    "        j=0\n",
    "        file = open(filename,'r')\n",
    "        search_phrase1 = \" HS\"\n",
    "        for line1 in file.readlines():\n",
    "            if line1.find(search_phrase1) >= 0:\n",
    "                j=j+1\n",
    "                break\n",
    "        if j>0:\n",
    "            pattern=\"[\\d]{4,16}\";\n",
    "            HSN=re.findall(pattern , s[1])\n",
    "            is_HSN=bool(HSN)\n",
    "            if is_HSN==True:\n",
    "                worksheet.write('D'+str(18+C),HSN[0],merge_format_1)\n",
    "                s[1]=s[1].replace(HSN[0],'')\n",
    "        s[0]=s[0].lstrip(str(C+1))\n",
    "        worksheet.write('B'+str(18+C),s[0],merge_format_1)\n",
    "        file.close()\n",
    "\n",
    "#Extracting all prices in a table starting from the S_No =1 till last \n",
    "# quantity * unit price * (1-discount%) = Taxable Amount       \n",
    "# (quantity * unit price) - discount = Taxable Amount         \n",
    "# quantity * unit price = Taxable Amount        \n",
    "# Checking all the numbers which satisfy this relation . The relation ehich gives least taxable amount is the ans because it will consists discount .        \n",
    "# The remaining number less than 29(Max GST rate is 28%) is CGST, SGST or IGST depending on transportation is interstate or not\n",
    "#Cess percent is applicable in time of VAT so it is 0.0\n",
    "#App% = IGST+CGST+SGST-discount%\n",
    "#Total Amount = Taxable amount * (1+(CGST+SGST)/IGST)\n",
    "# if the data in invoice is wrong everything will be printed 0 \n",
    "        app=0.0\n",
    "        pattern1 = \"[\\d*]{0,5}[,]{0,1}[.]{0,1}[\\d*]{0,5}[,]{0,1}[.]{0,1}[\\d*]{0,5}[,]{0,1}[.]{0,1}[\\d*]{0,5}[,]{0,1}[.]{0,1}[\\d+]{1,9}[.]{0,1}[0-9]{0,3}\";                \n",
    "        everything = re.findall(pattern1, s[1])\n",
    "        for i in range (0,len(everything)):\n",
    "            point=0\n",
    "            length=len(everything[i])\n",
    "            for j in range(0,length):\n",
    "                if everything[i][j]=='.':\n",
    "                     point = point+1\n",
    "            if point > 1: \n",
    "                everything[i]=everything[i].split('.')\n",
    "                correct_number=''\n",
    "                for j in range(0,len(everything[i])-1):\n",
    "                    correct_number=correct_number+everything[i][j]\n",
    "                correct_number=correct_number+'.'+everything[i][len(everything[i])-1]\n",
    "                everything[i]=correct_number\n",
    "        for i in range(0,len(everything)):\n",
    "            everything[i]=everything[i].translate({ord(','): None})\n",
    "        for i in range(0,len(everything)):\n",
    "            everything[i]=everything[i].translate({ord('\\n'): None})\n",
    "        for i in range(0,len(everything)):\n",
    "            everything[i]=float(everything[i])\n",
    "        def remove_values_from_list(the_list, val):\n",
    "             return [value for value in the_list if value != val]\n",
    "        everything = remove_values_from_list(everything, 0.0)\n",
    "        IGST=0.0\n",
    "        CGST=0.0\n",
    "        SGST=0.0\n",
    "        for i in range(0,len(everything)):\n",
    "            if inter_state==0:\n",
    "                if everything[i]==5.0 or everything[i]==12.0 or everything[i]==18.0 or everything[i]==28.0:\n",
    "                    IGST=everything[i]\n",
    "            else:\n",
    "                if everything[i]==5.0 or everything[i]==12.0 or everything[i]==18.0 or everything[i]==28.0:\n",
    "                    CGST=everything[i]/2\n",
    "                    SGST=everything[i]/2\n",
    "                if everything[i]==2.5 or everything[i]==6.0 or everything[i]==9.0 or everything[i]==14.0: \n",
    "                    CGST=everything[i]\n",
    "                    SGST=everything[i]\n",
    "        everything = [x for x in everything if x!=CGST]\n",
    "        everything = [x for x in everything if x!=SGST]\n",
    "        everything = [x for x in everything if x!=IGST]\n",
    "        if CGST>0.0 or IGST>0.0:\n",
    "            worksheet.write('J'+str(18+C),CGST,merge_format_1)\n",
    "            worksheet.write('K'+str(18+C),SGST,merge_format_1)\n",
    "            worksheet.write('L'+str(18+C),IGST,merge_format_1)\n",
    "        else:\n",
    "            worksheet.write('J'+str(18+C),'Detected Wrong or wrong GST written',merge_format_1)\n",
    "            worksheet.write('K'+str(18+C),'Detected Wrong or wrong GST written',merge_format_1)\n",
    "            worksheet.write('L'+str(18+C),'Detected Wrong or wrong GST written',merge_format_1)\n",
    "        app=app+CGST+IGST+SGST\n",
    "        a=0 \n",
    "        b=0\n",
    "        c=0\n",
    "        d=0\n",
    "        list1=[]\n",
    "        list2=[]\n",
    "        list3=[]\n",
    "        def removing_1_function ():\n",
    "            global list1\n",
    "            global list2\n",
    "            global list3\n",
    "            for i in range(0,len(everything)):\n",
    "                a=everything[i]\n",
    "                for m in range(0,len(everything)):\n",
    "                    b=everything[m]\n",
    "                    for k in range(0,len(everything)):\n",
    "                        c=everything[k]\n",
    "                        if a==c:\n",
    "                            continue\n",
    "                        if b==c:\n",
    "                            continue\n",
    "                        for n in range(0,len(everything)):\n",
    "                            if n==k or n==m or n==i:\n",
    "                                continue\n",
    "                            else:\n",
    "                                d=everything[n]\n",
    "                                form_a=a*b*(1-c)\n",
    "                                form_b=(a*b)-c\n",
    "                                form_c=a*b\n",
    "                                a1=max(a,b)\n",
    "                                b1=min(a,b)\n",
    "                                if CGST!=0.0:\n",
    "                                    for y in range(0,len(everything)):\n",
    "                                        if a1/CGST==y:\n",
    "                                            a1=y\n",
    "                                            break\n",
    "                                if form_a == d:\n",
    "                                    list1=[a1,b1,c,d]\n",
    "                                    if list1[0]>1 and list1[1]>1:\n",
    "                                        return \n",
    "                                if form_b==d :\n",
    "                                    list2=[a1,b1,c,d]\n",
    "                                    if list2[0]>1 and list2[1]>1:\n",
    "                                        return\n",
    "                                if form_c==d:\n",
    "                                    list3=[a1,b1,d]\n",
    "                                    if list3[0]>1.0 and list3[1]>1.0:\n",
    "                                        return\n",
    "                   \n",
    "        removing_1_function ()\n",
    "        x=float('inf')\n",
    "        y=float('inf')\n",
    "        z=float('inf')\n",
    "        taxable_amount=0.0\n",
    "        discount_percent=0.0\n",
    "        is_list1=bool(list1)\n",
    "        if is_list1==True:\n",
    "            x=list1[3]\n",
    "        is_list2=bool(list2)\n",
    "        if is_list2==True:\n",
    "            y=list2[3]\n",
    "        is_list3=bool(list3)\n",
    "        if is_list3==True:\n",
    "            z=list3[2]\n",
    "        \n",
    "        if (is_list1==True or is_list2==True or is_list3==True)  :\n",
    "            if(x <= y and x <= z): \n",
    "                worksheet.write('G'+str(18+C),list1[0],merge_format_1)\n",
    "                worksheet.write('F'+str(18+C),list1[1],merge_format_1)\n",
    "                worksheet.write('I'+str(18+C),list1[2],merge_format_1)\n",
    "                app=app-list1[2]\n",
    "                Net_Unit_price=Net_Unit_price+list1[0]\n",
    "                taxable_amount=list1[3]\n",
    "                Net_WH_Amount=Net_WH_Amount+taxable_amount\n",
    "            elif(y <= x and y <= z): \n",
    "                worksheet.write('G'+str(18+C),list2[0],merge_format_1)\n",
    "                worksheet.write('F'+str(18+C),list2[1],merge_format_1)\n",
    "                worksheet.write('I'+str(18+C),list2[2],merge_format_1)\n",
    "                taxable_amount=list2[3]\n",
    "                Net_Unit_price=Net_Unit_price+list2[0]\n",
    "                discount_percent=list2[2]/(list2[0]*list2[1])\n",
    "                app=app-discount_percent\n",
    "                Net_WH_Amount=Net_WH_Amount+taxable_amount\n",
    "            else:\n",
    "                worksheet.write('G'+str(18+C),list3[0],merge_format_1)\n",
    "                worksheet.write('F'+str(18+C),list3[1],merge_format_1)\n",
    "                worksheet.write('I'+str(18+C),'0.0',merge_format_1)\n",
    "                taxable_amount=list3[2]\n",
    "                app=app-discount_percent\n",
    "                qty=qty+list3[1]\n",
    "                Net_Unit_price=Net_Unit_price+list3[0]\n",
    "                worksheet.write('I'+str(18+C),'0.0',merge_format_1)\n",
    "                Net_WH_Amount=Net_WH_Amount+taxable_amount\n",
    "        worksheet.write('M'+str(18+C),'0.0',merge_format_1)\n",
    "        \n",
    "        #Finding the line number of excise duty . The next number after it gives excise duty percent \n",
    "        file = open(filename,'r')\n",
    "        line_num = 0\n",
    "        j=0\n",
    "        specific_line=''\n",
    "        search_phrase1 = \"Excise\"\n",
    "        search_phrase2=\"Duty\"\n",
    "        for line in file.readlines():\n",
    "            line_num += 1\n",
    "            if line.find(search_phrase1) >= 0:\n",
    "                if line.find(search_phrase2) >= 0:\n",
    "                    j=line_num\n",
    "                    break\n",
    "        file.close()\n",
    "        file = open(filename)\n",
    "        all_lines_variable = file.readlines()\n",
    "        if j>0:\n",
    "            specific_line=all_lines_variable[j-1]\n",
    "            specific_line=specific_line.split('Duty')\n",
    "            pattern = \"\\d{1,15}[.]{0,1}\\d{0,3}\"\n",
    "            excise_duty = re.findall(pattern, specific_line[1])\n",
    "            worksheet.write('H'+str(18+C),excise_duty[0],merge_format_1)\n",
    "        else:\n",
    "            worksheet.write('H'+str(18+C),'0.0',merge_format_1)\n",
    "        file.close()\n",
    "\n",
    "         #Finding the line number of TCS . The next number after it gives TCS percent .\n",
    "        file = open(filename,'r')\n",
    "        line_num = 0\n",
    "        j=0\n",
    "        specific_line=''\n",
    "        search_phrase1 = \"TCS\"\n",
    "        for line in file.readlines():\n",
    "            line_num=line_num+1\n",
    "            if line.find(search_phrase1) >= 0:\n",
    "                j=line_num\n",
    "                break\n",
    "        file.close()\n",
    "        file = open(filename)\n",
    "        all_lines_variable = file.readlines()\n",
    "        if j>0:\n",
    "            specific_line=all_lines_variable[j-1]\n",
    "            specific_line=specific_line.split('TCS')\n",
    "            pattern = \"\\d{1,15}[.]{0,1}\\d{0,3}\"\n",
    "            TCS = re.findall(pattern, specific_line[1])\n",
    "            worksheet.write('N'+str(18+C),TCS[0],merge_format_1)\n",
    "            Total_TCS=Total_TCS+(TCS[0]*taxable_amount)\n",
    "        else:\n",
    "            worksheet.write('N'+str(18+C),'0.0',merge_format_1)\n",
    "        file.close()\n",
    "        \n",
    "        Total_amount=taxable_amount - discount_percent + (taxable_amount*app)/100\n",
    "        worksheet.write('O'+str(C+18) , Total_amount ,merge_format_1 )\n",
    "        Net_Amount=Net_Amount+Total_amount\n",
    "        C=C+1\n",
    "worksheet.merge_range('M7:Q7' , qty ,merge_format_1 )\n",
    "worksheet.merge_range('M12:Q12' , qty ,merge_format_1 )\n",
    "worksheet.write('F'+str(18+C) , qty ,merge_format_5 )\n",
    "worksheet.write('G'+str(18+C),Net_Unit_price,merge_format_5)\n",
    "worksheet.merge_range('M11:Q11' , Net_Amount ,merge_format_1 )\n",
    "worksheet.write('O'+str(C+18) , Net_Amount ,merge_format_5 )\n",
    "worksheet.merge_range('M6:Q6' , Net_WH_Amount ,merge_format_1 )\n",
    "worksheet.merge_range('M8:Q8',Total_TCS,merge_format_1)\n",
    "fractional,whole=math.modf(Net_Amount)\n",
    "if fractional >= .50:\n",
    "    whole=whole+1\n",
    "    result = re.match(r\"whole\", filename)\n",
    "    if result is not None :\n",
    "        worksheet.merge_range('M9:Q9' , whole ,merge_format_1 )\n",
    "else:\n",
    "    result = re.match(r\"whole\", filename)\n",
    "    if result is not None :\n",
    "        worksheet.merge_range('M9:Q9' , whole ,merge_format_1 )\n",
    "workbook.close()       \n",
    "os.remove(outfile)"
   ]
  }
 ],
 "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}