public frmViewReport(DataSetDetailReportDSBH _ds)
{
InitializeComponent();
mode = 5;
ds5 = _ds;
}
public frmViewReport(DataSetDetailReportDSBH _ds)
{
InitializeComponent();
mode = 5;
ds5 = _ds;
}
private void btnDSBHReport_Click(object sender, EventArgs e)
{
//Get Data From DB
string strWheredate = "";
string strWhereProduct = "";
string strWhereEmployee = "";
string strWhereRoom = "";
string strQuerySP = "";
string strQueryPhong_PT = "";
DateTime startDate = dtDSBHFromByCustom.Value;
DateTime endDate = dtDSBHToByCustom.Value;
bool isPhuThu = chkDSBH_TP_PT.Checked;
string strNameProduct = cbDSBHProduct.Text;
DataAccess da = new DataAccess();
//DataSetDetailReport dsDetailReport = new DataSetDetailReport();
DataSet dsTP_PT = null;
DataSet dsSP = null;
string strEmployeeName = cbDSHBEmployee.Text;
if ((strNameProduct != "") && (strNameProduct != "Tất cả"))
{
strWhereProduct += " and TenNhomSP = '" + strNameProduct + "' ";
}
if ((strEmployeeName != "") && (strEmployeeName != "Tất cả"))
{
strWhereEmployee += " and Ten = '" + strEmployeeName + "' ";
}
string strRoomName = cbDSBHRoom.Text;
if ((strRoomName != "") && (strRoomName != "Tất cả"))
{
strWhereRoom += " and TenPhong = '" + strRoomName + "' ";
}
if (rdDSBHByDate.Checked)
{
DateTime dtDate = dtDateDSBHByDate.Value;
strWheredate += " AND Year(Ngayxuat) = " + dtDate.Year + " and Month(Ngayxuat) = " +
dtDate.Month + " and Day(Ngayxuat) = " + dtDate.Day;
}
else if (rdDSBHByMonth.Checked)
{
string month = "1";
if (cbDSBHMonthByMonth.Text != "")
month = cbDSBHMonthByMonth.Text;
else
month = DateTime.Now.Month.ToString();
string year = "2010";
if (cbDSBHYearByMonth.Text != "")
year = cbDSBHYearByMonth.Text;
else
year = DateTime.Now.Month.ToString();
strWheredate += " AND Year(Ngayxuat) = " + year + " and Month(Ngayxuat) = " + month + " ";
}
else if (rdDSBHByCustom.Checked)
{
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 (isPhuThu)
{
strQueryPhong_PT =
"Select IDHoadonxuat, Hoadonxuat.IDGiaLoaiPhong, Hoadonxuat.IDPhong as IDPhong,Hoadonxuat.Giam as Giam, Ngayxuat As Ngayxuat,Hoadonxuat.Thue as Thue, " +
"Phuthu, GioBD, GioKT, GiaLoaiPhong.Gia, TenPhong As TenSanPham" +
" From Hoadonxuat, Phong, GiaLoaiPhong, Nhanvien" +
" Where Hoadonxuat.IDPhong = Phong.IDPhong and " +
"Hoadonxuat.IDNhanvien = Nhanvien.IDNhanvien and " +
" Hoadonxuat.IDGiaLoaiPhong = GiaLoaiPhong.IDGiaLoaiPhong " +
strWhereEmployee + strWhereRoom + strWheredate;
dsTP_PT = (DataSet)(da.getDataByQuery(strQueryPhong_PT));
}
string subQuerySP = "Select Hoadonxuat.IDHoadonXuat, Ngayxuat As NgayBan, SanPham.IDSanPham, " +
"TenNhomSP, TenSanPham, Soluong, Ten, " +
"DVT, Hoadonxuat.Giam, Max(NgayXuatSP) as NgayXuatSP1 " +
"From Hoadonxuat, ChitietHDXuat, SanPham, NhomSP, GiaXuatSP, Nhanvien, Phong " +
"Where ChitietHDXuat.IDHoadonXuat = Hoadonxuat.IDHoadonXuat and " +
"Phong.IDPhong = Hoadonxuat.IDPhong and " +
"Hoadonxuat.IDNhanvien = Nhanvien.IDNhanvien and " +
"NhomSP.IDNhomSP = SanPham.IDNhomSP and " +
"SanPham.IDSanPham = ChitietHDXuat.IDSanPham and " +
"SanPham.IDSanPham = GiaXuatSP.IDSanPham " + strWhereProduct +
strWhereEmployee + strWhereRoom + strWheredate +
" and (Year(NgayXuatSP) < Year(Ngayxuat) or " +
"(Year(NgayXuatSP) = Year(Ngayxuat) and Month(NgayXuatSP) < Month(Ngayxuat)) or " +
"(Year(NgayXuatSP) = Year(Ngayxuat) and Month(NgayXuatSP) = Month(Ngayxuat) and Day(NgayXuatSP) <= Day(Ngayxuat)))" +
" GROUP BY Hoadonxuat.IDHoadonXuat, Ngayxuat, SanPham.IDSanPham, TenNhomSP, " +
"TenSanPham, Soluong, Ten, DVT, Hoadonxuat.Giam ";
strQuerySP = "Select T.IDHoadonXuat, T.NgayBan, T.IDSanPham, T.TenSanPham, T.Soluong, " +
"T.DVT, T.Giam, T.NgayXuatSP1, Max(Gia) as Gia " +
"From GiaXuatSP, (" + subQuerySP + ") as T " +
"Where T.IDSanPham = GiaXuatSP.IDSanPham and " +
"T.NgayXuatSP1 = GiaXuatSP.NgayXuatSP " +
"GROUP BY T.IDHoadonXuat, T.NgayBan, T.IDSanPham, T.TenSanPham, T.Soluong, " +
"T.DVT, T.Giam, T.NgayXuatSP1";
string sqlCommand = "SELECT Hoadonxuat.Ngayxuat AS Ngayxuat, Hoadonxuat.Thue AS Thue, ChitietHDXuat.IDChitietHDXuat AS IDChitietHDXuat, ChitietHDXuat.IDHoadonXuat AS IDHoadonXuat, ChitietHDXuat.IDSanpham AS IDSanpham, SanPham.TenSanPham AS TenSanPham, SanPham.DVT AS DVT, GiaXuatSP.IDGiaXuatSP AS IDGiaXuatSP, GiaXuatSP.Gia AS Gia, ChitietHDXuat.Giam AS Giam, ChitietHDXuat.Soluong AS Soluong, ([Gia]*(100-[Giam])/100)*[Soluong] AS Thanhtien, SanPham.TonKho AS TonKho, ChitietHDXuat.Bep AS Bep, ChitietHDXuat.Kho AS Kho, ChitietHDXuat.Ghichu AS Ghichu, 1 AS [Delete], Phong.TenPhong as TenPhong "+
"FROM (Hoadonxuat INNER JOIN (SanPham INNER JOIN (GiaXuatSP INNER JOIN ChitietHDXuat ON GiaXuatSP.IDGiaXuatSP = ChitietHDXuat.IDGiaxuat) ON (SanPham.IDSanPham = GiaXuatSP.IDSanPham) AND (SanPham.IDSanPham = ChitietHDXuat.IDSanpham)) ON Hoadonxuat.IDHoadonXuat = ChitietHDXuat.IDHoadonXuat) INNER JOIN Phong ON Hoadonxuat.IDPhong = Phong.IDPhong "+
"Where 1=1 " + strWhereProduct + strWhereEmployee + strWhereRoom + strWheredate + ";";
dsSP = (DataSet)(da.getDataByQuery(sqlCommand));
//dsSP = (DataSet)(da.getDataByQuery(strQuerySP));
Decimal tongtien = 0;
Decimal giam = 0;
Decimal thue = 0;
int i = 0, j = 0;
if (dsSP != null)
{
detailReportDSBH = new DataSetDetailReportDSBH();
for (i = 0; i < dsSP.Tables[0].Rows.Count; i++)
{
DataRow dr1 = detailReportDSBH.Tables[0].NewRow();
Decimal soluong = Convert.ToDecimal(dsSP.Tables[0].Rows[i]["Soluong"].ToString());
Decimal gia = Convert.ToDecimal(dsSP.Tables[0].Rows[i]["Gia"].ToString());
if (dsSP.Tables[0].Rows[i]["Giam"].ToString() != "")
{
giam += (soluong * (gia / 100) * (Convert.ToDecimal(dsSP.Tables[0].Rows[i]["Giam"].ToString())));
}
dr1["ID"] = i.ToString();
dr1["STT"] = (i + 1).ToString();
int index = dsSP.Tables[0].Rows[i]["Ngayxuat"].ToString().IndexOf(' ');
dr1["NgayBan"] = dsSP.Tables[0].Rows[i]["Ngayxuat"].ToString().Substring(0, index);
dr1["TenSanPham"] = dsSP.Tables[0].Rows[i]["TenSanPham"].ToString();
dr1["DVT"] = dsSP.Tables[0].Rows[i]["DVT"].ToString();
dr1["Giam"] = Convert.ToInt32(dsSP.Tables[0].Rows[i]["Giam"]).ToString("##0") + "%";
dr1["Thue"] = Convert.ToInt32(dsSP.Tables[0].Rows[i]["Thue"]).ToString("##0") + "%";
//dr1["DonGia"] = dsSP.Tables[0].Rows[i]["Gia"].ToString();
dr1["DonGia"] = Convert.ToInt32(dsSP.Tables[0].Rows[i]["Gia"]).ToString("###,###,###,###");
dr1["Soluong"] = Convert.ToInt32(soluong).ToString("###,###,###,###");
dr1["ThanhTien"] = Convert.ToInt32(dsSP.Tables[0].Rows[i]["Thanhtien"]).ToString("###,###,###,###");
if (dsSP.Tables[0].Rows[i]["Thue"].ToString() != "")
{
thue += ((Convert.ToInt32(dsSP.Tables[0].Rows[i]["Thanhtien"]) / 100) * (Convert.ToDecimal(dsSP.Tables[0].Rows[i]["Thue"])));
tongtien = tongtien + Convert.ToInt32(dsSP.Tables[0].Rows[i]["Thanhtien"]) + ((Convert.ToInt32(dsSP.Tables[0].Rows[i]["Thanhtien"]) / 100) * (Convert.ToDecimal(dsSP.Tables[0].Rows[i]["Thue"])));
}
else
tongtien += Convert.ToInt32(dsSP.Tables[0].Rows[i]["Thanhtien"]);
detailReportDSBH.Tables[0].Rows.Add(dr1);
}
}
if (dsTP_PT != null)
{
if(detailReportDSBH == null)
detailReportDSBH = new DataSetDetailReportDSBH();
for (j = 0; j < dsTP_PT.Tables[0].Rows.Count; j++)
{
if (Convert.ToInt32(dsTP_PT.Tables[0].Rows[j]["IDPhong"]) >= 0)
{
int thanhtien = 0;
DataRow dr1 = detailReportDSBH.Tables[0].NewRow();
Decimal gia = Convert.ToDecimal(dsTP_PT.Tables[0].Rows[j]["Gia"].ToString());
dr1["ID"] = i.ToString();
dr1["STT"] = (i + 1).ToString();
int index = dsTP_PT.Tables[0].Rows[j]["Ngayxuat"].ToString().IndexOf(' ');
dr1["NgayBan"] = dsTP_PT.Tables[0].Rows[j]["Ngayxuat"].ToString().Substring(0, index);
dr1["TenSanPham"] = ("Tiền phòng " + dsTP_PT.Tables[0].Rows[j]["TenSanPham"].ToString()).ToString();
DateTime dtBD = Convert.ToDateTime(dsTP_PT.Tables[0].Rows[j]["GioBD"].ToString());
DateTime dtKT = Convert.ToDateTime(dsTP_PT.Tables[0].Rows[j]["GioKT"].ToString());
TimeSpan dif = dtKT - dtBD;
Decimal soluongGio = dif.Hours + Convert.ToDecimal(dif.Minutes) / 60;
dr1["DVT"] = ("Gio").ToString();
dr1["Giam"] = Convert.ToInt32(dsTP_PT.Tables[0].Rows[j]["Giam"]).ToString("##0") + "%";
dr1["Thue"] = Convert.ToInt32(dsTP_PT.Tables[0].Rows[j]["Thue"]).ToString("##0") + "%";
dr1["DonGia"] = Convert.ToInt32(gia).ToString("###,###,###,###");
dr1["Soluong"] = soluongGio.ToString("###,###,###,##0.##");
thanhtien = (int)((gia / 100) * (100 - Convert.ToInt32(dsTP_PT.Tables[0].Rows[j]["Giam"])));
thanhtien = (int)(thanhtien * soluongGio);
dr1["ThanhTien"] = thanhtien.ToString("###,###,###,##0.##");
if (dsTP_PT.Tables[0].Rows[j]["Thue"].ToString() != "")
{
thue += ((thanhtien / 100) * (Convert.ToDecimal(dsTP_PT.Tables[0].Rows[j]["Thue"])));
tongtien = tongtien + thanhtien + (thanhtien / 100) * (Convert.ToDecimal(dsTP_PT.Tables[0].Rows[j]["Thue"]));
}
else
tongtien += thanhtien;
giam += (int)(gia * soluongGio - thanhtien);
detailReportDSBH.Tables[0].Rows.Add(dr1);
i++;
if (dsTP_PT.Tables[0].Rows[j]["PhuThu"].ToString() != "")
{
Decimal phuThu = Convert.ToDecimal(dsTP_PT.Tables[0].Rows[j]["PhuThu"].ToString());
if (phuThu != 0)
{
DataRow dr2 = detailReport.Tables[0].NewRow();
dr2["ID"] = i.ToString();
dr2["STT"] = (i + 1).ToString();
int index1 = dsTP_PT.Tables[0].Rows[j]["Ngayxuat"].ToString().IndexOf(' ');
dr2["NgayBan"] = dsTP_PT.Tables[0].Rows[j]["Ngayxuat"].ToString().Substring(0, index1);
dr2["TenSanPham"] = ("Phụ thu phòng " + dsTP_PT.Tables[0].Rows[j]["TenSanPham"].ToString()).ToString();
dr2["ThanhTien"] = (phuThu).ToString("###,###,###,###.##");
dr2["Thue"] = Convert.ToInt32(dsTP_PT.Tables[0].Rows[j]["Thue"]).ToString("##0") + "%";
dr2["Giam"] = "0" + "%";
if (dsTP_PT.Tables[0].Rows[i]["Thue"].ToString() != "")
{
thue += ((phuThu / 100) * (Convert.ToDecimal(dsTP_PT.Tables[0].Rows[j]["Thue"])));
tongtien = tongtien + phuThu + (thanhtien / 100) * (Convert.ToDecimal(dsTP_PT.Tables[0].Rows[j]["Thue"]));
}
else
tongtien += phuThu;
detailReportDSBH.Tables[0].Rows.Add(dr2);
i++;
}
}
}
}
}
DataRow dr = detailReportDSBH.Tables[1].NewRow();
if (rdDSBHByDate.Checked)
{
dr["StartDate"] = dtDateDSBHByDate.Value.ToString("dd/MM/yyyy");
dr["EndDate"] = dtDateDSBHByDate.Value.ToString("dd/MM/yyyy");
}
else if (rdDSBHByMonth.Checked)
{
string month = "1";
if (cbDSBHMonthByMonth.Text != "")
month = cbDSBHMonthByMonth.Text;
else
month = DateTime.Now.Month.ToString();
string year = "2010";
if (cbDSBHYearByMonth.Text != "")
year = cbDSBHYearByMonth.Text;
else
year = DateTime.Now.Month.ToString();
strWheredate += " AND Year(Ngayxuat) = " + year + " and Month(Ngayxuat) = " + month + " ";
dr["StartDate"] = "Tháng " + month + "/" + year;
dr["EndDate"] = "Tháng " + month + "/" + year;
}
else if (rdDSBHByCustom.Checked)
{
dr["StartDate"] = startDate.ToString("dd/MM/yyyy");
dr["EndDate"] = endDate.ToString("dd/MM/yyyy");
}
dr["TCThanhTien"] = tongtien.ToString("###,###,###,###.##");
if (strRoomName == "")
strRoomName = "Tất cả";
if (strEmployeeName == "")
strEmployeeName = "Tất cả";
if (strNameProduct == "")
strNameProduct = "Tất cả";
dr["TenNhanVien"] = strEmployeeName;
dr["LoaiSanPham"] = strNameProduct;
dr["TenPhong"] = strRoomName;
detailReportDSBH.Tables[1].Rows.Add(dr);
txtDanhThu.Text = tongtien.ToString("###,###,###,##0") + " VND";
//txt_Giam.Text = giam.ToString("###,###,###,###.##") + " VND";
gridDSBH.DataSource = detailReportDSBH.Tables[0];
btnDSBHViewReport.Enabled = true;
}
