HEGII.ft.mes.wcf/DK.Service/SystemModuleLogic/SystemModuleLogicPartial.cs

/*******************************************************************************
 * 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
{
	/// <summary>
	/// 产品档案数据查询处理
	/// </summary>
	public partial class SystemModuleLogic
	{
		#region 产品档案

		/// <summary>
		/// 查询产品信息
		/// </summary>
		/// <param name="sUserInfo">用户基本信息</param>
		/// <param name="goodsEntity">产品信息</param>
		/// <returns>DataSet</returns>
		/// <remarks>
		/// 陈冰              2014.09.01              新建
		/// </remarks>
		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();
				}
			}
		}

		/// <summary>
		/// 新建产品档案
		/// </summary>
		/// <param name="sUserInfo">用户基本信息</param>
		/// <param name="goodsEntity">产品实体</param>
		/// <param name="imgList">产品图片集合</param>
		/// <param name="attList">缺陷位置ID集合</param>
		/// <returns>int受影响行数</returns>
		/// <remarks>
		/// 庄天威             2014.09.04              新建
		/// </remarks>
		public static int AddGoods(SUserInfo sUserInfo, GoodsEntity goodsEntity
			, List<GoodsImageEntity> imgList, List<GoodsAttachmentEntity> 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,Goods_Line_Code,");
				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,:Goods_Line_Code,");
				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(":Goods_Line_Code",goodsEntity.GoodsLineCode),
					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;
		}

		/// <summary>
		/// 修改产品档案
		/// </summary>
		/// <param name="sUserInfo">用户基本信息</param>
		/// <param name="goodsEntity">产品实体</param>
		/// <param name="imgList">产品图片集合</param>
		/// <param name="attList">缺陷位置ID集合</param>
		/// <returns>int受影响行数</returns>
		/// <remarks>
		/// 庄天威             2014.09.04              新建
		/// </remarks>
		public static int updateGoods(SUserInfo sUserInfo, GoodsEntity goodsEntity
			, List<GoodsImageEntity> imgList, List<GoodsAttachmentEntity> 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,Goods_Line_CODE=:Goods_Line_CODE,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(":Goods_Line_CODE",goodsEntity.GoodsLineCode),
					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;
		}

        /// <summary>
        /// 查询产品物料信息
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="goodsID"></param>
        /// <returns></returns>
        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();
                }
            }
        }

        /// <summary>
        /// 查询产品物料信息(编辑用)
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="goodsID"></param>
        /// <returns></returns>
        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 获得生产工号集合

        /// <summary>
        /// 获得生产工号集合
        /// </summary>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        /// <remarks>
        /// 陈冰             2014.09.03              新建
        /// </remarks>
        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 获得工种集合

		/// <summary>
		/// 获得工种集合
		/// </summary>
		/// <param name="sUserInfo">用户基本信息</param>
		/// <returns>DataSet</returns>
		/// <remarks>
		/// 陈冰             2014.09.03              新建
		/// </remarks>
		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 获得缺陷集合

		/// <summary>
		/// 获得缺陷集合
		/// </summary>
		/// <param name="sUserInfo">用户基本信息</param>
		/// <returns>DataSet</returns>
		/// <remarks>
		/// 陈冰             2014.09.03              新建
		/// </remarks>
		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 产品图片

		/// <summary>
		/// 根据产品ID获取产品图片
		/// </summary>
		/// <param name="sUserInfo">用户基本信息</param>
		/// <param name="goodsId">产品ID</param>
		/// <returns>DataSet</returns>
		/// <remarks>
		/// 庄天威             2014.09.04              新建
		/// </remarks>
		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 附件

		/// <summary>
		/// 新建附件信息
		/// </summary>
		/// <param name="AttList">附件实体集合</param>
		/// <param name="mainId">产品ID</param>
		/// <param name="userInfo">用户基本信息</param>
		/// <returns>int影响结果行数</returns>
		public static int AddAttachment(List<GoodsAttachmentEntity> 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;
			}
		}

		/// <summary>
		/// 修改附件信息
		/// </summary>
		/// <param name="AttList">附件实体集合</param>
		/// <param name="mainId">产品ID</param>
		/// <param name="userInfo">用户基本信息</param>
		/// <returns>int受影响行数</returns>
		public static int UpdateAttachment(List<GoodsAttachmentEntity> 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;
			}
		}

		/// <summary>
		/// 根据产品ID获取附件
		/// </summary>
		/// <param name="goodsId">产品ID</param>
		/// <returns>DataSet</returns>
		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 缺陷位置

		/// <summary>
		/// 获取缺陷位置
		/// </summary>
		/// <param name="dpEntity">缺陷位置实体</param>
		/// <param name="userInfo">用户基本信息</param>
		/// <returns>DataSet</returns>
		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();
				}
			}
		}

		/// <summary>
		/// 根据产品获得产品缺陷位置
		/// </summary>
		/// <param name="GoodsId">产品ID</param>
		/// <returns>DataSet</returns>
		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();
				}
			}
		}

		/// <summary>
		/// 保存产品缺陷位置关联(依附在添加产品中)
		/// </summary>
		/// <param name="dpList">缺陷位置集</param>
		/// <param name="GoodsId">产品ID</param>
		/// <param name="userInfo">用户基本信息</param>
		/// <returns>int</returns>
		public static int SaveGoodsDefectPosition(IDBTransaction oracleTrConn, List<int> 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 成型线类型

		/// <summary>
		/// 获取成型线类型
		/// </summary>
		/// <param name="userInfo">用户基本信息</param>
		/// <returns>数据集</returns>
		/// <remarks>
		/// 庄天威             2014.09.04              新建
		/// </remarks>
		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 产品分级数据源

		/// <summary>
		/// 产品分级数据源
		/// </summary>
		/// <param name="type">1适用半成品2检验 2适用成品检验 3入窑前检验</param>
		/// <param name="userInfo">用户基本信息</param>
		/// <returns>DataSet</returns>
		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 审核状态

		/// <summary>
		/// 获取全部审核状态
		/// </summary>
		/// <returns>DataSet审核状态数据源</returns>
		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

		/// <summary>
		/// 工序集合
		/// </summary>
		/// <param name="ProductionLineEntity">工序实体</param>
		/// <param name="sUserInfo">用户<基本信息/param>
		/// <returns>DataSet</returns>
		/// <remarks>
		/// 王鑫              2014.11.29              新建
		/// </remarks>
		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();
				}
			}
		}

		/// <summary>
		/// 获取全部工种计件方式
		/// </summary>
		/// <returns>DataSet工种计件方式集合</returns>
		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();
				}
			}
		}

        #region 工位

        #region 工位配置
        /// <summary>
        /// 获取工位
        /// </summary>
        /// <param name="cre"></param>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetWorkStation(ClientRequestEntity cre, SUserInfo sUserInfo)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sqlString = string.Empty;
                List<OracleParameter> paras = new List<OracleParameter>();

                con.Open();
                sqlString = "SELECT ws.workstationid\n" +
							"      ,ws.workstationname\n" +
							"      ,p.procedurename\n" +
							"      ,ws.remarks\n" +
							"  FROM tp_mst_workstation ws\n" +
							"  LEFT JOIN tp_pc_procedure p\n" +
							"    ON p.procedureid = ws.procedureid\n" +
							" WHERE ws.valueflag = '1'\n" +
							"   AND ws.accountid = :accountid";

				paras.Add(new OracleParameter(":accountid", sUserInfo.AccountID));

                if (!string.IsNullOrEmpty(cre.Properties["workstationname"] + ""))
                {
                    sqlString += " AND ws.workstationname LIKE :workstationname ";
                    paras.Add(new OracleParameter(":workstationname", "%" + cre.Properties["workstationname"] + "%"));
                }

                if (!string.IsNullOrEmpty(cre.Properties["remarks"] + ""))
                {
                    sqlString += " AND ws.remarks LIKE :remarks ";
                    paras.Add(new OracleParameter(":remarks", "%" + cre.Properties["remarks"] + "%"));
                }

                sqlString += " ORDER BY ws.displayno";

                sre.Data = con.GetSqlResultToDs(sqlString, paras.ToArray());
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取工位工号
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetWorkStationUser(int workStationID)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sqlString =
				"SELECT u.userid\n" +
				"      ,u.usercode\n" +
				"      ,u.username\n" +
				"      ,o.organizationid\n" +
				"      ,o.organizationname\n" +
				"      ,o.organizationfullname\n" +
				"      ,o.telephone\n" +
				"      ,u.remarks\n" +
				"      ,0 as addflag\n" +
				"  FROM tp_mst_user u\n" +
				"  LEFT JOIN tp_mst_organization o\n" +
				"    ON o.organizationid = u.organizationid\n" +
				" WHERE EXISTS (SELECT 1\n" +
				"          FROM tp_mst_workstationuser wsu\n" +
				"         WHERE wsu.userid = u.userid\n" +
				"           AND wsu.workstationid = :workstationid)\n" +
				" ORDER BY u.usercode";

				OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":workstationid", workStationID)
                };

                con.Open();
                sre.Data = con.GetSqlResultToDs(sqlString, paras);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

		/// <summary>
		/// 保存工位工号
		/// </summary>
		/// <param name="SProductEntity"></param>
		/// <param name="userInfo"></param>
		/// <returns></returns>
		public static ServiceResultEntity SaveWorkStationUser(ClientRequestEntity cre, SUserInfo sUserInfo)
		{
			ServiceResultEntity sre = new ServiceResultEntity();
			IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
			try
			{
				string sqlString = string.Empty;
				OracleParameter[] paras = null;
				int workstationid = Convert.ToInt32(cre.Properties["workststionid"]);
				oracleTrConn.Connect();

				// 校验当前工号是否配置该工位
				sqlString = "SELECT 1\n" +
							"  FROM tp_mst_workstation w\n" +
							" WHERE w.workstationid = :workstationid";

				paras = new OracleParameter[]
				{
					new OracleParameter(":workstationid", workstationid)
				};

				object isExists = oracleTrConn.GetSqlResultToObj(sqlString, paras);
				if (isExists == null)
				{
					sre.OtherStatus = -1;
					sre.Message = "当前工位不存在";
					return sre;
				}

				// 删除当前工位工号
				sqlString = "DELETE FROM tp_mst_workstationuser wu WHERE wu.workstationid = :workstationid";

				paras = new OracleParameter[]
				{
					new OracleParameter(":workstationid", workstationid)
				};

				sre.OtherStatus += oracleTrConn.ExecuteNonQuery(sqlString, paras);

				// 保存工位工号
				sqlString = "INSERT INTO tp_mst_workstationuser\n" +
							"    (workstationid\n" +
							"    ,userid\n" +
							"    ,usercode\n" +
							"    ,accountid\n" +
							"    ,createuserid\n" +
							"    ,updateuserid\n" +
							"    ,organizationid)\n" +
							"    SELECT :workstationid\n" +
							"          ,u.userid\n" +
							"          ,u.usercode\n" +
							"          ,:accountid\n" +
							"          ,:createuserid\n" +
							"          ,:createuserid\n" +
							"          ,u.organizationid\n" +
							"      FROM tp_mst_user u\n" +
							"     WHERE u.userid = :userid";

				foreach (DataRow row in cre.Data.Tables[0].Rows)
                {
					paras = new OracleParameter[]
					{
						new OracleParameter(":workstationid", workstationid),
						new OracleParameter(":userid", row["userid"]),
						new OracleParameter(":accountid", sUserInfo.AccountID),
						new OracleParameter(":createuserid", sUserInfo.UserID)
					};

					sre.OtherStatus += oracleTrConn.ExecuteNonQuery(sqlString, paras);
				}

				oracleTrConn.Commit();
				oracleTrConn.Disconnect();
			}
			catch (Exception ex)
			{
				if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
				{
					oracleTrConn.Rollback();
					oracleTrConn.Disconnect();
				}
				throw new Exception(ex.ToString(), ex);
			}
			finally
			{
				if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
				{
					oracleTrConn.Disconnect();
				}
			}
			return sre;
		}
		#endregion

		#region 工位打卡
		/// <summary>
		/// 获取工号工位
		/// </summary>
		/// <param name="sUserInfo"></param>
		/// <returns></returns>
		public static ServiceResultEntity GetUserWorkStation(SUserInfo sUserInfo)
		{
			ServiceResultEntity sre = new ServiceResultEntity();
			IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
			try
			{
				string sqlString =
				"SELECT w.workstationid\n" +
				"      ,w.workstationname\n" +
				"  FROM tp_mst_workstationuser wu\n" +
				" INNER JOIN tp_mst_workstation w\n" +
				"    ON w.workstationid = wu.workstationid\n" +
				" WHERE w.valueflag = '1'\n" +
				"   AND wu.usercode = :usercode\n" +
				" ORDER BY w.displayno";

				OracleParameter[] paras = new OracleParameter[]
				{
					new OracleParameter(":usercode", sUserInfo.UserCode)
				};

				con.Open();
				sre.Data = con.GetSqlResultToDs(sqlString, paras);
				if (sre.Data.Tables[0].Rows.Count == 0)
				{
					sre.Message = "当前工号未配置工位";
				}
				return sre;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (con.ConnState == ConnectionState.Open)
				{
					con.Close();
				}
			}
		}

		/// <summary>
		/// 工号工位打卡
		/// </summary>
		/// <param name="SProductEntity"></param>
		/// <param name="userInfo"></param>
		/// <returns></returns>
		public static ServiceResultEntity SaveUserWorkStation(int workStationID, SUserInfo userInfo)
		{
			ServiceResultEntity sre = new ServiceResultEntity();
			IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
			try
			{
				string sqlString = string.Empty;
				OracleParameter[] paras = null;
				oracleTrConn.Connect();

				// 校验当前工号是否配置该工位
				sqlString = "SELECT 1\n" +
							"  FROM tp_mst_workstationuser wu\n" +
							" WHERE wu.userid = :userid\n" +
							"   AND wu.workstationid = :workstationid";

				paras = new OracleParameter[]
				{
					new OracleParameter(":userid", userInfo.UserID),
					new OracleParameter(":workstationid", workStationID)
				};

				object isExists = oracleTrConn.GetSqlResultToObj(sqlString, paras);
				if (isExists == null)
				{
					sre.OtherStatus = -1;
					sre.Message = "当前工号未配置该工位";
					return sre;
				}

				// 删除当前工号在任何一个工位，以确保当前工号在工位中唯一
				sqlString = "UPDATE tp_mst_workstation\n" +
							"   SET usercode = NULL\n" +
							" WHERE usercode = :usercode";

				paras = new OracleParameter[]
				{
					new OracleParameter(":usercode", userInfo.UserCode)
				};

				sre.OtherStatus += oracleTrConn.ExecuteNonQuery(sqlString, paras);

				// 更新工位当前工号
				sqlString = "UPDATE tp_mst_workstation\n" +
							"   SET usercode     = :usercode\n" +
							"      ,updateuserid = :userid\n" +
							" WHERE workstationid = :workstationid";

				paras = new OracleParameter[]
				{
					new OracleParameter(":usercode", userInfo.UserCode),
					new OracleParameter(":userid", userInfo.UserID),
					new OracleParameter(":workstationid", workStationID)
				};

				sre.OtherStatus += oracleTrConn.ExecuteNonQuery(sqlString, paras);

				oracleTrConn.Commit();
				oracleTrConn.Disconnect();
			}
			catch (Exception ex)
			{
				if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
				{
					oracleTrConn.Rollback();
					oracleTrConn.Disconnect();
				}
				throw new Exception(ex.ToString(), ex);
			}
			finally
			{
				if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
				{
					oracleTrConn.Disconnect();
				}
			}
			return sre;
		}
		#endregion

		#endregion
	}
}