/// <summary>
/// 获取目录中贴数，返回四个表，分别为总主题数，总回帖评论数，24小时内新主题数，24小时内新回帖评论数
/// </summary>
/// <returns></returns>
public DataSet GetCatesPostNum()
{
string cachyname = "QACatePostNum";
if (HttpRuntime.Cache[cachyname] == null)
{
DataSet tables = new DataSet();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"
SELECT COUNT(*) as questnum
,[CateSysNo],(case EndTime when null then '0' else '1' end) as IsSolved
FROM [QA_Question] where dr=0 group by [CateSysNo],(case EndTime when null then '0' else '1' end);
select * from
(SELECT COUNT(*) as AnswerNum
,[CateSysNo]
FROM QA_Answer left join [QA_Question] on QA_Answer.QuestionSysNo = QA_Question.SysNo where QA_Answer.dr=0 group by [CateSysNo]
) as PP,
(SELECT COUNT(*) as CommentNum
,[CateSysNo]
FROM QA_Comment left join [QA_Question] on QA_Comment.QuestionSysNo = QA_Question.SysNo where QA_Comment.dr=0 group by [CateSysNo]
) as KK where PP.CateSysNo = KK.CateSysNo;
SELECT COUNT(*) as questnum
,[CateSysNo]
FROM [QA_Question] where datediff(day,TS,getdate())<1 and dr=0 group by [CateSysNo];
select * from
(SELECT COUNT(*) as AnswerNum
,[CateSysNo]
FROM QA_Answer left join [QA_Question] on QA_Answer.QuestionSysNo = QA_Question.SysNo where datediff(day,QA_Answer.TS,getdate())<1 and QA_Answer.dr=0 group by [CateSysNo]
) as PP,
(SELECT COUNT(*) as CommentNum
,[CateSysNo]
FROM QA_Comment left join [QA_Question] on QA_Comment.QuestionSysNo = QA_Question.SysNo where datediff(day,QA_Comment.TS,getdate())<1 and QA_Comment.dr=0 group by [CateSysNo]
) as KK where PP.CateSysNo = KK.CateSysNo");
try
{
tables = data.CmdtoDataSet(builder.ToString());
HttpRuntime.Cache.Insert(cachyname, tables, null, DateTime.Now.AddHours(1), TimeSpan.Zero,
System.Web.Caching.CacheItemPriority.High, null);
}
catch (Exception exception)
{
throw exception;
}
}
}
return (HttpRuntime.Cache[cachyname] as DataSet).Copy();
}
public DataSet GetSameFamous(AstroMod m_astro)
{
DataSet m_ds = new DataSet();
using (SQLData data = new SQLData())
{
string sql = "select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where degree=" + m_astro.Stars[0].Degree + " and Constellation=" + (int)m_astro.Stars[0].Constellation+ " and star=" + (int)PublicValue.AstroStar.Sun + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where degree=" + m_astro.Stars[1].Degree + " and Constellation=" + (int)m_astro.Stars[1].Constellation + " and star=" + (int)PublicValue.AstroStar.Moo + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where degree=" + m_astro.Stars[2].Degree + " and Constellation=" + (int)m_astro.Stars[2].Constellation + " and star=" + (int)PublicValue.AstroStar.Mer + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where degree=" + m_astro.Stars[3].Degree + " and Constellation=" + (int)m_astro.Stars[3].Constellation + " and star=" + (int)PublicValue.AstroStar.Ven + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where degree=" + m_astro.Stars[4].Degree + " and Constellation=" + (int)m_astro.Stars[4].Constellation + " and star=" + (int)PublicValue.AstroStar.Mar + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[0].Gong + " and star=" + (int)PublicValue.AstroStar.Sun + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[1].Gong + " and star=" + (int)PublicValue.AstroStar.Moo + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[2].Gong + " and star=" + (int)PublicValue.AstroStar.Mer + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[3].Gong + " and star=" + (int)PublicValue.AstroStar.Ven + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[4].Gong + " and star=" + (int)PublicValue.AstroStar.Mar + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[5].Gong + " and star=" + (int)PublicValue.AstroStar.Jup + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[6].Gong + " and star=" + (int)PublicValue.AstroStar.Sat + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[7].Gong + " and star=" + (int)PublicValue.AstroStar.Ura + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[8].Gong + " and star=" + (int)PublicValue.AstroStar.Nep + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[9].Gong + " and star=" + (int)PublicValue.AstroStar.Plu + " and IsTop=1;";
try
{
m_ds = data.CmdtoDataSet(sql);
}
catch (Exception exception)
{
//throw exception;
}
return m_ds;
}
}
public DataSet GetRecommendList(int count)
{
string cachyname = "CMSRecommend";
if (HttpRuntime.Cache[cachyname + "-" + count.ToString()] == null)
{
DataSet tables = new DataSet();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
//最新
builder.Append(@"SELECT top " + count + @" [SysNo]
,[ArticleSysNo]
,[CateSysNo]
,[Source]
,[DR]
,[TS]
,[OrderID]
,[Title]
,[CustomerSysNo]
,[KeyWords]
,[Limited]
,[ReadCount]
,[Description]
,[Cost]
FROM [CMS_ArticleView] where dr=").Append((int)AppEnum.State.normal);
builder.Append(" order by TS desc;");
//推荐
builder.Append(@"SELECT top " + count + @" [SysNo]
,[ArticleSysNo]
,[CateSysNo]
,[Source]
,[DR]
,[TS]
,[OrderID]
,[Title]
,[CustomerSysNo]
,[KeyWords]
,[Limited]
,[ReadCount]
,[Description]
,[Cost]
FROM [CMS_ArticleView] where dr=").Append((int)AppEnum.State.normal);
builder.Append(" order by [OrderID] desc;");
//最热
builder.Append(@"SELECT top " + count + @" [SysNo]
,[ArticleSysNo]
,[CateSysNo]
,[Source]
,[DR]
,[TS]
,[OrderID]
,[Title]
,[CustomerSysNo]
,[KeyWords]
,[Limited]
,[ReadCount]
,[Description]
,[Cost]
FROM [CMS_ArticleView] where dr=").Append((int)AppEnum.State.normal);
builder.Append(" order by [ReadCount] desc;");
try
{
tables = data.CmdtoDataSet(builder.ToString());
HttpRuntime.Cache.Insert(cachyname + "-" + count.ToString(), tables, null, DateTime.Now.AddHours(2), TimeSpan.Zero,
System.Web.Caching.CacheItemPriority.High, null);
}
catch (Exception exception)
{
throw exception;
}
}
}
return (HttpRuntime.Cache[cachyname + "-" + count.ToString()] as DataSet).Copy();
}
public Dictionary<int, string[]> GetNeighbour(int SysNo, int Cate)
{
Dictionary<int, string[]> ret = new Dictionary<int, string[]>();
DataSet m_dt = new DataSet();
string strsql = "select top 1 sysno,ArticleSysno,title,OrderID from CMS_ArticleView where (OrderID>(select OrderID from CMS_ArticleView where sysno=" + SysNo + ") or sysno<" + SysNo + ") and CateSysNo=" + Cate + " order by OrderID asc,sysno desc;" +
" select top 1 sysno,ArticleSysno,title,OrderID from CMS_ArticleView where (OrderID<(select OrderID from CMS_ArticleView where sysno=" + SysNo + ") or sysno>" + SysNo + ") and CateSysNo=" + Cate + " order by OrderID desc,sysno asc";
using (SQLData m_data = new SQLData())
{
try
{
m_dt = m_data.CmdtoDataSet(strsql);
}
catch (Exception exception)
{
throw exception;
}
}
if(m_dt.Tables[0].Rows.Count>0)
{
string[] tmp = new string[3];
tmp[0] = m_dt.Tables[0].Rows[0]["SysNo"].ToString();
tmp[1] = m_dt.Tables[0].Rows[0]["title"].ToString();
tmp[2] = m_dt.Tables[0].Rows[0]["OrderID"].ToString();
ret.Add(0, tmp);
}
if(m_dt.Tables[1].Rows.Count>0)
{
string[] tmp = new string[3];
tmp[0] = m_dt.Tables[1].Rows[0]["SysNo"].ToString();
tmp[1] = m_dt.Tables[1].Rows[0]["title"].ToString();
tmp[2] = m_dt.Tables[1].Rows[0]["OrderID"].ToString();
ret.Add(1, tmp);
}
return ret;
}
public DataSet GetNeighbor(int SysNo, int cate)
{
DataSet m_dt = new DataSet();
string strsql = "select * from (select top 1 [SysNo],[Title] from QA_Question where [CateSysNo] = " + cate + " and sysno<"+SysNo+"order by sysno desc) as T "+
" union select top 1 [SysNo],[Title] from QA_Question where sysno<" + SysNo + "order by sysno desc;" +
"select * from (select top 1 [SysNo],[Title] from QA_Question where [CateSysNo] = " + cate + " and sysno>" + SysNo + "order by sysno asc) as P " +
" union select top 1 [SysNo],[Title] from QA_Question where sysno>" + SysNo + "order by sysno asc;";
using (SQLData m_data = new SQLData())
{
try
{
m_dt = m_data.CmdtoDataSet(strsql);
}
catch (Exception exception)
{
throw exception;
}
}
return m_dt;
}
public int AddExp(int exp, int sysno)
{
int ret = 0;
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("update USR_Customer set Exp = Exp+(").Append(exp).Append(") where SysNo=").Append(sysno)
.Append(";select Exp from USR_Customer where SysNo=").Append(sysno)
.Append(";select * from USR_Grade where SysNo in (select GradeSysNo from USR_Customer where SysNo=").Append(sysno).Append(")");
try
{
DataSet m_ds = data.CmdtoDataSet(builder.ToString());
ret = int.Parse(m_ds.Tables[0].Rows[0]["Exp"].ToString());
DataTable m_grade = USR_GradeBll.GetInstance().GetList();
for (int i = 0; i < m_grade.Rows.Count; i++)
{
if (int.Parse(m_grade.Rows[i]["LevelNum"].ToString()) == int.Parse(m_ds.Tables[1].Rows[0]["LevelNum"].ToString()) + 1)
{
if (ret >= int.Parse(m_grade.Rows[i]["Exp"].ToString()))
{
data.CmdtoNone("update USR_Customer set GradeSysNo=" + m_grade.Rows[i]["SysNo"].ToString() + " where sysno=" + sysno);
}
}
}
}
catch (Exception exception)
{
//throw exception;
}
}
return ret;
}
