/// <summary>
/// 通过员工的ID来获得所在部门的ID字符串,用','分割 ( Added By Liujun at 11.1 )
/// </summary>
/// <param name="EmployeeID">部门员工ID</param>
/// <returns>所在部门ID</returns>
public string GetDepartmentIDByEmployeeID(string EmployeeID)
{
System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
string SelectSql = "SELECT BI_Department.IDKey FROM BI_DepartmentEmployee,BI_Department WHERE BI_DepartmentEmployee.DepartmentID=BI_Department.IDKey AND BI_DepartmentEmployee.IDKey='" + EmployeeID + "'";
using (System.Data.SqlClient.SqlDataReader dr = _da.GetDataReader(SelectSql))
{
while (dr.Read())
{
strBuilder.Append("'" + Convert.ToString(dr["IDKey"]) + "',");
}
}
string strDepartmentIDKey = string.Empty;
if (strBuilder.Length > 0)
{
if (strBuilder[strBuilder.Length - 1] == ',')
{
// 将最后一个","去掉
strDepartmentIDKey = strBuilder.Remove(strBuilder.Length - 1, 1).ToString();
}
}
return(strDepartmentIDKey);
}
/// <summary>
/// 按条件选取审核人表中审核人对应的审核级别列表
/// </summary>
/// <param name="personID"></param>
/// <returns>审核级别列表</returns>
public IList GetApproveLevel(string personID)
{
IList ApproveInfoList = new ArrayList();
string SelectSql = @"SELECT ApproveMember.ApproveLevel ,
ApproveMember.PutInID
FROM ApproveMember
INNER JOIN BI_DepartmentEmployee ON ApproveMember.ApproveID = BI_DepartmentEmployee.IDKey
INNER JOIN PutIn ON PutIn.IDKey = ApproveMember.PutInID
WHERE BI_DepartmentEmployee.EmployeeID = '" + personID + "' AND PutIn.State <> 1";
using (SqlDataReader dr = _da.GetDataReader(SelectSql))
{
while (dr.Read())
{
ApproveInfo approveInfo = new ApproveInfo();
approveInfo.ApproveLevel = Convert.ToInt32(dr["ApproveLevel"]);
approveInfo.PutInID = Convert.ToString(dr["PutInID"]);
approveInfo.PersonID = personID;
ApproveInfoList.Add(approveInfo);
}
}
return(ApproveInfoList);
}
/// <summary>
/// 选择采办部的所有部门ID
/// </summary>
/// <returns></returns>
public string GetDepartmentID()
{
string SelectSql = "SELECT IDKey FROM BI_Department WHERE IsMisDepartment = 1";
string strDepartmentIDs = string.Empty;
System.Text.StringBuilder sbuilder = new System.Text.StringBuilder();
using (System.Data.SqlClient.SqlDataReader dr = _da.GetDataReader(SelectSql))
{
while (dr.Read())
{
sbuilder.Append(" '");
sbuilder.Append(dr["IDKey"].ToString());
sbuilder.Append("',");
}
}
if (sbuilder.Length > 0)
{
if (sbuilder[sbuilder.Length - 1] == ',')
{
// 将最后一个","去掉
strDepartmentIDs = sbuilder.Remove(sbuilder.Length - 1, 1).ToString();
}
}
return(strDepartmentIDs);
}
/// <summary>
/// 获得最大的OrderID,然后新增的时候加1
/// </summary>
/// <returns></returns>
public int GetMaxOrderID()
{
int orderID = 0;
string SelectSql = "SELECT dbo.f_GetMaxOrderID() AS ORDERID ";
using (System.Data.SqlClient.SqlDataReader dr = _da.GetDataReader(SelectSql))
{
while (dr.Read())
{
orderID = Convert.ToInt32(dr["ORDERID"]);
}
}
return(orderID);
}
/// <summary>
/// 通过部门来获得所有子科室
/// </summary>
/// <param name="DepID"></param>
/// <returns></returns>
public string GetDepsByDepID(string DepID)
{
System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
string SelectSql = "SELECT a.IDKey FROM BI_Department a,f_CDepartment_IDKey('" + DepID + "') b WHERE a.IDKey=b.[ID]";
using (System.Data.SqlClient.SqlDataReader dr = _da.GetDataReader(SelectSql))
{
while (dr.Read())
{
strBuilder.Append("'" + Convert.ToString(dr["IDKey"]) + "',");
}
}
string strDepartmentIDKey = string.Empty;
if (strBuilder.Length > 0)
{
if (strBuilder[strBuilder.Length - 1] == ',')
{
strDepartmentIDKey = strBuilder.Remove(strBuilder.Length - 1, 1).ToString();
}
}
return(strDepartmentIDKey);
}
public string GetProjectName()
{
string SelectSQL = "SELECT ProjectName FROM Projects WHERE OneSelf = 1 ";
string strProjectName = string.Empty;
using (System.Data.SqlClient.SqlDataReader dr = _da.GetDataReader(SelectSQL))
{
while (dr.Read())
{
strProjectName = Convert.ToString(dr["ProjectName"]);
}
}
return(strProjectName);
}
/// <summary>
/// 通过采办策略编号来获得服务申请编号,合同模式编号,项目名称
/// </summary>
/// <param name="tenderID">采办策略编号</param>
/// <returns>包含服务申请编号,合同模式编号,项目名称的Hashtable</returns>
public Hashtable GetDetialByTenderID(string tenderID)
{
//string SelectSql = "SELECT SRIDKey , ContractMode , ProjectName FROM TCStrategy WHERE TenderID = '"+tenderID+"'";
//Modified by QSQ 12.15 修改显示SRName
string SelectSql = @"SELECT ProjectName FROM TCStrategy
WHERE TenderID = '" + tenderID + "'";
Hashtable hashtable = new Hashtable();
using (System.Data.SqlClient.SqlDataReader dr = _da.GetDataReader(SelectSql))
{
while (dr.Read())
{
hashtable.Add("ProjectName", Convert.ToString(dr["ProjectName"]));
}
}
return(hashtable);
}
/// <summary>
/// 根据提交ID ，联合Approved和PutIn表选取Approved表中（状态为通过的）在审的SR对应的当前审核级别
/// 查不到记录，返回1；
/// </summary>
/// <param name="objType"></param>
/// <param name="objID"></param>
/// <returns></returns>
public int GetCurrentSRApproveLevel(string objType, string objID)
{
int iApproveLevel = 1;
string SelectSql = @"SELECT MAX( Approved.CurrApproveLevel ) AS Level FROM Approved
INNER JOIN PutIn
ON Approved.PutInID = PutIn.IDKey
WHERE PutIn.ObjectiveType = '" + objType + "' AND PutIn.ObjectiveID = '" + objID + "' AND PutIn.State = 0 ";
using (System.Data.SqlClient.SqlDataReader dr = _da.GetDataReader(SelectSql))
{
while (dr.Read())
{
if (dr["Level"] != DBNull.Value)
{
iApproveLevel = Convert.ToInt32(dr["Level"]);
}
}
}
return(iApproveLevel);
}