public frmViewReport(DataSetTKHD _ds)
{
InitializeComponent();
mode = 6;
ds6 = _ds;
}
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);
}
}
