|
P
p1nr author
I am developing a work schedule web app for my company. I am able to get the proper values and use cases to show up correctly on PHPrunner, but it breaks when I filter (between) dates.
Here's an example:
SELECT Schedule.techid, concat( ifnull(lastname,''), ', ',ifnull(firstname,''), ' ', ifnull(aliasname,'')) as name,
min(Schedule.area) as area,
ifnull(`weekday`,'') as `weekday`, `date`,
max(ifnull(d1,'')) as d1, max(ifnull(d2,'')) as d2, max(ifnull(d3,'')) as d3, max(ifnull(d4,'')) as d4, max(ifnull(d5,'')) as d5
FROM (
SELECT vcschedule.techid, vcschedule.`weekday`, vcschedule.`date`, supervisors.area AS area,
CASE WHEN day(vcschedule.date) = 1 THEN vcschedule.status END AS d1
FROM vcschedule
INNER JOIN vcemployees on vcschedule.techid = vcemployees.techid
INNER JOIN (select techid as supervisor, area from vcemployees where egroup = '2') as Supervisors on Supervisors.supervisor = vcemployees.esupervisor
WHERE vcschedule.`date` >= '2013-03-01' AND vcschedule.`date` <= '2013-03-31'
GROUP BY vcschedule.techid,day(vcschedule.date)
) AS Schedule
INNER JOIN vcemployees on vcemployees.techid = Schedule.techid
GROUP BY Schedule.techid
Basically, I need PHPrunner to apply the date filter parameter to the sub query and not outside of it.
The parameter would replace the hard coded filter that I put in the script:
WHERE vcschedule.`date` >= '2013-03-01' AND vcschedule.`date` <= '2013-03-31'
Does this make sense? Can anyone help?
|
|