static int?DetermineMinMaxValue(IEnumerable <QueryComposerTermDTO> terms)
        {
            int?value = null;

            foreach (var term in terms)
            {
                AgeRangeValues v = AdapterHelpers.ParseAgeRangeValues(term);
                if (v.MaxAge.HasValue && (value == null || v.MaxAge.Value > value.Value))
                {
                    value = v.MaxAge;
                }
            }
            return(value);
        }
        public override IEnumerable <QueryComposerResponseQueryResultDTO> Execute(QueryComposerQueryDTO query, bool viewSQL)
        {
            //This is based on the xsl for QueryComposer in The ESPQueryBuilder project

            //TODO: need to see how multiple criteria are handled by the current xsl
            if (query.Where.Criteria.SelectMany(c => GetAllTermsWithinCriteria(c, ModelTermsFactory.ESPDiagnosisCodesID)).Any())
            {
                throw new NotImplementedException("The Term ESP Diagnosis Codes is currently not implemented and cannot be apart of the query.");
            }
            bool hasSQLTerm = query.Where.Criteria.SelectMany(c => GetAllTermsWithinCriteria(c, ModelTermsFactory.SqlDistributionID)).Any();

            if (hasSQLTerm)
            {
                if (query.Where.Criteria.Where(c => c.Terms.Any(d => d.Type != ModelTermsFactory.SqlDistributionID)).Any())
                {
                    throw new NotSupportedException("Another Term is Included with Sql Distribution and this is not Supported");
                }
                var    sqlCriteria = query.Where.Criteria.First().Terms.FirstOrDefault(t => t.Type == Lpp.QueryComposer.ModelTermsFactory.SqlDistributionID);
                string sql         = (sqlCriteria.GetStringValue("Sql"));

                List <DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO> columnProperties = new List <QueryComposerResponsePropertyDefinitionDTO>();
                var results = new QueryComposerResponseQueryResultDTO {
                    ID = query.Header.ID, QueryStart = DateTimeOffset.UtcNow
                };

                List <Dictionary <string, object> > queryResults = new List <Dictionary <string, object> >();
                using (var cmd = db.Database.Connection.CreateCommand())
                {
                    cmd.CommandText = sql;
                    using (var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo))
                    {
                        int       noNameIndex = 1;
                        DataTable schemaTable = reader.GetSchemaTable();
                        foreach (DataRow row in schemaTable.Rows)
                        {
                            foreach (DataColumn column in schemaTable.Columns)
                            {
                                if (column.ColumnName == "ColumnName")
                                {
                                    string columnName = row[column].ToString();
                                    if (string.IsNullOrWhiteSpace(columnName))
                                    {
                                        columnName = "NoColumnName" + noNameIndex;
                                        noNameIndex++;
                                    }
                                    columnProperties.Add(new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO {
                                        Name = columnName, Type = column.DataType.FullName
                                    });
                                }
                            }
                        }

                        while (reader.Read())
                        {
                            Dictionary <string, object> row = new Dictionary <string, object>();
                            //have to enumerate over the record using ordinal index since there may not be a column name in the reader
                            for (int i = 0; i < columnProperties.Count; i++)
                            {
                                row.Add(columnProperties[i].Name, reader.GetValue(i));
                            }
                            queryResults.Add(row);
                        }
                        reader.Close();
                    }
                }

                results.QueryEnd   = DateTimeOffset.UtcNow;
                results.Results    = new[] { queryResults };
                results.Properties = columnProperties;

                return(new[] { results });
            }
            else
            {
                bool hasConditionsTerm = query.Where.Criteria.SelectMany(c => GetAllTermsWithinCriteria(c, ModelTermsFactory.ConditionsID)).Any();
                bool hasICD9CodesTerm  = query.Where.Criteria.SelectMany(c => GetAllTermsWithinCriteria(c, ModelTermsFactory.ICD9DiagnosisCodes3digitID)).Any();

                var firstCriteria = query.Where.Criteria.First();
                QueryComposerTermDTO ageRangeTerm = GetAllTermsWithinCriteria(firstCriteria, ModelTermsFactory.AgeRangeID).FirstOrDefault();

                var diagnosisQuery = from d in db.Diagnosis select d;
                var diseasesQuery  = from d in db.Diseases select d;
                var encounterQuery = db.Demographics.Join(db.Encounters, o => o.PatID, i => i.PatID, (o, i) => new { o.PatID, i.EncounterID, i.AgeGroup5yr, i.AgeGroup10yr, i.A_Date, i.AgeAtEncYear });

                if (ageRangeTerm != null)
                {
                    var ageRange = AdapterHelpers.ParseAgeRangeValues(ageRangeTerm);
                    if (ageRange.MinAge.HasValue)
                    {
                        diagnosisQuery = diagnosisQuery.Where(q => q.AgeAtEncYear >= ageRange.MinAge.Value);
                        diseasesQuery  = diseasesQuery.Where(q => q.AgeAtDetectYear >= ageRange.MinAge.Value);
                        encounterQuery = encounterQuery.Where(q => q.AgeAtEncYear >= ageRange.MinAge.Value);
                    }
                    else if (ageRange.MaxAge.HasValue)
                    {
                        diagnosisQuery = diagnosisQuery.Where(q => q.AgeAtEncYear <= ageRange.MaxAge.Value);
                        diseasesQuery  = diseasesQuery.Where(q => q.AgeAtDetectYear <= ageRange.MaxAge.Value);
                        encounterQuery = encounterQuery.Where(q => q.AgeAtEncYear <= ageRange.MaxAge.Value);
                    }
                }

                //var observationPeriod = firstCriteria.Terms.FirstOrDefault(t => t.Type == ModelTermsFactory.ObservationPeriodID);
                var observationPeriod = GetAllTermsWithinCriteria(firstCriteria, ModelTermsFactory.ObservationPeriodID).FirstOrDefault();
                if (observationPeriod != null)
                {
                    var obp_range = AdapterHelpers.ParseDateRangeValues(observationPeriod);

                    if (obp_range.StartDate.HasValue)
                    {
                        int startDate = ConvertDateToNumberOfDays(obp_range.StartDate.Value);
                        diagnosisQuery = diagnosisQuery.Where(q => q.A_Date >= startDate);
                        diseasesQuery  = diseasesQuery.Where(q => q.Date >= startDate);
                        encounterQuery = encounterQuery.Where(q => q.A_Date >= startDate);
                    }
                    if (obp_range.EndDate.HasValue)
                    {
                        int endDate = ConvertDateToNumberOfDays(obp_range.EndDate.Value);
                        diagnosisQuery = diagnosisQuery.Where(q => q.A_Date <= endDate);
                        diseasesQuery  = diseasesQuery.Where(q => q.Date <= endDate);
                        encounterQuery = encounterQuery.Where(q => q.A_Date <= endDate);
                    }
                }


                //TODO: need to determine the precision of icd9 code from somewhere
                int icd9Precision = hasICD9CodesTerm ? 3 : 0;//0 == exclude, 3,4,5

                var entityQuery = from demographics in db.Demographics
                                  join race in db.UVT_Race on demographics.Race equals race.Code
                                  join sex in db.UVT_Sex on demographics.Sex equals sex.Code
                                  join ethnicity in db.UVT_Race_Ethnicity on demographics.Ethnicity equals ethnicity.Code
                                  select new ESPQueryResult
                {
                    PatientID         = demographics.PatID,
                    Patients          = null,
                    Sex               = sex.Text,
                    EthnicityCode     = demographics.Ethnicity,
                    Ethnicity         = ethnicity.Text,
                    Race              = race.Text,
                    Zip               = demographics.Zip5,
                    TobaccoUse        = demographics.Smoking,
                    Code              = null,
                    CodeDescription   = null,
                    Disease           = null,
                    ObservationPeriod = null,
                    Age_10yrGroup     = null,
                    Age_5yrGroup      = null,
                    Age_Detect        = null,
                    CenterID          = null
                };

                if (icd9Precision == 3)
                {
                    entityQuery = from o in entityQuery
                                  from diagnosis_inc in diagnosisQuery.Where(d => d.PatID == o.PatientID).DefaultIfEmpty()
                                  from dx in db.UVT_Dx3Digit.Where(x => x.Code == diagnosis_inc.DxCode3digit).DefaultIfEmpty()
                                  select new ESPQueryResult
                    {
                        PatientID     = o.PatientID,
                        Patients      = null,
                        Sex           = o.Sex,
                        EthnicityCode = o.EthnicityCode,
                        Ethnicity     = o.Ethnicity,
                        Race          = o.Race,
                        Zip           = o.Zip,
                        TobaccoUse    = o.TobaccoUse,

                        Code            = dx.Code,
                        CodeDescription = dx.Text,

                        Disease = o.Disease,
                        //NOTE: Value in db is # of days from 1960-01-01
                        ObservationPeriod = diagnosis_inc.A_Date,

                        Age_10yrGroup = diagnosis_inc.AgeGroup10yr,
                        Age_5yrGroup  = diagnosis_inc.AgeGroup5yr,
                        Age_Detect    = diagnosis_inc.AgeAtEncYear,

                        CenterID = diagnosis_inc.CenterID
                    };
                }
                else if (icd9Precision == 4)
                {
                    entityQuery = from o in entityQuery
                                  from diagnosis_inc in diagnosisQuery.Where(d => d.PatID == o.PatientID).DefaultIfEmpty()
                                  from dx in db.UVT_Dx4Digit.Where(x => x.Code == diagnosis_inc.DxCode4digitWithDec).DefaultIfEmpty()
                                  select new ESPQueryResult
                    {
                        PatientID     = o.PatientID,
                        Patients      = null,
                        Sex           = o.Sex,
                        EthnicityCode = o.EthnicityCode,
                        Ethnicity     = o.Ethnicity,
                        Race          = o.Race,
                        Zip           = o.Zip,
                        TobaccoUse    = o.TobaccoUse,

                        Code            = dx.Code,
                        CodeDescription = dx.Text,

                        Disease = o.Disease,
                        //NOTE: Value in db is # of days from 1960-01-01
                        ObservationPeriod = diagnosis_inc.A_Date,

                        Age_10yrGroup = diagnosis_inc.AgeGroup10yr,
                        Age_5yrGroup  = diagnosis_inc.AgeGroup5yr,
                        Age_Detect    = diagnosis_inc.AgeAtEncYear,

                        CenterID = diagnosis_inc.CenterID
                    };
                }
                else if (icd9Precision == 5)
                {
                    entityQuery = from o in entityQuery
                                  from diagnosis_inc in diagnosisQuery.Where(d => d.PatID == o.PatientID).DefaultIfEmpty()
                                  from dx in db.UVT_Dx5Digit.Where(x => x.Code == diagnosis_inc.DxCode5digitWithDec).DefaultIfEmpty()
                                  select new ESPQueryResult
                    {
                        PatientID     = o.PatientID,
                        Patients      = null,
                        Sex           = o.Sex,
                        EthnicityCode = o.EthnicityCode,
                        Ethnicity     = o.Ethnicity,
                        Race          = o.Race,
                        Zip           = o.Zip,
                        TobaccoUse    = o.TobaccoUse,

                        Code            = dx.Code,
                        CodeDescription = dx.Text,

                        Disease = o.Disease,
                        //NOTE: Value in db is # of days from 1960-01-01
                        ObservationPeriod = diagnosis_inc.A_Date,

                        Age_10yrGroup = diagnosis_inc.AgeGroup10yr,
                        Age_5yrGroup  = diagnosis_inc.AgeGroup5yr,
                        Age_Detect    = diagnosis_inc.AgeAtEncYear,

                        CenterID = diagnosis_inc.CenterID
                    };
                }

                if (hasConditionsTerm)
                {
                    entityQuery = from o in entityQuery
                                  from disease_inc in diseasesQuery.Where(d => d.PatID == o.PatientID).DefaultIfEmpty()
                                  select new ESPQueryResult
                    {
                        PatientID     = o.PatientID,
                        Patients      = null,
                        Sex           = o.Sex,
                        EthnicityCode = o.EthnicityCode,
                        Ethnicity     = o.Ethnicity,
                        Race          = o.Race,
                        Zip           = o.Zip,
                        TobaccoUse    = o.TobaccoUse,

                        Code            = o.Code,
                        CodeDescription = o.CodeDescription,

                        Disease = disease_inc.Condition,
                        //NOTE: Value in db is # of days from 1960-01-01
                        ObservationPeriod = disease_inc.Date,

                        Age_10yrGroup = disease_inc.AgeGroup10yr,
                        Age_5yrGroup  = disease_inc.AgeGroup5yr,
                        Age_Detect    = disease_inc.AgeAtDetectYear,

                        //center ID is not set in xsl when has visits
                        CenterID = o.CenterID
                    };
                }

                List <string> selectProperties = new List <string> {
                    "Patients"
                };                                                              //Patient aggregate is always included.

                bool primaryCriteria = true;
                foreach (var criteria in query.Where.Criteria)
                {
                    if (!primaryCriteria && criteria.Exclusion)
                    {
                        //exclusion is a not exists in the where clause
                        var subQuery = ApplyCriteria(criteria);
                        entityQuery = entityQuery.Where(q => !subQuery.Where(s => s.PatID == q.PatientID).Any());
                    }
                    else if (!primaryCriteria)
                    {
                        var subQuery = ApplyCriteria(criteria);
                        entityQuery = entityQuery.Where(q => subQuery.Where(s => s.PatID == q.PatientID).Any());
                    }
                    else
                    {
                        primaryCriteria = false;
                        //process the first criteria as the primary query

                        Expression <Func <ESPQueryResult, bool> > icdDiseasePredicate = null;

                        foreach (var tGroup in (criteria.Criteria.SelectMany(c => c.Terms).Concat(criteria.Terms)).GroupBy(k => k.Type))
                        {
                            var termID = tGroup.Key;

                            if (termID == Lpp.QueryComposer.ModelTermsFactory.AgeRangeID)
                            {
                                selectProperties.AddRange(new[] { "Age_5yrGroup", "Age_10yrGroup" });
                                //age range is incorporated into the diagnosis and disease joins
                            }
                            else if (termID == Lpp.QueryComposer.ModelTermsFactory.ConditionsID)
                            {
                                selectProperties.AddRange(new[] { "Disease" });

                                IEnumerable <string> diseases = tGroup.Select(g => TranslateCondition(g.GetStringValue("Condition"))).ToArray();
                                if (diseases.Any())
                                {
                                    if (icdDiseasePredicate == null)
                                    {
                                        icdDiseasePredicate = q => diseases.Contains(q.Disease);
                                    }
                                    else
                                    {
                                        icdDiseasePredicate = icdDiseasePredicate.Or(q => diseases.Contains(q.Disease));
                                    }
                                }
                            }
                            else if (termID == Lpp.QueryComposer.ModelTermsFactory.EthnicityID)
                            {
                                selectProperties.Add("Ethnicity");

                                var values = tGroup.SelectMany(t => t.GetValue("Ethnicities").Values <int>()).Distinct().Select(t => TranslateEthnicity(t)).ToArray();

                                Expression <Func <ESPQueryResult, bool> > ethnicityExpression = null;
                                foreach (int value in values)
                                {
                                    if (ethnicityExpression == null)
                                    {
                                        ethnicityExpression = eth => eth.EthnicityCode == value;
                                    }
                                    else
                                    {
                                        ethnicityExpression = ethnicityExpression.Or(q => q.EthnicityCode == value);
                                    }
                                }

                                if (ethnicityExpression != null)
                                {
                                    entityQuery = entityQuery.Where(ethnicityExpression);
                                }
                            }
                            else if (termID == Lpp.QueryComposer.ModelTermsFactory.SexID)
                            {
                                selectProperties.Add("Sex");

                                IEnumerable <string> sexs = tGroup.SelectMany(t => TranslateSex(t.GetStringValue("Sex"))).Distinct();
                                logger.Debug(sexs);
                                if (sexs.Any())
                                {
                                    entityQuery = entityQuery.Where(q => sexs.Contains(q.Sex.ToUpper()));
                                }
                            }
                            else if (termID == Lpp.QueryComposer.ModelTermsFactory.ICD9DiagnosisCodes3digitID)
                            {
                                selectProperties.AddRange(new[] { "Code", "CodeDescription" });

                                //TODO:going to have to be terms for 4 and 5 digit as well.
                                IEnumerable <string> codes = tGroup.SelectMany(t => AdapterHelpers.ParseCodeTermValues(t)).Select(t => t.Trim()).Distinct();
                                if (codes.Any())
                                {
                                    Expression <Func <ESPQueryResult, bool> > icd9Expression = null;
                                    foreach (string code in codes)
                                    {
                                        if (icd9Expression == null)
                                        {
                                            icd9Expression = q => q.Code.StartsWith(code.ToUpper());
                                        }
                                        else
                                        {
                                            icd9Expression = icd9Expression.Or(q => q.Code.StartsWith(code.ToUpper()));
                                        }
                                    }

                                    if (icdDiseasePredicate == null)
                                    {
                                        icdDiseasePredicate = icd9Expression;
                                    }
                                    else
                                    {
                                        icdDiseasePredicate = icdDiseasePredicate.Or(icd9Expression);
                                    }
                                }
                            }
                            else if (termID == Lpp.QueryComposer.ModelTermsFactory.ObservationPeriodID)
                            {
                                if (hasConditionsTerm || hasICD9CodesTerm)
                                {
                                    selectProperties.AddRange(new[] { "ObservationPeriod" });
                                }
                                //observation period is incorporated into the diagnosis and disease joins
                            }
                            else if (termID == Lpp.QueryComposer.ModelTermsFactory.VisitsID)
                            {
                                try
                                {
                                    IQueryable <EncountersGroupingResult> encountersAppliedQuery = encounterQuery.GroupBy(v => new { v.PatID, v.AgeGroup5yr, v.AgeGroup10yr }).Select(v => new EncountersGroupingResult {
                                        PatID = v.Key.PatID, AgeGroup5yr = v.Key.AgeGroup5yr, AgeGroup10yr = v.Key.AgeGroup10yr, Count = v.Count()
                                    });

                                    int visits = tGroup.Where(t => t.GetValue("Visits") != null).Select(t => Convert.ToInt32(t.GetStringValue("Visits"))).Min();

                                    if (!hasConditionsTerm && !hasICD9CodesTerm)
                                    {
                                        entityQuery = entityQuery.Join(encountersAppliedQuery.Where(q => q.Count >= visits), o => o.PatientID, i => i.PatID, (o, i) => new ESPQueryResult
                                        {
                                            PatientID         = o.PatientID,
                                            Patients          = null,
                                            Sex               = o.Sex,
                                            Ethnicity         = o.Ethnicity,
                                            Race              = o.Race,
                                            Zip               = o.Zip,
                                            TobaccoUse        = o.TobaccoUse,
                                            Code              = o.Code,
                                            CodeDescription   = o.CodeDescription,
                                            Disease           = o.Disease,
                                            ObservationPeriod = o.ObservationPeriod,
                                            Age_10yrGroup     = i.AgeGroup10yr,
                                            Age_5yrGroup      = i.AgeGroup5yr,
                                            Age_Detect        = o.Age_Detect,
                                            CenterID          = o.CenterID
                                        });
                                    }
                                    else
                                    {
                                        entityQuery = entityQuery.Join(encountersAppliedQuery.Where(q => q.Count >= visits), o => o.PatientID, i => i.PatID, (o, i) => o);
                                    }
                                }
                                catch { }
                            }
                            else if (termID == Lpp.QueryComposer.ModelTermsFactory.ZipCodeID)
                            {
                                selectProperties.Add("Zip");

                                var zipCodes = tGroup.SelectMany(t => AdapterHelpers.ParseCodeTermValues(t)).Select(t => t.Trim()).Distinct();

                                if (zipCodes.Any())
                                {
                                    entityQuery = entityQuery.Where(q => zipCodes.Contains(q.Code));
                                }
                            }
                            else
                            {
                                logger.Debug("Term specified but not implemented in criteria: " + termID);
                            }
                        }

                        if (icdDiseasePredicate != null)
                        {
                            entityQuery = entityQuery.Where(icdDiseasePredicate);
                        }
                    }
                }

                entityQuery = entityQuery.GroupBy(k => new
                {
                    k.Sex,
                    k.EthnicityCode,
                    k.Ethnicity,
                    k.Race,
                    k.Zip,
                    k.TobaccoUse,
                    k.Code,
                    k.CodeDescription,
                    k.Disease,
                    k.ObservationPeriod,
                    k.Age_10yrGroup,
                    k.Age_5yrGroup,
                    k.Age_Detect,
                    k.CenterID
                })
                              .Select(k => new ESPQueryResult
                {
                    PatientID         = null,
                    Patients          = k.Count(),
                    Sex               = k.Key.Sex,
                    EthnicityCode     = k.Key.EthnicityCode,
                    Ethnicity         = k.Key.Ethnicity,
                    Race              = k.Key.Race,
                    Zip               = k.Key.Zip,
                    TobaccoUse        = k.Key.TobaccoUse,
                    Code              = k.Key.Code,
                    CodeDescription   = k.Key.CodeDescription,
                    Disease           = k.Key.Disease,
                    ObservationPeriod = k.Key.ObservationPeriod,
                    Age_10yrGroup     = k.Key.Age_10yrGroup,
                    Age_5yrGroup      = k.Key.Age_5yrGroup,
                    Age_Detect        = k.Key.Age_Detect,
                    CenterID          = k.Key.CenterID
                });



                //TODO: generate the select fields and apply to the query
                //NOTE: dynamically selecting into a result class didn't affect the generated query, skipping and filtering when building response.
                //var select = query.Select(Utility.MapToClass<ESPQueryResult, ESPAggregatedResult>(selectProperties));

                //TODO: if all projection are stratification and options specified? wrap with additional query


                //TODO: apply the ordering to the select projection

                /*
                 * Here's a possiblity using reflection...
                 *  var param = "Address";
                 *  var pi = typeof(Student).GetProperty(param);
                 *  var orderByAddress = items.OrderBy(x => pi.GetValue(x, null));
                 *
                 * see that will use expression:
                 * http://www.singingeels.com/Blogs/Nullable/2008/03/26/Dynamic_LINQ_OrderBy_using_String_Names.aspx
                 * */

                var response = new QueryComposerResponseQueryResultDTO {
                    ID = query.Header.ID, QueryStart = DateTimeOffset.UtcNow
                };
                List <Dictionary <string, object> > results = new List <Dictionary <string, object> >();
                if (!viewSQL)
                {
                    logger.Debug(entityQuery.ToString());

                    foreach (var item in entityQuery)
                    {
                        Dictionary <string, object> row = new Dictionary <string, object>();
                        Type itemType = item.GetType();
                        foreach (var propInfo in itemType.GetProperties())
                        {
                            if (selectProperties.Contains(propInfo.Name))
                            {
                                object value = propInfo.GetValue(item, null);
                                if (propInfo.Name == "ObservationPeriod")
                                {
                                    //The value is currently the number of days from Jan 1, 1960, convert to date.
                                    value = new DateTime(1960, 1, 1, 0, 0, 0, DateTimeKind.Unspecified).AddDays(Convert.ToInt32(value ?? 0));
                                }
                                row.Add(propInfo.Name, value);
                            }
                        }
                        results.Add(row);
                    }
                }
                else
                {
                    Dictionary <string, object> row = new Dictionary <string, object>();
                    row.Add("SQL", entityQuery.ToString());
                    results.Add(row);
                }

                logger.Debug("Number of results found:" + results.Count);

                //QueryComposerResponseDTO response = new QueryComposerResponseDTO
                //{
                //    ResponseDateTime = DateTime.UtcNow,
                //    Results = new[] { results }
                //};

                //if (query.ID.HasValue)
                //    response.RequestID = query.ID.Value;

                response.QueryEnd = DateTimeOffset.UtcNow;
                response.Results  = new[] { results };

                return(new[] { response });
            }
        }