/// <summary>
/// ��ӽ�ɫ��Ȩ��
/// </summary>
/// <param name="roleName">��ɫ����</param>
/// <param name="privilegeId">Ȩ��ID</param>
/// <param name="projectIDs">��ĿID����</param>
/// <returns></returns>
public static int AddPrivilege(string roleName, int privilegeId, string projectIDs)
{
string sql = "INSERT INTO PLM.ROLEINPRIVILEGE_TAB (ROLENAME, PRIVILEGE_ID, PROJECT_ID) VALUES (:rolename, :privilegeid, :projectids)";
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
db.AddInParameter(cmd, "privilegeid", DbType.Int32, privilegeId);
db.AddInParameter(cmd, "projectids", DbType.String, projectIDs);
return db.ExecuteNonQuery(cmd);
}
/// <summary>
/// �ڵ��Ƿ��и�Ȩ������
/// </summary>
/// <returns></returns>
public static bool ExistPrivilege(int privilegeid,int nodeid)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = "SELECT * FROM PLM.PRIVILEGE_NODE_TAB WHERE PRIVILEGE_ID=:privilegeid AND NODE_ID=:nodeid";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "privilegeid", DbType.Int32, privilegeid);
db.AddInParameter(cmd, "nodeid", DbType.Int32, nodeid);
object ret = db.ExecuteScalar(cmd);
if (ret == null || ret == DBNull.Value) return false;
return true;
}
/// <summary>
/// ��ӽ�ɫ�̳�
/// </summary>
/// <param name="roleName"></param>
/// <param name="inheritRoleName"></param>
/// <returns></returns>
public static void AddInherit(string roleName, string[] inheritRoleName)
{
string sql = "INSERT INTO PLM.ROLEINHERIT_TAB (ROLENAME, INHERITNAME) VALUES (:rolename, :inheritname)";
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
foreach (string inheritName in inheritRoleName)
{
if (inheritName == string.Empty) continue;
if (cmd.Parameters.Contains("inheritname")) cmd.Parameters.RemoveAt("inheritname");
db.AddInParameter(cmd, "inheritname", DbType.String, inheritName);
db.ExecuteNonQuery(cmd);
}
}
/// <summary>
/// ����Ȩ��ID���Ȩ����Ϣ
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Privilege Find(int id)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM PLM.PRIVILEGE_TAB WHERE PRIVILEGE_ID=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.Int32, id);
return Populate(db.ExecuteReader(cmd));
}
/// <summary>
/// ����Ȩ�ޱ�ʶ�����Ȩ����Ϣ
/// </summary>
/// <param name="flag"></param>
/// <returns></returns>
public static Privilege Find(string flag)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM PLM.PRIVILEGE_TAB WHERE PRIVILEGE_FLAG=:flag";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "flag", DbType.String, flag);
return Populate(db.ExecuteReader(cmd));
}
public static Unit Find(string id)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT UNIT_CODE FROM IFSAPP.ISO_UNIT WHERE UNIT_CODE=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return Populate(db.ExecuteReader(cmd));
}
/// <summary>
/// ����Ȩ�ޱ�ʶ���ж��Ƿ���ڴ�Ȩ��
/// </summary>
/// <param name="flag"></param>
/// <returns></returns>
public static bool Exist(string flag)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT PRIVILEGE_FLAG FROM PLM.PRIVILEGE_TAB WHERE PRIVILEGE_FLAG=:flag";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "flag", DbType.String, flag);
object ret = db.ExecuteScalar(cmd);
return (ret == null || ret == DBNull.Value) ? false : true;
}
public static SubProject Find(string id)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT * FROM IFSAPP.SUB_PROJECT WHERE project_id=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return Populate(db.ExecuteReader(cmd));
}
/// <summary>
/// ����ERPID��ȡ��Ӧ��ECDMSID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static string FindECDMSID(string id)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT ECDMSID FROM plm.PROJECT_RELATION_TAB WHERE ERPid=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return Convert.ToString(db.ExecuteScalar(cmd));
}
public static string FindDesc(string id)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT DESCRIPTION FROM IFSAPP.YRS_REQUISITION_REASON_TAB WHERE REASON_CODE=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return Convert.ToString(db.ExecuteScalar(cmd));
}
/// <summary>
/// �жϽ�ɫ�Ƿ����
/// </summary>
/// <param name="roleName"></param>
/// <returns></returns>
public static bool Available(string roleName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT STATE FROM PLM.ROLE_TAB WHERE ROLENAME=:rolename";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
object state = db.ExecuteScalar(cmd);
if (state == null || state == DBNull.Value) return false;
return state.ToString().ToUpper() == RoleState.NORMAL.ToString();
}
public static ReasonCode Find(string id)
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
//Database db = DatabaseFactory.CreateDatabase();
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = "SELECT * FROM IFSAPP.YRS_REQUISITION_REASON_TAB WHERE REASON_CODE=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return Populate(db.ExecuteReader(cmd));
}
/// <summary>
/// ��øĽڵ����е�Ȩ��id
/// </summary>
/// <param name="nodeid"></param>
/// <returns></returns>
public static List<int> GetPrivilegeIds(int nodeid)
{
List<int> privilegeids=new List<int>();
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//Database db = DatabaseFactory.CreateDatabase("oidsConnection");
string sql = "SELECT PRIVILEGE_ID FROM PRIVILEGE_NODE_TAB WHERE NODE_ID=:nodeid ORDER BY PRIVILEGE_ID";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "nodeid", DbType.Int32, nodeid);
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
{
privilegeids.Add(Convert.ToInt32(dr[0]));
}
dr.Close();
}
return privilegeids;
}
/// <summary>
/// ���ݽ�ɫ���ƻ�ô˽�ɫȨ�ޱ�ʶ
/// </summary>
/// <param name="roleName"></param>
/// <returns></returns>
public static string FindFlagsByRoleName(string roleName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT PRIVILEGE_FLAGS FROM PLM.ROLE_TAB WHERE ROLENAME=:rolename";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
object flags = db.ExecuteScalar(cmd);
return (flags == null || flags == DBNull.Value) ? string.Empty : flags.ToString();
}
/// <summary>
/// ����PROJECTNAME��ȡ��Ӧ��ERP����ĿID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static string FindERPID(string id)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT ERPID FROM plm.project_relation_view WHERE projectname_db=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return Convert.ToString(db.ExecuteScalar(cmd));
}
/// <summary>
/// ���ݽ�ɫ�������Ȩ��ID:Project Id����
/// </summary>
/// <param name="roleName"></param>
/// <returns></returns>
public static List<string> FindPrivilege(string roleName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT PRIVILEGE_ID,PROJECT_ID FROM PLM.ROLEINPRIVILEGE_TAB WHERE ROLENAME=:rolename";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
List<string> pidList = new List<string>();
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
pidList.Add(string.Format("{0}:{1}", dr[0], dr[1]));
dr.Close();
}
return pidList;
}
public static string FindSiteName(string id)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT CONTRACT_REF FROM IFSAPP.site_tab WHERE CONTRACT=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return Convert.ToString(db.ExecuteScalar(cmd));
}
public static string FindName(string id)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT description FROM IFSAPP.PROJECT WHERE project_id=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return Convert.ToString (db.ExecuteScalar(cmd));
}
/// <summary>
/// ���ݽ�ɫ��ͼֽ��Ż�ȡ�Ƿ�������ʱ�ʶ
/// </summary>
/// <param name="roleName"></param>
/// <param name="drawingId"></param>
/// <returns></returns>
public static string DrawingAllowedFlag(string roleName, int drawingId)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT ALLOW_FLAG FROM PLM.ROLEINDRAWING_TAB WHERE ROLENAME=:rolename AND DRAWING_ID=:drawingid";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
db.AddInParameter(cmd, "drawingid", DbType.Int32, drawingId);
object allowFlag = db.ExecuteScalar(cmd);
if (allowFlag == DBNull.Value || allowFlag == null) return string.Empty;
return allowFlag.ToString();
}
/// <summary>
/// ���ݽ�ɫ�������̳н�ɫ
/// </summary>
/// <param name="roleName"></param>
/// <returns></returns>
public static List<string> FindInheritRole(string roleName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT INHERITNAME FROM PLM.ROLEINHERIT_TAB WHERE ROLENAME=:rolename";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
List<string> inheritRoleList = new List<string>();
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
inheritRoleList.Add(dr[0].ToString());
dr.Close();
}
return inheritRoleList;
}
/// <summary>
/// ������������Ŀ�µ�����Ŀ�б�
/// </summary>
/// <returns></returns>
public static DataSet FindSub2ProjectsDs(string id, string subId)
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
// Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = " SELECT * FROM IFSAPP.SUB_PROJECT T WHERE T.PROJECT_ID = :id AND T.PARENT_SUB_PROJECT_ID =:subId ORDER BY t.sub_project_id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
db.AddInParameter(cmd, "subId", DbType.String, subId);
return db.ExecuteDataSet(cmd);
}
/// <summary>
/// ���Ľ�ɫ��״̬
/// </summary>
/// <returns></returns>
public static int UpdateState(string roleName, RoleState us)
{
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "UPDATE PLM.ROLE_TAB SET STATE=:state WHERE LOWER(ROLENAME)=:rolename";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "state", DbType.String, us.ToString());
db.AddInParameter(cmd, "rolename", DbType.String, roleName.ToLower());
return db.ExecuteNonQuery(cmd);
}
/// <summary>
/// ������������Ŀ�б�
/// </summary>
/// <returns></returns>
public static List<SubProject> FindAll(string id)
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT * FROM IFSAPP.SUB_PROJECT T WHERE T.PROJECT_ID =:id AND T.PARENT_SUB_PROJECT_ID IS NULL ORDER BY t.sub_project_id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return EntityBase<SubProject>.DReaderToEntityList(db.ExecuteReader(cmd));
}
/// <summary>
/// ���ݽ�ɫ���ơ�Ȩ�ޱ�ʶ������ĿID�ж��Ƿ���и�Ȩ��
/// </summary>
/// <param name="roleName"></param>
/// <param name="privlegeId"></param>
/// <param name="projectId"></param>
/// <returns></returns>
public static bool HavingPrivilege(string roleName, int privlegeId, int projectId)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT PROJECT_ID FROM PLM.ROLEINPRIVILEGE_TAB WHERE ROLENAME=:rolename AND PRIVILEGE_ID=:privilegeid";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
db.AddInParameter(cmd, "privilegeid", DbType.Int32, privlegeId);
object projectIDS = db.ExecuteScalar(cmd);
if (projectIDS != null)
{
if (projectIDS.ToString().ToLower().Contains("all")) return true;
string[] pidArray = projectIDS.ToString().Split(',');
if (DreamStu.Common.Util.IsArrayContainStr(pidArray, projectId.ToString())) return true;
}
//Inherit Role Privilege Check
//bool hasPrivilege = false;
foreach (string inheritRole in FindInheritRole(roleName))
{
if (HavingPrivilege(inheritRole, privlegeId, projectId)) return true;
}
return false;
}
/// <summary>
/// ���ݽ�ɫ���Ƽ�Ȩ�ޱ�ʶID�ж��Ƿ���и�Ȩ��
/// </summary>
/// <param name="privlegeId"></param>
/// <param name="roleName"></param>
/// <returns></returns>
public static bool HavingPrivilege(string roleName, int privlegeId)
{
if (!Available(roleName)) return false;
//Self Privlege Check
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT PRIVILEGE_ID FROM PLM.ROLEINPRIVILEGE_TAB WHERE ROLENAME=:rolename AND PRIVILEGE_ID=:privilegeid";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
db.AddInParameter(cmd, "privilegeid", DbType.Int32, privlegeId);
object ret = db.ExecuteScalar(cmd);
if (ret != null && ret != DBNull.Value) return true;
//Inherit Role Privilege Check
//bool hasPrivilege = false;
foreach (string inheritRole in FindInheritRole(roleName))
{
if (HavingPrivilege(inheritRole, privlegeId)) return true;
}
return false;
}
/// <summary>
/// ��ý�ɫ״̬
/// </summary>
/// <param name="roleName"></param>
/// <returns></returns>
public static RoleState GetState(string roleName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT STATE FROM PLM.ROLE_TAB WHERE LOWER(ROLENAME)=:rolename";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName.ToLower());
object s = db.ExecuteScalar(cmd);
if (s == null || s == DBNull.Value) return RoleState.LOCKED;
if (string.IsNullOrEmpty(s.ToString())) return RoleState.LOCKED;
return (RoleState)Enum.Parse(typeof(RoleState), s.ToString());
}
/// <summary>
/// ���ݽ�ɫ���ơ�Ȩ�ޱ�ʶ������ĿID�ж��Ƿ���и�Ȩ��
/// </summary>
/// <param name="roleName"></param>
/// <param name="privlegeId"></param>
/// <param name="projectId"></param>
/// <returns></returns>
public static bool HavingPrivilege(string roleName, int privlegeId, string andSql)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT ASQL FROM PLM.ROLEINPRIVILEGE_TAB WHERE ROLENAME=:rolename AND PRIVILEGE_ID=:privilegeid";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
db.AddInParameter(cmd, "privilegeid", DbType.Int32, privlegeId);
object asql = db.ExecuteScalar(cmd);
if (asql != null)
{
if (asql.ToString() == "ALL") return true;
string finalSql = string.Format("{0}{1}", asql, string.IsNullOrEmpty(andSql) ? string.Empty : (" AND " + andSql));
DbCommand finalCmd = db.GetSqlStringCommand(finalSql);
object c = db.ExecuteScalar(finalCmd);
if (Convert.ToInt32(c) > 0) return true;
}
//Inherit Role Privilege Check
//bool hasPrivilege = false;
foreach (string inheritRole in FindInheritRole(roleName))
{
if (HavingPrivilege(inheritRole, privlegeId, andSql)) return true;
}
return false;
}
/// <summary>
/// ���ݽ�ɫ�������Ա��
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public static List<Role> FindName(string roleName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM PLM.userinrole_tab WHERE ROLENAME=:rolename";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
return EntityBase<Role>.DReaderToEntityList(db.ExecuteReader(cmd));
}
/// <summary>
/// ������������Ŀ�б�
/// </summary>
/// <returns></returns>
public static DataSet FindAllSubPro(string id)
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT t.project_id, t.sub_project_id,nvl(parent_sub_project_id,'0') parent_sub_project_id,t.description FROM IFSAPP.SUB_PROJECT T WHERE T.PROJECT_ID =:id ORDER BY t.sub_project_id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return db.ExecuteDataSet(cmd);
}
/// <summary>
/// �Խ�ɫ�Ƴ�Ȩ��
/// </summary>
/// <param name="roleName"></param>
/// <returns></returns>
public static int RemovePrivilege(string roleName)
{
string sql = "DELETE FROM PLM.ROLEINPRIVILEGE_TAB WHERE ROLENAME=:rolename";
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
return db.ExecuteNonQuery(cmd);
}
