wangdu_public_api/app/Modules/Trade/Models/OrderDayStat.php

<?php

namespace App\Modules\Trade\Models;

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

class OrderDayStat extends Model
{
    protected $table = 'order_day_stats';

    protected $fillable = ['distribution_channel_id','channel_user_id','distribution_channel_name', 'date','pay_success_user_num', 'ticket_recharge_user_num', 'year_recharge_user_num', 'total_recharge_amount','unpaid_num','paid_num','promotion_total_uv','promotion_total_pv','force_user_num','ticket_recharge_paid_num','ticket_recharge_unpaid_num','ticket_recharge_amount','year_recharge_paid_num','year_recharge_unpaid_num','year_recharge_amount','send_order_num','real_send_order_num','register_user_num','first_recharge_user_num','first_recharge_amount','month','reg_user_first_recharge_amount','reg_user_first_recharge_user_num','fee','charge_balance','reward_balance','real_register_user_num','channel_merchant_recharge_amount',
        'whole_site_uv','whole_site_pv','auto_force_sub_num','self_force_sub_num','subscribe_user_num','subscribe_amount','old_user_subscribe_num','male_channel_recharge','female_channel_recharge'];

    //创建日统计数据
    static function add($params)
    {
        $data = [];
        $data['channel_user_id'] = $params['channel_user_id'];
        $data['distribution_channel_id'] = $params['distribution_channel_id'];
        $data['distribution_channel_name'] = $params['distribution_channel_name'];
        $data['date'] = $params['date'];
        $data['month'] = $params['month'];
        $data['pay_success_user_num'] = $params['pay_success_user_num'];
        $data['ticket_recharge_user_num'] = $params['ticket_recharge_user_num'];
        $data['year_recharge_user_num'] = $params['year_recharge_user_num'];
        $data['total_recharge_amount'] = $params['total_recharge_amount'];
        $data['unpaid_num'] = $params['unpaid_num'];
        $data['paid_num'] = $params['paid_num'];
        $data['promotion_total_uv'] = $params['promotion_total_uv'];
        $data['promotion_total_pv'] = $params['promotion_total_pv'];
        $data['force_user_num'] = $params['force_user_num'];

        //普通充值数据
        $data['ticket_recharge_paid_num'] = $params['ticket_recharge_paid_num'];
        $data['ticket_recharge_unpaid_num'] = $params['ticket_recharge_unpaid_num'];
        $data['ticket_recharge_amount'] =  $params['ticket_recharge_amount'];

        //包年充值数据
        $data['year_recharge_paid_num'] = $params['year_recharge_paid_num'];
        $data['year_recharge_unpaid_num'] = $params['year_recharge_unpaid_num'];
        $data['year_recharge_amount'] = $params['year_recharge_amount'];
        $data['send_order_num'] = $params['send_order_num'];
        $data['real_send_order_num'] = $params['real_send_order_num'];
        $data['register_user_num'] = $params['register_user_num'];
        $data['real_register_user_num'] = $params['real_register_user_num'];

        //注册用户当日充值数据
        $data['reg_user_first_recharge_amount'] = $params['reg_user_first_recharge_amount'];
        $data['reg_user_first_recharge_user_num'] = $params['reg_user_first_recharge_user_num'];

        //首充数据
        $data['first_recharge_user_num'] = $params['first_recharge_user_num'];
        $data['first_recharge_amount'] = $params['first_recharge_amount'];

        $data['channel_merchant_recharge_amount'] = $params['channel_merchant_recharge_amount'];
        $data['whole_site_uv'] = $params['whole_site_uv'];
        $data['whole_site_pv'] = $params['whole_site_pv'];
        $data['auto_force_sub_num'] = $params['auto_force_sub_num'];
        $data['self_force_sub_num'] = $params['self_force_sub_num'];
        $data['old_user_subscribe_num'] =  $params['old_user_subscribe_num'];

        // echo "<pre>";
        // print_r($data);
        return self::create($data);

    }
    
    static function accountSearch($params, $is_all)
    {
    
    	$search_obj = self::join('distribution_channels','distribution_channels.id','=','order_day_stats.distribution_channel_id')
    	->join('channel_users','channel_users.id','=','distribution_channels.channel_user_id')
    	->join('companies','companies.id','=','channel_users.company_id')
    	->select("order_day_stats.date",
    			DB::raw("sum(order_day_stats.pay_success_user_num) as pay_success_user_num"),
    			DB::raw("sum(order_day_stats.ticket_recharge_user_num) as ticket_recharge_user_num"),
    			DB::raw("sum(order_day_stats.year_recharge_user_num) as year_recharge_user_num"),
    			DB::raw("sum(order_day_stats.total_recharge_amount) as total_recharge_amount"),
    			DB::raw("sum(order_day_stats.unpaid_num) as unpaid_num"),
    			DB::raw("sum(order_day_stats.paid_num) as paid_num"),
    			DB::raw("sum(order_day_stats.promotion_total_uv) as promotion_total_uv"),
    			DB::raw("sum(order_day_stats.promotion_total_pv) as promotion_total_pv"),
    			DB::raw("sum(order_day_stats.force_user_num) as force_user_num"),
    			"companies.name as company_name",
    			"companies.fans_gender");
    
    	if(isset($params['distribution_channel_id']) && $params['distribution_channel_id'])
    	{
    		if(is_array($params['distribution_channel_id']) && count($params['distribution_channel_id']))
    		{
    			$search_obj->whereIn('order_day_stats.distribution_channel_id',$params['distribution_channel_id']);
    		}else{
    			$search_obj->where('order_day_stats.distribution_channel_id',$params['distribution_channel_id']);
    		}
    	}
    	if(isset($params['begin_time']) && $params['begin_time']) $search_obj->where('order_day_stats.date','>=',$params['begin_time']);
    	if(isset($params['end_time']) && $params['end_time']) $search_obj->where('order_day_stats.date','<=',$params['end_time']);
    
    	$search_obj->groupBy('order_day_stats.date');
    	$search_obj->orderBy('order_day_stats.date','desc');
    	
    	if($is_all)
    	{
    		return $search_obj->get();
    	}else{
    		return $search_obj->paginate();
    	}
    }
    
    static function search($params, $is_all)
    {

        $search_obj = self::join('distribution_channels','distribution_channels.id','=','order_day_stats.distribution_channel_id')
            ->join('channel_users','channel_users.id','=','distribution_channels.channel_user_id')
            ->join('companies','companies.id','=','channel_users.company_id')
            ->select(DB::raw("order_day_stats.*,companies.name as company_name,companies.fans_gender"));

        if(isset($params['orderBy']) && $params['orderBy'])
        {
            $search_obj->orderBy('order_day_stats.'.$params['orderBy'],'desc');
        }else{
            $search_obj->orderBy('order_day_stats.id','desc');
        }
        if(isset($params['distribution_channel_id']) && $params['distribution_channel_id'])
        {
            if(is_array($params['distribution_channel_id']) && count($params['distribution_channel_id']))
            {
                $search_obj->whereIn('order_day_stats.distribution_channel_id',$params['distribution_channel_id']);
            }else{
                $search_obj->where('order_day_stats.distribution_channel_id',$params['distribution_channel_id']);
            }
        }
        if(isset($params['begin_time']) && $params['begin_time']) $search_obj->where('order_day_stats.date','>=',$params['begin_time']);
        if(isset($params['end_time']) && $params['end_time']) $search_obj->where('order_day_stats.date','<=',$params['end_time']);



        if($is_all)
        {
            return $search_obj->get();
        }else{
            return $search_obj->paginate();
        }
    }

    /**
     * 获取渠道汇总统计数据
     * @param  int $distribution_channel_id 分销渠道ID
     */
    static function getSumByChannelId($distribution_channel_id)
    {
        $fields = [
            DB::raw("sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(real_send_order_num) real_send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount
                     
                     ")
        ];
        return self::select($fields)->where('distribution_channel_id', $distribution_channel_id)->first();
    }

    /**
     * 获取昨日渠道汇总统计数据
     * @param  int $distribution_channel_id 分销渠道ID
     */
    static function getYesterdaySumByChannelId($distribution_channel_id)
    {
        $fields = [
            DB::raw("sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount
                     ")
        ];
        $yesterday = date("Y-m-d",strtotime('-1 day'));
        return self::select($fields)->where('distribution_channel_id', $distribution_channel_id)->where('date','=',$yesterday)->first();
    }

    /**
     * 获取当月渠道汇总统计数据
     * @param  int $distribution_channel_id 分销渠道ID
     */
    static function getCurrentMonthSumByChannelId($distribution_channel_id)
    {
        $fields = [
            DB::raw("sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount
                     ")
        ];
        $begin_date = date("Y-m").'-1';
        $end_date = date("Y-m",strtotime('+1 month')).'-1';
        return self::select($fields)->where('distribution_channel_id', $distribution_channel_id)
                   ->where('date','>=',$begin_date)
                   ->where('date','<',$end_date)
                   ->first();
    }

    /**
     * 获取上月渠道汇总统计数据
     * @param  int $distribution_channel_id 分销渠道ID
     */
    static function getLastMonthSumByChannelId($distribution_channel_id)
    {
        $fields = [
            DB::raw("sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount
                     ")
        ];

        $begin_date = date("Y-m",strtotime('-1 month')).'-01';
        $end_date = date("Y-m").'-01';
        return self::select($fields)->where('distribution_channel_id', $distribution_channel_id)
                   ->where('date','>=',$begin_date)
                   ->where('date','<',$end_date)
                   ->first();
    }

    //根据渠道获取合计
    static function getStatByChannel($params)
    {
        $fields = [
            DB::raw("date,
                     sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(real_send_order_num) real_send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(real_register_user_num) real_register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount,
                     sum(reg_user_first_recharge_amount) reg_user_first_recharge_amount,
                     sum(reg_user_first_recharge_user_num) reg_user_first_recharge_user_num,
                     sum(channel_merchant_recharge_amount) channel_merchant_recharge_amount
                     ")
        ];

        $begin_time = $params['begin_time'];
        $end_time = $params['end_time'];
        return self::select($fields)
                          ->where('date','>=',$begin_time)
                          ->where('date','<=',$end_time)
                          ->orderBy('date','desc')
                          ->groupBy('date')
                          ->get();
    }

    static function getAllGroupbyMonth()
    {
        $fields = [
            DB::raw("month,
                     sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(real_send_order_num) real_send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(real_register_user_num) real_register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount
                     ")
        ];
        return self::select($fields)->groupBy('month')->orderBy('month','desc')->orderBy('total_recharge_amount','desc')->get();
    }

    static function getAllGroupbyMonthChannel($params=[],$is_all)
    {
        $fields = [
            DB::raw("month,
                     distribution_channel_id,
                     distribution_channel_name,
                     sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(real_send_order_num) real_send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(real_register_user_num) real_register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount,
                     sum(fee) as fee,
                     sum(charge_balance) as charge_balance,
                     sum(reward_balance) as reward_balance
                     ")
        ];
        $search_obj = self::select($fields);
        if(isset($params['month']) && $params['month']) $search_obj->where('month',$params['month']);
        if(isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('distribution_channel_id',$params['distribution_channel_id']);
        if(isset($params['distribution_channel_name']) && $params['distribution_channel_name']) $search_obj->where('distribution_channel_name',$params['distribution_channel_name']);

        $search_obj->groupBy(['month','distribution_channel_id'])->orderBy('month','desc')->orderBy('total_recharge_amount','desc');

        if($is_all)
        {
            return $search_obj->get();
        }else{
            return $search_obj->paginate();
        }

    }


    static function getOutPayData($params = [], $isAll = false) {
        //渠道方通道充值总额
        $not_in_pay_merchant_ids = env('not_in_pay_merchant_id');

        $sqlObj = self::select('order_day_stats.distribution_channel_id',
                     'order_day_stats.distribution_channel_name',
                     'order_day_stats.month',
                     'order_day_stats.date',
                     'order_day_stats.channel_merchant_recharge_amount',
                     'distribution_channels.pay_merchant_id'
            )
            ->leftjoin('distribution_channels','distribution_channels.id','=','order_day_stats.distribution_channel_id')
            ->where('order_day_stats.channel_merchant_recharge_amount' , '>', 0)
            ->orderBy('order_day_stats.date', 'desc');

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

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

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

    static function getRechargeByChannels($distribution_channels,$time_range) {
        $obj = self::whereIn('distribution_channel_id',$distribution_channels);
        if($time_range['begin_time']){
            $obj->where('date','>=',$time_range['begin_time']);
        }
        if($time_range['end_time']){
            $obj->where('date','<=',$time_range['end_time']);
        }
        return $obj->sum('total_recharge_amount');
    }
}