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); } }