本文整理汇总了C#中BKIT.Model.DataAccess.getDataByQuery方法的典型用法代码示例。如果您正苦于以下问题：C# DataAccess.getDataByQuery方法的具体用法？C# DataAccess.getDataByQuery怎么用？C# DataAccess.getDataByQuery使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类BKIT.Model.DataAccess的用法示例。
在下文中一共展示了DataAccess.getDataByQuery方法的6个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: btnInComeViewChart_Click
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)
//.........这里部分代码省略.........
示例2: cbRoomViewChart_Click
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);
//.........这里部分代码省略.........
示例3: btnViewChart_Click
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)
//.........这里部分代码省略.........
示例4: button4_Click
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;
}
}示例5: btnTKHDReport_Click
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);
//.........这里部分代码省略.........
示例6: btnDSBHReport_Click
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";
//.........这里部分代码省略.........