public PhieuNhapHang GetPhieuNhapByID(string id_phieu)
{
SQLiteConnection conn = new SQLiteConnection(conString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select * from Phieu_Nhap_Hang join Loai_Qua on Phieu_Nhap_Hang.id_loai = Loai_Qua.id_loai " +
"where id_phieu_nhap_hang = @id_phieu";
cmd.Parameters.AddWithValue("@id_phieu", id_phieu);
SQLiteDataReader rd = cmd.ExecuteReader();
PhieuNhapHang pn = new PhieuNhapHang();
while (rd.Read())
{
pn.id_loai = rd["id_loai"].ToString();
pn.ngay_nhap = rd["ngay_nhap"].ToString();
pn.so_luong = rd["so_luong"].ToString();
pn.gia_nhap = rd["gia_nhap"].ToString();
pn.id_cssx = rd["id_cssx"].ToString();
pn.ten_loai = rd["ten_loai"].ToString();
}
conn.Close();
return(pn);
}
public IEnumerable <PhieuNhapHang> GetListPhieuNhap()
{
SQLiteConnection conn = new SQLiteConnection(conString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select id_phieu_nhap_hang,id_cssx,Phieu_Nhap_Hang.id_loai,ten_loai,ngay_nhap,so_luong,gia_nhap " +
"from Phieu_Nhap_Hang join Loai_Qua on Phieu_Nhap_Hang.id_loai = Loai_Qua.id_loai";
SQLiteDataReader rd = cmd.ExecuteReader();
List <PhieuNhapHang> li = new List <PhieuNhapHang>();
while (rd.Read())
{
PhieuNhapHang pn = new PhieuNhapHang();
pn.id_phieu_nhap_hang = rd["id_phieu_nhap_hang"].ToString();
pn.id_cssx = rd["id_cssx"].ToString();
pn.id_loai = rd["id_loai"].ToString();
pn.ten_loai = rd["ten_loai"].ToString();
pn.ngay_nhap = rd["ngay_nhap"].ToString();
pn.so_luong = rd["so_luong"].ToString();
pn.gia_nhap = rd["gia_nhap"].ToString();
li.Add(pn);
}
conn.Close();
return(li);
}
public IEnumerable <PhieuNhapHang> GetListPhieuNhapGroupByIDLoai()
{
SQLiteConnection conn = new SQLiteConnection(conString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select Phieu_Nhap_Hang.id_loai,ten_loai,gia_nhap,ngay_nhap,sum(so_luong) as sl " +
"from Phieu_Nhap_Hang join Loai_Qua on Phieu_Nhap_Hang.id_loai = Loai_Qua.id_loai " +
"group by Phieu_Nhap_Hang.id_loai,ten_loai,gia_nhap,ngay_nhap " +
"order by ngay_nhap asc";
SQLiteDataReader rd = cmd.ExecuteReader();
List <PhieuNhapHang> li = new List <PhieuNhapHang>();
while (rd.Read())
{
PhieuNhapHang pn = new PhieuNhapHang();
pn.id_loai = rd["id_loai"].ToString();
pn.ten_loai = rd["ten_loai"].ToString();
pn.ngay_nhap = rd["ngay_nhap"].ToString();
pn.so_luong = rd["sl"].ToString();
pn.gia_nhap = rd["gia_nhap"].ToString();
li.Add(pn);
}
conn.Close();
return(li);
}
public async Task <IActionResult> APIGetPhieuNhapHang(int id)
{
try
{
List <object> result = new List <object>();
PhieuNhapHang content = await _bookStoreData
.GetPhieuNhapHangById(id);
foreach (var item in content.ChiTietPhieuNhapHang)
{
result.Add(new {
HangHoaId = item.HangHoaId,
TenHangHoa = item.HangHoa.TenHangHoa,
GiaNhap = (int)item.GiaNhap,
SoLuong = item.SoLuong
});
}
return(Json(result));
}
catch (Exception ex)
{
return(null);
}
}
public frmInDonNhapThietBi(DTO.NhaCungCap nhaCungCap, PhieuNhapHang phieuNhap, bool bDaGiaoHang = false)
{
this.phieuNhap = phieuNhap;
this.nhaCungCap = nhaCungCap;
this.bDaGiaoHang = bDaGiaoHang;
InitializeComponent();
}
public static ArrayList getAllPhieuNhapHang()
{
ArrayList dataList = new ArrayList();
try
{
DataSet dataSet = PhieuNhapHangDAO.getAllPhieuNhapHang();
foreach (DataRow row in dataSet.Tables[0].Rows)
{
int id = Convert.ToInt32(row["id"]);
int idMatHang = Convert.ToInt32(row["idMatHang"]);
DateTime ngayNhap = (DateTime)row["ngayNhap"];
int soLuong = Convert.ToInt32(row["soLuong"]);
float tongTien = (float)Convert.ToDouble(row["TongTien"]);
PhieuNhapHang phieuNhapHang = new PhieuNhapHang(id, idMatHang, ngayNhap, soLuong, tongTien);
dataList.Insert(0, phieuNhapHang);
}
}
catch (SqlException e)
{
}
return(dataList);
}
//huy don hang
public void HuyPhieuNhap(string ma)
{
PhieuNhapHang hd = da.PhieuNhapHangs.Where(t => t.maPN == ma).SingleOrDefault();
da.PhieuNhapHangs.DeleteOnSubmit(hd);
da.SubmitChanges();
}
// Hien thi SLDH theo MASP va MADH
public DataTable HienThiSlDhTheoMaDhMaSp(PhieuNhapHang pn)
{
try
{
OpenConnection();
SqlCommand sqlcommand = new SqlCommand();
sqlcommand.CommandType = CommandType.StoredProcedure;
sqlcommand.CommandText = "sp_HienThiSlDhChoBangPhieuNhap";
sqlcommand.Connection = conn;
sqlcommand.Parameters.Add("@MASP", SqlDbType.Int).Value = pn.MaSp;
sqlcommand.Parameters.Add("@MADH", SqlDbType.Int).Value = pn.MaDh;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlcommand);
DataTable dt = new DataTable();
sqlDataAdapter.Fill(dt);
return(dt);
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
public frmNhapCTNguyenLieu(DTO.NhaCungCap nhaCungCap, PhieuNhapHang phieuNhapHang)
{
this.nhaCungCap = nhaCungCap;
this.phieuNhapHang = phieuNhapHang;
InitializeComponent();
khoiTao();
}
// Sửa Phiếu Nhập
public bool EditPhieuNhap(PhieuNhapHang pn)
{
var result = phieuNhapDAL.sp_UPDATE_PHIEUNHAP(pn.MaNv, pn.MaDh, pn.MaSp, pn.Sln, pn.DonGias, pn.NgayNhap, pn.MaPn);
phieuNhapDAL.SubmitChanges();
return(true);
}
// Xóa Phiếu Nhập
public bool DeletePhieuNhap(PhieuNhapHang pn)
{
var result = phieuNhapDAL.sp_DELETE_PHIEUNHAP(pn.MaPn, pn.MaSp);
phieuNhapDAL.SubmitChanges();
return(true);
}
public frmNhapChiTietSanPham(DTO.NhaCungCap nhaCungCap, PhieuNhapHang phieuNhapHang = null)
{
this.nhaCungCap = nhaCungCap;
this.phieuNhapHang = phieuNhapHang;
InitializeComponent();
khoiTao();
}
// Hiển Thị Tên Sản Phẩm Theo Mã Sản Phẩm
public DataTable HienThiTenSanPhamTheoMaSanPham(PhieuNhapHang pn)
{
try
{
OpenConnection();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "SELECT TENSP FROM dbo.SANPHAM WHERE MASP=@MASP";
sqlCommand.Connection = conn;
sqlCommand.Parameters.Add("@MASP", SqlDbType.Int).Value = pn.MaSp;
SqlDataAdapter sqlData = new SqlDataAdapter(sqlCommand);
DataTable dt = new DataTable();
sqlData.Fill(dt);
return(dt);
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
// Hiển Thị Tên Nhà Cung Cấp Theo Mã Nhà Cung
//public DataTable HienThiTenNhaCungCapTheoMaCungCap(PhieuNhapHang pn)
//{
// try
// {
// OpenConnection();
// SqlCommand sqlCommand = new SqlCommand();
// sqlCommand.CommandType = CommandType.Text;
// sqlCommand.CommandText= "SELECT MADH FROM dbo.DONDH WHERE MADH=@MADH";
// sqlCommand.Connection = conn;
// sqlCommand.Parameters.Add("@MADH", SqlDbType.Int).Value = pn.MaDh;
// SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
// DataTable dt = new DataTable();
// sqlDataAdapter.Fill(dt);
// return (dt);
// }
// catch
// {
// throw;
// }
// finally
// {
// CloseConnection();
// }
//}
// Thêm Phiếu Nhập
public bool ThemPhieuNhap(PhieuNhapHang pn)
{
try
{
OpenConnection();
bool isCheck = false;
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_Insert_PHIEUNHAP";
sqlCommand.Connection = conn;
sqlCommand.Parameters.Add("@MANV", SqlDbType.Int).Value = pn.MaNv;
sqlCommand.Parameters.Add("@MADH", SqlDbType.Int).Value = pn.MaDh;
sqlCommand.Parameters.Add("@MASP", SqlDbType.Int).Value = pn.MaSp;
sqlCommand.Parameters.Add("@SLN", SqlDbType.Int).Value = pn.Sln;
sqlCommand.Parameters.Add("@DONGIA", SqlDbType.Money).Value = pn.DonGias;
sqlCommand.Parameters.Add("@NGAYNHAP", SqlDbType.DateTime).Value = pn.NgayNhap;
int result = sqlCommand.ExecuteNonQuery();
if (result > 0)
{
isCheck = true;
}
return(isCheck);
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
private void btnThem_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Bạn có muốn lập phiếu không?", "Xác nhận", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
DTO.NhaCungCap nhaCungCap = ((DTO.NhaCungCap)cmbNhaCungCap.SelectedValue);
PhieuNhapHang soPhieuNhap = BUS.NguyenLieuBUS.LapPhieuNhap(nhaCungCap.MaNCC, User.NhanVien.MaNV);
if (soPhieuNhap != null)
{
MessageBox.Show("Lập phiếu thành công");
frmNhapCTNguyenLieu nhapCTNguyenLieu = new frmNhapCTNguyenLieu(nhaCungCap, soPhieuNhap);
nhapCTNguyenLieu.ShowDialog();
pageNumber = 1;
txtPageNumber.Text = "1";
totalPage = BUS.NguyenLieuBUS.DemPhieuNhapNguyenLieu(rbChuaGiao.Checked ? 0 : 1);
totalPage = Utility.TinhKichThuocTrang(totalPage, pageSize);
txtTotalPage.Text = totalPage.ToString();
bindingSource.DataSource = BUS.NguyenLieuBUS.XemPhieuNhapHang(rbChuaGiao.Checked ? 0 : 1, pageNumber, pageSize);
}
else
{
MessageBox.Show("Có lỗi xảy ra. Hãy báo cho nhà cung cấp phần mềm để biết thêm chi tiết");
}
}
else if (result == DialogResult.No)
{
//...
}
else
{
//...
}
}
// Delete Phieu Nhap
public bool DeletePhieuNhap(PhieuNhapHang pn)
{
try
{
bool isCheck = false;
OpenConnection();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_DELETE_PHIEUNHAP";
sqlCommand.Connection = conn;
sqlCommand.Parameters.Add("@MAPN", SqlDbType.Int).Value = pn.MaPn;
int result = sqlCommand.ExecuteNonQuery();
if (result > 0)
{
isCheck = true;
}
return(isCheck);
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
public async Task <int> AddPhieuNhapHang(PhieuNhapHang phieuNhapHang,
ICollection <ChiTietPhieuNhapHang> properties)
{
phieuNhapHang.TrangThaiId =
(await GetTrangThai("Waiting", "PhieuNhapHang")
.FirstOrDefaultAsync()).Id;
await _context.PhieuNhapHang.AddAsync(phieuNhapHang);
foreach (var property in properties)
{
property.PhieuNhapHangId = phieuNhapHang.Id;
phieuNhapHang.TongTien += property.SoLuong * property.GiaNhap;
}
await _context.ChiTietPhieuNhapHang.AddRangeAsync(properties);
PhieuChi phieuChi = new PhieuChi()
{
NhanVienId = phieuNhapHang.NhanVienId,
NgayLap = phieuNhapHang.NgayLap,
PhieuNhapHangId = phieuNhapHang.Id,
TongTien = phieuNhapHang.TongTien,
};
return(await AddPhieuChiNhapHang(phieuChi));
}
public IActionResult LuuPhieuNhapKho()
{
double TongTien = 0;
PhieuNhapHang pnh = new PhieuNhapHang
{
NgayNhap = DateTime.Now,
TongTien = TongTien
};
db.PhieuNhapHang.Add(pnh);
db.SaveChanges();
foreach (var item in ListNhaps)
{
TongTien += item.DonGiaNhap * item.SoLuongNhap;
ChiTietPhieuNhap ct = new ChiTietPhieuNhap
{
MaPn = pnh.MaPn,
MaHh = item.MaHh,
KichCo = item.KichCo,
SoLuongNhap = item.SoLuongNhap,
DonGiaNhap = item.DonGiaNhap
};
SanPhamKho spk = db.SanPhamKho.SingleOrDefault(p => p.MaHh == ct.MaHh && p.KichCo == ct.KichCo);
spk.SoLuong += ct.SoLuongNhap;
db.ChiTietPhieuNhap.Add(ct);
db.SaveChanges();
}
pnh.TongTien = TongTien;
db.SaveChanges();
HttpContext.Session.Remove("ListNhap");
return(RedirectToAction("Index"));
}
private void LoadPhieuNhapHang()
{
PhieuNhapHang pbh = new PhieuNhapHang();
pbh.Dock = DockStyle.Fill;
ucBanHang1.Controls.Clear();
ucBanHang1.Controls.Add(pbh);
ucBanHang1.Dock = DockStyle.Fill;
}
//them phieu nhap
public void ThemPhieuNhap(string ma, string ngaylap, string manv)
{
PhieuNhapHang hd = new PhieuNhapHang();
hd.maPN = ma;
hd.NgayNhap = DateTime.Parse(ngaylap);
hd.maNV = manv;
da.PhieuNhapHangs.InsertOnSubmit(hd);
da.SubmitChanges();
}
private void Frm_InPhieuNhapHang_Load(object sender, EventArgs e)
{
var query = from cthd in dbContext.Chitiet_HoaDonNhapHangs
where cthd.id_nhaphang == idNhapHang
select cthd;
PhieuNhapHang report = new PhieuNhapHang();
report.DataSource = query;
documentViewer1.PrintingSystem = report.PrintingSystem;
report.CreateDocument();
}
private void btnSua_Click(object sender, EventArgs e)
{
if (dGVPhieuNhap.CurrentRow != null)
{
int index = dGVPhieuNhap.CurrentRow.Index;
DTO.NhaCungCap nhaCungCap = ((DTO.NhaCungCap)cmbNhaCungCap.SelectedValue);
PhieuNhapHang soPhieuNhap = (PhieuNhapHang)bindingSource[index];
frmNhapCTNguyenLieu nhapCTNguyenLieu = new frmNhapCTNguyenLieu(nhaCungCap, soPhieuNhap);
nhapCTNguyenLieu.ShowDialog();
bindingSource.DataSource = BUS.NguyenLieuBUS.XemPhieuNhapHang(rbChuaGiao.Checked ? 0 : 1, pageNumber, pageSize);
}
}
// Hiển Thị Giá Thành
public DataTable HienThiGiaThanhMaSp(PhieuNhapHang pn)
{
var result = phieuNhapDAL.sp_HienThiGiaThanhTheoMaSP(pn.MaSp);
DataTable table = new DataTable();
table.Columns.Add("GIATHANH", typeof(Int32));
foreach (sp_HienThiGiaThanhTheoMaSPResult gt in result)
{
table.Rows.Add(gt.GIATHANH);
}
return(table);
}
// lay ma phiếu nhập cuoi cung
public string getMAPNLast()
{
List <PhieuNhapHang> a = da.PhieuNhapHangs.ToList();
if (a.Count == 0)// neu chua co hoa don nao
{
return("");
}
//da co ma hoa don
PhieuNhapHang b = da.PhieuNhapHangs.ToList().OrderByDescending(t => t.maPN).First();// lay hoa don dau tien giam dan theo ma
return(b.maPN);
}
// Tim Kiem Phieu Nhap Hang
public List <PhieuNhapHang> TimKiemPhieuNhap(PhieuNhapHang pn)
{
var result = phieuNhapDAL.sp_TimKiemPhieuNhap(pn.Search);
List <PhieuNhapHang> list = new List <PhieuNhapHang>();
foreach (sp_TimKiemPhieuNhapResult pns in result)
{
list.Add(new PhieuNhapHang {
MaPn = pns.MAPN, MaNv = pns.MANV, NgayNhap = pns.NGAYNHAP, MaDh = pns.MADH, MaSp = pns.MASP, Sln = pns.SLN, DonGias = pns.DONGIA
});
}
return(list);
}
// Hiển Thị Mã Nhà Cung Cấp
public DataTable LoadComboxBoxMaDh()
{
var result = phieuNhapDAL.sp_HienThiDanhSachPhieuNhapTheoMaDH();
DataTable table = new DataTable();
table.Columns.Add("MADH", typeof(Int32));
foreach (sp_HienThiDanhSachPhieuNhapTheoMaDHResult pn in result)
{
PhieuNhapHang pnh = new PhieuNhapHang();
pnh.MaDh = pn.MADH;
table.Rows.Add(pnh.MaDh);
}
return(table);
}
// Hiển Thị Mã Sản Phẩm
public DataTable LoadComboBoxMaSanPham()
{
var result = phieuNhapDAL.sp_HienThiDanhSachPhieuNhapTheoMaSP();
DataTable table = new DataTable();
table.Columns.Add("MASP", typeof(Int32));
foreach (sp_HienThiDanhSachPhieuNhapTheoMaSPResult pn in result)
{
PhieuNhapHang pnh = new PhieuNhapHang();
pnh.MaSp = pn.MASP;
table.Rows.Add(pnh.MaSp);
}
return(table);
}
public ActionResult Edit(int id, PhieuNhapHang pn)
{
try
{
// TODO: Add update logic here
pn.id_phieu_nhap_hang = id.ToString();
new PhieuNhapHangDAO().UpdatePhieuNhap(pn);
return(RedirectToAction("Index"));
}
catch
{
return(View());
}
}
public ActionResult Create(PhieuNhapHang pn)
{
try
{
// TODO: Add insert logic here
new PhieuNhapHangDAO().InsertPhieuNhap(pn);
new PhieuNhapHangDAO().InsertToQua(pn);
return(RedirectToAction("Index"));
}
catch
{
return(View("Index"));
}
}
private void btnSua_Click(object sender, EventArgs e)
{
if (dGVPhieuNhap.CurrentRow != null)
{
int index = dGVPhieuNhap.CurrentRow.Index;
DTO.NhaCungCap nhaCungCap = ((DTO.NhaCungCap)cmbNhaCungCap.SelectedValue);
PhieuNhapHang phieuNhapHang = (PhieuNhapHang)bindingSource[index];
string soPhieuNhap = dGVPhieuNhap[0, index].Value.ToString();
frmNhapChiTietSanPham chiTietSanPham = new
frmNhapChiTietSanPham(nhaCungCap, phieuNhapHang);
chiTietSanPham.ShowDialog();
}
}
