static function getOptions($name, $interval = 1)
{
$all_array_option = array('*' => TTi18n::getText('-- All --'));
$retval = FALSE;
switch ($name) {
case 'minute':
for ($i = 0; $i <= 59; $i += $interval) {
$retval[$i] = $i;
}
$retval = Misc::prependArray($all_array_option, $retval);
break;
case 'hour':
for ($i = 0; $i <= 23; $i += $interval) {
$retval[$i] = $i;
}
$retval = Misc::prependArray($all_array_option, $retval);
break;
case 'day_of_month':
$retval = Misc::prependArray($all_array_option, TTDate::getDayOfMonthArray());
break;
case 'month':
$retval = Misc::prependArray($all_array_option, TTDate::getMonthOfYearArray());
break;
case 'day_of_week':
$retval = Misc::prependArray($all_array_option, TTDate::getDayOfWeekArray());
break;
}
return $retval;
}
function _getFactoryOptions($name, $parent = NULL)
{
$retval = NULL;
switch ($name) {
case 'columns':
$retval = array('-1100-type' => TTi18n::gettext('Type'), '-1110-status' => TTi18n::gettext('Status'), '-1210-time_stamp' => TTi18n::gettext('Date/Time'), '-1220-date' => TTi18n::gettext('Date'), '-1230-time' => TTi18n::gettext('Time'), '-1239-in_type' => TTi18n::gettext('In Type'), '-1240-in_time_stamp' => TTi18n::gettext('In Date/Time'), '-1250-in_punch_date' => TTi18n::gettext('In Date'), '-1260-in_punch_time' => TTi18n::gettext('In Time'), '-1269-out_type' => TTi18n::gettext('Out Type'), '-1270-out_time_stamp' => TTi18n::gettext('Out Date/Time'), '-1280-out_punch_date' => TTi18n::gettext('Out Date'), '-1290-out_punch_time' => TTi18n::gettext('Out Time'), '-1310-branch' => TTi18n::gettext('Branch'), '-1320-department' => TTi18n::gettext('Department'), '-1410-station_id' => TTi18n::gettext('Station ID'), '-1420-longitude' => TTi18n::gettext('Longitude'), '-1430-latitude' => TTi18n::gettext('Latitude'), '-1500-note' => TTi18n::gettext('Note'));
//Since getOptions() can be called without first setting a company, we don't always know the product edition for the currently
//logged in employee.
if (is_object($this->getCompanyObject()) and $this->getCompanyObject()->getProductEdition() >= TT_PRODUCT_CORPORATE or !is_object($this->getCompanyObject()) and getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$retval += array('-1330-job' => TTi18n::gettext('Job'), '-1340-job_item' => TTi18n::gettext('Task'), '-1350-quantity' => TTi18n::gettext('Quantity'), '-1360-bad_quantity' => TTi18n::gettext('Bad Quantity'));
}
$retval = Misc::addSortPrefix(Misc::prependArray($this->getUserIdentificationColumns(), Misc::trimSortPrefix($retval)));
ksort($retval);
break;
case 'column_aliases':
//Used for converting column names after they have been parsed.
$retval = array('type' => 'type_id', 'status' => 'status_id', 'branch' => 'branch_id', 'department' => 'department_id', 'job' => 'job_id', 'job_item' => 'job_item_id');
break;
case 'import_options':
$retval = array('-1010-fuzzy_match' => TTi18n::getText('Enable smart matching.'), '-1020-disable_rounding' => TTi18n::getText('Disable rounding.'));
break;
case 'parse_hint':
$upf = TTnew('UserPreferenceFactory');
$retval = array('branch' => array('-1010-name' => TTi18n::gettext('Name'), '-1010-manual_id' => TTi18n::gettext('Code')), 'department' => array('-1010-name' => TTi18n::gettext('Name'), '-1010-manual_id' => TTi18n::gettext('Code')), 'job' => array('-1010-name' => TTi18n::gettext('Name'), '-1010-manual_id' => TTi18n::gettext('Code')), 'job_item' => array('-1010-name' => TTi18n::gettext('Name'), '-1010-manual_id' => TTi18n::gettext('Code')), 'time_stamp' => $upf->getOptions('date_time_format'), 'in_time_stamp' => $upf->getOptions('date_time_format'), 'out_time_stamp' => $upf->getOptions('date_time_format'), 'date' => $upf->getOptions('date_format'), 'in_punch_date' => $upf->getOptions('date_format'), 'out_punch_date' => $upf->getOptions('date_format'), 'time' => $upf->getOptions('time_format'), 'in_punch_time' => $upf->getOptions('time_format'), 'out_punch_time' => $upf->getOptions('time_format'));
break;
}
return $retval;
}
function _getFactoryOptions($name, $parent = NULL)
{
$retval = NULL;
switch ($name) {
case 'columns':
$uwf = TTNew('UserWageFactory');
$retval = Misc::prependArray($this->getUserIdentificationColumns(), Misc::arrayIntersectByKey(array('wage_group', 'type', 'wage', 'effective_date', 'hourly_rate', 'labor_burden_percent', 'weekly_time', 'note'), Misc::trimSortPrefix($uwf->getOptions('columns'))));
break;
case 'column_aliases':
//Used for converting column names after they have been parsed.
$retval = array('type' => 'type_id', 'wage_group' => 'wage_group_id');
break;
case 'import_options':
$retval = array('-1010-fuzzy_match' => TTi18n::getText('Enable smart matching.'));
break;
case 'parse_hint':
case 'parse_hint':
$upf = TTnew('UserPreferenceFactory');
$retval = array('effective_date' => $upf->getOptions('date_format'), 'weekly_time' => $upf->getOptions('time_unit_format'));
break;
}
return $retval;
}
function _getFactoryOptions($name, $parent = NULL)
{
$retval = NULL;
switch ($name) {
case 'columns':
$baf = TTNew('BankAccountFactory');
$retval = Misc::prependArray($this->getUserIdentificationColumns(), Misc::arrayIntersectByKey(array('transit', 'institution', 'account'), Misc::trimSortPrefix($baf->getOptions('columns'))));
break;
case 'column_aliases':
//Used for converting column names after they have been parsed.
$retval = array();
break;
case 'import_options':
$retval = array('-1010-fuzzy_match' => TTi18n::getText('Enable smart matching.'));
break;
case 'parse_hint':
case 'parse_hint':
//$upf = TTnew('UserPreferenceFactory');
$retval = array();
break;
}
return $retval;
}
function _getFactoryOptions($name, $parent = NULL)
{
$retval = NULL;
switch ($name) {
case 'columns':
$psaf = TTNew('PayStubAmendmentFactory');
$retval = Misc::prependArray($this->getUserIdentificationColumns(), Misc::arrayIntersectByKey(array('status', 'type', 'pay_stub_entry_name', 'effective_date', 'amount', 'rate', 'units', 'description', 'ytd_adjustment'), Misc::trimSortPrefix($psaf->getOptions('columns'))));
break;
case 'column_aliases':
//Used for converting column names after they have been parsed.
$retval = array('status' => 'status_id', 'type' => 'type_id', 'pay_stub_entry_name' => 'pay_stub_entry_name_id');
break;
case 'import_options':
$retval = array('-1010-fuzzy_match' => TTi18n::getText('Enable smart matching.'));
break;
case 'parse_hint':
case 'parse_hint':
$upf = TTnew('UserPreferenceFactory');
$retval = array('effective_date' => $upf->getOptions('date_format'));
break;
}
return $retval;
}
function _getFactoryOptions($name, $parent = NULL)
{
$retval = NULL;
switch ($name) {
case 'columns':
$apf = TTNew('AccrualFactory');
$retval = Misc::prependArray($this->getUserIdentificationColumns(), Misc::arrayIntersectByKey(array('accrual_policy', 'type', 'amount', 'date_stamp'), Misc::trimSortPrefix($apf->getOptions('columns'))));
break;
case 'column_aliases':
//Used for converting column names after they have been parsed.
$retval = array('type' => 'type_id', 'accrual_policy' => 'accrual_policy_id');
break;
case 'import_options':
$retval = array('-1010-fuzzy_match' => TTi18n::getText('Enable smart matching.'));
break;
case 'parse_hint':
case 'parse_hint':
$upf = TTnew('UserPreferenceFactory');
$retval = array('date_stamp' => $upf->getOptions('date_format'), 'amount' => $upf->getOptions('time_unit_format'));
break;
}
return $retval;
}
$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') );
//Get primary/secondary order list
$filter_data['sort_options'] = $columns;
$filter_data['sort_options']['effective_date_order'] = 'Wage Effective Date';
unset($filter_data['sort_options']['effective_date']);
$filter_data['sort_direction_options'] = Misc::getSortDirectionArray();
$saved_report_options = $ugdlf->getByUserIdAndScriptArray($current_user->getId(), $_SERVER['SCRIPT_NAME']);
$generic_data['saved_report_options'] = $saved_report_options;
$smarty->assign_by_ref('generic_data', $generic_data);
/**
* 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;
}
}
$prepend_array_option = array(0 => '--', -1 => TTi18n::gettext('-- Default --'));
//Select box options;
$aplf = TTnew('AbsencePolicyListFactory');
$absence_policy_options = $aplf->getByCompanyIdArray($current_company->getId());
$splf = TTnew('SchedulePolicyListFactory');
$data['schedule_options'] = $splf->getByCompanyIdArray($current_company->getId());
$blf = TTnew('BranchListFactory');
$blf->getByCompanyId($current_company->getId());
$data['branch_options'] = Misc::prependArray($prepend_array_option, $blf->getArrayByListFactory($blf, FALSE, TRUE));
//$data['branch_options'] = Misc::prependArray( array( -1 => '-- Default --' ), $blf->getByCompanyIdArray( $current_company->getId(), FALSE, FALSE ) );
$dlf = TTnew('DepartmentListFactory');
$dlf->getByCompanyId($current_company->getId());
$data['department_options'] = Misc::prependArray($prepend_array_option, $dlf->getArrayByListFactory($dlf, FALSE, TRUE));
//$data['department_options'] = $dlf->getByCompanyIdArray( $current_company->getId() );
if ($current_company->getProductEdition() >= 20) {
$jlf = TTnew('JobListFactory');
$data['job_options'] = $jlf->getByCompanyIdAndStatusArray($current_company->getId(), array(10, 20, 30, 40), TRUE);
$jilf = TTnew('JobItemListFactory');
$data['job_item_options'] = $jilf->getByCompanyIdArray($current_company->getId(), TRUE);
}
$data['status_options'] = $rstlf->getOptions('status');
$data['absence_policy_options'] = Misc::prependArray(array('- ' . TTi18n::getText('Select Policy') . ' -'), $absence_policy_options);
//var_dump($week_rows);
$smarty->assign_by_ref('data', $data);
$smarty->assign_by_ref('week_rows', $week_rows);
break;
}
$smarty->assign_by_ref('rstcf', $rstcf);
$smarty->assign_by_ref('rstf', $rstf);
$smarty->display('schedule/EditRecurringScheduleTemplate.tpl');
$slf = TTnew('StationListFactory');
$sulf = TTnew('StationUserListFactory');
$slf->GetByIdAndCompanyId($id, $current_company->getId());
foreach ($slf as $station) {
//Debug::Arr($station,'Department', __FILE__, __LINE__, __METHOD__,10);
if (isset($station_data['user_ids'])) {
Debug::Text('Using Selected Users', __FILE__, __LINE__, __METHOD__, 10);
//Use selected values
$user_ids = $station_data['user_ids'];
} else {
Debug::Text('Grabbing Users from DB', __FILE__, __LINE__, __METHOD__, 10);
$sulf->getByStationId($station->getId());
$user_ids = array();
foreach ($sulf as $station_user) {
$user_ids[] = $station_user->getUser();
}
}
$station_data = array('id' => $station->getId(), 'status' => TTi18n::gettext($station->getStatus()), 'type' => $station->getType(), 'station' => $station->getStation(), 'source' => $station->getSource(), 'description' => $station->getDescription(), 'user_ids' => $user_ids, 'created_date' => $station->getCreatedDate(), 'created_by' => $station->getCreatedBy(), 'updated_date' => $station->getUpdatedDate(), 'updated_by' => $station->getUpdatedBy(), 'deleted_date' => $station->getDeletedDate(), 'deleted_by' => $station->getDeletedBy());
}
}
//Select box options;
$station_data['status_options'] = $sf->getOptions('status');
$station_data['type_options'] = $sf->getOptions('type');
$user_options = UserListFactory::getByCompanyIdArray($current_company->getId(), FALSE);
$user_options = Misc::prependArray(array(-1 => TTi18n::gettext('-- ALL --')), $user_options);
$station_data['user_options'] = $user_options;
$smarty->assign_by_ref('station_data', $station_data);
break;
}
$smarty->assign_by_ref('sf', $sf);
$smarty->display('station/EditStationUser.tpl');
$ulf = TTnew('UserListFactory');
$ulf->getSearchByCompanyIdAndArrayCriteria($current_company->getId(), $filter_data);
$src_user_options = UserListFactory::getArrayByListFactory($ulf, FALSE, FALSE);
$user_options = Misc::arrayDiffByKey((array) $filter_user_id, $src_user_options);
$filter_user_options = Misc::arrayIntersectByKey((array) $filter_user_id, $src_user_options);
$prepend_array_option = array(0 => '--', -1 => TTi18n::gettext('-- Default --'));
$splf = TTnew('SchedulePolicyListFactory');
$schedule_policy_options = $splf->getByCompanyIdArray($current_company->getId());
$aplf = TTnew('AbsencePolicyListFactory');
$absence_policy_options = $aplf->getByCompanyIdArray($current_company->getId());
$blf = TTnew('BranchListFactory');
$blf->getByCompanyId($current_company->getId());
$branch_options = Misc::prependArray($prepend_array_option, $blf->getArrayByListFactory($blf, FALSE, TRUE));
$dlf = TTnew('DepartmentListFactory');
$dlf->getByCompanyId($current_company->getId());
$department_options = Misc::prependArray($prepend_array_option, $dlf->getArrayByListFactory($dlf, FALSE, TRUE));
if ($current_company->getProductEdition() >= 20) {
$jlf = TTnew('JobListFactory');
$jlf->getByStatusIdAndCompanyId(array(10, 20, 30, 40), $current_company->getId());
//$jlf->getByCompanyIdAndUserIdAndStatus( $current_company->getId(), $data['user_id'], array(10,20,30,40) );
$data['job_options'] = $jlf->getArrayByListFactory($jlf, TRUE, TRUE);
$data['job_manual_id_options'] = $jlf->getManualIDArrayByListFactory($jlf, TRUE);
$jilf = TTnew('JobItemListFactory');
$jilf->getByCompanyId($current_company->getId());
$data['job_item_options'] = $jilf->getArrayByListFactory($jilf, TRUE);
$data['job_item_manual_id_options'] = $jilf->getManualIdArrayByListFactory($jilf, TRUE);
}
//Select box options;
$smarty->assign_by_ref('user_options', $user_options);
$smarty->assign_by_ref('filter_user_options', $filter_user_options);
$data['status_options'] = $sf->getOptions('status');
$cdlf = TTnew('CompanyDeductionListFactory');
$cdlf->getByCompanyIdAndId($current_company->getId(), $id);
foreach ($cdlf as $cd_obj) {
//Debug::Arr($station,'Department', __FILE__, __LINE__, __METHOD__,10);
$data = array('id' => $cd_obj->getId(), 'company_id' => $cd_obj->getCompany(), 'status_id' => $cd_obj->getStatus(), 'type_id' => $cd_obj->getType(), 'name' => $cd_obj->getName(), 'start_date' => $cd_obj->getStartDate(), 'end_date' => $cd_obj->getEndDate(), 'minimum_length_of_service' => $cd_obj->getMinimumLengthOfService(), 'minimum_length_of_service_unit_id' => $cd_obj->getMinimumLengthOfServiceUnit(), 'maximum_length_of_service' => $cd_obj->getMaximumLengthOfService(), 'maximum_length_of_service_unit_id' => $cd_obj->getMaximumLengthOfServiceUnit(), 'minimum_user_age' => $cd_obj->getMinimumUserAge(), 'maximum_user_age' => $cd_obj->getMaximumUserAge(), 'calculation_id' => $cd_obj->getCalculation(), 'calculation_order' => $cd_obj->getCalculationOrder(), 'country' => $cd_obj->getCountry(), 'province' => $cd_obj->getProvince(), 'district' => $cd_obj->getDistrict(), 'company_value1' => $cd_obj->getCompanyValue1(), 'company_value2' => $cd_obj->getCompanyValue2(), 'user_value1' => $cd_obj->getUserValue1(), 'user_value2' => $cd_obj->getUserValue2(), 'user_value3' => $cd_obj->getUserValue3(), 'user_value4' => $cd_obj->getUserValue4(), 'user_value5' => $cd_obj->getUserValue5(), 'user_value6' => $cd_obj->getUserValue6(), 'user_value7' => $cd_obj->getUserValue7(), 'user_value8' => $cd_obj->getUserValue8(), 'user_value9' => $cd_obj->getUserValue9(), 'user_value10' => $cd_obj->getUserValue10(), 'lock_user_value1' => $cd_obj->getLockUserValue1(), 'lock_user_value2' => $cd_obj->getLockUserValue2(), 'lock_user_value3' => $cd_obj->getLockUserValue3(), 'lock_user_value4' => $cd_obj->getLockUserValue4(), 'lock_user_value5' => $cd_obj->getLockUserValue5(), 'lock_user_value6' => $cd_obj->getLockUserValue6(), 'lock_user_value7' => $cd_obj->getLockUserValue7(), 'lock_user_value8' => $cd_obj->getLockUserValue8(), 'lock_user_value9' => $cd_obj->getLockUserValue9(), 'lock_user_value10' => $cd_obj->getLockUserValue10(), 'pay_stub_entry_account_id' => $cd_obj->getPayStubEntryAccount(), 'include_pay_stub_entry_account_ids' => $cd_obj->getIncludePayStubEntryAccount(), 'exclude_pay_stub_entry_account_ids' => $cd_obj->getExcludePayStubEntryAccount(), 'include_account_amount_type_id' => $cd_obj->getIncludeAccountAmountType(), 'exclude_account_amount_type_id' => $cd_obj->getExcludeAccountAmountType(), 'user_ids' => $cd_obj->getUser(), 'created_date' => $cd_obj->getCreatedDate(), 'created_by' => $cd_obj->getCreatedBy(), 'updated_date' => $cd_obj->getUpdatedDate(), 'updated_by' => $cd_obj->getUpdatedBy(), 'deleted_date' => $cd_obj->getDeletedDate(), 'deleted_by' => $cd_obj->getDeletedBy());
}
} elseif ($action != 'submit') {
$data = array('country' => 0, 'province' => 0, 'district' => 0, 'user_value1' => 0, 'user_value2' => 0, 'user_value3' => 0, 'user_value4' => 0, 'user_value5' => 0, 'user_value6' => 0, 'user_value7' => 0, 'user_value8' => 0, 'user_value9' => 0, 'user_value10' => 0, 'minimum_length_of_service' => 0, 'maximum_length_of_service' => 0, 'minimum_user_age' => 0, 'maximum_user_age' => 0, 'calculation_order' => 100);
}
//Select box options;
$data['status_options'] = $cdf->getOptions('status');
$data['type_options'] = $cdf->getOptions('type');
$data['length_of_service_unit_options'] = $cdf->getOptions('length_of_service_unit');
$data['account_amount_type_options'] = $cdf->getOptions('account_amount_type');
$cf = TTnew('CompanyFactory');
$data['country_options'] = Misc::prependArray(array(0 => '--'), $cf->getOptions('country'));
if (isset($data['country'])) {
$data['province_options'] = $cf->getOptions('province', $data['country']);
}
if (isset($data['district'])) {
$district_options = $cf->getOptions('district', $data['country']);
if (isset($district_options[$data['province']])) {
$data['district_options'] = $district_options[$data['province']];
}
}
$data['us_medicare_filing_status_options'] = $cdf->getOptions('us_medicare_filing_status');
$data['us_eic_filing_status_options'] = $cdf->getOptions('us_eic_filing_status');
$data['federal_filing_status_options'] = $cdf->getOptions('federal_filing_status');
$data['state_filing_status_options'] = $cdf->getOptions('state_filing_status');
$data['state_ga_filing_status_options'] = $cdf->getOptions('state_ga_filing_status');
$data['state_nj_filing_status_options'] = $cdf->getOptions('state_nj_filing_status');
//Get branches
$blf = new BranchListFactory();
$blf->getByCompanyId($current_company->getId());
$branch_options = Misc::prependArray($all_array_option, $blf->getArrayByListFactory($blf, FALSE, TRUE));
$filter_data['src_branch_options'] = Misc::arrayDiffByKey((array) $filter_data['branch_ids'], $branch_options);
$filter_data['selected_branch_options'] = Misc::arrayIntersectByKey((array) $filter_data['branch_ids'], $branch_options);
//Get departments
$dlf = new DepartmentListFactory();
$dlf->getByCompanyId($current_company->getId());
$department_options = Misc::prependArray($all_array_option, $dlf->getArrayByListFactory($dlf, FALSE, TRUE));
$filter_data['src_department_options'] = Misc::arrayDiffByKey((array) $filter_data['department_ids'], $department_options);
$filter_data['selected_department_options'] = Misc::arrayIntersectByKey((array) $filter_data['department_ids'], $department_options);
//Get employee titles
$utlf = new UserTitleListFactory();
$utlf->getByCompanyId($current_company->getId());
$user_title_options = Misc::prependArray($all_array_option, $utlf->getArrayByListFactory($utlf, FALSE, TRUE));
$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);
//Deduction PSEA accounts
$psealf = new PayStubEntryAccountListFactory();
$filter_data['deduction_pay_stub_entry_account_options'] = $psealf->getByCompanyIdAndStatusIdAndTypeIdArray($current_company->getId(), 10, array(20, 30, 40), TRUE);
$filter_data['earning_pay_stub_entry_account_options'] = $psealf->getByCompanyIdAndStatusIdAndTypeIdArray($current_company->getId(), 10, array(10, 40), TRUE);
$filter_data['income_pay_stub_entry_account_options'] = $psealf->getByCompanyIdAndStatusIdAndTypeIdArray($current_company->getId(), 10, array(10, 30, 40), TRUE);
//Get employee list
//$filter_data['user_options'] = UserListFactory::getByCompanyIdArray( $current_company->getId(), FALSE );
//Quarters
$filter_data['year_options'] = $year_options;
$setup_data['state_options'] = $state_options;
$saved_report_options = $ugdlf->getByUserIdAndScriptArray($current_user->getId(), $_SERVER['SCRIPT_NAME']);
$generic_data['saved_report_options'] = $saved_report_options;
$smarty->assign_by_ref('generic_data', $generic_data);
$user_obj = $ulf->getById($p_obj->getColumn('user_id'))->getCurrent();
$rows[] = array('id' => $p_obj->getColumn('punch_id'), 'punch_control_id' => $p_obj->getPunchControlId(), 'user_id' => $p_obj->getColumn('user_id'), 'first_name' => $user_obj->getFirstName(), 'last_name' => $user_obj->getLastName(), 'title' => Option::getByKey($user_obj->getTitle(), $title_options), 'group' => Option::getByKey($user_obj->getGroup(), $group_options), 'default_branch' => Option::getByKey($user_obj->getDefaultBranch(), $branch_options), 'default_department' => Option::getByKey($user_obj->getDefaultDepartment(), $department_options), 'branch_id' => $p_obj->getColumn('branch_id'), 'branch' => Option::getByKey($p_obj->getColumn('branch_id'), $branch_options), 'department_id' => $p_obj->getColumn('department_id'), 'department' => Option::getByKey($p_obj->getColumn('department_id'), $department_options), 'status_id' => Option::getByKey($p_obj->getStatus(), $punch_status_options), 'type_id' => Option::getByKey($p_obj->getType(), $punch_type_options), 'date_stamp' => TTDate::getDate('DATE', TTDate::strtotime($p_obj->getColumn('date_stamp'))), 'job_id' => $p_obj->getColumn('job_id'), 'job_name' => $p_obj->getColumn('job_name'), 'job_group_id' => $p_obj->getColumn('job_group_id'), 'job_item_id' => $p_obj->getColumn('job_item_id'), 'time_stamp' => TTDate::getDate('DATE+TIME', $p_obj->getTimeStamp()), 'is_owner' => $permission->isOwner($p_obj->getCreatedBy(), $current_user->getId()), 'is_child' => $permission->isChild($p_obj->getColumn('user_id'), $permission_children_ids));
}
$smarty->assign_by_ref('rows', $rows);
$all_array_option = array('-1' => TTi18n::gettext('-- Any --'));
$ulf->getSearchByCompanyIdAndArrayCriteria($current_company->getId(), $filter_data);
$filter_data['user_options'] = Misc::prependArray($all_array_option, UserListFactory::getArrayByListFactory($ulf, FALSE, TRUE));
//Select box options;
$filter_data['branch_options'] = Misc::prependArray($all_array_option, $branch_options);
$filter_data['department_options'] = Misc::prependArray($all_array_option, $department_options);
$filter_data['title_options'] = Misc::prependArray($all_array_option, $title_options);
$filter_data['group_options'] = Misc::prependArray($all_array_option, $group_options);
$filter_data['status_options'] = Misc::prependArray($all_array_option, $ulf->getOptions('status'));
$filter_data['pay_period_options'] = Misc::prependArray($all_array_option, $pay_period_options);
$filter_data['punch_status_options'] = Misc::prependArray($all_array_option, $punch_status_options);
$filter_data['punch_type_options'] = Misc::prependArray($all_array_option, $punch_type_options);
$filter_data['saved_search_options'] = $ugdlf->getArrayByListFactory($ugdlf->getByUserIdAndScript($current_user->getId(), $_SERVER['SCRIPT_NAME']), FALSE);
//Get column list
$filter_data['src_column_options'] = Misc::arrayDiffByKey((array) $filter_data['columns'], $columns);
$filter_data['selected_column_options'] = Misc::arrayIntersectByKey((array) $filter_data['columns'], $columns);
$filter_data['sort_options'] = Misc::trimSortPrefix($columns);
$filter_data['sort_direction_options'] = Misc::getSortDirectionArray(TRUE);
foreach ($filter_data['columns'] as $column_key) {
$filter_columns[Misc::trimSortPrefix($column_key)] = $columns[$column_key];
}
unset($column_key);
$smarty->assign_by_ref('sort_column', $sort_column);
$smarty->assign_by_ref('sort_order', $sort_order);
$smarty->assign_by_ref('filter_data', $filter_data);
$smarty->assign_by_ref('columns', $filter_columns);
$smarty->assign('total_columns', count($filter_columns) + 3);
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();
$sort_column_aliases = array();
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('date_stamp' => 'desc', 'name' => 'asc');
$strict = FALSE;
} else {
if (!isset($order['date_stamp'])) {
$order = Misc::prependArray(array('date_stamp' => 'desc'), $order);
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$pgf = new PolicyGroupFactory();
$pguf = new PolicyGroupUserFactory();
$hpf = new HolidayPolicyFactory();
$cgmf = new CompanyGenericMapFactory();
$ph = array('company_id' => $company_id);
$query = '
select distinct a.*,
y.first_name as created_by_first_name,
y.middle_name as created_by_middle_name,
y.last_name as created_by_last_name,
z.first_name as updated_by_first_name,
z.middle_name as updated_by_middle_name,
z.last_name as updated_by_last_name
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $hpf->getTable() . ' as hpf ON ( a.holiday_policy_id = hpf.id AND hpf.deleted = 0 )
LEFT JOIN ' . $cgmf->getTable() . ' as cgmf ON ( cgmf.company_id = hpf.company_id AND cgmf.object_type_id = 180 AND cgmf.map_id = a.holiday_policy_id )
LEFT JOIN ' . $pgf->getTable() . ' as pgf ON ( pgf.id = cgmf.object_id AND pgf.deleted = 0 )
LEFT JOIN ' . $pguf->getTable() . ' as pguf ON ( pguf.policy_group_id = pgf.id AND pgf.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 hpf.company_id = ?
';
if (isset($filter_data['permission_children_ids']) and isset($filter_data['permission_children_ids'][0]) and !in_array(-1, (array) $filter_data['permission_children_ids'])) {
$query .= ' AND a.created_by in (' . $this->getListSQL($filter_data['permission_children_ids'], $ph) . ') ';
}
if (isset($filter_data['id']) and isset($filter_data['id'][0]) and !in_array(-1, (array) $filter_data['id'])) {
$query .= ' AND a.id in (' . $this->getListSQL($filter_data['id'], $ph) . ') ';
}
if (isset($filter_data['exclude_id']) and isset($filter_data['exclude_id'][0]) and !in_array(-1, (array) $filter_data['exclude_id'])) {
$query .= ' AND a.id not in (' . $this->getListSQL($filter_data['exclude_id'], $ph) . ') ';
}
if (isset($filter_data['holiday_policy_id']) and isset($filter_data['holiday_policy_id'][0]) and !in_array(-1, (array) $filter_data['holiday_policy_id'])) {
$query .= ' AND a.holiday_policy_id in (' . $this->getListSQL($filter_data['holiday_policy_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 pguf.user_id in (' . $this->getListSQL($filter_data['user_id'], $ph) . ') ';
}
if (isset($filter_data['name']) and trim($filter_data['name']) != '') {
$ph[] = strtolower(trim($filter_data['name']));
$query .= ' AND lower(a.name) LIKE ?';
}
if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') {
$ph[] = $this->db->BindDate($filter_data['start_date']);
$query .= ' AND a.date_stamp >= ?';
}
if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') {
$ph[] = $this->db->BindDate($filter_data['end_date']);
$query .= ' AND a.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;
$query .= '
AND a.deleted = 0
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
$this->ExecuteSQL($query, $ph, $limit, $page);
return $this;
}
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
if ($company_id == '') {
return FALSE;
}
if (!is_array($order)) {
//Use Filter Data ordering if its set.
if (isset($filter_data['sort_column']) and $filter_data['sort_order']) {
$order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']);
}
}
$additional_order_fields = array('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;
}
}
$pay_stub_amendments[] = array('id' => $psa_obj->getId(), 'user_id' => $psa_obj->getUser(), 'first_name' => $user_obj->getFirstName(), 'middle_name' => $user_obj->getMiddleName(), 'last_name' => $user_obj->getLastName(), 'status_id' => $psa_obj->getStatus(), 'status' => Option::getByKey($psa_obj->getStatus(), $psa_obj->getOptions('status')), 'type_id' => $psa_obj->getType(), 'type' => Option::getByKey($psa_obj->getType(), $psa_obj->getOptions('type')), 'effective_date' => TTDate::getDate('DATE', $psa_obj->getEffectiveDate()), 'pay_stub_account_name' => Option::getByKey($psa_obj->getPayStubEntryNameId(), $pay_stub_entry_name_options), 'amount' => $amount, 'rate' => $psa_obj->getRate(), 'units' => $psa_obj->getUnits(), 'description' => $psa_obj->getDescription(), 'authorized' => $psa_obj->getAuthorized(), 'ytd_adjustment' => Misc::HumanBoolean($psa_obj->getYTDAdjustment()), 'deleted' => $psa_obj->getDeleted());
}
$export_type_options = Misc::trimSortPrefix($psalf->getOptions('export_eft'));
$all_array_option = array('-1' => TTi18n::gettext('-- Any --'));
$ulf->getSearchByCompanyIdAndArrayCriteria($current_company->getId(), $filter_data);
$filter_data['user_options'] = Misc::prependArray($all_array_option, UserListFactory::getArrayByListFactory($ulf, FALSE, TRUE));
//Select box options;
$filter_data['branch_options'] = Misc::prependArray($all_array_option, $branch_options);
$filter_data['department_options'] = Misc::prependArray($all_array_option, $department_options);
$filter_data['title_options'] = Misc::prependArray($all_array_option, $title_options);
$filter_data['group_options'] = Misc::prependArray($all_array_option, $group_options);
$filter_data['status_options'] = Misc::prependArray($all_array_option, $ulf->getOptions('status'));
$filter_data['pay_period_options'] = Misc::prependArray($all_array_option, $pay_period_options);
$filter_data['recurring_ps_amendment_options'] = Misc::prependArray($all_array_option, $recurring_ps_amendment_options);
$filter_data['pay_stub_entry_name_options'] = Misc::prependArray($all_array_option, $pay_stub_entry_name_options);
$filter_data['saved_search_options'] = $ugdlf->getArrayByListFactory($ugdlf->getByUserIdAndScript($current_user->getId(), $_SERVER['SCRIPT_NAME']), FALSE);
//Get column list
$filter_data['src_column_options'] = Misc::arrayDiffByKey((array) $filter_data['columns'], $columns);
$filter_data['selected_column_options'] = Misc::arrayIntersectByKey((array) $filter_data['columns'], $columns);
$filter_data['sort_options'] = Misc::trimSortPrefix($columns);
$filter_data['sort_direction_options'] = Misc::getSortDirectionArray(TRUE);
foreach ($filter_data['columns'] as $column_key) {
$filter_columns[Misc::trimSortPrefix($column_key)] = $columns[$column_key];
}
unset($column_key);
$smarty->assign_by_ref('pay_stub_amendments', $pay_stub_amendments);
$smarty->assign_by_ref('filter_data', $filter_data);
$smarty->assign_by_ref('export_type_options', $export_type_options);
$smarty->assign_by_ref('columns', $filter_columns);
$smarty->assign('total_columns', count($filter_columns) + 3);
$user_data['title_options'] = $user_titles;
//Get Permission Groups
$pclf = TTnew('PermissionControlListFactory');
$pclf->getByCompanyId($current_company->getId());
$user_data['permission_control_options'] = $pclf->getArrayByListFactory($pclf, FALSE);
//Get pay period schedules
$ppslf = TTnew('PayPeriodScheduleListFactory');
$pay_period_schedules = $ppslf->getByCompanyIDArray($current_company->getId());
$user_data['pay_period_schedule_options'] = $pay_period_schedules;
$pglf = TTnew('PolicyGroupListFactory');
$policy_groups = $pglf->getByCompanyIDArray($current_company->getId());
$user_data['policy_group_options'] = $policy_groups;
$user_data['company'] = $current_company->getName();
$user_data['language_options'] = TTi18n::getLanguageArray();
$user_data['date_format_options'] = $upf->getOptions('date_format');
$user_data['other_date_format_options'] = $upf->getOptions('other_date_format');
$user_data['time_format_options'] = $upf->getOptions('time_format');
$user_data['time_unit_format_options'] = $upf->getOptions('time_unit_format');
$user_data['timesheet_view_options'] = $upf->getOptions('timesheet_view');
$user_data['start_week_day_options'] = $upf->getOptions('start_week_day');
$timezone_options = Misc::prependArray(array(-1 => '---'), $upf->getOptions('time_zone'));
$user_data['time_zone_options'] = $timezone_options;
//Get all Company Deductions for drop down box.
$cdlf = TTnew('CompanyDeductionListFactory');
$user_data['company_deduction_options'] = $cdlf->getByCompanyIdAndStatusIdArray($current_company->getId(), 10, FALSE);
$smarty->assign_by_ref('user_data', $user_data);
$smarty->assign_by_ref('data_saved', $data_saved);
break;
}
$smarty->assign_by_ref('udf', $udf);
$smarty->display('users/EditUserDefault.tpl');
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
if ($company_id == '') {
return FALSE;
}
if (!is_array($order)) {
//Use Filter Data ordering if its set.
if (isset($filter_data['sort_column']) and $filter_data['sort_order']) {
$order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']);
}
}
$additional_order_fields = array('type_id');
$sort_column_aliases = array('type' => 'type_id');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('type_id' => 'asc', 'name' => 'asc');
$strict = FALSE;
} else {
//Always try to order by status first so INACTIVE employees go to the bottom.
if (!isset($order['type_id'])) {
$order = Misc::prependArray(array('type_id' => 'asc'), $order);
}
//Always sort by last name,first name after other columns
if (!isset($order['name'])) {
$order['name'] = 'asc';
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$apf = new AccrualPolicyFactory();
$cgmf = new CompanyGenericMapFactory();
$pgf = new PolicyGroupFactory();
$pguf = new PolicyGroupUserFactory();
$ph = array('company_id' => $company_id);
$query = '
select DISTINCT a.*,
apf.name as accrual_policy,
y.first_name as created_by_first_name,
y.middle_name as created_by_middle_name,
y.last_name as created_by_last_name,
z.first_name as updated_by_first_name,
z.middle_name as updated_by_middle_name,
z.last_name as updated_by_last_name
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $cgmf->getTable() . ' as cgmf ON ( a.id = cgmf.map_id AND cgmf.object_type_id = 170 )
LEFT JOIN ' . $pgf->getTable() . ' as pgf ON ( cgmf.object_id = pgf.id AND pgf.deleted = 0 )
LEFT JOIN ' . $pguf->getTable() . ' as pguf ON ( pguf.policy_group_id = pgf.id )
LEFT JOIN ' . $apf->getTable() . ' as apf ON ( a.accrual_policy_id = apf.id AND apf.deleted = 0 )
LEFT JOIN ' . $uf->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 )
LEFT JOIN ' . $uf->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 )
where a.company_id = ?
';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.created_by', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['exclude_id']) ? $this->getWhereClauseSQL('a.id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL;
if (isset($filter_data['type']) and trim($filter_data['type']) != '' and !isset($filter_data['type_id'])) {
$filter_data['type_id'] = Option::getByFuzzyValue($filter_data['type'], $this->getOptions('type'));
}
$query .= isset($filter_data['type_id']) ? $this->getWhereClauseSQL('a.type_id', $filter_data['type_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('pguf.user_id', $filter_data['user_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['pay_stub_entry_account_id']) ? $this->getWhereClauseSQL('a.pay_stub_entry_account_id', $filter_data['pay_stub_entry_account_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['accrual_policy_id']) ? $this->getWhereClauseSQL('a.accrual_policy_id', $filter_data['accrual_policy_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['wage_group_id']) ? $this->getWhereClauseSQL('a.wage_group_id', $filter_data['wage_group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['name']) ? $this->getWhereClauseSQL('a.name', $filter_data['name'], 'text', $ph) : NULL;
$query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL;
$query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL;
$query .= '
AND a.deleted = 0
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
$this->ExecuteSQL($query, $ph, $limit, $page);
return $this;
}
} else {
$last_level = $last_saved_level;
}
Debug::Text('Last level: ' . $last_level, __FILE__, __LINE__, __METHOD__, 10);
$hierarchy_level_data[$next_blank_id] = array('id' => $next_blank_id, 'level' => $last_level + 1);
} elseif ($action != 'submit' and $action != 'delete_level') {
//New hierarchy.
$hierarchy_level_data[-1] = array('id' => -1, 'level' => 1);
}
$prepend_array_option = array(0 => TTi18n::gettext('-- Please Choose --'));
$ulf = TTnew('UserListFactory');
$user_options = $ulf->getByCompanyIDArray($current_company->getId(), FALSE, TRUE);
//Select box options;
$hotlf = TTnew('HierarchyObjectTypeListFactory');
$hierarchy_control_data['user_options'] = $user_options;
$hierarchy_control_data['level_user_options'] = Misc::prependArray($prepend_array_option, $user_options);
$hierarchy_control_data['object_type_options'] = $hotlf->getOptions('object_type');
if (isset($hierarchy_control_data['user_ids']) and is_array($hierarchy_control_data['user_ids'])) {
$tmp_user_options = $user_options;
foreach ($hierarchy_control_data['user_ids'] as $user_id) {
if (isset($tmp_user_options[$user_id])) {
$filter_user_options[$user_id] = $tmp_user_options[$user_id];
}
}
unset($user_id);
}
$smarty->assign_by_ref('filter_user_options', $filter_user_options);
$smarty->assign_by_ref('hierarchy_control_data', $hierarchy_control_data);
$smarty->assign_by_ref('hierarchy_level_data', $hierarchy_level_data);
break;
}
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');
$sort_column_aliases = array('status' => 'status_id');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('status_id' => 'asc', 'name' => 'asc');
$strict = FALSE;
} else {
//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['name'])) {
$order['name'] = 'asc';
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
y.first_name as created_by_first_name,
y.middle_name as created_by_middle_name,
y.last_name as created_by_last_name,
z.first_name as updated_by_first_name,
z.middle_name as updated_by_middle_name,
z.last_name as updated_by_last_name
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $uf->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 )
LEFT JOIN ' . $uf->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 )
where a.company_id = ?';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.created_by', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['exclude_id']) ? $this->getWhereClauseSQL('a.id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL;
if (isset($filter_data['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;
$query .= isset($filter_data['name']) ? $this->getWhereClauseSQL('a.name', $filter_data['name'], 'text_metaphone', $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['manual_id']) ? $this->getWhereClauseSQL('a.manual_id', $this->Validator->stripNon32bitInteger($filter_data['manual_id']), 'numeric', $ph) : NULL;
$query .= isset($filter_data['work_phone']) ? $this->getWhereClauseSQL('a.work_phone', $filter_data['work_phone'], 'phone', $ph) : NULL;
$query .= isset($filter_data['fax_phone']) ? $this->getWhereClauseSQL('a.work_phone', $filter_data['fax_phone'], 'phone', $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['tag']) ? $this->getWhereClauseSQL('a.id', array('company_id' => $company_id, 'object_type_id' => 110, 'tag' => $filter_data['tag']), 'tag', $ph) : NULL;
$query .= isset($filter_data['created_date']) ? $this->getWhereClauseSQL('a.created_date', $filter_data['created_date'], 'date_range', $ph) : NULL;
$query .= isset($filter_data['updated_date']) ? $this->getWhereClauseSQL('a.updated_date', $filter_data['updated_date'], 'date_range', $ph) : NULL;
/*
$query .= ( isset($filter_data['created_by']) AND is_array($filter_data['created_by']) ) ? $this->getWhereClauseSQL( 'a.created_by', $filter_data['created_by'], 'numeric_list', $ph ) : NULL;
$query .= ( isset($filter_data['updated_by']) AND is_array($filter_data['updated_by']) ) ? $this->getWhereClauseSQL( 'a.updated_by', $filter_data['updated_by'], 'numeric_list', $ph ) : NULL;
if ( isset($filter_data['created_by']) AND !is_array($filter_data['created_by']) AND trim($filter_data['created_by']) != '' ) {
$ph[] = $ph[] = $this->handleSQLSyntax(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 !is_array($filter_data['updated_by']) AND trim($filter_data['updated_by']) != '' ) {
$ph[] = $ph[] = $this->handleSQLSyntax(strtolower(trim($filter_data['updated_by'])));
$query .= ' AND (lower(z.first_name) LIKE ? OR lower(z.last_name) LIKE ? ) ';
}
*/
$query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL;
$query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL;
$query .= '
AND a.deleted = 0
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
$this->ExecuteSQL($query, $ph, $limit, $page);
return $this;
}
//Get employee titles
$utlf = TTnew('UserTitleListFactory');
$utlf->getByCompanyId($current_company->getId());
$user_title_options = Misc::prependArray($all_array_option, $utlf->getArrayByListFactory($utlf, FALSE, TRUE));
$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);
//Get pay periods
$pplf = TTnew('PayPeriodListFactory');
$pplf->getByCompanyId($current_company->getId());
$pay_period_options = Misc::prependArray($all_array_option, $pplf->getArrayByListFactory($pplf, FALSE, TRUE));
$filter_data['src_pay_period_options'] = Misc::arrayDiffByKey((array) $filter_data['pay_period_ids'], $pay_period_options);
$filter_data['selected_pay_period_options'] = Misc::arrayIntersectByKey((array) $filter_data['pay_period_ids'], $pay_period_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);
//Get primary/secondary order list
$filter_data['sort_options'] = $columns;
$filter_data['sort_options']['effective_date_order'] = 'Wage Effective Date';
unset($filter_data['sort_options']['effective_date']);
$filter_data['sort_direction_options'] = Misc::getSortDirectionArray();
/***/
$filter_data['group_by_options'] = Misc::prependArray(array('0' => TTi18n::gettext('No Grouping')), $static_columns);
/***/
$saved_report_options = $ugdlf->getByUserIdAndScriptArray($current_user->getId(), $_SERVER['SCRIPT_NAME']);
$generic_data['saved_report_options'] = $saved_report_options;
$smarty->assign_by_ref('generic_data', $generic_data);
$smarty->assign_by_ref('filter_data', $filter_data);
$smarty->assign_by_ref('ugdf', $ugdf);
$smarty->display('report/TimesheetSummary.tpl');
break;
}
foreach ($ppslf as $pay_period_schedule) {
//Debug::Arr($station,'Department', __FILE__, __LINE__, __METHOD__,10);
$pay_period_schedule_data = array('id' => $pay_period_schedule->getId(), 'company_id' => $pay_period_schedule->getCompany(), 'name' => $pay_period_schedule->getName(), 'description' => $pay_period_schedule->getDescription(), 'type' => $pay_period_schedule->getType(), 'start_week_day_id' => $pay_period_schedule->getStartWeekDay(), 'start_day_of_week' => $pay_period_schedule->getStartDayOfWeek(), 'transaction_date' => $pay_period_schedule->getTransactionDate(), 'primary_day_of_month' => $pay_period_schedule->getPrimaryDayOfMonth(), 'secondary_day_of_month' => $pay_period_schedule->getSecondaryDayOfMonth(), 'primary_transaction_day_of_month' => $pay_period_schedule->getPrimaryTransactionDayOfMonth(), 'secondary_transaction_day_of_month' => $pay_period_schedule->getSecondaryTransactionDayOfMonth(), 'transaction_date_bd' => $pay_period_schedule->getTransactionDateBusinessDay(), 'anchor_date' => $pay_period_schedule->getAnchorDate(), 'annual_pay_periods' => $pay_period_schedule->getAnnualPayPeriods(), 'day_start_time' => $pay_period_schedule->getDayStartTime(), 'time_zone' => $pay_period_schedule->getTimeZone(), 'new_day_trigger_time' => $pay_period_schedule->getNewDayTriggerTime(), 'maximum_shift_time' => $pay_period_schedule->getMaximumShiftTime(), 'shift_assigned_day_id' => $pay_period_schedule->getShiftAssignedDay(), 'timesheet_verify_type_id' => $pay_period_schedule->getTimeSheetVerifyType(), 'timesheet_verify_before_end_date' => $pay_period_schedule->getTimeSheetVerifyBeforeEndDate(), 'timesheet_verify_before_transaction_date' => $pay_period_schedule->getTimeSheetVerifyBeforeTransactionDate(), 'timesheet_verify_notice_before_transaction_date' => $pay_period_schedule->getTimeSheetVerifyNoticeBeforeTransactionDate(), 'timesheet_verify_notice_email' => $pay_period_schedule->getTimeSheetVerifyNoticeEmail(), 'user_ids' => $pay_period_schedule->getUser(), 'deleted' => $pay_period_schedule->getDeleted(), 'created_date' => $pay_period_schedule->getCreatedDate(), 'created_by' => $pay_period_schedule->getCreatedBy(), 'updated_date' => $pay_period_schedule->getUpdatedDate(), 'updated_by' => $pay_period_schedule->getUpdatedBy(), 'deleted_date' => $pay_period_schedule->getDeletedDate(), 'deleted_by' => $pay_period_schedule->getDeletedBy());
}
} elseif ($action != 'submit') {
$pay_period_schedule_data = array('anchor_date' => TTDate::getBeginMonthEpoch(time()), 'day_start_time' => 0, 'new_day_trigger_time' => 3600 * 4, 'maximum_shift_time' => 3600 * 16, 'time_zone' => $current_user_prefs->getTimeZone(), 'type' => 20, 'timesheet_verify_type_id' => 10, 'timesheet_verify_before_end_date' => 0, 'timesheet_verify_before_transaction_date' => 0, 'annual_pay_periods' => 0);
}
//Select box options;
$pay_period_schedule_data['type_options'] = $ppsf->getOptions('type');
$pay_period_schedule_data['start_week_day_options'] = $ppsf->getOptions('start_week_day');
$pay_period_schedule_data['shift_assigned_day_options'] = $ppsf->getOptions('shift_assigned_day');
$pay_period_schedule_data['timesheet_verify_type_options'] = $ppsf->getOptions('timesheet_verify_type');
$pay_period_schedule_data['time_zone_options'] = $ppsf->getTimeZoneOptions();
$pay_period_schedule_data['transaction_date_bd_options'] = $ppsf->getOptions('transaction_date_business_day');
$pay_period_schedule_data['day_of_week_options'] = TTDate::getDayOfWeekArray();
$pay_period_schedule_data['transaction_date_options'] = Misc::prependArray(array(0 => '0'), TTDate::getDayOfMonthArray());
$pay_period_schedule_data['day_of_month_options'] = TTDate::getDayOfMonthArray();
$pay_period_schedule_data['day_of_month_options'][-1] = TTi18n::gettext('- Last Day Of Month -');
$pay_period_schedule_data['user_options'] = UserListFactory::getByCompanyIdArray($current_company->getId(), FALSE, TRUE);
if (isset($pay_period_schedule_data['user_ids']) and is_array($pay_period_schedule_data['user_ids'])) {
$tmp_user_options = UserListFactory::getByCompanyIdArray($current_company->getId(), FALSE, TRUE);
foreach ($pay_period_schedule_data['user_ids'] as $user_id) {
if (isset($tmp_user_options[$user_id])) {
$filter_user_options[$user_id] = $tmp_user_options[$user_id];
}
}
unset($user_id);
}
$smarty->assign_by_ref('filter_user_options', $filter_user_options);
$smarty->assign_by_ref('pay_period_schedule_data', $pay_period_schedule_data);
break;
protected function _getOptions($name, $params = NULL)
{
$retval = NULL;
switch ($name) {
case 'output_format':
$retval = array_merge(parent::getOptions('default_output_format'), array('-1100-pdf_form' => TTi18n::gettext('Form')));
break;
case 'default_setup_fields':
$retval = array('template', 'time_period', 'columns');
break;
case 'setup_fields':
$retval = array('-1000-template' => TTi18n::gettext('Template'), '-1010-time_period' => TTi18n::gettext('Time Period'), '-2010-user_status_id' => TTi18n::gettext('Employee Status'), '-2020-user_group_id' => TTi18n::gettext('Employee Group'), '-2030-user_title_id' => TTi18n::gettext('Employee Title'), '-2040-include_user_id' => TTi18n::gettext('Employee Include'), '-2050-exclude_user_id' => TTi18n::gettext('Employee Exclude'), '-2060-default_branch_id' => TTi18n::gettext('Default Branch'), '-2070-default_department_id' => TTi18n::gettext('Default Department'), '-2100-custom_filter' => TTi18n::gettext('Custom Filter'), '-4020-exclude_ytd_adjustment' => TTi18n::gettext('Exclude YTD Adjustments'), '-5000-columns' => TTi18n::gettext('Display Columns'), '-5010-group' => TTi18n::gettext('Group By'), '-5020-sub_total' => TTi18n::gettext('SubTotal By'), '-5030-sort' => TTi18n::gettext('Sort By'));
break;
case 'time_period':
$retval = TTDate::getTimePeriodOptions();
break;
case 'date_columns':
$retval = TTDate::getReportDateOptions(NULL, TTi18n::getText('Date'), 13, TRUE);
break;
case 'report_custom_column':
if (getTTProductEdition() >= TT_PRODUCT_PROFESSIONAL) {
$rcclf = TTnew('ReportCustomColumnListFactory');
// Because the Filter type is just only a filter criteria and not need to be as an option of Display Columns, Group By, Sub Total, Sort By dropdowns.
// So just get custom columns with Selection and Formula.
$custom_column_labels = $rcclf->getByCompanyIdAndTypeIdAndFormatIdAndScriptArray($this->getUserObject()->getCompany(), $rcclf->getOptions('display_column_type_ids'), NULL, 'Form940Report', 'custom_column');
if (is_array($custom_column_labels)) {
$retval = Misc::addSortPrefix($custom_column_labels, 9500);
}
}
break;
case 'report_custom_filters':
if (getTTProductEdition() >= TT_PRODUCT_PROFESSIONAL) {
$rcclf = TTnew('ReportCustomColumnListFactory');
$retval = $rcclf->getByCompanyIdAndTypeIdAndFormatIdAndScriptArray($this->getUserObject()->getCompany(), $rcclf->getOptions('filter_column_type_ids'), NULL, 'Form940Report', 'custom_column');
}
break;
case 'report_dynamic_custom_column':
if (getTTProductEdition() >= TT_PRODUCT_PROFESSIONAL) {
$rcclf = TTnew('ReportCustomColumnListFactory');
$report_dynamic_custom_column_labels = $rcclf->getByCompanyIdAndTypeIdAndFormatIdAndScriptArray($this->getUserObject()->getCompany(), $rcclf->getOptions('display_column_type_ids'), $rcclf->getOptions('dynamic_format_ids'), 'Form940Report', 'custom_column');
if (is_array($report_dynamic_custom_column_labels)) {
$retval = Misc::addSortPrefix($report_dynamic_custom_column_labels, 9700);
}
}
break;
case 'report_static_custom_column':
if (getTTProductEdition() >= TT_PRODUCT_PROFESSIONAL) {
$rcclf = TTnew('ReportCustomColumnListFactory');
$report_static_custom_column_labels = $rcclf->getByCompanyIdAndTypeIdAndFormatIdAndScriptArray($this->getUserObject()->getCompany(), $rcclf->getOptions('display_column_type_ids'), $rcclf->getOptions('static_format_ids'), 'Form940Report', 'custom_column');
if (is_array($report_static_custom_column_labels)) {
$retval = Misc::addSortPrefix($report_static_custom_column_labels, 9700);
}
}
break;
case 'formula_columns':
$retval = TTMath::formatFormulaColumns(array_merge(array_diff($this->getOptions('static_columns'), (array) $this->getOptions('report_static_custom_column')), $this->getOptions('dynamic_columns')));
break;
case 'filter_columns':
$retval = TTMath::formatFormulaColumns(array_merge($this->getOptions('static_columns'), $this->getOptions('dynamic_columns'), (array) $this->getOptions('report_dynamic_custom_column')));
break;
case 'static_columns':
$retval = array('-1000-first_name' => TTi18n::gettext('First Name'), '-1001-middle_name' => TTi18n::gettext('Middle Name'), '-1002-last_name' => TTi18n::gettext('Last Name'), '-1005-full_name' => TTi18n::gettext('Full Name'), '-1030-employee_number' => TTi18n::gettext('Employee #'), '-1035-sin' => TTi18n::gettext('SIN/SSN'), '-1040-status' => TTi18n::gettext('Status'), '-1050-title' => TTi18n::gettext('Title'), '-1060-province' => TTi18n::gettext('Province/State'), '-1070-country' => TTi18n::gettext('Country'), '-1080-group' => TTi18n::gettext('Group'), '-1090-default_branch' => TTi18n::gettext('Default Branch'), '-1100-default_department' => TTi18n::gettext('Default Department'), '-1110-currency' => TTi18n::gettext('Currency'), '-1400-permission_control' => TTi18n::gettext('Permission Group'), '-1410-pay_period_schedule' => TTi18n::gettext('Pay Period Schedule'), '-1420-policy_group' => TTi18n::gettext('Policy Group'));
$retval = array_merge($retval, $this->getOptions('date_columns'), (array) $this->getOptions('report_static_custom_column'));
ksort($retval);
break;
case 'dynamic_columns':
$retval = array('-2010-total_payments' => TTi18n::gettext('Total Payments'), '-2020-exempt_payments' => TTi18n::gettext('Exempt Payments'), '-2030-excess_payments' => TTi18n::gettext('Excess Payments'), '-2040-taxable_wages' => TTi18n::gettext('Taxable Wages'), '-2050-before_adjustment_tax' => TTi18n::gettext('Tax Before Adjustments'), '-2052-adjustment_tax' => TTi18n::gettext('Tax Adjustments'), '-2054-after_adjustment_tax' => TTi18n::gettext('Tax After Adjustments'));
break;
case 'columns':
$retval = array_merge($this->getOptions('static_columns'), $this->getOptions('dynamic_columns'), (array) $this->getOptions('report_dynamic_custom_column'));
break;
case 'column_format':
//Define formatting function for each column.
$columns = array_merge($this->getOptions('dynamic_columns'), (array) $this->getOptions('report_custom_column'));
if (is_array($columns)) {
foreach ($columns as $column => $name) {
$retval[$column] = 'currency';
}
}
break;
case 'aggregates':
$retval = array();
$dynamic_columns = array_keys(Misc::trimSortPrefix(array_merge($this->getOptions('dynamic_columns'), (array) $this->getOptions('report_dynamic_custom_column'))));
if (is_array($dynamic_columns)) {
foreach ($dynamic_columns as $column) {
switch ($column) {
default:
$retval[$column] = 'sum';
}
}
}
break;
case 'state':
$retval = Misc::prependArray(array(0 => TTi18n::getText('- Multi-state Employer -')), $this->getUserObject()->getCompanyObject()->getOptions('province', 'US'));
break;
case 'return_type':
$retval = array(10 => TTi18n::getText('Amended'), 20 => TTi18n::getText('Successor Employer'), 30 => TTi18n::getText('No Payments to Employees'), 40 => TTi18n::getText('Final: Business closed or stopped paying wages'));
break;
case 'exempt_payment':
$retval = array(10 => TTi18n::getText('4a. Fringe benefits'), 20 => TTi18n::getText('4b. Group term life insurance'), 30 => TTi18n::getText('4c. Retirement/Pension'), 40 => TTi18n::getText('4d. Dependant care'), 50 => TTi18n::getText('4e. Other'));
break;
case 'templates':
$retval = array('-1010-by_quarter' => TTi18n::gettext('by Quarter'), '-1010-by_month' => TTi18n::gettext('by Month'), '-1020-by_employee' => TTi18n::gettext('by Employee'), '-1030-by_branch' => TTi18n::gettext('by Branch'), '-1040-by_department' => TTi18n::gettext('by Department'), '-1050-by_branch_by_department' => TTi18n::gettext('by Branch/Department'), '-1060-by_month_by_employee' => TTi18n::gettext('by Month/Employee'), '-1070-by_month_by_branch' => TTi18n::gettext('by Month/Branch'), '-1080-by_month_by_department' => TTi18n::gettext('by Month/Department'), '-1090-by_month_by_branch_by_department' => TTi18n::gettext('by Month/Branch/Department'));
break;
case 'template_config':
$template = strtolower(Misc::trimSortPrefix($params['template']));
if (isset($template) and $template != '') {
switch ($template) {
case 'default':
//Proper settings to generate the form.
//$retval['-1010-time_period']['time_period'] = 'last_quarter';
$retval['columns'] = $this->getOptions('columns');
$retval['group'][] = 'date_quarter_month';
$retval['sort'][] = array('date_quarter_month' => 'asc');
$retval['other']['grand_total'] = TRUE;
break;
default:
Debug::Text(' Parsing template name: ' . $template, __FILE__, __LINE__, __METHOD__, 10);
$retval['-1010-time_period']['time_period'] = 'last_year';
//Parse template name, and use the keywords separated by '+' to determine settings.
$template_keywords = explode('+', $template);
if (is_array($template_keywords)) {
foreach ($template_keywords as $template_keyword) {
Debug::Text(' Keyword: ' . $template_keyword, __FILE__, __LINE__, __METHOD__, 10);
switch ($template_keyword) {
//Columns
//Filter
//Group By
//SubTotal
//Sort
case 'by_quarter':
$retval['columns'][] = 'date_quarter';
$retval['group'][] = 'date_quarter';
$retval['sort'][] = array('date_quarter' => 'asc');
break;
case 'by_month':
$retval['columns'][] = 'date_month';
$retval['group'][] = 'date_month';
$retval['sort'][] = array('date_month' => 'asc');
break;
case 'by_employee':
$retval['columns'][] = 'first_name';
$retval['columns'][] = 'last_name';
$retval['group'][] = 'first_name';
$retval['group'][] = 'last_name';
$retval['sort'][] = array('last_name' => 'asc');
$retval['sort'][] = array('first_name' => 'asc');
break;
case 'by_branch':
$retval['columns'][] = 'default_branch';
$retval['group'][] = 'default_branch';
$retval['sort'][] = array('default_branch' => 'asc');
break;
case 'by_department':
$retval['columns'][] = 'default_department';
$retval['group'][] = 'default_department';
$retval['sort'][] = array('default_department' => 'asc');
break;
case 'by_branch_by_department':
$retval['columns'][] = 'default_branch';
$retval['columns'][] = 'default_department';
$retval['group'][] = 'default_branch';
$retval['group'][] = 'default_department';
$retval['sub_total'][] = 'default_branch';
$retval['sort'][] = array('default_branch' => 'asc');
$retval['sort'][] = array('default_department' => 'asc');
break;
case 'by_month_by_employee':
$retval['columns'][] = 'date_month';
$retval['columns'][] = 'first_name';
$retval['columns'][] = 'last_name';
$retval['group'][] = 'date_month';
$retval['group'][] = 'first_name';
$retval['group'][] = 'last_name';
$retval['sub_total'][] = 'date_month';
$retval['sort'][] = array('date_month' => 'asc');
$retval['sort'][] = array('last_name' => 'asc');
$retval['sort'][] = array('first_name' => 'asc');
break;
case 'by_month_by_branch':
$retval['columns'][] = 'date_month';
$retval['columns'][] = 'default_branch';
$retval['group'][] = 'date_month';
$retval['group'][] = 'default_branch';
$retval['sub_total'][] = 'date_month';
$retval['sort'][] = array('date_month' => 'asc');
$retval['sort'][] = array('default_branch' => 'asc');
break;
case 'by_month_by_department':
$retval['columns'][] = 'date_month';
$retval['columns'][] = 'default_department';
$retval['group'][] = 'date_month';
$retval['group'][] = 'default_department';
$retval['sub_total'][] = 'date_month';
$retval['sort'][] = array('date_month' => 'asc');
$retval['sort'][] = array('default_department' => 'asc');
break;
case 'by_month_by_branch_by_department':
$retval['columns'][] = 'date_month';
$retval['columns'][] = 'default_branch';
$retval['columns'][] = 'default_department';
$retval['group'][] = 'date_month';
$retval['group'][] = 'default_branch';
$retval['group'][] = 'default_department';
$retval['sub_total'][] = 'date_month';
$retval['sub_total'][] = 'default_branch';
$retval['sort'][] = array('date_month' => 'asc');
$retval['sort'][] = array('default_branch' => 'asc');
$retval['sort'][] = array('default_department' => 'asc');
break;
}
}
}
$retval['columns'] = array_merge($retval['columns'], array_keys(Misc::trimSortPrefix($this->getOptions('dynamic_columns'))));
break;
}
}
//Set the template dropdown as well.
$retval['-1000-template'] = $template;
//Add sort prefixes so Flex can maintain order.
if (isset($retval['filter'])) {
$retval['-5000-filter'] = $retval['filter'];
unset($retval['filter']);
}
if (isset($retval['columns'])) {
$retval['-5010-columns'] = $retval['columns'];
unset($retval['columns']);
}
if (isset($retval['group'])) {
$retval['-5020-group'] = $retval['group'];
unset($retval['group']);
}
if (isset($retval['sub_total'])) {
$retval['-5030-sub_total'] = $retval['sub_total'];
unset($retval['sub_total']);
}
if (isset($retval['sort'])) {
$retval['-5040-sort'] = $retval['sort'];
unset($retval['sort']);
}
Debug::Arr($retval, ' Template Config for: ' . $template, __FILE__, __LINE__, __METHOD__, 10);
break;
default:
//Call report parent class options function for options valid for all reports.
$retval = $this->__getOptions($name);
break;
}
return $retval;
}
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
if ($company_id == '') {
return FALSE;
}
if (!is_array($order)) {
//Use Filter Data ordering if its set.
if (isset($filter_data['sort_column']) and $filter_data['sort_order']) {
$order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']);
}
}
$additional_order_fields = array('type_id');
$sort_column_aliases = array('type' => 'type_id');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('type_id' => 'asc', 'name' => 'asc');
$strict = FALSE;
} else {
//Always try to order by status first so INACTIVE employees go to the bottom.
if (!isset($order['type_id'])) {
$order = Misc::prependArray(array('type_id' => 'asc'), $order);
}
//Always sort by last name,first name after other columns
if (!isset($order['name'])) {
$order['name'] = 'asc';
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
y.first_name as created_by_first_name,
y.middle_name as created_by_middle_name,
y.last_name as created_by_last_name,
z.first_name as updated_by_first_name,
z.middle_name as updated_by_middle_name,
z.last_name as updated_by_last_name
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $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['permission_children_ids']) and isset($filter_data['permission_children_ids'][0]) and !in_array(-1, (array) $filter_data['permission_children_ids'])) {
$query .= ' AND a.created_by in (' . $this->getListSQL($filter_data['permission_children_ids'], $ph) . ') ';
}
if (isset($filter_data['id']) and isset($filter_data['id'][0]) and !in_array(-1, (array) $filter_data['id'])) {
$query .= ' AND a.id in (' . $this->getListSQL($filter_data['id'], $ph) . ') ';
}
if (isset($filter_data['exclude_id']) and isset($filter_data['exclude_id'][0]) and !in_array(-1, (array) $filter_data['exclude_id'])) {
$query .= ' AND a.id not in (' . $this->getListSQL($filter_data['exclude_id'], $ph) . ') ';
}
if (isset($filter_data['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['name']) and trim($filter_data['name']) != '') {
$ph[] = strtolower(trim($filter_data['name']));
$query .= ' AND lower(a.name) LIKE ?';
}
$query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL;
$query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL;
$query .= '
AND a.deleted = 0
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
$this->ExecuteSQL($query, $ph, $limit, $page);
return $this;
}
//Skip this row.
Debug::Text('Skipping Row: User ID: ' . $user_id, __FILE__, __LINE__, __METHOD__, 10);
continue;
}
$rows[] = array('id' => $rsc_obj->getId(), 'user_id' => $user_id, 'name' => $rsc_obj->getColumn('name'), 'description' => $rsc_obj->getColumn('description'), 'start_week' => $rsc_obj->getStartWeek(), 'start_date' => $rsc_obj->getStartDate(), 'end_date' => $rsc_obj->getEndDate(), 'first_name' => $u_obj->getFirstName(), 'middle_name' => $u_obj->getMiddleName(), 'last_name' => $u_obj->getLastName(), 'user_full_name' => $u_obj->getFullName(TRUE), 'is_owner' => $permission->isOwner($u_obj->getCreatedBy(), $u_obj->getId()), 'is_child' => $permission->isChild($u_obj->getId(), $permission_children_ids), 'deleted' => $rsc_obj->getDeleted());
}
$all_array_option = array('-1' => TTi18n::gettext('-- Any --'));
$ulf->getSearchByCompanyIdAndArrayCriteria($current_company->getId(), $filter_data);
$filter_data['user_options'] = Misc::prependArray($all_array_option, UserListFactory::getArrayByListFactory($ulf, FALSE, TRUE));
//Select box options;
$filter_data['template_options'] = Misc::prependArray($all_array_option, $template_options);
$filter_data['branch_options'] = Misc::prependArray($all_array_option, $branch_options);
$filter_data['department_options'] = Misc::prependArray($all_array_option, $department_options);
$filter_data['title_options'] = Misc::prependArray($all_array_option, $title_options);
$filter_data['group_options'] = Misc::prependArray($all_array_option, $group_options);
$filter_data['status_options'] = Misc::prependArray($all_array_option, $ulf->getOptions('status'));
$filter_data['saved_search_options'] = $ugdlf->getArrayByListFactory($ugdlf->getByUserIdAndScript($current_user->getId(), $_SERVER['SCRIPT_NAME']), FALSE);
//Get column list
$filter_data['src_column_options'] = Misc::arrayDiffByKey((array) $filter_data['columns'], $columns);
$filter_data['selected_column_options'] = Misc::arrayIntersectByKey((array) $filter_data['columns'], $columns);
$filter_data['sort_options'] = Misc::trimSortPrefix($columns);
$filter_data['sort_direction_options'] = Misc::getSortDirectionArray(TRUE);
foreach ($filter_data['columns'] as $column_key) {
$filter_columns[Misc::trimSortPrefix($column_key)] = $columns[$column_key];
}
unset($column_key);
$smarty->assign_by_ref('rows', $rows);
$smarty->assign_by_ref('filter_data', $filter_data);
$smarty->assign_by_ref('columns', $filter_columns);
$smarty->assign('total_columns', count($filter_columns) + 3);
$smarty->assign_by_ref('sort_column', $sort_column);
$pplf = new PremiumPolicyListFactory();
$premium_policy_options = Misc::prependArray($none_array_option, $pplf->getByCompanyIDArray($current_company->getId(), FALSE));
$riplf = new RoundIntervalPolicyListFactory();
$round_interval_policy_options = Misc::prependArray($none_array_option, $riplf->getByCompanyIDArray($current_company->getId(), FALSE));
$mplf = new MealPolicyListFactory();
$meal_options = Misc::prependArray($none_array_option, $mplf->getByCompanyIdArray($current_company->getId(), FALSE));
$bplf = new BreakPolicyListFactory();
$break_options = Misc::prependArray($none_array_option, $bplf->getByCompanyIdArray($current_company->getId(), FALSE));
$epclf = new ExceptionPolicyControlListFactory();
$exception_options = Misc::prependArray($none_array_option, $epclf->getByCompanyIdArray($current_company->getId(), FALSE));
$hplf = new HolidayPolicyListFactory();
$holiday_policy_options = Misc::prependArray($none_array_option, $hplf->getByCompanyIdArray($current_company->getId(), FALSE));
$aplf = new AccrualPolicyListFactory();
$aplf->getByCompanyIdAndTypeID($current_company->getId(), array(20, 30));
//Calendar and Hour based.
$accrual_options = Misc::prependArray($none_array_option, $aplf->getArrayByListFactory($aplf, FALSE));
//Select box options;
$data['user_options'] = $user_options;
$data['over_time_policy_options'] = $over_time_policy_options;
$data['premium_policy_options'] = $premium_policy_options;
$data['round_interval_policy_options'] = $round_interval_policy_options;
$data['accrual_policy_options'] = $accrual_options;
$data['meal_options'] = $meal_options;
$data['break_options'] = $break_options;
$data['exception_options'] = $exception_options;
$data['holiday_policy_options'] = $holiday_policy_options;
if (isset($data['user_ids']) and is_array($data['user_ids'])) {
$tmp_user_options = $user_options;
foreach ($data['user_ids'] as $user_id) {
if (isset($tmp_user_options[$user_id])) {
$filter_user_options[$user_id] = $tmp_user_options[$user_id];
}
$rows[] = array('id' => $e_obj->getId(), 'user_date_id' => $e_obj->getUserDateID(), 'user_id' => $e_obj->getColumn('user_id'), 'first_name' => $user_obj->getFirstName(), 'middle_name' => $user_obj->getMiddleName(), 'last_name' => $user_obj->getLastName(), 'user_full_name' => Option::getByKey($e_obj->getColumn('user_id'), $user_options), 'date_stamp' => TTDate::getDate('DATE', TTDate::strtotime($e_obj->getColumn('user_date_stamp'))), 'type_id' => $e_obj->getType(), 'severity_id' => $e_obj->getColumn('severity_id'), 'severity' => Option::getByKey($e_obj->getColumn('severity_id'), $exception_policy_severity_options), 'exception_color' => $exception_color, 'exception_policy_type_id' => $e_obj->getColumn('exception_policy_type_id'), 'exception_policy_type' => Option::getByKey($e_obj->getColumn('exception_policy_type_id'), $exception_policy_type_options), 'created_date' => $e_obj->getCreatedDate(), 'deleted' => $e_obj->getDeleted());
}
$smarty->assign_by_ref('rows', $rows);
$all_array_option = array('-1' => TTi18n::gettext('-- Any --'));
$ulf->getSearchByCompanyIdAndArrayCriteria($current_company->getId(), $filter_data);
$filter_data['user_options'] = Misc::prependArray($all_array_option, UserListFactory::getArrayByListFactory($ulf, FALSE, TRUE));
//Select box options;
$filter_data['branch_options'] = Misc::prependArray($all_array_option, $branch_options);
$filter_data['department_options'] = Misc::prependArray($all_array_option, $department_options);
$filter_data['title_options'] = Misc::prependArray($all_array_option, $title_options);
$filter_data['group_options'] = Misc::prependArray($all_array_option, $group_options);
$filter_data['status_options'] = Misc::prependArray($all_array_option, $ulf->getOptions('status'));
$filter_data['pay_period_options'] = Misc::prependArray($all_array_option, $pay_period_options);
$filter_data['severity_options'] = Misc::prependArray($all_array_option, $exception_policy_severity_options);
$filter_data['type_options'] = Misc::prependArray($all_array_option, $exception_policy_type_options);
$filter_data['saved_search_options'] = $ugdlf->getArrayByListFactory($ugdlf->getByUserIdAndScript($current_user->getId(), $_SERVER['SCRIPT_NAME']), FALSE);
//Get column list
$filter_data['src_column_options'] = Misc::arrayDiffByKey((array) $filter_data['columns'], $columns);
$filter_data['selected_column_options'] = Misc::arrayIntersectByKey((array) $filter_data['columns'], $columns);
$filter_data['sort_options'] = Misc::trimSortPrefix($columns);
$filter_data['sort_direction_options'] = Misc::getSortDirectionArray(TRUE);
foreach ($filter_data['columns'] as $column_key) {
$filter_columns[Misc::trimSortPrefix($column_key)] = $columns[$column_key];
}
unset($column_key);
$smarty->assign_by_ref('sort_column', $sort_column);
$smarty->assign_by_ref('sort_order', $sort_order);
$smarty->assign_by_ref('filter_data', $filter_data);
$smarty->assign_by_ref('columns', $filter_columns);
$smarty->assign('total_columns', count($filter_columns) + 3);
$udtlf->getById($id);
foreach ($udtlf as $udt_obj) {
//Debug::Arr($station,'Department', __FILE__, __LINE__, __METHOD__,10);
$user_id = $udt_obj->getUserDateObject()->getUser();
$udt_data = array('id' => $udt_obj->getId(), 'user_date_id' => $udt_obj->getUserDateId(), 'date_stamp' => $udt_obj->getUserDateObject()->getDateStamp(), 'user_id' => $udt_obj->getUserDateObject()->getUser(), 'user_full_name' => $udt_obj->getUserDateObject()->getUserObject()->getFullName(), 'status_id' => $udt_obj->getStatus(), 'type_id' => $udt_obj->getType(), 'total_time' => $udt_obj->getTotalTime(), 'absence_policy_id' => $udt_obj->getAbsencePolicyID(), 'branch_id' => $udt_obj->getBranch(), 'department_id' => $udt_obj->getDepartment(), 'job_id' => $udt_obj->getJob(), 'job_item_id' => $udt_obj->getJobItem(), 'override' => $udt_obj->getOverride(), 'created_date' => $udt_obj->getCreatedDate(), 'created_by' => $udt_obj->getCreatedBy(), 'updated_date' => $udt_obj->getUpdatedDate(), 'updated_by' => $udt_obj->getUpdatedBy(), 'deleted_date' => $udt_obj->getDeletedDate(), 'deleted_by' => $udt_obj->getDeletedBy());
}
} elseif ($action != 'submit') {
Debug::Text(' ID was NOT passed: ' . $id, __FILE__, __LINE__, __METHOD__, 10);
//Get user full name
$ulf = TTnew('UserListFactory');
$user_obj = $ulf->getById($user_id)->getCurrent();
$user_date_id = UserDateFactory::getUserDateID($user_id, $date_stamp);
$udt_data = array('user_id' => $user_id, 'date_stamp' => $date_stamp, 'user_date_id' => $user_date_id, 'user_full_name' => $user_obj->getFullName(), 'branch_id' => $user_obj->getDefaultBranch(), 'department_id' => $user_obj->getDefaultDepartment(), 'total_time' => 0, 'override' => TRUE);
}
$aplf = TTnew('AbsencePolicyListFactory');
$absence_policy_options = Misc::prependArray(array(0 => TTi18n::gettext('-- Please Choose --')), $aplf->getByCompanyIdArray($current_company->getId()));
$blf = TTnew('BranchListFactory');
$branch_options = $blf->getByCompanyIdArray($current_company->getId());
$dlf = TTnew('DepartmentListFactory');
$department_options = $dlf->getByCompanyIdArray($current_company->getId());
if ($current_company->getProductEdition() >= 20) {
$jlf = TTnew('JobListFactory');
$jlf->getByCompanyIdAndUserIdAndStatus($current_company->getId(), $user_id, array(10, 20, 30, 40));
$udt_data['job_options'] = $jlf->getArrayByListFactory($jlf, TRUE, TRUE);
$udt_data['job_manual_id_options'] = $jlf->getManualIDArrayByListFactory($jlf, TRUE);
$jilf = TTnew('JobItemListFactory');
$jilf->getByCompanyId($current_company->getId());
$udt_data['job_item_options'] = $jilf->getArrayByListFactory($jilf, TRUE, TRUE);
$udt_data['job_item_manual_id_options'] = $jilf->getManualIdArrayByListFactory($jilf, TRUE);
}
//Select box options;
$department_options = Misc::prependArray($all_array_option, $dlf->getArrayByListFactory($dlf, FALSE, TRUE));
$filter_data['src_department_options'] = Misc::arrayDiffByKey((array) $filter_data['department_ids'], $department_options);
$filter_data['selected_department_options'] = Misc::arrayIntersectByKey((array) $filter_data['department_ids'], $department_options);
//Get employee titles
$utlf = TTnew('UserTitleListFactory');
$utlf->getByCompanyId($current_company->getId());
$user_title_options = Misc::prependArray($all_array_option, $utlf->getArrayByListFactory($utlf, FALSE, TRUE));
$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);
//Get Log Actions
$lf = TTnew('LogFactory');
$log_action_options = Misc::prependArray($all_array_option, $lf->getOptions('action'));
$filter_data['src_log_action_options'] = Misc::arrayDiffByKey((array) $filter_data['log_action_ids'], $log_action_options);
$filter_data['selected_log_action_options'] = Misc::arrayIntersectByKey((array) $filter_data['log_action_ids'], $log_action_options);
//Get table names
$log_table_name_options = Misc::prependArray($all_array_option, $lf->getOptions('table_name'));
$filter_data['src_log_table_name_options'] = Misc::arrayDiffByKey((array) $filter_data['log_table_name_ids'], $log_table_name_options);
$filter_data['selected_log_table_name_options'] = Misc::arrayIntersectByKey((array) $filter_data['log_table_name_ids'], $log_table_name_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);
//Get primary/secondary order list
$filter_data['sort_options'] = $columns;
$filter_data['sort_direction_options'] = Misc::getSortDirectionArray();
//$filter_data['group_by_options'] = Misc::prependArray( array('0' => TTi18n::gettext('No Grouping')), $static_columns );
$saved_report_options = $ugdlf->getByUserIdAndScriptArray($current_user->getId(), $_SERVER['SCRIPT_NAME']);
$generic_data['saved_report_options'] = $saved_report_options;
$smarty->assign_by_ref('generic_data', $generic_data);
$smarty->assign_by_ref('filter_data', $filter_data);
$smarty->assign_by_ref('ugdf', $ugdf);
$smarty->display('report/SystemLog.tpl');
