$filter_data['src_user_title_options'] = Misc::arrayDiffByKey((array) $filter_data['user_title_ids'], $user_title_options);
$filter_data['selected_user_title_options'] = Misc::arrayIntersectByKey((array) $filter_data['user_title_ids'], $user_title_options);
if ($current_company->getProductEdition() == 20) {
$jlf = new JobListFactory();
//Get include job list.
$jlf->getByCompanyId($current_company->getId());
$job_options = Misc::prependArray(array('0' => TTi18n::gettext('- No Job -')), $jlf->getArrayByListFactory($jlf, FALSE, TRUE));
$filter_data['job_manual_id_options'] = $jlf->getManualIDArrayByListFactory($jlf, TRUE);
$filter_data['src_include_job_options'] = Misc::arrayDiffByKey((array) $filter_data['include_job_ids'], $job_options);
$filter_data['selected_include_job_options'] = Misc::arrayIntersectByKey((array) $filter_data['include_job_ids'], $job_options);
//Get exclude job list
$exclude_job_options = Misc::prependArray($all_array_option, $jlf->getArrayByListFactory($jlf, FALSE, TRUE));
$filter_data['src_exclude_job_options'] = Misc::arrayDiffByKey((array) $filter_data['exclude_job_ids'], $job_options);
$filter_data['selected_exclude_job_options'] = Misc::arrayIntersectByKey((array) $filter_data['exclude_job_ids'], $job_options);
//Get Job Groups
$jglf = new JobGroupListFactory();
$nodes = FastTree::FormatArray($jglf->getByCompanyIdArray($current_company->getId()), 'TEXT', TRUE);
$job_group_options = Misc::prependArray($all_array_option, $jglf->getArrayByNodes($nodes, FALSE, TRUE));
$filter_data['src_job_group_options'] = Misc::arrayDiffByKey((array) $filter_data['job_group_ids'], $job_group_options);
$filter_data['selected_job_group_options'] = Misc::arrayIntersectByKey((array) $filter_data['job_group_ids'], $job_group_options);
//Get Job Items
$jilf = new JobItemListFactory();
$jilf->getByCompanyId($current_company->getId());
$job_item_options = Misc::prependArray(array('-1' => TTi18n::gettext('-- All --'), '0' => TTi18n::gettext('- No Task -')), $jilf->getArrayByListFactory($jilf, FALSE, TRUE));
$filter_data['src_job_item_options'] = Misc::arrayDiffByKey((array) $filter_data['job_item_ids'], $job_item_options);
$filter_data['selected_job_item_options'] = Misc::arrayIntersectByKey((array) $filter_data['job_item_ids'], $job_item_options);
}
//Get column list
$filter_data['src_column_options'] = Misc::arrayDiffByKey((array) $filter_data['column_ids'], $columns);
$filter_data['selected_column_options'] = Misc::arrayIntersectByKey((array) $filter_data['column_ids'], $columns);
//$filter_data['refresh_options'] = array( 0 => TTi18n::gettext('- Disabled -'), 60 => TTi18n::gettext('1 minute'), 300 => TTi18n::gettext('5 minutes'), 600 => TTi18n::gettext('10 minutes'), 1800 => TTi18n::gettext('30 minutes'), 3600 => TTi18n::gettext('60 minutes') );
$pay_stub_entry_options = $psealf->getByCompanyIdAndStatusIdAndTypeIdArray($current_company->getId(), 10, array(10, 20, 30, 50));
//Get branches
$blf = new BranchListFactory();
$blf->getByCompanyId($current_company->getId());
$branch_options = $blf->getArrayByListFactory($blf, FALSE, TRUE);
$data['src_branch_options'] = Misc::arrayDiffByKey((array) $data['branch_ids'], $branch_options);
$data['selected_branch_options'] = Misc::arrayIntersectByKey((array) $data['branch_ids'], $branch_options);
//Get departments
$dlf = new DepartmentListFactory();
$dlf->getByCompanyId($current_company->getId());
$department_options = $dlf->getArrayByListFactory($dlf, FALSE, TRUE);
$data['src_department_options'] = Misc::arrayDiffByKey((array) $data['department_ids'], $department_options);
$data['selected_department_options'] = Misc::arrayIntersectByKey((array) $data['department_ids'], $department_options);
if ($current_company->getProductEdition() == 20) {
//Get Job Groups
$jglf = new JobGroupListFactory();
$nodes = FastTree::FormatArray($jglf->getByCompanyIdArray($current_company->getId()), 'TEXT', TRUE);
$job_group_options = $jglf->getArrayByNodes($nodes, FALSE, FALSE);
$data['src_job_group_options'] = Misc::arrayDiffByKey((array) $data['job_group_ids'], $job_group_options);
$data['selected_job_group_options'] = Misc::arrayIntersectByKey((array) $data['job_group_ids'], $job_group_options);
//Get Jobs
$jlf = new JobListFactory();
$jlf->getByCompanyId($current_company->getId());
$job_options = $jlf->getArrayByListFactory($jlf, FALSE, TRUE);
$data['src_job_options'] = Misc::arrayDiffByKey((array) $data['job_ids'], $job_options);
$data['selected_job_options'] = Misc::arrayIntersectByKey((array) $data['job_ids'], $job_options);
//Get Job Item Groups
$jiglf = new JobItemGroupListFactory();
$nodes = FastTree::FormatArray($jiglf->getByCompanyIdArray($current_company->getId()), 'TEXT', TRUE);
$job_item_group_options = $jiglf->getArrayByNodes($nodes, FALSE, FALSE);
$data['src_job_item_group_options'] = Misc::arrayDiffByKey((array) $data['job_item_group_ids'], $job_item_group_options);
function setJobGroup($ids)
{
if (getTTProductEdition() != TT_PRODUCT_PROFESSIONAL) {
return FALSE;
}
Debug::text('Setting IDs...', __FILE__, __LINE__, __METHOD__, 10);
if (is_array($ids)) {
$tmp_ids = array();
if (!$this->isNew()) {
//If needed, delete mappings first.
$lf_a = new PremiumPolicyJobGroupListFactory();
$lf_a->getByPremiumPolicyId($this->getId());
foreach ($lf_a as $obj) {
$id = $obj->getJobGroup();
Debug::text('Job Group ID: ' . $obj->getJobGroup() . ' ID: ' . $id, __FILE__, __LINE__, __METHOD__, 10);
//Delete users that are not selected.
if (!in_array($id, $ids)) {
Debug::text('Deleting: ' . $id, __FILE__, __LINE__, __METHOD__, 10);
$obj->Delete();
} else {
//Save ID's that need to be updated.
Debug::text('NOT Deleting : ' . $id, __FILE__, __LINE__, __METHOD__, 10);
$tmp_ids[] = $id;
}
}
unset($id, $obj);
}
//Insert new mappings.
$lf_b = new JobGroupListFactory();
foreach ($ids as $id) {
if (isset($ids) and $id > 0 and !in_array($id, $tmp_ids)) {
$f = new PremiumPolicyJobGroupFactory();
$f->setPremiumPolicy($this->getId());
$f->setJobGroup($id);
$obj = $lf_b->getById($id)->getCurrent();
if ($this->Validator->isTrue('job_group', $f->Validator->isValid(), TTi18n::gettext('Selected Job Group is invalid') . ' (' . $obj->getName() . ')')) {
$f->save();
}
}
}
return TRUE;
}
Debug::text('No IDs to set.', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
function getScheduleSummaryReportByCompanyIdAndArrayCriteria($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('schedule_policy_id', 'schedule_policy', 'first_name', 'last_name', 'user_status_id', 'group_id', 'group', 'title_id', 'title', 'default_branch_id', 'default_branch', 'default_department_id', 'default_department', 'total_time', 'date_stamp', 'pay_period_id');
$sort_column_aliases = array('updated_date' => 'a.updated_date', 'created_date' => 'a.created_date');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('c.pay_period_id' => 'asc', 'c.user_id' => 'asc', 'a.start_time' => 'asc');
$strict = FALSE;
} else {
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
if (isset($filter_data['exclude_user_ids'])) {
$filter_data['exclude_user_id'] = $filter_data['exclude_user_ids'];
}
if (isset($filter_data['include_user_ids'])) {
$filter_data['include_user_id'] = $filter_data['include_user_ids'];
}
if (isset($filter_data['user_status_ids'])) {
$filter_data['user_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['branch_id'] = $filter_data['branch_ids'];
}
if (isset($filter_data['department_ids'])) {
$filter_data['department_id'] = $filter_data['department_ids'];
}
if (isset($filter_data['schedule_branch_id'])) {
$filter_data['branch_id'] = $filter_data['schedule_branch_id'];
}
if (isset($filter_data['schedule_department_id'])) {
$filter_data['department_id'] = $filter_data['schedule_department_id'];
}
if (isset($filter_data['exclude_job_ids'])) {
$filter_data['exclude_job_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'];
}
if (isset($filter_data['pay_period_ids'])) {
$filter_data['pay_period_id'] = $filter_data['pay_period_ids'];
}
if (isset($filter_data['start_time'])) {
$filter_data['start_date'] = $filter_data['start_time'];
}
if (isset($filter_data['end_time'])) {
$filter_data['end_date'] = $filter_data['end_time'];
}
$spf = new SchedulePolicyFactory();
$apf = new AbsencePolicyFactory();
$uf = new UserFactory();
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$udf = new UserDateFactory();
$ppf = new PayPeriodFactory();
$uwf = new UserWageFactory();
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$jf = new JobFactory();
$jif = new JobItemFactory();
}
$ph = array('company_id' => $company_id, 'company_id2' => $company_id);
//"group" is a reserved word in MySQL.
$query = '
select
a.id as id,
a.id as schedule_id,
a.status_id as status_id,
a.start_time as start_time,
a.end_time as end_time,
a.user_date_id as user_date_id,
a.branch_id as branch_id,
j.name as branch,
a.department_id as department_id,
k.name as department,
a.job_id as job_id,
a.job_item_id as job_item_id,
a.total_time as total_time,
a.schedule_policy_id as schedule_policy_id,
i.name as schedule_policy,
a.note as note,
a.absence_policy_id as absence_policy_id,
apf.name as absence_policy,
apf.type_id as absence_policy_type_id,
c.user_id as user_id,
c.date_stamp as date_stamp,
ppf.id as pay_period_id,
ppf.start_date as pay_period_start_date,
ppf.end_date as pay_period_end_date,
ppf.transaction_date as pay_period_transaction_date,
c.pay_period_id as pay_period_id,
d.first_name as first_name,
d.last_name as last_name,
d.status_id as user_status_id,
d.group_id as group_id,
g.name as "group",
d.title_id as title_id,
h.name as title,
d.default_branch_id as default_branch_id,
e.name as default_branch,
d.default_department_id as default_department_id,
f.name as default_department,
d.created_by as user_created_by,
m.id as user_wage_id,
m.hourly_rate as user_wage_hourly_rate,
m.labor_burden_percent as user_labor_burden_percent,
m.effective_date as user_wage_effective_date,
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';
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$query .= ',
w.name as job_name,
w.status_id as job_status_id,
w.manual_id as job_manual_id,
w.branch_id as job_branch_id,
w.department_id as job_department_id,
w.group_id as job_group_id';
}
$query .= '
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $spf->getTable() . ' as i ON ( a.schedule_policy_id = i.id AND i.deleted = 0)
LEFT JOIN ' . $apf->getTable() . ' as apf ON ( a.absence_policy_id = apf.id AND apf.deleted = 0)
LEFT JOIN ' . $udf->getTable() . ' as c ON a.user_date_id = c.id
LEFT JOIN ' . $ppf->getTable() . ' as ppf ON c.pay_period_id = ppf.id
LEFT JOIN ' . $uf->getTable() . ' as d ON ( c.user_id = d.id AND d.deleted = 0 )
LEFT JOIN ' . $bf->getTable() . ' as e ON ( d.default_branch_id = e.id AND e.deleted = 0)
LEFT JOIN ' . $df->getTable() . ' as f ON ( d.default_department_id = f.id AND f.deleted = 0)
LEFT JOIN ' . $ugf->getTable() . ' as g ON ( d.group_id = g.id AND g.deleted = 0 )
LEFT JOIN ' . $utf->getTable() . ' as h ON ( d.title_id = h.id AND h.deleted = 0 )
LEFT JOIN ' . $bf->getTable() . ' as j ON ( a.branch_id = j.id AND j.deleted = 0)
LEFT JOIN ' . $df->getTable() . ' as k ON ( a.department_id = k.id AND k.deleted = 0)
LEFT JOIN ' . $uwf->getTable() . ' as m ON m.id = (select m.id
from ' . $uwf->getTable() . ' as m
where m.user_id = c.user_id
and m.effective_date <= c.date_stamp
and m.deleted = 0
order by m.effective_date desc limit 1)
';
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$query .= ' LEFT JOIN ' . $jf->getTable() . ' as w ON a.job_id = w.id';
$query .= ' LEFT JOIN ' . $jif->getTable() . ' as x ON a.job_item_id = x.id';
}
$query .= '
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 ( d.company_id = ? OR a.company_id = ? )';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('d.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;
//Need to include/exclude users based on c.user_id, as we need to support OPEN shifts and user_id=0 which can only happen in user_date table.
$query .= isset($filter_data['include_user_id']) ? $this->getWhereClauseSQL('c.user_id', $filter_data['include_user_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['exclude_user_id']) ? $this->getWhereClauseSQL('c.user_id', $filter_data['exclude_user_id'], 'not_numeric_list', $ph) : NULL;
$query .= isset($filter_data['user_status_id']) ? $this->getWhereClauseSQL('d.status_id', $filter_data['user_status_id'], 'numeric_list', $ph) : NULL;
if (isset($filter_data['include_user_subgroups']) and (bool) $filter_data['include_user_subgroups'] == TRUE) {
$uglf = new UserGroupListFactory();
$filter_data['group_id'] = $uglf->getByCompanyIdAndGroupIdAndSubGroupsArray($company_id, $filter_data['group_id'], TRUE);
}
$query .= isset($filter_data['group_id']) ? $this->getWhereClauseSQL('d.group_id', $filter_data['group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['default_branch_id']) ? $this->getWhereClauseSQL('d.default_branch_id', $filter_data['default_branch_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['default_department_id']) ? $this->getWhereClauseSQL('d.default_department_id', $filter_data['default_department_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['title_id']) ? $this->getWhereClauseSQL('d.title_id', $filter_data['title_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['branch_id']) ? $this->getWhereClauseSQL('a.branch_id', $filter_data['branch_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['department_id']) ? $this->getWhereClauseSQL('a.department_id', $filter_data['department_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['status_id']) ? $this->getWhereClauseSQL('a.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['schedule_policy_id']) ? $this->getWhereClauseSQL('a.pay_period_id', $filter_data['schedule_policy_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['pay_period_id']) ? $this->getWhereClauseSQL('c.pay_period_id', $filter_data['pay_period_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['tag']) ? $this->getWhereClauseSQL('d.id', array('company_id' => $company_id, 'object_type_id' => 200, 'tag' => $filter_data['tag']), 'tag', $ph) : NULL;
//Use the job_id in the schedule table so we can filter by '0' or No Job
$query .= isset($filter_data['include_job_id']) ? $this->getWhereClauseSQL('a.job_id', $filter_data['include_job_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['exclude_job_id']) ? $this->getWhereClauseSQL('a.job_id', $filter_data['exclude_job_id'], 'not_numeric_list', $ph) : NULL;
if (isset($filter_data['include_job_subgroups']) and (bool) $filter_data['include_job_subgroups'] == TRUE) {
$jglf = new JobGroupListFactory();
$filter_data['job_group_id'] = $jglf->getByCompanyIdAndGroupIdAndSubGroupsArray($company_id, $filter_data['job_group_id'], TRUE);
}
$query .= isset($filter_data['job_group_id']) ? $this->getWhereClauseSQL('w.group_id', $filter_data['job_group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['job_item_id']) ? $this->getWhereClauseSQL('a.job_item_id', $filter_data['job_item_id'], 'numeric_list', $ph) : NULL;
if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') {
$ph[] = $this->db->BindTimeStamp($filter_data['start_date']);
$query .= ' AND a.start_time >= ?';
}
if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') {
$ph[] = $this->db->BindTimeStamp($filter_data['end_date']);
$query .= ' AND a.start_time <= ?';
}
$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 AND c.deleted = 0 )
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
//Debug::Arr($ph,'Query: '. $query, __FILE__, __LINE__, __METHOD__,10);
$this->ExecuteSQL($query, $ph, $limit, $page);
return $this;
}
