function getByPolicyGroupUserId($user_id, $where = NULL, $order = NULL) { if ($user_id == '') { return FALSE; } if ($order == NULL) { //$order = array( 'c.type_id' => 'asc', 'c.trigger_time' => 'desc' ); $strict = FALSE; } else { $strict = TRUE; } $pgf = new PolicyGroupFactory(); $pguf = new PolicyGroupUserFactory(); $cgmf = new CompanyGenericMapFactory(); $ph = array('user_id' => $user_id); $query = ' select c.* from ' . $pguf->getTable() . ' as a, ' . $pgf->getTable() . ' as b, ' . $cgmf->getTable() . ' as c, ' . $this->getTable() . ' as d where a.policy_group_id = b.id AND ( b.id = c.object_id AND c.company_id = b.company_id AND c.object_type_id = 180) AND c.map_id = d.id AND a.user_id = ? AND ( b.deleted = 0 AND d.deleted = 0 ) '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict); $this->ExecuteSQL($query, $ph); return $this; }
function getByCompanyId($id, $where = NULL, $order = NULL) { if ($id == '') { return FALSE; } if ($order == NULL) { $order = array('a.type_id' => 'asc', 'a.name' => 'asc'); $strict = FALSE; } else { $strict = TRUE; } $pgf = new PolicyGroupFactory(); $cgmf = new CompanyGenericMapFactory(); $ph = array('id' => $id); $query = ' select a.*, (select count(*) from ' . $cgmf->getTable() . ' as z where z.company_id = a.company_id AND z.object_type_id = 160 AND z.map_id = a.id) as assigned_policy_groups from ' . $this->getTable() . ' as a where a.company_id = ? AND a.deleted = 0'; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict); $this->ExecuteSQL($query, $ph); }
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('date_stamp' => 'desc', 'name' => 'asc'); $strict = FALSE; } else { if (!isset($order['date_stamp'])) { $order = Misc::prependArray(array('date_stamp' => 'desc'), $order); } $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $uf = new UserFactory(); $pgf = new PolicyGroupFactory(); $pguf = new PolicyGroupUserFactory(); $hpf = new HolidayPolicyFactory(); $cgmf = new CompanyGenericMapFactory(); $ph = array('company_id' => $company_id); $query = ' select distinct a.*, 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 ' . $hpf->getTable() . ' as hpf ON ( a.holiday_policy_id = hpf.id AND hpf.deleted = 0 ) LEFT JOIN ' . $cgmf->getTable() . ' as cgmf ON ( cgmf.company_id = hpf.company_id AND cgmf.object_type_id = 180 AND cgmf.map_id = a.holiday_policy_id ) LEFT JOIN ' . $pgf->getTable() . ' as pgf ON ( pgf.id = cgmf.object_id AND pgf.deleted = 0 ) LEFT JOIN ' . $pguf->getTable() . ' as pguf ON ( pguf.policy_group_id = pgf.id AND pgf.deleted = 0 ) 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 hpf.company_id = ? '; 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 a.created_by 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 a.id not in (' . $this->getListSQL($filter_data['exclude_id'], $ph) . ') '; } if (isset($filter_data['holiday_policy_id']) and isset($filter_data['holiday_policy_id'][0]) and !in_array(-1, (array) $filter_data['holiday_policy_id'])) { $query .= ' AND a.holiday_policy_id in (' . $this->getListSQL($filter_data['holiday_policy_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 pguf.user_id in (' . $this->getListSQL($filter_data['user_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['start_date']) and trim($filter_data['start_date']) != '') { $ph[] = $this->db->BindDate($filter_data['start_date']); $query .= ' AND a.date_stamp >= ?'; } if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') { $ph[] = $this->db->BindDate($filter_data['end_date']); $query .= ' AND a.date_stamp <= ?'; } $query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL; $query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL; $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); $this->ExecuteSQL($query, $ph, $limit, $page); 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(); 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(); $pguf = new PolicyGroupUserFactory(); $cgmf = new CompanyGenericMapFactory(); $ph = array('company_id' => $company_id); $query = ' select distinct a.*, 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 ' . $pguf->getTable() . ' as b ON a.id = b.policy_group_id LEFT JOIN ' . $cgmf->getTable() . ' as c ON ( a.id = c.object_id AND c.company_id = a.company_id AND c.object_type_id = 130) LEFT JOIN ' . $cgmf->getTable() . ' as d ON ( a.id = d.object_id AND d.company_id = a.company_id AND d.object_type_id = 110) LEFT JOIN ' . $cgmf->getTable() . ' as e ON ( a.id = e.object_id AND e.company_id = a.company_id AND e.object_type_id = 120) LEFT JOIN ' . $cgmf->getTable() . ' as f ON ( a.id = f.object_id AND f.company_id = a.company_id AND f.object_type_id = 140) 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['exception_policy_control']) and isset($filter_data['exception_policy_control'][0]) and !in_array(-1, (array) $filter_data['exception_policy_control'])) { $query .= ' AND a.exception_policy_control_id in (' . $this->getListSQL($filter_data['exception_policy_control'], $ph) . ') '; } if (isset($filter_data['holiday_policy']) and isset($filter_data['holiday_policy'][0]) and !in_array(-1, (array) $filter_data['holiday_policy'])) { $query .= ' AND a.holiday_policy_id in (' . $this->getListSQL($filter_data['holiday_policy'], $ph) . ') '; } if (isset($filter_data['user']) and isset($filter_data['user'][0]) and !in_array(-1, (array) $filter_data['user'])) { $query .= ' AND b.user_id in (' . $this->getListSQL($filter_data['user'], $ph) . ') '; } if (isset($filter_data['round_interval_policy']) and isset($filter_data['round_interval_policy'][0]) and !in_array(-1, (array) $filter_data['round_interval_policy'])) { $query .= ' AND c.map_id in (' . $this->getListSQL($filter_data['round_interval_policy'], $ph) . ') '; } if (isset($filter_data['over_time_policy']) and isset($filter_data['over_time_policy'][0]) and !in_array(-1, (array) $filter_data['over_time_policy'])) { $query .= ' AND d.map_id in (' . $this->getListSQL($filter_data['over_time_policy'], $ph) . ') '; } if (isset($filter_data['premium_policy']) and isset($filter_data['premium_policy'][0]) and !in_array(-1, (array) $filter_data['premium_policy'])) { $query .= ' AND e.map_id in (' . $this->getListSQL($filter_data['premium_policy'], $ph) . ') '; } if (isset($filter_data['accrual_policy']) and isset($filter_data['accrual_policy'][0]) and !in_array(-1, (array) $filter_data['accrual_policy'])) { $query .= ' AND f.map_id in (' . $this->getListSQL($filter_data['accrual_policy'], $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; }
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('type_id'); $sort_column_aliases = array('type' => 'type_id'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('type_id' => 'asc', 'name' => 'asc'); $strict = FALSE; } else { //Always try to order by status first so INACTIVE employees go to the bottom. if (!isset($order['type_id'])) { $order = Misc::prependArray(array('type_id' => 'asc'), $order); } //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(); $apf = new AccrualPolicyFactory(); $cgmf = new CompanyGenericMapFactory(); $pgf = new PolicyGroupFactory(); $pguf = new PolicyGroupUserFactory(); $ph = array('company_id' => $company_id); $query = ' select DISTINCT a.*, apf.name as accrual_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 ' . $cgmf->getTable() . ' as cgmf ON ( a.id = cgmf.map_id AND cgmf.object_type_id = 170 ) LEFT JOIN ' . $pgf->getTable() . ' as pgf ON ( cgmf.object_id = pgf.id AND pgf.deleted = 0 ) LEFT JOIN ' . $pguf->getTable() . ' as pguf ON ( pguf.policy_group_id = pgf.id ) LEFT JOIN ' . $apf->getTable() . ' as apf ON ( a.accrual_policy_id = apf.id AND apf.deleted = 0 ) 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 = ? '; $query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.created_by', $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('a.id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL; if (isset($filter_data['type']) and trim($filter_data['type']) != '' and !isset($filter_data['type_id'])) { $filter_data['type_id'] = Option::getByFuzzyValue($filter_data['type'], $this->getOptions('type')); } $query .= isset($filter_data['type_id']) ? $this->getWhereClauseSQL('a.type_id', $filter_data['type_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('pguf.user_id', $filter_data['user_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['pay_stub_entry_account_id']) ? $this->getWhereClauseSQL('a.pay_stub_entry_account_id', $filter_data['pay_stub_entry_account_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['accrual_policy_id']) ? $this->getWhereClauseSQL('a.accrual_policy_id', $filter_data['accrual_policy_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['wage_group_id']) ? $this->getWhereClauseSQL('a.wage_group_id', $filter_data['wage_group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['name']) ? $this->getWhereClauseSQL('a.name', $filter_data['name'], 'text', $ph) : NULL; $query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL; $query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL; $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }
function getByPolicyGroupUserIdOrId($user_id, $id = NULL, $where = NULL, $order = NULL) { if ($user_id == '') { return FALSE; } if ($id == '') { $id = 0; } if ($order == NULL) { $order = array('type_id' => 'asc', 'trigger_time' => 'desc'); $strict = FALSE; } else { $strict = TRUE; } $pgf = new PolicyGroupFactory(); $pguf = new PolicyGroupUserFactory(); $cgmf = new CompanyGenericMapFactory(); $otpf = new OverTimePolicyFactory(); $ph = array('user_id' => $user_id, 'id' => $id); $query = ' select d.* from ' . $pguf->getTable() . ' as a, ' . $pgf->getTable() . ' as b, ' . $cgmf->getTable() . ' as c, ' . $this->getTable() . ' as d where a.policy_group_id = b.id AND ( b.id = c.object_id AND b.company_id = c.company_id AND c.object_type_id = 110 ) AND c.map_id = d.id AND a.user_id = ? AND ( b.deleted = 0 AND d.deleted = 0 ) UNION select e.* from ' . $otpf->getTable() . ' as e where e.id = ? AND e.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict); $this->rs = $this->db->Execute($query, $ph); return $this; }
function getByPolicyGroupUserIdOrSchedulePolicyID($user_id, $id, $where = NULL, $order = NULL) { if ($user_id == '') { return FALSE; } if ($order == NULL) { $order = array('type_id' => 'asc', 'id' => 'asc'); $strict = FALSE; } else { $strict = TRUE; } $pgf = new PolicyGroupFactory(); $pguf = new PolicyGroupUserFactory(); $cgmf = new CompanyGenericMapFactory(); $ppf = new PremiumPolicyFactory(); $ph = array('user_id' => $user_id); //Make sure we don't include duplicate premium policies //if the premium policy is assigned to the policy group AND the schedule policy. //it could double up on the premium time. $query = ' select distinct * from ( select distinct d.* from ' . $pguf->getTable() . ' as a, ' . $pgf->getTable() . ' as b, ' . $cgmf->getTable() . ' as c, ' . $this->getTable() . ' as d where a.policy_group_id = b.id AND ( b.id = c.object_id AND b.company_id = c.company_id AND c.object_type_id = 120 ) AND c.map_id = d.id AND a.user_id = ? AND ( b.deleted = 0 AND d.deleted = 0 ) UNION ALL select z.* from ' . $this->getTable() . ' as z, ' . $cgmf->getTable() . ' as c WHERE c.map_id = z.id AND c.object_type_id = 125 AND c.object_id in (' . $this->getListSQL($id, $ph) . ') AND z.deleted = 0 ) as tmp '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict); $this->ExecuteSQL($query, $ph); //Debug::Arr($ph ,' Query: '. $query, __FILE__, __LINE__, __METHOD__,10); return $this; }
function getByPolicyGroupUserIdAndTypeId($user_id, $type_id, $where = NULL, $order = NULL) { if ($user_id == '') { return FALSE; } if ($type_id == '') { return FALSE; } if ($order == NULL) { $order = array('d.punch_type_id' => 'desc'); $strict = FALSE; } else { $strict = TRUE; } $pgf = new PolicyGroupFactory(); $pguf = new PolicyGroupUserFactory(); $cgmf = new CompanyGenericMapFactory(); $ripf = new RoundIntervalPolicyFactory(); $punch_type_relation_options = $ripf->getOptions('punch_type_relation'); if (isset($punch_type_relation_options[$type_id])) { $punch_type_ids = $punch_type_relation_options[$type_id]; $punch_type_ids[] = $type_id; } else { return FALSE; } $ph = array('user_id' => $user_id); $query = ' select d.* from ' . $pguf->getTable() . ' as a, ' . $pgf->getTable() . ' as b, ' . $cgmf->getTable() . ' as c, ' . $this->getTable() . ' as d where a.policy_group_id = b.id AND ( b.id = c.object_id AND b.company_id = c.company_id AND c.object_type_id = 130 ) AND c.map_id = d.id AND a.user_id = ? AND d.punch_type_id in ( ' . $this->getListSQL($punch_type_ids, $ph) . ') AND ( b.deleted = 0 AND d.deleted = 0 ) '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict); $query .= ' LIMIT 1'; $this->rs = $this->db->Execute($query, $ph); return $this; }
function setGroup($ids) { Debug::text('Setting Groups IDs : ', __FILE__, __LINE__, __METHOD__, 10); Debug::Arr($ids, 'Setting Group data... ', __FILE__, __LINE__, __METHOD__, 10); return CompanyGenericMapFactory::setMapIDs($this->getCompany(), 2020, $this->getID(), $ids); }
function setHolidayPolicy($ids) { Debug::text('Setting Holiday Policy IDs : ', __FILE__, __LINE__, __METHOD__, 10); return CompanyGenericMapFactory::setMapIDs($this->getCompany(), 180, $this->getID(), $ids); }
function getByCompanyIdAndStartDateAndEndDate($company_id, $start_date, $end_date, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if ($start_date == '') { return FALSE; } if ($end_date == '') { return FALSE; } if ($order == NULL) { $order = array('d.date_stamp' => 'desc'); $strict = FALSE; } else { $strict = TRUE; } $pgf = new PolicyGroupFactory(); $pguf = new PolicyGroupUserFactory(); $hpf = new HolidayPolicyFactory(); $cgmf = new CompanyGenericMapFactory(); $ph = array('company_id' => $company_id, 'start_date' => $this->db->BindDate($start_date), 'end_date' => $this->db->BindDate($end_date)); //Query was: distinct(d.*) but MySQL doesnt like that. $query = ' select distinct d.* from ' . $pguf->getTable() . ' as a, ' . $pgf->getTable() . ' as b, ' . $hpf->getTable() . ' as c, ' . $cgmf->getTable() . ' as z, ' . $this->getTable() . ' as d where a.policy_group_id = b.id AND ( b.id = z.object_id AND z.company_id = b.company_id AND z.object_type_id = 180) AND z.map_id = d.holiday_policy_id AND d.holiday_policy_id = c.id AND b.company_id = ? AND d.date_stamp >= ? AND d.date_stamp <= ? AND ( c.deleted = 0 AND d.deleted=0 ) '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict); $this->rs = $this->db->Execute($query, $ph); 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('total_users'); $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(); $pguf = new PolicyGroupUserFactory(); $cgmf = new CompanyGenericMapFactory(); $ph = array('company_id' => $company_id); //Count total users in PolicyGroup factory, so we can disable it when needed. That way it doesn't slow down Policy Group dropdown boxes. //(select count(*) from '. $pguf->getTable() .' as pguf_tmp where pguf_tmp.policy_group_id = a.id ) as total_users, $query = ' select distinct a.*, 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 ' . $pguf->getTable() . ' as b ON a.id = b.policy_group_id LEFT JOIN ' . $cgmf->getTable() . ' as c ON ( a.id = c.object_id AND c.company_id = a.company_id AND c.object_type_id = 130) LEFT JOIN ' . $cgmf->getTable() . ' as d ON ( a.id = d.object_id AND d.company_id = a.company_id AND d.object_type_id = 110) LEFT JOIN ' . $cgmf->getTable() . ' as e ON ( a.id = e.object_id AND e.company_id = a.company_id AND e.object_type_id = 120) LEFT JOIN ' . $cgmf->getTable() . ' as f ON ( a.id = f.object_id AND f.company_id = a.company_id AND f.object_type_id = 140) LEFT JOIN ' . $cgmf->getTable() . ' as g ON ( a.id = g.object_id AND g.company_id = a.company_id AND g.object_type_id = 180) 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['permission_children_ids']) and isset($filter_data['permission_children_ids'][0]) and !in_array(-1, (array) $filter_data['permission_children_ids'])) { $query .= ' AND a.created_by 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['exception_policy_control']) and isset($filter_data['exception_policy_control'][0]) and !in_array(-1, (array) $filter_data['exception_policy_control'])) { $query .= ' AND a.exception_policy_control_id in (' . $this->getListSQL($filter_data['exception_policy_control'], $ph) . ') '; } if (isset($filter_data['holiday_policy']) and isset($filter_data['holiday_policy'][0]) and !in_array(-1, (array) $filter_data['holiday_policy'])) { $query .= ' AND g.map_id in (' . $this->getListSQL($filter_data['holiday_policy'], $ph) . ') '; } if (isset($filter_data['user']) and isset($filter_data['user'][0]) and !in_array(-1, (array) $filter_data['user'])) { $query .= ' AND b.user_id in (' . $this->getListSQL($filter_data['user'], $ph) . ') '; } if (isset($filter_data['round_interval_policy']) and isset($filter_data['round_interval_policy'][0]) and !in_array(-1, (array) $filter_data['round_interval_policy'])) { $query .= ' AND c.map_id in (' . $this->getListSQL($filter_data['round_interval_policy'], $ph) . ') '; } if (isset($filter_data['over_time_policy']) and isset($filter_data['over_time_policy'][0]) and !in_array(-1, (array) $filter_data['over_time_policy'])) { $query .= ' AND d.map_id in (' . $this->getListSQL($filter_data['over_time_policy'], $ph) . ') '; } if (isset($filter_data['premium_policy']) and isset($filter_data['premium_policy'][0]) and !in_array(-1, (array) $filter_data['premium_policy'])) { $query .= ' AND e.map_id in (' . $this->getListSQL($filter_data['premium_policy'], $ph) . ') '; } if (isset($filter_data['accrual_policy']) and isset($filter_data['accrual_policy'][0]) and !in_array(-1, (array) $filter_data['accrual_policy'])) { $query .= ' AND f.map_id in (' . $this->getListSQL($filter_data['accrual_policy'], $ph) . ') '; } if (isset($filter_data['name']) and trim($filter_data['name']) != '') { $ph[] = strtolower(trim($filter_data['name'])); $query .= ' AND lower(a.name) LIKE ?'; } $query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL; $query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL; $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }
static function setMapIDs($company_id, $object_type_id, $object_id, $ids, $is_new = FALSE) { if ($company_id == '') { return FALSE; } if ($object_type_id == '') { return FALSE; } if ($object_id == '') { return FALSE; } if ($ids == '') { return FALSE; } if (!is_array($ids) and is_numeric($ids)) { $ids = array($ids); } Debug::Arr($ids, 'Object Type ID: ' . $object_type_id . ' Object ID: ' . $object_id . ' IDs: ', __FILE__, __LINE__, __METHOD__, 10); if (is_array($ids)) { if ($is_new == FALSE) { //If needed, delete mappings first. $cgmlf = new CompanyGenericMapListFactory(); $cgmlf->getByCompanyIDAndObjectTypeAndObjectID($company_id, $object_type_id, $object_id); $tmp_ids = array(); foreach ($cgmlf as $obj) { $id = $obj->getMapID(); Debug::text('Object Type ID: ' . $object_type_id . ' Object ID: ' . $obj->getObjectID() . ' ID: ' . $id, __FILE__, __LINE__, __METHOD__, 10); //Delete objects 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); } foreach ($ids as $id) { if (isset($ids) and !in_array($id, $tmp_ids)) { $cgmf = new CompanyGenericMapFactory(); $cgmf->setCompany($company_id); $cgmf->setObjectType($object_type_id); $cgmf->setObjectID($object_id); $cgmf->setMapId($id); $cgmf->Save(); } } return TRUE; } Debug::text('No objects to map.', __FILE__, __LINE__, __METHOD__, 10); return FALSE; }
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (isset($filter_data['kpi_id'])) { $filter_data['id'] = $filter_data['kpi_id']; } if (isset($filter_data['kpi_status_id'])) { $filter_data['status_id'] = $filter_data['kpi_status_id']; } if (isset($filter_data['kpi_type_id'])) { $filter_data['type_id'] = $filter_data['kpi_type_id']; } if (isset($filter_data['kpi_group_id'])) { $filter_data['group_id'] = $filter_data['kpi_group_id']; } 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('type_id', 'status_id'); $sort_column_aliases = array('type' => 'type_id', 'status' => 'status_id'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('name' => 'asc'); $strict = FALSE; } else { $strict = TRUE; } Debug::Arr($order, 'Order Data:', __FILE__, __LINE__, __METHOD__, 10); Debug::Arr($filter_data, 'Filter Data:', __FILE__, __LINE__, __METHOD__, 10); $uf = new UserFactory(); $cgmf = new CompanyGenericMapFactory(); //$kgf = new KPIGroupFactory(); $ph = array('company_id' => $company_id); $query = ' select DISTINCT a.*, 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 ' . $cgmf->getTable() . ' as b ON ( a.id = b.object_id AND b.company_id = a.company_id AND b.object_type_id = 2020 ) 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 = ? '; $query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.created_by', $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['name']) ? $this->getWhereClauseSQL('a.name', $filter_data['name'], 'text', $ph) : NULL; $query .= isset($filter_data['description']) ? $this->getWhereClauseSQL('a.description', $filter_data['description'], 'text', $ph) : NULL; $query .= isset($filter_data['minimum_rate']) ? $this->getWhereClauseSQL('a.minimum_rate', $filter_data['minimum_rate'], 'numeric', $ph) : NULL; $query .= isset($filter_data['maximum_rate']) ? $this->getWhereClauseSQL('a.maximum_rate', $filter_data['maximum_rate'], 'numeric', $ph) : NULL; if (isset($filter_data['status']) and trim($filter_data['status']) != '' and !isset($filter_data['status_id'])) { $filter_data['status_id'] = Option::getByFuzzyValue($filter_data['status'], $this->getOptions('status')); } if (isset($filter_data['type']) and trim($filter_data['type']) != '' and !isset($filter_data['type_id'])) { $filter_data['type_id'] = Option::getByFuzzyValue($filter_data['type'], $this->getOptions('type')); } $query .= isset($filter_data['type_id']) ? $this->getWhereClauseSQL('a.type_id', $filter_data['type_id'], 'numeric_list', $ph) : NULL; if (isset($filter_data['group_id']) and isset($filter_data['group_id'][0])) { $query .= ' AND b.map_id in (' . $this->getListSQL($filter_data['group_id'], $ph) . ') '; } //$query .= ( isset($filter_data['group_id']) ) ? $this->getWhereClauseSQL( 'b.map_id', $filter_data['group_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['tag']) ? $this->getWhereClauseSQL('a.id', array('company_id' => $company_id, 'object_type_id' => 310, 'tag' => $filter_data['tag']), 'tag', $ph) : NULL; //$query .= ( isset($filter_data['created_by']) ) ? $this->getWhereClauseSQL( 'a.created_by', $filter_data['created_by'], 'numeric_list', $ph ) : NULL; //$query .= ( isset($filter_data['updated_by']) ) ? $this->getWhereClauseSQL( 'a.updated_by', $filter_data['updated_by'], 'numeric_list', $ph ) : NULL; if (isset($filter_data['created_date']) and trim($filter_data['created_date']) != '') { $date_filter = $this->getDateRangeSQL($filter_data['created_date'], 'a.created_date'); if ($date_filter != FALSE) { $query .= ' AND ' . $date_filter; } unset($date_filter); } if (isset($filter_data['updated_date']) and trim($filter_data['updated_date']) != '') { $date_filter = $this->getDateRangeSQL($filter_data['updated_date'], 'a.updated_date'); if ($date_filter != FALSE) { $query .= ' AND ' . $date_filter; } unset($date_filter); } $query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL; $query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL; $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); Debug::Arr($ph, 'Query: ' . $query, __FILE__, __LINE__, __METHOD__, 10); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }
function setExpensePolicy($ids) { Debug::text('Setting Expense Policy IDs : ', __FILE__, __LINE__, __METHOD__, 10); return CompanyGenericMapFactory::setMapIDs($this->getCompany(), 200, $this->getID(), (array) $ids); }