public ActionResult ReportTonKho(int?MaHang, int?KhoId, int?LoaiCap3Id)
{
var user = kiemtra.getUser(User.Identity.Name);
db = ShopEntities.CreateEntitiesForSpecificDatabaseName(user.MaCN);
DataSetTonKho ds = new DataSetTonKho();
DataTable dtKho = ds.DataTableKho;
DataTable dtTonKho = ds.DataTableTonKho;
ds.EnforceConstraints = false;
var Khos = db.KhoHangs.ToList();
var TonKhos = db.ChiTietKhoHangs.ToList();
if (MaHang.HasValue)
{
TonKhos = TonKhos.Where(x => x.MatHang.ItemId == MaHang).ToList();
}
var Kho = new KhoHang();
if (KhoId.HasValue)
{
Kho = Khos.FirstOrDefault(x => x.WarehouseID == KhoId);
TonKhos = TonKhos.Where(x => x.KhoHang.WarehouseID == KhoId).ToList();
}
if (LoaiCap3Id.HasValue)
{
TonKhos = TonKhos.Where(x => x.MatHang.LoaiCap3Id == LoaiCap3Id.Value).ToList();
}
if (Kho.WarehouseID != 0)
{
dtKho.Rows.Add(Kho.WarehouseID, Kho.Name, Kho.DiaChi, Kho.SDT);
}
else
{
foreach (var item in Khos)
{
dtKho.Rows.Add(item.WarehouseID, item.Name, item.DiaChi, item.SDT);
}
}
foreach (var item in TonKhos)
{
dtTonKho.Rows.Add(item.MatHang.Name, item.MatHang.QuyCach.Name, item.MatHang.LoaiCap3.Name, item.Quantities, item.WarehouseID);
}
CrystalReportTonKho rpt = new CrystalReportTonKho();
rpt.Load();
rpt.SetDataSource(ds);
Stream s = rpt.ExportToStream(ExportFormatType.PortableDocFormat);
return(File(s, "application/pdf"));
}
public frmViewReport(DataSetTonKho _ds)
{
InitializeComponent();
mode = 3;
ds3 = _ds;
}
public frmViewReport(DataSetTonKho _ds)
{
InitializeComponent();
mode = 3;
ds3 = _ds;
}
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;
}
}