/// <summary>
/// Loads a <see cref="DeliveryBook"/> collection from the database, based on given criteria.
/// </summary>
/// <param name="crit">The fetch criteria.</param>
protected void DataPortal_Fetch(DeliveryBookCriteriaGet crit)
{
using (var ctx = ConnectionManager <SqlConnection> .GetManager(Database.InterwayDocsConnection, false))
{
using (var cmd = new SqlCommand(GetDeliveryBookInlineQuery(crit), ctx.Connection))
{
var fullText = string.Empty;
if (!string.IsNullOrEmpty(crit.FullText))
{
fullText = "%" + crit.FullText + "%";
}
cmd.CommandType = CommandType.Text;
if (!crit.CriteriaStartDate.IsEmpty)
{
cmd.Parameters.AddWithValue("@CriteriaStartDate", crit.CriteriaStartDate.DBValue).DbType = DbType.Date;
}
if (!crit.CriteriaEndDate.IsEmpty)
{
cmd.Parameters.AddWithValue("@CriteriaEndDate", crit.CriteriaEndDate.DBValue).DbType = DbType.Date;
}
if (!string.IsNullOrEmpty(crit.FullText))
{
cmd.Parameters.AddWithValue("@FullText", fullText).DbType = DbType.String;
}
var args = new DataPortalHookArgs(cmd, crit);
OnFetchPre(args);
LoadCollection(cmd);
OnFetchPost(args);
}
}
}
private string GetSelectPart(DeliveryBookCriteriaGet crit)
{
var query = "SELECT " + QueryList + Environment.NewLine + "FROM DeliveryRegisters";
if (!string.IsNullOrEmpty(crit.FullText))
{
query += ", DeliveryView";
}
query += Environment.NewLine;
return(query);
}
private string GetDeliveryBookInlineQuery(DeliveryBookCriteriaGet crit)
{
var clauseStatus = ClauseStatus.NoClause;
var query = GetSelectPart(crit);
if (!string.IsNullOrEmpty(crit.FullText) ||
!crit.CriteriaStartDate.IsEmpty ||
!crit.CriteriaEndDate.IsEmpty)
{
query = GetWherePart(query, crit, ref clauseStatus);
}
query = GetOrderPart(query);
return(query);
}
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;
}
}
/// <summary>
/// Factory method. Loads a <see cref="DeliveryBook"/> collection, based on given parameters.
/// </summary>
/// <param name="crit">The fetch criteria.</param>
/// <returns>A reference to the fetched <see cref="DeliveryBook"/> collection.</returns>
public static DeliveryBook GetDeliveryBook(DeliveryBookCriteriaGet crit)
{
return(DataPortal.Fetch <DeliveryBook>(crit));
}
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);
}
