public static function getFilteredIDs(array $filter_ar, $return_all_on_empty_filter = false)
{
// check filter and return all or none
if (!$filter_ar && !$return_all_on_empty_filter) {
return array();
}
// Build WHERE clause
$where_sql = array();
$prepped_where_fields_values = array();
foreach ($filter_ar as $filter_field => $filter_value) {
switch ($filter_field) {
case 'created_by_user_id':
case 'employee_id':
if (is_array($filter_value)) {
$tmp_filter_select_ar = array();
foreach ($filter_value as $tmp_key => $tmp_value) {
$tmp_filter_select_ar[':' . $filter_field . '_' . $tmp_key] = $tmp_value;
}
$where_sql[] = 'mplylvs.' . $filter_field . ' IN (' . implode(', ', array_keys($tmp_filter_select_ar)) . ')';
$prepped_where_fields_values = array_merge($prepped_where_fields_values, $tmp_filter_select_ar);
} else {
$where_sql[] = 'mplylvs.' . $filter_field . ' = :' . $filter_field;
$prepped_where_fields_values[':' . $filter_field] = $filter_value;
}
break;
case 'minimum_creation_date':
$where_sql[] = 'mplylvs.date_created >= :' . $filter_field;
$prepped_where_fields_values[':' . $filter_field] = date('Y-m-d', strtotime($filter_value));
break;
case 'maximum_creation_date':
$where_sql[] = 'mplylvs.date_created <= :' . $filter_field;
$prepped_where_fields_values[':' . $filter_field] = date('Y-m-d', strtotime($filter_value));
break;
case 'minimum_leave_start_date':
$where_sql[] = 'mplylvs.leave_start_date >= :' . $filter_field;
$prepped_where_fields_values[':' . $filter_field] = date('Y-m-d', strtotime($filter_value));
break;
case 'maximum_leave_start_date':
$where_sql[] = 'mplylvs.leave_start_date <= :' . $filter_field;
$prepped_where_fields_values[':' . $filter_field] = date('Y-m-d', strtotime($filter_value));
break;
case 'minimum_leave_end_date':
$where_sql[] = 'mplylvs.leave_end_date >= :' . $filter_field;
$prepped_where_fields_values[':' . $filter_field] = date('Y-m-d', strtotime($filter_value));
break;
case 'maximum_leave_end_date':
$where_sql[] = 'mplylvs.leave_end_date <= :' . $filter_field;
$prepped_where_fields_values[':' . $filter_field] = date('Y-m-d', strtotime($filter_value));
break;
}
}
// check for month/year period filter
if (!empty($filter_ar['month']) && !empty($filter_ar['year'])) {
$where_sql[] = '(mplylvs.leave_start_date <= :check_period_end AND COALESCE(mplylvs.leave_end_date, mplylvs.leave_start_date) >= :check_period_start)';
$check_period_start_int = mktime(0, 0, 0, $filter_ar['month'], 1, $filter_ar['year']);
$prepped_where_fields_values[':check_period_start'] = date('Y-m-d', $check_period_start_int);
$prepped_where_fields_values[':check_period_end'] = date('Y-m-d', TS_Format_Dates::lastDayOfMonth($check_period_start_int));
}
// Build SQL
$sql_ar[] = 'SELECT DISTINCT mplylvs.id FROM ' . TS_DB_HRM_TBNames::$employee_leaves . ' mplylvs';
if ($where_sql) {
$sql_ar[] = 'WHERE ' . implode(' AND ', $where_sql);
}
// Get query result
return DB::fetchAllColumn(implode(' ', $sql_ar), $prepped_where_fields_values);
}
public static function getFilteredIDs(array $filter_ar, $return_all_on_empty_filter = false)
{
// check filter and return all or none
if (!$filter_ar && !$return_all_on_empty_filter) {
return array();
}
// Build WHERE clause
$where_sql = array();
$prepped_where_fields_values = array();
foreach ($filter_ar as $filter_field => $filter_value) {
// set db table abbreviation
$db_table_abbr = 'prsncvls';
if (in_array($filter_field, array('article_number', 'article_name', 'mass_density', 'product_type_id'))) {
$db_table_abbr = 'skuprdcts';
}
switch ($filter_field) {
case 'created_by_user_id':
case 'last_modified_by_user_id':
case 'employee_id':
case 'presence_value_date':
case 'num_worked_hours':
case 'presence_value':
if (is_array($filter_value)) {
$tmp_filter_select_ar = array();
foreach ($filter_value as $tmp_key => $tmp_value) {
$tmp_filter_select_ar[':' . $filter_field . '_' . $tmp_key] = $tmp_value;
}
$where_sql[] = $db_table_abbr . '.' . $filter_field . ' IN (' . implode(', ', array_keys($tmp_filter_select_ar)) . ')';
$prepped_where_fields_values = array_merge($prepped_where_fields_values, $tmp_filter_select_ar);
} else {
$where_sql[] = $db_table_abbr . '.' . $filter_field . ' = :' . $filter_field;
$prepped_where_fields_values[':' . $filter_field] = $filter_value;
}
break;
}
}
// check for month/year period filter
if (!empty($filter_ar['month']) && !empty($filter_ar['year'])) {
$where_sql[] = '(prsncvls.presence_value_date <= :check_period_end AND prsncvls.presence_value_date >= :check_period_start)';
$check_period_start_int = mktime(0, 0, 0, $filter_ar['month'], 1, $filter_ar['year']);
$prepped_where_fields_values[':check_period_start'] = date('Y-m-d', $check_period_start_int);
$prepped_where_fields_values[':check_period_end'] = date('Y-m-d', TS_Format_Dates::lastDayOfMonth($check_period_start_int));
}
// Build SQL
$sql_ar[] = 'SELECT DISTINCT prsncvls.id';
$sql_ar[] = 'FROM ' . TS_DB_HRM_TBNames::$employee_presence_values . ' prsncvls';
if ($where_sql) {
$sql_ar[] = 'WHERE ' . implode(' AND ', $where_sql);
}
// Get query result
return DB::fetchAllColumn(implode(' ', $sql_ar), $prepped_where_fields_values);
}