/// <summary>
/// 根据角色ID获取主体权限
/// </summary>
/// <param name="RID"></param>
/// <returns></returns>
public List <MainAuthority> GetAllMainAuthoritiesByRID(string RID)
{
string sql = @"select a.主体权限id AID ,
a.角色id RoleID ,
a.主体id MID ,
a.主体名称 MainName ,
a.类型 Type ,
a.权限类型 AuthorityType ,
a.关联资源ID RID ,
a.关联资源明细ID DID ,
a.最后修改人 LastModify ,
a.最后修改时间 LastDate
from 主体权限 a
where a.角色ID = :RID";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
OracleParameter[] oracleParameters =
{
new OracleParameter(":RID", OracleDbType.Varchar2, RID, ParameterDirection.Input)
};
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, oracleParameters);
List <MainAuthority> mainAuthorities = ModelConvert.DataTableToIList <MainAuthority>(dataTable).ToList();
return(mainAuthorities);
}
/// <summary>
/// 获取 要素目录/资源目录/资源分类/统计指标定义/计算要素条件 联合查询
/// </summary>
/// <param name="RID"></param>
/// <returns></returns>
public List <Element> GetAllElementByResourceID(string RID)
{
string sql = @"select a.要素id EID,
a.资源id RID,
a.要素名称 Name,
a.要素类型 Type,
a.单位 Unit,
a.备注 Note,
a.最后修改人 LastModify,
a.最后修改时间 LastDate,
a.默认值 Defaulta,
a.选项类型 OptionType,
a.序号 Numbera,
a.长度 Length,
a.精度 Precision,
a.是否必填 IFRequired，
a.是否展示主目录 IFZSZML,
a.名称是否展示 IFZSMC,
a.要素分类 ElementClassify,
b.资源名称 ResourceName,
c.分类名称 ClassifyName,
count(d.指标定义id) DYZBGS,
e.表达式文本 Text FROM 要素目录 a,资源目录 b,资源分类 c,统计指标定义 d,计算要素条件 e
where a.资源id= '{0}' and a.资源id=b.资源id(+)
and a.分类id=c.资源分类id(+)
and a.要素id=d.关联id(+)
and a.要素id=e.要素id(+)
group by a.要素id,
a.资源id,
a.要素名称,
a.要素类型,
a.单位,
a.备注,
a.最后修改人,
a.最后修改时间,
a.默认值,
a.选项类型,
a.序号,
a.长度,
a.精度,
a.是否必填，
a.是否展示主目录,
a.名称是否展示,
a.要素分类,
b.资源名称,
c.分类名称,
e.表达式文本
order by a.序号";
sql = string.Format(sql, RID);
//数据库连接
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, null);
List <Element> elements = ModelConvert.DataTableToIList <Element>(dataTable).ToList();
return(elements);
}
/// <summary>
/// 获取v_要素资源值域
/// </summary>
/// <returns></returns>
public List <DR> GetAllDR()
{
string sql = @"select 资源名称 Name,资源ID RID,类型 Type from V_要素资源值域 ";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, null);
List <DR> dr = ModelConvert.DataTableToIList <DR>(dataTable).ToList();
return(dr);
}
/// <summary>
/// 获取角色信息
/// </summary>
/// <returns></returns>
public List <MainAuthority> GetAllRoleInfo()
{
string sql = @"Select distinct a.角色ID RoleID,a.角色名称 RoleName
From 角色信息 a ,角色用户信息 b
Where a.角色ID = b.角色ID";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, null);
List <MainAuthority> mainAuthority = ModelConvert.DataTableToIList <MainAuthority>(dataTable).ToList();
return(mainAuthority);
}
/// <summary>
/// 获取全部角色信息
/// </summary>
/// <returns></returns>
public List <RoleInformation> GetAllRoleInformation()
{
string sql = @"select 角色ID ID,
角色名称 Name,
备注 Note,
最后修改人 LastModify,
最后修改时间 LastDate
from 角色信息";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, null);
List <RoleInformation> roleInformation = ModelConvert.DataTableToIList <RoleInformation>(dataTable).ToList();
return(roleInformation);
}
/// <summary>
/// 获取设计页面信息
/// </summary>
/// <returns></returns>
public List <Designpage> GetAllDesignpage()
{
string sql = @"select 编码 Code,
名称 Name,
描述 Description,
最后修改人 LastModify,
最后修改时间 LastDate
from 设计页面";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, null);
List <Designpage> designpages = ModelConvert.DataTableToIList <Designpage>(dataTable).ToList();
return(designpages);
}
/// <summary>
/// 根据资源ID获取资源类型
/// </summary>
/// <param name="RID"></param>
/// <returns></returns>
public List <Resource> GetTypeByRID(string RID)
{
string sql = @"select 类型 Type from 资源目录 where 资源ID = :RID";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
OracleParameter[] oracleParameters =
{
new OracleParameter(":RID", OracleDbType.Varchar2, RID, ParameterDirection.Input)
};
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, oracleParameters);
List <Resource> resourcesType = ModelConvert.DataTableToIList <Resource>(dataTable).ToList();
return(resourcesType);
}
/// <summary>
/// 根据角色ID获取主体权限ID
/// </summary>
/// <param name="RoleID">角色ID</param>
/// <returns></returns>
public List <MainAuthority> GetAllAIDByRoleID(string RoleID)
{
string sql = @"select 主体权限ID AID from 主体权限 where 角色ID = :RoleID";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
OracleParameter[] oracleParameters =
{
new OracleParameter(":RoleID", OracleDbType.Varchar2, RoleID, ParameterDirection.Input),
};
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, oracleParameters);
List <MainAuthority> mainAuthority = ModelConvert.DataTableToIList <MainAuthority>(dataTable).ToList();
return(mainAuthority);
}
/// <summary>
/// 获取通用编码目录
/// </summary>
/// <returns></returns>
public List <GCodedir> GetAllGCodedir()
{
string sql = @"select 目录ID ID,
目录名称 Name,
备注 Note,
最后修改人 LastModify,
最后修改时间 LastDate,
是否树形 TreeForm
from 通用编码目录";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, null);
List <GCodedir> gCodedir = ModelConvert.DataTableToIList <GCodedir>(dataTable).ToList();
return(gCodedir);
}
/// <summary>
/// 获取资源目录
/// </summary>
/// <returns></returns>
public List <Resource> GetAll()
{
string sql = @"select 资源ID ID,
上级资源ID PID,
资源名称 Name,
类型 Type,
备注 Note,
最后修改人 LastModify,
最后修改时间 LastDate,
显示名称 ShowName,
是否树形 TreeForm,
关系资源ID RelationID from 资源目录 order by 资源名称";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, null);
List <Resource> resources = ModelConvert.DataTableToIList <Resource>(dataTable).ToList();
return(resources);
}
/// <summary>
/// 根据资源分类ID获取要素分类
/// </summary>
/// <param name="ClassifyID">资源分类ID</param>
/// <returns></returns>
public List <ElementClassify> GetElementClassifyByID(string CID)
{
string sql = @"select 资源分类ID ID,
资源ID RID,
分类名称 Name,
备注 Note,
最后修改人 LastModify,
最后修改时间 LastDate from 资源分类 where 资源分类ID = :ID";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
OracleParameter[] oracleParameters =
{
new OracleParameter(":ID", OracleDbType.Varchar2, CID, ParameterDirection.Input)
};
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, oracleParameters);
List <ElementClassify> elementClassifies = ModelConvert.DataTableToIList <ElementClassify>(dataTable).ToList();
return(elementClassifies);
}
/// <summary>
/// 根据选项值域ID获取文本值域选项
/// </summary>
/// <param name="ID">选项值域ID</param>
/// <returns></returns>
public List <ElementRange> GetAllElementRangeByID(string ID)
{
string sql = @"select 选项值域ID ID,
选项名称 Name,
备注 Note,
最后修改人 LastModify,
最后修改时间 LastDate,
要素ID EID from 文本选项值域 where 选项值域ID = :选项值域ID";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
OracleParameter[] oracleParameter =
{
new OracleParameter(":选项值域ID", OracleDbType.Varchar2, ID, ParameterDirection.Input)
};
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, CommandType.Text, oracleParameter);
List <ElementRange> list = ModelConvert.DataTableToIList <ElementRange>(dataTable).ToList();
return(list);
}
/// <summary>
/// 根据资源ID获取资源明细数据
/// </summary>
/// <param name="RID"></param>
/// <returns></returns>
public List <Detail> GetAllDetailByResourceID(string RID)
{
string sql = @"select a.资源明细id DID ,
a.明细名称 DetailName ,
a.资源id RID ,
a.明细要素信息 DetailInfo ,
a.备注 Note ,
a.最后修改人 LastModify ,
a.最后修改时间 LastDate ,
a.明细关系id DetailRelationID ,
a.上级资源id PID ,
a.上级资源明细id PDID ,
a.上级明细名称 PDetailName ,
a.事务时间 TransactionDate
from(select a.资源明细id,
a.明细名称,
a.资源id,
a.明细要素信息,
a.备注,
a.最后修改人,
a.最后修改时间,
b.明细关系id,
b.上级资源id,
b.上级资源明细id,
c.明细名称 上级明细名称,
a.事务时间
from 资源明细 a, 资源明细关系 b, 资源明细 c
where a.资源明细id = b.资源明细id(+)
and b.上级资源明细id = c.资源明细id(+)
and a.资源id = '{0}') a";
sql = string.Format(sql, RID);
//数据库连接
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, null);
List <Detail> Details = ModelConvert.DataTableToIList <Detail>(dataTable).ToList();
return(Details);
}
/// <summary>
/// 根据资源ID获取资源目录
/// </summary>
/// <param name="RID">资源ID</param>
/// <returns></returns>
public List <Resource> GetResourceByID(string RID)
{
string sql = @"select 上级资源ID PID,
资源名称 Name,
类型 Type,
备注 Note,
最后修改人 LastModify,
最后修改时间 LastDate,
显示名称 ShowName,
是否树形 TreeForm,
关系资源ID RelationID from 资源目录 where 资源ID=:RID order by 资源名称";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
OracleParameter[] oracleParameters =
{
new OracleParameter(":RID", OracleDbType.Varchar2, RID, ParameterDirection.Input)
};
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, oracleParameters);
List <Resource> resources = ModelConvert.DataTableToIList <Resource>(dataTable).ToList();
return(resources);
}
/// <summary>
/// 根据资源明细ID获取主体权限数据
/// </summary>
/// <param name="RID"></param>
/// <returns></returns>
public List <MainAuthority> GetAllMainAuthorityByDetailID(string DID)
{
string sql = @"select a.主体权限id AID ,
a.角色id RoleID ,
a.角色名称 RoleName ,
a.主体id MID ,
a.主体名称 MainName ,
a.类型 Type ,
a.权限类型 AuthorityType ,
a.关联资源ID RID ,
a.关联资源明细ID DID ,
a.最后修改人 LastModify ,
a.最后修改时间 LastDate
from ( select a.主体权限id,
a.角色id,
b.角色名称,
a.主体id,
a.主体名称,
a.类型,
a.权限类型,
a.关联资源ID,
a.关联资源明细ID,
a.最后修改人,
a.最后修改时间
from 主体权限 a , 角色信息 b
where a.关联资源明细ID = '{0}'
and a.角色id = b.角色id(+) ) a";
sql = string.Format(sql, DID);
//数据库连接
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, null);
List <MainAuthority> mainAuthority = ModelConvert.DataTableToIList <MainAuthority>(dataTable).ToList();
return(mainAuthority);
}
public object FunctionTest(string type)
{
string sql = "p_test_core";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
OracleParameter[] oracleParameters =
{
new OracleParameter("Type_in", OracleDbType.Varchar2, type, ParameterDirection.Input),
new OracleParameter("Numbera_out", OracleDbType.RefCursor, "", ParameterDirection.Output)
};
/*object numbera = oracleDataAccess.ExecuteProcdure(sql, oracleParameters);
* return numbera;*/
Dictionary <string, object> dic = oracleDataAccess.ExecuteProcdure(sql, oracleParameters);
if (dic.ContainsKey("Numbera_out"))
{
return(ModelConvert.DataTableToIList <Test>(dic["Numbera_out"] as DataTable).ToList());
}
else
{
return(null);
}
}
/// <summary>
/// 根据指标定义ID获取统计指标定义
/// </summary>
/// <param name="指标定义ID"></param>
/// <returns></returns>
public List <CountNorm> GetAllCountNormByID(string ID)
{
string sql = @"select 指标定义ID ID,
指标名称 Name,
计算类型 CalculateType,
运算符 Operator,
运算值 Ovalue,
关联类型 AssociationType,
关联ID GLID,
备注 Note,
最后修改人 LastModify,
最后修改时间 LastDate,
资源ID RID from 统计指标定义 where 指标定义ID = :指标定义ID";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
OracleParameter[] oracleParameters =
{
new OracleParameter(":指标定义ID", OracleDbType.Varchar2, ID, ParameterDirection.Input)
};
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, oracleParameters);
List <CountNorm> countNorms = ModelConvert.DataTableToIList <CountNorm>(dataTable).ToList();
return(countNorms);
}
/// <summary>
/// 根据用户ID获取人员权限
/// </summary>
/// <param name="UID">用户ID</param>
/// <returns></returns>
public List <PersonnelAuthority> GetAllPersonnelAuthorityByUID(string UserID)
{
string sql = @"select a.人员权限id PAID,
a.主体权限id MAID,
a.主体id MID,
a.主体名称 MName,
a.类型 Type,
a.用户id UserID,
a.权限类型 AuthorityType,
a.最后修改人 LastModify,
a.最后修改时间 LastDate
from 人员权限 a
where a.用户id = :UserID";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
OracleParameter[] oracleParameters =
{
new OracleParameter(":UserID", OracleDbType.Varchar2, UserID, ParameterDirection.Input)
};
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, oracleParameters);
List <PersonnelAuthority> personnelAuthorities = ModelConvert.DataTableToIList <PersonnelAuthority>(dataTable).ToList();
return(personnelAuthorities);
}
/// <summary>
/// 根据角色ID获取角色用户信息
/// </summary>
/// <param name="RID">角色ID</param>
/// <returns></returns>
public List <RoleUesr> GetAllRoleUserByRID(string RID)
{
string sql = @"select 角色用户ID RUID,
角色ID RID,
用户ID UserID,
最后修改人 LastModify,
最后修改时间 LastDate,
用户名 UserName,
编码 Code,
组织机构 Organization,
邮箱地址 EmailAddress
from 角色用户信息
where 角色ID=:RID";
OracleDataAccess oracleDataAccess = new OracleDataAccess(SiteConfig.OracleConn);
OracleParameter[] oracleParameters =
{
new OracleParameter(":RID", OracleDbType.Varchar2, RID, ParameterDirection.Input)
};
DataTable dataTable = oracleDataAccess.ExecuteDataTable(sql, System.Data.CommandType.Text, oracleParameters);
List <RoleUesr> roleUesrs = ModelConvert.DataTableToIList <RoleUesr>(dataTable).ToList();
return(roleUesrs);
}