/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package model.database;
import model.object.Subscription;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.logging.Level;
import resource.log.ProjectLogger;
/**
*
* @author Yoldark34 <yoldark@gmail.com>
*/
public class SubscriptionMapper extends AbstractMapper {
/**
* get all subscriptions from the database
*
* @return
* @throws SQLException
* @throws ClassNotFoundException
*/
public ArrayList<Subscription> getAllSubscriptions() throws SQLException, ClassNotFoundException {
DbConnection adapter = DbConnection.getDbConnection();
adapter.executeSelectQuery("Select * from " + DataBaseElements.SUBSCRIPTION);
return (ArrayList<Subscription>) adapter.getModelsFromRequest(this);
}
/**
* Insert subscription if id == -1 or update subscription instead
*
* @param subscription
* @return int number of rows
*/
public int save(Subscription subscription) {
int nbRows = 0;
int idResult = -1;
String query;
if (subscription.getId() != -1) {
query = "UPDATE `" + DataBaseElements.SUBSCRIPTION + "` SET ";
//query += "`"+DataBaseElements.SUBSCRIPTION_ID+"` = '"+subscription.getId()+"',";Can't be updated because used in where
query += "`" + DataBaseElements.SUBSCRIPTION_IDPRICE + "` = '" + subscription.getIdPrice() + "',";
query += "`" + DataBaseElements.SUBSCRIPTION_IDNEMOUSER + "` = '" + subscription.getIdNemoUser() + "',";
if (subscription.getAmount() != -1) {
query += "`" + DataBaseElements.SUBSCRIPTION_AMOUNT + "` = '" + subscription.getAmount() + "',";
} else {
query += "NULL,";
}
if (subscription.getStartDate() == null) {
query += "`" + DataBaseElements.SUBSCRIPTION_STARTDATE + "` = " + subscription.getStartDate() + ",";
} else {
query += "`" + DataBaseElements.SUBSCRIPTION_STARTDATE + "` = '" + subscription.getStartDate() + "',";
}
if (subscription.getEndDate() == null) {
query += "`" + DataBaseElements.SUBSCRIPTION_ENDDATE + "` = " + subscription.getEndDate() + " ";
} else {
query += "`" + DataBaseElements.SUBSCRIPTION_ENDDATE + "` = '" + subscription.getEndDate() + "' ";
}
query += "WHERE `" + DataBaseElements.SUBSCRIPTION_ID + "` = '" + subscription.getId() + "';";
try {
DbConnection adapter = DbConnection.getDbConnection();
nbRows = adapter.executeUpdateQuery(query);
} catch (Exception e) {
}
return nbRows;
} else {
query = "INSERT INTO " + DataBaseElements.SUBSCRIPTION + " (";
//query += "`" + DataBaseElements.SUBSCRIPTION_ID + "`,";
query += "`" + DataBaseElements.SUBSCRIPTION_IDPRICE + "`,";
query += "`" + DataBaseElements.SUBSCRIPTION_IDNEMOUSER + "`,";
query += "`" + DataBaseElements.SUBSCRIPTION_AMOUNT + "`,";
query += "`" + DataBaseElements.SUBSCRIPTION_STARTDATE + "`,";
query += "`" + DataBaseElements.SUBSCRIPTION_ENDDATE + "` ";
query += ") VALUES (";
//query += "'" + subscription.getId() + "',";
query += "'" + subscription.getIdPrice() + "',";
query += "'" + subscription.getIdNemoUser() + "',";
if (subscription.getAmount() != -1) {
query += "'" + subscription.getAmount() + "',";
} else {
query += "NULL,";
}
if (subscription.getStartDate() == null) {
query += subscription.getStartDate() + ",";
} else {
query += "'" + subscription.getStartDate() + "',";
}
if (subscription.getEndDate() == null) {
query += subscription.getEndDate() + " ";
} else {
query += "'" + subscription.getEndDate() + "' ";
}
query += ")";
try {
DbConnection adapter = DbConnection.getDbConnection();
idResult = adapter.executeInsertQuery(query);
} catch (Exception e) {
}
return idResult;
}
}
/**
* get subscription from an id
*
* @param idSubscription
* @return Subscription
*/
public Subscription getSubscription(int idSubscription) {
String query;
Subscription result = new Subscription();
query = "SELECT ";
query += "*";
query += " FROM ";
query += DataBaseElements.SUBSCRIPTION + " " + DataBaseElements.ALIAS_SUBSCRIPTION;
query += " WHERE ";
query += DataBaseElements.ALIAS_SUBSCRIPTION + "." + DataBaseElements.SUBSCRIPTION_ID + " = '" + idSubscription + "'";
try {
DbConnection adapter = DbConnection.getDbConnection();
adapter.executeSelectQuery(query);
result = (Subscription) adapter.getModelFromRequest(this);
} catch (SQLException | ClassNotFoundException ex) {
ProjectLogger.log(this, Level.SEVERE, "Erreur d'exécution de la requête de la fonction getUniquePriceDurationUnitForRent", ex);
}
return result;
}
/**
* get all subscriptions from a nemouser id
*
* @param idNemoUser
* @return ArrayList<Subscription>
*/
public ArrayList<Subscription> getSubscriptionsFromNemoUser(int idNemoUser) {
String query;
ArrayList<Subscription> results = new ArrayList<>();
query = "SELECT ";
query += "*";
query += " FROM ";
query += DataBaseElements.SUBSCRIPTION + " " + DataBaseElements.ALIAS_SUBSCRIPTION;
query += " WHERE ";
query += DataBaseElements.ALIAS_SUBSCRIPTION + "." + DataBaseElements.SUBSCRIPTION_IDNEMOUSER + " = '" + idNemoUser + "'";
try {
DbConnection adapter = DbConnection.getDbConnection();
adapter.executeSelectQuery(query);
results = (ArrayList<Subscription>) adapter.getModelsFromRequest(this);
} catch (SQLException | ClassNotFoundException ex) {
ProjectLogger.log(this, Level.SEVERE, "Erreur d'exécution de la requête de la fonction getUniquePriceDurationUnitForRent", ex);
}
return results;
}
@Override
public Object populateModel(ResultSet row) throws SQLException {
Subscription obj = new Subscription();
if (this.hasColumn(DataBaseElements.SUBSCRIPTION_ID, row)) {
obj.setId(row.getInt(DataBaseElements.SUBSCRIPTION_ID));
}
if (this.hasColumn(DataBaseElements.SUBSCRIPTION_IDPRICE, row)) {
obj.setIdPrice(row.getInt(DataBaseElements.SUBSCRIPTION_IDPRICE));
}
if (this.hasColumn(DataBaseElements.SUBSCRIPTION_IDNEMOUSER, row)) {
obj.setIdNemoUser(row.getInt(DataBaseElements.SUBSCRIPTION_IDNEMOUSER));
}
if (this.hasColumn(DataBaseElements.SUBSCRIPTION_AMOUNT, row)) {
obj.setAmount(row.getFloat(DataBaseElements.SUBSCRIPTION_AMOUNT));
}
if (this.hasColumn(DataBaseElements.SUBSCRIPTION_STARTDATE, row)) {
obj.setStartDate(row.getTimestamp(DataBaseElements.SUBSCRIPTION_STARTDATE));
}
if (this.hasColumn(DataBaseElements.SUBSCRIPTION_ENDDATE, row)) {
obj.setEndDate(row.getTimestamp(DataBaseElements.SUBSCRIPTION_ENDDATE));
}
return obj;
}
@Override
Object getEmptyModel() {
return new Subscription();
}
/**
* close a subscription when there is no bikes rented for it.
*
* @param idSubscription
* @param today
*/
void closeSubscriptionIfFinish(int idSubscription, Timestamp today) {
Subscription sub = this.getSubscription(idSubscription);
BikeUsageMapper bum = new BikeUsageMapper();
int bikeUsageQuantity = bum.getNumberOfRentedBikes(sub.getIdNemoUser(), sub.getStartDate());
if (bikeUsageQuantity == 0) {
sub.setEndDate(today);
this.save(sub);
}
}
}