/**
*
*/
package com.cin.ejb.transactionejb;
import java.rmi.RemoteException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;
import javax.ejb.TransactionManagement;
import javax.ejb.TransactionManagementType;
import javax.naming.NamingException;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import com.cin.dto.IndustryDTO;
import com.cin.dto.InvitationDTO;
import com.cin.dto.OccupationDTO;
import com.cin.dto.StateAbbvDTO;
import com.cin.dto.StatisticsDTO;
import com.cin.dto.UserDTO;
import com.cin.dto.WageDTO;
import com.cin.dto.ZipDTO;
import com.cin.entity.Industry;
import com.cin.entity.Invitation;
import com.cin.entity.Occupation;
import com.cin.entity.Payroll;
import com.cin.entity.Stateabbv;
import com.cin.entity.Userrecord;
import com.cin.entity.Wage;
import com.cin.entity.WagePK;
import com.cin.entity.Ziptable;
import com.cin.exceptions.PersistenceException;
import com.cin.jndi.lookup.EJBLookup;
import com.cin.log.EventLogger;
import com.cin.translators.DataTranslator;
@TransactionManagement(value = TransactionManagementType.CONTAINER)
@TransactionAttribute(TransactionAttributeType.REQUIRED)
@Stateless(name="TransactionEJB",mappedName="TransactionEJB")
public class TransactionEJB implements TransactionLocal, TransactionRemote {
@javax.persistence.PersistenceContext(unitName = "census_context")
private EntityManager manager;
public InvitationDTO addInvitation(InvitationDTO invitation){
Invitation invitationEJB = new Invitation();
if(invitation.getAgent() != null ){
int ssn = invitation.getAgent().getSsn();
invitationEJB.setAgent((Userrecord) manager.find(Userrecord.class, ssn));
}
if(invitation.getCustomer() != null ){
int ssn = invitation.getCustomer().getSsn();
invitationEJB.setCustomer((Userrecord) manager.find(Userrecord.class, ssn));
}
invitationEJB.setDateCreated(invitation.getDateCreated());
persist(invitationEJB);
return new InvitationDTO(invitationEJB);
}
public List<InvitationDTO> getInvitationsByCustomer(int ssn) {
Query query = manager.createQuery(
"SELECT DISTINCT i "+
"FROM Invitation i "+
"WHERE i.customer = :customer "+
"ORDER BY i.dateCreated "
);
Object customer = manager.find(Userrecord.class, ssn);
query.setParameter("customer", customer);
List<?> result = query.getResultList();
List<InvitationDTO> invitations = new ArrayList<InvitationDTO>();
for( Object o : result){
invitations.add( new InvitationDTO((Invitation)o) );
}
return invitations;
}
public Occupation findOccupationCode(String pOccupation) {
String aQuery = "select o from Occupation o where upper(trim(o.occupation)) = '" + pOccupation.toUpperCase() + "' " ;
Query aQueryObject = manager.createQuery(aQuery);
Occupation anOccupation = (Occupation)aQueryObject.getSingleResult();
return anOccupation;
}
public Industry findIndCode(String pIndustry) {
String aQuery = "select ind from Industry ind where upper(trim(ind.industry)) = '" + pIndustry.toUpperCase() + "' " ;
Query aQueryObject = manager.createQuery(aQuery);
Industry aInd = (Industry)aQueryObject.getSingleResult();
return aInd;
}
public OccupationDTO findStabilityForOccupationCode(int pCode) {
Occupation anEntity = (Occupation) manager.find(Occupation.class, pCode);
OccupationDTO anDTO = null;
if(anEntity != null) {
anDTO = new OccupationDTO(anEntity);
}
return anDTO;
}
public IndustryDTO findStabilityForIndustryCode(int pCode) {
Industry anEntity = (Industry) manager.find(Industry.class, pCode);
IndustryDTO anDTO = null;
if(anEntity != null) {
anDTO = new IndustryDTO(anEntity);
}
return anDTO;
}
public void addPayroll(UserDTO agent, int paymentValue) {
Userrecord agentEJB = (Userrecord) manager.find(Userrecord.class, agent.getSsn());
Payroll payrollEJB = new Payroll();
payrollEJB.setAgent(agentEJB);
payrollEJB.setPaymentValue(paymentValue);
persist(payrollEJB);
}
public List<WageDTO> findAllWageDetails() {
Query query = manager.createQuery(
"SELECT w from Wage w");
List<?> result = query.getResultList();
List<WageDTO> dtoList = new ArrayList<WageDTO>( result.size() );
for(Object ejb : result){
dtoList.add(new WageDTO((Wage) ejb));
}
return dtoList;
}
public List<OccupationDTO> findAllOccupationDetails() {
List<OccupationDTO> theOccupation = null;
Query query = manager.createQuery("SELECT obj from Occupation obj");
List<?> result = query.getResultList();
DataTranslator aTranslator = new DataTranslator();
aTranslator.translateToOccupationDTO(result);
return theOccupation;
}
public int getMeanWage(WageDTO wageDTO) {
WagePK key = new WagePK();
key.setIndustrycode(wageDTO.getIndustryCode());
key.setOccupationcode(wageDTO.getOccupationCode());
Wage wage = manager.find(Wage.class, key);
if( wage == null ){
throw new RuntimeException("Requested wage info not found.");
} else {
return wage.getMeanweekwage();
}
}
public void setMeanWage(WageDTO wageDTO) {
WagePK key = new WagePK();
key.setIndustrycode(wageDTO.getIndustryCode());
key.setOccupationcode(wageDTO.getOccupationCode());
Wage wage = manager.find(Wage.class, key);
if( wage == null ){
wage = new Wage();
wage.setIndustrycode(wageDTO.getIndustryCode());
wage.setOccupationcode(wageDTO.getOccupationCode());
wage.setMeanweekwage(wageDTO.getMeanWeekWage());
persist(wage);
} else {
wage.setMeanweekwage(wageDTO.getMeanWeekWage());
update(wage);
}
}
public List<IndustryDTO> getLargestIndustries(int resultSize, String stateAbbv, String zipCode){
List<?> ejbList = queryLargestOccupationOrIndustry("industry", stateAbbv, zipCode);
List<IndustryDTO> dtoList = new ArrayList<IndustryDTO>();
int copySize = Math.min(resultSize, ejbList.size());
for( int i = 0; i < copySize; i++ ){
Industry ejb = (Industry) ejbList.get(i);
dtoList.add(new IndustryDTO( ejb) );
}
return dtoList;
}
public List<OccupationDTO> getLargestOccupations(int resultSize, String stateAbbv, String zipCode){
List<?> ejbList = queryLargestOccupationOrIndustry("occupation", stateAbbv, zipCode);
int copySize = Math.min(resultSize, ejbList.size());
List<OccupationDTO> dtoList = new ArrayList<OccupationDTO>(copySize);
for( int i = 0; i < copySize; i++ ){
Occupation ejb = (Occupation) ejbList.get(i);
dtoList.add(new OccupationDTO( ejb) );
}
return dtoList;
}
private List<?> queryLargestOccupationOrIndustry(String jobField, String stateAbbv, String zipCode){
StringBuilder query = new StringBuilder();
boolean whereClouse = false;
query.append("SELECT o ");
query.append("FROM Userrecord u, IN( u.job ) j, IN( j."+jobField+" ) o ");
if( stateAbbv != null ){
query.append("WHERE u.zip = ANY ( SELECT z.zip FROM Ziptable z WHERE UPPER(z.statename) = UPPER(:statename) ) ");
whereClouse = true;
}
if( zipCode != null ){
if( whereClouse ){
query.append("AND ");
} else {
query.append("WHERE ");
}
query.append("u.zip = :zipcode ");
whereClouse = true;
}
query.append("GROUP BY o ");
query.append("ORDER BY COUNT( j ) DESC ");
Query q = manager.createQuery(query.toString());
if( stateAbbv != null ){
q.setParameter("statename", stateAbbv);
}
if( zipCode != null ){
q.setParameter("zipcode", zipCode);
}
List<?> ejbList = q.getResultList();
return ejbList;
}
public List<String> getPrincipleStates(int resultSize, Integer industryCode, Integer occupationCode ){
StringBuilder query = new StringBuilder();
query.append("SELECT z.statename ");
query.append("FROM Ziptable z, Userrecord u, IN( u.job ) j, IN( j.occupation ) o, IN( j.industry ) i ");
query.append("WHERE z.zip = u.zip ");
if( industryCode != null ){
query.append("AND i.industrycode = :industryCode ");
}
if( occupationCode != null ){
query.append("AND o.occupationcode = :occupationCode ");
}
query.append("GROUP BY z.statename ");
query.append("ORDER BY COUNT( j ) DESC ");
Query q = manager.createQuery(query.toString());
if( industryCode != null ){
q.setParameter("industryCode", industryCode);
}
if( occupationCode != null ){
q.setParameter("occupationCode", occupationCode);
}
List<?> resultList = q.getResultList();
int copySize = Math.min(resultSize, resultList.size());
List<String> stateList = new ArrayList<String>(copySize);
for( int i = 0; i < copySize; i++){
stateList.add( (String) resultList.get(i) );
}
return stateList;
}
public List<UserDTO> findForAvgWage(StatisticsDTO pStatistics) throws PersistenceException {
String aState = pStatistics.getState();
String aOccCategory = pStatistics.getOccCategory();
String aIndCategory = pStatistics.getIndCategory();
int aOccCode = pStatistics.getOccCode();
int aIndCode = pStatistics.getIndustryCode();
StringBuilder query = new StringBuilder();
String stateAbbv = null;
TransactionRemote aTxnRemote = null;
boolean isCriteria = false;
query.append("SELECT u ");
query.append("FROM Userrecord u, IN( u.job ) j, IN( j.occupation ) o, IN( j.industry ) i ");
try {
aTxnRemote = EJBLookup.getInstance().getTransactionEJB();
if(aState != null) {
stateAbbv = aTxnRemote.getStateAbbv(aState);
}
}
catch(RemoteException pException) {
EventLogger.getInstance().log(Level.SEVERE, pException.getMessage());
}
catch(NamingException pException) {
EventLogger.getInstance().log(Level.SEVERE, pException.getMessage());
}
if( stateAbbv != null ) {
query.append(" WHERE u.zip = ANY ( SELECT z.zip FROM Ziptable z WHERE UPPER(z.statename) = UPPER('"+ stateAbbv + "') ) ");
isCriteria = true;
}
if((aOccCategory != null && !aOccCategory.equals("")) || aOccCode > 0) {
if(aOccCategory != null) {
Occupation anEntity = aTxnRemote.findOccupationCode(aOccCategory);
if(anEntity != null) {
aOccCode = anEntity.getOccupationcode();
}
}
if((aIndCategory != null && !aIndCategory.equals("")) || aIndCode > 0) {
if(aIndCategory != null) {
Industry indEntity = aTxnRemote.findIndCode(aIndCategory);
if(indEntity != null)
aIndCode = indEntity.getIndustrycode();
}
}
if(isCriteria) {
query.append(" AND ");
}
else {
query.append(" WHERE ");
}
query.append(" i.industrycode = " + aIndCode);
query.append(" AND o.occupationcode = " + aOccCode);
isCriteria = true;
}
Query queryObj = manager.createQuery(query.toString());
DataTranslator aTranslator = new DataTranslator();
List<UserDTO> result = aTranslator.translateToUserDTO(queryObj.getResultList());
return result;
}
public List<StateAbbvDTO> findAllStates() {
Query query = manager.createQuery(
"SELECT state from StateAbbv state");
DataTranslator aTranslator = new DataTranslator();
List<StateAbbvDTO> result = aTranslator.translateToState(query.getResultList());
return result;
}
public ZipDTO findStateForZip(int pZip) {
Query query = manager.createQuery(
"SELECT z from Ziptable z where z.zip = " + pZip);
Ziptable aZipEntity = (Ziptable)query.getSingleResult();
ZipDTO zip = new ZipDTO(aZipEntity);
return zip;
}
public void remove(Object obj) {
Object mergedObj = manager.merge(obj);
manager.remove(mergedObj);
}
public void persist(Object obj) {
manager.persist(obj);
manager.refresh(obj);
}
public void update(Object obj) {
manager.merge(obj);
manager.flush();
}
public List<ZipDTO> findZipForState(String pState) throws PersistenceException {
// TODO: this can't be right
String stateAbbv = this.getStateAbbv(pState);
String aQuery = "select o from Ziptable o where o.statename = :statename " ;
Query aQueryObject = manager.createQuery(aQuery);
aQueryObject.setParameter("statename", stateAbbv);
List<?> theZipDetails = aQueryObject.getResultList();
DataTranslator aTranslator = new DataTranslator();
return aTranslator.translateToZipDTO(theZipDetails);
}
public String getStateAbbv(String pStateName) throws PersistenceException {
Stateabbv entity = null;
try {
EventLogger.getInstance().info("State is " + pStateName);
String aQuery = "select s from Stateabbv s where upper(trim(s.name)) = '" + pStateName.toUpperCase() + "' " ;
Query aQueryObject = manager.createQuery(aQuery);
entity = (Stateabbv)aQueryObject.getSingleResult();
}
catch(Exception pException) {
throw new PersistenceException("State does not exist");
}
return entity.getAbbv();
}
public List<UserDTO> findUserForZip(int pZip) {
String aQuery = "select o from Userrecord o where o.zip = '" + pZip + "' " ;
Query aQueryObject = manager.createQuery(aQuery);
DataTranslator aTranslator = new DataTranslator();
List<?> theUserDetails = aQueryObject.getResultList();
return aTranslator.translateToUserDTO(theUserDetails);
}
public boolean zipCodeExists(String zip){
String queryStirng = "select COUNT( z ) from Ziptable z where z.zip = :zip ";
Query query = manager.createQuery(queryStirng);
query.setParameter("zip", zip);
Long count = (Long) query.getSingleResult();
return count > 0;
}
}