public static List<MEOMSS_discipline_new> FindAll(string sql)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
DbCommand cmd = db.GetSqlStringCommand(sql);
return EntityBase<MEOMSS_discipline_new>.DReaderToEntityList(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));
}
/// <summary>
/// ��������Project�б�
/// </summary>
/// <returns></returns>
public static List<project> FindAll()
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
// Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT * FROM IFSAPP.PROJECT";
DbCommand cmd = db.GetSqlStringCommand(sql);
return EntityBase<project>.DReaderToEntityList(db.ExecuteReader(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>
/// ��������Project�б�
/// </summary>
/// <returns></returns>
public static List<ReasonCode> FindAll()
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
// Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT * FROM IFSAPP.YRS_REQUISITION_REASON_TAB";
DbCommand cmd = db.GetSqlStringCommand(sql);
return EntityBase<ReasonCode>.DReaderToEntityList(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));
}
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));
}
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 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>
/// <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 List<Role> FindAll(string userName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT r.* FROM PLM.ROLE_TAB r, PLM.USERINROLE_TAB u WHERE r.ROLENAME=u.ROLENAME AND u.USERNAME=:username";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "username", DbType.String, userName);
return EntityBase<Role>.DReaderToEntityList(db.ExecuteReader(cmd));
}
/// <summary>
/// ���ݽ�ɫ���������Լ̳еĽ�ɫ���ϣ��������䱾����̳������Ľ�ɫ��
/// </summary>
/// <param name="roleName"></param>
/// <returns></returns>
public static List<Role> FindAvailableInheritRole(string roleName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM PLM.ROLE_TAB WHERE ROLENAME<>:rolename AND ROLENAME NOT IN (SELECT DISTINCT ROLENAME FROM PLM.ROLEINHERIT_TAB WHERE INHERITNAME=: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 List<PartType> Find1STPartType()
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM plm.MM_PART_TYPE_TAB WHERE PARENT_ID=0";
DbCommand cmd = db.GetSqlStringCommand(sql);
return EntityBase<PartType>.DReaderToEntityList(db.ExecuteReader(cmd));
}
/// <summary>
/// �������н�ɫ
/// </summary>
/// <returns></returns>
public static List<Role> FindAll()
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM PLM.ROLE_TAB ORDER BY ROLENAME";
DbCommand cmd = db.GetSqlStringCommand(sql);
return EntityBase<Role>.DReaderToEntityList(db.ExecuteReader(cmd));
}
/// <summary>
/// ���ص�ǰ��Ŀ��ǰ��������Ԥ����ʷ
/// </summary>
/// <param name="pid"></param>
/// <param name="projectstr"></param>
/// <param name="creator"></param>
/// <param name="lastflag"></param>
/// <returns></returns>
public static List<PartParameter> FindPartList(string partid, string projectstr, string creator)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM mm_part_para_view t WHERE t.last_flag=0 and t.creator='" + creator + "' and t.PARTID=" + partid + " and t.projectid ='" + projectstr + "'";
DbCommand cmd = db.GetSqlStringCommand(sql);
return EntityBase<PartParameter>.DReaderToEntityList(db.ExecuteReader(cmd));
}
public static List<PartBasicInfo> FindSelectResult(string sql)
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
return EntityBase<PartBasicInfo>.DReaderToEntityList(db.ExecuteReader(cmd));
}
/// <summary>
/// 根据用户名获得其所有权限id
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public static string FindPrivilegeidList(string userName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT PRIVILEGE_ID FROM PLM.USERINPRIVILEGE_TAB WHERE LOWER(USERNAME)=:username";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, ":username", DbType.String, userName.ToLower());
string pidList = string.Empty;
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
pidList += ("," + Convert.ToInt32(dr[0]));
dr.Close();
}
if (pidList != string.Empty)
pidList = pidList.Substring(1);
return pidList;
}
/// <summary>
/// ������������Ŀ�б�
/// </summary>
/// <returns></returns>
public static List<PartBasicInfo> FindAll(string Pid, string ACT_SEQ)
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT PROJECT_ID, ACTIVITY_SEQ, SITE, PART_NO, IFSAPP.PURCHASE_PART_API.GET_DESCRIPTION(SITE, PART_NO) PART_NAME,IFSAPP.INVENTORY_PART_API.GET_UNIT_MEAS(SITE, PART_NO) UNIT, IFSAPP.INVENTORY_PART_API.PART_EXIST(SITE, PART_NO) IS_EXIST, ISSUE_FROM_INV, REQUEST_QTY, IFSAPP.PROJ_PROCU_RATION_API.GET_ACCU_RATION_QTY(MATR_SEQ_NO) SUM, REQUIRE_QTY -IFSAPP.PROJ_PROCU_RATION_API.GET_ACCU_RATION_QTY(MATR_SEQ_NO) REST, REQUEST_DATE, P_REQUISITION_NO, P_REQ_LINE_NO, P_REQ_RELEASE_NO, INFORMATION, REASON_CODE, IFSAPP.YRS_REQUISITION_REASON_API.GET_DESCRIPTION(REASON_CODE) REASON_DESC, DESIGN_CODE FROM IFSAPP.PROJECT_MISC_PROCUREMENT WHERE PROJECT_ID =:PROID AND (ACTIVITY_SEQ =:ACT_SEQ)";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "PROID", DbType.String, Pid);
db.AddInParameter(cmd, "ACT_SEQ", DbType.String, ACT_SEQ);
return EntityBase<PartBasicInfo>.DReaderToEntityList(db.ExecuteReader(cmd));
}
public static List<Spool> GetSpoolName(string drawingno,int flag)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = string.Empty;
if (flag == 0)
sql = "select * from SP_SPOOL_TAB t where t.drawingno='" + drawingno + "' and t.flag='Y'";
else
sql = "select * from SP_SPOOL_TAB t where t.modifydrawingno='" + drawingno + "' and t.flag='Y'";
DbCommand cmd = db.GetSqlStringCommand(sql);
return EntityBase<Spool>.DReaderToEntityList(db.ExecuteReader(cmd));
}
public static Ration Find(int sequenceNo)
{
//Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM plm.MM_PART_RATION_TAB WHERE ration_id=:seqNo";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "seqNo", DbType.Int32, sequenceNo);
return Populate(db.ExecuteReader(cmd));
}
/// <summary>
/// �������е�2������б�
/// </summary>
/// <returns></returns>
public static List<PartType> Find2STPartType(int typeid)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM plm.MM_PART_TYPE_TAB WHERE PARENT_ID=:typeid";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "typeid", DbType.Int32, typeid);
return EntityBase<PartType>.DReaderToEntityList(db.ExecuteReader(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 List<PartRelative> FindRelativeERPPartList(string stnPartno,string projectId,string Site)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM plm.MM_IFS_STA_PART_TAB where STA_PART_NO=:stnPartno and PROJECTID=:proId and SITE=:site";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "stnPartno", DbType.String, stnPartno);
db.AddInParameter(cmd, "proId", DbType.String, projectId);
db.AddInParameter(cmd, "site", DbType.String, Site);
return EntityBase<PartRelative>.DReaderToEntityList(db.ExecuteReader(cmd));
}
/// <summary>
/// 根据用户名获得用户
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public static User Find(string userName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//Database db = DatabaseFactory.CreateDatabase("oidsConnection");
string sql = "SELECT * FROM PLM.USER_TAB WHERE LOWER(NAME)=:username";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, ":username", DbType.String, userName.ToLower());
return Populate(db.ExecuteReader(cmd));
}
/// <summary>
/// ���ݽ�ɫ����ý�ɫ
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public static Role Find(string roleName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM PLM.ROLE_TAB WHERE ROLENAME=:rolename";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
return Populate(db.ExecuteReader(cmd));
}
public static PartParameter Find(int systemid,string project, string partno, string site,string creator)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//Database db = DatabaseFactory.CreateDatabase("oidsConnection");
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = "SELECT * FROM plm.MM_PART_PARAMETER_TAB WHERE systemid=:systemid and last_flag=1 and creator='" + creator + "' and PROJECTID=:projectid and PART_NO=:partno and CONTRACT=:site";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "projectid", DbType.String, project);
db.AddInParameter(cmd, "partno", DbType.String, partno);
db.AddInParameter(cmd, "site", DbType.String, site);
db.AddInParameter(cmd, "systemid", DbType.Int32, systemid);
return Populate(db.ExecuteReader(cmd));
}
/// <summary>
/// 根据用户名获得其所有角色名
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public static List<string> FindRoleName(string userName)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT r.ROLENAME FROM PLM.ROLE_TAB r, PLM.USERINROLE_TAB u WHERE r.ROLENAME=u.ROLENAME AND LOWER(u.USERNAME)=:username";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, ":username", DbType.String, userName.ToLower());
List<string> rnameList = new List<string>();
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
rnameList.Add(dr[0].ToString());
dr.Close();
}
return rnameList;
}
/// <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="sequenceNo"></param>
/// <returns></returns>
public static PartParameter Find(int sequenceNo)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = "SELECT * FROM plm.MM_PART_PARAMETER_TAB WHERE SEQUENCE_ID=:seqNo";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "seqNo", DbType.Int32, sequenceNo);
return Populate(db.ExecuteReader(cmd));
}
