Hi guys,

 

Im having a little trouble here, Im trying to query a DB to gather total hours worked for a month period but split into individual weeks to export to an excel spreadsheet with 4/5 weekly columns.

 

This is the query i have but im not sure how i should be using the result.

 

$sql = "SELECT user_id, name
, sum(hours_worked) AS sum_hours
, location, sum(lunch) AS sum_lunch
, sum(break) AS sum_break
, sick, holiday
, clock_status.pay_rate AS pay_rate 
, amend_time BETWEEN '$period1' AS week1
, amend_time BETWEEN '$period2' AS week2
, amend_time BETWEEN '$period3' AS week3
, amend_time BETWEEN '$period4' AS week4
FROM clock_status , staff                
WHERE auth = 1 and clock_status.user_id = staff.staff_number
and clock_status.location = '3301'
GROUP BY user_id, week1, week2, week3, week4, pay_rate ";  

 

and this is the while loop:

while($d = mysql_fetch_array($query)){
$id = $row-1;

$objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $d['user_id']);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $d['name']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$row, '=I'.$row);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $row, $rate);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$row, '=IF(F'.$row.'='.$low.',0.53,0)+IF(F'.$row.'='.$high.',0.64,0)');
$objPHPExcel->getActiveSheet()->setCellValue('H'.$row, '=SUM(E'.$row.'*F'.$row.')+E'.$row.'*G'.$row.'');
$objPHPExcel->getActiveSheet()->setCellValue('I' . $row, $d['sum_hours']);
$objPHPExcel->getActiveSheet()->setCellValue('J' . $row, $d['sum_hours']);
$objPHPExcel->getActiveSheet()->setCellValue('K' . $row, $d['sum_hours']);
$objPHPExcel->getActiveSheet()->setCellValue('L' . $row, $d['sum_hours']);
$row++;
}

 

Its the weeks 1-4 and the columns i,j,k,l, im stuck on, how do i get the information for the weeks into those columns??

Edited June 27, 201115 yr by ma_anse

Thanks for the reply, however i think you have missed my point entirely, i have the required classes to create the spread sheet, and can create a spreadsheet no problem at all.. What i am struggling with is how to get the required data into the spreadsheet. I can get it to export a single weeks data but not multiple weeks and still sort it in the same way.

 

My spread sheet is to look something like this....

 

Name-------Total Hours-------Rate------Week1-------week2-----week3------week4------week5

john-------------8----------------4.92----------2---------------1-------------2--------------2--------------2

john-------------12---------------5.62----------3---------------2-------------3--------------2--------------2

sarah------------5----------------4.92----------1---------------1-------------1--------------1--------------1

 

i have the php to give me the required date ranges but im not sure how to structure the query and how to use the result set to create this.