function getOrphansByUserId($user_id, $where = NULL, $order = NULL)
{
if ($user_id == '') {
return FALSE;
}
$apf = new AccrualPolicyFactory();
$udtf = new UserDateTotalFactory();
$udf = new UserDateFactory();
$ph = array('user_id' => $user_id);
//Make sure we check if user_date rows are deleted where user_date_total rows are not.
$query = '
select a.*
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $udtf->getTable() . ' as b ON a.user_date_total_id = b.id
LEFT JOIN ' . $udf->getTable() . ' as c ON b.user_date_id = c.id
LEFT JOIN ' . $apf->getTable() . ' as d ON a.accrual_policy_id = d.id
where a.user_id = ?
AND (
( b.id is NULL OR b.deleted = 1 )
OR
( b.deleted = 0 AND ( c.id is NULL OR c.deleted = 1) )
)
AND ( a.type_id = 10 OR a.type_id = 20 OR ( a.type_id = 75 AND d.type_id = 30 ) )
AND a.deleted = 0';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order);
$this->rs = $this->db->Execute($query, $ph);
return $this;
}
function createAccrualPolicy($company_id, $type)
{
$apf = new AccrualPolicyFactory();
$apf->setCompany($company_id);
switch ($type) {
case 10:
//Bank Time
$apf->setName('Bank Time');
$apf->setType(10);
break;
case 20:
//Calendar Based: Vacation/PTO
$apf->setName('Personal Time Off (PTO)/Vacation');
$apf->setType(20);
$apf->setApplyFrequency(10);
$apf->setMilestoneRolloverHireDate(TRUE);
$apf->setMinimumEmployedDays(30);
break;
case 30:
//Calendar Based: Vacation/PTO
$apf->setName('Sick Time');
$apf->setType(20);
$apf->setApplyFrequency(10);
$apf->setMilestoneRolloverHireDate(TRUE);
$apf->setMinimumEmployedDays(30);
break;
}
if ($apf->isValid()) {
$insert_id = $apf->Save();
Debug::Text('Accrual Policy ID: ' . $insert_id, __FILE__, __LINE__, __METHOD__, 10);
$apmf = new AccrualPolicyMilestoneFactory();
if ($type == 20) {
$apmf->setAccrualPolicy($insert_id);
$apmf->setLengthOfService(1);
$apmf->setLengthOfServiceUnit(40);
$apmf->setAccrualRate(3600 * 8 * 5);
$apmf->setMaximumTime(3600 * 8 * 5);
if ($apmf->isValid()) {
Debug::Text('Saving Milestone...', __FILE__, __LINE__, __METHOD__, 10);
$apmf->Save();
}
$apmf->setAccrualPolicy($insert_id);
$apmf->setLengthOfService(2);
$apmf->setLengthOfServiceUnit(40);
$apmf->setAccrualRate(3600 * 8 * 10);
$apmf->setMaximumTime(3600 * 8 * 10);
if ($apmf->isValid()) {
Debug::Text('Saving Milestone...', __FILE__, __LINE__, __METHOD__, 10);
$apmf->Save();
}
$apmf->setAccrualPolicy($insert_id);
$apmf->setLengthOfService(3);
$apmf->setLengthOfServiceUnit(40);
$apmf->setAccrualRate(3600 * 8 * 15);
$apmf->setMaximumTime(3600 * 8 * 15);
if ($apmf->isValid()) {
Debug::Text('Saving Milestone...', __FILE__, __LINE__, __METHOD__, 10);
$apmf->Save();
}
} elseif ($type == 30) {
$apmf->setAccrualPolicy($insert_id);
$apmf->setLengthOfService(1);
$apmf->setLengthOfServiceUnit(10);
$apmf->setAccrualRate(3600 * 8 * 3);
$apmf->setMaximumTime(3600 * 8 * 3);
if ($apmf->isValid()) {
Debug::Text('Saving Milestone...', __FILE__, __LINE__, __METHOD__, 10);
$apmf->Save();
}
$apmf->setAccrualPolicy($insert_id);
$apmf->setLengthOfService(1);
$apmf->setLengthOfServiceUnit(40);
$apmf->setAccrualRate(3600 * 8 * 6);
$apmf->setMaximumTime(3600 * 8 * 6);
if ($apmf->isValid()) {
Debug::Text('Saving Milestone...', __FILE__, __LINE__, __METHOD__, 10);
$apmf->Save();
}
}
return $insert_id;
}
Debug::Text('Failed Creating Accrual Policy!', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
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('type_id');
$sort_column_aliases = array('type' => 'type_id');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('type_id' => 'asc', 'name' => 'asc');
$strict = FALSE;
} else {
//Always try to order by status first so INACTIVE employees go to the bottom.
if (!isset($order['type_id'])) {
$order = Misc::prependArray(array('type_id' => 'asc'), $order);
}
//Always sort by last name,first name after other columns
if (!isset($order['name'])) {
$order['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();
$apf = new AccrualPolicyFactory();
$cgmf = new CompanyGenericMapFactory();
$pgf = new PolicyGroupFactory();
$pguf = new PolicyGroupUserFactory();
$ph = array('company_id' => $company_id);
$query = '
select DISTINCT a.*,
apf.name as accrual_policy,
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 ' . $cgmf->getTable() . ' as cgmf ON ( a.id = cgmf.map_id AND cgmf.object_type_id = 170 )
LEFT JOIN ' . $pgf->getTable() . ' as pgf ON ( cgmf.object_id = pgf.id AND pgf.deleted = 0 )
LEFT JOIN ' . $pguf->getTable() . ' as pguf ON ( pguf.policy_group_id = pgf.id )
LEFT JOIN ' . $apf->getTable() . ' as apf ON ( a.accrual_policy_id = apf.id AND apf.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 a.company_id = ?
';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.created_by', $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;
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;
$query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('pguf.user_id', $filter_data['user_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['pay_stub_entry_account_id']) ? $this->getWhereClauseSQL('a.pay_stub_entry_account_id', $filter_data['pay_stub_entry_account_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['wage_group_id']) ? $this->getWhereClauseSQL('a.wage_group_id', $filter_data['wage_group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['name']) ? $this->getWhereClauseSQL('a.name', $filter_data['name'], 'text', $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;
$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;
}
if (isset($data['milestone_rows'])) {
foreach ($data['milestone_rows'] as $milestone_row_id => $milestone_row) {
if ($data['type_id'] == 20 and isset($milestone_row['accrual_rate']) and $milestone_row['accrual_rate'] != '') {
$data['milestone_rows'][$milestone_row_id]['accrual_rate'] = TTDate::parseTimeUnit($milestone_row['accrual_rate']);
}
if (isset($milestone_row['maximum_time']) and $milestone_row['maximum_time'] != '') {
$data['milestone_rows'][$milestone_row_id]['maximum_time'] = TTDate::parseTimeUnit($milestone_row['maximum_time']);
}
/*
if ( isset($milestone_row['minimum_time']) AND $milestone_row['minimum_time'] != '' ) {
$data['milestone_rows'][$milestone_row_id]['minimum_time'] = TTDate::parseTimeUnit($milestone_row['minimum_time'] );
}
*/
}
}
$apf = new AccrualPolicyFactory();
$apmf = new AccrualPolicyMilestoneFactory();
$action = Misc::findSubmitButton();
$action = strtolower($action);
switch ($action) {
case 'delete':
//Debug::setVerbosity(11);
if (count($ids) > 0) {
foreach ($ids as $apm_id) {
if ($apm_id > 0) {
Debug::Text('cDeleting Milestone Row ID: ' . $apm_id, __FILE__, __LINE__, __METHOD__, 10);
$apmlf = new AccrualPolicyMilestoneListFactory();
$apmlf->getById($apm_id);
if ($apmlf->getRecordCount() == 1) {
foreach ($apmlf as $apm_obj) {
$apm_obj->setDeleted(TRUE);
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 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('type_id' => 'asc', 'name' => 'asc', 'length_of_service_days' => 'asc');
$strict = FALSE;
} else {
if (!isset($order['type_id'])) {
$order = Misc::prependArray(array('type_id' => 'asc'), $order);
}
if (!isset($order['name'])) {
$order['name'] = 'asc';
}
if (!isset($order['length_of_service_days'])) {
$order['length_of_service_days'] = '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();
$apf = new AccrualPolicyFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
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 ' . $apf->getTable() . ' as b ON ( a.accrual_policy_id = b.id AND b.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 = ?
';
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.created_by 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['accrual_policy_id']) and isset($filter_data['accrual_policy_id'][0]) and !in_array(-1, (array) $filter_data['accrual_policy_id'])) {
$query .= ' AND a.accrual_policy_id in (' . $this->getListSQL($filter_data['accrual_policy_id'], $ph) . ') ';
}
$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;
$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;
}
function getByUserIdAndCompanyIdAndEnablePayStubBalanceDisplay($user_id, $company_id, $enable_pay_stub_balance_display, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
if ($user_id == '') {
return FALSE;
}
if ($company_id == '') {
return FALSE;
}
$additional_order_fields = array('a.balance', 'c.name');
if ($order == NULL) {
$order = array('c.name' => 'asc');
$strict = FALSE;
} else {
$strict = TRUE;
}
$uf = new UserFactory();
$apf = new AccrualPolicyFactory();
$ph = array('company_id' => $company_id, 'enable_pay_stub_balance_display' => (int) $enable_pay_stub_balance_display);
$query = '
select a.*,
c.name as name
from ' . $this->getTable() . ' as a,
' . $uf->getTable() . ' as b,
' . $apf->getTable() . ' as c
where a.user_id = b.id
AND a.accrual_policy_id = c.id
AND b.company_id = ?
AND c.enable_pay_stub_balance_display = ?
AND a.user_id in (' . $this->getListSQL($user_id, $ph) . ')
AND a.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;
}
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('accrual_policy', 'accrual_policy_type_id', 'first_name', 'last_name', 'name', 'default_branch', 'default_department');
$sort_column_aliases = array('accrual_policy_type' => 'accrual_policy_type_id', 'group' => 'e.name');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('last_name' => 'asc', 'accrual_policy_id' => 'asc');
$strict = FALSE;
} else {
//Always sort by last name,first name after other columns
/*
if ( !isset($order['effective_date']) ) {
$order['effective_date'] = 'desc';
}
*/
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$apf = new AccrualPolicyFactory();
$af = new AccrualFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
ab.name as accrual_policy,
ab.type_id as accrual_policy_type_id,
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 "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 ' . $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 )
where b.company_id = ?
AND EXISTS ( select 1 from ' . $af->getTable() . ' as af WHERE af.accrual_policy_id = a.accrual_policy_id AND a.user_id = af.user_id AND af.deleted = 0 )
';
$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('ab.type_id', $filter_data['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;
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['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['group']) ? $this->getWhereClauseSQL('e.name', $filter_data['group'], 'text', $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_branch']) ? $this->getWhereClauseSQL('c.name', $filter_data['default_branch'], 'text', $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['default_department']) ? $this->getWhereClauseSQL('d.name', $filter_data['default_department'], 'text', $ph) : NULL;
$query .= isset($filter_data['title_id']) ? $this->getWhereClauseSQL('a.title_id', $filter_data['title_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['title']) ? $this->getWhereClauseSQL('e.name', $filter_data['title'], 'text', $ph) : NULL;
$query .= isset($filter_data['first_name']) ? $this->getWhereClauseSQL('b.first_name', $filter_data['first_name'], 'text_metaphone', $ph) : NULL;
$query .= isset($filter_data['last_name']) ? $this->getWhereClauseSQL('b.last_name', $filter_data['last_name'], 'text_metaphone', $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 .= '
AND a.deleted = 0
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
$this->ExecuteSQL($query, $ph, $limit, $page);
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('type_id' => 'asc', 'name' => 'asc');
$strict = FALSE;
} else {
//Always try to order by status first so INACTIVE employees go to the bottom.
if (!isset($order['type_id'])) {
$order = Misc::prependArray(array('type_id' => 'asc'), $order);
}
//Always sort by last name,first name after other columns
if (!isset($order['name'])) {
$order['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();
$apf = new AccrualPolicyFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
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 ' . $apf->getTable() . ' as b ON ( a.accrual_policy_id = b.id AND b.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 = ?
';
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['accrual_policy_id']) and isset($filter_data['accrual_policy_id'][0]) and !in_array(-1, (array) $filter_data['accrual_policy_id'])) {
$query .= ' AND a.accrual_policy_id in (' . $this->getListSQL($filter_data['accrual_policy_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);
if ($limit == NULL) {
$this->rs = $this->db->Execute($query, $ph);
} else {
$this->rs = $this->db->PageExecute($query, $limit, $page, $ph);
}
return $this;
}