Skip to content

Ninniku IT Hub

Provide open-source solutions for businesses.

Menu
  • Home
  • Technical Support
  • Traning
    • Arduino for IoT and ERP Integration
  • Expert Certification
  • Plug-ins (Taiwan)
    • Accounting for Taiwan
    • Meeting Room Booking
Menu

iDempiere Process: Updating inventory data through Excel.

Posted on 2023-03-10

The provided Java program UpdateInventoryLineByExcel is used to update the inventory line records in the iDempiere ERP software system by importing the data from an Excel file. The program reads the data from an Excel file and matches the data with the corresponding fields of the inventory line record in iDempiere. If a match is found, the program updates the inventory line record with the imported data.

The program reads the data from the Excel file and matches it with the corresponding fields of the inventory line record in iDempiere. If a match is found, the program updates the inventory line record with the imported data. If no match is found, the program moves on to the next row in the Excel file. The program updates the inventory line record only if the inventory is in the “Drafted” or “In Progress” status; otherwise, it throws an error.

The program first reads the column names from the Excel file and stores them in a map. It then iterates over each row in the Excel file, reads the data from the corresponding columns, and matches it with the corresponding fields of the inventory line record in iDempiere. If a match is found, the program updates the inventory line record with the imported data. The program also updates the counter variable with the number of inventory lines updated.

Note that this program is designed to work specifically with the iDempiere ERP software system and may not work with other systems without modifications. Additionally, the program assumes that the Excel file contains data in the expected format and may throw errors if the data is not in the expected format. Therefore, it is important to verify the data in the Excel file before importing it into the iDempiere ERP software system.

package tw.ninniku.mrp.process;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachment;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MAttributeSetInstance;
import org.compiere.model.MInventoryLine;
import org.compiere.model.MLocator;
import org.compiere.model.MProduct;
import org.compiere.model.Query;
import org.compiere.process.DocAction;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.AdempiereUserError;

import tw.ninniku.mrp.model.MInventoryTG;

public class UpdateInventoryLineByExcel extends SvrProcess {


	MInventoryTG inventory ;
	Map<String, Integer> columnmap = new HashMap<String,Integer>(); 
	private  String AD_Language;
	private String p_Locator = "儲位";
	private String p_Product = "產品";
	private String p_ASI = "批號";
	private String p_Quantity = "盤點數量";
	private String p_Description = "描述";
	
	private int counter = 0;

	@Override
	protected void prepare() {
		ProcessInfoParameter[] para = getParameter();

		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("Locator"))
				p_Locator = para[i].getParameterAsString();
			else if (name.equals("Product"))
				p_Product = para[i].getParameterAsString();
			else if (name.equals("ASI"))
				p_ASI= para[i].getParameterAsString();
			else if (name.equals("ASI"))
				p_ASI= para[i].getParameterAsString();
			else if (name.equals("Quantity"))
				p_Quantity =para[i].getParameterAsString();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		} 
		AD_Language =  getCtx().getProperty("#AD_Language");
		 inventory = new MInventoryTG(getCtx(), getRecord_ID(), get_TrxName());
		if(!inventory.getDocStatus().equals(DocAction.STATUS_Drafted) && !inventory.getDocStatus().equals(DocAction.STATUS_InProgress))
			throw new AdempiereUserError("草稿狀態才能更新盤點數量");
		
		if(inventory.getAttachment(true) == null)
			throw new AdempiereUserError("未上傳Excel File");
	}

	@Override
	protected String doIt() throws Exception {
		// TODO Auto-generated method stub
		MAttachment attachment = inventory.getAttachment(true);
		MAttachmentEntry[] entries = attachment.getEntries();
		for (MAttachmentEntry entry : entries) {
			System.out.println(entry.getName());
			updateByExcel(entry);
			
		}
		return "執行完畢! 更新了 " + counter + " 筆" ;
	}

	private void updateByExcel(MAttachmentEntry entry) {
		FileInputStream file;
		//System.out.println(getCtx());
		//System.out.println("AD_Language:" + AD_Language);
		try {
			file = new FileInputStream(entry.getFile());
		    XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            setColumnName(sheet);
            Iterator<Row> rowIterator = sheet.iterator();
            // pass first row
            rowIterator.next();
            while (rowIterator.hasNext()) 
            {
                Row row = rowIterator.next();
                String asi =null;
                           String description = null;
                try {
                    String locator =   row.getCell(columnmap.get(p_Locator)).getStringCellValue();
                    String product =   row.getCell(columnmap.get(p_Product)).getStringCellValue();
                    BigDecimal qtyCount =  new BigDecimal(row.getCell(columnmap.get(p_Quantity)).getNumericCellValue()) ;
                    if(row.getCell(columnmap.get(p_ASI))!=null)
                    {
                    	switch (row.getCell(columnmap.get(p_ASI)).getCellType().toString())
                    	{
                    		case "NUMERIC":
                    			asi = String.valueOf(row.getCell(columnmap.get(p_ASI)).getNumericCellValue()) ;
                    			break;
                    		
                    		case "STRING":
                    			asi = row.getCell(columnmap.get(p_ASI)).getStringCellValue();
                    			break;
                    	}
                    }
                    
                    if(row.getCell(columnmap.get(p_Description))!=null)
                    {
                    	//System.out.println(row.getCell(columnmap.get(p_Description)).getCellType().toString());
                    	switch (row.getCell(columnmap.get(p_Description)).getCellType().toString())
                    	{
                    		case "NUMERIC":
                    			description = String.valueOf(row.getCell(columnmap.get(p_Description)).getNumericCellValue()) ;
                    			break;
                    		
                    		case "STRING":
                        	    description = row.getCell(columnmap.get(p_Description)).getStringCellValue();
                    			break;
                    			
                    		case "BLANK":
                    			break;
                    			default:
                    				
                    	
                    	}
                    }
                    	   // description = row.getCell(columnmap.get(p_Description)).getStringCellValue();
                    
                    if(updateInventoryLine(locator,product,asi,qtyCount,description))
                    	counter++;
				} catch (Exception e) {
					System.out.println(e.getMessage());
					System.out.println();
					
				}


                
            }
            file.close();
            workbook.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
    

	}

	private boolean updateInventoryLine(String locator, String product, String asi, BigDecimal qtyCount, String description) {
		if(product == null || product.length()== 0)
			return false;
	  //  System.out.println(locator + "/" + product + "/" + asi +"/"+   qtyCount );
		String SQLWhere = "AD_Client_ID = ?";
		SQLWhere += " AND AD_Org_ID = ?";
		SQLWhere += " AND Value =  ? ";
		List<MLocator> locators =  new Query(getCtx(), MLocator.Table_Name, SQLWhere, get_TrxName())
				.setParameters(new Object[] {inventory.getAD_Client_ID(),inventory.getAD_Org_ID(),locator.trim()})
				.setOnlyActiveRecords(true)
				.list();
		if(locators.size() ==0)
			throw new AdempiereUserError("儲位找不到:" + locator);
		
		SQLWhere = "AD_Client_ID = ?";
		SQLWhere += " AND upper(Value) =  ? ";
		List<MProduct> products =  new Query(getCtx(), MProduct.Table_Name, SQLWhere, get_TrxName())
				.setParameters(new Object[] {inventory.getAD_Client_ID(),product.trim().toUpperCase()})
				.setOnlyActiveRecords(true)
				.list();
		
		if(products.size() ==0)
			throw new AdempiereUserError("產品編號找不到:" + product);
		
		if(asi == null)
		{
			asi = "";
		}
		
		
		/**
		 *  Query by lot
		 */
			SQLWhere = "M_Inventory_ID = ?";
			SQLWhere += " AND M_Locator_ID = ?";
			SQLWhere += " AND M_Product_ID = ?";
			SQLWhere += " AND exists( Select 1 From M_AttributeSetInstance where M_AttributeSetInstance_ID = M_InventoryLine.M_AttributeSetInstance_ID"
					+ " and trim(upper(coalesce(lot,''))) = ?)";
			List<MInventoryLine>			lines = new Query(getCtx(), MInventoryLine.Table_Name, SQLWhere, get_TrxName())
					.setParameters(new Object[] {inventory.getM_Inventory_ID(),locators.get(0).getM_Locator_ID(),products.get(0).getM_Product_ID(),asi.trim().toUpperCase()})
					.setOnlyActiveRecords(true)
					.list();
		
		if(lines.size()>0)
		{
			lines.get(0).setQtyCount(qtyCount);
			lines.get(0).setDescription(description);
			lines.get(0).save();
		}
		return true;
	}

	private void setColumnName(XSSFSheet sheet) {
		// TODO Auto-generated method stub
		Row row = sheet.getRow(0); //Get first row
        Iterator<Cell> cellIterator = row.cellIterator();
		 while (cellIterator.hasNext()) 
         {
             Cell cell = cellIterator.next();
             String columnname = null;
             //Check the cell type and format accordingly
             switch (cell.getCellType().toString()) 
             {
             		case "STRING":
             			columnname = cell.getStringCellValue();
             			break;
             		case "NUMERIC":
             			columnname = String.valueOf(cell.getNumericCellValue()) ;
             			break;                    			
             			
             		default:
             			columnname=  "Unknown";
             		System.out.println("Unknown Cell type:" +  cell.getCellType());	
                     break;
             }
             columnmap.put(columnname,cell.getColumnIndex()) ;
         }
		
		 Object[] fields =  new Object[] {p_Locator,p_Product,p_ASI,p_Quantity,p_Description};
		 
		 
	 	  for (Object field : fields) {
				
	 	     try {
	 	    	  
	 	    	  if(row.getCell(columnmap.get(field.toString())) == null)
	 	           	throw new AdempiereUserError("Excel首列未設定 " + field.toString() +" 欄位名稱" );
	 		} catch (Exception e) {
	 			throw new AdempiereUserError("Excel首列未設定 " + field.toString() +" 欄位名稱" );
	 		}
    		  
		  }	 
 

	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • Apple
  • iDempiere
  • IoT
  • IT Tools
  • Linux
  • Productivity
  • SAP
  • uncategorized
  • Wordpress

Tags

Access Arduino Bad Dept Booking Business One Button Confirmation Developer ERP Exam Free git github GPIO iDempiere Jasper Report Java Language LED Linux M1 MacOS Material Receipt Maven Meeting room Open Source OSGi Period Control Permission Plug-In PostgreSQL Potentiometer Premiere Pro Process PWM Raspberry PI Resistor Role Sales Management SWT Tips Ubuntu Video Editor Wordpress Workflow

Recent Posts

  • Unlocking Creative Potential: Top Open-Source Alternatives to Adobe Premiere Pro for Video Editing
  • Enhancing Meeting Room Efficiency with the iDempiere Meeting Room Booking Plug-in
  • How to Create a GitHub Repository from a Local Folder
  • Demystifying OSGi Service Ranking: A Comprehensive Guide
  • PostgreSQL Time Travel: Upgrading from Version 9.6 to 14

Meta

  • Register
  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©2023 Ninniku IT Hub | Design: Newspaperly WordPress Theme