php中关于 left join 的分页显示

  
    /* 
     * 统计商机搜索总条数信息
     * ftt
     * 2015-11-10 10:13:15
     */
    public function getBusinessCount($btype='',$buname='',$sTime='',$eTime=''){
          $sql = 'select Count(*) from `admin_business` as b left join `web_user` as w on b.b_mid = w.id left join `admin_advisor` as v on v.s_id = w.adv_id ';       
        if($btype !=''){
            $AOR = $AOR !="" ? ' AND ' : ' WHERE ';
            $sql .= $AOR.' b.b_type='.$btype; 
        }
        if($buname !=''){
            $AOR = $AOR !="" ? ' AND ' : ' WHERE ';
            $sql .= $AOR.' b.b_uname='."'$buname'";
        }
        if($sTime !=''){
            $AOR = $AOR !="" ? ' AND ' : ' WHERE ';
            $sql .= $AOR.'b.b_create_time >='."'$sTime'";
        }
        if ($eTime !=''){
            $AOR = $AOR !="" ? ' AND ' : ' WHERE ';
             $sql .= $AOR.'b.b_create_time <='."'$eTime'";
        }
    $sql  .= ' order by b.id  desc';
//    echo $sql;
    $data = $this->query($sql);
    return $data?$data->fetch(PDO::FETCH_ASSOC):array();
    }

    
    
    /*
     * 用于搜索条件的分页显示
     * ftt
     * 2015年11月10日10:13:46
     */
    
    public function getBusiness1($btype='',$buname='',$sTime='',$eTime='',$offset = 0, $limit = 10) {
    $sql = 'select b.id as bidd,b.b_uname as b_uname,b.b_iphone as b_iphone,b.b_type as b_type,b.b_pro_name as b_pro_name,b.b_searl as b_searl,b.b_orgin as b_orgin,b.b_from as b_from,w.adv_id as adv_id, b.b_create_time as b_create_time from `admin_business` as b left join `web_user` as w on b.b_mid = w.id left join `admin_advisor` as v on v.s_id = w.adv_id ';       
        if($btype !=''){
            $AOR = $AOR !="" ? ' AND ' : ' WHERE ';
            $sql .= $AOR.' b.b_type='.$btype; 
        }
        if($buname !=''){
            $AOR = $AOR !="" ? ' AND ' : ' WHERE ';
            $sql .= $AOR.' b.b_uname='."'$buname'";
        }
        if($sTime !=''){
            $AOR = $AOR !="" ? ' AND ' : ' WHERE ';
            $sql .= $AOR.'b.b_create_time >='."'$sTime'";
        }
        if ($eTime !=''){
            $AOR = $AOR !="" ? ' AND ' : ' WHERE ';
             $sql .= $AOR.'b.b_create_time <='."'$eTime'";
        }
    $sql  .= ' order by b.id  desc  limit  '.$offset.', '.$limit;
    $data = $this->query($sql);
    return $data?$data->fetchAll(PDO::FETCH_ASSOC):array();
    }
}

    /**
     * 列表管理
     */
    public function listsAction() {
        $page_count = 10;
        $Model = new BusinessModel();
        $av = new AdminAdvisorModel();
        $p = $this->_getDataGet['p'] ? intval($this->_getDataGet['p']) : 1;
        $count = $Model ->countModel(array());
      
        $parameter = array(
                'p' => $p
        );
        
        $page = new Comm_Page($count, $page_count, $parameter);// 实例化分页类 传入总记录数


        $lists =  $Model -> getBusiness(array(), ($p-1)*$page_count , $page_count);
        $lists = $av->getAdvisors($lists);

        $pagesize =$page->show(); //得到分页模板
           foreach($lists as $k=>$v){
               $lists[$k]['b_type'] = $this->getTypeDesc($v['b_type']);
               $lists[$k]['b_status'] = $this->getStatusDesc($v['b_status']);
           }
        
        $view = $this->getView();
        $view->assign("pagesize", $pagesize);
        $view->assign("lists", $lists);
        $view->assign("action", 'lists'); 
        $view->display("business/lists.html");
    }

    
        public function seachListAction() {
        $page_count = 10;
        $Model = new BusinessModel();
        $view = $this->getView();
        
        $startTime = $this->_getDataGet['startTime'] != '开始时间'? $this->_getDataGet['startTime']: '';
        $endTime = $this->_getDataGet['endTime']!='结束时间' ? $this->_getDataGet['endTime']:'';

        $b_uname = $this->_getDataGet['b_uname']!='' ? $this->_getDataGet['b_uname'] : '';
        $b_type = $this->_getDataGet['b_type'];
        
        /*
         * 用于对数据的返回
         */
        $query['startTime'] = $startTime;
        $query['endTime'] = $endTime;
        $query['b_type'] = $b_type;
        $query['b_uname'] = $b_uname;
        
//        print_r($query);
        if($this->_getDataGet['startTime']==''&& $this->_getDataGet['endTime'] =='' && $this->_getDataGet['b_type'] =='all' && $this->_getDataGet['b_uname'] =='' ) {//跳转404
            $url = '/index.php/Business/lists';
            Comm_Tools::redirect($url);
            exit();    
        }
        
   
        $p = $this->_getDataGet['p'] ? intval($this->_getDataGet['p']) : 0;
        $p = $p -1;
        if($b_type == 'all'){
            unset($b_type);
        }else{
            $where['b_type']=$b_type;
            $btype = $b_type;
        }
        if($b_uname != ''){
            $where['b_uname']=$b_uname;
            $buname = $b_uname;
        }
        if($startTime !=''){$sTime = strtotime($startTime);}
        if($endTime !=''){$eTime = strtotime($endTime);}
        $count = $Model ->getBusinessCount($btype,$buname,$sTime,$eTime);
        $count = $count['Count(*)'];
        $parameter = array(
                'startTime' => $startTime,
                'endTime' => $endTime,
                        'b_uname'=>$b_uname,
                        'b_type'=>$btype,
                'p' => intval($this->_get['p'])
        );
        $page = new Comm_Page($count, $page_count, $parameter);// 实例化分页类 传入总记录数
//        if($p-1 < 0){
//            $p =1;
//            $page_count = $p *10;
//        }else{
//            $page_count =($p-1)*10;
//        }
                
            $lists =  $Model -> getBusiness1($btype,$buname,$sTime,$eTime,$p *$page_count , $page_count);
            foreach($lists as $k=>$v){
                    $lists[$k]['b_type'] = $this->getTypeDesc($v['b_type']);
                    $lists[$k]['b_status'] = $this->getStatusDesc($v['b_status']);
            }
            
     
        $pagesize =$page->show(); //得到分页模板
        
        $view->assign("lists", $lists);
        $view->assign("pagesize", $pagesize);
        $view->assign("query", $query);
        $view->assign("action", 'lists');
        $view->display("business/lists.html");
    }

    /*
     * 解释业务类型
     * yangsf
     */
     public function getTypeDesc($type){
         $typeArr = ['0'=>'国内商标','1'=>'国际商标','2'=>'商标转让','3'=>'版权','4'=>'专利'];
         if(in_array($type, range(0, 4))){
             return $typeArr[$type];
         }else{
             return '未知业务类型';
         }
     }

    <div class="page-content">
        <div class="page-header">
            <h1>
                商机列表
            </h1>
        </div><!-- /.page-header -->

      
            <form action="/index.php/Business/seachList" method='get' name='searchBusiness' >
                    <div  style="display: inline-block; float: left; margin-left:12px;">
                        <select name="b_type" class="selectpicker show-tick" data-size="5" id="s_type" style="height:34px;">
                            <option value="all" <{if ($query['b_type'] == all)}> selected = "selected" <{/if}>>全部业务类型</option>
                          <option value = "0" <{if ($query['b_type'] == '0')}> selected = "selected" <{/if}>  >国内商标</option>
                          <option value = "1" <{if ($query['b_type'] == '1')}> selected = "selected" <{/if}>  >国际商标</option>
                          <option value = "2" <{if ($query['b_type'] == '2')}> selected = "selected" <{/if}>  >商标转让</option>
                          <option value = "3" <{if ($query['b_type'] == '3')}> selected = "selected" <{/if}>  >版权</option>
                          <option value = "4" <{if ($query['b_type'] == '4')}> selected = "selected" <{/if}>  >专利</option>
                        </select>
                    </div>
                
<!--                    <div  style="display: inline-block; float: left; margin-left:12px;">
                        <select name="s_type" class="selectpicker show-tick" data-size="5" id="s_type" style="height:34px;">
                          <option value="">关键词</option>
                          <option value = "1" <{if ($requery['s_type'] == 1)}> selected = "selected" <{/if}>  >订单编号</option>
                          <option value = "2" <{if ($requery['s_type'] == 2)}> selected = "selected" <{/if}>  >顾问</option>
                        </select>
                    </div>  -->
                
                
        
              <div class="input-group bootstrap-timepicker col-xs-2">
                <input type="text" id="timepicker1" name='startTime' class="form-control date-picker" value = "<{if $query['startTime']}><{$query['startTime']}><{else}>开始时间<{/if}>"  onclick="SelectDate(this,'yyyy-MM-dd hh:mm')"  />
                <span class="input-group-addon">
                <i class="icon-time bigger-110"></i>
                </span>
                </div>
                    <div class="input-group bootstrap-timepicker col-xs-2">
                <input id="timepicker2" type="text"  name='endTime'  class="form-control date-picker" value = "<{if $query['endTime']}><{$query['endTime']}><{else}>结束时间<{/if}>"  onclick="SelectDate(this,'yyyy-MM-dd hh:mm')"  />
                <span class="input-group-addon">
                <i class="icon-time bigger-110"></i>
                </span>
              </div>
                    <div  style="display: inline-block; float: left; margin-left:12px;">
                        <input type="text" name="b_uname" aria-controls="sample-table-2" <{if $query['b_uname']}>value="<{$query['b_uname']}>"<{else}>value=""<{/if}> placeholder="用户名称">
                    </div>
                    <input type="hidden" name='p' <{if $query['p']}> value=<{$query['p']}><{else}> value="1" <{/if}> >
            <div class="col-xs-1">
                <button class="btn btn-purple btn-sm" onclick="search();">搜索
                    <i class="icon-search icon-on-right bigger-110"></i>
                </button>
            </div>  
                
                
<!--              <button class="btn btn-purple btn-sm" onclick="search();">查询
                    <i class="icon-search icon-on-right bigger-110"></i>
               </button>-->
         </form>
        </div>
            <div class="col-xs-12">
                <!-- PAGE CONTENT BEGINS -->
                <div class="row">
                    <div class="col-xs-12">
                        <div class="table-responsive">
                            <table id="sample-table-1" class="table table-striped table-bordered table-hover">
                                <thead>
                                <tr>
                                        <th>流水号</th>
                                        <th>用户名称</th>
                                        <th>手机号码</th>
                                        <th>业务类型</th>
                                        <th>商品名称</th>
                                        <th>注册体系</th>    
                                        <th>商机来源</th>
                                        <th>状态</th>
                                                    <th>顾问</th>
                                        <th>创建时间</th>                    
                                </tr>
                                </thead>

                                <{foreach from = $lists item = info1 key=key}>
                                <tr>
                                    <td><{$info1.bidd}></td>
                                    <td style='text-align: left; padding-left: 55px'><b><{$info1.b_uname}></b></td>
                                    <td><{if $info1.b_iphone == ''}> 暂无
                                        <{else}><{$info1.b_iphone}><{/if}></td>
                                    <td><{$info1.b_type}></td>
                                    <td><{$info1.b_pro_name}></td>
                                    <td><{$info1.b_orgin}></td>
                                    <td><{$info1.b_from}></td>
                                    <td><{if $info1.adv_id!='' }> 已分配<{else}> 未分配 <{/if}></td>
                                                                        <td><{if $info1.adv_id!='' }> <{$info1.adv_id}><{else}> 暂无 <{/if}> </td>

<!--                                    <td><textarea>
                                           <{$info1.text}>
                                         </textarea>
                                     </td> -->
                                     <td><{$info1.b_create_time|date_format:'%Y-%m-%d %H:%M:%S'}></td>
                                </tr>
                                <{/foreach}>
                                
                            </table>
                            <div>
                            <{$pagesize}>
                            </div>
                        </div><!-- /.table-responsive -->
                    </div><!-- /span -->
                </div><!-- /row -->

            </div><!-- /.page-content -->
</div><!-- /.main-content -->
<{include file="admin/footer.html"}>

</body>
</html>
<script>
function search(){
    if($("#timepicker1").val() == "开始时间"){
        $("#timepicker1").val('');
    }
    if($("#timepicker2").val() == "结束时间"){
        $("#timepicker2").val('');
    }
}



</script>
<script src="/public/My97DatePicker/WdatePicker.js"></script>