public DataTable GetTable_CT(string ma)
{
DataTable dt;
string sql = "select TenSach,SoLuong,DonGia,SUM(SoLuong*DonGia) as 'ThanhTien' from CHI_TIET_HOA_DON inner join SACH on CHI_TIET_HOA_DON.MaSach=SACH.MaSach where MaHD='" + ma + "' group by TenSach,SoLuong,DonGia";
dt = XuLy.CreateTable(sql);
return(dt);
}
public DataTable GetTable_CT(string ma)
{
DataTable dt;
string sql = "select TenSach,SoLuong,DonGiaNhap,SUM(SoLuong*DonGiaNhap) as 'ThanhTien' from CHI_TIET_PHIEU_NHAP inner join SACH on CHI_TIET_PHIEU_NHAP.MaSach=SACH.MaSach where MaPN='" + ma + "' group by TenSach,SoLuong,DonGiaNhap";
dt = XuLy.CreateTable(sql);
return(dt);
}
public DataTable BaoCao_Thang(string month)
{
DataTable dt;
string sql = "select TenSach,SUM(SoLuong)as 'SoLuongBan',DonGia,SUM(SoLuong*DonGia) as 'ThanhTien' " +
"from SACH inner join CHI_TIET_HOA_DON on SACH.MaSach = CHI_TIET_HOA_DON.MaSach " +
"inner join HOA_DON on CHI_TIET_HOA_DON.MaHD = HOA_DON.MaHD " +
"where CONVERT(NVARCHAR(7), NgayMua, 120) = '" + month + "' " +
"group by TenSach,DonGia order by SoLuongBan DESC";
dt = XuLy.CreateTable(sql);
return(dt);
}
public DataTable BaoCao_Nam(string year)
{
DataTable dt;
string sql = "select TenSach,SUM(SoLuong)as 'SoLuongBan',DonGia,SUM(SoLuong*DonGia) as 'ThanhTien' " +
"from SACH inner join CHI_TIET_HOA_DON on SACH.MaSach = CHI_TIET_HOA_DON.MaSach " +
"inner join HOA_DON on CHI_TIET_HOA_DON.MaHD = HOA_DON.MaHD " +
"where YEAR(NgayMua) = '" + year + "' " +
"group by TenSach,DonGia order by SoLuongBan DESC";
dt = XuLy.CreateTable(sql);
return(dt);
}
public bool Sua_NCC(Person p)
{
string sql = "update NHA_CUNG_CAP set TenNCC=N'" + p.ten + "',DiaChi=N'" + p.diachi + "',Sdt='" + p.sdt + "' where MaNCC='" + p.ma + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Them_NCC(Person p)
{
string sql = "insert into NHA_CUNG_CAP(TenNCC,DiaChi,Sdt) values(N'" + p.ten + "', N'" + p.diachi + "', '" + p.sdt + "')";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Xoa_TL(string ma)
{
string sql = "delete from THE_LOAI where MaTL='" + ma + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Them_TL(TheLoai tl)
{
string sql = "insert into THE_LOAI(TenTL) values(N'" + tl.ten + "')";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Them_CT(string mahd, string masach, int soluong)
{
string sql = "insert into CHI_TIET_HOA_DON(SoLuong,MaSach,MaHD) values('" + soluong + "', '" + masach + "', '" + mahd + "')";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Sua_Sach(Sach s)
{
string sql = "update SACH set TenSach=N'" + s.tensach + "',TacGia= N'" + s.tacgia + "',DonGiaNhap='" + s.dongianhap + "',DonGia='" + s.dongia + "',MaTL='" + s.matl + "',MaNXB='" + s.manxb + "' where MaSach='" + s.masach + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Them_Sach(Sach s)
{
string sql = "insert into SACH(TenSach,TacGia,DonGiaNhap,DonGia,SoLuongCon,MaTL,MaNXB) values(N'" + s.tensach + "', N'" + s.tacgia + "', '" + s.dongianhap + "', '" + s.dongia + "','" + s.soluongcon + "', '" + s.matl + "', '" + s.manxb + "')";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool CapNhatSoLuongNhap(string ma, int sl)
{
string sql = "update SACH set SoLuongCon=SoLuongCon +'" + sl + "' where MaSach='" + ma + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public int SoLuongCon(string ma)
{
try
{
DataTable dt;
string sql = "select SoLuongCon from SACH where MaSach='" + ma + "'";
dt = XuLy.CreateTable(sql);
DataRow dr = dt.Rows[0];
int sum = Convert.ToInt32(dr["SoLuongCon"].ToString());
return(sum);
}
catch { return((int)0); }
}
public bool Xoa_PN(string ma)
{
string sql = "delete from PHIEU_NHAP where MaPN='" + ma + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Them_PN(PhieuNhap pn)
{
string sql = "insert into PHIEU_NHAP(MaNV,MaNCC,NgayNhap) values('" + pn.manv + "', '" + pn.mancc + "', '" + pn.ngaynhap + "')";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Them_NV(NhanVien nv)
{
string sql = "insert into NHAN_VIEN(TenNV,DiaChi,Sdt,MatKhau) values( N'" + nv.ten + "', N'" + nv.diachi + "', '" + nv.sdt + "', '" + nv.matkhau + "')";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Xoa_Sach(string ma)
{
string sql = "delete from SACH where MaSach='" + ma + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Xoa_CT_MaHD(string mahd)
{
string sql = "delete from CHI_TIET_HOA_DON where MaHD='" + mahd + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Xoa_NXB(string ma)
{
string sql = "delete from NHA_XUAT_BAN where MaNXB='" + ma + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public int TongTien(string ma)
{
try
{
DataTable dt;
string sql = "select SUM(SoLuong*DonGia) as 'TongTien' from CHI_TIET_HOA_DON inner join SACH on CHI_TIET_HOA_DON.MaSach=SACH.MaSach where MaHD='" + ma + "'";
dt = XuLy.CreateTable(sql);
DataRow dr = dt.Rows[0];
int sum = Convert.ToInt32(dr["TongTien"].ToString());
return(sum);
}
catch { return((int)0); }
}
public bool Them_KH(Person p)
{
string sql = "insert into KHACH_HANG(TenKH,DiaChi,Sdt) values(N'" + p.ten + "', N'" + p.diachi + "', '" + p.sdt + "')";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Sua_TL(TheLoai tl)
{
string sql = "update THE_LOAI set TenTL=N'" + tl.ten + "' where MaTL='" + tl.ma + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Sua_KH(Person p)
{
string sql = "update KHACH_HANG set TenKH=N'" + p.ten + "',DiaChi=N'" + p.diachi + "',Sdt='" + p.sdt + "' where MaKH='" + p.ma + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Xoa_KH(string ma)
{
string sql = "delete from KHACH_HANG where MaKH='" + ma + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Xoa_NCC(string ma)
{
string sql = "delete from NHA_CUNG_CAP where MaNCC='" + ma + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Xoa_HD(string ma)
{
string sql = "delete from HOA_DON where MaHD='" + ma + "'";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Them_CT(string mapn, string masach, int soluong)
{
string sql = "insert into CHI_TIET_PHIEU_NHAP(SoLuong,MaSach,MaPN) values('" + soluong + "', '" + masach + "', '" + mapn + "')";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Them_HD(HoaDon hd)
{
string sql = "insert into HOA_DON(MaNV,MaKH,NgayMua,KhachTra) values('" + hd.manv + "', '" + hd.makh + "', '" + hd.ngaymua + "','" + hd.khachtra + "')";
if (XuLy.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public int BaoCao_TongSL_Thang(string month)
{
try
{
DataTable dt;
string sql = "select SUM(SoLuong)as 'TongSoLuongBan' " +
"from CHI_TIET_HOA_DON inner join HOA_DON on CHI_TIET_HOA_DON.MaHD=HOA_DON.MaHD " +
"where CONVERT(NVARCHAR(7), NgayMua, 120) = '" + month + "'";
dt = XuLy.CreateTable(sql);
DataRow dr = dt.Rows[0];
int sum = Convert.ToInt32(dr["TongSoLuongBan"].ToString());
return(sum);
}
catch { return((int)0); }
}
public int BaoCao_TongSL_Nam(string year)
{
try
{
DataTable dt;
string sql = "select SUM(SoLuong)as 'TongSoLuongBan' " +
"from CHI_TIET_HOA_DON inner join HOA_DON on CHI_TIET_HOA_DON.MaHD=HOA_DON.MaHD " +
"where YEAR(NgayMua) = '" + year + "'";
dt = XuLy.CreateTable(sql);
DataRow dr = dt.Rows[0];
int sum = Convert.ToInt32(dr["TongSoLuongBan"].ToString());
return(sum);
}
catch { return((int)0); }
}