//public DataTable GetTeamList(string leaderId, DateTime beginDate, DateTime endDate, string type = "99") public string GetTeamList(string xmlString) { string result = "", employeeIDs = "", val = "", filterString = "", sql = ""; result = "<GetTeamCallList>" + "<Result>False</Result>" + "<Description></Description>" + "<DataRows></DataRows>" + "</GetTeamCallList>"; try { XmlDocument doc = new XmlDocument(); doc.LoadXml(xmlString); XmlNode vNode = doc.SelectSingleNode("GetTeamCallList/EmployeeIDs"); if (vNode == null || vNode.InnerText.Trim().Length == 0)//没有设置下属ID时,通过LeaderID来获取其下属的IDs { vNode = doc.SelectSingleNode("GetTeamCallList/LeaderID"); if (vNode != null && vNode.InnerText.Trim().Length > 0) { val = vNode.InnerText.Trim(); WorkShip w = new WorkShip(); string xmlParam = "<GetTeamMembers><LeaderID>" + val + "</LeaderID></GetTeamMembers>"; employeeIDs = w.GetTeamMemberIDs(xmlParam); if (employeeIDs.Length == 0)//没有直接下属,直接返回 { return(result); } else { filterString = filterString.Trim().Length > 0 ? filterString + " and t1.FEmployeeID In ('" + employeeIDs.Replace("|", "','") + "')" : " t1.FEmployeeID In ('" + employeeIDs.Replace("|", "','") + "')"; } } else { throw new Exception("团队领导ID不能为空"); } } else//已设置了相应的下属IDs,直接读取 { val = vNode.InnerText.Trim(); if (val.Length > 0) { filterString = filterString.Trim().Length > 0 ? filterString + " and t1.FEmployeeID In ('" + val.Replace("|", "','") + "')" : " t1.FEmployeeID In ('" + val.Replace("|", "','") + "')"; } } vNode = doc.SelectSingleNode("GetTeamCallList/BeginDate"); if (vNode != null) { val = vNode.InnerText.Trim(); if (val.Length > 0) { filterString = filterString.Length > 0 ? filterString + " And t1.FDate >= '" + val + " 0:0:0.000'" : " t1.FDate >= '" + val + " 0:0:0.000'"; } } vNode = doc.SelectSingleNode("GetTeamCallList/EndDate"); if (vNode != null) { val = vNode.InnerText.Trim(); if (val.Length > 0) { filterString = filterString.Length > 0 ? filterString + " And t1.FDate <= '" + val + " 23:59:59.999'" : " t1.FDate <= '" + val + " 23:59:59.999'"; } } sql = "Select t1.*,Isnull(t2.FName,'') As InstitutionName,'' As ClientName,Isnull(t4.FName,'') As EmployeeName," + " (Left(CONVERT(varchar(100), t1.FStartTime, 108),5) +'~'+ Left(CONVERT(varchar(100), t1.FEndTime, 108),5)) As TimeString,t1.FStartTime As Date" + " From [CallActivity] t1" + " Left Join t_Items t2 On t1.FInstitutionID= t2.FID" + " Left Join t_Items t4 On t1.FEmployeeID= t4.FID"; if (filterString.Length > 0) { sql = sql + " Where " + filterString; } sql = sql + " Order by t1.FStartTime Desc"; SQLServerHelper runner = new SQLServerHelper(); DataTable dt = runner.ExecuteSql(sql); result = Common.DataTableToXml(dt, "GetTeamCallList", "", "List"); } catch (Exception err) { throw err; } return(result); }
//public DataTable GetTeamList(string leaderId, DateTime beginDate, DateTime endDate, string type = "99") public string GetTeamList(string xmlString) { string result = "", employeeIDs = "", val = "", filterString = "", dateFilter = ""; result = "<GetTeamScheduleList>" + "<Result>False</Result>" + "<Description></Description>" + "<DataRows></DataRows>" + "</GetTeamScheduleList>"; try { XmlDocument doc = new XmlDocument(); doc.LoadXml(xmlString); XmlNode vNode = doc.SelectSingleNode("GetTeamScheduleList/EmployeeIDs"); if (vNode == null || vNode.InnerText.Trim().Length == 0)//没有设置下属ID时,通过LeaderID来获取其下属的IDs { vNode = doc.SelectSingleNode("GetTeamScheduleList/LeaderID"); if (vNode == null || vNode.InnerText.Trim().Length == 0) { throw new Exception("团队领导ID不能为空"); } else { val = vNode.InnerText.Trim(); WorkShip w = new WorkShip(); string xmlParam = "<GetTeamMembers><LeaderID>" + val + "</LeaderID></GetTeamMembers>"; employeeIDs = w.GetTeamMemberIDs(xmlParam); if (employeeIDs.Length == 0)//没有直接下属,直接返回 { return(result); } else { filterString = filterString.Trim().Length > 0 ? filterString + " and t1.FExcutorID In ('" + employeeIDs.Replace("|", "','") + "')" : " t1.FExcutorID In ('" + employeeIDs.Replace("|", "','") + "')"; } } } else//已设置了相应的下属IDs,直接读取 { val = vNode.InnerText.Trim(); if (val.Length > 0) { filterString = filterString.Trim().Length > 0 ? filterString + " and t1.FExcutorID In ('" + val.Replace("|", "','") + "')" : " t1.FExcutorID In ('" + val.Replace("|", "','") + "')"; } } vNode = doc.SelectSingleNode("GetTeamScheduleList/BeginDate"); if (vNode != null) { val = vNode.InnerText.Trim(); if (val.Length > 0) { dateFilter = dateFilter.Length > 0 ? dateFilter + " And t2.FStartTime >= '" + val + " 0:0:0.000'" : " t2.FStartTime >= '" + val + " 0:0:0.000'"; } } vNode = doc.SelectSingleNode("GetTeamScheduleList/FSEndTime"); if (vNode != null) { val = vNode.InnerText.Trim(); if (val.Length > 0) { dateFilter = dateFilter.Length > 0 ? dateFilter + " And t2.FEndTime <= '" + val + " 23:59:59.999'" : " t2.FSEndTime <= '" + val + " 23:59:59.999'"; } } vNode = doc.SelectSingleNode("GetTeamScheduleList/Type"); if (vNode != null) { val = vNode.InnerText.Trim(); if (val.Length > 0 && val != "99") { ///将类型代码换为ID val = TypeNumber2ID(val); filterString = filterString.Length > 0 ? filterString + " and t2.FType In ('" + val.Replace("|", "','") + "')" : " t2.FType In ('" + val.Replace("|", "','") + "')"; } } if (dateFilter.Length > 0) { filterString = filterString + (filterString.Trim().Length > 0 ? " and " + dateFilter : dateFilter); } string sql = "SELECT (Left(CONVERT(varchar(100), t2.FStartTime, 108),5) +'~'+ Left(CONVERT(varchar(100), t2.FEndTime, 108),5)) As TimeString," + " t1.FExcutorID,t3.FName AS FExcutorName,Isnull(t4.FName,'') +':'+ t2.FSubject As SubjectString ,t1.FScheduleID" + " FROM ScheduleExecutor t1" + " Left Join Schedule t2 On t1.FScheduleID= t2.FID" + " Left Join t_Items t3 On t1.FExcutorID= t3.FID" + " Left Join t_Items t4 On t4.FID= t2.FInstitutionID"; if (filterString.Length > 0) { sql = sql + " Where " + filterString; } sql = sql + " order by t2.FStartTime Desc"; SQLServerHelper runner = new SQLServerHelper(); DataTable dt = runner.ExecuteSql(sql); result = Common.DataTableToXml(dt, "GetTeamScheduleList", "", "List"); } catch (Exception err) { throw err; } return(result); }
public string Update(string dataString) { string id = "", sql = "", valueString = "", leaderId = "-1"; string result = "-1"; SQLServerHelper runner = new SQLServerHelper(); try { dataString = dataString.Replace("UpdateDepartment>", "UpdateItem>"); XmlDocument doc = new XmlDocument(); XmlNode vNode; doc.LoadXml(dataString); //更新消息信息 vNode = doc.SelectSingleNode("UpdateItem/FSupervisorID"); string val = ""; if (vNode != null) { val = vNode.InnerText.Trim(); if (val.Length > 0 || val != "-1") { valueString = valueString + "FSupervisorID='" + val + "',"; leaderId = val; } } id = iClass.Update(dataString); if (id == "-1")//插入t_items表错误 { result = "-1"; } if (doc.SelectSingleNode("UpdateItem/ID").InnerText.Trim() == "" || doc.SelectSingleNode("UpdateItem/ID").InnerText.Trim() == "-1")//新增 { sql = "Insert into t_Departments(FID) Values('" + id + "')"; if (runner.ExecuteSqlNone(sql) < 0)//插入新日程失败 { throw new Exception("新建失败"); } } vNode = doc.SelectSingleNode("UpdateItem/FIntroduce"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FIntroduce='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FIsPartTime"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FIsPartTime='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FSortIndex"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FSortIndex='" + val + "',"; } } if (valueString.Trim().Length > 0) { valueString = valueString.Substring(0, valueString.Length - 1); sql = "Update t_Departments Set " + valueString + " Where FID='" + id + "'"; if (runner.ExecuteSqlNone(sql) < 0)//更新消息失败 { id = "-1"; throw new Exception("更新失败"); } } if (leaderId != "-1")//主管更新,维护t_Workships表 { WorkShip ws = new WorkShip(); ws.Update(leaderId, id); } } catch (Exception err) { if (id != "-1")//t_tems已插入数据成功,要删除 { sql = "Delete from t_Items Where FID='" + id + "' Delete from t_Departments Where FID='" + id + "'"; runner.ExecuteSqlNone(sql); } throw err; } result = id; return(result); }
public string Update(string dataString) { string id = "", sql = "", valueString = "", dptId = ""; string result = "-1"; SQLServerHelper runner = new SQLServerHelper(); try { dataString = dataString.Replace("UpdateEmployee>", "UpdateItem>"); XmlDocument doc = new XmlDocument(); XmlNode vNode; doc.LoadXml(dataString); //更新消息信息 vNode = doc.SelectSingleNode("UpdateItem/FDeptID"); string val = ""; if (vNode == null || vNode.InnerXml.Trim().Length == 0) { throw new Exception("所在部门ID不能为空"); } else { val = vNode.InnerText; dptId = val; if (val.Trim().Length > 0) { valueString = valueString + "FDeptID='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FPositionID"); if (vNode == null || vNode.InnerXml.Trim().Length == 0) { throw new Exception("职位ID不能为空"); } else { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FPositionID='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FLoginName"); if (vNode == null || vNode.InnerXml.Trim().Length == 0) { throw new Exception("登录名不能为空"); } else { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FLoginName='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FLoginPwd"); if (vNode == null || vNode.InnerXml.Trim().Length == 0) { throw new Exception("登录密码不能为空"); } else { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FLoginPwd='" + val + "',"; } } id = iClass.Update(dataString); if (id == "-1")//插入t_items表错误 { result = "-1"; } if (doc.SelectSingleNode("UpdateItem/ID").InnerText.Trim() == "" || doc.SelectSingleNode("UpdateItem/ID").InnerText.Trim() == "-1")//新增 { sql = "Insert into t_Employees(FID) Values('" + id + "')"; if (runner.ExecuteSqlNone(sql) < 0)//插入新日程失败 { throw new Exception("新建失败"); } } vNode = doc.SelectSingleNode("UpdateItem/FIntroduce"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FIntroduce='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FMobile"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FMobile='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FMail"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FMail='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FRoleID"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FRoleID='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FPageID"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FPageID='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FRemark"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FRemark='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FWechat"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FWechat='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FTypeID"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FTypeID='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FIsAgency"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FIsAgency='" + val + "',"; } } vNode = doc.SelectSingleNode("UpdateItem/FSortIndex"); if (vNode != null) { val = vNode.InnerText; if (val.Trim().Length > 0) { valueString = valueString + "FSortIndex='" + val + "',"; } } if (valueString.Trim().Length > 0) { valueString = valueString.Substring(0, valueString.Length - 1); sql = "Update t_Employees Set " + valueString + " Where FID='" + id + "'"; if (runner.ExecuteSqlNone(sql) < 0)//更新消息失败 { id = "-1"; throw new Exception("更新失败"); } } if (dptId != "-1") { WorkShip ws = new WorkShip(); ws.Update(dptId); } } catch (Exception err) { if (id != "-1")//t_tems已插入数据成功,要删除 { sql = "Delete from t_Items Where FID='" + id + "' Delete from t_Employees Where FID='" + id + "'"; runner.ExecuteSqlNone(sql); } throw err; } result = id; return(result); }