public frmViewReport(dataSetNewDetailReport _ds)
{
InitializeComponent();
mode = 1;
ds1 = _ds;
}
public frmViewReport(dataSetNewDetailReport _ds)
{
InitializeComponent();
mode = 1;
ds1 = _ds;
}
private void btnReport_Click(object sender, EventArgs e)
{
//Get Data From DB
string strWhere = "";
string strQuerySP = "";
string strQueryPhong_PT = "";
DateTime startDate = dtFromByCustom.Value;
DateTime endDate = dtToByCustom.Value;
bool isPhuThu = chbAll.Checked;
string strName = cbTenNhomSP.Text;
DataAccess da = new DataAccess();
DataSetDetailReport dsDetailReport = new DataSetDetailReport();
DataSet dsTP_PT = null;
DataSet dsSP = null;
if ((strName != "") && (strName != "Tất cả"))
{
strWhere += " and TenNhomSP = '" + strName + "' ";
}
if (rdDate.Checked)
{
DateTime dtDate = dtDateByDate.Value;
strWhere += " AND Year(Ngayxuat) = " + dtDate.Year + " and Month(Ngayxuat) = " +
dtDate.Month + " and Day(Ngayxuat) = " + dtDate.Day;
}
else if (rdMonth.Checked)
{
string month = "1";
if (cbMonthByMonth.Text != "")
month = cbMonthByMonth.Text;
else
month = DateTime.Now.Month.ToString();
string year = "2010";
if (cbYearByMonth.Text != "")
year = cbYearByMonth.Text;
else
year = DateTime.Now.Month.ToString();
strWhere += " AND Year(Ngayxuat) = " + year + " and Month(Ngayxuat) = " + month + " ";
}
else if (rdCustom.Checked)
{
if (startDate != null && endDate != null)
{
strWhere += " 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, Hoadonxuat.Ngayxuat as Ngayxuat, Hoadonxuat.Thue as Thue," +
"Phuthu, GioBD, GioKT, GiaLoaiPhong.Gia, TenPhong As TenSanPham" +
" From Hoadonxuat, Phong, GiaLoaiPhong" +
" Where Hoadonxuat.IDPhong = Phong.IDPhong and " +
" Hoadonxuat.IDGiaLoaiPhong = GiaLoaiPhong.IDGiaLoaiPhong " + strWhere;
dsTP_PT = (DataSet)(da.getDataByQuery(strQueryPhong_PT));
}
string subQuerySP = "Select Hoadonxuat.IDHoadonXuat, Ngayxuat As NgayBan, SanPham.IDSanPham, TenSanPham, Soluong, " +
"DVT, ChitietHDXuat.Giam as Giam, Max(NgayXuatSP) as NgayXuatSP1 " +
"From Hoadonxuat, ChitietHDXuat, SanPham, NhomSP, GiaXuatSP " +
"Where ChitietHDXuat.IDHoadonXuat = Hoadonxuat.IDHoadonXuat and " +
"NhomSP.IDNhomSP = SanPham.IDNhomSP and " +
"SanPham.IDSanPham = ChitietHDXuat.IDSanPham and " +
"SanPham.IDSanPham = GiaXuatSP.IDSanPham " + strWhere +
" 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, TenSanPham, Soluong, " +
"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] " +
"FROM Hoadonxuat INNER JOIN (SanPham INNER JOIN (GiaXuatSP INNER JOIN ChitietHDXuat ON GiaXuatSP.IDGiaXuatSP = ChitietHDXuat.IDGiaxuat) ON (SanPham.IDSanPham = ChitietHDXuat.IDSanpham) AND (SanPham.IDSanPham = GiaXuatSP.IDSanPham)) ON Hoadonxuat.IDHoadonXuat = ChitietHDXuat.IDHoadonXuat " +
"Where 1=1 "+strWhere + ";";
dsSP = (DataSet)(da.getDataByQuery(sqlCommand));
Decimal tongtien = 0;
Decimal giam = 0;
Decimal thue = 0;
int i = 0, j = 0;
if (dsSP != null)
{
detailReport = new dataSetNewDetailReport();
for (i = 0; i < dsSP.Tables[0].Rows.Count; i++)
{
DataRow dr1 = detailReport.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"]);
detailReport.Tables[0].Rows.Add(dr1);
}
}
if (dsTP_PT != null)
{
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 = detailReport.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);
detailReport.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;
detailReport.Tables[0].Rows.Add(dr2);
i++;
}
}
}
}
}
DataRow dr = detailReport.Tables[1].NewRow();
dr["StartDate"] = startDate.ToString("dd/MM/yyyy");
dr["EndDate"] = endDate.ToString("dd/MM/yyyy");
dr["TCThanhTien"] = tongtien.ToString("###,###,###,###");
detailReport.Tables[1].Rows.Add(dr);
txt_TC.Text = tongtien.ToString("###,###,###,###") + " VND";
txt_Giam.Text = giam.ToString("###,###,###,###") + " VND";
txt_Thue.Text = thue.ToString("###,###,###,###") + " VND";
gridSP.DataSource = detailReport.Tables[0];
btnViewReport.Enabled = true;
}
