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