public bool DeleteItemDesc(string fid)
{
string sql = "delete from t_ItemPropDesc where fid=" + fid;
object o = new DataBaseLayer(_Loginer.DBName).ExecuteSql(sql);
return int.Parse(o.ToString()) > 0;
}
/// <summary>
/// 根据表名获取该表的SQL命令生成器
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
//protected override IGenerateSqlCommand CreateSqlGenerator(string tableName)
//{
// Type ORM = null;
// if (tableName == tb_sys_AttachFile.__TableName) ORM = typeof(tb_sys_AttachFile);
// if (ORM == null) throw new Exception(tableName + "表没有ORM模型！");
// return new GenerateSqlCmdByTableFields(ORM, _Loginer.DbType);
//}
/// <summary>
/// 获取指定单据的附件数据
/// </summary>
/// <param name="docID">单据号码</param>
/// <returns></returns>
public DataTable GetAttachFileData(string docID)
{
string sql = "select * from tb_AttachFile where FDocID='" + docID + "'";
DataTable dt = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataTable(sql);
return dt;
}
public DataTable GetDBLinkByID(string key)
{
string sql = "SELECT d.*, t.FName DbType, u.FUserName FROM sys_DbLink d INNER JOIN sys_DbType t ON d.FDataType = t.FID INNER JOIN sys_User u ON d.FUserID = u.FID where d.fid='" + key + "' order by d.fNumber";
DataTable dt = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataTable(sql);
dt.TableName = tb_sys_DbLink.__TableName;
return dt;
}
/// <summary>
/// 检查用户组是否存在
/// </summary>
/// <param name="groupCode">用户组编号</param>
/// <returns></returns>
public bool CheckNoExists(string groupCode)
{
string sql = "select count(*) from sys_UserGroup where FNumber='" + groupCode + "'";
object o = new DataBaseLayer(_Loginer.DBName).ExecuteSql(sql);
return int.Parse(o.ToString()) > 0;
}
/// <summary>
/// 启用事务控制
/// </summary>
protected virtual void BeginTransaction()
{
IDbConnection conn = new DataBaseLayer(_Loginer.DBName).GetConnection(); //DataProvider.Instance.CreateConnection(_Loginer.DBName);
if (conn.State == ConnectionState.Closed)
conn.Open();
_CurrentTrans = conn.BeginTransaction();
}
//<summary>
//在同一事务内生成单号
//</summary>
//<param name="tran">当前事务</param>
//<param name="DocNoName">单据名称</param>
//<returns></returns>
public static string GetNumber(string sDBName, string Tablename,bool IsUp)
{
string strNo = "";
string strNy = DateTime.Now.ToString("yy") + DateTime.Now.ToString("MM");
string sHead = "";
string sfid="";
string sql = "select * from sys_FBillSN where FTableName ='" + Tablename + "' and FYYMM='" + strNy + "'";
DataTable dt = new DataBaseLayer(sDBName).ExecuteQueryDataTable(sql);
if (dt.Rows.Count == 0)
{
sql = "select FHeader from sys_FBillSN where fid in (select max(fid) from sys_FBillSN where FTableName ='" + Tablename + "')";
sHead = new DataBaseLayer(sDBName).GetSingle(sql).ToString();
strNo = sHead + strNy + "1";
sfid=GetTableID(sDBName,"sys_FBillSN","FID");
sql = "inert into sys_FBillSN(Fid, FTableName, FHeader, FYYMM, FMaxID, FIsLock) values(" + sfid + ",'" + Tablename + "','" + sHead + "','" + strNy + "',2,0)";
if (IsUp)
new DataBaseLayer(sDBName).ExecuteSql(sql);
}
if (dt.Rows.Count == 1)
{
sHead = dt.Rows[0]["FHeader"].ToString();
strNo = sHead + strNy + dt.Rows[0]["FMaxID"].ToString();
sql = "update sys_FBillSN set FMaxID=FMaxID+1 where FTableName ='" + Tablename + "' and FYYMM='" + strNy + "'";
if(IsUp)
new DataBaseLayer(sDBName).ExecuteSql(sql);
}
return strNo;
}
public System.Data.DataTable GetItemClass(string FNumber)
{
string sql = "select t.*,u.FUserName from t_ItemClass t inner join sys_user u on t.FUserID=u.fid where t.FNumber='" + FNumber + "' order by t.fnumber ";
DataTable dt = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataTable(sql);
dt.TableName = tb_t_ItemClass.__TableName;
return dt;
}
public System.Data.DataSet GetItemDesc(string FNumber)
{
ArrayList sql = new ArrayList();
sql.Add("select t.*,u.FUserName from t_ItemClass t inner join sys_user u on t.FUserID=u.fid where t.FNumber='" + FNumber + "' order by t.fnumber ");
sql.Add("SELECT D.*, C.FNumber ClassNum, C.FName ClassName FROM t_ItemPropDesc D INNER JOIN t_ItemClass C ON D.FItemClassID = C.FID where d.FItemClassID in (select fid from t_ItemClass where FNumber='" + FNumber + "'");
DataSet ds = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataSet(sql);
ds.Tables[0].TableName = tb_t_ItemClass.__TableName;
ds.Tables[1].TableName = tb_t_ItemPropDesc.__TableName;
return ds;
}
public System.Data.DataSet GetSystemProfile()
{
ArrayList arrSqlList = new ArrayList();
arrSqlList.Add("select distinct FCategory from sys_SystemProfile order by FCategory ");
arrSqlList.Add("select FID, FCategory, Fkey, FValue, FReadonly, FDescription, FLevel, FExplanation, FType, FSort,case when FType=2 then case when FValue='0' then '否' else '是' end else FValue end FSValue,0 IsUp from sys_SystemProfile order by FSort ");
DataSet ds = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataSet(arrSqlList);
ds.Tables[1].TableName = tb_sys_SystemProfile.__TableName;
return ds;
}
/// <summary>
/// 获取报表数据
/// </summary>
/// <param name="sReportName">报表名称</param>
/// <param name="sFilter">过滤条件</param>
/// <param name="bIsGetCount">是否获取记录总数</param>
/// <param name="nPageIndex">当前页数</param>
/// <returns></returns>
public DataTable GetRportData(string sReportName, string sFilter, bool bIsGetCount, int nPageIndex)
{
DataTable dtResult = new DataTable();
string sql="";
if (_Loginer.DbType == DbAcessTyp.SQLServer)
sql = string.Format(@"select isnull(FSQL,'') as FSQL,FIsPage,isnull(FPageSize,0) as FPageSize from Sys_Fun_SQL where FName='{0}'", sReportName);
else if(_Loginer.DbType == DbAcessTyp.Oracle)
sql = string.Format(@"select nvl(FSQL,'') as FSQL,FIsPage,nvl(FPageSize,0) as FPageSize from Sys_Fun_SQL where FName='{0}'", sReportName);
DataTable dtSql = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataTable(sql);
if(dtSql.Rows.Count>0)
{
bool bIsPagination = dtSql.Rows[0]["FIsPage"].ToString() == "0" ? false : true;
int nPageSize = Convert.ToInt32(dtSql.Rows[0]["FPageSize"]);
string sExeSql="";
string sRepSql = dtSql.Rows[0]["FSQL"].ToString();
if (sFilter.Trim() != "")
sFilter = " where 1=1 and " + sFilter;
if (_Loginer.DbType == DbAcessTyp.SQLServer)
sExeSql = "select 0 as val_selected,Row_Number() over (order by getdate()) as val_keyid,aa.* from(" + sRepSql + ") aa" + sFilter;
else if (_Loginer.DbType == DbAcessTyp.Oracle)
sExeSql = "select 0 as val_selected,Row_Number() over (order by sysdate) as val_keyid,aa.* from(" + sRepSql + ") aa" + sFilter;
if (bIsPagination) //启用分页
{
if (bIsGetCount) //是获取记录总数
{
if (_Loginer.DbType == DbAcessTyp.SQLServer)
sExeSql = "select isnull(COUNT(1),0) as recordercount,isnull(CEILING((COUNT(1)+0.0)/" + nPageSize + "),0) as totalpagecount," + nPageSize + " as FPageSize from (" + sExeSql + ") cc ";
else if (_Loginer.DbType == DbAcessTyp.Oracle)
sExeSql = "select nvl(COUNT(1),0) as recordercount,nvl(ceil((COUNT(1)+0.0)/" + nPageSize + "),0) as totalpagecount," + nPageSize + " as FPageSize from (" + sExeSql + ") cc ";
}
else
{
if (nPageIndex == 1) //首页
{
sExeSql = "select top " + nPageSize + " * from (" + sExeSql + ") cc";
}
else
{
sExeSql = "select top " + nPageSize + " * from (" + sExeSql + ") cc WHERE val_keyid > " + (nPageIndex - 1) * nPageSize;
}
}
}
if (!bIsGetCount) //不是获取记录总数
{
//if (sOrder.IndexOf("Order by") > 0)
// sExeSql += sOrder;
}
dtResult = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataTable(sExeSql);
}
return dtResult;
}
/// <summary>
/// 查询日志数据 第二个参数为查询条件
/// </summary>
/// <param name="logUser"></param>
/// <param name="sFilter"></param>
/// <returns></returns>
public static DataSet GetLog(Loginer logUser,string sFilter)
{
DataSet ds;
string sql = "SELECT l.FID, l.FDate, u.FAccount, u.FUserName, f.FNumber AS FunNum, f.FName AS FunName, fs.FNumber AS FsubNum, fs.FName AS FsubName, l.FDescription, " +
" l.FMachineName, l.FIPAddress, l.FSQL " +
" FROM sys_log AS l INNER JOIN " +
" sys_User AS u ON l.FUserID = u.FID LEFT OUTER JOIN " +
" sys_Function AS f ON l.FFunctionID = f.FID LEFT OUTER JOIN " +
" sys_Fun_MenuBar AS fs ON l.FSubFunctionID = fs.FID where 1=1 " + sFilter;
ds = new DataBaseLayer(logUser.DBName).Query(sql);
return ds;
}
/// <summary>
/// 获取编号
/// </summary>
/// <param name="tran"></param>
/// <param name="dataCode"></param>
/// <param name="asHeader"></param>
/// <returns></returns>
public static string GetDataSN(string sDBName,string Tablename, bool asHeader, bool IsUp)
{
string strNo = "";
string sHead = "";
string sql = "select * from sys_DataSN where FTableName ='" + Tablename + "'";
DataTable dt = new DataBaseLayer(sDBName).ExecuteQueryDataTable(sql);
if (dt.Rows.Count == 1)
{
sHead = dt.Rows[0]["FHeader"].ToString();
strNo = sHead +Convert.ToInt32(dt.Rows[0]["FMaxID"]).ToString("d" + dt.Rows[0]["FLength"].ToString());
sql = "update sys_DataSN set FMaxID=FMaxID+1 where FTableName ='" + Tablename + "'";
if (IsUp)
new DataBaseLayer(sDBName).ExecuteSql(sql);
}
return strNo;
}
public static DataBaseLayer GetDBLayer()
{
if (dbLayer == null)
{
#if (Debug || RegistryConnection)
dbLayer = GetRegistryDBLayer();
#else
if (LicenseServer == null)
{
dbLayer = GetLocalDBLayer();
}
else
{
dbLayer = GetLicenseDBLayer();
}
#endif
}
return(dbLayer);
}
public static String AddReconciliation(string Clearing, string ReconciliaT, string ReconciliaPer, string Remarks
, string Pspnum, string hospitalSelect1, string STime, string ETime)
{
string[] arr = null;
RecipeModel rm = new RecipeModel();
DataTable sdr1 = rm.AccountStatementInfo(Pspnum, STime, ETime, Convert.ToInt32(hospitalSelect1));
arr = new string[sdr1.Rows.Count];
for (var i = 0; i < sdr1.Rows.Count; i++)
{
arr[i] = sdr1.Rows[i]["id"].ToString();
}
int sdr = 0;
// string[] strRows1Id = strRowIDs.Split(',');
DataBaseLayer db = new DataBaseLayer();
// now：生成对账单时间
System.DateTime now = new System.DateTime();
now = System.DateTime.Now;
string n = now.ToString();
// ReconciliaT:对账时间
string current = now.ToString("yyyyMMddhhmmss");
string str = "select id from Clearingparty where ClearPName = '" + Clearing + "'";
SqlDataReader sr = db.get_Reader(str);
if (sr.Read())
{
for (int i = 0; i < arr.Length; i++)
{
string Retime = sr["id"].ToString() + current;
ClearingpartyHandler ch = new ClearingpartyHandler();
sdr = ch.AddReconciliation1(Clearing, ReconciliaT, ReconciliaPer, Remarks, arr[i], n, Retime);
}
}
return("");
}
public DataTable SearchHospital(string hname, string hnum)
{
string strSql = "select id,hnum,hname,Hshortname,contacter,phone,address,pricetype,settler from hospital where 1=1 ";
if (hnum != "0" && hnum != "")
{
strSql += "and hnum ='" + hnum + "'";
}
if (hname != "0" && hname != "")
{
strSql += "and id ='" + hname + "'";
}
DataBaseLayer db = new DataBaseLayer();
DataTable dt = db.get_DataTable(strSql);
return(dt);
}
public bool UpdateSalaryDetail(SalaryDetailUpdateDTO salary)
{
bool res = false;
SqlCommand SqlCmd = new SqlCommand("");
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlCmd.Parameters.AddWithValue("@Id", salary.EmployeeId);
SqlCmd.Parameters.AddWithValue("@SalaryCompensate", salary.SalaryCompensate);
SqlCmd.Parameters.AddWithValue("@Amount", salary.Amount);
SqlCmd.Parameters.AddWithValue("@ModifiedBy", salary.ModifiedBy);
SqlCmd.Parameters.AddWithValue("@Active", salary.Active);
int result = new DataBaseLayer().ExecuteNonQuery(SqlCmd);
if (result != Int32.MaxValue)
{
res = true;
}
return(res);
}
public static DataBaseLayer GetDBLayer()
{
if (dbLayer == null)
{
#if (Debug || RegistryConnection)
dbLayer = GetRegistryDBLayer();
#else
if (LicenseServer == null)
{
dbLayer = GetLocalDBLayer();
}
else
{
dbLayer = GetLicenseDBLayer();
}
#endif
}
return dbLayer;
}
public bool DrugGlobalInfo(DrugGlobalInfoGet drug)
{
/// <summary>
/// 添加泡药信息
/// </summary>
/// <param name="einfo"></param>
/// <returns></returns>
string strSql = "insert into drugGlobalInfo(num,delNum,hospitalNum,hospitalName,pspNum,drugTakeWay," +
"patientName,sex,age,personPhone,address,department,inpatientAreaNum,inpatientRoomNum,inpatientBedNum," +
"diagnosisResult,drugNum,drinkWay,takeNum,packageNum,drinkMethod,takeMethod,firstTakeTime," +
"secondTakeTime,soakPlusWater,soakTime,labelNum,remarkInfo,doctor,doctorFootNote,getDrugTime," +
"getDrugNum,orderTime,stateNow,operationTime,operationPerson,distributionCompany,distributionAddress," +
"companyPhone,expressType) ";
strSql += "values ('" + drug.strNum + "','" + drug.strDelNum + "','" + drug.strHospitalNum + "',";
strSql += "'" + drug.strHospitalName + "','" + drug.strPspNum + "','" + drug.strDrugTakeWay + "',";
strSql += "'" + drug.strName + "','" + drug.strSex + "','" + drug.strAge + "',";
strSql += "'" + drug.strPersonPhone + "','" + drug.strAddress + "','" + drug.strDepartment + "',";
strSql += "'" + drug.strInpatientAreaNum + "','" + drug.strInpatientRoomNum + "','" + drug.strInpatientBedNum + "',";
strSql += "'" + drug.strDiagnosisResult + "','" + drug.strDrugNum + "','" + drug.strDrinkWay + "',";
strSql += "'" + drug.strTakeNum + "','" + drug.strPackageNum + "','" + drug.strDrinkMethod + "',";
strSql += "'" + drug.strTakeMethod + "','" + drug.strFirstTakeTime + "','" + drug.strSecondTakeTime + "',";
strSql += "'" + drug.strSoakPlusWater + "','" + drug.strSoakTime + "','" + drug.strLabelNum + "',";
strSql += "'" + drug.strRemarkInfo + "','" + drug.strDoctor + "','" + drug.strDoctorFootNote + "',";
strSql += "'" + drug.strGetDrugTime + "','" + drug.strGetDrugNum + "','" + drug.strOrderTime + "',";
strSql += "'" + drug.strStateNow + "','" + drug.strOperationTime + "','" + drug.strOperationPerson + "',";
strSql += "'" + drug.strDistributionCompany + "','" + drug.strDistributionAddress + "','" + drug.strCompanyPhone + "',";
strSql += "'" + drug.strExpressType + "')";
DataBaseLayer db = new DataBaseLayer();
int n = db.cmd_Execute(strSql);
if (n > 0)
{
return(true);
}
else
{
return(false);
}
}
public DataTable DrugDisplayInfo()
{
System.DateTime currentTime = new System.DateTime();
currentTime = System.DateTime.Now;//当前时间
string strS = currentTime.ToString("yyyy/MM/dd 00:00:00");
string strS2 = currentTime.ToString("yyyy/MM/dd 23:59:59");
DataBaseLayer db = new DataBaseLayer();
// string sql = "select id , (select Pspnum from prescription as p p.id = b.pid )as Pspnum, (select getdrugnum from prescription as p p.id = b.pid )as getdrugnum, (select getdrugtime from prescription as p p.id = b.pid )as getdrugtime,bubbleperson as bp,starttime,doingtime,bubblestatus from bubble as b";
string sql = "select (select pid from bubble where pid= p.id) as ID,Pspnum,customid,delnum,(select bubblestatus from bubble where pid= p.id) as bubblestatus,(select doingtime from bubble where pid = p.id) as doingtime,(select starttime from bubble where pid = p.id) as starttime,(select warningstatus from bubble where pid = p.id) as warningstatus,(SELECT bubbleperson FROM bubble WHERE pid = p.id) as bp,(SELECT mark FROM bubble WHERE pid = p.id) as mark,(select hnum from hospital as h where h.id = p.hospitalid ) as hnum,(select hname from hospital as h where h.id = p.hospitalid ) as hname,Hospitalid,name,sex,age,phone,address,department,inpatientarea,ward,sickbed,";
sql += "diagresult,(select wateryield from bubble where pid= p.id) as wateryield,dose,takenum,getdrugtime,getdrugnum,takemethod,decscheme,oncetime,twicetime,packagenum,dotime,doperson,dtbcompany,dtbaddress,dtbphone,dtbtype,soakwater,soaktime,labelnum,remark,doctor,footnote,ordertime,curstate,RemarksA,RemarksB";
sql += " from prescription as p where id not in (select pid from InvalidPrescription) and id in (select pid from bubble ) and id not in (select pid from tisaneinfo ) and p.Hospitalid in (select id from hospital where DrugDisplayState='0') and p.dotime between '" + strS + "' and '" + strS2 + "'";
DataTable dt = db.get_DataTable(sql);
return(dt);
}
public DataTable SearchHospital(string hname, string hnum)
{
string strSql = "select h.id,hnum,h.hname,h.Hshortname,h.contacter,h.phone,h.address,h.pricetype,h.settler,t.relation_name from hospital h " +
" left join tb_hospital_drug_type t on h.relation_drug_type_id=t.relation_id where 1=1 ";
if (hnum != "0" && hnum != "")
{
strSql += "and hnum ='" + hnum + "'";
}
if (hname != "0" && hname != "")
{
strSql += "and h.id ='" + hname + "'";
}
DataBaseLayer db = new DataBaseLayer();
DataTable dt = db.get_DataTable(strSql);
return(dt);
}
/// <summary>
/// 检查用户是否存在
/// </summary>
/// <param name="userid">用户编号</param>
/// <returns></returns>
public bool ExistsUser(string account)
{
string sql = "";
IDbDataParameter[] sPara = new IDbDataParameter[1];
if (_Loginer.DbType == DbAcessTyp.SQLServer)
{
sql = "select count(*) from sys_User where FAccount=@Account";
sPara[0] = DataConverter.GetSqlPara("@Account", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
else
{
sql = "select count(*) from sys_User where FAccount=:Account";
sPara[0] = DataConverter.GetOraclePara("Account", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
sPara[0].Value = account;
object o = new DataBaseLayer(_Loginer.DBName).GetSingle(sql, sPara);
return(int.Parse(o.ToString()) > 0);
}
//大屏显示煎药信息
public string gettisaneinfo()
{
DataBaseLayer db = new DataBaseLayer();
System.DateTime currentTime = new System.DateTime();
currentTime = System.DateTime.Now;//当前时间
string strS = currentTime.ToString("yyyy/MM/dd 00:00:00");
string strS2 = currentTime.ToString("yyyy/MM/dd 23:59:59");
string str = "select count(*) as ct from prescription where dotime between '" + strS + "' and '" + strS2 + "'";
SqlDataReader sdr = db.get_Reader(str);
string ct = "";
if (sdr.Read())
{
ct = sdr["ct"].ToString();
}
string str2 = "select count(*) as ct from prescription where dotime between '" + strS + "' and '" + strS2 + "' and id in (select pid from tisaneinfo)";
SqlDataReader sdr2 = db.get_Reader(str2);
string ct2 = "";
if (sdr2.Read())
{
ct2 = sdr2["ct"].ToString();
}
string result = "";
result = "当日接单数:" + ct + " " + " 已煎药:" + ct2;
return(result);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["id"] != null)
{
int id = Convert.ToInt16(Request.QueryString["id"]);
idnum.Value = Request.QueryString["id"].ToString();
}
if (!IsPostBack)
{
TeModel tm = new TeModel();
SqlDataReader sdr = tm.findNameAll();
if (sdr != null)
{
while (sdr.Read())
{
this.tisaneperson.Items.Add(new ListItem(sdr["EmNumAName"].ToString()));
// this.SwapPer.Items.Add(new ListItem(sdr["JobNum"].ToString()));
}
}
DataBaseLayer db = new DataBaseLayer();
if (Session["userNamebar"] != null)
{
string name = Session["userNamebar"].ToString();
string sq1 = "select EmNumAName from Employee where jobnum ='" + name + "' ";
SqlDataReader sr = db.get_Reader(sq1);
if (sr.Read())
{
tisaneperson.Value = sr["EmNumAName"].ToString();
}
}
else
{
Response.Write("<script>alert('用户名已失效');window.parent.loginview();</script>");
}
}
}
public int updatePackinginfo(int id, int status, string endDate, String tisaneNum)
{
DataBaseLayer db = new DataBaseLayer();
string sql = "update packing set Fpactate='" + status + "',PacTime='" + endDate + "' where id=" + id;
int count = db.cmd_Execute(sql);
if (count > 0)
{
string sql2 = "update prescription set curstate = '包装完成' where id = '" + tisaneNum + "'";
db.cmd_Execute(sql2);
string sql3 = "INSERT INTO [Delivery](DecoctingNum,Sendstate) VALUES('" + tisaneNum + "','0')";
db.cmd_Execute(sql3);
string sqlStr = "update machine set status='空闲',pid='' where id=(SELECT c.id FROM machine AS c INNER JOIN (SELECT a.id, a.roomnum, a.mark, a.unitnum FROM machine AS a INNER JOIN (SELECT machineid FROM tisaneunit WHERE (pid = '" + tisaneNum + "')) AS b ON a.id = b.machineid) AS d ON d.roomnum = c.roomnum AND d.unitnum = c.unitnum AND c.mark = 1)";
db.cmd_Execute(sqlStr);
}
return(count);
}
/// <summary>
/// 获取编号
/// </summary>
/// <param name="tran"></param>
/// <param name="dataCode"></param>
/// <param name="asHeader"></param>
/// <returns></returns>
public static string GetDataSN(string sDBName, string Tablename, bool asHeader, bool IsUp)
{
string strNo = "";
string sHead = "";
string sql = "select * from sys_DataSN where FTableName ='" + Tablename + "'";
DataTable dt = new DataBaseLayer(sDBName).ExecuteQueryDataTable(sql);
if (dt.Rows.Count == 1)
{
sHead = dt.Rows[0]["FHeader"].ToString();
strNo = sHead + Convert.ToInt32(dt.Rows[0]["FMaxID"]).ToString("d" + dt.Rows[0]["FLength"].ToString());
sql = "update sys_DataSN set FMaxID=FMaxID+1 where FTableName ='" + Tablename + "'";
if (IsUp)
{
new DataBaseLayer(sDBName).ExecuteSql(sql);
}
}
return(strNo);
}
public DataTable MedicineDisplayInfo()
{
System.DateTime currentTime = new System.DateTime();
currentTime = System.DateTime.Now;//当前时间
string strS = currentTime.ToString("yyyy/MM/dd 00:00:00");
string strS2 = currentTime.ToString("yyyy/MM/dd 23:59:59");
DataBaseLayer db = new DataBaseLayer();
string sql = "select d.DecoctingNum , d.ID,(select PacTime from Packing as i where i.DecoctingNum = p.id ) as StartTime,d.Sendpersonnel ,d.warningstatus,d.SendTime,d.Sendstate,d.Starttime,d.Remarks, p.customid,p.delnum,(select hnum from hospital as h where h.id = p.hospitalid ) as hnum,(select hname from hospital as h where h.id = p.hospitalid) as hname,p.Hospitalid,p.name,p.sex,p.age,p.phone,p.address,p.department,p.inpatientarea,p.ward,p.sickbed,";
sql += "p.diagresult,p.Pspnum,(select machinename from machine where id = (select machineid from tisaneinfo where pid = p.id )) as machineid,p.dose,p.takenum,p.getdrugtime,p.getdrugnum,p.takemethod,p.decscheme,p.oncetime,p.RemarksA,p.RemarksB,p.twicetime,p.packagenum,p.dotime,p.doperson,p.dtbcompany,p.dtbaddress,p.dtbphone,p.dtbtype,p.soakwater,p.soaktime,p.labelnum,p.remark,p.doctor,p.footnote,p.ordertime,p.curstate";
sql += " from prescription as p join Delivery as d on p.id =d.DecoctingNum where d.DecoctingNum not in (select pid from InvalidPrescription) and d.Sendstate ='0' and p.hospitalid in (select id from hospital where DrugSendDisplayState='0') and p.dotime between '" + strS + "' and '" + strS2 + "' order by d.DecoctingNum desc";
DataTable dt = db.get_DataTable(sql);
return(dt);
}
/// <summary>
/// 查询发货信息
/// </summary>
/// <param > Sendstate, SendTime, Sendpersonnel</param>
/// <returns>dt</returns>
public DataTable findDeliveryInfofy(string Sendstate, string SendTime, string Sendpersonnel, string Hospitalid, string GetDrugTime, int ts, int page)
{
DataBaseLayer db = new DataBaseLayer();
System.DateTime currentTime = new System.DateTime();
currentTime = System.DateTime.Now;//获取当前时间
string sql = "select top " + ts + " * from ( select *,row_number() over(order by t.id desc ) as rownumber from ( select d.DecoctingNum , d.ID,d.Sendpersonnel ,d.warningstatus,d.SendTime,d.Sendstate,d.Remarks,(select hnum from hospital as h where h.id = p.hospitalid) as hnum,(select hname from hospital as h where h.id = p.hospitalid) as hname,";
sql += "p.Pspnum,p.name,(select machinename from machine where id = (select top 1 machineid from tisaneinfo where pid = p.id )) as machineid,p.dose,p.takenum,p.packagenum,p.dtbaddress,p.getdrugtime,p.getdrugnum";
sql += " from prescription as p left join Delivery as d on p.id =d.DecoctingNum left join packing pa on p.id =pa.DecoctingNum where 1=1 ";
if (Sendstate != "")
{
sql += " and d.Sendstate ='" + Sendstate + "'";
}
if (SendTime != "0")
{
sql += "and Convert(varchar,SendTime ,120) like '" + SendTime + "%'";
}
if (Sendpersonnel != "0")
{
sql += "and Sendpersonnel='" + Sendpersonnel + "'";
}
if (Hospitalid != "0")
{
sql += "and p.hospitalid='" + Hospitalid + "'";
}
if (GetDrugTime != "0")
{
sql += "and Convert(varchar,p.getdrugtime ,120) like '" + GetDrugTime + "%'";
}
sql += ") t ) p where p.rownumber>" + (page - 1) * ts;
sql += " order by p.DecoctingNum desc";
DataTable dt = db.get_DataTable(sql);
return(dt);
}
public bool DrugDelete()
{
/// <summary>
/// 删除泡药信息
/// </summary>
/// <param name="einfo"></param>
/// <returns></returns>
string strSql = "delete from drugGlobalInfo where num = @num";
DataBaseLayer db = new DataBaseLayer();
int n = db.cmd_Execute(strSql);
if (n > 0)
{
return(true);
}
else
{
return(false);
}
}
public int AddClearingparty(string ClearPName, string ConPerson, string Address, string ConPhone, string Remarks, string GenDecoct)
{
/// <summary>
/// 添加结算方信息
/// </summary>
/// <param name="einfo"></param>
/// <returns></returns>
int end = 0;
DataBaseLayer db = new DataBaseLayer();
string str = "select ClearPName from Clearingparty where ClearPName = '" + ClearPName + "'";
SqlDataReader sr = db.get_Reader(str);
if (sr.Read())
{
end = 0;
}
else
{
string str1 = "select ClearPName from Clearingparty where ConPerson = '" + ConPerson + "'";
SqlDataReader sr1 = db.get_Reader(str1);
if (sr1.Read())
{
end = 0;
}
else
{
string strSql = "insert into Clearingparty(ClearPName,ConPerson,ConPhone,Address,Remarks,GenDecoct) ";
strSql += "values ('" + ClearPName + "','" + ConPerson + "','" + ConPhone + "',";
strSql += "'" + Address + "','" + Remarks + "','" + GenDecoct + "')";
end = db.cmd_Execute(strSql);
}
}
return(end);
}
/// <summary>
/// 查询发货信息
/// </summary>
/// <param > Sendstate, SendTime, Sendpersonnel</param>
/// <returns>dt</returns>
public DataTable findDeliveryInfo(string Sendstate, string SendTime, string Sendpersonnel, string Hospitalid, string GetDrugTime)
{
DataBaseLayer db = new DataBaseLayer();
System.DateTime currentTime = new System.DateTime();
currentTime = System.DateTime.Now;//获取当前时间
string sql = "select d.DecoctingNum , d.ID,d.Sendpersonnel ,d.warningstatus,d.SendTime,d.Sendstate,d.Remarks,(select hnum from hospital as h where h.id = p.hospitalid) as hnum,(select hname from hospital as h where h.id = p.hospitalid) as hname,";
sql += "p.Pspnum,p.name,(select machinename from machine where id = (select machineid from tisaneinfo where pid = p.id )) as machineid,p.dose,p.takenum,p.packagenum,p.dtbaddress,p.getdrugtime,p.getdrugnum";
sql += " from prescription as p join Delivery as d on p.id =d.DecoctingNum where d.DecoctingNum not in (select pid from InvalidPrescription) ";
if (Sendstate != "")
{
sql += " and Sendstate ='" + Sendstate + "'";
}
if (SendTime != "0")
{
sql += "and Convert(varchar,SendTime ,120) like '" + SendTime + "%'";
}
if (Sendpersonnel != "0")
{
sql += "and Sendpersonnel='" + Sendpersonnel + "'";
}
if (Hospitalid != "0")
{
sql += "and p.hospitalid='" + Hospitalid + "'";
}
if (GetDrugTime != "0")
{
sql += "and Convert(varchar,p.getdrugtime ,120) like '" + GetDrugTime + "%'";
}
sql += " order by d.DecoctingNum desc";
DataTable dt = db.get_DataTable(sql);
return(dt);
}
public DataTable SearchInfoabc(string[] aPid)
{
DataBaseLayer db = new DataBaseLayer();
string strSql = "select r.id , r.Clearing,r.CheckNum,r.ReconciliaPer,r.ReconciliaT,r.now,r.State,r.Remarks,p.dotime,(select distinct hname from hospital as h where h.id = p.hospitalid and h.id in (select hospitalid from prescription where p.id = r.pid )) as hname,p.Pspnum,p.decscheme,p.name,p.dose,p.takenum,p.packagenum, (select count(pid) from drug as s where s.pid =p.id and s.pid=r.pid ) as DrugAcount"
+ " from prescription as p join Reconciliation as r on p.id = r.pid where 1=1";
/* for (int i = 0; i < aPid.Length; i++)
* {
* strSql += " or r.pid = " + aPid[i] + "";
*
* }*/
if (aPid.Length == 1)
{
strSql += " and r.pid = " + aPid[0] + "";
}
else
{
for (int i = 0; i < aPid.Length; i++)
{
if (i == 0)
{
strSql += " and r.pid in(" + aPid[i] + ",";
}
else if (i == aPid.Length - 1)
{
strSql += aPid[i] + ")";
}
else
{
strSql += aPid[i] + ",";
}
}
}
DataTable dt = db.get_DataTable(strSql);
return(dt);
}
/// <summary>
/// 检查数据是否存在
/// </summary>
/// <param name="keyValue">主键</param>
/// <returns></returns>
public virtual bool CheckNoExists(string keyValue)
{
string sql = "";
IDbDataParameter[] sPara = new IDbDataParameter[1];
if (_Loginer.DbType == DbAcessTyp.SQLServer)
{
sql = string.Format("SELECT COUNT(*) C FROM {0} WHERE {1}=@KEY", _TableName, _KeyName);
sPara[0] = DataConverter.GetSqlPara("@KEY", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
else
{
sql = string.Format("SELECT COUNT(*) C FROM {0} WHERE {1}=:KEY", _TableName, _KeyName);
sPara[0] = DataConverter.GetOraclePara(":KEY", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
sPara[0].Value = keyValue;
//SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
//cmd.AddParam("@KEY", SqlDbType.VarChar, keyValue);
//object o = DataProvider.Instance.ExecuteScalar(_Loginer.DBName, cmd.SqlCommand);
object o = new DataBaseLayer(_Loginer.DBName).GetSingle(sql, sPara);
return(ConvertEx.ToInt(o) > 0);
}
/// <summary>
/// 调剂大屏显示
/// </summary>
/// <returns></returns>
public DataTable AdjustMonitors()
{
System.DateTime currentTime = new System.DateTime();
currentTime = System.DateTime.Now;//当前时间
string strS = currentTime.ToString("yyyy/MM/dd 00:00:00");
string strS2 = currentTime.ToString("yyyy/MM/dd 23:59:59");
DataBaseLayer db = new DataBaseLayer();
string sql = @"select p.id, Pspnum,customid,delnum,h.Hnum ,h.Hname,Hospitalid,a.SwapPer,a.wordDate,a.endDate,a.status,p.name,sex,age,department,inpatientarea,ward,sickbed,
diagresult,takenum,getdrugtime,getdrugnum,decscheme,dose,packagenum,dotime,doperson,dtbcompany,dtbaddress,dtbphone,dtbtype,soakwater,soaktime,labelnum,remark,
ordertime,curstate,RemarksA,RemarksB
from prescription as p
right join adjust as a on p.ID=a.prescriptionId
left join Hospital as h on h.ID=p.Hospitalid and h.DrugSendDisplayState='0'
where p.ID in (select prescriptionId from adjust )
and p.ID not in (select pid from Audit ) and p.dotime between '" + strS + "' and '" + strS2 + "'";
DataTable dt = db.get_DataTable(sql);
return(dt);
}
/// <summary>
/// 删除一条记录
/// </summary>
/// <param name="keyValue">主键</param>
/// <returns></returns>
public virtual bool Delete(string keyValue)
{
string sql = "";
//SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
//cmd.AddParam("@KEY", SqlDbType.VarChar, keyValue);
IDbDataParameter[] sPara = new IDbDataParameter[1];
if (_Loginer.DbType == DbAcessTyp.SQLServer)
{
sql = string.Format("Delete {0} where {1}=@KEY", _TableName, _KeyName);
sPara[0] = DataConverter.GetSqlPara("@KEY", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
else
{
sql = string.Format("Delete {0} where {1}=:KEY", _TableName, _KeyName);
sPara[0] = DataConverter.GetOraclePara(":KEY", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
sPara[0].Value = keyValue;
int i = new DataBaseLayer(_Loginer.DBName).ExecuteSql(sql, sPara); //DataProvider.Instance.ExecuteNoQuery(_Loginer.DBName, cmd.SqlCommand);
return(i != 0);
}
/// <summary>
/// 获取帐套库的数据库写入HasTable
/// </summary>
public static void GetDbAccount()
{
if (SG.Parameters.SGParameter.hAccountConn != null)
SG.Parameters.SGParameter.hAccountConn.Clear();
else
SG.Parameters.SGParameter.hAccountConn = new System.Collections.Hashtable();
string dbType = DbConfiguration_Factory.DbType;
string sConn = DbConfiguration_Factory.GetAccountConnString();
string sSQL = "SELECT T_Account.FID, T_Account.FNumber, T_Account.FName, T_Account.FDatabase, T_Account.FServerName, T_Account.FUser, T_Account.FPwd, T_Account.FDataType, " +
" T_Account.FOrgID, T_Account.FCreateDate, T_Account.FBackUpDate, T_Account.FVer, T_Account.FProductName, T_Account.FOnlineCount, T_Account.FServerIP, T_DbType.FSign " +
" FROM T_Account INNER JOIN T_DbType ON T_Account.FID = T_DbType.FID ";
DataTable sAccount = new DataBaseLayer(sConn, dbType).ExecuteQueryDataTable(sSQL);
foreach(DataRow myRow in sAccount.Rows)
{
SG.Parameters.SGParameter.sAccountConn sAC = new Parameters.SGParameter.sAccountConn();
sAC.sServer = myRow["FServerName"].ToString();
sAC.sDatabase = myRow["FDatabase"].ToString();
sAC.sUser = myRow["FUser"].ToString();
sAC.sPwd = myRow["FPwd"].ToString();
sAC.sDbType = myRow["FSign"].ToString();
SG.Parameters.SGParameter.hAccountConn.Add(myRow["FNumber"].ToString(), sAC);
}
}
public bool TestConnection()
{
try
{
string dbType = DbConfiguration_Factory.DbType;
string sConn = DbConfiguration_Factory.GetAccountConnString();
string sSQL = "SELECT T_Account.FID, T_Account.FNumber, T_Account.FName, T_Account.FDatabase, T_Account.FServerName, T_Account.FUser, T_Account.FPwd, T_Account.FDataType, T_Account.FOrgID, T_Account.FCreateDate, T_Account.FBackUpDate, T_Account.FVer, T_Account.FProductName, T_Account.FOnlineCount, T_Account.FServerIP,T_DbType.FSign " +
" FROM T_Account inner join T_DbType ON T_Account.FID = T_DbType.FID ";
DataTable dt = new DataBaseLayer(sConn, dbType).ExecuteQueryDataTable(sSQL);
if (dt == null)
{
return(false);
}
else
{
return(true);
}
}
catch
{
return(false);
}
}
public string getTimeoutNumber(string date)
{
DataBaseLayer db = new DataBaseLayer();
// string sql = "select id , (select Pspnum from prescription as p p.id = b.pid )as Pspnum, (select getdrugnum from prescription as p p.id = b.pid )as getdrugnum, (select getdrugtime from prescription as p p.id = b.pid )as getdrugtime,bubbleperson as bp,starttime,doingtime,bubblestatus from bubble as b";
string sql = "SELECT p.ID FROM prescription AS p INNER JOIN bubble AS b ON p.ID = b.pid AND DATEDIFF(minute, b.starttime, GETDATE()) > p.soaktime WHERE (b.bubblestatus = 0) AND (b.bubblestatus = 0) AND (CONVERT(varchar, p.dotime, 120) LIKE '%" + date + "%') ORDER BY DATEDIFF(minute, b.starttime, GETDATE()) DESC";
SqlDataReader sr = db.get_Reader(sql);
string str = "";
int index = 0;
while (sr.Read())
{
str += sr["ID"].ToString() + ",";
index++;
if (index == 3)
{
break;
}
}
return(str);
}
//开始包装指令 重写
public static void Insert_PackCmd_override(string bmNum)
{
DataBaseLayer db = new DataBaseLayer();
//开始包装指令
string strtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");//
string sql12 = "select macaddress from machine where mark=1 and unitnum = (select top 1 unitnum from machine where id =(select top 1 machineid from tisaneunit where pid ='" + bmNum + "'))";
db.write_log_txt("重发包装指令：" + sql12);
SqlDataReader sr12 = db.get_Reader(sql12);
string mac = "";
if (sr12.Read())
{
mac = sr12["macaddress"].ToString();
}
string sql10 = "select *, RIGHT(CAST('000000000' + RTRIM(id) AS varchar(20)), 10) as bNum from prescription where id = '" + bmNum + "'";
SqlDataReader sr10 = db.get_Reader(sql10);
string content = "";
if (sr10.Read())
{
string sql = "select package_machine_nums from tb_sys_add_setting";
SqlDataReader pack = db.get_Reader(sql);
int PackNum = 2;
if (pack.Read())
{
PackNum = Convert.ToInt32(pack["package_machine_nums"].ToString());
}
content = (Convert.ToInt32(sr10["dose"].ToString()) * Convert.ToInt32(sr10["takenum"].ToString()) + PackNum).ToString().PadLeft(2, '0') + bmNum.PadLeft(10, '0') + sr10["packagenum"].ToString().PadLeft(4, '0');
}
sr10.Close();
string sql11 = "insert into cmdtable(cmd,bmip,time) values('" + content + "','" + mac + "','" + strtime + "');";
db.cmd_Execute(sql11);
}
public DataTable employeelimits(string Ename)
{
//ArrayList list = new ArrayList();
DataBaseLayer db = new DataBaseLayer();
// string role ="";
// string str = "select Role from Employee where EName ='" + Ename+ "' ";
// SqlDataReader sdr = db.get_Reader(str);
// if(sdr.Read()){
// while (sdr.Read())
// {
// list.Add(sdr["Role"].ToString());
// }
// }
string strSql = "";
/**/
if (Ename != "0")
{
strSql = "select * from limitsauthority where role in (select Role from Employee where EName ='" + Ename + "')";
}
else
{
strSql = "select * from limitsauthority";
}
// strSql = "select * from limitsauthority";
// for (int i = 0; i < list.Count; i++)
// {
// strSql += "";
//}
DataTable dt = db.get_DataTable(strSql);
return(dt);
}
public string GetMonitorTimeList()
{
try
{
using (DataBaseLayer dataBaseLayer = new DataBaseLayer())
{
DataTable dt = new DataTable();
StringBuilder strBuilder = new StringBuilder("select distinct DT from ZT");
strBuilder.Append("").Append(" t where t.dt like to_date('");
strBuilder.Append("2016-4-23");
strBuilder.Append("','yyyy-mm-dd')and t.id=").Append("0136").Append(" order by DT desc");
//'2016/4/2 1:00:00','yyyy-mm-dd hh24:mi:ss') and t.id=:buoyId
dt = dataBaseLayer.ExecuteQuery(strBuilder.ToString());
if (dt.Rows.Count > 0)
{
List <string> strList = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
strList.Add(Convert.ToDateTime(dt.Rows[i][0].ToString()).ToLongTimeString());
}
return(JsonConvert.SerializeObject(strList));
}
}
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(USER_BN), "GetMonitorTimeList()当天获得实时数据的时间序列 程序段的异常" + ex);
return("");
}
finally
{
}
return("");
}
public void DeleteSomeRows_ValidParameter_DeleteCallTriggered()
{
// arrange
var cut = new DataBaseLayer();
var myparameterList = new List <object>();
var dbCommandMock = CreateCommandMock(myparameterList);
var paramValue = "myTestString";
// act: here the dependency injection happens (its called "method injection")
cut.DeleteSomeRows_Refactored(dbCommandMock.Object, paramValue);
// assert that parameters were assigned correctly
var myExpectedParamName = GetParamNameFromQuery(dbCommandMock.Object.CommandText);
var myExpectedParamValue = paramValue;
var myActualParamName = ((IDbDataParameter)myparameterList[0]).ParameterName;
var myActualParamValue = ((IDbDataParameter)myparameterList[0]).Value;
Assert.AreEqual(myExpectedParamName, myActualParamName);
Assert.AreEqual(myExpectedParamValue, myActualParamValue);
// assert that query was executed
dbCommandMock.Verify(foo => foo.ExecuteNonQuery(), Times.Exactly(1));
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ReviewAddInfo rinfo = new ReviewAddInfo();
SqlDataReader sdr = rinfo.findNameAll();
if (sdr != null)
{
while (sdr.Read())
{
this.ReviewPer.Items.Add(new ListItem(sdr["EmNumAName"].ToString()));
// this.SwapPer.Items.Add(new ListItem(sdr["JobNum"].ToString()));
}
}
DataBaseLayer db = new DataBaseLayer();
if (Session["userNamebar"] != null)
{
string name = Session["userNamebar"].ToString();
string sq1 = "select EmNumAName from Employee where jobnum ='" + name + "' ";
SqlDataReader sr = db.get_Reader(sq1);
if (sr.Read())
{
ReviewPer.Value = sr["EmNumAName"].ToString();
}
}
else
{
Response.Write("<script>alert('用户名已失效');window.parent.loginview();</script>");
}
}
}
public bool IsExistsItemClass(string FNumber)
{
string sql = "select count(*) from t_ItemClass where FNumber='" + FNumber + "'";
object x = new DataBaseLayer(_Loginer.DBName).ExecuteSql(sql);
return int.Parse(x.ToString()) > 0;
}
/// <summary>
/// 检查数据是否存在
/// </summary>
/// <param name="keyValue">主键</param>
/// <returns></returns>
public virtual bool CheckNoExists(string keyValue)
{
string sql = "";
IDbDataParameter[] sPara = new IDbDataParameter[1];
if (_Loginer.DbType == DbAcessTyp.SQLServer)
{
sql = string.Format("SELECT COUNT(*) C FROM {0} WHERE {1}=@KEY", _TableName, _KeyName);
sPara[0] = DataConverter.GetSqlPara("@KEY", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
else
{
sql = string.Format("SELECT COUNT(*) C FROM {0} WHERE {1}=:KEY", _TableName, _KeyName);
sPara[0] = DataConverter.GetOraclePara(":KEY", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
sPara[0].Value = keyValue;
//SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
//cmd.AddParam("@KEY", SqlDbType.VarChar, keyValue);
//object o = DataProvider.Instance.ExecuteScalar(_Loginer.DBName, cmd.SqlCommand);
object o = new DataBaseLayer(_Loginer.DBName).GetSingle(sql, sPara);
return ConvertEx.ToInt(o) > 0;
}
/// <summary>
/// 删除一条记录
/// </summary>
/// <param name="keyValue">主键</param>
/// <returns></returns>
public virtual bool Delete(string keyValue)
{
string sql = "";
//SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
//cmd.AddParam("@KEY", SqlDbType.VarChar, keyValue);
IDbDataParameter[] sPara = new IDbDataParameter[1];
if (_Loginer.DbType == DbAcessTyp.SQLServer)
{
sql = string.Format("Delete {0} where {1}=@KEY", _TableName, _KeyName);
sPara[0] = DataConverter.GetSqlPara("@KEY", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
else
{
sql = string.Format("Delete {0} where {1}=:KEY", _TableName, _KeyName);
sPara[0] = DataConverter.GetOraclePara(":KEY", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
sPara[0].Value = keyValue;
int i = new DataBaseLayer(_Loginer.DBName).ExecuteSql(sql, sPara); //DataProvider.Instance.ExecuteNoQuery(_Loginer.DBName, cmd.SqlCommand);
return i != 0;
}
/// <summary>
/// 获取主表数据
/// </summary>
/// <returns></returns>
public virtual DataTable GetSummaryData()
{
this.AssertTableName();
string sql = string.Format("SELECT * FROM {0}", _TableName);
DataTable RTatatable=new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataTable(sql);
RTatatable.TableName = _TableName;
return RTatatable;
}
/// <summary>
/// 获取用户数据
/// </summary>
/// <param name="account">帐号</param>
/// <returns></returns>
public DataTable Getb_sys_User(string account)
{
string sql = "select * from sys_User where FAccount='" + account + "'";
DataTable dt = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataTable(sql); //DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_sys_User.__TableName);
return dt;
}
/// <summary>
/// 获取用户所属组
/// </summary>
/// <param name="account">当前用户</param>
/// <returns></returns>
public DataTable Getb_sys_UserGroups(string account)
{
string sql = "SELECT g.FID, g.FNumber, g.FName, g.FNote FROM sys_User_Group ug INNER JOIN sys_UserGroup g ON ug.FUserGroupID = g.FID INNER JOIN sys_User u ON ug.FUserID = u.FID WHERE (u.FAccount = '" + account + "')";
DataTable dt = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataTable(sql); //DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_sys_UserGroup.__TableName);
return dt;
}
/// <summary>
/// 返回表字段的值，根据条件SCon返回sTableName的字段sFieldID的值
/// </summary>
/// <param name="sTableName"></param>
/// <param name="sFieldID"></param>
/// <returns></returns>
public string GetTableFieldValue(string sTableName, string sField,string SCon)
{
string sql="";
if (_Loginer.DbType == DbAcessTyp.SQLServer)
sql = string.Format("SELECT isnull({0},'') FROM {1} where 1=1 " + SCon, sField, sTableName);
else
sql = string.Format("SELECT nvl({0},'') FROM {1} where 1=1 " + SCon, sField, sTableName);
object o=new DataBaseLayer(_Loginer.DBName).GetSingle(sql);
return o==null?"":o.ToString();
}
/// <summary>
/// 获取当前用户的系统权限
/// </summary>
/// <param name="user">当前用户</param>
/// <returns></returns>
public DataTable Getb_sys_UserAuthorities(Loginer user)
{
string sql = "SELECT UA.FID, UA.FUGID, UA.FunctionID, UA.FAuths, UA.FModelID,UA.FMenu, U.FNumber GNumber, U.FName GName, F.FNumber FNum, F.FName FName,F.FMODELID FROM sys_UG_Auth UA INNER JOIN sys_UserGroup U ON UA.FUGID = U.FID left outer JOIN sys_Function F ON UA.FunctionID = F.FID " +
" where UA.FUGID in (SELECT UG.FUserGroupID FROM sys_User_Group UG INNER JOIN sys_User U ON UG.FUserID = U.FID WHERE (U.FID = '" + user.Fid + "')) ";
DataTable dt = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataTable(sql);
return dt;
}
/// <summary>
/// 检查用户是否存在
/// </summary>
/// <param name="userid">用户编号</param>
/// <returns></returns>
public bool ExistsUser(string account)
{
string sql = "";
IDbDataParameter[] sPara = new IDbDataParameter[1];
if (_Loginer.DbType == DbAcessTyp.SQLServer)
{
sql = "select count(*) from sys_User where FAccount=@Account";
sPara[0] = DataConverter.GetSqlPara("@Account", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
else
{
sql = "select count(*) from sys_User where FAccount=:Account";
sPara[0] = DataConverter.GetOraclePara("Account", DataConverter.SqlTypeToString(SqlDbType.VarChar));
}
sPara[0].Value = account;
object o = new DataBaseLayer(_Loginer.DBName).GetSingle(sql, sPara);
return int.Parse(o.ToString()) > 0;
}
public DataTable Getb_sys_UserDirect(string account, string DBName)
{
string sql = "select * from sys_User where FAccount='"+ account +"'";
//SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
//cmd.AddParam("@Account", SqlDbType.VarChar, account);
DataTable dt = new DataBaseLayer(DBName).ExecuteQueryDataTable(sql); //DataProvider.Instance.GetTable(DBName, cmd.SqlCommand, tb_sys_User.__TableName);
return dt;
}
private static DataBaseLayer makeDbLayer(string server, string database)
{
if (workingDBMS == "SQLServer")
{
dbLayer = new SqlLayer(server, database, "sa", "password");
}
else if (workingDBMS == "Other")
{
dbLayer = new OleDbLayer(server, database, "sa", "password");
}
else
{
Debug.Assert(false, "SGBD desconhecido");
}
dbLayer.ConnectionStateChanged += new System.Data.StateChangeEventHandler(Connection_StateChange);
return dbLayer;
}
//煎药警告
public string packwarning()
{
DataBaseLayer db = new DataBaseLayer();
string sql3 = "";
sql3 = "select ID,Pspnum,customid,delnum,(select packwarning from warning where hospitalid = p.Hospitalid and type=0) as packwarning,(select doingtime from bubble where pid = p.id) as doingtime,(SELECT bubbleperson FROM bubble WHERE pid = p.id) as bp,(select hnum from hospital as h where h.id = p.hospitalid) as hnum,(select hname from hospital as h where h.id = p.hospitalid) as hname,Hospitalid,name,sex,age,phone,address,department,inpatientarea,ward,sickbed,";
sql3 += "diagresult,(select warningtime from tisaneinfo where pid = p.id) as warningtime,dose,takenum,getdrugtime,getdrugnum,takemethod,decscheme,oncetime,twicetime,packagenum,dotime,doperson,dtbcompany,dtbaddress,dtbphone,dtbtype,soakwater,soaktime,labelnum,remark,doctor,footnote,ordertime,curstate";
sql3 += " from prescription as p where id in (select pid from tisaneinfo where tisanestatus = 1)";
SqlDataReader sr3 = db.get_Reader(sql3);//煎药完成的所有信息
System.DateTime currentTime = new System.DateTime();
currentTime = System.DateTime.Now;//当前时间
string warningtime = currentTime.ToString("yyyy-MM-dd HH:mm:ss");
ArrayList list2 = new ArrayList();
ArrayList list1 = new ArrayList();
ArrayList list3 = new ArrayList();
ArrayList list4 = new ArrayList();
ArrayList list5 = new ArrayList();
while (sr3.Read())
{
// sr3["bubblewarning"].ToString();
// sr3["getdrugtime"].ToString();
string d1 = sr3["packwarning"].ToString();//包装警告时间
list1.Add(d1);
string drugtime = sr3["getdrugtime"].ToString();//得到该处方号的取药时间
// DateTime d2 = Convert.ToDateTime(sr3["getdrugtime"].ToString());
list2.Add(drugtime);
string id = sr3["ID"].ToString();//当前id煎药单号
list3.Add(id);
string hospitalid = sr3["hospitalid"].ToString();
list4.Add(hospitalid);
string awarningtime = sr3["warningtime"].ToString();
list5.Add(awarningtime);
}
for (int i = 0; i < list2.Count; i++)
{
string sql8 = "select status from warning where hospitalid = '" + list4[i] + "'";
SqlDataReader sr8 = db.get_Reader(sql8);
string status = "";//医院预警开关状态
if (sr8.Read())
{
status = sr8["status"].ToString();
}
string d1 = list1[i].ToString(); //包装警告时间
DateTime d2 = Convert.ToDateTime(list2[i].ToString()); //取药时间
string strY = currentTime.ToString("yyyy-MM-dd HH:mm:ss");
DateTime d3 = Convert.ToDateTime(strY);//当前时间
TimeSpan d4 = d2.Subtract(d3);//取药时间- 当前时间
//取药时间- 当前时间
int time = Convert.ToInt32(d4.Days.ToString()) * 24 * 60 + Convert.ToInt32(d4.Hours.ToString()) * 60 + Convert.ToInt32(d4.Minutes.ToString());
//包装警告时间
if (d1 == "")
{
d1 = "-10000000";
}
int time2 = Convert.ToInt32(d1);
if (status == "0")
{
time2 = -10000000;
}
if (time < time2)
{
string strsql1 = "update tisaneinfo set warningstatus = 1,warningtype='包装预警' where pid = '" + list3[i] + "'";
db.cmd_Execute(strsql1);
// if (list5[i].ToString() == "1970-1-1 0:00:00")
if (list5[i].ToString() == "" || Convert.ToDateTime(list5[i].ToString()).ToString("yyyy-MM-dd HH:mm:ss") == "1970-01-01 00:00:00")
{
string strsql2 = "update tisaneinfo set warningtime ='" + warningtime + "' where pid = '" + list3[i] + "'";
db.cmd_Execute(strsql2);
}
}
else
{
string strsql2 = "update tisaneinfo set warningstatus = 0,warningtype='暂无预警',warningtime ='1970-1-1 00:00:00' where pid = '" + list3[i] + "'";
db.cmd_Execute(strsql2);
}
}
string strS = currentTime.ToString("yyyy/MM/dd 00:00:00");
string strS2 = currentTime.ToString("yyyy/MM/dd 23:59:59");
string str2 = "";
string str = "select pid from tisaneinfo where tisanestatus =1 and warningstatus = 1 and pid not in (select Decoctingnum from packing) and pid in (select id from prescription as p where p.dotime between '" + strS + "' and '" + strS2 + "') ";
SqlDataReader sr = db.get_Reader(str);
while (sr.Read())
{
str2 += sr["pid"].ToString() + ",";
}
return(str2);
}
public string countall()
{
DataBaseLayer db = new DataBaseLayer();
System.DateTime currentTime = new System.DateTime();
currentTime = System.DateTime.Now;//当前时间
string strS = currentTime.ToString("yyyy/MM/dd 00:00:00");
string strS2 = currentTime.ToString("yyyy/MM/dd 23:59:59");
string num;
string num1 = "";
string num2 = "";
string num3 = "";
string num4 = "";
string num5 = "";
string num6 = "";
string num7 = "";
string num8 = "";
string num9 = "";
string num10 = "";
string num11 = "";
string num12 = "";
string str1 = "select count(*) as num1 from bubble where endDate between '" + strS + "' and '" + strS2 + "'";
//
string str2 = "select count(*) as num2 from tisaneinfo where endDate between '" + strS + "' and '" + strS2 + "'";
//包装
String str3 = "select count(*) as num3 from Packing where pacTime between '" + strS + "' and '" + strS2 + "'";
//发货
string str4 = "select count(*) as num4 from Delivery where sendtime between '" + strS + "' and '" + strS2 + "'";
//复核
string str5 = "select count(*) as num5 from Audit where audittime between '" + strS + "' and '" + strS2 + "'";
//调剂
string str6 = "select count(*) as num6 from adjust where endDate between '" + strS + "' and '" + strS2 + "'";
//已匹配
//string str7 = "select count(*) as num7 from DrugMatching where status =1";
string str7 = "select count(*) as num7 from DrugMatching where pspId IS NULL";
//打印
string str8 = "select count(*) as num8 from printstatus where printstatus =1";
//已审核
string str9 = "select count(*) as num9 from PrescriptionCheckState where partytime between '" + strS + "' and '" + strS2 + "'";
//已录入
string str10 = "select count(*) as num10 from Prescription where dotime between '" + strS + "' and '" + strS2 + "'";
//未打印
string str11 = "select count(*) as num11 from printstatus where printstatus =0";
//未匹配
// string str12 = "select count(*) as num12 from DrugMatching where status =2";
//string str12 = "select count(*) as num12 from prescription as p right join DrugMatching dm on p.id=dm.pspId";
string str12 = "select count(*)as num12 from prescription as p left join PrescriptionCheckState pcs on p.id=pcs.prescriptionId left join drug d on d.Pspnum=p.Pspnum and p.Hospitalid = d.Hospitalid left join DrugMatching dm on d.id=dm.drugId and dm.pspId = p.ID where pcs.prescriptionId IS NULL and dm.drugId IS NULL AND d.ID IS NOT NULL and p.dotime between '" + strS + "' and '" + strS2 + "'";
/// string str12 = "select count(*) from prescription as p left join PrescriptionCheckState pcs on p.id=pcs.prescriptionId left join drug d on d.Pspnum=p.Pspnum and p.Hospitalid = d.Hospitalid left join DrugMatching dm on d.id=dm.drugId and dm.pspId = p.ID where pcs.prescriptionId IS NULL and dm.drugId IS NULL AND d.ID IS NOT NULL and p.id not in (select pid from InvalidPrescription)";
///
SqlDataReader sr1 = db.get_Reader(str1);
if (sr1.Read())
{
num1 = sr1["num1"].ToString();
}
SqlDataReader sr2 = db.get_Reader(str2);
if (sr2.Read())
{
num2 = sr2["num2"].ToString();
}
//包装
SqlDataReader sr3 = db.get_Reader(str3);
if (sr3.Read())
{
num3 = sr3["num3"].ToString();
}
//发货
SqlDataReader sr4 = db.get_Reader(str4);
if (sr4.Read())
{
num4 = sr4["num4"].ToString();
}
//复核
SqlDataReader sr5 = db.get_Reader(str5);
if (sr5.Read())
{
num5 = sr5["num5"].ToString();
}
//调剂
SqlDataReader sr6 = db.get_Reader(str6);
if (sr6.Read())
{
num6 = sr6["num6"].ToString();
}
//匹配
SqlDataReader sr7 = db.get_Reader(str7);
if (sr7.Read())
{
num7 = sr7["num7"].ToString();
}
//打印
SqlDataReader sr8 = db.get_Reader(str8);
if (sr8.Read())
{
num8 = sr8["num8"].ToString();
}
//审核
SqlDataReader sr9 = db.get_Reader(str9);
if (sr9.Read())
{
num9 = sr9["num9"].ToString();
}
//录入
SqlDataReader sr10 = db.get_Reader(str10);
if (sr10.Read())
{
num10 = sr10["num10"].ToString();
}
//未打印
SqlDataReader sr11 = db.get_Reader(str11);
if (sr11.Read())
{
num11 = sr11["num11"].ToString();
}
//未匹配
SqlDataReader sr12 = db.get_Reader(str12);
if (sr12.Read())
{
num12 = sr12["num12"].ToString();
}
num = num1 + "," + num2 + "," + num3 + "," + num4 + "," + num5 + "," + num6 + "," + num7 + "," + num8 + "," + num9 + "," + num10 + "," + num11 + "," + num12;
return(num);
}
public System.Data.DataTable SearchLog(string sFilter)
{
DataSet ds;
string sql = "SELECT l.FID, l.FDate, u.FAccount, u.FUserName, f.FNumber AS FunNum, f.FName AS FunName, fs.FNumber AS FsubNum, fs.FName AS FsubName, l.FDescription, " +
" l.FMachineName, l.FIPAddress, l.FSQL " +
" FROM sys_log AS l INNER JOIN " +
" sys_User AS u ON l.FUserID = u.FID LEFT OUTER JOIN " +
" sys_Function AS f ON l.FFunctionID = f.FID LEFT OUTER JOIN " +
" sys_Fun_MenuBar AS fs ON l.FSubFunctionID = fs.FID where 1=1 " + sFilter;
ds = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataSet(sql);
return ds.Tables[0];
}
/// <summary>
/// 获取用户组的权限信息，包括组的用户，组的权限
/// </summary>
/// <param name="groupCode"></param>
/// <returns></returns>
public System.Data.DataSet GetUserGroup(string groupCode)
{
ArrayList arrSqlList = new ArrayList();
arrSqlList.Add("SELECT FID, FNumber, FName, FNote FROM sys_UserGroup where fnumber ='" + groupCode + "' order by FName");
arrSqlList.Add("SELECT UG.*, U.FAccount, U.FUserName, G.FNumber, G.FName FROM sys_User_Group UG INNER JOIN " +
" sys_User U ON UG.FUserID = U.FID INNER JOIN sys_UserGroup G ON UG.FUserGroupID = G.FID where g.Fnumber ='" + groupCode + "'");
arrSqlList.Add("SELECT UA.FID, UA.FUGID, UA.FunctionID, UA.FAuths, UA.FModelID,UA.FMenu, G.FNumber AS GNum, G.FName GName, F.FNumber FNum, F.FName FName, F.FModelID, F.FAuths " +
" FROM sys_UG_Auth UA INNER JOIN sys_UserGroup G ON UA.FUGID = G.FID LEFT OUTER JOIN sys_Function F ON UA.FunctionID = F.FID where g.Fnumber ='" + groupCode + "'");
arrSqlList.Add("select FID,FAccount,FUserName from sys_User where fid not in (select UG.FUserID from sys_UserGroup G inner join sys_User_Group UG on UG.FUserGroupID=G.FID where G.Fnumber='" + groupCode + "')");
DataSet ds = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataSet(arrSqlList);
ds.Tables[BusinessDataSetIndex.Groups].TableName = tb_sys_UserGroup.__TableName;
ds.Tables[BusinessDataSetIndex.GroupUsers].TableName = tb_sys_User_Group.__TableName;
ds.Tables[BusinessDataSetIndex.GroupAuthorities].TableName = tb_sys_UG_Auth.__TableName;
ds.Tables[BusinessDataSetIndex.GroupAvailableUser].TableName = "GroupAvailableUser";
return ds;
}
public bool TestConnection()
{
try
{
string dbType = DbConfiguration_Factory.DbType;
string sConn = DbConfiguration_Factory.GetAccountConnString();
string sSQL = "SELECT T_Account.FID, T_Account.FNumber, T_Account.FName, T_Account.FDatabase, T_Account.FServerName, T_Account.FUser, T_Account.FPwd, T_Account.FDataType, T_Account.FOrgID, T_Account.FCreateDate, T_Account.FBackUpDate, T_Account.FVer, T_Account.FProductName, T_Account.FOnlineCount, T_Account.FServerIP,T_DbType.FSign " +
" FROM T_Account inner join T_DbType ON T_Account.FID = T_DbType.FID ";
DataTable dt = new DataBaseLayer(sConn, dbType).ExecuteQueryDataTable(sSQL);
if (dt == null)
return false;
else
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 用户刷卡登录
/// </summary>
/// <param name="loginUser">登录用户信息</param>
/// <param name="LoginUserType">登录类型</param>
/// <returns></returns>
public DataTable LoginByCard(LoginUser loginUser, char LoginUserType)
{
string strErr = "";
string sql = "";
//用户名为空
if (loginUser.CardNo == string.Empty)
{
strErr = "卡号不能为空！";
}
if (!"S,W,N".Contains(LoginUserType.ToString()))
{
strErr = "无法识别的登录类型！";
}
//Windows域
if (LoginUserType == 'W')
{
if (loginUser.DbType == DbAcessTyp.SQLServer)
{
sql = "select * from sys_user where fdomainName like '%'+(select FAccount from sys_User where FCardNo='" + loginUser.CardNo + "')+'@%'";
}
else if (loginUser.DbType == DbAcessTyp.Oracle)
{
sql = "select * from sys_user where fdomainName like '%'||(select FAccount from sys_User where FCardNo='" + loginUser.CardNo + "')||'@%'";
}
}
//Novell域
if (LoginUserType == 'N')
{
if (loginUser.DbType == DbAcessTyp.SQLServer)
{
sql = "select * from sys_user where fnovellaccount like '%'+(select FAccount from sys_User where FCardNo='" + loginUser.CardNo + "')+'.%'";
}
else if (loginUser.DbType == DbAcessTyp.Oracle)
{
sql = "select * from sys_user where fnovellaccount like '%'||(select FAccount from sys_User where FCardNo='" + loginUser.CardNo + "')||'.%'";
}
}
//系统
if (LoginUserType == 'S')
{
sql = "select * from sys_User where FCardNo='" + loginUser.CardNo + "'";
}
if (strErr.Trim() != string.Empty)
throw new CustomException(strErr); //抛出异常
DataTable ds = new DataBaseLayer(_Loginer.DBName).ExecuteQueryDataTable(sql);
if (ds.Rows.Count == 1)
{
if (_Loginer.DbType == DbAcessTyp.SQLServer)
sql = "UPDATE sys_User SET FFlagOnline='1',FLastLoginTime=GetDate(),FLoginCounter=isnull(FLoginCounter,0) + 1 WHERE FCardNo='" + loginUser.CardNo + "'";
else
sql = "UPDATE sys_User SET FFlagOnline='1',FLastLoginTime=sysdate,FLoginCounter=nvl(FLoginCounter,0) + 1 WHERE FCardNo='" + loginUser.CardNo + "'";
new DataBaseLayer(_Loginer.DBName).ExecuteSql(sql);
return ds;
}
else
throw new CustomException("请检查卡号！"); //抛出异常
}
public bool ModifyPwdDirect(string account, string pwd, string DBName)
{
string sql = "update sys_User set Fpassword='******' where Faccount='" + account + "'";
object o = new DataBaseLayer(DBName).ExecuteSql(sql); //DataProvider.Instance.ExecuteNoQuery(DBName, cmd.SqlCommand);
return int.Parse(o.ToString()) != 0;
}