CREMB Min Thinkphp 数据的统计查询

查询所有定制产品ID 的订单 和 统计总数以及计算运费总和

附加条件

• 已付款
• 当前订单

1) 复制StoreOrderCartInfo.php 为 StoreOrderCartProductInfo.php 新建两个关联模型。orderInfo

和 productInfo

<?php
// +----------------------------------------------------------------------
// | CRMEB [ CRMEB赋能开发者，助力企业发展 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2016~2020 https://www.crmeb.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed CRMEB并不是自由软件，未经许可不能去掉CRMEB相关版权
// +----------------------------------------------------------------------
// | Author: CRMEB Team <admin@crmeb.com>
// +----------------------------------------------------------------------
// zhouyong 修改2021/07/20

namespace app\model\order;

use crmeb\basic\BaseModel;
use crmeb\traits\ModelTrait;
use think\Model;
use app\model\order\StoreOrder;
use app\model\product\product\StoreProduct;

/**
 * TODO 订单记录Model
 * Class StoreOrderCartInfo
 * @package app\model\order
 */
class StoreOrderCartProductInfo extends BaseModel
{
    use ModelTrait;

    /**
     * 模型名称
     * @var string
     */
    protected $name = 'store_order_cart_info';

    /**
     * 购物车信息获取器
     * @param $value
     * @return array|mixed
     */
    public function getCartInfoAttr($value)
    {
        return json_decode($value, true) ?? [];
    }

    /**
     * 订单ID搜索器
     * @param Model $query
     * @param $value
     * @param $data
     */
    public function searchOidAttr($query, $value, $data)
    {
        $query->where('oid', $value);
    }

    /**
     * 购物车ID搜索器
     * @param Model $query
     * @param $value
     * @param $data
     */
    public function searchCartIdAttr($query, $value, $data)
    {
        if (is_array($value)) {
            $query->whereIn('cart_id', $value);
        } else {
            $query->where('cart_id', $value);
        }
    }

    public function orderInfo() {
        return $this->hasOne(StoreOrder::class, 'id', 'oid');
    }

    public function productInfo() {
        return $this->hasOne(StoreProduct::class, 'id', 'product_id');
    }
}

2）查询附近查询条件 StoreOrderCartProductInfoDao.php

<?php
// +----------------------------------------------------------------------
// | CRMEB [ CRMEB赋能开发者，助力企业发展 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2016~2020 https://www.crmeb.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed CRMEB并不是自由软件，未经许可不能去掉CRMEB相关版权
// +----------------------------------------------------------------------
// | Author: CRMEB Team <admin@crmeb.com>
// +----------------------------------------------------------------------
// zhouyong 2021/07/20

namespace app\dao\order;


use app\dao\BaseDao;
use app\model\order\StoreOrderCartProductInfo;

/**
 * 订单详情
 * Class StoreOrderCartInfoDao
 * @package app\dao\order
 */
class StoreOrderCartProductInfoDao extends BaseDao
{
    /**
     * 设置模型
     * @return string
     */
    protected function setModel(): string
    {
        return StoreOrderCartProductInfo::class;
    }

    /**
     * 获取购物车详情列表
     * @param array $where
     * @param array $field
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function getCartInfoList(array $where, array $field)
    {
        return $this->search($where)->field($field)->limit(10)->select()->toArray();
    }

    /**
     * 获取购物车信息以数组返回
     * @param array $where
     * @param string $field
     * @param string $key
     */
    public function getCartColunm(array $where, string $field, string $key)
    {
        return $this->search($where)->column($field, $key);
    }

    public function getCartProductListLength($ids, $dateSecs)
    {
        $time_start = $dateSecs;
        $time_end = $time_start + 24 * 60 * 60;
        // 闭包中使用外部变量，需要注意的点
        return $this->getModel()->hasWhere('orderInfo', function($query) use ($time_start, $time_end){
            $query->where([
                ['paid','=', 1],
                ['add_time', 'between', [$time_start, $time_end]]
        ]);
        })->with(['productInfo','orderInfo'])->where('product_id', 'in', $ids)->count();
    }

    public function getCartProductMoney($ids, $dateSecs)
    {
        $time_start = $dateSecs;
        $time_end = $time_start + 24 * 60 * 60;
        // 闭包中使用外部变量，需要注意的点
        return $this->getModel()->hasWhere('orderInfo', function($query) use ($time_start, $time_end){
            $query->where([
                ['paid','=', 1],
                ['add_time', 'between', [$time_start, $time_end]]
        ]);
        })->with(['productInfo','orderInfo'])->where('product_id', 'in', $ids)->sum('pay_price');
    }

    public function getCartInfoList2($ids, $date, $page, $limit)
    {
        /*
        * 这个吊东西查起来太复杂了吧，卧槽
        */
        if(empty($date)) {
            $date = date('Y-m-d',time());
        }
        $time_start = strtotime($date);
        $time_end = $time_start + 24 * 60 * 60;
        // 闭包中使用外部变量，需要注意的点
        return $this->getModel()->hasWhere('orderInfo', function($query) use ($time_start, $time_end){
            $query->where([
                ['paid','=', 1],
                ['add_time', 'between', [$time_start, $time_end]]
        ]);
        })->with(['productInfo','orderInfo'])->where('product_id', 'in', $ids)->page($page, $limit)->select()->toArray();
    }
}

3）StoreOrderCartProductInfoServices.php

<?php
// +----------------------------------------------------------------------
// | CRMEB [ CRMEB赋能开发者，助力企业发展 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2016~2020 https://www.crmeb.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed CRMEB并不是自由软件，未经许可不能去掉CRMEB相关版权
// +----------------------------------------------------------------------
// | Author: CRMEB Team <admin@crmeb.com>
// +----------------------------------------------------------------------
// zhouyong 2021/07/20

namespace app\services\order;

use crmeb\utils\Str;
use app\services\BaseServices;
use crmeb\services\CacheService;
use app\dao\order\StoreOrderCartProductInfoDao;

/**
 * Class StoreOrderCartInfoServices
 * @package app\services\order
 * @method array getCartColunm(array $where, string $field, ?string $key) 获取购物车信息以数组返回
 * @method array getCartInfoList(array $where, array $field) 获取购物车详情列表
 * @method getOne(array $where, ?string $field = '*', array $with = []) 根据条件获取一条数据
 */
class StoreOrderCartProductInfoServices extends BaseServices
{
    /**
     * StorePinkServices constructor.
     * @param StorePinkDao $dao
     */
    public function __construct(StoreOrderCartProductInfoDao $dao)
    {
        $this->dao = $dao;
    }

    /**
     * 获取指定订单下的商品详情
     * @param int $oid
     * @return array|mixed
     */
    public function getOrderCartInfo(int $oid)
    {
        $cartInfo = CacheService::get(md5('store_order_cart_info_' . $oid));
        if ($cartInfo) return $cartInfo;
        $cart_info = $this->dao->getColumn(['oid' => $oid], 'cart_info', 'cart_id');
        $info = [];
        foreach ($cart_info as $k => $v) {
            $_info = is_string($v) ? json_decode($v, true) : $v;
            if (!isset($_info['productInfo'])) $_info['productInfo'] = [];
            $info[$k]['cart_info'] = $_info;
            unset($_info);
        }
        CacheService::set(md5('store_order_cart_info_' . $oid), $info);
        return $info;
//        return CacheService::get(md5('store_order_cart_info_' . $oid), function () use ($oid) {
//            $cart_info = $this->dao->getColumn(['oid' => $oid], 'cart_info', 'cart_id');
//            $info = [];
//            foreach ($cart_info as $k => $v) {
//                $_info = is_string($v) ? json_decode($v, true) : $v;
//                if (!isset($_info['productInfo'])) $_info['productInfo'] = [];
//                $info[$k]['cart_info'] = $_info;
//                unset($_info);
//            }
//            return $info;
//        }) ?: [];
    }

    /**
     * 查找购物车里的所有商品标题
     * @param $cartId
     * @return bool|string
     */
    public function getCarIdByProductTitle($cartId, $goodsNum = false)
    {
        $title = '';
        $orderCart = $this->dao->getCartInfoList(['cart_id' => $cartId], ['cart_info']);
        foreach ($orderCart as $item) {
            if (isset($item['cart_info']['productInfo']['store_name'])) {
                if ($goodsNum && isset($item['cart_info']['cart_num'])) {
                    $title .= $item['cart_info']['productInfo']['store_name'] . ' * '.$item['cart_info']['cart_num'].' | ';
                }else{
                    $title .= $item['cart_info']['productInfo']['store_name'] . '|';
                }

            }
        }
        if ($title) {
            $title = substr($title, 0, strlen($title) - 1);
        }
        return $title;
    }

    /**
     * 获取打印订单的商品信息
     * @param array $cartId
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function getCartInfoPrintProduct(array $cartId)
    {
        $cartInfo = $this->dao->getCartInfoList(['cart_id' => $cartId], ['cart_info']);
        $product = [];
        foreach ($cartInfo as $item) {
            $value = is_string($item['cart_info']) ? json_decode($item['cart_info'], true) : $item['cart_info'];
            $value['productInfo']['store_name'] = $value['productInfo']['store_name'] ?? "";
            $value['productInfo']['store_name'] = Str::substrUTf8($value['productInfo']['store_name'], 10, 'UTF-8', '');
            $product[] = $value;
        }
        return $product;
    }


    /**
     * 保存购物车info
     * @param $oid
     * @param array $cartInfo
     * @return int
     */
    public function setCartInfo($oid, array $cartInfo)
    {
        $group = [];
        foreach ($cartInfo as $cart) {
            $group[] = [
                'oid' => $oid,
                'cart_id' => $cart['id'],
                'product_id' => $cart['productInfo']['id'],
                'cart_info' => json_encode($cart),
                'unique' => md5($cart['id'] . '' . $oid)
            ];
        }
        return $this->dao->saveAll($group);
    }

    /**
     * 商品编号
     * @param $cartId
     * @return array
     */
    public function getCartIdsProduct($cartId)
    {
        return $this->dao->getColumn([['cart_id', 'in', $cartId]], 'product_id', 'oid');
    }

    public function getCartProductListLength($ids, $dateSecs)
    {
        return $this->dao->getCartProductListLength($ids, $dateSecs);
    }

    public function getCartProductMoney($ids, $dateSecs)
    {
        return $this->dao->getCartProductMoney($ids, $dateSecs);
    }

    public function getCartProductList($ids, $date, $page, $limit) {
        return $this->dao->getCartInfoList2($ids, $date, $page, $limit);
    }
}

4)  StoreOrderExt.php

<?php
// +----------------------------------------------------------------------
// | CRMEB [ CRMEB赋能开发者，助力企业发展 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2016~2020 https://www.crmeb.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed CRMEB并不是自由软件，未经许可不能去掉CRMEB相关版权
// +----------------------------------------------------------------------
// | Author: CRMEB Team <admin@crmeb.com>
// +----------------------------------------------------------------------
namespace app\adminapi\controller\v1\order;


use app\adminapi\controller\AuthController;
use app\adminapi\validate\order\StoreOrderValidate;
use app\services\serve\ServeServices;
use app\services\order\{
    StoreOrderDeliveryServices,
    StoreOrderRefundServices,
    StoreOrderStatusServices,
    StoreOrderTakeServices,
    StoreOrderWriteOffServices,
    StoreOrderServices,
    StoreOrderCartProductInfoServices
};
use app\services\redeemcode\DistributerUserServices;
use app\services\pay\OrderOfflineServices;
use app\services\shipping\ExpressServices;
use app\services\user\UserServices;
use think\facade\App;

/**
 * 订单管理
 * Class StoreOrder
 * @package app\adminapi\controller\v1\order
 */
class StoreOrderExt extends AuthController
{
   
    public function __construct(App $app, StoreOrderServices $service)
    {
        parent::__construct($app);
        $this->services = $service;
    }

    protected function initialize()
    {
        // TODO: Implement initialize() method.
    }

    
    /**
     * 获取订单列表
     * @return mixed
     */
    public function lst()
    {
        $where = $this->request->getMore([
            ['status', ''],
            ['real_name', ''],
            ['is_del', ''],
            ['data', '', '', 'time'],
            ['type', ''],
            ['pay_type', ''],
            ['order', ''],
            ['field_key', ''],
        ]);
        $where['shipping_type'] = 1;
        $where['is_system_del'] = 0;
        return app('json')->success($this->services->getOrderList($where, ['*'], ['invoice']));
    }
    
    /**
     * 修改配送信息
     * @param $id  订单id
     * @return mixed
     */
    public function update_distribution(StoreOrderDeliveryServices $services, $id)
    {
        $data = $this->request->postMore([['delivery_name', ''], ['delivery_id', '']]);
        if (!$id) return app('json')->fail('Data does not exist!');
        $services->updateDistribution($id, $data);
        return app('json')->success('Modified success');
    }
    
    
    /**
     * 订单发送货
     * @param $id 订单id
     * @return mixed
     */
    public function update_delivery($id, StoreOrderDeliveryServices $services)
    {
        $data = $this->request->postMore([
            ['type', 1],
            ['delivery_name', ''],//快递公司名称
            ['delivery_id', ''],//快递单号
            ['delivery_code', ''],//快递公司编码

            ['express_record_type', 2],//发货记录类型
            ['express_temp_id', ""],//电子面单模板
            ['to_name', ''],//寄件人姓名
            ['to_tel', ''],//寄件人电话
            ['to_addr', ''],//寄件人地址

            ['sh_delivery_name', ''],//送货人姓名
            ['sh_delivery_id', ''],//送货人电话
            ['sh_delivery_uid', ''],//送货人ID

            ['fictitious_content', '']//虚拟发货内容
        ]);
        $services->delivery((int)$id, $data);
        return app('json')->success('SUCCESS');
    }

    public function distributor_login(DistributerUserServices $services) {
        //return $services->getCartProductList();
        $data = $this->request->postMore([
            ['user', "admin"],
            ['password', "admin"]
        ]);
        //echo $data['user'];
        //echo;
        $list = $services->getUserList($data['user'], $data['password']);
        if(empty($list)) {
            return app('json')->fail('user name or password error');
        } else {
            return app('json')->success($list);
        }
    }

    public function distributor_order_summary(StoreOrderCartProductInfoServices $services) {
        $data = $this->request->postMore([
            ['product_ids', ""],
            ['start_time', ""] // 起始时间一定要
        ]);
        $start_time = strtotime($data['start_time']); // 起始日期0点时间戳
        $now_time = strtotime(date("Y-m-d"),time()); // 当天0点时间戳
        $seconds_per_day = 24 * 60 * 60; // 每天的秒数
        $result = array();
        for($ii = $start_time; $ii <= $now_time; $ii += $seconds_per_day) {
            $count = $services->getCartProductListLength($data['product_ids'], $ii);
            $money = $services->getCartProductMoney($data['product_ids'], $ii);
            if($count > 0)
            {
                array_push($result, [
                    'date' => date("Y-m-d", $ii),
                    'count' => $count,
                    'money' => $money
                ]);
            }
        }
        return app('json')->success($result);
    }

    public function distributor_order_detail(StoreOrderCartProductInfoServices $services) {
        $data = $this->request->postMore([
            ['product_ids', ""],
            ['date', ""], // 这个日期可以传空，如果为空则使用当天日期
            ['page', 1],
            ['limit', 10]
        ]);
        return app('json')->success($services->getCartProductList($data['product_ids'], $data['date'], $data['page'], $data['limit']));
    }
    
}