zhuishuyun_quickapp/app/Modules/Activity/Services/ActivityService.php

<?php

namespace App\Modules\Activity\Services;

use App\Modules\Activity\Models\Activity;
use App\Modules\Statistic\Services\WapVisitStatService;
use App\Modules\Subscribe\Models\Order;
use App\Modules\Trade\Services\OrderService;
use DB;
use Redis;

class ActivityService
{
    /**
     * 查询活动信息
     * @param array $params
     * @param bool $is_all
     * @return mixed
     */
    static function search($params = [], $is_all = false)
    {
        return Activity::search($params, $is_all);
    }

    /**
     * 查询活动id
     * @param array $params
     * @param bool $is_all
     * @return mixed
     */
    static function getActivityIds($params = [])
    {
        return Activity::getActivityIds($params);
    }


    /**
     * 查询活动的的充值信息
     * @param array $params
     * @param bool $is_all
     * @return mixed
     */
    static function getActivityRechargeInfo($params = [], $is_all = false)
    {
        return OrderService::search($params, $is_all);
    }

    /**
     * 更新活动控制开关
     * @param $activity_id
     * @param $params
     * @return mixed
     */
    static function updateShowSwitch($activity_id, $params)
    {
        return Activity::updateShowSwitch($activity_id, $params);
    }

    /**
     * 根据id获取
     * @param $id
     * @return mixed
     */
    static function getById($id)
    {
        return Activity::find($id);
    }

    /**
     * 根据token获取
     * @param $id
     * @return mixed
     */
    static function getByToken($token)
    {
        return Activity::where('token', $token)->first();
    }

    /**
     * 获取当前渠道的9.9元活动
     * @param $chanel_id
     * @return mixed
     */
    static function getActiveCustomActivity($chanel_id)
    {
        return Activity::where('distribution_channel_id', $chanel_id)
            ->where('start_time', '<=', date('Y-m-d H:i:s'))
            ->where('end_time', '>', date('Y-m-d H:i:s'))
            ->where('product_id', 1252)
            ->select('activity_page')
            ->first();
    }

    static function getActiveCustomActivityByType($chanel_id,$type='DEFAULT'){
        return Activity::where('distribution_channel_id', $chanel_id)
            ->where('start_time', '<=', date('Y-m-d H:i:s'))
            ->where('end_time', '>', date('Y-m-d H:i:s'))
            ->where('type', $type)
            ->select('activity_page')
            ->first();
    }

    /**
     * 活动统计
     * @param $activity_id
     * @param $distribution_channel_id
     * @return array
     */
    static function getActivityUvPv($activity_id, $distribution_channel_id)
    {
        $activity = self::getById($activity_id);

        $uv_key_format = 'activity:%s:distribution_channel_id:%s:date:%s:uv';
        $pv_key_format = 'activity:%s:distribution_channel_id:%s:pv';

        $start = strtotime($activity->start_time);
        $end_time = strtotime($activity->end_time);
        $end = time() > $end_time ? $end_time : time();
        $page_pv = 0;
        $page_uv = 0;
        $visit_info = WapVisitStatService::getActivityUvAndPv($distribution_channel_id, $activity_id);
        $page_pv = $visit_info['pv'];
        $page_uv = $visit_info['uv'];
        /*while ($start<$end){
            $temp_day = date('Y-m-d',$start);
            $uv_key = sprintf($uv_key_format, $activity_id, $distribution_channel_id,$temp_day);
            $puv = Redis::scard($uv_key);
            if($puv) $page_uv += $puv;
            $start += 86400;
        }
        $pv_key = sprintf($pv_key_format, $activity_id, $distribution_channel_id);
        $pv_all = Redis::hgetAll($pv_key);
        if($pv_all){
            foreach ($pv_all as $v){
                $page_pv += $v;
            }
        }*/
        $button_pv = Order::where('activity_id', $activity_id)->where('distribution_channel_id', $distribution_channel_id)->count();

        $button_uv = DB::select('select ifnull(count(distinct uid),0) as count from orders where activity_id=' . $activity_id . ' and distribution_channel_id=' . $distribution_channel_id);
        $button_uv = $button_uv[0]->count;
        $order_num = Order::where('activity_id', $activity_id)->where('status', 'PAID')->where('distribution_channel_id', $distribution_channel_id)->count();
        $order_sum = Order::where('activity_id', $activity_id)->where('status', 'PAID')->where('distribution_channel_id', $distribution_channel_id)->sum('price');
        $sql = 'select ifnull(count(*),0) as count from orders a where activity_id=' . $activity_id . ' and status="PAID" and distribution_channel_id= ' . $distribution_channel_id . ' and EXISTS (select uid from orders b where created_at <="' . $activity->start_time . '" and b.uid=a.uid)';
        $re_order_info = DB::select($sql);
        $re_order = $re_order_info[0]->count;
        return compact('page_pv', 'page_uv', 'button_pv', 'button_uv', 'order_num', 're_order', 'order_sum');
    }

    static function ActivityStats($activity_id)
    {
        $sql = "select a.*,b.price,b.order_num as success_order  from(
select date(created_at) as `date`,distribution_channel_id,count(*) as order_num,count(DISTINCT  uid) as uv
 from orders 
where activity_id={$activity_id} GROUP by date(created_at),distribution_channel_id
) a
left join (
select date(created_at) as `date`,distribution_channel_id,sum(price) as price,count(*) as order_num
 from orders 
where activity_id={$activity_id} and `status`='PAID' GROUP by date(created_at),distribution_channel_id
) b on a.`date`=b.`date` and a.distribution_channel_id=b.distribution_channel_id";

        $res = DB::select($sql);
        $activity = self::getById($activity_id);
        $data = [];
        $uv_key_format = 'activity:%s:distribution_channel_id:%s:date:%s:uv';
        foreach ($res as $v) {
            $temp = [];
            $temp['date'] = $v->date;
            $temp['distribution_channel_id'] = $v->distribution_channel_id;
            $temp['button_pv'] = $v->order_num;
            $temp['order_num'] = $v->success_order ? $v->success_order : 0;
            $sql1 = 'select ifnull(count(*),0) as count from orders a where activity_id=' . $activity_id . ' and status="PAID" and date(created_at)="' . $v->date . '" and distribution_channel_id= ' . $v->distribution_channel_id . ' and not EXISTS (select uid from orders b where created_at <="' . $activity->start_time . '" and b.uid=a.uid)';
            $sql2 = 'select ifnull(count(*),0) as count from orders a where activity_id=' . $activity_id . ' and status="PAID" and from_type="reader" and date(created_at)="' . $v->date . '" and distribution_channel_id= ' . $v->distribution_channel_id . ' and not EXISTS (select uid from orders b where created_at <="' . $activity->start_time . '" and b.uid=a.uid)';
            $re_order_info = DB::select($sql1);
            $re_order_info2 = DB::select($sql2);
            $temp['first_charge'] = $re_order_info[0]->count ? $re_order_info[0]->count : 0;
            $temp['first_charge_reader'] = $re_order_info2[0]->count ? $re_order_info2[0]->count : 0;
            $temp['button_uv'] = $v->uv;
            $uv_key = sprintf($uv_key_format, $activity_id, $v->distribution_channel_id, $v->date);
            $puv = Redis::scard($uv_key);
            $puv = $puv ? $puv : 0;
            $temp['page_uv'] = $puv;
            $from = 'reader';
            $reader_uv = Redis::scard('push:distribution_channel_id:' . $v->distribution_channel_id . 'from:' . $from . ':date:' . $v->date);
            $temp['reader_uv'] = $reader_uv ? $reader_uv : 0;
            $data[] = $temp;
        }

        $sql3 = "select a.*,b.price,b.order_num as success_order  from(
select date(created_at) as `date`,distribution_channel_id,count(*) as order_num,count(DISTINCT  uid) as uv
 from orders 
where activity_id=$activity_id and from_type='reader' GROUP by date(created_at),distribution_channel_id
) a
left join (
select date(created_at) as `date`,distribution_channel_id,sum(price) as price,count(*) as order_num
 from orders 
where activity_id=$activity_id and `status`='PAID' and from_type='reader' GROUP by date(created_at),distribution_channel_id
) b on a.`date`=b.`date` and a.distribution_channel_id=b.distribution_channel_id";
        $res2 = DB::select($sql3);
        foreach ($data as &$v) {
            $v['reader_order_num'] = 0;
            $v['reader_order_success_num'] = 0;
            foreach ($res2 as $val) {
                if ($v['date'] == $val->date && $val->distribution_channel_id == $v['distribution_channel_id']) {
                    $v['reader_order_num'] = $val->order_num ? $val->order_num : 0;
                    $v['reader_order_success_num'] = $val->success_order ? $val->success_order : 0;
                }
            }
        }
        return $data;
    }

    public static function createActivity(array $param)
    {
        return Activity::create($param);
    }

    public static function getActivitySetting()
    {
        $activity_settng_key = 'activity:setting';
        $res = Redis::get($activity_settng_key);
        if ($res) {
            return json_decode($res, 1);
        }
        return null;
    }

    static function ActivityStatsI(int $activity_id): array
    {
        $activity_info = self::getById($activity_id);
        $sql_format = "select
distribution_channel_id,date(created_at) as `day`,COUNT(*) as order_num,
count(case when `status`='PAID' then `status` else null end) as success,
GROUP_CONCAT(case when `status`='PAID' and `from_type`='reader' then uid else null end) as reader_uids,
GROUP_CONCAT(case when `status`='PAID' and `from_type`='signcallback' then uid else null end) as signcallback_uids,
sum(case when `status`='PAID' then `price` else null end) as sums,
count(case when `from_type`='reader' then  `uid` else null end) as reader_order,
count(DISTINCT uid) as button_uv,
count(case when `from_type`='reader' and `status`='PAID' then  `uid` else null end) as reader_success_order,
count(case when `from_type`='signcallback' then  `uid` else null end) as signcallback_order,
count(case when `from_type`='signcallback' and `status`='PAID' then  `uid` else null end) as signcallback_success_order
from orders where activity_id=%s 
and created_at BETWEEN '%s' and '%s' 
GROUP by distribution_channel_id,date(created_at)";
        $sql = sprintf($sql_format, $activity_id, $activity_info->start_time, $activity_info->end_time);
        $res = DB::select($sql);
        $data = [];
        foreach ($res as $v) {
            $temp['day'] = $v->day;
            $temp['siteid'] = $v->distribution_channel_id;
            $temp['button_uv'] = $v->button_uv;
            $temp['order_num'] = $v->order_num;
            $temp['success_order_num'] = $v->success;
            $temp['reader_order'] = $v->reader_order;
            $temp['reader_success_order'] = $v->reader_success_order;
            $temp['signcallback_order'] = $v->signcallback_order;
            $temp['signcallback_success_order'] = $v->signcallback_success_order;
            $sql1 = 'select ifnull(count(DISTINCT uid),0) as `count` from orders a where activity_id=' . $activity_id . ' and status="PAID" and date(created_at)="' . $v->day . '" and distribution_channel_id= ' . $v->distribution_channel_id . ' and not EXISTS (select uid from orders b where created_at <="' . $activity_info->start_time . '" and b.uid=a.uid and b.status="PAID")';
            $res1 = DB::select($sql1);
            $temp['first_charge_num'] = $res1[0]->count;
            $reader_uv_pv_info = WapVisitStatService::getSitePvAndUvOneDay($v->distribution_channel_id, 'reader', $v->day);
            $signcallback_uv_pv_info = WapVisitStatService::getSitePvAndUvOneDay($v->distribution_channel_id, 'signcallback', $v->day);
            $pv_uv_info = WapVisitStatService::getActivityUvAndPvByDay($v->distribution_channel_id, $activity_id, $v->day);
            $temp['uv'] = $pv_uv_info['uv'];
            $temp['reader_uv'] = $reader_uv_pv_info['uv'];
            $temp['signcallback_uv'] = $signcallback_uv_pv_info['uv'];
            $sq2 = "select count(distinct uid) as `count` from orders WHERE  uid in (%s) and created_at<='%s' ";
            if ($v->reader_uids) {
                $v->reader_uids = rtrim($v->reader_uids, ',');
                $temp_reader_second = DB::select(sprintf($sq2, $v->reader_uids, $activity_info->start_time));
                $temp['reader_first_charge'] = $v->reader_success_order - (int)$temp_reader_second[0]->count;
            } else {
                $temp['reader_first_charge'] = 0;
            }

            if ($v->signcallback_uids) {
                $v->signcallback_uids = rtrim($v->signcallback_uids, ',');
                $temp_reader_second = DB::select(sprintf($sq2, $v->signcallback_uids, $activity_info->start_time));
                $temp['signcallback_first_charge'] = $v->signcallback_success_order - (int)$temp_reader_second[0]->count;
            } else {
                $temp['signcallback_first_charge'] = 0;
            }
            $data[] = $temp;
        }
        return $data;
    }

    static function yearActivityStats(int $activity_id): array
    {
        $activity_info = self::getById($activity_id);
        $sql_format = "select
distribution_channel_id,date(created_at) as `day`,COUNT(*) as order_num,
count(case when `status`='PAID' then `status` else null end) as success,
GROUP_CONCAT(case when `status`='PAID' then uid else null end) as uids,
sum(case when `status`='PAID' then `price` else null end) as sums,
price from orders where activity_id=%s 
and 
created_at BETWEEN '%s' and '%s' 
GROUP by distribution_channel_id,date(created_at),price";
        $sql = sprintf($sql_format, $activity_id, $activity_info->start_time, $activity_info->end_time);
        $res = DB::select($sql);
        $data = [];
        foreach ($res as $v) {
            $temp['siteid'] = $v->distribution_channel_id;
            $temp['day'] = $v->day;
            $temp['price'] = $v->price;
            $temp['order_num'] = $v->order_num;
            $temp['success'] = $v->success;
            $temp['uv'] = 0;
            $second_sql_fromat = "SELECT COUNT(DISTINCT uid) as counts FROM orders WHERE uid in (%s) and `status` = 'PAID' and created_at<='%s'";
            if ($v->uids) {
                $v->uids = rtrim($v->uids, ',');
                $second_sql = sprintf($second_sql_fromat, $v->uids, $activity_info->start_time);
                $temp_res = DB::select($second_sql);
                $temp['first_charge'] = $v->success - (int)$temp_res[0]->counts;
            } else {
                $temp['first_charge'] = $v->success;
            }

            $pv_uv_info = WapVisitStatService::getActivityUvAndPvByDay($v->distribution_channel_id, $activity_id, $v->day);

            $temp['sums'] = $v->sums;
            $temp['uv'] = $pv_uv_info['uv'];
            $data[] = $temp;
            $temp = null;
        }
        return $data;
    }

    public static function getValidActivities()
    {
        return Activity::getValidActivities();
    }
}