public async Task <object> chiTietDonDatHangAD(int MaDDH) { var connection = new SqlConnection(connectionstr); string queryGroupByDDH = "SELECT SP.MaSP, (SELECT SPL.TenSP From SANPHAM_LOAI SPL" + " WHERE SPL.MaSP = SP.MaSP) as TenSP,COUNT(SP.MaSP)AS SoLuong, CTDDH.DonGia " + "FROM CHITIETDONDATHANG CTDDH " + "LEFT JOIN SANPHAM SP ON CTDDH.MaSeri = SP.MaSeri " + "WHERE MaDDH=" + MaDDH + " GROUP BY MaSP, DonGia"; var chiTiet = connection.Query <SanPhamDDH>(queryGroupByDDH, commandType: CommandType.Text); var p = new DynamicParameters(); p.Add("@ID", MaDDH); p.Add("@TABLE", "DONDATHANG"); DonDatHang donDatHang = connection.QuerySingleOrDefault <DonDatHang>("SP_GETDETAILBYID", p, commandType: CommandType.StoredProcedure); if (donDatHang == null) { var response = await tbl.TBLoi(ThongBaoLoi.Loi500, "Order is not exist"); return(response.Content); } string queryND = "select TaiKhoan, HoTen, Email, SoDT, DiaChi from NGUOIDUNG where MaND =" + donDatHang.MaKH; NguoiDungInforVM khachHang = connection.QuerySingleOrDefault <NguoiDungInforVM>(queryND, commandType: CommandType.Text); string queryNV = "select TaiKhoan, HoTen, Email, SoDT, DiaChi from NGUOIDUNG where MaND =" + donDatHang.MaNV; NguoiDungInforVM nhanVien = connection.QuerySingleOrDefault <NguoiDungInforVM>(queryNV, commandType: CommandType.Text); ChiTietDonDatHangVM ct = new ChiTietDonDatHangVM(); ct.MaDDH = MaDDH; ct.NgayXuLy = donDatHang.NgayXuLy; ct.NgayDat = donDatHang.NgayDat; ct.TrangThai = donDatHang.TrangThai; ct.TinhTrang = donDatHang.TinhTrang; ct.DiaChiNhan = donDatHang.DiaChiNhan; ct.TenNguoiNhan = donDatHang.TenNguoiNhan; ct.Email = donDatHang.Email; ct.SoDT = donDatHang.SoDT; ct.TongTien = 0; ct.khachHang = khachHang; ct.nhanVien = nhanVien; //int idND = donDatHang.MaKH; foreach (var item in chiTiet) { SanPhamDDH itemOrder = new SanPhamDDH(); itemOrder.MaSP = item.MaSP; itemOrder.tenSP = item.tenSP; itemOrder.SoLuong = item.SoLuong; itemOrder.DonGia = item.DonGia; ct.TongTien += (itemOrder.SoLuong * itemOrder.DonGia); ct.DSSanPhamDDH.Add(itemOrder); } return(ct); }
private async Task <ChiTietPhieuNhapVM> chiTietPhieuNhapAD(int maPN) { var connection = new SqlConnection(connectionstr); string queryGroupByDDH = @"SELECT SP.MaSP, (SELECT SPL.TenSP From SANPHAM_LOAI SPL WHERE SPL.MaSP = SP.MaSP) as TenSP,COUNT(SP.MaSP)AS SoLuong, CTDDH.DonGia FROM CHITIETPHIEUNHAP CTDDH LEFT JOIN SANPHAM SP ON CTDDH.MaSeri = SP.MaSeri WHERE MaPN=" + maPN + " GROUP BY MaSP, DonGia"; var chiTiet = connection.Query <SanPhamDDH>(queryGroupByDDH, commandType: CommandType.Text); var p = new DynamicParameters(); p.Add("@ID", maPN); p.Add("@TABLE", "PHIEUNHAP"); PhieuNhap phieuNhap = connection.QuerySingleOrDefault <PhieuNhap>("SP_GETDETAILBYID", p, commandType: CommandType.StoredProcedure); string queryND = "select TaiKhoan, HoTen, Email, SoDT, DiaChi from NGUOIDUNG where MaND =" + phieuNhap.MaNV; NguoiDungInforVM nhanVien = connection.QuerySingleOrDefault <NguoiDungInforVM>(queryND, commandType: CommandType.Text); ChiTietPhieuNhapVM ct = new ChiTietPhieuNhapVM(); ct.MaPN = maPN; ct.MaCode = phieuNhap.MaCode; ct.TrangThai = phieuNhap.TrangThai; ct.MaNCC = phieuNhap.MaNCC; ct.MaNV = phieuNhap.MaNV; ct.NgayTao = phieuNhap.NgayTao; ct.TongTien = phieuNhap.TongTien; ct.nhanVien = nhanVien; foreach (var item in chiTiet) { SanPhamLoaiPhieuNhap itemPN = new SanPhamLoaiPhieuNhap(); itemPN.MaSP = item.MaSP; itemPN.tenSP = item.tenSP; itemPN.SoLuong = item.SoLuong; itemPN.DonGia = item.DonGia; string querySeri = "select CT.MaSeri from CHITIETPHIEUNHAP CT " + "INNER JOIN SANPHAM SP ON CT.MaSeri = SP.MaSeri " + "where MaPN = " + maPN + " and SP.MaSP ='" + item.MaSP + "'"; var sp = await connection.QueryAsync <string>(querySeri, commandType : CommandType.Text); foreach (var spnhap in sp) { itemPN.dsSeriSanPham.Add(spnhap); } ct.TongTien += (itemPN.SoLuong * itemPN.DonGia); ct.DSSanPhamNhap.Add(itemPN); } return(ct); }
public async Task <dynamic> chiTietBinhLuanAD(int maBL) { ChiTietBinhLuanVM chiTietBL = new ChiTietBinhLuanVM(); using (var connection = new SqlConnection(connectionstr)) { var result = connection.QuerySingleOrDefault <ChiTietBinhLuanVM>(" SELECT MaBL,NgayTao, DanhGia, NoiDung, SP.MaSP, TenSP," + " SP.DonGia, SP.HinhAnh, SP.TrangThai, SP.SoLuongTon, MaKH, ND.HoTen, ND.TaiKhoan, ND.Email, ND.SoDT " + "FROM BINHLUAN BL " + "INNER JOIN SANPHAM_LOAI SP ON BL.MaSP = SP.MaSP " + "INNER JOIN NGUOIDUNG ND ON BL.MaKH = ND.MaND " + "WHERE BL.MaBL =" + maBL, commandType: CommandType.Text); if (result == null) { return("Review is not exitst"); } string queryND = "select TaiKhoan, HoTen, Email, SoDT, DiaChi from NGUOIDUNG where MaND =" + result.MaKH; NguoiDungInforVM khachHang = connection.QuerySingleOrDefault <NguoiDungInforVM>(queryND, commandType: CommandType.Text); chiTietBL.MaBL = result.MaBL; chiTietBL.NoiDung = result.NoiDung; chiTietBL.DanhGia = result.DanhGia; chiTietBL.NgayTao = result.NgayTao; chiTietBL.MaSP = result.MaSP; chiTietBL.MaKH = result.MaKH; chiTietBL.KhachHang = khachHang; string querySP = "select MaSP, HinhAnh, TenSP, SoLuongTon, TrangThai, DonGia from SANPHAM_LOAI where MaSP ='" + result.MaSP + "'"; SanPhamVM sanPham = connection.QuerySingleOrDefault <SanPhamVM>(querySP, commandType: CommandType.Text); chiTietBL.SanPham = sanPham; var lstBL = connection.Query <BinhLuanHoiDap>("SELECT * FROM TRALOIBL WHERE MaBL=" + maBL, commandType: CommandType.Text); if (lstBL != null) { foreach (var bl in lstBL) { BinhLuanHoiDap binhLuan = new BinhLuanHoiDap(); binhLuan.MaTL = bl.MaTL; binhLuan.MaBL = bl.MaBL; binhLuan.MaNV = bl.MaNV; binhLuan.NgayTao = bl.NgayTao; binhLuan.NoiDung = bl.NoiDung; chiTietBL.dsHoiDap.Add(binhLuan); } } } return(chiTietBL); }