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