public bool CheckUserName(string TenDangNhap)
{
if (DBcon.GetTable("select * from DSNhanVien where TenDangNhap = '" + TenDangNhap + "'").Rows.Count > 0)
{
return(true);
}
return(false);
}
public DataTable getAllNCC()
{
//SqlDataAdapter dataAdapter = new SqlDataAdapter("proDSNCC", Conn);
//DataTable dt = new DataTable();
//dataAdapter.Fill(dt);
//return dt;
return(DBcon.GetTable("proDSNCC"));
}
public DataTable DSNhapTheoThang(int Nam)
{
string Query = "select MONTH(pn.NgayNhap) as Thang, DemHD.SL as SoHD, sum(ct.SoLuong) as TongHang, sum(ct.SoLuong*ct.GiaNhap) as TongGT " +
"from CTPhieuNhap as ct, PhieuNhap as pn, " +
"(select NgayNhap, COUNT(PhieuNhap.MaPN) as SL from PhieuNhap group by NgayNhap) as DemHD " +
"where ct.MaPN = pn.MaPN and YEAR(pn.NgayNhap) = " + Nam + " and pn.NgayNhap = DemHD.NgayNhap " +
"group by MONTH(pn.NgayNhap), DemHD.SL";
return(DBcon.GetTable(Query));
}
public DataTable DSXuatTheoThang(int Nam)
{
string Query = "select Month(px.NgayXuat) as Thang, DemHD.SL as SoHD, Sum(ct.SoLuong) as TongHang, SUM(ct.SoLuong*sp.DonGia - ct.SoLuong*sp.DonGia*ct.GiamGia) as TongGT " +
"from CTPhieuXuat as ct, PhieuXuat as px, SanPham as sp, " +
"(select NgayXuat, COUNT(PhieuXuat.MaPX) as SL from PhieuXuat " +
"group by NgayXuat ) as DemHD " +
"where px.MaPX = ct.MaPX and sp.MaSP = ct.MaSP and px.NgayXuat = DemHD.NgayXuat and year(px.NgayXuat) = " + Nam +
" group by Month(px.NgayXuat), DemHD.SL ";
return(DBcon.GetTable(Query));
}
public DataTable DSXuatTheoNam()
{
string Query = "select * from DSXuatTheoNam";
return DBcon.GetTable(Query);
}
public DataTable getAllLSP()
{
return(DBcon.GetTable("select * from LoaiSP"));
}
public DataTable ThongKeDSNhapTheoNgay()
{
string Query = "SELECT * FROM ThongKeDSNhapTheoNgay";
return(DBcon.GetTable(Query));
}
public DataTable getAllSP()
{
return(DBcon.GetTable("select sp.MaSP, sp.TenSP, lsp.TenLoai, ncc.TenNCC," +
" sp.GioiTinh, kc.KichCo, sp.DonGia , sp.GhiChu from SanPham as sp," +
" LoaiSP as lsp, NhaCungCap as ncc, KichCo as kc " +
"where sp.MaLoai = lsp.MaLoai and sp.MaNCC = ncc.MaNCC and sp.MaKichCo = kc.MaKichCo"));
}
public DataTable getAllPX()
{
return(DBcon.GetTable("procDSPX"));
}
public DataTable getAllCTPN()
{
return(DBcon.GetTable("select pn.MaPN, sp.TenSP, ctpn.SoLuong, ctpn.GiaNhap," +
"ncc.TenNCC from CTPhieuNhap as ctpn, PhieuNhap as pn, SanPham as sp, NhaCungCap as ncc " +
"where ctpn.MaPN = pn.MaPN and ctpn.MaSP = sp.MaSP and ctpn.MaNCC = ncc.MaNCC"));
}
public DataTable DSNhapTheoNam()
{
string Query = "select * from DSNhapTheoNam";
return(DBcon.GetTable(Query));
}
public DataTable getAllCTPX()
{
return(DBcon.GetTable("select px.MaPX, sp.TenSP, ctpx.SoLuong, ctpx.GiamGia," +
" kh.TenKH from CTPhieuXuat as ctpx, PhieuXuat as px, SanPham as sp, KhachHang as kh " +
"where ctpx.MaPX = px.MaPX and ctpx.MaSP = sp.MaSP and ctpx.MaKH = kh.MaKH"));
}
//hiển thị danh sách khách hàng
public DataTable getAllKH()
{
return(DBcon.GetTable("proDSKH"));
}
public DataTable getAllNV()
{
return(DBcon.GetTable("select * from DSNhanVien"));
}
public DataTable getAllPN()
{
return(DBcon.GetTable("proDSPN"));
}
public DataTable DSXuatTheoKH()
{
string Query = "select * from DSXuatTheoKH";
return(DBcon.GetTable(Query));
}
public DataTable DSTheoNCC()
{
string Query = "select * from DSTheoNCC";
return(DBcon.GetTable(Query));
}
