public List<License> findAll() throws SQLException {
try (Connection conn = ds.getConnection()) {
try (PreparedStatement statement = conn.prepareStatement("SELECT * FROM License")) {
try (ResultSet resultSet = statement.executeQuery()) {
List<License> licenses = new ArrayList<>();
while (resultSet.next()) {
int productId = resultSet.getInt("productId");
Product productById = productRepository.getProductById(productId);
int releaseId = resultSet.getInt("releaseId");
Release release = null;
if (releaseId != 0) release = releaseRepository.getReleaseById(releaseId);
int customerId = resultSet.getInt("customerId");
Customer customerById = customerRepository.getCustomerById(customerId);
Integer licenseTypeId = getInteger(resultSet, "licenseTypeId");
LicenseType licenseType =
licenseTypeId != null ? getLicenseTypeById(conn, licenseTypeId) : null;
License license =
getLicense(resultSet, productById, customerById, release, licenseType);
licenses.add(license);
}
return licenses;
}
}
}
}
public List<License> findExpiringLicenses() throws SQLException {
try (Connection connection = ds.getConnection()) {
try (PreparedStatement stmnt =
connection.prepareStatement(
"Select *, DATEDIFF('DAY', CURRENT_DATE, validTill),CURRENT_DATE from License where DATEDIFF('DAY', CURRENT_DATE, validTill) < 31 AND DATEDIFF('DAY', CURRENT_DATE, validTill) > 0")) {
try (ResultSet resultSet = stmnt.executeQuery()) {
List<License> expiringLicenses = new ArrayList<>();
while (resultSet.next()) {
int productId = resultSet.getInt("productId");
Product productById = productRepository.getProductById(productId);
int releaseId = resultSet.getInt("releaseId");
Release release = null;
if (releaseId != 0) release = releaseRepository.getReleaseById(releaseId);
int customerId = resultSet.getInt("customerId");
Customer customerById = customerRepository.getCustomerById(customerId);
Integer licenseTypeId = getInteger(resultSet, "licenseTypeId");
LicenseType licenseType =
licenseTypeId != null ? getLicenseTypeById(connection, licenseTypeId) : null;
License license =
getLicense(resultSet, productById, customerById, release, licenseType);
expiringLicenses.add(license);
}
return expiringLicenses;
}
}
}
}
public License findById(int id) throws SQLException {
try (Connection connection = ds.getConnection()) {
try (PreparedStatement statement =
connection.prepareStatement("SELECT * FROM License WHERE id = ?;")) {
statement.setInt(1, id);
try (ResultSet resultSet = statement.executeQuery()) {
if (!resultSet.next()) {
throw new SQLException("Ei leitud ühtegi litsentsi, kus rida on id-ga " + id);
}
int releaseId = resultSet.getInt("releaseId");
Release release = null;
if (releaseId != 0) release = releaseRepository.getReleaseById(releaseId);
Integer licenseTypeId = getInteger(resultSet, "licenseTypeId");
LicenseType licenseType =
licenseTypeId != null ? getLicenseTypeById(connection, licenseTypeId) : null;
return getLicense(
resultSet,
productRepository.getProductById(resultSet.getInt("productId")),
customerRepository.getCustomerById(resultSet.getInt("customerId")),
release,
licenseType);
}
}
}
}
public List<License> findByKeyword(String kword, StateHelper sh) throws SQLException {
try (Connection conn = ds.getConnection()) {
try (PreparedStatement statement =
conn.prepareStatement(
"SELECT * FROM License LEFT JOIN Customer ON License.customerId = Customer.id LEFT JOIN Release ON License.releaseId = Release.id LEFT JOIN Product ON License.productId = Product.id LEFT JOIN LicenseType ON License.licenseTypeId = LicenseType.id WHERE (License.contractNumber LIKE (LOWER(CONCAT('%',?,'%'))) OR ( License.productId = Product.id AND LOWER(Product.name) LIKE LOWER(CONCAT('%',?,'%'))) OR ( License.releaseId = Release.id AND Release.version LIKE (CONCAT('%',?,'%')) ) OR ( License.customerId = Customer.id AND LOWER(Customer.organizationName) LIKE LOWER(CONCAT('%',?,'%')) ) OR License.validFrom LIKE (CONCAT('%',?,'%')) OR License.validTill LIKE (CONCAT('%',?,'%')) OR ( License.licenseTypeId = LicenseType.id AND LicenseType.name LIKE (CONCAT('%',?,'%')) )) AND ((License.state = 1 AND ?=true) OR (License.state = 2 AND ?=true) OR (License.state = 3 AND ?=true) OR (License.state = 4 AND ?=true) OR (License.state = 5 AND ?=true) OR (License.state = 6 AND ?=true));")) {
statement.setString(1, kword);
statement.setString(2, kword);
statement.setString(3, kword);
statement.setString(4, kword);
statement.setString(5, kword);
statement.setString(6, kword);
statement.setString(7, kword);
statement.setBoolean(8, sh.getRejected());
statement.setBoolean(9, sh.getNegotiated());
statement.setBoolean(10, sh.getWaiting_for_signature());
statement.setBoolean(11, sh.getActive());
statement.setBoolean(12, sh.getExpiration_nearing());
statement.setBoolean(13, sh.getTerminated());
System.out.println(statement);
try (ResultSet resultSet = statement.executeQuery()) {
List<License> licenses = new ArrayList<>();
while (resultSet.next()) {
int productId = resultSet.getInt("productId");
Product productById = productRepository.getProductById(productId);
int releaseId = resultSet.getInt("releaseId");
Release release = null;
if (releaseId != 0) release = releaseRepository.getReleaseById(releaseId);
int customerId = resultSet.getInt("customerId");
Customer customerById = customerRepository.getCustomerById(customerId);
Integer licenseTypeId = getInteger(resultSet, "licenseTypeId");
LicenseType licenseType =
licenseTypeId != null ? getLicenseTypeById(conn, licenseTypeId) : null;
License license =
getLicense(resultSet, productById, customerById, release, licenseType);
licenses.add(license);
}
return licenses;
}
}
}
}
