/// <summary>
/// 取得材料的申请详细信息
/// </summary>
/// <param name="site"></param>
/// <param name="PartNo"></param>
/// <param name="projectid"></param>
/// <returns></returns>
public static InventoryPart GetRequiredqty(string site, string PartNo, string projectid)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sprojectname = projectid.Substring(projectid.Length - 3, 3);
string querysql = "select p.contract ,p.part_no ,p.description ,p.unit_meas unit,nvl((select sum(tt.qty_onhand - tt.qty_reserved) from ifsapp.yr_inv_on_hand_vw tt WHERE tt.part_no = p.part_no and tt.contract = p.contract and tt.req_dept like 'YL" + sprojectname + "%'), 0) qty_reserved,nvl((select sum(REQUIRE_QTY ) from IFSAPP.PROJECT_MISC_PROCUREMENT where PROJECT_ID = '" + projectid + "' and site = p.contract and issue_from_inv = 0 and PART_NO = p.part_no and (select state from ifsapp.purchase_req_line_part q where q.requisition_no =p_requisition_no and q.part_no=p.part_no) <>'Cancelled'),0) qty_onhand,nvl((select sum(IFSAPP.PROJ_PROCU_RATION_API.Get_Accu_Ration_Qty(MATR_SEQ_NO)) from IFSAPP.PROJECT_MISC_PROCUREMENT where PROJECT_ID = '" + projectid + "' and site = p.contract and issue_from_inv = 0 and PART_NO = p.part_no and (select state from ifsapp.purchase_req_line_part q where q.requisition_no =p_requisition_no and q.part_no=p.part_no) <>'Cancelled'),0) qty_issued from ifsapp.inventory_part p where p.part_status='A' " + " AND CONTRACT = '" + site + "'" + " AND part_no like '" + PartNo + "%'";
DbCommand cmd = db.GetSqlStringCommand(querysql);
return(Populate(db.ExecuteReader(cmd)));
}
/// <summary>
/// 取得材料的库存信息
/// </summary>
/// <param name="site"></param>
/// <param name="PartNo"></param>
/// <param name="projectid"></param>
/// <returns></returns>
public static InventoryPart GetOnhandqty(string site, string PartNo, string projectid)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand("select sum(qty_onhand) qty_onhand,sum(qty_reserved) qty_reserved,part_no,part_desc,unit from IFSAPP.yr_inv_on_hand_vw where CONTRACT=:site and PART_NO=:partno and req_dept like '%" + projectid + "%' group by part_no,part_desc,unit");
db.AddInParameter(cmd, "site", DbType.String, site);
db.AddInParameter(cmd, "partno", DbType.String, PartNo);
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>
/// 根据零件ID取得零件信息
/// </summary>
/// <param name="partid"></param>
/// <returns></returns>
public static Part Find(string partid)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//Database db = DatabaseFactory.CreateDatabase("oidsConnection");
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = "SELECT * FROM plm.MM_PART_TAB WHERE id=" + partid;
DbCommand cmd = db.GetSqlStringCommand(sql);
return(Populate(db.ExecuteReader(cmd)));
}
// <summary>
/// 根据ID查询MEO信息
/// </summary>
/// <param name="sequenceNo"></param>
/// <returns></returns>
public static MEOsub Find(int sequenceNo, string partid)
{
//Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM plm.MM_PART_REQUIRELINE_TAB WHERE REQUIRE_ID=:seqNo and deleteflag='N' and part_id=" + partid;
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "seqNo", DbType.Int32, sequenceNo);
return(Populate(db.ExecuteReader(cmd)));
}
/// <summary>
/// 返回所有子项目下的子项目列表
/// </summary>
/// <returns></returns>
public static List <SubProject> FindAllSubProjects(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(EntityBase <SubProject> .DReaderToEntityList(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)));
}
public static String GetConnectStr()
{
//String connectionString = "Data Source=ORACLE82;User id=his;Password=h7j1y4s5g;";
NameValueCollection app = System.Configuration.ConfigurationManager.AppSettings;
String ls_db = app["dbservername"];
String ls_user = app["predbuser"];
String ls_password = app["predbpwd"];
String userName = app["userName"];
String connectionString = GenConnectString(ls_db, ls_user, ls_password);
List <User> list_user = new List <User>();
OracleDatabase db = new OracleDatabase(connectionString);
#region 获取数据
using (DbConnection dbConnection = db.CreateConnection())
{
dbConnection.Open();
string connStr = @"select dbusername,dbuserpasswd from vi_dbuser";
DbCommand cmd = db.GetSqlStringCommand(connStr);
//db.AddInParameter(cmd, ":id", DbType.String, "12");
#region 12
//List<DbParameter> ListPara;
//DbParameter para = factory.CreateParameter();
//para.ParameterName = "@xm";
//para.Size = 10;
//para.DbType = DbType.String;
//para.Value = "郑成城";
#endregion
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
User user = new User();
user.UserName = f_passwd2(reader.GetString(0), 2).ToLower().Trim();
user.UserPassword = f_passwd2(reader.GetString(1), 2);
list_user.Add(user);
}
}
}
#endregion
foreach (User user in list_user)
{
if (user.UserName == userName)
{
return(GenConnectString(ls_db, user.UserName, user.UserPassword));
}
}
return("无");
}
/// <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)));
}
/// <summary>
/// 返回所有子项目列表
/// </summary>
/// <returns></returns>
public static List <Activity> FindAll(string id, string SubPro)
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT * FROM IFSAPP.ACTIVITY T WHERE T.PROJECT_ID =:id AND T.sub_project_id=:subPro ORDER BY t.activity_no";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
db.AddInParameter(cmd, "subPro", DbType.String, SubPro);
return(EntityBase <Activity> .DReaderToEntityList(db.ExecuteReader(cmd)));
}
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)));
}
public static List <ProjectSystem> FindSub(int projectid, string newFlag)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM PLM.PROJECT_SYSTEM_TAB WHERE PROJECT_ID=:projectid AND NEWFLAG=:newflag AND PARENT_ID<>0";
sql += " ORDER BY SYSTEM_ID";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "projectid", DbType.Int32, projectid);
db.AddInParameter(cmd, "newflag", DbType.String, newFlag);
return(EntityBase <ProjectSystem> .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)));
}
//1医生签到的主增量图
public List <DoctorRegisterMainInformaton> GetDoctorRegisterMainInformaton(string time)
{
List <DoctorRegisterMainInformaton> list_model = new List <DoctorRegisterMainInformaton>();
String command = GetCommandForDetailedDoctorRegistered();
DbCommand queryCommand = db.GetSqlStringCommand(command);
using (IDataReader reader = db.ExecuteReader(queryCommand))
{
int tempNumber = 0;
while (reader.Read())
{
DoctorRegisterMainInformaton model = new DoctorRegisterMainInformaton();
tempNumber += reader["RegisteredDoctorQuanty"] is DBNull ? 0 : Convert.ToInt32(reader["RegisteredDoctorQuanty"]);
model.RegisteredDoctorQuanty = tempNumber;
model.TimeSpan = reader["SpanTime"] is DBNull ? string.Empty : (reader["SpanTime"].ToString());
list_model.Add(model);
}
}
return(list_model);
}
/// <summary>
/// 从采购申请表中查询某零件唯一的MEO NO
/// </summary>
/// <returns></returns>
public static string meoNo(string sql, string DBname)
{
string meoNo = string.Empty;
OracleDatabase db = new OracleDatabase(DBname);
DbCommand cmd = db.GetSqlStringCommand(sql);
IDataReader rname = db.ExecuteReader(cmd);
while (rname.Read())
{
meoNo = meoNo + "," + rname[0].ToString();
}
return(meoNo);
}
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)));
}
public List <AverageTreatmentTime> GetAverageTime(DateTime startTime, DateTime endTime, String type)
{
var avgTreatmentTimes = new List <AverageTreatmentTime>();
String command = GetCommandDepongOnType(startTime, endTime, type);
DbCommand queryCommand = db.GetSqlStringCommand(command);
using (IDataReader reader = db.ExecuteReader(queryCommand))
{
while (reader.Read())
{
var att = new AverageTreatmentTime
{
TimeStemp = reader.GetString(0),
Appointment = reader.GetDouble(1),
AwaitingDiagnosis = reader.GetDouble(2),
PayFees = reader.GetDouble(3),
MedicineReceiving = reader.GetDouble(4)
};
avgTreatmentTimes.Add(att);
}
}
return(avgTreatmentTimes);
}
public void RecordsAreSavedWhenStoredProcWithParameterAndTransactionIsCommitted()
{
OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase("OracleTest");
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
int initialCount = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, "select count(countrycode) from Country"));
using (db.ExecuteReader(transaction, "AddCountryListAll", new object[] { "TEMP", "Temporary", "" })) { }
using (db.ExecuteReader(transaction, "DeleteCountryListAll", new object[] { "TEMP", "" })) { }
transaction.Commit();
int finalCount = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, "select count(countrycode) from Country"));
Assert.IsTrue(finalCount == initialCount);
}
catch (Exception e)
{
transaction.Rollback();
Assert.Fail("Transaction Rolled Back : " + e.Message);
}
}
}
public static List <Acceemp> Finding(string block, int flag)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = string.Empty;
if (flag == 0)
{
sql = "select * from plm.sp_acceemp t where t.BLOCKNAME='" + block + "' and t.flag='Y'";
}
else
{
sql = "select * from plm.sp_acceemp t where t.flag='Y' and t.spoolname in (select s.spoolname from plm.sp_spool_tab s where s.blockno='" + block + "' and s.flag='Y' and MODIFYDRAWINGNO<>null)";
}
DbCommand cmd = db.GetSqlStringCommand(sql);
return(EntityBase <Acceemp> .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)));
}
/// <summary>
///根据图号查询相关列表
/// </summary>
/// <param name="drawingno"></param>
/// <returns></returns>
public static List <Acceemp> Find(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 plm.sp_acceemp t where t.drawingno='" + drawingno + "' and t.flag='Y'";
}
else
{
sql = "select * from plm.sp_acceemp t where t.flag='Y' and t.spoolname in (select s.spoolname from plm.sp_spool_tab s where s.modifydrawingno='" + drawingno + "' and s.flag='Y')";
}
DbCommand cmd = db.GetSqlStringCommand(sql);
return(EntityBase <Acceemp> .DReaderToEntityList(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="SysId"></param>
/// <returns></returns>
public static List <int> GetChildSystems(int SysId)
{
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, SysId);
List <int> SysIdList = new List <int>();
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
{
SysIdList.Add(Convert.ToInt32(dr[0].ToString()));
}
dr.Close();
}
return(SysIdList);
}
/// <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>
/// <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>
/// 根据角色名获得其权限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);
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
*
* DbCommand dbCommand = db.GetStoredProcCommand("UP_GetRecordByPage");
* db.AddInParameter(dbCommand, "tblName", DbType.AnsiString, "yw_xsdjhzb");
* db.AddInParameter(dbCommand, "fldName", DbType.AnsiString, "djid");
* db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
* db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
* db.AddInParameter(dbCommand, "IsReCount", DbType.Boolean, 0);
* db.AddInParameter(dbCommand, "OrderType", DbType.Boolean, 0);
* db.AddInParameter(dbCommand, "strWhere", DbType.AnsiString, strWhere);
* return db.ExecuteDataSet(dbCommand);
* }*/
/// <summary>
/// 获得数据列表（比DataSet效率高，推荐使用）
/// </summary>
public List <SOSOshop.Model.Service.Orders.yw_xsdjhzb> GetListArray(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select djid,jgid,jjid,rq,djbh,djbs,dwid,ywyid,lxrid,jsfsid,hsje,name,url,is_kp ");
strSql.Append(" FROM yw_xsdjhzb ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
List <SOSOshop.Model.Service.Orders.yw_xsdjhzb> list = new List <SOSOshop.Model.Service.Orders.yw_xsdjhzb>();
using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (dataReader.Read())
{
list.Add(ReaderBind(dataReader));
}
}
return(list);
}
/// <summary>
/// 2门诊收入总额
/// </summary>
/// <param name="sfyq">收费院区</param>
//门诊收入总额
public List <IncomeStatistics> GetOutpatientIncome(DateTime startDateTime, DateTime endDateTime, String sfyq)
{
List <IncomeStatistics> list_income = new List <IncomeStatistics>();
String command = GetCommandForIncome(startDateTime, endDateTime, sfyq);
DbCommand queryCommand = db.GetSqlStringCommand(command);
using (IDataReader reader = db.ExecuteReader(queryCommand))
{
while (reader.Read())
{
IncomeStatistics income = new IncomeStatistics();
income.CashIncome = reader["CashIncome"] is DBNull ? 0 : Convert.ToInt64(reader["CashIncome"]);
income.AccountingIncome = reader["AccountingIncome"] is DBNull ? 0 : Convert.ToInt64(reader["AccountingIncome"]);
income.ReduceMoney = reader["ReduceMoney"] is DBNull ? 0 : Convert.ToInt64(reader["ReduceMoney"]);
income.StoredConsumeMoney = reader["StoredConsumeMoney"] is DBNull ? 0 : Convert.ToInt64(reader["StoredConsumeMoney"]);
income.CollectionMoney = reader["CollectionMoney"] is DBNull ? 0 : Convert.ToInt64(reader["CollectionMoney"]);
list_income.Add(income);
}
}
return(list_income);
}
/// <summary>
/// 根据系统ID获得其子系统下的所有ID集合
/// </summary>
/// <param name="systemId"></param>
/// <param name="recursiveFlag">是否递归查询</param>
/// <returns></returns>
public static List <string> FindAllChild(int systemId, bool recursiveFlag)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT SYSTEM_ID FROM PLM.PROJECT_SYSTEM_TAB WHERE PARENT_ID=:pid";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "pid", DbType.Int32, systemId);
List <string> idsList = new List <string>();
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
{
idsList.Add(dr[0].ToString());
if (recursiveFlag)
{
idsList.AddRange(FindAllChild(Convert.ToInt32(dr[0]), recursiveFlag));
}
}
dr.Close();
}
return(idsList);
}
/// <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);
}