mes-server-api/application/api/controller/CostAccounting.php

<?php


namespace app\api\controller;

use app\common\controller\Api;
use think\Request;

/**
 * 各月制造费用维护
 */
class CostAccounting extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];


    /**
     * 各月制造费用维护菜单
     * @return void
     */
    public function getTab()
    {
        // 1. 请求方法验证
        if (!$this->request->isGet()) {
            $this->error('仅支持GET请求');
        }

        // 2. 使用静态变量避免重复创建数组
        static $tabs, $sist;
        if ($tabs === null) {
            $tabs = [
                '1、月度人工数据',
                '2、水电气直接费用',
                '3、水电气分摊费用',
                '4、其他待摊费用',
                '5、车间色度数'
            ];

            $sist = ['胶印车间', '凹丝印车间', '印后车间'];
        }

        // 3. 数据库查询优化
        $months = db('成本v23_月度成本明细')
            ->field('sys_ny AS year_month')
            ->group('sys_ny')
            ->order('sys_ny DESC')
            ->column('sys_ny');

        // 4. 使用模板构建策略
        $data = [];
        $template = [];

        // 预构建模板
        foreach ($tabs as $tab) {
            $template[$tab] = ($tab === '2、水电气直接费用') ? $sist : $tab;
        }

        // 仅需单次循环赋值
        foreach ($months as $month) {
            $data[$month] = $template;
        }

        $this->success('成功', $data);
    }


    /**
     * 月度车间人工维护
     * @return void
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function ArtificialAdd()
    {
        if($this->request->isPost() === false){
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $data = [];
        foreach ($param as $key => $value) {
            $data[$key] = [
                'Sys_ny' => $value['sys_ny'],
                '车间' => $value['sist'],
                '一线工资总额' => $value['number'],
                'Sys_id' => $value['sys_id'],
                'Sys_rq' => date('Y-m-d H:i:s', time())
            ];
        }
        $sql = db('成本v23_各月人工')->fetchSql(true)->insertAll($data);
        $res = db()->query($sql);
        if ($res !== false) {
            $this->success('新增成功');
        }else{
            $this->error('添加失败');
        }
    }


    /**
     * 月度人工维护列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function ArtificialAddList()
    {
        if($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || empty($param['Sys_ny'])) {
            $this->error('参数错误');
        }
        $list = db('成本v23_各月人工')
            ->where('Sys_ny', $param['Sys_ny'])
            ->field('Sys_ny, 车间, 一线工资总额, Sys_id as 创建用户, Sys_rq as 创建时间,Mod_rq as 修订时间,UniqID')
            ->select();
        $this->success('成功', $list);
    }


    /**
     * 月度人工维护修改
     * @return void
     * @throws \think\Exception
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function ArtificialEdit()
    {
        if($this->request->isPost() === false){
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $i = 0;
        foreach ($param as $value) {
            $sql = db('成本v23_各月人工')
                ->where('UniqID', $value['UniqID'])
                ->fetchSql(true)
                ->update(['一线工资总额'=>$value['number'],'Mod_rq'=>date('Y-m-d H:i:s', time())]);
            $res = db()->query($sql);
            if ($res === false) {
                $i++;
            }
        }
        if ($i === 0) {
            $this->success('修改成功');
        }else{
            $this->error('修改失败');
        }
    }


    /**
     * 月度人工数据右侧上方列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function ArtificialList()
    {
        if (!$this->request->isGet()) {
            $this->error('仅支持GET请求');
        }

        $month = $this->request->param('month');
        if (empty($month)) {
            $this->error('month参数缺失或为空');
        }
        $list = db('成本v23_各月人工')->alias('a')
            ->join('设备_基本资料 b', 'a.车间 = b.使用部门')
            ->join('绩效工资汇总 c', 'a.Sys_ny = c.sys_ny AND c.sczl_jtbh = b.设备编号')
            ->join('成本_各月其他费用 d', 'a.Sys_ny = d.sys_ny','left')
            ->field([
                'a.Sys_ny' => '年月',
                'rtrim(a.车间)' => '车间',
                'SUM(c.个人计件工资) + SUM(c.个人加班工资)' => '人工分摊因子总额',
                'a.一线工资总额' => '车间实发工资',
                'd.部门人员工资' => '部门人员工资实发',
                'd.管理人员工资' => '管理人员工资实发'
            ])
            ->where('a.Sys_ny', $month)
            ->group('a.Sys_ny, a.车间')
            ->select();
        if (empty($list)) {
            $this->success('未找到匹配数据', []);
        }
        $this->success('获取成功', $list);
    }

    /**
     * 月度人工数据下方工单详情
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function ArtificialDetailList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $month = $param['month'];
        $sist = $param['sist'];
        $list = db('工单_质量考核汇总')
            ->alias('a')
            ->join('设备_基本资料 b', 'a.sczl_jtbh = b.设备编号')
            ->join('工单_印件资料 c', 'a.Gy0_gdbh = c.Yj_Gdbh and a.印件及工序 = c.yj_Yjno')
            ->join('工单_工艺资料 d', 'a.Gy0_gdbh = d.Gy0_gdbh and a.印件及工序 = d.Gy0_yjno and a.工序 = d.Gy0_gxh')
            ->field([
                'a.sys_ny' => '年月',
                'a.Gy0_gdbh' => '工单编号',
                'a.印件及工序' => '印件号',
                'a.工序' => '工序号',
                'c.yj_yjmc' => '印件名称',
                'd.Gy0_gxmc' => '工序名称',
                'a.印件工序产量' => '班组车头产量',
                'd.工价系数' => '工序难度系数',
                'SUM(a.CjsJe)' => '计件工资',
                'rtrim(b.使用部门)' => '车间名称',
            ])
            ->where('a.Sys_ny', $month)
            ->where('b.使用部门', $sist)
            ->group('a.Sys_ny, a.Gy0_gdbh,a.印件及工序,a.印件及工序')
            ->order('a.Gy0_gdbh')
            ->select();
        if (empty($list)) {
            $this->success('未找到数据');
        }else{
            $this->success('成功', $list);
        }
    }

    /**
     * 水电气直接费用右侧列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function shuidianqiList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $month = $param['month'];
        $sist = $param['sist'];
        $list = db('成本_各月水电气')
            ->field('rtrim(部门名称) as 部门名称,rtrim(设备编号) as 设备编号,rtrim(科目名称) as 设备名称,rtrim(耗电量) as 耗电量,
            rtrim(单位电价) as 单位电价,rtrim(耗气量) as 耗气量,rtrim(单位气价) as 单位气价,rtrim(Sys_id) as 创建用户,Sys_rq as 创建时间,UniqID,耗电量*单位电价 as 直接费用合计')
            ->where('Sys_ny', $month)
            ->where('部门名称', $sist)
            ->where('费用类型', '直接')
            ->group('Sys_ny, Sys_rq, UniqID')
            ->select();
        if (empty($list)) {
            $this->success('未找到数据');
        }else{
            $this->success('成功', $list);
        }
    }

    /**
     * 水电气下方工单详情
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function shuidianqiDetailList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $month = substr($param['month'], 0, 4) . '-' . substr($param['month'], 4, 2);
        $machine = $param['machine'];
        $list = db('设备_产量计酬')
            ->alias('a')
            ->join('工单_印件资料 b', 'a.sczl_gdbh = b.Yj_Gdbh and a.sczl_yjno = b.yj_Yjno')
            ->field('a.sczl_gdbh as 工单编号,b.yj_yjmc as 印件名称,a.sczl_yjno as 印件号,a.sczl_gxh as 工序号,
            sum(a.sczl_cl)*a.sczl_ls as 班组车头产量,sum(a.sczl_设备运行工时) as 占用机时')
            ->where('a.sczl_rq','like', '%'.$month.'%')
            ->where('a.sczl_jtbh', $machine)
            ->group('a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh')
            ->select();
        if (empty($list)) {
            $this->success('未找到数据');
        }
        foreach ($list as $k => $v) {
            $list[$k]['计件产量'] = $v['班组车头产量'];
            $list[$k]['水电气分摊因子'] = $v['占用机时'];
            $list[$k]['年月'] = $param['month'];
        }
        $this->success('成功', $list);
    }

    /**
     * 水电气分摊费用
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function shuidianqifentanList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $month = $param['month'];
        $list = db('成本_各月水电气')
            ->field('rtrim(部门名称) as 部门名称,rtrim(设备编号) as 设备编号,rtrim(科目名称) as 设备名称,rtrim(耗电量) as 耗电量,
            rtrim(单位电价) as 单位电价,rtrim(耗气量) as 耗气量,rtrim(单位气价) as 单位气价,rtrim(Sys_id) as 创建用户,Sys_rq as 创建时间,UniqID,耗电量*单位电价 as 直接费用合计')
            ->where('Sys_ny', $month)
            ->where('费用类型', '分摊')
            ->group('Sys_ny, Sys_rq, UniqID')
            ->select();
        if (empty($list)) {
            $this->success('未找到数据');
        }else{
            $this->success('成功', $list);
        }
    }


    /**
     * 各月工单人工明细
     * @return void
     * @throws \think\db\exception\BindParamException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function ChromaticityAdd()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $list = db('绩效工资汇总')
            ->alias('a')
            ->join('工单_工艺资料 b','a.sczl_gdbh = b.Gy0_gdbh and a.sczl_yjno = b.Gy0_yjno and a.sczl_gxh = b.Gy0_gxh')
            ->join('设备_基本资料 c','a.sczl_jtbh = c.设备编号','LEFT')
            ->join('工单_印件资料 d','a.sczl_gdbh = d.Yj_Gdbh and a.sczl_yjno = d.yj_Yjno')
            ->field('a.sczl_gdbh as 工单编号,a.sczl_yjno as 印件号,a.sczl_gxh as 工序号,sum(a.班组车头产量) as 班组车头产量,b.Gy0_gxmc as 工序名称,
            a.sczl_ms as 墨色数,c.使用部门,b.印刷方式,b.版距,b.工价系数,a.sczl_jtbh,d.yj_yjmc as 印件名称,sum(a.车头产量占用机时) as 占用机时,a.sys_rq as 年月,
            a.工序难度系数,sum(a.班组换算产量) as 班组换算产量,a.千件工价')
            ->where('a.sys_ny', $param['month'])
            ->group('a.sczl_gdbh,a.sczl_yjno,a.sczl_gxh,a.sczl_jtbh')
            ->select();
        $data = [];
        foreach ($list as $k => $v) {
            if ($v['版距'] === '0.0'){
                $list[$k]['版距'] = 1000;
            }
            if ($v['墨色数'] === '0.00'){
                $list[$k]['墨色数'] = 1;
            }
            if (strpos($v['工序名称'],'切废')){
                $list[$k]['墨色数'] = 0.2;
            }
            $chanliang = $v['班组车头产量']*$v['工序难度系数'] + $v['班组换算产量'];
            $data[] = [
                '车间名称' => $v['使用部门'],
                'sys_ny' => $param['month'],
                'sczl_gdbh' => $v['工单编号'],
                '印件名称' => $v['印件名称'],
                'sczl_yjno' => $v['印件号'],
                'sczl_gxh' => $v['工序号'],
                '工序名称' => $v['工序名称'],
                'sczl_jtbh' => $v['sczl_jtbh'],
                '卷张换算系数' => $list[$k]['版距']/1000,
                '占用机时' => $v['占用机时'],
                '班组车头产量' => $v['班组车头产量'],
                'sczl_ms' => $list[$k]['墨色数'],
                '工序难度系数' => $v['工序难度系数'],
                '班组换算产量' => $v['班组换算产量'],
                '千件工价' => $v['千件工价'],
                '计件产量' => $chanliang,
                '水电分摊因子' => $v['占用机时'],
                '材料分摊因子' => $chanliang,
                '人工分摊因子' => ($chanliang/1000)*$v['千件工价'],
                'Sys_id' => $param['sys_id'],
                'Sys_rq' => date('Y-m-d H:i:s', time())
            ];
        }
        db('成本v23_月度成本明细')->where('sys_ny', $param['month'])->delete();
        $sql = db('成本v23_月度成本明细')->fetchSql(true)->insertAll($data);
        $res = db()->query($sql);
        if ($res !== false) {
            $this->success('成功');
        }else{
            $this->error('失败');
        }
    }


    /**
     * 各月工单色度数
     * @return void
     * @throws \think\db\exception\BindParamException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function ChromaticityDetailAdd()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $monthArr = db('成本v23_月度成本明细')->where(['sys_ny' => $param['month']])->select();
        if (empty($monthArr)) {
            $this->error('请先创建月度数据...');
        }
        $sist = ['胶印车间','凹丝印车间','印后车间','检验车间'];
        $list = db('成本v23_月度成本明细')
            ->alias('a')
            ->join('设备_基本资料 b','a.sczl_jtbh = b.设备编号')
            ->join('工单_工艺资料 c','a.sczl_gdbh = c.Gy0_gdbh and a.sczl_yjno = c.Gy0_yjno and a.sczl_gxh = c.Gy0_gxh')
            ->field('a.sczl_gdbh,a.sczl_yjno,a.sczl_gxh,a.sczl_jtbh,a.工序名称,sum(a.计件产量) as 产量,a.sczl_jtbh,a.sczl_ms,b.使用部门,
            sum(a.占用机时) as 通电工时,c.Gy0_dedh,c.Gy0_gxmc')
            ->where('a.sys_ny', $param['month'])
            ->whereIn('车间名称',$sist)
            ->group('a.sczl_gdbh,a.sczl_yjno,a.sczl_gxh,a.sczl_jtbh')
            ->select();
        if (empty($list)) {
            $this->error('没找到生产数据');
        }
        $data = [];
        foreach ($list as $k => $v) {
            if ($v['sczl_ms'] === '0.00'){
                $list[$k]['sczl_ms'] = 1;
            }
            if (strpos($v['Gy0_gxmc'],'切废') !== false){
                $list[$k]['sczl_ms'] = 0.2;
            }
            $data[] = [
                '年月' => $param['month'],
                'sczl_gdbh' => $v['sczl_gdbh'],
                'sczl_yjno' => $v['sczl_yjno'],
                'sczl_gxh' => $v['sczl_gxh'],
                'sczl_jtbh' => $v['sczl_jtbh'],
                'sczl_gxmc' => $v['工序名称'],
                'sczl_ms' => $v['sczl_ms'],
                'sczl_dedh' => $v['Gy0_dedh'],
                '通电时间' => $v['通电工时'],
                'sczl_cl' => $v['产量'],
                '部门' => $v['使用部门'],
                'Sys_ID' => $param['sys_id'],
                'Sys_Rq' => date('Y-m-d H:i:s', time())
            ];
        }
        if (db('成本_各月色度数')->where('年月',$param['month'])->count() !== 0) {
            db('成本_各月色度数')->where('年月',$param['month'])->delete();
        }
        $sql = db('成本_各月色度数')->fetchSql(true)->insertAll($data);
        $res = db()->query($sql);
        if ($res !== false) {
            $this->success('成功');
        }else{
            $this->error('失败');
        }
    }

    /**
     * 各月车间色度数列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function ChromaticityDetailList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        // 执行统计查询
        $stats =  db('成本_各月色度数')
            ->field([
                'rtrim(部门) AS workshop',
                'SUM(sczl_cl * IF(sczl_ms=0, 1, sczl_ms)) AS total'
            ])
            ->where('部门', '<>', '')   // 过滤空车间数据
            ->where('sczl_cl', '>', 0) // 过滤无效产量
            ->where('年月',  $param['month'])
            ->group('部门')
            ->select();

        // 构造中文返回结果
        $result = [];
        foreach ($stats as $item) {
            $result[] = [
                '年月' => $param['month'],
                '车间' => $item['workshop'],
                '色度数' => number_format($item['total'], 2)
            ];
        }
        $this->success('成功', $result);
    }

    /**
     * 车间色度数详情列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function MonochromaticDetailList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $list = db('成本_各月色度数')
            ->where('年月', $param['month'])
            ->where('部门', $param['sist'])
            ->order('sczl_gdbh')
            ->select();
        $this->success('成功', $list);
    }

    /**
     * 车间成本核算汇总
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function SummaryCostAccountingList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $page = $this->request->param('page', 1);
        $pageSize = $this->request->param('limit', 30);
        $total = db('成本v23_月度成本明细')
            ->where('车间名称', $param['sist'])
            ->where('sys_ny', $param['month'])
            ->count();
        $list = db('成本v23_月度成本明细')
            ->where('车间名称', $param['sist'])
            ->where('sys_ny', $param['month'])
            ->order('sczl_gdbh')
            ->page($page, $pageSize)
            ->select();
        $data = [
            'total' => $total,
            'list' => $list,
        ];
        if (empty($list)) {
            $this->error('未找到数据');
        }else{
            $this->success('成功', $data);
        }
    }


    /**
     * 创建各月水电气分摊
     * @return void
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function UtilitiesAdd()
    {
        if ($this->request->isPost() === false) {
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $data = [];
        $currentTime = date('Y-m-d H:i:s');

        foreach ($param as $item) {
            // 验证必要字段是否存在
            if (empty($item['sys_ny']) || empty($item['sist']) || empty($item['科目名称'])) {
                $this->error('缺少必要参数');
            }

            // 确保数值字段正确处理为 decimal 类型
            $data[] = [
                'Sys_ny'     => $item['sys_ny'],
                '部门名称'    => $item['sist'],
                '费用类型'    => '分摊',
                '科目名称'    => $item['科目名称'],
                '耗电量'      => isset($item['耗电量']) ? (float)$item['耗电量'] : 0.00,
                '单位电价'    => isset($item['单位电价']) ? (float)$item['单位电价'] : 0.00,
                '耗气量'      => isset($item['耗气量']) ? (float)$item['耗气量'] : 0.00,
                '单位气价'    => isset($item['单位气价']) ? (float)$item['单位气价'] : 0.00,
                'Sys_id'     => $item['sys_id'] ?? '',
                'Sys_rq'     => $currentTime
            ];
        }
        $result = db('成本_各月水电气')->where('Sys_ny', $data[0]['Sys_ny'])->where('费用类型','分摊')->delete();
        $sql = db('成本_各月水电气')->fetchSql(true)->insertAll($data);
        $res = db()->query($sql);
        if ($res !== false) {
            $this->success('成功');
        }else{
            $this->error('失败');
        }
    }

    /**
     * 各月水电气分摊费用列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function UtilitiesList()
    {
        if($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }

        $list = db('成本_各月水电气')
            ->field('rtrim(部门名称) as 部门名称,rtrim(科目名称) as 科目名称,耗电量,单位电价,耗气量,单位气价')
            ->where('费用类型', '分摊')
            ->where('Sys_ny', $param['month'])
            ->group('部门名称,科目名称')
            ->select();
        if (empty($list)) {
            $list = db('成本_各月水电气')
                ->field('rtrim(部门名称) as 部门名称,rtrim(科目名称) as 科目名称')
                ->where('费用类型', '分摊')
                ->group('部门名称,科目名称')
                ->select();
        }
        foreach ($list as $k => $v) {
            $list[$k]['年月'] = $param['month'];
        }
        $this->success('成功', $list);
    }


    /**
     * 成本汇总左侧列表
     * @return void
     */
    public function getSummaryTab()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $months = db('成本v23_月度成本明细')
            ->field('sys_ny AS year_month')
            ->group('Sys_ny')
            ->order('Sys_ny DESC')
            ->column('Sys_ny');
        $sist = ['胶印车间','凹丝印车间','印后车间','检验车间'];
        $data = [];
        foreach ($months as $month) {
            $data[$month] = $sist;
        }
        $this->success('成功', $data);
    }

    //计算每个车间色度数
    private function CountSistChromaticity($month,$sist)
    {
        $data = db('成本v23_月度成本明细')
            ->where('sys_ny', $month)
            ->where('车间名称', $sist)
            ->group('车间名称')
            ->value('班组车头产量*sczl_ms as 色度数');
        return $data;
    }

    //计算每个机台色度数
    private function CountMachineChromatic($month,$sist)
    {
        $data = db('成本v23_月度成本明细')
            ->where('sys_ny', $month)
            ->where('车间名称', $sist)
            ->group('sczl_jtbh')
            ->value('班组车头产量*sczl_ms as 色度数');
        return $data;
    }

    //水电气分摊费用下方明细列表
//    public function shuidianqiMachineDetailList()
//    {
//        if ($this->request->isGet() === false) {
//            $this->error('请求错误');
//        }
//        $param = $this->request->param();
//        if (empty($param)) {
//            $this->error('参数错误');
//        }
//        $list = db('设备_基本资料')
//            ->alias('a')
//            ->join('成本_各月分摊系数 b', 'a.设备编号 = b.设备编号', 'LEFT')
//            ->field()
//
//    }

    /**
     * 其他待摊费用
     * @return void
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function PrepaidExpensesListEdit()
    {
        if ($this->request->isPost() === false) {
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $data = [
            'sys_ny' => $param['month'],
            '部门人员工资' => $param['salary1'],
            '管理人员工资' => $param['salary2'],
            '场地租金' => $param['rental'],
            '待摊折旧' => $param['depreciation'],
            '工资成本占比' => $param['proportion'],
            '其他' => $param['rest'],
            '后勤人员工资' => $param['profit'],
            'sys_id' => $param['sys_id'],
        ];
        $result = db('成本_各月其他费用')->where('sys_ny', $param['month'])->count();
        if ($result > 0) {
            $data['mod_rq'] = date('Y-m-d H:i:s', time());
            $sql = db('成本_各月其他费用')->where('sys_ny', $param['month'])->fetchSql(true)->update($data);
        }else{
            $data['sys_rq'] = date('Y-m-d H:i:s', time());
            $sql = db('成本_各月其他费用')
                ->fetchSql(true)
                ->insert($data);
        }
        $res = db()->query($sql);
        if ($res !== false) {
            $this->success('成功');
        }else{
            $this->error('失败');
        }
    }

    /**
     * 其他待摊费用列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function PrepaidExpensesList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $list = db('成本_各月其他费用')
            ->where('sys_ny', $param['month'])
            ->field('sys_ny as 年月,部门人员工资,管理人员工资,场地租金,待摊折旧,工资成本占比,其他,后勤人员工资,sys_id as 创建用户,sys_rq as 创建时间,mod_rq as 修改时间,UniqID')
            ->find();
        if (empty($list)) {
            $this->error('未找到数据');
        }else{
            $this->success('成功', $list);
        }
    }

    /**
     * 参考其他费用参考月份
     * @return void
     * @throws \think\db\exception\BindParamException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function PrepaidExpensesDetailCopy()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $formMonth = $param['formMonth'];
        $toMonth = $param['toMonth'];
        $list = db('成本_各月其他费用')
            ->where('sys_ny', $formMonth)
            ->field('管理人员工资,场地租金,待摊折旧,工资成本占比,后勤人员工资')
            ->find();
        $list['sys_ny'] = $toMonth;
        $list['sys_id'] = $param['sys_id'];
        $list['sys_rq'] = date('Y-m-d H:i:s', time());
        $sql = db('成本_各月其他费用')
            ->fetchSql(true)
            ->insert($list);
        $res = db()->query($sql);
        if ($res !== false) {
            $this->success('成功');
        }else{
            $this->error('失败');
        }
    }


    /**
     * 各月水电气直接费用创建
     * @return void
     * @throws \think\db\exception\BindParamException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function shuidianqiDetailAdd()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $month = substr($param['month'], 0, 4) . '-' . substr($param['month'], 4, 2);
        $sist = ['胶印车间','凹丝印车间','印后车间'];
        $list = db('设备_产量计酬')
            ->alias('a')
            ->join('设备_基本资料 b', 'a.sczl_jtbh = b.设备编号')
            ->where('a.sczl_rq', 'like', $month . '%')
            ->where('b.sys_sbID','<>','')
            ->where('b.使用部门','in',$sist)
            ->field('a.sczl_jtbh,sum(a.sczl_设备运行工时) as 通电工时,b.使用部门,rtrim(b.设备名称) as 设备名称')
            ->order('b.使用部门,a.sczl_jtbh')
            ->group('a.sczl_jtbh')
            ->select();
        $data = [];
        foreach ($list as $k => $v) {
           $data[] = [
               'Sys_ny' => $param['month'],
               '部门名称' => $v['使用部门'],
               '费用类型' => '直接',
               '设备编号' => $v['sczl_jtbh'],
               '科目名称' => $v['设备名称'],
               '耗电量' => $v['通电工时'],
               '单位电价' => 0.69,
               'Sys_id' => $param['sys_id'],
               'Sys_rq' => date('Y-m-d H:i:s', time()),
           ];
        }
        $sql = db('成本_各月水电气')->fetchSql(true)->insertAll($data);
        $res = db()->query($sql);
        if ($res !== false) {
            $this->success('成功');
        }else{
            $this->error('失败');
        }
    }

    /**
     * 获取各车间人工分摊因子比例
     * @param string $month 年月
     * @return array
     */
    protected function WageRatio($month)
    {
        try {
            // 使用一次查询获取所有需要的数据
            $result = db('成本v23_月度成本明细')
                ->where('sys_ny', $month)
                ->whereNotNull('车间名称')
                ->field('
                    SUM(人工分摊因子) as total,
                    rtrim(车间名称) as workshop_name,
                    SUM(人工分摊因子) as workshop_total
                ')
                ->group('车间名称')
                ->select();

            if (empty($result)) {
                return [];
            }

            // 计算总人工分摊因子
            $total = array_sum(array_column($result, 'workshop_total'));

            // 计算每个车间的比例
            $data = [];
            foreach ($result as $item) {
                if ($total > 0) {
                    $data[$item['workshop_name']] = number_format($item['workshop_total'] / $total, 2);
                } else {
                    $data[$item['workshop_name']] = 0;
                }
            }

            return $data;
        } catch (\Exception $e) {
            // 记录日志
            \think\facade\Log::error('WageRatio计算失败: ' . $e->getMessage());
            return [];
        }
    }

    /**
     * 获取指定部门各月的色度数数据
     * @param string $month 年月
     * @param string $sist 部门
     * @return array
     */
    protected function WageRatioMonth($month, $sist)
    {
        try {
            // 使用一次查询获取数据和总数
            $query = db('成本_各月色度数')
                ->where(['年月' => $month, '部门' => $sist]);

            // 计算总数
            $total = $query->value('SUM(sczl_cl * CASE 
                WHEN sczl_ms = 0 OR sczl_ms IS NULL OR sczl_ms = \'\' THEN 1 
                ELSE sczl_ms 
            END)');

            // 获取列表数据
            $list = $query->field("
                sczl_gdbh,
                sczl_yjno,
                sczl_gxh,
                sczl_jtbh,
                sczl_cl,
                CASE 
                    WHEN sczl_ms = 0 OR sczl_ms IS NULL OR sczl_ms = '' 
                    THEN 1 
                    ELSE sczl_ms 
                END as sczl_ms
            ")->select();

            return [
                'total' => $total ?: 0, // 防止空值
                'list' => $list ?: [],  // 防止空值
            ];
        } catch (\Exception $e) {
            \think\facade\Log::error('WageRatioMonth查询失败: ' . $e->getMessage());
            return ['total' => 0, 'list' => []];
        }
    }

    /**
     * 计算工资分配
     * @param string $month 年月
     * @param string $sist 部门
     * @param float $ratio 比例
     * @param string $type 类型
     * @param float $amount 金额
     * @return array
     */
    protected function WageRatioCalculation($month, $sist, $ratio, $type, $amount)
    {
        try {
            $data = [];
            $chromaNumber = $this->WageRatioMonth($month, $sist);

            if (empty($chromaNumber['list']) || $chromaNumber['total'] == 0) {
                return [];
            }

            $money = $ratio * $amount;
            $name = $type === '部门人员工资' ? '车间人工' : '部门人工附加';

            foreach ($chromaNumber['list'] as $item) {
                if ($chromaNumber['total'] > 0) {
                    $perAmount = ($item['sczl_cl'] * $item['sczl_ms'] / $chromaNumber['total']) * $money;
                    $data[] = [
                        'name' => $name,
                        'money' => round($perAmount, 2),
                        'sczl_gdbh' => $item['sczl_gdbh'],
                        'sczl_yjno' => $item['sczl_yjno'],
                        'sczl_gxh' => $item['sczl_gxh'],
                        'sczl_jtbh' => $item['sczl_jtbh'],
                    ];
                }
            }

            return $data;
        } catch (\Exception $e) {
            \think\facade\Log::error('WageRatioCalculation计算失败: ' . $e->getMessage());
            return [];
        }
    }

    /**
     * 工资费用分配主函数（优化版）
     */
    public function WageExpensesList()
    {

        // 参数验证
        if (!$this->request->isGet()) {
            throw new \Exception('请求错误，仅支持GET请求');
        }

        $param = $this->request->param();
        if (empty($param) || !isset($param['month'])) {
            throw new \Exception('参数错误，缺少必要参数');
        }

        $month = trim($param['month']);

        // 获取工资比例
        $wageRatio = $this->WageRatio($month);
        if (empty($wageRatio)) {
            throw new \Exception('未找到该月的人工分摊因子数据');
        }

        // 获取月度工资数据
        $monthWage = db('成本_各月其他费用')
            ->where('sys_ny', $month)
            ->field('部门人员工资,管理人员工资')
            ->find();

        if (empty($monthWage)) {
            throw new \Exception('未找到该月的工资数据');
        }

        // 准备批量更新数据
        $updateData = [];
        $updateCount = 0;

        // 遍历所有车间
        foreach ($wageRatio as $workshopName => $ratio) {
            // 计算两种类型的工资分配
            $updateTypes = [
                '部门人员工资' => '车间人工',
                '管理人员工资' => '部门人工附加'
            ];

            foreach ($updateTypes as $wageType => $fieldName) {
                if (!isset($monthWage[$wageType])) {
                    continue;
                }

                $calculatedData = $this->WageRatioCalculation(
                    $month,
                    $workshopName,
                    $ratio,
                    $wageType,
                    $monthWage[$wageType]
                );

                // 收集更新数据
                foreach ($calculatedData as $item) {
                    if (!empty($item['sczl_gdbh'])) {
                        // 构建唯一标识作为更新条件
                        $conditions = [
                            'sczl_gdbh' => $item['sczl_gdbh'],
                            'sczl_gxh' => $item['sczl_gxh'] ?? null,
                            'sczl_jtbh' => $item['sczl_jtbh'] ?? null,
                            'sczl_yjno' => $item['sczl_yjno'] ?? null,
                        ];

                        // 生成唯一标识字符串
                        $uniqCondition = http_build_query(array_filter($conditions));

                        $updateData[] = [
                            'condition' => $uniqCondition, // 用于去重的条件
                            'conditions' => $conditions,   // 实际查询条件
                            'field' => $item['name'],
                            'value' => $item['money'],
                            'month' => $month
                        ];
                        $updateCount++;
                    }
                }
            }
        }

        // 批量更新
        $updatedRows = $this->batchUpdateData($updateData,$month);

        // 返回成功结果
        return json([
            'code' => 200,
            'msg' => '工资分配完成',
            'data' => [
                'updated_rows' => $updatedRows,
                'update_count' => $updateCount,
                'workshop_count' => count($wageRatio)
            ]
        ]);
    }

    /**
     * 批量更新数据（原生SQL版本）
     * @param array $updateData 更新数据
     * @param string $month 月份
     * @return int 更新的行数
     */
    private function batchUpdateData(array $updateData, string $month)
    {
        if (empty($updateData)) {
            return 0;
        }

        $updatedRows = 0;

        // 按字段分组
        $groupedData = [];
        foreach ($updateData as $item) {
            $field = $item['field'];
            $groupedData[$field][] = $item;
        }

        // 对每个字段进行批量更新
        foreach ($groupedData as $field => $items) {

                // 构建CASE WHEN语句
                $caseWhens = [];
                $gdbhList = [];

                foreach ($items as $item) {
                    $baseCondition = "sczl_gdbh = '{$item['conditions']['sczl_gdbh']}'";

                    // 添加其他可选条件
                    $conditions = [$baseCondition];

                    if (!empty($item['conditions']['sczl_gxh'])) {
                        $conditions[] = "sczl_gxh = '{$item['conditions']['sczl_gxh']}'";
                    }
                    if (!empty($item['conditions']['sczl_jtbh'])) {
                        $conditions[] = "sczl_jtbh = '{$item['conditions']['sczl_jtbh']}'";
                    }
                    if (!empty($item['conditions']['sczl_yjno'])) {
                        $conditions[] = "sczl_yjno = '{$item['conditions']['sczl_yjno']}'";
                    }

                    $whenCondition = implode(' AND ', $conditions);
                    $caseWhens[] = "WHEN {$whenCondition} THEN {$item['value']}";

                    $gdbhList[] = $item['conditions']['sczl_gdbh'];
                }

                if (empty($caseWhens)) {
                    continue;
                }

                // 去重
                $gdbhList = array_unique($gdbhList);
                $gdbhStr = implode("','", $gdbhList);
                $caseStr = implode(' ', $caseWhens);

                // 构建SQL
                $sql = "UPDATE 成本v23_月度成本明细 
                   SET {$field} = CASE {$caseStr} ELSE {$field} END 
                   WHERE sczl_gdbh IN ('{$gdbhStr}') AND sys_ny = '{$month}'";

                // 执行SQL
                $result = db()->execute($sql);
                $updatedRows += $result;

            }

        return $updatedRows;
    }

    /**
     * 获取工资分配详情（调试用）
     */
    public function getWageDistributionDetail($month)
    {
        $wageRatio = $this->WageRatio($month);
        $distribution = [];

        foreach ($wageRatio as $workshop => $ratio) {
            $distribution[$workshop] = [
                'ratio' => $ratio,
                'data' => $this->WageRatioMonth($month, $workshop)
            ];
        }

        return $distribution;
    }

    /**
     * 计算工艺材料分摊
     * @param $month
     * @return bool
     */
    public function calculationChroma($month)
    {
        try {
            // 1. 批量获取数据，减少数据库查询次数
            $date = substr($month, 0, 4) . '-' . substr($month, 4, 2);

            // 获取分摊材料总金额
            $totalMoney = db('材料出库单列表')
                ->where([
                    '出库日期' => ['like', $date . '%'],
                    '部门' => '印刷成本中心'
                ])
                ->whereNull('表体生产订单号')
                ->field('SUM(金额) as money')
                ->find();

            if (!$totalMoney || $totalMoney['money'] <= 0) {
                return false;
            }

            // 获取工单工艺数据及总色度数
            $processData = db('成本v23_月度成本明细')
                ->whereNotNull('车间名称')
                ->where('sys_ny', $month)
                ->field('Uniqid, 班组车头产量*sczl_ms as 色度数, SUM(班组车头产量*sczl_ms) OVER() as total_chroma')
                ->order('Uniqid asc')
                ->select();

            if (empty($processData)) {
                return false;
            }

            // 从第一条数据中获取总色度数
            $totalChroma = $processData[0]['total_chroma'] ?? 0;

            if ($totalChroma <= 0) {
                return false; // 防止除以零
            }

            // 2. 批量更新数据，减少数据库操作次数
            $updateData = [];
            foreach ($processData as $process) {
                $money = round($totalMoney['money'] * ($process['色度数'] / $totalChroma), 2);
                $updateData[] = [
                    'Uniqid' => $process['Uniqid'],
                    '分摊材料' => $money
                ];
            }

            // 批量更新（使用fetchSql构建安全的SQL）
            if (!empty($updateData)) {
                return $this->batchUpdateWithFetchSql('成本v23_月度成本明细', $updateData, 'Uniqid');
            }

            return false;

        } catch (\Exception $e) {
            // 记录错误日志
            \think\Log::error('工艺材料分摊计算失败: ' . $e->getMessage());
            return false;
        }
    }

    /**
     * 使用fetchSql方法批量更新数据（安全处理中文字段名）
     * @param string $table 表名
     * @param array $data 更新数据数组
     * @param string $pk 主键字段名
     * @return bool
     */
    protected function batchUpdateWithFetchSql($table, $data, $pk = 'Uniqid')
    {
        if (empty($data)) {
            return false;
        }

        try {
            // 方法一：使用查询构建器配合fetchSql
            $ids = array_column($data, $pk);

            // 开始事务
            db()->startTrans();

            // 先查询现有数据
            $query = db($table)
                ->whereIn($pk, $ids)
                ->field($pk)
                ->fetchSql(true)
                ->select();
            $sql = $query;

            // 构造批量更新SQL
            $updateSql = "UPDATE `{$table}` SET ";

            // 获取所有需要更新的字段（排除主键）
            $fields = array_keys($data[0]);
            $updateFields = array_diff($fields, [$pk]);

            $cases = [];
            foreach ($updateFields as $field) {
                $caseSql = "`{$field}` = CASE ";
                foreach ($data as $item) {
                    $caseSql .= "WHEN `{$pk}` = '{$item[$pk]}' THEN '{$item[$field]}' ";
                }
                $caseSql .= "ELSE `{$field}` END";
                $cases[] = $caseSql;
            }

            $updateSql .= implode(', ', $cases);
            $updateSql .= " WHERE `{$pk}` IN ('" . implode("','", $ids) . "')";

            // 执行更新
            $result = db()->execute($updateSql);

            db()->commit();
            return $result !== false;

        } catch (\Exception $e) {
            db()->rollback();
            \think\Log::error('批量更新失败: ' . $e->getMessage());

            // 如果批量更新失败，回退到单条更新（也使用fetchSql）
            return $this->singleUpdateFallback($table, $data, $pk);
        }
    }

    /**
     * 单条更新回退方案（使用fetchSql）
     */
    protected function singleUpdateFallback($table, $data, $pk)
    {
        $success = true;

        foreach ($data as $item) {
            try {
                // 使用fetchSql构建安全的更新语句
                $updateData = [];
                foreach ($item as $key => $value) {
                    if ($key !== $pk) {
                        $updateData[$key] = $value;
                    }
                }

                $sql = db($table)
                    ->where($pk, $item[$pk])
                    ->update($updateData)
                    ->fetchSql(true);

                // 获取SQL并执行
                $result = db()->execute($sql);

                if ($result === false) {
                    $success = false;
                    \think\Log::error('单条更新失败 - SQL: ' . $sql);
                }

            } catch (\Exception $e) {
                $success = false;
                \think\Log::error('单条更新异常: ' . $e->getMessage());
            }
        }

        return $success;
    }

}