amb_wechatapp/application/admin/controller/data/Goods.php

<?php

namespace app\admin\controller\data;

use app\common\controller\Backend;
use think\Db;
/**
 * 商品数据
 * @icon fa fa-circle-o
 */
class Goods extends Backend
{
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\data\Traffic();
        $this->timeModel = new \app\admin\model\data\Time();
    }


    /**
     * 商品访客排行
     * @return void
     */
    public function orderRank(){

        $time = $this->request->post('time/a',0);
        $unit = $this->request->post('unit','');
        if(!$time){
            $this->success('获取成功', []);
        }
        if(count($time) == 1){
            $time = $time[0];
        }
        $time = $this->timeModel->getFilterStartEndTime($time,$unit);

        $sql = "SELECT * FROM (
    SELECT
        goods_type,
        count(v.user_id) as user_count,
        sum(v.real_price) as price_sum,
        CASE
            WHEN goods_type = 'course' THEN (
                SELECT CONCAT(id, '||', name, '||', cover)
                FROM ".\app\admin\model\course\Course::getTable()." as childtable
                WHERE childtable.id = v.item_id
                  AND (childtable.type = 'audio' OR childtable.type = 'video' OR childtable.type = 'live' OR childtable.type = 'article' OR childtable.type = 'column')
            )
            WHEN goods_type = 'vipcard' THEN (
                SELECT CONCAT(id, '||', title)
                FROM ".\app\admin\model\app\vip\Card::getTable()." as childtable
                WHERE childtable.id = v.item_id
            )
            WHEN goods_type = 'exercise' THEN (
                SELECT CONCAT(id, '||', name, '||', cover)
                FROM ".\app\admin\model\app\exam\Exercises::getTable()." as childtable
                WHERE childtable.id = v.item_id
            )
            WHEN goods_type = 'activity' THEN (
                SELECT CONCAT(id, '||', name, '||', cover)
                FROM ".\app\admin\model\app\activity\Activity::getTable()." as childtable
                WHERE childtable.id = v.item_id
            )
        END AS product_name
    FROM
        ".\app\admin\model\order\Item::getTable()." AS v
    RIGHT JOIN ".\app\admin\model\order\Order::getTable()." as orders ON v.order_no = orders.order_no
    WHERE  v.createtime BETWEEN {$time['start']} AND {$time['end']} and orders.status in ('".implode('\',\'',\app\common\constant\order\Status::getSuccessOrderStatus())."')
    GROUP BY v.goods_type, v.item_id,v.user_id
) AS subquery
WHERE subquery.product_name IS NOT NULL
ORDER BY subquery.user_count DESC
LIMIT 0, 5;";


        $data = $this->parseSqlQuery($sql,$time);

        $this->success("获取成功",$data);
    }

    /**
     * 商品访客排行
     * @return void
     */
    public function visiterRank(){

        $time = $this->request->post('time/a',0);
        $unit = $this->request->post('unit','');
        if(!$time){
            $this->success('获取成功', []);
        }
        if(count($time) == 1){
            $time = $time[0];
        }
        $time = $this->timeModel->getFilterStartEndTime($time,$unit);

        $sql = "SELECT * FROM (
    SELECT
        page_type,
        page_id,
        CASE
            WHEN page_type = 'course' THEN (
                SELECT CONCAT(id, '||', name, '||', cover)
                FROM ".\app\admin\model\course\Course::getTable()." as childtable
                WHERE childtable.id = v.page_id
                  AND (childtable.type = 'audio' OR childtable.type = 'video' OR childtable.type = 'live' OR childtable.type = 'article' OR childtable.type = 'column')
            )
            WHEN page_type = 'vipcard' THEN (
                SELECT CONCAT(id, '||', title)
                FROM ".\app\admin\model\app\vip\Card::getTable()." as childtable
                WHERE childtable.id = v.page_id
            )
            WHEN page_type = 'exercise' THEN (
                SELECT CONCAT(id, '||', name, '||', cover)
                FROM ".\app\admin\model\app\exam\Exercises::getTable()." as childtable
                WHERE childtable.id = v.page_id
            )
            WHEN page_type = 'activity' THEN (
                SELECT CONCAT(id, '||', name, '||', cover)
                FROM ".\app\admin\model\app\activity\Activity::getTable()." as childtable
                WHERE childtable.id = v.page_id
            )
        END AS product_name,
        COUNT(DISTINCT ip) AS visitor_count
    FROM
        ".$this->model->getTable()." AS v
    WHERE page_type <> 'other' AND createtime BETWEEN {$time['start']} AND {$time['end']}
    GROUP BY page_type, page_id
) AS subquery
WHERE subquery.product_name IS NOT NULL
ORDER BY subquery.visitor_count DESC
LIMIT 0, 5;";

// 执行查询


        $data = $this->parseSqlQuery($sql,$time);



        $this->success("获取成功",$data);

    }

    public function parseSqlQuery($sql,$time){
        $data = [];
        $result = Db::query($sql);
        if($result){


            $orderModel = new \app\admin\model\data\Order;

            foreach ($result as $item){
                $temp = explode("||",$item['product_name']);
                $item['name'] = $temp[1];
                $item['id'] = $temp[0];
                if((isset($item['page_type']) && $item['page_type'] == 'vipcard') || (isset($item['goods_type']) && $item['goods_type'] == 'vipcard')){
                    if(\app\common\library\Platform::getSystemType() == 'single'){
                        $item['cover'] = MODULE_URL.'/assets/image/vipcard.png';
                    }else{
                        $item['cover'] = MODULE_URL.'/public/assets/image/vipcard.png';
                    }
                }else{
                    $item['cover'] = $temp[2];
                }

                $payerCount = $orderModel->getTimeRangePayUserCount($time['start'],$time['end']);

                if(isset($item['visitor_count'])){
                    $item['pay_rate'] = $item['visitor_count'] && $payerCount ? intval($payerCount / $item['visitor_count'] * 100) ."%" : '0%';
                }

                $data[] = $item;
            }
        }

        return $data;
    }

    /**
     * 商品统计
     * @return void
     */
    public function total(){

        $dataFields = [
            'course'=>[
                'column','video','audio','article','live','goods'
            ],
            'interaction'=>[
                'vipcard','activity','exercise'
            ]
        ];

        $query =\app\common\model\goods\Handle::getGoodsQuery();

        $fields = [];
        foreach ($dataFields as $item){
            foreach ($item as $option){
                $fields[] = "COUNT(IF(type='{$option}', 1, NULL)) as {$option}_count";
            }
        }
        $fields[] = "COUNT(*) as total_count";

        $data = [];
        $data['goods']['total'] = $query->where([
            'status'=>1
        ])->count();
        $query =\app\common\model\goods\Handle::getGoodsQuery();
        $data['goods']['total_pay'] = $query->where([
            'pay_type'=>'pay',
            'status'=>1
        ])->count();

        $data['goods']['total_free'] = $data['goods']['total'] - $data['goods']['total_pay'];

        $query =\app\common\model\goods\Handle::getGoodsQuery();
        $result = $query->where([
            'status'=>1
        ])->field($fields)->find();


        foreach ($dataFields as $key =>$item){
            $data[$key] = [];
            foreach ($item as $option){
                $data[$key][$option] = $result[$option."_count"];
            }
        }
        $this->success("获取成功",$data);

    }
}