public IDataReader GetDataSource(int siteId, string searchText, string templateTypeString)
{
if (string.IsNullOrEmpty(searchText) && string.IsNullOrEmpty(templateTypeString))
{
var parms = new IDataParameter[]
{
GetParameter(ParmSiteId, DataType.Integer, siteId)
};
var enumerable = ExecuteReader(SqlSelectAllTemplateBySiteId, parms);
return(enumerable);
}
if (!string.IsNullOrEmpty(searchText))
{
var whereString = (string.IsNullOrEmpty(templateTypeString)) ? string.Empty :
$"AND TemplateType = '{templateTypeString}' ";
searchText = AttackUtils.FilterSql(searchText);
whereString +=
$"AND (TemplateName LIKE '%{searchText}%' OR RelatedFileName LIKE '%{searchText}%' OR CreatedFileFullName LIKE '%{searchText}%' OR CreatedFileExtName LIKE '%{searchText}%')";
string sqlString =
$"SELECT Id, SiteId, TemplateName, TemplateType, RelatedFileName, CreatedFileFullName, CreatedFileExtName, Charset, IsDefault FROM siteserver_Template WHERE SiteId = {siteId} {whereString} ORDER BY TemplateType, RelatedFileName";
var enumerable = ExecuteReader(sqlString);
return(enumerable);
}
return(GetDataSourceByType(siteId, TemplateTypeUtils.GetEnumType(templateTypeString)));
}
private void SetTaxisSubtract(int id, string parentsPath, int subtractNum)
{
var path = AttackUtils.FilterSql(parentsPath);
var sqlString =
$"UPDATE siteserver_Department SET Taxis = Taxis - {subtractNum} WHERE Id = {id} OR ParentsPath = '{path}' OR ParentsPath LIKE '{path},%'";
ExecuteNonQuery(sqlString);
DepartmentManager.ClearCache();
}
private void SetTaxisAdd(int areaId, string parentsPath, int addNum)
{
var path = AttackUtils.FilterSql(parentsPath);
string sqlString =
$"UPDATE siteserver_Area SET Taxis = Taxis + {addNum} WHERE Id = {areaId} OR ParentsPath = '{path}' OR ParentsPath LIKE '{path},%'";
ExecuteNonQuery(sqlString);
AreaManager.ClearCache();
}
private static string GetGroupWhereString(DatabaseType databaseType, string group, string groupNot)
{
var whereStringBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(group))
{
group = group.Trim().Trim(',');
var groupArr = group.Split(',');
if (groupArr.Length > 0)
{
whereStringBuilder.Append(" AND (");
foreach (var theGroup in groupArr)
{
var trimGroup = theGroup.Trim();
whereStringBuilder.Append(
$" (siteserver_Channel.GroupNames = '{trimGroup}' OR {SqlUtils.GetInStr(databaseType, "siteserver_Channel.GroupNames", trimGroup + ",")} OR {SqlUtils.GetInStr(databaseType, "siteserver_Channel.GroupNames", "," + trimGroup + ",")} OR {SqlUtils.GetInStr(databaseType, "siteserver_Channel.GroupNames", "," + trimGroup)}) OR ");
}
if (groupArr.Length > 0)
{
whereStringBuilder.Length -= 3;
}
whereStringBuilder.Append(") ");
}
}
if (!string.IsNullOrEmpty(groupNot))
{
groupNot = groupNot.Trim().Trim(',');
var groupNotArr = groupNot.Split(',');
if (groupNotArr.Length > 0)
{
whereStringBuilder.Append(" AND (");
foreach (var theGroupNot in groupNotArr)
{
var trimGroupNot = AttackUtils.FilterSql(theGroupNot.Trim());
//whereStringBuilder.Append(
// $" (siteserver_Channel.GroupNames <> '{trimGroupNot}' AND CHARINDEX('{trimGroupNot},',siteserver_Channel.GroupNames) = 0 AND CHARINDEX(',{trimGroupNot},',siteserver_Channel.GroupNames) = 0 AND CHARINDEX(',{trimGroupNot}',siteserver_Channel.GroupNames) = 0) AND ");
whereStringBuilder.Append(
$" (siteserver_Channel.GroupNames <> '{trimGroupNot}' AND {SqlUtils.GetNotInStr(databaseType, "siteserver_Channel.GroupNames", trimGroupNot + ",")} AND {SqlUtils.GetNotInStr(databaseType, "siteserver_Channel.GroupNames", "," + trimGroupNot + ",")} AND {SqlUtils.GetNotInStr(databaseType, "siteserver_Channel.GroupNames", "," + trimGroupNot)}) AND ");
}
if (groupNotArr.Length > 0)
{
whereStringBuilder.Length -= 4;
}
whereStringBuilder.Append(") ");
}
}
return(whereStringBuilder.ToString());
}
private int GetMaxTaxisByParentPath(string parentPath)
{
parentPath = AttackUtils.FilterSql(parentPath);
var sqlString = string.Concat("SELECT MAX(Taxis) AS MaxTaxis FROM siteserver_Department WHERE (ParentsPath = '", parentPath, "') OR (ParentsPath LIKE '", parentPath, ",%')");
var maxTaxis = 0;
using (var rdr = ExecuteReader(sqlString))
{
if (rdr.Read())
{
maxTaxis = GetInt(rdr, 0);
}
rdr.Close();
}
return(maxTaxis);
}
public List <string> GetTagListByStartString(int siteId, string startString, int totalNum)
{
var sqlWithParameter = SqlUtils.GetInStrWithParameter("Tag", AttackUtils.FilterSql(startString));
var sqlString = SqlUtils.GetDistinctTopSqlString("siteserver_Tag", "Tag, UseNum",
$"WHERE SiteId = @SiteId AND {sqlWithParameter.Key}",
"ORDER BY UseNum DESC", totalNum);
IDataParameter[] parameters =
{
GetParameter("@SiteId", DataType.Integer, siteId),
sqlWithParameter.Value
};
return(DataProvider.DatabaseDao.GetStringList(sqlString, parameters));
}
public string GetSelectCommend(string category, string pluginId, string keyword, string dateFrom, string dateTo)
{
var whereString = new StringBuilder();
if (!string.IsNullOrEmpty(category))
{
whereString.Append($"Category = '{AttackUtils.FilterSql(category)}'");
}
if (!string.IsNullOrEmpty(pluginId))
{
whereString.Append($"PluginId = '{AttackUtils.FilterSql(pluginId)}'");
}
if (!string.IsNullOrEmpty(keyword))
{
if (whereString.Length > 0)
{
whereString.Append(" AND ");
}
var filterKeyword = AttackUtils.FilterSql(keyword);
var keywordId = TranslateUtils.ToInt(keyword);
whereString.Append(keywordId > 0
? $"Id = {keywordId}"
: $"(Message LIKE '%{filterKeyword}%' OR Stacktrace LIKE '%{filterKeyword}%' OR Summary LIKE '%{filterKeyword}%')");
}
if (!string.IsNullOrEmpty(dateFrom))
{
if (whereString.Length > 0)
{
whereString.Append(" AND ");
}
whereString.Append($"AddDate >= {SqlUtils.GetComparableDate(TranslateUtils.ToDateTime(dateFrom))}");
}
if (!string.IsNullOrEmpty(dateTo))
{
if (whereString.Length > 0)
{
whereString.Append(" AND ");
}
whereString.Append($"AddDate <= {SqlUtils.GetComparableDate(TranslateUtils.ToDateTime(dateTo))}");
}
return(whereString.Length > 0
? $"SELECT Id, Category, PluginId, Message, Stacktrace, Summary, AddDate FROM {TableName} WHERE {whereString}"
: $"SELECT Id, Category, PluginId, Message, Stacktrace, Summary, AddDate FROM {TableName}");
}
public string GetSelectCommend(string userName, string keyword, string dateFrom, string dateTo)
{
if (string.IsNullOrEmpty(userName) && string.IsNullOrEmpty(keyword) && string.IsNullOrEmpty(dateFrom) && string.IsNullOrEmpty(dateTo))
{
return(GetSelectCommend());
}
var whereString = new StringBuilder("WHERE ");
var isWhere = false;
if (!string.IsNullOrEmpty(userName))
{
isWhere = true;
whereString.AppendFormat("(UserName = '******')", AttackUtils.FilterSql(userName));
}
if (!string.IsNullOrEmpty(keyword))
{
if (isWhere)
{
whereString.Append(" AND ");
}
isWhere = true;
whereString.AppendFormat("(Action LIKE '%{0}%' OR Summary LIKE '%{0}%')", AttackUtils.FilterSql(keyword));
}
if (!string.IsNullOrEmpty(dateFrom))
{
if (isWhere)
{
whereString.Append(" AND ");
}
isWhere = true;
whereString.Append($"(AddDate >= {SqlUtils.GetComparableDate(TranslateUtils.ToDateTime(dateFrom))})");
}
if (!string.IsNullOrEmpty(dateTo))
{
if (isWhere)
{
whereString.Append(" AND ");
}
whereString.Append($"(AddDate <= {SqlUtils.GetComparableDate(TranslateUtils.ToDateTime(dateTo))})");
}
return("SELECT ID, UserName, IPAddress, AddDate, Action, Summary FROM siteserver_UserLog " + whereString);
}
private string GetWhereString(string tag, int siteId, int contentId)
{
var builder = new StringBuilder();
builder.Append($" WHERE SiteId = {siteId} ");
if (!string.IsNullOrEmpty(tag))
{
builder.Append($"AND Tag = '{AttackUtils.FilterSql(tag)}' ");
}
if (contentId > 0)
{
builder.Append(
$"AND (ContentIdCollection = '{contentId}' OR ContentIdCollection LIKE '{contentId},%' OR ContentIdCollection LIKE '%,{contentId},%' OR ContentIdCollection LIKE '%,{contentId}')");
}
return(builder.ToString());
}
public bool IsExists(string tableName)
{
var isExists = false;
string sqlString =
$"SELECT TableName FROM siteserver_Table WHERE TableName = '{AttackUtils.FilterSql(tableName)}'";
using (var rdr = ExecuteReader(sqlString))
{
if (rdr.Read() && !rdr.IsDBNull(0))
{
isExists = true;
}
rdr.Close();
}
return(isExists);
}
public ApiContentsParameters(RequestImpl request)
{
ChannelIds = TranslateUtils.StringCollectionToIntList(request.GetQueryString("channelIds"));
ChannelGroup = StringUtils.Trim(AttackUtils.FilterSql(request.GetQueryString("channelGroup")));
ContentGroup = StringUtils.Trim(AttackUtils.FilterSql(request.GetQueryString("contentGroup")));
Tag = StringUtils.Trim(AttackUtils.FilterSql(request.GetQueryString("tag")));
Top = request.GetQueryInt("top", 20);
Skip = request.GetQueryInt("skip");
Likes = TranslateUtils.StringCollectionToStringList(StringUtils.Trim(AttackUtils.FilterSql(request.GetQueryString("like"))));
OrderBy = StringUtils.Trim(AttackUtils.FilterSql(request.GetQueryString("orderBy")));
QueryString = new NameValueCollection(request.QueryString);
QueryString.Remove("siteId");
QueryString.Remove("channelIds");
QueryString.Remove("channelGroup");
QueryString.Remove("contentGroup");
QueryString.Remove("tag");
QueryString.Remove("top");
QueryString.Remove("skip");
QueryString.Remove("like");
QueryString.Remove("orderBy");
}
public string GetSqlString(string keyword, string dateFrom, string dateTo)
{
if (string.IsNullOrEmpty(keyword) && string.IsNullOrEmpty(dateFrom) && string.IsNullOrEmpty(dateTo))
{
return(GetSqlString());
}
var whereString = new StringBuilder("WHERE ");
var isWhere = false;
if (!string.IsNullOrEmpty(keyword))
{
isWhere = true;
var filterKeyword = AttackUtils.FilterSql(keyword);
whereString.Append(
$"(Text LIKE '%{filterKeyword}%' OR Summary LIKE '%{filterKeyword}%' OR Source LIKE '%{filterKeyword}%')");
}
if (!string.IsNullOrEmpty(dateFrom))
{
if (isWhere)
{
whereString.Append(" AND ");
}
isWhere = true;
whereString.Append($"(AddDate >= {SqlUtils.GetComparableDate(TranslateUtils.ToDateTime(dateFrom))})");
}
if (!string.IsNullOrEmpty(dateTo))
{
if (isWhere)
{
whereString.Append(" AND ");
}
whereString.Append($"(AddDate <= {SqlUtils.GetComparableDate(TranslateUtils.ToDateTime(dateTo))})");
}
return($"SELECT Id, Text, Summary, Source, AddDate FROM {TableName} {whereString}");
}
public List <SpecialInfo> GetSpecialInfoList(int siteId, string keyword)
{
var list = new List <SpecialInfo>();
keyword = AttackUtils.FilterSql(keyword);
var sqlString = $@"SELECT {nameof(SpecialInfo.Id)},
{nameof(SpecialInfo.SiteId)},
{nameof(SpecialInfo.Title)},
{nameof(SpecialInfo.Url)},
{nameof(SpecialInfo.AddDate)}
FROM {TableName} WHERE {nameof(SpecialInfo.SiteId)} = {siteId} AND ({nameof(SpecialInfo.Title)} LIKE '%{keyword}%' OR {nameof(SpecialInfo.Url)} LIKE '%{keyword}%') ORDER BY {nameof(SpecialInfo.Id)} DESC";
using (var rdr = ExecuteReader(sqlString))
{
while (rdr.Read())
{
list.Add(GetSpecialInfo(rdr));
}
rdr.Close();
}
return(list);
}
private void InsertWithTrans(DepartmentInfo parentInfo, DepartmentInfo departmentInfo, IDbTransaction trans)
{
if (parentInfo != null)
{
departmentInfo.ParentsPath = parentInfo.ParentsPath + "," + parentInfo.Id;
departmentInfo.ParentsCount = parentInfo.ParentsCount + 1;
var maxTaxis = GetMaxTaxisByParentPath(departmentInfo.ParentsPath);
if (maxTaxis == 0)
{
maxTaxis = parentInfo.Taxis;
}
departmentInfo.Taxis = maxTaxis + 1;
}
else
{
departmentInfo.ParentsPath = "0";
departmentInfo.ParentsCount = 0;
var maxTaxis = GetMaxTaxisByParentPath("0");
departmentInfo.Taxis = maxTaxis + 1;
}
var sqlInsert = "INSERT INTO siteserver_Department (DepartmentName, Code, ParentID, ParentsPath, ParentsCount, ChildrenCount, IsLastNode, Taxis, AddDate, Summary, CountOfAdmin) VALUES (@DepartmentName, @Code, @ParentID, @ParentsPath, @ParentsCount, @ChildrenCount, @IsLastNode, @Taxis, @AddDate, @Summary, @CountOfAdmin)";
var insertParms = new IDataParameter[]
{
GetParameter(ParmName, DataType.VarChar, 255, departmentInfo.DepartmentName),
GetParameter(ParmCode, DataType.VarChar, 50, departmentInfo.Code),
GetParameter(ParmParentId, DataType.Integer, departmentInfo.ParentId),
GetParameter(ParmParentsPath, DataType.VarChar, 255, departmentInfo.ParentsPath),
GetParameter(ParmParentsCount, DataType.Integer, departmentInfo.ParentsCount),
GetParameter(ParmChildrenCount, DataType.Integer, 0),
GetParameter(ParmIsLastNode, DataType.VarChar, 18, true.ToString()),
GetParameter(ParmTaxis, DataType.Integer, departmentInfo.Taxis),
GetParameter(ParmAddDate, DataType.DateTime, departmentInfo.AddDate),
GetParameter(ParmSummary, DataType.VarChar, 255, departmentInfo.Summary),
GetParameter(ParmCountOfAdmin, DataType.Integer, departmentInfo.CountOfAdmin)
};
string sqlString =
$"UPDATE siteserver_Department SET {SqlUtils.ToPlusSqlString("Taxis")} WHERE (Taxis >= {departmentInfo.Taxis})";
ExecuteNonQuery(trans, sqlString);
departmentInfo.Id = ExecuteNonQueryAndReturnId(TableName, nameof(DepartmentInfo.Id), trans, sqlInsert, insertParms);
if (!string.IsNullOrEmpty(departmentInfo.ParentsPath))
{
sqlString = $"UPDATE siteserver_Department SET {SqlUtils.ToPlusSqlString("ChildrenCount")} WHERE Id IN ({AttackUtils.FilterSql(departmentInfo.ParentsPath)})";
ExecuteNonQuery(trans, sqlString);
}
sqlString = $"UPDATE siteserver_Department SET IsLastNode = '{false}' WHERE ParentID = {departmentInfo.ParentId}";
ExecuteNonQuery(trans, sqlString);
//sqlString =
// $"UPDATE siteserver_Department SET IsLastNode = '{true}' WHERE (Id IN (SELECT TOP 1 Id FROM siteserver_Department WHERE ParentID = {departmentInfo.ParentId} ORDER BY Taxis DESC))";
sqlString =
$"UPDATE siteserver_Department SET IsLastNode = '{true}' WHERE (Id IN ({SqlUtils.ToInTopSqlString("siteserver_Department", "Id", $"WHERE ParentID = {departmentInfo.ParentId}", "ORDER BY Taxis DESC", 1)}))";
ExecuteNonQuery(trans, sqlString);
DepartmentManager.ClearCache();
}
public string GetStlWhereString(int siteId, string group, string groupNot, string tags, bool isTopExists, bool isTop, string where)
{
var whereStringBuilder = new StringBuilder();
if (isTopExists)
{
whereStringBuilder.Append($" AND IsTop = '{isTop}' ");
}
var databaseType = Database.DatabaseType;
if (!string.IsNullOrEmpty(group))
{
group = group.Trim().Trim(',');
var groupArr = group.Split(',');
if (groupArr.Length > 0)
{
whereStringBuilder.Append(" AND (");
foreach (var theGroup in groupArr)
{
var trimGroup = theGroup.Trim();
whereStringBuilder.Append(
$" ({nameof(Content.GroupNames)} = '{AttackUtils.FilterSql(trimGroup)}' OR {SqlUtils.GetInStr(databaseType, nameof(Content.GroupNames), trimGroup + ",")} OR {SqlUtils.GetInStr(databaseType, nameof(Content.GroupNames), "," + trimGroup + ",")} OR {SqlUtils.GetInStr(databaseType, nameof(Content.GroupNames), "," + trimGroup)}) OR ");
}
if (groupArr.Length > 0)
{
whereStringBuilder.Length -= 3;
}
whereStringBuilder.Append(") ");
}
}
if (!string.IsNullOrEmpty(groupNot))
{
groupNot = groupNot.Trim().Trim(',');
var groupNotArr = groupNot.Split(',');
if (groupNotArr.Length > 0)
{
whereStringBuilder.Append(" AND (");
foreach (var theGroupNot in groupNotArr)
{
//whereStringBuilder.Append(
// $" ({ContentAttribute.GroupNameCollection} <> '{theGroupNot.Trim()}' AND CHARINDEX('{theGroupNot.Trim()},',{ContentAttribute.GroupNameCollection}) = 0 AND CHARINDEX(',{theGroupNot.Trim()},',{ContentAttribute.GroupNameCollection}) = 0 AND CHARINDEX(',{theGroupNot.Trim()}',{ContentAttribute.GroupNameCollection}) = 0) AND ");
whereStringBuilder.Append(
$" ({nameof(Content.GroupNames)} <> '{theGroupNot.Trim()}' AND {SqlUtils.GetNotInStr(databaseType, nameof(Content.GroupNames), theGroupNot.Trim() + ",")} AND {SqlUtils.GetNotInStr(databaseType, nameof(Content.GroupNames), "," + theGroupNot.Trim() + ",")} AND {SqlUtils.GetNotInStr(databaseType, nameof(Content.GroupNames), "," + theGroupNot.Trim())}) AND ");
}
if (groupNotArr.Length > 0)
{
whereStringBuilder.Length -= 4;
}
whereStringBuilder.Append(") ");
}
}
if (!string.IsNullOrEmpty(tags))
{
tags = tags.Trim().Trim(',');
var tagNames = ListUtils.GetStringList(tags);
if (tagNames.Count > 0)
{
whereStringBuilder.Append(" AND (");
foreach (var tagName in tagNames)
{
whereStringBuilder.Append(
$" ({nameof(Content.TagNames)} = '{AttackUtils.FilterSql(tagName)}' OR {SqlUtils.GetInStr(databaseType, nameof(Content.TagNames), tagName + ",")} OR {SqlUtils.GetInStr(databaseType, nameof(Content.TagNames), "," + tagName + ",")} OR {SqlUtils.GetInStr(databaseType, nameof(Content.TagNames), "," + tagName)}) OR ");
}
whereStringBuilder.Length -= 3;
whereStringBuilder.Append(") ");
}
}
if (!string.IsNullOrEmpty(where))
{
whereStringBuilder.Append($" AND ({where}) ");
}
return(whereStringBuilder.ToString());
}
public string GetStlWhereString(int siteId, string group, string groupNot, string tags, bool isImageExists, bool isImage, bool isVideoExists, bool isVideo, bool isFileExists, bool isFile, bool isTopExists, bool isTop, bool isRecommendExists, bool isRecommend, bool isHotExists, bool isHot, bool isColorExists, bool isColor, string where)
{
var whereBuilder = new StringBuilder();
whereBuilder.Append($" AND SiteId = {siteId} ");
if (isImageExists)
{
whereBuilder.Append(isImage
? $" AND {nameof(Content.ImageUrl)} <> '' "
: $" AND {nameof(Content.ImageUrl)} = '' ");
}
if (isVideoExists)
{
whereBuilder.Append(isVideo
? $" AND {nameof(Content.VideoUrl)} <> '' "
: $" AND {nameof(Content.VideoUrl)} = '' ");
}
if (isFileExists)
{
whereBuilder.Append(isFile
? $" AND {nameof(Content.FileUrl)} <> '' "
: $" AND {nameof(Content.FileUrl)} = '' ");
}
if (isTopExists)
{
whereBuilder.Append($" AND {nameof(Content.Top)} = {StringUtils.ToLower(isTop.ToString())} ");
}
if (isRecommendExists)
{
whereBuilder.Append($" AND {nameof(Content.Recommend)} = {StringUtils.ToLower(isRecommend.ToString())} ");
}
if (isHotExists)
{
whereBuilder.Append($" AND {nameof(Content.Hot)} = {StringUtils.ToLower(isHot.ToString())} ");
}
if (isColorExists)
{
whereBuilder.Append($" AND {nameof(Content.Color)} = {StringUtils.ToLower(isColor.ToString())} ");
}
var databaseType = _settingsManager.Database.DatabaseType;
if (!string.IsNullOrEmpty(group))
{
group = group.Trim().Trim(',');
var groups = ListUtils.GetStringList(group);
if (groups.Count > 0)
{
whereBuilder.Append(" AND (");
foreach (var theGroup in groups)
{
var trimGroup = theGroup.Trim();
whereBuilder.Append(
$" ({nameof(Content.GroupNames)} = '{AttackUtils.FilterSql(trimGroup)}' OR {SqlUtils.GetInStr(databaseType, nameof(Content.GroupNames), trimGroup + ",")} OR {SqlUtils.GetInStr(databaseType, nameof(Content.GroupNames), "," + trimGroup + ",")} OR {SqlUtils.GetInStr(databaseType, nameof(Content.GroupNames), "," + trimGroup)}) OR ");
}
whereBuilder.Length -= 3;
whereBuilder.Append(") ");
}
}
if (!string.IsNullOrEmpty(groupNot))
{
groupNot = groupNot.Trim().Trim(',');
var groupNots = ListUtils.GetStringList(groupNot);
if (groupNots.Count > 0)
{
whereBuilder.Append(" AND (");
foreach (var theGroupNot in groupNots)
{
var trimGroup = theGroupNot.Trim();
//whereBuilder.Append(
// $" ({ContentAttribute.GroupNameCollection} <> '{trimGroup}' AND CHARINDEX('{trimGroup},',{ContentAttribute.GroupNameCollection}) = 0 AND CHARINDEX(',{trimGroup},',{ContentAttribute.GroupNameCollection}) = 0 AND CHARINDEX(',{trimGroup}',{ContentAttribute.GroupNameCollection}) = 0) AND ");
whereBuilder.Append(
$" ({nameof(Content.GroupNames)} <> '{trimGroup}' AND {SqlUtils.GetNotInStr(databaseType, nameof(Content.GroupNames), trimGroup + ",")} AND {SqlUtils.GetNotInStr(databaseType, nameof(Content.GroupNames), "," + trimGroup + ",")} AND {SqlUtils.GetNotInStr(databaseType, nameof(Content.GroupNames), "," + trimGroup)}) AND ");
}
whereBuilder.Length -= 4;
whereBuilder.Append(") ");
}
}
if (!string.IsNullOrEmpty(tags))
{
tags = tags.Trim().Trim(',');
var tagNames = ListUtils.GetStringList(tags);
if (tagNames.Count > 0)
{
whereBuilder.Append(" AND (");
foreach (var tagName in tagNames)
{
whereBuilder.Append(
$" ({nameof(Content.TagNames)} = '{AttackUtils.FilterSql(tagName)}' OR {SqlUtils.GetInStr(databaseType, nameof(Content.TagNames), tagName + ",")} OR {SqlUtils.GetInStr(databaseType, nameof(Content.TagNames), "," + tagName + ",")} OR {SqlUtils.GetInStr(databaseType, nameof(Content.TagNames), "," + tagName)}) OR ");
}
whereBuilder.Length -= 3;
whereBuilder.Append(") ");
}
}
if (!string.IsNullOrEmpty(where))
{
whereBuilder.Append($" AND ({where}) ");
}
return(whereBuilder.ToString());
}
public string GetStlWhereStringBySearch(string group, string groupNot, bool isImageExists, bool isImage, bool isVideoExists, bool isVideo, bool isFileExists, bool isFile, bool isTopExists, bool isTop, bool isRecommendExists, bool isRecommend, bool isHotExists, bool isHot, bool isColorExists, bool isColor, string where)
{
var whereBuilder = new StringBuilder();
if (isImageExists)
{
whereBuilder.Append(isImage
? $" AND {nameof(Content.ImageUrl)} <> '' "
: $" AND {nameof(Content.ImageUrl)} = '' ");
}
if (isVideoExists)
{
whereBuilder.Append(isVideo
? $" AND {nameof(Content.VideoUrl)} <> '' "
: $" AND {nameof(Content.VideoUrl)} = '' ");
}
if (isFileExists)
{
whereBuilder.Append(isFile
? $" AND {nameof(Content.FileUrl)} <> '' "
: $" AND {nameof(Content.FileUrl)} = '' ");
}
if (isTopExists)
{
whereBuilder.Append($" AND {nameof(Content.Top)} = {StringUtils.ToLower(isTop.ToString())} ");
}
if (isRecommendExists)
{
whereBuilder.Append($" AND {nameof(Content.Recommend)} = {StringUtils.ToLower(isRecommend.ToString())} ");
}
if (isHotExists)
{
whereBuilder.Append($" AND {nameof(Content.Hot)} = {StringUtils.ToLower(isHot.ToString())} ");
}
if (isColorExists)
{
whereBuilder.Append($" AND {nameof(Content.Color)} = {StringUtils.ToLower(isColor.ToString())} ");
}
var database = _settingsManager.Database;
if (!string.IsNullOrEmpty(group))
{
group = group.Trim().Trim(',');
var groupArr = group.Split(',');
if (groupArr != null && groupArr.Length > 0)
{
whereBuilder.Append(" AND (");
foreach (var theGroup in groupArr)
{
var trimGroup = theGroup.Trim();
whereBuilder.Append(
$" ({nameof(Content.GroupNames)} = '{AttackUtils.FilterSql(trimGroup)}' OR {DatabaseUtils.GetInStr(database, nameof(Content.GroupNames), trimGroup + ",")} OR {DatabaseUtils.GetInStr(database, nameof(Content.GroupNames), "," + trimGroup + ",")} OR {DatabaseUtils.GetInStr(database, nameof(Content.GroupNames), "," + trimGroup)}) OR ");
}
if (groupArr.Length > 0)
{
whereBuilder.Length -= 3;
}
whereBuilder.Append(") ");
}
}
if (!string.IsNullOrEmpty(groupNot))
{
groupNot = groupNot.Trim().Trim(',');
var groupNotArr = groupNot.Split(',');
if (groupNotArr != null && groupNotArr.Length > 0)
{
whereBuilder.Append(" AND (");
foreach (var theGroupNot in groupNotArr)
{
var trimGroup = theGroupNot.Trim();
//whereBuilder.Append(
// $" ({ContentAttribute.GroupNameCollection} <> '{trimGroup}' AND CHARINDEX('{trimGroup},',{ContentAttribute.GroupNameCollection}) = 0 AND CHARINDEX(',{trimGroup},',{ContentAttribute.GroupNameCollection}) = 0 AND CHARINDEX(',{trimGroup}',{ContentAttribute.GroupNameCollection}) = 0) AND ");
whereBuilder.Append(
$" ({nameof(Content.GroupNames)} <> '{trimGroup}' AND {DatabaseUtils.GetNotInStr(database, nameof(Content.GroupNames), trimGroup + ",")} AND {DatabaseUtils.GetNotInStr(database, nameof(Content.GroupNames), "," + trimGroup + ",")} AND {DatabaseUtils.GetNotInStr(database, nameof(Content.GroupNames), "," + trimGroup)}) AND ");
}
if (groupNotArr.Length > 0)
{
whereBuilder.Length -= 4;
}
whereBuilder.Append(") ");
}
}
if (!string.IsNullOrEmpty(where))
{
whereBuilder.Append($" AND ({where}) ");
}
return(whereBuilder.ToString());
}
public string GetQueryString(string name)
{
return(!string.IsNullOrEmpty(HttpRequest.QueryString[name])
? AttackUtils.FilterSql(HttpRequest.QueryString[name])
: null);
}
public string GetSelectCommend(int siteId, string logType, string userName, string keyword, string dateFrom, string dateTo)
{
if (siteId == 0 && (string.IsNullOrEmpty(logType) || StringUtils.EqualsIgnoreCase(logType, "All")) && string.IsNullOrEmpty(userName) && string.IsNullOrEmpty(keyword) && string.IsNullOrEmpty(dateFrom) && string.IsNullOrEmpty(dateTo))
{
return(GetSelectCommend());
}
var whereString = new StringBuilder("WHERE ");
var isWhere = false;
if (siteId > 0)
{
isWhere = true;
whereString.AppendFormat("(SiteId = {0})", siteId);
}
if (!string.IsNullOrEmpty(logType) && !StringUtils.EqualsIgnoreCase(logType, "All"))
{
if (isWhere)
{
whereString.Append(" AND ");
}
isWhere = true;
if (StringUtils.EqualsIgnoreCase(logType, "Channel"))
{
whereString.Append("(ChannelId > 0 AND ContentId = 0)");
}
else if (StringUtils.EqualsIgnoreCase(logType, "Content"))
{
whereString.Append("(ChannelId > 0 AND ContentId > 0)");
}
}
if (!string.IsNullOrEmpty(userName))
{
if (isWhere)
{
whereString.Append(" AND ");
}
isWhere = true;
whereString.AppendFormat("(UserName = '******')", userName);
}
if (!string.IsNullOrEmpty(keyword))
{
if (isWhere)
{
whereString.Append(" AND ");
}
isWhere = true;
whereString.AppendFormat("(Action LIKE '%{0}%' OR Summary LIKE '%{0}%')", AttackUtils.FilterSql(keyword));
}
if (!string.IsNullOrEmpty(dateFrom))
{
if (isWhere)
{
whereString.Append(" AND ");
}
isWhere = true;
whereString.Append($"(AddDate >= {SqlUtils.GetComparableDate(TranslateUtils.ToDateTime(dateFrom))})");
}
if (!string.IsNullOrEmpty(dateTo))
{
if (isWhere)
{
whereString.Append(" AND ");
}
whereString.Append($"(AddDate <= {SqlUtils.GetComparableDate(TranslateUtils.ToDateTime(dateTo))})");
}
return("SELECT Id, SiteId, ChannelId, ContentId, UserName, IpAddress, AddDate, Action, Summary FROM siteserver_SiteLog " + whereString);
}
public List <string> GetTagListByStartString(int siteId, string startString, int totalNum)
{
var sqlString = SqlUtils.GetDistinctTopSqlString("siteserver_Tag", "Tag, UseNum",
$"WHERE SiteId = {siteId} AND {SqlUtils.GetInStr("Tag", AttackUtils.FilterSql(startString))}",
"ORDER BY UseNum DESC", totalNum);
return(DataProvider.DatabaseDao.GetStringList(sqlString));
}
public List <TableStyleInfo> GetTableStyleInfoList(List <int> relatedIdentities, string tableName)
{
var list = new List <TableStyleInfo>();
string sqlString =
$"SELECT Id, RelatedIdentity, TableName, AttributeName, Taxis, DisplayName, HelpText, IsVisibleInList, InputType, DefaultValue, IsHorizontal, ExtendValues FROM siteserver_TableStyle WHERE RelatedIdentity IN ({TranslateUtils.ToSqlInStringWithoutQuote(relatedIdentities)}) AND TableName = '{AttackUtils.FilterSql(tableName)}' ORDER BY Id DESC";
using (var rdr = ExecuteReader(sqlString))
{
while (rdr.Read())
{
list.Add(GetTableStyleInfoByReader(rdr));
}
rdr.Close();
}
return(list);
}
public string FilterSql(string sql)
{
return(AttackUtils.FilterSql(sql));
}
private void InsertWithTrans(AreaInfo parentInfo, AreaInfo areaInfo, IDbTransaction trans)
{
if (parentInfo != null)
{
areaInfo.ParentsPath = parentInfo.ParentsPath + "," + parentInfo.Id;
areaInfo.ParentsCount = parentInfo.ParentsCount + 1;
var maxTaxis = GetMaxTaxisByParentPath(areaInfo.ParentsPath);
if (maxTaxis == 0)
{
maxTaxis = parentInfo.Taxis;
}
areaInfo.Taxis = maxTaxis + 1;
}
else
{
areaInfo.ParentsPath = "0";
areaInfo.ParentsCount = 0;
var maxTaxis = GetMaxTaxisByParentPath("0");
areaInfo.Taxis = maxTaxis + 1;
}
var sqlInsert = "INSERT INTO siteserver_Area (AreaName, ParentID, ParentsPath, ParentsCount, ChildrenCount, IsLastNode, Taxis, CountOfAdmin) VALUES (@AreaName, @ParentID, @ParentsPath, @ParentsCount, @ChildrenCount, @IsLastNode, @Taxis, @CountOfAdmin)";
IDataParameter[] insertParms =
{
GetParameter(ParmName, DataType.VarChar, 255, areaInfo.AreaName),
GetParameter(ParmParentId, DataType.Integer, areaInfo.ParentId),
GetParameter(ParmParentsPath, DataType.VarChar, 255, areaInfo.ParentsPath),
GetParameter(ParmParentsCount, DataType.Integer, areaInfo.ParentsCount),
GetParameter(ParmChildrenCount, DataType.Integer, 0),
GetParameter(ParmIsLastNode, DataType.VarChar, 18, true.ToString()),
GetParameter(ParmTaxis, DataType.Integer, areaInfo.Taxis),
GetParameter(ParmCountOfAdmin, DataType.Integer, areaInfo.CountOfAdmin)
};
string sqlString = $"UPDATE siteserver_Area SET {SqlUtils.ToPlusSqlString("Taxis")} WHERE (Taxis >= {areaInfo.Taxis})";
ExecuteNonQuery(trans, sqlString);
areaInfo.Id = ExecuteNonQueryAndReturnId(TableName, nameof(AreaInfo.Id), trans, sqlInsert, insertParms);
if (!string.IsNullOrEmpty(areaInfo.ParentsPath) && areaInfo.ParentsPath != "0")
{
sqlString = $"UPDATE siteserver_Area SET {SqlUtils.ToPlusSqlString("ChildrenCount")} WHERE Id IN ({AttackUtils.FilterSql(areaInfo.ParentsPath)})";
ExecuteNonQuery(trans, sqlString);
}
sqlString = $"UPDATE siteserver_Area SET IsLastNode = '{false}' WHERE ParentID = {areaInfo.ParentId}";
ExecuteNonQuery(trans, sqlString);
//sqlString =
// $"UPDATE siteserver_Area SET IsLastNode = 'True' WHERE (Id IN (SELECT TOP 1 Id FROM siteserver_Area WHERE ParentID = {areaInfo.ParentId} ORDER BY Taxis DESC))";
sqlString =
$"UPDATE siteserver_Area SET IsLastNode = '{true}' WHERE Id IN ({SqlUtils.ToInTopSqlString(TableName, "Id", $"WHERE ParentID = {areaInfo.ParentId}", "ORDER BY Taxis DESC", 1)})";
ExecuteNonQuery(trans, sqlString);
AreaManager.ClearCache();
}
public string GetSelectCommand(int groupId, string searchWord, int dayOfCreate, int dayOfLastActivity, int loginCount, string searchType)
{
var whereBuilder = new StringBuilder();
if (dayOfCreate > 0)
{
if (whereBuilder.Length > 0)
{
whereBuilder.Append(" AND ");
}
var dateTime = DateTime.Now.AddDays(-dayOfCreate);
whereBuilder.Append($"(CreateDate >= {SqlUtils.GetComparableDate(dateTime)})");
}
if (dayOfLastActivity > 0)
{
if (whereBuilder.Length > 0)
{
whereBuilder.Append(" AND ");
}
var dateTime = DateTime.Now.AddDays(-dayOfLastActivity);
whereBuilder.Append($"(LastActivityDate >= {SqlUtils.GetComparableDate(dateTime)}) ");
}
if (groupId > -1)
{
if (whereBuilder.Length > 0)
{
whereBuilder.Append(" AND ");
}
whereBuilder.Append(groupId == 0 ? "(GroupId = 0 OR GroupId IS NULL)" : $"GroupId = {groupId}");
}
searchWord = AttackUtils.FilterSql(searchWord);
if (string.IsNullOrEmpty(searchType))
{
if (whereBuilder.Length > 0)
{
whereBuilder.Append(" AND ");
}
whereBuilder.Append(
$"(UserName LIKE '%{searchWord}%' OR EMAIL LIKE '%{searchWord}%')");
}
else
{
if (whereBuilder.Length > 0)
{
whereBuilder.Append(" AND ");
}
whereBuilder.Append($"({searchType} LIKE '%{searchWord}%') ");
}
if (loginCount > 0)
{
if (whereBuilder.Length > 0)
{
whereBuilder.Append(" AND ");
}
whereBuilder.Append($"(CountOfLogin > {loginCount})");
}
var whereString = string.Empty;
if (whereBuilder.Length > 0)
{
whereString = $"WHERE {whereBuilder}";
}
return(DataProvider.DatabaseDao.GetSelectSqlString(TableName, SqlUtils.Asterisk, whereString));
}
public void Delete(List <int> relatedIdentities, string tableName)
{
if (relatedIdentities == null || relatedIdentities.Count <= 0)
{
return;
}
var sqlString =
$"DELETE FROM siteserver_TableStyle WHERE RelatedIdentity IN ({TranslateUtils.ToSqlInStringWithoutQuote(relatedIdentities)}) AND TableName = '{AttackUtils.FilterSql(tableName)}'";
ExecuteNonQuery(sqlString);
TableStyleManager.ClearCache();
}
public string[] FindUsersInRole(string roleName, string userNameToMatch)
{
var tmpUserNames = string.Empty;
string sqlString =
$"SELECT UserName FROM siteserver_AdministratorsInRoles WHERE RoleName = @RoleName AND UserName LIKE '%{AttackUtils.FilterSql(userNameToMatch)}%'";
var parms = new IDataParameter[]
{
GetParameter("@RoleName", DataType.VarChar, 255, roleName)
};
using (var rdr = ExecuteReader(sqlString, parms))
{
while (rdr.Read())
{
tmpUserNames += GetString(rdr, 0) + ",";
}
rdr.Close();
}
if (tmpUserNames.Length > 0)
{
tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
return(tmpUserNames.Split(','));
}
return(new string[0]);
}
public string GetWhereSqlString(bool isConsoleAdministrator, string creatorUserName, string searchWord, string roleName, int dayOfLastActivity, int departmentId, int areaId)
{
var whereBuilder = new StringBuilder();
if (dayOfLastActivity > 0)
{
var dateTime = DateTime.Now.AddDays(-dayOfLastActivity);
whereBuilder.Append($"(LastActivityDate >= {SqlUtils.GetComparableDate(dateTime)}) ");
}
if (!string.IsNullOrEmpty(searchWord))
{
if (whereBuilder.Length > 0)
{
whereBuilder.Append(" AND ");
}
var filterSearchWord = AttackUtils.FilterSql(searchWord);
whereBuilder.Append(
$"(UserName LIKE '%{filterSearchWord}%' OR EMAIL LIKE '%{filterSearchWord}%' OR DisplayName LIKE '%{filterSearchWord}%')");
}
if (!isConsoleAdministrator)
{
if (whereBuilder.Length > 0)
{
whereBuilder.Append(" AND ");
}
whereBuilder.Append($"CreatorUserName = '******'");
}
if (departmentId != 0)
{
if (whereBuilder.Length > 0)
{
whereBuilder.Append(" AND ");
}
whereBuilder.Append($"DepartmentId = {departmentId}");
}
if (areaId != 0)
{
if (whereBuilder.Length > 0)
{
whereBuilder.Append(" AND ");
}
whereBuilder.Append($"AreaId = {areaId}");
}
var whereString = string.Empty;
if (!string.IsNullOrEmpty(roleName))
{
if (whereBuilder.Length > 0)
{
whereString = $"AND {whereBuilder}";
}
whereString =
$"WHERE (UserName IN (SELECT UserName FROM {DataProvider.AdministratorsInRolesDao.TableName} WHERE RoleName = '{AttackUtils.FilterSql(roleName)}')) {whereString}";
}
else
{
if (whereBuilder.Length > 0)
{
whereString = $"WHERE {whereBuilder}";
}
}
return(whereString);
}
public async Task <string> GetWhereStringByStlSearchAsync(IDatabaseManager databaseManager, bool isAllSites, string siteName, string siteDir, string siteIds, string channelIndex, string channelName, string channelIds, string type, string word, string dateAttribute, string dateFrom, string dateTo, string since, int siteId, List <string> excludeAttributes, NameValueCollection form)
{
var whereBuilder = new StringBuilder();
Site site = null;
if (!string.IsNullOrEmpty(siteName))
{
site = await _siteRepository.GetSiteBySiteNameAsync(siteName);
}
else if (!string.IsNullOrEmpty(siteDir))
{
site = await _siteRepository.GetSiteByDirectoryAsync(siteDir);
}
if (site == null)
{
site = await _siteRepository.GetAsync(siteId);
}
var channelId = await _channelRepository.GetChannelIdAsync(siteId, siteId, channelIndex, channelName);
var channel = await _channelRepository.GetAsync(channelId);
if (isAllSites)
{
whereBuilder.Append("(SiteId > 0) ");
}
else if (!string.IsNullOrEmpty(siteIds))
{
whereBuilder.Append($"(SiteId IN ({TranslateUtils.ToSqlInStringWithoutQuote(ListUtils.GetIntList(siteIds))})) ");
}
else
{
whereBuilder.Append($"(SiteId = {site.Id}) ");
}
if (!string.IsNullOrEmpty(channelIds))
{
whereBuilder.Append(" AND ");
var channelIdList = new List <int>();
foreach (var theChannelId in ListUtils.GetIntList(channelIds))
{
var theChannel = await _channelRepository.GetAsync(theChannelId);
channelIdList.AddRange(
await _channelRepository.GetChannelIdsAsync(theChannel.SiteId, theChannel.Id, ScopeType.All));
}
whereBuilder.Append(channelIdList.Count == 1
? $"(ChannelId = {channelIdList[0]}) "
: $"(ChannelId IN ({TranslateUtils.ToSqlInStringWithoutQuote(channelIdList)})) ");
}
else if (channelId != siteId)
{
whereBuilder.Append(" AND ");
var channelIdList = await _channelRepository.GetChannelIdsAsync(siteId, channelId, ScopeType.All);
whereBuilder.Append(channelIdList.Count == 1
? $"(ChannelId = {channelIdList[0]}) "
: $"(ChannelId IN ({TranslateUtils.ToSqlInStringWithoutQuote(channelIdList)})) ");
}
var typeList = new List <string>();
if (string.IsNullOrEmpty(type))
{
typeList.Add(nameof(Content.Title));
}
else
{
typeList = ListUtils.GetStringList(type);
}
if (!string.IsNullOrEmpty(word))
{
whereBuilder.Append(" AND (");
foreach (var attributeName in typeList)
{
whereBuilder.Append($"[{attributeName}] LIKE '%{AttackUtils.FilterSql(word)}%' OR ");
}
whereBuilder.Length = whereBuilder.Length - 3;
whereBuilder.Append(")");
}
if (string.IsNullOrEmpty(dateAttribute))
{
dateAttribute = nameof(Content.AddDate);
}
if (!string.IsNullOrEmpty(dateFrom))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($" {dateAttribute} >= {SqlUtils.GetComparableDate(_settingsManager.Database.DatabaseType, TranslateUtils.ToDateTime(dateFrom))} ");
}
if (!string.IsNullOrEmpty(dateTo))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($" {dateAttribute} <= {SqlUtils.GetComparableDate(_settingsManager.Database.DatabaseType, TranslateUtils.ToDateTime(dateTo))} ");
}
if (!string.IsNullOrEmpty(since))
{
var sinceDate = DateTime.Now.AddHours(-DateUtils.GetSinceHours(since));
whereBuilder.Append($" AND {dateAttribute} BETWEEN {SqlUtils.GetComparableDateTime(_settingsManager.Database.DatabaseType, sinceDate)} AND {SqlUtils.GetComparableNow(_settingsManager.Database.DatabaseType)} ");
}
var tableName = _channelRepository.GetTableName(site, channel);
//var styleInfoList = RelatedIdentities.GetTableStyleInfoList(site, channel.Id);
foreach (string key in form.Keys)
{
if (ListUtils.ContainsIgnoreCase(excludeAttributes, key))
{
continue;
}
if (string.IsNullOrEmpty(form[key]))
{
continue;
}
var value = StringUtils.Trim(form[key]);
if (string.IsNullOrEmpty(value))
{
continue;
}
var columnInfo = await databaseManager.GetTableColumnInfoAsync(tableName, key);
if (columnInfo != null && (columnInfo.DataType == DataType.VarChar || columnInfo.DataType == DataType.Text))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($"({key} LIKE '%{value}%')");
}
//else
//{
// foreach (var tableStyleInfo in styleInfoList)
// {
// if (StringUtils.EqualsIgnoreCase(tableStyleInfo.AttributeName, key))
// {
// whereBuilder.Append(" AND ");
// whereBuilder.Append($"({ContentAttribute.SettingsXml} LIKE '%{key}={value}%')");
// break;
// }
// }
//}
}
return(whereBuilder.ToString());
}
public List <string> GetKeywordListByContent(string content)
{
//string sqlString =
// $"SELECT Keyword FROM siteserver_Keyword WHERE CHARINDEX(Keyword, '{PageUtils.FilterSql(content)}') > 0";
var sqlString = $"SELECT Keyword FROM siteserver_Keyword WHERE {SqlUtils.GetInStrReverse(AttackUtils.FilterSql(content), nameof(KeywordInfo.Keyword))}";
return(DataProvider.DatabaseDao.GetStringList(sqlString));
}
private void UpdateSubtractChildrenCount(string parentsPath, int subtractNum)
{
if (!string.IsNullOrEmpty(parentsPath))
{
var sqlString = string.Concat("UPDATE siteserver_Department SET ChildrenCount = ChildrenCount - ", subtractNum, " WHERE Id IN (", AttackUtils.FilterSql(parentsPath), ")");
ExecuteNonQuery(sqlString);
DepartmentManager.ClearCache();
}
}