/**
*
* @param type $employeeIds
* @param type $dateFrom
* @param type $dateTo
* @param type $subDivision
* @param type $employeementStatus
* @return type array
*/
public function searchTimesheetItems($employeeIds = null, $employeementStatus = null, $supervisorIds = null, $subDivision = null, $dateFrom = null, $dateTo = null)
{
$q = Doctrine_Query::create()->select("e.emp_middle_name, e.termination_id , e.emp_lastname, e.emp_firstname, i.date, cust.name, prj.name, act.name, i.comment, SUM(i.duration) AS total_duration ")->from("ProjectActivity act")->leftJoin("act.Project prj")->leftJoin("prj.Customer cust")->leftJoin("act.TimesheetItem i")->leftJoin("i.Employee e");
$q->where("act.activity_id = i.activity_id ");
if ($employeeIds != null) {
if (is_array($employeeIds)) {
$q->whereIn("e.emp_number", $employeeIds);
} else {
$q->andWhere(" e.emp_number = ?", $employeeIds);
}
}
if (is_array($supervisorIds) && sizeof($supervisorIds) > 0) {
$q->whereIn("e.emp_number", $supervisorIds);
}
if ($employeementStatus > 0) {
$q->andWhere("e.emp_status = ?", $employeementStatus);
} else {
if ($employeeIds <= 0) {
$q->andWhere("(e.termination_id IS NULL)");
}
}
if ($subDivision > 0) {
$companyService = new CompanyStructureService();
$subDivisions = $companyService->getCompanyStructureDao()->getSubunitById($subDivision);
$subUnitIds = array($subDivision);
if (!empty($subDivisions)) {
$descendents = $subDivisions->getNode()->getDescendants();
foreach ($descendents as $descendent) {
$subUnitIds[] = $descendent->id;
}
}
$q->andWhereIn("e.work_station", $subUnitIds);
}
if ($dateFrom != null) {
$q->andWhere("i.date >=?", $dateFrom);
}
if ($dateTo != null) {
$q->andWhere("i.date <=?", $dateTo);
}
$q->groupBy("e.emp_number, i.date, act.activity_id");
$q->orderBy("e.lastName ASC, i.date DESC, cust.name, act.name ASC ");
$result = $q->execute(array(), Doctrine::HYDRATE_SCALAR);
return $result;
}
/**
*
* @param int $employeeId
* @param string $employeementStatus
* @param int $subDivision
* @param date $dateFrom
* @param date $dateTo
* @return array
*/
public function searchAttendanceRecords($employeeIds = null, $employeementStatus = null, $subDivision = null, $dateFrom = null, $dateTo = null)
{
$q = Doctrine_Query::create()->select("e.emp_number, e.termination_id, e.emp_firstname, e.emp_middle_name, e.emp_lastname, a.punch_in_user_time as in_date_time, a.punch_out_user_time as out_date_time, punch_in_note, punch_out_note, TIMESTAMPDIFF(MINUTE, a.punch_in_user_time, a.punch_out_user_time) as duration")->from("AttendanceRecord a")->leftJoin("a.Employee e")->orderBy('a.punch_in_user_time DESC');
if ($employeeIds != null) {
if (is_array($employeeIds)) {
$q->andWhereIn("e.emp_number", $employeeIds);
} else {
$q->andWhere(" e.emp_number = ?", $employeeIds);
}
}
if ($employeementStatus != null) {
$q->andWhere("e.emp_status = ?", $employeementStatus);
} else {
if ($employeeIds <= 0) {
$q->andWhere("(e.termination_id IS NULL)");
}
}
if ($subDivision > 0) {
$companyService = new CompanyStructureService();
$subDivisions = $companyService->getCompanyStructureDao()->getSubunitById($subDivision);
$subUnitIds = array($subDivision);
if (!empty($subDivisions)) {
$descendents = $subDivisions->getNode()->getDescendants();
foreach ($descendents as $descendent) {
$subUnitIds[] = $descendent->id;
}
}
$q->andWhereIn("e.work_station", $subUnitIds);
}
if ($dateFrom != null) {
$q->andWhere("a.punch_in_user_time >=?", $dateFrom);
}
if ($dateTo != null) {
$q->andWhere("a.punch_out_user_time <=?", $dateTo);
}
$result = $q->execute(array(), Doctrine::HYDRATE_SCALAR);
return $result;
}
