//Method Name: GetQuarterlyReports
//Purpose of the Method: This method returns a list of reports that display the sales of all products over the past quarter.
public List <Report> GetQuarterlyReports()
{
BangazonWebConnection Conn = new BangazonWebConnection();
List <Report> ReportList = new List <Report>();
string query = $@"
SELECT Product.Name, Product.Price*COUNT(LineItem.ProductId)
FROM Product
JOIN LineItem ON Product.ProductId = LineItem.ProductId
JOIN 'Order' O ON LineItem.OrderId = O.OrderId
WHERE O.DateCompleted >= datetime('now', '-90 days') AND O.DateCompleted <= datetime('now', 'localtime')
GROUP BY Product.Name";
Conn.execute(query, (SqliteDataReader reader) =>
{
while (reader.Read())
{
ReportList.Add(new Report
{
Name = reader[0].ToString(),
Price = reader.GetDouble(1)
});
}
reader.Close();
});
return(ReportList);
}
//Method Name: GetRevenueByProduct
//Purpose of the Method: This method returns a list of reports that display the total revenue of products sold.
public List <Report> GetRevenueByProduct()
{
BangazonWebConnection Conn = new BangazonWebConnection();
List <Report> ReportList = new List <Report>();
string query = $@"
SELECT Product.Name, SUM(Product.Price)
FROM Product
JOIN LineItem ON Product.ProductId = LineItem.ProductId
GROUP BY Product.Name";
Conn.execute(query, (SqliteDataReader reader) =>
{
while (reader.Read())
{
ReportList.Add(new Report
{
Name = reader[0].ToString(),
Price = reader.GetDouble(1)
});
}
reader.Close();
});
return(ReportList);
}
//Method Name: GetRevenueByCustomer
//Purpose of the Method: This method returns a list of reports that display the amount of revenue made from each customer in the database.
public List <Report> GetRevenueByCustomer()
{
BangazonWebConnection Conn = new BangazonWebConnection();
List <Report> ReportList = new List <Report>();
string query = $@"
SELECT Customer.FirstName || ' ' || Customer.LastName AS ""Whole Name"", SUM(Product.Price)
FROM Customer
JOIN 'Order' O ON Customer.CustomerId = O.CustomerId
JOIN LineItem ON O.OrderId = LineItem.OrderId
JOIN Product ON LineItem.ProductId = Product.ProductId
GROUP BY ""Whole Name""";
Conn.execute(query, (SqliteDataReader reader) =>
{
while (reader.Read())
{
ReportList.Add(new Report
{
Name = reader[0].ToString(),
Price = reader.GetDouble(1)
});
}
reader.Close();
});
return(ReportList);
}
public List <Product> getProductRevenue()
{
List <Product> productList = new List <Product>();
BangazonWebConnection conn = new BangazonWebConnection();
string query = "Select P.Name, Sum(P.Price) From Product P Join LineItem L On P.ProductId = L.ProductId Join 'Order' O on O.OrderId = L.OrderId Group By P.Name Order By upper(P.Name)";
conn.execute(query, (SqliteDataReader reader) =>
{
while (reader.Read())
{
productList.Add(new Product
{
productName = reader[0].ToString(),
revenue = reader.GetDouble(1)
});
}
reader.Close();
});
return(productList);
}
public List <Customer> getCustomerRevenue()
{
List <Customer> productList = new List <Customer>();
BangazonWebConnection conn = new BangazonWebConnection();
string query = $"Select C.FirstName, C.LastName, Sum(P.Price) From Customer C Join 'Order' O on C.CustomerId = O.CustomerId Join LineItem L on L.OrderId = O.OrderId Join Product P on P.ProductId = L.ProductId Group By C.FirstName, C.LastName Order By Upper(C.LastName)";
conn.execute(query, (SqliteDataReader reader) =>
{
while (reader.Read())
{
productList.Add(new Customer
{
firstName = reader[0].ToString(),
lastName = reader[1].ToString(),
revenue = reader.GetDouble(2)
});
}
reader.Close();
});
return(productList);
}
//This method takes in an integer as an argument. This integer specifies the number of days worth of sales to query the database.
public List <Product> getProductsWithinTime(int days)
{
List <Product> productList = new List <Product>();
BangazonWebConnection conn = new BangazonWebConnection();
string query = $"Select P.ProductId, P.Name, P.Price, O.DateCompleted From Product P Join LineItem L On P.ProductId = L.ProductId Join 'Order' O on O.OrderId = L.OrderId WHERE O.DateCompleted >= datetime('now', '-{days} days') AND O.DateCompleted <= datetime('now', 'localtime') Order By upper(P.Name)";
conn.execute(query, (SqliteDataReader reader) =>
{
while (reader.Read())
{
productList.Add(new Product
{
productId = reader.GetInt32(0),
productName = reader[1].ToString(),
productPrice = reader.GetDouble(2),
orderDate = reader.GetDateTime(3)
});
}
reader.Close();
});
return(productList);
}
