public static string InsertDonGoiMon(string ngay, string thoiGian, string ghiChu, string ban)
{
string maMoiNhat = new QuanLyQuanAnDbContext().Database.SqlQuery <string>("select top 1 MADON from DONGOIMON order by MADON desc").First();
string newMa = maMoiNhat.Remove(0, 2);
int newNum = Int32.Parse(newMa) + 1;
newMa = newNum + "";
int l = newMa.Length;
for (int i = 0; i < maMoiNhat.Length - 2 - l; i++)
{
newMa = "0" + newMa;
}
newMa = "GM" + newMa;
//insert
string insertCommand = "Sp_DONGOIMON_Insert @maDon='" + newMa + "', @ngay='" + ngay + "',@thoiGian='" + thoiGian + "',@ghiChu=N'" + ghiChu + "',@ban='" + ban + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(insertCommand);
if (res > 0)
{
return(newMa);
}
else
{
return("");
}
}
public static bool InsertCtDonGoiMon(string maDon, string maMatHang, string soLuong, string donGia)
{
string maMoiNhat = new QuanLyQuanAnDbContext().Database.SqlQuery <string>("Sp_CT_DONGOIMON_GetMaMoiNhat").First();
string newMa = maMoiNhat.Remove(0, 2);
int newNum = Int32.Parse(newMa) + 1;
newMa = newNum + "";
int l = newMa.Length;
for (int i = 0; i < maMoiNhat.Length - 2 - l; i++)
{
newMa = "0" + newMa;
}
newMa = "CT" + newMa;
//insert
string insertCommand = "Sp_CT_DONGOIMON_Insert @maCT='" + newMa + "', @maDon='" + maDon + "', @maMatHang='" + maMatHang + "', @soLuong=" + soLuong + ",@donGia=" + donGia;
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(insertCommand);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool InsertNhaCungCap(string tenNCC, string nguoiDaiDien, string sdt, string diaChi, string maLoaiMHCC, string moTa)
{
string maMoiNhat = new QuanLyQuanAnDbContext().Database.SqlQuery <string>("Sp_NHACUNGCAP_GetMaMoiNhat").First();
string newMa = maMoiNhat.Remove(0, 3);
int newNum = Int32.Parse(newMa) + 1;
newMa = newNum + "";
int l = newMa.Length;
for (int i = 0; i < maMoiNhat.Length - 3 - l; i++)
{
newMa = "0" + newMa;
}
newMa = "NCC" + newMa;
//insert
string insertCommand = "Sp_NHACUNGCAP_Insert @maNCC='" + newMa + "', @tenNCC=N'" + tenNCC + "', @nguoiDaiDien=N'" + nguoiDaiDien + "', @sdt='" + sdt + "', @diaChi=N'" + diaChi + "', @maLoaiMHCC='" + maLoaiMHCC + "', @moTa=N'" + moTa + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(insertCommand);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool InsertDonViTinhLuong(string tenDonViTinhLuong)
{
string maMoiNhat = new QuanLyQuanAnDbContext().Database.SqlQuery <string>("Sp_DONVITINHLUONG_GetMaMoiNhat").First();
string newMa = maMoiNhat.Remove(0, 3);
int newNum = Int32.Parse(newMa) + 1;
newMa = newNum + "";
int l = newMa.Length;
for (int i = 0; i < maMoiNhat.Length - 3 - l; i++)
{
newMa = "0" + newMa;
}
newMa = "DVT" + newMa;
//insert
string insertCommand = "Sp_DONVITINHLUONG_Insert @maDVT='" + newMa + "', @tenDVT=N'" + tenDonViTinhLuong + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(insertCommand);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Register(string hoten, string username, string pass)
{
string maMoiNhat = new QuanLyQuanAnDbContext().Database.SqlQuery <string>("Sp_KHACHHANG_GetMaKhachHangLast").First();
string newMa = maMoiNhat.Remove(0, 2);
int newNum = Int32.Parse(newMa) + 1;
newMa = newNum + "";
int l = newMa.Length;
for (int i = 0; i < maMoiNhat.Length - 2 - l; i++)
{
newMa = "0" + newMa;
}
newMa = "kh" + newMa;
string passHash = CalculateMD5Hash(username + "" + pass);
//insert
string insertCommand = "Sp_ACCOUNT_Insert @id='" + newMa + "' ,@hoten=N'" + hoten + "', @username='******',@pass='******'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(insertCommand);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool InsertKhachHang(string taiKhoan, string matKhau, string hoTen, string ngaySinh, string gioiTinh, string sdt, string diaChi)
{
string maMoiNhat = new QuanLyQuanAnDbContext().Database.SqlQuery <string>("Sp_KHACHHANG_GetMaKhachHangLast").First();
string newMa = maMoiNhat.Remove(0, 2);
int newNum = Int32.Parse(newMa) + 1;
newMa = newNum + "";
int l = newMa.Length;
for (int i = 0; i < maMoiNhat.Length - 2 - l; i++)
{
newMa = "0" + newMa;
}
newMa = "kh" + newMa;
//insert
string insertCommand = "Sp_KHACHHANG_Insert @maKhachHang='" + newMa + "', @taiKhoan='" + taiKhoan + "', @matKhau='" + matKhau + "', @hoTen=N'" + hoTen + "', @ngaySinh='" + ngaySinh + "', @gioiTinh=N'" + gioiTinh + "', @sdt='" + sdt + "', @diaChi=N'" + diaChi + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(insertCommand);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public string ChangePassword(string tendangnhap, string oldPass, string newPass)
{
//Get mk cu
string oldPasswordHash = CalculateMD5Hash(tendangnhap + "" + oldPass);
var ktMKCu = context.Database.SqlQuery <int>("select count(*) from ACCOUNT where TAIKHOAN='" + tendangnhap + "' and MATKHAU='" + oldPasswordHash + "'").First();
if (ktMKCu < 1)
{
return("Đổi mật khẩu thất bại! Mật khẩu cũ không chính xác!");
}
else
{
string newPassHash = CalculateMD5Hash(tendangnhap + "" + newPass);
if (oldPasswordHash == newPassHash)
{
return("Mật khẩu mới giống mật khẩu cũ!");
}
string command = "UPDATE ACCOUNT SET MATKHAU='" + newPassHash + "' where MATKHAU='" + oldPasswordHash + "' and TAIKHOAN='" + tendangnhap + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(command);
if (res > 0)
{
return("Đổi mật khẩu thành công!");
}
else
{
return("Đổi mật khẩu thất bại!");
}
}
}
public static bool HuyDonGoiMon(string maDon)
{
try //Tang so luong nuoc giai khat
{
string command = "Sp_CT_DONGOIMON_GetByMaDonGoiMon @MaDon='" + maDon + "'";
List <CT_DONGOIMON> listCT = new QuanLyQuanAnDbContext().Database.SqlQuery <CT_DONGOIMON>(command).ToList();
foreach (var item in listCT)
{
if (item.MAMATHANG.StartsWith("NC") && item.SOLUONG.HasValue)
{
NuocGiaiKhatModel.UpdateThemSoLuongNuocGiaiKhat(item.MAMATHANG, item.SOLUONG.Value + "");
}
}
}
catch { }
//
string cmd = "Sp_DONGOIMON_Delete @MaDon='" + maDon + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(cmd);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool ThemCTDonDatHang(string maDon, string maMatHang, string soLuong)
{
try
{
string maMoiNhat = new QuanLyQuanAnDbContext().Database.SqlQuery <string>("Sp_CT_DONDATHANG_GetMaMoiNhat").First();
string newMa = maMoiNhat.Remove(0, 2);
int newNum = Int32.Parse(newMa) + 1;
newMa = newNum + "";
int l = newMa.Length;
for (int i = 0; i < maMoiNhat.Length - 2 - l; i++)
{
newMa = "0" + newMa;
}
newMa = "CT" + newMa;
//new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand("Sp_DONDATHANG_Update_TinhTrang_GhiChuBan @maDon='{0}', @tinhTrang=3, @ghiChuNguoiBan='{2}'", sqlParams[0], sqlParams[1], sqlParams[2]);
string insertCommand = "Sp_CT_DONDATHANG_Insert @maCTDDH='" + newMa + "', @maDon='" + maDon + "', @maMH=" + maMatHang + ", @soLuong='" + soLuong + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(insertCommand);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
return(false);
}
}
public static bool InsertNhanVien(string taiKhoan, string matKhau, string hoTen, string ngaySinh, string gioiTinh, string sdt, string diaChi, string luong, string maDVL, string ngayKyHopDong)
{
string maMoiNhat = new QuanLyQuanAnDbContext().Database.SqlQuery <string>("Sp_NHANVIEN_GetMaNhanVienLast").First();
string newMa = maMoiNhat.Remove(0, 2);
int newNum = Int32.Parse(newMa) + 1;
newMa = newNum + "";
int l = newMa.Length;
for (int i = 0; i < maMoiNhat.Length - 2 - l; i++)
{
newMa = "0" + newMa;
}
newMa = "nv" + newMa;
//insert
string insertCommand = "Sp_NHANVIEN_Insert @maNhanVien='" + newMa + "', @taiKhoan='" + taiKhoan + "', @matKhau='" + matKhau + "', @hoTen=N'" + hoTen + "', @ngaySinh='" + ngaySinh + "', @gioiTinh=N'" + gioiTinh + "', @sdt='" + sdt + "', @diaChi=N'" + diaChi + "', @luong=" + luong + ", @maDVL='" + maDVL + "', @ngayKyHopDong='" + ngayKyHopDong + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(insertCommand);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public string ThemDonDathang(string ngay, string thoiGian, string maKH, string diaChiGiaoHang, string ghiChu, string tinhTrangDonHang, string ghiChuNguoiBan)
{
string maMoiNhat = new QuanLyQuanAnDbContext().Database.SqlQuery <string>("select top 1 MADON from DONDATHANG order by MADON desc").First();
string newMa = maMoiNhat.Remove(0, 2);
int newNum = Int32.Parse(newMa) + 1;
newMa = newNum + "";
int l = newMa.Length;
for (int i = 0; i < maMoiNhat.Length - 2 - l; i++)
{
newMa = "0" + newMa;
}
newMa = "DH" + newMa;
//insert
string insertCommand = "Sp_DONDATHANG_Insert @maDon='" + newMa + "', @ngay='" + ngay + "',@thoiGian='" + thoiGian + "',@maKH='"
+ maKH + "',@diaChiGiaoHang=N'" + diaChiGiaoHang + "',@ghiChu=N'" + ghiChu + "',@tinhTrangDonHang=" + tinhTrangDonHang + ",@ghiChuNguoiBan=N'" + ghiChuNguoiBan + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(insertCommand);
if (res > 0)
{
return(newMa);
}
else
{
return("");
}
}
public static NHANVIEN GetByMaNhanVienStt(string maNhanVien)
{
var nhanVien = new QuanLyQuanAnDbContext().Database.SqlQuery <NHANVIEN>("Sp_NHANVIEN_GetByMaNhanVien @maNhanVien='" + maNhanVien + "'").First();
//Lay thong tin
nhanVien.GetAllRefInfo();
return(nhanVien);
}
public List <KHACHHANG> GetAll()
{
var list = new QuanLyQuanAnDbContext().Database.SqlQuery <KHACHHANG>("select * from KHACHHANG order by MAKHACHHANG DESC").ToList();
foreach (var item in list)
{
item.GetThongTinAccount();
}
return(list);
}
public static List <MENUNUOCGIAIKHAT> ListCon()
{
var list = new QuanLyQuanAnDbContext().Database.SqlQuery <MENUNUOCGIAIKHAT>("Sp_MENUNUOCGIAIKHAT_ListCon").ToList();
//Lay thong tin khach hang
foreach (MENUNUOCGIAIKHAT menuNuocGiaiKhat in list)
{
menuNuocGiaiKhat.GetAllRefInfo();
}
return(list);
}
public static List <DONGOIMON> List3DonGoiMonDaLamGanNhat()
{
string ngay = DateTime.Now.ToString("yyyy/MM/dd");
var list = new QuanLyQuanAnDbContext().Database.SqlQuery <DONGOIMON>("Sp_DONGOIMON_List3DaLamGanNhat @ngay='" + ngay + "'").ToList();
//Lay thong tin khach hang
foreach (DONGOIMON donGoiMon in list)
{
donGoiMon.GetAllRefInfo();
}
return(list);
}
public static bool UpdateCTDonDatHang(string maPhieuNhap, string maMatHang, string maNhaCungCap, string ngayNhap, string soLuong, string donGia, string thanhTien, string hanSuDung)
{
//getSoLuongCu
string getcmd = "select SOLUONG from NHAPMATHANG where MAPHIEUNHAP='" + maPhieuNhap + "'";
int soLuongOld = new QuanLyQuanAnDbContext().Database.SqlQuery <int>(getcmd).First();
//update
string updateCommand = "Sp_NHAPMATHANG_Update @MaPhieuNhap='" + maPhieuNhap + "',@MaMatHang='" + maMatHang + "',@MaNhaCungCap='" + maNhaCungCap + "',@NgayNhap='" + ngayNhap
+ "',@SoLuong=" + soLuong + ",@DonGia=" + donGia + ",@ThanhTien=" + thanhTien + ",@HanSuDung='" + hanSuDung + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(updateCommand);
if (res > 0)
{
try //sua so luong nuoc giai khat
{
if (maMatHang.StartsWith("NC"))
{
DateTime dateTime = DateTime.UtcNow.Date;
string dayString = dateTime.Year + "-";
if (dateTime.Month.ToString().Length == 1)
{
dayString += "0" + dateTime.Month;
}
else
{
dayString += dateTime.Month;
}
if (dateTime.Day.ToString().Length == 1)
{
dayString += "-0" + dateTime.Day;
}
else
{
dayString += "-" + dateTime.Day;
}
if (dayString == ngayNhap)
{
string addCommand = "Sp_NUOCGIAIKHAT_AddCount @maNuocGiaiKhat='" + maMatHang + "',@soLuongThem=" + (Int32.Parse(soLuong) - soLuongOld);
var res2 = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(addCommand);
}
}
}
catch { }
return(true);
}
else
{
return(false);
}
}
public static bool SuaTrangThaiDonGoiMon(string maDon, string trangThai)
{
string cmd = "Sp_DONGOIMON_UpdateTrangThai @MaDon='" + maDon + "',@trangThai=" + trangThai;
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(cmd);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateDiaChiNhanHang(string userID, string diaChiNhanHang)
{
string command = "Sp_KHACHHANG_Update @maKH='" + userID + "', @diaChiNhanHang=N'" + diaChiNhanHang + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(command);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateInfo(string userID, string hoTen, string gioiTinh, string ngaySinh, string sdt, string taiKhoan, string diaChi)
{
string command = "Sp_ACCOUNT_Update @userID= '" + userID + "' , @hoTen=N'" + hoTen + "', @gioiTinh=N'" + gioiTinh + "', @ngaySinh='" + ngaySinh + "', @sdt ='" + sdt + "', @taiKhoan ='" + taiKhoan + "' , @diaChi =N'" + diaChi + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(command);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateTaiKhoan(string maKhachHang, string taiKhoan, string matKhau)
{
string command = "Sp_ACCOUNT_UpdateTK @id='" + maKhachHang + "', @taiKhoan='" + taiKhoan + "', @matKhau='" + matKhau + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(command);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool DeleteNhanVien(string maNhanVien)
{
string Command = "Sp_NHANVIEN_Delete @maNhanVien='" + maNhanVien + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(Command);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool DeleteKhachHang(string maKhachHang)
{
string Command = "Sp_KHACHHANG_Delete @maKhachHang='" + maKhachHang + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(Command);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool DeleteDonViTinh(string maDonViTinh)
{
//delete
string command = "Sp_DONVITINH_Delete @maDVT='" + maDonViTinh + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(command);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool UpdateThamSo(string maThamSo, string giaTri, string tinhTrang)
{
//update
string command = "Sp_THAMSO_Update @maThamSo='" + maThamSo + "', @giaTri=" + giaTri + ",@tinhTrang=" + tinhTrang;
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(command);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool UpdateDonViTinhLuong(string maDonViTinhLuong, string tenDonViTinhLuong)
{
//update
string command = "Sp_DONVITINHLUONG_Update @maDVT='" + maDonViTinhLuong + "', @tenDVT=N'" + tenDonViTinhLuong + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(command);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool UpdateNhanVien(string maNhanVien, string hoTen, string ngaySinh, string gioiTinh, string sdt, string diaChi, string luong, string maDVL, string ngayKyHopDong)
{
//insert
//new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand("set dateformat dmy");
string command = "Sp_NHANVIEN_Update @maNhanVien='" + maNhanVien + "', @hoTen=N'" + hoTen + "', @ngaySinh='" + ngaySinh + "', @gioiTinh=N'" + gioiTinh + "', @sdt='" + sdt + "', @diaChi=N'" + diaChi + "', @luong=" + luong + ", @maDVL='" + maDVL + "', @ngayKyHopDong='" + ngayKyHopDong + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(command);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool DeleteMatHangMonAn(string maMatHangMonAn)
{
try
{
try // xoa tu bang menumonan
{
new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand("Sp_MENUMONAN_Delete @MaMonAn='" + maMatHangMonAn + "'");
}
catch { }
try //xoa tu bang ct mon an
{
new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand("Sp_CT_MONAN_Delete @MaMonAn='" + maMatHangMonAn + "'");
}
catch { }
string deleteCommand = "Sp_MONAN_Delete @maMatHang='" + maMatHangMonAn + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(deleteCommand);
if (res > 0)
{
string deleteCommand2 = "Sp_MATHANG_Delete @maMatHang='" + maMatHangMonAn + "'";
try
{
var res2 = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(deleteCommand2);
if (res2 > 0)
{
return(true);
}
else
{
return(false);
}
}
catch
{
//new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand("insert into MONAN values ('" + maMatHangMonAn + "')");
return(false);
}
}
else
{
return(false);
}
}
catch (Exception e)
{
return(false);
}
}
public string ChinhSuaSoLuongOfGioHang(string maMatHang, string soLuong, string taiKhoan)
{
string userID = new AccountModel().GetID(taiKhoan);
//UPDATE
string cmd = "Sp_CT_GIOHANG_Update @maKH='" + userID + "', @maMH='" + maMatHang + "',@soLuong=" + soLuong;
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(cmd);
if (res > 0)
{
return("Sua thanh cong");
}
else
{
return("Sua that bai");
}
}
public string XoaMatHangKhoiGioHang(string maMH, string taiKhoan)
{
string userID = new AccountModel().GetID(taiKhoan);
//UPDATE
string cmd = "Sp_CT_GIOHANG_Delete @maKH='" + userID + "', @maMH='" + maMH + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(cmd);
if (res > 0)
{
return("Xoa thanh cong");
}
else
{
return("Xoa that bai");
}
}
public static bool DeleteDungCu_NhaCungCap(string maDungCu, string maNhaCungCap)
{
try
{
string deleteCommand = "Sp_MATHANGNHAP_NHACUNGCAP_Delete @maMatHang='" + maDungCu + "', @maNhaCungCap='" + maNhaCungCap + "'";
var res = new QuanLyQuanAnDbContext().Database.ExecuteSqlCommand(deleteCommand);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
return(false);
}
}