Beispiel #1
0
        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;
 }
Beispiel #3
0
 public frmViewReport(DataSetTonKho _ds)
 {
     InitializeComponent();
     mode = 3;
     ds3 = _ds;
 }
Beispiel #4
0
        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;
            }
        }