private void DoGrid() { Id = Request["Id"]; if (!string.IsNullOrEmpty(Id)) { Pent = ProjectAttendance.Find(Id); if (Pent == null) { return; } dt = new DataTable(); DataColumn dc = new DataColumn("Id"); dt.Columns.Add(dc); dc = new DataColumn("UserId"); dt.Columns.Add(dc); dc = new DataColumn("UserName"); dt.Columns.Add(dc); for (int i = 26; i < 32; i++) { dc = new DataColumn("c" + i); dt.Columns.Add(dc); } for (int i = 1; i < 26; i++) { dc = new DataColumn("c" + i); dt.Columns.Add(dc); } dc = new DataColumn("SumDay"); dt.Columns.Add(dc); dc = new DataColumn("monthAdd"); dt.Columns.Add(dc); // IList<ProjectUser> puEnts = ProjectUser.FindAllByProperty(ProjectUser.Prop_ProjectId, paEnt.ProjectId); sql = "select UserId,UserName from NCRL_SP..ProjectAttendanceDetail where ProjectAttendanceId='" + Pent.Id + "' group by UserName,UserId "; DataTable count = DataHelper.QueryDataTable(sql); for (int i = 0; i < count.Rows.Count; i++) { DataRow dr = dt.NewRow(); dr["UserId"] = count.Rows[i].ItemArray[0]; dr["UserName"] = count.Rows[i].ItemArray[1]; IList <ProjectAttendanceDetail> padEnts = ProjectAttendanceDetail.FindAllByProperties(ProjectAttendanceDetail.Prop_ProjectAttendanceId, Pent.Id, ProjectAttendanceDetail.Prop_UserId, count.Rows[i].ItemArray[0]); int sumday = 0; foreach (ProjectAttendanceDetail padEnt in padEnts) { string adate = "c" + padEnt.AttendanceDate.Value.Day; dr[adate] = padEnt.AttendanceType; dr["Id"] = padEnt.Id; sumday++; } dr["SumDay"] = sumday; dt.Rows.Add(dr); } string str = JsonHelper.GetJsonStringFromDataTable(dt); string json = "{\"rows\":" + str + "}"; Response.Write(json); Response.End(); } }
private void SelectData() { string Year = Request["Year"]; string Month = Request["Month"]; if (!string.IsNullOrEmpty(Year) && !string.IsNullOrEmpty(Month)) { sql = "select UserName,UserId,ProjectAttendanceId from NCRL_SP..ProjectAttendanceDetail where ProjectAttendanceId in (select Id from NCRL_SP..CmpAttendance where Year =" + Year + " and Month= " + Month + ") group by UserName,UserId,ProjectAttendanceId"; dics = DataHelper.QueryDictList(sql); if (dics.Count > 0) { SetPageUI(); for (int i = 0; i < dics.Count; i++) { DataRow dr = dt.NewRow(); dr["UserId"] = dics[i].Get <string>("UserId"); dr["UserName"] = dics[i].Get <string>("UserName"); sql = "select Name from NCRL_Portal..SysGroup where GroupID in (Select GroupID from NCRL_Portal..SysUserGroup where UserID= '" + dics[i].Get <string>("UserId") + "')"; IList <EasyDictionary> name = DataHelper.QueryDictList(sql); dr["DeptName"] = name[0].Get <string>("Name"); dr["ComId"] = dics[i].Get <string>("ProjectAttendanceId"); IList <ProjectAttendanceDetail> padEnts = ProjectAttendanceDetail.FindAllByProperties(ProjectAttendanceDetail.Prop_ProjectAttendanceId, dics[i].Get <string>("ProjectAttendanceId"), ProjectAttendanceDetail.Prop_UserId, dics[i].Get <string>("UserId")); foreach (ProjectAttendanceDetail padEnt in padEnts) { string adate = "c" + padEnt.AttendanceDate.Value.Day; dr[adate] = padEnt.AttendanceType; } dt.Rows.Add(dr); } string str = JsonHelper.GetJsonStringFromDataTable(dt); string json = "{\"rows\":" + str + "}"; Response.Write(json); Response.End(); } else { DateTime time = new DateTime(Convert.ToInt32(Year), Convert.ToInt32(Month), 1); time = time.AddMonths(-1); sql = "select UserName,UserId from NCRL_SP..ProjectAttendanceDetail where ProjectAttendanceId in (select Id from NCRL_SP..CmpAttendance where Year =" + time.Year.ToString() + " and Month= " + time.Month.ToString() + ") group by UserName,UserId"; dics = DataHelper.QueryDictList(sql); if (dics.Count > 0) { SetPageUI(); for (int i = 0; i < dics.Count; i++) { DataRow dr = dt.NewRow(); dr["UserId"] = dics[i].Get <string>("UserId"); dr["UserName"] = dics[i].Get <string>("UserName"); sql = "select Name from NCRL_Portal..SysGroup where GroupID in (Select GroupID from NCRL_Portal..SysUserGroup where UserID= '" + dics[i].Get <string>("UserId") + "')"; IList <EasyDictionary> name = DataHelper.QueryDictList(sql); dr["DeptName"] = name[0].Get <string>("Name"); dt.Rows.Add(dr); } } string str = JsonHelper.GetJsonStringFromDataTable(dt); string json = "{\"rows\":" + str + "}"; Response.Write(json); Response.End(); } } }
private void DoSelect() { DeptId = Request["DeptId"]; Year = Request["Year"]; Month = Request["Month"]; DataTable dt = new DataTable(); DataColumn dc = new DataColumn("ProjectName"); dt.Columns.Add(dc); dc = new DataColumn("UserId"); dt.Columns.Add(dc); dc = new DataColumn("UserName"); dt.Columns.Add(dc); for (int i = 26; i < 32; i++) { dc = new DataColumn("c" + i); dt.Columns.Add(dc); } for (int i = 1; i < 26; i++) { dc = new DataColumn("c" + i); dt.Columns.Add(dc); } dc = new DataColumn("RowNumber"); dt.Columns.Add(dc); dc = new DataColumn("SumDay"); dt.Columns.Add(dc); IList <ProjectAttendance> Pents = ProjectAttendance.FindAllByProperties("BelongDeptId", DeptId, "Year", Convert.ToInt32(Year), "Month", Convert.ToInt32(Month)); foreach (ProjectAttendance Pent in Pents) { int RowNumber = 0; sql = "select UserId,UserName,ProjectAttendanceId from NCRL_SP..ProjectAttendanceDetail where ProjectAttendanceId='" + Pent.Id + "' group by UserName,UserId,ProjectAttendanceId "; DataTable count = DataHelper.QueryDataTable(sql); for (int i = 0; i < count.Rows.Count; i++) { RowNumber++; DataRow dr = dt.NewRow(); dr["RowNumber"] = RowNumber; dr["UserId"] = count.Rows[i].ItemArray[0]; dr["UserName"] = count.Rows[i].ItemArray[1]; dr["ProjectName"] = Pent.ProjectName; IList <ProjectAttendanceDetail> padEnts = ProjectAttendanceDetail.FindAllByProperties(ProjectAttendanceDetail.Prop_ProjectAttendanceId, Pent.Id, ProjectAttendanceDetail.Prop_UserId, count.Rows[i].ItemArray[0]); int sumday = 0; foreach (ProjectAttendanceDetail padEnt in padEnts) { string adate = "c" + padEnt.AttendanceDate.Value.Day; dr[adate] = padEnt.AttendanceType; // dr["Id"] = padEnt.Id; sumday++; } dr["SumDay"] = sumday; dt.Rows.Add(dr); } } string str = JsonHelper.GetJsonStringFromDataTable(dt); string json = "{\"rows\":" + str + "}"; Response.Write(json); Response.End(); }