The full procedure is as follows:
DROP PROCEDURE IF EXISTS sp_pt_4_action_list
$$
CREATE PROCEDURE `sp_pt_4_action_list`(param_groupownercompanyid INT, param_selectedcompanyid INT, param_actionstatus VARCHAR(100), param_actiontype VARCHAR(100), param_actionpriority VARCHAR(100), param_dayssinceactionraised INT, param_yearssinceactionraised INT, param_allocateid VARCHAR(100), param_locationid VARCHAR(100))
BEGIN
/* Report drill throughs */
/* Report general styling */
select 'dbr.headerstyle', mydbr_style('tableheaderstyle');
select 'dbr.resultclass', 'myborder';
select 'dbr.colstyle', 'actionid', mydbr_style('center column data');
/* Report Title */
select 'dbr.title', 'R4: Actions/Recommendations';
select 'dbr.export.options', 'header.title', 'R4: Actions/Recommendations';
/* Report specific commands and styling */
select 'dbr.rowclass', 'actionclass';
/* Report hidden columns */
select 'dbr.hidecolumn', 'actionclass';
select 'dbr.hidecolumn', 'actionentryid';
select 'dbr.hidecolumn', 'actionpriority';
select 'dbr.hidecolumn', 'actiontype';
/* Report SQL */
SELECT
IF(actionpriority = 'High','redclass', IF(actionpriority = 'Medium','orangeclass','greenclass')) AS 'actionclass',
(
SELECT
companyname
FROM
f42910_flytrend2.company
WHERE
companyid = member.companymemberid
) AS 'Facility [sitename]',
(
SELECT
CAST(
CASE actiontype
WHEN 'trendaction' THEN CONCAT('TR',actionnumber)
WHEN 'groupaction' THEN CONCAT('Grp',actionnumber)
WHEN 'generalaction' THEN CONCAT('Gen',actionnumber)
WHEN 'contractaction' THEN CONCAT('Crt',actionnumber)
WHEN 'recommendation' THEN CONCAT('Rec',actionnumber)
WHEN 'auditaction' THEN CONCAT('Aud',actionnumber)
END
AS CHAR)
) AS 'Action id [actionid]',
dateactionraised AS 'Date action raised[dateactionraised]',
entrytext AS 'Description[entrytext]',
(
SELECT
CASE actionentry.locationid
WHEN 0 THEN ('All Locations')
ELSE (SELECT locationname FROM f42910_flytrend2.location WHERE locationid=actionentry.locationid)
END
)AS 'Location[actionlocationname]',
SUBSTRING(
( SELECT
username
FROM
f42910_flytrend2.users
WHERE
userid =allocateid
),1,30)AS 'Current Action Owner[ownerusername]',
actionstatus AS 'Action Status[actionstatus]',
inspectionresults AS 'Action Update[inspectionresults]',
CAST(ifnull(actionverificationdate, 'Open') as char) AS 'Action Close Date[actionverificationdate]',
IFNULL(SUBSTRING(
( SELECT
username
FROM
f42910_flytrend2.users
WHERE
userid =actionverifiedby
),1,30), 'Open') AS 'Action Closed By[actionverifiedby]',
actionentryid,
actionpriority,
actiontype
FROM
f42910_flytrend2.actionentry
LEFT JOIN f42910_flytrend2.efk
ON efk.efkid=actionentry.efkid
LEFT JOIN f42910_flytrend2.location
ON efk.locationid = location.locationid
INNER JOIN
(
SELECT companymemberid
FROM f42910_flytrend2.company
INNER JOIN f42910_flytrend2.corporategroupdef
ON companyid = groupownercompanyid
INNER JOIN f42910_flytrend2.groupmemberlink
ON corporategroupdef.corporategroupdefid = groupmemberlink.corporategroupdefid
WHERE
(corporategroupdef.groupownercompanyid = param_groupownercompanyid
OR corporategroupdef.groupclientcompanyid = param_selectedcompanyid)
AND membershipstate = 'Approved'
UNION
SELECT param_selectedcompanyid AS companymemberid
) AS member
ON member.companymemberid = actionentry.companyid
WHERE
actionentry.companyid LIKE IF(param_selectedcompanyid='all', '%', param_selectedcompanyid)
AND
(
CASE param_actionstatus
WHEN 'Open' THEN actionentry.actionstatus <>'Closed'
WHEN 'Closed' THEN actionentry.actionstatus = 'Closed'
WHEN 'all' THEN (actionentry.actionstatus='Closed' OR actionentry.actionstatus<>'Closed')
WHEN 'Unallocated' THEN (actionentry.actionstatus='Open' OR actionentry.actionstatus='Awaiting Allocation')
WHEN 'nonupdateclosed' THEN (actionentry.actionstatus='Closed' AND actionentry.inspectionresults='No updates')
ELSE actionentry.actionstatus=param_actionstatus
END
)
AND
(
CASE param_actiontype
WHEN 'all' THEN actionentry.actiontype <> 'all'
WHEN 'norec' THEN actionentry.actiontype <> 'recommendation'
ELSE actionentry.actiontype=param_actiontype
END
)
AND
(
CASE param_actionpriority
WHEN 'all' THEN actionentry.actionpriority <> 'all'
ELSE actionentry.actionpriority=param_actionpriority
END
)
AND dateactionraised > (CURDATE()- INTERVAL param_dayssinceactionraised DAY)
AND dateactionraised > (CURDATE()- INTERVAL param_yearssinceactionraised YEAR)
AND actionentry.allocateid LIKE IF(param_allocateid='all', '%', param_allocateid)
AND actionentry.locationid LIKE IF(param_locationid='all', '%', param_locationid)
ORDER BY
'[sitename]', dateactionraised, actionentry.efkid, actionentryid;
END
$$
