466 lines
21 KiB
PHP
466 lines
21 KiB
PHP
<?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'=>'失败')));
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
}
|
|
|