public static string DeleteBook(BLibraryPro.Book Book)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_BOOK WHERE BOOK_CODE = '" + Book.BookCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Deleted Successfully";
}
public static string DeleteSource(BLibraryPro.BookSource source)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_BOOK_SOURCE WHERE SOURCE_TYPE_CODE = '" + source.SourceTypeCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Deleted Successfully";
}
public static string DeleteFinePolicy(BLibraryPro.FinePolicy FinePolicy)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_LIB_FINE_POLICY WHERE FINE_CODE = '" + FinePolicy.FinePolicyCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Deleted Successfully";
}
public static string DeleteCategory(BLibraryPro.Category category)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_BOOK_CATEGORY WHERE CATEGORY_CODE = '" + category.CategoryCode + "'");
da.ExecuteNonQuery(sql,CommandType.Text);
return "Data Deleted Successfully";
}
public static string SaveBookLot(BLibraryPro.BookLot ObjBookLot, string UserName, string Author)
{
DataAccess da = new DataAccess();
string new_id = BLibraryPro.MISC.GetNewID("BOOKLOT");
string sql = string.Format(@"INSERT
INTO M_BOOK_LOT
(BOOK_LOT_NO,BOOK_CODE,EDITION_CODE,YEAR_OF_PUBLICATION,ISBN,PRICE,NO_OF_PAGES,SOURCE_TYPE_CODE,RECEIVED_ON,NO_OF_BOOKS)
VALUES('" + new_id + @"','" + ObjBookLot.BookCode + @"','" + ObjBookLot.EditionCode + @"','" + ObjBookLot.YearOfPublication + @"','" + ObjBookLot.ISBN + @"','" + ObjBookLot.Price + @"','" + ObjBookLot.NoOfPage + @"','" + ObjBookLot.SourceTypeCode + @"',to_date('" + Convert.ToDateTime(ObjBookLot.ReceivedOn).ToShortDateString() + "','MM/dd/yyyy'),'" + ObjBookLot.NoOfBooks + @"') "
);
da.ExecuteNonQuery(sql, CommandType.Text);
//insert data into inventory table
string Inv_new_id = BLibraryPro.MISC.GetNewID("BOOKINVENTORY");
string sqlInv = string.Format(@"INSERT
INTO M_BOOK_INVENTORY
(BOOK_SN,BOOK_LOT_NO,BOOK_CODE,ENTRY_DATE,ENTERED_BY)
VALUES('" + Inv_new_id + @"','" + new_id + @"','" + ObjBookLot.BookCode + @"',to_date('" + Convert.ToDateTime(ObjBookLot.ReceivedOn).ToShortDateString() + "','MM/dd/yyyy'),'" + UserName + @"') "
);
da.ExecuteNonQuery(sqlInv, CommandType.Text);
//insert data into book author table
string sqlBookAuthor = string.Format(@"INSERT
INTO M_BOOK_AUTHORS
(BOOK_LOT_NO,BOOK_CODE,AUTHOR_CODE)
VALUES('" + new_id + @"','" + ObjBookLot.BookCode + @"','" + Author + @"') "
);
da.ExecuteNonQuery(sqlBookAuthor, CommandType.Text);
return "Data added Successfully";
}
public static string DeletePublication(BLibraryPro.Publication Publication)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_PUBLICATION WHERE PUBLICATION_CODE ='" + Publication.PublicationCode +@"'");
da.ExecuteNonQuery(sql,CommandType.Text);
return "Data Deleted Successfully";
}
public static string DeleteMembers(BLibraryPro.Member Member)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_MEMBER WHERE MEMBER_CODE = '" + Member.MemberCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Deleted Successfully";
}
public static void Populate(BLibraryPro.BookLot BookLot, string new_code)
{
DataAccess da = new DataAccess();
string sql = @"SELECT M_BOOK_LOT.BOOK_LOT_NO,
M_BOOK_LOT.BOOK_CODE,
M_BOOK_LOT.EDITION_CODE,
M_BOOK_LOT.YEAR_OF_PUBLICATION,
M_BOOK_LOT.ISBN,
M_BOOK_LOT.PRICE,
M_BOOK_LOT.NO_OF_PAGES,
M_BOOK_LOT.SOURCE_TYPE_CODE,
M_BOOK_LOT.RECEIVED_ON,
M_BOOK_LOT.NO_OF_BOOKS
FROM M_BOOK_LOT
where BOOK_LOT_NO = '" + new_code + "'";
OracleDataReader odr = da.ExecuteReader(sql, CommandType.Text);
if (odr.Read())
{
BookLot.BookLotNo = Convert.ToInt32(new_code);
BookLot.BookCode = Convert.ToInt32(odr["BOOK_CODE"].ToString());
BookLot.EditionCode = odr["EDITION_CODE"].ToString();
BookLot.YearOfPublication = Convert.ToInt32(odr["YEAR_OF_PUBLICATION"].ToString());
BookLot.ISBN = odr["ISBN"].ToString();
BookLot.Price = float.Parse(odr["PRICE"].ToString());
BookLot.NoOfPage = Convert.ToInt32(odr["NO_OF_PAGES"].ToString());
BookLot.SourceTypeCode = odr["SOURCE_TYPE_CODE"].ToString();
BookLot.ReceivedOn = Convert.ToDateTime(odr["RECEIVED_ON"].ToString());
BookLot.NoOfBooks = Convert.ToInt32(odr["NO_OF_BOOKS"].ToString());
}
}
public static void Populate(BLibraryPro.Member Member, string new_code)
{
DataAccess da = new DataAccess();
string sql = @"SELECT M_MEMBER.MEMBER_CODE,
M_MEMBER.TYPE_CODE,
M_MEMBER.FNAME,
M_MEMBER.MNAME,
M_MEMBER.LNAME,
M_MEMBER.CITY_CODE,
M_MEMBER.REGISTERED_BY,
M_MEMBER.REGISTERED_DATE,
M_MEMBER.ACTIVE_FLAG,
M_MEMBER.REASON_FOR_INACTIVE,
M_MEMBER.INACTIVE_DATE,
M_MEMBER.INACTIVATED_BY
FROM M_MEMBER
where MEMBER_CODE = '" + new_code + "'";
OracleDataReader odr = da.ExecuteReader(sql, CommandType.Text);
if (odr.Read())
{
Member.MemberCode = new_code;
Member.TypeCode = odr["TYPE_CODE"].ToString();
Member.FirstName = odr["FNAME"].ToString();
Member.MiddleName = odr["MNAME"].ToString();
Member.LastName = odr["LNAME"].ToString();
Member.CityCode = odr["CITY_CODE"].ToString();
Member.RegisteredBy = odr["REGISTERED_BY"].ToString();
Member.RegisteredDate = Convert.ToDateTime(odr["REGISTERED_DATE"].ToString());
Member.ActiveFlag = odr["ACTIVE_FLAG"].ToString();
Member.InactiveReason = odr["REASON_FOR_INACTIVE"].ToString();
Member.InactivedDate = Convert.ToDateTime(odr["INACTIVE_DATE"].ToString());
Member.InactivatedBy = odr["INACTIVATED_BY"].ToString();
}
}
public static string DeleteBookInventory(BLibraryPro.BookInventory BookInventory)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_BOOK_INVENTORY WHERE BOOK_SN = '" + BookInventory.BookSn + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Deleted Successfully";
}
public static string DeleteMemberType(BLibraryPro.MemberType memberType)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_LIBRARY_MEMBER_TYPE WHERE TYPE_CODE = '" + memberType.MemberTypeCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Deleted Successfully";
}
public static string DeleteCardType(BLibraryPro.CardType cardType)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_MEMBER_CARD_TYPES WHERE CARD_TYPE = '" + cardType.CardTypeCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Deleted Successfully";
}
public static string DeleteAuthor(BLibraryPro.Author Author)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_AUTHOR WHERE AUTHOR_CODE = '" + Author.AuthorCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Deleted Successfully";
}
public static string DeleteEdition(BLibraryPro.Edition ObjEdition)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_EDITION WHERE EDITION_CODE = '" + ObjEdition.EditionCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Deleted Successfully";
}
public static string DeleteBookLot(BLibraryPro.BookLot BookLot)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"DELETE FROM M_BOOK_LOT WHERE BOOK_LOT_NO = '" + BookLot.BookLotNo + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Deleted Successfully";
}
public static string UpdateSource(BLibraryPro.BookSource source)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"UPDATE M_BOOK_SOURCE
SET SOURCE_DESC = '" + source.SourceDesc + @"',
REMARKS= '" + source.remarks + @"'
WHERE SOURCE_TYPE_CODE = '" + source.SourceTypeCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Updated Successfully";
}
public static string UpdateCategory(BLibraryPro.Category category)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"UPDATE M_BOOK_CATEGORY
SET CATEGORY_DESC = '" + category.CategoryDesc + @"',
REMARKS= '" + category.remarks + @"'
WHERE CATEGORY_CODE = '" + category.CategoryCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Updated Successfully";
}
public static string UpdateEditon(BLibraryPro.Edition ObjEdition)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"UPDATE M_EDITION
SET EDITION_DESC = '" + ObjEdition.EditionDesc + @"',
REMARKS= '" + ObjEdition.Remarks + @"'
WHERE EDITION_CODE = '" + ObjEdition.EditionCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Updated Successfully";
}
public static string UpdatePublication(BLibraryPro.Publication publication)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"UPDATE M_PUBLICATION
SET PUBLICATION_DESC = '" +publication.PublicationDesc+ @"',
COUNTRY_CODE = '" + publication.CountryCode + @"',ESTABLISHED_DATE = to_date('" + Convert.ToDateTime(publication.EstDate).ToShortDateString() + "','MM/dd/yyyy')" + @"
WHERE PUBLICATION_CODE = '" + publication.PublicationCode + @"'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Updated Successfully";
}
public static string UpdateCardType(BLibraryPro.CardType cardType)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"UPDATE M_MEMBER_CARD_TYPES
SET TYPE_DESC = '" + cardType.TypeDesc + @"',
NO_OF_BOOKS_ALLOWED= '" + cardType.BooksAllowed + @"'
WHERE CARD_TYPE = '" + cardType.CardTypeCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Updated Successfully";
}
public static string UpdateMemberType(BLibraryPro.MemberType memberType)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"UPDATE M_LIBRARY_MEMBER_TYPE
SET TYPE_DESC = '" + memberType.TypeDesc + @"',
ACTIVE_FLAG= '" + memberType.ActiveFlag + @"',
REASON_FOR_INACTIVE= '" + memberType.Reason + @"'
WHERE TYPE_CODE = '" + memberType.MemberTypeCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Updated Successfully";
}
public static string UpdateBookAuthor(BLibraryPro.BookAuthors BookAuthors)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"UPDATE M_BOOK_AUTHORS
SET BOOK_LOT_NO = '" + BookAuthors.BookLotNo + @"',
BOOK_CODE = '" + BookAuthors.BookCode + @"',
AUTHOR_CODE = '" + BookAuthors.AuthorCode + @"'
WHERE AUTHOR_CODE = '" + BookAuthors.AuthorCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Updated Successfully";
}
public static string SaveEdition(BLibraryPro.Edition ObjEdition)
{
DataAccess da = new DataAccess();
string new_id = BLibraryPro.MISC.GetNewID("EDITION");
string sql = string.Format(@"INSERT
INTO M_EDITION
(EDITION_CODE ,EDITION_DESC,REMARKS)
VALUES('" + new_id + @"',' " + ObjEdition.EditionDesc + @"','" + ObjEdition.Remarks + @"' ) "
);
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data added Successfully";
}
public static string UpdateAuthor(BLibraryPro.Author Author)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"UPDATE M_AUTHOR
SET FNAME = '" + Author.FirstName + @"',
MNANE = '" + Author.MiddleName + @"',
LNAME = '" + Author.LastName + @"',
COUNTRY_CODE = '" + Author.CountryCode + @"'
WHERE AUTHOR_CODE = '" + Author.AuthorCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Updated Successfully";
}
public static string UpdateBookInventory(BLibraryPro.BookInventory BookInventory)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"UPDATE M_BOOK_INVENTORY
SET BOOK_LOT_NO = '" + BookInventory.BookLotNo + @"',
BOOK_CODE = '" + BookInventory.BookCode + @"',
ENTRY_DATE = '" + BookInventory.EntryDate + @"',
ENTERED_BY = '" + BookInventory.EntryBy + @"'
WHERE BOOK_SN = '" + BookInventory.BookSn + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Updated Successfully";
}
public static string SaveCategory(BLibraryPro.Category ObjCategory)
{
DataAccess da = new DataAccess();
string new_id = BLibraryPro.MISC.GetNewID("CATEGORY");
string sql = string.Format(@"INSERT
INTO M_BOOK_CATEGORY
(CATEGORY_CODE ,CATEGORY_DESC,REMARKS)
VALUES('" + new_id + @"',' " + ObjCategory.CategoryDesc + @"','" + ObjCategory.remarks + @"' ) "
);
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data added Successfully";
}
public static string SaveCardType(BLibraryPro.CardType ObjCardType)
{
DataAccess da = new DataAccess();
string new_id = BLibraryPro.MISC.GetNewID("CARDTYPE");
string sql = string.Format(@"INSERT
INTO M_MEMBER_CARD_TYPES
(CARD_TYPE ,TYPE_DESC,NO_OF_BOOKS_ALLOWED)
VALUES('" + new_id + @"',' " + ObjCardType.TypeDesc + @"','" + ObjCardType.BooksAllowed + @"' ) "
);
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data added Successfully";
}
public static string SaveBookInventory(BLibraryPro.BookInventory ObjBookInventory)
{
DataAccess da = new DataAccess();
string new_id = BLibraryPro.MISC.GetNewID("BOOKINVENTORY");
string sql = string.Format(@"INSERT
INTO M_BOOK_INVENTORY
(BOOK_SN,BOOK_LOT_NO,BOOK_CODE,ENTRY_DATE,ENTERED_BY)
VALUES('" + new_id + @"','" + ObjBookInventory.BookLotNo + @"','" + ObjBookInventory.BookCode + @"','" + ObjBookInventory.EntryDate + @"','" + ObjBookInventory.EntryBy + @"') "
);
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data added Successfully";
}
public static string UpdateFinePolicy(BLibraryPro.FinePolicy FinePolicy)
{
DataAccess da = new DataAccess();
string sql = string.Format(@"UPDATE M_LIB_FINE_POLICY
SET FINE_DESC = '" + FinePolicy.FinePolicyDesc + @"',
FROM_DAYS= '" + FinePolicy.FromDays + @"',
TO_DAYS= '" + FinePolicy.ToDays + @"',
FINE_PER_DAY= '" + FinePolicy.FinePerDay + @"',
ACTIVE_FLAG= '" + FinePolicy.ActiveFlag + @"',
REASON_FOR_INACTIVE= '" + FinePolicy.Reason + @"'
WHERE FINE_CODE = '" + FinePolicy.FinePolicyCode + "'");
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data Updated Successfully";
}
public static string SaveFinePolicy(BLibraryPro.FinePolicy ObjFinePolicy)
{
DataAccess da = new DataAccess();
int new_id = Convert.ToInt32(BLibraryPro.MISC.GetNewID("FINEPOLICY"));
string sql = string.Format(@"INSERT
INTO M_LIB_FINE_POLICY
(FINE_CODE ,FINE_DESC,FROM_DAYS,TO_DAYS,FINE_PER_DAY,ACTIVE_FLAG,REASON_FOR_INACTIVE)
VALUES('" + new_id + @"',' " + ObjFinePolicy.FinePolicyDesc + @"','" + ObjFinePolicy.FromDays + @"','" + ObjFinePolicy.ToDays + @"','" + ObjFinePolicy.FinePerDay + @"','" + ObjFinePolicy.ActiveFlag + @"','" + ObjFinePolicy.Reason + @"' ) "
);
da.ExecuteNonQuery(sql, CommandType.Text);
return "Data added Successfully";
}
