public bool Create(User user)
{
if (user == null) throw new ArgumentNullException("user");
var query = new DapperQuery(ConnectionString)
{
Sql = @"Insert into dbo.[User]([name], [Hash], [Salt], [hintquestion], [hintanswer])
Values(@Name, @Hash, @Salt, @HintQuestion, @HintAnswer)",
Parameters = new {
Name = user.Name,
Hash = user.Hash,
Salt = user.Salt,
HintQuestion = user.HintQuestion,
HintAnswer = user.HintAnswer
}
};
try
{
QueryExecutor.Execute(query);
return true;
}
catch (Exception)
{
return false;
}
}
public DeviceAuth GetDeviceAuth(string auth)
{
var query = new DapperQuery(ConnectionString)
{
Sql = @"SELECT [Code], [IsValid] FROM DeviceAuthentication with (NOLOCK) where [Code] = @Code",
Parameters = new { Code = auth }
};
return QueryExecutor.Query<DeviceAuth>(query).FirstOrDefault();
}
public User GetByUserName(string userName)
{
if (userName == null) throw new ArgumentNullException("userName");
var query = new DapperQuery(ConnectionString)
{
Sql = "Select Id, Name, Salt, Hash, HintQuestion, HintAnswer from dbo.[User] with (nolock) where [Name] = @Name",
Parameters = new { Name = userName }
};
return QueryExecutor.Query<User>(query).FirstOrDefault();
}
public Authentication GetByCode(string authCode)
{
var select = new DapperQuery(ConnectionString)
{
Sql = @"SELECT [Id], [Code], [UserId], [Truck], [Created], [Updated]
FROM dbo.[UserAuthentication] with (NOLOCK)
WHERE [Code] = @Code",
Parameters = new { Code = authCode }
};
var result = QueryExecutor.Query<Authentication>(select).FirstOrDefault();
return result;
}
public Delivery GetById(int id)
{
var query = new DapperQuery(ConnectionString)
{
Sql = "SELECT" + _deliveryFields + @"
FROM Delivery with (NOLOCK)
WHERE [Id] = @Id",
Parameters = new { Id = id }
};
var delivery = QueryExecutor.Query<Delivery>(query).FirstOrDefault();
if (delivery == null) return null;
delivery.Items = GetItemsForDelivery(delivery.Id);
return delivery;
}
public void SetUpdated(string authCode, DateTime date)
{
var update = new DapperQuery(ConnectionString)
{
Sql = @"UPDATE dbo.[UserAuthentication] Set [Updated] = @Updated
WHERE [Code] = @Code",
Parameters = new
{
Code = authCode,
Updated = date
}
};
QueryExecutor.Execute(update);
}
public bool Save(DeviceAuthRequest auth)
{
var query = new DapperQuery(ConnectionString)
{
Sql = @"INSERT INTO [DeviceAuthentication] ([Code], [Name]) VALUES (@Code, @Name)",
Parameters = new { Code = auth.AuthCode, Name = auth.Name }
};
try
{
QueryExecutor.Execute(query);
}
catch (Exception)
{
return false;
}
return true;
}
public AuthenticationResponse Save(Authentication authentication)
{
authentication.Code = Guid.NewGuid().ToString();
var query = new DapperQuery(ConnectionString)
{
Sql = @"INSERT INTO UserAuthentication([Code], [UserId], [Truck])
VALUES (@Code, @UserId, @Truck)",
Parameters = new
{
Code = authentication.Code,
UserId = authentication.UserId,
Truck = authentication.Truck
}
};
QueryExecutor.Execute(query);
return authentication;
}
public IEnumerable<Delivery> GetByTruck(int truck)
{
var query = new DapperQuery(ConnectionString)
{
Sql = "SELECT" + _deliveryFields + @"
FROM Delivery with (NOLOCK)
WHERE [Truck] = @Truck
AND [Completed] is null",
Parameters = new { Truck = truck }
};
var deliveries = QueryExecutor.Query<Delivery>(query);
foreach (var delivery in deliveries)
{
delivery.Items = GetItemsForDelivery(delivery.Id);
}
return deliveries;
}
public Delivery Complete(Delivery delivery)
{
var query = new DapperQuery(ConnectionString)
{
Sql = @"UPDATE [Delivery]
SET [Signature] = @Signature,
[Printed] = @Printed,
[Completed] = @Completed
WHERE [Id] = @Id",
Parameters = new
{
Id = delivery.Id,
Signature = delivery.Signature,
Printed = delivery.Printed,
Completed = DateTime.UtcNow
}
};
QueryExecutor.Execute(query);
foreach (var item in delivery.Items)
{
SaveDeliveredQty(item);
}
return GetById(delivery.Id);
}
public bool Update(User user)
{
if (user == null) throw new ArgumentNullException("user");
var query = new DapperQuery(ConnectionString)
{
Sql = @"Update dbo.[User] set [Hash] = @Hash, [Salt] = @Salt, [hintquestion] = @HintQuestion, [hintanswer] = @HintAnswer
WHERE Id = @Id",
Parameters = new {
Id = user.Id,
Salt = user.Salt,
Hash = user.Hash,
HintQuestion = user.HintQuestion,
HintAnswer = user.HintAnswer
}
};
try
{
QueryExecutor.Execute(query);
return true;
}
catch (Exception)
{
return false;
}
}
private void SaveDeliveredQty(Item item)
{
var query = new DapperQuery(ConnectionString)
{
Sql = @"UPDATE [DeliveryItem]
SET [Delivered] = @Delivered
WHERE [Id] = @Id",
Parameters = new
{
Id = item.Id,
Delivered = item.Delivered
}
};
QueryExecutor.Execute(query);
}
private IEnumerable<Item> GetItemsForDelivery(int deliveryId)
{
var query = new DapperQuery(ConnectionString)
{
Sql = "SELECT" + _itemFields + "FROM [DeliveryItem] with (NOLOCK) where [DeliveryId] = @DeliveryId",
Parameters = new { DeliveryId = deliveryId }
};
return QueryExecutor.Query<Item>(query);
}
public User GetById(int id)
{
var query = new DapperQuery(ConnectionString)
{
Sql = "Select Id, Name, Salt, Hash, HintQuestion, HintAnswer from dbo.[User] with (nolock) where [Id] = @Id",
Parameters = new { Id = id }
};
return QueryExecutor.Query<User>(query).FirstOrDefault();
}
