Weekly reports that needs to be sent manually.

Just want to write out the code for generating weekly reports. It is very boring to run the queries manually then paste in excel and send it out.
 

Code Snippet

1. using System;
2. using System.Data.OracleClient;
3. using System.Text;
4.  
5. namespace WeeklyReports
6. {
7.  
8.     public static class DateTimeExtensions
9.     {
10.         public static DateTime StartOfWeek(this DateTime dt, DayOfWeek startOfWeek) { int diff = dt.DayOfWeek - startOfWeek; if (diff < 0) { diff += 7; } return dt.AddDays(-1 * diff).Date; }
11.     }
12.  
14.  
15.     public partial class _Default : System.Web.UI.Page
16.     {
17.  
18.  
19.  
20.         protected void Page_Load(object sender, EventArgs e)
21.         {
22.  
23.             StringBuilder allTables = new StringBuilder();
24.  
25.             using (OracleConnection oraConn = new OracleConnection(@"Data Source=abc.world;Persist Security Info=True;User ID=abc;Password=abc;Unicode=True"))
26.             {
27.  
28.                 DateTime monday = DateTime.Now.StartOfWeek(DayOfWeek.Monday);
29.                 DateTime sunday = DateTime.Now.StartOfWeek(DayOfWeek.Sunday);
30.                 monday = monday.AddDays(-7);
31.  
32.                 for (int ctr = 0; ctr < 10; ctr++)
33.                 {
34.  
35.  
36.  
37.                     String strMonday = FormatDateToOracle(monday);
38.                     String strSunday = FormatDateToOracle(sunday);
39.  
40.                    OracleCommand oraCmd = new OracleCommand(@"SELECT applicationname,  COUNT(*) FROM  EM_MONITOR.LOAD_DOT_NET_LOG_ERROR_PRD WHERE error_date BETWEEN '" + strMonday + "' AND '" + strSunday + "' AND   applicationname!='(null)' GROUP BY applicationname ORDER BY COUNT(*) DESC ", oraConn);
41.  
42.                   //  OracleCommand oraCmd = new OracleCommand(@"select * from (  SELECT COUNT(*),  MESSAGE FROM EM_MONITOR.LOAD_DOT_NET_LOG_ERROR_PRD WHERE error_date BETWEEN '" + strMonday + "' AND '" + strSunday + "'  AND applicationname!='(null)' GROUP BY MESSAGE ORDER BY COUNT(*) DESC  ) where rownum <= 5 ", oraConn);
43.  
44.                     oraConn.Open();
45.                     OracleDataReader oraDr = oraCmd.ExecuteReader();
46.  
47.                     StringBuilder sbTable = new StringBuilder();
48.  
49.                     StringBuilder sbRows = new StringBuilder();
50.  
51.                     while (oraDr.Read())
52.                     {
53.  
54.                         sbRows.Append("");
55.                         for (int col = 0; col < oraDr.FieldCount; col++)
56.                         {
57.                             sbRows.Append("" + oraDr[col].ToString() + "");
58.  
59.                         }
60.  
61.                         sbRows.Append("");
62.  
63.                     }
64.  
65.                     sbTable.Append("" + sbRows + "

    "  + "For week " +  strMonday + " - " + strSunday + "

    ");
66.  
67.                     allTables.Append(sbTable.ToString() + @"");
68.                     oraConn.Close();
69.  
70.                     monday = monday.AddDays(-7);
71.                     sunday = sunday.AddDays(-7);
72.  
73.                 }
74.             }
75.  
76.             divTables.InnerHtml  = allTables.ToString();
77.  
78.         }
79.  
80.         private string FormatDateToOracle(DateTime date)
81.         {
82.             return date.ToString("dd-MMM-yyyy").ToUpper();
83.         }
84.  
85.       
86.     }
87. }