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
}
}