public List <Models.DbModels.tb_token> GetByPage(XXF.Db.DbConn PubConn, int pno, int pagesize, string keywords, DbModels.TokenType tokentype, out int totalcount)
{
List <DbModels.tb_token> tokenlist = new List <DbModels.tb_token>();
string basesql = "select {0} from " + tokentype.ToString() + " ";
string whercon = "";
XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter();
string querysql = string.Format(basesql, "ROW_NUMBER() over (order by createtime desc) as rownum, token,userid,id,username,appid,createtime,expires");
if (!string.IsNullOrEmpty(keywords))
{
para.Add("@keywords", keywords);
whercon = " where token=@keywords or userid like '%' + @keywords + '%' or username like '%' + @keywords + '%' or appid like '%' + @keywords + '%' ";
}
querysql = string.Concat("select A.* from (", querysql, whercon, ") A ", " where A.rownum between ", (pno - 1) * pagesize + 1, " and ", pagesize * pno);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, querysql, para.ToParameters());
foreach (DataRow dr in ds.Tables[0].Rows)
{
tokenlist.Add(CreateModel(dr));
}
totalcount = (int)PubConn.ExecuteScalar(string.Format(basesql, " count(*) ") + whercon, para.ToParameters());
return(tokenlist);
}
public virtual bool Add2(DbConn PubConn, tb_consumer_model model)
{
return SqlHelper.Visit((ps) =>
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//��������ʱid(�����������Ψһ,Guidתlong)
new ProcedureParameter("@tempid", model.tempid),
//������clinet��id
new ProcedureParameter("@consumerclientid", model.consumerclientid),
//֧�ֵķ���˳���(֧�ֶ��˳���)
new ProcedureParameter("@partitionindexs", model.partitionindexs),
//�ͻ�������
new ProcedureParameter("@clientname", model.clientname),
////�������ʱ��(�Ե�ǰ��ʱ��Ϊ)
//new ProcedureParameter("@lastheartbeat", model.lastheartbeat),
////��һ�θ���ʱ��(�Ե�ǰ��ʱ��Ϊ)
//new ProcedureParameter("@lastupdatetime", model.lastupdatetime),
////�ͻ��˴���ʱ��(�Ե�ǰ��ʱ��Ϊ)
//new ProcedureParameter("@createtime", model.createtime)
};
int rev = PubConn.ExecuteSql(@"insert into tb_consumer(tempid,consumerclientid,partitionindexs,clientname,lastheartbeat,lastupdatetime,createtime)
values(@tempid,@consumerclientid,@partitionindexs,@clientname,getdate(),getdate(),getdate())", Par);
return rev == 1;
});
}
public virtual bool Add(DbConn PubConn, tb_user_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//员工工号
new ProcedureParameter("@userstaffno", model.userstaffno),
//
new ProcedureParameter("@username", model.username),
//员工角色，查看代码枚举：开发人员，管理员
new ProcedureParameter("@userrole", model.userrole),
//
new ProcedureParameter("@usercreatetime", model.usercreatetime),
//员工手机号码
new ProcedureParameter("@usertel", model.usertel),
//
new ProcedureParameter("@useremail", model.useremail),
//登录密码
new ProcedureParameter("@userpsw", model.userpsw)
};
int rev = PubConn.ExecuteSql(@"insert into tb_user(userstaffno,username,userrole,usercreatetime,usertel,useremail,userpsw)
values(@userstaffno,@username,@userrole,@usercreatetime,@usertel,@useremail,@userpsw)", Par);
return rev == 1;
}
/// <summary>
/// 退出登录，即删除token
/// </summary>
/// <param name="c"></param>
/// <param name="tokentype"></param>
/// <returns></returns>
public static ActionResult LogOut(Controller c, CertCenter.Models.DbModels.TokenType tokentype)
{
CertComm.ServerResult r = new CertComm.ServerResult();
JsonResult jsonresult = new JsonResult();
jsonresult.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
jsonresult.Data = r;
string token = c.Request.Params["token"] ?? "";
if (string.IsNullOrEmpty(token))
{
r.code = -905;
r.msg = "token不能为空。";
return(jsonresult);
}
using (XXF.Db.DbConn PubConn = XXF.Db.DbConfig.CreateConn())
{
PubConn.Open();
DeleteToken(PubConn, token, tokentype);
r.code = 1;
r.msg = "退出成功。";
return(jsonresult);
}
}
/// <summary>
/// createtime expiresetime不用传。
/// </summary>
/// <param name="PubConn"></param>
/// <param name="model"></param>
/// <param name="tokentype"></param>
/// <returns></returns>
public virtual bool Add(DbConn PubConn, DbModels.tb_token model, Models.DbModels.TokenType tokentype)
{
DateTime nowtime = PubConn.GetServerDate();
model.createtime = nowtime;
model.expires = nowtime.AddMinutes(GetExpiresminutes(tokentype));
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//
new ProcedureParameter("@token", model.token),
//
new ProcedureParameter("@userid", model.userid),
new ProcedureParameter("@id", model.id),
//
new ProcedureParameter("@username", model.username),
//
new ProcedureParameter("@appid", model.appid),
//
new ProcedureParameter("@createtime", model.createtime),
//
new ProcedureParameter("@expires", model.expires )
};
int rev = PubConn.ExecuteSql("insert into " + tokentype.ToString() + " (token,userid,id,username,appid,createtime,expires)" +
" values(@token,@userid,@id,@username,@appid,@createtime,@expires)", Par);
return rev == 1;
}
public virtual bool Edit(DbConn PubConn, tb_user_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//员工工号
new ProcedureParameter("@userstaffno", model.userstaffno),
//
new ProcedureParameter("@username", model.username),
//员工角色，查看代码枚举：开发人员，管理员
new ProcedureParameter("@userrole", model.userrole),
//
new ProcedureParameter("@usercreatetime", model.usercreatetime),
//员工手机号码
new ProcedureParameter("@usertel", model.usertel),
//
new ProcedureParameter("@useremail", model.useremail),
//登录密码
new ProcedureParameter("@userpsw", model.userpsw)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_user set userstaffno=@userstaffno,username=@username,userrole=@userrole,usercreatetime=@usercreatetime,usertel=@usertel,useremail=@useremail,userpsw=@userpsw where id=@id", Par);
return rev == 1;
}
public List<tb_partition_model> GetClientCreateTime(DbConn conn)
{
try
{
List<tb_partition_model> list = new List<tb_partition_model>();
string sql = @"select partitionid,clientname,tb_consumer_partition.createtime from tb_consumer_partition,[tb_consumer] WITH(NOLOCK)
where [tb_consumer].tempid=tb_consumer_partition.lastconsumertempid";
DataTable dt = conn.SqlToDataTable(sql, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
tb_partition_model model = new tb_partition_model();
model.partitionid = Convert.ToInt32(dr["partitionid"]);
model.createtime = Convert.ToDateTime(dr["createtime"]);
list.Add(model);
}
}
return list;
}
catch (Exception ex)
{
throw ex;
}
}
public virtual List<tb_shuntruleconn_config_model> Get(DbConn PubConn,DateTime lastUpdateTime)
{
return XXF.ProjectTool.SqlHelper.Visit(ps =>
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
StringBuilder stringSql = new StringBuilder();
if (lastUpdateTime == default(DateTime))
{
stringSql.Append(@"select s.* from tb_distribution_rule_config s");
}
else
{
stringSql.Append(@"select s.* from tb_distribution_rule_config s where s.f_last_update_time>=@lastTime");
ps.Add("lastTime", lastUpdateTime);
}
// int rev = PubConn.ExecuteSql(stringSql.ToString(), ps.ToParameters());
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
var rs = new List<tb_shuntruleconn_config_model>();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
var r = CreateModel(dr);
rs.Add(r);
}
}
return rs;
});
}
/// <summary>
/// ɾ������
/// </summary>
/// <param name="conn"></param>
/// <param name="mqNodeConn"></param>
/// <param name="id"></param>
/// <param name="partitionId"></param>
/// <returns></returns>
public int DeleteMqQueue(DbConn conn, DbConn mqNodeConn, int id, int partitionId)
{
int flag = this.IsDeleted(conn, mqNodeConn, id, partitionId);
if (flag != 1) return flag;
try
{
conn.BeginTransaction();
if (this.DeletePartition(conn, partitionId))
{
if (!partitionDal.UpdateIsUsed(conn, 0, partitionId))
throw new Exception("���³���");
}
else
{
throw new Exception("ɾ������");
}
conn.Commit();
}
catch (Exception ex)
{
conn.Rollback();
XXF.Log.ErrorLog.Write("ɾ��MQ���г���:", ex);
return -1;
}
return 1;
}
public virtual bool Add2(DbConn PubConn, tb_producter_model model)
{
return SqlHelper.Visit((ps) =>
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//��������ʱid(�����������Ψһ,Guidתlong)
new ProcedureParameter("@tempid", model.tempid),
//����������
new ProcedureParameter("@productername", model.productername),
//ip��ַ
new ProcedureParameter("@ip", model.ip),
//����id
new ProcedureParameter("@mqpathid", model.mqpathid),
////�������������ʱ��
//new ProcedureParameter("@lastheartbeat", model.lastheartbeat),
////�����ߴ���ʱ��
//new ProcedureParameter("@createtime", model.createtime)
};
int rev = PubConn.ExecuteSql(@"insert into tb_producter(tempid,productername,ip,mqpathid,lastheartbeat,createtime)
values(@tempid,@productername,@ip,@mqpathid,getdate(),getdate())", Par);
return rev == 1;
});
}
public List <DbModels.certcenterlog> GetPage(XXF.Db.DbConn PubConn, int pno, int pagesize, string keywords, out int totalcount)
{
string basesql = "select {0} from certcenterlog ";
string querysql = string.Format(basesql, " ROW_NUMBER() over (order by reqtime desc ) as rownum,id,url,reqdata,userid,username,reqtime,ip,opecontent");
XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter();
string wherecon = "";
if (!string.IsNullOrEmpty(keywords))
{
wherecon = " where userid like '%' +@keywords+'%' or username like '%' +@keywords+'%' or url like '%' +@keywords+'%' or ip like '%' +@keywords+'%' ";
para.Add("@keywords", keywords);
}
DataSet ds = new DataSet();
querysql = string.Concat("select A.* from (", querysql, wherecon, ") A", " where A.rownum between ", (pno - 1) * pagesize + 1, " and ", pagesize * pno);
PubConn.SqlToDataSet(ds, querysql, para.ToParameters());
List <DbModels.certcenterlog> listlog = new List <DbModels.certcenterlog>();
foreach (DataRow a in ds.Tables[0].Rows)
{
listlog.Add(CreateModel(a));
}
totalcount = (int)PubConn.ExecuteScalar(string.Format(basesql, " count(*) ") + wherecon, para.ToParameters());
return(listlog);
}
public ProducterInfo GetProducterInfo(DbConn PubConn, string mqpath,string productername)
{
ProducterInfo r = new ProducterInfo();
tb_mqpath_dal mqpathdal = new tb_mqpath_dal();
r.MqPathModel = mqpathdal.Get(PubConn,mqpath);
if (r.MqPathModel == null)
throw new BusinessMQException(string.Format("当前mqpath:{0}未在MQ中注册队列,请联系管理员注册成功后使用。",mqpath));
tb_mqpath_partition_dal mqpathpartitiondal = new tb_mqpath_partition_dal();
r.MqPathParitionModel = mqpathpartitiondal.GetList(PubConn,r.MqPathModel.id);
if (r.MqPathParitionModel == null||r.MqPathParitionModel.Count==0)
throw new BusinessMQException(string.Format("当前mqpath:{0}未在MQ中分配相应的分区,请联系管理员分配分区后使用。", mqpath));
//注册生产者
r.ProducterModel = this.RegisterProducter(PubConn, r.MqPathModel.id, productername);
//获取分区相关节点
List<int> datanodepartition = new List<int>();
foreach (var p in r.MqPathParitionModel)
{
var partitionidinfo = PartitionRuleHelper.GetPartitionIDInfo(p.partitionid);
if (!datanodepartition.Contains(partitionidinfo.DataNodePartition))
datanodepartition.Add(partitionidinfo.DataNodePartition);
}
r.DataNodeModelDic = this.GetDataNodeModelsDic(PubConn, datanodepartition);
return r;
}
/// <summary>
/// nodeList
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public IList<tb_datanode_model> GetPageList(DbConn conn, int pageIndex, int pageSize, ref int count)
{
int tempCount = 0;
IList<tb_datanode_model> list = new List<tb_datanode_model>();
var result = SqlHelper.Visit((ps) =>
{
string sql = "SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_datanode WITH(NOLOCK)";
string countSql = "SELECT COUNT(1) FROM tb_datanode WITH(NOLOCK) ";
object obj = conn.ExecuteScalar(countSql, null);
if (obj != DBNull.Value && obj != null)
{
tempCount = LibConvert.ObjToInt(obj);
}
string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
DataTable dt = conn.SqlToDataTable(sqlPage, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
tb_datanode_model model = CreateModel(dr);
list.Add(model);
}
}
return list;
});
count = tempCount;
return result;
}
public static tb_node_model GetOneNode(DbConn PubConn,int id)
{
tb_node_dal dal = new tb_node_dal();
DataRow dr = dal.GetOneNode(PubConn, id);
tb_node_model model = CreateModelForNode(dr);
return model;
}
/// <summary>
/// debug日志分页列表
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="count"></param>
/// <returns></returns>
public IList<tb_debuglog_model> GetPageList(DbConn conn, DateTime? startTime, DateTime? endTime, string mqpathid, string mqpath, string methodname, string info, int pageSize, int pageIndex, ref int count)
{
int tempCount = 0;
var result = SqlHelper.Visit((ps) =>
{
IList<tb_debuglog_model> list = new List<tb_debuglog_model>();
StringBuilder where = new StringBuilder();
List<ProcedureParameter> parameters = new List<ProcedureParameter>();
where.Append(" WHERE 1=1 ");
if (startTime != null && endTime != null)
{
parameters.Add(new ProcedureParameter("startTime", startTime.Value.ToString("yyyy-MM-dd")));
parameters.Add(new ProcedureParameter("endTime", endTime.Value.ToString("yyyy-MM-dd")));
where.Append(" AND createtime>=@startTime AND createtime<=@endTime ");
}
if (!string.IsNullOrWhiteSpace(mqpathid))
{
parameters.Add(new ProcedureParameter("mqpathid", mqpathid));
where.Append(" AND mqpathid=@mqpathid ");
}
if (!string.IsNullOrWhiteSpace(mqpath))
{
parameters.Add(new ProcedureParameter("mqpath", mqpath));
where.Append(" AND mqpath=@mqpath ");
}
if (!string.IsNullOrWhiteSpace(methodname))
{
parameters.Add(new ProcedureParameter("methodname", methodname));
where.Append(" AND methodname like '%'+@methodname+'%' ");
}
if (!string.IsNullOrWhiteSpace(info))
{
parameters.Add(new ProcedureParameter("info", info));
where.Append(" AND info like '%'+@info+'%' ");
}
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_debuglog WITH(NOLOCK)");
string countSql = string.Concat("SELECT COUNT(1) FROM tb_debuglog WITH(NOLOCK) ", where.ToString());
object obj = conn.ExecuteScalar(countSql, parameters);
if (obj != DBNull.Value && obj != null)
{
tempCount = LibConvert.ObjToInt(obj);
}
string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
DataTable dt = conn.SqlToDataTable(sqlPage, parameters);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
tb_debuglog_model model = CreateModel(dr);
list.Add(model);
}
}
return list;
});
count = tempCount;
return result;
}
public tb_producter_model RegisterProducter(DbConn PubConn, int mqpathid,string productername)
{
long tempid = CommonHelper.GenerateIntID();
tb_producter_dal dal = new tb_producter_dal();
dal.DeleteNotOnLineByMqpathid(PubConn, mqpathid, SystemParamConfig.Producter_Heartbeat_MAX_TIME_OUT);
dal.Add2(PubConn, new tb_producter_model() { tempid = tempid, ip = CommonHelper.GetDefaultIP(), mqpathid=mqpathid, productername=productername });
return dal.Get(PubConn, tempid, mqpathid);
}
public Dictionary<int, Model.tb_datanode_model> GetDataNodeModelsDic(DbConn PubConn, List<int> datanodepartition)
{
tb_datanode_dal datanodedal = new tb_datanode_dal();
var rs = new Dictionary<int, Model.tb_datanode_model>();
foreach (var o in datanodedal.List(PubConn, datanodepartition))
rs.Add(o.datanodepartition, o);
return rs;
}
public tb_mqpath_model GetMqPathModel(DbConn PubConn, string mqpath)
{
tb_mqpath_dal mqpathdal = new tb_mqpath_dal();
var mqpathmodel = mqpathdal.Get(PubConn, mqpath);
if (mqpathmodel == null)
throw new BusinessMQException("当前队列不存在:" + mqpath);
return mqpathmodel;
}
public virtual string GetDataNodeConnectString(DbConn PubConn, int datanodepartition)
{
tb_datanode_dal dal = new tb_datanode_dal();
var model = dal.Get2(PubConn, datanodepartition);
if (model == null)
throw new BusinessMQException("当前数据节点不存在:" + datanodepartition);
return GetDataNodeConnectString(SystemParamConfig.Consumer_DataNode_ConnectString_Template, model);
}
public static ActionResult GetTokenInfo(Controller c, CertCenter.Models.DbModels.TokenType tokentype)
{
CertComm.ServerResult sr = new CertComm.ServerResult();
JsonResult jsonresult = new JsonResult();
jsonresult.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
jsonresult.Data = sr;
string token = c.Request.Params["token"];
if (string.IsNullOrEmpty(token))
{
sr.code = -905;
sr.msg = "token不能为空。";
return(jsonresult);
}
try
{
using (XXF.Db.DbConn PubConn = XXF.Db.DbConfig.CreateConn())
{
PubConn.Open();//打开基本
CertCenter.Models.DbModels.tb_token Ttoken = CertCenter.Models.TokenDal.Instance.GetToken(PubConn, token, tokentype);
if (Ttoken == null)
{
sr.code = -905;
#warning 修改提示
sr.msg = "token不存在";
return(jsonresult);
}
if (Ttoken.expires.CompareTo(DateTime.Now) <= 0)
{
sr.code = -905;
sr.msg = "token过期。";
return(jsonresult);
}
#region 修改名字,除去修改了用户名后不同步的bug
using (XXF.Db.DbConn priconn = XXF.Db.DbConn.CreateConn(GetConnStr(tokentype, Ttoken.userid)))
{
Ttoken.username = CertCenter.Models.AccountDal.Instance.getUserName(priconn, Ttoken.userid, tokentype);
}
CertCenter.Models.TokenDal.Instance.Edit(PubConn, Ttoken, tokentype);
#endregion
sr.code = 1;
sr.response = new { appid = Ttoken.appid, userid = Ttoken.userid, username = Ttoken.username, token = Ttoken.token, createtime = Ttoken.createtime.ToString("yyyy-MM-dd HH:mm:ss"), expires = Ttoken.expires.ToString("yyyy-MM-dd HH:mm:ss"), id = Ttoken.id };
return(jsonresult);
}
}
catch (Exception ex)
{
sr.code = -1;
sr.msg = ex.Message;
return(jsonresult);
}
}
public int UpdateTaskSuccess(DbConn PubConn, int id)
{
return SqlHelper.Visit(ps =>
{
string cmd = "update tb_task set taskerrorcount=0,taskruncount=taskruncount+1 where id=@id";
ps.Add("id", id);
return PubConn.ExecuteSql(cmd, ps.ToParameters());
});
}
public void DeleteAll(DbConn PubConn)
{
SqlHelper.Visit((ps) =>
{
string Sql = "truncate table tb_error";
int rev = PubConn.ExecuteSql(Sql, ps.ToParameters());
return rev;
});
}
public int UpdateLastStartTime(DbConn PubConn, int id, DateTime time)
{
return SqlHelper.Visit(ps =>
{
string cmd = "update tb_task set tasklaststarttime=@tasklaststarttime where id=@id";
ps.Add("id", id);
ps.Add("tasklaststarttime", time);
return PubConn.ExecuteSql(cmd, ps.ToParameters());
});
}
public bool DeleteOneNode(DbConn PubConn, int id)
{
return SqlHelper.Visit<bool>(ps =>
{
ps.Add("id", id);
string sql = "delete from tb_user where (select count(1) from tb_task where taskcreateuserid=@id)=0 and id=@id";
int i = PubConn.ExecuteSql(sql, ps.ToParameters());
return i > 0;
});
}
public virtual int SaveTempData(DbConn PubConn,int taskid,string json)
{
return SqlHelper.Visit(ps =>
{
string cmd = "update tb_tempdata set tempdatajson=@tempdatajson where taskid=@taskid";
ps.Add("taskid", taskid);
ps.Add("tempdatajson",json);
return PubConn.ExecuteSql(cmd, ps.ToParameters());
});
}
public bool ClientHeatbeat(DbConn PubConn, int mqpathid, long tempid)
{
return SqlHelper.Visit((ps) =>
{
ps.Add("@mqpathid", mqpathid); ps.Add("@tempid", tempid);
string Sql = "update tb_producter WITH (ROWLOCK) set lastheartbeat=getdate() where mqpathid=@mqpathid and tempid=@tempid";
int rev = PubConn.ExecuteSql(Sql, ps.ToParameters());
return true;
});
}
/// <summary>
/// Add
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(tb_config_model model, DbConn conn)
{
return SqlHelper.Visit((ps) =>
{
tb_config_model config = Get(conn, model.key);
if (config == null)
return Add(conn, model);
return false;
});
}
public bool DeleteNotOnLineByClientID(DbConn PubConn, int consumerclientid, int maxtimeoutsenconds)
{
return SqlHelper.Visit((ps) =>
{
ps.Add("@consumerclientid", consumerclientid); ps.Add("@maxtimeoutsenconds", maxtimeoutsenconds);
string Sql = "delete from tb_consumer where consumerclientid=@consumerclientid and DATEDIFF(S,lastheartbeat,getdate())>@maxtimeoutsenconds";
int rev = PubConn.ExecuteSql(Sql, ps.ToParameters());
return true;
});
}
public int UpdateTaskError(DbConn PubConn, int id, DateTime time)
{
return SqlHelper.Visit(ps =>
{
string cmd = "update tb_task set taskerrorcount=taskerrorcount+1,tasklasterrortime=@tasklasterrortime where id=@id";
ps.Add("id", id);
ps.Add("tasklasterrortime", time);
return PubConn.ExecuteSql(cmd, ps.ToParameters());
});
}
public bool Update(DbConn PubConn, tb_category_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
new ProcedureParameter("@categoryname", model.categoryname),
new ProcedureParameter("@id", model.id),
};
int rev = PubConn.ExecuteSql(@"update tb_category set categoryname=@categoryname where id=@id", Par);
return rev == 1;
}
public int GetVersion(DbConn PubConn, int taskid)
{
return SqlHelper.Visit(ps =>
{
ps.Add("taskid", taskid);
string sql = "select max(version) version from tb_version where taskid=@taskid";
int version = Convert.ToInt32(PubConn.ExecuteScalar(sql, ps.ToParameters()));
return version;
});
}
public virtual void Add2(DbConn PubConn, string client)
{
SqlHelper.Visit((ps) =>
{
ps.Add("@client", client);
PubConn.ExecuteSql(@"insert into tb_consumer_client(client,createtime)
values(@client,getdate())", ps.ToParameters());
return true;
});
}
public void UpdateCommandState(DbConn PubConn,int id,int commandstate)
{
SqlHelper.Visit(ps =>
{
ps.Add("@commandstate", commandstate);
ps.Add("@id", id);
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"update tb_command set commandstate=@commandstate where id=@id");
return PubConn.ExecuteSql(stringSql.ToString(),ps.ToParameters());
});
}
/// <summary>
/// 缓存
/// </summary>
/// <param name="conn"></param>
/// <param name="key"></param>
/// <returns></returns>
public IList<tb_config_model> GetCacheList(DbConn conn, string key)
{
IList<tb_config_model> cacheList = CacheManage.Retrieve(key) as List<tb_config_model>;
if (cacheList != null && cacheList.Count > 0)
{
return cacheList;
}
IList<tb_config_model> list = this.GetList(conn);
CacheManage.Add(key, list);
return list;
}
public List <DbModels.api> GetGradeApis(XXF.Db.DbConn PubConn, int apptype, int appgradeno)
{
string sql = "SELECT apiid,apptype,appgradeno,categoryid,apiname,apititle,area,controller,action,para,apidesc,freeze FROM api where apptype=" + apptype + " and appgradeno=" + appgradeno;
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, null);
List <DbModels.api> list = new List <DbModels.api>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
list.Add(CreateModel(dr));
}
return(list);
}
public static ActionResult RefreshToken(Controller c, CertCenter.Models.DbModels.TokenType tokentype)
{
CertComm.ServerResult r = new CertComm.ServerResult();
JsonResult jsonresult = new JsonResult();
jsonresult.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
jsonresult.Data = r;
string token = c.Request.Params["token"] ?? "";
if (string.IsNullOrEmpty(token))
{
r.code = -905;
r.msg = "token不能为空。";
return(jsonresult);
}
try
{
using (XXF.Db.DbConn PubConn = XXF.Db.DbConfig.CreateConn())
{
PubConn.Open();//打开基本
CertCenter.Models.DbModels.tb_token Ttoken = RefreshToken(PubConn, token, "", tokentype);
if (Ttoken == null)
{
r.code = -101;
r.msg = CertCenter.Models.AUTH_CODE_MSG.Get(r.code);
return(jsonresult);
}
#region 修改名字,除去修改了用户名后不同步的bug
using (XXF.Db.DbConn priconn = XXF.Db.DbConn.CreateConn(GetConnStr(tokentype, Ttoken.userid)))
{
Ttoken.username = CertCenter.Models.AccountDal.Instance.getUserName(priconn, Ttoken.userid, tokentype);
}
CertCenter.Models.TokenDal.Instance.Edit(PubConn, Ttoken, tokentype);
#endregion
r.code = 1;
r.response = Ttoken;
return(jsonresult);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>清除过期token</summary>
public static void DeleteExpiresToken()
{
try
{
string sql = @"delete from usertoken where expires<GETDATE()
delete from managetoken where expires<GETDATE()
delete from shoptoken where expires<GETDATE()";
using (XXF.Db.DbConn PubConn = XXF.Db.DbConfig.CreateConn())
{
PubConn.Open();//打开基本
PubConn.ExecuteSql(sql, null);
}
}
catch (Exception ex) { }
}
public CertCenter.Models.DbModels.tb_token GetToken(XXF.Db.DbConn PubConn, string userid, string appid, CertCenter.Models.DbModels.TokenType tokentype)
{
string sql = " select token,userid,id,username,appid,createtime,expires from " + tokentype.ToString() + " where userid=@userid and appid=@appid";
List <ProcedureParameter> Par = new List <ProcedureParameter>();
Par.Add(new ProcedureParameter("@userid", userid));
Par.Add(new ProcedureParameter("@appid", appid));
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, Par);
if (ds.Tables[0].Rows.Count != 1)
{
return(null);
}
return(CreateModel(ds.Tables[0].Rows[0]));
}
public static string GetConnStr(CertCenter.Models.DbModels.TokenType tokentype, string userid = "")
{
string constr = "";
switch (tokentype)
{
case CertCenter.Models.DbModels.TokenType.usertoken:
constr = XXF.Db.DbConfig.GetConfig("dydDbConn");
break;
case CertCenter.Models.DbModels.TokenType.shoptoken:
using (XXF.Db.DbConn PubConn = XXF.Db.DbConfig.CreateConn(XXF.Db.DbType.SQLSERVER, XXF.Db.DbConfig.GetConfig("dydDbConn")))
{
PubConn.Open();
string sql = "select f_shzh,f_shdqbm,f_shsj from tb_shop_area where f_shzh=@userid or f_shsj=@userid";
XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter();
para.Add("@userid", userid);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, para.ToParameters());
if (ds.Tables[0].Rows.Count == 0)
{
return("");
}
string dqbm = ds.Tables[0].Rows[0]["f_shdqbm"].ToString();
string t = XXF.Db.DbConfig.GetConfig("ShopAreaConnectString");
throw new Exception("no show");
// constr = XXF.Db.DbAreaRule.ShopAreaPartitionRule(t, Convert.ToInt32(dqbm));
}
break;
case CertCenter.Models.DbModels.TokenType.managetoken:
constr = XXF.Db.DbConfig.GetConfig("CrmDbConn");
break;
default:
break;
}
if (constr == "")
{
throw new Exception("车有问题。");
}
return(constr);
}
/// <summary>创建连接</summary>
/// <param name="dbtype">数据库类型</param>
/// <param name="ACn">已有的连接</param>
/// <returns></returns>
public static DbConn CreateConn(DbType dbtype, DbConnection ACn)
{
DbConn cn = null;
switch (dbtype)
{
case DbType.SQLSERVER:
cn = new DbConnSql();
break;
case DbType.ORACLE:
cn = new DbConnOracle();
break;
default:
throw new Exception("该数据库类型不适合使用CreateConn，请用new创建！");
}
cn._conn = ACn;
return(cn);
}
private static CertCenter.Models.DbModels.tb_token RefreshToken(XXF.Db.DbConn PubConn, string token, string username, CertCenter.Models.DbModels.TokenType tokentype)
{
CertCenter.Models.DbModels.tb_token Token = CertCenter.Models.TokenDal.Instance.GetToken(PubConn, token, tokentype);
if (Token == null)
{
return(null);
}
if (Token.expires.CompareTo(DateTime.Now) < 0)
{
DeleteToken(PubConn, token, tokentype);
return(null);
}
Token.expires = DateTime.Now.AddMinutes(CertCenter.Models.TokenDal.Instance.GetExpiresminutes(tokentype));
if (!string.IsNullOrEmpty(username))
{
Token.username = username;
}
CertCenter.Models.TokenDal.Instance.Edit(PubConn, Token, tokentype);
return(Token);
}
private static int ManageAccountVali(XXF.Db.DbConn PubConn, string userid, string md5pwd, out string username, out string id)
{
username = "";
id = "";
CertCenter.Models.DbModels.manage manager = CertCenter.Models.AccountDal.Instance.getManage(PubConn, userid);
if (manager == null)
{
return(-112);
}
else if (manager.freeze == 1)
{
return(-114);
}
else if (CertComm.Authcomm.ToMD5String(manager.pwd).ToUpper() != md5pwd.ToUpper())
{
return(-113);
}
username = manager.username;
id = manager.id;
return(1);
}
public static ActionResult GetToken(Controller c, CertCenter.Models.DbModels.TokenType tokentype)
{
JsonResult jsonr = new JsonResult();
jsonr.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
CertComm.ServerResult sr = new CertComm.ServerResult();
string msg = "";
jsonr.Data = sr;
//sr.code = 1;
//sr.response = new CertCenter.Models.DbModels.tb_token()
//{
// appid = "abc",
// token = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
// userid = "ad",
// id = "",
// username = "******"
//};
//return jsonr;
Dictionary <string, string> para = CertCenter.Models.CertCenterComm.GetRequestPara(c, new string[] { "appid", "userid", "timespan", "sign", "pwd" });
sr.code = CertCenter.Models.CertCenterComm.ValiFields(para, out msg);
if (sr.code < 0)
{
sr.msg = msg;
return(jsonr);
}
string userid = para["userid"];
string appid = para["appid"];
string pwd = para["pwd"];
string sign = para["sign"];
string timespan = para["timespan"];
if (!CertComm.Authcomm.TestTimeSpanOk(timespan, 10 * 60))
{
sr.code = -102;
sr.msg = CertCenter.Models.AUTH_CODE_MSG.Get(sr.code);
return(jsonr);
}
using (XXF.Db.DbConn PubConn = XXF.Db.DbConfig.CreateConn())
{
PubConn.Open();//打开基本
CertCenter.Models.DbModels.app appitem = CertCenter.Models.AppDal.Instance.GetAppInfo(PubConn, appid);
if (appitem == null || TokenTypeGetAppType(tokentype) != appitem.apptype)
{
sr.code = -103;
sr.msg = CertCenter.Models.AUTH_CODE_MSG.Get(sr.code);
return(jsonr);
}
else if (appitem.freeze == 1)
{
sr.code = -107;
sr.msg = CertCenter.Models.AUTH_CODE_MSG.Get(sr.code);
return(jsonr);
}
string nowsign = CertComm.Authcomm.ToSign(para, appitem.appsecret);
if (nowsign.ToLower() != para["sign"].ToLower())
{
sr.code = -104;
sr.msg = CertCenter.Models.AUTH_CODE_MSG.Get(sr.code);
return(jsonr);
}
string bsconstr = GetConnStr(tokentype, userid);
if (string.IsNullOrEmpty(bsconstr))
{
sr.code = -112;
sr.msg = CertCenter.Models.AUTH_CODE_MSG.Get(sr.code);
return(jsonr);
}
string username = "";
string identityid = "";
using (XXF.Db.DbConn dydpubConn = XXF.Db.DbConfig.CreateConn(XXF.Db.DbType.SQLSERVER, bsconstr))
{
dydpubConn.Open();
////用户相关验证
switch (tokentype)
{
case CertCenter.Models.DbModels.TokenType.managetoken:
sr.code = ManageAccountVali(dydpubConn, userid, pwd, out username, out identityid);
break;
case CertCenter.Models.DbModels.TokenType.shoptoken:
string tt = "";
sr.code = ShopAccountVali(dydpubConn, userid, pwd, out username, out tt, out identityid);
userid = tt;
break;
case CertCenter.Models.DbModels.TokenType.usertoken:
sr.code = UserAccountVali(dydpubConn, userid, pwd, out username, out identityid);
break;
default:
break;
}
}
if (sr.code < 0)
{
sr.msg = CertCenter.Models.AUTH_CODE_MSG.Get(sr.code);
return(jsonr);
}
CertCenter.Models.DbModels.tb_token Ttoken = CertCenter.Models.TokenDal.Instance.GetToken(PubConn, userid, appid, tokentype);
if (Ttoken == null || Ttoken.expires.CompareTo(DateTime.Now) < 0)
{
if (Ttoken != null)
{
DeleteToken(PubConn, Ttoken.token, tokentype);
}
Ttoken = new CertCenter.Models.DbModels.tb_token();
Ttoken.appid = appid;
Ttoken.token = Guid.NewGuid().ToString().Replace("-", "");
Ttoken.userid = userid;
Ttoken.id = identityid;
Ttoken.username = username;
CertCenter.Models.TokenDal.Instance.Add(PubConn, Ttoken, tokentype);
}
else
{
Ttoken = RefreshToken(PubConn, Ttoken.token, username, tokentype);
}
sr.code = 1;
sr.response = new { appid = Ttoken.appid, userid = Ttoken.userid, username = Ttoken.username, token = Ttoken.token, createtime = Ttoken.createtime.ToString("yyyy-MM-dd HH:mm:ss"), expires = Ttoken.expires.ToString("yyyy-MM-dd HH:mm:ss"), id = Ttoken.id };
return(jsonr);
}
}
private static void DeleteToken(XXF.Db.DbConn PubConn, string token, CertCenter.Models.DbModels.TokenType tokentype)
{
CertCenter.Models.TokenDal.Instance.Delete(PubConn, token, tokentype);
}
public static ActionResult GetApiList(Controller c, CertCenter.Models.DbModels.TokenType tokentype)
{
JsonResult jsonresult = new JsonResult();
jsonresult.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
CertComm.ServerResult r = new CertComm.ServerResult();
jsonresult.Data = r;
string token = c.Request.Params["token"];
if (string.IsNullOrEmpty(token))
{
r.code = -905;
r.msg = "token不能为空。";
return(jsonresult);
}
using (XXF.Db.DbConn PubConn = XXF.Db.DbConfig.CreateConn())
{
PubConn.Open();//打开基本
#region token
CertCenter.Models.DbModels.tb_token Token = CertCenter.Models.TokenDal.Instance.GetToken(PubConn, token, tokentype);
if (Token == null || Token.expires.CompareTo(DateTime.Now) < 0)
{
DeleteToken(PubConn, token, tokentype);
r.code = -101;
r.msg = CertCenter.Models.AUTH_CODE_MSG.Get(r.code);
return(jsonresult);
}
#endregion
List <CertCenter.Models.DbModels.api> apis = new List <CertCenter.Models.DbModels.api>();
CertCenter.Models.DbModels.app appinfo = CertCenter.Models.AppDal.Instance.GetAppInfo(PubConn, Token.appid);
if (appinfo == null)
{
DeleteToken(PubConn, token, tokentype);
r.code = -103;
r.msg = CertCenter.Models.AUTH_CODE_MSG.Get(r.code);
return(jsonresult);
}
int apptype = 0;
switch (tokentype)
{
case CertCenter.Models.DbModels.TokenType.managetoken:
apptype = 0;
break;
case CertCenter.Models.DbModels.TokenType.shoptoken:
apptype = 2;
break;
case CertCenter.Models.DbModels.TokenType.usertoken:
apptype = 1;
break;
}
for (int i = appinfo.appgradeno; i > 0; i--)
{
apis.AddRange(CertCenter.Models.ApiDal.Instance.GetGradeApis(PubConn, apptype, i));
}
r.code = 1;
r.response = apis;
return(jsonresult);
}
}
public static ActionResult TestAuth(Controller c, CertCenter.Models.DbModels.TokenType tokentype)
{
JsonResult jsonresult = new JsonResult();
jsonresult.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
CertComm.ServerResult r = new CertComm.ServerResult();
jsonresult.Data = r;
string token = c.Request.Params["token"];
string _controller = c.Request.Params["controller"];
string _area = c.Request.Params["area"];
string _action = c.Request.Params["action"];
string _apiname = c.Request.Params["apiname"];
int _apiid = 0;
int.TryParse(c.Request.Params["apiid"] ?? "", out _apiid);
if (string.IsNullOrEmpty(token))
{
r.code = -905;
r.msg = "token不能为空。";
return(jsonresult);
}
using (XXF.Db.DbConn PubConn = XXF.Db.DbConfig.CreateConn())
{
PubConn.Open();//打开基本
#region token
CertCenter.Models.DbModels.tb_token Ttoken = CertCenter.Models.TokenDal.Instance.GetToken(PubConn, token, tokentype);
if (Ttoken == null || Ttoken.expires.CompareTo(DateTime.Now) < 0)
{
DeleteToken(PubConn, token, tokentype);
r.code = -101;
r.msg = CertCenter.Models.AUTH_CODE_MSG.Get(r.code);
return(jsonresult);
}
#endregion
//#region user
//#endregion
#region //应用
CertCenter.Models.DbModels.app appitem = CertCenter.Models.AppDal.Instance.GetAppInfo(PubConn, Ttoken.appid);
if (appitem == null)
{
r.code = -103;
r.msg = CertCenter.Models.AUTH_CODE_MSG.Get(r.code);
return(jsonresult);
}
else if (appitem.freeze == 1)
{
r.code = -107;
r.msg = CertCenter.Models.AUTH_CODE_MSG.Get(r.code);
return(jsonresult);
}
#endregion
#region 接口
int apptype = TokenTypeGetAppType(tokentype);
CertCenter.Models.DbModels.api apiinfo = null;
if (_apiid > 0)
{
apiinfo = CertCenter.Models.ApiDal.Instance.Get(PubConn, _apiid);
}
else if (!string.IsNullOrEmpty(_apiname))
{
apiinfo = CertCenter.Models.ApiDal.Instance.Get(PubConn, _apiname, apptype);
}
else
{
apiinfo = CertCenter.Models.ApiDal.Instance.Get(PubConn, _area, _controller, _action, apptype);
}
if (apiinfo == null)
{
r.code = -108;
r.msg = CertCenter.Models.AUTH_CODE_MSG.Get(r.code);
return(jsonresult);
}
if (apiinfo.freeze == 1)
{
r.code = -106;
r.msg = CertCenter.Models.AUTH_CODE_MSG.Get(r.code);
return(jsonresult);
}
if (appitem.appgradeno < appitem.appgradeno || apptype != apiinfo.apptype)
{
r.code = -105;
r.msg = CertCenter.Models.AUTH_CODE_MSG.Get(r.code);
return(jsonresult);
}
#endregion
r.code = 1;
r.response = new { appid = Ttoken.appid, userid = Ttoken.userid, username = Ttoken.username, token = Ttoken.token, createtime = Ttoken.createtime.ToString("yyyy-MM-dd HH:mm:ss"), expires = Ttoken.expires.ToString("yyyy-MM-dd HH:mm:ss") };
return(jsonresult);
}
}