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