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
}
}
English Version
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
}
}
日本語版
提供されたJavaプログラムUpdateInventoryLineByExcelは、Excelファイルからデータをインポートすることで、iDempiere ERPソフトウェアシステムの在庫明細レコードを更新するために使用されます。プログラムはExcelファイルからデータを読み取り、iDempiereの在庫明細レコードの対応するフィールドとデータを照合します。一致が見つかった場合、プログラムはインポートされたデータで在庫明細レコードを更新します。
プログラムはExcelファイルからデータを読み取り、iDempiereの在庫明細レコードの対応するフィールドと照合します。一致が見つかった場合、プログラムはインポートされたデータで在庫明細レコードを更新します。一致が見つからない場合、プログラムはExcelファイルの次の行に進みます。プログラムは、在庫が「下書き」または「進行中」のステータスの場合のみ在庫明細レコードを更新し、それ以外の場合はエラーをスローします。
プログラムはまずExcelファイルから列名を読み取り、マップに格納します。次に、Excelファイルの各行を反復処理し、対応する列からデータを読み取り、iDempiereの在庫明細レコードの対応するフィールドと照合します。一致が見つかった場合、プログラムはインポートされたデータで在庫明細レコードを更新します。プログラムはまた、更新された在庫明細の数でカウンター変数を更新します。
このプログラムはiDempiere ERPソフトウェアシステム専用に設計されており、変更なしでは他のシステムでは動作しない可能性があることに注意してください。さらに、プログラムはExcelファイルに期待される形式のデータが含まれていることを前提としており、データが期待される形式でない場合はエラーをスローする可能性があります。したがって、iDempiere ERPソフトウェアシステムにインポートする前に、Excelファイルのデータを確認することが重要です。
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 = "";
}
/**
* ロットで検索
*/
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); //最初の行を取得
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
String columnname = null;
//セルタイプを確認し、適切にフォーマット
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
}
}
