Date search doesn't work in cases

[waterfoul]

When doing a date search in cases (doesn't matter what range or type of search) it doesn't do anything. I did some debugging myself and with both entered and modified searches active it runs the query

SELECT cases.id ,cases_cstm.contact_id1_c,cases_cstm.case_number_c , cases.case_number , cases.name , accounts.name account_name , accounts.assigned_user_id account_name_owner , 'Accounts' account_name_mod, cases.account_id , cases.status , TRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,''))) contact_c , cases.priority , cases.date_entered , cases.assigned_user_id FROM cases LEFT JOIN cases_cstm ON cases.id = cases_cstm.id_c LEFT JOIN accounts accounts ON
accounts.id= cases.account_id AND accounts.deleted=0
AND accounts.deleted=0 LEFT JOIN contacts jt1 ON cases_cstm.contact_id1_c = jt1.id AND jt1.deleted=0 where cases.deleted=0 ORDER BY cases.case_number ASC LIMIT 0,51

which has no date information in it. Below that in the log there is

1-3c8e-03d5-b598-4dc9437c7fcb][INFO] Query:
SELECT meetings.id, name,reminder_time, description,location, date_start, assigned_user_id
FROM meetings LEFT JOIN meetings_users ON meetings.id = meetings_users.meeting_id
WHERE meetings_users.user_id ='196b1d71-3c8e-03d5-b598-4dc9437c7fcb'
AND meetings_users.accept_status != 'decline'
AND meetings.reminder_time != -1
AND meetings_users.deleted != 1
AND meetings.status != 'Held'
AND date_start >= '2011-07-28 21:28:53'
AND date_start <= '2011-07-28 22:28:53'

which has the date start info but not for the cases. How can i fix this?

[waterfoul]

I still can't figure this out....

[rafael.q.g@hotmail.com]

If the field was included correctly on searchdefs (what looks that was) the query should works.
Once you was able to get the generated query, if you is able to find what is its problem, maybe you can extend the list.view.php to fix the where statment before process it.

But in your case, at least for this sql exemple, looks a little bit strange the field start_data have appeared twice:
AND date_start >= '2011-07-28 21:28:53'
AND date_start <= '2011-07-28 22:28:53'

Is possible a data is bigger and smaller than a value in the same time? Looks like this case will get the record just when the start date is equals the searched value, including the minutes and seconds.

May you have to fix the sql part to use a "between" sql statment instead of AND.

[waterfoul]

I'm having problems parsing your english but if I understand correctly you are having issues with the and clause. Even if that is incorrect (which doesn't matter as it is attached to the table of an unused module) I still can't get the date search to do anything. I know that the date fields were added correctly because they are default fields and the relevant section of the searchdefs is:

 'date_entered' => 
      array (
        'width' => '10%',
        'label' => 'LBL_DATE_ENTERED',
        'default' => true,
        'name' => 'date_entered',
      ),
      'date_modified' => 
      array (
        'type' => 'datetime',
        'label' => 'LBL_DATE_MODIFIED',
        'width' => '10%',
        'default' => true,
        'name' => 'date_modified',
      ), 
  

Which was added using the studio they show up in the advanced search as expected they just don't do anything. Also the " 'type' => 'datetime', " Doesn't seem to matter as both created and modified show up as the same selectbox/search options and neither do anything

[waterfoul]

I still can't figure this out.....

[jgrimaldos]

Any insight on this? I'm having exactly the same Problem.

[waterfoul]

sadly no........ I still can't figure it out......

[waterfoul]

FOUND IT!
It is a problem with the date info missing from modules\Cases\metadata\SearchFields.php which is fixed in teh most recent beta All I did is replace that file with the one from the beta archive and it worked. I attached it so everyone else doesn't have to go looking for it.

[jgrimaldos]

Thanks, tested and it worked like a charm.

[calebeskurdal]

Thanks for the bug fix! Just found this and used your fix.