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;
}
}
}
