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