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