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 MAC
	                                        WHEN 'xx:xx:xx:xx:xx:xx' THEN
		                                    'WEB质量登记'
	                                        ELSE
	                                        CASE
		                                    ACTIONTYPE 
		                                    WHEN '0' THEN
		                                    'PC登录' 
		                                    WHEN '1' THEN
		                                    'PC登出' 
		                                    WHEN '2' THEN
		                                    '移动设备登录' 
		                                    WHEN '3' THEN
		                                    '移动设备登出' 
		                                    WHEN '4' THEN
		                                    'PC登录MBC' ELSE 'PC登出MBC' 
	                                        END 
                                            END  as  ACTIONTYPE,
                                        	IP ,
                                            IMEI,
                                            ACTIONTIME
                                        FROM
                                        	TP_MST_USERLOGINLOG
                                        	LEFT JOIN TP_MST_User U ON u.USERID = TP_MST_USERLOGINLOG.USERID
                                        	LEFT JOIN TP_MST_Organization O ON U.Organizationid = O.Organizationid
                                        	LEFT JOIN TP_MST_POST POST ON POST.POSTID = U.POST 
                                        WHERE
                                        	U.AccountID = 1) where 1=1  AND ACTIONTIME>=to_date(:opentime,'yyyy-mm-dd hh24:mi:ss')AND ACTIONTIME<=to_date(:closetime,'yyyy-mm-dd hh24:mi:ss')";
                if (!string.IsNullOrEmpty(cre.Properties["UserCode"].ToString()))
                {
                    sqlString += "AND UserCode  like :UserCode";
                }
                if (!string.IsNullOrEmpty(cre.Properties["UserName"].ToString()))
                {
                    sqlString += " AND  UserName like :UserName";
                }
                if (!string.IsNullOrEmpty(cre.Properties["Organization"].ToString()))
                {
                    sqlString += " AND  ORGANIZATIONCODE = :Organization";
                }
                if (!string.IsNullOrEmpty(cre.Properties["ActionType"].ToString()))
                {
                    if (cre.Properties["ActionType"].ToString()== "6")
                    {
                        sqlString += " AND  MAC = 'xx:xx:xx:xx:xx:xx'";
                    }
                    else { sqlString += " AND  ActionType like :ActionType"; }  
                }
                sqlString += @" ORDER BY
                                        	ACTIONTIME DESC";
                paras = new OracleParameter[]{
                    new OracleParameter(":UserCode",'%'+cre.Properties["UserCode"].ToString()+'%'),
                    new OracleParameter(":UserName",'%'+cre.Properties["UserName"].ToString()+'%'),
                    new OracleParameter(":Organization",cre.Properties["Organization"].ToString()),
                    new OracleParameter(":ActionType",'%'+cre.Properties["ActionType"].ToString()+'%'),
                     new OracleParameter(":opentime",cre.Properties["opentime"].ToString()),
                     new OracleParameter(":closetime",cre.Properties["closetime"].ToString()),
                };
                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = conn.GetSqlResultToDs(sqlString, paras);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
    }
}