public int findPackingInfo_cts(String Fpactate, String Pacpersonnel, String PacTime, String StartTime)
{
string strSQL = "SELECT count(*) from prescription as p join Packing as c on p.id=c.DecoctingNum where 1=1 ";
if (Fpactate != "")
{
strSQL += " and c.Fpactate ='" + Fpactate + "'";
}
if (Pacpersonnel != "0" && Pacpersonnel != "")
{
strSQL += " and c.Pacpersonnel='" + Pacpersonnel + "'";
}
if (StartTime != "0")
{
DateTime d = Convert.ToDateTime(StartTime);
string strS = d.ToString("yyyy/MM/dd 00:00:00");
strSQL += " and c.startTime >='" + strS + "'";
}
if (PacTime != "0")
{
DateTime d4 = Convert.ToDateTime(PacTime);
string strE = d4.ToString("yyyy/MM/dd 23:59:59");
strSQL += " and c.startTime <='" + strE + "'";
}
int count = Convert.ToInt32(db.cmd_ExecuteScalar(strSQL));
return(count);
}
public int GetDrugInfoCount()
{
string strSql = "select count(*) from DrugAdmin";
int count = Convert.ToInt32(db.cmd_ExecuteScalar(strSql));
return(count);
}
public int findRecipeInfots(int status, string begindate, string enddate, string eName)
{
string sql = "select count(*) from ( select max(id) as id,SwapPer,convert(varchar, wordDate, 111) as wordDate,'调剂' AS wordcontent, count(wordDate) as workload from adjust as a where 1=1 ";
if (status != 2)
{
sql += " and a.status=" + status;
}
if (begindate != null && begindate.Length > 0)
{
DateTime d = Convert.ToDateTime(begindate);
string strB = d.ToString("yyyy/MM/dd 00:00:00");
sql += " and a.wordDate>='" + strB + "'";
}
if (enddate != null && enddate.Length > 0)
{
DateTime d4 = Convert.ToDateTime(enddate);
string strE = d4.ToString("yyyy/MM/dd 23:59:59");
sql += " and a.wordDate<='" + strE + "'";
}
if (eName != null && eName.Length > 0)
{
sql += " and a.SwapPer='" + eName + "'";
}
sql += "GROUP BY SwapPer,CONVERT(varchar, wordDate, 111)";
sql += ") as b";
int count = Convert.ToInt32(db.cmd_ExecuteScalar(sql));
return(count);
}
/// <summary>
/// 查询发货信息
/// </summary>
/// <param > Sendstate, SendTime, Sendpersonnel</param>
/// <returns>dt</returns>
public int findDeliveryInfots(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 count(*) 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 + "%'";
}
int count = Convert.ToInt32(db.cmd_ExecuteScalar(sql));
return(count);
}
public int findallmachineinfots(string typeofmachine, string machine_name)
{
string sql = "select count(*) from machine where 1=1";
if (typeofmachine != "3")
{
sql += "and mark ='" + typeofmachine + "' and machinename like '%" + machine_name + "%'";
}
int count = Convert.ToInt32(db.cmd_ExecuteScalar(sql));
return(count);
}
///// <summary>
///// 根据处方号查询未匹配药品信息
///// </summary>
///// <param name="pspnum">处方号</param>
///// <returns>DataTable对象</returns>
public int findNotMatchDrugByPspnum_count(string pid)
{
string sql = "select count(d.id) from drug d " +
// left join DrugMatching dm on d.id=dm.drugId and dm.hospitalId=d.hospitalId
"where d.pid ='" + pid + "' and " +
"d.id not in( select distinct aaa.id from ( select d1.id from drug d1 where d1.Hospitalid in( select id from Hospital where relation_drug_type_id ='1' ) and d1.drugnum in" +
"(select dd.hdrugNum from DrugMatching dd where dd.hospitalId ='128') union all" +
" select d2.id from drug d2 where d2.Hospitalid in( select id from Hospital where relation_drug_type_id ='2' ) and d2.drugnum in" +
"(select dd.hdrugNum from DrugMatching dd where dd.hospitalId =d2.Hospitalid )) aaa)";
int count = Convert.ToInt32(db.cmd_ExecuteScalar(sql));
return(count);
db.write_log_txt("药品匹配录入药品数量显示NEWNEW:" + sql);
}
public int findEmployeeInfo_c(string EName, string JobNum, string Role)
{
string sql = " select count(id) from Employee where 1=1 ";
if (EName != "0")
{
sql += "and EName like'%" + EName + "%'";
}
if (JobNum != "0")
{
sql += "and JobNum like'%" + JobNum + "%'";
}
if (Role != "")
{
sql += "and Role like'%" + Role + "%'";
}
// sql += " and id ='" + EName + "'";
int count = Convert.ToInt32(db.cmd_ExecuteScalar(sql));
return(count);
}
public DataTable get_p_dt_panel(string pspnum)
{
DataTable dtt = new DataTable();
DataTable dt = new DataTable();
string sql = "";
//判断是否是当天处方
int flag = 1;
string flag_sql = "select count(tt.id) from tb_temp tt inner join machine m on m.id=tt.machine_id " +
" inner join prescription p on p.ID=tt.p_id where p.pspnum='" + pspnum + "' ";
flag = Convert.ToInt32(db.cmd_ExecuteScalar(flag_sql));
if (flag > 0)
{
// sql = "select top 1 tt.id as id , convert(varchar(60),datepart(hh,tt.time))+':'+convert(varchar(60),datepart(mi,tt.time)) " +
//"as time,m.equipmenttype as machine_type,m.machineroom as machine_room,m.unitnum as unit_num," +
// "p.Pspnum as psp_num,tt.temp as temp,m.machinename as machine_name,tt.p_id as p_id,m.id as machine_id from tb_temp tt inner join machine m on m.id=tt.machine_id " +
// " inner join prescription p on p.ID=tt.p_id where p.pspnum='" + pspnum + "' order by tt.id desc";
// dt = db.get_DataTable(sql);
sql = "select top 1 tt.id as id , convert(varchar(60),datepart(hh,tt.time))+':'+replicate('0',2-len(convert(varchar(60),datepart(mi,tt.time))))+convert(varchar(60),datepart(mi,tt.time)) " +
"as time,m.equipmenttype as machine_type,m.machineroom as machine_room,m.unitnum as unit_num," +
"p.Pspnum as psp_num,tt.temp as temp,m.machinename as machine_name,tt.p_id as p_id,m.id as machine_id from tb_temp tt inner join machine m on m.id=tt.machine_id " +
" inner join prescription p on p.ID=tt.p_id where p.pspnum='" + pspnum + "' order by tt.id desc";
dt = db.get_DataTable(sql);
}
//去历史记录表里查询
else
{
string sql1 = " select top 1 t.temp_json from tb_temp_back t inner join prescription p on p.ID=t.p_id where p.Pspnum ='" + pspnum + "'";
SqlDataReader sdr = db.get_Reader(sql1);
string json = "";
while (sdr.Read())
{
json = sdr["temp_json"].ToString();
}
sdr.Close();
// dt = JsonHelper.JsonToDataTable(json);
dtt = JsonHelper.JsonToDataTable(json);
// dtt = JsonHelper.JsonToDataTable(json);
/* string idd= dtt.Compute("max(id)", "").ToString();
* dtt.DefaultView.Sort = "id desc";
* dtt.AcceptChanges();
* DataRow[] dr = dtt.Select("id='"+idd+"'");
* dt=dtt.Clone();//克隆DATATABLE结构
* foreach (DataRow row in dr) // 将查询的结果添加到dt中；
* {
* dt.Rows.Add(row.ItemArray);
* // dt.Rows.Add(row);
* // dt.ImportRow(row);
* }
* */
DataRow[] dr = dtt.Select("psp_num='" + pspnum + "'");
// dr1 = dtt.Rows.Find(dtt.Rows.Count - 1);
// int num = int.Parse(dr[dtt.Rows.Count-1].Value.ToString());
// DataRow delRow = shiZhongGuanLiBEO1.SHEBEI_JIEDIAN.Rows.Find(num);
dt = dtt.Clone();//克隆DATATABLE结构
// dt.Rows.Add(dr1);
int i = dr.Count() - 1;
int j = 0;
foreach (DataRow row in dr) // 将查询的结果添加到dt中；
{
if (j == i)
{
dt.Rows.Add(row.ItemArray);
}
// dt.Rows.Add(row);
// dt.ImportRow(row);
j++;
}
}
return(dt);
}