function getUnitSumByPayStubIdAndType($pay_stub_id, $type_id, $where = NULL, $order = NULL)
{
if ($pay_stub_id == '') {
return FALSE;
}
if ($type_id == '') {
return FALSE;
}
//$psenlf = new PayStubEntryNameListFactory();
$psealf = new PayStubEntryAccountListFactory();
$ph = array('pay_stub_id' => $pay_stub_id, 'type_id' => $type_id);
$query = '
select sum(units)
from ' . $this->getTable() . ' as a,
' . $psealf->getTable() . ' as b
where a.pay_stub_entry_name_id = b.id
AND a.pay_stub_id = ?
AND b.type_id = ?
AND a.deleted = 0
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order);
$sum = $this->db->GetOne($query, $ph);
if ($sum !== FALSE or $sum !== NULL) {
Debug::text('Unit Sum: ' . $sum, __FILE__, __LINE__, __METHOD__, 10);
return $sum;
}
Debug::text('Unit Sum is NULL', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
function getSearchByCompanyIdAndArrayCriteria($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('b.last_name', 'b.first_name');
if ($order == NULL) {
$order = array('a.effective_date' => 'desc', 'a.status_id' => 'asc', 'b.last_name' => 'asc');
$strict = FALSE;
} else {
//Always try to order by status first so UNPAID employees go to the bottom.
if (isset($order['last_name'])) {
$order['b.last_name'] = $order['last_name'];
unset($order['last_name']);
}
if (isset($order['first_name'])) {
$order['b.first_name'] = $order['first_name'];
unset($order['first_name']);
}
if (isset($order['type'])) {
$order['type_id'] = $order['type'];
unset($order['type']);
}
if (isset($order['status'])) {
$order['status_id'] = $order['status'];
unset($order['status']);
}
if (isset($order['effective_date'])) {
$order['b.last_name'] = 'asc';
} else {
$order['a.effective_date'] = 'desc';
}
$strict = TRUE;
}
//Debug::Arr($order,'bOrder Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$psealf = new PayStubEntryAccountListFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $uf->getTable() . ' as b ON a.user_id = b.id
LEFT JOIN ' . $psealf->getTable() . ' as c ON a.pay_stub_entry_name_id = c.id
where b.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 b.id in (' . $this->getListSQL($filter_data['permission_children_ids'], $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 b.id in (' . $this->getListSQL($filter_data['user_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['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 b.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 b.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 b.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 b.title_id in (' . $this->getListSQL($filter_data['title_id'], $ph) . ') ';
}
if (isset($filter_data['recurring_ps_amendment_id']) and isset($filter_data['recurring_ps_amendment_id'][0]) and !in_array(-1, (array) $filter_data['recurring_ps_amendment_id'])) {
$query .= ' AND a.recurring_ps_amendment_id in (' . $this->getListSQL($filter_data['recurring_ps_amendment_id'], $ph) . ') ';
}
if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') {
$ph[] = strtolower(trim($filter_data['start_date']));
$query .= ' AND a.effective_date >= ?';
}
if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') {
$ph[] = strtolower(trim($filter_data['end_date']));
$query .= ' AND a.effective_date <= ?';
}
if (isset($filter_data['effective_date']) and trim($filter_data['effective_date']) != '') {
$ph[] = strtolower(trim($filter_data['effective_date']));
$query .= ' AND a.effective_date = ?';
}
$query .= '
AND ( a.deleted = 0 AND b.deleted = 0 AND c.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;
}
