public string Update(string supervisorID, string teamID)
{
string result = "-1", sql = "";
SQLServerHelper runner;
try
{
runner = new SQLServerHelper();
sql = "Select FID As FEmployeeID,FTypeID,FIsAgency From t_Employees Where FDeptID='" + teamID + "' And FIsDeleted=0";
DataTable dt = runner.ExecuteSql(sql);
foreach (DataRow row in dt.Rows)
{
sql = "Select * from t_Workships Where FEmployeeID='" + row["FEmployeeID"].ToString() + "' and FIsDeleted=0";
DataTable dtWorkship = runner.ExecuteSql(sql);
if (dtWorkship.Rows.Count == 0)//此员工尚未建立工作关系
{
sql = "INSERT INTO t_Workships(FTeamID,FEmployeeID,FTeamLeaderID,FBeginDate,FIsDeleted,FIsAgency)VALUES('" + teamID + "','" + row["FEmployeeID"].ToString() +
"','" + supervisorID + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "',0,'" + row["FIsAgency"].ToString() + "')";
runner.ExecuteSqlNone(sql);
}
else//workship表有此记录
{
foreach (DataRow wsRow in dtWorkship.Rows)
{
if (!wsRow["FTeamLeaderID"].ToString().Trim().Equals(supervisorID))//Workship表中的主管ID不同于当前LeaderID
{
sql = "Select FDeptID From t_Employees Where FID= '" + row["FEmployeeID"].ToString() + "' and FIsDeleted=0 And FDeptID !='" + teamID + "'";
DataTable dt2 = runner.ExecuteSql(sql);
if (dt2.Rows.Count == 0)//同部门，Leader变化
{
sql = "Udpdate t_Workships Set FIsDeleted=1,FEndDate='" + DateTime.Now.ToString("yyyy-MM-dd") + "'";
sql = sql + " Where FID=" + wsRow["FID"].ToString();
runner.ExecuteSqlNone(sql);
}
else//该员工在多个部门任职
{
sql = "INSERT INTO t_Workships(FTeamID,FEmployeeID,FTeamLeaderID,FBeginDate,FIsDeleted,FIsAgency)VALUES('" + teamID + "','" + row["FEmployeeID"].ToString() +
"','" + supervisorID + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "',0," + row["FIsAgency"].ToString() + ")";
runner.ExecuteSqlNone(sql);//插入此主管的汇报关系记录
}
}
}
}
}
}
catch (Exception err)
{
throw err;
}
return(result);
}
public string Delete(string xmlString)
{
string result = "-1", itemID = "-1", parentID = "-1";
XmlDocument doc = new XmlDocument();
try
{
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("DeleteItem/ID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("ID不能为空");
}
itemID = vNode.InnerText.Trim();
string sql = "Select FName, FID from t_items Where FIsDeleted=0 And FParentID='" + itemID + "'";
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
if (dt.Rows.Count > 0)
{
throw new Exception("该节点还下级节点，不能删除");
}
sql = "Update t_items Set FIsDeleted =1 Where FIsDeleted=0 And FID='" + itemID + "'";
if (runner.ExecuteSqlNone(sql) < 1)
{
itemID = "-1";
throw new Exception("操作成功");
}
sql = "Select FParentID From t_Items Where FID='" + itemID + "'";
dt = runner.ExecuteSql(sql);
if (dt.Rows.Count > 0)
{
parentID = dt.Rows[0]["FParentID"].ToString();
sql = "Select FParentID From t_Items Where FIsDetail=0 And FParentID='" + parentID + "'";
dt = runner.ExecuteSql(sql);
if (dt.Rows.Count == 0)
{
sql = "Update t_items Set FIsdetail =1 Where FID='" + parentID + "'";
runner.ExecuteSqlNone(sql);
}
}
}
catch (Exception err)
{
throw err;
}
result = itemID;
return(result);
}
public string CheckVCode(string xmlString)
{
string result = "0", mobile = "", sql = "", code = "";
string callType = "CheckVCode";
result = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<" + callType + ">" +
"<Result>False</Result>" +
"<Description></Description></" + callType + ">";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("CheckVCode/Mobile");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("Mobile不能为空");
}
else
{
mobile = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode("CheckVCode/Code");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("Code不能为空");
}
else
{
code = vNode.InnerText.Trim();
}
string curTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
sql = "Select FExpireTime From VCodes Where FMobile='" + mobile + "' and FCode ='" + code + "' and FStatus =0 and '" + curTime + "' Between FCreateTime and FExpireTime";
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
if (dt.Rows.Count == 0)//
{
throw new Exception("验证码错误或已过期");
}
else
{
sql = "Update VCodes Set FStatus =1 Where FMobile='" + mobile + "' and FCode ='" + code + "' and FStatus =0 and '" + curTime + "' Between FCreateTime and FExpireTime";
runner.ExecuteSqlNone(sql);
result = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<" + callType + ">" +
"<Result>True</Result>" +
"<Description>验证码正确</Description></" + callType + ">";
}
}
catch (Exception err)
{
throw err;
}
return(result);
}
public string Delete(string xmlMessage)
{
string result = "-1", callID = "";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlMessage);
XmlNode vNode = doc.SelectSingleNode("DeleteCall/ID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("拜访记录ID不能为空");
}
callID = vNode.InnerText.Trim();
string sql = "Delete from CallActivity Where FID = '" + callID + "'";
SQLServerHelper runner = new SQLServerHelper();
result = runner.ExecuteSqlNone(sql).ToString();
}
catch (Exception err)
{
throw err;
}
if (int.Parse(result) > 0)
{
result = callID;
}
else
{
result = "-1";
}
return(result);
}
public string Delete(string xmlString)
{
string result = "", scheduleID = "-1";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("DeleteSchedule/ID");
if (vNode == null || vNode.InnerText.Length == 0)
{
throw new Exception("日程ID不能为空");
}
else
{
scheduleID = vNode.InnerText.Trim();
}
string sql = "Delete from Schedule Where FID = '" + scheduleID + "' Delete from ScheduleExecutor Where FScheduleID = '" + scheduleID + "'";
SQLServerHelper runner = new SQLServerHelper();
result = runner.ExecuteSqlNone(sql).ToString();
}
catch (Exception err)
{
scheduleID = "-1";
throw err;
}
result = scheduleID;
return(result);
}
public string Delete(string xmlString)
{
string result = "-1", id = "-1", sql = "";
XmlDocument doc = new XmlDocument();
try
{
xmlString = xmlString.Replace("DeleteDoctor>", "DeleteItem>");
Items item = new Items();
id = item.Delete(xmlString);
if (id.Trim() != "-1")//t_Items删除成功
{
sql = " Update t_Doctors Set FIsDeleted =1 Where FID='" + id + "' And FIsDeleted=0";
SQLServerHelper runner = new SQLServerHelper();
runner.ExecuteSqlNone(sql);
}
}
catch (Exception err)
{
sql = "Update t_Doctors Set FIsDeleted =0 Where FID='" + id + "' And FIsDeleted=1 Update t_Items Set FIsDeleted =0 Where FID='" + id + "' And FIsDeleted=1";
SQLServerHelper runner = new SQLServerHelper();
runner.ExecuteSqlNone(sql);
throw err;
}
result = id;
return(result);
}
public string SendVCode(string xmlString)
{
string result = "", mobile = "", sql = "";
string callType = "SendVCode";
result = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<" + callType + ">" +
"<Result>False</Result>" +
"<Description></Description></" + callType + ">";
try
{
string vCode = "";
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("SendVCode/Mobile");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("Mobile不能为空");
}
else
{
mobile = vNode.InnerText.Trim();
}
string curTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
sql = "Select FCode from VCodes Where '" + curTime + "' Between FCreateTime and FExpireTime and FStatus =0 and FMobile='" + mobile + "'";
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
if (dt.Rows.Count > 0)//存在未验证且在有限期内
{
vCode = dt.Rows[0]["FCode"].ToString();
}
else
{
Random ran = new Random();
vCode = ran.Next(1000, 9999).ToString();
}
AliDayuSMS smsSender = new AliDayuSMS();
if (smsSender.SendSms(vCode, mobile) == "1" && dt.Rows.Count == 0)//发送成功,且不存在该记录
{
DateTime expireTime = DateTime.Now.AddMinutes(5);
sql = "Insert Into VCodes(FMobile,FCode)Values('" + mobile + "','" + vCode + "')";
runner = new SQLServerHelper();
if (runner.ExecuteSqlNone(sql) > 0)
{
result = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<" + callType + ">" +
"<Result>True</Result>" +
"<Description>OK</Description></" + callType + ">";
}
}
}
catch (Exception err)
{
throw err;
}
return(result);
}
private void SetInvoceCheckStatus(string invoceIID, DateTime checkDate, int chkCount = 3, string errdescription = "", string errCode = "")
{
string sql = "";
SQLServerHelper runner = new SQLServerHelper();
sql = @"update formson_5248 Set field0033= {0} ,field0042 ='{1}' ,field0025='{3}',field0024 ='{4}',field0027='{3}' Where ID={2}";
sql = string.Format(sql, chkCount, checkDate, invoceIID, errdescription, errCode);
runner.ExecuteSqlNone(sql);
}
public string Update(string xmlString)
{
string id = "", sql = "", valueString = "";
try
{
Dictionary <string, string> fieldValues = Common.GetFieldValuesFromXml(xmlString, "UpdateMarketingActivity");
SQLServerHelper runner = new SQLServerHelper();
if (fieldValues["FID"] == "-1" || fieldValues["FID"].Trim().Length == 0)
{
id = Guid.NewGuid().ToString();
sql = "Insert Into MarketingActivity(FID) Values('" + id + "')";
runner.ExecuteSqlNone(sql);
}
else
{
id = fieldValues["FID"];
}
foreach (string key in fieldValues.Keys)
{
if (key == "FID")
{
continue;
}
valueString = valueString + key + "='" + fieldValues[key] + "',";
}
if (valueString.Length > 0)
{
sql = "Update MarketingActivity Set " + valueString.Substring(0, valueString.Length - 1) + " Where FID ='" + id + "'";
}
runner.ExecuteSqlNone(sql);
}
catch (Exception err)
{
id = "-1";
throw err;
}
return(id);
}
public static void WriteToDB(string log, string fErrorCode = "", string fCheckErrcode = "", string fDescription = "", string fFileName = "", string fJsonData = "", string fInvoiceType = "", string type = "InvoiceMessage", string caller = "", string method = "")
{
try
{
string sql = $"Insert Into [DataService].[dbo].[InvoiceLogs](FLog,FErrorCode,FCheckErrcode,FDescription,FFileName,FJsonData,FInvoiceType,FCaller,FMethod)Values('{log}','{fErrorCode}','{fCheckErrcode}','{fDescription}','{fFileName}','{fJsonData}','{fInvoiceType}','{caller}','{method}')";
SQLServerHelper runner = new SQLServerHelper();
runner.ExecuteSqlNone(sql);
runner = null;
}
catch (Exception err)
{
throw err;
}
}
public string DeleteTripData(string xmlMessage)
{
string result = "-1", id = "", filter = "", feeId = "";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlMessage);
XmlNode vNode = doc.SelectSingleNode("DeleteTripData/ID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
}
else
{
id = vNode.InnerText.Trim();
filter = filter.Trim().Length > 0 ? filter = filter + " and FID ='" + id + "'" : filter = " FID ='" + id + "'";
}
vNode = doc.SelectSingleNode("DeleteTripData/FeeID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
}
else
{
feeId = vNode.InnerText.Trim();
filter = filter.Trim().Length > 0 ? filter = filter + " and FFeeID ='" + feeId + "'" : filter = " FFeeID ='" + feeId + "'";
}
if (filter.Trim().Length == 0)
{
throw new Exception("差旅费用ID和费用列表ID,不能同时为空");
}
string sql = "Update TripDetail Set FDeleted=1 Where " + filter;
SQLServerHelper runner = new SQLServerHelper();
result = runner.ExecuteSqlNone(sql).ToString();
}
catch (Exception err)
{
throw err;
}
if (int.Parse(result) > 0)
{
result = id;
}
else
{
result = "-1";
}
return(result);
}
//根据注册公司信息创建组织架构的根节点（公司）
public string CreateCompany(string xmlString)
{
string result = "", id = "", companyName = "";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("CreateCompany/CompanyName");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("公司名称不能为空");
}
else
{
companyName = vNode.InnerText.Trim();
}
id = Guid.NewGuid().ToString();
string sql = "Insert Into t_Items(FID,FCompanyID,FName,FNumber,FLevel,FFullNumber,FIsDetail)";
sql = sql + "Values('" + id + "','" + id + "','" + companyName + "','" + "A" + DateTime.Now.ToString("yyyyMMddhhmmss") + "','1','" + "A" + DateTime.Now.ToString("yyyyMMddhhmmss") + "',1)";
SQLServerHelper runner = new SQLServerHelper();
if (runner.ExecuteSqlNone(sql) > 0)
{
result = "<?xml version=\"1.0\" encoding=\"utf-8\"?><CreateCompany>" +
"<Result>True</Result>" +
"<Description></Description>" +
"<ID>" + id + "</ID>" +
"</CreateCompany>";
}
else
{
result = "<?xml version=\"1.0\" encoding=\"utf-8\"?><CreateCompany>" +
"<Result>False</Result>" +
"<Description></Description>" +
"<ID>" + id + "</ID>" +
"</CreateCompany>";
}
}
catch (Exception err)
{
throw err;
}
return(result);
}
public string Approve(string xmlString)
{
string result = "-1", sql = "", id = "-1", valueString = "";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("ApproveDoctor/ID");
if (vNode == null || vNode.InnerText.Length == 0)
{
throw new Exception("医生ID不能为空");
}
else
{
id = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode("UpdateItem/FApproverID");
if (vNode != null)
{
string val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = ",FApproverID='" + val + "'";
}
}
if (id.Trim() != "-1")//t_Items删除成功
{
sql = " Update t_Doctors Set FApproved =1,FApproveDate='" + DateTime.Now.ToString("yyyy-MM-dd") + "'" + valueString + " Where FID='" + id + "'";
SQLServerHelper runner = new SQLServerHelper();
runner.ExecuteSqlNone(sql);
}
}
catch (Exception err)
{
id = "-1";
throw err;
}
result = id;
return(result);
}
public string AppendFromEmployee(string employeeID)
{
string result = "";
try
{
SQLServerHelper runner = new SQLServerHelper();
string sql = "Insert into RepresentativeRegistration(FEmployeeID) Values('" + employeeID + "')";
if (runner.ExecuteSqlNone(sql) < 0)
{
throw new Exception("新建失败");
}
}
catch (Exception err)
{
throw err;
}
return(result);
}
public string Delete(string id)
{
string sql = "";
string result = "-1";
SQLServerHelper runner = new SQLServerHelper();
try
{
sql = "Update [Messages] Set FDeleted=1 Where FID= '" + id + "' Update MsgReceivers Set FDeleted=1 Where FMsgID='" + id + "'";
if (runner.ExecuteSqlNone(sql) > 0)
{
result = id;
}
}
catch (Exception err)
{
throw err;
}
return(result);
}
public string Delete(string classID)
{
string result = "";
XmlDocument doc = new XmlDocument();
try
{
string sql = "Update t_ItemClass Set FIsDeleted =1 Where FIsDeleted=0 and FID='" + classID + "'";
SQLServerHelper runner = new SQLServerHelper();
if (runner.ExecuteSqlNone(sql) < 1)
{
classID = "-1";
throw new Exception("删除信息失败");
}
}
catch (Exception err)
{
throw err;
}
result = classID;
return(result);
}
public string Delete(string xmlMessage)
{
string result = "-1", id = "";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlMessage);
XmlNode vNode = doc.SelectSingleNode("DeleteFeeData/ID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("FeeID不能为空");
}
id = vNode.InnerText.Trim();
string sql = "Update FeeList Set FDeleted=1 Where FID = '" + id + "'";
sql = sql + " Update ExpendDetail Set FDeleted=1 Where FFeeID = '" + id + "'";
sql = sql + " Update TripDetail Set FDeleted=1 Where FFeeID = '" + id + "'";
SQLServerHelper runner = new SQLServerHelper();
result = runner.ExecuteSqlNone(sql).ToString();
}
catch (Exception err)
{
throw err;
}
if (int.Parse(result) > 0)
{
result = id;
}
else
{
result = "-1";
}
return(result);
}
public string Delete(string routeID)
{
string result = "-1";
try
{
string sql = "Delete from RouteData Where FID = '" + routeID + "'";
SQLServerHelper runner = new SQLServerHelper();
result = runner.ExecuteSqlNone(sql).ToString();
}
catch (Exception err)
{
throw err;
}
if (int.Parse(result) > 0)
{
result = routeID;
}
else
{
result = "-1";
}
return(result);
}
public string UpdateHospitalStock(string xmlString)
{
string id = "", sql = "", valueString = "";
try
{
List <Dictionary <string, string> > formson = new List <Dictionary <string, string> >();
Dictionary <string, string> mainform = Common.GetFieldValuesFromXmlEx(xmlString, "UpdateHospitalStock", out formson, "1", "");
//获取周序数
int year, weekofyear;
Common.GetWeekIndexOfYear(mainform["FWeekIndex"], out year, out weekofyear);
mainform["FYear"] = year.ToString();
mainform["FWeekIndex"] = weekofyear.ToString();
SQLServerHelper runner = new SQLServerHelper();
if (mainform["FID"] == "-1" || mainform["FID"].Trim().Length == 0)
{
//判断是否已存在相应的本周进销存记录
sql = "Select FID from HospitalStock Where FYear='{0}' and FWeekIndex='{1}' and FEmployeeID ='{2}' and FProductID='{3}'";
sql = string.Format(sql, mainform["FYear"], mainform["FWeekIndex"], mainform["FEmployeeID"], mainform["FProductID"]);
DataTable dt = runner.ExecuteSql(sql);
if (dt.Rows.Count > 0)
{
mainform["FID"] = dt.Rows[0]["FID"].ToString();
id = mainform["FID"];
}
else
{
id = Guid.NewGuid().ToString();
sql = "Insert Into HospitalStock(FID) Values('" + id + "')";
runner.ExecuteSqlNone(sql);
}
}
else
{
id = mainform["FID"];
}
foreach (string key in mainform.Keys)
{
if (key == "FID")
{
continue;
}
valueString = valueString + key + "='" + mainform[key] + "',";
}
if (valueString.Length > 0)
{
sql = "Update HospitalStock Set " + valueString.Substring(0, valueString.Length - 1) + " Where FID ='" + id + "'";
}
runner.ExecuteSqlNone(sql);
//插入明细表
sql = "Delete from [HospitalStock_Detail] Where FFormmainID='" + id + "'";
runner.ExecuteSqlNone(sql);
foreach (Dictionary <string, string> dic in formson)
{
sql = @"Insert Into HospitalStock_Detail(FFormmainID,FHospitalID,FStock_IB,FStock_IN,FStock_EB,FSaleAmount)
Values('{0}','{1}',{2},{3},{4},{5})";
decimal saleAmount = Convert.ToDecimal(dic["FStock_IB"]) + Convert.ToDecimal(dic["FStock_IN"]) - Convert.ToDecimal(dic["FStock_EB"]);
sql = string.Format(sql, id, dic["FHospitalID"], dic["FStock_IB"], dic["FStock_IN"], dic["FStock_EB"], saleAmount);
runner.ExecuteSqlNone(sql);
}
}
catch (Exception err)
{
id = " - 1";
throw err;
}
return(id);
}
public string Update(string dataString)
{
string id = "", sql = "", valueString = "";
bool datachecked = true;
string result = "-1";
SQLServerHelper runner = new SQLServerHelper();
try
{
dataString = dataString.Replace("UpdateProduct>", "UpdateItem>");
XmlDocument doc = new XmlDocument();
XmlNode vNode;
doc.LoadXml(dataString);
//更新消息信息
vNode = doc.SelectSingleNode("UpdateItem/FTypeID");
string val = "";
if (vNode == null || vNode.InnerXml.Trim().Length == 0)
{
datachecked = false;
throw new Exception("产品类型ID不能为空");
}
else
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FTypeID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FSKU");
if (vNode == null || vNode.InnerXml.Trim().Length == 0)
{
datachecked = false;
throw new Exception("产品SKU不能为空");
}
else
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FSKU='" + val + "',";
}
}
id = iClass.Update(dataString);
if (id == "-1")//插入t_items表错误
{
result = "-1";
}
datachecked = true;
if (doc.SelectSingleNode("UpdateItem/ID").InnerText.Trim() == "" || doc.SelectSingleNode("UpdateItem/ID").InnerText.Trim() == "-1")//新增
{
sql = "Insert into t_Products(FID) Values('" + id + "')";
if (runner.ExecuteSqlNone(sql) < 0)//插入失败
{
throw new Exception("新建失败");
}
}
vNode = doc.SelectSingleNode("UpdateItem/FPackage");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FPackage='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FIntroduce");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FIntroduce='" + val + "',";
}
}
if (valueString.Trim().Length > 0)
{
valueString = valueString.Substring(0, valueString.Length - 1);
sql = "Update t_Products Set " + valueString + " Where FID='" + id + "'";
if (runner.ExecuteSqlNone(sql) < 0)//更新消息失败
{
id = "-1";
throw new Exception("更新失败");
}
}
}
catch (Exception err)
{
if (id != "-1" && datachecked)//t_tems已插入数据成功，要删除
{
sql = "Delete from t_Items Where FID='" + id + "' Delete from t_Products Where FID='" + id + "'";
runner.ExecuteSqlNone(sql);
}
throw err;
}
result = id;
return(result);
}
public string Update(string dataString)
{
string id = "", sql = "", valueString = "";
bool datachecked = true;
string result = "-1";
SQLServerHelper runner = new SQLServerHelper();
try
{
dataString = dataString.Replace("UpdateDoctor>", "UpdateItem>");
XmlDocument doc = new XmlDocument();
XmlNode vNode;
doc.LoadXml(dataString);
//更新消息信息
vNode = doc.SelectSingleNode("UpdateItem/FHospitalID");
string val = "";
if (vNode == null || vNode.InnerXml.Trim().Length == 0)
{
datachecked = false;
throw new Exception("医院ID不能为空");
}
else
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FHospitalID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FDeptID");
if (vNode == null || vNode.InnerXml.Trim().Length == 0)
{
datachecked = false;
throw new Exception("科室ID不能为空");
}
else
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FDeptID ='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FTitleID");
if (vNode == null || vNode.InnerXml.Trim().Length == 0)
{
datachecked = false;
throw new Exception("职称ID不能为空");
}
else
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FTitleID ='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FCompanyID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FCompanyID='" + val + "',";
}
}
id = iClass.Update(dataString);
if (id == "-1")//插入t_items表错误
{
result = "-1";
}
datachecked = true;
if (doc.SelectSingleNode("UpdateItem/ID").InnerText.Trim() == "" || doc.SelectSingleNode("UpdateItem/ID").InnerText.Trim() == "-1")//新增
{
sql = "Insert into t_Doctors(FID) Values('" + id + "')";
if (runner.ExecuteSqlNone(sql) < 0)//插入失败
{
throw new Exception("新建失败");
}
}
vNode = doc.SelectSingleNode("UpdateItem/FSortIndex");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FSortIndex='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FRankID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FRankID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FSpeciality");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FSpeciality='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FIntroduce");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FIntroduce='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FPhotoFile");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FPhotoFile='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FLicenseNumber");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FLicenseNumber='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FApproved");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FApproved='" + val + "',";
}
}
if (valueString.Trim().Length > 0)
{
valueString = valueString.Substring(0, valueString.Length - 1);
sql = "Update t_Doctors Set " + valueString + " Where FID='" + id + "'";
if (runner.ExecuteSqlNone(sql) < 0)//更新消息失败
{
id = "-1";
throw new Exception("更新失败");
}
}
}
catch (Exception err)
{
if (id != "-1" && datachecked)//t_tems已插入数据成功，要删除
{
sql = "Delete from t_Items Where FID='" + id + "' Delete from t_Doctors Where FID='" + id + "'";
runner.ExecuteSqlNone(sql);
}
throw err;
}
result = id;
return(result);
}
public string Update(string dataString)
{
string id = "", sql = "", valueString = "", result = "-1";
SQLServerHelper runner = new SQLServerHelper();
try
{
XmlDocument doc = new XmlDocument();
XmlNode vNode;
doc.LoadXml(dataString);
vNode = doc.SelectSingleNode("UpdateRegistration/FEmployeeID");
string val = "", employeeId = "";
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
new Exception("员工ID不能为空");
}
else
{
employeeId = vNode.InnerText;
}
id = doc.SelectSingleNode("UpdateRegistration/ID").InnerText;
if (id.Trim() == "" || id.Trim() == "-1")//新增
{
id = Guid.NewGuid().ToString();
sql = "Insert into RepresentativeRegistration(FEmployeeID) Values('" + employeeId + "')";
if (runner.ExecuteSqlNone(sql) < 0)//插入新日程失败
{
throw new Exception("新建失败");
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/FCompanyID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FCompanyID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/FContractNumber");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FContractNumber='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/FContractBeginDate");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FContractBeginDate='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/FContracEndDate");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FContracEndDate='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/FRegistrationNumber");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FRegistrationNumber='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/FOperatorID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FOperatorID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/FRemark");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FRemark='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/FCompany");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FCompany='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/FInstitution");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FInstitution='" + val + "',";
}
}
if (valueString.Trim().Length > 0)
{
valueString = valueString.Substring(0, valueString.Length - 1);
sql = "Update Reg_Application Set " + valueString + " Where FDeleted=0 And FEmployeeID='" + employeeId + "'";
if (runner.ExecuteSqlNone(sql) < 0)//更新消息失败
{
id = "-1";
throw new Exception("更新失败");
}
}
}
catch (Exception err)
{
throw err;
}
result = id;
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 = "";
bool datachecked = true;
string result = "-1";
SQLServerHelper runner = new SQLServerHelper();
try
{
dataString = dataString.Replace("UpdateHospital>", "UpdateItem>");
XmlDocument doc = new XmlDocument();
XmlNode vNode;
doc.LoadXml(dataString);
//更新消息信息
vNode = doc.SelectSingleNode("UpdateItem/FGrandID");
string val = "";
if (vNode == null || vNode.InnerXml.Trim().Length == 0)
{
datachecked = false;
throw new Exception("医院等级ID不能为空");
}
else
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FGrandID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FProvinceID");
if (vNode == null || vNode.InnerXml.Trim().Length == 0)
{
datachecked = false;
throw new Exception("省/直辖市ID不能为空");
}
else
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FProvinceID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FLatitude");
if (vNode == null || vNode.InnerXml.Trim().Length == 0)
{
datachecked = false;
throw new Exception("纬度不能为空");
}
else
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FLatitude='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FLongitude");
if (vNode == null || vNode.InnerXml.Trim().Length == 0)
{
datachecked = false;
throw new Exception("经度不能为空");
}
else
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FLongitude='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FAddress");
if (vNode == null || vNode.InnerXml.Trim().Length == 0)
{
datachecked = false;
throw new Exception("地址不能为空");
}
else
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FAddress='" + val + "',";
}
}
id = iClass.Update(dataString);
if (id == "-1")//插入t_items表错误
{
result = "-1";
}
datachecked = true;
if (doc.SelectSingleNode("UpdateItem/ID").InnerText.Trim() == "" || doc.SelectSingleNode("UpdateItem/ID").InnerText.Trim() == "-1")//新增
{
sql = "Insert into t_Hospital(FID) Values('" + id + "')";
if (runner.ExecuteSqlNone(sql) < 0)//插入失败
{
throw new Exception("新建失败");
}
}
vNode = doc.SelectSingleNode("UpdateItem/FCityID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FCityID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FCountryID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FCountryID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FTownID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FTownID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FPostcode");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FPostcode='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FTelNumber");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FTelNumber='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FContrac");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FContrac='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FAbbreviation");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FAbbreviation='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FSortIndex");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FSortIndex='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FRevenueLevelID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FRevenueLevelID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FHighLevelID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FHighLevelID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FModeID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FModeID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FIntroduce");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FIntroduce='" + val + "',";
}
}
if (valueString.Trim().Length > 0)
{
valueString = valueString.Substring(0, valueString.Length - 1);
sql = "Update t_Hospital Set " + valueString + " Where FID='" + id + "'";
if (runner.ExecuteSqlNone(sql) < 0)//更新消息失败
{
id = "-1";
throw new Exception("更新失败");
}
}
}
catch (Exception err)
{
if (id != "-1" && datachecked)//t_tems已插入数据成功，要删除
{
sql = "Delete from t_Items Where FID='" + id + "' Delete from t_Hospital Where FID='" + id + "'";
runner.ExecuteSqlNone(sql);
}
throw err;
}
result = id;
return(result);
}
public string Update(string xmlString)
{
string id = "", sql = "", valueString = "", FScheduleID = "-1", FEmployeeID = "-1", val = "";
string FRouteID = "";
int isNew = 0;
SQLServerHelper runner = new SQLServerHelper();
try
{
XmlDocument doc = new XmlDocument();
DataTable dt = new DataTable();
doc.LoadXml(xmlString);
id = doc.SelectSingleNode("UpdateCallData/ID").InnerText;
if (id.Trim() == "" || id.Trim() == "-1")//新增
{
isNew = 1;
}
XmlNode vNode = doc.SelectSingleNode("UpdateCallData/EmployeeID");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText;
FEmployeeID = val.Trim();
if (val.Trim().Length > 0)
{
valueString = valueString + "FEmployeeID='" + val + "',";
}
}
else
{
if (isNew == 1)
{
throw new Exception("拜访人ID不能为空");
}
}
vNode = doc.SelectSingleNode("UpdateCallData/Date");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText;
if (DateTime.Parse(val).CompareTo(DateTime.Now) > 0)
{
throw new Exception("拜访日期不能大于今天");
}
if (DateTime.Parse(val).CompareTo(DateTime.Now.AddDays(-2)) < 0)
{
throw new Exception("不能补录2天前的拜访记录");
}
if (val.Trim().Length > 0)
{
valueString = valueString + "FDate='" + val + "',";
}
}
else
{
if (isNew == 1)
{
throw new Exception("拜访日期不能为空");
}
}
vNode = doc.SelectSingleNode("UpdateCallData/Subject");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FSubject='" + val + "',";
}
}
else
{
if (isNew == 1)
{
throw new Exception("拜访主题不能为空");
}
}
vNode = doc.SelectSingleNode("UpdateCallData/InstitutionID");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FInstitutionID='" + val + "',";
}
}
else
{
if (isNew == 1)
{
throw new Exception("拜访机构不能为空");
}
}
vNode = doc.SelectSingleNode("UpdateCallData/DepartmentID_Ins");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FDepartmentID_Ins='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateCallData/ClientID");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FClientID='" + val + "',";
}
}
else
{
//if (isNew == 1) throw new Exception("拜访客户不能为空");
}
vNode = doc.SelectSingleNode("UpdateCallData/Activity");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FActivity='" + val + "',";
}
}
else
{
if (isNew == 1)
{
throw new Exception("拜访内容不能为空");
}
}
vNode = doc.SelectSingleNode("UpdateCallData/StartTime");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FStartTime='" + val + "',";
}
}
else
{
if (isNew == 1)
{
throw new Exception("拜访开始时间不能为空");
}
}
vNode = doc.SelectSingleNode("UpdateCallData/EndTime");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FEndTime='" + val + "',";
}
}
else
{
if (isNew == 1)
{
throw new Exception("拜访结束时间不能为空");
}
}
vNode = doc.SelectSingleNode("UpdateCallData/ProductID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FProductID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateCallData/Concept");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FConcept='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateCallData/Result");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FResult='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateCallData/Action");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FAction='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateCallData/ScheduleID");
if (vNode != null)
{
val = vNode.InnerText.Trim();
if (val.Trim().Length > 0)
{
FScheduleID = val;
valueString = valueString + "FScheduleID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateCallData/RouteID");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText.Trim();
FRouteID = val;
valueString = valueString + "FRouteID='" + val + "',";
}
vNode = doc.SelectSingleNode("UpdateCallData/Deliveries");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FDeliveries='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateCallData/Remark");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FRemark='" + val + "',";
}
}
//id = doc.SelectSingleNode("UpdateCallData/ID").InnerText;
//if (id.Trim() == "" || id.Trim() == "-1")//新增
if (isNew == 1)
{
if (FScheduleID.Trim() != "4484030a-28d1-4e5e-ba72-6655f1cb2898")
{
sql = "Select FID From CallActivity Where FScheduleID='" + FScheduleID + "'";
dt = runner.ExecuteSql(sql);
if (dt.Rows.Count > 0)
{
throw new Exception("该日程已完成拜访，不能再选择");
}
}
if (FRouteID.Trim().Length > 0)
{
sql = "Select FID From CallActivity Where FRouteID='" + FRouteID + "'";
dt = runner.ExecuteSql(sql);
if (dt.Rows.Count > 0)
{
throw new Exception("该签到已完成拜访，不能再选择");
}
}
id = Guid.NewGuid().ToString();
sql = "Insert into CallActivity(FID) Values('" + id + "') ";
if (runner.ExecuteSqlNone(sql) < 0)//插入失败
{
throw new Exception("添加拜访记录失败");
}
else
{
isNew = 2;
}
}
//经理点评
vNode = doc.SelectSingleNode("UpdateCallData/Comment");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText.Trim();
valueString = valueString + "FComment='" + val + "',";
}
//拜访类型
vNode = doc.SelectSingleNode("UpdateCallData/Type");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FType='" + val + "',";
}
}
//病人数
vNode = doc.SelectSingleNode("UpdateCallData/PatientList");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
string[] types = doc.SelectSingleNode("UpdateCallData/PatientList/Type").InnerText.Trim().Split(new[] { '|' });
string[] oldpatients = doc.SelectSingleNode("UpdateCallData/PatientList/OldPatient").InnerText.Trim().Split(new[] { '|' });
string[] newpatients = doc.SelectSingleNode("UpdateCallData/PatientList/NewPatient").InnerText.Trim().Split(new[] { '|' });
//删除已存在的
sql = "Delete from CallPatients Where FCallID ='{0}'";
sql = string.Format(sql, id);
runner.ExecuteSqlNone(sql);
for (int i = 0; i < types.Length; i++)
{
int iold = 0, inew = 0, itotal = 0;
if (oldpatients[i].Trim().Length > 0)
{
iold = int.Parse(oldpatients[i].Trim());
}
if (newpatients[i].Trim().Length > 0)
{
inew = int.Parse(newpatients[i].Trim());
}
itotal = iold + inew;
if (itotal > 0)
{
sql = @"Insert Into CallPatients( FCallID,FPatientType,FOldPatientCount,FNewPatientCount,FTotal)
Values('{0}','{1}',{2},{3},{4})";
sql = string.Format(sql, id, types[i], iold, inew, itotal);
runner.ExecuteSqlNone(sql);
}
}
}
//拜访详情
vNode = doc.SelectSingleNode("UpdateCallData/CallList");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
string[] deptids = doc.SelectSingleNode("UpdateCallData/CallList/DeptID").InnerText.Trim().Split(new[] { '|' });
string[] clientids = doc.SelectSingleNode("UpdateCallData/CallList/ClientID").InnerText.Trim().Split(new[] { '|' });
string[] aims = doc.SelectSingleNode("UpdateCallData/CallList/Aims").InnerText.Trim().Split(new[] { '|' });
string[] results = doc.SelectSingleNode("UpdateCallData/CallList/Result").InnerText.Trim().Split(new[] { '|' });
string[] improvements = doc.SelectSingleNode("UpdateCallData/CallList/Improvement").InnerText.Trim().Split(new[] { '|' });
//删除已存在的
sql = "Delete from CallDetail Where FCallID ='{0}'";
sql = string.Format(sql, id);
runner.ExecuteSqlNone(sql);
int year, weekOfyear;
for (int i = 0; i < deptids.Length; i++)
{
Common.GetWeekIndexOfYear("0", out year, out weekOfyear);
sql = @"Insert Into CallDetail( FCallID,FDeptID,FClientID,FAims,FResult,FImprovement)
Values('{0}','{1}','{2}','{3}','{4}','{5}')";
sql = string.Format(sql, id, deptids[i], clientids[i], aims[i], results[i], improvements[i]);
runner.ExecuteSqlNone(sql);
}
}
if (valueString.Trim().Length > 0)
{
valueString = valueString.Substring(0, valueString.Length - 1);
sql = "Update CallActivity Set " + valueString + " Where FID='" + id + "'";
if (FScheduleID != "4484030a-28d1-4e5e-ba72-6655f1cb2898")//计划内拜访，更新日程的是否执行信息
{
sql = sql + " Update ScheduleExecutor Set FIsExcuted =1 Where FScheduleID='" + FScheduleID + "' and FExcutorID='" + FEmployeeID + "'";
}
runner.ExecuteSqlNone(sql).ToString();
int year, weekOfyear;
Common.GetWeekIndexOfYear("0", out year, out weekOfyear);
sql = $"Update CallActivity Set FWeek='{DateTime.Now.Year + "-" + weekOfyear}',FMonth='{DateTime.Now.ToString("yyyy-MM")}' Where FID='" + id + "'";
runner.ExecuteSqlNone(sql);
}
}
catch (Exception err)
{
if (isNew == 2)//新增异常，删除相关已插入的数据
{
sql = "Delete from CallActivity where FID='" + id + "'";
runner.ExecuteSqlNone(sql);
sql = "Delete from CallPatients where FCallID='" + id + "'";
runner.ExecuteSqlNone(sql);
sql = "Delete from CallDetail where FCallID='" + id + "'";
runner.ExecuteSqlNone(sql);
}
id = "-1";
throw err;
}
return(id);
}
public string UploadImage(string xmlString)
{
string callType = "UploadRegImage";
string result = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<" + callType + ">" +
"<Result>False</Result>" +
"<Description></Description></" + callType + ">";
try
{
string base64String = "", fileName = "", ownerID = "", formId = "", fileNum = "";
string path = System.Configuration.ConfigurationManager.AppSettings["Path"];
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode(callType + "/FileNum");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("FileNum不能为空");
}
else
{
fileNum = vNode.InnerText.Trim();
}
string[] fileNos = fileNum.Split('|');
if (fileNos.Length == 0)
{
throw new Exception("FileNum参数格式不正确");
}
if (int.Parse(fileNos[1]) > 0)//只有上传附件个数大于0，才做判断
{
vNode = doc.SelectSingleNode(callType + "/Base64String");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("Base64String不能为空");
}
else
{
base64String = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode(callType + "/FileName");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("FileName不能为空");
}
else
{
fileName = vNode.InnerText.Trim();
}
}
vNode = doc.SelectSingleNode(callType + "/PageID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("PageID不能为空");
}
else
{
formId = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode(callType + "/OwnerID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("OwnerID不能为空");
}
else
{
ownerID = vNode.InnerText.Trim();
}
if (int.Parse(fileNos[1]) > 9)
{
throw new Exception("拟上传的附件个数已大于最大数9");
}
else if (int.Parse(fileNos[0]) == 1)//上传第一个附件，删除数据库中的相干附件
{
string sql = "Update Attachments Set FDeleted=1 where FPageID='{0}' and FOwnerID='{1}' ";
sql = string.Format(sql, formId, ownerID);
SQLServerHelper runner = new SQLServerHelper();
runner.ExecuteSqlNone(sql);
result = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<" + callType + ">" +
"<Result>True</Result>" +
"<Description></Description></" + callType + ">";
}
if (int.Parse(fileNos[1]) > 0)
{
string fileextra = "jpg";
if (fileName.Split('.').Length > 1)
{
fileextra = fileName.Split('.')[1];
}
fileName = Guid.NewGuid().ToString().Replace("-", "") + "." + fileextra;
if (FileHelper.UploadImage(base64String, path, fileName, formId, ownerID))
{
string url = System.Configuration.ConfigurationManager.AppSettings["URL"];
result = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<" + callType + ">" +
"<Result>True</Result>" +
"<ImageUrl>" + url + "/" + path + "/" + fileName + "</ImageUrl>" +
"<T_ImageUrl>" + url + "/" + path + "/T_" + fileName + "</T_ImageUrl>" +
"<Description></Description></" + callType + ">";
}
}
}
catch (Exception err)
{
throw err;
}
return(result);
}
public string Update(string dataString)
{
string id = "", sql = "", fullnumber = "", valueString = "", parentID = "-1", level = "1";
string result = "-1";
DataTable itemtb = new DataTable();
SQLServerHelper runner = new SQLServerHelper();
try
{
if (!CheckUpateData(dataString))
{
result = "-1";
}
XmlDocument doc = new XmlDocument();
XmlNode vNode;
doc.LoadXml(dataString);
id = doc.SelectSingleNode("UpdateItem/ID").InnerText;
if (id.Trim() == "" || id.Trim() == "-1")//新增
{
vNode = doc.SelectSingleNode("UpdateItem/FParentID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)//没有设置父节点，默认为一级节点
{
parentID = "-1";
level = "1";
fullnumber = doc.SelectSingleNode("UpdateItem/FNumber").InnerText;
}
else
{
parentID = vNode.InnerText;
sql = "Select FID,FName,FFullNumber,FLevel From t_items Where FID='" + parentID + "'";
itemtb = runner.ExecuteSql(sql);
if (itemtb.Rows.Count > 0)//存在该父级节点
{
fullnumber = itemtb.Rows[0]["FFullNumber"].ToString() + "." + doc.SelectSingleNode("UpdateItem/FNumber").InnerText;
level = (int.Parse(itemtb.Rows[0]["FLevel"].ToString()) + 1).ToString();
}
else//不存在该父节点
{
parentID = "-1";
level = "1";
fullnumber = doc.SelectSingleNode("UpdateItem/FNumber").InnerText;
}
}
id = Guid.NewGuid().ToString();
sql = "Insert into [t_Items](FID,FLevel,FParentID,FFullNumber,FNumber) Values('" + id + "','" + level + "','" + parentID + "','" + fullnumber + "','" + doc.SelectSingleNode("UpdateItem/FNumber").InnerText + "')";
if (runner.ExecuteSqlNone(sql) < 0)//插入新日程失败
{
throw new Exception("新建资料失败");
}
sql = "Update t_Items Set FIsDetail=0 Where FIsDetail=1 And FID='" + parentID + "'";
if (runner.ExecuteSqlNone(sql) < 0)//插入新日程失败
{
throw new Exception("新建资料失败");
}
}
//更新消息信息
vNode = doc.SelectSingleNode("UpdateItem/FName");
string val = "";
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FName='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FDescription");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FDescription='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateItem/FClassID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FClassID='" + val + "',";
}
}
if (valueString.Trim().Length > 0)
{
valueString = valueString.Substring(0, valueString.Length - 1);
sql = "Update [t_Items] Set " + valueString + " Where FID='" + id + "'";
if (runner.ExecuteSqlNone(sql) < 0)//更新消息失败
{
id = "-1";
throw new Exception("更新资料失败");
}
}
}
catch (Exception err)
{
throw err;
}
result = id;
return(result);
}
public string UpdateRegRelationship(string xmlString)
{
string result = "-1", sql = "", id = "", hospitalID = "";
DataTable dt = null;
try
{
SQLServerHelper runner = new SQLServerHelper();
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("UpdateRegistration/ID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("ID不能为空");
}
else
{
id = vNode.InnerText.Trim();
sql = "Select FID from Reg_Application Where FID='" + id + "'";
dt = runner.ExecuteSql(sql);
if (dt.Rows.Count == 0)
{
throw new Exception("ID不存在");
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/HospitalID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("HospitalID不能为空");
}
else
{
hospitalID = vNode.InnerText.Trim();
}
sql = "Delete from Reg_HospitalRelationShip Where FApplicationID='" + id + "'";
runner.ExecuteSqlNone(sql);
XmlNode dataNode = doc.SelectSingleNode("UpdateRegistration/Datas");
foreach (XmlNode node in dataNode.ChildNodes)
{
string name = node["Name"].InnerText;
string title = node["Title"].InnerText;
string dept = node["Department"].InnerText;
string relationship = node["RelationShipTypeID"].InnerText;
string sortindx = node["SortIndx"].InnerText;
sql = "Insert Into Reg_HospitalRelationShip(FApplicationID,FHospitalID,FName,FTitle,FDepartment,FRelationShipTypeID,FSortIndx) Values('";
sql = sql + id + "','" + hospitalID + "','" + name + "','" + title + "','" + dept + "','" + relationship + "'," + sortindx + ")";
runner.ExecuteSqlNone(sql);
}
result = id;
}
catch (Exception err)
{
throw err;
}
return(result);
}
//public string List(string filter = "")
//{
// DataTable result = new DataTable();
// try
// {
// SQLServerHelper runer = new SQLServerHelper();
// string sql = "Select t1.*,t2.FName As FTypeName,t3.FName As FProductName,t4.FName As FProvinceName,t5.FName As FCityName,"+
// " t6.FName As FCountryName,t6.FName As FCountryName,t7.FName As FApproveryName" +
// " From Reg_Application t1"+
// " Left Join t_items t2 On t2.FID = t1.FTypeID"+
// " Left Join t_items t3 On t3.FID = t1.FProductID"+
// " Left Join t_items t4 On t4.FID = t1.FProvinceID"+
// " Left Join t_items t5 On t5.FID = t1.FCityID"+
// " Left Join t_items t6 On t6.FID = t1.FCountryID"+
// " Left Join t_items t7 On t7.FID = t1.FApproverID";
// if (filter.Length > 0)
// sql = sql + " Where " + filter;
// result = runer.ExecuteSql(sql);
// }
// catch (Exception err)
// {
// throw err;
// }
// return result;
//}
#endregion List
#region Update
public string Update(string dataString)
{
string id = "", sql = "", valueString = "", result = "-1", val = "", mobile = "";
SQLServerHelper runner = new SQLServerHelper();
try
{
XmlDocument doc = new XmlDocument();
XmlNode vNode;
doc.LoadXml(dataString);
vNode = doc.SelectSingleNode("UpdateRegistration/Mobile");
if (vNode != null)
{
mobile = vNode.InnerText;
if (mobile.Trim().Length > 0)
{
valueString = valueString + "FMobile='" + mobile + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/ID");
if (vNode == null || vNode.InnerText.Trim() == "-1")//新增
{
//if(val.Trim().Length ==0)
// throw new Exception("手机号码不能为空");
//else
//{
// sql = "Select FID from Reg_Application Where FMobile ='{0}'";
// sql = string.Format(sql, mobile);
// DataTable dt = runner.ExecuteSql(sql);
// if (dt.Rows.Count > 0)
// id = dt.Rows[0]["FID"].ToString();
// else
// {
id = Guid.NewGuid().ToString();
sql = "Insert into Reg_Application(FID) Values('" + id + "')";
if (runner.ExecuteSqlNone(sql) < 0)//插入新日程失败
{
throw new Exception("新建失败");
}
// }
//}
}
else
{
id = vNode.InnerText.Trim();
}
//更新信息
vNode = doc.SelectSingleNode("UpdateRegistration/Applicant");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FApplicant='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/RegType");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FRegType='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/Registed");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FRegisted='" + val + "',";
}
}
//vNode = doc.SelectSingleNode("UpdateRegistration/Mobile");
//if (vNode != null)
//{
// val = vNode.InnerText;
// if (val.Trim().Length > 0)
// valueString = valueString + "FMobile='" + val + "',";
//}
vNode = doc.SelectSingleNode("UpdateRegistration/ProductID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FProductID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/ProvinceID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FProvinceID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/CityID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FCityID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/CountryID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FCountryID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/ApproverID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FApproverID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/ApproveDate");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FApproveDate='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/ProductTypeID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FProductTypeID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/HospitalID");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FHospitalID='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/HistoryPerformance");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FHistoryPerformance='" + val + "',";
}
}
vNode = doc.SelectSingleNode("UpdateRegistration/ForecastPerformance");
if (vNode != null)
{
val = vNode.InnerText;
if (val.Trim().Length > 0)
{
valueString = valueString + "FForecastPerformance='" + val + "',";
}
}
if (valueString.Trim().Length > 0)
{
valueString = valueString.Substring(0, valueString.Length - 1);
sql = "Update Reg_Application Set " + valueString + " Where FID='" + id + "'";
if (runner.ExecuteSqlNone(sql) < 0)//更新消息失败
{
id = "-1";
throw new Exception("更新失败");
}
}
}
catch (Exception err)
{
throw err;
}
result = id;
return(result);
}
public string UpdateRepresentative(string xmlString)
{
string result = "-1", sql;
string name = "", idNumber = "", gerder = "", education = "", major = "", mobile = "", address = "", experience = "", companyID = "", id = "";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
SQLServerHelper runner = new SQLServerHelper();
XmlNode vNode = doc.SelectSingleNode("UpdateRegistration/Name");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("姓名不能为空");
}
else
{
name = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode("UpdateRegistration/ID");
id = vNode.InnerText.Trim();
vNode = doc.SelectSingleNode("UpdateRegistration/IDNumber");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("身份证号码不能为空");
}
else
{
idNumber = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode("UpdateRegistration/Gender");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("性别不能为空");
}
else
{
gerder = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode("UpdateRegistration/Mobile");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("手机号码不能为空");
}
else
{
mobile = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode("UpdateRegistration/Education");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
education = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode("UpdateRegistration/MajorID");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
major = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode("UpdateRegistration/Address");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
address = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode("UpdateRegistration/Experience");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
experience = vNode.InnerText.Trim();
}
vNode = doc.SelectSingleNode("UpdateRegistration/CompanyID");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
companyID = vNode.InnerText.Trim();
}
//更新手机号码到注册登记表
Update(xmlString);
sql = "Select FMobile from Reg_Representative Where FMobile ='" + mobile + "'";
DataTable dt = runner.ExecuteSql(sql);
if (dt.Rows.Count > 0)// 已存在，更新
{
sql = "Update Reg_Representative Set FName='" + name + "',FIDNumber='" + idNumber + "',FGender='" + gerder + "',FEducation='" + education + "',FMajorID='" + major + "',FMobile='" + mobile + "',FAddress='" + address + "'" +
",FExperience='" + experience + "',FCompanyID='" + companyID + "' Where FMobile ='" + mobile + "'";
}
else
{
sql = "Insert Into Reg_Representative(FApplicationID,FName,FIDNumber,FGender,FEducation,FMajorID,FMobile,FAddress,FExperience,FCompanyID)" +
" Values('" + id + "','" + name + "','" + idNumber + "','" + gerder + "','" + education + "','" + major + "','" + mobile + "','" + address + "','" + experience + "','" + companyID + "')";
}
runner.ExecuteSqlNone(sql);
result = "1";
}
catch (Exception err)
{
throw err;
}
return(result);
}