public frmViewReport(DataSetTKHD _ds) { InitializeComponent(); mode = 6; ds6 = _ds; }
private void btnTKHDReport_Click(object sender, EventArgs e) { //Get Data From DB string strWheredate = ""; string strWhereEmployee = ""; string strWhereRoom = ""; string strWhereTax = ""; DataAccess da = new DataAccess(); DataSet dsSP = null; string strEmployeeName = cbTKHDEmployee.Text; if ((strEmployeeName != "") && (strEmployeeName != "Tất cả")) { strWhereEmployee += " and Ten = '" + strEmployeeName + "' "; } string strRoomName = cbTKHDRoom.Text; if ((strRoomName != "") && (strRoomName != "Tất cả")) { strWhereRoom += " and TenPhong = '" + strRoomName + "' "; } if (rdTKHDByDate.Checked) { DateTime dtDate = dtTKHDByDate.Value; strWheredate += " AND Year(Ngayxuat) = " + dtDate.Year + " and Month(Ngayxuat) = " + dtDate.Month + " and Day(Ngayxuat) = " + dtDate.Day; } else if (rdTKHDByMonth.Checked) { if (cbTKHDByMonthMonth.Text == "" || cbTKHDByMonthYear.Text == "") { MessageBox.Show("Bạn chưa chọn thông tin tháng. Xin vui lòng chọn thông tin tháng", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } string month = cbTKHDByMonthMonth.Text; string year = cbTKHDByMonthYear.Text; strWheredate += " AND Year(Ngayxuat) = " + year + " and Month(Ngayxuat) = " + month + " "; } else if (rdTKHDByCustom.Checked) { DateTime startDate = dtTKHDByCustomFrom.Value; DateTime endDate = dtTKHDByCustomTo.Value; if (startDate != null && endDate != null) { strWheredate += " AND ( Year(Ngayxuat) > " + startDate.Year + " or (Year(Ngayxuat) = " + startDate.Year + " and Month(Ngayxuat) > " + startDate.Month + ") " + " or (Year(Ngayxuat) = " + startDate.Year + " and Month(Ngayxuat) = " + startDate.Month + " and Day(Ngayxuat) >= " + startDate.Day + "))" + " AND ( Year(Ngayxuat) < " + endDate.Year + " or (Year(Ngayxuat) = " + endDate.Year + " and Month(Ngayxuat) < " + endDate.Month + ") " + " or (Year(Ngayxuat) = " + endDate.Year + " and Month(Ngayxuat) = " + endDate.Month + " and Day(Ngayxuat) <= " + endDate.Day + ")) "; } } if (ckVAT.Checked && (!ckNormal.Checked)) { strWhereTax = " AND Hoadonxuat.Thue > 0"; } else if ((!ckVAT.Checked) && (ckNormal.Checked)) strWhereTax = " AND Hoadonxuat.Thue = 0"; string strQuery = "SELECT Hoadonxuat.IDHoadonXuat, Hoadonxuat.Ngayxuat, Hoadonxuat.Giam as Giam1,Hoadonxuat.Giam&'%' as Giam, Nhanvien.Ten, " + "Hoadonxuat.Phuthu, Phong.TenPhong, Hoadonxuat.GioBD, Hoadonxuat.GioKT,Hoadonxuat.Thue as Thue1,Hoadonxuat.Thue&'%' as Thue, " + "GiaLoaiPhong.Gia, Hoadonxuat.Ghichu "+ " FROM ((Hoadonxuat INNER JOIN GiaLoaiPhong ON " + "Hoadonxuat.IDGiaLoaiphong = GiaLoaiPhong.IDGiaLoaiPhong) INNER JOIN Phong " + "ON Hoadonxuat.IDPhong = Phong.IDPhong) INNER JOIN Nhanvien ON " + "Hoadonxuat.IDNhanvien = Nhanvien.IDNhanvien " + "WHERE 1 = 1 " + strEmployeeName + strRoomName + strWheredate + strWhereTax; string strQuery1 = "SELECT Hoadonxuat.IDHoadonXuat " + " FROM ((Hoadonxuat INNER JOIN GiaLoaiPhong ON " + "Hoadonxuat.IDGiaLoaiphong = GiaLoaiPhong.IDGiaLoaiPhong) INNER JOIN Phong " + "ON Hoadonxuat.IDPhong = Phong.IDPhong) INNER JOIN Nhanvien ON " + "Hoadonxuat.IDNhanvien = Nhanvien.IDNhanvien " + "WHERE 1 = 1 " + strEmployeeName + strRoomName + strWheredate + strWhereTax; string strQuery2 = "SELECT ChitietHDXuat.IDHoadonXuat, ChitietHDXuat.IDChitietHDXuat,ChitietHDXuat.Giam as Giam1,ChitietHDXuat.Giam&'%' as Giam, " + "ChitietHDXuat.IDSanpham, SanPham.TenSanPham, SanPham.DVT, ChitietHDXuat.Soluong as Soluong, " + " GiaXuatSP.Gia as Gia, ([Gia]*(100 - [Giam1])/100)*[Soluong] AS Thanhtien " + "FROM (SanPham INNER JOIN ChitietHDXuat ON SanPham.IDSanPham = ChitietHDXuat.IDSanpham) " + "INNER JOIN GiaXuatSP ON SanPham.IDSanPham = GiaXuatSP.IDSanPham " ; dsSP = (DataSet)(da.getDataByQuery(strQuery, strQuery1, strQuery2)); try { gridControlTKHD.DataSource = dsSP.Tables[0]; } catch { gridControlTKHD.DataSource = null; } btnTKHDViewReport.Enabled = true; DataTable dt = new DataTable(); if (dsSP != null) { TKHDReport = new DataSetTKHD(); for (int t = 0; t < dsSP.Tables["HoadonXuat"].Rows.Count; t++) { DataRow dr = TKHDReport.Tables[0].NewRow(); dr["IDHoadonXuat"] = Convert.ToInt32(dsSP.Tables["HoadonXuat"].Rows[t]["IDHoadonXuat"]); int index1 = dsSP.Tables["HoadonXuat"].Rows[t]["Ngayxuat"].ToString().IndexOf(' '); //dr2["NgayBan"] = dsSP.Tables[0].Rows[j]["NgayBan"].ToString().Substring(0, index1); dr["Ngayxuat"] = dsSP.Tables["HoadonXuat"].Rows[t]["Ngayxuat"].ToString().Substring(0, index1); dr["Ten"] = dsSP.Tables["HoadonXuat"].Rows[t]["Ten"]; dr["Phuthu"] = dsSP.Tables["HoadonXuat"].Rows[t]["Phuthu"]; dr["Thue"] = dsSP.Tables["HoadonXuat"].Rows[t]["Thue1"]; dr["TenPhong"] = dsSP.Tables["HoadonXuat"].Rows[t]["TenPhong"]; string strBD = dsSP.Tables["HoadonXuat"].Rows[t]["GioBD"].ToString(); string strKT = dsSP.Tables["HoadonXuat"].Rows[t]["GioKT"].ToString(); index1 = strBD.IndexOf(' '); //dr["GioBD"] = dsSP.Tables["HoadonXuat"].Rows[t]["GioBD"]; dr["GioBD"] = strBD.Substring(index1, strBD.Length - index1); index1 = strKT.IndexOf(' '); //dr["GioKT"] = dsSP.Tables["HoadonXuat"].Rows[t]["GioKT"]; dr["GioKT"] = strKT.Substring(index1, strKT.Length - index1); dr["Gia"] = Convert.ToInt32(dsSP.Tables["HoadonXuat"].Rows[t]["Gia"]).ToString("###,###,###,###"); dr["Ghichu"] = dsSP.Tables["HoadonXuat"].Rows[t]["Ghichu"]; dr["Giam"] = dsSP.Tables["HoadonXuat"].Rows[t]["Giam1"]; TKHDReport.Tables[0].Rows.Add(dr); } for (int t = 0; t < dsSP.Tables["ChitietHDXuat"].Rows.Count; t++) { DataRow dr = TKHDReport.Tables[1].NewRow(); dr["IDHoadonXuat"] = Convert.ToInt32(dsSP.Tables["ChitietHDXuat"].Rows[t]["IDHoadonXuat"]); dr["IDChitietHDXuat"] = dsSP.Tables["ChitietHDXuat"].Rows[t]["IDChitietHDXuat"]; dr["IDSanpham"] = dsSP.Tables["ChitietHDXuat"].Rows[t]["IDSanpham"]; dr["TenSanPham"] = dsSP.Tables["ChitietHDXuat"].Rows[t]["TenSanPham"]; dr["DVT"] = dsSP.Tables["ChitietHDXuat"].Rows[t]["DVT"]; dr["Soluong"] = Convert.ToInt32(dsSP.Tables["ChitietHDXuat"].Rows[t]["Soluong"]).ToString("###,###,###,###"); dr["Gia"] = Convert.ToInt32(dsSP.Tables["ChitietHDXuat"].Rows[t]["Gia"]).ToString("###,###,###,###"); dr["Giam"] = Convert.ToInt32(dsSP.Tables["ChitietHDXuat"].Rows[t]["Giam1"]).ToString("###,###,###,###"); dr["Thanhtien"] = Convert.ToInt32(dsSP.Tables["ChitietHDXuat"].Rows[t]["Thanhtien"]).ToString("###,###,###,###"); TKHDReport.Tables[1].Rows.Add(dr); } DataRow dr1 = TKHDReport.Tables[2].NewRow(); if (rdTKHDByDate.Checked) { DateTime dtDate = dtDateDSBHByDate.Value; dr1["FromDate"] = dtDate.ToString("dd/MM/yyyy"); dr1["ToDate"] = dtDate.ToString("dd/MM/yyyy"); } else if (rdTKHDByMonth.Checked) { string month = cbTKHDByMonthMonth.Text; string year = cbTKHDByMonthYear.Text; dr1["FromDate"] = "Tháng " + month + "//" + year; dr1["ToDate"] = "Tháng " + month + "//" + year; } else if (rdTKHDByCustom.Checked) { DateTime startDate = dtTKHDByCustomFrom.Value; DateTime endDate = dtTKHDByCustomTo.Value; dr1["FromDate"] = startDate.ToString("dd/MM/yyyy"); dr1["ToDate"] = endDate.ToString("dd/MM/yyyy"); } if (strEmployeeName == "") dr1["TenNV"] = "Tất cả"; else dr1["TenNV"] = strEmployeeName; if (strRoomName == "") dr1["TenPhong"] = "Tất cả"; else dr1["TenPhong"] = strRoomName; dr1["PrintDate"] = DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss tt"); TKHDReport.Tables[2].Rows.Add(dr1); } }