Source code

001/*
002 * Copyright 2011 The Kuali Foundation.
003 *
004 * Licensed under the Educational Community License, Version 1.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl1.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016package org.kuali.ole.vnd.batch.dataaccess;
017
018import java.util.ArrayList;
019import java.util.List;
020
021import org.apache.commons.lang.StringUtils;
022import org.kuali.ole.vnd.businessobject.DebarredVendorMatch;
023import org.kuali.ole.vnd.businessobject.VendorAddress;
024import org.kuali.ole.vnd.document.service.VendorService;
025import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
026import org.springframework.jdbc.support.rowset.SqlRowSet;
027
028public class DebarredVendorDaoJdbc extends  PlatformAwareDaoBaseJdbc implements DebarredVendorDao {
029    private VendorService vendorService;
030    private DebarredVendorMatchDao debarredVendorMatchDao;
031
032    @Override
033    public List<DebarredVendorMatch> match() {
034        String active = "dtl.DOBJ_MAINT_CD_ACTV_IND = 'Y'";
035        String joinDtl = " INNER JOIN pur_vndr_dtl_t dtl";
036        String joinExcl = " INNER JOIN PUR_VNDR_EXCL_MT excl";
037        String where = " WHERE " + active;
038        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" +
039                        ", 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";
040
041        String selectName = "SELECT dtl.VNDR_HDR_GNRTD_ID, dtl.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID";
042        String fromName = " FROM pur_vndr_dtl_t dtl";
043        String name = filter("dtl.VNDR_NM", "., ");
044        String eplsName = filter("excl.VNDR_EXCL_NM", "., ");
045        String onName = " ON " + compare(name, eplsName, false); // use = to compare
046        String sqlName = selectName + fromName + joinExcl + onName + where;
047
048        String selectAlias = "SELECT als.VNDR_HDR_GNRTD_ID, als.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID";
049        String fromAlias = " FROM pur_vndr_alias_t als";
050        String onAlsDtl = " ON als.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND als.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID";
051        String alias = filter("als.VNDR_ALIAS_NM", "., ");
052        String eplsAlias = filter("excl.VNDR_EXCL_NM", "., ");
053        String onAlias = " ON " + compare(alias, eplsAlias, false); // use = to compare
054        String sqlAlias = selectAlias + fromAlias + joinDtl + onAlsDtl + joinExcl + onAlias + where;
055
056        String selectAddr = "SELECT addr.VNDR_HDR_GNRTD_ID, addr.VNDR_DTL_ASND_ID, " + eplsFields + " , addr.VNDR_ADDR_GNRTD_ID";
057        String fromAddr = " FROM pur_vndr_addr_t addr";
058        String onAddrDtl = " ON addr.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND addr.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID";
059        //
060        String addr1 = filter("addr.VNDR_LN1_ADDR", ".,# ");
061        String eplsAddr1 = filter("excl.VNDR_EXCL_LN1_ADDR", ".,# ");
062        String cmpAddr1 = compare(addr1, eplsAddr1, true); // use LIKE to compare
063        //
064        String city = filter("addr.VNDR_CTY_NM", "., ");
065        String eplsCity = filter("excl.VNDR_EXCL_CTY_NM", "., ");
066        String cmpCity = compare(city, eplsCity, false); // use = to compare
067        //
068        String state = "upper(addr.VNDR_ST_CD)";
069        String eplsState = "upper(excl.VNDR_EXCL_ST_CD)";
070        String cmpState = compare(state, eplsState, false); // use = to compare
071        //
072        String zip = filter("addr.VNDR_ZIP_CD", "-");
073        String eplsZip = filter("excl.VNDR_EXCL_ZIP_CD", "-");
074        String cmpZip = compare(zip, eplsZip, false); // use = to compare
075        String fullZip = "length(addr.VNDR_ZIP_CD) > 5";
076        //
077        String onAddr = " ON (" + cmpAddr1 + " OR " + cmpZip + " AND " + fullZip + ") AND " + cmpCity + " AND " + cmpState;
078        String sqlAddr = selectAddr + fromAddr + joinDtl + onAddrDtl + joinExcl + onAddr + where;
079
080        String max = ", MAX(VNDR_ADDR_GNRTD_ID)";
081        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" +
082                ", VNDR_EXCL_ST_CD, VNDR_EXCL_PRVN_NM, VNDR_EXCL_ZIP_CD, VNDR_EXCL_OTHR_NM, VNDR_EXCL_DESC_TXT";
083        String select = "SELECT " + selectFields + max;
084        String subqr = sqlName + " UNION " + sqlAlias + " UNION " + sqlAddr;
085        String from = " FROM (" + subqr + ")";
086        String group = " GROUP BY " + selectFields;
087        String sql = select + from + group;
088
089
090        List<DebarredVendorMatch> matches = new ArrayList<DebarredVendorMatch>();
091        try {
092            SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql);
093            DebarredVendorMatch match;
094
095            while(rs.next()) {
096                match = new DebarredVendorMatch();
097                match.setVendorHeaderGeneratedIdentifier(new Integer(rs.getInt(1)));
098                match.setVendorDetailAssignedIdentifier(new Integer(rs.getInt(2)));
099                match.setLoadDate(rs.getDate(4));
100                match.setName(rs.getString(5));
101                match.setAddress1(rs.getString(6));
102                match.setAddress2(rs.getString(7));
103                match.setCity(rs.getString(8));
104                match.setState(rs.getString(9));
105                match.setProvince(rs.getString(10));
106                match.setZip(rs.getString(11));
107                match.setAliases(rs.getString(12));
108                match.setDescription(rs.getString(13));
109                match.setAddressGeneratedId(rs.getLong(14));
110                // didn't find a matched address, search for best one
111                if (match.getAddressGeneratedId() == 0) {
112                    match.setAddressGeneratedId(getMatchAddressId(match));
113                }
114
115                DebarredVendorMatch oldMatch = debarredVendorMatchDao.getPreviousVendorExcludeConfirmation(match);
116                if (oldMatch == null) {
117                    // store the match only if an exact old match doesn't exist
118                    match.setConfirmStatusCode("U"); // status - Unprocessed
119                    matches.add(match);
120                }
121            }
122        } catch (Exception e) {
123            // if exception occurs, return empty results
124            throw new RuntimeException(e);
125        }
126
127        return matches;
128    }
129
130    /**
131     * Gets the addressGeneratedId of the vendor address that matches best with the address of the
132     * EPLS debarred vendor in the specified vendor exclude match.
133     * If no address matches, returns the default address for IU campus.
134     */
135    protected long getMatchAddressId(DebarredVendorMatch match) {
136        long bestid = 0;
137        long defaultId = 0;
138        int maxPriority = 0;
139        List<VendorAddress> addresses = vendorService.getVendorDetail(match.getVendorHeaderGeneratedIdentifier(),
140                match.getVendorDetailAssignedIdentifier()).getVendorAddresses();
141        if (addresses == null ) {
142            return bestid;
143        }
144
145        for (VendorAddress address : addresses) {
146            if (address.isVendorDefaultAddressIndicator()) {
147                defaultId = address.getVendorAddressGeneratedIdentifier();
148            }
149            //each condition satisfied will increase the priority score for this address
150            int priority = 0;
151            String vendorAddr1 = StringUtils.replaceChars(address.getVendorLine1Address(), ".,# ", "");
152            String eplsAddr1 = StringUtils.replaceChars(match.getAddress1(), ".,# ", "");
153            if (StringUtils.equalsIgnoreCase(vendorAddr1, eplsAddr1)) {
154                priority++;
155            }
156            String vendorCity = StringUtils.replaceChars(address.getVendorCityName(), "., ", "");
157            String eplsCity = StringUtils.replaceChars(match.getCity(), "., ", "");
158            if (StringUtils.equalsIgnoreCase(vendorCity, eplsCity)) {
159                priority++;
160            }
161            if (StringUtils.equalsIgnoreCase(address.getVendorStateCode(), match.getState())) {
162                priority++;
163            }
164            String vendorZip = StringUtils.substring(address.getVendorZipCode(), 0, 5);
165            String eplsZip = StringUtils.substring(match.getZip(), 0, 5);
166            if (StringUtils.equals(vendorZip, eplsZip)) {
167                priority++;
168            }
169            if (priority >= maxPriority) {
170                bestid = address.getVendorAddressGeneratedIdentifier();
171                maxPriority = priority;
172            }
173        }
174        if (bestid == 0) {
175            bestid = defaultId;
176        }
177        return bestid;
178    }
179
180    protected String filter(String field, String charset) {
181        // add upper function
182        String upper = "upper(" + field + ")";
183        if (charset == null)
184            return upper;
185
186        // add replace functions layer by layer to filter out the chars in the charset one by one
187        String replace = upper;
188        char[] chararr = charset.toCharArray();
189        for (char ch : chararr) {
190            // replace with empty string
191            replace = "replace(" + replace + ", '" + ch + "', '')";
192        }
193        return replace;
194    }
195
196    protected String compare(String fieldl, String fieldr, boolean useLike) {
197        String cmpstr = "";
198
199        // whether neither field is null
200        String notnulll = fieldl + " IS NOT NULL";
201        String notnullr = fieldr + " IS NOT NULL";
202
203        if (useLike) {
204            // whether one of the two fields is substring of the other
205            String like1 = notnullr + " AND " + fieldl + " LIKE '%'||" + fieldr + "||'%'";
206            String like2 = notnulll + " AND " + fieldr + " LIKE '%'||" + fieldl + "||'%'";
207            cmpstr += "(" + like1 + " OR " + like2 + ")"; // put () around the 'OR' to ensure integrity
208        }
209        else {
210            // whether the two fields equal
211            cmpstr = notnulll + " AND " + fieldl + " = " + fieldr;
212        }
213
214        return cmpstr;
215    }
216
217    /**
218     * Gets the vendorService attribute.
219     * @return Returns the vendorService.
220     */
221    public VendorService getVendorService() {
222        return vendorService;
223    }
224
225    /**
226     * Sets the vendorService attribute value.
227     * @param vendorService The vendorService to set.
228     */
229    public void setVendorService(VendorService vendorService) {
230        this.vendorService = vendorService;
231    }
232
233    /**
234     * Gets the debarredVendorMatchDao attribute.
235     * @return Returns the debarredVendorMatchDao.
236     */
237    public DebarredVendorMatchDao getDebarredVendorMatchDao() {
238        return debarredVendorMatchDao;
239    }
240
241    /**
242     * Sets the debarredVendorMatchDao attribute value.
243     * @param debarredVendorMatchDao The debarredVendorMatchDao to set.
244     */
245    public void setDebarredVendorMatchDao(DebarredVendorMatchDao debarredVendorMatchDao) {
246        this.debarredVendorMatchDao = debarredVendorMatchDao;
247    }
248
249}