/*
* The Kuali Financial System, a comprehensive financial management system for higher education.
*
* Copyright 2005-2014 The Kuali Foundation
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
package org.kuali.kfs.vnd.batch.dataaccess;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.kuali.kfs.vnd.businessobject.DebarredVendorMatch;
import org.kuali.kfs.vnd.businessobject.VendorAddress;
import org.kuali.kfs.vnd.document.service.VendorService;
import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
import org.springframework.jdbc.support.rowset.SqlRowSet;
public class DebarredVendorDaoJdbc extends PlatformAwareDaoBaseJdbc implements DebarredVendorDao {
private VendorService vendorService;
private DebarredVendorMatchDao debarredVendorMatchDao;
@Override
public List<DebarredVendorMatch> match() {
String active = "dtl.DOBJ_MAINT_CD_ACTV_IND = 'Y'";
String joinDtl = " INNER JOIN pur_vndr_dtl_t dtl";
String joinExcl = " INNER JOIN PUR_VNDR_EXCL_MT excl";
String where = " WHERE " + active;
String eplsFields = "excl.VNDR_EXCL_ID, excl.VNDR_EXCL_LOAD_DT, excl.VNDR_EXCL_NM, excl.VNDR_EXCL_LN1_ADDR, excl.VNDR_EXCL_LN2_ADDR, excl.VNDR_EXCL_CTY_NM" +
", excl.VNDR_EXCL_ST_CD, excl.VNDR_EXCL_PRVN_NM, excl.VNDR_EXCL_ZIP_CD, excl.VNDR_EXCL_OTHR_NM, excl.VNDR_EXCL_DESC_TXT";
String selectName = "SELECT dtl.VNDR_HDR_GNRTD_ID, dtl.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID";
String fromName = " FROM pur_vndr_dtl_t dtl";
String name = filter("dtl.VNDR_NM", "., ");
String eplsName = filter("excl.VNDR_EXCL_NM", "., ");
String onName = " ON " + compare(name, eplsName, false); // use = to compare
String sqlName = selectName + fromName + joinExcl + onName + where;
String selectAlias = "SELECT als.VNDR_HDR_GNRTD_ID, als.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID";
String fromAlias = " FROM pur_vndr_alias_t als";
String onAlsDtl = " ON als.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND als.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID";
String alias = filter("als.VNDR_ALIAS_NM", "., ");
String eplsAlias = filter("excl.VNDR_EXCL_NM", "., ");
String onAlias = " ON " + compare(alias, eplsAlias, false); // use = to compare
String sqlAlias = selectAlias + fromAlias + joinDtl + onAlsDtl + joinExcl + onAlias + where;
String selectAddr = "SELECT addr.VNDR_HDR_GNRTD_ID, addr.VNDR_DTL_ASND_ID, " + eplsFields + " , addr.VNDR_ADDR_GNRTD_ID";
String fromAddr = " FROM pur_vndr_addr_t addr";
String onAddrDtl = " ON addr.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND addr.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID";
//
String addr1 = filter("addr.VNDR_LN1_ADDR", ".,# ");
String eplsAddr1 = filter("excl.VNDR_EXCL_LN1_ADDR", ".,# ");
String cmpAddr1 = compare(addr1, eplsAddr1, true); // use LIKE to compare
//
String city = filter("addr.VNDR_CTY_NM", "., ");
String eplsCity = filter("excl.VNDR_EXCL_CTY_NM", "., ");
String cmpCity = compare(city, eplsCity, false); // use = to compare
//
String state = "upper(addr.VNDR_ST_CD)";
String eplsState = "upper(excl.VNDR_EXCL_ST_CD)";
String cmpState = compare(state, eplsState, false); // use = to compare
//
String zip = filter("addr.VNDR_ZIP_CD", "-");
String eplsZip = filter("excl.VNDR_EXCL_ZIP_CD", "-");
String cmpZip = compare(zip, eplsZip, false); // use = to compare
String fullZip = "length(addr.VNDR_ZIP_CD) > 5";
//
String onAddr = " ON (" + cmpAddr1 + " OR " + cmpZip + " AND " + fullZip + ") AND " + cmpCity + " AND " + cmpState;
String sqlAddr = selectAddr + fromAddr + joinDtl + onAddrDtl + joinExcl + onAddr + where;
String max = ", MAX(VNDR_ADDR_GNRTD_ID)";
String selectFields = "VNDR_HDR_GNRTD_ID, VNDR_DTL_ASND_ID, VNDR_EXCL_ID, VNDR_EXCL_LOAD_DT, VNDR_EXCL_NM, VNDR_EXCL_LN1_ADDR, VNDR_EXCL_LN2_ADDR, VNDR_EXCL_CTY_NM" +
", VNDR_EXCL_ST_CD, VNDR_EXCL_PRVN_NM, VNDR_EXCL_ZIP_CD, VNDR_EXCL_OTHR_NM, VNDR_EXCL_DESC_TXT";
String select = "SELECT " + selectFields + max;
String subqr = sqlName + " UNION " + sqlAlias + " UNION " + sqlAddr;
String from = " FROM (" + subqr + ")";
String group = " GROUP BY " + selectFields;
String sql = select + from + group;
List<DebarredVendorMatch> matches = new ArrayList<DebarredVendorMatch>();
try {
SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql);
DebarredVendorMatch match;
while(rs.next()) {
match = new DebarredVendorMatch();
match.setVendorHeaderGeneratedIdentifier(new Integer(rs.getInt(1)));
match.setVendorDetailAssignedIdentifier(new Integer(rs.getInt(2)));
match.setLoadDate(rs.getDate(4));
match.setName(rs.getString(5));
match.setAddress1(rs.getString(6));
match.setAddress2(rs.getString(7));
match.setCity(rs.getString(8));
match.setState(rs.getString(9));
match.setProvince(rs.getString(10));
match.setZip(rs.getString(11));
match.setAliases(rs.getString(12));
match.setDescription(rs.getString(13));
match.setAddressGeneratedId(rs.getLong(14));
// didn't find a matched address, search for best one
if (match.getAddressGeneratedId() == 0) {
match.setAddressGeneratedId(getMatchAddressId(match));
}
DebarredVendorMatch oldMatch = debarredVendorMatchDao.getPreviousVendorExcludeConfirmation(match);
if (oldMatch == null) {
// store the match only if an exact old match doesn't exist
match.setConfirmStatusCode("U"); // status - Unprocessed
matches.add(match);
}
}
} catch (Exception e) {
// if exception occurs, return empty results
throw new RuntimeException(e);
}
return matches;
}
/**
* Gets the addressGeneratedId of the vendor address that matches best with the address of the
* EPLS debarred vendor in the specified vendor exclude match.
* If no address matches, returns the default address for IU campus.
*/
protected long getMatchAddressId(DebarredVendorMatch match) {
long bestid = 0;
long defaultId = 0;
int maxPriority = 0;
List<VendorAddress> addresses = vendorService.getVendorDetail(match.getVendorHeaderGeneratedIdentifier(),
match.getVendorDetailAssignedIdentifier()).getVendorAddresses();
if (addresses == null ) {
return bestid;
}
for (VendorAddress address : addresses) {
if (address.isVendorDefaultAddressIndicator()) {
defaultId = address.getVendorAddressGeneratedIdentifier();
}
//each condition satisfied will increase the priority score for this address
int priority = 0;
String vendorAddr1 = StringUtils.replaceChars(address.getVendorLine1Address(), ".,# ", "");
String eplsAddr1 = StringUtils.replaceChars(match.getAddress1(), ".,# ", "");
if (StringUtils.equalsIgnoreCase(vendorAddr1, eplsAddr1)) {
priority++;
}
String vendorCity = StringUtils.replaceChars(address.getVendorCityName(), "., ", "");
String eplsCity = StringUtils.replaceChars(match.getCity(), "., ", "");
if (StringUtils.equalsIgnoreCase(vendorCity, eplsCity)) {
priority++;
}
if (StringUtils.equalsIgnoreCase(address.getVendorStateCode(), match.getState())) {
priority++;
}
String vendorZip = StringUtils.substring(address.getVendorZipCode(), 0, 5);
String eplsZip = StringUtils.substring(match.getZip(), 0, 5);
if (StringUtils.equals(vendorZip, eplsZip)) {
priority++;
}
if (priority >= maxPriority) {
bestid = address.getVendorAddressGeneratedIdentifier();
maxPriority = priority;
}
}
if (bestid == 0) {
bestid = defaultId;
}
return bestid;
}
protected String filter(String field, String charset) {
// add upper function
String upper = "upper(" + field + ")";
if (charset == null)
return upper;
// add replace functions layer by layer to filter out the chars in the charset one by one
String replace = upper;
char[] chararr = charset.toCharArray();
for (char ch : chararr) {
// replace with empty string
replace = "replace(" + replace + ", '" + ch + "', '')";
}
return replace;
}
protected String compare(String fieldl, String fieldr, boolean useLike) {
String cmpstr = "";
// whether neither field is null
String notnulll = fieldl + " IS NOT NULL";
String notnullr = fieldr + " IS NOT NULL";
if (useLike) {
// whether one of the two fields is substring of the other
String like1 = notnullr + " AND " + fieldl + " LIKE '%'||" + fieldr + "||'%'";
String like2 = notnulll + " AND " + fieldr + " LIKE '%'||" + fieldl + "||'%'";
cmpstr += "(" + like1 + " OR " + like2 + ")"; // put () around the 'OR' to ensure integrity
}
else {
// whether the two fields equal
cmpstr = notnulll + " AND " + fieldl + " = " + fieldr;
}
return cmpstr;
}
/**
* Gets the vendorService attribute.
* @return Returns the vendorService.
*/
public VendorService getVendorService() {
return vendorService;
}
/**
* Sets the vendorService attribute value.
* @param vendorService The vendorService to set.
*/
public void setVendorService(VendorService vendorService) {
this.vendorService = vendorService;
}
/**
* Gets the debarredVendorMatchDao attribute.
* @return Returns the debarredVendorMatchDao.
*/
public DebarredVendorMatchDao getDebarredVendorMatchDao() {
return debarredVendorMatchDao;
}
/**
* Sets the debarredVendorMatchDao attribute value.
* @param debarredVendorMatchDao The debarredVendorMatchDao to set.
*/
public void setDebarredVendorMatchDao(DebarredVendorMatchDao debarredVendorMatchDao) {
this.debarredVendorMatchDao = debarredVendorMatchDao;
}
}