wangdu_public_api/app/Modules/CpSub/Models/CpSub.php

<?php

namespace App\Modules\CpSub\Models;

use Illuminate\Database\Eloquent\Model;
use DB;

class CpSub extends Model
{
    protected  $table = 'cp_subs';
    protected  $fillable = ['cp_user_id','month','bid','yestoday_charge_balance','yestoday_available_amount','yestoday_last_amount','yestoday_total_amount','current_month_book_num','total_amount','available_amount','last_amount','created_at','updated_at','book_name','cp_source','calculate_date'];
    
    public static function getCpSub($cp_source, $calculate_date,$bid) {
    	return self::where(['cp_source'=>$cp_source,'calculate_date'=>$calculate_date,'bid'=>$bid])->first();
    }
    
    /**
     * 获取图书列表
     * @param $params[]
     *                  book_name: 书名 可选
     *                  book_id: 书名 可选
     *                  start_time:开始时间 可选
     *                  end_time:结束时间 可选
     *                  is_enabled: 0:不可用； 1:可用
     * @param $isAll
     * @return mixed
     */
    public static function getCpSubs($params=[], $isAll = false) {
        $search_object = self::orderBy('month','desc');

        if(isset($params['book_name']) && $params['book_name']) {
            $search_object->where('book_name', 'like', "%".$params['book_name']."%");
        }
        if(isset($params['book_id']) && $params['book_id']) {
        	$search_object->where('bid', $params['book_id']);
        }
        if(isset($params['all_bid'])) {
            $search_object->whereIn('bid', $params['all_bid']);
        }
        if(isset($params['cp_user_id']) && $params['cp_user_id']) {
        	$search_object->where('cp_user_id', $params['cp_user_id']);
        }
        if(isset($params['start_time']) && $params['start_time']) $search_object->where('month','>=', $params['start_time']);
        if(isset($params['end_time']) && $params['end_time']) $search_object->where('month','<=', $params['end_time']);

        $search_object->orderBy('calculate_date', 'desc');
        $search_object->orderBy('cp_source', 'desc');
        $search_object->orderBy('current_month_book_num','desc');
        $search_object->select('cp_subs.*',DB::raw('(select last_amount from cp_subs cp where cp_subs.cp_user_id=cp.cp_user_id and cp.bid=cp_subs.bid  and cp.month=cp_subs.month and cp.calculate_date < cp_subs.calculate_date order by calculate_date desc limit 1 offset 0) as before_last_amount'));
        if($isAll) {
            return $search_object->get();

        } else {
            return $search_object->paginate();
        }
    }
}