function getByCompanyId($company_id, $where = NULL, $order = NULL)
{
if ($company_id == '') {
return FALSE;
}
$cdf = new CompanyDeductionFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $cdf->getTable() . ' as cdf ON a.company_deduction_id = cdf.id
where
cdf.company_id = ?
AND ( cdf.deleted = 0 )
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order);
$this->ExecuteSQL($query, $ph);
return $this;
}
function getByCompanyIdAndUserId($company_id, $user_id, $where = NULL, $order = NULL)
{
if ($company_id == '') {
return FALSE;
}
if ($user_id == '') {
return FALSE;
}
if ($order == NULL) {
$order = array('c.status_id' => 'asc', 'c.calculation_order' => 'asc');
$strict = FALSE;
} else {
$strict = TRUE;
}
$uf = new UserFactory();
$cdf = new CompanyDeductionFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*
from ' . $this->getTable() . ' as a,
' . $uf->getTable() . ' as b,
' . $cdf->getTable() . ' as c
where
a.user_id = b.id
AND a.company_deduction_id = c.id
AND b.company_id = ?
AND a.user_id in (' . $this->getListSQL($user_id, $ph) . ')
AND (a.deleted = 0 AND c.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();
if ($order == NULL) {
$order = array('uf.last_name' => 'asc', 'uf.first_name' => 'asc');
$strict = FALSE;
} else {
//Always sort by last name,first name after other columns
if (!isset($order['uf.last_name'])) {
$order['uf.last_name'] = 'asc';
}
if (!isset($order['uf.first_name'])) {
$order['uf.first_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();
$cdf = new CompanyDeductionFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
uf.first_name as first_name,
uf.last_name as last_name,
uf.country as country,
uf.province as province,
cdf.name as name,
cdf.status_id as status_id,
cdf.type_id as type_id,
cdf.calculation_id as calculation_id,
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 ' . $uf->getTable() . ' as uf ON ( a.user_id = uf.id AND uf.deleted = 0 )
LEFT JOIN ' . $cdf->getTable() . ' as cdf ON ( a.company_deduction_id = cdf.id AND cdf.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 uf.company_id = ?
';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.user_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('a.id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL;
$query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('a.user_id', $filter_data['user_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['company_deduction_id']) ? $this->getWhereClauseSQL('a.company_deduction_id', $filter_data['company_deduction_id'], 'numeric_list', $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;
/*
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.user_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 a.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 a.user_id in ('. $this->getListSQL($filter_data['user_id'], $ph) .') ';
}
if ( isset($filter_data['company_deduction_id']) AND isset($filter_data['company_deduction_id'][0]) AND !in_array(-1, (array)$filter_data['company_deduction_id']) ) {
$query .= ' AND a.company_deduction_id in ('. $this->getListSQL($filter_data['company_deduction_id'], $ph) .') ';
}
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);
$this->ExecuteSQL($query, $ph, $limit, $page);
return $this;
}
