function getShiftPunchesByUserIDAndEpochAndArrayCriteria($user_id, $epoch, $filter_data, $punch_control_id = 0, $maximum_shift_time = NULL) { if ($user_id == '') { return FALSE; } if ($epoch == '') { return FALSE; } if ($maximum_shift_time == '') { $maximum_shift_time = $this->getPayPeriodMaximumShiftTime($user_id, $maximum_shift_time); } //Make sure that we get all punches surrounding the EPOCH within the maximum shift time, //We also need to take into account punch pairs, for example: // Punch Pair: 10-Mar-09 @ 11:30PM -> 11-Mar-09 @ 2:30PM. If the maximum shift time ends at 11:45PM // we need to include the out punch as well. $start_time_stamp = $epoch - $maximum_shift_time; $end_time_stamp = $epoch + $maximum_shift_time; $udf = new UserDateFactory(); $pcf = new PunchControlFactory(); $ppf = new PremiumPolicyFactory(); $ppbf = new PremiumPolicyBranchFactory(); $ppdf = new PremiumPolicyDepartmentFactory(); if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) { $jf = new JobFactory(); $jif = new JobItemFactory(); $ppjgf = new PremiumPolicyJobGroupFactory(); $ppjf = new PremiumPolicyJobFactory(); $ppjigf = new PremiumPolicyJobItemGroupFactory(); $ppjif = new PremiumPolicyJobItemFactory(); } $ph = array('user_id' => $user_id, 'start_date_stamp' => $this->db->BindDate($start_time_stamp), 'end_date_stamp' => $this->db->BindDate($end_time_stamp), 'start_time_stamp2' => $this->db->BindTimeStamp($start_time_stamp), 'end_time_stamp2' => $this->db->BindTimeStamp($end_time_stamp)); //This query removes the sub-query and is optimized for MySQL. $query = ' select distinct a.* from ' . $this->getTable() . ' as a INNER JOIN ( select ' . (int) $punch_control_id . ' as punch_control_id UNION ALL select x.punch_control_id from ' . $this->getTable() . ' as x LEFT JOIN ' . $pcf->getTable() . ' as pcf ON x.punch_control_id = pcf.id LEFT JOIN ' . $udf->getTable() . ' as z ON pcf.user_date_id = z.id '; if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) { $query .= ' LEFT JOIN ' . $jf->getTable() . ' as jf ON (pcf.job_id = jf.id AND jf.deleted = 0 )'; $query .= ' LEFT JOIN ' . $jif->getTable() . ' as jif ON (pcf.job_item_id = jif.id AND jf.deleted = 0 )'; } $query .= ' where z.user_id = ? AND z.date_stamp >= ? AND z.date_stamp <= ? AND x.time_stamp >= ? AND x.time_stamp <= ? '; //Branch criteria if (isset($filter_data['exclude_default_branch']) and $filter_data['exclude_default_branch'] == TRUE and isset($filter_data['default_branch_id'])) { $query .= $this->getWhereClauseSQL('pcf.branch_id', $filter_data['default_branch_id'], 'not_numeric_list', $ph); } if (isset($filter_data['branch_selection_type_id']) and $filter_data['branch_selection_type_id'] == 20 and isset($filter_data['premium_policy_id'])) { $query .= ' AND pcf.branch_id in ( select zz.branch_id from ' . $ppbf->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } elseif (isset($filter_data['branch_selection_type_id']) and $filter_data['branch_selection_type_id'] == 30 and isset($filter_data['premium_policy_id'])) { $query .= ' AND pcf.branch_id not in ( select zz.branch_id from ' . $ppbf->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } //Department criteria if (isset($filter_data['exclude_default_department']) and $filter_data['exclude_default_department'] == TRUE and isset($filter_data['default_department_id'])) { $query .= $this->getWhereClauseSQL('pcf.department_id', $filter_data['default_department_id'], 'not_numeric_list', $ph); } if (isset($filter_data['department_selection_type_id']) and $filter_data['department_selection_type_id'] == 20 and isset($filter_data['premium_policy_id'])) { $query .= ' AND pcf.department_id in ( select zz.department_id from ' . $ppdf->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } elseif (isset($filter_data['department_selection_type_id']) and $filter_data['department_selection_type_id'] == 30 and isset($filter_data['premium_policy_id'])) { $query .= ' AND pcf.department_id not in ( select zz.department_id from ' . $ppdf->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) { //Job Group Criteria if (isset($filter_data['job_group_selection_type_id']) and $filter_data['job_group_selection_type_id'] == 20 and isset($filter_data['premium_policy_id'])) { $query .= ' AND jf.group_id in ( select zz.job_group_id from ' . $ppjgf->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } elseif (isset($filter_data['job_group_selection_type_id']) and $filter_data['job_group_selection_type_id'] == 30 and isset($filter_data['premium_policy_id'])) { $query .= ' AND jf.group_id not in ( select zz.job_group_id from ' . $ppjgf->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } //Job Criteria if (isset($filter_data['job_selection_type_id']) and $filter_data['job_selection_type_id'] == 20 and isset($filter_data['premium_policy_id'])) { $query .= ' AND pcf.job_id in ( select zz.job_id from ' . $ppjf->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } elseif (isset($filter_data['job_selection_type_id']) and $filter_data['job_selection_type_id'] == 30 and isset($filter_data['premium_policy_id'])) { $query .= ' AND pcf.job_id not in ( select zz.job_id from ' . $ppjf->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } //Task Group Criteria if (isset($filter_data['job_item_group_selection_type_id']) and $filter_data['job_item_group_selection_type_id'] == 20 and isset($filter_data['premium_policy_id'])) { $query .= ' AND jif.group_id in ( select zz.job_item_group_id from ' . $ppjigf->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } elseif (isset($filter_data['job_item_group_selection_type_id']) and $filter_data['job_item_group_selection_type_id'] == 30 and isset($filter_data['premium_policy_id'])) { $query .= ' AND jif.group_id not in ( select zz.job_item_group_id from ' . $ppjigf->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } //Task Criteria if (isset($filter_data['job_item_selection_type_id']) and $filter_data['job_item_selection_type_id'] == 20 and isset($filter_data['premium_policy_id'])) { $query .= ' AND pcf.job_item_id in ( select zz.job_item_id from ' . $ppjif->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } elseif (isset($filter_data['job_item_selection_type_id']) and $filter_data['job_item_selection_type_id'] == 30 and isset($filter_data['premium_policy_id'])) { $query .= ' AND pcf.job_item_id not in ( select zz.job_item_id from ' . $ppjif->getTable() . ' as zz WHERE zz.premium_policy_id = ' . (int) $filter_data['premium_policy_id'] . ' ) '; } } $query .= ' AND ( x.deleted = 0 AND pcf.deleted=0 AND z.deleted=0 ) ) as z ON a.punch_control_id = z.punch_control_id WHERE a.deleted = 0 ORDER BY a.time_stamp asc, a.punch_control_id, a.status_id asc '; //$query .= $this->getSortSQL( $order ); $this->ExecuteSQL($query, $ph); //Debug::Arr($ph, 'Query: '. $query, __FILE__, __LINE__, __METHOD__,10); return $this; }
function setJobItemGroup($ids) { if (getTTProductEdition() != TT_PRODUCT_PROFESSIONAL) { return FALSE; } Debug::text('Setting IDs...', __FILE__, __LINE__, __METHOD__, 10); if (is_array($ids)) { $tmp_ids = array(); if (!$this->isNew()) { //If needed, delete mappings first. $lf_a = new PremiumPolicyJobItemGroupListFactory(); $lf_a->getByPremiumPolicyId($this->getId()); foreach ($lf_a as $obj) { $id = $obj->getJobItemGroup(); Debug::text('Job Item Group ID: ' . $obj->getJobItemGroup() . ' ID: ' . $id, __FILE__, __LINE__, __METHOD__, 10); //Delete users that are not selected. if (!in_array($id, $ids)) { Debug::text('Deleting: ' . $id, __FILE__, __LINE__, __METHOD__, 10); $obj->Delete(); } else { //Save ID's that need to be updated. Debug::text('NOT Deleting : ' . $id, __FILE__, __LINE__, __METHOD__, 10); $tmp_ids[] = $id; } } unset($id, $obj); } //Insert new mappings. $lf_b = new JobItemGroupListFactory(); foreach ($ids as $id) { if (isset($ids) and $id > 0 and !in_array($id, $tmp_ids)) { $f = new PremiumPolicyJobItemGroupFactory(); $f->setPremiumPolicy($this->getId()); $f->setJobItemGroup($id); $obj = $lf_b->getById($id)->getCurrent(); if ($this->Validator->isTrue('job_item_group', $f->Validator->isValid(), TTi18n::gettext('Selected Task Group is invalid') . ' (' . $obj->getName() . ')')) { $f->save(); } } } return TRUE; } Debug::text('No IDs to set.', __FILE__, __LINE__, __METHOD__, 10); return FALSE; }