wangdu_public_api/app/Modules/SendOrder/Models/SendOrderForceDayStat.php

<?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();
    }
}