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()); }