4
\$\begingroup\$

In response to my previous question, I've refactored my class quite a bit. I've truncated my class a bit, because I have several methods that are very similar, with the exception of the query for the database. The class itself is at the 930 line number.

So, a little background. I'm not only the sole programmer on this project, but also in the company. And this is the first time I've ever attempted anything like this (only out of school 3 years). The purpose of the program is to query a local database, and generate a daily report. I've broken it up into two projects: WPF, and Report class library (what you see below). I already knew I didn't want the UI project to actually do the calculations. So I broke it off into the class library. I also knew I didn't want to just query the whole database and store ALL it's data in memory. So, I have each calculation calling a query method. I had looked into stored procedures, but this is a SQLite database, and stored procedures aren't supported.

Now, with all that in mind, I'm not really sure how best to improve this any further. So here I am, asking you guys: Is this too over the top, or is it fine?

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using MySql.Data.MySqlClient;
using NLog;

namespace DailyReport
{
    public class Report
    {
        #region Global Variables

        #region Enums

        public enum TimePeriod
        {
            Daily, Total, None
        }

        #endregion Enums

        private static readonly Logger Logger = LogManager.GetLogger("ReportLogger");
        public string ConnectionStringFile { get; private set; }

        #region Query Parameters

        public string StartTime { get; set; }
        public string EndTime { get; set; }
        public string Date { get; set; }

        #endregion Query Parameters

        #endregion Global Variables

        #region Constructors

        public Report(string stringFileName, DateTime date = default(DateTime), string start = "0", string end = "0")
        {
            Logger.Info("Creating a new Report...");

            StartTime = start;
            EndTime = end;
            Date = date.ToString("yyyy-MM-dd");

            SetStartEndTimes();

            ConnectionStringFile = string.Format(@"Data Source=C:\path\{0}.hdd;Version=3;New=False;Compress=True;", stringFileName);

            Logger.Info("Report Created");
        }

        public Report()
        {

        }

        #endregion Constructors

        #region Calculations

        #region Hours

        /// <summary>
        /// Drilling Hours
        /// </summary>
        /// <param name="period">Enum: Period of time</param>
        /// <param name="timeFrame">Bool: If the calculation is for a specific time frame</param>
        /// <returns>Double: Drilling Hours</returns>
        public double DrillingHours(TimePeriod period = TimePeriod.Daily, bool timeFrame = false)
        {
            Logger.Info("Calculating Drilling Hours...");

            const string query = "SELECT timestamp FROM myDatabase WHERE measured_dist = bit_loc AND rop > 0";
            var tempList = GetDatabaseResults(timeFrame, query, period);
            var drillingHours = CalculateHours(tempList);

            Logger.Info(period + " Drilling Hours calculated.");

            return drillingHours;
        }

        /// <summary>
        /// Circulating Hours
        /// </summary>
        /// <param name="period">Enum: Period of time</param>
        /// <param name="timeFrame">Bool: If the calculation is for a specific time frame</param>
        /// <returns>Double: Circulating Hours</returns>
        public double CirculatingHours(TimePeriod period = TimePeriod.Daily, bool timeFrame = false)
        {
            Logger.Info("Calculating Circulating Hours...");

            const string query = "SELECT timestamp FROM myDatabase WHERE pump_press > 100";
            var tempList = GetDatabaseResults(timeFrame, query, period);
            var circulatingHours = CalculateHours(tempList);

            Logger.Info(period + " Circulating Hours calculated.");

            return circulatingHours;
        }

        /// <summary>
        /// NonDrillingActivity Hours
        /// </summary>
        /// <param name="period">Enum: Period of time</param>
        /// <param name="timeFrame">Bool: If the calculation is for a specific time frame</param>
        /// <returns>Double: NonDrillingActivity Hours</returns>
        public double NonDrillingActivityHours(TimePeriod period = TimePeriod.Daily, bool timeFrame = false)
        {
            Logger.Info("Calculating NonDrillingActivity Hours...");

            const string query = "SELECT timestamp FROM myDatabase WHERE pump_press <= 100 AND rop = 0";
            var tempList = GetDatabaseResults(timeFrame, query, period);
            var nonDrillingActivityHours = CalculateHours(tempList);

            Logger.Info(period + " NonDrillingActivity Hours calculated.");

            return nonDrillingActivityHours;
        }

        #endregion Hours

        /// <summary>
        /// Footage
        /// </summary>
        /// <param name="period">Enum: Period of time</param>
        /// <param name="timeFrame">Bool: If the calculation is for a specific time frame</param>
        /// <returns>Double: Footage</returns>
        public double Footage(TimePeriod period = TimePeriod.Daily, bool timeFrame = false)
        {
            Logger.Info("Calculating Footage...");

            var query = period == TimePeriod.Daily ? "SELECT measured_dist FROM myDatabase WHERE date = @Date" : "SELECT measured_dist FROM myDatabase";
            var tempList = GetDatabaseResults(timeFrame, query);
            var dailyDepths = tempList.Select(Convert.ToDouble).ToList();

            dailyDepths.Sort();

            var footage = Math.Round((dailyDepths.Last() - dailyDepths.First()), 2, MidpointRounding.AwayFromZero);

            Logger.Info(period + " Footage calculated.");

            return footage;
        }

        /// <summary>
        /// ROP Average
        /// </summary>
        /// <param name="footage">Double: Footage drilled for the day</param>
        /// <param name="drillingHours">Double: Drilling hours for the day</param>
        /// <param name="period">Enum: Period of time</param>
        /// <returns>Double: ROP Average</returns>
        public double RopAVG(double footage, double drillingHours, TimePeriod period = TimePeriod.Daily)
        {
            Logger.Info("Calculating ROP Avg...");

            double ropAvg = 0;

            if(footage > 0 && drillingHours > 0)
            {
                ropAvg = Math.Round(footage / (drillingHours*60), 2, MidpointRounding.AwayFromZero);
            }

            Logger.Info(period + " ROP Avg calculated.");

            return ropAvg;
        }

        /// <summary>
        /// Pump Strokes
        /// </summary>
        /// <param name="pump">Int: Pump being evaluated</param>
        /// <param name="drillingHours">Double: Drilling hours for the day</param>
        /// <param name="period">Enum: Period of time</param>
        /// <param name="timeFrame">Bool: If the calculation is for a specific time frame</param>
        /// <returns>Double: Pump Strokes</returns>
        public double PumpStrokes(int pump, double drillingHours, TimePeriod period = TimePeriod.Daily, bool timeFrame = false)
        {
            Logger.Info("Calculating Pump strokes...");

            string query;

            switch(pump)
            {
                case 1:
                    query = period == TimePeriod.Daily
                                ? "SELECT pump_1_strokes_pm FROM myDatabase WHERE date = @Date"
                                : "SELECT pump_1_strokes_pm FROM myDatabase";
                    break;
                case 2:
                    query = period == TimePeriod.Daily
                                ? "SELECT pump_2_strokes_pm FROM myDatabase WHERE date = @Date"
                                : "SELECT pump_2_strokes_pm FROM myDatabase";
                    break;
                default:
                    throw new NoDataException("There is no pump number " + pump);
            }

            IEnumerable<string> tempList;

            switch (period)
            {
                case TimePeriod.Daily:
                    tempList = GetDatabaseResults(timeFrame, query);
                    break;
                case TimePeriod.Total:
                    tempList = GetDatabaseResults(timeFrame, query, period);
                    break;
                default:
                    throw new NoDataException("This should never happen.");
            }

            var pumpStrokes = tempList.Select(Convert.ToDouble).ToList();

            var totalStrokes = Math.Round((pumpStrokes.Sum() / pumpStrokes.Count) * (drillingHours * 60), 2,
                                          MidpointRounding.AwayFromZero);

            Logger.Info(period + pump + " Pump strokes calculated.");

            return totalStrokes;
        }

        /// <summary>
        /// PSI Max
        /// </summary>
        /// <param name="period">Enum: Period of time</param>
        /// <param name="timeFrame">Bool: If the calculation is for a specific time frame</param>
        /// <returns>Double: PSI Max</returns>
        public double PsiMAX(TimePeriod period = TimePeriod.Daily, bool timeFrame = false)
        {
            Logger.Info("Calculating Max PSI...");

            var query = period == TimePeriod.Daily ? "SELECT pump_press FROM myDatabase WHERE date = @Date" : "SELECT pump_press FROM myDatabase";
            var tempList = GetDatabaseResults(timeFrame, query);
            var psiList = tempList.Select(Convert.ToDouble).ToList();
            var psiMax = Math.Round(psiList.Max(), 2, MidpointRounding.AwayFromZero);

            Logger.Info(period + " Max PSI calculated.");

            return psiMax;
        }

        /// <summary>
        /// Torque Avg
        /// </summary>
        /// <param name="drillingHours">Double: Drilling hours for the day</param>
        /// <param name="period">Enum: Period of time</param>
        /// <param name="timeFrame">Bool: If the calculation is for a specific time frame</param>
        /// <returns>Double: Torque Avg</returns>
        public double TorqueAVG(double drillingHours, TimePeriod period = TimePeriod.Daily, bool timeFrame = false)
        {
            Logger.Info("Calculating Avg Torque...");

            var query = period == TimePeriod.Daily ? "SELECT torque FROM myDatabase WHERE date = @Date" : "SELECT torque FROM myDatabase";
            var tempList = GetDatabaseResults(timeFrame, query);
            var torqueList = tempList.Select(Convert.ToDouble).ToList();

            if(torqueList.Sum() > 0 && drillingHours > 0)
            {
                var torqueAvg = Math.Round((torqueList.Sum() / torqueList.Count), 2, MidpointRounding.AwayFromZero);

                Logger.Info(period + " Avg Torque calculated.");

                return torqueAvg;
            }

            Logger.Info("Torque Avg = 0 because torque sum = " + torqueList.Sum() + " or DrillingHours = " + drillingHours);

            return 0;
        }

        /// <summary>
        /// SPM Avg
        /// </summary>
        /// <param name="pumpStrokes1"></param>
        /// <param name="pumpStrokes2"></param>
        /// <param name="drillingHours">Double: Drilling hours for the day</param>
        /// <param name="period">Enum: Period of time</param>
        /// <param name="timeFrame">Bool: If the calculation is for a specific time frame</param>
        /// <returns>Double: SPM Avg</returns>
        public double SpmAVG(double pumpStrokes1, double pumpStrokes2, double drillingHours, TimePeriod period = TimePeriod.Daily, bool timeFrame = false)
        {
            Logger.Info("Calculating Avg SPM...");

            var totalPumpStrokes = pumpStrokes1 + pumpStrokes2;

            if (totalPumpStrokes > 0 && drillingHours > 0)
            {
                var spmAvg = Math.Round(totalPumpStrokes/(drillingHours*60), 2, MidpointRounding.AwayFromZero);

                Logger.Info(period + " Avg SPM calculated.");

                return spmAvg;
            }
            Logger.Info("SPM Avg = 0 because spm sum = " + totalPumpStrokes + " or DrillingHours = " + drillingHours);

            return 0;
        }

        /// <summary>
        /// PSI Off Bottom
        /// </summary>
        /// <param name="period">Enum: Period of time</param>
        /// <param name="timeFrame">Bool: If the calculation is for a specific time frame</param>
        /// <returns>Double: PSI Off Bottom</returns>
        public double PsiOffBottom(TimePeriod period = TimePeriod.Daily, bool timeFrame = false)
        {
            Logger.Info("Calculating PSI off bottom...");

            const string query = "SELECT pump_press FROM myDatabase WHERE measured_dist != bit_loc"; 

            var tempList = GetDatabaseResults(timeFrame, query, period);
            var psiList = tempList.Select(Convert.ToDouble).ToList();
            var psiOffBottom = Math.Round(psiList.Max(), 2, MidpointRounding.AwayFromZero);

            Logger.Info(period + " PSI off bottom calculated.");

            return psiOffBottom;
        }

        #endregion Calculations

        #region Everything Else

        private static double CalculateHours(IEnumerable<string> projectHours)
        {
            return Convert.ToDouble(Math.Round(TimeCalculations(ConvertStringListToDateTimeList(projectHours)).TotalHours, 2, MidpointRounding.AwayFromZero));
        }

        private IEnumerable<string> GetDatabaseResults(bool timeFrame, string query, TimePeriod period = TimePeriod.None)
        {
            var daily = period == TimePeriod.Daily;

            query = BuildQuery(query, daily, timeFrame);

            var tempList = ExecuteQuery(query);

            if (tempList.Any())
            {
                return tempList;
            }

            throw new NoDataException("There was no data for the selected time frame. Please select another.");
        }

        private static TimeSpan TimeCalculations(IList<DateTime> timeStamps)
        {
            var interval = new TimeSpan(0, 0, 10);
            var totalTime = new TimeSpan();

            for (var j = 0; j < timeStamps.Count - 1; j++)
            {
                if (timeStamps[j + 1].Subtract(timeStamps[j]) > interval) continue;
                var timeDifference = timeStamps[j + 1].Subtract(timeStamps[j]);
                totalTime = totalTime.Add(timeDifference);
            }

            return totalTime;
        }

        private List<string> ExecuteQuery(string query)
        {
            Logger.Info("Executing query...");

            var tempList = new List<string>();

            try
            {
                using (var connection = new SQLiteConnection(ConnectionStringFile))
                {
                    using (var command = new SQLiteCommand(query, connection))
                    {
                        connection.Open();

                        command.Parameters.Add(new SQLiteParameter("@Date", Date));
                        command.Parameters.Add(new SQLiteParameter("@StartTime", StartTime));
                        command.Parameters.Add(new SQLiteParameter("@EndTime", EndTime));

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                for (var i = 0; i < reader.FieldCount; i++)
                                {
                                    tempList.Add(reader.GetValue(i).ToString());
                                } // for
                            } // while
                        } // using reader
                    } // using command
                } // using connection
            } // try
            catch (Exception ex)
            {
                Logger.Error(ex.Message);
            }

            Logger.Info("Query complete");

            return tempList;
        }

        private static string BuildQuery(string query, bool daily, bool timeFrame)
        {
            Logger.Info("Building query...");

            const string dailyAdd = " AND date = @Date";
            const string timeFrameAdd = " AND timestamp BETWEEN @StartTime AND @EndTime";

            // Stack Overflow would HATE this so much
            if (daily)
            {
                query += dailyAdd;
            }

            if (timeFrame)
            {
                query += timeFrameAdd;
            }

            Logger.Info("Query built");

            return query;
        }

        // The list of strings should be DateTimes so that we can calculate hours
        private static List<DateTime> ConvertStringListToDateTimeList(IEnumerable<string> stringList)
        {
            var dateTimeList = stringList.Select(Convert.ToDateTime).ToList();

            return dateTimeList;
        }

        /// <summary>
        /// Get list of unique dates.  These are dates that have information in the database.
        /// </summary>
        /// <returns>List of unique dates</returns>
        public List<string> GetUniquesDates()
        {
            var dates = new List<string>();

            const string query = "SELECT date FROM myDatabase";

            try
            {
                using (var connection = new SQLiteConnection(ConnectionStringFile))
                {
                    using (var command = new SQLiteCommand(query, connection))
                    {
                        connection.Open();

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                for (var i = 0; i < reader.FieldCount; i++)
                                {
                                    dates.Add(reader.GetValue(i).ToString());
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex.Message);
            }

            dates.Sort();

            return dates.Distinct().ToList();
        }

        /// <summary>
        /// Sets the global variables for StartTime and EndTime
        /// </summary>
        /// StartTime and EndTime won't change at all throughout the live of the Report object
        private void SetStartEndTimes()
        {
            // Because the timestamp retrieved will be a string, and will be in "yyyy-MM-dd HH:mm:ss" format
            if (StartTime.Equals("0")) return;
            StartTime = Date + string.Format(StartTime, "HH:mm:ss");
            EndTime = Date + string.Format(EndTime, "HH:mm:ss");
        }

        #endregion Everything Else
    }
}
\$\endgroup\$
3
  • 1
    \$\begingroup\$ Am I the only one left who hates var? \$\endgroup\$
    – banging
    Commented Apr 23, 2013 at 17:05
  • 6
    \$\begingroup\$ @banging - Yes? \$\endgroup\$
    – Bobson
    Commented Apr 23, 2013 at 17:13
  • \$\begingroup\$ Does it make sense to call Report()? If not I would remove that and consider making the End, Start and Date properties private set. \$\endgroup\$
    – dreza
    Commented Apr 24, 2013 at 19:54

1 Answer 1

3
\$\begingroup\$

It looks like some of the methods are simply calculating the max or average. You might be able to move these types of calculations into the queries by using the corresponding aggregate functions. For example:

SELECT MAX(pump_press) FROM myDatabase
SELECT AVG(torque) FROM myDatabase

I believe the Footage calculation could even be done using:

SELECT MAX(measured_dist) - MIN(measured_dist) FROM myDatabase

I would also discourage the use of variables with names like tempList.

\$\endgroup\$
1
  • \$\begingroup\$ That's a good call. This is the first project where I've worked extensively with databases, so I'm still learning the ins and outs. \$\endgroup\$
    – PiousVenom
    Commented Apr 23, 2013 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.