对色记录单查询优化 当前工单模块 出站汇报

yangle

2024-04-18 c98968a17029fb98e72596c1a868e7d543816102

对色记录单查询优化
当前工单模块 出站汇报

 Pub_Class/CustomerCls/ClsXt_SystemParameterMain.cs

@@ -436,6 +436,7 @@
        public string MES_StationInBill_DeleteRelationCtl;              //进站接收单删除时是否进行关联控制
        public string MES_StationInBill_PreQtyCtl;              //本道进站数量不能大于上道出站合格数量
        public string MES_StationInBill_QtyRateCtl;              //本道进站数量汇总数不能大于流转卡数量上限
        public string MES_StationInBill_DyCentralInspectionControl;              //判断工序进站之前是否做过检验单

        //工序出站汇报单
        public string MES_StationOutBill_AutoCheck;                        //保存后是否自动审核

 WebAPI/Controllers/BLL/Xt_UserController.cs

@@ -206,14 +206,14 @@
                    ",HWhID,HWHName,HSupID,HSupName " +
                    ",HSCWHID,HSCWHName " +
                    ",HCloudUserName,HCloudUserPsd,HUSEORGID,HProcID" +
                    ",HGroupID,HSourceID,HCheckEmpID) " +
                    ",HGroupID,HSourceID,HCheckEmpID,HWorkCenterID) " +
                    "values ('" + lsmain[0].Czybm.ToString() + "','" + lsmain[0].Czymc.ToString() + "','" + ClsPub.StrToPsd(lsmain[0].Czmm.ToString()) + "','" + lsmain[0].Explain.ToString() + "','" + lsmain[0].HEmpID.ToString() + "',"+ lsmain[0].HK3UserID.ToString() + "" +
                    ","+ lsmain[0].HKeeperID.ToString() + ",'"+ lsmain[0].HKeeper.ToString() + "',"+ lsmain[0].HSecManagerID.ToString() + ",'"+ lsmain[0].HSecManager.ToString() + "'" +
                    "," + lsmain[0].HSellManID.ToString() + ",'" + lsmain[0].HSellMan.ToString() + "'," + lsmain[0].HDeptID.ToString() + ",'" + lsmain[0].HDept.ToString() + "'" +
                    "," + lsmain[0].HWhID.ToString() + ",'" + lsmain[0].HWHName.ToString() + "'," + lsmain[0].HSupID.ToString() + ",'" + lsmain[0].HSupName.ToString() + "'" +
                    "," + lsmain[0].HSCWHID.ToString() + ",'" + lsmain[0].HSCWHName.ToString() + "'" + 
                    ",'" + lsmain[0].HCloudUserName.ToString() + "','" + lsmain[0].HCloudUserPsd.ToString() + "',"+ lsmain[0].HOrgID.ToString() + "," + lsmain[0].HProcID.ToString() + "" +
                    ","+ lsmain[0].HGroupID.ToString() + ","+ lsmain[0].HSourceID.ToString() + "," + lsmain[0].HCheckEmpID.ToString() + ")");
                    ","+ lsmain[0].HGroupID.ToString() + ","+ lsmain[0].HSourceID.ToString() + "," + lsmain[0].HCheckEmpID.ToString() + "," + lsmain[0].HWorkCenterID.ToString() + ")");

                    //新增后查询  确保数据添加成功
                    ds = oCN.RunProcReturn("Select * from  Gy_Czygl where Czybm='" + lsmain[0].Czybm.ToString() + "'", "Gy_Czygl");
@@ -264,6 +264,7 @@
                        ",HProcID=" + lsmain[0].HProcID.ToString() +
                        ",HSourceID=" + lsmain[0].HSourceID.ToString() +
                        ",HGroupID=" + lsmain[0].HGroupID.ToString() +
                        ",HWorkCenterID=" + lsmain[0].HWorkCenterID.ToString() +
                        ",HSupName='" + lsmain[0].HSupName.ToString() + "'" +
                        ",HCloudUserName='" + lsmain[0].HCloudUserName.ToString() + "'" +
                        ",HCloudUserPsd='" + lsmain[0].HCloudUserPsd.ToString() + "'" +
@@ -293,6 +294,7 @@
                            ",HProcID=" + lsmain[0].HProcID.ToString() +
                           ",HSourceID=" + lsmain[0].HSourceID.ToString() +
                           ",HGroupID=" + lsmain[0].HGroupID.ToString() +
                           ",HWorkCenterID=" + lsmain[0].HWorkCenterID.ToString() +
                           ",HSupName='" + lsmain[0].HSupName.ToString() + "'" +
                           ",HCloudUserName='" + lsmain[0].HCloudUserName.ToString() + "'" +
                           ",HCloudUserPsd='" + lsmain[0].HCloudUserPsd.ToString() + "'" +

 WebAPI/Controllers/CJGL/Cj_StationEntrustInBillController.cs

@@ -1023,12 +1023,19 @@
                }

                ds = oCN.RunProcReturn("select ManagerFlag from gy_czygl where czymc='" + user.Trim() + "'", "gy_czygl");
                if (!(bool)ds.Tables[0].Rows[0]["ManagerFlag"])  //是否管理员
                string sErr = "";
                oSystemParameter.ShowBill(ref sErr);
                if (oSystemParameter.omodel.WMS_CampanyName == "乔一") 
                {
                    //不是管理员
                    sWhere += " and 制单人 = '" + user + "'";
                }

                else
                {
                    if (!(bool)ds.Tables[0].Rows[0]["ManagerFlag"])  //是否管理员
                    {
                        //不是管理员
                        sWhere += " and 制单人 = '" + user + "'";
                    }
                }

                string sql = "select * from  h_v_QC_FirstPieceCheckBillMainList where 1=1  " + sWhere + " order by 日期 ";


 WebAPI/Controllers/CJGL/Cj_StationOutBillController.cs

@@ -3210,5 +3210,269 @@
            }
        }
        #endregion

        #region  当前工单(扫码汇报) 条码查询
        [Route("Cj_StationOutBill_CurrentWork/SNHBardCodeList")]
        [HttpGet]
        public object SNHBardCodeList(string HBarCode, string user, int HOrgID)
        {
            try
            {
                ds = oCN.RunProcReturn(@"select HSourceInterID,HSourceBillNo from Gy_BarCodeBill WITH(NOLOCK) where HBarCode='" + HBarCode + "'", "Gy_BarCodeBill");
              
                if (ds.Tables[0].Rows.Count > 0)
                {
                    string HSourceInterID = ds.Tables[0].Rows[0]["HSourceInterID"].ToString();
                    string HSourceBillNo = ds.Tables[0].Rows[0]["HSourceBillNo"].ToString();

                    ds = oCN.RunProcReturn("select * from gy_czygl WITH(NOLOCK) where czymc='" + user + "'", "gy_czygl");
                    string HProcID = ds.Tables[0].Rows[0]["HProcID"].ToString();

                    ds = oCN.RunProcReturn("select  * from h_v_Sc_ProcessExchangeBillList where hmainid=" + HSourceInterID + " and 单据号='" + HSourceBillNo + "' and HProcID=" + HProcID, "h_v_Sc_ProcessExchangeBillList");

                    if (ds.Tables[0].Rows.Count == 0) {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "条码:" + HBarCode + ",流转卡:" + HSourceBillNo + ",与当前流转卡,所对应的工序不存在!";
                        objJsonResult.data = ds.Tables[0];
                        return objJsonResult;
                    }
                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = "";
                    objJsonResult.data = ds.Tables[0];
                    return objJsonResult;
                }
                else {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "条码查无数据!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
            }
            catch (Exception e)
            {

                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "没有返回任何记录！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region  当前工单(扫码汇报) 出站单查询
        [Route("Cj_StationOutBill_CurrentWork/get_Out_Display")]
        [HttpGet]
        public object get_Out_Display(string sWhere, string user,string HBillSubType)
        {
            try
            {
                //判断是否有查询权限
                if (!DBUtility.ClsPub.Security_Log("Cj_StationOutBill_Query", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "出站单无查询权限!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                string sql1 = "select * from h_v_MES_StationOutBillList_SN where 1 = 1 ";
                string sql = sql1 + sWhere + "  order by 制单日期 desc";
                ds = oCN.RunProcReturn(sql, "h_v_MES_StationOutBillList_SN");

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 当前工单(扫码汇报)  子表保存
        [Route("Cj_StationOutBill_CurrentWork/AddBillSub")]
        [HttpPost]
        public object AddBillSub([FromBody] JObject oMain)
        {
            var _value = oMain["oMain"].ToString();
            string msg1 = _value.ToString();
            string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
            try
            {
                //反序列化
                string msg2 = "[" + sArray[0].ToString() + "]";
                string user = sArray[1].ToString();//用户名
                string OperationType = sArray[2].ToString();//类型

                if (OperationType == "5")
                {
                    //判断是否有编辑权限
                    if (!DBUtility.ClsPub.Security_Log("Cj_StationOutBill_Sub_Edit", 1, false, user))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "完工单无编辑权限!";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }
                else
                {
                    //判断是否有编辑权限
                    if (!DBUtility.ClsPub.Security_Log("Cj_StationOutBill_Edit", 1, false, user))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "出站单无编辑权限!";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                List<StationBill> list = Newtonsoft.Json.JsonConvert.DeserializeObject<List<StationBill>>(msg2);
                long HInterID = list[0].HInterID;//递入type得到的单据ID
                string HBillNo = list[0].HBillNo;//递入type得到的单据号
                double HQty = list[0].HQty;//合格数量
                long HProcExchInterID = list[0].lngBillKey;
                long HProcExchEntryID = list[0].lngBillSubKey;
                string HProcExchBillNo = list[0].HProcExchBillNo;//流转卡
                string HBarCode = list[0].HBarCode;//条码
                string eventType = list[0].eventType;

                //若MAINDI重复则重新获取
                oCN.BeginTran();
                if ("Add".Equals(eventType))
                {
                    ds = oCN.RunProcReturn("select  * from Sc_StationOutBillSub_SN WITH(NOLOCK) where HInterID='" + HInterID + "' order by HEntryID desc", "Sc_StationOutBillSub_SN");

                    oCN.RunProc($@"insert into Sc_StationOutBillSub_SN(HInterID,HBillNo_bak,HEntryID,HBarCode,HBarCodeQty,HMakeTime,HRemark,HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney)
values({HInterID}, '{HBillNo}', {(ds.Tables[0].Rows.Count == 0 ? 1 : int.Parse(ds.Tables[0].Rows[0]["HEntryID"].ToString()) + 1)}, '{HBarCode}', {HQty}, GETDATE(), '', " + HProcExchInterID + "," + HProcExchEntryID + ", '" + HProcExchBillNo + "', '', 0, 0)");

                }
                else
                {
                    oCN.RollBack();
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "操作类型无效！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "新增单据成功！";
                objJsonResult.data = ds.Tables[0];
                return objJsonResult;
            }
            catch (Exception e)
            {
                oCN.RollBack();
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 当前工单(扫码汇报) 删除出站数据
        [Route("Cj_StationOutBill_CurrentWork/DelOutBill_SN")]
        [HttpGet]
        public object DelOutBill_SN(int HInterID, string HDeleteMan,string HBillSubType)
        {
            try
            {
                //判断是否有查询权限
                if (!DBUtility.ClsPub.Security_Log("Cj_StationOutBill_Drop", 1, false, HDeleteMan))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无删除权限!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                ds = oCN.RunProcReturn("select * from h_v_MES_StationOutBillList_SN WITH(NOLOCK) where hmainid = " + HInterID, "h_v_MES_StationOutBillList_SN");
              
                if (ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "查无数据！";
                    objJsonResult.data = null;
                }

                oCN.BeginTran();

                string sql = "";
                if (ds.Tables[0].Rows.Count == 1)
                {
                    sql = "delete from Sc_StationOutBillMain where HInterID = " + HInterID;
                    oCN.RunProc(sql);
                }
                sql = "delete from Sc_StationOutBillSub_SN where HInterID = " + HInterID ;
                oCN.RunProc(sql);

                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = null;
                return objJsonResult;
            }
            catch (Exception e)
            {
                oCN.RollBack();
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region  当前工单(扫码汇报) 仪表盘查询
        [Route("Cj_StationOutBill_CurrentWork/get_PanelDisplay")]
        [HttpGet]
        public object get_PanelDisplay(string HBarCode, string user, int HOrgID)
        {
            try
            {
                ds = oCN.RunProcReturn(@"select HSourceInterID,HSourceBillNo from Gy_BarCodeBill WITH(NOLOCK) where HBarCode='" + HBarCode + "'", "Gy_BarCodeBill");

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "";
                objJsonResult.data = ds.Tables[0];
                return objJsonResult;
            }
            catch (Exception e)
            {

                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "没有返回任何记录！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
    }
}

 WebAPI/Models/Gy_User.cs

@@ -35,5 +35,6 @@
        public int HGroupID;
        public int HSourceID;
        public int HCheckEmpID;
        public int HWorkCenterID;
    }
}