优化自动产生工作联系单方法

zrg

7 天以前 981ea6f779477b4b729c6796cfc42ee933c781b0

 WebAPI/Controllers/WebAPIController.cs

@@ -1150,7 +1150,52 @@
            }
        }

        /// <summary>
        /// 获取仓库列表  --添加模具仓库条件  用于只查询模具仓库
        /// </summary>
        /// <returns></returns>
        [Route("Web/GetWarehouseList_Json_MJ")]
        [HttpGet]
        public object GetWarehouseList_Json_MJ(string Warehouse, Int64 HOrgID)
        {
            sWhere = " Where HStopFlag=0  and HEndFlag=1 and HWarehouseType='模具仓库' and HUSEORGID =" + HOrgID + "";
            //sWhere = " Where HStopFlag=0  and HEndFlag=1  and HUSEORGID = " + DBUtility.ClsPub.HORGANIZATIONSID.ToString();
            if (Warehouse != "")
            {
                sWhere = sWhere + " and ( HNumber like '%" + Warehouse + "%' or HName like '%" + Warehouse + "%'  or HUSEORGID like '%" + Warehouse + "%' ) ";
            }

            try
            {
                WebS.WebService1 oWeb = new WebS.WebService1();
                ds = oWeb.GetWarehouseList(sWhere, ref DBUtility.ClsPub.sErrInfo);
                if (ds == null || ds.Tables[0].Rows.Count <= 0)
                {
                    objjson.code = "0";
                    objjson.count = 0;
                    objjson.Message = "获取失败" + DBUtility.ClsPub.sErrInfo;
                    objjson.data = null;
                    return objjson;
                }
                else
                {
                    objjson.code = "0";
                    objjson.count = 1;
                    objjson.Message = "获取成功!";
                    objjson.data = ds.Tables[0];
                    return objjson;
                }
            }
            catch (Exception ex)
            {

                objjson.code = "0";
                objjson.count = 0;
                objjson.Message = "获取失败" + ex.ToString();
                objjson.data = null;
                return objjson;
            }
        }

        /// <summary>
        /// 获取仓位列表
@@ -1466,14 +1511,68 @@
            }
        }

        #region 获取职员列表 (添加车间筛选)
        [Route("Web/GetEmployeeList_Json")]
        [HttpGet]
        public object GetEmployeeList_Json3(string Employee, Int64 HGroupID, Int64 HDeptID = -1)
        {
            // 给部门(车间)ID设置默认值，当没有传入值或者值为0的时候，走旧有的函数，否则走新的逻辑
            if(HDeptID == -1 || HDeptID == 0)
            {
                return GetEmployeeList_Json2(Employee, HGroupID);
            }else
            {
                sWhere = $" Where HStopFlag=0  and HEndFlag=1  and HDeptID=${HDeptID}";
                
                if(Employee != "")
                {
                    sWhere += " and ( HNumber like '%" + Employee + "%' or HName like '%" + Employee + "%' )";
                }
                if(HGroupID != 0)
                {
                    sWhere += " and HGroupID=" + HGroupID.ToString();
                }

                try
                {
                    string sql = $"Select * from h_v_IF_Emp {sWhere} Order by HNumber";
                    ds = oCN.RunProcReturn(sql, "h_v_IF_Emp");
                    if (ds == null || ds.Tables[0].Rows.Count <= 0)
                    {
                        // 如果 递入 部门没有维护人员列表 则降级使用老方案
                        return GetEmployeeList_Json2(Employee, HGroupID);
                    }
                    else
                    {
                        objjson.code = "0";
                        objjson.count = 1;
                        objjson.Message = "获取成功!";
                        objjson.data = ds.Tables[0];
                        return objjson;
                    }
                }
                catch (Exception ex)
                {

                    objjson.code = "0";
                    objjson.count = 0;
                    objjson.Message = "获取失败" + ex.ToString();
                    objjson.data = null;
                    return objjson;
                }

            }
        }
        #endregion


        /// <summary>
        /// 获取职员列表
        /// </summary>
        /// <returns></returns>
        [Route("Web/GetEmployeeList_Json")]
        [HttpGet]
        public object GetEmployeeList_Json(string Employee, Int64 HGroupID)
        // [Route("Web/GetEmployeeList_Json")]
        // [HttpGet]
        public object GetEmployeeList_Json2(string Employee, Int64 HGroupID)
        {
            sWhere = " Where HStopFlag=0  and HEndFlag=1";
            //sWhere = " Where HStopFlag=0  and HEndFlag=1  and HUSEORGID = " + DBUtility.ClsPub.HORGANIZATIONSID.ToString();
@@ -1492,11 +1591,11 @@
            {
                if (Employee != "")
                {
                    sWhere = " Where HStopFlag=0 and HWHID=" + HGroupID.ToString() + " and ( HNumber like '%" + Employee + "%' or HName like '%" + Employee + "%' ) ";
                    sWhere = " Where HStopFlag=0 and HGroupID=" + HGroupID.ToString() + " and ( HNumber like '%" + Employee + "%' or HName like '%" + Employee + "%' ) ";
                }
                else
                {
                    sWhere = " Where HStopFlag=0 and HWHID=" + HGroupID.ToString();
                    sWhere = " Where HStopFlag=0 and HGroupID=" + HGroupID.ToString();
                }
            }
            try
@@ -1531,6 +1630,7 @@
            }
        }

       

        /// <summary>
        /// 获取班次列表
@@ -20070,7 +20170,7 @@
                List<object> columnNameList = new List<object>();

                SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
                string sql = "Select * from h_v_Gy_GetSampleSchemeListView where 1 = 1 " + sWhere + "Order by hmainid ";
                string sql = "Select * from h_v_Gy_GetSampleSchemeListView where 1 = 1 and HUSEORGID = "+OrganizationID + sWhere + "Order by hmainid ";
                ds = oCN.RunProcReturn(sql, "h_v_Gy_GetSampleSchemeListView");

                //添加列名
@@ -20333,6 +20433,7 @@
            try
            {
                SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();

                string sql = "exec h_p_Gy_GetQCCheckItemByProject_Sec " + CheckProjectID + "," + HBatchQty + "," + HMaterID + ",'" + HBillType + "'";
                ds = oCN.RunProcReturn(sql, "h_p_Gy_GetQCCheckItemByProject_Sec");
                if (ds == null || ds.Tables[0].Rows.Count <= 0)
@@ -23175,5 +23276,407 @@
            }
        }
        #endregion

        #region 用于连接的健康度检验，只要能访问通这个接口，则表明连接可用
        [Route("Health")]
        [HttpGet]
        public IHttpActionResult CheckHealth()
        {
            // 返回 200 状态码 确认健康状态
            return Ok();
        }
        #endregion

        #region 登录用户与设备ClientID 的关系 写入数据库
        [Route("Web/SetGy_UserClientIdConnection")]
        [HttpPost]
        public object SetGy_UserClientIdConnection([FromBody] JObject msg)
        {
            DataSet ds;
            oCN.BeginTran();
            try
            {
                ds = oCN.RunProcReturn($@"exec h_p_SetGy_UserClientIdRelation 
                @HUserbm=N'{msg["HUserbm"].ToString()}',
               @HUserName=N'{msg["HUserName"].ToString()}',
               @HClientID=N'{msg["HClientID"].ToString()}',
                @HOnline={msg["HOnline"].ToString()}
                ", "h_p_SetGy_UserClientIdRelation");
                
                if(ds.Tables[0].Rows[0]["HStatus"].ToString() == "1")
                {
                    oCN.Commit();
                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = "保存设备信息成功！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                else
                {
                    oCN.RollBack();
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "保存设备信息失败！" + ds.Tables[0].Rows[0]["msg"];
                    objJsonResult.data = null;
                    return objJsonResult;
                }
            }catch(Exception e)
            {
                oCN.RollBack();
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "保存设备信息失败！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region APP 根据模块获取该模块下需要显示消息标识的子模块
        [Route("Web/getMessageIdentified")]
        [HttpGet]
        public object getMessageIdentified(string user, string moduleName)
        {
            DataSet oDs = oCN.RunProcReturn($"exec h_p_getMessageIdentifier_APP '{user}', '{moduleName}'", "h_p_getMessageIdentifier_APP");
            if(oDs == null || oDs.Tables[0].Rows.Count == 0)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "获取消息标识失败！";
                objJsonResult.data = null;
                return objJsonResult;
            }
            else
            {
                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "获取消息标识成功！";
                objJsonResult.data = oDs.Tables[0];
                return objJsonResult;
            }
        }
        #endregion

        #region 模具待上模查询 获取
        [Route("Web/getSc_MouldUpperAwaitQuery")]
        [HttpGet]
        public object getSc_MouldUpperAwaitQuery(string user, string HBeginDate = "", string HEndDate = "")
        {
            if(HBeginDate == "") // 默认7天间距
            {
                HBeginDate = DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd");
            }

            if(HEndDate == "")
            {
                HEndDate = DateTime.Today.ToString("yyyy-MM-dd");
            }
            List<object> columnNameList = new List<object>();
            try
            {
                DataSet oDs = oCN.RunProcReturn($" exec h_p_Sc_MouldUpperAwaitQuery '{user}', '{HBeginDate}', '{HEndDate}'", "h_p_Sc_MouldUpperAwaitQuery");
                if(oDs == null || oDs.Tables[0].Rows.Count == 0)
                {
                    objjson.code = "0";
                    objjson.count = 0;
                    objjson.Message = "获取失败, 单据无返回值!";
                    objjson.data = null;
                    return objjson;
                }else
                {
                    //添加列名
                    foreach (DataColumn col in oDs.Tables[0].Columns)
                    {
                        Type dataType = col.DataType;
                        string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                        columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                    }

                    objjson.code = "1";
                    objjson.count = 1;
                    objjson.Message = "获取成功!";
                    objjson.data = oDs.Tables[0];
                    objjson.list = columnNameList;
                    return objjson;
                }

            }catch(Exception ex)
            {
                LogService.Write(ex.ToString());
                objjson.code = "0";
                objjson.count = 0;
                objjson.Message = "获取失败" + ex.Message.ToString();
                objjson.data = null;
                return objjson;
            }
        }
        #endregion

        #region 待开工列表 获取
        [Route("Web/getCj_StationInBillAwaitQuery")]
        [HttpGet]
        public object getCj_StationInBillAwaitQuery(string user, string HBeginDate = "", string HEndDate = "")
        {
            if (HBeginDate == "") // 默认7天间距
            {
                HBeginDate = DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd");
            }

            if (HEndDate == "")
            {
                HEndDate = DateTime.Today.ToString("yyyy-MM-dd");
            }
            List<object> columnNameList = new List<object>();
            try
            {
                DataSet oDs = oCN.RunProcReturn($" exec h_p_Cj_StationInBillAwaitQuery '{user}', '{HBeginDate}', '{HEndDate}'", "h_p_Cj_StationInBillAwaitQuery");
                if (oDs == null || oDs.Tables[0].Rows.Count == 0)
                {
                    objjson.code = "0";
                    objjson.count = 0;
                    objjson.Message = "获取失败, 单据无返回值!";
                    objjson.data = null;
                    return objjson;
                }
                else
                {
                    //添加列名
                    foreach (DataColumn col in oDs.Tables[0].Columns)
                    {
                        Type dataType = col.DataType;
                        string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                        columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                    }

                    objjson.code = "1";
                    objjson.count = 1;
                    objjson.Message = "获取成功!";
                    objjson.data = oDs.Tables[0];
                    objjson.list = columnNameList;
                    return objjson;
                }

            }
            catch (Exception ex)
            {
                LogService.Write(ex.ToString());
                objjson.code = "0";
                objjson.count = 0;
                objjson.Message = "获取失败" + ex.Message.ToString();
                objjson.data = null;
                return objjson;
            }
        }
        #endregion

        #region 模具待下模查询 获取
        [Route("Web/getSc_MouldLowerBillAwaitQuery")]
        [HttpGet]
        public object getSc_MouldLowerBillAwaitQuery(string user, string HBeginDate = "", string HEndDate = "")
        {
            if (HBeginDate == "") // 默认7天间距
            {
                HBeginDate = DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd");
            }

            if (HEndDate == "")
            {
                HEndDate = DateTime.Today.ToString("yyyy-MM-dd");
            }
            List<object> columnNameList = new List<object>();
            try
            {
                DataSet oDs = oCN.RunProcReturn($" exec h_p_Sc_MouldLowerBillAwaitQuery '{user}', '{HBeginDate}', '{HEndDate}'", "h_p_Sc_MouldLowerBillAwaitQuery");
                if (oDs == null || oDs.Tables[0].Rows.Count == 0)
                {
                    objjson.code = "0";
                    objjson.count = 0;
                    objjson.Message = "获取失败, 单据无返回值!";
                    objjson.data = null;
                    return objjson;
                }
                else
                {
                    //添加列名
                    foreach (DataColumn col in oDs.Tables[0].Columns)
                    {
                        Type dataType = col.DataType;
                        string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                        columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                    }

                    objjson.code = "1";
                    objjson.count = 1;
                    objjson.Message = "获取成功!";
                    objjson.data = oDs.Tables[0];
                    objjson.list = columnNameList;
                    return objjson;
                }

            }
            catch (Exception ex)
            {
                LogService.Write(ex.ToString());
                objjson.code = "0";
                objjson.count = 0;
                objjson.Message = "获取失败" + ex.Message.ToString();
                objjson.data = null;
                return objjson;
            }
        }
        #endregion

        #region 获取 设备绑定职员信息(带出模具职员信息) 华舟
        [Route("Web/getEquipBindingEmp")]
        [HttpGet]
        public object getEquipBindingEmp(string HEquipNumber)
        {
            try
            {
                List<object> columnNameList = new List<object>();

                ds = oCN.RunProcReturn($"exec h_p_getEquipBindingEmp_huazhou {HEquipNumber}", "h_p_getEquipBindingEmp_huazhou");
                if (ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "当前设备没有上模信息";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.list = columnNameList;
                return objJsonResult;
            }
            catch (Exception ex)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "查询数据异常，请与管理员联系！" + ex.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region APP 获取异常反馈单 获取 检验单内容 通用方法
        [Route("Web/getCheckBillStatus")]
        [HttpGet]
        public object getCheckBillStatus(Int64 HInterID, string HBillType)
        {
            try { 
                string sql = "";
                List<object> columnNameList = new List<object>();
                if(HBillType == "7505")
                {
                    // 首检
                    sql = $@"SELECT
                          a.HInterID AS hmainid,b.HEntryID hsubid, a.HDate AS 日期, a.HBillNo AS 单据号, 
                          a.HRemark AS 表头备注,s.HName 生产资源名称,s.HNumber 生产资源代码, m.HNumber AS 产品内码, m.HName AS 产品名称, m.HModel AS 产品型号,e.HNumber as 检验员代码, e.HName as 检验员名称,
                          a.HMaker AS 制单人, a.HMakeDate AS 制单日期, a.HChecker AS 审核人, a.HCheckDate AS 审核日期, a.HUpDater AS 修改人, 
                          a.HUpDateDate AS 修改日期, a.HCloseMan AS 关闭人, a.HCloseDate AS 关闭日期, a.HDeleteMan AS 作废人, a.HDeleteDate AS 作废日期, 
                          a.HBillType AS 单据类型,b.HEntryID hsubid, a.HCheckQty 检验数量, a.HSourceID HSourceID,
                        a.HProcExchInterID HProcExchInterID, a.HProcExchEntryID HProcExchEntryID, a.HProcExchBillNo 工序流转卡号,
                        a.HICMOInterID HICMOInterID, a.HICMOEntryID HICMOEntryID, a.HICMOBillNo 生产订单号
                          FROM				  
                          dbo.QC_FirstPieceCheckBillMain AS a INNER JOIN
                          dbo.QC_FirstPieceCheckBillSub AS b ON a.HInterID = b.HInterID LEFT OUTER JOIN
                        dbo.Gy_Source s on a.HSourceID =s.HItemID LEFT OUTER JOIN
                        dbo.Gy_Material m on a.HMaterID = m.HItemID LEFT OUTER JOIN
                          dbo.Gy_Employee AS e ON a.HFirstCheckEmp = e.HItemID ";
                    sql += $" where a.HInterID = {HInterID}";

                    ds = oCN.RunProcReturn(sql, "QC_FirstPieceCheckBillMain");

                }
                else if (HBillType == "7520")
                {
                    // 巡检
                    sql = $@"SELECT
                          a.HInterID AS hmainid,b.HEntryID hsubid, a.HDate AS 日期, a.HBillNo AS 单据号, 
                          a.HRemark AS 表头备注,s.HName 生产资源名称,s.HNumber 生产资源代码, m.HNumber AS 产品内码, m.HName AS 产品名称, m.HModel AS 产品型号,e.HNumber as 检验员代码, e.HName as 检验员名称,
                          a.HMaker AS 制单人, a.HMakeDate AS 制单日期, a.HChecker AS 审核人, a.HCheckDate AS 审核日期, a.HUpDater AS 修改人, 
                          a.HUpDateDate AS 修改日期, a.HCloseMan AS 关闭人, a.HCloseDate AS 关闭日期, a.HDeleteMan AS 作废人, a.HDeleteDate AS 作废日期, 
                          a.HBillType AS 单据类型,b.HEntryID hsubid, a.HCheckQty 检验数量, a.HSourceID HSourceID,
                        a.HProcExchInterID HProcExchInterID, a.HProcExchEntryID HProcExchEntryID, a.HProcExchBillNo 工序流转卡号,
                        a.HICMOInterID HICMOInterID, a.HICMOEntryID HICMOEntryID, a.HICMOBillNo 生产订单号
                          FROM				  
                          dbo.QC_PatrolProcCheckOtherBillMain AS a INNER JOIN
                          dbo.QC_PatrolProcCheckOtherBillSub AS b ON a.HInterID = b.HInterID LEFT OUTER JOIN
                        dbo.Gy_Source s on a.HSourceID =s.HItemID LEFT OUTER JOIN
                        dbo.Gy_Material m on a.HMaterID = m.HItemID LEFT OUTER JOIN
                          dbo.Gy_Employee AS e ON a.HFirstCheckEmp = e.HItemID ";
                    sql += $" where a.HInterID = {HInterID}";

                    ds = oCN.RunProcReturn(sql, "QC_PatrolProcCheckOtherBillMain");
                }
                else if(HBillType == "7507")
                {
                    // 末检
                    sql = $@"SELECT
                          a.HInterID AS hmainid,b.HEntryID hsubid, a.HDate AS 日期, a.HBillNo AS 单据号, 
                          a.HRemark AS 表头备注,s.HName 生产资源名称,s.HNumber 生产资源代码, m.HNumber AS 产品内码, m.HName AS 产品名称, m.HModel AS 产品型号,e.HNumber as 检验员代码, e.HName as 检验员名称,
                          a.HMaker AS 制单人, a.HMakeDate AS 制单日期, a.HChecker AS 审核人, a.HCheckDate AS 审核日期, a.HUpDater AS 修改人, 
                          a.HUpDateDate AS 修改日期, a.HCloseMan AS 关闭人, a.HCloseDate AS 关闭日期, a.HDeleteMan AS 作废人, a.HDeleteDate AS 作废日期, 
                          a.HBillType AS 单据类型,b.HEntryID hsubid, a.HCheckQty 检验数量, a.HSourceID HSourceID,
                        a.HProcExchInterID HProcExchInterID, a.HProcExchEntryID HProcExchEntryID, a.HProcExchBillNo 工序流转卡号,
                        a.HICMOInterID HICMOInterID, a.HICMOEntryID HICMOEntryID, a.HICMOBillNo 生产订单号
                          , HRemarkSN 条码                          
                          FROM				  
                          dbo.QC_ProcessCheckBillMain AS a INNER JOIN
                          dbo.QC_ProcessCheckBillSub AS b ON a.HInterID = b.HInterID LEFT OUTER JOIN
                        dbo.Gy_Source s on a.HSourceID =s.HItemID LEFT OUTER JOIN
                        dbo.Gy_Material m on a.HMaterID = m.HItemID LEFT OUTER JOIN
                          dbo.Gy_Employee AS e ON a.HFirstCheckEmp = e.HItemID ";
                    sql += $" where a.HInterID = {HInterID}";

                    ds = oCN.RunProcReturn(sql, "QC_ProcessCheckBillMain");
                }
                if(ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "查询数据异常，无检验单！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.list = columnNameList;
                return objJsonResult;
            }
            catch (Exception ex)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "查询数据异常，请与管理员联系！" + ex.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
    }
}