In this section, we will go through the code that we use to validate the processing of the invoice. You will need to load the data in the timesheet table to cross-verify with the invoice table (the invoice table was populated using the PDF file).

The following are the steps to create a timesheet table in the database and load data into the table:

1. Use the Cloud Shell instance to connect to MySQL, as we discussed in step 2 of the Storing the invoice with Cloud SQL recipe and run the following queries to create the table timesheet table:

create table timesheet 
(Company_Name VARCHAR(50),
SOW_Number VARCHAR(50),
Project_ID VARCHAR(50),
Invoice_Number VARCHAR(50),
Invoice_Date DATE,
Billing_Period DATE,
Developer VARCHAR(255),
Rate VARCHAR(50),
Hours INT,
Bank_Account_Number INT,
Bank_Name VARCHAR(50));

2. Run the following queries to insert data into the timesheet table. As mentioned earlier, you can use aip.sql to get these queries:

insert into timesheet (Company_Name, SOW_Number, Project_ID, Invoice_Number, Invoice_Date, Billing_Period, Developer, Rate, Hours, Bank_Account_Number, Bank_name)
values ('Vsquare Systems', '001', '002', '030', '2020-01-31', '2020-01-31', 'Developer1', '$185', 160, 000000001, 'Payment Bank');

insert into timesheet (Company_Name, SOW_Number, Project_ID, Invoice_Number, Invoice_Date, Billing_Period, Developer, Rate, Hours, Bank_Account_Number, Bank_name)
values ('Vsquare Systems', '001', '002', '030', '2020-01-31', '2020-01-31', 'Developer2', '$150', 152, 000000001, 'Payment Bank');

insert into timesheet (Company_Name, SOW_Number, Project_ID, Invoice_Number, Invoice_Date, Billing_Period, Developer, Rate, Hours, Bank_Account_Number, Bank_name)
values ('Vsquare Systems', '001', '002', '030', '2020-01-31', '2020-01-31', 'Developer3', '$140', 168, 000000001, 'Payment Bank')

Now that we have the data ready, let's take a deep dive into the validation process.

3. Let's walk through the code that we need for data validation. In the following code, we extract data from the timesheet table that we created earlier and we create the data frame based on the Company Name, SOW_Number, Project_ID, Invoice_Number, and Developer:

#Reading data from timesheet table.
 df_timesheet = pd.read_sql(f'SELECT * FROM timesheet', con=db_con)
 joined_df = pd.merge(df_invoice, df_timesheet, on=['Company_Name','SOW_Number','Project_ID','Invoice_Number','Invoice_Date','Developer'])

In the following code, we are matching the invoice and timesheet data frame based on the rate and the hours. If all the records match, then we can move on; if there is a discrepancy, then the invoice will not be processed:

#Matching data of both tables.
 matched = []
 
 for index, row in joined_df.iterrows():
 
 if row['Rate_x'] == row['Rate_y'] and row['Hours_x'] == row['Hours_y'] and                                               row['Bank_Account_Number_x'] ==                                                                   row['Bank_Account_Number_y']:
 
     matched.append(True)
 
 else:
 
     matched.append(False)