public static void XoaDatBan(int IDBan)
{
string sTruyVan = string.Format(@"SELECT * FROM [CF_HoaDon] WHERE IDBan = {0} AND [TrangThai] = 0", IDBan);
DataTable data = new DataTable();
data = DataProvider.TruyVanLayDuLieu(sTruyVan);
if (data.Rows.Count > 0)
{
DTO_HoaDon hd = new DTO_HoaDon(data.Rows[0]);
sTruyVan = string.Format(@"DELETE FROM [CF_ChiTietHoaDon] WHERE IDHoaDon = {0}", hd.ID);
DataProvider.TruyVanKhongLayDuLieu(sTruyVan);
sTruyVan = string.Format(@"DELETE FROM [CF_HoaDon] WHERE ID = {0} AND [TrangThai] = 0", hd.ID);
DataProvider.TruyVanKhongLayDuLieu(sTruyVan);
}
}
/// <summary>
/// hàm kiểm tra key
/// </summary>
/// <param name="Key"></param>
/// <param name="user"></param>
/// <returns></returns>
public static int setKeyCode(string Key, string user)
{
string sx = GetHardDiskSerialNo();
string strAddress = sx + "GPM";
if (KiemTraTenKey(Key) != "")
{
string sha1Address = GetSHA1HashData(strAddress);
string sTruyVan = string.Format(@"INSERT INTO [CF_KeyCode] (GetKey,NgayKichHoat) VALUES('{0}',getdate())", sha1Address);
DataProvider.TruyVanKhongLayDuLieu(sTruyVan);
string sTruyVan1 = string.Format(@"Update CF_KeyKichHoat set [SoLanKichHoat] = [SoLanKichHoat] - 1 where TenKey = N'{0}'", Key);
DataProvider.TruyVanKhongLayDuLieu(sTruyVan1);
return(1);
}
return(-1);
}
public static void XoaDatBan(int IDBan, string IDChiNhanh)
{
string sTruyVan = string.Format(@"SELECT * FROM [CF_HoaDon] WHERE IDBan = {0} AND [TrangThai] = 0 AND [IDChiNhanh] = '{1}'", IDBan, IDChiNhanh);
DataTable data = new DataTable();
data = DataProvider.TruyVanLayDuLieu(sTruyVan);
if (data.Rows.Count > 0)
{
DTO_HoaDon hd = new DTO_HoaDon(data.Rows[0]);
sTruyVan = string.Format(@"DELETE FROM [CF_ChiTietHoaDon_Temp] WHERE IDHoaDon = {0} AND IDChiNhanh = '{1}'", hd.ID, hd.IDChiNhanh);
DataProvider.TruyVanKhongLayDuLieu(sTruyVan);
sTruyVan = string.Format(@"DELETE FROM [CF_HoaDon] WHERE ID = {0} AND [TrangThai] = 0 AND IDChiNhanh = '{1}'", hd.ID, hd.IDChiNhanh);
DataProvider.TruyVanKhongLayDuLieu(sTruyVan);
//sTruyVan = string.Format(@"DELETE FROM [CF_ChiTietGio] WHERE IDHoaDon = {0} AND [ThanhToan] = 0", hd.ID);
//DataProvider.TruyVanKhongLayDuLieu(sTruyVan);
}
}
public static bool CapNhatHoaDonChinh(int IDHoaDon, int IDBan, int IDNhanVien, double KhachThanhToan, double TienThua, double KhachCanTra, string HinhThucGiamGia, double GiamGia, double TyLeGiamGia, double TienGiamGia, string IDChiNhanh)
{
//lấy mã hóa đơn
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 CF_HoaDon
where MaHoaDon is not null AND DATEDIFF(dd,NgayBan, GetDate()) = 0 AND [IDChiNhanh] = '" + IDChiNhanh + "'";
string MaHoaDon = "";
DataTable data = new DataTable();
data = DataProvider.TruyVanLayDuLieu(CompuMaHoaDon);
if (data.Rows.Count > 0)
{
DataRow dr = data.Rows[0];
MaHoaDon = dr["Mã Hóa Đơn"].ToString();
}
string sTruyVan = string.Format(@"UPDATE [CF_HoaDon] SET MaHoaDon = '{10}' ,[TienGiamGia] = '{9}',[TyLeGiamGia] = '{8}',[GiamGia] = '{7}',[HinhThucGiamGia] = N'{6}',[KhachCanTra] = '{5}',[TrangThai] = 1, [GioRa] = getdate(), [IDNhanVien] = {0},[KhachThanhToan] = '{1}', [TienThua] = '{2}' WHERE [ID] = {3} AND [IDBan] = {4} AND [IDChiNhanh] = '" + IDChiNhanh + "'", IDNhanVien, KhachThanhToan, TienThua, IDHoaDon, IDBan, KhachCanTra, HinhThucGiamGia, GiamGia, TyLeGiamGia, TienGiamGia, MaHoaDon);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool XoaKhachDat(int IDBan, string IDChiNhanh)
{
string sTruyVan = string.Format(@"DELETE FROM [CF_DatBan] WHERE IDBan = {0} AND [IDChiNhanh] = '" + IDChiNhanh + "'", IDBan);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
/// <summary>
/// cập nhật hóa đơn
/// </summary>
/// <param name="IDHoaDon"></param>
/// <param name="IDBanMoi"></param>
/// <returns></returns>
public static bool CapNhatHoaDon(int IDHoaDon, int IDBanMoi)
{
string sTruyVan = string.Format(@"UPDATE [CF_HoaDon] SET [IDBan] = {0} WHERE ID = {1}", IDBanMoi, IDHoaDon);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
/// <summary>
/// cạp nhật idbanmoi
/// </summary>
/// <param name="idban"></param>
/// <returns></returns>
public static bool CapNhatIDBanMoi(int IDBanMoi, int ID, string IDChiNhanh)
{
string sTruyVan = string.Format(@"UPDATE [CF_ChiTietHoaDon_Temp] SET [IDBan] = '{0}' WHERE ID = {1} AND [IDChiNhanh] = '{2}'", IDBanMoi, ID, IDChiNhanh);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool XoaMonAn(string ID)
{
string sTruyVan = string.Format(@"DELETE FROM [CF_ChiTietHoaDon_Temp] WHERE ID = '{0}'", ID);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool CapNhatKetCa(int IDNhanVien)
{
string sTruyVan = string.Format(@" UPDATE [CF_HoaDon] SET [TrangThaiKetCa] = 1 WHERE IDNhanVien = {0} AND TrangThai = 1 AND TrangThaiKetCa = 0", IDNhanVien);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool CapNhatChiTietGio(int IDHoaDon, DateTime GioBatDau, int IDBan, int IDGio)
{
string sTruyVan = string.Format(@"UPDATE [CF_ChiTietGio] SET [GioBatDau] = '{0}' WHERE [IDHoaDon] = {1} AND [ID] = {2} AND [IDBan] = {3}", GioBatDau.ToString("yyyy-MM-dd H:mm:ss tt"), IDHoaDon, IDGio, IDBan);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool CapNhatChiTietGio(int IDHoaDon, int IDBan, DateTime GioKetThuc, string TongGioChoi, double ThanhTien, int IDGio)
{
string sTruyVan = string.Format(@"UPDATE CF_ChiTietGio SET [GioKetThuc] = '{0}' , [TongGioChoi] = N'{1}',[ThanhTien] = '{2}', [TrangThai] = 1 WHERE [IDHoaDon] ='{3}' AND [IDBan] = '{4}' AND [ID] = {5}", GioKetThuc.ToString("yyyy-MM-dd H:mm:ss tt"), TongGioChoi, ThanhTien, IDHoaDon, IDBan, IDGio);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool ThemKhachHang(int IDNhomKhachHang, string MaKhachHang, string TenKhachHang, DateTime NgaySinh, string CMND, string DiaChi, string DienThoai, string GhiChu)
{
string sTruyVan = string.Format(@"INSERT INTO GPM_KHACHHANG([IDNhomKhachHang],[MaKhachHang], [TenKhachHang], [NgaySinh], [CMND], [DiaChi], [DienThoai], [GhiChu]) VALUES ('{0}',N'{1}','{2}', '{3}','{4}','{5}','{6}','{7}')", IDNhomKhachHang, MaKhachHang, TenKhachHang, NgaySinh.ToString("yyyy-MM-dd hh:mm:ss"), CMND, DiaChi, DienThoai, GhiChu);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool XoaBanVeMatDinh(int idban)
{
string sTruyVan = string.Format(@"UPDATE [CF_Ban] SET [TrangThai] = 0 WHERE ID = {0}", idban);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool XoaHoaDonCu(int IDHoaDon, int IDBanCu)
{
string sTruyVan = string.Format(@"DELETE [CF_HoaDon] WHERE ID = {0} AND IDBan = {1}", IDHoaDon, IDBanCu);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool XoaKhachDat(int IDBan)
{
string sTruyVan = string.Format(@"DELETE FROM [CF_DatBan] WHERE IDBan = {0} ", IDBan);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool CapNhatChiTietHoaDon(int IDHoaDon, int SL, float ThanhTien, int IDHangHoa, int IDBan)
{
string sTruyVan = string.Format(@"UPDATE CF_ChiTietHoaDon SET [SoLuong] = SoLuong + {0}, [ThanhTien] = [ThanhTien] + {1} WHERE [IDHoaDon] = {2} AND [IDHangHoa] = {3} AND [IDBan] = {4}", SL, ThanhTien, IDHoaDon, IDHangHoa, IDBan);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool ThemChiTietHoaDon(object IDHoaDon, int IDHangHoa, int SL, float DonGia, float ThanhTien, int IDBan, string MaHangHoa, int IDDonViTinh, float PhuThuGio, float PhuThuKhuVuc, float GiaTong)
{
string sTruyVan = string.Format(@"INSERT INTO CF_ChiTietHoaDon(IDHoaDon,IDHangHoa,SoLuong,DonGia,ThanhTien,IDBan,MaHangHoa,IDDonViTinh,PhuThuGio,PhuThuKhuVuc,GiaTong) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", IDHoaDon, IDHangHoa, SL, DonGia, ThanhTien, IDBan, MaHangHoa, IDDonViTinh, PhuThuGio, PhuThuKhuVuc, GiaTong);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
//public static string GioRaCa(int IDNhanVien)
//{
// string sTruyVan = string.Format(@"SELECT GioRa FROM [CF_HoaDon] WHERE IDNhanVien = {0} AND TrangThai = 1 AND TrangThaiKetCa = 0 AND FORMAT(GioVao,'yyyy-MM-dd') = '{1}' ORDER BY GioRa DESC", IDNhanVien, NgayHomNay.ToString("yyyy-MM-dd"));
// DataTable data = new DataTable();
// data = DataProvider.TruyVanLayDuLieu(sTruyVan);
// if (data.Rows.Count > 0)
// {
// DataRow dr = data.Rows[0];
// return dr["GioRa"].ToString();
// }
// else
// return DateTime.Now.ToString("yyyy-MM-dd H:mm:ss");
//}
public static bool ThemKetCa(int IDNhanVien, string GioVao, double TongTienSauCa, string IDChiNhanh, double TongTien, double GiamGia)
{
string sTruyVan = string.Format(@"INSERT INTO CF_KetCa(ThoiGianBatDau,ThoiGianKetThuc,TongTienSauCa,IDNhanVien,IDChiNhanh,TongTien,GiamGia) VALUES ('{0}',getdate(),'{1}','{2}','{3}','{4}','{5}')", DateTime.Parse(GioVao).ToString("yyyy-MM-dd hh:mm:ss tt"), TongTienSauCa.ToString(), IDNhanVien, IDChiNhanh, TongTien.ToString(), GiamGia.ToString());
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool DoiTrangThaiBanCoNguoi(int idban)
{
string sTruyVan = string.Format(@"UPDATE [CF_Ban] SET [TrangThai] = 2 WHERE ID = {0}", idban);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool DoiMatKhau(string TenDangNhap, string MatKhau, string MatKhauMoi)
{
string sTruyVan = string.Format(@"UPDATE [CF_NguoiDung] SET [MatKhau] = N'{0}' WHERE [TenDangNhap] = N'{1}' AND [MatKhau] = N'{2}'", MatKhauMoi, TenDangNhap, MatKhau);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool ThemKhachDatBan(string TenKhachHang, string DienThoai, DateTime GioDat, int idban)
{
string sTruyVan = string.Format(@"INSERT INTO CF_DatBan(TenKhachHang,DienThoai,GioDat,IDBan) VALUES (N'{0}',N'{1}','{2}','{3}')", TenKhachHang, DienThoai, GioDat.ToString("yyyy-MM-dd hh:mm:ss tt"), idban);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool CapNhatSoLuong(int IDHoaDon, string ThanhTien, string SL, string MaHangHoa)
{
string sTruyVan = string.Format(@"UPDATE CF_ChiTietHoaDon SET [ThanhTien] = {0}, [SoLuong] = {1} WHERE [IDHoaDon] = {2} AND [MaHangHoa] = '{3}' ", ThanhTien, SL, IDHoaDon, MaHangHoa);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool ThemChiTietGio(object IDHoaDon, DateTime GioBatDau, float DonGia, int IDBan)
{
string sTruyVan = string.Format(@"INSERT INTO CF_ChiTietGio(IDHoaDon,GioBatDau,DonGia,IDBan) VALUES ('{0}','{1}','{2}','{3}')", IDHoaDon, GioBatDau.ToString("yyyy-MM-dd H:mm:ss tt"), DonGia, IDBan);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool TruTonKho(int IDNguyenLieu, string IDChiNhanh, double TrongLuong)
{
string sTruyVan = string.Format(@"UPDATE [CF_TonKho] SET [TrongLuong] = [TrongLuong] - {0} WHERE IDNguyenLieu = {1} AND [IDChiNhanh] = {2}", TrongLuong, IDNguyenLieu, IDChiNhanh);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool CapNhatTienGio(int IDHoaDon, double TienGio, int IDBan)
{
string sTruyVan = string.Format(@"UPDATE CF_HoaDon SET [TienGio] = [TienGio] + '{0}' WHERE [ID] ='{1}' AND [IDBan] = '{2}'", TienGio, IDHoaDon, IDBan);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool CapNhatMayInBill(string TenMayIn, string KhoGiay, string CongTy, string DiaChi, string SDT, string GiaoDienApDung, string IDChiNhanh)
{
string sTruyVan = string.Format(@"UPDATE [CF_ChiNhanh] SET [MayIn] = '{0}',[ReportBill] = '{1}',[TenChiNhanh] = N'{2}',[DiaChi] = N'{3}',[DienThoai] = '{4}',[GiaoDienApDung] = '{5}' WHERE ID = '" + IDChiNhanh + "'", TenMayIn, KhoGiay, CongTy, DiaChi, SDT, GiaoDienApDung);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool CapNhatSoLuong(string ThanhTien, string SL, string ID, string IDChiNhanh)
{
string sTruyVan = string.Format(@"UPDATE CF_ChiTietHoaDon_Temp SET [ThanhTien] = {0}, [SoLuong] = {1} WHERE [ID] = '{2}' AND [IDChiNhanh] = '" + IDChiNhanh + "' ", ThanhTien, SL, ID);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool CapNhatBillInTemp(string ID)
{
string sTruyVan = string.Format(@"UPDATE [CF_HoaDon] SET [LanIn] = [LanIn] + 1 WHERE ID = {0}", ID);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool CapNhatTongTien(int ID, string TongTien, string KhachCanTra, string TienGio)
{
string sTruyVan = string.Format(@"UPDATE CF_HoaDon SET [TongTien] = {0}, [KhachCanTra] = {1}, [TienGio] = {3} WHERE [ID] = {2} ", TongTien, KhachCanTra, ID, TienGio);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}
public static bool ThemLichSuTruyCap(int IDNhanVien, int IDNhom, string IDChiNhanh, string Menu, string HanhDong)
{
string sTruyVan = string.Format(@"INSERT INTO [CF_LichSuTruyCap](IDChiNhanh,IDNhom,IDNguoiDung,Menu,HanhDong,ThoiGian) VALUES ('{0}','{1}','{2}',N'{3}',N'{4}',getdate())", IDChiNhanh, IDNhom, IDNhanVien, Menu, HanhDong);
return(DataProvider.TruyVanKhongLayDuLieu(sTruyVan));
}