phpstock/app/admin/controller/Stat.php

<?php


namespace app\admin\controller;
use think\facade\Db;

use think\App;

class Stat extends \app\BaseController
{

    public function __construct(App $app)
    {
        parent::__construct($app);
    }

    public function  list()
    {
        $post = request()->post();
        $endtime = isset($post['date']) ? $post['date'] : date("Y-m-d");

        $sql = "
        SELECT
	`month` as m,
	ifnull(month_total_fee,0) as total,
	ifnull(`week`,'') as w,
	ifnull(week_total_fee,0) as wtotal,
	ifnull(`day`,'') as d,
	ifnull(day_total_fee,0) as dtotal,
	ifnull(a.NAME,'') as depart,
   a.id as depar_id,
	ifnull(month_thfee,0) as mthfee,
	ifnull(week_thfee,0) as wthfee,
	ifnull(day_thfee,0) as thfee,
	ifnull(a.platform_name,'') as p 
FROM
	(
	SELECT MONTH
		( a.addtime ) AS `month`,
		SUM( total_price ) AS month_total_fee,
		((
			SELECT
				sum( total_fee ) 
			FROM
				wsm_sale_return e
				LEFT JOIN wsm_depart_user k ON e.apply_id = k.uid 
			WHERE
				k.itemid = c.id 
				AND e.platform_id = a.platform_id 
				AND DATE_FORMAT( e.addtime, '%Y-%m' ) = DATE_FORMAT( '$endtime', '%Y-%m' )) + (
			SELECT
				sum( total_fee ) 
			FROM
				wsm_order_back f
				LEFT JOIN wsm_depart_user p ON f.apply_id = p.uid 
			WHERE
				p.itemid = c.id 
				AND f.platform_id = a.platform_id 
			AND DATE_FORMAT( f.addtime, '%Y-%m' ) = DATE_FORMAT( '$endtime', '%Y-%m' ))) AS month_thfee,
		a.platform_id,
		c.`name`,
	       c.id,
		d.platform_name 
	FROM
		wsm_sale a
		LEFT JOIN wsm_depart_user b ON a.apply_id = b.uid
		LEFT JOIN wsm_platform d ON d.id = a.platform_id
		LEFT JOIN wsm_company_item c ON c.id = b.itemid 
	WHERE
		DATE_FORMAT( a.addtime, '%Y-%m' ) = DATE_FORMAT( '$endtime', '%Y-%m' ) 
	GROUP BY
		a.platform_id,
		platform_name,
		c.NAME,
		c.id,
		`month` 
	) AS a
	LEFT JOIN (
	SELECT WEEK
		( a.addtime, 1 ) AS `week`,
		SUM( total_price ) AS week_total_fee,
		((
			SELECT
				sum( total_fee ) 
			FROM
				wsm_sale_return e
				LEFT JOIN wsm_depart_user k ON e.apply_id = k.uid 
			WHERE
				k.itemid = c.id 
				AND e.platform_id = a.platform_id 
				AND WEEK ( e.addtime, 1 ) = WEEK ( '$endtime', 1 ) 
				AND DATE_FORMAT ( e.addtime, '%Y' ) = DATE_FORMAT ( '$endtime', '%Y' )) + (
			SELECT
				sum( total_fee ) 
			FROM
				wsm_order_back f
				LEFT JOIN wsm_depart_user p ON f.apply_id = p.uid 
			WHERE
				p.itemid = c.id 
				AND f.platform_id = a.platform_id 
				AND WEEK ( f.addtime, 1 ) = WEEK ( '$endtime', 1 ) 
				AND DATE_FORMAT ( f.addtime, '%Y' ) = DATE_FORMAT ( '$endtime', '%Y' ) 
			)) AS week_thfee,
		a.platform_id,
		c.`name`,
	        c.id,
		platform_name 
	FROM
		wsm_sale a
		LEFT JOIN wsm_depart_user b ON a.apply_id = b.uid
		LEFT JOIN wsm_platform d ON d.id = a.platform_id
		LEFT JOIN wsm_company_item c ON c.id = b.itemid 
	WHERE
		WEEK ( a.addtime, 1 ) = WEEK ( '$endtime', 1 ) 
	GROUP BY
		a.platform_id,
		platform_name,
		c.NAME,
		c.`id`,
		`week` 
	) AS b ON a.NAME = b.NAME 
	AND a.platform_id = b.platform_id
	LEFT JOIN (
	SELECT
		date_format( a.addtime, '%Y-%m-%d' ) AS `day`,
		SUM( total_price ) AS day_total_fee,
		((
			SELECT
				sum( total_fee ) 
			FROM
				wsm_sale_return e
				LEFT JOIN wsm_depart_user k ON e.apply_id = k.uid 
			WHERE
				k.itemid = c.id 
				AND e.platform_id = a.platform_id 
				AND DATE_FORMAT( e.addtime, '%Y-%m-%d' ) = DATE_FORMAT( '$endtime', '%Y-%m-%d' )) + (
			SELECT
				sum( total_fee ) 
			FROM
				wsm_order_back f
				LEFT JOIN wsm_depart_user p ON f.apply_id = p.uid 
			WHERE
				p.itemid = c.id 
				AND f.platform_id = a.platform_id 
			AND DATE_FORMAT( f.addtime, '%Y-%m-%d' ) = DATE_FORMAT( '$endtime', '%Y-%m-%d' ))) AS day_thfee,
		a.platform_id,
		c.`name`,
	        c.id as depar_id,
		platform_name 
	FROM
		wsm_sale a
		LEFT JOIN wsm_depart_user b ON a.apply_id = b.uid
		LEFT JOIN wsm_platform d ON d.id = a.platform_id
		LEFT JOIN wsm_company_item c ON c.id = b.itemid 
	WHERE
		date_format( a.addtime, '%Y-%m-%d' ) = date_format ( '$endtime', '%Y-%m-%d' ) 
	GROUP BY
		a.platform_id,
		platform_name,
		c.NAME,
		c.id,
		`day` 
	) AS d ON a.NAME = d.NAME 
	AND a.platform_id = d.platform_id
        ";
        $data = Db::query($sql);
        $dtae = isset($endtime) && $endtime != "" ? $endtime . " 00:00:00" : date("Y-m-d H:i:s");
        $temp = ["p" => "总计", 'item' => "-", "thtotal" => 0, "total" => 0, "mthfee" => 0, "wtotal" => 0, "wthfee" => 0, "dtotal" => 0, "thfee" => 0, "tips" => 0, "rate" => 0, "thrate" => 0];
        $tips = Db::name("depart_tips")->field("depart_id ,total_tips")->where("year=YEAR('{$dtae}') and month=month('{$dtae}')")->select();
        $kdata = [];
        foreach ($data as $key => $value) {
            $value['tips'] = 0;
            foreach ($tips as $val) {
                if ($value["depar_id"] == $val['depart_id']) {
                    $value['tips'] = $val['total_tips'];
                }
            }
            $value['thtotal'] = $value['total'] - $value['mthfee'];
            $kdata[] = $value;
        }
        $list = $this->check($kdata, $temp);
        array_walk($list, function (&$value) {
            if (is_null($value['tips']) || $value['tips'] == 0) {
                $value['tips'] = "-";
                $value["rate"] = "-";
                $value["thrate"] = "-";
            } else {
                $value["rate"] = number_format($value['total'] / $value['tips'] * 100, 2);
                $value["thrate"] = number_format(($value['total'] - $value['mthfee']) / $value['tips'] * 100, 2);
                $value['tips'] = sprintf("%.2f", $value['tips']);
            }
            $value['total'] = sprintf("%.2f", $value['total']);
            $value['mthfee'] = sprintf("%.2f", $value['mthfee']);
            $value['wtotal'] = sprintf("%.2f", $value['wtotal']);
            $value['wthfee'] = sprintf("%.2f", $value['wthfee']);
            $value['dtotal'] = sprintf("%.2f", $value['dtotal']);
            $value['thfee'] = sprintf("%.2f", $value['thfee']);
            $value['thtotal'] = sprintf("%.2f", $value['thtotal']);
        });
        $list=array_values($list);
        if ($temp['tips'] == 0) {
            $temp['rate'] = "-";
            $temp['thrate'] = "-";
            $temp['tips'] = "-";
        } else {
            $temp['rate'] = number_format($temp['total'] / $temp['tips'] * 100, 2);
            $temp['thrate'] = number_format(($temp['total'] - $temp['mthfee']) / $temp['tips'] * 100, 2);;
            $temp['tips'] = round($temp['tips'], 2);
        }
        $temp['total'] = sprintf("%.2f", $temp['total']);
        $temp['mthfee'] = sprintf("%.2f", $temp['mthfee']);
        $temp['wtotal'] = sprintf("%.2f", $temp['wtotal']);
        $temp['wthfee'] = sprintf("%.2f", $temp['wthfee']);
        $temp['dtotal'] = sprintf("%.2f", $temp['dtotal']);
        $temp['thfee'] = sprintf("%.2f", $temp['thfee']);
        $temp['thtotal'] = sprintf("%.2f", $temp['thtotal']);

        $sort = array_column($list, "thrate");
        array_multisort($sort, SORT_ASC, $list);
        array_push($list, $temp);
        $list = array_reverse($list);
        return app_show(0, "获取成功", $list);

    }

    public function check($data, &$temp)
    {
        $list = [];
        foreach ($data as $key => $value) {

            isset($list[$value['depar_id']]) ? "" : $list[$value['depar_id']] = ['total' => 0, 'mthfee' => 0, 'tips' => 0, 'wtotal' => 0, 'wthfee' => 0, 'dtotal' => 0,
                'child' => [], 'depart' => '', 'thfee' => 0, 'thtotal' => 0];
            $list[$value['depar_id']]['total'] += $value['total'];
            $list[$value['depar_id']]['mthfee'] += $value['mthfee'];
            $list[$value['depar_id']]['wtotal'] += $value['wtotal'];
            $list[$value['depar_id']]['wthfee'] += $value['wthfee'];
            $list[$value['depar_id']]['dtotal'] += $value['dtotal'];
            $list[$value['depar_id']]['thfee'] += $value['thfee'];
            $list[$value['depar_id']]['thtotal'] += $value['thtotal'];
            $list[$value['depar_id']]['tips'] += is_null($value['tips']) || $value['tips'] == 0 ? 0 : $value['tips'];

            $temp['total'] += $value['total'];
            $temp['mthfee'] += $value['mthfee'];
            $temp['wtotal'] += $value['wtotal'];
            $temp['wthfee'] += $value['wthfee'];
            $temp['thfee'] += $value['thfee'];
            $temp['dtotal'] += $value['dtotal'];
            $temp['tips'] += is_null($value['tips']) || $value['tips'] == 0 ? 0 : $value['tips'];
            $temp['thtotal'] += $value['thtotal'];
            if (is_null($value['tips']) || $value['tips'] == 0) {
                $value['tips'] = "-";
            } else {
                $value['tips'] = sprintf("%.2f", $value['tips']);
            }
            $value['total'] = sprintf("%.2f", $value['total']);
            $value['mthfee'] = sprintf("%.2f", $value['mthfee']);
            $value['wtotal'] = sprintf("%.2f", $value['wtotal']);
            $value['wthfee'] = sprintf("%.2f", $value['wthfee']);
            $value['dtotal'] = sprintf("%.2f", $value['dtotal']);
            $value['thfee'] = sprintf("%.2f", $value['thfee']);
            $value['thtotal'] = sprintf("%.2f", $value['thtotal']);
            $list[$value['depar_id']]['child'][] = $value;
            $list[$value['depar_id']]['depart'] = $value['depart'];

        }
        return $list;
    }
}