thinkPHP交易详情查询功能详解

5年以前 | 阅读数：407 次 | 编程语言：PHP

本文实例分析了thinkPHP交易详情查询功能。分享给大家供大家参考，具体如下：

交易详情

一般都是按月的，包含，交易日期，交易金额，交易状态（可有可无）
总交易额等等。
如果数据多的话，最好能够分页。
最好能够查询具体的哪一个商户。

1.模拟sql实现查询功能


    SELECT a.id as user_id,a.username,b.name as store_name,c.id as order_id,c.price,c.paytime,c.sendtime,c.receivetime FROM sh_user a
      LEFT JOIN sh_store b on a.id = b.user_id
      LEFT JOIN sh_order c ON b.id = c.store_id
      WHERE a.opener_id = 1 and a.`status` = 1 and c.status = 1 ORDER BY c.id desc;
    SELECT count(b.id) as count ,sum(c.price) as total_price FROM sh_user a
      LEFT JOIN sh_store b on a.id = b.user_id
      LEFT JOIN sh_order c ON b.id = c.store_id
      WHERE a.opener_id = 1 and a.`status` = 1 and c.status = 1;

sql查询出来了，基本上就搞定了，剩下的就是用php，thinkphp实现这个查询功能，加入一些逻辑与条件。


    // 商户交易
    public function trade(){
      if($type = $this->_request('type','trim')){
       $s_year = $this->_request('s_year','trim');
       $s_month = $this->_request('s_month','trim');
       $s_user_id = $this->_request('s_user_id','trim,intval');
       $this->assign('s_user_id',$s_user_id);
       if($type == 'last'){ // 获取上一月
        if($s_month==1){
         $useYear = $s_year-1;
         $useMonth = 12;
        }else{
         $useYear = $s_year;
         $useMonth = $s_month-1;
        }
       }
       if($type == 'next'){ // 获取下一月
        if($s_month==12){
         $useYear = $s_year+1;
         $useMonth = 1;
        }else{
         $useYear = $s_year;
         $useMonth = $s_month+1;
        }
       }
       if ($type == 'selectuser'){
        $useYear = $s_year;
        $useMonth = $s_month;
       }
      }else{
       // 获取当前年 月
       $useYear = date('Y'); 
       $useMonth = date('m'); 
      }
      $this->assign('s_year',$useYear);
      $this->assign('s_month',$useMonth);
      $b_time = strtotime($useYear.'-'.$useMonth.'-'.'1');
      $e_time = strtotime($useYear.'-'.$useMonth.'-'.date('t',strtotime($b_time)).' 23:59:59');
      if(isset($s_user_id) && $s_user_id > 0){
       $where['a.id'] = $s_user_id;
      }
      $where['a.opener_id'] = $this->opener_id;
      $where['a.status'] = 1; // 合法的用户
      $where['c.status'] = 1; // 合法的订单
      $where['c.paytime'] = array(array('gt',$b_time),array('lt',$e_time),'and');
      $count_and_totalprice = M()->table('sh_user a')
            ->join('sh_store b on a.id = b.user_id')
            ->join('sh_order c on b.id = c.store_id')
            ->where($where)
            ->field('count(b.id) as count ,sum(c.price) as totalprice')
            ->find();
      $count  = $count_and_totalprice['count'];
      $totalprice = $count_and_totalprice['totalprice'] ? $count_and_totalprice['totalprice'] : 0;
      $Page  = new Page($count, 10);
      $list  = M()->table('sh_user a')
         ->join('sh_store b on a.id = b.user_id')
         ->join('sh_order c on b.id = c.store_id')
         ->where($where)
         ->order('c.id desc')
         ->limit($Page->firstRow.','.$Page->listRows)
         ->field('a.id as user_id,a.username,b.name as store_name,c.id as order_id,c.price,c.paytime,c.sendtime,c.receivetime')
         ->select();
      foreach ($list as $k => $v) {
       if($v['sendtime'] == 0 && $v['receivetime'] == 0){
        $list[$k]['progress'] = '1'; // 已付款,待发货
       }
       if($v['sendtime'] > 0 && $v['receivetime'] == 0){
        $list[$k]['progress'] = '2'; // 已发货,待签收
       }
       if($v['sendtime'] > 0 && $v['receivetime'] > 0){
        $list[$k]['progress'] = '3'; // 交易完成
       }
      }
      // 获取拓展员用户
      $user_list = M('User')
          ->where(array('opener_id'=>$this->opener_id))
          ->field('id,username')
          ->select();
      $this->assign('user_list',$user_list);
      $this->assign('totalprice',$totalprice);
      $this->assign('page',$Page->show());
      $this->assign('list', $list);
      $this->display();
    }

html部分


    <include file="Public:head" title="交易详情" />
    <style>
    .top {
      background-color: #eee;
      height: 50px;
      line-height: 50px;
      font-size: 18px;
      border-bottom: #ddd 1px solid;
      margin-bottom: -1px;
    }
    .list-group{
      border: 1px solid #DDDDDD;
    }
    .list-group .list-group-item {
      text-align: left;
      line-height: 25px;
      border: none;
      background-color: #F9F9F9;
      font-size: 14px;
    }
    #select-date {
      padding: 0px 10px;
    }
    #select-date .date-txt {
      font-size: 18px;
    }
    #total {
      width: 140px;
      height: 140px;
      background-color: #EC6C00;
      margin: auto;
    }
    #total .money-txt {
      color: white;
      padding-top: 10px;
    }
    #datalist {
      margin-top: 30px;
    }
    #relief .form-control{
      margin-top: 10px;
      margin-bottom: 10px;
      /*background-color: #FFCE42;*/
    }
    .page{
      margin-right: 10px;
      margin-bottom: 20px;
    }
    .table th {
      color: #C4C4C4;
    }
    .table tbody tr td+td+td {
      color: #D3964F;
    }
    </style>
    <script type="text/javascript">
    function lastMonth(){
      todo('last');
    }
    function nextMonth(){
      todo('next');
    }
    function selectUser(){
      todo('selectuser');
    }
    function todo(type){
      var s_year = $('#s_year').val();
      var s_month = $('#s_month').val();
      var s_user_id = $('#s_user_id').val();
      window.location.href="{sh::U('User/trade')}&s;_year="+s_year+"&s;_month="+s_month+"&s;_user_id="+s_user_id+"&type;="+type;
    }
    </script>
    <body>
      <div data-example-id="list-group-btns" class="bs-example">
        <div id="select-date">
          <ul class="pager">
            <li class="previous"><a onclick="lastMonth();"><span aria-hidden="true"><-</span> </a></li>
            <span class="date-txt"><strong>{sh:$s_year}.{sh:$s_month}</strong>
            <present name="paymentData"><span class="glyphicon glyphicon-ok-sign" aria-hidden="true"></span></present>
            </span>
            <input type="text" id="s_year" value="{sh:$s_year}" hidden="hidden">
            <input type="text" id="s_month" value="{sh:$s_month}" hidden="hidden">
            <li class="next"><a onclick="nextMonth();"><span aria-hidden="true">-></span></a></li>
          </ul>
        </div>
        <div id="relief">
          <select id="s_user_id" onchange="selectUser();" class="form-control btn-success">
            <option value="">全部商户</option>
            <volist name="user_list" id="vo">
              <option value="{sh:$vo.id}" <eq name="vo.id" value="$s_user_id">selected="selected"</eq>>{sh:$vo.username}</option>
            </volist>
          </select>
        </div>
        <div id="total" class="img-circle">
          <div class="text-center money-txt">
            <h3>总交易金额</h3>
            <h2>￥{sh:$totalprice}</h2>
          </div>
        </div>
        <div id="datalist">
          <table class="table table-striped">
            <thead>
              <tr>
                <th>商户</th>
                <th>日期</th>
                <th>交易金额</th>
                <!-- <th>状态</th> -->
              </tr>
            </thead>
            <tbody>
            <empty name="list"><tr><td class="text-center" colspan="4">暂无数据</td></tr></empty>
            <volist name="list" id="vo">
              <tr>
                <td>{sh:$vo.username}</td>
                <td>{sh:$vo.paytime|date="Y-m-d H:i",###}</td>
                <td>{sh:$vo.price}</td>
                <!-- <td>
                <if condition="$vo.progress eq 1"><span class="text-primary">待发货</span>
                <elseif condition="$vo.progress eq 2"/><span class="text-danger">待签收</span>
                <elseif condition="$vo.progress eq 3"/><span class="text-success"><strong>已完成</strong></span>
                </if>
                </td> -->
              </tr>
            </volist>
            </tbody>
          </table>
          <div class="page text-right">
            {sh:$page}
          </div>
        </div>
    </body>
    </html>

效果，多看看别人的设计，多学学，最重要的就是界面展示，一切的数据都是基于几面展示，所以先确定好需要什么数据，然后获取他们。