public SYS_DistrictMod GetNearestByPoi(string lng, string lat)
{
DataTable table = new DataTable();
SYS_DistrictMod m_dist = new SYS_DistrictMod();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select top 1 sysno
from [Sys_District] where level=3 and englishname is null and ABS([longitude]-").Append(SQLData.SQLFilter(lng)).Append(")<3 and ABS([Latitude]-").Append(SQLData.SQLFilter(lat)).Append(")<3 order by SQUARE([longitude]-").Append(SQLData.SQLFilter(lng)).Append(")+ SQUARE([Latitude]-").Append(SQLData.SQLFilter(lat)).Append(")");
//try
//{
table = data.CmdtoDataTable(builder.ToString());
//}
//catch (Exception exception)
//{
// throw exception;
//}
if (table.Rows.Count > 0)
{
m_dist = GetModel(int.Parse(table.Rows[0]["sysno"].ToString()));
table = data.CmdtoDataTable("select * from [District3Level] where SysNo3=" + m_dist.SysNo);
m_dist.Name = (table.Rows[0]["Name1"].ToString() + "-" + table.Rows[0]["Name2"].ToString() + "-" + table.Rows[0]["Name3"].ToString()).Replace("市辖区-", "");
}
else
{
return(null);
}
}
return(m_dist);
}
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 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);
}
/// <summary>
///
/// </summary>
/// <param name="UseType">全部则为0</param>
/// <returns></returns>
public DataTable GetFirstLevel(int UseType)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select [SysNo]
,[UpSysNo]
,[Name]
,[EnglishName]
,[Level]
,[UseType]
,[DR]
,[TS]
,[Latitude]
,[longitude]
from [SYS_District] where [Level]=1 and dr=").Append((int)AppEnum.State.normal);
if (UseType != 0 && UseType != AppConst.IntNull)
{
builder.Append(" and usetype=").Append(UseType);
}
builder.Append(" order by [Name] desc");
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return(table);
}
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 DataTable GetList()
{
string cachyname = "UserMedal";
if (HttpRuntime.Cache[cachyname] == null)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT *
FROM [USR_Medal] 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 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 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 DataTable GetSubLevel(int upSysNo)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select [SysNo]
,[UpSysNo]
,[Name]
,[EnglishName]
,[Level]
,[UseType]
,[DR]
,[TS]
,[Latitude]
,[longitude]
from [SYS_District] where upsysno=").Append(upSysNo).Append(" and dr=").Append((int)AppEnum.State.normal)
.Append(" order by [Name]");
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return(table);
}
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 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 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);
}
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 ORD_CashMod GetModelByOrderID(string OrderID)
{
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select SysNo from ORD_Cash where OrderID='").Append(OrderID).Append("'");
try
{
int sysno = Convert.ToInt32(data.CmdtoDataTable(builder.ToString()).Rows[0][0]);
return(GetModel(sysno));
}
catch (Exception exception)
{
return(null);
}
}
}
/// <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);
}
/// <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);
}
public DataTable GetListByAnswer(int answersysno)
{
DataTable m_dt = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select QA_Comment.*,NickName,Photo from QA_Comment left join USR_Customer on CustomerSysNo=USR_Customer.SysNo where AnswerSysNo=").Append(answersysno).Append(" and QA_Comment.DR=").Append((int)AppEnum.State.normal).Append(" order by QA_Comment.SysNo");
try
{
m_dt = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
return(null);
}
}
return(m_dt);
}
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 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 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 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 GetTreeDetail(string name)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select *
from [District3Level] where (Name3='").Append(SQLData.SQLFilter(name)).Append("' or EnglishName3='").Append(SQLData.SQLFilter(name)).Append("')");
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 GetRecordByUser(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 AdvTopic.dr=").Append((int)AppEnum.State.normal);
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return(table);
}
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 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 GetInfoByPoi(string lat, string lng)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select *
from [District3Level] where [longitude3]=").Append(SQLData.SQLFilter(lng)).Append(" and [Latitude3]=").Append(SQLData.SQLFilter(lat));
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return(table);
}