billiards-server/application/admin/controller/Dashboard.php

<?php

namespace app\admin\controller;

use addons\fastchat\library\Chat;
use app\admin\model\Admin;
use app\admin\model\dynamic\Dynamic;
use app\admin\model\massager\Comment;
use app\admin\model\massager\Massager;
use app\admin\model\massager\Visa;
use app\admin\model\Order;
use app\admin\model\profit\Bill;
use app\admin\model\Store;
use app\admin\model\system\Feedback;
use app\admin\model\User;
use app\admin\model\user\Wallet;
use app\api\model\deposit\Record;
use app\api\model\ThirdPayLog;
use app\common\controller\Backend;
use app\common\model\Attachment;
use fast\Date;
use think\Db;
use think\Request;

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

    public function __construct(Request $request = null)
    {
        parent::__construct($request);
        $admin = $this->auth->getUserInfo();
        $this->view->assign([
            "areas" => (new \app\admin\model\Area())->findAreaByAdmin($admin),
            "area_code" => $request->param("area_code"),
            "daterange" => $request->param("daterange"),
            "admin_type" => $admin["type"]
        ]);
    }

    private function fetchWhere()
    {
        $admin = $this->auth->getUserInfo();
        if (!$admin)
            $this->error("error");
        $p_where = [];
        if (\E_ADMIN_TYPE::Store === $admin["type"]) {
            array_push($p_where, ["store_id", "=", $admin["store_id"]]);
        } else if (\E_ADMIN_TYPE::Agency === $admin["type"]) {
            array_push($p_where, ["city_code", "in", is_null($admin["city_codes"]) ? [] : explode(",", $admin["city_codes"])]);
        }
        return $p_where;
    }


    private function fmt_params(array $params)
    {
        $area_code = null;
        $daterange = [0, time()];
        if (isset($params["area_code"]) && $params["area_code"] > 0) {
            $area_code = $params["area_code"];
        }
        if (isset($params["daterange"]) && strlen($params["daterange"]) > 0) {
            $daterange = array_map(function ($data) {
                return strtotime($data);
            }, explode(" - ", $params["daterange"]));
        }
        return [
            "area_code" => $area_code,
            "daterange" => $daterange
        ];
    }

    /**
     * @param null $area_code
     * @return string|\think\response\Json
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function index()
    {
        $p_where = $this->fetchWhere();
        $rows = [
            ["available" => [\E_ADMIN_TYPE::Platform], "title" => "代理商申请", "num" => \app\admin\model\Agency::where("status", \E_BASE_STATUS::Checking)->count(), "url" => "/backstage.php/agency?ref=addtabs"],
            ["available" => [\E_ADMIN_TYPE::Platform, \E_ADMIN_TYPE::Agency], "title" => "球房申请", "num" => Store::where("status", \E_BASE_STATUS::Checking)->count(), "url" => "/backstage.php/store/store/index"],
            ["available" => [\E_ADMIN_TYPE::Platform, \E_ADMIN_TYPE::Agency], "title" => "系统投诉", "num" => Feedback::where([
                "type" => "complaint",
                "status" => \E_BASE_STATUS::Default
            ])->count(), "url" => "/backstage.php/system/feedback?ref=addtabs"],
            ["available" => [\E_ADMIN_TYPE::Platform, \E_ADMIN_TYPE::Agency], "title" => "申述订单", "num" => Order::where("is_appeal", 1)->where("status", "<>", \E_ORDER_STATUS::Finish)->count(), "url" => "/backstage.php/order/order?ref=addtabs"],
            ["available" => [\E_ADMIN_TYPE::Platform, \E_ADMIN_TYPE::Agency], "title" => "异地签证", "num" => Visa::where("status", \E_BASE_STATUS::Default)->count(), "url" => "/backstage.php/massager/visa?ref=addtabs"],
            ["available" => [\E_ADMIN_TYPE::Platform, \E_ADMIN_TYPE::Agency], "title" => "助教申请", "num" => Massager::where("status", \E_BASE_STATUS::Checking)->count(), "url" => "/backstage.php/massager/massager?ref=addtabs"],
            ["available" => [\E_ADMIN_TYPE::Platform, \E_ADMIN_TYPE::Agency], "title" => "助教评论申述", "num" => Comment::where(["allegedly" => 1, "allegedly_status" => \E_BASE_STATUS::Default])->count(), "url" => "/backstage.php/massager/comment?ref=addtabs"],
            ["available" => [\E_ADMIN_TYPE::Platform], "title" => "动态审核", "num" => Dynamic::where(["status" => \E_BASE_STATUS::Checking])->count(), "url" => "/backstage.php/dynamic/dynamic?ref=addtabs"],
            ["available" => [\E_ADMIN_TYPE::Platform], "title" => "动态评论审核", "num" => \app\admin\model\dynamic\Comment::where("status", \E_BASE_STATUS::Checking)->count(), "url" => "/backstage.php/dynamic/comment?ref=addtabs"],
            ["available" => [\E_ADMIN_TYPE::Platform], "title" => "提现审核", "num" => \app\admin\model\deposit\Record::where("apply_status", \E_BASE_STATUS::Default)->count(), "url" => "/backstage.php/deposit/record?ref=addtabs"],
            ["available" => [\E_ADMIN_TYPE::Platform, \E_ADMIN_TYPE::Agency, \E_ADMIN_TYPE::Store], "title" => "待服务订单", "num" => Order::where("status", "in", [
                \E_ORDER_STATUS::Purchase,
            ])->where(count($p_where) > 0 ? $p_where[0][0] : "id", count($p_where) > 0 ? $p_where[0][1] : ">", count($p_where) > 0 ? $p_where[0][2] : 0)
                ->where("store_id", ">", 0)
                ->count(), "url" => "/backstage.php/order/order?ref=addtabs"],
        ];
        $this->view->assign("rows", $rows);
        return $this->view->fetch();
    }

    public function wait()
    {
        $this->view->assign([
            "totaluser" => User::count(),
            "sevendnu" => User::whereTime('jointime', '-7 days')->count(),
            "totalmassger" => Massager::count(),
            "totalagency" => Admin::where(["type" => \E_IDENTITY_TYPE::Agency])->count(),
            "totalstore" => Store::count(),
            "totalorder" => Order::count(),
            "totaluserresiduemoney" => Wallet::sum("money") + Wallet::sum("give_money"),
            "totalmassagerresiduemoney" => \app\admin\model\massager\Wallet::sum("profit_amount"),
            "totalagencyresiduemoney" => Admin::where(["type" => \E_IDENTITY_TYPE::Agency])->sum("profit_amount"),
            "totalstoreresiduemoney" => Store::sum("profit_amount"),
            "channeltotaluser" => User::where("parent_id", ">", 0)->count()
        ]);
        return $this->view->fetch();
    }

    public function massager()
    {
        $fmt = $this->fmt_params(input());
        $area_code = $fmt["area_code"];
        $daterange = $fmt["daterange"];
        $where = ["city_code", $area_code ? "=" : ">", $area_code ? $area_code : 0];
        $this->view->assign([
            "totalmassger" => Massager::where($where[0], $where[1], $where[2])->where("createtime", "BETWEEN", join(",", $daterange))->count(),
            "goodcomment" => Comment::where($where[0], $where[1], $where[2])->where("star", ">=", 3)->where("createtime", "BETWEEN", join(",", $daterange))->count(),
            "negativecomment" => Comment::where($where[0], $where[1], $where[2])->where("star", "<=", 3)->where("createtime", "BETWEEN", join(",", $daterange))->count(),
            "totalmassagerresiduemoney" => \app\admin\model\massager\Wallet::sum("profit_amount"),
            "totalmassageruseresiduemoney" => Record::where([
                "apply_status" => 'pass',
                "deposit_status" => 'success'
            ])
                ->where("massager_id", ">", 0)
                ->where("createtime", "BETWEEN", join(",", $daterange))
                ->where($where[0], $where[1], $where[2])
                ->sum("deposit_amount"),
            "topbyworkduration" => Db::query("SELECT w . massager_id,m . `name`,SUM(w . duration) as total_duration
                                                FROM `ma_massager_work` w JOIN ma_massager m ON w . massager_id = m . id
                                                WHERE w . clock_in_time BETWEEN {$daterange[0]} and $daterange[1]
    and w . clock_off_time BETWEEN {$daterange[0]} and $daterange[1]
    and m . city_code {$where[1]} {$where[2]}
                                                GROUP BY
                                                    w . massager_id
                                                ORDER BY
                                                    total_duration DESC 
                                                    LIMIT 20"),
            "topbynegative" => Db::query("SELECT
                c . massager_id,
                m . `name`,
                count(*) as total_count 
            FROM
                `ma_massager_comment` c
                JOIN ma_massager m ON c . massager_id = m . id
                WHERE c . updatetime BETWEEN {$daterange[0]} and $daterange[1]
    and m . city_code {$where[1]} {$where[2]}
                and c . negative = 1
            GROUP BY
                c . massager_id
            ORDER BY
                total_count DESC 
                LIMIT 20"),
        ]);

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

    public function agency()
    {
        $fmt = $this->fmt_params(input());
        $daterange = $fmt["daterange"];
        $this->view->assign([
            "totalagency" => Admin::where(["type" => \E_IDENTITY_TYPE::Agency])->where("createtime", "BETWEEN", join(',', $daterange))->count(),
            "totalagencyresiduemoney" => Admin::where("type", \E_IDENTITY_TYPE::Agency)->sum("profit_amount"),
            "totalagencyuseresiduemoney" => Record::where([
                "apply_status" => 'pass',
                "deposit_status" => 'success'
            ])
                ->where("agency_id", ">", 0)
                ->where("createtime", "BETWEEN", join(",", $daterange))
                ->sum("deposit_amount"),
        ]);
        return $this->view->fetch();
    }


    public function store()
    {
        $fmt = $this->fmt_params(input());
        $area_code = $fmt["area_code"];
        $daterange = $fmt["daterange"];
        $this->view->assign([
            "totalstore" => Store::count(),
            "totalstoreresiduemoney" => Store::sum("profit_amount"),
            "totalstoreuseresiduemoney" => Record::where([
                "apply_status" => 'pass',
                "deposit_status" => 'success'
            ])
                ->where("store_id", ">", 0)
                ->where("createtime", "BETWEEN", join(",", $daterange))
                ->sum("deposit_amount"),
        ]);
        return $this->view->fetch();
    }

    public function order()
    {
        $fmt = $this->fmt_params(input());
        $area_code = $fmt["area_code"];
        $daterange = $fmt["daterange"];
        $where = ["city_code", $area_code ? "=" : ">", $area_code ? $area_code : 0];
        $this->view->assign([
            "totalorder" => Order::where("status", "in", [
                \E_ORDER_STATUS::Purchase,
                \E_ORDER_STATUS::Proceed,
                \E_ORDER_STATUS::WaitFeedback
                , \E_ORDER_STATUS::Finish
            ])->where($where[0], $where[1], $where[2])
                ->where("createtime", "BETWEEN", join(",", $daterange))
                ->count(),
            "apptotalorder" => Order::where("status", "in", [
                \E_ORDER_STATUS::Purchase,
                \E_ORDER_STATUS::Proceed,
                \E_ORDER_STATUS::WaitFeedback
                , \E_ORDER_STATUS::Finish
            ])->where($where[0], $where[1], $where[2])
                ->where("createtime", "BETWEEN", join(",", $daterange))
                ->where("massager_id", ">", 0)
                ->count(),
            "storetotalorder" => Order::where("status", "in", [
                \E_ORDER_STATUS::Purchase,
                \E_ORDER_STATUS::Proceed,
                \E_ORDER_STATUS::WaitFeedback
                , \E_ORDER_STATUS::Finish
            ])->where($where[0], $where[1], $where[2])
                ->where("createtime", "BETWEEN", join(",", $daterange))
                ->where("store_id", ">", 0)
                ->count(),
            "totalchannlorder" => Order::where("status", "in", [
                \E_ORDER_STATUS::Purchase,
                \E_ORDER_STATUS::Proceed,
                \E_ORDER_STATUS::WaitFeedback
                , \E_ORDER_STATUS::Finish
            ])->where($where[0], $where[1], $where[2])
                ->where("channel_id", ">", 0)
                ->where("createtime", "BETWEEN", join(",", $daterange))
                ->count(),
            "totalturnover" => Order::where("status", "in", [
                \E_ORDER_STATUS::Purchase,
                \E_ORDER_STATUS::Proceed,
                \E_ORDER_STATUS::WaitFeedback
                , \E_ORDER_STATUS::Finish
            ])->where($where[0], $where[1], $where[2])
                ->where("createtime", "BETWEEN", join(",", $daterange))
                ->sum("total_real_amount"),
            "apptotalturnover" => Order::where("status", "in", [
                \E_ORDER_STATUS::Purchase,
                \E_ORDER_STATUS::Proceed,
                \E_ORDER_STATUS::WaitFeedback
                , \E_ORDER_STATUS::Finish
            ])->where($where[0], $where[1], $where[2])
                ->where("createtime", "BETWEEN", join(",", $daterange))
                ->where("massager_id", ">", 0)
                ->sum("total_real_amount"),
            "storetotalturnover" => Order::where("status", "in", [
                \E_ORDER_STATUS::Purchase,
                \E_ORDER_STATUS::Proceed,
                \E_ORDER_STATUS::WaitFeedback,
                \E_ORDER_STATUS::Finish
            ])->where($where[0], $where[1], $where[2])
                ->where("createtime", "BETWEEN", join(",", $daterange))
                ->where("store_id", ">", 0)
                ->sum("total_real_amount"),
        ]);

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


    public function finance()
    {
        $fmt = $this->fmt_params(input());
        $area_code = $fmt["area_code"];
        $daterange = $fmt["daterange"];
        $where = ["city_code", $area_code ? "=" : ">", $area_code ? $area_code : 0];

        // 开通会员订单金额
        $vip_amount = ThirdPayLog::where([
            "reason" => "充值会员",
            "status" => \E_BASE_STATUS::Normal
        ])
            ->where("createtime", "BETWEEN", join(",", $daterange))
            ->sum("amount");
        $record = \db()->query("
                SELECT
                        SUM( `change` ) AS tp_sum 
                    FROM
                        `ma_massager_bill` 
                    WHERE
                        `createtime` BETWEEN {$daterange[0]}
                        AND {$daterange[1]}
                        AND `change_type` = '102'
                        AND `currency_type` = 'money'
                        LIMIT 1
                ");
        $trip_amount = $record[0]["tp_sum"] ?? 0;
        $this->view->assign([
            "total_order_amount" => $this->order_amount($where, $daterange),
            "total_app_amount" => $this->order_amount($where, $daterange, ["massager_id", ">", 0]),
            "total_store_amount" => $this->order_amount($where, $daterange, ["massager_id", "=", null]),
            "total_trip_amount" => $trip_amount,
            "total_vip_amount" => $vip_amount,
            "platform_profit_amount" => $this->profit_amount($where, $daterange, \E_IDENTITY_TYPE::Platform),
            "agency_profit_amount" => $this->profit_amount($where, $daterange, \E_IDENTITY_TYPE::Agency),
            "store_profit_amount" => $this->profit_amount($where, $daterange, \E_IDENTITY_TYPE::Store),
            "massager_profit_amount" => $this->profit_amount($where, $daterange, \E_IDENTITY_TYPE::Massager) + $this->profit_amount($where, $daterange, \E_IDENTITY_TYPE::Massager, \E_PROFIT_BILL_CHANGE_TYPE::ChannelExpend),
            "platform_invite_profit_amount" => $this->profit_amount($where, $daterange, \E_IDENTITY_TYPE::Platform, \E_PROFIT_BILL_CHANGE_TYPE::ChannelIncome),
            "user_invite_profit_amount" => $this->profit_amount($where, $daterange, \E_IDENTITY_TYPE::User, \E_PROFIT_BILL_CHANGE_TYPE::ChannelIncome),
            "profit_deduction_amount" => Order::where("createtime", "BETWEEN", join(",", $daterange))
                ->where($where[0],$where[1],$where[2])
                ->where("payment_type", "<>", \E_ORDER_PAY_TYPE::Balance)
                ->sum('balance_deduction'),
            "user_profit_amount" => Record::where("createtime", "BETWEEN", join(",", $daterange))
            ->where([
                "identity_type" => \E_IDENTITY_TYPE::User,
                "apply_status" => "pass",
                "deposit_status" => "success"
            ])->sum("deposit_amount")
        ]);
        return $this->view->fetch();
    }

    private function profit_amount($where, $daterange, $identity_type, $change_type = \E_PROFIT_BILL_CHANGE_TYPE::Profit)
    {

        $record = \db()->query("
        SELECT
                SUM( `change` ) AS tp_sum 
            FROM
                `ma_profit_bill` 
            WHERE
                {$where[0]} {$where[1]} {$where[2]}
                AND `createtime` BETWEEN {$daterange[0]}
                AND {$daterange[1]}
                AND `change_type` = '{$change_type}'
                AND `identity_type` = '{$identity_type}'
                LIMIT 1
        ");
        return $record[0]["tp_sum"] ?? 0;
    }

    private function order_amount($where, $daterange, $Q = [])
    {
        // 总订单金额
        $q_1 = Order::where("status", "in", [
            \E_ORDER_STATUS::Finish
        ])->where($where[0], $where[1], $where[2])
            ->where("createtime", "BETWEEN", join(",", $daterange));
        if (count($Q) > 0)
            $q_1->where($Q[0], $Q[1], $Q[2]);
        $totalOrderAmount = $q_1->sum("total_real_amount");

        // 退款金额
        $q_2 = Order::where("status", "in", [
            \E_ORDER_STATUS::Finish
        ])->where($where[0], $where[1], $where[2])
            ->where([
                "is_appeal" => 1
            ])
            ->where("createtime", "BETWEEN", join(",", $daterange));
        if (count($Q) > 0)
            $q_2->where($Q[0], $Q[1], $Q[2]);
        $totalOrderRefundAmount = $q_2
            ->sum("refund_amount");

        // 出行费退款金额
        $q_3 = Order::where("status", "in", [
            \E_ORDER_STATUS::Finish
        ])->where($where[0], $where[1], $where[2])
//            ->where([
//                "is_refund_trip" => 1,
//                "is_appeal" => 1
//            ])
            ->where("createtime", "BETWEEN", join(",", $daterange));
        if (count($Q) > 0)
            $q_3->where($Q[0], $Q[1], $Q[2]);
        $totalOrderTripAmount = $q_3
            ->sum("trip_amount");
        return $totalOrderAmount - $totalOrderRefundAmount - $totalOrderTripAmount;
    }
}