Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Shawn Reichner 29Shawn Reichner 29 

Using Records from Aggregate Results...Help Needed

Hello Devs,

I have the followign code that is using the Aggregate Results method to batch up records to create one Opportunity record for each Account and Status of the record being Aggregated.  My question is I now have a need to take each one of the records that is being Aggregated and create a record for each of the Aggregated records in a new custom object.  I have th eobject and needed fields created but I am unsure how to use those records that are being aggregated to create a record in the custom object for each aggregated record, I can only get the aggregated results to create one record, btu again I need a record create din the new custom object for each of the records being used in the aggregate result and not the aggregated result itself.  

Can anyone help me understand how to tap into those records being aggregated before the aggregate for loop to achieve this need? 

Thank you so much for any help you can provide,

Shawn

Apex Class code: 
  
global class CreateUpgradeDowngradeOpportunities Implements Schedulable
{
    global void execute(SchedulableContext sc)
    {
        CreateOpportunities();
    }
      
    public void CreateOpportunities()
    {
        //Variable & List Declarations
        
        Date Today = Date.Today();
        Date d1 = Today.addDays(-1);
        List<Opportunity> lstOpp = new List<Opportunity>();
        Opportunity objOpp;
        Id idRecTypeDowngrade = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get('AMP Downgrade').getRecordTypeId();
        Id idRecTypeUpgrade = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get('AMP Upgrade').getRecordTypeId();
        User FH = [Select ID FROM User WHERE FirstName = 'Firehost' LIMIT 1];
        User API = [Select ID FROM User WHERE Alias = 'api' LIMIT 1];
        Map<Id, Account> mapAcc = new Map<Id, Account>();
        Id idAcc;
        String strStatus;
        String pline;
        Decimal amt;
        Set<Id> setIdAcc = new Set<Id>();
        List<AggregateResult> lstAR = new List<AggregateResult>();
        String ZID;
        
        // collect sum by account and status and product line
        for(AggregateResult objAR : [SELECT Zuora__Account__c , Zuora__Status__c,Product_Line__c, SUM(Total_Booking_Amount__c) 
                                    FROM Zuora__Subscription__c 
                                    WHERE Zuora__Status__c IN ('Cancelled', 'Active')
                                        AND Zuora__Account__c != NULL
                                        AND ((Zuora__ServiceActivationDate__c = YESTERDAY AND OpportunityId__c = null AND (Migrated__c = 'False' OR Migrated__c = null)) OR Zuora__TermEndDate__c = YESTERDAY)
                                       // AND Migrated__c = 'False'
                                     AND SameDayDeactivation__c = False
                                     AND isAcctDiscount__c = False
                                    GROUP BY ROLLUP(Zuora__Account__c, Zuora__Status__c, Product_Line__c)])
        {
            lstAR.add(objAR);
            setIdAcc.add((Id)objAR.get('Zuora__Account__c'));
        } // End of Aggregate For Loop
        
        // collect account infos
        if(!setIdAcc.isEmpty())
        {
            mapAcc = new Map<Id, Account>([SELECT Id, Name, OwnerId, Service_Lead__c FROM Account WHERE Id IN: setIdAcc]);   
        }
        
        // create opps
        for(AggregateResult objAR : lstAR)
        {
            idAcc = (Id)objAR.get('Zuora__Account__c');
            strStatus = (String)objAR.get('Zuora__Status__c');
            amt = (Double)objAR.get('expr0');
            pline = (String)objAR.get('Product_Line__c');
            
            if(strStatus == 'Cancelled' && !String.isBlank(mapAcc.get(idAcc).Service_Lead__c) && pline == 'Complete')
            {
                objOpp = new Opportunity();
                objOpp.RecordTypeId = idRecTypeDowngrade;
                objOpp.OwnerId = mapAcc.get(idAcc).Service_Lead__c;
                objOpp.Name = mapAcc.get(idAcc).Name + ' - AMP';
                objOpp.AccountId = mapAcc.get(idAcc).Id;
                objOpp.Opportunity_Source__c = 'Portal';
                objOpp.Type = 'Downgrade';
                objOpp.Amount = amt * -1;
                objOpp.CloseDate = d1;
                objOpp.StageName = 'Cancelled';
                objOpp.Armor_Product_Category__c = 'Armor | '+pline;
                objOpp.Armor_Product__c = 'COMPLETE';
                objOpp.Armor_Anywhere_Location__c = 'None';
               // objOpp.ZuoraId__c = ZID;
                objOpp.Solution__c = 'General Security';
                objOpp.Auto_Bookings__c = true;
                objOpp.CreatedById = API.Id;
                lstOpp.add(objOpp);
                
            }
            else if(strStatus == 'Cancelled' && !String.isBlank(mapAcc.get(idAcc).Service_Lead__c) && pline == 'Anywhere')
            {
                objOpp = new Opportunity();
                objOpp.RecordTypeId = idRecTypeDowngrade;
                objOpp.OwnerId = mapAcc.get(idAcc).Service_Lead__c;
                objOpp.Name = mapAcc.get(idAcc).Name + ' - AMP';
                objOpp.AccountId = mapAcc.get(idAcc).Id;
                objOpp.Opportunity_Source__c = 'Portal';
                objOpp.Type = 'Non Opportunity';
                objOpp.Amount = amt * -1;
                objOpp.CloseDate = d1;
                objOpp.StageName = 'Cancelled';
                objOpp.Armor_Product_Category__c = 'Armor | '+pline;
                objOpp.Armor_Product__c = 'CORE';
                objOpp.Armor_Anywhere_Location__c = 'Other';
                objOpp.Armor_Anywhere_Other_Location__c = 'Other';
               // objOpp.ZuoraId__c = ZID;
                objOpp.Solution__c = 'General Security';
                objOpp.Auto_Bookings__c = true;
                objOpp.CreatedById = API.Id;
                lstOpp.add(objOpp);    
            } 
            else if(strStatus == 'Cancelled' && String.isBlank(mapAcc.get(idAcc).Service_Lead__c) && pline == 'Complete')
            {
                objOpp = new Opportunity();
                objOpp.RecordTypeId = idRecTypeDowngrade;
                objOpp.OwnerId = FH.Id;
                objOpp.Name = mapAcc.get(idAcc).Name + ' - AMP';
                objOpp.AccountId = mapAcc.get(idAcc).Id;
                objOpp.Opportunity_Source__c = 'Portal';
                objOpp.Type = 'Downgrade';
                objOpp.Amount = amt * -1;
                objOpp.CloseDate = d1;
                objOpp.StageName = 'Cancelled';
                objOpp.Armor_Product_Category__c = 'Armor | '+pline;
                objOpp.Armor_Product__c = 'COMPLETE';
                objOpp.Armor_Anywhere_Location__c = 'None';
               // objOpp.ZuoraId__c = ZID;
                objOpp.Solution__c = 'General Security';
                objOpp.Auto_Bookings__c = true;
                objOpp.CreatedById = API.Id;
                lstOpp.add(objOpp);    
            }
            else if(strStatus == 'Cancelled' && String.isBlank(mapAcc.get(idAcc).Service_Lead__c) && pline == 'Anywhere')
            {
                objOpp = new Opportunity();
                objOpp.RecordTypeId = idRecTypeDowngrade;
                objOpp.OwnerId = FH.Id;
                objOpp.Name = mapAcc.get(idAcc).Name + ' - AMP';
                objOpp.AccountId = mapAcc.get(idAcc).Id;
                objOpp.Opportunity_Source__c = 'Portal';
                objOpp.Type = 'Non Opportunity';
                objOpp.Amount = amt * -1;
                objOpp.CloseDate = d1;
                objOpp.StageName = 'Cancelled';
                objOpp.Armor_Product_Category__c = 'Armor | '+pline;
                objOpp.Armor_Product__c = 'CORE';
                objOpp.Armor_Anywhere_Location__c = 'Other';
                objOpp.Armor_Anywhere_Other_Location__c = 'Other';
               // objOpp.ZuoraId__c = ZID;
                objOpp.Solution__c = 'General Security';
                objOpp.Auto_Bookings__c = true;
                objOpp.CreatedById = API.Id;
                lstOpp.add(objOpp);    
            }
            
            else if(strStatus == 'Active' && !String.isBlank(mapAcc.get(idAcc).Service_Lead__c) && pline == 'Complete')
            {
                objOpp = new Opportunity();
                objOpp.RecordTypeId = idRecTypeUpgrade;
                objOpp.OwnerId = mapAcc.get(idAcc).Service_Lead__c;
                objOpp.Name = mapAcc.get(idAcc).Name + ' - AMP';
                objOpp.AccountId = mapAcc.get(idAcc).Id;
                objOpp.Opportunity_Source__c = 'Portal';
                objOpp.Type = 'Existing Business';
                objOpp.Amount = amt;
                objOpp.CloseDate = d1;
                objOpp.StageName = 'Closed Won';
                objOpp.Armor_Product_Category__c = 'Armor | '+pline;
                objOpp.Armor_Product__c = 'COMPLETE';
                objOpp.Armor_Anywhere_Location__c = 'None';
                objOpp.Closed_Comments__c = 'AMP Portal Self Service Upgrade';
               // objOpp.ZuoraId__c = ZID;
                objOpp.Solution__c = 'General Security';
                objOpp.Auto_Bookings__c = true;
                objOpp.CreatedById = API.Id;
                lstOpp.add(objOpp);
            }
            
            else if(strStatus == 'Active' && !String.isBlank(mapAcc.get(idAcc).Service_Lead__c) && pline == 'Anywhere')
            {
                objOpp = new Opportunity();
                objOpp.RecordTypeId = idRecTypeUpgrade;
                objOpp.OwnerId = mapAcc.get(idAcc).Service_Lead__c;
                objOpp.Name = mapAcc.get(idAcc).Name + ' - AMP';
                objOpp.AccountId = mapAcc.get(idAcc).Id;
                objOpp.Opportunity_Source__c = 'Portal';
                objOpp.Type = 'Non Opportunity';
                objOpp.Amount = amt;
                objOpp.CloseDate = d1;
                objOpp.StageName = 'Closed Won';
                objOpp.Armor_Product_Category__c = 'Armor | '+pline;
                objOpp.Armor_Product__c = 'CORE';
                objOpp.Armor_Anywhere_Location__c = 'Other';
                objOpp.Armor_Anywhere_Other_Location__c = 'Other';
                objOpp.Closed_Comments__c = 'AMP Portal Self Service Upgrade';
               // objOpp.ZuoraId__c = ZID;
                objOpp.Solution__c = 'General Security';
                objOpp.Auto_Bookings__c = true;
                objOpp.CreatedById = API.Id;
                lstOpp.add(objOpp);
            }
            else if(strStatus == 'Active' && String.isBlank(mapAcc.get(idAcc).Service_Lead__c) && pline == 'Complete')
            {
                objOpp = new Opportunity();
                objOpp.RecordTypeId = idRecTypeUpgrade;
                objOpp.OwnerId = FH.Id;
                objOpp.Name = mapAcc.get(idAcc).Name + ' - AMP';
                objOpp.AccountId = mapAcc.get(idAcc).Id;
                objOpp.Opportunity_Source__c = 'Portal';
                objOpp.Type = 'Existing Business';
                objOpp.Amount = amt;
                objOpp.CloseDate = d1;
                objOpp.StageName = 'Closed Won';
                objOpp.Armor_Product_Category__c = 'Armor | '+pline;
                objOpp.Armor_Product__c = 'COMPLETE';
                objOpp.Armor_Anywhere_Location__c = 'None';
                objOpp.Closed_Comments__c = 'AMP Portal Self Service Upgrade';
              //  objOpp.ZuoraId__c = ZID;
                objOpp.Solution__c = 'General Security';
                objOpp.Auto_Bookings__c = true;
                objOpp.CreatedById = API.Id;
                lstOpp.add(objOpp);
            }
            
            else if(strStatus == 'Active' && String.isBlank(mapAcc.get(idAcc).Service_Lead__c) && pline == 'Anywhere')
            {
                objOpp = new Opportunity();
                objOpp.RecordTypeId = idRecTypeUpgrade;
                objOpp.OwnerId = FH.Id;
                objOpp.Name = mapAcc.get(idAcc).Name + ' - AMP';
                objOpp.AccountId = mapAcc.get(idAcc).Id;
                objOpp.Opportunity_Source__c = 'Portal';
                objOpp.Type = 'Non Opportunity';
                objOpp.Amount = amt;
                objOpp.CloseDate = d1;
                objOpp.StageName = 'Closed Won';
                objOpp.Armor_Product_Category__c = 'Armor | '+pline;
                objOpp.Armor_Product__c = 'CORE';
                objOpp.Armor_Anywhere_Location__c = 'Other';
                objOpp.Armor_Anywhere_Other_Location__c = 'Other';
                objOpp.Closed_Comments__c = 'AMP Portal Self Service Upgrade';
              //  objOpp.ZuoraId__c = ZID;
                objOpp.Solution__c = 'General Security';
                objOpp.Auto_Bookings__c = true;
                objOpp.CreatedById = API.Id;
                lstOpp.add(objOpp);
            }
        } // END of FOR Loop

        if(!lstOpp.isEmpty())
        {
            insert lstOpp;
        }
    } // End of CreateOpportunities Method 
} // End of Class

 
Paul S.Paul S.
Is it that you're trying to create a record in that new object for each subscription record or are you trying to create a new record for each row of the AggregateResult array?  If the latter, I believe you'd just create those records in this for loop: 
for(AggregateResult objAR : [SELECT Zuora__Account__c , Zuora__Status__c,Product_Line__c, SUM(Total_Booking_Amount__c) 
                                    FROM Zuora__Subscription__c 
                                    WHERE Zuora__Status__c IN ('Cancelled', 'Active')
                                        AND Zuora__Account__c != NULL
                                        AND ((Zuora__ServiceActivationDate__c = YESTERDAY AND OpportunityId__c = null AND (Migrated__c = 'False' OR Migrated__c = null)) OR Zuora__TermEndDate__c = YESTERDAY)
                                       // AND Migrated__c = 'False'
                                     AND SameDayDeactivation__c = False
                                     AND isAcctDiscount__c = False
                                    GROUP BY ROLLUP(Zuora__Account__c, Zuora__Status__c, Product_Line__c)])
        {
            lstAR.add(objAR);
            setIdAcc.add((Id)objAR.get('Zuora__Account__c'));
        }
Shawn Reichner 29Shawn Reichner 29
Paul, thank you for the note and suggestion.  It is for the former.  I need to create a record in the custom object from each record that is used in the Aggregate Results, not the aggregated records.  Any advice on that one sir?
Paul S.Paul S.
I think you'd ultimately need two similar queries.  You can leave your aggregate result query as-is, but I'd insert a query in front of that one that returned actual subscription records.  Loop through those, building a map: 
Map<Id, Map<String, Map<String, List<Zuora__Subscription__c>>>>
Where Id = Zuora__Account__c, String = Zuora__Status__c, String = Product_Line__c, and List<Zuora__Subscription__c> = the list of records aggregated.

As you loop though the aggregate result, you could then use those values returned to get (ultimately) the list of subscription records that would make up each row in the aggregate result.

Might be a better way, but that's what initially comes to mind.