/// <summary>
/// Determines if FTS search can be used for the given table, columns, and keyword.
/// </summary>
/// <param name="tableName">The name of the table being searched</param>
/// <param name="columnList">The list of columns being selected</param>
/// <param name="searchPattern">The search pattern or keyword</param>
/// <returns>True if FTS will be available for this search, false otherwise.</returns>
public static bool UseFtsSearch(string tableName, string columnList, string searchPattern)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("tableName", "The table name must be specified.");
}
if (string.IsNullOrEmpty(columnList))
{
throw new ArgumentException("At least one column must be specified.", "columns");
}
// IF THERE IS NO SEARCH KEYWORD WE DON'T NEED TO WORRY ABOUT FTS
if (string.IsNullOrEmpty(searchPattern))
{
return(false);
}
// PARSE THE COLUMN LIST
List <string> columns = new List <string>(columnList.Split(",".ToCharArray()));
// DETERMINE IF FTS SEARCH WILL BE AVAILABLE
return(Store.GetCachedSettings().FullTextSearch &&
KeywordSearchHelper.IsSearchPatternSupportedForFts(searchPattern) &&
ColumnsIndexed(tableName, columns));
}
/// <summary>
/// Gets a SQL filter for the given table, columns, and keyword
/// </summary>
/// <param name="tableName">Name of the table being searched</param>
/// <param name="columnPrefix">Table name or alias that must be applied to the column(s)</param>
/// <param name="columnList">The column(s) being searched, multiple columns should be comma delimited with no spaces.</param>
/// <param name="parameterName">The name to use for the SQL parameter.</param>
/// <param name="defaultToSubstring">If true, the search pattern will be converted to a substring match unless otherwise specified. When false, the search pattern is used as is. This applies only to non-FTS searches.</param>
/// <param name="searchPattern">Pattern to search for - this value may be normalized by the procedure and must be passed by reference</param>
/// <returns>A clause suitable for use in a SQL where statement</returns>
/// <remarks>The returned SQL string will use a parameter - specified by parameterName. This must be populated when executing the query.</remarks>
internal static string PrepareSqlFilter(string tableName, string columnPrefix, string columnList, string parameterName, bool defaultToSubstring, ref string searchPattern)
{
// VALIDATE INPUTS
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("tableName", "The table name must be specified.");
}
if (string.IsNullOrEmpty(columnList))
{
throw new ArgumentException("At least one column must be specified.", "columns");
}
if (string.IsNullOrEmpty(parameterName))
{
throw new ArgumentNullException("parameterName", "The parameter name must be specified.");
}
if (string.IsNullOrEmpty(searchPattern))
{
throw new ArgumentNullException("searchPattern", "The search pattern must be specified.");
}
// MAKE SURE TABLE PREFIX HAS A TERMINAL .
if (!string.IsNullOrEmpty(columnPrefix) && !columnPrefix.EndsWith("."))
{
columnPrefix += ".";
}
// MAKE SURE PARAMETER NAME HAS APPROPRIATE PREFIX
if (!parameterName.StartsWith("@"))
{
parameterName = "@" + parameterName;
}
// PARSE THE COLUMN LIST
List <string> columns = new List <string>(columnList.Split(",".ToCharArray()));
// DETERMINE THE SEARCH TYPE
if (Store.GetCachedSettings().FullTextSearch &&
KeywordSearchHelper.IsSearchPatternSupportedForFts(searchPattern) &&
ColumnsIndexed(tableName, columns))
{
// CREATE A SEARCH FILTER USING FTS
searchPattern = (new FTSQueryParser(searchPattern)).NormalForm;
return(" AND CONTAINS(" + PrepareFtsColumnList(tableName, columnPrefix, columns) + ", " + parameterName + ")");
}
else
{
// CREATE A SEARCH FILTER WITHOUT USING FTS
return(PrepareNonFtsSqlFilter(columnPrefix, columns, parameterName, defaultToSubstring, ref searchPattern));
}
}
/// <summary>
/// Builds a select command using this object, the given sort expression and the count value.
/// </summary>
/// <param name="sortExpression">The sort expression to use in the select command</param>
/// <param name="count">The number of rows to retrieve</param>
/// <returns>The DbCommand object representing the required select command</returns>
private DbCommand GetFilteredOrderIds(int maximumRows, int startRowIndex, string sortExpression, bool count)
{
// ADD THE ORDER TABLE PREFIX TO AVOID AMBIGIOUS COLUMN SQL ERROR
if (string.IsNullOrEmpty(sortExpression))
{
sortExpression = "ac_Orders.OrderDate DESC";
}
else if (!sortExpression.StartsWith("ac_Orders."))
{
sortExpression = "ac_Orders." + sortExpression;
}
Token token = Token.Instance;
Database database = token.Database;
Store store = token.Store;
// CREATE A BLANK SQL COMMAND TO BE POPULATED
DbCommand selectCommand = database.GetSqlStringCommand("SELECT");
// BUILD A LIST OF THE SOURCE TABLE(S)
bool distinct = false;
StringBuilder fromTables = new StringBuilder();
fromTables.Append("ac_Orders");
// DETERMINE FTS PATH
bool useFtsSearch = false;
if (!string.IsNullOrEmpty(this.Keyword))
{
useFtsSearch = Store.GetCachedSettings().FullTextSearch&& KeywordSearchHelper.IsSearchPatternSupportedForFts(this.Keyword);
if (!useFtsSearch)
{
// BASIC SEARCH REQUIRES TABLE JOINS
if ((this.KeywordField & KeywordFieldType.ShippingInfo) == KeywordFieldType.ShippingInfo)
{
distinct = true;
fromTables.Append(" INNER JOIN ac_OrderShipments ON ac_Orders.OrderId = ac_OrderShipments.OrderId");
if ((this.KeywordField & KeywordFieldType.Notes) == KeywordFieldType.Notes)
{
fromTables.Insert(0, "(");
fromTables.Append(") INNER JOIN ac_OrderNotes ON ac_Orders.OrderId = ac_OrderNotes.OrderId");
}
}
else if ((this.KeywordField & KeywordFieldType.Notes) == KeywordFieldType.Notes)
{
distinct = true;
fromTables.Append(" INNER JOIN ac_OrderNotes ON ac_Orders.OrderId = ac_OrderNotes.OrderId");
}
}
}
fromTables.Insert(0, " FROM ");
// BUILD THE WHERE CRITERIA
List <string> whereCriteria = new List <string>();
// ALL FILTERS MUST INCLUDE STOREID
whereCriteria.Add("ac_Orders.StoreId = @storeId");
database.AddInParameter(selectCommand, "storeId", DbType.Int32, Token.Instance.StoreId);
// ADD DATE FILTER
if (OrderDateStart > DateTime.MinValue)
{
whereCriteria.Add("ac_Orders.OrderDate >= @orderDateStart");
database.AddInParameter(selectCommand, "orderDateStart", DbType.DateTime, this.OrderDateStart);
}
if (OrderDateEnd > DateTime.MinValue && OrderDateEnd < DateTime.MaxValue)
{
whereCriteria.Add("ac_Orders.OrderDate <= @orderDateEnd");
database.AddInParameter(selectCommand, "orderDateEnd", DbType.DateTime, this.OrderDateEnd);
}
// ORDER ID RANGE FILTER
IdRangeParser orderIdRangeParser = new IdRangeParser("ac_Orders.OrderId", _OrderIdRange, "oi");
if (orderIdRangeParser.RangeCount > 0)
{
whereCriteria.Add(orderIdRangeParser.GetSqlString(string.Empty));
orderIdRangeParser.AddParameters(database, selectCommand);
}
// ORDER NUMBER RANGE FILTER
IdRangeParser orderNumberRangeFilter = new IdRangeParser("ac_Orders.OrderNumber", _OrderNumberRange, "on");
if (orderNumberRangeFilter.RangeCount > 0)
{
whereCriteria.Add(orderNumberRangeFilter.GetSqlString(string.Empty));
orderNumberRangeFilter.AddParameters(database, selectCommand);
}
// ADD ORDER STATUS FILTER
if (this.OrderStatus.Count > 0)
{
if (OrderStatus.Count > 1)
{
whereCriteria.Add("ac_Orders.OrderStatusId IN ('" + this.OrderStatus.ToList("','") + "')");
}
else
{
whereCriteria.Add("ac_Orders.OrderStatusId = @orderStatusId");
database.AddInParameter(selectCommand, "orderStatusId", DbType.Int32, this.OrderStatus[0]);
}
}
// ADD PAYMENT STATUS FILTER
if (this.PaymentStatus != OrderPaymentStatus.Unspecified)
{
whereCriteria.Add("PaymentStatusId = @paymentStatus");
database.AddInParameter(selectCommand, "paymentStatus", System.Data.DbType.Byte, this.PaymentStatus);
}
// ADD SHIPMENT STATUS FILTER
if (this.ShipmentStatus != OrderShipmentStatus.Unspecified)
{
whereCriteria.Add("ShipmentStatusId = @shipmentStatus");
database.AddInParameter(selectCommand, "shipmentStatus", System.Data.DbType.Byte, this.ShipmentStatus);
}
if (!string.IsNullOrEmpty(this.Keyword))
{
// DETERMINE THE SEARCH TYPE
if (useFtsSearch)
{
// CREATE A SEARCH FILTER USING FTS
string searchPattern = (new FTSQueryParser(this.Keyword)).NormalForm;
List <string> containsCriteria = new List <string>();
if ((this.KeywordField & KeywordFieldType.BillingInfo) == KeywordFieldType.BillingInfo)
{
containsCriteria.Add("ac_Orders.OrderId IN (SELECT OrderId FROM ac_Orders WHERE CONTAINS(*, @keyword))");
}
if ((this.KeywordField & KeywordFieldType.ShippingInfo) == KeywordFieldType.ShippingInfo)
{
containsCriteria.Add("ac_Orders.OrderId IN (SELECT OrderId FROM ac_OrderShipments WHERE CONTAINS(*, @keyword))");
}
if ((this.KeywordField & KeywordFieldType.Notes) == KeywordFieldType.Notes)
{
containsCriteria.Add("ac_Orders.OrderId IN (SELECT OrderId FROM ac_OrderNotes WHERE CONTAINS(*, @keyword))");
}
if (containsCriteria.Count == 1)
{
whereCriteria.Add(containsCriteria[0]);
}
else
{
whereCriteria.Add("(" + string.Join(" OR ", containsCriteria.ToArray()) + ")");
}
database.AddInParameter(selectCommand, "keyword", DbType.String, searchPattern);
}
else
{
// BUILD COLUMNS FOR BASIC (NON-FTS) SEARCH
List <string> columns = new List <string>();
if ((this.KeywordField & KeywordFieldType.BillingInfo) == KeywordFieldType.BillingInfo)
{
columns.Add("BillToFirstName");
columns.Add("BillToLastName");
columns.Add("BillToCompany");
columns.Add("BillToAddress1");
columns.Add("BillToAddress2");
columns.Add("BillToCity");
columns.Add("BillToProvince");
columns.Add("BillToPostalCode");
columns.Add("BillToPhone");
columns.Add("BillToFax");
columns.Add("BillToEmail");
}
if ((this.KeywordField & KeywordFieldType.ShippingInfo) == KeywordFieldType.ShippingInfo)
{
columns.Add("ShipToFirstName");
columns.Add("ShipToLastName");
columns.Add("ShipToCompany");
columns.Add("ShipToAddress1");
columns.Add("ShipToAddress2");
columns.Add("ShipToCity");
columns.Add("ShipToProvince");
columns.Add("ShipToPostalCode");
columns.Add("ShipToPhone");
columns.Add("ShipToFax");
columns.Add("ShipToEmail");
columns.Add("ShipMessage");
}
if ((this.KeywordField & KeywordFieldType.Notes) == KeywordFieldType.Notes)
{
columns.Add("Comment");
}
// PARSE THE CRITERIA AND ADD TO THE QUERY
KeywordCriterion criterion = KeywordSearchHelper.ParseKeywordCriterion(this.Keyword, columns.ToArray());
whereCriteria.Add(criterion.WhereClause);
foreach (DatabaseParameter param in criterion.Parameters)
{
database.AddInParameter(selectCommand, param.Name, param.DbType, param.Value);
}
}
}
// COMPILE THE FINAL QUERY
StringBuilder sqlBuilder = new StringBuilder();
if (!count)
{
// BUILD SELECT TO RETRIEVE ALL DATA
sqlBuilder.Append("SELECT");
if (distinct)
{
sqlBuilder.Append(" DISTINCT");
}
if (maximumRows > 0)
{
sqlBuilder.Append(" TOP " + (startRowIndex + maximumRows).ToString());
}
sqlBuilder.Append(" ac_Orders.OrderId");
string sortColumn = sortExpression.Replace(" ASC", "").Replace(" DESC", "");
if (sortColumn != "ac_Orders.OrderId")
{
sqlBuilder.Append("," + sortColumn);
}
}
else
{
// BUILD COUNT COMMAND
sqlBuilder.Append("SELECT COUNT(");
if (distinct)
{
sqlBuilder.Append("DISTINCT ");
}
sqlBuilder.Append("ac_Orders.OrderId) AS OrderCount");
}
sqlBuilder.Append(fromTables.ToString());
sqlBuilder.Append(" WHERE " + string.Join(" AND ", whereCriteria.ToArray()) + " ");
// ADD IN SORT EXPRESSION
if (!count)
{
sqlBuilder.Append("ORDER BY " + sortExpression);
}
// SET SQL STRING AND RETURN COMMAND
selectCommand.CommandText = sqlBuilder.ToString();
return(selectCommand);
}
){kind=link}