IMEX.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,");
				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;
		}

		/// <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,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;
		}

        /// <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();
				}
			}
		}


		/// <summary>
		/// 查询金蝶代码数据
		/// </summary>
		/// <param name="sUserInfo"></param>
		/// <param name="goodsID"></param>
		/// <returns></returns>
		public static ServiceResultEntity GetKingdeeCode(SUserInfo sUserInfo,ClientRequestEntity cre)
		{
			IDBConnection conn = null;
			try
			{
				conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

				string sqlString = @"
								SELECT
									TP_MST_GOODSKINGDEECODE.GOODSCODE,
									gd.goodsname,
									TP_MST_GOODSKINGDEECODE.GLAZETYPE,
									TP_MST_GOODSKINGDEECODE.LOGONAME,
									TP_MST_GOODSKINGDEECODE.KINGDEECODE 
								FROM
									TP_MST_GOODSKINGDEECODE
									LEFT JOIN TP_MST_GOODS gd on gd.GOODSCODE=TP_MST_GOODSKINGDEECODE.GOODSCODE
								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";
				}
				OracleParameter[] 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();
				}
			}
		}

		/// <summary>
		/// 查询商标名称
		/// </summary>
		/// <param name="sUserInfo"></param>
		/// <param name="goodsID"></param>
		/// <returns></returns>
		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
								isdefault DESC,
								displayno";
				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();
				}
			}
		}

		///<summary>
		///保存金蝶代码信息
		/// </summary>
		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 valueflag=1";

						paras = new OracleParameter[] { new OracleParameter(":KINGDEECODE", cre.Properties["KINGDEECODE"].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,
											VALUEFLAG
										) VALUES(
											:GOODSCODE,
											:GLAZETYPE,
											:LOGONAME,
											:KINGDEECODE,
											 '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()) ,
				};
					}
					#endregion
					#region 编辑
					if (cre.Properties["FromStatus"].ToString() == "Edit")
					{
						sqlString = @"
											SELECT 1 FROM TP_MST_GOODSKINGDEECODE WHERE KINGDEECODE = :KINGDEECODE AND KINGDEECODE != :KINGDEECODEONE AND valueflag=1";

						paras = new OracleParameter[] { 
							new OracleParameter(":KINGDEECODE", cre.Properties["KINGDEECODE"].ToString()),
							new OracleParameter(":KINGDEECODEONE", cre.Properties["KINGDEECODEONE"].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
											WHERE
												KINGDEECODE = :KINGDEECODEONE";
						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()) ,
				};
					}
				#endregion

					#region 删除
				if (cre.Properties["FromStatus"].ToString() == "Delete")
				{
					sqlString = @"UPDATE TP_MST_GOODSKINGDEECODE
											SET
												VALUEFLAG=0
											WHERE
												KINGDEECODE = :KINGDEECODEONE";
					paras = new OracleParameter[] {
					new OracleParameter(":KINGDEECODEONE", cre.Properties["KINGDEECODEONE"].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();
					}
				}
			}
	}
}