Design Page
Default.aspx.cs
int GetDaysInMonth(int month, int year)
{
if (month < 1 || month > 12)
{
throw new System.ArgumentOutOfRangeException("month", month, "month mustbe between 1 and 12");
}
if (1 == month || 3 == month || 5 == month || 7 == month || 8 == month || 10 == month || 12 == month)
{
return 31;
}
else if (2 == month)
{
if (0 == (year % 4))
{
if (0 == (year % 400))
{
return 29;
}
else if (0 == (year % 100))
{
return 28;
}
return 29;
}
return 28;
}
return 30;
}
protected void btnReport_Click(object sender, EventArgs e)
{
string bno = DrpBatchNo.SelectedValue.ToString().Trim();
string icode = DrpInsName.SelectedValue.ToString().Trim();
string year21 = txtYear.Text.ToString().Trim();
string month21 = DrpMonth.SelectedValue.ToString().Trim();
int year = 0;
if (year21 != "")
{
year = Convert.ToInt32(year21);
}
int month = 0;
if (month21 != "")
{
month = Convert.ToInt32(month21);
}
if (year21.Length == 4)
{
if ((bno != "All" || bno != "") && (icode != "All" || icode != ""))
{
//Check No Of Days
int days = GetDaysInMonth(Convert.ToInt32(DrpMonth.SelectedValue), Convert.ToInt32(txtYear.Text.ToString().Trim()));
//Create a DataTable
DataTable myDT = new DataTable();
for (int i = 1; i <= days; i++)
{
if (i == 1)
{
DataColumn id2 = new DataColumn("id");
//Create a First column Name
id2.ColumnName = "Member Code";
id2.MaxLength = 60;
myDT.Columns.Add(id2);
DataColumn id1 = new DataColumn("id");
//Create a First Second Name
id1.ColumnName = "Member Name";
id1.MaxLength = 60;
myDT.Columns.Add(id1);
}
// loop end after i < = days
DataColumn id = new DataColumn("id");
// i is a day 1 column until end of days
id.ColumnName = i.ToString("#00");
id.MaxLength = 4;
myDT.Columns.Add(id);
}
for (int i = 1; i <= 1; i++)
{
// This Datatable is used to select all members from member table
DataTable dt = TRN.RGetAttendance(bno);
if (dt.Rows.Count > 0)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
DataRow dr = myDT.NewRow();
string name = dt.Rows[j][1].ToString();
string mcode = dt.Rows[j][0].ToString();
// single Member code until end of month days
for (int ii = 0; ii <= days + 1; ii++)
{
if (ii == 0)
{
dr[ii] = mcode;
}
if (ii == 1)
{
dr[ii] = name;
}
else if (ii > 1)
{
string attdate = (ii - 1).ToString("#00") + "/" + month.ToString("#00") + "/" + year.ToString("#0000");
string day1 = attdate.Substring(0, 2);
string month1 = attdate.Substring(3, 2);
string year1 = attdate.Substring(6, 4);
int day11 = 0;
int month11 = 0;
int year11 = 0;
if (day1 != "" && month1 != "" && year1 != "")
{
day11 = Convert.ToInt32(day1);
month11 = Convert.ToInt32(month1);
year11 = Convert.ToInt32(year1);
attdate = datetimeconvert(year11, month11, day11);
}
// This Datatable is used to select a data from member code
DataTable dt1 = TRN.RGetAttendance1(mcode, attdate);
if (dt1.Rows.Count > 0)
{
string status = dt1.Rows[0][3].ToString();
dr[ii] = status;
}
}
}
// add a name, code, no of days present or absent
myDT.Rows.Add(dr);
}
}
}
//Bind All rows in Gridview
GrdAttendance.DataSource = myDT;
GrdAttendance.DataBind();
}
else
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "Alert", "<script>alert('" + "Select Valid Institute and Batch Name" + "');</script>", false);
}
}
else
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "Alert", "<script>alert('" + "Enter a Valid Year" + "');</script>", false);
}
}
public static string datetimeconvert(int a, int b, int c)
{
string ye2 = Convert.ToString(a);
string mo2 = Convert.ToString(b);
string da2 = Convert.ToString(c);
if (mo2.Length == 1)
{
mo2 = "0" + mo2;
}
if (da2.Length == 1)
{
da2 = "0" + da2;
}
string[] allStr = new String[] { mo2, da2, ye2 };
string datetime = String.Join("/", allStr);
return datetime;
}
Table
CREATE TABLE [dbo].[attendance_dtl](
[attend_date] [smalldatetime] NULL,
[attend_time] [varchar](20) NULL,
[attend_membercode] [varchar](25) NULL,
[attend_status] [char](1) NULL,
[attend_batchno] [varchar](20) NULL,
[attend_institute_code] [int] NULL
)
Stored Procedure in Sql
CREATE PROCEDURE [dbo].[Sp_RGetAttendance]
@bno varchar(100)
AS
BEGIN
Select trainee_dtl.trainee_membercode, trainee_dtl.trainee_membername from trainee_dtl
where trainee_dtl.trainee_course_batchno = @bno
END
CREATE PROCEDURE [dbo].[Sp_RGetAttendance1]
@mcode varchar(100),
@attdate smalldatetime
AS
BEGIN
Select tnpvp_attendance_dtl.* from attendance_dtl
where attendance_dtl.attend_membercode = @mcode and attendance_dtl.attend_date = @attdate
END
Output
No comments:
Post a Comment