/// <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); }