screen/application/admin/controller/Mobile.php

<?php

namespace app\admin\controller;

use app\admin\library\Auth;
use app\admin\model\Admin;
use app\admin\model\MobileExportLog;
use app\common\controller\Backend;
use app\common\library\MobileConstant;
use app\common\model\MobileInfo;
use app\common\model\MobileSub;
use app\common\service\MobileExport;
use app\common\service\MobileImport;
use app\common\service\SubService;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use think\App;
use think\Cache;
use think\Config;
use think\Db;
use think\Exception;
use think\exception\PDOException;
use think\Loader;
use think\Url;
use app\admin\model\Mobile as MO;

/**
 * 
 *
 * @icon fa fa-mobile
 */
class Mobile extends Backend
{
    protected $noNeedRight=['mobile_rules','status','constant','exclude_nums'];
    /**
     * Mobile模型对象
     * @var \app\admin\model\Mobile
     */
    protected $model = null;
    protected $relationSearch=true;

    public function _initialize()
    {
        //ini_set('memory_limit',-1);
        parent::_initialize();
        $this->model = new \app\admin\model\Mobile;
        $this->assign('status',\app\common\model\Mobile::$status);
        $this->assign('network',MobileConstant::getNetworkString());
        $this->assign('network_color',MobileConstant::getNetworkColor());
        $this->assign('network_select',MobileConstant::getNetworkSelect());
    }

    public function import()
    {
        //MobileImport::import(input('file'),$this->auth->id);
        MobileImport::saveFile(input('file'),$this->auth->id);
        $this->success('上传成功，请等待导入');
    }
    public function import_status_disabled(){
        //MobileImport::import(input('file'),$this->auth->id,1,\app\common\model\Mobile::cantOrderStatus());
        MobileImport::saveFile(input('file'),$this->auth->id,1,\app\common\model\Mobile::cantOrderStatus());
        $this->success('上传成功，请等待导入');
    }

    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */


    /**
     * 查看
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        list($where, $sort, $order, $offset, $limit,$page) = $this->buildindexparams();

        if($this->request->get('export')==1 && $this->auth->check('mobile/_mobile_export')){
            if(MobileExportLog::exists()){
                die('有未完成导出，请稍后再试');
            }
            $export=MobileExportLog::make($this->admin('id'));
            ini_set('memory_limit',-1);
            ini_set('max_execution_time',0);
            $db=Db::name('mobile');
            $db->getConnection()->setConfig('resultset_type','array');
            $list= $db
                ->where($where)
                ->where('mobile.type',1)
                ->orderRaw($this->getOrder())
                ->buildSql();
            //dd($list);
            $export['sql']=$list;
            $export->save();
            die('已添加到导出日志，请稍后查看');
            //return MobileExport::export($list,$this->admin());
        }

        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }

            $deleteMode=input('_delete_condition');
            if($deleteMode==='true'){
                if(!$this->auth->check('mobile/del_condition')){
                    $this->error('您无权进行此操作');
                }
                $num=$this->model
                    ->alias(null)
                    ->where($where)
                    ->where('mobile.type',1)
                    ->delete();
                \app\common\model\Mobile::deleteOtherTableInfo();
                $deleteConditionMsg=sprintf("成功删除%d个号码",$num);
            }

            if(!$this->admin('is_sub')){
                $list = $this->model
                    ->with(['info','proxy'])
                    ->where($where)
                    ->where('mobile.type',1)
                    ->orderRaw($this->getOrder())
                    ->page($page)
                    ->limit($limit)
                    ->select();
                    //->paginate($limit);
            }else{
                $list=$this->model
                    ->with(['info'])
                    ->join('mobile_sub','mobile_sub.mobile_id=mobile.id and mobile_sub.sub_admin_id='.$this->auth->id,'left')
                    ->field('mobile.*,mobile_sub.*')
                    ->where($where)
                    ->where('mobile.type',1)
                    ->orderRaw($this->getOrder())
                    ->page($page)
                    ->limit($limit)
                    ->select();
                    //->paginate($limit);
            }

            foreach ($list as $row) {
                $rules=[];
                foreach (MobileConstant::getFilters() as $rule=>$field){
                    foreach (array_values($field) as $column){
                        if($row[$column]==1){
                            $rules[]=$rule;
                        }
                    }
                }
                $row['rules']=array_values(array_unique($rules));

                if($this->admin('is_sub')) {
                    $row['mobile_sub'] = [
                        'sub_sort' => (int)$row['sub_sort'],
                        'sub_rec_time' => (int)$row['sub_rec_time'],
                        'sub_top_time' => (int)$row['sub_top_time'],
                    ];
                }
            }

            //$result = array("total" => $list->total(), "rows" => $list->items(),'input'=>input(),'delete_condition_msg'=>$deleteConditionMsg??null);
            $result = array("total" => 100000000, "rows" => $list,'input'=>input(),'delete_condition_msg'=>$deleteConditionMsg??null,'url'=>$this->request->url().'&export=1');

            return json($result);
        }
        $this->assign('no_type',array_column(MobileConstant::getNoType(),'name','id'));
        $this->assign('filters',MobileConstant::getFilters());
        return $this->view->fetch();
    }
    protected function getOrder(){
        $filter=json_decode(input('filter'),true);
        $default='mobile.id desc';
        return $default;
        if(!$filter||empty($filter['no'])){
            return $default;
        }
        $value=str_replace('%','',$filter['no']);
        if(!$value){
            return $default;
        }
        $lastNum=substr($value,-1);
        if(strlen($value)==1){
            return "FIELD(`mobile.filter_no_pos_11`,$lastNum) DESC";
        }else{
            $arr=[];
            $numLen=strlen($value);
            $idx=1;
            for ($i=11;$i>=2;$i--){
                if($idx>$numLen){
                    break;
                }
                $numPos=$value[$numLen-$idx];
                $arr[]="FIELD(mobile.filter_no_pos_{$i},$numPos) DESC";
                $idx++;
            }

            return implode(',',$arr);
        }
    }
    #秒杀
    public function mobile_kill()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildindexparams();

            $map=[];
            if($this->admin('is_sub')){
                $map['hold_chan']=$this->auth->id;
            }

            if(!$this->admin('is_sub')){
                $list = $this->model
                    ->with(['info','proxy'])
                    ->where($where)
                    ->where($map)
                    ->where('mobile.type',1)
                    ->where('mobile.is_activity',1)
                    ->orderRaw($this->getOrder())
                    ->paginate($limit);
            }else{
                $list=$this->model
                    ->with(['info'])
                    ->join('mobile_sub','mobile_sub.mobile_id=mobile.id and mobile_sub.sub_admin_id='.$this->auth->id,'left')
                    ->field('mobile.*,mobile_sub.*')
                    ->where($where)
                    ->where($map)
                    ->where('mobile.type',1)
                    ->where('mobile.is_activity',1)
                    ->orderRaw($this->getOrder())
                    ->paginate($limit);
            }

            foreach ($list as $row) {
                $rules=[];
                foreach (MobileConstant::getFilters() as $rule=>$field){
                    foreach (array_values($field) as $column){
                        if($row[$column]==1){
                            $rules[]=$rule;
                        }
                    }
                }
                $row['rules']=array_values(array_unique($rules));

                if($this->admin('is_sub')) {
                    $row['mobile_sub'] = [
                        'sub_sort' => (int)$row['sub_sort'],
                        'sub_rec_time' => (int)$row['sub_rec_time'],
                        'sub_top_time' => (int)$row['sub_top_time'],
                    ];
                }
            }

            $result = array("total" => $list->total(), "rows" => $list->items());

            return json($result);
        }
        $this->assign('no_type',array_column(MobileConstant::getNoType(),'name','id'));
        $this->assign('filters',MobileConstant::getFilters());
        return $this->view->fetch();
    }

    public function constant(){
        return json([
            'no_type'=>MobileConstant::getNoType(),
        ]);
    }

    #置顶推荐
    public function batch(){
        $this->validate($data=input(),[
            'id'=>'require',
            'field'=>['require','in:rec_time,top_time'],
            'status'=>'require',
        ]);
        $time=$data['status']?time():null;
        $this->model->where('id',$data['id'])->update([
            $data['field']=>$time
        ]);
        $this->success('','',[
            'status'=>$time,
        ]);
    }
    #设为特价
    public function setdiscount($ids){
        if($this->request->isGet()){
            return view();
        }else{
            $this->validate($data=input('row/a'),[
                'activity_time_end'=>['date','requireIf:activity_forever,0'],
                'activity_forever'=>['require','in:0,1'],
            ]);
            if($data['activity_forever']){
                $activity_time_end=null;
            }else {
                if (strtotime($data['activity_time_end']) <= time()) {
                    $this->error('请选择将来时间');
                }
                $activity_time_end=$data['activity_time_end'];
            }
            $this->model->whereIn('id',$ids)->update([
                'is_activity'=>1,
                'activity_time_end'=>$activity_time_end,
            ]);
            $this->success();
        }
    }
    #取消设为特价
    public function cancelsetdiscount(){
        $ids=input('ids/a');
        if($ids){
            $this->model->whereIn('id',$ids)->update([
                'is_activity'=>0,
                'activity_time_end'=>null,
            ]);
        }
        $this->success('');
    }
    #
    protected function subDisabled(){
        if($this->admin('is_manager')){
            return false;
        }
        return true;
    }
    protected function killDisabled($mobile){
        if($this->admin('is_manager')){
            return false;
        }
        if ($mobile['is_activity']) {
            return $mobile['hold_chan']!=$this->admin('id');
        }else{
            //!$this->auth->check('mobile/mobile_sort')
            return false;
        }
    }
    protected function canEditActivity(){

    }
    #预占
    public function takeit($ids){
        if($this->request->isGet()){
            $mobile=$this->model->find($ids);
            $this->assign('row',$mobile);
            $this->assign('subDisabled',$this->subDisabled());
            $this->assign('killDisabled',$this->killDisabled($mobile));
            return view();
        }else{
            Db::startTrans();
            $mobile=$this->model->lock(true)->find($ids);
            $data=input('row/a');
            $editField=[];
            if($this->admin('is_manager')){
                $editField=['amount_original','amount_di','amount_base','amount_charge','amount_kill','is_activity','activity_time_end'];
            }elseif (!$this->killDisabled($mobile)){
                $editField=['amount_kill','is_activity','activity_time_end'];
            }else{
                Db::rollback();
                $this->error('无权操作');
            }
            foreach ($editField as $field){
                $mobile[$field]=$data[$field];
            }
            if($mobile['amount_kill']<$mobile['amount_di']){
                Db::rollback();
                $this->error('秒杀价不能低于底价');
            }
            $change=$mobile->getChangedData();
            $admin=Admin::get($this->auth->id);
            if(isset($change['is_activity']) && $change['is_activity']){
                if($admin['sub']){
                    $mobile['hold_chan']=$this->auth->id;
                }
                $mobile['hold_user']=$this->auth->id;
            }
            $mobile->save();
            Db::commit();
            $this->success();
        }
    }

    public function edit($ids=null){
        if($this->request->isGet()){
            $mobile=$this->model->find($ids);
            $mobile['province']=\app\common\model\Area::where('id',$mobile['province_id'])->value('name');
            $mobile['city']=\app\common\model\Area::where('id',$mobile['city_id'])->value('name');
            if($this->admin('is_sub')){
                $sub=MobileSub::getBy($mobile,$this->admin());
                $mobile['rec_time']=$sub['sub_rec_time'];
                $mobile['top_time']=$sub['sub_top_time'];
                $mobile['sort']=$sub['sub_sort'];
            }
            $this->assign('row',$mobile);
            $this->assign('disabled',$this->subDisabled());
            $this->assign('otherSubDisabled',$this->killDisabled($mobile));
            return view();
        }else{
            $data=input('row/a');
            Db::startTrans();
            //$mobiles=$this->model->whereIn('id',$ids)->select();
            $mobile=$this->model->where('id',$ids)->lock(true)->find();
            if(!$mobile){
                $this->error('号码不存在');
            }
            $editField=[];
            if($this->admin('is_manager')){
                $editField=['network','proxy_id','brand','remark','status','amount_original','amount_di','amount_base','amount_charge','amount_kill','is_activity','activity_time_end','sort','top_time','rec_time'];
            }elseif (!$this->killDisabled($mobile)){
                $editField=['amount_kill','is_activity','activity_time_end'];
            }
            foreach ($editField as $field){
                $mobile[$field]=$data[$field];
            }
            /*foreach ($mobiles as $mobile){*/
                if($mobile['amount_kill']<$mobile['amount_di']){
                    $this->error('秒杀价不能低于底价');
                }
                if($this->admin('is_manager')) {
                    $city = $data['city'] ?? null;
                    if ($city) {
                        $ex = explode('/', $city);
                        list($data['province'], $data['city']) = $ex;
                        $data['province_id'] = \app\common\model\Area::getIdByName($data['province']);
                        $data['city_id'] = \app\common\model\Area::getIdByName($data['city']);
                    }
                    $describe = $data['describe'] ?? '';
                    if ($describe) {
                        $mobile->info()->update(compact('describe'));
                    }
                }else{
                    $sub=MobileSub::getBy($mobile,$this->admin());
                    if($this->service()->hasRecPower()){
                        $sub['sub_rec_time']=$data['rec_time'];
                    }
                    if($this->service()->hasTopPower()){
                        $sub['sub_top_time']=$data['top_time'];
                    }
                    if($this->service()->hasSortPower()){
                        $sub['sub_sort']=$data['sort'];
                    }
                    $sub->save();
                }
            $mobile->save();
            //$mobile->makeSortLine($this->admin());
            Db::commit();
            /*}*/
            $this->success();
        }
    }

    public function multi_edit($ids){
        if($this->request->isGet()) {
            return view();
        }else{
            $tempData=input('row/a');
            $data=[];
            $infoData=[];
            if(!empty($tempData['brand'])){
                $data['brand']=$tempData['brand'];
            }
            if(!empty($tempData['remark'])){
                $data['remark']=$tempData['remark'];
            }
            if(isset($tempData['top_time'])){
                $data['top_time']=$tempData['top_time'];
            }
            if(isset($tempData['rec_time'])){
                $data['rec_time']=$tempData['rec_time'];
            }
            if(!empty($tempData['describe'])){
                $infoData['describe']=$tempData['describe'];
            }

            if($data) {
                $mobiles = $this->model->whereIn('id', $ids)->select();
                foreach ($mobiles as $mobile) {
                    foreach ($data as $key => $value) {
                        $mobile[$key] = $value;
                        $mobile->save();
                    }
                }
            }
            if($infoData){
                MobileInfo::whereIn('mobile_id',$ids)->update($infoData);
            }
            $this->success();
        }
    }

    public function multi_edit_proxy(){
        if($this->request->isGet()) {
            return view();
        }else{
            $this->validate(input('row/a'),[
                'proxy_id'=>['require','integer'],
            ]);
            $this->model->whereIn('id',input('ids'))->update([
                'proxy_id'=>input('row.proxy_id'),
            ]);
            $this->success();
        }
    }

    public function multi_edit_status(){
        if($this->request->isGet()) {
            return view();
        }else{
            $this->validate(input('row/a'),[
                'status'=>['require','integer'],
            ]);
            $status=input('row.status');
            if(!isset(MO::beautiStatus()[$status])){
                $this->error('状态有误');
            }
            $this->model->whereIn('id',input('ids'))->update([
                'status'=>$status,
            ]);
            $this->success();
        }
    }

    protected function buildindexparams($searchfields = null, $relationSearch = null)
    {
        $searchfields = is_null($searchfields) ? $this->searchFields : $searchfields;
        $relationSearch = is_null($relationSearch) ? $this->relationSearch : $relationSearch;
        $search = $this->request->get("search", '');
        $filter = $this->request->get("filter", '');
        $op = $this->request->get("op", '', 'trim');
        $sort = $this->request->get("sort", !empty($this->model) && $this->model->getPk() ? $this->model->getPk() : 'id');
        $order = $this->request->get("order", "DESC");
        $offset = $this->request->get("offset/d", 0);
        $limit = $this->request->get("limit/d", 999999);
        //新增自动计算页码
        $page = $limit ? intval($offset / $limit) + 1 : 1;
        if ($this->request->has("page")) {
            $page = $this->request->get("page/d", 1);
        }
        $this->request->get([config('paginate.var_page') => $page]);
        $filter = (array)json_decode($filter, true);
        $op = (array)json_decode($op, true);
        $filter = $filter ? $filter : [];
        $where = [];
        $alias = [];
        $bind = [];
        $name = '';
        $aliasName = '';
        if (!empty($this->model) && $this->relationSearch) {
            $name = $this->model->getTable();
            $alias[$name] = Loader::parseName(basename(str_replace('\\', '/', get_class($this->model))));
            $aliasName = $alias[$name] . '.';
        }
        $sortArr = explode(',', $sort);
        foreach ($sortArr as $index => & $item) {
            $item = stripos($item, ".") === false ? $aliasName . trim($item) : $item;
        }
        unset($item);
        $sort = implode(',', $sortArr);
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            $where[] = [$aliasName . $this->dataLimitField, 'in', $adminIds];
        }
        if ($search) {
            $searcharr = is_array($searchfields) ? $searchfields : explode(',', $searchfields);
            foreach ($searcharr as $k => &$v) {
                $v = stripos($v, ".") === false ? $aliasName . $v : $v;
            }
            unset($v);
            $where[] = [implode("|", $searcharr), "LIKE", "%{$search}%"];
        }
        $index = 0;
        foreach ($filter as $k => $v) {
            if($k=='rules'){
                continue;
            }
            if (!preg_match('/^[a-zA-Z0-9_\-.]+$/', $k)) {
                continue;
            }
            $sym = isset($op[$k]) ? $op[$k] : '=';
            if (stripos($k, ".") === false) {
                $k = $aliasName . $k;
            }
            $v = !is_array($v) ? trim($v) : $v;
            $sym = strtoupper(isset($op[$k]) ? $op[$k] : $sym);
            //null和空字符串特殊处理
            if (!is_array($v)) {
                if (in_array(strtoupper($v), ['NULL', 'NOT NULL'])) {
                    $sym = strtoupper($v);
                }
                if (in_array($v, ['""', "''"])) {
                    $v = '';
                    $sym = '=';
                }
            }

            switch ($sym) {
                case '=':
                case '<>':
                    $where[] = [$k, $sym, (string)$v];
                    break;
                case 'LIKE':
                case 'NOT LIKE':
                case 'LIKE %...%':
                case 'NOT LIKE %...%':
                    $where[] = [$k, trim(str_replace('%...%', '', $sym)), "%{$v}%"];
                    break;
                case '>':
                case '>=':
                case '<':
                case '<=':
                    $where[] = [$k, $sym, intval($v)];
                    break;
                case 'FINDIN':
                case 'FINDINSET':
                case 'FIND_IN_SET':
                    $v = is_array($v) ? $v : explode(',', str_replace(' ', ',', $v));
                    $findArr = array_values($v);
                    foreach ($findArr as $idx => $item) {
                        $bindName = "item_" . $index . "_" . $idx;
                        $bind[$bindName] = $item;
                        $where[] = "FIND_IN_SET(:{$bindName}, `" . str_replace('.', '`.`', $k) . "`)";
                    }
                    break;
                case 'IN':
                case 'IN(...)':
                case 'NOT IN':
                case 'NOT IN(...)':
                    $where[] = [$k, str_replace('(...)', '', $sym), is_array($v) ? $v : explode(',', $v)];
                    break;
                case 'BETWEEN':
                case 'NOT BETWEEN':
                    $arr = array_slice(explode(',', $v), 0, 2);
                    if (stripos($v, ',') === false || !array_filter($arr)) {
                        continue 2;
                    }
                    //当出现一边为空时改变操作符
                    if ($arr[0] === '') {
                        $sym = $sym == 'BETWEEN' ? '<=' : '>';
                        $arr = $arr[1];
                    } elseif ($arr[1] === '') {
                        $sym = $sym == 'BETWEEN' ? '>=' : '<';
                        $arr = $arr[0];
                    }
                    $where[] = [$k, $sym, $arr];
                    break;
                case 'RANGE':
                case 'NOT RANGE':
                    $v = str_replace(' - ', ',', $v);
                    $arr = array_slice(explode(',', $v), 0, 2);
                    if (stripos($v, ',') === false || !array_filter($arr)) {
                        continue 2;
                    }
                    //当出现一边为空时改变操作符
                    if ($arr[0] === '') {
                        $sym = $sym == 'RANGE' ? '<=' : '>';
                        $arr = $arr[1];
                    } elseif ($arr[1] === '') {
                        $sym = $sym == 'RANGE' ? '>=' : '<';
                        $arr = $arr[0];
                    }
                    $tableArr = explode('.', $k);
                    if (count($tableArr) > 1 && $tableArr[0] != $name && !in_array($tableArr[0], $alias) && !empty($this->model)) {
                        //修复关联模型下时间无法搜索的BUG
                        $relation = Loader::parseName($tableArr[0], 1, false);
                        $alias[$this->model->$relation()->getTable()] = $tableArr[0];
                    }
                    $where[] = [$k, str_replace('RANGE', 'BETWEEN', $sym) . ' TIME', $arr];
                    break;
                case 'NULL':
                case 'IS NULL':
                case 'NOT NULL':
                case 'IS NOT NULL':
                    $where[] = [$k, strtolower(str_replace('IS ', '', $sym))];
                    break;
                default:
                    break;
            }
            $index++;
        }
        if (!empty($this->model)) {
            $this->model->alias($alias);
        }
        if(isset($filter['rules']) && $filter['rules']){
            $temp=[];
            foreach (MobileConstant::getFilters()[$filter['rules']] as $pos=>$column){
                $temp[]=$column;
            }
            $flip=array_flip($temp);

            if(isset($filter['filter_first_last'])){
                if($filter['filter_first_last']==1){
                    $where[] = [$temp[1],1];
                }elseif ($filter['filter_first_last']==2){
                    $where[] = [$temp[0],1];
                }
            }else {
                $where[] = [implode("|", $temp), 1];
            }
        }

        $model = $this->model;
        $where = function ($query) use ($where, $alias, $bind, &$model,$filter) {
            if (!empty($model)) {
                $model->alias($alias);
                $model->bind($bind);
            }
            foreach ($where as $k => $v) {
                if (is_array($v)) {
                    if(in_array($v[0],['mobile_sub.sub_top_time','mobile_sub.sub_rec_time'])){
                        if($v[2]) {
                            $query->whereIn($v[0], 1);
                        }else{
                            $query->where(function ($query)use ($v){
                                $query->whereRaw("{$v[0]}=0 or {$v[0]} is null");
                            });
                        }
                        continue;
                    }elseif ($v[0]=='proxy.nickname'){
                        $proxy_id=Admin::where('proxy',1)->where('nickname','like',"%{$v[2]}%")->column('id')?:[0];
                        $query->whereIn('proxy_id',$proxy_id);
                        continue;
                    }elseif ($v[0]=='mobile.exclude_nums'){
                        $arr=explode(',',$v[2]);
                        foreach ($arr as $num){
                            if(!is_numeric($num)){
                                continue;
                            }
                            $query->where(sprintf("filter_num_%s",$num),0);
                        }
                        continue;
                    }elseif ($v[0]=='mobile.filter_first_last'){
                        continue;
                    }elseif ($v[0]=='mobile.no'){
                        $noSearch=$filter['no'];
                        if(isset($filter['filter_first_last'])){
                            if($filter['filter_first_last']==1) {
                                $noPad = str_pad($noSearch, 11, 'a',STR_PAD_LEFT);
                                for ($i = 11; $i >1; $i--) {
                                    if ($noPad[$i-1] !== 'a') {
                                        $query->where("filter_no_pos_{$i}", $noPad[$i-1]);
                                    }
                                }
                            }elseif ($filter['filter_first_last']==2){
                                $query->where('filter_no_pos_11','<>',$noSearch[strlen($noSearch)-1])->whereRaw("LOCATE('{$noSearch}',no)");
                            }
                        }else{
                            //$query->where('no','like',"%{$noSearch}%");
                            $query->whereRaw("LOCATE('{$noSearch}',no)");
                        }
                        continue;
                    }
                    call_user_func_array([$query, 'where'], $v);
                } else {
                    $query->where($v);
                }
            }
        };
        return [$where, $sort, $order, $offset, $limit, $page, $alias, $bind];
    }

    public function status(){
        return \app\common\model\Mobile::$status;
    }

    public function add()
    {
        if($this->request->isPost()) {
            $data = input('row/a');
            if (!empty($data['city'])) {
                $data['city'] = explode('/', $data['city'])[1];
            }
            Db::startTrans();
            $this->validate($data, \app\admin\validate\Mobile::class);
            $data['type'] = 1;
            $info = [];
            if (isset($data['describe'])) {
                $info['describe'] = $data['describe'];
                unset($data['describe']);
            }
            $mobile = $this->model::create($data);
            $mobile->info()->save($info);
            Db::commit();
            $this->success();
        }else{
            return view();
        }
    }
    public function export($ids){
        $mobiles=$this->model->whereIn('id',$ids)->select();
        $excel = new Spreadsheet();
        $excel->getProperties()
            ->setCreator("admin")
            ->setLastModifiedBy("admin")
            ->setTitle("导出号码")
            ->setSubject("导出号码");
        $excel->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
        $excel->getDefaultStyle()->getFont()->setSize(12);
        $excel->getDefaultStyle()->applyFromArray(
            array(
                'fill'      => array(
                    'type'  => Fill::FILL_SOLID,
                    'color' => array('rgb' => '000000')
                ),
                'font'      => array(
                    'color' => array('rgb' => "000000"),
                ),
                'alignment' => array(
                    'vertical'   => Alignment::VERTICAL_CENTER,
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                    'indent'     => 1
                ),
                'borders'   => array(
                    'allborders' => array('style' => Border::BORDER_THIN),
                )
            ));
        $excel->getActiveSheet()->setCellValue('A1','ID');
        $excel->getActiveSheet()->setCellValue('B1','手机号');
        $excel->getActiveSheet()->setCellValue('C1','省份');
        $excel->getActiveSheet()->setCellValue('D1','归属地');
        $excel->getActiveSheet()->setCellValue('E1','运营商');
        $excel->getActiveSheet()->setCellValue('F1','供应商');
        $excel->getActiveSheet()->setCellValue('G1','卡品牌');
        $excel->getActiveSheet()->setCellValue('H1','规律');
        $excel->getActiveSheet()->setCellValue('I1','套餐信息');
        $excel->getActiveSheet()->setCellValue('J1','原价');
        $excel->getActiveSheet()->setCellValue('K1','底价');
        $excel->getActiveSheet()->setCellValue('L1','售价');
        $excel->getActiveSheet()->setCellValue('M1','秒杀价');
        $excel->getActiveSheet()->setCellValue('N1','预存话费');
        $excel->getActiveSheet()->setCellValue('O1','备注');
        $excel->getActiveSheet()->setCellValue('P1','置顶');
        $excel->getActiveSheet()->setCellValue('Q1','推荐');
        $excel->getActiveSheet()->setCellValue('R1','号码状态');
        $excel->getActiveSheet()->setCellValue('S1','更新时间');
        $excel->getActiveSheet()->setCellValue('T1','上架时间');
        $excel->getActiveSheet()->setCellValue('U1','排序');
        $excel->getActiveSheet()->setCellValue('V1','预占通道');
        $excel->getActiveSheet()->setCellValue('W1','预占用户ID');
        $excel->getActiveSheet()->setCellValue('X1','上传用户');
        $i=2;
        foreach ($mobiles as $mobile){
            $rules=[];
            foreach (MobileConstant::getFilters() as $rule=>$field){
                foreach (array_values($field) as $column){
                    if($mobile[$column]==1){
                        $rules[]=$rule;
                    }
                }
            }
            $mobile['rules']=array_values(array_unique($rules));
            $status=$mobile['is_activity']?'预占':\app\common\model\Mobile::$status[$mobile['status']];
            $excel->getActiveSheet()->setCellValue('A'.$i,$mobile['id']);
            $excel->getActiveSheet()->setCellValue('B'.$i,$mobile['no']);
            $excel->getActiveSheet()->setCellValue('C'.$i,$mobile['province']);
            $excel->getActiveSheet()->setCellValue('D'.$i,$mobile['city']);
            $excel->getActiveSheet()->setCellValue('E'.$i,$mobile['network']);
            $excel->getActiveSheet()->setCellValue('F'.$i,$mobile['proxy']['nickname']??'');
            $excel->getActiveSheet()->setCellValue('G'.$i,$mobile['brand']);
            $excel->getActiveSheet()->setCellValue('H'.$i,implode(',',$mobile['rules']));
            $excel->getActiveSheet()->setCellValue('I'.$i,$mobile['info']['describe']??'');
            $excel->getActiveSheet()->setCellValue('J'.$i,$mobile['amount_original']);
            $excel->getActiveSheet()->setCellValue('K'.$i,$mobile['amount_di']);
            $excel->getActiveSheet()->setCellValue('L'.$i,$mobile['amount_base']);
            $excel->getActiveSheet()->setCellValue('M'.$i,$mobile['amount_kill']);
            $excel->getActiveSheet()->setCellValue('N'.$i,$mobile['amount_charge']);
            $excel->getActiveSheet()->setCellValue('O'.$i,$mobile['remark']);
            $excel->getActiveSheet()->setCellValue('P'.$i,$mobile['top_time']?'是':'否');
            $excel->getActiveSheet()->setCellValue('Q'.$i,$mobile['rec_time']?'是':'否');
            $excel->getActiveSheet()->setCellValue('R'.$i,$status);
            $excel->getActiveSheet()->setCellValue('S'.$i,date('Y-m-d H:i:s',$mobile['update_time']));
            $excel->getActiveSheet()->setCellValue('T'.$i,date('Y-m-d H:i:s',$mobile['create_time']));
            $excel->getActiveSheet()->setCellValue('U'.$i,$mobile['sort']);
            $excel->getActiveSheet()->setCellValue('V'.$i,$mobile['hold_chan']);
            $excel->getActiveSheet()->setCellValue('W'.$i,$mobile['hold_user']);
            $excel->getActiveSheet()->setCellValue('X'.$i,$mobile['admin_id']);
            $i++;
        }
        $excel->createSheet();
        // Redirect output to a client’s web browser (Excel2007)
        $title = date("YmdHis");
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0


        $objWriter = IOFactory::createWriter($excel, 'Xlsx');
        $objWriter->save('php://output');
    }
    public function force_update(){
        $this->success();
    }
    public function batch_set_amount($ids){
        if($this->request->isGet()){
            return view();
        }else{
            $data=input('row/a');
            $this->validate($data,[
                'amount_base|售价'=>['require','number','gt:0'],
                'amount_di|底价'=>['require','number','egt:0'],
            ]);
            $mobiles=$this->model->whereIn('id',$ids)->select();
            foreach ($mobiles as $mobile){
                $mobile['amount_base']=$data['amount_base'];
                $mobile['amount_di']=$data['amount_di'];
                $mobile->save();
            }
            $this->success();
        }
    }

    public function set_kill($ids){
        if($this->request->isGet()){
            return view();
        }else{
            $data=input('row/a');
            $this->validate($data,[
                'amount_kill|秒杀价'=>['require','number','gt:0'],
            ]);
            $mobiles=$this->model->whereIn('id',$ids)->select();
            Db::startTrans();
            foreach ($mobiles as $mobile){
                $mobile['amount_kill']=$data['amount_kill'];
                $mobile['is_activity']=1;
                if(!$mobile->save()){
                    Db::rollback();
                    $this->error('保存失败');
                }
            }
            Db::commit();
            $this->success();
        }
    }

    public function set_online_clear(){
        $type = input('type',1);
        if($this->request->isGet()) {
            $this->assign('type',$type);
            return view();
        }else{
            $data=[];
            $act=input('act');
            $reserve=input('reserve');
            if($type==1){
                switch ($act){
                    case 'check':
                        $num=$this->model->where('status',0)->group('no')->having('no_cf_count>1')->column('count(no) as no_cf_count');
                        $data['num']=array_sum($num);
                        break;
                    case 'del':
                        $data['num']=$this->set_online_clear_del($reserve,$type);
                        break;
                }
            }elseif ($type==2){
                switch ($act){
                    case 'check':
                        $num=$this->model->where('status',1)->group('no')->having('no_cf_count>1')->column('count(no) as no_cf_count');
                        $data['num']=array_sum($num);
                        break;
                    case 'del':
                        $data['num']=$this->set_online_clear_del($reserve,$type);
                        break;
                }
            }
            $this->result($data,1);
        }
    }

    protected function set_online_clear_del($reserve,$type){
        if(in_array($reserve,[1,2])){
            return $this->set_online_clear_query($type,'id',$reserve==1);
        }elseif(in_array($reserve,[3,4])){
            return $this->set_online_clear_query($type,'amount_base',$reserve==3);
        }elseif(in_array($reserve,[5,6])){
            return $this->set_online_clear_query($type,'amount_di',$reserve==5);
        }
    }

    protected function set_online_clear_query($type,$column,$asc){
        $map=[];
        if($type==1) {
            $map['status'] = ['eq',0];
        }elseif($type==2) {
            $map['status'] = ['eq',1];
        }
        $res=$this->model->where($map)->group('no')->having('num>1')->column("count(no) as num,no,group_concat(id) as ids,GROUP_CONCAT($column) as $column",'no');
        $ids=[];
        $num=0;
        foreach ($res as $item){
            $tempIds=explode(',',$item['ids']);
            $tempAmount=explode(',',$item[$column]);
            $tempNew=array_combine($tempIds,$tempAmount);
            $asc?asort($tempNew,1):arsort($tempNew,1);
            unset($tempNew[array_keys($tempNew)[0]]);
            //$ids=array_merge($ids,array_keys($tempNew));
            $newIds=array_keys($tempNew);
            $num+=count($newIds);
            $ids=array_merge($ids,$newIds);
            if(count($ids)>8000) {
                $this->model->whereIn('id', $ids)->delete();
                $ids=[];
            }
        }
        if($ids) {
            $this->model->whereIn('id', $ids)->delete();
        }
        return $num;
    }

    public function batch_copy_operation(){
        if($this->request->isGet()) {
            return view();
        }else{
            $num=0;
            $data=input('row/a');
            $this->validate($data,[
                'no|手机号'=>['require'],
                'act'=>'require',
            ]);
            $noFiltered=array_unique(array_filter(explode("\n",$data['no'])));
            if($data['act']=='delete'){
                $mobiles=$this->model->whereIn('no',$noFiltered)->select();
                Db::startTrans();
                foreach ($mobiles as $mobile){
                    $mobile->delete();
                }
                $num=count($mobiles);
                Db::commit();
            }elseif($data['act']=='up'){
                $num=$this->model->whereIn('no',$noFiltered)->update(['status'=>0]);
            }elseif($data['act']=='down'){
                $num=$this->model->whereIn('no',$noFiltered)->update(['status'=>2]);
            }elseif ($data['act']=='top'){
                if($noFiltered){
                    $mobiles=$this->model->where('top_time',0)->whereIn('no',$noFiltered)->select();
                    foreach ($mobiles as $mobile){
                        $mobile['top_time']=1;
                        $mobile->save();
                    }
                    $num=$mobiles->count();
                }
            }elseif ($data['act']=='rec'){
                if($noFiltered){
                    $mobiles=$this->model->where('rec_time',0)->whereIn('no',$noFiltered)->select();
                    foreach ($mobiles as $mobile){
                        $mobile['rec_time']=1;
                        $mobile->save();
                    }
                    $num=$mobiles->count();
                }
            }
            $this->result(['num'=>$num],1);
        }
    }

    public function mobile_rules(){
        return MobileConstant::getRuleKeys();
    }

    public function activity_cancel($ids){
        $mobile=$this->model->findOrFail($ids);
        if(!$mobile['is_activity']){
            $this->error('该号码没有被预占无法取消');
        }
        if($this->admin('is_proxy')){
            $this->error('您是供应商，无法使用此功能');
        }
        if($this->admin('sub') && $mobile['hold_chan']!=$this->auth->id){
            $this->error('您无法取消');
        }
        $mobile->makeNotActivity();
        $this->success();
    }
    public function batch_activity_cancel($ids){
        $map=[];
        if($this->admin('is_sub')){
            $map['hold_chan']=$this->admin('id');
        }
       $this->model->whereIn('id',$ids)->where($map)->update([
           'is_activity'=>0,
           'activity_time'=>0,
           'hold_chan' =>0,
           'hold_user' =>0,
       ]);
       $this->success();
    }

    /** 排序 */
    public function mobile_sort($ids){
        $mobile=$this->model->find($ids);
        if(!$mobile){
            return '';
        }
        $row=[
            'sort'=>0,
        ];
        if($this->request->isGet()){
            if($this->admin('is_sub')){
                $row['sort']=MobileSub::getBy($mobile,$this->admin())['sub_sort'];
            }else{
                $row['sort']=$mobile['sort'];
            }
            $this->assign('row',$row);
            return view();
        }else{
            $data=input('row/a');
            $this->validate($data,[
                'sort|排序'=>['require','integer','gt:0'],
            ]);
            if($this->admin('is_sub')){
                $mobileSub=MobileSub::getBy($mobile,$this->admin());
                $mobileSub['sub_sort']=$data['sort'];
                $mobileSub->save();
            }else{
                $mobile['sort']=$data['sort'];
                $mobile->save();
            }
            $this->success();
        }
    }

    public function del_all(){
        $data=['num'=>0];
        $data['num']=\app\admin\model\Mobile::where('type',1)->delete();
        \app\common\model\Mobile::deleteOtherTableInfo();
        $this->success('',null,$data);
    }

    public function exclude_nums(){
        return json([
            'list'=>[
                ['id'=>2,'title'=>'2'],
                ['id'=>3,'title'=>'3'],
                ['id'=>4,'title'=>'4'],
                ['id'=>5,'title'=>'5'],
                ['id'=>6,'title'=>'6'],
                ['id'=>7,'title'=>'7'],
                ['id'=>8,'title'=>'8'],
                ['id'=>9,'title'=>'9'],
                ['id'=>0,'title'=>'0'],
            ],
            'total'=>10,
        ]);
    }
}