"; // 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'); } }