public function syncBalances()
{
$app = Prado::getApplication();
$db = $app->getModule('horuxDb')->DbConnection;
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$db->Active = true;
$sql = "SELECT id FROM hr_user WHERE name!='??' AND firstname!='??' AND isBlocked=0";
$cmd = $db->createCommand($sql);
$data = $cmd->query();
$data = $data->readAll();
$val = "";
foreach ($data as $data) {
$val .= $data['id'] . "/";
$employee = new employee($data['id']);
$overTimeLastMonth = $employee->getOvertimeLastMonth(date('n'), date('Y'));
$overTimeMonth = 0;
for ($day = 1; $day < date('j'); $day++) {
$todo = $employee->getDayTodo($day, date('n'), date('Y'));
$done = $employee->getDayDone($day, date('n'), date('Y'));
$overTimeMonth = bcadd($overTimeMonth, bcsub($done['done'], $todo, 4), 4);
}
//get the info the current day only if the overtime +
$todo = $employee->getDayTodo(date('j'), date('n'), date('Y'));
$done = $employee->getDayDone(date('j'), date('n'), date('Y'));
if (bcsub($done['done'], $todo, 4) > 0) {
$overTimeMonth = bcadd($overTimeMonth, bcsub($done['done'], $todo, 4), 4);
}
$overtime = bcadd($overTimeLastMonth, $overTimeMonth, 4);
$val .= sprintf("%.02f", $overtime) . "/";
$lastYear = $employee->geHolidaystMonth(date('Y') - 1, 12);
$nvy = $employee->geHolidaystMonth(date('Y'), date('n'));
for ($month = 1; $month < date('n'); $month++) {
$nv = $employee->getRequest(date('Y'), $month, $employee->getDefaultHolidaysCounter());
$nvy -= $nv['nbre'];
}
$nvy = bcsub($nvy, $lastYear, 4);
$holidays = $employee->getRequest(date('Y'), date('n'), $defH);
$holidaysLastMonth = $nvy + $lastYear;
$holidaysCurrentMonth = bcsub($holidaysLastMonth, $holidays['nbre'], 2);
$val .= sprintf("%.02f", $holidaysCurrentMonth);
$val .= ";";
}
return $val;
}
public function getData($isPrint = false)
{
$param = Prado::getApplication()->getParameters();
$computation2 = $param['computation2'];
if ($computation2 != '') {
Prado::using('horux.pages.components.timuxuser.' . $computation2);
}
$userList = $this->getEmployeeList();
$filterEmployee = $this->FilterEmployee->getSelectedValue();
$filterLoad = $this->FilterLoad->getSelectedValue();
if ($filterEmployee > 0) {
$employee = new employee($filterEmployee);
$userList = array();
$userList[] = array("Value" => $filterEmployee, "Text" => $employee->getFullName());
}
$year = $this->FilterYear->getSelectedValue();
$month = $this->FilterMonth->getSelectedValue();
$from = $year . '-' . $month . '-1';
$until = $year . '-' . $month . '-' . date("t", mktime(0, 0, 0, $month, 1, $year));
$date = " t.date>='{$from}' AND t.date<='{$until}' AND ";
$timeCodeList = array();
$hourMonthTodo = array();
$hourly = array();
$hoursByDay = array();
foreach ($userList as $user) {
if ($user['Value'] > 0) {
$employee = new employee($user['Value']);
$hourMonthTodo[$user['Value']] = $employee->getHoursMonthTodo($month, $year);
$hourly[$user['Value']] = $employee->getHourly($month, $year);
$hoursByDay[$user['Value']] = $employee->getHoursByDay($month, $year);
$cmd = $this->db->createCommand("SELECT t.id_user, t.id, t.date, tb.roundBooking AS time, tb.action, tb.actionReason, tb.internet, tbb.BDE1\r\n FROM hr_tracking AS t\r\n LEFT JOIN hr_timux_booking AS tb ON tb.tracking_id=t.id\r\n LEFT JOIN hr_timux_booking_bde AS tbb ON tbb.tracking_id=t.id\r\n WHERE {$date} t.id_user={$user['Value']} AND tb.action!='NULL' GROUP BY t.id ORDER BY t.date ASC , t.time ASC, tb.action ASC");
$data = $cmd->query();
$data = $data->readAll();
$nextBookingType = 'IN';
$type = '';
$timeCode = '';
foreach ($data as $d) {
$type = $this->isBookingIn($d) ? 'IN' : 'OUT';
if ($type == $nextBookingType) {
if ($type == 'IN') {
$bookinIN = $d['time'];
$timeCode = $d['BDE1'];
} else {
if ($d['action'] == '100') {
$dateTodo = explode("-", $d['date']);
$ddone = $employee->getDayDone($dateTodo[2], $dateTodo[1], $dateTodo[0]);
$timeCodeList[$ddone['timecodeId']]['total'] = bcadd($timeCodeList[$ddone['timecodeId']]['total'], $ddone['compensation'], 4);
$timeCodeList[$ddone['timecodeId']][$user['Text']]['total'] = bcadd($timeCodeList[$ddone['timecodeId']][$user['Text']]['total'], $ddone['compensation'], 4);
$timeCodeList[$ddone['timecodeId']][$user['Text']]['hourDayTodo'] = $hoursByDay[$user['Value']];
$timeCodeList[$ddone['timecodeId']][$user['Text']]['hourly'] = $hourly[$user['Value']];
$timeCodeList[$ddone['timecodeId']][$user['Text']]['id'] = $user['Value'];
$timeCodeList[$ddone['timecodeId']][$user['Text']]['hourMonthTodo'] = $hourMonthTodo[$user['Value']];
}
$t = bcdiv(strtotime($d['time']) - strtotime($bookinIN), 3600, 4);
$timeCodeList[$timeCode][$user['Text']]['total'] = bcadd($timeCodeList[$timeCode][$user['Text']]['total'], $t, 4);
$timeCodeList[$timeCode][$user['Text']]['hourDayTodo'] = $hoursByDay[$user['Value']];
$timeCodeList[$timeCode][$user['Text']]['hourly'] = $hourly[$user['Value']];
$timeCodeList[$timeCode][$user['Text']]['id'] = $user['Value'];
$timeCodeList[$timeCode][$user['Text']]['hourMonthTodo'] = $hourMonthTodo[$user['Value']];
$timeCodeList[$timeCode]['total'] = bcadd($timeCodeList[$timeCode]['total'], $t, 4);
$bookinIN = 0;
$timeCode = '';
}
$nextBookingType = $nextBookingType == 'IN' ? 'OUT' : 'IN';
} else {
$bookinIN = 0;
$timeCode = '';
$nextBookingType = 'IN';
}
}
}
}
foreach ($userList as $user) {
if ($user['Value'] > 0) {
$employee = new employee($user['Value']);
$dtcH = $employee->getDefaultHolidaysCounter();
$dtcO = $employee->getDefaultOvertimeCounter();
$h = $employee->getRequest($year, $month, $dtcH);
if ($h['nbre'] > 0) {
if ($h['disp'] == 'day') {
$timeCodeList[$dtcH]['total'] = bcadd($timeCodeList[$dtcH]['total'], bcmul($h['nbre'], $hoursByDay[$user['Value']], 4), 4);
$timeCodeList[$dtcH][$user['Text']]['total'] = bcadd($timeCodeList[$dtcH][$user['Text']]['total'], bcmul($h['nbre'], $hoursByDay[$user['Value']], 4), 4);
$timeCodeList[$dtcH][$user['Text']]['hourDayTodo'] = $hoursByDay[$user['Value']];
$timeCodeList[$dtcH][$user['Text']]['hourly'] = $hourly[$user['Value']];
$timeCodeList[$dtcH][$user['Text']]['id'] = $user['Value'];
$timeCodeList[$dtcH][$user['Text']]['hourMonthTodo'] = $hourMonthTodo[$user['Value']];
} else {
$timeCodeList[$dtcH]['total'] += bcadd($timeCodeList[$dtcH]['total'], $h['nbre'], 4);
$timeCodeList[$dtcH][$user['Text']]['total'] = bcadd($timeCodeList[$dtcH][$user['Text']]['total'], $h['nbre'], 4);
$timeCodeList[$dtcH][$user['Text']]['hourDayTodo'] = $hoursByDay[$user['Value']];
$timeCodeList[$dtcH][$user['Text']]['hourly'] = $hourly[$user['Value']];
$timeCodeList[$dtcH][$user['Text']]['id'] = $user['Value'];
$timeCodeList[$dtcH][$user['Text']]['hourMonthTodo'] = $hourMonthTodo[$user['Value']];
}
}
$cmd = $this->db->createCommand("SELECT * FROM hr_timux_timecode WHERE id NOT IN ({$dtcH},{$dtcO}) AND type!='load'");
$data = $cmd->query();
$data = $data->readAll();
foreach ($data as $d) {
$h = $employee->getRequest($year, $month, $d['id']);
if ($h['nbre'] > 0) {
if ($h['disp'] == 'day') {
$timeCodeList[$d['id']]['total'] = bcadd($timeCodeList[$d['id']]['total'], bcmul($h['nbre'], $hoursByDay[$user['Value']], 4), 4);
$timeCodeList[$d['id']][$user['Text']]['total'] = bcadd($timeCodeList[$d['id']][$user['Text']]['total'], bcmul($h['nbre'], $hoursByDay[$user['Value']], 4), 4);
$timeCodeList[$d['id']][$user['Text']]['hourDayTodo'] = $hoursByDay[$user['Value']];
$timeCodeList[$d['id']][$user['Text']]['hourly'] = $hourly[$user['Value']];
$timeCodeList[$d['id']][$user['Text']]['id'] = $user['Value'];
$timeCodeList[$d['id']][$user['Text']]['hourMonthTodo'] = $hourMonthTodo[$user['Value']];
} else {
$timeCodeList[$d['id']]['total'] = bcadd($timeCodeList[$d['id']]['total'], $h['nbre'], 4);
$timeCodeList[$d['id']][$user['Text']]['total'] = bcadd($timeCodeList[$d['id']][$user['Text']]['total'], $h['nbre'], 4);
$timeCodeList[$d['id']][$user['Text']]['hourDayTodo'] = $hoursByDay[$user['Value']];
$timeCodeList[$d['id']][$user['Text']]['hourly'] = $hourly[$user['Value']];
$timeCodeList[$d['id']][$user['Text']]['id'] = $user['Value'];
$timeCodeList[$d['id']][$user['Text']]['hourMonthTodo'] = $hourMonthTodo[$user['Value']];
}
}
}
for ($i = 1; $i < date('t', mktime(0, 0, 0, $month, 1, $year)); $i++) {
$nwd = $employee->getNonWorkingDay($year, $month, $i);
if ($nwd > 0 && $employee->isWorking($year, $month, $i)) {
$timeCodeList[Prado::localize('Non working day')]['total'] = bcadd($timeCodeList[Prado::localize('Non working day')]['total'], bcmul($nwd, $hoursByDay[$user['Value']], 4), 4);
$timeCodeList[Prado::localize('Non working day')][$user['Text']]['total'] = bcadd($timeCodeList[Prado::localize('Non working day')][$user['Text']]['total'], bcmul($nwd, $hoursByDay[$user['Value']], 4), 4);
$timeCodeList[Prado::localize('Non working day')][$user['Text']]['hourDayTodo'] = $hoursByDay[$user['Value']];
$timeCodeList[Prado::localize('Non working day')][$user['Text']]['hourly'] = $hourly[$user['Value']];
$timeCodeList[Prado::localize('Non working day')][$user['Text']]['id'] = $user['Value'];
$timeCodeList[Prado::localize('Non working day')][$user['Text']]['hourMonthTodo'] = $hourMonthTodo[$user['Value']];
}
}
$employee = new employee($user['Value']);
for ($i = 1; $i <= date("t", mktime(0, 0, 0, $month, 1, $year)); $i++) {
$b = $employee->getDayDone($i, $month, $year);
$todo = $employee->getDayTodo($i, $month, $year);
$overtime = bcsub($b['done'], $todo, 4);
$overtime = bcmul($overtime, -1, 4);
if ($overtime != 0) {
$timeCodeList[Prado::localize('Overtime2')][$user['Text']]['total'] = bcadd($timeCodeList[Prado::localize('Overtime2')][$user['Text']]['total'], $overtime, 4);
$timeCodeList[Prado::localize('Overtime2')][$user['Text']]['hourDayTodo'] = $hoursByDay[$user['Value']];
$timeCodeList[Prado::localize('Overtime2')]['total'] = bcadd($timeCodeList[Prado::localize('Overtime2')]['total'], $overtime, 4);
$timeCodeList[Prado::localize('Overtime2')][$user['Text']]['hourly'] = $hourly[$user['Value']];
$timeCodeList[Prado::localize('Overtime2')][$user['Text']]['id'] = $user['Value'];
$timeCodeList[Prado::localize('Overtime2')][$user['Text']]['hourMonthTodo'] = $hourMonthTodo[$user['Value']];
}
}
}
}
$cmd = $this->db->createCommand("SELECT * FROM hr_timux_config");
$query = $cmd->query();
$data = $query->read();
$hoursByDay = bcdiv($data['hoursByWeek'], $data['daysByWeek'], 4);
$cmd = $this->db->createCommand("SELECT * FROM hr_site");
$query = $cmd->query();
$data = $query->read();
$devise = " " . $data['devise'];
$res = array();
$extendHourly = false;
if (class_exists($computation2)) {
$extendHourly = new $computation2();
}
foreach ($timeCodeList as $k => $v) {
$totalCost = 0;
if (is_array($v)) {
foreach ($v as $k2 => $u) {
if (is_array($u)) {
if ($extendHourly) {
$u['hourly'] = $extendHourly->getHourly($month, $year, $u);
}
$totalCost = bcadd($totalCost, bcmul($u['hourly'], round($u['total'], 2), 2), 2);
}
}
}
if ($k != '') {
$cmd = $this->db->createCommand("SELECT * FROM hr_timux_timecode WHERE abbreviation=:abb || id=:abb");
$cmd->bindValue(":abb", $k);
$data = $cmd->query();
$data = $data->read();
if ($filterLoad != 0 && $data['id'] != $filterLoad) {
continue;
}
if ($data) {
$r['timecode'] = '<b>' . $data['name'] . '</b>';
//how the time code is computed
$r['hoursdone'] = '<b>' . round($v['total'], 2) . '</b>';
$r['cost'] = sprintf("<b>%.02f {$devise}</b>", $totalCost);
$r['hourly'] = '';
$r['id'] = '';
$res[] = $r;
if ($this->showUser->getChecked()) {
foreach ($v as $k => $u) {
if ($u['hourDayTodo'] > 0) {
$hoursByDay2 = $u['hourDayTodo'];
} else {
$hoursByDay2 = $hoursByDay;
}
if ($k != 'total') {
if ($extendHourly) {
$u['hourly'] = $extendHourly->getHourly($month, $year, $u);
}
$r['timecode'] = " " . $k;
$r['hoursdone'] = round($u['total'], 2);
$r['cost'] = sprintf("%.02f {$devise}", bcmul($u['hourly'], round($u['total'], 2), 2));
$r['hourly'] = sprintf("%.02f {$devise}", $u['hourly']);
$r['id'] = $u['id'];
$res[] = $r;
}
}
}
} else {
$r['timecode'] = '<b>' . $k . '</b>';
$r['hoursdone'] = '<b>' . round($v['total'], 2) . '</b>';
$r['cost'] = sprintf("<b>%.02f {$devise}</b>", $totalCost);
$r['hourly'] = '';
$r['id'] = '';
$res[] = $r;
if ($this->showUser->getChecked()) {
foreach ($v as $k => $u) {
if ($u['hourDayTodo'] > 0) {
$hoursByDay2 = $u['hourDayTodo'];
} else {
$hoursByDay2 = $hoursByDay;
}
if ($k != 'total') {
if ($extendHourly) {
$u['hourly'] = $extendHourly->getHourly($month, $year, $u);
}
$r['timecode'] = " " . $k;
$r['hoursdone'] = round($u['total'], 2);
$r['cost'] = sprintf("%.02f {$devise}", bcmul($u['hourly'], round($u['total'], 2), 2));
$r['hourly'] = sprintf("%.02f {$devise}", $u['hourly']);
$r['id'] = $u['id'];
$res[] = $r;
}
}
}
}
} else {
$r['timecode'] = '<b>' . Prado::localize('Unkown time code') . '</b>';
$r['hoursdone'] = '<b>' . round($v['total'], 2) . '</b>';
$r['cost'] = sprintf("<b>%.02f {$devise}</b>", $totalCost);
$r['hourly'] = '';
$r['id'] = '';
$res[] = $r;
if ($this->showUser->getChecked()) {
foreach ($v as $k => $u) {
if ($u['hourDayTodo'] > 0) {
$hoursByDay2 = $u['hourDayTodo'];
} else {
$hoursByDay2 = $hoursByDay;
}
if ($k != 'total') {
if ($extendHourly) {
$u['hourly'] = $extendHourly->getHourly($month, $year, $u);
}
$r['timecode'] = " " . $k;
$r['hoursdone'] = round($u['total'], 2);
$r['cost'] = sprintf("%.02f {$devise}", bcmul($u['hourly'], round($u['total'], 2), 2));
$r['hourly'] = sprintf("%.02f {$devise}", $u['hourly']);
$r['id'] = $u['id'];
$res[] = $r;
}
}
}
}
}
return $res;
}
