/// <summary>
/// Return a list of Sample addresses using LINQ to SQL
/// </summary>
/// <returns></returns>
public List<sp_Vol_Address_DM> ListAddresses()
{
List<sp_Vol_Address_DM> list = new List<sp_Vol_Address_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
list = (from result in context.sp_Vol_Address_Select(null)
select new sp_Vol_Address_DM
{
AddrID = result.AddrID,
ActiveFlg = result.ActiveFlg,
AddrLine1 = result.AddrLine1,
AddrLine2 = result.AddrLine2,
AddrLine3 = result.AddrLine3,
City = result.City,
St = result.St,
Zip = result.Zip,
Zip4 = result.Zip4
}).ToList();
} // Guaranteed to close the Connection
}
catch (Exception ex)
{
throw (ex);
}
return list;
}
/// <summary>
/// DeleteAddressContext - Will do a soft delete (make inactive) by AddrID
/// </summary>
/// <param name="_cAddress"></param>
public void DeleteAddressContext(sp_Vol_Address_DM _cAddress, sp_Vol_Addr_DM _cVolAddr)
{
using (VolTeerEntities context = new VolTeerEntities())
{
try
{
var AddrToRemove = (from n in context.tblVolAddrs where n.AddrID == _cVolAddr.AddrID select n).FirstOrDefault();
if (AddrToRemove != null)
context.tblVolAddrs.Remove(AddrToRemove);
context.SaveChanges();
var AddressToRemove = (from n in context.tblVolAddresses where n.AddrID == _cAddress.AddrID select n).FirstOrDefault();
//context.tblVolAddresses.Remove(AddressToRemove);
AddressToRemove.ActiveFlg = false;
if (AddressToRemove != null)
context.sp_Vol_Address_Update(AddressToRemove.AddrID, AddressToRemove.ActiveFlg, AddressToRemove.AddrLine1,
AddressToRemove.AddrLine2, AddressToRemove.AddrLine3, AddressToRemove.City, AddressToRemove.St, AddressToRemove.Zip,
AddressToRemove.Zip4, AddressToRemove.GeoCodeGetSet);
context.SaveChanges();
}
catch (Exception ex)
{
throw (ex);
}
}
}
/// <summary>
/// Returns all or a phone number for the given volunteer
/// </summary>
/// <returns></returns>
public List<sp_Phone_DM> ListPhones(sp_Phone_DM cVolPhone)
{
List<sp_Phone_DM> list = new List<sp_Phone_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
list = (from result in context.sp_Vol_Phone_Select(cVolPhone.VolID, cVolPhone.PhoneID, null)
select new sp_Phone_DM
{
PhoneID = result.PhoneID,
PhoneNbr = result.PhoneNbr,
VolID = result.VolID,
ActiveFlg = result.ActiveFlg,
PrimaryFlg = result.PrimaryFlg
}).ToList();
} // Guaranteed to close the Connection
}
catch (Exception ex)
{
throw (ex);
}
return list;
}
public void DeleteSampleAddress(int addrID)
{
using (VolTeerEntities context = new VolTeerEntities())
{
context.sp_Sample_Address_Delete(addrID);
}
}
public List<sp_Availablity_DM> ListVolunteerAvailability(Guid? VolID)
{
List<sp_Availablity_DM> list = new List<sp_Availablity_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
list = (from result in context.sp_Availability_Select(VolID)
select new sp_Availablity_DM
{
VolID = result.VolID,
AddrID = result.AddrID,
AvailDateID = result.AvailDateID,
AvailStartDate = result.AvailStartDate,
AvailEndDate = result.AvailEndDate,
DayID = result.DayID,
StartTime = result.StartTime,
EndTime = result.EndTime
}).ToList();
} // Guaranteed to close the Connection
}
catch (Exception ex)
{
throw (ex);
}
return list;
}
/// <summary>
/// Return a list of Sample addresses using LINQ to SQL
/// </summary>
/// <returns></returns>
public List<sp_Volunteer_DM> ListVolunteers()
{
List<sp_Volunteer_DM> list = new List<sp_Volunteer_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
list = (from result in context.sp_Volunteer_Select(null)
select new sp_Volunteer_DM
{
VolFirstName = result.VolFirstName,
VolID = result.VolID,
VolMiddleName = result.VolMiddleName,
VolLastName = result.VolLastName,
ActiveFlg = result.ActiveFlg
}).ToList();
} // Guaranteed to close the Connection
}
catch (Exception ex)
{
throw (ex);
}
return list;
}
public List<sp_EventRating_DM> ListEventRatings(int? RatingID)
{
List<sp_EventRating_DM> list = new List<sp_EventRating_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
list = (from result in context.sp_EventRating_Select(RatingID,null)
select new sp_EventRating_DM
{
RatingID = result.RatingID,
EventID = result.EventID,
VolID = result.VolID,
RatingValue = result.RatingValue,
ActiveFlg = result.ActiveFlg
}).ToList();
} // Guaranteed to close the Connection
}
catch (Exception ex)
{
throw (ex);
}
return list;
}
public List<sp_Volunteer_DM> ListGroupFindVols(sp_Group_DM Group)
{
List<sp_Volunteer_DM> list = new List<sp_Volunteer_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
list = (from result in context.sp_GroupVol_Select_FindNewVols(Group.GroupID)
select new sp_Volunteer_DM
{
ActiveFlg = result.VolActive,
VolFirstName = result.VolFirstName,
VolMiddleName = result.VolMiddleName,
VolLastName = result.VolLastName,
VolID = result.VolID
}).ToList();
} // Guaranteed to close the Connection
}
catch (Exception ex)
{
throw (ex);
}
return list;
}
/// <summary>
/// Return a list of Sample projects using LINQ to SQL
/// </summary>
/// <returns></returns>
public List<sp_Project_DM> ListProjects()
{
List<sp_Project_DM> list = new List<sp_Project_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
list = (from result in context.sp_Project_Select(null)
select new sp_Project_DM
{
ProjectID = result.ProjectID,
ProjectName = result.ProjectName,
ProjectDesc = result.ProjectDesc,
AddrID = result.AddrID,
ActiveFlg = result.ActiveFlg
}).ToList();
} // Guaranteed to close the Connection
}
catch (Exception ex)
{
throw (ex);
}
return list;
}
public void InsertVolunteerAvailability(ISchedulerInfo shedulerInfo, Appointment appointmentToInsert, sp_Availablity_DM cAvail)
{
using (VolTeerEntities context = new VolTeerEntities())
{
try
{
var cVolAvail = new tblAvailability
{
VolID = cAvail.VolID,
AddrID = cAvail.AddrID,
AvailStart = appointmentToInsert.Start,
AvailEnd = appointmentToInsert.End,
Description = appointmentToInsert.Description,
RecurrenceParentID = (int?)(appointmentToInsert.RecurrenceParentID),
RecurrenceRule = appointmentToInsert.RecurrenceRule,
Subject = appointmentToInsert.Subject
};
context.tblAvailabilities.Add(cVolAvail);
context.SaveChanges();
}
catch (Exception ex)
{
}
}
}
/// <summary>
/// InsertVolunteerContext - Will insert a record into Volunteer table via SProc
/// </summary>
/// <param name="_cVolunteer"></param>
public sp_Volunteer_DM InsertVolunteerContext(ref sp_Volunteer_DM _cVolunteer)
{
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
var cVolunteer = new tblVolunteer
{
VolID = _cVolunteer.VolID,
VolFirstName = _cVolunteer.VolFirstName,
VolMiddleName = _cVolunteer.VolMiddleName,
VolLastName = _cVolunteer.VolLastName,
ActiveFlg = _cVolunteer.ActiveFlg
};
context.tblVolunteers.Add(cVolunteer);
context.SaveChanges();
// pass VolID back to BLL
_cVolunteer.VolID = cVolunteer.VolID;
return _cVolunteer;
}
}
catch (Exception ex)
{
throw (ex);
}
}
public List<sp_Skill_DM> ListSkills(Guid? Skill)
{
List<sp_Skill_DM> list = new List<sp_Skill_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
list = (from result in context.sp_Skill_Select(Skill)
select new sp_Skill_DM
{
SkillID = result.SkillID,
SkillName = result.SkillName,
MstrSkillID = result.MstrSkillID,
ActiveFlg = result.ActiveFlg,
ReqCert = result.ReqCert
}).ToList();
} // Guaranteed to close the Connection
}
catch (Exception ex)
{
throw (ex);
}
return list;
}
public sp_Group_DM ListGroups(int? groupID)
{
List<sp_Group_DM> list = new List<sp_Group_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
list = (from result in context.sp_Group_Select(groupID)
select new sp_Group_DM
{
GroupID = result.GroupID,
GroupName = result.GroupName,
ParticipationLevelID = result.ParticipationLevelID,
ActiveFlg = result.ActiveFlg,
ShortDesc = result.ShortDesc,
LongDesc = result.LongDesc
}).ToList();
} // Guaranteed to close the Connection
}
catch (Exception ex)
{
throw (ex);
}
return list.FirstOrDefault();
//return list.FirstOrDefault();
}
public List<sp_ProjectEvent_DM> ListEvents(Guid? EventID)
{
List<sp_ProjectEvent_DM> list = new List<sp_ProjectEvent_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
list = (from result in context.sp_ProjectEvent_Select(EventID)
select new sp_ProjectEvent_DM
{
EventID = result.EventID,
ProjectID = result.ProjectID,
StartDateTime = result.StartDateTime,
EndDateTime = result.EndDateTime,
AddrID = result.AddrID
}).ToList();
} // Guaranteed to close the Connection
}
catch (Exception ex)
{
throw (ex);
}
return list;
}
/// <summary>
/// Return a list of Sample addresses using LINQ to SQL
/// </summary>
/// <returns></returns>
public List<sp_Email_DM> ListEmails()
{
List<sp_Email_DM> list = new List<sp_Email_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
list = (from result in context.sp_Vol_Email_Select(null)
select new sp_Email_DM
{
EmailID = result.EmailID,
EmailAddr = result.EmailAddr,
VolID = result.VolID,
ActiveFlg = result.ActiveFlg
}).ToList();
} // Guaranteed to close the Connection
}
catch (Exception ex)
{
throw (ex);
}
return list;
}
public IEnumerable<Appointment> ListVolunteerAvailability(Guid? VolID, int AddrID)
{
List<Appointment> apts = new List<Appointment>();
List<sp_Availablity_DM> listAvailability = new List<sp_Availablity_DM>();
try
{
using (VolTeerEntities context = new VolTeerEntities())
{
listAvailability = (from result in context.sp_Availability_Select(VolID, AddrID)
select new sp_Availablity_DM
{
VolID = result.VolID,
AddrID = result.AddrID,
AvailID = result.AvailID,
AvailStart = result.AvailStart,
AvailEnd = result.AvailEnd,
Reminder = result.Reminder,
Annotations = result.Annotations,
Description = result.Description,
RecurrenceParentID = result.RecurrenceParentID,
Subject = result.Subject,
RecurrenceRule = result.RecurrenceRule
//Start = result.AvailStart,
//End = result.AvailEnd,
//Description = result.Description,
//RecurrenceParentID = result.RecurrenceParentID,
//RecurrenceRule = result.RecurrenceRule,
//Subject = result.Subject
}).ToList();
foreach (sp_Availablity_DM cAvailability in listAvailability)
{
Appointment apt = new Appointment();
apt.Start = cAvailability.AvailStart;
apt.End = cAvailability.AvailEnd;
apt.Description = cAvailability.Description;
apt.RecurrenceParentID = cAvailability.RecurrenceParentID;
apt.RecurrenceRule = cAvailability.RecurrenceRule;
apt.Subject = cAvailability.Subject;
apt.Attributes.Add("VolID", cAvailability.VolID.ToString());
apt.Attributes.Add("AddrID", cAvailability.AddrID.ToString());
apt.Attributes.Add("AvailID", cAvailability.AvailID.ToString());
apts.Add(apt);
}
return apts;
}
}
catch (Exception ex)
{
throw (ex);
}
return apts;
}
public void DeleteGroup(int GroupID_IN, bool ActiveFlg_IN)
{
using (VolTeerEntities context = new VolTeerEntities())
{
context.sp_Group_Delete(GroupID_IN,ActiveFlg_IN);
}
}
public void InsertSampleAddress(string addrLine1, string addrLine2, string addrLine3, string city, string st, Nullable<int> zip, Nullable<int> zip4, Nullable<bool> activeFlg)
{
using (VolTeerEntities context = new VolTeerEntities())
{
context.sp_Sample_Address_Insert(addrLine1, addrLine2, addrLine3, city, st, zip, zip4, activeFlg);
}
}
public void InsertVolSkill(Guid? VolID, Guid? SkillID)
{
using (VolTeerEntities context = new VolTeerEntities())
{
context.Database.ExecuteSqlCommand("vol.sp_VolSkill_Insert {0}, {1}", VolID, SkillID);
}
}
public void DeleteVolSkillALL(Guid? VolID)
{
using (VolTeerEntities context = new VolTeerEntities())
{
context.Database.ExecuteSqlCommand("vol.sp_VolSkill_DeleteAll {0}", VolID);
}
}
public void UpdateGroup(int GroupID_IN, string GroupName_IN, int participationLevelID_IN)
{
using (VolTeerEntities context = new VolTeerEntities())
{
context.sp_Group_Update(GroupID_IN, GroupName_IN, participationLevelID_IN);
}
}
public void DeleteAddressContext(sp_VendorAddr_DM InputAddress)
{
using (VolTeerEntities context = new VolTeerEntities())
{
var VendorAddrsToRemove = (from n in context.tblVendorAddrs where n.VendorID == InputAddress.VendorID select n).FirstOrDefault();
context.tblVendorAddrs.Remove(VendorAddrsToRemove);
context.SaveChanges();
}
}
public void DeleteContactEmailContext(sp_ContactEmail_DM contactemail)
{
using (VolTeerEntities context = new VolTeerEntities())
{
var ContactEmailtoRemove = (from n in context.tblContactEmails where n.ContactID == contactemail.ContactID & n.EmailID == contactemail.EmailID select n).FirstOrDefault();
context.tblContactEmails.Remove(ContactEmailtoRemove);
context.SaveChanges();
}
}
public void DeleteProjectEventContext(sp_ProjectEvent_DM InputProjectEvent)
{
using (VolTeerEntities context = new VolTeerEntities())
{
var ProjectEventToRemove = (from n in context.tblProjectEvents where n.EventID == InputProjectEvent.EventID select n).FirstOrDefault();
context.tblProjectEvents.Remove(ProjectEventToRemove);
context.SaveChanges();
}
}
public void DeleteEmailContext(sp_VendEmail_DM InputEmail)
{
using (VolTeerEntities context = new VolTeerEntities())
{
var VendEmailToRemove = (from n in context.tblVendEmails where n.EmailID == InputEmail.EmailID select n).FirstOrDefault();
context.tblVendEmails.Remove(VendEmailToRemove);
context.SaveChanges();
}
}
public void DeleteVendContactContext(sp_VendContact_DM vendcontact)
{
using (VolTeerEntities context = new VolTeerEntities())
{
var VendContacttoRemove = (from n in context.tblVendContacts where n.ContactID == vendcontact.ContactID & n.VendorID == vendcontact.VendorID select n).FirstOrDefault();
context.tblVendContacts.Remove(VendContacttoRemove);
context.SaveChanges();
}
}
/// <summary>
/// DeleteAddressContext - Will do a soft delete (make inactive) by AddrID
/// </summary>
/// <param name="_cAddress"></param>
public void DeleteAddressContext(sp_Vol_Address_DM _cAddress)
{
using (VolTeerEntities context = new VolTeerEntities())
{
var AddressToRemove = (from n in context.tblVolAddresses where n.AddrID == _cAddress.AddrID select n).FirstOrDefault();
context.tblVolAddresses.Remove(AddressToRemove);
context.SaveChanges();
}
}
/// <summary>
/// DeleteVolunteerContext - Will do a soft delete (make inactive) by VolID
/// </summary>
/// <param name="_cVolunteer"></param>
public void DeleteProjectContext(sp_Project_DM _cProject)
{
using (VolTeerEntities context = new VolTeerEntities())
{
var ProjectToRemove = (from n in context.tblProjects where n.ProjectID == _cProject.ProjectID select n).FirstOrDefault();
context.tblProjects.Remove(ProjectToRemove);
context.SaveChanges();
}
}
/// <summary>
/// DeleteEmailsContext - Will do a soft delete (make inactive) by EmailID
/// </summary>
/// <param name="_cVolunteer"></param>
public void DeleteEmailsContext(sp_Email_DM _cEmail)
{
using (VolTeerEntities context = new VolTeerEntities())
{
var EmailsToRemove = (from n in context.tblVolEmails where n.EmailID == _cEmail.EmailID select n).FirstOrDefault();
context.tblVolEmails.Remove(EmailsToRemove);
context.SaveChanges();
}
}
public void DeleteEventRatingContext(sp_EventRating_DM InputRating)
{
using (VolTeerEntities context = new VolTeerEntities())
{
var RatingToRemove = (from n in context.tblEventRatings where n.RatingID == InputRating.RatingID select n).FirstOrDefault();
context.tblEventRatings.Remove(RatingToRemove);
context.SaveChanges();
}
}
