wangdu_public_api/app/Modules/Book/Services/SuperiorHistoryBookService.php

<?php
/**
 * Created by PhpStorm.
 * User: tandunzhao
 * Date: 2017/12/4
 * Time: 下午1:43
 */

namespace App\Modules\Book\Services;


use App\Modules\Book\Models\BadBookInfo;
use App\Modules\Book\Models\SuperiorHistoryBooks;
use App\Modules\Book\Models\SuperiorHistoryBooksLevel;
use App\Modules\Book\Models\SuperiorNewBooks;
use DB;

class SuperiorHistoryBookService
{
    public static function getSendOrderStats(){
        $res = DB::select("select * from (select t.*,book_categories.pid,book_configs.recommend_index,books.category_id from (select bid,book_name,sum(uv) UV,
sum(register_user_num) register_user_sum,sum(recharge_amount) recharge_sum,sum(force_user_num) force_user_sum,sum(pay_user_num) pay_user_num 
from send_orders_stats group by  
bid ) as t,books,book_categories,book_configs where books.id = t.bid and book_configs.bid=t.bid and book_categories.id = books.category_id) tmp where tmp.UV >=2500 and 
not exists(select id from superior_history_book shb where shb.bid=tmp.bid)");

        $activities = DB::table('activity')
            ->select('id','start_time','end_time')
            ->where('customer_msg','<>','')
            ->get();
        //var_dump($res);
        foreach ($res as $item) {
            $temp = array(
                'bid'=>$item->bid,
                'UV'=>$item->UV,
                'register_user_sum'=>$item->register_user_sum,
                'pay_user_num'=>$item->pay_user_num,
                'recharge_sum'=>$item->recharge_sum,
                'force_user_sum'=>$item->force_user_sum,
                'pid'=>$item->pid,
                'category_id'=>$item->category_id,
                'recommend_index'=>$item->recommend_index
            );
            $year_paid = DB::select("select count(distinct t.uid) as year_recharge_user_num,sum(t.fee) year_recharge_sum from year_orders t
where t.send_order_id in (select s.send_order_id from send_orders_stats s where s.bid={$item->bid})");
            //var_dump($year_paid);
            $temp['pay_user_num'] -= $year_paid[0]->year_recharge_user_num;
            $temp['recharge_sum'] -= $year_paid[0]->year_recharge_sum;
            foreach ($activities as $key=>$value) {
                $filter = DB::table('send_orders_force_day_stats')
                    ->select(DB::raw('sum(uv) as UV,sum(pay_user_num) as pay_user_num,sum(register_user_num) register_user_sum,sum(recharge_amount) recharge_sum,sum(force_user_num) force_user_sum'))
                    ->where([
                        ['date','>=',date('Y-m-d',strtotime($value->start_time))],
                        ['date','<=',date('Y-m-d',strtotime(($value->end_time).' -1 minute +3 day'))],
                        ['bid','=',$item->bid],
                    ])
                    ->first();
                $temp['UV'] -= $filter->UV;
                $temp['register_user_sum'] -= $filter->register_user_sum;
                $temp['recharge_sum'] -= $filter->recharge_sum;
                $temp['force_user_sum'] -= $filter->force_user_sum;
                $temp['pay_user_num'] -= $filter->pay_user_num;
                //echo date('Y-m-d',strtotime($value->start_time)).'--'.date('Y-m-d',strtotime(($value->end_time).' -1 minute +3 day'));
            }
            //var_dump($temp);
            //if($item->pid==2){
                self::addHistorySuperiorItem($temp);
            //}
        }

    }

    public static function addHistorySuperiorItem($param) {
            $uv = $param['UV'];
            $register_uv_rate = $param['UV']>0?round($param['register_user_sum']/$param['UV'],4)*100:0;
            $force_sub_rate = $param['register_user_sum']>0?round($param['force_user_sum']/$param['register_user_sum'],4)*100:0;
            $pay_force_sub_rate = $param['force_user_sum']>0?round($param['pay_user_num']/$param['force_user_sum'],4)*100:0;
            $pay_uv_rate = $param['UV']>0?round($param['pay_user_num']/$param['UV'],4)*100:0;
            $charge_uv_rate = $param['UV']>0?round($param['recharge_sum']/$param['UV'],2):0;
            $sign = array('bid'=>$param['bid']);
            $data = array(
                //'bid'=>$param['bid'],
                'category_id'=>$param['category_id'],
                'pid'=>$param['pid'],
                'status'=>1,
                'recommend_index'=>$param['recommend_index'],
                'uv'=>$uv,
                'register_uv_rate'=>$register_uv_rate,
                'force_sub_rate'=>$force_sub_rate,
                'pay_uv_rate'=>$pay_uv_rate,
                'pay_force_sub_rate'=>$pay_force_sub_rate,
                'charge_uv_rate'=>$charge_uv_rate,
                'updated_at'=>date('Y-m-d H:i:s')
            );
            if($param['pid']==2 && $uv>=5000) {
                if($register_uv_rate>=85){
                    if(($force_sub_rate>=40 && $pay_force_sub_rate>=10)||$pay_uv_rate>=4){
                        $data['level'] = 1;
                        SuperiorHistoryBooks::updateOrCreate($sign,$data);
                    }
                }elseif($register_uv_rate>=30 && $register_uv_rate<85){
                    if($pay_uv_rate>=3.6){
                        $data['level'] = 2;
                        SuperiorHistoryBooks::updateOrCreate($sign,$data);
                    }
                }elseif($register_uv_rate<30 && $charge_uv_rate>=1) {
                    $data['level'] = 3;
                    SuperiorHistoryBooks::updateOrCreate($sign,$data);
                }
            }
            if($param['pid']==1){
                if($pay_uv_rate>1.5 && $register_uv_rate>=25) {
                    $data['level'] = 1;
                    SuperiorHistoryBooks::updateOrCreate($sign,$data);
                }elseif($pay_uv_rate>1 &&$pay_uv_rate<1.5 && $register_uv_rate>=20) {
                    $data['level'] = 2;
                    SuperiorHistoryBooks::updateOrCreate($sign,$data);
                }elseif($pay_uv_rate<1 && $charge_uv_rate>=0.1) {
                    $data['level'] = 3;
                    SuperiorHistoryBooks::updateOrCreate($sign,$data);
                }
            }

    }

    public static function getSuperiorList($pid='',$page_size=15,$search=[],$paginate=true){
        return SuperiorHistoryBooks::getSuperiorList($pid,$page_size,$paginate,$search);
    }

    public static function getSupriorBidList(){
        $books = SuperiorHistoryBooks::getSuperiorList('','',false);
        $res = [];
        foreach ($books as $book){
            $res[] = $book->bid;
        }
        return $res;
    }

    public static function deleteOne($bid){
        return SuperiorHistoryBooks::where('bid',$bid)->update(['status'=>0]);
    }

    public static function setTop($bid){
        $item = SuperiorHistoryBooks::where('bid',$bid)->first();

        $max = SuperiorHistoryBooks::select(DB::raw('max(order_index) as max_index'))
            ->where('pid',$item->pid)
            ->first();
        //var_dump($max);
        $max_index = intval($max->max_index)+1;
        $item->order_index = $max_index;
        $item->save();
    }

    public static function selectHistoryBooks() {
        //$to_do = array('tmp.register_uv_rate>=0.85','tmp.register_uv_rate>=0.3 and tmp.register_uv_rate<0.85','tmp.register_uv_rate<0.3');
        $to_do = array('tmp.register_uv_rate>=0.85','tmp.register_uv_rate<0.3');
        foreach ($to_do as $key=>$value){
            $level = ++$key;
            $res = DB::select("select * from (select t.*,book_categories.pid,book_configs.recommend_index,books.category_id from (SELECT tmp.bid,tmp.book_name,tmp.charge_type,sum(tmp.uv) UV,
sum(tmp.register_user_num) register_user_sum,
sum(tmp.recharge_amount) recharge_sum,
sum(tmp.force_user_num) force_user_sum,
sum(tmp.pay_user_num) pay_user_num,
SUM(tmp.uv_one_day) AS uv_one_day_sum,
SUM(tmp.uv_three_day) AS uv_three_day_sum,
SUM(tmp.recharge_amount_in_one_day)-SUM(tmp.one_day_year_recharge) AS recharge_amount_in_one_day_sum,
SUM(tmp.recharge_amount_in_three_days)-SUM(tmp.three_day_year_recharge) AS recharge_amount_in_three_days
  FROM (
		select s.bid,s.book_name,book_configs.charge_type,s.uv,s.register_user_num,s.recharge_amount,send_order_extra_stats.uv_one_day,send_order_extra_stats.uv_three_day,
		IFNULL((SELECT sum(price) FROM orders WHERE orders.send_order_id=s.send_order_id AND orders.`status`='PAID' AND orders.order_type='YEAR' AND orders.created_at > s.send_time AND UNIX_TIMESTAMP(orders.created_at)<=(UNIX_TIMESTAMP(s.send_time)+84600)),0) AS one_day_year_recharge,
		IFNULL((SELECT sum(price) FROM orders WHERE orders.send_order_id=s.send_order_id AND orders.`status`='PAID' AND orders.order_type='YEAR' AND orders.created_at > s.send_time AND UNIX_TIMESTAMP(orders.created_at)<=(UNIX_TIMESTAMP(s.send_time)+259200)),0) AS three_day_year_recharge,
		s.recharge_amount_in_one_day,s.recharge_amount_in_three_days,s.year_paid_num,
		s.force_user_num,s.pay_user_num,s.register_user_num/s.uv as register_uv_rate,s.force_user_num/s.register_user_num as force_sub_rate 
		from send_orders_stats as s JOIN send_order_extra_stats ON send_order_extra_stats.send_order_id=s.send_order_id 
		join book_configs on book_configs.bid=s.bid where book_configs.is_on_shelf in (1,2) and s.send_time between '".date('Y-m-d',strtotime('-34 day'))."' and '".date('Y-m-d 23:59:59',strtotime('-4 day'))."' and s.uv >= 100
	) tmp 
	WHERE $value and tmp.force_sub_rate <= 0.65 GROUP BY tmp.bid) as t,books,book_categories,book_configs where books.id = t.bid and book_configs.bid=t.bid and book_categories.id = books.category_id and book_categories.pid=2) tmp2 where tmp2.uv_one_day_sum >=1000
	 and not exists(select 1 from superior_history_book shb where shb.bid=tmp2.bid and shb.status=1)
");//and not exists(select id from superior_history_level shb where shb.bid=tmp2.bid and shb.level=$level)

            foreach ($res as $item) {
                $temp = array(
                    'bid'=>$item->bid,
                    'UV'=>$item->UV,
                    'charge_type'=>$item->charge_type,
                    'register_user_sum'=>$item->register_user_sum,
                    'pay_user_num'=>$item->pay_user_num,
                    'recharge_sum'=>$item->recharge_sum,
                    'force_user_sum'=>$item->force_user_sum,
                    'pid'=>$item->pid,
                    'category_id'=>$item->category_id,
                    'recommend_index'=>$item->recommend_index,
                    'recharge_amount_in_one_day'=>$item->recharge_amount_in_one_day_sum,
                    'recharge_amount_in_three_days'=>$item->recharge_amount_in_three_days,
                    'uv_one_day_sum'=>$item->uv_one_day_sum,
                    'uv_three_day_sum'=>$item->uv_three_day_sum
                );

                self::judgeHistoryBook($level,$temp);
            }
        }
    }

    public static function judgeHistoryBook($level,$param){
        $uv = $param['UV'];
        $register_uv_rate = $param['UV']>0?round($param['register_user_sum']/$param['UV'],4)*100:0;
        $force_sub_rate = $param['register_user_sum']>0?round($param['force_user_sum']/$param['register_user_sum'],4)*100:0;
        $pay_force_sub_rate = $param['force_user_sum']>0?round($param['pay_user_num']/$param['force_user_sum'],4)*100:0;
        $pay_uv_rate = $param['UV']>0?round($param['pay_user_num']/$param['UV'],4)*100:0;
        $charge_uv_rate = $param['UV']>0?round($param['recharge_sum']/$param['UV'],2):0;
        $one_day_recharge_uv_rate = $param['uv_one_day_sum']>0?round($param['recharge_amount_in_one_day']/$param['uv_one_day_sum'],2):0;
        $three_days_recharge_uv_rate = $param['uv_three_day_sum']>0?round($param['recharge_amount_in_three_days']/$param['uv_three_day_sum'],2):0;
        $three_days_recharge_one_day_uv = $param['uv_one_day_sum']>0?round( $param['recharge_amount_in_three_days']/ $param['uv_one_day_sum'],2):0;
        $sign = array('bid'=>$param['bid']);
        $data = array(
            //'bid'=>$param['bid'],
            'category_id'=>$param['category_id'],
            'pid'=>$param['pid'],
            'status'=>1,
            'recommend_index'=>$param['recommend_index'],
            'uv'=>$uv,
            'register_uv_rate'=>$register_uv_rate,
            'force_sub_rate'=>$force_sub_rate,
            'pay_uv_rate'=>$pay_uv_rate,
            'pay_force_sub_rate'=>$pay_force_sub_rate,
            'charge_uv_rate'=>$charge_uv_rate,
            'updated_at'=>date('Y-m-d H:i:s'),
            'one_day_recharge_uv_rate'=>$one_day_recharge_uv_rate,
            'three_days_recharge_uv_rate'=>$three_days_recharge_uv_rate
            //'three_days_recharge_one_day_uv'=>$three_days_recharge_one_day_uv
        );
        $flag_level = [
            'bid'=>$param['bid'],
            'level'=>$level
        ];
        $data_level = [
            'uv'=>$uv,
            'register_uv_rate'=>$register_uv_rate,
            'force_sub_rate'=>$force_sub_rate,
            'pay_uv_rate'=>$pay_uv_rate,
            'pay_force_sub_rate'=>$pay_force_sub_rate,
            'charge_uv_rate'=>$charge_uv_rate,
            'one_day_recharge_uv_rate'=>$one_day_recharge_uv_rate,
            'three_days_recharge_uv_rate'=>$three_days_recharge_uv_rate
            //'three_days_recharge_one_day_uv'=>$three_days_recharge_one_day_uv
        ];
        $max_sequence = self::getMaxSequence();
        $data['order_index'] = 1+$max_sequence;
        //if($param['pid']==2 && $uv>=5000) {
            if($register_uv_rate>=85){
                //if(($force_sub_rate>=40 && $pay_force_sub_rate>=10)||$pay_uv_rate>=4){
                if(($param['charge_type']=='BOOK' && $one_day_recharge_uv_rate>=1)||($param['charge_type']=='CHAPTER' && $one_day_recharge_uv_rate>=0.8)){
                    $data['level'] = 1;
                    SuperiorHistoryBooks::updateOrCreate($sign,$data);
                    SuperiorHistoryBooksLevel::updateOrCreate($flag_level,$data_level);
                }
            }elseif($register_uv_rate<=30) {
                //if(($param['charge_type']=='BOOK' && $one_day_recharge_uv_rate>=0.85)||($param['charge_type']=='CHAPTER' && $three_days_recharge_uv_rate>=1)){
                 if(($param['charge_type']=='BOOK' && $one_day_recharge_uv_rate>=0.85)||($param['charge_type']=='CHAPTER' && $three_days_recharge_one_day_uv>=1.15)){
                    $data['level'] = 3;
                    SuperiorHistoryBooks::updateOrCreate($sign,$data);
                    SuperiorHistoryBooksLevel::updateOrCreate($flag_level,$data_level);
                }
            }
        //}
        if($param['pid']==1){
            //if($pay_uv_rate>0.5) {
                $data['level'] = 1;
                SuperiorHistoryBooks::updateOrCreate($sign,$data);
                SuperiorHistoryBooksLevel::updateOrCreate($flag_level,$data_level);
            //}
        }
    }

    public static function selectMaleSuperiorBooks(){
        /*$res = DB::select("SELECT s.*,book_categories.pid,book_configs.recommend_index,books.category_id FROM (SELECT tmp.bid,tmp.book_name,sum(tmp.uv) UV,
sum(tmp.register_user_num) register_user_sum,
sum(tmp.recharge_amount) recharge_sum,
sum(tmp.force_user_num) force_user_sum,
sum(tmp.pay_user_num) pay_user_sum
  FROM send_orders_stats tmp
	WHERE tmp.uv>1500 GROUP BY tmp.bid) as s,books,book_categories,book_configs where books.id = s.bid and book_configs.bid=s.bid and book_categories.id = books.category_id and book_categories.pid=1 AND s.recharge_sum/s.UV > 0.5 and  
not exists(select id from superior_history_level shb where shb.bid=tmp2.bid)");*/
        $res = DB::select("SELECT s.*,book_categories.pid,book_configs.recommend_index,books.category_id FROM (SELECT tmp.bid,tmp.book_name,sum(tmp.uv) UV,
sum(tmp.register_user_num) register_user_sum,
sum(tmp.recharge_amount) recharge_sum,
sum(tmp.force_user_num) force_user_sum,
sum(tmp.pay_user_num) pay_user_num
  FROM send_orders_stats tmp where tmp.bid in (1969,1998,758,2115,872,1894,642,1890,2032,635,1163,1174,1166,1893,693,680,2066,1165,1896,1048,842,2064,1897,1891,770,816,971,694,829,2120) GROUP BY tmp.bid) as s,books,book_categories,book_configs where books.id = s.bid and book_configs.bid=s.bid and book_categories.id = books.category_id and book_categories.pid=1 and  
not exists(select id from superior_history_level shb where shb.bid=s.bid)");
        foreach ($res as $item) {
            $temp = array(
                'bid'=>$item->bid,
                'UV'=>$item->UV,
                'register_user_sum'=>$item->register_user_sum,
                'pay_user_num'=>$item->pay_user_num,
                'recharge_sum'=>$item->recharge_sum,
                'force_user_sum'=>$item->force_user_sum,
                'pid'=>$item->pid,
                'category_id'=>$item->category_id,
                'recommend_index'=>$item->recommend_index
            );
            /*$year_paid = DB::select("select count(distinct t.uid) as year_recharge_user_num,sum(t.fee) year_recharge_sum from year_orders t
where t.send_order_id in (select s.send_order_id from send_orders_stats s where s.bid={$item->bid})");
            $temp['pay_user_num'] -= $year_paid[0]->year_recharge_user_num;
            $temp['recharge_sum'] -= $year_paid[0]->year_recharge_sum;*/

            self::judgeHistoryBook(1,$temp);
        }
    }

    public static function updateOne($bid,$param) {
        return SuperiorHistoryBooks::where('bid',$bid)->update($param);
    }

    public static function adjustSequence($bid,$sequence) {
        $total = SuperiorHistoryBooks::where('status',1)->count();
        if($sequence <1 || $sequence>$total){
            throw new \Exception('sequence offset!',10001);
        }
        $pres = SuperiorHistoryBooks::where([
                //['order_index','<',$sequence],
                ['superior_history_book.status','=',1],
                ['superior_history_book.bid','<>',$bid]
            ])
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('book_configs')
                    ->whereRaw('book_configs.bid = superior_history_book.bid')
                    ->whereBetween('is_on_shelf',[1,2]);
            })
            ->select(['superior_history_book.id','superior_history_book.bid','superior_history_book.register_uv_rate','superior_history_book.pay_uv_rate','superior_history_book.charge_uv_rate','superior_history_book.pid','superior_history_book.created_at'])
            ->orderBy('superior_history_book.order_index','asc')
            ->orderBy('superior_history_book.recommend_index','desc')
            ->orderBy('superior_history_book.id','asc')
            ->get();
        //self::sortBooks($pres,1); //对序号之前的书重新排序
        $current_book = SuperiorHistoryBooks::where('bid',$bid)->first();
        $current_book->order_index = $sequence;
        $current_book->save(); //把当前书插入$sequence序号
        /*$post = SuperiorHistoryBooks::where([
                ['order_index','>=',$sequence],
                ['superior_history_book.status','=',1],
                ['superior_history_book.bid','<>',$bid]
            ])
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('book_configs')
                    ->whereRaw('book_configs.bid = superior_history_book.bid')
                    ->whereBetween('is_on_shelf',[1,2]);
            })
            ->select(['superior_history_book.id','superior_history_book.bid','superior_history_book.register_uv_rate','superior_history_book.pay_uv_rate','superior_history_book.charge_uv_rate','superior_history_book.pid','superior_history_book.created_at'])
            ->orderBy('superior_history_book.order_index','asc')
            ->orderBy('superior_history_book.recommend_index','desc')
            ->orderBy('superior_history_book.id','asc')
            ->get();*/
        self::sortBooks($pres,$sequence);//后面的书重新排序
    }

    private static function sortBooks($datas,$sequence){
        foreach ($datas as $key=>$data) {
            if($key+1 < $sequence){
                $data->order_index = $key+1;
                $data->save();
            }else{
                $data->order_index = $key+2;
                $data->save();
            }
        }
    }

    public static function getMaxSequence() {
        return SuperiorHistoryBooks::join('book_configs','book_configs.bid','=','superior_history_book.bid')
            ->where('superior_history_book.status',1)
            ->whereBetween('book_configs.is_on_shelf',[1,2])
            ->max('superior_history_book.order_index');
    }

    public static function resortSuperiorBooks() {
        $list = SuperiorHistoryBooks::getSuperiorList('','',false);
        foreach ($list as $key=>$item) {
            $item->order_index = $key+1;
            $item->save();
        }
    }
}