public void InsertDataExtendFields(ExtendRelationType relationType, int relationId, IDictionary <int, string> extendData)
{
if (extendData.Count == 0)
{
return;
}
string sql = base.OptimizeSQL(SP.DataExtend_InsertOrUpdateFieldValue);
IList <SqlQuery> querys = new List <SqlQuery>();
querys.Add(SqlQueryHelper.Format(SP.DataExtend_ClearupExtendFielValue,
new object[, ] {
{ "@relationType", relationType },
{ "@relationId", relationId }
}
));
foreach (int key in extendData.Keys)
{
if (!String.IsNullOrEmpty(extendData[key]))
{
querys.Add(new SqlQuery(sql, new object[, ] {
{ "@relationType", relationType },
{ "@relationId", relationId },
{ "@fieldId", key },
{ "@fieldValue", extendData[key] }
}));
}
}
base.ExecuteNonQuery(querys.ToArray());
}
public void SaveLinksOfContent(string typeIndent, int relatedId, IList <ILink> list)
{
if (list.Count == 0)
{
return;
}
SqlQuery[] querys = new SqlQuery[list.Count];
int i = 0;
foreach (ILink link in list)
{
querys[i++] = SqlQueryHelper.Format(
(link.LinkId <= 0 ? SP.Link_InsertRelatedLink:SP.Link_UpdateRelatedLink),
new object[, ] {
{ "@typeIndent", typeIndent },
{ "@relatedId", relatedId },
{ "@id", link.LinkId },
{ "@name", link.LinkName },
{ "@uri", link.LinkUri },
{ "@title", link.LinkTitle },
{ "@enabled", link.Enabled }
});
}
base.ExecuteNonQuery(querys);
}
public void GetExtendValuesList(int siteId, int relationType, IList <int> idList, DataReaderFunc func)
{
if (idList == null || idList.Count == 0)
{
return;
}
string ids = "";
int tmpInt = 0;
foreach (int i in idList)
{
if (tmpInt++ != 0)
{
ids += ",";
}
ids += i.ToString();
}
base.ExecuteReader(SqlQueryHelper.Format(SP.DataExtend_GetExtendValuesList,
new object[, ] {
{ "@siteId", siteId },
{ "@relationType", relationType }
}, ids
), func);
}
/************ 分页 ****************/
/// <summary>
/// 获取栏目分页文档
/// </summary>
/// <param name="siteId"></param>
/// <param name="lft"></param>
/// <param name="pageSize"></param>
/// <param name="currentPageIndex"></param>
/// <param name="rgt"></param>
/// <param name="recordCount"></param>
/// <param name="pages"></param>
/// <returns></returns>
public DataTable GetPagedArchives(
int siteId, int lft, int rgt,
int pageSize, ref int currentPageIndex,
out int recordCount, out int pages)
{
object[,] data = new object[, ] {
{ "@siteId", siteId },
{ "@lft", lft },
{ "@rgt", rgt }
};
const string condition = " ";
//数据库为ACCESS,页码为1时调用SQL
const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_archive.ID AS ID,* FROM $PREFIX_archive
INNER JOIN $PREFIX_category ON $PREFIX_archive.[CID]=$PREFIX_category.[ID]
WHERE $PREFIX_category.siteId=@siteId AND (lft>=@lft AND rgt<=@rgt)
AND flags LIKE '%st:''0''%'AND flags LIKE '%v:''1''%'
ORDER BY [CreateDate] DESC,$PREFIX_archive.[ID]";
//获取记录条数
recordCount = int.Parse(base.ExecuteScalar(SqlQueryHelper.Format(SP.Archive_GetPagedArchivesCountSql_pagerqurey, data)).ToString());
pages = recordCount / pageSize;
if (recordCount % pageSize != 0)
{
pages++;
}
//验证当前页数
if (currentPageIndex > pages && currentPageIndex != 1)
{
currentPageIndex = pages;
}
if (currentPageIndex < 1)
{
currentPageIndex = 1;
}
//计算分页
int skipCount = pageSize * (currentPageIndex - 1);
//如果调过记录为0条，且为OLEDB时候，则用sql1
string sql = skipCount == 0 && base.DbType == DataBaseType.OLEDB ?
sql1 :
SP.Archive_GetPagedArchivesByCategoryID_pagerquery;
sql = SQLRegex.Replace(sql, m =>
{
switch (m.Groups[1].Value)
{
case "skipsize": return(skipCount.ToString());
case "pagesize": return(pageSize.ToString());
}
return(null);
});
return(base.GetDataSet(SqlQueryHelper.Format(sql, data)).Tables[0]);
}
public int UpdateSite(ISite site)
{
return(base.ExecuteNonQuery(
SqlQueryHelper.Format(SP.Site_EditSite,
new object[, ] {
{ "@siteId", site.Id },
{ "@name", site.Name },
{ "@dirname", site.DirName },
{ "@tpl", site.Tpl },
{ "@domain", site.Domain },
{ "@location", site.Location },
{ "@language", site.Language },
{ "@note", site.Note },
{ "@seotitle", site.SeoTitle },
{ "@seokeywords", site.SeoKeywords },
{ "@seodescription", site.SeoDescription },
{ "@state", site.State },
{ "@protel", site.Tel },
{ "@prophone", site.Phone },
{ "@profax", site.Fax },
{ "@proaddress", site.Address },
{ "@proemail", site.Email },
{ "@im", site.Im },
{ "@postcode", site.PostCode },
{ "@pronotice", site.Notice },
{ "@proslogan", site.Slogan }
})));
}
public void ReadLinksOfContent(string typeIndent, int relatedId, DataReaderFunc func)
{
base.ExecuteReader(
SqlQueryHelper.Format(SP.Link_GetRelatedLinks, new object[, ] {
{ "@typeIndent", typeIndent },
{ "@relatedId", relatedId }
}), func);
}
public void GetSiteLinkById(int siteId, int linkId, DataReaderFunc func)
{
base.ExecuteReader(
SqlQueryHelper.Format(SP.Link_GetSiteLinkById, new object[, ] {
{ "@siteId", siteId },
{ "@linkId", linkId }
}), func);
}
/// <summary>
/// 获取所有的友情链接
/// </summary>
/// <returns></returns>
public void GetAllSiteLinks(int siteId, SiteLinkType type, DataReaderFunc func)
{
base.ExecuteReader(
SqlQueryHelper.Format(SP.Link_GetSiteLinksByLinkType, new object[, ] {
{ "@siteId", siteId },
{ "@linkType", (int)type }
}), func);
}
public void RemoveRelatedLinks(string typeIndent, int relatedId, string ids)
{
base.ExecuteNonQuery(
SqlQueryHelper.Format(SP.Link_RemoveRelatedLinks, new object[, ] {
{ "@typeIndent", typeIndent },
{ "@relatedId", relatedId }
}, ids));
}
public int GetCategoryExtendRefrenceNum(int siteId, int categoryId, int extendFieldId)
{
return(int.Parse(base.ExecuteScalar(
SqlQueryHelper.Format(SP.DataExtend_GetCategoryExtendRefrenceNum, new object[, ] {
{ "@siteId", siteId },
{ "@categoryId", categoryId },
{ "@fieldId", extendFieldId }
})
).ToString()));
}
public void GetArchivesExtendValues(int siteId, int relationType, string categoryTag, int number, DataReaderFunc func)
{
base.ExecuteReader(
SqlQueryHelper.Format(SP.Archive_GetArchivesExtendValues,
new object[, ] {
{ "@siteId", siteId },
{ "@tag", categoryTag },
{ "@relationType", relationType }
}, number.ToString()
), func);
}
public void GetSelftAndChildArchiveExtendValues(int siteId, int relationType, int lft, int rgt, int number, DataReaderFunc func)
{
/*
* base.ExecuteReader(
* new SqlQuery(String.Format(base.OptimizeSQL(SP.Archive_GetSelfAndChildArchives), number),
* new object[,]{
* }), func
* );
*/
base.ExecuteReader(
SqlQueryHelper.Format(SP.Archive_GetSelfAndChildArchiveExtendValues,
new object[, ] {
{ "@siteId", siteId },
{ "@lft", lft },
{ "@rgt", rgt },
{ "@relationType", relationType }
}, number.ToString()
), func);
}
public int CreateSite(ISite site)
{
int row = base.ExecuteNonQuery(
SqlQueryHelper.Format(SP.Site_CreateSite,
new object[, ] {
{ "@name", site.Name },
{ "@dirname", site.DirName },
{ "@domain", site.Domain },
{ "@tpl", site.Tpl },
{ "@language", site.Language },
{ "@note", site.Note },
{ "@seotitle", site.SeoTitle },
{ "@seokeywords", site.SeoKeywords },
{ "@seodescription", site.SeoDescription },
{ "@state", site.State },
{ "@protel", site.Tel },
{ "@prophone", site.Phone },
{ "@profax", site.Fax },
{ "@proaddress", site.Address },
{ "@proemail", site.Email },
{ "@im", site.Im },
{ "@postcode", site.PostCode },
{ "@pronotice", site.Notice },
{ "@proslogan", site.Slogan }
}),
//初始化Root数据
SqlQueryHelper.Format(@"INSERT INTO $PREFIX_categories
(siteid,pagetitle,moduleid,tag,name,keywords,description,lft,rgt,orderindex)
VALUES((SELECT MAX(siteid) FROM $PREFIX_sites),
'ROOT',1,'root','根栏目','','',1,2,1)
")
);
if (row != 2)
{
return(-1);
}
return(int.Parse(base.ExecuteScalar(SqlQueryHelper.Format("SELECT MAX(siteid) FROM $PREFIX_sites")).ToString()));
}
public void UpdateCategoryExtendsBind(int categoryId, int[] extendIds)
{
SqlQuery[] querys = new SqlQuery[extendIds.Length + 1];
const string insertSql = "INSERT INTO $PREFIX_categoryExtend (categoryId,extendId,enabled) VALUES (@categoryId, @extendId,1)";
const string delSql = "DELETE FROM $PREFIX_categoryExtend WHERE categoryId=@categoryId";
querys[0] = SqlQueryHelper.Format(delSql, new object[, ] {
{ "@categoryId", categoryId }
});
for (int i = 0; i < extendIds.Length; i++)
{
querys[i + 1] = SqlQueryHelper.Format(insertSql, new object[, ] {
{ "@categoryId", categoryId },
{ "@extendId", extendIds[i] }
}
);
}
base.ExecuteNonQuery(querys);
}
/// <summary>
/// 在指定栏目下搜索关键词相关的内容
/// </summary>
/// <param name="categoryRgt"></param>
/// <param name="keyword"></param>
/// <param name="pageSize"></param>
/// <param name="currentPageIndex"></param>
/// <param name="recordCount"></param>
/// <param name="pageCount"></param>
/// <param name="orderby"></param>
/// <param name="siteId"></param>
/// <param name="categoryLft"></param>
/// <param name="func"></param>
/// <returns></returns>
public void SearchArchivesByCategory(int siteId,
int categoryLft, int categoryRgt, string keyword,
int pageSize, int currentPageIndex,
out int recordCount, out int pageCount,
string orderby, DataReaderFunc func)
{
const string condition = " flags LIKE '%st:''0''%'AND flags LIKE '%v:''1''%' ";
if (String.IsNullOrEmpty(orderby))
{
orderby = String.Intern("ORDER BY CreateDate DESC");
}
object[,] data = new object[, ]
{
{ "@siteId", siteId },
{ "@lft", categoryLft },
{ "@rgt", categoryRgt }
};
//为第一页时
const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_archive.[ID] AS ID,*
FROM $PREFIX_archive INNER JOIN $PREFIX_category
ON $PREFIX_archive.[CgID]=$PREFIX_category.[ID]
WHERE $[condition] AND $PREFIX_category.siteid=@siteId AND ($PREFIX_category.lft>=@lft
AND $PREFIX_category.rgt<=@rgt) AND ([Title] LIKE
'%$[keyword]%' OR [Outline] LIKE '%$[keyword]%'
OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%')
$[orderby],$PREFIX_archive.[ID]";
//记录数
recordCount = int.Parse(base.ExecuteScalar(
SqlQueryHelper.Format(SP.Archive_GetSearchRecordCountByCategoryID,
data,
keyword,
condition
)).ToString());
//页数
pageCount = recordCount / pageSize;
if (recordCount % pageSize != 0)
{
pageCount++;
}
//对当前页数进行验证
if (currentPageIndex > pageCount && currentPageIndex != 1)
{
currentPageIndex = pageCount;
}
if (currentPageIndex < 1)
{
currentPageIndex = 1;
}
//跳过记录数
int skipCount = pageSize * (currentPageIndex - 1);
string sql = skipCount == 0 && base.DbType == DataBaseType.OLEDB ?
sql1:
SP.Archive_GetPagedSearchArchivesByCategoryID;
sql = SQLRegex.Replace(sql, (match) =>
{
switch (match.Groups[1].Value)
{
case "siteid": return(siteId.ToString());
case "condition": return(condition);
case "pagesize": return(pageSize.ToString());
case "skipsize": return(skipCount.ToString());
case "keyword": return(keyword);
case "orderby": return(orderby);
}
return(null);
});
base.ExecuteReader(SqlQueryHelper.Format(sql, data), func);
}
/// <summary>
/// 搜索关键词相关的内容
/// </summary>
/// <param name="keyword"></param>
/// <param name="pageSize"></param>
/// <param name="currentPageIndex"></param>
/// <param name="recordCount"></param>
/// <param name="pageCount"></param>
/// <param name="orderby"></param>
/// <returns></returns>
public void SearchArchives(int siteID, string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount, string orderby, DataReaderFunc func)
{
/*
* string condition = ArchiveFlag.GetSQLString(new string[,]{
* {"st","0"},
* {"v","1"}
* });
*/
const string condition = " flags LIKE '%st:''0''%'AND flags LIKE '%v:''1''%' ";
//排序规则
if (String.IsNullOrEmpty(orderby))
{
orderby = String.Intern("ORDER BY Createdate DESC");
}
//数据库为OLEDB,且为第一页时
const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_archive.[ID] AS ID,* FROM $PREFIX_archive INNER JOIN $PREFIX_category ON $PREFIX_archive.[CID]=$PREFIX_category.[ID]
WHERE $[condition] AND ([Title] LIKE '%$[keyword]%' OR [Outline] LIKE '%$[keyword]%' OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%') $[orderby],$PREFIX_archive.[ID]";
//记录数
recordCount = int.Parse(base.ExecuteScalar(
SqlQueryHelper.Format(SP.Archive_GetSearchRecordCount, keyword, siteID.ToString(), condition)
).ToString());
//页数
pageCount = recordCount / pageSize;
if (recordCount % pageSize != 0)
{
pageCount++;
}
//对当前页数进行验证
if (currentPageIndex > pageCount && currentPageIndex != 1)
{
currentPageIndex = pageCount;
}
if (currentPageIndex < 1)
{
currentPageIndex = 1;
}
//跳过记录数
int skipCount = pageSize * (currentPageIndex - 1);
//如果调过记录为0条，且为OLEDB时候，则用sql1
string sql = skipCount == 0 && base.DbType == DataBaseType.OLEDB?
sql1:
SP.Archive_GetPagedSearchArchives;
sql = SQLRegex.Replace(sql, (match) =>
{
switch (match.Groups[1].Value)
{
case "condition": return(condition);
case "siteid": return(siteID.ToString());
case "pagesize": return(pageSize.ToString());
case "skipsize": return(skipCount.ToString());
case "keyword": return(keyword);
case "orderby": return(orderby);
}
return(null);
});
base.ExecuteReader(new SqlQuery(base.OptimizeSQL(sql)), func);
}