function createPayPeriodSchedule()
{
$ppsf = new PayPeriodScheduleFactory();
$ppsf->setCompany($this->company_id);
//$ppsf->setName( 'Bi-Weekly'.rand(1000,9999) );
$ppsf->setName('Bi-Weekly');
$ppsf->setDescription('Pay every two weeks');
$ppsf->setType(20);
$ppsf->setStartWeekDay(0);
$anchor_date = TTDate::getBeginWeekEpoch(TTDate::getBeginYearEpoch(time()) - 86400 * (7 * 6));
//Start 6 weeks ago
$ppsf->setAnchorDate($anchor_date);
$ppsf->setStartDayOfWeek(TTDate::getDayOfWeek($anchor_date));
$ppsf->setTransactionDate(7);
$ppsf->setTransactionDateBusinessDay(TRUE);
$ppsf->setTimeZone('PST8PDT');
$ppsf->setDayStartTime(0);
$ppsf->setNewDayTriggerTime(4 * 3600);
$ppsf->setMaximumShiftTime(16 * 3600);
$ppsf->setShiftAssignedDay(10);
$ppsf->setEnableInitialPayPeriods(FALSE);
if ($ppsf->isValid()) {
$insert_id = $ppsf->Save(FALSE);
Debug::Text('Pay Period Schedule ID: ' . $insert_id, __FILE__, __LINE__, __METHOD__, 10);
$ppsf->setUser(array($this->user_id));
$ppsf->Save();
$this->pay_period_schedule_id = $insert_id;
return $insert_id;
}
Debug::Text('Failed Creating Pay Period Schedule!', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
function createPayPeriodSchedule($type, $start_dow, $transaction_dow, $primary_dom, $secondary_dom, $primary_transaction_dom, $secondary_transaction_dom, $transaction_bd, $day_start_time = '00:00')
{
$ppsf = new PayPeriodScheduleFactory();
$ppsf->setCompany($this->company_id);
Debug::text('zzzCompany ID: ' . $this->company_id, __FILE__, __LINE__, __METHOD__, 10);
$ppsf->setName('test_' . rand(1000, 99999));
$ppsf->setDescription('test');
/*
20 => 'Bi-Weekly',
30 => 'Semi-Monthly',
40 => 'Monthly + Advance'
*/
$ppsf->setType($type);
$day_start_time = TTDate::parseTimeUnit($day_start_time);
Debug::text('parsed Day Start Time: ' . $day_start_time, __FILE__, __LINE__, __METHOD__, 10);
$ppsf->setDayStartTime($day_start_time);
if ($type == 10 or $type == 20) {
$ppsf->setStartDayOfWeek($start_dow);
$ppsf->setTransactionDate($transaction_dow);
} elseif ($type == 30) {
$ppsf->setPrimaryDayOfMonth($primary_dom);
$ppsf->setSecondaryDayOfMonth($secondary_dom);
$ppsf->setPrimaryTransactionDayOfMonth($primary_transaction_dom);
$ppsf->setSecondaryTransactionDayOfMonth($secondary_transaction_dom);
} elseif ($type == 50) {
$ppsf->setPrimaryDayOfMonth($primary_dom);
$ppsf->setPrimaryTransactionDayOfMonth($primary_transaction_dom);
}
$ppsf->setTransactionDateBusinessDay((bool) $transaction_bd);
$ppsf->setTimeZone('PST8PDT');
$ppsf->setEnableInitialPayPeriods(FALSE);
if ($ppsf->isValid()) {
$pp_schedule_id = $ppsf->Save();
$ppslf = new PayPeriodScheduleListFactory();
$ret_obj = $ppslf->getById($pp_schedule_id)->getCurrent();
return $ret_obj;
}
return FALSE;
}
function createPayPeriodSchedule($company_id, $user_ids)
{
$ppsf = new PayPeriodScheduleFactory();
$ppsf->setCompany($company_id);
$ppsf->setName('Bi-Weekly');
$ppsf->setDescription('Pay every two weeks');
$ppsf->setType(20);
$ppsf->setStartWeekDay(0);
//$anchor_date = TTDate::getBeginWeekEpoch( (time()-(86400*42)) ); //Start 6 weeks ago
$anchor_date = TTDate::getBeginWeekEpoch(time() - 86400 * 14);
//Start 6 weeks ago
$ppsf->setAnchorDate($anchor_date);
$ppsf->setStartDayOfWeek(TTDate::getDayOfWeek($anchor_date));
$ppsf->setTransactionDate(7);
$ppsf->setTransactionDateBusinessDay(TRUE);
/*
$ppsf->setPrimaryDate( ($anchor_date+(86400*14)) );
$ppsf->setPrimaryDateLastDayOfMonth( FALSE );
$ppsf->setPrimaryTransactionDate( ($anchor_date+(86400*21)) );
$ppsf->setPrimaryTransactionDateLastDayOfMonth( FALSE );
$ppsf->setPrimaryTransactionDateBusinessDay( TRUE );
$ppsf->setSecondaryDate( ($anchor_date+(86400*28)) );
$ppsf->setSecondaryDateLastDayOfMonth( FALSE );
$ppsf->setSecondaryTransactionDate( ($anchor_date+(86400*35)) );
$ppsf->setSecondaryTransactionDateLastDayOfMonth( FALSE );
$ppsf->setSecondaryTransactionDateBusinessDay( TRUE );
*/
$ppsf->setDayStartTime(0);
$ppsf->setShiftAssignedDay(10);
//Day the shift starts on.
$ppsf->setNewDayTriggerTime(4 * 3600);
$ppsf->setMaximumShiftTime(16 * 3600);
if ($ppsf->isValid()) {
$insert_id = $ppsf->Save(FALSE);
Debug::Text('Pay Period Schedule ID: ' . $insert_id, __FILE__, __LINE__, __METHOD__, 10);
//Dont create pay periods twice.
$ppsf->setEnableInitialPayPeriods(FALSE);
$ppsf->setUser($user_ids);
$ppsf->Save();
return $insert_id;
}
Debug::Text('Failed Creating Pay Period Schedule!', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
function getShifts($start_date, $end_date, &$holiday_data = array(), &$branch_options = array(), &$department_options = array(), &$n, &$shifts = array(), &$shifts_index = array(), $open_shift_conflict_index = array(), $permission_children_ids = NULL)
{
//Debug::text('Start Date: '. TTDate::getDate('DATE+TIME', $start_date) .' End Date: '. TTDate::getDate('DATE+TIME', $end_date), __FILE__, __LINE__, __METHOD__, 10);
$recurring_schedule_control_start_date = TTDate::strtotime($this->getColumn('recurring_schedule_control_start_date'));
//Debug::text('Recurring Schedule Control Start Date: '. TTDate::getDate('DATE+TIME', $recurring_schedule_control_start_date),__FILE__, __LINE__, __METHOD__, 10);
$current_template_week = $this->getColumn('remapped_week');
$max_week = $this->getColumn('max_week');
//Debug::text('Template Week: '. $current_template_week .' Max Week: '. $this->getColumn('max_week') .' ReMapped Week: '. $this->getColumn('remapped_week') ,__FILE__, __LINE__, __METHOD__, 10);
if ($recurring_schedule_control_start_date == '') {
return FALSE;
}
//Get week of start_date
$start_date_week = TTDate::getBeginWeekEpoch($recurring_schedule_control_start_date, 0);
//Start week on Sunday to match Recurring Schedule.
//Debug::text('Week of Start Date: '. $start_date_week ,__FILE__, __LINE__, __METHOD__, 10);
$apf = TTnew('AbsencePolicyFactory');
$absence_policy_paid_type_options = $apf->getOptions('paid_type');
for ($i = $start_date; $i <= $end_date; $i += 86400 + 43200) {
//Handle DST by adding 12hrs to the date to get the mid-day epoch, then forcing it back to the beginning of the day.
$i = TTDate::getBeginDayEpoch($i);
if ($this->getColumn('hire_date') != '' and $i < $this->getColumn('hire_date') or $this->getColumn('termination_date') != '' and $i > $this->getColumn('termination_date')) {
//Debug::text('Skipping due to Hire/Termination date: User ID: '. $this->getColumn('user_id') .' I: '. $i .' Hire Date: '. $this->getColumn('hire_date') .' Termination Date: '. $this->getColumn('termination_date') ,__FILE__, __LINE__, __METHOD__, 10);
continue;
}
//This needs to take into account weeks spanning January 1st of each year. Where the week goes from 53 to 1.
//Rather then use the week of the year, calculate the weeks between the recurring schedule start date and now.
$current_week = round((TTDate::getBeginWeekEpoch($i, 0) - $start_date_week) / 604800);
//Find out which week we are on based on the recurring schedule start date. Use round due to DST the week might be 6.9 or 7.1, so we need to round to the nearest full week.
//Debug::text('I: '. $i .' User ID: '. $this->getColumn('user_id') .' Current Date: '. TTDate::getDate('DATE+TIME', $i) .' Current Week: '. $current_week .' Start Week: '. $start_date_week,__FILE__, __LINE__, __METHOD__, 10);
$template_week = $current_week % $max_week + 1;
//Debug::text('Template Week: '. $template_week .' Max Week: '. $max_week,__FILE__, __LINE__, __METHOD__, 10);
if ($template_week == $current_template_week) {
//Debug::text('Current Date: '. TTDate::getDate('DATE+TIME', $i) .' Current Week: '. $current_week,__FILE__, __LINE__, __METHOD__, 10);
//Debug::text(' Template Week: '. $template_week .' Max Week: '. $max_week,__FILE__, __LINE__, __METHOD__, 10);
if ($this->isActiveShiftDay($i)) {
//Debug::text(' Active Shift on this day...',__FILE__, __LINE__, __METHOD__, 10);
$start_time = TTDate::getTimeLockedDate($this->getStartTime(), $i);
$end_time = TTDate::getTimeLockedDate($this->getEndTime(), $i);
if ($end_time < $start_time) {
//Spans the day boundary, add 86400 to end_time
$end_time = $end_time + 86400;
//Debug::text(' Schedule spans day boundary, bumping endtime to next day: ',__FILE__, __LINE__, __METHOD__, 10);
}
$iso_date_stamp = TTDate::getISODateStamp(PayPeriodScheduleFactory::getShiftAssignedDate($start_time, $end_time, $this->getColumn('shift_assigned_day_id')));
//$iso_date_stamp = TTDate::getISODateStamp( $i );
$open_shift_multiplier = $this->getColumn('user_id') == 0 ? $this->getOpenShiftMultiplier() : 1;
//Debug::text('Open Shift Multiplier: '. $open_shift_multiplier,__FILE__, __LINE__, __METHOD__, 10);
for ($x = 0; $x < $open_shift_multiplier; $x++) {
//Check all non-OPEN shifts for conflicts.
if ($this->getColumn('user_id') > 0 and isset($shifts_index[$iso_date_stamp][$this->getColumn('user_id')])) {
//User has previous recurring schedule shifts, check for overlap.
//Loop over each employees shift for this day and check for conflicts
foreach ($shifts_index[$iso_date_stamp][$this->getColumn('user_id')] as $shift_key) {
if (isset($shifts[$iso_date_stamp][$shift_key])) {
//Must use parseDateTime() when called from the API due to date formats that strtotime() fails on.
if (TTDate::isTimeOverLap(defined('TIMETREX_API') ? TTDate::parseDateTime($shifts[$iso_date_stamp][$shift_key]['start_date']) : $shifts[$iso_date_stamp][$shift_key]['start_date'], defined('TIMETREX_API') ? TTDate::parseDateTime($shifts[$iso_date_stamp][$shift_key]['end_date']) : $shifts[$iso_date_stamp][$shift_key]['end_date'], $start_time, $end_time) == TRUE) {
//Debug::text(' Found overlapping recurring schedules! User ID: '. $this->getColumn('user_id') .' Start Time: '. $start_time,__FILE__, __LINE__, __METHOD__, 10);
continue 2;
}
}
}
unset($shift_key);
} elseif ($this->getColumn('user_id') == 0 and isset($shifts_index[$iso_date_stamp])) {
//Debug::text(' Checking OPEN shift conflicts... Date: '. $iso_date_stamp,__FILE__, __LINE__, __METHOD__, 10);
//Check all OPEN shifts for conflicts.
//This is special, since there can be multiple open shifts for the same branch,department,job,task, so we need to check if are conflicts with *any* employee.
//Do we allow conflicting shifts between committed and recurring OPEN shifts? For example what if there are two open shifts on the same day
//6AM-3PM (x2) and they want to override one of those shifts to 7AM-4PM? If we use this check:
// ( $shifts[$iso_date_stamp][$shift_key]['user_id'] > 0 OR ( isset($shifts[$iso_date_stamp][$shift_key]['id']) AND $shifts[$iso_date_stamp][$shift_key]['id'] > 0 ) )
//That allows committed OPEN shifts to override recurring open shifts, which is great, but it prevents adding additional open shifts that may
//also overlap unless they override all recurring shifts first. I think this is the trade-off we have to make as its more likely that they
//will adjust an open shift time rather than add/remove specific shifts. Removing recurring OPEN shifts can be done by making them ABSENT.
//This will also affect when recurring OPEN shifts are committed by preventing the shifts from doubling up.
foreach ($shifts_index[$iso_date_stamp] as $tmp_index_user_id => $tmp_index_arr) {
foreach ($tmp_index_arr as $shift_key) {
$tmp_start_date = defined('TIMETREX_API') ? TTDate::parseDateTime($shifts[$iso_date_stamp][$shift_key]['start_date']) : $shifts[$iso_date_stamp][$shift_key]['start_date'];
$tmp_end_date = defined('TIMETREX_API') ? TTDate::parseDateTime($shifts[$iso_date_stamp][$shift_key]['end_date']) : $shifts[$iso_date_stamp][$shift_key]['end_date'];
if (($shifts[$iso_date_stamp][$shift_key]['user_id'] > 0 or isset($shifts[$iso_date_stamp][$shift_key]['id']) and $shifts[$iso_date_stamp][$shift_key]['id'] > 0) and (!isset($open_shift_conflict_index['open'][$this->getID()][$shift_key]) and (isset($shifts[$iso_date_stamp][$shift_key]['id']) and !isset($open_shift_conflict_index['scheduled'][$shifts[$iso_date_stamp][$shift_key]['id']]))) and $this->getColumn('schedule_branch_id') == $shifts[$iso_date_stamp][$shift_key]['branch_id'] and $this->getColumn('schedule_department_id') == $shifts[$iso_date_stamp][$shift_key]['department_id'] and $this->getColumn('job_id') == $shifts[$iso_date_stamp][$shift_key]['job_id'] and $this->getColumn('job_item_id') == $shifts[$iso_date_stamp][$shift_key]['job_item_id'] and ($tmp_start_date == $start_time and $tmp_end_date == $end_time)) {
//Debug::text(' Found OPEN shift conflict... Skipping...! Shift Key: '. $shift_key,__FILE__, __LINE__, __METHOD__, 10);
//We need to track each shift_key that caused a conflict so it can't cause another conflict later on.
// Make sure we just track it on a per template basis though, otherwise the same $shift_key from a previous template can affect other templates.
// The above issue would show up as OPEN shifts not being overridden.
//We also need to track which scheduled shift that caused a conflict so it can't cause another one later on.
// This prevents a single scheduled shift from overriding multiple OPEN shifts of different times.
//However we need to be smarter about which shifts override which OPEN shifts...
// So if there are two open shifts, 10AM-4PM and 3:50PM-9PM, a 10AM-4PM scheduled shift overrides the OPEN shift that best fits it (10AM to 4PM, *not* 3:50-9PM)
// For now require an exact match to override an OPEN shift, if we start using partial schedules it gets much more complicated.
// Or we could introduce a hardcoded "fudge factor" setting (ie: 5 mins) that is always used instead.
$open_shift_conflict_index['open'][$this->getID()][$shift_key] = TRUE;
$open_shift_conflict_index['scheduled'][$shifts[$iso_date_stamp][$shift_key]['id']] = TRUE;
continue 3;
}
unset($tmp_start_date, $tmp_end_date);
}
}
unset($tmp_index_user_id, $tmp_index_arr);
}
//This check has to occurr after the committed schedule check, otherwise no committed schedules will appear.
if ($this->getColumn('recurring_schedule_control_start_date') != '' and $i < TTDate::strtotime($this->getColumn('recurring_schedule_control_start_date')) or $this->getColumn('recurring_schedule_control_end_date') != '' and $i > TTDate::strtotime($this->getColumn('recurring_schedule_control_end_date'))) {
//Debug::text('Skipping due to Recurring Schedule Start/End date: ID: '. $this->getColumn('id') .' User ID: '. $this->getColumn('user_id') .' I: '. $i .' Start Date: '. $this->getColumn('recurring_schedule_control_start_date') .' ('. TTDate::strtotime( $this->getColumn('recurring_schedule_control_start_date') ) .') End Date: '. $this->getColumn('recurring_schedule_control_end_date') ,__FILE__, __LINE__, __METHOD__, 10);
continue;
}
//Debug::text(' Start Date: '. TTDate::getDate('DATE+TIME', $start_time) .' End Date: '. TTDate::getDate('DATE+TIME', $end_time),__FILE__, __LINE__, __METHOD__, 10);
$status_id = $this->getColumn('status_id');
$absence_policy_id = $this->getColumn('absence_policy_id');
$absence_policy_type_id = $this->getColumn('absence_policy_type_id');
$absence_policy = $this->getColumn('absence_policy') != '' ? $this->getColumn('absence_policy') : NULL;
//Must be NULL to be converted to N/A
if (isset($holiday_data[$iso_date_stamp])) {
//We have to assume they are eligible, because we really won't know
//if they will have worked enough days or not. We could assume they
//work whatever their schedule is, but chances are they will be eligible then anyways.
//Debug::text(' Found Holiday on this day...',__FILE__, __LINE__, __METHOD__, 10);
$status_id = $holiday_data[$iso_date_stamp]['status_id'];
if (isset($holiday_data[$iso_date_stamp]['absence_policy_id'])) {
$absence_policy_id = $holiday_data[$iso_date_stamp]['absence_policy_id'];
$absence_policy_type_id = $holiday_data[$iso_date_stamp]['type_id'];
$absence_policy = $holiday_data[$iso_date_stamp]['absence_policy'];
}
}
$hourly_rate = Misc::MoneyFormat($this->getColumn('user_wage_hourly_rate'), FALSE);
if ($absence_policy_id > 0 and in_array($absence_policy_type_id, $absence_policy_paid_type_options) == FALSE) {
//UnPaid Absence.
$total_time_wage = Misc::MoneyFormat(0);
} else {
$total_time_wage = Misc::MoneyFormat(bcmul(TTDate::getHours($this->getTotalTime()), $hourly_rate), FALSE);
}
//Debug::text('I: '. $i .' N: '. $n .' User ID: '. $this->getColumn('user_id') .' Current Date: '. TTDate::getDate('DATE+TIME', $i) .' Current Week: '. $current_week .' Start Time: '. TTDate::getDate('DATE+TIME', $start_time ) .' Absence Policy: '. $absence_policy,__FILE__, __LINE__, __METHOD__, 10);
//$shifts[$iso_date_stamp][$this->getColumn('user_id').$start_time] = array(
$shifts[$iso_date_stamp][$n] = array('pay_period_id' => FALSE, 'user_id' => (int) $this->getColumn('user_id'), 'user_created_by' => $this->getColumn('user_created_by'), 'user_full_name' => $this->getColumn('user_id') > 0 ? Misc::getFullName($this->getColumn('first_name'), NULL, $this->getColumn('last_name'), FALSE, FALSE) : TTi18n::getText('OPEN'), 'first_name' => $this->getColumn('first_name'), 'last_name' => $this->getColumn('last_name'), 'title_id' => $this->getColumn('title_id'), 'title' => $this->getColumn('title'), 'group_id' => $this->getColumn('group_id'), 'group' => $this->getColumn('group'), 'default_branch_id' => $this->getColumn('default_branch_id'), 'default_branch' => $this->getColumn('default_branch'), 'default_department_id' => $this->getColumn('default_department_id'), 'default_department' => $this->getColumn('default_department'), 'job_id' => $this->getJob(), 'job' => $this->getColumn('job'), 'job_status_id' => $this->getColumn('job_status_id'), 'job_manual_id' => $this->getColumn('job_manual_id'), 'job_branch_id' => $this->getColumn('job_branch_id'), 'job_department_id' => $this->getColumn('job_department_id'), 'job_group_id' => $this->getColumn('job_group_id'), 'job_item_id' => $this->getJobItem(), 'job_item' => $this->getColumn('job_item'), 'type_id' => 20, 'status_id' => $status_id, 'date_stamp' => TTDate::getAPIDate('DATE', strtotime($iso_date_stamp)), 'start_date_stamp' => defined('TIMETREX_API') ? TTDate::getAPIDate('DATE', $start_time) : $start_time, 'start_date' => defined('TIMETREX_API') ? TTDate::getAPIDate('DATE+TIME', $start_time) : $start_time, 'end_date' => defined('TIMETREX_API') ? TTDate::getAPIDate('DATE+TIME', $end_time) : $end_time, 'start_time' => defined('TIMETREX_API') ? TTDate::getAPIDate('TIME', $start_time) : $start_time, 'end_time' => defined('TIMETREX_API') ? TTDate::getAPIDate('TIME', $end_time) : $end_time, 'start_time_stamp' => $start_time, 'end_time_stamp' => $end_time, 'total_time' => $this->getTotalTime(), 'hourly_rate' => $hourly_rate, 'total_time_wage' => $total_time_wage, 'note' => FALSE, 'schedule_policy_id' => $this->getSchedulePolicyID(), 'absence_policy_id' => $absence_policy_id, 'absence_policy' => $absence_policy, 'branch_id' => $this->getColumn('schedule_branch_id'), 'branch' => $this->getColumn('schedule_branch'), 'department_id' => $this->getColumn('schedule_department_id'), 'department' => $this->getColumn('schedule_department'), 'created_by_id' => $this->getColumn('recurring_schedule_control_created_by'), 'created_date' => $this->getCreatedDate(), 'updated_date' => $this->getUpdatedDate());
//Make sure we add in permission columns.
$this->getPermissionColumns($shifts[$iso_date_stamp][$n], (int) $this->getColumn('user_id'), $this->getColumn('recurring_schedule_control_created_by'), $permission_children_ids);
//$shifts_index[$iso_date_stamp][$this->getColumn('user_id')][] = $this->getColumn('user_id').$start_time;
$shifts_index[$iso_date_stamp][$this->getColumn('user_id')][] = $n;
$n++;
}
unset($open_shift_multiplier);
unset($start_time, $end_time);
} else {
//Debug::text(' NOT active shift on this day... ID: '. $this->getColumn('id') .' User ID: '. $this->getColumn('user_id') .' Start Time: '. TTDate::getDate('DATE+TIME', $i),__FILE__, __LINE__, __METHOD__, 10);
}
}
}
if (isset($shifts)) {
//Debug::Arr($shifts, 'Template Shifts: ',__FILE__, __LINE__, __METHOD__, 10);
return $shifts;
}
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']);
}
}
//Debug::Arr($order,'aOrder Data:', __FILE__, __LINE__, __METHOD__,10);
$additional_order_fields = array('name', 'description', 'last_name', 'start_date', 'user_id');
if ($order == NULL) {
$order = array('c.start_date' => 'asc', 'cb.user_id' => 'desc', 'a.week' => 'asc');
$strict = FALSE;
} else {
$strict = TRUE;
}
if (isset($filter_data['exclude_user_ids'])) {
$filter_data['exclude_id'] = $filter_data['exclude_user_ids'];
}
//This is used by Flex Schedule Summary report.
if (isset($filter_data['include_user_id'])) {
$filter_data['id'] = $filter_data['include_user_id'];
}
if (isset($filter_data['include_user_ids'])) {
$filter_data['id'] = $filter_data['include_user_ids'];
}
/*
if ( isset($filter_data['user_status_ids']) ) {
$filter_data['status_id'] = $filter_data['user_status_ids'];
}
*/
if (isset($filter_data['user_title_ids'])) {
$filter_data['title_id'] = $filter_data['user_title_ids'];
}
if (isset($filter_data['group_ids'])) {
$filter_data['group_id'] = $filter_data['group_ids'];
}
if (isset($filter_data['default_branch_ids'])) {
$filter_data['default_branch_id'] = $filter_data['default_branch_ids'];
}
if (isset($filter_data['default_department_ids'])) {
$filter_data['default_department_id'] = $filter_data['default_department_ids'];
}
if (isset($filter_data['branch_ids'])) {
$filter_data['schedule_branch_id'] = $filter_data['branch_ids'];
}
if (isset($filter_data['department_ids'])) {
$filter_data['schedule_department_id'] = $filter_data['department_ids'];
}
if (isset($filter_data['schedule_branch_ids'])) {
$filter_data['schedule_branch_id'] = $filter_data['schedule_branch_ids'];
}
if (isset($filter_data['schedule_department_ids'])) {
$filter_data['schedule_department_id'] = $filter_data['schedule_department_ids'];
}
if (isset($filter_data['exclude_job_ids'])) {
$filter_data['exclude_id'] = $filter_data['exclude_job_ids'];
}
if (isset($filter_data['include_job_ids'])) {
$filter_data['include_job_id'] = $filter_data['include_job_ids'];
}
if (isset($filter_data['job_group_ids'])) {
$filter_data['job_group_id'] = $filter_data['job_group_ids'];
}
if (isset($filter_data['job_item_ids'])) {
$filter_data['job_item_id'] = $filter_data['job_item_ids'];
}
//Debug::Arr($order,'bOrder Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$uwf = new UserWageFactory();
$rscf = new RecurringScheduleControlFactory();
$rsuf = new RecurringScheduleUserFactory();
$rstcf = new RecurringScheduleTemplateControlFactory();
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$apf = new AbsencePolicyFactory();
$ppsuf = new PayPeriodScheduleUserFactory();
$ppsf = new PayPeriodScheduleFactory();
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$jf = new JobFactory();
$jif = new JobItemFactory();
}
$ph = array('filter_end_date' => $this->db->BindDate($filter_data['end_date']), 'company_id' => $company_id);
$query = '
select a.*,
apf.type_id as absence_policy_type_id,
apf.name as absence_policy,
cb.user_id as user_id,
CASE WHEN a.branch_id = -1 THEN d.default_branch_id ELSE a.branch_id END as schedule_branch_id,
CASE WHEN a.branch_id = -1 THEN bf.name ELSE bfb.name END as schedule_branch,
CASE WHEN a.department_id = -1 THEN d.default_department_id ELSE a.department_id END as schedule_department_id,
CASE WHEN a.department_id = -1 THEN df.name ELSE dfb.name END as schedule_department,
c.start_date as recurring_schedule_control_start_date,
c.end_date as recurring_schedule_control_end_date,
c.start_week as recurring_schedule_control_start_week,
zz.max_week as max_week,
( (((a.week-1)+zz.max_week-(c.start_week-1))%zz.max_week) + 1) as remapped_week,
d.first_name as first_name,
d.last_name as last_name,
d.default_branch_id as default_branch_id,
bf.name as default_branch,
d.default_department_id as default_department_id,
df.name as default_department,
d.title_id as title_id,
utf.name as title,
d.group_id as group_id,
ugf.name as "group",
d.created_by as user_created_by,
d.hire_date as hire_date,
d.termination_date as termination_date,
uw.id as user_wage_id,
uw.hourly_rate as user_wage_hourly_rate,
uw.effective_date as user_wage_effective_date,
ppsf.shift_assigned_day_id as shift_assigned_day_id,
c.created_by as recurring_schedule_control_created_by
';
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$query .= ',
x.name as job,
x.status_id as job_status_id,
x.manual_id as job_manual_id,
x.branch_id as job_branch_id,
x.department_id as job_department_id,
x.group_id as job_group_id,
y.name as job_item,
y.manual_id as job_item_manual_id,
y.group_id as job_item_group_id';
}
//Since when dealing with recurring schedules, we don't have a row for each specific date, so when determining wages
//we can only use the last wage entered that is earlier than the filter end date.
//Since in theory committed schedules will occur before todays date anyways, the accuracy won't be off too much unless
//the end date they specify is really far in the future, and post dated wage entry is also made.
$query .= '
from ' . $this->getTable() . ' as a
LEFT JOIN ( select z.recurring_schedule_template_control_id, max(z.week) as max_week from recurring_schedule_template as z where deleted = 0 group by z.recurring_schedule_template_control_id ) as zz ON a.recurring_schedule_template_control_id = zz.recurring_schedule_template_control_id
LEFT JOIN ' . $rstcf->getTable() . ' as b ON a.recurring_schedule_template_control_id = b.id
LEFT JOIN ' . $rscf->getTable() . ' as c ON a.recurring_schedule_template_control_id = c.recurring_schedule_template_control_id
LEFT JOIN ' . $rsuf->getTable() . ' as cb ON c.id = cb.recurring_schedule_control_id
LEFT JOIN ' . $uf->getTable() . ' as d ON cb.user_id = d.id
LEFT JOIN ' . $ppsuf->getTable() . ' as ppsuf ON d.id = ppsuf.user_id
LEFT JOIN ' . $ppsf->getTable() . ' as ppsf ON ( ppsuf.pay_period_schedule_id = ppsf.id AND ppsf.deleted = 0 )
LEFT JOIN ' . $bf->getTable() . ' as bf ON ( d.default_branch_id = bf.id AND bf.deleted = 0)
LEFT JOIN ' . $bf->getTable() . ' as bfb ON ( a.branch_id = bfb.id AND bfb.deleted = 0)
LEFT JOIN ' . $df->getTable() . ' as df ON ( d.default_department_id = df.id AND df.deleted = 0)
LEFT JOIN ' . $df->getTable() . ' as dfb ON ( a.department_id = dfb.id AND dfb.deleted = 0)
LEFT JOIN ' . $ugf->getTable() . ' as ugf ON ( d.group_id = ugf.id AND ugf.deleted = 0 )
LEFT JOIN ' . $utf->getTable() . ' as utf ON ( d.title_id = utf.id AND utf.deleted = 0 )
LEFT JOIN ' . $apf->getTable() . ' as apf ON ( a.absence_policy_id = apf.id AND apf.deleted = 0 )
LEFT JOIN ' . $uwf->getTable() . ' as uw ON uw.id = (select uwb.id
from ' . $uwf->getTable() . ' as uwb
where uwb.user_id = cb.user_id
and uwb.effective_date <= ?
and uwb.deleted = 0
order by uwb.effective_date desc limit 1)
';
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$query .= ' LEFT JOIN ' . $jf->getTable() . ' as x ON a.job_id = x.id';
$query .= ' LEFT JOIN ' . $jif->getTable() . ' as y ON a.job_item_id = y.id';
}
$query .= ' 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 d.id 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 d.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 d.id not in (' . $this->getListSQL($filter_data['exclude_id'], $ph) . ') ';
}
if (isset($filter_data['user_status_id']) and isset($filter_data['user_status_id'][0]) and !in_array(-1, (array) $filter_data['user_status_id'])) {
$query .= ' AND d.status_id in (' . $this->getListSQL($filter_data['user_status_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 d.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 d.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 d.default_department_id in (' . $this->getListSQL($filter_data['default_department_id'], $ph) . ') ';
}
if (isset($filter_data['schedule_branch_id']) and isset($filter_data['schedule_branch_id'][0]) and !in_array(-1, (array) $filter_data['schedule_branch_id'])) {
$query .= ' AND ( a.branch_id in (' . $this->getListSQL($filter_data['schedule_branch_id'], $ph) . ') OR ( a.branch_id = -1 AND d.default_branch_id in (' . $this->getListSQL($filter_data['schedule_branch_id'], $ph) . ') ) )';
}
if (isset($filter_data['schedule_department_id']) and isset($filter_data['schedule_department_id'][0]) and !in_array(-1, (array) $filter_data['schedule_department_id'])) {
$query .= ' AND ( a.department_id in (' . $this->getListSQL($filter_data['schedule_department_id'], $ph) . ') OR ( a.department_id = -1 AND d.default_department_id in (' . $this->getListSQL($filter_data['schedule_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 d.title_id in (' . $this->getListSQL($filter_data['title_id'], $ph) . ') ';
}
//Use the job_id in the schedule table so we can filter by '0' or No Job
if (isset($filter_data['job_id']) and isset($filter_data['job_id'][0]) and !in_array(-1, (array) $filter_data['job_id'])) {
$query .= ' AND a.job_id in (' . $this->getListSQL($filter_data['job_id'], $ph) . ') ';
}
if (isset($filter_data['job_group_id']) and isset($filter_data['job_group_id'][0]) and !in_array(-1, (array) $filter_data['job_group_id'])) {
if (isset($filter_data['include_job_subgroups']) and (bool) $filter_data['include_job_subgroups'] == TRUE) {
$uglf = new UserGroupListFactory();
$filter_data['job_group_id'] = $uglf->getByCompanyIdAndGroupIdAndjob_subgroupsArray($company_id, $filter_data['job_group_id'], TRUE);
}
$query .= ' AND x.group_id in (' . $this->getListSQL($filter_data['job_group_id'], $ph) . ') ';
}
if (isset($filter_data['job_item_id']) and isset($filter_data['job_item_id'][0]) and !in_array(-1, (array) $filter_data['job_item_id'])) {
$query .= ' AND a.job_item_id in (' . $this->getListSQL($filter_data['job_item_id'], $ph) . ') ';
}
if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '' and isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') {
$start_date_stamp = $this->db->BindDate($filter_data['start_date']);
$end_date_stamp = $this->db->BindDate($filter_data['end_date']);
$ph[] = $start_date_stamp;
$ph[] = $end_date_stamp;
$ph[] = $start_date_stamp;
$ph[] = $start_date_stamp;
$ph[] = $end_date_stamp;
$ph[] = $start_date_stamp;
$ph[] = $end_date_stamp;
$ph[] = $start_date_stamp;
$ph[] = $end_date_stamp;
$ph[] = $start_date_stamp;
$ph[] = $end_date_stamp;
$ph[] = $start_date_stamp;
$ph[] = $end_date_stamp;
$ph[] = $filter_data['end_date'];
$ph[] = $filter_data['start_date'];
$query .= ' AND (
(c.start_date >= ? AND c.start_date <= ? AND c.end_date IS NULL )
OR
(c.start_date <= ? AND c.end_date IS NULL )
OR
(c.start_date <= ? AND c.end_date >= ? )
OR
(c.start_date >= ? AND c.end_date <= ? )
OR
(c.start_date >= ? AND c.start_date <= ? )
OR
(c.end_date >= ? AND c.end_date <= ? )
OR
(c.start_date <= ? AND c.end_date >= ? )
)
AND
(
( d.hire_date is NULL OR d.hire_date <= ? )
AND
( d.termination_date is NULL OR d.termination_date >= ? )
)
';
}
$query .= '
AND ( a.deleted = 0 AND b.deleted = 0 AND c.deleted = 0 AND (d.deleted is NULL OR d.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;
}
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('status_id', 'type_id', 'pay_period_schedule');
$sort_column_aliases = array('status' => 'status_id', 'type' => 'type_id');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('transaction_date' => 'desc', 'end_date' => 'desc', 'start_date' => 'desc', 'pay_period_schedule_id' => 'asc');
$strict = FALSE;
} else {
//Always try to order by status first so INACTIVE employees go to the bottom.
if (!isset($order['transaction_date'])) {
$order = Misc::prependArray(array('transaction_date' => 'desc'), $order);
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$ppsf = new PayPeriodScheduleFactory();
$uf = new UserFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
b.name as pay_period_schedule,
b.type_id as type_id,
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 ' . $ppsf->getTable() . ' as b ON ( a.pay_period_schedule_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 a.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['pay_period_schedule_id']) and isset($filter_data['pay_period_schedule_id'][0]) and !in_array(-1, (array) $filter_data['pay_period_schedule_id'])) {
$query .= ' AND a.pay_period_schedule_id in (' . $this->getListSQL($filter_data['pay_period_schedule_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['type_id']) and isset($filter_data['type_id'][0]) and !in_array(-1, (array) $filter_data['type_id'])) {
$query .= ' AND b.type_id in (' . $this->getListSQL($filter_data['type_id'], $ph) . ') ';
}
if (isset($filter_data['name']) and trim($filter_data['name']) != '') {
$ph[] = strtolower(trim($filter_data['name']));
$query .= ' AND lower(b.name) LIKE ?';
}
if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') {
$ph[] = $this->db->BindTimeStamp($filter_data['start_date']);
$query .= ' AND a.start_date >= ?';
}
if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') {
$ph[] = $this->db->BindTimeStamp($filter_data['end_date']);
$query .= ' AND a.start_date <= ?';
}
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;
}
function getByPayPeriodScheduleIdAndUserID($id, $user_id, $where = NULL, $order = NULL)
{
if ($id == '') {
return FALSE;
}
if ($user_id == '') {
return FALSE;
}
$ppsf = new PayPeriodScheduleFactory();
$ph = array('id' => $id, 'user_id' => $user_id);
$query = '
select a.*
from ' . $this->getTable() . ' as a,
' . $ppsf->getTable() . ' as b
where b.id = a.pay_period_schedule_id
AND pay_period_schedule_id = ?
AND user_id = ?
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order);
$this->ExecuteSQL($query, $ph);
return $this;
}
function createPayPeriodSchedule()
{
$ppsf = new PayPeriodScheduleFactory();
$ppsf->setCompany($this->company_id);
//$ppsf->setName( 'Bi-Weekly'.rand(1000,9999) );
$ppsf->setName('Bi-Weekly');
$ppsf->setDescription('Pay every two weeks');
$ppsf->setType(20);
$ppsf->setStartWeekDay(0);
$anchor_date = TTDate::getBeginWeekEpoch(strtotime('01-Jan-06'));
//Start 6 weeks ago
$ppsf->setAnchorDate($anchor_date);
$ppsf->setStartDayOfWeek(TTDate::getDayOfWeek($anchor_date));
$ppsf->setTransactionDate(7);
$ppsf->setTransactionDateBusinessDay(TRUE);
/*
$ppsf->setPrimaryDate( ($anchor_date+(86400*14)) );
$ppsf->setPrimaryDateLastDayOfMonth( FALSE );
$ppsf->setPrimaryTransactionDate( ($anchor_date+(86400*21)) );
$ppsf->setPrimaryTransactionDateLastDayOfMonth( FALSE );
$ppsf->setPrimaryTransactionDateBusinessDay( FALSE );
$ppsf->setSecondaryDate( ($anchor_date+(86400*28)) );
$ppsf->setSecondaryDateLastDayOfMonth( FALSE );
$ppsf->setSecondaryTransactionDate( ($anchor_date+(86400*35)) );
$ppsf->setSecondaryTransactionDateLastDayOfMonth( FALSE );
$ppsf->setSecondaryTransactionDateBusinessDay( FALSE );
*/
$ppsf->setDayStartTime(0);
$ppsf->setNewDayTriggerTime(4 * 3600);
$ppsf->setMaximumShiftTime(16 * 3600);
//$ppsf->setContinuousTime( 64800 );
$ppsf->setEnableInitialPayPeriods(FALSE);
if ($ppsf->isValid()) {
$insert_id = $ppsf->Save(FALSE);
Debug::Text('Pay Period Schedule ID: ' . $insert_id, __FILE__, __LINE__, __METHOD__, 10);
$ppsf->setUser(array($this->user_id));
$ppsf->Save();
$this->pay_period_schedule_id = $insert_id;
return $insert_id;
}
Debug::Text('Failed Creating Pay Period Schedule!', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
/**
* Return user records based on advanced filter criteria.
*
* @param int $company_id Company ID
* @param array $filter_data Filter criteria in array('id' => array(1,2), 'last_name' => 'smith' ) format, with possible top level array keys as follows: id, exclude_id, status_id, user_group_id, default_branch_id, default_department_id, title_id, currency_id, permission_control_id, pay_period_schedule_id, policy_group_id, sex_id, first_name, last_name, home_phone, work_phone, country, province, city, address1, address2, postal_code, employee_number, user_name, sin, work_email, home_email, tag, last_login_date, created_by, created_date, updated_by, updated_date
* @param int $limit Optional. Restrict the number of records returned
* @param int $page Optional. Specify the page of records to return
* @param array $where Optional. Additional WHERE clauses in array( 'column' => 'value', 'column' => 'value' ) format.
* @param array $order Optional. Sort order in array( 'column' => ASC, 'column2' => DESC ) format.
*
* @return object $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']);
}
}
if (isset($filter_data['user_status_id'])) {
$filter_data['status_id'] = $filter_data['user_status_id'];
}
if (isset($filter_data['include_user_id'])) {
$filter_data['id'] = $filter_data['include_user_id'];
}
if (isset($filter_data['exclude_user_id'])) {
$filter_data['exclude_id'] = $filter_data['exclude_user_id'];
}
//Some of these are passed from Flex Schedule view.
if (isset($filter_data['default_branch_ids'])) {
$filter_data['default_branch_id'] = $filter_data['default_branch_ids'];
}
if (isset($filter_data['default_department_ids'])) {
$filter_data['default_department_id'] = $filter_data['default_department_ids'];
}
if (isset($filter_data['group_id'])) {
$filter_data['user_group_id'] = $filter_data['group_id'];
}
if (isset($filter_data['user_title_id'])) {
$filter_data['title_id'] = $filter_data['user_title_id'];
}
if (isset($filter_data['user_tag'])) {
$filter_data['tag'] = $filter_data['user_tag'];
}
//$additional_order_fields = array('b.name', 'c.name', 'd.name', 'e.name');
$additional_order_fields = array('default_branch', 'default_department', 'default_job', 'default_job_item', 'sex', 'user_group', 'title', 'currency', 'permission_control', 'pay_period_schedule', 'policy_group');
$sort_column_aliases = array('type' => 'type_id', 'status' => 'status_id', 'sex' => 'sex_id', 'full_name' => 'last_name');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('status_id' => 'asc', 'last_name' => 'asc', 'first_name' => 'asc', 'middle_name' => 'asc');
$strict = FALSE;
} else {
//Do order by column conversions, because if we include these columns in the SQL
//query, they contaminate the data array.
//Always try to order by status first so INACTIVE employees go to the bottom.
if (!isset($order['status_id'])) {
$order = Misc::prependArray(array('status_id' => 'asc'), $order);
}
//Always sort by last name,first name after other columns
if (!isset($order['last_name'])) {
$order['last_name'] = 'asc';
}
if (!isset($order['first_name'])) {
$order['first_name'] = 'asc';
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$compf = new CompanyFactory();
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$cf = new CurrencyFactory();
$pcf = new PermissionControlFactory();
$puf = new PermissionUserFactory();
$ppsuf = new PayPeriodScheduleUserFactory();
$ppsf = new PayPeriodScheduleFactory();
$pguf = new PolicyGroupUserFactory();
$pgf = new PolicyGroupFactory();
$egf = new EthnicGroupFactory();
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$jf = new JobFactory();
$jif = new JobItemFactory();
}
$ph = array('company_id' => $company_id);
$query = '
select
a.*,
compf.name as company,
b.name as default_branch,
c.name as default_department,
d.name as user_group,
e.name as title,
f.name as currency,
f.conversion_rate as currency_rate,
g.id as permission_control_id,
g.name as permission_control,
h.id as pay_period_schedule_id,
h.name as pay_period_schedule,
i.id as policy_group_id,
i.name as policy_group,
egf.name as ethnic_group, ';
$query .= Permission::getPermissionIsChildIsOwnerSQL(isset($filter_data['permission_current_user_id']) ? $filter_data['permission_current_user_id'] : 0, 'a.id');
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$query .= ' jf.name as default_job,
jif.name as default_job_item, ';
}
$query .= ' 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 ' . $compf->getTable() . ' as compf ON ( a.company_id = compf.id AND compf.deleted = 0)
LEFT JOIN ' . $bf->getTable() . ' as b ON ( a.default_branch_id = b.id AND b.deleted = 0)
LEFT JOIN ' . $df->getTable() . ' as c ON ( a.default_department_id = c.id AND c.deleted = 0)
LEFT JOIN ' . $ugf->getTable() . ' as d ON ( a.group_id = d.id AND d.deleted = 0 )
LEFT JOIN ' . $utf->getTable() . ' as e ON ( a.title_id = e.id AND e.deleted = 0 )
LEFT JOIN ' . $cf->getTable() . ' as f ON ( a.currency_id = f.id AND f.deleted = 0 )
LEFT JOIN ' . $egf->getTable() . ' as egf ON ( a.ethnic_group_id = egf.id AND egf.deleted = 0 ) ';
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$query .= ' LEFT JOIN ' . $jf->getTable() . ' as jf ON a.default_job_id = jf.id';
$query .= ' LEFT JOIN ' . $jif->getTable() . ' as jif ON a.default_job_item_id = jif.id';
}
$query .= ' LEFT JOIN
(
SELECT g2.*,g1.user_id
FROM ' . $puf->getTable() . ' as g1, ' . $pcf->getTable() . ' as g2
WHERE ( g1.permission_control_id = g2.id AND g2.deleted = 0)
) as g ON ( a.id = g.user_id )
LEFT JOIN
(
SELECT h2.*, h1.user_id
FROM ' . $ppsuf->getTable() . ' as h1, ' . $ppsf->getTable() . ' as h2
WHERE ( h1.pay_period_schedule_id = h2.id AND h2.deleted = 0)
) as h ON ( a.id = h.user_id )
LEFT JOIN
(
SELECT i2.*, i1.user_id
FROM ' . $pguf->getTable() . ' as i1, ' . $pgf->getTable() . ' as i2
WHERE ( i1.policy_group_id = i2.id AND i2.deleted = 0)
) as i ON ( a.id = i.user_id ) ';
$query .= Permission::getPermissionHierarchySQL($company_id, isset($filter_data['permission_current_user_id']) ? $filter_data['permission_current_user_id'] : 0, 'a.id');
$query .= '
LEFT JOIN ' . $this->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 )
LEFT JOIN ' . $this->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 )
where a.company_id = ?
';
$query .= Permission::getPermissionIsChildIsOwnerFilterSQL($filter_data, 'a.id');
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.id', $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['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('a.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL;
if (isset($filter_data['include_subgroups']) and (bool) $filter_data['include_subgroups'] == TRUE) {
$uglf = new UserGroupListFactory();
$filter_data['user_group_id'] = $uglf->getByCompanyIdAndGroupIdAndSubGroupsArray($company_id, $filter_data['user_group_id'], TRUE);
}
$query .= isset($filter_data['user_group_id']) ? $this->getWhereClauseSQL('a.group_id', $filter_data['user_group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['user_group']) ? $this->getWhereClauseSQL('d.name', $filter_data['user_group'], 'text', $ph) : NULL;
$query .= isset($filter_data['default_branch_id']) ? $this->getWhereClauseSQL('a.default_branch_id', $filter_data['default_branch_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['default_branch']) ? $this->getWhereClauseSQL('b.name', $filter_data['default_branch'], 'text', $ph) : NULL;
$query .= isset($filter_data['default_department_id']) ? $this->getWhereClauseSQL('a.default_department_id', $filter_data['default_department_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['default_department']) ? $this->getWhereClauseSQL('c.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['ethnic_group_id']) ? $this->getWhereClauseSQL('a.ethnic_group_id', $filter_data['ethnic_group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['ethnic_group']) ? $this->getWhereClauseSQL('egf.name', $filter_data['ethnic_group'], 'text', $ph) : NULL;
$query .= isset($filter_data['currency_id']) ? $this->getWhereClauseSQL('a.currency_id', $filter_data['currency_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['currency']) ? $this->getWhereClauseSQL('f.name', $filter_data['currency'], 'text', $ph) : NULL;
$query .= isset($filter_data['permission_control_id']) ? $this->getWhereClauseSQL('g.id', $filter_data['permission_control_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['permission_control']) ? $this->getWhereClauseSQL('g.name', $filter_data['permission_control'], 'text', $ph) : NULL;
$query .= isset($filter_data['pay_period_schedule_id']) ? $this->getWhereClauseSQL('i.pay_period_schedule_id', $filter_data['pay_period_schedule_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['pay_period_schedule']) ? $this->getWhereClauseSQL('h.name', $filter_data['pay_period_schedule'], 'text', $ph) : NULL;
$query .= isset($filter_data['policy_group_id']) ? $this->getWhereClauseSQL('k.policy_group_id', $filter_data['policy_group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['policy_group']) ? $this->getWhereClauseSQL('i.name', $filter_data['policy_group'], 'text', $ph) : NULL;
if (isset($filter_data['sex']) and trim($filter_data['sex']) != '' and !isset($filter_data['sex_id'])) {
$filter_data['sex_id'] = Option::getByFuzzyValue($filter_data['sex'], $this->getOptions('sex'));
}
$query .= isset($filter_data['sex_id']) ? $this->getWhereClauseSQL('a.sex_id', $filter_data['sex_id'], 'text_list', $ph) : NULL;
$query .= isset($filter_data['first_name']) ? $this->getWhereClauseSQL('a.first_name', $filter_data['first_name'], 'text_metaphone', $ph) : NULL;
$query .= isset($filter_data['last_name']) ? $this->getWhereClauseSQL('a.last_name', $filter_data['last_name'], 'text_metaphone', $ph) : NULL;
$query .= isset($filter_data['home_phone']) ? $this->getWhereClauseSQL('a.home_phone', $filter_data['home_phone'], 'phone', $ph) : NULL;
$query .= isset($filter_data['work_phone']) ? $this->getWhereClauseSQL('a.work_phone', $filter_data['work_phone'], 'phone', $ph) : NULL;
$query .= isset($filter_data['country']) ? $this->getWhereClauseSQL('a.country', $filter_data['country'], 'upper_text_list', $ph) : NULL;
$query .= isset($filter_data['province']) ? $this->getWhereClauseSQL('a.province', $filter_data['province'], 'upper_text_list', $ph) : NULL;
$query .= isset($filter_data['city']) ? $this->getWhereClauseSQL('a.city', $filter_data['city'], 'text', $ph) : NULL;
$query .= isset($filter_data['address1']) ? $this->getWhereClauseSQL('a.address1', $filter_data['address1'], 'text', $ph) : NULL;
$query .= isset($filter_data['address2']) ? $this->getWhereClauseSQL('a.address2', $filter_data['address2'], 'text', $ph) : NULL;
$query .= isset($filter_data['postal_code']) ? $this->getWhereClauseSQL('a.postal_code', $filter_data['postal_code'], 'text', $ph) : NULL;
$query .= isset($filter_data['employee_number']) ? $this->getWhereClauseSQL('a.employee_number', $filter_data['employee_number'], 'numeric', $ph) : NULL;
$query .= isset($filter_data['user_name']) ? $this->getWhereClauseSQL('a.user_name', $filter_data['user_name'], 'text', $ph) : NULL;
$query .= isset($filter_data['sin']) ? $this->getWhereClauseSQL('a.sin', $filter_data['sin'], 'numeric', $ph) : NULL;
$query .= isset($filter_data['email']) ? 'AND (' . $this->getWhereClauseSQL('a.work_email', $filter_data['email'], 'text', $ph, NULL, FALSE) . ' OR ' . $this->getWhereClauseSQL('a.home_email', $filter_data['email'], 'text', $ph, NULL, FALSE) . ')' : NULL;
$query .= isset($filter_data['work_email']) ? $this->getWhereClauseSQL('a.work_email', $filter_data['work_email'], 'text', $ph) : NULL;
$query .= isset($filter_data['home_email']) ? $this->getWhereClauseSQL('a.home_email', $filter_data['home_email'], 'text', $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;
//$query .= ( isset($filter_data['longitude']) ) ? $this->getWhereClauseSQL( 'a.longitude', $filter_data['longitude'], 'numeric', $ph ) : NULL;
if (isset($filter_data['last_login_date']) and trim($filter_data['last_login_date']) != '') {
$date_filter = $this->getDateRangeSQL($filter_data['last_login_date'], 'a.last_login_date');
if ($date_filter != FALSE) {
$query .= ' AND ' . $date_filter;
}
unset($date_filter);
}
if (isset($filter_data['created_date']) and trim($filter_data['created_date']) != '') {
$date_filter = $this->getDateRangeSQL($filter_data['created_date'], 'a.created_date');
if ($date_filter != FALSE) {
$query .= ' AND ' . $date_filter;
}
unset($date_filter);
}
if (isset($filter_data['updated_date']) and trim($filter_data['updated_date']) != '') {
$date_filter = $this->getDateRangeSQL($filter_data['updated_date'], 'a.updated_date');
if ($date_filter != FALSE) {
$query .= ' AND ' . $date_filter;
}
unset($date_filter);
}
$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);
//Debug::Arr($ph, 'Query: '. $query, __FILE__, __LINE__, __METHOD__,10);
$this->ExecuteSQL($query, $ph, $limit, $page);
return $this;
}
function setPayPeriodType($value)
{
$value = trim($value);
Debug::Text('Type ID: ' . $value, __FILE__, __LINE__, __METHOD__, 10);
$ppsf = new PayPeriodScheduleFactory();
$key = Option::getByValue($value, $ppsf->getOptions('type'));
if ($key !== FALSE) {
$value = $key;
}
if ($this->Validator->inArrayKey('pay_period_type_id', $value, TTi18n::gettext('Incorrect pay period type'), $ppsf->getOptions('type'))) {
$this->data['pay_period_type_id'] = $value;
return FALSE;
}
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('b.name', 'c.name', 'd.name', 'e.name');
$additional_order_fields = array('default_branch', 'default_department', 'group', 'title', 'currency', 'permission_control', 'pay_period_schedule', 'policy_group');
$sort_column_aliases = array('type' => 'type_id', 'status' => 'status_id');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('status_id' => 'asc', 'last_name' => 'asc', 'first_name' => 'asc', 'middle_name' => 'asc');
$strict = FALSE;
} else {
//Do order by column conversions, because if we include these columns in the SQL
//query, they contaminate the data array.
//Always try to order by status first so INACTIVE employees go to the bottom.
if (!isset($order['status_id'])) {
$order = Misc::prependArray(array('status_id' => 'asc'), $order);
}
//Always sort by last name,first name after other columns
if (!isset($order['last_name'])) {
$order['last_name'] = 'asc';
}
if (!isset($order['first_name'])) {
$order['first_name'] = 'asc';
}
$strict = TRUE;
}
Debug::Arr($order, 'Order Data:', __FILE__, __LINE__, __METHOD__, 10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$cf = new CurrencyFactory();
$pcf = new PermissionControlFactory();
$puf = new PermissionUserFactory();
$ppsuf = new PayPeriodScheduleUserFactory();
$ppsf = new PayPeriodScheduleFactory();
$pguf = new PolicyGroupUserFactory();
$pgf = new PolicyGroupFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
b.name as default_branch,
c.name as default_department,
d.name as group,
e.name as title,
f.name as currency,
g.id as permission_control_id,
g.name as permission_control,
h.id as pay_period_schedule_id,
h.name as pay_period_schedule,
i.id as policy_group_id,
i.name as policy_group,
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 ' . $bf->getTable() . ' as b ON ( a.default_branch_id = b.id AND b.deleted = 0)
LEFT JOIN ' . $df->getTable() . ' as c ON ( a.default_department_id = c.id AND c.deleted = 0)
LEFT JOIN ' . $ugf->getTable() . ' as d ON ( a.group_id = d.id AND d.deleted = 0 )
LEFT JOIN ' . $utf->getTable() . ' as e ON ( a.title_id = e.id AND e.deleted = 0 )
LEFT JOIN ' . $cf->getTable() . ' as f ON ( a.currency_id = f.id AND f.deleted = 0 )
LEFT JOIN
(
SELECT g2.*,g1.user_id
FROM ' . $puf->getTable() . ' as g1, ' . $pcf->getTable() . ' as g2
WHERE ( g1.permission_control_id = g2.id AND g2.deleted = 0)
) as g ON ( a.id = g.user_id )
LEFT JOIN
(
SELECT h2.*, h1.user_id
FROM ' . $ppsuf->getTable() . ' as h1, ' . $ppsf->getTable() . ' as h2
WHERE ( h1.pay_period_schedule_id = h2.id AND h2.deleted = 0)
) as h ON ( a.id = h.user_id )
LEFT JOIN
(
SELECT i2.*, i1.user_id
FROM ' . $pguf->getTable() . ' as i1, ' . $pgf->getTable() . ' as i2
WHERE ( i1.policy_group_id = i2.id AND i2.deleted = 0)
) as i ON ( a.id = i.user_id )
LEFT JOIN ' . $this->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 )
LEFT JOIN ' . $this->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 )
where a.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.id 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['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 a.group_id in (' . $this->getListSQL($filter_data['group_id'], $ph) . ') ';
}
if (isset($filter_data['group']) and trim($filter_data['group']) != '') {
$ph[] = strtolower(trim($filter_data['group']));
$query .= ' AND lower(d.name) LIKE ?';
}
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 a.default_branch_id in (' . $this->getListSQL($filter_data['default_branch_id'], $ph) . ') ';
}
if (isset($filter_data['default_branch']) and trim($filter_data['default_branch']) != '') {
$ph[] = strtolower(trim($filter_data['default_branch']));
$query .= ' AND lower(b.name) LIKE ?';
}
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 a.default_department_id in (' . $this->getListSQL($filter_data['default_department_id'], $ph) . ') ';
}
if (isset($filter_data['default_department']) and trim($filter_data['default_department']) != '') {
$ph[] = strtolower(trim($filter_data['default_department']));
$query .= ' AND lower(c.name) LIKE ?';
}
if (isset($filter_data['title_id']) and isset($filter_data['title_id'][0]) and !in_array(-1, (array) $filter_data['title_id'])) {
$query .= ' AND a.title_id in (' . $this->getListSQL($filter_data['title_id'], $ph) . ') ';
}
if (isset($filter_data['title']) and trim($filter_data['title']) != '') {
$ph[] = strtolower(trim($filter_data['title']));
$query .= ' AND lower(e.name) LIKE ?';
}
if (isset($filter_data['currency_id']) and isset($filter_data['currency_id'][0]) and !in_array(-1, (array) $filter_data['currency_id'])) {
$query .= ' AND a.currency_id in (' . $this->getListSQL($filter_data['currency_id'], $ph) . ') ';
}
if (isset($filter_data['currency']) and trim($filter_data['currency']) != '') {
$ph[] = strtolower(trim($filter_data['currency']));
$query .= ' AND lower(f.name) LIKE ?';
}
if (isset($filter_data['permission_control_id']) and isset($filter_data['permission_control_id'][0]) and !in_array(-1, (array) $filter_data['permission_control_id'])) {
$query .= ' AND g.permission_control_id in (' . $this->getListSQL($filter_data['permission_control_id'], $ph) . ') ';
}
if (isset($filter_data['permission_control']) and trim($filter_data['permission_control']) != '') {
$ph[] = strtolower(trim($filter_data['permission_control']));
$query .= ' AND lower(g.name) LIKE ?';
}
if (isset($filter_data['pay_period_schedule_id']) and isset($filter_data['pay_period_schedule_id'][0]) and !in_array(-1, (array) $filter_data['pay_period_schedule_id'])) {
$query .= ' AND i.pay_period_schedule_id in (' . $this->getListSQL($filter_data['pay_period_schedule_id'], $ph) . ') ';
}
if (isset($filter_data['pay_period_schedule']) and trim($filter_data['pay_period_schedule']) != '') {
$ph[] = strtolower(trim($filter_data['pay_period_schedule']));
$query .= ' AND lower(h.name) LIKE ?';
}
if (isset($filter_data['policy_group_id']) and isset($filter_data['policy_group_id'][0]) and !in_array(-1, (array) $filter_data['policy_group_id'])) {
$query .= ' AND k.policy_group_id in (' . $this->getListSQL($filter_data['policy_group_id'], $ph) . ') ';
}
if (isset($filter_data['policy_group']) and trim($filter_data['policy_group']) != '') {
$ph[] = strtolower(trim($filter_data['policy_group']));
$query .= ' AND lower(i.name) LIKE ?';
}
if (isset($filter_data['sex']) and trim($filter_data['sex']) != '' and !isset($filter_data['sex_id'])) {
$filter_data['sex_id'] = Option::getByFuzzyValue($filter_data['sex'], $this->getOptions('sex'));
}
if (isset($filter_data['sex_id']) and isset($filter_data['sex_id'][0]) and !in_array(-1, (array) $filter_data['sex_id'])) {
$query .= ' AND a.sex_id in (' . $this->getListSQL($filter_data['sex_id'], $ph) . ') ';
}
if (isset($filter_data['country']) and isset($filter_data['country'][0]) and !in_array(-1, (array) $filter_data['country'])) {
$query .= ' AND a.country in (' . $this->getListSQL($filter_data['country'], $ph) . ') ';
}
if (isset($filter_data['province']) and isset($filter_data['province'][0]) and !in_array(-1, (array) $filter_data['province']) and !in_array('00', (array) $filter_data['province'])) {
$query .= ' AND a.province in (' . $this->getListSQL($filter_data['province'], $ph) . ') ';
}
if (isset($filter_data['city']) and trim($filter_data['city']) != '') {
$ph[] = strtolower(trim($filter_data['city']));
$query .= ' AND lower(a.city) LIKE ?';
}
if (isset($filter_data['first_name']) and trim($filter_data['first_name']) != '') {
$ph[] = strtolower(trim($filter_data['first_name']));
$query .= ' AND lower(a.first_name) LIKE ?';
}
if (isset($filter_data['last_name']) and trim($filter_data['last_name']) != '') {
$ph[] = strtolower(trim($filter_data['last_name']));
$query .= ' AND lower(a.last_name) LIKE ?';
}
if (isset($filter_data['home_phone']) and trim($filter_data['home_phone']) != '') {
$ph[] = trim($filter_data['home_phone']);
$query .= ' AND a.home_phone LIKE ?';
}
if (isset($filter_data['employee_number']) and trim($filter_data['employee_number']) != '') {
$ph[] = trim($filter_data['employee_number']);
$query .= ' AND a.employee_number LIKE ?';
}
if (isset($filter_data['user_name']) and trim($filter_data['user_name']) != '') {
$ph[] = strtolower(trim($filter_data['user_name']));
$query .= ' AND lower(a.user_name) LIKE ?';
}
if (isset($filter_data['sin']) and trim($filter_data['sin']) != '') {
$ph[] = trim($filter_data['sin']);
$query .= ' AND a.sin LIKE ?';
}
if (isset($filter_data['created_date']) and trim($filter_data['created_date']) != '') {
$date_filter = $this->getDateRangeSQL($filter_data['created_date'], 'a.created_date');
if ($date_filter != FALSE) {
$query .= ' AND ' . $date_filter;
}
unset($date_filter);
}
if (isset($filter_data['updated_date']) and trim($filter_data['updated_date']) != '') {
$date_filter = $this->getDateRangeSQL($filter_data['updated_date'], 'a.updated_date');
if ($date_filter != FALSE) {
$query .= ' AND ' . $date_filter;
}
unset($date_filter);
}
if (isset($filter_data['created_by']) and trim($filter_data['created_by']) != '') {
$ph[] = $ph[] = strtolower(trim($filter_data['created_by']));
$query .= ' AND (lower(y.first_name) LIKE ? OR lower(y.last_name) LIKE ? ) ';
}
if (isset($filter_data['updated_by']) and trim($filter_data['updated_by']) != '') {
$ph[] = $ph[] = strtolower(trim($filter_data['updated_by']));
$query .= ' AND (lower(z.first_name) LIKE ? OR lower(z.last_name) LIKE ? ) ';
}
$query .= '
AND ( a.deleted = 0 )
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
Debug::Text('Query: ' . $query, __FILE__, __LINE__, __METHOD__, 10);
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('pay_stub_entry_name', 'user_status_id', 'last_name', 'first_name', 'default_branch', 'default_department', 'user_group', 'title');
$sort_column_aliases = array('user_status' => 'user_status_id', 'status' => 'status_id', 'type' => 'type_id');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('effective_date' => 'desc', 'last_name' => 'asc');
$strict = FALSE;
} else {
//Always sort by effective_date,last name after other columns
if (!isset($order['effective_date'])) {
$order['effective_date'] = 'desc';
}
if (!isset($order['last_name'])) {
$order['last_name'] = 'asc';
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
if (strncmp($this->db->databaseType, 'mysql', 5) == 0) {
$to_timestamp_sql = 'from_unixtime(a.effective_date)';
} else {
$to_timestamp_sql = 'to_timestamp(a.effective_date)';
}
$uf = new UserFactory();
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$pseaf = new PayStubEntryAccountFactory();
$ppf = new PayPeriodFactory();
$ppsf = new PayPeriodScheduleFactory();
$ppsuf = new PayPeriodScheduleUserFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
b.first_name as first_name,
b.last_name as last_name,
b.status_id as user_status_id,
b.default_branch_id as default_branch_id,
bf.name as default_branch,
b.default_department_id as default_department_id,
df.name as default_department,
b.group_id as group_id,
ugf.name as user_group,
b.title_id as title_id,
utf.name as title,
pseaf.name as pay_stub_entry_name,
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 ' . $uf->getTable() . ' as b ON ( a.user_id = b.id AND b.deleted = 0 )
LEFT JOIN ' . $bf->getTable() . ' as bf ON ( b.default_branch_id = bf.id AND bf.deleted = 0)
LEFT JOIN ' . $df->getTable() . ' as df ON ( b.default_department_id = df.id AND df.deleted = 0)
LEFT JOIN ' . $ugf->getTable() . ' as ugf ON ( b.group_id = ugf.id AND ugf.deleted = 0 )
LEFT JOIN ' . $utf->getTable() . ' as utf ON ( b.title_id = utf.id AND utf.deleted = 0 )
LEFT JOIN ' . $pseaf->getTable() . ' as pseaf ON ( a.pay_stub_entry_name_id = pseaf.id AND pseaf.deleted = 0 )
LEFT JOIN ' . $ppsuf->getTable() . ' as ppsuf ON ( a.user_id = ppsuf.user_id )
LEFT JOIN ' . $ppsf->getTable() . ' as ppsf ON ( ppsuf.pay_period_schedule_id = ppsf.id AND ppsf.deleted = 0 )
LEFT JOIN ' . $ppf->getTable() . ' as ppf ON ( ppsuf.pay_period_schedule_id = ppf.pay_period_schedule_id AND ' . $to_timestamp_sql . ' >= ppf.start_date AND ' . $to_timestamp_sql . ' <= ppf.end_date AND ppf.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 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['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['user_id']) and isset($filter_data['user_id'][0]) and !in_array(-1, (array) $filter_data['user_id'])) {
$query .= ' AND a.user_id in (' . $this->getListSQL($filter_data['user_id'], $ph) . ') ';
}
if (isset($filter_data['pay_period_id']) and isset($filter_data['pay_period_id'][0]) and !in_array(-1, (array) $filter_data['pay_period_id'])) {
$query .= ' AND ppf.id in (' . $this->getListSQL($filter_data['pay_period_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 .= 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;
//Need to account for employees being assigned to deleted pay period schedules.
$query .= '
AND ( ppsuf.id IS NULL OR ppsf.id IS NOT NULL )
AND a.deleted = 0
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
//Debug::Arr($query,'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']);
}
}
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['exception_policy_severity_id'])) {
$filter_data['severity_id'] = $filter_data['exception_policy_severity_id'];
}
$additional_order_fields = array('d.name', 'e.name', 'f.name', 'g.name', 'h.status_id', 'i.severity_id', 'i.type_id', 'c.first_name', 'c.last_name', 'c.country', 'c.province', 'b.date_stamp', 'pgf.name', 'pscf.name', 'ppsf.name');
$sort_column_aliases = array('status' => 'status_id', 'type' => 'type_id');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
//$order = array( 'status_id' => 'asc', 'last_name' => 'asc', 'first_name' => 'asc', 'middle_name' => 'asc');
$order = array('i.severity_id' => 'desc', 'c.last_name' => 'asc', 'b.date_stamp' => 'asc', 'i.type_id' => 'asc');
$strict = FALSE;
} else {
//Do order by column conversions, because if we include these columns in the SQL
//query, they contaminate the data array.
if (isset($order['default_branch'])) {
$order['d.name'] = $order['default_branch'];
unset($order['default_branch']);
}
if (isset($order['default_department'])) {
$order['e.name'] = $order['default_department'];
unset($order['default_department']);
}
if (isset($order['user_group'])) {
$order['f.name'] = $order['user_group'];
unset($order['user_group']);
}
if (isset($order['title'])) {
$order['g.name'] = $order['title'];
unset($order['title']);
}
if (isset($order['exception_policy_type_id'])) {
$order['i.type_id'] = $order['exception_policy_type_id'];
unset($order['exception_policy_type_id']);
}
if (isset($order['severity_id'])) {
$order['i.severity_id'] = $order['severity_id'];
unset($order['severity_id']);
}
if (isset($order['severity'])) {
$order['i.severity_id'] = $order['severity'];
unset($order['severity']);
}
if (isset($order['exception_policy_type'])) {
$order['i.type_id'] = $order['exception_policy_type'];
unset($order['exception_policy_type']);
}
if (isset($order['exception_policy_type_id'])) {
$order['i.type_id'] = $order['exception_policy_type_id'];
unset($order['exception_policy_type_id']);
}
if (isset($order['first_name'])) {
$order['c.first_name'] = $order['first_name'];
unset($order['first_name']);
}
if (isset($order['last_name'])) {
$order['c.last_name'] = $order['last_name'];
unset($order['last_name']);
}
if (isset($order['country'])) {
$order['c.country'] = $order['country'];
unset($order['country']);
}
if (isset($order['province'])) {
$order['c.province'] = $order['province'];
unset($order['province']);
}
if (isset($order['date_stamp'])) {
$order['b.date_stamp'] = $order['date_stamp'];
unset($order['date_stamp']);
}
if (isset($order['policy_group'])) {
$order['pgf.name'] = $order['policy_group'];
unset($order['policy_group']);
}
if (isset($order['permission_group'])) {
$order['pscf.name'] = $order['permission_group'];
unset($order['permission_group']);
}
if (isset($order['pay_period_schedule'])) {
$order['ppsf.name'] = $order['pay_period_schedule'];
unset($order['pay_period_schedule']);
}
//Always sort by last name,first name after other columns
if (!isset($order['c.last_name'])) {
$order['c.last_name'] = 'asc';
}
if (!isset($order['c.first_name'])) {
$order['c.first_name'] = 'asc';
}
if (!isset($order['b.date_stamp'])) {
$order['b.date_stamp'] = 'asc';
}
if (!isset($order['i.severity_id'])) {
$order['i.severity_id'] = 'desc';
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$udf = new UserDateFactory();
$uf = new UserFactory();
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$ppf = new PayPeriodFactory();
$ppsf = new PayPeriodScheduleFactory();
$epf = new ExceptionPolicyFactory();
$epcf = new ExceptionPolicyControlFactory();
$pguf = new PolicyGroupUserFactory();
$pgf = new PolicyGroupFactory();
$pf = new PunchFactory();
$pcf = new PunchControlFactory();
$pscf = new PermissionControlFactory();
$puf = new PermissionUserFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
b.date_stamp as date_stamp,
b.pay_period_id as pay_period_id,
h.pay_period_schedule_id as pay_period_schedule_id,
i.severity_id as severity_id,
i.type_id as exception_policy_type_id,
b.user_id as user_id,
h.start_date as pay_period_start_date,
h.end_date as pay_period_end_date,
h.transaction_date as pay_period_transaction_date,
c.first_name as first_name,
c.last_name as last_name,
c.country as country,
c.province as province,
c.status_id as user_status_id,
c.group_id as group_id,
f.name as "group",
c.title_id as title_id,
g.name as title,
c.default_branch_id as default_branch_id,
d.name as default_branch,
c.default_department_id as default_department_id,
e.name as default_department,
pcf.branch_id as branch_id,
bf.name as branch,
pcf.department_id as department_id,
df.name as department,
pgf.name as policy_group,
pscf.name as permission_group,
ppsf.name as pay_period_schedule,
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 ' . $pf->getTable() . ' as pf ON ( a.punch_id IS NOT NULL AND a.punch_id = pf.id AND pf.deleted = 0)
LEFT JOIN ' . $pcf->getTable() . ' as pcf ON ( ( ( pf.id IS NOT NULL AND pf.punch_control_id = pcf.id ) OR ( a.punch_control_id is NOT NULL AND a.punch_control_id = pcf.id ) ) AND pcf.deleted = 0)
LEFT JOIN ' . $bf->getTable() . ' as bf ON pcf.branch_id = bf.id
LEFT JOIN ' . $df->getTable() . ' as df ON pcf.department_id = df.id
LEFT JOIN ' . $udf->getTable() . ' as b ON a.user_date_id = b.id
LEFT JOIN ' . $uf->getTable() . ' as c ON b.user_id = c.id
LEFT JOIN ' . $bf->getTable() . ' as d ON c.default_branch_id = d.id
LEFT JOIN ' . $df->getTable() . ' as e ON c.default_department_id = e.id
LEFT JOIN ' . $ugf->getTable() . ' as f ON c.group_id = f.id
LEFT JOIN ' . $utf->getTable() . ' as g ON c.title_id = g.id
LEFT JOIN ' . $ppf->getTable() . ' as h ON b.pay_period_id = h.id
LEFT JOIN ' . $ppsf->getTable() . ' as ppsf ON ppsf.id = h.pay_period_schedule_id
LEFT JOIN ' . $epf->getTable() . ' as i ON a.exception_policy_id = i.id
LEFT JOIN ' . $epcf->getTable() . ' as epcf ON epcf.id = i.exception_policy_control_id
LEFT JOIN ' . $pguf->getTable() . ' as pguf ON b.user_id = pguf.user_id
LEFT JOIN ' . $pgf->getTable() . ' as pgf ON pguf.policy_group_id = pgf.id
LEFT JOIN ' . $puf->getTable() . ' as puf ON c.id = puf.user_id
LEFT JOIN ' . $pscf->getTable() . ' as pscf ON pscf.id = puf.permission_control_id
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 c.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 c.id 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['user_id']) and isset($filter_data['user_id'][0]) and !in_array(-1, (array) $filter_data['user_id'])) {
$query .= ' AND c.id in (' . $this->getListSQL($filter_data['user_id'], $ph) . ') ';
}
$query .= isset($filter_data['exclude_user_id']) ? $this->getWhereClauseSQL('c.id', $filter_data['exclude_user_id'], 'not_numeric_list', $ph) : NULL;
if (isset($filter_data['user_status_id']) and isset($filter_data['user_status_id'][0]) and !in_array(-1, (array) $filter_data['user_status_id'])) {
$query .= ' AND c.status_id in (' . $this->getListSQL($filter_data['user_status_id'], $ph) . ') ';
}
if (isset($filter_data['type_id']) and isset($filter_data['type_id'][0]) and !in_array(-1, (array) $filter_data['type_id'])) {
$query .= ' AND a.type_id in (' . $this->getListSQL($filter_data['type_id'], $ph) . ') ';
}
if (isset($filter_data['severity_id']) and isset($filter_data['severity_id'][0]) and !in_array(-1, (array) $filter_data['severity_id'])) {
$query .= ' AND i.severity_id in (' . $this->getListSQL($filter_data['severity_id'], $ph) . ') ';
}
if (isset($filter_data['exception_policy_type_id']) and isset($filter_data['exception_policy_type_id'][0]) and !in_array(-1, (array) $filter_data['exception_policy_type_id'])) {
$query .= ' AND i.type_id in (' . $this->getListSQL($filter_data['exception_policy_type_id'], $ph) . ') ';
}
if (isset($filter_data['pay_period_id']) and isset($filter_data['pay_period_id'][0]) and !in_array(-1, (array) $filter_data['pay_period_id'])) {
$query .= ' AND b.pay_period_id in (' . $this->getListSQL($filter_data['pay_period_id'], $ph) . ') ';
}
if (isset($filter_data['pay_period_status_id']) and isset($filter_data['pay_period_status_id'][0]) and !in_array(-1, (array) $filter_data['pay_period_status_id'])) {
$query .= ' AND h.status_id in (' . $this->getListSQL($filter_data['pay_period_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 c.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 c.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 c.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 c.title_id in (' . $this->getListSQL($filter_data['title_id'], $ph) . ') ';
}
if (isset($filter_data['branch_id']) and isset($filter_data['branch_id'][0]) and !in_array(-1, (array) $filter_data['branch_id'])) {
$query .= ' AND pcf.branch_id in (' . $this->getListSQL($filter_data['branch_id'], $ph) . ') ';
}
if (isset($filter_data['department_id']) and isset($filter_data['department_id'][0]) and !in_array(-1, (array) $filter_data['department_id'])) {
$query .= ' AND pcf.department_id in (' . $this->getListSQL($filter_data['department_id'], $ph) . ') ';
}
if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') {
$ph[] = $this->db->BindDate($filter_data['start_date']);
$query .= ' AND b.date_stamp >= ?';
}
if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') {
$ph[] = $this->db->BindDate($filter_data['end_date']);
$query .= ' AND b.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 accept exception rows assign to pay_period_id = 0 (no pay period), as this can happen when punches exist in the future.
$query .= '
AND ( a.deleted = 0 AND b.deleted = 0 AND c.deleted = 0 AND pgf.deleted = 0 AND ( h.deleted = 0 OR h.deleted is NULL ) )
';
$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;
}
if (isset($pay_period_schedule_data)) {
if (isset($pay_period_schedule_data['anchor_date'])) {
$pay_period_schedule_data['anchor_date'] = TTDate::parseDateTime($pay_period_schedule_data['anchor_date']);
}
if (isset($pay_period_schedule_data['day_start_time'])) {
$pay_period_schedule_data['day_start_time'] = TTDate::parseTimeUnit($pay_period_schedule_data['day_start_time']);
}
if (isset($pay_period_schedule_data['new_day_trigger_time'])) {
$pay_period_schedule_data['new_day_trigger_time'] = TTDate::parseTimeUnit($pay_period_schedule_data['new_day_trigger_time']);
}
if (isset($pay_period_schedule_data['maximum_shift_time'])) {
$pay_period_schedule_data['maximum_shift_time'] = TTDate::parseTimeUnit($pay_period_schedule_data['maximum_shift_time']);
}
}
//var_dump($pay_period_schedule_data);
$ppsf = new PayPeriodScheduleFactory();
$action = Misc::findSubmitButton();
$action = strtolower($action);
switch ($action) {
case 'submit':
//Debug::setVerbosity(11);
Debug::Text('Submit!', __FILE__, __LINE__, __METHOD__, 10);
$ppsf->StartTransaction();
$ppsf->setId($pay_period_schedule_data['id']);
$ppsf->setCompany($current_company->getId());
$ppsf->setName($pay_period_schedule_data['name']);
$ppsf->setDescription($pay_period_schedule_data['description']);
$ppsf->setType($pay_period_schedule_data['type']);
$ppsf->setStartWeekDay($pay_period_schedule_data['start_week_day_id']);
if ($pay_period_schedule_data['type'] == 5) {
$ppsf->setAnnualPayPeriods($pay_period_schedule_data['annual_pay_periods']);
$last_date = $punch_obj->getPunchControlObject()->getUserDateObject()->getDateStamp();
} else {
$last_date = TTDate::getTime();
}
Debug::Text('Last Punch Date: ' . TTDate::getDate('DATE+TIME', $last_date), __FILE__, __LINE__, __METHOD__, 10);
//Get pay period of last shift workd
$plf = new PayPeriodListFactory();
$pay_period_obj = $plf->getByUserIdAndEndDate($user_id, $last_date)->getCurrent();
$pay_period_type_id = FALSE;
if (is_object($pay_period_obj->getPayPeriodScheduleObject())) {
$pay_period_type_id = $pay_period_obj->getPayPeriodScheduleObject()->getType();
}
$roe_data = array('user_id' => $user_id, 'pay_period_type_id' => $pay_period_type_id, 'first_date' => $first_date, 'last_date' => $last_date, 'pay_period_end_date' => $pay_period_obj->getEndDate());
}
//Select box options;
$roe_data['code_options'] = $roef->getOptions('code');
$ppsf = new PayPeriodScheduleFactory();
$roe_data['pay_period_type_options'] = $ppsf->getOptions('type');
unset($roe_data['pay_period_type_options'][5]);
$user_options = UserListFactory::getByCompanyIdArray($current_company->getId(), FALSE);
$smarty->assign_by_ref('user_options', $user_options);
//PSEA accounts
$psealf = new PayStubEntryAccountListFactory();
$earning_pay_stub_entry_account_options = $psealf->getByCompanyIdAndStatusIdAndTypeIdArray($current_company->getId(), 10, array(10, 30, 40), TRUE);
$smarty->assign_by_ref('earning_pay_stub_entry_account_options', $earning_pay_stub_entry_account_options);
$smarty->assign_by_ref('roe_data', $roe_data);
$smarty->assign_by_ref('setup_data', $setup_data);
break;
}
$smarty->assign_by_ref('roef', $roef);
$smarty->display('roe/EditROE.tpl');
function getObjectAsArray($include_columns = NULL)
{
$variable_function_map = $this->getVariableToFunctionMap();
if (is_array($variable_function_map)) {
$ppsf = new PayPeriodScheduleFactory();
foreach ($variable_function_map as $variable => $function_stub) {
if ($include_columns == NULL or isset($include_columns[$variable]) and $include_columns[$variable] == TRUE) {
$function = 'get' . $function_stub;
switch ($variable) {
case 'status':
$function = 'get' . $variable;
if (method_exists($this, $function)) {
$data[$variable] = Option::getByKey($this->{$function}(), $this->getOptions($variable));
}
break;
case 'type':
//Make sure type_id is set first.
if (isset($data['type_id'])) {
$data[$variable] = Option::getByKey($data['type_id'], $ppsf->getOptions($variable));
} else {
$data[$variable] = NULL;
}
break;
case 'type_id':
case 'pay_period_schedule':
$data[$variable] = $this->getColumn($variable);
break;
case 'start_date':
case 'end_date':
case 'transaction_date':
if (method_exists($this, $function)) {
$data[$variable] = TTDate::getAPIDate('DATE+TIME', $this->{$function}());
}
break;
default:
if (method_exists($this, $function)) {
$data[$variable] = $this->{$function}();
}
break;
}
}
}
$this->getCreatedAndUpdatedColumns(&$data, $include_columns);
}
return $data;
}
