public static List<LocationModel> GetLocations(DSModel db, params uint[] locationIDs)
{
if (db == null)
throw new ArgumentNullException("db");
if (locationIDs.Length == 0)
return new List<LocationModel>();
string sql = @"
SELECT
l.*,
ToBool(0) AS IsChanged
FROM locations l
WHERE l.LocationID IN (@LocationID);";
sql = sql.Replace("@LocationID",string.Join<uint>(",",locationIDs));
var items = db.ExecuteQuery<LocationModel>(sql).ToList();
foreach (var i in items)
{
if (i.ConfirmationContactID.HasValue)
i.ConfirmationContact = ContactRepository.GetContacts(db, i.ConfirmationContactID.Value).FirstOrDefault();
if (i.InvoiceContactID.HasValue)
i.InvoiceContact = ContactRepository.GetContacts(db, i.InvoiceContactID.Value).FirstOrDefault();
if (i.DispatchContactID.HasValue)
i.DispatchContact = ContactRepository.GetContacts(db, i.DispatchContactID.Value).FirstOrDefault();
i.IsChanged = false;
}
return items;
}
/// <summary>
/// Gets all license notifications to be sent
/// </summary>
/// <param name="db"></param>
/// <param name="checkDate"></param>
/// <returns></returns>
public static List<NotificationModel> GetLicenseNotifications(DSModel db, DateTime checkDate = default(DateTime))
{
if (db == null)
throw new ArgumentNullException("db");
if (checkDate == DateTime.MinValue)
checkDate = DateTime.Now.Date;
return db.ExecuteQuery<NotificationModel>("CALL GetLicenseNotifications(@CheckDate);", new MySqlParameter("CheckDate", checkDate.Date)).ToList();
}
public static List<ContactModel> GetContacts(DSModel db, params uint[] contactIDs)
{
if (db == null)
throw new ArgumentNullException("db");
if (contactIDs.Length == 0)
return new List<ContactModel>();
string sql = @"
SELECT
c.*,
ToBool(0) AS IsChanged
FROM contacts c
WHERE c.ContactID IN (@ContactID);";
sql = sql.Replace("@ContactID", string.Join<uint>(",", contactIDs));
return db.ExecuteQuery<ContactModel>(sql).ToList();
}
public static List<FileBlobViewModel> GetViewBlobsByDriver(DSModel db, uint driverID)
{
if (db == null)
throw new ArgumentNullException("db");
string sql = @"
SELECT f.BlobID
, f.BlobName
, f.BlobDescription
, f.BlobExtension
, concat(round((length(f.BlobData) / 1024) / 1024, 2), ' MB') AS BlobSize
FROM
file_blobs f
WHERE
f.DriverID = @DriverID
ORDER BY
f.LastUpdateTime DESC;";
return db.ExecuteQuery<FileBlobViewModel>(sql, new MySqlParameter("DriverID", driverID)).ToList();
}
public static List<DispatchCatalogModel> GetDispatches(DSModel db, uint[] drivers = null, uint[] companies = null, uint[] locations = null, DateTime? fromDate = null, DateTime? toDate = null, bool? includeCancelled = null)
{
if (db == null)
throw new ArgumentNullException("db");
string sql = @"
SELECT
d.DispatchID,
d.DriverID,
d.CompanyID,
d.LocationID,
d.FromDateTime,
d.ToDateTime,
IF(d.HasTraining, c.TrainingTime, 0) AS TrainingTime,
IF(d.HasLunch, c.LunchTime, 0) AS LunchTime,
d.SpecialPayRate,
d.MiscCharge,
d.Note,
d.IsCancelled,
d.IsConfirmed,
d.HasLunch,
d.HasTraining,
d.UserID,
d.LastUpdateTime,
CONCAT(c.CompanyName, ' - ', c.CompanyCode) AS CompanyName,
CONCAT(dr.LastName, ', ', dr.FirstName) AS DriverName,
CONCAT(l.LocationName, ' - ', l.LocationCode) AS LocationName,
CONCAT(u.FirstName, ' ', u.LastName, ' (', u.UserName, ')') AS UserName
FROM dispatches d
INNER JOIN companies c
ON d.CompanyID = c.CompanyID
INNER JOIN drivers dr
ON d.DriverID = dr.DriverID
INNER JOIN locations l
ON d.LocationID = l.LocationID
INNER JOIN users u
ON d.UserID = u.UserID
WHERE 1 = 1
#DriverID AND d.DriverID IN (@DriverID)
#CompanyID AND d.CompanyID IN (@CompanyID)
#LocationID AND d.LocationID IN (@LocationID)
#FromDate AND d.FromDateTime >= DATE(@FromDate)
#ToDate AND d.ToDateTime <= DATE_ADD(DATE(@ToDate),INTERVAL 86399 SECOND)
#IsCancelled AND d.IsCancelled = @IsCancelled";
List<MySqlParameter> pars = new List<MySqlParameter>();
if (drivers != null && drivers.Length > 0)
{
sql = sql.Replace("#DriverID", string.Empty);
sql = sql.Replace("@DriverID", string.Join<uint>(",", drivers));
}
if (companies != null && companies.Length > 0)
{
sql = sql.Replace("#CompanyID", string.Empty);
sql = sql.Replace("@CompanyID", string.Join<uint>(",", companies));
}
if (locations != null && locations.Length > 0)
{
sql = sql.Replace("#LocationID", string.Empty);
sql = sql.Replace("@LocationID", string.Join<uint>(",", locations));
}
if (fromDate.HasValue && fromDate.GetValueOrDefault() != DateTime.MinValue)
{
sql = sql.Replace("#FromDate", string.Empty);
pars.Add(new MySqlParameter("FromDate", fromDate.Value.Date));
}
if (toDate.HasValue && toDate.GetValueOrDefault() != DateTime.MinValue)
{
sql = sql.Replace("#ToDate", string.Empty);
pars.Add(new MySqlParameter("ToDate", toDate.Value.Date));
}
if (includeCancelled.HasValue && includeCancelled.Value == false)
{
sql = sql.Replace("#IsCancelled", string.Empty);
pars.Add(new MySqlParameter("IsCancelled", false));
}
var items = db.ExecuteQuery<DispatchCatalogModel>(sql, pars.ToArray()).ToList();
items.ForEach(i => i.IsChanged = false);
return items;
}
public static List<DispatchModel> GetDispatches(DSModel db, params uint[] dispatchIDs)
{
if (db == null)
throw new ArgumentNullException("db");
if (dispatchIDs.Length == 0)
return new List<DispatchModel>();
string sql = @"
SELECT
d.DispatchID,
d.DriverID,
d.CompanyID,
d.LocationID,
d.FromDateTime,
d.ToDateTime,
IF(d.HasLunch, c.LunchTime, 0) AS LunchTime,
IF(d.HasTraining, c.TrainingTime, 0) AS TrainingTime,
d.SpecialPayRate,
d.MiscCharge,
d.Note,
d.IsCancelled,
d.IsConfirmed,
d.HasLunch,
d.HasTraining,
d.UserID,
d.LastUpdateTime,
CONCAT(u.FirstName, ' ', u.LastName, ' (', u.Username, ')') AS UserName
FROM dispatches d
INNER JOIN users u
ON d.UserID = u.UserID
INNER JOIN companies c
ON d.CompanyID = c.CompanyID
WHERE d.DispatchID IN (@DispatchID)
GROUP BY d.DispatchID;";
sql = sql.Replace("@DispatchID", string.Join<uint>(",", dispatchIDs));
var items = db.ExecuteQuery<DispatchModel>(sql).ToList();
items.ForEach(i => i.IsChanged = false);
return items;
}
public static List<DriverModel> FindDrivers(DSModel db, DriverFilterModel filter)
{
if (db == null)
throw new ArgumentNullException("db");
if (filter == null)
throw new ArgumentNullException("filter");
List<MySqlParameter> parameters = new List<MySqlParameter>();
string sql = SqlCache.Get(db, "drivers catalog load");
if (!string.IsNullOrWhiteSpace(filter.DriverCode))
{
sql = sql.Replace("#DriverCode", string.Empty);
parameters.Add(new MySqlParameter("DriverCode", filter.DriverCode + "%"));
}
if (!string.IsNullOrWhiteSpace(filter.FirstName))
{
sql = sql.Replace("#FirstName", string.Empty);
parameters.Add(new MySqlParameter("FirstName", filter.FirstName + "%"));
}
if (!string.IsNullOrWhiteSpace(filter.SecondName))
{
sql = sql.Replace("#SecondName", string.Empty);
parameters.Add(new MySqlParameter("SecondName", filter.SecondName + "%"));
}
if (!string.IsNullOrWhiteSpace(filter.LastName))
{
sql = sql.Replace("#LastName", string.Empty);
parameters.Add(new MySqlParameter("LastName", filter.LastName + "%"));
}
if (!string.IsNullOrWhiteSpace(filter.CellPhone))
{
sql = sql.Replace("#CellPhone", string.Empty);
parameters.Add(new MySqlParameter("CellPhone", filter.CellPhone + "%"));
}
if (!string.IsNullOrWhiteSpace(filter.Email))
{
sql = sql.Replace("#Email", string.Empty);
parameters.Add(new MySqlParameter("Email", filter.Email + "%"));
}
if (!string.IsNullOrWhiteSpace(filter.LicenseID))
{
sql = sql.Replace("#LicenseID", string.Empty);
sql = sql.Replace("@LicenseID", filter.LicenseID);
}
if (!string.IsNullOrWhiteSpace(filter.PermitID))
{
sql = sql.Replace("#PermitID", string.Empty);
sql = sql.Replace("@PermitID", filter.PermitID);
}
string isEnabled = (filter.IncludeDisabled ? "0,1" : "1");
sql = sql.Replace("@IsEnabled", isEnabled);
parameters.Add(new MySqlParameter("TodayDate", DateTime.Now.Date));
return db.ExecuteQuery<DriverModel>(sql, parameters.ToArray())
.ToList();
}
public static List<InvoiceCatalogModel> FindInvoices(DSModel db, InvoiceCatalogFilter filter)
{
if (db == null)
throw new ArgumentNullException("db");
if (filter == null)
throw new ArgumentNullException("filter");
string sql = SqlCache.Get(db, "invoice catalog load");
List<MySqlParameter> par = new List<MySqlParameter>();
if (!string.IsNullOrWhiteSpace(filter.InvoiceNumber))
{
sql = sql.Replace("#InvoiceNumber", string.Empty);
par.Add(new MySqlParameter("InvoiceNumber", filter.InvoiceNumber + "%"));
}
if (filter.IssuedFrom.HasValue && filter.IssuedFrom.Value != DateTime.MinValue)
{
sql = sql.Replace("#IssuedFrom", string.Empty);
par.Add(new MySqlParameter("IssuedFrom", filter.IssuedFrom.Value.Date));
}
if (filter.IssuedTo.HasValue && filter.IssuedTo.Value != DateTime.MinValue)
{
sql = sql.Replace("#IssuedTo", string.Empty);
par.Add(new MySqlParameter("IssuedTo", filter.IssuedTo.Value.Date));
}
if (filter.PeriodFrom.HasValue && filter.PeriodFrom.Value != DateTime.MinValue)
{
sql = sql.Replace("#PeriodFrom", string.Empty);
par.Add(new MySqlParameter("PeriodFrom", filter.PeriodFrom.Value.Date));
}
if (filter.PeriodTo.HasValue && filter.PeriodTo.Value != DateTime.MinValue)
{
sql = sql.Replace("#PeriodTo", string.Empty);
par.Add(new MySqlParameter("PeriodTo", filter.PeriodTo.Value.Date));
}
if (!string.IsNullOrWhiteSpace(filter.CompanyID))
{
sql = sql.Replace("#CompanyID", string.Empty);
sql = sql.Replace("@CompanyID", filter.CompanyID);
}
if (!string.IsNullOrWhiteSpace(filter.LocationID))
{
sql = sql.Replace("#LocationID", string.Empty);
sql = sql.Replace("@LocationID", filter.LocationID);
}
return db.ExecuteQuery<InvoiceCatalogModel>(sql, par.ToArray()).ToList();
}
public static void CalculateInvoice(DSModel db, InvoiceModel invoice)
{
var details = db.ExecuteQuery<InvoiceDetailModel>("CALL InvoiceCalculate(@CompanyID, @LocationID, @PeriodFrom, @PeriodTo);",
new MySqlParameter("CompanyID", invoice.CompanyID),
new MySqlParameter("LocationID", invoice.LocationID),
new MySqlParameter("PeriodFrom", invoice.InvoicePeriodFrom),
new MySqlParameter("PeriodTo", invoice.InvoicePeriodTo));
invoice.Details.Clear();
invoice.Details.AddRange(details);
}
private static void SaveReminders(DSModel db, KeyBinder key, DriverMedicalModel model, DriversMedical poco)
{
string sqlDelete = @"DELETE FROM drivers_medicals_reminders WHERE DriverMedicalID = @DriverMedicalID AND DriverMedicalReminderID NOT IN (@DriverMedicalReminderID);";
string ids = "0";
if (model.Reminders.Count > 0)
ids = string.Join<uint>(",", model.Reminders.Select(r => r.DriverMedicalReminderID));
sqlDelete = sqlDelete.Replace("@DriverMedicalReminderID", ids);
db.ExecuteNonQuery(sqlDelete, new MySqlParameter("DriverMedicalID", poco.DriverMedicalID));
string sqlInsert = @"
INSERT INTO drivers_medicals_reminders
(DriverMedicalID, ReminderID, ReminderType, ShouldRemind) VALUES (@DriverMedicalID, @ReminderID, @ReminderType, @ShouldRemind);
SELECT LAST_INSERT_ID();";
string sqlUpdate = @"
UPDATE drivers_medicals_reminders
SET
DriverMedicalID = @DriverMedicalID,
ReminderID = @ReminderID,
ReminderType = @ReminderType,
ShouldRemind = @ShouldRemind
WHERE
DriverMedicalReminderID = @DriverMedicalReminderID;";
foreach (var rem in model.Reminders)
{
if (rem.DriverMedicalReminderID == 0)
{
rem.DriverMedicalID = poco.DriverMedicalID;
UtilityModel<uint> temp = new UtilityModel<uint>();
temp.Value = db.ExecuteQuery<uint>(sqlInsert,
new MySqlParameter("DriverMedicalID", rem.DriverMedicalID),
new MySqlParameter("ReminderID", rem.ReminderID),
new MySqlParameter("ReminderType", rem.ReminderType),
new MySqlParameter("ShouldRemind", rem.ShouldRemind))
.First();
key.AddKey(temp, rem, "Value", rem.GetName(p => p.DriverMedicalReminderID));
}
else
{
rem.DriverMedicalID = poco.DriverMedicalID;
db.ExecuteNonQuery(sqlUpdate,
new MySqlParameter("DriverMedicalReminderID", rem.DriverMedicalReminderID),
new MySqlParameter("DriverMedicalID", rem.DriverMedicalID),
new MySqlParameter("ReminderID", rem.ReminderID),
new MySqlParameter("ReminderType", rem.ReminderType),
new MySqlParameter("ShouldRemind", rem.ShouldRemind));
}
}
}
public static List<DriverMedicalCatalogModel> FindMedicals(DSModel db, DriverMedicalFilterModel filter)
{
if (db == null)
throw new ArgumentNullException("db");
if (filter == null)
throw new ArgumentNullException("filter");
string sql = SqlCache.Get(db, "medicals catalog refresh");
List<MySqlParameter> par = new List<MySqlParameter>();
if (!string.IsNullOrWhiteSpace(filter.DriverID))
{
sql = sql.Replace("#DriverID", string.Empty);
sql = sql.Replace("@DriverID", filter.DriverID);
}
if (!string.IsNullOrWhiteSpace(filter.MedTypeID))
{
sql = sql.Replace("#MedTypeID", string.Empty);
sql = sql.Replace("@MedTypeID", filter.MedTypeID);
}
if (filter.ValidityDateFrom.HasValue)
{
sql = sql.Replace("#ValidityDateFrom", string.Empty);
par.Add(new MySqlParameter("ValidityDateFrom", filter.ValidityDateFrom.Value.Date));
}
if (filter.ValidityDateTo.HasValue)
{
sql = sql.Replace("#ValidityDateTo", string.Empty);
par.Add(new MySqlParameter("ValidityDateTo", filter.ValidityDateTo.Value.Date));
}
return db.ExecuteQuery<DriverMedicalCatalogModel>(
sql,
par.ToArray())
.ToList();
}