protected void PatInfo_Query()
{
string PATIC = SearchID.Text;
string PATNAME = SearchName.Text;
string sql;
sql = " SELECT pif_id, pif_name, if(pif_sex = 'M','男','女') as sex, pif_dob, pif_ic, pif_docname FROM pat_info ";
sql += "WHERE 1=1 ";
if (!string.IsNullOrEmpty(PATNAME)) //姓名篩選
{
sql += "AND pif_name like '%" + PATNAME + "%' ";
}
if (!string.IsNullOrEmpty(PATIC)) //身分證號篩選
{
sql += "AND pif_ic like '%" + PATIC + "%' ";
}
sql += "ORDER BY pif_id ";
DataTable dt = db.Query(sql);
Store istore = GridList.GetStore();
istore.DataSource = db.GetDataArray(dt);
istore.DataBind();
Window1.Show();
}
/// <summary>
/// 沒有_PAT_ID的時候, 用這個函式來顯示頁面(不論是歷史病患或是當前病患)
/// </summary>
protected void show1()
{
//string chkmth = DateTime.Now.AddMonths(-1).ToString("yyyy-MM");
//String weekType = getWeekType();
DateTime datetime = DateTime.Now;
int iOrn = 0;
string weekType = (0 == (iOrn = datetime.DayOfWeek.GetHashCode()) ? 7 : iOrn).ToString(); // 一般程式(Basic,C,Delphi...) 週末指星期六,其實星期日是每週的第一日。
String date = datetime.ToString("yyyy-MM-dd");
//String ldate = datetime.AddMonths(-1).ToString("yyyy-MM-dd").Substring(0, 8);
//string days = DateTime.DaysInMonth(int.Parse(ldate.Substring(0, 4)), int.Parse(ldate.Substring(5, 2))).ToString();
String sql = " SELECT p.pif_id ,";
sql += " P.pif_name AS PERSON_NAME,";
sql += " if (P.pif_sex = 'M','男','女') as sex,";
sql += " P.pif_dob, ";
sql += " P.pif_ic, ";
//sql += " if (e.txt_10 = '','血透','腹透') as txt_10,";
sql += " if (b.next_visit_date > '','腹透','血透') as txt_10,";
sql += " b.next_visit_date,";
sql += " e.dat_9,";
sql += " e.info_date as info_date1,";
sql += " if (g.BI > 0,'V',' ') as txt_101,";
sql += " case f.opt_1 when '1' then '退出' when '2' then '移植' when '3' then '转出' when '4' then '死亡'";
sql += " when '5' then '转入' else '' end,";
sql += " f.info_date,";
sql += " ' ',";
//sql += " dchk.NotChecked,";
sql += " p.pif_docname,";
sql += " V.pv_weight AS PERSON_WEIGHT,";
sql += " V.pv_macstat AS PERSON_STATE ";
sql += " FROM mac_setup M LEFT JOIN appointment_setup A ON M.mac_flr = A.apptst_flr ";
sql += " AND M.mac_sec = A.apptst_sec AND M.mac_bedno = A.apptst_bed AND A.apptst_daytyp='" + weekType + "'";
sql += " LEFT JOIN pat_visit V ON A.apptst_patic=V.pv_ic AND V.pv_datevisit='" + date + "'";
sql += " LEFT JOIN pat_info P ON A.apptst_patic=P.pif_ic ";
sql += " left join zinfo_f_012 e on P.pif_id = e.pat_id ";
sql += " left join zinfo_a_07 f on P.pif_id = f.pat_id ";
sql += " left join (SELECT pat_id, MAX(dat_3) AS next_visit_date FROM zinfo_p_06 group by pat_id) b on P.pif_id=b.pat_id";
sql += " left join BI_SUM_View g on P.pif_id = g.pat_no";
//sql += " left join (SELECT pif_id,SUM(NotChecked) AS NotChecked FROM";
//sql += " (SELECT pi.pif_id, pi.pif_name, pi.pif_ic, pi.RESULT_CODE, ars.RESULT_VALUE, if (ars.RESULT_VALUE > 0, 0, 1) as NotChecked";
//sql += " FROM (SELECT pio.pif_id, pio.pif_name, pio.pif_ic, bcl.RESULT_CODE";
//sql += " FROM pat_info pio";
//sql += " NATURAL JOIN bio_check_list bcl";
//sql += " where bcl.I_MONTH = 1) pi";
//sql += " LEFT JOIN (SELECT arl.pat_no, arl.result_date, arl.result_code, arl.RESULT_VALUE_T AS RESULT_VALUE";
//sql += " FROM a_result_log arl";
//sql += " where arl.RESULT_CODE IN (SELECT RESULT_CODE FROM bio_check_list where I_MONTH = 1)";
//sql += " and result_date >= '" + ldate + "01' and result_date <= '" + ldate + days + "') ars on pi.pif_id = ars.pat_no and pi.RESULT_CODE = ars.RESULT_CODE";
//sql += " ORDER BY pi.pif_id) dc";
//sql += " GROUP BY pif_id) dchk on p.pif_id = dchk.pif_id";
this.CommandColumn1.Text = "填寫信息";
//((GridCommand)this.CommandColumn1.Commands[0]).Text = "查看信息";
Store istore = GridList.GetStore();
istore.DataSource = db.GetDataArray_AddRowNum(db.Query(sql));
istore.DataBind();
}
protected void Dialysis_detail(object sender, DirectEventArgs e)
{
string json = e.ExtraParams["Values"];
Dictionary <string, string>[] selRow = JSON.Deserialize <Dictionary <string, string>[]>(json);
_PAT_IC = selRow[0]["pat_ic"].ToString();
_PIF_NAME = selRow[0]["pif_name"].ToString();
_USER_NAME = selRow[0]["pif_docname"].ToString();
_PIF_SX = selRow[0]["pif_sex"].ToString() + "性";
string sql = "SELECT pif_id, pif_name, pif_docname FROM pat_info ";
sql += " where pif_ic = '" + _PAT_IC + "' ";
DataTable dt = db.Query(sql);
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["pif_docname"].ToString() != null)
{
_PatDocName = dt.Rows[0]["pif_docname"].ToString();
}
else
{
_PatDocName = "";
}
Session["PAT_ID"] = dt.Rows[0]["pif_id"].ToString();
_PAT_ID = Session["PAT_ID"].ToString();
Store istore = GridList.GetStore();
istore.RemoveAll();
Window1.Hide();
Load_Appointment();
}
}
protected void PatInfo_Query()
{
string PATIC = SearchID.Text;
string PATNAME = SearchName.Text;
string sql;
sql = " SELECT p.pif_id, p.pif_name, if(p.pif_sex = 'M','男','女') as sex, p.pif_dob, p.pif_ic, p.pif_docname, ";
sql += "if((SELECT count(*) FROM blood_group bg WHERE bg.bgrp_patic=p.pif_ic AND (bg.bgrp_aids='Y' OR bg.bgrp_syphilis='Y' OR bg.bgrp_hbv='Y' OR bg.bgrp_hcv='Y' OR bg.bgrp_diabetic='Y'))=1,'Y','N') AS pif_kind ";
sql += "FROM pat_info p ";
sql += "WHERE 1=1 ";
if (!string.IsNullOrEmpty(PATNAME)) //姓名篩選
{
sql += "AND p.pif_name like '%" + PATNAME + "%' ";
}
if (!string.IsNullOrEmpty(PATIC)) //身分證號篩選
{
sql += "AND p.pif_ic like '%" + PATIC + "%' ";
}
sql += "ORDER BY pif_id ";
DataTable dt = db.Query(sql);
Store istore = GridList.GetStore();
istore.DataSource = db.GetDataArray(dt);
istore.DataBind();
Window1.Show();
}
/// <summary>
/// 有資料
/// </summary>
protected void show_err1()
{
string sql;
sql = "select a.pif_id, a.pif_name, a.pif_sex, a.pif_dob, a.pif_ic, ";
sql += "'' AS lgord_usr1, '' AS info_survey_date";
sql += " from pat_info a ";
sql += "where a.pif_sex not in ('M','F') or length(a.pif_dob)<>10 or length(REPLACE(a.pif_dob, '/', ''))<>8 ";
sql += "order by a.pif_id ";
Store istore = GridList.GetStore();
istore.DataSource = db.GetDataArray_AddRowNum(db.Query(sql));
istore.DataBind();
}
protected void show_history()
{
string sql;
DateTime datetime = DateTime.Now;
sql = " SELECT a.pif_id, a.pif_name, if (a.pif_sex = 'M','男','女') as sex, a.pif_dob, a.pif_ic,";
sql += " if (b.next_visit_date > '','腹透','血透') as txt_10, ";
sql += " e.diadate AS FirstDate, '' AS InfoDate, ";
sql += " if (g.BI > 0,'V',' ') as txt_101,";
//sql += "'' as txt_101,";
sql += " case f.opt_1 when '1' then '退出' when '2' then '肾移植' when '3' then '转出' when '4' then '死亡' when '5' then '转入' else '' end AS opt_52,";
sql += " f.info_date,' ',a.pif_docname,c.info_survey_date,'ignore' ";
sql += "FROM pat_info a ";
sql += "LEFT JOIN zinfo_maim c ON a.pif_id=c.pat_id ";
sql += "LEFT JOIN (SELECT cln1_patic, MAX(cln1_diadate) AS diadate FROM clinical1_nurse group by cln1_patic) e ON a.pif_ic = e.cln1_patic ";
sql += "LEFT JOIN zinfo_a_07 f ON a.pif_id = f.pat_id ";
sql += "LEFT JOIN (SELECT pat_id, MAX(dat_3) AS next_visit_date FROM zinfo_p_06 group by pat_id) b ON a.pif_id=b.pat_id ";
sql += "LEFT JOIN BI_SUM_View g ON a.pif_id = g.pat_no ";
sql += "WHERE 1=1";
if (!string.IsNullOrEmpty(Text_Name.Text)) //姓名篩選
{
sql += " and a.pif_name like '%" + Text_Name.Text + "%'";
}
if (!string.IsNullOrEmpty(Cbo_Gender.Text)) //姓別篩選
{
sql += " and a.pif_sex ='" + GetComboBoxValue(Cbo_Gender) + "'";
}
if (!string.IsNullOrEmpty(Text_ID.Text)) //身分證號篩選
{
if (Text_ID.Text.Substring(0, 1) != "#")
{
sql += " and a.pif_ic like '%" + Text_ID.Text + "%'";
}
else
{
sql += " and a.pif_id='" + Text_ID.Text.Substring(1) + "'";
}
}
sql += " ORDER BY a.pif_id ";
DataTable dt = db.Query(sql);
Store istore = GridList.GetStore();
istore.DataSource = db.GetDataArray(dt);
istore.DataBind();
}
protected void Dialysis_detail(object sender, DirectEventArgs e)
{
string json = e.ExtraParams["Values"];
Dictionary <string, string>[] selRow = JSON.Deserialize <Dictionary <string, string>[]>(json);
_PAT_ID = selRow[0]["pat_id"].ToString();
_PAT_IC = selRow[0]["pat_ic"].ToString();
_PIF_NAME = selRow[0]["pif_name"].ToString();
_USER_NAME = selRow[0]["pif_docname"].ToString();
_PIF_SX = selRow[0]["pif_sex"].ToString() + "性";
if (LoadPatinfo(_PAT_ID) == true)
{
Store istore = GridList.GetStore();
istore.RemoveAll();
}
Window1.Close();
pifid.Text = _PAT_ID;
}
protected void show_now() // 沒有_PAT_ID的時候, 用這個函式來顯示頁面(不論是歷史病患或是當前病患)
{
DateTime datetime = DateTime.Now;
int iOrn = 0;
string weekType = (0 == (iOrn = datetime.DayOfWeek.GetHashCode()) ? 7 : iOrn).ToString();
String date = datetime.ToString("yyyy-MM-dd");
String sql = "SELECT p.pif_id , P.pif_name AS PERSON_NAME, if (P.pif_sex = 'M','男','女') as sex, P.pif_dob, P.pif_ic, ";
sql += " if (b.next_visit_date > '','腹透','血透') as txt_10,";
sql += " b.next_visit_date,";
sql += " e.dat_9,";
sql += " e.info_date as info_date1,";
sql += " if (g.BI > 0,'V',' ') as txt_101,";
sql += " case f.opt_1 ";
sql += " when '1' then '退出' ";
sql += " when '2' then '移植' ";
sql += " when '3' then '转出' ";
sql += " when '4' then '死亡'";
sql += " when '5' then '转入' ";
sql += " else '' ";
sql += " end AS opt_52,";
sql += " f.info_date, ' ', p.pif_docname,";
sql += " V.pv_weight AS PERSON_WEIGHT,";
sql += " V.pv_macstat AS PERSON_STATE ";
sql += " FROM mac_setup M";
sql += " LEFT JOIN appointment_setup A ON M.mac_flr = A.apptst_flr ";
sql += " AND M.mac_sec = A.apptst_sec AND M.mac_bedno = A.apptst_bed AND A.apptst_daytyp='" + weekType + "'";
sql += " LEFT JOIN pat_visit V ON A.apptst_patic=V.pv_ic AND V.pv_datevisit='" + date + "'";
sql += " LEFT JOIN pat_info P ON A.apptst_patic=P.pif_ic ";
sql += " LEFT JOIN zinfo_f_012 e ON P.pif_id = e.pat_id ";
sql += " LEFT JOIN zinfo_a_07 f ON P.pif_id = f.pat_id ";
sql += " LEFT JOIN (SELECT pat_id, MAX(dat_3) AS next_visit_date FROM zinfo_p_06 group by pat_id) b ON P.pif_id=b.pat_id";
sql += " LEFT JOIN BI_SUM_View g ON P.pif_id = g.pat_no";
sql += " WHERE P.pif_ic IS NOT NULL";
sql += " ORDER BY A.apptst_timetyp, M.mac_flr, M.mac_bedno";
//this.CommandColumn1.Text = "填寫信息";
Store istore = GridList.GetStore();
istore.DataSource = db.GetDataArray_AddRowNum(db.Query(sql));
istore.DataBind();
}
/// <summary>
///
/// </summary>
protected void show()
{
string sql;
DateTime datetime = DateTime.Now;
//String ldate = datetime.AddMonths(-1).ToString("yyyy-MM-dd").Substring(0, 8);
//string days = DateTime.DaysInMonth(int.Parse(ldate.Substring(0, 4)), int.Parse(ldate.Substring(5, 2))).ToString();
//string chkmth = DateTime.Now.AddMonths(-1).ToString("yyyy-MM");
//sql = "select DISTINCT a.pif_id ,a.pif_name,a.pif_sex,a.pif_dob,a.pif_ic,b.lgord_usr1,c.info_survey_date";
//sql += " from pat_info a";
//sql += " left join longterm_ordermgt b on a.pif_ic=b.lgord_patic ";
//sql += " and concat(b.lgord_dateord ,b.lgord_timeord) = ";
//sql += " (select max(CONCAT(lgord_dateord ,lgord_timeord)) from longterm_ordermgt where lgord_patic=a.pif_ic)";
//sql += " left join zinfo_maim c on a.pif_id=c.pat_id where 1=1";
//sql = "select DISTINCT a.pif_id ,a.pif_name,a.pif_sex,a.pif_dob,a.pif_ic,b.lgord_usr1,c.info_survey_date,d.info_date";
//sql += " from pat_info a";
//sql += " left join longterm_ordermgt b on a.pif_ic=b.lgord_patic ";
//sql += " and concat(b.lgord_dateord ,b.lgord_timeord) = ";
//sql += " (select max(CONCAT(lgord_dateord ,lgord_timeord)) from longterm_ordermgt where lgord_patic=a.pif_ic)";
//sql += " left join zinfo_maim c on a.pif_id=c.pat_id";
//sql += " left join zinfo_f_012 d on a.pif_id = d.pat_id and SUBSTR(d.info_date,1,7) = '" + chkmth + "' ";
//sql += " where 1=1";
//old andy 20150602
//sql = "select DISTINCT a.pif_id ,a.pif_name,if (a.pif_sex = 'M','男','女') as sex,a.pif_dob,a.pif_ic,a.pif_docname,c.info_survey_date,d.info_date";
//sql += " from pat_info a";
//sql += " left join zinfo_maim c on a.pif_id=c.pat_id";
//sql += " left join zinfo_e_01 d on a.pif_id = d.pat_id and SUBSTR(d.info_date,1,7) = '" + chkmth + "' ";
//sql += " where 1=1";
//new andy 20150602
sql = " select DISTINCT a.pif_id ,a.pif_name,if (a.pif_sex = 'M','男','女') as sex,a.pif_dob,a.pif_ic,";
sql += " if (b.next_visit_date > '','腹透','血透') as txt_10,";
sql += " b.next_visit_date,";
sql += " e.dat_9,";
sql += " e.info_date as info_date1,";
sql += " if (g.BI > 0,'V',' ') as txt_101,";
sql += " case f.opt_1 ";
//sql += " case f.opt_52 ";
sql += " when '1' then '退出'";
sql += " when '2' then '肾移植'";
sql += " when '3' then '转出'";
sql += " when '4' then '死亡'";
sql += " when '5' then '转入'";
sql += " else ''";
sql += " end,";
//sql += " f.info_date,dchk.NotChecked,a.pif_docname,c.info_survey_date,'ignore'";
sql += " f.info_date,' ',a.pif_docname,c.info_survey_date,'ignore'";
sql += " from pat_info a";
sql += " left join zinfo_maim c on a.pif_id=c.pat_id";
sql += " left join zinfo_f_012 e on a.pif_id = e.pat_id";
sql += " left join zinfo_a_07 f on a.pif_id = f.pat_id";
sql += " left join (SELECT pat_id, MAX(dat_3) AS next_visit_date FROM zinfo_p_06 group by pat_id) b on a.pif_id=b.pat_id";
sql += " left join BI_SUM_View g on a.pif_id = g.pat_no";
//sql += " left join (SELECT pif_id,SUM(NotChecked) AS NotChecked FROM";
//sql += " (SELECT pi.pif_id, pi.pif_name, pi.pif_ic, pi.RESULT_CODE, ars.RESULT_VALUE, if (ars.RESULT_VALUE > 0, 0, 1) as NotChecked";
//sql += " FROM (SELECT pio.pif_id, pio.pif_name, pio.pif_ic, bcl.RESULT_CODE";
//sql += " FROM pat_info pio";
//sql += " NATURAL JOIN bio_check_list bcl";
//sql += " where bcl.I_MONTH = 1) pi";
//sql += " LEFT JOIN (SELECT arl.pat_no, arl.result_date, arl.result_code, arl.RESULT_VALUE_T AS RESULT_VALUE";
//sql += " FROM a_result_log arl";
//sql += " where arl.RESULT_CODE IN (SELECT RESULT_CODE FROM bio_check_list where I_MONTH = 1)";
//sql += " and result_date >= '" + ldate + "01' and result_date <= '" + ldate + days + "') ars on pi.pif_id = ars.pat_no and pi.RESULT_CODE = ars.RESULT_CODE";
//sql += " ORDER BY pi.pif_id) dc";
//sql += " GROUP BY pif_id) dchk on a.pif_id = dchk.pif_id";
sql += " where 1=1";
if (Text_Name.Text != "")
{
sql += " and a.pif_name like '%" + Text_Name.Text + "%'";
}
if (Cbo_Gender.SelectedItem != null)
{
sql += " and a.pif_sex ='" + GetComboBoxValue(Cbo_Gender) + "'";
}
if (Text_ID.Text != "")
{
if (Text_ID.Text.Substring(0, 1) != "#")
{
sql += " and a.pif_ic like '%" + Text_ID.Text + "%'";
}
else
{
sql += " and a.pif_id='" + Text_ID.Text.Substring(1) + "'";
}
}
sql += " order by a.pif_id ";
this.CommandColumn1.Text = "查看信息";
//((GridCommand)this.CommandColumn1.Commands[0]).Text = "查看信息";
Store istore = GridList.GetStore();
istore.DataSource = db.GetDataArray_AddRowNum(db.Query(sql));
istore.DataBind();
}
