/**
* Copyright 1999-2009 The Pegadi Team
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.pegadi.server.sources;
import org.pegadi.server.SourceServer;
import org.pegadi.sources.*;
import org.pegadi.sqlsearch.SearchTerm;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import javax.sql.DataSource;
import java.sql.*;
import java.util.List;
public class SourceServerImpl implements SourceServer {
private final Logger log = LoggerFactory.getLogger(getClass());
private JdbcTemplate template;
public void setDataSource(DataSource dataSource) {
template = new JdbcTemplate(dataSource);
}
public int saveSource(Source source) {
if(source.getID()==-1)
return insertSource(source);
else {
updateSource(source);
return -1;
}
}
public void deleteSource(Source source) {
template.update("DELETE FROM Source_person WHERE id=?", source.getID());
template.update("DELETE FROM Source_Phone_Number WHERE sourceID=?", source.getID());
template.update("DELETE FROM Source_Contact WHERE sourceID=?", source.getID());
template.update("DELETE FROM Source_Category_MemberShip WHERE sourceID=?", source.getID());
log.info("Deleted source with id={}", source.getID());
}
private int insertSource(final Source source) {
GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
template.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
PreparedStatement stmt = conn.prepareStatement("INSERT INTO Source_person (name,email,url,address,postnumber,postaddress,position,notes,organization) VALUES (?,?,?,?,?,?,?,?,?)", new String[]{"ID"});
stmt.setString(1, source.getName());
stmt.setString(2, source.getEmail());
stmt.setString(3, source.getURL());
stmt.setString(4, source.getAddress());
stmt.setString(5, source.getPostNumber());
stmt.setString(6, source.getPostAddress());
stmt.setString(7, source.getPosition());
stmt.setString(8, source.getNotes());
stmt.setInt(9, source.getOrganizationID());
return stmt;
}
}, generatedKeyHolder);
int id = generatedKeyHolder.getKey().intValue();
source.setID(id);
updatePhoneNumbers(source);
updateContacts(source);
return id;
}
private void updateSource(final Source source) {
template.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
PreparedStatement stmt = conn.prepareStatement("UPDATE Source_person SET name=?,email=?,url=?,address=?,postnumber=?,postaddress=?,position=?,notes=?,organization=? WHERE ID=?");
stmt.setString(1, source.getName());
stmt.setString(2, source.getEmail());
stmt.setString(3, source.getURL());
stmt.setString(4, source.getAddress());
stmt.setString(5, source.getPostNumber());
stmt.setString(6, source.getPostAddress());
stmt.setString(7, source.getPosition());
stmt.setString(8, source.getNotes());
stmt.setInt(9, source.getOrganizationID());
stmt.setInt(10, source.getID());
return stmt;
}
});
updatePhoneNumbers(source);
updateContacts(source);
}
public void updatePhoneNumbers(final Source source) {
template.update("DELETE FROM Source_Phone_Number WHERE sourceID=?", source.getID());
for(PhoneNumber number : source.getPhoneNumbers()){
template.update("INSERT INTO Source_Phone_Number (type,number,description,sourceID) VALUES (?,?,?,?)",
number.getType().name(), number.getNumber(), number.getDescription(), source.getID() );
}
}
public void updateContacts(Source source) {
final List<Contact> contacts = source.getContacts();
template.update("DELETE FROM Source_Contact WHERE sourceID=?", source.getID());
for (Contact contact : contacts) {
template.update("INSERT INTO Source_Contact (sourceID,time_of_contact,notes) VALUES (?,?,?)", contact.getSourceID(), new Date(contact.getDate().getTime()), contact.getNotes());
}
log.info("Inserting {} contacts for source {}", contacts.size(), source.getID());
}
public List<Source> getSourcesBySearchTerm(SearchTerm term) {
return template.query(term.getQuery("Source_person.ID"), new RowMapper<Source>() {
@Override
public Source mapRow(ResultSet rs, int rowNum) throws SQLException {
return getSourceByID(rs.getInt(1));
}
});
}
public Source getSourceByID(int ID) {
return template.queryForObject("SELECT ID,name,email,url,address,postnumber,postaddress,position,notes,organization FROM Source_person WHERE ID=?", new RowMapper<Source>() {
@Override
public Source mapRow(ResultSet rs, int rowNum) throws SQLException {
return getSourceFromResultSet(rs);
}
}, ID);
}
public List<Category> getSourceCategories() {
return template.query("SELECT ID,name from Source_Category ORDER BY name", new RowMapper<Category>() {
@Override
public Category mapRow(ResultSet rs, int rowNum) throws SQLException {
return getCategoryFromResultSet(rs);
}
});
}
public List<Contact> getContactsBySource(int sourceID) {
return template.query("SELECT ID,sourceID,time_of_contact,notes FROM Source_Contact WHERE sourceID=? ORDER BY time_of_contact DESC,ID", new RowMapper<Contact>() {
@Override
public Contact mapRow(ResultSet rs, int rowNum) throws SQLException {
return getContactFromResultSet(rs);
}
}, sourceID);
}
public List<Category> getCategoriesBySource(int sourceID) {
return template.query("SELECT Source_Category.ID,Source_Category.name FROM Source_person,Source_Category,Source_Category_MemberShip WHERE Source_person.ID=Source_Category_MemberShip.sourceID AND Source_Category.ID=Source_Category_MemberShip.categoryID AND Source_person.ID=?", new RowMapper<Category>() {
@Override
public Category mapRow(ResultSet rs, int rowNum) throws SQLException {
return getCategoryFromResultSet(rs);
}
}, sourceID);
}
public void updateSourceCategoryMemberships(int sourceID, List<Category> members) {
deleteAllCategoryMemberships(sourceID);
for (Category member : members) insertCategoryMembership(sourceID, member.getID());
}
private void deleteAllCategoryMemberships(int sourceID) {
template.update("DELETE FROM Source_Category_MemberShip WHERE categoryID=?", sourceID);
}
private void insertCategoryMembership(int sourceID,int categoryID) {
template.update("INSERT INTO Source_Category_MemberShip (sourceID,categoryID) VALUES (?,?)", sourceID, categoryID);
}
private Contact getContactFromResultSet(ResultSet rs) throws SQLException {
Contact c = new Contact();
c.setID(rs.getInt(1));
c.setSourceID(rs.getInt(2));
c.setDate(rs.getDate(3));
c.setNotes(rs.getString(4));
return c;
}
private Category getCategoryFromResultSet(ResultSet rs) throws SQLException {
Category c = new Category();
c.setID(rs.getInt(1));
c.setName(rs.getString(2));
return c;
}
private Source getSourceFromResultSet(ResultSet rs) throws SQLException {
Source source = new Source();
source.setID(rs.getInt(1));
source.setName(rs.getString(2));
source.setEmail(rs.getString(3));
source.setURL(rs.getString(4));
source.setAddress(rs.getString(5));
source.setPostNumber(rs.getString(6));
source.setPostAddress(rs.getString(7));
source.setPosition(rs.getString(8));
source.setNotes(rs.getString(9));
source.setOrganizationID(rs.getInt(10));
source.setPhoneNumbers(getPhoneNumbersBySource(source.getID()));
List<Contact> conts = getContactsBySource(source.getID());
if(conts.size() != 0) // No need to flood the server log with useless data
log.info("Setting {} contacts for source {}", conts.size(), source.getName());
source.setContacts(conts);
return source;
}
private List<PhoneNumber> getPhoneNumbersBySource(int ID) {
return template.query("SELECT Source_Phone_Number.ID, Source_Phone_Number.type, Source_Phone_Number.number, Source_Phone_Number.description FROM Source_Phone_Number WHERE sourceID = ? ORDER BY Source_Phone_Number.ID", new RowMapper<PhoneNumber>() {
@Override
public PhoneNumber mapRow(ResultSet rs, int rowNum) throws SQLException {
PhoneNumberType t = PhoneNumberType.valueOf(rs.getString(2));
PhoneNumber p = new PhoneNumber(t,rs.getString(3),rs.getString(4));
p.setID(rs.getInt(1));
return p;
}
}, ID);
}
public void addSourceCategory(Category newCategory) {
template.update("INSERT INTO Source_Category (name) VALUES (?)", newCategory.getName());
log.info("Added a new category: {}", newCategory.getName());
}
public void updateSourceCategory(Category category) {
template.update("UPDATE Source_Category SET name=? WHERE ID=?", category.getID(), category.getName());
log.info("Updated category: {}, {}", category.getID(), category.getName());
}
public void deleteSourceCategory(Category category) {
template.update("DELETE FROM Source_Category WHERE ID=?", category.getID());
template.update("DELETE FROM Source_Category_MemberShip WHERE categoryID=?", category.getID());
log.info("Deleted category: '{}' id={}", category.getName(), category.getID());
}
}