public List <KeyValuePair <string, int> > RevenuePerCustomer() { BangazonConnection conn = new BangazonConnection(); List <KeyValuePair <string, int> > reportValues = new List <KeyValuePair <string, int> >(); conn.execute(@"SELECT CustomerFirstName || ' ' || CustomerLastName AS CustomerName, " + "SUM(ProductRevenue) AS ProductRevenue FROM Revenue GROUP BY " + "CustomerName ORDER BY ProductRevenue desc", (SqliteDataReader reader) => { while (reader.Read()) { var rawCustomerName = reader[0]; var customerNameString = rawCustomerName.ToString(); var rawRevenuePerCustomer = reader[1]; var RevenuePerCustomerString = rawRevenuePerCustomer.ToString(); var revenuePerCustomerInteger = int.Parse(RevenuePerCustomerString); var straightupbull = new KeyValuePair <string, int>(customerNameString, revenuePerCustomerInteger); reportValues.Add(straightupbull); } }); return(reportValues); }
public static List <Revenue> getQuartlyReport() { BangazonConnection connection = _connection; connection.execute(@"SELECT ProductName, ProductRevenue, PurchaseDate, CASE WHEN cast(strftime('%m', PurchaseDate) as integer) BETWEEN 1 AND 3 THEN 1 WHEN cast(strftime('%m', PurchaseDate) as integer) BETWEEN 4 and 6 THEN 2 WHEN cast(strftime('%m', PurchaseDate) as integer) BETWEEN 7 and 9 THEN 3 ELSE 4 END as Quarter, CASE WHEN cast(strftime('%m', DATE('now')) as integer) BETWEEN 1 and 3 THEN 1 WHEN cast(strftime('%m', DATE('now')) as integer) BETWEEN 4 and 6 THEN 2 WHEN cast(strftime('%m', DATE('now')) as integer) BETWEEN 7 and 9 THEN 3 ELSE 4 END as CurrentQuarter FROM Revenue WHERE Quarter == CurrentQuarter", (SqliteDataReader reader) => { while (reader.Read()) { ListProducts.Add(new Revenue { ProductName = reader[0].ToString(), ProductRevenue = reader.GetInt32(1), PurchaseDate = Convert.ToDateTime(reader[2].ToString()), Quarter = reader.GetInt32(3) }); } }); return(ListProducts); }
/** * Purpose: Return a list of all Incidents from the database * Arguments: * void * Return: * A list of Incidents currently in the database */ public List <Incident> getAll() { BangazonConnection conn = new BangazonConnection(); List <Incident> list = new List <Incident>(); // Execute the query to retrieve all incidents conn.execute(@"select IncidentId, IncidentTypeId, OrderId, EmployeeId, Resolution, DateResolved from Incident" , (SqliteDataReader reader) => { while (reader.Read()) { list.Add(new Incident { IncidentId = reader.GetInt32(0), IncidentTypeId = reader.GetInt32(1), OrderId = reader.GetInt32(2), EmployeeId = reader.GetInt32(3), Resolution = reader[4].ToString(), DateResolved = ParseDate(reader[5].ToString()) }); } } ); return(list); }
/** * Purpose: Retrieve all Incident Types from the database * Arguments: * void * Return: * A list of all Incident Types with an IncidentTypeId and Label */ public List <IncidentType> getAll() { BangazonConnection conn = new BangazonConnection(); List <IncidentType> list = new List <IncidentType>(); // Execute the query to retrieve all customers conn.execute(@"select IncidentTypeId, Label from IncidentType" , (SqliteDataReader reader) => { while (reader.Read()) { list.Add(new IncidentType { IncidentTypeId = reader.GetInt32(0), Label = reader[1].ToString() }); } } ); return(list); }
public void CanGetQuarterlyPurchasedProducts() { BangazonConnection connection = new BangazonConnection(); List <Revenue> quarterlyReport = new List <Revenue>(); int currentMonth = DateTime.Now.Month; int currentQuarter = 4; quarterlyReport = ReportFactory.getQuartlyReport(); if (currentMonth < 4) { currentQuarter = 1; } else if (currentMonth > 3 && currentMonth < 7) { currentQuarter = 2; } else if (currentMonth > 6 && currentMonth < 10) { currentQuarter = 3; } foreach (Revenue product in quarterlyReport) { Assert.True(product.Quarter == currentQuarter); } }
/** * Purpose: Gets Label records from database that match an IncidentTypeId * Return: * List of applicable Labels */ public List <Label> GetLabels(int id) { BangazonConnection conn = new BangazonConnection(); List <Label> list = new List <Label>(); // Execute the query to retrieve all customers conn.execute(@"SELECT L.LabelId, L.Description FROM Label AS L JOIN IncidentTypeLabel AS IL ON IL.LabelId = L.LabelId JOIN IncidentType AS I ON IL.IncidentTypeId = I.IncidentTypeId WHERE IL.IncidentTypeId =" + id, (SqliteDataReader reader) => { while (reader.Read()) { list.Add(new Label { LabelId = reader.GetInt32(0), Description = reader[1].ToString() }); } } ); return(list); }
/** * Purpose: Gets all incidents for an employee id * Arguments: * employeeId - the id of the employee * Return: * A list of all incidents with this employee id */ public List <Incident> getByEmployeeId(int EmployeeId) { BangazonConnection conn = new BangazonConnection(); List <Incident> list = new List <Incident>(); conn.execute(@"select IncidentId, IncidentTypeId, OrderId, EmployeeId, Resolution, DateResolved from Incident where EmployeeId = " + EmployeeId, (SqliteDataReader reader) => { while (reader.Read()) { list.Add(new Incident { IncidentId = reader.GetInt32(0), IncidentTypeId = reader.GetInt32(1), OrderId = reader.GetInt32(2), EmployeeId = reader.GetInt32(3), Resolution = reader[4].ToString(), DateResolved = ParseDate(reader[5].ToString()) }); } }); return(list); }
/** * Purpose: Return a single Incident from the database, retrieved by IncidentId * Arguments: * IncidentId - the Id of an Incident that is being requested * Return: * An Incident matching the provided IncidentId retreived from the database */ public Incident get(int IncidentId) { BangazonConnection conn = new BangazonConnection(); Incident i = null; conn.execute(@"select IncidentId, IncidentTypeId, OrderId, EmployeeId, Resolution, DateResolved from Incident where IncidentId = " + IncidentId, (SqliteDataReader reader) => { while (reader.Read()) { i = new Incident { IncidentId = reader.GetInt32(0), IncidentTypeId = reader.GetInt32(1), OrderId = reader.GetInt32(2), EmployeeId = reader.GetInt32(3), Resolution = reader[4].ToString(), DateResolved = ParseDate(reader[5].ToString()) }; } }); return(i); }
public void CreateDatabase() { string sql3 = "CREATE TABLE Revenue (" + "[Id] INTEGER NOT NULL CONSTRAINT \"PK_Revenue\" PRIMARY KEY AUTOINCREMENT, " + "[ProductName] TEXT NOT NULL, " + "[ProductCost] INTEGER NOT NULL," + "[ProductRevenue] INTEGER NOT NULL, " + "[ProductSupplierState] TEXT NOT NULL, " + "[CustomerFirstName] TEXT NOT NULL, " + "[CustomerLastName] TEXT NOT NULL, " + "[CustomerAddress] TEXT NOT NULL, " + "[CustomerZipCode] INTEGER NOT NULL, " + "[PurchaseDate] TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S')) " + "); " + RandomizeCustomerProducts(1000); BangazonConnection dbConnect = new BangazonConnection(); SqliteConnection connection = new SqliteConnection(dbConnect.path); using (connection) { connection.Open(); SqliteCommand command3 = new SqliteCommand(sql3, connection); command3.ExecuteNonQuery(); } }
public List <KeyValuePair <string, int> > RevenuePerProduct() { BangazonConnection conn = new BangazonConnection(); List <KeyValuePair <string, int> > reportValues = new List <KeyValuePair <string, int> >(); conn.execute(@"SELECT ProductName, SUM(productrevenue) as ProductTotalRevenue from revenue group by productname order by ProductTotalRevenue desc", (SqliteDataReader reader) => { while (reader.Read()) { var rawProductName = reader[0]; var productNameString = rawProductName.ToString(); var rawRevenuePerProduct = reader[1]; var RevenuePerProductString = rawRevenuePerProduct.ToString(); var revenuePerProductInteger = int.Parse(RevenuePerProductString); var straightupbull = new KeyValuePair <string, int>(RevenuePerProductString, revenuePerProductInteger); reportValues.Add(straightupbull); } }); return(reportValues); }
/** * Purpose: Update this Incident in the database * Arguments: * void * Return: * void */ public void update() { string query = $"UPDATE Incident SET Resolution = \"{this.Resolution}\", DateResolved = {this.DateResolved.Value.ToString("yyyyMMdd")} " + $"WHERE IncidentId = {this.IncidentId}"; BangazonConnection conn = new BangazonConnection(); conn.insert(query); }
public void CanGetMonthlyPurchasedProducts() { BangazonConnection connection = new BangazonConnection(); List <Revenue> monthlyReport = new List <Revenue>(); monthlyReport = ReportFactory.getMonthlyReport(); foreach (Revenue product in monthlyReport) { Assert.True(product.PurchaseDate.Month == DateTime.Today.Month); } }
public void CanGetWeeklyPurchasedProducts() { BangazonConnection connection = new BangazonConnection(); List <Revenue> weeklyReport = new List <Revenue>(); weeklyReport = ReportFactory.getWeeklyReport(); foreach (Revenue product in weeklyReport) { Assert.NotNull(product); Assert.True(product.PurchaseDate >= DateTime.Today.AddDays(-(int)DateTime.Today.DayOfWeek)); } }
public void CanGetRevenuePerCustomer() { BangazonConnection connection = new BangazonConnection(); List <Revenue> customers = new List <Revenue>(); customers = ReportFactory.getCustomerReport(); foreach (Revenue customer in customers) { Assert.True(customer.CustomerFirstName != null); Assert.True(customer.ProductRevenue > 0); } }
/** * Purpose: Insert this Incident into the database via SQL * Arguments: * void * Return: * void */ public void save() { string query; if (this.Resolution == null) { query = "INSERT INTO Incident (OrderId,EmployeeId,IncidentTypeId,Resolution,DateResolved) " + $"VALUES ({this.OrderId},{this.EmployeeId},{this.IncidentTypeId},'',null)"; } else { query = "INSERT INTO Incident (OrderId,EmployeeId,IncidentTypeId,Resolution,DateResolved) " + $"VALUES ({this.OrderId},{this.EmployeeId},{this.IncidentTypeId},'{this.Resolution}',{this.DateResolved.Value.ToString("yyyyMMdd")})"; } BangazonConnection conn = new BangazonConnection(); conn.insert(query); }
public static List <Revenue> getMonthlyReport() { BangazonConnection connection = _connection; connection.execute(@"SELECT ProductName, ProductRevenue, PurchaseDate FROM Revenue WHERE PurchaseDate BETWEEN DATE('now','start of month') AND DATE('now')", (SqliteDataReader reader) => { while (reader.Read()) { ListProducts.Add(new Revenue { ProductName = reader[0].ToString(), ProductRevenue = reader.GetInt32(1), PurchaseDate = Convert.ToDateTime(reader[2].ToString()) }); } }); return(ListProducts); }
public void CanUpdateIncidentInDB() { var fact = new IncidentFactory(); Incident last = fact.getAll().Last(); last.Resolution = "Test Resolution"; last.DateResolved = DateTime.Today; last.update(); Incident updated = fact.get(last.IncidentId); Assert.Equal(updated.Resolution, last.Resolution); Assert.Equal(updated.DateResolved, last.DateResolved); // Remove Last Incident from Database if (last.IncidentId == updated.IncidentId) { var conn = new BangazonConnection(); conn.insert($"DELETE FROM Incident WHERE IncidentId = {updated.IncidentId}"); } }
public static List <Revenue> getCustomerReport() { BangazonConnection connection = _connection; connection.execute(@"SELECT CustomerFirstName, CustomerLastName, SUM(ProductRevenue) AS 'GrossSales' FROM Revenue GROUP BY CustomerFirstName || CustomerLastName ORDER BY GrossSales DESC", (SqliteDataReader reader) => { while (reader.Read()) { ListCustomers.Add(new Revenue { CustomerFirstName = reader[0].ToString(), CustomerLastName = reader[1].ToString(), ProductRevenue = reader.GetInt32(2) }); } }); return(ListCustomers); }
public List <KeyValuePair <string, int> > QuarterlyReport() { BangazonConnection conn = new BangazonConnection(); List <KeyValuePair <string, int> > reportValues = new List <KeyValuePair <string, int> >(); conn.execute(@"SELECT * FROM Revenue WHERE PurchaseDate > DateTime ('now', '-90 days') ORDER BY ProductName", (SqliteDataReader reader) => { while (reader.Read()) { var productName = reader[1]; var productNameString = productName.ToString(); var rawProductCost = reader[2]; var productRevenueString = rawProductCost.ToString(); var productCostInteger = int.Parse(productRevenueString); var straightupbull = new KeyValuePair <string, int>(productNameString, productCostInteger); reportValues.Add(straightupbull); } }); return(reportValues); }
public void DatabasePathSet() { BangazonConnection connection = new BangazonConnection(); Assert.True(connection.path.Contains(".db")); }
public static void Main(string[] args) { BangazonConnection db = new BangazonConnection(); Revenue data = null; bool isActive = true; string userInput = ""; try { db.execute("SELECT Id FROM Revenue WHERE Id = 1000", (SqliteDataReader reader) => { while (reader.Read()) { data = new Revenue { Id = reader.GetInt32(0) }; } }); } catch { DatabaseGenerator gen = new DatabaseGenerator(); gen.CreateDatabase(); } while (isActive) { Console.WriteLine(@" ========================== BANGAZON FINANCIAL REPORTS ========================== 1. Weekly Report 2. Monthly Report 3. Quarterly Report 4. Customer Revenue Report 5. Product Revenue Report x. Exit Program"); Console.Write("> "); userInput = Console.ReadLine(); if (userInput.ToUpper() == "X") { isActive = false; break; } switch (userInput) { case "1": ReportAction.printWeeklyReport(); break; case "2": ReportAction.printMonthlyReport(); break; case "3": ReportAction.printQuarterlyReport(); break; case "4": ReportAction.printCustomerReport(); break; case "5": //ReportAction.getProductReport(); break; default: Console.WriteLine("You did not enter a valid menu option. Please try again."); Console.WriteLine(""); break; } } }