public DataTable getSub_item_code()
{
String strSQL = "select NVL(normal_data,(select normal_data from sub_item_code1 where item_code=sub_item_code.item_code and seq_no=sub_item_code.seq_no)) normal_data,* ";
strSQL += " from sub_item_code ";
DataTable dt = new DataTable();
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dt = conn.GetData(strSQL);
}
return(dt);
}
public DataTable getCode_file(string item_type)
{
String strSQL = "select * from code_file ";
strSQL += " where item_type= '";
strSQL += item_type + "'";
DataTable dt = new DataTable();
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dt = conn.GetData(strSQL);
}
return(dt);
}
public string GetPatVisitsList(string RegNo, string startDate, string endDate)
{
string strReturn = String.Empty;
// Transact-SQL 陳述式
string strSQL = "select opd_date,dep_no,'' typeName,doc_code Dor,'' DorName , '' out_date,room_no, '' bed_no,reg_bill keybill,reg_no,ill_code,ill_code1,ill_code2,ill_code3,card_no ,insurance ";
strSQL += " from v_all_opd_reg ";
strSQL += " where reg_no = '";
strSQL += RegNo + "'";
strSQL += " and rec_status='A' ";
strSQL += "and ((ins_remark<>'E' and ins_remark<>'J' and ins_remark<>'K' and ins_remark<>'U') or (ins_remark is null)) ";
strSQL += " and opd_date >= '";
strSQL += startDate + "' ";
strSQL += " and opd_date <= '";
strSQL += endDate + "' ";
//strSQL += " union all ";
////strSQL += "select in_date 日期,dep_no 科別,'' 科別名稱,doc_code 醫師,'' 醫生姓名,to_char(out_date,'%Y/%m/%d') 出院日,'住院' 診別, bed_no 床號,rel_house_no keybill,reg_no,'' ill_code,'' ill_code1,'' ill_code2,'' ill_code3,card_no,insurance ";
//strSQL += "select in_date ,dep_no ,'' typeName,doc_code Dor,'' DorName,to_char(out_date,'%Y/%m/%d') out_date,'住院' type, bed_no ,rel_house_no keybill,reg_no,'' ill_code,'' ill_code1,'' ill_code2,'' ill_code3,card_no,insurance ";
//strSQL += " from ipdhis@on2tcp:bed_file ";
//strSQL += " where reg_no = '";
//strSQL += RegNo + "' ";
//strSQL += " and in_date >= '";
//strSQL += startDate + "' ";
//strSQL += " and in_date <= '";
//strSQL += endDate + "' ";
//strSQL += " union all ";
////strSQL += " select in_date 日期,dep_no 科別,'' 科別名稱,doc_code 醫師,'' 醫生姓名,to_char(out_date,'%Y/%m/%d') 出院日,'住院' 診別, bed_no 床號,rel_house_no keybill,reg_no,'' ill_code,'' ill_code1,'' ill_code2,'' ill_code3,card_no,insurance ";
//strSQL += "select in_date ,dep_no ,'' typeName,doc_code Dor,'' DorName,to_char(out_date,'%Y/%m/%d') out_date,'住院' type, bed_no ,rel_house_no keybill,reg_no,'' ill_code,'' ill_code1,'' ill_code2,'' ill_code3,card_no,insurance ";
//strSQL += " from ipdhis@on2tcp:hist_bed ";
//strSQL += " where reg_no = '";
//strSQL += RegNo + "'";
//strSQL += " and out_date is not null ";
//strSQL += " and in_date >= '";
//strSQL += startDate + "' ";
//strSQL += " and in_date <= '";
//strSQL += endDate + "' ";
//strSQL += " order by opd_date desc ";
DataTable dt = new DataTable();
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dt = conn.GetData(strSQL);
}
strReturn = dt.Rows.Count > 0 ? JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented) : "0";
return(strReturn);
//將table整理成<li>格式的html字串後放入div中
//divRecord.InnerHtml = GenerateUL(dtBed);
}
public DataTable getExam_report(string reg_no, string startDate, string endDate)
{
//String strSQL = "select distinct DECODE(oi_flag,'I','住診',DECODE(oi_flag,'O','門診','急診')) 診別,order_date 門診日期 ";
String strSQL = "select distinct DECODE(oi_flag,'I','住診',DECODE(oi_flag,'O','門診','急診')) oi_flag,order_date";
strSQL += " from exam_report ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
//2013.05.27陳俊廷指示要加上區間 ︾
strSQL += " and order_date >= '";
strSQL += startDate + "'";
strSQL += " and order_date <= '";
strSQL += endDate + "'";
//2013.05.27陳俊廷指示要加上區間 ︽
strSQL += " and rec_status<>'R' and work_group<>'B' ";
strSQL += " union ";
strSQL += "select distinct DECODE(oi_flag,'I','住診',DECODE(oi_flag,'O','門診','急診')) oi_flag,order_date ";
strSQL += " from past_exam_report ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
//2013.05.27陳俊廷指示要加上區間 ︾
strSQL += " and order_date >= '";
strSQL += startDate + "'";
strSQL += " and order_date <= '";
strSQL += endDate + "'";
//2013.05.27陳俊廷指示要加上區間 ︽
strSQL += " and rec_status<>'R' and work_group<>'B' ";
strSQL += " union ";
strSQL += "select distinct DECODE(oi_flag,'I','住診',DECODE(oi_flag,'O','門診','急診')) oi_flag,order_date ";
strSQL += " from hispast@on1tcp:past_exam_report ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
//2013.05.27陳俊廷指示要加上區間 ︾
strSQL += " and order_date >= '";
strSQL += startDate + "'";
strSQL += " and order_date <= '";
strSQL += endDate + "'";
//2013.05.27陳俊廷指示要加上區間 ︽
strSQL += " and rec_status<>'R' and work_group<>'B' ";
//strSQL += " order by 門診日期 desc ";
strSQL += " order by order_date desc ";
DataTable dt = new DataTable();
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dt = conn.GetData(strSQL);
}
return(dt);
}
public DataTable getAllexam_report(string reg_no, string order_date, string oi_flag, Boolean vhistory_data)
{
String strSQL = "select exam_type,item_code,seq_no,'' normal_data,'' unit,'' NWD,* ";
strSQL += " from exam_report ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and order_date = '";
strSQL += order_date + "'";
strSQL += " and oi_flag='";
strSQL += oi_flag + "'";
strSQL += " and rec_status<>'R' and work_group<>'B' ";
if (vhistory_data)
{
strSQL += " union ";
strSQL += "select exam_type,item_code,seq_no,'' normal_data,'' unit,'' NWD,* ";
strSQL += " from past_exam_report ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and order_date = '";
strSQL += order_date + "'";
strSQL += " and oi_flag='";
strSQL += oi_flag + "'";
strSQL += " and rec_status<>'R' and work_group<>'B' ";
strSQL += " union ";
strSQL += "select exam_type,item_code,seq_no,'' normal_data,'' unit,'' NWD,* ";
strSQL += " from hispast@on1tcp:past_exam_report ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and order_date = '";
strSQL += order_date + "'";
strSQL += " and oi_flag='";
strSQL += oi_flag + "'";
strSQL += " and rec_status<>'R' and work_group<>'B' ";
}
strSQL += " order by 1,2,3 ";
DataTable dt = new DataTable();
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dt = conn.GetData(strSQL);
}
return(dt);
}
/// <summary>
///
/// </summary>
/// <param name="reg_no">病歷號</param>
/// <param name="item_code">檢查代號</param>
/// <param name="seq_no">序號</param>
/// <param name="exam_time">檢查時間</param>
/// <param name="bill_no">處方單號</param>
/// <param name="oi_flag">門急住診註記</param>
/// <param name="order_date">處方日期</param>
/// <param name="report_date">報告日期</param>
/// <returns></returns>
public DataTable getReportData(string reg_no, string item_code, string seq_no, string exam_time, string bill_no, string oi_flag, string order_date, string report_date)
{
string SQL = "";
DateTime now = DateTime.Now;
//取得描述性報告資料
SQL = "select * from exam_text_data ";
SQL += " where reg_no ='" + reg_no + "'";
SQL += " and order_date='" + order_date + "'";
SQL += " and bill_no='" + bill_no + "'";
SQL += " and oi_flag='" + oi_flag + "'";
SQL += " and item_code = '" + item_code + "'";
SQL += " and exam_time = '" + exam_time + "'";
SQL += " and seq_no='" + seq_no + "' and rec_status<>'R'";
if ((Convert.ToInt16(now.Year) - Convert.ToInt16(report_date.Substring(0, 4))) > 1)
{
SQL += " union";
SQL += " select * from past_exam_text";
SQL += " where reg_no ='" + reg_no + "'";
SQL += " and order_date='" + order_date + "'";
SQL += " and bill_no='" + bill_no + "'";
SQL += " and item_code = '" + item_code + "'";
SQL += " and exam_time = '" + exam_time + "'";
SQL += " and seq_no='" + seq_no + "' and rec_status<>'R'";
SQL += " union";
//SQL += " select text_data from '+ publiclib_DB_switch('pch01_hispast','past_exam_text'";
SQL += " select * from hispast@on1tcp:past_exam_text ";
SQL += " where reg_no ='" + reg_no + "'";
SQL += " and order_date='" + order_date + "'";
SQL += " and bill_no='" + bill_no + "'";
SQL += " and item_code = '" + item_code + "'";
SQL += " and exam_time = '" + exam_time + "'";
SQL += " and seq_no='" + seq_no + "' and rec_status<>'R'";
}
DataTable dtreport = new DataTable();
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dtreport = conn.GetData(SQL);
}
return(dtreport);
}
public string GetPatInfo(string ID)
{
string strReturn = String.Empty;
DataTable dtTemp = new DataTable();
string SQL = String.Empty;;
if (ID.Length > 6)
{
SQL = string.Format("Select to_char(birth_date) birth_date, p_name, sex, reg_no From reg_file Where id_no ='{0}'", ID);
}
else
{
SQL = string.Format("Select to_char(birth_date) birth_date, p_name, sex, reg_no From reg_file Where reg_no ='{0}'", ID);
}
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dtTemp = conn.GetData(SQL);
}
strReturn = dtTemp.Rows.Count > 0 ? JsonConvert.SerializeObject(dtTemp, Newtonsoft.Json.Formatting.Indented) : "0";
return(strReturn);
}
protected void aspbtnIDsearch_Click(object sender, EventArgs e)
{
initial_page();
string ID = asp_txtIDsearch.Text;
DataTable dt = new DataTable();
string SQL = String.Empty;
//找人
if (ID.Length > 6)
{
SQL = string.Format("Select to_char(birth_date) birth_date, p_name, sex, reg_no, id_no From reg_file Where id_no ='{0}'", ID);
}
else
{
SQL = string.Format("Select to_char(birth_date) birth_date, p_name, sex, reg_no, id_no From reg_file Where reg_no ='{0}'", ID);
}
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dt = conn.GetData(SQL);
}
foreach (DataRow dr in dt.Rows)
{
asp_lbRegNo.Text = dr["reg_no"].ToString().Trim();
asp_lbID.Text = dr["id_no"].ToString().Trim();
asp_lbPatName.Text = dr["p_name"].ToString().Trim();
asp_lbBirthDate.Text = dr["birth_date"].ToString().Trim();
asp_lbSex.Text = (dr["sex"].ToString().Trim() == "2") ? "女" : "男";
}
//找區間
search_opddate();
//btn_date_Click(asp_btn_onemonth, EventArgs.Empty); //預設找一個月(Button版)
strPageStatus = "Two";
str_selectDate = "";
}
protected void queryDepNo(object sender)
{
try
{
DataTable dt = new DataTable();
string sql = "select TRIM(description) As Text, TRIM(item_code) As Value from code_file where item_type = '07' and check_flag= 'Y'";
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dt = conn.GetData(sql);
}
DataView dv = dt.DefaultView;
dv.Sort = "Text ASC";
((System.Web.UI.WebControls.DropDownList)sender).DataTextField = "Text";
((System.Web.UI.WebControls.DropDownList)sender).DataValueField = "Value";
((System.Web.UI.WebControls.DropDownList)sender).DataSource = dv;
((System.Web.UI.WebControls.DropDownList)sender).DataBind();
((System.Web.UI.WebControls.DropDownList)sender).Items.Insert(0, new ListItem("", ""));
}
catch (Exception ex)
{
//PublicLib.handleError("", this.GetType().Name, ex.Message);
}
}
public DataTable getTab(string reg_no, Boolean vhistory_data, string startDate, string endDate)
{
DataTable dtTab = new DataTable();
dtTab.Columns.Add("Desc", typeof(String));
// 檢驗和檢驗彙總
String strSQL = "select distinct oi_flag,order_date ";
strSQL += " from exam_report ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and order_date >= '";
strSQL += startDate + "'";
strSQL += " and order_date <= '";
strSQL += endDate + "'";
strSQL += " and rec_status<>'R' ";
strSQL += " and work_group<>'B' ";
if (vhistory_data)
{
strSQL += " union ";
strSQL += " select distinct oi_flag,order_date ";
strSQL += " from past_exam_report ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and order_date >= '";
strSQL += startDate + "'";
strSQL += " and order_date <= '";
strSQL += endDate + "'";
strSQL += " and rec_status<>'R' ";
strSQL += " and work_group<>'B' ";
strSQL += " union ";
strSQL += " select distinct oi_flag,order_date ";
strSQL += " from hispast@on1tcp:past_exam_report ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and order_date >= '";
strSQL += startDate + "'";
strSQL += " and order_date <= '";
strSQL += endDate + "'";
strSQL += " and rec_status<>'R' ";
strSQL += " and work_group<>'B' ";
}
DataTable tmpDt = new DataTable();
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
tmpDt = conn.GetData(strSQL);
}
if (tmpDt.Rows.Count > 0)
{
dtTab.Rows.Add(new object[] { "檢驗" });
dtTab.Rows.Add(new object[] { "檢驗彙總" });
}
//X光
strSQL = "select distinct oi_flag,opd_date,report_date ";
strSQL += " from xray_sub_data ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and opd_date >= '";
strSQL += startDate + "'";
strSQL += " and opd_date <= '";
strSQL += endDate + "'";
strSQL += " and rec_status='A' ";
strSQL += " and xray_type<>'0' ";
if (vhistory_data)
{
strSQL += " union ";
strSQL += "select distinct oi_flag,opd_date,report_date ";
strSQL += " from past_xray_sub_data ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and opd_date >= '";
strSQL += startDate + "'";
strSQL += " and opd_date <= '";
strSQL += endDate + "'";
strSQL += " and rec_status='A' ";
strSQL += " and xray_type<>'0' ";
strSQL += " union ";
strSQL += "select distinct oi_flag,opd_date,report_date ";
strSQL += " from hispast@on1tcp:past_xray_sub_data ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and opd_date >= '";
strSQL += startDate + "'";
strSQL += " and opd_date <= '";
strSQL += endDate + "'";
strSQL += " and rec_status='A' ";
strSQL += " and xray_type<>'0' ";
}
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
tmpDt = conn.GetData(strSQL);
}
if (tmpDt.Rows.Count > 0)
{
dtTab.Rows.Add(new object[] { "X光" });
}
//微生物
string[] start_data = Convert.ToDateTime(startDate).ToString("yyyy/MM/dd").Split('/');
string[] end_data = Convert.ToDateTime(endDate).ToString("yyyy/MM/dd").Split('/');
//strSQL = "select distinct id_data,rep_date,rep_time ";
//String strSQL = "select distinct id_data,rep_date,rep_time ";
strSQL = "select distinct rep_date ";
strSQL += " from ordresult ";
strSQL += " where chart_no = '";
strSQL += reg_no + "'";
strSQL += " and rep_date >= '";
strSQL += (Convert.ToInt16(start_data[0]) - 1911).ToString() + start_data[1] + start_data[2] + "'";
strSQL += " and rep_date <= '";
strSQL += (Convert.ToInt16(end_data[0]) - 1911).ToString() + end_data[1] + end_data[2] + "'";
if (vhistory_data)
{
strSQL += " union ";
//strSQL += "select distinct id_data,rep_date,rep_time ";
strSQL += " select distinct rep_date ";
strSQL += " from past_ordresult ";
strSQL += " where chart_no = '";
strSQL += reg_no + "'";
strSQL += " and rep_date >= '";
strSQL += (Convert.ToInt16(start_data[0]) - 1911).ToString() + start_data[1] + start_data[2] + "'";
strSQL += " and rep_date <= '";
strSQL += endDate + "'";
strSQL += " union ";
//strSQL += "select distinct id_data,rep_date,rep_time ";
strSQL += " select distinct rep_date ";
strSQL += " from hispast@on1tcp:past_ordresult ";
strSQL += " where chart_no = '";
strSQL += reg_no + "'";
strSQL += " and rep_date >= '";
strSQL += (Convert.ToInt16(start_data[0]) - 1911).ToString() + start_data[1] + start_data[2] + "'";
strSQL += " and rep_date <= '";
strSQL += (Convert.ToInt16(end_data[0]) - 1911).ToString() + end_data[1] + end_data[2] + "'";
}
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
tmpDt = conn.GetData(strSQL);
}
if (tmpDt.Rows.Count > 0)
{
dtTab.Rows.Add(new object[] { "微生物" });
}
//檢查
strSQL = "select distinct oi_flag,opd_date,report_date ";
strSQL += " from check_wait_data ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and opd_date >= '";
strSQL += startDate + "'";
strSQL += " and opd_date <= '";
strSQL += endDate + "'";
strSQL += " and rec_status='A' ";
strSQL += " and test_type='2' ";
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
tmpDt = conn.GetData(strSQL);
}
if (tmpDt.Rows.Count > 0)
{
dtTab.Rows.Add(new object[] { "檢查" });
}
//精液分析
strSQL = "select * ";
strSQL += " from exam_semen ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and opd_date >= '";
strSQL += startDate + "'";
strSQL += " and opd_date <= '";
strSQL += endDate + "'";
strSQL += " and rec_status='A' ";
strSQL += " and test_status='1' ";
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
tmpDt = conn.GetData(strSQL);
}
if (tmpDt.Rows.Count > 0)
{
dtTab.Rows.Add(new object[] { "精液分析" });
}
//病理報告(只讀一年內)
strSQL = "select distinct patid ";
strSQL += " from pat010f ";
strSQL += " where reg_no = '00";
strSQL += reg_no + "'";
strSQL += " and aply_hsptl='00' ";
if (!vhistory_data)
{
DateTime dt = new DateTime();
dt = DateTime.Now;
dt = dt.AddDays(-365);
strSQL += " and tk_date>= ";
strSQL += dt.ToString("yyyy/M/dd") + "'";
}
strSQL += " order by patid desc ";
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
tmpDt = conn.GetData(strSQL);
}
if (tmpDt.Rows.Count > 0)
{
dtTab.Rows.Add(new object[] { "病理報告" });
}
//手術
strSQL = "select distinct a.or_date,a.or_no,a.reg_no ";
strSQL += " from or_info a,op_note b ";
strSQL += " where a.reg_no = '";
strSQL += reg_no + "'";
strSQL += " and b.reg_no= '";
strSQL += reg_no + "'";
strSQL += " and a.or_date >= '";
strSQL += startDate + "'";
strSQL += " and a.or_date <= '";
strSQL += endDate + "'";
strSQL += " and a.reg_no=b.reg_no and a.or_no=b.or_no and a.rec_status<>'R' order by a.or_date desc ";
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
tmpDt = conn.GetData(strSQL);
}
if (tmpDt.Rows.Count > 0)
{
dtTab.Rows.Add(new object[] { "手術" });
}
//EKG
strSQL = "select distinct STUDY_DATE ";
strSQL += " from ECGREPORT ";
strSQL += " where PATIENT_ID = '";
strSQL += reg_no + "'";
strSQL += " and STUDY_DATE >= '";
strSQL += start_data[0] + start_data[1] + start_data[2] + "'";
strSQL += " and STUDY_DATE <= '";
strSQL += end_data[0] + end_data[1] + end_data[2] + "'";
strSQL += " order by STUDY_DATE desc ";
tmpDt = GetEKGSqlData(strSQL);
if (tmpDt.Rows.Count > 0)
{
dtTab.Rows.Add(new object[] { "EKG" });
}
//腫瘤科報告
strSQL = "select * ";
strSQL += " from neo_report ";
strSQL += " where reg_no = '";
strSQL += reg_no + "'";
strSQL += " and report_date >= '";
strSQL += startDate + "'";
strSQL += " and report_date <= '";
strSQL += endDate + "'";
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
tmpDt = conn.GetData(strSQL);
}
if (tmpDt.Rows.Count > 0)
{
dtTab.Rows.Add(new object[] { "腫瘤科報告" });
}
//捷格表單系統
strSQL = "select a.*,b.examname ";
strSQL += " from css_rep a,css_tmp b ";
strSQL += " where spatientid= '";
strSQL += reg_no + "'";
strSQL += " and screatedate >= '";
strSQL += startDate + "'";
strSQL += " and screatedate <= '";
strSQL += endDate + "'";
strSQL += " and a.stype=b.examid ";
strSQL += "and a.sprotect<>'D' ";
//tmpDt = GetSqlData(strSQL);
//if (tmpDt.Rows.Count > 0)
//{
// dtTab.Rows.Add(new object[] { "特殊表單" });
//}
return(dtTab);
}
private void search_opddate()
{
string startDate = string.Empty;
string endDate = DateTime.Today.Date.ToShortDateString();
switch (ddl_OpdList.SelectedValue)
{
case "month":
startDate = DateTime.Now.AddMonths(-1).ToShortDateString();
break;
case "3months":
startDate = DateTime.Now.AddMonths(-3).ToShortDateString();
break;
case "6months":
startDate = DateTime.Now.AddMonths(-6).ToShortDateString();
break;
case "year":
startDate = DateTime.Now.AddYears(-1).ToShortDateString();
break;
case "3years":
startDate = DateTime.Now.AddYears(-3).ToShortDateString();
break;
case "5years":
startDate = DateTime.Now.AddYears(-5).ToShortDateString();
break;
case "free":
startDate = txt_FreeDate1.Text;
endDate = txt_FreeDate2.Text;
break;
default: //以上都不成立執行預設值
break;
}
string strSQL = "select to_char(opd_date,'%Y/%m/%d') opd_date,dep_no,'' typeName,doc_code Dor,'' DorName , '' out_date,DECODE(room_no,'ER','急診','門診') type, '' bed_no,reg_bill keybill,reg_no,ill_code,ill_code1,ill_code2,ill_code3,card_no ,insurance ";
strSQL += " from v_all_opd_reg ";
strSQL += " where reg_no = '";
strSQL += asp_lbRegNo.Text + "'";
strSQL += " and rec_status='A' ";
strSQL += "and ((ins_remark<>'E' and ins_remark<>'J' and ins_remark<>'K' and ins_remark<>'U') or (ins_remark is null)) ";
strSQL += " and opd_date >= '";
strSQL += startDate + "' ";
strSQL += " and opd_date <= '";
strSQL += endDate + "' ";
strSQL += " union all ";
//strSQL += "select in_date 日期,dep_no 科別,'' 科別名稱,doc_code 醫師,'' 醫生姓名,to_char(out_date,'%Y/%m/%d') 出院日,'住院' 診別, bed_no 床號,rel_house_no keybill,reg_no,'' ill_code,'' ill_code1,'' ill_code2,'' ill_code3,card_no,insurance ";
strSQL += "select to_char(in_date,'%Y/%m/%d') ,dep_no ,'' typeName,doc_code Dor,'' DorName,to_char(out_date,'%Y/%m/%d') out_date,'住院' type, bed_no ,rel_house_no keybill,reg_no,'' ill_code,'' ill_code1,'' ill_code2,'' ill_code3,card_no,insurance ";
strSQL += " from ipdhis@on2tcp:bed_file ";
strSQL += " where reg_no = '";
strSQL += asp_lbRegNo.Text + "' ";
strSQL += " and in_date >= '";
strSQL += startDate + "' ";
strSQL += " and in_date <= '";
strSQL += endDate + "' ";
strSQL += " union all ";
//strSQL += " select in_date 日期,dep_no 科別,'' 科別名稱,doc_code 醫師,'' 醫生姓名,to_char(out_date,'%Y/%m/%d') 出院日,'住院' 診別, bed_no 床號,rel_house_no keybill,reg_no,'' ill_code,'' ill_code1,'' ill_code2,'' ill_code3,card_no,insurance ";
strSQL += "select to_char(in_date,'%Y/%m/%d') ,dep_no ,'' typeName,doc_code Dor,'' DorName,to_char(out_date,'%Y/%m/%d') out_date,'住院' type, bed_no ,rel_house_no keybill,reg_no,'' ill_code,'' ill_code1,'' ill_code2,'' ill_code3,card_no,insurance ";
strSQL += " from ipdhis@on2tcp:hist_bed ";
strSQL += " where reg_no = '";
strSQL += asp_lbRegNo.Text + "'";
strSQL += " and out_date is not null ";
strSQL += " and in_date >= '";
strSQL += startDate + "' ";
strSQL += " and in_date <= '";
strSQL += endDate + "' ";
strSQL += " order by opd_date desc ";
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dt_VisitList = conn.GetData(strSQL);
}
PH_visits_tbody.Controls.Clear();
ddl_visits.Items.Clear();
if (dt_VisitList.Rows.Count > 30)
{
foreach (DataRow dr in dt_VisitList.Rows)
{
PH_visits_tbody.Controls.Add(new LiteralControl("<tr><td>"));
PH_visits_tbody.Controls.Add(creat_Linkbutton(dr));
PH_visits_tbody.Controls.Add(new LiteralControl("</td></tr>"));
}
}
else
{
foreach (DataRow dr in dt_VisitList.Rows)
{
ddl_visits.Items.Add(creat_ddlOption(dr));
}
ddl_visits.Items.Insert(0, new ListItem("請選擇就診日期", ""));
}
}
protected void btnSubmit_Click(object sender, ImageClickEventArgs e)
{
bool flag = true;
string strAlert = String.Empty;
string strUserName = String.Empty;
if (flag)
{
string strSql = string.Format("Select userid,username From users Where userid='{0}' And userpasswd='{1}'", txtUID.Text.Trim(), txtPwd.Text.Trim());
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dt = conn.GetData(strSql);
}
if (dt.Rows.Count <= 0)
{
flag = false;
strAlert += "此帳號不存在或密碼錯誤";
}
else
{
strUserName = dt.Rows[0]["username"].ToString().Trim();
}
}
if (flag)
{
string strSql = string.Format("Select FIRST 1 a.prog_id From users b Inner Join ctrl_mpriv a On a.user_id=b.userid Where a.user_id='{0}' Or prog_id='{1}'", dt.Rows[0]["userid"].ToString(), "-");
DataTable dt2 = new DataTable();
using (DevADODBConn conn = new DevADODBConn("pch01"))
{
dt2 = conn.GetData(strSql);
}
if (dt2.Rows.Count <= 0)
{
flag = false;
strAlert += "此帳號沒有本系統權限";
}
}
if (flag)
{
//string strSql = "If NOT EXISTS (Select ID From HMC_Permit Where UID=@UID And ProcID='hmc0000p')" +
// " Begin Insert Into HMC_Permit (ID, UID, ProcID, Cre_Date, Cre_User) Values (NEWID(), @UID, @ProcID, @Cre_Date, @Cre_User) End";
//SqlCommand cmd = new SqlCommand(strSql);
//cmd.Parameters.Add("@UID", System.Data.SqlDbType.VarChar).Value = dt.Rows[0]["userid"].ToString();
//cmd.Parameters.Add("@ProcID", System.Data.SqlDbType.VarChar).Value = "hmc0000p";
//cmd.Parameters.Add("@Cre_Date", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
//cmd.Parameters.Add("@Cre_User", System.Data.SqlDbType.VarChar).Value = dt.Rows[0]["userid"].ToString();
//DevConn conn = new DevConn();
//conn.ExecuteQuery(cmd);
//Session["UID"] = dt.Rows[0]["userid"].ToString();
Session["UID"] = txtUID.Text.Trim();
Session["UName"] = strUserName;
Response.Redirect(ResolveUrl("~/main.aspx"), false);
}
}
