<?php
/**
 * Created by PhpStorm.
 * User: zhao
 * Date: 2017/11/17
 * Time: 下午4:44
 */

namespace  App\Modules\Book\Services;

use App\Modules\Book\Models\BookConfig;
use App\Modules\Book\Models\CpExceptBook;
use DB;

class CpExceptBookService
{

    /**
     * 获取列表
     * @param array $where
     */
    public static function getList(array $where=[]){

        $res = CpExceptBook::join('cp_users','cp_users.id','=','cp_except_book.cp_user_id')
            ->select('cp_except_book.id','cp_except_book.bid','cp_except_book.book_name','cp_users.username');
        if(isset($where['username']) && !empty($where['username'])){
            $res->where('cp_users.username',$where['username']);
        }

        return $res->paginate(20);
    }

    /**
     * 根据cp和书名获取图书信息
     * @param string $cp
     * @param string $name
     * @return mixed
     */
    public static function getBookByCp(string $cp,string $name){
        return BookConfig::join('books','book_configs.bid','=','books.id')->select('books.name','books.id')
            ->where('book_configs.cp_source',$cp)
            ->where('books.name','like','%'.$name.'%')
            ->whereNotExists(function ($query){
                $query->select('bid')->from('cp_except_book')->whereRaw('book_configs.bid=cp_except_book.bid');
            })
            ->get();
    }

    /**
     * 添加
     * @param array $info
     * @return mixed
     */
    public static function create(array $info){
        return CpExceptBook::create($info);
    }


    /**
     * 获取派出的bid
     * @param $id
     * @return array
     */
    public static function getCpBidList(int $id):array {
        if($id == 1){
            return CpExceptBook::select('bid')->get()->pluck('bid')->all();
        }
        return CpExceptBook::where('cp_user_id',$id)->select('bid')->get()->pluck('bid')->all();
    }

    /**
     * 是否已经加入排除
     * @param int $cp_user_id
     * @param int $bid
     * @return bool
     */
    public static function isHadExcept(int $cp_user_id,int $bid){
        $exist = CpExceptBook::where('cp_user_id',$cp_user_id)->where('bid',$bid)->first();
        if($exist)
            return true;
        else
            return false;
    }

    /***
     * 获取所有cp
     * @return mixed
     */
    public static function getAllcp(){
        return DB::table('cp_users')->select('username','id')->get();
    }

    public static function deleteExcept(int $id){
        CpExceptBook::where('id',$id)->delete();
    }

    public static function getBookCpList($oldname='',$newname=''){
        $where = [];
        if($oldname){
            $where[] = ['books.name','like','%'.trim($oldname).'%'];
        }
        if($newname){
            $where[] = ['book_configs.book_name','like','%'.trim($newname).'%'];
        }
        return BookConfig::join('books','books.id','=','book_configs.bid')
            ->select('books.id','books.ly_bid','books.name as oldname','book_configs.book_name as newname','book_configs.cp_source','book_configs.is_on_shelf')
            ->where($where)
            ->paginate(20);
    }

    public static function editBookCp($bid,$cpid,$cpname){
        BookConfig::where('bid',$bid)->update(['cp_source'=>$cpname]);
        DB::table('cp_subs')->where('bid',$bid)->update([
            'cp_user_id'=>$cpid,
            'cp_source'=>$cpname,
            'updated_at'=>date('Y-m-d H:i:s')
        ]);
    }

    public static function cpDataStats($where,$whereIn,$group,$is_all=false){
        $result =  DB::table('book_order_statistical')->join('book_configs','book_order_statistical.bid','book_configs.bid')
            ->where($where);
        if($whereIn){
            $result->where('book_configs.bid',$whereIn);
        }

        if($group){
            $result->select(
                'book_configs.bid',
                'book_configs.book_name',
                'book_configs.cp_source',
                'book_order_statistical.day',
                'book_order_statistical.charge_balance',
                DB::raw('left(book_order_statistical.day,7) as month'),
                DB::raw('left(book_order_statistical.day,4) as year'));
            foreach ($group as $v){
                $result->groupBy($v);
            }
            //return $result;
        }else{
            $result->select(
                'book_configs.bid',
                'book_configs.book_name',
                'book_configs.cp_source',
                'book_order_statistical.day',
                'book_order_statistical.charge_balance',
                DB::raw('left(book_order_statistical.day,7) as month'),
                DB::raw('left(book_order_statistical.day,4) as year')
            );
            if($is_all){
                return $result->limit(10000)->get();
            }else{
                return $result->paginate(20);
            }
        }
    }
}