You need to sign in to do that
Don't have an account?
Gabe Rothman 8
And here is the entire controller:
Is it possible to query data in a VisualForce page before insert?
I have a visualforce page that we use to add products to opporttunities. Before inserting the line items I need to run an aggregateresult query to get the values of several fields in order to insert an additional support line-item and populate it's fields with the aggregated results from the query. In order to accomplish this, I need to query the values that have been input in the Visualforce page before they are inserted into the database so that I can insert all of the line-items at once. Thanks for your help!
Here is my relevant code snippet:
public PageReference onSave(){ Decimal supportQty; Decimal svcTerm; Decimal sprice; List<Dynamic_Pricing__c> dplist = [SELECT Pricing_Coefficient__c, Pricing_Type__c FROM Dynamic_Pricing__c]; Map<string, decimal> supportPricingMap = new Map<string, decimal>(); List<PriceBookEntry> supportSkus = [SELECT Id, Name, Pricebook2.Name, Pricebook2Id, IsActive, Product2.Name, Product2.Family, Product2.Type__c, Product2.IsActive, Product2.Description, UnitPrice, Product2.SKU__c, Disti_Transfer_Price__c, Product2.Product_Term_Years__c, Product2.SKU_Level_Discount__c FROM PriceBookEntry WHERE Pricebook2Id =: theBook.id AND Product2.Type__c =: 'Support']; Map<String,PricebookEntry> supportTypeMap = new Map<String, PricebookEntry>(); // If previously selected products are now removed, we need to delete them if(forDeletion.size()>0) delete(forDeletion); for(Dynamic_Pricing__c dp : dplist){ if(!supportPricingMap.containsKey(dp.Pricing_Type__c)){ supportPricingMap.put(dp.Pricing_Type__c, dp.Pricing_Coefficient__c); } } for(PricebookEntry d : supportSkus){ if(!supportTypeMap.containsKey(d.Product2.SKU__c)){ supportTypeMap.put(d.Product2.SKU__c,d); } } AggregateResult[] groupedResults =[SELECT SUM(Quantity)sumQty, MAX(Service_Term_in_Months__c)svcTerm, SUM(UnitPrice)sprice FROM OpportunityLineItem WHERE Id in: shoppingCartProxy AND PriceBookEntry.Product2.Family =: 'MTP' AND PriceBookEntry.Product2.Type__c =: 'Subscription']; if(groupedResults.isempty()){ ApexPages.addmessage(new ApexPages.message(ApexPages.severity.WARNING,'Please enter value')); } for(AggregateResult ar : groupedResults){ supportQty = (Decimal)ar.get('sumQty'); svcTerm = (Decimal)ar.get('svcTerm'); sprice = (Decimal)ar.get('sprice'); } if(supportType!=null && supportType!='none'){ shoppingCart.add(new opportunityLineItem(OpportunityId=theOpp.Id, PriceBookEntry=supportTypeMap.get(supportType), PriceBookEntryId=supportTypeMap.get(supportType).Id, Quantity=supportQty, Service_Term_in_Months__c = 12, UnitPrice=12)); //* supportPricingMap.get(supportType), Discount_off_list_manual__c = 0)); } // Previously selected products may have new quantities and amounts, and we may have new products listed, so we use upsert here try{ if(shoppingCart.size()>0) upsert(shoppingCart); } catch(Exception e){ ApexPages.addMessages(e); return null; } // After save return the user to the Opportunity return new PageReference('/' + ApexPages.currentPage().getParameters().get('Id')); }
And here is the entire controller:
public with sharing class opportunityProductEntryExtension { public Opportunity theOpp {get;set;} public String searchString {get;set;} public String supportType {get;set;} public OpportunityLineItem[] shoppingCart {get;set;} public List<OpportunityLineItem> shoppingCartProxy {get;set;} public PriceBookEntry[] availableProducts {get;set;} public Pricebook2 theBook {get;set;} public String toSelect {get; set;} public String toUnselect {get; set;} public Decimal Total {get;set;} public Boolean overLimit {get;set;} public Boolean multipleCurrencies {get; set;} private Boolean forcePricebookSelection = false; private OpportunityLineItem[] forDeletion = new opportunityLineItem[]{}; private ApexPages.StandardController std; public opportunityProductEntryExtension(ApexPages.StandardController controller) { std=controller; // Need to know if org has multiple currencies enabled multipleCurrencies = UserInfo.isMultiCurrencyOrganization(); // Get information about the Opportunity being worked on if(multipleCurrencies) theOpp = database.query('select Id, Pricebook2Id, Pricebook2.Name, CurrencyIsoCode from Opportunity where Id = \'' + controller.getRecord().Id + '\' limit 1'); else theOpp = [select Id, Pricebook2Id, PriceBook2.Name from Opportunity where Id = :controller.getRecord().Id limit 1]; // If products were previously selected need to put them in the "selected products" section to start with shoppingCart = [select Id, Quantity, TotalPrice, UnitPrice, Description, List_Price__c, Disti_Transfer_Price2__c, Disti_Transfer_Price__c, PriceBookEntryId, PriceBookEntry.Name, PriceBookEntry.UnitPrice, PriceBookEntry.IsActive, PriceBookEntry.Product2Id, PriceBookEntry.Product2.Name, PriceBookEntry.Product2.Product_Term_Years__c, PriceBookEntry.PriceBook2Id, PriceBookEntry.Product2.SKU__c, PriceBookEntry.Product2.Family, PriceBookEntry.Product2.Type__c, Service_Term_in_Months__c, PriceBookEntry.Disti_Transfer_Price__c, Discount_off_list_manual__c from opportunityLineItem where OpportunityId=:theOpp.Id]; shoppingCartProxy =[select Id, Quantity, TotalPrice, UnitPrice, Description, List_Price__c, Disti_Transfer_Price2__c, Disti_Transfer_Price__c, PriceBookEntryId, PriceBookEntry.Name, PriceBookEntry.UnitPrice, PriceBookEntry.IsActive, PriceBookEntry.Product2Id, PriceBookEntry.Product2.Name, PriceBookEntry.Product2.Product_Term_Years__c, PriceBookEntry.PriceBook2Id, PriceBookEntry.Product2.SKU__c, PriceBookEntry.Product2.Family, PriceBookEntry.Product2.Type__c, Service_Term_in_Months__c, PriceBookEntry.Disti_Transfer_Price__c, Discount_off_list_manual__c from OpportunityLineItem where OpportunityId=:std.getId()]; // Check if Opp has a pricebook associated yet if(theOpp.Pricebook2Id == null){ Pricebook2[] activepbs = [select Id, Name from Pricebook2 where isActive = true limit 2]; if(activepbs.size() == 2){ forcePricebookSelection = true; theBook = new Pricebook2(); } else{ theBook = activepbs[0]; } } else{ theBook = theOpp.Pricebook2; } if(!forcePricebookSelection) updateAvailableList(); } // this is the 'action' method on the page public PageReference priceBookCheck(){ // if the user needs to select a pricebook before we proceed we send them to standard pricebook selection screen if(forcePricebookSelection){ return changePricebook(); } else{ //if there is only one active pricebook we go with it and save the opp if(theOpp.pricebook2Id != theBook.Id){ try{ theOpp.Pricebook2Id = theBook.Id; update(theOpp); } catch(Exception e){ ApexPages.addMessages(e); } } return null; } } public String getChosenCurrency(){ if(multipleCurrencies) return (String)theOpp.get('CurrencyIsoCode'); else return ''; } public void updateAvailableList() { String supportstr = 'Support'; // We dynamically build a query string and exclude items already in the shopping cart String qString = 'select Id, Name, Pricebook2.Name, Pricebook2Id, IsActive, Product2.Name, Product2.Family, Product2.Type__c, Product2.IsActive, Product2.Description, UnitPrice, Product2.SKU__c, Disti_Transfer_Price__c, Product2.Product_Term_Years__c, Product2.SKU_Level_Discount__c from PricebookEntry where IsActive=true AND Pricebook2Id = \'' + theBook.Id + '\''; if(multipleCurrencies) qstring += ' and CurrencyIsoCode = \'' + theOpp.get('currencyIsoCode') + '\''; // note that we are looking for the search string entered by the user in the name OR description // modify this to search other fields if desired if(searchString!=null){ qString+= ' and (Product2.Name like \'%' + searchString + '%\' or Product2.Description like \'%' + searchString + '%\')'; } Set<Id> selectedEntries = new Set<Id>(); for(opportunityLineItem d:shoppingCart){ selectedEntries.add(d.PricebookEntryId); } if(selectedEntries.size()>0){ String tempFilter = ' and Id not in ('; for(Id i : selectedEntries){ tempFilter+= '\'' + (String)i + '\','; } String extraFilter = tempFilter.substring(0,tempFilter.length()-1); extraFilter+= ')'; qString+= extraFilter; } qString+= ' AND Product2.Type__c != \'' + supportstr + '\''; qString+= ' order by Product2.Name'; qString+= ' limit 101'; system.debug('qString:' +qString); AvailableProducts = database.query(qString); // We only display up to 100 results... if there are more than we let the user know (see vf page) if(AvailableProducts.size()==101){ AvailableProducts.remove(100); overLimit = true; } else{ overLimit=false; } } public void addToShoppingCart(){ list<Price_Book_Discounts__c> discounts = [SELECT Discount__c, Name FROM Price_Book_Discounts__c WHERE Name =: theBook.Name]; list<SKU_Discounts__c> skus = [SELECT Discount__c, SKU__c, Price_Book_Name__c FROM SKU_Discounts__c WHERE Price_Book_Name__c =: theBook.Name]; list<Product2> products = [SELECT Product_Term_Years__c, Name FROM Product2]; Map<String, Decimal> pbNameToDiscountMap = new Map<String, Decimal>(); Map<String, Decimal> skuToDiscountMap = new Map<String, Decimal>(); Map<Decimal, Decimal> yearsToMonthsMap = new Map<Decimal, Decimal>(); // This function runs when a user hits "select" button next to a product for(Price_Book_Discounts__c discount : discounts){ pbNameToDiscountMap.put(discount.Name, discount.Discount__c); } for(SKU_Discounts__c sku : skus){ if (!SkuToDiscountMap.containsKey(sku.SKU__c)) { SkuToDiscountMap.put(sku.SKU__c, sku.Discount__c); } } for(Product2 prod : products){ if(!yearsToMonthsMap.containsKey(prod.Product_Term_Years__c)){ if(prod.Product_Term_Years__c == null){ yearsToMonthsMap.put(prod.Product_Term_Years__c, 12); } if(prod.Product_Term_Years__c == 1){ yearsToMonthsMap.put(prod.Product_Term_Years__c, 12); } if(prod.Product_Term_Years__c == 3){ yearsToMonthsMap.put(prod.Product_Term_Years__c, 36); } } } for(PricebookEntry d : AvailableProducts){ if((String)d.Id==toSelect && d.Product2.Type__c!='Non-Revenue' && d.Product2.Type__c!='Support'){ if(d.Product2.SKU_Level_Discount__c == false){ shoppingCart.add(new opportunityLineItem(OpportunityId=theOpp.Id, PriceBookEntry=d, PriceBookEntryId=d.Id, Quantity=0, Service_Term_in_Months__c = yearsToMonthsMap.get(d.Product2.Product_Term_Years__c), UnitPrice=d.UnitPrice, Discount_off_list_manual__c = pbNameToDiscountMap.get(theBook.Name))); break; } if(d.Product2.SKU_Level_Discount__c == true){ shoppingCart.add(new opportunityLineItem(OpportunityId=theOpp.Id, PriceBookEntry=d, PriceBookEntryId=d.Id, Quantity=0, Service_Term_in_Months__c = yearsToMonthsMap.get(d.Product2.Product_Term_Years__c), UnitPrice=d.UnitPrice, Discount_off_list_manual__c = SkuToDiscountMap.get(d.Product2.SKU__c))); break; } } if((String)d.Id==toSelect && d.Product2.Type__c=='Non-Revenue' && d.Product2.Type__c=='Support'){ if(d.Product2.Type__c=='Non-Revenue'){ shoppingCart.add(new opportunityLineItem(OpportunityId=theOpp.Id, PriceBookEntry=d, PriceBookEntryId=d.Id, Quantity=0, Service_Term_in_Months__c = 2, UnitPrice=d.UnitPrice, Discount_off_list_manual__c = 0)); break; }else{ shoppingCart.add(new opportunityLineItem(OpportunityId=theOpp.Id, PriceBookEntry=d, PriceBookEntryId=d.Id, Quantity=0, Service_Term_in_Months__c = yearsToMonthsMap.get(d.Product2.Product_Term_Years__c), UnitPrice=d.UnitPrice, Discount_off_list_manual__c = 0)); break; } } } updateAvailableList(); } public PageReference removeFromShoppingCart(){ // This function runs when a user hits "remove" on an item in the "Selected Products" section Integer count = 0; for(opportunityLineItem d : shoppingCart){ if((String)d.PriceBookEntryId==toUnselect){ if(d.Id!=null) forDeletion.add(d); shoppingCart.remove(count); break; } count++; } updateAvailableList(); return null; } public PageReference onSave(){ Decimal supportQty; Decimal svcTerm; Decimal sprice; List<Dynamic_Pricing__c> dplist = [SELECT Pricing_Coefficient__c, Pricing_Type__c FROM Dynamic_Pricing__c]; Map<string, decimal> supportPricingMap = new Map<string, decimal>(); List<PriceBookEntry> supportSkus = [SELECT Id, Name, Pricebook2.Name, Pricebook2Id, IsActive, Product2.Name, Product2.Family, Product2.Type__c, Product2.IsActive, Product2.Description, UnitPrice, Product2.SKU__c, Disti_Transfer_Price__c, Product2.Product_Term_Years__c, Product2.SKU_Level_Discount__c FROM PriceBookEntry WHERE Pricebook2Id =: theBook.id AND Product2.Type__c =: 'Support']; Map<String,PricebookEntry> supportTypeMap = new Map<String, PricebookEntry>(); // If previously selected products are now removed, we need to delete them if(forDeletion.size()>0) delete(forDeletion); for(Dynamic_Pricing__c dp : dplist){ if(!supportPricingMap.containsKey(dp.Pricing_Type__c)){ supportPricingMap.put(dp.Pricing_Type__c, dp.Pricing_Coefficient__c); } } for(PricebookEntry d : supportSkus){ if(!supportTypeMap.containsKey(d.Product2.SKU__c)){ supportTypeMap.put(d.Product2.SKU__c,d); } } AggregateResult[] groupedResults =[SELECT SUM(Quantity)sumQty, MAX(Service_Term_in_Months__c)svcTerm, SUM(UnitPrice)sprice FROM OpportunityLineItem WHERE Id in: shoppingCartProxy AND PriceBookEntry.Product2.Family =: 'MTP' AND PriceBookEntry.Product2.Type__c =: 'Subscription']; if(groupedResults.isempty()){ ApexPages.addmessage(new ApexPages.message(ApexPages.severity.WARNING,'Please enter value')); } for(AggregateResult ar : groupedResults){ supportQty = (Decimal)ar.get('sumQty'); svcTerm = (Decimal)ar.get('svcTerm'); sprice = (Decimal)ar.get('sprice'); } if(supportType!=null && supportType!='none'){ shoppingCart.add(new opportunityLineItem(OpportunityId=theOpp.Id, PriceBookEntry=supportTypeMap.get(supportType), PriceBookEntryId=supportTypeMap.get(supportType).Id, Quantity=supportQty, Service_Term_in_Months__c = 12, UnitPrice=12)); //* supportPricingMap.get(supportType), Discount_off_list_manual__c = 0)); } // Previously selected products may have new quantities and amounts, and we may have new products listed, so we use upsert here try{ if(shoppingCart.size()>0) upsert(shoppingCart); } catch(Exception e){ ApexPages.addMessages(e); return null; } // After save return the user to the Opportunity return new PageReference('/' + ApexPages.currentPage().getParameters().get('Id')); } public PageReference onCancel(){ // If user hits cancel we commit no changes and return them to the Opportunity return new PageReference('/' + ApexPages.currentPage().getParameters().get('Id')); } public PageReference changePricebook(){ // This simply returns a PageReference to the standard Pricebook selection screen // Note that is uses retURL parameter to make sure the user is sent back after they choose PageReference ref = new PageReference('/oppitm/choosepricebook.jsp'); ref.getParameters().put('id',theOpp.Id); ref.getParameters().put('retURL','/apex/opportunityProductEntry?id=' + theOpp.Id); return ref; } }