CREATE TABLE IF NOT EXISTS tb_timecard (
timecard_id bigint(20) unsigned NOT NULL auto_increment,
timecard_user int(10) unsigned NOT NULL DEFAULT ” ,
timecard_ip varchar(16) NOT NULL DEFAULT ” ,
timecard_onduty datetime NOT NULL DEFAULT ” ,
timecard_offduty datetime NOT NULL DEFAULT ” ,
timecard_overtime datetime NOT NULL DEFAULT ” ,
timecard_memo tinytext ,
timecard_state tinyint(3) unsigned NOT NULL DEFAULT ‘0’ ,
PRIMARY KEY (timecard_id),
UNIQUE KEY timecard_id (timecard_id),
KEY timecard_id_2 (timecard_id),
KEY idx_datetime (timecard_offduty,timecard_onduty,timecard_overtime),
KEY idx_user (timecard_user)
);
<?php
$sql=”SELECT user_name,timecard_memo,timecard_ip,”;
$sql.=”CASE WHEN DATE_FORMAT(timecard_offduty,’%k:%i’)=’0:00′ “;
$sql.=”THEN “;
$sql.=”IF(DATE_FORMAT(timecard_overtime,’%k:%i’)<>’0:00′,TIME_FORMAT(TIMEDIFF(timecard_overtime,timecard_onduty),’%H:%i’),’00:00′) “;
$sql.=”ELSE TIME_FORMAT(TIMEDIFF(timecard_offduty,timecard_onduty),’%H:%i’) “;
$sql.=”END as t_worktime,”;
$sql.=”DATE_FORMAT(timecard_onduty,’%k:%i’) as t_onduty,”;
$sql.=”IF(DATE_FORMAT(timecard_offduty,’%k:%i’)<>’0:00′,DATE_FORMAT(timecard_offduty,’%k:%i’),”) as t_offduty,”;
$sql.=”IF(DATE_FORMAT(timecard_overtime,’%k:%i’)<>’0:00′,DATE_FORMAT(timecard_overtime,’%k:%i’),”) as t_overtime,”;
$sql.=”IF(DATE_FORMAT(timecard_overtime,’%k:%i’)<>’0:00′,TIME_FORMAT(TIMEDIFF(timecard_overtime,CONCAT(DATE_FORMAT(timecard_onduty,’%Y-%c-%d’),’ 18:00:00′)),’%H:%i’),”) as t_overtimeworktime,”;
$sql.=”DATE_FORMAT(timecard_onduty,’%Y-%m-%d %W’) as t_dateweek “;
$sql.=”FROM tb_timecard “;
$sql.=”INNER JOIN tb_user ON user_id=timecard_user “;
$sql.=”WHERE YEAR(NOW())=YEAR(timecard_onduty) and MONTH(timecard_onduty)=”.$_POST[‘select_month’].” and timecard_user=”.$_POST[‘select_user’].” “;
$sql.=”GROUP BY date_format(timecard_onduty,’%Y-%m-%d’) order by timecard_onduty”;
$result=mysql_query($sql);
while($row=mysql_fetch_array($result))
{
echo “总工作时间:”.$row[‘t_worktime’];
echo “上班考勤时间:”.$row[‘t_onduty’];
echo “下班考勤时间:”.$row[‘t_offduty’];
echo “加班时间:”.$row[‘t_overtimeworktime’];
}
?>