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