protected ServiceBase2(IDatabaseFactory dbf, IMapper map)
{
this.map = map;
this.db = dbf.Get();
this.dbset = db.Set <T>();
this.logPrefix = typeof(T).ToString();
}
private static void recInitialize(MacheteContext DB)
{
Person p1 = (Person)Records.person.Clone();
p1.Worker = (Worker)Records.worker.Clone();
p1.Worker.dwccardnum = 30040; p1.Worker.skill1 = 62;
p1.Worker.memberStatusID = Worker.iActive;
p1.Worker.memberexpirationdate = DateTime.Now.AddDays(-1);
p1.Worker.Person = p1;
DB.Persons.Add(p1);
DB.Workers.Add(p1.Worker);
Person p2 = (Person)Records.person.Clone();
DB.Persons.Add(p2); p2.Worker = (Worker)Records.worker.Clone();
p2.Worker.dwccardnum = 30041;
p2.Worker.memberStatusID = Worker.iActive;
p2.Worker.memberexpirationdate = DateTime.Now.AddDays(1);
Person p3 = (Person)Records.person.Clone();
DB.Persons.Add(p3); p3.Worker = (Worker)Records.worker.Clone();
p3.Worker.dwccardnum = 30042;
p3.Worker.memberReactivateDate = DateTime.Now.AddDays(-1);
p3.Worker.memberStatusID = Worker.iSanctioned;
p3.Worker.memberexpirationdate = DateTime.Now.AddDays(1);
DB.SaveChanges();
}
/// <summary>
/// Expires active workers based on expiration date for a given tenant db
/// </summary>
/// <param name="context"></param>
public ExpireMembersResults ExpireMembers(MacheteContext context)
{
var results = new ExpireMembersResults();
results.Executed = false;
results.RecordsUpdated = 0;
var expiredLookUp = context.Lookups.SingleOrDefault(row => row.category.Equals(LCategory.memberstatus) && row.key.Equals(LMemberStatus.Expired));
var activeLookUp = context.Lookups.SingleOrDefault(row => row.category.Equals(LCategory.memberstatus) && row.key.Equals(LMemberStatus.Active));
var list = context.Workers
.Where(w =>
w.memberexpirationdate < DateTime.Now &&
w.memberStatusID == activeLookUp.ID)
.ToList();
if (list.Any())
{
foreach (Worker wkr in list)
{
wkr.memberStatusID = expiredLookUp.ID;
wkr.memberStatusEN = expiredLookUp.text_EN;
wkr.memberStatusES = expiredLookUp.text_ES;
wkr.updatedby = "ExpirationBot";
}
results.RecordsUpdated = context.SaveChanges();
results.Executed = true;
}
return(results);
}
private static void AddDBReadOnlyUser(MacheteContext context, string readonlyPassword)
{
var connection = context.Database.GetDbConnection();
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (var command = connection.CreateCommand())
{
command.CommandText = "sp_executesql";
command.CommandType = CommandType.StoredProcedure;
var param = command.CreateParameter();
param.ParameterName = "@statement";
param.Value = $@"
CREATE LOGIN readonlylogin WITH PASSWORD='******'
CREATE USER readonlyuser FROM LOGIN readonlylogin
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';
";
command.Parameters.Add(param);
try
{
command.ExecuteNonQuery();
}
catch (Microsoft.Data.SqlClient.SqlException ex)
{
var userAlreadyExists = ex.Errors[0].Number.Equals(15025) || ex.Message.Contains("already exists");
if (!userAlreadyExists)
{
throw;
}
}
}
}
public List <string> ExecuteSql(MacheteContext context, string query)
{
var errors = new List <string>();
var connection = (context as System.Data.Entity.DbContext).Database.Connection;
using (var command = connection.CreateCommand())
{
command.CommandText = "sp_executesql";
command.CommandType = CommandType.StoredProcedure;
var param = command.CreateParameter();
param.ParameterName = "@statement";
param.Value = query;
command.Parameters.Add(param);
connection.Open();
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
for (var i = 0; i < ex.Errors.Count; i++)
{
// just messages for now; more available: https://stackoverflow.com/a/5842100/2496266
errors.Add(ex.Errors[i].Message);
}
}
}
return(errors);
}
public static void Initialize(MacheteContext DB)
{
IdentityResult ir;
var rm = new RoleManager<IdentityRole>
(new RoleStore<IdentityRole>(DB));
ir = rm.Create(new IdentityRole("Administrator"));
ir = rm.Create(new IdentityRole("Manager"));
ir = rm.Create(new IdentityRole("Check-in"));
ir = rm.Create(new IdentityRole("PhoneDesk"));
ir = rm.Create(new IdentityRole("Teacher"));
ir = rm.Create(new IdentityRole("User"));
ir = rm.Create(new IdentityRole("Hirer")); // This role is used exclusively for the online hiring interface
var um = new UserManager<ApplicationUser>(
new UserStore<ApplicationUser>(DB));
var user = new ApplicationUser()
{
UserName = "******",
IsApproved = true,
Email = "*****@*****.**"
};
ir = um.Create(user, "ChangeMe");
ir = um.AddToRole(user.Id, "Administrator"); //Default Administrator, edit to change
ir = um.AddToRole(user.Id, "Teacher"); //Required to make tests work
DB.Commit();
}
public MacheteUserStore(MacheteContext assignedContext, bool isSeedInstance)
{
_dataContext = assignedContext;
_users = _dataContext.Users;
_roles = _dataContext.Roles;
_userRoles = _dataContext.UserRoles;
}
public static async Task SeedAsync(MacheteContext db)
{
if (!db.Users.Any())
{
await MacheteSeedUsers.Initialize(db);
}
}
public MacheteUserStore(IDatabaseFactory factory)
{
_dataContext = factory.Get(); // should have its own _tenantService
_users = _dataContext.Users;
_roles = _dataContext.Roles;
_userRoles = _dataContext.UserRoles;
_userClaims = _dataContext.UserClaims;
}
public static int nextAvailableDwccardnum(MacheteContext DB)
{
int attempt = 30000;
while (DB.Workers.Any(x => x.dwccardnum == attempt))
{
++attempt;
}
return attempt;
}
//public void Set(MacheteContext context)
//{
// dataContext = context;
//}
public void Dispose()
{
if (macheteContext != null)
{
log_connection_count("DatabaseFactory.Dispose");
macheteContext.Dispose();
macheteContext = null;
}
}
public MacheteContext Get()
{
if (macheteContext == null)
{
macheteContext = new MacheteContext(options);
}
log_connection_count("DatabaseFactory.Get");
return(macheteContext);
}
//public void Set(MacheteContext context)
//{
// dataContext = context;
//}
protected override void DisposeCore()
{
if (macheteContext != null)
{
log_connection_count("DatabaseFactory.DisposeCore");
macheteContext.Dispose();
macheteContext = null;
}
}
/// <summary>
///
/// </summary>
/// <param name="personID"></param>
/// <param name="q"></param>
/// <param name="asRepo"></param>
public static void getAssociated(int personID, ref IQueryable <Activity> q, MacheteContext db)
{
var asRepo = db.ActivitySignins.AsNoTracking().AsQueryable();
q = from a in q
join az in asRepo on a.ID equals az.activityID into g
from f in g.DefaultIfEmpty()
where f.personID == personID
select a;
}
public static int nextAvailableDwccardnum(MacheteContext DB)
{
int attempt = 30000;
while (DB.Workers.Any(x => x.dwccardnum == attempt))
{
++attempt;
}
return(attempt);
}
public AccountController(
UserManager <MacheteUser> userManager,
SignInManager <MacheteUser> signInManager,
IHtmlLocalizer <AccountController> localizer,
MacheteContext context)
{
UserManager = userManager;
SignInManager = signInManager;
_context = context;
_localizer = localizer;
}
// used for report initialization
public static string getUIColumnsJson(MacheteContext context, string query)
{
var cols = SqlServerUtils.getMetadata(context, query);
var result = cols.Select(a =>
new {
field = a.name,
header = a.name,
visible = a.name == "id" ? false : true
});
return(JsonConvert.SerializeObject(result));
}
private const int PasswordExpirationInMonths = 6; // represents number of months where users passwords expire
public AccountController(
UserManager <MacheteUser> userManager,
SignInManager <MacheteUser> signinManager,
RoleManager <MacheteRole> roleManager,
MacheteContext context
)
{
_userManager = userManager;
_signinManager = signinManager;
_roleManager = roleManager;
_context = context;
}
/// <summary>
/// Initialize the Machete Lookups (configuration values); this should only be called by MacheteConfiguration.
/// This represents an example of how to execute plain text SQL commands directly against the injected context.
/// </summary>
/// <param name="context">MacheteContext is injected, therefore disposal is handled by the container.</param>
public static void Initialize(MacheteContext context)
{
_cache.ForEach(u => {
u.datecreated = DateTime.Now;
u.dateupdated = DateTime.Now;
u.createdby = "Init T. Script";
u.updatedby = "Init T. Script";
context.Lookups.Add(u);
});
context.Database.OpenConnection();
context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Lookups ON");
context.SaveChanges();
context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Lookups OFF");
}
public static List <QueryMetadata> getMetadata(MacheteContext context, string fromQuery)
{
var param = new SqlParameter("@query", escapeQueryText(fromQuery));
var queryResult = context.Database.SqlQuery <QueryMetadata>(
// https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-describe-first-result-set-transact-sql
// http://stackoverflow.com/questions/13766564/finding-number-of-columns-returned-by-a-query
@"SELECT
name, is_nullable, system_type_name
FROM
sys.dm_exec_describe_first_result_set(@query, NULL, 0);",
param);
return(queryResult.ToList());
}
public static void ClassInitialize(TestContext testContext)
{
WebServer.StartIis();
// getting Project path for dummy image
string solutionDirectory = sharedUI.SolutionDirectory();
//testdir = solutionDirectory + "\\Machete.test\\";
testimagefile = solutionDirectory + "\\jimmy_machete.jpg";
driver = new ChromeDriver(ConfigurationManager.AppSettings["CHROMEDRIVERPATH"]);
baseURL = "http://localhost:4213/";
ui = new sharedUI(driver, baseURL);
DB = new MacheteContext();
wsiSet = DB.Set<WorkerSignin>();
wSet = DB.Set<Worker>();
pSet = DB.Set<Person>();
}
/// <summary>
/// Expires active workers based on expiration date
/// </summary>
/// <param name="db"></param>
/// <returns>true if at least one record expired</returns>
public static bool ExpireMembers(MacheteContext db)
{
bool rtn = false;
IQueryable<Worker> list = db.Workers
.Where(w => w.memberexpirationdate < DateTime.Now &&
w.memberStatus == Worker.iActive);
//
if (list.Count() > 0) rtn = true;
//
foreach (Worker wkr in list)
{
wkr.memberStatus = Worker.iExpired;
}
db.SaveChanges();
return rtn;
}
public MacheteContext Get()
{
if (macheteContext == null)
{
if (connString == null)
{
macheteContext = new MacheteContext();
}
else
{
macheteContext = new MacheteContext(connString);
}
}
log_connection_count("DatabaseFactory.Get");
return(macheteContext);
}
public static void ClassInitialize(TestContext testContext)
{
WebServer.StartIis();
// getting Project path for dummy image
string solutionDirectory = sharedUI.SolutionDirectory();
//testdir = solutionDirectory + "\\Machete.test\\";
testimagefile = solutionDirectory + "\\jimmy_machete.jpg";
map = new Machete.Web.MapperConfig().getMapper();
driver = new ChromeDriver(ConfigurationManager.AppSettings["CHROMEDRIVERPATH"]);
baseURL = "http://localhost:4213/";
ui = new sharedUI(driver, baseURL, map);
DB = new MacheteContext();
wsiSet = DB.Set <WorkerSignin>();
wSet = DB.Set <Worker>();
pSet = DB.Set <Person>();
}
/// <summary>
/// Checks if a config exists, if it doesn't, it creates one
/// This method should always check if tenant has all configs,
/// </summary>
/// <param name="context"></param>
/// <param name="tenantTimeZone"></param>
public static void Synchronize(MacheteContext context, string tenantTimeZone)
{
foreach (var c in list)
{
if (!context.Configs.Any(config => config.key == c.key))
{
if (c.key == Cfg.MicrosoftTimeZoneIndex)
{
c.value = TZConvert.IanaToWindows(tenantTimeZone);
}
c.datecreated = DateTime.Now;
c.dateupdated = DateTime.Now;
c.createdby = "Init T. Script";
c.updatedby = "Init T. Script";
context.Configs.Add((Config)c.Clone());
context.SaveChanges();
}
}
}
public static void Seed(MacheteContext db, string tenantTimeZone)
{
if (!db.Lookups.Any())
{
MacheteLookups.Initialize(db);
}
if (!db.TransportProviders.Any() || !db.TransportProviderAvailabilities.Any())
{
MacheteTransports.Initialize(db);
}
MacheteConfigs.Synchronize(db, tenantTimeZone);
if (!db.TransportRules.Any())
{
MacheteRules.Initialize(db);
}
if (db.ReportDefinitions.Count() == 0) // Reports are manged in UI now
{
MacheteReportDefinitions.Initialize(db);
}
}
public static void Seed(MacheteContext db)
{
if (!db.Lookups.Any())
{
MacheteLookups.Initialize(db);
}
if (!db.TransportProviders.Any() || !db.TransportProviderAvailabilities.Any())
{
MacheteTransports.Initialize(db);
}
if (!db.Configs.Any())
{
MacheteConfigs.Initialize(db);
}
if (!db.TransportRules.Any())
{
MacheteRules.Initialize(db);
}
if (db.ReportDefinitions.Count() != MacheteReportDefinitions._cache.Count)
{
MacheteReportDefinitions.Initialize(db);
}
}
// Enable initialization with an instance of ApplicationUser:
public SelectUserRolesViewModel(MacheteUser user, MacheteContext dbFactory)
: this()
{
UserName = user.UserName;
string[] firstLast = user.UserName.Split('.');
FirstName = firstLast[0];
if (firstLast.Length > 1)
{
LastName = firstLast[1];
}
else
{
LastName = "";
}
UserId = user.Id;
// ReSharper disable once SuggestVarOrType_Elsewhere
// Add all available roles to the list of EditorViewModels:
DbSet <IdentityRole> allRoles = dbFactory.Roles;
foreach (var role in allRoles)
{
// An EditorViewModel will be used by Editor Template:
var rvm = new SelectRoleEditorViewModel(role);
Roles.Add(rvm);
}
// Set the Selected property to true for those roles for
// which the current user is a member:
foreach (var userRole in user.Roles)
{
SelectRoleEditorViewModel checkUserRole =
Roles.Find(r => r.RoleId == userRole.Id);
checkUserRole.Selected = true;
}
}
public void Integration_Email_EF_Test_concurrency_exception()
{
// Arrange
var db1 = new MacheteContext();
var e1 = new Email()
{
emailFrom = "*****@*****.**",
emailTo = "*****@*****.**",
subject = "testing",
body = "testing",
statusID = Email.iReadyToSend,
datecreated = DateTime.Now,
dateupdated = DateTime.Now
};
db1.Emails.Add(e1);
db1.SaveChanges(); // initial save, context 1
var db2 = new MacheteContext();
var e2 = db2.Emails.Find(e1.ID);
e2.statusID = Email.iSending;
db2.SaveChanges(); // context 2 saves on top of context 1
e1.statusID = Email.iPending;
// Act
db1.SaveChanges(); // context 1 tries to save again, throws exception
}
public static void Initialize(MacheteContext context)
{
if (!context.TransportProviders.Any())
{
context.Database.ExecuteSqlCommand(@"insert into dbo.TransportProviders
( [key], text_EN, text_ES, defaultAttribute, sortorder, active, datecreated, dateupdated, Createdby, Updatedby )
select [key], text_EN, text_ES, selected, sortorder, active, datecreated, dateupdated, Createdby, Updatedby
from dbo.Lookups
where category = 'transportmethod'");
context.SaveChanges();
}
if (!context.TransportProviderAvailabilities.Any())
{
var providers = context.TransportProviders.ToList();
foreach (var provider in providers)
{
provider.AvailabilityRules = new List <TransportProviderAvailabilities>();
for (var dayOfWeek = 0; dayOfWeek < 7; dayOfWeek++)
{
provider.AvailabilityRules.Add(new TransportProviderAvailabilities {
transportProviderID = provider.ID,
day = dayOfWeek,
available = dayOfWeek != 0 || (provider.key == "transport_pickup"),
datecreated = DateTime.Now,
dateupdated = DateTime.Now,
createdby = "Init T. Script",
updatedby = "Init T. Script"
});
}
}
context.SaveChanges();
}
}
//
//
public static void Initialize(MacheteContext db)
{
cache = MemoryCache.Default;
DB = db;
FillCache();
}
/// <summary>
///
/// </summary>
/// <param name="personID"></param>
/// <param name="q"></param>
/// <param name="asRepo"></param>
public static void getUnassociated(int personID, ref IQueryable <Activity> q, MacheteContext db)
{
var aRepo = db.Activities.AsNoTracking().AsQueryable();
var asRepo = db.ActivitySignins.AsNoTracking().AsQueryable();
//
//SELECT extent1.* FROM [dbo].[Activities] AS [Extent1]
//LEFT OUTER JOIN [dbo].[ActivitySignins] AS [Extent2] ON
// ([Extent1].[ID] = [Extent2].[activityID]) AND
// ([Extent2].[WorkerID] = <personID> )
//WHERE [Extent2].[activityID] IS NULL
q = from b in aRepo
join aa in
// joins activities (a) to activity signins (az)
// where az.personID
(from a in q
join az in asRepo on a.ID equals az.activityID into grouped
from az2 in grouped.DefaultIfEmpty()
where az2.personID == personID
select a)
on b.ID equals aa.ID into h
from i in h.DefaultIfEmpty()
where i == null
select b;
}
private static void recInitialize(MacheteContext DB)
{
Person p1 = (Person)Records.person.Clone();
p1.Worker = (Worker)Records.worker.Clone();
p1.Worker.dwccardnum = 30040; p1.Worker.skill1 = 62;
p1.Worker.memberStatus = Worker.iActive;
p1.Worker.memberexpirationdate = DateTime.Now.AddDays(-1);
p1.Worker.Person = p1;
DB.Persons.Add(p1);
DB.Workers.Add(p1.Worker);
Person p2 = (Person)Records.person.Clone();
DB.Persons.Add(p2); p2.Worker = (Worker)Records.worker.Clone();
p2.Worker.dwccardnum = 30041;
p2.Worker.memberStatus = Worker.iActive;
p2.Worker.memberexpirationdate = DateTime.Now.AddDays(1);
Person p3 = (Person)Records.person.Clone();
DB.Persons.Add(p3); p3.Worker = (Worker)Records.worker.Clone();
p3.Worker.dwccardnum = 30042;
p3.Worker.memberReactivateDate = DateTime.Now.AddDays(-1);
p3.Worker.memberStatus = Worker.iSanctioned;
p3.Worker.memberexpirationdate = DateTime.Now.AddDays(1);
DB.SaveChanges();
}
//
//
public static void Refresh(MacheteContext db)
{
FillCache(db);
}
//
//
private static void FillCache(MacheteContext db)
{
DB = db;
FillCache();
}
public MacheteRoleStore(MacheteContext assignedContext, bool isSeedInstance)
{
_dataContext = assignedContext;
_roles = _dataContext.Roles;
}
//public void Set(MacheteContext context)
//{
// dataContext = context;
//}
protected override void DisposeCore()
{
if (dataContext != null)
{
dataContext.Dispose();
dataContext = null;
}
}
public MacheteContext Get()
{
if (dataContext == null)
{
if (connString == null)
{
dataContext = new MacheteContext();
}
else
{
dataContext = new MacheteContext(connString);
}
}
//log_connection_count();
return dataContext;
}
public UnitOfWork(IDatabaseFactory databaseFactory)
{
this.databaseFactory = databaseFactory;
dataContext = databaseFactory.Get();
}
//
//
public static void Initialize(MacheteContext context)
{
_cache.ForEach(u => {
u.datecreated = DateTime.Now;
u.dateupdated = DateTime.Now;
u.Createdby = "Init T. Script";
u.Updatedby = "Init T. Script";
context.Lookups.Add(u);
});
context.Commit();
}
public void Commit()
{
DataContext.SaveChanges();
databaseFactory.Dispose();
dataContext = null;
}
public MacheteRoleStore(IDatabaseFactory factory)
{
_dataContext = factory.Get();
_roles = _dataContext.Roles;
}
public MacheteContext Get()
{
var sb = new StringBuilder();
if (dataContext == null)
{
if (connString == null)
{
dataContext = new MacheteContext();
}
else
{
dataContext = new MacheteContext(connString);
}
}
var conn1 = (dataContext as System.Data.Entity.DbContext).Database.Connection;
var objid1 = field.GetValue(conn1);
sb.AppendFormat("DatabaseFactory SqlConnection # [{0}], Conn: {1}",
objid1.ToString(),
connString);
Debug.WriteLine(sb.ToString());
return dataContext;
}
