/******************************************************************************* * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential * 类的信息： * 1.程序名称：PDAModuleLogic.cs * 2.功能描述：PDA相关处理。 * 编辑履历： * 作者 日期 版本 修改内容 * 陈晓野 2014/09/16 1.00 新建 *******************************************************************************/ using System; using System.Collections.Generic; using System.Data; using System.Drawing; using System.Drawing.Imaging; using System.IO; using System.Text; using Dongke.IBOSS.PRD.Basics.BaseResources; using Dongke.IBOSS.PRD.Basics.DataAccess; using Dongke.IBOSS.PRD.Basics.Library; using Dongke.IBOSS.PRD.Service.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels.HRModule; using Dongke.IBOSS.PRD.WCF.DataModels.PCModule; using Dongke.IBOSS.PRD.WCF.DataModels.PMModule; using Oracle.DataAccess.Client; namespace Dongke.IBOSS.PRD.Service.PDAModuleLogic { /// /// PDA /// public partial class PDAModuleLogic { #region PDA端登陆 /// /// PDA端登陆 /// /// public static PDALoginResult DoPDALogin(LoginRequestEntity requestEntity) { PDALoginResult pdaResult = new PDALoginResult(); LoginResultEntity resultEntity = DKIBOSSPRDLogic.DKIBOSSPRDLogic.DoLogin(requestEntity, "2"); pdaResult.Status = resultEntity.LoginStatus; //pdaResult.Message = resultEntity.LoginMessage; if (pdaResult.Status != 0) { switch (pdaResult.Status) { case 0: pdaResult.Message = "登录成功"; break; case 1: pdaResult.Message = "帐套不存在"; break; case 2: pdaResult.Message = "该帐套不存在该用户"; break; case 3: pdaResult.Message = "用户密码不正确"; break; case 4: pdaResult.Message = "MAC地址错误"; break; case 5: pdaResult.Message = "登录时间错误"; break; case 6: pdaResult.Message = "用户停用"; break; case 7: pdaResult.Message = "用户被锁死"; break; case 8: pdaResult.Message = "此用户不能在移动端登录"; break; case 9: pdaResult.Message = "APP版本需要更新"; break; case 10: pdaResult.Message = Constant.PDA_RESULT_SYSTEMDATEERROR; break; case 11: pdaResult.Message = "没有找到加服务器密锁"; break; case 12: pdaResult.Message = "服务器加密锁不是东科的加密锁"; break; case 13: pdaResult.Message = "读取服务器锁内客户编码内容失败"; break; case 21: //pdaResult.Message = "没有找到license文件"; pdaResult.Message = Messages.MSG_SYS_W011; break; case 22: //pdaResult.Message = "license文件损坏"; pdaResult.Message = Messages.MSG_SYS_W012; break; case 23: //pdaResult.Message = "license文件内容不正确"; pdaResult.Message = Messages.MSG_SYS_W013; break; case 31: pdaResult.Message = "服务器加密锁的内容和license文件的内容不相符"; break; case 32: //pdaResult.Message = "临时的License已经过期"; pdaResult.Message = Messages.MSG_SYS_W015; break; case 41: pdaResult.Message = "打开客户端加密锁失败"; break; case 42: pdaResult.Message = "没有找到客户端加密锁"; break; case 43: pdaResult.Message = "客户端加密锁不是东科的加密锁"; break; case 44: pdaResult.Message = "读取加密锁信息失败（硬件损坏、管理密码错误等）"; break; case 45: pdaResult.Message = "加密锁的内容和license内容不一致"; break; default: pdaResult.Message = "登录失败"; break; } } else { pdaResult.SessionKey = resultEntity.CurrentUserEntity.SessionKey; pdaResult.AccountID = resultEntity.CurrentUserEntity.AccountID; pdaResult.UserID = resultEntity.CurrentUserEntity.UserID; pdaResult.UserName = resultEntity.CurrentUserEntity.UserName; pdaResult.IsGroutingWorker = resultEntity.CurrentUserEntity.IsGroutingWorker.ToString(); //pdaResult.Message = INIUtility.Instance(INIUtility.IniFile.Config).ReadIniData("WCFSetting", "ServerName"); pdaResult.ServerName = INIUtility.Instance(INIUtility.IniFile.Config).ReadIniData("WCFSetting", "ServerName"); if (resultEntity.LicenseInfo != null) { DateTime vEnd = Convert.ToDateTime(resultEntity.LicenseInfo.Tables["Info"].Rows[0]["ValidityEnd"]); pdaResult.ValidityEnd = vEnd.ToString("yyyy-MM-dd"); // TODO message -1 登录成功，显示消息（确定）；-2 登录成功，显示消息（不确定）； } pdaResult.AppVersion = INIUtility.Instance(INIUtility.IniFile.Config).ReadIniData("VersionSetting", "AndroidVersion");//System.Configuration.ConfigurationManager.AppSettings["AndroidVersion"]; string ApkPath = INIUtility.Instance(INIUtility.IniFile.Config).ReadIniData("PathSetting", "UpgradeAPKPath"); ApkPath = System.AppDomain.CurrentDomain.BaseDirectory + ApkPath; if (File.Exists(ApkPath)) { //pdaResult.Rights = userEntity.UserRightData; FileStream file = File.OpenRead(ApkPath); pdaResult.ApkSize = file.Length; file.Close(); } else { pdaResult.ApkSize = -1;//APK文件不存在 } } return pdaResult; } #endregion #region 获得显示数据 /// /// 获得生产线菜单 /// /// 用户信息 /// DataTable /// /// 陈冰 2014.09.23 新建 /// public static DataTable GetProductionLineMenu(SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_userID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor,ParameterDirection.Output), }; DataSet dsResult = con.ExecStoredProcedure("PRO_PDA_GetProductionLineMenu", paras); if (dsResult != null && dsResult.Tables.Count > Constant.INT_IS_ZERO) { return dsResult.Tables[0]; } return null; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 统计 /// /// 统计当日计数数量 /// /// 当前工序ID /// /// public static DataSet StatisticsCollectBarcode(int procedureID, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // string sql = @"select tuser.UserCode, goods.GoodsCode, goods.GoodsName, goodscount.Count // from (select Goodsid, count(Goodsid) count, UserID // from tp_pm_productiondata // where ProcedureID = :procedureID // and CreateUserID = :createUserID // and AccountDate = fun_cmn_getaccountdate(accountid) // group by Goodsid, UserID) goodscount // inner join tp_mst_goods goods on goods.goodsid = goodscount.goodsid // inner join tp_mst_user tuser on tuser.userid = goodscount.userid // order by tuser.usercode, goods.goodscode"; //tp_pm_productiondata.UserCode, // string sql = @" select // decode(GoodsCode,'小计','-',UserCode) as UserCode, // // decode(UserCode,'合计','合计',GoodsCode) as GoodsCode, // count // from // ( // // select // // decode(tp_pm_productiondata.GoodsCode,'合计','-',tp_pm_productiondata.UserCode) as UserCode, // tp_pm_productiondata.GoodsCode, // // tp_pm_productiondata.count // from ( // select // // // decode(grouping(tuser.UserCode),1,'合计',tuser.UserCode) UserCode, // decode(grouping(goods.GoodsCode),1,'小计',goods.GoodsCode) GoodsCode, // // count(tp_pm_productiondata.goodsid) count // from // tp_pm_productiondata // inner join tp_mst_goods goods // on tp_pm_productiondata.goodsid = goods.goodsid // and tp_pm_productiondata.procedureid=:procedureID // and tp_pm_productiondata.createUserID=:createUserID // and tp_pm_productiondata.AccountDate = trunc(sysdate) // inner join tp_mst_user tuser on tuser.userid = tp_pm_productiondata.userid // // group by rollup(tuser.UserCode,goods.GoodsCode) // // ) tp_pm_productiondata // // ) // // "; string sql = @" select decode(GoodsCode,'小计','-',UserCode) as UserCode, decode(UserCode,'合计','合计',GoodsCode) as GoodsCode, count from ( select decode(tp_pm_productiondata.GoodsCode,'合计','-',tp_pm_productiondata.UserCode) as UserCode, tp_pm_productiondata.GoodsCode, tp_pm_productiondata.count from ( select decode(grouping(UserCode),1,'合计',UserCode) UserCode, decode(grouping(GoodsCode),1,'小计',GoodsCode) GoodsCode, count(tp_pm_productiondata.goodsid) count from tp_pm_productiondata where tp_pm_productiondata.procedureid=:procedureID and tp_pm_productiondata.createUserID=:createUserID and tp_pm_productiondata.AccountDate = trunc(sysdate) and tp_pm_productiondata.valueflag=1 group by rollup(UserCode,GoodsCode) ) tp_pm_productiondata ) "; // 陈冰 2014-11-24 // and tp_pm_productiondata.userid=:createUserID 改成 tp_pm_productiondata.createUserID = :createUserID // and tp_pm_productiondata.AccountDate = fun_cmn_getaccountdate(tp_pm_productiondata.accountid) // 改成 and tp_pm_productiondata.AccountDate =trunc(sysdate) OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":procedureID",OracleDbType.Int32, procedureID,ParameterDirection.Input), new OracleParameter(":createUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; DataSet dsResult = con.GetSqlResultToDs(sql, paras); return dsResult; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 使用存储过程PRO_PM_StatisticsKilnCar统计当前用户下工序的窑车产品数据 /// /// 工序 /// public static DataSet StatisticsKilnCar(int procedureID, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); string procsql = "PRO_PM_StatisticsKilnCar"; try { IDataParameter[] paras = new OracleParameter[] { new OracleParameter("in_procedureid", OracleDbType.Int32,procedureID, ParameterDirection.Input), new OracleParameter("in_userid", OracleDbType.Int32,sUserInfo.UserID, ParameterDirection.Input), new OracleParameter("rs_result", OracleDbType.RefCursor, ParameterDirection.Output) }; oracleConn.Open(); DataSet returnDs = oracleConn.ExecStoredProcedure(procsql, paras); return returnDs; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 使用存储过程PRO_PM_StatisticsProductTrack统计当前条码所有工序 /// /// 工序 /// public static ProductionDataEntity StatisticsProductTrack(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); string procsql = "PRO_PM_StatisticsProductTrack"; try { IDataParameter[] paras = new OracleParameter[] { new OracleParameter("in_barcode", OracleDbType.Varchar2,barcode, ParameterDirection.Input), new OracleParameter("in_accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter("out_goodsCode", OracleDbType.Varchar2,100,null, ParameterDirection.Output), new OracleParameter("out_groutingUserCode", OracleDbType.Varchar2,100,null, ParameterDirection.Output), new OracleParameter("out_mouldCode", OracleDbType.Varchar2,100,null, ParameterDirection.Output), new OracleParameter("out_groutingNum", OracleDbType.Varchar2,100,null, ParameterDirection.Output), new OracleParameter("out_goodsEnding", OracleDbType.Varchar2,100,null, ParameterDirection.Output), new OracleParameter("rs_result", OracleDbType.RefCursor, ParameterDirection.Output) }; oracleConn.Open(); DataSet returnDs = oracleConn.ExecStoredProcedure(procsql, paras); ProductionDataEntity[] productionDataEntitys = new ProductionDataEntity[1]; ProductionDataEntity productionDataEntity = new ProductionDataEntity(); productionDataEntity.Barcode = paras[0].Value != null ? paras[0].Value.ToString() : ""; productionDataEntity.GoodsCode = paras[2].Value != null ? paras[2].Value.ToString() : ""; productionDataEntity.GroutingUserCode = paras[3].Value != null ? paras[3].Value.ToString() : ""; productionDataEntity.MouldCode = paras[4].Value != null ? paras[4].Value.ToString() : ""; string GroutingNum = paras[5].Value != null ? paras[5].Value.ToString() : ""; productionDataEntity.GroutingNum = Convert.ToInt32((GroutingNum == "" || GroutingNum.ToString() == "null") ? "0" : GroutingNum); productionDataEntity.GoodsEnding = paras[6].Value != null ? paras[6].Value.ToString() : ""; if (returnDs != null && returnDs.Tables[0].Rows.Count > 0) { //ProductionDataEntity[] productionDataEntitys = new ProductionDataEntity[returnDs.Tables[0].Rows.Count]; List productionProcedureListEntitys = new List(); for (int i = 0; i < returnDs.Tables[0].Rows.Count; i++) { ProductionProcedureListEntity productionProcedureListEntity = new ProductionProcedureListEntity(); productionProcedureListEntity.ProcedureName = returnDs.Tables[0].Rows[i]["ProcedureName"].ToString(); productionProcedureListEntity.ProductionUserCode = returnDs.Tables[0].Rows[i]["ProductionUserCode"].ToString(); productionProcedureListEntity.Usercode = returnDs.Tables[0].Rows[i]["CreateUserCode"].ToString(); productionProcedureListEntity.Createtime = Convert.ToDateTime(returnDs.Tables[0].Rows[i]["Createtime"].ToString()); productionProcedureListEntitys.Add(productionProcedureListEntity); // productionDataEntitys[i].ProcedureDataList = productionProcedureListEntitys; } productionDataEntity.ProcedureDataList = new List(); productionDataEntity.ProcedureDataList = productionProcedureListEntitys; } return productionDataEntity; } catch (Exception ex) { ex.HelpLink = "barcode:" + barcode; //为了看哪个条码出现的问题 throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion #region 文件上传下载 #region 保存PDA图片 /// /// 保存临时图片 /// /// /// public static string SaveImg(Stream mageStream) { try { // 路径不存在 需要创建 if (!Directory.Exists(Constant.SYSTEM_TEMP_PATH)) { Directory.CreateDirectory(Constant.SYSTEM_TEMP_PATH); } string path = Constant.SYSTEM_TEMP_PATH + "/" + System.Guid.NewGuid().ToString() + Constant.SYSTEM_IMAGE_FORMAT; Image img = Bitmap.FromStream(mageStream); img.Save(System.AppDomain.CurrentDomain.BaseDirectory + path); return path; } catch (Exception ex) { throw ex; } } public static Stream GetImg(string path) { Image image = Image.FromFile(path);//这是你图片文件的 MemoryStream stream = new MemoryStream(); image.Save(stream, ImageFormat.Jpeg); //把图片保存到流中。 return stream; } #endregion /// /// PAD自动更新 /// /// public static Stream AutoUpgrade() { try { string path = INIUtility.Instance(INIUtility.IniFile.Config).ReadIniData("PathSetting", "UpgradeAPKPath");//System.Configuration.ConfigurationManager.AppSettings["UpgradeAPKPath"].Trim(); path = System.AppDomain.CurrentDomain.BaseDirectory + path; FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read); return fileStream; } catch (Exception ex) { throw ex; } } #endregion /// /// 由产品条码获取注浆信息 /// /// /// public static DataSet GetGroutingProducttByBarCode(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode, TP_PM_GroutingDailyDetail.GroutingCount as GroutingNum, TP_PM_GroutingDailyDetail.GroutingMouldCode as MouldCode, (select tp_pm_inproduction.ispublicbody from tp_pm_inproduction where tp_pm_inproduction.BarCode=:barcode) as ispublicbody, (select tp_pm_inproductiontrash.ispublicbody from tp_pm_inproductiontrash where tp_pm_inproductiontrash.BarCode=:barcode) as ispublicbodyTrach, TP_PM_GroutingDailyDetail.Groutingdate from TP_PM_GroutingDailyDetail where TP_PM_GroutingDailyDetail.BarCode=:barcode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barcode",barcode), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取工号下的所有工种信息 /// /// 工号ID /// 获取系统账套信息 /// 数据集 public static DataSet GetAllJobsByUserID(int UserID, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_MST_UserJobs.Userid, TP_MST_Jobs.JobsID as Jobs, TP_MST_Jobs.JobsName, TP_MST_Jobs.JobsCode from TP_MST_UserJobs left join TP_MST_Jobs on TP_MST_UserJobs.JobsID=TP_MST_Jobs.JobsID where TP_MST_UserJobs.Userid=:UserId and TP_MST_Jobs.Accountid=:Accountid order by TP_MST_Jobs.JobsCode "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":Accountid",sUserInfo.AccountID), new OracleParameter(":UserId",UserID), }; DataSet dsUserIsWorker = con.GetSqlResultToDs(sqlString, paras); return dsUserIsWorker; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据所选工号对应的工种，查出缺陷责任员工 /// /// 工种ID /// public static DataSet GetDutyStaffByUserID(int jobs, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,TP_HR_Staff.StaffStatus,TP_MST_UserStaff.UJobsID as SJobsID from TP_MST_UserStaff left join TP_HR_Staff on TP_MST_UserStaff.Staffid=TP_HR_Staff.Staffid where TP_MST_UserStaff.Ujobsid=:jobs and TP_HR_Staff.StaffStatus in (1,2) order by TP_HR_Staff.StaffCode "; //and TP_PM_Producer.UserID=:UserID and TP_PM_Producer.UJobsID=:JobsID OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":jobs",OracleDbType.Int32, jobs,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据所选工号，查出漏检责任员工 /// /// 工号 /// public static DataSet GetMissedStaffByUserID(int userid, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,TP_HR_Staff.StaffStatus,TP_MST_UserStaff.UJobsID as UJobsID,TP_HR_Staff.Jobs as SJobsID from TP_MST_UserStaff left join TP_HR_Staff on TP_MST_UserStaff.Staffid=TP_HR_Staff.Staffid where TP_MST_UserStaff.Userid=:userid "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":userid",OracleDbType.Int32, userid,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 查询产品信息 /// /// 请求信息 /// 产品信息 /// /// /// 王鑫 2014.12.09 新建 /// public static DataSet SerachGoods(SUserInfo sUserInfo, GoodsEntity goodsEntity) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("account",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("goodsID",OracleDbType.Int32, goodsEntity.GoodsID,ParameterDirection.Input), new OracleParameter("goodsCode",OracleDbType.NVarchar2, goodsEntity.GoodsCode,ParameterDirection.Input), new OracleParameter("goodsName",OracleDbType.NVarchar2, goodsEntity.GoodsName,ParameterDirection.Input), new OracleParameter("goodsSpecification",OracleDbType.NVarchar2, goodsEntity.GoodsSpecification,ParameterDirection.Input), new OracleParameter("goodsModel",OracleDbType.NVarchar2, goodsEntity.GoodsModel,ParameterDirection.Input), new OracleParameter("goodsTypeCode",OracleDbType.NVarchar2, goodsEntity.GoodsTypeCode,ParameterDirection.Input), new OracleParameter("glazeTypeID",OracleDbType.Int32, goodsEntity.GlazeTypeID,ParameterDirection.Input), new OracleParameter("ceaseFlag",OracleDbType.NVarchar2, goodsEntity.CeaseFlag,ParameterDirection.Input), new OracleParameter("remarks",OracleDbType.NVarchar2, goodsEntity.Remarks,ParameterDirection.Input), new OracleParameter("valueFlag",OracleDbType.NVarchar2, goodsEntity.ValueFlag,ParameterDirection.Input), new OracleParameter("rs_result",OracleDbType.RefCursor,ParameterDirection.Output), new OracleParameter("rs_result_img",OracleDbType.RefCursor,ParameterDirection.Output), }; foreach (OracleParameter item in paras) { if (item.Value + "" == "") { item.Value = DBNull.Value; } } DataSet ds = con.ExecStoredProcedure("PRO_MST_SerachGoods", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取用户列表 /// /// 用户实体 /// 获取系统账套信息 /// 数据集 public static DataSet SearchUserData(SUserEntity requestEntity, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_accountID",sUserInfo.AccountID), new OracleParameter("in_userID",sUserInfo.UserID), new OracleParameter("in_userCode",requestEntity.UserCode), new OracleParameter("in_userName",requestEntity.UserName), new OracleParameter("in_organizationID",requestEntity.OrganizationID), new OracleParameter("in_remarks",requestEntity.Remarks), new OracleParameter("in_limitMAC",requestEntity.LimitMAC), new OracleParameter("in_isWorker",requestEntity.IsWorker), new OracleParameter("rs_result",OracleDbType.RefCursor), new OracleParameter("in_ValueFlag",requestEntity.IsValueFlag), new OracleParameter("in_IsLocked",requestEntity.IsLocked), }; paras[8].Direction = ParameterDirection.Output; DataSet ds = con.ExecStoredProcedure("PRO_MST_SearchUser", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取数据字典管理的数据 /// /// 显示停用标识 /// 字典类别 /// /// /// 2014.12.03 新建 /// public static DataSet GetDictionaryData(byte Pvalue, string dictionaryType, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = String.Empty; Oracle.DataAccess.Client.OracleParameter[] oracleParameter = null; if (Pvalue.ToString() == Constant.INT_IS_ONE.ToString()) { sqlString = "Select DictionaryID,DictionaryType,DictionaryValue,DisplayNo,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime," + "UpdateUserID,OPTimeStamp from TP_MST_DataDictionary where DictionaryType = :DictionaryType and AccountID = :AccountID"; oracleParameter = new Oracle.DataAccess.Client.OracleParameter[] { new Oracle.DataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID), new Oracle.DataAccess.Client.OracleParameter(":DictionaryType",dictionaryType) }; } else if (Pvalue.ToString() == Constant.INT_IS_ZERO.ToString()) { sqlString = "Select DictionaryID,DictionaryType,DictionaryValue,DisplayNo,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime," + "UpdateUserID,OPTimeStamp from TP_MST_DataDictionary where valueflag = :valueflag and DictionaryType = :DictionaryType and AccountID = :AccountID"; oracleParameter = new Oracle.DataAccess.Client.OracleParameter[] { new Oracle.DataAccess.Client.OracleParameter(":valueflag",Constant.INT_IS_ONE.ToString()), new Oracle.DataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID), new Oracle.DataAccess.Client.OracleParameter(":DictionaryType",dictionaryType) }; } oracleConn.Open(); DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); oracleConn.Close(); return result; } catch (Exception ex) { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 使用存储过程PRO_PM_GetProductionData获取生产工序计件数据 /// /// 生产数据实体类 /// 用户基本信息 /// DataTable public static DataTable GetProductionData(SearchProductionDataEntity searchEntity, SUserInfo user) { //IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); //string procsql = "PRO_PM_GetProductionDataPDA"; //try //{ // IDataParameter[] paras = new OracleParameter[] // { // new OracleParameter("in_procedureID",OracleDbType.Int32, searchEntity.ProcedureID, ParameterDirection.Input), // new OracleParameter("in_barCode", OracleDbType.Varchar2,searchEntity.BarCode, ParameterDirection.Input), // new OracleParameter("in_goodsCode", OracleDbType.Varchar2,searchEntity.GoodsCode, ParameterDirection.Input), // new OracleParameter("in_goodsName", OracleDbType.Varchar2,searchEntity.GoodsName, ParameterDirection.Input), // new OracleParameter("in_userCode", OracleDbType.Varchar2,searchEntity.UserCode, ParameterDirection.Input), // new OracleParameter("in_organizationID", OracleDbType.Int32,searchEntity.OrganizationID, ParameterDirection.Input), // new OracleParameter("in_remarks", OracleDbType.Varchar2,searchEntity.Remarks, ParameterDirection.Input), // new OracleParameter("in_isReworked", OracleDbType.Varchar2,searchEntity.IsRework, ParameterDirection.Input), // new OracleParameter("in_begindate", OracleDbType.Date,searchEntity.BeginDate, ParameterDirection.Input), // new OracleParameter("in_enddate", OracleDbType.Date,searchEntity.EndDate, ParameterDirection.Input), // new OracleParameter("in_accountID", OracleDbType.Int32,user.AccountID, ParameterDirection.Input), // new OracleParameter("in_userID", OracleDbType.Int32,user.UserID, ParameterDirection.Input), // new OracleParameter("in_kilnCode", OracleDbType.Varchar2,searchEntity.KilnCode, ParameterDirection.Input), // new OracleParameter("in_kilnCarCode", OracleDbType.Varchar2,searchEntity.KilnCarCode, ParameterDirection.Input), // new OracleParameter("in_kilnCarPosition", OracleDbType.Int32,searchEntity.KilnCarPosition, ParameterDirection.Input), // new OracleParameter("in_currentUserID", OracleDbType.Int32,user.UserID, ParameterDirection.Input), // new OracleParameter("out_result", OracleDbType.RefCursor, ParameterDirection.Output), // new OracleParameter("in_goodsTypeCode", OracleDbType.Varchar2,searchEntity.GoodsTypeCode, ParameterDirection.Input), // }; // oracleConn.Open(); // DataSet returnDs = oracleConn.ExecStoredProcedure(procsql, paras); // if (returnDs != null && returnDs.Tables.Count > 0) // { // return returnDs.Tables[0]; // } // else // { // return null; // } //} //catch (Exception ex) //{ // throw ex; //} //finally //{ // if (oracleConn.ConnState == ConnectionState.Open) // { // oracleConn.Close(); // } //} IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); string procsql = "";//PRO_PM_GetProductionDataPDA procsql = @"SELECT TP_PM_ProductionDataIn.ProductionDataID FROM TP_PM_ProductionDataIn where 1=1 and ProcedureID=" + searchEntity.ProcedureID + " and barcode='" + searchEntity.BarCode + "' order by TP_PM_ProductionDataIn.ProductionDataid desc"; try { //IDataParameter[] paras = new OracleParameter[] //{ // new OracleParameter("in_procedureID",OracleDbType.Int32, searchEntity.ProcedureID, ParameterDirection.Input), // new OracleParameter("in_barCode", OracleDbType.Varchar2,searchEntity.BarCode, ParameterDirection.Input), // new OracleParameter("in_goodsCode", OracleDbType.Varchar2,searchEntity.GoodsCode, ParameterDirection.Input), // new OracleParameter("in_goodsName", OracleDbType.Varchar2,searchEntity.GoodsName, ParameterDirection.Input), // new OracleParameter("in_userCode", OracleDbType.Varchar2,searchEntity.UserCode, ParameterDirection.Input), // new OracleParameter("in_organizationID", OracleDbType.Int32,searchEntity.OrganizationID, ParameterDirection.Input), // new OracleParameter("in_remarks", OracleDbType.Varchar2,searchEntity.Remarks, ParameterDirection.Input), // new OracleParameter("in_isReworked", OracleDbType.Varchar2,searchEntity.IsRework, ParameterDirection.Input), // new OracleParameter("in_begindate", OracleDbType.Date,searchEntity.BeginDate, ParameterDirection.Input), // new OracleParameter("in_enddate", OracleDbType.Date,searchEntity.EndDate, ParameterDirection.Input), // new OracleParameter("in_accountID", OracleDbType.Int32,user.AccountID, ParameterDirection.Input), // new OracleParameter("in_userID", OracleDbType.Int32,user.UserID, ParameterDirection.Input), // new OracleParameter("in_kilnCode", OracleDbType.Varchar2,searchEntity.KilnCode, ParameterDirection.Input), // new OracleParameter("in_kilnCarCode", OracleDbType.Varchar2,searchEntity.KilnCarCode, ParameterDirection.Input), // new OracleParameter("in_kilnCarPosition", OracleDbType.Int32,searchEntity.KilnCarPosition, ParameterDirection.Input), // new OracleParameter("in_currentUserID", OracleDbType.Int32,user.UserID, ParameterDirection.Input), // new OracleParameter("out_result", OracleDbType.RefCursor, ParameterDirection.Output), // new OracleParameter("in_goodsTypeCode", OracleDbType.Varchar2,searchEntity.GoodsTypeCode, ParameterDirection.Input), //}; oracleConn.Open(); DataSet returnDs = oracleConn.GetSqlResultToDs(procsql); if (returnDs != null && returnDs.Tables.Count > 0) { return returnDs.Tables[0]; } else { return null; } } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 根据所选生产数据ID,显示成检数据信息 /// /// 生产数据ID /// DataSet public static DataSet GetProductionDataByID(int productionDataID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); DataSet dsReturn = new DataSet(); string sqlString = @"select TP_PM_ProductionDataIn.Barcode as BarCode, TP_PM_ProductionDataIn.Goodsid as GoodsID, TP_PM_ProductionDataIn.Goodscode as GoodsCode, TP_PM_ProductionDataIn.Goodsname as GoodsName, TP_PM_ProductionDataIn.GoodsLevelID as DefectFlagID, TP_PM_ProductionDataIn.Reworkprocedureid as ReworkProcedureID, TP_PM_ProductionDataIn.Remarks as Remarks, TP_PM_ProductionDataIn.Userid as UserID, TP_PM_ProductionDataIn.UserCode as UserCode, TP_PM_ProductionDataIn.UserName as UserName, TP_PM_ProductionDataIn.Goodsleveltypeid as GoodsLevelTypeID, TP_PM_ProductionDataIn.SpecialRepairflag, TP_PM_ProductionDataIn.IsReFire, TP_PM_ProductionDataIn.KilnCode, TP_PM_ProductionDataIn.KilnCarCode, TP_PM_ProductionDataIn.GroutingUserCode, TP_PM_ProductionDataIn.GroutingMouldCode as MouldCode, TP_PM_ProductionDataIn.GroutingNum, TP_PM_ProductionDataIn.GroutingDate, TP_PM_ProductionDataIn.IsPublicBody, TP_MST_Logo.logoid, TP_MST_Logo.logocode, TP_MST_Logo.logoname, TP_PM_ProductionDataIn.CreateTime, TP_PM_ProductionDataIn.CheckTime from TP_PM_ProductionDataIn left join TP_MST_Logo on TP_PM_ProductionDataIn.logoid=TP_MST_Logo.logoid where TP_PM_ProductionDataIn.Productiondataid=:ProductionDataID "; string sqlString2 = @" select TP_PM_Defect.ProductionDefectID as ProductionDefectID, TP_PM_Defect.Barcode as BarCode, TP_PM_Defect.DefectID as DefectID, TP_PM_Defect.Defectname as DefectName, TP_PM_Defect.Defectcode as DefectCode, TP_PM_Defect.Defectpositionid as DefectPositionID, TP_PM_Defect.Defectpositionname as DefectPositionName, TP_PM_Defect.Defectpositioncode as DefectPositionCode, TP_PM_Defect.Defectprocedureid as DefectProcedureID, TP_PM_Defect.Defectprocedurename as DefectProcedureName, TP_PM_Defect.Defectprocedurecode as DefectProcedureCode, TP_PM_Defect.Defectuserid as DefectUserID, TP_PM_Defect.Defectusername as DefectUserName, TP_PM_Defect.Defectusercode as DefectUserCode, TP_PM_Defect.DefectJobs as Jobs, nvl(TP_PM_Defect.MissedUserID,-1) as MissedUserID, TP_PM_Defect.MissedUserCode, TP_PM_Defect.MissedUserName, TP_MST_Jobs.Jobsname as JobsText, TP_PM_Defect.Remarks as DefectRemarks, TP_PM_Defect.Productiondataid as ProductionDataID, nvl(TP_PM_Defect.DefectProductionDataID,0) as DefectProductionDataID, TP_PM_Defect.Defectfine as DefectFineID, TP_MST_DefectFine.DefectFineCode as DefectFineValue, TP_PM_Defect.SpecialDefect, TP_PM_Defect.DefectDeductionNum, TP_PM_Defect.CheckTime from TP_PM_Defect left join TP_MST_Jobs on TP_PM_Defect.Defectjobs=TP_MST_Jobs.JobsID left join TP_MST_DefectFine on TP_PM_Defect.Defectfine= TP_MST_DefectFine.DefectFineid where TP_PM_Defect.Productiondataid =:ProductionDataID"; string sqlString3 = @"select TP_PM_DefectResponsible.ProductionDefectID as ProductionDefectID, TP_PM_DefectResponsible.Staffid as StaffID, TP_HR_Staff.Staffcode as StaffCode, TP_HR_Staff.Staffname as StaffName, TP_PM_DefectResponsible.Staffstatus as StaffStatus, TP_PM_DefectResponsible.UJobsID, TP_PM_DefectResponsible.SJobsID from TP_PM_DefectResponsible left join TP_HR_Staff on TP_PM_DefectResponsible.StaffID=TP_HR_Staff.Staffid where TP_PM_DefectResponsible.Productiondefectid in ( select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID )"; string sqlString4 = @" select TP_PM_DefectImage.ProductionDefectID, TP_PM_DefectImage.Thumbnailpath, TP_PM_DefectImage.Imagepath from TP_PM_DefectImage where TP_PM_DefectImage.Productiondefectid in ( select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID )"; string sqlString5 = @"select TP_PM_DefectMissedResponsible.ProductionDefectID as ProductionDefectID, TP_PM_DefectMissedResponsible.Staffid as StaffID, TP_HR_Staff.Staffcode as StaffCode, TP_HR_Staff.Staffname as StaffName, TP_PM_DefectMissedResponsible.Staffstatus as StaffStatus, TP_PM_DefectMissedResponsible.UJobsID, TP_PM_DefectMissedResponsible.SJobsID from TP_PM_DefectMissedResponsible left join TP_HR_Staff on TP_PM_DefectMissedResponsible.StaffID=TP_HR_Staff.Staffid where TP_PM_DefectMissedResponsible.Productiondefectid in ( select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID )"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":ProductionDataID",OracleDbType.Int32, productionDataID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); ds.Tables[0].TableName = "TP_PM_ProductionData"; DataSet ds2 = con.GetSqlResultToDs(sqlString2, paras); ds2.Tables[0].TableName = "TP_PM_Defect"; DataSet ds3 = con.GetSqlResultToDs(sqlString3, paras); ds3.Tables[0].TableName = "TP_PM_DefectResponsible"; DataSet ds4 = con.GetSqlResultToDs(sqlString4, paras); ds4.Tables[0].TableName = "TP_PM_DefectImage"; DataSet ds5 = con.GetSqlResultToDs(sqlString5, paras); ds5.Tables[0].TableName = "TP_PM_DefectMissedResponsible"; dsReturn.Tables.Add(ds.Tables[0].Copy()); dsReturn.Tables.Add(ds2.Tables[0].Copy()); dsReturn.Tables.Add(ds3.Tables[0].Copy()); dsReturn.Tables.Add(ds4.Tables[0].Copy()); dsReturn.Tables.Add(ds5.Tables[0].Copy()); return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 绑定图片 /// /// public static Stream BindDefectImage(string imagePath) { try { FileStream fileStream = new FileStream(System.AppDomain.CurrentDomain.BaseDirectory + imagePath, FileMode.Open, FileAccess.Read, FileShare.Read); return fileStream; } catch (Exception ex) { throw ex; } } /// /// 根据所选工号对应的工种，查出缺陷责任员工 /// /// 工种ID /// 用户ID /// 用户基本信息 /// DataSet public static DataSet GetDutyStaffByUserJobsID(int jobs, SUserInfo sUserInfo, int userid) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,TP_HR_Staff.StaffStatus,TP_MST_UserStaff.UJobsID as SJobsID from TP_MST_UserStaff inner join TP_HR_Staff on TP_MST_UserStaff.Staffid=TP_HR_Staff.Staffid where TP_MST_UserStaff.Ujobsid=:jobs and TP_MST_UserStaff.Userid=:userid and TP_HR_Staff.valueflag = '1' and TP_HR_Staff.StaffStatus in (1,2) "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":jobs",OracleDbType.Int32, jobs,ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, userid,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #region 产品报废 /// /// 验证废弃产品唯一性 /// /// 产品条码 /// int public static string ScrapProductChack(string BarCode, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // 当前工号是否有条码所在工序的操作权限 string sqlString = "SELECT inp.procedureid\n" + " ,p.procedurename\n" + " ,p.MODELTYPE\n" + " ,(SELECT 1\n" + " FROM tp_mst_userpurview t\n" + " WHERE t.purviewtype = 10\n" + " AND t.userid = :userid\n" + " AND (t.purviewid = -1 OR t.purviewid = inp.procedureid)\n" + " AND rownum = 1) userpurview\n" + " FROM tp_pm_inproduction inp\n" + " LEFT JOIN tp_pc_procedure p\n" + " ON p.procedureid = inp.procedureid\n" + " WHERE inp.barcode = :barcode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":userid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":barcode",OracleDbType.Varchar2, BarCode,ParameterDirection.Input), }; DataTable dt = con.GetSqlResultToDt(sqlString, paras); if (dt == null || dt.Rows.Count == 0) { // 条码不在产。 return "2"; } object userpurview = dt.Rows[0]["userpurview"]; if (userpurview == null || userpurview == DBNull.Value) { return "3:" + dt.Rows[0]["procedurename"]; } int MODELTYPE = Convert.ToInt32(dt.Rows[0]["MODELTYPE"]); if (MODELTYPE == 1 || MODELTYPE == 2 || MODELTYPE == 3 || MODELTYPE == 6) { return "4:该产品处于无法报损的特殊工序中"; } /* string strSql = @"Select * from TP_PM_ScrapProduct where ValueFlag=1 and BarCode='" + BarCode + "' and (AuditStatus=1)"; //AuditStatus=0 or DataSet dsReturn = con.GetSqlResultToDs(strSql); if (dsReturn.Tables[0].Rows.Count == 0) { return 1; } else { return 0; } */ #region 判断产品是否被挂起 sqlString = "SELECT 1 AS RES\n" + " FROM TP_PM_PRODUCTSUSPEND PS\n" + " INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD\n" + " ON GDD.GROUTINGDAILYDETAILID = PS.GROUTINGDAILYDETAILID\n" + " WHERE GDD.BARCODE = :Barcode"; OracleParameter[] pars = new OracleParameter[] { new OracleParameter(":Barcode",OracleDbType.NVarchar2, BarCode,ParameterDirection.Input), }; object objResult = con.GetSqlResultToObj(sqlString, pars); if (objResult != null) { return "4:该产品被挂起"; } #endregion string strSql = "select GoodsLevelTypeID,AuditStatus from TP_PM_ScrapProduct " + " where BarCode='" + BarCode + "'and CreateTime=(select max(CreateTime) " + " from TP_PM_ScrapProduct " + " where BarCode='" + BarCode + "' and ValueFlag=1 and (AuditStatus = 0 or AuditStatus = 1) and recyclingflag=0)" + " and ValueFlag=1 and (AuditStatus = 0 or AuditStatus = 1) and recyclingflag=0"; DataSet dsReturn = con.GetSqlResultToDs(strSql); if (dsReturn.Tables[0].Rows.Count == 0) { return "1"; } else { if (dsReturn.Tables[0].Rows[0]["GOODSLEVELTYPEID"].ToString().Equals("9") && dsReturn.Tables[0].Rows[0]["AUDITSTATUS"].ToString().Equals("1")) { return "1"; } else if (dsReturn.Tables[0].Rows[0]["GOODSLEVELTYPEID"].ToString() != "9" && dsReturn.Tables[0].Rows[0]["AUDITSTATUS"].ToString().Equals("1")) { return "0"; } else { return "1"; } } } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据条码获取该产品的在产信息以及生产数据 /// /// 产品条码 /// 装有两个DataTable的数据集，第一个是在产信息，第二个是生产数据 public static DataSet GetInProductionAndProductionData(string BarCode, SUserInfo sUserInfo) { DataSet dsList = new DataSet(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); StringBuilder sbSql = new StringBuilder(); ////sbSql.Append("Select inp.*,pt.ProcedureModelTypeID from TP_PM_InProduction inp "); ////sbSql.Append(" Inner join TP_PC_Procedure p"); //////sbSql.Append(" On inp.CompleteProcedureID = p.ProcedureID"); ////sbSql.Append(" On inp.FlowProcedureID = p.ProcedureID"); ////sbSql.Append(" Inner join TP_SYS_ProcedureModelType pt"); ////sbSql.Append(" On p.ModelType = pt.ProcedureModelTypeID"); ////sbSql.Append(" where inp.Barcode=:Barcode and inp.ValueFlag=1"); //sbSql.Append("Select inp.*,inp.ModelType as ProcedureModelTypeID,1 as endmode from TP_PM_InProduction inp "); //sbSql.Append(" where inp.Barcode=:Barcode and inp.ValueFlag=1"); sbSql.Append("Select inp.*, p.ProcedureCode,p.ProcedureName, inp.ModelType as ProcedureModelTypeID,1 as endmode from TP_PM_InProduction inp "); sbSql.Append(" inner join tp_pc_Procedure p on p.ProcedureID = inp.ProcedureID "); sbSql.Append(" where inp.Barcode=:Barcode and inp.ValueFlag=1"); OracleParameter[] Paras = new OracleParameter[]{ new OracleParameter(":Barcode",OracleDbType.NVarchar2, BarCode,ParameterDirection.Input), }; DataTable dtProduction = con.GetSqlResultToDt(sbSql.ToString(), Paras); if (dtProduction != null) { if (dtProduction.Rows.Count != 0) { dsList.Tables.Add(dtProduction); } else { //在产产品不存在的话，看看是不是变成了成品 sbSql.Clear(); sbSql.Append("Select fp.*,2 as endmode from TP_PM_FinishedProduct fp "); sbSql.Append(" where fp.Barcode=:Barcode and fp.ValueFlag=1"); dtProduction = con.GetSqlResultToDt(sbSql.ToString(), Paras); if (dtProduction != null) { dsList.Tables.Add(dtProduction); } } } else { return null; } //sbSql.Clear(); //sbSql.Append("Select distinct ProductionDataID,ProcedureID,ProcedureName,ProcedureCode from TP_PM_ProductionDataIn where Barcode=:Barcode and ValueFlag=1 order by ProductionDataid"); DataSet sysSetting = CommonModuleLogic.CommonModuleLogic.GetSysSettingBySettingType("S_PM_015", sUserInfo); string spm015 = null; if (sysSetting != null && sysSetting.Tables.Count > 0 && sysSetting.Tables[0].Rows.Count > 0) { spm015 = sysSetting.Tables[0].Rows[0]["SettingValue"] + ""; } sbSql.Clear(); sbSql.Append("Select ProductionDataID,ProcedureID,ProcedureName,ProcedureCode from TP_PM_ProductionDataIn where Barcode=:Barcode and ValueFlag=1 "); // 报损不能选成型责任 if ("0" == spm015) { sbSql.Append(" and MODELTYPE <> 5"); } sbSql.Append(" order by ProductionDataid"); DataTable dtProductionData = con.GetSqlResultToDt(sbSql.ToString(), Paras); if (dtProductionData != null) { dsList.Tables.Add(dtProductionData); } else { return null; } return dsList; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据工号查询员工档案信息 /// /// 员工ID /// 用户基本信息 /// DataSet /// /// 作者 日期 内容 /// 冯雪 2014-9-23 新建 /// public static DataSet SearchHrStaffInfo(int userId, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = " SELECT TUser.userid,Staff.Staffid,Staff.Staffname,Staff.Organizationid," + " Staff.Jobs,Staff.Staffstatus,Staff.Staffcode,'' Remarks," + " (CASE Staff.staffStatus" + " WHEN 0 THEN '未入职'" + " WHEN 1 THEN '试用 '" + " WHEN 2 THEN '转正'" + " WHEN 3 THEN '离职'" + " ELSE '' END) AS staffStatusName," + " Jobs.Jobsname,Org.Organizationname,TUser.UserCode,TUserJobs.Jobsname as UJobsName,TUserJobs.JobsId as UJobsId" + " FROM TP_HR_Staff Staff " + " LEFT JOIN TP_MST_Jobs Jobs ON Jobs.Jobsid = Staff.Jobs " + " LEFT JOIN TP_MST_Organization Org ON Org.Organizationid = Staff.Organizationid " + " LEFT JOIN TP_MST_UserStaff TUserStaff on TUserStaff.StaffID = Staff.StaffID " + " LEFT JOIN TP_MST_User TUser on TUserStaff.Userid = TUser.Userid" + " LEFT JOIN TP_MST_Jobs TUserJobs on TUserJobs.Jobsid = TUserStaff.Ujobsid" + " WHERE Staff.Accountid = :accountID " + " AND TUser.Userid = :userId" + " AND Staff.Staffstatus in(1,2)"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":userId", OracleDbType.Int32, userId, ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(strSql, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 添加废弃产品记录 /// /// 废弃产品实体 /// 废弃责任工序实体 /// 责任者集合 /// 用户基本信息 /// int结果返回值 /// /// 庄天威 2014.09.24 新建 /// public static int AddScrapProduct(ScrapProductEntity SProductEntity, ResponProcedureEntity RProcedureEntity, ScrapResponsibleEntity[] SResponsibleList, SUserInfo userInfo) { int ll = 0; int returnRows = 0; int sresponsibleReturnRows = 0; int rprocedureReturn = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); //#region 判断产品是否被挂起 //string sqlString = "SELECT 1 AS RES\n" + // " FROM TP_PM_PRODUCTSUSPEND PS\n" + // " INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD\n" + // " ON GDD.GROUTINGDAILYDETAILID = PS.GROUTINGDAILYDETAILID\n" + // " WHERE GDD.BARCODE = :Barcode"; //OracleParameter[] pars = new OracleParameter[] //{ // new OracleParameter(":Barcode",OracleDbType.NVarchar2, SProductEntity.BarCode,ParameterDirection.Input), //}; //object objResult = oracleTrConn.GetSqlResultToObj(sqlString, pars); //if (objResult != null) //{ // return Constant.INT_IS_NEGATIE_ONE; //} //#endregion object result = DBNull.Value; ll = 1; if (SProductEntity.AuditStatus != 0) { ll = 2; //string strSql1 = "select FUN_CMN_GetAccountDate(:accountId) From DUAL"; string strSql1 = "select sysdate from dual"; // Oracle.DataAccess.Client.OracleParameter[] paras1 = new Oracle.DataAccess.Client.OracleParameter[] //{ // new Oracle.DataAccess.Client.OracleParameter(":accountId",userInfo.AccountID) //}; object strResult = oracleTrConn.GetSqlResultToObj(strSql1); if (strResult == null || strResult == DBNull.Value) { // 服务器时间错误 throw new Exception("SystemDateTimeError"); } result = Convert.ToDateTime(strResult); } ll = 3; // wangx 20156/01/13 int? ProductionDataID = null; int? completeProcdureID = null; string completeProcdureName = string.Empty; string completeProcdureCode = string.Empty; //string sqlProductionDataID = @"select tp_pm_inproduction.productiondataid, // tp_pc_procedure.procedureid, // tp_pc_procedure.procedurename, // tp_pc_procedure.procedurecode // from tp_pm_inproduction // left join tp_pc_procedure // on tp_pm_inproduction.procedureid=tp_pc_procedure.procedureid // where tp_pm_inproduction.barcode = :barcode // union // select tp_pm_inproductiontrash.productiondataid, // tp_pc_procedure.procedureid, // tp_pc_procedure.procedurename, // tp_pc_procedure.procedurecode // from tp_pm_inproductiontrash // left join tp_pc_procedure // on tp_pm_inproductiontrash.procedureid=tp_pc_procedure.procedureid // where tp_pm_inproductiontrash.barcode = :barcode // "; string sqlProductionDataID = @"select tp_pm_inproduction.productiondataid, tp_pc_procedure.procedureid, tp_pc_procedure.procedurename, tp_pc_procedure.procedurecode, tp_pm_inproduction.InScrapFlag from tp_pm_inproduction left join tp_pc_procedure on tp_pm_inproduction.procedureid=tp_pc_procedure.procedureid where tp_pm_inproduction.barcode = :barcode for update"; OracleParameter[] ParasProductionDataID = new OracleParameter[] { new OracleParameter(":Barcode",OracleDbType.NVarchar2, SProductEntity.BarCode,ParameterDirection.Input), }; ll = 4; DataSet dsProductionDataID = oracleTrConn.GetSqlResultToDs(sqlProductionDataID, ParasProductionDataID); if (dsProductionDataID != null && dsProductionDataID.Tables[0].Rows.Count > 0) { ProductionDataID = Convert.ToInt32(dsProductionDataID.Tables[0].Rows[0]["productiondataid"]); completeProcdureID = Convert.ToInt32(dsProductionDataID.Tables[0].Rows[0]["procedureid"]); completeProcdureName = dsProductionDataID.Tables[0].Rows[0]["procedurename"].ToString(); completeProcdureCode = dsProductionDataID.Tables[0].Rows[0]["procedurecode"].ToString(); string InScrapFlag = dsProductionDataID.Tables[0].Rows[0]["InScrapFlag"].ToString(); if (InScrapFlag == "1") { return Constant.RETURN_IS_DATACHANGED; } } else { return Constant.RETURN_IS_DATACHANGED; } // end ll = 5; // 如果完成工序ID发生变化,则返回提示； if (completeProcdureID != SProductEntity.ProcedureID) { return Constant.RETURN_IS_DATACHANGED; } //首先添加废弃产品主体信息 StringBuilder sbSql = new StringBuilder(); //获取序列ID sbSql.Clear(); sbSql.Append("select SEQ_PM_ScrapProduct_ID.nextval from dual"); int id = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_PM_ScrapProduct"); sbSql.Append("(ScrapProductID,Barcode,ProductionLineID,ProductionLineCode,ProductionLineName,"); sbSql.Append("GoodsID,GoodsCode,GoodsName,GroutingDailyID,GroutingDailyDetailID,"); sbSql.Append("GroutingDate,GroutingLineID,GroutingLineCode,GroutingLineName,"); sbSql.Append("GMouldTypeID,GroutingLineDetailID,GroutingMouldCode,MouldCode,"); sbSql.Append("GoodsLevelID,GoodsLevelTypeID,ResponType,ScrapDate,Rreason,"); sbSql.Append("Remarks,AuditStatus,"); sbSql.Append("SettlementFlag,AccountID,ValueFlag,CreateTime,CreateUserID,"); sbSql.Append("UpdateTime,UpdateUserID,OPTimeStamp,GroutingUserID,GroutingUserCode,GroutingNum,IsPublicBody,IsReFire,ScrapFine,Auditor,AuditDate,SpecialRepairflag,Procedureid,ProcedureName,ProcedureCode,logoid,ProductionDataID)"); sbSql.Append("values(:ScrapProductID,:Barcode,:ProductionLineID,:ProductionLineCode,:ProductionLineName,"); sbSql.Append(":GoodsID,:GoodsCode,:GoodsName,:GroutingDailyID,:GroutingDailyDetailID,"); sbSql.Append(":GroutingDate,:GroutingLineID,:GroutingLineCode,:GroutingLineName,"); sbSql.Append(":GMouldTypeID,:GroutingLineDetailID,:GroutingMouldCode,:MouldCode,"); sbSql.Append(":GoodsLevelID,:GoodsLevelTypeID,:ResponType,:ScrapDate,:Rreason,"); sbSql.Append(":Remarks,:AuditStatus,"); sbSql.Append("0,:AccountID,1,sysdate,:CreateUserID,"); sbSql.Append("sysdate,:UpdateUserID,systimestamp,:GroutingUserID,:GroutingUserCode,:GroutingNum,:IsPublicBody,:IsReFire,:ScrapFine,:Auditor,:AuditlDate,:SpecialRepairflag,:Procedureid,:ProcedureName,:ProcedureCode,:logoid,:ProductionDataID)"); OracleParameter[] Paras = new OracleParameter[] { new OracleParameter(":ScrapProductID",OracleDbType.Int32, id,ParameterDirection.Input), new OracleParameter(":Barcode",OracleDbType.NVarchar2, SProductEntity.BarCode,ParameterDirection.Input), new OracleParameter(":ProductionLineID",OracleDbType.Int32, SProductEntity.ProductionLineID,ParameterDirection.Input), new OracleParameter(":ProductionLineCode",OracleDbType.NVarchar2, SProductEntity.ProductionLineCode,ParameterDirection.Input), new OracleParameter(":ProductionLineName",OracleDbType.NVarchar2, SProductEntity.ProductionLineName,ParameterDirection.Input), new OracleParameter(":GoodsID",OracleDbType.Int32, SProductEntity.GoodsID,ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.NVarchar2, SProductEntity.GoodsCode,ParameterDirection.Input), new OracleParameter(":GoodsName",OracleDbType.NVarchar2, SProductEntity.GoodsName,ParameterDirection.Input), new OracleParameter(":GroutingDailyID",OracleDbType.Int32, SProductEntity.GroutingDailyID,ParameterDirection.Input), new OracleParameter(":GroutingDailyDetailID",OracleDbType.Int32, SProductEntity.GroutingDailyDetailID,ParameterDirection.Input), new OracleParameter(":GroutingDate",OracleDbType.Date, Convert.ToDateTime(SProductEntity.GroutingDatePDA),ParameterDirection.Input), new OracleParameter(":GroutingLineID",OracleDbType.Int32, SProductEntity.GroutingLineID,ParameterDirection.Input), new OracleParameter(":GroutingLineCode",OracleDbType.NVarchar2, SProductEntity.GroutingLineCode,ParameterDirection.Input), new OracleParameter(":GroutingLineName",OracleDbType.NVarchar2, SProductEntity.GroutingLineName,ParameterDirection.Input), new OracleParameter(":GMouldTypeID",OracleDbType.Int32, SProductEntity.GMouldTypeID,ParameterDirection.Input), new OracleParameter(":GroutingLineDetailID",OracleDbType.Int32, SProductEntity.GroutingLineDetailID,ParameterDirection.Input), new OracleParameter(":GroutingMouldCode",OracleDbType.NVarchar2, SProductEntity.GroutingMouldCode,ParameterDirection.Input), new OracleParameter(":MouldCode",OracleDbType.NVarchar2, SProductEntity.MouldCode,ParameterDirection.Input), new OracleParameter(":GoodsLevelID",OracleDbType.Int32, SProductEntity.GoodsLevelID,ParameterDirection.Input), new OracleParameter(":GoodsLevelTypeID",OracleDbType.Int32, SProductEntity.GoodsLevelTypeID,ParameterDirection.Input), new OracleParameter(":ResponType",OracleDbType.Int32, Convert.ToInt32(SProductEntity.ResponType),ParameterDirection.Input), new OracleParameter(":ScrapDate",OracleDbType.Date, Convert.ToDateTime(SProductEntity.ScrapDatePDA),ParameterDirection.Input), new OracleParameter(":Rreason",OracleDbType.NVarchar2, SProductEntity.Rreason,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.NVarchar2, SProductEntity.Remarks,ParameterDirection.Input), new OracleParameter(":AuditStatus",OracleDbType.Int32, SProductEntity.AuditStatus,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":GroutingUserID",OracleDbType.Int32, SProductEntity.GroutingUserID,ParameterDirection.Input), new OracleParameter(":GroutingUserCode",OracleDbType.NVarchar2, SProductEntity.GroutingUserCode,ParameterDirection.Input), new OracleParameter(":GroutingNum",OracleDbType.Decimal, SProductEntity.GroutingNum,ParameterDirection.Input), new OracleParameter(":IsPublicBody",OracleDbType.Int32, SProductEntity.IsPublicBody,ParameterDirection.Input), new OracleParameter(":IsReFire",OracleDbType.Int32, SProductEntity.IsReFire,ParameterDirection.Input), new OracleParameter(":ScrapFine",OracleDbType.Decimal, SProductEntity.ScrapFine,ParameterDirection.Input), new OracleParameter(":Auditor",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":AuditlDate",OracleDbType.Date, result,ParameterDirection.Input), new OracleParameter(":SpecialRepairflag",OracleDbType.Int32, SProductEntity.SpecialRepairflag==null?0:SProductEntity.SpecialRepairflag,ParameterDirection.Input),//SpecialRepairflag new OracleParameter(":Procedureid",OracleDbType.Int32, completeProcdureID,ParameterDirection.Input), new OracleParameter(":ProcedureName",OracleDbType.Varchar2, completeProcdureName,ParameterDirection.Input), new OracleParameter(":ProcedureCode",OracleDbType.Varchar2, completeProcdureCode,ParameterDirection.Input), new OracleParameter(":logoid",OracleDbType.Int32, SProductEntity.LogoID==0?null:SProductEntity.LogoID,ParameterDirection.Input), new OracleParameter(":ProductionDataID",OracleDbType.Int32, ProductionDataID,ParameterDirection.Input), }; ll = 6; returnRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), Paras); ll = 7; //如果是直接审批通过 wangxin 2015-03-24 if (SProductEntity.AuditStatus == 1) { ll = 8; int DeleteRows = 0; string sqlInsert = @"insert into TP_PM_InProductionTrash ( BarCode, ProductionLineID, ProductionLineCode, ProductionLineName, ProcedureModel, ModelType, DefectFlag, ReworkProcedureID, IsPublicBody, IsReFire, GoodsLevelID, GoodsLevelTypeID, GoodsID, GoodsCode, GoodsName, UserID, GroutingDailyID, GroutingDailyDetailID, GroutingDate, GroutingLineID, GroutingLineCode, GroutingLineName, GMouldTypeID, CanManyTimes, GroutingLineDetailID, GroutingMouldCode, MouldCode, GroutingUserID, GroutingUserCode, GroutingNum, Remarks, KilnID, KilnCode, KilnName, KilnCarID, KilnCarCode, KilnCarName, KilnCarBatchNo, KilnCarPosition, AccountID, ValueFlag, CreateUserID, UpdateUserID, SpecialRepairflag, FlowProcedureID, FlowProcedureTime, ProcedureID, ProcedureTime, ProductionDataID, logoid, ISREWORKFLAG, SEMICHECKID ) select BarCode, ProductionLineID, ProductionLineCode, ProductionLineName, ProcedureModel, ModelType, DefectFlag, ReworkProcedureID, IsPublicBody, IsReFire, :GoodsLevelID, :GoodsLevelTypeID, GoodsID, GoodsCode, GoodsName, UserID, GroutingDailyID, GroutingDailyDetailID, GroutingDate, GroutingLineID, GroutingLineCode, GroutingLineName, GMouldTypeID, CanManyTimes, GroutingLineDetailID, GroutingMouldCode, MouldCode, GroutingUserID, GroutingUserCode, GroutingNum, Remarks, KilnID, KilnCode, KilnName, KilnCarID, KilnCarCode, KilnCarName, KilnCarBatchNo, KilnCarPosition, AccountID, ValueFlag, :CreateUserID, :UpdateUserID, SpecialRepairflag, FlowProcedureID, FlowProcedureTime, ProcedureID, ProcedureTime, ProductionDataID, logoid, ISREWORKFLAG, SEMICHECKID from TP_PM_InProduction where barcode='" + SProductEntity.BarCode + "'"; OracleParameter[] Paras2 = new OracleParameter[] { new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":GoodsLevelID",OracleDbType.Int32, SProductEntity.GoodsLevelID,ParameterDirection.Input), new OracleParameter(":GoodsLevelTypeID",OracleDbType.Int32, SProductEntity.GoodsLevelTypeID,ParameterDirection.Input) }; ll = 9; DeleteRows = oracleTrConn.ExecuteNonQuery(sqlInsert, Paras2); //20150714 modify wangx ll = 10; if (SProductEntity.ProcedureID != null)//在生产线上报废的, { sbSql.Clear(); sbSql.Append("update TP_PM_InProduction set inscrapflag='0' where BarCode='" + SProductEntity.BarCode + "'"); DeleteRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), null); } //20150714 modify wangx end ll = 11; sbSql.Clear(); sbSql.Append("Delete from TP_PM_InProduction where BarCode='" + SProductEntity.BarCode + "'"); DeleteRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), null); //如果没有删除在产产品，那么要去删除成品表 if (DeleteRows == 0) { sbSql.Clear(); sbSql.Append("Update TP_PM_FinishedProduct set ValueFlag = 0 where BarCode='" + SProductEntity.BarCode + "'"); DeleteRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), null); } //并且要把该产品的生产数据的最终状态添加上 //sbSql.Clear(); //sbSql.Append("Update TP_PM_ProductionData set GoodsEnding=8 where BarCode='" + SProductEntity.BarCode + "'"); //UpdateRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), null); } else { ll = 12; if (SProductEntity.ProcedureID != null)//在生产线上报废的, { sbSql.Clear(); if (SProductEntity.AuditStatus == 0) //待审核 { sbSql.Append("update TP_PM_InProduction set inscrapflag='1' where BarCode='" + SProductEntity.BarCode + "'"); } else { sbSql.Append("update TP_PM_InProduction set inscrapflag='0' where BarCode='" + SProductEntity.BarCode + "'"); } oracleTrConn.ExecuteNonQuery(sbSql.ToString(), null); } } ll = 13; //判断一下责任类型，如果是责任工序的话，则添加责任工序信息 if (Convert.ToInt32(SProductEntity.ResponType) == 3) { //插入产品废弃责任工序 sbSql.Clear(); sbSql.Append("select SEQ_PM_ResponProcedure_ID.nextval from dual"); RProcedureEntity.ResponProcedureID = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_PM_ResponProcedure"); sbSql.Append("(ResponProcedureID,ScrapProductID,Barcode,ProductionDataID,ProductionLineID,"); sbSql.Append("ProductionLineCode,ProductionLineName,"); sbSql.Append("ProcedureID,ProcedureCode,ProcedureName,UserID,UserCode,UserName,Remarks,AccountID,"); sbSql.Append("ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp)"); sbSql.Append("Values(:ResponProcedureID,:ScrapProductID,:Barcode,:ProductionDataID,:ProductionLineID,"); sbSql.Append(":ProductionLineCode,:ProductionLineName,"); sbSql.Append(":ProcedureID,:ProcedureCode,:ProcedureName,:UserID,:UserCode,:UserName,:Remarks,"); sbSql.Append(":AccountID,1,sysdate,:CreateUserID,sysdate,:UpdateUserID,systimestamp)"); OracleParameter[] RPParas = new OracleParameter[] { new OracleParameter(":ResponProcedureID",OracleDbType.NVarchar2, RProcedureEntity.ResponProcedureID,ParameterDirection.Input), new OracleParameter(":ScrapProductID",OracleDbType.Int32, id,ParameterDirection.Input), new OracleParameter(":Barcode",OracleDbType.NVarchar2, RProcedureEntity.BarCode,ParameterDirection.Input), new OracleParameter(":ProductionDataID",OracleDbType.NVarchar2, RProcedureEntity.ProductionDataID,ParameterDirection.Input), new OracleParameter(":ProductionLineID",OracleDbType.NVarchar2, RProcedureEntity.ProductionLineID,ParameterDirection.Input), new OracleParameter(":ProductionLineCode",OracleDbType.NVarchar2, RProcedureEntity.ProductionLineCode,ParameterDirection.Input), new OracleParameter(":ProductionLineName",OracleDbType.NVarchar2, RProcedureEntity.ProductionLineName,ParameterDirection.Input), new OracleParameter(":ProcedureID",OracleDbType.NVarchar2, RProcedureEntity.ProcedureID,ParameterDirection.Input), new OracleParameter(":ProcedureCode",OracleDbType.NVarchar2, RProcedureEntity.ProcedureCode,ParameterDirection.Input), new OracleParameter(":ProcedureName",OracleDbType.NVarchar2, RProcedureEntity.ProcedureName,ParameterDirection.Input), new OracleParameter(":UserID",OracleDbType.NVarchar2, RProcedureEntity.UserID,ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.NVarchar2, RProcedureEntity.UserCode,ParameterDirection.Input), new OracleParameter(":UserName",OracleDbType.NVarchar2, RProcedureEntity.UserName,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.NVarchar2, RProcedureEntity.Remarks,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.NVarchar2, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.NVarchar2, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.NVarchar2, userInfo.UserID,ParameterDirection.Input) }; ll = 14; rprocedureReturn = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), RPParas); #region 更新废弃责任工序的生产工序ID string sql = "UPDATE TP_PM_SCRAPPRODUCT SP\n" + " SET (SP.RESPONPROCEDUREPROCEDUREID) =\n" + " (SELECT RP.PROCEDUREID\n" + " FROM TP_PM_RESPONPROCEDURE RP\n" + " WHERE RP.SCRAPPRODUCTID = SP.SCRAPPRODUCTID)\n" + " WHERE SP.SCRAPTYPE IN (0, 2, 3)\n" + " AND SP.SCRAPPRODUCTID = :SCRAPPRODUCTID"; RPParas = new OracleParameter[] { new OracleParameter(":SCRAPPRODUCTID",OracleDbType.Int32,id,ParameterDirection.Input), }; rprocedureReturn = oracleTrConn.ExecuteNonQuery(sql, RPParas); #endregion } ll = 15; if (Convert.ToInt32(SProductEntity.ResponType) != 1) { //计算每个责任人的报废扣罚 decimal scrapfine = 0; if (SResponsibleList.Length > 0) { scrapfine = SProductEntity.ScrapFine / SResponsibleList.Length; } //循环插入产品废弃责任者 OracleParameter[] SPParas; foreach (ScrapResponsibleEntity spFor in SResponsibleList) { sbSql.Clear(); sbSql.Append("select SEQ_PM_ScrapResponsible_ID.nextval from dual"); spFor.ResponsibleID = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); int? ResponProcedureID = null; if (RProcedureEntity != null) { ResponProcedureID = RProcedureEntity.ResponProcedureID; } sbSql.Clear(); sbSql.Append("Insert into TP_PM_ScrapResponsible"); sbSql.Append("(ResponsibleID,ScrapProductID,ResponType,ResponProcedureID,Barcode,StaffID,UserID,UserCode,UJobsID,SJobsID,"); sbSql.Append("StaffStatus,AccountID,CreateTime,CreateUserID,"); sbSql.Append("UpdateTime,UpdateUserID,OPTimeStamp,Scrapfine)"); sbSql.Append("values(:ResponsibleID,:ScrapProductID,:ResponType,:ResponProcedureID,:Barcode,:StaffID,:UserID,:UserCode,:UJobsID,:SJobsID,"); sbSql.Append(":StaffStatus,:AccountID,sysdate,:CreateUserID,"); sbSql.Append("sysdate,:UpdateUserID,systimestamp,:Scrapfine)"); SPParas = new OracleParameter[] { new OracleParameter(":ResponsibleID",OracleDbType.Int32, spFor.ResponsibleID,ParameterDirection.Input), new OracleParameter(":ScrapProductID",OracleDbType.Int32, id,ParameterDirection.Input), new OracleParameter(":ResponType",OracleDbType.Int32, Convert.ToInt32(SProductEntity.ResponType),ParameterDirection.Input), new OracleParameter(":ResponProcedureID",OracleDbType.NVarchar2, ResponProcedureID,ParameterDirection.Input), new OracleParameter(":Barcode",OracleDbType.NVarchar2, spFor.BarCode,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, spFor.StaffID,ParameterDirection.Input), new OracleParameter(":UserID",OracleDbType.Int32, spFor.UserID,ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.NVarchar2, spFor.UserCode,ParameterDirection.Input), new OracleParameter(":UJobsID",OracleDbType.Int32, spFor.UJobsID,ParameterDirection.Input), new OracleParameter(":SJobsID",OracleDbType.Int32, spFor.SJobsID,ParameterDirection.Input), new OracleParameter(":StaffStatus",OracleDbType.Int32, spFor.StaffStatus,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":Scrapfine",OracleDbType.Decimal, scrapfine,ParameterDirection.Input) }; sresponsibleReturnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), SPParas); } #region 更新废弃责任工序的责任者ID和编码 string sql = "UPDATE TP_PM_SCRAPPRODUCT SP\n" + " SET (SP.RESPONSIBLEUSERID, SP.RESPONSIBLEUSERCODE) =\n" + " (SELECT SR.USERID, SR.USERCODE\n" + " FROM TP_PM_SCRAPRESPONSIBLE SR\n" + " WHERE SR.SCRAPPRODUCTID = SP.SCRAPPRODUCTID\n" + " GROUP BY SR.USERID, SR.USERCODE)\n" + " WHERE SP.SCRAPTYPE IN (0, 2, 3)\n" + " AND SP.SCRAPPRODUCTID = :SCRAPPRODUCTID"; SPParas = new OracleParameter[] { new OracleParameter(":SCRAPPRODUCTID",OracleDbType.Int32,id,ParameterDirection.Input), }; rprocedureReturn = oracleTrConn.ExecuteNonQuery(sql, SPParas); #endregion } ll = 16; if (returnRows == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } else { oracleTrConn.Commit(); oracleTrConn.Disconnect(); } } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw new Exception(ll.ToString(), ex); } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return returnRows; } /// /// 获取登陆帐户有无报损权限 /// /// 工号编码 /// public static int GetScrapFuntion(string usercode, SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; if (usercode.ToUpper() == "SYSTEM") { strSql = @"SELECT Functionlevel, FunctionCode, FunctionName, FullName, FunctionFlag, FunctionButtonFlag, FormName, ButtonName FROM TP_SYS_Function WHERE ValueFlag = 1 AND FunctionCode = '01' OR FunctionCode = '0101' OR FunctionCode = '010101'"; } else if (usercode.ToUpper() == "ADMIN") { strSql = @" SELECT Functionlevel, FunctionCode, FunctionName, FullName, FunctionFlag, FunctionButtonFlag, FormName, ButtonName FROM TP_SYS_Function WHERE ValueFlag = 1 AND FunctionCode LIKE '01%' AND FunctionCode <> '0101' AND FunctionCode <> '010101'"; } else { strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON (R.FunctionCode = F.FunctionCode OR R.FunctionCode = '[ALL]') WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and instr(F.FunctionCode, '060401') = 1"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; } DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取是否存在报损未审核产品 /// /// 产品条码 /// int public static int GetScrapProductAuditStatus(string BarCode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // wangx modify 2015/07/14 string strSql = "select inscrapflag from TP_PM_INPRODUCTION where barcode='" + BarCode + "'"; DataSet dsReturn = con.GetSqlResultToDs(strSql); if (dsReturn != null && dsReturn.Tables[0].Rows.Count > 0) { if (dsReturn.Tables[0].Rows[0]["inscrapflag"].ToString() == "0") { return 0; } else { strSql = @"Select ScrapProductID from TP_PM_ScrapProduct where ValueFlag=1 and BarCode='" + BarCode + "' and (AuditStatus=0 or AuditStatus=2) order by ScrapProductid desc "; //AuditStatus=0 or dsReturn = con.GetSqlResultToDs(strSql); if (dsReturn.Tables[0].Rows.Count == 0) { return 0; } else { return Convert.ToInt32(dsReturn.Tables[0].Rows[0]["ScrapProductID"]); } } } else { return 0; } // wangx modify 2015/07/14 end //string strSql = @"Select * from TP_PM_ScrapProduct where ValueFlag=1 // strSql = @"Select ScrapProductID from TP_PM_ScrapProduct where ValueFlag=1 // and BarCode='" + BarCode + "' and (AuditStatus=0 or AuditStatus=2) order by ScrapProductid desc "; //AuditStatus=0 or // dsReturn = con.GetSqlResultToDs(strSql); // if (dsReturn.Tables[0].Rows.Count == 0) // { // return 0; // } // else // { // return Convert.ToInt32(dsReturn.Tables[0].Rows[0]["ScrapProductID"]); // } } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取产品报损明细 /// /// 工号编码 /// public static DataSet GetScrapProductInfo(string usercode, SUserInfo userInfo) { return null; } #endregion /// /// 修改废弃产品记录 /// /// 修改的废弃产品实体 /// 修改的废弃责任工序实体 /// 修改的废弃责任人实体 /// 修改前的废弃责任人实体 /// 用户基本信息 /// int结果返回值 /// /// 庄天威 2014.09.24 新建 /// public static int UpdateScrapProduct(ScrapProductEntity UpdateSProductEntity, ResponProcedureEntity UpdateRProcedureEntity, ScrapResponsibleEntity[] UpdateSResponsibleList, ScrapResponsibleEntity[] YSResponsibleList, SUserInfo userInfo) { int returnRows = 0; int sresponsibleReturnRows = 0; int rprocedureReturn = 0; int deleteRprocedureReturn = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); //首先修改废弃产品主体信息 StringBuilder sbSql = new StringBuilder(); sbSql.Append("update TP_PM_ScrapProduct"); sbSql.Append(" set ScrapDate=to_date(:ScrapDate,'yyyy-mm-dd hh24:mi:ss'),"); sbSql.Append(" Rreason=:Rreason,"); sbSql.Append(" Remarks=:Remarks,"); sbSql.Append(" ResponType=:ResponType,"); sbSql.Append(" UpdateUserID=:UpdateUserID,"); sbSql.Append(" ScrapFine=:ScrapFine"); if (UpdateSProductEntity.AuditStatus != null) { sbSql.Append(" ,AuditStatus=" + UpdateSProductEntity.AuditStatus); } if (UpdateSProductEntity.AuditStatus == 0) //待审批 { sbSql.Append(" ,AuditDate=null,Auditor=null"); } else { object result = DBNull.Value; string strSql1 = "select FUN_CMN_GetAccountDate(:accountId) From DUAL"; Oracle.DataAccess.Client.OracleParameter[] paras1 = new Oracle.DataAccess.Client.OracleParameter[] { new Oracle.DataAccess.Client.OracleParameter(":accountId",userInfo.AccountID) }; object strResult = oracleTrConn.GetSqlResultToObj(strSql1, paras1); if (strResult == null || strResult == DBNull.Value) { // 服务器时间错误 throw new Exception("SystemDateTimeError"); } result = Convert.ToDateTime(strResult); sbSql.Append(string.Format(" ,AuditDate=to_date('{0}','yyyy-mm-dd hh24:mi:ss'),Auditor={1}", result.ToString().Replace("/", "-"), userInfo.UserID)); } sbSql.Append(" where ScrapProductID=:ScrapProductID"); OracleParameter[] Paras = new OracleParameter[] { new OracleParameter(":ScrapDate",OracleDbType.NVarchar2, UpdateSProductEntity.ScrapDatePDA.ToString(),ParameterDirection.Input), new OracleParameter(":Rreason",OracleDbType.NVarchar2, UpdateSProductEntity.Rreason,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.NVarchar2, UpdateSProductEntity.Remarks,ParameterDirection.Input), new OracleParameter(":ResponType",OracleDbType.Int32, UpdateSProductEntity.ResponType,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":ScrapProductID",OracleDbType.NVarchar2, UpdateSProductEntity.ScrapProductID,ParameterDirection.Input), new OracleParameter(":ScrapFine",OracleDbType.Decimal, UpdateSProductEntity.ScrapFine,ParameterDirection.Input) }; returnRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), Paras); //如果是直接审批通过 wangxin 2015-03-24 if (UpdateSProductEntity.AuditStatus == 1) { int DeleteRows = 0; string sqlInsert = @"insert into TP_PM_InProductionTrash ( BarCode, ProductionLineID, ProductionLineCode, ProductionLineName, ProcedureModel, ModelType, DefectFlag, ReworkProcedureID, IsPublicBody, IsReFire, GoodsLevelID, GoodsLevelTypeID, GoodsID, GoodsCode, GoodsName, UserID, GroutingDailyID, GroutingDailyDetailID, GroutingDate, GroutingLineID, GroutingLineCode, GroutingLineName, GMouldTypeID, CanManyTimes, GroutingLineDetailID, GroutingMouldCode, MouldCode, GroutingUserID, GroutingUserCode, GroutingNum, Remarks, KilnID, KilnCode, KilnName, KilnCarID, KilnCarCode, KilnCarName, KilnCarBatchNo, KilnCarPosition, AccountID, ValueFlag, CreateUserID, UpdateUserID, SpecialRepairflag, FlowProcedureID, FlowProcedureTime, ProcedureID, ProcedureTime, ProductionDataID, logoid, ISREWORKFLAG, SEMICHECKID ) select BarCode, ProductionLineID, ProductionLineCode, ProductionLineName, ProcedureModel, ModelType, DefectFlag, ReworkProcedureID, IsPublicBody, IsReFire, :GoodsLevelID, :GoodsLevelTypeID, GoodsID, GoodsCode, GoodsName, UserID, GroutingDailyID, GroutingDailyDetailID, GroutingDate, GroutingLineID, GroutingLineCode, GroutingLineName, GMouldTypeID, CanManyTimes, GroutingLineDetailID, GroutingMouldCode, MouldCode, GroutingUserID, GroutingUserCode, GroutingNum, Remarks, KilnID, KilnCode, KilnName, KilnCarID, KilnCarCode, KilnCarName, KilnCarBatchNo, KilnCarPosition, AccountID, ValueFlag, :CreateUserID, :UpdateUserID, SpecialRepairflag, FlowProcedureID, FlowProcedureTime, ProcedureID, ProcedureTime, ProductionDataID, logoid, ISREWORKFLAG, SEMICHECKID from TP_PM_InProduction where barcode='" + UpdateSProductEntity.BarCode + "'"; OracleParameter[] Paras2 = new OracleParameter[] { new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":GoodsLevelID",OracleDbType.Int32, UpdateSProductEntity.GoodsLevelID,ParameterDirection.Input), new OracleParameter(":GoodsLevelTypeID",OracleDbType.Int32, UpdateSProductEntity.GoodsLevelTypeID,ParameterDirection.Input) }; DeleteRows = oracleTrConn.ExecuteNonQuery(sqlInsert, Paras2); //20150714 modify wangx if (UpdateSProductEntity.ProcedureID != null)//在生产线上报废的, { sbSql.Clear(); sbSql.Append("update TP_PM_InProduction set inscrapflag='0' where BarCode='" + UpdateSProductEntity.BarCode + "'"); DeleteRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), null); } //20150714 modify wangx end sbSql.Clear(); sbSql.Append("Delete from TP_PM_InProduction where BarCode='" + UpdateSProductEntity.BarCode + "'"); DeleteRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), null); //如果没有删除在产产品，那么要去删除成品表 if (DeleteRows == 0) { sbSql.Clear(); sbSql.Append("Update TP_PM_FinishedProduct set ValueFlag = 0 where BarCode='" + UpdateSProductEntity.BarCode + "'"); DeleteRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), null); } //并且要把该产品的生产数据的最终状态添加上 //sbSql.Clear(); //sbSql.Append("Update TP_PM_ProductionData set GoodsEnding=8 where BarCode='" + UpdateSProductEntity.BarCode + "'"); //UpdateRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), null); } else { //if (UpdateSProductEntity.ProcedureID != null)//在生产线上报废的, //{ sbSql.Clear(); if (UpdateSProductEntity.AuditStatus == 0) //待审核 { sbSql.Append("update TP_PM_InProduction set inscrapflag='1' where BarCode='" + UpdateSProductEntity.BarCode + "'"); } else { sbSql.Append("update TP_PM_InProduction set inscrapflag='0' where BarCode='" + UpdateSProductEntity.BarCode + "'"); } oracleTrConn.ExecuteNonQuery(sbSql.ToString(), null); //} } //无论如何，要把原始的责任者数据删除 if (YSResponsibleList != null) { foreach (ScrapResponsibleEntity spFor in YSResponsibleList) { sbSql.Clear(); sbSql.Append("delete from TP_PM_ScrapResponsible"); sbSql.Append(" where ResponsibleID=:ResponsibleID "); OracleParameter[] SPParas = new OracleParameter[] { new OracleParameter(":ResponsibleID",OracleDbType.Int32, spFor.ResponsibleID,ParameterDirection.Input) }; deleteRprocedureReturn += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), SPParas); } } //判断一下责任类型，如果有责任工序的话，则修改责任工序信息 if (Convert.ToInt32(UpdateSProductEntity.ResponType) == 3) { if (UpdateRProcedureEntity != null) { if (UpdateRProcedureEntity.ResponProcedureID != null) { sbSql.Clear(); sbSql.Append("update TP_PM_ResponProcedure"); sbSql.Append(" set ProcedureID=:ProcedureID,"); sbSql.Append(" ProcedureCode=:ProcedureCode,"); sbSql.Append(" ProcedureName=:ProcedureName,"); sbSql.Append(" UserID=:UserID,"); sbSql.Append(" UserCode=:UserCode,"); sbSql.Append(" UserName=:UserName,"); sbSql.Append(" UpdateUserID=:UpdateUserID"); sbSql.Append(" where ResponProcedureID=:ResponProcedureID"); OracleParameter[] RPParas = new OracleParameter[] { new OracleParameter(":ProcedureID",OracleDbType.NVarchar2, UpdateRProcedureEntity.ProcedureID,ParameterDirection.Input), new OracleParameter(":ProcedureCode",OracleDbType.NVarchar2, UpdateRProcedureEntity.ProcedureCode,ParameterDirection.Input), new OracleParameter(":ProcedureName",OracleDbType.NVarchar2, UpdateRProcedureEntity.ProcedureName,ParameterDirection.Input), new OracleParameter(":UserID",OracleDbType.NVarchar2, UpdateRProcedureEntity.UserID,ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.NVarchar2, UpdateRProcedureEntity.UserCode,ParameterDirection.Input), new OracleParameter(":UserName",OracleDbType.NVarchar2, UpdateRProcedureEntity.UserName,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.NVarchar2, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":ResponProcedureID",OracleDbType.Int32, UpdateRProcedureEntity.ResponProcedureID,ParameterDirection.Input) }; rprocedureReturn = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), RPParas); } else { //插入产品废弃责任工序 sbSql.Clear(); sbSql.Append("select SEQ_PM_ResponProcedure_ID.nextval from dual"); UpdateRProcedureEntity.ResponProcedureID = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_PM_ResponProcedure"); sbSql.Append("(ResponProcedureID,ScrapProductID,Barcode,ProductionDataID,ProductionLineID,"); sbSql.Append("ProductionLineCode,ProductionLineName,"); sbSql.Append("ProcedureID,ProcedureCode,ProcedureName,UserID,UserCode,UserName,Remarks,AccountID,"); sbSql.Append("ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp)"); sbSql.Append("Values(:ResponProcedureID,:ScrapProductID,:Barcode,:ProductionDataID,:ProductionLineID,"); sbSql.Append(":ProductionLineCode,:ProductionLineName,"); sbSql.Append(":ProcedureID,:ProcedureCode,:ProcedureName,:UserID,:UserCode,:UserName,:Remarks,"); sbSql.Append(":AccountID,1,sysdate,:CreateUserID,sysdate,:UpdateUserID,systimestamp)"); OracleParameter[] RPParas = new OracleParameter[] { new OracleParameter(":ResponProcedureID",OracleDbType.NVarchar2, UpdateRProcedureEntity.ResponProcedureID,ParameterDirection.Input), new OracleParameter(":ScrapProductID",OracleDbType.Int32, UpdateSProductEntity.ScrapProductID,ParameterDirection.Input), new OracleParameter(":Barcode",OracleDbType.NVarchar2, UpdateRProcedureEntity.BarCode,ParameterDirection.Input), new OracleParameter(":ProductionDataID",OracleDbType.NVarchar2, UpdateRProcedureEntity.ProductionDataID,ParameterDirection.Input), new OracleParameter(":ProductionLineID",OracleDbType.NVarchar2, UpdateRProcedureEntity.ProductionLineID,ParameterDirection.Input), new OracleParameter(":ProductionLineCode",OracleDbType.NVarchar2, UpdateRProcedureEntity.ProductionLineCode,ParameterDirection.Input), new OracleParameter(":ProductionLineName",OracleDbType.NVarchar2, UpdateRProcedureEntity.ProductionLineName,ParameterDirection.Input), new OracleParameter(":ProcedureID",OracleDbType.NVarchar2, UpdateRProcedureEntity.ProcedureID,ParameterDirection.Input), new OracleParameter(":ProcedureCode",OracleDbType.NVarchar2, UpdateRProcedureEntity.ProcedureCode,ParameterDirection.Input), new OracleParameter(":ProcedureName",OracleDbType.NVarchar2, UpdateRProcedureEntity.ProcedureName,ParameterDirection.Input), new OracleParameter(":UserID",OracleDbType.NVarchar2, UpdateRProcedureEntity.UserID,ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.NVarchar2, UpdateRProcedureEntity.UserCode,ParameterDirection.Input), new OracleParameter(":UserName",OracleDbType.NVarchar2, UpdateRProcedureEntity.UserName,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.NVarchar2, UpdateRProcedureEntity.Remarks,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.NVarchar2, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.NVarchar2, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.NVarchar2, userInfo.UserID,ParameterDirection.Input) }; rprocedureReturn = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), RPParas); } } } else //责任类型改为不存在责任工序了，要把原来的删除 { if (UpdateRProcedureEntity != null) //本来就没有的话就不用删了 { if (UpdateRProcedureEntity.ValueFlag == 0) //直接删除即可 { sbSql.Clear(); sbSql.Append("Delete from TP_PM_ResponProcedure"); sbSql.Append(" where ResponProcedureID=:ResponProcedureID"); OracleParameter[] RPParas = new OracleParameter[] { new OracleParameter(":ResponProcedureID",OracleDbType.Int32, UpdateRProcedureEntity.ResponProcedureID,ParameterDirection.Input) }; rprocedureReturn = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), RPParas); } } } //如果存在修改的产品废弃责任者 if (Convert.ToInt32(UpdateSProductEntity.ResponType) != 1) { //计算每个责任人的报废扣罚 decimal scrapfine = 0; if (UpdateSResponsibleList.Length > 0) { scrapfine = UpdateSProductEntity.ScrapFine / UpdateSResponsibleList.Length; } //循环插入产品废弃责任者 foreach (ScrapResponsibleEntity spFor in UpdateSResponsibleList) { sbSql.Clear(); sbSql.Append("select SEQ_PM_ScrapResponsible_ID.nextval from dual"); spFor.ResponsibleID = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); int? ResponProcedureID = null; if (UpdateRProcedureEntity != null) { if (UpdateRProcedureEntity.ValueFlag != 0) { ResponProcedureID = UpdateRProcedureEntity.ResponProcedureID; } } sbSql.Clear(); sbSql.Append("Insert into TP_PM_ScrapResponsible"); sbSql.Append("(ResponsibleID,ResponType,ResponProcedureID,Barcode,ScrapProductID,StaffID,UserID,UserCode,UJobsID,SJobsID,"); sbSql.Append("StaffStatus,AccountID,CreateTime,CreateUserID,"); sbSql.Append("UpdateTime,UpdateUserID,OPTimeStamp,Scrapfine)"); sbSql.Append("values(:ResponsibleID,:ResponType,:ResponProcedureID,:Barcode,:ScrapProductID,:StaffID,:UserID,:UserCode,:UJobsID,:SJobsID,"); sbSql.Append(":StaffStatus,:AccountID,sysdate,:CreateUserID,"); sbSql.Append("sysdate,:UpdateUserID,systimestamp,:Scrapfine)"); OracleParameter[] SPParas = new OracleParameter[] { new OracleParameter(":ResponsibleID",OracleDbType.Int32, spFor.ResponsibleID,ParameterDirection.Input), new OracleParameter(":ResponType",OracleDbType.Int32, Convert.ToInt32(UpdateSProductEntity.ResponType),ParameterDirection.Input), new OracleParameter(":ResponProcedureID",OracleDbType.NVarchar2, ResponProcedureID,ParameterDirection.Input), new OracleParameter(":Barcode",OracleDbType.NVarchar2, spFor.BarCode,ParameterDirection.Input), new OracleParameter(":ScrapProductID",OracleDbType.Int32, UpdateSProductEntity.ScrapProductID,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, spFor.StaffID,ParameterDirection.Input), new OracleParameter(":UserID",OracleDbType.Int32, spFor.UserID,ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.NVarchar2, spFor.UserCode,ParameterDirection.Input), new OracleParameter(":UJobsID",OracleDbType.Int32, spFor.UJobsID,ParameterDirection.Input), new OracleParameter(":SJobsID",OracleDbType.Int32, spFor.SJobsID,ParameterDirection.Input), new OracleParameter(":StaffStatus",OracleDbType.Int32, spFor.StaffStatus,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":Scrapfine",OracleDbType.Decimal, scrapfine,ParameterDirection.Input) }; sresponsibleReturnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), SPParas); } } #region 更新废弃责任工序的生产工序ID string sql = "UPDATE TP_PM_SCRAPPRODUCT SP\n" + " SET (SP.RESPONPROCEDUREPROCEDUREID) =\n" + " (SELECT RP.PROCEDUREID\n" + " FROM TP_PM_RESPONPROCEDURE RP\n" + " WHERE RP.SCRAPPRODUCTID = SP.SCRAPPRODUCTID)\n" + " WHERE SP.SCRAPTYPE IN (0, 2, 3)\n" + " AND SP.SCRAPPRODUCTID = :SCRAPPRODUCTID"; Paras = new OracleParameter[] { new OracleParameter(":SCRAPPRODUCTID",OracleDbType.Int32,UpdateSProductEntity.ScrapProductID,ParameterDirection.Input), }; rprocedureReturn = oracleTrConn.ExecuteNonQuery(sql, Paras); #endregion #region 更新废弃责任工序的责任者ID和编码 sql = "UPDATE TP_PM_SCRAPPRODUCT SP\n" + " SET (SP.RESPONSIBLEUSERID, SP.RESPONSIBLEUSERCODE) =\n" + " (SELECT SR.USERID, SR.USERCODE\n" + " FROM TP_PM_SCRAPRESPONSIBLE SR\n" + " WHERE SR.SCRAPPRODUCTID = SP.SCRAPPRODUCTID\n" + " GROUP BY SR.USERID, SR.USERCODE)\n" + " WHERE SP.SCRAPTYPE IN (0, 2, 3)\n" + " AND SP.SCRAPPRODUCTID = :SCRAPPRODUCTID"; Paras = new OracleParameter[] { new OracleParameter(":SCRAPPRODUCTID",OracleDbType.Int32,UpdateSProductEntity.ScrapProductID,ParameterDirection.Input), }; rprocedureReturn = oracleTrConn.ExecuteNonQuery(sql, Paras); #endregion if (returnRows == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } else { oracleTrConn.Commit(); oracleTrConn.Disconnect(); } } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return returnRows; } /// /// 获取窑炉管理的全部数据 /// /// 用户基本信息 /// DataSet /// /// 2014.10.31 任海 新建 /// public static DataSet GetAllKilntInfo(SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { //获取窑炉管理数据 string sqlString = "Select KilnID,KilnCode,KilnName,KilnType,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp " + "from TP_MST_Kiln where AccountID = :AccountID and valueflag=1"; Oracle.DataAccess.Client.OracleParameter[] oracleParameter = new Oracle.DataAccess.Client.OracleParameter[] { new Oracle.DataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID) }; oracleConn.Open(); DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); oracleConn.Close(); return result; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 获取次品产品条码允许编辑 /// /// 产品条码 /// Datase public static DataSet GetSubstandardInfo(string barcode) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { //获取窑炉管理数据 string sqlString = "Select barcode from TP_PM_InProductionTrash where barcode=:barcode"; Oracle.DataAccess.Client.OracleParameter[] oracleParameter = new Oracle.DataAccess.Client.OracleParameter[] { new Oracle.DataAccess.Client.OracleParameter(":barcode",barcode) }; oracleConn.Open(); DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); oracleConn.Close(); return result; } catch (Exception ex) { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 获取产品条码是否重烧 /// /// 产品条码 /// Datase public static DataSet GetReFine(string barcode) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = "select IsReFire from tp_PM_ProductionData where ProductionDataid=(select max(ProductionDataid) from tp_PM_ProductionData where barcode=:barcode)"; //获取窑炉管理数据 //string sqlString = "Select IsReFire from TP_PM_InProduction where barcode=:barcode"; Oracle.DataAccess.Client.OracleParameter[] oracleParameter = new Oracle.DataAccess.Client.OracleParameter[] { new Oracle.DataAccess.Client.OracleParameter(":barcode",barcode) }; oracleConn.Open(); DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); oracleConn.Close(); return result; } catch (Exception ex) { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 获取登陆帐户有无报损权限 /// /// 工号编码 /// public static int GetFinishedProductEditFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON (R.FunctionCode = F.FunctionCode OR R.FunctionCode = '[ALL]') WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and instr(F.FunctionCode, '060701') = 1 /*F.FunctionCode='060701'*/"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取登陆帐户有无报损审批权限 /// /// 工号编码 /// public static int GetScrapProductFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON (R.FunctionCode = F.FunctionCode OR R.FunctionCode = '[ALL]') WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and instr(F.FunctionCode, '06040103') = 1 /*F.FunctionCode='06040103'*/"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取登陆帐户有变更条码权限 /// /// public static int GetBarCodeRecordFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON (R.FunctionCode = F.FunctionCode OR R.FunctionCode = '[ALL]') WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and instr(F.FunctionCode, '060202') = 1 /*F.FunctionCode='060202'*/"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 成检-校验生产工号 /// /// public static DataSet GetUserCode(string usercode, SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); DataSet dsReturn = new DataSet(); string strSql = ""; strSql = @"select userid,username,usercode from tp_mst_user where tp_mst_user.isworker=1 and valueflag=1 and accountid=:accountid and usercode=:usercode"; Oracle.DataAccess.Client.OracleParameter[] oracleParameter = new Oracle.DataAccess.Client.OracleParameter[] { new Oracle.DataAccess.Client.OracleParameter(":accountid",userInfo.AccountID), new Oracle.DataAccess.Client.OracleParameter(":usercode",usercode) }; DataSet ds = con.GetSqlResultToDs(strSql, oracleParameter); ds.Tables[0].TableName = "TUser"; //strSql = "Select DictionaryID,DictionaryValue" // + " from TP_MST_DataDictionary where valueflag = 1 and DictionaryType = 'ASE002' and AccountID = :AccountID"; //oracleParameter = new Oracle.DataAccess.Client.OracleParameter[] // { // new Oracle.DataAccess.Client.OracleParameter(":AccountID",userInfo.AccountID), // }; //DataSet ds2 = con.GetSqlResultToDs(strSql, oracleParameter); //ds2.Tables[0].TableName = "TDataDictionary"; if (!dsReturn.Tables.Contains("TUser")) { dsReturn.Tables.Add(ds.Tables[0].Copy()); } //if (!dsReturn.Tables.Contains("TDataDictionary")) //{ // dsReturn.Tables.Add(ds2.Tables[0].Copy()); //} return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 保存班次配置信息 /// /// 班次配置实体 /// 班次配置明细table /// 用户基本信息 /// >0 保存成功 else 失败 /// /// 作者 日期 内容 /// 冯雪 2014-9-24 新建 /// public static int SaveClassesSetting(ClassesSettingEntity setEntity, ClassesDetailSettingEntity[] setDetailEntity, SUserInfo sUserInfo) { int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); // 查询新插入的生产数据ID string sqlText = "SELECT SEQ_PC_ClassesSetting_ID.NextVAL FROM dual"; int classesSettingID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText)); // 插入班次配置表 string sqlString1 = " INSERT INTO TP_PC_ClassesSetting " + " (ClassesSettingID,AccountDate,UserID,UserCode, " + " Remarks,AccountID,CreateTime,CreateUserID,UpdateUserID) " + " VALUES (:ClassesSettingID,:AccountDate,:userID,:userCode, " + " :remarks,:accountID,sysdate,:createUserID,:updateUserID)"; OracleParameter[] parmeters1 = new OracleParameter[] { new OracleParameter(":ClassesSettingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input), new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":userID",OracleDbType.Int32,setEntity.UserID,ParameterDirection.Input), new OracleParameter(":userCode",OracleDbType.Varchar2,setEntity.UserCode,ParameterDirection.Input), new OracleParameter(":AccountDate",OracleDbType.Date,Convert.ToDateTime(setEntity.AccountDatePDA),ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.Varchar2,setEntity.Remarks,ParameterDirection.Input), new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":createUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":updateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), }; returnRows = oracleTrConn.ExecuteNonQuery(sqlString1, parmeters1); #region 保存班次配置明细信息 foreach (ClassesDetailSettingEntity entity in setDetailEntity) { if (entity.UJobsId.ToString() == "" && entity.StaffCode.ToString() == "") { continue; } string sqlInsertString = "INSERT INTO TP_PC_ClassesDetail " + " (ClassesSettingID,AccountDate,userid,usercode,ujobsid,staffid,sjobsid,staffstatus," + " remarks,accountid,createtime,createuserid,UpdateUserID,UJobsNum) " + "VALUES (:settingID,:AccountDate,:userid,:usercode,:ujobsid,:staffid,:sjobsid,:staffstatus," + " :remarks,:accountid,sysdate,:createuserid,:updateUserID,:uJobsNum)"; int UJobsNum = 0;//默认一个 foreach (ClassesDetailSettingEntity entitycount in setDetailEntity) { if (entitycount.UJobsId.ToString() == entity.UJobsId.ToString()) { UJobsNum++; } } OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":settingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32,entity.Userid.ToString(),ParameterDirection.Input), new OracleParameter(":usercode",OracleDbType.Varchar2,entity.Usercode.ToString(),ParameterDirection.Input), new OracleParameter(":ujobsid",OracleDbType.Int32,entity.UJobsId.ToString(),ParameterDirection.Input), new OracleParameter(":staffid",OracleDbType.Int32,entity.Staffid.ToString(),ParameterDirection.Input), new OracleParameter(":sjobsid",OracleDbType.Int32,entity.Jobs.ToString(),ParameterDirection.Input), new OracleParameter(":staffstatus",OracleDbType.Int32,entity.Staffstatus.ToString(),ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.Varchar2,entity.Remarks,ParameterDirection.Input), new OracleParameter(":AccountDate",OracleDbType.Date,Convert.ToDateTime(setEntity.AccountDatePDA),ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":createuserid",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":updateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":uJobsNum",OracleDbType.Decimal,UJobsNum,ParameterDirection.Input) }; returnRows = oracleTrConn.ExecuteNonQuery(sqlInsertString, oracleParameter); } #endregion oracleTrConn.Commit(); oracleTrConn.Disconnect(); return returnRows; } catch (Exception ex) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); throw ex; } finally { // 释放资源 if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } } /// /// 获取登陆帐户有班次配置权限 /// /// public static int GetClassesSettingFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON (R.FunctionCode = F.FunctionCode OR R.FunctionCode = '[ALL]') WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and instr(F.FunctionCode, '0404') = 1 /*F.FunctionCode='0404'*/"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #region PDA报表 /// /// 取得RPT040104画面（产成品质量分析表）的查询数据 /// /// 登录用户信息 /// 查询条件 /// 查询结果 public static ServiceResultEntity GetRPT040104SData(SUserInfo user, RPT040104_SE se) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); List parameters = new List(); string sql = PDAModuleLogic.GetRPT040104SSQL(user, se, ref parameters); DataTable data = conn.GetSqlResultToDt(sql, parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); if (data == null || data.Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sre.Data = new DataSet(); sre.Data.Tables.Add(data.Copy()); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 获取RPT040104画面（产成品质量分析表）的查询sql /// /// sql private static string GetRPT040104SSQL(SUserInfo user, RPT040104_SE se, ref List parameters) { parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); parameters.Add(new OracleParameter(":in_rptSprocedureId", OracleDbType.Int32, se.RptSProcedureID, ParameterDirection.Input)); // 2015-06-16 modify by chenxy string sqlString = "\n" + "select qdgroup.gid,\n" + //" decode(qdgroup.gid, 7, '总计', 0, qdgroup.productionlinename, '--') productionlinename,\n" + " decode(qdgroup.gid, 3, '总计', 1, '合计', qdgroup.kilncode) kilncode,\n" + //" decode(qdgroup.gid, 1, '小计', 0, qdgroup.goodstypename2, '--') goodstypename2,\n" + " decode(qdgroup.gid, 0, qdgroup.goodstypename2, '') subgoodstypecode,\n" + //" decode(qdgroup.gid, 0, qdgroup.goodstypename, '--') goodstypename,\n" + //" decode(qdgroup.gid, 0, qdgroup.goodscode, '--') goodscode,\n" + " to_char(qdgroup.OutKilnCount) OutKilnCount,\n" + //" qdgroup.GoodCount,\n" + // 质量是合格率（正品+副品） //" to_char((qdgroup.GoodCount / qdgroup.OutKilnCount) * 100, '990.00') || '%' GoodPercent\n" + " to_char(((qdgroup.GoodCount + qdgroup.BadCount) / qdgroup.OutKilnCount) * 100, '990.00') || '%' GoodPercent\n" + //" qdgroup.SubstandardCount,\n" + //" to_char((qdgroup.SubstandardCount / qdgroup.OutKilnCount) * 100,\n" + //" '990.00') || '%' SubstandardPercent,\n" + //" qdgroup.BadCount\n" + //" to_char((qdgroup.BadCount / qdgroup.OutKilnCount) * 100, '990.00') || '%' BadPercent,\n" + //" qdgroup.ReFireCount,\n" + //" to_char((qdgroup.ReFireCount / qdgroup.OutKilnCount) * 100,\n" + //" '990.00') || '%' ReFirePercent\n" + " from (select grouping_id(--pcpl.productionlinename,\n" + " mstkiln.kilncode,\n" + " mstgoodstype2.goodstypename) gid,\n" + //" mstgoodstype.goodstypename,\n" + //" mstgoods.goodscode) gid,\n" + //" pcpl.productionlinename,\n" + " --qd.kilnid,\n" + " mstkiln.kilncode,\n" + " mstgoodstype2.goodstypename goodstypename2,\n" + //" mstgoodstype.goodstypename,\n" + //" mstgoods.goodscode,\n" + " sum(qd.OutKilnCount) OutKilnCount,\n" + " sum(qd.GoodCount) GoodCount,\n" + //" sum(qd.SubstandardCount) SubstandardCount,\n" + " sum(qd.BadCount) BadCount\n" + //" sum(qd.ReFireCount) ReFireCount\n" + " from (select --pdbc.barcode,\n" + //" pdbc.productionlineid,\n" + " pdbc.goodsid,\n" + " pdbc.kilnid,\n" + " --pdbc.kilncarid,\n" + " --pdbc.kilncarbatchno,\n" + " 1 OutKilnCount,\n" + " case\n" + " when glt.goodsleveltypeid in (5, 6, 7) then\n" + " 0\n" + " else\n" + " 1\n" + " end GoodCount,\n" + //" case\n" + //" when glt.goodsleveltypeid = 7 then\n" + //" 1\n" + //" else\n" + //" 0\n" + //" end SubstandardCount,\n" + //" case\n" + //" when glt.goodsleveltypeid = 6 then\n" + //" 1\n" + //" else\n" + //" 0\n" + //" end ReFireCount,\n" + " case\n" + " when glt.goodsleveltypeid = 5 then\n" + " 1\n" + " else\n" + " 0\n" + " end BadCount\n" + " from (select distinct pd.barcode,\n" + " pd.productionlineid,\n" + " pd.goodsid,\n" + " pd.kilnid,\n" + " --pd.kilncarid,\n" + " pd.kilncarbatchno\n" + " from tp_pm_productiondata pd\n" + " where pd.valueflag = '1'\n" + " and pd.procedureid = :in_rptSprocedureId \n" + " and pd.AccountID = :AccountID\n"; StringBuilder selSql = new StringBuilder(sqlString); if (se.CreateTimeStart.HasValue) { selSql.Append(" AND pd.createtime >= :CreateTimeStart\n"); parameters.Add(new OracleParameter(":CreateTimeStart", OracleDbType.Date, se.CreateTimeStart.Value, ParameterDirection.Input)); } if (se.CreateTimeEnd.HasValue) { selSql.Append(" AND pd.createtime < :CreateTimeEnd\n"); parameters.Add(new OracleParameter(":CreateTimeEnd", OracleDbType.Date, se.CreateTimeEnd.Value, ParameterDirection.Input)); } //if (!string.IsNullOrEmpty(se.GoodsIDS)) //{ // selSql.Append(" AND instr(','||:GoodsIDS||',', ','||pd.GoodsId||',') > 0\n"); // parameters.Add(new OracleParameter(":GoodsIDS", OracleDbType.NVarchar2, se.GoodsIDS, ParameterDirection.Input)); //} //if (se.ProductionLineID.HasValue) //{ // selSql.Append(" AND pd.ProductionLineId = :ProductionLineID\n"); // parameters.Add(new OracleParameter(":ProductionLineID", OracleDbType.Int32, se.ProductionLineID, ParameterDirection.Input)); //} //if (!string.IsNullOrEmpty(se.GroutingUserIDS)) //{ // selSql.Append(" AND instr(','||:GroutingUserIDS||',', ','||pd.groutinguserid||',') > 0\n"); // parameters.Add(new OracleParameter(":GroutingUserIDS", OracleDbType.NVarchar2, se.GroutingUserIDS, ParameterDirection.Input)); //} //if (!string.IsNullOrEmpty(se.GroutingLineIDS)) //{ // selSql.Append(" AND instr(','||:GroutingLineIDS||',', ','||pd.groutinglineid||',') > 0\n"); // parameters.Add(new OracleParameter(":GroutingLineIDS", OracleDbType.NVarchar2, se.GroutingLineIDS, ParameterDirection.Input)); //} //selSql.Append(" AND exists (Select 1 from TP_MST_UserPurview up where up.PurviewType= 7 and up.UserID = :UserID and (up.PurviewID=-1 or up.PurviewID= pd.ProductionLineId))"); //parameters.Add(new OracleParameter(":UserID", OracleDbType.NVarchar2, user.UserID, ParameterDirection.Input)); //if (se.KilnID.HasValue) //{ // selSql.Append(" AND pd.KilnID = :KilnID\n"); // parameters.Add(new OracleParameter(":KilnID", OracleDbType.Int32, se.KilnID, ParameterDirection.Input)); //} sqlString = " ) pdbc\n" + " left join (select kcbc.barcode, kcbc.kilncarbatchno, kcbc.goodsleveltypeid\n" + " from (select pd.barcode,\n" + " pd.kilncarbatchno,\n" + " pd.goodsleveltypeid,\n" + " pd.productiondataid,\n" + " ROW_NUMBER() OVER(PARTITION BY pd.barcode, pd.kilncarbatchno ORDER BY pd.productiondataid desc) AS dataid\n" + " from tp_pm_productiondata pd\n" + " where pd.valueflag = '1'\n" + " and pd.AccountID = :AccountID\n" + " and length(pd.kilncarbatchno) > 0\n" + " AND pd.createtime >= :CreateTimeStart\n" + " and pd.modeltype = -1) kcbc\n" + " where kcbc.dataid = 1) glt\n" + " on pdbc.kilncarbatchno = glt.kilncarbatchno\n" + " and pdbc.barcode = glt.barcode) qd\n" + //" inner join tp_pc_productionline pcpl\n" + //" on pcpl.productionlineid = qd.productionlineid\n" + " inner join tp_mst_kiln mstkiln\n" + " on mstkiln.kilnid = qd.kilnid\n" + " inner join tp_mst_goods mstgoods\n" + " on mstgoods.goodsid = qd.goodsid\n" + " inner join tp_mst_goodstype mstgoodstype\n" + " on mstgoodstype.goodstypeid = mstgoods.goodstypeid\n" + " and mstgoodstype.accountid = mstgoods.accountid\n" + " inner join tp_mst_goodstype mstgoodstype2\n" + " on mstgoodstype2.goodstypecode =\n" + " substr(mstgoodstype.goodstypecode, 0, 6)\n" + " and mstgoodstype.accountid = mstgoodstype2.accountid\n" + //" where 1=1\n"; //selSql.Append(sqlString); //if (!string.IsNullOrEmpty(se.GoodsTypeIDS)) //{ // selSql.Append(" AND instr(','||:GoodsTypeIDS||',', ','||mstgoods.GOODSTYPEId||',') > 0\n"); // parameters.Add(new OracleParameter(":GoodsTypeIDS", OracleDbType.NVarchar2, se.GoodsTypeIDS, ParameterDirection.Input)); //} //sqlString = " group by grouping sets((mstkiln.kilncode,\n" + " mstgoodstype2.goodstypename),\n" + " mstkiln.kilncode,())\n" + " order by --pcpl.productionlinename,\n" + " mstkiln.kilncode,\n" + " mstgoodstype2.goodstypename\n" + " ) qdgroup\n"; selSql.Append(sqlString); return selSql.ToString(); } /// /// 取得FP00002画面（工号产量质量分析表）的查询数据 /// /// 登录用户信息 /// 查询条件 /// 查询结果 public static ServiceResultEntity GetFP00002Data(SUserInfo user, int rptSProcedureID, string usercode, DateTime date) { //if(string.IsNullOrWhiteSpace(usercode)) //{ // ServiceResultEntity sre = new ServiceResultEntity(); // sre.Status = Constant.ServiceResultStatus.NoSearchResults; // return sre; //} IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); List parameters = new List(); date = date.Date; parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); parameters.Add(new OracleParameter(":CreateTimeStart", OracleDbType.Date, date, ParameterDirection.Input)); parameters.Add(new OracleParameter(":CreateTimeEnd", OracleDbType.Date, date.AddDays(1), ParameterDirection.Input)); parameters.Add(new OracleParameter(":Usercode", OracleDbType.NVarchar2, usercode, ParameterDirection.Input)); // 产量 string sqlString = "\n" + "select qdgroup.gid,\n" + //" decode(qdgroup.gid, 7, '总计', 0, qdgroup.productionlinename, '--') productionlinename,\n" + " decode(qdgroup.gid, 3, '总计', 1, '合计', 0, qdgroup.procedurename, '') procedurename,\n" + //" decode(qdgroup.gid, 1, '小计', 0, qdgroup.goodstypename2, '--') goodstypename2,\n" + //" --decode(qdgroup.gid, 0, qdgroup.usercode, '--') usercode,\n" + //" decode(qdgroup.gid, 0, qdgroup.goodstypename, '--') goodstypename,\n" + " decode(qdgroup.gid, 0, qdgroup.goodscode, '') goodscode,\n" + " to_char(qdgroup.production) production\n" + " from (select grouping_id(--pcpl.productionlinename,\n" + " pcp.procedurename,\n" + //" mstgoodstype2.goodstypename,\n" + //" --qd.usercode,\n" + " mstgoods.goodscode) gid,\n" + //" pcpl.productionlinename,\n" + " pcp.procedurename,\n" + //" mstgoodstype2.goodstypename goodstypename2,\n" + " --qd.usercode,\n" + //" mstgoodstype.goodstypename,\n" + " mstgoods.goodscode,\n" + " sum(qd.production) production\n" + " from (select --pd.barcode,\n" + //" pd.productionlineid,\n" + " pd.procedureid,\n" + " pd.goodsid,\n" + " --pd.userid,\n" + " --pd.usercode,\n" + " 1 production\n" + " from tp_pm_productiondata pd\n" + " where pd.valueflag = '1'\n" + " and ((pd.modeltype <> 5) or\n" + " (pd.modeltype = 5 and pd.SpecialRepairFlag = '0'))\n" + " and pd.Usercode = :Usercode\n" + " and pd.AccountID = :AccountID\n" + " AND pd.createtime >= :CreateTimeStart\n" + " AND pd.createtime < :CreateTimeEnd) qd\n" + //" left join tp_pc_productionline pcpl\n" + //" on pcpl.productionlineid = qd.productionlineid\n" + " left join tp_pc_procedure pcp\n" + " on pcp.procedureid = qd.procedureid\n" + " left join tp_mst_goods mstgoods\n" + " on mstgoods.goodsid = qd.goodsid\n" + //" left join tp_mst_goodstype mstgoodstype\n" + //" on mstgoodstype.goodstypeid = mstgoods.goodstypeid\n" + //" left join tp_mst_goodstype mstgoodstype2\n" + //" on mstgoodstype2.goodstypecode =\n" + //" substr(mstgoodstype.goodstypecode, 0, 6)\n" + " group by grouping sets((--pcpl.productionlinename,\n" + " pcp.procedurename,\n" + //" mstgoodstype2.goodstypename,\n" + //" --qd.usercode,\n" + //" mstgoodstype.goodstypename,\n" + " mstgoods.goodscode),\n" + " pcp.procedurename,())\n" + " order by --pcpl.productionlinename,\n" + " pcp.procedurename,\n" + //" mstgoodstype2.goodstypename,\n" + //" --qd.usercode,\n" + //" mstgoodstype.goodstypename,\n" + " mstgoods.goodscode) qdgroup"; DataTable data = conn.GetSqlResultToDt(sqlString, parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); if (data == null || data.Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sre.Data = new DataSet(); sre.Data.Tables.Add(data); parameters.Add(new OracleParameter(":in_rptSprocedureId", OracleDbType.Int32, rptSProcedureID, ParameterDirection.Input)); string sqlString1 = "\n" + "select qdgroup.gid,\n" + //" decode(qdgroup.gid, 0, qdgroup.productionlinename, '--') productionlinename,\n" + " decode(qdgroup.gid, 7, '总计', 3, '合计', 0, qdgroup.procedurename, '') procedurename,\n" + " decode(qdgroup.gid, 1, '小计', 0, qdgroup.kilncode, '') kilncode,\n" + //" decode(qdgroup.gid, 1, '小计', 0, qdgroup.goodstypename2, '--') goodstypename2,\n" + " decode(qdgroup.gid, 0, qdgroup.goodstypename2, '') goodstypename2,\n" + //" --decode(qdgroup.gid, 0, qdgroup.usercode, '--') usercode,\n" + //" decode(qdgroup.gid, 0, qdgroup.goodstypename, '--') goodstypename,\n" + //" decode(qdgroup.gid, 0, qdgroup.goodscode, '--') goodscode,\n" + " to_char(qdgroup.OutKilnCount) OutKilnCount,\n" + //" qdgroup.GoodCount,\n" + // 合格率（正品+副品） //" to_char((qdgroup.GoodCount / qdgroup.OutKilnCount) * 100, '990.00') || '%' GoodPercent\n" + " to_char(((qdgroup.GoodCount +qdgroup.BadCount) / qdgroup.OutKilnCount) * 100, '990.00') || '%' GoodPercent\n" + //" qdgroup.SubstandardCount,\n" + //" to_char((qdgroup.SubstandardCount / qdgroup.OutKilnCount) * 100,\n" + //" '990.00') || '%' SubstandardPercent,\n" + //" qdgroup.BadCount,\n" + //" to_char((qdgroup.BadCount / qdgroup.OutKilnCount) * 100, '990.00') || '%' BadPercent,\n" + //" qdgroup.ReFireCount,\n" + //" to_char((qdgroup.ReFireCount / qdgroup.OutKilnCount) * 100,\n" + //" '990.00') || '%' ReFirePercent\n" + " from (select grouping_id(--pcpl.productionlinename,\n" + " pcp.procedurename,\n" + " mstkiln.kilncode,\n" + " mstgoodstype2.goodstypename) gid,\n" + //" --qd.usercode,\n" + //" mstgoods.goodscode) gid,\n" + //" pcpl.productionlinename,\n" + " pcp.procedurename,\n" + " mstkiln.kilncode,\n" + " mstgoodstype2.goodstypename goodstypename2,\n" + //" --qd.usercode,\n" + //" mstgoodstype.goodstypename,\n" + //" mstgoods.goodscode,\n" + " sum(qd.OutKilnCount) OutKilnCount,\n" + " sum(qd.GoodCount) GoodCount,\n" + //" sum(qd.SubstandardCount) SubstandardCount,\n" + " sum(qd.BadCount) BadCount\n" + //" sum(qd.ReFireCount) ReFireCount\n" + " from (select --pdbc.barcode,\n" + //" pdbc.productionlineid,\n" + " pdata.procedureid,\n" + //" --pdata.usercode,\n" + " pdbc.goodsid,\n" + " pdbc.kilnid,\n" + " 1 OutKilnCount,\n" + " case\n" + " when defect.defectprocedureid is not null and glt.goodsleveltypeid in (5, 6, 7) then\n" + " 0\n" + " else\n" + " 1\n" + " end GoodCount,\n" + //" case\n" + //" when defect.defectprocedureid is not null and glt.goodsleveltypeid = 7 then\n" + //" 1\n" + //" else\n" + //" 0\n" + //" end SubstandardCount,\n" + //" case\n" + //" when defect.defectprocedureid is not null and glt.goodsleveltypeid = 6 then\n" + //" 1\n" + //" else\n" + //" 0\n" + //" end ReFireCount,\n" + " case\n" + " when defect.defectprocedureid is not null and glt.goodsleveltypeid = 5 then\n" + " 1\n" + " else\n" + " 0\n" + " end BadCount\n" + " from (select distinct pd.barcode,\n" + //" pd.productionlineid,\n" + " pd.goodsid,\n" + " pd.kilnid,\n" + " pd.kilncarbatchno\n" + " from tp_pm_productiondata pd\n" + " where pd.valueflag = '1'\n" + " and pd.procedureid = :in_rptSprocedureId \n" + " and pd.AccountID = :AccountID\n" + " and pd.createtime >= :CreateTimeStart\n" + " and pd.createtime < :CreateTimeEnd\n" + " ) pdbc\n" + " inner join (select pd.barcode,\n" + //" --pd.userid,\n" + //" --pd.usercode,\n" + " pd.procedureid\n" + " from tp_pm_productiondata pd\n" + " where pd.valueflag = '1'\n" + " and pd.AccountID = :AccountID\n" + " and pd.usercode = :Usercode\n" + // 公坯、干补不算交坯工序的质量。 //" and ((pd.modeltype <> 5) or (pd.modeltype = 5 and pd.IsPublicBody = '0' and pd.SpecialRepairFlag = '0')) \n" + // 干补不算交坯工序的质量。 " and ((pd.modeltype <> 5) or (pd.modeltype = 5 and pd.SpecialRepairFlag = '0')) \n" + " ) pdata\n" + " on pdata.barcode = pdbc.barcode\n" + " left join (select kcbc.barcode, kcbc.kilncarbatchno, kcbc.goodsleveltypeid, kcbc.productiondataid\n" + " from (select pd.barcode,\n" + " pd.kilncarbatchno,\n" + " pd.goodsleveltypeid,\n" + " pd.productiondataid,\n" + " ROW_NUMBER() OVER(PARTITION BY pd.barcode, pd.kilncarbatchno ORDER BY pd.productiondataid desc) AS dataid\n" + " from tp_pm_productiondata pd\n" + " where pd.valueflag = '1'\n" + " and pd.AccountID = :AccountID\n" + " and length(pd.kilncarbatchno) > 0\n" + " AND pd.createtime >= :CreateTimeStart\n" + " and pd.modeltype = -1) kcbc\n" + " where kcbc.dataid = 1) glt\n" + " on pdbc.kilncarbatchno = glt.kilncarbatchno\n" + " and pdbc.barcode = glt.barcode\n" + " left join (select distinct productiondataid, defectprocedureid" + " from tp_pm_defect def " + " where def.valueflag='1'" + " and def.AccountID = :AccountID\n" + " AND def.createtime >= :CreateTimeStart) defect\n" + " on defect.productiondataid = glt.productiondataid\n" + " and pdata.procedureid = defect.defectprocedureid) qd\n" + //" inner join tp_pc_productionline pcpl\n" + //" on pcpl.productionlineid = qd.productionlineid\n" + " inner join tp_pc_procedure pcp\n" + " on pcp.procedureid = qd.procedureid\n" + " inner join tp_mst_kiln mstkiln\n" + " on mstkiln.kilnid = qd.kilnid\n" + " inner join tp_mst_goods mstgoods\n" + " on mstgoods.goodsid = qd.goodsid\n" + " inner join tp_mst_goodstype mstgoodstype\n" + " on mstgoodstype.goodstypeid = mstgoods.goodstypeid\n" + " inner join tp_mst_goodstype mstgoodstype2\n" + " on mstgoodstype2.goodstypecode =\n" + " substr(mstgoodstype.goodstypecode, 0, 6)\n" + " and mstgoodstype.AccountID = mstgoodstype2.AccountID\n" + " group by grouping sets((--pcpl.productionlinename,\n" + " pcp.procedurename,\n" + " mstkiln.kilncode,\n" + " mstgoodstype2.goodstypename),\n" + //" --qd.usercode,\n" + //" mstgoodstype.goodstypename,\n" + //" mstgoods.goodscode),\n" + " (--pcpl.productionlinename,\n" + " pcp.procedurename,\n" + " mstkiln.kilncode),\n" + //" mstgoodstype2.goodstypename),\n" + " (--pcpl.productionlinename,\n" + " pcp.procedurename),\n" + //" mstkiln.kilncode),\n" + " ())\n" + " order by --pcpl.productionlinename,\n" + " pcp.procedurename,\n" + " mstkiln.kilncode,\n" + " mstgoodstype2.goodstypename\n" + //" --qd.usercode,\n" + //" mstgoodstype.goodstypename,\n" + //" mstgoods.goodscode\n" + " ) qdgroup\n"; DataTable data1 = conn.GetSqlResultToDt(sqlString1, parameters.ToArray()); if (data1 == null || data1.Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sre.Data.Tables.Add(data1); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得RPT000001画面（半检数据统计表）的查询数据 /// /// 登录用户信息 /// 查询条件 /// 查询结果 public static ServiceResultEntity GetPDARPT000001Data(SUserInfo user, string usercode, DateTime datebegin, DateTime dateend) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); List parameters = new List(); parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); parameters.Add(new OracleParameter(":datebegin", OracleDbType.Date, datebegin, ParameterDirection.Input)); parameters.Add(new OracleParameter(":dateend", OracleDbType.Date, dateend, ParameterDirection.Input)); parameters.Add(new OracleParameter(":usercode", OracleDbType.NVarchar2, usercode, ParameterDirection.Input)); // 返工 string sqlString = "SELECT p.procedurename, op.procedurename overprocedurename, sc.goodscode, COUNT(sc.semicheckid) reworkcount\n" + " FROM TP_PM_SemiCheck sc\n" + " INNER JOIN tp_pc_procedure p\n" + " ON p.procedureid = sc.ReworkProcedureID\n" + " INNER JOIN tp_pc_procedure op\n" + " ON op.procedureid = sc.ProcedureID\n" + " WHERE sc.accountid = :accountid\n" + " AND sc.ValueFlag = '1'\n" + " AND sc.BackOutFlag = '0'\n" + " AND sc.SemiCheckType = 1\n" + " AND (:usercode IS NULL OR sc.reworkusercode = :usercode)\n" + " AND (:datebegin IS NULL OR sc.semichecktime >= :datebegin)\n" + " AND (:dateend IS NULL OR sc.semichecktime <= :dateend)\n" + " GROUP BY p.procedurename, op.procedurename, p.displayno, sc.goodscode, sc.goodsid\n" + " ORDER BY p.displayno, sc.goodscode"; DataTable data = conn.GetSqlResultToDt(sqlString, parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); //if (data == null || data.Rows.Count == 0) //{ // sre.Status = Constant.ServiceResultStatus.NoSearchResults; // return sre; //} data.TableName = "rework"; sre.Data = new DataSet(); sre.Data.Tables.Add(data); // 复检 string sqlString1 = "SELECT p.procedurename, op.procedurename overprocedurename\n" + " ,sc.goodscode\n" + " ,SUM(decode(sc.ReSemiCheckType, 1, 1, 0)) qcount\n" + " ,SUM(decode(sc.ReSemiCheckType, 2, 1, 0)) unqcount\n" + " FROM TP_PM_SemiCheck sc\n" + " INNER JOIN tp_pc_procedure p\n" + " ON p.procedureid = sc.ReworkProcedureID\n" + " INNER JOIN tp_pc_procedure op\n" + " ON op.procedureid = sc.ProcedureID\n" + " WHERE sc.accountid = :accountid\n" + " AND sc.ValueFlag = '1'\n" + " AND sc.BackOutFlag = '0'\n" + " AND sc.SemiCheckType = 1\n" + " AND sc.ReSemiCheckType <> 0\n" + " AND (:usercode IS NULL OR sc.reworkusercode = :usercode)\n" + " AND (:datebegin IS NULL OR sc.semichecktime >= :datebegin)\n" + " AND (:dateend IS NULL OR sc.semichecktime <= :dateend)\n" + " GROUP BY p.procedurename, op.procedurename, p.displayno, sc.goodscode\n" + " ORDER BY p.displayno, sc.goodscode"; DataTable data1 = conn.GetSqlResultToDt(sqlString1, parameters.ToArray()); //if (data1 == null || data1.Rows.Count == 0) //{ // sre.Status = Constant.ServiceResultStatus.NoSearchResults; // return sre; //} data1.TableName = "ReSemiCheck"; sre.Data.Tables.Add(data1); // 未复检(总) string sqlString2 = "SELECT p.procedurename, op.procedurename overprocedurename\n" + " ,sc.goodscode\n" + //" ,SUM(decode(sc.ReSemiCheckType, 1, 1, 0)) qcount\n" + //" ,SUM(decode(sc.ReSemiCheckType, 2, 1, 0)) unqcount\n" + " ,COUNT(sc.semicheckid) renocountsum\n" + " FROM TP_PM_SemiCheck sc\n" + " INNER JOIN tp_pc_procedure p\n" + " ON p.procedureid = sc.ReworkProcedureID\n" + " INNER JOIN tp_pc_procedure op\n" + " ON op.procedureid = sc.ProcedureID\n" + " WHERE sc.accountid = :accountid\n" + " AND sc.ValueFlag = '1'\n" + " AND sc.BackOutFlag = '0'\n" + " AND sc.SemiCheckType = 1\n" + " AND sc.ReSemiCheckType = 0\n" + " AND (:usercode IS NULL OR sc.reworkusercode = :usercode)\n" + //" AND (:datebegin IS NULL OR sc.semichecktime >= :datebegin)\n" + //" AND (:dateend IS NULL OR sc.semichecktime <= :dateend)\n" + " GROUP BY p.procedurename, op.procedurename, p.displayno, sc.goodscode\n" + " ORDER BY p.displayno, sc.goodscode"; parameters.Clear(); parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); //parameters.Add(new OracleParameter(":datebegin", OracleDbType.Date, datebegin, ParameterDirection.Input)); //parameters.Add(new OracleParameter(":dateend", OracleDbType.Date, dateend, ParameterDirection.Input)); parameters.Add(new OracleParameter(":usercode", OracleDbType.NVarchar2, usercode, ParameterDirection.Input)); DataTable data2 = conn.GetSqlResultToDt(sqlString2, parameters.ToArray()); //if (data1 == null || data1.Rows.Count == 0) //{ // sre.Status = Constant.ServiceResultStatus.NoSearchResults; // return sre; //} data2.TableName = "NoReSemiCheck"; sre.Data.Tables.Add(data2); // 半检不合格 string sqlString3 = "SELECT scdp.procedurename, op.procedurename overprocedurename\n" + " ,sc.goodscode\n" + " ,COUNT(distinct sc.semicheckid) badcountsum\n" + " FROM TP_PM_SemiCheck sc\n" + //" INNER JOIN tp_pc_procedure p\n" + //" ON p.procedureid = sc.ReworkProcedureID\n" + " INNER JOIN tp_pc_procedure op\n" + " ON op.procedureid = sc.ProcedureID\n" + " LEFT JOIN TP_PM_SemiCheckDefect scd\n" + " ON sc.semicheckid = scd.semicheckid\n" + " LEFT JOIN tp_pc_procedure scdp\n" + " ON scdp.procedureid = scd.defectprocedureid\n" + " WHERE sc.accountid = :accountid\n" + " AND sc.ValueFlag = '1'\n" + " AND sc.BackOutFlag = '0'\n" + " AND sc.SemiCheckType = 2\n" + " AND sc.ReSemiCheckType = 0\n" + //" AND (:usercode IS NULL OR sc.reworkusercode = :usercode)\n" + " AND (:usercode IS NULL OR scd.defectusercode = :usercode)\n" + " AND (:datebegin IS NULL OR sc.semichecktime >= :datebegin)\n" + " AND (:dateend IS NULL OR sc.semichecktime <= :dateend)\n" + " GROUP BY scdp.procedurename, op.procedurename, scdp.displayno, sc.goodscode\n" + " ORDER BY scdp.displayno, sc.goodscode"; parameters.Clear(); parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); parameters.Add(new OracleParameter(":datebegin", OracleDbType.Date, datebegin, ParameterDirection.Input)); parameters.Add(new OracleParameter(":dateend", OracleDbType.Date, dateend, ParameterDirection.Input)); parameters.Add(new OracleParameter(":usercode", OracleDbType.NVarchar2, usercode, ParameterDirection.Input)); DataTable data3 = conn.GetSqlResultToDt(sqlString3, parameters.ToArray()); //if (data1 == null || data1.Rows.Count == 0) //{ // sre.Status = Constant.ServiceResultStatus.NoSearchResults; // return sre; //} data3.TableName = "SemiCheckBad"; sre.Data.Tables.Add(data3); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得RPT000002画面（工号质量统计表）的查询数据 /// /// 登录用户信息 /// 查询条件 /// 查询结果 public static ServiceResultEntity GetPDARPT000002Data(SUserInfo user, int rptSProcedureID, string usercode, DateTime datebegin, DateTime dateend) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); List parameters = new List(); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); parameters.Add(new OracleParameter(":DateBegin", OracleDbType.Date, datebegin, ParameterDirection.Input)); parameters.Add(new OracleParameter(":DateEnd", OracleDbType.Date, dateend, ParameterDirection.Input)); parameters.Add(new OracleParameter(":Usercode", OracleDbType.NVarchar2, usercode, ParameterDirection.Input)); parameters.Add(new OracleParameter(":in_rptSprocedureId", OracleDbType.Int32, rptSProcedureID, ParameterDirection.Input)); string sqlString1 = "select qdgroup.GoodsCode,\n" + " qdgroup.OutKilnCount OutKilnCount,\n" + // 合格品（正品+副品） " qdgroup.GoodCount + qdgroup.BadCount as pcount,\n" + // 合格率（正品+副品） " to_char(((qdgroup.GoodCount +qdgroup.BadCount) / qdgroup.OutKilnCount) * 100, '990.00') || '%' PPercent,\n" + // 正品 " qdgroup.GoodCount,\n" + " to_char((qdgroup.GoodCount / qdgroup.OutKilnCount) * 100, '990.00') || '%' GoodPercent,\n" + // 副品 " qdgroup.BadCount,\n" + " to_char((qdgroup.BadCount / qdgroup.OutKilnCount) * 100, '990.00') || '%' BadPercent,\n" + // 次品 " qdgroup.SubstandardCount,\n" + " to_char((qdgroup.SubstandardCount / qdgroup.OutKilnCount) * 100,\n" + " '990.00') || '%' SubstandardPercent,\n" + // 重烧 " qdgroup.ReFireCount,\n" + " to_char((qdgroup.ReFireCount / qdgroup.OutKilnCount) * 100,\n" + " '990.00') || '%' ReFirePercent\n" + " from (select qd.goodscode || '【' || pcp.procedurename || '】' as GoodsCode,\n" + //" pcp.procedurename,\n" + " sum(qd.OutKilnCount) OutKilnCount,\n" + " sum(qd.GoodCount) GoodCount,\n" + " sum(qd.SubstandardCount) SubstandardCount,\n" + " sum(qd.BadCount) BadCount,\n" + " sum(qd.ReFireCount) ReFireCount\n" + " from (select --pdbc.barcode,\n" + " pdata.procedureid,\n" + " pdbc.goodscode,\n" + " 1 OutKilnCount,\n" + " case\n" + " when defect.defectprocedureid is not null and glt.goodsleveltypeid in (5, 6, 7) then\n" + " 0\n" + " else\n" + " 1\n" + " end GoodCount,\n" + " case\n" + " when defect.defectprocedureid is not null and glt.goodsleveltypeid = 7 then\n" + " 1\n" + " else\n" + " 0\n" + " end SubstandardCount,\n" + " case\n" + " when defect.defectprocedureid is not null and glt.goodsleveltypeid = 6 then\n" + " 1\n" + " else\n" + " 0\n" + " end ReFireCount,\n" + " case\n" + " when defect.defectprocedureid is not null and glt.goodsleveltypeid = 5 then\n" + " 1\n" + " else\n" + " 0\n" + " end BadCount\n" + " from (select distinct pd.barcode,\n" + " pd.goodscode,\n" + " pd.kilncarbatchno\n" + " from tp_pm_productiondata pd\n" + " where pd.valueflag = '1'\n" + " and pd.procedureid = :in_rptSprocedureId \n" + " and pd.AccountID = :AccountID\n" + " and pd.createtime >= :DateBegin\n" + " and pd.createtime <= :DateEnd\n" + " ) pdbc\n" + " inner join (select pd.barcode,\n" + " pd.procedureid\n" + " from tp_pm_productiondata pd\n" + " where pd.valueflag = '1'\n" + " and pd.AccountID = :AccountID\n" + " and pd.usercode = :Usercode\n" + // 公坯、干补不算交坯工序的质量。 //" and ((pd.modeltype <> 5) or (pd.modeltype = 5 and pd.IsPublicBody = '0' and pd.SpecialRepairFlag = '0')) \n" + // 干补不算交坯工序的质量。 " and ((pd.modeltype <> 5) or (pd.modeltype = 5 and pd.SpecialRepairFlag = '0')) \n" + " ) pdata\n" + " on pdata.barcode = pdbc.barcode\n" + " left join (select kcbc.barcode, kcbc.kilncarbatchno, kcbc.goodsleveltypeid, kcbc.productiondataid\n" + " from (select pd.barcode,\n" + " pd.kilncarbatchno,\n" + " pd.goodsleveltypeid,\n" + " pd.productiondataid,\n" + " ROW_NUMBER() OVER(PARTITION BY pd.barcode, pd.kilncarbatchno ORDER BY pd.productiondataid desc) AS dataid\n" + " from tp_pm_productiondata pd\n" + " where pd.valueflag = '1'\n" + " and pd.AccountID = :AccountID\n" + " and length(pd.kilncarbatchno) > 0\n" + " AND pd.createtime >= :DateBegin\n" + " and pd.modeltype = -1) kcbc\n" + " where kcbc.dataid = 1) glt\n" + " on pdbc.kilncarbatchno = glt.kilncarbatchno\n" + " and pdbc.barcode = glt.barcode\n" + " left join (select distinct productiondataid, defectprocedureid\n" + " from tp_pm_defect def\n" + " where def.valueflag='1'\n" + " and def.AccountID = :AccountID\n" + " AND def.createtime >= :DateBegin) defect\n" + " on defect.productiondataid = glt.productiondataid\n" + " and pdata.procedureid = defect.defectprocedureid) qd\n" + " inner join tp_pc_procedure pcp\n" + " on pcp.procedureid = qd.procedureid\n" + " group by qd.goodscode, pcp.procedurename, pcp.displayno\n" + " order by pcp.displayno,\n" + " qd.goodscode\n" + " ) qdgroup\n"; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = new DataSet(); DataTable data1 = conn.GetSqlResultToDt(sqlString1, parameters.ToArray()); //if (data1 == null || data1.Rows.Count == 0) //{ // sre.Status = Constant.ServiceResultStatus.NoSearchResults; // return sre; //} sre.Data.Tables.Add(data1); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得RPT000003画面（损坯数据统计表）的查询数据 /// /// 登录用户信息 /// 查询条件 /// 查询结果 public static ServiceResultEntity GetPDARPT000003Data(SUserInfo user, string usercode, DateTime datebegin, DateTime dateend) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); List parameters = new List(); parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); parameters.Add(new OracleParameter(":datebegin", OracleDbType.Date, datebegin, ParameterDirection.Input)); parameters.Add(new OracleParameter(":dateend", OracleDbType.Date, dateend, ParameterDirection.Input)); parameters.Add(new OracleParameter(":usercode", OracleDbType.NVarchar2, usercode, ParameterDirection.Input)); // 本工号损坯 string sqlString = "SELECT spp.procedurename sppname\n" + " ,rpp.procedurename rppname\n" + " ,sp.goodscode\n" + " ,COUNT(sp.barcode) scount\n" + " FROM TP_PM_ScrapProduct sp\n" + " INNER JOIN tp_pc_procedure spp\n" + " ON spp.ProcedureID = sp.procedureid\n" + " LEFT JOIN TP_PM_ResponProcedure rp\n" + " ON rp.scrapproductid = sp.scrapproductid\n" + " LEFT JOIN tp_pc_procedure rpp\n" + " ON rp.procedureid = rpp.procedureid\n" + " WHERE sp.accountid = :accountid\n" + " AND sp.auditstatus = 1\n" + " AND sp.valueflag = '1'\n" + " AND sp.GoodsLevelTypeID IN (8, 9)\n" + " AND sp.ScrapType = '0'\n" + // 重烧后报损，不算后道损 //" AND sp.IsReFire = '0'\n" + " AND EXISTS (SELECT 1\n" + " FROM TP_PM_ScrapResponsible sr\n" + " WHERE sr.scrapproductid = sp.scrapproductid\n" + " AND sr.usercode = :usercode)\n" + " AND sp.AuditDate >= :datebegin\n" + " AND sp.AuditDate <= :dateend\n" + " GROUP BY spp.procedurename\n" + " ,rpp.procedurename\n" + " ,spp.displayno\n" + " ,rpp.displayno\n" + " ,sp.goodscode\n" + " ORDER BY spp.displayno, rpp.displayno, sp.goodscode"; DataTable data = conn.GetSqlResultToDt(sqlString, parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); //if (data == null || data.Rows.Count == 0) //{ // sre.Status = Constant.ServiceResultStatus.NoSearchResults; // return sre; //} data.TableName = "this"; sre.Data = new DataSet(); sre.Data.Tables.Add(data); // 后道损坯 // 后道工序损坯，应该经本工序生产后，非本工序责任损坯 string sqlString1 = "SELECT spp.procedurename sppname\n" + " ,rpp.procedurename rppname\n" + " ,sp.goodscode\n" + " ,COUNT(DISTINCT sp.barcode) scount\n" + " FROM TP_PM_ScrapProduct sp\n" + " INNER JOIN tp_pc_procedure spp\n" + " ON spp.ProcedureID = sp.procedureid\n" + " LEFT JOIN TP_PM_ResponProcedure rp\n" + " ON rp.scrapproductid = sp.scrapproductid\n" + " LEFT JOIN tp_pc_procedure rpp\n" + " ON rp.procedureid = rpp.procedureid\n" + " WHERE sp.accountid = :accountid\n" + " AND sp.auditstatus = 1\n" + " AND sp.valueflag = '1'\n" + " AND sp.GoodsLevelTypeID IN (8, 9)\n" + " AND sp.ScrapType = '0'\n" + // 重烧后报损，不算后道损 " AND sp.IsReFire = '0'\n" + // 非本工序责任损坯 " AND NOT EXISTS (SELECT 1\n" + " FROM TP_PM_ScrapResponsible sr\n" + " WHERE sr.scrapproductid = sp.scrapproductid\n" + " AND sr.usercode = :usercode)\n" + // 经本工序生产后 " AND EXISTS (SELECT 1\n" + " FROM tp_pm_productiondata pd\n" + " WHERE pd.barcode = sp.barcode\n" + " AND pd.productiondataid <= sp.productiondataid\n" + " AND pd.usercode = :usercode)\n" + " AND sp.AuditDate >= :datebegin\n" + " AND sp.AuditDate <= :dateend\n" + " GROUP BY spp.procedurename\n" + " ,rpp.procedurename\n" + " ,spp.displayno\n" + " ,rpp.displayno\n" + " ,sp.goodscode\n" + " ORDER BY spp.displayno, rpp.displayno, sp.goodscode"; DataTable data1 = conn.GetSqlResultToDt(sqlString1, parameters.ToArray()); //if (data1 == null || data1.Rows.Count == 0) //{ // sre.Status = Constant.ServiceResultStatus.NoSearchResults; // return sre; //} data1.TableName = "next"; sre.Data.Tables.Add(data1); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } #endregion /// /// 获取用户是否有统计产成品权限 /// /// public static int GetStatisticsFinishedProductFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON (R.FunctionCode = F.FunctionCode OR R.FunctionCode = '[ALL]') WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and instr(F.FunctionCode, '070205') = 1 /*F.FunctionCode='070205'*/"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取用户是否有盘点权限 /// /// public static int GetInCheckedFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON (R.FunctionCode = F.FunctionCode OR R.FunctionCode = '[ALL]') WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and instr(F.FunctionCode, '060802') = 1 /*F.FunctionCode='060802'*/"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取主表盘点信息 /// /// 盘点类 /// 用户基本信息 /// public static DataSet GetAllInChecked(InCheckedEntity entity, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_inCheckedNo",OracleDbType.NVarchar2, entity.InCheckedNo,ParameterDirection.Input), new OracleParameter("in_inCheckedName",OracleDbType.NVarchar2, entity.InCheckedName,ParameterDirection.Input), new OracleParameter("in_remarks",OracleDbType.NVarchar2, entity.Remarks,ParameterDirection.Input), new OracleParameter("in_begindate",OracleDbType.Date, entity.BeginDate,ParameterDirection.Input), new OracleParameter("in_enddate",OracleDbType.Date,entity.EndDate,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), new OracleParameter("in_accountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("in_userID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; DataSet returnDataSet = oracleConn.ExecStoredProcedure("PRO_PM_GetInCheckedPDA", paras); return returnDataSet; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 根据查询非工号下的员工档案信息 /// /// 员工查询实体 /// 用户基本信息 /// DataSet public static DataSet SearchStaffInfo(SearchStaffEntity searchStaffEntity, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = " SELECT Staff.Staffid,Staff.Staffname,Staff.Organizationid," + " Staff.Jobs,Staff.Staffstatus,Staff.Staffcode, Staff.Post," + " (CASE Staff.staffStatus" + " WHEN 0 THEN '未入职'" + " WHEN 1 THEN '试用 '" + " WHEN 2 THEN '转正'" + " WHEN 3 THEN '离职'" + " ELSE '' END) AS staffStatusName," + " Jobs.Jobsname,Org.Organizationname,Org.OrganizationFullName,Post.PostName,0 Sel" + " FROM TP_HR_Staff Staff" + " LEFT JOIN TP_MST_Jobs Jobs ON Jobs.Jobsid = Staff.Jobs " + " LEFT JOIN TP_MST_Organization Org ON Org.Organizationid = Staff.Organizationid " + " LEFT JOIN TP_MST_Post Post on Post.Postid = Staff.Post " + " WHERE Staff.Accountid = :accountID " + " AND Staff.Staffstatus in(1,2)" + " AND (Staff.Jobs = :jobs OR :jobs is null)"; if (!string.IsNullOrEmpty(searchStaffEntity.StaffCode)) { //strSql += " AND Staff.Staffcode like '%" + searchStaffEntity.StaffCode + "%'"; strSql += " AND Staff.Staffcode ='" + searchStaffEntity.StaffCode + "'"; } if (!string.IsNullOrEmpty(searchStaffEntity.StaffName)) { strSql += " AND Staff.Staffname like '%" + searchStaffEntity.StaffName + "%'"; } OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":jobs", OracleDbType.Int32, searchStaffEntity.Jobs, ParameterDirection.Input), new OracleParameter(":jobs", OracleDbType.Int32, searchStaffEntity.Jobs, ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(strSql, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据条件查询是否存在班次配置 /// /// 工号编码 /// 用户基本信息 /// DataSet public static DataSet GetClassesSetting(string uCode, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); DataSet dsReturn = new DataSet(); string strSql = @"select userid,username,usercode,fun_cmn_getaccountdate(:accountid) as accountdate, (select tp_pc_classessetting.remarks from tp_pc_classessetting where tp_pc_classessetting.classessettingid in (select nvl(max(classessettingID),0) classessettingID from tp_pc_classessetting where tp_pc_classessetting.usercode=:usercode and tp_pc_classessetting.accountdate=fun_cmn_getaccountdate(:accountid))) as remarks from tp_mst_user where usercode=:usercode and accountid=:accountid"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":usercode",OracleDbType.Varchar2,uCode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(strSql, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { ds.Tables[0].TableName = "Table1"; dsReturn.Tables.Add(ds.Tables[0].Copy()); } strSql = " SELECT classesDetail.ClassesSettingID," + " classesDetail.StaffID,classesDetail.Createtime," + " classesDetail.UjobsId UjobsId,classesDetail.SjobsId jobs,classesDetail.Remarks,classesDetail.CreateUserID," + " classesDetail.UpdateTime,CUser.Username createUserName," + " Jobs.Jobsname,Muser.Organizationid,Org.Organizationname,Staff.Staffname,Staff.Staffcode," + " status.staffstatusname AS staffStatusName,classesDetail.staffStatus,UJobs.JobsName as UJobsName,classesDetail.Ujobsid as UJobsId " + " FROM TP_PC_ClassesDetail classesDetail " + " LEFT JOIN TP_MST_User MUser ON MUser.UserId = classesDetail.UserID " + " LEFT JOIN TP_MST_User CUser ON CUser.UserId = classesDetail.CreateUserID " + " LEFT JOIN TP_MST_Jobs Jobs ON Jobs.Jobsid = classesDetail.SjobsId " + " LEFT JOIN TP_MST_Organization Org ON Org.Organizationid = Muser.Organizationid " + " LEFT JOIN TP_HR_Staff Staff ON staff.staffid = classesDetail.Staffid " + " LEFT JOIN TP_MST_UserStaff userStaff ON classesDetail.STAFFID = userStaff.Staffid and classesDetail.Userid = userStaff.UserId" + " LEFT JOIN TP_MST_JOBS UJobs ON classesDetail.Ujobsid = UJobs.Jobsid" + " Inner JOIN TP_SYS_StaffStatus status on classesDetail.Staffstatus = status.staffstatusid" + " WHERE classesDetail.AccountID = :accountID " + " AND classesDetail.ClassesSettingID = (select nvl(max(classessettingID),0) classessettingID from tp_pc_classessetting where tp_pc_classessetting.usercode=:usercode and tp_pc_classessetting.accountdate=fun_cmn_getaccountdate(:accountID))"; paras = new OracleParameter[]{ new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":usercode",OracleDbType.Varchar2,uCode,ParameterDirection.Input), }; ds = con.GetSqlResultToDs(strSql, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { ds.Tables[0].TableName = "Table2"; dsReturn.Tables.Add(ds.Tables[0].Copy()); return dsReturn; } strSql = " SELECT Staff.Staffid,Staff.Staffname,Staff.Organizationid," + " Staff.Jobs,Staff.Staffstatus,Staff.Staffcode,'' Remarks," + " (CASE Staff.staffStatus" + " WHEN 0 THEN '未入职'" + " WHEN 1 THEN '试用 '" + " WHEN 2 THEN '转正'" + " WHEN 3 THEN '离职'" + " ELSE '' END) AS staffStatusName," + " Jobs.Jobsname,Org.Organizationname,TUserJobs.Jobsname as UJobsName,TUserJobs.JobsId as UJobsId" + " FROM TP_HR_Staff Staff " + " LEFT JOIN TP_MST_Jobs Jobs ON Jobs.Jobsid = Staff.Jobs " + " LEFT JOIN TP_MST_Organization Org ON Org.Organizationid = Staff.Organizationid " + " LEFT JOIN TP_MST_UserStaff TUserStaff on TUserStaff.StaffID = Staff.StaffID " + " LEFT JOIN TP_MST_User TUser on TUserStaff.Userid = TUser.Userid" + " LEFT JOIN TP_MST_Jobs TUserJobs on TUserJobs.Jobsid = TUserStaff.Ujobsid" + " WHERE Staff.Accountid = :accountID " + " AND TUser.UserCode = :UserCode" + " AND Staff.Staffstatus in(1,2)"; paras = new OracleParameter[]{ new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":UserCode", OracleDbType.Varchar2, uCode, ParameterDirection.Input), }; ds = con.GetSqlResultToDs(strSql, paras); ds.Tables[0].TableName = "Table2"; dsReturn.Tables.Add(ds.Tables[0].Copy()); return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /* /// /// 获取用户是否有撤销包装权限 /// /// public static int GetChancelFinishedproductFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON R.FunctionCode = F.FunctionCode WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and F.FunctionCode='0609'"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取用户是否有撤销权限 /// /// public static int GetCancelScrapProductionFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON R.FunctionCode = F.FunctionCode WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and F.FunctionCode='0610'"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取用户是否有公坯设定权限 /// /// public static int GetPublicBodyProductFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON R.FunctionCode = F.FunctionCode WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and F.FunctionCode='0611'"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } */ /// /// 获取用户是否有公坯设定权限 /// /// public static DataSet GetUserAllFunction(SUserInfo userInfo) { // 产品报损 if (userInfo.UserCode.ToUpper() == "SYSTEM" || userInfo.UserCode.ToUpper() == "ADMIN") { return null; } IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":UserID", OracleDbType.Int32, userInfo.UserID, ParameterDirection.Input), }; string strSql = "SELECT F.FunctionCode, f.functionlevel, " + " CASE WHEN F.FunctionCode='060401' THEN '产品报损'" + " WHEN F.FunctionCode='060202' THEN '条码变更'" + " WHEN F.FunctionCode='0404' THEN '班次配置'" + " WHEN F.FunctionCode='060802' THEN '在产盘点'" + " WHEN F.FunctionCode='06230103' THEN '成型盘点'" + " WHEN F.FunctionCode='0609' THEN '成品撤销'" + " WHEN F.FunctionCode='0610' THEN '损坯撤销'" + " WHEN F.FunctionCode='0611' THEN '公坯设定'" + " WHEN F.FunctionCode='070205' THEN '产成品质量分析表'" + " WHEN F.FunctionCode='0615' THEN '计件撤销'" + " WHEN F.FunctionCode='061601' THEN '交坯撤销'" + " WHEN F.FunctionCode='070205' THEN '产成品质量分析表'" + " WHEN F.FunctionCode='060203' THEN '商标设定'" + " WHEN F.FunctionCode='062001' THEN '半成品检验'" + " WHEN F.FunctionCode='06200101' THEN '半检登记'" + " WHEN F.FunctionCode='06200102' THEN '复检登记'" + " WHEN F.FunctionCode='06200103' THEN '撤销复检'" + " WHEN F.FunctionCode='0614' THEN '条码恢复'" + " WHEN F.FunctionCode='0622' THEN '产成品交接'" + " WHEN F.FunctionCode='062201' THEN '交接'" + " WHEN F.FunctionCode='062202' THEN '撤销'" + " WHEN F.FunctionCode='062203' THEN '变更单号'" + " WHEN F.FunctionCode='060204' THEN '条码补打'" + " WHEN F.FunctionCode='0626' THEN '抽查记录'" + " WHEN F.FunctionCode='062601' THEN '品保抽查'" + " ELSE to_char(f.functionname) END functionname " + " FROM TP_SYS_Function F " + "WHERE F.ValueFlag = '1'" + " AND F.IS_PDA = '1' " + " AND EXISTS (SELECT 1" + " FROM TP_MST_UserRight R" + " WHERE R.UserID = :UserID" + //" AND (R.FunctionCode = F.FunctionCode OR R.FunctionCode = '[ALL]'))" + " AND (instr(R.FunctionCode , F.FunctionCode) = 1 OR R.FunctionCode = '[ALL]'))" + //" AND (F.FunctionCode='060401'" + //" OR F.FunctionCode='060202'" + //" OR F.FunctionCode='0404'" + //" OR F.FunctionCode='060802'" + //" OR F.FunctionCode='06230103'" + //" OR F.FunctionCode='0609'" + //" OR F.FunctionCode='0610'" + //" OR F.FunctionCode='0611'" + //" OR F.FunctionCode='070205'" + //" OR F.FunctionCode='0615'" + //" OR F.FunctionCode='061601'" + //" OR F.FunctionCode='070205'" + //" OR F.FunctionCode='060203'" + //" OR F.FunctionCode='062001'" + //" OR F.FunctionCode='06200101'" + //" OR F.FunctionCode='06200102'" + //" OR F.FunctionCode='06200103'" + //" OR F.FunctionCode='0614'" + //" OR F.FunctionCode='0622'" + //" OR F.FunctionCode='062201'" + //" OR F.FunctionCode='062202'" + //" OR F.FunctionCode='062203'" + //" OR F.FunctionCode='060204'" + //// 成型报损，撤销报损 //" OR F.FunctionCode='060106'" + //" OR F.FunctionCode='060107'" + //// 模具管理相关 //" OR F.FunctionCode in ('M04','M0401','M040103','M040104','M040105','M040106','M040107','M040108','M0402')" + //" OR F.FunctionCode='070303'" + //" OR F.FunctionCode='0402'" + //" OR F.FunctionCode in ('0624', '062401', '062402', '070713','0625','9001','9002')" + //" OR F.FunctionCode in ('0626','062601')" + //" OR F.FunctionCode like 'PDA%'" + //" )" + " Order by f.functionlevel"; DataSet ds = con.GetSqlResultToDs(strSql, parameters); return ds; //if (ds != null && ds.Tables[0].Rows.Count > 0) //{ // return 1; //} //return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取登陆帐户有无入窑前检验数据编辑权限 /// /// 工号编码 /// public static int GetIntoKilnCheckEditFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON (R.FunctionCode = F.FunctionCode OR R.FunctionCode = '[ALL]') WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and instr(F.FunctionCode, '061801') = 1 /*F.FunctionCode='061801'*/"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取登陆帐户有无半检验数据编辑权限 /// /// 工号编码 /// public static int GetNormalCheckEditFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON (R.FunctionCode = F.FunctionCode OR R.FunctionCode = '[ALL]') WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and instr(F.FunctionCode, '061901') = 1 /*F.FunctionCode='061901'*/"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #region 质量登记 /// /// 通过条码查出责任工序 /// /// 产品条码 /// 缺陷ID /// DataSet public static DataSet GetDutyProcedureByBarCode(string barcode, int defectid, int accountid) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); DataSet dsReturn = new DataSet(); string sqlString = "SELECT pdin.ProductionDataID\n" + " ,pdin.ProcedureID AS DutyProcedureID\n" + " ,pdin.ProcedureCode AS DutyProcedureCode\n" + " ,pdin.ProcedureName AS DutyProcedureName\n" + " ,pdin.NodeType\n" + " ,pdin.ModelType\n" + " ,pdin.classessettingid\n" + " ,pdin.userid\n" + " ,pdin.usercode\n" + " ,pdin.username\n" + " FROM TP_PM_ProductionDataIn pdin\n" + " WHERE pdin.valueflag = '1'\n" + " AND pdin.accountid = :accountid\n" + " AND pdin.Barcode = :Barcode\n" + " AND EXISTS (SELECT 1\n" + " FROM TP_PC_DefectProcedureJobs dpJobs\n" + " WHERE dpJobs.DefectID = :DefectID\n" + " AND pdin.ProcedureID = dpJobs.ProcedureID)\n" + " ORDER BY pdin.ProductionDataID"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":accountid",OracleDbType.Int32, accountid,ParameterDirection.Input), new OracleParameter(":Barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":DefectID",OracleDbType.Int32, defectid,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); ds.Tables[0].TableName = "TProcedure"; //sqlString = "Select DictionaryID,DictionaryValue" // + " from TP_MST_DataDictionary where valueflag = 1 and DictionaryType = 'ASE002' and AccountID = :AccountID"; //paras = new Oracle.DataAccess.Client.OracleParameter[] // { // new Oracle.DataAccess.Client.OracleParameter(":AccountID",accountid), // }; //DataSet ds2 = con.GetSqlResultToDs(sqlString, paras); //ds2.Tables[0].TableName = "TDataDictionary"; sqlString = "select procedureid,jobsid from TP_PC_DefectProcedureJobs where TP_PC_DefectProcedureJobs.Defectid=:Defectid"; paras = new Oracle.DataAccess.Client.OracleParameter[] { new Oracle.DataAccess.Client.OracleParameter(":Defectid",defectid), }; DataSet ds3 = con.GetSqlResultToDs(sqlString, paras); ds3.Tables[0].TableName = "TP_PC_DefectProcedureJobs"; if (!dsReturn.Tables.Contains("TProcedure")) { dsReturn.Tables.Add(ds.Tables[0].Copy()); } //if (!dsReturn.Tables.Contains("TDataDictionary")) //{ // dsReturn.Tables.Add(ds2.Tables[0].Copy()); //} if (!dsReturn.Tables.Contains("TP_PC_DefectProcedureJobs")) { dsReturn.Tables.Add(ds3.Tables[0].Copy()); } return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取责任工种 /// /// /// /// /// public static DataSet GetDutyJobsCodeByUser(int classesSettingID, int defectid, int procedureID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT DISTINCT cd.UserID, jobs.JobsID, jobs.JobsName, jobs.JobsCode\n" + " FROM TP_PC_DefectProcedureJobs dpjobs\n" + " INNER JOIN tp_pc_classesdetail cd\n" + " ON cd.ujobsid = dpjobs.jobsid AND cd.valueflag = '1'\n" + " LEFT JOIN TP_MST_Jobs jobs\n" + " ON cd.UJobsID = jobs.JobsID\n" + " WHERE dpjobs.procedureid = :procedureid\n" + " AND dpjobs.defectid = :defectid\n" + " AND cd.classessettingid = :classessettingid\n" + " order by jobs.JobsCode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":procedureid",OracleDbType.Int32, procedureID,ParameterDirection.Input), new OracleParameter(":defectid",OracleDbType.Int32, defectid,ParameterDirection.Input), new OracleParameter(":classessettingid",OracleDbType.Int32, classesSettingID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取责任员工 /// /// 生产数据ID /// 用户ID /// 工种ID /// DataSet public static DataSet GetDutyStaffByUserIDAndJobs(int classesSettingID, int jobsID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_HR_Staff.StaffID, TP_HR_Staff.StaffCode, TP_HR_Staff.StaffName, TP_HR_Staff.StaffStatus, tp_pc_classesdetail.SJobsID from tp_pc_classesdetail inner join TP_HR_Staff on tp_pc_classesdetail.StaffID = TP_HR_Staff.StaffID where tp_pc_classesdetail.ClassesSettingID=:classesSettingID and tp_pc_classesdetail.valueflag = '1' and TP_HR_Staff.valueflag = '1' and tp_pc_classesdetail.UJobsID = :JobsID order by TP_HR_Staff.StaffCode "; // --and TP_HR_Staff.StaffStatus in (1,2) OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":classesSettingID",OracleDbType.Int32, classesSettingID,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, jobsID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 注浆盘点 /// /// 获取注浆盘点信息 /// /// 盘点类 /// 用户基本信息 /// public static DataSet GetAllGBChecked(InCheckedEntity entity, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); string sqlString = "SELECT gbc.GBCheckedID\n" + " ,gbc.GBCheckedNo\n" + " ,gbc.GBcheckname\n" + " ,gbc.AccountDate\n" + " ,gbc.Remarks\n" + " ,gbc.CreateTime\n" + " ,u.UserName AS CreateUserName\n" + " ,u.UserCode AS CreateUserCode\n" + " FROM Tp_Pm_Groutingchecked gbc\n" + " LEFT JOIN TP_MST_USER u\n" + " ON gbc.Createuserid = u.userid\n" + " WHERE gbc.valueflag = '1'\n" + " AND gbc.accountid = :accountid\n" + " AND (:CheckedNo IS NULL OR instr(gbc.GBCheckedNo, :CheckedNo) > 0)\n" + " AND gbc.CreateTime >= :begindate\n" + " AND gbc.CreateTime <= :enddate\n" + " AND (:CheckedName IS NULL OR instr(gbc.gbcheckname, :CheckedName) > 0)\n" + " AND (:remarks IS NULL OR instr(gbc.remarks, :remarks) > 0)\n" + " AND EXISTS (SELECT 1\n" + " FROM tp_pm_gbcheckeduser\n" + " WHERE userid = :userid\n" + " AND GBCheckedID = gbc.gbCheckedID)\n" + " ORDER BY gbc.gbCheckedID DESC"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":CheckedNo",OracleDbType.NVarchar2, entity.InCheckedNo,ParameterDirection.Input), new OracleParameter(":CheckedName",OracleDbType.NVarchar2, entity.InCheckedName,ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.NVarchar2, entity.Remarks,ParameterDirection.Input), new OracleParameter(":begindate",OracleDbType.Date, entity.BeginDate,ParameterDirection.Input), new OracleParameter(":enddate",OracleDbType.Date,entity.EndDate,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; DataSet returnDataSet = oracleConn.GetSqlResultToDs(sqlString, paras); return returnDataSet; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion #region 模具盘点 /// /// 获取模具盘点信息 /// /// 盘点类 /// 用户基本信息 /// public static DataSet GetAllMouldChecked(InCheckedEntity entity, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); string sqlString = "SELECT gbc.CheckedID\n" + " ,gbc.CheckedNo\n" + " ,gbc.checkname\n" + " ,gbc.AccountDate\n" + " ,gbc.Remarks\n" + " ,gbc.CreateTime\n" + " ,u.UserName AS CreateUserName\n" + " ,u.UserCode AS CreateUserCode\n" + " FROM tp_pc_mouldchecked gbc\n" + " LEFT JOIN TP_MST_USER u\n" + " ON gbc.Createuserid = u.userid\n" + " WHERE gbc.valueflag = '1'\n" + " AND gbc.accountid = :accountid\n" + " AND (:CheckedNo IS NULL OR instr(gbc.CheckedNo, :CheckedNo) > 0)\n" + " AND gbc.CreateTime >= :begindate\n" + " AND gbc.CreateTime <= :enddate\n" + " AND (:CheckedName IS NULL OR instr(gbc.checkname, :CheckedName) > 0)\n" + " AND (:remarks IS NULL OR instr(gbc.remarks, :remarks) > 0)\n" + " AND EXISTS (SELECT 1\n" + " FROM tp_pc_mouldcheckeduser\n" + " WHERE userid = :userid\n" + " AND CheckedID = gbc.CheckedID)\n" + " ORDER BY gbc.CheckedID DESC"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":CheckedNo",OracleDbType.NVarchar2, entity.InCheckedNo,ParameterDirection.Input), new OracleParameter(":CheckedName",OracleDbType.NVarchar2, entity.InCheckedName,ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.NVarchar2, entity.Remarks,ParameterDirection.Input), new OracleParameter(":begindate",OracleDbType.Date, entity.BeginDate,ParameterDirection.Input), new OracleParameter(":enddate",OracleDbType.Date,entity.EndDate,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; DataSet returnDataSet = oracleConn.GetSqlResultToDs(sqlString, paras); return returnDataSet; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion #region 模具管理 /// /// 验证模具管理生产工号 /// /// /// public static DataTable GetUserCodeOnMould(SUserInfo sUserInfo, string userCode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT g.userid, g.usercode\n" + " FROM tp_mst_user g\n" + " WHERE g.usercode = :usercode"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":usercode", OracleDbType.NVarchar2, userCode, ParameterDirection.Input), }; return con.GetSqlResultToDt(sqlString, parameters); } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取模具编辑信息 /// /// /// public static DataTable GetMouldEditInfo(SUserInfo sUserInfo, string mouldbarcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT m.mouldid\n" + " ,m.mouldbarcode\n" + " ,m.mouldtype\n" + " ,dd.dictionaryvalue mouldtypeName\n" + " ,m.materialsupplier\n" + " ,mms.suppliername\n" + " ,ms.mouldstatusname\n" + " ,m.productiondate\n" + " ,m.goodsid\n" + " ,m.goodscode\n" + " ,m.userid\n" + " ,m.usercode\n" + " ,m.weight\n" + " ,m.cost\n" + " ,m.standardgroutingnum\n" + " ,m.remarks\n" + " FROM tp_pc_mould m\n" + " LEFT JOIN tp_mst_datadictionary dd\n" + " ON dd.dictionaryid = m.mouldtype\n" + " LEFT JOIN tp_mst_mouldmaterialsuppliers mms\n" + " ON mms.supplierid = m.materialsupplier\n" + " LEFT JOIN tp_sys_mouldstatus ms\n" + " ON ms.mouldstatusid = m.mouldstatus\n" + " WHERE m.mouldbarcode = :mouldbarcode"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":mouldbarcode", OracleDbType.Varchar2, mouldbarcode, ParameterDirection.Input), }; return con.GetSqlResultToDt(sqlString, parameters); } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 成型模具管理 /// /// 获取当前用户成型线模具管理权限 /// /// /// /// public static DataTable GetGMouldStatusRight(SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT f.functioncode, f.functionname\n" + " FROM tp_sys_function f\n" + " WHERE f.valueflag = '1'\n" + " AND f.functionprogram = '1'\n" + " AND f.functionflag = '0'\n" + " AND f.functionbuttonflag = '1'\n" + " AND f.functioncode LIKE '0402%'\n" + " AND EXISTS (SELECT 1\n" + " FROM tp_mst_userright ur\n" + " WHERE (ur.functioncode = '[ALL]' OR\n" + " f.functioncode = ur.functioncode)\n" + " AND ur.userid = :userid)\n" + " ORDER BY f.functionlevel"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; return con.GetSqlResultToDt(sqlString, parameters); } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取成型线状态等信息，和成型模具信息 /// /// /// /// public static DataSet GetGroutingLineMould(SUserInfo sUserInfo, string groutingLineCode, int? groutingLineID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT gl.groutinglineid\n" + " ,gl.groutinglinecode\n" + " ,gl.gmouldstatus GroutingLineStatus\n" + " ,gs.gmouldstatusname GroutingLineStatusName\n" + " ,to_char(gl.optimestamp, 'yyyy-mm-dd hh24:mi:ss.ff') LineOPTimeStamp\n" + " FROM tp_pc_groutingline gl\n" + " INNER JOIN tp_sys_gmouldstatus gs\n" + " ON gs.gmouldstatusid = gl.gmouldstatus\n" + " WHERE gl.valueflag = '1'\n" + " AND gl.gmouldstatus > 0\n" + " AND ((:lineID IS NULL AND gl.groutinglinecode = :lineCode) OR\n" + " gl.groutinglineid = :lineID)"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":lineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input), new OracleParameter(":lineCode", OracleDbType.Varchar2, groutingLineCode, ParameterDirection.Input), }; DataTable line = con.GetSqlResultToDt(sqlString, parameters); if (line == null || line.Rows.Count == 0) { return null; } if (groutingLineID == null) { groutingLineID = Convert.ToInt32(line.Rows[0]["groutinglineid"]); } sqlString = "SELECT gld.groutinglinedetailid GLineDetailID\n" + " ,gld.groutinglineid\n" + " ,gld.groutingmouldcode GLineDetailCode\n" + " ,gld.MouldID\n" + " ,gld.MouldCode\n" + " ,nvl(gld.MouldOutputNo,0) MouldOutputNo\n" + " ,m.MouldBarcode\n" + " ,gld.GMouldStatus\n" + " ,gms.GMouldStatusName\n" + " ,gld.goodsid\n" + " ,g.goodscode\n" + " ,g.GOODSSPECIFICATION\n" + " ,gld.standardgroutingcount\n" + " ,gld.groutingcount\n" + " ,gld.beginuseddate\n" + " ,gld.remarks DetailRemarks\n" + " ,gmr.remarks RecordRemarks\n" + " ,to_char(gld.optimestamp, 'yyyy-mm-dd hh24:mi:ss.ff') OPTimeStamp\n" + " FROM tp_pc_groutinglinedetail gld\n" + " INNER JOIN tp_mst_goods g\n" + " ON g.goodsid = gld.goodsid\n" + " LEFT JOIN tp_pc_mould m\n" + " ON (gld.mouldid is not null and gld.mouldid = m.mouldid) or (gld.mouldcode = m.mouldcode)\n" + " INNER JOIN tp_sys_gmouldstatus gms\n" + " ON gms.gmouldstatusid = gld.gmouldstatus\n" + " LEFT JOIN tp_pc_gmouldrecord gmr\n" + " ON gmr.gmouldrecordid = gld.lastgmouldrecordid\n" + " WHERE gld.valueflag = '1'\n" + " AND gld.groutinglineid = :lineID\n" + " ORDER BY gld.groutingmouldcode"; parameters = new OracleParameter[] { new OracleParameter(":lineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input), }; DataTable lineDetail = con.GetSqlResultToDt(sqlString, parameters); DataSet ds = new DataSet(); line.TableName = "GroutingLineInfo"; lineDetail.TableName = "GMouldInfo"; ds.Tables.Add(line); ds.Tables.Add(lineDetail); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取成型线状态等信息，和成型模具信息 /// /// /// /// public static DataTable GetGroutingMouldOperationInit(SUserInfo sUserInfo, int groutingMouldOperationType) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // 结束换模、结束变产 if (groutingMouldOperationType == 6) { string sqlString = "SELECT s.settingcode, s.settingvalue\n" + " FROM tp_mst_systemsetting s\n" + " WHERE s.accountid = :accountid\n" + " AND s.settingcode = :settingcode"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":settingcode", OracleDbType.Varchar2, "S_PC_001", ParameterDirection.Input), }; return con.GetSqlResultToDt(sqlString, parameters); } return null; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 成型月度结算表 /// /// 成型月度结算表(总表) /// /// public static ServiceResultEntity GetGroutingSettlementInfo(SUserInfo sUserInfo, DateTime month) { IDBTransaction tran = null; try { tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); tran.Connect(); // 只限成型工查询 string sqlString = "select u.userid, u.isgroutingworker from tp_mst_user u where u.AccountID = :AccountID and u.usercode = :usercode"; OracleParameter[] paraUser = new OracleParameter[] { new OracleParameter(":AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":usercode", OracleDbType.NVarchar2, sUserInfo.UserCode, ParameterDirection.Input), }; DataTable dt = tran.GetSqlResultToDt(sqlString, paraUser); if (dt == null || dt.Rows.Count == 0) { ServiceResultEntity sreUser = new ServiceResultEntity(); sreUser.Status = Constant.ServiceResultStatus.Other; sreUser.Message = "系统中不存在此成型工号"; return sreUser; } if ("1" != (dt.Rows[0]["isgroutingworker"] + "")) { ServiceResultEntity sreUser = new ServiceResultEntity(); sreUser.Status = Constant.ServiceResultStatus.Other; sreUser.Message = "此工号不是成型工号"; return sreUser; } OracleParameter[] parameters = new OracleParameter[] { new OracleParameter("in_AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter("in_UserCode", OracleDbType.NVarchar2, sUserInfo.UserCode, ParameterDirection.Input), new OracleParameter("in_MonthBegin", OracleDbType.Date, month, ParameterDirection.Input), new OracleParameter("in_NextMonth", OracleDbType.Date, month.AddMonths(1), ParameterDirection.Input), new OracleParameter("out_DataM", OracleDbType.RefCursor, null, ParameterDirection.Output), }; DataSet data = tran.ExecStoredProcedure("PRO_P2_GetGUserPInfoByMonth", parameters); ServiceResultEntity sre = new ServiceResultEntity(); if (data != null) { data.Tables[0].TableName = "DataM"; } tran.Commit(); sre.Data = data; return sre; } catch (Exception ex) { //if (tran != null) //{ // tran.Rollback(); //} throw ex; } finally { if (tran != null && tran.ConnState == ConnectionState.Open) { tran.Disconnect(); } } } /// /// 成型月度结算表(明细) /// /// public static ServiceResultEntity GetGroutingSettlementDetail(SUserInfo sUserInfo, DateTime month, string goodsCode, DateTime? date) { IDBTransaction tran = null; try { tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); tran.Connect(); // 只限成型工查询 string sqlString = "select u.userid, u.isgroutingworker from tp_mst_user u where u.AccountID = :AccountID and u.usercode = :usercode"; OracleParameter[] paraUser = new OracleParameter[] { new OracleParameter(":AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":usercode", OracleDbType.NVarchar2, sUserInfo.UserCode, ParameterDirection.Input), }; DataTable dt = tran.GetSqlResultToDt(sqlString, paraUser); if (dt == null || dt.Rows.Count == 0) { ServiceResultEntity sreUser = new ServiceResultEntity(); sreUser.Status = Constant.ServiceResultStatus.Other; sreUser.Message = "系统中不存在此成型工号"; return sreUser; } if ("1" != (dt.Rows[0]["isgroutingworker"] + "")) { ServiceResultEntity sreUser = new ServiceResultEntity(); sreUser.Status = Constant.ServiceResultStatus.Other; sreUser.Message = "此工号不是成型工号"; return sreUser; } OracleParameter[] parameters = new OracleParameter[] { new OracleParameter("in_AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter("in_UserCode", OracleDbType.NVarchar2, sUserInfo.UserCode, ParameterDirection.Input), new OracleParameter("in_GoodsCode", OracleDbType.NVarchar2, goodsCode, ParameterDirection.Input), new OracleParameter("in_MonthBegin", OracleDbType.Date, (date != null ? date.Value : month), ParameterDirection.Input), new OracleParameter("in_NextMonth", OracleDbType.Date, (date != null ? date.Value.AddDays(1) :month.AddMonths(1)), ParameterDirection.Input), new OracleParameter("out_DataCC", OracleDbType.RefCursor, null, ParameterDirection.Output), new OracleParameter("out_DataNS", OracleDbType.RefCursor, null, ParameterDirection.Output), new OracleParameter("out_DataSC", OracleDbType.RefCursor, null, ParameterDirection.Output), new OracleParameter("out_DataRC", OracleDbType.RefCursor, null, ParameterDirection.Output), }; //,out_DataCC OUT SYS_REFCURSOR -- 成检明细 //,out_DataNS OUT SYS_REFCURSOR -- 后损明细 //,out_DataSC OUT SYS_REFCURSOR -- 半检不合格 //,out_DataRC OUT SYS_REFCURSOR -- 返工合格 DataSet data = tran.ExecStoredProcedure("PRO_P2_GetGUserPInfoByDetail", parameters); ServiceResultEntity sre = new ServiceResultEntity(); if (data != null) { data.Tables[0].TableName = "DataCC"; // 成检明细 data.Tables[1].TableName = "DataNS"; // 后损明细 data.Tables[2].TableName = "DataSC"; // 半检不合格 data.Tables[3].TableName = "DataRC"; // 返工合格 } tran.Commit(); sre.Data = data; return sre; } catch (Exception ex) { //if (tran != null) //{ // tran.Rollback(); //} throw ex; } finally { if (tran != null && tran.ConnState == ConnectionState.Open) { tran.Disconnect(); } } } #endregion #region 产成品质量改判 /// /// 获取产成品信息及缺陷数据 /// /// /// /// public static ServiceResultEntity GetDefectData(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); ServiceResultEntity sre = new ServiceResultEntity(); string gbarcode = null; #region 数据验证 // 1、获得注浆条码 string sqlString = @"select FUN_CMN_GetBarCode(:barcode, null, :accountid) From DUAL"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input) }; DataSet dsResult = oracleConn.GetSqlResultToDs(sqlString, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { gbarcode = dsResult.Tables[0].Rows[0][0].ToString(); } // 2.判断产品是否在产成品表中 sqlString = @"SELECT 1 FROM TP_PM_FINISHEDPRODUCT F WHERE F.VALUEFLAG = 1 AND F.GOODSLEVELTYPEID IN(4,5) AND F.BARCODE = :BARCODE"; paras = new OracleParameter[] { new OracleParameter(":BARCODE", OracleDbType.NVarchar2, gbarcode, ParameterDirection.Input) }; dsResult = oracleConn.GetSqlResultToDs(sqlString, paras); if (dsResult == null || dsResult.Tables.Count == 0 || dsResult.Tables[0].Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.Other; sre.OtherStatus = -11; sre.Message = "条码【" + barcode + "】,不符合条件"; return sre; } #endregion #region 查询产品相关信息 // 查询总表 sqlString = "SELECT GD.BARCODE,\n" + " GOODSTYPE.GOODSTYPENAME,\n" + " GD.GOODSID,\n" + " GD.GOODSCODE,\n" + " L.LOGONAME || '[' || L.LOGOCODE || ']' AS LOGONAME,\n" + " TO_CHAR(GD.GROUTINGDATE, 'yyyy-mm-dd') || '(' || GD.GROUTINGBATCHNO || ')' AS GROUTINGDATE,\n" + " GD.USERCODE,\n" + " GD.GROUTINGMOULDCODE\n" + " FROM TP_PM_GROUTINGDAILYDETAIL GD\n" + " INNER JOIN TP_MST_LOGO L\n" + " ON GD.LOGOID = L.LOGOID\n" + " INNER JOIN TP_MST_GOODS GOODS\n" + " ON GD.GOODSID = GOODS.GOODSID\n" + " INNER JOIN TP_MST_GOODSTYPE GOODSTYPE\n" + " ON GOODSTYPE.GOODSTYPEID = GOODS.GOODSTYPEID\n" + " WHERE GD.VALUEFLAG = '1'\n" + " AND GD.BARCODE = :BARCODE"; dsResult = oracleConn.GetSqlResultToDs(sqlString, paras); // 查询缺陷 if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { PDADefectData defectData = new PDADefectData(); defectData.PDAGoodsDataTotal = dsResult.Tables[0]; sqlString = "SELECT PD.PRODUCTIONDATAID,\n" + " PD.PROCEDUREID,\n" + " PD.PROCEDURENAME,\n" + " PD.USERCODE AS PROCEDUREUSERCODE,\n" + " TO_CHAR(PD.CREATETIME, 'yyyy-mm-dd') AS PROCEDURETIME,\n" + " GLT.GOODSLEVELTYPENAME,\n" + " PD.ISREFIRE,\n" + " PD.SPECIALREPAIRFLAG\n" + " FROM TP_PM_PRODUCTIONDATA PD\n" + " LEFT JOIN TP_SYS_GOODSLEVELTYPE GLT\n" + " ON PD.GOODSLEVELTYPEID = GLT.GOODSLEVELTYPEID\n" + " WHERE PD.MODELTYPE = -1\n" + " AND PD.VALUEFLAG = '1'\n" + " AND PD.GOODSLEVELTYPEID IN (5, 6)\n" + " AND PD.BARCODE = :BARCODE"; dsResult = oracleConn.GetSqlResultToDs(sqlString, paras); // 查询缺陷明细 if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { PDADefectDataDetail defectDataDetail; foreach (DataRow row in dsResult.Tables[0].Rows) { // 转实体 defectDataDetail = DataConvert.DataRowConvertToObject(row); sqlString = "SELECT D.PRODUCTIONDEFECTID,\n" + " D.DEFECTID,\n" + " D.DEFECTCODE,\n" + " D.DEFECTNAME,\n" + " D.DEFECTPOSITIONID,\n" + " D.DEFECTPOSITIONCODE,\n" + " D.DEFECTPOSITIONNAME,\n" + " D.DEFECTPROCEDUREID,\n" + " D.DEFECTPROCEDURECODE,\n" + " D.DEFECTPROCEDURENAME,\n" + " D.DEFECTUSERID,\n" + " D.DEFECTUSERCODE,\n" + " D.DEFECTUSERNAME,\n" + " D.DEFECTJOBS,\n" + " J.JOBSNAME AS DEFECTJOBSNAME,\n" + " D.DEFECTFINE,\n" + " DF.DEFECTFINECODE,\n" + " D.DEFECTDEDUCTIONNUM,\n" + " D.SPECIALDEFECT\n" + " FROM TP_PM_PRODUCTIONDATA PD\n" + " INNER JOIN TP_PM_DEFECT D\n" + " ON PD.PRODUCTIONDATAID = D.PRODUCTIONDATAID\n" + " LEFT JOIN TP_MST_JOBS J\n" + " ON J.JOBSID = D.DEFECTJOBS\n" + " LEFT JOIN TP_MST_DEFECTFINE DF\n" + " ON DF.DEFECTFINEID = D.DEFECTFINE\n" + " WHERE PD.MODELTYPE = -1\n" + " AND PD.VALUEFLAG = '1'\n" + " AND PD.GOODSLEVELTYPEID IN (4, 5, 6)\n" + " AND PD.PRODUCTIONDATAID = :PRODUCTIONDATAID"; paras = new OracleParameter[] { new OracleParameter(":PRODUCTIONDATAID", OracleDbType.NVarchar2, defectDataDetail.ProductionDataID, ParameterDirection.Input) }; DataTable dtResult = oracleConn.GetSqlResultToDt(sqlString, paras); defectDataDetail.ProductionDefects = DataConvert.TableConvertToObject(dtResult); // 查询缺陷责任者和缺陷图片 if (dtResult != null && dtResult.Rows.Count > 0) { int productionDefectID = 0; List productionDefects = new List(); PDADefectEntity productionDefect; for (int i = 0; i < dtResult.Rows.Count; i++) { productionDefect = new PDADefectEntity(); productionDefectID = int.Parse(dtResult.Rows[i]["PRODUCTIONDEFECTID"] + ""); sqlString = "SELECT D.PRODUCTIONDEFECTID,\n" + " D.STAFFID,\n" + " S.STAFFNAME,\n" + " D.USERID,\n" + " D.USERCODE,\n" + " D.STAFFSTATUS,\n" + " D.UJOBSID,\n" + " D.SJOBSID\n" + " FROM TP_PM_DEFECTRESPONSIBLE D\n" + " LEFT JOIN TP_HR_STAFF S\n" + " ON D.STAFFID = S.STAFFID\n" + " WHERE PRODUCTIONDEFECTID = :PRODUCTIONDEFECTID"; paras = new OracleParameter[] { new OracleParameter(":PRODUCTIONDEFECTID", OracleDbType.Int32, productionDefectID, ParameterDirection.Input) }; DataTable dtDefectResponsible = oracleConn.GetSqlResultToDt(sqlString, paras); defectDataDetail.ProductionDefects[i].DefectResponsibles = DataConvert.TableConvertToObject(dtDefectResponsible); } } defectData.PDADefectDataDetailList.Add(defectDataDetail); } sre.Status = Constant.ServiceResultStatus.Success; sre.Message = "条码【" + barcode + "】,查询成功"; } sre.Result = defectData; } else { sre.Status = Constant.ServiceResultStatus.Other; sre.OtherStatus = -12; sre.Message = "条码【" + barcode + "】,未查到相应的产品信息"; return sre; } #endregion return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 成品改判 /// /// 工序ID /// 生产数据集 /// 用户基本信息 /// string public static ServiceResultEntity SaveDefectData(ProductionDataEntity productionDataEntity, SUserInfo sUserInfo) { string errMsg = ""; ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 验证数据是否已经被修改 string sqlString = "SELECT 1 FROM TP_PM_PRODUCTIONDATA PD WHERE PD.VALUEFLAG = '1' AND PD.PRODUCTIONDATAID = :PRODUCTIONDATAID"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":PRODUCTIONDATAID",OracleDbType.Int32, productionDataEntity.ProductionDataID,ParameterDirection.Input) }; DataSet dsResult = oracleTrConn.GetSqlResultToDs(sqlString, paras); if (dsResult == null || dsResult.Tables.Count == 0 || dsResult.Tables[0].Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = "【此条码生产数据已被修改，请重新扫描此条码】"; return sre; } #endregion #region 查询新插入的生产数据ID sqlString = "SELECT SEQ_PM_PRODUCTIONDATA_ID.NEXTVAL FROM DUAL"; string idStr = oracleTrConn.GetSqlResultToStr(sqlString); int newProductionDataID = 0; if (string.IsNullOrEmpty(idStr) || !int.TryParse(idStr, out newProductionDataID)) { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = string.Format(Messages.MSG_CMN_W001, "条码", "保存"); return sre; } #endregion #region 插入生产数据 sqlString = "INSERT INTO TP_PM_PRODUCTIONDATA(\n" + " PRODUCTIONDATAID,\n" + " BARCODE,\n" + " CENTRALIZEDBATCHNO,\n" + " PRODUCTIONLINEID,\n" + " PRODUCTIONLINECODE,\n" + " PRODUCTIONLINENAME,\n" + " PROCEDUREID,\n" + " PROCEDURECODE,\n" + " PROCEDURENAME,\n" + " PROCEDUREMODEL,\n" + " MODELTYPE,\n" + " PIECETYPE,\n" + " ISREWORKED,\n" + " NODETYPE,\n" + " ISPUBLICBODY,\n" + " ISREFIRE,\n" + " ORGANIZATIONID,\n" + " GOODSID,\n" + " GOODSCODE,\n" + " GOODSNAME,\n" + " USERID,\n" + " USERCODE,\n" + " USERNAME,\n" + " KILNID,\n" + " KILNCODE,\n" + " KILNNAME,\n" + " KILNCARID,\n" + " KILNCARCODE,\n" + " KILNCARNAME,\n" + " KILNCARBATCHNO,\n" + " KILNCARPOSITION,\n" + " REWORKPROCEDUREID,\n" + " REWORKPROCEDURECODE,\n" + " REWORKPROCEDURENAME,\n" + " REMARKS,\n" + " ACCOUNTDATE,\n" + " SETTLEMENTFLAG,\n" + " ACCOUNTID,\n" + " VALUEFLAG,\n" + " CREATETIME,\n" + " CREATEUSERID,\n" + " UPDATETIME,\n" + " UPDATEUSERID,\n" + " OPTIMESTAMP,\n" + " GOODSLEVELID,\n" + " GOODSLEVELTYPEID,\n" + " GROUTINGDATE,\n" + " GROUTINGMOULDCODE,\n" + " MOULDCODE,\n" + " GROUTINGUSERID,\n" + " GROUTINGUSERCODE,\n" + " GROUTINGNUM,\n" + " GROUTINGDAILYID,\n" + " GROUTINGDAILYDETAILID,\n" + " GROUTINGLINEID,\n" + " GROUTINGLINECODE,\n" + " GROUTINGLINENAME,\n" + " GMOULDTYPEID,\n" + " CANMANYTIMES,\n" + " GROUTINGLINEDETAILID,\n" + " SPECIALREPAIRFLAG,\n" + " CLASSESSETTINGID,\n" + " LOGOID,\n" + " BACKOUTTIME,\n" + " BACKOUTUSERID,\n" + " BACKOUTUSERCODE,\n" + " CHECKTIME,\n" + " TAMPERTIME)\n" + " SELECT :NEWPRODUCTIONDATAID,\n" + " BARCODE,\n" + " CENTRALIZEDBATCHNO,\n" + " PRODUCTIONLINEID,\n" + " PRODUCTIONLINECODE,\n" + " PRODUCTIONLINENAME,\n" + " PROCEDUREID,\n" + " PROCEDURECODE,\n" + " PROCEDURENAME,\n" + " PROCEDUREMODEL,\n" + " MODELTYPE,\n" + " PIECETYPE,\n" + " ISREWORKED,\n" + " NODETYPE,\n" + " ISPUBLICBODY,\n" + " ISREFIRE,\n" + " ORGANIZATIONID,\n" + " GOODSID,\n" + " GOODSCODE,\n" + " GOODSNAME,\n" + " :USERID,\n" + " :USERCODE,\n" + " :USERNAME,\n" + " KILNID,\n" + " KILNCODE,\n" + " KILNNAME,\n" + " KILNCARID,\n" + " KILNCARCODE,\n" + " KILNCARNAME,\n" + " KILNCARBATCHNO,\n" + " KILNCARPOSITION,\n" + " REWORKPROCEDUREID,\n" + " REWORKPROCEDURECODE,\n" + " REWORKPROCEDURENAME,\n" + " REMARKS,\n" + " ACCOUNTDATE,\n" + " SETTLEMENTFLAG,\n" + " ACCOUNTID,\n" + " '1',\n" + " CREATETIME,\n" + " :CREATEUSERID,\n" + " UPDATETIME,\n" + " :UPDATEUSERID,\n" + " OPTIMESTAMP,\n" + " GOODSLEVELID,\n" + " GOODSLEVELTYPEID,\n" + " GROUTINGDATE,\n" + " GROUTINGMOULDCODE,\n" + " MOULDCODE,\n" + " GROUTINGUSERID,\n" + " GROUTINGUSERCODE,\n" + " GROUTINGNUM,\n" + " GROUTINGDAILYID,\n" + " GROUTINGDAILYDETAILID,\n" + " GROUTINGLINEID,\n" + " GROUTINGLINECODE,\n" + " GROUTINGLINENAME,\n" + " GMOULDTYPEID,\n" + " CANMANYTIMES,\n" + " GROUTINGLINEDETAILID,\n" + " SPECIALREPAIRFLAG,\n" + " CLASSESSETTINGID,\n" + " LOGOID,\n" + " BACKOUTTIME,\n" + " BACKOUTUSERID,\n" + " BACKOUTUSERCODE,\n" + " CHECKTIME,\n" + " SYSDATE\n" + " FROM TP_PM_PRODUCTIONDATA\n" + " WHERE VALUEFLAG = '1'\n" + " AND PRODUCTIONDATAID = :PRODUCTIONDATAID"; paras = new OracleParameter[] { new OracleParameter(":NEWPRODUCTIONDATAID",OracleDbType.Int32, newProductionDataID,ParameterDirection.Input), new OracleParameter(":USERID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":USERCODE",OracleDbType.Varchar2, sUserInfo.UserCode,ParameterDirection.Input), new OracleParameter(":USERNAME",OracleDbType.Varchar2, sUserInfo.UserCode,ParameterDirection.Input), new OracleParameter(":CREATEUSERID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UPDATEUSERID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":PRODUCTIONDATAID",OracleDbType.Int32, productionDataEntity.ProductionDataID,ParameterDirection.Input) }; int returnRows = oracleTrConn.ExecuteNonQuery(sqlString, paras); #endregion #region 查询刚插入得数据 sqlString = "SELECT * FROM TP_PM_PRODUCTIONDATA PD WHERE PD.VALUEFLAG = '1' AND PD.PRODUCTIONDATAID = :PRODUCTIONDATAID"; paras = new OracleParameter[] { new OracleParameter(":PRODUCTIONDATAID",OracleDbType.Int32, newProductionDataID,ParameterDirection.Input) }; DataTable dtResult = oracleTrConn.GetSqlResultToDt(sqlString, paras); ProductionDataEntity newProductionDataEntity; if (dtResult != null && dtResult.Rows.Count > 0) { newProductionDataEntity = DataConvert.DataRowConvertToObject(dtResult.Rows[0]); } else { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = "【此条码生产数据已被修改，请重新扫描此条码】"; return sre; } #endregion #region 删除原数据 // 删除生产数据 sqlString = "UPDATE TP_PM_PRODUCTIONDATA\n" + " SET VALUEFLAG = '0'\n" + " WHERE PRODUCTIONDATAID = :PRODUCTIONDATAID"; paras = new OracleParameter[] { new OracleParameter(":PRODUCTIONDATAID",OracleDbType.Int32, productionDataEntity.ProductionDataID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sqlString, paras); // 删除缺陷 sqlString = "UPDATE TP_PM_DEFECT\n" + " SET VALUEFLAG = '0'\n" + " WHERE PRODUCTIONDATAID = :PRODUCTIONDATAID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlString, paras); // 删除缺陷责任人 sqlString = "UPDATE TP_PM_DEFECTRESPONSIBLE A\n" + " SET A.VALUEFLAG = '0'\n" + " WHERE A.PRODUCTIONDEFECTID IN\n" + " (SELECT PRODUCTIONDEFECTID\n" + " FROM TP_PM_DEFECT\n" + " WHERE PRODUCTIONDATAID = :PRODUCTIONDATAID)"; returnRows += oracleTrConn.ExecuteNonQuery(sqlString, paras); #endregion #region 条码有缺陷 // 存在缺陷 插入缺陷表 if (productionDataEntity.ProductionDefects != null) { #region 查询工序信息 ProcedureEntity procedure = GetProcedurByID(oracleTrConn, productionDataEntity.ProcedureID); #endregion #region 取最近一条缺陷记录 sqlString = "SELECT PRODUCTIONDEFECTID\n" + " FROM TP_PM_DEFECT\n" + " WHERE PRODUCTIONDATAID = :PRODUCTIONDATAID\n" + " ORDER BY PRODUCTIONDEFECTID DESC"; paras = new OracleParameter[] { new OracleParameter(":PRODUCTIONDATAID",OracleDbType.Int32, productionDataEntity.ProductionDataID,ParameterDirection.Input) }; string productionDefectID = oracleTrConn.GetSqlResultToStr(sqlString, paras); int originalProductionDefectID = 0; if (!string.IsNullOrEmpty(productionDefectID)) { originalProductionDefectID = int.Parse(productionDefectID); } #endregion foreach (ProductionDefectEntity productionDefect in productionDataEntity.ProductionDefects) { #region 保存缺陷 productionDefect.ProductionDataID = newProductionDataID; productionDefect.BarCode = newProductionDataEntity.Barcode; productionDefect.ProductionLineID = procedure.ProductionLineID; productionDefect.ProductionLineCode = procedure.ProductionlineCode; productionDefect.ProductionLineName = procedure.ProductionlineName; productionDefect.ProcedureID = procedure.ProcedureID; productionDefect.ProcedureCode = procedure.ProcedureCode; productionDefect.ProcedureName = procedure.ProcedureName; productionDefect.UserID = newProductionDataEntity.UserID; productionDefect.UserCode = newProductionDataEntity.UserCode; productionDefect.UserName = newProductionDataEntity.UserName; productionDefect.GoodsID = newProductionDataEntity.GoodsID; productionDefect.GoodsCode = newProductionDataEntity.GoodsCode; productionDefect.GoodsName = newProductionDataEntity.GoodsName; productionDefect.Remarks = newProductionDataEntity.Remarks; productionDefect.ScrapResponFlag = string.IsNullOrEmpty(productionDefect.ScrapResponFlag) ? "0" : productionDefect.ScrapResponFlag; productionDefect.ProductionDefectID = productionDefect.ProductionDefectID == 0 ? originalProductionDefectID : productionDefect.ProductionDefectID; productionDefect.CreateTime = newProductionDataEntity.CreateTime == null ? DateTime.Now : DateTime.Parse(newProductionDataEntity.CreateTime + ""); productionDefect.CheckTime = newProductionDataEntity.CheckTime; // 保存缺陷 errMsg = AddDefect(oracleTrConn, productionDefect, sUserInfo); if (!string.IsNullOrEmpty(errMsg)) { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = errMsg; return sre; } #endregion } } #endregion // 没有错误 提交事务 if (string.IsNullOrEmpty(errMsg)) { oracleTrConn.Commit(); } else { oracleTrConn.Rollback(); } } catch (Exception ex) { oracleTrConn.Rollback(); throw ex; } finally { // 释放资源 if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return sre; } /// /// 查询工序信息 /// /// 连接对象 /// 工序ID /// 工序实体 private static ProcedureEntity GetProcedurByID(IDBTransaction oracleTrConn, int procedureID) { ProcedureEntity procedureEntity = new ProcedureEntity(); string procsql = "pro_pm_searchProcedurbyID"; try { IDataParameter[] paras = new OracleParameter[] { new OracleParameter("in_procedureID", OracleDbType.Int32, procedureID, ParameterDirection.Input), new OracleParameter("out_result", OracleDbType.RefCursor, ParameterDirection.Output) }; DataSet returnDs = oracleTrConn.ExecStoredProcedure(procsql, paras); if (returnDs != null && returnDs.Tables.Count > Constant.INT_IS_ZERO && returnDs.Tables[0].Rows.Count > Constant.INT_IS_ZERO) { DataRow row = returnDs.Tables[0].Rows[0]; // 转实体 procedureEntity = DataConvert.DataRowConvertToObject(row); return procedureEntity; } else { return null; } } catch (Exception ex) { throw ex; } } /// /// 保存缺陷 /// /// 连接对象 /// 缺陷数据 /// 用户基本信息 /// string private static string AddDefect(IDBTransaction oracleTrConn, ProductionDefectEntity productionDefect, SUserInfo sUserInfo) { // 查询新插入的生产缺陷ID string sqlString = "select SEQ_PM_Defect_DefectID.nextval from dual"; string idStr = oracleTrConn.GetSqlResultToStr(sqlString); int newProductionDefectID = 0; if (!string.IsNullOrEmpty(idStr)) { newProductionDefectID = int.Parse(idStr); } else { return string.Format(Messages.MSG_CMN_W001, "条码", "保存"); } OracleParameter[] paras = null; if (productionDefect.ProductionDefectID != 0) { #region SQL sqlString = "INSERT INTO TP_PM_DEFECT\n" + " (PRODUCTIONDEFECTID,\n" + " PRODUCTIONDATAID,\n" + " BARCODE,\n" + " PRODUCTIONLINEID,\n" + " PRODUCTIONLINECODE,\n" + " PRODUCTIONLINENAME,\n" + " PROCEDUREID,\n" + " PROCEDURECODE,\n" + " PROCEDURENAME,\n" + " USERID,\n" + " USERCODE,\n" + " USERNAME,\n" + " GOODSID,\n" + " GOODSCODE,\n" + " GOODSNAME,\n" + " DEFECTID,\n" + " DEFECTCODE,\n" + " DEFECTNAME,\n" + " DEFECTPOSITIONID,\n" + " DEFECTPOSITIONCODE,\n" + " DEFECTPOSITIONNAME,\n" + " DEFECTPRODUCTIONDATAID,\n" + " DEFECTPROCEDUREID,\n" + " DEFECTPROCEDURECODE,\n" + " DEFECTPROCEDURENAME,\n" + " DEFECTUSERID,\n" + " DEFECTUSERCODE,\n" + " DEFECTUSERNAME,\n" + " DEFECTJOBS,\n" + " REMARKS,\n" + " ACCOUNTID,\n" + " VALUEFLAG,\n" + " CREATETIME,\n" + " CREATEUSERID,\n" + " UPDATETIME,\n" + " UPDATEUSERID,\n" + " SCRAPRESPONFLAG,\n" + " DEFECTFINE,\n" + " MISSEDUSERID,\n" + " MISSEDUSERCODE,\n" + " MISSEDUSERNAME,\n" + " SPECIALDEFECT,\n" + " DEFECTDEDUCTIONNUM,\n" + " CHECKTIME)\n" + " SELECT :NEWPRODUCTIONDEFECTID,\n" + " :PRODUCTIONDATAID,\n" + " BARCODE,\n" + " PRODUCTIONLINEID,\n" + " PRODUCTIONLINECODE,\n" + " PRODUCTIONLINENAME,\n" + " PROCEDUREID,\n" + " PROCEDURECODE,\n" + " PROCEDURENAME,\n" + " USERID,\n" + " USERCODE,\n" + " USERNAME,\n" + " GOODSID,\n" + " GOODSCODE,\n" + " GOODSNAME,\n" + " :DEFECTID,\n" + " :DEFECTCODE,\n" + " :DEFECTNAME,\n" + " :DEFECTPOSITIONID,\n" + " :DEFECTPOSITIONCODE,\n" + " :DEFECTPOSITIONNAME,\n" + " DEFECTPRODUCTIONDATAID,\n" + " :DEFECTPROCEDUREID,\n" + " :DEFECTPROCEDURECODE,\n" + " :DEFECTPROCEDURENAME,\n" + " :DEFECTUSERID,\n" + " :DEFECTUSERCODE,\n" + " :DEFECTUSERNAME,\n" + " :DEFECTJOBS,\n" + " REMARKS,\n" + " ACCOUNTID,\n" + " '1',\n" + " CREATETIME,\n" + " :CREATEUSERID,\n" + " UPDATETIME,\n" + " :UPDATEUSERID,\n" + " SCRAPRESPONFLAG,\n" + " :DEFECTFINE,\n" + " MISSEDUSERID,\n" + " MISSEDUSERCODE,\n" + " MISSEDUSERNAME,\n" + " SPECIALDEFECT,\n" + " :DEFECTDEDUCTIONNUM,\n" + " CHECKTIME\n" + " FROM TP_PM_DEFECT\n" + " WHERE PRODUCTIONDEFECTID = :PRODUCTIONDEFECTID"; #endregion #region 参数 paras = new OracleParameter[] { new OracleParameter(":NEWPRODUCTIONDEFECTID",OracleDbType.Int32,newProductionDefectID,ParameterDirection.Input), new OracleParameter(":PRODUCTIONDATAID",OracleDbType.Int32,productionDefect.ProductionDataID,ParameterDirection.Input), new OracleParameter(":DEFECTID",OracleDbType.Int32,productionDefect.DefectID,ParameterDirection.Input), new OracleParameter(":DEFECTCODE",OracleDbType.Varchar2,productionDefect.DefectCode,ParameterDirection.Input), new OracleParameter(":DEFECTNAME",OracleDbType.Varchar2,productionDefect.DefectName,ParameterDirection.Input), new OracleParameter(":DEFECTPOSITIONID",OracleDbType.Int32,productionDefect.DefectPositionID,ParameterDirection.Input), new OracleParameter(":DEFECTPOSITIONCODE",OracleDbType.Varchar2,productionDefect.DefectPositionCode,ParameterDirection.Input), new OracleParameter(":DEFECTPOSITIONNAME",OracleDbType.Varchar2,productionDefect.DefectPositionName,ParameterDirection.Input), new OracleParameter(":DEFECTPROCEDUREID",OracleDbType.Int32,productionDefect.DefectProcedureID,ParameterDirection.Input), new OracleParameter(":DEFECTPROCEDURECODE",OracleDbType.Varchar2,productionDefect.DefectProcedureCode,ParameterDirection.Input), new OracleParameter(":DEFECTPROCEDURENAME",OracleDbType.Varchar2,productionDefect.DefectProcedureName,ParameterDirection.Input), new OracleParameter(":DEFECTUSERID",OracleDbType.Int32,productionDefect.DefectUserID,ParameterDirection.Input), new OracleParameter(":DEFECTUSERCODE",OracleDbType.Varchar2,productionDefect.DefectUserCode,ParameterDirection.Input), new OracleParameter(":DEFECTUSERNAME",OracleDbType.Varchar2,productionDefect.DefectUserName,ParameterDirection.Input), new OracleParameter(":DEFECTJOBS",OracleDbType.Int32,productionDefect.DefectJobs,ParameterDirection.Input), new OracleParameter(":CREATEUSERID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UPDATEUSERID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":DEFECTFINE",OracleDbType.Int32,productionDefect.DefectFine,ParameterDirection.Input), new OracleParameter(":DefectDeductionNum",OracleDbType.Decimal,productionDefect.DefectDeductionNum==null?0:productionDefect.DefectDeductionNum,ParameterDirection.Input), new OracleParameter(":PRODUCTIONDEFECTID",OracleDbType.Int32,productionDefect.ProductionDefectID,ParameterDirection.Input) }; #endregion } else { #region SQL sqlString = "insert into tp_pm_defect" + " ( ProductionDefectID," + " productiondataid," + " barcode," + " productionlineid," + " productionlinecode," + " productionlinename," + " procedureid," + " procedurecode," + " procedurename," + " userid," + " usercode," + " username," + " goodsid," + " goodscode," + " goodsname," + " defectid," + " defectcode," + " defectname," + " defectpositionid," + " defectpositioncode," + " defectpositionname," + " scrapResponFlag," + " defectproductiondataid," + " defectprocedureid," + " defectprocedurecode," + " defectprocedurename," + " defectuserid," + " defectusercode," + " defectusername," + " defectjobs," + " remarks," + " accountid," + " createuserid," + " CREATETIME," + " defectfine," + " misseduserid," + " missedusercode," + " missedusername," + " updateuserid,SpecialDefect,DefectDeductionNum,CheckTime)" + " values" + " (" + " :ProductionDefectID," + " :productiondataid," + " :barcode," + " :productionlineid," + " :productionlinecode," + " :productionlinename," + " :procedureid," + " :procedurecode," + " :procedurename," + " :userid," + " :usercode," + " :username," + " :goodsid," + " :goodscode," + " :goodsname," + " :defectid," + " :defectcode," + " :defectname," + " :defectpositionid," + " :defectpositioncode," + " :defectpositionname," + " :scrapResponFlag," + " :defectproductiondataid," + " :defectprocedureid," + " :defectprocedurecode," + " :defectprocedurename," + " :defectuserid," + " :defectusercode," + " :defectusername," + " :defectjobs," + " :remarks," + " :accountid," + " :createuserid," + " :createtime," + " :defectfine," + " :misseduserid," + " :missedusercode," + " :missedusername," + " :updateuserid,:SpecialDefect,:DefectDeductionNum,:CheckTime)"; #endregion #region 参数 paras = new OracleParameter[] { new OracleParameter(":ProductionDefectID",newProductionDefectID), new OracleParameter(":productiondataid",productionDefect.ProductionDataID), new OracleParameter(":barcode",productionDefect.BarCode), new OracleParameter(":productionlineid",productionDefect.ProductionLineID), new OracleParameter(":productionlinecode",productionDefect.ProductionLineCode), new OracleParameter(":productionlinename",productionDefect.ProductionLineName), new OracleParameter(":procedureid",productionDefect.ProcedureID), new OracleParameter(":procedurecode",productionDefect.ProcedureCode), new OracleParameter(":procedurename",productionDefect.ProcedureName), new OracleParameter(":userid",productionDefect.UserID), new OracleParameter(":usercode",productionDefect.UserCode), new OracleParameter(":username",productionDefect.UserName), new OracleParameter(":goodsid",productionDefect.GoodsID), new OracleParameter(":goodscode",productionDefect.GoodsCode), new OracleParameter(":goodsname",productionDefect.GoodsName), new OracleParameter(":defectid",productionDefect.DefectID), new OracleParameter(":defectcode",productionDefect.DefectCode), new OracleParameter(":defectname",productionDefect.DefectName), new OracleParameter(":defectpositionid",productionDefect.DefectPositionID), new OracleParameter(":defectpositioncode",productionDefect.DefectPositionCode), new OracleParameter(":defectpositionname",productionDefect.DefectPositionName), new OracleParameter(":scrapResponFlag",productionDefect.ScrapResponFlag), new OracleParameter(":defectproductiondataid",productionDefect.DefectProductionDataID), new OracleParameter(":defectprocedureid",productionDefect.DefectProcedureID), new OracleParameter(":defectprocedurecode",productionDefect.DefectProcedureCode), new OracleParameter(":defectprocedurename",productionDefect.DefectProcedureName), new OracleParameter(":defectuserid",productionDefect.DefectUserID), new OracleParameter(":defectusercode",productionDefect.DefectUserCode), new OracleParameter(":defectusername",productionDefect.DefectUserName), new OracleParameter(":defectjobs",productionDefect.DefectJobs), new OracleParameter(":remarks",productionDefect.Remarks), new OracleParameter(":accountid",sUserInfo.AccountID), new OracleParameter(":createuserid",sUserInfo.UserID), new OracleParameter(":createtime",productionDefect.CreateTime), new OracleParameter(":defectfine",productionDefect.DefectFine), new OracleParameter(":misseduserid",productionDefect.MissedUserID), new OracleParameter(":missedusercode",productionDefect.MissedUserCode), new OracleParameter(":missedusername",productionDefect.MissedUserName), new OracleParameter(":updateuserid",sUserInfo.UserID), new OracleParameter(":SpecialDefect",productionDefect.SpecialDefect==null?"0":productionDefect.SpecialDefect), new OracleParameter(":DefectDeductionNum",productionDefect.DefectDeductionNum==null?0:productionDefect.DefectDeductionNum), new OracleParameter(":CheckTime",productionDefect.CheckTime) }; #endregion } int result = oracleTrConn.ExecuteNonQuery(sqlString, paras); // 保存失败 if (result < 1) { return string.Format(Messages.MSG_CMN_W001, "条码", "保存"); } #region 插入生产缺陷责任者 if (productionDefect.DefectResponsibles != null) { foreach (DefectResponsibleEntity DefectResponsible in productionDefect.DefectResponsibles) { #region SQL sqlString = "insert into tp_pm_defectresponsible" + " (productiondefectid," + " staffid," + " userid," + " usercode," + " Ujobsid," + " SJobsID," + " staffstatus," + " accountid," + " createuserid," + " updateuserid)" + " values" + " (:productiondefectid," + " :staffid," + " :userid," + " :usercode," + " :ujobsid," + " :sjobsid," + " :staffstatus," + " :accountid," + " :createuserid," + " :updateuserid)"; #endregion #region 参数 paras = new OracleParameter[] { new OracleParameter(":productiondefectid",OracleDbType.Int32,newProductionDefectID,ParameterDirection.Input), new OracleParameter(":staffid",OracleDbType.Int32,DefectResponsible.StaffID,ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32,DefectResponsible.UserID,ParameterDirection.Input), new OracleParameter(":usercode",OracleDbType.Varchar2,DefectResponsible.UserCode,ParameterDirection.Input), new OracleParameter(":ujobsid",OracleDbType.Int32,DefectResponsible.UJobsID,ParameterDirection.Input), new OracleParameter(":sjobsid",OracleDbType.Int32,DefectResponsible.SJobsID,ParameterDirection.Input), new OracleParameter(":staffstatus",OracleDbType.Int32,DefectResponsible.StaffStatus,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":createuserid",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":updateuserid",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input) }; #endregion result = oracleTrConn.ExecuteNonQuery(sqlString, paras); // 保存失败 if (result < 1) { return string.Format(Messages.MSG_CMN_W001, "条码", "保存"); } } } #endregion return null; } /// /// 通过条码查出责任工序(成品改判用) /// /// 产品条码 /// 缺陷ID /// DataSet public static ServiceResultEntity GetDutyProcedureByBarCodeForTamper(string barcode, int defectid, int accountid) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); ServiceResultEntity sre = new ServiceResultEntity(); DataSet dsReturn = new DataSet(); //string sqlString = "SELECT pdin.ProductionDataID\n" + // " ,pdin.ProcedureID AS DutyProcedureID\n" + // " ,pdin.ProcedureCode AS DutyProcedureCode\n" + // " ,pdin.ProcedureName AS DutyProcedureName\n" + // " ,pdin.NodeType\n" + // " ,pdin.ModelType\n" + // " ,pdin.classessettingid\n" + // " ,pdin.userid\n" + // " ,pdin.usercode\n" + // " ,pdin.username\n" + // " FROM TP_PM_ProductionData pdin\n" + // " WHERE pdin.valueflag = '1'\n" + // " AND pdin.accountid = :accountid\n" + // " AND pdin.Barcode = :Barcode\n" + // " AND EXISTS (SELECT 1\n" + // " FROM TP_PC_DefectProcedureJobs dpJobs\n" + // " WHERE dpJobs.DefectID = :DefectID\n" + // " AND pdin.ProcedureID = dpJobs.ProcedureID)\n" + // " ORDER BY pdin.ProductionDataID"; // 去掉重复工序（取最后一个） string sqlString = "select pdin.ProductionDataID\n" + " ,pdin.ProcedureID as DutyProcedureID\n" + " ,pdin.ProcedureCode as DutyProcedureCode\n" + " ,pdin.ProcedureName as DutyProcedureName\n" + " ,pdin.NodeType\n" + " ,pdin.ModelType\n" + " ,pdin.classessettingid\n" + " ,pdin.userid\n" + " ,pdin.usercode\n" + " ,pdin.username\n" + " from TP_PM_ProductionDataIn pdin\n" + " where pdin.productiondataid in\n" + " (select max(pdi.productiondataid) pdid\n" + " from TP_PM_ProductionDataIn pdi\n" + " where pdi.valueflag = '1'\n" + " and pdi.accountid = :accountid\n" + " and pdi.Barcode = :Barcode\n" + " and exists\n" + " (select 1\n" + " from TP_PC_DefectProcedureJobs dpJobs\n" + " where dpJobs.DefectID = :DefectID\n" + " and pdi.ProcedureID = dpJobs.ProcedureID)\n" + " group by pdi.ProcedureID)\n" + " order by pdin.ProductionDataID"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":accountid",OracleDbType.Int32, accountid,ParameterDirection.Input), new OracleParameter(":Barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":DefectID",OracleDbType.Int32, defectid,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); ds.Tables[0].TableName = "TProcedure"; sqlString = "select procedureid,jobsid from TP_PC_DefectProcedureJobs where TP_PC_DefectProcedureJobs.Defectid=:Defectid"; paras = new OracleParameter[] { new OracleParameter(":Defectid",defectid), }; DataSet ds3 = con.GetSqlResultToDs(sqlString, paras); ds3.Tables[0].TableName = "TP_PC_DefectProcedureJobs"; if (!dsReturn.Tables.Contains("TProcedure")) { dsReturn.Tables.Add(ds.Tables[0].Copy()); } if (!dsReturn.Tables.Contains("TP_PC_DefectProcedureJobs")) { dsReturn.Tables.Add(ds3.Tables[0].Copy()); } sre.Status = Constant.ServiceResultStatus.Success; sre.Data = dsReturn; return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 水效标识 public static ServiceResultEntity GetGoodsByBarCode(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); ServiceResultEntity sre = new ServiceResultEntity(); string gbarcode = null; #region 获得注浆条码 string sqlString = @"select FUN_CMN_GetBarCode(:barcode, null, :accountid) From DUAL"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input) }; DataTable dtResult = oracleConn.GetSqlResultToDt(sqlString, paras); if (dtResult != null && dtResult.Rows.Count > 0) { gbarcode = dtResult.Rows[0][0].ToString(); } else { sre.Status = Constant.ServiceResultStatus.Other; sre.OtherStatus = -11; sre.Message = "条码【" + barcode + "】,不符合条件。"; return sre; } //// 2.判断产品是否在产成品表中 //sqlString = @"SELECT 1 FROM TP_PM_FINISHEDPRODUCT F WHERE F.VALUEFLAG = 1 AND F.GOODSLEVELTYPEID IN(4,5) AND F.BARCODE = :BARCODE"; //paras = new OracleParameter[] //{ // new OracleParameter(":BARCODE", OracleDbType.NVarchar2, gbarcode, ParameterDirection.Input) //}; //dtResult = oracleConn.GetSqlResultToDt(sqlString, paras); //if (dtResult != null && dtResult.Rows.Count > 0) //{ // sre.Status = Constant.ServiceResultStatus.Other; // sre.OtherStatus = -11; // sre.Message = "条码【" + barcode + "】,已经是产成品。"; // return sre; //} #endregion #region 查询产品相关信息 sqlString = "SELECT GD.BARCODE,\n" + //" (GOODS.MATERIALCODE || L.TAGCODE || GD.ONLYCODE) AS FINISHEDBARCODE,\n" + " nvl(GD.outlabelcode, (GOODS.MATERIALCODE || (select a.workcode from tp_mst_account a where a.accountid = gd.accountid) || L.TAGCODE || GD.ONLYCODE)) AS FINISHEDBARCODE,\n" + " L.LOGONAME || '[' || L.LOGOCODE || ']' AS LOGONAME,\n" + " GD.GOODSCODE,\n" + " GOODS.GOODSMODEL,\n" + //" GOODS.WATERLABELCODE\n" + " nvl((select gls.WATERLABELCODE\n" + " from TP_MST_GOODSLOGOSAP gls\n" + " where gls.goodsid = GD.goodsid\n" + " and gls.logoid = GD.LOGOID)\n" + " ,GOODS.WATERLABELCODE) WATERLABELCODE\n" + " FROM TP_PM_GROUTINGDAILYDETAIL GD\n" + " INNER JOIN TP_MST_LOGO L\n" + " ON GD.LOGOID = L.LOGOID\n" + " INNER JOIN TP_MST_GOODS GOODS\n" + " ON GD.GOODSID = GOODS.GOODSID\n" + " WHERE GD.VALUEFLAG = '1'\n" + " AND GD.BARCODE = :BARCODE"; paras = new OracleParameter[] { new OracleParameter(":BARCODE", OracleDbType.NVarchar2, gbarcode, ParameterDirection.Input) }; dtResult = oracleConn.GetSqlResultToDt(sqlString, paras); if (dtResult != null && dtResult.Rows.Count > 0) { sre.Status = Constant.ServiceResultStatus.Success; sre.Result = dtResult; } else { sre.Status = Constant.ServiceResultStatus.Other; sre.OtherStatus = -11; sre.Message = "条码【" + barcode + "】,不符合条件。"; } #endregion return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion } }