function getAPIReportByCompanyIdAndArrayCriteria($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('default_branch', 'default_department', 'group', 'title', 'currency', 'pay_period_transaction_date', 'pay_stub_transaction_date', 'user_id'); if ($order == NULL) { $order = array('b.user_id' => 'asc'); $strict = FALSE; } else { $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $ppf = new PayPeriodFactory(); $bf = new BranchFactory(); $df = new DepartmentFactory(); $ugf = new UserGroupFactory(); $utf = new UserTitleFactory(); $psf = new PayStubFactory(); $uf = new UserFactory(); $psaf = new PayStubAmendmentFactory(); $ph = array('company_id' => $company_id); $query = ' select a.*, b.user_id as user_id, b.pay_period_id as pay_period_id, ppf.start_date as pay_period_start_date, ppf.end_date as pay_period_end_date, ppf.transaction_date as pay_period_transaction_date, b.start_date as pay_stub_start_date, b.end_date as pay_stub_end_date, b.transaction_date as pay_stub_transaction_date, b.currency_id as currency_id, b.currency_rate as currency_rate, a.pay_stub_entry_name_id as pay_stub_entry_name_id, a.rate as rate, a.units as units, a.amount as amount, a.ytd_amount as ytd_amount from ( select aa.pay_stub_id as pay_stub_id, aa.pay_stub_entry_name_id as pay_stub_entry_name_id, avg(aa.rate) as rate, sum(aa.units) as units, sum(aa.amount) as amount, max(aa.ytd_amount) as ytd_amount from ' . $this->getTable() . ' as aa LEFT JOIN ' . $psaf->getTable() . ' as hh ON aa.pay_stub_amendment_id = hh.id LEFT JOIN ' . $psf->getTable() . ' as bb ON aa.pay_stub_id = bb.id LEFT JOIN ' . $uf->getTable() . ' as cc ON bb.user_id = cc.id LEFT JOIN ' . $bf->getTable() . ' as dd ON cc.default_branch_id = dd.id LEFT JOIN ' . $df->getTable() . ' as ee ON cc.default_department_id = ee.id LEFT JOIN ' . $ugf->getTable() . ' as ff ON cc.group_id = ff.id LEFT JOIN ' . $utf->getTable() . ' as gg ON cc.title_id = gg.id where cc.company_id = ? '; $query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('cc.id', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('cc.id', $filter_data['id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('cc.id', $filter_data['user_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['include_user_id']) ? $this->getWhereClauseSQL('cc.id', $filter_data['include_user_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['exclude_user_id']) ? $this->getWhereClauseSQL('cc.id', $filter_data['exclude_user_id'], 'not_numeric_list', $ph) : NULL; $query .= isset($filter_data['status_id']) ? $this->getWhereClauseSQL('cc.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL; if (isset($filter_data['exclude_ytd_adjustment']) and (bool) $filter_data['exclude_ytd_adjustment'] == TRUE) { $query .= ' AND ( hh.ytd_adjustment is NULL OR hh.ytd_adjustment = 0 )'; } 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 .= isset($filter_data['group_id']) ? $this->getWhereClauseSQL('cc.group_id', $filter_data['group_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_branch_id']) ? $this->getWhereClauseSQL('cc.default_branch_id', $filter_data['default_branch_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['default_department_id']) ? $this->getWhereClauseSQL('cc.default_department_id', $filter_data['default_department_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['title_id']) ? $this->getWhereClauseSQL('cc.title_id', $filter_data['title_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['sex_id']) ? $this->getWhereClauseSQL('cc.sex_id', $filter_data['sex_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['currency_id']) ? $this->getWhereClauseSQL('bb.currency_id', $filter_data['currency_id'], 'numeric_list', $ph) : NULL; $query .= isset($filter_data['pay_period_id']) ? $this->getWhereClauseSQL('bb.pay_period_id', $filter_data['pay_period_id'], 'numeric_list', $ph) : NULL; //$query .= ( isset($filter_data['tag']) ) ? $this->getWhereClauseSQL( 'a.id', array( 'company_id' => $company_id, 'object_type_id' => 200, 'tag' => $filter_data['tag'] ), 'tag', $ph ) : NULL; if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') { $ph[] = $this->db->BindTimeStamp(strtolower(trim($filter_data['start_date']))); $query .= ' AND bb.transaction_date >= ?'; } if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') { $ph[] = $this->db->BindTimeStamp(strtolower(trim($filter_data['end_date']))); $query .= ' AND bb.transaction_date <= ?'; } /* if ( isset($filter_data['transaction_date']) AND trim($filter_data['transaction_date']) != '' ) { $ph[] = $this->db->BindTimeStamp( strtolower(trim($filter_data['transaction_date'])) ); $query .= ' AND bb.transaction_date = ?'; } */ $query .= ' AND (aa.deleted = 0 AND bb.deleted = 0 AND cc.deleted=0) group by aa.pay_stub_id,aa.pay_stub_entry_name_id ) a LEFT JOIN ' . $psf->getTable() . ' as b ON a.pay_stub_id = b.id LEFT JOIN ' . $uf->getTable() . ' as c ON b.user_id = c.id LEFT JOIN ' . $ppf->getTable() . ' as ppf ON b.pay_period_id = ppf.id where 1=1 '; $query .= ' AND (c.deleted=0) '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); //Debug::Arr($ph, 'Query: '. $query, __FILE__, __LINE__, __METHOD__,10); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }