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