我有一个用于生成文档（Excel）的ReportBuilder类。我想要的是选择员工和日期期间，然后计算该日期期间的总工作小时。这是我的php文件
<?php
class EmployeeSummaryTimesheet extends ReportBuilder{
public function getMainQuery(){
$query = "SELECT
(SELECT `employee_id` from Employees where id = at.employee) as 'Employee',
(SELECT concat(`first_name`,' ',`middle_name`,' ', `last_name`) from Employees where id = at.employee) as 'Employee',
TIMEDIFF(out_time, in_time) as 'Working Hours'
FROM Attendance at";
return $query;
}
public function getWhereQuery($request){
if(($request['employee'] != "NULL" && !empty($request['employee']))){
$query = "where employee = ? and in_time >= ? and out_time <= ? group by employee;";
$params = array(
$request['employee'],
$request['date_start']." 00:00:00",
$request['date_end']." 23:59:59",
);
}else{
$query = "where in_time >= ? and out_time <= ? group by employee;";
$params = array(
$request['date_start']." 00:00:00",
$request['date_end']." 23:59:59",
);
}
return array($query, $params);
}}
现在我无法获得第一天的特定日期段的总和TIMEDIFF（out_time，in_time）值，只是工作时间的总和不能帮助我
EDit-这里有两个表Employee和Attendance（id，Employee（这是Employees表中的员工id），in_time，out_time）
编辑：我将DATE_FORMAT（in_time，'%Y-%m-%d'）取为"日期"，我不需要此列
您希望每个员工和日期都有一条记录，并显示该员工当天的总工作时间。因此，按员工和日期加入并分组，并计算工作时间。
select
e.employee_id,
concat(e.first_name, ' ', e.middle_name, ' ', e.last_name) as employee_name
date_format(at.in_time, '%Y-%m-%d') as 'Date',
sum(timediff(at.out_time, at.in_time)) as 'Working Hours'
from attendance at
join employee e on e.id = at.employee
where ...
group by e.employee_id, date_format(at.in_time, '%Y-%m-%d');
这是我自己做的答案
public function getMainQuery(){
$query = "SELECT
(SELECT `employee_id` from Employees where id = at.employee) as 'Employee',
(SELECT concat(`first_name`,' ',`middle_name`,' ', `last_name`) from Employees where id = at.employee) as 'Employee',
DATE_FORMAT(in_time, '%Y-%m') as 'MONTH',
CONCAT(
FLOOR(
SUM(CASE WHEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))<=0 THEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))=''
ELSE TIME_TO_SEC(TIMEDIFF(out_time,in_time)) END)/3600) ,':',
FLOOR(MOD(
SUM(CASE WHEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))<=0 THEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))=''
ELSE TIME_TO_SEC(TIMEDIFF(out_time,in_time)) END),3600)/60)) as 'WORKING HOURS'
FROM Attendance at";
return $query;
}
public function getWhereQuery($request){
if(($request['employee'] != "NULL" && !empty($request['employee']))){
$query = "where employee = ? and in_time >= ? and out_time <= ? order by in_time;";
$params = array(
$request['employee'],
$request['date_start']." 00:00:00",
$request['date_end']." 23:59:59",
);
}else{
$query = "where in_time >= ? and out_time <= ? group by employee;";
$params = array(
$request['date_start']." 00:00:00",
$request['date_end']." 23:59:59",
);
}
return array($query, $params);
}
}