noride/noridecrm/service/backend/models/Rebate.php

<?php

namespace backend\models;

use Yii;

class Rebate extends \yii\db\ActiveRecord
{
    
    /**
     * @return \yii\db\Connection the database connection used by this AR class.
     */
    public static function getDb()
    {
        return Yii::$app->get('dbXcrm');
    }

    /**
     * @param array $post
     * @return array
     */
    public function getList($post)
    {
        $result = ['code' => 0, 'data' => [], 'message' => ''];

        $member_id = $post['member_id'];
        $order = isset($post['order']) ? strtolower($post['order']) : '';
        $orderBy = isset($post['orderBy']) ? strtolower($post['orderBy']) : 'desc';
        $search = isset($post['search']) ? $post['search'] : '';

        $start = isset($post['start']) ? (int) $post['start'] : 0;
        $length = isset($post['length']) ? (int) $post['length'] : 20;
        $sTime = isset($post['sTime']) ? $post['sTime'] : '';
        $eTime = isset($post['eTime']) ? $post['eTime'] . ' 23:59:59' : '';
        $ibId = isset($post['ibId']) ? $post['ibId'] : 0;
        $draw = isset($post['draw']) ? $post['draw'] : 1;
        
        $where = ['and'];
        
        // 代理商，没有搜索条件就查当前用户
        $member = new Member();
        $ibs = $member->findChildrenIncludeSelf($member_id);
        $id_arr = array_column($ibs, 'id');
        if ($ibId && in_array($ibId, $id_arr)) {
            $where[] = ['=', 'ib_id', $ibId];
        } else {
            $where[] = ['=', 'ib_id', $member_id];
        }
        
        // 账户或订单号
        if ($search) {
            $where[] = [
                'or',
                ['like', 'user_login', $search],
                ['like', 'trade_ticket', $search],
            ];
        }
        
        // 时间，数据库的是毫秒时间戳
        if ($sTime) {
            $sTime = strtotime($sTime);
            if ($sTime) {
                $sTime *= 1000;
                $where[] = ['>=', 'in_time', $sTime];
            }
        }
        if ($eTime) {
            $eTime = strtotime($eTime);
            if ($eTime) {
                $eTime *= 1000;
                $where[] = ['<=', 'in_time', $eTime];
            }
        }
        
        // 排序
        $allowOrderColumn = ['trade_ticket', 'user_login', 'trade_type', 'trade_volume', 'commission_rule', 'commission', 'in_time'];
        if (in_array($order, $allowOrderColumn) && in_array($orderBy, ['asc', 'desc'])) {
            if ($orderBy == 'asc') {
                $orderCondition = [$order => SORT_ASC];    
            } else {
                $orderCondition = [$order => SORT_DESC];
            }
        } else {
            $orderCondition = ['id' => SORT_DESC];
        }
        
        $query = CommissionRecord::find();
        $query->where($where)
            ->orderBy($orderCondition);
        
        $count = $query->count();
        $query->offset($start)->limit($length);
        $list = $query->asArray()->all();
        
        if ($count) {
            $userLogins = array_column($list, 'user_login');
            $mt4Users = Mt4Users::find()->where(['in', 'login', $userLogins])->asArray()->all();
            foreach ($list as $k => $v) {
                $list[$k]['name'] = '';
                $list[$k]['in_time'] = (int) $v['in_time'];
                foreach ($mt4Users as $k2 => $v2) {
                    if ($v['user_login'] == $v2['LOGIN']) {
                        $list[$k]['name'] = $v2['NAME'];
                        break;
                    }
                }
            }
            
            // 计算手数和佣金，放到最后一行
            $query = CommissionRecord::find();
            $sum = $query->select(['SUM(trade_volume) AS trade_volume', 'SUM(commission) AS commission'])->where($where)->asArray()->limit(1)->one();
            $lastRow = [
                'commission' => (float) $sum['commission'],
                'commission_rule' => '',
                'in_time' => '',
                'name' => '',
                'trade_ticket' => '',
                'trade_type' => '99',
                'trade_volume' => (float) $sum['trade_volume'],
                'user_login' => '',
            ];
            array_push($list, $lastRow);
            
        } else {
            // 没有数据则输出默认值
            $list = [
                [
                    'commission' => 0,
                    'commission_rule' => '',
                    'in_time' => '',
                    'name' => '',
                    'trade_ticket' => '',
                    'trade_type' => '99',
                    'trade_volume' => 0,
                    'user_login' => '',
                ],
            ];
        }

        $data['data'] = $list;
        $data['draw'] = $draw;
        $data['recordsFiltered'] = $count;
        $data['recordsTotal'] = $count;
        
        $result['data'] = $data;
        $result['code'] = 1;
        
        return $result;
    }

}