<?php
/**
* Created by PhpStorm.
* User: hp
* Date: 2017/11/22
* Time: 14:15
*/
namespace App\Modules\SendOrder\Models;
use App\Modules\Book\Models\BookConfig;
use DB;
use Illuminate\Database\Eloquent\Model;
class SendOrder extends Model
{
protected $table = 'send_orders';
protected $fillable = [
'name',
'channel_type',
'promotion_type',
'distribution_channel_id',
'book_id',
'book_name',
'chapter_id',
'chapter_name',
'headline_id',
'body_template_id',
'document_cover_id',
'original_guide_id',
'subscribe_chapter_id',
'import_company_name',
'fan_num',
'subscribe_chapter_name',
'qr_code_id',
'subscribe_chapter_seq',
'cost',
'sex_preference',
'is_enable',
'entrance',
'domain',
'redirect_url',
'send_time',
'charge_type',
'pre_send_date',
'promotion_point'
];
static function searchForDaliy($params, $isAll = false)
{
$search_object = self::where('is_enable', 1);
$fields = ['send_orders.id', 'book_id', 'name', 'promotion_type', 'send_orders.created_at', 'remark', 'import_company_name', 'send_time', 'promotion_point', 'pre_send_date', 'book_name', 'distribution_channel_id', 'cost'];
$search_object->select($fields);
if (isset($params['distribution_channel_ids']) && $params['distribution_channel_ids']) {
$search_object->whereIn('distribution_channel_id', $params['distribution_channel_ids']);
}
if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) {
$search_object->where('distribution_channel_id', $params['distribution_channel_id']);
}
if (isset($params['start_time']) && $params['start_time']) {
$search_object->where('send_time', '>=', $params['start_time']);
}
if (isset($params['end_time']) && $params['end_time']) {
$search_object->where('send_time', '<=', $params['end_time']);
}
//按预计日期
if (isset($params['pre_date']) && $params['pre_date']) {
$search_object->where('pre_send_date', '=', $params['pre_date']);
}
if (isset($params['promotion_type']) && $params['promotion_type']) {
$search_object->where('promotion_type', $params['promotion_type']);
}
if (isset($params['is_single_site']) && $params['is_single_site']) {
$search_object->orderBy('pre_send_date');
} else {
$search_object->orderBy('distribution_channel_id');
}
if ($isAll) {
return $search_object->get();
} else {
return $search_object->paginate();
}
}
public static function getDuringCostByChannelIds($distribution_channels, $begin_time, $end_time)
{
$search_obj = self::where('is_enable', 1)
->where('pre_send_date', '>=', $begin_time)
->where('pre_send_date', '<=', $end_time)
->whereIn('distribution_channel_id', $distribution_channels);
return $search_obj->sum('cost');
}
/**
* 通过bid获取该书的推广次数
* @param $bid 书本id
* @param $channelId 渠道id
*/
static function getPromotionCountByBid($bid, $channelId)
{
return self::where('book_id', $bid)->where('distribution_channel_id', $channelId)->count();
}
/**
* 通过bid获取该书的推广次数
* @param $bid 书本id
* @param $channelId 渠道id
*/
static function getDuringPromotionCountByBid($bid, $begin_time, $end_time)
{
$search_obj = self::orderBy('id', 'desc')->where('is_enable', 1);
if ($bid) {
$search_obj->where('book_id', $bid);
}
if ($begin_time) {
$search_obj->where('send_time', '>=', $begin_time);
}
if ($end_time) {
$search_obj->where('send_time', '<=', $end_time);
}
return $search_obj->count();
}
/**
* 获取渠道下某时间段内推广次数
* @param $channelId 渠道id
* @param $begin_time 开始日期
* @param $end_time 结束日期
*/
static function getChannelPromotionCount($channelId, $begin_time, $end_time)
{
return self::where('created_at', '>=', $begin_time)->where('created_at', '<=', $end_time)->where('distribution_channel_id', $channelId)->count();
}
/**
* 获取渠道下某时间段内实际推广次数
* @param $channelId 渠道id
* @param $begin_time 开始日期
* @param $end_time 结束日期
*/
static function getChannelRealPromotionCount($channelId, $begin_time, $end_time)
{
return self::where('send_time', '>=', $begin_time)->where('send_time', '<=', $end_time)->where('distribution_channel_id', $channelId)->count();
}
/**
* 获取实际推广次数
* @param $params
*/
static function getRealPromotionCount($params)
{
$search_obj = self::orderBy('id', 'desc');
if (isset($params['begin_time'])) $search_obj->where('send_time', '>=', $params['begin_time']);
if (isset($params['end_time'])) $search_obj->where('send_time', '<=', $params['end_time']);
if (isset($params['distribution_channel_id'])) $search_obj->where('distribution_channel_id', $params['distribution_channel_id']);
return $search_obj->count();
}
/**
* 获取推广次数
* @param $params
*/
static function getPromotionCount($params)
{
$search_obj = self::orderBy('id', 'desc');
if (isset($params['begin_time'])) $search_obj->where('created_at', '>=', $params['begin_time']);
if (isset($params['end_time'])) $search_obj->where('created_at', '<=', $params['end_time']);
if (isset($params['distribution_channel_id'])) $search_obj->where('distribution_channel_id', $params['distribution_channel_id']);
if (isset($params['channels'])) $search_obj->whereIn('distribution_channel_id', $params['channels']);
if (isset($params['book_id'])) $search_obj->where('book_id', $params['book_id']);
return $search_obj->count();
}
/**
* 通过渠道id获取该渠道的推广次数
* @param $channelId 渠道id
*/
static function getPromotionCountByChannelId($channelId)
{
return self::where('distribution_channel_id', $channelId)->count();
}
/**
* 获取渠道id下所有的推广次数
* @param $channelId 渠道id
*/
static function getTotalPromotionCountByChannelIds($channelIds)
{
return self::whereIn('distribution_channel_id', $channelIds)->count();
}
/**
* 通过区间内的渠道id获取该渠道的推广次数
* @param $channelId 渠道id
* @param $start_time 开始时间
* @param $end_time 结束时间
*/
static function getDuraingPromotionCountByChannelId($distribution_channel_id, $start_time = '', $end_time = '')
{
$search_object = self::where('is_enable', 1);
if ($distribution_channel_id) {
$search_object->where('distribution_channel_id', $distribution_channel_id);
}
if ($start_time) {
$search_object->where('created_at', '>=', $start_time);
}
if ($end_time) {
$search_object->where('created_at', '<=', $end_time);
}
return $search_object->count();
}
/**
* 获取派单信息
* @param $bookId 推广书籍id
* @param $distribution_channel_id 推广渠道id
* @param $name 派单名称
* @param $bookName 书名
* @param $sendOrderId 派单id
* @param string $start_date 开始时间
* @param string $end_date 结束时间
* @param string $isAll 是否获取所有
* @return mixed
*/
static function getSendOrders($bookId, $distribution_channel_id, $name = null, $bookName = null, $sendOrderId, $start_time = '', $end_time = '', $isAll = false)
{
$search_object = self::orderBy('id', 'desc');
if ($bookId) {
$search_object->where('book_id', $bookId);
}
if ($distribution_channel_id) {
$search_object->where('distribution_channel_id', $distribution_channel_id);
}
if ($bookName) {
$search_object->where('book_name', $bookName);
}
if ($sendOrderId) {
$search_object->where('id', $sendOrderId);
}
if ($name) {
$search_object->where('name', 'like', '%' . $name . '%');
}
if ($start_time) {
$search_object->where('created_at', '>=', $start_time);
}
if ($end_time) {
$search_object->where('created_at', '<=', $end_time . ' 23:59:59');
}
$search_object->where('is_enable', 1);
if ($isAll) {
return $search_object->get();
} else {
return $search_object->paginate();
}
}
/**
* 获取派单信息
* @param $bookId 推广书籍id
* @param $distribution_channel_id 推广渠道id
* @param $name 派单名称
* @param $bookName 书名
* @param $sendOrderId 派单id
* @param string $start_date 开始时间
* @param string $end_date 结束时间
* @param string $isAll 是否获取所有
* @return mixed
*/
static function getManageSendOrders($params = [], $is_all = false)
{
$search_object = self::orderBy('send_orders.created_at', 'desc')->where('is_enable', 1)
->join('books', 'books.id', '=', 'send_orders.book_id')
->join('book_categories', 'book_categories.id', '=', 'books.category_id');
if (isset($params['id']) && $params['id']) $search_object->where('send_orders.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', 'like', '%' . $params['book_name'] . '%');
if (isset($params['start_time']) && $params['start_time']) $search_object->where('send_orders.created_at', '>=', $params['start_time']);
if (isset($params['end_time']) && $params['end_time']) $search_object->where('send_orders.created_at', '<=', $params['end_time'] . ' 23:59:59');
if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_object->where('send_orders.distribution_channel_id', $params['distribution_channel_id']);
if (isset($params['gender']) && $params['gender']) $search_object->where('book_categories.pid', '=', $params['gender']);
$search_object->select('send_orders.id', 'send_orders.name', 'send_orders.channel_type', 'send_orders.distribution_channel_id', 'send_orders.created_at', 'send_orders.updated_at', 'book_id', 'book_name', 'chapter_id', 'chapter_name', 'headline_id',
'body_template_id', 'document_cover_id', 'original_guide_id', 'subscribe_chapter_id', 'subscribe_chapter_name', 'qr_code_id',
'subscribe_chapter_seq', 'cost', 'sex_preference', 'is_enable', 'entrance', 'domain', 'redirect_url', 'send_time', 'charge_type', 'channel_name',
DB::raw('(select ifnull(count(*),0) from force_subscribe_users where send_order_id=send_orders.id and is_subscribed=1) as fansNum'),
DB::raw('(select ifnull(sum(price),0) from orders where send_order_id=send_orders.id and status="PAID" and pay_end_at BETWEEN send_orders.send_time and DATE_ADD(send_orders.send_time,interval 12 HOUR)) as half_day_charge_amount'),
DB::raw('(select ifnull(sum(price),0) from orders where send_order_id=send_orders.id and status="PAID" and pay_end_at BETWEEN send_orders.send_time and DATE_ADD(send_orders.send_time,interval 7 DAY)) as weekend_charge_amount'));
//判断内外部派单
/* if (isset($params['order_status']) && $params['order_status']) {
if (1 == $params['order_status']) {
// $search_object->whereExists('fansNum', '<', 20);
$search_object->whereExists(function ($query) {
$query->from('force_subscribe_users')
->whereRaw('send_order_id=send_orders.id and is_subscribed=1')->where(DB::raw('count(*)'), '<', 20);
});
} elseif (2 == $params['order_status']) {
// $search_object->having('fansNum', '>=', 20);
$search_object->whereExists(function ($query) {
$query->from('force_subscribe_users')
->whereRaw('send_order_id=send_orders.id and is_subscribed=1')->where(DB::raw('count(*)'), '>=', 20);
});
}
}*/
if (isset($params['orderBy']) && $params['orderBy']) {
$orderByType = 'desc';
$orderColum = 'send_orders.created_at'; //排序的列
if (isset($params['orderByType']) && $params['orderByType']) {
if (2 == $params['orderByType']) {
$orderByType = 'asc';
}
}
//12小时充值
if (1 == $params['orderBy']) {
$orderColum = 'half_day_charge_amount';
//7天充值充值
} elseif (2 == $params['orderBy']) {
$orderColum = 'weekend_charge_amount';
//充值总额
}
$search_object->orderBy($orderColum, $orderByType);
}
if ($is_all) {
return $search_object->get();
} else {
return $search_object->paginate();
}
}
/**
* 获取派单信息
* @param $bookId 推广书籍id
* @param $distribution_channel_id 推广渠道id
* @param $name 派单名称
* @param $bookName 书名
* @param $sendOrderId 派单id
* @param string $start_date 开始时间
* @param string $end_date 结束时间
* @param string $isAll 是否获取所有
* @return mixed
*/
static function search($params, $isAll = false)
{
$search_object = self::where('is_enable', 1)->orderBy('id', 'desc');
if (isset($params['book_id']) && $params['book_id']) {
$search_object->where('send_orders.book_id', $params['book_id']);
}
if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) {
$search_object->where('send_orders.distribution_channel_id', $params['distribution_channel_id']);
}
if (isset($params['book_name']) && $params['book_name']) {
$book_ids = BookConfig::getIdByName($params['book_name']);
if ($book_ids) {
$bids = [];
foreach ($book_ids as $item) {
$bids[] = $item->bid;
}
$search_object->whereIn('send_orders.book_id', $bids);
}
}
if (isset($params['import_company_name']) && $params['import_company_name']) {
$search_object->where('send_orders.import_company_name', 'like', '%' . $params['import_company_name'] . '%');
}
if (isset($params['name']) && $params['name']) {
$search_object->where('send_orders.name', 'like', '%' . $params['name'] . '%');
}
if (isset($params['start_time']) && $params['start_time']) {
$search_object->where('send_orders.created_at', '>=', $params['start_time']);
}
if (isset($params['end_time']) && $params['end_time']) {
$search_object->where('send_orders.created_at', '<=', $params['end_time']);
}
if (isset($params['send_time_start_time']) && $params['send_time_start_time']) {
$search_object->where('send_orders.send_time', '>=', $params['send_time_start_time']);
}
if (isset($params['send_time_end_time']) && $params['send_time_end_time']) {
$search_object->where('send_orders.send_time', '<=', $params['send_time_end_time']);
}
if (isset($params['pre_send_date_end']) && $params['pre_send_date_end']) {
$search_object->where('send_orders.pre_send_date', '<=', $params['pre_send_date_end']);
}
if (isset($params['pre_send_date_start']) && $params['pre_send_date_start']) {
$search_object->where('send_orders.pre_send_date', '>=', $params['pre_send_date_start']);
}
if (isset($params['start_send_time']) && $params['start_send_time']) {
$search_object->where('send_orders.send_time', '>=', $params['start_send_time']);
}
if (isset($params['end_send_time']) && $params['end_send_time']) {
$search_object->where('send_orders.send_time', '<=', $params['end_send_time']);
}
if (isset($params['promotion_type']) && $params['promotion_type']) {
$search_object->where('send_orders.promotion_type', $params['promotion_type']);
}
if (isset($params['id']) && $params['id']) {
$search_object->where('send_orders.id', $params['id']);
}
if (isset($params['promotion_point']) && $params['promotion_point']) {
$search_object->where('send_orders.promotion_point', $params['promotion_point']);
}
// \Log::info('my_sql:'.($search_object->toSql()));
if ($isAll) {
return $search_object->get();
} else {
return $search_object->paginate();
}
}
static function searchForDaily($params, $isAll = false)
{
$search_object = self::where('is_enable', 1);
$fields = ['send_orders.id', 'book_id', 'name', 'promotion_type', 'send_orders.created_at', 'remark', 'import_company_name', 'send_time', 'promotion_point', 'pre_send_date', 'book_name', 'distribution_channel_id', 'cost'];
$search_object->select($fields);
if (isset($params['distribution_channel_ids']) && $params['distribution_channel_ids']) {
$search_object->whereIn('distribution_channel_id', $params['distribution_channel_ids']);
}
if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) {
$search_object->where('distribution_channel_id', $params['distribution_channel_id']);
}
if (isset($params['start_time']) && $params['start_time']) {
$search_object->where('send_time', '>=', $params['start_time']);
}
if (isset($params['end_time']) && $params['end_time']) {
$search_object->where('send_time', '<=', $params['end_time']);
}
//按预计日期
if (isset($params['pre_date']) && $params['pre_date']) {
$search_object->where('pre_send_date', '=', $params['pre_date']);
}
if (isset($params['promotion_type']) && $params['promotion_type']) {
$search_object->where('promotion_type', $params['promotion_type']);
}
if (isset($params['is_single_site']) && $params['is_single_site']) {
$search_object->orderBy('pre_send_date');
} else {
$search_object->orderBy('distribution_channel_id');
}
if ($isAll) {
return $search_object->get();
} else {
return $search_object->paginate();
}
}
/**
* 更新派单的备注
* @param $id 派单id
* @param $distribution_channel_id 渠道id
* @param $remark 备注
* @return mixed
*/
static function updateRemark($id, $distribution_channel_id, $remark)
{
return self::where('id', $id)->where('distribution_channel_id', $distribution_channel_id)->update(['remark' => $remark]);
}
/**
* 更新派单的星数
* @param $id 派单id
* @param $distribution_channel_id 渠道id
* @param $starNum 星数
* @return mixed
*/
static function updateStarNum($id, $distribution_channel_id, $starNum)
{
return self::where('id', $id)->where('distribution_channel_id', $distribution_channel_id)->update(['star_num' => $starNum]);
}
/**
* 更新派单的星数、备注
* @param $id 派单id
* @param $distribution_channel_id 渠道id
* @param $starNum 星数
* @param $remark 备注
* @return mixed
*/
static function updateStarNumAndRemark($id, $distribution_channel_id, $starNum, $remark)
{
return self::where('id', $id)->where('distribution_channel_id', $distribution_channel_id)->update(['star_num' => $starNum, 'remark' => $remark]);
}
/**
* 更新派单
* @param $id 派单id
* @param $distribution_channel_id 渠道id
* @param $name 派单名称
* @param $cost 成本
* @param $channel_type 派单渠道类型.(允许值: AUTHENTICATED, UNAUTHENTICATED)
* @param $promotion_type 派单类型.(允许值: INTERNAL, EXTERNAL)
* @return mixed
*/
static function updateSendOrderInfo($id, $distribution_channel_id, $name, $pre_send_date, $channel_type, $cost, $promotion_type, $subscribe_chapter = [])
{
$object = self::where('id', $id)->where('distribution_channel_id', $distribution_channel_id);
$params = [];
if ($name) {
$params['name'] = $name;
}
if ($channel_type) {
$params['channel_type'] = $channel_type;
}
if ($cost) {
$params['cost'] = $cost;
}
if ($promotion_type) {
$params['promotion_type'] = $promotion_type;
}
if ($pre_send_date) {
$params['pre_send_date'] = $pre_send_date;
}
if ($subscribe_chapter) {
$params['subscribe_chapter_id'] = $subscribe_chapter['subscribe_chapter_id'];
$params['subscribe_chapter_seq'] = $subscribe_chapter['subscribe_chapter_seq'];
$params['subscribe_chapter_name'] = $subscribe_chapter['subscribe_chapter_name'];
}
return $object->update($params);
}
/**
* 创建推广派单（章节）
*/
static function createFromChapter($data)
{
return self::create($data);
}
/**
* 创建推广派单（页面）
*/
static function createFromPage($data)
{
return self::create($data);
}
/**
* 创建推广派单（目录）
*/
static function createFromDirectory($data)
{
return self::create($data);
}
/**
* 删除派单
* @param $id 派单id
* @param $distribution_channel_id 渠道id
* @return
*/
static function removeSendOrder($id, $distribution_channel_id)
{
return self::where('id', $id)->where('distribution_channel_id', $distribution_channel_id)->update(['is_enable' => 0]);
}
/**
* 设置成本
* @param $id 派单id
* @param $distribution_channel_id 渠道id
* @param $cost 成本
* @return mixed
*/
static function setSendOrderCost($id, $distribution_channel_id, $cost)
{
return self::where('id', $id)->where('distribution_channel_id', $distribution_channel_id)->update(['cost' => $cost]);
}
/**
* 获取派单下的首充用户数
* @param $id 派单id
*/
static function getFirstChargeUserNum($id)
{
$result = DB::select("SELECT COUNT(*) as num FROM ( SELECT (SELECT IFNULL(COUNT(*),0) as isss FROM orders WHERE uid=a.uid and `status` = 'PAID' and pay_end_at < a.end LIMIT 1) as temp FROM (SELECT uid,MIN(pay_end_at) as 'end' FROM orders WHERE status='PAID' and send_order_id ='{$id}' GROUP BY uid) a ) b WHERE temp = 0");
return ($result && isset($result[0]->num)) ? $result[0]->num : 0;
}
/**
* 获取派单下的非首充用户数
* @param $id 派单id
*/
static function getRepetitiousChargeUserNum($id)
{
$result = DB::select("SELECT COUNT(*) as num FROM (SELECT (SELECT IFNULL(COUNT(*),0) as isss FROM orders WHERE uid=a.uid and `status` = 'PAID' and pay_end_at < a.end LIMIT 1) as temp FROM (SELECT uid,MIN(pay_end_at) as 'end' FROM orders WHERE status='PAID' and send_order_id ='{$id}' GROUP BY uid) a ) b WHERE temp = 1");
return ($result && isset($result[0]->num)) ? $result[0]->num : 0;
}
/**
* 获取实际派单数
*/
static function getSendOrderCount($distribution_channel_id = '', $date)
{
$search_object = self::where('send_time', '>=', $date)->where('send_time', '<=', date('Y-m-d', strtotime($date) + 86400));
if ($distribution_channel_id) {
$search_object->where('distribution_channel_id', $distribution_channel_id);
}
return $search_object->count();
}
static function getBookdSendNum($start_date,$end_date)
{
return self::select(DB::raw('book_id,count(1) num'))->where('send_time','>=',$start_date)->where('send_time','<',$end_date)->groupBy('book_id')->get()->pluck('num','book_id');
}
public static function getSendOrdersByIds($ids)
{
$result = self::whereIn('id', $ids)->get();
return $result ? $result->toArray() : [];
}
}