function getListByQuery($query, $department, $rtype = '')
{
$sDeptCondition = '';
$sCondition = '';
$sYear = getSchoolYear($this);
if (getRole($this) == 2) {
$sDept = $this->mod->getDeptOfEmployee(getUserId($this));
$sDept = $sDept[0]->department_id;
$sDeptCondition = "employees.department_id = {$sDept} and";
}
$sDeptCondition = '';
$rCondition = '';
if (getRole($this) != 2 && ($department != -1 && !empty($department))) {
$sDeptCondition = "(employees.department_id = {$department}) and";
}
if (!empty($query)) {
$sCondition = "(employees.empno like '%{$query}%' or employees.fname like '%{$query}%' or employees.lname like '%{$query}%') and";
}
$iProfileId = getUserId($this);
if (getRole($this) == 2) {
$sdep = $this->mod->getDeptOfEmployee($iProfileId);
$sDeptCondition = "(employees.department_id = {$sdep[0]->department_id}) and ";
}
if (!empty($rtype)) {
if (strpos($rtype, 'nal') !== false) {
$rCondition = "and if(emp_types.time_unit = 'd', \r\n\r\n (select (sum(abs(datediff(g.end_date, g.start_date)) - abs(g.end_date_hw + g.start_date_hw)) +1)\r\n from \r\n leave_transactions g, leave_credits \r\n where \r\n\r\n employment_id = employment.id and \r\n leave_credits.id = leave_credits_id and \r\n leave_credits.is_base = 1 and g.schoolyear = '{$sYear}'),\r\n\r\n\r\n\r\n\r\n round(( select (sum(timediff(g.end_time,g.start_time)))/10000 from \r\n leave_transactions g \r\n where \r\n\r\n g.employment_id = employment.id and g.schoolyear = '{$sYear}'\r\n\r\n ),2)) is null";
}
}
$query = $this->db->query("\r\n select \r\n employees.id as myid, \r\n employment.*, \r\n employees.*,\r\n ranks.*,\r\n roles.*,\r\n departments.*,\r\n emp_types.*,\r\n status.*,\r\n emp_types.time_unit as timeunit\r\n from \r\n employment, \r\n emp_types, \r\n status, \r\n employees, \r\n emp_types_stat,\r\n ranks,\r\n departments,\r\n roles\r\n where \r\n ({$sCondition} {$sDeptCondition} \r\n employment.emp_types_stat_id = emp_types_stat.id and\r\n \r\n emp_types.id = emp_types_stat.emp_types_id and \r\n \r\n status.id = emp_types_stat.status_id and \r\n \r\n employment.employee_id = employees.id and\r\n \r\n employees.rank_id = ranks.id and\r\n \r\n employees.department_id = departments.id and\r\n \r\n employees.role_id = roles.id) {$rCondition} \r\n\r\n group by employees.department_id, lname, fname\r\n ");
if ($query->num_rows() > 0) {
return $query->result();
} else {
return "";
}
}
function getlist_json_employee($employee, $bool)
{
$list = $this->base_model->getLeaveCreditsByEmployee($employee, $bool);
$date = $this->mod->getHireDateByEmployee(getUserId($this));
$arrayList = objectToArray($list);
$newArray = array();
foreach ($arrayList as $key => $value) {
$dayval = $value['days'];
$usercount = 0;
if ($value['date'] != '0000-00-00' && $value['date'] != '') {
$hired = strtotime($date);
$setdate = strtotime($value['date']);
if ($hired > $setdate) {
$dayval = $value['days_after'];
} else {
if ($hired < $setdate) {
$dayval = $value['days_before'];
}
}
}
if ($value['schoolyear'] === getSchoolYear($this)) {
$usercount = $value['usecount'];
}
array_push($newArray, array('id' => $value['id'], 'ltid' => $value['ltid'], 'title' => $value['title'], 'days' => $dayval, 'date' => $value['date'], 'days_before' => $value['days_before'], 'days_after' => $value['days_after'], 'usecount' => $usercount));
}
echo json_encode($newArray);
}
function getLeaveCreditsByEmployee($employee_id)
{
$sYear = getSchoolYear($this);
$query = $this->db->query("\r\n select \r\n leave_credits.id as id,\r\n leave_types.id as ltid,\r\n leave_types.title,\r\n leave_credits.date,\r\n leave_credits.days_before,\r\n leave_credits.days_after,\r\n leave_credits.days,\r\n (select \r\n sum((datediff(g.end_date,g.start_date))+(g.end_date_hw+g.start_date_hw)+1)\r\n from \r\n leave_transactions g \r\n where \r\n leave_credits_id = leave_credits.id and \r\n status = 'Approved' and rtype = 'd' and \r\n g.employment_id = employment.id and \r\n employment.employee_id = {$employee_id} and g.schoolyear = '{$sYear}'\r\n\r\n ) as usecount,\r\n (select \r\n g.schoolyear\r\n from \r\n leave_transactions g \r\n where \r\n leave_credits_id = leave_credits.id and \r\n status = 'Approved' and rtype = 'd' and \r\n g.employment_id = employment.id and \r\n employment.employee_id = {$employee_id} and g.schoolyear = '{$sYear}' limit 1\r\n\r\n ) as schoolyear \r\n from\r\n leave_types,\r\n leave_credits,\r\n employment\r\n where\r\n (leave_credits.emp_types_stat_id = employment.emp_types_stat_id and\r\n employment.employee_id = {$employee_id} and\r\n leave_credits.leave_types_id = leave_types.id)");
if ($query->num_rows() > 0) {
return $query->result();
} else {
return false;
}
}
function getLeaveBalanceOfEmployee($employee_id)
{
$emp = $employee_id;
$tu = getEmployeeTimeUnit($this);
$tunit = $tu;
$val = 0;
$sCondition = '';
if (getEmployeeTimeUnit($this) == 'd') {
$sCondition = "and is_base = true";
}
$sYear = getSchoolYear($this);
if ($tunit == 'h') {
$query = $this->db->query("select employment.units as units from employment\r\n where employment.employee_id = {$employee_id}");
$query2 = $this->db->query("select l.end_time,l.start_time from leave_transactions l, employment\r\n where employment.employee_id = {$employee_id} and l.employment_id = employment.id and l.status = 'Approved' and l.schoolyear = '{$sYear}'");
$unit = $query->result();
$val = $unit[0]->units;
if ($query2->num_rows() > 0) {
$units = $unit[0]->units;
$total = 0;
$res = objectToArray($query2->result());
$diff = 0;
$total = 0;
foreach ($res as $key => $value) {
if (!empty($value['td'])) {
$parsed = date_parse($value['td']);
}
$time1 = strtotime($value['start_time']);
$time2 = strtotime($value['end_time']);
$diff = $time2 - $time1;
$total = $total + $diff;
}
$val = $units * 3600 - $total;
$val = $val / 3600;
}
return array('value' => $val, 'unit' => ' h');
} else {
if ($tunit == 'd') {
if (!empty($employee_id)) {
$employee_id = "and e.id = {$employee_id}";
}
$query = $this->db->query("\r\n select \r\n sum(\r\n (datediff(g.end_date,g.start_date)) + (g.end_date_hw + g.start_date_hw)+ 1\r\n ) as days\r\n from \r\n leave_transactions g, \r\n employees e,\r\n employment em,\r\n leave_credits lc\r\n where (\r\n g.employment_id = em.id and \r\n em.employee_id = e.id \r\n {$employee_id} and \r\n g.status = 'Approved' and \r\n g.leave_credits_id = lc.id and \r\n lc.is_base = 1) and \r\n g.schoolyear = '{$sYear}'");
$days = $query->result();
$est = $this->getEmpTypesStatByEmployee($emp);
$q = $this->db->query("select lc.days \r\n from leave_credits lc, employment e where e.employee_id = {$emp} and lc.emp_types_stat_id = {$est[0]->id} {$sCondition}");
$value = $q->result();
return array('value' => $value[0]->days - $days[0]->days, 'unit' => ' d');
}
}
}
function getlist()
{
$sEmployeeId = $_REQUEST['eid'];
$sDept = $_REQUEST['did'];
$sStat = $_REQUEST['stat'];
$list = $this->basemodel->getQueryList($sEmployeeId, $sDept, $sStat);
$arrayList = objectToArray($list);
$newArray = array();
foreach ($arrayList as $key => $value) {
if ($value['schoolyear'] !== getSchoolYear($this)) {
continue;
}
$leave = "Regular Leave";
if (!empty($value['leave_credits_id'])) {
$leave = $this->mod->getLeaveTypeTitleByCreditId($value['leave_credits_id']);
}
$resumption = "N/S";
if (strpos($value['resumption_date'], "0000-00-00") === false and !empty($value['resumption_date'])) {
$resumption = $value['resumption_date'];
}
if ($value['start_date'] != null && $value['end_date'] != null && isset($_REQUEST['date'])) {
$setDate = $_REQUEST['date'];
$setDate = strtotime($_REQUEST['date']);
$contractDateBegin = strtotime($value['start_date']);
$contractDateEnd = strtotime($value['end_date']);
if (!($setDate >= $contractDateBegin && $setDate <= $contractDateEnd)) {
$diff = 0;
$addon = 0;
if ($value['rtype'] === 'h') {
$from = strtotime($value['start_time']);
$to = strtotime($value['end_time']);
$diff = $to - $from;
$from = date("h:i A", $from);
$to = date("h:i A", $to);
$diff = $diff / 3600;
$diff = $diff . ' h';
} else {
$from = strtotime($value['start_date']);
$to = strtotime($value['end_date']);
$diff = $to - $from;
$from = $value['start_date'];
$to = $value['end_date'];
if ($value['start_date_hw'] > 0) {
$from = $from . ' (hd)';
$addon = $addon + 0.5;
} else {
if ($value['end_date_hw'] > 0) {
$to = $to . ' (hd)';
$addon = $addon + 0.5;
}
}
$diff = $diff / 60 / 60 / 24 + 1;
$diff = $diff + $addon;
$diff = $diff . ' d';
}
array_push($newArray, array('id' => $value['id'], 'filing_date' => $value['date'] ? $value['date'] : $value['filing_date'], 'employee' => $value['employee'], 'status' => $value['status'], 'from' => $from, 'to' => $to, 'diff' => $diff, 'from_type' => $value['start_date_hw'] ? $value['start_date_hw'] : '', 'to_type' => $value['end_date_hw'] ? $value['end_date_hw'] : '', 'resumption' => $resumption, 'reason' => $value['notes'], 'leave_title' => $leave, 'leave_hours' => $value['leaves'], 'deny_reason' => $value['deny_reason']));
}
} else {
if (!isset($_REQUEST['date'])) {
$diff = 0;
$addon = 0;
if ($value['rtype'] === 'h') {
$from = strtotime($value['start_time']);
$to = strtotime($value['end_time']);
$diff = $to - $from;
$from = date("h:i A", $from);
$to = date("h:i A", $to);
$diff = $diff / 3600;
$diff = $diff . ' h';
} else {
$from = strtotime($value['start_date']);
$to = strtotime($value['end_date']);
$diff = $to - $from;
$from = $value['start_date'];
$to = $value['end_date'];
if ($value['start_date_hw'] > 0) {
$from = $from . ' (hd)';
$addon = $addon + 0.5;
} else {
if ($value['end_date_hw'] > 0) {
$to = $to . ' (hd)';
$addon = $addon + 0.5;
}
}
$diff = $diff / 60 / 60 / 24 + 1;
$diff = $diff + $addon;
$diff = $diff . ' d';
}
array_push($newArray, array('id' => $value['id'], 'filing_date' => $value['date'] ? $value['date'] : $value['filing_date'], 'employee' => $value['employee'], 'status' => $value['status'], 'from' => $from, 'to' => $to, 'diff' => $diff, 'from_type' => $value['start_date_hw'] ? $value['start_date_hw'] : '', 'to_type' => $value['end_date_hw'] ? $value['end_date_hw'] : '', 'resumption' => $resumption, 'reason' => $value['notes'], 'leave_title' => $leave, 'leave_hours' => $value['leaves'], 'deny_reason' => $value['deny_reason']));
}
}
}
echo json_encode($newArray);
}
function createUpdate($aDetails)
{
if ($aDetails['rtype'] == 'd') {
$data = array('employment_id' => $aDetails['employment_id'], 'leave_credits_id' => $aDetails['leave_credits_id'], 'start_date' => $aDetails['start_date'], 'start_date_hw' => $aDetails['start_date_hw'], 'end_date' => $aDetails['end_date'], 'end_date_hw' => $aDetails['end_date_hw'], 'filing_date' => date('Y-m-d'), 'notes' => $aDetails['notes'], 'status' => 'Pending', 'resumption_date' => $aDetails['resume'], 'rtype' => $aDetails['rtype'], 'status' => 'Pending', 'schoolyear' => getSchoolYear($this));
}
if ($aDetails['rtype'] == 'h') {
$data = array('employment_id' => $aDetails['employment_id'], 'leave_credits_id' => $aDetails['leave_credits_id'], 'date' => $aDetails['date'], 'start_time' => date('H:i', strtotime($aDetails['from'])), 'end_time' => date('H:i', strtotime($aDetails['to'])), 'filing_date' => date('Y-m-d'), 'notes' => $aDetails['notes'], 'rtype' => $aDetails['rtype'], 'status' => 'Pending', 'schoolyear' => getSchoolYear($this));
}
if (!empty($aDetails['id'])) {
$this->db->where('id', $aDetails['id']);
$this->db->update('leave_transactions', $data);
} else {
$this->db->insert('leave_transactions', $data);
}
return true;
}
