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 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('失败');
        }
    }

    /**
     * 获取月份工序成本左侧菜单
     * @return void
     */
    public function getProcessTab()
    {
        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 = ['01、切纸机组','02、胶印机组', '03、卷凹机组', '04、圆烫机组','05、圆切机组', '06、单凹机组',
            '07、丝印机组','08、喷码机组','09、烫金机组','10、模切机组','11、检品机组','12、覆膜机组' ];
        $data = [];
        foreach ($months as $month) {
            $data[$month] = $sist;
        }
        $this->success('成功', $data);
    }

    /**
     * 获取月份工序成本列表
     * @return void
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function ProcessCostAccountingList()
    {
        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_月度成本明细')
            ->alias('a')
            ->join('设备_基本资料 b', 'a.sczl_jtbh = b.设备编号')
            ->where('b.设备编组', $param['sist'])
            ->where('a.sys_ny', $param['month'])
            ->count();
        $list = db('成本v23_月度成本明细')
            ->alias('a')
            ->join('设备_基本资料 b', 'a.sczl_jtbh = b.设备编号')
            ->where('b.设备编组', $param['sist'])
            ->where('a.sys_ny', $param['month'])
            ->field('a.*')
            ->order('a.sczl_gdbh')
            ->page($page, $pageSize)
            ->select();
        $data = [
            'total' => $total,
            'list' => $list,
        ];
        if (empty($list)) {
            $this->error('未找到数据');
        }else{
            $this->success('成功', $data);
        }
    }

    /**
     * 计算完工成本明细
     * @return void
     * @throws \think\Exception
     * @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 CalculateCompletedCost()
    {
        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);
        $field = [
            'a.jjcp_gdbh as 工单编号',
            'sum(b.车间人工) as 车间人工',
            'sum(b.部门人工附加) as 部门人工附加',
            'sum(c.金额) as 直接材料',
            'sum(b.分摊材料) as 分摊材料',
            'sum(b.直接水电) as 直接水电',
            'sum(b.分摊水电) as 分摊水电',
            'sum(b.废气处理) as 废气处理',
            'sum(b.锅炉 + b.热水锅炉) as 锅炉',
            'sum(b.空压机) as 空压机',
            'sum(b.真空鼓风机) as 真空鼓风机',
            'sum(b.中央空调) as 中央空调',
            'sum(b.分摊其他) as 分摊其他'
        ];

        $list = db('成品入仓')
            ->alias('a')
            ->join('成本v23_月度成本明细 b', 'a.jjcp_gdbh = b.sczl_gdbh')
            ->join('材料出库单列表 c', 'a.jjcp_gdbh = c.表体生产订单号', 'left')
            ->field($field)
            ->where('a.jjcp_sj', 'like', $month.'%')
            ->group('a.jjcp_gdbh')
            ->select();

        if (empty($list)) {
            $this->error('未找到完工工单');
        }

        // 获取所有工单编号
        $gdbhList = array_column($list, '工单编号');

        // 删除已存在的工单数据
        db('成本v23_完工工单车间成本汇总')
            ->where('工单编号', 'in', $gdbhList)
            ->delete();

        foreach ($list as $k => $v) {
            $list[$k]['考核直接材料'] = $v['直接材料'];
            $list[$k]['成本合计'] = $v['直接材料'] + $v['车间人工'] + $v['部门人工附加'] + $v['分摊材料'] + $v['直接水电'] + $v['分摊水电'] + $v['废气处理'] +
                $v['锅炉'] + $v['空压机'] + $v['真空鼓风机'] + $v['中央空调'] + $v['分摊其他'];
            $list[$k]['Sys_id'] = $param['sys_id'];
            $list[$k]['Sys_rq'] = date('Y-m-d H:i:s', time());
        }

        $sql = db('成本v23_完工工单车间成本汇总')->fetchSql(true)->insertAll($list);
        $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 GetCompletionWorkOrderCostTab()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $list = db('成本v23_完工工单车间成本汇总')
            ->alias('a')
            ->join('工单_基本资料 b', 'a.工单编号 = b.Gd_gdbh')
            ->join('产品_基本资料 c', 'b.成品代号 = c.产品编号')
            ->where('b.成品代号', '<>', '')
            ->where('c.客户编号', '<>', '')
            ->where('c.客户名称', '<>', '')
            ->field([
                'TRIM(c.客户名称) as 客户名称',
                'TRIM(c.客户编号) as 客户编号'
            ])
            ->group('客户编号, 客户名称')
            ->order('客户编号')
            ->select();
        if (empty($list)) {
            $this->error('未找到完工数据');
        }
        $data = [];
        foreach ($list as $k => $v) {
            $data[] = $v['客户编号'] .'-->'.$v['客户名称'];
        }
        $this->success('成功', $data);
    }


    /**
     * 完工工单成本列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function CompletionWorkOrderCostList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param)) {
            $this->error('参数错误');
        }

// 参数过滤和安全处理
        $param = array_map(function($value) {
            return is_string($value) ? trim($value) : $value;
        }, $param);

        $where = [];

// 精确查询成品代号
        if (!empty($param['code'])) {
            $code = htmlspecialchars($param['code'], ENT_QUOTES);
            $where['b.成品代号'] = ['like', $code . '%'];
        }

        // 分页参数处理
        $page = max(1, intval($param['page'] ?? 1));
        $pageSize = max(1, min(100, intval($param['limit'] ?? 30))); // 限制最大100条

        // 搜索条件处理
        if (!empty($param['search'])) {
            $search = htmlspecialchars($param['search'], ENT_QUOTES);
            $where['a.工单编号|b.成品名称'] = ['like', $search . '%'];
        }

        // 获取总数用于分页（性能优化）
        $totalQuery = db('成本v23_完工工单车间成本汇总')
            ->alias('a')
            ->join('工单_基本资料 b', 'a.工单编号 = b.Gd_gdbh')
            ->where($where);

        $total = $totalQuery->count();

        // 分页查询数据
        $list = [];
        if ($total > 0) {
            $offset = ($page - 1) * $pageSize;

            $list = db('成本v23_完工工单车间成本汇总')
                ->alias('a')
                ->join('工单_基本资料 b', 'a.工单编号 = b.Gd_gdbh')
                ->field([
                    'a.*',
                    'TRIM(b.成品代号) as 成品代号',
                    'TRIM(b.成品名称) as 成品名称',
                ])
                ->where($where)
                ->order('a.工单编号 DESC')  // 添加排序，避免分页混乱
                ->limit($offset, $pageSize)
                ->select();
        }
        if (empty($list)) {
            $this->error('未找到完工成本数据');
        }
         $data = [
            'total' => $total,
            'list' => $list,
        ];
        $this->success('成功', $data);
    }
}