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;
}
