wangdu_public_api/app/Modules/Trade/Services/OrderDayStatService.php

<?php
/**
 * Created by PhpStorm.
 * User: hp
 * Date: 2017/11/21
 * Time: 10:42
 */

namespace App\Modules\Trade\Services;
use App\Modules\Statistic\Services\WapVisitStatService;
use App\Modules\Trade\Services\OrderService;
use App\Modules\OfficialAccount\Services\ForceSubscribeService;
use App\Modules\SendOrder\Services\SendOrderService;
use App\Modules\User\Services\UserService;
use App\Modules\Trade\Models\OrderDayStat;
use App\Modules\Trade\Models\Order;
use DB;

class OrderDayStatService
{

    //创建日统计数据
    //'distribution_channel_id', 'date','pay_success_user_num', 'ticket_recharge_user_num', 'year_recharge_user_num', 'total_recharge_amount','unpaid_num','paid_num','current_month_unpaid_num','promotion_total_uv','promotion_total_pv','force_user_num'
    static function add($distribution_channel_id, $channel_user_id, $date, $distribution_channel_name,$real_reg_num)
    {
        $data = [];
        $data['channel_user_id'] = $channel_user_id;
        $data['distribution_channel_id'] = $distribution_channel_id;
        $data['distribution_channel_name'] = $distribution_channel_name;
        $data['date'] = $date;
        $data['month'] = date("Y-m",strtotime($date));
        $data['pay_success_user_num'] = OrderService::getDayPayUserNumByChannelId($distribution_channel_id, $date);//付费总用户数
        $data['ticket_recharge_user_num'] = OrderService::getDayRechargePayUserNumByChannelId($distribution_channel_id, $date);//ticket_recharge_user_num
        $data['year_recharge_user_num'] = OrderService::getDayVipPayUserNumByChannelId($distribution_channel_id, $date);//year_recharge_user_num
        
        $end_time = date('Y-m-d H:i:s',strtotime($date) + 86400-1);
        $data['total_recharge_amount'] = OrderService::getAmount(['distribution_channel_id'=>$distribution_channel_id,'begin_time'=>$date,'end_time'=>$end_time]);//total_recharge_amount
        
        $order_info = OrderService::getChannelDateOrderInfo($distribution_channel_id,$date);
        $data['unpaid_num'] = $order_info['total_num'] - $order_info['paid_number'];
        $data['paid_num'] = $order_info['paid_number'];

        $uv_pv = SendOrderService::getChannelPromotionTotalUvPv($distribution_channel_id,$date);
        $data['promotion_total_uv'] = $uv_pv['uv'];
        $data['promotion_total_pv'] = $uv_pv['pv'];

        $data['force_user_num'] = ForceSubscribeService::forceSubscribeUserCountByChannelIdAndDate($distribution_channel_id,$date,'');

        //普通充值数据
        $ticket_recharge_data = OrderService::getChannelDayTicketRechargeData($distribution_channel_id, $date);
        $data['ticket_recharge_paid_num'] = $ticket_recharge_data['ticket_recharge_paid_num'];
        $data['ticket_recharge_unpaid_num'] = $ticket_recharge_data['ticket_recharge_unpaid_num'];
        $data['ticket_recharge_amount'] =  $ticket_recharge_data['ticket_recharge_amount'];

        //包年充值数据
        $year_recharge_data = OrderService::getChannelDayYearRechargeData($distribution_channel_id, $date);
        $data['year_recharge_paid_num'] = $year_recharge_data['year_recharge_paid_num'];
        $data['year_recharge_unpaid_num'] = $year_recharge_data['year_recharge_unpaid_num'];
        $data['year_recharge_amount'] = $year_recharge_data['year_recharge_amount'];

        //派单数
        $data['send_order_num'] = SendOrderService::getChannelPromotionCount($distribution_channel_id, $date, $end_time);

        //实际派单数
        $data['real_send_order_num'] = SendOrderService::getChannelRealPromotionCount($distribution_channel_id, $date, $end_time);

        //注册用户数
        $data['register_user_num'] = UserService::getChannelDayTotal($distribution_channel_id,$date);

        //实际注册用户数
        $data['real_register_user_num'] = $real_reg_num;

        //注册用户当日充值数据
        $reg_user_data = DB::select("select count(distinct uid) num,sum(price) amount from orders,users where orders.distribution_channel_id = '{$distribution_channel_id}' and orders.created_at > '{$date}' and orders.created_at <= '{$end_time}' and status ='PAID' and users.id = orders.uid and users.created_at > '{$date}' and users.created_at < '{$end_time}'");
        $data['reg_user_first_recharge_amount'] = (float)$reg_user_data[0]->amount;
        $data['reg_user_first_recharge_user_num'] = (float)$reg_user_data[0]->num;

        $old_user_subscribe_num = ForceSubscribeService::getOldUserNewSubsribeNum(array('distribution_channel_id'=>$distribution_channel_id,'start_time'=>date('Y-m-d',strtotime('-1 day')),'end_time'=>date('Y-m-d 23:59:59',strtotime('-1 day'))));
        $data['old_user_subscribe_num'] = $old_user_subscribe_num;


        $data2 = WapVisitStatService::getReaderUvAndPv($distribution_channel_id, date('Y-m-d',strtotime('-1 day')));
        $whole_pv=$whole_uv=0;
        if ($data2) {
            $whole_uv = $data2['uv'];
            $whole_pv = $data2['pv'];
        }
        try{
            $data['whole_site_uv'] =$whole_uv;
            $data['whole_site_pv'] =$whole_pv;
            $data['auto_force_sub_num']=self::getAutoForceSubUserNum($distribution_channel_id,$date,$end_time);
            $data['self_force_sub_num']=self::getSelfForceSubUserNum($distribution_channel_id,$date,$end_time);

        }catch (\Exception $e){
            \Log::error($e->getMessage());
        }

        //渠道方通道充值总额
        $not_in_pay_merchant_ids = env('not_in_pay_merchant_id');
        if($not_in_pay_merchant_ids)
        {
            $channel_merchant_recharge_amount = DB::select("select sum(price) amount from orders join pay_merchants on pay_merchants.id=orders.pay_merchant_id where orders.distribution_channel_id = '{$distribution_channel_id}' and orders.created_at > '{$date}' and orders.created_at <= '{$end_time}' and orders.status ='PAID' and pay_merchants.is_self_channel <> 1 and orders.pay_merchant_id not in (44,41)");//pay_merchant_id in ({$not_in_pay_merchant_ids})
        }
        $data['channel_merchant_recharge_amount'] = (float)$channel_merchant_recharge_amount[0]->amount;

        //首充数据
        $first_recharge_data = OrderService::getChannelDayFirstRechargeData($distribution_channel_id,$date);
        $data['first_recharge_user_num'] = $first_recharge_data['count'];
        $data['first_recharge_amount'] = $first_recharge_data['amount'];

        return OrderDayStat::add($data);
    }



    static function tempAdd($distribution_channel_id, $channel_user_id, $date, $distribution_channel_name,$real_reg_num)
    {
        $data = [];
        $data['channel_user_id'] = $channel_user_id;
        $data['distribution_channel_id'] = $distribution_channel_id;
        $data['distribution_channel_name'] = $distribution_channel_name;
        $data['date'] = $date;
        $data['month'] = date("Y-m",strtotime($date));
        $data['pay_success_user_num'] = OrderService::getDayPayUserNumByChannelId($distribution_channel_id, $date);//付费总用户数
        $data['ticket_recharge_user_num'] = OrderService::getDayRechargePayUserNumByChannelId($distribution_channel_id, $date);//ticket_recharge_user_num
        $data['year_recharge_user_num'] = OrderService::getDayVipPayUserNumByChannelId($distribution_channel_id, $date);//year_recharge_user_num

        $end_time = date('Y-m-d H:i:s',strtotime($date) + 86400-1);
        $data['total_recharge_amount'] = OrderService::getAmount(['distribution_channel_id'=>$distribution_channel_id,'begin_time'=>$date,'end_time'=>$end_time]);//total_recharge_amount

        //$order_info = OrderService::getChannelDateOrderInfo($distribution_channel_id,$date);
        $result = Order::where('distribution_channel_id',$distribution_channel_id)->where('created_at','>=',$date)
            ->select(DB::raw('status,count(*) as count'))
            ->where('created_at','<=',$date.' 23:59:59')->groupBy('status')->get();
        $data['unpaid_num'] = 0;
        $data['paid_num'] = 0;
        foreach ($result as $item){
            if($item->status == 'PAID') $data['paid_num'] = $item->count;
            if($item->status == 'UNPAID') $data['unpaid_num'] = $item->count;
        }
        //$data['unpaid_num'] = $order_info['total_num'] - $order_info['paid_number'];
        //$data['paid_num'] = $order_info['paid_number'];

        $uv_pv = SendOrderService::getChannelPromotionTotalUvPv($distribution_channel_id,$date);
        $data['promotion_total_uv'] = $uv_pv['uv'];
        $data['promotion_total_pv'] = $uv_pv['pv'];

        $data['force_user_num'] = ForceSubscribeService::forceSubscribeUserCountByChannelIdAndDate($distribution_channel_id,$date,'');

        //普通充值数据
        $ticket_recharge_data = OrderService::getChannelDayTicketRechargeData($distribution_channel_id, $date);
        $data['ticket_recharge_paid_num'] = $ticket_recharge_data['ticket_recharge_paid_num'];
        $data['ticket_recharge_unpaid_num'] = $ticket_recharge_data['ticket_recharge_unpaid_num'];
        $data['ticket_recharge_amount'] =  $ticket_recharge_data['ticket_recharge_amount'];

        //包年充值数据
        $year_recharge_data = OrderService::getChannelDayYearRechargeData($distribution_channel_id, $date);
        $data['year_recharge_paid_num'] = $year_recharge_data['year_recharge_paid_num'];
        $data['year_recharge_unpaid_num'] = $year_recharge_data['year_recharge_unpaid_num'];
        $data['year_recharge_amount'] = $year_recharge_data['year_recharge_amount'];

        //派单数
        $data['send_order_num'] = SendOrderService::getChannelPromotionCount($distribution_channel_id, $date, $end_time);

        //实际派单数
        $data['real_send_order_num'] = SendOrderService::getChannelRealPromotionCount($distribution_channel_id, $date, $end_time);

        //注册用户数
        $data['register_user_num'] = UserService::getChannelDayTotal($distribution_channel_id,$date);

        //实际注册用户数
        $data['real_register_user_num'] = $real_reg_num;

        //注册用户当日充值数据
        $reg_user_data = DB::select("select count(distinct uid) num,sum(price) amount from orders,users where orders.distribution_channel_id = '{$distribution_channel_id}' and orders.created_at > '{$date}' and orders.created_at <= '{$end_time}' and status ='PAID' and users.id = orders.uid and users.created_at > '{$date}' and users.created_at < '{$end_time}'");
        $data['reg_user_first_recharge_amount'] = (float)$reg_user_data[0]->amount;
        $data['reg_user_first_recharge_user_num'] = (float)$reg_user_data[0]->num;

        $old_user_subscribe_num = ForceSubscribeService::getOldUserNewSubsribeNum(array('distribution_channel_id'=>$distribution_channel_id,'start_time'=>date('Y-m-d',strtotime('-1 day')),'end_time'=>date('Y-m-d 23:59:59',strtotime('-1 day'))));
        $data['old_user_subscribe_num'] = $old_user_subscribe_num;


        $data2 = WapVisitStatService::getReaderUvAndPv($distribution_channel_id, date('Y-m-d',strtotime('-1 day')));
        $whole_pv=$whole_uv=0;
        if ($data2) {
            $whole_uv = $data2['uv'];
            $whole_pv = $data2['pv'];
        }
        try{
            $data['whole_site_uv'] =$whole_uv;
            $data['whole_site_pv'] =$whole_pv;
            $data['auto_force_sub_num']=self::getAutoForceSubUserNum($distribution_channel_id,$date,$end_time);
            $data['self_force_sub_num']=self::getSelfForceSubUserNum($distribution_channel_id,$date,$end_time);

        }catch (\Exception $e){
            \Log::error($e->getMessage());
        }

        //渠道方通道充值总额
        $not_in_pay_merchant_ids = env('not_in_pay_merchant_id');
        if($not_in_pay_merchant_ids)
        {
            $channel_merchant_recharge_amount = DB::select("select sum(price) amount from orders join pay_merchants on pay_merchants.id=orders.pay_merchant_id where orders.distribution_channel_id = '{$distribution_channel_id}' and orders.created_at > '{$date}' and orders.created_at <= '{$end_time}' and orders.status ='PAID' and pay_merchants.is_self_channel <> 1 and orders.pay_merchant_id not in (44,41)");//pay_merchant_id in ({$not_in_pay_merchant_ids})
        }
        $data['channel_merchant_recharge_amount'] = (float)$channel_merchant_recharge_amount[0]->amount;

        //首充数据
        $first_recharge_data = OrderService::getChannelDayFirstRechargeData($distribution_channel_id,$date);
        $data['first_recharge_user_num'] = $first_recharge_data['count'];
        $data['first_recharge_amount'] = $first_recharge_data['amount'];


        //获取前天用户充值
        $dayBeforeYesDay = OrderDayStat::where('distribution_channel_id',$distribution_channel_id)
            ->where('date',date('Y-m-d',strtotime($date.' -1 day')))
            ->first();
        //\Log::info(date('Y-m-d',strtotime($date.' -1 day')));
        if($dayBeforeYesDay){
            $dayBeforeYesDay = $dayBeforeYesDay->total_recharge_amount;
            $dayBeforeYesDayReal = OrderService::getAmount(['distribution_channel_id'=>$distribution_channel_id,'begin_time'=>date('Y-m-d',strtotime($date.' -1 day')),'end_time'=>date('Y-m-d',strtotime($date.' -1 day')).' 23:59:59']);
            $diff = $dayBeforeYesDayReal-$dayBeforeYesDay;
            if($diff>0){
                $data['total_recharge_amount'] +=$diff;
                \Log::info('distribution_channel_recharge_regards_as_next_day_for_order_day_stats:distribution:'.$distribution_channel_id.':date:'.$date.':diff:'.$diff);
            }
        }
        /*try{
            //男女频充值
            $recharge_by_gender_raw = self::getRechargeByGender($distribution_channel_id,date('Y-m-d',strtotime($date.' -1 day')),date('Y-m-d',strtotime($date.' -1 day')).' 23:59:59');
            $recharge_amount_left = $data['total_recharge_amount']-$recharge_by_gender_raw['amount'];
            $recharge_male_channel = $recharge_female_channel = 0;
            if(isset($recharge_by_gender_raw[1])) {
                $recharge_male_channel = $recharge_by_gender_raw[1]+($recharge_by_gender_raw[1]/$recharge_by_gender_raw['amount'])*$recharge_amount_left;
            }
            if(isset($recharge_by_gender_raw[2])) {
                $recharge_female_channel = $recharge_by_gender_raw[2]+($recharge_by_gender_raw[2]/$recharge_by_gender_raw['amount'])*$recharge_amount_left;
            }
            $data['male_channel_recharge']=$recharge_male_channel;
            $data['female_channel_recharge']=$recharge_female_channel;
        }catch (\Exception $exception){
            \Log::error($exception->getMessage());
        }*/

        return OrderDayStat::add($data);
    }

    /**
     * 查询日统计
     * @param  $params $distribution_channel_id
     */
    static function search($params, $is_all=false)
    {
        return OrderDayStat::search($params, $is_all);
    }
    
    /**
     * 账户级别查询日统计
     * @param  $params $distribution_channel_id
     */
    static function accountSearch($params, $is_all=false)
    {
    	return OrderDayStat::accountSearch($params, $is_all);
    }

    /**
     * 获取渠道汇总统计
     */
    static function getSumByChannelId($distribution_channel_id)
    {
        return OrderDayStat::getSumByChannelId($distribution_channel_id);
    }

    /**
     * 获取昨日渠道汇总统计
     */
    static function getYesterdaySumByChannelId($distribution_channel_id)
    {
        return OrderDayStat::getYesterdaySumByChannelId($distribution_channel_id);
    }

    /**
     * 获取当月渠道汇总统计
     */
    static function getCurrentMonthSumByChannelId($distribution_channel_id)
    {
        return OrderDayStat::getCurrentMonthSumByChannelId($distribution_channel_id);
    }

    /**
     * 获取上月渠道汇总统计
     */
    static function getLastMonthSumByChannelId($distribution_channel_id)
    {
        return OrderDayStat::getLastMonthSumByChannelId($distribution_channel_id);
    }

    /**
     * 根据渠道获取合计
     */
    static function getStatByChannel($params)
    {
        return OrderDayStat::getStatByChannel($params);
    }

    /**
     *
     */
    static function getAllGroupbyMonth()
    {
        return OrderDayStat::getAllGroupbyMonth();
    }

    static function getAllGroupbyMonthChannel($params,$is_all=false)
    {
        return OrderDayStat::getAllGroupbyMonthChannel($params,$is_all);
    }


    /**
     * 获取外部渠道充值数据
     * @param array $params
     * @param bool $isAll
     */
    static function getOutPayData($params = [], $isAll = false) {
        return OrderDayStat::getOutPayData($params, $isAll);
    }

    static function getRechargeByChannels($distribution_channels,$time_range){
        return OrderDayStat::getRechargeByChannels($distribution_channels,$time_range);
    }

    /**
     * 获取自动强关用户数
     * @param $channel_id
     * @param $start
     * @param $end
     * @return mixed
     */
    public static function getAutoForceSubUserNum($channel_id,$start,$end){
        $obj= DB::table('force_subscribe_users')

            ->whereBetween('created_at',[$start,$end])
            ->where('official_account_id','<>',1);
        if($channel_id) $obj->where('distribution_channel_id',$channel_id);
        return $obj->count();
    }

    /**
     * 获取主动关注用户数
     * @param $channel_id
     * @param $start
     * @param $end
     * @return mixed
     */
    public static function getSelfForceSubUserNum($channel_id,$start,$end){
        $obj= DB::table('force_subscribe_users')
            ->whereBetween('created_at',[$start,$end])
            ->where('official_account_id','=',1);
        if($channel_id) $obj->where('distribution_channel_id',$channel_id);
        return   $obj->count();
    }

    public static function getRechargeByGender($start,$end) {
        $res = DB::table('orders')
            ->join('books','books.id','=','orders.from_bid')
            ->join('book_categories','book_categories.id','=','books.category_id')
            ->groupBy('orders.distribution_channel_id')
            ->groupBy('book_categories.pid')
            ->where('orders.status','PAID')
            //->where('orders.distribution_channel_id',$channel_id)
            ->whereBetween('orders.created_at',[$start,$end])
            ->select(['book_categories.pid','orders.distribution_channel_id',DB::raw('sum(price) as amount')])
            ->get();
        //$ret = ['amount'=>0];
        $ret = [];
        foreach ($res as $key=>$value){
            if(array_key_exists($value->distribution_channel_id,$ret)) {
                $ret[$value->distribution_channel_id][$value->pid] = $value->amount;
                $ret[$value->distribution_channel_id]['amount'] += $value->amount;

            }else {
                $ret[$value->distribution_channel_id] = array('amount'=>$value->amount);
                $ret[$value->distribution_channel_id][$value->pid] = $value->amount;
            }
        }
        return $ret;
    }
}