public IEnumerable <TrailerInformationRecord> FindTrailersInfoForUser(IUserIdentity identity)
{
// First, check if this user exists it the database. If not, this is for prototyping, so
// we randomly assign the user to have a few records per email address
if (!Exists(identity))
{
logger.Debug("User {0} does not exist. Adding to the Trailer information", identity.UserName);
AssignUserTrailerInfo(identity, 3);
}
// The email claim can be a string or an array of email addresses
using (var conn = SimpleDbConnection())
{
return(conn.Query <TrailerInformationRecord>(@"
SELECT
TrailerInformation.trailer_id as Id,
trailer_description as Description,
make as Make,
model as Model,
trailer_type as TrailerType,
serial_number as SerialNumber,
license_number as License,
trailer_state as State,
empty_weight as EmptyWeight,
registered_weight as RegisteredWeight,
regulation_weight as RegulationWeight
FROM TrailerInformation
INNER JOIN Users
ON TrailerInformation.trailer_id = Users.trailer_id
WHERE user_email IN @Emails
",
new { Emails = identity.EmailAddresses() }
));
}
}
public IEnumerable <InsuranceInformationRecord> FindInsuranceInfoForUser(IUserIdentity identity)
{
// First, check if this user exists it the database. If not, this is for prototyping, so
// we randomly assign the user to have a few records per email address
if (!Exists(identity))
{
logger.Debug("User {0} does not exist. Adding to the insurance information", identity.UserName);
AssignUserInsuranceInfo(identity, 3);
}
// The email claim can be a string or an array of email addresses
using (var conn = SimpleDbConnection())
{
return(conn.Query <InsuranceInformationRecord>(@"
SELECT
InsuranceInformation.insurance_id as Id,
provider_name AS ProviderName,
agency_address AS AgencyAddress,
policy_number AS PolicyNumber,
insured_amount AS InsuredAmount
FROM InsuranceInformation
INNER JOIN Users
ON InsuranceInformation.insurance_id = Users.insurance_id
WHERE user_email IN @Emails
",
new { Emails = identity.EmailAddresses() }
));
}
}
public dynamic CreatePermitForUser(IUserIdentity identity, string blob)
{
// First, make sure the user exists. If not, create a new user record
var userId = FindUserId(identity);
if (!userId.HasValue)
{
userId = CreateEmptyUser();
}
// Now, check to make sure that all of the user's email addresses are associated with them
var emails = identity.EmailAddresses();
var dbEmails = GetEmailAddressesForUser(userId.Value);
// If there are any emails that are missing, add them
AddEmailAddressesToUser(userId.Value, emails.Where(e => !dbEmails.Contains(e)).ToList());
// Finally we can create the actual permit application
using (var conn = SimpleDbConnection())
{
var appId = conn.Query <int>(@"
INSERT INTO Applications (application_data, application_status) VALUES (@Data, @Status); SELECT last_insert_rowid()
", new { Data = blob, Status = PermitStatus.NEW }).First();
conn.Execute(@"INSERT INTO UserApplications (user_id, application_id) VALUES (@UserId, @AppId)", new { UserId = userId.Value, AppId = appId });
}
return(null);
}
public IEnumerable <VehicleInformationRecord> FindVehiclesInfoForUser(IUserIdentity identity)
{
// First, check if this user exists it the database. If not, this is for prototyping, so
// we randomly assign the user to have a few records per email address
if (!Exists(identity))
{
logger.Debug("User {0} does not exist. Adding to the vehicle information", identity.UserName);
AssignUserVehicleInfo(identity, 3);
}
// The email claim can be a string or an array of email addresses
using (var conn = SimpleDbConnection())
{
return(conn.Query <VehicleInformationRecord>(@"
SELECT
vehicle_year as Year,
make as Make,
model as Model,
vehicle_type as Type,
license_number as License,
vehicle_state as State,
serial_number as SerialNumber,
usdot_number as USDOTNumber,
empty_weight as EmptyWeight,
registered_weight as RegisteredWeight
FROM VehicleInformation
INNER JOIN Users
ON VehicleInformation.vehicle_id = Users.vehicle_id
WHERE user_email IN @Emails
",
new { Emails = identity.EmailAddresses() }
));
}
}
public IEnumerable <CompanyInformationRecord> FindCompanyInfoForUser(IUserIdentity identity)
{
// First, check if this user exists it the database. If not, this is for prototyping, so
// we randomly assign the user to have a few companies per email address
if (!Exists(identity))
{
logger.Debug("User {0} does not exist. Adding to the company information", identity.UserName);
AssignUserToCompanies(identity, 3);
}
// The email claim can be a string or an array of email addresses
using (var conn = SimpleDbConnection())
{
return(conn.Query <CompanyInformationRecord>(@"
SELECT
CompanyInformation.company_id as Id,
company_name AS CompanyName,
email AS Email,
contact AS Contact,
phone AS Phone,
fax AS Fax,
cell AS Cell,
bill_to AS BillTo,
billing_address AS BillingAddress
FROM CompanyInformation
INNER JOIN Users
ON CompanyInformation.company_id = Users.company_id
WHERE user_email IN @Emails
",
new { Emails = identity.EmailAddresses() }
));
}
}
private bool Exists(IUserIdentity identity)
{
using (var conn = SimpleDbConnection())
{
return(conn.Query <dynamic>(@"
SELECT
1
FROM Users
WHERE user_email IN @Emails
",
new { Emails = identity.EmailAddresses() }
).Any());
}
}
private int?FindUserId(IUserIdentity identity)
{
using (var conn = SimpleDbConnection())
{
return(conn.Query <int?>(@"
SELECT U.user_id
FROM Emails E
INNER JOIN Users U
ON E.user_id = U.user_id
WHERE U.is_active = 1 AND E.user_email IN @Emails
",
new { Emails = identity.EmailAddresses() }
).FirstOrDefault());
}
}
public List <dynamic> FindPermitsForUser(IUserIdentity identity)
{
using (var conn = SimpleDbConnection())
{
var emailAddresses = identity.EmailAddresses();
return(conn.Query <dynamic>(@"
SELECT A.application_id AS Id, A.application_status AS Status, A.application_data AS Data
FROM Applications A
LEFT JOIN UserApplications UA ON A.application_id = UA.application_id
LEFT JOIN Emails E ON UA.user_id = E.user_id
LEFT JOIN Users U ON UA.user_id = U.user_id
WHERE U.is_active = 1 AND E.user_email IN (@Addresses)
", new { Addresses = emailAddresses })
.ToList());
}
}
public IEnumerable <AxleInformationRecord> FindAxlesInfoForUser(IUserIdentity identity)
{
// First, check if this user exists it the database. If not, this is for prototyping, so
// we randomly assign the user to have a few records per email address
if (!Exists(identity))
{
logger.Debug("User {0} does not exist. Adding to the Axle information", identity.UserName);
AssignUserAxleInfo(identity, 3);
}
// The email claim can be a string or an array of email addresses
using (var conn = SimpleDbConnection())
{
return(conn.Query <AxleInformationRecord>(@"
SELECT
AxleInformation.axle_id as Id,
axle_description as Description,
weight_per_axle as WeightPerAxle,
description_summary as DescriptionSummary,
axle_count as AxleCount,
group_count as GroupCount,
approx_axle_length as ApproxAxleLength,
axle_length as AxleLength,
max_axle_width as MaxAxleWidth,
axle_group_summary as AxleGroupSummary,
axles_per_group as AxlesPerGroup,
axle_group_tire_type as GroupTireType,
axle_group_width as GroupWidth,
axle_operating_weights as OperatingWeights,
axle_group_weight as GroupWeight,
axle_group_max_width as GroupMaxWidth,
axle_group_total_weight as GroupTotalWeight,
axle_group_distance as GroupDistance
FROM AxleInformation
INNER JOIN Users
ON AxleInformation.axle_id = Users.axle_id
WHERE user_email IN @Emails
",
new { Emails = identity.EmailAddresses() }
));
}
}
private void AssignUserAxleInfo(IUserIdentity identity, int count)
{
// We know the test data has primary keys from 1 .. N
var numRecords = AxleCount();
using (var conn = SimpleDbConnection())
{
foreach (var email in identity.EmailAddresses())
{
// Draw the indicies 0 to n-1 and then add one to convert to a PK. Add in the email address, too
var values = DrawWithoutReplacement(numRecords, count).Select(x => new { Email = email, AxleId = x + 1 });
// Insert into the join table
conn.Execute(@"
INSERT INTO Users (user_email, axle_id)
VALUES (@Email, @AxleId)
", values
);
}
}
}
public IEnumerable <TruckInformationRecord> FindTrucksInfoForUser(IUserIdentity identity)
{
// First, check if this user exists it the database. If not, this is for prototyping, so
// we randomly assign the user to have a few records per email address
if (!Exists(identity))
{
logger.Debug("User {0} does not exist. Adding to the Truck information", identity.UserName);
AssignUserTruckInfo(identity, 3);
}
// The email claim can be a string or an array of email addresses
using (var conn = SimpleDbConnection())
{
return(conn.Query <TruckInformationRecord>(@"
SELECT
TruckInformation.truck_id as Id,
gross_weight as GrossWeight,
empty_weight as EmptyWeight,
registered_weight as RegisteredWeight,
regulation_weight as RegulationWeight,
height as Height,
width as Width,
truck_length as Length,
front_overhang as FrontOverhang,
rear_overhang as RearOverhang,
left_overhang as LeftOverhang,
right_overhang as RightOverhang,
diagram as Diagram
FROM TruckInformation
INNER JOIN Users
ON TruckInformation.truck_id = Users.truck_id
WHERE user_email IN @Emails
",
new { Emails = identity.EmailAddresses() }
));
}
}