/// <summary>
/// 是否存在该记录
/// </summary>
public bool Exists(decimal djid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from \"yw_xsdjhzb\" where \"djid\"=:djid");
DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
db.AddInParameter(dbCommand, "djid", DbType.Int32, djid);
int cmdresult;
object obj = db.ExecuteScalar(dbCommand);
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// 从采购申请表中查询某零件是否已经存在多个MEO NO
/// </summary>
/// <returns></returns>
public static int meomssCount(string sql, string DBname)
{
OracleDatabase db = new OracleDatabase(DBname);
// Database db = DatabaseFactory.CreateDatabase(DBname);
// string sql = "SELECT COUNT(P_REQUISITION_NO) FROM IFSAPP.PROJECT_MISC_PROCUREMENT misc where P_REQUISITION_NO is not null and SITE=:site and misc.PART_NO=:partno";
DbCommand cmd = db.GetSqlStringCommand(sql);
//db.AddInParameter(cmd, "site", DbType.String, site);
// db.AddInParameter(cmd, "partno", DbType.String, Partno);
object rname = db.ExecuteScalar(cmd);
return((rname == null || rname == DBNull.Value)? 0: Convert.ToInt32(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 从采购申请表中查询某零件是否存在MEO NO
/// </summary>
/// <returns></returns>
public static int meomssExistERP(string meono, string partno)
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT 1 FROM IFSAPP.PROJECT_MISC_PROCUREMENT misc where P_REQUISITION_NO =:meono and part_no ='" + partno + "'";
DbCommand cmd = db.GetSqlStringCommand(sql);
//db.AddInParameter(cmd, "site", DbType.String, site);
db.AddInParameter(cmd, "meono", DbType.String, meono);
object rname = db.ExecuteScalar(cmd);
return((rname == null || rname == DBNull.Value) ? 0 : Convert.ToInt32(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 根据用户名称及权限标识符判断是否具有该权限
/// </summary>
/// <param name="userName"></param>
/// <param name="privilegeFlag"></param>
/// <returns></returns>
public static bool HavingPrivilege(string userName, string privilegeFlag, string andSql)
{
int privlegeId = Privilege.FindIdByFlag(privilegeFlag);
if (privlegeId == 0)
{
return(false);
}
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT ASQL FROM PLM.USERINPRIVILEGE_TAB WHERE USERNAME=:username AND PRIVILEGE_ID=:privilegeid";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "username", DbType.String, userName);
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);
}
}
List <string> roleNameList = FindRoleName(userName);
if (roleNameList.Count == 0)
{
return(false);
}
bool ret = false;
foreach (string roleName in roleNameList)
{
if (Role.HavingPrivilege(roleName, privlegeId, andSql))
{
ret = true; break;
}
}
return(ret);
}
/// <summary>
/// 功能：对传入的SQL执行查询操作,返回单个值
/// 日期：2013-05-15
/// </summary>
/// <param name="sql">执行的数据库</param>
/// <param name="parList">参数</param>
/// <param name="dbT">执行库</param>
/// <returns></returns>
public static object ExecuteScalar(string sql, List <ParameterEntity> parList, DataBaseType dbT)
{
try
{
OracleDatabase oraDb = DBManager.CreateDataBase(dbT);
DbCommand cmd = oraDb.GetSqlStringCommand(sql);
if (parList != null && parList.Count > 0)
{
foreach (ParameterEntity par in parList)
{
oraDb.AddInParameter(cmd, par.Name, par.DbType, par.Value);
}
}
object obj = oraDb.ExecuteScalar(cmd);
if (Convert.IsDBNull(obj))
{
return(null);
}
return(obj);
}
catch (Exception e)
{
//return null;
throw e;
}
}
/// <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);
}
public void RecordsAreNotSavedwhenLoadDataSetStoredProcCommandAndTransactionRollback()
{
OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase("OracleTest");
DataSet dsCountry = new DataSet();
DbCommand dbAddCountry = db.GetStoredProcCommand("AddCountryListAll");
db.AddInParameter(dbAddCountry, "vCountryCode", DbType.String, "SCO");
db.AddInParameter(dbAddCountry, "vCountryName", DbType.String, "Scotland");
DbCommand dbAddCountry1 = db.GetStoredProcCommand("AddCountryListAll");
db.AddInParameter(dbAddCountry1, "vCountryCode", DbType.String, "SCO");
db.AddInParameter(dbAddCountry1, "vCountryName", DbType.String, "Scotland");
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
db.LoadDataSet(dbAddCountry, dsCountry, "Country", transaction);
db.LoadDataSet(dbAddCountry1, dsCountry, "Country", transaction);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
string countryName = (string)db.ExecuteScalar(CommandType.Text, "select countryName from Country where CountryCode='SCO'");
Assert.IsNull(countryName);
}
/// <summary>
/// 新增MEO从表
/// </summary>
/// <returns></returns>
public int REQUIRE_Add()
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("INSERT INTO plm.MM_PART_REQUIRE_TAB(DISCIPLINEID,ECPROJECTID,REQUIRE_NO,CONTRACT,INFORMATION,IS_INVENTORY,CREATE_DATE,CREATER,PROJECT_ID,SYSTEM_ID,PARTTYPE_ID,REMARK,ERP_PARTTYPE,ERP_DISCIPLINE,state) VALUES (:discpid,:ECprojectid,:requireno,:contract,:information,:isinventory,sysdate,:creater,:projectId,:sysId,:parttypeid,:remark,:ERPtypeid,:ERPDiscip,1)");
db.AddInParameter(cmd, "requireno", DbType.String, REQUIRE_NO);
db.AddInParameter(cmd, "contract", DbType.String, CONTRACT);
db.AddInParameter(cmd, "information", DbType.String, INFORMATION);
db.AddInParameter(cmd, "isinventory", DbType.String, IS_INVENTORY);
db.AddInParameter(cmd, "creater", DbType.String, CREATER);
//db.AddInParameter(cmd, "createrole", DbType.String, "MaterialApp");
db.AddInParameter(cmd, "projectId", DbType.String, PROJECT_ID);
db.AddInParameter(cmd, "sysId", DbType.String, SYSTEM_ID);
db.AddInParameter(cmd, "remark", DbType.String, REMARK);
db.AddInParameter(cmd, "parttypeid", DbType.String, PARTTYPE_ID);
db.AddInParameter(cmd, "ECprojectid", DbType.String, ECPROJECTID);
db.AddInParameter(cmd, "discpid", DbType.String, DISCIPLINEID);
db.AddInParameter(cmd, "ERPtypeid", DbType.String, ERP_PARTTYPE);
db.AddInParameter(cmd, "ERPDiscip", DbType.String, ERP_DISCIPLINE);
int requireId = 0;
int rowsAffected = db.ExecuteNonQuery(cmd);
if (rowsAffected > 0)
{
DbCommand cmdSeq = db.GetSqlStringCommand("SELECT PLM.MM_REQUIRE_SEQ.CURRVAL FROM DUAL");
requireId = Convert.ToInt32(db.ExecuteScalar(cmdSeq));
}
return(requireId);
}
public static string GetSystemTree(int systemId)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "select PLM.PROJECT_SYSTEM_API.GetSystemTree(" + systemId + ") from dual";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteScalar(cmd).ToString());
}
/// <summary>
/// 根据项目ID和分段描述查找BLOCKID
/// </summary>
/// <param name="block"></param>
/// <param name="projectid"></param>
/// <returns></returns>
public static int FindBlockId(string block, string projectid)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT BLOCK_ID FROM PLM.Project_Block_tab WHERE PROJECT_ID=:projectid and Description like '%" + block + "%'";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "projectid", DbType.String, projectid);
return(Convert.ToInt32(db.ExecuteScalar(cmd)));
}
public static int FindDelDrawing(int id)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT DRAWING_ID FROM PLM.project_drawing_tab WHERE BLOCK_ID=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.Int32, id);
return(Convert.ToInt32(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 返回所有inventory part列表
/// </summary>
/// <returns></s>
public static string FindInvPartName(string partno, string site)
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT DESCRIPTION FROM IFSAPP.inventory_part where PART_NO = '" + partno + "' and CONTRACT='" + site + "'";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(Convert.ToString(db.ExecuteScalar(cmd)));
}
public static string GetFamilyName(string ID)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand("select IFSAPP.INVENTORY_PRODUCT_FAMILY_API.Get_Description(PART_PRODUCT_FAMILY) familyname from IFSAPP.inventory_part where PART_PRODUCT_FAMILY=:id");
db.AddInParameter(cmd, "id", DbType.String, ID);
return(Convert.ToString(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 根据System编号查找删除信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static int Findid(int id)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT system_id FROM PLM.PROJECT_System_TAB WHERE parent_Id=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.Int32, id);
return(Convert.ToInt32(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 根据系统ID查找系统描述
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static string GetDes(int id)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT description FROM PLM.PROJECT_System_TAB WHERE System_Id=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.Int32, id);
return(Convert.ToString(db.ExecuteScalar(cmd)));
}
public static int FindDelDevice(int id)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT DEVICE_ID FROM PLM.device_tab WHERE SYSTEM_ID=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.Int32, id);
return(Convert.ToInt32(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 根据ERP的项目ID查找对应ECDMS的项目ID
/// </summary>
/// <param name="proid"></param>
/// <returns></returns>
public static int FindProjectid(string proid)
{
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, proid);
return(Convert.ToInt32(db.ExecuteScalar(cmd)));
}
public static string FindPartTypeDesc(int typeid)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT TYPE_DESC FROM plm.MM_PART_TYPE_TAB WHERE TYPEID=:typeid";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "typeid", DbType.Int32, typeid);
return(Convert.ToString(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 获得项目名字
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static string GetProName(string id)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT name FROM PLM.PROJECT_TAB WHERE Id=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return(Convert.ToString(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 根据申请单号和项目ID查找申请号
/// </summary>
/// <param name="meoid"></param>
/// <param name="projectid"></param>
/// <returns></returns>
public static string GetMEONO(string meoid, string projectid)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT require_no FROM plm.mm_part_require_tab WHERE REQUIRE_ID=" + meoid + " and ecprojectid=" + projectid;
DbCommand cmd = db.GetSqlStringCommand(sql);
object pdsumqty = db.ExecuteScalar(cmd);
return((pdsumqty == null || pdsumqty == DBNull.Value) ? "" : Convert.ToString(pdsumqty));
}
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)));
}
public static string GetIsInventory(string site, string PartNo)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand("select IFSAPP.Inventory_Part_API.Part_Exist(CONTRACT, PART_NO) isExist from IFSAPP.inventory_part where CONTRACT=:site and PART_NO=:partno");
db.AddInParameter(cmd, "site", DbType.String, site);
db.AddInParameter(cmd, "partno", DbType.String, PartNo);
return(Convert.ToString(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 取得ERP中的积压库存
/// </summary>
/// <param name="site"></param>
/// <param name="PartNo"></param>
/// <returns></returns>
public static string GetInventoryJYqty(string site, string PartNo)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand("select IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Reserved(CONTRACT,PART_NO,NULL) invQty from IFSAPP.inventory_part where CONTRACT=:site and PART_NO=:partno");
db.AddInParameter(cmd, "site", DbType.String, site);
db.AddInParameter(cmd, "partno", DbType.String, PartNo);
return(string.IsNullOrEmpty(Convert.ToString(db.ExecuteScalar(cmd))) == false?Convert.ToString(db.ExecuteScalar(cmd)) : "0");
}
/// <summary>
/// 取得spec字段个数
/// </summary>
/// <param name="typeid"></param>
/// <param name="num"></param>
/// <returns></returns>
public static int GetSpecCou(string typeid)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = "SELECT spec_namecount(" + typeid + ") FROM dual";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(Convert.ToInt32(db.ExecuteScalar(cmd)));
}
public static int GetPartParaCou(string partid, string creator, string projectstr)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = "SELECT count(*) from MM_PART_PARAMETER_TAB where projectid='" + projectstr + "' and part_no=" + partid + " and last_flag=0 and discipline =" + creator;
DbCommand cmd = db.GetSqlStringCommand(sql);
return(Convert.ToInt32(db.ExecuteScalar(cmd)));
}
public static string FindName(string id)
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT description FROM IFSAPP.ACTIVITY WHERE activity_seq=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return(Convert.ToString(db.ExecuteScalar(cmd)));
}
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)));
}
/// <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)));
}
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>
/// 根据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)));
}