function getPunchSummaryReportByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
//$order = array( 'b.pay_period_id' => 'asc', 'b.user_id' => 'asc' );
//$order = array( 'b.pay_period_id' => 'asc','uf.last_name' => 'asc', 'b.date_stamp' => 'asc' );
/*
if ( $order == NULL ) {
$order = array( 'b.pay_period_id' => 'asc', 'b.user_id' => 'asc' );
$strict = FALSE;
} else {
$strict = TRUE;
}
*/
if (isset($filter_data['punch_branch_ids'])) {
$filter_data['punch_branch_id'] = $filter_data['punch_branch_ids'];
}
if (isset($filter_data['punch_department_ids'])) {
$filter_data['punch_department_id'] = $filter_data['punch_department_ids'];
}
if (isset($filter_data['branch_ids'])) {
$filter_data['branch_id'] = $filter_data['branch_ids'];
}
if (isset($filter_data['department_ids'])) {
$filter_data['department_id'] = $filter_data['department_ids'];
}
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$udf = new UserDateFactory();
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ppf_b = new PayPeriodFactory();
$uwf = new UserWageFactory();
$pcf = new PunchControlFactory();
$pf = new PunchFactory();
$sf = new StationFactory();
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$jf = new JobFactory();
$jgf = new JobGroupFactory();
$jif = new JobItemFactory();
$jigf = new JobItemGroupFactory();
}
$ph = array('company_id' => $company_id);
//Make it so employees with 0 hours still show up!! Very important!
//Order dock hours first, so it can be deducted from regular time.
$query = '
select
a.id as punch_id,
a.punch_control_id as punch_control_id,
a.type_id as type_id,
a.status_id as status_id,
a.time_stamp as punch_time_stamp,
a.actual_time_stamp as punch_actual_time_stamp,
a.created_date as punch_created_date,
uf_b.first_name as punch_created_by_first_name,
uf_b.middle_name as punch_created_by_middle_name,
uf_b.last_name as punch_created_by_last_name,
a.updated_date as punch_updated_date,
uf_c.first_name as punch_updated_by_first_name,
uf_c.middle_name as punch_updated_by_middle_name,
uf_c.last_name as punch_updated_by_last_name,
pcf.user_date_id as user_date_id,
pcf.branch_id as branch_id,
pcf.department_id as department_id,
pcf.job_id as job_id,
pcf.job_item_id as job_item_id,
pcf.quantity as quantity,
pcf.bad_quantity as bad_quantity,
pcf.total_time as total_time,
pcf.actual_total_time as actual_total_time,
pcf.meal_policy_id as meal_policy_id,
pcf.other_id1 as other_id1,
pcf.other_id2 as other_id2,
pcf.other_id3 as other_id3,
pcf.other_id4 as other_id4,
pcf.other_id5 as other_id5,
pcf.note as note,
b.user_id as user_id,
b.date_stamp as date_stamp,
b.pay_period_id as pay_period_id,
ppf.id as pay_period_id,
ppf.start_date as pay_period_start_date,
ppf.end_date as pay_period_end_date,
ppf.transaction_date as pay_period_transaction_date,
CASE WHEN b.user_id != a.created_by OR a.created_by != a.updated_by OR ( a.created_by is NULL AND a.updated_by is NOT NULL ) THEN 1 ELSE 0 END as tainted,
bf.name as branch,
df.name as department,
a.status_id as status_id,
a.type_id as type_id,
z.id as user_wage_id,
z.effective_date as user_wage_effective_date,
z.hourly_rate as hourly_rate,
z.labor_burden_percent as labor_burden_percent,
sf.type_id as station_type_id,
sf.station_id as station_station_id,
sf.source as station_source,
sf.description as station_description';
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$query .= ',
jf.name as job,
jf.description as job_description,
jf.status_id as job_status_id,
jf.manual_id as job_manual_id,
jf.branch_id as job_branch_id,
jbf.name as job_branch,
jf.department_id as job_department_id,
jdf.name as job_department,
jf.group_id as job_group_id,
jgf.name as job_group,
jf.other_id1 as job_other_id1,
jf.other_id2 as job_other_id2,
jf.other_id3 as job_other_id3,
jf.other_id4 as job_other_id4,
jf.other_id5 as job_other_id5,
jif.name as job_item,
jif.description as job_item_description,
jif.manual_id as job_item_manual_id,
jif.group_id as job_item_group_id,
jigf.name as job_item_group
';
}
$query .= ' from ' . $this->getTable() . ' as a
LEFT JOIN ' . $pcf->getTable() . ' as pcf ON a.punch_control_id = pcf.id
LEFT JOIN ' . $udf->getTable() . ' as b ON pcf.user_date_id = b.id
LEFT JOIN ' . $ppf_b->getTable() . ' as ppf ON b.pay_period_id = ppf.id
LEFT JOIN ' . $uf->getTable() . ' as uf ON b.user_id = uf.id
LEFT JOIN ' . $uf->getTable() . ' as uf_b ON a.created_by = uf_b.id
LEFT JOIN ' . $uf->getTable() . ' as uf_c ON a.updated_by = uf_c.id
LEFT JOIN ' . $bf->getTable() . ' as bf ON pcf.branch_id = bf.id
LEFT JOIN ' . $df->getTable() . ' as df ON pcf.department_id = df.id
LEFT JOIN ' . $sf->getTable() . ' as sf ON a.station_id = sf.id
LEFT JOIN ' . $uwf->getTable() . ' as z ON z.id = (select z.id
from ' . $uwf->getTable() . ' as z
where z.user_id = b.user_id
and z.effective_date <= b.date_stamp
and z.wage_group_id = 0
and z.deleted = 0
order by z.effective_date desc limit 1) ';
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$query .= ' LEFT JOIN ' . $jf->getTable() . ' as jf ON pcf.job_id = jf.id
LEFT JOIN ' . $jif->getTable() . ' as jif ON pcf.job_item_id = jif.id
LEFT JOIN ' . $bf->getTable() . ' as jbf ON jf.branch_id = jbf.id
LEFT JOIN ' . $df->getTable() . ' as jdf ON jf.department_id = jdf.id
LEFT JOIN ' . $jgf->getTable() . ' as jgf ON jf.group_id = jgf.id
LEFT JOIN ' . $jigf->getTable() . ' as jigf ON jif.group_id = jigf.id
';
}
$query .= ' where uf.company_id = ? ';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('uf.id', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['include_user_id']) ? $this->getWhereClauseSQL('uf.id', $filter_data['include_user_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['exclude_user_id']) ? $this->getWhereClauseSQL('uf.id', $filter_data['exclude_user_id'], 'not_numeric_list', $ph) : NULL;
$query .= isset($filter_data['user_status_id']) ? $this->getWhereClauseSQL('uf.status_id', $filter_data['user_status_id'], 'numeric_list', $ph) : NULL;
if (isset($filter_data['include_user_subgroups']) and (bool) $filter_data['include_user_subgroups'] == TRUE) {
$uglf = new UserGroupListFactory();
$filter_data['user_group_id'] = $uglf->getByCompanyIdAndGroupIdAndSubGroupsArray($company_id, $filter_data['user_group_id'], TRUE);
}
$query .= isset($filter_data['user_group_id']) ? $this->getWhereClauseSQL('uf.group_id', $filter_data['user_group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['default_branch_id']) ? $this->getWhereClauseSQL('uf.default_branch_id', $filter_data['default_branch_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['default_department_id']) ? $this->getWhereClauseSQL('uf.default_department_id', $filter_data['default_department_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['user_title_id']) ? $this->getWhereClauseSQL('uf.title_id', $filter_data['user_title_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['punch_branch_id']) ? $this->getWhereClauseSQL('pcf.branch_id', $filter_data['punch_branch_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['punch_department_id']) ? $this->getWhereClauseSQL('pcf.department_id', $filter_data['punch_department_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['pay_period_id']) ? $this->getWhereClauseSQL('b.pay_period_id', $filter_data['pay_period_id'], 'numeric_list', $ph) : NULL;
if (isset($filter_data['job_status']) and trim($filter_data['job_status']) != '' and !isset($filter_data['job_status_id'])) {
$filter_data['job_status_id'] = Option::getByFuzzyValue($filter_data['job_status'], $jf->getOptions('status'));
}
$query .= isset($filter_data['job_status_id']) ? $this->getWhereClauseSQL('jf.status_id', $filter_data['job_status_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['job_group_id']) ? $this->getWhereClauseSQL('jf.group_id', $filter_data['job_group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['include_job_id']) ? $this->getWhereClauseSQL('pcf.job_id', $filter_data['include_job_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['exclude_job_id']) ? $this->getWhereClauseSQL('pcf.job_id', $filter_data['exclude_job_id'], 'not_numeric_list', $ph) : NULL;
$query .= isset($filter_data['job_item_group_id']) ? $this->getWhereClauseSQL('jif.group_id', $filter_data['job_item_group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['include_job_item_id']) ? $this->getWhereClauseSQL('pcf.job_item_id', $filter_data['include_job_item_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['exclude_job_item_id']) ? $this->getWhereClauseSQL('pcf.job_item_id', $filter_data['exclude_job_item_id'], 'not_numeric_list', $ph) : NULL;
$query .= isset($filter_data['tag']) ? $this->getWhereClauseSQL('uf.id', array('company_id' => $company_id, 'object_type_id' => 200, 'tag' => $filter_data['tag']), 'tag', $ph) : NULL;
if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') {
$ph[] = $this->db->BindDate($filter_data['start_date']);
$query .= ' AND b.date_stamp >= ?';
}
if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') {
$ph[] = $this->db->BindDate($filter_data['end_date']);
$query .= ' AND b.date_stamp <= ?';
}
$query .= '
AND ( a.deleted = 0 AND b.deleted = 0 AND pcf.deleted = 0 )
';
$query .= $this->getSortSQL($order, FALSE);
$this->ExecuteSQL($query, $ph);
return $this;
}