Is get_full_list ignoring AND?

Hi all

I’ve been developing a timesheet module for us to track time spent on client matters and to make checking and invoicing them as easy as possible. I now ran into problems trying to develop an action to convert checked timesheets into invoices. What we are trying to achieve is get employees track their time by creating timesheets, marking their actual and billable time, price (relating to products), text for invoice and so on. All timesheets are then checked by supervisors, and checked and approved timesheets would then be added to an invoice for the client.

I’ve got all other functionality up and running, but I’ve ran into problems trying to collect timesheets to the invoices. After a frustrating evening of debugging I’ve finally found the reason: the get_full_list method doesn’t apparently accept logical operators and so I cannot use multiple ‘where’ criteria. Is this the intended action or am I doing something wrong?

To demonstrate, here’s the code. Note that we are using a custom “Matters” to keep track of client matters, which then can include billable work on projects, cases etc. One can either invoice all timesheets of a matter or just some part of it, e.g. a project or a single case. Currently this depends on the record from which the invoicing action is initiated from.

//// TIMESHEETS ////
	// Produce a list of Timesheet beans.
	$timesheetBean = BeanFactory::getBean('LWS_Timesheets');

  if ($parentmodule != 'LWS_Matters') {
    // if we are invoicing only part of a matter (e.g. project), we'll retrieve according to the parent.
    $timesheetList = $timesheetBean->get_full_list(
      "parent_id = '" . $parentBean->id . "'",
    ); //get_full_list
  } elseif ($parentmodule == 'LWS_Matters') {
    // If we are invoicing full matter, we'll retrieve by the parent matter. TODO: Sort these by parents, if multiple.
    $timesheetList = $timesheetBean->get_full_list(
      "lws_matters_id_c = '" . $matterBean->id . "'",
    ); //get_full_list
  } //if

This version seems to work, but I still need to separately filter all records except those with status = ‘Checked’. However the method returned NULL when I tried using AND to add more conditions. To be clear, the same clause worked when I tried in phpmyadmin. Example returning NULL:

   // if we are invoicing only part of a matter (e.g. project), we'll retrieve according to the parent.
    $timesheetList = $timesheetBean->get_full_list(
      "parent_id = '" . $parentBean->id . "' AND status = 'Checked'",
    ); //get_full_list

Have I misunderstood something, is this an intended yet undocumented limitation or have I stumbled accross a bug that shouldn’t be there?

There are examples in the core code of AND clauses there

Try increasing your log level to DEBUG and see in the logs how the query is generated, this will surely tell you where the problem is.

You might need table qualifiers for the fields, for example ( instead of just id).

I think i have a similar issue.

i have a custom subpanel that is getting data by using

$opportunityBeanList = $opportunityBean ->get_full_list(’’,“opportunities.last_name = ‘Strachen’” );
return $opportunityBeanList;

in the log file this results in the following query

SELECT opportunities.* ,opportunities_cstm.subject_c,opportunities_cstm.budget_low_c,opportunities_cstm.countries_c,opportunities_cstm.telephone_c,opportunities_cstm.codetesting_c,opportunities_cstm.email_c,opportunities_cstm.contact_id1_c,opportunities_cstm.budget_high_c,opportunities_cstm.mobile_c,opportunities_cstm.contact_notes_c,opportunities_cstm.reference_c,opportunities_cstm.contact_id_c,opportunities_cstm.received_c , jt0.user_name modified_by_name , jt0.created_by modified_by_name_owner , ‘Users’ modified_by_name_mod , jt1.user_name created_by_name , jt1.created_by created_by_name_owner , ‘Users’ created_by_name_mod , jt2.user_name assigned_user_name , jt2.created_by assigned_user_name_owner , ‘Users’ assigned_user_name_mod , campaign_name , jt3.assigned_user_id campaign_name_owner , ‘Campaigns’ campaign_name_mod, ’ ’ site_site_opportunities_1_name , ’ ’ site_site_opportunities_1site_site_ida , LTRIM(RTRIM(CONCAT(IFNULL(opportunities.first_name,’’),’ ‘,IFNULL(opportunities.last_name,’’)))) as full_name, LTRIM(RTRIM(CONCAT(IFNULL(jt5.first_name,’’),’ ‘,IFNULL(jt5.last_name,’’)))) referredby_c , LTRIM(RTRIM(CONCAT(IFNULL(jt6.first_name,’’),’ ‘,IFNULL(jt6.last_name,’’)))) contact_c FROM opportunities LEFT JOIN opportunities_cstm ON = opportunities_cstm.id_c LEFT JOIN users jt0 ON AND jt0.deleted=0
AND jt0.deleted=0 LEFT JOIN users jt1 ON AND jt1.deleted=0
AND jt1.deleted=0 LEFT JOIN users jt2 ON AND jt2.deleted=0
AND jt2.deleted=0 LEFT JOIN campaigns jt3 ON AND jt3.deleted=0
AND jt3.deleted=0 LEFT JOIN contacts jt5 ON opportunities_cstm.contact_id_c = AND jt5.deleted=0 LEFT JOIN contacts jt6 ON opportunities_cstm.contact_id1_c = AND jt6.deleted=0 where (opportunities.last_name = ‘Strachen’) AND opportunities.deleted=0

and then

[DEBUG] process_full_list_query: result is mysqli_result Object
[current_field] => 0
[field_count] => 73
[lengths] =>
[num_rows] => 1
[type] => 0

and all 73 of the opportunities show up in the sub panel. :frowning:

However when i run the query (copied from the log file) in phpmyadmin it returns the only one record with the surname ‘strachen’.:slight_smile:

im completely stumped - why does the query run correctly in mysql, but the filtering is ignored in suitecrm?