2. PHPRunner General questions
  3. How can I create an Invoice from two Custom Views
This topic is locked

How can I create an Invoice from two Custom Views

 		1/2/2008 05:41:35
PHPRunner General questions
S
 swanside author

Hello.

I am using PHPRunner 4.1 and have two custom views, Material Invoice and Labour Invoice.
I need to get the data from both of these custom views and create an Invoice or maybe another custom view with the totals on the same page for printing.
Here is my code for the material Invoice.

select `Job_No`,

(Quantity*Unit_Price+(Quantity*Unit_Price*Additional_Cost/100)),

(Quantity*Unit_Price+(Quantity*Unit_Price*Additional_Cost/100))*0.175,

(Quantity*Unit_Price+(Quantity*Unit_Price*Additional_Cost/100))*1.175,

`Unit_Price`,

`Additional_Cost`,

`Description`,

`VAT`

From `material`


and here is my code for the Labour Invoice.

select Job_No,

Paying_Rate*Working_Hrs,

Paying_Rate*Working_Hrs*0.175,

Paying_Rate*Working_Hrs*1.175

From `labour`


I have uploaded it to a demo account to play around with. The login in pggrimes@hotmail.com and the password is 1234
Thanks

Paul.
Sergey Kornilov admin 1/3/2008

Answered to your personal email.
S
 		swanside author 1/3/2008

Thanks, I have sent you some screen shots.
Paul.
S
 		swanside author 1/3/2008

IDEA!!!!!
If I added three fields to the following table, labour. Labvat, Labsubtotal,Labtotal.
Made a custom view to read,

select Job_No,

Paying_Rate*Working_Hrs,

Paying_Rate*Working_Hrs*0.175,

Paying_Rate*Working_Hrs*1.175

From `labour`


Then added another three fields to the table, materials. Matvat, Matsubtotal,Mattotal
Made a custom view to read,

select `Job_No`,

(Quantity*Unit_Price+(Quantity*Unit_Price*Additional_Cost/100)),

(Quantity*Unit_Price+(Quantity*Unit_Price*Additional_Cost/100))*0.175,

(Quantity*Unit_Price+(Quantity*Unit_Price*Additional_Cost/100))*1.175,

`Unit_Price`,

`Additional_Cost`,

`Description`,

`VAT`

From `material`


How would I get the results to go back into the correct fields in the correct table?
Then,
I could make a custom field to get the info from the two tables which has already been calculated using the JOIN word??
similar to this, if its correct.

select job.`Job_No`,

labour.Labsubtotal,

labour.Labvat,

labour.Labtotal,

material.Matsubtotal,

material.Matvat,

material.Mattotal

from job inner join labour

on (job.Job_No = labour.Job_No)

inner join material

on (job.Job_No = material.Job_No)


Cheers

Paul.
S
 		swanside author 1/4/2008

Sorry to add to this again, but, I am now getting desparate. I really need to get this completed before Monday, as I need to produce it to my boss, and give him the receipt for the software.

If anybody has any ideas, please let me know.

Thanks

Paul.