Menu
▾
▴
[Opentaps-dataimport-commits] [dataimport commits] r55 - in versions/0.9/trunk: entitydef script/mysql servicedef src/org/opentaps/dataimport
|
From: <cli...@gr...> - 2007-01-30 02:29:00
|
Author: cliberty
Date: 2007-01-29 18:28:57 -0800 (Mon, 29 Jan 2007)
New Revision: 55
Added:
versions/0.9/trunk/script/mysql/importProductInventoryExample.sql
Modified:
versions/0.9/trunk/entitydef/entitygroup.xml
versions/0.9/trunk/entitydef/entitymodel_product.xml
versions/0.9/trunk/servicedef/services.xml
versions/0.9/trunk/src/org/opentaps/dataimport/ProductImportServices.java
Log:
1645751: Move inventory import to dataimport
Modified: versions/0.9/trunk/entitydef/entitygroup.xml
===================================================================
--- versions/0.9/trunk/entitydef/entitygroup.xml 2007-01-29 18:45:39 UTC (rev 54)
+++ versions/0.9/trunk/entitydef/entitygroup.xml 2007-01-30 02:28:57 UTC (rev 55)
@@ -30,6 +30,7 @@
<!-- =================================== -->
<entity-group group="org.ofbiz" entity="DataImportProduct"/>
+ <entity-group group="org.ofbiz" entity="DataImportInventory"/>
<!-- =================================== -->
<!-- org.opentaps.dataimport.orders -->
Modified: versions/0.9/trunk/entitydef/entitymodel_product.xml
===================================================================
--- versions/0.9/trunk/entitydef/entitymodel_product.xml 2007-01-29 18:45:39 UTC (rev 54)
+++ versions/0.9/trunk/entitydef/entitymodel_product.xml 2007-01-30 02:28:57 UTC (rev 55)
@@ -27,7 +27,7 @@
<version>1.0</version>
<!-- ================================= -->
- <!-- org.opentaps.dataimport.customers -->
+ <!-- org.opentaps.dataimport.products -->
<!-- ================================= -->
<!-- for precise definitions of the field types see the file framework/entity/fieldtype/fieldtypeXXX.xml for your database XXX -->
@@ -58,4 +58,19 @@
<prim-key field="productId"/>
</entity>
+ <entity entity-name="DataImportInventory"
+ package-name="org.opentaps.dataimport.products"
+ title="Intermediate import entity for inventory data.">
+ <field name="productId" type="id"/>
+ <!--<field name="productName" type="description"/>-->
+ <field name="availableToPromise" type="floating-point"/>
+ <field name="onHand" type="floating-point"/>
+ <field name="inventoryValue" type="currency-amount"/>
+ <field name="processedTimestamp" type="date-time"/>
+ <prim-key field="productId"/>
+ <relation type="one" fk-name="IMPT_INV_PROD" rel-entity-name="Product">
+ <key-map field-name="productId"/>
+ </relation>
+ </entity>
+
</entitymodel>
Added: versions/0.9/trunk/script/mysql/importProductInventoryExample.sql
===================================================================
--- versions/0.9/trunk/script/mysql/importProductInventoryExample.sql 2007-01-29 18:45:39 UTC (rev 54)
+++ versions/0.9/trunk/script/mysql/importProductInventoryExample.sql 2007-01-30 02:28:57 UTC (rev 55)
@@ -0,0 +1,25 @@
+-- Example MySQL script for importing product inventory
+-- productId must exist in the Product entity
+-- availableToPromise, onHand, and inventoryValue may be positive or negative numbers
+
+
+load data local infile
+''
+ignore
+into table data_import_inventory
+fields optionally enclosed by '"'
+lines terminated by '\r'
+ignore 1 lines
+
+(
+ @productId,
+ @availableToPromise,
+ @onHand,
+ @inventoryValue
+)
+
+set
+product_id = nullif(trim(@productId),''),
+available_to_promise = nullif(trim(@availableToPromise),''),
+on_hand = nullif(trim(@onHand),''),
+inventory_value = nullif(trim(@inventoryValue),'')
Property changes on: versions/0.9/trunk/script/mysql/importProductInventoryExample.sql
___________________________________________________________________
Name: svn:mime-type
+ text/plain
Name: svn:keywords
+ Date Rev Author URL Id
Name: svn:eol-style
+ native
Modified: versions/0.9/trunk/servicedef/services.xml
===================================================================
--- versions/0.9/trunk/servicedef/services.xml 2007-01-29 18:45:39 UTC (rev 54)
+++ versions/0.9/trunk/servicedef/services.xml 2007-01-30 02:28:57 UTC (rev 55)
@@ -76,4 +76,18 @@
<attribute type="Integer" mode="OUT" name="productsImported" optional="true"/>
</service>
+ <service name="importProductInventory" engine="java" use-transaction="false"
+ location="org.opentaps.dataimport.ProductImportServices" invoke="importProductInventory">
+ <description>
+ Import product inventory using DataImportInventory.
+ Note that this service is not wrapped in a transaction. Each inventory record set imported is in its
+ own transaction, so it can store as many good records as possible.
+ </description>
+ <attribute name="organizationPartyId" type="String" mode="IN" optional="false"/>
+ <attribute name="facilityId" type="String" mode="IN" optional="false"/>
+ <attribute name="inventoryGlAccountId" type="String" mode="IN" optional="false"/>
+ <attribute name="offsettingGlAccountId" type="String" mode="IN" optional="false"/>
+ <attribute name="importedRecords" type="Integer" mode="OUT" optional="true"/>
+ </service>
+
</services>
Modified: versions/0.9/trunk/src/org/opentaps/dataimport/ProductImportServices.java
===================================================================
--- versions/0.9/trunk/src/org/opentaps/dataimport/ProductImportServices.java 2007-01-29 18:45:39 UTC (rev 54)
+++ versions/0.9/trunk/src/org/opentaps/dataimport/ProductImportServices.java 2007-01-30 02:28:57 UTC (rev 55)
@@ -31,6 +31,8 @@
import org.ofbiz.entity.transaction.TransactionUtil;
import org.ofbiz.service.ServiceUtil;
import org.ofbiz.service.DispatchContext;
+import org.ofbiz.service.LocalDispatcher;
+import org.ofbiz.service.GenericServiceException;
/**
* Import products via intermediate DataImportProduct entity.
@@ -38,8 +40,12 @@
* @author <a href="mailto:le...@op...">Leon Torres</a>
*/
public class ProductImportServices {
-
+
public static String module = ProductImportServices.class.getName();
+ public static final int decimals = UtilNumber.getBigDecimalScale("order.decimals");
+ public static final int rounding = UtilNumber.getBigDecimalRoundingMode("order.rounding");
+ public static final BigDecimal ZERO = (new BigDecimal("0")).setScale(decimals, rounding);
+ private static int acctgTransSeqNum = 1;
public static Map importProducts(DispatchContext dctx, Map context) {
GenericDelegator delegator = dctx.getDelegator();
@@ -123,6 +129,131 @@
return results;
}
+ public static Map importProductInventory(DispatchContext dctx, Map context) {
+ GenericDelegator delegator = dctx.getDelegator();
+ LocalDispatcher dispatcher = dctx.getDispatcher();
+ GenericValue userLogin = (GenericValue) context.get("userLogin");
+
+ String organizationPartyId = (String) context.get("organizationPartyId");
+ String facilityId = (String) context.get("facilityId");
+ String inventoryGlAccountId = (String) context.get("inventoryGlAccountId");
+ String offsettingGlAccountId = (String) context.get("offsettingGlAccountId");
+ Timestamp now = UtilDateTime.nowTimestamp();
+
+ int imported = 0;
+
+ // main try/catch block that traps errors related to obtaining data from delegator
+ try {
+
+ // Make sure the organizationParty exists
+ GenericValue organizationParty = delegator.findByPrimaryKey("Party", UtilMisc.toMap("partyId", organizationPartyId));
+
+ if (UtilValidate.isEmpty(organizationParty)) {
+ String errMsg = "Error in importProductInventory service: party [" + organizationPartyId + "] does not exist";
+ Debug.logError(errMsg, module);
+ return ServiceUtil.returnError(errMsg);
+ }
+
+ // Make sure the facility exists
+ if (! UtilValidate.isEmpty(facilityId)) {
+ GenericValue facility = delegator.findByPrimaryKey("Facility", UtilMisc.toMap("facilityId", facilityId));
+
+ if (UtilValidate.isEmpty(facility)) {
+ String errMsg = "Error in importProductInventory service: facility [" + facilityId + "] does not exist";
+ Debug.logError(errMsg, module);
+ return ServiceUtil.returnError(errMsg);
+ }
+ }
+
+ // Make sure the supplied GL accounts exist for the organization
+ GenericValue glAccountOrganization = null;
+ if (! UtilValidate.isEmpty(inventoryGlAccountId)) {
+ glAccountOrganization = delegator.findByPrimaryKey("GlAccountOrganization", UtilMisc.toMap("glAccountId", inventoryGlAccountId, "organizationPartyId", organizationPartyId));
+ if (glAccountOrganization == null) {
+ return ServiceUtil.returnError("Cannot import inventory: organization ["+organizationPartyId+"] does not have inventory General Ledger account ["+inventoryGlAccountId+"] defined in GlAccountOrganization.");
+ }
+ }
+ if (! UtilValidate.isEmpty(offsettingGlAccountId)) {
+ glAccountOrganization = delegator.findByPrimaryKey("GlAccountOrganization", UtilMisc.toMap("glAccountId", offsettingGlAccountId, "organizationPartyId", organizationPartyId));
+ if (glAccountOrganization == null) {
+ return ServiceUtil.returnError("Cannot import inventory: organization ["+organizationPartyId+"] does not have offsetting General Ledger account ["+offsettingGlAccountId+"] defined in GlAccountOrganization.");
+ }
+ }
+
+ // Get the base currency for the organization
+ GenericValue partyAcctgPref = delegator.findByPrimaryKeyCache("PartyAcctgPreference", UtilMisc.toMap("partyId", organizationPartyId));
+ if (UtilValidate.isEmpty(partyAcctgPref)) {
+ String errMsg = "Error in importProductInventory service: organization [" + organizationPartyId + "] does not have a PartyAcctgPref record";
+ Debug.logError(errMsg, module);
+ return ServiceUtil.returnError(errMsg);
+ }
+ String currencyUomId = partyAcctgPref.getString("baseCurrencyUomId");
+ if (UtilValidate.isEmpty(currencyUomId)) {
+ String errMsg = "Error in importProductInventory service: organization [" + organizationPartyId + "] does not have a baseCurrencyUomId defined in PartyAcctgPref";
+ Debug.logError(errMsg, module);
+ return ServiceUtil.returnError(errMsg);
+ }
+
+ // Create the header of an AcctgTrans record for all the subsequent transaction entries
+ Map createAcctgTransResults = dispatcher.runSync("createAcctgTrans", UtilMisc.toMap("acctgTransTypeId", "INTERNAL_ACCTG_TRANS", "glFiscalTypeId", "ACTUAL", "transactionDate", now, "userLogin", userLogin));
+ if (ServiceUtil.isError(createAcctgTransResults)) {
+ return createAcctgTransResults;
+ }
+ String acctgTransId = (String) createAcctgTransResults.get("acctgTransId");
+
+ // need to get an ELI because of possibly large number of records. productId <> null will get all records
+ EntityConditionList conditions = new EntityConditionList( UtilMisc.toList(
+ new EntityExpr("productId", EntityOperator.NOT_EQUAL, null),
+ new EntityExpr("processedTimestamp", EntityOperator.EQUALS, null) // leave out previously processed orders
+ ), EntityOperator.AND);
+ TransactionUtil.begin();
+ EntityListIterator importProductInventory = delegator.findListIteratorByCondition("DataImportInventory", conditions, null, null);
+ TransactionUtil.commit();
+
+ GenericValue productInventory = null;
+ while ((productInventory = (GenericValue) importProductInventory.next()) != null) {
+
+ try {
+
+ List toStore = decodeInventory(productInventory, organizationPartyId, facilityId, inventoryGlAccountId, offsettingGlAccountId, acctgTransId, currencyUomId, now, delegator);
+ if (toStore == null) {
+ Debug.logWarning("Import of product inventory["+productInventory.get("productId")+"] was unsuccessful.", module);
+ }
+
+ TransactionUtil.begin();
+
+ delegator.storeAll(toStore);
+
+ Debug.logInfo("Successfully imported product inventory ["+productInventory.get("productId")+"].", module);
+ imported++;
+
+ TransactionUtil.commit();
+
+ } catch ( GenericEntityException e) {
+ TransactionUtil.rollback();
+ Debug.logError(e, "Failed to import product inventory["+productInventory.get("productId")+"]. Error stack follows.", module);
+ } catch (Exception e) {
+ TransactionUtil.rollback();
+ Debug.logWarning("Import of product inventory["+productInventory.get("productId")+"] was unsuccessful.", module);
+ }
+ }
+ importProductInventory.close();
+
+ } catch ( GenericServiceException se) {
+ String errMsg = "Error in importProductInventory service: " + se.getMessage();
+ Debug.logError(se, errMsg, module);
+ return ServiceUtil.returnError(errMsg);
+ } catch (GenericEntityException ee) {
+ String errMsg = "Error in importProductInventory service: " + ee.getMessage();
+ Debug.logError(ee, errMsg, module);
+ return ServiceUtil.returnError(errMsg);
+ }
+
+ Map results = ServiceUtil.returnSuccess();
+ results.put("importedRecords", new Integer(imported));
+ return results;
+ }
+
/**
* Helper method to decode a DataImportProduct into a List of GenericValues modeling that product in the OFBiz schema.
* If for some reason obtaining data via the delegator fails, this service throws that exception.
@@ -205,11 +336,106 @@
GenericValue productFeatureAppl = delegator.makeValue("ProductFeatureAppl", input);
toStore.add(productFeatureAppl);
}
-
+
// update the original data record with a timestamp which also denotes that it has been processed (processedTimestamp = null was original search condition)
data.set("processedTimestamp", UtilDateTime.nowTimestamp());
toStore.add(data);
return toStore;
}
+
+ /**
+ * Helper method to decode a DataImportInventory into a List of GenericValues modeling that product in the OFBiz schema.
+ * If for some reason obtaining data via the delegator fails, this service throws that exception.
+ * Note that everything is done with the delegator for maximum efficiency.
+ */
+ private static List decodeInventory(GenericValue productInventory, String organizationPartyId, String facilityId, String inventoryGlAccountId, String offsettingGlAccountId, String acctgTransId, String currencyUomId, Timestamp now, GenericDelegator delegator) throws GenericEntityException, Exception {
+ Map input;
+ List toStore = FastList.newInstance();
+ Debug.logInfo("Now processing data [" + productInventory.get("productId") + "]", module);
+
+ String productId = productInventory.getString("productId");
+ Double onHand = productInventory.getDouble("onHand");
+ Double availableToPromise = productInventory.getDouble("availableToPromise");
+ Double inventoryValue = productInventory.getDouble("inventoryValue");
+
+ BigDecimal averageCost = ZERO;
+ if (onHand.doubleValue() > 0.0) {
+ averageCost = new BigDecimal(inventoryValue/onHand).setScale(decimals, rounding);
+ }
+
+ // Verify that productId exists
+ GenericValue product = delegator.findByPrimaryKey("Product", UtilMisc.toMap("productId", productId));
+ if (product == null) {
+ Debug.logInfo("Could not find product ["+productId+"], not importing.", module);
+ return toStore;
+ }
+
+ String inventoryItemId = delegator.getNextSeqId("InventoryItem");
+
+ // Create the inventory item
+ input = FastMap.newInstance();
+ input.put("inventoryItemId", inventoryItemId);
+ input.put("unitCost", new Double(averageCost.doubleValue()));
+ input.put("productId", productId);
+ input.put("ownerPartyId", organizationPartyId);
+ input.put("datetimeReceived", now);
+ input.put("facilityId", facilityId);
+ input.put("comments", "Auto-generated from Product Inventory Import.");
+ input.put("inventoryItemTypeId", "NON_SERIAL_INV_ITEM");
+ input.put("currencyUomId", currencyUomId);
+ GenericValue inventoryItem = delegator.makeValue("InventoryItem", input);
+ toStore.add(inventoryItem);
+
+ // Create the inventory item detail
+ input = FastMap.newInstance();
+ input.put("inventoryItemId", inventoryItemId);
+ input.put("inventoryItemDetailSeqId", UtilFormatOut.formatPaddedNumber(1, 4));
+ input.put("quantityOnHandDiff", onHand);
+ if (! UtilValidate.isEmpty(availableToPromise)) {
+ input.put("availableToPromiseDiff", availableToPromise);
+ } else {
+ input.put("availableToPromiseDiff", onHand);
+ }
+ GenericValue inventoryItemDetail = delegator.makeValue("InventoryItemDetail", input);
+ toStore.add(inventoryItemDetail);
+
+ // Create the product average cost
+ input = FastMap.newInstance();
+ input.put("organizationPartyId", organizationPartyId);
+ input.put("fromDate", now);
+ input.put("averageCost", new Double(averageCost.doubleValue()));
+ input.put("productId", productId);
+ GenericValue productAverageCost = delegator.makeValue("ProductAverageCost", input);
+ toStore.add(productAverageCost);
+
+ // Create the two AcctgTransEntries for this item
+ input = FastMap.newInstance();
+ input.put("acctgTransId", acctgTransId);
+ input.put("acctgTransEntrySeqId", UtilFormatOut.formatPaddedNumber(acctgTransSeqNum, 5));
+ input.put("acctgTransEntryTypeId", "_NA_");
+ input.put("productId", productId);
+ input.put("organizationPartyId", organizationPartyId);
+ input.put("currencyUomId", currencyUomId);
+ input.put("reconcileStatusId", "AES_NOT_RECONCILED");
+ input.put("glAccountId", inventoryGlAccountId);
+ input.put("debitCreditFlag", "D");
+ input.put("amount", inventoryValue);
+ GenericValue debitAcctgTransEntry = delegator.makeValue("AcctgTransEntry", input);
+ toStore.add(debitAcctgTransEntry);
+ acctgTransSeqNum++;
+
+ input.put("acctgTransEntrySeqId", UtilFormatOut.formatPaddedNumber(acctgTransSeqNum, 5));
+ input.put("glAccountId", offsettingGlAccountId);
+ input.put("debitCreditFlag", "C");
+ GenericValue creditAcctgTransEntry = delegator.makeValue("AcctgTransEntry", input);
+ toStore.add(creditAcctgTransEntry);
+ acctgTransSeqNum++;
+
+ // Update the original productInventory record with a timestamp which also denotes that it has been processed (processedTimestamp = null was original search condition)
+ productInventory.set("processedTimestamp", UtilDateTime.nowTimestamp());
+ toStore.add(productInventory);
+
+ return toStore;
+ }
}
|
