public int AddApp(XXF.Db.DbConn PubConn, Models.DbModels.app model)
{
if (string.IsNullOrEmpty(model.appid))
{
model.appid = XXF.Db.LibString.MakeRandomNumber(16).ToLower();
}
if (ExitAppid(PubConn, model.appid))
{
return -2;
}
if (string.IsNullOrEmpty(model.appsecret))
{
model.appsecret = Guid.NewGuid().ToString().Replace("-", "");
}
string sql = "insert into app(appid,appname,apptype,appgradeno,appsecret,appdesc,freeze) values(@appid,@appname,@apptype,@appgradeno,@appsecret,@appdesc,@freeze)";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@appid", model.appid);
para.Add("@appsecret", model.appsecret);
para.Add("@appname", model.appname);
para.Add("@apptype", model.apptype);
para.Add("@appgradeno", model.appgradeno);
para.Add("@freeze", model.freeze);
para.Add("@appdesc", model.appdesc ?? "");
int r = PubConn.ExecuteSql(sql, para.ToParameters());
return r;
}
/// <summary>根据userid得到管理员工信息</summary>
public DbModels.manage getManage(XXF.Db.DbConn PubConn, string userid)
{
string sql = "select ygbh,ygmc,ygmm,ygzt,sfsc from tb_userinfo where ygbh=@userid";
DataSet ds = new DataSet();
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@userid", userid);
PubConn.SqlToDataSet(ds, sql, para.ToParameters());
if (ds.Tables[0].Rows.Count != 1)
return null;
DbModels.manage model = new DbModels.manage();
model.userid = ds.Tables[0].Rows[0]["ygbh"].ToString();
model.id = model.userid;
model.username = ds.Tables[0].Rows[0]["ygmc"].ToString();
model.pwd = privateToDeDES(ds.Tables[0].Rows[0]["ygmm"].ToString());
model.freeze = Convert.ToInt32(ds.Tables[0].Rows[0]["sfsc"]);
if (model.freeze == 0)
{
model.freeze = 1;
}
else
{
model.freeze = 0;
}
return model;
}
public bool ExitAppid(XXF.Db.DbConn PubConn, string appid)
{
string sql = "select count(*) from app where appid=@appid";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@appid", appid);
int r = (int)PubConn.ExecuteScalar(sql, para.ToParameters());
return r == 1;
}
public int DeletApp(XXF.Db.DbConn PubConn, string appid)
{
string sql = "delete from app where appid=@appid";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@appid", appid);
int r = PubConn.ExecuteSql(sql, para.ToParameters());
return r;
}
public int DeleteCategory(XXF.Db.DbConn PubConn, int apptype, int categoryid)
{
string countsql = " select COUNT(*) from gradepermission where apptype=" + apptype + " and categoryid=" + categoryid + "";
int c = (int)PubConn.ExecuteScalar(countsql, null);
if (c != 0)
return -2;
string sql = "delete from appcategory where apptype=@apptype and categoryid=@categoryid";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@apptype", apptype);
para.Add("@categoryid", categoryid);
int r = PubConn.ExecuteSql(sql, para.ToParameters());
return r;
}
public int DeleteGrade(XXF.Db.DbConn PubConn, int apptype, int appgradeno)
{
string countsql = " select COUNT(*) from api where apptype=" + apptype + " and appgradeno=" + appgradeno + "";
int c = (int)PubConn.ExecuteScalar(countsql, null);
if (c != 0)
return -2;
string sql = "delete from appgrade where apptype=@apptype and appgradeno=@appgradeno";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@apptype", apptype);
para.Add("@appgradeno", appgradeno);
int r = PubConn.ExecuteSql(sql, para.ToParameters());
return r;
}
public int Addcategory(XXF.Db.DbConn PubConn, Models.DbModels.appcategory model)
{
model.categorydesc = model.categorydesc ?? "";
string getcatid = "select isnull(max(categoryid),0)+1 from appcategory where apptype=" + model.apptype;
model.categoryid = Convert.ToInt32(PubConn.ExecuteScalar(getcatid, null));
string addsql = "insert into appcategory(apptype,categoryid,categorytitle,categorydesc) values(@apptype,@categoryid,@categorytitle,@categorydesc)";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@apptype", model.apptype);
para.Add("@categoryid", model.categoryid);
para.Add("@categorytitle", model.categorytitle);
para.Add("@categorydesc", model.categorydesc);
int r = PubConn.ExecuteSql(addsql, para.ToParameters());
return r;
}
public int AddGrade(XXF.Db.DbConn PubConn, Models.DbModels.appgrade model)
{
model.appgradedesc = model.appgradedesc ?? "";
string getcatid = "select isnull(max(appgradeno),0)+1 from appgrade where apptype=" + model.apptype;
model.appgradeno = Convert.ToInt32(PubConn.ExecuteScalar(getcatid, null));
string addsql = "insert into appgrade(apptype,appgradeno,appgradename,appgradedesc) values(@apptype,@appgradeno,@appgradename,@appgradedesc)";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@apptype", model.apptype);
para.Add("@appgradeno", model.appgradeno);
para.Add("@appgradename", model.appgradename);
para.Add("@appgradedesc", model.appgradedesc);
int r = PubConn.ExecuteSql(addsql, para.ToParameters());
return r;
}
/// <summary>根据userid得到管理员工信息</summary>
public DbModels.manage getShop(XXF.Db.DbConn PubConn, string userid)
{
string sql = "SELECT f_shzh,f_shsj,f_shmc,f_dlmm,f_sfdj FROM [tb_shop] where f_shzh=@userid or f_shsj=@userid";
DataSet ds = new DataSet();
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@userid", userid);
PubConn.SqlToDataSet(ds, sql, para.ToParameters());
if (ds.Tables[0].Rows.Count != 1)
return null;
DbModels.manage model = new DbModels.manage();
model.userid = ds.Tables[0].Rows[0]["f_shzh"].ToString();
model.id = model.userid;
model.username = ds.Tables[0].Rows[0]["f_shmc"].ToString();
model.pwd = privateToDeDES(ds.Tables[0].Rows[0]["f_dlmm"].ToString());
model.freeze = Convert.ToInt32(ds.Tables[0].Rows[0]["f_sfdj"]);
return model;
}
/// <summary></summary>
public DbModels.manage getUser(XXF.Db.DbConn PubConn, string userid)
{
string sql = "select f_id, f_yhzh,f_yhxm,f_dlmm,f_sfdj from tb_customer where f_yhzh=@userid";
DataSet ds = new DataSet();
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@userid", userid);
PubConn.SqlToDataSet(ds, sql, para.ToParameters());
if (ds.Tables[0].Rows.Count != 1)
return null;
DbModels.manage model = new DbModels.manage();
model.userid = ds.Tables[0].Rows[0]["f_yhzh"].ToString();
model.id = ds.Tables[0].Rows[0]["f_id"].ToString();
model.username = ds.Tables[0].Rows[0]["f_yhxm"].ToString();
model.pwd = privateToDeDES(ds.Tables[0].Rows[0]["f_dlmm"].ToString());
model.freeze = Convert.ToInt32(ds.Tables[0].Rows[0]["f_sfdj"]);
return model;
}
public static void AddLog(XXF.Db.DbConn PubConn,DbModels.apilog log)
{
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@reqsource", log.reqsource);
para.Add("@url", log.url);
para.Add("@reqpara", log.reqpara);
para.Add("@token", log.token);
para.Add("@appid", log.appid);
// para.Add("@appname", log.appname);
para.Add("@userid", log.userid);
para.Add("@username", log.username);
para.Add("@reqdate", log.reqdate);
para.Add("@opecontent", log.opecontent);
string sql = "INSERT INTO operationlog (reqsource ,url ,reqpara ,token ,appid ,appname,userid ,username ,reqdate ,opecontent)"+
"VALUES (@reqsource,@url, @reqpara,@token,@appid,@appname,@userid, @username,@reqdate,@opecontent)";
PubConn.ExecuteSql(sql, para.ToParameters());
}
internal string getUserName(XXF.Db.DbConn PubConn, string userid, DbModels.TokenType tokentype)
{
CertCenter.Models.DbModels.manage model = null;
switch (tokentype)
{
case DbModels.TokenType.usertoken:
model = getUser(PubConn, userid);break;
case DbModels.TokenType.managetoken:
model = getManage(PubConn, userid);
break;
case DbModels.TokenType.shoptoken:
model = getShop(PubConn, userid);
break;
}
if (model == null)
return "";
return model.username;
}
public Models.DbModels.appcategory GetCategoryInfo(XXF.Db.DbConn PubConn, int typeid, int categoryid)
{
string sql = "select apptype,categoryid,categorytitle, categorydesc from appcategory where apptype=@apptype and categoryid=@categoryid";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@apptype", typeid);
para.Add("@categoryid", categoryid);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, para.ToParameters());
if (ds.Tables[0].Rows.Count == 0)
return null;
DataRow dr = ds.Tables[0].Rows[0];
DbModels.appcategory item = new DbModels.appcategory();
item.apptype = Convert.ToInt32(dr["apptype"]);
item.categoryid = Convert.ToInt32(dr["categoryid"]);
item.categorytitle = dr["categorytitle"].ToString();
item.categorydesc = dr["categorydesc"].ToString();
return item;
}
public DbModels.appgrade GetGradeInfo(XXF.Db.DbConn PubConn, int apptype, int appgradeno)
{
string sql = "select apptype,appgradeno,appgradename,appgradedesc from appgrade where apptype=@apptype and appgradeno=@appgradeno ";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@apptype", apptype);
para.Add("@appgradeno", appgradeno);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, para.ToParameters());
if (ds.Tables[0].Rows.Count == 0)
return null;
DataRow dr = ds.Tables[0].Rows[0];
DbModels.appgrade item = new DbModels.appgrade();
item.apptype = Convert.ToInt32(dr["apptype"]);
item.appgradeno = Convert.ToInt32(dr["appgradeno"]);
item.appgradename = dr["appgradename"].ToString();
item.appgradedesc = dr["appgradedesc"].ToString();
return item;
}
public List<DbModels.appgrade> GetGrades(XXF.Db.DbConn PubConn, int typ)
{
List<DbModels.appgrade> listmodel = new List<DbModels.appgrade>();
string sql = "select apptype,appgradeno,appgradename,appgradedesc from appgrade where apptype=@apptype order by appgradeno asc";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@apptype", typ);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, para.ToParameters());
foreach (DataRow dr in ds.Tables[0].Rows)
{
DbModels.appgrade item = new DbModels.appgrade();
item.apptype = Convert.ToInt32(dr["apptype"]);
item.appgradeno = Convert.ToInt32(dr["appgradeno"]);
item.appgradename = dr["appgradename"].ToString();
item.appgradedesc = dr["appgradedesc"].ToString();
listmodel.Add(item);
}
return listmodel;
}
public List<Models.DbModels.appcategory> GetAppTypeCategorys(XXF.Db.DbConn PubConn, int typeid)
{
List<DbModels.appcategory> listmodel = new List<DbModels.appcategory>();
string sql = "select apptype,categoryid,categorytitle, categorydesc from appcategory where apptype=@apptype order by categoryid asc";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@apptype", typeid);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, para.ToParameters());
foreach (DataRow dr in ds.Tables[0].Rows)
{
DbModels.appcategory item = new DbModels.appcategory();
item.apptype = Convert.ToInt32(dr["apptype"]);
item.categoryid = Convert.ToInt32(dr["categoryid"]);
item.categorytitle = dr["categorytitle"].ToString();
item.categorydesc = dr["categorydesc"].ToString();
listmodel.Add(item);
}
return listmodel;
}
public int UpdateApp(XXF.Db.DbConn PubConn, Models.DbModels.app model)
{
string sql = "update app set appname=@appname,appsecret=@appsecret,appdesc=@appdesc ,freeze=@freeze ,apptype = @apptype,appgradeno =@appgradeno where appid=@appid";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@appid", model.appid);
para.Add("@appsecret", model.appsecret);
para.Add("@appname", model.appname);
para.Add("@apptype", model.apptype);
para.Add("@appgradeno", model.appgradeno);
para.Add("@freeze", model.freeze);
para.Add("@appdesc", model.appdesc ?? "");
int r = PubConn.ExecuteSql(sql, para.ToParameters());
return r;
}
public List<Models.DbModels.app> GetList(XXF.Db.DbConn PubConn, int pno, int pagesize, string keywords, out int totalcount)
{
totalcount = 0;
List<Models.DbModels.app> listapp = new List<DbModels.app>();
string sql = string.Format(basesql, " ROW_NUMBER() over (order by appid) as rownum, appid,appname,appsecret,app.apptype,appgrade.appgradeno,appgrade.appgradename,appdesc,freeze "); ;
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
string con = "";
if (!string.IsNullOrEmpty(keywords))
{
con = " and app.appid like '%' + @keywords + '%' or app.appname like '%' + @keywords + '%' or appname like '%' + @keywords + '%' ";
para.Add("@keywords", keywords);
sql = sql + con;
}
string querysql = string.Concat("select A.* from (", sql, ") A ", " where A.rownum between ", (pno - 1) * pagesize + 1, " and ", pno * pagesize);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, querysql, para.ToParameters());
foreach (DataRow dr in ds.Tables[0].Rows)
{
listapp.Add(FillModel(dr));
}
totalcount = (int)PubConn.ExecuteScalar(string.Format(basesql, " count(*) ") + con, para.ToParameters());
return listapp;
}
private static int UserAccountVali(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.getUser(PubConn, userid);
if (manager == null)
{
return -112;
}
else if (manager.freeze == 1)
{
return -114;
}
else if (CertComm.Authcomm.ToMD5String(manager.pwd) != md5pwd)
{
return -113;
}
username = manager.username;
id = manager.id;
return 1;
}
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 void DeleteToken(XXF.Db.DbConn PubConn, string token, CertCenter.Models.DbModels.TokenType tokentype)
{
CertCenter.Models.TokenDal.Instance.Delete(PubConn, token, tokentype);
}
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 int UpdateCategory(XXF.Db.DbConn PubConn, Models.DbModels.appcategory model)
{
model.categorydesc = model.categorydesc ?? "";
string sql = "update appcategory set categorytitle=@categorytitle,categorydesc=@categorydesc where apptype=@apptype and categoryid=@categoryid";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@apptype", model.apptype);
para.Add("@categoryid", model.categoryid);
para.Add("@categorytitle", model.categorytitle);
para.Add("@categorydesc", model.categorydesc);
int r = PubConn.ExecuteSql(sql, para.ToParameters());
return r;
}
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 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 int UpdateGrade(XXF.Db.DbConn PubConn, Models.DbModels.appgrade model)
{
model.appgradedesc = model.appgradedesc ?? "";
string sql = "update appgrade set appgradename=@appgradename,appgradedesc=@appgradedesc where apptype=@apptype and appgradeno=@appgradeno";
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@apptype", model.apptype);
para.Add("@appgradeno", model.appgradeno);
para.Add("@appgradename", model.appgradename);
para.Add("@appgradedesc", model.appgradedesc);
int r = PubConn.ExecuteSql(sql, para.ToParameters());
return r;
}
//get
public Models.DbModels.app GetAppInfo(XXF.Db.DbConn PubConn, string appid)
{
string sql = string.Format(basesql, " appid,appname,appsecret,app.apptype,appgrade.appgradeno,appgrade.appgradename,appdesc,freeze ");
XXF.Db.SimpleProcedureParameter para = new XXF.Db.SimpleProcedureParameter();
para.Add("@appid", appid);
sql += " and appid=@appid";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, para.ToParameters());
if (ds.Tables[0].Rows.Count == 0)
return null;
return FillModel(ds.Tables[0].Rows[0]);
}
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;
}
