function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (!is_array($order)) { //Use Filter Data ordering if its set. if (isset($filter_data['sort_column']) and $filter_data['sort_order']) { $order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']); } } if (isset($filter_data['user_date_total_type_id'])) { $filter_data['type_id'] = $filter_data['user_date_total_type_id']; } $additional_order_fields = array('first_name', 'last_name', 'date_stamp', 'time_stamp', 'type_id', 'status_id', 'branch', 'department', 'default_branch', 'default_department', 'group', 'title'); if ($order == NULL) { $order = array('c.date_stamp' => 'asc', 'a.status_id' => 'asc', 'a.type_id' => 'asc', 'a.total_time' => 'asc', 'a.status_id' => 'asc'); $strict = FALSE; } else { $strict = FALSE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); if (isset($filter_data['exclude_user_ids'])) { $filter_data['exclude_id'] = $filter_data['exclude_user_ids']; } if (isset($filter_data['include_user_ids'])) { $filter_data['user_id'] = $filter_data['include_user_ids']; } if (isset($filter_data['user_title_ids'])) { $filter_data['title_id'] = $filter_data['user_title_ids']; } if (isset($filter_data['group_ids'])) { $filter_data['group_id'] = $filter_data['group_ids']; } if (isset($filter_data['branch_ids'])) { $filter_data['default_branch_id'] = $filter_data['branch_ids']; } if (isset($filter_data['department_ids'])) { $filter_data['default_department_id'] = $filter_data['department_ids']; } if (isset($filter_data['pay_period_ids'])) { $filter_data['pay_period_id'] = $filter_data['pay_period_ids']; } if (isset($filter_data['exclude_job_ids'])) { $filter_data['exclude_id'] = $filter_data['exclude_job_ids']; } if (isset($filter_data['include_job_ids'])) { $filter_data['include_job_id'] = $filter_data['include_job_ids']; } if (isset($filter_data['job_group_ids'])) { $filter_data['job_group_id'] = $filter_data['job_group_ids']; } if (isset($filter_data['job_item_ids'])) { $filter_data['job_item_id'] = $filter_data['job_item_ids']; } $uf = new UserFactory(); $udf = new UserDateFactory(); $uwf = new UserWageFactory(); $bf = new BranchFactory(); $df = new DepartmentFactory(); $ugf = new UserGroupFactory(); $utf = new UserTitleFactory(); $otpf = new OverTimePolicyFactory(); $apf = new AbsencePolicyFactory(); $ppf = new PremiumPolicyFactory(); $mpf = new MealPolicyFactory(); $bpf = new BreakPolicyFactory(); if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) { $jf = new JobFactory(); $jif = new JobItemFactory(); } $ph = array('company_id' => $company_id); $query = ' select a.id as id, a.user_date_id as user_date_id, a.type_id as type_id, a.status_id as status_id, a.punch_control_id as punch_control_id, a.over_time_policy_id as over_time_policy_id, otpf.name as over_time_policy, a.absence_policy_id as absence_policy_id, apf.name as absence_policy, apf.type_id as absence_policy_type_id, a.premium_policy_id as premium_policy_id, ppf.name as premium_policy, a.meal_policy_id as meal_policy_id, mpf.name as meal_policy, a.break_policy_id as break_policy_id, bpf.name as break_policy, a.start_time_stamp as start_time_stamp, a.end_time_stamp as end_time_stamp, a.override as override, a.branch_id as branch_id, j.name as branch, a.department_id as department_id, k.name as department, a.job_id as job_id, a.job_item_id as job_item_id, a.quantity as quantity, a.bad_quantity as bad_quantity, a.total_time as total_time, a.actual_total_time as actual_total_time, c.user_id as user_id, c.date_stamp as date_stamp, c.pay_period_id as pay_period_id, d.first_name as first_name, d.last_name as last_name, d.status_id as user_status_id, d.group_id as group_id, g.name as "group", d.title_id as title_id, h.name as title, d.default_branch_id as default_branch_id, e.name as default_branch, d.default_department_id as default_department_id, f.name as default_department, d.created_by as user_created_by, z.id as user_wage_id, z.effective_date as user_wage_effective_date '; if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) { $query .= ', x.name as job, x.name as job_name, x.status_id as job_status_id, x.manual_id as job_manual_id, x.branch_id as job_branch_id, x.department_id as job_department_id, x.group_id as job_group_id, y.name as job_item'; } $query .= ' from ' . $this->getTable() . ' as a LEFT JOIN ' . $udf->getTable() . ' as c ON a.user_date_id = c.id LEFT JOIN ' . $uf->getTable() . ' as d ON c.user_id = d.id LEFT JOIN ' . $bf->getTable() . ' as e ON ( d.default_branch_id = e.id AND e.deleted = 0) LEFT JOIN ' . $df->getTable() . ' as f ON ( d.default_department_id = f.id AND f.deleted = 0) LEFT JOIN ' . $ugf->getTable() . ' as g ON ( d.group_id = g.id AND g.deleted = 0 ) LEFT JOIN ' . $utf->getTable() . ' as h ON ( d.title_id = h.id AND h.deleted = 0 ) LEFT JOIN ' . $bf->getTable() . ' as j ON ( a.branch_id = j.id AND j.deleted = 0) LEFT JOIN ' . $df->getTable() . ' as k ON ( a.department_id = k.id AND k.deleted = 0) LEFT JOIN ' . $otpf->getTable() . ' as otpf ON ( a.over_time_policy_id > 0 AND a.over_time_policy_id = otpf.id AND otpf.deleted = 0 ) LEFT JOIN ' . $apf->getTable() . ' as apf ON ( a.absence_policy_id > 0 AND a.absence_policy_id = apf.id AND apf.deleted = 0 ) LEFT JOIN ' . $ppf->getTable() . ' as ppf ON ( a.premium_policy_id > 0 AND a.premium_policy_id = ppf.id AND ppf.deleted = 0 ) LEFT JOIN ' . $mpf->getTable() . ' as mpf ON ( a.meal_policy_id > 0 AND a.meal_policy_id = mpf.id AND mpf.deleted = 0 ) LEFT JOIN ' . $bpf->getTable() . ' as bpf ON ( a.break_policy_id > 0 AND a.break_policy_id = bpf.id AND bpf.deleted = 0 ) LEFT JOIN ' . $uwf->getTable() . ' as z ON z.id = (select z.id from ' . $uwf->getTable() . ' as z where z.user_id = c.user_id and z.effective_date <= c.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 x ON a.job_id = x.id'; $query .= ' LEFT JOIN ' . $jif->getTable() . ' as y ON a.job_item_id = y.id'; } $query .= ' WHERE d.company_id = ?'; $query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('d.id', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['exclude_id']) ? $this->getWhereClauseSQL('d.id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL; $query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('c.user_id', $filter_data['user_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['user_status_id']) ? $this->getWhereClauseSQL('d.status_id', $filter_data['user_status_id'], 'numeric_list', $ph) : NULL; if (isset($filter_data['include_subgroups']) and (bool) $filter_data['include_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('d.group_id', $filter_data['user_group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['user_group']) ? $this->getWhereClauseSQL('g.name', $filter_data['user_group'], 'text', $ph) : NULL; $query .= isset($filter_data['group_id']) ? $this->getWhereClauseSQL('d.group_id', $filter_data['group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_branch_id']) ? $this->getWhereClauseSQL('d.default_branch_id', $filter_data['default_branch_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_department_id']) ? $this->getWhereClauseSQL('d.default_department_id', $filter_data['default_department_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['title_id']) ? $this->getWhereClauseSQL('d.title_id', $filter_data['title_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['status_id']) ? $this->getWhereClauseSQL('a.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['type_id']) ? $this->getWhereClauseSQL('a.type_id', $filter_data['type_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['pay_period_id']) ? $this->getWhereClauseSQL('c.pay_period_id', $filter_data['pay_period_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['branch_id']) ? $this->getWhereClauseSQL('a.branch_id', $filter_data['branch_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['department_id']) ? $this->getWhereClauseSQL('a.department_id', $filter_data['department_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['job_id']) ? $this->getWhereClauseSQL('a.job_id', $filter_data['job_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['include_job_id']) ? $this->getWhereClauseSQL('a.job_id', $filter_data['include_job_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['exclude_job_id']) ? $this->getWhereClauseSQL('a.job_id', $filter_data['exclude_job_id'], 'not_numeric_list', $ph) : NULL; $query .= isset($filter_data['job_group_id']) ? $this->getWhereClauseSQL('x.group_id', $filter_data['job_group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['job_item_id']) ? $this->getWhereClauseSQL('a.job_item_id', $filter_data['job_item_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['include_job_item_id']) ? $this->getWhereClauseSQL('a.job_item_id', $filter_data['include_job_item_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['exclude_job_item_id']) ? $this->getWhereClauseSQL('a.job_item_id', $filter_data['exclude_job_item_id'], 'not_numeric_list', $ph) : NULL; /* if ( isset($filter_data['permission_children_ids']) AND isset($filter_data['permission_children_ids'][0]) AND !in_array(-1, (array)$filter_data['permission_children_ids']) ) { $query .= ' AND d.id in ('. $this->getListSQL($filter_data['permission_children_ids'], $ph) .') '; } if ( isset($filter_data['id']) AND isset($filter_data['id'][0]) AND !in_array(-1, (array)$filter_data['id']) ) { $query .= ' AND a.id in ('. $this->getListSQL($filter_data['id'], $ph) .') '; } if ( isset($filter_data['exclude_id']) AND isset($filter_data['exclude_id'][0]) AND !in_array(-1, (array)$filter_data['exclude_id']) ) { $query .= ' AND d.id not in ('. $this->getListSQL($filter_data['exclude_id'], $ph) .') '; } if ( isset($filter_data['user_id']) AND isset($filter_data['user_id'][0]) AND !in_array(-1, (array)$filter_data['user_id']) ) { $query .= ' AND c.user_id in ('. $this->getListSQL($filter_data['user_id'], $ph) .') '; } if ( isset($filter_data['user_status_id']) AND isset($filter_data['user_status_id'][0]) AND !in_array(-1, (array)$filter_data['user_status_id']) ) { $query .= ' AND d.status_id in ('. $this->getListSQL($filter_data['user_status_id'], $ph) .') '; } if ( isset($filter_data['group_id']) AND isset($filter_data['group_id'][0]) AND !in_array(-1, (array)$filter_data['group_id']) ) { if ( isset($filter_data['include_subgroups']) AND (bool)$filter_data['include_subgroups'] == TRUE ) { $uglf = new UserGroupListFactory(); $filter_data['group_id'] = $uglf->getByCompanyIdAndGroupIdAndSubGroupsArray( $company_id, $filter_data['group_id'], TRUE); } $query .= ' AND d.group_id in ('. $this->getListSQL($filter_data['group_id'], $ph) .') '; } if ( isset($filter_data['default_branch_id']) AND isset($filter_data['default_branch_id'][0]) AND !in_array(-1, (array)$filter_data['default_branch_id']) ) { $query .= ' AND d.default_branch_id in ('. $this->getListSQL($filter_data['default_branch_id'], $ph) .') '; } if ( isset($filter_data['default_department_id']) AND isset($filter_data['default_department_id'][0]) AND !in_array(-1, (array)$filter_data['default_department_id']) ) { $query .= ' AND d.default_department_id in ('. $this->getListSQL($filter_data['default_department_id'], $ph) .') '; } if ( isset($filter_data['title_id']) AND isset($filter_data['title_id'][0]) AND !in_array(-1, (array)$filter_data['title_id']) ) { $query .= ' AND d.title_id in ('. $this->getListSQL($filter_data['title_id'], $ph) .') '; } if ( isset($filter_data['branch_id']) AND isset($filter_data['branch_id'][0]) AND !in_array(-1, (array)$filter_data['branch_id']) ) { $query .= ' AND a.branch_id in ('. $this->getListSQL($filter_data['branch_id'], $ph) .') '; } if ( isset($filter_data['department_id']) AND isset($filter_data['department_id'][0]) AND !in_array(-1, (array)$filter_data['department_id']) ) { $query .= ' AND a.department_id in ('. $this->getListSQL($filter_data['department_id'], $ph) .') '; } if ( isset($filter_data['status_id']) AND isset($filter_data['status_id'][0]) AND !in_array(-1, (array)$filter_data['status_id']) ) { $query .= ' AND a.status_id in ('. $this->getListSQL($filter_data['status_id'], $ph) .') '; } if ( isset($filter_data['type_id']) AND isset($filter_data['type_id'][0]) AND !in_array(-1, (array)$filter_data['type_id']) ) { $query .= ' AND a.type_id in ('. $this->getListSQL($filter_data['type_id'], $ph) .') '; } if ( isset($filter_data['pay_period_id']) AND isset($filter_data['pay_period_id'][0]) AND !in_array(-1, (array)$filter_data['pay_period_id']) ) { $query .= ' AND c.pay_period_id in ('. $this->getListSQL($filter_data['pay_period_id'], $ph) .') '; } //Use the job_id in the punch_control table so we can filter by '0' or No Job if ( isset($filter_data['include_job_id']) AND isset($filter_data['include_job_id'][0]) AND !in_array(-1, (array)$filter_data['include_job_id']) ) { $query .= ' AND a.job_id in ('. $this->getListSQL($filter_data['include_job_id'], $ph) .') '; } if ( isset($filter_data['exclude_job_id']) AND isset($filter_data['exclude_job_id'][0]) AND !in_array(-1, (array)$filter_data['exclude_job_id']) ) { $query .= ' AND a.job_id not in ('. $this->getListSQL($filter_data['exclude_job_id'], $ph) .') '; } if ( isset($filter_data['job_group_id']) AND isset($filter_data['job_group_id'][0]) AND !in_array(-1, (array)$filter_data['job_group_id']) ) { if ( isset($filter_data['include_job_subgroups']) AND (bool)$filter_data['include_job_subgroups'] == TRUE ) { $uglf = new UserGroupListFactory(); $filter_data['job_group_id'] = $uglf->getByCompanyIdAndGroupIdAndjob_subgroupsArray( $company_id, $filter_data['job_group_id'], TRUE); } $query .= ' AND x.group_id in ('. $this->getListSQL($filter_data['job_group_id'], $ph) .') '; } if ( isset($filter_data['job_item_id']) AND isset($filter_data['job_item_id'][0]) AND !in_array(-1, (array)$filter_data['job_item_id']) ) { $query .= ' AND a.job_item_id in ('. $this->getListSQL($filter_data['job_item_id'], $ph) .') '; } */ if (isset($filter_data['date']) and trim($filter_data['date']) != '') { $ph[] = $this->db->BindDate($filter_data['date']); $query .= ' AND c.date_stamp = ?'; } if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') { $ph[] = $this->db->BindDate($filter_data['start_date']); $query .= ' AND c.date_stamp >= ?'; } if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') { $ph[] = $this->db->BindDate($filter_data['end_date']); $query .= ' AND c.date_stamp <= ?'; } $query .= ' AND (a.deleted = 0 AND c.deleted = 0 AND d.deleted = 0) '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); $this->ExecuteSQL($query, $ph, $limit, $page); //Debug::Arr($ph, 'Query: '. $query, __FILE__, __LINE__, __METHOD__,10); return $this; }
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (!is_array($order)) { //Use Filter Data ordering if its set. if (isset($filter_data['sort_column']) and $filter_data['sort_order']) { $order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']); } } $additional_order_fields = array(); $sort_column_aliases = array(); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('name' => 'asc'); $strict = FALSE; } else { //Always sort by last name,first name after other columns if (!isset($order['name'])) { $order['name'] = 'asc'; } $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $uf = new UserFactory(); $mpf = new MealPolicyFactory(); $apf = new AbsencePolicyFactory(); $ph = array('company_id' => $company_id); $query = ' select a.*, b.name as meal_policy, c.name as absence_policy, y.first_name as created_by_first_name, y.middle_name as created_by_middle_name, y.last_name as created_by_last_name, z.first_name as updated_by_first_name, z.middle_name as updated_by_middle_name, z.last_name as updated_by_last_name from ' . $this->getTable() . ' as a LEFT JOIN ' . $mpf->getTable() . ' as b ON a.meal_policy_id = b.id LEFT JOIN ' . $apf->getTable() . ' as c ON a.absence_policy_id = c.id LEFT JOIN ' . $uf->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 ) where a.company_id = ? '; if (isset($filter_data['id']) and isset($filter_data['id'][0]) and !in_array(-1, (array) $filter_data['id'])) { $query .= ' AND a.id in (' . $this->getListSQL($filter_data['id'], $ph) . ') '; } if (isset($filter_data['exclude_id']) and isset($filter_data['exclude_id'][0]) and !in_array(-1, (array) $filter_data['exclude_id'])) { $query .= ' AND a.id not in (' . $this->getListSQL($filter_data['exclude_id'], $ph) . ') '; } if (isset($filter_data['meal_policy_id']) and isset($filter_data['meal_policy_id'][0]) and !in_array(-1, (array) $filter_data['meal_policy_id'])) { $query .= ' AND a.meal_policy_id in (' . $this->getListSQL($filter_data['meal_policy_id'], $ph) . ') '; } if (isset($filter_data['absence_policy_id']) and isset($filter_data['absence_policy_id'][0]) and !in_array(-1, (array) $filter_data['absence_policy_id'])) { $query .= ' AND a.absence_policy_id in (' . $this->getListSQL($filter_data['absence_policy_id'], $ph) . ') '; } if (isset($filter_data['name']) and trim($filter_data['name']) != '') { $ph[] = strtolower(trim($filter_data['name'])); $query .= ' AND lower(a.name) LIKE ?'; } if (isset($filter_data['created_by']) and isset($filter_data['created_by'][0]) and !in_array(-1, (array) $filter_data['created_by'])) { $query .= ' AND a.created_by in (' . $this->getListSQL($filter_data['created_by'], $ph) . ') '; } if (isset($filter_data['updated_by']) and isset($filter_data['updated_by'][0]) and !in_array(-1, (array) $filter_data['updated_by'])) { $query .= ' AND a.updated_by in (' . $this->getListSQL($filter_data['updated_by'], $ph) . ') '; } $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); if ($limit == NULL) { $this->rs = $this->db->Execute($query, $ph); } else { $this->rs = $this->db->PageExecute($query, $limit, $page, $ph); } return $this; }