public DataTable GetList()
{
string cachyname = "UserGrade";
if (HttpRuntime.Cache[cachyname] == null)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT *
FROM [USR_Grade] where dr=").Append((int)AppEnum.State.normal);
try
{
tables = data.CmdtoDataTable(builder.ToString());
HttpRuntime.Cache.Insert(cachyname, tables, null, DateTime.Now.AddHours(10), TimeSpan.Zero,
System.Web.Caching.CacheItemPriority.High, null);
}
catch (Exception exception)
{
throw exception;
}
}
}
return (HttpRuntime.Cache[cachyname] as DataTable).Copy();
}
public DataTable GetListByQuest(int SysNo)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT [QA_Order].[SysNo]
,[AnswerSysNo]
,[QuestionSysNo]
,[CustomerSysNo]
,[Price]
,[QA_Order].[Status]
,[QA_Order].[TS]
,[Words]
,[Description]
,[Score]
,[Trial]
,NickName
,photo
FROM [QA_Order] left join USR_Customer on CustomerSysNo=USR_Customer.SysNo where QuestionSysNo=").Append(SysNo);
try
{
tables = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return tables;
}
public Dictionary<int, SYS_ArticleContentMod> GetContentByArticle(int ArticleSysno)
{
Dictionary<int, SYS_ArticleContentMod> ret = new Dictionary<int, SYS_ArticleContentMod>();
DataTable m_dt = new DataTable();
string strsql = "select * from SYS_ArticleContent where ArticleSysNo=" + ArticleSysno + " and DR=" + (int)AppEnum.State.normal + " order by Page asc";
using (SQLData m_data = new SQLData())
{
try
{
m_dt = m_data.CmdtoDataTable(strsql);
}
catch (Exception exception)
{
throw exception;
}
}
for (int i = 0; i < m_dt.Rows.Count; i++)
{
SYS_ArticleContentMod m_tmp = new SYS_ArticleContentMod();
m_tmp.ArticleSysNo = int.Parse(m_dt.Rows[i]["ArticleSysNo"].ToString());
m_tmp.Context = m_dt.Rows[i]["Context"].ToString();
m_tmp.DR = int.Parse(m_dt.Rows[i]["DR"].ToString());
m_tmp.Page = int.Parse(m_dt.Rows[i]["Page"].ToString());
m_tmp.SysNo = int.Parse(m_dt.Rows[i]["SysNo"].ToString());
m_tmp.TS = DateTime.Parse(m_dt.Rows[i]["TS"].ToString());
ret.Add(int.Parse(m_dt.Rows[i]["Page"].ToString()), m_tmp);
}
return ret;
}
public DataTable GetCates(int parent)
{
string cachyname = "CMSCate";
if (HttpRuntime.Cache[cachyname + parent] == null)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT [SysNo]
,[Name]
,[ParentSysNo]
,[DR]
,[TS]
FROM [CMS_Category] where dr=").Append((int)AppEnum.State.normal)
.Append(" and IsHide=").Append((int)AppEnum.BOOL.False)
.Append(" and [ParentSysNo] = ").Append(parent).Append(" order by [Name];");
try
{
tables = data.CmdtoDataTable(builder.ToString());
HttpRuntime.Cache.Insert(cachyname + parent, tables, null, DateTime.Now.AddHours(2), TimeSpan.Zero,
System.Web.Caching.CacheItemPriority.High, null);
}
catch (Exception exception)
{
throw exception;
}
}
}
return (HttpRuntime.Cache[cachyname + parent] as DataTable).Copy();
}
public DataTable GetIndexCateArticle(int count, int cate)
{
string cachyname = "IndexArticle";
if (HttpRuntime.Cache[cachyname + cate.ToString() + "-" + count.ToString()] == null)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT top " + count + @" [SysNo]
,[ArticleSysNo]
,[CateSysNo]
,[KeyWords]
,[TS]
,[OrderID]
,[Title]
,Description
,ReadCount
FROM CMS_ArticleView where CateSysNo in (select SysNo from CMS_Category where SysNo=").Append(cate).Append(" or TopSysNo=").Append(cate).Append(" and dr=").Append((int)AppEnum.State.normal).Append(") and dr=").Append((int)AppEnum.State.normal);
builder.Append(" order by [OrderID] desc, [TS] desc;");
try
{
tables = data.CmdtoDataTable(builder.ToString());
HttpRuntime.Cache.Insert(cachyname + cate.ToString() + "-" + 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 + cate.ToString() + "-" + count.ToString()] as DataTable).Copy();
}
public DataTable GetListByAdmin(int sysno)
{
DataTable m_dt = new DataTable();
using (SQLData m_data = new SQLData())
{
m_dt = m_data.CmdtoDataTable("select * from REL_Admin_Privilege where Admin_SysNo=" + sysno);
}
return m_dt;
}
public DataTable GetList()
{
DataTable m_dt = new DataTable();
using (SQLData m_data = new SQLData())
{
m_dt = m_data.CmdtoDataTable("select * from SYS_Privilege");
}
return m_dt;
}
/// <summary>
/// 获取某案例的所有关键字
/// </summary>
/// <param name="SysNo"></param>
/// <returns></returns>
public DataTable GetFamousList(int SysNo)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("select SYS_Famous_KeyWords.*,REL_Famous_KeyWord.SysNo as RelSysNo,Value from REL_Famous_KeyWord left join SYS_Famous_KeyWords on KeyWord_SysNo = SYS_Famous_KeyWords.SysNo where Famous_SysNo=").Append(SysNo).Append(" and DR=").Append((int)AppEnum.State.normal);
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return table;
}
public DataTable GetListByLogic(int sysno)
{
DataTable m_dt = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select * from RSH_BaziCondition where LogicSysNo=").Append(sysno).Append(" order by sysno asc");
try
{
m_dt = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
return null;
}
}
return m_dt;
}
public DataTable GetRecentRecordByUser(int SysNo)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select T.*,AdvTopic.Group from (max(TS) as LastTime, TopicSysNo, from [TopicSendRecord] where UserSysNo=" + SysNo + " group by TopicSysNo) as T left join AdvTopic on T.TopicSysNo = AdvTopic.SysNo where AdvTopic.dr=").Append((int)AppEnum.State.normal).Append(" order by LastTime desc");
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return table;
}
public DataTable GetList()
{
DataTable table = new DataTable();
Dictionary<int, SYS_PrivilegeMod> dictionary = new Dictionary<int, SYS_PrivilegeMod>();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("select SysNo, [Name], ParentSysNo from SYS_Famous_Category where ParentSysNo=0").Append(" and DR=").Append((int)AppEnum.State.normal);
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return table;
}
public DataTable GetListByTopic(int SysNo)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select * from [AdvTopicContent] where TopicSysNo=" + SysNo);
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return table;
}
public DataTable GetMedalByCustomer(int UserSysno, int type)
{
DataTable m_dt = new DataTable();
#region 设置参数
string sql = @" select REL_Customer_Medal.*,MedalName,type,Detail" +
" from REL_Customer_Medal left join USR_Medal on MedalSysNo=USR_Medal.SysNo" +
" where CustomerSysNo=" + UserSysno + " and REL_Customer_Medal.DR=" + (int)AppEnum.State.normal + " and USR_Medal.DR=" + (int)AppEnum.State.normal;
if (type != 0 && type != AppConst.IntNull)
{
sql += " and type=" + type;
}
sql += " order by REL_Customer_Medal.sysno desc";
#endregion
using (SQLData m_data = new SQLData())
{
m_dt = m_data.CmdtoDataTable(sql);
}
return m_dt;
}
public DataTable GetAllCates()
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT [SysNo]
,[Name]
,[ParentSysNo]
,[DR]
,[TS]
FROM [QA_Category] where dr=").Append((int)AppEnum.State.normal);
try
{
tables = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return tables;
}
public bool HasMedal(int usersysno, int medalsysno)
{
bool ret = false;
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("select * from REL_Customer_Medal where customersysno=").Append(usersysno).Append(" and medalsysno=").Append(medalsysno);
try
{
if (data.CmdtoDataTable(builder.ToString()).Rows.Count > 0)
{
ret = true;
}
}
catch (Exception exception)
{
//throw exception;
}
}
return ret;
}
public DataTable GetOrderDetail(int SysNo)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("select qa_order.*,qa_question.title,TT.nickname as orderuname,PP.nickname as questuname from qa_order left join qa_question on questionsysno = qa_question.sysno left join usr_customer TT on TT.sysno = qa_order.customersysno left join usr_customer PP on PP.sysno = qa_question.customersysno where qa_order.sysno=").Append(SysNo);
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
//throw exception;
}
}
return table;
}
public Dictionary<int, SYS_PrivilegeMod> GetAdminPrivilege(int AdminSysNo)
{
DataTable table = new DataTable();
Dictionary<int, SYS_PrivilegeMod> dictionary = new Dictionary<int, SYS_PrivilegeMod>();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("select SysNo,[Name],url from SYS_Privilege where SysNo in (select Privilege_SysNo from REL_Admin_Privilege where Admin_SysNo=").Append(AdminSysNo).Append(") and DR=").Append((int)AppEnum.State.normal);
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
if (table.Rows.Count > 0)
{
for (int i = 0; i < table.Rows.Count; i++)
{
SYS_PrivilegeMod mod = new SYS_PrivilegeMod();
mod.Name =table.Rows[i]["Name"].ToString();
mod.SysNo = int.Parse(table.Rows[i]["SysNo"].ToString());
mod.URL = table.Rows[i]["url"].ToString();
dictionary.Add(int.Parse(table.Rows[i]["SysNo"].ToString()), mod);
}
}
return dictionary;
}
public DataTable GetTopListByCate(int top)
{
string cachyname = "QACatePostTop" + top;
if (HttpRuntime.Cache[cachyname] == null)
{
DataTable m_dt = new DataTable();
string sqlstr = @"select b.*,NickName,Photo from(select * from
(select SysNo,CateSysNo,CustomerSysNo,Title,Context,Award,LastReplyTime,row_number() over(partition by CateSysNo order by LastReplyTime desc)
as rowindex from QA_Question) a where rowindex <= " + top + ") b left join USR_Customer on CustomerSysNo=USR_Customer.sysno";
using (SQLData m_data = new SQLData())
{
m_dt = m_data.CmdtoDataTable(sqlstr);
}
HttpRuntime.Cache.Insert(cachyname, m_dt, null, DateTime.Now.AddMinutes(5), TimeSpan.Zero,
System.Web.Caching.CacheItemPriority.High, null);
}
return (HttpRuntime.Cache[cachyname] as DataTable).Copy();
}
public DataTable GetCatesForAdmin(int parent)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT [SysNo]
,[Name]
,[ParentSysNo]
,IsHide
,[DR]
,[TS]
FROM [CMS_Category] where ")
.Append(" [ParentSysNo] = ").Append(parent).Append(" order by [Name];");
try
{
tables = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return tables;
}
public FATE_ChartMod GetChartByQuest(int SysNo)
{
int fatesysno = 0;
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select Chart_SysNo from REL_Question_Chart where Question_SysNo=").Append(SysNo);
try
{
fatesysno = Convert.ToInt32(data.CmdtoDataTable(builder.ToString()).Rows[0]["Chart_SysNo"]);
}
catch (Exception exception)
{
return null;
}
}
return FATE_ChartBll.GetInstance().GetModel(fatesysno);
}
public DataTable GetReturnRecordByUser(int SysNo)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select TopicSendRecord.*,AdvTopic.Group from [TopicSendRecord] left join AdvTopic on TopicSysNo = AdvTopic.SysNo where UserSysNo=" + SysNo + " and IsReturn = "+(int)AppEnum.BOOL.True+" and AdvTopic.dr=").Append((int)AppEnum.State.normal);
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return table;
}
public DataTable GetStarsList(int customersysno,int count)
{
string cachyname = "QAStarsQuest";
if (HttpRuntime.Cache[cachyname + customersysno.ToString() + "-" + count.ToString()] == null)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT top " + count + @" Title
, Award
, EndTime
, ReplyCount
, Context
FROM [QA_Question] where sysno in (select QuestionSysNo from QA_Answer where CustomerSysNo and Award>0 amd dr=").Append((int)AppEnum.State.normal).Append(") as T and IsSecret=0 and dr=").Append((int)AppEnum.State.normal);
builder.Append(" order by [OrderID] desc;");
try
{
tables = data.CmdtoDataTable(builder.ToString());
HttpRuntime.Cache.Insert(cachyname + customersysno.ToString() + "-" + 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 + customersysno.ToString() + "-" + count.ToString()] as DataTable).Copy();
}
public DataTable GetTalk(int Sysno)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select USR_SMS.SysNo, FromSysNo, ToSysNo, Title, Context, IsRead, Parent, USR_SMS.DR, USR_SMS.TS,a.NickName as FromName,a.photo as FromPhoto,b.NickName as ToName,b.photo as ToPhoto
from USR_SMS left join USR_Customer a on FromSysNo = a.sysno left join USR_Customer b on ToSysNo = b.sysno
where ([Parent]=" + Sysno + @" or (Parent=0 and USR_SMS.SysNo=" + Sysno + ")) and dr=").Append((int)AppEnum.State.normal)
.Append(" order by USR_SMS.TS asc");
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return table;
}
public DataTable GetToEndList()
{
DataTable m_dt = new DataTable();
string sqlstr = "select * from QA_Question where EndTime is null and TS < '" + DateTime.Now.AddDays(-10).ToString("yyyy-MM-dd HH:mm:ss") + "' and Award>0 and ReplyCount>0;"
+ "update QA_Question set EndTime=getdate() where EndTime is null and Award=0;";
using (SQLData m_data = new SQLData())
{
m_dt = m_data.CmdtoDataTable(sqlstr);
}
return m_dt;
}
public DataTable GetStarsList(int count,int type)
{
string cachyname = "QAStars";
if (HttpRuntime.Cache[cachyname + type.ToString() + "-" + count.ToString()] == null)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT top " + count + @" [QA_Star].[SysNo]
,[NickName]
,[CustomerSysNo]
,[OrderID]
,[QA_Star].[Intro]
,[QA_Star].[FateType]
,[Photo]
,[Credit]
,[Point]
FROM [QA_Star] left join USR_Customer on CustomerSysNo = USR_Customer.SysNo where status=").Append((int)AppEnum.State.normal);
if(type!= 0)
{
builder.Append(" and [FateType] = ").Append(type);
}
builder.Append(" order by [OrderID] desc;");
try
{
tables = data.CmdtoDataTable(builder.ToString());
HttpRuntime.Cache.Insert(cachyname + type.ToString() + "-" + 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 + type.ToString() + "-" + count.ToString()] as DataTable).Copy();
}
public bool HasRecord(int usersysno, int Catesysno,int type)
{
bool ret = false;
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("select * from REL_Customer_Category where customersysno=").Append(usersysno).Append(" and Categorysysno=").Append(Catesysno).Append(" and type=").Append(type);
try
{
if (data.CmdtoDataTable(builder.ToString()).Rows.Count > 0)
{
ret = true;
}
}
catch (Exception exception)
{
//throw exception;
}
}
return ret;
}
public DateTime GetPayTimeByQAOrder(int ordersysno)
{
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select PayTime from ORD_Cash where producttype=").Append((int)AppEnum.CashOrderType.consultpay).Append(" and productsysno=").Append(ordersysno).Append(" and status=").Append((int)AppEnum.CashOrderStatus.succed);
try
{
DataTable tmp = data.CmdtoDataTable(builder.ToString());
if (tmp.Rows.Count > 0)
{
return DateTime.Parse(tmp.Rows[0]["PayTime"].ToString());
}
else
{
return AppConst.DateTimeNull;
}
}
catch (Exception exception)
{
return AppConst.DateTimeNull;
}
}
}
public bool IsAdmin(int customersysno)
{
bool ret = false;
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("select * from SYS_Admin where customersysno=").Append(customersysno).Append(" and DR=").Append(0);
try
{
if (data.CmdtoDataTable(builder.ToString()).Rows.Count > 0)
{
ret = true;
}
}
catch (Exception exception)
{
//throw exception;
}
}
return ret;
}
public DataTable GetTodayTopList(int count)
{
DataTable m_dt = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("select top ").Append(count).Append(" SysNo,[Name],[FullName],[Gender],photo from SYS_Famous where istop=").Append((int)AppEnum.BOOL.True)
.Append(" and BirthTime>='").Append(new DateTime(AppConst.DateTimeNull.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0))
.Append("' and BirthTime<='").Append(new DateTime(AppConst.DateTimeNull.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59))
.Append("' and dr=").Append((int)AppEnum.State.normal);
try
{
m_dt = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
//throw exception;
}
}
return m_dt;
}
/// <summary>
/// 获取某提问的已分配悬赏值
/// </summary>
/// <param name="sysno"></param>
/// <returns></returns>
public int GetUsedAward(int sysno)
{
int sum = 0;
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select sum(Award) from QA_Answer where QuestionSysNo=").Append(sysno);
try
{
sum = Convert.ToInt32(data.CmdtoDataTable(builder.ToString()).Rows[0][0]);
}
catch (Exception exception)
{
return sum;
}
}
return sum;
}
