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