public static PackageStandardType GetMedicationById(int id)
{
using (var context = new Entities())
{
var query = from m in context.PackageStandardTypes where m.Id == id select m;
return query.FirstOrDefault();
}
}
/// <summary>
/// Returns the package which barcode matches with the parameter
/// </summary>
/// <param name="barcode">The barcode of the package</param>
/// <returns>The package</returns>
public static Package GetPackageByBarcode(int barcode)
{
using (var context = new Entities())
{
var query = from package in context.Packages where package.PackageId == barcode select package;
return query.First();
}
}
public static List<PackageStandardType> GetAllMedications()
{
using (var context = new Entities())
{
var query = from m in context.PackageStandardTypes select m;
return query.ToList();
}
}
public static void AddPackage(Package package)
{
using (var context = new Entities())
{
context.Packages.Add(package);
context.SaveChanges();
}
}
/// <summary>
/// Retrieve all DistCentres from database and put it in SelectListItem(Datatype for Razor DropDownList)
/// </summary>
private void SetUpDistCentreDropDown()
{
using (var context = new Entities())
{
ViewBag.DistCentres = (from d in context.DistCentres
select new SelectListItem { Value = d.CentreId.ToString(), Text = d.CentreName }).ToList();
}
}
public static DistCentre GetDistCentreById(int distCentreId)
{
using (var context = new Entities())
{
var distCentre = from d in context.DistCentres where d.CentreId == distCentreId
select d;
return distCentre.FirstOrDefault();
}
}
/// <summary>
/// Get All Distribution centre and put in the list
/// Currently give exception :
/// $exception {"The underlying provider failed on Open."} System.Exception {System.Data.Entity.Core.EntityException}
///
/// Debug in DistCentreTest -> DistCentreLogicTest_GetAllDistCentre_CountEqualsThree()
/// </summary>
/// <returns></returns>
public static ICollection<DistCentre> GetAllDistCentre()
{
using(var context = new Entities())
{
var distCentre = from d in context.DistCentres
select d;
return distCentre.ToList();
}
}
public static Dictionary<int, string> GetMedicationTypes()
{
Dictionary<int, string> result = new Dictionary<int, string>();
using (var context = new Entities())
{
var query = from medication in context.PackageStandardTypes select medication;
foreach (PackageStandardType p in query.ToList())
{
result.Add(p.Id, p.Description);
}
}
return result;
}
public static List<Report_MedicationInStock> GetMedicationsInStock()
{
using (var context = new Entities())
{
List<Package> packages = new List<Package>();
packages = (from p in context.PackageStatus where p.Status == (int)PackageStatusLogic.StatusEnum.InStock select p.Package).ToList();
List<Report_MedicationInStock> medications = new List<Report_MedicationInStock>();
medications = (from package in packages
group package by package.PackageStandardTypeId into p
select new Report_MedicationInStock
{
Medication = p.First().PackageStandardType,
Quantity = p.Count(),
TotalPrice = (double)p.Sum(x => x.PackageStandardType.Cost)
}).ToList();
return medications;
}
}
public static List<Report_DistributionCentreLoss> GetDistributionCentreLosses()
{
using (var context = new Entities())
{
List<Report_DistributionCentreLoss> centresLosses = new List<Report_DistributionCentreLoss>();
List<PackageStatus> packagesStatus = new List<PackageStatus>();
packagesStatus = (from p in context.PackageStatus where p.Status == (int)PackageStatusLogic.StatusEnum.Lost || p.Status == (int)PackageStatusLogic.StatusEnum.Discarded select p).ToList();
centresLosses = (from pS in packagesStatus
group pS by pS.DestinationCentreID into x
select new Report_DistributionCentreLoss {
DistCentre = GetDistCentreById((int)x.First().DestinationCentreID),
LossRatio = (x.Count() / (x.Count() + PackageStatusLogic.GetPackagesStatusByStatusAndDistCentre(PackageStatusLogic.StatusEnum.Distributed, (int)x.First().DestinationCentreID).Count()))*100,
TotalValueLost = (double)x.Sum(p => p.Package.PackageStandardType.Cost)
}).ToList();
return centresLosses;
}
}
/// <summary>
/// Alters the package state. Created because receiving, discarding and distributing do essentially
/// the same thing - find the package Id, update the staff and status of it.
/// </summary>
/// <param name="packageId">ID of the package</param>
/// <param name="staffId">ID of the staff</param>
/// <param name="status">The new package status</param>
public static PackageStatus AlterPackage(int packageId, string staffId, StatusEnum status)
{
using (var context = new Entities())
{
try
{
var staffCentreQuery = from s in context.AspNetUsers where s.Id == staffId select s;
int centreId = (int)staffCentreQuery.FirstOrDefault().CentreId;
var packageStatusQuery = from p in context.PackageStatus where p.PackageID == packageId select p;
PackageStatus currentPackageStatus = packageStatusQuery.First();
currentPackageStatus.StaffID = staffId;
currentPackageStatus.DestinationCentreID = centreId;
currentPackageStatus.Status = (int)status;
context.SaveChanges();
return currentPackageStatus;
}
catch (NullReferenceException)
{
return null;
}
}
}
public static PackageStatus GetPackageStatusById(int packageId)
{
using (var context = new Entities())
{
var query = from p in context.PackageStatus where p.PackageID == packageId select p;
return query.FirstOrDefault();
}
}
public static List<Package> GetPackagesByDistCentre(int centreId)
{
using (var context = new Entities())
{
var query = from package in context.PackageStatus where package.DestinationCentreID == centreId select package.Package;
return query.ToList();
}
}
public static List<Report_MedicationInTransit> GetMedicationsInTransit()
{
using (var context = new Entities())
{
List<PackageStatus> packages = new List<PackageStatus>();
packages = (from p in context.PackageStatus
where p.Status == (int)PackageStatusLogic.StatusEnum.InTransit
select p).ToList();
List<Report_MedicationInTransit> medications = new List<Report_MedicationInTransit>();
medications = (from package in packages
group package by new
{
package.DestinationCentreID,
package.SourceCentreID,
package.Package.PackageStandardTypeId
} into p
select new Report_MedicationInTransit
{
Medication = p.First().Package.PackageStandardType,
Quantity = p.Count(),
TotalPrice = (double)p.Sum(x => x.Package.PackageStandardType.Cost),
SourceCentre = p.First().SourceCentre,
DestinationCentre = p.First().DestCentre
}).ToList();
return medications;
}
}
private void SetupMedicationList()
{
using (var context = new Entities())
{
ViewBag.Medications = (from d in medications
select new SelectListItem { Value = d.Id.ToString(), Text = d.Description }).ToList();
}
}
private void SetUpPackagesDropDown()
{
using (var context = new Entities())
{
ViewBag.Packages = (from d in context.PackageStatus
where d.Status != (int)PackageStatusLogic.StatusEnum.Discarded &&
d.Status != (int)PackageStatusLogic.StatusEnum.Distributed &&
d.Status != (int)PackageStatusLogic.StatusEnum.Lost
select new SelectListItem { Value = d.PackageID.ToString(), Text = d.PackageID.ToString()}).ToList();
}
}
public static void SendPackage(int source, int destination, string staffId, int packageId)
{
using (var context = new Entities())
{
var query = from p in context.PackageStatus where p.PackageID == packageId select p;
var staffCentreQuery = from s in context.AspNetUsers where s.Id == staffId select s;
int centreId = (int)staffCentreQuery.First().CentreId;
PackageStatus currentPackageStatus = query.First();
currentPackageStatus.StaffID = staffId;
currentPackageStatus.SourceCentreID = centreId;
currentPackageStatus.DestinationCentreID = destination;
currentPackageStatus.Status = (int)StatusEnum.InTransit;
context.SaveChanges();
}
}
public static List<Package> UpdatePackageStatusLost(string staffid, int medicationId)
{
List<PackageStatus> packages = GetPackageStatusInStockByDistributionCentreAndType(staffid, medicationId);
List<Package> lostPackages = new List<Package>();
foreach (PackageStatus ps in packages)
{
if (!packagesInStock.Exists(x => x.PackageId == ps.PackageID))
{
lostPackages.Add(PackageLogic.GetPackageByBarcode((int)ps.PackageID));
using (var context = new Entities())
{
var packageStatusQuery = from p in context.PackageStatus where p.PackageStatusID == ps.PackageStatusID select p;
packageStatusQuery.SingleOrDefault().Status = (int)StatusEnum.Lost;
context.SaveChanges();
}
}
}
packagesInStock = new List<Package>();
return lostPackages;
}
public static List<PackageStatus> GetStatusesInTransitPackages()
{
using (var context = new Entities())
{
var query = from p in context.PackageStatus.Include("SourceCentre").Include("AspNetUser").Include("DestCentre").Include("Status1").Include("Package").Include("Package.PackageStandardType")
where p.PackageStatusID == (int)StatusEnum.InTransit
select p;
return query.ToList();
}
}
public static void RegisterArrival(int packageId, int centreId, string staffId)
{
PackageStatus p = new PackageStatus();
p.PackageID = packageId;
p.DestinationCentreID = centreId;
p.StaffID = staffId;
p.Status = (int)StatusEnum.InStock;
using (var context = new Entities())
{
context.PackageStatus.Add(p);
context.SaveChanges();
}
}
public static List<PackageStatus> GetPackageStatusInStockByDistributionCentreAndType(string staffId, int medicationId)
{
using (var context = new Entities())
{
var staffCentreQuery = from s in context.AspNetUsers where s.Id == staffId select s;
int centreId = (int)staffCentreQuery.First().CentreId;
var query = from ps in context.PackageStatus
join p in context.Packages
on ps.PackageID equals p.PackageId
where ps.Status == (int)StatusEnum.InStock && ps.DestinationCentreID == centreId && p.PackageStandardTypeId == medicationId
select ps;
return query.ToList();
}
}
public static PackageStatus GetPackageStatusEager(PackageStatus status)
{
using (var context = new Entities())
{
var query = from p in context.PackageStatus.Include("SourceCentre").Include("AspNetUser").Include("DestCentre").Include("Status1").Include("Package")
where p.PackageStatusID == status.PackageStatusID
select p;
return query.First();
}
}
/// <summary>
/// Registers the package to the system and returns the barcode
/// </summary>
/// <param name="expiryDate">Expiry date of the package</param>
/// <param name="medicationId">The medication ID</param>
/// <returns>Barcode</returns>
public static int RegisterPackage(DateTime expiryDate, int medicationId)
{
Package p = new Package();
p.ExpiryDate = expiryDate;
p.PackageStandardTypeId = medicationId;
int result = -1;
if (ValidateInput(expiryDate) == Result.Ok)
{
using (var context = new Entities())
{
context.Packages.Add(p);
context.SaveChanges();
return p.PackageId;
}
}
return result;
}
public static List<PackageStatus> GetPackagesStatusByStatusAndDistCentre(StatusEnum status, int centre)
{
using (var context = new Entities())
{
var query = from p in context.PackageStatus where p.Status == (int)status && p.DestinationCentreID == centre select p;
return query.ToList();
}
}
/// <summary>
/// Retrives a list of Packages by the status parameter. This method was created to reduce repetitive code.
/// </summary>
/// <param name="status">The status of packages to retrieve</param>
/// <returns>List of packages</returns>
public static List<Package> GetPackagesListByStatus(StatusEnum status)
{
using (var context = new Entities())
{
var query = from p in context.PackageStatus where p.Status == (int)status select p.Package;
return query.ToList();
}
}