PCSE to Xero Automoation
Sat Oct 29 2022 22:11:33 GMT+0000 (Coordinated Universal Time)
Saved by @janduplessis883 ##line #plot
import PyPDF2 import pandas as pd from tkinter import Tk from tkinter.filedialog import askopenfilename import os import shutil #Collect required information invoice_date_input = input("What is the invoice date? Format: 'DD-MMM-YYYY' ") invoice_number_input = input("Sepcify unique Xero Invoice Number: AutoINV-") invoice_number_input = "AutoINV-"+invoice_number_input Tk().withdraw() # we don't want a full GUI, so keep the root window from appearing pdf_path = askopenfilename() # show an "Open" dialog box and return the path to the selected file pdf = open(pdf_path, 'rb') pdfReader = PyPDF2.PdfFileReader(pdf) page_one = pdfReader.getPage(0) page1 = page_one.extractText() page_two = pdfReader.getPage(1) page2 = page_two.extractText() print('PDF extraction successfull.') #Prepare Python List from extracted data - Check Current Format list_all = page1 + page2 list_all2 = list_all.splitlines() print("Output > list_all2 ", type(list_all2), "Successful") print(list_all) #Extract Total Paid to variable total_paid = list_all2[-1] total_amount = float(total_paid.split('£')[-1].replace(",","")) print("Total Paid - ", total_amount) counta = 0 final_list = [] exclude_list = ["Paycode Description", "GMC Code Name Tier Rate", "Paycode Description", "Type Source", "Total Amount Paid", "Invoice", "Net", "Credit", "Percentage Received"] iter_index = 0 # first iter over list_all2 with list comprehension to find index of Global sum +1 and extract for proceeding. for list in list_all2: if [ex for ex in exclude_list if(ex in list)]: #print("Invoice Line Removed - ", iter_index) iter_index = iter_index +1 elif "£" in list: final_list.append(str(list)) counta = counta +1 iter_index = iter_index +1 # Find Index of Global Sum ammount in all_list2 iter_index = 0 search_text = "Capitation Monthly Payment" for list in list_all2: if search_text not in list: iter_index = iter_index + 1 else: print("Global Sum Amount found - list_all2 index: " + str(iter_index + 1)) global_sum_index = iter_index + 1 globalsum_amount = list_all2[global_sum_index] globalsum_amount = globalsum_amount.rstrip().replace(",","") print(globalsum_amount) global_sum_str = "Global Sum £"+ str(globalsum_amount) #print(global_sum_str) final_list.append(global_sum_str) #print("final_list created - entries = " + str(len(final_list))) #print(final_list) print("Global Sum - **Successful**") #Declairing required variables. contact_name = [] email_address = [] PO1 = [] PO2 = [] PO3 = [] PO4 = [] city = [] PO_region = [] postal_code = [] country = [] address1 = [] address2 = [] address3 = [] address4 = [] sa_city = [] sa_region = [] sa_postalcode = [] sa_country = [] invoice_number = [] ref = [] invoice_date = [] due_date = [] planned_date = [] total = [] tax_total = [] invoice_amount_paid = [] invoice_amount_due = [] inv_item_code = [] description = [] quantitiy = [] unit_amount = [] line_amount = [] account_code = [] tax_type = [] discount =[] tax_amount = [] trackn1 = [] tracko1 = [] trackn2 = [] tracko2 = [] currency = [] type_ = [] sent = [] status = [] #function to assign Account Code account_code_dic = { "rates": "102", "childhood flu": "124A", "influenza": "124", "mmr": "137", "pneumoc": "125", "rotavirus": "128", "hpv": "158", "men b": "135", "hib/men c": "135A", "6 in 1": "120", "pms cis": "209", "last phase of life": "165", "hard to reach": "193", "carers": "193", "my way": "183B", "access": "193", "long covid": "193", "pcn participation": "250B", "rent": "111", "prescribing": "203", "ecg": "185", "spoke": "132A", "warfarin": "181", "homeless": "186", "wound": "192", "mental": "187", "diabetes": "184", "phlebotomy": "189", "coordinate": "183", "levy": "523", "global": "100", "aspiration": "103" } #function to assign account_code def get_account_no(invoice_description): output = "" for x, y in account_code_dic.items(): if x in invoice_description: output = y else: pass return output for l in final_list: if "-------------" in l: index_in_l = final_list.index("-----------") print("****** Global Sum on next String **********", index_in_l) #value_pair_list = list.split("£") #net_income_str = value_pair_list[-1] #remove ',' from str #net_income = net_income_str.replace(",", "") #net_income = float(net_income) #print("Net Income = ", net_income, type(net_income)) elif "-£" in l: print('invoiceable MINUS - '+ l) value_pair_list = l.split("-£") invoicable_desc = value_pair_list[0] invcoice_desc_lower = invoicable_desc.lower() value_pair_list = l.split("£") invoicable_desc = l[0] invoicable_value_str = value_pair_list[-1].replace(",","") invoicable_value = -abs(float(invoicable_value_str)) contact_name.append('NHS England GMS') email_address.append("") PO1.append("") PO2.append('') PO3.append('') PO4.append('') city.append('') PO_region.append('') postal_code.append('') country.append('') address1.append('') address2.append('') address3.append('') address4.append('') sa_city.append('') sa_region.append('') sa_postalcode.append('') sa_country.append('') invoice_number.append(invoice_number_input) ref.append('') invoice_date.append(invoice_date_input) due_date.append(invoice_date_input) planned_date.append('') tax_total.append(0) invoice_amount_paid.append(0) inv_item_code.append('') description.append(invoicable_desc) quantitiy.append(1) unit_amount.append(invoicable_value) invoice_amount_due.append(invoicable_value) discount.append('') line_amount.append(invoicable_value) tax_type.append('No VAT') tax_amount.append(0) trackn1.append('') tracko1.append('') trackn2.append('') tracko2.append('') currency.append('GBP') type_.append('Sales Invoice') sent.append('') status.append('') account_code.append(get_account_no(invcoice_desc_lower)) else: print("invoiceable POS - "+ l) value_pair_list = l.split("£") invoicable_desc = value_pair_list[0] invcoice_desc_lower = invoicable_desc.lower() invoicable_value_str = value_pair_list[-1].replace(",","") invoicable_value = float(invoicable_value_str) contact_name.append('NHS England GMS') email_address.append("") PO1.append("") PO2.append('') PO3.append('') PO4.append('') city.append('') PO_region.append('') postal_code.append('') country.append('') address1.append('') address2.append('') address3.append('') address4.append('') sa_city.append('') sa_region.append('') sa_postalcode.append('') sa_country.append('') invoice_number.append(invoice_number_input) ref.append('') invoice_date.append(invoice_date_input) due_date.append(invoice_date_input) planned_date.append('') tax_total.append(0) invoice_amount_paid.append(0) inv_item_code.append('') description.append(invoicable_desc) quantitiy.append(1) unit_amount.append(invoicable_value) discount.append('') line_amount.append(invoicable_value) invoice_amount_due.append(invoicable_value) account_code.append(get_account_no(invcoice_desc_lower)) tax_type.append('No VAT') tax_amount.append(0) trackn1.append('') tracko1.append('') trackn2.append('') tracko2.append('') currency.append('GBP') type_.append('Sales Invoice') sent.append('') status.append('') #Calculte the SUM of all the valuse on the list invoicable_value - create 2 lists total and invoice_amount_due invoice_total_sum=0 total = [] invoice_total_sum = sum(unit_amount) total = [invoice_total_sum] * len(currency) print("**Successful**") # dictionary of lists FUMCTION def make_dict(): dict = { 'ContactName': contact_name, 'EmailAddress': email_address, 'POAddressLine1':PO1, 'POAddressLine2': PO2, 'POAddressLine3': PO3, 'POAddressLine4': PO4, 'POCity': city, 'PORegion': PO_region, 'POPostalCode': postal_code, 'POCountry': country, 'SAAddressLine1': address1, 'SAAddressLine2': address2, 'SAAddressLine3': address3, 'SAAddressLine4': address4, 'SACity': sa_city, 'SARegion': sa_region, 'SAPostalCode': sa_postalcode, 'SACountry': sa_country, 'InvoiceNumber': invoice_number, 'Reference': ref, 'InvoiceDate': invoice_date, 'DueDate': invoice_date, 'PlannedDate': invoice_date, 'Total': total, 'TaxTotal': tax_total, 'InvoiceAmountPaid': invoice_amount_paid, 'InvoiceAmountDue': invoice_amount_due, 'InventoryItemCode': inv_item_code, 'Description': description, 'Quantity': quantitiy, 'UnitAmount': unit_amount, 'Discount': discount, 'LineAmount': line_amount, 'AccountCode': account_code, 'TaxType': tax_type, 'TaxAmount': tax_amount, 'TrackingName1': trackn1, 'TrackingOption1': tracko1, 'TrackingName2': trackn2, 'TrackingOption2': tracko2, 'Currency': currency, 'Type': type_, 'Sent': sent, 'Status': status } return dict df = pd.DataFrame(make_dict()) df.shape print("DataFrame **Successful**") directory = "/XERO_CSV Export/" if not os.path.exists(directory): os.makedirs(directory) file_name = "Xero Import CSV - " + str(invoice_date_input) + ".csv" output_path = directory + file_name df.to_csv(output_path, index=False) print("✅ File Export Successful >>> MyGoogleDrive/Xero Automation/" + file_name + "\n") check_total = round(total_amount - invoice_total_sum, 2) if check_total > 0.0: print(f"PDF Invoice Total = £{total_amount}") print(f"CSV Invoice Total = £{round(invoice_total_sum, 2)}") print(f"⚠️ Manually add invoice item for £{check_total} to Xero after import.") else: print("👍 PDF Invoice Total and CSV Invoice Totals MATCHES!!")
Comments