// GetAllUsers() will return the list of all users in database. // We put the user data in UserHelper class defined in Models/AccountModels file public static List<UserHelper> GetAllUsers() { // Call the EntityFramework to retreive data from AspNetUsers database table. // EntityFramework class spark1Entities inherits IDisposable interface and should be disposed // after usage. It is done by using statement. using (spark1Entities db = new spark1Entities()) { // This is basic LINQ to Entities example return db // First call db.AspNetUsers table, .AspNetUsers // then include AspNetRoles table in the search query (similar to inner join in SQL), .Include("AspNetRoles") // then for every return row we create one UserHelper object with data from that row, .Select(u => new UserHelper() { UserName = string.IsNullOrEmpty(u.AgentFullName) ? u.UserName : u.AgentFullName, UserRole = u.AspNetRoles.FirstOrDefault().Name, }) // and finally convert the search result to List<UserHelper> .ToList(); } }
public static string UpdateLoginStats(string Username) { using (spark1Entities db = new spark1Entities()) { string reportId = null; IEnumerable<UserSettings> settings = db .UserSettings .Include("AspNetUsers") .Where(us => us.AspNetUsers.UserName == Username); if (settings != null && settings.Count() == 1) { settings.FirstOrDefault().LastLogin = DateTime.Now; reportId = settings.FirstOrDefault().SelectedReport; } else { string userId = db .AspNetUsers .Where(us => us.UserName == Username) .FirstOrDefault() .Id; reportId = db .ReportEntries .OrderByDescending(r => r.EntryDateTime) .FirstOrDefault() .EntryId; db .UserSettings .Add(new UserSettings() { UserId = userId, LastLogin = DateTime.Now, SelectedReport = reportId }); } try { db.SaveChanges(); } catch (Exception e) { return e.Message; } HttpContext.Current.Session["Data"] = DBReportHelper.ParseDataWorkSheet( DBReportHelper.GetUserDefaultDBEntry().EntryId); HttpContext.Current.Session["DataTitle"] = DBReportHelper.GetUserDefaultDBEntry(); return null; } }
public static string GetMyUserName() { string result = "", username = HttpContext.Current.User.Identity.Name; using (spark1Entities db = new spark1Entities()) result = db .AspNetUsers .Where(u => u.UserName == username) .Count() > 0 ? db .AspNetUsers .Where(u => u.UserName == username) .FirstOrDefault() .AgentFullName : username; return result; }
public static List<DBEntry> GetAllReports() { using (spark1Entities db = new spark1Entities()) return db .ReportEntries .Include("AspNetUsers") .OrderByDescending(re => re.EntryDateTime) .Select(re => new DBEntry() { EntryDateTime = re.EntryDateTime.ToString(), EntryId = re.EntryId, UserName = string.IsNullOrEmpty(re.AspNetUsers.AgentFullName) ? re.AspNetUsers.UserName : re.AspNetUsers.AgentFullName, Title = re.Title, Description = re.Description }) .ToList(); }
// Gets default DB report file id from the UserSettings table in database public static DBEntry GetUserDefaultDBEntry() { string User_id = UsersHelper.GetMyUserId(); using (spark1Entities db = new spark1Entities()) { // we will enter default report id in this raturn variable string report_id = null; // check if there is a row entry in UserSettings table for the current user if (db.UserSettings.Where(us=>us.UserId == User_id).Count() == 1) { // assign default report id from the table row report_id = db .UserSettings .Where(us => us.UserId == User_id) .FirstOrDefault() .SelectedReport; } // if report_id variable still has NULL value (no row entry in UserSettings, no value in SelectedReport cell) // return latest uploaded report or NULL if there are no reports uploaded at all if (string.IsNullOrEmpty(report_id)) { IEnumerable<ReportEntries> allReports = db .ReportEntries .Include("AspNetUsers") .Include("UserSettings"); if (allReports == null || allReports.Count() <= 0) return null; return allReports .OrderByDescending(ar => ar.EntryDateTime) .Select(ar => new DBEntry() { EntryDateTime = ar.EntryDateTime.ToString(), EntryId = ar.EntryId, UserName = ar.AspNetUsers.AgentFullName, Title = ar.Title, Description = ar.Description }) .FirstOrDefault(); } // this means we have report id and will return object with that id return db .ReportEntries .Include("AspNetUsers") .Where(re => re.EntryId == report_id) .Select(re => new DBEntry() { EntryDateTime = re.EntryDateTime.ToString(), EntryId = re.EntryId, UserName = re.AspNetUsers.AgentFullName, Title = re.Title, Description = re.Description }) .FirstOrDefault(); } }
public static string UploadReport(HttpPostedFileBase File, string Title, string Description) { if (File == null || File.ContentLength <= 0 || string.IsNullOrEmpty(File.FileName)) return "err_03"; string UserId = UsersHelper.GetMyUserId(); string fileExtension = Path.GetExtension(File.FileName).ToLower(); // we accept only xlsx Excel files if (fileExtension != ".xlsx") return "no_xlsx"; string dataDir = HttpContext.Current.Server.MapPath("~/xls_reports/"), userDir = Path.Combine(dataDir, UserId); if (!Directory.Exists(userDir)) Directory.CreateDirectory(userDir); using (spark1Entities db = new spark1Entities()) { // generating new report id bool exists = true; string report_id = ""; while (exists) { report_id = Guid.NewGuid().ToString(); exists = db.ReportEntries.Where(re => re.EntryId == report_id).Count() > 0; } // adding the report to ReportEntries database table db.ReportEntries.Add(new ReportEntries() { EntryId = report_id, Description = Description, Title = Title, EntryDateTime = DateTime.Now, EntryUser = UserId }); try { // save changes to database db.SaveChanges(); } catch { return "err_01"; } try { // save file to user directory string fileName = Path.Combine(userDir, report_id + fileExtension); File.SaveAs(fileName); } catch { return "err_02"; } // set newly uploaded file as users default SetDefaultDBEntry(report_id); return report_id; } }
// Store default DB Entry to database public static string SetDefaultDBEntry(string Report_id) { using (spark1Entities db = new spark1Entities()) { string User_id = UsersHelper.GetMyUserId(); // get user settings entry from UserSettings table in database IEnumerable<UserSettings> userSettings = db.UserSettings.Where(us => us.UserId == User_id); // if there is no entry for this user if (userSettings == null || userSettings.Count() <= 0) { // add new user settings entry db.UserSettings.Add(new UserSettings() { LastLogin = DateTime.Now, UserId = User_id, SelectedReport = Report_id }); } else { // change value in SelectedReport field in the UserSettings table userSettings.FirstOrDefault().SelectedReport = Report_id; } // save changes to the database try { db.SaveChanges(); } catch (Exception e) { return e.Message; } HttpContext.Current.Session["Data"] = ParseDataWorkSheet( GetUserDefaultDBEntry().EntryId); HttpContext.Current.Session["DataTitle"] = DBReportHelper.GetUserDefaultDBEntry(); return null; } }
// This function will parse all data from a DB report worksheet to a DataTable object public static DataTable ParseDataWorkSheet(string Entry_id) { string fileName = null; using (spark1Entities db = new spark1Entities()) { ReportEntries entry = db .ReportEntries .Where(re => re.EntryId == Entry_id) .FirstOrDefault(); fileName = Path.Combine( HttpContext.Current.Server.MapPath("~/xls_reports"), entry.EntryUser, entry.EntryId + ".xlsx"); } using (var pck = new OfficeOpenXml.ExcelPackage()) { using (var stream = File.OpenRead(fileName)) { pck.Load(stream); } var ws = pck.Workbook.Worksheets["Data"]; DataTable tbl = new DataTable(); bool hasHeader = true; foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; var row = tbl.NewRow(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } tbl.Rows.Add(row); } return tbl; } }