// 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;
}
}
