public IEnumerable <LineItem> GetLineItemsByInvoiceID(int invoiceID)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"SELECT * FROM LineItems WHERE InvoiceNum = {invoiceID}";
return(ToList(command));
}
}
/// <summary>
/// Gets every invoice by the id
/// </summary>
/// <returns></returns>
public IEnumerable <Invoice> GetAll()
{
using (var command = Context.CreateCommand())
{
command.CommandText = "SELECT * FROM Invoices";
return(ToList(command));
}
}
public IEnumerable <Item> GetItemByExactCost(float cost)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"SELECT * FROM ItemDesc WHERE Cost = {cost}";
return(ToList(command));
}
}
public void Remove(Item item)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"DELETE FROM ItemDesc WHERE ItemCode = {item.ItemCode}";
command.ExecuteNonQuery();
}
}
public IEnumerable <Item> GetItemByDescription(string desc)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"SELECT * FROM ItemDesc WHERE ItemDesc = '{desc}'";
return(ToList(command));
}
}
public void Add(Item item)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"INSERT INTO ItemDesc (ItemCode, ItemDesc, Cost) VALUES({item.ItemCode}, {item.Description}, {item.Cost})";
command.ExecuteNonQuery();
}
}
public IEnumerable <LineItem> GetLineItemsByItemCode(string ItemCode)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"SELECT * FROM LineItems WHERE ItemCode = {ItemCode}";
return(ToList(command));
}
}
/// <summary>
/// Gets the invoices by the total cost on the invoice
/// </summary>
/// <param name="charge"></param>
/// <returns></returns>
public IEnumerable <Invoice> GetInvoicesByTotalCost(int charge)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"SELECT * FROM Invoices WHERE TotalCost = {charge}";
return(ToList(command));
}
}
/// <summary>
/// Adds an invoice to the database
/// </summary>
/// <param name="item"></param>
public void Add(Invoice item)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"INSERT INTO Invoices (InvoiceNum, InvoiceDate, TotalCost) VALUES({item.ID}, {item.Date}, {item.TotalCost})";
command.ExecuteNonQuery();
}
}
/// <summary>
/// Removes an invoice from the database
/// </summary>
/// <param name="item"></param>
public void Remove(Invoice item)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"DELETE FROM Invoices WHERE InvoiceNum = {item.ID}";
command.ExecuteNonQuery();
}
}
public void Remove(LineItem item)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"DELETE FROM LineItems WHERE InvoiceNum = {item.Invoice_ID} AND ItemCode = {item.ItemCode}";
command.ExecuteNonQuery();
}
}
/// <summary>
/// Gets the invoice by the date
/// </summary>
/// <param name="dateTime"></param>
/// <returns></returns>
public IEnumerable <Invoice> GetInvoicesByDate(DateTime dateTime)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"SELECT * FROM Invoices WHERE InvoiceDate = {dateTime}";
return(ToList(command));
}
}
/// <summary>
/// Gets an invoice by the invoice id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Invoice Get(int id)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"SELECT * FROM Invoices WHERE InvoiceNum = {id}";
return(ToList(command).First());
}
}
public IEnumerable <LineItem> GetLineItemsByLineNumber(int line)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"SELECT * FROM LineItems WHERE LineItemNumber = {line}";
return(ToList(command));
}
}
public IEnumerable <LineItem> GetAll()
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"SELECT * FROM LineItems";
return(ToList(command));
}
}
public void Add(LineItem item)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"INSERT INTO LineItems (InvoiceNum, LineItemNum, ItemCode) VALUES({item.Invoice_ID}, {item.LineItemNumber}, {item.ItemCode})";
command.ExecuteNonQuery();
}
}
/// <summary>
///
/// </summary>
/// <param name="id">ASCII value of the ItemCode character</param>
/// <returns></returns>
public Item Get(int id)
{
string codeValue = ((char)id).ToString();
using (var command = Context.CreateCommand())
{
command.CommandText = $"SELECT * FROM ItemDesc WHERE ItemCode = {codeValue}";
return(ToList(command).First());
}
}
/// <summary>
/// Adds a range of invoices to the database
/// </summary>
/// <param name="items"></param>
public void AddRange(IEnumerable <Invoice> items)
{
foreach (Invoice item in items)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"INSERT INTO Invoices (InvoiceNum, InvoiceDate, TotalCost) VALUES({item.ID}, {item.Date}, {item.TotalCost})";
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Remove a range of invoice from the database
/// </summary>
/// <param name="items"></param>
public void RemoveRange(IEnumerable <Invoice> items)
{
foreach (Invoice item in items)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"DELETE FROM Invoices WHERE InvoiceNum = {item.ID}";
command.ExecuteNonQuery();
}
}
}
public void AddRange(IEnumerable <LineItem> items)
{
foreach (LineItem item in items)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"INSERT INTO LineItems (InvoiceNum, LineItemNum, ItemCode) VALUES({item.Invoice_ID}, {item.LineItemNumber}, {item.ItemCode})";
command.ExecuteNonQuery();
}
}
}
public void RemoveRange(IEnumerable <LineItem> items)
{
foreach (LineItem item in items)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"DELETE FROM LineItems WHERE InvoiceNum = {item.Invoice_ID} AND ItemCode = {item.ItemCode}";
command.ExecuteNonQuery();
}
}
}
public void RemoveRange(IEnumerable <Item> items)
{
foreach (Item item in items)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"DELETE FROM ItemDesc WHERE ItemCode = {item.ItemCode}";
command.ExecuteNonQuery();
}
}
}
public void AddRange(IEnumerable <Item> items)
{
foreach (Item item in items)
{
using (var command = Context.CreateCommand())
{
command.CommandText = $"INSERT INTO ItemDesc (ItemCode, ItemDesc, Cost) VALUES({item.ItemCode}, {item.Description}, {item.Cost})";
command.ExecuteNonQuery();
}
}
}