First,
Business logic does not belong in the database, no matter what the best DBAs tell you. Putting some in an SP and some in the application is asking for trouble because it's spread out all over the place.
As for refactoring this, take each case and make it it's own method. This is just step 1. Loop, Switch (case), and If statements should be considered candidates for their own methods, and maybe even their own classes. Always remember the Single Responsibility rule: each class should have one and only one purpose. Then, each method in that class should have one and only one purpose.
Once you've got things refactored into smaller methods, look at it for general patterns. This is step 2. Chain of Responsibility and Strategy patterns may help here.
There is also LOTS of tight coupling in this method. GetDCIComponents, logging, etc, and lots of magic numbers. What is Log.Format(4)? 4 is meaningless and does magic things. Look at using an enum. Or even better, just pass the message to the logger and let it deal with everything else.
>Hi everybody,
>
>I have a code that is done in a loop and it has continue statements (VFP loop analogue). Now, I want to take this inner logic of the loop and somehow made it another method which I am going to return as part of different procedure.
>
>While I was writing this message, I think I figured out what I should do instead of continue in that method. I think I need to use premature returns of false in in the main body I will check for method's return and execute continue there.
>
>Does it sound like correct approach?
>
>Here is the current code in the loop which I'd like to make a separate method to re-use:
>
>
> String department = "", category = "", item = "";
> GetDCIComponents(dci, out department, out category, out item);
> sqlCommand.Parameters["@department"].Value = department;
> sqlCommand.Parameters["@category"].Value = category;
> sqlCommand.Parameters["@item"].Value = item;
>
> dsDCI.Clear();
> if (database.ExecuteSqlCommand(sqlCommand, out dsDCI, ref messageText, ref statusCode))
> {
> dsDCI.Tables[0].TableName = "csrDCIInfo";
> if (0 == dsDCI.Tables["csrDCIInfo"].Rows.Count)
> {
> Logging.LogFormat(4, "No lesson type and category information found for: {0} skipping", dci);
> continue;
> }
>
> String lsn_cat = dsDCI.Tables["csrDCIInfo"].Rows[0].Field<String>("lsn_cat");
> Int32 lessontype = dsDCI.Tables["csrDCIInfo"].Rows[0].Field<Int32>("lessontype");
>
> EnumerableRowCollection<DataRow> checkQuery;
> if (checkType && checkCategory)
> checkQuery = from c in dtMax4SaleLimits.AsEnumerable()
> where ((2 == c.Field<Byte>("type") && lsn_cat == c.Field<String>("lsn_cat"))
> || (3 == c.Field<Byte>("type") && lessontype == c.Field<Int32>("lessontype")))
>
> select c;
> else
> {
> if (checkCategory)
> checkQuery = from c in dtMax4SaleLimits.AsEnumerable()
> where (2 == c.Field<Byte>("type") && lsn_cat == c.Field<String>("lsn_cat"))
> select c;
> else
> checkQuery = from c in dtMax4SaleLimits.AsEnumerable()
> where (3 == c.Field<Byte>("type") && lessontype == c.Field<Int32>("lessontype"))
> select c;
> }
>
> Int32 recordsCount = checkQuery.Count();
> if (0 == recordsCount)
> {
> Logging.LogFormat(4, "No Max4Sale limits found for: {0} skipping...",
> dci);
> continue;
> }
>
> //STEP 4 - loop through each start time for this DCI
> for (int i = 1; i <= 10; i++)
> {
> String startTime = dsDCI.Tables["csrDCIInfo"].Rows[0].Field<String>(String.Format("startime{0}", i));
> if (String.IsNullOrWhiteSpace(startTime)) // don't process empty time
> continue;
>
> DateTime dt, begin_time, ending_time;
>
> if (DateTime.TryParse(startTime, out dt))
> {
> begin_time = start_time.AddHours(dt.Hour).AddMinutes(dt.Minute);
> ending_time = begin_time.AddMinutes(dsDCI.Tables["csrDCIInfo"].Rows[0].Field<Int16>("span"));
> }
> else // bad format
> {
> Logging.LogFormat(2, "Bad time format for {0}: {1}", dci, startTime);
> continue;
> }
>
> EnumerableRowCollection<DataRow> query;
> if (checkType && checkCategory)
> query = from c in dtMax4SaleLimits.AsEnumerable()
> where ((2 == c.Field<Byte>("type") && lsn_cat == c.Field<String>("lsn_cat"))
> || (3 == c.Field<Byte>("type") && lessontype == c.Field<Int32>("lessontype")))
> && (c.Field<DateTime?>("start_time") < ending_time &&
> c.Field<DateTime?>("end_time") > begin_time)
> select c;
> else
> {
> if (checkCategory)
> query = from c in dtMax4SaleLimits.AsEnumerable()
> where (2 == c.Field<Byte>("type") && lsn_cat == c.Field<String>("lsn_cat"))
> && (c.Field<DateTime?>("start_time") < ending_time &&
> c.Field<DateTime?>("end_time") > begin_time)
> select c;
> else
> query = from c in dtMax4SaleLimits.AsEnumerable()
> where (3 == c.Field<Byte>("type") && lessontype == c.Field<Int32>("lessontype"))
> && (c.Field<DateTime?>("start_time") < ending_time &&
> c.Field<DateTime?>("end_time") > begin_time)
> select c;
> }
>
> recordsCount = query.Count();
> if (0 == recordsCount)
> {
> Logging.LogFormat(4, "No Max4Sale limits found for: {0} for: {1} thru {2} skipping...",
> dci, begin_time, ending_time);
> continue;
> }
> Logging.LogFormat(4, "{0} Restrictions found for: {1}", recordsCount, dci);
> if (Logging.iniVars.Verbosity > 4)
> {
> DataTable boundTable = query.CopyToDataTable<DataRow>();
> Logging.Log(5, database.GetFormattedReturnXml(boundTable));
> }
>
> //STEP 6 - loop through each restriction for this start time for this DCI and add up bookings
>
> Int64 limit = -999999999;
> Int64 qtyBooked = -999999999;
> Int64 qtyRemaining = 9999999999999;
>
> foreach (DataRow limits in query)
> {
> Byte type = limits.Field<Byte>("type");
>
> DateTime endTime; // minimum time of ending_time and limits.end_time
>
> if (((DateTime)ending_time).CompareTo((DateTime)limits.Field<DateTime?>("end_time")) < 0)
> endTime = (DateTime)ending_time;
> else
> endTime = (DateTime)limits.Field<DateTime?>("end_time");
>
> DateTime startingTime; // maximum of begin_time and limits.start_time
>
> if (((DateTime)begin_time).CompareTo((DateTime)limits.Field<DateTime?>("start_time")) > 0)
> startingTime = (DateTime)begin_time;
> else
> startingTime = (DateTime)limits.Field<DateTime?>("start_time");
>
> using (SqlCommand countCommand = new SqlCommand())
> {
> countCommand.CommandType = CommandType.Text;
> if (2 == type)
> {
> countCommand.CommandText = @"SELECT COUNT(*) as limitsCount FROM dbo.b_sched
>WHERE start_time <@end_time AND end_time > @start_time AND lsn_cat = @lsn_cat AND is_pod = 0 AND layer <>7;";
> countCommand.Parameters.Add("@lst_cat", SqlDbType.Char, 1).Value = limits.Field<String>("lsn_cat");
> }
> else
> {
> countCommand.CommandText = @"SELECT COUNT(*) as limitsCount FROM dbo.b_sched
>WHERE start_time <@end_time AND end_time > @start_time AND lessontype = @lessontype AND is_pod = 0 AND layer <>7;";
> countCommand.Parameters.Add("@lessontype", SqlDbType.Int).Value = limits.Field<Int32>("lessontype");
> }
> countCommand.Parameters.Add("@start_time", SqlDbType.DateTime).Value = startingTime;
> countCommand.Parameters.Add("@end_time", SqlDbType.DateTime).Value = endTime;
>
> Int32 lessonsCount;
> if (database.ExecuteSqlCommand(countCommand, ref messageText, ref statusCode))
> {
> lessonsCount = Convert.ToInt32(messageText);
> Logging.LogFormat(4, "{0} lessons matching {1} restriction found for {2} thru {3}",
> lessonsCount, ((2 == type) ? "Category" : "Lesson Type"), begin_time, ending_time);
>
> Int32 max_sale = limits.Field<Int32>("max_sale");
> if ((max_sale - lessonsCount) < qtyRemaining) // we have a new winner
> {
> limit = max_sale;
> qtyBooked = lessonsCount;
> qtyRemaining = limit - qtyBooked;
> }
> }
>
> DataRow newRow = dtReturn.NewRow();
> newRow["dci"] = dci;
> newRow["start_time"] = begin_time;
> newRow["end_time"] = ending_time;
> newRow["limi"] = limit;
> newRow["qty_booked"] = qtyBooked;
> newRow["qty_rem"] = qtyRemaining;
> dtReturn.Rows.Add(newRow);
> }
> }
> }
> }
>
Craig Berntson
MCSD, Microsoft .Net MVP, Grape City Community Influencer
