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

<?php

namespace app\api\controller;

use app\common\controller\Api;
use app\common\library\token\driver\Redis;
use think\Db;
use Overtrue\Pinyin;

/**
 * 中间表数据同步
 */
class Synchronization extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /**
     * 工单资料数据同步
     */
    public function WorkOrderData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db3 = Db::connect(config('database.db3'));
        $workOrderList = $db3->name('U8_06工单资料')
            ->where('MES接收时间', null)
            ->where('MES接收状态', '0')
            ->where('U8插入类型', '<>', '关闭')
            ->select();
        if (empty($workOrderList)) {
            $this->success('未获取新工单');
        }
        $j = $m = $n = 0;
        foreach ($workOrderList as $key => $value) {
            $sort = 0;
            if (substr($value['工单编号'], 0, 1) === 'Y') {
                $sort = 1;
            } else if (substr($value['工单编号'], -2) === 'JZ' || substr($value['工单编号'], -2) === 'JS') {
                $sort = 2;
            }
            $code = substr($value['成品编号'], 0, 5);
            $client = \db('物料_存货结构')->where('编号', $code)->value('名称');
            //插入产品资料
            $processData = \db('产品_基本资料')
                ->where('产品编号', $value['成品编号'])
                ->count();
            $detail = \db('物料_存货编码')->where('物料代码', $value['成品编号'])->find();
            if ($processData === 0) {
                $product = [
                    '客户编号' => $code,
                    '客户名称' => $client,
                    '产品编号' => $value['成品编号'],
                    '产品名称' => $value['成品名称'],
                    '计量单位' => $detail['领用单位'],
                    '状态' => '',
                    'U8UID' => $detail['U8UID'],
                    '产品类别' => $detail['规格'],
                    'Sys_id' => '[272/超级用户]',
                    'Sys_rq' => date('Y-m-d H:i:s', time()),
                    'Mod_rq' => date('Y-m-d H:i:s', time()),
                ];
                $productSql = \db('产品_基本资料')->fetchSql(true)->insert($product);
                \db()->query($productSql);
            }
            //插入工单资料
            if (substr($value['工单编号'], 0, 1) === 'Y' || substr($value['工单编号'], -2) === 'JZ' || substr($value['工单编号'], -2) === 'JS') {
                $data = [
                    'Gd_gdbh' => $value['工单编号'],
                    '行号' => $value['行号'],
                    'Gd_客户代号' => $code,
                    'Gd_客户名称' => $client,
                    'Gd_khdh' => $value['客户编号'],
                    'Gd_khmc' => $value['客户名称'],
                    '客户料号' => $value['客户料号'],
                    '成品代号' => $value['成品编号'],
                    '成品名称' => $value['成品名称'],
                    'Gd_cpdh' => $value['产品编号'],
                    'Gd_cpmc' => $value['产品名称'],
                    '产品版本号' => $value['版本号'],
                    '销售订单号' => $value['销售订单号'],
                    '警语版面' => $value['警语版面'],
                    '码源数量' => substr(str_replace('.', '', $value['投料数量']), 0, -2),
                    'Gd_desc' => $value['备注'],
                    '接单日期' => $value['接单日期'],
                    '开单日期' => $value['开单日期'],
                    '交货日期' => $value['交货日期'],
                    '订单数量' => $value['订单数量'],
                    '实际投料' => $value['投料数量'],
                    '产品单价' => $value['产品单价'],
                    '计量单位' => '万张',
                    '成本考核_胶印' => 1,
                    '成本考核_凹印' => 1,
                    '成本考核_丝印' => 1,
                    '成本考核_模切' => 1,
                    '成本考核_检验' => 1,
                    'gd_statu' => '3-计划中',
                    'Sys_id' => '[1012/开单员]',
                    'Sys_rq' => date('Y-m-d H:i:s', time()),
                    'Mod_rq' => date('Y-m-d H:i:s', time()),
                    'U8UID' => $value['U8_UID'],
                    '工单分类' => $sort
                ];
                //获取版本号
                $banben = \db('工单_基本资料')
                    ->where('Gd_cpdh',$data['Gd_cpdh'])
                    ->order('Sys_rq desc')
                    ->column('产品版本号');
                if ($banben){
                    $data['产品版本号']  = $banben[0];
                }
                //插入数据
                $workOrderdata = \db('工单_基本资料')
                    ->where('U8UID', $value['U8_UID'])
                    ->count();
                if ($workOrderdata === 0) {
                    $addSql = \db('工单_基本资料')->fetchSql(true)->insert($data);
                    $result = \db()->query($addSql);
                    if ($result === false) {
                        $m++;
                    } else {
                        $sqlString = $db3->name('U8_06工单资料')
                            ->where('UniqId', $value['UniqId'])
                            ->fetchSql(true)
                            ->update([
                                'MES接收时间' => date('Y-m-d H:i:s', time()),
                                'MES接收状态' => '1'
                            ]);
                        $db3->execute($sqlString);
                    }
                } else {
                    unset($data['Uniqid']);
                    unset($data['行号']);
                    $updateSql = \db('工单_基本资料')->where('U8UID', $value['U8_UID'])->fetchSql(true)->update($data);
                    $updateRes = \db()->query($updateSql);
                    if ($updateRes === false) {
                        $j++;
                    } else {
                        $sqlString = $db3->name('U8_06工单资料')
                            ->where('UniqId', $value['UniqId'])
                            ->fetchSql(true)
                            ->update([
                                'MES接收时间' => date('Y-m-d H:i:s', time()),
                                'MES接收状态' => '1'
                            ]);
                        $db3->execute($sqlString);
                    }
                }
            }
        }
        if ($j !== 0 || $m !== 0 || $n !== 0) {
            $this->error('工单资料同步失败');
        } else {
            $this->success('工单资料同步成功');
        }
    }

    /**
     * 工单bom资料同步
     */

    public function WorkOrderBomData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db3 = Db::connect(config('database.db3'));
        $BomDataList = $db3->name('U8_09工单bom')
            ->where('MES接收时间', null)
            ->where('MES接收状态', '0')
            ->order('U8传递时间 desc')
            ->select();
        if (empty($BomDataList)) {
            $this->success('未找到新工单BOM');
        }
        $j = $m = 0;
        foreach ($BomDataList as $key => $value) {
            $res = \db('工单_bom资料')->where('U8UID', $value['U8_UID'])->count();
            if (preg_match("/[A-Za-z]/", $value['BOM_工单编号'])) {
                $workcode = $value['BOM_工单编号'];
            } else {
                $workcode = 'Y' . $value['BOM_工单编号'];
            }
            $data = [
                'BOM_方案' => '工单评审定案',
                'BOM_工单编号' => $workcode,
                'BOM_版本' => $value['BOM_版本'],
                'BOM_工单行号' => $value['BOM_工单行号'],
                'BOM_行号' => $value['BOM_行号'],
                'BOM_产品编号' => $value['BOM_产品编号'],
                'BOM_物料编码' => $value['BOM_物料编码'],
                'BOM_物料名称' => $value['BOM_物料名称'],
                'BOM_投料单位' => $value['BOM_投料单位'],
                'BOM_投入数' => $value['BOM_投入数'],
                'BOM_产出数' => $value['BOM_产出数'],
                'BOM_产出单位' => $value['BOM_产出单位'],
                'BOM_标准用量' => $value['BOM_标准用量'],
                'BOM_实际用量' => $value['BOM_实际用量'],
                'BOM_计划用量' => $value['BOM_计划用量'],
                'BOM_核算价格' => 0,
                'Bom_领用工序' => '01-01',
                'BOM_备注' => $value['BOM_备注'],
                'Mod_rq' => date('Y-m-d H:i:s', time()),
                'U8UID' => $value['U8_UID'],
            ];
            if ($res === 0) {
                $addSql = \db('工单_bom资料')->fetchSql(true)->insert($data);
                $addRes = \db()->query($addSql);
                if ($addRes === false) {
                    $m++;
                } else {
                    $sqlString = $db3->name('U8_09工单bom')
                        ->where('UNIQID', $value['UNIQID'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            } else {
                unset($data['UNIQID']);
                $updateSql = \db('工单_bom资料')
                    ->where('U8UID', $value['U8_UID'])
                    ->fetchSql(true)
                    ->update($data);
                $updateRes = \db()->query($updateSql);
                if ($updateRes === false) {
                    $j++;
                } else {
                    $sqlString = $db3->name('U8_09工单bom')
                        ->where('UNIQID', $value['UNIQID'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            }
        }
        if ($j !== 0 || $m !== 0) {
            $this->error('工单BOM资料同步失败');
        } else {
            $this->success('工单BOM资料同步成功');
        }
    }

    /**
     * 存货结构数据同步
     */

    public function MaterialRequisitionData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db3 = \db()->connect(config('database.db3'));
        $MaterialDataList = $db3->name('U8_11存货结构')
            ->where('MES接收时间', null)
            ->where('MES接收状态', '0')
            ->select();
        if (empty($MaterialDataList)) {
            $this->success('未找到新的物料存货结构');
        }
        $i = 0;
        foreach ($MaterialDataList as $key => $value) {
            $data = [
                '编号' => $value['编号'],
                '名称' => $value['名称'],
                'Sys_id' => '[272/超级用户]',
                'Sys_rq' => date('Y-m-d H:i:s', time()),
                'Mod_rq' => date('Y-m-d H:i:s', time()),
                'U8UID' => $value['U8_UID'],
            ];
            if ($value['U8插入类型'] === '新增') {
                $sql = \db('物料_存货结构')->fetchSql(true)->insert($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db3->name('U8_11存货结构')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            } else {
                $sql = \db('物料_存货结构')
                    ->fetchSql(true)
                    ->where('U8UID', $value['U8_UID'])
                    ->update($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {

                    $sqlString = $db3->name('U8_11存货结构')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);

                }
            }
        }
        if ($i !== 0) {
            $this->error('存货结构同步失败');
        } else {
            $this->success('存货结构同步成功');
        }
    }

    /**
     * 人事基本资料同步
     * @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 PersonnelData()
    {
        //拼音类
        $pinyin = new Pinyin\Pinyin();
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db3 = \db()->connect(config('database.db3'));
        $PersonnelDataList = $db3->name('U8_02人事资料')
            ->where('MES接收时间', null)
            ->where('MES接收状态', '0')
            ->select();
        if (empty($PersonnelDataList)) {
            $this->success('未找到新的人事资料');
        }
        $i = 0;
        foreach ($PersonnelDataList as $key => $value) {
            //获取姓名首字母
            $pycode = $pinyin->abbr($value['员工姓名']);
            $nameCode = strtoupper($pycode);
            $data = [
                '工卡编号' => '',
                '卡钟设定' => '',
                '打卡设置' => '',
                '员工编号' => $value['员工编号'],
                '员工姓名' => $value['员工姓名'],
                '性别' => $value['性别'],
                '聘用日期' => $value['聘用日期'],
                '转正日期' => $value['转正日期'],
                'U8离职日期' => $value['离职日期'],
                'MES离职日期' => $value['离职日期'],
                '扣减司龄' => 0,
                '部门编码' => $value['部门编码'],
                '所在部门' => $value['所在部门'],
                '人员类别' => $value['人员类别'],
                '人员性质' => $value['人员性质'],
                '班次类型' => '',
                '标准工时制' => '',
                '职称职务' => $value['职称职务'],
                '薪资级别' => $value['级别'],
                '工资表类别' => '',
                '基本工资' => '',
                '绩效工资1' => '',
                '绩效工资2' => '',
                '技能工资' => '',
                '岗位津贴' => '',
                '竞业补贴' => '',
                '专业技术津贴' => '',
                '技工技师津贴' => '',
                '特殊工种津贴' => '',
                '各类奖项津贴' => '',
                '职危津贴' => '',
                '夜班津贴' => '',
                '全勤津贴' => '',
                '住房津贴' => '',
                '高温津贴' => '',
                '用餐津贴' => '',
                '司龄津贴' => '',
                '联系电话' => $value['联系电话'],
                '合同类型' => '',
                '合同起始日期' => '1900-01-01 00:00:00',
                '合同终止日期' => '1900-01-01 00:00:00',
                '合同备注' => '',
                '出生日期' => $value['出生日期'],
                'pycode' => $nameCode,
                '籍贯' => '',
                '民族' => '',
                '身份证号' => $value['身份证号'],
                '证件有效日期' => '1900-01-01 00:00:00',
                '发证机关' => '',
                '家庭住址' => '',
                '学历' => '',
                '婚姻状况' => '',
                '社保开始日期' => '1900-01-01 00:00:00',
                '开户银行' => $value['开户银行'],
                '开户账号' => $value['开户账号'],
                '存折办理日期' => '1900-01-01 00:00:00',
                '紧急电话' => '',
                '照片ID' => '',
                '在职状态' => $value['在职状态'],
                'U8在职' => $value['在职状态'],
                '薪酬核算分组' => '',
                '考勤类型' => '',
                '班组代号' => '',
                'sys_id' => '[272/超级用户]',
                'sys_rq' => date('Y-m-d H:i:s', time()),
                'mod_rq' => '1900-01-01 00:00:00',
                'U8UID' => $value['U8_UID'],
            ];
            $number = \db('人事_基本资料')
                ->where('U8UID', $value['U8_UID'])
                ->count();
            if ($number === 0) {
                $sql = \db('人事_基本资料')->fetchSql(true)->insert($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db3->name('U8_02人事资料')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            } else {
                $sql = \db('人事_基本资料')
                    ->fetchSql(true)
                    ->where('U8UID', $value['U8_UID'])
                    ->update($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {

                    $sqlString = $db3->name('U8_02人事资料')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);

                }
            }
        }
        if ($i !== 0) {
            $this->error('人事资料同步失败');
        } else {
            $this->success('人事资料同步成功');
            //14906570@qq.com
        }
    }

    /**
     * 人事组织结构
     * @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 OrganizationalStructureData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db3 = \db()->connect(config('database.db3'));
        $OrganizationalDataList = $db3->name('U8_01组织结构')
            ->where('MES接收时间', null)
            ->where('MES接收状态', '0')
            ->select();
        if (empty($OrganizationalDataList)) {
            $this->success('未找到新的组织结构');
        }
        $i = 0;
        foreach ($OrganizationalDataList as $key => $value) {
            $data = [
                '编号' => $value['编号'],
                '名称' => $value['名称'],
                '状态' => $value['状态'],
                'Sys_id' => '[272/超级用户]',
                'Sys_rq' => date('Y-m-d H:i:s', time()),
                'Mod_rq' => '1900-01-01 00:00:00',
                'U8UID' => $value['U8_UID'],
            ];
            $number = \db('人事_组织结构')
                ->where('U8UID', $value['U8_UID'])
                ->count();
            if ($number === 0) {
                $sql = \db('人事_组织结构')->fetchSql(true)->insert($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db3->name('U8_01组织结构')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            } else {
                $sql = \db('人事_组织结构')
                    ->fetchSql(true)
                    ->where('U8UID', $value['U8_UID'])
                    ->update($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {

                    $sqlString = $db3->name('U8_01组织结构')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);

                }
            }
        }
        if ($i !== 0) {
            $this->error('人事组织结构同步失败');
        } else {
            $this->success('人事组织结构同步成功');
        }
    }

    /**
     * 物料存货编码数据同步
     * @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 InventoryCodeData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db3 = \db()->connect(config('database.db3'));
        $OrganizationalDataList = $db3->name('U8_04物料编码')
            ->where('MES接收时间', null)
            ->where('MES接收状态', '0')
            ->select();
        if (empty($OrganizationalDataList)) {
            $this->success('未找到新的物料编码');
        }
        $i = 0;
        foreach ($OrganizationalDataList as $key => $value) {
            $code = substr($value['物料代码'], 0, 3);
            if ($code === 'Y10' || $code === 'Y14' || $code === 'Y12' || $code === 'J00' || $code === 'J01' || $code === 'J02') {
                //插入产品资料
                $productData = \db('产品_基本资料')
                    ->where('产品编号', $value['物料代码'])
                    ->count();
                $clientCode = substr($value['物料代码'], 0, 5);
                $clientName = \db('物料_存货结构')->where('编号', $clientCode)->value('rtrim(名称)');
                $product = [
                    '客户编号' => $clientCode,
                    '客户名称' => $clientName,
                    '产品编号' => $value['物料代码'],
                    '产品名称' => $value['物料名称'],
                    '计量单位' => $value['领用单位'],
                    '状态' => '',
                    'U8UID' => $value['U8_UID'],
                    '产品类别' => $value['规格'],
                    'Sys_id' => '[272/超级用户]',
                    'Sys_rq' => date('Y-m-d H:i:s', time()),
                    'Mod_rq' => date('Y-m-d H:i:s', time()),
                ];
                if ($productData === 0) {
                    $productSql = \db('产品_基本资料')->fetchSql(true)->insert($product);
                } else {
                    $productSql = \db('产品_基本资料')->where('产品编号', $value['物料代码'])->fetchSql(true)->update($product);
                }
                $productRes = \db()->query($productSql);
            }
            $data = [
                '物料代码' => $value['物料代码'],
                '物料名称' => $value['物料名称'],
                '规格' => $value['规格'],
                '采购单位' => $value['采购单位'],
                '领用单位' => $value['领用单位'],
                '单位换算率' => $value['单位换算率'],
                '单价' => $value['单价'],
                '币种' => $value['币种'],
                '物料备注' => $value['物料备注'],
                '状态' => $value['状态'],
                'Sys_id' => '[272/超级用户]',
                'Sys_rq' => date('Y-m-d H:i:s', time()),
                'Mod_rq' => '1900-01-01 00:00:00',
                'U8UID' => $value['U8_UID'],
            ];
            $number = \db('物料_存货编码')
                ->where('U8UID', $value['U8_UID'])
                ->count();
            if ($number === 0) {
                $sql = \db('物料_存货编码')->fetchSql(true)->insert($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db3->name('U8_04物料编码')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            } else {
                $sql = \db('物料_存货编码')
                    ->fetchSql(true)
                    ->where('U8UID', $value['U8_UID'])
                    ->update($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db3->name('U8_04物料编码')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            }
        }
        if ($i !== 0) {
            $this->error('物料编码同步失败');
        } else {
            $this->success('物料编码同步成功');
        }
    }

    /**
     * 客户供应商数据同步
     * @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 CustomerSupplierData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db3 = \db()->connect(config('database.db3'));
        $OrganizationalDataList = $db3->name('U8_03客户供应商')
            ->where('MES接收时间', null)
            ->where('MES接收状态', '0')
            ->select();
        if (empty($OrganizationalDataList)) {
            $this->success('未找到新的客户供应商');
        }
        $i = 0;
        foreach ($OrganizationalDataList as $key => $value) {
            $data = [
                '类型' => $value['类型'],
                '编号' => $value['编号'],
                '名称' => $value['名称'],
                '简称' => $value['简称'],
                '地址' => $value['地址'],
                '对口部门' => $value['对口部门'],
                '联系人' => $value['联系人'],
                '电话' => $value['电话'],
                '业务员' => $value['业务员'],
                '币种' => $value['币种'],
                'Sys_id' => '[272/超级用户]',
                'Sys_rq' => date('Y-m-d H:i:s', time()),
                'Mod_rq' => '1900-01-01 00:00:00',
                'U8UID' => $value['U8_UID'],
                'UniqId' => $value['UniqId'],
            ];
            $number = \db('erp_客户供应商')
                ->where('U8UID', $value['U8_UID'])
                ->count();
            if ($number === 0) {
                $sql = \db('erp_客户供应商')->fetchSql(true)->insert($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db3->name('U8_03客户供应商')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            } else {
                $sql = \db('erp_客户供应商')
                    ->fetchSql(true)
                    ->where('U8UID', $value['U8_UID'])
                    ->update($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db3->name('U8_03客户供应商')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            }
        }
        if ($i !== 0) {
            $this->error('客户供应商同步失败');
        } else {
            $this->success('客户供应商同步成功');
        }
    }

    /**
     * 物料领用记录数据同步
     * @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 ReceiptRecordData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db3 = \db()->connect(config('database.db3'));
        $OrganizationalDataList = $db3->name('U8_07物料领用')
            ->where('MES接收时间', null)
            ->where('MES接收状态', '0')
            ->select();
        if (empty($OrganizationalDataList)) {
            $this->success('未找到新的物料领用记录');
        }
        $i = 0;
        foreach ($OrganizationalDataList as $key => $value) {
            if (preg_match("/[A-Za-z]/", $value['工单编号'])) {
                $workcode = $value['工单编号'];
            } else {
                $workcode = 'Y' . $value['工单编号'];
            }
            $data = [
                'st_rq' => $value['日期'],
                'st_jylb' => $value['交易类别'],
                'st_gdbh' => $workcode,
                '采购单号' => $value['采购单号'],
                '供方批次' => $value['供方批次'],
                'st_wlbh' => $value['物料编号'],
                'st_sl' => $value['领用数量'],
                'st_oldSl' => $value['领用数量'],
                'st_dw' => $value['领用单位'],
                '领用单价' => $value['领用单价'],
                'st_desc' => $value['备注'],
                '仓库编号' => $value['仓库编号'],
                'st_dpt' => $value['采购单号'],
                'st_jtbh' => $value['机台编号'],
                'sys_id' => '[272/超级用户]',
                'sys_rq' => date('Y-m-d H:i:s', time()),
                'mod_rq' => '1900-01-01 00:00:00',
                'U8UID' => $value['U8_UID'],
                'cpdh' => $value['产品编码'],
            ];
            $number = \db('物料_收发记录')
                ->where('U8UID', $value['U8_UID'])
                ->count();
            if ($number === 0) {
                $sql = \db('物料_收发记录')->fetchSql(true)->insert($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $logData = [
                        'Gd_gdbh'      => $data['st_gdbh'], // 使用原始工单编号
                        'ModifyUser'   => '系统同步',
                        'ModifyTime'   => date('Y-m-d H:i:s'),
                        'FieldName'    => $data['U8UID'],
                        'OldValue'     => $data['st_sl'],
                        'NewValue'     => $data['仓库编号'],
                        'ModifySource' => '物料收发记录同步'
                    ];
                    $logSql = \db('系统操作日志表')->fetchSql(true)->insert($logData);
                    $result = \db()->query($logSql);
                    $sqlString = $db3->name('U8_07物料领用')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            } else {
                $oldData = \db('物料_收发记录')
                    ->where('U8UID', $value['U8_UID'])
                    ->select();
                $changes = [];
                foreach ($data as $field => $newValue) {
                    $oldValue = $oldData[$field] ?? null;
                    if ($oldValue != $newValue) {
                        $changes[] = [
                            'field' => $field,
                            'old'   => $oldValue,
                            'new'   => $newValue
                        ];
                    }
                }
                $sql = \db('物料_收发记录')
                    ->fetchSql(true)
                    ->where('U8UID', $value['U8_UID'])
                    ->update($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    if (!empty($changes)) {
                        $logData = [];
                        foreach ($changes as $change) {
                            $logData[] = [
                                'Gd_gdbh'      => $data['st_gdbh'], // 使用原始工单编号
                                'ModifyUser'   => '系统同步',
                                'ModifyTime'   => date('Y-m-d H:i:s'),
                                'FieldName'    => $change['field'],
                                'OldValue'     => $change['old'],
                                'NewValue'     => $change['new'],
                                'ModifySource' => '物料收发记录修改数据同步'
                            ];
                        }
                        $logSql = \db('系统操作日志表')->fetchSql(true)->insertAll($logData);
                        $result = \db()->query($logSql);
                    }
                    $sqlString = $db3->name('U8_07物料领用')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            }
        }
        if ($i !== 0) {
            $this->error('物料领用记录同步失败');
        } else {
            $this->success('物料领用记录同步成功');
        }
    }

    /**
     * 仓库信息数据同步
     * @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 WarehouseInformationData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db3 = \db()->connect(config('database.db3'));
        $OrganizationalDataList = $db3->name('U8_12仓库信息')
            ->where('MES接收时间', null)
            ->where('MES接收状态', '0')
            ->select();
        if (empty($OrganizationalDataList)) {
            $this->success('未找到新的仓库信息');
        }
        $i = 0;
        foreach ($OrganizationalDataList as $key => $value) {
            $data = [
                '编号' => $value['编号'],
                '名称' => $value['名称'],
                'Sys_id' => '[272/超级用户]',
                'Sys_rq' => date('Y-m-d H:i:s', time()),
                'Mod_rq' => '1900-01-01 00:00:00',
                'U8UID' => $value['U8_UID'],
            ];
            $number = \db('物料_仓库信息')
                ->where('U8UID', $value['U8_UID'])
                ->count();
            if ($number === 0) {
                $sql = \db('物料_仓库信息')->fetchSql(true)->insert($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db3->name('U8_12仓库信息')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            } else {
                $sql = \db('物料_仓库信息')
                    ->fetchSql(true)
                    ->where('U8UID', $value['U8_UID'])
                    ->update($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db3->name('U8_12仓库信息')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            }
        }
        if ($i !== 0) {
            $this->error('仓库信息同步失败');
        } else {
            $this->success('仓库信息同步成功');
        }
    }

    /**
     * 职位编码数据同步
     * @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 PositionData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db3 = \db()->connect(config('database.db3'));
        $OrganizationalDataList = $db3->name('U8_13职位编码')
            ->where('MES接收时间', null)
            ->where('MES接收状态', '0')
            ->select();
        if (empty($OrganizationalDataList)) {
            $this->success('未找到新的职位编码');
        }
        $i = 0;
        foreach ($OrganizationalDataList as $key => $value) {
            $data = [
                '职位编码' => $value['职位编码'],
                '职位名称' => $value['职位名称'],
                '定编人数' => 0,
                '备注说明' => '',
                'sys_id' => '[272/超级用户]',
                'sys_rq' => date('Y-m-d H:i:s', time()),
                'Mod_rq' => '1900-01-01 00:00:00',
                'U8UID' => $value['U8_UID'],
            ];
            $number = \db('人事_职位编码')
                ->where('U8UID', $value['U8_UID'])
                ->count();
            if ($number === 0) {
                $sql = \db('人事_职位编码')->fetchSql(true)->insert($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db3->name('U8_13职位编码')
                        ->where('UniqID', $value['UniqID'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            } else {
                $sql = \db('人事_职位编码')
                    ->fetchSql(true)
                    ->where('U8UID', $value['U8_UID'])
                    ->update($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db3->name('U8_13职位编码')
                        ->where('UniqID', $value['UniqID'])
                        ->fetchSql(true)
                        ->update([
                            'MES接收时间' => date('Y-m-d H:i:s', time()),
                            'MES接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            }
        }
        if ($i !== 0) {
            $this->error('职位编码同步失败');
        } else {
            $this->success('职位编码同步成功');
        }
    }

    /**
     * 设备产量计酬云中间表数据库同步
     * @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 ProductionData()
//    {
//        if ($this->request->isGet() === false) {
//            $this->error('请求错误');
//        }
//        if (is_dir(ROOT_PATH . 'public/' . date('Y-m-d', time())) == null) {
//            mkdir(ROOT_PATH . 'public/' . date('Y-m-d', time()), 0777, true);
//        }
//        //查询翌星数据库美浓设备编号
//        $machineList = \db('设备_基本资料')
//            ->where('mn_设备编号', '<>', '')
////            ->where('sys_sbID', '<>', '')
//            ->column('mn_设备编号');
////        halt($machineList);
////        $machineList = ['JP17#','MQ02#','PM04#','SY03#','TJ03#','TJ05#','TJ07#'];
//        //链接美浓mes数据库
//        $db5 = \db()->connect(config('database.db5'));
//        //获取缓存中最后的id
//        $lastId = cache('MnLastId');
//        //确定美浓设备计酬的查询范围
//        if ($lastId === false) {
//            $where = [
//                'sczl_rq' => ['between', ['2024-09-01 00:00:00', '2024-09-30 00:00:00']],
//                'sczl_jtbh' => ['in', $machineList]
//            ];
//        } else {
//            $where = [
//                'sczl_rq' => ['between', ['2024-09-01 00:00:00', '2024-09-30 00:00:00']],
//                'sczl_jtbh' => ['in', $machineList],
//                'UniqId' => ['>', $lastId]
//            ];
//        }
//        //查询美浓数据库时间范围内的产量上报记录
//        $productData = $db5->name('设备_产量计酬')
//            ->where($where)
//            ->limit(5000)
//            ->order('UniqId')
//            ->select();
//        if (!empty($productData)) {
//            cache('MnLastId', end($productData)['UniqId']);
//        }
//        //获取美浓和翌星设备编号对照表
//        $machineData = \db('设备_基本资料')
//            ->alias('a')
//            ->join('dic_lzde b','a.设备编号 = b.适用机型')
//            ->where('a.mn_设备编号', '<>', '')
////            ->where('sys_sbID', '<>', '')
////            ->field('rtrim(a.设备编号) as 设备编号,rtrim(a.mn_设备编号) as 美浓设备编号,b.sys_bh')
//            ->column('rtrim(a.设备编号) as 设备编号,b.sys_bh','a.mn_设备编号');
////        halt($machineData);
//        //获取员工资料
//        $employee = \db('人事_基本资料')
//            ->where('mn_员工编号', '<>', '')
//            ->column('rtrim(mn_员工编号) as 美浓员工编号');
//        //循环判断机台、员工编号
//        $j = 0;
//        $data = [];
//        foreach ($productData as $key => $value) {
//            //判断机台编号
//
//                if (array_key_exists($value['sczl_jtbh'],$machineData)) {
//
//                    if (substr($productData[$key]['sczl_jtbh'],0,2) === 'JP'){
//                        if (substr((int)$productData[$key]['sczl_dedh'],-1,1) === '1'){
//                            $productData[$key]['sczl_dedh'] = $machineData[$productData[$key]['sczl_jtbh']]['sys_bh'].'001';
//                        }else{
//                            $productData[$key]['sczl_dedh'] = $machineData[$productData[$key]['sczl_jtbh']]['sys_bh'].'002';
//                        }
//                    }else{
//                        $productData[$key]['sczl_dedh'] = $machineData[$productData[$key]['sczl_jtbh']]['sys_bh'];
//                    }
//                    $productData[$key]['sczl_jtbh'] = $machineData[$productData[$key]['sczl_jtbh']]['设备编号'];
//                }
//                array_push($data, $productData[$key]);
//
//        }
//        if (empty($data)) {
//            $this->error('未找到新的生产数据');
//        }
//        foreach ($data as $key => $value) {
//            $name = [];
//            //判断员工编号,如果不存在写入日志文件
//            for ($i = 1; $i <= 10; $i++) {
//                if (in_array($data[$key]['sczl_bh' . $i], $employee)) {
//                    $data[$key]['sczl_bh' . $i] = \db('人事_基本资料')
//                        ->where('mn_员工编号', $data[$key]['sczl_bh' . $i])
//                        ->value('员工编号');
//                } else {
//                    array_push($name, $value['sczl_bh' . $i]);
//                }
//
//            }
//            unset($data[$key]['UniqId']);
//            $name = array_unique($name);
//            $filename = ROOT_PATH . 'public/' . date('Y-m-d', time()) . '/' . $value['sczl_jtbh'] . '日志文件.txt';
//            $handle = fopen($filename, 'w');
//            foreach ($name as $v) {
//                fwrite($handle, $v);
//            }
//            fclose($handle);
//            //修改工单编号
////            if (preg_match("/[A-Za-z]/",$value['sczl_gdbh'])){
////                $workcode = $value['sczl_gdbh'];
////            }else{
////                $workcode = 'Y'.$value['sczl_gdbh'];
////            }
////            $data[$key]['sczl_gdbh'] = $workcode;
//        }
//        //插入设备产量计酬数据
////        $sql = \db('设备_产量计酬')->where('sczl_rq','between',['2024-09-01 00:00:00', '2024-09-30 00:00:00'])->delete();
//        $sql = \db('设备_产量计酬')->fetchSql(true)->insertAll($data);
//        $res = \db()->query($sql);
//        if ($res !== false) {
//            $this->success('同步成功');
//        } else {
//            $this->error('同步失败');
//        }
//    }


    /**
     * 美浓工单数据同步
     * @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 MNWorkOrderData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db7 = Db::connect(config('database.db7'));
        $workOrderList = $db7->name('U8_06工单资料')
            ->where('YX接收时间', null)
            ->where('YX接收状态', null)
            ->where('U8插入类型', '<>', '关闭')
            ->select();
        if (empty($workOrderList)) {
            $this->success('未获取新工单');
        }
        $j = $m = $n = 0;
        foreach ($workOrderList as $key => $value) {
            $sort = 3;
            $code = substr($value['成品编号'], 0, 4);
            $client = \db('物料_存货结构')->where('编号', $code)->value('名称');
            //插入产品资料
            $processData = \db('产品_基本资料')
                ->where('产品编号', $value['成品编号'])
                ->count();
            $detail = \db('物料_存货编码')->where('物料代码', $value['成品编号'])->find();
            if ($processData === 0) {
                $product = [
                    '客户编号' => $code,
                    '客户名称' => $client,
                    '产品编号' => $value['成品编号'],
                    '产品名称' => $value['成品名称'],
                    '计量单位' => $detail['领用单位'],
                    '状态' => '',
                    'U8UID' => $detail['U8UID'],
                    '产品类别' => $detail['规格'],
                    'Sys_id' => '[272/超级用户]',
                    'Sys_rq' => date('Y-m-d H:i:s', time()),
                    'Mod_rq' => date('Y-m-d H:i:s', time()),
                ];
                $productSql = \db('产品_基本资料')->fetchSql(true)->insert($product);
                \db()->query($productSql);
            }
            //插入工单资料

            $data = [
                'Gd_gdbh' => $value['工单编号'],
                '行号' => $value['行号'],
                'Gd_客户代号' => $code,
                'Gd_客户名称' => $client,
                'Gd_khdh' => $value['客户编号'],
                'Gd_khmc' => $value['客户名称'],
                '客户料号' => $value['客户料号'],
                '成品代号' => $value['成品编号'],
                '成品名称' => $value['成品名称'],
                'Gd_cpdh' => $value['产品编号'],
                'Gd_cpmc' => $value['产品名称'],
                '产品版本号' => $value['版本号'],
                '销售订单号' => $value['销售订单号'],
                '警语版面' => $value['警语版面'],
                '码源数量' => substr(str_replace('.', '', $value['投料数量']), 0, -2),
                'Gd_desc' => $value['备注'],
                '接单日期' => $value['接单日期'],
                '开单日期' => $value['开单日期'],
                '交货日期' => $value['交货日期'],
                '订单数量' => $value['订单数量'],
                '实际投料' => $value['投料数量'],
                '产品单价' => $value['产品单价'],
                '计量单位' => '万张',
                '成本考核_胶印' => 1,
                '成本考核_凹印' => 1,
                '成本考核_丝印' => 1,
                '成本考核_模切' => 1,
                '成本考核_检验' => 1,
                'gd_statu' => '3-计划中',
                'Sys_id' => '[1012/开单员]',
                'Sys_rq' => date('Y-m-d H:i:s', time()),
                'Mod_rq' => date('Y-m-d H:i:s', time()),
                'U8UID' => $value['U8_UID'],
                '工单分类' => $sort
            ];
            //获取版本号
            $banben = \db('工单_基本资料')
                ->where('Gd_cpdh',$data['Gd_cpdh'])
                ->order('Sys_rq desc')
                ->column('产品版本号');
            if ($banben){
                $data['产品版本号']  = $banben[0];
            }
            $workOrderdata = \db('工单_基本资料')
                ->where('U8UID', $value['U8_UID'])
                ->count();
            if ($workOrderdata === 0) {
                $addSql = \db('工单_基本资料')->fetchSql(true)->insert($data);
                $result = \db()->query($addSql);
                if ($result === false) {
                    $m++;
                } else {
                    $sqlString = $db7->name('U8_06工单资料')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'YX接收时间' => date('Y-m-d H:i:s', time()),
                            'YX接收状态' => '1'
                        ]);
                    $db7->execute($sqlString);
                }
            } else {
                unset($data['行号']);
                $updateSql = \db('工单_基本资料')->where('U8UID', $value['U8_UID'])->fetchSql(true)->update($data);
                $updateRes = \db()->query($updateSql);
                if ($updateRes === false) {
                    $j++;
                } else {
                    $sqlString = $db7->name('U8_06工单资料')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'YX接收时间' => date('Y-m-d H:i:s', time()),
                            'YX接收状态' => '1'
                        ]);
                    $db7->execute($sqlString);
                }
            }
        }
        if ($j !== 0 || $m !== 0 || $n !== 0) {
            $this->error('工单资料同步失败');
        } else {
            $this->success('工单资料同步成功');
        }
    }

    /**
     * 美浓工单BOM同步
     * @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 MNWorkOrderBomData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db7 = Db::connect(config('database.db7'));
        $BomDataList = $db7->name('U8_09工单bom')
            ->where('YX接收时间', null)
            ->where('YX接收状态', null)
            ->order('U8传递时间 desc')
            ->select();
        if (empty($BomDataList)) {
            $this->success('未找到新工单BOM');
        }
        $j = $m = 0;
        foreach ($BomDataList as $key => $value) {
            $res = \db('工单_bom资料')->where('U8UID', $value['U8_UID'])->count();
            $data = [
                'BOM_方案' => '工单评审定案',
                'BOM_工单编号' => $value['BOM_工单编号'],
                'BOM_版本' => $value['BOM_版本'],
                'BOM_工单行号' => $value['BOM_工单行号'],
                'BOM_行号' => $value['BOM_行号'],
                'BOM_产品编号' => $value['BOM_产品编号'],
                'BOM_物料编码' => $value['BOM_物料编码'],
                'BOM_物料名称' => $value['BOM_物料名称'],
                'BOM_投料单位' => $value['BOM_投料单位'],
                'BOM_投入数' => $value['BOM_投入数'],
                'BOM_产出数' => $value['BOM_产出数'],
                'BOM_产出单位' => $value['BOM_产出单位'],
                'BOM_标准用量' => $value['BOM_标准用量'],
                'BOM_实际用量' => $value['BOM_实际用量'],
                'BOM_计划用量' => $value['BOM_计划用量'],
                'BOM_核算价格' => 0,
                'Bom_领用工序' => '01-01',
                'BOM_备注' => $value['BOM_备注'],
                'Mod_rq' => date('Y-m-d H:i:s', time()),
                'U8UID' => $value['U8_UID'],
            ];
            if ($res === 0) {
                $addSql = \db('工单_bom资料')->fetchSql(true)->insert($data);
                $addRes = \db()->query($addSql);
                if ($addRes === false) {
                    $m++;
                } else {
                    $sqlString = $db7->name('U8_09工单bom')
                        ->where('UNIQID', $value['UNIQID'])
                        ->fetchSql(true)
                        ->update([
                            'YX接收时间' => date('Y-m-d H:i:s', time()),
                            'YX接收状态' => '1'
                        ]);
                    $db7->execute($sqlString);
                }
            } else {
                unset($data['UNIQID']);
                $updateSql = \db('工单_bom资料')
                    ->where('U8UID', $value['U8_UID'])
                    ->fetchSql(true)
                    ->update($data);
                $updateRes = \db()->query($updateSql);
                if ($updateRes === false) {
                    $j++;
                } else {
                    $sqlString = $db7->name('U8_09工单bom')
                        ->where('UNIQID', $value['UNIQID'])
                        ->fetchSql(true)
                        ->update([
                            'YX接收时间' => date('Y-m-d H:i:s', time()),
                            'YX接收状态' => '1'
                        ]);
                    $db7->execute($sqlString);
                }
            }
        }
        if ($j !== 0 || $m !== 0) {
            $this->error('工单BOM资料同步失败');
        } else {
            $this->success('工单BOM资料同步成功');
        }
    }

    /**
     * 美浓物料存货结构同步
     * @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 MNMaterialRequisitionData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db7 = \db()->connect(config('database.db7'));
        $MaterialDataList = $db7->name('U8_11存货结构')
            ->where('YX接收时间', null)
            ->where('YX接收状态', null)
            ->select();
        if (empty($MaterialDataList)) {
            $this->success('未找到新的物料存货结构');
        }
        $i = 0;
        foreach ($MaterialDataList as $key => $value) {
            $data = [
                '编号' => $value['编号'],
                '名称' => $value['名称'],
                'Sys_id' => '[272/超级用户]',
                'Sys_rq' => date('Y-m-d H:i:s', time()),
                'Mod_rq' => date('Y-m-d H:i:s', time()),
                'U8UID' => $value['U8_UID'],
            ];
            if ($value['U8插入类型'] === '新增') {
                $sql = \db('物料_存货结构')->fetchSql(true)->insert($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db7->name('U8_11存货结构')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'YX接收时间' => date('Y-m-d H:i:s', time()),
                            'YX接收状态' => '1'
                        ]);
                    $db7->execute($sqlString);
                }
            } else {
                $sql = \db('物料_存货结构')
                    ->fetchSql(true)
                    ->where('U8UID', $value['U8_UID'])
                    ->update($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {

                    $sqlString = $db7->name('U8_11存货结构')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'YX接收时间' => date('Y-m-d H:i:s', time()),
                            'YX接收状态' => '1'
                        ]);
                    $db7->execute($sqlString);

                }
            }
        }
        if ($i !== 0) {
            $this->error('存货结构同步失败');
        } else {
            $this->success('存货结构同步成功');
        }
    }

    /**
     * 美浓物料存货编码同步
     * @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 MNInventoryCodeData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db7 = \db()->connect(config('database.db7'));
        $OrganizationalDataList = $db7->name('U8_04物料编码')
            ->where('YX接收时间', null)
            ->where('YX接收状态', null)
            ->select();
        if (empty($OrganizationalDataList)) {
            $this->success('未找到新的物料编码');
        }
        $i = 0;
        foreach ($OrganizationalDataList as $key => $value) {
            $code = substr($value['物料代码'], 0, 2);
            if ($code === '10' || $code === '14' || $code === '12') {
                //插入产品资料
                $productData = \db('产品_基本资料')
                    ->where('产品编号', $value['物料代码'])
                    ->count();
                $clientCode = substr($value['物料代码'], 0, 4);
                $clientName = \db('物料_存货结构')->where('编号', $clientCode)->value('rtrim(名称)');
                $product = [
                    '客户编号' => $clientCode,
                    '客户名称' => $clientName,
                    '产品编号' => $value['物料代码'],
                    '产品名称' => $value['物料名称'],
                    '计量单位' => $value['领用单位'],
                    '状态' => '',
                    'U8UID' => $value['U8_UID'],
                    '产品类别' => $value['规格'],
                    'Sys_id' => '[272/超级用户]',
                    'Sys_rq' => date('Y-m-d H:i:s', time()),
                    'Mod_rq' => date('Y-m-d H:i:s', time()),
                ];
                if ($productData === 0) {
                    $productSql = \db('产品_基本资料')->fetchSql(true)->insert($product);
                } else {
                    $productSql = \db('产品_基本资料')->where('产品编号', $value['物料代码'])->fetchSql(true)->update($product);
                }
                $productRes = \db()->query($productSql);
            }
            $data = [
                '物料代码' => $value['物料代码'],
                '物料名称' => $value['物料名称'],
                '规格' => $value['规格'],
                '采购单位' => $value['采购单位'],
                '领用单位' => $value['领用单位'],
                '单位换算率' => $value['单位换算率'],
                '单价' => $value['单价'],
                '币种' => $value['币种'],
                '物料备注' => $value['物料备注'],
                '状态' => $value['状态'],
                'Sys_id' => '[272/超级用户]',
                'Sys_rq' => date('Y-m-d H:i:s', time()),
                'Mod_rq' => '1900-01-01 00:00:00',
                'U8UID' => $value['U8_UID'],
            ];
            $number = \db('物料_存货编码')
                ->where('U8UID', $value['U8_UID'])
                ->count();
            if ($number === 0) {
                $sql = \db('物料_存货编码')->fetchSql(true)->insert($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db7->name('U8_04物料编码')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'YX接收时间' => date('Y-m-d H:i:s', time()),
                            'YX接收状态' => '1'
                        ]);
                    $db7->execute($sqlString);
                }
            } else {
                $sql = \db('物料_存货编码')
                    ->fetchSql(true)
                    ->where('U8UID', $value['U8_UID'])
                    ->update($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $sqlString = $db7->name('U8_04物料编码')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'YX接收时间' => date('Y-m-d H:i:s', time()),
                            'YX接收状态' => '1'
                        ]);
                    $db7->execute($sqlString);
                }
            }
        }
        if ($i !== 0) {
            $this->error('物料编码同步失败');
        } else {
            $this->success('物料编码同步成功');
        }
    }

    /**
     * 美浓MES产品资料同步
     * @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 minoMesProductData()
//    {
//        if ($this->request->isGet() === false) {
//            $this->error('请求错误');
//        }
//        $db5 = \db()->connect(config('database.db5'));
//        //工单数据查询
////
//
//        $workOrderList = \db('设备_产量计酬')->where('sczl_rq', 'between', ['2024-09-01 00:00:00', '2024-09-30 00:00:00'])->column('DISTINCT(sczl_gdbh) as gdbh');
//
////        $workOrderData = $db5->name('工单_基本资料')->where('Gd_gdbh','in',$workOrderList)->select();
//        $workOrderData = $db5->name('工单_基本资料')->where('Gd_gdbh', 'in', $workOrderList)->column('rtrim(Gd_gdbh) as gdbh');
////        $print = $process = [];
////        foreach ($workOrderData as $key=> $value){
////            unset($workOrderData[$key]['Uniqid']);
//        //删除工单基本资料表中重复工单
////            $workOrderNum = \db('工单_基本资料')->where('Gd_gdbh',$value['Gd_gdbh'])->count();
////            if ($workOrderNum !== 0){
////                \db('工单_基本资料')->where('Gd_gdbh',$value['Gd_gdbh'])->delete();
////            }
//        //查询工单印件表中印件数据
//
////            $printData = $db5->name('工单_印件资料')->where('Yj_Gdbh','in',$workOrderData)->select();
////         \db('工单_印件资料')->where('Yj_Gdbh','in',$workOrderData)->delete();
////            foreach ($printData as $kk=>$vv){
////                unset($printData[$kk]['Uniqid']);
////                array_push($print,$printData[$kk]);
//        //删除工单印件表中重复数据
////                $printNum = \db('工单_印件资料')
////                    ->where('Yj_Gdbh',$vv['Yj_Gdbh'])
////                    ->where('yj_Yjno',$vv['yj_Yjno'])
////                    ->count();
////                if ($printNum !== 0){
////                    \db('工单_印件资料')
////                        ->where('Yj_Gdbh',$vv['Yj_Gdbh'])
////                        ->where('yj_Yjno',$vv['yj_Yjno'])
////                        ->delete();
////                }
//        //查询工单工艺资料表中数据
//        \db('工单_工艺资料')
//            ->where('Gy0_gdbh', 'in', $workOrderData)
//            ->delete();
//        $processData = $db5->name('工单_工艺资料')
//            ->where('Gy0_gdbh', 'in', $workOrderData)
//            ->select();
////                \db('工单_工艺资料')
////                    ->where('Gy0_gdbh',$value['Gd_gdbh'])
////                    ->delete();
//        foreach ($processData as $k => $v) {
//            unset($processData[$k]['UniqId']);
////                    array_push($process,$processData[$k]);
////        }
//
////            }
//        }
////        halt($workOrderData);
////        $workSql = \db('工单_基本资料')->fetchSql(true)->insertAll($workOrderData);
////        $workRes = \db()->query($workSql);
////        die;
////        halt($printData);
////        $printSql = \db('工单_印件资料')->fetchSql(true)->insertAll($printData);
////        $printRes = \db()->query($printSql);
////        die;
////        halt($processData);
//        $processSql = \db('工单_工艺资料')->fetchSql(true)->insertAll($processData);
//        $processRes = \db()->query($processSql);
////        die;
//        if ($workRes !== false && $printRes !== false && $processRes !== false) {
//            $this->success('成功');
//        } else {
//            $this->error('失败');
//        }
//    }


    //工单核检单同步
//    public function hejiandancopy()
//    {
////        $param = $this->request->param();
//        $db5 = \db()->connect(config('database.db5'));
//        //查询本月工单核检单数据
//        $list = $db5->name('db_qczl')
//            ->where('sys_rq', '>=', '2024-11-21 00:00:00')
//            ->select();
//        $employeeList = \db('人事_基本资料')
//            ->whereNotNull('mn_员工编号')
//            ->column('员工编号', 'mn_员工编号');
//        foreach ($list as $key => $value) {
//            for ($i = 1; $i < 14; $i++) {
//                if ($value['fp_bh' . $i] !== '') {
//                    if (array_key_exists($value['fp_bh' . $i], $employeeList)) {
//                        $list[$key]['fp_bh' . $i] = $employeeList[$value['fp_bh' . $i]];
//                    }
//                }
//            }
//            unset($list[$key]['UniqId']);
//        }
//        $sql = \db('db_qczl')->fetchSql(true)->insertAll($list);
//        $res = \db()->query($sql);
//        if ($res !== false) {
//            $this->success('成功');
//        } else {
//            $this->error('失败');
//        }
//    }


    //拆片工序数据同步
//    public function chaipianCopy()
//    {
//        if ($this->request->isGet() === false) {
//            $this->error('请求错误');
//        }
//        $param = $this->request->param();
//        if (!empty($param['end'])) {
//            $where['sczl_rq'] = ['between', [$param['start'], $param['end']]];
//        } else {
//            $where['sczl_rq'] = ['>=', $param['start']];
//        }
//        $db5 = \db()->connect(config('database.db5'));
//        $list = $db5->name('db_sczl')
//            ->where($where)
//            ->select();
//        $employeeList = \db('人事_基本资料')
//            ->whereNotNull('mn_员工编号')
//            ->column('员工编号', 'mn_员工编号');
//        foreach ($list as $key => $value) {
//            unset($list[$key]['UniqId']);
//            if (array_key_exists($value['sczl_bh1'], $employeeList)) {
//                $list[$key]['sczl_bh1'] = $employeeList[$value['sczl_bh1']];
//            }
//        }
//        $sql = \db('db_sczl')->fetchSql(true)->insertAll($list);
//        $res = \db()->query($sql);
//        if ($res !== false) {
//            $this->success('成功');
//        } else {
//            $this->error('失败');
//        }
//    }

    //手工检验报工数据同步
//    public function shougongCopy()
//    {
//        if ($this->request->isGet() === false) {
//            $this->error('请求错误');
//        }
//        $param = $this->request->param();
//        if (!empty($param['end'])) {
//            $where['sczl_rq'] = ['between', [$param['start'], $param['end']]];
//        } else {
//            $where['sczl_rq'] = ['>=', $param['start']];
//        }
//        $db5 = \db()->connect(config('database.db5'));
//        $list = $db5->name('db_手工检验')
//            ->where($where)
//            ->select();
//        $employeeList = \db('人事_基本资料')
//            ->whereNotNull('mn_员工编号')
//            ->column('员工编号', 'mn_员工编号');
//        foreach ($list as $key => $value) {
//            for ($i = 0; $i < 13; $i++) {
//                if ($value['sczl_bh' . $i] !== '') {
//                    if (array_key_exists($value['sczl_bh' . $i], $employeeList)) {
//                        $list[$key]['sczl_bh' . $i] = $employeeList[$value['sczl_bh' . $i]];
//                    }
//                }
//            }
//            unset($list[$key]['UniqId']);
//        }
//        $sql = \db('db_手工检验')->fetchSql(true)->insertAll($list);
//        $res = \db()->query($sql);
//        if ($res !== false) {
//            $this->success('成功');
//        } else {
//            $this->error('失败');
//        }
//    }

    //包装计件数据同步
//    public function baozhaungCopy()
//    {
//        if ($this->request->isGet() === false) {
//            $this->error('请求错误');
//        }
//        $param = $this->request->param();
//        if (!empty($param['end'])) {
//            $where['sczl_rq'] = ['between', [$param['start'], $param['end']]];
//        } else {
//            $where['sczl_rq'] = ['>=', $param['start']];
//        }
//        $db5 = \db()->connect(config('database.db5'));
//        $list = $db5->name('db_包装计件')
//            ->where($where)
//            ->select();
//        $typeList = $db5->name('物料_存货编码')
//            ->where('物料代码','like','10%')
//            ->whereOr('物料代码','like','12%')
//            ->whereOr('物料代码','like','14%')
//            ->column('rtrim(规格) as 规格','rtrim(物料代码)');
//        $cpdhList = $db5->name('工单_印件资料')
//            ->where('Uniqid', '>', 18000)
//            ->where('yj_cpdh', '<>', '') // 确保 yj_cpdh 不为空
//            ->where('Yj_Gdbh','<>','')
//            ->column('rtrim(yj_cpdh) AS cpdh, rtrim(yj_Yjno) AS yjno', 'CONCAT(rtrim(Yj_Gdbh), "-", rtrim(yj_Yjno))');
//        $employeeList = \db('人事_基本资料')
//            ->whereNotNull('mn_员工编号')
//            ->column('员工编号', 'mn_员工编号');
//        foreach ($list as $key => $value) {
//            for ($i = 1; $i < 7; $i++) {
//                //循环判断工单编号是否为空，不为空判断物料规格是条盒或小盒，条盒核算参数取1000，小盒核算参数取2500，如果工单编号为空，核算参数为空
//                if ($value['sczl_gdbh' . $i] !== '') {
//                    $yjno = (int)substr($value['sczl_yjGx'.$i],0,2);
//                    $liatKey = $value['sczl_gdbh' . $i].'-'.$yjno;
//                    if (array_key_exists($liatKey,$cpdhList) !== false){
//                        $cpdh = $cpdhList[$liatKey]['cpdh'];
//                        $guige = $typeList[$cpdh];
//                        if ($guige == '小盒'){
//                            $list[$key]['sczl_type'.$i] = '2500';
//                        }else{
//                            $list[$key]['sczl_type'.$i] = '1000';
//                        }
//                    }else{
//                        $list[$key]['sczl_type'.$i] = '1000';
//                    }
//                }else{
//                    $list[$key]['sczl_type'.$i] = '';
//                }
//
//            }
//            if (array_key_exists($value['sczl_bh'], $employeeList)) {
//                $list[$key]['sczl_bh'] = $employeeList[$value['sczl_bh']];
//            }
//            unset($list[$key]['UniqId']);
//        }
//        $sql = \db('db_包装计件')->fetchSql(true)->insertAll($list);
//        $res = \db()->query($sql);
//        if ($res !== false) {
//            $this->success('成功');
//        } else {
//            $this->error('失败');
//        }
//    }

    //计时工资数据同步
//    public function jishiCopy()
//    {
//        if ($this->request->isGet() === false) {
//            $this->error('请求错误');
//        }
//        $param = $this->request->param();
//        if (!empty($param['end'])) {
//            $where['wgjs_rq'] = ['between', [$param['start'], $param['end']]];
//        } else {
//            $where['wgjs_rq'] = ['>=', $param['start']];
//        }
//        $db5 = \db()->connect(config('database.db5'));
//        $list = $db5->name('db_wgjs')
//            ->where($where)
//            ->select();
//        $employeeList = \db('人事_基本资料')
//            ->whereNotNull('mn_员工编号')
//            ->column('员工编号', 'mn_员工编号');
//        foreach ($list as $key => $value) {
//            for ($i = 1; $i < 7; $i++) {
//                if ($value['wgjs_bh' . $i] !== '') {
//                    if (array_key_exists($value['wgjs_bh' . $i], $employeeList)) {
//                        $list[$key]['wgjs_bh' . $i] = $employeeList[$value['wgjs_bh' . $i]];
//                    }
//                }
//            }
//            unset($list[$key]['UniqId']);
//        }
//        $sql = \db('db_wgjs')->fetchSql(true)->insertAll($list);
//        $res = \db()->query($sql);
//        if ($res !== false) {
//            $this->success('成功');
//        } else {
//            $this->error('失败');
//        }
//    }

    /**
     * 美浓MES物料领用记录
     * @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 MNReceiptRecordData()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $db3 = \db()->connect(config('database.db7'));
        $OrganizationalDataList = $db3->name('U8_07物料领用')
            ->where('YX接收时间', null)
            ->where('YX接收状态', null)
            ->where('UniqId','>',80838718)
            ->select();
        if (empty($OrganizationalDataList)) {
            $this->success('未找到新的物料领用记录');
        }
        $i = 0;
        foreach ($OrganizationalDataList as $key => $value) {
            $data = [
                'st_rq' => $value['日期'],
                'st_jylb' => $value['交易类别'],
                'st_gdbh' => $value['工单编号'],
                '采购单号' => $value['采购单号'],
                '供方批次' => $value['供方批次'],
                'st_wlbh' => $value['物料编号'],
                'st_sl' => $value['领用数量'],
                'st_oldSl' => $value['领用数量'],
                'st_dw' => $value['领用单位'],
                '领用单价' => $value['领用单价'],
                'st_desc' => $value['备注'],
                '仓库编号' => $value['仓库编号'],
                'st_dpt' => $value['采购单号'],
                'st_jtbh' => $value['机台编号'],
                'sys_id' => '[272/超级用户]',
                'sys_rq' => date('Y-m-d H:i:s', time()),
                'mod_rq' => '1900-01-01 00:00:00',
                'U8UID' => $value['U8_UID'],
                'cpdh' => $value['产品编码']
            ];
            $number = \db('物料_收发记录')
                ->where('U8UID', $value['U8_UID'])
                ->count();
            if ($number === 0) {
                $sql = \db('物料_收发记录')->fetchSql(true)->insert($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    $logData = [
                        'Gd_gdbh'      => $data['st_gdbh'], // 使用原始工单编号
                        'ModifyUser'   => '系统同步',
                        'ModifyTime'   => date('Y-m-d H:i:s'),
                        'FieldName'    => $data['U8UID'],
                        'OldValue'     => $data['st_sl'],
                        'NewValue'     => $data['仓库编号'],
                        'ModifySource' => '物料收发记录同步'
                    ];
                    $logSql = \db('系统操作日志表')->fetchSql(true)->insert($logData);
                    $result = \db()->query($logSql);
                    $sqlString = $db3->name('U8_07物料领用')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'YX接收时间' => date('Y-m-d H:i:s', time()),
                            'YX接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            } else {
                $oldData = \db('物料_收发记录')
                    ->where('U8UID', $value['U8_UID'])
                    ->select();
                $changes = [];
                foreach ($data as $field => $newValue) {
                    $oldValue = $oldData[$field] ?? null;
                    if ($oldValue != $newValue) {
                        $changes[] = [
                            'field' => $field,
                            'old'   => $oldValue,
                            'new'   => $newValue
                        ];
                    }
                }
                $sql = \db('物料_收发记录')
                    ->fetchSql(true)
                    ->where('U8UID', $value['U8_UID'])
                    ->update($data);
                $res = \db()->query($sql);
                if ($res === false) {
                    $i++;
                } else {
                    if (!empty($changes)) {
                        $logData = [];
                        foreach ($changes as $change) {
                            $logData[] = [
                                'Gd_gdbh'      => $data['st_gdbh'], // 使用原始工单编号
                                'ModifyUser'   => '系统同步',
                                'ModifyTime'   => date('Y-m-d H:i:s'),
                                'FieldName'    => $change['field'],
                                'OldValue'     => $change['old'],
                                'NewValue'     => $change['new'],
                                'ModifySource' => '物料收发记录修改数据同步'
                            ];
                        }
                        $logSql = \db('系统操作日志表')->fetchSql(true)->insertAll($logData);
                        $result = \db()->query($logSql);
                    }
                    $sqlString = $db3->name('U8_07物料领用')
                        ->where('UniqId', $value['UniqId'])
                        ->fetchSql(true)
                        ->update([
                            'YX接收时间' => date('Y-m-d H:i:s', time()),
                            'YX接收状态' => '1'
                        ]);
                    $db3->execute($sqlString);
                }
            }
        }
        if ($i !== 0) {
            $this->error('物料领用记录同步失败');
        } else {
            $this->success('物料领用记录同步成功');
        }
    }
}