public static ReturnStatus GetProjectByNameAndDate(string name, string date)
{
//parse date into datetime
//probably not a good method of handling keys
//seems to work with and without a 24 hour time
DateTime beginDate = DateTime.Parse(date);
//find the record with PK_name+beginDate
//doesn't work with auto incrementing id field
//return db.projects.Find(name, beginDate);
//work around that lets the db save
//return db.projects.Where(x => x.name.Equals(name) && x.beginDate.Equals(beginDate)).Single();
ReturnStatus st = new ReturnStatus();
Project project = new Project();
try
{
VolunteerDbContext db = new VolunteerDbContext();
project = db.projects.Where(x => x.name.Equals(name) && x.beginDate.Equals(beginDate)).Single();
}
catch (Exception e)
{
st.errorCode = (int)ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
st.errorMessage = e.ToString();
st.data = "Could not connect to database. Try again later.";
return(st);
}
st.errorCode = (int)ReturnStatus.ALL_CLEAR;
st.errorMessage = "";
st.data = project;
return(st);
}
/// <summary>
/// Attempts to determine if a user is logged in by fetching all the timesheets by user id and
/// selecting the most recent one. If the most recent timesheet has a clock out time of midnight
/// then the user is still clocked in, otherwise they're clocked out.
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static ReturnStatus GetClockedInUserTimeSheet(int userId)
{
ReturnStatus st = new ReturnStatus();
st.data = new TimeSheet();
try
{
VolunteerDbContext db = new VolunteerDbContext();
var sheet = db.timeSheets.Where(x => x.user_Id == userId).ToList().OrderBy(y => y.clockInTime);
if (sheet.Count() > 0)
{
if (sheet.Last().clockOutTime == DateTime.Today.AddDays(1))
{
st.errorCode = 0;
st.data = sheet.Last();
return(st);
}
}
st.errorCode = 0;
return(st);
}
catch
{
st.errorCode = -1;
return(st);
}
}
/// <summary>
/// Updates the users information based on a new model.
/// </summary>
/// <param name="user">User object with new information.</param>
public static ReturnStatus EditUser(User user)
{
ReturnStatus st = new ReturnStatus();
st.data = null;
try
{
VolunteerDbContext db = new VolunteerDbContext();
user.homePhoneNumber = user.homePhoneNumber.Replace('(', ' ').Replace(')', ' ').Replace('.', ' ').Replace('-', ' ');
user.homePhoneNumber = Regex.Replace(user.homePhoneNumber, @"\s", "");
user.workPhoneNumber = user.workPhoneNumber.Replace('(', ' ').Replace(')', ' ').Replace('.', ' ').Replace('-', ' ');
user.workPhoneNumber = Regex.Replace(user.workPhoneNumber, @"\s", "");
user.emergencyHomePhone = user.homePhoneNumber.Replace('(', ' ').Replace(')', ' ').Replace('.', ' ').Replace('-', ' ');
user.emergencyHomePhone = Regex.Replace(user.homePhoneNumber, @"\s", "");
user.emergencyWorkPhone = user.workPhoneNumber.Replace('(', ' ').Replace(')', ' ').Replace('.', ' ').Replace('-', ' ');
user.emergencyWorkPhone = Regex.Replace(user.workPhoneNumber, @"\s", "");
db.Entry(user).State = EntityState.Modified;
db.SaveChanges();
st.errorCode = ReturnStatus.ALL_CLEAR;
return(st);
}
catch (Exception e)
{
st.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
st.errorMessage = e.ToString();
return(st);
}
}
/// <summary>
/// Deletes the TimeSheet from the database with the matching id.
/// </summary>
/// <param name="id"></param>
public static ReturnStatus DeleteTimeSheetById(int id)
{
ReturnStatus st = new ReturnStatus();
st.data = null;
try
{
VolunteerDbContext db = new VolunteerDbContext();
TimeSheet ts = db.timeSheets.Find(id);
if (ts != null)
{
db.timeSheets.Remove(ts);
db.SaveChanges();
st.errorCode = (int)ReturnStatus.ALL_CLEAR;
return(st);
}
st.errorCode = ReturnStatus.NULL_ARGUMENT;
return(st);
}
catch (Exception e)
{
st.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
st.errorMessage = e.ToString();
return(st);
}
}
/// <summary>
/// Gets the record in the timesheet table by it's natural key: user_id+project_id+clockInTime.
/// </summary>
/// <param name="userId">Id of the user</param>
/// <param name="projectId">Id of the project</param>
/// <param name="clockInTime">MM/DD/YYYY</param>
/// <returns>Timesheet Object</returns>
public static ReturnStatus GetTimeSheetByNaturalKey(int userId, int projectId, string clockInTime)
{
ReturnStatus st = new ReturnStatus();
st.data = new TimeSheet();
try
{
VolunteerDbContext db = new VolunteerDbContext();
DateTime cit = DateTime.Parse(clockInTime);
st.errorCode = ReturnStatus.ALL_CLEAR;
st.data = db.timeSheets.Where(x => x.user_Id == userId && x.project_Id == projectId && x.clockInTime.Equals(cit)).Single();
return(st);
}
catch (InvalidOperationException e)
{
st.errorCode = ReturnStatus.ERROR_WHILE_ACCESSING_DATA;
st.errorMessage = e.ToString();
return(st);
}
catch (Exception e)
{
st.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
st.errorMessage = e.ToString();
return(st);
}
}
/// <summary>
/// Returns whether or not a user's waiver is outdated
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static ReturnStatus waiverNotSigned(int userId)
{
ReturnStatus rs = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
User user = db.users.Find(userId);
if ((DateTime.Now.AddYears(-1) - user.waiverSignDate).TotalDays < (DateTime.Now - DateTime.Now.AddYears(-1)).TotalDays)
{
rs.data = false;
}
else
{
rs.data = true;
}
rs.errorCode = 0;
return(rs);
}
catch
{
rs.errorCode = -1;
rs.data = new List <User>();
return(rs);
}
}
/// <summary>
/// Gets a list of Projects that are possible candidates
/// to add to an HfhEvent. This includes all Projects that
/// are not currently joined to the HfhEvent.
/// </summary>
/// <param name="id">The HfhEvent Id</param>
/// <returns>List of EventAddRemoveProjectVM view models</returns>
public static ReturnStatus GetNotHfhEventProjects(int id)
{
ReturnStatus rs = new ReturnStatus();
string sql = " SELECT " +
" @hfhEventId AS hfhEventId, " +
" P.Id AS projectId, " +
" P.name AS projectName, " +
" CAST(0 AS bit) AS isSelected " +
" FROM Project P " +
" WHERE " +
" P.Id NOT IN( " +
" SELECT " +
" PE.project_Id " +
" FROM " +
" ProjectEvent PE " +
" WHERE " +
" PE.project_Id IS NOT NULL " +
" AND PE.event_Id = @hfhEventId )";
var evId = new SqlParameter("@hfhEventId", id);
try
{
VolunteerDbContext db = new VolunteerDbContext();
var projects = db.Database.SqlQuery <EventAddRemoveProjectVM>(sql, evId).ToList();
rs.errorCode = ReturnStatus.ALL_CLEAR;
rs.data = projects;
}
catch
{
rs.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
}
return(rs);
}
/// <summary>
/// Deletes the user in the database with matching id.
/// </summary>
/// <param name="id"></param>
public static ReturnStatus DeleteUserById(int id)
{
ReturnStatus st = new ReturnStatus();
st.data = null;
try
{
VolunteerDbContext db = new VolunteerDbContext();
User user = db.users.Find(id);
if (user != null)
{
db.users.Remove(user);
db.SaveChanges();
}
else
{
st.errorCode = (int)ReturnStatus.COULD_NOT_UPDATE_DATABASE;
}
return(st);
}
catch (Exception e)
{
st.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
st.errorMessage = e.ToString();
return(st);
}
}
public static ReturnStatus GetOrganizationSQL(string queryFilter, int status)
{
VolunteerDbContext db = new VolunteerDbContext();
ReturnStatus st = new ReturnStatus();
var orgStatus = new SqlParameter("@Status", status);
var orgName = new SqlParameter("@Name", "%" + queryFilter + "%");
var orgs = db.organizations.SqlQuery(
"SELECT * FROM Organization " +
"WHERE Organization.status = @Status " +
"AND Organization.name in " +
"(SELECT Organization.name FROM Organization WHERE Organization.name LIKE @Name)", orgStatus, orgName).OrderByDescending(x => x.status).ToList <Organization>();
if (orgs.Count < 1)
{
List <Organization> orgList = new List <Organization>();
st.data = orgList;
st.errorCode = ReturnStatus.ERROR_WHILE_ACCESSING_DATA;
return(st);
}
st.errorCode = ReturnStatus.ALL_CLEAR;
st.data = orgs;
return(st);
}
/// <summary>
/// Deletes an organization from the database by id.
/// </summary>
/// <param name="id">The id of the organization to delete.</param>
public static ReturnStatus DeleteOrganizationById(int id)
{
ReturnStatus st = new ReturnStatus();
st.data = null;
try
{
VolunteerDbContext db = new VolunteerDbContext();
Organization org = db.organizations.Find(id);
if (org != null)
{
db.organizations.Remove(org);
db.SaveChanges();
st.errorCode = ReturnStatus.ALL_CLEAR;
return(st);
}
st.errorCode = ReturnStatus.COULD_NOT_UPDATE_DATABASE;
return(st);
}
catch (Exception e)
{
st.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
st.errorMessage = e.ToString();
return(st);
}
}
public static ReturnStatus GetAllCategoriesByPageSize(int page, int recordsPerPage, ref int totalCount)
{
ReturnStatus st = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
var categories = (from cat in db.projectCategories
orderby cat.categoryType ascending
select cat)
.Skip(page * recordsPerPage)
.Take(recordsPerPage).ToList();
totalCount = db.projectCategories.Count();
st.data = categories;
st.errorCode = ReturnStatus.ALL_CLEAR;
}
catch (Exception e)
{
st.errorCode = ReturnStatus.ERROR_WHILE_ACCESSING_DATA;
st.errorMessage = e.ToString();
st.data = new List <ProjectCategory>();
}
return(st);
}
public static ReturnStatus GetNumBadPunches()
{
ReturnStatus st = new ReturnStatus();
try
{
DateTime today = DateTime.Today;
DateTime aMonthAgo = DateTime.Today.AddDays(-30);
VolunteerDbContext db = new VolunteerDbContext();
var numsheets = db.timeSheets.Where(
t => t.clockInTime <today &&
t.clockInTime> aMonthAgo &&
t.clockOutTime.Hour == 0 &&
t.clockOutTime.Minute == 0).Count();
st.errorCode = ReturnStatus.ALL_CLEAR;
st.data = numsheets;
return(st);
}
catch (Exception e)
{
st.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
st.errorMessage = e.ToString();
return(st);
}
}
/// <summary>
/// Get a single event by id.
/// </summary>
/// <param name="id"></param>
/// <returns>A single event object with a matching id otherwise null.</returns>
public static ReturnStatus GetHfhEventById(int id)
{
ReturnStatus st = new ReturnStatus();
st.data = new HfhEvent();
if (id < 1)
{
return(new ReturnStatus()
{
errorCode = -1, data = null
});
}
try
{
VolunteerDbContext db = new VolunteerDbContext();
st.data = db.hfhEvents.Find(id);
st.errorCode = ReturnStatus.ALL_CLEAR;
return(st);
}
catch (Exception e)
{
st.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
st.errorMessage = e.ToString();
return(st);
}
}
public static ReturnStatus Get12WeeksTimeSheetsByCategory(List <int> restoreIds, List <int> abwkIds, List <int> homeIds)
{
ReturnStatus rs = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
DateTime startRange;
DateTime endRange;
DateTime today = DateTime.Today;
int thisYear = DateTime.Today.Year;
int thisMonth = DateTime.Today.Month;
List <TimeSheet>[] timesheetInception = new List <TimeSheet> [36];
int j = 11, k = 11, l = 11;
for (int i = 0; i < 36; i++)
{
if (i < 12)
{
startRange = today.AddDays(-7 * j);
endRange = today.AddDays(-7 * (j - 1));
timesheetInception[i] = (
from t in db.timeSheets
where restoreIds.Contains(t.project_Id) && (t.clockInTime >= startRange) && (t.clockInTime < endRange)
select t).ToList();
j--;
}
else if (i < 24)
{
startRange = today.AddDays(-7 * k);
endRange = today.AddDays(-7 * (k - 1));
timesheetInception[i] = (
from t in db.timeSheets
where abwkIds.Contains(t.project_Id) && (t.clockInTime >= startRange) && (t.clockInTime < endRange)
select t).ToList();
k--;
}
else
{
startRange = today.AddDays(-7 * l);
endRange = today.AddDays(-7 * (l - 1));
timesheetInception[i] = (
from t in db.timeSheets
where abwkIds.Contains(t.project_Id) && (t.clockInTime >= startRange) && (t.clockInTime < endRange)
select t).ToList();
l--;
}
}
rs.data = timesheetInception;
rs.errorCode = ReturnStatus.ALL_CLEAR;
}
catch
{
rs.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
}
return(rs);
}
public static ReturnStatus GetTimeCardPageWithFilter(int page, int itemsPerPage, ref int totalTimeCards, int orgId, int projId, DateTime rangeStart, DateTime rangeEnd, string searchTerm)
{
ReturnStatus cardsReturn = new ReturnStatus();
try
{
string sql = " SELECT T.Id AS timeId, " +
" T.user_Id AS userId, " +
" P.Id AS projId, " +
" O.Id AS orgId, " +
" T.clockInTime AS inTime, " +
" T.clockOutTime AS outTime, " +
" O.name AS orgName, " +
" P.name AS projName, " +
" ISNULL(U.firstName,U.emailAddress) + ' ' + ISNULL(U.lastName,U.emailAddress) AS volName " +
" FROM dbo.TimeSheet T LEFT JOIN dbo.[User] U ON T.user_Id = U.Id " +
" LEFT JOIN Organization O ON T.org_Id = O.Id " +
" LEFT JOIN Project P ON P.Id = T.project_Id " +
" WHERE 1=1 ";
sql += (projId > 0) ? " AND P.Id = @projectId " : "";
sql += (orgId > 0) ? " AND O.Id = @orgId " : "";
sql += " AND CONVERT(DATE, T.clockInTime) BETWEEN '" + rangeStart.Date.ToString("yyyyMMdd") +
"' AND '" + rangeEnd.Date.ToString("yyyyMMdd") + "' ";
sql += (!string.IsNullOrEmpty(searchTerm)) ?
//" AND (U.firstName LIKE '%' + @searchTerm + '%' OR U.lastName LIKE '%' + @searchTerm + '%') " : "";
" AND (U.firstName LIKE '%' + @searchTerm + '%' OR U.lastName LIKE '%' + @searchTerm + '%' " +
" OR O.name LIKE '%' + @searchTerm + '%' OR P.name LIKE '%' + @searchTerm + '%') " : "";
sql += " ORDER BY T.clockInTime DESC ";
VolunteerDbContext db = new VolunteerDbContext();
List <SqlParameter> sqlParams = new List <SqlParameter>();
if (projId > 0)
{
sqlParams.Add(new SqlParameter("@projectId", projId));
}
if (orgId > 0)
{
sqlParams.Add(new SqlParameter("@orgId", orgId));
}
if (!string.IsNullOrEmpty(searchTerm))
{
sqlParams.Add(new SqlParameter("@searchTerm", searchTerm));
}
var cards = db.Database.SqlQuery <TimeCardVM>(sql, sqlParams.ToArray()).ToList();
cardsReturn.errorCode = 0;
cardsReturn.data = cards.ToList();
return(cardsReturn);
}
catch
{
cardsReturn.errorCode = ReturnStatus.ERROR_WHILE_ACCESSING_DATA;
return(cardsReturn);
}
}
public static ReturnStatus GetProjectDemographicsReport(int monthsAgo)
{
ReturnStatus reportReturn = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
DateTime today = DateTime.Today;
DateTime firstThisMonth = new DateTime(today.Year, today.Month, 1);
DateTime periodEnd = firstThisMonth.AddMonths(-monthsAgo);
DateTime periodStart = periodEnd.AddMonths(-1);
string q =
" SELECT MAX(T.CLOCKINTIME) AS monthName, " +
" PC.CATEGORYTYPE AS category, " +
" COUNT(U.ID) AS numVolunteers, " +
" CONVERT(INT,SUM(DATEDIFF(HH,T.CLOCKINTIME,T.CLOCKOUTTIME))) AS numHours, " +
" SUM(CASE WHEN U.COLLEGESTATUS LIKE 'Yes' THEN 1 ELSE 0 END) AS numStudents, " +
" SUM(CASE WHEN U.VETERANSTATUS LIKE 'Yes' THEN 1 ELSE 0 END) AS numVeterans, " +
" SUM(CASE WHEN U.DISABLEDSTATUS LIKE 'Yes' THEN 1 ELSE 0 END) AS numDisabled, " +
" SUM(CASE WHEN U.incomeTier LIKE 'Under $24,999' THEN 1 ELSE 0 END) AS numUnder25k, " +
" SUM(CASE WHEN U.ethnicity LIKE 'Native American%' THEN 1 ELSE 0 END) AS numNative, " +
" SUM(CASE WHEN U.ethnicity LIKE 'Asian' THEN 1 ELSE 0 END) AS numAsian, " +
" SUM(CASE WHEN U.ethnicity LIKE 'Black%' THEN 1 ELSE 0 END) AS numBlack, " +
" SUM(CASE WHEN U.ethnicity LIKE 'Hispanic%' THEN 1 ELSE 0 END) AS numHispanic, " +
" SUM(CASE WHEN U.ethnicity LIKE '%Hawaii%' THEN 1 ELSE 0 END) AS numHawaiian, " +
" SUM(CASE WHEN U.ethnicity LIKE 'White' THEN 1 ELSE 0 END) AS numWhite, " +
" SUM(CASE WHEN U.ethnicity LIKE 'Two or More' THEN 1 ELSE 0 END) AS numTwoEthnic, " +
" SUM(CASE WHEN U.gender = 'M' THEN 1 ELSE 0 END) AS male, " +
" SUM(CASE WHEN U.gender = 'F' THEN 1 ELSE 0 END) AS female" +
" FROM TIMESHEET T " +
" LEFT JOIN PROJECT P ON T.PROJECT_ID = P.Id " +
" LEFT JOIN PROJECTCATEGORY PC ON P.CATEGORYID = PC.ID " +
" LEFT JOIN dbo.[User] U ON T.[USER_ID] = U.ID " +
" WHERE T.CLOCKINTIME BETWEEN '" + periodStart.ToString() + "' AND '" + periodEnd.ToString() + "' " +
" GROUP BY " +
" PC.CATEGORYTYPE ";
var projectDemographics = db.Database.SqlQuery <ProjDemogReportVM>(q
).ToList();
reportReturn.errorCode = 0;
reportReturn.data = projectDemographics.ToList();
return(reportReturn);
}
catch
{
reportReturn.errorCode = ReturnStatus.ERROR_WHILE_ACCESSING_DATA;
return(reportReturn);
}
}
/// <summary>
/// Finds email if it exists in the database.
/// </summary>
/// <param name="email">Email to search for.</param>
/// <returns>True if email exists</returns>
public static ReturnStatus EmailExists(string email)
{
ReturnStatus st = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
st.data = db.users.Any(u => u.emailAddress.Equals(email));
return(st);
}
catch
{
st.errorCode = -1;
return(st);
}
}
/// <summary>
/// Gets data for Hours volunteered Bar Chart Admin/Dashboard
/// </summary>
/// <param name="restoreId"></param>
/// <param name="awbkId"></param>
/// <returns>An array of 9 lists of timecards, 3 years worth of timesheets for the 3 categories, restore, awbk, and everything else</returns>
public static ReturnStatus Get3YearsTimeSheetsByCategory(List <int> restoreIds, List <int> abwkIds, List <int> homeIds)
{
ReturnStatus rs = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
int thisYear = DateTime.Today.Year;
List <TimeSheet>[] timesheetInception = new List <TimeSheet> [9];
int j = 2, k = 2, l = 2;
for (int i = 0; i < 9; i++)
{
if (i < 3)
{
timesheetInception[i] = (
from t in db.timeSheets
where restoreIds.Contains(t.project_Id) && (t.clockInTime.Year == thisYear - j)
select t).ToList();
j--;
}
else if (i < 6)
{
timesheetInception[i] = (
from t in db.timeSheets
where abwkIds.Contains(t.project_Id) && (t.clockInTime.Year == thisYear - k)
select t).ToList();
k--;
}
else
{
timesheetInception[i] = (
from t in db.timeSheets
where restoreIds.Contains(t.project_Id) && (t.clockInTime.Year == thisYear - l)
select t).ToList();
l--;
}
}
rs.data = timesheetInception;
rs.errorCode = ReturnStatus.ALL_CLEAR;
}
catch
{
rs.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
}
return(rs);
}
public static ReturnStatus GetWaiverByID(int id)
{
ReturnStatus rs = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
var waiver = db.waiverHistory.Find(id);
rs.errorCode = ReturnStatus.ALL_CLEAR;
rs.data = waiver;
}
catch
{
rs.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
}
return(rs);
}
public static ReturnStatus GetWaiverHistoryByUserId(int id)
{
ReturnStatus rs = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
List <WaiverHistory> waiverHistory = db.waiverHistory.Where(wh => wh.user_Id == id).ToList();
rs.errorCode = ReturnStatus.ALL_CLEAR;
rs.data = waiverHistory;
}
catch
{
rs.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
}
return(rs);
}
public static ReturnStatus CreateEvent(HfhEvent hfhEvent)
{
ReturnStatus rs = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
db.hfhEvents.Add(hfhEvent);
db.SaveChanges();
rs.errorCode = ReturnStatus.ALL_CLEAR;
}
catch
{
rs.errorCode = ReturnStatus.COULD_NOT_UPDATE_DATABASE;
}
return(rs);
}
public static ReturnStatus GetProjectIdByName(string name)
{
ReturnStatus rs = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
var pId = db.projects.Where(p => p.name.Contains(name)).FirstOrDefault().Id;
rs.errorCode = ReturnStatus.ALL_CLEAR;
rs.data = pId;
}
catch
{
rs.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
}
return(rs);
}
/// <summary>
/// Gets the user by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static ReturnStatus GetUser(int id)
{
ReturnStatus st = new ReturnStatus();
st.data = new User();
try
{
VolunteerDbContext db = new VolunteerDbContext();
st.data = db.users.Find(id);
st.errorCode = 0;
return(st);
}
catch
{
st.errorCode = -1;
return(st);
}
}
/// <summary>
/// Creates a joining relationship for a Project
/// to an HfhEvent
/// </summary>
/// <param name="pe">A joining object with the id's of the two objects to join</param>
/// <returns>Returns an ErrorCode indicating the result of the db insert</returns>
public static ReturnStatus AddProjectToEvent(ProjectEvent pe)
{
ReturnStatus rs = new ReturnStatus();
VolunteerDbContext db = new VolunteerDbContext();
try
{
db.eventProjects.Add(pe);
db.SaveChanges();
rs.errorCode = ReturnStatus.ALL_CLEAR;
}
catch
{
rs.errorCode = ReturnStatus.COULD_NOT_UPDATE_DATABASE;
}
return(rs);
}
/// <summary>
/// Gets all the users that contain any characters in firstName or lastName.
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <returns>ReturnStatus object containing a list of users</returns>
public static ReturnStatus GetUsersByName(string firstName, string lastName)
{
ReturnStatus st = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
st.data = db.users.Where(x => x.firstName.Contains(firstName) || x.lastName.Contains(lastName)).ToList();
st.errorCode = 0;
return(st);
}
catch
{
st.errorCode = -1;
st.data = new List <User>();
return(st);
}
}
/// <summary>
/// Adds a user to the database.
/// </summary>
/// <param name="user">User to add.</param>
/// <returns>The id of the user or 0 if no user could be added.</returns>
public static ReturnStatus CreateUser(User user)
{
ReturnStatus st = new ReturnStatus();
st.data = null;
try
{
user.password = Crypto.HashPassword(user.password);
//user.waiverSignDate = DateTime.Today;
VolunteerDbContext db = new VolunteerDbContext();
db.users.Add(user);
db.SaveChanges();
//entity framework automagically populates a model with all database generated ids
//so the passed in user object will have an id
st.errorCode = (int)ReturnStatus.ALL_CLEAR;
st.data = user.Id;
//var users = db.users.Where(u => u.emailAddress.Equals(user.emailAddress));
//User newUser = users.FirstOrDefault();
//if (newUser != null)
//{
// userId = newUser.Id;
//}
//return userId;
return(st);
}
catch (ArgumentNullException e)
{
st.errorCode = (int)ReturnStatus.NULL_ARGUMENT;
st.errorMessage = e.ToString();
return(st);
}
catch (Exception e)
{
st.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
st.errorMessage = e.ToString();
return(st);
}
}
public static ReturnStatus GetTimeSheetIdsByUserId(int userId)
{
ReturnStatus rs = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
var ids = (from t in db.timeSheets
where t.user_Id == userId
select t.Id).ToList();
rs.errorCode = ReturnStatus.ALL_CLEAR;
rs.data = ids;
}
catch
{
rs.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
}
return(rs);
}
/// <summary>
/// Gets all the supplied volunteers timesheets
/// </summary>
/// <param name="volunteerId"></param>
/// <returns></returns>
public static ReturnStatus GetAllVolunteerTimeSheets(int volunteerId)
{
ReturnStatus st = new ReturnStatus();
st.data = new List <TimeSheet>();
try
{
VolunteerDbContext db = new VolunteerDbContext();
st.errorCode = ReturnStatus.ALL_CLEAR;
st.data = db.timeSheets.Where(x => x.user_Id == volunteerId).ToList();
return(st);
}
catch (Exception e)
{
st.errorCode = (int)ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
st.errorMessage = e.ToString();
return(st);
}
}
/// <summary>
/// Get the TimeSheet with the matching id.
/// </summary>
/// <param name="id"></param>
/// <returns>A TimeSheet object with matching id otherwise null.</returns>
public static ReturnStatus GetTimeSheetById(int id)
{
ReturnStatus st = new ReturnStatus();
st.data = new TimeSheet();
try
{
VolunteerDbContext db = new VolunteerDbContext();
st.errorCode = ReturnStatus.ALL_CLEAR;
st.data = db.timeSheets.Find(id);
return(st);
}
catch (Exception e)
{
st.errorCode = ReturnStatus.COULD_NOT_CONNECT_TO_DATABASE;
st.errorMessage = e.ToString();
return(st);
}
}
public static ReturnStatus GetAllUsers()
{
ReturnStatus rs = new ReturnStatus();
ReturnStatus st = new ReturnStatus();
try
{
VolunteerDbContext db = new VolunteerDbContext();
st.data = db.users.ToList();
st.errorCode = 0;
return(st);
}
catch
{
st.errorCode = -1;
st.data = new List <User>();
return(st);
}
}
