2. PHPRunner General questions
  3. [SOLVED]  Generate invoice details
This topic is locked
[SOLVED]

 Generate invoice details

 		5/5/2014 10:18:10 PM
PHPRunner General questions
P
 pmorenoc07 author

Hi everyone,
I need some help with the invoice templete

What I want when adding new Invoice is to have button to generate invoicedatails table from invitem table

where client and start_dateis between[i]from_date[/i], to_date time frame from the master table invoice
This are the 3 example tables
Master table-> invoice

Fields->id, , date, invoice_number, clientId, from_date, to_date, total
Detail table-> invoicedetails

Fields-> id, id_invoice, start_date, item, price, quantity, total
Items table->invitems

Fields-> id, start_date, item, price
I'am stuck in something like this query:
$sql = "INSERT INTO invoicedetails(id_invoice, start_date, end_date, item, price)

SELECT start_date, end_date, item

FROM invitems

WHERE start_date BETWEEN $values["from_date"] AND $values["to_date"] AND clientId = $values["clientId"]";

CustomQuery($sql);
Note: WHERE codition are the values selected in the master invoice form.
Thank you all.
Sergey Kornilov admin 5/6/2014

Could you elaborate a little bit what causes the trouble?

Were you able to run this code? If yes, are there any error messages?
P
 		pmorenoc07 author 5/7/2014



Could you elaborate a little bit what causes the trouble?

Were you able to run this code? If yes, are there any error messages?


Ok I inserted this code in Add event Before record added

if I do a simple sql lake this one it works

$sql = "INSERT INTO invoicedetails(id_invoice, start_date, end_date item, price) values ('".$values['id']."' ,'2014-05-1', '2014-05-5','item name', 80)";

CustomQuery($sql);
The problem is when I try to SELECT values FROM another table with condition.

I was reading about DAL but I confused how to implemented here or if there is another way to do it.

Any clue will be good.
Sergey Kornilov admin 5/8/2014

Instead of executing the query you may want to print it on the page to see what might be wrong.

$sql = "INSERT INTO invoicedetails(id_invoice, start_date, end_date item, price) values ('".$values['id']."' ,'2014-05-1', '2014-05-5','item name', 80)";

echo $sql;

exit();

CustomQuery($sql);


My guess is that instead of $values['id'] you need to use $keys['id'] if 'id' is your key column.
W
 		wpl 5/8/2014



Ok I inserted this code in Add event Before record added

if I do a simple sql lake this one it works

$sql = "INSERT INTO invoicedetails(id_invoice, start_date, end_date item, price) values ('".$values['id']."' ,'2014-05-1', '2014-05-5','item name', 80)";

CustomQuery($sql);
The problem is when I try to SELECT values FROM another table with condition.

I was reading about DAL but I confused how to implemented here or if there is another way to do it.

Any clue will be good.


Hi,
I think you have to be careful with the number of arguments you supply to the insert statement.



INSERT INTO invoicedetails(id_invoice, start_date, end_date, item, price)


There are 5 arguments but your select query only returns 3,



SELECT start_date, end_date, item

FROM invitems


whereas your simple query gives the correct number of arguments.



values ('".$values['id']."' ,'2014-05-1', '2014-05-5','item name', 80)


If you supply less arguments than there are fields, you have to name only the fields in your insert query you want to populate, I guess.
HTH
P
 		pmorenoc07 author 5/9/2014



Hi,
I think you have to be careful with the number of arguments you supply to the insert statement.



INSERT INTO invoicedetails(id_invoice, start_date, end_date, item, price)


There are 5 arguments but your select query only returns 3,



SELECT start_date, end_date, item

FROM invitems


whereas your simple query gives the correct number of arguments.



values ('".$values['id']."' ,'2014-05-1', '2014-05-5','item name', 80)


If you supply less arguments than there are fields, you have to name only the fields in your insert query you want to populate, I guess.
HTH


Thank you all,

Now is working.
Here is my final code.


$rs = CustomQuery("select max(".AddFieldWrappers("id").") as max_number from ".AddTableWrappers("invoices"));

$data = db_fetch_array($rs);

if ($data)

$keys['id'] = $data['max_number'] + 1;
$sql= ("INSERT INTO invoicedetails (id_invoice, start_date, end_date, item, price)

SELECT '".$keys['id']."',start_date, end_date, item, price

FROM invitems

WHERE client_id = '".$values['client_id']."' AND start_date BETWEEN '".$values['from_date']."' AND '".$values['to_date']."'");
CustomQuery($sql);