public static object GetNoteGridList(int personId)
{
var context = new MusicManEntities();
return(context.Notes.Where(x => x.PersonID == personId).OrderBy(x => x.Date)
.Select(x => new { x.NoteID, x.Date, x.Note1, x.IsSent }).ToList());
}
/// <summary>Saves, or creates if new this instance of person.</summary>
private void Save()
{
using (var db = new MusicManEntities())
{
var person = PersonKey > 0 ? db.People.FirstOrDefault(x => x.PersonID == PersonKey) : new Person();
if (person == null)
{
return;
}
person.FirstName = txtFirstName.Text;
person.LastName = txtLastName.Text;
person.Email = txtEmail.Text;
person.Phone = txtPhone.Text;
person.InvoiceDay = (int?)numInvoiceDay.Value;
person.IsActive = chkActive.Checked;
person.IsVenmo = chkVenmo.Checked;
person.IsPaypal = chkPaypal.Checked;
person.IsParent = IsParent;
person.Rate = (int?)numRate.Value;
//Create a new person if a key was not passed in when the form was created.
if (PersonKey == 0)
{
db.People.Add(person);
}
db.SaveChanges();
PersonKey = person.PersonID;
}
using (var db = new MusicManEntities())
{
var person = db.People.FirstOrDefault(x => x.PersonID == PersonKey);
if (!IsParent)
{
if (cboParent.SelectedItem is Person selectedParent && ParentKey != selectedParent.PersonID)
{
if (person.Relationships.FirstOrDefault() != null)
{
person.Relationships.FirstOrDefault().ParentID = selectedParent.PersonID;
}
else
{
var relationship = new Relationship {
ChildID = PersonKey, ParentID = selectedParent.PersonID
};
db.Relationships.Add(relationship);
}
}
Enum.TryParse <DayOfWeek>(cboDayOfWeek.SelectedValue.ToString(), out var dayOfWeek);
Schedule.UpdateScheduleFromStudentId(person.PersonID, dayOfWeek, dtTime.Value.TimeOfDay);
}
db.SaveChanges();
}
}
/// <summary>Updates the schedule from student identifier. Creates a
/// new schedule if one isn't found for the current student</summary>
/// <param name="studentId">The student identifier.</param>
/// <param name="dayOfWeek">The day of week.</param>
/// <param name="time">The time.</param>
public static void UpdateScheduleFromStudentId(int studentId, DayOfWeek dayOfWeek, TimeSpan time)
{
using (var db = new MusicManEntities())
{
var newSchedule = false;
var schedule = db.Schedules.FirstOrDefault(x => x.PersonID == studentId);
if (schedule == null)
{
newSchedule = true;
schedule = new Schedule {
PersonID = studentId
};
}
var intDayOfWeek = (int)dayOfWeek;
schedule.DayOfTheWeek = intDayOfWeek;
var timeWoSeconds = new TimeSpan(time.Hours, time.Minutes, 0);
schedule.TimeOfDay = timeWoSeconds;
if (newSchedule)
{
db.Schedules.Add(schedule);
}
db.SaveChanges();
}
}
public static User GetDefaultUser()
{
using (var db = new MusicManEntities())
{
return(db.Users.FirstOrDefault());
}
}
/// <summary>Gets the student grid list.</summary>
/// <returns></returns>
public static object GetStudentGridList()
{
var context = new MusicManEntities();
return(context.People.Where(x => x.IsParent == false).OrderBy(x => x.LastName)
.Select(x => new { x.PersonID, x.LastName, x.FirstName, x.Phone, x.Email, x.Rate, x.IsActive }).ToList());
}
public static User GetUserFromEmail(string email)
{
using (var db = new MusicManEntities())
{
return(db.Users.FirstOrDefault(x => x.Email == email));
}
}
/// <summary>Gets the parent grid list.</summary>
/// <returns></returns>
public static object GetParentGridList()
{
var context = new MusicManEntities();
return(context.People.Where(x => x.IsParent == true).OrderBy(x => x.LastName)
.Select(x => new { x.PersonID, x.LastName, x.FirstName, x.Phone, x.Email, x.IsVenmo, x.IsPaypal }).ToList());
}
public void UpdateUser(string password)
{
using (var db = new MusicManEntities())
{
PasswordHash = SecurePasswordHasher.Hash(password);
db.SaveChanges();
}
}
//Static methods
/// <summary>Gets all parents.</summary>
/// <returns></returns>
public static List <Person> GetActiveStudents()
{
using (var db = new MusicManEntities())
{
var parents = db.People.Where(x => x.IsParent == false);
return(parents.ToList());
}
}
/// <summary>Gets the person from primary key.</summary>
/// <param name="personId">The person identifier.</param>
/// <returns></returns>
public static Person GetPersonFromId(int personId)
{
using (var db = new MusicManEntities())
{
var person = db.People.FirstOrDefault(x => x.PersonID == personId);
return(person);
}
}
/// <summary>
/// <para>
/// Gets the schedule from student foreign key.
/// </para>
/// </summary>
/// <param name="studentId">The student identifier.</param>
/// <returns></returns>
public static Schedule GetScheduleFromStudentId(int studentId)
{
using (var db = new MusicManEntities())
{
var schedule = db.Schedules.FirstOrDefault(x => x.PersonID == studentId);
return(schedule);
}
}
public static List <Note> GetNotesForUser(int personId)
{
using (var db = new MusicManEntities())
{
var notes = db.Notes.Where(x => x.PersonID == personId);
return(notes.ToList());
}
}
public static Note GetNoteFromId(int noteId)
{
using (var db = new MusicManEntities())
{
var note = db.Notes.FirstOrDefault(x => x.NoteID == noteId);
return(note);
}
}
public static void UpdateNote(int noteKey, string noteText)
{
using (var db = new MusicManEntities())
{
var note = db.Notes.FirstOrDefault(x => x.NoteID == noteKey);
note.Note1 = noteText;
db.SaveChanges();
}
}
public static void DeleteNote(int noteKey)
{
using (var db = new MusicManEntities())
{
var note = db.Notes.FirstOrDefault(x => x.NoteID == noteKey);
db.Notes.Attach(note);
db.Notes.Remove(note);
db.SaveChanges();
}
}
public static void CreateNote(int personKey, string noteText)
{
using (var db = new MusicManEntities())
{
var note = new Note {
PersonID = personKey, Note1 = noteText, Date = DateTime.Now, IsSent = false
};
db.Notes.Add(note);
db.SaveChanges();
}
}
/// <summary>Marks the invoice as paid.</summary>
/// <param name="billingId">The billing identifier.</param>
public static void MarkInvoiceAsPaid(short billingId)
{
using (var db = new MusicManEntities())
{
var billingDetail = db.BillingDetails.FirstOrDefault(x => x.BillingDetailID == billingId);
if (billingDetail != null)
{
billingDetail.IsPaid = true;
db.SaveChanges();
}
}
}
/// <summary>Gets the billing entry list.</summary>
/// <param name="parentId">The parent identifier.</param>
/// <param name="start">The start date.</param>
/// <param name="end">The end date.</param>
/// <returns>A list of billing entries related to a given parent, within a certain date range</returns>
public static object GetBillingEntryList(int parentId, DateTime start, DateTime end)
{
using (var db = new MusicManEntities())
{
var billingEntries = from p in db.People
join b in db.BillingDetails on p.PersonID equals b.PersonID
orderby b.BillMonth
where p.PersonID == parentId && b.BillMonth >= start && b.BillMonth <= end
select new { b.BillingDetailID, b.BillMonth, b.Amount, b.IsInvoiced, b.IsPaid };
return(billingEntries.ToList());
}
}
/// <summary>Sets the initial form control values.</summary>
private void SetInitialValues()
{
using (var db = new MusicManEntities())
{
var billingDetail = db.BillingDetails.FirstOrDefault(x => x.BillingDetailID == _billingDetailKey);
if (billingDetail != null)
{
if (billingDetail.BilledDate != null)
{
txtInvoiceDate.Text = billingDetail.BilledDate.Value.ToShortDateString();
}
txtParentName.Text = billingDetail.Person.FullName;
txtTotal.Text = billingDetail.Amount.ToString();
}
}
}
/// <summary>Handles the Click event of the btnSave control. Saves changes to the current billing record.
/// Only the amount can change </summary>
private void btnSave_Click(object sender, EventArgs e)
{
using (var db = new MusicManEntities())
{
var billingDetail = db.BillingDetails.FirstOrDefault(x => x.BillingDetailID == _billingDetailKey);
if (billingDetail == null)
{
return;
}
billingDetail.Amount = Convert.ToDecimal(txtTotal.Text);
db.SaveChanges();
}
Close();
}
/// <summary>Gets the parent from the current student.</summary>
/// <returns></returns>
public Person GetParent()
{
using (var db = new MusicManEntities())
{
var relationship = db.Relationships
.FirstOrDefault(s => s.ChildID == PersonID);
if (relationship != null)
{
var parent = db.People.Find(relationship.ParentID);
return(parent);
}
return(null);
}
}
public static void SendInvoices()
{
using (var db = new MusicManEntities())
{
var unsentInvoices = db.BillingDetails.Where(x => x.IsInvoiced == false);
foreach (var unsentInvoice in unsentInvoices)
{
if (unsentInvoice.Person.InvoiceDay <= DateTime.Today.Day)
{
SendInvoice(unsentInvoice, unsentInvoice.Person.Phone);
}
unsentInvoice.IsInvoiced = true;
}
db.SaveChanges();
}
}
public static void UpdateUser(string email, string companyName, string payPal, string venmo, string twilioSID, string twilioAuthKey, string twilioPhone)
{
using (var db = new MusicManEntities())
{
var user = db.Users.FirstOrDefault(x => x.Email == email);
if (user != null)
{
user.CompanyName = companyName;
user.PayPalEmail = payPal;
user.VenmoUser = venmo;
user.TwilioAccountSid = twilioSID;
user.TwilioAuthToken = twilioAuthKey;
user.TwilioPhoneNumber = twilioPhone;
db.SaveChanges();
}
}
}
/// <summary>Creates the invoices for the current month.</summary>
public static void CreateInvoices()
{
var date = DateTime.Now.AddMonths(-1);
var fromDate = new DateTime(date.Year, date.Month, 1);
var toDate = fromDate.AddMonths(1);
var context = new MusicManEntities();
var parents = context.People.Where(x => x.IsParent == true && x.IsActive == true)
.Select(x => new { x.PersonID }).ToList();
foreach (var parent in parents)
{
var parentId = parent.PersonID;
var billCount = context.BillingDetails
.Count(x => x.PersonID == parentId &&
x.BillMonth >= fromDate && x.BillMonth < toDate);
if (billCount == 0)
{
var total = 0;
using (var db = new MusicManEntities())
{
var studentQuery = from r in db.Relationships
join p in db.People on r.ChildID equals p.PersonID
join s in db.Schedules on p.PersonID equals s.PersonID
where r.ParentID == parentId
select new { p.PersonID, p.Rate, s.DayOfTheWeek, };
var studentView = studentQuery.ToList();
foreach (var student in studentView)
{
var dayOfWeek = (DayOfWeek)student.DayOfTheWeek;
var numLessons = CountDays(dayOfWeek, fromDate, toDate);
total += numLessons * student.Rate.Value;
}
if (total > 0)
{
CreateBillingEntry(parentId, total, fromDate);
}
}
}
}
}
/// <summary>Creates the billing entry.</summary>
/// <param name="parentId">The parent identifier.</param>
/// <param name="total">The total.</param>
private static void CreateBillingEntry(int parentId, int total, DateTime fromDate)
{
using (var db = new MusicManEntities())
{
var bill = new BillingDetail
{
PersonID = parentId,
BilledDate = DateTime.Today,
Amount = total,
IsInvoiced = false,
IsPaid = false,
BillMonth = fromDate
};
db.BillingDetails.Add(bill);
db.SaveChanges();
}
}
public static User CreateUser(string email, string password)
{
using (var db = new MusicManEntities())
{
var user = new User
{
Email = email,
PasswordHash = SecurePasswordHasher.Hash(password)
};
//var hash = SecurePasswordHasher.Hash(password);
db.Users.Add(user);
db.SaveChanges();
// Verify
//var result = SecurePasswordHasher.Verify("mypassword", hash);
return(db.Users.FirstOrDefault());
}
}