//Search Member Name By Code
public static Member getMemberByCode(string memberCode)
{
Member memberObj = new Member();
BookPOSEntities3 db = new BookPOSEntities3();
try
{
var memQuery = from mem in db.Members
where mem.MemberCode == memberCode
select mem;
if (memQuery.SingleOrDefault() != null) //must be use first or default (can error when return multiple value)
{
memberObj = memQuery.Single();
}
else
{
memberObj = null;
}
db.Dispose();
}
catch (Exception ex)
{
throw ex;
}
return(memberObj);
}
//Search For All Book by Filter
public static List <SearchBook_Result> getSearchBook(string BookName, string Author, int CategoryID)
{
List <SearchBook_Result> resultList = new List <SearchBook_Result>();
BookPOSEntities3 db = new BookPOSEntities3();
try
{
var bookName = new SqlParameter("@BookName", BookName);
var author = new SqlParameter("@Author", Author);
var categoryId = new SqlParameter("@Category", CategoryID);
resultList = db.Database
.SqlQuery <SearchBook_Result>("SearchBook @BookName,@Author,@Category", bookName, author, categoryId)
.ToList();
}catch (Exception ex)
{
throw ex;
}
finally
{
db.Dispose();
}
return(resultList);
}
//Update Restore Book from Rent
public static int RestoreRentBook(int Id)
{
int l_return = 0; // For Return Value for Save or Fail
BookPOSEntities3 db = new BookPOSEntities3();
using (var transaction = db.Database.BeginTransaction())
{
try
{
var result = db.RentBookDetails.SingleOrDefault(b => b.RentDetailID == Id);
if (result != null)
{
result.Status = 0;
l_return = db.SaveChanges();
}
transaction.Commit();
transaction.Dispose();
db.Dispose();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
return(l_return);
}
//Search For Rent Book History
public static List <SearchRentBookHistory_Result> getSearchRentBookHistory(string BookName, long memberID, int CategoryID, string fDate, string tDate)
{
List <SearchRentBookHistory_Result> resultList = new List <SearchRentBookHistory_Result>();
BookPOSEntities3 db = new BookPOSEntities3();
try
{
var memID = new SqlParameter("@MemberID", memberID);
var bookName = new SqlParameter("@BookName", BookName);
var categoryId = new SqlParameter("@Category", CategoryID);
var startDate = new SqlParameter("@StartDate", fDate);
var endDate = new SqlParameter("@EndDate", tDate);
resultList = db.Database
.SqlQuery <SearchRentBookHistory_Result>("SearchRentBookHistory @MemberID,@BookName,@Category,@StartDate,@EndDate",
memID, bookName, categoryId, startDate, endDate).ToList();
db.Dispose();
}
catch (Exception ex)
{
throw ex;
}
return(resultList);
}
//get Member Rows Count
public static int getMemberCount()
{
BookPOSEntities3 db = new BookPOSEntities3();
int l_count = db.Members.Count();
db.Dispose();
return(l_count);
}
//Master and Detail INSERT
public static int SaveDAO(BookRent bookObj, DataTable dt)
{
BookPOSEntities3 db = new BookPOSEntities3();
using (var transaction = db.Database.BeginTransaction())
{
try
{
var entity = db.Set <BookRent>().Create();
//mapping the values of your view models to data models
entity.MemberId = bookObj.MemberId;
entity.StartDate = bookObj.StartDate;
entity.IssueDate = bookObj.IssueDate;
entity.NumberOfDay = bookObj.NumberOfDay;
//INSERT MASTER TABLE
db.BookRents.Add(entity);
int l_return = db.SaveChanges();
long RentID = entity.RentID; //GET MASTER ID
if (l_return > 0)
{
//INSERT DETAIL TABLE
for (int i = 0; i < dt.Rows.Count; i++)
{
var RentDetail = db.Set <RentBookDetail>().Create(); //can not assign null value
RentDetail.RentID = RentID;
RentDetail.BookID = Convert.ToInt64(dt.Rows[i]["BookID"].ToString());
RentDetail.Status = 1;
db.RentBookDetails.Add(RentDetail);
l_return = db.SaveChanges();
RentDetail = null; //can not use null vlue
}
}
transaction.Commit();
transaction.Dispose();
db.Dispose();
return(l_return);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
//get Category
public static List <Category> getCategory()
{
List <Category> categoryList = null;
try
{
BookPOSEntities3 db = new BookPOSEntities3();
categoryList = db.Categories.ToList();
db.Dispose();
}
catch (Exception ex)
{
throw ex;
}
return(categoryList);
}
public static int getCategoryID(string catName)
{
int category_Id = 0;
try
{
BookPOSEntities3 db = new BookPOSEntities3();
category_Id = Convert.ToInt32((from cat in db.Categories
where cat.CategoryName.Equals(catName)
select cat.CategoryId).SingleOrDefault());
}
catch (Exception ex)
{
throw ex;
}
return(category_Id);
}
//Insert New Member
public static int SaveNewMember(DataTable dt)
{
BookPOSEntities3 db = new BookPOSEntities3();
int l_return = 0;
using (var transaction = db.Database.BeginTransaction())
{
try
{
//INSERT DETAIL TABLE
for (int i = 0; i < dt.Rows.Count; i++)
{
var Obj = db.Set <Member>().Create();
Obj.MemberCode = dt.Rows[i]["MemberCode"].ToString();
Obj.MemberName = dt.Rows[i]["MemberName"].ToString();
Obj.email = dt.Rows[i]["Email"].ToString();
Obj.City = dt.Rows[i]["City"].ToString();
Obj.Phone = dt.Rows[i]["Phone"].ToString();
Obj.Active = 1;
Obj.Address = dt.Rows[i]["Address"].ToString();
db.Members.Add(Obj);
l_return = db.SaveChanges();
}
transaction.Commit();
transaction.Dispose();
db.Dispose();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
return(l_return);
}
//Insert New Book
public static int SaveNewBook(DataTable dt)
{
BookPOSEntities3 db = new BookPOSEntities3();
int l_return = 0;
using (var transaction = db.Database.BeginTransaction())
{
try
{
//INSERT DETAIL TABLE
for (int i = 0; i < dt.Rows.Count; i++)
{
var bookObj = db.Set <Book>().Create();
bookObj.BookCode = dt.Rows[i]["BookCode"].ToString();
bookObj.BookName = dt.Rows[i]["BookName"].ToString();
bookObj.CategoryId = Convert.ToInt64(dt.Rows[i]["Category"]);
bookObj.Author = dt.Rows[i]["Author"].ToString();
bookObj.ISBN = dt.Rows[i]["ISBN"].ToString();
db.Books.Add(bookObj);
l_return = db.SaveChanges();
}
transaction.Commit();
transaction.Dispose();
db.Dispose();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
return(l_return);
}
