public Domain.TemperatureSensor Save(TemperatureSensorSaverInput input, Domain.Building building)
{
return(_dbExecutor.Query(c =>
{
c.Attach(building);
Domain.TemperatureSensor temperatureSensor = null;
if (input.SensorId.HasValue)
{
temperatureSensor = c.TemperatureSensors.Find(input.SensorId.Value);
}
if (temperatureSensor == null)
{
temperatureSensor = new Domain.TemperatureSensor
{
BuildingId = building.BuildingId
};
building.TemperatureSensors.Add(temperatureSensor);
}
temperatureSensor.Name = input.Name;
temperatureSensor.ProtocolName = input.ProtocolName;
temperatureSensor.InputDescriptor = Newtonsoft.Json.JsonConvert.SerializeObject(input.InputDescriptor);
c.SaveChanges();
return temperatureSensor;
}));
}
public Domain.PowerZone Save(PowerZoneSaverInput input, Domain.Building building)
{
return(_dbExecutor.Query(c =>
{
c.Attach(building);
Domain.PowerZone powerZone = null;
if (input.PowerZoneId.HasValue)
{
powerZone = building.PowerZones.SingleOrDefault(z => z.PowerZoneId == input.PowerZoneId.Value);
}
if (powerZone == null)
{
powerZone = new Domain.PowerZone {
BuildingId = building.BuildingId
};
building.PowerZones.Add(powerZone);
}
powerZone.Name = input.Name;
powerZone.MaxUsage = input.PowerLimitValue;
powerZone.UsageUnit = input.PowerLimitUnit;
powerZone.RoundRobinIntervalMinutes = input.RoundRobinIntervalMinutes;
powerZone.SwitchDelayBetweenOutputsSeconds = input.SwitchDelay;
MergeHeaters(input, c, powerZone);
c.SaveChanges();
return powerZone;
}));
}
public Domain.Zone Save(ZoneSaverInput input, Domain.Building building)
{
return(_dbExecutor.Query(c =>
{
c.Attach(building);
Domain.Zone zone = null;
if (input.ZoneId.HasValue)
{
zone = building.Zones.SingleOrDefault(z => z.ZoneId == input.ZoneId.Value);
TryClearSchedule(input, zone);
}
if (zone == null)
{
zone = new Domain.Zone {
BuildingId = building.BuildingId
};
building.Zones.Add(zone);
}
zone.Name = input.Name;
zone.NameDashboardEn = input.NameDashboardEn ?? input.Name;
MergeTemperatureControlledZone(input, c, zone);
MergeHeaters(input, c, zone);
c.SaveChanges();
return zone;
}));
}
internal static IEnumerable <Package> GetPackages(
IDbExecutor dbExecutor,
int packageRegistrationKey)
{
return(dbExecutor.Query <Package>(
"SELECT pr.Id, p.Version FROM Packages p JOIN PackageRegistrations PR on pr.[Key] = p.PackageRegistrationKey WHERE pr.[Key] = @packageRegistrationKey",
new { packageRegistrationKey }));
}
internal static User GetUser(
IDbExecutor dbExecutor,
string username)
{
var user = dbExecutor.Query <User>(
"SELECT u.[Key], u.Username, u.EmailAddress, u.UnconfirmedEmailAddress FROM Users u WHERE u.Username = @username",
new { username }).SingleOrDefault();
if (user != null)
{
user.PackageRegistrationIds = dbExecutor.Query <string>(
"SELECT r.[Id] FROM PackageRegistrations r INNER JOIN PackageRegistrationOwners o ON o.PackageRegistrationKey = r.[Key] WHERE o.UserKey = @userKey AND NOT EXISTS(SELECT * FROM PackageRegistrationOwners other WHERE other.PackageRegistrationKey = r.[Key] AND other.UserKey != @userKey)",
new { userkey = user.Key });
}
return(user);
}
internal static PackageRegistration GetPackageRegistration(
IDbExecutor dbExecutor,
string id)
{
return(dbExecutor.Query <PackageRegistration>(
"SELECT [Key], Id FROM PackageRegistrations WHERE Id = @id",
new { id }).SingleOrDefault());
}
internal static Package GetPackage(
IDbExecutor dbExecutor,
string id,
string version)
{
return(dbExecutor.Query <Package>(
"SELECT p.[Key], pr.Id, p.Version, p.NormalizedVersion, p.Hash FROM Packages p JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey WHERE pr.Id = @id AND p.Version = @version",
new { id, version }).SingleOrDefault());
}
public static Db GetDatabase(
IDbExecutor dbExecutor,
string databaseName)
{
var dbs = dbExecutor.Query <Db>(
"SELECT name, state FROM sys.databases WHERE name = @databaseName",
new { databaseName });
return(dbs.SingleOrDefault());
}
public static bool DatabaseExistsAndIsOnline(
IDbExecutor dbExecutor,
string restoreName)
{
var backupDbs = dbExecutor.Query<Database>(
"SELECT name, state FROM sys.databases WHERE name = @restoreName AND state = @state",
new { restoreName, state = OnlineState })
.OrderByDescending(database => database.Name);
return backupDbs.FirstOrDefault() != null;
}
public Domain.User Provide(Expression <Func <Domain.User, bool> > predicate)
{
return(_dbExecutor.Query(c =>
{
return c.Users
.Include(u => u.UserPermissions)
.Where(x => x.IsActive)
.Where(predicate)
.SingleOrDefault();
}));
}
public static bool DatabaseExistsAndIsOnline(
IDbExecutor dbExecutor,
string restoreName)
{
var backupDbs = dbExecutor.Query <Db>(
"SELECT name, state FROM sys.databases WHERE name = @restoreName AND state = @state",
new { restoreName, state = OnlineState })
.OrderByDescending(database => database.Name);
return(backupDbs.FirstOrDefault() != null);
}
public int Create(IDbExecutor dbExecutor, Driver driver)
{
var insertDriverSql = $@"
insert into Drivers(FullName, Phone)
values (""{driver.FullName}"", ""{driver.Phone}"");
select last_insert_rowid();
";
var driverId = dbExecutor.Query <int>(insertDriverSql).First();
return(driverId);
}
public Domain.Building ProvideDefault()
{
return(_dbExecutor.Query(c => c.Buildings
.Include(b => b.DigitalInputs)
.Include(b => b.Heaters)
.ThenInclude(h => h.DigitalOutput)
.Include(b => b.PowerZones)
.Include(b => b.TemperatureSensors)
.Include(b => b.Zones)
.ThenInclude(z => z.TemperatureControlledZone)
.Include(b => b.Zones)
.ThenInclude(z => z.Schedule)
.SingleOrDefault(x => x.IsDefault)));
}
public Domain.Heater Save(HeaterSaverInput input, Domain.Building building)
{
return(_dbExecutor.Query(c =>
{
c.Attach(building);
Domain.Heater heater = null;
if (input.HeaterId.HasValue)
{
heater = c.Heaters.Find(input.HeaterId.Value);
}
if (heater == null)
{
heater = new Domain.Heater {
BuildingId = building.BuildingId, DigitalOutput = new DigitalOutput()
};
building.Heaters.Add(heater);
}
else
{
if (heater.PowerZoneId.HasValue &&
heater.UsageUnit != input.UsageUnit)
{
heater.PowerZone = null;
heater.PowerZoneId = null;
Logger.Info(Localization.NotificationMessage.HeaterRemovedFromPowerZoneDueToUsageUnitChange.FormatWith(input.Name));
}
}
heater.Name = input.Name;
heater.UsageUnit = input.UsageUnit;
heater.UsagePerHour = input.UsagePerHour;
heater.MinimumStateChangeIntervalSeconds = input.MinimumStateChangeIntervalSeconds;
heater.DigitalOutput.ProtocolName = input.PowerOutputProtocolName;
heater.DigitalOutput.OutputDescriptor = Newtonsoft.Json.JsonConvert.SerializeObject(input.PowerOutputDescriptor);
heater.DigitalOutput.DeviceId = -1;
c.SaveChanges();
return heater;
}));
}
public void Get__DriverExistsInStorage__ReturnThisDriver_V1()
{
// Arrange
var driver = DriverFake.Generate();
var insertDriverSql = $@"
insert into Drivers(FullName, Phone)
values (""{driver.FullName}"", ""{driver.Phone}"");
select last_insert_rowid();
";
var driverId = _dbExecutor.Query <int>(insertDriverSql).First();
// Act
var foundDriver = _query.Get(driverId);
// Assert
Assert.NotNull(foundDriver);
Assert.Equal(driver.Phone, foundDriver.Phone);
Assert.Equal(driver.FullName, foundDriver.FullName);
}
public Domain.User Update(UserUpdaterInput input)
{
return(_dbExecutor.Query(c =>
{
var user = c.Users
.Include(u => u.UserPermissions)
.SingleOrDefault(u => u.UserId == input.UserId);
if (user != null)
{
user.PasswordHash = input.PasswordHash ?? user.PasswordHash;
user.QuickLoginPinHash = input.PinHash ?? user.QuickLoginPinHash;
var permissionsToRemove = new HashSet <Permission>(user.UserPermissions.Select(x => x.Permission));
foreach (var permission in input.Permissions)
{
if (permissionsToRemove.Contains(permission))
{
permissionsToRemove.Remove(permission);
}
else
{
user.UserPermissions.Add(new UserPermission {
Permission = permission
});
}
}
foreach (var permissionToRemove in permissionsToRemove)
{
user.UserPermissions.Remove(x => x.Permission == permissionToRemove);
}
c.SaveChanges();
}
return user;
}));
}
public static Db GetDatabase(
IDbExecutor dbExecutor,
string databaseName)
{
var dbs = dbExecutor.Query<Db>(
"SELECT name, state FROM sys.databases WHERE name = @databaseName",
new { databaseName });
return dbs.SingleOrDefault();
}
internal static User GetUser(
IDbExecutor dbExecutor,
string username)
{
var user = dbExecutor.Query<User>(
"SELECT u.[Key], u.Username, u.EmailAddress, u.UnconfirmedEmailAddress FROM Users u WHERE u.Username = @username",
new { username }).SingleOrDefault();
if (user != null)
{
user.PackageRegistrationIds = dbExecutor.Query<string>(
"SELECT r.[Id] FROM PackageRegistrations r INNER JOIN PackageRegistrationOwners o ON o.PackageRegistrationKey = r.[Key] WHERE o.UserKey = @userKey AND NOT EXISTS(SELECT * FROM PackageRegistrationOwners other WHERE other.PackageRegistrationKey = r.[Key] AND other.UserKey != @userKey)",
new { userkey = user.Key });
}
return user;
}
internal static IEnumerable<Package> GetPackages(
IDbExecutor dbExecutor,
int packageRegistrationKey)
{
return dbExecutor.Query<Package>(
"SELECT pr.Id, p.Version FROM Packages p JOIN PackageRegistrations PR on pr.[Key] = p.PackageRegistrationKey WHERE pr.[Key] = @packageRegistrationKey",
new { packageRegistrationKey });
}
internal static PackageRegistration GetPackageRegistration(
IDbExecutor dbExecutor,
string id)
{
return dbExecutor.Query<PackageRegistration>(
"SELECT [Key], Id FROM PackageRegistrations WHERE Id = @id",
new { id }).SingleOrDefault();
}
internal static Package GetPackage(
IDbExecutor dbExecutor,
string id,
string version)
{
return dbExecutor.Query<Package>(
"SELECT p.[Key], pr.Id, p.Version, p.NormalizedVersion, p.Hash FROM Packages p JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey WHERE pr.Id = @id AND p.Version = @version",
new { id, version }).SingleOrDefault();
}
public TItem Read(int id)
{
return(_dbExecutor.Query(c => c.Set <TItem>().Find(id)));
}