zhuishuyun_quickapp/app/Modules/SendOrder/Models/SendOrder.php

<?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'
    ];

    /**
     * 通过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('created_at', '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();
        }
    }

    /**
     *  更新派单的备注
     * @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();
    }
}