Example 1 with X_M_Product_Classification
use of org.compiere.model.X_M_Product_Classification in project adempiere by adempiere.
the class ImportProduct method doIt.
// prepare
/**
* Perform process.
* @return Message
* @throws Exception
*/
protected String doIt() throws java.lang.Exception {
StringBuffer sql = null;
int no = 0;
String clientCheck = getWhereClause();
// Delete Old Imported
if (m_deleteOldImported) {
sql = new StringBuffer("DELETE I_Product " + "WHERE I_IsImported='Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Delete Old Imported =" + no);
}
// Set Client, Org, IaActive, Created/Updated, ProductType
sql = new StringBuffer("UPDATE I_Product " + "SET AD_Client_ID = COALESCE (AD_Client_ID, ").append(m_AD_Client_ID).append(")," + " AD_Org_ID = COALESCE (AD_Org_ID, 0)," + " IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " ProductType = COALESCE (ProductType, 'I')," + " I_ErrorMsg = ' '," + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Reset=" + no);
ModelValidationEngine.get().fireImportValidate(this, null, null, ImportValidator.TIMING_BEFORE_VALIDATE);
// Set Optional BPartner
sql = new StringBuffer("UPDATE I_Product i " + "SET C_BPartner_ID=(SELECT C_BPartner_ID FROM C_BPartner p" + " WHERE i.BPartner_Value=p.Value AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("BPartner=" + no);
//
sql = new StringBuffer("UPDATE I_Product " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid BPartner,' " + "WHERE C_BPartner_ID IS NULL AND BPartner_Value IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid BPartner=" + no);
// **** Find Product
// EAN/UPC
sql = new StringBuffer("UPDATE I_Product i " + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p" + " WHERE i.UPC=p.UPC AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Product Existing UPC=" + no);
// Value
sql = new StringBuffer("UPDATE I_Product i " + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p" + " WHERE i.Value=p.Value AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Product Existing Value=" + no);
// BP ProdNo
sql = new StringBuffer("UPDATE I_Product i " + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product_po p" + " WHERE i.C_BPartner_ID=p.C_BPartner_ID" + " AND i.VendorProductNo=p.VendorProductNo AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Product Existing Vendor ProductNo=" + no);
// Set Product Category
sql = new StringBuffer("UPDATE I_Product " + "SET ProductCategory_Value=(SELECT MAX(Value) FROM M_Product_Category" + " WHERE IsDefault='Y' AND AD_Client_ID=").append(m_AD_Client_ID).append(") " + "WHERE ProductCategory_Value IS NULL AND M_Product_Category_ID IS NULL" + // set category only if product not found
" AND M_Product_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Category Default Value=" + no);
//
sql = new StringBuffer("UPDATE I_Product i " + "SET M_Product_Category_ID=(SELECT M_Product_Category_ID FROM M_Product_Category c" + " WHERE i.ProductCategory_Value=c.Value AND i.AD_Client_ID=c.AD_Client_ID) " + "WHERE ProductCategory_Value IS NOT NULL AND M_Product_Category_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Set Category=" + no);
// Since 3.8.1 - Set Product Class
sql = new StringBuffer("UPDATE I_Product " + "SET ProductClass_Value=(SELECT MAX(Value) FROM M_Product_Class" + " WHERE IsDefault='Y' AND AD_Client_ID=").append(m_AD_Client_ID).append(") " + "WHERE ProductClass_Value IS NULL AND M_Product_Class_ID IS NULL" + // set class only if product not found
" AND M_Product_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Class Default Value=" + no);
//
sql = new StringBuffer("UPDATE I_Product i " + "SET M_Product_Class_ID=(SELECT M_Product_Class_ID FROM M_Product_Class c" + " WHERE i.ProductClass_Value=c.Value AND i.AD_Client_ID=c.AD_Client_ID) " + "WHERE ProductClass_Value IS NOT NULL AND M_Product_Class_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Set Class=" + no);
// Since 3.8.1 - Set Product Classification
sql = new StringBuffer("UPDATE I_Product " + "SET ProductClassification_Value=(SELECT MAX(Value) FROM M_Product_Classification" + " WHERE IsDefault='Y' AND AD_Client_ID=").append(m_AD_Client_ID).append(") " + "WHERE ProductClassification_Value IS NULL AND M_Product_Classification_ID IS NULL" + // set Classification only if product not found
" AND M_Product_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Classification Default Value=" + no);
//
sql = new StringBuffer("UPDATE I_Product i " + "SET M_Product_Classification_ID=(SELECT M_Product_Classification_ID FROM M_Product_Classification c" + " WHERE i.ProductClassification_Value=c.Value AND i.AD_Client_ID=c.AD_Client_ID) " + "WHERE ProductClassification_Value IS NOT NULL AND M_Product_Classification_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Set Classification=" + no);
// Since 3.8.1 - Set Product Group
sql = new StringBuffer("UPDATE I_Product " + "SET ProductGroup_Value=(SELECT MAX(Value) FROM M_Product_Group" + " WHERE IsDefault='Y' AND AD_Client_ID=").append(m_AD_Client_ID).append(") " + "WHERE ProductGroup_Value IS NULL AND M_Product_Group_ID IS NULL" + // set Group only if product not found
" AND M_Product_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Group Default Value=" + no);
//
sql = new StringBuffer("UPDATE I_Product i " + "SET M_Product_Group_ID=(SELECT M_Product_Group_ID FROM M_Product_Group c" + " WHERE i.ProductGroup_Value=c.Value AND i.AD_Client_ID=c.AD_Client_ID) " + "WHERE ProductGroup_Value IS NOT NULL AND M_Product_Group_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Set Group=" + no);
// Copy From Product if Import does not have value
String[] strFields = new String[] { "Value", "Name", "Description", "DocumentNote", "Help", "UPC", "SKU", "Classification", "ProductType", "Discontinued", "DiscontinuedBy", "DiscontinuedAt", "ImageURL", "DescriptionURL" };
for (int i = 0; i < strFields.length; i++) {
sql = new StringBuffer("UPDATE I_Product i " + "SET ").append(strFields[i]).append(" = (SELECT ").append(strFields[i]).append(" FROM M_Product p" + " WHERE i.M_Product_ID=p.M_Product_ID AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NOT NULL" + " AND ").append(strFields[i]).append(" IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.fine(strFields[i] + " - default from existing Product=" + no);
}
String[] numFields = new String[] { "C_UOM_ID", "M_Product_Category_ID", "M_Product_Class_ID", "M_Product_Classification_ID", "M_Product_Group_ID", "Volume", "Weight", "ShelfWidth", "ShelfHeight", "ShelfDepth", "UnitsPerPallet" };
for (int i = 0; i < numFields.length; i++) {
sql = new StringBuffer("UPDATE I_PRODUCT i " + "SET ").append(numFields[i]).append(" = (SELECT ").append(numFields[i]).append(" FROM M_Product p" + " WHERE i.M_Product_ID=p.M_Product_ID AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NOT NULL" + " AND (").append(numFields[i]).append(" IS NULL OR ").append(numFields[i]).append("=0)" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.fine(numFields[i] + " default from existing Product=" + no);
}
// Copy From Product_PO if Import does not have value
String[] strFieldsPO = new String[] { "UPC", "PriceEffective", "VendorProductNo", "VendorCategory", "Manufacturer", "Discontinued", "DiscontinuedBy", "DiscontinuedAt" };
for (int i = 0; i < strFieldsPO.length; i++) {
sql = new StringBuffer("UPDATE I_PRODUCT i " + "SET ").append(strFieldsPO[i]).append(" = (SELECT ").append(strFieldsPO[i]).append(" FROM M_Product_PO p" + " WHERE i.M_Product_ID=p.M_Product_ID AND i.C_BPartner_ID=p.C_BPartner_ID AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NOT NULL AND C_BPartner_ID IS NOT NULL" + " AND ").append(strFieldsPO[i]).append(" IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.fine(strFieldsPO[i] + " default from existing Product PO=" + no);
}
String[] numFieldsPO = new String[] { "C_UOM_ID", "C_Currency_ID", "PriceList", "PricePO", "RoyaltyAmt", "Order_Min", "Order_Pack", "CostPerOrder", "DeliveryTime_Promised" };
for (int i = 0; i < numFieldsPO.length; i++) {
sql = new StringBuffer("UPDATE I_PRODUCT i " + "SET ").append(numFieldsPO[i]).append(" = (SELECT ").append(numFieldsPO[i]).append(" FROM M_Product_PO p" + " WHERE i.M_Product_ID=p.M_Product_ID AND i.C_BPartner_ID=p.C_BPartner_ID AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NOT NULL AND C_BPartner_ID IS NOT NULL" + " AND (").append(numFieldsPO[i]).append(" IS NULL OR ").append(numFieldsPO[i]).append("=0)" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.fine(numFieldsPO[i] + " default from existing Product PO=" + no);
}
// Invalid Category
sql = new StringBuffer("UPDATE I_Product " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid ProdCategory,' " + "WHERE M_Product_Category_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid Category=" + no);
// Set UOM (System/own)
sql = new StringBuffer("UPDATE I_Product i " + "SET X12DE355 = " + "(SELECT MAX(X12DE355) FROM C_UOM u WHERE u.IsDefault='Y' AND u.AD_Client_ID IN (0,i.AD_Client_ID)) " + "WHERE X12DE355 IS NULL AND C_UOM_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set UOM Default=" + no);
//
sql = new StringBuffer("UPDATE I_Product i " + "SET C_UOM_ID = (SELECT C_UOM_ID FROM C_UOM u WHERE u.X12DE355=i.X12DE355 AND u.AD_Client_ID IN (0,i.AD_Client_ID)) " + "WHERE C_UOM_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Set UOM=" + no);
//
sql = new StringBuffer("UPDATE I_Product " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid UOM, ' " + "WHERE C_UOM_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid UOM=" + no);
// Set Currency
sql = new StringBuffer("UPDATE I_Product i " + "SET ISO_Code=(SELECT ISO_Code FROM C_Currency c" + " INNER JOIN C_AcctSchema a ON (a.C_Currency_ID=c.C_Currency_ID)" + " INNER JOIN AD_ClientInfo ci ON (a.C_AcctSchema_ID=ci.C_AcctSchema1_ID)" + " WHERE ci.AD_Client_ID=i.AD_Client_ID) " + "WHERE C_Currency_ID IS NULL AND ISO_Code IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Currency Default=" + no);
//
sql = new StringBuffer("UPDATE I_Product i " + "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_Currency c" + " WHERE i.ISO_Code=c.ISO_Code AND c.AD_Client_ID IN (0,i.AD_Client_ID)) " + "WHERE C_Currency_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("doIt- Set Currency=" + no);
//
sql = new StringBuffer("UPDATE I_Product " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Currency,' " + "WHERE C_Currency_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid Currency=" + no);
// Verify ProductType
sql = new StringBuffer("UPDATE I_Product " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid ProductType,' " + "WHERE ProductType NOT IN ('E','I','R','S')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid ProductType=" + no);
// Unique UPC/Value
sql = new StringBuffer("UPDATE I_Product i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Value not unique,' " + "WHERE I_IsImported<>'Y'" + " AND Value IN (SELECT Value FROM I_Product ii WHERE i.AD_Client_ID=ii.AD_Client_ID GROUP BY Value HAVING COUNT(*) > 1)").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Not Unique Value=" + no);
//
sql = new StringBuffer("UPDATE I_Product i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=UPC not unique,' " + "WHERE I_IsImported<>'Y'" + " AND UPC IN (SELECT UPC FROM I_Product ii WHERE i.AD_Client_ID=ii.AD_Client_ID GROUP BY UPC HAVING COUNT(*) > 1)").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Not Unique UPC=" + no);
// Mandatory Value
sql = new StringBuffer("UPDATE I_Product i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Mandatory Value,' " + "WHERE Value IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("No Mandatory Value=" + no);
// Vendor Product No
// sql = new StringBuffer ("UPDATE I_Product i "
// + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Mandatory VendorProductNo,' "
// + "WHERE I_IsImported<>'Y'"
// + " AND VendorProductNo IS NULL AND (C_BPartner_ID IS NOT NULL OR BPartner_Value IS NOT NULL)").append(clientCheck);
// no = DB.executeUpdate(sql.toString(), get_TrxName());
// log.info(log.l3_Util, "No Mandatory VendorProductNo=" + no);
sql = new StringBuffer("UPDATE I_Product " + "SET VendorProductNo=Value " + "WHERE C_BPartner_ID IS NOT NULL AND VendorProductNo IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("VendorProductNo Set to Value=" + no);
//
sql = new StringBuffer("UPDATE I_Product i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=VendorProductNo not unique,' " + "WHERE I_IsImported<>'Y'" + " AND C_BPartner_ID IS NOT NULL" + " AND (C_BPartner_ID, VendorProductNo) IN " + " (SELECT C_BPartner_ID, VendorProductNo FROM I_Product ii WHERE i.AD_Client_ID=ii.AD_Client_ID GROUP BY C_BPartner_ID, VendorProductNo HAVING COUNT(*) > 1)").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Not Unique VendorProductNo=" + no);
// Get Default Tax Category
int C_TaxCategory_ID = 0;
try {
PreparedStatement pstmt = DB.prepareStatement("SELECT C_TaxCategory_ID FROM C_TaxCategory WHERE IsDefault='Y'" + clientCheck, get_TrxName());
ResultSet rs = pstmt.executeQuery();
if (rs.next())
C_TaxCategory_ID = rs.getInt(1);
rs.close();
pstmt.close();
} catch (SQLException e) {
throw new Exception("TaxCategory", e);
}
log.fine("C_TaxCategory_ID=" + C_TaxCategory_ID);
ModelValidationEngine.get().fireImportValidate(this, null, null, ImportValidator.TIMING_AFTER_VALIDATE);
commitEx();
// -------------------------------------------------------------------
int noInsert = 0;
int noUpdate = 0;
int noInsertPO = 0;
int noUpdatePO = 0;
// Check for new Product Class, Classification or Group
// Go through Records
log.fine("start inserting/updating ...");
sql = new StringBuffer("SELECT * FROM I_Product WHERE I_IsImported='N'").append(clientCheck);
try {
/* Insert Product from Import
PreparedStatement pstmt_insertProduct = conn.prepareStatement
("INSERT INTO M_Product (M_Product_ID,"
+ "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
+ "Value,Name,Description,DocumentNote,Help,"
+ "UPC,SKU,C_UOM_ID,IsSummary,M_Product_Category_ID,C_TaxCategory_ID,"
+ "ProductType,ImageURL,DescriptionURL) "
+ "SELECT ?,"
+ "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
+ "Value,Name,Description,DocumentNote,Help,"
+ "UPC,SKU,C_UOM_ID,'N',M_Product_Category_ID," + C_TaxCategory_ID + ","
+ "ProductType,ImageURL,DescriptionURL "
+ "FROM I_Product "
+ "WHERE I_Product_ID=?");
*/
// Update Product from Import
//jz moved
/*
String sqlt = "UPDATE M_PRODUCT "
+ "SET (Value,Name,Description,DocumentNote,Help,"
+ "UPC,SKU,C_UOM_ID,M_Product_Category_ID,Classification,ProductType,"
+ "Volume,Weight,ShelfWidth,ShelfHeight,ShelfDepth,UnitsPerPallet,"
+ "Discontinued,DiscontinuedBy,Updated,UpdatedBy)= "
+ "(SELECT Value,Name,Description,DocumentNote,Help,"
+ "UPC,SKU,C_UOM_ID,M_Product_Category_ID,Classification,ProductType,"
+ "Volume,Weight,ShelfWidth,ShelfHeight,ShelfDepth,UnitsPerPallet,"
+ "Discontinued,DiscontinuedBy,SysDate,UpdatedBy"
+ " FROM I_Product WHERE I_Product_ID=?) "
+ "WHERE M_Product_ID=?";
PreparedStatement pstmt_updateProduct = DB.prepareStatement
(sqlt, get_TrxName());
// Update Product_PO from Import
sqlt = "UPDATE M_Product_PO "
+ "SET (IsCurrentVendor,C_UOM_ID,C_Currency_ID,UPC,"
+ "PriceList,PricePO,RoyaltyAmt,PriceEffective,"
+ "VendorProductNo,VendorCategory,Manufacturer,"
+ "Discontinued,DiscontinuedBy,Order_Min,Order_Pack,"
+ "CostPerOrder,DeliveryTime_Promised,Updated,UpdatedBy)= "
+ "(SELECT 'Y',C_UOM_ID,C_Currency_ID,UPC,"
+ "PriceList,PricePO,RoyaltyAmt,PriceEffective,"
+ "VendorProductNo,VendorCategory,Manufacturer,"
+ "Discontinued,DiscontinuedBy,Order_Min,Order_Pack,"
+ "CostPerOrder,DeliveryTime_Promised,SysDate,UpdatedBy"
+ " FROM I_Product"
+ " WHERE I_Product_ID=?) "
+ "WHERE M_Product_ID=? AND C_BPartner_ID=?";
PreparedStatement pstmt_updateProductPO = DB.prepareStatement
(sqlt, get_TrxName());
*/
// Insert Product from Import
PreparedStatement pstmt_insertProductPO = DB.prepareStatement("INSERT INTO M_Product_PO (M_Product_ID,C_BPartner_ID, " + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy," + "IsCurrentVendor,C_UOM_ID,C_Currency_ID,UPC," + "PriceList,PricePO,RoyaltyAmt,PriceEffective," + "VendorProductNo,VendorCategory,Manufacturer," + "Discontinued,DiscontinuedBy, DiscontinuedAt, Order_Min,Order_Pack," + "CostPerOrder,DeliveryTime_Promised) " + "SELECT ?,?, " + "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy," + "'Y',C_UOM_ID,C_Currency_ID,UPC," + "PriceList,PricePO,RoyaltyAmt,PriceEffective," + "VendorProductNo,VendorCategory,Manufacturer," + "Discontinued,DiscontinuedBy, DiscontinuedAt, Order_Min,Order_Pack," + "CostPerOrder,DeliveryTime_Promised " + "FROM I_Product " + "WHERE I_Product_ID=?", get_TrxName());
// Set Imported = Y
PreparedStatement pstmt_setImported = DB.prepareStatement("UPDATE I_Product SET I_IsImported='Y', M_Product_ID=?, " + "Updated=SysDate, Processed='Y' WHERE I_Product_ID=?", get_TrxName());
//
PreparedStatement pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
X_I_Product imp = new X_I_Product(getCtx(), rs, get_TrxName());
int I_Product_ID = imp.getI_Product_ID();
int M_Product_ID = imp.getM_Product_ID();
int C_BPartner_ID = imp.getC_BPartner_ID();
boolean newProduct = M_Product_ID == 0;
log.fine("I_Product_ID=" + I_Product_ID + ", M_Product_ID=" + M_Product_ID + ", C_BPartner_ID=" + C_BPartner_ID);
// Since 3.8.1 Check and add product Class, Classification and Group
Boolean saveNeeded = false;
// Class
if (imp.getM_Product_Class_ID() == 0 && imp.getProductClass_Value() != null && !imp.getProductClass_Value().isEmpty()) {
String value = imp.getProductClass_Value();
String whereClause = "VALUE= '" + value + "'";
// Have we already added it?
X_M_Product_Class pClass = MTable.get(getCtx(), X_M_Product_Class.Table_ID).createQuery(whereClause, // will lock the table
get_TrxName()).setOnlyActiveRecords(true).setClient_ID().first();
if (pClass == null) {
pClass = new X_M_Product_Class(getCtx(), 0, get_TrxName());
pClass.setValue(imp.getProductClass_Value());
pClass.setName(imp.getProductClass_Value());
pClass.setIsActive(true);
pClass.setIsDefault(false);
pClass.saveEx();
}
imp.setM_Product_Class_ID(pClass.getM_Product_Class_ID());
pClass = null;
saveNeeded = true;
}
// Classification
if (imp.getM_Product_Classification_ID() == 0 && imp.getProductClassification_Value() != null && !imp.getProductClassification_Value().isEmpty()) {
String value = imp.getProductClassification_Value();
String whereClause = "VALUE= '" + value + "'";
// Have we already added it?
X_M_Product_Classification pClassification = MTable.get(getCtx(), X_M_Product_Classification.Table_ID).createQuery(whereClause, // will lock the table
get_TrxName()).setOnlyActiveRecords(true).setClient_ID().first();
if (pClassification == null) {
pClassification = new X_M_Product_Classification(getCtx(), 0, get_TrxName());
pClassification.setValue(imp.getProductClassification_Value());
pClassification.setName(imp.getProductClassification_Value());
pClassification.setIsActive(true);
pClassification.setIsDefault(false);
pClassification.saveEx();
}
imp.setM_Product_Classification_ID(pClassification.getM_Product_Classification_ID());
pClassification = null;
saveNeeded = true;
}
// Group
if (imp.getM_Product_Group_ID() == 0 && imp.getProductGroup_Value() != null && !imp.getProductGroup_Value().isEmpty()) {
String value = imp.getProductGroup_Value();
String whereClause = "VALUE= '" + value + "'";
// Have we already added it?
X_M_Product_Group pGroup = MTable.get(getCtx(), X_M_Product_Group.Table_ID).createQuery(whereClause, // will lock the table
get_TrxName()).setOnlyActiveRecords(true).setClient_ID().first();
if (pGroup == null) {
pGroup = new X_M_Product_Group(getCtx(), 0, get_TrxName());
pGroup.setValue(imp.getProductGroup_Value());
pGroup.setName(imp.getProductGroup_Value());
pGroup.setIsActive(true);
pGroup.setIsDefault(false);
pGroup.saveEx();
}
imp.setM_Product_Group_ID(pGroup.getM_Product_Group_ID());
pGroup = null;
saveNeeded = true;
}
if (saveNeeded)
imp.saveEx();
// Product
if (// Insert new Product
newProduct) {
MProduct product = new MProduct(imp);
product.setC_TaxCategory_ID(C_TaxCategory_ID);
ModelValidationEngine.get().fireImportValidate(this, imp, product, ImportValidator.TIMING_AFTER_IMPORT);
if (product.save()) {
M_Product_ID = product.getM_Product_ID();
log.finer("Insert Product");
noInsert++;
} else {
StringBuffer sql0 = new StringBuffer("UPDATE I_Product i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert Product failed")).append("WHERE I_Product_ID=").append(I_Product_ID);
DB.executeUpdate(sql0.toString(), get_TrxName());
continue;
}
} else // Update Product
{
String sqlt = "UPDATE M_PRODUCT " + "SET (Value,Name,Description,DocumentNote,Help," + "UPC,SKU,C_UOM_ID,M_Product_Category_ID,Classification,ProductType," + "M_Product_Class_ID, M_Product_Classification_ID, M_Product_Group_ID," + "Volume,Weight,ShelfWidth,ShelfHeight,ShelfDepth,UnitsPerPallet," + "Discontinued,DiscontinuedBy, DiscontinuedAt, Updated,UpdatedBy)= " + "(SELECT Value,Name,Description,DocumentNote,Help," + "UPC,SKU,C_UOM_ID,M_Product_Category_ID,Classification,ProductType," + "M_Product_Class_ID, M_Product_Classification_ID, M_Product_Group_ID," + "Volume,Weight,ShelfWidth,ShelfHeight,ShelfDepth,UnitsPerPallet," + "Discontinued,DiscontinuedBy, DiscontinuedAt, SysDate,UpdatedBy" + " FROM I_Product WHERE I_Product_ID=" + I_Product_ID + ") " + "WHERE M_Product_ID=" + M_Product_ID;
PreparedStatement pstmt_updateProduct = DB.prepareStatement(sqlt, get_TrxName());
// pstmt_updateProduct.setInt(2, M_Product_ID);
try {
no = pstmt_updateProduct.executeUpdate();
log.finer("Update Product = " + no);
noUpdate++;
} catch (SQLException ex) {
log.warning("Update Product - " + ex.toString());
StringBuffer sql0 = new StringBuffer("UPDATE I_Product i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update Product: " + ex.toString())).append("WHERE I_Product_ID=").append(I_Product_ID);
DB.executeUpdate(sql0.toString(), get_TrxName());
continue;
}
pstmt_updateProduct.close();
}
// Do we have PO Info
if (C_BPartner_ID != 0) {
no = 0;
// If Product existed, Try to Update first
if (!newProduct) {
String sqlt = "UPDATE M_Product_PO " + "SET (IsCurrentVendor,C_UOM_ID,C_Currency_ID,UPC," + "PriceList,PricePO,RoyaltyAmt,PriceEffective," + "VendorProductNo,VendorCategory,Manufacturer," + "Discontinued,DiscontinuedBy, DiscontinuedAt, Order_Min,Order_Pack," + "CostPerOrder,DeliveryTime_Promised,Updated,UpdatedBy)= " + //jz fix EDB unknown datatype error
"(SELECT CAST('Y' AS CHAR),C_UOM_ID,C_Currency_ID,UPC," + "PriceList,PricePO,RoyaltyAmt,PriceEffective," + "VendorProductNo,VendorCategory,Manufacturer," + "Discontinued,DiscontinuedBy, DiscontinuedAt, Order_Min,Order_Pack," + "CostPerOrder,DeliveryTime_Promised,SysDate,UpdatedBy" + " FROM I_Product" + " WHERE I_Product_ID=" + I_Product_ID + ") " + "WHERE M_Product_ID=" + M_Product_ID + " AND C_BPartner_ID=" + C_BPartner_ID;
PreparedStatement pstmt_updateProductPO = DB.prepareStatement(sqlt, get_TrxName());
// pstmt_updateProductPO.setInt(3, C_BPartner_ID);
try {
no = pstmt_updateProductPO.executeUpdate();
log.finer("Update Product_PO = " + no);
noUpdatePO++;
} catch (SQLException ex) {
log.warning("Update Product_PO - " + ex.toString());
noUpdate--;
rollback();
StringBuffer sql0 = new StringBuffer("UPDATE I_Product i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update Product_PO: " + ex.toString())).append("WHERE I_Product_ID=").append(I_Product_ID);
DB.executeUpdate(sql0.toString(), get_TrxName());
continue;
}
pstmt_updateProductPO.close();
}
if (// Insert PO
no == 0) {
pstmt_insertProductPO.setInt(1, M_Product_ID);
pstmt_insertProductPO.setInt(2, C_BPartner_ID);
pstmt_insertProductPO.setInt(3, I_Product_ID);
try {
no = pstmt_insertProductPO.executeUpdate();
log.finer("Insert Product_PO = " + no);
noInsertPO++;
} catch (SQLException ex) {
log.warning("Insert Product_PO - " + ex.toString());
// assume that product also did not exist
noInsert--;
rollback();
StringBuffer sql0 = new StringBuffer("UPDATE I_Product i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert Product_PO: " + ex.toString())).append("WHERE I_Product_ID=").append(I_Product_ID);
DB.executeUpdate(sql0.toString(), get_TrxName());
continue;
}
}
}
// Price List
if (p_M_PriceList_Version_ID != 0) {
BigDecimal PriceList = imp.getPriceList();
BigDecimal PriceStd = imp.getPriceStd();
BigDecimal PriceLimit = imp.getPriceLimit();
if (PriceStd.signum() != 0 || PriceLimit.signum() != 0 || PriceList.signum() != 0) {
MProductPrice pp = MProductPrice.get(getCtx(), p_M_PriceList_Version_ID, M_Product_ID, get_TrxName());
if (pp == null)
pp = new MProductPrice(getCtx(), p_M_PriceList_Version_ID, M_Product_ID, get_TrxName());
pp.setPrices(PriceList, PriceStd, PriceLimit);
ModelValidationEngine.get().fireImportValidate(this, imp, pp, ImportValidator.TIMING_AFTER_IMPORT);
pp.saveEx();
}
}
// Update I_Product
pstmt_setImported.setInt(1, M_Product_ID);
pstmt_setImported.setInt(2, I_Product_ID);
no = pstmt_setImported.executeUpdate();
//
commitEx();
}
// for all I_Product
rs.close();
pstmt.close();
//
// pstmt_insertProduct.close();
// pstmt_updateProduct.close();
pstmt_insertProductPO.close();
// pstmt_updateProductPO.close();
pstmt_setImported.close();
//
} catch (SQLException e) {
}
// Set Error to indicator to not imported
sql = new StringBuffer("UPDATE I_Product " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
addLog(0, null, new BigDecimal(no), "@Errors@");
addLog(0, null, new BigDecimal(noInsert), "@M_Product_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noUpdate), "@M_Product_ID@: @Updated@");
addLog(0, null, new BigDecimal(noInsertPO), "@M_Product_ID@ @Purchase@: @Inserted@");
addLog(0, null, new BigDecimal(noUpdatePO), "@M_Product_ID@ @Purchase@: @Updated@");
return "";
}
Also used :
MProduct(org.compiere.model.MProduct)
SQLException(java.sql.SQLException)
X_M_Product_Class(org.compiere.model.X_M_Product_Class)
PreparedStatement(java.sql.PreparedStatement)
X_I_Product(org.compiere.model.X_I_Product)
SQLException(java.sql.SQLException)
BigDecimal(java.math.BigDecimal)
X_M_Product_Group(org.compiere.model.X_M_Product_Group)
ResultSet(java.sql.ResultSet)
X_M_Product_Classification(org.compiere.model.X_M_Product_Classification)
MProductPrice(org.compiere.model.MProductPrice)
Aggregations
BigDecimal (java.math.BigDecimal)1
PreparedStatement (java.sql.PreparedStatement)1
ResultSet (java.sql.ResultSet)1
SQLException (java.sql.SQLException)1
MProduct (org.compiere.model.MProduct)1
MProductPrice (org.compiere.model.MProductPrice)1
X_I_Product (org.compiere.model.X_I_Product)1
X_M_Product_Class (org.compiere.model.X_M_Product_Class)1
X_M_Product_Classification (org.compiere.model.X_M_Product_Classification)1
X_M_Product_Group (org.compiere.model.X_M_Product_Group)1
