Home > Back-end >  How to export a report by date range in codeigniter
How to export a report by date range in codeigniter

Time:03-20

I have no experience in codeigniter and iam working on an open source Eassy!Appointments application, I need to add feature in settings tab that export report between two dates entered by the admin!I have 2 problems,first: Ican't pass input date to controller and the second i can't display the report table in the view

the view:

<?php $hidden = ($privileges[PRIV_SYSTEM_SETTINGS]['view'] == TRUE) ? '' : 'hidden'; ?>
<div id="export" >
    <form   method= "post" action='<?= base_url(); ?>'>
           
            <!---------input--------->
        
        <fieldset >
            <legend>Export Appointment Data from:</legend>
            
            <input type="date"  name="from_date">
            <legend>To:</legend>
            <input type="date"  name="to_date">
            
            
            <button type="button" id="data-csv-export"  onclick="window.location='<?php echo site_url("backend/export_appointments");?>'">Download report.CSV</button>
            
        
            <br>
            
        <table >
                            <thead>
                            <tr>
                                <th>First Name</th>
                                <th>Last Name</th>
                                <th>Book Datetime</th>
                                <th>Start Datetime</th>
                                <th>End Datetime</th>
                             </tr>
                            </thead>
                            <tbody><!-- Dynamic Content -->
            
                
 <?php
     /*** new***/
     if (is_array($query) || is_object($query))
         /*** new***/
  foreach($data as $row)
  {
   echo '
   <tr>
    <td>'.$row["first_name"].'</td>
    <td>'.$row["last_name"].'</td>
    <td>'.$row["book_datetime"].'</td>
    <td>$'.$row["start_datetime"].'</td>
    <td>'.$row["end_datetime"].'</td>
   </tr>
   ';
  }
  ?>
            </tbody>
            </table>
            </fieldset>
                      
        
    </form>
    </div>

the controller:

public function export_appointments() {

    //check if the user is admin

    $this->session->set_userdata('dest_url', site_url('backend/users'));

    if ( ! $this->has_privileges(PRIV_USERS))
    {
        return;
    }
     //if the user is admin
     
    $this->load->helper('download');
   

  $date1 = date("Y-m-d",strtotime($_post['from_date']));
  $date2 = date("Y-m-d",strtotime($_post['to_date']));

    
    $this->db->select('first_name, last_name, book_datetime,start_datetime,end_datetime');
    $this->db->from('ea_appointments');
    $this->db->join('ea_users', 'ea_users.id = ea_appointments.id');
    $this->db->where('ea_users.id_roles',3);
    
   $this->db->where('cast(book_datetime as date) BETWEEN "'. date('Y-m-d', strtotime($date1)). '" and "'. date('Y-m-d', strtotime($date2)).'"');
    
    
    /* input date*/
   
    $query = $this->db->get()->result_array();

    $fp = fopen('php://output', 'w');
    $header = array("First_Name","Last_Name","Booking_Date","Start_Date","End_Date"); 
    fputcsv($fp, $header);
    foreach ($query as $fields) {
    fputcsv($fp, $fields);
    }

    $data = file_get_contents('php://output');
    $name = 'report.csv';

    // Build the headers to push out the file properly.
    header('Pragma: public');     // required
    header('Expires: 0');         // no cache
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Cache-Control: private',false);
    header('Content-Disposition: attachment; filename="'.basename($name).'"');  // Add the file name
    header('Content-Transfer-Encoding: binary');
    header('Connection: close');
    exit();

    force_download($name, $data);
    fclose($fp);
    
    $this->load->view('backend/settings');
    
   
}

CodePudding user response:

You can use your "controller name"/export_appointments instead of base_url() in the form url.

CodePudding user response:

You have used $_post instead of $_POST to reference to values posted in the form. Also, you have an exit() statement after your headers, which would be required for the output to prevent further execution, but it is not conditional, and will always exit so it never gets to the load view function. Suggestion before doing the headers and exit:

if (!empty($_POST)) {
    // Code that does things only when the form has been submitted. Headers and export features go here.
} else {
    // Do things when the form is NOT submitted. The load view will go here.
}

This should get you closer to the solution.

You also do not have to do the strtotime() calls on the dates - simply put the posted date values in the query, but I would recommend you sanitize your inputs before doing so.

  • Related