thinkPHP交易詳情查詢功能

這篇文章主要介紹了thinkPHP交易詳情查詢功能,結合執行個體形式分析了thinkPHP資料庫查詢功能及視圖輸出相關操作技巧,需要的朋友可以參考下

本文執行個體分析了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>  <p data-example-id="list-group-btns" class="bs-example">    <p 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>    </p>    <p 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>    </p>    <p id="total" class="img-circle">      <p class="text-center money-txt">        <h3>總交易金額</h3>        <h2>￥{sh:$totalprice}</h2>      </p>    </p>    <p 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>      <p class="page text-right">        {sh:$page}      </p>    </p></body></html>

效果，多看看別人的設計，多學學，最重要的就是介面展示，一切的資料都是基於幾面展示，所以先確定好需要什麼資料，然後擷取他們。