想在php的帮助下以csv格式导出我们的报表数据“只从下拉过滤器中选择”

mxg2im7a  于 2021-06-25  发布在  Mysql
关注(0)|答案(0)|浏览(135)

这是我的代码,它生成了一个报告,我想将此报告导出为csv格式。

public function productsellerreportAction(){    

     global $mySession ; 

     $this->view->pageHeading = "Product seller";

     $this->view->pageDescription = "Booking Report";

      //$sId =$this->_getParam('sId') ;

     //$storeData = $this->modelSuper->Super_Get('stores'," 1","fetchAll");

      //archana////

     if($this->adminLogged->admin_id==1){

         $storeData = $this->modelSuper->Super_Get('stores'," 1","fetchAll");

     }else{

      $store_array=array();

      $store_ids = $this->modelSuper->getAdapter()->query("select ash_store_id from acl_store_hq where ash_admin_id=".$this->adminLogged->admin_id)->fetchAll();

      foreach($store_ids as $s_i){

         $store_array[]=$s_i['ash_store_id'];

      }

      $store_array_ids = join("','",$store_array);  

      $storeData = $this->modelSuper->getAdapter()->query("select * from stores where s_id in ('$store_array_ids') ")->fetchAll();

     }

    //archana////

     $this->view->storeData=$storeData;

     $sd=$sdate =$this->_getParam('sdate') ;

     $ed=$edate =$this->_getParam('edate') ;

            $sId =$this->_getParam('store') ;

            $this->view->sId=$sId;

            $this->view->sd=$sd;

            $this->view->ed=$ed;

}

public function getproductsellerdataAction(){

    global $mySession ; 

    $this->dbObj = Zend_Registry::get('db');

    $sId=$this->_getParam('sId') ;

      $sd=$sdate =$this->_getParam('sd') ;

      $ed=$edate =$this->_getParam('ed') ;

      if($sdate==""){

      }else{

        $sdate=explode("-",$sdate);

        $sdate=$sdate[2].'-'.$sdate[1].'-'.$sdate[0];

      }

      if($edate==""){

      }else{

        $edate=explode("-",$edate);

        $edate=$edate[2].'-'.$edate[1].'-'.$edate[0];

      }

      //'SUM(bp_qty) as bp_qty1','SUBSTRING(bp_resn, 1, 8) as bp_resn1','(bp_amount/bp_qty) as bp_rate2'

            $aColumns = array('b_id','bp_resn','bp_name','b_orderid','bp_qty','bp_amount','b_date','b_status','(bp_amount/bp_qty) as bp_rate2','cast(substr(b_orderid, 6) as unsigned) AS b_orderid1');

    $sIndexColumn = 'b_id';

    $sTable1 = ' booking ';

    $sTable = 'booking';

    /* 

     * Paging

     */

    $sLimit = "";

    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )

    {

        $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );

    }

        /*
     * Ordering
     */
    $sOrder = "";

    if ( isset( $_GET['iSortCol_0'] ) )
    {
        $sOrder = "ORDER BY  ";
        for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
        {
            if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
            {
                if($aColumns[ intval( $_GET['iSortCol_'.$i] ) ]=='b_orderid1'){
                       $sOrder .= "b_orderid1".
                    ($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
                }
                else if($aColumns[ intval( $_GET['iSortCol_'.$i] ) ]=='b_date'){
                       $sOrder .= "b_date ".
                    ($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
                }else{
                      $sOrder .= "".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]." ".
                    ($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
                }

            }
        }

        //prd($sOrder);
        $sOrder = substr_replace( $sOrder, "", -2 );
        if ( $sOrder == "ORDER BY" )
        {
            $sOrder = "";
        }
    }

    /* 
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    $sWhere = "";
    if ( isset($_GET['sSearch']) and $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            $sWhere .= " LCASE(".$aColumns[$i].") LIKE '%".strtolower( $_GET['sSearch'] )."%' OR ";
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }

    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) and $_GET['bSearchable_'.$i] == "true" and $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= "LCASE(".$aColumns[$i].") LIKE '%".strtolower($_GET['sSearch_'.$i])."%' ";

            //$sWhere .= "".$aColumns[$i]." LIKE '%".$_GET['sSearch_'.$i]."%' ";
        }
    }

    if($sdate!=""){

        $con1=" and b_date>='".$sdate."'";

    }else {

        $con1='';

    }

    if($edate!=""){

        $con2=" and b_date<='".$edate."'";

    }else{

        $con2='';

    }

    if($sWhere==""){

        $sWhere=" where 1 ";

    }

    $con3="join booking_product on b_id=bp_b_id and b_booked_by=".$sId." and b_status <> '-1' ";

    $newsql=$con1. $con2;

    $sWhere.=" and ".$newsql;

    $sQuery = " SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))." FROM  $sTable1 

    $con3  $newsql $sOrder $sLimit";
    //echo $sQuery; die;

    $qry = $this->dbObj->query($sQuery)->fetchAll();

    //echo "<pre>"; var_dump($qry); echo "</pre>"; die;

    /* Data set length after filtering */

    $sQuery = "SELECT FOUND_ROWS() as fcnt";

    $aResultFilterTotal =  $this->dbObj->query($sQuery)->fetchAll(); 

    $iFilteredTotal = $aResultFilterTotal[0]['fcnt'];

    /* Total data set length */

    $sQuery = "SELECT COUNT(`".$sIndexColumn."`) as cnt FROM $sTable1 $con3 ".$newsql ;

    $rResultTotal = $this->dbObj->query($sQuery)->fetchAll(); 

    $iTotal = $rResultTotal[0]['cnt'];

    /*

     * Output

     */

         $output = array(

            "iTotalRecords" => $iTotal,

            "iTotalDisplayRecords" => $iFilteredTotal,

            "aaData" => array()

         );

    $j=0;$i=1; $count=1;

    foreach($qry as $row12)

    {  

         $row=array();

         $row[]=$count; 

         $row[]=$row12['bp_resn'];

         $row[]=$row12['bp_name'];     

         $row[]=$row12['b_orderid'];

         $row[]=$row12['bp_qty'];

    //   $row[]=$row12['b_date'];

        $row[]= number_format((float)$row12['bp_rate2'], 2, '.', '');

         //$row[]=$row12['bp_total'];
         $output['aaData'][] = $row;

         $j++; $i++; $count++;

    }   

    echo json_encode($output);

    exit();

}

这是php代码。
这是在选择商店和日期后得到的结果:

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题