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