using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using DSWeb.DataAccess;
using DSWeb.Models;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace DSWeb.EntityDA
{
public class EBPricequeryDA
{
private const string PARM_MODULE_GID = "@GID";
private const string PARM_MODULE_LINKGID = "@LINKGID";
private const string PARM_MODULE_LEVEL = "@LEVEL";
private const string SQL_SELECT_INFO = "select GID,PODLOAD,PORTDISCHARGE,DESTINATION,CARRIER,FORWARDER,ETD,TT,VIA,POC,SVC,LANE,CARRIERLANE,OWS,VALIDDATE,EFFECTIVEDATE,INPUTBY,INPUTTIME,COMMODITY,SC,NAMEAC,SICUT,CUSCLSG,CYCLSG,[20GP],[40GP],[40HQ],[45HQ],LEVEL,MODIFIEDUSER,MODIFYTIME,LINKGID,REMARK,CARRIERCN,BOOKINGFEE,CTNFEE,THCFEE,FILEFEE,LCLPRICE,CISF,REMARK2,TYPE,CORPID,[40RH],PIRICEVIAPORT,[20RS],[40NOR] from eb_pricequery where GID=@GID";
private const string SQL_SELECT_INFO_LEVEL = "select GID,PODLOAD,PORTDISCHARGE,DESTINATION,CARRIER,FORWARDER,ETD,TT,VIA,POC,SVC,LANE,CARRIERLANE,OWS,VALIDDATE,EFFECTIVEDATE,INPUTBY,INPUTTIME,COMMODITY,SC,NAMEAC,SICUT,CUSCLSG,CYCLSG,[20GP],[40GP],[40HQ],[45HQ],LEVEL,MODIFIEDUSER,MODIFYTIME,LINKGID,REMARK,CARRIERCN,BOOKINGFEE,CTNFEE,THCFEE,FILEFEE,LCLPRICE,CISF,REMARK2,TYPE,CORPID,[40RH],PIRICEVIAPORT,[20RS],[40NOR] from eb_pricequery where LEVEL=@LEVEL and TYPE=@TYPE";
private const string SQL_SELECT_INFO_ALL = "select GID,PODLOAD,PORTDISCHARGE,DESTINATION,CARRIER,FORWARDER,ETD,TT,VIA,POC,SVC,LANE,CARRIERLANE,OWS,VALIDDATE,EFFECTIVEDATE,INPUTBY,INPUTTIME,COMMODITY,SC,NAMEAC,SICUT,CUSCLSG,CYCLSG,[20GP],[40GP],[40HQ],[45HQ],LEVEL,MODIFIEDUSER,MODIFYTIME,LINKGID,REMARK,CARRIERCN,BOOKINGFEE,CTNFEE,THCFEE,FILEFEE,LCLPRICE,CISF,REMARK2,TYPE,CORPID,[40RH],PIRICEVIAPORT,[20RS],[40NOR] from eb_pricequery where LINKGID=@LINKGID and TYPE=@TYPE";
private const string SQL_DELETE_INFO = "DELETE FROM eb_pricequery WHERE GID=@GID";
private string SQL_INSERT_INFO = "insert into eb_pricequery(GID,PODLOAD,PORTDISCHARGE,DESTINATION,CARRIER,FORWARDER,ETD,TT,VIA,POC,SVC,LANE,CARRIERLANE,OWS,{0}{2}INPUTBY,COMMODITY,SC,NAMEAC,SICUT,CUSCLSG,CYCLSG,[20GP],[40GP],[40HQ],[45HQ],LEVEL,MODIFIEDUSER,LINKGID,REMARK,CARRIERCN,BOOKINGFEE,CTNFEE,THCFEE,FILEFEE,LCLPRICE,CISF,REMARK2,TYPE,CORPID,[40RH],PIRICEVIAPORT,[20RS],[40NOR]) "
+ " values(@GID,@PODLOAD,@PORTDISCHARGE,@DESTINATION,@CARRIER,@FORWARDER,@ETD,@TT,@VIA,@POC,@SVC,@LANE,@CARRIERLANE,@OWS,{1}{3}@INPUTBY,@COMMODITY,@SC,@NAMEAC,@SICUT,@CUSCLSG,@CYCLSG,@D_20GP,@D_40GP,@D_40HQ,@D_45HQ,@LEVEL,@MODIFIEDUSER,@LINKGID,@REMARK,@CARRIERCN,@BOOKINGFEE,@CTNFEE,@THCFEE,@FILEFEE,@LCLPRICE,@CISF,@REMARK2,@TYPE,@CORPID,@D_40RH,@PIRICEVIAPORT,@D_20RS,@D_40NOR)";
private string SQL_UPDATE_INFO = "update eb_pricequery set PODLOAD=@PODLOAD,PORTDISCHARGE=@PORTDISCHARGE,DESTINATION=@DESTINATION,CARRIER=@CARRIER,FORWARDER=@FORWARDER,ETD=@ETD,TT=@TT,VIA=@VIA,POC=@POC,SVC=@SVC,LANE=@LANE,CARRIERLANE=@CARRIERLANE,OWS=@OWS,{0}{1}{2}{3}MODIFIEDUSER=@MODIFIEDUSER,COMMODITY=@COMMODITY,SC=@SC,NAMEAC=@NAMEAC,SICUT=@SICUT,CUSCLSG=@CUSCLSG,CYCLSG=@CYCLSG,[20GP]=@D_20GP,[40GP]=@D_40GP,[40HQ]=@D_40HQ,[45HQ]=@D_45HQ,LEVEL=@LEVEL,MODIFYTIME=getdate(),LINKGID=@LINKGID,REMARK=@REMARK,CARRIERCN=@CARRIERCN,BOOKINGFEE=@BOOKINGFEE,CTNFEE=@CTNFEE,THCFEE=@THCFEE,FILEFEE=@FILEFEE,LCLPRICE=@LCLPRICE,CISF=@CISF,REMARK2=@REMARK2,[40RH]=@D_40RH,[PIRICEVIAPORT]=@PIRICEVIAPORT,[20RS]=@D_20RS,[40NOR]=@D_40NOR where GID=@GID ";
public EBPricequeryDA()
{
}
#region 根据GID获取信息
///
/// 根据GID获取信息
///
///
///
public EBPricequeryEntity GetID(string strGID)
{
//初始化返回变量
EBPricequeryEntity EBPricequeryEntity = null;
//初始化参数并赋值
SqlParameter parm = new SqlParameter(PARM_MODULE_GID, SqlDbType.VarChar, 100);
parm.Value = strGID;
using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_INFO, parm))
{
try
{
EBPricequeryEntity = new EBPricequeryEntity();
//读取字段值
while (sqlRead.Read())
{
if (!sqlRead.IsDBNull(0)) { EBPricequeryEntity.GID = sqlRead.GetString(0); }// 惟一编号
if (!sqlRead.IsDBNull(1)) { EBPricequeryEntity.PODLOAD = sqlRead.GetString(1); }// 起运港
if (!sqlRead.IsDBNull(2)) { EBPricequeryEntity.PORTDISCHARGE = sqlRead.GetString(2); }// 卸货港
if (!sqlRead.IsDBNull(3)) { EBPricequeryEntity.DESTINATION = sqlRead.GetString(3); }// 目的地
if (!sqlRead.IsDBNull(4)) { EBPricequeryEntity.CARRIER = sqlRead.GetString(4); }// 船公司
if (!sqlRead.IsDBNull(5)) { EBPricequeryEntity.FORWARDER = sqlRead.GetString(5); }// 订舱代理
if (!sqlRead.IsDBNull(6)) { EBPricequeryEntity.ETD = sqlRead.GetString(6); }// 开航日
if (!sqlRead.IsDBNull(7)) { EBPricequeryEntity.TT = sqlRead.GetString(7); }// 航程
if (!sqlRead.IsDBNull(8)) { EBPricequeryEntity.VIA = sqlRead.GetString(8); }// 中转港
if (!sqlRead.IsDBNull(9)) { EBPricequeryEntity.POC = sqlRead.GetString(9); }// 挂靠港
if (!sqlRead.IsDBNull(10)) { EBPricequeryEntity.SVC = sqlRead.GetString(10); }// 服务
if (!sqlRead.IsDBNull(11)) { EBPricequeryEntity.LANE = sqlRead.GetString(11); }// 航线
if (!sqlRead.IsDBNull(12)) { EBPricequeryEntity.CARRIERLANE = sqlRead.GetString(12); }// 船公司航线代码
if (!sqlRead.IsDBNull(13)) { EBPricequeryEntity.OWS = sqlRead.GetString(13); }// 超重费
if (!sqlRead.IsDBNull(14)) { EBPricequeryEntity.VALIDDATE = sqlRead.GetDateTime(14); }// 有效期
if (!sqlRead.IsDBNull(15)) { EBPricequeryEntity.EFFECTIVEDATE = sqlRead.GetDateTime(15); }// 生效期
if (!sqlRead.IsDBNull(16)) { EBPricequeryEntity.INPUTBY = sqlRead.GetString(16); }// 录入人
if (!sqlRead.IsDBNull(17)) { EBPricequeryEntity.INPUTTIME = sqlRead.GetDateTime(17); }// 录入日期
if (!sqlRead.IsDBNull(18)) { EBPricequeryEntity.COMMODITY = sqlRead.GetString(18); }// 品名
if (!sqlRead.IsDBNull(19)) { EBPricequeryEntity.SC = sqlRead.GetString(19); }// 合约方
if (!sqlRead.IsDBNull(20)) { EBPricequeryEntity.NAMEAC = sqlRead.GetString(20); }// 合约内容
if (!sqlRead.IsDBNull(21)) { EBPricequeryEntity.SICUT = sqlRead.GetString(21); }// AMS时间
if (!sqlRead.IsDBNull(22)) { EBPricequeryEntity.CUSCLSG = sqlRead.GetString(22); }// 截关时间
if (!sqlRead.IsDBNull(23)) { EBPricequeryEntity.CYCLSG = sqlRead.GetString(23); }// 操作时间
if (!sqlRead.IsDBNull(24)) { EBPricequeryEntity.D_20GP = sqlRead.GetDecimal(24); }// 售价
if (!sqlRead.IsDBNull(25)) { EBPricequeryEntity.D_40GP = sqlRead.GetDecimal(25); }//
if (!sqlRead.IsDBNull(26)) { EBPricequeryEntity.D_40HQ = sqlRead.GetDecimal(26); }//
if (!sqlRead.IsDBNull(27)) { EBPricequeryEntity.D_45HQ = sqlRead.GetDecimal(27); }//
if (!sqlRead.IsDBNull(28)) { EBPricequeryEntity.LEVEL = sqlRead.GetString(28); }// 运价级别
if (!sqlRead.IsDBNull(29)) { EBPricequeryEntity.MODIFIEDUSER = sqlRead.GetString(29); }//
if (!sqlRead.IsDBNull(30)) { EBPricequeryEntity.MODIFYTIME = sqlRead.GetDateTime(30); }//
if (!sqlRead.IsDBNull(31)) { EBPricequeryEntity.LINKGID = sqlRead.GetString(31); }//
if (!sqlRead.IsDBNull(32)) { EBPricequeryEntity.REMARK = sqlRead.GetString(32); }//
if (!sqlRead.IsDBNull(33)) { EBPricequeryEntity.CARRIERCN = sqlRead.GetString(33); }//
if (!sqlRead.IsDBNull(34)) { EBPricequeryEntity.BOOKINGFEE = sqlRead.GetString(34); }//
if (!sqlRead.IsDBNull(35)) { EBPricequeryEntity.CTNFEE = sqlRead.GetString(35); }//
if (!sqlRead.IsDBNull(36)) { EBPricequeryEntity.THCFEE = sqlRead.GetString(36); }//
if (!sqlRead.IsDBNull(37)) { EBPricequeryEntity.FILEFEE = sqlRead.GetString(37); }//
if (!sqlRead.IsDBNull(38)) { EBPricequeryEntity.LCLPRICE = sqlRead.GetString(38); }//
if (!sqlRead.IsDBNull(39)) { EBPricequeryEntity.CISF = sqlRead.GetString(39); }//
if (!sqlRead.IsDBNull(40)) { EBPricequeryEntity.REMARK2 = sqlRead.GetString(40); }//
if (!sqlRead.IsDBNull(41)) { EBPricequeryEntity.TYPE = sqlRead.GetString(41); }//
if (!sqlRead.IsDBNull(42)) { EBPricequeryEntity.CORPID = sqlRead.GetString(42); }//
if (!sqlRead.IsDBNull(43)) { EBPricequeryEntity.D_40RH = sqlRead.GetDecimal(43); }//
if (!sqlRead.IsDBNull(44)) { EBPricequeryEntity.PIRICEVIAPORT = sqlRead.GetString(44); }
if (!sqlRead.IsDBNull(45)) { EBPricequeryEntity.D_20RS = sqlRead.GetDecimal(45); }
if (!sqlRead.IsDBNull(46)) { EBPricequeryEntity.D_40NOR = sqlRead.GetDecimal(46); }
}
}
catch (Exception exceError)
{
//抛出异常
throw exceError;
}
}
return EBPricequeryEntity;
}
#endregion
#region 根据运价查询LEVEL获取集装箱信息
///
/// 根据运价查询LEVEL获取集装箱信息
///
///
///
public IList GetEBPricequeryByLEVEL(string tempOrdno,string strTYPE)
{
IList EBPricequeryEntities = new List();
//初始化参数并赋值
SqlParameter[] parms = new SqlParameter[]
{
new SqlParameter(PARM_MODULE_LINKGID, SqlDbType.VarChar, 36),
new SqlParameter("@TYPE",SqlDbType.VarChar,3)//
};
parms[0].Value = tempOrdno;//卸货港
parms[1].Value = strTYPE;//
using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_INFO_LEVEL, parms))
{
try
{
while (sqlRead.Read())
{
EBPricequeryEntity EBPricequeryEntity = new EBPricequeryEntity();
if (!sqlRead.IsDBNull(0)) { EBPricequeryEntity.GID = sqlRead.GetString(0); }// 惟一编号
if (!sqlRead.IsDBNull(1)) { EBPricequeryEntity.PODLOAD = sqlRead.GetString(1); }// 起运港
if (!sqlRead.IsDBNull(2)) { EBPricequeryEntity.PORTDISCHARGE = sqlRead.GetString(2); }// 卸货港
if (!sqlRead.IsDBNull(3)) { EBPricequeryEntity.DESTINATION = sqlRead.GetString(3); }// 目的地
if (!sqlRead.IsDBNull(4)) { EBPricequeryEntity.CARRIER = sqlRead.GetString(4); }// 船公司
if (!sqlRead.IsDBNull(5)) { EBPricequeryEntity.FORWARDER = sqlRead.GetString(5); }// 订舱代理
if (!sqlRead.IsDBNull(6)) { EBPricequeryEntity.ETD = sqlRead.GetString(6); }// 开航日
if (!sqlRead.IsDBNull(7)) { EBPricequeryEntity.TT = sqlRead.GetString(7); }// 航程
if (!sqlRead.IsDBNull(8)) { EBPricequeryEntity.VIA = sqlRead.GetString(8); }// 中转港
if (!sqlRead.IsDBNull(9)) { EBPricequeryEntity.POC = sqlRead.GetString(9); }// 挂靠港
if (!sqlRead.IsDBNull(10)) { EBPricequeryEntity.SVC = sqlRead.GetString(10); }// 服务
if (!sqlRead.IsDBNull(11)) { EBPricequeryEntity.LANE = sqlRead.GetString(11); }// 航线
if (!sqlRead.IsDBNull(12)) { EBPricequeryEntity.CARRIERLANE = sqlRead.GetString(12); }// 船公司航线代码
if (!sqlRead.IsDBNull(13)) { EBPricequeryEntity.OWS = sqlRead.GetString(13); }// 超重费
if (!sqlRead.IsDBNull(14)) { EBPricequeryEntity.VALIDDATE = sqlRead.GetDateTime(14); }// 有效期
if (!sqlRead.IsDBNull(15)) { EBPricequeryEntity.EFFECTIVEDATE = sqlRead.GetDateTime(15); }// 生效期
if (!sqlRead.IsDBNull(16)) { EBPricequeryEntity.INPUTBY = sqlRead.GetString(16); }// 录入人
if (!sqlRead.IsDBNull(17)) { EBPricequeryEntity.INPUTTIME = sqlRead.GetDateTime(17); }// 录入日期
if (!sqlRead.IsDBNull(18)) { EBPricequeryEntity.COMMODITY = sqlRead.GetString(18); }// 品名
if (!sqlRead.IsDBNull(19)) { EBPricequeryEntity.SC = sqlRead.GetString(19); }// 合约方
if (!sqlRead.IsDBNull(20)) { EBPricequeryEntity.NAMEAC = sqlRead.GetString(20); }// 合约内容
if (!sqlRead.IsDBNull(21)) { EBPricequeryEntity.SICUT = sqlRead.GetString(21); }// AMS时间
if (!sqlRead.IsDBNull(22)) { EBPricequeryEntity.CUSCLSG = sqlRead.GetString(22); }// 截关时间
if (!sqlRead.IsDBNull(23)) { EBPricequeryEntity.CYCLSG = sqlRead.GetString(23); }// 操作时间
if (!sqlRead.IsDBNull(24)) { EBPricequeryEntity.D_20GP = sqlRead.GetDecimal(24); }// 售价
if (!sqlRead.IsDBNull(25)) { EBPricequeryEntity.D_40GP = sqlRead.GetDecimal(25); }//
if (!sqlRead.IsDBNull(26)) { EBPricequeryEntity.D_40HQ = sqlRead.GetDecimal(26); }//
if (!sqlRead.IsDBNull(27)) { EBPricequeryEntity.D_45HQ = sqlRead.GetDecimal(27); }//
if (!sqlRead.IsDBNull(28)) { EBPricequeryEntity.LEVEL = sqlRead.GetString(28); }// 运价级别
if (!sqlRead.IsDBNull(29)) { EBPricequeryEntity.MODIFIEDUSER = sqlRead.GetString(29); }//
if (!sqlRead.IsDBNull(30)) { EBPricequeryEntity.MODIFYTIME = sqlRead.GetDateTime(30); }//
if (!sqlRead.IsDBNull(31)) { EBPricequeryEntity.LINKGID = sqlRead.GetString(31); }//
if (!sqlRead.IsDBNull(32)) { EBPricequeryEntity.REMARK = sqlRead.GetString(32); }//
if (!sqlRead.IsDBNull(33)) { EBPricequeryEntity.CARRIERCN = sqlRead.GetString(33); }//
if (!sqlRead.IsDBNull(34)) { EBPricequeryEntity.BOOKINGFEE = sqlRead.GetString(34); }//
if (!sqlRead.IsDBNull(35)) { EBPricequeryEntity.CTNFEE = sqlRead.GetString(35); }//
if (!sqlRead.IsDBNull(36)) { EBPricequeryEntity.THCFEE = sqlRead.GetString(36); }//
if (!sqlRead.IsDBNull(37)) { EBPricequeryEntity.FILEFEE = sqlRead.GetString(37); }//
if (!sqlRead.IsDBNull(38)) { EBPricequeryEntity.LCLPRICE = sqlRead.GetString(38); }//
if (!sqlRead.IsDBNull(39)) { EBPricequeryEntity.CISF = sqlRead.GetString(39); }//
if (!sqlRead.IsDBNull(40)) { EBPricequeryEntity.REMARK2 = sqlRead.GetString(40); }//
if (!sqlRead.IsDBNull(41)) { EBPricequeryEntity.TYPE = sqlRead.GetString(41); }//
if (!sqlRead.IsDBNull(42)) { EBPricequeryEntity.CORPID = sqlRead.GetString(42); }//
if (!sqlRead.IsDBNull(43)) { EBPricequeryEntity.D_40RH = sqlRead.GetDecimal(43); }//
if (!sqlRead.IsDBNull(44)) { EBPricequeryEntity.PIRICEVIAPORT = sqlRead.GetString(44); }
if (!sqlRead.IsDBNull(45)) { EBPricequeryEntity.D_20RS = sqlRead.GetDecimal(45); }
if (!sqlRead.IsDBNull(46)) { EBPricequeryEntity.D_40NOR = sqlRead.GetDecimal(46); }
EBPricequeryEntities.Add(EBPricequeryEntity);
}
}
catch (Exception execError)
{
throw execError;
}
}
return EBPricequeryEntities;
}
#endregion
#region 根据运价查询ORDNO获取集装箱信息
///
/// 根据运价查询ORDNO获取集装箱信息
///
///
///
public IList GetEBPricequeryByLINKGID(string tempOrdno,string strTYPE)
{
IList EBPricequeryEntities = new List();
SqlParameter parm = new SqlParameter(PARM_MODULE_LINKGID, SqlDbType.VarChar, 36);
parm.Value = tempOrdno;
//初始化参数并赋值
SqlParameter[] parms = new SqlParameter[]
{
new SqlParameter(PARM_MODULE_LINKGID, SqlDbType.VarChar, 36),
new SqlParameter("@TYPE",SqlDbType.VarChar,3)//
};
parms[0].Value = tempOrdno;//卸货港
parms[1].Value = strTYPE;//
using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_INFO_ALL, parms))
{
try
{
while (sqlRead.Read())
{
EBPricequeryEntity EBPricequeryEntity = new EBPricequeryEntity();
if (!sqlRead.IsDBNull(0)) { EBPricequeryEntity.GID = sqlRead.GetString(0); }// 惟一编号
if (!sqlRead.IsDBNull(1)) { EBPricequeryEntity.PODLOAD = sqlRead.GetString(1); }// 起运港
if (!sqlRead.IsDBNull(2)) { EBPricequeryEntity.PORTDISCHARGE = sqlRead.GetString(2); }// 卸货港
if (!sqlRead.IsDBNull(3)) { EBPricequeryEntity.DESTINATION = sqlRead.GetString(3); }// 目的地
if (!sqlRead.IsDBNull(4)) { EBPricequeryEntity.CARRIER = sqlRead.GetString(4); }// 船公司
if (!sqlRead.IsDBNull(5)) { EBPricequeryEntity.FORWARDER = sqlRead.GetString(5); }// 订舱代理
if (!sqlRead.IsDBNull(6)) { EBPricequeryEntity.ETD = sqlRead.GetString(6); }// 开航日
if (!sqlRead.IsDBNull(7)) { EBPricequeryEntity.TT = sqlRead.GetString(7); }// 航程
if (!sqlRead.IsDBNull(8)) { EBPricequeryEntity.VIA = sqlRead.GetString(8); }// 中转港
if (!sqlRead.IsDBNull(9)) { EBPricequeryEntity.POC = sqlRead.GetString(9); }// 挂靠港
if (!sqlRead.IsDBNull(10)) { EBPricequeryEntity.SVC = sqlRead.GetString(10); }// 服务
if (!sqlRead.IsDBNull(11)) { EBPricequeryEntity.LANE = sqlRead.GetString(11); }// 航线
if (!sqlRead.IsDBNull(12)) { EBPricequeryEntity.CARRIERLANE = sqlRead.GetString(12); }// 船公司航线代码
if (!sqlRead.IsDBNull(13)) { EBPricequeryEntity.OWS = sqlRead.GetString(13); }// 超重费
if (!sqlRead.IsDBNull(14)) { EBPricequeryEntity.VALIDDATE = sqlRead.GetDateTime(14); }// 有效期
if (!sqlRead.IsDBNull(15)) { EBPricequeryEntity.EFFECTIVEDATE = sqlRead.GetDateTime(15); }// 生效期
if (!sqlRead.IsDBNull(16)) { EBPricequeryEntity.INPUTBY = sqlRead.GetString(16); }// 录入人
if (!sqlRead.IsDBNull(17)) { EBPricequeryEntity.INPUTTIME = sqlRead.GetDateTime(17); }// 录入日期
if (!sqlRead.IsDBNull(18)) { EBPricequeryEntity.COMMODITY = sqlRead.GetString(18); }// 品名
if (!sqlRead.IsDBNull(19)) { EBPricequeryEntity.SC = sqlRead.GetString(19); }// 合约方
if (!sqlRead.IsDBNull(20)) { EBPricequeryEntity.NAMEAC = sqlRead.GetString(20); }// 合约内容
if (!sqlRead.IsDBNull(21)) { EBPricequeryEntity.SICUT = sqlRead.GetString(21); }// AMS时间
if (!sqlRead.IsDBNull(22)) { EBPricequeryEntity.CUSCLSG = sqlRead.GetString(22); }// 截关时间
if (!sqlRead.IsDBNull(23)) { EBPricequeryEntity.CYCLSG = sqlRead.GetString(23); }// 操作时间
if (!sqlRead.IsDBNull(24)) { EBPricequeryEntity.D_20GP = sqlRead.GetDecimal(24); }// 售价
if (!sqlRead.IsDBNull(25)) { EBPricequeryEntity.D_40GP = sqlRead.GetDecimal(25); }//
if (!sqlRead.IsDBNull(26)) { EBPricequeryEntity.D_40HQ = sqlRead.GetDecimal(26); }//
if (!sqlRead.IsDBNull(27)) { EBPricequeryEntity.D_45HQ = sqlRead.GetDecimal(27); }//
if (!sqlRead.IsDBNull(28)) { EBPricequeryEntity.LEVEL = sqlRead.GetString(28); }// 运价级别
if (!sqlRead.IsDBNull(29)) { EBPricequeryEntity.MODIFIEDUSER = sqlRead.GetString(29); }//
if (!sqlRead.IsDBNull(30)) { EBPricequeryEntity.MODIFYTIME = sqlRead.GetDateTime(30); }//
if (!sqlRead.IsDBNull(31)) { EBPricequeryEntity.LINKGID = sqlRead.GetString(31); }//
if (!sqlRead.IsDBNull(32)) { EBPricequeryEntity.REMARK = sqlRead.GetString(32); }//
if (!sqlRead.IsDBNull(33)) { EBPricequeryEntity.CARRIERCN = sqlRead.GetString(33); }//
if (!sqlRead.IsDBNull(34)) { EBPricequeryEntity.BOOKINGFEE = sqlRead.GetString(34); }//
if (!sqlRead.IsDBNull(35)) { EBPricequeryEntity.CTNFEE = sqlRead.GetString(35); }//
if (!sqlRead.IsDBNull(36)) { EBPricequeryEntity.THCFEE = sqlRead.GetString(36); }//
if (!sqlRead.IsDBNull(37)) { EBPricequeryEntity.FILEFEE = sqlRead.GetString(37); }//
if (!sqlRead.IsDBNull(38)) { EBPricequeryEntity.LCLPRICE = sqlRead.GetString(38); }//
if (!sqlRead.IsDBNull(39)) { EBPricequeryEntity.CISF = sqlRead.GetString(39); }//
if (!sqlRead.IsDBNull(40)) { EBPricequeryEntity.REMARK2 = sqlRead.GetString(40); }//
if (!sqlRead.IsDBNull(41)) { EBPricequeryEntity.TYPE = sqlRead.GetString(41); }//
if (!sqlRead.IsDBNull(42)) { EBPricequeryEntity.CORPID = sqlRead.GetString(42); }//
if (!sqlRead.IsDBNull(43)) { EBPricequeryEntity.D_40RH = sqlRead.GetDecimal(43); }//
if (!sqlRead.IsDBNull(44)) { EBPricequeryEntity.PIRICEVIAPORT = sqlRead.GetString(44); }
if (!sqlRead.IsDBNull(45)) { EBPricequeryEntity.D_20RS = sqlRead.GetDecimal(45); }
if (!sqlRead.IsDBNull(46)) { EBPricequeryEntity.D_40NOR = sqlRead.GetDecimal(46); }
EBPricequeryEntities.Add(EBPricequeryEntity);
}
}
catch (Exception execError)
{
throw execError;
}
}
return EBPricequeryEntities;
}
#endregion
///
/// 插入信息
///
/// 实体类
/// 值为1插入数据正常,-1操作异常
public int InserInfo(EBPricequeryEntity infoEntity)
{
int iResult = 0;
//获取参数
SqlParameter[] parms = GetInsertParms();
parms[0].Value = infoEntity.GID;//惟一编号
parms[1].Value = infoEntity.PODLOAD;//起运港
parms[2].Value = infoEntity.PORTDISCHARGE;//卸货港
parms[3].Value = infoEntity.DESTINATION;//目的地
parms[4].Value = infoEntity.CARRIER;//船公司
parms[5].Value = infoEntity.FORWARDER;//订舱代理
parms[6].Value = infoEntity.ETD;//开航日
parms[7].Value = infoEntity.TT;//航程
parms[8].Value = infoEntity.VIA;//中转港
parms[9].Value = infoEntity.POC;//挂靠港
parms[10].Value = infoEntity.SVC;//服务
parms[11].Value = infoEntity.LANE;//航线
parms[12].Value = infoEntity.CARRIERLANE;//船公司航线代码
parms[13].Value = infoEntity.OWS;//超重费
parms[14].Value = infoEntity.INPUTBY;//录入人
parms[15].Value = infoEntity.COMMODITY;//品名
parms[16].Value = infoEntity.SC;//合约方
parms[17].Value = infoEntity.NAMEAC;//合约内容
parms[18].Value = infoEntity.SICUT;//AMS时间
parms[19].Value = infoEntity.CUSCLSG;//截关时间
parms[20].Value = infoEntity.CYCLSG;//操作时间
parms[21].Value = infoEntity.D_20GP;//一级售价
parms[22].Value = infoEntity.D_40GP;//
parms[23].Value = infoEntity.D_40HQ;//
parms[24].Value = infoEntity.D_45HQ;//
parms[25].Value = infoEntity.LEVEL;//运价级别
parms[26].Value = infoEntity.MODIFIEDUSER;//
parms[27].Value = infoEntity.LINKGID;//
parms[28].Value = infoEntity.REMARK;//
parms[29].Value = infoEntity.CARRIERCN;//
parms[30].Value = infoEntity.BOOKINGFEE;//
parms[31].Value = infoEntity.CTNFEE;//
parms[32].Value = infoEntity.THCFEE;//
parms[33].Value = infoEntity.FILEFEE;//
parms[34].Value = infoEntity.LCLPRICE;//
parms[35].Value = infoEntity.CISF;//
parms[36].Value = infoEntity.REMARK2;//
parms[37].Value = infoEntity.TYPE;//
parms[38].Value = infoEntity.CORPID;//
parms[39].Value = infoEntity.D_40RH;//
parms[40].Value = infoEntity.PIRICEVIAPORT;//
parms[41].Value = infoEntity.D_20RS;//
parms[42].Value = infoEntity.D_40NOR;//
//VALIDDATE;//有效期datetime
//EFFECTIVEDATE;//生效期datetime
//INPUTTIME;//录入日期datetime
//有效期
string strVALIDDATE = infoEntity.VALIDDATE.ToString().IndexOf("0001")>-1 ? "" : "'" + infoEntity.VALIDDATE.ToString() + "',";
string strVALIDDATE_name = strVALIDDATE.Equals("") ? "" : "VALIDDATE,";
//生效期
string strEFFECTIVEDATE = infoEntity.EFFECTIVEDATE.ToString().IndexOf("0001")>-1 ? "" : "'" + infoEntity.EFFECTIVEDATE.ToString() + "',";
string strEFFECTIVEDATE_name = strEFFECTIVEDATE.Equals("") ? "" : "EFFECTIVEDATE,";
SQL_INSERT_INFO = String.Format(SQL_INSERT_INFO, strVALIDDATE_name, strVALIDDATE, strEFFECTIVEDATE_name, strEFFECTIVEDATE);
//
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
{
int existVal = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_INFO, parms);
if (existVal > 0)
{
iResult = 1;
//
string str2 = "update crm_price_carrier set ISISSUE=1 where 1=1 and GID='" + infoEntity.LINKGID.ToString().Trim() + "'";
bool bl2 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str2);
//
//string str0 = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('插入客户运价查询表：eb_pricequery','插入操作','GID=" + infoEntity.GID + "','" + infoEntity.INPUTBY + "')";
//bool bl0 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str0);
}
else
{
iResult = -1;//执行异常
}
}
return iResult;
}
///
/// 更新信息
///
/// 实体类
/// 值为1更新数据正常,-1操作异常
public int UpdateInfo(EBPricequeryEntity infoEntity)
{
int iResult = 0;
//获取参数
SqlParameter[] parms = GetUpdateParms();
parms[0].Value = infoEntity.GID;//惟一编号
parms[1].Value = infoEntity.PODLOAD;//起运港
parms[2].Value = infoEntity.PORTDISCHARGE;//卸货港
parms[3].Value = infoEntity.DESTINATION;//目的地
parms[4].Value = infoEntity.CARRIER;//船公司
parms[5].Value = infoEntity.FORWARDER;//订舱代理
parms[6].Value = infoEntity.ETD;//开航日
parms[7].Value = infoEntity.TT;//航程
parms[8].Value = infoEntity.VIA;//中转港
parms[9].Value = infoEntity.POC;//挂靠港
parms[10].Value = infoEntity.SVC;//服务
parms[11].Value = infoEntity.LANE;//航线
parms[12].Value = infoEntity.CARRIERLANE;//船公司航线代码
parms[13].Value = infoEntity.OWS;//超重费
parms[14].Value = infoEntity.MODIFIEDUSER;//录入人
parms[15].Value = infoEntity.COMMODITY;//品名
parms[16].Value = infoEntity.SC;//合约方
parms[17].Value = infoEntity.NAMEAC;//合约内容
parms[18].Value = infoEntity.SICUT;//AMS时间
parms[19].Value = infoEntity.CUSCLSG;//截关时间
parms[20].Value = infoEntity.CYCLSG;//操作时间
parms[21].Value = infoEntity.D_20GP;//售价
parms[22].Value = infoEntity.D_40GP;//
parms[23].Value = infoEntity.D_40HQ;//
parms[24].Value = infoEntity.D_45HQ;//
parms[25].Value = infoEntity.LEVEL;//运价级别
parms[26].Value = infoEntity.LINKGID;//
parms[27].Value = infoEntity.REMARK;//运价级别
parms[28].Value = infoEntity.CARRIERCN;//
parms[29].Value = infoEntity.BOOKINGFEE;//
parms[30].Value = infoEntity.CTNFEE;//
parms[31].Value = infoEntity.THCFEE;//
parms[32].Value = infoEntity.FILEFEE;//
parms[33].Value = infoEntity.LCLPRICE;//
parms[34].Value = infoEntity.CISF;//
parms[35].Value = infoEntity.REMARK2;//
parms[36].Value = infoEntity.D_40RH;//
parms[37].Value = infoEntity.PIRICEVIAPORT;//
parms[38].Value = infoEntity.D_20RS;//
parms[39].Value = infoEntity.D_40NOR;//
//VALIDDATE;//有效期datetime
//EFFECTIVEDATE;//生效期datetime
//INPUTTIME;//录入日期datetime
//有效期
string strVALIDDATE = infoEntity.VALIDDATE.ToString().IndexOf("0001")>-1 ? "null," : "'" + infoEntity.VALIDDATE.ToString() + "',";
string strVALIDDATE_name = "VALIDDATE=";
//生效期
string strEFFECTIVEDATE = infoEntity.EFFECTIVEDATE.ToString().IndexOf("0001")>-1 ? "null," : "'" + infoEntity.EFFECTIVEDATE.ToString() + "',";
string strEFFECTIVEDATE_name = "EFFECTIVEDATE=";
SQL_UPDATE_INFO = String.Format(SQL_UPDATE_INFO, strVALIDDATE_name, strVALIDDATE, strEFFECTIVEDATE_name, strEFFECTIVEDATE);
//
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
{
int existVal = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_UPDATE_INFO, parms);
if (existVal > 0)
{
iResult = 1;
//
string str2 = "update crm_price_carrier set ISISSUE=1 where 1=1 and GID='" + infoEntity.LINKGID.ToString().Trim() + "'";
bool bl2 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str2);
//
//string str0 = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('更新运价管理表：crm_price_carrier内容','更新操作','GID=" + infoEntity.GID + "','')";
//bool bl0 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str0);
}
else
{
iResult = -1;//执行异常
}
}
return iResult;
}
#region 生成插入语句参数
///
/// 生成插入语句参数
///
/// 返回SqlParameter数组
private SqlParameter[] GetInsertParms()
{
SqlParameter[] parms = new SqlParameter[]{
new SqlParameter("@GID",SqlDbType.VarChar,36),//惟一编号
new SqlParameter("@PODLOAD",SqlDbType.VarChar,60),//起运港
new SqlParameter("@PORTDISCHARGE",SqlDbType.VarChar,60),//卸货港
new SqlParameter("@DESTINATION",SqlDbType.VarChar,60),//目的地
new SqlParameter("@CARRIER",SqlDbType.VarChar,50),//船公司
new SqlParameter("@FORWARDER",SqlDbType.VarChar,20),//订舱代理
new SqlParameter("@ETD",SqlDbType.VarChar,10),//开航日
new SqlParameter("@TT",SqlDbType.VarChar,3),//航程
new SqlParameter("@VIA",SqlDbType.VarChar,60),//中转港
new SqlParameter("@POC",SqlDbType.VarChar,100),//挂靠港
new SqlParameter("@SVC",SqlDbType.VarChar,50),//服务
new SqlParameter("@LANE",SqlDbType.VarChar,50),//航线
new SqlParameter("@CARRIERLANE",SqlDbType.VarChar,50),//船公司航线代码
new SqlParameter("@OWS",SqlDbType.VarChar,200),//超重费
new SqlParameter("@INPUTBY",SqlDbType.VarChar,10),//录入人
new SqlParameter("@COMMODITY",SqlDbType.VarChar,600),//品名
new SqlParameter("@SC",SqlDbType.VarChar,50),//合约方
new SqlParameter("@NAMEAC",SqlDbType.VarChar,100),//合约内容
new SqlParameter("@SICUT",SqlDbType.VarChar,10),//AMS时间
new SqlParameter("@CUSCLSG",SqlDbType.VarChar,10),//截关时间
new SqlParameter("@CYCLSG",SqlDbType.VarChar,10),//操作时间
new SqlParameter("@D_20GP",SqlDbType.Decimal),//一级售价
new SqlParameter("@D_40GP",SqlDbType.Decimal),//
new SqlParameter("@D_40HQ",SqlDbType.Decimal),//
new SqlParameter("@D_45HQ",SqlDbType.Decimal),//
new SqlParameter("@LEVEL",SqlDbType.VarChar,1),//运价级别
new SqlParameter("@MODIFIEDUSER",SqlDbType.VarChar,10),//录入人
new SqlParameter("@LINKGID",SqlDbType.VarChar,36),//
new SqlParameter("@REMARK",SqlDbType.VarChar,1024),//
new SqlParameter("@CARRIERCN",SqlDbType.VarChar,100),//
new SqlParameter("@BOOKINGFEE",SqlDbType.VarChar,50),//
new SqlParameter("@CTNFEE",SqlDbType.VarChar,50),//
new SqlParameter("@THCFEE",SqlDbType.VarChar,50),//
new SqlParameter("@FILEFEE",SqlDbType.VarChar,50),//
new SqlParameter("@LCLPRICE",SqlDbType.VarChar,50),//
new SqlParameter("@CISF",SqlDbType.VarChar,50),//
new SqlParameter("@REMARK2",SqlDbType.VarChar,1024),//
new SqlParameter("@TYPE",SqlDbType.VarChar,3),//
new SqlParameter("@CORPID",SqlDbType.VarChar,36),//
new SqlParameter("@D_40RH",SqlDbType.Decimal),
new SqlParameter("@PIRICEVIAPORT",SqlDbType.VarChar,60),
new SqlParameter("@D_20RS",SqlDbType.Decimal),
new SqlParameter("@D_40NOR",SqlDbType.Decimal)
//VALIDDATEdatetime;//有效期
//EFFECTIVEDATEdatetime;//生效期
//INPUTTIMEdatetime;//录入日期
};
return parms;
}
#endregion
#region 生成更新语句参数
///
/// 生成更新语句参数
///
/// 返回SqlParameter数组
private SqlParameter[] GetUpdateParms()
{
SqlParameter[] parms = new SqlParameter[]{
new SqlParameter("@GID",SqlDbType.VarChar,36),//惟一编号
new SqlParameter("@PODLOAD",SqlDbType.VarChar,60),//起运港
new SqlParameter("@PORTDISCHARGE",SqlDbType.VarChar,60),//卸货港
new SqlParameter("@DESTINATION",SqlDbType.VarChar,60),//目的地
new SqlParameter("@CARRIER",SqlDbType.VarChar,50),//船公司
new SqlParameter("@FORWARDER",SqlDbType.VarChar,20),//订舱代理
new SqlParameter("@ETD",SqlDbType.VarChar,10),//开航日
new SqlParameter("@TT",SqlDbType.VarChar,3),//航程
new SqlParameter("@VIA",SqlDbType.VarChar,60),//中转港
new SqlParameter("@POC",SqlDbType.VarChar,100),//挂靠港
new SqlParameter("@SVC",SqlDbType.VarChar,50),//服务
new SqlParameter("@LANE",SqlDbType.VarChar,50),//航线
new SqlParameter("@CARRIERLANE",SqlDbType.VarChar,50),//船公司航线代码
new SqlParameter("@OWS",SqlDbType.VarChar,200),//超重费
new SqlParameter("@MODIFIEDUSER",SqlDbType.VarChar,10),//录入人
new SqlParameter("@COMMODITY",SqlDbType.VarChar,600),//品名
new SqlParameter("@SC",SqlDbType.VarChar,50),//合约方
new SqlParameter("@NAMEAC",SqlDbType.VarChar,100),//合约内容
new SqlParameter("@SICUT",SqlDbType.VarChar,10),//AMS时间
new SqlParameter("@CUSCLSG",SqlDbType.VarChar,10),//截关时间
new SqlParameter("@CYCLSG",SqlDbType.VarChar,10),//操作时间
new SqlParameter("@D_20GP",SqlDbType.Decimal),//一级售价
new SqlParameter("@D_40GP",SqlDbType.Decimal),//
new SqlParameter("@D_40HQ",SqlDbType.Decimal),//
new SqlParameter("@D_45HQ",SqlDbType.Decimal),//
new SqlParameter("@LEVEL",SqlDbType.VarChar,1),//运价级别
new SqlParameter("@LINKGID",SqlDbType.VarChar,36),//
new SqlParameter("@REMARK",SqlDbType.VarChar,1024),//
new SqlParameter("@CARRIERCN",SqlDbType.VarChar,100),//
new SqlParameter("@BOOKINGFEE",SqlDbType.VarChar,50),//
new SqlParameter("@CTNFEE",SqlDbType.VarChar,50),//
new SqlParameter("@THCFEE",SqlDbType.VarChar,50),//
new SqlParameter("@FILEFEE",SqlDbType.VarChar,50),//
new SqlParameter("@LCLPRICE",SqlDbType.VarChar,50),//
new SqlParameter("@CISF",SqlDbType.VarChar,50),//
new SqlParameter("@REMARK2",SqlDbType.VarChar,1024),//
new SqlParameter("@D_40RH",SqlDbType.Decimal),
new SqlParameter("@PIRICEVIAPORT",SqlDbType.VarChar,60),
new SqlParameter("@D_20RS",SqlDbType.Decimal),
new SqlParameter("@D_40NOR",SqlDbType.Decimal)
//VALIDDATEdatetime;//有效期
//EFFECTIVEDATEdatetime;//生效期
//INPUTTIMEdatetime;//录入日期
};
return parms;
}
///
/// 更新信息
///
/// 实体类
/// 值为1更新数据正常,-1操作异常
public int UpdateInfoCNTRTOTAL(string strSql, string strUserID)
{
int iResult = 0;
//
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
{
int existVal = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql, null);
if (existVal > 0)
{
iResult = 1;
//
//strSql = strSql.Trim().Replace("'", "''");
//string str0 = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('更新所有crm_price_carrier海运业务预订舱表集装箱字段','更新操作','" + strSql + "','" + strUserID + "')";
//bool bl0 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str0);
}
else
{
iResult = -1;//执行异常
}
}
return iResult;
}
#endregion
#region 删除委托信息
///
/// 删除运价查询信息
///
/// 运价查询信息GID
/// 删除人ID
/// 值1表示删除成功 值-1表示有异常，删除失败 值-2表示插入异常,事务已回滚成功
public String GetEBPricequeryDel(string strSel, string strUserID, string strTYPE, string strCORPID)
{
string alt = "";
string str = "delete from eb_pricequery where TYPE='" + strTYPE + "' and (1=0 " + strSel + ") and CORPID='" + strCORPID + "'";
bool bl = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str);
if (bl == false)
{
alt = "客户运价查询eb_pricequery删除操作出错！";
return alt;
}
else
{
strSel = strSel.Trim().Replace("LINKGID", "GID");
string str2 = "update crm_price_carrier set ISISSUE=0 where TYPE='" + strTYPE + "' and (1=0 " + strSel + ") and CORPID='" + strCORPID + "'";
bool bl2 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str2);
//
//str = str.Trim().Replace("'", "''");
//string str0 = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('删除客户运价查询eb_pricequery数据','删除操作','" + str + "','" + strUserID + "')";
//bool bl0 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str0);
}
return alt;
}
#endregion
#region 删除运价查询信息
///
/// 删除运价查询信息
///
/// 运价查询信息GID
/// 删除人ID
/// 值1表示删除成功 值-1表示有异常，删除失败 值-2表示插入异常,事务已回滚成功
public String GetEBPricequeryDelALL(string strUserID, string strTYPE, string strCORPID, string hid_sel)
{
string alt = "";
string str = "delete from eb_pricequery where TYPE='" + strTYPE + "' and LINKGID in(select gid from crm_price_carrier where TYPE='" + strTYPE + "' " + hid_sel + ")";// and CORPID='" + strCORPID + "'
bool bl = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str);
if (bl == false)
{
alt = "客户运价查询eb_pricequery删除操作出错！";
return alt;
}
else
{
string str2 = "update crm_price_carrier set ISISSUE=0 where TYPE='" + strTYPE + "' " + hid_sel;// and CORPID='" + strCORPID + "'";
bool bl2 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str2);
//
//str = str.Trim().Replace("'", "''");
//string str0 = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('删除客户运价查询eb_pricequery数据','删除操作','" + str + "','" + strUserID + "')";
//bool bl0 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str0);
}
return alt;
}
#endregion
#region 全部发布
///
/// 全部发布
///
/// 运价查询信息GID
/// 删除人ID
/// 值1表示删除成功 值-1表示有异常，删除失败 值-2表示插入异常,事务已回滚成功
public int GetISSUEALL(string strUserID, string strCORPID, string hid_sel)
{
int iResult = 0;
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
T_ALL_DA T_ALL_DA = new T_ALL_DA();
string sSHOWNAME = T_ALL_DA.GetStrSQL("SHOWNAME", "select SHOWNAME from [user] where GID ='" + strUserID + "'");
string str = "delete from eb_pricequery where TYPE='FCL' and LINKGID in (select gid from crm_price_carrier where LOCKFLAG=1 and TYPE='FCL' " + hid_sel + ")";// and CORPID='" + strCORPID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, str, null);
//
str = "update crm_price_carrier set ISISSUE=1 where LOCKFLAG=1 and TYPE='FCL' " + hid_sel;//and CORPID='" + strCORPID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, str, null);
//
str = "insert into eb_pricequery(GID,PODLOAD,PORTDISCHARGE,DESTINATION,CARRIER,FORWARDER,ETD,TT,VIA,POC,SVC,LANE,CARRIERLANE,OWS,VALIDDATE,EFFECTIVEDATE,INPUTBY,COMMODITY,SC,NAMEAC,SICUT,CUSCLSG,CYCLSG,[20GP],[40GP],[40HQ],[45HQ],LEVEL,MODIFIEDUSER,LINKGID,REMARK,CARRIERCN,BOOKINGFEE,CTNFEE,THCFEE,FILEFEE,LCLPRICE,CISF,REMARK2,TYPE,CORPID,[40RH],PIRICEVIAPORT,[20RS],[40NOR]) "
+ " select newid(),PODLOAD,PORTDISCHARGE,DESTINATION,CARRIER,FORWARDER,ETD,TT,VIA,POC,SVC,LANE,CARRIERLANE,OWS,VALIDDATE,EFFECTIVEDATE,'" + sSHOWNAME + "',COMMODITY,SC,NAMEAC,SICUT,CUSCLSG,CYCLSG,[20GP1],[40GP1],[40HQ1],[45HQ1],'1','" + sSHOWNAME + "',GID,REMARK,CARRIERCN,BOOKINGFEE,CTNFEE,THCFEE,FILEFEE,LCLPRICE,CISF,REMARK2,TYPE,CORPID,[40RH1],PIRICEVIAPORT,[20RS1],[40NOR1] from crm_price_carrier where LOCKFLAG=1 and TYPE='FCL' " + hid_sel;//and CORPID='" + st`rCORPID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, str, null);
//事务提交
sqlTran.Commit();
iResult = 1;//状态为1表示插入成功
}
catch (Exception execError)
{
iResult = -1;//有异常,插入失败
sqlTran.Rollback();
iResult = -2;//插入异常,事务已回滚成功
throw execError;
}
finally
{
SqlHelper.CloseConnection();
}
}
return iResult;
}
#endregion
#region 全部发布LCL
///
/// 全部发布LCL
///
/// 运价查询信息GID
/// 删除人ID
/// 值1表示删除成功 值-1表示有异常，删除失败 值-2表示插入异常,事务已回滚成功
public int GetISSUEALLLCL(string strUserID, string strCORPID, string hid_sel)
{
int iResult = 0;
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
T_ALL_DA T_ALL_DA = new T_ALL_DA();
string sSHOWNAME = T_ALL_DA.GetStrSQL("SHOWNAME", "select SHOWNAME from [user] where GID ='" + strUserID + "'");
string str = "delete from eb_pricequery where TYPE='LCL' and LINKGID in (select gid from crm_price_carrier where TYPE='LCL' " + hid_sel + ")";// and CORPID='" + strCORPID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, str, null);
//
str = "update crm_price_carrier set ISISSUE=1 where LOCKFLAG=1 and TYPE='LCL' " + hid_sel;// and CORPID='" + strCORPID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, str, null);
//
str = "insert into eb_pricequery(GID,PODLOAD,PORTDISCHARGE,DESTINATION,CARRIER,FORWARDER,ETD,TT,VIA,POC,SVC,LANE,CARRIERLANE,OWS,VALIDDATE,EFFECTIVEDATE,INPUTBY,COMMODITY,SC,NAMEAC,SICUT,CUSCLSG,CYCLSG,[20GP],[40GP],[40HQ],[45HQ],LEVEL,MODIFIEDUSER,LINKGID,REMARK,CARRIERCN,BOOKINGFEE,CTNFEE,THCFEE,FILEFEE,LCLPRICE,CISF,REMARK2,TYPE,CORPID,[40RH],PIRICEVIAPORT,[20RS],[40NOR]) "
+ " select newid(),PODLOAD,PORTDISCHARGE,DESTINATION,CARRIER,FORWARDER,ETD,TT,VIA,POC,SVC,LANE,CARRIERLANE,OWS,VALIDDATE,EFFECTIVEDATE,'" + sSHOWNAME + "',COMMODITY,SC,NAMEAC,SICUT,CUSCLSG,CYCLSG,[20GP1],[40GP1],[40HQ1],[45HQ1],'1','" + sSHOWNAME + "',GID,REMARK,CARRIERCN,BOOKINGFEE,CTNFEE,THCFEE,FILEFEE,LCLPRICE,CISF,REMARK2,TYPE,CORPID,[40RH1],PIRICEVIAPORT,[20RS1],[40NOR1] from crm_price_carrier where LOCKFLAG=1 and TYPE='LCL' " + hid_sel;// and CORPID='" + strCORPID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, str, null);
//
//str = str.Trim().Replace("'", "''");
//str = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('删除客户运价查询eb_pricequery数据','删除操作','" + str + "','" + strUserID + "')";
//SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, str, null);
//事务提交
sqlTran.Commit();
iResult = 1;//状态为1表示插入成功
}
catch (Exception execError)
{
iResult = -1;//有异常,插入失败
sqlTran.Rollback();
iResult = -2;//插入异常,事务已回滚成功
throw execError;
}
finally
{
SqlHelper.CloseConnection();
}
}
return iResult;
}
#endregion
#region 全部发布LC
///
/// 全部发布LC
///
/// 运价查询信息GID
/// 删除人ID
/// 值1表示删除成功 值-1表示有异常，删除失败 值-2表示插入异常,事务已回滚成功
public int GetISSUEALLLC(string strUserID, string strCORPID, string hid_sel)
{
int iResult = 0;
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
T_ALL_DA T_ALL_DA = new T_ALL_DA();
string sSHOWNAME = T_ALL_DA.GetStrSQL("SHOWNAME", "select SHOWNAME from [user] where GID ='" + strUserID + "'");
string str = "delete from eb_pricequery where TYPE='LC' and LINKGID in (select gid from crm_price_carrier where TYPE='LC' " + hid_sel + ")";// and CORPID='" + strCORPID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, str, null);
//
str = "update crm_price_carrier set ISISSUE=1 where LOCKFLAG=1 and TYPE='LC' " + hid_sel;// and CORPID='" + strCORPID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, str, null);
//
str = "insert into eb_pricequery(GID,PODLOAD,PORTDISCHARGE,DESTINATION,CARRIER,FORWARDER,ETD,TT,VIA,POC,SVC,LANE,CARRIERLANE,OWS,VALIDDATE,EFFECTIVEDATE,INPUTBY,COMMODITY,SC,NAMEAC,SICUT,CUSCLSG,CYCLSG,[20GP],[40GP],[40HQ],[45HQ],LEVEL,MODIFIEDUSER,LINKGID,REMARK,CARRIERCN,BOOKINGFEE,CTNFEE,THCFEE,FILEFEE,LCLPRICE,CISF,REMARK2,TYPE,CORPID,[40RH],PIRICEVIAPORT,[20RS],[40NOR]) "
+ " select newid(),PODLOAD,PORTDISCHARGE,DESTINATION,CARRIER,FORWARDER,ETD,TT,VIA,POC,SVC,LANE,CARRIERLANE,OWS,VALIDDATE,EFFECTIVEDATE,'" + sSHOWNAME + "',COMMODITY,SC,NAMEAC,SICUT,CUSCLSG,CYCLSG,[20GP1],[40GP1],[40HQ1],[45HQ1],'1','" + sSHOWNAME + "',GID,REMARK,CARRIERCN,BOOKINGFEE,CTNFEE,THCFEE,FILEFEE,LCLPRICE,CISF,REMARK2,TYPE,CORPID,[40RH1],PIRICEVIAPORT,[20RS1],[40NOR1] from crm_price_carrier where LOCKFLAG=1 and TYPE='LC' " + hid_sel;// and CORPID='" + strCORPID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, str, null);
//
//str = str.Trim().Replace("'", "''");
//str = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('删除客户运价查询eb_pricequery数据','删除操作','" + str + "','" + strUserID + "')";
//SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, str, null);
//事务提交
sqlTran.Commit();
iResult = 1;//状态为1表示插入成功
}
catch (Exception execError)
{
iResult = -1;//有异常,插入失败
sqlTran.Rollback();
iResult = -2;//插入异常,事务已回滚成功
throw execError;
}
finally
{
SqlHelper.CloseConnection();
}
}
return iResult;
}
#endregion
///
/// 根据SQL语句查询日志记录
///
///
///
public DataSet GetExcuteSql(string strSql)
{
DataSet userSet = new DataSet();
userSet = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql);
return userSet;
}
///
/// 获取日志记录总页数
///
/// sql查询语句
/// 返回int型日志记录总数
public int GetPageTotal(string strSql)
{
int iResult = 0;
using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql, null))
{
try
{
while (sqlRead.Read())
{
iResult = sqlRead.GetInt32(0);
}
}
catch (Exception error)
{
throw error;
}
}
return iResult;
}
}
}