/// <summary>
/// New Order Line (different Product)
/// </summary>
/// <param name="rLine">request line</param>
private void NewLine(MRequisitionLine rLine)
{
if (_orderLine != null)
{
if (!_orderLine.Save())
{
throw new Exception("Cannot update Order Line");
}
}
_orderLine = null;
MProduct product = null;
// Get Business Partner
int C_BPartner_ID = rLine.GetC_BPartner_ID();
if (C_BPartner_ID != 0)
{
;
}
else if (rLine.GetC_Charge_ID() != 0)
{
MCharge charge = MCharge.Get(GetCtx(), rLine.GetC_Charge_ID());
C_BPartner_ID = charge.GetC_BPartner_ID();
if (C_BPartner_ID == 0)
{
throw new Exception("No Vendor for Charge " + charge.GetName());
}
}
else
{
// Find Vendor from Produt
product = MProduct.Get(GetCtx(), rLine.GetM_Product_ID());
MProductPO[] ppos = MProductPO.GetOfProduct(GetCtx(), product.GetM_Product_ID(), null);
for (int i = 0; i < ppos.Length; i++)
{
if (ppos[i].IsCurrentVendor() && ppos[i].GetC_BPartner_ID() != 0)
{
C_BPartner_ID = ppos[i].GetC_BPartner_ID();
break;
}
}
if (C_BPartner_ID == 0 && ppos.Length > 0)
{
C_BPartner_ID = ppos[0].GetC_BPartner_ID();
}
if (C_BPartner_ID == 0)
{
throw new Exception("No Vendor for " + product.GetName());
}
}
// New Order - Different Vendor
if (_order == null ||
_order.GetC_BPartner_ID() != C_BPartner_ID)
{
NewOrder(rLine, C_BPartner_ID);
}
// No Order Line
_orderLine = new MOrderLine(_order);
if (product != null)
{
_orderLine.SetProduct(product);
_orderLine.SetM_AttributeSetInstance_ID(rLine.GetM_AttributeSetInstance_ID());
}
else
{
_orderLine.SetC_Charge_ID(rLine.GetC_Charge_ID());
_orderLine.SetPriceActual(rLine.GetPriceActual());
}
_orderLine.SetAD_Org_ID(rLine.GetAD_Org_ID());
// Prepare Save
_m_M_Product_ID = rLine.GetM_Product_ID();
_m_M_AttributeSetInstance_ID = rLine.GetM_AttributeSetInstance_ID();
if (!_orderLine.Save())
{
throw new Exception("Cannot save Order Line");
}
}
} // 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 Orders
/// </summary>
/// <returns>true if created</returns>
private bool CreateOrders()
{
// Get Counter Org/BP
int runAD_Org_ID = _run.GetAD_Org_ID();
if (runAD_Org_ID == 0)
{
runAD_Org_ID = GetCtx().GetAD_Org_ID();
}
MOrg runOrg = MOrg.Get(GetCtx(), runAD_Org_ID);
int runC_BPartner_ID = runOrg.GetLinkedC_BPartner_ID();
bool counter = !_run.IsCreateSingleOrder() && // no single Order
runC_BPartner_ID > 0 && // Org linked to BP
!_docType.IsSOTrx(); // PO
MBPartner runBPartner = counter ? new MBPartner(GetCtx(), runC_BPartner_ID, Get_TrxName()) : null;
if (!counter || runBPartner == null || runBPartner.Get_ID() != runC_BPartner_ID)
{
counter = false;
}
if (counter)
{
log.Info("RunBP=" + runBPartner
+ " - " + _docType);
}
log.Info("Single=" + _run.IsCreateSingleOrder()
+ " - " + _docType + ",SO=" + _docType.IsSOTrx());
log.Fine("Counter=" + counter
+ ",C_BPartner_ID=" + runC_BPartner_ID + "," + runBPartner);
//
MBPartner bp = null;
MOrder singleOrder = null;
MProduct product = null;
// Consolidated Order
if (_run.IsCreateSingleOrder())
{
bp = new MBPartner(GetCtx(), _run.GetC_BPartner_ID(), Get_TrxName());
if (bp.Get_ID() == 0)
{
throw new ArgumentException("Business Partner not found - C_BPartner_ID=" + _run.GetC_BPartner_ID());
}
//
if (!_IsTest)
{
singleOrder = new MOrder(GetCtx(), 0, Get_TrxName());
singleOrder.SetC_DocTypeTarget_ID(_docType.GetC_DocType_ID());
singleOrder.SetC_DocType_ID(_docType.GetC_DocType_ID());
singleOrder.SetIsReturnTrx(_docType.IsReturnTrx());
singleOrder.SetIsSOTrx(_docType.IsSOTrx());
singleOrder.SetBPartner(bp);
if (_run.GetC_BPartner_Location_ID() != 0)
{
singleOrder.SetC_BPartner_Location_ID(_run.GetC_BPartner_Location_ID());
}
singleOrder.SetDateOrdered(_DateOrdered);
singleOrder.SetDatePromised(_DatePromised);
if (!singleOrder.Save())
{
log.Log(Level.SEVERE, "Order not saved");
return(false);
}
_counter++;
}
}
int lastC_BPartner_ID = 0;
int lastC_BPartner_Location_ID = 0;
MOrder order = null;
// For all lines
for (int i = 0; i < _details.Length; i++)
{
MDistributionRunDetail detail = _details[i];
// Create Order Header
if (_run.IsCreateSingleOrder())
{
order = singleOrder;
}
// New Business Partner
else if (lastC_BPartner_ID != detail.GetC_BPartner_ID() ||
lastC_BPartner_Location_ID != detail.GetC_BPartner_Location_ID())
{
// finish order
order = null;
}
lastC_BPartner_ID = detail.GetC_BPartner_ID();
lastC_BPartner_Location_ID = detail.GetC_BPartner_Location_ID();
// New Order
if (order == null)
{
bp = new MBPartner(GetCtx(), detail.GetC_BPartner_ID(), Get_TrxName());
if (!_IsTest)
{
order = new MOrder(GetCtx(), 0, Get_TrxName());
order.SetC_DocTypeTarget_ID(_docType.GetC_DocType_ID());
order.SetIsReturnTrx(_docType.IsReturnTrx());
order.SetC_DocType_ID(_docType.GetC_DocType_ID());
order.SetIsSOTrx(_docType.IsSOTrx());
// Counter Doc
if (counter && bp.GetAD_OrgBP_ID_Int() > 0)
{
log.Fine("Counter - From_BPOrg=" + bp.GetAD_OrgBP_ID_Int()
+ "-" + bp + ", To_BP=" + runBPartner);
order.SetAD_Org_ID(bp.GetAD_OrgBP_ID_Int());
MOrgInfo oi = MOrgInfo.Get(GetCtx(), bp.GetAD_OrgBP_ID_Int(), null);
if (oi.GetM_Warehouse_ID() > 0)
{
order.SetM_Warehouse_ID(oi.GetM_Warehouse_ID());
}
order.SetBPartner(runBPartner);
}
else // normal
{
log.Fine("From_Org=" + runAD_Org_ID
+ ", To_BP=" + bp);
order.SetAD_Org_ID(runAD_Org_ID);
order.SetBPartner(bp);
if (detail.GetC_BPartner_Location_ID() != 0)
{
order.SetC_BPartner_Location_ID(detail.GetC_BPartner_Location_ID());
}
}
order.SetDateOrdered(_DateOrdered);
order.SetDatePromised(_DatePromised);
if (!order.Save())
{
log.Log(Level.SEVERE, "Order not saved");
return(false);
}
}
}
// Line
if (product == null || product.GetM_Product_ID() != detail.GetM_Product_ID())
{
product = MProduct.Get(GetCtx(), detail.GetM_Product_ID());
}
if (_IsTest)
{
AddLog(0, null, detail.GetActualAllocation(),
bp.GetName() + " - " + product.GetName());
continue;
}
// Create Order Line
MOrderLine line = new MOrderLine(order);
if (counter && bp.GetAD_OrgBP_ID_Int() > 0)
{
; // don't overwrite counter doc
}
else // normal - optionally overwrite
{
line.SetC_BPartner_ID(detail.GetC_BPartner_ID());
if (detail.GetC_BPartner_Location_ID() != 0)
{
line.SetC_BPartner_Location_ID(detail.GetC_BPartner_Location_ID());
}
}
//
line.SetProduct(product);
line.SetQty(detail.GetActualAllocation());
line.SetPrice();
if (!line.Save())
{
log.Log(Level.SEVERE, "OrderLine not saved");
return(false);
}
AddLog(0, null, detail.GetActualAllocation(), order.GetDocumentNo()
+ ": " + bp.GetName() + " - " + product.GetName());
}
// finish order
order = null;
return(true);
}
