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
			ifnull(sum(total_fee),0) 
			FROM
				wsm_sale_return e
				LEFT JOIN wsm_depart_user k ON e.apply_id = k.uid 
				AND k.is_del = 0 
				AND k.STATUS = 1 
			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
			ifnull(sum(total_fee),0)  
			FROM
				wsm_order_back f
				LEFT JOIN wsm_depart_user p ON f.apply_id = p.uid 
				AND p.is_del = 0 
				AND p.STATUS = 1 
			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 
		AND b.is_del = 0 
		AND b.STATUS = 1
		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
				ifnull(sum(total_fee),0) 
			FROM
				wsm_sale_return e
				LEFT JOIN wsm_depart_user k ON e.apply_id = k.uid 
				AND k.is_del = 0 
				AND k.STATUS = 1 
			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
				ifnull(sum(total_fee),0) 
			FROM
				wsm_order_back f
				LEFT JOIN wsm_depart_user p ON f.apply_id = p.uid 
				AND p.is_del = 0 
				AND p.STATUS = 1 
			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 
		AND b.is_del = 0 
		AND b.STATUS = 1
		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
				ifnull(sum(total_fee),0) 
			FROM
				wsm_sale_return e
				LEFT JOIN wsm_depart_user k ON e.apply_id = k.uid 
				AND k.is_del = 0 
				AND k.STATUS = 1 
			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
				ifnull(sum(total_fee),0) 
			FROM
				wsm_order_back f
				LEFT JOIN wsm_depart_user p ON f.apply_id = p.uid 
				AND p.is_del = 0 
				AND p.STATUS = 1 
			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 
		AND b.is_del = 0 
		AND b.STATUS = 1
		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 = [];
        try{
        $wlb =$this->innterOrder($endtime);
        if(!empty($wlb)) $data=array_merge($data,$wlb);
        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);
        }catch (\Exception $e){
            return error_show(1004, $e->getFile()."|".$e->getLine()."|".$e->getMessage());
        }
        return app_show(0, "获取成功", $list);

    }

    public function check($data, &$temp)
    {
        $list = [];
        foreach ($data as $key => $value) {
            $temp['total'] += $value['total'];
            $temp['mthfee'] += $value['mthfee'];
            $temp['wtotal'] += $value['wtotal'];
            $temp['wthfee'] += $value['wthfee'];
            $temp['thfee'] += $value['thfee'];
            $temp['dtotal'] += $value['dtotal'];
            isset($list[$value['depar_id']]) ? "" : $list[$value['depar_id']] = ['total' => 0, 'mthfee' => 0, 'tips' => $value['tips'], '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'];
            $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'];

        }
        $temp['tips'] = array_sum(array_column($list,'tips'));
        $temp['thtotal'] = sprintf("%.2f", array_sum(array_column($list,'thtotal')));
        return $list;
    }


    public function innterOrder($endtime)
    {
        $db =Db::connect("mysql3");
        $list = $db->query("SELECT
	a.suppitem as p,
	       a.depart,
       43 as  depar_id,
	m,
	a.sale_total AS total,
	`a`.`th_total` AS `mthfee`,
	IFNULL(w,WEEK('{$endtime}',1)) as w,
	IFNULL(b.sale_total,0) AS wtotal,
	ifnull( `b`.`th_total`, 0 ) AS `wthfee`,
	IFNULL(c.d,date_format('{$endtime}', '%Y-%m-%d' )) as d,
	IFNULL(c.sale_total,0) AS dtotal,
	ifnull( `c`.`th_total`, 0 ) AS `thfee` 
FROM
	(
	SELECT MONTH
		( ordertime ) AS m,
		sum( sale_total ) AS sale_total,
		suppitem,
        depart,
		(
		SELECT
			ifnull( sum( t.th_qrd_fee ), 0 ) 
		FROM
					cfp_th_source t LEFT JOIN source_all l on 	l.productNo = t.th_qrdcpNo
		WHERE
		 t.th_qrdcpNo <> ''  and l.depart=k.depart and l.suppitem=k.suppitem and t.th_status<>3
			AND DATE_FORMAT ( t.createtime,'%Y-%m' )  = DATE_FORMAT ( '{$endtime}' ,'%Y-%m') 
		) AS th_total

	FROM
		source_all k
	WHERE
			DATE_FORMAT(ordertime,'%Y-%m') = DATE_FORMAT('{$endtime}','%Y-%m')
	GROUP BY
		suppitem,depart,
		m 
	) AS a
	LEFT JOIN (
	SELECT WEEK
		( ordertime, 1 ) AS w,
		sum( sale_total ) AS sale_total,
		suppitem,
        depart,
	(
		SELECT
			ifnull( sum( t.th_qrd_fee ), 0 ) 
		FROM
					cfp_th_source t LEFT JOIN source_all l on 	l.productNo = t.th_qrdcpNo
		WHERE
		 t.th_qrdcpNo <> ''  and l.depart=k.depart and l.suppitem=k.suppitem and t.th_status<>3
			AND week ( t.createtime,1 ) = WEEK ( '{$endtime}', 1 ) and DATE_FORMAT ( t.createtime,'%Y' )  = DATE_FORMAT ( '{$endtime}' ,'%Y') 
		) AS th_total 
	FROM
		source_all k
	WHERE
		WEEK ( ordertime, 1 ) = WEEK ( '{$endtime}', 1 )  and DATE_FORMAT ( ordertime,'%Y' )  = DATE_FORMAT ( '{$endtime}' ,'%Y') 
	GROUP BY
		suppitem,depart,
		w 
	) AS b ON a.suppitem = b.suppitem  and a.depart=b.depart
	LEFT JOIN (
	SELECT
		date_format( ordertime, '%Y-%m-%d' ) AS d,
		sum( sale_total ) AS sale_total,
		suppitem,depart,
		(

		SELECT
			ifnull( sum( t.th_qrd_fee ), 0 ) 
		FROM
					cfp_th_source t LEFT JOIN source_all l on 	l.productNo = t.th_qrdcpNo
		WHERE
		 t.th_qrdcpNo <> ''  and l.depart=k.depart and l.suppitem=k.suppitem and t.th_status<>3
			AND date_format ( t.createtime,'%Y-%m-%d') = date_format ( '{$endtime}', '%Y-%m-%d' )
		) AS th_total 	FROM
		source_all k
	WHERE
		date_format( ordertime, '%Y-%m-%d' ) = date_format( '{$endtime}', '%Y-%m-%d' ) 
	GROUP BY
		suppitem,depart,
	d 
	) AS c ON a.suppitem = c.suppitem  and a.depart=c.depart
");
        return $list;
    }
}