// Xóa nhân viên
public static bool XoaNhanVien(string maNV, string tenTK)
{
string query = "EXEC usp_XoaNhanVien @MANV, @TENTK";
// Truyền tham số
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@MANV", SqlDbType.Char)
{
IsNullable = false, Value = maNV
},
new SqlParameter("@TENTK", SqlDbType.VarChar)
{
IsNullable = false, Value = tenTK
},
};
try
{
Dataprovider.ExcuteQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
return(false);
}
return(true);
}
// Load thông tin nhân viên
public static DataTable LoadNhanVien()
{
// string query = "SELECT * FROM NHANVIEN WHERE TENTK IS NOT NULL";
string query = "EXEC usp_LoadNhanVien";
return(Dataprovider.ExcuteQuery(query));
}
public static List <NguyenLieuMADataSource> XemNguyenLieuMonAn(string maMonAn)
{
string query = "EXEC uspXemChiTietMonAn @maMonAn";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@maMonAn", SqlDbType.NVarChar)
{
IsNullable = false, Value = maMonAn
},
};
List <NguyenLieuMADataSource> list = null;
try
{
DataTable table = Dataprovider.ExcuteQuery(query, parameters.ToArray());
list = table.AsEnumerable().ToList().ConvertAll(x =>
new NguyenLieuMADataSource()
{
Ma = x[0].ToString(),
Ten = x[1].ToString(),
DonViTinh = x[2].ToString(),
Gia = x[3].ToString(),
SoLuong = x[4].ToString(),
});
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(list);
}
public static DataTable XemNhaCungCap(string tenNhaCungCap, int pageNumber, int pageSize)
{
string query = "EXEC usp_LoadNCC @tenNhaCungCap,@pageNumber,@pageSize";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@tenNhaCungCap", SqlDbType.NVarChar)
{
IsNullable = false, Value = tenNhaCungCap
},
new SqlParameter("@pageNumber", SqlDbType.Int)
{
IsNullable = false, Value = pageNumber
},
new SqlParameter("@pageSize", SqlDbType.Int)
{
IsNullable = false, Value = pageSize
},
};
DataTable data = new DataTable();
try
{
data = Dataprovider.ExcuteQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(data);
}
public static DataTable XemLuongTong(String maNV, String thangLuong)
{
string query = "EXECUTE usp_XemLuong @maNV ,@thangLuong";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@maNV", SqlDbType.NVarChar)
{
IsNullable = true, Value = maNV ?? (Object)DBNull.Value
},
new SqlParameter("@thangLuong", SqlDbType.NVarChar)
{
IsNullable = true, Value = thangLuong ?? (Object)DBNull.Value
}
};
DataTable table = null;
try
{
table = Dataprovider.ExcuteQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(table);
}
/// <summary>
/// Xem danh sách nguyên liệu
/// </summary>
/// <param name="maNhaCungCap">mã nhà cung cấp</param>
/// <param name="loaiNL">loại nguyên liệu</param>
/// <param name="isXemToiThieu">nếu true thì ta xem các nguyên liệu đạt ngưỡng tối thiểu</param>
/// <returns></returns>
public static DataTable XemNguyenLieu(string tenNguyenLieu, string maNhaCungCap, bool isXemToiThieu, int pageNumber, int pageSize)
{
string query = "EXEC uspXemNguyenLieu @tenNguyenLieu,@maNhaCungCap,@isXemToiThieu,@pageNumber,@pageSize";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@tenNguyenLieu", SqlDbType.NVarChar)
{
IsNullable = true, Value = tenNguyenLieu
},
new SqlParameter("@maNhaCungCap", SqlDbType.VarChar)
{
IsNullable = true, Value = maNhaCungCap
},
new SqlParameter("@isXemToiThieu", SqlDbType.Bit)
{
IsNullable = true, Value = isXemToiThieu
},
new SqlParameter("@pageNumber", SqlDbType.Int)
{
IsNullable = true, Value = pageNumber
},
new SqlParameter("@pageSize", SqlDbType.Int)
{
IsNullable = true, Value = pageSize
},
};
return(Dataprovider.ExcuteQuery(query, parameters.ToArray()));
}
public static DataTable DemNguyenLieu(string tenNguyenLieu, string maNhaCungCap, bool isXemToiThieu)
{
string query = "EXEC uspDemNguyenLieu @tenNguyenLieu,@maNhaCungCap,@isXemToiThieu";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@tenNguyenLieu", SqlDbType.NVarChar)
{
IsNullable = true, Value = tenNguyenLieu
},
new SqlParameter("@maNhaCungCap", SqlDbType.VarChar)
{
IsNullable = true, Value = maNhaCungCap
},
new SqlParameter("@isXemToiThieu", SqlDbType.Bit)
{
IsNullable = true, Value = isXemToiThieu
},
};
DataTable table = null;
try
{
table = Dataprovider.ExcuteQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(table);
}
public static List <PhieuNhapHangDataSource> XemChiTietPhieuNhapNguyenlieu(string soPhieuDat)
{
string query = "EXEC uspXemChiTietPhieuNhapNguyenLieu @soPhieuDat";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@soPhieuDat", SqlDbType.VarChar)
{
IsNullable = false, Value = soPhieuDat
},
};
List <PhieuNhapHangDataSource> list = null;
try
{
list = Dataprovider.ExcuteQuery(query, parameters.ToArray()).AsEnumerable().ToList().ConvertAll(x =>
new PhieuNhapHangDataSource()
{
Ma = x[1].ToString(),
Ten = x[2].ToString(),
Loai = x[3].ToString(),
DonViTinh = x[4].ToString(),
Gia = x[5].ToString(),
Soluong = x[6].ToString(),
Thanhtien = x[7].ToString(),
});
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(list);
}
public static List <MonAn> XemMonAn(string tenMonAn, int loaiMonAn, int pageNumber, int pageSize, uint donGia = 0)
{
string query = "EXEC uspXemMonAn @tenMonAn,@loaiMonAn,@donGia,@pageNumber,@pageSize";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@tenMonAn", SqlDbType.NVarChar)
{
IsNullable = false, Value = tenMonAn
},
new SqlParameter("@loaiMonAn", SqlDbType.Int)
{
IsNullable = false, Value = loaiMonAn
},
new SqlParameter("@donGia", SqlDbType.Decimal)
{
IsNullable = false, Value = donGia
},
new SqlParameter("@pageNumber", SqlDbType.Int)
{
IsNullable = false, Value = pageNumber
},
new SqlParameter("@pageSize", SqlDbType.Int)
{
IsNullable = false, Value = pageSize
},
};
List <MonAn> list = new List <MonAn>();
try
{
DataTable table = Dataprovider.ExcuteQuery(query, parameters.ToArray());
list = table.AsEnumerable().ToList().ConvertAll(x =>
new MonAn()
{
Ma = x[0].ToString(),
Ten = x[1].ToString(),
Loai = x[2].ToString(),
TenHinhAnh = x[3].ToString(),
Gia = uint.Parse(x[4].ToString())
});
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(list);
}
public static DataTable LayThangHoaDon()
{
DataTable data = new DataTable();
string query = "EXEC usp_LayThangHoaDon";
try
{
data = Dataprovider.ExcuteQuery(query);
}catch (Exception ex)
{
Utility.Log(ex);
}
return(data);
}
// Cập nhật thông tin nhân viên
public static bool CapNhatNhanVien(NhanVien nv, string maCV, string tenTK)
{
string query = "EXEC usp_CapNhatNhanVien @MANV, @TENTK, @MACV, @TENNV, @SDT, @DIACHI";
string maNV = nv.MaNV;
string tenNV = nv.HoTen;
string sdt = nv.SoDienThoai;
string diaChi = nv.DiaChi;
// Truyền tham số
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@MANV", SqlDbType.Char)
{
IsNullable = false, Value = maNV
},
new SqlParameter("@TENTK", SqlDbType.VarChar)
{
IsNullable = false, Value = tenTK
},
new SqlParameter("@MACV", SqlDbType.Char)
{
IsNullable = false, Value = maCV
},
new SqlParameter("@TENNV", SqlDbType.NVarChar)
{
IsNullable = false, Value = tenNV
},
new SqlParameter("@SDT", SqlDbType.NVarChar)
{
IsNullable = false, Value = sdt
},
new SqlParameter("@DIACHI", SqlDbType.NVarChar)
{
IsNullable = false, Value = diaChi
},
};
try
{
Dataprovider.ExcuteQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
return(false);
}
return(true);
}
public static DataTable LoadLamViec()
{
DataTable data = new DataTable();
string query = "EXEC usp_LoadCaTruc";
try
{
data = Dataprovider.ExcuteQuery(query);
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(data);
}
public static List <MonAnDataSource> XemMonAnDataSource(string tenMonAn, int loaiMonAn, int pageNumber, int pageSize)
{
string query = "EXEC uspXemMonAn2 @tenMonAn,@loaiMonAn,@pageNumber,@pageSize";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@tenMonAn", SqlDbType.NVarChar)
{
IsNullable = false, Value = tenMonAn
},
new SqlParameter("@loaiMonAn", SqlDbType.Int)
{
IsNullable = false, Value = loaiMonAn
},
new SqlParameter("@pageNumber", SqlDbType.Int)
{
IsNullable = false, Value = pageNumber
},
new SqlParameter("@pageSize", SqlDbType.Int)
{
IsNullable = false, Value = pageSize
},
};
List <MonAnDataSource> list = new List <MonAnDataSource>();
try
{
DataTable table = Dataprovider.ExcuteQuery(query, parameters.ToArray());
list = table.AsEnumerable().ToList().ConvertAll(x =>
new MonAnDataSource()
{
Ma = x[0].ToString(),
Ten = x[1].ToString(),
MaLoaiHangHoa = x[2].ToString(),
Gia = x[3].ToString(),
AnhMinhHoa = x[4].ToString(),
TenLoaiHangHoa = x[5].ToString(),
});
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(list);
}
public static List <PhieuNhapHang> XemPhieuNhapHang(int loai, int pageNumber, int pageSize)
{
string query = "EXEC uspXemPhieuNhapNguyenlieu @loai,@pageNumber,@pageSize";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@loai", SqlDbType.Int)
{
IsNullable = false, Value = loai
},
new SqlParameter("@pageNumber", SqlDbType.Int)
{
IsNullable = false, Value = pageNumber
},
new SqlParameter("@pageSize", SqlDbType.Int)
{
IsNullable = false, Value = pageSize
}
};
DataTable table = Dataprovider.ExcuteQuery(query, parameters.ToArray());
List <PhieuNhapHang> list = null;
try
{
list = table.AsEnumerable().ToList().ConvertAll(x =>
new PhieuNhapHang()
{
SoPhieu = x[0].ToString(),
MaNhanVien = x[1].ToString(),
NgayDat = x[2].ToString(),
NgayGiao = x[3].ToString(),
ThanhTien = uint.Parse(x[4].ToString()),
NguoiGiao = x[5].ToString(),
TenNhanVien = x[6].ToString(),
SoDienThoai = x[7].ToString(),
});
}
catch (Exception ex)
{
Utility.Log(ex);
}
//Chuyển Table thành List tên hành khách
return(list);
}
// Tra cứu thông tin nhân viên
public static DataTable TraCuuNhanVien(NhanVien nv, string maCV, string tenTK)
{
string query = "EXEC usp_TraCuuNhanVien @MANV, @TENTK, @MACV, @TENNV, @SDT, @DIACHI";
string maNV = nv.MaNV;
string tenNV = nv.HoTen;
string sdt = nv.SoDienThoai;
string diaChi = nv.DiaChi;
// Truyền tham số
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@MANV", SqlDbType.Char)
{
IsNullable = true, Value = maNV
},
new SqlParameter("@TENTK", SqlDbType.VarChar)
{
IsNullable = true, Value = tenTK
},
new SqlParameter("@MACV", SqlDbType.Char)
{
IsNullable = true, Value = maCV
},
new SqlParameter("@TENNV", SqlDbType.NVarChar)
{
IsNullable = true, Value = tenNV
},
new SqlParameter("@SDT", SqlDbType.NVarChar)
{
IsNullable = true, Value = sdt
},
new SqlParameter("@DIACHI", SqlDbType.NVarChar)
{
IsNullable = true, Value = diaChi
},
};
return(Dataprovider.ExcuteQuery(query, parameters.ToArray()));
}
public static List <LoaiMon> XemLoaiMon()
{
string query = "SELECT * FROM LOAIMONAN";
List <LoaiMon> list = null;
try
{
DataTable table = Dataprovider.ExcuteQuery(query);
list = table.AsEnumerable().ToList().ConvertAll(x =>
new LoaiMon()
{
Ma = x[0].ToString(),
Ten = x[1].ToString()
});
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(list);
}
public static List <NhaCungCap> XemNhaCungCap()
{
string query = "SELECT * FROM NHACUNGCAP";
DataTable table = Dataprovider.ExcuteQuery(query);
List <NhaCungCap> list = null;
try
{
list = table.AsEnumerable().ToList().ConvertAll(x =>
new NhaCungCap()
{
Ten = x[1].ToString(), SDT = x[2].ToString(), DiaChi = x[3].ToString(), MaNCC = x[0].ToString()
});
}
catch (Exception ex)
{
Utility.Log(ex);
}
//Chuyển Table thành List tên hành khách
return(list);
}
public static DataTable XemChiTietPhieuNhapHangTable(string soPhieuDat)
{
string query = "EXEC uspXemChiTietPhieuNhapNguyenLieu @soPhieuDat";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@soPhieuDat", SqlDbType.VarChar)
{
IsNullable = false, Value = soPhieuDat
},
};
DataTable table = null;
try
{
table = Dataprovider.ExcuteQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(table);
}
public static DataTable LoadDoanhThu(String thangHD)
{
DataTable data = new DataTable();
string query = "EXEC dbo.usp_LoadDoanhThu @thangHD";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@thangHD", SqlDbType.VarChar)
{
IsNullable = true, Value = thangHD ?? (Object)DBNull.Value
},
};
try
{
data = Dataprovider.ExcuteQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(data);
}
public static PhieuNhapHang LapPhieuNhap(string nhaCungCap, string maNhanVien)
{
string query = "EXEC uspLapPhieuNhapNguyenLieu @nhaCungCap,@maNhanVien";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@nhaCungCap", SqlDbType.VarChar)
{
IsNullable = false, Value = nhaCungCap
},
new SqlParameter("@maNhanVien", SqlDbType.VarChar)
{
IsNullable = false, Value = maNhanVien
},
};
DataRow result = null;
PhieuNhapHang phieuNhapHang = null;
try
{
result = Dataprovider.ExcuteQuery(query, parameters.ToArray()).Rows[0];
phieuNhapHang = new PhieuNhapHang()
{
SoPhieu = result[0].ToString(),
MaNhanVien = result[1].ToString(),
TenNhanVien = result[2].ToString(),
SoDienThoai = result[3].ToString(),
NgayDat = result[4].ToString(),
};
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(phieuNhapHang);
}
public static DataTable LoadPhanCong()
{
string query = "EXEC usp_LoadPhanCong";
return(Dataprovider.ExcuteQuery(query));
}
public static DataTable LayMaNV()
{
string query = "SELECT DISTINCT MANV,TENNV FROM NHANVIEN";
return(Dataprovider.ExcuteQuery(query));
}
public static DataTable LayMaCa()
{
string query = "SELECT DISTINCT MACA,TENCA FROM CALAMVIEC";
return(Dataprovider.ExcuteQuery(query));
}
// Load mã chức vụ
public static DataTable LoadMaChucVu()
{
string query = "SELECT DISTINCT MACV,TENCV FROM CHUCVU";
return(Dataprovider.ExcuteQuery(query));
}
public static DataTable LoadNhanVienPhanCong()
{
string query = "SELECT DISTINCT dbo.PHANCONG.MANV,TENNV FROM dbo.NHANVIEN JOIN dbo.PHANCONG ON PHANCONG.MANV = NHANVIEN.MANV";
return(Dataprovider.ExcuteQuery(query));
}
public static DataTable LoadThangLuongDesc()
{
string query = "SELECT DISTINCT THANGLUONG FROM dbo.LUONG ORDER BY THANGLUONG DESC";
return(Dataprovider.ExcuteQuery(query));
}
public static DataTable SelectAll(String query)
{
return(Dataprovider.ExcuteQuery(query));
}