public List <phieuxuat> GetAll(phieuxuatSearch s)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var model = (from a in db.DeliveryNotes
where (s.nguoigiao == null || a.receiverName.Contains(s.nguoigiao)) &&
(s.id == null || a.DeliveryNoteID == s.id) &&
(s.id_dl == null || a.AgencyID == s.id_dl) &&
(s.nguoinhan == null || a.deliverName.Contains(s.nguoinhan)) &&
(s.tientu == null || a.totalPrice >= s.tientu) &&
(s.tienden == null || a.totalPrice <= s.tienden) &&
(s.ngaytu == null || a.deliverDateCreated >= s.ngaytu) &&
(s.ngayden == null || a.deliverDateCreated <= s.ngayden)
select new phieuxuat()
{
id = a.DeliveryNoteID,
ngayxuat = a.deliverDateCreated,
nguoigia = a.deliverName,
nguoitao = a.receiverName,
hanthanhtoan = a.endeliverdate,
id_dl = a.AgencyID,
giatri = a.totalPrice,
dl = (from b in db.Agencies
where a.AgencyID == b.AgencyID
select new daily()
{
id = b.AgencyID,
name = b.agencyName
}).FirstOrDefault()
}).ToList();
return(model);
}
}
public thongkecongnodl tkcndl(DateTime nt, DateTime nd)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
thongkecongnodl tk = new thongkecongnodl();
tk.ctg = (from a in db.DeliveryNotes
where (nt == null || a.deliverDateCreated >= nt.Date) &&
(nd == null || a.deliverDateCreated <= nd.Date)
select new chitietthongkecongnodl()
{
id = a.DeliveryNoteID,
datecreate = a.deliverDateCreated,
nguoigiao = a.deliverName,
nguoinhan = a.receiverName,
tongtien = a.totalPrice,
dl = (from b in db.Agencies
where b.AgencyID == a.AgencyID
select new daily()
{
id = b.AgencyID,
name = b.agencyName
}).FirstOrDefault(),
tientra = (from c in db.Debts
where c.DeliveryNoteID == a.DeliveryNoteID
select c.thanhtoan).Sum().HasValue? (from c in db.Debts
where c.DeliveryNoteID == a.DeliveryNoteID
select c.thanhtoan).Sum().Value:0,
tienno = a.totalPrice - (db.Debts.Where(x => x.DeliveryNoteID == a.DeliveryNoteID).Select(x => x.thanhtoan).Sum().HasValue? db.Debts.Where(x => x.DeliveryNoteID == a.DeliveryNoteID).Select(x => x.thanhtoan).Sum().Value:0)
}).ToList();
tk.tongcong = tk.ctg.Select(x => x.tongtien).Sum();
tk.tongno = tk.ctg.Select(x => x.tienno).Sum().HasValue ? tk.ctg.Select(x => x.tienno).Sum().Value:0;
tk.tongthu = tk.ctg.Select(x => x.tienno).Sum().Value;
return(tk);
}
}
public bool DeleteBook(int id)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var obj = db.Books.Find(id);
obj.status = 2;
db.SaveChanges();
return(true);
}
}
public List <nxb> GetAll()
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var model = (from a in db.Publishers
select new nxb()
{
id = a.PublisherID,
name = a.publisherName
}).ToList();
return(model);
}
}
public List <category> GetAllCate()
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var model = (from a in db.Categories
select new category()
{
id = a.CategoryID,
name = a.CategoryName
}).ToList();
return(model);
}
}
public bool Insert(phieuxuat obj)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
try
{
int id = db.DeliveryNotes.Max(x => x.DeliveryNoteID) + 10;
var ob = new DeliveryNotes();
ob.DeliveryNoteID = id;
ob.deliverName = obj.nguoigia;
ob.receiverName = obj.nguoitao;
ob.totalPrice = obj.giatri;
ob.AgencyID = obj.id_dl;
ob.endeliverdate = obj.hanthanhtoan;
ob.deliverDateCreated = obj.ngayxuat;
db.DeliveryNotes.Add(ob);
List <DeliveryDetails> rd = new List <DeliveryDetails>();
foreach (var item in obj.chitiet)
{
var ct = new DeliveryDetails();
var s = db.Books.Find(item.masach);
s.amount = s.amount - item.sl;
ct.BookID = item.masach;
ct.DeliveryNoteID = id;
ct.amount = item.sl;
ct.price = s.receiptPrice;
rd.Add(ct);
}
db.DeliveryDetails.AddRange(rd);
if (obj.thanhtoan > 0)
{
var tt = new Debts();
tt.DeliveryNoteID = id;
tt.dateCreated = obj.ngayxuat;
tt.thanhtoan = obj.thanhtoan;
tt.id_dl = obj.id_dl;
tt.creatorName = obj.nguoitao;
db.Debts.Add(tt);
}
db.SaveChanges();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
}
public List <thonhkesoluongtonkho> tksltk(DateTime nt, DateTime nd)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var lPhieuXuat = (from a in db.DeliveryDetails
from b in db.DeliveryNotes
where a.DeliveryNoteID == b.DeliveryNoteID &&
(nd == null || b.deliverDateCreated <= nd.Date)
group a by new { a.BookID } into g
select new chitet {
id = g.Key.BookID,
sl = g.Sum(p => p.amount)
});
var lPhieunhap = (from a in db.ReceiptNotes
from b in db.ReceiptDetails
where a.ReceiptNoteID == b.ReceiptNoteID &&
(nd == null || a.receiptDateCreated <= nd.Date)
group b by new { b.BookID } into g
select new chitet
{
id = g.Key.BookID,
sl = g.Sum(p => p.receiptAmount)
});
var lsach = (from a in db.Books
join b in lPhieuXuat on a.BookID equals b.id into j
from c in j.DefaultIfEmpty()
join d in lPhieunhap on a.BookID equals d.id into k
from e in k.DefaultIfEmpty()
select new thonhkesoluongtonkho()
{
id = a.BookID,
slht = a.amount,
gia = a.price,
name = a.bookName,
gianhap = a.receiptPrice,
nxb = (from l in db.Publishers
where l.PublisherID == a.PublisherID
select new nxb()
{
id = l.PublisherID,
name = l.publisherName
}).FirstOrDefault(),
slx = c.sl.HasValue?c.sl.Value:0,
sln = e.sl.HasValue ? e.sl.Value : 0,
sltk = (e.sl.HasValue ? e.sl.Value : 0) - (c.sl.HasValue ? c.sl.Value : 0)
}).ToList();
return(lsach);
}
}
public List <daily> GetAllCate()
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var model = (from a in db.Agencies
select new daily()
{
id = a.AgencyID,
name = a.agencyName,
address = a.agencyAddress,
mucno = a.maxdebt
}).ToList();
return(model);
}
}
public daily GetCatById(int id)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var model = (from a in db.Agencies
where a.AgencyID == id
select new daily()
{
id = a.AgencyID,
name = a.agencyName,
address = a.agencyAddress,
mucno = a.maxdebt
}).FirstOrDefault();
return(model);
}
}
public List <phieuthu> GetAll(int id)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var model = (from a in db.Debts
where a.DeliveryNoteID == id
select new phieuthu()
{
id = a.DebtID,
nguoithu = a.creatorName,
id_phieuxuat = a.DeliveryNoteID,
thanhtoan = a.thanhtoan,
ngaylap = a.dateCreated
}).OrderBy(x => x.ngaylap).ToList();
return(model);
}
}
public List <phieuchi> GetAll(int id)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var model = (from a in db.Payments
where a.ReceiptNoteID == id
select new phieuchi()
{
id = a.PaymentID,
nguoithu = a.creatorName,
id_phieunhap = a.ReceiptNoteID,
thanhtoan = a.price,
ngaylap = a.dateCreated
}).OrderBy(x => x.ngaylap).ToList();
return(model);
}
}
public List <book> GetBookByobj(bookSearch bs, int page = 1)
{
int RecordsPerPage = 10;
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var book = (from b in db.Books
where (bs.ten == null || b.bookName.Contains(bs.ten)) &&
b.status != 2 &&
(bs.mota == null || b.description.Contains(bs.mota)) &&
(bs.id_danhmuc == null || b.CategoryID == bs.id_danhmuc.Value) &&
(bs.ma == null || b.BookID == bs.ma.Value) &&
(bs.id_nxb == null || b.PublisherID == bs.id_nxb.Value) &&
(bs.gianhap == null || b.receiptPrice >= bs.gianhap.Value) &&
(bs.gianhapden == null || b.receiptPrice <= bs.gianhapden.Value) &&
(bs.giaxuat == null || b.price >= bs.giaxuat.Value) &&
(bs.giaxuatden == null || b.price <= bs.giaxuatden.Value)
select new book()
{
ma = b.BookID,
ten = b.bookName,
gianhap = b.receiptPrice,
giaxuat = b.price,
sl = b.amount,
id_nxb = b.PublisherID,
id_danhmuc = b.CategoryID,
nhaxb = (from c in db.Publishers
where c.PublisherID == b.PublisherID
select new nxb()
{
id = c.PublisherID,
name = c.publisherName
}).FirstOrDefault(),
cat = (from d in db.Categories
where d.CategoryID == d.CategoryID
select new category()
{
id = d.CategoryID,
name = d.CategoryName
}).FirstOrDefault()
}).OrderBy(x => x.ten).Skip((page - 1) * RecordsPerPage).Take(RecordsPerPage).ToList();
return(book);
}
}
public phieuxuat GetCatById(int id)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var model = (from a in db.DeliveryNotes
where a.DeliveryNoteID == id
select new phieuxuat()
{
id = a.DeliveryNoteID,
ngayxuat = a.deliverDateCreated,
nguoigia = a.deliverName,
nguoitao = a.receiverName,
id_dl = a.AgencyID,
hanthanhtoan = a.endeliverdate,
giatri = a.totalPrice,
phieuthu = (from c in db.Debts
where a.DeliveryNoteID == c.DeliveryNoteID
select new phieuthu()
{
id = c.DebtID,
ngaylap = c.dateCreated,
id_phieuxuat = c.DeliveryNoteID,
nguoithu = c.creatorName,
thanhtoan = c.thanhtoan
}).ToList(),
chitiet = (from b in db.DeliveryDetails
where b.DeliveryNoteID == a.DeliveryNoteID
select new Chitietphieuxuat()
{
masach = b.BookID,
mapn = b.DeliveryNoteID,
sl = b.amount,
sach = (from s in db.Books
where b.BookID == s.BookID
select new sach()
{
id = s.BookID,
name = s.bookName
}).FirstOrDefault(),
gia = b.price
}).ToList()
}).FirstOrDefault();
return(model);
}
}
public phieunhap GetCatById(int id)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var model = (from a in db.ReceiptNotes
where a.ReceiptNoteID == id
select new phieunhap()
{
id = a.ReceiptNoteID,
giatri = a.totalPrice,
nguoitao = a.receiptName,
id_nxb = a.PublisherID,
nguoigia = a.senderName,
ngayxuat = a.receiptDateCreated,
phieuchi = (from c in db.Payments
where c.ReceiptNoteID == a.ReceiptNoteID
select new phieuchi()
{
id = c.PaymentID,
id_phieunhap = c.ReceiptNoteID,
nguoithu = c.creatorName,
ngaylap = c.dateCreated,
thanhtoan = c.price
}).ToList(),
chitiet = (from b in db.ReceiptDetails
where b.ReceiptNoteID == a.ReceiptNoteID
select new Chitietphieunhap()
{
masach = b.BookID,
mapn = b.ReceiptNoteID,
sach = (from s in db.Books
where s.BookID == b.BookID
select new sach()
{
id = s.BookID,
name = s.bookName
}).FirstOrDefault(),
sl = b.receiptAmount,
gia = b.totalPrice
}).ToList()
}).FirstOrDefault();
return(model);
}
}
public bool Update(daily obj)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
try
{
var d = db.Agencies.Find(obj.id);
d.agencyName = obj.name;
d.agencyAddress = obj.address;
d.maxdebt = obj.mucno;
db.SaveChanges();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
}
public List <phieunhap> GetAll(phieunhapsearch s)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
var model = (from a in db.ReceiptNotes
where (s.nguoigiao == null || a.receiptName.Contains(s.nguoigiao)) &&
(s.id_nxb == null || a.PublisherID == s.id_nxb) &&
(s.id == null || a.ReceiptNoteID == s.id) &&
(s.nguoinhan == null || a.senderName.Contains(s.nguoinhan)) &&
(s.tientu == null || a.totalPrice >= s.tientu) &&
(s.tienden == null || a.totalPrice <= s.tienden) &&
(s.ngaytu == null || a.receiptDateCreated >= s.ngaytu) &&
(s.ngayden == null || a.receiptDateCreated <= s.ngayden)
select new phieunhap()
{
id = a.ReceiptNoteID,
ngayxuat = a.receiptDateCreated,
nguoigia = a.senderName,
nguoitao = a.receiptName,
id_nxb = a.PublisherID,
giatri = a.totalPrice,
phieuchi = (from c in db.Payments
where c.ReceiptNoteID == a.ReceiptNoteID
select new phieuchi()
{
id = c.PaymentID,
id_phieunhap = c.ReceiptNoteID,
nguoithu = c.creatorName,
ngaylap = c.dateCreated,
thanhtoan = c.price
}).ToList(),
nxb = (from b in db.Publishers
where a.PublisherID == b.PublisherID
select new nxb()
{
id = b.PublisherID,
name = b.publisherName
}).FirstOrDefault()
}).ToList();
return(model);
}
}
public bool insert(phieuchi pt)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
try
{
var entity = new Payments();
entity.ReceiptNoteID = pt.id_phieunhap;
entity.creatorName = pt.nguoithu;
entity.dateCreated = pt.ngaylap;
entity.price = pt.thanhtoan;
db.Payments.Add(entity);
db.SaveChanges();
return(true);
}catch (Exception ex)
{
return(false);
}
}
}
public bool insert(phieuthu pt)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
try
{
var entity = new Debts();
entity.DeliveryNoteID = pt.id_phieuxuat;
entity.creatorName = pt.nguoithu;
entity.dateCreated = pt.ngaylap;
entity.id_dl = pt.id_daily;
entity.thanhtoan = pt.thanhtoan;
db.Debts.Add(entity);
db.SaveChanges();
return(true);
}catch (Exception ex)
{
return(false);
}
}
}
public bool Insert(phieunhap obj)
{
using (CTyPHSachEntities db = new CTyPHSachEntities())
{
try
{
int id = db.ReceiptNotes.Max(x => x.ReceiptNoteID) + 10;
var ob = new ReceiptNotes();
ob.ReceiptNoteID = id;
ob.receiptName = obj.nguoigia;
ob.senderName = obj.nguoitao;
ob.totalPrice = obj.giatri;
ob.PublisherID = obj.id_nxb;
ob.subid = id;
ob.receiptDateCreated = obj.ngayxuat;
db.ReceiptNotes.Add(ob);
List <ReceiptDetails> rd = new List <ReceiptDetails>();
foreach (var item in obj.chitiet)
{
var ct = new ReceiptDetails();
var s = db.Books.Find(item.masach);
s.amount = s.amount + item.sl;
ct.BookID = item.masach;
ct.ReceiptNoteID = id;
ct.receiptAmount = item.sl;
ct.totalPrice = s.receiptPrice;
rd.Add(ct);
}
db.ReceiptDetails.AddRange(rd);
db.SaveChanges();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
}
