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 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 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 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 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 GetNewID(string ruleName) { // DataAccess da = new DataAccess(); OracleDataReader odr; decimal org_new_id= 0; string new_val = ""; odr = da.ExecuteReader("select * from auto_num where rule_name = '"+ruleName+"'", CommandType.Text); if (odr.Read()) { string prefix = odr["PRE_FIX"].ToString(); string old_val = odr["CUR_VALUE"].ToString(); string pad_char = odr["PAD_CHAR"].ToString(); int len = Convert.ToInt32(odr["LENGTH"]); string pad_val=""; for (int i = 0; i < len - prefix.Length-old_val.Length; i++) { pad_val += pad_char; } org_new_id = Convert.ToDecimal(odr["CUR_VALUE"]); new_val = prefix + pad_val + org_new_id.ToString(); //update to new odr.Close(); da.ExecuteNonQuery("update auto_num set cur_value = cur_value+1 where rule_name = '" + ruleName + "' ", CommandType.Text); } return new_val; }
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 DataTable GetMembers(string paramFilterValues) { DataAccess da = new DataAccess(); // string sql = @"SELECT M_MEMBER.MEMBER_CODE, // M_MEMBER.TYPE_CODE, // M_MEMBER.FNAME || ' ' || M_MEMBER.MNAME || ' ' || M_MEMBER.LNAME as NAME, // 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 FNAME like '%" + paramFilterValues + "%' OR MNAME like '%" + paramFilterValues + "%' OR LNAME like '%" + paramFilterValues + "%'"; string sql = @"SELECT MEMBER_CODE, M_MEMBER.TYPE_CODE, M_MEMBER.FNAME || ' ' || M_MEMBER.MNAME || ' ' || M_MEMBER.LNAME as NAME, CITY_CODE, REGISTERED_BY, REGISTERED_DATE, M_MEMBER.ACTIVE_FLAG, M_MEMBER.REASON_FOR_INACTIVE, INACTIVE_DATE, INACTIVATED_BY, M_LIBRARY_MEMBER_TYPE.TYPE_DESC FROM M_MEMBER inner join M_LIBRARY_MEMBER_TYPE on M_MEMBER.TYPE_CODE =M_LIBRARY_MEMBER_TYPE.TYPE_CODE where FNAME like '%" + paramFilterValues + "%' OR MNAME like '%" + paramFilterValues + "%' OR LNAME like '%" + paramFilterValues + "%'"; return da.ExecuteDataTable(sql, CommandType.Text); }
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 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 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 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 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 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 DataTable GetMemberCardCodes(string paramFilterValues) { DataAccess da = new DataAccess(); string sql = @"SELECT MEMBER_CARD_CODE, MEMBER_CODE FROM M_MEMBER_CARD"; return da.ExecuteDataTable(sql, CommandType.Text); }
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 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 DataTable GetBookSN(string paramFilterValues) { DataAccess da = new DataAccess(); string sql = @"SELECT BOOK_CODE, BOOK_SN FROM M_BOOK_INVENTORY"; return da.ExecuteDataTable(sql, CommandType.Text); }
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 DataTable GetSources(string paramFilterValues) { DataAccess da = new DataAccess(); string sql = @"SELECT M_BOOK_SOURCE.SOURCE_TYPE_CODE, M_BOOK_SOURCE.SOURCE_DESC, M_BOOK_SOURCE.REMARKS FROM M_BOOK_SOURCE where SOURCE_DESC like '%" + paramFilterValues + "%'"; return da.ExecuteDataTable(sql, CommandType.Text); }
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 DataTable GetCategories(string paramFilterValues) { DataAccess da = new DataAccess(); string sql = @"SELECT M_BOOK_CATEGORY.CATEGORY_CODE, M_BOOK_CATEGORY.CATEGORY_DESC, M_BOOK_CATEGORY.REMARKS FROM M_BOOK_CATEGORY where category_desc like '%" + paramFilterValues + "%'"; return da.ExecuteDataTable(sql, CommandType.Text); }
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 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 DataTable GetEditions(string paramFilterValues) { DataAccess da = new DataAccess(); string sql = @"SELECT M_EDITION.EDITION_CODE, M_EDITION.EDITION_DESC, M_EDITION.REMARKS FROM M_EDITION where EDITION_DESC like '%" + paramFilterValues + "%'"; return da.ExecuteDataTable(sql, CommandType.Text); }
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 DataTable GetCardTypes(string paramFilterValues) { DataAccess da = new DataAccess(); string sql = @"SELECT M_MEMBER_CARD_TYPES.CARD_TYPE, M_MEMBER_CARD_TYPES.TYPE_DESC, M_MEMBER_CARD_TYPES.NO_OF_BOOKS_ALLOWED FROM M_MEMBER_CARD_TYPES where TYPE_DESC like '%" + paramFilterValues + "%'"; return da.ExecuteDataTable(sql, CommandType.Text); }
public static DataTable GetMemberTypes(string paramFilterValues) { DataAccess da = new DataAccess(); string sql = @"SELECT M_LIBRARY_MEMBER_TYPE.TYPE_CODE, M_LIBRARY_MEMBER_TYPE.TYPE_DESC, M_LIBRARY_MEMBER_TYPE.ACTIVE_FLAG, M_LIBRARY_MEMBER_TYPE.REASON_FOR_INACTIVE FROM M_LIBRARY_MEMBER_TYPE where TYPE_DESC like '%" + paramFilterValues + "%'"; return da.ExecuteDataTable(sql, CommandType.Text); }
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"; }