global class testoppty implements Database.Batchable<sObject> {
    public String query = 'SELECT Realtor__c,Realtor__r.Email, ConvertedOpportunityId, Name, Phone,  ' 
                          +  'Status, Loan_Officer_Lookup_Name__c ' 
                          +   ' FROM Lead';
    public EmailTemplate templateId = [Select Id,HtmlValue,Subject from EmailTemplate where name = 'RealtorRecord' LIMIT 1];

    global Database.QueryLocator start(Database.BatchableContext bc) {

        query += ' WHERE CreatedDate = LAST_MONTH  AND Realtor__c != null';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, list<Lead> allLeads) {
        
        List<String> convertedOppId = new List<String>();
        for(Lead l: allLeads){
	         convertedOppId.add(l.ConvertedOpportunityId); 
        }
        Map<Id, Opportunity> opptyMap = new Map<Id, Opportunity>([SELECT Id,Name,StageName FROM Opportunity WHERE Id IN: convertedOppId ]);  
        Map<Id,List<Lead>> leadMap = new Map<Id,List<Lead>>();
        List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMEssage>();
        if(allLeads != null && allLeads.size() > 0){
            for(Lead l: allLeads){
                if(!leadMap.containsKey(l.Realtor__c)){
                    leadMap.put(l.Realtor__c, new List<lead>());
                }
                leadMap.get(l.Realtor__c).add(l);
            }
        }
        if(leadMap.keySet().size() > 0){
            Map<Id,Contact> officers = new Map<Id,Contact>([SELECT Id,Email,Name FROM Contact WHERE Id IN: leadMap.keySet()]);
            for(Id i: leadMap.keySet()){
                Contact con = officers.get(i);
                System.debug(con);
                if(String.isnOtBlank(con.Email)){
                    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                    mail.setToAddresses(new String[]{con.EMail});
                    mail.setSubject(templateId.Subject);
                    String html = templateId.HtmlValue;
                    html = html.replace('||RealtorName||',con.Name);
                    String leadsTable = '<table cellpadding="3" cellspacing="3" width="100%" align="center" border="1" style="border-collapse:collapse;">'+
                        '<tr style="font-weight:bold;"><td>Name</td><td>Phone</td><td>Status</td><td>Loan Officer Name</td></tr>';
                    for(Lead l: leadMap.get(i)){
                        leadsTable += '<tr><td>'+l.Name+'</td>'+
                            '<td>'+l.Phone+'</td><td>'+l.Status+'</td>'+
                            '<td>'+l.Loan_Officer_Lookup_Name__c+'</td></tr>';
                    }
                    leadsTable += '</table>';
                    String opptyTable = '<table cellpadding="3" cellspacing="3" width="100%" align="center" border="1" style="border-collapse:collapse;">'+
                        '<tr style="font-weight:bold;"><td>Name</td><td>Stage Name</td></tr>';
                    
                    for(Lead l: leadMap.get(i)){
                        Opportunity o = opptyMap.get(l.ConvertedOpportunityId);                        
                        opptyTable += '<tr><td>'+o.Name+'</td>'
                            +'<td>'+o.StageName+'</td></tr>';
                    }
                    opptyTable += '</table>';
                    html = html.replace('||Leads||',leadsTable);
                    html = html.replace('||Opportunity||',opptyTable);
                    html = html.replace('null',' ');
                    mail.setHTMLBody(html);
                    mails.add(mail);
                }
            }
        }
        if(mails.size() > 0){
            Messaging.sendEmail(mails);
        }
    }

    global void finish(Database.BatchableContext BC) {}
}