public IEnumerable <AirCom> GetAirComList()
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * FROM jinri.dbo.tblAircom WITH(NOLOCK) ORDER BY SORT ");
return(DapperHelper <AirCom> .GetPageList(ConnectionStr.JinRiDb, sql.ToString(), null));
}
/// <summary>
/// 获取一个系统角色权限列表
/// </summary>
/// <param name="roleId"></param>
/// <param name="isAppId"></param>
/// <param name="pageItem"></param>
/// <returns></returns>
public IEnumerable <SysRoleRight> GetRoleRightList(int roleId, bool isAppId = false, int userId = -1, PageItem pageItem = null)
{
StringBuilder sql = new StringBuilder();
if (isAppId)
{
sql.Append("SELECT ID,RoleId,UserId,(SELECT TOP 1 MenuId FROM dbo.SysMenu WHERE MenuName = '" + AppSettingsHelper.ConfigCenterMenuName + "') AS MenuId,AppId FROM SysRoleRight WHERE 1=1 ");
}
else
{
sql.Append("SELECT ID,RoleId,UserId,MenuId,AppId FROM SysRoleRight WHERE 1=1 ");
}
if (roleId >= 0)
{
sql.AppendFormat("and RoleId={0} ", roleId);
}
if (isAppId)
{
sql.Append("and MenuId = (SELECT TOP 1 MenuId FROM SysMenu WHERE MenuName = '" + AppSettingsHelper.ConfigCenterMenuName + "') ");
sql.Append("and AppId > 0 and UserId > 0 ");
}
else
{
sql.Append("and AppId = 0 and UserId = 0 ");
}
if (userId > 0)
{
sql.AppendFormat("and UserId = {0} ", userId);
}
sql.AppendFormat("ORDER BY ID ");
return(DapperHelper <SysRoleRight> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}
/// <summary>
/// 获取一个产品线列表
/// </summary>
/// <param name="systemId">负数表示不限制</param>
/// <param name="status">负数表示不限制</param>
/// <returns></returns>
public IEnumerable <SysProductEntity> GetProductList(string productName, PageItem pageItem = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ProductId,ProductName,ProductEName,Remark FROM SysProduct WHERE 1=1 ");
if (!string.IsNullOrEmpty(productName))
{
sql.AppendFormat("AND (ProductName LIKE '%{0}%' OR ProductEName LIKE '%{0}%') ", productName);
}
sql.AppendFormat(" ORDER BY ProductId ");
return(DapperHelper <SysProductEntity> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}
/// <summary>
/// 获取依赖、被依赖的APP
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public IEnumerable <SysAppDependentEntity> GetAppDependentList(int id)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT Id,AppId,DependentAppId FROM SysAppDependent WHERE 1=1 ");
if (id >= 0)
{
sql.AppendFormat("AND (AppId = {0} OR DependentAppId = {0} ) ", id);
}
sql.AppendFormat(" ORDER BY AppId ");
return(DapperHelper <SysAppDependentEntity> .GetPageList(ConnectionStr.FxDb, sql.ToString(), null));
}
/// <summary>
/// 获取应用类型列表
/// </summary>
/// <param name="typeName">应用类型名称</param>
/// <returns></returns>
public IEnumerable <SysAppTypeEntity> GetAppTypeList(string typeName, PageItem pageItem = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT AppTypeId,TypeName FROM SysAppType WHERE 1=1 ");
if (!string.IsNullOrEmpty(typeName))
{
sql.AppendFormat("AND TypeName LIKE '%{0}%' ", typeName);
}
sql.AppendFormat(" ORDER BY AppTypeId ");
return(DapperHelper <SysAppTypeEntity> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}
public IEnumerable <SysMenu> GetUserMenuList(int userId)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT C.MenuId,C.MenuName,C.MenuUrl,C.ImageUrl,C.ModuleId,C.Sort,C.Status,C.Remark ");
sql.AppendLine("FROM SysUser A ");
sql.AppendLine("INNER JOIN SysRoleRight B ON A.RoleId=B.RoleId ");
sql.AppendLine("INNER JOIN SysMenu C ON B.MenuId=C.MenuId ");
sql.AppendFormat("WHERE C.Status=0 AND A.UserId={0} ", userId);
sql.AppendLine("ORDER BY C.Sort ");
return(DapperHelper <SysMenu> .GetPageList(ConnectionStr.FxDb, sql.ToString(), null));
}
/// <summary>
/// 获取应用列表
/// </summary>
/// <param name="systemId">负数表示不限制</param>
/// <param name="status">负数表示不限制</param>
/// <returns></returns>
public IEnumerable <SysApplicationEntity> GetSysApplicationList(string appId, PageItem pageItem = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT AppId FROM SysApplication WHERE 1=1 ");
if (!string.IsNullOrEmpty(appId))
{
sql.AppendFormat("AND (AppId LIKE '%{0}%') ", appId);
}
sql.AppendFormat(" ORDER BY AppId ");
return(DapperHelper <SysApplicationEntity> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}
/// <summary>
/// 获取子系统列表
/// </summary>
/// <param name="productId">负数表示不限制</param>
/// <param name="systemName">子系统名称</param>
/// <returns></returns>
public IEnumerable <SysSubEntity> GetSubSystemList(int productId, string systemName, PageItem pageItem = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT SubSystemId,ProductId,SystemName,SystemEName,Remark FROM SysSub WHERE 1=1 ");
if (productId > 0)
{
sql.AppendFormat("AND ProductId={0} ", productId);
}
if (!string.IsNullOrEmpty(systemName))
{
sql.AppendFormat("AND (SystemName LIKE '%{0}%' OR SystemEName LIKE '%{0}%' )", systemName);
}
sql.AppendFormat(" ORDER BY SubSystemId ");
return(DapperHelper <SysSubEntity> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}
public IEnumerable <EtermConfig> GetEtermConfigList(int state, string serverUrl = "", string officeNo = "", PageItem pageItem = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * FROM EtermConfig WITH(NOLOCK) WHERE 1=1 ");
if (state >= 0)
{
sql.AppendFormat("AND ConfigState={0} ", state);
}
if (!string.IsNullOrEmpty(serverUrl))
{
sql.AppendFormat("AND serverUrl like '%{0}%' ", serverUrl);
}
if (!string.IsNullOrEmpty(officeNo))
{
sql.AppendFormat("AND officeNo ='{0}' ", officeNo);
}
return(DapperHelper <EtermConfig> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}
public IEnumerable <SysApplicationEntity> GetCanBindApplication(DependentSearchRequest request, PageItem pageItem)
{
StringBuilder command = new StringBuilder();
command.AppendLine("SELECT A.AppId,A.AppName,A.AppEName,A.AppTypeId,A.Owner,A.Status,");
command.AppendLine("CASE WHEN B.DependentAppId IS NOT NULL THEN 1 ELSE 0 END AS IsDependent");
command.AppendLine("FROM SysApplication A WITH(NOLOCK) ");
command.AppendFormat("LEFT JOIN SysAppDependent B WITH(NOLOCK) ON A.APPID=B.DependentAppId AND B.APPID={0} ", request.AppId);
command.AppendFormat("WHERE A.APPID<>{0} ", request.AppId);
if (!string.IsNullOrEmpty(request.AppName))
{
command.AppendFormat("AND (A.AppName LIKE '%{0}%' OR A.AppEName LIKE '%{0}%') ", request.AppName);
}
if (request.AppTypeId >= 0)
{
command.AppendFormat("AND A.AppTypeId={0} ", request.AppTypeId);
}
if (request.Bind >= 0)
{
if (request.Bind == 0)
{
command.AppendFormat("AND B.DependentAppId IS NULL ", request.Bind);
}
else
{
command.AppendFormat("AND B.DependentAppId IS NOT NULL ", request.Bind);
}
}
if (request.Status >= 0)
{
command.AppendFormat("AND A.Status={0} ", request.Status);
}
if (request.SubSystemId >= 0)
{
command.AppendFormat("AND A.SubSystemId={0} ", request.SubSystemId);
}
command.AppendLine("ORDER BY AppId");
using (var conn = new SqlConnection(ConnectionStr.FxDb))
{
conn.Open();
return(DapperHelper <SysApplicationEntity> .GetPageList(ConnectionStr.FxDb, command.ToString(), pageItem));
}
}
/// <summary>
/// 获取一个系统角色列表
/// </summary>
/// <param name="systemId">负数表示不限制</param>
/// <param name="status">负数表示不限制</param>
/// <returns></returns>
public IEnumerable <SysRole> GetRoleList(string roleName, int systemId, int status, PageItem pageItem = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT RoleId,RoleName,SystemId,Status,Remark FROM SysRole WHERE 1=1 ");
if (!string.IsNullOrEmpty(roleName))
{
sql.AppendFormat("AND RoleName LIKE '%{0}%' ", roleName);
}
if (systemId >= 0)
{
sql.AppendFormat("AND SystemId={0} ", systemId);
}
if (status >= 0)
{
sql.AppendFormat("AND Status={0} ", status);
}
sql.AppendFormat(" ORDER BY RoleId ");
return(DapperHelper <SysRole> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}
/// <summary>
/// 获取一个系统菜单列表
/// </summary>
/// <param name="menuName">负数表示不限制</param>
/// <param name="moduleId">负数表示不限制</param>
/// <param name="status">负数表示不限制</param>
/// <param name="pageItem">分页信息</param>
/// <returns></returns>
public IEnumerable <SysMenu> GetMenuList(string menuName, int moduleId, int status, PageItem pageItem = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT MenuId,MenuName,MenuUrl,ImageUrl,ModuleId,Sort,Status,Remark FROM SysMenu WHERE 1=1 ");
if (!string.IsNullOrEmpty(menuName))
{
sql.AppendFormat("and MenuName LIKE '%{0}%' ", menuName);
}
if (moduleId >= 0)
{
sql.AppendFormat("and ModuleId={0} ", moduleId);
}
if (status >= 0)
{
sql.AppendFormat("and Status={0} ", status);
}
sql.AppendFormat("ORDER BY SORT ");
return(DapperHelper <SysMenu> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}
/// <summary>
/// 获取一个系统用户列表
/// </summary>
/// <param name="systemId">负数表示不限制</param>
/// <param name="status">负数表示不限制</param>
/// <returns></returns>
public IEnumerable <SysUser> GetUserList(string userName, int roleId, int status, PageItem pageItem = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT UserId,UserName,Password,RoleId,Status,RealName,Tel,Email,RegDate FROM SysUser WHERE 1=1 ");
if (!string.IsNullOrEmpty(userName))
{
sql.AppendFormat("AND UserName LIKE '%{0}%' ", userName);
}
if (roleId >= 0)
{
sql.AppendFormat("AND RoleId={0} ", roleId);
}
if (status >= 0)
{
sql.AppendFormat("AND Status={0} ", status);
}
sql.AppendFormat(" ORDER BY UserId ");
return(DapperHelper <SysUser> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}
/// <summary>
/// 获取一个接口注册列表
/// </summary>
/// <param name="systemId">负数表示不限制</param>
/// <param name="status">负数表示不限制</param>
/// <returns></returns>
public IEnumerable <SysApiEntity> GetSysApiList(int appId, int status, string aipName, PageItem pageItem)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT SysApiId,AppId,ApiName,ApiType,ApiDescription,ApiOwner,ApiAddress,ApiStatus,Remark,AddTime FROM SysApi WHERE 1=1 ");
if (appId >= 0)
{
sql.AppendFormat("AND AppId={0} ", appId);
}
if (status >= 0)
{
sql.AppendFormat("AND ApiStatus={0} ", status);
}
if (!string.IsNullOrEmpty(aipName))
{
sql.AppendFormat("AND (ApiName LIKE '%{0}%') ", aipName);
}
sql.AppendFormat(" ORDER BY SysApiId ");
return(DapperHelper <SysApiEntity> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}
public IEnumerable <JobHttpScheduler> GetJobList(JobHttpScheduler searchCondition, PageItem pageItem = null)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT JobHttpSchedulerID,JobName,GroupName,RequestURL,RequestType, ");
sql.AppendLine("JobDescription,StartTime,TriggerType,RepeatCount,RepeatInterval,CronExpression,JobStatus,AddTime ");
sql.AppendLine("FROM JobHttpScheduler WITH(NOLOCK) WHERE 1=1 ");
if (searchCondition != null)
{
if (searchCondition.JobHttpSchedulerID > 0)
{
sql.AppendLine(" AND JobHttpSchedulerID = " + searchCondition.JobHttpSchedulerID);
}
if (!string.IsNullOrWhiteSpace(searchCondition.JobName))
{
sql.AppendLine(" AND JobName LIKE '%" + searchCondition.JobName + "%'");
}
if (searchCondition.JobStatus > -1)
{
sql.AppendLine(" AND JobStatus = " + searchCondition.JobStatus);
}
}
return(DapperHelper <JobHttpScheduler> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}
/// <summary>
/// 获得满足条件的Eterm脚本记录集。如果 PageItem 为null，则将获得所有满足条件的Eterm脚本记录集；如果 PageItem 不为null，则将获得某页的满足条件的Eterm脚本记录集。
/// </summary>
/// <param name="searchCondition">查询条件</param>
/// <param name="pageItem">分页信息</param>
/// <returns></returns>
public IEnumerable <EtermScript> GetEtermScriptList(EtermScript searchCondition, PageItem pageItem = null)
{
string sql =
@"SELECT id AS EtermScriptID, method AS MethodName, script AS ScriptContent, remark AS Remark, updatetime AS UpdateTime
FROM dbo.EtermScripts WITH(NOLOCK)
WHERE 1 = 1";
StringBuilder whereBuilder = new StringBuilder();
if (searchCondition != null)
{
if (!string.IsNullOrWhiteSpace(searchCondition.MethodName))
{
whereBuilder.AppendLine(" AND method LIKE '" + searchCondition.MethodName + "%'");
}
}
if (whereBuilder.Length > 0)
{
sql += whereBuilder.ToString();
}
sql += " ORDER BY updatetime DESC";
return(DapperHelper <EtermScript> .GetPageList(ConnectionStr.FxDb, sql, pageItem));
}
/// <summary>
/// 获取Job运行日志列表
/// </summary>
public IEnumerable <JobExecuteLog> GetJobExecuteLogList(int jobId, DateTime startTime, DateTime endTime, PageItem pageItem)
{
string sql = string.Format("SELECT ID,JobId,ExecuteTime,Success FROM JobExecuteLog WITH(NOLOCK) WHERE JobId={0} AND ExecuteTime BETWEEN '{1}' AND '{2}' ORDER BY ID DESC ", jobId, startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"));
return(DapperHelper <JobExecuteLog> .GetPageList(ConnectionStr.FxDb, sql, pageItem));
}
/// <summary>
/// 获取应用列表
/// </summary>
/// <param name="productIdList">1表示国内机票；2表示国际机票；3表示酒店；4表示公共服务；5表示框架；6表示手机</param>
/// <returns></returns>
public IEnumerable <SysApplicationEntity> GetSysApplicationList(int appId, int subSystemId, string appName, int appTypeId, int status, PageItem pageItem = null, List <int> productIdList = null, string owner = "")
{
StringBuilder sql = new StringBuilder();
if (productIdList != null && productIdList.Count > 0)
{
string sqlStr = @"SELECT CASE
WHEN (SubSystemId >= 1000 AND SubSystemId <= 1099) THEN 1
WHEN (SubSystemId >= 1100 AND SubSystemId <= 1199) THEN 2
WHEN (SubSystemId >= 1200 AND SubSystemId <= 1299) THEN 3
WHEN (SubSystemId >= 1300 AND SubSystemId <= 1399) THEN 4
WHEN (SubSystemId >= 1400 AND SubSystemId <= 1499) THEN 5
WHEN (SubSystemId >= 1500 AND SubSystemId <= 1599) THEN 6
END AS ModuleId,AppId,SubSystemId,AppName,(SELECT TOP 1 MenuId FROM dbo.SysMenu WHERE MenuName = '" + AppSettingsHelper.ConfigCenterMenuName + "') AS MenuId,CAST([AppId] AS NVARCHAR(20)) + '（' + [AppName] + '）' AS MenuName,AppEName,AppTypeId,Owner,Status,Remark,AddTime FROM SysApplication WHERE 1=1 ";
sql.Append(sqlStr);
}
else
{
sql.Append("SELECT AppId,SubSystemId,AppName,CAST([AppId] AS NVARCHAR(20)) + '（' + [AppName] + '）' AS MenuName,AppEName,AppTypeId,Owner,Status,Remark,AddTime FROM SysApplication WHERE 1=1 ");
}
if (appId >= 0)
{
sql.AppendFormat("AND AppId={0} ", appId);
}
if (subSystemId >= 0)
{
sql.AppendFormat("AND SubSystemId={0} ", subSystemId);
}
if (productIdList != null && productIdList.Count > 0)
{
sql.Append("AND ( ");
for (int i = 0; i < productIdList.Count; ++i)
{
int productId = productIdList[i];
if (i == 0)
{
switch (productId)
{
case 1: //1表示国内机票
sql.Append("(SubSystemId >= 1000 AND SubSystemId <= 1099) ");
break;
case 2: //2表示国际机票
sql.Append("(SubSystemId >= 1100 AND SubSystemId <= 1199) ");
break;
case 3: //传3表示酒店
sql.Append("(SubSystemId >= 1200 AND SubSystemId <= 1299) ");
break;
case 4: //传4表示公共服务
sql.Append("(SubSystemId >= 1300 AND SubSystemId <= 1399) ");
break;
case 5: //传5表示框架
sql.Append("(SubSystemId >= 1400 AND SubSystemId <= 1499) ");
break;
case 6: //传6表示手机
sql.Append("(SubSystemId >= 1500 AND SubSystemId <= 1599) ");
break;
}
continue;
}
switch (productId)
{
case 1: //1表示国内机票
sql.Append("OR (SubSystemId >= 1000 AND SubSystemId <= 1099) ");
break;
case 2: //2表示国际机票
sql.Append("OR (SubSystemId >= 1100 AND SubSystemId <= 1199) ");
break;
case 3: //传3表示酒店
sql.Append("OR (SubSystemId >= 1200 AND SubSystemId <= 1299) ");
break;
case 4: //传4表示公共服务
sql.Append("OR (SubSystemId >= 1300 AND SubSystemId <= 1399) ");
break;
case 5: //传5表示框架
sql.Append("OR (SubSystemId >= 1400 AND SubSystemId <= 1499) ");
break;
case 6: //传6表示手机
sql.Append("OR (SubSystemId >= 1500 AND SubSystemId <= 1599) ");
break;
}
}
sql.Append(") ");
}
if (!string.IsNullOrWhiteSpace(owner))
{
sql.AppendFormat("AND Owner = '{0}' ", owner);
}
if (appTypeId >= 0)
{
sql.AppendFormat("AND AppTypeId={0} ", appTypeId);
}
if (status >= 0)
{
sql.AppendFormat("AND Status={0} ", status);
}
if (!string.IsNullOrEmpty(appName))
{
sql.AppendFormat("AND (AppName LIKE '%{0}%' OR AppEName LIKE '%{0}%' )", appName);
}
if (productIdList != null && productIdList.Count > 0)
{
sql.AppendFormat(" ORDER BY ModuleId, AppId ");
}
else
{
sql.AppendFormat(" ORDER BY AppId ");
}
return(DapperHelper <SysApplicationEntity> .GetPageList(ConnectionStr.FxDb, sql.ToString(), pageItem));
}