Assign Date Loup in Reports
9 posts by 2 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: February 8, 2013 (RSS)
By nmsinc - February 5, 2013
I'm having some trouble with setting a date loup for reporting data within a set date FROM and TO.
The code below returns a PHP error on the set funtions of $start, $end, $date - any help woulr be appreciated!
SELECT DATE_FORMAT(date_hours_worked, '%M %D %Y') as 'Work Date',
assinged_to_which_group as 'Member Company',
employee_name as 'Imployee Number',
hours_worked as 'Hours Worked',
payroll_accounting_group as 'Work Group'
FROM `<?php echo $TABLE_PREFIX ?>paysheet_hours`
$start = strtotime('2013-02-01');
$end = strtotime('2013-02-28');
$date = $start;
while($date < $end)
{
WHERE assinged_to_which_group = 176
GROUP BY date_hours_worked
ORDER BY YEAR(date_hours_worked) DESC, MONTH(date_hours_worked) DESC
}
Thanks
nmsinc
By gregThomas - February 5, 2013 - edited: February 5, 2013
Hi nmsinc.
What is the PHP error you getting?
It looks like the while loop you have is infinite, I can't see a way it can be exited, I think you might need to use an if statement instead.
Thanks!
Greg
PHP Programmer - interactivetools.com
By nmsinc - February 5, 2013
I have recoded using the where statment (see below) - this returns a PHP syntax error as follows:
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= strtotime('2013-02-01'); $end = strtotime('2013-02-28'); $date = $start; ' at line
SELECT DATE_FORMAT(date_hours_worked, '%M %D %Y') as 'Work Date',
assinged_to_which_group as 'Member Company',
employee_name as 'Imployee Number',
hours_worked as 'Hours Worked',
payroll_accounting_group as 'Work Group'
FROM `<?php echo $TABLE_PREFIX ?>paysheet_hours`
$start = strtotime('2013-02-01');
$end = strtotime('2013-02-28');
$date = $start
WHERE assinged_to_which_group = 176 AND date_hours_worked > $date AND date_hours_worked < $end
GROUP BY date_hours_worked
ORDER BY YEAR(date_hours_worked) DESC, MONTH(date_hours_worked) DESC
Hi,
I think the problem is your mixing both PHP and MYSQL code. What are the $start, $end and $date variables used for? Could you give me the entire pages code?
Thanks!
Greg
PHP Programmer - interactivetools.com
By nmsinc - February 5, 2013
Your right on that - I have changed the code as follows, however, the final reports all dates in the file and I only need the dates expressed in the strtotime!
SELECT DATE_FORMAT(date_hours_worked, '%M %D %Y') as 'Work Date',
assinged_to_which_group as 'Member Company',
employee_name as 'Imployee Number',
hours_worked as 'Hours Worked',
payroll_accounting_group as 'Work Group'
FROM `<?php echo $TABLE_PREFIX ?>paysheet_hours`
<?php
$start = strtotime('2013-02-10');
$end = strtotime('2013-02-28');
$timedate = strtotime('date_hours_worked');
?>
<?php if ($timedate > $start OR $timedate < $end): ?>
WHERE assinged_to_which_group = 176
GROUP BY date_hours_worked
ORDER BY YEAR(date_hours_worked) DESC, MONTH(date_hours_worked) DESC
<?php endif; ?>
Hi,
I think you need to add the dates to your where statement:
SELECT DATE_FORMAT(date_hours_worked, '%M %D %Y') as 'Work Date',
assinged_to_which_group as 'Member Company',
employee_name as 'Imployee Number',
hours_worked as 'Hours Worked',
payroll_accounting_group as 'Work Group'
FROM `<?php echo $TABLE_PREFIX ?>paysheet_hours`
<?php
$start = date("Y-m-d 00:00:00", strtotime('2013-02-10'));
$end = date("Y-m-d 00:00:00", strtotime('2013-02-28'));
$timedate = strtotime('date_hours_worked');
?>
<?php if ($timedate > $start OR $timedate < $end): ?>
WHERE assinged_to_which_group = 176 AND date_hours_worked > '<?php echo $start; ?>' AND date_hours_worked < '<?php echo $end; ?>'
GROUP BY date_hours_worked
ORDER BY YEAR(date_hours_worked) DESC, MONTH(date_hours_worked) DESC
<?php endif; ?>
The code will return all results between these two dates, so you might need to change it slightly if you want it to return items from just these two dates.
I'm not sure if date_hours_worked is the correct field to use in the where loop, so you might need to change that as well.
I've also used the date function to convert your strings into the correct date time format for MySQL.
Let me know if you have any questions.
Thanks!
Greg
PHP Programmer - interactivetools.com
By nmsinc - February 7, 2013
By the way, is there a way to code for the total 'hours-worked' and post it to the bottom of the output report page. This record field holds numeric values!
Hi,
There is no simple way to have a total field at the end of the report.
The simplest solution would be to have a second report that just returns a row of the total hours worked, but I'm not sure how useful that would actually be.
I can guide you through creating this report if you need.
Thanks!
Greg
PHP Programmer - interactivetools.com