private bool kiemTraCoMaCSKCB(string _MaCSKCB) { SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string query = "SELECT MaCSKCB FROM DanhMucCoSoKCB WHERE MaCSKCB = '" + _MaCSKCB + "'"; DataTable dt = DBUtils.GetDBTable(query, conn); if (dt.Rows.Count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } return(false); }
public void khoiTaoCauTruyVan_lookUpEdit() { SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string query = "SELECT MaDieuKien as 'Mã Câu Truy Vấn', TenDieuKien as 'Tên Câu Truy Vấn', DieuKien as 'Câu Truy Vấn SQL' FROM DanhMucDieuKienNgayGiuongNangCao"; DataTable dt = DBUtils.GetDBTable(query, conn); dtBangDieuKien = dt; soluongLookupEdit = dt.Rows.Count; DataRow row = dt.NewRow(); row["Mã Câu Truy Vấn"] = "Tất cả điều kiện"; row["Tên Câu Truy Vấn"] = "Tất cả điều kiện"; row["Câu Truy Vấn SQL"] = "Tất cả điều kiện"; dt.Rows.Add(row); this.lookUpEditCauTruyVan.Properties.DataSource = dt; this.lookUpEditCauTruyVan.Properties.DisplayMember = "Tên Câu Truy Vấn"; this.lookUpEditCauTruyVan.Properties.ValueMember = "Câu Truy Vấn SQL"; this.lookUpEditCauTruyVan.EditValue = "Tên Câu Truy Vấn"; this.lookUpEditCauTruyVan.Properties.PopulateColumns(); this.lookUpEditCauTruyVan.Properties.Columns[2].Visible = false; } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } }
public void khoiTaoDanhMuc() { SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string query = "SELECT MaCSKCB as 'Mã CSKCB', TenCSKCB as 'Tên CSKCB' FROM DanhMucCoSoKCB"; DataTable dt = DBUtils.GetDBTable(query, conn); DataColumn Col = dt.Columns.Add("STT"); Col.SetOrdinal(0); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["STT"] = i + 1; } this.gridControlMain.DataSource = dt; this.gridViewMain.Columns[0].BestFit(); this.gridViewMain.Columns[1].BestFit(); this.gridViewMain.Columns[2].Width = 800; } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } }
private void btnTruyVan_Click(object sender, EventArgs e) { if (this.lookUpEditCauTruyVan.EditValue.ToString() != "Tên Câu Truy Vấn") { //Code string _CauTruyVan = this.lookUpEditCauTruyVan.EditValue.ToString(); //_CauTruyVan = _CauTruyVan.Replace("bhyt14a", "dmQueryMau14a"); SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { DataTable dt = DBUtils.GetDBTable(_CauTruyVan, conn); this.gridControlKetQua.DataSource = dt; } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } } else { MessageBox.Show("Chưa chọn câu truy vấn"); } }
public void khoiTaoCauTruyVan_lookUpEdit() { SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string query = "SELECT MaDieuKien as 'Mã Câu Truy Vấn', TenDieuKien as 'Tên Câu Truy Vấn', DieuKien as 'Câu Truy Vấn SQL' FROM dmQueryMau14a"; DataTable dt = DBUtils.GetDBTable(query, conn); this.lookUpEditCauTruyVan.Properties.DataSource = dt; this.lookUpEditCauTruyVan.Properties.DisplayMember = "Tên Câu Truy Vấn"; this.lookUpEditCauTruyVan.Properties.ValueMember = "Câu Truy Vấn SQL"; this.lookUpEditCauTruyVan.EditValue = "Tên Câu Truy Vấn"; this.lookUpEditCauTruyVan.Properties.PopulateColumns(); this.lookUpEditCauTruyVan.Properties.Columns[2].Visible = false; } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } }
public void khoiTaoDVKTCoDieuKien() { SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string query = "SELECT MaDieuKien as 'Mã Điều Kiện', TenDieuKien as 'Tên Điều Kiện', DieuKien as 'Điều Kiện' FROM dmQueryPhauThuatThuThuat"; DataTable dt = DBUtils.GetDBTable(query, conn); DataColumn Col = dt.Columns.Add("STT"); Col.SetOrdinal(0); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["STT"] = i + 1; } this.gridControlMain.DataSource = dt; this.gridViewMain.Columns[0].BestFit(); this.gridViewMain.Columns[1].BestFit(); this.gridViewMain.Columns[2].BestFit(); this.gridViewMain.Columns[3].Width = 800; } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } }
private bool kiemTraCoMaDieuKien(string _MaDieuKien) { SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string query = "SELECT MaDieuKien FROM dmQueryPhauThuatThuThuat WHERE MaDieuKien = '" + _MaDieuKien + "'"; DataTable dt = DBUtils.GetDBTable(query, conn); if (dt.Rows.Count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } return(false); }
public void khoiTaoCoSoKCB_lookUpEdit() { SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string query = "SELECT MaCSKCB as 'Mã CSKCB', TenCSKCB as 'Tên CSKCB' FROM DanhMucCoSoKCB"; DataTable dt = DBUtils.GetDBTable(query, conn); DataRow row = dt.NewRow(); row["Mã CSKCB"] = "Tất cả CSKCB"; row["Tên CSKCB"] = "Tất cả CSKCB"; dt.Rows.Add(row); this.lookUpEditCSKCB.Properties.DataSource = dt; this.lookUpEditCSKCB.Properties.DisplayMember = "Tên CSKCB"; this.lookUpEditCSKCB.Properties.ValueMember = "Mã CSKCB"; this.lookUpEditCSKCB.EditValue = "Tên CSKCB"; } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } }
private void themVaoBangxml123() { //////////////////////////////////////////////////////////////////////////////// SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { List <string> _dm_xml123 = new List <string>(); List <string> _dm_schema_table = new List <string>(); string query = "SELECT MaCSKCB FROM dmxml123"; DataTable _dt_xml123 = DBUtils.GetDBTable(query, conn); for (int i = 0; i < _dt_xml123.Rows.Count; i++) { _dm_xml123.Add("xml123_" + _dt_xml123.Rows[i][0].ToString()); } query = "select table_name from information_schema.tables where table_name like '%xml123_%'"; DataTable _dt_schema_table = DBUtils.GetDBTable(query, conn); for (int i = 0; i < _dt_schema_table.Rows.Count; i++) { _dm_schema_table.Add(_dt_schema_table.Rows[i][0].ToString()); } for (int i = 0; i < _dm_schema_table.Count; i++) { bool flag = false; for (int j = 0; j < _dm_xml123.Count; j++) { if (_dm_schema_table[i].ToString() == _dm_xml123[j].ToString()) { flag = true; } } if (!flag) { //Chưa có, cần thêm vào table dmxml123 string maCSKCB = _dm_schema_table[i].ToString(); maCSKCB = maCSKCB.Split('_')[1].ToString(); query = "INSERT INTO dmxml123(MaCSKCB, TenCSKCB) VALUES('" + maCSKCB + "','Tên CSKCB')"; DBUtils.ExecuteNonQuery(query, conn); } } } catch (SqlException ex) { //MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } }
private void btnTruyVan_Click(object sender, EventArgs e) { if (this.lookUpEditCSKCB.EditValue.ToString() != "Tên CSKCB") { string _MaCSKCB = this.lookUpEditCSKCB.EditValue.ToString(); if (this.lookUpEditCauTruyVan.EditValue.ToString() != "Tên Câu Truy Vấn") { //Code string _ngayBatDau = this.tbThoiGianBatDau.Text; string _ngayKetThuc = this.tbThoiGianKetThuc.Text; string _CauTruyVan = this.lookUpEditCauTruyVan.EditValue.ToString(); _CauTruyVan = _CauTruyVan.Replace("_ngaybatdau_", _ngayBatDau); _CauTruyVan = _CauTruyVan.Replace("_ngayketthuc_", _ngayKetThuc); if (this.lookUpEditCSKCB.Text == "Tất cả CSKCB") { _CauTruyVan = _CauTruyVan.Replace(@"ma_cskcb = '_MaCSKCB_'", "1=1"); _CauTruyVan = _CauTruyVan.Replace(@"ma_cskcb='_MaCSKCB_'", "1=1"); _CauTruyVan = _CauTruyVan.Replace(@"ma_cskcb= '_MaCSKCB_'", "1=1"); _CauTruyVan = _CauTruyVan.Replace(@"ma_cskcb ='_MaCSKCB_'", "1=1"); _CauTruyVan = _CauTruyVan.Replace(@"MA_CSKCB = '_MaCSKCB_'", "1=1"); _CauTruyVan = _CauTruyVan.Replace(@"MA_CSKCB='_MaCSKCB_'", "1=1"); _CauTruyVan = _CauTruyVan.Replace(@"MA_CSKCB= '_MaCSKCB_'", "1=1"); _CauTruyVan = _CauTruyVan.Replace(@"MA_CSKCB ='_MaCSKCB_'", "1=1"); } else { _CauTruyVan = _CauTruyVan.Replace("_MaCSKCB_", _MaCSKCB); } SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { DataTable dt = DBUtils.GetDBTable(_CauTruyVan, conn); this.gridControlKetQua.DataSource = dt; } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } } else { MessageBox.Show("Chưa chọn câu truy vấn"); } } else { MessageBox.Show("Chưa chọn CSKCB"); } }
public void khoiTaoDVKTCoDieuKien() { SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string query = "SELECT MaDieuKien as 'Mã Điều Kiện', TenDieuKien as 'Tên Tiêu Chí', DieuKien as 'Điều Kiện' FROM DanhMucDieuKienDichVu"; DataTable dt = DBUtils.GetDBTable(query, conn); this.gridControlDieuKien.DataSource = dt; this.gridViewDieuKien.Columns[2].Visible = false; } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } }
public void khoiTaodmCSKCB() { DataTable dt = new DataTable(); DataColumn Col1 = dt.Columns.Add("Mã CSKCB"); DataColumn Col2 = dt.Columns.Add("Tên CSKCB"); Col1.SetOrdinal(0); Col2.SetOrdinal(1); SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { List <string> _dm_xml123 = new List <string>(); List <string> _dm_schema_table = new List <string>(); //Lấy dmcskcb trong bảng string query = "SELECT MaCSKCB, TenCSKCB FROM DanhMucCoSoKCB"; DataTable _dt_xml123 = DBUtils.GetDBTable(query, conn); for (int i = 0; i < _dt_xml123.Rows.Count; i++) { _dm_xml123.Add("xml123_" + _dt_xml123.Rows[i][0].ToString()); } //Lấy dmcskcb like xml123 trong csdl query = "select table_name from information_schema.tables where table_name like '%xml123_%'"; DataTable _dt_schema_table = DBUtils.GetDBTable(query, conn); for (int i = 0; i < _dt_schema_table.Rows.Count; i++) { _dm_schema_table.Add(_dt_schema_table.Rows[i][0].ToString()); } for (int i = 0; i < _dm_schema_table.Count; i++) { for (int j = 0; j < _dm_xml123.Count; j++) { if (_dm_schema_table[i].ToString().Trim() == _dm_xml123[j].ToString().Trim()) { //Thêm giá trị dòng mới dt.Rows.Add(new Object[] { _dt_xml123.Rows[j][0].ToString(), _dt_xml123.Rows[j][1].ToString() }); } } } } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } DataRow row = dt.NewRow(); row["Mã CSKCB"] = "Tất cả CSKCB"; row["Tên CSKCB"] = "Tất cả CSKCB"; dt.Rows.Add(row); DataColumn Col = dt.Columns.Add("STT"); Col.SetOrdinal(0); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["STT"] = i + 1; } this.lookUpEditCSKCB.Properties.DataSource = dt; this.lookUpEditCSKCB.Properties.DisplayMember = "Tên CSKCB"; this.lookUpEditCSKCB.Properties.ValueMember = "Mã CSKCB"; this.lookUpEditCSKCB.EditValue = "Tên CSKCB"; }
private void btnTruyVan_Click(object sender, EventArgs e) { tenCSKCB = ""; complinkMain = new CompositeLink(new PrintingSystem()); int soluongGridControl = soluongLookupEdit; int i_sl = 0; GridControl[] gridcontrolTemps = new GridControl[soluongGridControl + 1]; PrintableComponentLink[] linkTemps = new PrintableComponentLink[soluongGridControl + 1]; DataTable[] dt_Temps = new DataTable[soluongGridControl + 1]; int soLuongSheet = 0; maDieuKiens = new string[soluongGridControl]; tenDieuKiens = new string[soluongGridControl]; DieuKiens = new string[soluongGridControl]; if (this.lookUpEditCSKCB.EditValue.ToString() != "Tên CSKCB") { if (this.lookUpEditCSKCB.Text == "Tất cả CSKCB") { flag_excel = false; string query = "select table_name from information_schema.tables where table_name like '%xml123_%' and table_name not like 'xml123_dtdi'"; SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { DataTable _dt_schema_table = DBUtils.GetDBTable(query, conn); if (_dt_schema_table.Rows.Count > 0) { string temp = ""; string temp_cautruyvan = ""; if (this.lookUpEditCauTruyVan.EditValue.ToString() != "Tất cả điều kiện") { //Code string _ngayBatDau = this.tbThoiGianBatDau.Text; string _ngayKetThuc = this.tbThoiGianKetThuc.Text; string _CauTruyVan = this.lookUpEditCauTruyVan.EditValue.ToString(); _CauTruyVan = _CauTruyVan.Replace("_ngaybatdau_", _ngayBatDau); _CauTruyVan = _CauTruyVan.Replace("_ngayketthuc_", _ngayKetThuc); temp_cautruyvan = _CauTruyVan; temp = temp_cautruyvan.Replace("xml123", _dt_schema_table.Rows[0][0].ToString()); if (_dt_schema_table.Rows.Count > 1) { for (int i = 1; i < _dt_schema_table.Rows.Count; i++) { temp_cautruyvan = _CauTruyVan; string _MaCSKCB = _dt_schema_table.Rows[i][0].ToString(); temp = temp + " UNION ALL " + temp_cautruyvan.Replace("xml123", _MaCSKCB); } } //MessageBox.Show(temp); DataTable dt_alllll = DBUtils.GetDBTable(temp, conn); this.gridControlKetQua.DataSource = dt_alllll; } else { MessageBox.Show("Không được chọn tất cả các điều kiện!", "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } } else //Từng cơ sở khám chữa bệnh { string _MaCSKCB = this.lookUpEditCSKCB.EditValue.ToString(); tenCSKCB = _MaCSKCB; _MaCSKCB = "xml123_" + _MaCSKCB; if (this.lookUpEditCauTruyVan.EditValue.ToString() != "Tất cả điều kiện") { flag_excel = false; //Code string _ngayBatDau = this.tbThoiGianBatDau.Text; string _ngayKetThuc = this.tbThoiGianKetThuc.Text; string _CauTruyVan = this.lookUpEditCauTruyVan.EditValue.ToString(); _CauTruyVan = _CauTruyVan.Replace("_ngaybatdau_", _ngayBatDau); _CauTruyVan = _CauTruyVan.Replace("_ngayketthuc_", _ngayKetThuc); _CauTruyVan = _CauTruyVan.Replace("xml123", _MaCSKCB); SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { DataTable dt = DBUtils.GetDBTable(_CauTruyVan, conn); this.gridControlKetQua.DataSource = dt; } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } } else //Chọn tất cả các câu truy vấn. { flag_excel = true; /* * **************************************************** * **************************************************** * **************************************************** * **************************************************** * **************************************************** * **************************************************** * **************************************************** */ //MessageBox.Show(dtBangDieuKien.Rows.Count.ToString()); string DieuKien = ""; string query = ""; for (int j = 0; j < dtBangDieuKien.Rows.Count - 1; j++) { string _ngayBatDau = this.tbThoiGianBatDau.Text; string _ngayKetThuc = this.tbThoiGianKetThuc.Text; string _CauTruyVan = dtBangDieuKien.Rows[j][2].ToString(); _CauTruyVan = _CauTruyVan.Replace("_ngaybatdau_", _ngayBatDau); _CauTruyVan = _CauTruyVan.Replace("_ngayketthuc_", _ngayKetThuc); _CauTruyVan = _CauTruyVan.Replace("xml123", _MaCSKCB); query = _CauTruyVan; DieuKien = _CauTruyVan; maDieuKiens[i_sl] = dtBangDieuKien.Rows[j][0].ToString(); tenDieuKiens[i_sl] = dtBangDieuKien.Rows[j][1].ToString(); //Gridv SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { dt_Temps[i_sl] = DBUtils.GetDBTable(query, conn); DataColumn Col = dt_Temps[i_sl].Columns.Add("STT"); Col.SetOrdinal(0); for (int i = 0; i < dt_Temps[i_sl].Rows.Count; i++) { dt_Temps[i_sl].Rows[i]["STT"] = i + 1; } //MessageBox.Show(dt_Temps[i_sl].Rows.Count.ToString()); if (dt_Temps[i_sl].Rows.Count > 0) { gridcontrolTemps[i_sl] = new GridControl(); gridcontrolTemps[i_sl].BindingContext = new System.Windows.Forms.BindingContext(); gridcontrolTemps[i_sl].DataSource = dt_Temps[i_sl]; Form frm = new Form(); frm.Controls.Add(gridcontrolTemps[i_sl]); gridcontrolTemps[i_sl].ForceInitialize(); linkTemps[i_sl] = new PrintableComponentLink(new PrintingSystem()); linkTemps[i_sl].Component = gridcontrolTemps[i_sl]; //Bảng đổi tên soLuongSheets[soLuongSheet] = tenDieuKiens[i_sl]; soLuongMaDieuKienSheets[soLuongSheet] = maDieuKiens[i_sl]; soLuongSheet++; linkTemps[i_sl].CreateMarginalHeaderArea += new CreateAreaEventHandler(Link_CreateMarginalHeaderArea); complinkMain.Links.Add(linkTemps[i_sl]); } } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } //KẾT THÚC ĐIỀU KIỆN i_sl++; } MessageBox.Show("Đã khởi tạo xong dữ liệu!", "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } else { MessageBox.Show("Chưa chọn Cơ sở khám chữa bệnh!", "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
private void truyVanTheoCSKCBVoiCacDieuKien(string _MaCSKCB) { string MaCSKCB = _MaCSKCB; complinkMain = new CompositeLink(new PrintingSystem()); int soluongGridControl = this.gridViewDieuKien.GetSelectedRows().Length; int i_sl = 0; GridControl[] gridcontrolTemps = new GridControl[soluongGridControl + 1]; PrintableComponentLink[] linkTemps = new PrintableComponentLink[soluongGridControl + 1]; DataTable[] dt_Temps = new DataTable[soluongGridControl + 1]; maDieuKiens = new string[soluongGridControl]; tenDieuKiens = new string[soluongGridControl]; DieuKiens = new string[soluongGridControl]; string _ngayBatDau = this.tbThoiGianBatDau.Text; string _ngayKetThuc = this.tbThoiGianKetThuc.Text; string _menhDeWhereKyQuyetToan = "KY_QT BETWEEN " + _ngayBatDau + " AND " + _ngayKetThuc; _menhDeWhereKyQuyetToan = " AND (" + _menhDeWhereKyQuyetToan + ")"; string queryTongHopTatCaCacDieuKien = ""; int soLuongSheet = 0; if (this.gridViewDieuKien.GetSelectedRows().Length > 0) { string DieuKien = ""; ////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////// //Query Tổng hợp tất cả các điều kiện //SHEET 1 trong EXCEL xuất ra //Truy Vấn Theo GridControl /*foreach (int i in this.gridViewCSKCB.GetSelectedRows()) * { * * string MaCSKCB = this.gridViewCSKCB.GetDataRow(i)["Mã CSKCB"].ToString(); * }*/ //Truy Vấn Theo Look Up Edit queryTongHopTatCaCacDieuKien = traVeChuoiCacDieuKienDuocChonTheoMaCSKCB(MaCSKCB); //string queryTemp = "SELECT HO_TEN as 'Họ tên',MA_THE as 'Mã thẻ',NGAY_SINH as 'Ngày sinh',(CASE WHEN GIOI_TINH = '1' THEN 'Nam' ELSE 'Nu' END) as 'Giới tính',MA_DKBD as 'Mã ĐKBĐ',NGAY_VAO as 'Ngày vào',NGAY_RA as 'Ngày ra',MA_BENH as 'Mã bệnh',MA_BENHKHAC as 'Mã bệnh khác',TEN_BENH as 'Tên bệnh',ma_cp as 'Mã CP',TEN_CP as 'Tên CP',SO_LUONG_BV as 'Số lượng',don_gia as 'Đơn giá', thanh_tien as 'Thành tiền',NGAY_YL as 'Ngày y lệnh'"; string queryTemp = "SELECT HO_TEN as 'Họ tên',MA_THE as 'Mã thẻ',NGAY_SINH as 'Ngày sinh',(CASE WHEN GIOI_TINH = '1' THEN 'Nam' ELSE 'Nu' END) as 'Giới tính',MA_DKBD as 'Mã ĐKBĐ',NGAY_VAO as 'Ngày vào',NGAY_RA as 'Ngày ra',MA_BENH as 'Mã bệnh',MA_BENHKHAC as 'Mã bệnh khác',TEN_BENH as 'Tên bệnh'"; for (int j = 0; j < maDieuKiens.Length; j++) { //queryTemp = queryTemp + ", SUM(" + '"' + maDieuKiens[j] + '"' + ") as " + "'" + maDieuKiens[j] + "'"; queryTemp = queryTemp + ",( CASE WHEN SUM(" + '"' + maDieuKiens[j] + '"' + ") >= 1 THEN 'x' ELSE '' END) as " + "'" + maDieuKiens[j] + "'"; //(CASE WHEN GIOI_TINH = '1' THEN 'Nam' ELSE 'Nu' END) as 'Giới tính' } //queryTongHopTatCaCacDieuKien = queryTemp + " FROM " + "(" + queryTongHopTatCaCacDieuKien + ")" + " as test GROUP BY HO_TEN,MA_THE,NGAY_SINH,GIOI_TINH,MA_DKBD,NGAY_VAO,NGAY_RA,MA_BENH,MA_BENHKHAC,TEN_BENH,ma_cp,TEN_CP,SO_LUONG_BV,don_gia, thanh_tien,NGAY_YL"; queryTongHopTatCaCacDieuKien = queryTemp + " FROM " + "(" + queryTongHopTatCaCacDieuKien + ")" + " as test GROUP BY HO_TEN,MA_THE,NGAY_SINH,GIOI_TINH,MA_DKBD,NGAY_VAO,NGAY_RA,MA_BENH,MA_BENHKHAC,TEN_BENH"; SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { dt_Temps[soluongGridControl] = DBUtils.GetDBTable(queryTongHopTatCaCacDieuKien, conn); DataColumn Col = dt_Temps[soluongGridControl].Columns.Add("STT"); Col.SetOrdinal(0); for (int i = 0; i < dt_Temps[soluongGridControl].Rows.Count; i++) { dt_Temps[soluongGridControl].Rows[i]["STT"] = i + 1; } if (dt_Temps[soluongGridControl].Rows.Count > 0) { gridcontrolTemps[soluongGridControl] = new GridControl(); gridcontrolTemps[soluongGridControl].BindingContext = new System.Windows.Forms.BindingContext(); gridcontrolTemps[soluongGridControl].DataSource = dt_Temps[soluongGridControl]; //Hiển thị gridcontrol this.gridViewKetQua.Columns.Clear(); this.gridControlKetQua.DataSource = gridcontrolTemps[soluongGridControl].DataSource;; this.gridControlKetQua.Refresh(); Form frm = new Form(); frm.Controls.Add(gridcontrolTemps[soluongGridControl]); gridcontrolTemps[soluongGridControl].ForceInitialize(); linkTemps[soluongGridControl] = new PrintableComponentLink(new PrintingSystem()); linkTemps[soluongGridControl].Component = gridcontrolTemps[soluongGridControl]; linkTemps[soluongGridControl].CreateMarginalHeaderArea += new CreateAreaEventHandler(Link_CreateMarginalHeaderArea); complinkMain.Links.Add(linkTemps[soluongGridControl]); } } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } ////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////// string query = ""; foreach (int j in this.gridViewDieuKien.GetSelectedRows()) { //BẮT ĐẦU ĐIỀU KIỆN DieuKien = this.gridViewDieuKien.GetDataRow(j)["Điều Kiện"].ToString() + _menhDeWhereKyQuyetToan; maDieuKiens[i_sl] = this.gridViewDieuKien.GetDataRow(j)["Mã Điều Kiện"].ToString(); tenDieuKiens[i_sl] = this.gridViewDieuKien.GetDataRow(j)["Tên Tiêu Chí"].ToString(); /*Truy vấn theo nhiều Cơ sở khám chữa bệnh + Grid Control * query = ""; * if (this.gridViewCSKCB.GetSelectedRows().Length > 0) * { * bool flag_MaCSKCB = true; * //MessageBox.Show("Sl CSKCB: " + this.gridViewCSKCB.GetSelectedRows().Length.ToString()); * foreach (int i in this.gridViewCSKCB.GetSelectedRows()) * { * string MaCSKCB = this.gridViewCSKCB.GetDataRow(i)["Mã CSKCB"].ToString(); * //query = "(SELECT HO_TEN as 'Họ tên',MA_THE as 'Mã thẻ',NGAY_SINH as 'Ngày sinh',(CASE WHEN GIOI_TINH='1' THEN 'Nam' ELSE 'Nu' END) as 'Giới tính',MA_DKBD as 'Mã ĐKBĐ',NGAY_VAO as 'Ngày vào',NGAY_RA as 'Ngày ra',MA_BENH as 'Mã bệnh',MA_BENHKHAC as 'Mã bệnh khác',TEN_BENH as 'Tên bệnh',ma_cp as 'Mã CP',TEN_CP as 'Tên CP',SO_LUONG_BV as 'Số lượng',don_gia as 'Đơn giá', thanh_tien as 'Thành tiền',NGAY_YL as 'Ngày y lệnh' FROM xml123_" + MaCSKCB + " WHERE " + DieuKien + ")"; * if (flag_MaCSKCB) * { * flag_MaCSKCB = false; * query = "(SELECT HO_TEN as 'Họ tên',MA_THE as 'Mã thẻ',NGAY_SINH as 'Ngày sinh',(CASE WHEN GIOI_TINH='1' THEN 'Nam' ELSE 'Nu' END) as 'Giới tính',MA_DKBD as 'Mã ĐKBĐ',NGAY_VAO as 'Ngày vào',NGAY_RA as 'Ngày ra',MA_BENH as 'Mã bệnh',MA_BENHKHAC as 'Mã bệnh khác',TEN_BENH as 'Tên bệnh',ma_cp as 'Mã CP',TEN_CP as 'Tên CP',SO_LUONG_BV as 'Số lượng',don_gia as 'Đơn giá', thanh_tien as 'Thành tiền',NGAY_YL as 'Ngày y lệnh' FROM xml123_" + MaCSKCB + " WHERE " + DieuKien + ")"; * * } * else * { * query = query + " UNION (SELECT HO_TEN as 'Họ tên',MA_THE as 'Mã thẻ',NGAY_SINH as 'Ngày sinh',(CASE WHEN GIOI_TINH='1' THEN 'Nam' ELSE 'Nu' END) as 'Giới tính',MA_DKBD as 'Mã ĐKBĐ',NGAY_VAO as 'Ngày vào',NGAY_RA as 'Ngày ra',MA_BENH as 'Mã bệnh',MA_BENHKHAC as 'Mã bệnh khác',TEN_BENH as 'Tên bệnh',ma_cp as 'Mã CP',TEN_CP as 'Tên CP',SO_LUONG_BV as 'Số lượng',don_gia as 'Đơn giá', thanh_tien as 'Thành tiền',NGAY_YL as 'Ngày y lệnh' FROM xml123_" + MaCSKCB + " WHERE " + DieuKien + ")"; * } * } * * * } * else * { * MessageBox.Show("Chưa Chọn Cơ Sở KCB"); * } *///////////////////////////////////////////////////////////////////////////////////////////////// /*Truy vấn theo 1 Cơ sở khám chữa bệnh + Look Up Edit*/ query = "(SELECT HO_TEN as 'Họ tên',MA_THE as 'Mã thẻ',NGAY_SINH as 'Ngày sinh',(CASE WHEN GIOI_TINH='1' THEN 'Nam' ELSE 'Nu' END) as 'Giới tính',MA_DKBD as 'Mã ĐKBĐ',NGAY_VAO as 'Ngày vào',NGAY_RA as 'Ngày ra',MA_BENH as 'Mã bệnh',MA_BENHKHAC as 'Mã bệnh khác',TEN_BENH as 'Tên bệnh',ma_cp as 'Mã CP',TEN_CP as 'Tên CP',SO_LUONG_BV as 'Số lượng',don_gia as 'Đơn giá', thanh_tien as 'Thành tiền',NGAY_YL as 'Ngày y lệnh' FROM xml123_" + MaCSKCB + " WHERE " + DieuKien + ")"; ////////////////////////////////////////////////////////////////////////////////////////////////// //Gridv conn = DBUtils.GetDBConnection(); conn.Open(); try { dt_Temps[i_sl] = DBUtils.GetDBTable(query, conn); DataColumn Col = dt_Temps[i_sl].Columns.Add("STT"); Col.SetOrdinal(0); for (int i = 0; i < dt_Temps[i_sl].Rows.Count; i++) { dt_Temps[i_sl].Rows[i]["STT"] = i + 1; } //MessageBox.Show(dt_Temps[i_sl].Rows.Count.ToString()); if (dt_Temps[i_sl].Rows.Count > 0) { gridcontrolTemps[i_sl] = new GridControl(); gridcontrolTemps[i_sl].BindingContext = new System.Windows.Forms.BindingContext(); gridcontrolTemps[i_sl].DataSource = dt_Temps[i_sl]; Form frm = new Form(); frm.Controls.Add(gridcontrolTemps[i_sl]); gridcontrolTemps[i_sl].ForceInitialize(); linkTemps[i_sl] = new PrintableComponentLink(new PrintingSystem()); linkTemps[i_sl].Component = gridcontrolTemps[i_sl]; //Bảng đổi tên soLuongSheets[soLuongSheet] = tenDieuKiens[i_sl]; soLuongMaDieuKienSheets[soLuongSheet] = maDieuKiens[i_sl]; soLuongSheet++; linkTemps[i_sl].CreateMarginalHeaderArea += new CreateAreaEventHandler(Link_CreateMarginalHeaderArea); complinkMain.Links.Add(linkTemps[i_sl]); } } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } //KẾT THÚC ĐIỀU KIỆN i_sl++; } }//END MessageBox.Show("Truy vấn xong!"); }
private void btnTruyVan_Click(object sender, EventArgs e) { if (this.lookUpEditCSKCB.EditValue.ToString() != "Tên CSKCB") { if (this.lookUpEditCSKCB.Text == "Tất cả CSKCB") { string query = "select table_name from information_schema.tables where table_name like '%xml123_%' and table_name not like 'xml123_dtdi'"; SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { DataTable _dt_schema_table = DBUtils.GetDBTable(query, conn); if (_dt_schema_table.Rows.Count > 0) { string temp = ""; string temp_cautruyvan = ""; if (this.lookUpEditCauTruyVan.EditValue.ToString() != "Tên Câu Truy Vấn") { //Code string _ngayBatDau = this.tbThoiGianBatDau.Text; string _ngayKetThuc = this.tbThoiGianKetThuc.Text; string query_timkiem = "SELECT DieuKien1 , DieuKien2, DieuKien3 FROM DanhMucDieuKien3 where MaDieuKien = '" + this.lookUpEditCauTruyVan.EditValue.ToString() + "'"; DataTable dt_timkiem = DBUtils.GetDBTable(query_timkiem, conn); string _CauTruyVan1 = dt_timkiem.Rows[0][0].ToString(); string _CauTruyVan2 = dt_timkiem.Rows[0][1].ToString(); string _CauTruyVan3 = dt_timkiem.Rows[0][2].ToString(); _CauTruyVan1 = _CauTruyVan1.Replace("_ngaybatdau_", _ngayBatDau); _CauTruyVan1 = _CauTruyVan1.Replace("_ngayketthuc_", _ngayKetThuc); _CauTruyVan2 = _CauTruyVan2.Replace("_ngaybatdau_", _ngayBatDau); _CauTruyVan2 = _CauTruyVan2.Replace("_ngayketthuc_", _ngayKetThuc); _CauTruyVan3 = _CauTruyVan3.Replace("_ngaybatdau_", _ngayBatDau); _CauTruyVan3 = _CauTruyVan3.Replace("_ngayketthuc_", _ngayKetThuc); temp_cautruyvan = _CauTruyVan2; temp = temp_cautruyvan.Replace("xml123", _dt_schema_table.Rows[0][0].ToString()); if (_dt_schema_table.Rows.Count > 1) { for (int i = 1; i < _dt_schema_table.Rows.Count; i++) { temp_cautruyvan = _CauTruyVan2; string _MaCSKCB = _dt_schema_table.Rows[i][0].ToString(); temp = temp + " UNION ALL " + temp_cautruyvan.Replace("xml123", _MaCSKCB); } } //MessageBox.Show(temp); temp = _CauTruyVan1 + " " + temp + " " + _CauTruyVan3; DataTable dt = DBUtils.GetDBTable(temp, conn); this.gridControlKetQua.DataSource = dt; } } } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } } else { string _MaCSKCB = this.lookUpEditCSKCB.EditValue.ToString(); _MaCSKCB = "xml123_" + _MaCSKCB; if (this.lookUpEditCauTruyVan.EditValue.ToString() != "Tên Câu Truy Vấn") { SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string query_timkiem = "SELECT DieuKien1 , DieuKien2, DieuKien3 FROM DanhMucDieuKien3 where MaDieuKien = '" + this.lookUpEditCauTruyVan.EditValue.ToString() + "'"; DataTable dt_timkiem = DBUtils.GetDBTable(query_timkiem, conn); //Code string _ngayBatDau = this.tbThoiGianBatDau.Text; string _ngayKetThuc = this.tbThoiGianKetThuc.Text; string _CauTruyVan1 = dt_timkiem.Rows[0][0].ToString(); string _CauTruyVan2 = dt_timkiem.Rows[0][1].ToString(); string _CauTruyVan3 = dt_timkiem.Rows[0][2].ToString(); _CauTruyVan1 = _CauTruyVan1.Replace("_ngaybatdau_", _ngayBatDau); _CauTruyVan1 = _CauTruyVan1.Replace("_ngayketthuc_", _ngayKetThuc); _CauTruyVan2 = _CauTruyVan2.Replace("_ngaybatdau_", _ngayBatDau); _CauTruyVan2 = _CauTruyVan2.Replace("_ngayketthuc_", _ngayKetThuc); _CauTruyVan3 = _CauTruyVan3.Replace("_ngaybatdau_", _ngayBatDau); _CauTruyVan3 = _CauTruyVan3.Replace("_ngayketthuc_", _ngayKetThuc); _CauTruyVan2 = _CauTruyVan2.Replace("xml123", _MaCSKCB); _CauTruyVan2 = _CauTruyVan1 + " " + _CauTruyVan2 + " " + _CauTruyVan3; DataTable dt = DBUtils.GetDBTable(_CauTruyVan2, conn); this.gridControlKetQua.DataSource = dt; } catch (SqlException ex) { MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } } else { MessageBox.Show("Chưa chọn câu truy vấn"); } } } else { MessageBox.Show("Chưa chọn CSKCB"); } }
public void khoiTaodmCSKCB() { DataTable dt = new DataTable(); DataColumn Col1 = dt.Columns.Add("Mã CSKCB"); DataColumn Col2 = dt.Columns.Add("Tên CSKCB"); Col1.SetOrdinal(0); Col2.SetOrdinal(1); SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { /*Thêm giá trị mới * dt.Rows.Add(new Object[]{ * "giá trị 1", * "giá trị 2" * }); */ List <string> _dm_xml123 = new List <string>(); List <string> _dm_schema_table = new List <string>(); string query = "SELECT MaCSKCB, TenCSKCB FROM DanhMucCoSoKCB"; DataTable _dt_xml123 = DBUtils.GetDBTable(query, conn); for (int i = 0; i < _dt_xml123.Rows.Count; i++) { _dm_xml123.Add("xml123_" + _dt_xml123.Rows[i][0].ToString()); } query = "select table_name from information_schema.tables where table_name like '%xml123_%'"; DataTable _dt_schema_table = DBUtils.GetDBTable(query, conn); for (int i = 0; i < _dt_schema_table.Rows.Count; i++) { _dm_schema_table.Add(_dt_schema_table.Rows[i][0].ToString()); } for (int i = 0; i < _dm_schema_table.Count; i++) { for (int j = 0; j < _dm_xml123.Count; j++) { if (_dm_schema_table[i].ToString().Trim() == _dm_xml123[j].ToString().Trim()) { //Thêm giá trị dòng mới dt.Rows.Add(new Object[] { _dt_xml123.Rows[j][0].ToString(), _dt_xml123.Rows[j][1].ToString() }); } } } } catch (SqlException ex) { //MessageBox.Show("Error: " + ex.ToString()); } finally { conn.Close(); conn.Dispose(); } DataColumn Col = dt.Columns.Add("STT"); Col.SetOrdinal(0); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["STT"] = i + 1; } this.gridControlMain.DataSource = dt; this.gridViewMain.Columns[0].BestFit(); this.gridViewMain.Columns[1].BestFit(); this.gridViewMain.Columns[2].BestFit(); }