function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (isset($filter_data['user_status_id'])) { $filter_data['status_id'] = $filter_data['user_status_id']; } if (isset($filter_data['user_group_id'])) { $filter_data['group_id'] = $filter_data['user_group_id']; } if (isset($filter_data['user_title_id'])) { $filter_data['title_id'] = $filter_data['user_title_id']; } if (isset($filter_data['include_user_id'])) { $filter_data['user_id'] = $filter_data['include_user_id']; } if (isset($filter_data['exclude_user_id'])) { $filter_data['exclude_id'] = $filter_data['exclude_user_id']; } if (isset($filter_data['accrual_type_id'])) { $filter_data['type_id'] = $filter_data['accrual_type_id']; } 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']); } } if (isset($filter_data['accrual_type_id'])) { $filter_data['type_id'] = $filter_data['accrual_type_id']; } $additional_order_fields = array('accrual_policy', 'accrual_policy_type_id', 'date_stamp'); $sort_column_aliases = array('accrual_policy_type' => 'accrual_policy_type_id', 'type' => 'type_id'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('accrual_policy_id' => 'asc', 'date_stamp' => 'desc'); $strict = FALSE; } else { $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $uf = new UserFactory(); $udtf = new UserDateTotalFactory(); $udf = new UserDateFactory(); $bf = new BranchFactory(); $df = new DepartmentFactory(); $ugf = new UserGroupFactory(); $utf = new UserTitleFactory(); $apf = new AccrualPolicyFactory(); $ph = array('company_id' => $company_id); $query = ' select a.*, ab.name as accrual_policy, ab.type_id as accrual_policy_type_id, CASE WHEN udf.date_stamp is NOT NULL THEN udf.date_stamp ELSE a.time_stamp END as date_stamp, b.first_name as first_name, b.last_name as last_name, b.country as country, b.province as province, c.id as default_branch_id, c.name as default_branch, d.id as default_department_id, d.name as default_department, e.id as group_id, e.name as "user_group", f.id as title_id, f.name as title from ' . $this->getTable() . ' as a LEFT JOIN ' . $apf->getTable() . ' as ab ON ( a.accrual_policy_id = ab.id AND ab.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as b ON ( a.user_id = b.id AND b.deleted = 0 ) LEFT JOIN ' . $udtf->getTable() . ' as udtf ON ( a.user_date_total_id = udtf.id AND udtf.deleted = 0 ) LEFT JOIN ' . $udf->getTable() . ' as udf ON ( udtf.user_date_id = udf.id AND udf.deleted = 0 ) LEFT JOIN ' . $bf->getTable() . ' as c ON ( b.default_branch_id = c.id AND c.deleted = 0) LEFT JOIN ' . $df->getTable() . ' as d ON ( b.default_department_id = d.id AND d.deleted = 0) LEFT JOIN ' . $ugf->getTable() . ' as e ON ( b.group_id = e.id AND e.deleted = 0 ) LEFT JOIN ' . $utf->getTable() . ' as f ON ( b.title_id = f.id AND f.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 b.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['user_id']) ? $this->getWhereClauseSQL('a.user_id', $filter_data['user_id'], '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.user_id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL; if (isset($filter_data['type']) and trim($filter_data['type']) != '' and !isset($filter_data['type_id'])) { $filter_data['type_id'] = Option::getByFuzzyValue($filter_data['type'], $this->getOptions('type')); } $query .= isset($filter_data['type_id']) ? $this->getWhereClauseSQL('a.type_id', $filter_data['type_id'], 'numeric_list', $ph) : NULL; if (isset($filter_data['status']) and trim($filter_data['status']) != '' and !isset($filter_data['status_id'])) { $filter_data['status_id'] = Option::getByFuzzyValue($filter_data['status'], $this->getOptions('status')); } $query .= isset($filter_data['accrual_policy_type_id']) ? $this->getWhereClauseSQL('ab.type_id', $filter_data['accrual_policy_type_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['accrual_policy_id']) ? $this->getWhereClauseSQL('a.accrual_policy_id', $filter_data['accrual_policy_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['status_id']) ? $this->getWhereClauseSQL('b.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['group_id']) ? $this->getWhereClauseSQL('b.group_id', $filter_data['group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_branch_id']) ? $this->getWhereClauseSQL('b.default_branch_id', $filter_data['default_branch_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_department_id']) ? $this->getWhereClauseSQL('b.default_department_id', $filter_data['default_department_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['title_id']) ? $this->getWhereClauseSQL('b.title_id', $filter_data['title_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['country']) ? $this->getWhereClauseSQL('b.country', $filter_data['country'], 'upper_text_list', $ph) : NULL; $query .= isset($filter_data['province']) ? $this->getWhereClauseSQL('b.province', $filter_data['province'], 'upper_text_list', $ph) : NULL; $query .= isset($filter_data['pay_period_id']) ? $this->getWhereClauseSQL('udf.pay_period_id', $filter_data['pay_period_id'], 'numeric_list', $ph) : NULL; if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') { $ph[] = $this->db->BindDate($filter_data['start_date']); $ph[] = $this->db->BindDate($filter_data['start_date']); $query .= ' AND ( ( udf.date_stamp is NULL AND a.time_stamp >= ? ) OR ( udf.date_stamp is NOT NULL AND udf.date_stamp >= ? ) )'; } if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') { $ph[] = $this->db->BindDate($filter_data['end_date']); $ph[] = $this->db->BindDate($filter_data['end_date']); $query .= ' AND ( ( udf.date_stamp is NULL AND a.time_stamp <= ? ) OR ( udf.date_stamp is NOT NULL AND udf.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; //Make sure we exclude delete user_date_total records, so we match the accrual balances. $query .= ' AND ( ( a.user_date_total_id is NOT NULL AND udtf.id is NOT NULL AND udtf.deleted = 0 ) OR ( a.user_date_total_id IS NULL AND udtf.id is NULL ) ) AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); //Debug::Arr($ph,'Filter Data:'. $query, __FILE__, __LINE__, __METHOD__,10); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }
function getSumByUserIdAndAccrualPolicyId($user_id, $accrual_policy_id) { if ($user_id == '') { return FALSE; } if ($accrual_policy_id == '') { return FALSE; } $udtf = new UserDateTotalFactory(); $ph = array('user_id' => $user_id, 'accrual_policy_id' => $accrual_policy_id); $query = ' select sum(amount) as amount from ' . $this->getTable() . ' as a LEFT JOIN ' . $udtf->getTable() . ' as b ON a.user_date_total_id = b.id where a.user_id = ? AND a.accrual_policy_id = ? AND ( (a.user_date_total_id is NOT NULL AND b.id is NOT NULL) OR a.user_date_total_id IS NULL AND b.id is NULL ) AND a.deleted = 0'; $total = $this->db->GetOne($query, $ph); if ($total === FALSE) { $total = 0; } Debug::text('Balance: ' . $total, __FILE__, __LINE__, __METHOD__, 10); return $total; }
function postSave() { $this->removeCache($this->getId()); if ($this->getDeleted() == TRUE) { Debug::Text('UnAssign Hours from Branch: ' . $this->getId(), __FILE__, __LINE__, __METHOD__, 10); //Unassign hours from this branch. $pcf = new PunchControlFactory(); $udtf = new UserDateTotalFactory(); $uf = new UserFactory(); $sf = new StationFactory(); $sf_b = new ScheduleFactory(); $udf = new UserDefaultFactory(); $rstf = new RecurringScheduleTemplateFactory(); $query = 'update ' . $pcf->getTable() . ' set branch_id = 0 where branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $udtf->getTable() . ' set branch_id = 0 where branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $sf_b->getTable() . ' set branch_id = 0 where branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $uf->getTable() . ' set default_branch_id = 0 where company_id = ' . $this->getCompany() . ' AND default_branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $udf->getTable() . ' set default_branch_id = 0 where company_id = ' . $this->getCompany() . ' AND default_branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $sf->getTable() . ' set branch_id = 0 where company_id = ' . $this->getCompany() . ' AND branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $rstf->getTable() . ' set branch_id = 0 where branch_id = ' . $this->getId(); $this->db->Execute($query); } return TRUE; }