wangdu_public_api/app/Modules/Channel/Models/BusinessChannelStat.php

<?php
/**
 * Created by PhpStorm.
 * User: tandunzhao
 * Date: 2017/12/26
 * Time: 下午3:21
 */

namespace App\Modules\Channel\Models;


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

/**
 * 商务渠道数据统计
 * Class BusinessChannelStat
 * @package App\Modules\Channel\Models
 */
class BusinessChannelStat extends Model
{
    protected  $table = 'business_channel_stats';
    protected  $fillable = ['distribution_channel_id', 'yesterday_register_user_num', 'current_month_register_user_num', 'last_month_register_user_num', 'total_register_user_num', 'total_send_order_num',
        'service_account_num','last_week_login_days','current_week_login_days','last_week_actual_send_orders','current_week_actual_send_orders','yesterday_create_orders','is_login_yesterday',
        'current_month_new_channels_recharge','current_month_channels_recharge','last_month_channels_recharge','channel_total_recharge','last_month_new_channel_recharge'];

    public static function getByChannelID($channel_id)
    {
        return self::where('distribution_channel_id',$channel_id)->first();
    }

    /**
     * @param $channel_id
     * @param $params [yesterday_register_user_num， current_month_register_user_num，last_month_register_user_num，total_register_user_num，total_send_order_num]
     * @return mixed
     */
    public static function crateUpdate($channel_id, $params) {
        $emptyData = false;
        $data = self::where('distribution_channel_id', $channel_id)->first();
        if(empty($data)) {
            $emptyData = true;
        }

        $data['distribution_channel_id'] = $channel_id;

        if(isset($params['yesterday_register_user_num']) && is_numeric($params['yesterday_register_user_num'])) {
            $data['yesterday_register_user_num'] = $params['yesterday_register_user_num'];
        }
        if(isset($params['current_month_register_user_num']) && is_numeric($params['current_month_register_user_num'])) {
            $data['current_month_register_user_num'] = $params['current_month_register_user_num'];
        }
        if(isset($params['last_month_register_user_num']) && is_numeric($params['last_month_register_user_num'])) {
            $data['last_month_register_user_num'] = $params['last_month_register_user_num'];
        }
        if(isset($params['total_register_user_num']) && is_numeric($params['total_register_user_num'])) {
            $data['total_register_user_num'] = $params['total_register_user_num'];
        }
        if(isset($params['total_send_order_num']) && is_numeric($params['total_send_order_num'])) {
            $data['total_send_order_num'] = $params['total_send_order_num'];
        }
        if(isset($params['service_account_num']) && $params['service_account_num']) {
            $data['service_account_num'] = intval($params['service_account_num']);
        }
        if(isset($params['last_week_login_days']) && $params['last_week_login_days']) {
            $data['last_week_login_days'] = intval($params['last_week_login_days']);
        }
        if(isset($params['current_week_login_days']) && $params['current_week_login_days']) {
            $data['current_week_login_days'] = intval($params['current_week_login_days']);
        }
        if(isset($params['current_week_actual_send_orders']) && $params['current_week_actual_send_orders']) {
            $data['current_week_actual_send_orders'] = intval($params['current_week_actual_send_orders']);
        }
        if(isset($params['yesterday_create_orders']) && $params['yesterday_create_orders']) {
            $data['yesterday_create_orders'] = intval($params['yesterday_create_orders']);
        }
        if(isset($params['is_login_yesterday']) && $params['is_login_yesterday']) {
            $data['is_login_yesterday'] = intval($params['is_login_yesterday']);
        }
        if(isset($params['current_month_new_channels_recharge']) && is_numeric($params['current_month_new_channels_recharge'])) {
            $data['current_month_new_channels_recharge'] = $params['current_month_new_channels_recharge'];
        }
        if(isset($params['current_month_channels_recharge']) && is_numeric($params['current_month_channels_recharge'])) {
            $data['current_month_channels_recharge'] = $params['current_month_channels_recharge'];
        }
        if(isset($params['last_month_channels_recharge']) && is_numeric($params['last_month_channels_recharge'])) {
            $data['last_month_channels_recharge'] = $params['last_month_channels_recharge'];
        }
        if(isset($params['last_month_new_channel_recharge']) && is_numeric($params['last_month_new_channel_recharge'])) {
            $data['last_month_new_channel_recharge'] = $params['last_month_new_channel_recharge'];
        }
        if(isset($params['channel_total_recharge']) && is_numeric($params['channel_total_recharge'])) {
            $data['channel_total_recharge'] = $params['channel_total_recharge'];
        }

        if($emptyData) {
            $data = BusinessChannelStat::create($data);
        } else {
            $data->save();
        }
        return $data;
    }


    /**
     * 商务渠道数据统计列表
     * @param $params[]
     *                  channel_id:渠道ID 可选
     *                  channel_name:渠道名称 可选
     *                  search_name: 搜索名称
     *                  start_date:开始时间 可选
     *                  end_date:结束时间 可选
     *                  is_enabled: 是否开通 0:未审核； 1:审核通过
     *                  distribution_manages_id: 管理员 可选
     *                  yesterday_register_user_num_sort: 0 1
     *                  current_month_register_user_num_sort: 0 1
     *                  last_month_register_user_num_sort: 0 1
     *                  total_register_user_num_sort: 0 1
     *                  total_send_order_num_sort: 0 1
     *
     * @param string $isAll
     * @return mixed
     */
    public static function getList($params, $isAll = '') {
        $search_object = Channel::select([
            'distribution_channels.id',
            'distribution_channels.name',
            'distribution_channels.pay_merchant_id',
            'distribution_channels.phone',
            'distribution_channels.nickname',
            'distribution_channels.person_in_charge_name',
            'distribution_channels.latest_login_time',
            'distribution_channels.remark',
            'distribution_channels.latest_login_ip',
            'distribution_channels.password',
            'distribution_channels.register_ip',
            'distribution_channels.is_enabled',
            'distribution_channels.distribution_manages_id',
            'distribution_channels.created_at',
            'distribution_manages.account as distribution_manages_account',
            'distribution_manages.number as distribution_manages_number',
            'distribution_manages.nickname as distribution_manages_nickname',
            'business_channel_stats.yesterday_register_user_num as business_channel_stats_yesterday_register_user_num',
            'business_channel_stats.current_month_register_user_num as business_channel_stats_current_month_register_user_num',
            'business_channel_stats.last_month_register_user_num as business_channel_stats_last_month_register_user_num',
//            'business_channel_stats.total_register_user_num as business_channel_stats_total_register_user_num',
            'business_channel_stats.total_send_order_num as business_channel_stats_total_send_order_num',
        ])
            ->leftjoin('distribution_manages','distribution_manages.id','=','distribution_channels.distribution_manages_id')
            ->leftjoin('business_channel_stats','business_channel_stats.distribution_channel_id','=','distribution_channels.id');

        $search_object->where(function ($query) use($params) {
            $query->where('distribution_manages.role', 'business')
                ->orWhereNull('distribution_manages.role');
        });

        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['search_name']) && $params['search_name']) {
            $search_object->where(function ($query) use($params) {
                $query->where('distribution_channels.name','like', "%".$params['search_name']."%")
                    ->orWhere('distribution_channels.nickname', 'like', "%".$params['search_name']."%")
                    ->orWhere('distribution_channels.person_in_charge_name', 'like', "%".$params['search_name']."%");
            });
        }

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

        if(isset($params['is_enabled']) && is_numeric($params['is_enabled'])) {
            $search_object->where('distribution_channels.is_enabled', $params['is_enabled']);
        }

        if(isset($params['distribution_manages_id']) && is_numeric($params['distribution_manages_id'])) {
            $search_object->where('distribution_channels.distribution_manages_id', $params['distribution_manages_id']);
        }

        if(isset($params['yesterday_register_user_num_sort']) && is_numeric($params['yesterday_register_user_num_sort'])) {
            if($params['yesterday_register_user_num_sort'] == 1) {
                $search_object->orderBy('business_channel_stats.yesterday_register_user_num','desc');
            } else {
                $search_object->orderBy('business_channel_stats.yesterday_register_user_num','asc');
            }
        }
        if(isset($params['current_month_register_user_num_sort']) && is_numeric($params['current_month_register_user_num_sort'])) {
            if($params['current_month_register_user_num_sort'] == 1) {
                $search_object->orderBy('business_channel_stats.current_month_register_user_num','desc');
            } else {
                $search_object->orderBy('business_channel_stats.current_month_register_user_num','asc');
            }
        }
        if(isset($params['last_month_register_user_num_sort']) && is_numeric($params['last_month_register_user_num_sort'])) {
            if($params['last_month_register_user_num_sort'] == 1) {
                $search_object->orderBy('business_channel_stats.last_month_register_user_num','desc');
            } else {
                $search_object->orderBy('business_channel_stats.last_month_register_user_num','asc');
            }
        }
        if(isset($params['total_register_user_num_sort']) && is_numeric($params['total_register_user_num_sort'])) {
            if($params['total_register_user_num_sort'] == 1) {
                $search_object->orderBy('business_channel_stats.total_register_user_num','desc');
            } else {
                $search_object->orderBy('business_channel_stats.total_register_user_num','asc');
            }
        }

        if(isset($params['total_send_order_num_sort']) && is_numeric($params['total_send_order_num_sort'])) {
            if($params['total_send_order_num_sort'] == 1) {
                $search_object->orderBy('business_channel_stats.total_send_order_num','desc');
            } else {
                $search_object->orderBy('business_channel_stats.total_send_order_num','asc');
            }
        }

        if($isAll) {
            return $search_object->get();

        } else {

            return $search_object->paginate();
        }
    }

    /**
     * 获取指定渠道列表汇总
     * @param array $channelIds
     * @return mixed
     */
    public static function getBusinessChannelStatSingle($channelIds=[]) {
        $search_object = self::select(
            DB::raw('sum(yesterday_register_user_num)  as  yesterday_register_user_num'),
            DB::raw('sum(current_month_register_user_num)  as  current_month_register_user_num'),
            DB::raw('sum(last_month_register_user_num)  as  last_month_register_user_num'),
            DB::raw('sum(total_register_user_num) as  total_register_user_num'),
            DB::raw('sum(total_send_order_num) as  total_send_order_num'),
            DB::raw('sum(current_month_new_channels_recharge) as  current_month_new_channels_recharge_sum'),
            DB::raw('sum(current_month_channels_recharge) as  current_month_channels_recharge_sum'),
            DB::raw('sum(last_month_channels_recharge) as  last_month_channels_recharge_sum'),
            DB::raw('sum(last_month_new_channel_recharge) as  last_month_new_channels_recharge_sum')

        );
        $search_object->whereIn('business_channel_stats.distribution_channel_id', $channelIds);
        return $search_object->first();
    }

    /**
     * @param $params
     * @param string $isAll
     */
    public static function getListNew($params, $isAll = '') {
        $search_object = Channel::select(DB::raw('channel_users.company_id,
        distribution_manages.nickname as manage_name,
        companies.city as city,
        sum(business_channel_stats.yesterday_register_user_num) as business_channel_stats_yesterday_register_user_num,
        sum(business_channel_stats.current_month_register_user_num) as business_channel_stats_current_month_register_user_num,
        sum(business_channel_stats.last_month_register_user_num) as business_channel_stats_last_month_register_user_num,
        sum(business_channel_stats.total_send_order_num) as business_channel_stats_total_send_order_num,
        sum(business_channel_stats.service_account_num) as service_account_sum,
        sum(business_channel_stats.last_week_actual_send_orders) as last_week_actual_send_orders_sum,
        sum(business_channel_stats.current_week_actual_send_orders) as current_week_actual_send_orders_sum,
        sum(business_channel_stats.yesterday_create_orders) as yesterday_create_orders_sum,
        max(business_channel_stats.last_week_login_days) as last_week_login_day_sum,
        max(business_channel_stats.current_week_login_days) as current_week_login_days_sum,
        max(business_channel_stats.is_login_yesterday) as is_yesterday_login,
        sum(business_channel_stats.last_month_channels_recharge) as last_month_channel_recharge_sum'))
            ->leftjoin('channel_users','channel_users.id','=','distribution_channels.channel_user_id')
            ->leftjoin('companies','channel_users.company_id','=','companies.id')
            ->leftjoin('distribution_manages','distribution_manages.id','=','channel_users.distribution_manages_id')
            ->leftjoin('business_channel_stats','business_channel_stats.distribution_channel_id','=','distribution_channels.id')
            ->groupBy('channel_users.company_id')
            ->whereNotNull('channel_users.company_id');
        /*$search_object->where(function ($query) use($params) {
            $query->where('distribution_manages.role', 'business')
                ->orWhereNull('distribution_manages.role');
        });*/

        if(isset($params['company_id']) && $params['company_id']) $search_object->where('companies.id', $params['company_id']);
        if(isset($params['company_name']) && $params['company_name']) $search_object->where('companies.name','like', "%".$params['company_name']."%");

        if(isset($params['manager_name']) && $params['manager_name']){
            $search_object->where('distribution_manages.nickname','like', "%".$params['manager_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.nickname', 'like', "%".$params['search_name']."%")
                    ->orWhere('distribution_channels.person_in_charge_name', 'like', "%".$params['search_name']."%");
            });
        }

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

        if(isset($params['is_enabled']) && is_numeric($params['is_enabled'])) {
            $search_object->where('distribution_channels.is_enabled', $params['is_enabled']);
        }

        if(isset($params['distribution_manages_id']) && is_numeric($params['distribution_manages_id'])) {
            $search_object->where('distribution_manages.id', $params['distribution_manages_id']);
        }

        if(isset($params['yesterday_register_user_num_sort']) && is_numeric($params['yesterday_register_user_num_sort'])) {
            if($params['yesterday_register_user_num_sort'] == 1) {
                $search_object->orderBy('business_channel_stats.yesterday_register_user_num','desc');
            } else {
                $search_object->orderBy('business_channel_stats.yesterday_register_user_num','asc');
            }
        }
        if(isset($params['current_month_register_user_num_sort']) && is_numeric($params['current_month_register_user_num_sort'])) {
            if($params['current_month_register_user_num_sort'] == 1) {
                $search_object->orderBy('business_channel_stats.current_month_register_user_num','desc');
            } else {
                $search_object->orderBy('business_channel_stats.current_month_register_user_num','asc');
            }
        }
        if(isset($params['last_month_register_user_num_sort']) && is_numeric($params['last_month_register_user_num_sort'])) {
            if($params['last_month_register_user_num_sort'] == 1) {
                $search_object->orderBy('business_channel_stats.last_month_register_user_num','desc');
            } else {
                $search_object->orderBy('business_channel_stats.last_month_register_user_num','asc');
            }
        }
        if(isset($params['total_register_user_num_sort']) && is_numeric($params['total_register_user_num_sort'])) {
            if($params['total_register_user_num_sort'] == 1) {
                $search_object->orderBy('business_channel_stats.total_register_user_num','desc');
            } else {
                $search_object->orderBy('business_channel_stats.total_register_user_num','asc');
            }
        }

        if(isset($params['total_send_order_num_sort']) && is_numeric($params['total_send_order_num_sort'])) {
            if($params['total_send_order_num_sort'] == 1) {
                $search_object->orderBy('business_channel_stats.total_send_order_num','desc');
            } else {
                $search_object->orderBy('business_channel_stats.total_send_order_num','asc');
            }
        }

        if($isAll) {
            return $search_object->get();

        } else {

            return $search_object->paginate();
        }
    }

    public static function getNewAddedCompaniesNum($start_time,$end_time,$channelIds) {
        $res = DB::table('companies')
            ->leftjoin('channel_users','channel_users.company_id','=','companies.id')
            ->leftjoin('distribution_channels','distribution_channels.channel_user_id','=','channel_users.id')
            ->where([
                ['companies.created_at','>=',$start_time],
                ['companies.created_at','<=',$end_time]
            ]);
        if($channelIds){
            $res->whereIn('distribution_channels.id',$channelIds);
        }
        return $res->count(DB::raw('distinct companies.id'));
    }
    public static function getNewwAddedChannelNum($start_time,$end_time,$channelIds='') {
        $res = DB::table('distribution_channels')
            //->leftjoin('channel_users','channel_users.id','=','distribution_user_id')
            ->where([
                ['created_at','>=',$start_time],
                ['created_at','<=',$end_time]
            ]);
        if($channelIds) {
            $res->whereIn('id',$channelIds);
        }
        return $res->count('id');
    }
}