//thong ke bao cao - button In
private void btnIn_Click(object sender, EventArgs e)
{
#region Code ban dau
////rpBaoCaoKhamBenh rp = new rpBaoCaoKhamBenh();
//dsBaoCaoKhamBenh dataset = new dsBaoCaoKhamBenh();
////Instantiate variables
//ReportDocument reportDocument = new ReportDocument();
//ParameterField paramField = new ParameterField();
//ParameterFields paramFields = new ParameterFields();
//ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
////Set instances for input parameter 1 - @vDepartment
//paramField.Name = "TUNGAY";
////Below variable can be set to any data present in SalseData table, Department column
//paramDiscreteValue.Value = dtpTuNgay1.Value.ToString();
//paramField.CurrentValues.Add(paramDiscreteValue);
////Add the paramField to paramFields
//paramFields.Add(paramField);
////Set instances for input parameter 2 - @iSalseYear
////*Remember to reconstruct the paramDiscreteValue and paramField objects
//paramField = new ParameterField();
//paramField.Name = "DENNGAY";
//paramDiscreteValue = new ParameterDiscreteValue();
//paramDiscreteValue.Value = dtpDenNgay1.Value.ToString();
//paramField.CurrentValues.Add(paramDiscreteValue);
////Add the paramField to paramFields
//paramFields.Add(paramField);
//crystalReportViewer4.ParameterFieldInfo = paramFields;
//reportDocument.Load(@"..\..\..\QuanLyBenhVien\rpBaoCaoKhamBenh.rpt");
////reportDocument.Load(@"D:\Hoc tap\Lap trinh CSDL\Quan ly benh vien\Do an Quan Ly Benh Vien\Code\QuanLyBenhVien\QuanLyBenhVien\rpBaoCaoKhamBenh.rpt");
////set the database loggon information.
////**Note that the third parameter is the DSN name and not the Database or System name
////reportDocument.SetDatabaseLogon("", "", "", "");
//reportDocument.SetDataSource((DataTable)dataset.BAOCAO_BENHNHAN);
////Load the report by setting the report source
//crystalReportViewer4.ReportSource = reportDocument;
//// ConnectionInfo CInfo = new ConnectionInfo();
//// CInfo.ServerName = "XUBI-LAP"; // This Should be the same name What we have Configured under Oledb(ADO)
//// CInfo.UserID = "xubi";
//// CInfo.Password = "******";
//// TableLogOnInfo tableInfo = new TableLogOnInfo(); // providing table Details use the Seperate object of TableLogOnInfo Class for each table we use in the Report
//// tableInfo.ConnectionInfo = CInfo;
//// TableLogOnInfos tablelog = new TableLogOnInfos();
//// tablelog.Add(tableInfo);
//// crystalReportViewer1.ReportSource = @"D:\Hoc tap\Lap trinh CSDL\Quan ly benh vien\Do an Quan Ly Benh Vien\Code\QuanLyBenhVien\QuanLyBenhVien\rpBaoCaoKhamBenh.rpt"; //<"Path of the report">;
//// crystalReportViewer1.LogOnInfo = tablelog;
////// Using Above Coding to Skip/Elliminate user Id,passaword i.e.Connection Datails
//// // string cname = ConfigurationManager.AppSettings.Get("CName");
//// // string addr = ConfigurationManager.AppSettings.Get("Addr");
//// //string Website = ConfigurationManager.AppSettings.Get("WebSite");
//// ReportDocument obj = new ReportDocument();
//// obj.Load(@"D:\Hoc tap\Lap trinh CSDL\Quan ly benh vien\Do an Quan Ly Benh Vien\Code\QuanLyBenhVien\QuanLyBenhVien\rpBaoCaoKhamBenh.rpt");
//// crystalReportViewer1.ReportSource = obj;
//// //obj.SetParameterValue("CompanyName", cname);
//// //obj.SetParameterValue("Address", addr);
//// // obj.SetParameterValue("Company Website", Website);
//// obj.SetParameterValue("TUNGAY",dtpTuNgay.Value.ToString());
//// obj.SetParameterValue("DENNGAY",dtpDenNgay.Value.ToString());
#endregion
// In danh sách bệnh nhân khám bệnh
if (dtpTuNgay1.Visible == true)
{
SqlConnection cnn;
string connectionString = null;
string sql = null;
string tungay;
tungay = dtpTuNgay1.Value.ToString("MM/dd/yyyy");
string denngay;
denngay = dtpDenNgay1.Value.ToString("MM/dd/yyyy");
connectionString = connectData.ChuoiKetNoi();
//connectionString = "Data Source=XUBI-LAP;Initial Catalog=QLBV;Integrated Security=True";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "SELECT PHIEUKHAM.STT, BENHNHAN.MaBenhNhan, BENHNHAN.Ho, BENHNHAN.Ten, BENHNHAN.GioiTinh, " +
"BENHNHAN.NgheNghiep, BENHNHAN.NgaySinh, BENHNHAN.DiaChi, " +
"PHIEUKHAM.MaPhieuKham, PHIEUKHAM.NgayKham," +
"PHONGKHAM.TenPhong " +
"FROM BENHNHAN, PHIEUKHAM, PHONGKHAM " +
"WHERE (BENHNHAN.MaBenhNhan = PHIEUKHAM.MaBenhNhan) AND (BENHNHAN.MaPhongKham = PHONGKHAM.MaPhongKham) AND " +
"(PHIEUKHAM.NgayKham >= " + "'" + tungay + "'" + ") AND (PHIEUKHAM.NgayKham <= " + "'" + denngay + "'" + ")" +
"ORDER BY PHIEUKHAM.STT";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
QLBVDataSet ds = new QLBVDataSet();
dscmd.Fill(ds, "BAOCAO_BENHNHAN");
//MessageBox.Show(ds.Tables[1].Rows.Count.ToString());
cnn.Close();
rpBaoCaoKhamBenh objRpt = new rpBaoCaoKhamBenh();
//objRpt.SetDataSource(ds.Tables[0]);
objRpt.Database.Tables["BAOCAO_BENHNHAN"].SetDataSource(ds);
//objRpt.SetDataSource((DataTable)ds.BAOCAO_BENHNHAN);
ParameterValues a = new ParameterValues();
ParameterDiscreteValue b = new ParameterDiscreteValue();
b.Value = dtpTuNgay1.Text;
a.Add(b);
objRpt.DataDefinition.ParameterFields["TUNGAY"].ApplyCurrentValues(a);
b.Value = dtpDenNgay1.Text;
a.Add(b);
objRpt.DataDefinition.ParameterFields["DENNGAY"].ApplyCurrentValues(a);
crystalReportViewer4.ReportSource = objRpt;
crystalReportViewer4.Refresh();
}
// In danh sách bệnh nhân xét nghiệm
if (dtpTuNgay2.Visible == true)
{
SqlConnection cnn;
string connectionString = null;
string sql = null;
string tungay;
tungay = dtpTuNgay2.Value.ToString("MM/dd/yyyy");
string denngay;
denngay = dtpDenNgay2.Value.ToString("MM/dd/yyyy");
connectionString = connectData.ChuoiKetNoi();
//connectionString = "Data Source=XUBI-LAP;Initial Catalog=QLBV;Integrated Security=True";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "SELECT XETNGHIEM.STT, BENHNHAN.MaBenhNhan, BENHNHAN.Ho, BENHNHAN.Ten, BENHNHAN.GioiTinh," +
"BENHNHAN.NgheNghiep, BENHNHAN.NgaySinh, BENHNHAN.DiaChi," +
"XETNGHIEM.MaXetNghiem, XETNGHIEM.NgayXetNghiem, " +
"PHONGLAMVIECNV.TenPhongLamViec " +
"FROM BENHNHAN, XETNGHIEM, PHONGLAMVIECNV " +
"WHERE (BENHNHAN.MaBenhNhan = XETNGHIEM.MaBenhNhan) AND ( XETNGHIEM.MaPhongXetNghiem = PHONGLAMVIECNV.MaPhongLamViec) AND " +
"(XETNGHIEM.NgayXetNghiem >= " + "'" + tungay + "'" + ") AND (XETNGHIEM.NgayXetNghiem <= " + "'" + denngay + "'" + ")" +
" ORDER BY XETNGHIEM.STT";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
QLBVDataSet ds = new QLBVDataSet();
dscmd.Fill(ds, "BAOCAO_BENHNHAN_XETNGHIEM");
//MessageBox.Show(ds.Tables[1].Rows.Count.ToString());
cnn.Close();
rpBaoCaoXetNghiem objRpt = new rpBaoCaoXetNghiem();
//objRpt.SetDataSource(ds.Tables[0]);
objRpt.Database.Tables["BAOCAO_BENHNHAN_XETNGHIEM"].SetDataSource(ds);
//objRpt.SetDataSource((DataTable)ds.BAOCAO_BENHNHAN);
ParameterValues a = new ParameterValues();
ParameterDiscreteValue b = new ParameterDiscreteValue();
b.Value = dtpTuNgay2.Text;
a.Add(b);
objRpt.DataDefinition.ParameterFields["TUNGAY"].ApplyCurrentValues(a);
b.Value = dtpDenNgay2.Text;
a.Add(b);
objRpt.DataDefinition.ParameterFields["DENNGAY"].ApplyCurrentValues(a);
crystalReportViewer4.ReportSource = objRpt;
crystalReportViewer4.Refresh();
}
// IN DANH SÁCH BỆNH NHÂN CHỤP X-QUANG
if (dtpTuNgay3.Visible == true)
{
SqlConnection cnn;
string connectionString = null;
string sql = null;
string tungay;
tungay = dtpTuNgay3.Value.ToString("MM/dd/yyyy");
string denngay;
denngay = dtpDenNgay3.Value.ToString("MM/dd/yyyy");
connectionString = connectData.ChuoiKetNoi();
//connectionString = "Data Source=XUBI-LAP;Initial Catalog=QLBV;Integrated Security=True";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "SELECT X_QUANG.STT, BENHNHAN.MaBenhNhan, BENHNHAN.Ho, BENHNHAN.Ten, BENHNHAN.GioiTinh, "+
"BENHNHAN.NgheNghiep, BENHNHAN.NgaySinh, BENHNHAN.DiaChi, "+
"X_QUANG.MaPhongXQ, X_QUANG.NgayChup, "+
"PHONGLAMVIECNV.TenPhongLamViec "+
"FROM BENHNHAN, X_QUANG, PHONGLAMVIECNV "+
"WHERE (BENHNHAN.MaBenhNhan = X_QUANG.MaBenhNhan) AND (X_QUANG.MaPhongXQ = PHONGLAMVIECNV.MaPhongLamViec) AND "+
"(X_QUANG.NgayChup >= " + "'" + tungay + "'" + ") AND (X_QUANG.NgayChup <= " + "'" + denngay + "'" + ")" +
"ORDER BY X_QUANG.STT";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
QLBVDataSet ds = new QLBVDataSet();
dscmd.Fill(ds, "BAOCAO_BENHNHAN_XQUANG");
//MessageBox.Show(ds.Tables[1].Rows.Count.ToString());
cnn.Close();
rpBaoCaoXQuang objRpt = new rpBaoCaoXQuang();
//objRpt.SetDataSource(ds.Tables[0]);
objRpt.Database.Tables["BAOCAO_BENHNHAN_XQUANG"].SetDataSource(ds);
//objRpt.SetDataSource((DataTable)ds.BAOCAO_BENHNHAN);
ParameterValues a = new ParameterValues();
ParameterDiscreteValue b = new ParameterDiscreteValue();
b.Value = dtpTuNgay3.Text;
a.Add(b);
objRpt.DataDefinition.ParameterFields["TUNGAY"].ApplyCurrentValues(a);
b.Value = dtpDenNgay3.Text;
a.Add(b);
objRpt.DataDefinition.ParameterFields["DENNGAY"].ApplyCurrentValues(a);
crystalReportViewer4.ReportSource = objRpt;
crystalReportViewer4.Refresh();
}
// in báo cáo doanh thu
if (dtpDenNgay4.Visible == true)
{
SqlConnection cnn;
string connectionString = null;
string sql = null;
string tungay;
tungay = dtpTuNgay4.Value.ToString("MM/dd/yyyy");
string denngay;
denngay = dtpDenNgay4.Value.ToString("MM/dd/yyyy");
connectionString = connectData.ChuoiKetNoi();
// connectionString = "Data Source=XUBI-LAP;Initial Catalog=QLBV;Integrated Security=True";
cnn = new SqlConnection(connectionString);
cnn.Open();
/*
sql = "SELECT HOADON.STT, HOADON.MaHoaDon, HOADON.NgayLap, HOADON.ThanhTien, HOADON.MaDichVu," +
"DICHVU.TenDichVu, DICHVU.DonGia "+
"FROM HOADON, DICHVU "+
"WHERE (HOADON.MaDichVu = DICHVU.MaDichVu) AND "+
"(HOADON.NgayLap >= " + "'" + tungay + "'" + ") AND (HOADON.NgayLap <= " + "'" + denngay + "'" + ")" +
"ORDER BY HOADON.STT";
*/
sql = "EXEC BAOCAO_DOANHTHU '" + tungay + "','" + denngay + "'";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
QLBVDataSet ds = new QLBVDataSet();
dscmd.Fill(ds, "BAOCAO_DOANHTHU");
//MessageBox.Show(ds.Tables[1].Rows.Count.ToString());
cnn.Close();
rpBaoCaoDoanhThu objRpt = new rpBaoCaoDoanhThu();
//objRpt.SetDataSource(ds.Tables[0]);
//objRpt.Database.Tables["BAOCAO_DOANHTHU"].SetDataSource(ds);
objRpt.SetDataSource((DataTable)ds.BAOCAO_DOANHTHU);
try
{
string tongtien = ds.Tables["BAOCAO_DOANHTHU"].Rows[0].ItemArray[0].ToString();
double tien = double.Parse(tongtien);
ParameterValues a = new ParameterValues();
ParameterDiscreteValue b = new ParameterDiscreteValue();
b.Value = dtpTuNgay4.Text;
a.Add(b);
objRpt.DataDefinition.ParameterFields["TUNGAY"].ApplyCurrentValues(a);
b.Value = dtpDenNgay4.Text;
a.Add(b);
objRpt.DataDefinition.ParameterFields["DENNGAY"].ApplyCurrentValues(a);
b.Value = So_chu(tien).ToString();
a.Add(b);
objRpt.DataDefinition.ParameterFields["TongTien1"].ApplyCurrentValues(a);
crystalReportViewer4.ReportSource = objRpt;
crystalReportViewer4.Refresh();
}
catch
{
MessageBox.Show( "Không có hóa đơn thuộc khoảng thời gian đã chọn!");
}
}
// in danh sach bac si
if (btnTongSo.Enabled == true && btnKhoa.Enabled == false)
{
SqlConnection cnn;
string connectionString = null;
string sql = null;
connectionString = connectData.ChuoiKetNoi();
// connectionString = "Data Source=XUBI-LAP;Initial Catalog=QLBV;Integrated Security=True";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "EXEC BAOCAO_DANHSACHBACSI ";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
QLBVDataSet ds = new QLBVDataSet();
dscmd.Fill(ds, "BAOCAO_DANHSACHBACSI");
//MessageBox.Show(ds.Tables[1].Rows.Count.ToString());
cnn.Close();
rpDanhSachBacSi objRpt = new rpDanhSachBacSi();
//objRpt.SetDataSource(ds.Tables[0]);
objRpt.Database.Tables["BAOCAO_DANHSACHBACSI"].SetDataSource(ds);
//objRpt.SetDataSource((DataTable)ds.BAOCAO_BENHNHAN);
crystalReportViewer4.ReportSource = objRpt;
crystalReportViewer4.Refresh();
}
// IN DANH SACH BAC SI THEO KHOA
if (btnTongSo.Enabled == false && btnKhoa.Enabled == true)
{
SqlConnection cnn;
string connectionString = null;
string sql = null;
connectionString = connectData.ChuoiKetNoi();
// connectionString = "Data Source=XUBI-LAP;Initial Catalog=QLBV;Integrated Security=True";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "EXEC BAOCAO_DANHSACHBACSI ";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
QLBVDataSet ds = new QLBVDataSet();
dscmd.Fill(ds, "BAOCAO_DANHSACHBACSI");
//MessageBox.Show(ds.Tables[1].Rows.Count.ToString());
cnn.Close();
rpDanhSachBacSi1 objRpt = new rpDanhSachBacSi1();
//objRpt.SetDataSource(ds.Tables[0]);
objRpt.Database.Tables["BAOCAO_DANHSACHBACSI"].SetDataSource(ds);
//objRpt.SetDataSource((DataTable)ds.BAOCAO_BENHNHAN);
crystalReportViewer4.ReportSource = objRpt;
crystalReportViewer4.Refresh();
}
}
public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
rpDanhSachBacSi rpt = new rpDanhSachBacSi();
rpt.Site = this.Site;
return rpt;
}