001: /*
002: * $Id: ZipSalesServices.java,v 1.17 2004/02/17 17:50:59 ajzeneski Exp $
003: *
004: * Copyright (c) 2001-2003 The Open For Business Project - www.ofbiz.org
005: *
006: * Permission is hereby granted, free of charge, to any person obtaining a
007: * copy of this software and associated documentation files (the "Software"),
008: * to deal in the Software without restriction, including without limitation
009: * the rights to use, copy, modify, merge, publish, distribute, sublicense,
010: * and/or sell copies of the Software, and to permit persons to whom the
011: * Software is furnished to do so, subject to the following conditions:
012: *
013: * The above copyright notice and this permission notice shall be included
014: * in all copies or substantial portions of the Software.
015: *
016: * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
017: * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
018: * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
019: * IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
020: * CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT
021: * OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR
022: * THE USE OR OTHER DEALINGS IN THE SOFTWARE.
023: */
024: package org.ofbiz.order.thirdparty.zipsales;
025:
026: import org.ofbiz.service.DispatchContext;
027: import org.ofbiz.service.ServiceUtil;
028: import org.ofbiz.entity.GenericDelegator;
029: import org.ofbiz.entity.GenericValue;
030: import org.ofbiz.entity.GenericEntityException;
031: import org.ofbiz.entity.util.EntityUtil;
032: import org.ofbiz.datafile.DataFile;
033: import org.ofbiz.datafile.DataFileException;
034: import org.ofbiz.datafile.Record;
035: import org.ofbiz.datafile.RecordIterator;
036: import org.ofbiz.base.util.*;
037: import org.ofbiz.security.Security;
038:
039: import java.util.*;
040: import java.net.URL;
041: import java.net.MalformedURLException;
042: import java.net.URI;
043: import java.net.URISyntaxException;
044: import java.sql.Timestamp;
045: import java.text.SimpleDateFormat;
046: import java.text.ParseException;
047: import java.text.DecimalFormat;
048: import java.io.File;
049:
050: /**
051: * Zip-Sales Database Services
052: *
053: * @author <a href="mailto:jaz@ofbiz.org">Andy Zeneski</a>
054: * @version $Revision: 1.17 $
055: * @since 3.0
056: */
057: public class ZipSalesServices {
058:
059: public static final String module = ZipSalesServices.class
060: .getName();
061: public static final String dataFile = "org/ofbiz/order/thirdparty/zipsales/ZipSalesTaxTables.xml";
062: public static final String flatTable = "FlatTaxTable";
063: public static final String ruleTable = "FreightRuleTable";
064:
065: // number formatting
066: private static String curFmtStr = UtilProperties.getPropertyValue(
067: "general.properties", "currency.decimal.format", "##0.00");
068: private static DecimalFormat curFormat = new DecimalFormat(
069: curFmtStr);
070:
071: // date formatting
072: private static SimpleDateFormat dateFormat = new SimpleDateFormat(
073: "yyyyMMdd");
074:
075: // import table service
076: public static Map importFlatTable(DispatchContext dctx, Map context) {
077: GenericDelegator delegator = dctx.getDelegator();
078: Security security = dctx.getSecurity();
079: GenericValue userLogin = (GenericValue) context
080: .get("userLogin");
081: String taxFileLocation = (String) context
082: .get("taxFileLocation");
083: String ruleFileLocation = (String) context
084: .get("ruleFileLocation");
085:
086: // do security check
087: if (!security.hasPermission("SERVICE_INVOKE_ANY", userLogin)) {
088: return ServiceUtil
089: .returnError("You do not have permission to load tax tables");
090: }
091:
092: // get a now stamp (we'll use 2000-01-01)
093: Timestamp now = parseDate("20000101", null);
094:
095: // load the data file
096: DataFile tdf = null;
097: try {
098: tdf = DataFile.makeDataFile(UtilURL.fromResource(dataFile),
099: flatTable);
100: } catch (DataFileException e) {
101: Debug.logError(e, module);
102: return ServiceUtil
103: .returnError("Unable to read ZipSales DataFile");
104: }
105:
106: // locate the file to be imported
107: URL tUrl = UtilURL.fromResource(taxFileLocation);
108: if (tUrl == null) {
109: return ServiceUtil
110: .returnError("Unable to locate tax file at location : "
111: + taxFileLocation);
112: }
113:
114: RecordIterator tri = null;
115: try {
116: tri = tdf.makeRecordIterator(tUrl);
117: } catch (DataFileException e) {
118: Debug.logError(e, module);
119: return ServiceUtil
120: .returnError("Problem getting the Record Iterator");
121: }
122: if (tri != null) {
123: while (tri.hasNext()) {
124: Record entry = null;
125: try {
126: entry = tri.next();
127: } catch (DataFileException e) {
128: Debug.logError(e, module);
129: }
130: GenericValue newValue = delegator.makeValue(
131: "ZipSalesTaxLookup", null);
132: // PK fields
133: newValue.set("zipCode", entry.getString("zipCode")
134: .trim());
135: newValue
136: .set("stateCode",
137: entry.get("stateCode") != null ? entry
138: .getString("stateCode").trim()
139: : "_NA_");
140: newValue.set("city", entry.get("city") != null ? entry
141: .getString("city").trim() : "_NA_");
142: newValue.set("county",
143: entry.get("county") != null ? entry.getString(
144: "county").trim() : "_NA_");
145: newValue.set("fromDate", parseDate(entry
146: .getString("effectiveDate"), now));
147:
148: // non-PK fields
149: newValue.set("countyFips", entry.get("countyFips"));
150: newValue.set("countyDefault", entry
151: .get("countyDefault"));
152: newValue.set("generalDefault", entry
153: .get("generalDefault"));
154: newValue.set("insideCity", entry.get("insideCity"));
155: newValue.set("geoCode", entry.get("geoCode"));
156: newValue.set("stateSalesTax", entry
157: .get("stateSalesTax"));
158: newValue.set("citySalesTax", entry.get("citySalesTax"));
159: newValue.set("cityLocalSalesTax", entry
160: .get("cityLocalSalesTax"));
161: newValue.set("countySalesTax", entry
162: .get("countySalesTax"));
163: newValue.set("countyLocalSalesTax", entry
164: .get("countyLocalSalesTax"));
165: newValue.set("comboSalesTax", entry
166: .get("comboSalesTax"));
167: newValue.set("stateUseTax", entry.get("stateUseTax"));
168: newValue.set("cityUseTax", entry.get("cityUseTax"));
169: newValue.set("cityLocalUseTax", entry
170: .get("cityLocalUseTax"));
171: newValue.set("countyUseTax", entry.get("countyUseTax"));
172: newValue.set("countyLocalUseTax", entry
173: .get("countyLocalUseTax"));
174: newValue.set("comboUseTax", entry.get("comboUseTax"));
175:
176: try {
177: delegator.createOrStore(newValue);
178: } catch (GenericEntityException e) {
179: Debug.logError(e, module);
180: return ServiceUtil
181: .returnError("Error writing record(s) to the database");
182: }
183:
184: // console log
185: Debug.log(newValue.get("zipCode") + "/"
186: + newValue.get("stateCode") + "/"
187: + newValue.get("city") + "/"
188: + newValue.get("county") + "/"
189: + newValue.get("fromDate"));
190: }
191: }
192:
193: // load the data file
194: DataFile rdf = null;
195: try {
196: rdf = DataFile.makeDataFile(UtilURL.fromResource(dataFile),
197: ruleTable);
198: } catch (DataFileException e) {
199: Debug.logError(e, module);
200: return ServiceUtil
201: .returnError("Unable to read ZipSales DataFile");
202: }
203:
204: // locate the file to be imported
205: URL rUrl = UtilURL.fromResource(ruleFileLocation);
206: if (rUrl == null) {
207: return ServiceUtil
208: .returnError("Unable to locate rule file from location : "
209: + ruleFileLocation);
210: }
211:
212: RecordIterator rri = null;
213: try {
214: rri = rdf.makeRecordIterator(rUrl);
215: } catch (DataFileException e) {
216: Debug.logError(e, module);
217: return ServiceUtil
218: .returnError("Problem getting the Record Iterator");
219: }
220: if (rri != null) {
221: while (rri.hasNext()) {
222: Record entry = null;
223: try {
224: entry = rri.next();
225: } catch (DataFileException e) {
226: Debug.logError(e, module);
227: }
228: if (entry.get("stateCode") != null
229: && entry.getString("stateCode").length() > 0) {
230: GenericValue newValue = delegator.makeValue(
231: "ZipSalesRuleLookup", null);
232: // PK fields
233: newValue.set("stateCode",
234: entry.get("stateCode") != null ? entry
235: .getString("stateCode").trim()
236: : "_NA_");
237: newValue.set("city",
238: entry.get("city") != null ? entry
239: .getString("city").trim() : "_NA_");
240: newValue.set("county",
241: entry.get("county") != null ? entry
242: .getString("county").trim()
243: : "_NA_");
244: newValue.set("fromDate", parseDate(entry
245: .getString("effectiveDate"), now));
246:
247: // non-PK fields
248: newValue.set("idCode",
249: entry.get("idCode") != null ? entry
250: .getString("idCode").trim() : null);
251: newValue
252: .set(
253: "taxable",
254: entry.get("taxable") != null ? entry
255: .getString("taxable")
256: .trim()
257: : null);
258: newValue.set("shipCond",
259: entry.get("shipCond") != null ? entry
260: .getString("shipCond").trim()
261: : null);
262:
263: try {
264: // using storeAll as an easy way to create/update
265: delegator.storeAll(UtilMisc.toList(newValue));
266: } catch (GenericEntityException e) {
267: Debug.logError(e, module);
268: return ServiceUtil
269: .returnError("Error writing record(s) to the database");
270: }
271:
272: // console log
273: Debug.log(newValue.get("stateCode") + "/"
274: + newValue.get("city") + "/"
275: + newValue.get("county") + "/"
276: + newValue.get("fromDate"));
277: }
278: }
279: }
280:
281: return ServiceUtil.returnSuccess();
282: }
283:
284: // tax calc service
285: public static Map flatTaxCalc(DispatchContext dctx, Map context) {
286: GenericDelegator delegator = dctx.getDelegator();
287: List itemProductList = (List) context.get("itemProductList");
288: List itemAmountList = (List) context.get("itemAmountList");
289: List itemShippingList = (List) context.get("itemShippingList");
290: Double orderShippingAmount = (Double) context
291: .get("orderShippingAmount");
292: GenericValue shippingAddress = (GenericValue) context
293: .get("shippingAddress");
294:
295: // flatTaxCalc only uses the Zip + City from the address
296: String stateProvince = shippingAddress
297: .getString("stateProvinceGeoId");
298: String postalCode = shippingAddress.getString("postalCode");
299: String city = shippingAddress.getString("city");
300:
301: // setup the return lists.
302: List orderAdjustments = new ArrayList();
303: List itemAdjustments = new ArrayList();
304:
305: // check for a valid state/province geo
306: String validStates = UtilProperties.getPropertyValue(
307: "zipsales.properties", "zipsales.valid.states");
308: if (validStates != null && validStates.length() > 0) {
309: List stateSplit = StringUtil.split(validStates, "|");
310: if (!stateSplit.contains(stateProvince)) {
311: Map result = ServiceUtil.returnSuccess();
312: result.put("orderAdjustments", orderAdjustments);
313: result.put("itemAdjustments", itemAdjustments);
314: return result;
315: }
316: }
317:
318: try {
319: // loop through and get per item tax rates
320: for (int i = 0; i < itemProductList.size(); i++) {
321: GenericValue product = (GenericValue) itemProductList
322: .get(i);
323: Double itemAmount = (Double) itemAmountList.get(i);
324: Double shippingAmount = (Double) itemShippingList
325: .get(i);
326: itemAdjustments.add(getItemTaxList(delegator, product,
327: postalCode, city, itemAmount.doubleValue(),
328: shippingAmount.doubleValue(), false));
329: }
330: if (orderShippingAmount.doubleValue() > 0) {
331: List taxList = getItemTaxList(delegator, null,
332: postalCode, city, 0.00, orderShippingAmount
333: .doubleValue(), false);
334: orderAdjustments.addAll(taxList);
335: }
336: } catch (GeneralException e) {
337: return ServiceUtil.returnError(e.getMessage());
338: }
339:
340: Map result = ServiceUtil.returnSuccess();
341: result.put("orderAdjustments", orderAdjustments);
342: result.put("itemAdjustments", itemAdjustments);
343: return result;
344: }
345:
346: private static List getItemTaxList(GenericDelegator delegator,
347: GenericValue item, String zipCode, String city,
348: double itemAmount, double shippingAmount, boolean isUseTax)
349: throws GeneralException {
350: List adjustments = new ArrayList();
351:
352: // check the item for tax status
353: if (item != null && item.get("taxable") != null
354: && "N".equals(item.getString("taxable"))) {
355: // item not taxable
356: return adjustments;
357: }
358:
359: // lookup the records
360: List zipLookup = delegator.findByAnd("ZipSalesTaxLookup",
361: UtilMisc.toMap("zipCode", zipCode), UtilMisc
362: .toList("-fromDate"));
363: if (zipLookup == null || zipLookup.size() == 0) {
364: throw new GeneralException(
365: "The zip code entered is not valid.");
366: }
367:
368: // the filtered list
369: List taxLookup = null;
370:
371: // only do filtering if there are more then one zip code found
372: if (zipLookup != null && zipLookup.size() > 1) {
373: // first filter by city
374: List cityLookup = EntityUtil.filterByAnd(zipLookup,
375: UtilMisc.toMap("city", city.toUpperCase()));
376: if (cityLookup != null && cityLookup.size() > 0) {
377: if (cityLookup.size() > 1) {
378: // filter by county
379: List countyLookup = EntityUtil.filterByAnd(
380: taxLookup, UtilMisc.toMap("countyDefault",
381: "Y"));
382: if (countyLookup != null && countyLookup.size() > 0) {
383: // use the county default
384: taxLookup = countyLookup;
385: } else {
386: // no county default; just use the first city
387: taxLookup = cityLookup;
388: }
389: } else {
390: // just one city found; use that one
391: taxLookup = cityLookup;
392: }
393: } else {
394: // no city found; lookup default city
395: List defaultLookup = EntityUtil.filterByAnd(zipLookup,
396: UtilMisc.toMap("generalDefault", "Y"));
397: if (defaultLookup != null && defaultLookup.size() > 0) {
398: // use the default city lookup
399: taxLookup = defaultLookup;
400: } else {
401: // no default found; just use the first from the zip lookup
402: taxLookup = zipLookup;
403: }
404: }
405: } else {
406: // zero or 1 zip code found; use it
407: taxLookup = zipLookup;
408: }
409:
410: // get the first one
411: GenericValue taxEntry = null;
412: if (taxLookup != null && taxLookup.size() > 0) {
413: taxEntry = (GenericValue) taxLookup.iterator().next();
414: }
415:
416: if (taxEntry == null) {
417: Debug.logWarning("No tax entry found for : " + zipCode
418: + " / " + city + " - " + itemAmount, module);
419: return adjustments;
420: }
421:
422: String fieldName = "comboSalesTax";
423: if (isUseTax) {
424: fieldName = "comboUseTax";
425: }
426:
427: Double comboTaxRate = taxEntry.getDouble(fieldName);
428: if (comboTaxRate == null) {
429: Debug.logWarning("No Combo Tax Rate In Field " + fieldName
430: + " @ " + zipCode + " / " + city + " - "
431: + itemAmount, module);
432: return adjustments;
433: }
434:
435: // get state code
436: String stateCode = taxEntry.getString("stateCode");
437:
438: // check if shipping is exempt
439: boolean taxShipping = true;
440:
441: // look up the rules
442: List ruleLookup = null;
443: try {
444: ruleLookup = delegator.findByAnd("ZipSalesRuleLookup",
445: UtilMisc.toMap("stateCode", stateCode), UtilMisc
446: .toList("-fromDate"));
447: } catch (GenericEntityException e) {
448: Debug.logError(e, module);
449: }
450:
451: // filter out city
452: if (ruleLookup != null && ruleLookup.size() > 1) {
453: ruleLookup = EntityUtil.filterByAnd(ruleLookup, UtilMisc
454: .toMap("city", city.toUpperCase()));
455: }
456:
457: // no county captured; so filter by date
458: if (ruleLookup != null && ruleLookup.size() > 1) {
459: ruleLookup = EntityUtil.filterByDate(ruleLookup);
460: }
461:
462: if (ruleLookup != null) {
463: Iterator ruleIterator = ruleLookup.iterator();
464: while (ruleIterator.hasNext()) {
465: if (!taxShipping) {
466: // if we found an rule which passes no need to contine (all rules are ||)
467: break;
468: }
469: GenericValue rule = (GenericValue) ruleIterator.next();
470: String idCode = rule.getString("idCode");
471: String taxable = rule.getString("taxable");
472: String condition = rule.getString("shipCond");
473: if ("T".equals(taxable)) {
474: // this record is taxable
475: continue;
476: } else {
477: // except if conditions are met
478: boolean qualify = false;
479: if (condition != null && condition.length() > 0) {
480: char[] conditions = condition.toCharArray();
481: for (int i = 0; i < conditions.length; i++) {
482: switch (conditions[i]) {
483: case 'A':
484: // SHIPPING CHARGE SEPARATELY STATED ON INVOICE
485: qualify = true; // OFBiz does this by default
486: break;
487: case 'B':
488: // SHIPPING CHARGE SEPARATED ON INVOICE FROM HANDLING OR SIMILAR CHARGES
489: qualify = false; // we do not support this currently
490: break;
491: case 'C':
492: // ITEM NOT SOLD FOR GUARANTEED SHIPPED PRICE
493: qualify = false; // we don't support this currently
494: break;
495: case 'D':
496: // SHIPPING CHARGE IS COST ONLY
497: qualify = false; // we assume a handling charge is included
498: break;
499: case 'E':
500: // SHIPPED DIRECTLY TO PURCHASER
501: qualify = true; // this is true, unless gifts do not count?
502: break;
503: case 'F':
504: // SHIPPED VIA COMMON CARRIER
505: qualify = true; // best guess default
506: break;
507: case 'G':
508: // SHIPPED VIA CONTRACT CARRIER
509: qualify = false; // best guess default
510: break;
511: case 'H':
512: // SHIPPED VIA VENDOR EQUIPMENT
513: qualify = false; // best guess default
514: break;
515: case 'I':
516: // SHIPPED F.O.B. ORIGIN
517: qualify = false; // no clue
518: break;
519: case 'J':
520: // SHIPPED F.O.B. DESTINATION
521: qualify = false; // no clue
522: break;
523: case 'K':
524: // F.O.B. IS PURCHASERS OPTION
525: qualify = false; // no clue
526: break;
527: case 'L':
528: // SHIPPING ORIGINATES OR TERMINATES IN DIFFERENT STATES
529: qualify = true; // not determined at order time, no way to know
530: break;
531: case 'M':
532: // PROOF OF VENDOR ACTING AS SHIPPING AGENT FOR PURCHASER
533: qualify = false; // no clue
534: break;
535: case 'N':
536: // SHIPPED FROM VENDOR LOCATION
537: qualify = true; // sure why not
538: break;
539: case 'O':
540: // SHIPPING IS BY PURCHASER OPTION
541: qualify = false; // most online stores require shipping
542: break;
543: case 'P':
544: // CREDIT ALLOWED FOR SHIPPING CHARGE PAID BY PURCHASER TO CARRIER
545: qualify = false; // best guess default
546: break;
547: default:
548: break;
549: }
550: }
551: }
552:
553: if (qualify) {
554: if (isUseTax) {
555: if (idCode.indexOf('U') > 0) {
556: taxShipping = false;
557: }
558: } else {
559: if (idCode.indexOf('S') > 0) {
560: taxShipping = false;
561: }
562: }
563: }
564: }
565: }
566: }
567:
568: double taxableAmount = itemAmount;
569: if (taxShipping) {
570: //Debug.log("Taxing shipping", module);
571: taxableAmount += shippingAmount;
572: } else {
573: Debug.log("Shipping is not taxable", module);
574: }
575:
576: // calc tax amount
577: double taxRate = comboTaxRate.doubleValue();
578: double taxCalc = taxableAmount * taxRate;
579:
580: // format the number
581: Double taxAmount = new Double(formatCurrency(taxCalc));
582: adjustments.add(delegator.makeValue("OrderAdjustment", UtilMisc
583: .toMap("amount", taxAmount, "orderAdjustmentTypeId",
584: "SALES_TAX", "comments", new Double(taxRate)
585: .toString(), "description",
586: "Sales Tax (" + stateCode + ")")));
587:
588: return adjustments;
589: }
590:
591: // formatting methods
592: private static Timestamp parseDate(String dateString,
593: Timestamp useWhenNull) {
594: Timestamp ts = null;
595: if (dateString != null) {
596: try {
597: ts = new Timestamp(dateFormat.parse(dateString)
598: .getTime());
599: } catch (ParseException e) {
600: Debug.logError(e, module);
601: }
602: }
603:
604: if (ts != null) {
605: return ts;
606: } else {
607: return useWhenNull;
608: }
609: }
610:
611: private static String formatCurrency(double currency) {
612: return curFormat.format(currency);
613: }
614: }
|