//Sales by week will generate a HTML formatted report int the form of a string.
// We will use the NSADatabase Class to retrieve the Data.
// The Table will be in the following format
// ┌───────────────────────────┐
// | Sales By Day |
// ├───────────────────────────┤
// |Store - # |
// ├───────────────────────────|
// │Day │Date│Num Orders│Sales│
// ├─────┼────┼──────────┼─────┤
// └─────┴────┴──────────┴─────┘
private string SalesByDay()
{
//Stringbuilder for creating the Query to use to get the sales data.
StringBuilder ByDayQuery = new StringBuilder();
//for readability the query is built via multiple appends rather than a single one.
ByDayQuery.Append("SELECT storeid, month(O.timeplaced) as Month, day(O.timeplaced) as Day, ");
ByDayQuery.Append("WEEKDAY(O.timeplaced) as DayOfWeek, DATE_FORMAT(O.timeplaced, '%m/%d/%Y') as Date, ");
ByDayQuery.Append("sum(O.total) as Sales, count(orderid) as Orders, refunded ");
ByDayQuery.Append("FROM Orders O WHERE O.refunded = 0 AND storeid IN (");
//loop over all the stations in the list execpt for the last one adding them
//to the set of numbers for the in clause
for (int i = 0; i < (Stores.Count - 1); i++) // Loop through List with for
{
ByDayQuery.Append(Stores[i].ToString());
ByDayQuery.Append(",");
}
//adding the last store to the In clause
ByDayQuery.Append(Stores[(Stores.Count - 1)].ToString());
//add the end of the query
ByDayQuery.Append(") GROUP BY storeid , Day;");
//StringBuilder object.
StringBuilder ByDayReport = new StringBuilder();
//Create and Open the Database Connection.
NSADatabase DBConnection = new NSADatabase("localhost", "nsa-database", "root", "", 1);
//Create Data reader object using the built query with the database object.
MySqlDataReader ByDayData = DBConnection.CustomQuery(ByDayQuery.ToString());
//Append the top portion of the report again done in multiple appends for readability.
ByDayReport.Append("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\"><html><head><title>NSASALES - By Day</title></head>");
ByDayReport.Append("<body><table width=\"100%\" border=\"1\" cellpadding=\"0\" cellspacing=\"0\" summary=\"Sales By Day\" w>");
ByDayReport.Append("<thead><th align=\"center\" colspan=\"4\">Sales By Day</th></thead>");
//There is not a store it < 0
int storeid = -1;
//Read the data and Build the HTML File.
while (ByDayData.Read())
{
//if we hit a new store we need to enter the store Id and header lines.
if (storeid != (int)ByDayData["storeid"])
{
storeid = (int)ByDayData["storeid"];
ByDayReport.Append("<tr><th align=\"left\" colspan=\"4\">Store: ");
ByDayReport.Append(storeid.ToString());
ByDayReport.Append("</th></tr>");
ByDayReport.Append("<tr><th>Date</th><th>Day</th><th>Orders per Day</th><th>Sales in Dollars per Day</th></tr>");
}
//Add the weeks data row to the report.
ByDayReport.Append("<tr><td align=\"center\">");
ByDayReport.Append(string.Format("{0:MM/dd/yyyy}", ByDayData["Date"]));
ByDayReport.Append("</td><td align=\"center\">");
ByDayReport.Append(DayofWeek((int)ByDayData["DayOfWeek"]));
ByDayReport.Append("</td><td align=\"center\">");
ByDayReport.Append(ByDayData["Orders"]);
ByDayReport.Append("</td><td align=\"center\">");
ByDayReport.Append(String.Format("{0:C}", ByDayData["Sales"]));
ByDayReport.Append("</td></tr>");
}
//add the closing tags for the report.
ByDayReport.Append("</table></body></html>");
//must remember to close the reader
ByDayData.Close();
//Close the connection
DBConnection.CloseConnection();
//return the built report to the calling function.
return(ByDayReport.ToString());
}
//Sales by week will generate a HTML formatted report int the form of a string.
// We will use the NSADatabase Class to retrieve the Data.
// The Table will be in the following format
// ┌──────────────────────────┐
// | Sales By WeeK |
// ├──────────────────────────┤
// |Store - # |
// ├──────────────────────────|
// │Week│Date│Num Orders│Sales│
// ├────┼────┼──────────┼─────┤
// └────┴────┴──────────┴─────┘
private string SalesByWeek()
{
//Stringbuilder for creating the Query to use to get the sales data.
StringBuilder ByWeekQuery = new StringBuilder();
//for readability the query is built via multiple appends rather than a single one.
ByWeekQuery.Append("SELECT storeid, weekofyear(O.timeplaced) as Week, ");
ByWeekQuery.Append("STR_TO_DATE(CONCAT(CONCAT( DATE_FORMAT(O.timeplaced,'%Y'), ");
ByWeekQuery.Append("weekofyear(O.timeplaced) ),' Monday'), '%X%V %W') as WeekDate, ");
ByWeekQuery.Append("sum(O.total) as Sales, sum(O.tax) as SalesTax, count(orderid) as Orders, refunded ");
ByWeekQuery.Append("FROM orders O WHERE O.refunded = 0 AND storeid IN (");
//loop over all the stations in the list execpt for the last one adding them
//to the set of numbers for the in clause
for (int i = 0; i < (Stores.Count - 1); i++) // Loop through List with for
{
ByWeekQuery.Append(Stores[i].ToString());
ByWeekQuery.Append(",");
}
//adding the last store to the In clause
ByWeekQuery.Append(Stores[(Stores.Count - 1)].ToString());
//add the end of the query
ByWeekQuery.Append(") GROUP BY storeid , Week, refunded;");
//StringBuilder object.
StringBuilder ByWeekReport = new StringBuilder();
if (!nsadb.Connected())
{
nsadb.OpenConnection();
}
//Create Data reader object using the built query with the database object.
MySqlDataReader ByWeekData = nsadb.CustomQuery(ByWeekQuery.ToString());
//Append the top portion of the report again done in multiple appends for readability.
ByWeekReport.Append("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\"><html><head><title>NSASALES - By Week</title></head>");
ByWeekReport.Append("<body><table width=\"100%\" border=\"1\" cellpadding=\"0\" cellspacing=\"0\" summary=\"Sales By Week\" w>");
ByWeekReport.Append("<thead><th align=\"center\" colspan=\"4\">Sales By Week</th></thead>");
int storeid = 0;
//Read the data and Build the HTML File.
while (ByWeekData.Read())
{
//if we hit a new store we need to enter the store Id and header lines.
if (storeid != (int)ByWeekData["storeid"])
{
storeid = (int)ByWeekData["storeid"];
ByWeekReport.Append("<tr><th align=\"left\" colspan=\"4\">Store: ");
ByWeekReport.Append(storeid.ToString());
ByWeekReport.Append("</th></tr>");
ByWeekReport.Append("<tr><th>Week</th><th>Date</th><th>Orders per Week</th><th>Sales in dollars Per Week</th></tr>");
}
//Add the weeks data row to the report.
ByWeekReport.Append("<tr><td align=\"center\">");
ByWeekReport.Append(ByWeekData["Week"]);
ByWeekReport.Append("</td><td align=\"center\">");
ByWeekReport.Append(string.Format("{0:MM/dd/yyyy}", ByWeekData["WeekDate"]));
ByWeekReport.Append("</td><td align=\"center\">");
ByWeekReport.Append(ByWeekData["Orders"]);
ByWeekReport.Append("</td><td align=\"center\">");
ByWeekReport.Append(String.Format("{0:C}", ByWeekData["Sales"]));
ByWeekReport.Append("</td></tr>");
}
//add the closing tags for the report.
ByWeekReport.Append("</table></body></html>");
//must remember to close the reader
ByWeekData.Close();
//Close the connection
nsadb.CloseConnection();
//return the built report to the calling function.
return(ByWeekReport.ToString());
}
