wangdu_public_api/app/Modules/Statistic/Services/ProductionStatsEmailService.php

<?php
/**
 * Created by PhpStorm.
 * User: z_yang
 * Date: 2018/03/09
 * Time: 20:18
 */

namespace App\Modules\Statistic\Services;

use DB;
use Redis;

class ProductionStatsEmailService
{
    public static function getNewProductionNum($from_date,$to_date){
        $res = DB::table('book_configs')
            ->where([
                ['book_configs.created_at','>=',$from_date],
                ['book_configs.created_at','<=',$to_date],
            ])
           ->count('id');
        return $res;
    }
    public static function getNewProductionNumGroupByGender($from_date,$to_date){
        $male = 0;
        $female=0;
        $num = 0;
        $res = DB::table('book_updates')
            ->select(DB::raw("channel_name,count(bid) as new_products_num"))
            ->where('update_type','add_book')
            ->whereBetween('update_date',[$from_date,$to_date])
            ->groupBy('channel_name')
            ->get();
        foreach ($res as $item){
            if($item->channel_name == '男频'){
                $male = $item->new_products_num;
            }
            if($item->channel_name =='女频'){
                $female = $item->new_products_num;
            }
            $num+=$item->new_products_num;
        }
        return ['total'=>$num,'male'=>$male,'female'=>$female];
    }

    public static function getUpdatedProductionNum($from_date,$to_date) {
        $num = 0;
        $books = DB::table('books')
            ->select('id','created_at','updated_at')
            ->where([
                ['updated_at','>=',$from_date],
                ['updated_at','<=',$to_date]
            ])
            ->get();
        foreach ($books as $book){
            if(strtotime($book->updated_at)-strtotime($book->created_at)>1) {
                $num++;
            }
        }
        return $num;
    }
    public static function getUpdatedProductionNumGroupByGender($from_date,$to_date) {
        $num = 0;
        $male = 0;
        $female = 0;
        $books = $res = DB::table('book_updates')
            ->select(DB::raw("channel_name,count(bid) as update_products_num"))
            ->where('update_type','add_chapter')
            ->whereBetween('update_date',[$from_date,$to_date])
            ->groupBy('channel_name')
            ->get();
        foreach ($books as $book){
            if($book->channel_name == '男频'){
                $male = $book->update_products_num;
            }
            if($book->channel_name =='女频'){
                $female = $book->update_products_num;
            }
            $num+=$book->update_products_num;
        }
        return ['total'=>$num,'male'=>$male,'female'=>$female];
    }

    public static function getUpdatedChaptersNum($from_date,$to_date) {
        $num = 0;
        DB::table('chapters')
            ->select('chapters.id','chapters.created_at','chapters.updated_at','book_configs.created_at as book_add_time')
            ->leftjoin('book_configs','book_configs.bid','=','chapters.bid')
            ->orderBy('chapters.id','desc')
            ->where([['chapters.created_at','>=',$from_date],['chapters.created_at','<=',$to_date]])
            ->chunk(1000,function($chapters) use(&$num,$from_date,$to_date){
                foreach ($chapters as $chapter){
                    if(date('Y-m-d',strtotime($chapter->created_at))!=date('Y-m-d',strtotime($chapter->book_add_time))){
                        $num++;
                    }
                }
                //return false;
            });
        return $num;
    }
    public static function getUpdatedChaptersNumByGender($from_date,$to_date) {
        $num = 0;
        $male = 0;
        $female = 0;
        $books = $res = DB::table('book_updates')
            ->select(DB::raw("channel_name,sum(update_chapter_count) as update_chapter_count"))
            ->where('update_type','add_chapter')
            ->whereBetween('update_date',[$from_date,$to_date])
            ->groupBy('channel_name')
            ->get();
        foreach ($books as $book){
            if($book->channel_name == '男频'){
                $male = $book->update_chapter_count;
            }
            if($book->channel_name =='女频'){
                $female = $book->update_chapter_count;
            }
            $num+=$book->update_chapter_count;
        }
        return ['total'=>$num,'male'=>$male,'female'=>$female];
    }

    public static function getSerialNum(){
        $num = DB::table('books')
            ->where('status',0)
            ->count('id');
        return $num;
    }

    public static function getSerialNumGroupByGender(){
        $num =0;
        $female=0;
        $male=0;
        $res = DB::table('books')
            ->select(DB::raw('count(books.id) as serial_num,book_categories.pid'))
            ->leftjoin('book_categories','books.category_id','=','book_categories.id')
            ->where('books.status',0)
            ->groupBy('book_categories.pid')
            ->get();
        foreach ($res as $item) {
            if($item->pid == 1){
                $male=$item->serial_num;
            }
            if($item->pid == 2){
                $female=$item->serial_num;
            }
            $num+=$item->serial_num;
        }
        return ['num'=>$num,'male'=>$male,'female'=>$female];
    }

    public static function getNotUpdatedSerial(){
        $two_days_ago = date('Y-m-d H:i:s',strtotime('-2 day'));
        $num = DB::table('books as b')
            ->select(DB::raw('count(bcs.bid) as num'))
            ->leftjoin('book_categories as bc','b.category_id','=','bc.id')
            ->leftjoin('book_configs as bcs','bcs.bid','=','b.id')
            ->where([
                ['b.updated_at','<=',$two_days_ago],
                ['b.status','=',0],
                ['bcs.is_on_shelf','>=',1],
                ['bcs.is_on_shelf','<=',2]
            ])
            ->groupBy('bcs.is_on_shelf')
            ->orderBy('bcs.is_on_shelf','asc')
            ->get();
            //->count('bcs.bid');
        return $num;
    }
    public static function getNotUpdatedSerialGroupByGender(){
        $two_days_ago = date('Y-m-d H:i:s',strtotime('-2 day'));
        $num=0;
        $inter_num=0;
        $inter_male = 0;
        $inter_female = 0;
        $outer_male = 0;
        $outer_female = 0;
        $outer_num =0;
        $res = DB::table('books as b')
            ->select(DB::raw('count(bcs.bid) as num,is_on_shelf,pid'))
            ->leftjoin('book_categories as bc','b.category_id','=','bc.id')
            ->leftjoin('book_configs as bcs','bcs.bid','=','b.id')
            ->where([
                ['b.updated_at','<=',$two_days_ago],
                ['b.status','=',0],
                ['bcs.is_on_shelf','>=',1],
                ['bcs.is_on_shelf','<=',2]
            ])
            ->groupBy('bcs.is_on_shelf')
            ->groupBy('bc.pid')
            //->orderBy('bcs.is_on_shelf','asc')
            ->get();
            //->count('bcs.bid');
        foreach ($res as $item) {
            if($item->is_on_shelf ==1){
                $inter_num +=$item->num;
                if($item->pid ==1){
                    $inter_male = $item->num;
                }
                if($item->pid ==2){
                    $inter_female =$item->num;
                }
            }

            if($item->is_on_shelf==2) {
                $outer_num +=$item->num;
                if($item->pid ==1){
                    $outer_male = $item->num;
                }
                if($item->pid ==2){
                    $outer_female =$item->num;
                }
            }
        }
        return ['num'=>$inter_num+$outer_num,'inter_num'=>$inter_num,
            'inter_male'=>$inter_male,'inter_female'=> $inter_female,
            'outer_num'=>$outer_num,'outer_male'=>$outer_male,
            'outer_female'=>$outer_female];
    }

    public static function setDayStats($from_date,$to_date){
        $new_product_num = self::getNewProductionNum($from_date,$to_date);
        $updated_product_num = self::getUpdatedProductionNum($from_date,$to_date);
        $updated_chapter_num = self::getUpdatedChaptersNum($from_date,$to_date);
        $serial_num = self::getSerialNum();
        $not_updated_serial = self::getNotUpdatedSerial();
        $internal_suspend = $not_updated_serial[0]->num;
        $outer_suspend = $not_updated_serial[1]->num;

        $data = array(
            'new_product_num'=>$new_product_num,
            'updated_product_num'=>$updated_product_num,
            'updated_chapter_num'=>$updated_chapter_num,
            'serial_num'=>$serial_num,
            'not_updated_serial'=>$internal_suspend+$outer_suspend,
            'internal_suspend'=>$internal_suspend,
            'outer_suspend'=>$outer_suspend,
            'date'=>date('Y-m-d',strtotime($from_date)),
            'type'=>'day'
        );
        $res = DB::table('product_stats')->insert($data);
        return $res;
    }
    public static function setDayStatsNew($from_date,$to_date){
        $data = self::getAllByGender($from_date,$to_date);
        $data['date']=date('Y-m-d',strtotime($from_date));
        $data['type']='day';
        $res = DB::table('product_stats')->insert($data);
        return $res;
    }

    public static function setMonthStats($from_date,$to_date) {
        $new_product_num = self::getNewProductionNum($from_date,$to_date);
        $updated_product_num = self::getUpdatedProductionNum($from_date,$to_date);
        $updated_chapter_num = self::getUpdatedChaptersNum($from_date,$to_date);
        $serial_num = self::getSerialNum();
        $not_updated_serial = self::getNotUpdatedSerial();
        $internal_suspend = $not_updated_serial[0]->num;
        $outer_suspend = $not_updated_serial[1]->num;
        $data = array(
            'new_product_num'=>$new_product_num,
            'updated_product_num'=>$updated_product_num,
            'updated_chapter_num'=>$updated_chapter_num,
            'serial_num'=>$serial_num,
            'not_updated_serial'=>$not_updated_serial,
            'internal_suspend'=>$internal_suspend,
            'outer_suspend'=>$outer_suspend,
            'date'=>date('Y-m-01',strtotime($from_date)),
            'type'=>'month'
        );
        $res = DB::table('product_stats')->insert($data);
        return $res;
    }

    public static function getAll($from_date,$to_date) {
        $new_product_num = self::getNewProductionNum($from_date,$to_date);
        $updated_product_num = self::getUpdatedProductionNum($from_date,$to_date);
        $updated_chapter_num = self::getUpdatedChaptersNum($from_date,$to_date);
        $serial_num = self::getSerialNum();
        $not_updated_serial = self::getNotUpdatedSerial();
        $internal_suspend = $not_updated_serial[0]->num;
        $outer_suspend = $not_updated_serial[1]->num;
        $data = array(
            'new_product_num'=>$new_product_num,
            'updated_product_num'=>$updated_product_num,
            'updated_chapter_num'=>$updated_chapter_num,
            'serial_num'=>$serial_num,
            'not_updated_serial'=>$not_updated_serial,
            'internal_suspend'=>$internal_suspend,
            'outer_suspend'=>$outer_suspend,
        );
        return $data;
    }
    public static function getAllByGender($from_date,$to_date) {
        $new_product_num = self::getNewProductionNumGroupByGender($from_date,$to_date);
        $updated_product_num = self::getUpdatedProductionNumGroupByGender($from_date,$to_date);
        $updated_chapter_num = self::getUpdatedChaptersNumByGender($from_date,$to_date);
        $serial_num = self::getSerialNumGroupByGender();
        $not_updated_serial = self::getNotUpdatedSerialGroupByGender();
        //$internal_suspend = $not_updated_serial[0]->num;
        //$outer_suspend = $not_updated_serial[1]->num;
        $data = array(
            'new_product_num'=>$new_product_num['total'],
            'new_product_male_num'=>$new_product_num['male'],
            'new_product_female_num'=>$new_product_num['female'],
            'updated_product_num'=>$updated_product_num['total'],
            'updated_product_male_num'=>$updated_product_num['male'],
            'updated_product_female_num'=>$updated_product_num['female'],
            'updated_chapter_num'=>$updated_chapter_num['total'],
            'updated_chapter_male_num'=>$updated_chapter_num['male'],
            'updated_chapter_female_num'=>$updated_chapter_num['female'],
            'serial_num'=>$serial_num['num'],
            'serial_male_num'=>$serial_num['male'],
            'serial_female_num'=>$serial_num['female'],
            'not_updated_serial'=>$not_updated_serial['num'],
            'not_updated_serial_male'=>$not_updated_serial['inter_male']+$not_updated_serial['outer_male'],
            'not_updated_serial_female'=>$not_updated_serial['inter_female']+$not_updated_serial['outer_female'],
            'internal_suspend'=>$not_updated_serial['inter_num'],
            'internal_suspend_male'=>$not_updated_serial['inter_male'],
            'internal_suspend_female'=>$not_updated_serial['inter_female'],
            'outer_suspend'=>$not_updated_serial['outer_num'],
            'outer_suspend_male'=>$not_updated_serial['outer_male'],
            'outer_suspend_female'=>$not_updated_serial['outer_female'],

        );
        return $data;
    }

    public static function getSuspendProducts ($paginate=true,$page_size=15,$filter=''){
        $two_days_ago = date('Y-m-d H:i:s',strtotime('-2 day'));

        $res = DB::table('books as b')
            ->select('b.id','b.name','bc.pid','b.updated_at','bcs.is_on_shelf')
            ->leftjoin('book_categories as bc','b.category_id','=','bc.id')
            ->leftjoin('book_configs as bcs','bcs.bid','=','b.id')
            ->where([
                ['b.updated_at','<=',$two_days_ago],
                ['b.status','=',0],
                ['bcs.is_on_shelf','>=',1],
                ['bcs.is_on_shelf','<=',2]
            ])
            ->orderBy('b.updated_at');
        if($filter){
            $res = $res->where($filter);
        }
        if($paginate){
            $res = $res->paginate($page_size);
        }else{
            $res = $res->get();
        }
        return $res;
    }
}