Example 1 with MProductPrice
use of org.compiere.model.MProductPrice 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)
Example 2 with MProductPrice
use of org.compiere.model.MProductPrice in project adempiere by adempiere.
the class InventoryUtil method getCreateProductPrice.
public static MProductPrice getCreateProductPrice(String ctxPriceList, int M_Product_ID, int price) {
Properties ctx = Env.getCtx();
int M_PriceList_ID = Env.getContextAsInt(ctx, ctxPriceList);
MPriceList pl = MPriceList.get(ctx, M_PriceList_ID, null);
MPriceListVersion plv = pl.getPriceListVersion(null);
//
BigDecimal priceBD = BigDecimal.valueOf(price);
MProductPrice pp = MProductPrice.get(ctx, plv.get_ID(), M_Product_ID, null);
if (pp == null) {
pp = new MProductPrice(plv, M_Product_ID, priceBD, priceBD, priceBD);
}
pp.setPrices(priceBD, priceBD, priceBD);
pp.saveEx();
return pp;
}
Also used :
MPriceListVersion(org.compiere.model.MPriceListVersion)
MPriceList(org.compiere.model.MPriceList)
Properties(java.util.Properties)
BigDecimal(java.math.BigDecimal)
MProductPrice(org.compiere.model.MProductPrice)
Example 3 with MProductPrice
use of org.compiere.model.MProductPrice in project adempiere by adempiere.
the class WAttributeGrid method addProduct.
// getGridElement
/**
* Add Product
* @param element panel
* @param product product
*/
private void addProduct(Panel element, MProduct product) {
int M_Product_ID = product.getM_Product_ID();
Vbox pe = new Vbox();
pe.setStyle("border-width: thin; border-color: blue;");
// Product Value - Price
pe.appendChild(new Label(product.getValue()));
String formatted = "";
if (m_M_PriceList_Version_ID != 0) {
MProductPrice pp = MProductPrice.get(Env.getCtx(), m_M_PriceList_Version_ID, M_Product_ID, null);
if (pp != null) {
BigDecimal price = pp.getPriceStd();
formatted = m_price.format(price);
} else
formatted = "-";
}
pe.appendChild(new Label(formatted));
// Product Name - Qty
pe.appendChild(new Label(product.getName()));
formatted = "";
if (m_M_Warehouse_ID != 0) {
BigDecimal qty = MStorage.getQtyAvailable(m_M_Warehouse_ID, M_Product_ID, 0, null);
if (qty == null)
formatted = "-";
else
formatted = m_qty.format(qty);
}
pe.appendChild(new Label(formatted));
//
element.appendChild(pe);
}
Also used :
Label(org.adempiere.webui.component.Label)
Vbox(org.zkoss.zul.Vbox)
BigDecimal(java.math.BigDecimal)
MProductPrice(org.compiere.model.MProductPrice)
Example 4 with MProductPrice
use of org.compiere.model.MProductPrice in project adempiere by adempiere.
the class ImportPriceList method doIt.
// prepare
/**
* Perform process.
* @return Message
* @throws Exception
*/
protected String doIt() throws Exception {
StringBuffer sql = null;
int no = 0;
String clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID;
int m_discountschema_id = DB.getSQLValue(get_TrxName(), "SELECT MIN(M_DiscountSchema_ID) FROM M_DiscountSchema WHERE DiscountType='P' AND IsActive='Y' AND AD_Client_ID=?", m_AD_Client_ID);
if (m_discountschema_id <= 0)
throw new AdempiereUserError("Price List Schema not configured");
// Delete Old Imported
if (m_deleteOldImported) {
sql = new StringBuffer("DELETE I_PriceList " + "WHERE I_IsImported='Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Delete Old Impored =" + no);
}
// Set Client, Org, IsActive, Created/Updated, EnforcePriceLimit, IsSOPriceList, IsTaxIncluded, PricePrecision
sql = new StringBuffer("UPDATE I_PriceList " + "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)," + " EnforcePriceLimit = COALESCE (EnforcePriceLimit, 'N')," + " IsSOPriceList = COALESCE (IsSOPriceList, 'N')," + " IsTaxIncluded = COALESCE (IsTaxIncluded, 'N')," + " PricePrecision = COALESCE (PricePrecision, 2)," + " 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);
// Set Optional BPartner
sql = new StringBuffer("UPDATE I_PriceList " + "SET C_BPartner_ID=(SELECT C_BPartner_ID FROM C_BPartner p" + " WHERE I_PriceList.BPartner_Value=p.Value AND I_PriceList.AD_Client_ID=p.AD_Client_ID) " + "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());
log.info("BPartner=" + no);
//
sql = new StringBuffer("UPDATE I_PriceList " + "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);
// Product
sql = new StringBuffer("UPDATE I_PriceList " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE I_PriceList.ProductValue=p.Value AND I_PriceList.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND ProductValue IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Product from Value=" + no);
sql = new StringBuffer("UPDATE I_PriceList " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Product, ' " + "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL)" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid Product=" + no);
// **** Find Price List
// Name
sql = new StringBuffer("UPDATE I_PriceList " + "SET M_PriceList_ID=(SELECT M_PriceList_ID FROM M_PriceList p" + " WHERE I_PriceList.Name=p.Name AND I_PriceList.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Price List Existing Value=" + no);
// **** Find Price List Version
// List Name (ID) + ValidFrom
sql = new StringBuffer("UPDATE I_PriceList " + "SET M_PriceList_Version_ID=(SELECT M_PriceList_Version_ID FROM M_PriceList_Version p" + " WHERE I_PriceList.ValidFrom=p.ValidFrom AND I_PriceList.M_PriceList_ID=p.M_PriceList_ID) " + "WHERE M_PriceList_ID IS NOT NULL AND M_PriceList_Version_ID IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Price List Version Existing Value=" + no);
/* UOM For Future USE
// Set UOM (System/own)
sql = new StringBuffer ("UPDATE I_PriceList "
+ "SET X12DE355 = "
+ "(SELECT MAX(X12DE355) FROM C_UOM u WHERE u.IsDefault='Y' AND u.AD_Client_ID IN (0,I_PriceList.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_PriceList "
+ "SET C_UOM_ID = (SELECT C_UOM_ID FROM C_UOM u WHERE u.X12DE355=I_PriceList.X12DE355 AND u.AD_Client_ID IN (0,I_PriceList.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_PriceList "
+ "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_PriceList " + "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_PriceList.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_PriceList " + "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_Currency c" + " WHERE I_PriceList.ISO_Code=c.ISO_Code AND c.AD_Client_ID IN (0,I_PriceList.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_PriceList " + "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);
// Mandatory Name or PriceListID
sql = new StringBuffer("UPDATE I_PriceList " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Mandatory Name or PriceListID,' " + "WHERE Name IS NULL AND M_PriceList_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("No Mandatory Name=" + no);
// Mandatory ValidFrom or PriceListVersionID
sql = new StringBuffer("UPDATE I_PriceList " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Mandatory ValidFrom or PriceListVersionID,' " + "WHERE ValidFrom IS NULL AND M_PriceList_Version_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("No Mandatory ValidFrom=" + no);
// Mandatory BreakValue if BPartner set
sql = new StringBuffer("UPDATE I_PriceList " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Mandatory BreakValue,' " + "WHERE BreakValue IS NULL AND (C_BPartner_ID IS NOT NULL OR BPartner_Value IS NOT NULL)" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("No Mandatory BreakValue=" + no);
commitEx();
// -------------------------------------------------------------------
int noInsertpp = 0;
int noUpdatepp = 0;
int noInsertppvb = 0;
int noUpdateppvb = 0;
int noInsertpl = 0;
int noInsertplv = 0;
// Go through Records
log.fine("start inserting/updating ...");
sql = new StringBuffer("SELECT * FROM I_PriceList WHERE I_IsImported='N'").append(clientCheck);
PreparedStatement pstmt_setImported = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// Set Imported = Y
pstmt_setImported = DB.prepareStatement("UPDATE I_PriceList SET I_IsImported='Y', M_PriceList_ID=?, M_PriceList_Version_ID=?, " + "Updated=SysDate, Processed='Y' WHERE I_PriceList_ID=?", get_TrxName());
//
pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
rs = pstmt.executeQuery();
while (rs.next()) {
X_I_PriceList imp = new X_I_PriceList(getCtx(), rs, get_TrxName());
int I_PriceList_ID = imp.getI_PriceList_ID();
int M_PriceList_ID = imp.getM_PriceList_ID();
if (M_PriceList_ID == 0) {
// try to obtain the ID directly from DB
M_PriceList_ID = DB.getSQLValue(get_TrxName(), "SELECT M_PriceList_ID FROM M_PriceList WHERE IsActive='Y' AND AD_Client_ID=? AND Name=?", m_AD_Client_ID, imp.getName());
if (M_PriceList_ID < 0)
M_PriceList_ID = 0;
}
boolean newPriceList = M_PriceList_ID == 0;
log.fine("I_PriceList_ID=" + I_PriceList_ID + ", M_PriceList_ID=" + M_PriceList_ID);
MPriceList pricelist = null;
// PriceList
if (// Insert new Price List
newPriceList) {
pricelist = new MPriceList(imp);
if (pricelist.save()) {
M_PriceList_ID = pricelist.getM_PriceList_ID();
log.finer("Insert Price List");
noInsertpl++;
} else {
StringBuffer sql0 = new StringBuffer("UPDATE I_PriceList i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert Price List failed")).append("WHERE I_PriceList_ID=").append(I_PriceList_ID);
DB.executeUpdate(sql0.toString(), get_TrxName());
continue;
}
} else {
// NOTE no else clause - if the price list already exists it's not updated
pricelist = new MPriceList(getCtx(), M_PriceList_ID, get_TrxName());
}
int M_PriceList_Version_ID = imp.getM_PriceList_Version_ID();
if (M_PriceList_Version_ID == 0) {
// try to obtain the ID directly from DB
M_PriceList_Version_ID = DB.getSQLValue(get_TrxName(), "SELECT M_PriceList_Version_ID FROM M_PriceList_Version WHERE IsActive='Y' AND ValidFrom=? AND M_PriceList_ID=?", new Object[] { imp.getValidFrom(), M_PriceList_ID });
if (M_PriceList_Version_ID < 0)
M_PriceList_Version_ID = 0;
}
boolean newPriceListVersion = M_PriceList_Version_ID == 0;
log.fine("I_PriceList_ID=" + I_PriceList_ID + ", M_PriceList_Version_ID=" + M_PriceList_Version_ID);
MPriceListVersion pricelistversion = null;
// PriceListVersion
if (// Insert new Price List Version
newPriceListVersion) {
pricelistversion = new MPriceListVersion(pricelist);
pricelistversion.setValidFrom(imp.getValidFrom());
pricelistversion.setName(pricelist.getName() + " " + imp.getValidFrom());
pricelistversion.setM_DiscountSchema_ID(m_discountschema_id);
if (pricelistversion.save()) {
M_PriceList_Version_ID = pricelistversion.getM_PriceList_Version_ID();
log.finer("Insert Price List Version");
noInsertplv++;
} else {
StringBuffer sql0 = new StringBuffer("UPDATE I_PriceList i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert Price List Version failed")).append("WHERE I_PriceList_ID=").append(I_PriceList_ID);
DB.executeUpdate(sql0.toString(), get_TrxName());
continue;
}
} else {
// NOTE no else clause - if the price list version already exists it's not updated
pricelistversion = new MPriceListVersion(getCtx(), M_PriceList_Version_ID, get_TrxName());
}
// If bpartner then insert/update into M_ProductPriceVendorBreak, otherwise insert/update M_ProductPrice
if (imp.getC_BPartner_ID() > 0) {
// M_ProductPriceVendorBreak
int M_ProductPriceVendorBreak_ID = DB.getSQLValue(get_TrxName(), "SELECT M_ProductPriceVendorBreak_ID " + "FROM M_ProductPriceVendorBreak " + "WHERE M_PriceList_Version_ID=? AND " + "IsActive='Y' AND " + "C_BPartner_ID=? AND " + "M_Product_ID=? AND " + "BreakValue=?", new Object[] { pricelistversion.getM_PriceList_Version_ID(), imp.getC_BPartner_ID(), imp.getM_Product_ID(), imp.getBreakValue() });
if (M_ProductPriceVendorBreak_ID < 0)
M_ProductPriceVendorBreak_ID = 0;
X_M_ProductPriceVendorBreak ppvb = new X_M_ProductPriceVendorBreak(getCtx(), M_ProductPriceVendorBreak_ID, get_TrxName());
boolean isInsert = false;
if (M_ProductPriceVendorBreak_ID == 0) {
ppvb.setM_PriceList_Version_ID(pricelistversion.getM_PriceList_Version_ID());
ppvb.setC_BPartner_ID(imp.getC_BPartner_ID());
ppvb.setM_Product_ID(imp.getM_Product_ID());
ppvb.setBreakValue(imp.getBreakValue());
isInsert = true;
}
if (p_importPriceLimit)
ppvb.setPriceLimit(imp.getPriceLimit());
if (p_importPriceList)
ppvb.setPriceList(imp.getPriceList());
if (p_importPriceStd)
ppvb.setPriceStd(imp.getPriceStd());
if (ppvb.save()) {
if (isInsert)
noInsertppvb++;
else
noUpdateppvb++;
log.finer("Insert/Update Product Price Vendor Break");
} else {
StringBuffer sql0 = new StringBuffer("UPDATE I_PriceList i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert/Update Product Price Vendor Break Version failed")).append("WHERE I_PriceList_ID=").append(I_PriceList_ID);
DB.executeUpdate(sql0.toString(), get_TrxName());
continue;
}
} else {
// M_ProductPrice
MProductPrice pp = MProductPrice.get(getCtx(), pricelistversion.getM_PriceList_Version_ID(), imp.getM_Product_ID(), get_TrxName());
boolean isInsert = false;
if (pp != null) {
if (p_importPriceLimit)
pp.setPriceLimit(imp.getPriceLimit());
if (p_importPriceList)
pp.setPriceList(imp.getPriceList());
if (p_importPriceStd)
pp.setPriceStd(imp.getPriceStd());
} else {
pp = new MProductPrice(pricelistversion, imp.getM_Product_ID(), p_importPriceList ? imp.getPriceList() : Env.ZERO, p_importPriceStd ? imp.getPriceStd() : Env.ZERO, p_importPriceLimit ? imp.getPriceLimit() : Env.ZERO);
isInsert = true;
}
if (pp.save()) {
log.finer("Insert/Update Product Price");
if (isInsert)
noInsertpp++;
else
noUpdatepp++;
} else {
StringBuffer sql0 = new StringBuffer("UPDATE I_PriceList i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert/Update Product Price failed")).append("WHERE I_PriceList_ID=").append(I_PriceList_ID);
DB.executeUpdate(sql0.toString(), get_TrxName());
continue;
}
}
// Update I_PriceList
pstmt_setImported.setInt(1, M_PriceList_ID);
pstmt_setImported.setInt(2, M_PriceList_Version_ID);
pstmt_setImported.setInt(3, I_PriceList_ID);
no = pstmt_setImported.executeUpdate();
//
commitEx();
}
// for all I_PriceList
//
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
DB.close(pstmt_setImported);
pstmt_setImported = null;
}
// Set Error to indicator to not imported
sql = new StringBuffer("UPDATE I_PriceList " + "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(noInsertpl), "@M_PriceList_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noInsertplv), "@M_PriceList_Version_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noInsertpp), "Product Price: @Inserted@");
addLog(0, null, new BigDecimal(noUpdatepp), "Product Price: @Updated@");
addLog(0, null, new BigDecimal(noInsertppvb), "@M_ProductPriceVendorBreak_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noUpdateppvb), "@M_ProductPriceVendorBreak_ID@: @Updated@");
return "";
}
Also used :
X_I_PriceList(org.compiere.model.X_I_PriceList)
AdempiereUserError(org.compiere.util.AdempiereUserError)
PreparedStatement(java.sql.PreparedStatement)
MPriceListVersion(org.compiere.model.MPriceListVersion)
MPriceList(org.compiere.model.MPriceList)
BigDecimal(java.math.BigDecimal)
ResultSet(java.sql.ResultSet)
X_M_ProductPriceVendorBreak(org.compiere.model.X_M_ProductPriceVendorBreak)
MProductPrice(org.compiere.model.MProductPrice)
Example 5 with MProductPrice
use of org.compiere.model.MProductPrice in project adempiere by adempiere.
the class ExpenseTypesFromAccounts method doIt.
@Override
protected String doIt() throws Exception {
// Fetch price list
MPriceList priceList = new MPriceList(getCtx(), m_priceListId, get_TrxName());
// Get current client id from price list since I for some reason can't read it from
// context.
m_clientId = priceList.getAD_Client_ID();
// Get active price list version
MPriceListVersion pv = priceList.getPriceListVersion(null);
if (pv == null)
throw new Exception("Pricelist " + priceList.getName() + " has no default version.");
MProduct product;
// Read all existing applicable products into memory for quick comparison.
List<MProduct> products = new Query(getCtx(), I_M_Product.Table_Name, "ProductType=?", get_TrxName()).setParameters(MProduct.PRODUCTTYPE_ExpenseType).list();
Map<String, MProduct> productMap = new TreeMap<String, MProduct>();
for (Iterator<MProduct> it = products.iterator(); it.hasNext(); ) {
product = it.next();
productMap.put(product.getValue(), product);
}
// Read all existing valid combinations comparison
MAccount validComb;
List<MAccount> validCombs = new Query(getCtx(), I_C_ValidCombination.Table_Name, "C_AcctSchema_ID=? and AD_Client_ID=? and AD_Org_ID=0", get_TrxName()).setParameters(m_acctSchemaId, m_clientId).list();
Map<Integer, MAccount> validCombMap = new TreeMap<Integer, MAccount>();
for (Iterator<MAccount> it = validCombs.iterator(); it.hasNext(); ) {
validComb = it.next();
validCombMap.put(validComb.getAccount_ID(), validComb);
}
// Read all accounttypes that fit the given criteria.
List<MElementValue> result = new Query(getCtx(), I_C_ElementValue.Table_Name, "AccountType=? and isSummary='N' and Value>=? and Value<=? and AD_Client_ID=?", get_TrxName()).setParameters(MElementValue.ACCOUNTTYPE_Expense, m_startElement, m_endElement, m_clientId).list();
MElementValue elem;
MProductPrice priceRec;
X_M_Product_Acct productAcct;
String expenseItemValue;
BigDecimal zero = Env.ZERO;
int addCount = 0;
int skipCount = 0;
for (Iterator<MElementValue> it = result.iterator(); it.hasNext(); ) {
elem = it.next();
expenseItemValue = m_productValuePrefix + elem.getValue() + m_productValueSuffix;
// See if a product with this key already exists
product = productMap.get(expenseItemValue);
if (product == null) {
// Create a new product from the account element
product = new MProduct(getCtx(), 0, get_TrxName());
product.set_ValueOfColumn("AD_Client_ID", Integer.valueOf(m_clientId));
product.setValue(expenseItemValue);
product.setName(elem.getName());
product.setDescription(elem.getDescription());
product.setIsActive(true);
product.setProductType(MProduct.PRODUCTTYPE_ExpenseType);
product.setM_Product_Category_ID(m_productCategoryId);
product.setC_UOM_ID(m_uomId);
product.setC_TaxCategory_ID(m_taxCategoryId);
product.setIsStocked(false);
product.setIsPurchased(true);
product.setIsSold(false);
// Save the product
product.saveEx(get_TrxName());
// Add a zero product price to the price list so it shows up in the price list
priceRec = new MProductPrice(getCtx(), pv.get_ID(), product.get_ID(), get_TrxName());
priceRec.set_ValueOfColumn("AD_Client_ID", Integer.valueOf(m_clientId));
priceRec.setPrices(zero, zero, zero);
priceRec.saveEx(get_TrxName());
// Set the revenue and expense accounting of the product to the given account element
// Get the valid combination
validComb = validCombMap.get(elem.getC_ElementValue_ID());
if (validComb == null) {
// Create new valid combination
validComb = new MAccount(getCtx(), 0, get_TrxName());
validComb.set_ValueOfColumn("AD_Client_ID", Integer.valueOf(m_clientId));
validComb.setAD_Org_ID(0);
validComb.setAlias(elem.getValue());
validComb.setAccount_ID(elem.get_ID());
validComb.setC_AcctSchema_ID(m_acctSchemaId);
validComb.saveEx(get_TrxName());
}
// TODO: It might be needed to make the accounting more specific, but the purpose
// of the process now is to create general accounts so this is intentional.
productAcct = new Query(getCtx(), I_M_Product_Acct.Table_Name, "M_Product_ID=? and C_AcctSchema_ID=?", get_TrxName()).setParameters(product.get_ID(), m_acctSchemaId).first();
productAcct.setP_Expense_Acct(validComb.get_ID());
productAcct.setP_Revenue_Acct(validComb.get_ID());
productAcct.saveEx(get_TrxName());
addCount++;
} else {
skipCount++;
}
}
String returnStr = addCount + " products added.";
if (skipCount > 0)
returnStr += " " + skipCount + " products skipped.";
return (returnStr);
}
Also used :
MElementValue(org.compiere.model.MElementValue)
MProduct(org.compiere.model.MProduct)
Query(org.compiere.model.Query)
MAccount(org.compiere.model.MAccount)
MPriceListVersion(org.compiere.model.MPriceListVersion)
MPriceList(org.compiere.model.MPriceList)
TreeMap(java.util.TreeMap)
X_M_Product_Acct(org.compiere.model.X_M_Product_Acct)
BigDecimal(java.math.BigDecimal)
MProductPrice(org.compiere.model.MProductPrice)
Aggregations
MProductPrice (org.compiere.model.MProductPrice)12
BigDecimal (java.math.BigDecimal)8
MPriceListVersion (org.compiere.model.MPriceListVersion)7
MPriceList (org.compiere.model.MPriceList)5
PreparedStatement (java.sql.PreparedStatement)2
ResultSet (java.sql.ResultSet)2
Properties (java.util.Properties)2
MProduct (org.compiere.model.MProduct)2
Query (org.compiere.model.Query)2
GridBagConstraints (java.awt.GridBagConstraints)1
GridBagLayout (java.awt.GridBagLayout)1
Insets (java.awt.Insets)1
SQLException (java.sql.SQLException)1
TreeMap (java.util.TreeMap)1
JLabel (javax.swing.JLabel)1
AdempierePOSException (org.adempiere.pos.AdempierePOSException)1
Label (org.adempiere.webui.component.Label)1
ALayoutConstraint (org.compiere.apps.ALayoutConstraint)1
MAccount (org.compiere.model.MAccount)1
MBPartnerLocation (org.compiere.model.MBPartnerLocation)1
