public function processPart2($old_fields)
{
// set all fields
$lr = new leaveregistration('civicrm', 'upgrade_2000_processPart2');
$lr->set_fields();
// get all request with leave type mom_dad_day, study_leave and care
$query = "SELECT * FROM " . $lr->custom_groups['leave_request']['table_name'];
$query .= " WHERE " . $lr->custom_fields['leave_request_leave_type']['column_name'] . " = 'mom_dad_day' ";
$query .= " OR " . $lr->custom_fields['leave_request_leave_type']['column_name'] . " = 'mom_dad_day_contiguous' ";
$query .= " OR " . $lr->custom_fields['leave_request_leave_type']['column_name'] . " = 'study_leave' ";
$query .= " OR " . $lr->custom_fields['leave_request_leave_type']['column_name'] . " = 'care' ";
$dao = CRM_Core_DAO::executeQuery($query);
while ($dao->fetch()) {
// copy all data
$query = "UPDATE " . $lr->custom_groups['leave_request']['table_name'];
$query .= " SET";
switch ($dao->{$lr->custom_fields['leave_request_leave_type']['column_name']}) {
case 'mom_dad_day_contiguous':
$query .= " " . $lr->custom_fields['leave_request_leave_type']['column_name'] . " = 'mom_dad_day'";
$query .= ", " . $lr->custom_fields['leave_request_daily_weekly_monthly_annually']['column_name'] . " = 'daily'";
$query .= ", " . $lr->custom_fields['leave_request_daily_each']['column_name'] . " = '1'";
$query .= ", " . $lr->custom_fields['leave_request_daily_every_day_working_day']['column_name'] . " = 'working_day'";
break;
case 'mom_dad_day':
case 'study_leave':
case 'care':
switch ($dao->{$old_fields['leave_request_day_week_month_year']['column_name']}) {
case 'day':
$query .= " " . $lr->custom_fields['leave_request_daily_weekly_monthly_annually']['column_name'] . " = 'daily'";
$query .= ", " . $lr->custom_fields['leave_request_daily_each']['column_name'] . " = '" . $dao->{$old_fields['leave_request_each']['column_name']} . "'";
break;
case 'week':
$query .= " " . $lr->custom_fields['leave_request_daily_weekly_monthly_annually']['column_name'] . " = 'weekly'";
$query .= ", " . $lr->custom_fields['leave_request_weekly_each']['column_name'] . " = '" . $dao->{$old_fields['leave_request_each']['column_name']} . "'";
if ('nvt' != $dao->{$old_fields['leave_request_day_of_the_week']['column_name']}) {
$query .= ", " . $lr->custom_fields['leave_request_weekly_day_of_the_week']['column_name'] . " = '" . strtolower($dao->{$old_fields['leave_request_day_of_the_week']['column_name']}) . "'";
}
break;
case 'month':
$query .= " " . $lr->custom_fields['leave_request_daily_weekly_monthly_annually']['column_name'] . " = 'monthly'";
$query .= ", " . $lr->custom_fields['leave_request_monthly_each']['column_name'] . " = '" . $dao->{$old_fields['leave_request_each']['column_name']} . "'";
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
// get day number of the month
$day = date('d', strtotime($lr->custom_fields['leave_request_from_date']['column_name']));
$query .= ", " . $lr->custom_fields['leave_request_monthly_every_day_of_the_month_day_of_the_week']['column_name'] . " = 'every_day'";
$query .= ", " . $lr->custom_fields['leave_request_monthly_day_of_the_month']['column_name'] . " = '" . $day . "'";
break;
default:
// check if it is the first, second, third, fourth or last day of the month
}
break;
case 'next':
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
break;
default:
}
break;
case 'previous':
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
break;
default:
}
break;
case 'first':
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
break;
default:
}
break;
case 'last':
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
break;
default:
}
break;
}
break;
case 'year':
$query .= " " . $this->custom_fields['leave_request_daily_weekly_monthly_annually']['column_name'] . " = 'annually'";
$query .= ", " . $this->custom_fields['leave_request_annually_each']['column_name'] . " = '" . $dao->{$old_fields['leave_request_each']['column_name']} . "'";
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
break;
default:
}
break;
case 'next':
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
break;
default:
}
break;
case 'previous':
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
break;
default:
}
break;
case 'first':
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
break;
default:
}
break;
case 'last':
switch ($dao->{$old_fields['leave_request_on']['column_name']}) {
case 'nvt':
break;
default:
}
break;
}
break;
}
break;
}
$query .= " WHERE id = '" . $dao->id . "' ";
CRM_Core_DAO::executeQuery($query);
}
return TRUE;
}
function buildRows($sql, &$rows)
{
// set days, months and years to empty
$days = array();
$months = array();
$years = array();
$year = $this->_formValues['year_value'];
$week = $this->_formValues['week_value'];
switch ($this->_formValues['period_value']) {
case 'year':
$years = [$this->_formValues['year_value']];
$months = [];
$first_day = date('Y-m-d', strtotime($this->_formValues['year_value'] . '-01-01'));
$last_day = date('Y-m-d', strtotime($this->_formValues['year_value'] . '-12-31'));
break;
case 'month':
$years = [$this->_formValues['year_value']];
$months = [$this->_formValues['month_value']];
$first_day = date('Y-m-d', strtotime($this->_formValues['year_value'] . '-' . $this->_formValues['month_value'] . '-01'));
$last_day = date('Y-m-t', strtotime($this->_formValues['year_value'] . '-' . $this->_formValues['month_value'] . '-02'));
break;
case 'week':
$dayrange = array(1, 2, 3, 4, 5, 6, 7);
// calculate the days in the week
for ($count = 0; $count <= 6; $count++) {
$week = $count == 7 ? $week + 1 : $week;
$week = str_pad($week, 2, '0', STR_PAD_LEFT);
$days[] = date('Y-m-d', strtotime($year . "W" . $week . $dayrange[$count]));
}
// calculate months and years from date
// calculate years from days
foreach ($days as $date) {
$year = date('Y', strtotime($date));
$years[$year] = $year;
$month = date('m', strtotime($date));
$months[$month] = $month;
}
$first_day = date('Y-m-d', strtotime($days[0]));
$last_day = date('Y-m-d', strtotime(end($days)));
break;
}
$start_date_totime = strtotime($first_day);
$end_date_totime = strtotime($last_day);
// create a array with all the employees, add department en business to the array, this
$datas = [];
$cids = [];
$dao = CRM_Core_DAO::executeQuery($sql);
while ($dao->fetch()) {
$data = [];
foreach ($this->_columnHeaders as $id => $columnheader) {
$data[$id] = $dao->{$id};
}
$datas[$dao->civicrm_contact_id] = $data;
$cids[] = $dao->civicrm_contact_id;
}
$lr = new leaveregistration('civicrm', 'CRM_Leaveregistration_Form_Report_LeaveRegistration');
$lr->set_fields();
$lr->set_contacts($cids);
$lr->set_data($years, $months);
for ($timestamp = $start_date_totime; $timestamp <= $end_date_totime; $timestamp = strtotime('+1 day', $timestamp)) {
$day = date('d', $timestamp);
$month = date('m', $timestamp);
$year = date('Y', $timestamp);
switch ($this->_formValues['period_value']) {
case 'year':
$this->_columnHeaders[date('Y-m-d', $timestamp)] = array('title' => ts('Year') . ' ' . date('Y') . ' ' . date('m-d', $timestamp) . ' ' . ts(date('l', $timestamp)));
break;
case 'month':
$this->_columnHeaders[date('Y-m-d', $timestamp)] = array('title' => ts('Month') . ' ' . ts(date('F', $timestamp)) . ' ' . date('m-d', $timestamp) . ' ' . ts(date('l', $timestamp)));
break;
case 'week':
$this->_columnHeaders[date('Y-m-d', $timestamp)] = array('title' => ts('Week') . ' ' . date('W', $timestamp) . ' ' . date('m-d', $timestamp) . ' ' . ts(date('l', $timestamp)));
break;
}
}
$rows = [];
foreach ($datas as $cid => $data) {
$row = [];
// must before department and business
foreach ($this->_columnHeaders as $id => $columnheader) {
$row[$id] = $data[$id];
}
for ($timestamp = $start_date_totime; $timestamp <= $end_date_totime; $timestamp = strtotime('+1 day', $timestamp)) {
$day = date('d', $timestamp);
$month = date('m', $timestamp);
$year = date('Y', $timestamp);
// get the request from the contact, year, month and day
$request = $lr->data[$data['civicrm_contact_id']][$year][$month][$day];
// empty content
$content = [];
$leave = 0;
$paid_leave = 0;
$sick = 0;
$time_for_time = 0;
if (isset($request['adjustments']['duration']) and 0 == $request['adjustments']['duration']) {
$content[] = 'rv';
}
// switch between the leave types
// and add the duration to the right counter (work, time for time, leave or sick)
if (isset($request['request']['is_request']) and 1 == $request['request']['is_request'] and 'approved' == $request['request']['status']) {
switch ($request['request']['leave_type']) {
case 'mom_dad_day':
case 'mom_dad_day_contiguous':
case 'doctor_visit':
case 'study_leave':
case 'care':
case 'special_leave':
case 'maternity':
$paid_leave += $request['request']['duration'];
break;
case 'sick_less_one_day':
case 'sick':
$sick += $request['request']['duration'];
break;
}
}
// time_for_time
if (isset($request['time_for_time']['is_time_for_time']) and 1 == $request['time_for_time']['is_time_for_time'] and 'approved' == $request['time_for_time']['status']) {
$time_for_time += $request['time_for_time']['duration'];
}
// normal_leave
// leave type is normal_leave_less_one_day and normal_leave
if (isset($request['normal_leave']['is_normal_leave']) and 1 == $request['normal_leave']['is_normal_leave'] and 'approved' == $request['normal_leave']['status']) {
$leave += $request['normal_leave']['duration'];
}
// calculate the leave hours and the minutes from the duration (in minutes)
if ($leave > 0) {
$hours = floor($leave / 60);
$minutes = $leave - $hours * 60;
$content[] = 'v: ' . $hours . ':' . sprintf("%02s", $minutes);
}
if ($paid_leave > 0) {
$hours = floor($paid_leave / 60);
$minutes = $paid_leave - $hours * 60;
$content[] = 'bv: ' . $hours . ':' . sprintf("%02s", $minutes);
}
if ($sick > 0) {
$hours = floor($sick / 60);
$minutes = $sick - $hours * 60;
$content[] = 'z: ' . $hours . ':' . sprintf("%02s", $minutes);
}
if ($time_for_time > 0) {
$hours = floor($time_for_time / 60);
$minutes = $time_for_time - $hours * 60;
$content[] = 't: ' . $hours . ':' . sprintf("%02s", $minutes);
}
switch ($this->_formValues['period_value']) {
case 'year':
$row[date('Y-m-d', $timestamp)] = implode(' ', $content);
break;
case 'month':
$row[date('Y-m-d', $timestamp)] = implode(' ', $content);
break;
case 'week':
$row[date('Y-m-d', $timestamp)] = implode(' ', $content);
break;
}
}
$rows[] = $row;
}
}
public function get_calendar_month()
{
$error = '';
// cid
if (!isset($this->data['cid']) or empty($this->data['cid'])) {
$error = ts('Error in get_calendar_month, no cid !');
}
// user_cid
if (!isset($this->data['user_cid']) or empty($this->data['user_cid'])) {
$error = ts('Error in get_calendar_month, no user_cid !');
}
// year
if (!isset($this->data['year']) or empty($this->data['year'])) {
$error = ts('Error in get_calendar_month, no year !');
}
// month
if (!isset($this->data['month']) or empty($this->data['month'])) {
$error = ts('Error in get_calendar_month, no month !');
}
// user_id
if (!isset($this->data['user_id']) or empty($this->data['user_id'])) {
$error = ts('Error in get_calendar_month, no user_id !');
}
if (!empty($error)) {
$form = array('type' => 'error', 'error' => $error);
if ('form' == $this->data['type']) {
return $form;
} else {
return $this->response('calendar_month', $form, 'error', '');
}
}
$header = array();
$header[] = array('data' => ts(date('F', strtotime($this->data['year'] . '-' . $this->data['month'] . '-01'))));
foreach ($this->lr->data[$this->data['cid']][$this->data['year']][$this->data['month']] as $day => $array) {
$header[] = array('data' => $day);
}
unset($day);
unset($array);
// employee self
$datas = array();
$class = '';
$rel = array();
$datas[] = array('data' => $this->lr->employees[$this->data['cid']]['display_name']);
foreach ($this->lr->data[$this->data['cid']][$this->data['year']][$this->data['month']] as $day => $array) {
$class = '';
$rel = '';
list($data, $class, $rel) = $this->get_calendar_month_day($this->lr->employees, $this->data['cid'], $this->data['user_cid'], $this->data['user_id'], $day, $this->data['month'], $this->data['year'], $array);
$datas[] = array('data' => $data, 'class' => $class, 'rel' => rawurlencode(serialize($rel)));
}
unset($day);
unset($array);
unset($data);
unset($class);
unset($rel);
$rows[] = array('data' => $datas);
unset($datas);
// all the colleages
$colleages = array();
switch ($this->lr->settings[$this->data['cid']]['show_all_colleagues']) {
case 'business':
foreach ($this->lr->business_colleages_ids[$this->data['cid']] as $bid => $business) {
foreach ($business['employees'] as $cid => $employee) {
if ($cid != $this->data['cid']) {
$colleages[htmlentities($employee['display_name'])] = $employee;
}
}
}
unset($bid);
unset($business);
unset($cid);
unset($employee);
break;
/*case 'main_business':
foreach($this->lr->department_heads_colleages_ids[$this->data['user_cid']] as $did => $department){
foreach($department['employees'] as $cid => $employee){
if($cid != $this->data['user_cid']){
$colleages[htmlentities($employee['display_name'])] = $employee;
}
}
}
break;*/
/*case 'main_business':
foreach($this->lr->department_heads_colleages_ids[$this->data['user_cid']] as $did => $department){
foreach($department['employees'] as $cid => $employee){
if($cid != $this->data['user_cid']){
$colleages[htmlentities($employee['display_name'])] = $employee;
}
}
}
break;*/
default:
foreach ($this->lr->department_colleages_ids[$this->data['cid']] as $did => $department) {
foreach ($department['employees'] as $cid => $employee) {
if ($cid != $this->data['cid']) {
$colleages[htmlentities($employee['display_name'])] = $employee;
}
}
}
unset($did);
unset($department);
unset($cid);
unset($employee);
}
// all the department heads
if ($this->lr->settings[$this->data['cid']]['show_department_head']) {
foreach ($this->lr->department_heads[$this->data['cid']] as $dhid => $department_head) {
if ($dhid != $this->data['cid']) {
$colleages[htmlentities($department_head['display_name'])] = $department_head;
}
}
unset($dhid);
unset($department_head);
}
$colids = array();
ksort($colleages);
foreach ($colleages as $display_name => $employee) {
$colids[] = $employee['id'];
}
unset($colleages);
unset($display_name);
unset($employee);
if (!empty($colids)) {
$lrcol = new leaveregistration($this->data['error_platform'], $this->data['error_id'] . ': get_calendar_month');
$lrcol->set_fields();
$lrcol->set_contacts($colids);
$lrcol->set_data($this->data['years'], $this->data['months']);
}
foreach ($colids as $collid) {
$datas = array();
$class = '';
$rel = array();
$datas[] = array('data' => $lrcol->employees[$collid]['display_name']);
foreach ($lrcol->data[$collid][$this->data['year']][$this->data['month']] as $day => $array) {
list($data, $class, $rel) = $this->get_calendar_month_day($lrcol->employees, $collid, $this->data['user_cid'], $this->data['user_id'], $day, $this->data['month'], $this->data['year'], $array);
$datas[] = array('data' => $data, 'class' => $class, 'rel' => rawurlencode(serialize($rel)));
}
unset($day);
unset($array);
unset($data);
unset($class);
unset($rel);
$rows[] = array('data' => $datas);
}
unset($datas);
$form = array('type' => 'table', 'header' => $header, 'rows' => $rows, 'attributes' => array('id' => 'calendar_months_' . $month, 'class' => 'calendar_months'), 'caption' => '', 'colgroups' => array(), 'sticky' => false, 'empty' => '');
unset($header);
unset($rows);
unset($month);
if ('form' == $this->data['type']) {
return $form;
} else {
return $this->response('calendar_month', $form, 'table', 'mouseover');
}
}
