private void BuildWhereClause(DeliveryBookCriteriaGet crit, string dateTypeName, ref string query,
ref ClauseStatus clauseStatus)
{
if (!crit.CriteriaStartDate.IsEmpty)
{
QueryBuilder.AddClause(ref query, ref clauseStatus);
query += QueryBuilder.NormalizeToDate("DeliveryRegisters.", dateTypeName) + " >= @CriteriaStartDate";
clauseStatus = ClauseStatus.ClauseAdded;
}
if (!crit.CriteriaEndDate.IsEmpty)
{
QueryBuilder.AddClause(ref query, ref clauseStatus);
query += QueryBuilder.NormalizeToDate("DeliveryRegisters.", dateTypeName) + " <= @CriteriaEndDate";
clauseStatus = ClauseStatus.ClauseAdded;
}
}
internal static void AddClause(ref string query, ref ClauseStatus status, bool useOr = false)
{
if (status == ClauseStatus.NoClause)
{
status = ClauseStatus.WhereAdded;
query += "WHERE" + Environment.NewLine;
}
else if (status == ClauseStatus.ClauseAdded)
{
if (useOr)
{
query += " OR" + Environment.NewLine;
}
else
{
query += " AND" + Environment.NewLine;
}
}
}
private string GetWherePart(string query, DeliveryBookCriteriaGet crit, ref ClauseStatus clauseStatus)
{
QueryBuilder.AddClause(ref query, ref clauseStatus);
if (!string.IsNullOrEmpty(crit.FullText))
{
query += "DeliveryView.RegisterId = DeliveryRegisters.RegisterId";
clauseStatus = ClauseStatus.ClauseAdded;
}
if (crit.SelectedDateTypeName == "AllDates")
{
if (!string.IsNullOrEmpty(crit.StartDate) && !string.IsNullOrEmpty(crit.EndDate))
{
QueryBuilder.AddClause(ref query, ref clauseStatus);
query += "(" + Environment.NewLine;
for (var dateTypeItem = 0; dateTypeItem < crit.DateTypeList.Count - 1; dateTypeItem++)
{
if (dateTypeItem > 0)
{
QueryBuilder.AddOrClause(ref query, ref clauseStatus);
}
var dateTypeName = crit.DateTypeList[dateTypeItem].DateTypeName;
query += "(";
query += QueryBuilder.NormalizeToDate("DeliveryRegisters.", dateTypeName) +
" >= @CriteriaStartDate";
clauseStatus = ClauseStatus.ClauseAdded;
QueryBuilder.AddClause(ref query, ref clauseStatus);
query += QueryBuilder.NormalizeToDate("DeliveryRegisters.", dateTypeName) +
" <= @CriteriaEndDate";
clauseStatus = ClauseStatus.ClauseAdded;
query += ")";
}
query += Environment.NewLine + ")";
}
else
{
QueryBuilder.AddClause(ref query, ref clauseStatus);
query += "(" + Environment.NewLine;
for (var dateTypeItem = 0; dateTypeItem < crit.DateTypeList.Count - 1; dateTypeItem++)
{
if (!crit.CriteriaStartDate.IsEmpty)
{
QueryBuilder.AddOrClause(ref query, ref clauseStatus);
var dateTypeName = crit.DateTypeList[dateTypeItem].DateTypeName;
query += QueryBuilder.NormalizeToDate("DeliveryRegisters.", dateTypeName) +
" >= @CriteriaStartDate";
clauseStatus = ClauseStatus.ClauseAdded;
}
if (!crit.CriteriaEndDate.IsEmpty)
{
QueryBuilder.AddOrClause(ref query, ref clauseStatus);
var dateTypeName = crit.DateTypeList[dateTypeItem].DateTypeName;
query += QueryBuilder.NormalizeToDate("DeliveryRegisters.", dateTypeName) +
" <= @CriteriaEndDate";
clauseStatus = ClauseStatus.ClauseAdded;
}
}
query += Environment.NewLine + ")";
}
}
else
{
foreach (var dateTypeInfo in crit.DateTypeList)
{
if (dateTypeInfo.DateTypeName == crit.SelectedDateTypeName)
{
BuildWhereClause(crit, crit.SelectedDateTypeName, ref query, ref clauseStatus);
break;
}
}
}
if (!string.IsNullOrEmpty(crit.FullText))
{
QueryBuilder.AddClause(ref query, ref clauseStatus);
query += "(" + Environment.NewLine;
query += "FullText LIKE @FullText";
clauseStatus = ClauseStatus.ClauseAdded;
QueryBuilder.AddClause(ref query, ref clauseStatus, true);
query += "FullText LIKE @FullText21";
QueryBuilder.AddClause(ref query, ref clauseStatus, true);
query += "FullText LIKE @FullText22";
QueryBuilder.AddClause(ref query, ref clauseStatus, true);
query += "FullText LIKE @FullText31";
QueryBuilder.AddClause(ref query, ref clauseStatus, true);
query += "FullText LIKE @FullText32";
QueryBuilder.AddClause(ref query, ref clauseStatus, true);
query += "FullText LIKE @FullText33";
QueryBuilder.AddClause(ref query, ref clauseStatus, true);
query += "FullText LIKE @FullText34";
QueryBuilder.AddClause(ref query, ref clauseStatus, true);
query += "FullText LIKE @FullText35";
QueryBuilder.AddClause(ref query, ref clauseStatus, true);
query += "FullText LIKE @FullText36";
query += Environment.NewLine;
query += ")";
}
return(query);
}
internal static void AddOrClause(ref string query, ref ClauseStatus status)
{
AddClause(ref query, ref status, true);
}
