<?php
/**
* Created by PhpStorm.
* User: hp
* Date: 2017/11/23
* Time: 19:36
*/
namespace App\Modules\SendOrder\Models;
use DB;
use Illuminate\Database\Eloquent\Model;
class SendOrderForceDayStat extends Model
{
protected $table = 'send_orders_force_day_stats';
protected $fillable = ['send_order_id', 'book_name', 'bid', 'recharge_amount', 'total_uv', 'date', 'total_pv', 'uv', 'pv', 'paid_num', 'unpaid_num', 'distribution_channel_id', 'recharge_amount_in_half_day',
'recharge_amount_in_two_weeks', 'send_time', 'cost', 'year_recharge_user_num', 'ticket_recharge_user_num', 'first_recharge_amount', 'first_recharge_user_num',
'year_paid_num', 'year_unpaid_num', 'pay_user_num', 'force_user_num', 'register_user_num', 'distribution_channel_name', 'name', 'charge_type','create_time','recharge_amount_in_one_day','recharge_amount_in_three_days'];
static function addDayStat($send_order_id, $click_num, $register_user_num, $recharge_amount, $pay_user_num)
{
$preDate = date("Y-m-d H:i:s", strtotime("-1 day"));
self::create(['send_order_id' => $send_order_id, 'click_num' => $click_num, 'register_user_num' => $register_user_num, 'recharge_amount' => $recharge_amount, 'pay_user_num' => $pay_user_num, 'date' => $preDate]);
}
//按书统计
static function getSendOrderDayStatsByBook($params = [])
{
$begin_time = isset($params['begin_time']) ? $params['begin_time'] : date('Y-m-d',strtotime('-1 month'));
$end_time = isset($params['end_time']) ? $params['end_time'] : date('Y-m-d');
$gender = isset($params['gender']) ? $params['gender']:'';
$gender_filter = '';
if(in_array($gender,[1,2,'1','2'])){
$gender_filter = " and book_categories.pid=".$gender;
}
// $sql = " select t.*,count(send_orders.id) send_order_num from ( select bid,
// book_name,book_categories.pid,sum(recharge_amount) as sum_recharge_amount,sum(uv) sum_uv,
// sum(pv) sum_pv,sum(paid_num) sum_paid_num,
// sum(unpaid_num) sum_unpaid_num,sum(register_user_num) sum_register_user_num
// from send_orders_force_day_stats force index(date_bid)
// inner join books on books.id=send_orders_force_day_stats.bid
// INNER JOIN book_categories ON book_categories.id=books.category_id
// where total_uv > 20 and date >='{$begin_time}' and date <= '{$end_time}' $gender_filter group by bid ) as t,send_orders
// where send_orders.book_id = t.bid and send_time >='{$begin_time}' and send_time <= '{$end_time} 23:59:59' group by t.bid order by t.sum_recharge_amount desc";
$sql = " select bid,
book_name,book_categories.pid,sum(recharge_amount) as sum_recharge_amount,sum(uv) sum_uv,
sum(pv) sum_pv,sum(paid_num) sum_paid_num,
sum(unpaid_num) sum_unpaid_num,sum(register_user_num) sum_register_user_num,
(select count(1) from send_orders where book_id = bid and send_time >='{$begin_time}' and send_time <= '{$end_time} 23:59:59') send_order_num
from send_orders_force_day_stats force index(date_bid)
inner join books on books.id=send_orders_force_day_stats.bid
INNER JOIN book_categories ON book_categories.id=books.category_id
where total_uv > 20 and date >='{$begin_time}' and date <= '{$end_time}' $gender_filter group by bid order by sum_recharge_amount desc";
return DB::select($sql);
}
//生成按书统计统计数据
static function generateForceDayStat($data)
{
$conditions = [
'send_order_id' => $data['send_order_id'],
'date' => $data['date'],
];
unset($data['send_order_id']);
unset($data['date']);
return self::updateOrCreate($conditions, $data);
}
/**
* 获取管理后台派单信息
* @param $params 参数列表
* @param $isAll 是否获取所有
* @return mixed
*/
static function getHistorySendOrders($params = [], $is_all = false)
{
$search_object = self::select([DB::raw("send_order_id,book_name, created_at,updated_at,create_time,bid, sum(recharge_amount) recharge_amount,total_uv, date, total_pv,sum(uv) uv, sum(pv) pv, sum(paid_num) paid_num, sum(unpaid_num) unpaid_num,distribution_channel_id, sum(recharge_amount_in_half_day) recharge_amount_in_half_day,sum(recharge_amount_in_two_weeks)recharge_amount_in_two_weeks, send_time, sum(cost) cost, sum(year_recharge_user_num) year_recharge_user_num,sum(ticket_recharge_user_num) ticket_recharge_user_num, sum(first_recharge_amount) first_recharge_amount, sum(first_recharge_user_num) first_recharge_user_num,sum(year_paid_num) year_paid_num, sum(year_unpaid_num) year_unpaid_num, sum(pay_user_num) pay_user_num, sum(force_user_num) force_user_num, sum(register_user_num)register_user_num,distribution_channel_name, name, charge_type")])->groupBy('send_order_id')->orderBy('send_orders_force_day_stats.date', 'desc');
if (isset($params['id']) && $params['id']) $search_object->where('send_order_id', $params['id']);
if (isset($params['book_id']) && $params['book_id']) $search_object->where('book_id', $params['book_id']);
if (isset($params['name']) && $params['name']) $search_object->where('name', 'like', '%' . $params['name'] . '%');
if (isset($params['book_name']) && $params['book_name']) $search_object->where('book_name', $params['book_name']);
if (isset($params['start_time']) && $params['start_time']) $search_object->where('send_orders_force_day_stats.created_at', '>=', $params['start_time']);
if (isset($params['end_time']) && $params['end_time']) $search_object->where('send_orders_force_day_stats.created_at', '<=', $params['end_time'] . ' 23:59:59');
if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_object->where('distribution_channel_id', $params['distribution_channel_id']);
//判断内外部派单
if (isset($params['order_status']) && $params['order_status']) {
if (1 == $params['order_status']) {
$search_object->where('force_user_num', '<', 20);
} elseif (2 == $params['order_status']) {
$search_object->where('force_user_num', '>=', 20);
}
}
if (isset($params['orderBy']) && $params['orderBy']) {
$orderColum = 'date'; //排序的列
$orderByType = 'desc'; //排序的类型(升序还是降序)
if (isset($params['orderByType']) && $params['orderByType']) {
if (2 == $params['orderByType']) {
$orderByType = 'asc';
}
}
//12小时充值
if (1 == $params['orderBy']) {
$orderColum = 'recharge_amount_in_half_day';
//7天充值充值
} elseif (2 == $params['orderBy']) {
$orderColum = 'recharge_amount_in_two_weeks';
//充值总额
} elseif (3 == $params['orderBy']) {
$orderColum = 'recharge_amount';
//首充总额
} elseif (4 == $params['orderBy']) {
$orderColum = 'first_recharge_amount';
}
$search_object->orderBy($orderColum, $orderByType);
}
if ($is_all) {
return $search_object->get();
} else {
return $search_object->paginate();
}
}
public static function getBookSendOrdersRechargeStats($channels) {
// self::
self::select(DB::raw('count(id) as send_orders_num,sum()'))
->where('date',date('Y-m-d'))
->whereIn('distribution_channel_id',$channels)
->groupBy('bid')
->get();
}
}