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

<?php

namespace app\api\controller;

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

/**
 * 中间表数据同步
 */
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){
            //插入产品资料
            $productData = \db('产品_基本资料')
                ->where('产品编号',$value['成品编号'])
                ->count();
            if ($productData === 0){
                $clientCode = substr($value['成品编号'],0,5);
                $product = [
                    '客户编号' => $clientCode,
                    '客户名称' => $value['客户名称'],
                    '产品编号' => $value['成品编号'],
                    '产品名称' => $value['成品名称'],
                    '计量单位' => '万张',
                    '状态' => '',
                    'Mod_rq' => date('Y-m-d H:i:s',time()),
                    'UniqID' => \db('产品_基本资料')->order('UniqID desc')->value('UniqID')+1,
                ];
                if (strpos($value['成品名称'],'小盒') === false){
                    $product['产品类别'] = '条盒';
                }else{
                    $product['产品类别'] = '小盒';
                }
                $productSql = \db('产品_基本资料')->fetchSql(true)->insert($product);
                $productRes = \db()->query($productSql);
                if ($productRes === false){
                    $n++;
                }
            }
            //插入工单资料
            $client = \db('产品_基本资料')->where('产品编号',$value['成品编号'])->field('rtrim(客户编号) as 客户编号,rtrim(客户名称) as 客户名称')->find();
            $data = [
                'Gd_gdbh' => $value['工单编号'],
                '行号' => $value['行号'],
                'Gd_客户代号' => $client['客户编号'],
                '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'],
                'Uniqid' => $value['UniqId']
            ];
            $workOrderdata = \db('工单_基本资料')
                ->where('Uniqid',$value['UniqId'])
                ->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{
                $updateSql = \db('工单_基本资料')->where('Uniqid',$value['UniqId'])->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('UNIQID',$value['UNIQID'])->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'],
                'UNIQID' => $value['UNIQID']
            ];
            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{
                $updateSql = \db('工单_bom资料')
                    ->where('UNIQID',$value['UNIQID'])
                    ->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'],
                'UniqId' => $value['UniqId']
            ];
            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('UniqId',$value['UniqId'])
                    ->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()
    {
        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){
            $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'=>$value['pycode'],
                '籍贯'=>'',
                '民族'=>'',
                '身份证号'=>$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'],
                'UniqID'=>$value['UniqId']
            ];
            $number = \db('人事_基本资料')
                ->where('Uniqid',$value['UniqId'])
                ->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('UniqId',$value['UniqId'])
                    ->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('人事资料同步成功');
        }
    }

    /**
     * 人事组织结构
     * @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'],
                'UNIQID'=>$value['UniqId'],
            ];
            $number = \db('人事_组织结构')
                ->where('Uniqid',$value['UniqId'])
                ->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('UniqId',$value['UniqId'])
                    ->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('人事组织结构同步成功');
        }
    }
}