You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by David E Jones <jo...@undersunconsulting.com> on 2008/06/05 15:06:23 UTC
Re: svn commit: r663581 - in /ofbiz/trunk/specialpurpose/assetmaint: build.xml data/ScheduleUploadJob.xml servicedef/services.xml src/org/ofbiz/poi/ src/org/ofbiz/poi/FileImportHelper.java src/org/ofbiz/poi/FileImportService.java
Some quick feedback on the location/naming of things:
1. this code doesn't really have much to do with assetmaint and
instead is for Product and InventoryItem, so being fairly low-level it
should go in the product component where someone looking for it might
actually expect to find it
2. the name of the files "FileImportHelper.java" and
"FileImportService.java" imply that it is generic code in those files,
but it is actually code just for the Product and InventoryItem
entities, so it would be best to make that more clear in the filename,
or at least in the path (again org/ofbiz/poi is a VERY generic path
that implies this is more general/generic than it actually is)
The main point is that there is a LOT of code in OFBiz and it is
important to organize and distinguish things the best possible to make
it easier to maintain things, and for people to find existing
functionality (increases the chances of it being used).
-David
On Jun 5, 2008, at 6:27 AM, ashish@apache.org wrote:
> Author: ashish
> Date: Thu Jun 5 05:27:04 2008
> New Revision: 663581
>
> URL: http://svn.apache.org/viewvc?rev=663581&view=rev
> Log:
> Applied patch from JIRA issue # OFBIZ-1810.
> This additional feature will help the Developer to get the idea of
> usage of POI api with the Ofbiz.
>
> Please take the attached pdf file from JIRA issue for the references
> for now.
> Pranay & Chirag please upload the document on Ofbiz Confluence
> whenever you get a chance.
>
> This addition looks really good to me and didn't see any comment
> from community so I am committing it and further enhancement can be
> done later on.
>
> Thanks Pranay Pandey and Chirag Manocha for working on this.
>
> Added:
> ofbiz/trunk/specialpurpose/assetmaint/data/
> ScheduleUploadJob.xml (with props)
> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportHelper.java (with props)
> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportService.java (with props)
> Modified:
> ofbiz/trunk/specialpurpose/assetmaint/build.xml
> ofbiz/trunk/specialpurpose/assetmaint/servicedef/services.xml
>
> Modified: ofbiz/trunk/specialpurpose/assetmaint/build.xml
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/specialpurpose/assetmaint/build.xml?rev=663581&r1=663580&r2=663581&view=diff
> =
> =
> =
> =
> =
> =
> =
> =
> ======================================================================
> --- ofbiz/trunk/specialpurpose/assetmaint/build.xml (original)
> +++ ofbiz/trunk/specialpurpose/assetmaint/build.xml Thu Jun 5
> 05:27:04 2008
> @@ -50,6 +50,7 @@
> <fileset dir="../../framework/webapp/build/lib"
> includes="*.jar"/>
> <fileset dir="../../framework/common/build/lib"
> includes="*.jar"/>
> <fileset dir="../../framework/workflow/build/lib"
> includes="*.jar"/>
> + <fileset dir="../../applications/content/lib"
> includes="*.jar"/>
> <fileset dir="../../applications/product/build/lib"
> includes="*.jar"/>
> </path>
> </target>
>
> Added: ofbiz/trunk/specialpurpose/assetmaint/data/
> ScheduleUploadJob.xml
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/specialpurpose/assetmaint/data/ScheduleUploadJob.xml?rev=663581&view=auto
> =
> =
> =
> =
> =
> =
> =
> =
> ======================================================================
> --- ofbiz/trunk/specialpurpose/assetmaint/data/ScheduleUploadJob.xml
> (added)
> +++ ofbiz/trunk/specialpurpose/assetmaint/data/ScheduleUploadJob.xml
> Thu Jun 5 05:27:04 2008
> @@ -0,0 +1,25 @@
> +<?xml version="1.0" encoding="UTF-8"?>
> +<!--
> +Licensed to the Apache Software Foundation (ASF) under one
> +or more contributor license agreements. See the NOTICE file
> +distributed with this work for additional information
> +regarding copyright ownership. The ASF licenses this file
> +to you under the Apache License, Version 2.0 (the
> +"License"); you may not use this file except in compliance
> +with the License. You may obtain a copy of the License at
> +
> +http://www.apache.org/licenses/LICENSE-2.0
> +
> +Unless required by applicable law or agreed to in writing,
> +software distributed under the License is distributed on an
> +"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
> +KIND, either express or implied. See the License for the
> +specific language governing permissions and limitations
> +under the License.
> +-->
> +<entity-engine-xml>
> + <!--Data for scheduling the service productImport -->
> + <!--RecurrenceRule recurrenceRuleId="500" untilDateTime=""
> frequency="MINUTELY" intervalNumber="5" countNumber="-1"/>
> + <RecurrenceInfo recurrenceInfoId="500"
> startDateTime="2006-06-06 01:10:00.000" recurrenceRuleId="500"
> recurrenceCount="0"/>
> + <JobSandbox jobId="9000" jobName="Import Excel Sheet Minutely"
> runTime="2008-05-29 01:10:00.000" serviceName="productImport"
> poolId="pool" runAsUser="admin" recurrenceInfoId="500"/-->
> +</entity-engine-xml>
> \ No newline at end of file
>
> Propchange: ofbiz/trunk/specialpurpose/assetmaint/data/
> ScheduleUploadJob.xml
> ------------------------------------------------------------------------------
> svn:eol-style = native
>
> Propchange: ofbiz/trunk/specialpurpose/assetmaint/data/
> ScheduleUploadJob.xml
> ------------------------------------------------------------------------------
> svn:keywords = Date Rev Author URL Id
>
> Propchange: ofbiz/trunk/specialpurpose/assetmaint/data/
> ScheduleUploadJob.xml
> ------------------------------------------------------------------------------
> svn:mime-type = text/xml
>
> Modified: ofbiz/trunk/specialpurpose/assetmaint/servicedef/
> services.xml
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/specialpurpose/assetmaint/servicedef/services.xml?rev=663581&r1=663580&r2=663581&view=diff
> =
> =
> =
> =
> =
> =
> =
> =
> ======================================================================
> --- ofbiz/trunk/specialpurpose/assetmaint/servicedef/services.xml
> (original)
> +++ ofbiz/trunk/specialpurpose/assetmaint/servicedef/services.xml
> Thu Jun 5 05:27:04 2008
> @@ -36,4 +36,10 @@
> <description>Asset Maintentance Permission Checking Logic</
> description>
> <implements service="permissionInterface"/>
> </service>
> +
> + <service name="productImport" engine="java"
> + location="org.ofbiz.poi.FileImportService"
> invoke="productImport" auth="true">
> + <description>Create product and inventory item</description>
> + <attribute name="dirName" type="java.lang.String" mode="IN"
> optional="true"/>
> + </service>
> </services>
>
> Added: ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportHelper.java
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportHelper.java?rev=663581&view=auto
> =
> =
> =
> =
> =
> =
> =
> =
> ======================================================================
> --- ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportHelper.java (added)
> +++ ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportHelper.java Thu Jun 5 05:27:04 2008
> @@ -0,0 +1,76 @@
> +/
> *******************************************************************************
> + * Licensed to the Apache Software Foundation (ASF) under one
> + * or more contributor license agreements. See the NOTICE file
> + * distributed with this work for additional information
> + * regarding copyright ownership. The ASF licenses this file
> + * to you under the Apache License, Version 2.0 (the
> + * "License"); you may not use this file except in compliance
> + * with the License. You may obtain a copy of the License at
> + *
> + * http://www.apache.org/licenses/LICENSE-2.0
> + *
> + * Unless required by applicable law or agreed to in writing,
> + * software distributed under the License is distributed on an
> + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
> + * KIND, either express or implied. See the License for the
> + * specific language governing permissions and limitations
> + * under the License.
> +
> *******************************************************************************/
> +
> +package org.ofbiz.poi;
> +
> +import java.util.HashMap;
> +import java.util.Map;
> +
> +import org.ofbiz.base.util.Debug;
> +import org.ofbiz.base.util.UtilMisc;
> +import org.ofbiz.entity.GenericDelegator;
> +import org.ofbiz.entity.GenericEntityException;
> +import org.ofbiz.entity.GenericValue;
> +
> +public class FileImportHelper {
> +
> + static String module = FileImportHelper.class.getName();
> +
> + // prepare the product map
> + public static Map prepareProduct(String productId) {
> + Map fields = new HashMap();
> + fields.put("productId", productId);
> + fields.put("productTypeId", "FINISHED_GOOD");
> + fields.put("internalName", "Product_" + productId);
> + fields.put("isVirtual", "N");
> + fields.put("isVariant", "N");
> + return fields;
> + }
> +
> + // prepare the inventoryItem map
> + public static Map prepareInventoryItem(String productId,
> + double quantityOnHand, String inventoryItemId) {
> + Map fields = new HashMap();
> + fields.put("inventoryItemId", inventoryItemId);
> + fields.put("inventoryItemTypeId", "NON_SERIAL_INV_ITEM");
> + fields.put("productId", productId);
> + fields.put("ownerPartyId", "Company");
> + fields.put("facilityId", "WebStoreWarehouse");
> + fields.put("quantityOnHandTotal", new
> Double(quantityOnHand));
> + fields.put("availableToPromiseTotal", new
> Double(quantityOnHand));
> + return fields;
> + }
> +
> + // check if product already exists in database
> + public static boolean checkProductExists(String productId,
> + GenericDelegator delegator) {
> + GenericValue tmpProductGV;
> + boolean productExists = false;
> + try {
> + tmpProductGV = delegator.findByPrimaryKey("Product",
> UtilMisc
> + .toMap("productId", productId));
> + if (tmpProductGV != null
> + && tmpProductGV.getString("productId") ==
> productId)
> + productExists = true;
> + } catch (GenericEntityException e) {
> + Debug.logError("Problem in reading data of product",
> module);
> + }
> + return productExists;
> + }
> +}
> \ No newline at end of file
>
> Propchange: ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportHelper.java
> ------------------------------------------------------------------------------
> svn:eol-style = native
>
> Propchange: ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportHelper.java
> ------------------------------------------------------------------------------
> svn:keywords = Date Rev Author URL Id
>
> Propchange: ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportHelper.java
> ------------------------------------------------------------------------------
> svn:mime-type = text/plain
>
> Added: ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportService.java
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportService.java?rev=663581&view=auto
> =
> =
> =
> =
> =
> =
> =
> =
> ======================================================================
> --- ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportService.java (added)
> +++ ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportService.java Thu Jun 5 05:27:04 2008
> @@ -0,0 +1,166 @@
> +/
> *******************************************************************************
> + * Licensed to the Apache Software Foundation (ASF) under one
> + * or more contributor license agreements. See the NOTICE file
> + * distributed with this work for additional information
> + * regarding copyright ownership. The ASF licenses this file
> + * to you under the Apache License, Version 2.0 (the
> + * "License"); you may not use this file except in compliance
> + * with the License. You may obtain a copy of the License at
> + *
> + * http://www.apache.org/licenses/LICENSE-2.0
> + *
> + * Unless required by applicable law or agreed to in writing,
> + * software distributed under the License is distributed on an
> + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
> + * KIND, either express or implied. See the License for the
> + * specific language governing permissions and limitations
> + * under the License.
> +
> *******************************************************************************/
> +
> +package org.ofbiz.poi;
> +
> +import java.io.File;
> +import java.io.FileInputStream;
> +import java.io.IOException;
> +import java.util.ArrayList;
> +import java.util.HashMap;
> +import java.util.List;
> +import java.util.Map;
> +
> +import org.apache.poi.hssf.usermodel.HSSFCell;
> +import org.apache.poi.hssf.usermodel.HSSFRow;
> +import org.apache.poi.hssf.usermodel.HSSFSheet;
> +import org.apache.poi.hssf.usermodel.HSSFWorkbook;
> +import org.apache.poi.poifs.filesystem.POIFSFileSystem;
> +import org.ofbiz.base.util.Debug;
> +import org.ofbiz.entity.GenericDelegator;
> +import org.ofbiz.entity.GenericEntityException;
> +import org.ofbiz.entity.GenericValue;
> +import org.ofbiz.service.DispatchContext;
> +import org.ofbiz.service.ServiceUtil;
> +
> +public class FileImportService {
> +
> + public static String module = FileImportService.class.getName();
> +
> + /**
> + * This method is responsible to import spreadsheet data into
> "Product" and
> + * "InventoryItem" entities into database. The method uses the
> + * FileImportHelper class to perform its opertaion. The method
> uses "Apache
> + * POI" api for importing spreadsheet(xls files) data.
> + *
> + * Note : Create the spreadsheet directory in the ofbiz home
> folder and keep
> + * your xls files in this folder only.
> + *
> + * @param dctx
> + * @param context
> + * @return
> + */
> + public static Map productImport(DispatchContext dctx, Map
> context) {
> + GenericDelegator delegator = dctx.getDelegator();
> + Map responseMsgs = new HashMap();
> + // System.getProperty("user.dir") returns the path upto
> ofbiz home
> + // directory
> + String path = System.getProperty("user.dir") + "/
> spreadsheet";
> + List fileItems = new ArrayList();
> +
> + if (path != null && path.length() > 0) {
> + File importDir = new File(path);
> + if (importDir.isDirectory() && importDir.canRead()) {
> + File[] files = importDir.listFiles();
> + // loop for all the containing xls file in the
> spreadsheet
> + // directory
> + for (int i = 0; i < files.length; i++) {
> + if
> (files[i].getName().toUpperCase().endsWith("XLS")) {
> + fileItems.add(files[i]);
> + }
> + }
> + } else {
> + Debug.logWarning("Directory not found or can't be
> read", module);
> + return responseMsgs;
> + }
> + } else {
> + Debug.logWarning("No path specified, doing nothing",
> module);
> + return responseMsgs;
> + }
> +
> + if (fileItems.size() < 1) {
> + Debug.logWarning("No spreadsheet exists in " + path,
> module);
> + return responseMsgs;
> + }
> +
> + for (int i = 0; i < fileItems.size(); i++) {
> + // read all xls file and create workbook one by one.
> + File item = (File) fileItems.get(i);
> + List products = new ArrayList();
> + List inventoryItems = new ArrayList();
> + POIFSFileSystem fs = null;
> + HSSFWorkbook wb = null;
> + try {
> + fs = new POIFSFileSystem(new FileInputStream(item));
> + wb = new HSSFWorkbook(fs);
> + } catch (IOException e) {
> + Debug.logError("Unable to read or create workbook
> from file", module);
> + return responseMsgs;
> + }
> +
> + // get first sheet
> + HSSFSheet sheet = wb.getSheetAt(0);
> + int sheetLastRowNumber = sheet.getLastRowNum();
> + for (int j = 1; j <= sheetLastRowNumber; j++) {
> + HSSFRow row = sheet.getRow(j);
> + if (row != null) {
> + // read productId from first column "sheet
> column index
> + // starts from 0"
> + HSSFCell cell1 = row.getCell((short) 1);
> + cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
> + String productId = cell1.getStringCellValue();
> + // read QOH from ninth column
> + HSSFCell cell8 = row.getCell((short) 8);
> + double quantityOnHand = 0.0;
> + if (cell8 != null && cell8.getCellType() ==
> HSSFCell.CELL_TYPE_NUMERIC)
> + quantityOnHand = cell8.getNumericCellValue();
> +
> + // check productId if null then skip creating
> inventory item
> + // too.
> +
> + boolean productExists =
> FileImportHelper.checkProductExists(productId, delegator);
> +
> + if (productId != null && !
> productId.trim().equalsIgnoreCase("") && !productExists) {
> +
> products.add(FileImportHelper.prepareProduct(productId));
> + if (quantityOnHand >= 0.0)
> +
> inventoryItems.add(FileImportHelper.prepareInventoryItem(productId,
> quantityOnHand,
> +
> delegator.getNextSeqId("InventoryItem")));
> + else
> +
> inventoryItems.add(FileImportHelper.prepareInventoryItem(productId,
> 0.0, delegator
> + .getNextSeqId("InventoryItem")));
> + }
> + int rowNum = row.getRowNum() + 1;
> + if (row.toString() != null && !
> row.toString().trim().equalsIgnoreCase("") && products.size() > 0
> + && !productExists) {
> + Debug.logWarning("Row number " + rowNum + "
> not imported from " + item.getName(), module);
> + }
> + }
> + }
> + // create and store values in "Product" and
> "InventoryItem" entity
> + // in database
> + for (int j = 0; j < products.size(); j++) {
> + GenericValue productGV =
> delegator.makeValue("Product", (Map) products.get(j));
> + GenericValue inventoryItemGV =
> delegator.makeValue("InventoryItem", (Map) inventoryItems.get(j));
> + if (!
> FileImportHelper
> .checkProductExists(productGV.getString("productId"), delegator)) {
> + try {
> + delegator.create(productGV);
> + delegator.create(inventoryItemGV);
> + } catch (GenericEntityException e) {
> + Debug.logError("Cannot store product",
> module);
> + return ServiceUtil.returnError("Cannot
> store product");
> + }
> + }
> + }
> + int uploadedProducts = products.size() + 1;
> + if (products.size() > 0)
> + Debug.logInfo("Uploaded " + uploadedProducts + "
> products from file " + item.getName(), module);
> + }
> + return responseMsgs;
> + }
> +}
>
> Propchange: ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportService.java
> ------------------------------------------------------------------------------
> svn:eol-style = native
>
> Propchange: ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportService.java
> ------------------------------------------------------------------------------
> svn:keywords = Date Rev Author URL Id
>
> Propchange: ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
> FileImportService.java
> ------------------------------------------------------------------------------
> svn:mime-type = text/plain
>
>
Re: svn commit: r663581 - in /ofbiz/trunk/specialpurpose/assetmaint: build.xml data/ScheduleUploadJob.xml servicedef/services.xml src/org/ofbiz/poi/ src/org/ofbiz/poi/FileImportHelper.java src/org/ofbiz/poi/FileImportService.java
Posted by Ashish Vijaywargiya <vi...@gmail.com>.
David,
Thanks for your comments.
I will do the necessary changes as suggested by you ASAP.
--
Ashish
On Thu, Jun 5, 2008 at 9:06 AM, David E Jones <
jonesde@undersunconsulting.com> wrote:
>
> Some quick feedback on the location/naming of things:
>
> 1. this code doesn't really have much to do with assetmaint and instead is
> for Product and InventoryItem, so being fairly low-level it should go in the
> product component where someone looking for it might actually expect to find
> it
>
> 2. the name of the files "FileImportHelper.java" and
> "FileImportService.java" imply that it is generic code in those files, but
> it is actually code just for the Product and InventoryItem entities, so it
> would be best to make that more clear in the filename, or at least in the
> path (again org/ofbiz/poi is a VERY generic path that implies this is more
> general/generic than it actually is)
>
> The main point is that there is a LOT of code in OFBiz and it is important
> to organize and distinguish things the best possible to make it easier to
> maintain things, and for people to find existing functionality (increases
> the chances of it being used).
>
> -David
>
>
>
> On Jun 5, 2008, at 6:27 AM, ashish@apache.org wrote:
>
> Author: ashish
>> Date: Thu Jun 5 05:27:04 2008
>> New Revision: 663581
>>
>> URL: http://svn.apache.org/viewvc?rev=663581&view=rev
>> Log:
>> Applied patch from JIRA issue # OFBIZ-1810.
>> This additional feature will help the Developer to get the idea of
>> usage of POI api with the Ofbiz.
>>
>> Please take the attached pdf file from JIRA issue for the references for
>> now.
>> Pranay & Chirag please upload the document on Ofbiz Confluence whenever
>> you get a chance.
>>
>> This addition looks really good to me and didn't see any comment from
>> community so I am committing it and further enhancement can be done later
>> on.
>>
>> Thanks Pranay Pandey and Chirag Manocha for working on this.
>>
>> Added:
>> ofbiz/trunk/specialpurpose/assetmaint/data/ScheduleUploadJob.xml (with
>> props)
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/
>>
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportHelper.java
>> (with props)
>>
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportService.java
>> (with props)
>> Modified:
>> ofbiz/trunk/specialpurpose/assetmaint/build.xml
>> ofbiz/trunk/specialpurpose/assetmaint/servicedef/services.xml
>>
>> Modified: ofbiz/trunk/specialpurpose/assetmaint/build.xml
>> URL:
>> http://svn.apache.org/viewvc/ofbiz/trunk/specialpurpose/assetmaint/build.xml?rev=663581&r1=663580&r2=663581&view=diff
>>
>> ==============================================================================
>> --- ofbiz/trunk/specialpurpose/assetmaint/build.xml (original)
>> +++ ofbiz/trunk/specialpurpose/assetmaint/build.xml Thu Jun 5 05:27:04
>> 2008
>> @@ -50,6 +50,7 @@
>> <fileset dir="../../framework/webapp/build/lib"
>> includes="*.jar"/>
>> <fileset dir="../../framework/common/build/lib"
>> includes="*.jar"/>
>> <fileset dir="../../framework/workflow/build/lib"
>> includes="*.jar"/>
>> + <fileset dir="../../applications/content/lib"
>> includes="*.jar"/>
>> <fileset dir="../../applications/product/build/lib"
>> includes="*.jar"/>
>> </path>
>> </target>
>>
>> Added: ofbiz/trunk/specialpurpose/assetmaint/data/ScheduleUploadJob.xml
>> URL:
>> http://svn.apache.org/viewvc/ofbiz/trunk/specialpurpose/assetmaint/data/ScheduleUploadJob.xml?rev=663581&view=auto
>>
>> ==============================================================================
>> --- ofbiz/trunk/specialpurpose/assetmaint/data/ScheduleUploadJob.xml
>> (added)
>> +++ ofbiz/trunk/specialpurpose/assetmaint/data/ScheduleUploadJob.xml Thu
>> Jun 5 05:27:04 2008
>> @@ -0,0 +1,25 @@
>> +<?xml version="1.0" encoding="UTF-8"?>
>> +<!--
>> +Licensed to the Apache Software Foundation (ASF) under one
>> +or more contributor license agreements. See the NOTICE file
>> +distributed with this work for additional information
>> +regarding copyright ownership. The ASF licenses this file
>> +to you under the Apache License, Version 2.0 (the
>> +"License"); you may not use this file except in compliance
>> +with the License. You may obtain a copy of the License at
>> +
>> +http://www.apache.org/licenses/LICENSE-2.0
>> +
>> +Unless required by applicable law or agreed to in writing,
>> +software distributed under the License is distributed on an
>> +"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
>> +KIND, either express or implied. See the License for the
>> +specific language governing permissions and limitations
>> +under the License.
>> +-->
>> +<entity-engine-xml>
>> + <!--Data for scheduling the service productImport -->
>> + <!--RecurrenceRule recurrenceRuleId="500" untilDateTime=""
>> frequency="MINUTELY" intervalNumber="5" countNumber="-1"/>
>> + <RecurrenceInfo recurrenceInfoId="500" startDateTime="2006-06-06
>> 01:10:00.000" recurrenceRuleId="500" recurrenceCount="0"/>
>> + <JobSandbox jobId="9000" jobName="Import Excel Sheet Minutely"
>> runTime="2008-05-29 01:10:00.000" serviceName="productImport" poolId="pool"
>> runAsUser="admin" recurrenceInfoId="500"/-->
>> +</entity-engine-xml>
>> \ No newline at end of file
>>
>> Propchange:
>> ofbiz/trunk/specialpurpose/assetmaint/data/ScheduleUploadJob.xml
>>
>> ------------------------------------------------------------------------------
>> svn:eol-style = native
>>
>> Propchange:
>> ofbiz/trunk/specialpurpose/assetmaint/data/ScheduleUploadJob.xml
>>
>> ------------------------------------------------------------------------------
>> svn:keywords = Date Rev Author URL Id
>>
>> Propchange:
>> ofbiz/trunk/specialpurpose/assetmaint/data/ScheduleUploadJob.xml
>>
>> ------------------------------------------------------------------------------
>> svn:mime-type = text/xml
>>
>> Modified: ofbiz/trunk/specialpurpose/assetmaint/servicedef/services.xml
>> URL:
>> http://svn.apache.org/viewvc/ofbiz/trunk/specialpurpose/assetmaint/servicedef/services.xml?rev=663581&r1=663580&r2=663581&view=diff
>>
>> ==============================================================================
>> --- ofbiz/trunk/specialpurpose/assetmaint/servicedef/services.xml
>> (original)
>> +++ ofbiz/trunk/specialpurpose/assetmaint/servicedef/services.xml Thu Jun
>> 5 05:27:04 2008
>> @@ -36,4 +36,10 @@
>> <description>Asset Maintentance Permission Checking
>> Logic</description>
>> <implements service="permissionInterface"/>
>> </service>
>> +
>> + <service name="productImport" engine="java"
>> + location="org.ofbiz.poi.FileImportService"
>> invoke="productImport" auth="true">
>> + <description>Create product and inventory item</description>
>> + <attribute name="dirName" type="java.lang.String" mode="IN"
>> optional="true"/>
>> + </service>
>> </services>
>>
>> Added:
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportHelper.java
>> URL:
>> http://svn.apache.org/viewvc/ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportHelper.java?rev=663581&view=auto
>>
>> ==============================================================================
>> ---
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportHelper.java
>> (added)
>> +++
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportHelper.java
>> Thu Jun 5 05:27:04 2008
>> @@ -0,0 +1,76 @@
>>
>> +/*******************************************************************************
>> + * Licensed to the Apache Software Foundation (ASF) under one
>> + * or more contributor license agreements. See the NOTICE file
>> + * distributed with this work for additional information
>> + * regarding copyright ownership. The ASF licenses this file
>> + * to you under the Apache License, Version 2.0 (the
>> + * "License"); you may not use this file except in compliance
>> + * with the License. You may obtain a copy of the License at
>> + *
>> + * http://www.apache.org/licenses/LICENSE-2.0
>> + *
>> + * Unless required by applicable law or agreed to in writing,
>> + * software distributed under the License is distributed on an
>> + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
>> + * KIND, either express or implied. See the License for the
>> + * specific language governing permissions and limitations
>> + * under the License.
>> +
>> *******************************************************************************/
>> +
>> +package org.ofbiz.poi;
>> +
>> +import java.util.HashMap;
>> +import java.util.Map;
>> +
>> +import org.ofbiz.base.util.Debug;
>> +import org.ofbiz.base.util.UtilMisc;
>> +import org.ofbiz.entity.GenericDelegator;
>> +import org.ofbiz.entity.GenericEntityException;
>> +import org.ofbiz.entity.GenericValue;
>> +
>> +public class FileImportHelper {
>> +
>> + static String module = FileImportHelper.class.getName();
>> +
>> + // prepare the product map
>> + public static Map prepareProduct(String productId) {
>> + Map fields = new HashMap();
>> + fields.put("productId", productId);
>> + fields.put("productTypeId", "FINISHED_GOOD");
>> + fields.put("internalName", "Product_" + productId);
>> + fields.put("isVirtual", "N");
>> + fields.put("isVariant", "N");
>> + return fields;
>> + }
>> +
>> + // prepare the inventoryItem map
>> + public static Map prepareInventoryItem(String productId,
>> + double quantityOnHand, String inventoryItemId) {
>> + Map fields = new HashMap();
>> + fields.put("inventoryItemId", inventoryItemId);
>> + fields.put("inventoryItemTypeId", "NON_SERIAL_INV_ITEM");
>> + fields.put("productId", productId);
>> + fields.put("ownerPartyId", "Company");
>> + fields.put("facilityId", "WebStoreWarehouse");
>> + fields.put("quantityOnHandTotal", new Double(quantityOnHand));
>> + fields.put("availableToPromiseTotal", new
>> Double(quantityOnHand));
>> + return fields;
>> + }
>> +
>> + // check if product already exists in database
>> + public static boolean checkProductExists(String productId,
>> + GenericDelegator delegator) {
>> + GenericValue tmpProductGV;
>> + boolean productExists = false;
>> + try {
>> + tmpProductGV = delegator.findByPrimaryKey("Product", UtilMisc
>> + .toMap("productId", productId));
>> + if (tmpProductGV != null
>> + && tmpProductGV.getString("productId") == productId)
>> + productExists = true;
>> + } catch (GenericEntityException e) {
>> + Debug.logError("Problem in reading data of product", module);
>> + }
>> + return productExists;
>> + }
>> +}
>> \ No newline at end of file
>>
>> Propchange:
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportHelper.java
>>
>> ------------------------------------------------------------------------------
>> svn:eol-style = native
>>
>> Propchange:
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportHelper.java
>>
>> ------------------------------------------------------------------------------
>> svn:keywords = Date Rev Author URL Id
>>
>> Propchange:
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportHelper.java
>>
>> ------------------------------------------------------------------------------
>> svn:mime-type = text/plain
>>
>> Added:
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportService.java
>> URL:
>> http://svn.apache.org/viewvc/ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportService.java?rev=663581&view=auto
>>
>> ==============================================================================
>> ---
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportService.java
>> (added)
>> +++
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportService.java
>> Thu Jun 5 05:27:04 2008
>> @@ -0,0 +1,166 @@
>>
>> +/*******************************************************************************
>> + * Licensed to the Apache Software Foundation (ASF) under one
>> + * or more contributor license agreements. See the NOTICE file
>> + * distributed with this work for additional information
>> + * regarding copyright ownership. The ASF licenses this file
>> + * to you under the Apache License, Version 2.0 (the
>> + * "License"); you may not use this file except in compliance
>> + * with the License. You may obtain a copy of the License at
>> + *
>> + * http://www.apache.org/licenses/LICENSE-2.0
>> + *
>> + * Unless required by applicable law or agreed to in writing,
>> + * software distributed under the License is distributed on an
>> + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
>> + * KIND, either express or implied. See the License for the
>> + * specific language governing permissions and limitations
>> + * under the License.
>> +
>> *******************************************************************************/
>> +
>> +package org.ofbiz.poi;
>> +
>> +import java.io.File;
>> +import java.io.FileInputStream;
>> +import java.io.IOException;
>> +import java.util.ArrayList;
>> +import java.util.HashMap;
>> +import java.util.List;
>> +import java.util.Map;
>> +
>> +import org.apache.poi.hssf.usermodel.HSSFCell;
>> +import org.apache.poi.hssf.usermodel.HSSFRow;
>> +import org.apache.poi.hssf.usermodel.HSSFSheet;
>> +import org.apache.poi.hssf.usermodel.HSSFWorkbook;
>> +import org.apache.poi.poifs.filesystem.POIFSFileSystem;
>> +import org.ofbiz.base.util.Debug;
>> +import org.ofbiz.entity.GenericDelegator;
>> +import org.ofbiz.entity.GenericEntityException;
>> +import org.ofbiz.entity.GenericValue;
>> +import org.ofbiz.service.DispatchContext;
>> +import org.ofbiz.service.ServiceUtil;
>> +
>> +public class FileImportService {
>> +
>> + public static String module = FileImportService.class.getName();
>> +
>> + /**
>> + * This method is responsible to import spreadsheet data into
>> "Product" and
>> + * "InventoryItem" entities into database. The method uses the
>> + * FileImportHelper class to perform its opertaion. The method uses
>> "Apache
>> + * POI" api for importing spreadsheet(xls files) data.
>> + *
>> + * Note : Create the spreadsheet directory in the ofbiz home folder
>> and keep
>> + * your xls files in this folder only.
>> + *
>> + * @param dctx
>> + * @param context
>> + * @return
>> + */
>> + public static Map productImport(DispatchContext dctx, Map context) {
>> + GenericDelegator delegator = dctx.getDelegator();
>> + Map responseMsgs = new HashMap();
>> + // System.getProperty("user.dir") returns the path upto ofbiz
>> home
>> + // directory
>> + String path = System.getProperty("user.dir") + "/spreadsheet";
>> + List fileItems = new ArrayList();
>> +
>> + if (path != null && path.length() > 0) {
>> + File importDir = new File(path);
>> + if (importDir.isDirectory() && importDir.canRead()) {
>> + File[] files = importDir.listFiles();
>> + // loop for all the containing xls file in the
>> spreadsheet
>> + // directory
>> + for (int i = 0; i < files.length; i++) {
>> + if (files[i].getName().toUpperCase().endsWith("XLS"))
>> {
>> + fileItems.add(files[i]);
>> + }
>> + }
>> + } else {
>> + Debug.logWarning("Directory not found or can't be read",
>> module);
>> + return responseMsgs;
>> + }
>> + } else {
>> + Debug.logWarning("No path specified, doing nothing", module);
>> + return responseMsgs;
>> + }
>> +
>> + if (fileItems.size() < 1) {
>> + Debug.logWarning("No spreadsheet exists in " + path, module);
>> + return responseMsgs;
>> + }
>> +
>> + for (int i = 0; i < fileItems.size(); i++) {
>> + // read all xls file and create workbook one by one.
>> + File item = (File) fileItems.get(i);
>> + List products = new ArrayList();
>> + List inventoryItems = new ArrayList();
>> + POIFSFileSystem fs = null;
>> + HSSFWorkbook wb = null;
>> + try {
>> + fs = new POIFSFileSystem(new FileInputStream(item));
>> + wb = new HSSFWorkbook(fs);
>> + } catch (IOException e) {
>> + Debug.logError("Unable to read or create workbook from
>> file", module);
>> + return responseMsgs;
>> + }
>> +
>> + // get first sheet
>> + HSSFSheet sheet = wb.getSheetAt(0);
>> + int sheetLastRowNumber = sheet.getLastRowNum();
>> + for (int j = 1; j <= sheetLastRowNumber; j++) {
>> + HSSFRow row = sheet.getRow(j);
>> + if (row != null) {
>> + // read productId from first column "sheet column
>> index
>> + // starts from 0"
>> + HSSFCell cell1 = row.getCell((short) 1);
>> + cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
>> + String productId = cell1.getStringCellValue();
>> + // read QOH from ninth column
>> + HSSFCell cell8 = row.getCell((short) 8);
>> + double quantityOnHand = 0.0;
>> + if (cell8 != null && cell8.getCellType() ==
>> HSSFCell.CELL_TYPE_NUMERIC)
>> + quantityOnHand = cell8.getNumericCellValue();
>> +
>> + // check productId if null then skip creating
>> inventory item
>> + // too.
>> +
>> + boolean productExists =
>> FileImportHelper.checkProductExists(productId, delegator);
>> +
>> + if (productId != null &&
>> !productId.trim().equalsIgnoreCase("") && !productExists) {
>> +
>> products.add(FileImportHelper.prepareProduct(productId));
>> + if (quantityOnHand >= 0.0)
>> +
>> inventoryItems.add(FileImportHelper.prepareInventoryItem(productId,
>> quantityOnHand,
>> +
>> delegator.getNextSeqId("InventoryItem")));
>> + else
>> +
>> inventoryItems.add(FileImportHelper.prepareInventoryItem(productId, 0.0,
>> delegator
>> + .getNextSeqId("InventoryItem")));
>> + }
>> + int rowNum = row.getRowNum() + 1;
>> + if (row.toString() != null &&
>> !row.toString().trim().equalsIgnoreCase("") && products.size() > 0
>> + && !productExists) {
>> + Debug.logWarning("Row number " + rowNum + " not
>> imported from " + item.getName(), module);
>> + }
>> + }
>> + }
>> + // create and store values in "Product" and "InventoryItem"
>> entity
>> + // in database
>> + for (int j = 0; j < products.size(); j++) {
>> + GenericValue productGV = delegator.makeValue("Product",
>> (Map) products.get(j));
>> + GenericValue inventoryItemGV =
>> delegator.makeValue("InventoryItem", (Map) inventoryItems.get(j));
>> + if
>> (!FileImportHelper.checkProductExists(productGV.getString("productId"),
>> delegator)) {
>> + try {
>> + delegator.create(productGV);
>> + delegator.create(inventoryItemGV);
>> + } catch (GenericEntityException e) {
>> + Debug.logError("Cannot store product", module);
>> + return ServiceUtil.returnError("Cannot store
>> product");
>> + }
>> + }
>> + }
>> + int uploadedProducts = products.size() + 1;
>> + if (products.size() > 0)
>> + Debug.logInfo("Uploaded " + uploadedProducts + " products
>> from file " + item.getName(), module);
>> + }
>> + return responseMsgs;
>> + }
>> +}
>>
>> Propchange:
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportService.java
>>
>> ------------------------------------------------------------------------------
>> svn:eol-style = native
>>
>> Propchange:
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportService.java
>>
>> ------------------------------------------------------------------------------
>> svn:keywords = Date Rev Author URL Id
>>
>> Propchange:
>> ofbiz/trunk/specialpurpose/assetmaint/src/org/ofbiz/poi/FileImportService.java
>>
>> ------------------------------------------------------------------------------
>> svn:mime-type = text/plain
>>
>>
>>
>