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 Create()
{
string Year = Request["Year"];
string Month = Request["Month"];
string day = Request["day"];
string SignType = Request["SignType"];
string ProjectId = Request["ProjectId"];
string UserId = Request["UserId"];
string UserName = Request["UserName"];
string Attachment = Request["Attachment"];
string Remark = Request["Remark"];
string Id = Request["Id"];
Project ProEnt = Project.Find(ProjectId);
//查询是否存在该项目该年此月份的考勤
sql = @"select * from NCRL_SP..ProjectAttendance where Year='" + Convert.ToInt32(Year) + "' and Month='" + Convert.ToInt32(Month) + "' and ProjectId='" + ProjectId + "'";
DataTable dt = DataHelper.QueryDataTable(sql);
//查询是否存在该部门本该年此月份的考勤
sql = @"select Id from NCRL_SP..DeptAttendance where Year='" + Convert.ToInt32(Year) + "'and Month='" + Convert.ToInt32(Month) + "' and BelongDeptId ='" + ProEnt.BelongDeptId + "'";
DataTable da = DataHelper.QueryDataTable(sql);
//查询本公司是否存在该年此月份的考勤
sql = @"select * from NCRL_SP..CmpAttendance where Year='" + Convert.ToInt32(Year) + "'and Month='" + Convert.ToInt32(Month) + "'";
DataTable ca = DataHelper.QueryDataTable(sql);
if (string.IsNullOrEmpty(Id))
{
if (dt.Rows.Count > 0)
{
return;
}
Pent = new ProjectAttendance();
Pent.Attachment = Attachment;
Pent.Remark = Remark;
Pent.ProjectId = ProjectId;
Pent.ProjectName = ProEnt.ProjectName;
Pent.Year = Convert.ToInt32(Year);
Pent.Month = Convert.ToInt32(Month);
Pent.PManagerId = ProEnt.PManagerId;
Pent.PManagerName = ProEnt.PManagerName;
Pent.BelongDeptId = ProEnt.BelongDeptId;
Pent.BelongDeptName = ProEnt.BelongDeptName;
Pent.CreateId = WebPortalService.CurrentUserInfo.UserID;
Pent.CreateName = WebPortalService.CurrentUserInfo.Name;
Pent.CreateTime = DateTime.Now;
Pent.DoCreate();
if (da.Rows.Count <= 0)
{
dent = new DeptAttendance();
dent.Year = Convert.ToInt32(Year);
dent.Month = Convert.ToInt32(Month);
dent.BelongDeptId = Pent.BelongDeptId;
dent.BelongDeptName = Pent.BelongDeptName;
dent.DoCreate();
}
if (ca.Rows.Count <= 0)
{
cent = new CmpAttendance();
cent.Year = Convert.ToInt32(Year);
cent.Month = Convert.ToInt32(Month);
cent.DoCreate();
}
}
else
{
Pent = ProjectAttendance.Find(Id);
if (Pent.Attachment != Attachment || Pent.Remark != Remark)
{
Pent.Attachment = Attachment;
Pent.Remark = Remark;
Pent.DoUpdate();
}
}
sql = "select * from NCRL_SP..ProjectAttendanceDetail where UserId='" + UserId + "'and CONVERT(varchar(10), AttendanceDate,120)='" + Convert.ToDateTime(Year + "-" + Month + "-" + day).ToString("yyyy-MM-dd") + "' and ProjectAttendanceId<>'" + Id + "' or ProjectAttendanceId is null ";
IList <EasyDictionary> dicss = DataHelper.QueryDictList(sql);
if (dicss.Count > 0)
{
ents = ProjectAttendanceDetail.Find(dicss[0].Get <string>("Id"));
ents.AttendanceType = SignType;
if (!string.IsNullOrEmpty(Id))
{
ents.ProjectAttendanceId = Id;
}
else
{
ents.ProjectAttendanceId = Pent.Id;
}
ents.Description = "";
ents.DoUpdate();
if (!string.IsNullOrEmpty(Id))
{
Response.Write("{success: true ,id:'" + Id + "'}");
Response.End();
return;
}
Response.Write("{success: true ,id:'" + Pent.Id + "'}");
Response.End();
return;
}
sql = "select * from NCRL_SP..ProjectAttendanceDetail where UserId='" + UserId + "'and CONVERT(varchar(10), AttendanceDate,120)='" + Convert.ToDateTime(Year + "-" + Month + "-" + day).ToString("yyyy-MM-dd") + "' and ProjectAttendanceId='" + Id + "'";
dicss = DataHelper.QueryDictList(sql);
if (dicss.Count > 0)
{
ents = ProjectAttendanceDetail.Find(dicss[0].Get <string>("Id"));
if (ents.AttendanceType != SignType)
{
ents.AttendanceType = SignType;
}
else
{
sql = "delete NCRL_SP..ProjectAttendanceDetail where UserId='" + UserId + "'and CONVERT(varchar(10), AttendanceDate,120)='" + Convert.ToDateTime(Year + "-" + Month + "-" + day).ToString("yyyy-MM-dd") + "' and ProjectAttendanceId='" + Id + "'";
DataHelper.ExecSql(sql);
Response.Write("{success: true ,id:'" + Id + "'}");
Response.End();
return;
}
ents.CreateTime = System.DateTime.Now;
ents.DoUpdate();
}
else
{
ents = new ProjectAttendanceDetail();
ents.UserId = UserId;
ents.UserName = UserName;
if (!string.IsNullOrEmpty(Id))
{
ents.ProjectAttendanceId = Id;
}
else
{
ents.ProjectAttendanceId = Pent.Id;
}
ents.AttendanceType = SignType;
ents.AttendanceDate = Convert.ToDateTime(Year + "-" + Month + "-" + day);
ents.CreateId = WebPortalService.CurrentUserInfo.UserID;
ents.CreateName = WebPortalService.CurrentUserInfo.Name;
ents.CreateTime = DateTime.Now;
ents.DoCreate();
}
if (!string.IsNullOrEmpty(Id))
{
Response.Write("{success: true ,id:'" + Id + "'}");
Response.End();
return;
}
Response.Write("{success: true ,id:'" + Pent.Id + "'}");
Response.End();
}
private void Create()
{
string Year = Request["Year"];
string Month = Request["Month"];
string day = Request["day"];
string UserId = Request["UserId"];
string UserName = Request["UserName"];
string SignType = Request["SignType"];
sql = "select * from NCRL_SP..CmpAttendance where Year =" + Year + " and Month= " + Month;
Cmps = DataHelper.QueryDictList(sql);
string ComId;
if (Cmps.Count <= 0)
{
Cent = new CmpAttendance();
Cent.Year = Convert.ToInt32(Year);
Cent.Month = Convert.ToInt32(Month);
Cent.DoCreate();
ComId = Cent.Id;
}
else
{
ComId = Cmps[0].Get <string>("Id");
}
sql = "select * from NCRL_SP..ProjectAttendanceDetail where UserId='" + UserId + "'and CONVERT(varchar(10), AttendanceDate,120)='" + Convert.ToDateTime(Year + "-" + Month + "-" + day).ToString("yyyy-MM-dd") + "' and ProjectAttendanceId='" + ComId + "'";
IList <EasyDictionary> dicss = DataHelper.QueryDictList(sql);
if (dicss.Count > 0)
{
Pdent = ProjectAttendanceDetail.Find(dicss[0].Get <string>("Id"));
if (Pdent.AttendanceType != SignType)
{
Pdent.AttendanceType = SignType;
}
else
{
sql = "delete NCRL_SP..ProjectAttendanceDetail where UserId='" + UserId + "'and CONVERT(varchar(10), AttendanceDate,120)='" + Convert.ToDateTime(Year + "-" + Month + "-" + day).ToString("yyyy-MM-dd") + "' and ProjectAttendanceId='" + ComId + "'";
DataHelper.ExecSql(sql);
return;
}
Pdent.CreateTime = System.DateTime.Now;
Pdent.DoUpdate();
}
else
{
Pdent = new ProjectAttendanceDetail();
Pdent.UserId = UserId;
Pdent.UserName = UserName;
Pdent.ProjectAttendanceId = ComId;
Pdent.AttendanceType = SignType;
Pdent.AttendanceDate = Convert.ToDateTime(Year + "-" + Month + "-" + day);
Pdent.CreateId = WebPortalService.CurrentUserInfo.UserID;
Pdent.CreateName = WebPortalService.CurrentUserInfo.Name;
Pdent.CreateTime = DateTime.Now;
Pdent.Description = "非项目人员考勤";
Pdent.DoCreate();
}
Response.Write("{success: true ,ComId:'" + ComId + "'}");
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();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
Aim.Portal.Web.WebPortalService.CheckLogon();
}
catch
{
Response.Write("<script> window.location.href = '/Login.aspx';</script>");
Response.End();
}
string action = Request["action"];
string sql = "";
string id = Request["id"];
DataTable dt = null;
CmpAttendance caEnt = null;
string where = "";
ProjectAttendanceDetail padEnt = null;
switch (action)
{
case "loaddetail":
if (!string.IsNullOrEmpty(id))
{
caEnt = CmpAttendance.Find(id);
dt = new DataTable();
DataColumn dc = new DataColumn("UserId");
dt.Columns.Add(dc);
dc = new DataColumn("DeptName");
dt.Columns.Add(dc);
dc = new DataColumn("ProjectName");
dt.Columns.Add(dc);
dc = new DataColumn("UserName");
dt.Columns.Add(dc);
DateTime dt_cur = Convert.ToDateTime(caEnt.Year + "-" + caEnt.Month + "-1");
DateTime dt_pre = dt_cur.AddMonths(-1);
int days = DateTime.DaysInMonth(dt_pre.Year, dt_pre.Month);
for (int i = 20; i <= days; i++)
{
dc = new DataColumn("C" + i);
dt.Columns.Add(dc);
}
for (int i = 1; i <= 19; i++)
{
dc = new DataColumn("C" + i);
dt.Columns.Add(dc);
}
if (!string.IsNullOrEmpty(Request["SearchValue"])) //如果是查询
{
if (Request["SearchCondition"] == "UserName")
{
where += " and Name like '%" + Request["SearchValue"] + "%'";
}
if (Request["SearchCondition"] == "DeptName")
{
where += " and Server_Seed like '%" + Request["SearchValue"] + "%'";
}
if (Request["SearchCondition"] == "ProjectName")
{
where += " and a.UserId in (select c.UserId from NCRL_SP..ProjectAttendanceDetail c left join NCRL_SP..ProjectAttendance b on c.ProjectAttendanceId=b.Id"
+ " where datepart(yyyy,c.AttendanceDate)='" + caEnt.Year + "' and datepart(mm,c.AttendanceDate)='" + caEnt.Month + "' and b.ProjectName like '%" + Request["SearchValue"] + "%')";
}
}
sql = "select a.UserId,a.Name as UserName ,a.Server_Seed as DeptName,a.Server_IAGUID from SysUser a"
+ " where a.Server_Seed like '%江西瑞林建设监理有限公司%' and a.Status=1" + where;
IList <EasyDictionary> dics = DataHelper.QueryDictList(GetPageSql(sql));
foreach (EasyDictionary dic in dics)
{
DataRow dr = dt.NewRow();
dr["UserId"] = dic.Get <string>("UserId");
dr["UserName"] = dic.Get <string>("UserName");
//取该人员本年度本月所在的项目 可能是多个
sql = @"select distinct ProjectAttendanceId from NCRL_SP..ProjectAttendanceDetail where UserId='{0}' and datepart(yyyy,AttendanceDate)='{1}' and
datepart(mm,AttendanceDate)='{2}'";
sql = string.Format(sql, dic.Get <string>("UserId"), caEnt.Year, caEnt.Month);
IList <EasyDictionary> dics_tmp = DataHelper.QueryDictList(sql);
string projectNames = "";
foreach (EasyDictionary dic_tmp in dics_tmp)
{
ProjectAttendance paEnt = ProjectAttendance.TryFind(dic_tmp.Get <string>("ProjectAttendanceId"));
if (paEnt != null)
{
projectNames += (string.IsNullOrEmpty(projectNames) ? "" : ",") + paEnt.ProjectName;
}
}
dr["ProjectName"] = projectNames;
string deptName = dic.Get <string>("DeptName") == "江西瑞林建设监理有限公司" ? "江西瑞林建设监理有限公司" : dic.Get <string>("DeptName").Replace("江西瑞林建设监理有限公司", "");
dr["DeptName"] = deptName;
for (int i = 20; i <= days; i++)
{
sql = @"select AttendanceType,ProjectAttendanceId from NCRL_SP..ProjectAttendanceDetail where UserId='{0}' and datepart(year,AttendanceDate)='{1}' and
datepart(month,AttendanceDate)='{2}' and datepart(day,AttendanceDate)='{3}'";
sql = string.Format(sql, dic.Get <string>("UserId"), dt_pre.Year, dt_pre.Month, i);
IList <EasyDictionary> dics_padetail = DataHelper.QueryDictList(sql);
string attendtype = "";
if (dics_padetail.Count > 0)
{
switch (dics_padetail[0].Get <string>("AttendanceType"))
{
case "正常上班":
attendtype = "√";
break;
case "请假":
attendtype = "!";
break;
case "其他":
attendtype = "×";
break;
}
}
dr["C" + i] = attendtype;
}
for (int i = 1; i <= 19; i++)
{
sql = @"select AttendanceType,ProjectAttendanceId from NCRL_SP..ProjectAttendanceDetail where UserId='{0}' and datepart(year,AttendanceDate)='{1}' and
datepart(month,AttendanceDate)='{2}' and datepart(day,AttendanceDate)='{3}'";
sql = string.Format(sql, dic.Get <string>("UserId"), dt_cur.Year, dt_cur.Month, i);
IList <EasyDictionary> dics_padetail = DataHelper.QueryDictList(sql);
string attendtype = "";
if (dics_padetail.Count > 0)
{
switch (dics_padetail[0].Get <string>("AttendanceType"))
{
case "正常上班":
attendtype = "√";
break;
case "请假":
attendtype = "!";
break;
case "其他":
attendtype = "×";
break;
}
}
dr["C" + i] = attendtype;
}
dt.Rows.Add(dr);
}
string title = "江西瑞林建设监理有限公司" + caEnt.Year + "年" + caEnt.Month + "月考勤表";
Response.Write("{success:true,total:" + totalProperty + ",rows:" + JsonHelper.GetJsonStringFromDataTable(dt) + ",title:'" + title + "'}");
Response.End();
}
break;
case "update":
if (!string.IsNullOrEmpty(id))
{
string result = Request["attendanttype"];
caEnt = CmpAttendance.Find(id);
DateTime dt_cur = Convert.ToDateTime(caEnt.Year + "-" + caEnt.Month + "-1");
DateTime dt_pre = dt_cur.AddMonths(-1);
int day = Convert.ToInt32(Request["day"]);
sql = @"select * from NCRL_SP..ProjectAttendanceDetail where UserId='{0}' and datepart(year,AttendanceDate)='{1}' and
datepart(month,AttendanceDate)='{2}' and datepart(day,AttendanceDate)='{3}'";
if (day > 19)
{
sql = string.Format(sql, Request["userid"], dt_pre.Year, dt_pre.Month, Request["day"]);
}
else
{
sql = string.Format(sql, Request["userid"], dt_cur.Year, dt_cur.Month, Request["day"]);
}
IList <EasyDictionary> dics_padetail = DataHelper.QueryDictList(sql);
if (dics_padetail.Count == 0)
{
DateTime?dt_attend = null;
if (day > 19)
{
dt_attend = Convert.ToDateTime(dt_pre.Year + "-" + dt_pre.Month + "-" + Request["day"]);
}
else
{
dt_attend = Convert.ToDateTime(dt_cur.Year + "-" + dt_cur.Month + "-" + Request["day"]);
}
padEnt = new ProjectAttendanceDetail();
padEnt.UserId = Request["userid"];
padEnt.UserName = Request["username"];
padEnt.AttendanceDate = dt_attend;
padEnt.AttendanceType = Request["attendanttype"];
padEnt.DoCreate();
}
else
{
padEnt = ProjectAttendanceDetail.Find(dics_padetail[0].Get <string>("Id"));
if (padEnt.AttendanceType == Request["attendanttype"]) //如果先前是正常上班 再次点击还是正常上班 就删除
{
padEnt.DoDelete();
result = "";
}
else
{
padEnt.AttendanceType = Request["attendanttype"];
padEnt.DoUpdate();
}
}
switch (result)
{
case "正常上班":
result = "√";
break;
case "请假":
result = "!";
break;
case "其他":
result = "×";
break;
}
Response.Write("{success:true,result:'" + result + "'}");
Response.End();
}
break;
}
}
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();
}
