static List <HouseholdViewModel> SearchHouseholds(string searchString)
{
if (searchString == null)
{
return(null);
}
var householdView = new List <HouseholdViewModel>();
using (var db = new BHelpContext())
{
List <Client> clientList;
if (searchString.Any(char.IsDigit))
{
clientList = db.Clients.Where(c => c.Active && c.Phone.Contains(searchString))
.OrderBy(c => c.LastName).ToList();
}
else
{
clientList = db.Clients.Where(c => c.Active && c.LastName.Contains(searchString))
.OrderBy(c => c.LastName).ToList();
}
foreach (var client in clientList)
{
var household = new HouseholdViewModel()
{
ClientId = client.Id,
FirstName = client.FirstName,
LastName = client.LastName,
StreetNumber = client.StreetNumber,
StreetName = client.StreetName,
StreetToolTip = client.StreetName.Replace(" ", "\u00a0"),
City = client.City,
CityToolTip = client.City.Replace(" ", "\u00a0"),
Zip = client.Zip,
Phone = client.Phone,
PhoneToolTip = client.Phone.Replace(" ", "\u00a0"),
Notes = client.Notes,
// (full length on mouseover) \u00a0 is the Unicode character for NO-BREAK-SPACE.
NotesToolTip = client.Notes.Replace(" ", "\u00a0")
};
var s = household.StreetName; // For display, abbreviate to 10 characters:
s = s.Length <= 10 ? s : s.Substring(0, 10) + "...";
household.StreetName = s;
s = household.City; // For display, abbreviate to 11 characters:
s = s.Length <= 11 ? s : s.Substring(0, 11) + "...";
household.City = s;
s = household.Phone; // For display, abbreviate to 12 characters:
s = s.Length <= 12 ? s : s.Substring(0, 12) + "...";
household.Phone = s;
s = household.Notes; // For display, abbreviate to 12 characters:
s = s.Length <= 12 ? s : s.Substring(0, 12) + "...";
household.Notes = s;
householdView.Add(household);
}
}
return(householdView);
}
public static List <FamilyMember> GetFamilyMembers(int clientId)
{
var familyMembers = new List <FamilyMember>(); // For editiing
using (var db = new BHelpContext())
{
var client = db.Clients.Find(clientId);
var sqlString = "SELECT * FROM FamilyMembers ";
sqlString += "WHERE Active > 0 AND ClientId =" + clientId;
var familyList = db.Database.SqlQuery <FamilyMember>(sqlString).ToList();
if (client != null)
{
FamilyMember headOfHousehold = new FamilyMember()
{
FirstName = client.FirstName,
LastName = client.LastName,
DateOfBirth = client.DateOfBirth,
};
familyList.Add(headOfHousehold);
}
foreach (FamilyMember member in familyList)
{
member.Age = GetAge(member.DateOfBirth, DateTime.Today);
member.NameAge = member.FirstName + " " + member.LastName + "/" + member.Age;
familyMembers.Add(member);
}
}
return(familyMembers);
}
public static List <SelectListItem> GetFamilySelectList(int clientId)
{
List <SelectListItem> householdList = new List <SelectListItem>();
using (var db = new BHelpContext())
{
var client = db.Clients.Find(clientId);
if (client != null)
{
client.FamilyMembers = GetFamilyMembers(clientId);
foreach (var mbr in client.FamilyMembers)
{
var text = mbr.FirstName + " " + mbr.LastName + "/" +
AppRoutines.GetAge(mbr.DateOfBirth, DateTime.Today);
var selListItem = new SelectListItem()
{
Value = mbr.FirstName, Text = text
};
householdList.Add(selListItem);
}
}
return(householdList);
}
}
public static Boolean UploadClients()
{
var db = new BHelpContext();
var filePath = @"c:\TEMP\BH Food Client List-Table 1.csv";
DataTable csvtable = new DataTable();
using (CsvReader csvReader =
new CsvReader(new StreamReader(filePath), true))
{
csvtable.Load(csvReader);
}
foreach (DataRow row in csvtable.Rows)
{
Client client = new Client()
{
Active = true,
LastName = row[0].ToString(),
FirstName = row[1].ToString(),
StreetNumber = row[2].ToString(),
StreetName = row[3].ToString(),
City = row[4].ToString(),
Zip = row[5].ToString(),
Phone = row[6].ToString(),
Notes = row[13].ToString()
};
db.Clients.Add(client);
db.SaveChanges();
//System.Diagnostics.Debug.WriteLine(client.FirstName, client.LastName);
}
return(true);
}
public static Boolean ReverseNames() // Initial load had First & Last Names revesed
{
var db = new BHelpContext();
foreach (Client client in db.Clients.ToList())
{
var oldLastName = client.LastName;
client.LastName = client.FirstName;
client.FirstName = oldLastName;
db.SaveChanges();
}
return(true);
}
public static Boolean UploadAdults()
{
var db = new BHelpContext();
var filePath = @"c:\TEMP\BH Food Client List-Table 1.csv";
DataTable csvtable = new DataTable();
using (CsvReader csvReader = new CsvReader(new StreamReader(filePath), true))
{ csvtable.Load(csvReader); }
int i = 0;
foreach (DataRow row in csvtable.Rows)
{
i++; // Switch for Kids / Adults:
string _adults = row[10].ToString(); // Adults
//string _adults = row[11].ToString(); // Kids
string[] adultsArray = _adults.Split(',');
foreach (var nameAge in adultsArray)
{
if (i > 0) // to reset if errors occured
{
if (nameAge.Contains(row[0].ToString()) && nameAge.Contains(row[1].ToString())) // First and Last Name))
{
//Don't add to FamilyMembers - just capture the age & update the client DoB
int ageIndex = nameAge.IndexOf('/');
if (ageIndex > 0)
{
var years = nameAge.Substring(nameAge.IndexOf('/') + 1);
//System.Diagnostics.Debug.WriteLine(i.ToString() + ' ' + row[0].ToString() + ' ' + row[1].ToString() + ' ' + years);
if (int.TryParse(years, out int yy))
{
// Everybody born on April 1st
DateTime doB = new DateTime(2021 - yy, 4, 1);
var original = db.Clients.Find(i);
if (original != null)
{
original.DateOfBirth = doB;
db.SaveChanges();
}
}
}
}
else // not a client - add a family member =================
{
string nameAgeTrim = nameAge.Trim();
var years = nameAge.Substring(nameAge.IndexOf('/') + 1);
if (int.TryParse(years, out int yy))
{
DateTime doB = new DateTime(2021 - yy, 4, 1);
string fullName = nameAgeTrim.Substring(0, nameAgeTrim.IndexOf('/'));
string firstName;
string lastName;
if (fullName.IndexOf(' ') <= 0)
{
firstName = fullName;
lastName = "";
}
else
{
firstName = fullName.Substring(0, fullName.IndexOf(' '));
lastName = fullName.Substring(fullName.IndexOf(' ') + 1);
}
FamilyMember newAdult = new FamilyMember()
{
Active = true,
ClientId = i,
FirstName = firstName,
LastName = lastName,
DateOfBirth = doB
};
db.FamilyMembers.Add(newAdult);
db.SaveChanges();
}
}
}
}
//System.Diagnostics.Debug.WriteLine(client.FirstName, client.LastName);
}
return(true);
}
