Leads by activity (reporting)

[qido]

Hello everyone,

yuri tanya We are having some troubles with reports, the issue is that report interface (list) always returns 0, please check the attached screenshots. I have also attached the AJAX response. For PHP I have hardcoded report type "List" and for testing purposes I was using "column" names as data. I noticed one interesting while looking for other report pages (with type = List), the ajax response contains 2 fields ("list" and "total"), maybe we need to use that structure instead?


Спасибо,
С уважением, Оганеес

[cfernandez]

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

[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]

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

[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]

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

[yuri]

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

[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]

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]

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?

[Patrick]

Thank you Tanya, I'm looking into it.

[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]

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]

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]

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