Example #1
0
        /// <summary>
        /// Returns all response data for an assessment, in ValuePairs
        ///
        /// Uses direct SQL calls instead of EntityFramework to speed it up.
        /// </summary>
        /// <param name="formResultId">The form result ID to get data from</param>
        /// <returns>A list of value pairs. identifier = the item variable identifier, value = rspValue from the response variable</returns>
        public static List <ValuePair> GetDataByFormResultId(int formResultId)
        {
            List <ValuePair> result = new List <ValuePair>();

            using (formsEntities tempDB = DataContext.GetDbContext())
            {
                IQueryable qry = (from ir in tempDB.def_ItemResults
                                  where (ir.formResultId == formResultId)
                                  join rv in tempDB.def_ResponseVariables on ir.itemResultId equals rv.itemResultId
                                  join iv in tempDB.def_ItemVariables on rv.itemVariableId equals iv.itemVariableId
                                  select new { iv.identifier, rv.rspValue });
                foreach (dynamic elem in qry)
                {
                    result.Add(new ValuePair(elem.identifier, elem.rspValue));
                }
                //IQueryable qry = (from rv in tempDB.def_ResponseVariables
                //                  join ir in tempDB.def_ItemResults on rv.itemResultId equals ir.itemResultId
                //                  join iv in tempDB.def_ItemVariables on rv.itemVariableId equals iv.itemVariableId
                //                  where (ir.formResultId == formResultId)
                //                  select new { iv.identifier, rv.rspValue });
                //foreach (dynamic elem in qry)
                //{
                //    result.Add(new ValuePair(elem.identifier, elem.rspValue));
                //}
            }

            return(result);
        }
Example #2
0
        public ContentResult UploadFormResultsJSON()
        {
            //use session variables to pick a FormResults object from the database
            string formId = Request["formId"] as string;

            Debug.WriteLine("* * *  GetFormResultsJSON formId: " + formId);
            Session["formId"] = formId;
            int iFormId = Convert.ToInt32(formId);

            string formResultId = Request["formResultId"] as string;

            Debug.WriteLine("* * *  GetFormResultsJSON formResultId: " + formId);
            Session["formResultId"] = formResultId;
            int frmRsltId = Convert.ToInt32(formResultId);

            formsEntities db = DataContext.GetDbContext();

            db.Configuration.LazyLoadingEnabled = false;

            def_FormResults frmRslt = db.def_FormResults.Where(fr => fr.formId == iFormId).Include(fr => fr.def_ItemResults.Select(r => r.def_ResponseVariables)).First(fr => fr.formResultId == frmRsltId);

            string jsonString = fastJSON.JSON.ToJSON(frmRslt);

            //write json string to file, then stream it out
            //DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(FormResultJSON));
            string       outpath      = ControllerContext.HttpContext.Server.MapPath("../Content/formResults_" + System.DateTime.Now.Ticks + ".json");
            FileStream   stream       = new FileStream(outpath, FileMode.CreateNew);
            StreamWriter streamWriter = new StreamWriter(stream);

            streamWriter.Write(jsonString);
            streamWriter.Close();
            stream.Close();

            //used localhost for testing, should be changed to master server url
            // string url = "http://localhost:50209";
            string url = ConfigurationManager.AppSettings["SISOnlineURL"];

            //upload to master server
            string result;

            using (var client = new WebClient())
            {
                client.Headers[HttpRequestHeader.ContentType] = "application/x-www-form-urlencoded";
                var data = "json=" + System.IO.File.ReadAllText(outpath, Encoding.UTF8);
                result = client.UploadString(url + "/Defws/ImportFormResultJSON", "POST", data);
            }

            AccessLogging.InsertAccessLogRecord(formsRepo, frmRsltId, (int)AccessLogging.accessLogFunctions.EXPORT, "Export JSON of assessment.");

            return(Content("formResultID on master server: " + result));
        }
Example #3
0
        /// <summary>
        /// Recursive function to get all of the sub sections of a section.
        /// </summary>
        /// <param name="sectionId">The section to get subsections of</param>
        /// <returns>A list of section IDs for the subsections</returns>
        public static void GetSubSectionsRecursive(int sectionId, ref List <int> data)
        {
            using (formsEntities context = new formsEntities())
            {
                try
                {
                    StringBuilder qry = new StringBuilder("SELECT ss.sectionId  FROM def_SectionItems si");
                    qry.Append(" JOIN def_SubSections ss ON si.subSectionId = ss.subSectionId");
                    qry.Append(" WHERE si.sectionId = " + sectionId + " ORDER BY si.[order]");

                    using (SqlConnection sqlConn = new SqlConnection(context.Database.Connection.ConnectionString))
                    {
                        sqlConn.Open();
                        using (SqlCommand command = new SqlCommand(qry.ToString(), sqlConn))
                        {
                            command.CommandType = System.Data.CommandType.Text;
                            DataTable dt = new DataTable();
                            dt.Load(command.ExecuteReader());

                            data = dt.Rows.OfType <DataRow>().Select(dr => dr.Field <int>("sectionId")).ToList();
                        }
                    }
                }
                catch (Exception ex)
                {
                    return;
                }

                List <int> sectionIds = new List <int>(data);
                if (sectionIds.Count() > 0)
                {
                    foreach (int sId in sectionIds)
                    {
                        List <int> toCombine = new List <int>();

                        GetSubSectionsRecursive(sId, ref toCombine);

                        data.AddRange(toCombine);
                    }
                }
            }
        }
        /// <summary>Database Context for the def_ tables</summary>
        ///
        public static formsEntities GetDbContext()
        {
            bool ventureMode = GetVentureMode();

            // string currentPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
            // if (currentPath.ToLower().Contains("venture"))
            if (ventureMode)
            {
                Debug.WriteLine("GetDbContext Venture mode - using formsEntitiesVenture");
                // formsEntities fe = new formsEntities("formsEntitiesVenture");

                formsEntities fe = new formsEntities("formsEntitiesVenture");
                fe.Database.Connection.ConnectionString = ventureConnectionString;

                Debug.WriteLine("   ConnectionString: " + fe.Database.Connection.DataSource);
                return(fe);
            }

            return(new formsEntities());
        }
        /// <summary>
        ///
        /// </summary>
        public void DeleteOldUploadedAssessments()
        {
            int  defaultDaysUntilDelete = 60;
            int  daysUntilDelete        = 0;
            bool entAppConfigPresent    = false;

            try
            {
                using (var uasContext = DataContext.getUasDbContext())
                {
                    entAppConfigPresent = Int32.TryParse(uasContext.uas_EntAppConfig.Where(e => e.ApplicationID == UAS.Business.Constants.APPLICATIONID && e.EnterpriseID == SessionHelper.LoginStatus.EnterpriseID && e.EnumCode == ConfigEnumCodes.VENTURE_DELETE && e.StatusFlag == "A").Select(e => e.ConfigValue).FirstOrDefault(), out daysUntilDelete);
                }

                if (!entAppConfigPresent)
                {
                    daysUntilDelete = defaultDaysUntilDelete;
                }

                DateTime deleteBeforeDate = DateTime.Now.AddDays(-daysUntilDelete);

                formsEntities          defContext  = DataContext.GetDbContext();
                List <def_FormResults> formResults = defContext.def_FormResults.Where(x => (x.statusChangeDate != null) && (x.statusChangeDate < deleteBeforeDate) && (x.formStatus == (int)FormResults_formStatus.UPLOADED) && (x.EnterpriseID == SessionHelper.LoginStatus.EnterpriseID)).ToList();
                foreach (def_FormResults formResult in formResults)
                {
                    // The database is configured with cascading SQL DELETE
                    defContext.def_FormResults.Remove(formResult);
                }

                defContext.SaveChanges();
            }
            catch (Exception excptn)
            {
                Debug.WriteLine("AJBoggs.Sis.Domain.Assessments.DeleteOldUploadedAssessments - exception:" + excptn.Message);
                string innerExcptnMsg = string.Empty;
                if ((excptn.InnerException != null) && (excptn.InnerException.Message != null))
                {
                    innerExcptnMsg = " * Inner Exception: " + excptn.InnerException.Message;
                    Debug.WriteLine("  InnerException: " + innerExcptnMsg);
                }
            }
        }
Example #6
0
        private void GetSectionItemsAndSubSectionsRecursive(int sectionId, List <def_SectionItems> sectionItems, List <def_SubSections> subSections)
        {
            formsEntities db = DataContext.GetDbContext();

            db.Configuration.LazyLoadingEnabled = false;

            List <def_SectionItems> newSectionItems = db.def_SectionItems.Where(si => si.sectionId == sectionId).ToList();

            foreach (def_SectionItems si in newSectionItems)
            {
                sectionItems.Add(si);
                if (si.subSectionId != null)
                {
                    def_SubSections subSection = db.def_SubSections.Where(ss => ss.subSectionId == si.subSectionId).FirstOrDefault();

                    subSections.Add(subSection);

                    GetSectionItemsAndSubSectionsRecursive(subSection.sectionId, sectionItems, subSections);
                }
            }
        }
Example #7
0
        /// <summary>
        /// To check whether a user is authorized to access the form result id
        /// </summary>
        /// <param name="formresultId"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static bool CACheckUserHasAccesstoFormResult(int formresultId, int userid)
        {
            AuthenticationClient authClient = new AuthenticationClient();

            bool isAuthorizedFrId = false;

            formsEntities dbContext = DataContext.GetDbContext();

            //Will change to constant from constants file after merging the attchments branch
            int enterpriseId = 8;

            List <int> authorizedFormResultIds = new List <int>();

            if (UAS_Business_Functions.hasPermission(PermissionConstants.ASSIGNED, PermissionConstants.ASSMNTS))
            {
                authorizedFormResultIds = dbContext.vFormResultUsers
                                          .Where(fr => (fr.subject == userid) && (fr.EnterpriseID == enterpriseId))
                                          .Select(fr => fr.formResultId)
                                          .ToList();
            }
            else
            {
                //Other
                var groupIds = authClient.GetGroupsInUserPermissions(enterpriseId, SessionHelper.LoginStatus.UserID).GroupBy(x => x.GroupID).Select(x => (int?)x.FirstOrDefault().GroupID);
                authorizedFormResultIds = dbContext.vFormResultUsers
                                          .Where(fr => groupIds.Contains(fr.GroupID) && (fr.EnterpriseID == enterpriseId))
                                          .Select(fr => fr.formResultId)
                                          .ToList();
            }

            if (authorizedFormResultIds.Contains(formresultId))
            {
                isAuthorizedFrId = true;
            }

            return(isAuthorizedFrId);
        }
Example #8
0
        /// <summary>
        /// Gets data from the def_FormResults record for a form result
        /// </summary>
        /// <param name="formResult">The form result to get data from</param>
        /// <param name="form">The form result's form</param>
        /// <returns>ValuePairs containing identifier = label for a form result entry, value = the form result entry (string)</returns>
        public static List <ValuePair> GetFormResultValues(def_FormResults formResult, def_Forms form)
        {
            List <ValuePair> values = new List <ValuePair>();

            //number
            ValuePair valuePair = new ValuePair(FormResultExportTagName.recipientId.ToString(), formResult.subject.ToString());

            values.Add(valuePair);

            //number
            valuePair = new ValuePair(FormResultExportTagName.formResultId.ToString(), formResult.formResultId.ToString());
            values.Add(valuePair);

            //^ SisId in csv second row

            //text
            valuePair = new ValuePair(FormResultExportTagName.identifier.ToString(), form.identifier);
            values.Add(valuePair);

            //add formId (number)
            valuePair = new ValuePair(FormResultExportTagName.formId.ToString(), form.formId.ToString());
            values.Add(valuePair);

            //number
            valuePair = new ValuePair(FormResultExportTagName.group.ToString(), formResult.GroupID.ToString());
            values.Add(valuePair);

            //number
            valuePair = new ValuePair(FormResultExportTagName.enterprise.ToString(), formResult.EnterpriseID.ToString());
            values.Add(valuePair);

            ////number
            //valuePair = new ValuePair(interviewerId, formResult.interviewer.ToString());
            //values.Add(valuePair);

            //number
            valuePair = new ValuePair(FormResultExportTagName.assigned.ToString(), formResult.assigned.ToString());
            values.Add(valuePair);

            //number
            valuePair = new ValuePair(FormResultExportTagName.statusId.ToString(), formResult.formStatus.ToString());
            values.Add(valuePair);

            //text
            valuePair = new ValuePair(FormResultExportTagName.dateUpdated.ToString(), formResult.dateUpdated.ToString());
            values.Add(valuePair);

            //text
            valuePair = new ValuePair(FormResultExportTagName.statusChangeDate.ToString(), formResult.statusChangeDate.ToString());
            values.Add(valuePair);

            valuePair = new ValuePair(FormResultExportTagName.deleted.ToString(), formResult.deleted.ToString());
            values.Add(valuePair);

            valuePair = new ValuePair(FormResultExportTagName.locked.ToString(), formResult.locked.ToString());
            values.Add(valuePair);

            valuePair = new ValuePair(FormResultExportTagName.archived.ToString(), formResult.archived.ToString());
            values.Add(valuePair);

            //number
            valuePair = new ValuePair(FormResultExportTagName.reviewStatus.ToString(), formResult.reviewStatus.ToString());
            values.Add(valuePair);

            //number
            valuePair = new ValuePair(FormResultExportTagName.lastModifiedByUserId.ToString(), formResult.LastModifiedByUserId.ToString());
            values.Add(valuePair);

            //pull info that comes from other def tables
            using (formsEntities def = DataContext.GetDbContext())
            {
                try
                {
                    int    statusMasterId   = def.def_StatusMaster.Where(sm => sm.formId == 1 && sm.ApplicationId == 1).Select(sm => sm.statusMasterId).First();
                    int    statusDetailId   = def.def_StatusDetail.Where(sd => sd.statusMasterId == statusMasterId && sd.sortOrder == formResult.reviewStatus).Select(sd => sd.statusDetailId).First();
                    string reviewStatusText = def.def_StatusText.Where(st => st.statusDetailId == statusDetailId).Select(st => st.displayText).First();
                    valuePair = new ValuePair(FormResultExportTagName.reviewStatusText.ToString(), reviewStatusText);
                    values.Add(valuePair);
                }
                catch (Exception e) { Debug.WriteLine(e); }

                try
                {
                    valuePair = new ValuePair(FormResultExportTagName.statusText.ToString(),
                                              ((WebService.WSConstants.FR_formStatus)(formResult.formStatus)).ToString());
                    values.Add(valuePair);
                }
                catch (Exception e) { Debug.WriteLine(e); }
            }

            //pull info that comes from uas tables
            using (UASEntities uas = DataContext.getUasDbContext())
            {
                try
                {
                    valuePair = new ValuePair(FormResultExportTagName.groupName.ToString(),
                                              uas.uas_Group
                                              .Where(g => g.GroupID == formResult.GroupID)
                                              .Select(g => g.GroupName).First());
                    values.Add(valuePair);
                }
                catch (Exception e) { Debug.WriteLine(e); }

                try
                {
                    valuePair = new ValuePair(FormResultExportTagName.assignedLoginId.ToString(),
                                              uas.uas_User
                                              .Where(u => u.UserID == formResult.assigned)
                                              .Select(u => u.LoginID).First());
                    values.Add(valuePair);
                }
                catch (Exception e) { Debug.WriteLine(e); }

                try
                {
                    valuePair = new ValuePair(FormResultExportTagName.lastModifiedByLoginId.ToString(),
                                              uas.uas_User
                                              .Where(u => u.UserID == formResult.LastModifiedByUserId)
                                              .Select(u => u.LoginID).First());
                    values.Add(valuePair);
                }
                catch (Exception e) { Debug.WriteLine(e); }

                try
                {
                    valuePair = new ValuePair(FormResultExportTagName.enterpriseName.ToString(),
                                              uas.uas_Enterprise
                                              .Where(e => e.EnterpriseID == formResult.EnterpriseID)
                                              .Select(e => e.EnterpriseName).First());
                    values.Add(valuePair);
                }
                catch (Exception e) { Debug.WriteLine(e); }
            }

            return(values);
        }
Example #9
0
        public FileStreamResult ExportFormJSON()
        {
            //use session variables to pick a FormResults object from the database
            string formId = Request["formId"] as string;

            Debug.WriteLine("* * *  GetFormResultsJSON formId: " + formId);

            int iFormId = Convert.ToInt32(formId);

            formsEntities db = DataContext.GetDbContext();

            db.Configuration.LazyLoadingEnabled = false;

            // def_Forms form = db.def_Forms
            //     .Include(fr => fr.def_FormParts
            //         .Select(fp => fp.def_Parts)
            //         .Select(pa => pa.def_PartSections
            //             .Select(p => p.def_Sections)
            //             .Select(s => s.def_SectionItems
            //             .Select(si => si.def_SubSections))))
            //     .Include(fr => fr.def_FormParts
            //         .Select(fp => fp.def_Parts)
            //         .Select(pa => pa.def_PartSections
            //             .Select(p => p.def_Sections)
            //             .Select(s => s.def_SectionItems
            //                 .Select(si => si.def_Items)
            //                 .Select(i => i.def_ItemVariables))))
            //     .Single(f => f.formId == iFormId);



            def_Forms            form      = db.def_Forms.Where(f => f.formId == iFormId).FirstOrDefault();
            List <def_FormParts> formParts = db.def_FormParts.Where(fp => fp.formId == iFormId).ToList();
            List <int>           partIds   = formParts.Select(fp => fp.partId).ToList();
            List <def_Parts>     parts     = db.def_Parts.Where(p => partIds.Contains(p.partId)).ToList();

            partIds = parts.Select(p => p.partId).ToList();
            List <def_PartSections> partSections = db.def_PartSections.Where(ps => partIds.Contains(ps.partId)).ToList();
            List <int>          sectionIds       = partSections.Select(ps => ps.sectionId).ToList();
            List <def_Sections> sections         = db.def_Sections.Where(s => sectionIds.Contains(s.sectionId)).ToList();

            List <def_SectionItems>  sectionItems  = new List <def_SectionItems>();
            List <def_SubSections>   subSections   = new List <def_SubSections>();
            List <def_Items>         items         = new List <def_Items>();
            List <def_ItemVariables> itemVariables = new List <def_ItemVariables>();

            foreach (def_Sections section in sections)
            {
                GetSectionItemsAndSubSectionsRecursive(section.sectionId, sectionItems, subSections);
            }

            foreach (def_SectionItems si in sectionItems)
            {
                if (si.subSectionId == null)
                {
                    def_Items item = db.def_Items.Where(i => i.itemId == si.itemId).FirstOrDefault();

                    items.Add(item);

                    List <def_ItemVariables> newItemVariables = db.def_ItemVariables.Where(iv => iv.itemId == item.itemId).ToList();

                    itemVariables.AddRange(newItemVariables);
                }
            }


            string jsonString = "{\"data\":[" + fastJSON.JSON.ToJSON(form);

            jsonString += "," + fastJSON.JSON.ToJSON(formParts);
            jsonString += "," + fastJSON.JSON.ToJSON(parts);
            jsonString += "," + fastJSON.JSON.ToJSON(partSections);
            jsonString += "," + fastJSON.JSON.ToJSON(sections);
            jsonString += "," + fastJSON.JSON.ToJSON(sectionItems);
            jsonString += "," + fastJSON.JSON.ToJSON(subSections);
            jsonString += "," + fastJSON.JSON.ToJSON(items);
            jsonString += "," + fastJSON.JSON.ToJSON(itemVariables);
            jsonString += "]}";

            MemoryStream stream = new MemoryStream();

            //write json string to file, then stream it out
            //DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(FormResultJSON));

            StreamWriter streamWriter = new StreamWriter(stream);

            streamWriter.Write(jsonString);
            //streamWriter.Close();
            //stream.Close();

            FileStreamResult fsr = null;

            streamWriter.Flush();
            stream.Position = 0;

            try
            {
                fsr = new FileStreamResult(stream, "application/json")
                {
                    FileDownloadName = form.identifier + " " + DateTime.Now + ".json"
                };
            }
            catch (Exception exp)
            {
                Debug.WriteLine("File write exception: " + exp.Message);
                string errMsg = "File write exception: " + exp.Message;
                return(ProcessError(errMsg));
            }


            return(fsr);
        }
Example #10
0
        /// <summary>
        /// Primary method for processing database queries posted to DataTables.  Intended to be universal for all ADAP Applicant and Dashboard Reports.
        /// </summary>
        /// <param name="query">The query initialized by the DataTable Web Service calling this method.</param>
        /// <param name="sFName">String of the applicant's first name</param>
        /// <param name="sLName">String of the applicant's last name</param>
        /// <param name="sTeam">String of the Team associated with the application.</param>
        /// <param name="sStat">String of the Status of the application.  Some reports use a specialized key word to add additional parameters to the query.</param>
        /// <param name="sDate">String of the Date of the application.  Some reports use specialized key words to add additional parameters to the query.</param>
        /// <returns>IQueryable<vFormResultUsers> with additional parameters added for the DataTable.</returns>
        public IQueryable <vFormResultUser> SetVfruQueryParams(IQueryable <vFormResultUser> query, String sFName, String sLName, String sTeam, String sStat, String sDate, String sDob, String ssn, string adapId,
                                                               string siteNum, string enrollmentSite, String formType, String elgEndFrom, String elgEndTo, int statusMasterId = 1)
        {
            query = query.Where(q => q.StatusFlag.Equals("A"));
            if (!String.IsNullOrEmpty(sFName))
            {
                query = query.Where(q => q.FirstName.Contains(sFName));
            }

            if (!String.IsNullOrEmpty(sLName))
            {
                query = query.Where(q => q.LastName.Contains(sLName));
            }

            if (!string.IsNullOrWhiteSpace(siteNum))
            {
                query = query.Where(q => q.GroupName.StartsWith(siteNum));
            }

            int formId = 15;

            if (!string.IsNullOrWhiteSpace(formType))
            {
                var parts      = formType.Split('|');
                var formIdPart = parts[0];
                if (int.TryParse(formIdPart, out formId))
                {
                    query = query.Where(q => q.formId == formId);
                }

                if (formId == 15)
                {
                    var formVariant = parts[1];
                    if (formVariant != "Initial Enrollment Application")
                    {
                        query = query.Where(q => q.formVariant == formVariant);
                    }
                    else
                    {
                        query = query.Where(q => q.formVariant == null || q.formVariant == formVariant);
                    }
                }
            }

            if (!string.IsNullOrWhiteSpace(elgEndFrom))
            {
                DateTime elgEndFromDate;
                if (DateTime.TryParse(elgEndFrom, out elgEndFromDate))
                {
                    query = query.Where(q => q.EligibilityEndDate >= elgEndFromDate);
                }
            }

            if (!string.IsNullOrWhiteSpace(elgEndTo))
            {
                DateTime elgEndToDate;
                if (DateTime.TryParse(elgEndTo, out elgEndToDate))
                {
                    query = query.Where(q => q.EligibilityEndDate <= elgEndToDate);
                }
            }

            if (!String.IsNullOrWhiteSpace(enrollmentSite))
            {
                var context  = DataContext.getUasDbContext();
                var groupIds = (from g in context.uas_Group
                                where g.GroupDescription.Contains(enrollmentSite) &&
                                g.GroupTypeID == 193
                                select g.GroupID).ToList();
                query = query.Where(q => groupIds.Contains(q.GroupID.Value));
            }

            if (!String.IsNullOrEmpty(sTeam) && !sTeam.Equals("All"))
            {
                // serach by unit
                // get child groups of unit
                var context = DataContext.getUasDbContext();
                int groupId;
                int.TryParse(sTeam, out groupId);
                List <int> groupIds = (from g in context.uas_Group
                                       where g.ParentGroupId == groupId ||
                                       g.GroupID == groupId
                                       select g.GroupID).ToList();

                query = query.Where(q => groupIds.Contains(q.GroupID.Value));
            }

            if (!String.IsNullOrEmpty(sStat) && !sStat.Equals("All"))
            {
                if (sStat.Equals("Pending") || sStat.Equals("All Pending"))
                {
                    // In Process = 0, Needs Review = 1, Needs Information = 2
                    int inProcess        = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "IN_PROCESS").sortOrder);
                    int needsReview      = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "NEEDS_REVIEW").sortOrder);
                    int needsInformation = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "NEEDS_INFORMATION").sortOrder);
                    query = query.Where(q => q.formStatus == inProcess || q.formStatus == needsReview || q.formStatus == needsInformation);
                }
                else
                {
                    if (sStat.Contains(" Pending"))
                    {
                        sStat = sStat.Substring(0, sStat.IndexOf(" Pending"));
                    }

                    // In Process = 0, Needs Review = 1, Needs Information = 2, Denied = 3, Approved = 4, Cancelled = 5
                    if (!string.IsNullOrWhiteSpace(formType))
                    {
                        def_StatusMaster statusMaster = formsRepo.GetStatusMasterByFormId(formId);
                        statusMasterId = statusMaster == null ? 1 : statusMaster.statusMasterId;
                        var statusDisplay = formsRepo.GetStatusDetailByDisplayText(statusMasterId, sStat);
                        int index         = statusDisplay != null ? statusDisplay.sortOrder.Value : -1;
                        query = query.Where(q => q.formStatus == index);
                    }
                    else
                    {
                        // all form types
                        // form enrollment
                        int formIdStandard            = 15;
                        int formIdMoop                = 17;
                        int formIdSvf                 = 19;
                        def_StatusMaster statusMaster = formsRepo.GetStatusMasterByFormId(formIdStandard);
                        statusMasterId = statusMaster == null ? 1 : statusMaster.statusMasterId;
                        var statusDisplay = formsRepo.GetStatusDetailByDisplayText(statusMasterId, sStat);
                        int index         = statusDisplay != null ? statusDisplay.sortOrder.Value : -1;

                        // form moop
                        def_StatusMaster statusMasterMoop = formsRepo.GetStatusMasterByFormId(formIdMoop);
                        var statusMasterIdMoop            = statusMasterMoop == null ? 1 : statusMasterMoop.statusMasterId;
                        var statusDisplayMoop             = formsRepo.GetStatusDetailByDisplayText(statusMasterIdMoop, sStat);
                        int indexMoop = statusDisplayMoop != null ? statusDisplayMoop.sortOrder.Value : -1;

                        // form svf
                        def_StatusMaster statusMasterSvf = formsRepo.GetStatusMasterByFormId(formIdSvf);
                        var statusMasterIdSvf            = statusMasterSvf == null ? 1 : statusMasterSvf.statusMasterId;
                        var statusDisplaySvf             = formsRepo.GetStatusDetailByDisplayText(statusMasterIdSvf, sStat);
                        int indexSvf = statusDisplaySvf != null ? statusDisplaySvf.sortOrder.Value : -1;

                        query = query.Where(q => (q.formStatus == index && q.formId == formIdStandard) ||
                                            (q.formStatus == indexSvf && q.formId == formIdSvf) ||
                                            (q.formStatus == indexMoop && q.formId == formIdMoop));
                    }
                }
            }

            if (!String.IsNullOrWhiteSpace(sDob))
            {
                DateTime dob;
                if (DateTime.TryParse(sDob, out dob))
                {
                    query = query.Where(q => q.DOB == dob);
                }
            }

            if (!string.IsNullOrWhiteSpace(ssn))
            {
                formsEntities context       = new formsEntities();
                var           formResultIds = (from iv in context.def_ItemVariables
                                               join rv in context.def_ResponseVariables on iv.itemVariableId equals rv.itemVariableId
                                               join ir in context.def_ItemResults on rv.itemResultId equals ir.itemResultId
                                               where iv.identifier == "C1_MemberSocSecNumber" && rv.rspValue == ssn
                                               select ir.formResultId);
                query = from q in query
                        join fr in formResultIds.ToList() on q.formResultId equals fr
                        select q;
            }

            if (!string.IsNullOrWhiteSpace(adapId))
            {
                query = query.Where(x => x.adap_id == adapId);
            }

            if (!String.IsNullOrEmpty(sDate) && !sDate.Equals("All"))
            {
                //int formId = formsRepo.GetFormByIdentifier("ADAP").formId;
                // "Re-Certs Late" still needs to be added.
                // Current calculation does not account for Late re-certifications.
                if (sDate.Contains("Last Modified within"))
                {
                    try {
                        int      span    = Convert.ToInt32(sDate.Substring(21, 1));
                        DateTime compare = DateTime.Now.AddDays(span * -30);
                        query = query.Where(q => q.dateUpdated >= compare);
                    }
                    catch (FormatException ex) {
                        Debug.WriteLine("Adap Applications sDate span: " + ex.Message);
                    }
                }
                if (sDate.Contains("Re-Certs"))
                {
                    /// Base Rule: END of birth month, END of birth month +6.
                    ///
                    /// Upon first approval, if base rule results in LESS THAN 3 months, advance to the next 6 month date.  E.g. birth month March,
                    /// initially approved July.  Instead of recert due Sept 30, due next March 31.  Exception applies for initial approval.
                    /// After that, even if a recert is late, it doesn't delay the next recert due from the Base Rule.

                    int approved = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "APPROVED").sortOrder);
                    IQueryable <vFormResultUser> subQuery = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId)
                                                            .Where(sq => sq.formStatus == approved && sq.StatusFlag.Equals("A")).OrderByDescending(sq => sq.statusChangeDate);

                    // Duplicated variable to prevent InvalidOperationException: "A cycle was detected in a LINQ expression."
                    IQueryable <vFormResultUser> accepted = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId)
                                                            .Where(sq => sq.formStatus == approved && sq.StatusFlag.Equals("A")).OrderByDescending(sq => sq.statusChangeDate);

                    // DbFunctions formula:  AddDays(AddMonths(CreateDatetime()))
                    // CreateDateTime() to assemble the birthdate of the current year on the first of the month.
                    // AddMonths() to either advance the birthdate to 7 months in the future, or a single month in the future.
                    // AddDays() to subtract a single day, resulting in either the last day of the month 6 months after the birthdate, or the last day of the birth month.

                    // Logic used below should reflect the logic used in the getRecert method.
                    // nextRecert pseudocode: create new IQueryable<RecertObject>
                    // Test if the current time falls between the birth month and 6 months from the birth month ( DOB < Today < DOB + 6 Months )
                    //      if true: use DbFunctions formula to find the last day of the month 6 months after the birthdate
                    //      if false: Test if the current time is later than 6 months from teh birth month.  ( DOB + 6 Months < Today )
                    //          if true: use DbFunctions formula to find the last day of the birth month in the following year.
                    //          if false: use DbFunctions formula to find the last day of the birth month
                    IQueryable <RecertObject> nextRecert = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId)
                                                           .Select(nr => new RecertObject {
                        formResultId = nr.formResultId,
                        subject      = nr.subject,
                        recert       = (nr.DOB.Value.Month < DateTime.Now.Month && DateTime.Now.Month <= nr.DOB.Value.Month + 6)
                                                                        ? DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.CreateDateTime(DateTime.Now.Year, nr.DOB.Value.Month, 1, 0, 0, 0), 7), -1)
                                                                        : (nr.DOB.Value.Month + 6 < DateTime.Now.Month)
                                                                        ? DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.CreateDateTime(DateTime.Now.Year + 1, nr.DOB.Value.Month, 1, 0, 0, 0), 1), -1)
                                                                        : DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.CreateDateTime(DateTime.Now.Year, nr.DOB.Value.Month, 1, 0, 0, 0), 1), -1)
                    });

                    // applyExcep pseudocode: edit nextRecert object to apply the 3 month exception.
                    // Ensure there is only one accepted application, then
                    // Test if the absolute value of the most recent statusChangeDate for the subject minus the nextRecert.recert is LESS THAN 3 ( statusChangeDate - recert < 3 )
                    //      if true: Find the last day of the month 6 months from the recert with recert.AddDays(+1) -> AddMonths(+6) -> AddDays(-1)
                    //      if false: Use the established recert date.
                    IQueryable <RecertObject> applyExcep = nextRecert.Where(nr => accepted.Where(sq => sq.subject == nr.subject).Count() > 0)
                                                           .Select(nr => new RecertObject {
                        formResultId = nr.formResultId,
                        subject      = nr.subject,
                        recert       = (accepted.Where(sq => sq.subject == nr.subject).Count() == 1 &&
                                        Math.Abs(accepted.Select(sq => sq.statusChangeDate).FirstOrDefault().Value.Month - nr.recert.Value.Month) < 3)
                                                                                        ? DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.AddDays(nr.recert, 1), 6), -1)
                                                                                        : nr.recert
                    });

                    //// Data check for validation.
                    //List<RecertObject> recertCheck = new List<RecertObject>();
                    //foreach (var v in applyExcep)
                    //{
                    //    recertCheck.Add(v);
                    //}

                    DateTime deadline = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                    deadline = deadline.AddMonths(1).AddDays(-1);
                    if (sDate.Contains("Re-Certs Late"))
                    {
                        // Adjust the deadline to 6 months prior to the current deadline.
                        deadline = deadline.AddDays(1).AddMonths(-6).AddDays(-1);

                        // Take the most recently accepted application and check if the statusChangeDate falls before the deadline.
                        subQuery = subQuery.Where(sq => accepted.Where(ac => ac.subject == sq.subject).Select(ac => ac.statusChangeDate).FirstOrDefault() < deadline);
                    }
                    else if (sDate.Contains("Re-Certs Due within 7 Days"))
                    {
                        // Adjust deadline to the current date so recerts due will only show when the due date is within 7 days.
                        deadline = DateTime.Now;
                        DateTime range = deadline.AddDays(7);

                        subQuery = subQuery.Where(sq => deadline <= applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() &&
                                                  applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() <= range);

                        //// Data check for validation
                        //List<string[]> dataCheck = new List<string[]>();
                        //foreach (var v in subQuery)
                        //{
                        //    dataCheck.Add(new string[] { v.subject.ToString() });
                        //}
                    }
                    else if (sDate.Contains("Re-Certs Due within"))
                    {
                        try {
                            // This block begins by parsing the month multiplier from the selection string.
                            int      span  = Convert.ToInt32(sDate.Substring(20, 1));
                            DateTime range = deadline.AddDays(1).AddMonths(span).AddDays(-1);
                            subQuery = subQuery.Where(sq => deadline <= applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() &&
                                                      applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() <= range);
                        }
                        catch (FormatException ex) {
                            Debug.WriteLine("Adap Reports Controller sDate span: " + ex.Message);
                        }
                    }

                    query = query.Where(q => subQuery.Where(sq => sq.subject == q.subject).Select(sq => sq.subject).FirstOrDefault() == q.subject);
                    query = query.OrderByDescending(q => q.dateUpdated);
                }
                else if (sDate.Contains("&"))
                {
                    string[] dates = sDate.Split('&');
                    DateTime from;
                    bool     fromBool = true;
                    DateTime to;
                    bool     toBool = true;
                    if (!String.IsNullOrEmpty(dates[0]))
                    {
                        try {
                            from = Convert.ToDateTime(dates[0]);
                        }
                        catch (FormatException ex) {
                            Debug.WriteLine("Adap Reports Controller sDate from date conversation: " + ex.Message);
                            from = new DateTime(2000, 1, 1);
                        }
                    }
                    else
                    {
                        from     = new DateTime(2000, 1, 1);
                        fromBool = false;
                    }

                    if (!String.IsNullOrEmpty(dates[1]))
                    {
                        try {
                            to = Convert.ToDateTime(dates[1]);
                        }
                        catch (FormatException ex) {
                            Debug.WriteLine("Adap Reports Controller sDate to date conversation: " + ex.Message);
                            to = DateTime.Now.AddDays(1);
                        }
                    }
                    else
                    {
                        to     = DateTime.Now.AddDays(1);
                        toBool = false;
                    }

                    IQueryable <vFormResultUser> subQuery = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId);
                    int needsReview = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "NEEDS_REVIEW").sortOrder);
                    subQuery = subQuery.Where(sq => /*sq.formStatus == needsReview &&*/ sq.StatusFlag.Equals("A"));
                    if (dates[2].Contains("Overview"))
                    {
                        subQuery = subQuery.Where(sq => (sq.formStatus == needsReview) &&
                                                  ((fromBool) ? sq.statusChangeDate >= from : true) && ((toBool) ? sq.statusChangeDate <= to : true)).OrderByDescending(sq => sq.statusChangeDate);
                    }
                    else if (dates[2].Contains("Summary"))
                    {
                        subQuery = subQuery.Where(sq => ((fromBool) ? sq.statusChangeDate >= from : true) && ((toBool) ? sq.statusChangeDate <= to : true))
                                   .OrderByDescending(sq => sq.statusChangeDate);
                    }
                    //List<string[]> dataCheck = new List<string[]>();
                    //foreach (var v in subQuery)
                    //{
                    //    dataCheck.Add(new string[] { v.subject.ToString(), v.formStatus.ToString(), v.GroupName, v.statusChangeDate.ToString() });
                    //}

                    if (dates[2].Contains("Overview"))
                    {
                        query = query.Where(q => q.subject == subQuery.Where(sq => sq.subject == q.subject).Select(sq => sq.subject).FirstOrDefault());
                    }
                    else if (dates[2].Contains("Summary"))
                    {
                        query = query.Where(q => q.formResultId == subQuery.Where(sq => sq.subject == q.subject)
                                            .OrderByDescending(sq => sq.statusChangeDate).Select(sq => sq.formResultId).FirstOrDefault());
                    }
                }
            }

            return(query);
        }
Example #11
0
        public void SaveBatchResponses(Dictionary <int, Dictionary <int, string[]> > rspValsByIvByItem, int[] formResultIds)
        {
            DateTime startTime = DateTime.Now;

            try
            {
                using (formsEntities tempCtx = DataContext.GetDbContext())
                {
                    for (int i = 0; i < formResultIds.Length; i++)
                    {
                        int frId = formResultIds[i];

                        def_FormResults fr = tempCtx.def_FormResults.Where(frt => frt.formResultId == frId).SingleOrDefault();
                        foreach (int itmId in rspValsByIvByItem.Keys)
                        {
                            Dictionary <int, string[]> rspValsByIv = rspValsByIvByItem[itmId];
                            def_ItemResults            ir          = new def_ItemResults()
                            {
                                itemId        = itmId,
                                sessionStatus = 0,
                                dateUpdated   = DateTime.Now
                            };

                            foreach (int ivId in rspValsByIv.Keys)
                            {
                                string rspVal = rspValsByIv[ivId][i];
                                ir.def_ResponseVariables.Add(new def_ResponseVariables()
                                {
                                    itemVariableId = ivId,
                                    rspValue       = rspVal
                                });
                            }

                            fr.def_ItemResults.Add(ir);
                        }

                        tempCtx.def_FormResults.Attach(fr);
                        tempCtx.Entry(fr).State = EntityState.Modified;
                    }
                    tempCtx.SaveChanges();
                }
            }
            catch (DbEntityValidationException dbEx)
            {
                Debug.WriteLine("* * *  FormsRepository  SaveBatchResponses DbEntityValidationException: " + dbEx.Message);
                foreach (DbEntityValidationResult devr in dbEx.EntityValidationErrors)
                {
                    foreach (DbValidationError dve in devr.ValidationErrors)
                    {
                        Debug.WriteLine("    DbEntityValidationResult: " + dve.ErrorMessage);
                    }
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine("* * *  FormsRepository  SaveBatchResponses exception: " + ex.Message);
            }

            Debug.WriteLine("* * * FormsRepository  SaveBatchResponses completed in " + (DateTime.Now - startTime).Ticks + " ticks");

            return;
        }