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