Пример #1
0
 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());
     }
 }
Пример #2
0
 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";
 }
Пример #3
0
 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";
 }
Пример #4
0
 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";
 }
Пример #5
0
 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";
 }
Пример #6
0
        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;
        }
Пример #7
0
 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";
 }
Пример #8
0
        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);
        }
Пример #9
0
 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";
 }
Пример #10
0
        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";
        }
Пример #11
0
 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";
 }
Пример #12
0
 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";
 }
Пример #13
0
 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";
 }
Пример #14
0
 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();
     }
 }
Пример #15
0
 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";
 }
Пример #16
0
        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);
        }
Пример #17
0
 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";
 }
Пример #18
0
        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";
        }
Пример #19
0
        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);
        }
Пример #20
0
 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";
 }
Пример #21
0
 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);
 }
Пример #22
0
 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";
 }
Пример #23
0
 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);
 }
Пример #24
0
 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";
 }
Пример #25
0
 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";
 }
Пример #26
0
 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);
 }
Пример #27
0
 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";
 }
Пример #28
0
 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);
 }
Пример #29
0
 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);
 }
Пример #30
0
 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";
 }