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