public function getStatisticsList($condition = array())
{
$data = array();
$employee = new Hra_Model_Employee();
$sql = $employee->select()->setIntegrityCheck(false)->from(array('t1' => $this->_dbprefix . 'employee'))->joinLeft(array('t2' => $this->_dbprefix . 'employee_dept'), "t2.id = t1.dept_id", array('dept_name' => 'name'))->joinLeft(array('t3' => $this->_dbprefix . 'employee_post'), "t3.id = t1.post_id", array('post_name' => 'name'))->joinLeft(array('t4' => $this->_dbprefix . 'user'), "t4.employee_id = t1.id", array('user_id' => 'id'))->joinLeft(array('t5' => $this->_dbprefix . 'employee_type'), "t1.employment_type = t5.id", array('employment_type' => 'name'))->where("(t1.leave_date is null or (t1.leave_date is not null and t1.leave_date >= '" . $condition['date_from'] . "' and t1.leave_date <= '" . $condition['date_to'] . "'))")->order(array("t1.employment_type", "t1.number"));
if ($condition['employment_type'] != '') {
$sql->where("employment_type = " . $condition['employment_type']);
}
if ($condition['key']) {
$sql->where("t1.ename like '" . $condition['key'] . "' or t1.cname like '" . $condition['key'] . "' or t1.number like '" . $condition['key'] . "'");
}
$total = $employee->fetchAll($sql)->count();
if ($condition['type'] != 'csv') {
$sql->limitPage($condition['page'], $condition['limit']);
}
$data = $employee->fetchAll($sql)->toArray();
$workday = new Hra_Model_Workday();
$vacation = new Hra_Model_Attendance_Vacation();
$overtime = new Hra_Model_Attendance_Overtime();
// 员工工作日设置（目前均按弹性员工工作日设置处理）
$workdaySetting = $workday->getDayQtyBase(1, 1, $condition['date_from'], $condition['date_to']);
$holidaySetting = $workday->getDayQtyBase(3, 1, $condition['date_from'], $condition['date_to']);
for ($i = 0; $i < count($data); $i++) {
$data[$i]['workday_qty'] = $workdaySetting['day'];
$data[$i]['workhour_qty'] = $workdaySetting['hour'];
$data[$i]['holiday_qty'] = $holidaySetting['day'];
$data[$i]['attendance_qty'] = null;
$data[$i]['attendance_hour_qty'] = 0;
$data[$i]['v_personal_qty'] = null;
$data[$i]['v_vacation_qty'] = null;
$data[$i]['v_sick_qty'] = null;
$data[$i]['v_marriage_qty'] = null;
$data[$i]['v_funeral_qty'] = null;
$data[$i]['v_childbirth_qty'] = null;
$data[$i]['v_childbirth_with_qty'] = null;
$data[$i]['v_other_qty'] = null;
$data[$i]['o_workday_qty'] = null;
$data[$i]['o_restday_qty'] = null;
$data[$i]['o_holiday_qty'] = null;
$data[$i]['late_qty'] = null;
$data[$i]['leave_early_qty'] = null;
$data[$i]['absence_halfday_qty'] = null;
$data[$i]['absence_qty'] = null;
if ($data[$i]['user_id']) {
// 出勤天数
$sql = $this->select()->from($this->_name)->where("number = '" . $data[$i]['number'] . "' and clock_in >= '" . $condition['date_from'] . " 00:00:00' and clock_out <= '" . $condition['date_to'] . " 23:59:59'")->group("date_format(clock_in, '%Y-%m-%d')");
$aData = $this->fetchAll($sql)->toArray();
foreach ($aData as $a) {
$data[$i]['attendance_hour_qty'] += $a['clock_hours'];
}
$data[$i]['attendance_qty'] = count($aData);
// 请假天数
$data[$i]['v_personal_qty'] = $vacation->getVacationQty($data[$i]['user_id'], 1, $condition['date_from'], $condition['date_to']);
$data[$i]['v_vacation_qty'] = $vacation->getVacationQty($data[$i]['user_id'], 2, $condition['date_from'], $condition['date_to']);
$data[$i]['v_sick_qty'] = $vacation->getVacationQty($data[$i]['user_id'], 3, $condition['date_from'], $condition['date_to']);
$data[$i]['v_marriage_qty'] = $vacation->getVacationQty($data[$i]['user_id'], 4, $condition['date_from'], $condition['date_to']);
$data[$i]['v_funeral_qty'] = $vacation->getVacationQty($data[$i]['user_id'], 5, $condition['date_from'], $condition['date_to']);
$data[$i]['v_childbirth_qty'] = $vacation->getVacationQty($data[$i]['user_id'], 6, $condition['date_from'], $condition['date_to']);
$data[$i]['v_childbirth_with_qty'] = $vacation->getVacationQty($data[$i]['user_id'], 7, $condition['date_from'], $condition['date_to']);
$data[$i]['v_other_qty'] = $vacation->getVacationQty($data[$i]['user_id'], 8, $condition['date_from'], $condition['date_to']);
// 加班天数
$data[$i]['o_workday_qty'] = $overtime->getOvertimeQty($data[$i]['user_id'], 1, $condition['date_from'], $condition['date_to']);
$data[$i]['o_restday_qty'] = $overtime->getOvertimeQty($data[$i]['user_id'], 2, $condition['date_from'], $condition['date_to']);
$data[$i]['o_holiday_qty'] = $overtime->getOvertimeQty($data[$i]['user_id'], 3, $condition['date_from'], $condition['date_to']);
//其它统计（迟到、早退、旷工次数）
$other = $this->getAbsenceQty($data[$i]['number'], $condition['date_from'], $condition['date_to']);
$data[$i]['late_qty'] = $other[1];
$data[$i]['leave_early_qty'] = $other[2];
$data[$i]['absence_halfday_qty'] = $other[3];
$data[$i]['absence_qty'] = $other[4];
}
}
if ($condition['type'] == 'csv') {
$data_csv = array();
$title = array('cnt' => '#', 'employment_type' => '用工形式', 'number' => '工号', 'cname' => '中文名', 'ename' => '英文名', 'dept_name' => '部门', 'post_name' => '职位', 'active' => '状态', 'attendance' => '出勤/考勤 [天]', 'holiday_qty' => '法定假日', 'v_personal_qty' => '事假', 'v_vacation_qty' => '年假', 'v_sick_qty' => '病假', 'v_marriage_qty' => '婚假', 'v_funeral_qty' => '丧假', 'v_childbirth_qty' => '产假', 'v_childbirth_with_qty' => '陪产假', 'v_other_qty' => '调休', 'o_workday_qty' => '工作日加班', 'o_restday_qty' => '休息日加班', 'o_holiday_qty' => '法定假日加班', 'late_qty' => '迟到', 'leave_early_qty' => '早退', 'absence_halfday_qty' => '旷工半天', 'absence_qty' => '旷工一天');
array_push($data_csv, $title);
$i = 0;
foreach ($data as $d) {
$i++;
$info = array('cnt' => $i, 'employment_type' => $d['employment_type'] == 0 ? '弹性' : '非弹性', 'number' => $d['number'], 'cname' => $d['cname'], 'ename' => $d['ename'], 'dept_name' => $d['dept_name'], 'post_name' => $d['post_name'], 'active' => $d['active'] == 1 ? '在职' : '离职', 'attendance' => $d['workday_qty'] . ' { ' . $d['attendance_qty'] . ' }', 'attendance_hours' => $d['workhour_qty'] . ' { ' . $d['attendance_hour_qty'] . ' }', 'holiday_qty' => $d['holiday_qty'], 'v_personal_qty' => $d['v_personal_qty'], 'v_vacation_qty' => $d['v_vacation_qty'], 'v_sick_qty' => $d['v_sick_qty'], 'v_marriage_qty' => $d['v_marriage_qty'], 'v_funeral_qty' => $d['v_funeral_qty'], 'v_childbirth_qty' => $d['v_childbirth_qty'], 'v_childbirth_with_qty' => $d['v_childbirth_with_qty'], 'v_other_qty' => $d['v_other_qty'], 'o_workday_qty' => $d['o_workday_qty'], 'o_restday_qty' => $d['o_restday_qty'], 'o_holiday_qty' => $d['o_holiday_qty'], 'late_qty' => $d['late_qty'], 'leave_early_qty' => $d['leave_early_qty'], 'absence_halfday_qty' => $d['absence_halfday_qty'], 'absence_qty' => $d['absence_qty']);
array_push($data_csv, $info);
}
return $data_csv;
}
return array('total' => $total, 'rows' => $data);
}
public function testAction()
{
$workday = new Hra_Model_Workday();
$workdaySetting = $workday->getDayQtyBase(1, 0, '2014-07-01', '2014-07-31');
echo '<pre>';
print_r($workdaySetting);
exit;
}