Leads by activity (reporting)

[Patrick]

I try to make a report similar to the demo report "leads by activity",



in order to see who has not been contacted for 'a long time' (1 month) . I tried to edit the report on demo site to understand how to do it. however it does not show any editable parameters .. as shown by the following picture.

[Patrick]

this is what i tried to reproduce a similar report, but no avail.

[tanya]

The report also can be programmed. Leads by Last Activity report is defined in code. There are a path application/Espo/Modules/Advanced/Reports/LeadsByLastActivity.php , if you have Advanced Pack

[Patrick]

Hi Tanya,

Thank you for your prompt reply.

What are the recommended way of producing program-based report?

Will a custom-programmed report be 'overwritten' with each upgrade of advanced pack?

Thank you.

[tanya]

This report could be overwritten only in case of bug founding. Other reports, you want to create in such way will be safe.

If you want to create own internal report, you this one as example.
You can set it only through database (you can create through UI, but it needs modifications in db).
Look at this report (table report, id 001). The main columns are is_internal (set as 1), and internal_class_name.

The main code is in application/Espo/Modules/Advanced/Services/Report.php

[Patrick]

Thank you Tanya, I'm looking into it.

[cfernandez]

I am trying to create a custom report. I have added a record to reports table that has this data:
'011', 'Commsion Payment', '0', 'CommisionPaymentReport', 'Grid', NULL, '["id","agent_first_name","agent_last_name", "first_name", "last_name","application_id", "policy_number","effective_date","year", "status","premium_fee","subsidy","monthly_paym ent" , "plan_name", "carrier_name","received_on", "paid_on", "commission", "agent_commission","commission_for","calc_comm issi on"]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '#6FA8D6', '', '', NULL, '1', 'Advanced:CommisionPayment', '2014-11-05 14:43:16', '2014-11-05 14:43:16', '', NULL, NULL, NULL, NULL, NULL, '0', '1', '1', '1'

I have created a CommisionPayment.php and have added to application\Modules\Advance\CommissionPayment.php
***

I am not able to get anything to display, but I also do not get any errors. Did I miss anything?

[yuri]

Is there any error in server log (not data/logs)? I think something must be there. What is return by server (F12 > Network tab, run report to see response).

[cfernandez]

This is what I get
Uncaught TypeError: Cannot read property 'indexOf' of undefined
at ReportHelper.formatGroup (eval at _execute (http://dev.crm.clragroup.us/client/e...69099:16:26553), <anonymous>:41:20)
at n.eval (eval at _execute (http://dev.crm.clragroup.us/client/e...69099:16:26553), <anonymous>:53:127)
at Array.forEach (native)
at n.afterRender (eval at _execute (http://dev.crm.clragroup.us/client/e...69099:16:26553), <anonymous>:51:37)
at n._afterRender (http://dev.crm.clragroup.us/client/e...69099:16:10601)
at n.<anonymous> (http://dev.crm.clragroup.us/client/e...69099:16:10295)
at n.<anonymous> (http://dev.crm.clragroup.us/client/e...69099:16:14243)
at Bull.Templator.<anonymous> (http://dev.crm.clragroup.us/client/e...69099:16:21317)
at http://dev.crm.clragroup.us/client/e...69099:16:26702
at Array.forEach (native)
espo.min.js?r=1486112069099:2



if (~gr.indexOf('MONTH:')) {
return moment(value + '-01').format('MMM YYYY');
} else if (~gr.indexOf('DAY:')) {
return moment(value).format('MMM DD');
}


What I am not sure is what is return format for a List report?


$result = array( 'type' => 'List', 'groupBy' => $groupBy, 'columns' => $columns, 'columnsData'=> $columnsData, 'sums' => $sums, 'groupNameMap' => $groupNameMap, 'columnNameMap' => $columnNameMap, 'depth' => 1, 'grouping' => $grouping, 'reportData' => $reportData, 'entityType' => 'CommisionPaymentReport', ); return $result; here is my data:
$sth = $pdo->prepare($sql); $sth->execute(); $data = $sth->fetchAll(); foreach ($data as $row) { $id = $row["id"]; $resultData['id']= array( 'id' => $row['id'], 'agent_first_name' => $row['agent_first_name'], 'agent_last_name' => $row['agent_last_name'], 'first_name' => $row['first_name'], 'last_name' => $row['last_name'], 'application_id' => $row['application_id'], 'policy_number' => $row['policy_number'], 'effective_date' => $row['effective_date'], 'year' => $row['year'], 'status' => $row['status'], 'premium_fee' => $row['premium_fee'], 'subsidy' => $row['subsidy'], 'monthly_payment' => $row['monthly_payment'], 'plan_name' => $row['plan_name'], 'carrier_name' => $row['carrier_name'], 'received_on' => $row['received_on'], 'paid_on' => $row['paid_on'], 'commission' => $row['commission'], 'agent_commission' => $row['agent_commission'], 'commission_for' => $row['commission_for'], 'calc_commission' => $row['calc_commission'] ); } return $resultData;

[yuri]

You can try to create similar report through UI and then replicate return format in your custom report.

[cfernandez]

Thats how I got this far, for a list report which items are needed to be passed in?

[cfernandez]

What am trying to do is have the report be created from SQL statement, because you do not support the reporting on multiple objects. I am not sure if the data been passed back is in the correct format. I am not sure if am passing all the necessary parameters.

[cfernandez]

1st question, is this the right format or do I just pass back the $data variable?
foreach ($data as $row) { $id = $row["id"]; $resultData['id']= array( 'id' => $row['id'], 'agent_first_name' => $row['agent_first_name'], 'agent_last_name' => $row['agent_last_name'], 'first_name' => $row['first_name'], 'last_name' => $row['last_name'], 'application_id' => $row['application_id'], 'policy_number' => $row['policy_number'], 'effective_date' => $row['effective_date'], 'year' => $row['year'], 'status' => $row['status'], 'premium_fee' => $row['premium_fee'], 'subsidy' => $row['subsidy'], 'monthly_payment' => $row['monthly_payment'], 'plan_name' => $row['plan_name'], 'carrier_name' => $row['carrier_name'], 'received_on' => $row['received_on'], 'paid_on' => $row['paid_on'], 'commission' => $row['commission'], 'agent_commission' => $row['agent_commission'], 'commission_for' => $row['commission_for'], 'calc_commission' => $row['calc_commission'] ); } return $resultData; 2nd question, is this the right format to be passed back by the run method?
$result = array( 'type' => 'List', 'groupBy' => $groupBy, 'columns' => $columns, 'columnsData'=> $columnsData, 'sums' => $sums, 'groupNameMap' => $groupNameMap, 'columnNameMap' => $columnNameMap, 'depth' => 1, 'grouping' => $grouping, 'reportData' => $reportData, 'entityType' => 'CommisionPaymentReport', ); return $result; Thank you for your help

[tanya]

I am trying to create a custom report. I have added a record to reports table that has this data:

I have created a CommisionPayment.php and have added to application\Modules\Advance\CommissionPayment.php

application/Espo/Modules/Advanced/Reports/CommissionPayment.php - path of your report
CommisionPaymentReport - did you create this entity? If I remember well, 4-th column is target entity name

[cfernandez]

Yes, I created the entity and it has the same structure as the query which I am using.