using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using DSWeb.Models;
using DSWeb.DataAccess;

namespace DSWeb.EntityDA
{
    public class OpSeaeHSDA
    {
        private const string PARM_OpSeaeHS_GID = "@GID";

        private const string SQL_SELECT_OpSeaeHS_ALL = " SELECT GID,BSNO,CNTRNO,CARGOCODE,CARGOID,PKGS1,KINDPKGS1,NOPKGS1,CARGOGROSSWT1,CARGOMEASUREMENT1,PKGS2,KINDPKGS2,NOPKGS2,CARGOGROSSWT2,CARGOMEASUREMENT2,KGS,CBM,CONSIGNMENTNO,COUNTRYCODE,REMARK,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME FROM op_seae_hs order by CNTRNO";

        private const string SQL_SELECT_OpSeaeHS_BYGID = " SELECT GID,BSNO,CNTRNO,CARGOCODE,CARGOID,PKGS1,KINDPKGS1,NOPKGS1,CARGOGROSSWT1,CARGOMEASUREMENT1,PKGS2,KINDPKGS2,NOPKGS2,CARGOGROSSWT2,CARGOMEASUREMENT2,KGS,CBM,CONSIGNMENTNO,COUNTRYCODE,REMARK,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME FROM op_seae_hs WHERE GID = @GID";

        private const string SQL_SELECT_OpSeaeHS_TOP1 = " SELECT top 1 GID,BSNO,CNTRNO,CARGOCODE,CARGOID,PKGS1,KINDPKGS1,NOPKGS1,CARGOGROSSWT1,CARGOMEASUREMENT1,PKGS2,KINDPKGS2,NOPKGS2,CARGOGROSSWT2,CARGOMEASUREMENT2,KGS,CBM,CONSIGNMENTNO,COUNTRYCODE,REMARK,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME FROM op_seae_hs order by CNTRNO";

        private string strSqlInsertInfo = "insert into [op_seae_hs](GID,BSNO,CNTRNO,CARGOCODE,CARGOID,PKGS1,KINDPKGS1,NOPKGS1,CARGOGROSSWT1,CARGOMEASUREMENT1,PKGS2,KINDPKGS2,NOPKGS2,CARGOGROSSWT2,CARGOMEASUREMENT2,KGS,CBM,CONSIGNMENTNO,COUNTRYCODE,REMARK,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME) values(@GID,@BSNO,@CNTRNO,@CARGOCODE,@CARGOID,@PKGS1,@KINDPKGS1,@NOPKGS1,@CARGOGROSSWT1,@CARGOMEASUREMENT1,@PKGS2,@KINDPKGS2,@NOPKGS2,@CARGOGROSSWT2,@CARGOMEASUREMENT2,@KGS,@CBM,@CONSIGNMENTNO,@COUNTRYCODE,@REMARK,@CREATEUSER)";

        private string strSqlUpdateInfo = "update [op_seae_hs] set CNTRNO=@,CARGOCODE=@,CARGOID=@,PKGS1=@,KINDPKGS1=@,NOPKGS1=@,CARGOGROSSWT1=@,CARGOMEASUREMENT1=@,PKGS2=@,KINDPKGS2=@,NOPKGS2=@,CARGOGROSSWT2=@,CARGOMEASUREMENT2=@,KGS=@,CBM=@,CONSIGNMENTNO=@,COUNTRYCODE=@,REMARK=@,MODIFIEDUSER=@MODIFIEDUSER,MODIFIEDTIME=getdate() where GID=@GID and BSNO=@BSNO";

        private const string SQL_DELETE_OpSeaeHS_BY_GID = "DELETE FROM op_seae_hs WHERE GID = @GID";

        #region 获取所有业务状态信息信息
        /// <summary>
        /// 获取所有业务状态信息信息
        /// </summary>
        /// <returns>List<OpSeaeHSEntity></returns>
        public IList<OpSeaeHSEntity> GetAllOpSeaeHS()
        {
            IList<OpSeaeHSEntity> OpSeaeHSEntites = new List<OpSeaeHSEntity>();
            OpSeaeHSEntity OpSeaeHSEntity = null;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_OpSeaeHS_ALL, null))
            {
                try
                {
                    while (sqlRead.Read())
                    {
                        OpSeaeHSEntity = new OpSeaeHSEntity();
                        if (!sqlRead.IsDBNull(0)) { OpSeaeHSEntity.GID = sqlRead.GetString(0); }
                        if (!sqlRead.IsDBNull(1)) { OpSeaeHSEntity.BSNO = sqlRead.GetString(1); }
                        if (!sqlRead.IsDBNull(2)) { OpSeaeHSEntity.CNTRNO = sqlRead.GetString(2); }
                        if (!sqlRead.IsDBNull(3)) { OpSeaeHSEntity.CARGOCODE = sqlRead.GetString(3); }
                        if (!sqlRead.IsDBNull(4)) { OpSeaeHSEntity.CARGOID = sqlRead.GetString(4); }
                        if (!sqlRead.IsDBNull(5)) { OpSeaeHSEntity.PKGS1 = sqlRead.GetInt32(5); }
                        if (!sqlRead.IsDBNull(6)) { OpSeaeHSEntity.KINDPKGS1 = sqlRead.GetString(6); }
                        if (!sqlRead.IsDBNull(7)) { OpSeaeHSEntity.NOPKGS1 = sqlRead.GetString(7); }
                        if (!sqlRead.IsDBNull(8)) { OpSeaeHSEntity.CARGOGROSSWT1 = sqlRead.GetDecimal(8); }
                        if (!sqlRead.IsDBNull(9)) { OpSeaeHSEntity.CARGOMEASUREMENT1 = sqlRead.GetDecimal(9); }
                        if (!sqlRead.IsDBNull(10)) { OpSeaeHSEntity.PKGS2 = sqlRead.GetInt32(10); }
                        if (!sqlRead.IsDBNull(11)) { OpSeaeHSEntity.KINDPKGS2 = sqlRead.GetString(11); }
                        if (!sqlRead.IsDBNull(12)) { OpSeaeHSEntity.NOPKGS2 = sqlRead.GetString(12); }
                        if (!sqlRead.IsDBNull(13)) { OpSeaeHSEntity.CARGOGROSSWT2 = sqlRead.GetDecimal(13); }
                        if (!sqlRead.IsDBNull(14)) { OpSeaeHSEntity.CARGOMEASUREMENT2 = sqlRead.GetDecimal(14); }
                        if (!sqlRead.IsDBNull(15)) { OpSeaeHSEntity.KGS = sqlRead.GetDecimal(15); }
                        if (!sqlRead.IsDBNull(16)) { OpSeaeHSEntity.CBM = sqlRead.GetDecimal(16); }
                        if (!sqlRead.IsDBNull(17)) { OpSeaeHSEntity.CONSIGNMENTNO = sqlRead.GetString(17); }
                        if (!sqlRead.IsDBNull(18)) { OpSeaeHSEntity.COUNTRYCODE = sqlRead.GetString(18); }
                        if (!sqlRead.IsDBNull(19)) { OpSeaeHSEntity.REMARK = sqlRead.GetString(19); }
                        if (!sqlRead.IsDBNull(20)) { OpSeaeHSEntity.CREATEUSER = sqlRead.GetString(20); }
                        if (!sqlRead.IsDBNull(21)) { OpSeaeHSEntity.CREATETIME = sqlRead.GetDateTime(21); }
                        if (!sqlRead.IsDBNull(22)) { OpSeaeHSEntity.MODIFIEDUSER = sqlRead.GetString(22); }
                        if (!sqlRead.IsDBNull(23)) { OpSeaeHSEntity.MODIFIEDTIME = sqlRead.GetDateTime(23); }
                        OpSeaeHSEntites.Add(OpSeaeHSEntity);
                    }
                }
                catch (Exception execError)
                {
                    throw execError;
                }
            }
            return OpSeaeHSEntites;
        }
        #endregion

        #region 根据业务状态信息GID获取业务状态信息信息
        /// <summary>
        /// 根据业务状态信息GID获取业务状态信息信息
        /// </summary>
        /// <param name="strGid"></param>
        /// <returns></returns>
        public OpSeaeHSEntity GetOpSeaeHSByID(string strGid)
        {
            OpSeaeHSEntity OpSeaeHSEntity = null;

            SqlParameter parm = new SqlParameter(PARM_OpSeaeHS_GID, SqlDbType.VarChar, 36);
            parm.Value = strGid;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_OpSeaeHS_BYGID, parm))
            {
                try
                {
                    while (sqlRead.Read())
                    {
                        OpSeaeHSEntity = new OpSeaeHSEntity();
                        if (!sqlRead.IsDBNull(0)) { OpSeaeHSEntity.GID = sqlRead.GetString(0); }
                        if (!sqlRead.IsDBNull(1)) { OpSeaeHSEntity.BSNO = sqlRead.GetString(1); }
                        if (!sqlRead.IsDBNull(2)) { OpSeaeHSEntity.CNTRNO = sqlRead.GetString(2); }
                        if (!sqlRead.IsDBNull(3)) { OpSeaeHSEntity.CARGOCODE = sqlRead.GetString(3); }
                        if (!sqlRead.IsDBNull(4)) { OpSeaeHSEntity.CARGOID = sqlRead.GetString(4); }
                        if (!sqlRead.IsDBNull(5)) { OpSeaeHSEntity.PKGS1 = sqlRead.GetInt32(5); }
                        if (!sqlRead.IsDBNull(6)) { OpSeaeHSEntity.KINDPKGS1 = sqlRead.GetString(6); }
                        if (!sqlRead.IsDBNull(7)) { OpSeaeHSEntity.NOPKGS1 = sqlRead.GetString(7); }
                        if (!sqlRead.IsDBNull(8)) { OpSeaeHSEntity.CARGOGROSSWT1 = sqlRead.GetDecimal(8); }
                        if (!sqlRead.IsDBNull(9)) { OpSeaeHSEntity.CARGOMEASUREMENT1 = sqlRead.GetDecimal(9); }
                        if (!sqlRead.IsDBNull(10)) { OpSeaeHSEntity.PKGS2 = sqlRead.GetInt32(10); }
                        if (!sqlRead.IsDBNull(11)) { OpSeaeHSEntity.KINDPKGS2 = sqlRead.GetString(11); }
                        if (!sqlRead.IsDBNull(12)) { OpSeaeHSEntity.NOPKGS2 = sqlRead.GetString(12); }
                        if (!sqlRead.IsDBNull(13)) { OpSeaeHSEntity.CARGOGROSSWT2 = sqlRead.GetDecimal(13); }
                        if (!sqlRead.IsDBNull(14)) { OpSeaeHSEntity.CARGOMEASUREMENT2 = sqlRead.GetDecimal(14); }
                        if (!sqlRead.IsDBNull(15)) { OpSeaeHSEntity.KGS = sqlRead.GetDecimal(15); }
                        if (!sqlRead.IsDBNull(16)) { OpSeaeHSEntity.CBM = sqlRead.GetDecimal(16); }
                        if (!sqlRead.IsDBNull(17)) { OpSeaeHSEntity.CONSIGNMENTNO = sqlRead.GetString(17); }
                        if (!sqlRead.IsDBNull(18)) { OpSeaeHSEntity.COUNTRYCODE = sqlRead.GetString(18); }
                        if (!sqlRead.IsDBNull(19)) { OpSeaeHSEntity.REMARK = sqlRead.GetString(19); }
                        if (!sqlRead.IsDBNull(20)) { OpSeaeHSEntity.CREATEUSER = sqlRead.GetString(20); }
                        if (!sqlRead.IsDBNull(21)) { OpSeaeHSEntity.CREATETIME = sqlRead.GetDateTime(21); }
                        if (!sqlRead.IsDBNull(22)) { OpSeaeHSEntity.MODIFIEDUSER = sqlRead.GetString(22); }
                        if (!sqlRead.IsDBNull(23)) { OpSeaeHSEntity.MODIFIEDTIME = sqlRead.GetDateTime(23); }
                    }
                }
                catch (Exception execError)
                {
                    throw execError;
                }
            }
            return OpSeaeHSEntity;
        }
        #endregion

        #region 根据业务状态信息GID获取业务状态信息信息
        /// <summary>
        /// 根据业务状态信息GID获取业务状态信息信息
        /// </summary>
        /// <param name="strGid"></param>
        /// <returns></returns>
        public OpSeaeHSEntity GetOpSeaeHSTop1()
        {
            OpSeaeHSEntity OpSeaeHSEntity = null;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_OpSeaeHS_TOP1, null))
            {
                try
                {
                    while (sqlRead.Read())
                    {
                        OpSeaeHSEntity = new OpSeaeHSEntity();
                        if (!sqlRead.IsDBNull(0)) { OpSeaeHSEntity.GID = sqlRead.GetString(0); }
                        if (!sqlRead.IsDBNull(1)) { OpSeaeHSEntity.BSNO = sqlRead.GetString(1); }
                        if (!sqlRead.IsDBNull(2)) { OpSeaeHSEntity.CNTRNO = sqlRead.GetString(2); }
                        if (!sqlRead.IsDBNull(3)) { OpSeaeHSEntity.CARGOCODE = sqlRead.GetString(3); }
                        if (!sqlRead.IsDBNull(4)) { OpSeaeHSEntity.CARGOID = sqlRead.GetString(4); }
                        if (!sqlRead.IsDBNull(5)) { OpSeaeHSEntity.PKGS1 = sqlRead.GetInt32(5); }
                        if (!sqlRead.IsDBNull(6)) { OpSeaeHSEntity.KINDPKGS1 = sqlRead.GetString(6); }
                        if (!sqlRead.IsDBNull(7)) { OpSeaeHSEntity.NOPKGS1 = sqlRead.GetString(7); }
                        if (!sqlRead.IsDBNull(8)) { OpSeaeHSEntity.CARGOGROSSWT1 = sqlRead.GetDecimal(8); }
                        if (!sqlRead.IsDBNull(9)) { OpSeaeHSEntity.CARGOMEASUREMENT1 = sqlRead.GetDecimal(9); }
                        if (!sqlRead.IsDBNull(10)) { OpSeaeHSEntity.PKGS2 = sqlRead.GetInt32(10); }
                        if (!sqlRead.IsDBNull(11)) { OpSeaeHSEntity.KINDPKGS2 = sqlRead.GetString(11); }
                        if (!sqlRead.IsDBNull(12)) { OpSeaeHSEntity.NOPKGS2 = sqlRead.GetString(12); }
                        if (!sqlRead.IsDBNull(13)) { OpSeaeHSEntity.CARGOGROSSWT2 = sqlRead.GetDecimal(13); }
                        if (!sqlRead.IsDBNull(14)) { OpSeaeHSEntity.CARGOMEASUREMENT2 = sqlRead.GetDecimal(14); }
                        if (!sqlRead.IsDBNull(15)) { OpSeaeHSEntity.KGS = sqlRead.GetDecimal(15); }
                        if (!sqlRead.IsDBNull(16)) { OpSeaeHSEntity.CBM = sqlRead.GetDecimal(16); }
                        if (!sqlRead.IsDBNull(17)) { OpSeaeHSEntity.CONSIGNMENTNO = sqlRead.GetString(17); }
                        if (!sqlRead.IsDBNull(18)) { OpSeaeHSEntity.COUNTRYCODE = sqlRead.GetString(18); }
                        if (!sqlRead.IsDBNull(19)) { OpSeaeHSEntity.REMARK = sqlRead.GetString(19); }
                        if (!sqlRead.IsDBNull(20)) { OpSeaeHSEntity.CREATEUSER = sqlRead.GetString(20); }
                        if (!sqlRead.IsDBNull(21)) { OpSeaeHSEntity.CREATETIME = sqlRead.GetDateTime(21); }
                        if (!sqlRead.IsDBNull(22)) { OpSeaeHSEntity.MODIFIEDUSER = sqlRead.GetString(22); }
                        if (!sqlRead.IsDBNull(23)) { OpSeaeHSEntity.MODIFIEDTIME = sqlRead.GetDateTime(23); }
                    }
                }
                catch (Exception execError)
                {
                    throw execError;
                }
            }
            return OpSeaeHSEntity;
        }
        #endregion

        /// <summary>
        /// 插入信息
        /// </summary>
        /// <param name="OpSeaeHSEntity">实体类</param>
        /// <returns>值为1插入数据正常,-1操作异常</returns>
        public int InserInfo(OpSeaeHSEntity infoEntity)
        {
            int iResult = 0;
            //获取参数
            SqlParameter[] parms = GetInsertParms();
            parms[0].Value = infoEntity.GID;//	惟一编号
            parms[1].Value = infoEntity.BSNO;//	业务编号
            parms[2].Value = infoEntity.CNTRNO;//	箱号
            parms[3].Value = infoEntity.CARGOCODE;//	海关HS码
            parms[4].Value = infoEntity.CARGOID;//	货物标识
            parms[5].Value = infoEntity.PKGS1;//	第一层包装件数
            parms[6].Value = infoEntity.KINDPKGS1;//	第一层包装类型
            parms[7].Value = infoEntity.NOPKGS1;//	第一层包装说明
            parms[8].Value = infoEntity.CARGOGROSSWT1;//	第一层包装皮重
            parms[9].Value = infoEntity.CARGOMEASUREMENT1;//	第一层包装尺码
            parms[10].Value = infoEntity.PKGS2;//	第二层包装件数
            parms[11].Value = infoEntity.KINDPKGS2;//	第二层包装类型
            parms[12].Value = infoEntity.NOPKGS2;//	第二层包装说明
            parms[13].Value = infoEntity.CARGOGROSSWT2;//	第二层包装皮重
            parms[14].Value = infoEntity.CARGOMEASUREMENT2;//	第二层包装尺码
            parms[15].Value = infoEntity.KGS;//	货净重
            parms[16].Value = infoEntity.CBM;//	货物体积
            parms[17].Value = infoEntity.CONSIGNMENTNO;//	托运编号
            parms[18].Value = infoEntity.COUNTRYCODE;//	货物原产国代码
            parms[19].Value = infoEntity.REMARK;//	备注
            parms[20].Value = infoEntity.CREATEUSER;//	创建人
            //
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                int existVal = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSqlInsertInfo, parms);
                if (existVal > 0)
                {
                    iResult = 1;
                }
                else
                {
                    iResult = -1;//执行异常
                }
            }
            return iResult;
        }

        /// <summary>
        /// 更新信息
        /// </summary>
        /// <param name="OpSeaeHSEntity">实体类</param>
        /// <returns>值为1更新数据正常,-1操作异常</returns>
        public int UpdateInfo(OpSeaeHSEntity infoEntity)
        {
            int iResult = 0;
            //获取参数
            SqlParameter[] parms = GetUpdateParms(); 
            parms[0].Value = infoEntity.GID;//	惟一编号
            parms[1].Value = infoEntity.BSNO;//	业务编号
            parms[2].Value = infoEntity.CNTRNO;//	箱号
            parms[3].Value = infoEntity.CARGOCODE;//	海关HS码
            parms[4].Value = infoEntity.CARGOID;//	货物标识
            parms[5].Value = infoEntity.PKGS1;//	第一层包装件数
            parms[6].Value = infoEntity.KINDPKGS1;//	第一层包装类型
            parms[7].Value = infoEntity.NOPKGS1;//	第一层包装说明
            parms[8].Value = infoEntity.CARGOGROSSWT1;//	第一层包装皮重
            parms[9].Value = infoEntity.CARGOMEASUREMENT1;//	第一层包装尺码
            parms[10].Value = infoEntity.PKGS2;//	第二层包装件数
            parms[11].Value = infoEntity.KINDPKGS2;//	第二层包装类型
            parms[12].Value = infoEntity.NOPKGS2;//	第二层包装说明
            parms[13].Value = infoEntity.CARGOGROSSWT2;//	第二层包装皮重
            parms[14].Value = infoEntity.CARGOMEASUREMENT2;//	第二层包装尺码
            parms[15].Value = infoEntity.KGS;//	货净重
            parms[16].Value = infoEntity.CBM;//	货物体积
            parms[17].Value = infoEntity.CONSIGNMENTNO;//	托运编号
            parms[18].Value = infoEntity.COUNTRYCODE;//	货物原产国代码
            parms[19].Value = infoEntity.REMARK;//	备注
            parms[20].Value = infoEntity.MODIFIEDUSER;//	最后一次更新操作人
            parms[21].Value = infoEntity.MODIFIEDTIME;//	最后一次更新操作时间
            //
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                int existVal = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSqlUpdateInfo, parms);
                if (existVal > 0)
                {
                    iResult = 1;
                }
                else
                {
                    iResult = -1;//执行异常
                }
            }
            return iResult;
        }

        #region 生成插入语句参数
        /// <summary>
        /// 生成插入语句参数
        /// </summary>
        /// <returns>返回SqlParameter数组</returns>
        private SqlParameter[] GetInsertParms()
        {
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@GID",SqlDbType.VarChar,36),//惟一编号
                new SqlParameter("@BSNO",SqlDbType.VarChar,100),//	业务编号
                new SqlParameter("@CNTRNO",SqlDbType.VarChar,30)	,//	箱号
                new SqlParameter("@CARGOCODE",SqlDbType.VarChar,8)	,//	海关HS码
                new SqlParameter("@CARGOID",SqlDbType.VarChar,1)	,//	货物标识
                new SqlParameter("@PKGS1",SqlDbType.Int),//	第一层包装件数
                new SqlParameter("@KINDPKGS1",SqlDbType.VarChar,20)	,//	第一层包装类型
                new SqlParameter("@NOPKGS1",SqlDbType.VarChar,35)	,//	第一层包装说明
                new SqlParameter("@CARGOGROSSWT1",SqlDbType.Decimal),//	第一层包装皮重
                new SqlParameter("@CARGOMEASUREMENT1",SqlDbType.Decimal)	,//	第一层包装尺码
                new SqlParameter("@PKGS2",SqlDbType.Int),//	第二层包装件数
                new SqlParameter("@KINDPKGS2",SqlDbType.VarChar,20)	,//	第二层包装类型
                new SqlParameter("@NOPKGS2",SqlDbType.VarChar,35)	,//	第二层包装说明
                new SqlParameter("@CARGOGROSSWT2",SqlDbType.Decimal)	,//	第二层包装皮重
                new SqlParameter("@CARGOMEASUREMENT2",SqlDbType.Decimal)	,//	第二层包装尺码
                new SqlParameter("@KGS",SqlDbType.Decimal),//	货净重
                new SqlParameter("@CBM",SqlDbType.Decimal),//	货物体积
                new SqlParameter("@CONSIGNMENTNO",SqlDbType.VarChar,15)	,//	托运编号
                new SqlParameter("@COUNTRYCODE",SqlDbType.VarChar,2)	,//	货物原产国代码
                new SqlParameter("@REMARK",SqlDbType.VarChar,1024)	,//	备注
                new SqlParameter("@CREATEUSER",SqlDbType.VarChar,36)//	创建人
            };
            return parms;
        }
        #endregion

        #region 生成更新语句参数
        /// <summary>
        /// 生成更新语句参数
        /// </summary>
        /// <returns>返回SqlParameter数组</returns>
        private SqlParameter[] GetUpdateParms()
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@GID",SqlDbType.VarChar,36),//惟一编号
                new SqlParameter("@BSNO",SqlDbType.VarChar,100),//	业务编号
                new SqlParameter("@CNTRNO",SqlDbType.VarChar,30)	,//	箱号
                new SqlParameter("@CARGOCODE",SqlDbType.VarChar,8)	,//	海关HS码
                new SqlParameter("@CARGOID",SqlDbType.VarChar,1)	,//	货物标识
                new SqlParameter("@PKGS1",SqlDbType.Int),//	第一层包装件数
                new SqlParameter("@KINDPKGS1",SqlDbType.VarChar,20)	,//	第一层包装类型
                new SqlParameter("@NOPKGS1",SqlDbType.VarChar,35)	,//	第一层包装说明
                new SqlParameter("@CARGOGROSSWT1",SqlDbType.Decimal),//	第一层包装皮重
                new SqlParameter("@CARGOMEASUREMENT1",SqlDbType.Decimal)	,//	第一层包装尺码
                new SqlParameter("@PKGS2",SqlDbType.Int),//	第二层包装件数
                new SqlParameter("@KINDPKGS2",SqlDbType.VarChar,20)	,//	第二层包装类型
                new SqlParameter("@NOPKGS2",SqlDbType.VarChar,35)	,//	第二层包装说明
                new SqlParameter("@CARGOGROSSWT2",SqlDbType.Decimal)	,//	第二层包装皮重
                new SqlParameter("@CARGOMEASUREMENT2",SqlDbType.Decimal)	,//	第二层包装尺码
                new SqlParameter("@KGS",SqlDbType.Decimal),//	货净重
                new SqlParameter("@CBM",SqlDbType.Decimal),//	货物体积
                new SqlParameter("@CONSIGNMENTNO",SqlDbType.VarChar,15)	,//	托运编号
                new SqlParameter("@COUNTRYCODE",SqlDbType.VarChar,2)	,//	货物原产国代码
                new SqlParameter("@REMARK",SqlDbType.VarChar,1024)	,//	备注
                new SqlParameter("@MODIFIEDUSER",SqlDbType.VarChar,36)	,//	最后一次更新操作人
                new SqlParameter("@MODIFIEDTIME",SqlDbType.DateTime)//	最后一次更新操作时间
            };
            return parms;
        }
        #endregion

        #region 返回数据集 业务状态信息表（OpSeaeHS）信息
        /// <summary>
        /// 返回 业务状态信息表（OpSeaeHS）信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetOpSeaeHSSQL(string strSQL)
        {
            string str = "select * from [op_seae_hs] where 1=1 " + strSQL;
            DataSet DS = SqlHelper.OpenSqlDataSet(SqlHelper.ConnectionStringLocalTransaction, str);
            try
            {
                if (DS.Tables[0].Rows.Count <= 0)
                {
                    DS = null;
                }
            }
            catch
            {
                DS = null;
            }
            return DS;
        }
        #endregion

        #region 返回数据集 业务状态信息表（OpSeaeHS）信息
        /// 返回 业务状态信息表（OpSeaeHS）删除
        /// </summary>
        /// <returns></returns>
        /// <summary>
        public String GetOpSeaeHSDel(string strGid, string strUserID)
        {
            string alt = "";
            string str = "delete from op_seae_hs where GID='" + strGid + "'";
            bool bl = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str);
            if (bl == false)
            {
                alt = "账户表（op_seae_hs）删除操作出错！";
                return alt;
            }
            else
            {
                //string str1 = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,STATUS_OPSEAE) values('删除账户表（op_seae_hs）的信息','更新操作','" + str + "','" + strUserID + "')";
                //bool bl1 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str1);
            }
            return alt;
        }
        #endregion
        /// <summary>
        /// 根据SQL语句查询账户数据集
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataSet GetExcuteSql(string strSql)
        {
            DataSet userSet = new DataSet();
            userSet = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql);
            return userSet;
        }
        #region 插入账户列表信息
        /// <summary>
        /// 插入账户列表信息
        /// </summary>
        /// <param name="sqlList">插入SQL语句组,将所有要执行的插入语句写入ArrayList,每个索引对应一条SQL语句,执行时需要遍历操作</param>
        /// <returns>返回状态值 为1表示插入完成;为-1插入出现异常但未正确回滚事务;为-2插入异常,事务已经成功回滚;默认状态为0</returns>
        public int InsertOpSeaeHSFromGrid(ArrayList sqlList)
        {
            int result = 0;
            using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        string strUpdateSql = sqlList[i].ToString();
                        SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strUpdateSql, null);
                    }

                    sqlTran.Commit();
                    result = 1;//状态为1表示插入成功
                }
                catch (Exception execError)
                {
                    result = -1;//有异常,插入失败
                    sqlTran.Rollback();
                    result = -2;//插入异常,事务已回滚成功
                    throw execError;
                }
                finally
                {
                    SqlHelper.CloseConnection();
                }
            }
            return result;
        }
        #endregion
        #region 更新账户列表信息
        /// <summary>
        /// 更新账户列表信息
        /// </summary>
        /// <param name="sqlList">更新SQL语句组,将所有要执行的更新语句写入ArrayList,每个索引对应一条SQL语句,执行时需要遍历操作</param>
        /// <returns>返回状态值 为1表示更新完成;为-1更新出现异常但未正确回滚事务;为-2更新异常,事务已经成功回滚;默认状态为0</returns>
        public int UpdateOpSeaeHSFromGrid(ArrayList sqlList)
        {
            int result = 0;
            using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        string strUpdateSql = sqlList[i].ToString();
                        SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strUpdateSql, null);
                    }

                    sqlTran.Commit();
                    result = 1;//状态为1表示更新成功
                }
                catch (Exception execError)
                {
                    result = -1;//有异常,更新失败
                    sqlTran.Rollback();
                    result = -2;//更新异常,事务已回滚成功
                    throw execError;
                }
                finally
                {
                    SqlHelper.CloseConnection();
                }
            }
            return result;
        }
        #endregion
        #region 通过关联编号BSNO与账户类型获取账户信息
        /// <summary>
        /// 通过关联编号BSNO与账户类型获取账户信息
        /// </summary>
        /// <param name="strGID"></param>
        /// <returns></returns>
        public OpSeaeHSEntity GetOpSeaeHSByBSNOAndType(string strBSNO)
        {
            OpSeaeHSEntity OpSeaeHSEntity = null;

            string strSql = " SELECT top 1 GID,BSNO,CNTRNO,CARGOCODE,CARGOID,PKGS1,KINDPKGS1,NOPKGS1,CARGOGROSSWT1,CARGOMEASUREMENT1,PKGS2,KINDPKGS2,NOPKGS2,CARGOGROSSWT2,CARGOMEASUREMENT2,KGS,CBM,CONSIGNMENTNO,COUNTRYCODE,REMARK,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME FROM op_seae_hs WHERE BSNO = '" + strBSNO + "'";

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql, null))
            {

                try
                {
                    while (sqlRead.Read())
                    {
                        OpSeaeHSEntity = new OpSeaeHSEntity();
                        if (!sqlRead.IsDBNull(0)) { OpSeaeHSEntity.GID = sqlRead.GetString(0); }
                        if (!sqlRead.IsDBNull(1)) { OpSeaeHSEntity.BSNO = sqlRead.GetString(1); }
                        if (!sqlRead.IsDBNull(2)) { OpSeaeHSEntity.CNTRNO = sqlRead.GetString(2); }
                        if (!sqlRead.IsDBNull(3)) { OpSeaeHSEntity.CARGOCODE = sqlRead.GetString(3); }
                        if (!sqlRead.IsDBNull(4)) { OpSeaeHSEntity.CARGOID = sqlRead.GetString(4); }
                        if (!sqlRead.IsDBNull(5)) { OpSeaeHSEntity.PKGS1 = sqlRead.GetInt32(5); }
                        if (!sqlRead.IsDBNull(6)) { OpSeaeHSEntity.KINDPKGS1 = sqlRead.GetString(6); }
                        if (!sqlRead.IsDBNull(7)) { OpSeaeHSEntity.NOPKGS1 = sqlRead.GetString(7); }
                        if (!sqlRead.IsDBNull(8)) { OpSeaeHSEntity.CARGOGROSSWT1 = sqlRead.GetDecimal(8); }
                        if (!sqlRead.IsDBNull(9)) { OpSeaeHSEntity.CARGOMEASUREMENT1 = sqlRead.GetDecimal(9); }
                        if (!sqlRead.IsDBNull(10)) { OpSeaeHSEntity.PKGS2 = sqlRead.GetInt32(10); }
                        if (!sqlRead.IsDBNull(11)) { OpSeaeHSEntity.KINDPKGS2 = sqlRead.GetString(11); }
                        if (!sqlRead.IsDBNull(12)) { OpSeaeHSEntity.NOPKGS2 = sqlRead.GetString(12); }
                        if (!sqlRead.IsDBNull(13)) { OpSeaeHSEntity.CARGOGROSSWT2 = sqlRead.GetDecimal(13); }
                        if (!sqlRead.IsDBNull(14)) { OpSeaeHSEntity.CARGOMEASUREMENT2 = sqlRead.GetDecimal(14); }
                        if (!sqlRead.IsDBNull(15)) { OpSeaeHSEntity.KGS = sqlRead.GetDecimal(15); }
                        if (!sqlRead.IsDBNull(16)) { OpSeaeHSEntity.CBM = sqlRead.GetDecimal(16); }
                        if (!sqlRead.IsDBNull(17)) { OpSeaeHSEntity.CONSIGNMENTNO = sqlRead.GetString(17); }
                        if (!sqlRead.IsDBNull(18)) { OpSeaeHSEntity.COUNTRYCODE = sqlRead.GetString(18); }
                        if (!sqlRead.IsDBNull(19)) { OpSeaeHSEntity.REMARK = sqlRead.GetString(19); }
                        if (!sqlRead.IsDBNull(20)) { OpSeaeHSEntity.CREATEUSER = sqlRead.GetString(20); }
                        if (!sqlRead.IsDBNull(21)) { OpSeaeHSEntity.CREATETIME = sqlRead.GetDateTime(21); }
                        if (!sqlRead.IsDBNull(22)) { OpSeaeHSEntity.MODIFIEDUSER = sqlRead.GetString(22); }
                        if (!sqlRead.IsDBNull(23)) { OpSeaeHSEntity.MODIFIEDTIME = sqlRead.GetDateTime(23); }
                    }
                }
                catch (Exception execError)
                {
                    throw execError;
                }
            }
            return OpSeaeHSEntity;
        }
        #endregion

        #region 通过账户GID删除单条账户
        public int DeleteOpSeaeHSByGid(string tempGid)
        {
            int result = 0;
            SqlParameter parm = new SqlParameter(PARM_OpSeaeHS_GID, SqlDbType.VarChar, 36);
            parm.Value = tempGid;

            using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_DELETE_OpSeaeHS_BY_GID, parm);

                    result = 1;//状态为1表示删除成功
                    sqlTran.Commit();
                }
                catch (Exception execError)
                {
                    result = -1;//有异常,插入失败
                    sqlTran.Rollback();
                    result = -2;//插入异常,事务已回滚成功
                    throw execError;
                }
                finally
                {
                    SqlHelper.CloseConnection();
                }
            }
            return result;
        }
        #endregion
    }
}