<?php
namespace app\admin\controller;
use app\common\controller\Admin;
use think\Request;
use think\Loader;
use think\Cache;

/**
* 作品管理
*/
class Works extends Admin{

	public function _initialize(){
		 parent::_initialize();
	}

    public function index(){
        $where = array();
        $timewh = array();
		$get = input('');
		$channel = 2;
		$where['channel'] = 2;
		if($get){
			if(!empty($get['id'])){
				$where['id'] = $get['id'];
				$wid = $get['id'];
			}
			if(!empty($get['bkname'])){
				$where['title'] = array('like',"%".$get['bkname']."%");
				$wbkname = $get['bkname'];
			}
			if(!empty($get['type'])){
				$where['category_id'] = $get['type'];
			}
			if(!empty($get['booknumber'])){
				$where['booknumber'] = $get['booknumber'];
				$booknumber = $get['booknumber'];
			}
			if($get['sex']){
			    $where['channel'] = $get['sex'];
			    $channel = $get['sex'];
			}
	    }
		//小说分类列表
		$category_list = db('category')->field('id,title')->select();
		//列表
		$list = db('novel')->where($where)->order('allvisit desc')->paginate(10,false,['query' => request()->param(),]);
		$page = $list->render();
		$arrlist = $list->toArray();
		$arrlist = $arrlist['data'];
		foreach($arrlist as $k=>$v){
			//分类
			$arrlist[$k]['category'] = db('category')->where('id',$v['category_id'])->value('title');
			//今日充值次数
			$arrlist[$k]['day_count'] = db('order')->where(array(
				'articleid'=>$v['id'],
				'status'=>1
			))->where('iscontrol',0)->whereTime('create_time', 'today')->count('id');
			//今日充值
			$arrlist[$k]['day'] = db('order')->where(array(
				'articleid'=>$v['id'],
				'status'=>1
			))->where('iscontrol',0)->whereTime('create_time', 'today')->sum('money');
			$arrlist[$k]['day'] = $arrlist[$k]['day']?$arrlist[$k]['day']:0;
			
			//昨日充值次数
			$arrlist[$k]['zday_count'] = db('order')->where(array(
				'articleid'=>$v['id'],
				'status'=>1
			))->where('iscontrol',0)->whereTime('create_time', 'yesterday')->count('id');
			//昨日充值
			$arrlist[$k]['zday'] = db('order')->where(array(
				'articleid'=>$v['id'],
				'status'=>1
			))->where('iscontrol',0)->whereTime('create_time', 'yesterday')->sum('money');
			$arrlist[$k]['zday'] = $arrlist[$k]['zday']?$arrlist[$k]['zday']:0;
			
			//总充值次数
			$arrlist[$k]['money_count'] = db('order')->where(array(
				'articleid'=>$v['id'],
				'status'=>1
			))->where('iscontrol',0)->count('id');
			
			//总充值
			$arrlist[$k]['money'] = db('order')->where(array(
				'articleid'=>$v['id'],
				'status'=>1
			))->where('iscontrol',0)->where($timewh)->sum('money');
			$arrlist[$k]['money'] = $arrlist[$k]['money']?$arrlist[$k]['money']:0;
		}
		$list = $arrlist;
		$list = $this->array_sort($list,'day', 'desc');
        $data = array(
           'list'=>$list,
		   'page'=>$page,
		   'printime'=>empty($get['printime'])?'':date('Y-m-d H:i:s',$get['printime']),
		   'oftime'=>empty($get['oftime'])?'':date('Y-m-d H:i:s',$get['oftime']),
		   'category_list'=>$category_list,
		   'wid'=>empty($wid)?'':$wid,
		   'wbkname'=>empty($wbkname)?'':$wbkname,
		   'booknumber'=>empty($booknumber)?'':$booknumber,
		   'sex'=>$channel
		);
		$this->assign($data);   
        $this->setMeta('作品管理');
		return $this->fetch('index');
    }
    
    //充值留存分析
    public function analyze(){
        $id = input('id');
        $date = input('date');
        $data = db('history_read')->where('articleid',$id)->find();
        if($data){
    		$starttime = strtotime( date("Y-m-d") ); //今日凌晨
    		$endtime = time(); //当前时间
    		if(empty($date)){
    		    $date = 1;
    		}
            if($date == 2){
		        $starttime = strtotime( date("Y-m-d",strtotime("-1 day")) ); //昨日凌晨
    		    $endtime = strtotime( date("Y-m-d") ); //今日凌晨
            }
            if($date == 3){
		        $starttime = 946656000;
            }
    		
            $data['seenum'] = db('history_read')
                    ->where('articleid',$id)
                    ->whereTime('create_time', 'between', [$starttime,$endtime])
                    ->count('distinct(uid)');
                    
            $data['subscribe'] = db('subscription')
                    ->where('articleid',$id)
                    ->whereTime('create_time', 'between', [$starttime,$endtime])
                    ->sum('price');
                    
            $data['onenum'] = db('order')
                    ->where('articleid',$id)
                    ->where('status',1)
                    ->where('is_complex',1)
                    ->where('iscontrol',0)
                    ->whereTime('create_time', 'between', [$starttime,$endtime])
                    ->count('distinct(uid)');
                    
            $data['onemoney'] = db('order')
                ->where('articleid',$id)
                ->where('status',1)
                ->where('is_complex',1)
                ->where('iscontrol',0)
                ->whereTime('create_time', 'between', [$starttime,$endtime])
                ->sum('money');
            
            $data['twonum'] = db('order')
                    ->where('articleid',$id)
                    ->where('status',1)
                    ->where('is_complex',2)
                    ->where('iscontrol',0)
                    ->whereTime('create_time', 'between', [$starttime,$endtime])
                    ->count('distinct(uid)');
                    
            $data['twomoney'] = db('order')
                ->where('articleid',$id)
                ->where('status',1)
                ->where('is_complex',2)
                ->where('iscontrol',0)
                ->whereTime('create_time', 'between', [$starttime,$endtime])
                ->sum('money');
                
            $data['threenum'] = db('order')
                    ->where('articleid',$id)
                    ->where('status',1)
                    ->where(['is_complex'=>array('egt',3)])
                    ->where('iscontrol',0)
                    ->whereTime('create_time', 'between', [$starttime,$endtime])
                    ->count('distinct(uid)');
                    
            $data['threemoney'] = db('order')
                ->where('articleid',$id)
                ->where('status',1)
                ->where(['is_complex'=>array('egt',3)])
                ->where('iscontrol',0)
                ->whereTime('create_time', 'between', [$starttime,$endtime])
                ->sum('money');
            
            if($data['seenum']=='0'){
                $data['onerate'] = $data['onenum']=='0'?'0':100;
            }else{
                $data['onerate'] = $data['onenum']=='0'?'0':round(($data['onenum'] / $data['seenum'])*100,2);
            }
            if($data['onenum']=='0'){
                $data['tworate'] = $data['twonum']=='0'?'0':100;
            }else{
                $data['tworate'] = $data['twonum']=='0'?'0':round(($data['twonum'] / $data['onenum'])*100,2);
            }
            if($data['onenum']=='0'){
                $data['threerate'] = $data['threenum']=='0'?'0':100;
            }else{
                $data['threerate'] = $data['threenum']=='0'?'0':round(($data['threenum'] / $data['onenum'])*100,2);
            }
            if($data['onemoney']==null){
                $data['onemoney']=0;
            }
            if($data['twomoney']==null){
                $data['twomoney']=0;
            }
            if($data['threemoney']==null){
                $data['threemoney']=0;
            }
            
            $data['one'] = db('history_read')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[1,10])->count('distinct(uid)');
            
            $data['two'] = db('history_read')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[11,20])->count('distinct(uid)');
            
            $data['three'] = db('history_read')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[21,100])->count('distinct(uid)');
            
            $data['four'] = db('history_read')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[101,200])->count('distinct(uid)');
            
            $data['five'] = db('history_read')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[201,300])->count('distinct(uid)');
            
            $data['six'] = db('history_read')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[301,400])->count('distinct(uid)');
            
            $data['seven'] = db('history_read')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[401,500])->count('distinct(uid)');
            
            $data['eight'] = db('history_read')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','gt',500)->count('distinct(uid)');
            
            $data['rate1'] = $data['one']=='0'?'0':round(($data['one'] / $data['seenum'])*100,2);
            $data['rate2'] = $data['two']=='0'?'0':round(($data['two'] / $data['seenum'])*100,2);
            $data['rate3'] = $data['three']=='0'?'0':round(($data['three'] / $data['seenum'])*100,2);
            $data['rate4'] = $data['four']=='0'?'0':round(($data['four'] / $data['seenum'])*100,2);
            $data['rate5'] = $data['five']=='0'?'0':round(($data['five'] / $data['seenum'])*100,2);
            $data['rate6'] = $data['six']=='0'?'0':round(($data['six'] / $data['seenum'])*100,2);
            $data['rate7'] = $data['seven']=='0'?'0':round(($data['seven'] / $data['seenum'])*100,2);
            $data['rate8'] = $data['eight']=='0'?'0':round(($data['eight'] / $data['seenum'])*100,2);
            
            $one_sub = db('subscription')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[1,10])->sum('price');
            $data['one_sub'] = empty($one_sub)?0:$one_sub;
            
            $one_sub = db('subscription')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[11,20])->sum('price');
            $data['two_sub'] = empty($one_sub)?0:$one_sub;
            
            $one_sub = db('subscription')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[21,100])->sum('price');
            $data['three_sub'] = empty($one_sub)?0:$one_sub;
            
            $one_sub = db('subscription')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[101,200])->sum('price');
            $data['four_sub'] = empty($one_sub)?0:$one_sub;
            
            $one_sub = db('subscription')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[201,300])->sum('price');
            $data['five_sub'] = empty($one_sub)?0:$one_sub;
            
            $one_sub = db('subscription')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[301,400])->sum('price');
            $data['six_sub'] = empty($one_sub)?0:$one_sub;
            
            $one_sub = db('subscription')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','between',[401,500])->sum('price');
            $data['seven_sub'] = empty($one_sub)?0:$one_sub;
            
            $one_sub  = db('subscription')->where('articleid',$id)->whereTime('create_time', 'between', [$starttime,$endtime])->where('order','gt',500)->sum('price');
            $data['eight_sub'] = empty($one_sub)?0:$one_sub;
            
            $data['rate9'] = $data['one_sub']=='0'?'0':round(($data['one_sub'] / $data['subscribe'])*100,2);
            $data['rate10'] = $data['two_sub']=='0'?'0':round(($data['two_sub'] / $data['subscribe'])*100,2);
            $data['rate11'] = $data['three_sub']=='0'?'0':round(($data['three_sub'] / $data['subscribe'])*100,2);
            $data['rate12'] = $data['four_sub']=='0'?'0':round(($data['four_sub'] / $data['subscribe'])*100,2);
            $data['rate13'] = $data['five_sub']=='0'?'0':round(($data['five_sub'] / $data['subscribe'])*100,2);
            $data['rate14'] = $data['six_sub']=='0'?'0':round(($data['six_sub'] / $data['subscribe'])*100,2);
            $data['rate15'] = $data['seven_sub']=='0'?'0':round(($data['seven_sub'] / $data['subscribe'])*100,2);
            $data['rate16'] = $data['eight_sub']=='0'?'0':round(($data['eight_sub'] / $data['subscribe'])*100,2);
            // $data['id'] = $id;
            exit(json_encode(array('status'=>1,'reg'=>'成功','data'=>$data,'date'=>$date)));
        }else{
            exit(json_encode(array('status'=>2,'reg'=>'无数据')));
        }
    }
    
    //章节订阅详情
    public function subscribeinfo(){
        $data = input('');
        if($data){
            $id = $data['id'];
            $t = $data['t'];
            $date = $data['date'];
            
    		$starttime = strtotime( date("Y-m-d") ); //今日凌晨
    		$endtime = time(); //当前时间
            if($date == 2){
		        $starttime = strtotime( date("Y-m-d",strtotime("-1 day")) ); //昨日凌晨
    		    $endtime = strtotime( date("Y-m-d") ); //今日凌晨
            }
            if($date == 3){
		        $starttime = 946656000;
            }
            
            $map = array();
            $map['articleid'] = $id;
            if($t=='1'){
                $map['chapterorder'] = array('between',array(1,10));
            }elseif($t==2){
                $map['chapterorder'] = array('between',array(10,20));
            }elseif($t==3){
                $map['chapterorder'] = array('between',array(21,100));
            }elseif($t==4){
                $map['chapterorder'] = array('between',array(101,200));
            }elseif($t==5){
                $map['chapterorder'] = array('between',array(201,300));
            }elseif($t==6){
                $map['chapterorder'] = array('between',array(301,400));
            }elseif($t==7){
                $map['chapterorder'] = array('between',array(401,500));
            }elseif($t==8){
                $map['chapterorder'] = array('gt',500);
            }
            $list = db('chapter')->field('id,chapterorder,chaptername,isvip')->where($map)->order('chapterorder asc')->select();
            if($list){
		        foreach($list as $k=>$v){
		            $num = $v['chapterorder'] + 1;
	                $list[$k]['seenum'] = db('history_read')->where('articleid',$id)->where('order',$v['chapterorder'])->whereTime('create_time', 'between', [$starttime,$endtime])->count('distinct(uid)');
	                if($num > 0){
                        $list[$k]['survivenum'] = db('history_read')->where('articleid',$id)->where('order',$num)->whereTime('create_time', 'between', [$starttime,$endtime])->count('distinct(uid)');
                        if(empty($list[$k]['survivenum'])){
                            $list[$k]['survivenum'] = $list[$k]['seenum'];
                        }
	                }else{
	                    $list[$k]['survivenum'] = $list[$k]['seenum'];
	                }
	                if($list[$k]['survivenum'] > $list[$k]['seenum']){
	                    $list[$k]['survivenum'] = $list[$k]['seenum'];
                        $list[$k]['nums'] = 0;
	                }else{
                        $list[$k]['nums'] = $list[$k]['seenum'] - $list[$k]['survivenum'];
	                }
	               // $list[$k]['subscription'] = db('subscription')->where('articleid',$id)->where('order',$num)->whereTime('create_time', 'between', [$starttime,$endtime])->sum('price');
	                if(empty($list[$k]['subscription'])){
	                    $list[$k]['subscription'] = 0;
	                }
        	        if($v['isvip']==1){
        	            $list[$k]['seenum'] = '-';
        	            $list[$k]['nums'] = '-';
        			    $list[$k]['chaptername'] = $v['chaptername'].' vip';          
        	        }
		        }
                exit(json_encode(array('status'=>1,'reg'=>'成功','data'=>$list)));
            }else{
                exit(json_encode(array('status'=>2,'reg'=>'无数据')));
            }
        }
    }
    
    public function bookExcelList($id, $date){
        $booknumber = db('novel')->where('id',$id)->value('booknumber');
        
        $list = db('chapter')->field('chapterorder,chaptername,isvip')->where('articleid',$id)->order('chapterorder asc')->select();
        
        $starttime = strtotime( date("Y-m-d") ); //今日凌晨
        $endtime = time(); //当前时间
        $title = date("Y-m-d") .'-当天书编号-'.$booknumber;
        if($date == 2){
            $starttime = strtotime( date("Y-m-d",strtotime("-1 day")) ); //昨日凌晨
            $endtime = strtotime( date("Y-m-d") ); //今日凌晨
            
            $title = date("Y-m-d",strtotime("-1 day")) .'-当天书编号-'.$booknumber;
        }
        if($date == 3){
            $starttime = 946656000;
            $title = '-全部书编号-'.$booknumber;
        }
        if($list){
            foreach($list as $k=>$v){
                $num = $v['chapterorder'] + 1;
                $list[$k]['seenum'] = db('history_read')->where('articleid',$id)->where('order',$v['chapterorder'])->whereTime('create_time', 'between', [$starttime,$endtime])->count('distinct(uid)');
                if($num > 0){
                    $list[$k]['survivenum'] = db('history_read')->where('articleid',$id)->where('order',$num)->whereTime('create_time', 'between', [$starttime,$endtime])->count('distinct(uid)');
                    if(empty($list[$k]['survivenum'])){
                        $list[$k]['survivenum'] = $list[$k]['seenum'];
                    }
                }else{
                    $list[$k]['survivenum'] = $list[$k]['seenum'];
                }
                if($list[$k]['survivenum'] > $list[$k]['seenum']){
                    $list[$k]['survivenum'] = $list[$k]['seenum'];
                    $list[$k]['nums'] = 0;
                }else{
                    $list[$k]['nums'] = $list[$k]['seenum'] - $list[$k]['survivenum'];
                }
    	        if($v['isvip']==1){
    	            $list[$k]['seenum'] = '-';
    	            $list[$k]['nums'] = '-';
    			    $list[$k]['chaptername'] = $v['chaptername'].' vip';          
    	        }
    	        unset($list[$k]['isvip']);
            }
        }
        $field = array(
            'A' => array('chapterorder', '章节序号'),
            'B' => array('chaptername', '章节名称'),
            'C' => array('seenum', '新增人数'),
            'D' => array('nums', '离开人数'),
            'E' => array('survivenum', '留存人数'),
        );
		import("PHPExcel.PHPExcel", EXTEND_PATH,'.php');
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel); //设置保存版本格式
        foreach ($list as $key => $value) {
            foreach ($field as $k => $v) {
                if ($key == 0) {
                    $objPHPExcel->getActiveSheet()->setCellValue($k . '1', $v[1]);
                }
                $i = $key + 2; //表格是从2开始的
                $objPHPExcel->getActiveSheet()->setCellValue($k . $i, $value[$v[0]]);
            }

        }
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename='.$title.'.xls');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
	
	/**
	 * 排序
	 */
	public function array_sort($arr, $keys, $type='asc'){
		$keysvalue = $new_array = array();
		foreach ($arr as $k=>$v){
			$keysvalue[$k] = $v[$keys];
		}
		if($type == 'asc'){
			asort($keysvalue);
		}else{
			arsort($keysvalue);
		}
		reset($keysvalue);
		foreach ($keysvalue as $k=>$v){
			$new_array[] = $arr[$k];
		}
		return $new_array;
	}

	//设置兴趣推荐书籍
	public function setsavor(){   
	    $data = input('');
	    if($data){
	        if($data['t']=='1'){
	            $text = '取消设置';
	        }else{
	            $text = '设置';
	        }
	        $r = db('novel')->where('id',$data['id'])->update(['savor'=>$data['t']]);
	        if($r){
				exit(json_encode(array('status'=>1,'reg'=>'成功','text'=>$text,'class'=>$data['t'])));
			}else{
				exit(json_encode(array('status'=>2,'reg'=>'失败')));
			}
	    }
	}
	
	
	

}