Xataface Forums

I had a working Table Delegate class in my Work_Orders table before I upgraded to 1.2.2. The Work_Orders.php calculates the totals for several fields. I ran an echo statement and nothing is shown for the variables below:

Code: Select all

<?php

class tables_Work_Orders{
  function beforeSave(&$record){
              $txab=$record->val('tax');
              $notx = '0';

    list($lab) = mysql_fetch_row(mysql_query("Select sum(labor_time) from Time_Entry t where t.workorder_id=$record->val('workorder_id')", df_db()));

    list($trv) = mysql_fetch_row(mysql_query("Select sum(travel_time) from Time_Entry t where t.workorder_id=$record->val('workorder_id')", df_db()));

    list($lrate) = mysql_fetch_row(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 = $record->val('workorder_id')", df_db()));

    list($trate) = mysql_fetch_row(mysql_query("Select c.travel_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 =$record->val('workorder_id')", df_db()));

    list($ptot) = mysql_fetch_row(mysql_query("Select sum(p.total) from Part_Entry p where p.workorder_id =$record->val('workorder_id')", df_db()));

echo "$lab","$trv","$lrate","$trate","$ptot";exit;

list($txrt) = mysql_fetch_row(mysql_query("Select a.taxrate from account_taxes a left join Customers c on a.id = c.taxid 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 =$record->val('workorder_id')",
               df_db()));

             $tott =($trate*$trv);

           $totl =($lrate*$lab);

             $sub = ($ptot+$tott+totl);

           $tax = ($sub*$txrt);

           $wotl = ($sub+$tax);

       if ($txab==1)  {
           $record->setValue('tax_total', $tax);
           $record->setValue('total', $wotl);
          }else{
               $record->setValue('tax_total', $notx);
             $record->setValue('total', $sub);
          }

       $record->setValue('total_labor', $totl);

      $record->setValue('total_travel', $tott);

       $record->setValue('total_parts', $ptot);

      $record->setValue('subtotal', $sub);

      }
}

?>




I upgraded from 1.1.4 so that I could utilise the lookup function. I ran all the sql statements in mysql commandline and they are ok. I do get the correct value for the $txab variable when i run the echo statement after it. Any suggestions?

TIA
Jason

What version of PHP are you using?

If I remember correctly it is 5.2.4. But I will double check once I get back home.

TIA

Jason

I double checked the version and it is 5.2.6.

TIA

Jason

Ok here is where I am at I can get everything to calculate properly if I assign the workorder_id to a variable:

Code: Select all

  $woid = $record->val('workorder');

And then change the sql to:

Code: Select all

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

And everything will total for an existing record but if I add a new record I get errors saying the resource is not valid because the workorder_id is an autoincrement field so the value is not there before save.

TIA
Jason

worker_id will be available in afterSave(). If you do it in afterSave() you'll want to probably use raw SQL to update the record, since merely using setValue() won't help you (as the save has already taken place by that point).

-Steve

So just do an INSERT using the mysql_query string?

Such as:

Code: Select all

       if ($txab==1)  {
               mysql_query("INSERT INTO Work_Orders (tax_total, total) VALUES ($tax, $wotl)");
          }else{
               mysql_query("INSERT INTO Work_Orders (tax_total, total) VALUES ($notx, $sub)");
         }


TIA
Jason

No. You'll need to do an update because the record has already been created at this point.

Code: Select all

$res = mysql_query("update Workorders set foo='".addslashes('bar').'" where workorder_id='".addslashes($workorder_id).'"', df_db());


Still seem to be missing something on my end. Here is the whole file. I ran the echo statement and all the values are fine but they are not placed into the record.

Code: Select all

<?php

class tables_Work_Orders{
  function afterSave(&$record){
              $txab=$record->val('tax');
              $woid = $record->val('workorder_id');
              $notx = '0';
        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()));
        list($lrate) = mysql_fetch_row(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", df_db()));
        list($trate) = mysql_fetch_row(mysql_query("Select c.travel_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", df_db()));
        list($ptot) = mysql_fetch_row(mysql_query("Select sum(p.total) from Part_Entry p where p.workorder_id = $woid", df_db()));
        list($txrt) = mysql_fetch_row(mysql_query("Select a.taxrate from account_taxes a left join Customers c on a.id = c.taxid left join Equipment e $
               c.customer_id=e.customer_id left join Work_Orders w on e.control_number = w.control_number where w.workorder_id =$woid", df_db()));
            $tott =($trate*$trv);
            $totl =($lrate*$lab);
            $sub = ($ptot+$tott+$totl);
            $tax = ($sub*$txrt);
            $wotl = ($sub+$tax);
       if ($txab==1)  {
           $res = mysql_query("update Work_Orders set tax_total='".addslashes('$tax')."',total='".addslashes('$wotl')."' where workorder_id='".addslash$
         }else{
             $res = mysql_query("update Work_Orders set tax_total='".addslashes('$notx')."',total='".addslashes('$sub')."' where workorder_id='".addsla$
           }
            $res = mysql_query("update Work_Orders set total_labor='".addslashes('$totl')."',total_travel='".addslashes('$tott')."',
                   total_parts='".addslashes('$ptot')."',subtotal='".addslashes('$sub')."' where workorder_id='".addslashes($woid)."'", df_db());
      }
}
?>


Sorry I am sure this is simple but I not that up on the syntax.

Jason

I think I figured out a solution. I just nested the calculations in an if/then statement that uses the isset() function and tell it to perform the calculations if the value is not null and to do nothing if not. I will just have to save the record twice for it to perform the calculations. Thank you again for all the assistance on this one.

Jason