wangdu_public_api/app/Modules/Subscribe/Models/OfficialAccountDaySubStat.php

<?php
/**
 * Created by PhpStorm.
 * User: tandunzhao
 * Date: 2018/6/20
 * Time: 下午4:30
 */

namespace App\Modules\Subscribe\Models;


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

class OfficialAccountDaySubStat extends Model
{

    protected $table = 'official_account_day_sub_stats';

    protected $fillable = [
        'appid', 'official_account_name', 'date', 'reward_balance', 'charge_balance', 'fee', 'month'
    ];


    public static function getMonthListData($params = [], $isAll = false) {
        $sqlObj = self::select(
            'appid',
            'official_account_name',
            'month',
            DB::raw('sum(reward_balance) as reward_balance'),
            DB::raw('sum(charge_balance) as charge_balance'),
            DB::raw('sum(fee) as fee')
        )->orderBy('date', 'desc');

        if(isset($params['appid']) && $params['appid']) {
            $sqlObj->where('appid', $params['appid']);
        }

        if(isset($params['official_account_name']) && $params['official_account_name']) {
            $sqlObj->where('official_account_name', 'like', "%".$params['official_account_name']."%");
        }

        if(isset($params['start_date']) && $params['start_date']) {
            $sqlObj->where('date', '>=', $params['start_date']);
        }
        if(isset($params['end_date']) && $params['end_date']) {
            $sqlObj->where('date', '<=', $params['end_date']);
        }

        $sqlObj->groupBy('month', 'appid');

        if($isAll) {
            return $sqlObj->get();
        } else {
            return $sqlObj->paginate();
        }
    }

    public static function getDayListData($params = [], $isAll = false) {
        $sqlObj = self::select(
            'appid',
            'official_account_name',
            'date',
            'month',
            'reward_balance',
            'charge_balance',
            'fee'
        )->orderBy('date', 'desc');

        if(isset($params['appid']) && $params['appid']) {
            $sqlObj->where('appid', $params['appid']);
        }

        if(isset($params['official_account_name']) && $params['official_account_name']) {
            $sqlObj->where('official_account_name', 'like', "%".$params['official_account_name']."%");
        }

        if(isset($params['start_date']) && $params['start_date']) {
            $sqlObj->where('date', '>=', $params['start_date']);
        }
        if(isset($params['end_date']) && $params['end_date']) {
            $sqlObj->where('date', '<=', $params['end_date']);
        }

        $sqlObj->groupBy('date', 'appid');

        if($isAll) {
            return $sqlObj->get();
        } else {
            return $sqlObj->paginate();
        }
    }

}