thinkPHP交易详情查询功能详解

 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; 

 // 商户交易 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'] = '<a style="color:transparent">来源gao*daima.com搞@代#码网</a>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(); } 

  .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; }  <div class="bs-example"> <div id="select-date"> <ul class="pager"> <li class="previous"><span>←</span></li><span class="date-txt"><strong>{sh:$s_year}.{sh:$s_month}</strong><span class="glyphicon glyphicon-ok-sign"></span></span><li class="next"><span>→</span></li></ul></div><div id="relief">  全部商户 <option value="{sh:$vo.id}" selected="selected">{sh:$vo.username}</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> <tr><td class="text-center" colspan="4">暂无数据</td></tr> <tr> <td>{sh:$vo.username}</td><td>{sh:$vo.paytime|date="Y-m-d H:i",###}</td><td>{sh:$vo.price}</td><!-- <td> <span class="text-primary">待发货</span> <span class="text-danger">待签收</span> <span class="text-success"><strong>已完成</strong></span>  </td> --></tr></tbody></table><div class="page text-right"> {sh:$page} </div></div>