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

Use regular expressions to save time.

Replace in files...

Utils.GetConfigValue\({"[a-zA-Z0-9_ ]+"}\)

to

System.Configuration.ConfigurationManager.ConnectionStrings\[\1\]

Sorting entries in config appsettings.

   How to sort AppSettings without manually missing any keys or important information.

Code Snippet

1.   private void WriteAppSettingsToFile(string filename)
2.     {
3.  
4.         List<string> lstConfigKeys = new List<string>();
5.         foreach (string appKey in ConfigurationManager.AppSettings.AllKeys)
6.         {
7.             lstConfigKeys.Add(appKey);
8.         }
9.  
10.         lstConfigKeys.Sort();
12.  
13.         using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\" + filename + ".txt"))
14.         {
15.             foreach (string key in lstConfigKeys)
16.             {
17.                 file.WriteLine(string.Format("'{0}', -- {1}", key, ConfigurationManager.AppSettings.Get(key)));
18.             }
19.         }
20.     }

You can modify the code to write the keys in the form of   . Then copy and paste the output to your actual config file. You have all the keys in sorted order. 

How to generate dynamic report.

Below is the code that I had written for generating dynamic report. It takes few parameters and generates the reports for a particular frequency.

using System;

using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;


namespace Reports.DynamicReports
{
///

/// Summary description for GetDynamicFrequencyReport.
///

public class GetDynamicFrequencyReport : System.Web.UI.Page
{
protected System.Web.UI.WebControls.PlaceHolder plResults;


ReportDS rds;
DataSet ds;


private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here

// Put user code to initialize the page here

int intid;
string strSourceid ;
int intCount=0;
DateTime dtFromDate;
DateTime dtToDate;
string strFrequency;

string strSourceFromDate;
string strSourceToDate;
string strSourceFrequency;

int inti;

if (Request["id"]!=null)
{
strSourceid = Request["id"];
strSourceFromDate = Request["fromdate"];
strSourceToDate=Request["todate"];
strSourceFrequency = Request["frequency"];

try
{
intid = int.Parse(strSourceid);
dtFromDate = DateTime.Parse(strSourceFromDate);
dtToDate = DateTime.Parse(strSourceToDate);
strFrequency = strSourceFrequency;

rds = new ReportDS();
ds = rds.GetDynamiFrequencyReport(intid,dtFromDate,dtToDate,strFrequency);

if (ds != null && ds.Tables.Count > 0 )
{
intCount = int.Parse(ds.Tables[0].Rows[0][0].ToString());


for (inti = 1; inti <= intCount ; inti++) { DynamicTables(inti); } } } catch(Exception ex) {} } } private void DynamicTables(int inti) { int intLabelTable; int intGridTable; intLabelTable = (inti * 2 )-1; intGridTable = (inti * 2 ); AddToPlaceHolder(CreateLabel(ds.Tables[intLabelTable].Rows[0][0].ToString())); AddToPlaceHolder(CreateDataGrid(ds.Tables[intGridTable])); } private void AddToPlaceHolder(Control oControl) { try { if (oControl != null) { plResults.Controls.Add(oControl); } } catch(Exception Ex) { } } private Label CreateLabel(string sText) { Label oControl = new Label(); oControl.Text = sText; oControl.Font.Name = "Verdana"; oControl.Font.Size = 11; oControl.Font.Italic = true; oControl.Font.Bold = true; return oControl; } private DataGrid CreateDataGrid(DataTable dt) { DataGrid oControl = new DataGrid(); // //
//
//

//
//
//


oControl.BorderColor= Color.Black;
oControl.BorderStyle = BorderStyle.None;
oControl.BorderWidth = System.Web.UI.WebControls.Unit.Pixel(1);
oControl.BackColor = System.Drawing.Color.FromArgb(247,247,247);
oControl.CellPadding = 4;
oControl.Font.Name = "Verdana";
oControl.Font.Size = 9;


//

oControl.AlternatingItemStyle.BackColor = System.Drawing.Color.FromArgb(235,235,235);

//


oControl.PagerStyle.ForeColor = System.Drawing.Color.FromArgb(51,00,153);
oControl.PagerStyle.BackColor = System.Drawing.Color.FromArgb(255,255,204);



oControl.GridLines= GridLines.Vertical;
oControl.CellPadding = 3;



oControl.FooterStyle.ForeColor=Color.Black;
oControl.FooterStyle.BackColor=Color.LightGray;

oControl.AlternatingItemStyle.BackColor =Color.Gainsboro;
oControl.ItemStyle.ForeColor = Color.Black;

oControl.HeaderStyle.Font.Bold = true;
oControl.HeaderStyle.ForeColor=Color.White;
oControl.HeaderStyle.BackColor=Color.Blue;

oControl.DataSource = dt;


oControl.DataBind();

return oControl;
}


#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

///

/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///

private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
}
}

Exciting effect.

I wanted to write a simple exciting effect for my blog at XO. I have decided to eliminate a bunch of lines of crappy code and put decent code which is simple and effective.

http://jsfiddle.net/vijayphani/reRJP/

tableless layout with div

http://www.w3.org/2002/03/csslayout-howto

How to find time difference between two dates in oracle.

select numtodsinterval(last_upd_dt - created_dt,'day') time_difference from dates;