private void SetCustomSearchCriteria(ref SearchBuilder sb) { sb.Table = "[v_Asset]"; if (AuditAssetHistoryFinder != null) { AuditAssetHistoryFinder.OnlyDistinctAssetIds = true; sb.Criteria.Add(sb.TableAlias + ".AssetId IN (" + AuditAssetHistoryFinder.FindQuery + ")"); } if (Orientation != Orientation.All) { string sql = string.Empty; switch (Orientation) { case (Orientation.Portrait): sql = string.Format("[Height] > [Width]"); break; case (Orientation.Landscape): sql = string.Format("[Height] < [Width]"); break; case (Orientation.Square): sql = string.Format("[Height] = [Width]"); break; } if (sql != string.Empty) { sb.Criteria.Add(string.Format("({0})", sql)); } } if (GeneralKeyword != string.Empty) { if (FullTextSearchedEnabled) { UserQueryParser uq = new UserQueryParser(); bool isValid = uq.ParseTokens(GeneralKeyword); if (isValid) { string query = uq.GetSqlQuery(); SetFullTextSearchCriteria(sb, query); } else { string error = string.Format("Error parsing user query: \"{0}\" - {1}", GeneralKeyword, uq.Error); Debug.WriteLine(error); } } else { JoinableList jList = GetStandardSectorSearchSql(); sb.Criteria.Add(string.Format("({0})", jList)); } } if (IsBeforePublicationDate) { sb.Criteria.Add(string.Format("({0} > getdate())", Asset.Columns.PublishDate)); } if (IsExpired.HasValue) { sb.Criteria.Add("dbo.IsExpired(" + sb.TableAlias + ".ExpiryDate) = @isExpired"); sb.AddDataParameter("@isExpired", SqlUtils.BitValue(IsExpired.GetValueOrDefault())); } if (!ExpiryDateRange.IsNull) { const string dateFormat = "dd MMMM yyyy HH:mm:ss"; if (ExpiryDateRange.StartDate.HasValue && ExpiryDateRange.EndDate.HasValue) { sb.Criteria.Add(string.Format("({0} BETWEEN '{1}' AND '{2}')", Asset.Columns.ExpiryDate, ExpiryDateRange.StartDate.Value.ToString(dateFormat), ExpiryDateRange.EndDate.Value.ToString(dateFormat))); } else { if (ExpiryDateRange.StartDate.HasValue) { sb.Criteria.Add(string.Format("({0} >= '{1}')", Asset.Columns.ExpiryDate, ExpiryDateRange.StartDate.Value.ToString(dateFormat))); } if (ExpiryDateRange.EndDate.HasValue) { sb.Criteria.Add(string.Format("({0} <= '{1}')", Asset.Columns.ExpiryDate, ExpiryDateRange.EndDate.Value.ToString(dateFormat))); } } } foreach (ComplexCriteria cc in m_ComplexCriteria) { string operand; switch (cc.CompareType) { case (CompareType.LessThan): operand = "<"; break; case (CompareType.MoreThan): operand = ">"; break; default: operand = "="; break; } sb.Criteria.Add(string.Format("({0} {1} {2})", cc.Field, operand, cc.Value)); } if (IsCompletelyPublished) { // Entire clause JoinableList jList1 = new JoinableList(" OR "); // Currently published JoinableList jList2 = new JoinableList(" AND "); jList2.Add(string.Format("{0} = {1}", Asset.Columns.AssetPublishStatusId, Convert.ToInt32(AssetPublishStatus.Published))); jList2.Add(string.Format("{0} < getdate()", Asset.Columns.PublishDate)); jList2.Add(string.Format("{0} > getdate()", Asset.Columns.ExpiryDate)); // Add to entire clause jList1.Add(jList2); // If unpublished and expired assets need to be displayed too, we need to expand this criteria as follows: // 1. All admin users should see their own assets // 2. Brand admins should see assets in their primary brand // 3. Super admins should see all assets if (IncludeUnpublishedExpiredAssets) { JoinableList jList3 = new JoinableList(" OR "); if (IncludeUnpublishedExpiredAssets_UserId > 0) { jList3.Add(string.Format("({0}={1})", Asset.Columns.UploadedByUserId, IncludeUnpublishedExpiredAssets_UserId)); } if (IncludeUnpublishedExpiredAssets_BrandId > 0) { jList3.Add(string.Format("({0}={1})", Asset.Columns.BrandId, IncludeUnpublishedExpiredAssets_BrandId)); } if (jList3.Count > 0) { jList1.Add(jList3); } } string criteria = jList1.ToString(); if (!StringUtils.IsBlank(criteria)) { sb.Criteria.Add(string.Format("({0})", criteria)); } } if (IsPublished.HasValue) { string op = (IsPublished.Value) ? " = " : " <> "; sb.Criteria.Add(string.Concat(Asset.Columns.AssetPublishStatusId, op, Convert.ToInt32(AssetPublishStatus.Published))); } if (BrandIdList.Count > 0) { JoinableList jList = new JoinableList(BrandIdList); sb.Criteria.Add(string.Format("BrandId IN ({0})", jList)); } // Production date filter SetProductionMonthDayCriteria(sb); // Metadata filters AddMetadataCriteria(sb); // Category filters AddManyToManyCriteria(sb, "AssetCategory", "CategoryId", CategoryIdList); // Setup results ordering AddOrderByClause(); Debug.WriteLine(string.Format("AssetFinder: {0}", sb.GetFullQuery())); }