/// <summary>
/// 判断用户是否已经存在，
/// </summary>
/// <param name="usercode"></param>
/// <param name="openid"></param>
/// <param name="nickname"></param>
/// <returns></returns>
public static bool UserExsit(string usercode, string openid, string nickname)
{
using (DBSession db = new DBSession())
{
string sql = "select wcopenid,wcnickname,isunbind from wechat_user where gwyusercode='" + usercode + "' and wcopenid<>'" + openid + "' order by unbindtime desc ";
DataTable dt = db.QuerySignle(sql);
if (dt != null && dt.Rows.Count > 0)
{
//已经存在则向之前的用户推送消息
string exsql = @"insert into wechat_loginexceptioninfo(id,loginopenid,loginnickname,usercode,oldopenid,oldnickname)
values(wechat_loginexceptioninfo_id.nextval,'{0}','{1}','{2}','{3}','{4}')";
exsql = string.Format(exsql, openid, nickname, usercode, dt.Rows[0]["wcopenid"].ToString2(), dt.Rows[0]["wcnickname"].ToString2());
db.ExecuteSignle(exsql);
if (dt.Rows[0]["isunbind"].ToString2() == "1")
{
return(false);//已经解绑，可以登录
}
else
{
return(true);//未解绑，不可登录
}
}
return(false);
}
}
/// <summary>
/// 删除订阅信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static bool deleteSubscribe(string id)
{
using (DBSession db = new DBSession())
{
string sql = "delete from wechat_subscribe where id=" + id + " and triggerstatus=0";
return(db.ExecuteSignle(sql) == 0 ? false : true);
}
}
/// <summary>
/// 信息已推送
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static bool updateSubscirbeInfo(int id)
{
using (DBSession db = new DBSession())
{
string sql = "update wechat_subscribe set TriggerStatus=2,pushtime=sysdate where id=" + id;
return(db.ExecuteSignle(sql) == 0 ? true : false);
}
}
/// <summary>
/// 信息推送失败
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static bool updateLoginExceptionInfo_failure(int id)
{
using (DBSession db = new DBSession())
{
string sql = "update wechat_loginexceptioninfo set issend=issend + 1,sendtime=sysdate where id=" + id;
return(db.ExecuteSignle(sql) == 0 ? true : false);
}
}
/// <summary>
/// 注销账号
/// </summary>
/// <param name="openid"></param>
/// <returns></returns>
public static bool DeleteUser(string openid)
{
using (DBSession db = new DBSession())
{
string sql = "update wechat_user set isunbind=1,unbindtime=sysdate where wcopenid='" + openid + "'";
return(db.ExecuteSignle(sql) == 0 ? false : true);
}
}
// public static string Pass(string ordercode)
// {
// string userid = "763"; string realname = "昆山吉时报关有限公司";
// using (DBSession db = new DBSession())
// {
// string sql = "select to_char(sitepasstime,'yyyy/mm/dd hh24:mi:ss') as sitepasstime from list_order where code='" + ordercode + "'";
// DataTable dt = db.QuerySignle(sql);
// string curtime = dt.Rows[0]["SITEPASSTIME"].ToString();
// if (curtime != "")
// {
// return curtime.Left(curtime.Length - 3).Replace("/", "");
// }
// curtime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
// sql = "update list_order set sitepassuserid='{1}',sitepassusername='******',sitepasstime=to_date('{3}','yyyy-MM-dd HH24:mi:ss'),declstatus=160 where code='{0}'";
// sql = string.Format(sql, ordercode, userid, realname, curtime);
// int i = db.ExecuteSignle(sql);
// if (i > 0)
// {
// MethodSvc.MethodServiceClient msc = new MethodSvc.MethodServiceClient();
// msc.redis_OrderStatusLog(ordercode);
// //add 20180115 保存操作记录list_times
//// sql = @"insert into list_times(id,code,userid,realname,times,type,ispause)
//// values(list_times_id.nextval,'" + ordercode + "','" + userid + "','" + realname + "',sysdate,'0',0)";
//// db.ExecuteSignle(sql);
// //add 20180119 保存历史记录
// sql = @"insert into list_updatehistory(id,UPDATETIME,TYPE
// ,ORDERCODE,USERID,NEWFIELD,NAME,CODE,FIELD,FIELDNAME)
// values(LIST_UPDATEHISTORY_ID.nextval,sysdate,'1'
// ,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')";
// sql = string.Format(sql, ordercode, userid, curtime, realname, ordercode, "SITEPASSTIME", "报关放行");
// db.ExecuteSignle(sql);
// return curtime.Left(curtime.Length - 3).Replace("/", "");
// }
// else
// {
// return "";
// }
// /*string curtime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
// string sql = "update list_order set sitepassuserid='{1}',sitepassusername='******',sitepasstime=to_date('{3}','yyyy-MM-dd HH24:mi:ss') where code='{0}'";
// sql = string.Format(sql, ordercode, "763", "昆山吉时报关有限公司", curtime);
// int i = db.ExecuteSignle(sql);
// if (i > 0)
// {
// return curtime.Left(curtime.Length - 3).Replace("/", "");
// }
// else
// {
// return "";
// }*/
// }
// }
public static string Passall(string ordercode, WGUserEn user)
{
string userid = user.GwyUserID.ToString(); string username = user.GwyUserCode; string realname = user.GwyUserName;
//string userid = "763"; string username = "******"; string realname = "昆山吉时报关有限公司";
using (DBSession db = new DBSession())
{
string sql = "select to_char(sitepasstime,'yyyy/mm/dd hh24:mi:ss') as sitepasstime from list_order where code='" + ordercode + "'";
DataTable dt = db.QuerySignle(sql);
string curtime = dt.Rows[0]["SITEPASSTIME"].ToString();
if (curtime != "")
{
return("{\"ORDERCODE\":'" + ordercode + "',\"CURTIME\":'" + curtime.Left(curtime.Length - 3).Replace("/", "") + "',\"FLAG\":'',\"ISEXISTS\":'Y'}");
}
curtime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
sql = "update list_order set sitepassuserid='{1}',sitepassusername='******',sitepasstime=to_date('{3}','yyyy-MM-dd HH24:mi:ss'),declstatus=160 where code='{0}' and declstatus<=160";
sql = string.Format(sql, ordercode, userid, realname, curtime);
int i = db.ExecuteSignle(sql);
if (i > 0)
{
MethodSvc.MethodServiceClient msc = new MethodSvc.MethodServiceClient();
msc.redis_OrderStatusLog(ordercode);
//add 20180119 保存历史记录
sql = @"insert into list_updatehistory(id,UPDATETIME,TYPE
,ORDERCODE,USERID,NEWFIELD,NAME,CODE,FIELD,FIELDNAME)
values(LIST_UPDATEHISTORY_ID.nextval,sysdate,'1'
,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')";
sql = string.Format(sql, ordercode, userid, curtime, realname, ordercode, "SITEPASSTIME", "报关放行");
db.ExecuteSignle(sql);
return("{\"ORDERCODE\":'" + ordercode + "',\"CURTIME\":'" + curtime.Left(curtime.Length - 3).Replace("/", "") + "',\"FLAG\":'S',\"ISEXISTS\":'N'}");
}
else
{
return("{\"ORDERCODE\":'" + ordercode + "',\"CURTIME\":'" + curtime.Left(curtime.Length - 3).Replace("/", "") + "',\"FLAG\":'E',\"ISEXISTS\":'N'}");
}
}
}
/// <summary>
/// 新增账号
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public static bool SaveUser(WGUserEn user)
{
using (DBSession db = new DBSession())
{
//string sql = "delete from wechat_user where WCOpenID='" + user.WCOpenID + "'";
//db.ExecuteSignle(sql);
string sql = @"insert into wechat_user(id,GWYUSERCODE,GWYUSERNAME,WCOpenID,WCNickName,iscompany,iscustomer,isreceiver,customercode,createdate,gwyuserid,hscode)
values(wechat_user_id.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',sysdate,{8},'{9}')";
sql = string.Format(sql, user.GwyUserCode, user.GwyUserName, user.WCOpenID, user.WCNickName, user.IsCompany, user.IsCustomer, user.IsReceiver,
user.CustomerCode, user.GwyUserID, user.HSCode);
return(db.ExecuteSignle(sql) == 0 ? false : true);
}
}
public static bool saveModifyFlag(string preinspcode, int modifyflag, WGUserEn user)
{
bool bf = false;
try
{
string userid = user.GwyUserID.ToString(); string username = user.GwyUserCode; string realname = user.GwyUserName;
//string userid = "763"; string username = "******"; string realname = "昆山吉时报关有限公司";
using (DBSession db = new DBSession())
{
string sql = "";
if (modifyflag == 1)
{
sql = @",delorderuserid='{1}',delorderusername='******',delordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')
,modorderuserid=null,modorderusername=null,modordertime=null
,modfinishuserid=null,modfinishusername=null,modfinishtime=null";
}
if (modifyflag == 2)
{
sql = @",delorderuserid=null,delorderusername=null,delordertime=null
,modorderuserid='{1}',modorderusername='******',modordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')
,modfinishuserid=null,modfinishusername=null,modfinishtime=null";
}
if (modifyflag == 3)
{
sql = @",delorderuserid=null,delorderusername=null,delordertime=null
,modorderuserid=null,modorderusername=null,modordertime=null
,modfinishuserid='{1}',modfinishusername='******',modfinishtime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')";
}
sql = @"update list_inspection set modifyflag=" + modifyflag + sql + " where code='{0}'";
sql = string.Format(sql, preinspcode, userid, realname, DateTime.Now);
db.ExecuteSignle(sql);
bf = true;
}
}
catch (Exception ex)
{
LogHelper.Write("saveModifyFlag_sql:" + ex.Message + "——code:" + preinspcode + " modifyflag:" + modifyflag);
}
return(bf);
}
public static bool saveModifyFlag(string predelcode, int modifyflag, WGUserEn user)
{
bool bf = false;
try
{
string userid = user.GwyUserID.ToString(); string username = user.GwyUserCode; string realname = user.GwyUserName;
//string userid = "763"; string username = "******"; string realname = "昆山吉时报关有限公司";
using (DBSession db = new DBSession())
{
string sql = "";
sql = @"select code,ordercode,declarationcode from list_declaration ld where ld.code='" + predelcode + "'";
DataTable dt_decl = db.QuerySignle(sql);
string ordercode = dt_decl.Rows[0]["ordercode"].ToString();
if (modifyflag == 1)//删单1
{
sql = @"select ld.code,ld.ordercode from list_declaration ld inner join config_filesplit cfs on ld.busiunitcode=cfs.busiunitcode and cfs.filetype='53' and ld.code='" + predelcode + "'";
DataTable dt = db.QuerySignle(sql);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
if (!string.IsNullOrEmpty(ordercode))
{
sql = @"update list_attachmentdetail t1 set t1.filetypeid='162' where t1.ordercode='" + ordercode + "' and t1.filetypeid='53'";
db.ExecuteSignle(sql);
}
}
}
}
if (modifyflag == 2)//改单2
{
DateTime time = DateTime.Now;
sql = @"update list_declaration_after set dataconfirm='1',dataconfirmusertime=to_date('" + time + "','yyyy-MM-dd HH24:mi:ss') where code='" + predelcode + "' and xzlb like '报关单%'";
db.ExecuteSignle(sql);
}
//改单完成3
//修改删改单标志
sql = @"update list_declaration set modifyflag=" + modifyflag;
//if (modifyflag == 1) { sql += ",delorderuserid='{1}',delorderusername='******',delordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')"; }
//if (modifyflag == 2) { sql += ",modorderuserid='{1}',modorderusername='******',modordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')"; }
//if (modifyflag == 3) { sql += ",modfinishuserid='{1}',modfinishusername='******',modfinishtime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')"; }
if (modifyflag == 1)
{
sql += @",delorderuserid='{1}',delorderusername='******',delordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')
,modorderuserid=null,modorderusername=null,modordertime=null
,modfinishuserid=null,modfinishusername=null,modfinishtime=null";
}
if (modifyflag == 2)
{
sql += @",delorderuserid=null,delorderusername=null,delordertime=null
,modorderuserid='{1}',modorderusername='******',modordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')
,modfinishuserid=null,modfinishusername=null,modfinishtime=null";
}
if (modifyflag == 3)
{
sql += @",delorderuserid=null,delorderusername=null,delordertime=null
,modorderuserid=null,modorderusername=null,modordertime=null
,modfinishuserid='{1}',modfinishusername='******',modfinishtime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')";
}
sql += " where code='{0}'";
sql = string.Format(sql, predelcode, userid, realname, DateTime.Now);
db.ExecuteSignle(sql);
//修改订单的报关状态
sql = "select customsstatus from list_declaration where ordercode='" + ordercode + "' and isinvalid=0 and modifyflag<>1";
bool flag = true;
DataTable dt_order_status = db.QuerySignle(sql);
if (dt_order_status != null)
{
if (dt_order_status.Rows.Count > 0)
{
foreach (DataRow dr in dt_order_status.Rows)
{
if (dr["customsstatus"].ToString2() == "" || (dr["customsstatus"].ToString2() != "已结关" && dr["customsstatus"].ToString2() != "已放行"))
{
flag = false;
break;
}
}
}
}
if (flag)
{
sql = "update list_order set declstatus=160,sitepassusername='******',sitepasstime=sysdate,siteapplyuserid=-2 where code='" + ordercode + "' and declstatus<=160";
db.ExecuteSignle(sql);
}
//---------------------------------------------------------------------------------------------------------------
//保存操作记录list_times
//sql = @"insert into list_times(id,code,userid,realname,times,type,ispause)
//values(list_times_id.nextval,'" + predelcode + "','" + userid + "','" + realname + "',sysdate,'1'," + modifyflag + ")";
//db.ExecuteSignle(sql);
//调用缓存接口redis_DeclarationLog
MethodSvc.MethodServiceClient msc = new MethodSvc.MethodServiceClient();
msc.redis_DeclarationLog(ordercode, predelcode, dt_decl.Rows[0]["declarationcode"].ToString(), "", "0");
sql = @"select code,entrusttype,declstatus,inspstatus from list_order lo where lo.code='" + ordercode + "'";
DataTable dt_order = db.QuerySignle(sql);
if (dt_order.Rows[0]["entrusttype"].ToString() == "03")
{
if (Convert.ToInt32(dt_order.Rows[0]["declstatus"].ToString()) >= 160 && Convert.ToInt32(dt_order.Rows[0]["inspstatus"].ToString()) >= 120)
{
//add 20180115 费用异常接口
msc.FinanceExceptionOrder(ordercode, username, "list_declaration.modifyflag修改为" + modifyflag.ToString());
}
}
else
{
if (Convert.ToInt32(dt_order.Rows[0]["declstatus"].ToString()) >= 160)
{
//add 20180115 费用异常接口
msc.FinanceExceptionOrder(dt_decl.Rows[0]["ordercode"].ToString(), username, "list_declaration.modifyflag修改为" + modifyflag.ToString());
}
}
bf = true;
}
}
catch (Exception ex)
{
LogHelper.Write("saveModifyFlag_sql:" + ex.Message + "——code:" + predelcode + " modifyflag:" + modifyflag);
}
return(bf);
}
