public DataTable GetAllByParent(string parentID, bool isDetail = false)
{
DataTable result = new DataTable();
try
{
string pFNumber = "";
string sql = "select FNumber,FClassID from t_items Where FID='" + parentID + "' and FIsDeleted =0 ";
SQLServerHelper runner = new SQLServerHelper();
result = runner.ExecuteSql(sql);
if (result.Rows.Count > 0)
{
pFNumber = result.Rows[0]["FNumber"].ToString();
sql = "Select t1.* ,ISNULL(t2.FName,'') As FParentName,ISNULL(t3.FName,'') As FClassName" +
" From t_items t1" +
" Left Join t_items t2 On t1.FParentID=t2.FID" +
" Left Join t_itemclass t3 On t1.FClassID = t3.FID" +
" Where t1.FFullNumber like '" + pFNumber + "%' and t1.FIsDeleted =0 And t1.FClassID='" + result.Rows[0]["FClassID"].ToString() + "'";
if (isDetail)
{
sql = sql + " And t1.FIsDetail=1";
}
sql = sql + " Order by FNumber Asc";
result = runner.ExecuteSql(sql);
}
}
catch (Exception err)
{
throw err;
}
return(result);
}
public string AlterAutoStatus(string xmlstring)
{
string result = "<?xml version=\"1.0\" encoding=\"utf-8\"?><AlterState>" +
"<Result>False</Result>" +
"</AlterState>";
try
{
string status = "", mode = "", employeeId = "000000", sql = "";
XmlDocument doc = new XmlDocument();
XmlNode pNode = null, cNode = null;
doc.LoadXml(xmlstring);
DataTable dataTable = new DataTable();
XmlNode vNode = doc.SelectSingleNode("AlterAutoStatus/ID");
if (vNode == null && vNode.InnerText.Trim() == "")
{
throw new Exception("ID不能为空");
}
employeeId = vNode.InnerText.Trim();
//0是保存，1是获取
vNode = doc.SelectSingleNode("AlterAutoStatus/Mode");
mode = vNode.InnerText.Trim();
vNode = doc.SelectSingleNode("AlterAutoStatus/Status");
if (vNode != null && vNode.InnerText.Trim() != "")
{
status = vNode.InnerText.Trim();
}
SQLServerHelper runner = new SQLServerHelper();
//保存自动签到状态
if (mode == "0")
{
sql = $"if exists (select FID from yaodaibao.dbo.Profile where FEmployeeID='{ employeeId}') update yaodaibao.dbo.Profile set FRouteStatus = '{status}' where FEmployeeID = '{employeeId}' ELSE insert into yaodaibao.dbo.Profile(FEmployeeID, FRouteStatus) values('{employeeId}', '{status}') select FRouteStatus from Profile where FEmployeeID = '{employeeId}'";
dataTable = runner.ExecuteSql(sql);
result = $"<AlterAutoStatus><Result>True</Result><Status>{dataTable.Rows[0]["FRouteStatus"]}</Status></AlterAutoStatus>";
}
else
{
sql = $"select FRouteStatus from yaodaibao.dbo.Profile where FEmployeeID = '{employeeId}'";
dataTable = runner.ExecuteSql(sql);
//开启自动了签到
if (dataTable.Rows.Count > 0 && dataTable.Rows[0]["FRouteStatus"].ToString() == "True")
{
result = $"<AlterAutoStatus><Result>True</Result><Status>{dataTable.Rows[0]["FRouteStatus"]}</Status></AlterAutoStatus>";
}
else
{
result = $"<AlterAutoStatus><Result>False</Result><Status></Status></AlterAutoStatus>";
}
}
return(result);
}
catch (Exception e)
{
result = $"<?xml version=\"1.0\" encoding=\"utf-8\"?><AlterAutoStatus><Result>False</Result><Description>{e.Message}</Description></AlterAutoStatus>";
}
return(result);
}
public string GetAllMemberIDsByLeaderID(string leaderID, bool resQuery = false)
{
string deptID = "", sql = "";
deptIDs = "";
memberIDs = leaderID;
sql = "Select FID from t_Departments Where FIsDeleted =0 and FSupervisorID='" + leaderID + "'";
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
foreach (DataRow row in dt.Rows)
{
deptID = row["FID"].ToString();
if (resQuery)
{
GetResSubsID(deptID);
}
else
{
GetAllSbuDeptsByDeptID(deptID);
}
}
if (deptIDs.Length > 0)
{
deptIDs = deptIDs.Replace("|", "','");
sql = "Select FID from t_Employees Where FIsDeleted =0 and FDeptID in ('{0}') or FLeaderList like '%{1}%'";
sql = string.Format(sql, deptIDs, leaderID);
}
else
{
sql = "Select FID from t_Employees Where FIsDeleted =0 and FLeaderList like '%{0}%'";
sql = string.Format(sql, leaderID);
}
dt = runner.ExecuteSql(sql);
foreach (DataRow row in dt.Rows)
{
if (memberIDs.Length == 0)
{
memberIDs = row["FID"].ToString();
}
else
{
memberIDs = memberIDs + "|" + row["FID"].ToString();
}
}
return(memberIDs);
}
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 GetTripList(string xmlString)
{
string sql = "", feeID = "";
string result = "";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("GetTripList/FeeID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("FeeID不能为空");
}
else
{
feeID = vNode.InnerText;
}
sql = "Select FID,FFeeID,FStartDate,FDepartDate,FStartCity,FDepartCity,FFee1,FFee2,FFee3,FFee4 From TripDetail Where FFeeID ='{0}' and FDeleted=0";
sql = string.Format(sql, feeID);
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
result = Common.DataTableToXml(dt, "GetTripList", "", "List");
}
catch (Exception err)
{
throw err;
}
return(result);
}
public string GetExpendList(string xmlString)
{
string sql = "", feeID = "";
string result = "";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("GetExpendList/FeeID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("FeeID不能为空");
}
else
{
feeID = vNode.InnerText;
}
sql = " Select t1.FID,t1.FFeeID,t1.FAccountID1,ISnull(t2.FAccountCaptial1,'') As FAccountCaption1,t1.FAccountID2,IsNull(t2.FAccountCaptial2,'') As FAccountCaption2," +
" t1.FAmount,t1.FDigest" +
" From ExpendDetail t1" +
" Left Join t_Account t2 On t1.FAccountID1 = t2.FAccountID1 and t1.FAccountID2 = t2.FAccountID2" +
" Where t1.FDeleted=0 and t1.FFeeID = '{0}' and t1.FDeleted=0";
sql = string.Format(sql, feeID);
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
result = Common.DataTableToXml(dt, "GetExpendList", "", "List");
}
catch (Exception err)
{
throw err;
}
return(result);
}
//列出人员的拜访统计数量
public string GetAllVisitAmount(string employeeId, string weekindex, string calltype = "", string queryTable = "CallActivity", string startDate = "", string endDate = "")
{
try
{
string sql = $"Select 'true' as statis , t1.FEmployeeID,COUNT(FEmployeeID) as Amount,t4.FName as FName From {queryTable} t1 Left Join t_Items t4 On t1.FEmployeeID= t4.FID where t1.FEmployeeID in ('{employeeId}') and FWeek in ('{weekindex}') group by t1.FEmployeeID,FName order by Amount desc";
//string sql = $"SELECT (Left(CONVERT(varchar(100), t2.FStartTime, 120),16) +'~'+ Left(CONVERT(varchar(100), t2.FEndTime, 120),16)) As TimeString,t2.FEmployeeID as employeeId, t1.FExcutorID,t3.FName AS FExcutorName,t2.FSubject As SubjectString ,t1.FScheduleID As FID,t2.FInstitutionID As FInstitutionID,t4.FName As InstitutionName FROM ScheduleExecutor t1 Left Join Schedule t2 On t1.FScheduleID= t2.FID Left Join t_Items t3 On t1.FExcutorID= t3.FID Left Join t_Items t4 On t4.FID= t2.FInstitutionID where t2.FEmployeeID = '{employeeId}' and FStartTime between '{startDate}' and DATEADD(year, 1, '{endDate}') order by t2.FStartTime Desc";
if (calltype.Trim() != "")
{
if (string.IsNullOrEmpty(startDate) && string.IsNullOrEmpty(endDate))
{
sql = $"Select 'true' as statis , t1.FEmployeeID,COUNT(FEmployeeID) as Amount,t4.FName as FName From {queryTable} t1 Left Join t_Items t4 On t1.FEmployeeID= t4.FID where t1.FEmployeeID in ('{employeeId}') and FWeek in ('{weekindex}') and t1.FType IN ('{calltype}') group by FName, t1.FEmployeeID order by Amount desc";
}
else
{
sql = $"Select 'true' as statis , t1.FEmployeeID,COUNT(FEmployeeID) as Amount,t4.FName as FName From {queryTable} t1 Left Join t_Items t4 On t1.FEmployeeID= t4.FID where t1.FEmployeeID in ('{employeeId}') and FDate BETWEEN '{startDate}' AND '{endDate}' and t1.FType IN ('{calltype}') group by FName, t1.FEmployeeID order by Amount desc";
}
}
SQLServerHelper runHelper = new SQLServerHelper();
DataTable dt = runHelper.ExecuteSql(sql);
string result = Common.DataTableToXml(dt, "GetMultiReportJson", "", "List");
result = iTR.Lib.Common.XML2Json(result, "GetMultiReportJson");
return(result);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(HomeAccountingSystem.Model.jt_jc_sz model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into jt_jc_sz(");
strSql.Append("pk,v_key,v_value,v_mac_address,t_create_time,i_delete)");
strSql.Append(" values (");
strSql.Append("@pk,@v_key,@v_value,@v_mac_address,@t_create_time,@i_delete)");
SqlParameter[] parameters =
{
new SqlParameter("@pk", SqlDbType.Int, 4),
new SqlParameter("@v_key", SqlDbType.VarChar, 4000),
new SqlParameter("@v_value", SqlDbType.VarChar, 4000),
new SqlParameter("@v_mac_address", SqlDbType.VarChar, 4000),
new SqlParameter("@t_create_time", SqlDbType.DateTime),
new SqlParameter("@i_delete", SqlDbType.Int, 4)
};
parameters[0].Value = model.pk;
parameters[1].Value = model.v_key;
parameters[2].Value = model.v_value;
parameters[3].Value = model.v_mac_address;
parameters[4].Value = model.t_create_time;
parameters[5].Value = model.i_delete;
int rows = SQLServerHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
private void QuaterBuget_Click(object sender, EventArgs e)
{
try
{
if (excelExportFolder.Text.Trim().Length == 0)
{
throw new Exception("Excel文件导出文件夹不能为空");
}
System.Data.DataTable tb = null;
string sql = " Select FCompany As 公司,FDeptName As 成本中心,FDeptCode As 成本中心编码,FAcctCode AS 科目编码,FAcctName AS 科目,FYear AS 年度,FQuater As 季度,FAmout As 期初额,FAmout As 余额" +
" FROM Buget_Quarter ";
SQLServerHelper runner = new SQLServerHelper();
tb = runner.ExecuteSql(sql);
string excelFileName = excelExportFolder.Text.Trim();
if (excelFileName.Substring(excelFileName.Length - 1, 1) != "\\")
{
excelFileName = excelFileName + "\\成本中心季度预算导入.xlsx";
}
else
{
excelFileName = excelFileName + "成本中心季度预算导入.xlsx";
}
ExportExcel(tb, excelFileName);
}
catch (Exception err)
{
MessageBox.Show(err.Message, "系统提示");
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(HomeAccountingSystem.Model.jt_zffs model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into jt_zffs(");
strSql.Append("v_zffs_no,v_zffs_name,t_create_time,i_delete,i_zffs_lx)");
strSql.Append(" values (");
strSql.Append("@v_zffs_no,@v_zffs_name,@t_create_time,@i_delete,@i_zffs_lx)");
SqlParameter[] parameters =
{
new SqlParameter("@pk", SqlDbType.Int, 4),
new SqlParameter("@v_zffs_no", SqlDbType.VarChar, 4000),
new SqlParameter("@v_zffs_name", SqlDbType.VarChar, 4000),
new SqlParameter("@t_create_time", SqlDbType.DateTime),
new SqlParameter("@i_delete", SqlDbType.Int, 4),
new SqlParameter("@i_zffs_lx", SqlDbType.Int, 4)
};
parameters[0].Value = model.pk;
parameters[1].Value = model.v_zffs_no;
parameters[2].Value = model.v_zffs_name;
parameters[3].Value = model.t_create_time;
parameters[4].Value = model.i_delete;
parameters[5].Value = model.i_zffs_lx;
int rows = SQLServerHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(HomeAccountingSystem.Model.jt_zc_lx model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update jt_zc_lx set ");
strSql.Append("v_zc_no=@v_zc_no,");
strSql.Append("v_zclx_name=@v_zclx_name,");
strSql.Append("t_create_time=@t_create_time,");
strSql.Append("i_delete=@i_delete");
strSql.Append(" where pk=@pk ");
SqlParameter[] parameters =
{
new SqlParameter("@v_zc_no", SqlDbType.VarChar, 4000),
new SqlParameter("@v_zclx_name", SqlDbType.VarChar, 4000),
new SqlParameter("@t_create_time", SqlDbType.DateTime),
new SqlParameter("@i_delete", SqlDbType.Int, 4),
new SqlParameter("@pk", SqlDbType.Int, 4)
};
parameters[0].Value = model.v_zc_no;
parameters[1].Value = model.v_zclx_name;
parameters[2].Value = model.t_create_time;
parameters[3].Value = model.i_delete;
parameters[4].Value = model.pk;
int rows = SQLServerHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public DataTable GetList(string employeeIDs, DateTime beginDate, DateTime endDate, DateTime expirationDate, string type = "99")
{
DataTable result = new DataTable();
string filterString = "", sql = "";
try
{
SQLServerHelper runner = new SQLServerHelper();
//result = runner.ExecuteSql(sql);
filterString = " FSentDate between '" + beginDate.ToString("yyyy-MM-dd") + " 0:0:0.000' and '" + endDate.ToString("yyyy-MM-dd") + " 23:59:59.999'";
filterString = filterString + " and FExpirationDate <= '" + expirationDate.ToString("yyyy-MM-dd") + " 23:59:59.999'";
if (type != "99")
{
filterString = filterString + " and FType In (" + type + ")";
}
sql = "Select * from [Messages] Where FIsPulic=1 and FDeleted=0 and " + filterString;
if (employeeIDs.Trim().Length > 0)
{
sql = sql + " union Select * from [Messages] Where FIsPulic=0 and FDeleted=0 and FID In( Select FMsgID from MsgReceivers Where FReceiverID In('" + employeeIDs.Replace(";", "';'") + "')) and " + filterString;
}
result = runner.ExecuteSql(sql);
}
catch (Exception err)
{
throw err;
}
return(result);
}
public string GetItemList(string xmlString)
{
string sql = "", result = "", ClassID = "";
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode node = doc.SelectSingleNode("GetList/ClassID");
if (node != null)
{
ClassID = node.InnerText;
}
if (ClassID.Trim().Length < 19)
{
sql = @"Select field0001 As FNumber, field0008 AS FName
From v3x.dbo.formmain_2894
Where field0002 ='-4875734478274671070' and field0005='{0}'
Order by field0001 ASC";
}
else
{
sql = @" Select ID AS FNumber, SHOWVALUE AS FName
From v3x.dbo.CTP_ENUM_ITEM
Where REF_ENUMID = '{0}'
Order by SORTNUMBER ASc";
}
sql = string.Format(sql, ClassID);
runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
result = iTR.Lib.Common.DataTableToXml(dt, "GetList", "", "List");
return(result);
}
/// <summary>
/// 清空表
/// </summary>
/// <param name="tableName">要清空的表名</param>
/// <returns></returns>
public bool TruncateTable(string tableName)
{
string sql = " truncate table " + tableName;
SQLServerHelper.ExecuteSql(sql);
return(true);
}
/// <summary>
/// 删除一条代码项表数据
/// </summary>
/// <param name="id">主键</param>
/// <param name="transModel">事务类</param>
/// <returns>是否删除成功</returns>
public bool Delete(string id, TransactionModel transModel = null)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from udtWip_CodeItems ");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.NVarChar, 50)
};
parameters[0].Value = id;
int rows = 0;
if (transModel != null)
{
rows = SQLServerHelper.ExecuteSql(transModel.conn, transModel.trans, strSql.ToString(), parameters);
}
else
{
rows = SQLServerHelper.ExecuteSql(strSql.ToString(), parameters);
}
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
private void RefreshOARecordList()
{
lvOARecord.Items.Clear();
string sql = "Select t2.Name, t3.Subject,t1.start_date,(Case t1.finishedflag when 1 then '结束' Else '进行中' End) As finishedflag ,t1.ID As Form_RecordID,t3.ID As OBJECT_ID,t3.PROCESS_ID" +
" From OAServer.v3x.dbo.{0} t1" +
" Left Join OAServer.v3x.dbo.ORG_MEMBER t2 on t1.start_member_id = t2.ID" +
" Left Join OAServer.v3x.dbo.Col_Summary t3 On t1.ID =t3.Form_RecordID" +
" Where t1.start_date between '{1}' and '{2}'";
sql = string.Format(sql, selectedMTable, Date1.Value.ToString("yyyy-MM-dd 0:0:0.000"), Date2.Value.ToString("yyyy-MM-dd 23:59:59.999"));
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
if (dt.Rows.Count == 0)
{
return;
}
foreach (DataRow row in dt.Rows)
{
var item = new ListViewItem();
item.Text = row["Name"].ToString();
item.SubItems.Add(row["Subject"].ToString());
item.SubItems.Add(row["start_date"].ToString());
item.SubItems.Add(row["finishedflag"].ToString());
item.SubItems.Add(row["Form_RecordID"].ToString() + "|" + row["OBJECT_ID"].ToString() + "|" + row["PROCESS_ID"].ToString());
lvOARecord.Items.Add(item);
}
}
private DataTable 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);
}
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 GetImage(string xmlString)
{
string callType = "GetRegImage";
string result = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<" + callType + ">" +
"<Result>False</Result>" +
"<Description></Description></" + callType + ">";
try
{
string ownerID = "", pageID = "";
string url = System.Configuration.ConfigurationManager.AppSettings["URL"];
url = "http://ydb.tenrypharm.com:6060";
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode(callType + "/PageID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("PageID不能为空");
}
else
{
pageID = 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();
}
string sql = "SELECT (FPath + '\\' + FFileName) As FPath1,(FPath + '\\T_' + FFileName) As FPath2 FROM Attachments Where FPageID='{0}' and FOwnerID='{1}' and FDeleted=0 ";
sql = string.Format(sql, pageID, ownerID);
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
if (dt.Rows.Count > 0)
{
string imageString = "";
foreach (DataRow row in dt.Rows)
{
imageString = imageString + "<Image Original=" + url + "\\" + row["FPath1"].ToString() + ">" + url + "\\" + row["FPath2"].ToString() + "</Image>";
//imageString = imageString + "<Image>" + url + row["FPath2"].ToString() + "</Image>";
}
result = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<" + callType + ">" +
"<Result>True</Result><Rows>" + imageString + "</Rows><Description></Description></" + callType + ">";
}
}
catch (Exception err)
{
throw err;
}
return(result);
}
public string GetRegRelationShip(string xmlString)
{
string result = "", id = "", sql = "";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("GetRegistrationData/ID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("ID不能为空");
}
else
{
id = vNode.InnerText.Trim();
}
sql = " SELECT t1.FApplicationID,t1.FHospitalID,t1.FDate,t1.FName,t1.FTitle,t1.FDepartment,t1.FRelationShipTypeID,t1.FSortIndx, isnull(t2.FName,'') As FHospitalName,Isnull(t3.FName,'') As FRelationShipTypeName " +
" FROM Reg_HospitalRelationShip t1" +
" Left Join t_Items t2 On t1.FHospitalID = t2.FID" +
" Left Join t_Items t3 On t1.FRelationShipTypeID = t3.FID";
sql = sql + " Where FApplicationID='" + id + "'";
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
result = iTR.Lib.Common.DataTableToXml(dt, "GetRegistrationData", "", "List");
}
catch (Exception err)
{
throw err;
}
return(result);
}
/// <summary>
/// 禁启用代码集表数据
/// </summary>
/// <param name="code">代码编码</param>
/// <param name="delFlag">禁用/启用</param>
/// <param name="lastModifier">最后修改人</param>
/// <param name="transModel">事务类</param>
/// <returns>是否禁启用成功</returns>
public bool Enable(string code, string delFlag, string lastModifier, TransactionModel transModel = null)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" UPDATE CodeSetsEntity SET delflag=@delFlag ");
strSql.Append(" ,lastModifier=@lastModifier ");
strSql.Append(" ,lastModifyTime=@lastModifyTime ");
strSql.Append(" WHERE code=@code");
SqlParameter[] parameters =
{
new SqlParameter("@delFlag", SqlDbType.NVarChar, 10),
new SqlParameter("@lastModifier", SqlDbType.NVarChar, 20),
new SqlParameter("@lastModifyTime", SqlDbType.DateTime),
new SqlParameter("@code", SqlDbType.NVarChar, 50)
};
parameters[0].Value = delFlag;
parameters[1].Value = lastModifier;
parameters[2].Value = DateTime.Now;
parameters[3].Value = code;
int rows = 0;
if (transModel != null)
{
rows = SQLServerHelper.ExecuteSql(transModel.conn, transModel.trans, strSql.ToString(), parameters);
}
else
{
rows = SQLServerHelper.ExecuteSql(strSql.ToString(), parameters);
}
return(rows > 0 ? true : false);
}
public string GetTeamMemberList(string xmlString)
{
string leaderID = "";
string result = "<?xml version=\"1.0\" encoding=\"utf-8\"?><GetTeamMemberList>" +
"<Result>False</Result>" +
"<Description></Description><DataRows></DataRows>" +
"</GetTeamMemberList>";
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("GetTeamMembers/LeaderID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("团队领导ID不能为空");
}
else
{
leaderID = vNode.InnerText.Trim();
}
string sql = "Select t1.*,t2.FName As FEmployeeName,t3.FName As FDepartmentName" +
" From t_Workships t1" +
" Left Join t_Items t2 On t1.FEmployeeID= t2.FID" +
" Left Join t_Items t3 On t1.FTeamID= t3.FID";
sql = sql + " Where t1.FTeamLeaderID='" + leaderID + "' Order by t1.FTeamID";
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
doc.LoadXml(result);
XmlNode pNode = doc.SelectSingleNode("GetTeamMemberList/DataRows");
foreach (DataRow row in dt.Rows)
{
XmlNode cNode = doc.CreateElement("DataRow");
pNode.AppendChild(cNode);
vNode = doc.CreateElement("DepartmentName");
vNode.InnerText = row["FDepartmentName"].ToString();
cNode.AppendChild(vNode);
vNode = doc.CreateElement("EmployeeName");
vNode.InnerText = row["FEmployeeName"].ToString();
cNode.AppendChild(vNode);
vNode = doc.CreateElement("TeamID");
vNode.InnerText = row["FTeamID"].ToString();
cNode.AppendChild(vNode);
vNode = doc.CreateElement("EmployeeID");
vNode.InnerText = row["FEmployeeID"].ToString();
cNode.AppendChild(vNode);
}
result = doc.OuterXml;
}
catch (Exception err)
{
throw err;
}
return(result);
}
public string GetLogList(string xmlString)
{
string callType = "GetLogList";
string filter = "", val = "";
string 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("GetLogList/BeginDate");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText.Trim() + " 0:0:0.000";
filter = filter.Length > 0 ? filter = filter + " And FDate>='" + val + "'" : " FDate>='" + val + "'";
}
vNode = doc.SelectSingleNode("GetLogList/EndDate");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText.Trim() + " 23:59:59.999";
filter = filter.Length > 0 ? filter = filter + " And FDate<='" + val + "'" : "FDate<='" + val + "'";
}
vNode = doc.SelectSingleNode("GetLogList/Type");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText.Trim();
filter = filter.Length > 0 ? filter = filter + " And FType='" + val + "'" : "FType='" + val + "'";
}
vNode = doc.SelectSingleNode("GetLogList/Method");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText.Trim();
filter = filter.Length > 0 ? filter = filter + " And FMethod='" + val + "'" : "FMethod='" + val + "'";
}
string sql = "Select FDate,FType,FCaller,FMethod,FLog From AppLogs ";
if (filter.Length > 0)
{
sql = sql + " Where " + filter;
}
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
if (dt.Rows.Count > 0)
{
result = Common.DataTableToXml(dt, "GetLogList", "", "List");
}
}
catch (Exception err)
{
throw err;
}
return(result);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(HomeAccountingSystem.Model.jt_yh_zl model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update jt_yh_zl set ");
strSql.Append("v_yh_no=@v_yh_no,");
strSql.Append("v_yh_name=@v_yh_name,");
strSql.Append("v_yh_pwd=@v_yh_pwd,");
strSql.Append("i_yh_type=@i_yh_type,");
strSql.Append("v_phone=@v_phone,");
strSql.Append("t_birthday_gregorian=@t_birthday_gregorian,");
strSql.Append("t_birthday_lunar=@t_birthday_lunar,");
strSql.Append("t_create_time=@t_create_time,");
strSql.Append("i_delete=@i_delete,");
strSql.Append("v_photo=@v_photo,");
strSql.Append("v_age=@v_age,");
strSql.Append("v_photo_path=@v_photo_path");
strSql.Append(" where pk=@pk ");
SqlParameter[] parameters =
{
new SqlParameter("@v_yh_no", SqlDbType.VarChar, 256),
new SqlParameter("@v_yh_name", SqlDbType.VarChar, 256),
new SqlParameter("@v_yh_pwd", SqlDbType.VarChar, 256),
new SqlParameter("@i_yh_type", SqlDbType.Int, 4),
new SqlParameter("@v_phone", SqlDbType.VarChar, 256),
new SqlParameter("@t_birthday_gregorian", SqlDbType.DateTime),
new SqlParameter("@t_birthday_lunar", SqlDbType.VarChar, 4000),
new SqlParameter("@t_create_time", SqlDbType.DateTime),
new SqlParameter("@i_delete", SqlDbType.Int, 4),
new SqlParameter("@v_photo", SqlDbType.Image),
new SqlParameter("@v_age", SqlDbType.VarChar, 256),
new SqlParameter("@v_photo_path", SqlDbType.VarChar, 256),
new SqlParameter("@pk", SqlDbType.Int, 4)
};
parameters[0].Value = model.v_yh_no;
parameters[1].Value = model.v_yh_name;
parameters[2].Value = model.v_yh_pwd;
parameters[3].Value = model.i_yh_type;
parameters[4].Value = model.v_phone;
parameters[5].Value = model.t_birthday_gregorian;
parameters[6].Value = model.t_birthday_lunar;
parameters[7].Value = model.t_create_time;
parameters[8].Value = model.i_delete;
parameters[9].Value = model.v_photo;
parameters[10].Value = model.v_age;
parameters[11].Value = model.v_photo_path;
parameters[12].Value = model.pk;
int rows = SQLServerHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public static DataTable GetRecordSet(string sql)
{
DataTable dt = null;
SQLServerHelper runner = new SQLServerHelper();
dt = runner.ExecuteSql(sql);
return(dt);
}
public string GetDetailXml(string classID)
{
string result = "<?xml version=\"1.0\" encoding=\"utf-8\"?><GetClassDetail>" +
"<Result>False</Result>" +
"<Description></Description>" +
"<DataRows></DataRows>" +
"</GetClassDetail>";
try
{
string sql = "Select * from t_ItemClass Where FID ='" + classID + "' and FIsDeleted=0";
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
if (dt.Rows.Count > 0)
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(result);
doc.SelectSingleNode("GetClassDetail/Result").InnerText = "True";
XmlNode cNode = doc.SelectSingleNode("GetClassDetail");
foreach (DataRow row in dt.Rows)
{
XmlNode vNode = doc.CreateElement("FClassID");
vNode.InnerText = row["FID"].ToString();
cNode.AppendChild(vNode);
vNode = doc.CreateElement("FName");
vNode.InnerText = row["FName"].ToString();
cNode.AppendChild(vNode);
vNode = doc.CreateElement("FTableName");
vNode.InnerText = row["FTableName"].ToString();
cNode.AppendChild(vNode);
vNode = doc.CreateElement("FNumber");
vNode.InnerText = row["FNumber"].ToString();
cNode.AppendChild(vNode);
vNode = doc.CreateElement("FDescription");
vNode.InnerText = row["FDescription"].ToString();
cNode.AppendChild(vNode);
}
result = doc.OuterXml;
}
else
{
result = "<?xml version=\"1.0\" encoding=\"utf-8\"?><GetClassDetail>" +
"<Result>False</Result>" +
"<Description></Description>" +
"</GetClassDetail>";
}
}
catch (Exception err)
{
throw err;
}
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 Boolean CheckUpateData(string xmlString)
{
Boolean result = false;
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("UpdateItem/FName");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("名称不能为空");
}
//vNode = doc.SelectSingleNode("UpdateItem/ItemID");
//if (vNode == null || vNode.InnerText == "-1" || vNode.InnerText.Trim().Length == 0)//新增检查
//{
vNode = doc.SelectSingleNode("UpdateItem/FClassID");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("资料类型ID不能为空");
}
vNode = doc.SelectSingleNode("UpdateItem/FNumber");
if (vNode == null || vNode.InnerText.Trim().Length == 0)
{
throw new Exception("代码不能为空");
}
else
{
XmlNode idNode = doc.SelectSingleNode("UpdateItem/ID");
if (idNode == null)
{
throw new Exception("没有<ID>节点");
}
else
{
if (idNode.InnerText.Trim().Length == 0 || idNode.InnerText.Trim().Equals("-1")) //新增
{
string sql = "Select FID,FNumber from t_items Where FIsDeleted=0 And FClassID='" + doc.SelectSingleNode("UpdateItem/FClassID").InnerText + "' And FNumber ='" + doc.SelectSingleNode("UpdateItem/FNumber").InnerText + "'";
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
if (dt.Rows.Count > 0)
{
throw new Exception("代码：" + dt.Rows[0]["FNumber"].ToString() + "已存在");
}
}
}
}
//}
result = true;
return(result);
}
private DataTable Query(string xmlString, string nodeString)
{
string sql = "", filter = "", val = "";
DataTable result = null;
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode(nodeString + "/ID");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText.Trim();
filter = filter.Length > 0 ? filter = filter + " And t1.FID='" + val + "'" : "t1.FID='" + val + "'";
}
vNode = doc.SelectSingleNode(nodeString + "/BeginDate");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText.Trim() + " 0:0:0.000";
filter = filter.Length > 0 ? filter = filter + " And t1.FBeginDate>='" + val + "'" : "t1.FBeginDate>='" + val + "'";
}
vNode = doc.SelectSingleNode(nodeString + "/EndDate");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText.Trim() + " 23:59:59.999";
filter = filter.Length > 0 ? filter = filter + " And t1.FEndDate<='" + val + "'" : "t1.FEndDate <= '" + val + "'";
}
vNode = doc.SelectSingleNode(nodeString + "/EmployeeID");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText.Trim();
filter = filter.Length > 0 ? filter = filter + " And t1.FEmployeeID In ('" + val.Replace("|", "','") + "')" : "t1.FEmployeeID In ('" + val.Replace("|", "','") + "')";
}
vNode = doc.SelectSingleNode(nodeString + "/ActivityTypeID");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
val = vNode.InnerText.Trim();
filter = filter.Length > 0 ? filter = filter + " And t1.FActivityTypeID= '" + val + "'" : "t1.FActivityTypeID= '" + val + "'";
}
sql = "SELECT t1.*,Isnull(t2.FName,'') AS FEmployeeName,Isnull(t3.FName,'') AS FActivityTypeName " +
" FROM MarketingActivity t1 " +
" Left Join t_Items t2 On t1.FEmployeeID=t2.FID" +
" Left Join t_Items t3 On t1.FActivityTypeID=t3.FID";
if (filter.Length > 0)
{
sql = sql + " Where " + filter;
}
SQLServerHelper runner = new SQLServerHelper();
result = runner.ExecuteSql(sql);
}
catch (Exception err)
{
throw err;
}
return(result);
}