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 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;
}
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;
}
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 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;
}