Speech2TextPrototype/Repositories/LookupTableRepository.cs
/// <summary>
/// Construct the query by adding the known filters
/// </summary>
/// <returns>query</returns>
Construct the query by adding the known filters
Construct the query by adding the known filters
private string ConstructQuery()
        {
            string query = "";
            string measures = "";
            string wheres = "";
            bool listDatesExist = listDates.Any();
            if (listMeasures.Any())
            {
                measures = listMeasures.Aggregate((i, j) => i + ", " + j);
                if (!String.IsNullOrEmpty(aggregateFunction))
                {
                    measures = measures.Insert(0, aggregateFunction.ToUpper() + "(");
                    measures += ")";
                }
            }
            if (!String.IsNullOrEmpty(timeType))
            {
                if (!listDatesExist)
                {
                    if (timeType == "month" || timeType == "day")
                        wheres += timeType + "(PERIOD_START) between (" + timeType + "(getdate()) - " + timeNum + ") and getdate() and YEAR(PERIOD_START) = YEAR(getdate())";  
                    if (timeType == "year")
                        wheres += timeType + "(PERIOD_START) between (" + timeType + "(getdate()) - " + timeNum + ") and getdate()";
                }
                else
                {
                    var year = listDates[0].Split('=')[1].Replace(" ", "");
                    if (timeType == "month")
                    {
                        var monthDiff = 12 - Int32.Parse(timeNum);
                        string month = monthDiff.ToString();
                        if (monthDiff >= 1 && monthDiff <= 9)
                        {
                            month = "0" + month;
                        }
                        wheres += "CONVERT(VARCHAR(10), PERIOD_START) between '" + year + "-" + month + "-01' and '" + year + "-12-31'";
                    }
                    else if (timeType == "day")
                    {
                        var dayDiff = 31 - Int32.Parse(timeNum);
                        string day = dayDiff.ToString();
                        if (dayDiff >= 1 && dayDiff <= 9)
                        {
                            day = "0" + day;
                        }
                        wheres += "CONVERT(VARCHAR(10), PERIOD_START) between '" + year + "-12-" + dayDiff + "' and '" + year + "-12-31'";
                    }
                }
            }
            else if (listDatesExist && isBetween)
            {
                wheres += listDates.Aggregate((i, j) => i.Split('=')[0] + "Between" + i.Split('=')[1] + " AND" + j.Split('=')[1]);
            }
            else if (listDatesExist)
            {
                if (!String.IsNullOrEmpty(wheres))
                    wheres += " AND ";
                wheres += listDates.Aggregate((i, j) => "(" + i + " OR " + j + ")");
            }
            if (listWhereStatements.Any())
            {
                if (!String.IsNullOrEmpty(wheres))
                    wheres += " AND ";
                wheres += listWhereStatements.Aggregate((i, j) => i + " AND " + j);
            }
            if (isTop)
                query = $"SELECT TOP {topNum} * FROM TDATA";
            else
                query = "SELECT * FROM TDATA";
            if (!String.IsNullOrEmpty(wheres))
                query += " WHERE " + wheres;
            return query;
        }
private string ConstructQuery ( ) { string query = " " ; string measures = " " ; string wheres = " " ; bool listDatesExist = listDates . Any ( ) ; if ( listMeasures . Any ( ) ) { measures = listMeasures . Aggregate ( ( i , j ) => i + " " + j ) ; if ( ! String . IsNullOrEmpty ( aggregateFunction ) ) { measures = measures . Insert ( 0 , aggregateFunction . ToUpper ( ) + " " ) ; measures += " " ; } } if ( ! String . IsNullOrEmpty ( timeType ) ) { if ( ! listDatesExist ) { if ( timeType == " " || timeType == " " ) wheres += timeType + " " + timeType + " " + timeNum + " " ; if ( timeType == " " ) wheres += timeType + " " + timeType + " " + timeNum + " " ; } else { var year = listDates [ 0 ] . Split ( ' ' ) [ 1 ] . Replace ( " " , " " ) ; if ( timeType == " " ) { var monthDiff = 12 - Int32 . Parse ( timeNum ) ; string month = monthDiff . ToString ( ) ; if ( monthDiff >= 1 && monthDiff <= 9 ) { month = " " + month ; } wheres += " " + year + " " + month + " " + year + " " ; } else if ( timeType == " " ) { var dayDiff = 31 - Int32 . Parse ( timeNum ) ; string day = dayDiff . ToString ( ) ; if ( dayDiff >= 1 && dayDiff <= 9 ) { day = " " + day ; } wheres += " " + year + " " + dayDiff + " " + year + " " ; } } } else if ( listDatesExist && isBetween ) { wheres += listDates . Aggregate ( ( i , j ) => i . Split ( ' ' ) [ 0 ] + " " + i . Split ( ' ' ) [ 1 ] + " " + j . Split ( ' ' ) [ 1 ] ) ; } else if ( listDatesExist ) { if ( ! String . IsNullOrEmpty ( wheres ) ) wheres += " " ; wheres += listDates . Aggregate ( ( i , j ) => " " + i + " " + j + " " ) ; } if ( listWhereStatements . Any ( ) ) { if ( ! String . IsNullOrEmpty ( wheres ) ) wheres += " " ; wheres += listWhereStatements . Aggregate ( ( i , j ) => i + " " + j ) ; } if ( isTop ) query = $" SELECT TOP  { topNum }  * FROM TDATA " ; else query = " " ; if ( ! String . IsNullOrEmpty ( wheres ) ) query += " " + wheres ; return query ; }
Construct the query by adding the known filters
Construct the query by adding the known filters
// Add aggregate function // If past,last keyword is detected // Display last months or days of current year // Display last x years // Display last months or days of specific year, e.g last 2 months of 2016 // If dates are BETWEEN a certain range // Add the rest of the "where" Statements // Construct Query
None