private void btnInComeViewChart_Click(object sender, EventArgs e)
{
if (rdInComeDate.Checked)
{//By Date
DateTime dtFrom = dtInComeByDateFromDate.Value;
DateTime dtTo = dtInComeByDateToDate.Value;
if (DateTime.Compare(dtFrom, dtTo) > 0)
{
MessageBox.Show("Ngày bắt đầu lớn hơn ngày kết thúc. Vui lòng chọn lại thông tin ngày tháng",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else
{
DataAccess da = new DataAccess();
TimeSpan ts = dtTo - dtFrom;
//string stridProduct = "";
int i = 0, j = 0;
DateTime dtCurrent = dtFrom;
if (dtChartData != null && dtChartData.Rows.Count > 0)
dtChartData.Rows.Clear();
if (dtChartData != null && dtChartData.Columns.Count > 0)
dtChartData.Columns.Clear();
//if (listofProductName != null)
// listofProductName = null;
//if (listofProductID != null)
// listofProductID = null;
//listofProductName = new string[dtSelectedProduct.Rows.Count];
//listofProductID = new int[dtSelectedProduct.Rows.Count];
DataColumn dcdt = new DataColumn("Ngayxuat", Type.GetType("System.String"));
dtChartData.Columns.Add(dcdt);
DataColumn dcTT = new DataColumn("ThanhTien", Type.GetType("System.String"));
dtChartData.Columns.Add(dcTT);
string strWheredate = "";
string strQuery = "";
for (j = 0; j <= ts.Days; j++)
{
dtCurrent = dtFrom.AddDays(j);
//Get the number of product sold in that day.
strWheredate = "And Year(Ngayxuat) = " + dtCurrent.Year +
" and Month(Ngayxuat) = " + dtCurrent.Month +
" and Day(Ngayxuat) = " + dtCurrent.Day + " ";
DataSet dsTP_PT = null;
if (chkInComePhuThu_TienPhong.Checked)
{
string strQueryPhong_PT =
"Select IDHoadonxuat, Hoadonxuat.IDGiaLoaiPhong, Ngayxuat As NgayBan, " +
"Phuthu, GioBD, GioKT, GiaLoaiPhong.Gia, TenPhong As TenSanPham" +
" From Hoadonxuat, Phong, GiaLoaiPhong" +
" Where Hoadonxuat.IDPhong = Phong.IDPhong and " +
" Hoadonxuat.IDGiaLoaiPhong = GiaLoaiPhong.IDGiaLoaiPhong " + strWheredate;
dsTP_PT = (DataSet)(da.getDataByQuery(strQueryPhong_PT));
}
string subQuerySP = "Select Hoadonxuat.IDHoadonXuat, Ngayxuat As NgayBan, SanPham.IDSanPham, TenSanPham, Soluong, " +
"DVT, Hoadonxuat.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 " + 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, TenSanPham, Soluong, " +
"DVT, Hoadonxuat.Giam ";
strQuery = "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";
//dsSP = (DataSet)(da.getDataByQuery(strQuery));
//strQuery = "Select SanPham.IDSanPham, SanPham.TenSanPham, SanPham.DVT, " +
// "Hoadonxuat.Ngayxuat, Sum(soluong) as TongSoluong " +
// "From SanPham, ChitietHDXuat, Hoadonxuat " +
// "Where SanPham.IDSanPham = ChitietHDXuat.IDSanPham and " +
// "ChitietHDXuat.IDHoadonXuat = Hoadonxuat.IDHoadonXuat " +
// strWheredate +
// "Group by SanPham.IDSanPham, SanPham.TenSanPham, " +
// "SanPham.DVT, Hoadonxuat.Ngayxuat " +
// "ORDER BY Hoadonxuat.Ngayxuat";
DataSet dsTemp = da.getDataByQuery(strQuery);
DataRow dr = dtChartData.NewRow();
dr["Ngayxuat"] = dtCurrent.ToString("MM/dd/yyyy");
/////////////////////////////
if (dsTemp != null)
{
for (i = 0; i < dsTemp.Tables[0].Rows.Count; i++)
{
Decimal soluong = Convert.ToDecimal(dsTemp.Tables[0].Rows[i]["Soluong"].ToString());
Decimal gia = Convert.ToDecimal(dsTemp.Tables[0].Rows[i]["Gia"].ToString());
Decimal tt = gia * soluong;
if (!(dr["ThanhTien"] is DBNull || dr["ThanhTien"] == ""))
tt += Convert.ToDecimal(dr["ThanhTien"]);
dr["ThanhTien"] = (tt).ToString("###,###,###,###.##");
}
}
if (dsTP_PT != null)
{
for (i = 0; i < dsTP_PT.Tables[0].Rows.Count; i++)
{
Decimal gia = Convert.ToDecimal(dsTP_PT.Tables[0].Rows[i]["Gia"].ToString());
DateTime dtBD = Convert.ToDateTime(dsTP_PT.Tables[0].Rows[i]["GioBD"].ToString());
DateTime dtKT = Convert.ToDateTime(dsTP_PT.Tables[0].Rows[i]["GioKT"].ToString());
TimeSpan dif = dtKT - dtBD;
Decimal soluongGio = dif.Hours + Convert.ToDecimal(dif.Minutes) / 60;
Decimal tt = soluongGio * gia;
if (!(dr["ThanhTien"] is DBNull || dr["ThanhTien"] == ""))
tt += Convert.ToDecimal(dr["ThanhTien"]);
dr["ThanhTien"] = (tt).ToString("###,###,###,###.##");
if (dsTP_PT.Tables[0].Rows[i]["PhuThu"].ToString() != "")
{
Decimal phuThu = Convert.ToDecimal(dsTP_PT.Tables[0].Rows[i]["PhuThu"].ToString());
if (phuThu != 0)
{
if (!(dr["ThanhTien"] is DBNull || dr["ThanhTien"] == ""))
phuThu += Convert.ToDecimal(dr["ThanhTien"]);
dr["ThanhTien"] = (phuThu).ToString("###,###,###,###.##");
}
}
}
}
dtChartData.Rows.Add(dr);
////////////////////////////
}
if (dtChartData == null || dtChartData.Rows.Count == 0)
{
MessageBox.Show("Dữ liệu rỗng! Xin vui lòng chọn lại dữ liệu", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
frmChartView ch = new frmChartView(dtChartData, "ByDate");
ch.ShowDialog();
}
}
}
else if (rdInComeMonth.Checked)
{//By Month
if (cbInComeByMonthFromMonth.Text == "" || cbInComeByMonthToMonth.Text == "" ||
cbInComeByMonthFromYear.Text == "" || cbInComeByMonthToYear.Text == "")
{
MessageBox.Show("Bạn chưa thông tin tháng năm. Vui lòng chọn tháng bắt đầu và tháng kết thúc ",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
int startMonth = Convert.ToInt32(cbInComeByMonthFromMonth.Text);
int endMondth = Convert.ToInt32(cbInComeByMonthToMonth.Text);
int startYear = Convert.ToInt32(cbInComeByMonthFromYear.Text);
int endYear = Convert.ToInt32(cbInComeByMonthToYear.Text);
DateTime start = new DateTime(startYear, startMonth, 1);
DateTime end = new DateTime(endYear, endMondth, 1);
if (DateTime.Compare(start, end) > 0)
{
MessageBox.Show("Ngày bắt đầu lớn hơn ngày kết thúc. Vui lòng chọn lại thông tin ngày tháng",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else
{
DataAccess da = new DataAccess();
//string stridProduct = "";
int i = 0, j = 0;
DateTime dtCurrent = start;
int diff = (endYear - startYear) * 12 + (endMondth - startMonth);
if (dtChartData != null && dtChartData.Rows.Count > 0)
dtChartData.Rows.Clear();
if (dtChartData != null && dtChartData.Columns.Count > 0)
dtChartData.Columns.Clear();
DataColumn dcdt = new DataColumn("Ngayxuat", Type.GetType("System.String"));
dtChartData.Columns.Add(dcdt);
DataColumn dcTT = new DataColumn("ThanhTien", Type.GetType("System.String"));
dtChartData.Columns.Add(dcTT);
string strWheredate = "";
string strQuery = "";
for (j = 0; j <= diff; j++)
{
dtCurrent = start.AddMonths(j);
//Get the number of product sold in that day.
strWheredate = "And Year(Ngayxuat) = " + dtCurrent.Year +
" and Month(Ngayxuat) = " + dtCurrent.Month + " ";
DataSet dsTP_PT = null;
if (chkInComePhuThu_TienPhong.Checked)
{
string strQueryPhong_PT =
"Select IDHoadonxuat, Hoadonxuat.IDGiaLoaiPhong, Ngayxuat As NgayBan, " +
"Phuthu, GioBD, GioKT, GiaLoaiPhong.Gia, TenPhong As TenSanPham" +
" From Hoadonxuat, Phong, GiaLoaiPhong" +
" Where Hoadonxuat.IDPhong = Phong.IDPhong and " +
" Hoadonxuat.IDGiaLoaiPhong = GiaLoaiPhong.IDGiaLoaiPhong " + strWheredate;
dsTP_PT = (DataSet)(da.getDataByQuery(strQueryPhong_PT));
}
string subQuerySP = "Select Hoadonxuat.IDHoadonXuat, Ngayxuat As NgayBan, SanPham.IDSanPham, TenSanPham, Soluong, " +
"DVT, Hoadonxuat.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 " + 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, TenSanPham, Soluong, " +
"DVT, Hoadonxuat.Giam ";
strQuery = "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";
DataSet dsTemp = da.getDataByQuery(strQuery);
DataRow dr = dtChartData.NewRow();
dr["Ngayxuat"] = dtCurrent.ToString("MM/yyyy");
/////////////////////////////
if (dsTemp != null)
{
for (i = 0; i < dsTemp.Tables[0].Rows.Count; i++)
{
Decimal soluong = Convert.ToDecimal(dsTemp.Tables[0].Rows[i]["Soluong"].ToString());
Decimal gia = Convert.ToDecimal(dsTemp.Tables[0].Rows[i]["Gia"].ToString());
Decimal tt = gia * soluong;
if (!(dr["ThanhTien"] is DBNull || dr["ThanhTien"] == ""))
tt += Convert.ToDecimal(dr["ThanhTien"]);
dr["ThanhTien"] = (tt).ToString("###,###,###,###.##");
}
}
if (dsTP_PT != null)
{
for (i = 0; i < dsTP_PT.Tables[0].Rows.Count; i++)
{
Decimal gia = Convert.ToDecimal(dsTP_PT.Tables[0].Rows[i]["Gia"].ToString());
DateTime dtBD = Convert.ToDateTime(dsTP_PT.Tables[0].Rows[i]["GioBD"].ToString());
DateTime dtKT = Convert.ToDateTime(dsTP_PT.Tables[0].Rows[i]["GioKT"].ToString());
TimeSpan dif = dtKT - dtBD;
Decimal soluongGio = dif.Hours + Convert.ToDecimal(dif.Minutes) / 60;
Decimal tt = soluongGio * gia;
if (!(dr["ThanhTien"] is DBNull || dr["ThanhTien"] == ""))
tt += Convert.ToDecimal(dr["ThanhTien"]);
dr["ThanhTien"] = (tt).ToString("###,###,###,###.##");
if (dsTP_PT.Tables[0].Rows[i]["PhuThu"].ToString() != "")
{
Decimal phuThu = Convert.ToDecimal(dsTP_PT.Tables[0].Rows[i]["PhuThu"].ToString());
if (phuThu != 0)
{
if (!(dr["ThanhTien"] is DBNull || dr["ThanhTien"] == ""))
phuThu += Convert.ToDecimal(dr["ThanhTien"]);
dr["ThanhTien"] = (phuThu).ToString("###,###,###,###.##");
}
}
}
}
dtChartData.Rows.Add(dr);
////////////////////////////
}
if (dtChartData == null || dtChartData.Rows.Count == 0)
{
MessageBox.Show("Dữ liệu rỗng! Xin vui lòng chọn lại dữ liệu", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
frmChartView ch = new frmChartView(dtChartData, "ByMonth");
ch.ShowDialog();
}
}
}
}
private void cbRoomViewChart_Click(object sender, EventArgs e)
{
if (rdRoomDate.Checked)
{//By Date
if (dtselectedRoom == null || dtselectedRoom.Rows.Count <= 0)
{
MessageBox.Show("Bạn chưa chọn sản phẩm cần khảo sát. Vui lòng chọn một vài sản phẩm cần " +
"vẽ biều đồ ở khung loại sản phẩm!",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
DateTime dtFrom = dtRoomByDateFrom.Value;
DateTime dtTo = dtRoomByDateTo.Value;
if (DateTime.Compare(dtFrom, dtTo) > 0)
{
MessageBox.Show("Ngày bắt đầu lớn hơn ngày kết thúc. Vui lòng chọn lại thông tin ngày tháng",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else
{
DataAccess da = new DataAccess();
TimeSpan ts = dtTo - dtFrom;
string stridRoom = "";
int i = 0, j = 0;
DateTime dtCurrent = dtFrom;
if (dtChartData != null && dtChartData.Rows.Count > 0)
dtChartData.Rows.Clear();
if (dtChartData != null && dtChartData.Columns.Count > 0)
dtChartData.Columns.Clear();
if (listofRoomName != null)
listofRoomName = null;
if (listofRoomID != null)
listofRoomID = null;
listofRoomName = new string[dtselectedRoom.Rows.Count];
listofRoomID = new int[dtselectedRoom.Rows.Count];
DataColumn dcdt = new DataColumn("Ngayxuat", Type.GetType("System.String"));
dtChartData.Columns.Add(dcdt);
for (i = 0; i < dtselectedRoom.Rows.Count; i++)
{
DataColumn dc = new DataColumn(Convert.ToString(dtselectedRoom.Rows[i]["TenPhong"]),
Type.GetType("System.String"));
dtChartData.Columns.Add(dc);
listofRoomName[i] = Convert.ToString(dtselectedRoom.Rows[i]["TenPhong"]);
listofRoomID[i] = Convert.ToInt32(dtselectedRoom.Rows[i]["IDPhong"]);
if (i == 0)
{
stridRoom += " and (";
stridRoom += " Phong.IDPhong = " + Convert.ToString(dtselectedRoom.Rows[i]["IDPhong"]);
}
else
{
stridRoom += " or " + " Phong.IDPhong = " + Convert.ToString(dtselectedRoom.Rows[i]["IDPhong"]);
}
if (i == dtselectedRoom.Rows.Count - 1)
{
stridRoom += ")";
}
}
string strWheredate = "";
string strQuery = "";
for (j = 0; j <= ts.Days; j++)
{
dtCurrent = dtFrom.AddDays(j);
//Get the number of product sold in that day.
strWheredate = "And Year(Ngayxuat) = " + dtCurrent.Year +
" and Month(Ngayxuat) = " + dtCurrent.Month +
" and Day(Ngayxuat) = " + dtCurrent.Day + " ";
strQuery =
"Select IDHoadonxuat, Hoadonxuat.IDGiaLoaiPhong, Ngayxuat, " +
"GioBD, GioKT, TenPhong " +
" From Hoadonxuat, Phong" +
" Where Hoadonxuat.IDPhong = Phong.IDPhong " + stridRoom + strWheredate;
DataSet dsTemp = da.getDataByQuery(strQuery);
DataRow dr = dtChartData.NewRow();
dr["Ngayxuat"] = dtCurrent.ToString("MM/dd/yyyy");
if (dsTemp != null)
{
for (i = 0; i < dsTemp.Tables[0].Rows.Count; i++)
{
string colName = Convert.ToString(dsTemp.Tables[0].Rows[i]["TenPhong"]);
DateTime dtBD = Convert.ToDateTime(dsTemp.Tables[0].Rows[i]["GioBD"].ToString());
DateTime dtKT = Convert.ToDateTime(dsTemp.Tables[0].Rows[i]["GioKT"].ToString());
TimeSpan dif = dtKT - dtBD;
Decimal soluongGio = dif.Hours + Convert.ToDecimal(dif.Minutes) / 60;
if (!(dr[colName] is DBNull))
soluongGio += Convert.ToDecimal(dr[colName]);
dr[colName] = soluongGio.ToString("###,###,###,##0.##");
}
dtChartData.Rows.Add(dr);
}
}
if (dtChartData == null || dtChartData.Rows.Count == 0)
{
MessageBox.Show("Dữ liệu rỗng! Xin vui lòng chọn lại dữ liệu", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
frmChartView ch = new frmChartView(dtChartData, "ByDate", listofRoomName);
ch.ShowDialog();
}
}
}
else if (rdRoomMonth.Checked)
{//By Month
if (dtselectedRoom == null || dtselectedRoom.Rows.Count <= 0)
{
MessageBox.Show("Bạn chưa chọn sản phẩm cần khảo sát. Vui lòng chọn một vài sản phẩm cần " +
"vẽ biều đồ ở khung loại sản phẩm!",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (cbRoomByMonthFromMonth.Text == "" || cbRoomByMonthToMonth.Text == "" ||
cbRoomByMonthFromYear.Text == "" || cbRoomByMonthToYear.Text == "")
{
MessageBox.Show("Bạn chưa thông tin tháng năm. Vui lòng chọn tháng bắt đầu và tháng kết thúc ",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
int startMonth = Convert.ToInt32(cbRoomByMonthFromMonth.Text);
int endMondth = Convert.ToInt32(cbRoomByMonthToMonth.Text);
int startYear = Convert.ToInt32(cbRoomByMonthFromYear.Text);
int endYear = Convert.ToInt32(cbRoomByMonthToYear.Text);
DateTime start = new DateTime(startYear, startMonth, 1);
DateTime end = new DateTime(endYear, endMondth, 1);
if (DateTime.Compare(start, end) > 0)
{
MessageBox.Show("Ngày bắt đầu lớn hơn ngày kết thúc. Vui lòng chọn lại thông tin ngày tháng",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else
{
DataAccess da = new DataAccess();
DateTime dtCurrent = start;
int diff = (endYear - startYear) * 12 + (endMondth - startMonth);
string stridRoom = "";
int i = 0, j = 0;
if (dtChartData != null && dtChartData.Rows.Count > 0)
dtChartData.Rows.Clear();
if (dtChartData != null && dtChartData.Columns.Count > 0)
dtChartData.Columns.Clear();
if (listofRoomName != null)
listofRoomName = null;
if (listofRoomID != null)
listofRoomID = null;
listofRoomName = new string[dtselectedRoom.Rows.Count];
listofRoomID = new int[dtselectedRoom.Rows.Count];
DataColumn dcdt = new DataColumn("Ngayxuat", Type.GetType("System.String"));
dtChartData.Columns.Add(dcdt);
for (i = 0; i < dtselectedRoom.Rows.Count; i++)
{
DataColumn dc = new DataColumn(Convert.ToString(dtselectedRoom.Rows[i]["TenPhong"]),
Type.GetType("System.String"));
dtChartData.Columns.Add(dc);
listofRoomName[i] = Convert.ToString(dtselectedRoom.Rows[i]["TenPhong"]);
listofRoomID[i] = Convert.ToInt32(dtselectedRoom.Rows[i]["IDPhong"]);
if (i == 0)
{
stridRoom += " and (";
stridRoom += " Phong.IDPhong = " + Convert.ToString(dtselectedRoom.Rows[i]["IDPhong"]);
}
else
{
stridRoom += " or " + " Phong.IDPhong = " + Convert.ToString(dtselectedRoom.Rows[i]["IDPhong"]);
}
if (i == dtselectedRoom.Rows.Count - 1)
{
stridRoom += ")";
}
}
string strWheredate = "";
string strQuery = "";
for (j = 0; j <= diff; j++)
{
dtCurrent = start.AddMonths(j);
//Get the number of product sold in that day.
strWheredate = "And Year(Ngayxuat) = " + dtCurrent.Year +
" and Month(Ngayxuat) = " + dtCurrent.Month + " ";
strQuery =
"Select IDHoadonxuat, Hoadonxuat.IDGiaLoaiPhong, Ngayxuat, " +
"GioBD, GioKT, TenPhong " +
" From Hoadonxuat, Phong" +
" Where Hoadonxuat.IDPhong = Phong.IDPhong " + stridRoom + strWheredate;
DataSet dsTemp = da.getDataByQuery(strQuery);
DataRow dr = dtChartData.NewRow();
dr["Ngayxuat"] = dtCurrent.ToString("MM/yyyy");
if (dsTemp != null)
{
for (i = 0; i < dsTemp.Tables[0].Rows.Count; i++)
{
string colName = Convert.ToString(dsTemp.Tables[0].Rows[i]["TenPhong"]);
DateTime dtBD = Convert.ToDateTime(dsTemp.Tables[0].Rows[i]["GioBD"].ToString());
DateTime dtKT = Convert.ToDateTime(dsTemp.Tables[0].Rows[i]["GioKT"].ToString());
TimeSpan dif = dtKT - dtBD;
Decimal soluongGio = dif.Hours + Convert.ToDecimal(dif.Minutes) / 60;
if (!(dr[colName] is DBNull))
soluongGio += Convert.ToDecimal(dr[colName]);
dr[colName] = soluongGio.ToString("###,###,###,##0.##");
}
dtChartData.Rows.Add(dr);
}
}
if (dtChartData == null || dtChartData.Rows.Count == 0)
{
MessageBox.Show("Dữ liệu rỗng! Xin vui lòng chọn lại dữ liệu", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
frmChartView ch = new frmChartView(dtChartData, "ByMonth", listofRoomName);
ch.ShowDialog();
}
}
}
}
private void btnViewChart_Click(object sender, EventArgs e)
{
if (rdDate.Checked)
{//By Date
if (dtSelectedProduct == null || dtSelectedProduct.Rows.Count <= 0)
{
MessageBox.Show("Bạn chưa chọn sản phẩm cần khảo sát. Vui lòng chọn một vài sản phẩm cần " +
"vẽ biều đồ ở khung loại sản phẩm!",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
DateTime dtFrom = dtFromByDate.Value;
DateTime dtTo = dtToByDate.Value;
if (DateTime.Compare(dtFrom, dtTo) > 0)
{
MessageBox.Show("Ngày bắt đầu lớn hơn ngày kết thúc. Vui lòng chọn lại thông tin ngày tháng",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else
{
DataAccess da = new DataAccess();
TimeSpan ts = dtTo - dtFrom;
string stridProduct = "";
int i = 0, j = 0;
DateTime dtCurrent = dtFrom;
if (dtChartData != null && dtChartData.Rows.Count > 0)
dtChartData.Rows.Clear();
if (dtChartData != null && dtChartData.Columns.Count > 0)
dtChartData.Columns.Clear();
if (listofProductName != null)
listofProductName = null;
if (listofProductID != null)
listofProductID = null;
listofProductName = new string[dtSelectedProduct.Rows.Count];
listofProductID = new int[dtSelectedProduct.Rows.Count];
DataColumn dcdt = new DataColumn("Ngayxuat", Type.GetType("System.String"));
dtChartData.Columns.Add(dcdt);
for (i = 0; i < dtSelectedProduct.Rows.Count; i++)
{
DataColumn dc = new DataColumn(Convert.ToString(dtSelectedProduct.Rows[i]["TenSanPham"]),
Type.GetType("System.String"));
dtChartData.Columns.Add(dc);
listofProductName[i] = Convert.ToString(dtSelectedProduct.Rows[i]["TenSanPham"]);
listofProductID[i] = Convert.ToInt32(dtSelectedProduct.Rows[i]["IDSanPham"]);
if(i == 0){
stridProduct += " and (";
stridProduct += " SanPham.IDSanPham = " + Convert.ToString(dtSelectedProduct.Rows[i]["IDSanPham"]);
}else{
stridProduct += " or " + " SanPham.IDSanPham = " + Convert.ToString(dtSelectedProduct.Rows[i]["IDSanPham"]);
}
if(i == dtSelectedProduct.Rows.Count -1){
stridProduct += ")";
}
}
string strWheredate = "";
string strQuery = "";
for (j = 0; j <= ts.Days; j++)
{
dtCurrent = dtFrom.AddDays(j);
//Get the number of product sold in that day.
strWheredate = "And Year(Ngayxuat) = " + dtCurrent.Year +
" and Month(Ngayxuat) = " + dtCurrent.Month +
" and Day(Ngayxuat) = " + dtCurrent.Day + " ";
strQuery = "Select SanPham.IDSanPham, SanPham.TenSanPham, SanPham.DVT, " +
"Hoadonxuat.Ngayxuat, Sum(soluong) as TongSoluong " +
"From SanPham, ChitietHDXuat, Hoadonxuat " +
"Where SanPham.IDSanPham = ChitietHDXuat.IDSanPham and " +
"ChitietHDXuat.IDHoadonXuat = Hoadonxuat.IDHoadonXuat " +
stridProduct + strWheredate +
"Group by SanPham.IDSanPham, SanPham.TenSanPham, " +
"SanPham.DVT, Hoadonxuat.Ngayxuat " +
"ORDER BY Hoadonxuat.Ngayxuat";
DataSet dsTemp = da.getDataByQuery(strQuery);
DataRow dr = dtChartData.NewRow();
dr["Ngayxuat"] = dtCurrent.ToString("MM/dd/yyyy");
if (dsTemp != null)
{
for (i = 0; i < dsTemp.Tables[0].Rows.Count; i++)
{
string colName = Convert.ToString(dsTemp.Tables[0].Rows[i]["TenSanPham"]);
dr[colName] = Convert.ToString(dsTemp.Tables[0].Rows[i]["TongSoluong"]);
}
dtChartData.Rows.Add(dr);
}
}
if (dtChartData == null || dtChartData.Rows.Count == 0)
{
MessageBox.Show("Dữ liệu rỗng! Xin vui lòng chọn lại dữ liệu", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
frmChartView ch = new frmChartView(dtChartData, "ByDate", listofProductName);
ch.ShowDialog();
}
}
}
else if (rdMonth.Checked)
{//By Month
if (dtSelectedProduct == null || dtSelectedProduct.Rows.Count <= 0)
{
MessageBox.Show("Bạn chưa chọn sản phẩm cần khảo sát. Vui lòng chọn một vài sản phẩm cần " +
"vẽ biều đồ ở khung loại sản phẩm!",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if(cbMonthByMonth.Text == "" || cbToMonthByMonth.Text == "" ||
cbYearByMonth.Text == "" || cbToYearByMonth.Text == ""){
MessageBox.Show("Bạn chưa thông tin tháng năm. Vui lòng chọn tháng bắt đầu và tháng kết thúc ",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
DateTime dtFrom = dtFromByDate.Value;
DateTime dtTo = dtToByDate.Value;
int startMonth = Convert.ToInt32(cbMonthByMonth.Text);
int endMondth = Convert.ToInt32(cbToMonthByMonth.Text);
int startYear = Convert.ToInt32(cbYearByMonth.Text);
int endYear = Convert.ToInt32(cbToYearByMonth.Text);
DateTime start = new DateTime(startYear, startMonth, 1);
DateTime end = new DateTime(endYear, endMondth, 1);
if (DateTime.Compare(start, end) > 0)
{
MessageBox.Show("Ngày bắt đầu lớn hơn ngày kết thúc. Vui lòng chọn lại thông tin ngày tháng",
"Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else
{
DataAccess da = new DataAccess();
string stridProduct = "";
int i = 0, j = 0;
DateTime dtCurrent = start;
int diff = (endYear - startYear)*12 + (endMondth - startMonth);
if (dtChartData != null && dtChartData.Rows.Count > 0)
dtChartData.Rows.Clear();
if (dtChartData != null && dtChartData.Columns.Count > 0)
dtChartData.Columns.Clear();
if (listofProductName != null)
listofProductName = null;
if (listofProductID != null)
listofProductID = null;
listofProductName = new string[dtSelectedProduct.Rows.Count];
listofProductID = new int[dtSelectedProduct.Rows.Count];
DataColumn dcdt = new DataColumn("Ngayxuat", Type.GetType("System.String"));
dtChartData.Columns.Add(dcdt);
for (i = 0; i < dtSelectedProduct.Rows.Count; i++)
{
DataColumn dc = new DataColumn(Convert.ToString(dtSelectedProduct.Rows[i]["TenSanPham"]),
Type.GetType("System.String"));
dtChartData.Columns.Add(dc);
listofProductName[i] = Convert.ToString(dtSelectedProduct.Rows[i]["TenSanPham"]);
listofProductID[i] = Convert.ToInt32(dtSelectedProduct.Rows[i]["IDSanPham"]);
if (i == 0)
{
stridProduct += " and (";
stridProduct += " SanPham.IDSanPham = " + Convert.ToString(dtSelectedProduct.Rows[i]["IDSanPham"]);
}
else
{
stridProduct += " or " + " SanPham.IDSanPham = " + Convert.ToString(dtSelectedProduct.Rows[i]["IDSanPham"]);
}
if (i == dtSelectedProduct.Rows.Count - 1)
{
stridProduct += ")";
}
}
string strWheredate = "";
string strQuery = "";
for (j = 0; j <= diff; j++)
{
dtCurrent = start.AddMonths(j);
//Get the number of product sold in that day.
strWheredate = "And Year(Ngayxuat) = " + dtCurrent.Year +
" and Month(Ngayxuat) = " + dtCurrent.Month + " ";
strQuery = "Select SanPham.IDSanPham, SanPham.TenSanPham, SanPham.DVT, " +
"Hoadonxuat.Ngayxuat, Sum(soluong) as TongSoluong " +
"From SanPham, ChitietHDXuat, Hoadonxuat " +
"Where SanPham.IDSanPham = ChitietHDXuat.IDSanPham and " +
"ChitietHDXuat.IDHoadonXuat = Hoadonxuat.IDHoadonXuat " +
stridProduct + strWheredate +
"Group by SanPham.IDSanPham, SanPham.TenSanPham, " +
"SanPham.DVT, Hoadonxuat.Ngayxuat " +
"ORDER BY Hoadonxuat.Ngayxuat";
DataSet dsTemp = da.getDataByQuery(strQuery);
DataRow dr = dtChartData.NewRow();
dr["Ngayxuat"] = dtCurrent.ToString("MM/yyyy");
if (dsTemp != null)
{
for (i = 0; i < dsTemp.Tables[0].Rows.Count; i++)
{
string colName = Convert.ToString(dsTemp.Tables[0].Rows[i]["TenSanPham"]);
dr[colName] = Convert.ToString(dsTemp.Tables[0].Rows[i]["TongSoluong"]);
}
dtChartData.Rows.Add(dr);
}
}
if (dtChartData == null || dtChartData.Rows.Count == 0)
{
MessageBox.Show("Dữ liệu rỗng! Xin vui lòng chọn lại dữ liệu", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
frmChartView ch = new frmChartView(dtChartData, "ByMonth", listofProductName);
ch.ShowDialog();
}
}
}
}
private void button4_Click(object sender, EventArgs e)
{
//Get Data From DB
string strWhere = "";
string strWheredate = "";
string strWheredate1 = "";
string strQuerySP = "";
DateTime endDate = dtTKEndDate.Value;
DataAccess da = new DataAccess();
DataSetTonKho dsTKReport = new DataSetTonKho();
string strName = cbTKTenNhomSP.Text;
DataSet dsTKSP = null;
if ((strName != "") && (strName != "Tất cả"))
{
strWhere += " and TenNhomSP = '" + strName + "' ";
}
if (endDate != null)
{//Query endate should be modified
//strWhere += " 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 + ")) ";
strWheredate += " AND ( Year(Ngay) < " + endDate.Year +
" or (Year(Ngay) = " + endDate.Year + " and Month(Ngay) < " + endDate.Month + ") " +
" or (Year(Ngay) = " + endDate.Year + " and Month(Ngay) = " + endDate.Month + " and Day(Ngay) <= " + endDate.Day + ")) ";
strWheredate1 += " 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 + ")) ";
}
string subQuerySPTonKho = "Select IDSanPham, TenSanPham, DVT, TonKho as Soluong, IDNhomSP " +
"From SanPham ";
string subQueryGiaSP = "Select GiaXuatSP.IDSanPham, NgayXuatSP, Gia " +
"From GiaXuatSP, " +
"(Select IDSanPham, Max(NgayXuatSP) as Ngay From GiaXuatSP Group by IDSanPham) as Tbl " +
"Where GiaXuatSP.IDSanPham = Tbl.IDSanPham and " +
"Year(GiaXuatSP.NgayXuatSP) = Year(Tbl.Ngay) and " +
"Month(GiaXuatSP.NgayXuatSP) = Month(Tbl.Ngay) and " +
"Day(GiaXuatSP.NgayXuatSP) = Day(Tbl.Ngay) and " +
"Minute(GiaXuatSP.NgayXuatSP) = Minute(Tbl.Ngay) and " +
"Second(GiaXuatSP.NgayXuatSP) = Second(Tbl.Ngay)";
string subQueryNhap = "Select IDSanPham, Sum(SoLuong) as Soluong1 From ChiTietHoaDonNhap, HoaDonnhap "+
"where ChiTietHoaDonNhap.IDHoaDonNhap = HoaDonNhap.IDHoaDonNhap "+ strWheredate +" Group by IDSanPham";
string subQueryBan = "Select IDSanPham, Sum(SoLuong) as Soluong1 From ChitietHDXuat, Hoadonxuat "+
" where ChiTietHDXuat.IDHoadonXuat = Hoadonxuat.IDHoadonXuat " + strWheredate1 +" Group by IDSanPham ";
string subQueryNhap_Ban = "Select N.IDSanPham, N.Soluong1 - B.Soluong1 As Soluong, N.Soluong1 From " +
"(" + subQueryNhap + ") as N LEFT JOIN (" + subQueryBan + ") as B ON N.IDSanPham = B.IDSanPham";
string subQuerySPTonKho1 = "Select TK.IDSanPham,TK.IDNhomSP, TK.TenSanPham, TK.DVT, " +
"TK.Soluong + NB.Soluong as Soluong, TK.Soluong + NB.Soluong1 as Soluong1, TK.Soluong as Soluong2" +
" From (" + subQuerySPTonKho + ") as TK LEFT JOIN (" + subQueryNhap_Ban + ") as NB ON " +
"TK.IDSanPham = NB.IDSanPham";
strQuerySP = "Select T1.IDSanPham, T1.IDNhomSP, T1.TenSanPham, T1.DVT, T1.Soluong, T1.Soluong1, T1.Soluong2," +
" T2.Gia, T2.NgayXuatSP as NgayNhap, TenNhomSP " +
"From (" + subQuerySPTonKho1 + ") as T1 " + ", (" + subQueryGiaSP + ") as T2 " + ", NhomSP " +
"Where T1.IDSanPham = T2.IDSanPham and T1.IDNhomSP = NhomSP.IDNhomSP ";
strQuerySP += strWhere;
dsTKSP = (DataSet)(da.getDataByQuery(strQuerySP));
Decimal tongtien = 0;
int i = 0;
if (dsTKSP != null)
{
TonKhoReport = new DataSetTonKho();
for (i = 0; i < dsTKSP.Tables[0].Rows.Count; i++)
{
DataRow dr1 = TonKhoReport.Tables[0].NewRow();
Decimal soluong = 0;
if (dsTKSP.Tables[0].Rows[i]["Soluong"].ToString() != "")
soluong = Convert.ToDecimal(dsTKSP.Tables[0].Rows[i]["Soluong"].ToString());
else
{
if (dsTKSP.Tables[0].Rows[i]["Soluong1"].ToString() != "")
soluong = Convert.ToDecimal(dsTKSP.Tables[0].Rows[i]["Soluong1"].ToString());
else
soluong = Convert.ToDecimal(dsTKSP.Tables[0].Rows[i]["Soluong2"].ToString());
}
Decimal gia = Convert.ToDecimal(dsTKSP.Tables[0].Rows[i]["Gia"].ToString());
dr1["ID"] = dsTKSP.Tables[0].Rows[i]["IDSanPham"].ToString();
dr1["STT"] = (i + 1).ToString();
int index = dsTKSP.Tables[0].Rows[i]["NgayNhap"].ToString().IndexOf(' ');
dr1["NgayNhap"] = dsTKSP.Tables[0].Rows[i]["NgayNhap"].ToString().Substring(0, index);
dr1["TenSanPham"] = dsTKSP.Tables[0].Rows[i]["TenSanPham"].ToString();
dr1["DVT"] = dsTKSP.Tables[0].Rows[i]["DVT"].ToString();
dr1["DonGia"] = Convert.ToInt32(gia).ToString("###,###,###,###");
dr1["Soluong"] = Convert.ToInt32(soluong).ToString("###,###,###,###");
dr1["ThanhTien"] = (gia * soluong).ToString("###,###,###,###.##");
tongtien += gia * soluong;
TonKhoReport.Tables[0].Rows.Add(dr1);
}
DataRow dr = TonKhoReport.Tables[1].NewRow();
dr["NgayIn"] = DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss");
dr["TCThanhTien"] = tongtien.ToString("###,###,###,###.##");
TonKhoReport.Tables[1].Rows.Add(dr);
txtTK_TC.Text = tongtien.ToString("###,###,###,###.##") + " VND";
gridTK.DataSource = TonKhoReport.Tables[0];
button3.Enabled = true;
}
}
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);
}
}
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;
}
