产测中箱码、栈板码绑定sn添加日志表记录

zjh

3 天以前 1cb5959adf96911101864488a67e8b31b8de658f

产测中箱码、栈板码绑定sn添加日志表记录

 StandardPda/MES.Service/service/BasicData/SalesDeliveryNoticeManager.cs

@@ -169,6 +169,11 @@
            var single = base.GetSingle(it => it.ErpId == erpDto.ErpID);
            if (single != null) salesOrder.Id = single.Id;

            if (salesOrder.ErpId is null || salesOrder.ErpId=="")
            {
                throw new NotImplementedException(
    $"表头ErpID不能为空");
            }
            return salesOrder;
        }

@@ -264,7 +269,16 @@
                    FLINE_NO = erpDto.FLINE_NO

                };

                if (salesOrderSub.ErpLineId is null || salesOrderSub.ErpLineId=="")
                {
                    throw new NotImplementedException(
    $"明细行ErpID（ERP行ID）不能为空");
                }
                if (salesOrderSub.ErpHeadId is null || salesOrderSub.ErpHeadId == "")
                {
                    throw new NotImplementedException(
    $"明细行EHID（ERP头ID）不能为空");
                }
                var single = _SalesDeliveryDetailManager.GetSingle(it =>
                    it.ErpLineId == salesOrderSub.ErpLineId);
                if (single != null) salesOrderSub.Id = single.Id;

 StandardPda/MES.Service/service/Warehouse/WomdaaManager.cs

@@ -137,77 +137,112 @@
        }

        /// <summary>
        /// 设置中箱码
        /// 设置中箱码（绑定/解绑 SN）
        /// </summary>
        /// <param name="mesMiddleBox"></param>
        /// <returns></returns>
        /// <param name="mesMiddleBox">中箱码及SN数据</param>
        /// <returns>返回执行状态</returns>
        public string[] SetMediumBoxCode(MesMiddleBox mesMiddleBox)
        {
            var sqlHelper = new OracleSQLHelper();
            string[] msg= new string[2];
            string[] msg = new string[2];
            msg[0] = "true";
            //

            var (connection, transaction) = sqlHelper.BeginTransaction();

            try
            {
                if (mesMiddleBox.type.ToUpper() =="A") {
                if (mesMiddleBox.type.ToUpper() == "A") // 绑定
                {
                    if (mesMiddleBox.SnList.Count > 0)
                    {
                        string sql1 = "select count(*) count  from MES_MIDDLE_BOX where MEDIUMBOXCODE='"+ mesMiddleBox.MediumBoxCode + "'";
                        DataTable data= sqlHelper.ExecuteQuery(sql1);
                        if (data.Rows[0]["COUNT"].ToString() != "0") {
                        string sql1 = $"select count(*) count from MES_MIDDLE_BOX where MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";
                        DataTable data = sqlHelper.ExecuteQuery(sql1);

                        if (data.Rows[0]["COUNT"].ToString() != "0")
                        {
                            msg[0] = "false";
                            msg[1] = "该中箱码已经绑定SN数据，如需再次绑定请先解绑！中箱码：" + mesMiddleBox.MediumBoxCode;
                            transaction.Rollback();
                           
                            return msg;
                        }

                        foreach (var item in mesMiddleBox.SnList)
                        {
                            var sql2 = $@"INSERT INTO MES_MIDDLE_BOX
                                    (
                                        ID,
                                        MEDIUMBOXCODE,
                                        LOGIN_ID,
                                        SN_NO,
                                        TICKET_NO,
                                        CREATE_DATE,
                                        CREATED_BY
                                    )
                                    VALUES
                                    (
                                        SEQ_MES_MIDDLE_BOX_ID.NEXTVAL, -- 序列生成主键
                                        '{mesMiddleBox.MediumBoxCode.ToString()}',                 -- 中箱码
                                        '{mesMiddleBox.LoginId}',                       -- 登录号
                                        '{item.SnNo}',                          -- SN号
                                        '{item.TicketNo}',                      -- 工单单号
                                        SYSDATE,                         -- 创建时间
                                        '{mesMiddleBox.LoginId}'                       -- 创建人
                                    )
                                    ";
                            string sqlInsert = $@"
                        INSERT INTO MES_MIDDLE_BOX (
                            ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY
                        ) VALUES (
                            SEQ_MES_MIDDLE_BOX_ID.NEXTVAL,
                            '{mesMiddleBox.MediumBoxCode}',
                            '{mesMiddleBox.LoginId}',
                            '{item.SnNo}',
                            '{item.TicketNo}',
                            SYSDATE,
                            '{mesMiddleBox.LoginId}'
                        )";

                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlInsert);

                            // 日志记录插入：操作类型 0（绑定）
                            string logInsert = $@"
                        INSERT INTO MES_MIDDLE_BOX_LOG (
                            ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY, OPERATION_TYPE
                        ) VALUES (
                            MES_MIDDLE_BOX_LOG_ID.NEXTVAL,
                            '{mesMiddleBox.MediumBoxCode}',
                            '{mesMiddleBox.LoginId}',
                            '{item.SnNo}',
                            '{item.TicketNo}',
                            SYSDATE,
                            '{mesMiddleBox.LoginId}',
                            '0'
                        )";
                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
                        }
                    }
                    else
                    {
                        msg[0] = "false";
                        msg[1] = "SN明细为空，无法绑定！";
                    }
                } else if(mesMiddleBox.type.ToUpper() == "F") {
                    //解绑，根据中箱码全部删除
                    var sql2 = $@"delete MES_MIDDLE_BOX where MEDIUMBOXCODE='{mesMiddleBox.MediumBoxCode}'";
                }
                else if (mesMiddleBox.type.ToUpper() == "F") // 解绑
                {
                    // 查询要解绑的记录
                    string sqlSelect = $@"
                SELECT * FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";

                    sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
                    DataTable dt = sqlHelper.ExecuteQuery(sqlSelect);

                    foreach (DataRow row in dt.Rows)
                    {
                        // 插入解绑日志：操作类型 1（解绑）
                        string logInsert = $@"
                    INSERT INTO MES_MIDDLE_BOX_LOG (
                        ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY, OPERATION_TYPE
                    ) VALUES (
                        MES_MIDDLE_BOX_LOG_ID.NEXTVAL,
                        '{row["MEDIUMBOXCODE"]}',
                        '{row["LOGIN_ID"]}',
                        '{row["SN_NO"]}',
                        '{row["TICKET_NO"]}',
                        SYSDATE,
                        '{mesMiddleBox.LoginId}',
                        '1'
                    )";
                        sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
                    }

                    // 执行解绑（删除）
                    string sqlDelete = $@"DELETE FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";
                    sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlDelete);
                }
                else
                {
                    msg[1] = "SN明细为空，请确认！";
                    msg[0] = "false";
                    msg[1] = "无效的操作类型！";
                }
               

                // 全部成功提交
                transaction.Commit();
            }
            catch (Exception ex)
@@ -226,77 +261,115 @@
        }



        /// <summary>
        /// 设置栈板码（绑定/解绑 SN）
        /// </summary>
        /// <param name="mesPalletBinding">绑定参数</param>
        /// <returns>返回绑定结果</returns>
        public string[] SetStackCode(MesPalletBinding mesPalletBinding)
        {
            var sqlHelper = new OracleSQLHelper();
            string[] msg = new string[2];
            msg[0] = "true";
            //

            var (connection, transaction) = sqlHelper.BeginTransaction();

            try
            {
                //绑定
                if (mesPalletBinding.Type.ToUpper() == "A") {
                // 绑定
                if (mesPalletBinding.Type.ToUpper() == "A")
                {
                    if (mesPalletBinding.SnList.Count > 0)
                    {
                        string sql1 = "select count(*) count  from MES_PALLET_BINDING where STACKCODE='" + mesPalletBinding.StackCode + "'";
                        string sql1 = $"SELECT COUNT(*) COUNT FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
                        DataTable data = sqlHelper.ExecuteQuery(sql1);

                        if (data.Rows[0]["COUNT"].ToString() != "0")
                        {
                            msg[0] = "false";
                            msg[1] = "该栈板码已经绑定SN数据，如需再次绑定请先解绑！栈板码：" + mesPalletBinding.StackCode;
                            transaction.Rollback();

                            return msg;
                        }

                        foreach (var item in mesPalletBinding.SnList)
                        {
                            var sql2 = $@"INSERT INTO MES_PALLET_BINDING
                            (
                                ID,
                                STACKCODE,
                                LOGIN_ID,
                                SN_NO,
                                TICKET_NO,
                                MEDIUMBOXCODE,
                                CREATE_DATE,
                                CREATED_BY
                            )
                            VALUES
                            (
                                SEQ_MES_PALLET_BINDING_ID.NEXTVAL, -- 序列生成主键
                                '{mesPalletBinding.StackCode}',                        -- 栈板码
                                '{mesPalletBinding.LoginId}',                          -- 登录号
                                '{item.SnNo}',                             -- SN号
                                '{item.TicketNo}',                         -- 工单单号
                                '{item.MediumBoxCode}',                     -- 中箱码
                                SYSDATE,                            -- 创建时间
                                '{mesPalletBinding.LoginId}'                          -- 创建人
                            )
                            string sqlInsert = $@"
                        INSERT INTO MES_PALLET_BINDING (
                            ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY
                        ) VALUES (
                            SEQ_MES_PALLET_BINDING_ID.NEXTVAL,
                            '{mesPalletBinding.StackCode}',
                            '{mesPalletBinding.LoginId}',
                            '{item.SnNo}',
                            '{item.TicketNo}',
                            '{item.MediumBoxCode}',
                            SYSDATE,
                            '{mesPalletBinding.LoginId}'
                        )";

                                    ";
                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlInsert);

                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
                            // 插入绑定日志（操作类型 0）
                            string logInsert = $@"
                        INSERT INTO MES_PALLET_BINDING_LOG (
                            ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY, OPERATION_TYPE
                        ) VALUES (
                            MES_PALLET_BINDING_LOG_ID.NEXTVAL,
                            '{mesPalletBinding.StackCode}',
                            '{mesPalletBinding.LoginId}',
                            '{item.SnNo}',
                            '{item.TicketNo}',
                            '{item.MediumBoxCode}',
                            SYSDATE,
                            '{mesPalletBinding.LoginId}',
                            '0'
                        )";

                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
                        }
                    }
                    else
                    {
                        msg[1] = "SN明细为空，请确认！";
                        msg[0] = "false";
                        msg[1] = "SN明细为空，请确认！";
                    }
                }
                //解绑
                else if(mesPalletBinding.Type.ToUpper() == "F")
                // 解绑
                else if (mesPalletBinding.Type.ToUpper() == "F")
                {
                    //解绑，根据中箱码全部删除
                    var sql2 = $@"delete MES_PALLET_BINDING where STACKCODE='" + mesPalletBinding.StackCode + "'";
                    // 先查询绑定的记录
                    string sqlSelect = $"SELECT * FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
                    DataTable dt = sqlHelper.ExecuteQuery( sqlSelect);

                    sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
                    foreach (DataRow row in dt.Rows)
                    {
                        // 插入解绑日志（操作类型 1）
                        string logInsert = $@"
                    INSERT INTO MES_PALLET_BINDING_LOG (
                        ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY, OPERATION_TYPE
                    ) VALUES (
                        MES_PALLET_BINDING_LOG_ID.NEXTVAL,
                        '{row["STACKCODE"]}',
                        '{row["LOGIN_ID"]}',
                        '{row["SN_NO"]}',
                        '{row["TICKET_NO"]}',
                        '{row["MEDIUMBOXCODE"]}',
                        SYSDATE,
                        '{mesPalletBinding.LoginId}',
                        '1'
                    )";

                        sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
                    }

                    // 删除绑定记录
                    string sqlDelete = $"DELETE FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
                    sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlDelete);
                }
                

                // 全部成功提交
                // 提交事务
                transaction.Commit();
            }
            catch (Exception ex)
@@ -314,5 +387,6 @@
            return msg;
        }


    }
}