wangdu_public_api/app/Modules/Finance/Models/FinancialStat.php

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

namespace App\Modules\Finance\Models;

use App\Modules\Channel\Models\Channel;
use Illuminate\Database\Eloquent\Model;
use DB;

/**
 * Class FinancialStat 渠道提现信息（可提现，总提现...）
 * @package App\Modules\Finance\Models
 */
class FinancialStat extends Model
{
    protected $table = 'financial_stats';
    protected $fillable = ['distribution_channel_id', 'accumulative_withdrawal_amount','enable_withdrawal_amount', 'latest_withdrawal_amount', 'latest_withdraw_time','withdraw_pending_amount', 'commission_rate'];


    /**
     * 根据渠道ID获取FinancialStat
     * @param $distribution_channel_id
     * @return mixed
     */
    static function getByDistributionChannel($distribution_channel_id)
    {
        $result = self::where('distribution_channel_id', $distribution_channel_id)->first();
        return $result;
    }

    /**
     * 获取可提现金额总额
     * @param string $distribution_channel_id 不传获取所有渠道可提现金额总额
     * @return mixed
     */
    static function getEnableWithdrawalAmount($distribution_channel_id = '') {
        if($distribution_channel_id) {
            return FinancialStat::where('distribution_channel_id', $distribution_channel_id)->sum('enable_withdrawal_amount');
        } else {
            return FinancialStat::sum('enable_withdrawal_amount');
        }
    }

    /**
     * 获取账号信息列表
     * @param $params[]
     *                  channel_id:渠道ID 可选
     *                  channel_name:渠道名称 可选
     *                  person_in_charge_name: 渠道负责人姓名 可选
     *                  search_name: 搜索名称 可选
     *                  ac_start_time:开始时间 可选
     *                  ac_end_time:结束时间 可选
     *                  is_frozen:冻结  0； -1:冻结账户 可选
     *                  frozen_start_time:冻结开始时间 可选
     *                  frozen_end_time:冻结结束时间  可选
     *                  is_company 0:对私, 1:对公司 可选
     * @return mixed
     */
    public static function getFrozenFinancialStatListParam($params=[]) {
        $search_object = Channel::select([
            'distribution_channels.id',
            'distribution_channels.phone',
            'distribution_channels.name',
            'distribution_channels.pay_merchant_id',
            'distribution_channels.nickname',
            'distribution_channels.remark as channel_remark',
            'distribution_channels.person_in_charge_name',
            'distribution_channels.created_at as channel_created_at',
            'financial_stats.accumulative_withdrawal_amount',
            'financial_stats.enable_withdrawal_amount',
            'financial_stats.latest_withdrawal_amount',
            'financial_stats.latest_withdraw_time',
            'financial_stats.withdraw_pending_amount',
            'financial_stats.commission_rate',
            'cash_accounts.is_company',
            DB::raw('IFNULL(financial_configs.is_frozen, 0) as is_frozen'),
            DB::raw('IFNULL(financial_configs.remark , "") as fc_remark'),
            DB::raw('IFNULL(financial_configs.updated_at , "") as frozen_time'),
        ])
            ->leftjoin('financial_stats','financial_stats.distribution_channel_id','=','distribution_channels.id')
            ->leftjoin('financial_configs','financial_configs.distribution_channel_id','=','distribution_channels.id')
            ->leftjoin('cash_accounts','cash_accounts.distribution_channel_id','=','distribution_channels.id')
            ->orderBy('distribution_channels.id','asc');

        if(isset($params['channel_id']) && $params['channel_id']) $search_object->where('distribution_channels.id', $params['channel_id']);
        if(isset($params['channel_name']) && $params['channel_name']) $search_object->where('distribution_channels.name','like', "%".$params['channel_name']."%");
        if(isset($params['person_in_charge_name']) && $params['person_in_charge_name']) $search_object->where('distribution_channels.person_in_charge_name', 'like', "%".$params['person_in_charge_name']."%");

        if(isset($params['search_name']) && $params['search_name']) {
            $search_object->where(function ($query) use($params) {
                $query->where('distribution_channels.name','like', "%".$params['search_name']."%")
                    ->orWhere('distribution_channels.person_in_charge_name', 'like', "%".$params['search_name']."%");
            });
        }

        if(isset($params['is_frozen']) && is_numeric($params['is_frozen'])) {
            if($params['is_frozen'] == 0) {
                $search_object->where(function ($query) use($params) {
                    $query->where('financial_configs.is_frozen', 0)
                        ->orWhereNull('financial_configs.is_frozen');
                });
            } else {
                $search_object->where('financial_configs.is_frozen', $params['is_frozen']);
            }
        }

        if(isset($params['ac_start_time']) && $params['ac_start_time']) $search_object->where('distribution_channels.created_at','>=', $params['ac_start_time']);
        if(isset($params['ac_end_time']) && $params['ac_end_time']) $search_object->where('distribution_channels.created_at','<=', $params['ac_end_time']);

        if(isset($params['frozen_start_time']) && $params['frozen_start_time']) $search_object->where('financial_configs.updated_at','>=', $params['frozen_start_time']);
        if(isset($params['frozen_end_time']) && $params['frozen_end_time']) $search_object->where('financial_configs.updated_at','<=', $params['frozen_end_time']);

        if(isset($params['is_company']) && is_numeric($params['is_company'])) {
            if($params['is_company'] == 0) {
                $search_object->where(function ($query) use($params) {
                    $query->where('cash_accounts.is_company', 0)
                        ->orWhereNull('cash_accounts.is_company');
                });
            } else {
                $search_object->where('cash_accounts.is_company', $params['is_company']);
            }
        }

        return $search_object->paginate();
    }

    /**
     * 结算明细
     * @param $params[]
     *                  channel_id:渠道ID 可选
     *                  channel_name:渠道名称 可选
     *                  person_in_charge_name: 渠道负责人姓名 可选
     *                  search_name: 搜索名称 可选
     *                  ac_start_time:开始时间 可选
     *                  ac_end_time:结束时间 可选
     *                  is_frozen:冻结  0； -1:冻结账户 可选
     *                  frozen_start_time:冻结开始时间 可选
     *                  frozen_end_time:冻结结束时间  可选
     * @return mixed
     */
    static function getFrozenFinancialStatPriceListDetail($params=[]) {
        $search_object = Channel::select([
            'distribution_channels.id',
            'distribution_channels.phone',
            'distribution_channels.name',
            'distribution_channels.pay_merchant_id',
            'distribution_channels.nickname',
            'distribution_channels.remark as channel_remark',
            'distribution_channels.person_in_charge_name',
            'distribution_channels.created_at as channel_created_at',
            'financial_stats.accumulative_withdrawal_amount',
            'financial_stats.enable_withdrawal_amount',
            'financial_stats.latest_withdrawal_amount',
            'financial_stats.latest_withdraw_time',
            'financial_stats.withdraw_pending_amount',
            'financial_stats.commission_rate',
            'cash_accounts.is_company',
            DB::raw('IFNULL(financial_configs.is_frozen, 0) as is_frozen'),
            DB::raw('IFNULL(financial_configs.remark , "") as fc_remark'),
            DB::raw('IFNULL(financial_configs.updated_at , "") as frozen_time'),
            DB::raw('(SELECT sum(withdraw_cashes.tallage) from withdraw_cashes where withdraw_cashes.distribution_channel_id = distribution_channels.id) as  tallage'),
            DB::raw('(SELECT sum(bills.recharge_amount) from bills where bills.distribution_channel_id = distribution_channels.id) as  recharge_amount_count'),
            DB::raw('(SELECT sum(bills.settlement_price) from bills where bills.distribution_channel_id = distribution_channels.id) as  settlement_price_count')
        ])
            ->leftjoin('financial_stats','financial_stats.distribution_channel_id','=','distribution_channels.id')
            ->leftjoin('financial_configs','financial_configs.distribution_channel_id','=','distribution_channels.id')
            ->leftjoin('bills','bills.distribution_channel_id','=','distribution_channels.id')
            ->leftjoin('cash_accounts','cash_accounts.distribution_channel_id','=','distribution_channels.id')
            ->groupBy('distribution_channels.id')
            ->orderBy('distribution_channels.id','asc');

        if(isset($params['channel_id']) && $params['channel_id']) $search_object->where('distribution_channels.id', $params['channel_id']);
        if(isset($params['channel_name']) && $params['channel_name']) $search_object->where('distribution_channels.name','like', "%".$params['channel_name']."%");
        if(isset($params['person_in_charge_name']) && $params['person_in_charge_name']) $search_object->where('distribution_channels.person_in_charge_name', 'like', "%".$params['person_in_charge_name']."%");

        if(isset($params['search_name']) && $params['search_name']) {
            $search_object->where(function ($query) use($params) {
                $query->where('distribution_channels.name','like', "%".$params['search_name']."%")
                    ->orWhere('distribution_channels.person_in_charge_name', 'like', "%".$params['search_name']."%");
            });
        }

        if(isset($params['is_frozen']) && is_numeric($params['is_frozen'])) {
            if($params['is_frozen'] == 0) {
                $search_object->where(function ($query) use($params) {
                    $query->where('financial_configs.is_frozen', 0)
                        ->orWhereNull('financial_configs.is_frozen');
                });
            } else {
                $search_object->where('financial_configs.is_frozen', $params['is_frozen']);
            }
        }

        if(isset($params['ac_start_time']) && $params['ac_start_time']) $search_object->where('distribution_channels.created_at','>=', $params['ac_start_time']);
        if(isset($params['ac_end_time']) && $params['ac_end_time']) $search_object->where('distribution_channels.created_at','<=', $params['ac_end_time']);

        if(isset($params['frozen_start_time']) && $params['frozen_start_time']) $search_object->where('financial_configs.updated_at','>=', $params['frozen_start_time']);
        if(isset($params['frozen_end_time']) && $params['frozen_end_time']) $search_object->where('financial_configs.updated_at','<=', $params['frozen_end_time']);

        if(isset($params['is_company']) && is_numeric($params['is_company'])) {
            if($params['is_company'] == 0) {
                $search_object->where(function ($query) use($params) {
                    $query->where('cash_accounts.is_company', 0)
                        ->orWhereNull('cash_accounts.is_company');
                });
            } else {
                $search_object->where('cash_accounts.is_company', $params['is_company']);
            }
        }

        return $search_object->paginate();
    }
}