/// <summary>
/// 获取所有货币信息
/// </summary>
/// <returns></returns>
public static List <t_currencyEntity> GetCurrencyList()
{
using (var con = ConnectionFactory.CreateConnection())
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
string sql = SqlFactory.GetSelectListSql(new t_currencyEntity());
return(con.Query <t_currencyEntity>(sql).ToList());
}
}
/// <summary>
///
/// </summary>
public bool UpdateBdsbwh(List <yw_hddz_bdsbwhEntity> list)
{
using (conn = ConnectionFactory.CreateConnection())
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
string sql = SqlFactory.GetUpdateSql(new yw_hddz_bdsbwhEntity(), "yw_hddz_bdsbwh", "bdyssbh");
return(conn.Execute(sql, list) != 0);
}
}
/// <summary>
/// 插入地图查询日志
/// </summary>
public bool InsertMapLog(s_log_mapEntity model)
{
using (conn = ConnectionFactory.CreateConnection())
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
string sql = SqlFactory.GetInsertSql(model);
return(conn.Execute(sql, model) != 0);
}
}
/// <summary>
/// 获取所有货币信息
/// </summary>
/// <returns></returns>
public static List <yw_wldwEntity> GetWldwListByHddz(yw_hddzEntity model)
{
using (var con = ConnectionFactory.CreateConnection())
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
string sql = SqlFactory.GetSelectListSql(new yw_wldwEntity());
sql += " where yw_khbm in(@khbm,@gwgysbm,@jydwbm,@cyrbm)";
return(con.Query <yw_wldwEntity>(sql, model).ToList());
}
}
/// <summary>
/// 添加ocr值对应基础数据关系
/// </summary>
/// <param name="list"></param>
public static void AddMapList(List <yw_hddz_ocrvalmapEntity> list)
{
using (var con = ConnectionFactory.CreateConnection())
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
string sql = SqlFactory.GetInsertSql(new yw_hddz_ocrvalmapEntity());
con.Execute(sql, list);
}
}
/// <summary>
/// 分页查询货代单证
/// </summary>
/// <param name="strWhere"></param>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <yw_hddz_kycdEntity> GetKycdByPage(string strWhere, int page, int pageSize)
{
using (conn = ConnectionFactory.CreateConnection())
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
string sql = SqlFactory.GetPageSelectListSql(new yw_hddz_kycdEntity(), " zbrq desc ", (page - 1) * pageSize, pageSize, strWhere);
return(conn.Query <yw_hddz_kycdEntity>(sql).ToList());
}
}
/// <summary>
/// 保存同步失败的参数
/// </summary>
/// <param name="model"></param>
public static void SaveErrLog(s_log_errEntity model)
{
using (var con = ConnectionFactory.CreateConnection())
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
string sql = SqlFactory.GetInsertSql(model);
con.Execute(sql, model);
}
}
/// <summary>
/// 新建国家
/// </summary>
/// <param name="model"></param>
public static void AddCountry(t_countryEntity model)
{
using (var con = ConnectionFactory.CreateConnection())
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
string sql = SqlFactory.GetInsertSql(model);
con.Execute(sql, model);
}
}
/// <summary>
/// 查询s_log_err表数据
/// </summary>
/// <param name="pageSize">每次查询最大数据</param>
/// <param name="cscs">重试次数</param>
/// <returns></returns>
public List <s_log_errEntity> GetLogerrList(int pageSize, int cscs)
{
using (var con = ConnectionFactory.CreateConnection())
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
string strWhere = " cscs<@cscs and sfcg=0";
string sql = SqlFactory.GetPageSelectListSql(new s_log_errEntity(), " cjsj asc ", 0, pageSize, strWhere);
return(con.Query <s_log_errEntity>(sql, new { cscs = cscs }).ToList());
}
}
/// <summary>
/// 监控任务记录日志
/// </summary>
/// <param name="log"></param>
public void addfencelog(s_fence_log log)
{
using (conn = ConnectionFactory.CreateConnection())
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
log.id = Guid.NewGuid().ToString();
string sql = SqlFactory.GetInsertSql(log);
conn.Execute(sql, log);
}
}
/// <summary>
/// 保存失败数据
/// </summary>
public bool SaveFailData(List <FailLogEntity> list)
{
using (var con = ConnectionFactory.CreateConnection())
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
string strsql = SqlFactory.GetInsertSql(new FailLogEntity());
if (con.Execute(strsql, list) == list.Count)
{
return(true);
}
}
return(false);
}
/// <summary>
/// 添加货代单证，包含主表和商品信息表
/// </summary>
/// <param name="model"></param>
/// <param name="list"></param>
/// <returns></returns>
public bool SaveHddz(yw_hddzEntity model, List <yw_hddz_spxxEntity> list)
{
using (conn = ConnectionFactory.CreateConnection())
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
IDbTransaction _transaction = conn.BeginTransaction();
try
{
#region 数据补全
if (model.ysfs.Trim() == "海运")
{
model.ywbh = new Interfaces.Service.HddzService().GetNewYwbh(DateTime.Now.ToString("yy") + model.dqdm);
}
else
{
model.ywbh = new Interfaces.Service.HddzService().GetNewYwbh(DateTime.Now.ToString("yy") + "OC");
}
model.zbrq = DateTime.Now;
model.state = "新制";
model.zydl = model.zjsbm == model.khbm ? "自营" : "代理";
list.ForEach(p => p.ywbh = model.ywbh);
#endregion
//插入货代主表
string sql = SqlFactory.GetInsertSql(model);
conn.Execute(sql, model, _transaction);
//插入商品信息分录表
string spxxsql = SqlFactory.GetInsertSql(new yw_hddz_spxxEntity());
conn.Execute(spxxsql, list, _transaction);
AddHddzJzxxx(model, _transaction);
_transaction.Commit();
return(true);
}
catch (Exception ex)
{
_transaction.Rollback();
throw ex;
}
}
}
/// <summary>
/// 修改货代单证，包含主表和商品信息表
/// </summary>
/// <param name="model"></param>
/// <param name="list"></param>
/// <returns></returns>
public bool ModifyHddz(yw_hddzEntity model, List <yw_hddz_spxxEntity> list)
{
using (conn = ConnectionFactory.CreateConnection())
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
IDbTransaction _transaction = conn.BeginTransaction();;
try
{
#region 数据补全
model.zydl = model.zjsbm == model.khbm ? "自营" : "代理";
#endregion
//插入货代主表
//string sql = SqlFactory.GetUpdateSql(model, "yw_hddz", "ywbh");
string sql = @"update yw_hddz set gwgysbm=@gwgysbm,gwgysmc=@gwgysmc,gwgysjc=@gwgysjc,
khbm=@khbm,khmc=@khmc,khjc=@khjc,khpym=@khpym,zjsmc=@zjsmc,zjsbm=@zjsbm,zjsjc=@zjsjc,zjspym=@zjspym,shdwbm=@shdwbm,shdwmc=@shdwmc,
jydwbm=@jydwbm,jydwdm=@jydwdm,jydwmc=@jydwmc,jydwjc=@jydwjc,jydwpym=@jydwpym,jydwsjjgdm=@jydwsjjgdm,
hth=@hth,wbbb=@wbbb,zje=@zje,ycddm=@ycddm,ycd=@ycd,dqdm=@dqdm,yzbh=@yzbh,ctr_area2=@ctr_area2,qygdm=@qygdm,qiyunguodm=@qiyunguodm,qyg=@qyg,mdg=@mdg,
jgfs=@jgfs,invoiceno=@invoiceno,zjz=@zjz,zmz=@zmz,ysfs=@ysfs,zydl=@zydl,
ztdh=@ztdh,hz_spmc=@hz_spmc,cyr=@cyr,cyrbm=@cyrbm,cyrjc=@cyrjc,cyrpym=@cyrpym,ejycd=@ejycd,sfyyf=@sfyyf,tzrbm=@tzrbm,tzrmc=@tzrmc,tzrjc=@tzrjc,tzrpym=@tzrpym,
cm=@cm,hcorhbh=@hcorhbh,zz_hcorhbh=@zz_hcorhbh,zcrq=@zcrq,fh=@fh,hz_jzxh=@hz_jzxh,hz_xx=@hz_xx,hz_zhlx=@hz_zhlx,invoice_date=@invoice_date,
state=@state,mygb=@mygb,clfs=@clfs where ywbh=@ywbh";
conn.Execute(sql, model, _transaction);
//删除现有的商品，重新添加
string delsql = "delete from yw_hddz_spxx where ywbh=@ywbh";
conn.Execute(delsql, new { ywbh = model.ywbh }, _transaction);
//删除集装箱信息
string deljzxsql = "delete from yw_hddz_jzxxx where ywbh=@ywbh";
conn.Execute(deljzxsql, new { ywbh = model.ywbh }, _transaction);
//插入商品信息分录表
string spxxsql = SqlFactory.GetInsertSql(new yw_hddz_spxxEntity());
conn.Execute(spxxsql, list, _transaction);
AddHddzJzxxx(model, _transaction);
_transaction.Commit();
return(true);
}
catch (Exception ex)
{
_transaction.Rollback();
throw ex;
}
}
}
/// <summary>
/// 记录错误日志
/// </summary>
/// <param name="list"></param>
public static void SaveErrorLog(List <s_log> list)
{
foreach (var m in list)
{
m.cxh = GetCxh(m);
}
using (var con = ConnectionFactory.CreateConnection())
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
string sql = SqlFactory.GetInsertSql(new s_log());
con.Execute(sql, list);
//return con.Query<t_currencyEntity>(sql).ToList();
}
}
/// <summary>
/// 分页查询往来单位
/// </summary>
/// <param name="name"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static List <yw_wldwEntity> GetWldwListByPage(string name, int pageIndex, int pageSize, out int count)
{
using (var con = ConnectionFactory.CreateConnection())
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
count = 0;
string strWhere = "";
if (!string.IsNullOrEmpty(name))
{
strWhere += " (khmc like '%'+@name+'%' or khmc_yw like '%'+@name+'%')";
//countsql += " where (khmc like '%'+@name+'%' or khmc_yw like '%'+@name+'%')";
}
string sql = SqlFactory.GetPageSelectListSql(new yw_wldwEntity(), "khbm", pageIndex, pageSize, strWhere);
string countsql = "select count(1) from yw_wldw " + (string.IsNullOrEmpty(strWhere) ? "" : " where " + strWhere);
count = con.Query <int>(countsql, new { name = name }).FirstOrDefault();
return(con.Query <yw_wldwEntity>(sql, new { name = name }).ToList());
}
}
/// <summary>
/// 添加集装箱信息表
/// </summary>
public bool AddHddzJzxxx(yw_hddzEntity model, IDbTransaction _transaction)
{
List <yw_hddz_jzxxxEntity> list = new List <yw_hddz_jzxxxEntity>();
if (string.IsNullOrEmpty(model.hz_jzxh))
{
return(true);
}
string[] strjzxh = model.hz_jzxh.Replace("、", ",").Split(',');
int i = 1;
foreach (string jzxh in strjzxh)
{
if (jzxh.Trim() == "")
{
continue;
}
yw_hddz_jzxxxEntity jzx = new yw_hddz_jzxxxEntity();
jzx.ywbh = model.ywbh;
jzx.jzxh = jzxh;
jzx.ysfs = model.ysfs;
jzx.xx = "40尺高柜";
jzx.xl = "冷藏集装箱";
jzx.cxh = i++;
list.Add(jzx);
}
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
string sql = SqlFactory.GetInsertSql(new yw_hddz_jzxxxEntity());
return(conn.Execute(sql, list, _transaction) != 0);
}
/// <summary>
/// 根据主键和表查询数据
/// </summary>
/// <param name="tablename"></param>
/// <param name="mainid"></param>
/// <param name="parameters"></param>
/// <param name="cscs"></param>
/// <returns></returns>
public List <s_log_errEntity> GetLogerrListByTableName(string tablename, string mainid, string[] parameters, int cscs)
{
using (var con = ConnectionFactory.CreateConnection())
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
string strWhere = " where mainid=@mainid and tablename=@tablename and cscs<@cscs and sfcg=0 ";
string sql = SqlFactory.GetSelectListSql(new s_log_errEntity()) + strWhere;
string p = null;
if (parameters != null)
{
p = string.Join(",", parameters);
}
if (!string.IsNullOrEmpty(p))
{
strWhere += "and parameters=@parameters";
}
return(con.Query <s_log_errEntity>(sql, new { cscs = cscs, mainid = mainid, tablename = tablename, parameters = p }).ToList());
}
}
public void UpdateWlgzCmd(yw_hddz_wlgz_cmdEntity model)
{
using (conn = ConnectionFactory.CreateConnection())
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
StringBuilder sql = new StringBuilder();
sql.Append("update yw_hddz_wlgz_cmd set ");
string strWhere = "";
if (model.jrdzwlsj != null)
{
sql.Append("jrdzwlsj =@jrdzwlsj,jdzwlsj_lrsj=@jdzwlsj_lrsj,jdzwlsj_czry='北斗' ");
strWhere = " and jrdzwlsj is null";
}
else if (model.cdzwlsj != null)
{
sql.Append("cdzwlsj =@cdzwlsj,cdzwlsj_lrsj=@cdzwlsj_lrsj,cdzwlsj_czry='北斗' ");
strWhere = " and cdzwlsj is null";
}
else
{
throw new Exception("进出电子围栏时间必须有一个不能为空");
}
sql.Append(" where rwbh=@rwbh and wlxh=@wlxh");
sql.Append(strWhere);
conn.Execute(sql.ToString(), model);
conn.Execute("p_hywlgz", new { ywbh = model.ywbh, cxh = model.hddz_cxh }, null, null, CommandType.StoredProcedure);
s_log_errEntity log = new s_log_errEntity();
log.eid = "dzwl_" + Guid.NewGuid().ToString();
log.cjsj = DateTime.Now;
log.cscs = 0;
log.funname = "DataToFreshPort";
log.mainid = model.ywbh;
log.parameters = model.hddz_cxh.ToString();
log.changecols = "gq_sj,gq_cp,gq_sjlxfs,gq_rwbh,gq_c_rwbh,hgcyd_sj,hgcyd_cp,hgcyd_sjlxfs,hgcyd_rwbh,hgcyd_c_rwbh,jyd_sj,jyd_cp,jyd_sjlxfs,jyd_rwbh,jyd_c_rwbh,"
+ "sc_sj,sc_cp,sc_sjlxfs,sc_rwbh,sc_c_rwbh,zjfscwld_sj,zjfscwld_cp,zjfscwld_sjlxfs,zjfscwld_rwbh,zjfscwld_c_rwbh,jschfscwld_sj,jschfscwld_cp,"
+ "jschfscwld_sjlxfs,jschfscwld_rwbh,jschfscwld_c_rwbh,fscwld3_sj,fscwld3_cp,fscwld3_sjlxfs,fscwld3_rwbh,fscwld3_c_rwbh,fscwld4_sj,fscwld4_cp,"
+ "fscwld4_sjlxfs,fscwld4_rwbh,fscwld4_c_rwbh,dc_sj,dc_cp,dc_sjlxfs,dc_rwbh,cgqsj,rscsj,djydsj,tgjysj,cscsj,hdcsj,fscwl_ddsj,fscwl_lksj,fscwl2_ddsj,"
+ "fscwl2_lksj,fscwl3_ddsj,fscwl3_lksj,fscwl4_ddsj,fscwl4_lksj";
log.sfcg = 0;
log.tablename = "yw_hddz_jzxxx";
s_log_errEntity log1 = new s_log_errEntity();
log1.eid = "dzwl_" + Guid.NewGuid().ToString();
log1.cjsj = DateTime.Now;
log1.cscs = 0;
log1.funname = "DataToFreshPort";
log1.mainid = model.ywbh;
log1.parameters = model.hddz_cxh.ToString();
log1.changecols = "";
log1.sfcg = 0;
log1.tablename = "yw_hddz_wlgz";
List <s_log_errEntity> list = new List <s_log_errEntity>();
list.Add(log);
list.Add(log1);
string logsql = SqlFactory.GetInsertSql(log);
conn.Execute(logsql, list);
}
}