public List <object> GetArticleList(string userid, int pagesize, int pagecurrent)
{
//userid = "admin";//TODO
int iFrom = (pagecurrent - 1) * pagesize + 1;
int iTo = pagecurrent * pagesize;
if (string.IsNullOrEmpty(userid))
{
userid = "";
}
List <object> Ret = new List <object>();
try
{
string sql = " ";
sql = sql + " with cte as( ";
sql = sql + " select id0=ROW_NUMBER()over(order by jc_content.sort_date desc) ";
sql = sql + " ,s.protocol,s.domain ,ch.channel_path,jc_content.content_id,s.dynamic_suffix,e.title,jc_content.sort_date, config.context_path ";
sql = sql + " , case when config.port=null or config.port = '' or config.port = '80' then '' ";
sql = sql + " else ':'+cast(config.port as varchar(10)) ";
sql = sql + " end as port ";
sql = sql + " from jc_content ";
sql = sql + " left join jc_config config on 1=1 ";
sql = sql + " LEFT JOIN jc_user u ";
sql = sql + " on u.user_id=jc_content.user_id ";
sql = sql + " LEFT JOIN jc_channel ch ";
sql = sql + " on ch.channel_id=jc_content.channel_id ";
sql = sql + " LEFT JOIN jc_site s ";
sql = sql + " ON s.site_id=jc_content.site_id ";
sql = sql + " LEFT JOIN jc_content_ext e ";
sql = sql + " ON e.content_id=jc_content.content_id ";
sql = sql + " where u.username='******' ";
sql = sql + " ) ";
sql = sql + " select cte.* ";
sql = sql + " from cte ";
sql = sql + " where (cte.id0 BETWEEN " + iFrom + " AND " + iTo + ") ";
//调用CMS的数据库
CmsDbOperation db = new CmsDbOperation();
DataSet ds = new DataSet();
ds = db.GetDataSet(sql);
//保存数据表并返回
Ret.Add(ds);
return(Ret);
//return "{\"result\":\"success\",\"list\":" + ToJosn.wxListToJson(Ret) + "}";
}
catch (Exception ex)
{
Ret.Add("state:0,msg:获取数据错误！");
return(Ret);
}
}
public int GetArticleListCount(string userid)
{
//userid = "admin";//TODO
if (string.IsNullOrEmpty(userid))
{
userid = "";
}
List <object> Ret = new List <object>();
try
{
string sql = " ";
sql = sql + " with cte as( ";
sql = sql + " select id0=ROW_NUMBER()over(order by jc_content.sort_date desc) ";
sql = sql + " ,s.protocol,s.domain ,ch.channel_path,jc_content.content_id,s.dynamic_suffix,e.title,jc_content.sort_date, config.context_path ";
sql = sql + " , case when config.port=null or config.port = '' or config.port = '80' then '' ";
sql = sql + " else config.port ";
sql = sql + " end as port ";
sql = sql + " from jc_content ";
sql = sql + " left join jc_config config on 1=1 ";
sql = sql + " LEFT JOIN jc_user u ";
sql = sql + " on u.user_id=jc_content.user_id ";
sql = sql + " LEFT JOIN jc_channel ch ";
sql = sql + " on ch.channel_id=jc_content.channel_id ";
sql = sql + " LEFT JOIN jc_site s ";
sql = sql + " ON s.site_id=jc_content.site_id ";
sql = sql + " LEFT JOIN jc_content_ext e ";
sql = sql + " ON e.content_id=jc_content.content_id ";
sql = sql + " where u.username='******' ";
sql = sql + " ) ";
sql = sql + " select cte.* ";
sql = sql + " from cte ";
//调用CMS的数据库
CmsDbOperation db = new CmsDbOperation();
DataSet ds = new DataSet();
ds = db.GetDataSet(sql);
//返回
return(ds.Tables[0].Rows.Count);
}
catch (Exception ex)
{
Ret.Add("state:0,msg:获取数据错误！");
return(0);
}
}