private void btnLuu_Click(object sender, EventArgs e)
{
string maDieuKien = this.tbMaDieuKien.Text;
string tenDieuKien = this.tbTenDieuKien.Text;
string dieuKien = this.tbDieuKien.Text.Trim().Replace("\n", " ");
maDieuKien = maDieuKien.Replace("'", "''");
tenDieuKien = tenDieuKien.Replace("'", "''");
dieuKien = dieuKien.Replace("'", "''");
if (maDieuKien != null && tenDieuKien != null && dieuKien != null)
{
if (!this.kiemTraCoMaDieuKien(maDieuKien))
{
string query = "INSERT INTO dmQueryMau14a(MaDieuKien, TenDieuKien, DieuKien) VALUES(N'" + maDieuKien + "', N'" + tenDieuKien + "', N'" + dieuKien + "')";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
if (DBUtils.ExecuteNonQuery(query, conn) == 1)
{
MessageBox.Show("Thêm Điều Kiện Thành Công");
}
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
else
{
MessageBox.Show("Trùng Mã Điều Kiện");
}
}
//anTextBox();
khoiTaoDVKTCoDieuKien();
this.btnXoa.Visible = true;
this.btnSua.Visible = true;
}
private void themVaoCSDLxml123_3()
{
for (int i = 0; i < _FileNames.Count; i++)
{
string _FileName = _FileNames[i];
string _SafeFileName = _SafeFileNames[i];
string _MaCSKCB = (_SafeFileName.Split('.'))[0].ToString().Trim();
/////
khoitaoCSDL_2(_MaCSKCB);
//khoitaoCSDL(_MaCSKCB);
string tenProcedure = "Insert_xml123_" + _MaCSKCB;
string tenTable = "xml123_" + _MaCSKCB;
string tenType = "udt_xml123_" + _MaCSKCB;
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
DBUtils.ExecuteNonQueryStoredProcedure_DULIEUXML123(tenProcedure, conn, _FileName);
conn.Close();
conn.Dispose();
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
MessageBox.Show("Release done");
conn.Close();
conn.Dispose();
}
}
MessageBox.Show("xong");
}
private void btnSua_Click(object sender, EventArgs e)
{
string maDieuKien = this.tbMaDieuKien.Text;
string tenDieuKien = this.tbTenDieuKien.Text;
string dieuKien1 = this.tbDieuKien1.Text;
string dieuKien2 = this.tbDieuKien2.Text;
string dieuKien3 = this.tbDieuKien3.Text;
maDieuKien = maDieuKien.Replace("'", "''");
tenDieuKien = tenDieuKien.Replace("'", "''");
dieuKien1 = dieuKien1.Replace("'", "''");
dieuKien2 = dieuKien2.Replace("'", "''");
dieuKien3 = dieuKien3.Replace("'", "''");
if (maDieuKien != null && tenDieuKien != null)
{
string query = "UPDATE DanhMucDieuKien3 SET MaDieuKien = N'" + maDieuKien + "', TenDieuKien = N'" + tenDieuKien + "', DieuKien1 = N'" + dieuKien1 + "', DieuKien2 = N'" + dieuKien2 + "', DieuKien3 = N'" + dieuKien3 + "' WHERE MaDieuKien ='" + maDieuKien + "'";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
if (DBUtils.ExecuteNonQuery(query, conn) == 1)
{
MessageBox.Show("Đã Sửa Điều Kiện " + maDieuKien);
}
else
{
MessageBox.Show("Mã Điều Kiện Không Được Thay Đổi");
}
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
khoiTaoDVKTCoDieuKien();
}
private void btnLuu_Click(object sender, EventArgs e)
{
string MaCSKCB = this.tbMaCSKCB.Text;
string TenCSKCB = this.tbTenCSKCB.Text;
MaCSKCB = MaCSKCB.Replace("'", "''");
TenCSKCB = TenCSKCB.Replace("'", "''");
if (MaCSKCB != null && TenCSKCB != null)
{
if (!this.kiemTraCoMaCSKCB(MaCSKCB))
{
string query = "INSERT INTO DanhMucCoSoKCB(MaCSKCB, TenCSKCB) VALUES(N'" + MaCSKCB + "', N'" + TenCSKCB + "')";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
if (DBUtils.ExecuteNonQuery(query, conn) == 1)
{
MessageBox.Show("Thêm Điều Kiện Thành Công");
}
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
else
{
MessageBox.Show("Trùng Mã Điều Kiện");
}
}
//anTextBox();
khoiTaoDanhMuc();
this.btnXoa.Visible = true;
this.btnSua.Visible = true;
}
public void taoTable_2(string maCSKCB)
{
string tenTable = "xml123_" + maCSKCB;
string query = "";
query = "CREATE TABLE " + tenTable + "(ID numeric(18,0),XML1_ID numeric(18,0),Ky_QT numeric(10,0),CoSoKCB_ID numeric(18,0),Ma_CSKCB nvarchar(10),"
+ "Ma_LK nvarchar(100),MA_BN nvarchar(100),Ho_Ten nvarchar(1000),Ngay_Sinh nvarchar(15),Gioi_Tinh nvarchar(2),Ma_The nvarchar(20),Ma_DKBD nvarchar(10),"
+ "GT_The_Tu nvarchar(25),GT_The_Den nvarchar(25),Mien_Cung_CT numeric(18,2),Ngay_Vao nvarchar(25),Ngay_Ra nvarchar(25),So_Ngay_DTri numeric(10,0),Ma_LyDo_VVien nvarchar(2),Ma_Benh nvarchar(100),Ma_BenhKhac nvarchar(1000),Muc_Huong_XML1 numeric(18,2),T_TongChi numeric(18,0),T_BNTT numeric(18,0),T_BHTT numeric(18,0),T_BNCCT numeric(18,0),"
+ "T_XN numeric(18,0),T_CDHA numeric(18,0), T_Thuoc numeric(18,0),T_Mau numeric(18,0),T_TTPT numeric(18,0),T_VTYT numeric(18,0),T_DVKT_TyLe numeric(18,0),T_Thuoc_TyLe numeric(18,0),T_VTYT_TyLe numeric(18,0),"
+ "T_Kham numeric(18,0),T_Giuong numeric(18,0),T_VChuyen numeric(18,0),T_NgoaiDS numeric(18,0),T_NguonKhac numeric(18,0),Ma_Loai_KCB nvarchar(2),ID_CP numeric(18,0),Loai_CP nvarchar(10),Ma_CP nvarchar(40),"
+ "Ma_Vat_Tu nvarchar(40),Ma_Nhom nvarchar(3),Ten_CP nvarchar(300),DVT nvarchar(100),So_Dang_Ky nvarchar(100),Ham_Luong nvarchar(100),Duong_Dung nvarchar(100),So_Luong numeric(18,2),"
+ "So_Luong_BV numeric(18,2),Don_Gia numeric(18,2),Don_Gia_BV numeric(18,2),Thanh_Tien numeric(18,2),TyLe_TT numeric(18,2),Ngay_YL nvarchar(25),"
+ "Ngay_KQ nvarchar(25),T_NguonKhac_DTL numeric(18,0),T_BNTT_DTL numeric(18,0),"
+ "T_BHTT_DTL numeric(18,0),T_BNCCT_DTL numeric(18,0),T_NgoaiDS_DTL numeric(18,0),Muc_Huong_DTL numeric(10,0),TT_Thau nvarchar(100),Pham_Vi nvarchar(100),Ma_Giuong nvarchar(50),"
+ "T_TranTT numeric(18,2),Goi_VTYT nvarchar(100),Ten_Vat_Tu nvarchar(300),"
+ "Ten_Khoa nvarchar(100),Ma_Khoa nvarchar(20),Ma_Khoa_XML1 nvarchar(20),Ten_Khoa_XML1 nvarchar(300),Ten_Benh nvarchar(300),Ma_Bac_Si nvarchar(20),Ma_Tinh nvarchar(5),Ma_Tinh_The nvarchar(5))";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
if (DBUtils.ExecuteNonQuery(query, conn) > 0)
{
MessageBox.Show("Tạo Table Thành Công");
}
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
private void btnXoa_Click(object sender, EventArgs e)
{
if (this.lbMaCSKCB.Text != null && this.lbTenCSKCB.Text != null)
{
string maCSKCB = this.lbMaCSKCB.Text;
string tenCSKCB = this.lbTenCSKCB.Text;
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
//Xóa dòng trong bảng dmxml123
//DBUtils.ExecuteNonQuery(query, conn);
//Xóa luôn table xml123_maCSKCB
string tenBangCanXoa = "xml123_" + maCSKCB;
string query = "DROP TABLE " + tenBangCanXoa;
DBUtils.ExecuteNonQuery(query, conn);
MessageBox.Show("Xóa CSKCB " + maCSKCB + " Thành Công");
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
else
{
MessageBox.Show("Chưa Chọn Dữ Liệu Cần Xóa!");
}
khoiTaodmCSKCB();
this.lbSTT.Text = "";
this.lbMaCSKCB.Text = "";
this.lbTenCSKCB.Text = "";
}
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();
}
}
private void btnSua_Click(object sender, EventArgs e)
{
string MaCSKCB = this.tbMaCSKCB.Text;
string TenCSKCB = this.tbTenCSKCB.Text;
MaCSKCB = MaCSKCB.Replace("'", "''");
TenCSKCB = TenCSKCB.Replace("'", "''");
if (MaCSKCB != null && TenCSKCB != null)
{
string query = "UPDATE DanhMucCoSoKCB SET MaCSKCB = N'" + MaCSKCB + "', TenCSKCB = N'" + TenCSKCB + "' WHERE MaCSKCB ='" + MaCSKCB + "'";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
if (DBUtils.ExecuteNonQuery(query, conn) == 1)
{
MessageBox.Show("Đã Sửa Điều Kiện " + MaCSKCB);
}
else
{
MessageBox.Show("Mã Điều Kiện Không Được Thay Đổi");
}
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
khoiTaoDanhMuc();
}
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', DieuKien1 as 'Điều Kiện 1', DieuKien2 as 'Điều Kiện 2', DieuKien3 as 'Điều Kiện 3' FROM DanhMucDieuKien3";
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].BestFit();
this.gridViewMain.Columns[4].BestFit();
this.gridViewMain.Columns[5].BestFit();
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
public void taoProcedure(string maCSKCB)
{
string tenProcedure = "Insert_xml123_" + maCSKCB;
string tenTable = "xml123_" + maCSKCB;
string tenType = "udt_xml123_" + maCSKCB;
string query = "CREATE PROCEDURE " + tenProcedure + " "
+ "@xml123 " + tenType + " READONLY "
+ "AS "
+ "BEGIN "
+ "INSERT INTO " + tenTable + " "
+ "(ID, XML1_ID, Ky_QT, CoSoKCB_ID, Ma_CSKCB, Ma_LK, MA_BN, Ho_Ten, Ngay_Sinh, Gioi_Tinh, Ma_The, Ma_DKBD, GT_The_Tu, GT_The_Den, Mien_Cung_CT, Ngay_Vao, Ngay_Ra, So_Ngay_DTri, Ma_LyDo_VVien, Ma_Benh, Ma_BenhKhac, Muc_Huong_XML1, T_TongChi, T_BNTT, T_BHTT, T_BNCCT, T_XN, T_CDHA, T_Thuoc, T_Mau, T_TTPT, T_VTYT, T_DVKT_TyLe, T_Thuoc_TyLe, T_VTYT_TyLe, T_Kham, T_Giuong, T_VChuyen, T_NgoaiDS, T_NguonKhac, Ma_Loai_KCB, ID_CP, Loai_CP, Ma_CP, Ma_Vat_Tu, Ma_Nhom, Ten_CP, DVT, So_Dang_Ky, Ham_Luong, Duong_Dung, So_Luong, So_Luong_BV, Don_Gia, Don_Gia_BV, Thanh_Tien, TyLe_TT, Ngay_YL, Ngay_KQ, T_NguonKhac_DTL, T_BNTT_DTL, T_BHTT_DTL, T_BNCCT_DTL, T_NgoaiDS_DTL, Muc_Huong_DTL, TT_Thau, Pham_Vi, Ma_Giuong, T_TranTT, Goi_VTYT, Ten_Vat_Tu, Ten_Khoa, Ma_Khoa, Ma_Khoa_XML1, Ten_Khoa_XML1, Ten_Benh, Ma_Bac_Si, Ma_Tinh, Ma_Tinh_The) "
+ "SELECT ID, XML1_ID, Ky_QT, CoSoKCB_ID, Ma_CSKCB, Ma_LK, MA_BN, Ho_Ten, Ngay_Sinh, Gioi_Tinh, Ma_The, Ma_DKBD, GT_The_Tu, GT_The_Den, Mien_Cung_CT, Ngay_Vao, Ngay_Ra, So_Ngay_DTri, Ma_LyDo_VVien, Ma_Benh, Ma_BenhKhac, Muc_Huong_XML1, T_TongChi, T_BNTT, T_BHTT, T_BNCCT, T_XN, T_CDHA, T_Thuoc, T_Mau, T_TTPT, T_VTYT, T_DVKT_TyLe, T_Thuoc_TyLe, T_VTYT_TyLe, T_Kham, T_Giuong, T_VChuyen, T_NgoaiDS, T_NguonKhac, Ma_Loai_KCB, ID_CP, Loai_CP, Ma_CP, Ma_Vat_Tu, Ma_Nhom, Ten_CP, DVT, So_Dang_Ky, Ham_Luong, Duong_Dung, So_Luong, So_Luong_BV, Don_Gia, Don_Gia_BV, Thanh_Tien, TyLe_TT, Ngay_YL, Ngay_KQ, T_NguonKhac_DTL, T_BNTT_DTL, T_BHTT_DTL, T_BNCCT_DTL, T_NgoaiDS_DTL, Muc_Huong_DTL, TT_Thau, Pham_Vi, Ma_Giuong, T_TranTT, Goi_VTYT, Ten_Vat_Tu, Ten_Khoa, Ma_Khoa, Ma_Khoa_XML1, Ten_Khoa_XML1, Ten_Benh, Ma_Bac_Si, Ma_Tinh, Ma_Tinh_The "
+ "FROM @xml123 "
+ "END";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
if (DBUtils.ExecuteNonQuery(query, conn) > 0)
{
///MessageBox.Show("Tạo Procedure Thành Công");
}
}
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 themVaoCSDLCoSoKCB_BULKCOPY()
{
string _FileName = _FileNames[0];
string tenTable = "DanhMucCoSoKCB";
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
string query = "DBCC DROPCLEANBUFFERS";
DBUtils.ExecuteNonQuery(query, conn);
query = "DBCC FREEPROCCACHE";
DBUtils.ExecuteNonQuery(query, conn);
//Xóa table nếu tồn tại
query = "begin try drop table " + tenTable + " end try begin catch end catch";
DBUtils.ExecuteNonQuery(query, conn);
//Tạo table
taoTable_dmCSKCB(tenTable);
try
{
string constr = "";
if (System.IO.Path.GetExtension(_FileName).ToUpper() == ".XLS")
{
constr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;""", _FileName);
}
else
{
constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;""", _FileName);
}
constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;""", _FileName);
OleDbConnection Econ = new OleDbConnection(constr);
string Query = string.Format("Select [MACSKCB], [TENCSKCB] FROM [{0}]", "Sheet1$");
OleDbCommand Ecom = new OleDbCommand(Query, Econ);
Econ.Open();
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
Exceldt.AcceptChanges();
SqlBulkCopy objbulk = new SqlBulkCopy(conn);
objbulk.DestinationTableName = tenTable;
//Mapping Table column
objbulk.ColumnMappings.Add("[MACSKCB]", "MACSKCB");
objbulk.ColumnMappings.Add("[TENCSKCB]", "TENCSKCB");
objbulk.WriteToServer(Exceldt);
MessageBox.Show("Ghi thành công danh mục Cơ sở Khám chữa bệnh!", "Thành Công", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show(string.Format("Dữ liệu không đúng định dạng!"), "Không Thành Công", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
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");
}
}
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 _ngayBatDau2 = this.tbThoiGianBatDau2.Text;
string _ngayKetThuc2 = this.tbThoiGianKetThuc2.Text;
string _CauTruyVan = this.lookUpEditCauTruyVan.EditValue.ToString();
_CauTruyVan = _CauTruyVan.Replace("_ngaybatdau_", _ngayBatDau);
_CauTruyVan = _CauTruyVan.Replace("_ngayketthuc_", _ngayKetThuc);
_CauTruyVan = _CauTruyVan.Replace("_ngaybatdau2_", _ngayBatDau2);
_CauTruyVan = _CauTruyVan.Replace("_ngayketthuc2_", _ngayKetThuc2);
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");
}
}
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!");
}
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();
}
private void themVaoCSDLxml123_2()
{
for (int i = 0; i < _FileNames.Count; i++)
{
string _FileName = _FileNames[i];
string _SafeFileName = _SafeFileNames[i];
SQLiteHelper.setConnString(_FileName);
int _tongSoDong = int.Parse(SQLiteHelper.loadDatafromDB("SELECT COUNT(ID) FROM xml123").Rows[0][0].ToString());
//MessageBox.Show(_tongSoDong.ToString());
string _MaCSKCB = (_SafeFileName.Split('.'))[0].ToString().Trim();
/////
//khoitaoCSDL_2(_MaCSKCB);
khoitaoCSDL(_MaCSKCB);
string tenProcedure = "Insert_xml123_" + _MaCSKCB;
string tenTable = "xml123_" + _MaCSKCB;
string tenType = "udt_xml123_" + _MaCSKCB;
for (int j = 0; j < _tongSoDong; j = j + 100000)
{
int _start = j;
int _end = 100000;
if ((j + 100000) > _tongSoDong)
{
_start = j;
_end = _tongSoDong - j;
}
SQLiteHelper.setConnString(_FileName);
DataTable dt = SQLiteHelper.loadDatafromDB("SELECT ID, XML1_ID, Ky_QT, CoSoKCB_ID, Ma_CSKCB, Ma_LK, MA_BN, Ho_Ten, Ngay_Sinh, Gioi_Tinh, Ma_The, Ma_DKBD, GT_The_Tu, GT_The_Den, Mien_Cung_CT, Ngay_Vao, Ngay_Ra, So_Ngay_DTri, Ma_LyDo_VVien, Ma_Benh, Ma_BenhKhac, Muc_Huong_XML1, T_TongChi, T_BNTT, T_BHTT, T_BNCCT, T_XN, T_CDHA, T_Thuoc, T_Mau, T_TTPT, T_VTYT, T_DVKT_TyLe, T_Thuoc_TyLe, T_VTYT_TyLe, T_Kham, T_Giuong, T_VChuyen, T_NgoaiDS, T_NguonKhac, Ma_Loai_KCB, ID_CP, Loai_CP, Ma_CP, Ma_Vat_Tu, Ma_Nhom, Ten_CP, DVT, So_Dang_Ky, Ham_Luong, Duong_Dung, So_Luong, So_Luong_BV, Don_Gia, Don_Gia_BV, Thanh_Tien, TyLe_TT, Ngay_YL, Ngay_KQ, T_NguonKhac_DTL, T_BNTT_DTL, T_BHTT_DTL, T_BNCCT_DTL, T_NgoaiDS_DTL, Muc_Huong_DTL, TT_Thau, Pham_Vi, Ma_Giuong, T_TranTT, Goi_VTYT, Ten_Vat_Tu, Ten_Khoa, Ma_Khoa, Ma_Khoa_XML1, Ten_Khoa_XML1, Ten_Benh, Ma_Bac_Si, Ma_Tinh, Ma_Tinh_The FROM xml123 LIMIT " + _start + ", " + _end);
SqlConnection conn = DBUtils.GetDBConnection();
//MessageBox.Show("j:" + _start.ToString() + "_end:" + _end.ToString() + "_sodong: " + dt.Rows.Count.ToString());
conn.Open();
try
{
DBUtils.ExecuteNonQueryStoredProcedure_xml123(tenProcedure, dt, conn);
//DBUtils.ExecuteNonQuery("DBCC FREEPROCCACHE", conn);
/*
*
* for (int k = 0; k < dt.Rows.Count; k++)
* {
* DBUtils.ExecuteNonQueryStoredProcedure_xml123_2(tenProcedure, dt.Rows[k], conn);
* }*/
conn.Close();
conn.Dispose();
dt.Clear();
dt.Dispose();
}
catch (SqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
//MessageBox.Show("Release done");
//conn.Close();
//conn.Dispose();
//dt.Clear();
//dt.Dispose();
}
}
/*
* int _start = 0;
* int _end = 100000;
* SQLiteHelper.setConnString(_FileName);
*
*
* DataTable dt = SQLiteHelper.loadDatafromDB("SELECT ID, XML1_ID, Ky_QT, CoSoKCB_ID, Ma_CSKCB, Ma_LK, MA_BN, Ho_Ten, Ngay_Sinh, Gioi_Tinh, Ma_The, Ma_DKBD, GT_The_Tu, GT_The_Den, Mien_Cung_CT, Ngay_Vao, Ngay_Ra, So_Ngay_DTri, Ma_LyDo_VVien, Ma_Benh, Ma_BenhKhac, Muc_Huong_XML1, T_TongChi, T_BNTT, T_BHTT, T_BNCCT, T_XN, T_CDHA, T_Thuoc, T_Mau, T_TTPT, T_VTYT, T_DVKT_TyLe, T_Thuoc_TyLe, T_VTYT_TyLe, T_Kham, T_Giuong, T_VChuyen, T_NgoaiDS, T_NguonKhac, Ma_Loai_KCB, ID_CP, Loai_CP, Ma_CP, Ma_Vat_Tu, Ma_Nhom, Ten_CP, DVT, So_Dang_Ky, Ham_Luong, Duong_Dung, So_Luong, So_Luong_BV, Don_Gia, Don_Gia_BV, Thanh_Tien, TyLe_TT, Ngay_YL, Ngay_KQ, T_NguonKhac_DTL, T_BNTT_DTL, T_BHTT_DTL, T_BNCCT_DTL, T_NgoaiDS_DTL, Muc_Huong_DTL, TT_Thau, Pham_Vi, Ma_Giuong, T_TranTT, Goi_VTYT, Ten_Vat_Tu, Ten_Khoa, Ma_Khoa, Ma_Khoa_XML1, Ten_Khoa_XML1, Ten_Benh, Ma_Bac_Si, Ma_Tinh, Ma_Tinh_The FROM xml123 LIMIT " + _start + ", " + _end);
*
*
*
* SqlConnection conn = DBUtils.GetDBConnection();
* conn.Open();
* try
* {
*
* for (int j = 0; j < dt.Rows.Count; j++)
* {
* DBUtils.ExecuteNonQueryStoredProcedure_xml123_2(tenProcedure, dt.Rows[j], conn);
* }
* conn.Close();
* conn.Dispose();
* }
* catch (SqlException ex)
* {
* MessageBox.Show("Error: " + ex.ToString());
* }
* finally
* {
* }
*
*/
MessageBox.Show("xong");
}
}