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 < [EMAIL PROTECTED]> 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, [EMAIL PROTECTED] 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 >> >> >> >
