/**
* 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: 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 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: 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);
}
/**
* 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);
}
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 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);
}
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 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 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;
}
}
}