public void XoaNhomNguoiDung(int ID)
{
using (SqlConnection myConnection = new SqlConnection(StaticContext.ConnectionString))
{
try
{
dtHangHoa dt = new dtHangHoa();
myConnection.Open();
string strSQL = "DELETE [GPM_NHOMNGUOIDUNG] WHERE [ID] = @ID";
using (SqlCommand myCommand = new SqlCommand(strSQL, myConnection))
{
myCommand.Parameters.AddWithValue("@ID", ID);
myCommand.ExecuteNonQuery();
}
}
catch (Exception e)
{
throw new Exception("Lỗi: Quá trình Xóa dữ liệu gặp lỗi, hãy tải lại trang");
}
}
}
public object InsertHoaDon(string IDKho, string IDNhanVien, string IDKhachHang, HoaDon hoaDon, string TienChietKhauKhachHang, string TienCongNoKhachHang, string TyLeChietKhauKhachHang, string TrangThaiCongNoKhachHang, string CongNoCuKhachHang, string CongNoMoiKhachHang)
{
object IDHoaDon = null;
using (SqlConnection con = new SqlConnection(StaticContext.ConnectionString))
{
SqlTransaction trans = null;
try
{
con.Open();
trans = con.BeginTransaction();
string CompuMaHoaDon = @"SELECT
REPLICATE('0', 5 - LEN((count(ID) + 1))) +
CAST((count(ID) + 1) AS varchar) + '-' +
FORMAT(GETDATE() , 'ddMMyy')
as 'Mã Hóa Đơn'
from GPM_HoaDon
where (IDKho = @IDKho)
AND DATEDIFF(dd,NgayBan, GetDate()) = 0";
object MaHoaDon;
using (SqlCommand cmd = new SqlCommand(CompuMaHoaDon, con, trans))
{
cmd.Parameters.AddWithValue("@IDKho", dtSetting.LayMaKho(IDKho));
MaHoaDon = cmd.ExecuteScalar();
}
if (MaHoaDon != null)
{
string InsertHoaDon = "INSERT INTO [GPM_HoaDon] ([IDKho], [IDKhachHang],[IDNhanVien],[NgayBan],[SoLuongHang],[TongTien],[GiamGia],[KhachCanTra],[KhachThanhToan], [MaHoaDon],[TienChietKhauKhachHang],[TyLeChietKhauKhachHang],[TrangThaiCongNoKhachHang],[CongNoCuKhachHang],[CongNoMoiKhachHang]) " +
"OUTPUT INSERTED.ID " +
"VALUES (@IDKho, @IDKhachHang, @IDNhanVien, getdate(), @SoLuongHang, @TongTien, @GiamGia, @KhachCanTra, @KhachThanhToan, @MaHoaDon,@TienChietKhauKhachHang,@TyLeChietKhauKhachHang,@TrangThaiCongNoKhachHang,@CongNoCuKhachHang,@CongNoMoiKhachHang)";
using (SqlCommand cmd = new SqlCommand(InsertHoaDon, con, trans))
{
cmd.Parameters.AddWithValue("@IDKho", IDKho);
cmd.Parameters.AddWithValue("@IDKhachHang", IDKhachHang);
cmd.Parameters.AddWithValue("@IDNhanVien", IDNhanVien);
cmd.Parameters.AddWithValue("@SoLuongHang", hoaDon.SoLuongHang);
cmd.Parameters.AddWithValue("@TongTien", hoaDon.TongTien);
cmd.Parameters.AddWithValue("@GiamGia", 0);
cmd.Parameters.AddWithValue("@KhachCanTra", hoaDon.KhachCanTra);
cmd.Parameters.AddWithValue("@KhachThanhToan", hoaDon.KhachThanhToan);
cmd.Parameters.AddWithValue("@MaHoaDon", MaHoaDon);
cmd.Parameters.AddWithValue("@TienChietKhauKhachHang", TienChietKhauKhachHang);
cmd.Parameters.AddWithValue("@TyLeChietKhauKhachHang", TyLeChietKhauKhachHang);
cmd.Parameters.AddWithValue("@TrangThaiCongNoKhachHang", TrangThaiCongNoKhachHang);
cmd.Parameters.AddWithValue("@CongNoCuKhachHang", CongNoCuKhachHang);
cmd.Parameters.AddWithValue("@CongNoMoiKhachHang", CongNoMoiKhachHang);
IDHoaDon = cmd.ExecuteScalar();
}
if (IDHoaDon != null)
{
string XoaTemp_InTruoc = "DELETE [GPM_HoaDon_Temp] DELETE [GPM_ChiTietHoaDon_Temp]";
using (SqlCommand cmd = new SqlCommand(XoaTemp_InTruoc, con, trans))
{
cmd.ExecuteNonQuery();
}
if (dtKhachHang.LayIDNhomKH(Int32.Parse(IDKhachHang)) != 1)
{
string CapNhatCongNoKhachHang = "UPDATE [GPM_KhachHang] SET [CongNo] = [CongNo] + @CongNo WHERE [ID] = @IDKhachHang";
using (SqlCommand cmd = new SqlCommand(CapNhatCongNoKhachHang, con, trans))
{
// cập nhật công nợ khách hàng
cmd.Parameters.AddWithValue("@CongNo", TienCongNoKhachHang);
cmd.Parameters.AddWithValue("@IDKhachHang", IDKhachHang);
cmd.ExecuteNonQuery();
}
}
foreach (ChiTietHoaDon cthd in hoaDon.ListChiTietHoaDon)
{
dtHangHoa dtHH = new dtHangHoa();
string InsertChiTietHoaDon = "INSERT INTO [GPM_ChiTietHoaDon] ([HeSo],[TenHangHoa],[TenDonViTinh],[DoDay],[IDHoaDon],[IDHangHoa],[GiaBan] ,[SoLuong],[ChietKhau],[ThanhTien],[NgayBan],[IDKho],[TrangThaiGia]) " +
"VALUES (@HeSo,@TenHangHoa,@TenDonViTinh,@DoDay,@IDHoaDon, @IDHangHoa, @GiaBan, @SoLuong, @ChietKhau, @ThanhTien,getdate(),@IDKho,@TrangThaiGia)";
using (SqlCommand cmd = new SqlCommand(InsertChiTietHoaDon, con, trans))
{
//thêm chi tiết hóa đơn
cmd.Parameters.AddWithValue("@IDHoaDon", IDHoaDon);
cmd.Parameters.AddWithValue("@IDHangHoa", cthd.IDHangHoa);
cmd.Parameters.AddWithValue("@GiaBan", cthd.DonGia);
cmd.Parameters.AddWithValue("@SoLuong", cthd.SoLuong);
cmd.Parameters.AddWithValue("@ChietKhau", 0.0);
cmd.Parameters.AddWithValue("@ThanhTien", cthd.ThanhTien);
cmd.Parameters.AddWithValue("@IDKho", IDKho);
cmd.Parameters.AddWithValue("@TrangThaiGia", cthd.TrangThaiGiaSiHayLe);
cmd.Parameters.AddWithValue("@DoDay", cthd.DoDay.ToString());
cmd.Parameters.AddWithValue("@TenDonViTinh", cthd.DonViTinh);
cmd.Parameters.AddWithValue("@TenHangHoa", cthd.TenHang);
cmd.Parameters.AddWithValue("@HeSo", cthd.HeSo);
cmd.ExecuteNonQuery();
}
double DoDayCu = dtHangHoa.LayDoDayHienTai(cthd.IDHangHoa);
if (DoDayCu == cthd.DoDay)
{
if (cthd.TrangThaiGiaSiHayLe == 1)
{
//bán giá lẻ
double SLTru = cthd.SoLuong / (double)cthd.HeSo;
string UpdateLichSuBanHang = "DECLARE @SoLuongCu float = 0 " +
"SELECT @SoLuongCu = SoLuongCon FROM [GPM_HangHoaTonKho] WHERE IDHangHoa = @IDHangHoa AND IDKho = @IDKho " +
"DECLARE @SoLuongMoi float = @SoLuongCu - @SoLuongBan " +
"INSERT INTO [GPM_LichSuKho] ([IDHangHoa], [IDNhanVien], [SoLuong], [SoLuongMoi], [NoiDung],[NgayCapNhat]) VALUES (@IDHangHoa, @IDNhanVien, @SoLuongCu, @SoLuongMoi, @NoiDung, GetDate()) " +
"UPDATE [GPM_HangHoaTonKho] SET SoLuongCon = @SoLuongMoi, NgayCapNhat = GetDate() WHERE IDHangHoa = @IDHangHoa AND IDKho = @IDKho";
using (SqlCommand cmd = new SqlCommand(UpdateLichSuBanHang, con, trans))
{
cmd.Parameters.AddWithValue("@SoLuongBan", SLTru.ToString());
cmd.Parameters.AddWithValue("@IDHangHoa", cthd.IDHangHoa);
cmd.Parameters.AddWithValue("@IDKho", IDKho);
cmd.Parameters.AddWithValue("@IDNhanVien", IDNhanVien);
cmd.Parameters.AddWithValue("@NoiDung", "Bán hàng lẻ");
cmd.ExecuteNonQuery();
}
}
else
{
// bán giá sỉ
string UpdateLichSuBanHang = "DECLARE @SoLuongCu float = 0 " +
"SELECT @SoLuongCu = SoLuongCon FROM [GPM_HangHoaTonKho] WHERE IDHangHoa = @IDHangHoa AND IDKho = @IDKho " +
"DECLARE @SoLuongMoi float = @SoLuongCu - @SoLuongBan " +
"INSERT INTO [GPM_LichSuKho] ([IDHangHoa], [IDNhanVien], [SoLuong], [SoLuongMoi], [NoiDung],[NgayCapNhat]) VALUES (@IDHangHoa, @IDNhanVien, @SoLuongCu, @SoLuongMoi, @NoiDung, GetDate()) " +
"UPDATE [GPM_HangHoaTonKho] SET SoLuongCon = @SoLuongMoi, NgayCapNhat = GetDate() WHERE IDHangHoa = @IDHangHoa AND IDKho = @IDKho";
using (SqlCommand cmd = new SqlCommand(UpdateLichSuBanHang, con, trans))
{
cmd.Parameters.AddWithValue("@SoLuongBan", cthd.SoLuong.ToString());
cmd.Parameters.AddWithValue("@IDHangHoa", cthd.IDHangHoa);
cmd.Parameters.AddWithValue("@IDKho", IDKho);
cmd.Parameters.AddWithValue("@IDNhanVien", IDNhanVien);
cmd.Parameters.AddWithValue("@NoiDung", "Bán hàng lẻ");
cmd.ExecuteNonQuery();
}
}
}
//ghi thẻ kho
//int TonKhoHangHoa = 0;
//string cmdText1 = " SELECT SoLuongCon FROM [GPM_HangHoaTonKho] WHERE [IDHangHoa] = '" + cthd.IDHangHoa + "' AND IDKho =" + IDKho;
//using (SqlCommand cmd1 = new SqlCommand(cmdText1, con, trans))
//using (SqlDataReader reade1r = cmd1.ExecuteReader())
//{
// DataTable tb1 = new DataTable();
// tb1.Load(reade1r);
// if (tb1.Rows.Count != 0)
// {
// DataRow dr1 = tb1.Rows[0];
// TonKhoHangHoa = Int32.Parse(dr1["SoLuongCon"].ToString());
// }
// else TonKhoHangHoa = 0;
//}
//string cmdText = "INSERT INTO [GPM_TheKho] ([MaDonHang], [NgayLap], [DienGiai], [NhapTrongKy],[XuatTrongKy],[TonCuoiKy], [IDNhanVien],[IDKho],[IDHangHoa],[LoaiPhieu],[XuatKhac],[XuatTra],[KiemKho]) OUTPUT INSERTED.ID VALUES (@MaDonHang,getdate(),@DienGiai, @NhapTrongKy,@XuatTrongKy,@TonCuoiKy,@IDNhanVien,@IDKho,@IDHangHoa,@LoaiPhieu,@XuatKhac,@XuatTra,@KiemKho)";
//using (SqlCommand cmd = new SqlCommand(cmdText, con, trans))
//{
// cmd.Parameters.AddWithValue("@XuatKhac", 0);
// cmd.Parameters.AddWithValue("@XuatTra", 0);
// cmd.Parameters.AddWithValue("@KiemKho", 0);
// cmd.Parameters.AddWithValue("@LoaiPhieu", "Xuất");
// cmd.Parameters.AddWithValue("@IDHangHoa", cthd.IDHangHoa);
// cmd.Parameters.AddWithValue("@MaDonHang", MaHoaDon);
// cmd.Parameters.AddWithValue("@DienGiai", "Bán Hàng Lẻ");
// cmd.Parameters.AddWithValue("@NhapTrongKy", 0);
// cmd.Parameters.AddWithValue("@XuatTrongKy", cthd.SoLuong);
// cmd.Parameters.AddWithValue("@TonCuoiKy", (TonKhoHangHoa));
// cmd.Parameters.AddWithValue("@IDNhanVien", IDNhanVien);
// cmd.Parameters.AddWithValue("@IDKho", IDKho);
// cmd.ExecuteNonQuery();
//}
}
}
}
trans.Commit();
con.Close();
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
throw new Exception("Quá trình lưu dữ liệu có lỗi xảy ra, vui lòng tải lại trang và thanh toán lại !!");
}
}
return(IDHoaDon);
}
public object InsertHoaDon_Temp(string IDKho, string IDNhanVien, string IDKhachHang, HoaDon hoaDon, string TienChietKhauKhachHang, string TienCongNoKhachHang, string TyLeChietKhauKhachHang, string TrangThaiCongNoKhachHang, string CongNoCuKhachHang, string CongNoMoiKhachHang)
{
object IDHoaDon = null;
using (SqlConnection con = new SqlConnection(StaticContext.ConnectionString))
{
SqlTransaction trans = null;
try
{
con.Open();
trans = con.BeginTransaction();
string InsertHoaDon = "INSERT INTO [GPM_HoaDon_Temp] ([IDKho], [IDKhachHang],[IDNhanVien],[NgayBan],[SoLuongHang],[TongTien],[GiamGia],[KhachCanTra],[KhachThanhToan], [MaHoaDon],[TienChietKhauKhachHang],[TyLeChietKhauKhachHang],[TrangThaiCongNoKhachHang],[CongNoCuKhachHang],[CongNoMoiKhachHang]) " +
"OUTPUT INSERTED.ID " +
"VALUES (@IDKho, @IDKhachHang, @IDNhanVien, getdate(), @SoLuongHang, @TongTien, @GiamGia, @KhachCanTra, @KhachThanhToan, @MaHoaDon,@TienChietKhauKhachHang,@TyLeChietKhauKhachHang,@TrangThaiCongNoKhachHang,@CongNoCuKhachHang,@CongNoMoiKhachHang)";
using (SqlCommand cmd = new SqlCommand(InsertHoaDon, con, trans))
{
cmd.Parameters.AddWithValue("@IDKho", IDKho);
cmd.Parameters.AddWithValue("@IDKhachHang", IDKhachHang);
cmd.Parameters.AddWithValue("@IDNhanVien", IDNhanVien);
cmd.Parameters.AddWithValue("@SoLuongHang", hoaDon.SoLuongHang);
cmd.Parameters.AddWithValue("@TongTien", hoaDon.TongTien);
cmd.Parameters.AddWithValue("@GiamGia", 0);
cmd.Parameters.AddWithValue("@KhachCanTra", hoaDon.KhachCanTra);
cmd.Parameters.AddWithValue("@KhachThanhToan", hoaDon.KhachThanhToan);
cmd.Parameters.AddWithValue("@MaHoaDon", "");
cmd.Parameters.AddWithValue("@TienChietKhauKhachHang", TienChietKhauKhachHang);
cmd.Parameters.AddWithValue("@TyLeChietKhauKhachHang", TyLeChietKhauKhachHang);
cmd.Parameters.AddWithValue("@TrangThaiCongNoKhachHang", TrangThaiCongNoKhachHang);
cmd.Parameters.AddWithValue("@CongNoCuKhachHang", CongNoCuKhachHang);
cmd.Parameters.AddWithValue("@CongNoMoiKhachHang", CongNoMoiKhachHang);
IDHoaDon = cmd.ExecuteScalar();
}
if (IDHoaDon != null)
{
foreach (ChiTietHoaDon cthd in hoaDon.ListChiTietHoaDon)
{
dtHangHoa dtHH = new dtHangHoa();
string InsertChiTietHoaDon = "INSERT INTO [GPM_ChiTietHoaDon_Temp] ([HeSo],[TenHangHoa],[TenDonViTinh],[DoDay],[IDHoaDon],[IDHangHoa],[GiaBan] ,[SoLuong],[ChietKhau],[ThanhTien],[NgayBan],[IDKho],[TrangThaiGia]) " +
"VALUES (@HeSo,@TenHangHoa,@TenDonViTinh,@DoDay,@IDHoaDon, @IDHangHoa, @GiaBan, @SoLuong, @ChietKhau, @ThanhTien,getdate(),@IDKho,@TrangThaiGia)";
using (SqlCommand cmd = new SqlCommand(InsertChiTietHoaDon, con, trans))
{
//thêm chi tiết hóa đơn
cmd.Parameters.AddWithValue("@IDHoaDon", IDHoaDon);
cmd.Parameters.AddWithValue("@IDHangHoa", cthd.IDHangHoa);
cmd.Parameters.AddWithValue("@GiaBan", cthd.DonGia);
cmd.Parameters.AddWithValue("@SoLuong", cthd.SoLuong);
cmd.Parameters.AddWithValue("@ChietKhau", 0.0);
cmd.Parameters.AddWithValue("@ThanhTien", cthd.ThanhTien);
cmd.Parameters.AddWithValue("@IDKho", IDKho);
cmd.Parameters.AddWithValue("@TrangThaiGia", cthd.TrangThaiGiaSiHayLe);
cmd.Parameters.AddWithValue("@DoDay", cthd.DoDay.ToString());
cmd.Parameters.AddWithValue("@TenDonViTinh", cthd.DonViTinh);
cmd.Parameters.AddWithValue("@TenHangHoa", cthd.TenHang);
cmd.Parameters.AddWithValue("@HeSo", cthd.HeSo);
cmd.ExecuteNonQuery();
}
}
//end foreach
}
trans.Commit();
con.Close();
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
throw new Exception("Quá trình lưu dữ liệu có lỗi xảy ra, vui lòng tải lại trang và thanh toán lại !!");
}
}
return(IDHoaDon);
}
public object InsertHoaDon(string IDKho, string IDNhanVien, string IDKhachHang, HoaDon hoaDon, string HinhThucGiamGia, string TyLeGiam)
{
object IDHoaDon = null;
using (SqlConnection con = new SqlConnection(StaticContext.ConnectionString))
{
SqlTransaction trans = null;
try
{
con.Open();
trans = con.BeginTransaction();
string CompuMaHoaDon = @"SELECT
REPLICATE('0', 5 - LEN((count(ID) + 1))) +
CAST((count(ID) + 1) AS varchar) + '-' +
CAST(@IDKho AS varchar) + '-' +
FORMAT(GETDATE() , 'ddMMyy')
as 'Mã Hóa Đơn'
from GPM_HoaDon
where (IDKho = @IDKho)
AND DATEDIFF(dd,NgayBan, GetDate()) = 0";
object MaHoaDon;
using (SqlCommand cmd = new SqlCommand(CompuMaHoaDon, con, trans))
{
cmd.Parameters.AddWithValue("@IDKho", dtSetting.LayMaKho(IDKho));
MaHoaDon = cmd.ExecuteScalar();
}
if (MaHoaDon != null)
{
string InsertHoaDon = "INSERT INTO [GPM_HoaDon] ([IDKho], [IDKhachHang],[IDNhanVien],[NgayBan],[SoLuongHang],[TongTien],[GiamGia],[KhachCanTra],[KhachThanhToan], [MaHoaDon],[HinhThucGiamGia],[TyLeGiam]) " +
"OUTPUT INSERTED.ID " +
"VALUES (@IDKho, @IDKhachHang, @IDNhanVien, getdate(), @SoLuongHang, @TongTien, @GiamGia, @KhachCanTra, @KhachThanhToan, @MaHoaDon,@HinhThucGiamGia,@TyLeGiam)";
using (SqlCommand cmd = new SqlCommand(InsertHoaDon, con, trans))
{
cmd.Parameters.AddWithValue("@IDKho", IDKho);
cmd.Parameters.AddWithValue("@TyLeGiam", TyLeGiam);
cmd.Parameters.AddWithValue("@IDKhachHang", IDKhachHang);
cmd.Parameters.AddWithValue("@IDNhanVien", IDNhanVien);
cmd.Parameters.AddWithValue("@SoLuongHang", hoaDon.SoLuongHang);
cmd.Parameters.AddWithValue("@TongTien", hoaDon.TongTien);
cmd.Parameters.AddWithValue("@GiamGia", hoaDon.GiamGia);
cmd.Parameters.AddWithValue("@KhachCanTra", hoaDon.KhachCanTra);
cmd.Parameters.AddWithValue("@KhachThanhToan", hoaDon.KhachThanhToan);
cmd.Parameters.AddWithValue("@MaHoaDon", MaHoaDon);
cmd.Parameters.AddWithValue("@HinhThucGiamGia", HinhThucGiamGia);
IDHoaDon = cmd.ExecuteScalar();
}
if (IDHoaDon != null)
{
foreach (ChiTietHoaDon cthd in hoaDon.ListChiTietHoaDon)
{
dtHangHoa dtHH = new dtHangHoa();
string InsertChiTietHoaDon = "INSERT INTO [GPM_ChiTietHoaDon] ([IDHoaDon],[IDHangHoa],[GiaMua],[GiaBan] ,[SoLuong],[ThanhTien],[NgayBan],[IDKho]) " +
"VALUES (@IDHoaDon, @IDHangHoa, @GiaMua, @GiaBan, @SoLuong, @ThanhTien,getdate(),@IDKho)";
using (SqlCommand cmd = new SqlCommand(InsertChiTietHoaDon, con, trans))
{
cmd.Parameters.AddWithValue("@IDHoaDon", IDHoaDon);
cmd.Parameters.AddWithValue("@IDHangHoa", cthd.IDHangHoa);
cmd.Parameters.AddWithValue("@GiaMua", cthd.GiaMua);
cmd.Parameters.AddWithValue("@GiaBan", cthd.DonGia);
cmd.Parameters.AddWithValue("@SoLuong", cthd.SoLuong);
cmd.Parameters.AddWithValue("@ThanhTien", cthd.ThanhTien);
cmd.Parameters.AddWithValue("@IDKho", IDKho);
cmd.ExecuteNonQuery();
}
string UpdateLichSuBanHang = "DECLARE @SoLuongCu float = 0 " +
"SELECT @SoLuongCu = TrongLuong FROM [CF_TonKho] WHERE IDNguyenLieu = @IDHangHoa AND IDChiNhanh = @IDKho " +
"DECLARE @SoLuongMoi float = @SoLuongCu - @SoLuongBan " +
"INSERT INTO [GPM_LichSuKho] ([IDHangHoa], [IDNhanVien], [SoLuong], [SoLuongMoi], [NoiDung],[NgayCapNhat]) VALUES (@IDHangHoa, @IDNhanVien, @SoLuongCu, @SoLuongMoi, @NoiDung, GetDate()) " +
"UPDATE [CF_TonKho] SET TrongLuong = @SoLuongMoi WHERE IDNguyenLieu = @IDHangHoa AND IDChiNhanh = @IDKho";
using (SqlCommand cmd = new SqlCommand(UpdateLichSuBanHang, con, trans))
{
cmd.Parameters.AddWithValue("@SoLuongBan", cthd.SoLuong);
cmd.Parameters.AddWithValue("@IDHangHoa", cthd.IDHangHoa);
cmd.Parameters.AddWithValue("@IDKho", IDKho);
cmd.Parameters.AddWithValue("@IDNhanVien", IDNhanVien);
cmd.Parameters.AddWithValue("@NoiDung", "Bán hàng lẻ");
cmd.ExecuteNonQuery();
}
}
}
}
trans.Commit();
con.Close();
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
throw new Exception("Quá trình lưu dữ liệu có lỗi xảy ra, vui lòng tải lại trang và thanh toán lại !!");
}
}
return(IDHoaDon);
}