screen/application/admin/controller/GoodsData.php

<?php

namespace app\admin\controller;

use app\admin\model\Admin;
use app\admin\model\OrderInfo;
use app\admin\model\Refund;
use app\admin\model\User;
use app\common\controller\Backend;
use app\common\model\Attachment;
use app\common\model\SysConfig;
use fast\Date;
use think\Db;

/**
 * 控制台
 *
 * @icon   fa fa-dashboard
 * @remark 用于展示当前系统中的统计数据、统计报表及重要实时数据
 */
class GoodsData extends Backend
{

    /**
     * 查看
     */
    public function index()
    {
        try {
            \think\Db::execute("SET @@sql_mode='';");
        } catch (\Exception $e) {

        }
        $postTime = input('startTime', '');
        $where = [];
        $map = [];
        if ($postTime) {
            $time = explode(' - ', $postTime);
            $startTime = strtotime($time[0]);
            $endTime = strtotime($time[1]);
            $where['create_time'] = array('between', [$startTime, $endTime]);
            $map['createtime'] = array('between', [$startTime, $endTime]);
        }
        //某一时间段各单品订单数量占比
        $infoModel = new OrderInfo();
        $orderIds = Db("Orders")->where($where)->where('status', 'in', '5,10,20,30')->column('id');
        $goodsList = $infoModel
            ->where($where)
            ->where('order_id','in',$orderIds)
            ->where('is_return_goods',0)
            ->field('goods_id,goods_name, sum(amount_pay) AS amount_total')
            ->group('goods_id')
            ->select();
        $goods = [];
        $goodsSale = [];
        $goodsGross = [];
        $goodsGrossRate = [];
        foreach ($goodsList as $v){
            $v['nums'] =  $infoModel
                ->where($where)
                ->where('order_id','in',$orderIds)
                ->where('goods_id',$v['goods_id'])
                ->where('is_return_goods',0)
                ->group('order_id')
                ->count();
            $goods[$v['goods_name']] = $v['nums'];
            $goodsSale[$v['goods_name']] = $v['amount_total'];
            $saleTotal =  $infoModel
                ->where($where)
                ->where('order_id','in',$orderIds)
                ->where('goods_id',$v['goods_id'])
                ->where('is_return_goods',0)
                ->sum('amount');//某商品总销售额

            $saleTotalNum =$infoModel
                ->where($where)
                ->where('order_id','in',$orderIds) ->where('is_return_goods',0)
                ->where('goods_id',$v['goods_id'])->count();
            $saleTotal = $saleTotal*$saleTotalNum;
            $amountCostTotal =  $infoModel
                ->where($where)
                ->where('order_id','in',$orderIds)
                ->where('is_return_goods',0)
                ->where('goods_id',$v['goods_id'])->sum('amount_cost');//某商品总成本
            $amountCostTotal = $amountCostTotal*$saleTotalNum;
            $gross = bcsub($saleTotal, $amountCostTotal);//某商品毛利
            $grossRate = 0;
            if ($saleTotal > 0) $grossRate = round($gross / $saleTotal, 2) * 100;//某商品毛利率
            $goodsGross[$v['goods_name']] = $gross;
            $goodsGrossRate[$v['goods_name']] = $grossRate;
        }

        $payTotalCount = Db("Orders")->where($where)->where('status', 'in', '5,10,20,30')->count();

//        foreach ($goodsList as $k => $v) {
//            $rate = 0;
//            if($payTotalCount > 0)$rate = round($v['nums']/$payTotalCount,2)*100;
//            $rate = $rate.'%';
//              $goods[$v['goods_name']] = $v['nums'];
//            $goods[$v['goods_name']] = $rate;
//        }

        //平台所有商品退货率
        $allRefund = Db('order_info_refund')->count();
        $allRefundRate = 0;
        if($payTotalCount > 0)$allRefundRate = round($allRefund/$payTotalCount,2)*100;
        $allRefundRate = $allRefundRate.'%';

        //单品退货率
        $refundList = Refund::with('goods')
            ->where($where)
            ->field('goods_id, sum(num) AS nums')
            ->group('goods_id')
            ->select();
        $refundRate = [];
        foreach ($refundList as &$v){
            $orderIds = OrderInfo::where('goods_id',$v['goods_id'])->column('order_id');
            $orderIds = Db("Orders")->where($where)->where('status', 'in', '5,10,20,30')->where('id', 'in', $orderIds)->column('id');
            $goodsNum = OrderInfo::where('order_id','in',$orderIds)->where('goods_id',$v['goods_id'])->sum('num');

            $rate = 0;
            if($goodsNum > 0)$rate = round($v['nums']/$goodsNum,2)*100;
            $refundRate[$v['goods']['name']] = $rate;
        }

        //总销售额
        $sale = Db('Orders')->where($where)->where('status', 'in', '5,10,20')->sum('amount_pay');
        $refund = Db('OrderInfoRefund')->where($where)->where('refund_status', 20)->sum('amount');
        $sale = bcsub($sale, $refund);


        //毛利、毛利率
        $install = Db('Orders')->where($where)->where('status', 'in', '5,10,20')->sum('amount_install');//总安装费
        $orderIds = Db('Orders')->where($where)->where('status', 'in', '5,10,20')->column('id');
        $cost = Db('OrderInfo')->where('order_id', 'in', $orderIds)->sum('amount_cost');//总成本
        $gross_profit = bcsub($sale, $install);
        $gross_profit = bcsub($gross_profit, $cost);
        $gross_profit_margin = 0;
        if ($sale > 0) $gross_profit_margin = round($gross_profit / $sale, 2) * 100;//毛利率
        //销售金额排名
        $list = OrderInfo
            ::with(['goods'])
            ->where($where)
            ->where('is_return_goods',0)
            ->where('order_id','in',$orderIds)
            ->field('goods_id, sum(amount*num) AS total,sum(num) AS total_num')
            ->group('goods_id')
            ->order('total desc')->select();
        $list = $list->toArray();
        $saleMoney = [];
        $saleNum = [];
        foreach ($list as $v){
            $saleMoney[$v['goods']['name']] = $v['total'];
            $saleNum[$v['goods']['name']] = $v['total_num'];
        }



//        if ($this->request->isAjax()) {
//            //如果发送的来源是Selectpage，则转发到Selectpage
//            if ($this->request->request('keyField')) {
//                return $this->selectpage();
//            }
//            $orderIds = Db("Orders")->where($where)->where('status', 'in', '5,10,20,30')->column('id');
//            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
//            $this->model = new \app\admin\model\OrderInfo;
//            $list = OrderInfo
//                ::with(['goods'])
//                ->where($where)
//                ->where('is_return_goods',0)
//                ->where('order_id','in',$orderIds)
//                ->field('goods_id, sum(amount*num) AS total')
//                ->group('goods_id')
//                ->order('total desc')
//                ->paginate($limit);
//
//
//
//            $result = array("total" => $list->total(), "rows" => $list->items());
//
//            return json($result);
//        }


        $this->view->assign([
            'sale' => $sale,

            'gross_profit' => $gross_profit,
            'gross_profit_margin' => $gross_profit_margin,
            'post_time' => $postTime,
            'all_refund_rate'=>$allRefundRate

        ]);


        $this->assignconfig('goods_column', array_keys($goods));
        $this->assignconfig('goods_data', array_values($goods));
        $this->assignconfig('goods_sale_column', array_keys($goodsSale));
        $this->assignconfig('goods_sale_data', array_values($goodsSale));
        $this->assignconfig('goods_gross_column', array_keys($goodsGross));
        $this->assignconfig('goods_gross_data', array_values($goodsGross));
        $this->assignconfig('goods_gross_rate_column', array_keys($goodsGrossRate));
        $this->assignconfig('goods_gross_rate_data', array_values($goodsGrossRate));
        $this->assignconfig('refund_rate_column', array_keys($refundRate));
        $this->assignconfig('refund_rate_data', array_values($refundRate));
        $this->assignconfig('sale_money_column', array_keys($saleMoney));
        $this->assignconfig('sale_money_data', array_values($saleMoney));
        $this->assignconfig('sale_num_column', array_keys($saleNum));
        $this->assignconfig('sale_num_data', array_values($saleNum));

        return $this->view->fetch();
    }


}