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; } }
function civicrm_api3_job_exportleaveregistration_months($params) { $years = array($params['Year']); $months = explode(',', $params['Months']); echo 'Year: ' . '<br/>' . PHP_EOL; echo '<pre>'; print_r($years); echo '</pre>'; echo 'Months: ' . '<br/>' . PHP_EOL; echo '<pre>'; print_r($months); echo '</pre>'; $column_names = array(ts('Last name'), ts('First name'), ts('Full name'), ts('From'), ts('To'), ts('Message')); $column_days = array(); $from = $params['Year'] . '-' . $months[0] . '-01'; $to = $params['Year'] . '-' . end($months); $last_day = ''; foreach ($years as $year) { foreach ($months as $month) { for ($day_month = 1; $day_month <= cal_days_in_month(CAL_GREGORIAN, $month, $year); $day_month++) { $timestamp = strtotime($year . '-' . $month . '-' . $day_month); $day = date('d', $timestamp); $column_days[] = ts($day . '-' . $month); $last_day = $day; } } } $to .= '-' . $last_day; /*echo('Columns Names / Days: ') . '<br/>' . PHP_EOL; echo('<pre>'); print_r($column_names); print_r($column_days); echo('</pre>');*/ // get all the employees, from civicrm $parameters = array('version' => 3, 'sequential' => 1, 'contact_type' => 'Individual', 'contact_sub_type' => 'Employee', 'options' => array('limit' => 0, 'sort' => 'last_name ASC'), 'is_deleted' => 0); $result = civicrm_api('Contact', 'get', $parameters); // if there is a error, set everything to empty expect the week, from day and to day, and set the error in the leave column if ($result['is_error']) { $row = array_merge(array('last_name' => '', 'first_name' => '', 'display_name' => '', 'from' => $from, 'to' => $to, 'message' => ts('Failt get all employees !')), $column_days); } /*echo('<pre>'); print_r($result['values']); echo('</pre>');*/ // loop through the employees foreach ($result['values'] as $employee) { $data = array(); // define the variable that hold the class $lr = array(); // set the class $lr = new leaveregistration('civicrm'); // if there is a error, set sick to empty and set the error in the leave column if ($lr->isset_error()) { $rows[] = array_merge(array('last_name' => $employee['last_name'], 'first_name' => $employee['first_name'], 'display_name' => $employee['display_name'], 'from' => $from, 'to' => $to, 'message' => ts('Failt construct leave registration class !')), $column_days); continue; } // set the contact in the class $lr->set_contacts($employee['contact_id'], $employee['contact_id']); // if there is a error, set sick to empty and set the error in the leave column if ($lr->isset_error()) { $rows[] = array_merge(array('last_name' => $employee['last_name'], 'first_name' => $employee['first_name'], 'display_name' => $employee['display_name'], 'from' => $from, 'to' => $to, 'message' => ts('Failt set contacts leave registration class !')), $column_days); continue; } // set the data in the class $lr->set_data($years, array()); // if there is a error, set sick to empty and set the error in the leave column if ($lr->isset_error()) { $rows[] = array_merge(array('last_name' => $employee['last_name'], 'first_name' => $employee['first_name'], 'display_name' => $employee['display_name'], 'from' => $from, 'to' => $to, 'message' => ts('Failt set contacts leave registration class !')), $column_days); continue; } $days = array(); foreach ($years as $year) { foreach ($months as $month) { for ($day_month = 1; $day_month <= cal_days_in_month(CAL_GREGORIAN, $month, $year); $day_month++) { $timestamp = strtotime($year . '-' . $month . '-' . $day_month); $day = date('d', $timestamp); // get the data from the contact, year, month and day $data = $lr->data[$employee['contact_id']][$year][$month][$day]; // empty content $content = array(); $leave = 0; $sick = 0; $time_for_time = 0; if (isset($data['adjustments']['duration']) and 0 == $data['adjustments']['duration']) { $content[] = 'Vrij'; } // switch between the leave types // and add the duration to the right counter (work, time for time, leave or sick) if (isset($data['request']['is_request']) and 1 == $data['request']['is_request'] and 'approved' == $data['request']['status']) { switch ($data['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': $leave += $data['request']['duration']; break; case 'sick_less_one_day': case 'sick': $sick += $data['request']['duration']; break; } } // time_for_time if (isset($data['time_for_time']['is_time_for_time']) and 1 == $data['time_for_time']['is_time_for_time'] and 'approved' == $data['time_for_time']['status']) { $time_for_time = $data['time_for_time']['duration']; } // normal_leave // leave type is normal_leave_less_one_day and normal_leave if (isset($data['normal_leave']['is_normal_leave']) and 1 == $data['normal_leave']['is_normal_leave'] and 'approved' == $data['normal_leave']['status']) { $leave += $data['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); } // calculate the sick hours and the minutes from the duration (in minutes) if ($leave > 0) { $hours = floor($sick / 60); $minutes = $sick - $hours * 60; $content[] = 'Z: ' . $hours . ':' . sprintf("%02s", $minutes); } // create the row from the contact wiht the information $days[] = implode(' ', $content); } } } $rows[] = array_merge(array('last_name' => $employee['last_name'], 'first_name' => $employee['first_name'], 'display_name' => $employee['display_name'], 'from' => $from, 'to' => $to, 'message' => ts('')), $days); } // create the actualy content of the csv file $content = ''; // first the column names $content .= implode(';', $column_names) . ';' . implode(';', $column_days) . "\r\n"; // loop through the rows of data // add them to the content for the csv file foreach ($rows as $key => $data) { $content .= implode(';', $data) . "\r\n"; } echo $content . '<br/>' . PHP_EOL; unset($rows); unset($result); // donnot email if it is no if (!isset($params['doEmail']) or 'No' != $params['doEmail']) { civicrm_api3_job_exportleaveregistration_months_email($params, $content); } }
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'); } }