} // prepare
/// <summary>
/// Perrform Process.
/// </summary>
/// <returns>meesaage</returns>
protected override String DoIt()
{
StringBuilder sql = null;
int no = 0;
String clientCheck = " AND AD_Client_ID=" + _AD_Client_ID;
// **** Prepare ****
// Delete Old Imported
if (_deleteOldImported)
{
sql = new StringBuilder("DELETE FROM I_Product "
+ "WHERE I_IsImported='Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Delete Old Impored =" + no);
}
// Set Client, Org, IaActive, Created/Updated, ProductType
sql = new StringBuilder("UPDATE I_Product "
+ "SET AD_Client_ID = COALESCE (AD_Client_ID, ").Append(_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 = NULL,"
+ " I_IsImported = 'N' "
+ "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Reset=" + no);
// Set Optional BPartner
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("BPartner=" + no);
//
String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly
sql = new StringBuilder("UPDATE I_Product "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid BPartner,' "
+ "WHERE C_BPartner_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid BPartner=" + no);
}
// **** Find Product
// EAN/UPC
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Product Existing UPC=" + no);
// Value
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Product Existing Value=" + no);
// BP ProdNo
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Product Existing Vendor ProductNo=" + no);
// Set Product Category
sql = new StringBuilder("UPDATE I_Product "
+ "SET ProductCategory_Value=(SELECT MAX(Value) FROM M_Product_Category"
+ " WHERE IsDefault='Y' AND AD_Client_ID=").Append(_AD_Client_ID).Append(") "
+ "WHERE ProductCategory_Value IS NULL AND M_Product_Category_ID IS NULL"
+ " AND M_Product_ID IS NULL" // set category only if product not found
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Category Default Value=" + no);
//
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Set Category=" + 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", "ImageURL", "DescriptionURL" };
for (int i = 0; i < strFields.Length; i++)
{
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, 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",
"Volume", "Weight", "ShelfWidth", "ShelfHeight", "ShelfDepth", "UnitsPerPallet" };
for (int i = 0; i < numFields.Length; i++)
{
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, 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" };
for (int i = 0; i < strFieldsPO.Length; i++)
{
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, 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 StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine(numFieldsPO[i] + " default from existing Product PO=" + no);
}
}
// Invalid Category
sql = new StringBuilder("UPDATE I_Product "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid ProdCategorty,' "
+ "WHERE M_Product_Category_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Category=" + no);
}
// Set UOM (System/own)
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set UOM Default=" + no);
//
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Set UOM=" + no);
//
sql = new StringBuilder("UPDATE I_Product "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid UOM, ' "
+ "WHERE C_UOM_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid UOM=" + no);
}
// Set Currency
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Currency Default=" + no);
//
sql = new StringBuilder("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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("doIt- Set Currency=" + no);
//
sql = new StringBuilder("UPDATE I_Product "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Currency,' "
+ "WHERE C_Currency_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Currency=" + no);
}
// Verify ProductType
sql = new StringBuilder("UPDATE I_Product "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid ProductType,' "
+ "WHERE ProductType NOT IN ('I','S')"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid ProductType=" + no);
}
// Unique UPC/Value
sql = new StringBuilder("UPDATE I_Product i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Not Unique Value=" + no);
}
//
sql = new StringBuilder("UPDATE I_Product i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Not Unique UPC=" + no);
}
// Mandatory Value
sql = new StringBuilder("UPDATE I_Product i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Mandatory Value,' "
+ "WHERE Value IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No Mandatory Value=" + no);
}
// Vendor Product No
// sql = new StringBuilder ("UPDATE I_Product i "
// + "SET I_IsImported='E', I_ErrorMsg="+ts +"||'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 = DataBase.DB.ExecuteQuery(sql.ToString(),null, Get_TrxName());
// log.Info(log.l3_Util, "No Mandatory VendorProductNo=" + no);
sql = new StringBuilder("UPDATE I_Product "
+ "SET VendorProductNo=Value "
+ "WHERE C_BPartner_ID IS NOT NULL AND VendorProductNo IS NULL"
+ " AND I_IsImported='N'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("VendorProductNo Set to Value=" + no);
//
sql = new StringBuilder("UPDATE I_Product i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'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 = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Not Unique VendorProductNo=" + no);
}
// Get Default Tax Category
int C_TaxCategory_ID = 0;
IDataReader idr = null;
try
{
//PreparedStatement pstmt = DataBase.prepareStatement
idr = DataBase.DB.ExecuteReader("SELECT C_TaxCategory_ID FROM C_TaxCategory WHERE IsDefault='Y'" + clientCheck, null, Get_TrxName());
if (idr.Read())
{
C_TaxCategory_ID = Utility.Util.GetValueOfInt(idr[0]); //rs.getInt(1);
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
throw new Exception("TaxCategory", e);
}
log.Fine("C_TaxCategory_ID=" + C_TaxCategory_ID);
Commit();
// -------------------------------------------------------------------
int noInsert = 0;
int noUpdate = 0;
int noInsertPO = 0;
int noUpdatePO = 0;
// Go through Records
log.Fine("start inserting/updating ...");
sql = new StringBuilder("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 = DataBase.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 = DataBase.prepareStatement
+ (sqlt, Get_TrxName());
*/
// Insert Product from Import
//PreparedStatement pstmt_insertProductPO = DataBase.prepareStatement
String _insertProductPO = "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,Order_Min,Order_Pack,"
+ "CostPerOrder,DeliveryTime_Promised) "
+ "SELECT @param1,@param2, "
+ "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,Order_Min,Order_Pack,"
+ "CostPerOrder,DeliveryTime_Promised "
+ "FROM I_Product "
+ "WHERE I_Product_ID=@param3";
// Set Imported = Y
//PreparedStatement pstmt_setImported = DataBase.prepareStatement
String _setImported = "UPDATE I_Product SET I_IsImported='Y', M_Product_ID=@param1, "
+ "Updated=SysDate, Processed='Y' WHERE I_Product_ID=@param2";
//
//PreparedStatement pstmt = DataBase.prepareStatement(sql.ToString(), Get_TrxName());
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
while (idr.Read())
{
X_I_Product imp = new X_I_Product(GetCtx(), idr, 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();
bool 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);
// Product
if (newProduct) // Insert new Product
{
MProduct product = new MProduct(imp);
product.SetC_TaxCategory_ID(C_TaxCategory_ID);
if (product.Save())
{
M_Product_ID = product.GetM_Product_ID();
log.Finer("Insert Product");
noInsert++;
}
else
{
StringBuilder sql0 = new StringBuilder("UPDATE I_Product i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||").Append(DataBase.DB.TO_STRING("Insert Product failed"))
.Append("WHERE I_Product_ID=").Append(I_Product_ID);
DataBase.DB.ExecuteQuery(sql0.ToString(), null, 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,"
+ "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=" + I_Product_ID + ") "
+ "WHERE M_Product_ID=" + M_Product_ID;
//PreparedStatement pstmt_updateProduct = DataBase.prepareStatement
//(sqlt, Get_TrxName());
//jz pstmt_updateProduct.setInt(1, I_Product_ID);
// pstmt_updateProduct.setInt(2, M_Product_ID);
try
{
//no = pstmt_updateProduct.ExecuteQuery();
no = DataBase.DB.ExecuteQuery(sqlt, null, Get_TrxName());
log.Finer("Update Product = " + no);
noUpdate++;
}
catch (Exception ex)
{
log.Warning("Update Product - " + ex.ToString());
StringBuilder sql0 = new StringBuilder("UPDATE I_Product i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||").Append(DataBase.DB.TO_STRING("Update Product: " + ex.ToString()))
.Append("WHERE I_Product_ID=").Append(I_Product_ID);
DataBase.DB.ExecuteQuery(sql0.ToString(), null, 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,Order_Min,Order_Pack,"
+ "CostPerOrder,DeliveryTime_Promised,Updated,UpdatedBy)= "
+ "(SELECT CAST('Y' AS CHAR),C_UOM_ID,C_Currency_ID,UPC," //jz fix EDB unknown datatype error
+ "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=" + I_Product_ID + ") "
+ "WHERE M_Product_ID=" + M_Product_ID + " AND C_BPartner_ID=" + C_BPartner_ID;
//PreparedStatement pstmt_updateProductPO = DataBase.prepareStatement
//(sqlt, Get_TrxName());
//jz pstmt_updateProductPO.setInt(1, I_Product_ID);
// pstmt_updateProductPO.setInt(2, M_Product_ID);
// pstmt_updateProductPO.setInt(3, C_BPartner_ID);
try
{
//no = pstmt_updateProductPO.ExecuteQuery();
no = DataBase.DB.ExecuteQuery(sqlt, null, Get_TrxName());
log.Finer("Update Product_PO = " + no);
noUpdatePO++;
}
catch (Exception ex)
{
log.Warning("Update Product_PO - " + ex.ToString());
noUpdate--;
Rollback();
StringBuilder sql0 = new StringBuilder("UPDATE I_Product i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||").Append(DataBase.DB.TO_STRING("Update Product_PO: " + ex.ToString()))
.Append("WHERE I_Product_ID=").Append(I_Product_ID);
DataBase.DB.ExecuteQuery(sql0.ToString(), null, Get_TrxName());
continue;
}
//pstmt_updateProductPO.close();
}
if (no == 0) // Insert PO
{
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@param1", M_Product_ID);
//pstmt_insertProductPO.setInt(1, M_Product_ID);
param[1] = new SqlParameter("@param1", C_BPartner_ID);
//pstmt_insertProductPO.setInt(2, C_BPartner_ID);
param[2] = new SqlParameter("@param1", I_Product_ID);
//pstmt_insertProductPO.setInt(3, I_Product_ID);
try
{
//no = pstmt_insertProductPO.ExecuteQuery();
no = DataBase.DB.ExecuteQuery(_insertProductPO, param, Get_TrxName());
log.Finer("Insert Product_PO = " + no);
noInsertPO++;
}
catch (Exception ex)
{
log.Warning("Insert Product_PO - " + ex.ToString());
noInsert--; // assume that product also did not exist
Rollback();
StringBuilder sql0 = new StringBuilder("UPDATE I_Product i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||").Append(DataBase.DB.TO_STRING("Insert Product_PO: " + ex.ToString()))
.Append("WHERE I_Product_ID=").Append(I_Product_ID);
DataBase.DB.ExecuteQuery(sql0.ToString(), null, Get_TrxName());
continue;
}
}
} // C_BPartner_ID != 0
// Price List
if (_M_PriceList_Version_ID != 0)
{
Decimal PriceList = imp.GetPriceList();
Decimal PriceStd = imp.GetPriceStd();
Decimal PriceLimit = imp.GetPriceLimit();
if (Env.Signum(PriceStd) != 0 && Env.Signum(PriceLimit) != 0 && Env.Signum(PriceList) != 0)
{
MProductPrice pp = MProductPrice.Get(GetCtx(),
_M_PriceList_Version_ID, M_Product_ID, Get_TrxName());
if (pp == null)
{
pp = new MProductPrice(GetCtx(),
_M_PriceList_Version_ID, M_Product_ID, Get_TrxName());
}
pp.SetPrices(PriceList, PriceStd, PriceLimit);
pp.Save();
}
}
// Update I_Product
//pstmt_setImported.setInt(1, M_Product_ID);
//pstmt_setImported.setInt(2, I_Product_ID);
SqlParameter[] param1 = new SqlParameter[2];
param1[0] = new SqlParameter("@param1", M_Product_ID);
param1[1] = new SqlParameter("@param2", I_Product_ID);
no = DataBase.DB.ExecuteQuery(_setImported, param1, Get_TrxName());
//
Commit();
} // for all I_Product
idr.Close();
//
// pstmt_insertProduct.close();
// pstmt_updateProduct.close();
//pstmt_insertProductPO.close();
// pstmt_updateProductPO.close();
//pstmt_setImported.close();
//
}
catch
{
if (idr != null)
{
idr.Close();
idr = null;
}
}
// Set Error to indicator to not imported
sql = new StringBuilder("UPDATE I_Product "
+ "SET I_IsImported='N', Updated=SysDate "
+ "WHERE I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@M_Product_ID@: @Inserted@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noUpdate), "@M_Product_ID@: @Updated@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsertPO), "@M_Product_ID@ @Purchase@: @Inserted@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noUpdatePO), "@M_Product_ID@ @Purchase@: @Updated@");
return("");
} // doIt
/// <summary>
/// Create Price List
/// </summary>
/// <returns>info message</returns>
private String Create()
{
StringBuilder info = new StringBuilder();
// Delete Old Data
if (_deleteOld)
{
int no = DataBase.DB.ExecuteQuery(
"DELETE M_ProductPrice "
+ "WHERE M_PriceList_Version_ID=" + _M_PriceList_Version_ID, null,
Get_TrxName());
log.Info("Deleted=" + no);
info.Append("@Deleted@=").Append(no).Append(" - ");
}
int M_Pricelist_Version_Base_ID = _plv.GetM_Pricelist_Version_Base_ID();
MPriceList pl = _plv.GetPriceList();
int curPrecision = pl.GetStandardPrecision();
/**
* For All Discount Lines in Sequence
*/
MDiscountSchema ds = new MDiscountSchema(GetCtx(), _plv.GetM_DiscountSchema_ID(), Get_TrxName());
MDiscountSchemaLine[] dsl = ds.GetLines(false);
for (int i = 0; i < dsl.Length; i++)
{
MDiscountSchemaLine dsLine = dsl[i];
String message = "#" + dsLine.GetSeqNo();
String dd = dsLine.GetDescription();
if (dd != null && dd.Length > 0)
{
message += " " + dd;
}
// Clear Temporary Table
int noDeleted = DataBase.DB.ExecuteQuery("DELETE FROM T_Selection", null, Get_TrxName());
// Create Selection in Temporary Table
String sql = null;
int M_DiscountSchemaLine_ID = dsLine.GetM_DiscountSchemaLine_ID();
int p2 = M_Pricelist_Version_Base_ID;
if (p2 == 0) // Create from PO
{
p2 = dsLine.GetAD_Client_ID();
sql = "INSERT INTO T_Selection (T_Selection_ID) "
+ "SELECT DISTINCT po.M_Product_ID "
+ "FROM M_Product_PO po "
+ " INNER JOIN M_Product p ON (p.M_Product_ID=po.M_Product_ID)"
+ " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #1
+ "WHERE p.AD_Client_ID IN (" + p2 + ", 0)" // #2
+ " AND p.IsActive='Y' AND po.IsActive='Y' AND po.IsCurrentVendor='Y'"
// Optional Restrictions
+ " AND (dl.M_Product_Category_ID IS NULL OR p.M_Product_Category_ID=dl.M_Product_Category_ID)"
+ " AND (dl.C_BPartner_ID IS NULL OR po.C_BPartner_ID=dl.C_BPartner_ID)"
+ " AND (dl.M_Product_ID IS NULL OR p.M_Product_ID=dl.M_Product_ID)";
}
else // Create from Price List **
{
sql = "INSERT INTO T_Selection (T_Selection_ID) "
+ "SELECT DISTINCT p.M_Product_ID "
+ "FROM M_ProductPrice pp"
+ " INNER JOIN M_Product p ON (p.M_Product_ID=pp.M_Product_ID)"
+ " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #1
+ "WHERE pp.M_PriceList_Version_ID=" + p2 //#2 PriceList_Version_Base_ID
+ " AND p.IsActive='Y' AND pp.IsActive='Y'"
// Optional Restrictions
+ " AND (dl.M_Product_Category_ID IS NULL OR p.M_Product_Category_ID=dl.M_Product_Category_ID)"
+ " AND (dl.C_BPartner_ID IS NULL OR EXISTS "
+ "(SELECT * FROM M_Product_PO po "
+ "WHERE po.M_Product_ID=p.M_Product_ID AND po.C_BPartner_ID=dl.C_BPartner_ID))"
+ " AND (dl.M_Product_ID IS NULL OR p.M_Product_ID=dl.M_Product_ID)";
}
//idr = DataBase.prepareStatement(sql, get_TrxName());
int noSelected = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
message += ": @Selected@=" + noSelected;
// Delete Prices in Selection, so that we can insert
if (M_Pricelist_Version_Base_ID == 0 ||
M_Pricelist_Version_Base_ID != _M_PriceList_Version_ID)
{
sql = "DELETE FROM M_ProductPrice pp "
+ "WHERE pp.M_PriceList_Version_ID=" + _M_PriceList_Version_ID
+ " AND EXISTS (SELECT * FROM T_Selection s WHERE pp.M_Product_ID=s.T_Selection_ID)";
noDeleted = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
message += ", @Deleted@=" + noDeleted;
}
// Copy (Insert) Prices
int noInserted = 0;
sql = "INSERT INTO M_ProductPrice "
+ "(M_PriceList_Version_ID, M_Product_ID,"
+ " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,"
+ " PriceList, PriceStd, PriceLimit) ";
//
if (M_Pricelist_Version_Base_ID == _M_PriceList_Version_ID)
{
sql = null; // We have Prices already
}
else if (M_Pricelist_Version_Base_ID == 0)
{
/** Copy and Convert from Product_PO */
sql += "SELECT plv.M_PriceList_Version_ID, po.M_Product_ID,"
+ " plv.AD_Client_ID, plv.AD_Org_ID, 'Y', SysDate, plv.UpdatedBy, SysDate, plv.UpdatedBy,"
// Price List
+ " COALESCE(currencyConvert(po.PriceList,"
+ " po.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -po.PriceList),"
// Price Std
+ " COALESCE(currencyConvert(po.PriceList,"
+ " po.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -po.PriceList),"
// Price Limit
+ " COALESCE(currencyConvert(po.PricePO,"
+ " po.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -po.PricePO) "
//
+ "FROM M_Product_PO po"
+ " INNER JOIN M_PriceList_Version plv ON (plv.M_PriceList_Version_ID=" + _M_PriceList_Version_ID + ")" // #1
+ " INNER JOIN M_PriceList pl ON (pl.M_PriceList_ID=plv.M_PriceList_ID)"
+ " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #2
//
+ "WHERE EXISTS (SELECT * FROM T_Selection s WHERE po.M_Product_ID=s.T_Selection_ID)"
+ " AND po.IsCurrentVendor='Y' AND po.IsActive='Y'";
}
else
{
/** Copy and Convert from other PriceList_Version */
sql += "SELECT plv.M_PriceList_Version_ID, pp.M_Product_ID,"
+ " plv.AD_Client_ID, plv.AD_Org_ID, 'Y', SysDate, plv.UpdatedBy, SysDate, plv.UpdatedBy,"
// Price List
+ " COALESCE(currencyConvert(pp.PriceList,"
+ " bpl.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -pp.PriceList),"
// Price Std
+ " COALESCE(currencyConvert(pp.PriceStd,"
+ " bpl.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -pp.PriceStd),"
// Price Limit
+ " COALESCE(currencyConvert(pp.PriceLimit,"
+ " bpl.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -pp.PriceLimit) "
//
+ "FROM M_ProductPrice pp"
+ " INNER JOIN M_PriceList_Version plv ON (plv.M_PriceList_Version_ID=" + _M_PriceList_Version_ID + ")" // #1
+ " INNER JOIN M_PriceList pl ON (pl.M_PriceList_ID=plv.M_PriceList_ID)"
+ " INNER JOIN M_PriceList_Version bplv ON (pp.M_PriceList_Version_ID=bplv.M_PriceList_Version_ID)"
+ " INNER JOIN M_PriceList bpl ON (bplv.M_PriceList_ID=bpl.M_PriceList_ID)"
+ " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #2
//
+ "WHERE ";
if (M_Pricelist_Version_Base_ID != 0)
{
sql += "pp.M_PriceList_Version_ID=" + M_Pricelist_Version_Base_ID + " AND"; // #3 M_PriceList_Version_Base_ID
}
sql += " EXISTS (SELECT * FROM T_Selection s WHERE pp.M_Product_ID=s.T_Selection_ID)"
+ " AND pp.IsActive='Y'";
}
if (sql != null)
{
//pstmt = DataBase.prepareStatement(sql, get_TrxName());
//pstmt.setInt(1, _M_PriceList_Version_ID);
// pstmt.setInt(2, M_DiscountSchemaLine_ID);
//if (M_Pricelist_Version_Base_ID != 0)
//{
// pstmt.setInt(3, M_Pricelist_Version_Base_ID);
// }
noInserted = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
message += " @Inserted@=" + noInserted;
}
/** Calculations **/
MProductPrice[] pp = _plv.GetProductPrice(
"AND EXISTS (SELECT * FROM T_Selection s "
+ "WHERE s.T_Selection_ID=M_ProductPrice.M_Product_ID)");
for (int j = 0; j < pp.Length; j++)
{
MProductPrice price = pp[j];
Decimal priceList = price.GetPriceList();
Decimal priceStd = price.GetPriceStd();
Decimal priceLimit = price.GetPriceLimit();
//
price.SetPriceList(Calculate(dsLine.GetList_Base(),
priceList, priceStd, priceLimit, dsLine.GetList_Fixed(),
dsLine.GetList_AddAmt(), dsLine.GetList_Discount(),
dsLine.GetList_Rounding(), curPrecision));
price.SetPriceStd(Calculate(dsLine.GetStd_Base(),
priceList, priceStd, priceLimit, dsLine.GetStd_Fixed(),
dsLine.GetStd_AddAmt(), dsLine.GetStd_Discount(),
dsLine.GetStd_Rounding(), curPrecision));
price.SetPriceLimit(Calculate(dsLine.GetLimit_Base(),
priceList, priceStd, priceLimit, dsLine.GetLimit_Fixed(),
dsLine.GetLimit_AddAmt(), dsLine.GetLimit_Discount(),
dsLine.GetLimit_Rounding(), curPrecision));
price.Save();
} // for all products
// Clear Temporary Table
noDeleted = DataBase.DB.ExecuteQuery("DELETE FROM T_Selection", null, Get_TrxName());
//
AddLog(message);
} // for all lines
MProductPrice[] ppl = _plv.GetProductPrice(true);
info.Append(" - @Records@=").Append(ppl.Length);
return(info.ToString());
}