Example #1
0
        /// <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;
            }
        }
Example #5
0
 /// <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);
        }