Query language for Delegate Class [Solved]

[jhenry]

I know this has a simple answer but I can't seem to get my head around this search. I have scoured the forums for several hours and can't seem to find an answer. I have a Work_Orders table that has two fields total_labor and total travel. I have a related table Time_Entry that shows up as a grid on the Work_Orders data entry screen that has labor_time and travel_time fields that are date stamped and have a foreign key workorder_id that relates them to the Work_Order record. What I need is a field that uses the beforeSave function in the Delegate Class to update the total fields. I only want to total the entries with the related workorder_id for the record I am in.
Here is what I have so far:

class tables_Work_Orders {

function beforeSave(&$record){
$lab = mysql_query("Select sum(labor_time) from Time_Entry t inner join Work_Orders w where t.workorder_id=w.workorder_id ");
$trv = mysql_query("Select sum(travel_time) from Time_Entry t inner join Work_Orders w where t.workorder_id=w.workorder_id");
$record->setValue('total_labor', $lab);
$record->setvalue('total_travel', $trv);
}
}


All that shows up in the total_labor and total_travel fields is 99.99

TIA
Jason

[shannah]

There are a couple of things to note about this code:

1. the mysql_query() method returns a resource handle, not the value of a particular column. E.g.

Code: Select all

$lab = mysql_query("Select sum(labor_time) from Time_Entry t inner join Work_Orders w where t.workorder_id=w.workorder_id ");


should probably be something like:

Code: Select all

list($lab) = mysql_fetch_row(mysql_query("Select sum(labor_time) from Time_Entry t inner join Work_Orders w where t.workorder_id=w.workorder_id ", df_db()));


2. Your MySQL query here is getting the sum of ALL labor time for all work orders. Is this what you want? You probably want to change the query to retrieve the time for a particular work order. e.g.

Code: Select all

$sql = "select sum(labor_time) from Time_Entry where workorder_id='".$record->val("workorder_id")."'";


[jhenry]

Here is what I have so far:

Code: Select all

function beforeSave(&$record){
         $woid = $record->val('workorder_id');
             list($lab) = mysql_fetch_row(mysql_query("Select sum(labor_time) from Time_Entry t where t.workorder_id=$woid", df_db()));
             list($trv) = mysql_fetch_row(mysql_query("Select sum(travel_time) from Time_Entry t where t.workorder_id=$woid", df_db()));
       $record->setValue('total_labor', $lab);
       $record->setValue('total_travel', $trv);

This gives me a total of labor time as it should. My problem now is I have a value in another table Customers of labor_rate and travel_rate that I want to pull that value into this function to give a dollar figure instead of just the hours I tried this:

Code: Select all

function beforeSave(&$record){
         $woid = $record->val('workorder_id');
             list($lab) = mysql_fetch_row(mysql_query("Select sum(labor_time) from Time_Entry t where t.workorder_id=$woid", df_db()));
             list($trv) = mysql_fetch_row(mysql_query("Select sum(travel_time) from Time_Entry t where t.workorder_id=$woid", df_db()));
             $lrate = mysql_query("Select c.labor_rate from Customers c left join Equipment e on c.customer_id=e.customer_id left join Work_Orders w on
               e.control_number = w.control_number where w.workorder_id = $woid");
             $tlab =$lab*$lrate;
       $record->setValue('total_labor', $tlab);
       $record->setValue('total_travel', $trv);

But it just gives a 99.99 in each of the fields. I tried the SQL statement in mysql commandline and it works. I don't know if the mysql_query is the correct function.

TIA,
Jason

[shannah]

You've still got a problem with the $lrate variable.

You probably want to go through this code step by step to see where it's going wrong.

E.g.
echo "$lab : $trv : $lrate";exit;
See what it says.

[jhenry]

It says:

4.00 : 4.00 : 75.00

Which is what the values should be. But it is not putting the value into the field. Not sure what the next step is.

TIA

Jason

[jhenry]

Ok I feel really stupid but I figured it out. One set of values was a float(8,2) and one was a float(4,2) when I changed them to match it multiplied out perfectly. Sorry about that and I appreciate the help. Thanks again for all the assistance.

Jason