private DataTable GetMonthlyreport(DateTime startdate, DateTime endMonth) { 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.GetActiveUsersAdmin(startdate, endMonth, ViewState["Location"].ToString().Trim()); for (int j = 0; j < 6; j++) { DataSet dsResult = obj.GetWeeklyReportAdmin(startdate, enddate, ViewState["Location"].ToString().Trim()); if (ds.Tables[0].Rows.Count > 0) { dtAttandence.Columns.Add("Month" + (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]["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" + (j + 1)] = dtname.Rows[0]["days"].ToString() == "NULL" ? 0 : dtname.Rows[0]["days"].ToString() == "" ? 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; int Days1 = 0; int Days2 = 0; int Days3 = 0; int Days4 = 0; int Days5 = 0; int Days6 = 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); 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"])) + (dtAttandence.Rows[i]["Days5"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days5"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days5"])) + (dtAttandence.Rows[i]["Days6"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days6"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days6"])); 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"]); Days5 += dtAttandence.Rows[i]["Days5"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days5"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days5"]); Days6 += dtAttandence.Rows[i]["Days6"].ToString() == "NULL" ? 0 : dtAttandence.Rows[i]["Days6"].ToString() == "" ? 0 : Convert.ToInt32(dtAttandence.Rows[i]["Days6"]); 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]["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(); dtAttandence.Rows[dtAttandence.Rows.Count - 1]["empid"] = "<b>Totals</b>"; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Days"] = TotalDays; int sum = TotalHrs1 + TotalHrs2 + TotalHrs3 + TotalHrs4 + TotalHrs5 + TotalHrs6; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Totalhrs"] = sum == 0 ? "" : GeneralFunction.ConverttoTime(sum); } catch (Exception ex) { } return dtAttandence; }
private DataTable GetWeeklyReport(DateTime startdate, DateTime enddate) { 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.GetActiveUsersAdmin(startdate, enddate, ViewState["Location"].ToString().Trim()); for (int j = 0; j < 4; j++) { DataSet dsResult = obj.GetWeeklyReportAdmin(startdate, startdate.AddDays(6), 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() == "NULL" ? "" : ds.Tables[0].Rows[i]["Startdate"].ToString(); dtAttandence.Rows[i]["TermDate"] = ds.Tables[0].Rows[i]["Termdate"].ToString() == "NULL" ? "" : 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(); 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 == 0 ? "" : TotalHrs1.ToString(); dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Week2"] = TotalHrs2 == 0 ? "" : TotalHrs2.ToString(); dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Week3"] = TotalHrs3 == 0 ? "" : TotalHrs3.ToString(); dtAttandence.Rows[dtAttandence.Rows.Count - 1]["Week4"] = TotalHrs4 == 0 ? "" : TotalHrs4.ToString(); int SUM = TotalHrs1 + TotalHrs2 + TotalHrs3 + TotalHrs4; dtAttandence.Rows[dtAttandence.Rows.Count - 1]["empid"] = "<b>Totals</b>"; 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]["Totalhrs"] = SUM == 0 ? "" : GeneralFunction.ConverttoTime(SUM).ToString(); } catch (Exception ex) { } return dtAttandence; }