public IList <LoansClientsInventoryDTO> GetAllClients()
{
using (var context = new SingularityDBContext())
{
var clients = from c in context.Clients
join l in context.LoanMasters
on c.ClientID equals l.ClientId
join ld in context.LoanDetails
on l.LoanMasterId equals ld.LoanMasterId
join i in context.InventoryItems
on ld.InventoryItemId equals i.InventoryItemId
where l.IsDeleted == false
select new LoansClientsInventoryDTO()
{
CellPhone = c.CellPhone,
Email = c.Email,
LastName = c.LastName,
FirstName = c.FirstName,
ClientId = c.ClientID,
LoanEligibility = c.LoanEligibility,
InventoryItemId = i.InventoryItemId,
ItemName = i.ItemName,
Manufacturer = i.Manufacturer,
Description = i.Description,
};
var clientList = clients.ToList();
return(clientList);
}
}
//Renews all Items in a loan as a new loan
public void RenewLoan(LoansClientsInventoryDTO renewedDTO)
{
var loanNumber = renewedDTO.LoanNumber;
using (var context = new SingularityDBContext())
{
var loan = context.LoanMasters.FirstOrDefault(
lm => string.Equals(lm.LoanNumber, loanNumber));
if (loan != null) // Always check for Null
{
loan.IsActive = false;
loan.ClientOutcome = renewedDTO.ClientOutcome;
loan.LoanNotes = renewedDTO.LoanNotes;
//do not worry about item damages for now
}
context.SaveChanges();
foreach (var itemId in renewedDTO.InventoryItems)
{
var item = context.InventoryItems.FirstOrDefault(
i => i.InventoryItemId == itemId.InventoryItemId);
if (item != null)
{
item.Availability = true;
}
context.SaveChanges();
}
}
}
public void EditLoan(LoansClientsInventoryDTO loanSubmission)
{
//have client id
using (var context = new SingularityDBContext())
{
IEnumerable <LoanDetail> query = from itemId in loanSubmission.InventoryItemIds
select new LoanDetail
{
InventoryItemId = itemId,
LoanMasterId = loanSubmission.LoanMasterId,
Purpose = loanSubmission.Purpose,
PurposeType = loanSubmission.PurposeType
};
List <LoanDetail> loanDetailsList = query.ToList();
////Update New Inventory Items' Availability
foreach (var itemId in query)
{
var item = (from ii in context.InventoryItems
where ii.InventoryItemId == itemId.InventoryItemId
select ii).FirstOrDefault();
if (item != null)
{
item.Availability = false;
}
context.SaveChanges();
}
//now we can add a range of loan details to the loan details table
context.LoanDetails.AddRange(loanDetailsList);
context.SaveChanges();
}
}
//Used for AddClient Get Method to pre-populate the Client Number
public int ReturnNextClientNumber()
{
using (var context = new SingularityDBContext())
{
//var items = context.Clients;
int ClientID;
IList <Client> allClients = GetAllClients();
var itemCount = allClients.Count;
//need to add a segment that actually checks the id # of the last item
// --> This proposed fix should fix any un-aligned numbers
if (allClients.Count > 0)
{
ClientID = itemCount + 1;
}
else
{
ClientID = 1;
}
return(ClientID);
}
}
public void CheckInLoan_Nick(LoansClientsInventoryDTO checkInDTO) // copied into RenewLoan below
{
var loanNumber = checkInDTO.LoanNumber;
using (var context = new SingularityDBContext())
{
var loan = context.LoanMasters.FirstOrDefault(
lm => string.Equals(lm.LoanNumber, loanNumber));
if (loan != null)
{
loan.IsActive = false;
loan.ClientOutcome = checkInDTO.ClientOutcome;
loan.LoanNotes = checkInDTO.LoanNotes;
//loan.Damages = checkInDTO.Damages;
}
context.SaveChanges();
var itemIds = GetInventoryItemIdsByLoanNumber(loanNumber);
MarkInventoryItemsAsAvailable(context, itemIds);
}
}
//Gets list of all the DisabilityCategory Objects (Gets all the Categories)
public IList <DisabilityCategory> GetAllDisabilities()
{
using (var context = new SingularityDBContext())
{
return(context.DisabilityCategories.ToList());
}
}
//Get All Loans in DB
public IList <LoansClientsInventoryDTO> GetAllLoans()
{
using (var context = new SingularityDBContext())
{
//Get all Loans in DB
var loans = from c in context.Clients
join l in context.LoanMasters
on c.ClientID equals l.ClientId
where l.IsDeleted == false
//join ld in context.LoanDetails //union
//on l.LoanMasterId equals ld.LoanMasterId selects less and repeats because not all loans have loan details, and each detail then appears as a new loan
select new LoansClientsInventoryDTO()
{
LoanNumber = l.LoanNumber,
DateCreated = l.DateCreated,
ClientId = c.ClientID,
LastName = c.LastName,
FirstName = c.FirstName,
IsActive = l.IsActive,
CellPhone = c.CellPhone,
Email = c.Email
//LoanDate = ld.LoanDate
};
return(loans.ToList());
}
}
public void SaveClient(Client client)
{
using (var context = new SingularityDBContext())
{
client.DateCreated = DateTime.Now; // Manipulating the client object is done before saving to Db
context.Clients.Add(client);
context.SaveChanges();
// No Any() needed null check covers it
if (client.DisabilityIds != null)
// what was the thing that checked for null before Any()? default type for Enum?
{
var clientDisabilities = client.DisabilityIds
.Select(disabilityId => new ClientDisability
{
ClientId = client.ClientID, DisabilityCategoryId = disabilityId
});
context.ClientDisabilities.AddRange(clientDisabilities);
context.SaveChanges();
}
}
}
public ActionResult LogIn(UserLogIn user, string returnUrl)
{
if (ModelState.IsValid)
{
using (var context = new SingularityDBContext())
{
var username = user.Username;
var password = user.Password;
var userIsValid = context.UserLogIns.Any(dbUserLogIns => dbUserLogIns.Username == username &&
dbUserLogIns.Password == password);
if (userIsValid)
{
FormsAuthentication.SetAuthCookie(user.Username, false);
if (Url.IsLocalUrl(returnUrl) && returnUrl.Length > 1 && returnUrl.StartsWith("/") &&
!returnUrl.StartsWith("//") && !returnUrl.StartsWith("/\\"))
{
return(Redirect(returnUrl));
}
else
{
return(RedirectToAction("Index", "Home"));
}
}
else
{
ModelState.AddModelError("", "username or password is incorrect");
}
}
}
return(View(user));
}
public IList <InventoryItemCategory> GetItemCategories()
{
using (var context = new SingularityDBContext())
{
var list = context.InventoryCategories.ToList();
return(list);
}
}
public Client GetClientDetails() //int id
{
using (var context = new SingularityDBContext())
{
var client = context.Clients;
var clients = client.FirstOrDefault();
return(clients);
}
}
public IList <Client> GetAllClients()
{
using (var context = new SingularityDBContext())
{
var clients = context.Clients;
var clientList = clients.ToList();
return(clientList);
}
}
//add new inventory item
public void SaveNewItem(InventoryItem item)
{
using (var context = new SingularityDBContext())
{
item.DatePurchased = DateTime.Now; // Manipulating the item object is done before saving to Db
context.InventoryItems.Add(item);
context.SaveChanges();
}
}
public IList <LoanMaster> GetAllLoanMasters()
{
using (var context = new SingularityDBContext())
{
var loans = context.LoanMasters;
var loanList = loans.ToList();
return(loanList);
}
}
private IEnumerable <int> GetInventoryItemIdsByLoanNumber(string loanNumber)
{
using (var context = new SingularityDBContext())
{
var storedProcedureName = "dbo.GetInventoryItemsByLoanNumber @loanNumber";
var pLoanNumber = new SqlParameter("@loanNumber", loanNumber);
return(context.Database.SqlQuery <int>(
storedProcedureName, pLoanNumber).ToList());
}
}
//returns all inventory items that are currently available
public IList <InventoryItem> ViewAvailableInv()
{
using (var context = new SingularityDBContext())
{
IList <InventoryItem> allitems = GetAllInventory(); //gets full list of inventory items from db
IList <InventoryItem> AvailableItems = allitems.Where(item => item.Availability == true).ToList(); //filters out items which have an availability of True (or 1)
return(AvailableItems);
}
}
//gets all inventory items and passes it to the InventoryItemController
public IList <InventoryItem> GetAllInventory()
{
using (var context = new SingularityDBContext())
{
var inventory = context.InventoryItems.Where(i => i.IsDeleted == false);
var inventoryList = inventory.ToList();
return(inventoryList);
}
}
private IEnumerable <string> GetLoanNumberByInventoryItemId(int inventoryitemid)
{
using (var context = new SingularityDBContext())
{
var storedProcedureName = "dbo.GetLoanNumberByInventoryItemId @inventoryItemId";
var pInventoryItemId = new SqlParameter("@inventoryItemId", inventoryitemid);
return(context.Database.SqlQuery <string>(
storedProcedureName, pInventoryItemId).ToList());
}
}
public IList <Client> GetAllClientsAsInventoryList()
{
using (var context = new SingularityDBContext())
{
var items = context.Clients;
var itemList = items.ToList();
return(itemList);
}
}
public void ChangeStatusDeleted(int id)
{
using (var context = new SingularityDBContext())
{
var client = context.Clients.FirstOrDefault(x => x.ClientID == id);
client.IsDeleted = true;
context.SaveChanges();
}
}
//Get all INVENTORYITEMS
public IList <InventoryItem> GetAllItemsAsInventoryList()
{
using (var context = new SingularityDBContext())
{
var items = context.InventoryItems;
var itemList = items.ToList();
return(itemList);
}
}
//Get most recent loan number
public string LoanIncrement()
{
using (var context = new SingularityDBContext())
{
var loan = (from l in context.LoanMasters
orderby l.DateCreated descending
select l.LoanNumber).Take(1).Single();
var addNumber = string.Join("", loan.Where(char.IsDigit));
int lastNum = Int32.Parse(addNumber);
lastNum = lastNum + 1;
return(lastNum.ToString());
}
}
//edit existing item>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
public void EditExistingItem(InventoryItem item)
{
using (var context = new SingularityDBContext())
{
context.InventoryItems.Attach(item);
var entry = context.Entry(item);
entry.State = EntityState.Modified;
context.SaveChanges();
}
}
// Create a new service that will pass the values of the Client View Model to the Client report which will show
// All of the clients along with their email and phone number
public ClientReportViewModel CreateClientReportViewModel()
{
using (var context = new SingularityDBContext())
{
var viewModel = new ClientReportViewModel();
var clients = context.Clients.ToList();
viewModel.clientList = clients;
return(viewModel);
}
}
public void CreateRenewedLoan(LoansClientsInventoryDTO loanSubmission)
{
using (var context = new SingularityDBContext())
{
var loanNumIncrement = LoanIncrement();
var newLoan = new LoanMaster
{
ClientId = loanSubmission.ClientId,
DateCreated = DateTime.Now,
LoanNumber = loanNumIncrement,
IsActive = true,
IsDeleted = false
};
context.LoanMasters.Add(newLoan);
context.SaveChanges();
LoanDetail[] itemsListed = new LoanDetail[loanSubmission.InventoryItems.Count];
for (int i = 0; i < itemsListed.Length; i++)
{
foreach (var itemId in loanSubmission.InventoryItems)
{
//if (loanSubmission.Availability)
//{
itemsListed[i] = new LoanDetail
{
InventoryItemId = itemId.InventoryItemId,
LoanMasterId = newLoan.LoanMasterId,
Purpose = loanSubmission.Purpose,
PurposeType = loanSubmission.PurposeType
};
i++;
//};
}
;
//break;
}
List <LoanDetail> loanDetailsList = itemsListed.ToList();
//now we can add a range of loan details to the loan details table
context.LoanDetails.AddRange(loanDetailsList);
context.SaveChanges();
//Update Inventory Items' Availability
var itemIds = GetInventoryItemIdsByLoanNumber(newLoan.LoanNumber);
MarkInventoryItemsAsNotAvailable(context, itemIds);
}
}
private void MarkInventoryItemsAsAvailable(SingularityDBContext context, IEnumerable <int> itemIds)
{
foreach (var itemId in itemIds)
{
var item = context.InventoryItems.FirstOrDefault(
i => i.InventoryItemId == itemId);
if (item != null)
{
item.Availability = true;
}
context.SaveChanges();
}
}
//returns all inventory items that are currently on loan/not available
public IList <InventoryItem> ViewInvOnLoan()
{
//**************
//NEEDS FIXIN
//**************
using (var context = new SingularityDBContext())
{
IList <InventoryItem> allitems = GetAllInventory(); //gets full list of inventory items from db
IList <InventoryItem> ItemsOnLoan = allitems.Where(item => item.Availability == false).ToList(); //filters out items which have an availability of True (or 1)
return(ItemsOnLoan);
}
}
//Gets the Client Object and populates it's DisabilityIds Property using a Linq query
public Client GetClientDetails(int id)
{
using (var context = new SingularityDBContext())
{
var client = context.Clients.FirstOrDefault(x => x.ClientID == id); //default 0 int, The default value for reference and nullable types is null.
client.DisabilityIds = context.ClientDisabilities //Access the ClientDisabilities Associate table entries
.Where(cd => cd.ClientId == client.ClientID) //filter down to entries that match this ClientID
.Select(i => i.DisabilityCategoryId) //give me back the DisabilityCategoryId property for these entries
.ToList(); // They become the IEnumerable<int> DisabilityIds property values for this Client instance
return(client);
}
}
//Update existing client info
public void EditClientDetails(Client client, IEnumerable <int> DisabilityIds)
{
using (var context = new SingularityDBContext())
{
context.Clients.Attach(client);
var entry = context.Entry(client);
entry.State = EntityState.Modified;
context.SaveChanges();
if (DisabilityIds != null) // null check required to cover posting on null DisabilityIds
{
var oldClientDisabilities = context.ClientDisabilities.Where(cd => cd.ClientId == client.ClientID);
context.ClientDisabilities.RemoveRange(oldClientDisabilities);
context.SaveChanges();
var clientDisabilities = DisabilityIds //Access the IEnumerable<int> of DisabilityIds
.Select(DisabilityId => new ClientDisability //Foreach create a new cd object
{
ClientId = client.ClientID, DisabilityCategoryId = DisabilityId
});
// After the ClientDisability Ojects are created and added to var, they get added to the table
context.ClientDisabilities.AddRange(clientDisabilities);
context.SaveChanges();
}
else
{
var oldClientDisabilities = context.ClientDisabilities.Where(cd => cd.ClientId == client.ClientID);
context.ClientDisabilities.RemoveRange(oldClientDisabilities);
context.SaveChanges();
}
}
}
//Deletes Item - used in Update Inventory Items form
public void DeleteItem(int id)
{
using (var context = new SingularityDBContext())
{
var item = context.InventoryItems.FirstOrDefault(x => x.InventoryItemId == id);
if (item != null)
{
item.IsDeleted = true;
context.InventoryItems.Attach(item);
var entry = context.Entry(item);
entry.State = EntityState.Modified;
context.SaveChanges();
}
}
}
