function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Gabe Rothman 8Gabe Rothman 8 

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