Problem displaying employee timesheet in one row

sondavey · Post by **sondavey** » Thu Aug 01, 2013 12:21 pm

Hi There

I need help wrt to my sql query that I am using to display employees timesheet information, as I would like the info (ie time in and time out from sunday to saturday)to display in one row per employee. At the moment it displays numerous rows for an employee with the different time in and time out times from Sunday to saturday in different rows.For this example you can assume the Job name is the same for all employees and the time period is from sunday to saturday ie 7 days.

This is the sql:

$ts_timesheet = $wpdb->get_results("SELECT wp_wpts.ID, wp_wpts.ts_author, date_format(wp_wpts.ts_time_in,'%M %e, %Y'), wp_wpts.ts_job_name, wp_wpts.ts_description, date_format(wp_wpts.ts_time_in, '%h:%i %p'), date_format(wp_wpts.ts_time_out, '%h:%i %p'), SEC_TO_TIME(TIMESTAMPDIFF(SECOND,wp_wpts.ts_time_in,ts_time_out)),wp_usermeta.meta_value, TIMESTAMPDIFF(SECOND,wp_wpts.ts_time_in,ts_time_out)/3600*wp_usermeta.meta_value FROM wp_wpts, wp_usermeta, wp_users WHERE wp_wpts.ts_time_in >= '".$ts_d1."' AND wp_wpts.ts_time_out <= '".$ts_d2."' AND wp_usermeta.user_id = wp_wpts.ts_author AND wp_usermeta.meta_key ='Bill_Rates' AND wp_users.ID=wp_wpts.ts_author ORDER BY wp_users.user_login ASC,wp_wpts.ts_time_in", ARRAY_N);

And display in html is:
<?php
for ($i = 0; $i < sizeof($ts_timesheet); $i++) {
if ($i&1) {$alternate = 'alternate';} else {$alternate = '';}
$curauth = get_userdata($ts_timesheet[$i][1]);
?>
<tr class="iedit <?php echo $alternate;?> <?php echo $complete;?>">
<td class="date column-date">
<?php
//display Employee Name
echo $curauth->user_login?></td>
<td class="date column-date"><?php
//display Job Name
echo $ts_timesheet[$i][3]?></td>
<td class="date column-date"><?php
//display Job Description
echo $ts_timesheet[$i][4]?>
<div class="row-actions" style="display:inline; margin-left:10px;"><span class='add'>

<a href="?page=wpts-new.php&doaction=Add&date=<?php echo date('Y-m-d',strtotime($ts_timesheet[$i][2]))?>&ts_author=<?php echo $curauth->ID?>" title="Add">Add</a></span>
<?php if (current_user_can('manage_options')) { ?> | <span class='edit'><a href="?page=wpts-new.php&doaction=Edit&ID=<?php echo $ts_timesheet[$i][0]?>" title="Edit">Edit</a></span><?php } ?></div>
</td>
<td class="date column-date">
<?php
//display Time in for Saturday
$Day = date('l', strtotime( $ts_timesheet[$i][2]));
if ($Day =="Saturday"){
echo $ts_timesheet[$i][5];
}//end if
?></td>
<td class="date column-date">
<?php
//display Time out for Saturday
if ($Day =="Saturday"){
echo $ts_timesheet[$i][6];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Sunday"){
echo $ts_timesheet[$i][5];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Sunday"){
echo $ts_timesheet[$i][6];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Monday"){
echo $ts_timesheet[$i][5];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Monday"){
echo $ts_timesheet[$i][6];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Tuesday"){
echo $ts_timesheet[$i][5];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Tuesday"){
echo $ts_timesheet[$i][6];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Wednesday"){
echo $ts_timesheet[$i][5];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Wednesday"){
echo $ts_timesheet[$i][6];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Thursday"){
echo $ts_timesheet[$i][5];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Thursday"){
echo $ts_timesheet[$i][6];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Friday"){
echo $ts_timesheet[$i][5];
}//end if
?></td>
<td class="date column-date">
<?php
if ($Day =="Friday"){
echo $ts_timesheet[$i][6];
}//end if
?></td>
<td class="date column-date"><?php
//display Total Hours employee worked
echo substr($ts_timesheet[$i][7], 0, -3)?></td>
<td class="date column-date"><?php
//display hourly rate of employee
echo $ts_timesheet[$i][8]?></td>
<td class="date column-date">R <?php
//display Total amount to be paid to employee for that week
echo $ts_timesheet[$i][9]?></td>
</tr>
<?php
}//end for loop
?>