Question 21010 in Mysql Question posted in
The official MySQL documentation can be found here.

Create date range report column using codeigniter 3 excel – Mysql

I want to create export file date-range report using ci.

views.php

<div class="form-group row">
    <label for="attn_date" class="col-sm-4 col-form-label">From</label>
    <div class="col-sm-8">
        <input type="date" class="form-control" id="attn_from" name="attn_from">
        <?= form_error('attn_from'); ?>
    </div>
</div>
<div class="form-group row">
    <label for="attn_date" class="col-sm-4 col-form-label">To</label>
    <div class="col-sm-8">
        <input type="date" class="form-control" id="attn_to" name="attn_to">
        <?= form_error('attn_to'); ?>
    </div>
</div>
<div class="form-group row">
    <label for="daily_export_file" class="col-sm-4 col-form-label">Export Data</label>
    <div class="col-sm-8">
        <?= form_dropdown('export_file', ['report' => 'Report'], set_value('export_file'), 'class="form-control" id="export_file"'); ?>
        <?= form_error('export_file'); ?>
    </div>
</div>

controller.php

$querydata = $this->db->query("select a.*, b.* from db_attn a
                                    LEFT JOIN tbl_wrkhour b 
                                    ON b.id_wrkhour = a.wrk_hour WHERE attn_date between '". $this->input->post('attn_from')."' 
AND '". $this->input->post('attn_to')."'order by attn_date desc")->result();

$spreadsheet = new PhpOfficePhpSpreadsheetSpreadsheet();
$sheet_name = $spreadsheet->getActiveSheet()->setTitle("Attendance Report");
$sheet = $spreadsheet->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$highestColumm = $sheet->getHighestColumn();
$dataattn = $querydata;
$sheet->setCellValue('A5', 'No');
$sheet->setCellValue('B5', 'Employee Name');
$sheet->setCellValue('C5', 'Date');
$sheet->setCellValue('D5', 'Detail');
$no = 1;
$rowx = 6;
foreach ($dataattn as $rowattn) {
    $sheet->setCellValue('A' . $rowx, $no++);
    $sheet->setCellValue('B' . $rowx, $rowattn->emp_name);
    $sheet->setCellValue('C' . $rowx, $rowattn->date);
    $sheet->setCellValue('D' . $rowx, $rowattn->attn_detail);

}

I’ve successfully show date range of all employee details. How to transpose the detail of each date?

current view in my export excel file:

No Employee Name Date Detail
1 aaaa 03-01-2023 Present
2 bbbb 03-01-2023 leave
3 cccc 03-01-2023 late
4 aaaa 04-01-2023 Present
5 bbbb 04-01-2023 Present
6 cccc 04-01-2023 Present

expected change:

No Employee Name 03-01-2023 04-01-2023 next column date range
1 aaaa Present Present next
2 bbbb leave Present next
3 cccc late Present next

Any idea? Please help…

Tags:

2

Answers


  1. 0

    You can probably get that from SQL directly

    select
   no,
   emp_name,
   case 
      when exists (select user was present on time on 03-01-2023) then 'Present'
      when exists (select user was present late on 03-01-2023) then 'Late'
      else 'Absent'
  end as '03-01-2023',
....
    Login or Signup to reply.
  2. 0

    create a helper array, fill it in foreach function with rules and colums, then set this array to $dataattn.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search