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;
}
public ManagerBase(DSModel context)
{
if (context == null)
throw new ArgumentNullException("context");
this.DbContext = context;
}
public static LocationModel GetLocation(DSModel db, uint locationID)
{
if (db == null)
throw new ArgumentNullException("db");
return LocationRepository.GetLocations(db, locationID).FirstOrDefault();
}
public static CheckResult ValidateSave(DSModel db, DriverLicenseModel model)
{
CheckResult res = new CheckResult(model);
if (model.DriverID == 0)
res.AddError("Driver cannot be empty!", model.GetName(p => p.DriverID));
if (model.LicenseID == 0)
res.AddError("License type cannot be empty!", model.GetName(p => p.LicenseID));
if (model.IssueDate == DateTime.MinValue)
res.AddError("Issue date cannot be empty!", model.GetName(p => p.IssueDate));
if (model.ExpirationDate == DateTime.MinValue)
res.AddError("Expiration date cannot be empty!", model.GetName(p => p.ExpirationDate));
if (model.MVRReviewDate.HasValue && model.MVRReviewDate.Value.Date < model.IssueDate.Date)
res.AddError("MVR review date cannot be earlier than Issue date!", model.GetName(p => p.MVRReviewDate));
if (model.IssueDate.Date > model.ExpirationDate.Date)
res.AddError("Expiration date cannot be earlier than Issue date!", model.GetName(p => p.ExpirationDate));
if (model.Permits.Count == 0)
res.AddError("At least one permit must be selected!", model.GetName(p => p.Permits));
//overlap check
var check = db.DriversLicenses
.Where(d =>
d.DriverLicenseID != model.DriverLicenseID &&
d.DriverID == model.DriverID &&
d.IssueDate < model.ExpirationDate &&
d.ExpirationDate > model.IssueDate)
.FirstOrDefault();
if (check != null)
res.AddError("There is an overlapping license already!");
return res;
}
private static void InsertDriver(DSModel db, KeyBinder key, DriverModel model)
{
Driver poco = new Driver();
if (model.DriverCode == string.Empty)
poco.DriverCode = "D" + DriverRepository.PeekDriverCode(db, "D");
else
poco.DriverCode = model.DriverCode;
poco.FirstName = model.FirstName;
poco.SecondName = model.SecondName;
poco.LastName = model.LastName;
poco.DateOfBirth = model.DateOfBirth;
poco.DateOfHire = model.DateOfHire;
poco.CellPhone = model.CellPhone;
poco.EmergencyPhone = model.EmergencyPhone;
poco.Email = model.Email;
poco.PayRateOverride = model.PayRateOverride;
poco.IsEnabled = model.IsEnabled;
foreach (var l in model.Locations)
{
poco.LocationsDrivers.Add(
new LocationsDriver()
{
LocationID = l.LocationID,
Driver = poco,
TravelPay = l.TravelPay
});
key.AddKey(poco, l, poco.GetName(p => p.DriverID));
}
db.Add(poco);
key.AddKey(poco, model, poco.GetName(p => p.DriverID));
}
public static ContactModel GetContact(DSModel db, uint contactID)
{
if (db == null)
throw new ArgumentNullException("db");
return ContactRepository.GetContacts(db, contactID).FirstOrDefault();
}
public static CompanyModel GetCompany(DSModel db, uint companyID)
{
if (db == null)
throw new ArgumentNullException("db");
var poco = db.Companies.Where(c => c.CompanyID == companyID).FirstOrDefault();
if (poco == null)
return null;
CompanyModel mod = new CompanyModel();
mod.CompanyID = poco.CompanyID;
mod.CompanyName = poco.CompanyName;
mod.CompanyCode = poco.CompanyCode;
mod.CompanyAddress1 = poco.CompanyAddress1;
mod.CompanyAddress2 = poco.CompanyAddress2;
mod.CompanyCity = poco.CompanyCity;
mod.CompanyState = poco.CompanyState;
mod.CompanyPostCode = poco.CompanyPostCode;
mod.CompanyFax = poco.CompanyFax;
mod.CompanyPhone = poco.CompanyPhone;
mod.CompanyEmail = poco.CompanyEmail;
mod.LunchTime = poco.LunchTime;
mod.TrainingTime = poco.TrainingTime;
mod.IsEnabled = poco.IsEnabled;
mod.Locations.AddRange(LocationRepository.GetLocationsByCompany(db, companyID));
mod.IsChanged = false;
return mod;
}
public static DriverModel GetDriver(DSModel db, uint driverID)
{
if (db == null)
throw new ArgumentNullException("db");
var poco = db.Drivers.Where(d => d.DriverID == driverID).FirstOrDefault();
if (poco == null)
return null;
DriverModel mod = new DriverModel();
mod.DriverID = poco.DriverID;
mod.DriverCode = poco.DriverCode;
mod.FirstName = poco.FirstName;
mod.SecondName = poco.SecondName;
mod.LastName = poco.LastName;
mod.DateOfBirth = poco.DateOfBirth;
mod.DateOfHire = poco.DateOfHire;
mod.CellPhone = poco.CellPhone;
mod.EmergencyPhone = poco.EmergencyPhone;
mod.Email = poco.Email;
mod.PayRateOverride = poco.PayRateOverride;
mod.IsEnabled = poco.IsEnabled;
mod.IsChanged = false;
mod.Locations = poco.LocationsDrivers.Select(q => new LocationDriverModel() { LocationDriverID = q.LocationDriverID, CompanyID = q.Location.CompanyID, DriverID = q.DriverID, LocationID = q.LocationID, TravelPay = q.TravelPay, IsChanged = false }).ToList();
return mod;
}
public static string PeekCompanyCode(DSModel db, string prefix)
{
if (db == null)
throw new ArgumentNullException("db");
return db.ExecuteScalar<string>("SELECT PeekCompanyCode(@Prefix)", new MySqlParameter("Prefix", prefix));
}
private static void SendSMS(DSModel db, SMSService sms, NotificationModel notice)
{
try
{
string senderNumber = GLOB.Settings.Get<string>(16);
string[] managementNumbers = GLOB.Settings.Get<string>(17).Split(';');
List<string> numbersToSend = new List<string>();
if (notice.ReminderType == (uint)ReminderType.Driver || notice.ReminderType == (uint)ReminderType.MVR)
numbersToSend.Add("1" + notice.CellPhone.Replace("-", string.Empty).Replace(" ", string.Empty).Trim());
if (notice.ReminderType == (uint)ReminderType.Management || notice.ReminderType == (uint)ReminderType.MVR)
numbersToSend.AddRange(managementNumbers);
bool status = false;
foreach (string number in numbersToSend)
{
try
{
if (sms.SendSMS(senderNumber, number, notice.Message))
status = true;
}
catch { }
}
NotificationRepository.UpdateNotificationStatus(db, notice, status);
}
catch { }
}
public static DSModel GetContext()
{
var db = new DSModel(DB.ConnectionString);
db.ADO = new DSModel.ADOQuery(db);
return(db);
}
private static void SendEmail(DSModel db, EmailService email, NotificationModel notice)
{
try
{
string senderEmail = GLOB.Settings.Get<string>(20);
string[] managementEmails = GLOB.Settings.Get<string>(21).Split(';');
List<string> emailsToSend = new List<string>();
if (notice.ReminderType == (uint)ReminderType.Driver || notice.ReminderType == (uint)ReminderType.MVR)
emailsToSend.Add(notice.Email);
if (notice.ReminderType == (uint)ReminderType.Management || notice.ReminderType == (uint)ReminderType.MVR)
emailsToSend.AddRange(managementEmails);
bool status = false;
foreach (string address in emailsToSend)
{
try
{
if (!email.SendEmail(senderEmail, address, notice.Message, notice.Message).Result.Failed)
status = true;
}
catch { }
}
NotificationRepository.UpdateNotificationStatus(db, notice, status);
}
catch { }
}
private static List<InvoiceDetailModel> GetDetails(DSModel db, uint invoiceID)
{
if (db == null)
throw new ArgumentNullException("db");
return db.InvoicesDetails
.Where(i => i.InvoiceID == invoiceID)
.Select(i => new InvoiceDetailModel()
{
InvoiceDetailID = i.InvoiceDetailID,
InvoiceID = i.InvoiceID,
InvoiceDetailDate = i.InvoiceDetailDate,
InvoiceDetailName = i.InvoiceDetailName,
InvoiceDetailTotalTime = i.InvoiceDetailTotalTime,
InvoiceDetailOverTime = i.InvoiceDetailOverTime,
InvoiceDetailRegularRate = i.InvoiceDetailRegularRate,
InvoiceDetailOverRate = i.InvoiceDetailOverRate,
InvoiceDetailRegularPay = i.InvoiceDetailRegularPay,
InvoiceDetailOvertimePay = i.InvoiceDetailOvertimePay,
InvoiceDetailGroupName = i.InvoiceDetailGroupName,
InvoiceDetailGroupPosition = i.InvoiceDetailGroupPosition,
IsChanged = false
})
.ToList();
}
public static void SaveHolidays(DSModel db, KeyBinder key, List<HolidayModel> model)
{
if (db == null)
throw new ArgumentNullException("db");
if (key == null)
throw new ArgumentNullException("key");
if (model == null)
throw new ArgumentNullException("model");
var holidaysToDelete = db.Holidays.ToList();
foreach (var h in holidaysToDelete.ToList())
{
var check = model.Where(hh => hh.HolidayDate == h.HolidayDate).FirstOrDefault();
if (check == null)
db.Delete(h);
else
model.Remove(check);
}
foreach (var h in model)
{
Holiday poco = new Holiday();
poco.HolidayDate = h.HolidayDate;
db.Add(poco);
key.AddKey(poco, h, h.GetName(p => p.HolidayDate));
}
}
private CompanyManager(DSModel db, CompanyModel company, List<CompanyLicensePayRateModel> license, List<CompanyInvoicingPayRateModel> invoice)
: base(db)
{
this.ActiveModel = company;
this.LicensePayRates = license;
this.InvoicePayRates = invoice;
}
public static CheckResult ValidateSave(DSModel db, DriverModel model)
{
CheckResult res = new CheckResult(model);
if (string.IsNullOrWhiteSpace(model.FirstName))
res.AddError("Please enter a First Name of the driver!", model.GetName(p => p.FirstName));
if (string.IsNullOrWhiteSpace(model.SecondName))
res.AddWarning("Missing Second Name of the driver!", model.GetName(p => p.SecondName));
if (string.IsNullOrWhiteSpace(model.LastName))
res.AddError("Please enter a Last Name of the driver!", model.GetName(p => p.LastName));
//if (model.LicenseID == 0)
// res.AddError("Please select a license!", model.GetName(p => p.LicenseID));
//if (!model.LicenseExpirationDate.HasValue)
// res.AddError("Please enter a license expiration date", model.GetName(p => p.LicenseExpirationDate));
if (model.PayRateOverride < 0.0m)
res.AddError("Please enter a positive number for Pay Rate Override!", model.GetName(p => p.PayRateOverride));
if (model.DriverCode != string.Empty)
{
var check = db.Drivers.Where(d => d.DriverCode == model.DriverCode && d.DriverID != model.DriverID).FirstOrDefault();
if (check != null)
res.AddError("Another driver already uses this Driver Code! Use Peek or leave blank to autogenerate!", model.GetName(p => p.DriverCode));
}
foreach (var l in model.Locations)
{
if (l.LocationID == 0)
res.AddError("Please choose a Location for hte travel pay!", "LocationID");
if (l.TravelPay == 0.0m)
res.AddError("Please enter a positive non-zero number for Travel Pay!", "TravelPay");
}
return res;
}
private static void UpdateBlob(DSModel db, KeyBinder key, FileBlobModel model)
{
FileBlob poco = db.FileBlobs.Where(b => b.BlobID == model.BlobID).FirstOrDefault();
if (poco == null)
throw new ArgumentException("File does not exist!", "BlobID");
model.Map(poco);
}
public XF_TestForm()
{
InitializeComponent();
this.DbContext = DB.GetContext();
this.Load += XF_TestForm_Load;
}
public static List<UtilityModel<uint>> GetReports(DSModel db)
{
if (db == null)
throw new ArgumentNullException("db");
return db.Reports
.Select(r => new UtilityModel<uint>(r.ReportID, r.ReportName))
.ToList();
}
public XF_ReportOpen(DSModel db)
{
InitializeComponent();
this.DbContext = db;
this.Load += XF_ReportOpen_Load;
gridViewReports.RowCellClick += gridViewReports_RowCellClick;
}
private static void InsertBlob(DSModel db, KeyBinder key, FileBlobModel model)
{
key.AddRollback(model.BlobID, model, model.GetName(p => p.BlobID));
FileBlob poco = new FileBlob();
model.Map(poco);
db.Add(poco);
key.AddKey(poco, model, model.GetName(p => p.BlobID));
}
public static List<UtilityModel<uint>> GetReminderKinds(DSModel db)
{
List<UtilityModel<uint>> list = new List<UtilityModel<uint>>();
list.Add(new UtilityModel<uint>(1, "Driver"));
list.Add(new UtilityModel<uint>(2, "Management"));
list.Add(new UtilityModel<uint>(3, "MVR"));
return list;
}
public static List<DriverLicenseReminderModel> GetDriverLicenseReminders(DSModel db, uint driverLicenseID)
{
if (db == null)
throw new ArgumentNullException("db");
return db.DriversLicensesReminders
.Where(d => d.DriverLicenseID == driverLicenseID)
.Select(d => new DriverLicenseReminderModel(d))
.ToList();
}
/// <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 FileBlobModel GetBlob(DSModel db, uint blobID)
{
if (db == null)
throw new ArgumentNullException("db");
return db.FileBlobs
.Where(b => b.BlobID == blobID)
.Select(b => new FileBlobModel(b))
.FirstOrDefault();
}
public static List<MedicalTypeModel> GetMedicalTypes(DSModel db)
{
if (db == null)
throw new ArgumentNullException("db");
return db.ConstMedicalTypes
.OrderBy(m => m.MedTypePosition)
.Select(m => new MedicalTypeModel(m))
.ToList();
}
public static List<DriverMedicalReminderModel> GetReminders(DSModel db, uint driverMedicalID)
{
if (db == null)
throw new ArgumentNullException("db");
return db.DriversMedicalsReminders
.Where(r => r.DriverMedicalID == driverMedicalID)
.Select(r => new DriverMedicalReminderModel(r))
.ToList();
}
private static void UpdateMedical(DSModel db, KeyBinder key, DriverMedicalModel model)
{
var poco = db.DriversMedicals
.Where(m => m.DriverMedicalID == model.DriverMedicalID)
.FirstOrDefault();
model.Map(poco);
db.FlushChanges();
SaveReminders(db, key, model, poco);
}
private static Contact InsertContact(DSModel db, KeyBinder key, ContactModel model)
{
Contact poco = new Contact();
poco.ContactName = model.ContactName;
poco.ContactPhone = model.ContactPhone;
poco.ContactEmail = model.ContactEmail;
db.Add(poco);
key.AddKey(poco, model, model.GetName(p => p.ContactID));
return poco;
}
private static void InsertMedical(DSModel db, KeyBinder key, DriverMedicalModel model)
{
DriversMedical poco = new DriversMedical();
model.Map(poco);
db.Add(poco);
key.AddKey(poco, model, model.GetName(p => p.DriverMedicalID));
db.FlushChanges();
SaveReminders(db, key, model, poco);
}
public static CheckResult ValidateSaveView(DSModel db, FileBlobViewModel model)
{
CheckResult res = new CheckResult(model);
if (string.IsNullOrWhiteSpace(model.BlobName))
res.AddError("File name cannot bet empty!", model.GetName(p => p.BlobName));
if (string.IsNullOrWhiteSpace(model.BlobExtension))
res.AddError("File extension cannot be empty!", model.GetName(p => p.BlobExtension));
return res;
}
public static string Get(DSModel db, string sqlName)
{
if (db == null)
{
throw new ArgumentNullException("db");
}
if (!_Cache.ContainsKey(sqlName))
{
var sql = db.SqlQueries.Where(q => q.QueryName == sqlName).FirstOrDefault();
if (sql == null)
{
throw new ArgumentException("No such sql with the specified name!");
}
_Cache.Add(sqlName, sql.QuerySQL);
}
return(_Cache[sqlName]);
}
public static void Log(DSModel db, LogType type, string location, string message)
{
DriverSolutions.DAL.Log log = new Log();
try
{
log.LogType = (byte)type;
log.LogLocation = location;
log.LogMessage = message;
db.Add(log);
db.FlushChanges();
}
catch (Exception ex)
{
try
{
File.AppendAllText("error.log", log.ToMessage());
}
catch { }
}
}
public static void Log(DSModel db, Exception ex, string location = "")
{
Logger.Log(db, LogType.Error, (location == "" ? "Error" : location), ex.ToString());
db.FlushChanges();
}
public ADOQuery(DSModel context)
{
this.DB = context;
}
