public static int GenerateReport()
{
int amount = 0;
List <SachDTO> listOfCurrentBook = SachDAO.GetAllData();
foreach (SachDTO item in listOfCurrentBook)
{
int number = int.Parse(item.LuongTon);
amount += number;
}
string querry = @"INSERT INTO BaoCaoSachTon(NgayLap, SoLuong) VALUES ('" + DateTime.Now.Date + "'," + amount.ToString() + ")";
int result = DataProvider.ExecuteNonQuerry(querry);
querry = @"SELECT IDENT_CURRENT('BaoCaoSachTon') AS Id";
string id = DataProvider.ExecuteQuerry(querry).Rows[0]["Id"].ToString();
if (result == 0)
{
return(0);
}
else
{
foreach (SachDTO item in listOfCurrentBook)
{
querry = @"INSERT INTO Sach_BaoCao(Sach, BaoCao) VALUES (" + item.Id + "," + id + ")";
result = DataProvider.ExecuteNonQuerry(querry);
}
}
return(result);
}
public static int AddBill(HoaDonDTO bill)
{
string querry1 = @"INSERT INTO HoaDon (Ngay, ThanhTien, SoTienThanhToan) VALUES
('" + bill.Ngay + "', '" + bill.ThanhTien + "', '" + bill.SoTienThanhToan + "')";
int result = DataProvider.ExecuteNonQuerry(querry1);
if (result <= 0)
{
return(0);
}
string querry2 = @"SELECT IDENT_CURRENT('HoaDon') AS Id";
string id = DataProvider.ExecuteQuerry(querry2).Rows[0]["Id"].ToString();
foreach (SachDTO item in bill.Sach)
{
string querry = @"INSERT INTO Sach_HoaDon (SoLuong, HoaDon, Sach) VALUES (" + item.LuongTon + "," + id +
"," + item.Id + ")";
int res = DataProvider.ExecuteNonQuerry(querry);
if (result <= 0)
{
return(0);
}
}
return(1);
}
public static string addDeb(CongNoDTO deb)
{
string querry = @"INSERT INTO CongNo (NoDau, NoCuoi, PhatSinh) VALUES (" + deb.NoDau + ", " + deb.NoCuoi +
", " + deb.PhatSinh + ")";
int result = DataProvider.ExecuteNonQuerry(querry);
querry = @"SELECT IDENT_CURRENT('CongNo') AS Id";
string id = DataProvider.ExecuteQuerry(querry).Rows[0]["Id"].ToString();
return(id);
}
public static CongNoDTO getDeb(string Id)
{
string querry = @"SELECT * FROM CongNo WHERE Id=" + Id;
DataTable table = DataProvider.ExecuteQuerry(querry);
CongNoDTO result = new CongNoDTO();
result.Id = table.Rows[0]["Id"].ToString();
result.NoDau = table.Rows[0]["NoDau"].ToString();
result.NoCuoi = table.Rows[0]["NoCuoi"].ToString();
result.PhatSinh = table.Rows[0]["PhatSinh"].ToString();
return(result);
}
public static List <BaoCaoSachTonDTO> GetAllData()
{
string querry = @"SELECT Sach.Id AS SachId, BaoCaoSachTon.Id AS BaoCaoId, TenSach, DonGia, LuongTon, NgayLap, SoLuong, TenTacGia, TenTheLoai FROM Sach INNER JOIN Sach_BaoCao ON Sach_BaoCao.Sach = Sach.Id INNER JOIN BaoCaoSachTon ON BaoCaoSachTon.Id = Sach_BaoCao.BaoCao INNER JOIN TheLoai ON TheLoai.Id = Sach.TheLoai INNER JOIN TacGia ON TacGia.Id = Sach.TacGia";
DataTable table = DataProvider.ExecuteQuerry(querry);
List <BaoCaoSachTonDTO> result = new List <BaoCaoSachTonDTO>();
if (table.Rows.Count <= 0)
{
return(null);
}
else
{
string last = "0";
int i = 0;
while (i < table.Rows.Count)
{
BaoCaoSachTonDTO item = new BaoCaoSachTonDTO();
item.Id = table.Rows[i]["BaoCaoId"].ToString();
item.NgayLap = table.Rows[i]["NgayLap"].ToString();
item.SoLuong = table.Rows[i]["SoLuong"].ToString();
last = item.Id;
item.Sach = new List <SachDTO>();
while (table.Rows[i]["BaoCaoId"].ToString() == last)
{
SachDTO sach = new SachDTO();
sach.Id = table.Rows[i]["SachId"].ToString();
sach.TenSach = table.Rows[i]["TenSach"].ToString();
sach.LuongTon = table.Rows[i]["LuongTon"].ToString();
sach.DonGia = table.Rows[i]["DonGia"].ToString();
sach.TenTacGia = table.Rows[i]["TenTacGia"].ToString();
sach.TenTheLoai = table.Rows[i]["TenTheLoai"].ToString();
item.Sach.Add(sach);
i++;
if (i >= table.Rows.Count)
{
break;
}
}
result.Add(item);
}
}
return(result);
}