handz/worklife2/crm/backend/modules/ib/controllers/VolumeController.php

<?php
namespace backend\modules\ib\controllers;

use common\helpers\ExcelHelper;
use Yii;
use backend\models\MemberApi;
use backend\models\VolumeApi;
use yii\helpers\Url;
use common\helpers\UserAgentHelper;

class VolumeController extends BaseController
{
    
    public function actionIndex()
    {
        $request = Yii::$app->request;
        $ibId = (int) $request->get('ibId');
        $sTime = $request->get('sTime', $this->defaultStartTime()); // 开始时间，默认从上个星期天开始
        $eTime = $request->get('eTime', $this->defaultEndTime()); // 结束时间
        
        $api = new MemberApi();
        $ibs = $api->getIbs()['data'];

        return $this->render('index', [
            'ibId' => $ibId,
            'sTime' => $sTime,
            'eTime' => $eTime,
            'ibs' => $ibs,
            'isHaveAdmin' => Yii::$app->user->identity->isHaveAdmin(),
        ]);
    }

    /**
     * 列表数据
     */
    public function actionList()
    {
        $get = Yii::$app->request->get();
        $orderInt = isset($get['order'][0]['column']) ? (int) $get['order'][0]['column'] : 1;
        $order = isset($get['columns'][$orderInt]['data']) ? $get['columns'][$orderInt]['data'] : '';
        $orderBy = isset($get['order'][0]['dir']) ? $get['order'][0]['dir'] : 'desc';
        $search = isset($get['search']['value']) ? $get['search']['value'] : '';
        
        $start = isset($get['start']) ? (int) $get['start'] : 0;
        $length = isset($get['length']) ? (int) $get['length'] : 20;
        $sTime = isset($get['sTime']) ? $get['sTime'] : $this->defaultStartTime();
        $eTime = isset($get['eTime']) ? $get['eTime'] : $this->defaultEndTime();
        $ibId = isset($get['ibId']) ? $get['ibId'] : 0;
        $draw = isset($get['draw']) ? $get['draw'] : 1;
        
        $params = [
            'order' => $order,
            'orderBy' => $orderBy,
            'search' => $search,
            'start' => $start,
            'length' => $length,
            'sTime' => $sTime,
            'eTime' => $eTime,
            'ibId' => $ibId,
            'draw' => $draw,
        ];

        if ($params['length'] > 5000) {
            if (Yii::$app->getRequest()->getIsAjax()) {
                Yii::$app->getResponse()->redirect(Url::current(), UserAgentHelper::isIE() ? 200 : 302);
            } else {
                $this->exportXls($params);
            }
            Yii::$app->end();
        }
        
        $api = new VolumeApi();
        $result = $api->getList($params);
        echo json_encode($result['data']);
    }
    
    /**
     * 计算总数
     */
    public function actionCal()
    {
        $get = Yii::$app->request->get();
        $sTime = isset($get['sTime']) ? $get['sTime'] : $this->defaultStartTime();
        $eTime = isset($get['eTime']) ? $get['eTime'] : $this->defaultEndTime();
        $ibId = isset($get['ibId']) ? $get['ibId'] : 0;

        $params = [
            'sTime' => $sTime,
            'eTime' => $eTime,
            'ibId' => $ibId,
        ];

        $api = new VolumeApi();
        $result = $api->cal($params);
        echo json_encode($result['data']);
    }

    /**
     * 导出到Excel
     */
    public function actionExport()
    {
        $get = Yii::$app->request->get();

        $sTime = isset($get['sTime']) ? $get['sTime'] : $this->defaultStartTime();
        $eTime = isset($get['eTime']) ? $get['eTime'] : $this->defaultEndTime();
        $ibId = isset($get['ibId']) ? $get['ibId'] : 0;
        $length = 10000;

        $params = [
            'sTime' => $sTime,
            'eTime' => $eTime,
            'ibId' => $ibId,
            'length' => $length,
        ];

        $api = new VolumeApi();
        $result = $api->getList($params);
        if (empty($result['data']['data'])) {
            echo '没有数据';
            exit;
        }
        $list = $result['data']['data'];
        $cal = $api->cal($params)['data'];
        
        require Yii::$app->vendorPath . '/phpoffice/phpexcel/Classes/PHPExcel.php';
        require Yii::$app->vendorPath . '/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php';

        $filename = 'Data volume report';
        $objExcel = new \PHPExcel();
        $objProps = $objExcel->getProperties();
        $objProps->setCreator('AIKE');
        $objProps->setTitle($filename);
        $objExcel->setActiveSheetIndex(0);

        $objActSheet = $objExcel->getActiveSheet();
        $objActSheet->setTitle('交易量报表');

        // 表头
        $ExcelKey = array(
            'A' => '代理商',
            'B' => '账户',
            'C' => '姓名',
            'D' => '外汇',
            'E' => '佣金',
            'F' => '黄金',
            'G' => '佣金',
            'H' => '白银',
            'I' => '佣金',
            'J' => 'CFD',
            'K' => '佣金',
            'L' => '股指',
            'M' => '佣金',
            'N' => 'BTC',
            'O' => '佣金',
            'P' => '总外佣',
            'Q' => '总佣金',
        );
        foreach ($ExcelKey as $key => $val) {
            $objActSheet->setCellValue($key . '1', $val);
        }
        
        $rowNum = 2; // 行号
        foreach ($list as $k => $v) {
            $objActSheet->setCellValue('A' . $rowNum, $v['name']);
            $objActSheet->setCellValue('B' . $rowNum, $v['LOGIN']);
            $objActSheet->setCellValue('C' . $rowNum, $v['NAME']);
            $objActSheet->setCellValue('D' . $rowNum, $v['fv'] / 100); // 外汇
            $objActSheet->setCellValue('E' . $rowNum, $v['fvc'] / 100); // 外汇佣金
            $objActSheet->setCellValue('F' . $rowNum, $v['mvGold'] / 100); // 黄金
            $objActSheet->setCellValue('G' . $rowNum, $v['gvc'] / 100); // 黄金佣金
            $objActSheet->setCellValue('H' . $rowNum, $v['mvSilver'] / 100); // 白银
            $objActSheet->setCellValue('I' . $rowNum, $v['svc'] / 100); // 白银佣金
            $objActSheet->setCellValue('J' . $rowNum, $v['cv'] / 100); // CFD
            $objActSheet->setCellValue('K' . $rowNum, $v['cvc'] / 100); // CFD佣金
            $objActSheet->setCellValue('L' . $rowNum, $v['stv'] / 100); // 股指
            $objActSheet->setCellValue('M' . $rowNum, $v['stvc'] / 100); // 股指佣金
            $objActSheet->setCellValue('N' . $rowNum, $v['btv'] / 100); // BTC
            $objActSheet->setCellValue('O' . $rowNum, $v['btvc'] / 100); // BTC佣金
            $objActSheet->setCellValue('P' . $rowNum, $v['wvc'] / 100); // 外佣
            $objActSheet->setCellValue('Q' . $rowNum, $v['tvc'] / 100); // 总佣金
            
            $rowNum++;
        }

        // 在最后一行加上统计数据
        $objActSheet->setCellValue('D' . $rowNum, $cal['fvSum'] / 100);
        $objActSheet->setCellValue('E' . $rowNum, $cal['fvcSum'] / 100);
        $objActSheet->setCellValue('F' . $rowNum, $cal['mvGoldSum'] / 100);
        $objActSheet->setCellValue('G' . $rowNum, $cal['gvcSum'] / 100);
        $objActSheet->setCellValue('H' . $rowNum, $cal['mvSilverSum'] / 100);
        $objActSheet->setCellValue('I' . $rowNum, $cal['svcSum'] / 100);
        $objActSheet->setCellValue('J' . $rowNum, $cal['cvSum'] / 100);
        $objActSheet->setCellValue('K' . $rowNum, $cal['cvcSum'] / 100);
        $objActSheet->setCellValue('L' . $rowNum, $cal['stvSum'] / 100);
        $objActSheet->setCellValue('M' . $rowNum, $cal['stvcSum'] / 100);
        $objActSheet->setCellValue('N' . $rowNum, $cal['btvSum'] / 100);
        $objActSheet->setCellValue('O' . $rowNum, $cal['btvcSum'] / 100);
        $objActSheet->setCellValue('P' . $rowNum, $cal['wvcSum'] / 100);
        $objActSheet->setCellValue('Q' . $rowNum, $cal['tvcSum'] / 100);

        header('Pragma:public');
        header('Content-Type:application/x-msexecl;name="' . $filename . '.xls');
        header("Content-Disposition:inline;filename=" . $filename . '.xls');
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
        $objWriter->save('php://output');
    }

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

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

    /**
     * @param array $params
     */
    protected function exportXls($params)
    {
        ExcelHelper::init();
        $attachmentName = Yii::$app->user->identity->name . "_交易量报表_" . date('Y-m-d') . '.xlsx';
        $header = ['代理商', '账户', '姓名', '外汇', '佣金', '黄金', '佣金', '白银', '佣金', 'CFD', '佣金', '股指', '佣金', '总外佣', '总佣金'];
        $source = [];
        $api = new VolumeApi();
        $params['length'] = 5000;
        while (true) {
            $result = $api->getList($params);
            if ($result['code'] == 1 && !empty($result['data']['data'])) {
                foreach ((array)$result['data']['data'] as $key => $row) {
                    $row['fv'] = $row['fv'] / 100;
                    $row['fvc'] = $row['fvc'] / 100;
                    $row['mvGold'] = $row['mvGold'] / 100;
                    $row['gvc'] = $row['gvc'] / 100;
                    $row['mvSilver'] = $row['mvSilver'] / 100;
                    $row['svc'] = $row['svc'] / 100;
                    $row['cv'] = $row['cv'] / 100;
                    $row['cvc'] = $row['cvc'] / 100;
                    $row['stv'] = $row['stv'] / 100;
                    $row['stvc'] = $row['stvc'] / 100;
                    $row['wvc'] = $row['wvc'] / 100;
                    $row['tvc'] = $row['tvc'] / 100;
                    $arr = [];
                    $arr[] = $row['name'];
                    $arr[] = $row['LOGIN'];
                    $arr[] = $row['NAME'];
                    $arr[] = $row['fv'];
                    $arr[] = $row['fvc'];
                    $arr[] = $row['mvGold'];
                    $arr[] = $row['gvc'];
                    $arr[] = $row['mvSilver'];
                    $arr[] = $row['svc'];
                    $arr[] = $row['cv'];
                    $arr[] = $row['cvc'];
                    $arr[] = $row['stv'];
                    $arr[] = $row['stvc'];
                    $arr[] = $row['wvc'];
                    $arr[] = $row['tvc'];
                    $source[] = $arr;
                }
            } else {
                break;
            }
            $params['start'] += $params['length'];
            if ($params['start'] >= $result['data']['recordsTotal']) {
                break;
            }
        }
        $sum = $api->cal($params)['data'];
        foreach ($sum as $k => $v) {
            $sum[$k] = $v / 100;
        }
        $summaryRow = [];
        $summaryRow[] = '合计';
        $summaryRow[] = '';
        $summaryRow[] = '';
        $summaryRow[] = $sum['fvSum'];
        $summaryRow[] = $sum['fvcSum'];
        $summaryRow[] = $sum['mvGoldSum'];
        $summaryRow[] = $sum['gvcSum'];
        $summaryRow[] = $sum['mvSilverSum'];
        $summaryRow[] = $sum['svcSum'];
        $summaryRow[] = $sum['cvSum'];
        $summaryRow[] = $sum['cvcSum'];
        $summaryRow[] = $sum['stvSum'];
        $summaryRow[] = $sum['stvcSum'];
        $summaryRow[] = $sum['wvcSum'];
        $summaryRow[] = $sum['tvcSum'];
        $source[] = $summaryRow;
        ExcelHelper::output($source, $header, $attachmentName);
    }
}