private DataTable GetWeeklyReport(DateTime startdate, DateTime enddate, int userid) { DataTable dtAttandence = new DataTable(); try { lblWeekReportheading.Text = "Weekly summary attendance report"; DateTime endOfMonth = GeneralFunction.GetFirstDayOfWeekDate(enddate); lblWeekReport.Text = "( Wk of " + startdate.ToString("MM/dd/yyyy") + " - Wk of" + endOfMonth.ToString("MM/dd/yyyy") + " )"; dtAttandence.Columns.Add("empid", typeof(string)); dtAttandence.Columns.Add("Empname", typeof(string)); dtAttandence.Columns.Add("StatingDate", typeof(string)); dtAttandence.Columns.Add("TermDate", typeof(string)); dtAttandence.Columns.Add("TermReason", typeof(string)); dtAttandence.Rows.Add(); Attendance.BAL.Report obj = new Report(); DataSet ds = obj.GetActiveUsers(startdate, enddate, userid, ViewState["Location"].ToString().Trim()); for (int j = 0; j < 4; j++) { DataSet dsResult = obj.GetWeeklyReport(startdate, startdate.AddDays(6), userid, ViewState["Location"].ToString().Trim()); if (ds.Tables[0].Rows.Count > 0) { dtAttandence.Columns.Add("Week" + (j + 1), typeof(string)); dtAttandence.Columns.Add("Days" + (j + 1), typeof(string)); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (j == 0) { dtAttandence.Rows.Add(); } dtAttandence.Rows[i]["empid"] = ds.Tables[0].Rows[i]["empid"].ToString(); dtAttandence.Rows[i]["empname"] = ds.Tables[0].Rows[i]["firstName"].ToString() + " " + ds.Tables[0].Rows[i]["lastname"].ToString(); dtAttandence.Rows[i]["StatingDate"] = ds.Tables[0].Rows[i]["Startdate"].ToString(); dtAttandence.Rows[i]["TermDate"] = ds.Tables[0].Rows[i]["Termdate"].ToString(); if (dsResult.Tables.Count > 0) { if (dsResult.Tables[0].Rows.Count > 1) { DataTable dt = dsResult.Tables[0]; DataView dv = dt.DefaultView; DataTable dtname = new DataTable(); dv.RowFilter = "empid='" + ds.Tables[0].Rows[i]["empid"].ToString() + "'"; dtname = dv.ToTable(); if (dtname.Rows.Count > 0) { dtAttandence.Rows[i]["Week" + (j + 1)] = dtname.Rows[0]["weeklyhrs"].ToString() == "NULL" ? "" : dtname.Rows[0]["weeklyhrs"].ToString() == "" ? "" : GeneralFunction.CalNumericToint(Convert.ToDouble(dtname.Rows[0]["weeklyhrs"].ToString())).ToString(); dtAttandence.Rows[i]["Days" + (j + 1)] = dtname.Rows[0]["days"].ToString() == "NULL" ? 0 : dtname.Rows[0]["days"].ToString() == "" ? 0 : Convert.ToInt32(dtname.Rows[0]["days"].ToString()); } } } } startdate = startdate.AddDays(7); enddate = startdate.AddDays(6); } } int TotalHrs1 = 0; int TotalHrs2 = 0; int TotalHrs3 = 0; int TotalHrs4 = 0; int TotalDays = 0; int Days1 = 0; int Days2 = 0; int Days3 = 0; int Days4 = 0; dtAttandence.Columns.Add("Totalhrs"); dtAttandence.Columns.Add("Days"); for (int i = 0; i < dtAttandence.Rows.Count - 1; i++) { TotalHrs1 = TotalHrs1 + ((dtAttandence.Rows[i]["Week1"].ToString() == "Null") ? 0 : (dtAttandence.Rows[i]["Week1"].ToString() == "") ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Week1"])); TotalHrs2 = TotalHrs2 + (dtAttandence.Rows[i]["Week2"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Week2"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Week2"])); TotalHrs3 = TotalHrs3 + ((dtAttandence.Rows[i]["Week3"].ToString() == "Null" ? 0 : (dtAttandence.Rows[i]["Week3"].ToString() == "") ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Week3"]))); TotalHrs4 = TotalHrs4 + (dtAttandence.Rows[i]["Week4"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Week4"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Week4"])); int WeekHrs = ((dtAttandence.Rows[i]["Week1"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Week1"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Week1"]))) + (dtAttandence.Rows[i]["Week2"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Week2"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Week2"])) + (dtAttandence.Rows[i]["Week3"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Week3"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Week3"])) + (dtAttandence.Rows[i]["Week4"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Week4"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Week4"])); dtAttandence.Rows[i]["Totalhrs"] = WeekHrs == 0 ? "" : GeneralFunction.ConverttoTime(WeekHrs).ToString(); dtAttandence.Rows[i]["Days"] = (dtAttandence.Rows[i]["Days1"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days1"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days1"])) + (dtAttandence.Rows[i]["Days2"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days2"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days2"])) + (dtAttandence.Rows[i]["Days3"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days3"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days3"])) + (dtAttandence.Rows[i]["Days4"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days4"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days4"])); Days1 += dtAttandence.Rows[i]["Days1"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days1"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days1"]); Days2 += dtAttandence.Rows[i]["Days2"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days2"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days2"]); Days3 += dtAttandence.Rows[i]["Days3"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days3"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days3"]); Days4 += dtAttandence.Rows[i]["Days4"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days4"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days4"]); TotalDays += dtAttandence.Rows[i]["Days"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days"].ToString()); } dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Week1"] = TotalHrs1; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Week2"] = TotalHrs2; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Week3"] = TotalHrs3; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Week4"] = TotalHrs4; int sum = TotalHrs1 + TotalHrs2 + TotalHrs3 + TotalHrs4; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Totalhrs"] = sum == 0 ? "" : GeneralFunction.ConverttoTime(sum); dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Days"] = TotalDays; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Days1"] = Days1; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Days2"] = Days2; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Days3"] = Days3; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Days4"] = Days4; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["empid"] = "<b>Totals</b>"; } catch (Exception ex) { } return dtAttandence; }
private DataTable GetMonthlyreport(DateTime startdate, DateTime endMonth, int userid) { DataTable dtAttandence = new DataTable(); try { lblWeekReportheading.Text = "Monthly summary attendance report"; lblWeekReport.Text = "( " + startdate.ToString("MM/dd/yyyy") + " - " + endMonth.ToString("MM/dd/yyyy") + " )"; dtAttandence.Columns.Add("empid", typeof(string)); dtAttandence.Columns.Add("Empname", typeof(string)); dtAttandence.Columns.Add("StatingDate", typeof(string)); dtAttandence.Columns.Add("TermDate", typeof(string)); dtAttandence.Columns.Add("TermReason", typeof(string)); dtAttandence.Columns.Add("Days", typeof(int)); dtAttandence.Rows.Add(); Attendance.BAL.Report obj = new Report(); DateTime enddate = startdate.AddMonths(1).AddSeconds(-1); DataSet ds = obj.GetActiveUsers(startdate, endMonth, userid, ViewState["Location"].ToString().Trim()); for (int j = 0; j < 6; j++) { DataSet dsResult = obj.GetWeeklyReport(startdate, enddate, userid, ViewState["Location"].ToString().Trim()); if (ds.Tables[0].Rows.Count > 0) { dtAttandence.Columns.Add("Month" + (j + 1), typeof(string)); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (j == 0) { dtAttandence.Rows.Add(); } dtAttandence.Rows[i]["empid"] = ds.Tables[0].Rows[i]["empid"].ToString(); dtAttandence.Rows[i]["empname"] = ds.Tables[0].Rows[i]["firstName"].ToString() + " " + ds.Tables[0].Rows[i]["lastname"].ToString(); dtAttandence.Rows[i]["StatingDate"] = ds.Tables[0].Rows[i]["Startdate"].ToString(); dtAttandence.Rows[i]["TermDate"] = ds.Tables[0].Rows[i]["Termdate"].ToString(); if (dsResult.Tables.Count > 0) { if (dsResult.Tables[0].Rows.Count > 1) { DataTable dt = dsResult.Tables[0]; DataView dv = dt.DefaultView; DataTable dtname = new DataTable(); dv.RowFilter = "empid='" + ds.Tables[0].Rows[i]["empid"].ToString() + "'"; dtname = dv.ToTable(); if (dtname.Rows.Count > 0) { // dtAttandence.Rows[i]["Month" + (j + 1)] = dtname.Rows[0]["weeklyhrs"].ToString(); dtAttandence.Rows[i]["Month" + (j + 1)] = dtname.Rows[0]["weeklyhrs"].ToString() == "NULL" ? "" : dtname.Rows[0]["weeklyhrs"].ToString() == "" ? "" : GeneralFunction.CalNumericToint(Convert.ToDouble(dtname.Rows[0]["weeklyhrs"].ToString())).ToString(); dtAttandence.Rows[i]["Days"] = (dtAttandence.Rows[i]["Days"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days"].ToString())) + (dtname.Rows[0]["days"].ToString() == "NULL" ? 0 : Convert.ToInt32(dtname.Rows[0]["days"].ToString())); } } } } startdate = enddate.AddSeconds(1); enddate = startdate.AddMonths(1).AddSeconds(-1); } } int TotalHrs1 = 0; int TotalHrs2 = 0; int TotalHrs3 = 0; int TotalHrs4 = 0; int TotalHrs5 = 0; int TotalHrs6 = 0; int TotalDays = 0; dtAttandence.Columns.Add("Totalhrs"); for (int i = 0; i < dtAttandence.Rows.Count-1; i++) { TotalHrs1 = TotalHrs1 + ((dtAttandence.Rows[i]["Month1"].ToString() == "Null") ? 0 : (dtAttandence.Rows[i]["Month1"].ToString() == "") ? 0 :Convert.ToInt32(dtAttandence.Rows[i]["Month1"])); TotalHrs2 = TotalHrs2 + (dtAttandence.Rows[i]["Month2"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Month2"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Month2"])); TotalHrs3 = TotalHrs3 + ((dtAttandence.Rows[i]["Month3"].ToString() == "Null" ? 0 : (dtAttandence.Rows[i]["Month3"].ToString() == "") ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Month3"]))); TotalHrs4 = TotalHrs4 + (dtAttandence.Rows[i]["Month4"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Month4"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Month4"])); TotalHrs5 = TotalHrs5 + (dtAttandence.Rows[i]["Month5"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Month5"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Month5"])); TotalHrs6 = TotalHrs6 + (dtAttandence.Rows[i]["Month6"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Month6"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Month6"])); int WeekHrs = ((dtAttandence.Rows[i]["Month1"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Month1"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Month1"]))) + (dtAttandence.Rows[i]["Month2"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Month2"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Month2"])) + (dtAttandence.Rows[i]["Month3"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Month3"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Month3"])) + (dtAttandence.Rows[i]["Month4"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Month4"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Month4"]))+ (dtAttandence.Rows[i]["Month5"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Month5"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Month5"]))+ (dtAttandence.Rows[i]["Month6"].ToString() == "Null" ? 0 : dtAttandence.Rows[i]["Month6"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Month6"])); dtAttandence.Rows[i]["Totalhrs"] = WeekHrs == 0 ? "" : GeneralFunction.ConverttoTime(WeekHrs); TotalDays += Convert.ToInt32(dtAttandence.Rows[i]["Days"]); } dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Month1"] = TotalHrs1==0?"":TotalHrs1.ToString(); dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Month2"] = TotalHrs2==0?"":TotalHrs2.ToString(); dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Month3"] = TotalHrs3==0?"":TotalHrs3.ToString(); dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Month4"] = TotalHrs4==0?"":TotalHrs4.ToString(); dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Month5"] = TotalHrs5==0?"":TotalHrs5.ToString(); dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Month6"] = TotalHrs6==0?"":TotalHrs6.ToString(); int sum=TotalHrs1+TotalHrs2+TotalHrs3+TotalHrs4+TotalHrs5+TotalHrs6; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Totalhrs"]=sum==0?"":GeneralFunction.ConverttoTime(sum); dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Days"] = TotalDays; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["empid"] = "<b>Totals</b>"; } catch (Exception ex) { } return dtAttandence; }