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(); } }