trends/worklife/service/backend/models/Volume.php

<?php  

namespace backend\models;

use Yii;
use common\helpers\Utils;
use common\helpers\DateHelper;

class Volume 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']) ? $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']) ? DateHelper::convertDateToGmt($post['sTime']) : $this->defaultStartTime();
        $eTime = isset($post['eTime']) ? DateHelper::convertDateToGmt($post['eTime']. ' 23:59:59') : $this->defaultEndTime() . ' 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)) {
            $id_arr = [$ibId];
        }
        if ($id_arr) {
            $where[] = ['in', 'cum.member_id', $id_arr];
        }
        
        // 账户或姓名
        if ($search) {
            $where[] = [
                'or',
                ['like', 'mu.LOGIN', $search],
                ['like', 'mu.NAME', $search],
            ];
        }
        
        // 排序
        $allowOrderColumn = ['name', 'LOGIN', 'NAME'];
        if (in_array($order, $allowOrderColumn) && in_array($orderBy, ['asc', 'desc'])) {
            if ($order === 'name') {
                $order = 'cum.' . $order;
            } elseif ($order === 'NAME') {
                $order = 'mu.' . $order;
            }
            if ($order === 'LOGIN') {
                $order = 'mu.LOGIN';
            }
            if ($orderBy == 'asc') {
                $orderCondition = [$order => SORT_ASC];
            } else {
                $orderCondition = [$order => SORT_DESC];
            }
        } else {
            $orderCondition = ['mu.LOGIN' => SORT_DESC];
        }
        
        $query = UserMember::find();
        $query->alias('cum')
            ->select('cum.*, mu.*')
            ->innerJoin('mt4_users mu', 'cum.login = mu.LOGIN')
            ->where($where)
            ->orderBy($orderCondition);
        
        $count = $query->count();
        $query->offset($start)->limit($length);
        $list = $query->asArray()->all();
        
        if ($count) {
            $logins = array_column($list, 'login');
            
            // 配置，一定会有
            $config = Config::find()->asArray()->limit(1)->one();
            // forex
            $forexSymbols = preg_split('/\s*,\s*/', trim($config['forex_symbols']), -1, PREG_SPLIT_NO_EMPTY);
            // gold
            $xauSymbols = preg_split('/\s*,\s*/', trim($config['xau_symbols']), -1, PREG_SPLIT_NO_EMPTY);
            // silver
            $xagSymbols = preg_split('/\s*,\s*/', trim($config['xag_symbols']), -1, PREG_SPLIT_NO_EMPTY);
            // cfd
            $cfdSymbols = preg_split('/\s*,\s*/', trim($config['cfd_symbols']), -1, PREG_SPLIT_NO_EMPTY);
            // stock
            $stockSymbols = preg_split('/\s*,\s*/', trim($config['stock_symbols']), -1, PREG_SPLIT_NO_EMPTY);
            // btc
            $btcSymbols = preg_split('/\s*,\s*/', trim($config['btc_symbols']), -1, PREG_SPLIT_NO_EMPTY);
            
            $mt4Trades = new Mt4Trades();

            // 外汇，黄金，白银，CFD，股指，btc的交易量列表
            $fvList = $mt4Trades->getSumVolumeEachLogin($logins, $forexSymbols, $sTime, $eTime);
            $mvGoldList = $mt4Trades->getSumVolumeEachLogin($logins, $xauSymbols, $sTime, $eTime);
            $mvSilverList = $mt4Trades->getSumVolumeEachLogin($logins, $xagSymbols, $sTime, $eTime);
            $cvList = $mt4Trades->getSumVolumeEachLogin($logins, $cfdSymbols, $sTime, $eTime);
            $stvList = $mt4Trades->getSumVolumeEachLogin($logins, $stockSymbols, $sTime, $eTime);
            $btvList = $mt4Trades->getSumVolumeEachLogin($logins, $btcSymbols, $sTime, $eTime);

            // 外汇，黄金，白银，CFD，股指，btc的内佣列表，trade_type 外汇0，CFD1，黄金2，白银3，股指4，BTC5
            $fvCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 0, $sTime, $eTime);
            $mvGoldCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 2, $sTime, $eTime);
            $mvSilveCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 3, $sTime, $eTime);
            $cvCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 1, $sTime, $eTime);
            $stvCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 4, $sTime, $eTime);
            $btvCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 5, $sTime, $eTime);

            // 外汇，黄金，白银的外佣列表，CFD，股指，BTC没有外佣
            $fvCommissionWyList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 0, $sTime, $eTime, true);
            $mvGoldCommissionWyList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 2, $sTime, $eTime, true);
            $mvSilveCommissionWyList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 3, $sTime, $eTime, true);

            // 处理数据
            foreach ($list as $k => $v) {
                $list[$k]['MARGIN'] = Utils::formatFloatOrInt($v['MARGIN']);
                $list[$k]['MARGIN_FREE'] = Utils::formatFloatOrInt($v['MARGIN_FREE']);
                $list[$k]['MARGIN_LEVEL'] = Utils::formatFloatOrInt($v['MARGIN_LEVEL']);
                $list[$k]['EQUITY'] = Utils::formatFloatOrInt($v['EQUITY']);
                $list[$k]['MODIFY_TIME'] = date('m-d H:i', strtotime($v['MODIFY_TIME']));
                
                // 外汇，黄金，白银，CFD，股指，BTC交易量
                $list[$k]['fv'] = !empty($fvList[$v['login']]['total_volume']) ? (float) $fvList[$v['login']]['total_volume'] : 0;
                $list[$k]['mvGold'] = !empty($mvGoldList[$v['login']]['total_volume']) ? (float) $mvGoldList[$v['login']]['total_volume'] : 0;
                $list[$k]['mvSilver'] = !empty($mvSilverList[$v['login']]['total_volume']) ? (float) $mvSilverList[$v['login']]['total_volume'] : 0;
                $list[$k]['cv'] = !empty($cvList[$v['login']]['total_volume']) ? (float) $cvList[$v['login']]['total_volume'] : 0;
                $list[$k]['stv'] = !empty($stvList[$v['login']]['total_volume']) ? (float) $stvList[$v['login']]['total_volume'] : 0;
                $list[$k]['btv'] = !empty($btvList[$v['login']]['total_volume']) ? (float) $btvList[$v['login']]['total_volume'] : 0;

                // 外汇佣金
                $list[$k]['fvc'] = !empty($fvCommissionList[$v['login']]['total_commission']) ? (float) $fvCommissionList[$v['login']]['total_commission'] : 0;
                
                // 黄金佣金
                $list[$k]['gvc'] = !empty($mvGoldCommissionList[$v['login']]['total_commission']) ? (float) $mvGoldCommissionList[$v['login']]['total_commission'] : 0;
                
                // 白银佣金
                $list[$k]['svc'] = !empty($mvSilveCommissionList[$v['login']]['total_commission']) ? (float) $mvSilveCommissionList[$v['login']]['total_commission'] : 0;
                
                // CFD佣金
                $list[$k]['cvc'] = !empty($cvCommissionList[$v['login']]['total_commission']) ? (float) $cvCommissionList[$v['login']]['total_commission'] : 0;

                // 股指佣金
                $list[$k]['stvc'] = !empty($stvCommissionList[$v['login']]['total_commission']) ? (float) $stvCommissionList[$v['login']]['total_commission'] : 0;

                // BTC佣金
                $list[$k]['btvc'] = !empty($btvCommissionList[$v['login']]['total_commission']) ? (float) $btvCommissionList[$v['login']]['total_commission'] : 0;
                
                // 总外佣，CFD，股指，BTC没有外佣
                $list[$k]['wvc'] = 0;
                if (!empty($fvCommissionWyList[$v['login']]['total_commission'])) {
                    $list[$k]['wvc'] += (float) $fvCommissionWyList[$v['login']]['total_commission'];
                }
                if (!empty($mvGoldCommissionWyList[$v['login']]['total_commission'])) {
                    $list[$k]['wvc'] += (float) $mvGoldCommissionWyList[$v['login']]['total_commission'];
                }
                if (!empty($mvSilveCommissionWyList[$v['login']]['total_commission'])) {
                    $list[$k]['wvc'] += (float) $mvSilveCommissionWyList[$v['login']]['total_commission'];
                }
                
                // 总佣金
                $list[$k]['tvc'] = $list[$k]['fvc'] + $list[$k]['gvc'] + $list[$k]['svc'] + $list[$k]['cvc'] + $list[$k]['stvc'] + $list[$k]['btvc'] + $list[$k]['wvc'];
            }
        }

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

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

        $member_id = $post['member_id'];
        $sTime = isset($post['sTime']) ? DateHelper::convertDateToGmt($post['sTime']) : $this->defaultStartTime();
        $eTime = isset($post['eTime']) ? DateHelper::convertDateToGmt($post['eTime']. ' 23:59:59') : $this->defaultEndTime() . ' 23:59:59';
        $ibId = isset($post['ibId']) ? $post['ibId'] : 0;

        $where = ['and'];

        // 代理商，没有搜索条件就查所有
        $member = new Member();
        $ibs = $member->findChildrenIncludeSelf($member_id);
        $id_arr = array_column($ibs, 'id');
        if ($ibId && in_array($ibId, $id_arr)) {
            $id_arr = [$ibId];
        }
        if ($id_arr) {
            $where[] = ['in', 'cum.member_id', $id_arr];
        }

        $query = UserMember::find();
        $query->alias('cum')
            ->select('cum.login')
            ->innerJoin('mt4_users mu', 'cum.login = mu.LOGIN')
            ->where($where);

        $count = $query->count();
        $list = $query->asArray()->all();

        $fvSum = 0;
        $mvGoldSum = 0;
        $mvSilverSum = 0;
        $cvSum = 0;
        $stvSum = 0;
        $btvSum = 0;
        $fvcSum = 0;
        $gvcSum = 0;
        $svcSum = 0;
        $cvcSum = 0;
        $stvcSum = 0;
        $btvcSum = 0;
        $wvcSum = 0;
        $tvcSum = 0;
        if ($count) {
            $logins = array_column($list, 'login');

            // 配置，一定会有
            $config = Config::find()->asArray()->limit(1)->one();
            // forex
            $forexSymbols = preg_split('/\s*,\s*/', trim($config['forex_symbols']), -1, PREG_SPLIT_NO_EMPTY);
            // gold
            $xauSymbols = preg_split('/\s*,\s*/', trim($config['xau_symbols']), -1, PREG_SPLIT_NO_EMPTY);
            // silver
            $xagSymbols = preg_split('/\s*,\s*/', trim($config['xag_symbols']), -1, PREG_SPLIT_NO_EMPTY);
            // cfd
            $cfdSymbols = preg_split('/\s*,\s*/', trim($config['cfd_symbols']), -1, PREG_SPLIT_NO_EMPTY);
            // 股指
            $stockSymbols = preg_split('/\s*,\s*/', trim($config['stock_symbols']), -1, PREG_SPLIT_NO_EMPTY);
            // btc
            $btcSymbols = preg_split('/\s*,\s*/', trim($config['btc_symbols']), -1, PREG_SPLIT_NO_EMPTY);

            $mt4Trades = new Mt4Trades();
            
            // 外汇，黄金，白银，CFD，股指，BTC的总交易量
            $fvSum = $mt4Trades->getSumVolume($logins, $forexSymbols, $sTime, $eTime);
            $mvGoldSum = $mt4Trades->getSumVolume($logins, $xauSymbols, $sTime, $eTime);
            $mvSilverSum = $mt4Trades->getSumVolume($logins, $xagSymbols, $sTime, $eTime);
            $cvSum = $mt4Trades->getSumVolume($logins, $cfdSymbols, $sTime, $eTime);
            $stvSum = $mt4Trades->getSumVolume($logins, $stockSymbols, $sTime, $eTime);
            $btvSum = $mt4Trades->getSumVolume($logins, $btcSymbols, $sTime, $eTime);

            // 外汇，黄金，白银，CFD，股指，BTC的交易量列表
            $fvList = $mt4Trades->getSumVolumeEachLogin($logins, $forexSymbols, $sTime, $eTime);
            $mvGoldList = $mt4Trades->getSumVolumeEachLogin($logins, $xauSymbols, $sTime, $eTime);
            $mvSilverList = $mt4Trades->getSumVolumeEachLogin($logins, $xagSymbols, $sTime, $eTime);
            $cvList = $mt4Trades->getSumVolumeEachLogin($logins, $cfdSymbols, $sTime, $eTime);
            $stvList = $mt4Trades->getSumVolumeEachLogin($logins, $stockSymbols, $sTime, $eTime);
            $btvList = $mt4Trades->getSumVolumeEachLogin($logins, $btcSymbols, $sTime, $eTime);

            // 外汇，黄金，白银，CFD，股指，BTC的内佣列表，trade_type 外汇0，CFD1，黄金2，白银3，股指4，BTC5
            $fvCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 0, $sTime, $eTime);
            $mvGoldCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 2, $sTime, $eTime);
            $mvSilveCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 3, $sTime, $eTime);
            $cvCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 1, $sTime, $eTime);
            $stvCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 4, $sTime, $eTime);
            $btvCommissionList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 5, $sTime, $eTime);

            // 外汇，黄金，白银的外佣列表，CFD，股指，BTC没有外佣
            $fvCommissionWyList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 0, $sTime, $eTime, true);
            $mvGoldCommissionWyList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 2, $sTime, $eTime, true);
            $mvSilveCommissionWyList = $mt4Trades->getSumCommissionEachLogin($member_id, $logins, 3, $sTime, $eTime, true);

            // 计算佣金
            foreach ($list as $k => $v) {
                // 外汇，黄金，白银，CFD，股指，BTC交易量
                $list[$k]['fv'] = !empty($fvList[$v['login']]['total_volume']) ? (float) $fvList[$v['login']]['total_volume'] : 0;
                $list[$k]['mvGold'] = !empty($mvGoldList[$v['login']]['total_volume']) ? (float) $mvGoldList[$v['login']]['total_volume'] : 0;
                $list[$k]['mvSilver'] = !empty($mvSilverList[$v['login']]['total_volume']) ? (float) $mvSilverList[$v['login']]['total_volume'] : 0;
                $list[$k]['cv'] = !empty($cvList[$v['login']]['total_volume']) ? (float) $cvList[$v['login']]['total_volume'] : 0;
                $list[$k]['stv'] = !empty($stvList[$v['login']]['total_volume']) ? (float) $stvList[$v['login']]['total_volume'] : 0;
                $list[$k]['btv'] = !empty($btvList[$v['login']]['total_volume']) ? (float) $btvList[$v['login']]['total_volume'] : 0;

                // 外汇佣金
                if (!empty($fvCommissionList[$v['login']]['total_commission'])) {
                    $fvcSum += (float) $fvCommissionList[$v['login']]['total_commission'];
                }

                // 黄金佣金
                if (!empty($mvGoldCommissionList[$v['login']]['total_commission'])) {
                    $gvcSum += (float) $mvGoldCommissionList[$v['login']]['total_commission'];
                }

                // 白银佣金
                if (!empty($mvSilveCommissionList[$v['login']]['total_commission'])) {
                    $svcSum += (float) $mvSilveCommissionList[$v['login']]['total_commission'];
                }

                // CFD佣金
                if (!empty($cvCommissionList[$v['login']]['total_commission'])) {
                    $cvcSum += (float) $cvCommissionList[$v['login']]['total_commission'];
                }

                // 股指佣金
                if (!empty($stvCommissionList[$v['login']]['total_commission'])) {
                    $stvcSum += (float) $stvCommissionList[$v['login']]['total_commission'];
                }

                // BTC佣金
                if (!empty($btvCommissionList[$v['login']]['total_commission'])) {
                    $btvcSum += (float) $btvCommissionList[$v['login']]['total_commission'];
                }

                // 总外佣，CFD，股指，BTC没有外佣
                if (!empty($fvCommissionWyList[$v['login']]['total_commission'])) {
                    $wvcSum += (float) $fvCommissionWyList[$v['login']]['total_commission'];
                }
                if (!empty($mvGoldCommissionWyList[$v['login']]['total_commission'])) {
                    $wvcSum += (float) $mvGoldCommissionWyList[$v['login']]['total_commission'];
                }
                if (!empty($mvSilveCommissionWyList[$v['login']]['total_commission'])) {
                    $wvcSum += (float) $mvSilveCommissionWyList[$v['login']]['total_commission'];
                }
            }
            // 总佣金
            $tvcSum += $fvcSum + $gvcSum + $svcSum + $cvcSum + $stvcSum + $wvcSum;
        }
        
        $result['data'] = [
            'fvSum' => $fvSum,
            'mvGoldSum' => $mvGoldSum,
            'mvSilverSum' => $mvSilverSum,
            'cvSum' => $cvSum,
            'stvSum' => $stvSum,
            'btvSum' => $btvSum,
            'fvcSum' => $fvcSum,
            'gvcSum' => $gvcSum,
            'svcSum' => $svcSum,
            'cvcSum' => $cvcSum,
            'stvcSum' => $stvcSum,
            'btvcSum' => $btvcSum,
            'wvcSum' => $wvcSum,
            'tvcSum' => $tvcSum,
        ];
        $result['code'] = 1;
        
        return $result;
    }

    /**
     * 默认开始时间，默认从上个星期天开始
     * @return string
     */
    protected function defaultStartTime()
    {
        return date('Y-m-d', strtotime('last sunday'));
    }

    /**
     * 默认结束时间，默认今天
     * @return string
     */
    protected function defaultEndTime()
    {
        return date('Y-m-d');
    }
}