public DataTable LayBangKhachHang()
{
string query = "select * from KhachHang where TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayNVTheoMaNV(string MaNV)
{
string query = "select * from NhanVien where TrangThai = 1 and MaNhanVien='" + MaNV + "'";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangKhachHang(string strMaKH)
{
string query = string.Format("select * from KhachHang where TrangThai=1 and MaKhachHang='{0}'", strMaKH);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangNhaCungCap()
{
string query = "select * from NhaCungCap where TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangHangSanXuat()
{
string query = "select * from HangSanXuat where TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayThongTinNVTheoMa(string strMaNV)
{
string query = string.Format("select * from NhanVien where MaNhanVien='{0}' and TrangThai=1", strMaNV);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangSanPham(string strMaSP)
{
string query = string.Format("select * from SanPham sp,HangSanXuat hsx,LoaiSanPham lsp where sp.TrangThai=1 and sp.MaHangSanXuat = hsx.MaHangSanXuat and sp.MaLoaiSanPham = lsp.MaLoaiSanPham and sp.MaSanPham='{0}'", strMaSP);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangSanPham()
{
string query = "select * from SanPham sp,HangSanXuat hsx,LoaiSanPham lsp where sp.TrangThai=1 and sp.MaHangSanXuat = hsx.MaHangSanXuat and sp.MaLoaiSanPham = lsp.MaLoaiSanPham";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangChucVu()
{
string query = "select Quyen from NhanVien where TrangThai = 1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayChiTietPhieu(string strMaPhieu)
{
string query = string.Format("select * from ChiTietPhieuXuat ct,SanPham sp where ct.MaSanPham = sp.MaSanPham and ct.MaPhieuXuat = '{0}'", strMaPhieu);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangLoaiSanPham()
{
string query = "select * from LoaiSanPham where TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangPhieuNhap()
{
string query = "select * from PhieuNhap pn, NhanVien nv, NhaCungCap ncc where pn.NhanVienLap = nv.MaNhanVien and pn.MaNhaCungCap = ncc.MaNhaCungCap and pn.TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangNhaCungCapTheoMa(string MaNCC)
{
string query = string.Format("select * from NhaCungCap where TrangThai=1 and MaNhaCungCap='{0}'", MaNCC);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangNCCPhieuNhap(string strMaPhieu)
{
return(ThaoTacDuLieu.LayBang(string.Format(@"select *
from ChiTietPhieuNhap ct, PhieuNhap pn, NhaCungCap ncc
where ct.MaPhieuNhap = pn.MaPhieuNhap and pn.MaNhaCungCap = ncc.MaNhaCungCap
and pn.MaPhieuNhap = '{0}'", strMaPhieu)));
}
public DataTable LayThongTinNV(string strTenDN, string strMatKhau)
{
string query = string.Format("select * from NhanVien where TenDangNhap='{0}' and MatKhau='{1}' and TrangThai=1", strTenDN, strMatKhau);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangPhieuXuat()
{
string query = "select * from PhieuXuat px, NhanVien nv, KhachHang kh where px.NhanVienLap = nv.MaNhanVien and px.MaKhachHang = kh.MaKhachHang and px.TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangKhachHangPX(string strMaPhieu)
{
return(ThaoTacDuLieu.LayBang(string.Format(@"select *
from ChiTietPhieuXuat ct, PhieuXuat px, KhachHang kh
where ct.MaPhieuXuat = px.MaPhieuXuat and px.MaKhachHang = kh.MaKhachHang
and px.MaPhieuXuat = '{0}'", strMaPhieu)));
}
public DataTable TinhLoiNhuan()
{
return(ThaoTacDuLieu.LayBang(string.Format(@"select px.NgayLap, sum(px.TongTien) as 'DoanhThu', sum(sp.GiaMua * ctpx.SoLuong) as 'TriGia'
from PhieuXuat px, ChiTietPhieuXuat ctpx, SanPham sp
where px.MaPhieuXuat = ctpx.MaPhieuXuat
and ctpx.MaSanPham = sp.MaSanPham
group by px.NgayLap
")));
}
public DataTable LayBangChiTietKhoBH()
{
return(ThaoTacDuLieu.LayBang(@"select *
from ChiTietBaoHanh ct, BaoHanh bh, Serial sr, PhieuNhap pn,SanPham sp, NhaCungCap ncc
where ct.MaBaoHanh = bh.MaBaoHanh
and ct.MaSerial = sr.MaSerial
and sr.MaPhieuNhap = pn.MaPhieuNhap
and sr.MaSanPham = sp.MaSanPham
and pn.MaNhaCungCap = ncc.MaNhaCungCap
and (ct.TinhTrang = 0 or ct.TinhTrang = 2 or ct.TinhTrang = 3)"));
}
public DataTable LayBangSerial()
{
string query = @"select *
from Serial as sr
left join PhieuXuat as px on px.MaPhieuXuat = sr.MaPhieuXuat
left join PhieuNhap as pn on pn.MaPhieuNhap = sr.MaPhieuNhap
left join SanPham as sp on sp.MaSanPham = sr.MaSanPham
and sr.TrangThai=1";
return(ThaoTacDuLieu.LayBang(query));
}
public DataTable TinhThongKe()
{
DataTable dtThongKeXuat = ThaoTacDuLieu.LayBang(@"select NgayLap, sum(TongTien) as 'BanHang'
from PhieuXuat
where Loai = 1
group by NgayLap");
DataTable dtThongKeNhap = ThaoTacDuLieu.LayBang(@"select NgayLap, sum(TongTien) as 'NhapHang'
from PhieuNhap
group by NgayLap");
DataTable dtDoanhThu = new DataTable();
dtDoanhThu.Columns.Add("NgayLap", typeof(DateTime));
dtDoanhThu.Columns.Add("BanHang", typeof(decimal));
dtDoanhThu.Columns.Add("NhapHang", typeof(decimal));
foreach (DataRow drXuat in dtThongKeXuat.Rows)
{
DateTime tNgayLap = Convert.ToDateTime(drXuat["NgayLap"]);
decimal dBanHang = Convert.ToDecimal(drXuat["BanHang"]);
dtDoanhThu.Rows.Add(tNgayLap, dBanHang, 0);
}
foreach (DataRow drNhap in dtThongKeNhap.Rows)
{
bool bThem = true;
DateTime tNgayLap = Convert.ToDateTime(drNhap["NgayLap"]);
decimal dNhapHang = Convert.ToDecimal(drNhap["NhapHang"]);
foreach (DataRow drDoanhThu in dtDoanhThu.Rows)
{
if (tNgayLap == Convert.ToDateTime(drDoanhThu["NgayLap"]))
{
drDoanhThu["NhapHang"] = dNhapHang;
bThem = false;
break;
}
}
if (bThem)
{
dtDoanhThu.Rows.Add(tNgayLap, 0, dNhapHang);
}
}
return(dtDoanhThu);
}
public DataTable KiemTraTen(string strTen)
{
return(ThaoTacDuLieu.LayBang(string.Format("select * from HangSanXuat where TenHangSanXuat='{0}'", strTen)));
}
//public int LaySoLuong()
//{
// SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
// string sql = "SELECT COUNT(DISTINCT MACC) FROM CHAMCONG";
// SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql,conn);
// int kq = (int)cmd.ExecuteScalar();
// ThaoTacDuLieu.DongKetNoi(conn);
// return kq;
//}
//public bool ThemBangChamCong(int SoNgay, List<clsChamCong_DTO> lsChamCong)
//{
// SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
// string MaCC = "CC" + (LaySoLuong() + 1).ToString();
// if (lsChamCong.Count > 0)
// {
// for (int i = 0; i < lsChamCong.Count; i++)
// {
// string sql = "INSERT INTO CHAMCONG(MACC,MANV,THANG,NAM";
// for (int j = 1; j <= SoNgay; j++)
// {
// sql += string.Format(",D{0}", j.ToString());
// }
// sql += ",SONGAY)";
// sql += string.Format("VALUES('{0}','{1}',{2},{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}'", MaCC, lsChamCong[i].MaNV, lsChamCong[i].Thang, lsChamCong[i].Nam, lsChamCong[i].D1, lsChamCong[i].D2, lsChamCong[i].D3, lsChamCong[i].D4, lsChamCong[i].D5, lsChamCong[i].D6, lsChamCong[i].D7, lsChamCong[i].D8, lsChamCong[i].D9, lsChamCong[i].D10, lsChamCong[i].D11, lsChamCong[i].D12, lsChamCong[i].D13, lsChamCong[i].D14, lsChamCong[i].D15, lsChamCong[i].D16, lsChamCong[i].D17, lsChamCong[i].D18, lsChamCong[i].D19, lsChamCong[i].D20, lsChamCong[i].D21, lsChamCong[i].D22, lsChamCong[i].D23, lsChamCong[i].D24, lsChamCong[i].D25, lsChamCong[i].D26, lsChamCong[i].D27,lsChamCong[i].D28);
// if(SoNgay == 29)
// {
// sql += string.Format(",'{0}'",lsChamCong[i].D29);
// }
// else if(SoNgay == 30)
// {
// sql += string.Format(",'{0}','{1}'", lsChamCong[i].D29,lsChamCong[i].D30);
// }
// else if (SoNgay == 31)
// {
// sql += string.Format(",'{0}','{1}','{2}'", lsChamCong[i].D29, lsChamCong[i].D30,lsChamCong[i].D30,lsChamCong[i].D31);
// }
// sql += string.Format(",{0})", SoNgay);
// SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
// int kq = (int)cmd.ExecuteNonQuery();
// if (kq <= 0)
// {
// ThaoTacDuLieu.DongKetNoi(conn);
// return false;
// }
// }
// }
// else
// {
// ThaoTacDuLieu.DongKetNoi(conn);
// return false;
// }
// ThaoTacDuLieu.DongKetNoi(conn);
// return true;
//}
//public List<clsChamCong_DTO> LayTenBangChamCong()
//{
// SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
// string sql = "SELECT MACC,THANG,NAM,N'Bảng chấm công tháng ' + Convert(varchar(2),THANG) + N' năm ' +Convert(varchar(5),NAM) AS TENBANG FROM CHAMCONG GROUP BY MACC,THANG,NAM";
// SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
// SqlDataReader dr = cmd.ExecuteReader();
// List<clsChamCong_DTO> lsChamCong = new List<clsChamCong_DTO>();
// while(dr.Read())
// {
// clsChamCong_DTO ChamCong = new clsChamCong_DTO();
// if (!dr.IsDBNull(0))
// ChamCong.MaCC = dr.GetString(0);
// if (!dr.IsDBNull(1))
// ChamCong.Thang = dr.GetInt32(1);
// if (!dr.IsDBNull(2))
// ChamCong.Nam = dr.GetInt32(2);
// if (!dr.IsDBNull(3))
// ChamCong.TenBang = dr.GetString(3);
// lsChamCong.Add(ChamCong);
// }
// ThaoTacDuLieu.DongKetNoi(conn);
// return lsChamCong;
//}
public DataTable LayBangChiTietChamCongNV(string strTK)
{
string sql = string.Format("SELECT * FROM CHITIETCHAMCONG JOIN NHANVIEN ON NHANVIEN.MANV = CHITIETCHAMCONG.MANV JOIN PHONGBAN ON PHONGBAN.MAPB = PHONG WHERE MACC = '{0}' AND TRANGTHAI = 1 ", strTK);
return(ThaoTacDuLieu.LayBang(sql));
}
public DataTable LayBangChiTietBH(string strMaBH)
{
return(ThaoTacDuLieu.LayBang(string.Format("select * from ChiTietBaoHanh ct,Serial sr, SanPham sp where ct.MaSerial = sr.MaSerial and sr.MaSanPham = sp.MaSanPham and ct.MaBaoHanh = '{0}'", strMaBH)));
}
//lấy danh sách dùng cho in report
public DataTable LayDanhSachNhanVienInreport(int dk, string MaNVHoacTen, string MaPB)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT MANV, HO, TEN, NGAYSINH, DIACHI, CMND, GIOITINH, NGUYENQUAN, TINHTHANH, QUANHUYEN, QUOCTICH, DANTOC, TONGIAO, NGAYBATDAU, PHONG, MABAC, MACV, BANGCAP, TRANGTHAI, HINHDAIDIEN, TENPB FROM NHANVIEN, PHONGBAN WHERE PHONG = MAPB");
if (MaNVHoacTen == "")
{
if (MaPB != "0")
{
if (dk == 1)
{
sql += string.Format(" AND PHONG = '{0}' AND TRANGTHAI = 1", MaPB);
}
if (dk == -1)
{
sql += string.Format(" AND PHONG = '{0}' AND TRANGTHAI = 0", MaPB);
}
}
else
{
if (dk == 1)
{
sql += string.Format(" AND TRANGTHAI = 1");
}
if (dk == -1)
{
sql += string.Format(" AND TRANGTHAI = 0");
}
}
if (dk == 0 && MaPB != "0")
{
sql += string.Format(" AND PHONG = '{0}'", MaPB);
}
}
else
{
if (MaPB != "0")
{
if (dk == 1)
{
sql += string.Format(" AND TRANGTHAI = 1 AND PHONG = '{1}' AND (MANV LIKE '%{0}%' OR TEN LIKE N'%{0}%' )", MaNVHoacTen, MaPB);
}
if (dk == -1)
{
sql += string.Format(" AND TRANGTHAI = 0 AND PHONG = '{1}' AND (MANV LIKE '%{0}%' OR TEN LIKE N'%{0}%')", MaNVHoacTen, MaPB);
}
if (dk == 0)
{
sql += string.Format(" AND PHONG = '{1}' AND (MANV LIKE '%{0}%' OR TEN LIKE N'%{0}%')", MaNVHoacTen, MaPB);
}
}
else
{
if (dk == 1)
{
sql += string.Format(" AND TRANGTHAI = 1 AND (MANV LIKE '%{0}%' OR TEN LIKE N'%{0}%')", MaNVHoacTen);
}
if (dk == -1)
{
sql += string.Format(" AND TRANGTHAI = 0 AND (MANV LIKE '%{0}%' OR TEN LIKE N'%{0}%')", MaNVHoacTen);
}
if (dk == 0)
{
sql += string.Format(" AND MANV LIKE '%{0}%' OR TEN LIKE N'%{0}%'", MaNVHoacTen);
}
}
}
sql += " ORDER BY TEN";
DataTable dsnv = new DataTable();
dsnv = ThaoTacDuLieu.LayBang(sql);
ThaoTacDuLieu.DongKetNoi(conn);
return(dsnv);
}
public DataTable KiemTraTen(string strTen)
{
return(ThaoTacDuLieu.LayBang(string.Format("select * from LoaiSanPham where TenLoaiSanPham='{0}'", strTen)));
}
public DataTable LayBangInPhieuNhap(string strMaPhieu)
{
return(ThaoTacDuLieu.LayBang(string.Format(@"select sp.TenSanPham, sp.BaoHanh, sp.DonViTinh, ct.SoLuong, ct.Gia, (ct.SoLuong * ct.Gia) as ThanhTien
from SanPham sp, ChiTietPhieuNhap ct
where sp.MaSanPham = ct.MaSanPham and ct.MaPhieuNhap = '{0}'", strMaPhieu)));
}
public DataTable LayBangLoai(string strMaLoai)
{
string query = string.Format("select * from LoaiSanPham where TrangThai=1 and MaLoaiSanPham='{0}'", strMaLoai);
return(ThaoTacDuLieu.LayBang(query));
}
public DataTable LayBangBaoHanh()
{
return(ThaoTacDuLieu.LayBang("select * from BaoHanh bh, KhachHang kh, NhanVien nv where bh.MaKhachHang = kh.MaKhachHang and bh.MaNhanVien = nv.MaNhanVien and bh.TrangThai = 1"));
}
public DataTable LayBangHangSanXuat(string strMaHSX)
{
string query = string.Format("select * from HangSanXuat where TrangThai=1 and MaHangSanXuat='{0}'", strMaHSX);
return(ThaoTacDuLieu.LayBang(query));
}