function getByCompanyId($company_id, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } $hpf = new HolidayPolicyFactory(); $ph = array('company_id' => $company_id); $query = ' select a.* from ' . $this->getTable() . ' as a LEFT JOIN ' . $hpf->getTable() . ' as hpf ON a.holiday_policy_id = hpf.id where hpf.company_id = ? AND ( hpf.deleted = 0 )'; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order); $this->ExecuteSQL($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(); $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 getByCompanyId($id, $where = NULL, $order = NULL) { if ($id == '') { return FALSE; } if ($order == NULL) { $order = array('a.punch_type_id' => 'asc', 'a.name' => 'asc'); $strict = FALSE; } else { $strict = TRUE; } $pgf = new PolicyGroupFactory(); $cgmf = new CompanyGenericMapFactory(); $hpf = new HolidayPolicyFactory(); $ph = array('id' => $id); $query = ' select a.*, ( ( select count(*) from ' . $cgmf->getTable() . ' as w, ' . $pgf->getTable() . ' as v where w.company_id = a.company_id AND w.object_type_id = 130 AND w.map_id = a.id AND w.object_id = v.id AND v.deleted = 0)+ ( select count(*) from ' . $hpf->getTable() . ' as z where z.round_interval_policy_id = a.id and z.deleted = 0 ) ) 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->rs = $this->db->Execute($query, $ph); }
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; }