public void ReturnBook(Model.Transaction oMTransaction)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SP_RETURN_BOOK";
ddq.AddParamer("@BOOK_ID", SqlDbType.VarChar, oMTransaction.BOOK_ID);
ddq.AddParamer("@BORROWER_ID", SqlDbType.VarChar, oMTransaction.PERSON_ID);
ddq.AddParamer("@BOOK_NO", SqlDbType.VarChar, oMTransaction.BOOK_NO);
ddq.AddParamer("@MODIFIED_DATE", SqlDbType.VarChar, oMTransaction.MODIFIED_DATE);
ddq.AddParamer("@MODIFIED_BY", SqlDbType.VarChar, oMTransaction.MODIFIED_BY);
ddq.AddParamer("@TOTAL_AMOUNT", SqlDbType.Decimal, oMTransaction.TOTAL_AMOUNT);
ddq.AddParamer("@REMARKS", SqlDbType.VarChar, oMTransaction.REMARKS);
ddq.AddParamer("@STATUS", SqlDbType.VarChar, oMTransaction.STATUS);
ddq.ExecuteNonQuery(CommandType.StoredProcedure);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateBookDetails(Model.Transaction oMTransaction)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SP_UPDATE_BOOK_DETAILS";
ddq.AddParamer("@ID", SqlDbType.VarChar, oMTransaction.BOOK_ID);
ddq.AddParamer("@BOOK_NO", SqlDbType.VarChar, oMTransaction.BOOK_NO);
ddq.AddParamer("@TITLE", SqlDbType.VarChar, oMTransaction.TITLE);
ddq.AddParamer("@SUBJECT", SqlDbType.VarChar, oMTransaction.SUBJECT);
ddq.AddParamer("@CATEGORY", SqlDbType.VarChar, oMTransaction.CATEGORY);
ddq.AddParamer("@AUTHOR", SqlDbType.VarChar, oMTransaction.AUTHOR);
ddq.AddParamer("@PUBLISH_DATE", SqlDbType.VarChar, oMTransaction.PUBLISH_DATE);
ddq.AddParamer("@LOCATION", SqlDbType.VarChar, oMTransaction.LOCATION);
ddq.AddParamer("@BOOK_PRICE", SqlDbType.VarChar, oMTransaction.BOOK_PRICE);
ddq.AddParamer("@RENT_PRICE", SqlDbType.VarChar, oMTransaction.RENT_PRICE);
ddq.AddParamer("@ISBN_NUMBER", SqlDbType.VarChar, oMTransaction.ISBN_NUMBER);
ddq.AddParamer("@MODIFIED_DATE", SqlDbType.VarChar, oMTransaction.MODIFIED_DATE);
ddq.AddParamer("@MODIFIED_BY", SqlDbType.VarChar, oMTransaction.MODIFIED_BY);
ddq.AddParamer("@REMARKS", SqlDbType.VarChar, oMTransaction.REMARKS);
ddq.AddParamer("@STATUS", SqlDbType.VarChar, oMTransaction.STATUS);
ddq.ExecuteNonQuery(CommandType.StoredProcedure);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateBorrower(Model.Borrower oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SP_UPDATE_BORROWER";
ddq.AddParamer("@BORROWER_ID", SqlDbType.VarChar, oData.PERSON_ID);
ddq.AddParamer("@FIRST_NAME", SqlDbType.VarChar, oData.FIRST_NAME);
ddq.AddParamer("@MIDDLE_NAME", SqlDbType.VarChar, oData.MIDDLE_NAME);
ddq.AddParamer("@LAST_NAME", SqlDbType.VarChar, oData.LAST_NAME);
ddq.AddParamer("@DOB", SqlDbType.VarChar, oData.DOB);
ddq.AddParamer("@AGE", SqlDbType.VarChar, oData.AGE);
ddq.AddParamer("@CONTACT_NO", SqlDbType.VarChar, oData.CONTACT_NO);
ddq.AddParamer("@ADDRESS", SqlDbType.VarChar, oData.ADDRESS);
ddq.AddParamer("@MODIFIED_DATE", SqlDbType.VarChar, oData.MODIFIED_DATE);
ddq.AddParamer("@MODIFIED_BY", SqlDbType.VarChar, oData.MODIFIED_BY);
ddq.AddParamer("@STATUS", SqlDbType.VarChar, oData.STATUS);
ddq.AddParamer("@PROFILE_PIC", SqlDbType.VarChar, oData.PROFILE_PIC);
ddq.ExecuteNonQuery(CommandType.StoredProcedure);
}
catch (Exception ex)
{
throw ex;
}
}
public void RequestBook(Model.Transaction oMTransaction)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "SP_REQUEST_BOOK";
ddq.AddParamer("@ID", SqlDbType.VarChar, oMTransaction.BOOK_ID);
ddq.AddParamer("@BORROWER_ID", SqlDbType.VarChar, oMTransaction.PERSON_ID);
ddq.AddParamer("@BOOK_NO", SqlDbType.VarChar, oMTransaction.BOOK_NO);
ddq.AddParamer("@TITLE", SqlDbType.VarChar, oMTransaction.TITLE);
ddq.AddParamer("@SUBJECT", SqlDbType.VarChar, oMTransaction.SUBJECT);
ddq.AddParamer("@CATEGORY", SqlDbType.VarChar, oMTransaction.CATEGORY);
ddq.AddParamer("@AUTHOR", SqlDbType.VarChar, oMTransaction.AUTHOR);
ddq.AddParamer("@PUBLISH_DATE", SqlDbType.VarChar, oMTransaction.PUBLISH_DATE);
ddq.AddParamer("@ISBN_NUMBER", SqlDbType.VarChar, oMTransaction.ISBN_NUMBER);
ddq.AddParamer("@LOCATION", SqlDbType.VarChar, oMTransaction.LOCATION);
ddq.AddParamer("@BOOK_PRICE", SqlDbType.VarChar, oMTransaction.BOOK_PRICE);
ddq.AddParamer("@RENT_PRICE", SqlDbType.VarChar, oMTransaction.RENT_PRICE);
ddq.AddParamer("@DAYS_BORROWED", SqlDbType.VarChar, oMTransaction.TOTAL_DAYS);
ddq.AddParamer("@ADDED_DATE", SqlDbType.VarChar, oMTransaction.ADDED_DATE);
ddq.AddParamer("@ADDED_BY", SqlDbType.VarChar, oMTransaction.ADDED_BY);
ddq.AddParamer("@STATUS", SqlDbType.VarChar, oMTransaction.STATUS);
ddq.ExecuteNonQuery(CommandType.StoredProcedure);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateCategory(Model.Location oData)
{
try
{
scb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = scb.ConnectionString;
ddq.CommandText = "UPDATE TBL_LOCATION SET LOCATION = '" + oData.LOCATION + "', STATUS = '" + oData.STATUS + "' WHERE [ID] = '" + oData.LOCATION_ID + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
}
}
public void UpdateGeneratedKey(string sElectionCode)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Update tbl_VotersKey set Status = 'INACTIVE' Where ELECTION_CODE = '" + sElectionCode + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertLocation(Model.Location oData)
{
try
{
scb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = scb.ConnectionString;
ddq.CommandText = "INSERT INTO TBL_LOCATION (LOCATION,STATUS) VALUES ('" + oData.LOCATION + "','" + oData.STATUS + "')";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
}
}
public void InsertKey(Model.GenerateKey oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Insert Into tbl_VotersKey (VOTERS_ID,GENERATED_KEY,DATE_ADDED,ELECTION_CODE) Values ('" + oData.VOTERS_ID + "','" + oData.GENERATED_KEY + "','" + oData.DATED_ADDED + "','" + oData.ELECTION_CODE + "')";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateCandidate(Model.Candidate oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Update tbl_Candidates set POSITION_ID = '" + oData._Position.ID + "',PARTY_ID = '" + oData._Party.ID + "',PROFILE_PIC = '" + oData.PROFILE_PIC + "',ELECTION_CODE = '" + oData.ELECTION_CODE + "' where CANDIDATE_ID = '" + oData.UNIQUE_ID + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void ArchiveUser(string sUsername)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Update tbl_User set Status = 'INACTIVE' where Username = '******'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateStudent(Model.Student oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Update tbl_Students set First_Name = '" + oData.FIRST_NAME + "',Middle_Name = '" + oData.MIDDLE_NAME + "',Last_Name = '" + oData.LAST_NAME + "',DOB = '" + oData.DOB + "',AGE = '" + oData.AGE + "',COURSE = '" + oData.COURSE + "',SECTION = '" + oData.SECTION + "',ADDRESS = '" + oData.ADDRESS + "',Contact_No = '" + oData.CONTACT_NO + "',Modified_Date = '" + DateTime.Now.ToShortDateString() + "' where STUDENT_ID = '" + oData.UNIQUE_ID + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertStudent(Model.Student oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Insert Into tbl_Students (Student_ID,First_Name,Middle_Name,Last_Name,DOB,Age,Course,Section,Address,Contact_No,Date_Added) Values ('" + oData.UNIQUE_ID + "','" + oData.FIRST_NAME + "','" + oData.MIDDLE_NAME + "','" + oData.LAST_NAME + "','" + oData.DOB + "','" + oData.AGE + "','" + oData.COURSE + "','" + oData.SECTION + "','" + oData.ADDRESS + "','" + oData.CONTACT_NO + "','" + DateTime.Now.ToShortDateString() + "')";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertAuthor(Model.Author oData)
{
try
{
scb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = scb.ConnectionString;
ddq.CommandText = "INSERT INTO TBL_AUTHOR (FIRST_NAME,MIDDLE_NAME,LAST_NAME) VALUES ('" + oData.FIRST_NAME + "','" + oData.MIDDLE_NAME + "','" + oData.LAST_NAME + "')";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdatePosition(Model.Position oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Update tbl_Position set Status = '" + oData.STATUS + "', Position = '" + oData.POSITION + "' Where ID = '" + oData.ID + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertPosition(Model.Position oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Insert Into tbl_Position (Position,Status) Values ('" + oData.POSITION + "', '" + oData.STATUS + "')";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateCategory(Model.Category oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "UPDATE TBL_CATEGORY SET CATEGORY = '" + oData.CATEGORY + "', STATUS = '" + oData.STATUS + "' WHERE [ID] = '" + oData.CATEGORY_ID + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertSchedule(Model.VotingSchedule oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Insert Into tbl_VotingSchedule (DATE_START,DATE_END,TIME_START,TIME_END) Values ('" + oData.DATE_START + "','" + oData.DATE_END + "','" + oData.TIME_START + "','" + oData.TIME_END + "')";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void ARCHIVE_CANDIDATE(Model.Student oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "UDPATE TBL_CANDIDATES SET [STATUS] = 'INACTIVE' WHERE CANDIDATE_ID = '" + oData.UNIQUE_ID + "' AND ELECTION_CODE = '" + oData.ELECTION_CODE + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateRole(Model.Role oData)
{
try
{
scb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = scb.ConnectionString;
ddq.CommandText = "UPDATE TBL_ROLE SET ROLE = '" + oData.ROLE + "', STATUS = '" + oData.STATUS + "' WHERE [ID] = '" + oData.ID + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertRole(Model.Role oData)
{
try
{
scb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = scb.ConnectionString;
ddq.CommandText = "INSERT INTO TBL_ROLE (ROLE,STATUS) VALUES ('" + oData.ROLE + "','" + oData.STATUS + "')";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void DeleteUnsettledRequestTransaction(DateTime dTFrom, DateTime dTTo)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "DELETE FROM TBL_BORROWERREQUEST WHERE [STATUS] = 'REQUEST' AND ADDED_DATE BETWEEN '" + dTFrom.ToString("yyyy-MM-dd") + "' AND '" + dTTo.ToString("yyyy-MM-dd") + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertCategory(Model.Category oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "INSERT INTO TBL_CATEGORY (CATEGORY,STATUS ) VALUES ('" + oData.CATEGORY + "','" + oData.STATUS + "')";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void DeleteStudents(string sID)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Update tbl_Students set Status = 'INACTIVE' where Student_ID = '" + sID + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdatePolicy(Model.Policy oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "UPDATE TBL_POLICY SET DUE_PENALTY = '" + oData.DUE_INTEREST + "',LOST_DAMAGE_INTEREST = '" + oData.LOST_DAMAGE_INTEREST + "',DAYS_LIMIT = '" + oData.DAYS_LIMIT + "',BOOK_LIMIT = '" + oData.BOOK_LIMIT + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateAuthor(Model.Author oData)
{
try
{
scb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = scb.ConnectionString;
ddq.CommandText = "UPDATE TBL_AUTHOR SET FIRST_NAME = '" + oData.FIRST_NAME + "', MIDDLE_NAME = '" + oData.MIDDLE_NAME + "', LAST_NAME = '" + oData.LAST_NAME + "',STATUS = '" + oData.STATUS + "' WHERE ID = '" + oData.PERSON_ID + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertPolicy(Model.Policy oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "INSERT INTO TBL_POLICY (DUE_PENALTY,LOST_DAMAGE_INTERST,DAYS_LIMIT,BOOK_LIMIT) VALUES ('" + oData.DUE_INTEREST + "','" + oData.LOST_DAMAGE_INTEREST + "','" + oData.DAYS_LIMIT + "','" + oData.BOOK_LIMIT + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateSchedule(Model.VotingSchedule oData)
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Update tbl_VotingSchedule set DATE_START = '" + oData.DATE_START + "',DATE_END = '" + oData.DATE_END + "', TIME_START = '" + oData.TIME_START + "',TIME_END = '" + oData.TIME_END + "',STATUS = '" + oData.STATUS + "'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void DropDatabase()
{
try
{
osb.ConnectionString = sMasterDBConnection;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "DROP DATABASE iVotingSystem";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void RestoreDatabase(String sPath)
{
try
{
osb.ConnectionString = sMasterDBConnection;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "USE MASTER RESTORE Database iVotingSystem FROM DISK ='" + sPath + "' WITH REPLACE;";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateScheduleStatus()
{
try
{
osb.ConnectionString = sConnectionString;
ddq = new DatabaseQuery.DBQuery();
ddq.ConnectionString = osb.ConnectionString;
ddq.CommandText = "Update tbl_VotingSchedule set Status = 'CLOSED'";
ddq.ExecuteNonQuery(CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
}