/******************************************************************************* * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential * 类的信息： * 1.程序名称：SystemModuleLogic.cs * 2.功能描述：产品档案数据查询处理 * 编辑履历： * 作者 日期 版本 修改内容 * 张国印 2014/09/12 1.00 新建 *******************************************************************************/ using System; using System.Collections.Generic; using System.Data; using System.Text; using Dongke.IBOSS.PRD.Basics.DataAccess; using Dongke.IBOSS.PRD.Service.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels; using Oracle.ManagedDataAccess.Client; namespace Dongke.IBOSS.PRD.Service.SystemModuleLogic { /// /// 产品档案数据查询处理 /// public partial class SystemModuleLogic { #region 产品档案 /// /// 查询产品信息 /// /// 用户基本信息 /// 产品信息 /// DataSet /// /// 陈冰 2014.09.01 新建 /// public static DataSet SerachGoods(SUserInfo sUserInfo, GoodsEntity goodsEntity) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); if (!string.IsNullOrWhiteSpace(goodsEntity.GoodsCodeOnly)) { string sql = "select goodsid from tp_mst_goods g where g.goodscode = :goodscode"; OracleParameter[] paras1 = new OracleParameter[]{ new OracleParameter(":goodscode",OracleDbType.Varchar2, goodsEntity.GoodsCodeOnly,ParameterDirection.Input), }; return con.GetSqlResultToDs(sql, paras1); } 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(); } } } /// /// 新建产品档案 /// /// 用户基本信息 /// 产品实体 /// 产品图片集合 /// 缺陷位置ID集合 /// int受影响行数 /// /// 庄天威 2014.09.04 新建 /// public static int AddGoods(SUserInfo sUserInfo, GoodsEntity goodsEntity , List imgList, List attList) { int returnRows; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); if (goodsEntity.MudQuantity == null) { goodsEntity.MudQuantity = 0; } if (goodsEntity.GlazeQuantity == null) { goodsEntity.GlazeQuantity = 0; } if (goodsEntity.Remarks == null) { goodsEntity.Remarks = ""; } if (goodsEntity.GoodsSpecification == null) { goodsEntity.GoodsSpecification = ""; } if (goodsEntity.GoodsModel == null) { goodsEntity.GoodsModel = ""; } try { oracleTrConn.Connect(); // 物料编码重复验证 add by chenxy 2017-07-11 begin // 有不同编码，同物料编码的情况 delete by chenxy 2017-11-10 //string sqlString = "SELECT g.goodscode \n" + //" FROM tp_mst_goods g\n" + //" WHERE g.materialcode = :materialcode"; //OracleParameter[] checkParas = new OracleParameter[]{ // new OracleParameter(":materialcode",goodsEntity.MaterialCode), //}; //string goodscode = oracleTrConn.GetSqlResultToStr(sqlString, checkParas); //if (!string.IsNullOrWhiteSpace(goodscode)) //{ // return -10; //} // 物料编码重复验证 add by chenxy 2017-07-11 end StringBuilder sbSql = new StringBuilder(); sbSql.Append("select SEQ_MST_Goods_GoodsID.nextval from dual"); int id = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append(" INSERT INTO TP_MST_GOODS"); sbSql.Append("(GoodsID,GoodsCode,GoodsName,GoodsSpecification,GoodsModel,GoodsTypeID,"); sbSql.Append("GlazeTypeID,MudWeight,GlazeWeight,LusterwareWeight,ProductionCycle,CeaseFlag,Goods_Line_Type,"); sbSql.Append("StartingDate,AutoLossCycle,DeliverLimitCycle,PlateLimitNum,UnitPrice,ReservedDays,"); sbSql.Append("PackageNum,OutletDistance,MaterialCode,MaterialRemark,printcopies,"); sbSql.Append("MouldWeight,MouldCost,ScrapSumFlag,SEATCOVERCODE,"); sbSql.Append("WaterLabelCode,StandardGroutingNum,MouldMaterialCode,MouldOutputCount,logoid,"); sbSql.Append("Remarks,AccountID,ValueFlag,CreateUserID,UpdateUserID)"); sbSql.Append("VALUES( :GoodsId, :GoodsCode, :GoodsName, :GoodsSpecification, :GoodsModel, :GoodsTypeID,"); sbSql.Append(" :GlazeTypeID, :MudWeight, :GlazeWeight,:LusterwareWeight, :ProductionCycle, :CeaseFlag,:Goods_Line_Type,"); sbSql.Append(" :StartingDate, :AutoLossCycle, :DeliverLimitCycle,:PlateLimitNum,:UnitPrice,:ReservedDays,"); sbSql.Append(" :PackageNum, :OutletDistance, :MaterialCode,:MaterialRemark,:printcopies,"); sbSql.Append(" :MouldWeight, :MouldCost,:ScrapSumFlag,:SEATCOVERCODE,"); sbSql.Append(" :WaterLabelCode, :StandardGroutingNum, :MouldMaterialCode, :MouldOutputCount,:logoid,"); sbSql.Append(" :Remarks, :AccountID, :ValueFlag, :CreateUserID, :UpdateUserID)"); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":GoodsId",id), new OracleParameter(":GoodsCode",goodsEntity.GoodsCode), new OracleParameter(":GoodsName",goodsEntity.GoodsName), new OracleParameter(":GoodsSpecification",goodsEntity.GoodsSpecification), new OracleParameter(":GoodsModel",goodsEntity.GoodsModel), new OracleParameter(":GoodsTypeID",goodsEntity.GoodsTypeID), new OracleParameter(":GlazeTypeID",goodsEntity.GlazeTypeID), new OracleParameter(":MudWeight",goodsEntity.MudQuantity), new OracleParameter(":GlazeWeight",goodsEntity.GlazeQuantity), new OracleParameter(":LusterwareWeight",goodsEntity.LusterwareWeight), new OracleParameter(":ProductionCycle",goodsEntity.ProductionCycle), new OracleParameter(":CeaseFlag",goodsEntity.CeaseFlag), new OracleParameter(":Goods_Line_Type",goodsEntity.GoodsLineType), new OracleParameter(":StartingDate",goodsEntity.StartingDate), new OracleParameter(":AutoLossCycle",goodsEntity.AutoLossCycle), new OracleParameter(":DeliverLimitCycle",goodsEntity.DeliverLimitCycle), new OracleParameter(":PlateLimitNum",goodsEntity.PlateLimitNum), new OracleParameter(":ReservedDays",goodsEntity.ReservedDays), new OracleParameter(":UnitPrice",goodsEntity.UnitPrice), new OracleParameter(":PackageNum",goodsEntity.PackageNum), new OracleParameter(":OutletDistance",goodsEntity.OutletDistance), new OracleParameter(":MaterialCode",goodsEntity.MaterialCode), new OracleParameter(":MouldWeight",goodsEntity.MouldWeight), new OracleParameter(":MouldCost",goodsEntity.MouldCost), new OracleParameter(":ScrapSumFlag",goodsEntity.ScrapSumFlag), new OracleParameter(":MaterialRemark",goodsEntity.MaterialRemark), new OracleParameter(":printcopies",goodsEntity.PrintCopies), new OracleParameter(":SEATCOVERCODE", goodsEntity.SeatCoverCode), new OracleParameter(":WaterLabelCode", goodsEntity.WaterLabelCode), new OracleParameter(":StandardGroutingNum", goodsEntity.StandardGroutingNum), new OracleParameter(":MouldMaterialCode", goodsEntity.MouldMaterialCode), new OracleParameter(":MouldOutputCount", goodsEntity.MouldOutputCount), new OracleParameter(":logoid", goodsEntity.LogoID), new OracleParameter(":Remarks",goodsEntity.Remarks), new OracleParameter(":AccountID",sUserInfo.AccountID), new OracleParameter(":ValueFlag",goodsEntity.ValueFlag), //new OracleParameter(":CreateTime",OracleDbType.Date,DateTime.Now,ParameterDirection.Input), new OracleParameter(":CreateUserID",sUserInfo.UserID), //new OracleParameter(":UpdateTime",OracleDbType.Date,DateTime.Now,ParameterDirection.Input), new OracleParameter(":UpdateUserID",sUserInfo.UserID), //new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,DateTime.Now,ParameterDirection.Input) }; returnRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), paras); //SAP物料信息 string sql = "delete from TP_MST_GoodsLogoSAP where goodsid = " + id; returnRows += oracleTrConn.ExecuteNonQuery(sql); sql = "insert into TP_MST_GOODSLOGOSAP\n" + " (GOODSID, LOGOID, GOODSCODE, MATERIALCODE, MATERIALREMARK, CREATEUSERID, WaterLabelCode)\n" + "values\n" + " (:GOODSID, :LOGOID, :GOODSCODE, :MATERIALCODE, :MATERIALREMARK, :CREATEUSERID, :WaterLabelCode)"; foreach (DataRow item in goodsEntity.SAPInfo.Rows) { string sapcode = item["MATERIALCODE"] + ""; string sapremark = item["MATERIALREMARK"] + ""; if (string.IsNullOrWhiteSpace(sapcode)) { continue; } if (string.IsNullOrEmpty(sapremark)) { sapremark = " "; } paras = new OracleParameter[]{ new OracleParameter(":GOODSID",id), new OracleParameter(":LOGOID",item["LOGOID"]), new OracleParameter(":GOODSCODE",goodsEntity.GoodsCode), new OracleParameter(":MATERIALCODE",sapcode), new OracleParameter(":MATERIALREMARK",sapremark), new OracleParameter(":CREATEUSERID",sUserInfo.UserID), new OracleParameter(":WaterLabelCode",item["WaterLabelCode"]), }; returnRows += oracleTrConn.ExecuteNonQuery(sql, paras); } //此处添加图片信息 foreach (GoodsImageEntity img in imgList) { int imgReturn = 0; sbSql.Clear(); sbSql.Append("select SEQ_MST_GoodsImage_ID.nextval from dual"); int imgId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_MST_GoodsImage"); sbSql.Append("(GoodsImageID,GoodsID,Thumbnail,Image,AccountID,ValueFlag,"); sbSql.Append("CreateUserID,UpdateUserID)"); sbSql.Append(" VALUES(:GoodsImageID,:GoodsID,:Thumbnail,:Image,:AccountID,:ValueFlag,"); sbSql.Append(":CreateUserID,:UpdateUserID)"); OracleParameter[] imgParas = new OracleParameter[] { new OracleParameter(":GoodsImageID",OracleDbType.Int32,imgId,ParameterDirection.Input), new OracleParameter(":GoodsID",OracleDbType.Int32,id,ParameterDirection.Input), new OracleParameter(":Thumbnail",OracleDbType.Blob,img.Thumbnail,ParameterDirection.Input), new OracleParameter(":Image",OracleDbType.Blob,img.Image,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32,img.ValueFlag,ParameterDirection.Input), //new OracleParameter(":CreateTime",OracleDbType.Date,DateTime.Now,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), //new OracleParameter(":UpdateTime",OracleDbType.Date,DateTime.Now,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), //new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,DateTime.Now,ParameterDirection.Input) }; imgReturn = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), imgParas); } //此处添加产品附件 //int AttReturn = AddAttachment(oracleTrConn, attList, id, sUserInfo); //此处添加缺陷位置关联 //int dpCount = SaveGoodsDefectPosition(oracleTrConn, dpList, id, sUserInfo); oracleTrConn.Commit(); oracleTrConn.Disconnect(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } return returnRows; } /// /// 修改产品档案 /// /// 用户基本信息 /// 产品实体 /// 产品图片集合 /// 缺陷位置ID集合 /// int受影响行数 /// /// 庄天威 2014.09.04 新建 /// public static int updateGoods(SUserInfo sUserInfo, GoodsEntity goodsEntity , List imgList, List attList) { int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); if (goodsEntity.MudQuantity == null) { goodsEntity.MudQuantity = 0; } if (goodsEntity.GlazeQuantity == null) { goodsEntity.GlazeQuantity = 0; } if (goodsEntity.Remarks == null) { goodsEntity.Remarks = ""; } if (goodsEntity.GoodsSpecification == null) { goodsEntity.GoodsSpecification = ""; } if (goodsEntity.GoodsModel == null) { goodsEntity.GoodsModel = ""; } try { oracleTrConn.Connect(); // 物料编码重复验证 add by chenxy 2017-07-11 begin // 有不同编码，同物料编码的情况 delete by chenxy 2017-11-10 //string sqlString = "SELECT g.goodscode \n" + //" FROM tp_mst_goods g\n" + //" WHERE g.materialcode = :materialcode and g.goodsid <> :goodsid"; //OracleParameter[] checkParas = new OracleParameter[]{ // new OracleParameter(":materialcode",goodsEntity.MaterialCode), // new OracleParameter(":goodsid",goodsEntity.GoodsID), //}; //string goodscode = oracleTrConn.GetSqlResultToStr(sqlString, checkParas); //if (!string.IsNullOrWhiteSpace(goodscode)) //{ // return -10; //} // 物料编码重复验证 add by chenxy 2017-07-11 end StringBuilder sbSql = new StringBuilder(); sbSql.Append("update TP_MST_GOODS"); //sbSql.Append(" set GoodsCode=:GoodsCode,GoodsName=:GoodsName,GoodsSpecification=:GoodsSpecification,"); sbSql.Append(" set GoodsName=:GoodsName,GoodsSpecification=:GoodsSpecification,"); sbSql.Append(" GoodsModel=:GoodsModel,GoodsTypeID=:GoodsTypeID,GlazeTypeID=:GlazeTypeID,MudWeight=:MudWeight,"); sbSql.Append(" GlazeWeight=:GlazeWeight,LusterwareWeight=:LusterwareWeight,ProductionCycle=:ProductionCycle,CeaseFlag=:CeaseFlag,Goods_Line_Type=:Goods_Line_Type,Remarks=:Remarks,"); sbSql.Append(" StartingDate=:StartingDate, AutoLossCycle = :AutoLossCycle, DeliverLimitCycle=:DeliverLimitCycle, PlateLimitNum=:PlateLimitNum,"); sbSql.Append(" UnitPrice=:UnitPrice,PackageNum=:PackageNum,OutletDistance=:OutletDistance,MaterialCode=:MaterialCode,MaterialRemark=:MaterialRemark,printcopies=:printcopies,"); sbSql.Append(" MouldWeight=:MouldWeight, MouldCost=:MouldCost,ScrapSumFlag=:ScrapSumFlag, ReservedDays=:ReservedDays,SEATCOVERCODE=:SEATCOVERCODE,"); sbSql.Append(" WaterLabelCode=:WaterLabelCode, StandardGroutingNum=:StandardGroutingNum,MouldMaterialCode=:MouldMaterialCode, MouldOutputCount=:MouldOutputCount,"); sbSql.Append(" AccountID=:AccountID,ValueFlag=:ValueFlag,UpdateUserID=:UpdateUserID,logoid=:logoid"); //sbSql.Append(" CreateTime=:CreateTime,CreateUserID=:CreateUserID "); sbSql.Append(" where GoodsID=:GoodsID and OPTimeStamp=:OPTimeStamp"); OracleParameter[] paras = new OracleParameter[]{ // new OracleParameter(":GoodsCode",goodsEntity.GoodsCode), new OracleParameter(":GoodsName",goodsEntity.GoodsName), new OracleParameter(":GoodsSpecification",goodsEntity.GoodsSpecification), new OracleParameter(":GoodsModel",goodsEntity.GoodsModel), new OracleParameter(":GoodsTypeID",goodsEntity.GoodsTypeID), new OracleParameter(":GlazeTypeID",goodsEntity.GlazeTypeID), new OracleParameter(":MudWeight",goodsEntity.MudQuantity), new OracleParameter(":GlazeWeight",goodsEntity.GlazeQuantity), new OracleParameter(":LusterwareWeight",goodsEntity.LusterwareWeight), new OracleParameter(":ProductionCycle",goodsEntity.ProductionCycle), new OracleParameter(":CeaseFlag",goodsEntity.CeaseFlag), new OracleParameter(":Goods_Line_Type",goodsEntity.GoodsLineType), new OracleParameter(":StartingDate",goodsEntity.StartingDate), new OracleParameter(":AutoLossCycle",goodsEntity.AutoLossCycle), new OracleParameter(":DeliverLimitCycle",goodsEntity.DeliverLimitCycle), new OracleParameter(":PlateLimitNum",goodsEntity.PlateLimitNum), new OracleParameter(":ReservedDays",goodsEntity.ReservedDays), new OracleParameter(":UnitPrice",goodsEntity.UnitPrice), new OracleParameter(":PackageNum",goodsEntity.PackageNum), new OracleParameter(":OutletDistance",goodsEntity.OutletDistance), new OracleParameter(":MaterialCode",goodsEntity.MaterialCode), new OracleParameter(":MouldWeight",goodsEntity.MouldWeight), new OracleParameter(":MouldCost",goodsEntity.MouldCost), new OracleParameter(":ScrapSumFlag",goodsEntity.ScrapSumFlag), new OracleParameter(":MaterialRemark",goodsEntity.MaterialRemark), new OracleParameter(":printcopies",goodsEntity.PrintCopies), new OracleParameter(":Remarks",goodsEntity.Remarks), new OracleParameter(":SEATCOVERCODE",goodsEntity.SeatCoverCode), new OracleParameter(":WaterLabelCode",goodsEntity.WaterLabelCode), new OracleParameter(":StandardGroutingNum",goodsEntity.StandardGroutingNum), new OracleParameter(":MouldMaterialCode",goodsEntity.MouldMaterialCode), new OracleParameter(":MouldOutputCount",goodsEntity.MouldOutputCount), new OracleParameter(":logoid", goodsEntity.LogoID), new OracleParameter(":AccountID",goodsEntity.AccountID), new OracleParameter(":ValueFlag",goodsEntity.ValueFlag), //new OracleParameter(":UpdateTime",OracleDbType.Date,DateTime.Now,ParameterDirection.Input), new OracleParameter(":UpdateUserID",sUserInfo.UserID), //new OracleParameter(":CreateTime",OracleDbType.Date,goodsEntity.CreateTime,ParameterDirection.Input), //new OracleParameter(":CreateUserID",goodsEntity.CreateUserID), new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,goodsEntity.OPTimeStamp,ParameterDirection.Input), new OracleParameter(":GoodsId",goodsEntity.GoodsID) }; returnRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), paras); if (returnRows == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); return -500; } //SAP物料信息 string sql = "delete from TP_MST_GoodsLogoSAP where goodsid = " + goodsEntity.GoodsID; returnRows += oracleTrConn.ExecuteNonQuery(sql); sql = "insert into TP_MST_GOODSLOGOSAP\n" + " (GOODSID, LOGOID, GOODSCODE, MATERIALCODE, MATERIALREMARK, CREATEUSERID,WaterLabelCode)\n" + "values\n" + " (:GOODSID, :LOGOID, :GOODSCODE, :MATERIALCODE, :MATERIALREMARK, :CREATEUSERID,:WaterLabelCode)"; foreach (DataRow item in goodsEntity.SAPInfo.Rows) { string sapcode = item["MATERIALCODE"] + ""; string sapremark = item["MATERIALREMARK"] + ""; if (string.IsNullOrWhiteSpace(sapcode)) { continue; } if (string.IsNullOrEmpty(sapremark)) { sapremark = " "; } paras = new OracleParameter[]{ new OracleParameter(":GOODSID",goodsEntity.GoodsID), new OracleParameter(":LOGOID",item["LOGOID"]), new OracleParameter(":GOODSCODE",goodsEntity.GoodsCode), new OracleParameter(":MATERIALCODE",sapcode), new OracleParameter(":MATERIALREMARK",sapremark), new OracleParameter(":CREATEUSERID",sUserInfo.UserID), new OracleParameter(":WaterLabelCode",item["WaterLabelCode"]), }; returnRows += oracleTrConn.ExecuteNonQuery(sql, paras); } foreach (GoodsImageEntity imgEntity in imgList) { if (imgEntity.GoodsImageID == 0) { int imgReturn = 0; sbSql.Clear(); sbSql.Append("select SEQ_MST_GoodsImage_ID.nextval from dual"); int imgId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_MST_GoodsImage"); sbSql.Append("(GoodsImageID,GoodsID,Thumbnail,Image,AccountID,ValueFlag,"); sbSql.Append("CreateUserID,UpdateUserID)"); sbSql.Append(" VALUES(:GoodsImageID,:GoodsID,:Thumbnail,:Image,:AccountID,:ValueFlag,"); sbSql.Append(":CreateUserID,:UpdateUserID)"); OracleParameter[] imgParas = new OracleParameter[] { new OracleParameter(":GoodsImageID",OracleDbType.Int32,imgId,ParameterDirection.Input), new OracleParameter(":GoodsID",OracleDbType.Int32,goodsEntity.GoodsID,ParameterDirection.Input), new OracleParameter(":Thumbnail",OracleDbType.Blob,imgEntity.Thumbnail,ParameterDirection.Input), new OracleParameter(":Image",OracleDbType.Blob,imgEntity.Image,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32,imgEntity.ValueFlag,ParameterDirection.Input), //new OracleParameter(":CreateTime",OracleDbType.Date,DateTime.Now,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), //new OracleParameter(":UpdateTime",OracleDbType.Date,DateTime.Now,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), //new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,DateTime.Now,ParameterDirection.Input) }; imgReturn = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), imgParas); } else { int imgReturn = 0; sbSql.Clear(); sbSql.Append(" update TP_MST_GoodsImage"); sbSql.Append(" set GoodsID=:GoodsID,AccountID=:AccountID,ValueFlag=:ValueFlag,"); sbSql.Append(" UpdateUserID=:UpdateUserID"); //sbSql.Append(" where GoodsImageID=:GoodsImageID And OPTimeStamp=to_date(:OPTimeStamp,'yyyy-mm-dd HH24:mi:ss')"); sbSql.Append(" where GoodsImageID=:GoodsImageID"); OracleParameter[] imgParas = new OracleParameter[] { new OracleParameter(":GoodsID",OracleDbType.Int32, goodsEntity.GoodsID,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, imgEntity.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32, imgEntity.ValueFlag,ParameterDirection.Input), //new OracleParameter(":UpdateTime",OracleDbType.NVarchar2, // DateTime.Now.ToString(),ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":GoodsImageID",OracleDbType.Int32, imgEntity.GoodsImageID,ParameterDirection.Input) //,new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp, imgEntity.OPTimeStamp,ParameterDirection.Input) }; imgReturn = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), imgParas); } } //修改产品附件 //int AttReturn = UpdateAttachment(oracleTrConn, attList, Convert.ToInt32(goodsEntity.GoodsID), sUserInfo); oracleTrConn.Commit(); oracleTrConn.Disconnect(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } return returnRows; } /// /// 查询产品物料信息 /// /// /// /// public static ServiceResultEntity GetGoodsSAP(SUserInfo sUserInfo, int goodsID) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); string sqlString = "\n" + "select '产品档案' logoname, g.materialcode, g.materialremark,g.WaterLabelCode, '1' valueflag, -1 displayno\n" + " from tp_mst_goods g\n" + " where g.goodsid = :goodsid\n" + "union all\n" + "select to_char(l.logoname)\n" + " ,gls.materialcode\n" + " ,gls.materialremark\n" + " ,gls.WaterLabelCode\n" + " ,l.valueflag\n" + " ,l.displayno\n" + " from tp_mst_goodslogosap gls\n" + " inner join tp_mst_logo l\n" + " on l.logoid = gls.logoid\n" + " where gls.goodsid = :goodsid\n" + " order by valueflag desc, displayno"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":goodsid",goodsID), }; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(sqlString, paras); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 查询产品物料信息(编辑用) /// /// /// /// public static ServiceResultEntity GetGoodsSAPByEdit(SUserInfo sUserInfo, int goodsID) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); string sqlString = "select l.logoid\n" + " ,l.logoname\n" + " ,gls.materialcode\n" + " ,gls.materialremark\n" + " ,gls.WaterLabelCode\n" + " ,gls.goodsid\n" + " ,l.valueflag\n" + " from tp_mst_logo l\n" + " left join tp_mst_goodslogosap gls\n" + " on gls.goodsid = :goodsid\n" + " and l.logoid = gls.logoid\n" + " where (l.valueflag = '1' or gls.goodsid is not null)\n" + " order by gls.goodsid, l.valueflag desc, l.displayno"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":goodsid",goodsID), }; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(sqlString, paras); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } #endregion #region 获得生产工号集合 /// /// 获得生产工号集合 /// /// 用户基本信息 /// DataSet /// /// 陈冰 2014.09.03 新建 /// public static DataSet GetWorker(SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("rs_worker",OracleDbType.RefCursor,ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_MST_GetWorker", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 获得工种集合 /// /// 获得工种集合 /// /// 用户基本信息 /// DataSet /// /// 陈冰 2014.09.03 新建 /// public static DataSet GetJobs(SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("rs_jobs",OracleDbType.RefCursor,ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_MST_GetJobs", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 获得缺陷集合 /// /// 获得缺陷集合 /// /// 用户基本信息 /// DataSet /// /// 陈冰 2014.09.03 新建 /// public static DataSet GetDefect(SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("rs_defect",OracleDbType.RefCursor,ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_MST_GetDefect", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 产品图片 /// /// 根据产品ID获取产品图片 /// /// 用户基本信息 /// 产品ID /// DataSet /// /// 庄天威 2014.09.04 新建 /// public static DataSet GetImageByGoodsId(SUserInfo sUserInfo, int goodsId) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("in_goodsID",OracleDbType.Int32,goodsId,ParameterDirection.Input), new OracleParameter("rs_result",OracleDbType.RefCursor,ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_MST_GetImageByGoodsId", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 附件 /// /// 新建附件信息 /// /// 附件实体集合 /// 产品ID /// 用户基本信息 /// int影响结果行数 public static int AddAttachment(List AttList, int mainId, SUserInfo userInfo) { IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); int AttReturn = 0; int GAReturn = 0; StringBuilder sbSql = new StringBuilder(); foreach (GoodsAttachmentEntity attFor in AttList) { sbSql.Clear(); sbSql.Append("select SEQ_MST_Attachment_ID.nextval from dual"); int id = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append(@"Insert into TP_MST_Attachment(AttachmentID,FileName,FilePath,AccountID,ValueFlag, CreateUserID,UpdateUserID) Values(:AttachmentID,:FileName,:FilePath,:AccountID,:ValueFlag, :CreateUserID,:UpdateUserID)"); OracleParameter[] attParas = new OracleParameter[] { new OracleParameter(":AttachmentID",OracleDbType.Int32, id,ParameterDirection.Input), new OracleParameter(":FileName",OracleDbType.NVarchar2, attFor.FileName,ParameterDirection.Input), new OracleParameter(":FilePath",OracleDbType.NVarchar2, attFor.FilePath,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32, 1,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; AttReturn += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), attParas); sbSql.Clear(); sbSql.Append(@"Insert into TP_MST_GoodsAttachment(GoodsID,AttachmentID,CreateUserID,UpdateUserID) Values(:GoodsID,:AttachmentID,:CreateUserID,:UpdateUserID)"); OracleParameter[] gaParas = new OracleParameter[] { new OracleParameter(":GoodsID",OracleDbType.Int32, mainId,ParameterDirection.Input), new OracleParameter(":AttachmentID",OracleDbType.Int32, id,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; GAReturn += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), gaParas); sbSql.Clear(); } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return AttReturn; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } } /// /// 修改附件信息 /// /// 附件实体集合 /// 产品ID /// 用户基本信息 /// int受影响行数 public static int UpdateAttachment(List AttList, int mainId, SUserInfo userInfo) { IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); int AttReturn = 0; int GAReturn = 0; StringBuilder sbSql = new StringBuilder(); foreach (GoodsAttachmentEntity attFor in AttList) { if (attFor.IsUpdateAdd == 0) //不是新建的 { sbSql.Clear(); sbSql.Append(@"Update TP_MST_Attachment set ValueFlag = :ValueFlag,UpdateUserID=:UpdateUserID Where AttachmentID = :AttachmentID"); OracleParameter[] attParas = new OracleParameter[] { new OracleParameter(":ValueFlag",OracleDbType.Int32, attFor.ValueFlag,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":AttachmentID",OracleDbType.Int32, attFor.AttachmentID,ParameterDirection.Input) }; AttReturn += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), attParas); sbSql.Clear(); sbSql.Append(@"Update TP_MST_GoodsAttachment Set ValueFlag = :ValueFlag,UpdateUserID=:UpdateUserID Where AttachmentID = :AttachmentID"); GAReturn += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), attParas); sbSql.Clear(); } else //新建的 { sbSql.Clear(); sbSql.Append("select SEQ_MST_Attachment_ID.nextval from dual"); int id = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append(@"Insert into TP_MST_Attachment(AttachmentID,FileName,FilePath,AccountID,ValueFlag, CreateUserID,UpdateUserID) Values(:AttachmentID,:FileName,:FilePath,:AccountID,:ValueFlag, :CreateUserID,:UpdateUserID)"); OracleParameter[] attParas = new OracleParameter[] { new OracleParameter(":AttachmentID",OracleDbType.Int32, id,ParameterDirection.Input), new OracleParameter(":FileName",OracleDbType.NVarchar2, attFor.FileName,ParameterDirection.Input), new OracleParameter(":FilePath",OracleDbType.NVarchar2, attFor.FilePath,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32, 1,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; AttReturn += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), attParas); sbSql.Clear(); sbSql.Append(@"Insert into TP_MST_GoodsAttachment(GoodsID,AttachmentID,CreateUserID,UpdateUserID) Values(:GoodsID,:AttachmentID,:CreateUserID,:UpdateUserID)"); OracleParameter[] gaParas = new OracleParameter[] { new OracleParameter(":GoodsID",OracleDbType.Int32, mainId,ParameterDirection.Input), new OracleParameter(":AttachmentID",OracleDbType.Int32, id,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; GAReturn += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), gaParas); sbSql.Clear(); } } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return AttReturn; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } } /// /// 根据产品ID获取附件 /// /// 产品ID /// DataSet public static DataSet GetAttachmentByGoodsId(int goodsId) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); String strSql = @"Select goodsAtt.GoodsID,att.* from TP_MST_GoodsAttachment goodsAtt Inner join TP_MST_Attachment att On goodsAtt.AttachmentID = att.AttachmentID Where goodsAtt.ValueFlag = 1 and goodsAtt.GoodsID = " + goodsId; DataSet ds = con.GetSqlResultToDs(strSql, null); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 缺陷位置 /// /// 获取缺陷位置 /// /// 缺陷位置实体 /// 用户基本信息 /// DataSet public static DataSet GetDefectPosition(DefectPositionEntity dpEntity, SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_DefectPositionID",OracleDbType.Int32, dpEntity.DefectPositionID,ParameterDirection.Input), new OracleParameter("in_DefectPositionCode",OracleDbType.NVarchar2, dpEntity.DefectPositionCode,ParameterDirection.Input), new OracleParameter("in_DefectPositionName",OracleDbType.NVarchar2, dpEntity.DefectPositionName,ParameterDirection.Input), new OracleParameter("in_AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter("out_rs_defect",OracleDbType.RefCursor,ParameterDirection.Output), }; DataSet dsDefectPosition = con.ExecStoredProcedure("PRO_MST_GetDefectPosition", paras); return dsDefectPosition; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据产品获得产品缺陷位置 /// /// 产品ID /// DataSet public static DataSet getGoodsDefectPositionByGoodsId(int GoodsId) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sql = @"select gdp.*,dp.DefectPositionCode,dp.DefectPositionName,dp.Remarks from TP_MST_GoodsDefectPosition gdp inner join TP_MST_DefectPosition dp on gdp.DefectPositionID=dp.DefectPositionID where gdp.GoodsID=" + GoodsId; DataSet dsDefectPosition = con.GetSqlResultToDs(sql, null); return dsDefectPosition; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 保存产品缺陷位置关联(依附在添加产品中) /// /// 缺陷位置集 /// 产品ID /// 用户基本信息 /// int public static int SaveGoodsDefectPosition(IDBTransaction oracleTrConn, List dpList, int GoodsId, SUserInfo userInfo) { try { int ReturnCount = 0; string sql = "Delete from TP_MST_GoodsDefectPosition where GoodsId=" + GoodsId; foreach (int dpFor in dpList) { string sqlAdd = @"Insert into TP_MST_GoodsDefectPosition(GoodsID,DefectPositionID, CreateTime,CreateUserID) Values(:GoodsID,:DefectPositionID,sysdate,:CreateUserID)"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":GoodsID",OracleDbType.Int32, GoodsId,ParameterDirection.Input), new OracleParameter(":DefectPositionID",OracleDbType.Int32, dpFor,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), }; ReturnCount += oracleTrConn.ExecuteNonQuery(sqlAdd, paras); } return ReturnCount; } catch (Exception ex) { throw ex; } } #endregion #region 成型线类型 /// /// 获取成型线类型 /// /// 用户基本信息 /// 数据集 /// /// 庄天威 2014.09.04 新建 /// public static DataSet GetGMouldType(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_accountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter("in_valueFlag",OracleDbType.Int32, 1,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor,ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_MST_GetMouldType", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 产品分级数据源 /// /// 产品分级数据源 /// /// 1适用半成品2检验 2适用成品检验 3入窑前检验 /// 用户基本信息 /// DataSet public static DataSet GetGoodsLevel(int type, SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = ""; if (type == 1 || type == 3) { sqlString = "select GoodsLevelID as DefectFlagID,GoodsLevelName as DefectFlagName,GoodsLevelTypeID from TP_MST_GoodsLevel where IsSemiFinishedEx=1 and AccountID=:AccountID and ValueFlag=1 order by SFEDisplayNo"; } else if (type == 2) { sqlString = "select GoodsLevelID as DefectFlagID,GoodsLevelName as DefectFlagName,GoodsLevelTypeID from TP_MST_GoodsLevel where IsFinishedEx=1 and AccountID=:AccountID and ValueFlag=1 order by FEDisplayNo"; } OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":AccountID",userInfo.AccountID), }; DataSet dsGoodsLevel = con.GetSqlResultToDs(sqlString, paras); return dsGoodsLevel; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 审核状态 /// /// 获取全部审核状态 /// /// DataSet审核状态数据源 public static DataSet GetAuditStatus() { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); String strSql = "Select * from TP_SYS_AuditStatus Order by DisplayNo"; DataSet ds = con.GetSqlResultToDs(strSql, null); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion /// /// 工序集合 /// /// 工序实体 /// 用户<基本信息/param> /// DataSet /// /// 王鑫 2014.11.29 新建 /// public static DataSet GetProdureList(SearchProductionLineEntity productionLineEntity, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_procedureCode",OracleDbType.Varchar2,productionLineEntity.ProductionLineCode,ParameterDirection.Input), new OracleParameter("in_procedureName",OracleDbType.Varchar2,productionLineEntity.ProductionLineName,ParameterDirection.Input), new OracleParameter("in_procedureIDS",OracleDbType.Varchar2,productionLineEntity.ProcuteLineIDS,ParameterDirection.Input), new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet dsSearchReport = con.ExecStoredProcedure("PRO_PC_GetProcedureList", paras); return dsSearchReport; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取全部工种计件方式 /// /// DataSet工种计件方式集合 public static DataSet GetJobsPriceType() { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); String strSql = "Select * from TP_SYS_JobsPriceType Order by DisplayNo"; DataSet dsSearchReport = con.GetSqlResultToDs(strSql, null); return dsSearchReport; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 查询金蝶代码数据 /// /// /// /// public static ServiceResultEntity GetKingdeeCode(SUserInfo sUserInfo, ClientRequestEntity cre) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); OracleParameter[] paras = new OracleParameter[] { }; string sqlString = @" SELECT TP_MST_GOODSKINGDEECODE.GUID, TP_MST_GOODSKINGDEECODE.GOODSCODE, gd.goodsname, TP_MST_GOODSKINGDEECODE.GLAZETYPE, TP_MST_GOODSKINGDEECODE.LOGONAME, TP_MST_GOODSKINGDEECODE.KINGDEECODE, TP_MST_GOODSKINGDEECODE.CREATETIME, TP_MST_USER.USERNAME AS CREATEUSER, TP_MST_GOODSKINGDEECODE.UPDATETIME AS UPDATETIME, USERS.USERNAME AS UPDATEUSER FROM TP_MST_GOODSKINGDEECODE LEFT JOIN TP_MST_GOODS gd on gd.GOODSCODE=TP_MST_GOODSKINGDEECODE.GOODSCODE LEFT JOIN TP_MST_USER ON TP_MST_GOODSKINGDEECODE.CREATEUSERID=TP_MST_USER.USERID LEFT JOIN TP_MST_USER USERS ON TP_MST_GOODSKINGDEECODE.UPDATEUSERID=USERS.USERID WHERE 1=1 AND TP_MST_GOODSKINGDEECODE.valueflag=1 "; if (!string.IsNullOrEmpty(cre.Properties["GoodsCode"].ToString())) { sqlString += " AND TP_MST_GOODSKINGDEECODE.GOODSCODE like :GOODSCODE"; } if (!string.IsNullOrEmpty(cre.Properties["GlazeType"].ToString())) { sqlString += " AND TP_MST_GOODSKINGDEECODE.GLAZETYPE = :GLAZETYPE"; } if (!string.IsNullOrEmpty(cre.Properties["LogoName"].ToString())) { sqlString += " AND TP_MST_GOODSKINGDEECODE.LOGONAME = :LOGONAME"; } if (!string.IsNullOrEmpty(cre.Properties["KingdeeCode"].ToString())) { sqlString += " AND TP_MST_GOODSKINGDEECODE.KINGDEECODE like :KINGDEECODE"; } if (!string.IsNullOrEmpty(cre.Properties["GoodsName"].ToString())) { sqlString += " AND gd.goodsname like :GoodsName"; } paras = new OracleParameter[]{ new OracleParameter(":GOODSCODE",'%'+cre.Properties["GoodsCode"].ToString()+'%'), new OracleParameter(":GoodsName",'%'+cre.Properties["GoodsName"].ToString()+'%'), new OracleParameter(":GLAZETYPE",cre.Properties["GlazeType"].ToString()), new OracleParameter(":LOGONAME",cre.Properties["LogoName"].ToString()), new OracleParameter(":KINGDEECODE",'%'+cre.Properties["KingdeeCode"].ToString()+'%'), }; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(sqlString, paras); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 查询商标名称 /// /// /// /// public static ServiceResultEntity GetLogoName(SUserInfo sUserInfo, ClientRequestEntity cre) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); string sqlString = @" SELECT LogoID, LogoName AS LogoNameCode FROM tp_mst_logo WHERE AccountID = 1 AND valueflag = '1' ORDER BY DECODE(LogoName, '无标', 1) , LogoName"; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(sqlString, null); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// ///保存金蝶代码信息 /// public static ServiceResultEntity AddKingdeeCode(SUserInfo sUserInfo, ClientRequestEntity cre) { IDBTransaction conn = null; try { conn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); DataTable dt = new DataTable(); string sqlString = string.Empty; OracleParameter[] paras = new OracleParameter[] { }; //如果版面信息是新建的情况下，是否存在相同版面编码 if (cre.Properties["FromStatus"].ToString() == "Add") { sqlString = @"SELECT 1 FROM TP_MST_GOODSKINGDEECODE WHERE KINGDEECODE = :KINGDEECODE AND GOODSCODE =:GOODSCODE AND GLAZETYPE =:GLAZETYPE AND LOGONAME =:LOGONAME AND valueflag=1"; paras = new OracleParameter[] { new OracleParameter(":KINGDEECODE", cre.Properties["KINGDEECODE"].ToString()), new OracleParameter(":GOODSCODE", cre.Properties["GOODSCODE"].ToString()), new OracleParameter(":GLAZETYPE", cre.Properties["GLAZETYPE"].ToString()) , new OracleParameter(":LOGONAME", cre.Properties["LOGONAME"].ToString()) , }; dt = new DataTable(); dt = conn.GetSqlResultToDt(sqlString, paras); if (dt != null && dt.Rows.Count > 0) { //存在相同金蝶代码 sre.OtherStatus = -1001; return sre; } } #region 新增 if (cre.Properties["FromStatus"].ToString() == "Add") { //保存客户信息 sqlString = @"INSERT INTO TP_MST_GOODSKINGDEECODE( GOODSCODE, GLAZETYPE, LOGONAME, KINGDEECODE, CREATEUSERID, UPDATEUSERID, VALUEFLAG ) VALUES( :GOODSCODE, :GLAZETYPE, :LOGONAME, :KINGDEECODE, :CREATEUSERID,:UPDATEUSERID, '1' )"; paras = new OracleParameter[] { new OracleParameter(":GOODSCODE", cre.Properties["GOODSCODE"].ToString()), new OracleParameter(":GLAZETYPE", cre.Properties["GLAZETYPE"].ToString()) , new OracleParameter(":LOGONAME", cre.Properties["LOGONAME"].ToString()) , new OracleParameter(":KINGDEECODE", cre.Properties["KINGDEECODE"].ToString()) , new OracleParameter(":CREATEUSERID", sUserInfo.UserID) , new OracleParameter(":UPDATEUSERID", sUserInfo.UserID) , }; } #endregion #region 编辑 if (cre.Properties["FromStatus"].ToString() == "Edit") { sqlString = @"SELECT 1 FROM TP_MST_GOODSKINGDEECODE WHERE KINGDEECODE = :KINGDEECODE AND GOODSCODE =:GOODSCODE AND GLAZETYPE =:GLAZETYPE AND LOGONAME =:LOGONAME AND valueflag=1"; paras = new OracleParameter[] { new OracleParameter(":KINGDEECODE", cre.Properties["KINGDEECODE"].ToString()), new OracleParameter(":GOODSCODE", cre.Properties["GOODSCODE"].ToString()), new OracleParameter(":GLAZETYPE", cre.Properties["GLAZETYPE"].ToString()) , new OracleParameter(":LOGONAME", cre.Properties["LOGONAME"].ToString()) , }; dt = new DataTable(); dt = conn.GetSqlResultToDt(sqlString, paras); if (dt != null && dt.Rows.Count > 0) { //存在相同版面编码 sre.OtherStatus = -1001; return sre; } sqlString = @" UPDATE TP_MST_GOODSKINGDEECODE SET GOODSCODE=:GOODSCODE, GLAZETYPE = :GLAZETYPE, LOGONAME =:LOGONAME, KINGDEECODE = :KINGDEECODE, UPDATEUSERID =:UPDATEUSERID, UPDATETIME = SYSDATE WHERE GUID =:GUID "; paras = new OracleParameter[] { new OracleParameter(":GOODSCODE", cre.Properties["GOODSCODE"].ToString()) , new OracleParameter(":GLAZETYPE", cre.Properties["GLAZETYPE"].ToString()) , new OracleParameter(":LOGONAME", cre.Properties["LOGONAME"].ToString()) , new OracleParameter(":KINGDEECODEONE", cre.Properties["KINGDEECODEONE"].ToString()) , new OracleParameter(":KINGDEECODE", cre.Properties["KINGDEECODE"].ToString()) , new OracleParameter(":UPDATEUSERID", sUserInfo.UserID) , new OracleParameter(":GUID", cre.Properties["GUID"].ToString()) , }; } #endregion #region 删除 if (cre.Properties["FromStatus"].ToString() == "Delete") { sqlString = @"UPDATE TP_MST_GOODSKINGDEECODE SET VALUEFLAG=0 WHERE GUID = :GUID"; paras = new OracleParameter[] { new OracleParameter(":GUID", cre.Properties["GUID"].ToString()) , }; } #endregion int result = conn.ExecuteNonQuery(sqlString, paras); if (result <= 0) { //未改变任何数据 sre.OtherStatus = -1003; return sre; } else { //提交数据 sre.OtherStatus = 1; conn.Commit(); } return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Disconnect(); } } } /// /// 查询模具对应产品 /// /// /// /// public static ServiceResultEntity GetMouldGoodsCode(SUserInfo sUserInfo, ClientRequestEntity cre) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); OracleParameter[] paras = new OracleParameter[] { }; string sqlString = @" SELECT MOULD.GUID, MOULD.GOODSCODE, GD.GOODSNAME, MOULD.MOULDGOODSCODE, MOULD.MOULDNAME FROM TP_MST_MOULDGOODSCODE MOULD LEFT JOIN TP_MST_GOODS GD ON MOULD.GOODSCODE=GD.GOODSCODE AND GD.VALUEFLAG=1 WHERE MOULD.VALUEFLAG=1 "; if (!string.IsNullOrEmpty(cre.Properties["GoodsCode"].ToString())) { sqlString += "AND MOULD.GOODSCODE like :GOODSCODE"; } if (!string.IsNullOrEmpty(cre.Properties["MOULDGOODSCODE"].ToString())) { sqlString += " AND MOULD.MOULDGOODSCODE like :MOULDGOODSCODE"; } if (!string.IsNullOrEmpty(cre.Properties["MOULDNAME"].ToString())) { sqlString += " AND MOULD.MOULDNAME like :MOULDNAME"; } paras = new OracleParameter[]{ new OracleParameter(":GOODSCODE",'%'+cre.Properties["GoodsCode"].ToString()+'%'), new OracleParameter(":MOULDNAME",'%'+cre.Properties["MOULDNAME"].ToString()+'%'), new OracleParameter(":MOULDGOODSCODE",'%'+cre.Properties["MOULDGOODSCODE"].ToString()+'%'), }; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(sqlString, paras); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// ///保存金蝶代码信息 /// public static ServiceResultEntity AddMouldCode(SUserInfo sUserInfo, ClientRequestEntity cre) { IDBTransaction conn = null; try { conn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); DataTable dt = new DataTable(); string sqlString = string.Empty; OracleParameter[] paras = new OracleParameter[] { }; //如果版面信息是新建的情况下，是否存在相同版面编码 if (cre.Properties["FromStatus"].ToString() == "Add") { sqlString = @" SELECT 1 FROM TP_MST_MOULDGOODSCODE WHERE VALUEFLAG=1 AND GOODSCODE= :GOODSCODE AND MOULDGOODSCODE= :MOULDGOODSCODE AND MOULDNAME= :MOULDNAME"; paras = new OracleParameter[]{ new OracleParameter(":GOODSCODE",cre.Properties["GOODSCODE"].ToString()), new OracleParameter(":MOULDNAME",cre.Properties["MOULDNAME"].ToString()), new OracleParameter(":MOULDGOODSCODE",cre.Properties["MOULDGOODSCODE"].ToString()), }; dt = new DataTable(); dt = conn.GetSqlResultToDt(sqlString, paras); if (dt != null && dt.Rows.Count > 0) { //存在相同金蝶代码 sre.OtherStatus = -1001; return sre; } } #region 新增 if (cre.Properties["FromStatus"].ToString() == "Add") { //保存客户信息 sqlString = @"INSERT INTO TP_MST_MOULDGOODSCODE( GOODSCODE, MOULDGOODSCODE, MOULDNAME, VALUEFLAG ) VALUES( :GOODSCODE, :MOULDGOODSCODE, :MOULDNAME, '1' )"; paras = new OracleParameter[]{ new OracleParameter(":GOODSCODE",cre.Properties["GOODSCODE"].ToString()), new OracleParameter(":MOULDNAME",cre.Properties["MOULDNAME"].ToString()), new OracleParameter(":MOULDGOODSCODE",cre.Properties["MOULDGOODSCODE"].ToString()), }; } #endregion #region 编辑 if (cre.Properties["FromStatus"].ToString() == "Edit") { sqlString = @" SELECT 1 FROM TP_MST_MOULDGOODSCODE WHERE VALUEFLAG=1 AND GOODSCODE=:GOODSCODE AND MOULDGOODSCODE=:MOULDGOODSCODE AND MOULDNAME=:MOULDNAME"; paras = new OracleParameter[]{ new OracleParameter(":GOODSCODE",cre.Properties["GOODSCODE"].ToString()), new OracleParameter(":MOULDNAME",cre.Properties["MOULDNAME"].ToString()), new OracleParameter(":MOULDGOODSCODE",cre.Properties["MOULDGOODSCODE"].ToString()), }; dt = new DataTable(); dt = conn.GetSqlResultToDt(sqlString, paras); if (dt != null && dt.Rows.Count > 0) { //存在相同版面编码 sre.OtherStatus = -1001; return sre; } sqlString = @"UPDATE TP_MST_MOULDGOODSCODE SET GOODSCODE=:GOODSCODE, MOULDGOODSCODE =:MOULDGOODSCODE, MOULDNAME =:MOULDNAME WHERE GUID=:GUID"; paras = new OracleParameter[]{ new OracleParameter(":GOODSCODE",cre.Properties["GOODSCODE"].ToString()), new OracleParameter(":MOULDNAME",cre.Properties["MOULDNAME"].ToString()), new OracleParameter(":MOULDGOODSCODE",cre.Properties["MOULDGOODSCODE"].ToString()), new OracleParameter(":GUID",cre.Properties["GUID"].ToString()), }; } #endregion #region 删除 if (cre.Properties["FromStatus"].ToString() == "Delete") { sqlString = @"UPDATE TP_MST_MOULDGOODSCODE SET VALUEFLAG=0 WHERE GUID=:GUID "; paras = new OracleParameter[]{ new OracleParameter(":GUID",cre.Properties["GUID"].ToString()), }; } #endregion int result = conn.ExecuteNonQuery(sqlString, paras); if (result <= 0) { //未改变任何数据 sre.OtherStatus = -1003; return sre; } else { //提交数据 sre.OtherStatus = 1; conn.Commit(); } return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Disconnect(); } } } /// ///查询登录记录 /// public static ServiceResultEntity GetUserLoginLogdata(SUserInfo sUserInfo, ClientRequestEntity cre) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); OracleParameter[] paras = new OracleParameter[] { }; string sqlString = @"SELECT * from (SELECT 0 AS Sel, U.UserID, U.UserCode, U.limitMAC, U.UserName, POST.PostName, U.CanPCLogin, U.CanSmartLogin, U.IsWorker, U.IsGroutingWorker, U.IsPublicBody, U.CanLoginPRD, U.CanLoginMBC, U.ValueFlag, O.OrganizationName, O.OrganizationFullName, U.Remarks, TP_MST_USERLOGINLOG.MAC, COMPUTERNAME, ORGANIZATIONCODE, CASE MAC WHEN 'xx:xx:xx:xx:xx:xx' THEN 'web质量登记' ELSE CASE ACTIONTYPE WHEN '0' THEN 'pc登录' WHEN '1' THEN 'PC登出' WHEN '2' THEN '移动设备登录' WHEN '3' THEN '移动设备登出' WHEN '4' THEN 'PC登录MBC' ELSE 'PC登出MBC' END END as ACTIONTYPE, IP , IMEI, ACTIONTIME FROM TP_MST_USERLOGINLOG LEFT JOIN TP_MST_User U ON u.USERID = TP_MST_USERLOGINLOG.USERID LEFT JOIN TP_MST_Organization O ON U.Organizationid = O.Organizationid LEFT JOIN TP_MST_POST POST ON POST.POSTID = U.POST WHERE U.AccountID = 1) where 1=1 AND ACTIONTIME>=to_date(:opentime,'yyyy-mm-dd hh24:mi:ss')AND ACTIONTIME<=to_date(:closetime,'yyyy-mm-dd hh24:mi:ss')"; if (!string.IsNullOrEmpty(cre.Properties["UserCode"].ToString())) { sqlString += "AND UserCode like :UserCode"; } if (!string.IsNullOrEmpty(cre.Properties["UserName"].ToString())) { sqlString += " AND UserName like :UserName"; } if (!string.IsNullOrEmpty(cre.Properties["Organization"].ToString())) { sqlString += " AND ORGANIZATIONCODE = :Organization"; } if (!string.IsNullOrEmpty(cre.Properties["ActionType"].ToString())) { if (cre.Properties["ActionType"].ToString()== "6") { sqlString += " AND MAC = 'xx:xx:xx:xx:xx:xx'"; } else { sqlString += " AND ActionType like :ActionType"; } } sqlString += @" ORDER BY ACTIONTIME DESC"; paras = new OracleParameter[]{ new OracleParameter(":UserCode",'%'+cre.Properties["UserCode"].ToString()+'%'), new OracleParameter(":UserName",'%'+cre.Properties["UserName"].ToString()+'%'), new OracleParameter(":Organization",cre.Properties["Organization"].ToString()), new OracleParameter(":ActionType",'%'+cre.Properties["ActionType"].ToString()+'%'), new OracleParameter(":opentime",cre.Properties["opentime"].ToString()), new OracleParameter(":closetime",cre.Properties["closetime"].ToString()), }; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(sqlString, paras); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } } }