Filtering/Reporting capabilities

lirit

Hey guys.

After having now my custom modules working I was wondering about some filtering and reporting capabilities.

1. Is it possible for the date of birth field, to filter based on it when year is greater than X (i.e: year is greater than 1980, then it would return all contacts records where their date of birth is equal or greater to 1980, day/month do not matter).

2. I'd like to customize further the contacts module, whereas I want to replace the City field with a custom selectbox with predefine cities which I can later filter by that. How do I do that?

3. Relation between Polisas and Contacts is many-to-one. For each contact I may have many Polisa records. Is it possible to have some kind of filter which is really for reporting which I can list all Contacts that have just 1 polisa? or all Contacts that don't have polisa of a certain type (based on a selectbox field).

4. A bit off-topic of the filtering - are there reporting capabilities in terms of showing graphs or general reporting like which user has the most records and other type of criteria like that?

I'm guessing that some of the filtering questions I listed requires some more low-level knowledge of grabbing all records, then looping over them to match a value and such. I just haven't had that experience yet with epesibim as all the CRUD modules I've done so far are based on some very typical usage which I could have learned by looking at other modules's source code or with the help of you guys to fine-tune my code.

Regards,
Liran.

ethnar

Hello,

The answer you (hopefully 😉) need:

1. If we're talking simply about crits - it's possible. Just specify crits like

$crits = array( (...), '>=birth_date'=>'1980-01-01', (...));

If we're talking about filter for end-user it's more tricky. You'll need what I've written here, plus what I say about custom_filters at the end of this post.

2. If enabling filtering is all you want - use custom filter. Again - details at the bottom of the post. If not - remove the old field (delete_record_field()) and add new one that is based on CommonData with 'filter'=>true.

3. That's custom filter again, read on past point 4.

4. There's a module Utils/RecordBrowser/Reports but it is a bit experimental and we do not have a non-commercial module that could serve as example. It has graphs using OpenFlashChart. The format is a bit different - usually having days/weeks/months/year as table header and each row is one record for which data is displayed.
If that's the kind of thing you'd need, I can prepare short doc of that module for you - would look good on wiki too. 😉

Now, for the custom filters... good news is all you mentioned can be served using this technique.. the bad - well, you'd need to modify CRM/Contacts body method. That, or remove from CRM_ContactsCommon menu entry for contacts and add one, with proper body(), in your custom module. The latter is easier when it comes to updating epesi - you just need to remove old menu entry again.

Now for the filters itself:
Let's take a look at (surprise 😉) Projects/Tickets module. You can find there:



		$sts = Utils_CommonDataCommon::get_translated_array('Premium_Ticket_Status',true);

		$trans = array('__NULL__'=>array(), '__ALLACTIVE__'=>Premium_Projects_TicketsCommon::active_tickets_crits());

		foreach ($sts as $k=>$v)

			$trans[$k] = array('status'=>$k);

		$this->rb->set_custom_filter('status',array('type'=>'select','label'=>$this->t('Ticket status'),'args'=>array('__NULL__'=>'['.$this->t('All').']','__ALLACTIVE__'=>'['.$this->t('All active').']')+$sts,'trans'=>$trans));

For the code to be more clear let me rephrase it:

		$sts = Utils_CommonDataCommon::get_translated_array('Premium_Ticket_Status',true);

		$options = array('__NULL__'=>'['.$this->t('All').']','__ALLACTIVE__'=>'['.$this->t('All active').']')+$sts;

		$translate_to_crits = array('__NULL__'=>array(), '__ALLACTIVE__'=>array('!status'=>4));

		foreach ($sts as $k=>$v)

			$translate_to_crits[$k] = array('status'=>$k);

		$definition = array('type'=>'select','label'=>$this->t('Ticket status'),'args'=>$options,'trans'=>$translate_to_crits);

		$this->rb->set_custom_filter('status',$definition);

So, what we are doing here is custom "HTML select" filter element.
First - I define what options that select should have - in this case it's two custom values (NULL and ALLACTIVE) and all statuses taken directly from CommonData table - please be advised that default is always __NULL__, it's kinda rough atm, please forgive me that. 😉

Next we create "dictionary" - set of rules that translate selected value (key) into crits (value).

Than I put that together in one neat variable and set the custom filter.

If you want to filter by cities, you can leave them as text fields and use Utils_RecordBrowserCommon::get_possible_values($tab, $field);
For another example, see this:



$cities = Utils_RecordBrowserCommon::get_possible_values('contact', 'city');

$options = array('__NULL__'=>'---');

$trans = array('__NULL__'=>array());

asort($cities);

foreach ($cities as $city) {

	$options[$city] = $city;

	$trans[$city] = array('city'=>$city);

}

$definition = array('type'=>'select','label'=>$this->t('City'),'args'=>$options,'trans'=>$trans);

$this->rb->set_custom_filter('city',$definition);

For this to work thou, you have to enable filtering by city:



Utils_RecordBrowserCommon::new_filter('contact', 'City'); // watch the capital C here

Kind regards,
Arek

lirit

That's actually quite some stuff to digest but I got it working with the Polisa Type,
meaning I've been able to define the parameters for set_custom_filter just fine,
ofcourse it results in the same thing as Utils_RecordBrowserCommon::new_filter('insurance_polisa', 'Polisa Type'); which is then completely pointless 🙂

But, I want to re-phrase points 1 and 3:
1. Regarding the date as a filter, I need it ofcourse to be user-controlled like the rest of the filters and not hard-coded and it needs to be with options like "this month", "this week" so that Polisa records have a date field. If that date happens to be in "this month" (as chosen in the date filter) then it'll show only those records.

3. For point 3, that's not custom filter as you said. I do not need to negate a filter, but rather do a more complex query. I have contacts which have one or many polisa's of different types. I want to get a report of all contacts which don't polisa of a certain type. For this I need to get_records for all contacts, then for each contact I need to do get_records of all polisa records and then run a check if any of these polisa type records is matched against what I'm searching for, if it isn't then that's the record I need and I keep it for later displaying. The results I need back is ofcourse just the customer details (name, phone, etc). This actually suits much my newer post which you said you'll refer to later on as it involves just doing some get_records stuff and I need help with showing those records finally. (I'll later obviously need to add some filters there as well).

ethnar

For the first point:

Still, custom_filters is the way to go, you just need to build crits right.
For instance, if you want option "this month" use

array('<=date_field'=>date('Y-m-t'), '>=date_field'=>date('Y-m-01'))

Notice the "t" instead of "d" in first date.
Or you can go all wild and just use:



array('~"date_field'=>DB::Concat(DB::qstr(date('Y-m-')), DB::qstr('%')));

🙂
Latter is more dangerous - be sure to know why DB::qstr is there and DB::Concat.

For the 3rd point: yes, that actually sounds more like a work for Reports..
Filter could kinda work, but by far it wouldn't be really good when it comes to performance. Unless you will go for some SQL queries - but then you need to watch it on epesi updates.
Now, you wonder if filters can do that in any feasible way without shifting through all records back and forth - there's a way.

Instead of 'trans', like in regular custom filter, you can use 'trans_callback'. It must point to a valid callback, which after filters submit will receive the value of the filter and must return crits.
This way, you could for instance use key 'none' for filter option that will show contacts without polisas and in that callback, when param is 'none', do SQL query (or some sophisticated get_records() - like picking all contacts and unset'ing those that you find in get_possible_values() table of polisas - but that's a bit of perf. hit) and return array('id'=>$id_pool)

To sum up - trans_callback simply moves the weight of building crits after the filter is submitted - means you need to build only one when submitted - and never without option selected. (and not for every option every time)

Hope this helps,
Arek

lirit

Regarding the city select box.
I've replaced the Contact's module city text field with my own commondata type as such:



		Utils_CommonDataCommon::new_array('Insurance_General_Cities',

			array('תל אביב', 'ירושלים', 'אחר'),true,true);

		Utils_RecordBrowserCommon::delete_record_field('contact', 'City');

		Utils_RecordBrowserCommon::new_record_field('contact', 

			array('name' => 'City', 'type' => 'commondata', 

				'param'=>array('order_by_key'=>false,'Insurance_General_Cities'),

				'extra' => false, 'position'=>'Address 2', 'required' => false, 'visible' => true,

				));

It does show these options in the select box but in the record browser (listing mode) it shows a number like 3 or 2, which I'm guessing is the array element position or such. I think that maybe this has something to do with the display_callback that City field was originally bind with but I'm not sure. If so, is there a way to reset it maybe?

(In reality, I've filled the array with about 1000 possible options. Would that have an affect in terms of performance? I might see something slow but it could be just a psychological effect).

Regards,
Liran.

ethnar

There's a method in RB Common unset_display_callback().

Of course adding like 1000 records will slow down the system. At some point were discussion whether to replace selects with too many options with an text (or autocomplete) input for filters. Thou we switched architecture around a bit and it was good enough to work on. If there are slowdowns caused by many CommonData entries in single array, you may consider disabling/altering the filter - such select is next to useless anyway.

lirit

Uhmm, there is a point in adding those select options, question is if there's a more efficient way to do it? the options aren't changing on a regular basis so I don't mind having it hardcoded or something but it's somehow needs to be a select option.

Thanks for the unset_display_callback() method, it's just the answer I need for my other thread on Contacts phone notation 🙂

Regards,
Liran.

lirit

Hey,

In continuation to this thread - we were discussing on how to filter date field (polisa_renew_date) such as:



		$options = array(

		   '__NULL__'=>'---',

		   '__last_week'=>'Last week',



		$trans = array(

		   '__NULL__'=>array(),

		   '__last_week'=>array('<=polisa_renew_date'=>date('Y-m-d'), '>=polisa_renew_date'=>date('Y-m-d', strtotime("-7 day"))),

Though, I need this to work a little different. I need to filter based on months, so I have changed the crits to something like:



		$options = array(

		   '__NULL__'=>'---',

		   '__january'=>'January',



		$trans = array(

		   '__NULL__'=>array(),

		   '__january'=>array('<=polisa_renew_date'=>date('Y-01-t'), '>=polisa_renew_date'=>date('Y-01-01')),

The problem with this code is that the filter by date will only work if the year in the polisa_renew_date and the current year are the same, otherwise it wouldn't return any results. The filter by month is supposed to be non-specific to a year.

Hence I'm asking, is it possible to format the date that the 'date' type of field is using?

Regards,
Liran.

ethnar

Hi,

What you need is:

'~"polisa_renew_date'=>DB::Concat(DB::qstr('%'), DB::qstr('-01-'), DB::qstr('%'))

Kind regards,
Arek

lirit

Hey Arek,

Thanks, that indeed works.
It's the other way you once told me about which relates to the database doing the string matching,
I just remember that you told me I need to watch out for some stuff when working with that...

Regards,
Liran.

Epesi ENS | Epesi Academy | epesi.cloud PaaS | GitHub

Help Me! | Download from SourceForge