//按专科获取候诊/就诊人次列表
public List <MedicalService> GetSpecialistMedicalService()
{
string cmd = @"select * from (Select count(*)hz,zkid from yyfz_yyxx where pbid in(
select pbid from yyfz_yspb where
trunc(sbsj) = trunc(sysdate) and zllx <>'15'and ztbz = '1'
and (zbyy is null or zbyy in(select dm from yyfz_ddlbn where lb = 'FZ15'and nblb = '01')))
and ztbz in('2','3','5') group by zkid) a left join
(Select count(*)jz,zkid from yyfz_yyxx where pbid in(
select pbid from yyfz_yspb where
trunc(sbsj) = trunc(sysdate) and zllx <>'15'and ztbz = '1'
and (zbyy is null or zbyy in(select dm from yyfz_ddlbn where lb = 'FZ15'and nblb = '01')))
and ztbz = '5'group by zkid) b on a.zkid = b.zkid left join (SELECT BMID, BMMC FROM XTGL_BMDM) on bmid=a.zkid order by hz desc";
List <MedicalService> list_MedicalService = new List <MedicalService>();
DbCommand queryCommand = db.GetSqlStringCommand(cmd);
using (IDataReader reader = db.ExecuteReader(queryCommand))
{
while (reader.Read())
{
MedicalService medicalservice = new MedicalService();
//medicalservice.DoctorID = reader["ysyhid"] is DBNull ? 0 : Convert.ToInt32(reader["ysyhid"]);
//medicalservice.DoctorName = reader["yhxm"] is DBNull ? "" : (reader["yhxm"]).ToString();
medicalservice.SpecialistID = reader["zkid"] is DBNull ? 0 : Convert.ToInt32(reader["zkid"]);
medicalservice.SpecialistName = reader["BMMC"] is DBNull ? "" : (reader["BMMC"]).ToString();
medicalservice.HZnums = reader["hz"] is DBNull ? 0 : Convert.ToInt32(reader["hz"]);
medicalservice.JZnums = reader["jz"] is DBNull ? 0 : Convert.ToInt32(reader["jz"]);
list_MedicalService.Add(medicalservice);
}
}
return(list_MedicalService);
}
/// <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 List <SurgeryDetailedInformation> GetSurgeryDetailedInformation(string oprationState, string searchType, string area, string operationType, string sDate, string eDate, string content)
{
List <SurgeryDetailedInformation> list_model = new List <SurgeryDetailedInformation>();
String command = GetCommandForSurgeryDetailedInformation(oprationState, searchType, area, operationType, sDate, eDate, content);
DbCommand queryCommand = db.GetSqlStringCommand(command);
using (IDataReader reader = db.ExecuteReader(queryCommand))
{
while (reader.Read())
{
SurgeryDetailedInformation model = new SurgeryDetailedInformation();
model.SurgeryCode = reader["SurgeryCode"] is DBNull ? "" : reader["SurgeryCode"].ToString();
model.OperatingRoomCode = reader["OperatingRoomCode"] is DBNull ? "" : reader["OperatingRoomCode"].ToString();
model.PateintName = reader["PateintName"] is DBNull ? "" : reader["PateintName"].ToString();
if (reader["SurgeryStartTime"] is DBNull)
{
model.SurgeryStartTime = null;
}
else
{
model.SurgeryStartTime = Convert.ToDateTime(reader["SurgeryStartTime"].ToString());
}
model.SurgeryType = reader["SurgeryType"] is DBNull ? "" : reader["SurgeryType"].ToString();
model.SurgeonDoctor = reader["SurgeonDoctor"] is DBNull ? "" : reader["SurgeonDoctor"].ToString();
model.Anesthesiologist = reader["Anesthesiologist"] is DBNull ? "" : reader["Anesthesiologist"].ToString();
model.SurgeryName = reader["SurgeryName"] is DBNull ? "" : reader["SurgeryName"].ToString();
list_model.Add(model);
}
}
return(list_model);
}
/// <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>
/// 根据用户名称及权限标识符判断是否具有该权限
/// </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>
/// 功能：查询分页数据
/// 日期：2013-05-15
/// </summary>
/// <param name="sqlStr">查询SQL</param>
/// <param name="pi">页码</param>
/// <param name="pageSize">每页显示条数</param>
/// <param name="parList">参数类型</param>
/// <param name="dbT">执行库</param>
/// <returns></returns>
public static PagedTable GetDataByPage(string sqlStr, int pi, int pageSize, List <ParameterEntity> parList, DataBaseType dbT)
{
string falg = DataPageFalg;
#region 查询数据语句
//String sqlData = @"SELECT * FROM (SELECT A.*, ROWNUM ROW_NUM FROM (" + sqlStr + ") A) WHERE ROW_NUM > :ROW_NUM_OLD AND ROW_NUM <= :ROW_NUM_NEW";
String sqlData = @"SELECT * FROM (SELECT A.*, ROWNUM ROW_NUM FROM (" + sqlStr + ") A WHERE ROWNUM<= :ROW_NUM_NEW) WHERE ROW_NUM > :ROW_NUM_OLD";
#endregion
#region 查询数据总条数语句
String sqlCount = "(SELECT COUNT(1) ROW_TOTAL from (" + sqlStr + ")) ROW_TOTAL ";
#endregion
String SQL = "SELECT * FROM " + sqlCount + " ,(" + sqlData + ")";
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);
}
}
oraDb.AddInParameter(cmd, ":ROW_NUM_OLD", DbType.Int32, (pi - 1) * pageSize);
oraDb.AddInParameter(cmd, ":ROW_NUM_NEW", DbType.Int32, pageSize * pi);
DataSet ds = oraDb.ExecuteDataSet(cmd);
PagedTable pt = new PagedTable(ds.Tables[0], pi, pageSize, BusinessHelper.GetRowTotal(ds.Tables[0]));
return(pt);
}
/// <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>
/// 执行数据库语句支持存储过程 2013-07-01
/// </summary>
/// <param name="sql"></param>
/// <param name="parList"></param>
/// <param name="dbT"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, List <ParameterEntity> parList, DataBaseType dbT, CommandType cmdType)
{
try
{
DbCommand cmd;
OracleDatabase oraDb = DBManager.CreateDataBase(dbT);
if (cmdType == CommandType.StoredProcedure)
{
cmd = oraDb.GetStoredProcCommand(sql);
}
else
{
cmd = oraDb.GetSqlStringCommand(sql);
}
cmd.CommandType = cmdType;
if (parList != null && parList.Count > 0)
{
foreach (ParameterEntity par in parList)
{
oraDb.AddInParameter(cmd, par.Name, par.DbType, par.Value);
}
}
return(oraDb.ExecuteNonQuery(cmd));
}
catch (Exception e)
{
//return 0;
throw e;
}
}
/// <summary>
/// 功能:对传入的SQL执行增、改、删等操作【附加事物】
/// 日期:2013-05-15
/// </summary>
/// <param name="sql">执行的SQL</param>
/// <param name="parList">参数列表</param>
/// <param name="tran">事物</param>
/// <param name="dbT">执行的库</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, List <ParameterEntity> parList, DbTransaction tran, DataBaseType dbT)
{
try
{
if (string.IsNullOrEmpty(sql))
{
return(1);
}
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);
}
}
return(oraDb.ExecuteNonQuery(cmd, tran));
}
catch (Exception e)
{
//return 0;
throw e;
}
}
/// <summary>
/// 根据blockId列表查找BlockName
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public static string GetNames(string ids)
{
if (ids.Trim() == string.Empty)
{
return(string.Empty);
}
string blockNames = string.Empty;
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string Sql = "SELECT DESCRIPTION FROM PLM.PROJECT_BLOCK_TAB WHERE BLOCK_ID IN (" + ids + ")";
DbCommand cmd = db.GetSqlStringCommand(Sql);
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
{
blockNames += "," + dr[0].ToString();
}
dr.Close();
}
if (blockNames != string.Empty)
{
blockNames = blockNames.Substring(1);
}
return(blockNames);
}
public int Add()
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("INSERT INTO PLM.MM_PART_PARAMETER_TAB(description,blockid,systemid,ecprojectid,last_flag,PART_NO,CONTRACT,WEIGHT_SINGLE,PREDICTION_QTY,PREDICTION_ALERT,COEFFICIENT_ERP,PROJECTID,PREDICT_CREATOR,PROJECT_ZONE,DISCIPLINE,FIRSTBATCH_QTY,FIRSTBATCH_DATE,SECONDBATCH_QTY,SECONDBATCH_DATE,PART_TYPE,FINAL_PREDICTION_QTY,OPERATOR,MODIFY_REASON,PREDICT_DATE,UNIT) VALUES (:description,:blockid,:systemid,:ecprojectid,:lastflag,:partno,:site,:weightSingle,:preQty,:pre_alert,:coeficient,:PROJECTID,:creator,:predictzone,:discipline,:firstpreQty,:firstpreDate,:secondpreQty,:secondpreDate,:part_type,:finalpreQty,:operator,:modreason,:predictdate,:unitmeas)");
db.AddInParameter(cmd, "description", DbType.String, DESCRIPTION);
db.AddInParameter(cmd, "blockid", DbType.String, BLOCKID);
db.AddInParameter(cmd, "systemid", DbType.Int32, SYSTEMID);
db.AddInParameter(cmd, "ecprojectid", DbType.Int32, ECPROJECTID);
db.AddInParameter(cmd, "lastflag", DbType.Int32, LAST_FLAG);
db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
db.AddInParameter(cmd, "site", DbType.String, CONTRACT);
db.AddInParameter(cmd, "weightSingle", DbType.Decimal, WEIGHT_SINGLE);
db.AddInParameter(cmd, "preQty", DbType.Decimal, PREDICTION_QTY);
db.AddInParameter(cmd, "pre_alert", DbType.Decimal, PREDICTION_ALERT);
db.AddInParameter(cmd, "coeficient", DbType.Decimal, COEFFICIENT_ERP);
db.AddInParameter(cmd, "PROJECTID", DbType.String, PROJECTID);
db.AddInParameter(cmd, "creator", DbType.String, PREDICT_CREATOR);
db.AddInParameter(cmd, "predictzone", DbType.String, PROJECT_ZONE);
db.AddInParameter(cmd, "discipline", DbType.String, DISCIPLINE);
db.AddInParameter(cmd, "firstpreQty", DbType.Decimal, FIRSTBATCH_QTY);
db.AddInParameter(cmd, "firstpreDate", DbType.DateTime, FIRSTBATCH_DATE);
db.AddInParameter(cmd, "secondpreQty", DbType.Decimal, SECONDBATCH_QTY);
db.AddInParameter(cmd, "secondpreDate", DbType.DateTime, SECONDBATCH_DATE);
db.AddInParameter(cmd, "part_type", DbType.String, PART_TYPE);
db.AddInParameter(cmd, "finalpreQty", DbType.Decimal, FINAL_PREDICTION_QTY);
db.AddInParameter(cmd, "predictdate", DbType.DateTime, PREDICT_DATE);
db.AddInParameter(cmd, "operator", DbType.String, OPERATOR);
db.AddInParameter(cmd, "modreason", DbType.String, MODIFY_REASON);
db.AddInParameter(cmd, "unitmeas", DbType.String, UNIT);
return(db.ExecuteNonQuery(cmd));
}
public DataTable select_tmp_poslog_ora()
{
DataTable dtdoc = null;
//string sqlquery = "select RTL_LOC_ID,WKSTN_ID,TRANS_SEQ,BUSINESS_DATE,NUMDOC,TOTAL,DOCUMENT_TYPCODE," +
// "(SELECT POSLOG_DATA FROM TRN_POSLOG_DATA B WHERE B.ORGANIZATION_ID = 2000 " +
// "AND B.RTL_LOC_ID = A.RTL_LOC_ID AND B.WKSTN_ID = A.WKSTN_ID AND B.TRANS_SEQ = A.TRANS_SEQ " +
// "AND ROWNUM = 1) AS POSLOG_DATA " +
// "from " + Ent_Acceso_BD.nom_tabla_poslog + " A WHERE (SEND_SERVER IS NULL OR SEND_SERVER='X') AND ROWNUM<=50";
string sqlquery = "select A.RTL_LOC_ID,A.WKSTN_ID,A.TRANS_SEQ,A.BUSINESS_DATE,A.NUMDOC,A.TOTAL,A.DOCUMENT_TYPCODE,B.POSLOG_DATA " +
"from " + Ent_Acceso_BD.nom_tabla_poslog + " A " +
"INNER JOIN TRN_POSLOG_DATA B ON B.ORGANIZATION_ID = 2000 AND B.RTL_LOC_ID = A.RTL_LOC_ID " +
"AND B.WKSTN_ID = A.WKSTN_ID AND B.TRANS_SEQ = A.TRANS_SEQ " +
"WHERE(SEND_SERVER IS NULL OR SEND_SERVER = 'X') AND ROWNUM<= 50";
try
{
object results = new object[1];
Database db = new OracleDatabase(Ent_Acceso_BD.conn());
DbCommand dbCommandWrapper = db.GetSqlStringCommand(sqlquery);
dtdoc = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
}
catch
{
dtdoc = null;
}
return(dtdoc);
}
public int Update()
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("update plm.MM_PART_RATION_TAB set DISCIPLINE=:dpname,PART_NO=:partno,CONTRACT=:contract,P_REQUISITION_NO=:reqNo,ISSUED_QTY=:issqty,ISSUED_DATE=:issDate,IF_INVENTORY=:isInv,PURPOSE=:purpose,BLOCK_ID=:BLOCK_ID,REASON_CODE=:reasoncode,DESIGN_CODE=:designcode,REMARK=:information,UPDATE_DATE=sysdate,PROJECT_ID=:projectId,PART_ZONE=:partzone,part_discipline=:partdp,PART_FX= :actId,PART_TYPE=:parttype,unit=:unit,PART_NAME=:partname where RATION_ID=:rationId");
db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
db.AddInParameter(cmd, "partname", DbType.String, PART_NAME);
db.AddInParameter(cmd, "contract", DbType.String, CONTRACT);
db.AddInParameter(cmd, "reqNo", DbType.String, P_REQUISITION_NO);
db.AddInParameter(cmd, "issqty", DbType.Single, ISSUED_QTY);
db.AddInParameter(cmd, "issDate", DbType.Date, ISSUED_DATE);
db.AddInParameter(cmd, "isInv", DbType.String, IF_INVENTORY);
db.AddInParameter(cmd, "purpose", DbType.String, PURPOSE);
db.AddInParameter(cmd, "BLOCK_ID", DbType.String, BLOCK_ID);
db.AddInParameter(cmd, "reasoncode", DbType.String, REASON_CODE);
db.AddInParameter(cmd, "designcode", DbType.String, DESIGN_CODE);
db.AddInParameter(cmd, "information", DbType.String, REMARK);
db.AddInParameter(cmd, "projectId", DbType.String, PROJECT_ID);
db.AddInParameter(cmd, "partzone", DbType.String, PART_ZONE);
db.AddInParameter(cmd, "partdp", DbType.String, PART_DISCIPLINE);
db.AddInParameter(cmd, "unit", DbType.String, UNIT);
db.AddInParameter(cmd, "actId", DbType.String, PART_FX);
db.AddInParameter(cmd, "parttype", DbType.String, PART_TYPE);
db.AddInParameter(cmd, "rationId", DbType.Int32, RATION_ID);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 更新材料
/// </summary>
/// <returns></returns>
public int Update()
{
//Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("Update PLM.MM_PART_TAB set" +
" part_densityunit=:partdensityunit, supplycircle=:spcircle,PART_NO=:partno,CONTRACT=:site,PART_SPEC=:partspec,Part_TYpe=:parttype,parentid=:parentid,part_mat=:partmat,part_cert=:partcert,PART_level=:partlevel,part_unit=:partunit,part_unitdensity=:partunitdensity,part_spec1=:partspec1,part_spec2=:partspec2,part_spec3=:partspec3,part_spec4=:partspec4,UPDATER=:creator,UPDATEDATE=:updatetime,Replace_code=:replace_code" +
" where id=:partid");
db.AddInParameter(cmd, "spcircle", DbType.Int32, SUPPLYCIRCLE);
db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
db.AddInParameter(cmd, "site", DbType.String, CONTRACT);
db.AddInParameter(cmd, "partspec", DbType.String, PART_SPEC);
db.AddInParameter(cmd, "parttype", DbType.String, PART_TYPE);
db.AddInParameter(cmd, "parentid", DbType.Int32, PARENTID);
db.AddInParameter(cmd, "partmat", DbType.String, PART_MAT);
db.AddInParameter(cmd, "partcert", DbType.String, PART_CERT);
db.AddInParameter(cmd, "partlevel", DbType.String, PART_LEVEL);
db.AddInParameter(cmd, "creator", DbType.String, UPDATER);
db.AddInParameter(cmd, "partunit", DbType.String, PART_UNIT);
db.AddInParameter(cmd, "partunitdensity", DbType.Decimal, PART_UNITDENSITY);
db.AddInParameter(cmd, "partspec1", DbType.String, PART_SPEC1);
db.AddInParameter(cmd, "partspec2", DbType.String, PART_SPEC2);
db.AddInParameter(cmd, "partspec3", DbType.String, PART_SPEC3);
db.AddInParameter(cmd, "partspec4", DbType.String, PART_SPEC4);
db.AddInParameter(cmd, "partdensityunit", DbType.String, PART_DENSITYUNIT);
db.AddInParameter(cmd, "partid", DbType.Int32, ID);
db.AddInParameter(cmd, "updatetime", DbType.DateTime, UPDATEDATE);
db.AddInParameter(cmd, "replace_code", DbType.String, REPLACE_CODE);
return(db.ExecuteNonQuery(cmd));
}
public int Add()
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("INSERT INTO plm.MM_PART_RATION_TAB(PART_NO,CONTRACT,P_REQUISITION_NO,ISSUED_QTY,ISSUED_DATE,IF_INVENTORY,PURPOSE,BLOCK_ID,REASON_CODE,DESIGN_CODE,REMARK,CREATE_DATE,CREATER,PROJECT_ID,part_zone,part_discipline,part_fx,part_type,unit,PART_NAME) VALUES (:partno,:contract,:requisionNo,:requireqty,:requirDate,:isInventory,:purpose,:blockID,:reasoncode,:designcode,:information,sysdate,:creater,:projectId,:parentProId,:subProId,:actId,:parttype,:unit,:partname)");
db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
db.AddInParameter(cmd, "contract", DbType.String, CONTRACT);
db.AddInParameter(cmd, "requisionNo", DbType.String, P_REQUISITION_NO);
db.AddInParameter(cmd, "requireqty", DbType.Single, ISSUED_QTY);
db.AddInParameter(cmd, "requirDate", DbType.Date, ISSUED_DATE);
db.AddInParameter(cmd, "isInventory", DbType.String, IF_INVENTORY);
db.AddInParameter(cmd, "purpose", DbType.String, PURPOSE);
db.AddInParameter(cmd, "blockID", DbType.String, BLOCK_ID);
db.AddInParameter(cmd, "reasoncode", DbType.String, REASON_CODE);
db.AddInParameter(cmd, "designcode", DbType.String, DESIGN_CODE);
db.AddInParameter(cmd, "information", DbType.String, INFORMATION);
db.AddInParameter(cmd, "creater", DbType.String, CREATER);
db.AddInParameter(cmd, "projectId", DbType.String, PROJECT_ID);
db.AddInParameter(cmd, "parentProId", DbType.String, PART_ZONE);
db.AddInParameter(cmd, "subProId", DbType.String, PART_DISCIPLINE);
db.AddInParameter(cmd, "actId", DbType.String, PART_FX);
db.AddInParameter(cmd, "parttype", DbType.String, PART_TYPE);
db.AddInParameter(cmd, "unit", DbType.String, UNIT);
db.AddInParameter(cmd, "partname", DbType.String, PART_NAME);
;
return(db.ExecuteNonQuery(cmd));
}
/// <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>
/// <returns></returns>
public int Add()
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("INSERT INTO PLM.MM_PART_TAB" +
"(part_densityunit,SUPPLYCIRCLE,PART_NO,CONTRACT,PART_SPEC,Part_TYpe,parentid,part_mat,part_cert,PART_level,part_unit,part_unitdensity,part_spec1,part_spec2,part_spec3,part_spec4,CREATOR)" +
" VALUES (:partdensityunit,:spcircle,:partno,:site,:partspec,:parttype,:parentid,:partmat,:partcert,:partlevel,:partunit,:partunitdensity,:partspec1,:partspec2,:partspec3,:partspec4,:creator)");
db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
db.AddInParameter(cmd, "site", DbType.String, CONTRACT);
db.AddInParameter(cmd, "partspec", DbType.String, PART_SPEC);
db.AddInParameter(cmd, "parttype", DbType.String, PART_TYPE);
db.AddInParameter(cmd, "parentid", DbType.Int32, PARENTID);
db.AddInParameter(cmd, "spcircle", DbType.Int32, SUPPLYCIRCLE);
db.AddInParameter(cmd, "partmat", DbType.String, PART_MAT);
db.AddInParameter(cmd, "partcert", DbType.String, PART_CERT);
db.AddInParameter(cmd, "partlevel", DbType.String, PART_LEVEL);
db.AddInParameter(cmd, "creator", DbType.String, CREATOR);
db.AddInParameter(cmd, "partunit", DbType.String, PART_UNIT);
db.AddInParameter(cmd, "partunitdensity", DbType.Decimal, PART_UNITDENSITY);
db.AddInParameter(cmd, "partspec1", DbType.String, PART_SPEC1);
db.AddInParameter(cmd, "partspec2", DbType.String, PART_SPEC2);
db.AddInParameter(cmd, "partspec3", DbType.String, PART_SPEC3);
db.AddInParameter(cmd, "partspec4", DbType.String, PART_SPEC4);
db.AddInParameter(cmd, "partdensityunit", DbType.String, PART_DENSITYUNIT);
return(db.ExecuteNonQuery(cmd));
}
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)));
}
public static DataSet QueryPartRationListERP(string sql)
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(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)));
}
/// <summary>
/// 返回所有系统列表
/// </summary>
/// <returns></returns>
public static List <ProjectSystem> FindAll()
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM PLM.PROJECT_SYSTEM_TAB";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(EntityBase <ProjectSystem> .DReaderToEntityList(db.ExecuteReader(cmd)));
}
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查找MEO从表信息
/// </summary>
/// <param name="sequenceNo"></param>
/// <returns></returns>
public static List <MEOsub> FindMEOList(string sequenceNo)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM mm_part_REQUIRELINE_tab t WHERE t.deleteflag='N' and t.require_id= " + sequenceNo;
DbCommand cmd = db.GetSqlStringCommand(sql);
return(EntityBase <MEOsub> .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)));
}
public static DataSet FindSelectResultDS(string sql)
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(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>
/// 返回所有名称列表
/// </summary>
/// <returns></returns>
public static List <ProjectBlock> FindAll()
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM Project_Block_tab";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(EntityBase <ProjectBlock> .DReaderToEntityList(db.ExecuteReader(cmd)));
}
/// <summary>
/// 更新父节点及parentIndex值
/// </summary>
/// <param name="nodeid"></param>
/// <param name="num"></param>
/// <returns></returns>
public static int UpdateParentAndParentIndex(int nodeid, int parentid, int num)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "UPDATE PLM.TREENODES_TAB SET PARENT_ID=" + parentid + ",PARENT_INDEX=" + num + " WHERE ID=" + nodeid;
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// parentIndex依次减1
/// </summary>
/// <param name="nodeid"></param>
/// <param name="beforenode"></param>
/// <returns></returns>
public static int UpdateParentIndexDel(int nodeid, int beforenode, int dropindex)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "UPDATE (SELECT PARENT_INDEX FROM PLM.TREENODES_TAB S WHERE S.PARENT_ID=" + nodeid + ")T SET T.PARENT_INDEX=T.PARENT_INDEX-1 WHERE T.PARENT_INDEX<" + dropindex + " AND T.PARENT_INDEX>=" + beforenode;
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteNonQuery(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)));
}
public void CanExecuteCommandWithEmptyPackages()
{
ConnectionStringSettings data = ConfigurationManager.ConnectionStrings["OracleTest"];
OracleDatabase oracleDatabase = new OracleDatabase(data.ConnectionString);
DbConnection connection = oracleDatabase.CreateConnection();
Assert.IsNotNull(connection);
Assert.IsTrue(connection is OracleConnection);
connection.Open();
DbCommand cmd = oracleDatabase.GetSqlStringCommand("Select * from Region");
cmd.CommandTimeout = 0;
}