Gong-Ying-Lian-API/app/common/model/MoneyLog.php

<?php
declare(strict_types=1);

namespace app\common\model;

use think\Model;

class MoneyLog Extends Model
{
    // 自动写入时间戳字段
    protected $autoWriteTimestamp = true;
    protected $createTime = 'createtime';
    protected $updateTime = 'updatetime';

    protected $type = [
        'createtime'     =>  'timestamp:Y-m-d H:i',
        'updatetime'     =>  'timestamp:Y-m-d H:i',
    ];

    const TYPE_INCOME = 1;
    const TYPE_EXPENDITURE = 2;

    const STATUS_HIDDEN= 0;  //隐藏
    const STATUS_NORMAL = 1; //正常



    //
    public function users()
    {
        return $this->hasOne(User::class,'id','user_id')->field('id,nickname');
    }


    public function stockconfig()
    {
        return $this->hasOne(StockConfig::class,'id','variety_id')->field('id,title');
    }
    

    //按照类型月统计 收入- 支出
    public static function getCountMonthBalance(string $time = '')
    {
        $income = self::where('type', self::TYPE_INCOME)->where('status', self::STATUS_NORMAL);
        if(!empty($time)){
            $income = $income->whereMonth('create_date', $time);
        }
        $income = $income->sum('change');
        //支出
        $expenditure = self::where('type', self::TYPE_EXPENDITURE)->where('status', self::STATUS_NORMAL);
        if(!empty($time)){
            $expenditure = $expenditure->whereMonth('create_date', $time);
        }
        $expenditure = $expenditure->sum('change');
        $balance = $income - $expenditure;
        return [
            'balance' => round($balance ,2),
            'income' => round($income,2),
            'expenditure' => round($expenditure,2)
        ];
    }


    //按照类型年统计 收入- 支出
    public static function getCountYearBalance(string $time = '')
    {
        $income = self::where('type', self::TYPE_INCOME)->where('status', self::STATUS_NORMAL);
        if(!empty($time)){
            $income = $income->whereYear('create_date', $time);
        }
        $income = $income->sum('change');
        //支出
        $expenditure = self::where('type', self::TYPE_EXPENDITURE)->where('status', self::STATUS_NORMAL);
        if(!empty($time)){
            $expenditure = $expenditure->whereYear('create_date', $time);
        }
        $expenditure = $expenditure->sum('change');
        $balance = $income - $expenditure;
        return [
            'balance' => round($balance,2),
            'income' => round($income,2),
            'expenditure' => round($expenditure,2)
        ];
    }


    //按照类型天统计 收入- 支出
    public static function getCountDayBalance(string $time = '')
    {
        $list = self::where('status', self::STATUS_NORMAL);
        if(!empty($time)) $list = $list->whereMonth('create_date', $time);
        $list = $list->group('create_date')
                ->field('create_date, sum(if(type=1,`change`,0)) as income, sum(if(type=2,`change`, 0)) as expenditure')
                ->select();
        return $list;
    }

    //支收总览
    public static function getCountAllBalance(int $type, string $time = '', string $user_ids='')
    {
        $list = self::where('status', self::STATUS_NORMAL);
        if($type == 1 && !empty($time)) $list = $list->whereMonth('create_date', $time);
        if($type == 2 && !empty($time)) $list = $list->whereYear('create_date', $time);
        if($type == 3 && !empty($time)){
            $expTime = explode(',', $time);
            $list = $list->whereTime('create_date', [$expTime[0], $expTime[1]]);
        }
        if(!empty($user_ids)) $list = $list->whereIn('user_id', $user_ids);
        $list = $list
                ->field('type, sum(if(type=1,`change`,0)) as income, sum(if(type=2,`change`, 0)) as expenditure')
                ->group('type')
                ->select();
        return $list;
    }


    //统计用户余额
    public static function getCountAllUserBalance(int $type, string $time = '', string $user_ids='')
    {
        $list = self::alias('a')
            ->join('user b', 'a.user_id=b.id')
            ->where('a.status', self::STATUS_NORMAL);

        if($type == 1 && !empty($time)) $list = $list->whereMonth('create_date', $time);
        if($type == 2 && !empty($time)) $list = $list->whereYear('create_date', $time);
        if($type == 3 && !empty($time)){
            $expTime = explode(',', $time);
            $list = $list->whereTime('create_date', [$expTime[0], $expTime[1]]);
        }
        if(!empty($user_ids)) $list = $list->whereIn('user_id', $user_ids);
        $list = $list
                ->field('a.user_id, sum(if(a.type=1,`change`,0)) as income, sum(if(a.type=2,`change`, 0)) as expenditure,b.nickname,b.avatar')
                ->group('user_id')
                ->select();
        return $list;
    }

    //统计所有月统计
    public static function getCountAllMonthBalance(int $type, string $time = '', string $user_ids='')
    {
        $list = self::where('status', self::STATUS_NORMAL);

        if($type == 1 && !empty($time)) $list = $list->whereMonth('create_date', $time);
        //年
        if($type == 2 && !empty($time)) $list = $list->whereYear('create_date', $time);
        //自定义
        if($type == 3 && !empty($time)){
            $expTime = explode(',', $time);
            $list = $list->whereTime('create_date', [$expTime[0], $expTime[1]]);
        }
        if(!empty($user_ids)) $list = $list->whereIn('user_id', $user_ids);
        //月
        $list = $list
            ->field('create_month, sum(if(type=1,`change`,0)) as income, sum(if(type=2,`change`, 0)) as expenditure')
            ->group('create_month')
            ->order('create_month desc')
            ->select();
        return $list;
    }
}