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);
                OracleParameter[] paras = new OracleParameter[] { };
                string sqlString = @"
								SELECT
									TP_MST_GOODSKINGDEECODE.GUID,
									TP_MST_GOODSKINGDEECODE.GOODSCODE,
									gd.goodsname,
									TP_MST_GOODSKINGDEECODE.GLAZETYPE,
									TP_MST_GOODSKINGDEECODE.LOGONAME,
									TP_MST_GOODSKINGDEECODE.KINGDEECODE,
									TP_MST_GOODSKINGDEECODE.CREATETIME,
									TP_MST_USER.USERNAME AS CREATEUSER,
									TP_MST_GOODSKINGDEECODE.UPDATETIME AS UPDATETIME,
									USERS.USERNAME AS UPDATEUSER
								FROM
									TP_MST_GOODSKINGDEECODE
									LEFT JOIN TP_MST_GOODS gd on gd.GOODSCODE=TP_MST_GOODSKINGDEECODE.GOODSCODE
									LEFT JOIN TP_MST_USER ON TP_MST_GOODSKINGDEECODE.CREATEUSERID=TP_MST_USER.USERID 
									LEFT JOIN TP_MST_USER USERS ON TP_MST_GOODSKINGDEECODE.UPDATEUSERID=USERS.USERID 
									WHERE 1=1  AND TP_MST_GOODSKINGDEECODE.valueflag=1 ";
                if (!string.IsNullOrEmpty(cre.Properties["GoodsCode"].ToString()))
                {
                    sqlString += " AND TP_MST_GOODSKINGDEECODE.GOODSCODE like :GOODSCODE";
                }
                if (!string.IsNullOrEmpty(cre.Properties["GlazeType"].ToString()))
                {
                    sqlString += " AND TP_MST_GOODSKINGDEECODE.GLAZETYPE = :GLAZETYPE";
                }
                if (!string.IsNullOrEmpty(cre.Properties["LogoName"].ToString()))
                {
                    sqlString += " AND TP_MST_GOODSKINGDEECODE.LOGONAME = :LOGONAME";
                }
                if (!string.IsNullOrEmpty(cre.Properties["KingdeeCode"].ToString()))
                {
                    sqlString += " AND TP_MST_GOODSKINGDEECODE.KINGDEECODE like :KINGDEECODE";
                }
                if (!string.IsNullOrEmpty(cre.Properties["GoodsName"].ToString()))
                {
                    sqlString += " AND gd.goodsname like :GoodsName";
                }
                paras = new OracleParameter[]{
                    new OracleParameter(":GOODSCODE",'%'+cre.Properties["GoodsCode"].ToString()+'%'),
                    new OracleParameter(":GoodsName",'%'+cre.Properties["GoodsName"].ToString()+'%'),
                    new OracleParameter(":GLAZETYPE",cre.Properties["GlazeType"].ToString()),
                    new OracleParameter(":LOGONAME",cre.Properties["LogoName"].ToString()),
                    new OracleParameter(":KINGDEECODE",'%'+cre.Properties["KingdeeCode"].ToString()+'%'),
                };
                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = conn.GetSqlResultToDs(sqlString, paras);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <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
					   DECODE(LogoName, '无标', 1)		,	LogoName";
                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = conn.GetSqlResultToDs(sqlString, null);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        ///<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 GOODSCODE =:GOODSCODE   
														AND GLAZETYPE =:GLAZETYPE AND LOGONAME =:LOGONAME
														AND valueflag=1";

                    paras = new OracleParameter[] { new OracleParameter(":KINGDEECODE", cre.Properties["KINGDEECODE"].ToString()),
                                new OracleParameter(":GOODSCODE", cre.Properties["GOODSCODE"].ToString()),
                                new OracleParameter(":GLAZETYPE", cre.Properties["GLAZETYPE"].ToString()) ,
                                new OracleParameter(":LOGONAME", cre.Properties["LOGONAME"].ToString()) ,
                            };
                    dt = new DataTable();
                    dt = conn.GetSqlResultToDt(sqlString, paras);
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        //存在相同金蝶代码
                        sre.OtherStatus = -1001;
                        return sre;
                    }
                }
                #region 新增
                if (cre.Properties["FromStatus"].ToString() == "Add")
                {
                    //保存客户信息
                    sqlString = @"INSERT INTO TP_MST_GOODSKINGDEECODE(
											GOODSCODE,
											GLAZETYPE,
											LOGONAME,
											KINGDEECODE,
											CREATEUSERID,
											UPDATEUSERID,
											VALUEFLAG
										) VALUES(
											:GOODSCODE,
											:GLAZETYPE,
											:LOGONAME,
											:KINGDEECODE,
											:CREATEUSERID,:UPDATEUSERID,
											 '1' )";
                    paras = new OracleParameter[] {
                    new OracleParameter(":GOODSCODE", cre.Properties["GOODSCODE"].ToString()),
                    new OracleParameter(":GLAZETYPE", cre.Properties["GLAZETYPE"].ToString()) ,
                    new OracleParameter(":LOGONAME", cre.Properties["LOGONAME"].ToString()) ,
                    new OracleParameter(":KINGDEECODE", cre.Properties["KINGDEECODE"].ToString()) ,
                    new OracleParameter(":CREATEUSERID", sUserInfo.UserID) ,
                    new OracleParameter(":UPDATEUSERID", sUserInfo.UserID) ,
                };
                }
                #endregion
                #region 编辑
                if (cre.Properties["FromStatus"].ToString() == "Edit")
                {
                    sqlString = @"SELECT 1 FROM TP_MST_GOODSKINGDEECODE
													WHERE
														KINGDEECODE = :KINGDEECODE  AND GOODSCODE =:GOODSCODE   
														AND GLAZETYPE =:GLAZETYPE AND LOGONAME =:LOGONAME
														AND valueflag=1";

                    paras = new OracleParameter[] {
                                 new OracleParameter(":KINGDEECODE", cre.Properties["KINGDEECODE"].ToString()),
                                new OracleParameter(":GOODSCODE", cre.Properties["GOODSCODE"].ToString()),
                                new OracleParameter(":GLAZETYPE", cre.Properties["GLAZETYPE"].ToString()) ,
                                new OracleParameter(":LOGONAME", cre.Properties["LOGONAME"].ToString()) ,
                        };
                    dt = new DataTable();
                    dt = conn.GetSqlResultToDt(sqlString, paras);
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        //存在相同版面编码
                        sre.OtherStatus = -1001;
                        return sre;
                    }
                    sqlString = @" UPDATE TP_MST_GOODSKINGDEECODE
											SET
												GOODSCODE=:GOODSCODE,
												GLAZETYPE = :GLAZETYPE,
												LOGONAME =:LOGONAME,
												KINGDEECODE = :KINGDEECODE,
												UPDATEUSERID =:UPDATEUSERID,
												UPDATETIME = SYSDATE
											WHERE
												GUID =:GUID ";
                    paras = new OracleParameter[] {
                    new OracleParameter(":GOODSCODE", cre.Properties["GOODSCODE"].ToString()) ,
                    new OracleParameter(":GLAZETYPE", cre.Properties["GLAZETYPE"].ToString()) ,
                    new OracleParameter(":LOGONAME", cre.Properties["LOGONAME"].ToString()) ,
                    new OracleParameter(":KINGDEECODEONE", cre.Properties["KINGDEECODEONE"].ToString()) ,
                    new OracleParameter(":KINGDEECODE", cre.Properties["KINGDEECODE"].ToString()) ,
                    new OracleParameter(":UPDATEUSERID", sUserInfo.UserID) ,
                    new OracleParameter(":GUID", cre.Properties["GUID"].ToString()) ,
                };
                }
                #endregion

                #region 删除
                if (cre.Properties["FromStatus"].ToString() == "Delete")
                {
                    sqlString = @"UPDATE TP_MST_GOODSKINGDEECODE
											SET
												VALUEFLAG=0
											WHERE
												GUID = :GUID";
                    paras = new OracleParameter[] {
                    new OracleParameter(":GUID", cre.Properties["GUID"].ToString()) ,
                };
                }
                #endregion
                int result = conn.ExecuteNonQuery(sqlString, paras);
                if (result <= 0)
                {
                    //未改变任何数据
                    sre.OtherStatus = -1003;
                    return sre;
                }
                else
                {
                    //提交数据
                    sre.OtherStatus = 1;
                    conn.Commit();
                }

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Disconnect();
                }
            }
        }


        /// <summary>
        /// 查询模具对应产品
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="goodsID"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetMouldGoodsCode(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                OracleParameter[] paras = new OracleParameter[] { };
                string sqlString = @"
								SELECT  MOULD.GUID,
										MOULD.GOODSCODE,
										GD.GOODSNAME,
										MOULD.MOULDGOODSCODE,
										MOULD.MOULDNAME
										FROM TP_MST_MOULDGOODSCODE MOULD
										LEFT JOIN TP_MST_GOODS GD ON MOULD.GOODSCODE=GD.GOODSCODE AND GD.VALUEFLAG=1
										WHERE MOULD.VALUEFLAG=1  ";
                if (!string.IsNullOrEmpty(cre.Properties["GoodsCode"].ToString()))
                {
                    sqlString += "AND MOULD.GOODSCODE  like :GOODSCODE";
                }
                if (!string.IsNullOrEmpty(cre.Properties["MOULDGOODSCODE"].ToString()))
                {
                    sqlString += " AND  MOULD.MOULDGOODSCODE like :MOULDGOODSCODE";
                }
                if (!string.IsNullOrEmpty(cre.Properties["MOULDNAME"].ToString()))
                {
                    sqlString += " AND  MOULD.MOULDNAME like :MOULDNAME";
                }
                paras = new OracleParameter[]{
                    new OracleParameter(":GOODSCODE",'%'+cre.Properties["GoodsCode"].ToString()+'%'),
                    new OracleParameter(":MOULDNAME",'%'+cre.Properties["MOULDNAME"].ToString()+'%'),
                    new OracleParameter(":MOULDGOODSCODE",'%'+cre.Properties["MOULDGOODSCODE"].ToString()+'%'),
                };
                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = conn.GetSqlResultToDs(sqlString, paras);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }


        ///<summary>
        ///保存金蝶代码信息
        /// </summary>
        public static ServiceResultEntity AddMouldCode(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            IDBTransaction conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                ServiceResultEntity sre = new ServiceResultEntity();
                DataTable dt = new DataTable();
                string sqlString = string.Empty;
                OracleParameter[] paras = new OracleParameter[] { };
                //如果版面信息是新建的情况下，是否存在相同版面编码
                if (cre.Properties["FromStatus"].ToString() == "Add")
                {
                    sqlString = @"		SELECT 1 FROM TP_MST_MOULDGOODSCODE
																	WHERE VALUEFLAG=1 
																		AND	 GOODSCODE= :GOODSCODE
																		AND  MOULDGOODSCODE= :MOULDGOODSCODE
																		AND  MOULDNAME= :MOULDNAME";

                    paras = new OracleParameter[]{
                    new OracleParameter(":GOODSCODE",cre.Properties["GOODSCODE"].ToString()),
                    new OracleParameter(":MOULDNAME",cre.Properties["MOULDNAME"].ToString()),
                    new OracleParameter(":MOULDGOODSCODE",cre.Properties["MOULDGOODSCODE"].ToString()),
                };
                    dt = new DataTable();
                    dt = conn.GetSqlResultToDt(sqlString, paras);
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        //存在相同金蝶代码
                        sre.OtherStatus = -1001;
                        return sre;
                    }
                }
                #region 新增
                if (cre.Properties["FromStatus"].ToString() == "Add")
                {
                    //保存客户信息
                    sqlString = @"INSERT INTO TP_MST_MOULDGOODSCODE(
										GOODSCODE,
										MOULDGOODSCODE,
										MOULDNAME,
										VALUEFLAG
										) VALUES(
										:GOODSCODE,
										:MOULDGOODSCODE,
										:MOULDNAME,
										'1' )";
                    paras = new OracleParameter[]{
                    new OracleParameter(":GOODSCODE",cre.Properties["GOODSCODE"].ToString()),
                    new OracleParameter(":MOULDNAME",cre.Properties["MOULDNAME"].ToString()),
                    new OracleParameter(":MOULDGOODSCODE",cre.Properties["MOULDGOODSCODE"].ToString()),
                        };
                }
                #endregion
                #region 编辑
                if (cre.Properties["FromStatus"].ToString() == "Edit")
                {
                    sqlString = @"		SELECT 1 FROM TP_MST_MOULDGOODSCODE
																	WHERE VALUEFLAG=1 
																		AND	 GOODSCODE=:GOODSCODE
																		AND  MOULDGOODSCODE=:MOULDGOODSCODE
																		AND  MOULDNAME=:MOULDNAME";

                    paras = new OracleParameter[]{
                    new OracleParameter(":GOODSCODE",cre.Properties["GOODSCODE"].ToString()),
                    new OracleParameter(":MOULDNAME",cre.Properties["MOULDNAME"].ToString()),
                    new OracleParameter(":MOULDGOODSCODE",cre.Properties["MOULDGOODSCODE"].ToString()),
                        };
                    dt = new DataTable();
                    dt = conn.GetSqlResultToDt(sqlString, paras);
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        //存在相同版面编码
                        sre.OtherStatus = -1001;
                        return sre;
                    }
                    sqlString = @"UPDATE TP_MST_MOULDGOODSCODE
											SET
												GOODSCODE=:GOODSCODE,
												MOULDGOODSCODE =:MOULDGOODSCODE,
												MOULDNAME =:MOULDNAME
											WHERE
												GUID=:GUID";
                    paras = new OracleParameter[]{
                    new OracleParameter(":GOODSCODE",cre.Properties["GOODSCODE"].ToString()),
                    new OracleParameter(":MOULDNAME",cre.Properties["MOULDNAME"].ToString()),
                    new OracleParameter(":MOULDGOODSCODE",cre.Properties["MOULDGOODSCODE"].ToString()),
                    new OracleParameter(":GUID",cre.Properties["GUID"].ToString()),
                        };
                }
                #endregion

                #region 删除
                if (cre.Properties["FromStatus"].ToString() == "Delete")
                {
                    sqlString = @"UPDATE TP_MST_MOULDGOODSCODE
											SET
												VALUEFLAG=0
											WHERE GUID=:GUID
												 ";
                    paras = new OracleParameter[]{
                    new OracleParameter(":GUID",cre.Properties["GUID"].ToString()),
                };
                }
                #endregion
                int result = conn.ExecuteNonQuery(sqlString, paras);
                if (result <= 0)
                {
                    //未改变任何数据
                    sre.OtherStatus = -1003;
                    return sre;
                }
                else
                {
                    //提交数据
                    sre.OtherStatus = 1;
                    conn.Commit();
                }

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Disconnect();
                }
            }
        }


        ///<summary>
        ///查询登录记录
        /// </summary>
        public static ServiceResultEntity GetUserLoginLogdata(SUserInfo sUserInfo, ClientRequestEntity cre)

        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                OracleParameter[] paras = new OracleParameter[] { };
                string sqlString = @"SELECT * from (SELECT
                                        	0 AS Sel,
                                        	U.UserID,
                                        	U.UserCode,
                                        	U.limitMAC,
                                        	U.UserName,
                                        	POST.PostName,
                                        	U.CanPCLogin,
                                        	U.CanSmartLogin,
                                        	U.IsWorker,
                                        	U.IsGroutingWorker,
                                        	U.IsPublicBody,
                                        	U.CanLoginPRD,
                                        	U.CanLoginMBC,
                                        	U.ValueFlag,
                                        	O.OrganizationName,
                                        	O.OrganizationFullName,
                                        	U.Remarks,
                                        	TP_MST_USERLOGINLOG.MAC,
                                        	COMPUTERNAME,
                                            ORGANIZATIONCODE,
                                        CASE
                                        		ACTIONTYPE 
                                        		WHEN '0' THEN
                                        		'pc登录' 
                                        		WHEN '1' THEN
                                        		'PC登出' 
                                        		WHEN '2' THEN
                                        		'移动设备登录' 
                                        		WHEN '3' THEN
                                        		'移动设备登出' 
                                        		WHEN '4' THEN
                                        		'PC登录MBC' ELSE 'PC登出MBC' 
                                        	END ACTIONTYPE,
                                        	IP ,IMEI,ACTIONTIME
                                        FROM
                                        	TP_MST_USERLOGINLOG
                                        	LEFT JOIN TP_MST_User U ON u.USERID = TP_MST_USERLOGINLOG.USERID
                                        	LEFT JOIN TP_MST_Organization O ON U.Organizationid = O.Organizationid
                                        	LEFT JOIN TP_MST_POST POST ON POST.POSTID = U.POST 
                                        WHERE
                                        	U.AccountID = 1) where 1=1  AND ACTIONTIME>=to_date(:opentime,'yyyy-mm-dd hh24:mi:ss')AND ACTIONTIME<=to_date(:closetime,'yyyy-mm-dd hh24:mi:ss')";
                if (!string.IsNullOrEmpty(cre.Properties["UserCode"].ToString()))
                {
                    sqlString += "AND UserCode  like :UserCode";
                }
                if (!string.IsNullOrEmpty(cre.Properties["UserName"].ToString()))
                {
                    sqlString += " AND  UserName like :UserName";
                }
                if (!string.IsNullOrEmpty(cre.Properties["Organization"].ToString()))
                {
                    sqlString += " AND  ORGANIZATIONCODE = :Organization";
                }
                if (!string.IsNullOrEmpty(cre.Properties["ActionType"].ToString()))
                {
                    sqlString += " AND  ActionType like :ActionType";
                }
                sqlString += @" ORDER BY
                                        	ACTIONTIME DESC ";
                paras = new OracleParameter[]{
                    new OracleParameter(":UserCode",'%'+cre.Properties["UserCode"].ToString()+'%'),
                    new OracleParameter(":UserName",'%'+cre.Properties["UserName"].ToString()+'%'),
                    new OracleParameter(":Organization",cre.Properties["Organization"].ToString()),
                    new OracleParameter(":ActionType",'%'+cre.Properties["ActionType"].ToString()+'%'),
                     new OracleParameter(":opentime",cre.Properties["opentime"].ToString()),
                     new OracleParameter(":closetime",cre.Properties["closetime"].ToString()),
                };
                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = conn.GetSqlResultToDs(sqlString, paras);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
    }
}