/// <summary>
/// 获取合同及关联的设备信息
/// </summary>
/// <param name="contractIDs">合同ID</param>
/// <returns>合同及关联的设备信息</returns>
public List <ContractEqptInfo> GetContractEqpts(List <int> contractIDs)
{
List <ContractEqptInfo> infos = new List <ContractEqptInfo>();
sqlStr = "SELECT e.ID ,e.Name ,e.DepartmentID ,e.SerialCode , j.ContractID FROM jctContractEqpt j " +
" INNER JOIN tblEquipment AS e ON e.ID = j.EquipmentID" +
" WHERE j.ContractID in (" + (string.IsNullOrEmpty(SQLUtil.ConvertToInStr(contractIDs)) ? "null" : SQLUtil.ConvertToInStr(contractIDs)) + ") ";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
using (DataTable dt = GetDataTable(command))
{
ContractEqptInfo info = null;
foreach (DataRow dr in dt.Rows)
{
info = new ContractEqptInfo();
info.ContractID = SQLUtil.ConvertInt(dr["ContractID"]);
info.Equipment.ID = SQLUtil.ConvertInt(dr["ID"]);
info.Equipment.Name = SQLUtil.TrimNull(dr["Name"]);
info.Equipment.SerialCode = SQLUtil.TrimNull(dr["SerialCode"]);
info.Equipment.Department.ID = SQLUtil.ConvertInt(dr["DepartmentID"]);
info.Equipment.Department.Name = Manager.LookupManager.GetDepartmentDesc(info.Equipment.Department.ID);
infos.Add(info);
}
}
}
return(infos);
}
/// <summary>
/// 根据用户角色ID获取用户信息
/// </summary>
/// <param name="roleIds">用户角色ID</param>
/// <returns>用户信息</returns>
public List <UserInfo> GetActiveUsers(List <int> roleIds)
{
List <UserInfo> infos = new List <UserInfo>();
sqlStr = "SELECT DISTINCT u.* FROM tblUser AS u ";
sqlStr += " WHERE u.IsActive = 1 ";
if (roleIds != null && roleIds.Count > 0)
{
sqlStr += " AND u.RoleID IN ( " + SQLUtil.ConvertToInStr(roleIds) + " )";
}
sqlStr += " ORDER BY u.Name, u.ID ";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
infos.Add(new UserInfo(dr));
}
}
}
return(infos);
}
/// <summary>
/// 根据派工单状态获取派工单数量
/// </summary>
/// <param name="statusList">派工单状态</param>
/// <param name="userId">用户ID</param>
/// <returns>派工单数量</returns>
public int GetDispatchCount4App(List <int> statusList, int userId = 0)
{
sqlStr = "SELECT COUNT(d.ID) FROM tblDispatch AS d " +
" WHERE d.StatusID IN (" + SQLUtil.ConvertToInStr(statusList) + ")";
if (userId > 0)
{
sqlStr += " AND d.EngineerID=" + userId;
}
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
return(GetCount(command));
}
}
/// <summary>
/// 根据请求类型获取请求数量
/// </summary>
/// <param name="statusIdList">请求类型</param>
/// <returns>请求数量</returns>
public int GetRequestCount4App(List <int> statusIdList)
{
sqlStr = "SELECT COUNT(r.ID) FROM tblRequest AS r " +
" WHERE 1=1 ";
if (statusIdList[0] == RequestInfo.Statuses.Unfinished)
{
sqlStr += " AND r.StatusID != -1 AND r.StatusID != 99 ";
}
else
{
sqlStr += " AND r.StatusID IN (" + SQLUtil.ConvertToInStr(statusIdList) + ")";
}
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
return(GetCount(command));
}
}
/// <summary>
/// 获取派工单列表
/// </summary>
/// <param name="userID">用户ID</param>
/// <param name="userRoleID">用户角色ID</param>
/// <param name="statusList">状态ID</param>
/// <param name="urgency">派工单紧急程度</param>
/// <param name="type">派工类型</param>
/// <param name="filterField">搜索条件</param>
/// <param name="filterText">搜索框填写内容</param>
/// <param name="sortField">排序字段</param>
/// <param name="sortDirection">排序方式</param>
/// <param name="curRowNum">当前页数第一个数据的位置</param>
/// <param name="pageSize">一页几条数据</param>
/// <returns>派工单列表</returns>
public List <DispatchInfo> QueryDispatches(int userID, int userRoleID, List <int> statusList, int urgency, int type, string filterField, string filterText, string sortField, bool sortDirection, int curRowNum = 0, int pageSize = 0)
{
List <DispatchInfo> dispatches = new List <DispatchInfo>();
sqlStr = "SELECT DISTINCT d.*, CONVERT(VARCHAR(10),d.CreateDate,112),j.ID as DispatchJournalID,dr.ID as DispatchReportID , j.StatusID AS DispatchJournalStatusID,dr.StatusID AS DispatchReportStatusID, " + DispatchReportInfo.GetOverDueSQL() + string.Format(", CASE WHEN d.StatusID = {0} THEN -1 ELSE d.StatusID END AS newStatusID ", DispatchInfo.Statuses.Responded) +
" FROM tblDispatch d " +
" LEFT JOIN tblDispatchJournal j ON d.ID = j.DispatchID " +
" LEFT JOIN tblDispatchReport dr ON d.ID = dr.DispatchID " +
" LEFT JOIN tblRequest as r on r.ID = d.RequestID " +
" LEFT JOIN jctRequestEqpt jc ON jc.RequestID=r.ID " +
" LEFT JOIN tblEquipment e ON e.ID=jc.EquipmentID " +
" WHERE 1=1 ";
if (statusList != null && statusList.Count > 1)
{
sqlStr += " AND d.StatusID IN (" + SQLUtil.ConvertToInStr(statusList) + ")";
}
else if (statusList != null && statusList.Count == 1 && statusList[0] != 0)
{
sqlStr += " AND d.StatusID IN (" + SQLUtil.ConvertToInStr(statusList) + ")";
}
else
{
sqlStr += " AND d.StatusID <> " + DispatchInfo.Statuses.Cancelled;
}
if (urgency != 0)
{
sqlStr += " AND d.UrgencyID=" + urgency;
}
if (type != 0)
{
sqlStr += " AND d.RequestType=" + type;
}
if (userRoleID == BusinessObjects.Domain.UserRole.Admin)
{
sqlStr += " AND d.EngineerID=" + userID;
}
if (!string.IsNullOrEmpty(filterText))
{
sqlStr += GetFieldFilterClause(filterField);
}
if (sortField.Equals("init"))
{
if (userRoleID == BusinessObjects.Domain.UserRole.Admin)
{
sqlStr += string.Format(" ORDER BY newStatusID, d.RequestType, CONVERT(VARCHAR(10),d.CreateDate,112) DESC , d.ID ", DispatchInfo.Statuses.Responded, DispatchReportInfo.GetOverDueSQL());
}
else
{
sqlStr += string.Format(" ORDER BY {0} DESC, d.RequestType, CONVERT(VARCHAR(10),d.CreateDate,112) DESC, d.StatusID, d.ID ", DispatchReportInfo.GetOverDueSQL());
}
}
else
{
sqlStr += GenerateSortClause(sortDirection, sortField, "d.ID");
}
sqlStr = AppendLimitClause(sqlStr, curRowNum, pageSize);
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
if (!String.IsNullOrEmpty(filterText))
{
AddFieldFilterParam(command, filterField, filterText);
}
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
dispatches.Add(new DispatchInfo(dr));
}
}
}
return(dispatches);
}
/// <summary>
/// 获取请求列表信息
/// </summary>
/// <param name="statusList">请求状态</param>
/// <param name="requestType">请求类型</param>
/// <param name="isRecall">是否召回</param>
/// <param name="department">科室编号</param>
/// <param name="urgency">请求紧急程度</param>
/// <param name="overDue">是否超期</param>
/// <param name="source">请求来源</param>
/// <param name="filterField">搜索字段</param>
/// <param name="filterText">搜索框填写内容</param>
/// <param name="sortField">排序字段</param>
/// <param name="sortDirection">排序方式</param>
/// <param name="startDate">开始日期</param>
/// <param name="endDate">截至日期</param>
/// <param name="curRowNum">当前页数第一个数据的位置</param>
/// <param name="pageSize">每页展示数据条数</param>
/// <param name="requestUserID">请求用户ID</param>
/// <returns>请求列表信息</returns>
public List <RequestInfo> QueryRequestsList(List <int> statusList, int requestType, bool isRecall, int department, int urgency, bool overDue, int source, string filterField, string filterText, string sortField, bool sortDirection, string startDate, string endDate, int curRowNum = 0, int pageSize = 0, int requestUserID = 0)
{
List <RequestInfo> infos = new List <RequestInfo>();
sqlStr = " SELECT DISTINCT r.*,CONVERT(VARCHAR(10),r.RequestDate,112), " + RequestInfo.Statuses.GetCurOverDueField() +
" FROM tblRequest r " +
" LEFT JOIN jctRequestEqpt re ON re.RequestID=r.ID" +
" LEFT JOIN tblEquipment e ON e.ID=re.EquipmentID" +
" LEFT JOIN tblDispatch d ON d.RequestID=r.ID" +
" WHERE 1=1 ";
if (statusList != null && statusList.Count > 1)
{
sqlStr += " AND r.StatusID IN (" + SQLUtil.ConvertToInStr(statusList) + ")";
}
else if (statusList != null && statusList.Count == 1 && statusList[0] == RequestInfo.Statuses.Unfinished)
{
sqlStr += " AND r.StatusID <> " + RequestInfo.Statuses.Cancelled + " AND r.StatusID <> " + RequestInfo.Statuses.Close;
}
else if (statusList != null && statusList.Count == 1 && statusList[0] != 0)
{
sqlStr += " AND r.StatusID = " + statusList[0];
}
else
{
sqlStr += " AND r.StatusID <> " + RequestInfo.Statuses.Cancelled;
}
if (department >= 0)
{
sqlStr += " AND e.DepartmentID = " + department;
}
if (urgency != 0)
{
sqlStr += " AND r.PriorityID = " + urgency;
}
if (overDue)
{
sqlStr += " AND " + RequestInfo.Statuses.GetCurOverDueSQL();
}
if (source != 0)
{
sqlStr += " AND r.Source = " + source;
}
if (!string.IsNullOrEmpty(startDate))
{
sqlStr += " AND r.RequestDate >= @StartDate ";
}
if (!string.IsNullOrEmpty(endDate))
{
sqlStr += " AND r.RequestDate < @EndDate ";
}
if (requestType > 0)
{
sqlStr += " AND r.RequestType = " + requestType;
}
if (isRecall)
{
sqlStr += " AND r.IsRecall = 1 ";
}
if (requestUserID != 0)
{
sqlStr += " AND r.RequestUserID = " + requestUserID;
}
if (!string.IsNullOrEmpty(filterText))
{
if (filterField.Equals("e.ID") || filterField.Equals("e.Name"))
{
sqlStr += " AND EXISTS (SELECT j.EquipmentID FROM jctRequestEqpt j INNER JOIN tblEquipment as e ON j.EquipmentID = e.ID WHERE j.RequestID = r.ID";
sqlStr += GetFieldFilterClause(filterField);
sqlStr += ")";
}
else
{
sqlStr += GetFieldFilterClause(filterField);
}
}
if (sortField.Equals("init"))
{
sqlStr += string.Format(" ORDER BY {0} DESC, r.RequestType ASC, CONVERT(VARCHAR(10),r.RequestDate,112) DESC,r.StatusID, r.ID ", RequestInfo.Statuses.GetCurOverDueField());
}
else
{
sqlStr += GenerateSortClause(sortDirection, sortField, "r.ID");
}
sqlStr = AppendLimitClause(sqlStr, curRowNum, pageSize);
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
if (!String.IsNullOrEmpty(filterText))
{
AddFieldFilterParam(command, filterField, filterText);
}
if (!string.IsNullOrEmpty(startDate))
{
command.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = SQLUtil.ConvertDateTime(startDate);
}
if (!string.IsNullOrEmpty(endDate))
{
command.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = SQLUtil.ConvertDateTime(endDate).AddDays(1);
}
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
infos.Add(new RequestInfo(dr));
}
}
}
return(infos);
}
/// <summary>
/// 根据设备id和请求类型获取已存在该类型计划服务请求的设备信息
/// </summary>
/// <param name="equipmentIds">设备ID</param>
/// <param name="requestType">请求类型</param>
/// <returns>设备信息</returns>
public List <EquipmentInfo> CheckRequestExsist(List <int> equipmentIds, int requestType)
{
List <EquipmentInfo> infos = new List <EquipmentInfo>();
sqlStr = "SELECT DISTINCT e.* FROM tblRequest AS r " +
" LEFT JOIN jctRequestEqpt AS j ON j.RequestID = r.ID " +
" LEFT JOIN tblEquipment AS e ON e.ID = j.EquipmentID " +
" WHERE ((r.Source = @Source AND r.RequestType = @RequestType) AND (r.CloseDate IS NULL OR r.RequestDate = CONVERT(varchar(100), GETDATE(), 23)) ) AND e.ID IN (" + SQLUtil.ConvertToInStr(equipmentIds) + ")";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@Source", SqlDbType.Int).Value = RequestInfo.Sources.SysRequest;
command.Parameters.Add("@RequestType", SqlDbType.Int).Value = requestType;
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
infos.Add(new EquipmentInfo(dr));
}
}
}
return(infos);
}