/// <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);
}
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));
}
/// <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);
}
}
}
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);
}
}
}
/// <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);
}
/// <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);
}
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);
}
/// <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);
}
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;
}
