function createOverTimePolicy($company_id, $type, $accrual_policy_id = NULL)
{
$otpf = new OverTimePolicyFactory();
$otpf->setCompany($company_id);
switch ($type) {
case 10:
$otpf->setName('OverTime (>8hrs)');
$otpf->setType(10);
$otpf->setTriggerTime(3600 * 8);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 1'));
$otpf->setAccrualPolicyId(0);
$otpf->setAccrualRate(0);
break;
case 20:
$otpf->setName('Daily (>10hrs)');
$otpf->setType(10);
$otpf->setTriggerTime(3600 * 10);
$otpf->setRate('1.0');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId($accrual_policy_id);
$otpf->setAccrualRate('1.0');
}
if ($otpf->isValid()) {
$insert_id = $otpf->Save();
Debug::Text('Overtime Policy ID: ' . $insert_id, __FILE__, __LINE__, __METHOD__, 10);
return $insert_id;
}
Debug::Text('Failed Creating Overtime Policy!', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
require_once '../../includes/global.inc.php';
require_once Environment::getBasePath() . 'includes/Interface.inc.php';
if (!$permission->Check('over_time_policy', 'enabled') or !($permission->Check('over_time_policy', 'edit') or $permission->Check('over_time_policy', 'edit_own'))) {
$permission->Redirect(FALSE);
//Redirect
}
$smarty->assign('title', TTi18n::gettext($title = 'Edit Overtime Policy'));
// See index.php
/*
* Get FORM variables
*/
extract(FormVariables::GetVariables(array('action', 'id', 'data')));
if (isset($data['trigger_time'])) {
$data['trigger_time'] = TTDate::parseTimeUnit($data['trigger_time']);
}
$otpf = new OverTimePolicyFactory();
$action = Misc::findSubmitButton();
$action = strtolower($action);
switch ($action) {
case 'submit':
Debug::Text('Submit!', __FILE__, __LINE__, __METHOD__, 10);
$otpf->setId($data['id']);
$otpf->setCompany($current_company->getId());
$otpf->setName($data['name']);
$otpf->setType($data['type_id']);
//$otpf->setLevel( $data['level'] );
$otpf->setTriggerTime($data['trigger_time']);
$otpf->setRate($data['rate']);
$otpf->setWageGroup($data['wage_group_id']);
$otpf->setAccrualPolicyId($data['accrual_policy_id']);
$otpf->setAccrualRate($data['accrual_rate']);
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_date_total_type_id'])) {
$filter_data['type_id'] = $filter_data['user_date_total_type_id'];
}
$additional_order_fields = array('first_name', 'last_name', 'date_stamp', 'time_stamp', 'type_id', 'status_id', 'branch', 'department', 'default_branch', 'default_department', 'group', 'title');
if ($order == NULL) {
$order = array('c.date_stamp' => 'asc', 'a.status_id' => 'asc', 'a.type_id' => 'asc', 'a.total_time' => 'asc', 'a.status_id' => 'asc');
$strict = FALSE;
} else {
$strict = FALSE;
}
//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_id'] = $filter_data['exclude_user_ids'];
}
if (isset($filter_data['include_user_ids'])) {
$filter_data['user_id'] = $filter_data['include_user_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['branch_ids'])) {
$filter_data['default_branch_id'] = $filter_data['branch_ids'];
}
if (isset($filter_data['department_ids'])) {
$filter_data['default_department_id'] = $filter_data['department_ids'];
}
if (isset($filter_data['pay_period_ids'])) {
$filter_data['pay_period_id'] = $filter_data['pay_period_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'];
}
$uf = new UserFactory();
$udf = new UserDateFactory();
$uwf = new UserWageFactory();
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$otpf = new OverTimePolicyFactory();
$apf = new AbsencePolicyFactory();
$ppf = new PremiumPolicyFactory();
$mpf = new MealPolicyFactory();
$bpf = new BreakPolicyFactory();
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$jf = new JobFactory();
$jif = new JobItemFactory();
}
$ph = array('company_id' => $company_id);
$query = '
select
a.id as id,
a.user_date_id as user_date_id,
a.type_id as type_id,
a.status_id as status_id,
a.punch_control_id as punch_control_id,
a.over_time_policy_id as over_time_policy_id,
otpf.name as over_time_policy,
a.absence_policy_id as absence_policy_id,
apf.name as absence_policy,
apf.type_id as absence_policy_type_id,
a.premium_policy_id as premium_policy_id,
ppf.name as premium_policy,
a.meal_policy_id as meal_policy_id,
mpf.name as meal_policy,
a.break_policy_id as break_policy_id,
bpf.name as break_policy,
a.start_time_stamp as start_time_stamp,
a.end_time_stamp as end_time_stamp,
a.override as override,
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.quantity as quantity,
a.bad_quantity as bad_quantity,
a.total_time as total_time,
a.actual_total_time as actual_total_time,
c.user_id as user_id,
c.date_stamp as date_stamp,
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,
z.id as user_wage_id,
z.effective_date as user_wage_effective_date ';
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$query .= ',
x.name as job,
x.name as job_name,
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';
}
$query .= '
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $udf->getTable() . ' as c ON a.user_date_id = c.id
LEFT JOIN ' . $uf->getTable() . ' as d ON c.user_id = d.id
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 ' . $otpf->getTable() . ' as otpf ON ( a.over_time_policy_id > 0 AND a.over_time_policy_id = otpf.id AND otpf.deleted = 0 )
LEFT JOIN ' . $apf->getTable() . ' as apf ON ( a.absence_policy_id > 0 AND a.absence_policy_id = apf.id AND apf.deleted = 0 )
LEFT JOIN ' . $ppf->getTable() . ' as ppf ON ( a.premium_policy_id > 0 AND a.premium_policy_id = ppf.id AND ppf.deleted = 0 )
LEFT JOIN ' . $mpf->getTable() . ' as mpf ON ( a.meal_policy_id > 0 AND a.meal_policy_id = mpf.id AND mpf.deleted = 0 )
LEFT JOIN ' . $bpf->getTable() . ' as bpf ON ( a.break_policy_id > 0 AND a.break_policy_id = bpf.id AND bpf.deleted = 0 )
LEFT JOIN ' . $uwf->getTable() . ' as z ON z.id = (select z.id
from ' . $uwf->getTable() . ' as z
where z.user_id = c.user_id
and z.effective_date <= c.date_stamp
and z.wage_group_id = 0
and z.deleted = 0
order by z.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 d.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;
$query .= isset($filter_data['exclude_id']) ? $this->getWhereClauseSQL('d.id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL;
$query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('c.user_id', $filter_data['user_id'], '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_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('d.group_id', $filter_data['user_group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['user_group']) ? $this->getWhereClauseSQL('g.name', $filter_data['user_group'], 'text', $ph) : NULL;
$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['status_id']) ? $this->getWhereClauseSQL('a.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['type_id']) ? $this->getWhereClauseSQL('a.type_id', $filter_data['type_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['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['job_id']) ? $this->getWhereClauseSQL('a.job_id', $filter_data['job_id'], 'numeric_list', $ph) : NULL;
$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;
$query .= isset($filter_data['job_group_id']) ? $this->getWhereClauseSQL('x.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;
$query .= isset($filter_data['include_job_item_id']) ? $this->getWhereClauseSQL('a.job_item_id', $filter_data['include_job_item_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['exclude_job_item_id']) ? $this->getWhereClauseSQL('a.job_item_id', $filter_data['exclude_job_item_id'], 'not_numeric_list', $ph) : NULL;
/*
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 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 d.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 c.user_id in ('. $this->getListSQL($filter_data['user_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['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['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) .') ';
}
if ( isset($filter_data['branch_id']) AND isset($filter_data['branch_id'][0]) AND !in_array(-1, (array)$filter_data['branch_id']) ) {
$query .= ' AND a.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 a.department_id in ('. $this->getListSQL($filter_data['department_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 a.type_id in ('. $this->getListSQL($filter_data['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 c.pay_period_id in ('. $this->getListSQL($filter_data['pay_period_id'], $ph) .') ';
}
//Use the job_id in the punch_control table so we can filter by '0' or No Job
if ( isset($filter_data['include_job_id']) AND isset($filter_data['include_job_id'][0]) AND !in_array(-1, (array)$filter_data['include_job_id']) ) {
$query .= ' AND a.job_id in ('. $this->getListSQL($filter_data['include_job_id'], $ph) .') ';
}
if ( isset($filter_data['exclude_job_id']) AND isset($filter_data['exclude_job_id'][0]) AND !in_array(-1, (array)$filter_data['exclude_job_id']) ) {
$query .= ' AND a.job_id not in ('. $this->getListSQL($filter_data['exclude_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['date']) and trim($filter_data['date']) != '') {
$ph[] = $this->db->BindDate($filter_data['date']);
$query .= ' AND c.date_stamp = ?';
}
if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') {
$ph[] = $this->db->BindDate($filter_data['start_date']);
$query .= ' AND c.date_stamp >= ?';
}
if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') {
$ph[] = $this->db->BindDate($filter_data['end_date']);
$query .= ' AND c.date_stamp <= ?';
}
$query .= '
AND (a.deleted = 0 AND c.deleted = 0 AND d.deleted = 0)
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
$this->ExecuteSQL($query, $ph, $limit, $page);
//Debug::Arr($ph, 'Query: '. $query, __FILE__, __LINE__, __METHOD__,10);
return $this;
}
function getByCompanyId($id, $where = NULL, $order = NULL)
{
if ($id == '') {
return FALSE;
}
if ($order == NULL) {
$order = array('a.type_id' => 'asc', 'a.name' => 'asc');
$strict = FALSE;
} else {
$strict = TRUE;
}
$pgf = new PolicyGroupFactory();
$cgmf = new CompanyGenericMapFactory();
$otpf = new OverTimePolicyFactory();
$ppf = new PremiumPolicyFactory();
$apf = new AbsencePolicyFactory();
$ph = array('id' => $id);
$query = '
select a.*,
(
( select count(*) from ' . $cgmf->getTable() . ' as w, ' . $pgf->getTable() . ' as v where w.company_id = a.company_id AND w.object_type_id = 140 AND w.map_id = a.id AND w.object_id = v.id AND v.deleted = 0)+
( select count(*) from ' . $otpf->getTable() . ' as x where x.accrual_policy_id = a.id and x.deleted = 0)+
( select count(*) from ' . $ppf->getTable() . ' as y where y.accrual_policy_id = a.id and y.deleted = 0)+
( select count(*) from ' . $apf->getTable() . ' as z where z.accrual_policy_id = a.id and z.deleted = 0)
) as assigned_policy_groups
from ' . $this->getTable() . ' as a
where a.company_id = ?
AND a.deleted = 0';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict);
$this->ExecuteSQL($query, $ph);
}
function getReportByStartDateAndEndDateAndUserIdListAndJobListAndJobItemList($start_date, $end_date, $user_ids, $job_ids, $job_item_ids, $order = NULL)
{
if ($user_ids == '') {
Debug::Text('No User Ids: ', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
if ($job_ids == '') {
Debug::Text('No Job Ids: ', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
if ($job_item_ids == '') {
Debug::Text('No Job Item Ids: ', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
if ($start_date == '') {
Debug::Text('No Start Date: ', __FILE__, __LINE__, __METHOD__, 10);
$start_date = 0;
}
if ($end_date == '') {
Debug::Text('No End Date: ', __FILE__, __LINE__, __METHOD__, 10);
$end_date = time();
}
//$order = array( 'b.pay_period_id' => 'asc', 'b.user_id' => 'asc' );
//$order = array( 'z.last_name' => 'asc' );
/*
if ( $order == NULL ) {
$order = array( 'b.pay_period_id' => 'asc', 'b.user_id' => 'asc' );
$strict = FALSE;
} else {
$strict = TRUE;
}
*/
$ulf = new UserListFactory();
$udf = new UserDateFactory();
$uwf = new UserWageFactory();
$otpf = new OverTimePolicyFactory();
$apf = new AbsencePolicyFactory();
$ppf = new PremiumPolicyFactory();
$ph = array('start_date' => $this->db->BindDate($start_date), 'end_date' => $this->db->BindDate($end_date));
$query = '
select b.user_id as user_id,
a.status_id as status_id,
a.type_id as type_id,
a.branch_id as branch_id,
a.department_id as department_id,
a.job_id as job_id,
a.job_item_id as job_item_id,
a.over_time_policy_id as over_time_policy_id,
n.id as over_time_policy_wage_id,
n.effective_date as over_time_policy_wage_effective_date,
a.absence_policy_id as absence_policy_id,
p.id as absence_policy_wage_id,
p.effective_date as absence_policy_wage_effective_date,
a.premium_policy_id as premium_policy_id,
r.id as premium_policy_wage_id,
r.effective_date as premium_policy_wage_effective_date,
z.id as user_wage_id,
z.effective_date as user_wage_effective_date,
sum(total_time) as total_time,
sum(actual_total_time) as actual_total_time,
sum(quantity) as quantity,
sum(bad_quantity) as bad_quantity
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $udf->getTable() . ' as b ON a.user_date_id = b.id
LEFT JOIN ' . $otpf->getTable() . ' as m ON a.over_time_policy_id = m.id
LEFT JOIN ' . $uwf->getTable() . ' as n ON n.id = (select n.id
from ' . $uwf->getTable() . ' as n
where n.user_id = b.user_id
and n.wage_group_id = m.wage_group_id
and n.effective_date <= b.date_stamp
and n.deleted = 0
order by n.effective_date desc limit 1)
LEFT JOIN ' . $apf->getTable() . ' as o ON a.absence_policy_id = o.id
LEFT JOIN ' . $uwf->getTable() . ' as p ON p.id = (select p.id
from ' . $uwf->getTable() . ' as p
where p.user_id = b.user_id
and p.wage_group_id = o.wage_group_id
and p.effective_date <= b.date_stamp
and p.deleted = 0
order by p.effective_date desc limit 1)
LEFT JOIN ' . $ppf->getTable() . ' as q ON a.premium_policy_id = q.id
LEFT JOIN ' . $uwf->getTable() . ' as r ON r.id = (select r.id
from ' . $uwf->getTable() . ' as r
where r.user_id = b.user_id
and r.wage_group_id = q.wage_group_id
and r.effective_date <= b.date_stamp
and r.deleted = 0
order by r.effective_date desc limit 1)
LEFT JOIN ' . $uwf->getTable() . ' as z ON z.id = (select z.id
from ' . $uwf->getTable() . ' as z
where z.user_id = b.user_id
and z.effective_date <= b.date_stamp
and z.deleted = 0
order by z.effective_date desc limit 1)
where a.user_date_id = b.id
AND b.date_stamp >= ?
AND b.date_stamp <= ?
AND b.user_id in (' . $this->getListSQL($user_ids, $ph) . ')
AND a.job_id in (' . $this->getListSQL($job_ids, $ph) . ')
';
//AND a.job_item_id in ('. $this->getListSQL($job_item_ids, $ph) .')
$filter_query = NULL;
if ($job_item_ids != '' and isset($job_item_ids[0]) and !in_array(-1, $job_item_ids)) {
$query .= ' AND a.job_item_id in (' . $this->getListSQL($job_item_ids, $ph) . ') ';
}
$query .= '
AND a.status_id in (10,20,30)
AND ( a.deleted = 0 AND b.deleted = 0)
group by b.user_id,user_wage_id, user_wage_effective_date, over_time_policy_wage_id, over_time_policy_wage_effective_date, absence_policy_wage_id, absence_policy_wage_effective_date, premium_policy_wage_id, premium_policy_wage_effective_date, a.status_id, a.type_id, a.branch_id, a.department_id, a.job_id, a.job_item_id, a.over_time_policy_id, a.absence_policy_id, a.premium_policy_id
';
$query .= $this->getSortSQL($order, FALSE);
$this->rs = $this->db->Execute($query, $ph);
return $this;
}
function createOverTimePolicy($company_id, $type, $accrual_policy_id = NULL)
{
$otpf = new OverTimePolicyFactory();
$otpf->setCompany($company_id);
switch ($type) {
case 100:
$otpf->setName('Daily (>8hrs)');
$otpf->setType(10);
$otpf->setTriggerTime(3600 * 8);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 1'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 110:
$otpf->setName('Daily (>9hrs)');
$otpf->setType(10);
$otpf->setTriggerTime(3600 * 9);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 120:
$otpf->setName('Daily (>10hrs)');
$otpf->setType(10);
$otpf->setTriggerTime(3600 * 10);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 200:
$otpf->setName('Weekly (>47hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 47);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 210:
$otpf->setName('Weekly (>59hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 59);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 220:
$otpf->setName('Weekly (>71hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 71);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 230:
$otpf->setName('Weekly (>31hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 31);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 240:
$otpf->setName('Weekly (>39hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 39);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 250:
$otpf->setName('Weekly (>47hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 47);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 500:
$otpf->setName('Holiday');
$otpf->setType(180);
$otpf->setTriggerTime(0);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
}
if ($otpf->isValid()) {
$insert_id = $otpf->Save();
Debug::Text('Overtime Policy ID: ' . $insert_id, __FILE__, __LINE__, __METHOD__, 10);
return $insert_id;
}
Debug::Text('Failed Creating Overtime Policy!', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
function getByPolicyGroupUserIdOrId($user_id, $id = NULL, $where = NULL, $order = NULL)
{
if ($user_id == '') {
return FALSE;
}
if ($id == '') {
$id = 0;
}
if ($order == NULL) {
$order = array('type_id' => 'asc', 'trigger_time' => 'desc');
$strict = FALSE;
} else {
$strict = TRUE;
}
$pgf = new PolicyGroupFactory();
$pguf = new PolicyGroupUserFactory();
$cgmf = new CompanyGenericMapFactory();
$otpf = new OverTimePolicyFactory();
$ph = array('user_id' => $user_id, 'id' => $id);
$query = '
select d.*
from ' . $pguf->getTable() . ' as a,
' . $pgf->getTable() . ' as b,
' . $cgmf->getTable() . ' as c,
' . $this->getTable() . ' as d
where a.policy_group_id = b.id
AND ( b.id = c.object_id AND b.company_id = c.company_id AND c.object_type_id = 110 )
AND c.map_id = d.id
AND a.user_id = ?
AND ( b.deleted = 0 AND d.deleted = 0 )
UNION
select e.*
from
' . $otpf->getTable() . ' as e
where
e.id = ?
AND e.deleted = 0
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict);
$this->rs = $this->db->Execute($query, $ph);
return $this;
}
function createOverTimePolicy($company_id, $type, $accrual_policy_id = NULL)
{
$otpf = new OverTimePolicyFactory();
$otpf->setCompany($company_id);
switch ($type) {
//
//Changing the OT rates will make a big difference is how these tests are calculated.
//
case 100:
$otpf->setName('Daily (>8hrs)');
$otpf->setType(10);
$otpf->setTriggerTime(3600 * 8);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 1'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 110:
$otpf->setName('Daily (>9hrs)');
$otpf->setType(10);
$otpf->setTriggerTime(3600 * 9);
$otpf->setRate('2.0');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 120:
$otpf->setName('Daily (>10hrs)');
$otpf->setType(10);
$otpf->setTriggerTime(3600 * 10);
$otpf->setRate('2.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 200:
$otpf->setName('Weekly (>47hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 47);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 210:
$otpf->setName('Weekly (>59hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 59);
$otpf->setRate('2.0');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 220:
$otpf->setName('Weekly (>71hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 71);
$otpf->setRate('2.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 230:
$otpf->setName('Weekly (>31hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 31);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 240:
$otpf->setName('Weekly (>39hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 39);
$otpf->setRate('2.0');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 250:
$otpf->setName('Weekly (>47hrs)');
$otpf->setType(20);
$otpf->setTriggerTime(3600 * 47);
$otpf->setRate('2.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 300:
$otpf->setName('BiWeekly (>80hrs)');
$otpf->setType(30);
$otpf->setTriggerTime(3600 * 80);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 310:
$otpf->setName('BiWeekly (>84hrs)');
$otpf->setType(30);
$otpf->setTriggerTime(3600 * 84);
$otpf->setRate('2.0');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 320:
$otpf->setName('BiWeekly (>86hrs)');
$otpf->setType(30);
$otpf->setTriggerTime(3600 * 86);
$otpf->setRate('2.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 500:
$otpf->setName('Holiday');
$otpf->setType(180);
$otpf->setTriggerTime(0);
$otpf->setRate('1.5');
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
case 510:
$otpf->setName('Holiday');
$otpf->setType(180);
$otpf->setTriggerTime(0);
$otpf->setRate('4.0');
//This should have the highest rate as it always takes precedance.
$otpf->setPayStubEntryAccountId(CompanyDeductionFactory::getPayStubEntryAccountByCompanyIDAndTypeAndFuzzyName($company_id, 10, 'Over Time 2'));
$otpf->setAccrualPolicyId((int) $accrual_policy_id);
$otpf->setAccrualRate(1.0);
break;
}
if ($otpf->isValid()) {
$insert_id = $otpf->Save();
Debug::Text('Overtime Policy ID: ' . $insert_id, __FILE__, __LINE__, __METHOD__, 10);
return $insert_id;
}
Debug::Text('Failed Creating Overtime Policy!', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
