This is one of the R&D did sometimes back. But never had the chance to use it in the intended project. In the hope that this will be useful to anyone who will visit this site, I am going to publish it here.
What this basically do is based on user inputs it will generate a bar chart with the Monthly stats/ productivity. If a user is interested in drill down the chart they need to click the particular week bar and it will give user a daily stats/productivity of that selected week in a model popup.(refer the screen shot below)
OK. Let's get started.
Figure 1.0
Classes used:
·
DateTimeWorks.cs:
note this is a static class and hence can be called without first creating an
object.
using System;
using
System.Data;
using
System.Configuration;
using
System.Linq;
using System.Web;
using
System.Web.Security;
using
System.Web.UI;
using
System.Web.UI.HtmlControls;
using
System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using
System.Xml.Linq;
using
System.Globalization;
namespace TXChart
{
public static class DateTimeWorks
{
public static DateTime
GetFirstDayOfWeek(DateTime dayInWeek)
{
CultureInfo
defaultCultureInfo = CultureInfo.CurrentCulture;
DayOfWeek
firstDay = defaultCultureInfo.DateTimeFormat.FirstDayOfWeek;
DayOfWeek
day = DateTime.Now.DayOfWeek;
int
days = day - DayOfWeek.Monday;
return
GetFirstDateOfWeek(dayInWeek, firstDay);
}
public static DateTime
GetFirstDateOfWeek(DateTime dayInWeek, DayOfWeek firstDay)
{
DateTime
firstDayInWeek = dayInWeek.Date;
while
(firstDayInWeek.DayOfWeek != firstDay)
firstDayInWeek =
firstDayInWeek.AddDays(-1);
return
firstDayInWeek;
}
public static DateTime
GetLastDateOfWeek(DateTime dayInWeek, DayOfWeek lastDayOfWeek)
{
DateTime
lastDayInWeek = dayInWeek.Date;
while
(lastDayInWeek.DayOfWeek != lastDayOfWeek)
lastDayInWeek =
lastDayInWeek.AddDays(1);
return
lastDayInWeek;
}
public static int
GetWeekNumberOfDate(DateTime dayInWeek)
{
DateTimeFormatInfo
dfi = DateTimeFormatInfo.CurrentInfo;
System.Globalization.Calendar cal = dfi.Calendar;
return
cal.GetWeekOfYear(dayInWeek, dfi.CalendarWeekRule,
dfi.FirstDayOfWeek);
}
//get the
date of the first(Mon) day in the week (weekNum should be >=1)
public static DateTime
FirstDateOfWeek(int year, int weekNum)
{
DateTime
jan1 = new DateTime(year,
1, 1);
int
daysOffset = DayOfWeek.Monday -
jan1.DayOfWeek;
DateTime
firstMonday = jan1.AddDays(daysOffset);
//if(firstMonday.DayOfWeek
== DayOfWeek.Monday)
var
cal = CultureInfo.CurrentCulture.Calendar;
int
firstWeek = cal.GetWeekOfYear(jan1, CalendarWeekRule.FirstDay,
DayOfWeek.Monday);
if
(firstWeek <= 1)
{
weekNum -= 1;
}
DateTime
result = firstMonday.AddDays(weekNum * 7);
return
result;
}
//get the date
of the last(sat) day in the week (weekNum should be >=1)
public static DateTime
LastDateOfWeek(int year, int weekNum)
{
DateTime
jan1 = new DateTime(year,
1, 1);
int
daysOffset = DayOfWeek.Saturday -
jan1.DayOfWeek;
DateTime
firstSaturday= jan1.AddDays(daysOffset);
//if(firstMonday.DayOfWeek
== DayOfWeek.Monday)
var
cal = CultureInfo.CurrentCulture.Calendar;
int
firstWeek = cal.GetWeekOfYear(jan1, CalendarWeekRule.FirstDay,
DayOfWeek.Saturday);
if
(firstWeek <= 1)
{
weekNum -= 1;
}
DateTime
result =firstSaturday.AddDays(weekNum * 7);
return
result;
}
public static int
BusinessDaysUntil(this DateTime
firstDay, DateTime lastDay, params DateTime[]
currentHolidays)
{
firstDay = firstDay.Date;
lastDay = lastDay.Date;
if
(firstDay > lastDay)
{
throw
new ArgumentException("Incorrect last day " + lastDay);
}
TimeSpan
span = lastDay - firstDay;
int
businessDays = span.Days + 1;
int
fullWeekCount = businessDays / 7;
// find
out if there are weekends during the time exceedng the full weeks
if
(businessDays > fullWeekCount * 7)
{
// we
are here to find out if there is a 1-day or 2-days weekend
// in
the time interval remaining after subtracting the complete weeks
int
firstDayOfWeek = (int)firstDay.DayOfWeek;
int
lastDayOfWeek = (int)lastDay.DayOfWeek;
if
(lastDayOfWeek < firstDayOfWeek)
lastDayOfWeek += 7;
if
(firstDayOfWeek <= 6)
{
if
(lastDayOfWeek >= 7)
// Both Saturday and Sunday are in the remaining time interval
businessDays -= 2;
else
if (lastDayOfWeek >= 6)
//
Only Saturday is in the remaining time interval
businessDays -= 1;
}
else
if
(firstDayOfWeek <= 7 && lastDayOfWeek >= 7)// Only Sunday is in the remaining time interval
{
businessDays -= 1;
}
}
//
subtract the weekends during the full weeks in the interval
businessDays -= fullWeekCount +
fullWeekCount;
// subtract the
number of current holidays during the time interval
foreach
(DateTime curHoliday in currentHolidays)
{
DateTime
bh = curHoliday.Date;
if
(firstDay <= bh && bh <= lastDay)
--businessDays;
}
return
businessDays;
}
public static int
BusinessDaysUntil(this DateTime
firstDay, DateTime lastDay)
{
firstDay = firstDay.Date;
lastDay = lastDay.Date;
if
(firstDay > lastDay)
{
throw
new ArgumentException("Incorrect last day " + lastDay);
}
TimeSpan
span = lastDay - firstDay;
int
businessDays = span.Days + 1;
int
fullWeekCount = businessDays / 7;
// find
out if there are weekends during the time exceedng the full weeks
if
(businessDays > fullWeekCount * 7)
{
// we
are here to find out if there is a 1-day or 2-days weekend
// in
the time interval remaining after subtracting the complete weeks
int
firstDayOfWeek = (int)firstDay.DayOfWeek;
int
lastDayOfWeek = (int)lastDay.DayOfWeek;
if
(lastDayOfWeek < firstDayOfWeek)
lastDayOfWeek += 7;
if
(firstDayOfWeek <= 6)
{
if
(lastDayOfWeek >= 7)
// Both Saturday and Sunday are in the remaining time interval
businessDays -= 2;
else
if (lastDayOfWeek >= 6)
// Only Saturday is in the remaining time interval
businessDays -= 1;
}
else
if
(firstDayOfWeek <= 7 && lastDayOfWeek >= 7)// Only Sunday is in the remaining time interval
{
businessDays -= 1;
}
}
//
subtract the weekends during the full weeks in the interval
businessDays -= fullWeekCount +
fullWeekCount;
return
businessDays;
}
}
}
·
Productivity.cs:
using System;
using
System.Collections.Generic;
using
System.Linq;
using System.Web;
using
System.Data.SqlClient;
using
System.Configuration;
using
System.Data;
namespace TXChart
{
public class Productivity
{
private DateTime _startdate;
private DateTime _enddate;
private int _roleid;
private string _type;
//week details
are gathered from the selected date belonged week (from Monday to Saturday).
//based on the
role id individual or team)
public
Productivity(DateTime SelectedDate, int RoleID, string
type)
{
_startdate = TXChart.DateTimeWorks.GetFirstDateOfWeek(SelectedDate, DayOfWeek.Monday);
_enddate = TXChart.DateTimeWorks.GetLastDateOfWeek(SelectedDate, DayOfWeek.Saturday);
_roleid = RoleID;
_type = type;
}
//Current
week details are gathered
public
Productivity(int RoleID)
{
_startdate = TXChart.DateTimeWorks.GetFirstDateOfWeek(DateTime.Now, DayOfWeek.Monday);
_enddate = TXChart.DateTimeWorks.GetLastDateOfWeek(DateTime.Now, DayOfWeek.Saturday);
_roleid = RoleID;
}
//week
details are gathered from the provided weekno & year.
public
Productivity(int Year, int
WeekNo, int RoleID)
{
_startdate = TXChart.DateTimeWorks.FirstDateOfWeek(Year,WeekNo);
_enddate = TXChart.DateTimeWorks.LastDateOfWeek(Year, WeekNo);
_roleid = RoleID;
}
//get month
details from the selected weekno.
public
Productivity(int Year, int
WeekNo, int RoleID, string
type)
{
if
(type == "Weekly")
{
_startdate = TXChart.DateTimeWorks.FirstDateOfWeek(Year, WeekNo);
_enddate = TXChart.DateTimeWorks.LastDateOfWeek(Year, WeekNo);
_roleid = RoleID;
_type = type;
}
if
(type == "Monthly")
{
DateTime
startdate = TXChart.DateTimeWorks.FirstDateOfWeek(Year,
WeekNo);
//_startdate
= new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
_startdate = new DateTime(startdate.Year,
startdate.Month, 1);
//_enddate
= _startdate.AddMonths(1).AddDays(-1);
_enddate =
_startdate.AddMonths(1).AddDays(-1);
_roleid = RoleID;
_type = type;
}
}
protected
static string
GetConnectionString()
{
return
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
}
//Generate
weekly report for an agent
public DataSet ProductivityData(string
agentid)
{
DataSet
prodData = new DataSet();
if
(_roleid == 4)
{
if
(_type == "Weekly")
{
prodData = GetStats(agentid,
_startdate.ToString(), _enddate.ToString());
}
if
(_type == "Monthly")
{
prodData =
WeeklyGetStats(agentid, _startdate.ToString(), _enddate.ToString());
}
}
return
prodData;
}
private
DataSet GetStats(string
agentid, string fromdate, string todate)
{
using
(SqlConnection cnnCon = new SqlConnection(
GetConnectionString()))
{
SqlCommand
cmdImStats = new SqlCommand();
cmdImStats.Connection = cnnCon;
cmdImStats.CommandType = CommandType.StoredProcedure;
cmdImStats.CommandText = "sp_Test_GetProductivityEV";
cmdImStats.Parameters.Add("@agentID", SqlDbType.NVarChar);
cmdImStats.Parameters.Add("@fromDate", SqlDbType.Date);
cmdImStats.Parameters.Add("@toDate", SqlDbType.Date);
cmdImStats.Parameters["@agentID"].Value = agentid;
cmdImStats.Parameters["@fromDate"].Value = fromdate;
cmdImStats.Parameters["@toDate"].Value = todate;
SqlDataAdapter
daImStats = new SqlDataAdapter(cmdImStats);
DataSet
dsImStats = new DataSet();
daImStats.Fill(dsImStats);
return
dsImStats;
}
}
private
DataSet WeeklyGetStats(string agentid, string
fromdate, string todate)
{
using
(SqlConnection cnnCon = new SqlConnection(GetConnectionString()))
{
SqlCommand
cmdImStats = new SqlCommand();
cmdImStats.Connection = cnnCon;
cmdImStats.CommandType = CommandType.StoredProcedure;
//cmdImStats.CommandText
= "sp_Test_WeeklyGetProductivityEV";
cmdImStats.CommandText = "sp_Test_WeeklyGetProductivityEV";
cmdImStats.Parameters.Add("@agentID", SqlDbType.NVarChar);
cmdImStats.Parameters.Add("@fromDate",
SqlDbType.Date);
cmdImStats.Parameters.Add("@toDate", SqlDbType.Date);
cmdImStats.Parameters["@agentID"].Value = agentid;
cmdImStats.Parameters["@fromDate"].Value = fromdate;
cmdImStats.Parameters["@toDate"].Value = todate;
SqlDataAdapter
daImStats = new SqlDataAdapter(cmdImStats);
DataSet
dsImStats = new DataSet();
daImStats.Fill(dsImStats);
return dsImStats;
}}}}
Add an .aspx page to the solution (Chart1.aspx)
·
Design of the
interface
Figure 2.0
<asp:CHART ID="TChart1"
runat="server"
BackColor="Silver"
BackGradientStyle="TopBottom" BorderColor="181, 64, 1" BorderDashStyle="Solid"
BorderWidth="2" Height="272px" Width="680px" EnableViewState="True"
IsMapAreaAttributesEncoded="True" onclick="TChart1_Click">
<titles>
<asp:Title Font="Trebuchet MS, 14.25pt, style=Bold" ForeColor="26, 59,
105"
Name="Title1" ShadowColor="32, 0, 0, 0" ShadowOffset="3" Text="Weekly Chart">
asp:Title>
titles>
<legends>
<asp:Legend BackColor="Transparent" Enabled="True"
Font="Trebuchet MS, 8.25pt, style=Bold" IsTextAutoFit="True"
Name="Default"
TitleFont="Microsoft Sans Serif, 8pt, style=Bold" Alignment="Near"
TitleAlignment="Near">
asp:Legend>
legends>
<borderskin skinstyle="Emboss" />
<series>
<asp:Series Name="Enter"
BorderColor="180,
26, 59, 105">asp:Series>
<asp:Series Name="Verify"
BorderColor="180,
26, 59, 105">asp:Series>
series>
<chartareas>
<asp:ChartArea BackColor="OldLace" BackGradientStyle="TopBottom"
BackSecondaryColor="White" BorderColor="64, 64, 64, 64" Name="ChartArea1"
ShadowColor="LightGray">
<area3dstyle inclination="15" isclustered="False" isrightangleaxes="False"
perspective="10" rotation="10"
wallwidth="0"
/>
<axisy labelautofitmaxfontsize="8" linecolor="64, 64, 64, 64">
<LabelStyle Font="Trebuchet MS, 8.25pt, style=Bold" />
<majorgrid linecolor="64, 64, 64,
64" />
axisy>
<axisx labelautofitmaxfontsize="8" linecolor="64, 64, 64, 64">
<LabelStyle Font="Trebuchet MS, 8.25pt, style=Bold" Format="MM-dd"
IsEndLabelVisible="False" />
<majorgrid linecolor="64, 64, 64,
64" />
axisx>
asp:ChartArea>
chartareas>
asp:CHART>
·
Chart1.aspx.cs
using System;
using
System.Collections.Generic;
using
System.Linq;
using System.Web;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Data;
using System.Web.UI.DataVisualization.Charting;
·
Chart button click event
protected void btnChart_Click(object
sender, EventArgs e)
{
Productivity
prod = new Productivity(4);
if
(txtWeek.Text.Trim() != "")
{
if
(ddlType.SelectedItem.ToString().Trim() == "Weekly")
{
prod = new Productivity(Convert.ToInt32(ddlYear.SelectedItem.ToString()), Convert.ToInt32(txtWeek.Text), 4,"Weekly");
}
if
(ddlType.SelectedItem.ToString().Trim() == "Monthly")
{
prod = new Productivity(Convert.ToInt32(ddlYear.SelectedItem.ToString()), Convert.ToInt32(txtWeek.Text), 4, "Monthly");
}
}
DataSet
ds = prod.ProductivityData(txtAgentID.Text.Trim());
TChart1.DataSource = ds;
TChart1.Series["Enter"].XValueMember = "PDate";
TChart1.Series["Enter"].YValueMembers = "Entery Count";
TChart1.Series["Enter"].Name
= "Enter";
TChart1.ChartAreas[0].AxisY.Title =
"Batch Count";
TChart1.Series["Verify"].XValueMember = "PDate";
TChart1.Series["Verify"].YValueMembers = "Verify Count";
TChart1.Series["Verify"].Name = "Verify";
TChart1.Titles[0].Text =
ddlType.SelectedItem.ToString().Trim()+"
Chart";//Based on report type
TChart1.DataBind();
if
(ddlType.SelectedItem.ToString().Trim() != "Weekly")
{
for
(int i = 0; i < ds.Tables[0].Rows.Count;
i++)
{
TChart1.Series[0].Points[i].PostBackValue = ds.Tables[0].Rows[i]["PDate"].ToString();
TChart1.Series[1].Points[i].PostBackValue = ds.Tables[0].Rows[i]["PDate"].ToString();
}}
for
(int i = 0; i <
TChart1.Series[0].Points.Count; i++)
{
string
X_val = ds.Tables[0].Rows[i]["PDate"].ToString();
TChart1.Series[0].Points[i].ToolTip = X_val;
TChart1.Series[0].Points[i].AxisLabel
= X_val.ToString();
TChart1.Series[1].Points[i].ToolTip = X_val;
TChart1.Series[1].Points[i].AxisLabel = X_val.ToString();
}}
·
Style
<%@ Page
Language="C#"
AutoEventWireup="true"
CodeBehind="Chart1.aspx.cs"
Inherits="TXChart.Chart1"
%>
<%@ Register
Assembly="AjaxControlToolkit"
Namespace="AjaxControlToolkit"
TagPrefix="cc1"
%>
DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
>
<head runat="server">
<title>title>
<style type="text/css">
.block
{
background-color:
gray;
filter:
alpha(opacity=30);
}
style>head>
·
Trick1:Button1
is used as the TargetControl Id in the ModalPopupExtender but it’s visibility
is falls in the page load time as we are not going to use it to trigger the
modalpopup.
·
Trick2:block
style is used as the BackgroundCssClass for modalpopup to block the background
when it triggerd.But it is not truly blocking the controls.
protected void Page_Load(object
sender, EventArgs e)
{
Button1.Style.Add("display", "none");
}
<asp:Button ID="Button1"
runat="server"
Text="Button"
Height="26px"
/>
<cc1:ModalPopupExtender ID="Button1_ModalPopupExtender"
runat="server"
TargetControlID="Button1"
OkControlID="Button3"
PopupControlID="Panel1" BackgroundCssClass="block"
PopupDragHandleControlID="TCHART2">
cc1:ModalPopupExtender>
Panel1
<asp:Panel ID="Panel1" BackColor=""
runat="server"
style="display:none;">
<asp:Button ID="Button3" runat="server" Text="X" />
<div>
<asp:CHART ID="TCHART2"
runat="server"
BackColor="Silver"
BackGradientStyle="TopBottom" BorderColor="181, 64, 1" BorderDashStyle="Solid"
BorderWidth="2" Height="272px" Width="680px" EnableViewState="True"
IsMapAreaAttributesEncoded="True" onclick="TChart1_Click">
<titles>
<asp:Title Font="Trebuchet MS, 14.25pt, style=Bold" ForeColor="26, 59,
105"
Name="Title1" ShadowColor="32, 0, 0, 0" ShadowOffset="3" Text="Weekly Chart">
asp:Title>
titles>
<legends>
<asp:Legend BackColor="Transparent" Enabled="True"
Font="Trebuchet MS, 8.25pt, style=Bold" IsTextAutoFit="True"
Name="Default"
TitleFont="Microsoft Sans Serif, 8pt, style=Bold" Alignment="Near"
TitleAlignment="Near">
asp:Legend>
legends>
<borderskin skinstyle="Emboss" />
<series>
<asp:Series Name="Enter"
BorderColor="180,
26, 59, 105">asp:Series>
<asp:Series Name="Verify"
BorderColor="180,
26, 59, 105">asp:Series>
series>
<chartareas>
<asp:ChartArea BackColor="OldLace" BackGradientStyle="TopBottom"
BackSecondaryColor="White" BorderColor="64, 64, 64, 64" Name="ChartArea1"
ShadowColor="LightGray">
<area3dstyle inclination="15" isclustered="False" isrightangleaxes="False"
perspective="10" rotation="10"
wallwidth="0"
/>
<axisy labelautofitmaxfontsize="8" linecolor="64, 64, 64, 64">
<LabelStyle Font="Trebuchet MS, 8.25pt, style=Bold" />
<majorgrid linecolor="64, 64, 64,
64" />
axisy>
<axisx labelautofitmaxfontsize="8" linecolor="64, 64, 64, 64">
<LabelStyle Font="Trebuchet MS, 8.25pt, style=Bold" Format="MM-dd"
IsEndLabelVisible="False" />
<majorgrid linecolor="64, 64, 64, 64" />
axisx>
asp:ChartArea>
chartareas>
asp:CHART>
div>asp:Panel> form>body>html>
·
Chart1_click event Chart2 will be binded based on the selected Chart1
series
protected void TChart1_Click(object
sender, ImageMapEventArgs e)
{
string
region, year = "";
if
((e.PostBackValue != null) &&
(TChart1.Titles[0].Text != "Weekly Chart"))
{
region = e.PostBackValue;
year
=ddlYear.SelectedItem.Text.ToString();
TCHART2.Titles[0].Text = region
+ " - Weekly Chart";
Productivity
prod = new Productivity(Convert.ToInt32(year), Convert.ToInt32(region),
4, "Weekly");
DataSet
ds = prod.ProductivityData("GA43");
TCHART2.DataSource = ds;
TCHART2.Series["Enter"].XValueMember = "PDate";
TCHART2.Series["Enter"].YValueMembers = "Entery Count";
TCHART2.Series["Enter"].Name = "Enter";
TCHART2.ChartAreas[0].AxisY.Title
= "Batch Count";
TCHART2.Series["Verify"].XValueMember = "PDate";
TCHART2.Series["Verify"].YValueMembers = "Verify Count";
TCHART2.Series["Verify"].Name = "Verify";
TCHART2.Titles[0].Text = "Weekly Chart";//Based
on report type
TCHART2.DataBind();
}
Button1_ModalPopupExtender.Show();
}
Figure 3.0
When click on one of the series
from 1 to 4 based on the selection the weekly chart pops up as shown in the top Figure 1.0
