PCSE to Xero Automoation

import matplotlib.pyplot as plt

x = [1, 2, 3, 4, 5, 6]
y = [1, 5, 3, 5, 7, 8]

plt.plot(x, y)
plt.show()

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!!")

plt.figure(figsize=(10,10))
sns.heatmap(data.iloc[:, 1: 12].corr(),annot=True,fmt='.0%',cmap='twilight')

sns.pairplot(df,hue = ‘smoker’,diag_kind = “kde”,kind = “scatter”,palette = “husl”)

#where :
#kind : Kind of plot for the non-identity relationships. {‘scatter’, ‘reg’}
#diag_kind : Kind of plot for the diagonal subplots. {‘hist’, ‘kde’}

labels = 'Frogs', 'Hogs', 'Dogs', 'Logs'
sizes = [15, 30, 45, 10]
explode = (0, 0.1, 0, 0)  # only "explode" the 2nd slice (i.e. 'Hogs')

fig1, ax1 = plt.subplots()
ax1.pie(sizes, explode=explode, labels=labels, autopct='%1.1f%%',
        shadow=True, startangle=90)
ax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.

plt.show()

def print_data_chart():
    data = pd.read_csv("USDCAD.csv")
    chart_data = go.Candlestick(x=data['time'], open=data['open'], high=data['high'], low=data['low'], close=data['coose'])

# Importing Libraries
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import seaborn as sns

# Dataprep for exploratory data analysis
from dataprep.eda import create_report
from dataprep.eda import plot, plot_correlation, plot_missing

# mport Regressor Metric Graph Plot - for ML analysis
from regressormetricgraphplot import *

# Set style to 'seaborn / Plot inline
plt.style.use('seaborn')
%matplotlib inline
%config InlineBackend.figure_format = 'retina'


# Load Dataframe
df = pd.read_csv('path/to/file.csv')
df.head()

df.isna().sum()
df.drop_duplicates(inplace = True)
df.info()
df.describe()

# Generate Dataprep Report
report = create_report(df, title='My Report')
create_report()

plot(df, col1, col2)

# df sortby
df.sort_values(by=[col1, col2], ascending=[True, True])

# df groupby
df.groupby(col).mean()