public AppViewModel GetAppViewModel(Guid id)
{
AppViewModel avm = new AppViewModel();
avm.Id = id;
using (SlpContext context = new SlpContext())
{
SlpApplication dbApp = context.SlpApplication.Find(avm.Id);
if (dbApp != null)
{
avm.AssistanceProgramId = dbApp.AssistanceProgramId;
avm.AssistanceProgramCaseNumber = dbApp.AssistanceProgramCaseNumber;
avm.TotalMembers = dbApp.TotalMembers;
avm.MemberLastFourSSN = dbApp.MemberLastFourSSN;
avm.StreetAddress = dbApp.StreetAddress;
avm.AptNo = dbApp.AptNo;
avm.City = dbApp.City;
avm.State = dbApp.State;
avm.Zip = dbApp.Zip;
avm.Email = dbApp.Email;
avm.Phone = dbApp.Phone;
avm.AdultFilledByName = dbApp.AdultFilledByName;
avm.EthnicityId = dbApp.EthnicityId;
avm.NoSSN = dbApp.NoSSN;
}
}
List<MemberViewModel> members = GetMembers(avm.Id);
avm.Children = members.Where(a => a.IsChild == true).ToList();
avm.Adults = members.Where(a => a.IsChild == false).ToList();
//set domain values, default for all applications new/old
using (SlpContext context = new SlpContext())
{
avm.AssistancePrograms = context.AssistanceProgram.Where(a => a.Visible == true).Select(a => new NameValueViewModel { Name = a.Name, Value = a.Id }).OrderBy(a => a.Value).ToList();
avm.Ethnicities = context.Ethnicity.Where(a => a.Visible == true).Select(a => new NameValueViewModel { Name = a.Name, Value = a.Id }).OrderBy(a => a.Value).ToList();
avm.Frequencies = context.Frequency.Where(a => a.Visible == true).Select(a => new NameValueViewModel { Name = a.Name, Value = a.Id }).OrderBy(a => a.Value).ToList();
avm.Races = context.Race.Where(a => a.Visible == true).Select(a => new NameValueViewModel { Name = a.Name, Value = a.Id }).OrderBy(a => a.Value).ToList();
foreach (SlpApplicationRace dbrace in context.SlpApplicationRace.Where(a => a.SlpApplicationId == avm.Id))
{
NameValueViewModel race = avm.Races.Where(a => a.Value == dbrace.RaceId).FirstOrDefault();
if (race != null)
{
race.SelectedItem = true;
}
}
}
avm.AssistanceProgramNoneId = (int)AssistanceProgramsEnum.None;
return avm;
}
public MemberViewModel GetNewMember(Guid appId, bool IsChild)
{
MemberViewModel mvm = new MemberViewModel
{
AppId = appId,
IsChild = IsChild,
IsRowInEditMode = true,
IsNewRow = true,
FirstName = "",
MiddleInitial = "",
LastName = ""
};
if (IsChild)
{
using (SlpContext context = new SlpContext())
{
mvm.ChildAttributes = context.ChildAttributeTypes.Where(a => a.Visible == true).Select(a => new NameValueViewModel { Name = a.Name, Value = a.Id }).OrderBy(a => a.Value).ToList();
}
}
return mvm;
}
public ListViewModel GetListViewModel(int id)
{
ListViewModel lvm = new ListViewModel { ListId = id };
using (SlpContext context = new SlpContext())
{
if (lvm.ListId == (int)ListEnum.AssistancePrograms)
{
lvm.TableValues.AddRange(context.AssistanceProgram.Select(a => new NameValueViewModel { Name = a.Name, Value = a.Id, SelectedItem = a.Visible }));
}
else if (lvm.ListId == (int)ListEnum.ChildCategories)
{
lvm.TableValues.AddRange(context.ChildAttributeTypes.Select(a => new NameValueViewModel { Name = a.Name, Value = a.Id, SelectedItem = a.Visible }));
}
else if (lvm.ListId == (int)ListEnum.EthnicityList)
{
lvm.TableValues.AddRange(context.Ethnicity.Select(a => new NameValueViewModel { Name = a.Name, Value = a.Id, SelectedItem = a.Visible }));
}
else if (lvm.ListId == (int)ListEnum.RaceList)
{
lvm.TableValues.AddRange(context.Race.Select(a => new NameValueViewModel { Name = a.Name, Value = a.Id, SelectedItem = a.Visible }));
}
else if (lvm.ListId == (int)ListEnum.IncomeFrequencies)
{
lvm.TableValues.AddRange(context.Frequency.Select(a => new NameValueViewModel { Name = a.Name, Value = a.Id, SelectedItem = a.Visible }));
}
else if (lvm.ListId == (int)ListEnum.IncomeQuestionsChild)
{
lvm.TableValues.AddRange(context.IncomeQuestions.Where(b=>b.IsChild == true).Select(a => new NameValueViewModel { Name = a.Text, Value = a.Id, SelectedItem = a.Visible, Type= a.Type }));
}
else if (lvm.ListId == (int)ListEnum.IncomeQuestionsAdult)
{
lvm.TableValues.AddRange(context.IncomeQuestions.Where(b => b.IsChild == false).Select(a => new NameValueViewModel { Name = a.Text, Value = a.Id, SelectedItem = a.Visible, Type = a.Type }));
}
}
return lvm;
}
public void SaveListViewModel(ListViewModel lvm)
{
using (SlpContext context = new SlpContext())
{
if (lvm.ListId == (int)ListEnum.AssistancePrograms)
{
foreach (NameValueViewModel item in lvm.TableValues)
{
if (item.Value == 0 && item.Name != null && item.Name.Trim()!= "")
{
context.AssistanceProgram.Add(new AssistanceProgram { Name = item.Name, Visible = item.SelectedItem });
}
else {
var dbitem = context.AssistanceProgram.Find(item.Value);
dbitem.Name = item.Name;
dbitem.Visible = item.SelectedItem;
}
}
}
else if (lvm.ListId == (int)ListEnum.ChildCategories)
{
foreach (NameValueViewModel item in lvm.TableValues)
{
if (item.Value == 0 && item.Name != null && item.Name.Trim() != "")
{
context.ChildAttributeTypes.Add(new ChildAttributeType { Name = item.Name, Visible = item.SelectedItem });
}
else
{
var dbitem = context.ChildAttributeTypes.Find(item.Value);
dbitem.Name = item.Name;
dbitem.Visible = item.SelectedItem;
}
}
}
else if (lvm.ListId == (int)ListEnum.EthnicityList)
{
foreach (NameValueViewModel item in lvm.TableValues)
{
if (item.Value == 0 && item.Name != null && item.Name.Trim() != "")
{
context.Ethnicity.Add(new Ethnicity { Name = item.Name, Visible = item.SelectedItem });
}
else
{
var dbitem = context.Ethnicity.Find(item.Value);
dbitem.Name = item.Name;
dbitem.Visible = item.SelectedItem;
}
}
}
else if (lvm.ListId == (int)ListEnum.RaceList)
{
foreach (NameValueViewModel item in lvm.TableValues)
{
if (item.Value == 0 && item.Name != null && item.Name.Trim() != "")
{
context.Race.Add(new Race { Name = item.Name, Visible = item.SelectedItem });
}
else
{
var dbitem = context.Race.Find(item.Value);
dbitem.Name = item.Name;
dbitem.Visible = item.SelectedItem;
}
}
}
else if (lvm.ListId == (int)ListEnum.IncomeFrequencies)
{
foreach (NameValueViewModel item in lvm.TableValues)
{
if (item.Value == 0 && item.Name != null && item.Name.Trim() != "")
{
context.Frequency.Add(new Frequency { Name = item.Name, Visible = item.SelectedItem });
}
else
{
var dbitem = context.Frequency.Find(item.Value);
dbitem.Name = item.Name;
dbitem.Visible = item.SelectedItem;
}
}
}
else if (lvm.ListId == (int)ListEnum.IncomeQuestionsChild || lvm.ListId == (int)ListEnum.IncomeQuestionsAdult)
{
foreach (NameValueViewModel item in lvm.TableValues)
{
if (item.Value == 0 && item.Name != null && item.Name.Trim() != "")
{
context.IncomeQuestions.Add(new IncomeQuestion {
Text = item.Name,
Visible = item.SelectedItem,
IsChild = lvm.ListId == (int)ListEnum.IncomeQuestionsChild,
Type = item.Type });
}
else
{
var dbitem = context.IncomeQuestions.Find(item.Value);
dbitem.Text = item.Name;
dbitem.Visible = item.SelectedItem;
dbitem.Type = item.Type;
}
}
}
context.SaveChanges();
}
}
public List<AppDataFlatViewModel> AllAppData() {
List<AppDataFlatViewModel> apps = new List<AppDataFlatViewModel>();
List<NameViewModel> allRaces = new List<NameViewModel>();
using (SlpContext context = new SlpContext())
{
apps =
context.SlpApplication.Where(a => a.CompletedDate != null).Select(b =>
new AppDataFlatViewModel
{
AppId = b.Id,
AssistanceProgramName = b.AssistanceProgramId.HasValue && b.AssistanceProgram != null ? b.AssistanceProgram.Name : "",
AssistanceProgramCaseNumber = b.AssistanceProgramCaseNumber,
TotalMembers = b.TotalMembers.ToString(),
MemberLastFourSSN = b.MemberLastFourSSN.ToString(),
NoSSNFlag = b.NoSSN ? "true" : "",
StreetAddress = b.StreetAddress,
AptNo = b.AptNo,
City = b.City,
State = b.State,
Zip = b.Zip,
Phone = b.Phone,
Email = b.Email,
AdultFilledByName = b.AdultFilledByName,
CompletedDate = b.CompletedDate.ToString(),
Ethnicity = b.EthnicityId.HasValue ? b.Ethnicity.Name : ""
}).ToList();
//Race { get; set; }
allRaces = context.SlpApplicationRace.Select(a => new NameViewModel { Id = a.SlpApplicationId, Name = a.Race.Name }).ToList();
}
//child name and income
//adult names and income...
List<MemberViewModel> _allMembers = new List<MemberViewModel>();
AppOrchestrator appOrc = new AppOrchestrator();
_allMembers = appOrc.GetMembers(null);
foreach (AppDataFlatViewModel appdata in apps)
{
appdata.Race = string.Join(", ", allRaces.Where(a => a.Id == appdata.AppId).Select(a => a.Name));
List<MemberViewModel> appChilds = _allMembers.Where(a => a.AppId == appdata.AppId && a.IsChild == true).OrderBy(a => a.CreatedDate).Take(5).ToList();
List<MemberViewModel> appAdults = _allMembers.Where(a => a.AppId == appdata.AppId && a.IsChild == false).OrderBy(a => a.CreatedDate).Take(5).ToList();
int childCount = 0;
foreach (MemberViewModel child in appChilds)
{
childCount++;
if (childCount == 1)
{
appdata.Child1FirstName = child.FirstName;
appdata.Child1MiddleInitial = child.MiddleInitial;
appdata.Child1LastName = child.LastName;
appdata.Child1Info = string.Join(", ", child.ChildAttributes.Where(a => a.SelectedItem == true).Select(a => a.Name));
appdata.Child1Income = child.TotalIncomeReported;
}
if (childCount == 2)
{
appdata.Child2FirstName = child.FirstName;
appdata.Child2MiddleInitial = child.MiddleInitial;
appdata.Child2LastName = child.LastName;
appdata.Child2Info = string.Join(", ", child.ChildAttributes.Where(a => a.SelectedItem == true).Select(a => a.Name));
appdata.Child2Income = child.TotalIncomeReported;
}
if (childCount == 3)
{
appdata.Child3FirstName = child.FirstName;
appdata.Child3MiddleInitial = child.MiddleInitial;
appdata.Child3LastName = child.LastName;
appdata.Child3Info = string.Join(", ", child.ChildAttributes.Where(a => a.SelectedItem == true).Select(a => a.Name));
appdata.Child3Income = child.TotalIncomeReported;
}
if (childCount == 4)
{
appdata.Child4FirstName = child.FirstName;
appdata.Child4MiddleInitial = child.MiddleInitial;
appdata.Child4LastName = child.LastName;
appdata.Child4Info = string.Join(", ", child.ChildAttributes.Where(a => a.SelectedItem == true).Select(a => a.Name));
appdata.Child4Income = child.TotalIncomeReported;
}
if (childCount == 5)
{
appdata.Child5FirstName = child.FirstName;
appdata.Child5MiddleInitial = child.MiddleInitial;
appdata.Child5LastName = child.LastName;
appdata.Child5Info = string.Join(", ", child.ChildAttributes.Where(a => a.SelectedItem == true).Select(a => a.Name));
appdata.Child5Income = child.TotalIncomeReported;
}
}
int adultCount = 0;
foreach (MemberViewModel adult in appAdults)
{
adultCount++;
if (adultCount == 1)
{
appdata.Adult1FirstName = adult.FirstName;
appdata.Adult1MiddleInitial = adult.MiddleInitial;
appdata.Adult1LastName = adult.LastName;
appdata.Adult1Income = adult.TotalIncomeReported;
}
if (adultCount == 2)
{
appdata.Adult2FirstName = adult.FirstName;
appdata.Adult2MiddleInitial = adult.MiddleInitial;
appdata.Adult2LastName = adult.LastName;
appdata.Adult2Income = adult.TotalIncomeReported;
}
if (adultCount == 3)
{
appdata.Adult3FirstName = adult.FirstName;
appdata.Adult3MiddleInitial = adult.MiddleInitial;
appdata.Adult3LastName = adult.LastName;
appdata.Adult3Income = adult.TotalIncomeReported;
}
if (adultCount == 4)
{
appdata.Adult4FirstName = adult.FirstName;
appdata.Adult4MiddleInitial = adult.MiddleInitial;
appdata.Adult4LastName = adult.LastName;
appdata.Adult4Income = adult.TotalIncomeReported;
}
if (adultCount == 5)
{
appdata.Adult5FirstName = adult.FirstName;
appdata.Adult5MiddleInitial = adult.MiddleInitial;
appdata.Adult5LastName = adult.LastName;
appdata.Adult5Income = adult.TotalIncomeReported;
}
}
}
return apps;
}
public AppViewModel CompletedAppMembers(Guid applicationGuid) {
List<AppMembersViewModel> avm = new List<AppMembersViewModel>();
List<MemberViewModel> mvm = new List<MemberViewModel>();
using (SlpContext context = new SlpContext())
{
mvm =
context.Members.Where(a => a.SlpApplication.CompletedDate != null).Select(b =>
new MemberViewModel
{
AppId = b.SlpApplicationId,
FirstName = b.FirstName,
MiddleInitial = b.MiddleInitial,
LastName = b.LastName,
IsChild = b.IsChild,
ApplicationCompletedDate = b.SlpApplication.CompletedDate
}).ToList();
}
foreach (Guid appId in mvm.Select(a => a.AppId).Distinct())
{
List<MemberViewModel> appMembers = mvm.Where(a => a.AppId == appId).ToList();
avm.Add(
new AppMembersViewModel
{
AppId = appId,
CompletedDate = appMembers.FirstOrDefault().ApplicationCompletedDate.ToString(),
CompletedDateTime = appMembers.FirstOrDefault().ApplicationCompletedDate.Value,
Adults = string.Join(", ", appMembers.Where(a => a.IsChild == false).Select(b => b.DisplayName)),
Children = string.Join(", ", appMembers.Where(a => a.IsChild == true).Select(b => b.DisplayName))
}
);
}
AppViewModel appVM = new AppViewModel();
AppOrchestrator appOrc = new AppOrchestrator();
appVM = appOrc.GetAppViewModel(applicationGuid); //init objects with random guid
appVM.AppMembers = avm.OrderByDescending(a => a.CompletedDateTime).ToList(); //members for report
return appVM;
}
public List<MemberViewModel> MemberSave(MemberViewModel mvm)
{
TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;
List<MemberViewModel> cvmList = new List<MemberViewModel>();
using (SlpContext context = new SlpContext())
{
SlpApplication slpApp = context.SlpApplication.Find(mvm.AppId);
if (slpApp == null)
{
context.SlpApplication.Add(new SlpApplication { Id = mvm.AppId, CreateDate = DateTime.Now });
}
if (mvm.Id == new Guid()) //add child
{
mvm.Id = Guid.NewGuid();
context.Members.Add(new Member {
Id = mvm.Id,
SlpApplicationId = mvm.AppId,
FirstName = textInfo.ToTitleCase(mvm.FirstName.ToLower()),
LastName = textInfo.ToTitleCase(mvm.LastName.ToLower()),
MiddleInitial = mvm.MiddleInitial == "" || mvm.MiddleInitial == null ? "" : mvm.MiddleInitial.ToUpper(),
IsChild = mvm.IsChild,
CreatedDate = DateTime.Now });
}
else //update child
{
Member child = context.Members.Where(a => a.Id == mvm.Id).FirstOrDefault();
child.FirstName = textInfo.ToTitleCase(mvm.FirstName.ToLower());
child.LastName = textInfo.ToTitleCase(mvm.LastName.ToLower());
child.MiddleInitial = mvm.MiddleInitial == "" || mvm.MiddleInitial == null ? "" : mvm.MiddleInitial.ToUpper();
}
if (mvm.IsChild)
{
context.MemberChildAttributes.RemoveRange(context.MemberChildAttributes.Where(a => a.MemberId == mvm.Id));
foreach (NameValueViewModel vm in mvm.ChildAttributes)
{
context.MemberChildAttributes.Add(new MemberChildAttribute { MemberId = mvm.Id, AttributeTypeId = vm.Value, IsSelected = vm.SelectedItem });
}
}
context.SaveChanges();
}
cvmList = GetMembers(mvm.AppId).Where(a => a.IsChild == mvm.IsChild).ToList();
return cvmList;
}
public List<MemberViewModel> SaveMemberIncome(IncomeViewModel ivm)
{
Member member = null;
using (SlpContext context = new SlpContext())
{
member = context.Members.Find(ivm.MemberId);
context.MemberIncomeResponses.RemoveRange(context.MemberIncomeResponses.Where(a => a.MemberId == ivm.MemberId));
foreach (IncomeResponseViewModel irm in ivm.IncomeResponses)
{
List<MemberIncomeResponseDetail> details = new List<MemberIncomeResponseDetail>();
if (irm.ResponseDetails.Count > 0)
{
details = irm.ResponseDetails.Select(a => new MemberIncomeResponseDetail { Amount = a.Amount.Value, CreatedDate = DateTime.Now, FrequencyId = a.FrequencyId.Value }).ToList();
}
context.MemberIncomeResponses.Add(new MemberIncomeResponse {
IncomeQuestionId = irm.IncomeQuestionId,
MemberId = ivm.MemberId,
Response = irm.Response.Value,
IncomeResponseDetails = details
});
}
context.SaveChanges();
}
return GetMembers(member.SlpApplicationId).Where(a => a.IsChild == member.IsChild).ToList();
}
public IncomeViewModel GetMemberIncome(Guid memberId)
{
IncomeViewModel ivm = new IncomeViewModel();
using (SlpContext context = new SlpContext())
{
Member member = context.Members.Find(memberId);
ivm.MemberId = memberId;
ivm.MemberName = member.FirstName + (member.MiddleInitial == null || member.MiddleInitial == string.Empty ? " " : " " + member.MiddleInitial + " ") + member.LastName;
ivm.IsChild = member.IsChild;
ivm.IncomeResponses = member.MemberIncomeResponses.Select(a =>
new IncomeResponseViewModel {
IncomeQuestionId = a.IncomeQuestionId,
QuestionText = a.IncomeQuestion.Text,
QuestionType = a.IncomeQuestion.Type,
Response = a.Response,
ResponseId = a.Id,
ResponseDetails = a.IncomeResponseDetails.ToList().OrderBy(s=>s.CreatedDate).Select(z=> new IncomeResponseDetailViewModel { Amount= z.Amount, DetailError="", FrequencyId=z.FrequencyId } ).ToList()
}).ToList();
if (ivm.IncomeResponses.Count == 0)
{
ivm.IncomeResponses = context.IncomeQuestions.Where(a=>a.IsChild == member.IsChild && a.Visible).Select(a =>
new IncomeResponseViewModel {
IncomeQuestionId = a.Id,
QuestionText = a.Text,
QuestionType = a.Type }).ToList();
}
}
ivm.IncomeResponses = ivm.IncomeResponses.OrderBy(a => a.IncomeQuestionId).ToList();
return ivm;
}
public void SaveContactInformation(ContactViewModel cvm)
{
using (SlpContext context = new SlpContext())
{
SlpApplication dbApp = context.SlpApplication.Find(cvm.AppId);
dbApp.StreetAddress = cvm.StreetAddress;
dbApp.AptNo = cvm.AptNo;
dbApp.City = cvm.City;
dbApp.State = cvm.State;
dbApp.Zip = cvm.Zip;
dbApp.Email = cvm.Email;
dbApp.Phone = cvm.Phone;
dbApp.AdultFilledByName = cvm.AdultFilledByName == null || cvm.AdultFilledByName == "" ? "" : cvm.AdultFilledByName.ToUpper();
dbApp.EthnicityId = cvm.EthnicityId;
dbApp.CompletedDate = DateTime.Now;
dbApp.Steps = string.Join(",", cvm.StepsRequired);
if (!cvm.StepsRequired.Contains(2)) //if 2 not present, remove data for step 2
{
dbApp.AssistanceProgramId = null;
dbApp.AssistanceProgramCaseNumber = "";
}
if (!cvm.StepsRequired.Contains(3)) //if 3 not present remove data from step 3, all adults, all income records, total members, ssn field and no ssn field
{
dbApp.TotalMembers = null;
dbApp.MemberLastFourSSN = null;
dbApp.NoSSN = false;
context.Members.RemoveRange(context.Members.Where(a=>a.SlpApplicationId == cvm.AppId && a.IsChild == false)); //remove adults
//remove all income responses and income details
List<Guid> allmembers = context.Members.Where(a => a.SlpApplicationId == cvm.AppId).Select(a => a.Id).ToList();
context.MemberIncomeResponses.RemoveRange(context.MemberIncomeResponses.Where(a=> allmembers.Contains(a.MemberId)));
}
context.SlpApplicationRace.RemoveRange(context.SlpApplicationRace.Where(a => a.SlpApplicationId == cvm.AppId));
foreach (NameValueViewModel selectedRace in cvm.Races.Where(a=>a.SelectedItem))
{
context.SlpApplicationRace.Add(new SlpApplicationRace { SlpApplicationId = cvm.AppId, RaceId = selectedRace.Value });
}
context.SaveChanges();
}
}
public void SaveStep3(Step3ViewModel svm)
{
using (SlpContext context = new SlpContext())
{
SlpApplication dbApp = context.SlpApplication.Find(svm.AppId);
dbApp.TotalMembers = svm.TotalMembers;
dbApp.MemberLastFourSSN = svm.MemberLastFourSSN;
dbApp.NoSSN = svm.NoSSN;
context.SaveChanges();
}
}
public void SaveAssistanceProgram(AssistanceProgramViewModel apvm)
{
using (SlpContext context = new SlpContext())
{
SlpApplication dbApp = context.SlpApplication.Find(apvm.AppId);
dbApp.AssistanceProgramId = apvm.Id; // apvm.Id == null || apvm.Id == ? null : apvm.Id;
dbApp.AssistanceProgramCaseNumber = apvm.Id == null || apvm.Id == (int)AssistanceProgramsEnum.None ? "" : apvm.CaseNumber;
context.SaveChanges();
}
}
public List<MemberViewModel> GetMembers(Guid? appId)
{
List<MemberViewModel> list = new List<MemberViewModel>();
using (SlpContext context = new SlpContext())
{
//get names
string membersQuery = "Select m.Id, m.SlpApplicationId AppId, m.createdDate,m.FirstName,m.MiddleInitial, m.lastName,m.IsChild, case when count(mi.memberId) = 0 then cast(0 as bit) else cast(1 as bit) end IsIncomeCompleted";
membersQuery = membersQuery + " from Members m left outer join MemberIncomeResponses mi on m.id = mi.memberid";
if (appId.HasValue)
{
membersQuery = membersQuery + " where SlpApplicationId = {0}";
}
membersQuery = membersQuery + " group by m.Id, m.SlpApplicationId, m.createdDate, m.FirstName, m.MiddleInitial, m.lastName, m.IsChild ";
if (appId.HasValue)
{
list = context.Database.SqlQuery<MemberViewModel>(membersQuery, new object[] { appId }).ToList();
}
else {
list = context.Database.SqlQuery<MemberViewModel>(membersQuery).ToList();
}
//get child attributes
List<ChildAttributeViewModel> childAttributes = new List<ChildAttributeViewModel>();
string childAttributeQuery = "Select mca.memberId,mca.AttributeTypeId,mca.IsSelected ,cat.name AttributeName ";
childAttributeQuery = childAttributeQuery + " from Members m, MemberChildAttributes mca, ChildAttributeTypes cat ";
childAttributeQuery = childAttributeQuery + " where m.id = mca.memberId and mca.AttributeTypeId = cat.id ";
if (appId.HasValue)
{
childAttributeQuery = childAttributeQuery + " and SlpApplicationId = {0}";
childAttributes = context.Database.SqlQuery<ChildAttributeViewModel>(childAttributeQuery, new object[] { appId }).ToList();
}
else {
childAttributes = context.Database.SqlQuery<ChildAttributeViewModel>(childAttributeQuery).ToList();
}
List<IncomeSummaryViewModel> incomeGroups = new List<IncomeSummaryViewModel>();
string incomeQuery = "select memberId, Name FrequencyName, sum(amount)amount,fr.id FrequencyId ";
incomeQuery = incomeQuery + " from Members m, MemberIncomeResponses mir, MemberIncomeResponseDetails mird, Frequencies fr ";
incomeQuery = incomeQuery + " where m.id = mir.memberId and mir.Id = mird.memberincomeresponseId and mird.frequencyId = fr.id ";
if (appId.HasValue)
{
incomeQuery = incomeQuery + " and SlpApplicationId = {0}";
}
incomeQuery = incomeQuery + " group by memberId, Name,fr.id order by memberId,fr.id";
if (appId.HasValue)
{
incomeGroups = context.Database.SqlQuery<IncomeSummaryViewModel>(incomeQuery, new object[] { appId }).ToList();
}
else {
incomeGroups = context.Database.SqlQuery<IncomeSummaryViewModel>(incomeQuery).ToList();
}
foreach (MemberViewModel member in list)
{
member.ChildAttributes = childAttributes.Where(a => a.MemberId == member.Id).Select(z=> new NameValueViewModel { Name=z.AttributeName, SelectedItem=z.IsSelected, Value=z.AttributeTypeId }).ToList();
member.TotalIncomeReported = string.Join(", ", incomeGroups.Where(a => a.MemberId == member.Id).Select(z => z.Amount.ToString("c") + "/" + z.FrequencyName));
if (member.TotalIncomeReported == "")
{
member.TotalIncomeReported = "$ 0.00";
}
}
}
return list.OrderBy(a => a.CreatedDate).ToList();
}
public List<MemberViewModel> MemberDelete(MemberViewModel mvm)
{
List<MemberViewModel> cvmList = new List<MemberViewModel>();
using (SlpContext context = new SlpContext())
{
Member member = context.Members.Find(mvm.Id);
context.Members.Remove(member);
context.SaveChanges();
}
cvmList = GetMembers(mvm.AppId).Where(a => a.IsChild == mvm.IsChild).ToList();
return cvmList;
}