<?php
namespace App\Http\Controllers\Manage\Book;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Modules\Book\Models\BookCp;
use App\Modules\Book\Models\BookConfig;
use DB;
use App\Modules\Subscribe\Models\BookOrder;
use Illuminate\Support\Facades\Storage;
class CpBookController extends Controller
{
/**
* @apiDefine BookCP 图书CP模块
*/
/**
* @apiVersion 1.0.0
* @apiDescription 获取所有cp
* @api {get} bookcp/getAllCp 获取所有cp
* @apiGroup BookCP
* @apiName getAllCp
* @apiSuccess {int} code 态码
* @apiSuccess {String} msg 信息
* @apiSuccess {object} data 结果集
* @apiSuccessExample {json} Success-Response:
* HTTP/1.1 200 OK
* {
* code: 0,
* msg: "",
* data: [
* "quyuewang",
* "tianya",
* "zhongtian",
* "ycsd",
* "zhizihuan",
* "timeread",
* "shuhai",
* ]
* }
*/
public function getAllCp(){
$cps = BookCp::select('cp')->get()->pluck('cp');
return response()->success($cps);
}
/**
* @apiVersion 1.0.0
* @apiDescription cp统计数据
* @api {get} bookcp/cpTotal cp统计数据
* @apiGroup BookCP
* @apiName cpTotal
* @apiParam {String} cp cp名称
* @apiParam {String} [export] 要导出需要传值等于1
* @apiSuccess {int} code 态码
* @apiSuccess {String} msg 信息
* @apiSuccess {object} data 结果集
* @apiSuccess {object} data.cp_source cp名称
* @apiSuccess {object} data.count 图书数量
* @apiSuccess {object} data.bids bids(每日数据需要传递的参数)
* @apiSuccess {object} data.yesterday 昨日订阅量
* @apiSuccess {object} data.month 本月订阅量
* @apiSuccess {object} data.prev_month 上月订阅量
* @apiSuccess {object} data.today 上月订阅量
* @apiSuccessExample {json} Success-Response:
* HTTP/1.1 200 OK
* {
* code: 0,
* msg: "",
* data: [
* {
* cp_source: "17k",
* bids: "21,22,582,583,584,591,612,629,634,635,637,638,639,641,642,843,844",
* count: 17,
* yesterday: "68194",
* month: "660188",
* prev_month: "55172"
* }
* ]
* }
*/
public function cpTotal2(Request $request){
$cp = $request->input('cp');
$res = BookConfig::select('cp_source',DB::raw('GROUP_CONCAT(bid) as bids'),DB::raw('count(*) as count'))->where('cp_source','!=','');
if($cp){
$res = $res->where('cp_source','like','%'.$cp.'%');
}
$res = $res->groupBy('cp_source')->get();
//return $res;
$yesterday = date('Y-m-d',time()-86400);
$today = date('Y-m-d');
$tomorrow = date('Y-m-d',time()+86400);
$month = date('Y-m').'-01';
$prev_month = date('Y-m',strtotime('-1 month')).'-01';
$next_month = date('Y-m',strtotime('+1 month')).'-01';
//echo $next_month;
$sql = '(select ifnull(sum(fee),0) as %s from chapter_order_total where bid in (%s) and day BETWEEN "%s" and "%s") %s';
$book_sql = '(select ifnull(sum(fee),0) as %s from book_orders where bid in (%s) and created_at BETWEEN "%s" and "%s") %s';
//BookOrder::join('book_config','book_orders.bid','=','book_config.bid')->select()->get();
foreach ($res as &$v){
$yesterday_sql = sprintf($sql,'yesterday',$v->bids,$yesterday,$today,'a');
$month_sql = sprintf($sql,'month',$v->bids,$month,$next_month,'b');
$prev_month_sql = sprintf($sql,'prev_month',$v->bids,$prev_month,$month,'c');
$total_sql = 'select * from ('.$yesterday_sql.' join '.$month_sql.' join '.$prev_month_sql.')';
$temp_res = DB::connection('chapter_order_mysql')->select($total_sql);
$book_yesterday_sql = sprintf($book_sql,'yesterday',$v->bids,$yesterday,$today,'a');
$book_month_sql = sprintf($book_sql,'month',$v->bids,$month,$next_month,'b');
$book_prev_month_sql = sprintf($book_sql,'prev_month',$v->bids,$prev_month,$month,'c');
$book_total_sql = 'select * from ('.$book_yesterday_sql.' join '.$book_month_sql.' join '.$book_prev_month_sql.')';
$book_temp_res = DB::select($book_total_sql);
$v->yesterday = (int)$book_temp_res[0]->yesterday+(int)$temp_res[0]->yesterday;
$v->month = (int)$book_temp_res[0]->month+(int)$temp_res[0]->month;
$v->prev_month = (int)$book_temp_res[0]->prev_month+(int)$temp_res[0]->prev_month;
$v->today = '-';
$temp_res = null;
$total_sql = null;
}
if($request->input('export')){
$filename = 'cptotal'.date('YmdHis').'.csv';
Storage::append($filename,mb_convert_encoding("书籍数量,今日订阅量,昨日订阅量,本月订阅量,上月订阅量",'gbk'));
$str = '';
foreach ($res as $val){
$str .= "{$val->count},{$val->today},{$val->yesterday},{$val->month},{$val->prev_month}\r\n";
}
Storage::append($filename,mb_convert_encoding($str,'gbk'));
return response()->download(storage_path('app/'.$filename))->deleteFileAfterSend(true);
}
return response()->success($res);
}
public function cpTotal(Request $request){
$cp = $request->input('cp');
$sql_format = '
SELECT "-" as today,a.bids,a.cp_source,a.count,a.yesterday,a.yesterday_charge_balance,a.yesterday_reward_balance,b.`month`,b.month_charge_balance,b.month_reward_balance,c.prev_month,c.prev_month_charge_balance,c.prev_month_reward_balance from(
(SELECT SUM(a.fee) as yesterday,sum(reward_balance) as yesterday_reward_balance,sum(charge_balance) as yesterday_charge_balance,b.cp_source,count(*) as `count`,GROUP_CONCAT(b.bid) as bids from book_order_statistical a join book_configs b on a.bid=b.bid where a.`day` = "%s" and %s GROUP BY b.cp_source) a
join
(SELECT SUM(a.fee)as `month`,sum(reward_balance) as month_reward_balance,sum(charge_balance) as month_charge_balance,b.cp_source from book_order_statistical a join book_configs b on a.bid=b.bid where a.`day` >= "%s" and a.`day` < "%s" and %s GROUP BY b.cp_source) b
on a.cp_source = b.cp_source
JOIN (
SELECT SUM(a.fee) as prev_month,sum(reward_balance) as prev_month_reward_balance,sum(charge_balance) as prev_month_charge_balance,b.cp_source from book_order_statistical a join book_configs b on a.bid=b.bid where a.`day` >= "%s" and a.`day`< "%s" and %s GROUP BY b.cp_source
) c on a.cp_source = c.cp_source
)
';
$yesterday = date('Y-m-d',time()-86400);
$today = date('Y-m-d');
$tomorrow = date('Y-m-d',time()+86400);
$month = date('Y-m').'-01';
$now_month = date('n');
if($now_month == 1){
$year = date('Y');
$prev_month = ($year-1).'-12-01';
}else{
$year = date('Y');
$prev_month = ($year).'-'.($now_month-1).'-01';
}
//$prev_month = date('Y-m',strtotime('-1 month')).'-01';
$next_month = date('Y-m',strtotime('+1 month')).'-01';
if($cp){
$where = 'b.cp_source like '.'"%'.$cp.'%"';
}else{
$where = '1=1';
}
$sql = sprintf($sql_format,$yesterday,$where,$month,$next_month,$where,$prev_month,$month,$where);
$res = DB::select($sql);
if($request->input('export')){
$filename = 'cptotal'.date('YmdHis').'.csv';
Storage::append($filename,mb_convert_encoding("书籍数量,今日订阅量,昨日订阅量,昨日订阅量充值币,昨日订阅量赠送币,本月订阅量,本月订阅量充值币,本月订阅量赠送币,上月订阅量,上月订阅量充值币,上月订阅量赠送币",'gbk'));
$str = '';
foreach ($res as $val){
$str .= "{$val->count},{$val->today},
{$val->yesterday},{$val->yesterday_charge_balance},{$val->yesterday_reward_balance},
{$val->month},{$val->month_charge_balance},{$val->month_reward_balance},
{$val->prev_month},{$val->prev_month_charge_balance},{$val->prev_month_reward_balance}\r\n";
}
Storage::append($filename,mb_convert_encoding($str,'gbk'));
return response()->download(storage_path('app/'.$filename))->deleteFileAfterSend(true);
}
return response()->success($res);
}
/**
* @apiVersion 1.0.0
* @apiDescription cp每日数据
* @api {get} bookcp/everyDayCpTotal cp每日数据
* @apiGroup BookCP
* @apiName everyDayCpTotal
* @apiParam {String} cp cp名称
* @apiParam {String} bids bids(cp统计数据返回的bids)
* @apiParam {String} [export] 要导出需要传值等于1
* @apiParam {String} page 页码
* @apiParam {String} pagesize 每页数据
* @apiSuccess {int} code 态码
* @apiSuccess {String} msg 信息
* @apiSuccess {object} data 结果集
* @apiSuccess {object} data.list.fee 订阅量
* @apiSuccess {object} data.list.day 图书数量量
* @apiSuccess {object} data.meta.last_page 最后意一页
* @apiSuccess {object} data.meta.current_page 当前页
* @apiSuccessExample {json} Success-Response:
* HTTP/1.1 200 OK
* {
* code: 0,
* msg: "",
* data: {
* list:[
* {
* fee: "96",
* day: "2017-12-21"
* },
* {
* fee: "1262",
* day: "2017-12-22"
* },
* {
* fee: "382",
* day: "2017-12-24"
* },
* ],
* meta{
* last_page:3,
* current_page:1
* }
* }
* }
*/
public function everyDayCpTotal(Request $request){
$cp = $request->input('cp');
$bids = $request->input('bids');
$page = (int)$request->input('page',1);
$pagesize = (int)$request->input('pagesize',15);
$start = ($page-1)*$pagesize;
$sql = 'select sum(fee) as fee,`day` from chapter_order_total where bid in ('.$bids.') group by `day` limit '.$start.','.$pagesize;
$count_sql = 'select count(*) as count from (select sum(fee) as fee,`day` from chapter_order_total where bid in ('.$bids.') group by `day`) a';
if($request->input('export')){
$book_order_bids = $this->getBookOrderBid();
$this_book_bid = array_intersect($book_order_bids,explode(',',$bids));
$sqls = 'select sum(fee) as fee,`day` from chapter_order_total where bid in ('.$bids.') group by `day`';
$res = DB::connection('chapter_order_mysql')->select($sqls);
if($this_book_bid){
$book_order_res = BookOrder::whereIn('bid',$this_book_bid)
->select(DB::raw('date(created_at) as day'),DB::raw('sum(fee) as fee'))
->groupBy(DB::raw('date(created_at)'))
->get();
foreach ($res as &$v){
foreach ($book_order_res as $value){
if($v->day == $value->day){
$v->fee = $v->fee+$value->fee;
}
continue;
}
}
}
$filename = 'cptotal'.date('YmdHis').'.csv';
Storage::append($filename,mb_convert_encoding("日期,订阅量",'gbk'));
$str = '';
foreach ($res as $val){
$str .= "{$val->day},{$val->fee}\r\n";
}
Storage::append($filename,mb_convert_encoding($str,'gbk'));
return response()->download(storage_path('app/'.$filename))->deleteFileAfterSend(true);
}
$count = DB::connection('chapter_order_mysql')->select($count_sql);
$res = DB::connection('chapter_order_mysql')->select($sql);
$book_order_bids = $this->getBookOrderBid();
$this_book_bid = array_intersect($book_order_bids,explode(',',$bids));
if($this_book_bid){
$book_order_res = BookOrder::whereIn('bid',$this_book_bid)
->select(DB::raw('date(created_at) as day'),DB::raw('sum(fee) as fee'))
->groupBy(DB::raw('date(created_at)'))
->get();
foreach ($res as &$v){
foreach ($book_order_res as $value){
if($v->day == $value->day){
$v->fee = $v->fee+$value->fee;
}
continue;
}
}
}
$data = [
'meta'=>['last_page'=>ceil($count[0]->count/$pagesize),'current_page'=>$page],
'list'=>$res
];
return response()->success($data);
}
/**
* @apiVersion 1.0.0
* @apiDescription cp图书统计数据
* @api {get} bookcp/bookCpTotal cp图书统计数据
* @apiGroup BookCP
* @apiName bookCpTotal
* @apiParam {String} cp cp名称
* @apiParam {String} [export] 要导出需要传值等于1
* @apiParam {String} bids bids(cp统计数据返回的bids)
* @apiSuccess {int} code 态码
* @apiSuccess {String} msg 信息
* @apiSuccess {object} data 结果集
* @apiSuccess {object} data.book_name 书名
* @apiSuccess {object} data.bid bid
* @apiSuccess {object} data.yesterday 昨日订阅量
* @apiSuccess {object} data.month 本月订阅量
* @apiSuccess {object} data.prev_month 上月订阅量
* @apiSuccess {object} data.today 上月订阅量
* @apiSuccessExample {json} Success-Response:
* HTTP/1.1 200 OK
* {
* code: 0,
* msg: "",
* data: {
* yesterday: "32",
* month: "2762",
* prev_month: "0",
* book_name: "将军在上",
* today: "-",
* bid: "21"
* },
* }
* }
*/
public function bookCpTotal(Request $request){
$bids = $request->input('bids');
$yesterday = date('Y-m-d',time()-86400);
$today = date('Y-m-d');
$tomorrow = date('Y-m-d',time()+86400);
$month = date('Y-m').'-01';
$prev_month = date('Y-m',strtotime('-1 month')).'-01';
$next_month = date('Y-m',strtotime('+1 month')).'-01';
$sql_format = '(select ifnull(sum(fee),0) as %s from chapter_order_total where bid = %s and day BETWEEN "%s" and "%s") %s';
$book_sql_format = '(select ifnull(sum(fee),0) as %s from book_orders where bid = %s and date(created_at) BETWEEN "%s" and "%s") %s';
$book_array = explode(',',$bids);
$res = [];
$book_order_bids = $this->getBookOrderBid();
for ($i = 0;$i<count($book_array);$i++ ){
$yesterday_sql = sprintf($sql_format,'yesterday',$book_array[$i],$yesterday,$today,'a');
$month_sql = sprintf($sql_format,'month',$book_array[$i],$month,$next_month,'b');
$prev_month_sql = sprintf($sql_format,'prev_month',$book_array[$i],$prev_month,$month,'c');
$total_sql = 'select * from ('.$yesterday_sql.' join '.$month_sql.' join '.$prev_month_sql.'
join (select book_name from chapter_order_total where bid='.$book_array[$i].' limit 1) d
)';
$temp_res = DB::connection('chapter_order_mysql')->select($total_sql);
if(in_array($book_array[$i],$book_order_bids)){
$book_yesterday_sql = sprintf($book_sql_format,'yesterday',$book_array[$i],$yesterday,$today,'a');
$book_month_sql = sprintf($book_sql_format,'month',$book_array[$i],$month,$next_month,'b');
$book_prev_month_sql = sprintf($book_sql_format,'prev_month',$book_array[$i],$prev_month,$month,'c');
$book_total_sql = 'select * from ('.$book_yesterday_sql.' join '.$book_month_sql.' join '.$book_prev_month_sql.'
join (select book_name from chapter_order_total where bid='.$book_array[$i].' limit 1) d
)';
$book_temp_res = DB::connection('chapter_order_mysql')->select($book_total_sql);
$temp_res[0]->yesterday = $book_temp_res[0]->yesterday;
$temp_res[0]->yesterday = $book_temp_res[0]->month;
$temp_res[0]->yesterday = $book_temp_res[0]->prev_month;
$temp_res[0]->yesterday = $book_temp_res[0]->book_name;
}
$res[$i]['yesterday'] = $temp_res[0]->yesterday;
$res[$i]['month'] = $temp_res[0]->month;
$res[$i]['prev_month'] = $temp_res[0]->prev_month;
$res[$i]['book_name'] = $temp_res[0]->book_name;
$res[$i]['today'] = '-';
$res[$i]['bid'] = $book_array[$i];
$temp_res = null;
}
if($request->input('export')){
$filename = 'cptotal'.date('YmdHis').'.csv';
Storage::append($filename,mb_convert_encoding("书籍ID,书名,今日订阅量,昨日订阅量,本月订阅量,上月订阅量",'gbk'));
$str = '';
foreach ($res as $val){
$str .= "{$val['bid']},{$val['book_name']},{$val['today']},{$val['yesterday']},{$val['month']},{$val['prev_month']}\r\n";
}
Storage::append($filename,mb_convert_encoding($str,'gbk'));
return response()->download(storage_path('app/'.$filename))->deleteFileAfterSend(true);
}
return response()->success($res);
}
/**
* @apiVersion 1.0.0
* @apiDescription cp每本书数据
* @api {get} bookcp/everyBookTotal cp每本书数据
* @apiGroup BookCP
* @apiName everyBookTotal
* @apiParam {String} bid bid
* @apiParam {String} [export] 要导出需要传值等于1
* @apiParam {String} page 页码
* @apiParam {String} pagesize 每页数据
* @apiSuccess {int} code 态码
* @apiSuccess {String} msg 信息
* @apiSuccess {object} data 结果集
* @apiSuccess {object} data.list.fee 订阅量
* @apiSuccess {object} data.list.day 图书数量量
* @apiSuccess {object} data.meta.last_page 最后意一页
* @apiSuccess {object} data.meta.current_page 当前页
* @apiSuccessExample {json} Success-Response:
* HTTP/1.1 200 OK
* {
* code: 0,
* msg: "",
* data: {
* list:[
* {
* fee: "96",
* day: "2017-12-21"
* },
* {
* fee: "1262",
* day: "2017-12-22"
* },
* {
* fee: "382",
* day: "2017-12-24"
* },
* ],
* meta{
* last_page:3,
* current_page:1
* }
* }
* }
*/
public function everyBookTotal(Request $request){
$bid = $request->input('bid');
$page = (int)$request->input('page',1);
$pagesize = (int)$request->input('pagesize',15);
$start = ($page-1)*$pagesize;
$data = [];
if(in_array($bid,$this->getBookOrderBid())){
$book_sql = 'select sum(fee) as fee,date(created_at) as `day` from book_orders where bid='.$bid.' group by date(created_at) limit '.$start.','.$pagesize;
$count_sql = 'select count(*) as count from (select sum(fee) as fee from book_orders where bid='.$bid.' group by date(created_at)) a';
$temp_res = DB::select($book_sql);
$count = DB::select($count_sql);
$data = [
'meta'=>['last_page'=>ceil($count[0]->count/$pagesize),'current_page'=>$page],
'list'=>$temp_res
];
}else{
$sql = 'select sum(fee) as fee,`day` from chapter_order_total where bid='.$bid.' group by `day` limit '.$start.','.$pagesize;
$count_sql = 'select count(*) as count from (select sum(fee) as fee,day from chapter_order_total where bid='.$bid.' group by `day`) a';
$temp_res = DB::connection('chapter_order_mysql')->select($sql);
$count = DB::connection('chapter_order_mysql')->select($count_sql);
$data = [
'meta'=>['last_page'=>ceil($count[0]->count/$pagesize),'current_page'=>$page],
'list'=>$temp_res
];
}
if($request->input('export')){
$filename = 'cptotal'.date('YmdHis').'.csv';
Storage::append($filename,mb_convert_encoding("日期,订阅量",'gbk'));
$str = '';
foreach ($data['list'] as $val){
$str .= "{$val->day},{$val->fee}\r\n";
}
Storage::append($filename,mb_convert_encoding($str,'gbk'));
return response()->download(storage_path('app/'.$filename))->deleteFileAfterSend(true);
}
return response()->success($data);
}
protected function getBookOrderBid(){
static $book_order_bid = null;
if(is_null($book_order_bid)){
$book_order_bid_obj = BookOrder::select(DB::raw('DISTINCT bid'))->get();
foreach ($book_order_bid_obj as $v){
$book_order_bid[] = $v->bid;
}
}
return $book_order_bid;
}
}