2. PHPRunner General questions
  3. [SOLVED]  Parameters for a report
This topic is locked
[SOLVED]

 Parameters for a report

 		10/5/2011 3:14:15 PM
PHPRunner General questions
S
 Stucco author

I have a report that the client would like to be configurable with a parameter. Ideally, when they execute the report they are able to specify a date, and that value is available to me for the SQL powering the report. I'd like to avoid saving the value elsewhere and then running the report.
Thank you
C
 		cgphp 10/5/2011

You can filter the report using the "Report page: Before SQL query" event. The param, to filter the report, could be a value submitted from the user from a custom date form.
S
 		Stucco author 10/9/2011



You can filter the report using the "Report page: Before SQL query" event. The param, to filter the report, could be a value submitted from the user from a custom date form.


I need to put the value collected from the user into both the select and the where clause of the query. It appears I only have access to the where portion of the query ($strWhereClause).
This is the query I need to execute with the $date_parameter filled in with data from the user in a format like 'YYYY-MM-DD hh:mm:ss'.
The idea is that it runs a report coming up with a start and end time for a shipment. It will then split the window on the date_parameter, giving the number of hours in the first part (monitored_hours_before), second part(monitored_hours_after), and total (monitored_hours). I do not see how to embed the user provided value into the select portion of the query.



SELECT c.name, s.shipment_id, l_min.`time` as first_time, oa_min.`action` as first_action, l_max.`time` as last_time, oa_max.`action` as last_action, $date_parameter as cut_time,

TIMESTAMPDIFF(MINUTE, l_min.`time`, l_max.`time`)/60 as monitored_hours, TIMESTAMPDIFF(MINUTE, $date_parameter, l_max.`time`)/60 as monitored_hours_before, TIMESTAMPDIFF(MINUTE, l_min.`time`, $date_parameter)/60 as monitored_hours_after FROM shipments s, customers c, `logs` l_min, options_action oa_min, `logs` l_max, options_action oa_max, (

SELECT l.shipment_id, max( l.`time` ) as max_time, min( l.`time` ) as min_time

FROM `logs` l, options_action oa

WHERE l.action = oa.id

AND oa.active =1

GROUP BY l.shipment_id

) l_all

WHERE l_all.shipment_id = s.id

AND c.id = s.customer_id

AND l_min.`time` = l_all.min_time

AND l_min.shipment_id = l_all.shipment_id

AND l_min.`action` = oa_min.id

AND l_max.`time` = l_all.max_time

AND l_max.shipment_id = l_all.shipment_id

AND l_max.`action` = oa_max.id

AND l_min.`time` <= $date_parameter

AND l_max.`time` >= $date_parameter

ORDER BY monitored_hours DESC;
Sergey Kornilov admin 10/9/2011

You may want to check 'List page: Custom Query' event:

http://xlinesoft.com/phprunner/docs/custom_query.htm
In this event you can implement your own data retrieval procedure. Here is the sample code.

global $conn;

$rs=db_query($mySQLQuery,$conn);

return $rs;


PS. This event only works on the List page.
S
 		Stucco author 10/16/2011

I appreciate the pointer to the custom query event, however this is for a report and that option is not accessible there. How would I accomplish this functionality on a report? Can I run my custom query and completely replace the results?
S
 		Stucco author 10/23/2011



I appreciate the pointer to the custom query event, however this is for a report and that option is not accessible there. How would I accomplish this functionality on a report? Can I run my custom query and completely replace the results?


I have attempted to create this report as a listing page instead using the custom query. This is what I have so far:



function ListQuery($searchObj,$orderBy,$howOrderBy,$masterTable,$masterKeysReq,$selectedRecord,$pageSize,$myPage)

{

global $conn;

global $date_split_parameter;

$date_split_parameter = '2009-04-21 00:00:00';
$offset = $pageSize * ($myPage - 1);

$count = $pageSize;
$mySQLQuery = "SELECT c.name, s.shipment_id, l_min.`time` as first_time, oa_min.`action` as first_action, l_max.`time` as last_time, oa_max.`action` as last_action, '$date_split_parameter' as cut_time,

TIMESTAMPDIFF(MINUTE, l_min.`time`, l_max.`time`)/60 as monitored_hours, TIMESTAMPDIFF(MINUTE, '$date_split_parameter', l_max.`time`)/60 as monitored_hours_before, TIMESTAMPDIFF(MINUTE, l_min.`time`, '$date_split_parameter')/60 as monitored_hours_after FROM shipments s, customers c, `logs` l_min, options_action oa_min, `logs` l_max, options_action oa_max, (

SELECT l.shipment_id, max( l.`time` ) as max_time, min( l.`time` ) as min_time

FROM `logs` l, options_action oa

WHERE l.action = oa.id

AND oa.active =1

GROUP BY l.shipment_id

) l_all

WHERE l_all.shipment_id = s.id

AND c.id = s.customer_id

AND l_min.`time` = l_all.min_time

AND l_min.shipment_id = l_all.shipment_id

AND l_min.`action` = oa_min.id

AND l_max.`time` = l_all.max_time

AND l_max.shipment_id = l_all.shipment_id

AND l_max.`action` = oa_max.id

AND l_min.`time` <= '$date_split_parameter'

AND l_max.`time` >= '$date_split_parameter'

ORDER BY monitored_hours DESC LIMIT $offset, $count;";
$rs=db_query($mySQLQuery,$conn);

return $rs;

}


This code returns the correct results, but has the following problems:

-On attempted export, it returns a PHP error: Undefined variable: masterKeysReq

-The count indicates a number that is greater than the actual number of records returned. There are several pages to select with no results on them. It appears to base the count on the originally set SQL statement, not the custom one that I created.
Sergey Kornilov admin 10/24/2011

Need more info on Export issue. You can post your application to Demo Account and open a ticket at http://support.xlinesoft.com sending your Demo Account URL. 'Demo Account' button can be found on the last screen in the program.
In regards to record count - you will need to implement 'List page: Get Row Count' event for this purpose.
S
 		Stucco author 11/7/2011

I ended up just creating a table to store the report parameter. I do no allow them to add rows to that table. I then use the following query to run the report. This works well in the existing report structure, with exports, filters, etc.
This way I did not need any custom query business or anything, just some complicated joins.



SELECT

c.name,

s.shipment_id,

l_min.`time` as first_time,

oa_min.`action` as first_action,

l_max.`time` as last_time,

oa_max.`action` as last_action,

STR_TO_DATE(CONCAT(oc.date, ' ',oc.time), '%Y-%m-%d %H:%i:%s') as cut_time,

TIMESTAMPDIFF(MINUTE, l_min.`time`, l_max.`time`)/60 as monitored_hours,

TIMESTAMPDIFF(MINUTE, STR_TO_DATE(CONCAT(oc.date, ' ',oc.time), '%Y-%m-%d %H:%i:%s'), l_max.`time`)/60 as monitored_hours_before,

TIMESTAMPDIFF(MINUTE, l_min.`time`, STR_TO_DATE(CONCAT(oc.date, ' ',oc.time), '%Y-%m-%d %H:%i:%s'))/60 as monitored_hours_after

FROM

shipments s,

customers c,

`logs` l_min,

options_action oa_min,

`logs` l_max,

options_action oa_max,

options_cutoff oc,

(

SELECT

l.shipment_id,

max( l.`time` ) as max_time,

min( l.`time` ) as min_time

FROM

`logs` l,

options_action oa

WHERE

l.action = oa.id

AND oa.active =1

GROUP BY

l.shipment_id

) l_all

WHERE

l_all.shipment_id = s.id

AND c.id = s.customer_id

AND l_min.`time` = l_all.min_time

AND l_min.shipment_id = l_all.shipment_id

AND l_min.`action` = oa_min.id

AND l_max.`time` = l_all.max_time

AND l_max.shipment_id = l_all.shipment_id

AND l_max.`action` = oa_max.id