cakePHP连表查询兼容分页

/**
* 查询渠道数据
* $search_qd_type：渠道类型（正向点播、RDO等）
* $search_type：查询、导出
* $summary_type：汇总、明细
* 
* http://***/ebook/orders/statistics_summary/2015-12-21/2015-12-23/empty/empty/empty/zxdb/search/summary
*/
function summary_search_datas($search_begin_date=null,$search_end_date=null,$channel=null,$bids=null,$book_name=null,$search_qd_type=null,$search_type=null,$summary_type=null) {
if(empty($search_begin_date) || $search_begin_date==’empty’) {
$now = date(‘Y-m-d’);
$search_begin_date = date(‘Y-m-d’,strtotime($now.’ -1 day ‘));
}
if(empty($search_qd_type) || $search_qd_type==’empty’) {
$search_qd_type = ‘all’;
}
if(empty($search_type) || $search_type==’empty’) {
$search_type = ‘search’;
}
if(empty($summary_type) || $summary_type==’empty’) {
$summary_type = ‘summary’;
}

// var_dump(‘$this->data’);
// var_dump($this->data);
// var_dump(‘$_GET’);
// var_dump($_GET);
// var_dump($search_begin_date);
// var_dump($search_end_date);
// var_dump($channel);
// var_dump($bids);
// var_dump($book_name);
// var_dump($search_qd_type);
// var_dump($search_type);
// var_dump($summary_type);

set_time_limit(1800);
    $conditions = array();
    $this->data[‘Order’][‘search_begin_date’]=”;
    $this->data[‘Order’][‘search_end_date’] = ”;
    if(!empty($search_begin_date) && 0 != strcmp($search_begin_date,’empty’)){
    $conditions[‘date >=’] = $search_begin_date;
    $this->data[‘Order’][‘search_begin_date’] = $search_begin_date;
    }

    if(!empty($search_end_date) && 0 != strcmp($search_end_date,’empty’)){
    $conditions[‘date <=’] = $search_end_date;
    $this->data[‘Order’][‘search_end_date’] = $search_end_date;
    }

    if(!empty($channel) &&  0 != strcmp($channel,’empty’)){
    $conditions[‘channel’] = $channel;
    $this->data[‘Order’][‘channel’] = $channel;
    }

    if(!empty($bids) &&  0 != strcmp($bids,’empty’)){
    $bidArray = $this->validateBids($bids);
    if(!empty($bidArray) && count($bidArray)>0){
    $conditions[‘book_id’] = $bidArray;
    }
    $this->data[‘Order’][‘bids’] = $bids;
    }

        if(!empty($book_name) &&  0 != strcmp($book_name,’empty’)){
            $conditions[‘book_name’] = $book_name;
            $this->data[‘Order’][‘book_name’] = $book_name;
        }
        
        if(!empty($search_qd_type) &&  0 != strcmp($search_qd_type,’empty’)){
        if($search_qd_type !=’all’) {
        // FixMe:恶心的逻辑实现，这个要兼容分页，也要兼容连表查询的，暂时想不到其他方法，其他用字段的话 ，框架里面有个计数的sql会报错
        $conditions[‘1’] = ‘ exists(select 1 from qd_mappings qdm2 where qdm2.child_qd_code=Order.channel and qdm2.parent_qd_code=”‘.$search_qd_type.'” limit 1)’;
        }
            $this->data[‘Order’][‘search_qd_type’] = $search_qd_type;
        }
        
        if(!empty($search_type) &&  0 != strcmp($search_type,’empty’)){
            $this->data[‘Order’][‘search_type’] = $search_type;
        }
        
        if(!empty($summary_type) &&  0 != strcmp($summary_type,’empty’)){
            $this->data[‘Order’][‘summary_type’] = $summary_type;
        }

$fields = array(‘qdm.*’,’Order.*’);
$joins = array(array(
‘table’ => ‘qd_mappings’,
‘alias’ => ‘qdm’,        
‘type’ => ‘LEFT’,        
‘conditions’ => array(‘qdm.child_qd_code = Order.channel’)
));
$order = array(‘ date desc ‘);

$conn = array(‘conditions’=>$conditions,’fields’=>$fields,’limit’=>’30’,’joins’=>$joins,’order’=>$order);
// var_dump(‘$conn’);
// var_dump($conn);
        $this->paginate = $conn;
    $orders = $this->paginate();
        // 如果是查询明细，则分页，其他都是全查
        if(!($search_type==’search’ && $summary_type == ‘detail’)) {
        $orders = $this->Order->find(‘all’,$conn);
        }
        
//        var_dump(‘orders’);
//        var_dump($orders);
        
        App::import ( ‘Vendor’, ‘CmbookDetail’, array (‘file’ => ‘cmread’ . DS . ‘cmbook_detail.php’ ) );

        $summary_datas = array();
    foreach ($orders as $key=>$value){
    $sql=”select cps.name,cps.channels from cp_books,cps where  cp_books.cp_id=cps.id and cp_books.book_id='{$value[‘Order’][‘book_id’]}’ order by add_to_sum desc”;
    $cp=$this->Order->query($sql);
    foreach ($cp as $k=>$v){
    if(strpos($v[‘cps’][‘channels’],$value[‘Order’][‘channel’]) === false){
    unset($cp[$k]);
    }
    }
   
    $cp=array_values($cp);
    $sql=”select class_name,name from cmbooks where bid='{$value[‘Order’][‘book_id’]}’ limit 1″;
    $class=$this->Order->query($sql);
    if(!empty($cp)){
    $orders[$key][‘order’][‘cp’]=$cp[0][‘cps’][‘name’];
    }else{
    $orders[$key][‘order’][‘cp’]=’无’;
    }

            if(empty($class[0][‘cmbooks’][‘class_name’]))
{
// var_dump($value[‘Order’][‘book_id’]);
// var_dump(‘before’);var_dump($class);
$detail = new CmBookDetail ( $value[‘Order’][‘book_id’] );
$this->Order->query($detail->replace_cmbook_sql());
}

$class=$this->Order->query($sql);
// var_dump(‘end’);var_dump($class);
$orders[$key][‘order’][‘class_name’] = $class ? $class[0][‘cmbooks’][‘class_name’] : ‘不清楚’;
    }
   
    $detail_datas = $orders;
    $summary_datas = array();
//     var_dump(‘$detail_datas’);
//     var_dump($detail_datas);
   
    if($summary_type == ‘summary’) {
    if(!empty($detail_datas)) {
    $summary_datas[0] = $detail_datas[0];
    $summary_datas[0][‘Order’][‘date’] = ”;
    if($search_qd_type==’all’) {
    $summary_datas[0][‘Order’][‘channel’] = ‘全部’;
    }else{
    $summary_datas[0][‘Order’][‘channel’] = $detail_datas[0][‘qdm’][‘parent_qd_name’];
    }
    $summary_datas[0][‘Order’][‘book_id’] = ”;
    $summary_datas[0][‘Order’][‘book_name’] = ”;
    $summary_datas[0][‘Order’][‘access_users’] = 0;
    $summary_datas[0][‘Order’][‘pay_users’] = 0;
    $summary_datas[0][‘Order’][‘expense’] = 0;
    $summary_datas[0][‘Order’][‘ticket’] = 0;
    $summary_datas[0][‘order’][‘cp’] = ”;
    $summary_datas[0][‘order’][‘class_name’] = ”;
   
    foreach($detail_datas as $detail_data) {
    $summary_datas[0][‘Order’][‘access_users’] += $detail_data[‘Order’][‘access_users’];
    $summary_datas[0][‘Order’][‘pay_users’] += $detail_data[‘Order’][‘pay_users’];
    $summary_datas[0][‘Order’][‘expense’] += $detail_data[‘Order’][‘expense’];
    $summary_datas[0][‘Order’][‘ticket’] += $detail_data[‘Order’][‘ticket’];
    }
    }
   
    return $summary_datas;
    } else {
    return $detail_datas;
    }
   

}

导出excel

/**
     * 分类汇总 – 导出
     */
    function summary_export_order($search_begin_date=null,$search_end_date=null,$channel=null,$bids=null,$book_name=null,$search_qd_type=null,$search_type=null,$summary_type=null){
error_reporting(0);
$orders = $this->summary_search_datas($search_begin_date,$search_end_date,$channel,$bids,$book_name,$search_qd_type,$search_type,$summary_type);
//        var_dump($orders);die();
header(“Content-type:application/vnd.ms-excel”);
header(“Content-Disposition:attachment;filename=”. ‘图书订购分类汇总’ . “.csv”);
echo iconv(“UTF-8″,”GBK”,”\”日期\”,\”渠道号\”,\”书名\”,\”图书bid\”,\”类别\”,\”访问用户数\”,\”付费用户数\”,\”信息费\”,\”无价书卷\”,\”cp名称\”\r\n”);
        
$result = array();
foreach($orders as $value)
{
echo(“\”” . $value[‘Order’][‘date’].  “\”,”);
echo(“\”” . iconv(‘UTF-8′,’GBK’,$value[‘Order’][‘channel’]) .  “\”,”);
echo(“\”” . iconv(‘UTF-8′,’GBK’,$value[‘Order’][‘book_name’]) .  “\”,”);
echo(“\”” . $value[‘Order’][‘book_id’] .  “\”,”);
echo(“\”” . iconv(‘UTF-8′,’GBK’,$value[‘order’][‘class_name’]) .  “\”,”);
echo(“\”” . $value[‘Order’][‘access_users’] .  “\”,”);
echo(“\”” . $value[‘Order’][‘pay_users’] .  “\”,”);
echo(“\”” . $value[‘Order’][‘expense’] .  “\”,”);
echo(“\”” . $value[‘Order’][‘ticket’] .  “\”,”);
echo(“\”” . iconv(‘UTF-8′,’GBK’,$value[‘Order’][‘cp’]) .  “\”,”);
echo(“\”” .  “\”\r\n”);
}
exit();
}