public static List <ServiceSheetViewModel> loadHolidayAbsenceSheets()
{
List <ServiceSheetViewModel> retval = new List <ServiceSheetViewModel>();
UserViewModel dbUser = getDbUser();
if (dbUser == null)
{
return(null);
}
//Downloads the service sheets from the database and creates the vms
try
{
using (var dbContext = new ServiceSheetsEntities())
{
updateContextConnection(dbUser, dbContext);
var serviceSheets = from ServiceSheet in dbContext.ServiceSheets
where ServiceSheet.SubmissionNumber < 0
select ServiceSheet;
retval = ServiceSheetViewModel.loadFromModel(serviceSheets);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
return(retval);
}
private static void updateContextConnection(UserViewModel dbUser, ServiceSheetsEntities dbContext)
{
System.Data.Common.DbConnection connection = dbContext.Database.Connection;
System.Data.Common.DbConnectionStringBuilder str = new System.Data.Common.DbConnectionStringBuilder();
str.ConnectionString = dbContext.Database.Connection.ConnectionString;
str.Add("Password", dbUser.PasswordBoxObj.Password);
dbContext.Database.Connection.ConnectionString = str.ConnectionString;
}
public static bool?checkForClashingDates(DateTime startDate, DateTime endDate, string username)
{
//Returns true if a service day exists for between the given dates.
List <ServiceSheetViewModel> retval = new List <ServiceSheetViewModel>();
UserViewModel dbUser = getDbUser();
if (dbUser == null)
{
return(null);
}
try
{
using (var dbContext = new ServiceSheetsEntities())
{
updateContextConnection(dbUser, dbContext);
var serviceSheets =
from ss in dbContext.ServiceSheets
join sd in dbContext.ServiceDays on ss.Id equals sd.ServiceSheetId
where ss.Username == username && sd.DtReport >= startDate && sd.DtReport <= endDate
select ss;
retval = ServiceSheetViewModel.loadFromModel(serviceSheets);
}
}
catch (EntityException entityEx)
{
//Something went wrong with the load. Clear the cache for the username.
clearCacheDbUsername();
Console.WriteLine(entityEx.ToString());
return(null);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
if (retval.Count == 0)
{
return(false);
}
return(true);
}
public static List <DbEmployee> getAllUsers()
{
List <DbEmployee> retval = new List <DbEmployee>();
//Selects all submission numbers from the database
UserViewModel dbUser = getDbUser();
if (dbUser == null)
{
return(null);
}
try
{
using (var dbContext = new ServiceSheetsEntities())
{
updateContextConnection(dbUser, dbContext);
var userQuery = dbContext.ServiceSheets
.Select(x => new { x.Username, x.UserFirstName, x.UserSurname }).Distinct();
foreach (var user in userQuery)
{
retval.Add(new DbEmployee(user.Username, user.UserFirstName, user.UserSurname));
}
}
}
catch (EntityException entityEx)
{
//Something went wrong with the load. Clear the cache for the username.
clearCacheDbUsername();
Console.WriteLine(entityEx.ToString());
return(null);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
return(retval);
}
public static DateTime?getLastSubmissionDate()
{
UserViewModel dbUser = getDbUser();
if (dbUser == null)
{
return(null);
}
DateTime retval;
try
{
using (var dbContext = new ServiceSheetsEntities())
{
updateContextConnection(dbUser, dbContext);
var serviceSheets = (from sheets in dbContext.ServiceSheets
where sheets.SubmissionNumber > 0
orderby sheets.DtResponse descending
select sheets.DtResponse).FirstOrDefault();
retval = serviceSheets;
}
}
catch (EntityException entityEx)
{
//Something went wrong with the load. Clear the cache for the username.
clearCacheDbUsername();
Console.WriteLine(entityEx.ToString());
return(null);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
return(retval);
}
public static List <ServiceSheetViewModel> downloadServiceSheets(DateTime monthFirstDay, DateTime monthEndDay)
{
List <ServiceSheetViewModel> retval = new List <ServiceSheetViewModel>();
UserViewModel dbUser = getDbUser();
if (dbUser == null)
{
return(null);
}
//Downloads the service sheets from the database and creates the vms
try
{
using (var dbContext = new ServiceSheetsEntities())
{
updateContextConnection(dbUser, dbContext);
var serviceSheets = from ServiceSheet in dbContext.ServiceSheets
//join sDay in dbContext.ServiceDays
//on ServiceSheet.Id equals sDay.ServiceSheetId
where ServiceSheet.ServiceDays.Any(sDays => sDays.DtReport >= monthFirstDay && sDays.DtReport <= monthEndDay)
select ServiceSheet;
retval = ServiceSheetViewModel.loadFromModel(serviceSheets);
}
}
catch (EntityException entityEx)
{
//Something went wrong with the load. Clear the cache for the username.
clearCacheDbUsername();
Console.WriteLine(entityEx.ToString());
return(null);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
return(retval);
}
//Class for handling data from the service sheet database table
public static bool saveSheetsAndDays(ObservableCollection <ServiceSheetViewModel> allServiceSheets)
{
UserViewModel dbUserVM = getDbUser();
if (dbUserVM == null)
{
return(false);
}
try
{
using (var dbContext = new ServiceSheetsEntities())
{
updateContextConnection(dbUserVM, dbContext);
//System.Data.Common.DbConnection connection = dbContext.Database.Connection;
//System.Data.Common.DbConnectionStringBuilder str = new System.Data.Common.DbConnectionStringBuilder();
//str.ConnectionString = dbContext.Database.Connection.ConnectionString;
//str.Add("Password", dbUserVM.PasswordBoxEntry.Password);
//dbContext.Database.Connection.ConnectionString = str.ConnectionString;
//dbContext.Database.Log = Console.Write;
foreach (ServiceSheetViewModel serviceVM in allServiceSheets)
{
dbContext.ServiceSheets.Add(serviceVM.ServiceSubmission);
foreach (ServiceDayViewModel day in serviceVM.AllServiceDays)
{
dbContext.ServiceDays.Add(day.ServiceDayModel);
}
}
dbContext.SaveChanges();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(false);
}
return(true);
}
public static List <ServiceSheetViewModel> downloadAllServiceSheets()
{
List <ServiceSheetViewModel> retval = new List <ServiceSheetViewModel>();
UserViewModel dbUser = getDbUser();
if (dbUser == null)
{
return(null);
}
//Downloads the service sheets from the database and creates the vms
try
{
using (var dbContext = new ServiceSheetsEntities())
{
updateContextConnection(dbUser, dbContext);
var serviceSheets = from ServiceSheet in dbContext.ServiceSheets
where ServiceSheet.SubmissionNumber > 15679
select ServiceSheet;
retval = ServiceSheetViewModel.loadFromModel(serviceSheets);
}
}
catch (EntityException entityEx)
{
//Something went wrong with the load. Clear the cache for the username.
clearCacheDbUsername();
Console.WriteLine(entityEx.ToString());
return(null);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
return(retval);
}
public static List <int> getAllSubmissionNumbers()
{
List <int> retval = new List <int>();
//Selects all submission numbers from the database
UserViewModel dbUser = getDbUser();
if (dbUser == null)
{
return(null);
}
try
{
using (var dbContext = new ServiceSheetsEntities())
{
updateContextConnection(dbUser, dbContext);
var submissionNumberQuery = from submissions in dbContext.ServiceSheets
select submissions.SubmissionNumber;
retval = submissionNumberQuery.ToList <int>();
}
}
catch (EntityException entityEx)
{
//Something went wrong with the load. Clear the cache for the username.
clearCacheDbUsername();
Console.WriteLine(entityEx.ToString());
return(null);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
return(retval);
}
private void saveToDatabase()
{
//Saves all submissions to database
using (var dbContext = new ServiceSheetsEntities())
{
//dbContext.Database.Log = Console.Write;
foreach (ServiceSheetViewModel serviceVM in AllServiceSheets)
{
dbContext.ServiceSheets.Add(serviceVM.ServiceSubmission);
foreach (ServiceDayViewModel day in serviceVM.AllServiceDays.AllServiceDayVMs)
{
dbContext.ServiceDays.Add(day.ServiceDayModel);
}
}
dbContext.SaveChanges();
}
}