public static Category getById(int id)
{
using (DSModel model = new DSModel())
{
return(model.Categories.Find(id));
}
}
public static string NextNumber(DSModel db, InvoiceModel model)
{
return(db.ExecuteScalar <string>("SELECT NextInvoiceNumber(@CompID,@LocID,@InvYear);",
new MySqlParameter("CompID", model.CompanyID),
new MySqlParameter("LocID", model.LocationID),
new MySqlParameter("InvYear", model.InvoiceIssueDate.Year)));
}
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 List <Product> searchByName(string name)
{
using (DSModel model = new DSModel())
{
return(model.Products.Where(x => x.Name.Contains(name)).ToList());
}
}
public static List <Product> searchByBrand(int brandID)
{
using (DSModel model = new DSModel())
{
return(model.Products.Where(x => x.BrandID == brandID).ToList());
}
}
public static List <OrderDetail> getAll(Order order)
{
using (DSModel model = new DSModel())
{
return(model.OrderDetails.Where(x => x.Order == order).ToList());
}
}
private static void UpdateDispatch(DSModel db, KeyBinder key, DispatchModel model)
{
var poco = db.Dispatches.Where(d => d.DispatchID == model.DispatchID).FirstOrDefault();
if (poco == null)
{
throw new ArgumentNullException("No dispatch with the specified ID!");
}
poco.DriverID = model.DriverID;
poco.CompanyID = model.CompanyID;
poco.LocationID = model.LocationID;
poco.FromDateTime = model.FromDateTime;
poco.ToDateTime = model.ToDateTime;
poco.LunchTime = model.LunchTime;
poco.TrainingTime = model.TrainingTime;
poco.SpecialPayRate = model.SpecialPayRate;
poco.MiscCharge = model.MiscCharge;
poco.Note = model.Note;
poco.IsCancelled = model.IsCancelled;
poco.IsConfirmed = model.IsConfirmed;
poco.HasLunch = model.HasLunch;
poco.HasTraining = model.HasTraining;
poco.UserID = model.UserID;
poco.LastUpdateTime = model.LastUpdateTime;
}
public static CheckResult ValidateSave(DSModel db, DispatchModel model)
{
CheckResult res = new CheckResult(model);
if (model.DriverID == 0)
{
res.AddError("Please choose a Driver!", model.GetName(p => p.DriverID));
}
if (model.CompanyID == 0)
{
res.AddError("Please choose a Company!", model.GetName(p => p.CompanyID));
}
if (model.LocationID == 0)
{
res.AddError("Please choose a Location!", model.GetName(p => p.LocationID));
}
if (model.ToDateTime < model.FromDateTime)
{
res.AddError("End Date Time cannot be earlier than Start Date Time!", model.GetName(p => p.ToDateTime));
}
if (model.TrainingTime < 0.0m)
{
res.AddError("Training time cannot be negative!", model.GetName(p => p.TrainingTime));
}
if (model.MiscCharge < 0m)
{
res.AddError("Per diem cannot be negative!", model.GetName(p => p.MiscCharge));
}
var checkLicense = db.DriversLicenses
.Where(d => d.DriverID == model.DriverID &&
d.IssueDate <= model.FromDateTime.Date &&
d.ExpirationDate >= model.ToDateTime)
.Count();
if (checkLicense == 0)
{
res.AddError("Driver has no valid license for the specified dispatch dates!", model.GetName(p => p.DriverID));
}
//overlap check
var check = db.Dispatches
.Where(d =>
d.DispatchID != model.DispatchID &&
d.DriverID == model.DriverID &&
d.FromDateTime <model.ToDateTime &&
d.ToDateTime> model.FromDateTime)
.FirstOrDefault();
if (check != null)
{
res.AddError(string.Format("This driver has overlapping dispatches!\r\n\r\nCompany: {0}\r\nLocation: {1}\r\nStart: {2}\r\nEnd: {3}",
check.Company.CompanyName,
check.Location.LocationName,
check.FromDateTime.ToString("g"),
check.ToDateTime.ToString("g")), model.GetName(p => p.FromDateTime));
}
return(res);
}
public static List <Order> getAll()
{
using (DSModel model = new DSModel())
{
return(model.Orders.ToList());
}
}
public static CheckResult ValidateSave(DSModel db, UserModel user)
{
CheckResult res = new CheckResult();
if (string.IsNullOrWhiteSpace(user.Username))
{
res.AddError("Please enter a Username!", user.GetName(p => p.Username));
}
if (string.IsNullOrWhiteSpace(user.FirstName))
{
res.AddError("Please enter a First Name!", user.GetName(p => p.FirstName));
}
if (string.IsNullOrWhiteSpace(user.LastName))
{
res.AddError("Please enter a Last Name!", user.GetName(p => p.LastName));
}
var check = db.Users.Where(u => u.Username == user.Username && u.UserID != user.UserID).FirstOrDefault();
if (check != null)
{
res.AddError("Another user already uses this username!", user.GetName(p => p.Username));
}
return(res);
}
public static CheckResult ValidateSave(DSModel db, CompanyModel model)
{
CheckResult res = new CheckResult(model);
if (string.IsNullOrWhiteSpace(model.CompanyName))
{
res.AddError("Please enter a Company Name!", model.GetName(p => p.CompanyName));
}
if (model.CompanyCode != string.Empty)
{
var check = db.Companies.Where(c => c.CompanyCode == model.CompanyCode && c.CompanyID != model.CompanyID).FirstOrDefault();
if (check != null)
{
res.AddError("Another company already uses this Company Code! Use Peek or leave blank to autogenerate!", model.GetName(p => p.CompanyCode));
}
}
if (model.TrainingTime < 0m)
{
res.AddError("Traning time cannot be negative number!", model.GetName(p => p.TrainingTime));
}
var groups = model.Locations.GroupBy(l => l.LocationCode).ToList();
if (groups.Any(g => g.Count() > 1))
{
var gr = groups.Where(g => g.Count() > 1).FirstOrDefault();
res.AddError(string.Format("Duplicate location codes! Code: {0}", gr.FirstOrDefault().LocationCode));
}
return(res);
}
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 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 Supplier getById(int id)
{
using (DSModel model = new DSModel())
{
return(model.Suppliers.Find(id));
}
}
public CheckResult SaveFile(FileBlobModel model)
{
if (!model.IsChanged)
{
return(new CheckResult(model));
}
using (DSModel db = DB.GetContext())
{
var check = FileBlobValidator.ValidateSave(db, model);
if (check.Failed)
{
return(check);
}
KeyBinder key = new KeyBinder();
try
{
FileBlobRepository.SaveBlob(db, key, model);
db.SaveChanges();
key.BindKeys();
model.IsChanged = false;
return(new CheckResult(model));
}
catch (Exception ex)
{
key.RollbackKeys();
return(new CheckResult(ex));
}
}
}
public static CheckResult ValidateSave(DSModel db, InvoiceModel model)
{
CheckResult res = new CheckResult(model);
if (model.CompanyID == 0)
{
res.AddError("Please choose a Company!", model.GetName(p => p.CompanyID));
}
if (model.LocationID == 0)
{
res.AddError("Please choose a Location!", model.GetName(p => p.LocationID));
}
if (model.InvoicePeriodFrom == DateTime.MinValue)
{
res.AddError("Please enter a date for Period From!", model.GetName(p => p.InvoicePeriodFrom));
}
if (model.InvoicePeriodTo == DateTime.MinValue)
{
res.AddError("Please enter a date for Period To!", model.GetName(p => p.InvoicePeriodTo));
}
if (model.InvoicePeriodTo < model.InvoicePeriodFrom)
{
res.AddError("The date for Pertiod To cannot be earlier than Period From!", model.GetName(p => p.InvoicePeriodTo));
}
return(res);
}
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 void DeleteLocation(DSModel db, LocationModel model)
{
if (db == null)
{
throw new ArgumentNullException("db");
}
if (model.LocationID != 0)
{
var poco = db.Locations.Where(l => l.LocationID == model.LocationID).FirstOrDefault();
if (poco != null)
{
if (poco.ConfirmationContactID.HasValue)
{
db.Delete(poco.ConfirmationContact);
}
if (poco.InvoiceContactID.HasValue)
{
db.Delete(poco.InvoiceContact);
}
if (poco.DispatchContactID.HasValue)
{
db.Delete(poco.DispatchContact);
}
db.Delete(poco);
}
}
}
public static void SaveDispatch(DSModel db, KeyBinder key, DispatchModel disp)
{
if (db == null)
{
throw new ArgumentNullException("db");
}
if (key == null)
{
throw new ArgumentNullException("key");
}
if (disp == null)
{
throw new ArgumentNullException("disp");
}
disp.UserID = GLOB.User.UserID;
disp.LastUpdateTime = DateTime.Now;
if (disp.DispatchID == 0)
{
InsertDispatch(db, key, disp);
}
else
{
UpdateDispatch(db, key, disp);
}
}
public static List <Brand> getAll()
{
using (DSModel model = new DSModel())
{
return(model.Brands.ToList());
}
}
public static List <Product> getAll()
{
using (DSModel model = new DSModel())
{
return(model.Products.ToList());
}
}
public static Brand getById(int id)
{
using (DSModel model = new DSModel())
{
return(model.Brands.Find(id));
}
}
public static List <Product> searchByCategory(int cateID)
{
using (DSModel model = new DSModel())
{
return(model.Products.Where(x => x.CategoryID == cateID).ToList());
}
}
public static void SaveBlobView(DSModel db, KeyBinder key, FileBlobViewModel model)
{
if (db == null)
{
throw new ArgumentNullException("db");
}
if (key == null)
{
throw new ArgumentNullException("key");
}
if (model == null)
{
throw new ArgumentNullException("model");
}
if (model.BlobID == 0)
{
throw new ArgumentException("BlobID cannot be 0!", "model");
}
string sql = @"
UPDATE file_blobs f
SET
f.BlobName = @BlobName, f.BlobDescription = @BlobDescription, f.BlobExtension = @BlobExtension
WHERE
f.BlobID = @BlobID;";
db.ExecuteNonQuery(sql,
new MySqlParameter("BlobID", model.BlobID),
new MySqlParameter("BlobName", model.BlobName),
new MySqlParameter("BlobDescription", model.BlobDescription),
new MySqlParameter("BlobExtension", model.BlobExtension));
}
public static void SaveInvoice(DSModel db, KeyBinder key, InvoiceModel model)
{
if (db == null)
{
throw new ArgumentNullException("db");
}
if (key == null)
{
throw new ArgumentNullException("key");
}
if (model == null)
{
throw new ArgumentNullException("model");
}
model.UserID = GLOB.User.UserID;
model.LastUpdateTime = DateTime.Now;
if (model.InvoiceID == 0)
{
InsertInvoice(db, key, model);
}
else
{
UpdateInvoice(db, key, model);
}
}
public static CheckResult ValidateSave(DSModel db, DriverMedicalModel model)
{
CheckResult res = new CheckResult(model);
if (model.DriverID == 0)
{
res.AddError("Driver cannot be empty!", model.GetName(p => p.DriverID));
}
if (model.MedTypeID == 0)
{
res.AddError("Medical type cannot be empty!", model.GetName(p => p.MedTypeID));
}
if (model.ExaminationDate == DateTime.MinValue)
{
res.AddError("Examination date cannot be empty!", model.GetName(p => p.ExaminationDate));
}
if (model.ValidityDate == DateTime.MinValue)
{
res.AddError("Validity date cannot be empty!", model.GetName(p => p.ValidityDate));
}
if (model.ValidityDate.Date <= model.ExaminationDate.Date)
{
res.AddError("Validity date cannot be earlier than Examination date!", model.GetName(p => p.ValidityDate));
}
return(res);
}
public static InvoiceModel GetInvoice(DSModel db, string invoiceNumber, int year = 0)
{
if (db == null)
{
throw new ArgumentNullException("db");
}
var query = PredicateBuilder.True <Invoice>();
query = query.And(q => q.InvoiceNumber == invoiceNumber);
if (year != 0)
{
query = query.And(q => q.InvoiceIssueDate.Year == year);
}
uint invID = db.Invoices
.Where(query)
.Select(i => i.InvoiceID)
.FirstOrDefault();
if (invID == 0)
{
return(null);
}
return(InvoiceRepository.GetInvoice(db, invID));
}
public static List <LicenseModel> GetLicenses(DSModel db, bool?isEnabled = null)
{
if (db == null)
{
throw new ArgumentNullException("db");
}
var query = PredicateBuilder.True <ConstLicense>();
if (isEnabled.HasValue)
{
query = query.And(l => l.IsEnabled == isEnabled.Value);
}
return(db.ConstLicenses
.Where(query)
.Select(l => new LicenseModel()
{
LicenseID = l.LicenseID,
LicenseName = l.LicenseName,
IsEnabled = l.IsEnabled,
IsChanged = false
})
.ToList());
}
public static List <CompanyLicensePayRateModel> GetLicensesPayRates(DSModel db, uint companyID, DateTime?date = null)
{
var query = PredicateBuilder.True <CompaniesLicensesPayrate>();
query = query.And(c => c.CompanyID == companyID);
if (date.HasValue)
{
query = query.And(c => c.FromDate <= date.Value.Date && (c.ToDate >= date || !c.ToDate.HasValue));
}
return(db.CompaniesLicensesPayrates
.Where(query)
.Select(c => new CompanyLicensePayRateModel()
{
CompanyDriverID = c.CompanyDriverID,
CompanyID = c.CompanyID,
LicenseID = c.LicenseID,
PayRate = c.PayRate,
PayRateOT = c.PayRateOT,
FromDate = c.FromDate,
ToDate = c.ToDate,
IsChanged = false
})
.ToList());
}
public static List <Category> getAll()
{
using (DSModel model = new DSModel())
{
return(model.Categories.ToList());
}
}
