Apex modifications

Hello all,

I having a hard time when trying to modify a apex code from the previous person that was working in my company. He was a developer and I'm not. My level in Apex is normal, I'm an administrator and I'm trying to pull this up because is kind of urgent for my company.

We have a Scorecard system that will keep track of a number of fields from different objects. The scorecard object contains these cards for each account owner, contact related and leads.

The all idea is that if you fill in a number of fields you will get a percentage in these scorecards. The scorecards have a page layout and in those page layouts you have fields that will keep track of the percentages. Lest say you fill in 50% of you phone field on you accounts, there is a field in your scorecard page layout with a field Account Phone Number Filled = 50%.

This is done with a number of fields from different objects like I mentioned before. There is an apex schedule that will trigger the apex that update this scorecards every hour.

This is the script:

global class ScoreCardGenerator {

    global void processScoreCards() {

        final String RESOURCE_NAME_HAS = 'ScorecardQueriesHAS';
        final String RESOURCE_NAME_ALL = 'ScorecardQueriesALL';
    
        Map<String, Map<String, String>> scHASQueries = new Map<String, Map<String, String>>();
        Map<String, Map<String, String>> scALLQueries = new Map<String, Map<String, String>>();
        Map<Id, Score_Card__c> scoreCards = new Map<Id, Score_Card__c>();
        Map<String, String> fieldsToUpdateHAS = new Map<String, String>();
        Map<String, String> fieldsToUpdateALL = new Map<String, String>();
        StaticResource theStaticResource;
        List<Score_Card__c> updatedScoreCards = new List<Score_Card__c>();
        Set<Id> ownerIdKeys = new Set<Id>();
        Map<Id, Map<String, Double>> userHASResults = new Map<Id, Map<String, Double>>();
        Map<Id, Map<String, Double>> userALLResults = new Map<Id, Map<String, Double>>();
        DateTime todayDate = System.Now();
        DateTime startOfYear = DateTime.newInstanceGMT(todayDate.year(), 1, 1, 0, 0, 0);
        Integer currentHour = System.Now().hourGMT();

        try {
            theStaticResource = [SELECT id, Body FROM StaticResource WHERE name = :RESOURCE_NAME_HAS];
            String bodyText = theStaticResource.body.toString();
            List<String> bodyLines = bodyText.split('\n');
            for(String line : bodyLines) {
                if(line.trim().length() != 0) {
                    List<String> keyValuePairs = line.split('~');
                    String mapKey;
                    Map<String, String> tempMap = new Map<String, String>();
                    String objectName = '';
            
                    for(String pairString : keyValuePairs) {
                
                        // Some boilerplate splitting
                        List<String> pairs = pairString.split('#');
                        String key = pairs[0].trim();
                        String value = pairs[1].trim();
    
                        if (key == 'ID') {
                            mapKey = value;
                        }
                        else {
                            tempMap.put(key, value);
                        }
                        
                        if (key == 'OBJECT') {
                            objectName = value;
                        }   
            
                        if (key == 'FIELD') {
                            fieldsToUpdateHAS.put(value, objectName);
                        }
                    }
                    scHASQueries.put(mapKey, tempMap);
                }
            }
        } catch(System.QueryException e) {
            System.debug(e);
            // You should always let the admins/devs know if
            // something unexpected happened.
            // e.g. ExceptionUtils.mail(Administrator);
        }
           
        try {
            theStaticResource = [SELECT id, Body FROM StaticResource WHERE name = :RESOURCE_NAME_ALL];
            String bodyText = theStaticResource.body.toString();
            List<String> bodyLines = bodyText.split('\n');
            for(String line : bodyLines){
                if(line.trim().length() != 0) {
                    List<String> keyValuePairs = line.split('~');
                    String mapKey;
                    Map<String, String> tempMap = new Map<String, String>();
                    String objectName = '';
            
                    for(String pairString : keyValuePairs) {
            
                        // Some boilerplate splitting
                        List<String> pairs = pairString.split('#');
                        String key = pairs[0].trim();
                        String value = pairs[1].trim();
            
                        if (key == 'ID') {
                            mapKey = value;
                        }
                        else {
                            tempMap.put(key, value);
                        }
                        
                        if (key == 'OBJECT') {
                            objectName = value;
                        }   
            
                        if (key == 'FIELD') {
                            fieldsToUpdateALL.put(value, objectName);
                        }
                    }
                    scALLQueries.put(mapKey, tempMap);
                }
            }
        } catch(System.QueryException e) {
            System.debug(e);
            // You should always let the admins/devs know if
            // something unexpected happened.
            // e.g. ExceptionUtils.mail(Administrator);
        }   
        
        String businessUnitSalesTeam;
        if (currentHour == 0 || currentHour == 12) {
            businessUnitSalesTeam = 'Business_Unit__c = \'Benelux\'';
        }
        else if (currentHour == 1 || currentHour == 13) {
            businessUnitSalesTeam = 'Business_Unit__c = \'DACHI\' AND Sales_Team__c = \'Centre\'';
        }
        else if (currentHour == 2 || currentHour == 14) {
            businessUnitSalesTeam = 'Business_Unit__c = \'DACHI\' AND Sales_Team__c = \'North\'';
        }
        else if (currentHour == 3 || currentHour == 15) {
            businessUnitSalesTeam = 'Business_Unit__c = \'DACHI\' AND Sales_Team__c = \'South\'';
        }
        else if (currentHour == 4 || currentHour == 16) {
            businessUnitSalesTeam = 'Business_Unit__c = \'DACHI\' AND Sales_Team__c IN (\'Austria\', \'Dresden\', \'Italy\', \'Switzerland\')';
        }
        else if (currentHour == 5 || currentHour == 17) {
            businessUnitSalesTeam = 'Business_Unit__c IN (\'DB\', \'Consumer\', \'Hong Kong\')';
        }    
        else if (currentHour == 6 || currentHour == 18) {
            businessUnitSalesTeam = 'Business_Unit__c = \'Japan\'';
        }
        else if (currentHour == 7 || currentHour == 19) {
            businessUnitSalesTeam = 'Business_Unit__c = \'Spain\'';
        }
        else if (currentHour == 8 || currentHour == 20) {
            businessUnitSalesTeam = 'Business_Unit__c = \'UK\' AND Sales_Team__c = \'Key\'';
        }
        else if (currentHour == 9 || currentHour == 21) {
            businessUnitSalesTeam = 'Business_Unit__c = \'UK\' AND Sales_Team__c IN (\'Midlands\', \'MOD\')';
        }
        else if (currentHour == 10 || currentHour == 22) {
            businessUnitSalesTeam = 'Business_Unit__c = \'UK\' AND Sales_Team__c IN (\'North\', \'Telesales\')';
        }
        else if (currentHour == 11 || currentHour == 23) {
            businessUnitSalesTeam = 'Business_Unit__c = \'UK\' AND Sales_Team__c = \'South\'';
        }

    if (businessUnitSalesTeam != null) {
            for (Score_Card__c sc : Database.Query('Select Id, User__c FROM Score_Card__c WHERE Active__c = TRUE AND ' + businessUnitSalesTeam)) {
                scoreCards.put(sc.User__c, sc);
            }
        }
        ownerIdKeys = scorecards.keyset();
        for (Integer i = 1; i < scALLQueries.size()+1; i++) {
            String queryALL = scALLQueries.get(String.valueOf(i)).get('QUERYALL');
            for (AggregateResult arALL : Database.Query(queryALL + ' GROUP BY OwnerId ORDER BY OwnerId')) {
                Map<String, Double> tempMap = new Map<String, Double>();    
                if (userALLResults.containsKey(String.valueOf(arALL.get('OwnerId')))) {
                    tempMap = userALLResults.get(String.valueOf(arALL.get('OwnerId')));
                }
                tempMap.put(scALLQueries.get(String.valueOf(i)).get('OBJECT'), Double.valueOf(arALL.get('expr0')));
                userALLResults.put(String.valueOf(arALL.get('OwnerId')), tempMap);                      
            }
        } 
        for (Integer i = 1; i < scHASQueries.size()+1; i++) {
            String queryHAS = scHASQueries.get(String.valueOf(i)).get('QUERYHAS');
            for (AggregateResult arHAS : Database.Query(queryHAS + ' AND OwnerId IN :ownerIdKeys GROUP BY OwnerId ORDER BY OwnerId')) {
                Map<String, Double> tempMap = new Map<String, Double>();
                if (userHASResults.containsKey(String.valueOf(arHAS.get('OwnerId')))) {
                    tempMap = userHASResults.get(String.valueOf(arHAS.get('OwnerId')));
                }
                tempMap.put(scHASQueries.get(String.valueOf(i)).get('FIELD'), Double.valueOf(arHAS.get('expr0')));
                userHASResults.put(String.valueOf(arHAS.get('OwnerId')), tempMap);
            }
        }
        for (Id userId : ownerIdKeys) {
            SObject updateSC = scoreCards.get(userId);
            Set<String> hasFieldNames = fieldsToUpdateHAS.keySet();
            Set<String> allFieldNames = fieldsToUpdateALL.keySet();
            for (String field : hasFieldNames) {

        /*
                if (!userHASResults.get(userId).containsKey(field) || !userALLResults.get(userId).containsKey(fieldsToUpdateHAS.get(field)) || fieldsToUpdateHAS.get(field) == null) {
                  
                    updateSC.put(field, 0);
                }
        */
              if (fieldsToUpdateHAS.get(field) != null && !userALLResults.get(userId).containsKey(fieldsToUpdateHAS.get(field))) {
                updateSC.put(field, 100);
              }
                else if (!userHASResults.get(userId).containsKey(field) || fieldsToUpdateHAS.get(field) == null) {
                    updateSC.put(field, 0);
                }
                else {
                    updateSC.put(field, (userHASResults.get(userId).get(field) / userALLResults.get(userId).get(fieldsToUpdateHAS.get(field))) * 100);
                }
            }
            for (String field : allFieldNames) {
                if (!userALLResults.get(userId).containsKey(fieldsToUpdateALL.get(field))) {
                    updateSC.put(field, 0);
                }
                else {
                    updateSC.put(field, userALLResults.get(userId).get(fieldsToUpdateALL.get(field)));
                }
            }
            updatedScoreCards.add((Score_Card__c)updateSC);
        }       
        if (!updatedScoreCards.isEmpty()) { 
            update(updatedScoreCards);
        }
    } 
}

What I want is just to add another field in the scorecard object to track the Name in the Contact object. I noticed that on the scrip there are two lines:

        final String RESOURCE_NAME_HAS = 'ScorecardQueriesHAS';
        final String RESOURCE_NAME_ALL = 'ScorecardQueriesALL';

These two lines are very important becasue they contain the query for the fields that are in the scorecards.

Here it is one of the files (ScorecardQueriesHAS):

ID#1~OBJECT#Event~FIELD#Events_with_Comments__c~QUERYHAS#SELECT OwnerId  count(Subject) FROM Event WHERE StartDateTime >= :startOfYear AND EndDateTime <= :todayDate AND Sales_Comments__c != '',,
ID#2~OBJECT#Account~FIELD#Accounts_with_Address__c~QUERYHAS#SELECT ownerId count(Name) FROM Account WHERE BillingStreet != '' AND BillingCity != '' AND BillingPostalCode != '' AND BillingCountry != '', ,
ID#3~OBJECT#AccountExact~FIELD#Accounts_with_Exact_Debtor_Number__c~QUERYHAS#SELECT OwnerId  count(Name) FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE IsWon = true AND RecordTypeId != '01280000000PydL') AND Exact_Debtor_Number__c != '',,
ID#4~OBJECT#Account~FIELD#Accounts_with_Segmentation__c~QUERYHAS#SELECT ownerId count(Name) FROM Account WHERE ((Industry != '' AND Life_Segment__c != '' AND Sub_Sector__c != '') OR (Segment__c != '' AND Industry__c != '' AND Dimension__c != '' AND Organization__c != '')), ,
ID#5~OBJECT#Account~FIELD#Accounts_with_Status__c~QUERYHAS#SELECT ownerId count(Name) FROM Account WHERE Account_Status__c != '', ,
ID#6~OBJECT#Account~FIELD#Accounts_with_Territory__c~QUERYHAS#SELECT ownerId count(Name) FROM Account WHERE Territory__c != '', ,
ID#7~OBJECT#Account~FIELD#Accounts_with_Zone__c~QUERYHAS#SELECT ownerId  count(Name) FROM Account WHERE Zones__c != '',,
ID#8~OBJECT#Account~FIELD#Accounts_with_Phone_Number__c~QUERYHAS#SELECT ownerId count(Name) FROM Account WHERE Phone != '', ,
ID#9~OBJECT#Account~FIELD#Contacts_with_Email_Address__c~QUERYHAS#Select ownerId  COUNT(Name) FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE Email != '' AND RecordTypeId != '01280000000PydJ'),,
ID#10~OBJECT#OpportunityProduct~FIELD#Open_Opportunities_with_Products__c~OBJECT#Opportunity~QUERYHAS#SELECT ownerId count(Name) FROM Opportunity WHERE IsClosed = FALSE AND HasOpportunityLineItem = true AND StageName NOT IN ('F- First Phase Open' 'P - Qualified Prospect'), ,
 
ID#11~OBJECT#Opportunity~FIELD#Open_Opportunities_with_Net_Revenue__c~QUERYHAS#SELECT OwnerId  count(Name) FROM Opportunity WHERE IsClosed = FALSE AND Net_Revenue__c != NULL,,
ID#12~OBJECT#Account~FIELD#Contact_with_First_Name__c~QUERYHAS#Select ownerId  COUNT(Name) FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE Name != '' AND RecordTypeId != '01280000000PydJ'),,

I added the last line ID#12.

The first think that I'm not sure is when I try to view ScorecardQueriesHAS from the the Static Resources. I see that the file is a

MIME Type

application/vnd.ms-excel

and if I click on View file, I get a file and when I try to open this file I get the msg in excel: 'Excel has detected that 'ScorecardQueriesHAS (11).xls' is a SYKL file, but cannot load it. Either the file has errors or it is not a SKYLK file format.'

I beileve this is a csv file but I was wondering if there is any particular way to save this files or to modify them, because when I modified the file and put it back to the static resourcers the line ID#12 is not doing what it is suppose to do.

So, I need to know what I'm doing wrong here in order for me to be able to modify this. Any ideas?

One more question :( , I have to wait about one hour to see if there is any luck after making any modification. There is a way to execute this from the Developer Console?

Thanks in advance.

February 5, 2013
·
Like
0
·
Follow
0

Best Answer chosen by Admin (Salesforce Developers)

Thomas Dvornik
Interesting script. First, if you have a developerment cycle, I would greatly refactor and simplify this scripts. Just at a quick glance, there are several parts that do the same thing that can be refactored into methods. Then you could actually call that function with your added line to see if id does what you want it to do.

Second, I'm kind of surprised that script works. I must be missing somehting because the way it is spliting your resource, you should end up with the following query that gets executed by Database.query which is an invalid query.

Select ownerId COUNT(Name) FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE Name != '' AND RecordTypeId != '01280000000PydJ'),, AND OwnerId IN (...) GROUP BY OwnerId ORDER BY OwnerId'
You can always run queries in the developer console as well to see what they return. For example, in the query tab, this one works for me.

Select ownerid, COUNT(Name) FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE Name != '') GROUP BY OwnerId ORDER BY OwnerId

With that said, back to the problem at hand. I don't think that file should be application/vnd.ms-excel. Since you are just getting the text and parsing it yourself, you should be able to change the type to txt, and that should get rid of your problem modifing the file.

It seems like you could still update the file though, it just wasn't doing what you wanted it to do? What was it doing? Was there an error, or just not the results you wanted? You should be able to run it in the Developer Console by executing anonymous "new ScoreCardGenerator().processScoreCards();"

Hope that helps.

February 6, 2013
·
Like
0
·
Dislike
0

ZurMiles
Is this not clear? ... No reactions :(

February 6, 2013
·
Like
0
·
Dislike
0

This was selected as the best answer

ZurMiles
Thomas, thanks.
' ScoreCardGenerator scg = new ScoreCardGenerator();scg.processScoreCards();'I was running this code to execute the script so I don't have to wait. :)

You are right over the 'count', after running the code on the developer console I was getting and unexpected error with 'count'. I was missing the coma you mentioned in your post.

I have change the queries to a text file and it looks fine, I got an error regarding a '!' which was part of an '!=' expression operator that had a space between.
The error I'm getting now is 'Attempt to de-reference a null object' on the developer console.

So,
it just wasn't doing what you wanted it to do?
The script works fine if I don't modify anything on the query file scorecardQueriesHAS

I want to have another field on the Score_Card__c , 'Contact_with_First_Name__c', object that will give me a percentage based on the Name field from the contact inside the accounts.
Right now it is not happening.

February 7, 2013
·
Like
0
·
Dislike
0

ZurMiles
Thomas, thanks man ... it works now.
Now I can just use any field to be part of the Score_Card__c.
Again, small details ....

Thanks again.

February 7, 2013
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

Apex modifications

All Answers

You need to sign in to do that.