public string GetWhereStringBySearchOutput(PublishmentSystemInfo publishmentSystemInfo, int nodeId, ETableStyle tableStyle, string word, StringCollection typeCollection, string channelId, string dateFrom, string dateTo, string date, string dateAttribute, string excludeAttributes, NameValueCollection form)
{
var whereBuilder = new StringBuilder();
if (typeCollection.Count == 0)
{
typeCollection.Add(ContentAttribute.Title);
if (tableStyle == ETableStyle.BackgroundContent)
{
typeCollection.Add(BackgroundContentAttribute.Content);
}
else if (tableStyle == ETableStyle.JobContent)
{
typeCollection.Add(JobContentAttribute.Location);
typeCollection.Add(JobContentAttribute.Department);
typeCollection.Add(JobContentAttribute.Requirement);
typeCollection.Add(JobContentAttribute.Responsibility);
}
}
whereBuilder.Append($"(PublishmentSystemID = {publishmentSystemInfo.PublishmentSystemId}) ");
if (!string.IsNullOrEmpty(word))
{
whereBuilder.Append(" AND (");
foreach (var attributeName in typeCollection)
{
whereBuilder.Append($"[{attributeName}] like '%{PageUtils.FilterSql(word)}%' OR ");
}
whereBuilder.Length = whereBuilder.Length - 3;
whereBuilder.Append(")");
}
if (!string.IsNullOrEmpty(channelId))
{
var theChannelId = TranslateUtils.ToInt(channelId);
if (theChannelId > 0 && theChannelId != publishmentSystemInfo.PublishmentSystemId)
{
whereBuilder.Append(" AND ");
var nodeIdList = DataProvider.NodeDao.GetNodeIdListForDescendant(theChannelId);
nodeIdList.Add(theChannelId);
whereBuilder.Append(nodeIdList.Count == 1
? $"(NodeID = {nodeIdList[0]}) "
: $"(NodeID IN ({TranslateUtils.ToSqlInStringWithoutQuote(nodeIdList)})) ");
}
}
if (!string.IsNullOrEmpty(dateFrom))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($" {dateAttribute} >= '{dateFrom}' ");
}
if (!string.IsNullOrEmpty(dateTo))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($" {dateAttribute} <= '{dateTo}' ");
}
if (!string.IsNullOrEmpty(date))
{
var days = TranslateUtils.ToInt(date);
if (days > 0)
{
whereBuilder.Append(" AND ");
whereBuilder.Append(SqlUtils.GetDateDiffLessThanDays(dateAttribute, days.ToString()));
}
}
var styleInfoList = RelatedIdentities.GetTableStyleInfoList(publishmentSystemInfo, tableStyle, nodeId);
var tableName = RelatedIdentities.GetTableName(publishmentSystemInfo, tableStyle, nodeId);
var arraylist = TranslateUtils.StringCollectionToStringList(excludeAttributes);
foreach (string key in form.Keys)
{
if (arraylist.Contains(key.ToLower()))
{
continue;
}
if (!string.IsNullOrEmpty(form[key]))
{
var value = StringUtils.Trim(form[key]);
if (!string.IsNullOrEmpty(value))
{
if (TableManager.IsAttributeNameExists(tableStyle, tableName, key))
{
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;
}
}
}
if (tableStyle == ETableStyle.GovPublicContent)
{
if (StringUtils.EqualsIgnoreCase(key, GovPublicContentAttribute.DepartmentId))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($"([{key}] = {TranslateUtils.ToInt(value)})");
}
else if (StringUtils.EqualsIgnoreCase(key, GovPublicContentAttribute.Category1Id))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($"([{key}] = {TranslateUtils.ToInt(value)})");
}
else if (StringUtils.EqualsIgnoreCase(key, GovPublicContentAttribute.Category2Id))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($"([{key}] = {TranslateUtils.ToInt(value)})");
}
else if (StringUtils.EqualsIgnoreCase(key, GovPublicContentAttribute.Category3Id))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($"([{key}] = {TranslateUtils.ToInt(value)})");
}
else if (StringUtils.EqualsIgnoreCase(key, GovPublicContentAttribute.Category4Id))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($"([{key}] = {TranslateUtils.ToInt(value)})");
}
else if (StringUtils.EqualsIgnoreCase(key, GovPublicContentAttribute.Category5Id))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($"([{key}] = {TranslateUtils.ToInt(value)})");
}
else if (StringUtils.EqualsIgnoreCase(key, GovPublicContentAttribute.Category6Id))
{
whereBuilder.Append(" AND ");
whereBuilder.Append($"([{key}] = {TranslateUtils.ToInt(value)})");
}
}
}
}
}
return(whereBuilder.ToString());
}
