Welcome Guest, Not a member yet? Register   Sign In
Try to combine two Codeigniter scripts, to export data from mysql to excel
#1

I found two awesome scripts on Tech Arise Team website. The first one is: http://techarise.com/export-data-to-excel-using-codeigniter/ and the second one is: http://techarise.com/datatable-server-side-processing-codeigniter-mysql-ajax-custom-search/. I try to combine these scripts, that i get a form, which can be export filtered data to excel, based on user's input date range, in Codeigniter framework. The scripts, which i modified, is these: application/controllers/Order.php:

Code:
<?php
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/

/**
* Description of Order Controller
*
* @author TechArise Team
*
* @email  [email protected]
*/
if (!defined('BASEPATH'))
   exit('No direct script access allowed');

class Order extends CI_Controller {

   public function __construct() {
       parent::__construct();
       $this->load->model('Site_model', 'site');
   }
   // upload xlsx|xls file
   public function index() {
       $data['page'] = 'order';
       $data['title'] = 'Data Table | TechArise';
       $this->load->view('order/index', $data);
   }
   // get Orders List
   public function getOrderList() {    
       $orderID = $this->input->post('order_id');
       $name = $this->input->post('name');
       $startDate = $this->input->post('start_date');
       $endDate = $this->input->post('end_date');        
       if(!empty($orderID)){
           $this->site->setOrderID($orderID);
       }        
       if(!empty($name)){
           $this->site->setName($name);
       }                
       if(!empty($startDate) && !empty($endDate)) {
           $this->site->setStartDate(date('Y-m-d', strtotime($startDate)));
           $this->site->setEndDate(date('Y-m-d', strtotime($endDate)));
       }        
       $getOrderInfo = $this->site->getOrders();
       $dataArray = array();
       foreach ($getOrderInfo as $element) {            
           $dataArray[] = array(
               $element['order_id'],
               date(DATE_FORMAT_SIMPLE, $element['order_date']),
               $element['name'],
               $element['city'],
               $element['amount'],
               $element['status'],
           );
       }
       echo json_encode(array("data" => $dataArray));
   }

}
application/controllers/Export.php:

Code:
<?php

/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/

/**
* Description of Export Controller
*
* @author TechArise Team
*
* @email  [email protected]
*/
if (!defined('BASEPATH'))
   exit('No direct script access allowed');

class Export extends CI_Controller {

   // construct
   public function __construct() {
       parent::__construct();
       // load model
      // $this->load->model('Export_model', 'export');
       $this->load->model('Site_model', 'site');
   }

   // export xls file
   public function index() {
       $data['page'] = 'order';
       $data['title'] = 'tvep.hu - card export';
       //$data['employeeInfo'] = $this->export->getOrders();
       $this->load->view('order/index', $data);
   }

   // create xls
   public function createXLS() {
       // create file name
       $fileName = 'befizetesek-' . date("Y-m-d") . '.xls';
       // load excel library
       $this->load->library('excel');
       $getOrderList = $this->site->getOrders();
       $objPHPExcel = new PHPExcel();
       $objPHPExcel->setActiveSheetIndex(0);
       // set Header
       $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Order No');
       $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Date');
       $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Name');
       $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'City');
       $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Amount');
       $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Status');
       // set Row
       $rowCount = 2;



       $orderID = $this->input->post('order_id');
       $name = $this->input->post('name');
       $startDate = $this->input->post('start_date');
       $endDate = $this->input->post('end_date');        
       if(!empty($orderID)){
           $this->site->setOrderID($orderID);
       }        
       if(!empty($name)){
           $this->site->setName($name);
       }                
       if(!empty($startDate) && !empty($endDate)) {
           $this->site->setStartDate(date('Y-m-d', strtotime($startDate)));
           $this->site->setEndDate(date('Y-m-d', strtotime($endDate)));
       }        
       $getOrderInfo = $this->site->getOrders();
       $dataArray = array();
       foreach ($getOrderInfo as $element) {            
         $dataArray[] = array(
           $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['order_id']),
           $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, date(DATE_FORMAT_SIMPLE, $element['order_date'])),
           $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $element['name']),
           $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $element['city']),
           $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $element['amount']),
           $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $element['status']),
           $rowCount++,
           );
       }
       echo json_encode(array("data" => $dataArray));

       $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
       $objWriter->save(ROOT_UPLOAD_IMPORT_PATH . $fileName);
       // download file
       header("Content-Type: application/vnd.ms-excel");
       redirect(HTTP_UPLOAD_IMPORT_PATH . $fileName);
   }

}
application/views/order/index.php:

Code:
<?php
$this->load->view('templates/header');
?>
<div class="row">
   <div class="col-lg-12">
       <h2>Bankkártyás befizetések exportja - 2018.02.15.-től</h2>                
   </div>
</div><!-- /.row -->

<div class="row">  
           <div class="col-lg-2">                                        
               <div class="form-group">
                   <input type="text" name="order_id" value="" class="form-control" id="filter-order-no" placeholder="Order No">
               </div>
           </div>
           <div class="col-lg-2">  
               <div class="form-group">
                    <input type="text" name="name" value="" class="form-control" id="filter-name" placeholder="Name">
               </div>
           </div>
           <div class="col-lg-3">
               <div class="form-group">
                   <input type="text" name="order_start_date" value="" class="form-control getDatePicker" id="order-start-date" placeholder="Start date">
               </div>
           </div>
           <div class="col-lg-3">
               <div class="form-group">
                   <input type="text" name="order_end_date" value="" class="form-control getDatePicker" id="order-end-date" placeholder="End date">
               </div>
           </div>
           <div class="col-lg-2">                                      
               <div class="form-group">
                   <button name="filter_order_filter" type="button" class="btn btn-primary btn-block" id="filter-order-filter" value="filter"><i class="fa fa-search fa-fw"></i></button>
               </div>
           </div>                                        
       </div>
       <div class="row">
       <div class="col-lg-12">      
       <div id="render-list-of-order">
       </div>
       </div>        
   </div>
   <div class="row">
   <div class="col-lg-12">      
       <a class="btn btn-info btn-xs" style="margin: 2px" value="filter" href="<?php echo site_url() ?>export/createxls"><i class="fa fa-file-excel-o"></i> Export xls</a>          
   </div>
</div><!-- /.row -->
<?php
$this->load->view('templates/footer');
?>
Other scripts aren't modified.

The problem is that the exported file contains all datas from the data table, not just the filtered. :/ Test site is: https://tvep.hu/data-search/

[url=https://tvep.hu/data-search/][/url]What am i doing wrong?
(Sorry for my bad english) Any help appreciate! Thank you!!!
Reply




Theme © iAndrew 2016 - Forum software by © MyBB