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();
}
