global class Schedule_CreditEvaluation_LastYearDetail implements Schedulable {

    global void execute(SchedulableContext sc) {
        
        String body;
        String bodyHeader;
        String footer;  
        List<Messaging.SingleEmailMessage> listMessageEmail = new List<Messaging.SingleEmailMessage>();
        Set<Id> setCustomerId = new Set<Id>();
        Map<Id, List<Credit_Evaluation__c>> mapCustomerCreditEvaluation = new Map<Id, List<Credit_Evaluation__c>>();
        map<Id, Credit_Evaluation__c> mapCustIdToCE = new map<Id, Credit_Evaluation__c>();
        List<Credit_Evaluation__c> ceCustomer = new List<Credit_Evaluation__c>(); 
        
        // getting the start date and end date for fetching credit evaluations approved last year 
        Date dtLastYearEndDate = null;
        Date dtLastYearStartDate = date.newInstance(System.today().year() - 1, System.today().month() , 01);
        if(System.today().month() == 1 || System.today().month() == 3 || System.today().month() == 5 || System.today().month() == 7 || System.today().month() == 8 || System.today().month() == 10 || System.today().month() == 12) {
            dtLastYearEndDate = date.newInstance(System.today().year() - 1, System.today().month(), 31);
        } else if (System.today().month() == 4 || System.today().month() == 6 || System.today().month() == 9 || System.today().month() == 11) {
            dtLastYearEndDate = date.newInstance(System.today().year() - 1, System.today().month(), 30);
        } else {
            dtLastYearEndDate = date.newInstance(System.today().year() - 1, System.today().month(), 29);
        }
        // comment the below two lines later
        // Date dtLastYearStartDate = date.newInstance(2013, 06, 01);
        // Date dtLastYearEndDate = date.newInstance(2013, 06, 31);
        
        List<Credit_Evaluation__c> creditEval = [Select c.Approved_By__c,c.Customer_Owner_ID__c,c.Last_Approved_Credit_Limit_Internal__c,c.Last_Approved_Credit_Limit_Customer__c, c.CreatedDate, c.Credit_Term__c, c.Proposed_Credit_Limit__c, c.Customer_Name__r.Name, c.Customer_Sold_to_Party_Code__c, c.Customer_Name__c, c.Id, c.Name, c.Current_Approval_date__c, c.Status__c, c.Customer_Name__r.Customer_Type__c From Credit_Evaluation__c  c where Status__c =: 'Approved' AND Current_Approval_date__c >=: dtLastYearStartDate AND Current_Approval_date__c <=: dtLastYearEndDate AND Customer_Name__r.Type = 'Customer' limit 50000];
        System.debug('@@ creditEval @@' + creditEval);
        if(creditEval.size() > 0) {
            for(Credit_Evaluation__c ce: creditEval) {
                setCustomerId.add(ce.Customer_Name__c);
            }
            List<Credit_Evaluation__c> checkRenewalDoneCE = [Select c.Approved_By__c,c.Customer_Owner_ID__c,c.Last_Approved_Credit_Limit_Internal__c,c.Last_Approved_Credit_Limit_Customer__c,c.Insured_Credit_Limit__c,c.Last_Approved_Credit_Term_Customer__c, c.CreatedDate, c.Credit_Term__c, c.Proposed_Credit_Limit__c, c.Customer_Name__r.Name, c.Customer_Sold_to_Party_Code__c, c.Customer_Name__c, c.Id, c.Name, c.Current_Approval_date__c, c.Status__c, c.Customer_Name__r.Customer_Type__c From Credit_Evaluation__c  c where Status__c =: 'Approved' AND Customer_Name__c IN: setCustomerId ORDER BY Customer_Name__c ASC limit 50000];
            if(!checkRenewalDoneCE.isEmpty() && checkRenewalDoneCE != null) {
                for(Credit_Evaluation__c chkRenewal: checkRenewalDoneCE) {
                    if(chkRenewal.Current_Approval_date__c >= dtLastYearStartDate) {
                        if(!mapCustomerCreditEvaluation.isEmpty() && mapCustomerCreditEvaluation != null) {
                            if(chkRenewal != null && chkRenewal.Customer_Name__c != null && !mapCustIdToCE.isEmpty() && mapCustIdToCE != null && mapCustIdToCE.get(chkRenewal.Customer_Name__c) != null && mapCustIdToCE.get(chkRenewal.Customer_Name__c).Customer_Name__c != null && mapCustIdToCE.get(chkRenewal.Customer_Name__c).Customer_Name__c == chkRenewal.Customer_Name__c) {
                                   ceCustomer.add(chkRenewal);
                                   mapCustomerCreditEvaluation.put(chkRenewal.Customer_Name__c, ceCustomer);
                            } else {
                                ceCustomer = new List<Credit_Evaluation__c>();
                                   ceCustomer.add(chkRenewal); 
                                   mapCustIdToCE.put(chkRenewal.Customer_Name__c, chkRenewal); 
                                   mapCustomerCreditEvaluation.put(chkRenewal.Customer_Name__c, ceCustomer);
                            }
                        } else {
                            ceCustomer = new List<Credit_Evaluation__c>();
                               ceCustomer.add(chkRenewal); 
                               mapCustIdToCE.put(chkRenewal.Customer_Name__c, chkRenewal); 
                               mapCustomerCreditEvaluation.put(chkRenewal.Customer_Name__c, ceCustomer);
                        }
                    }
                }
            }
            System.debug('@@ mapCustomerCreditEvaluation @@' +mapCustomerCreditEvaluation);
            Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
            
            //Ishwar 08-Oct-15 Removed hardcoded emails, get emails ids from given Public Group
            String[] toAddresses = CommonUtil.getGroupMemberEmailIds('CreditEvalRenew_Email');
            
            // change shirlin tan email address    
            //String[] toAddresses = new String[] {'ST@natsteel.com.sg'};
            
            // Setting the To Address & Cc Address
            mail.setToAddresses(toAddresses);
            mail.setBccSender(false);
            mail.setUseSignature(false);
            
            // Creating the main table for Credit Evaluations for the Email Body
            bodyHeader = '<table width="100%" cellpadding="0", border="1">';
            bodyHeader += '<th> Customer Name </th> <th> Customer Code </th> <th> Last Approved Credit Limit (Customer) </th><th>Last Approved Credit Limit (Internal)</th> <th> Last Approved Credit Term (Customer) </th> <th>Insured Limit</th> <th>Last Approved Date</th> <th>Customer Owner</th>';            
            body = '';
            
            // querying the Credit Evaluations for the ones which are approved 1 year before and also only for those Customers with Type Customer
            for(Credit_Evaluation__c ce: [Select c.Approved_By__c,c.Customer_Owner_ID__c,c.Last_Approved_Credit_Limit_Internal__c,
                                          c.Last_Approved_Credit_Limit_Customer__c,
                                          c.Insured_Credit_Limit__c,c.Last_Approved_Credit_Term_Customer__c, 
                                          c.CreatedDate, c.Credit_Term__c, c.Proposed_Credit_Limit__c, 
                                          c.Customer_Name__r.Name, c.Customer_Sold_to_Party_Code__c, c.Customer_Name__c, c.Id, 
                                          c.Name, c.Current_Approval_date__c, c.Status__c, 
                                          c.Customer_Name__r.Customer_Type__c From Credit_Evaluation__c  c where 
                                          Status__c =: 'Approved' AND Current_Approval_date__c >=: dtLastYearStartDate AND 
                                          Current_Approval_date__c <=: dtLastYearEndDate AND Customer_Name__r.Type = 'Customer'
                                          AND Last_Approved_Credit_Term_Customer__c != 'COD' AND 
                                          Last_Approved_Credit_Limit_Customer__c != 0
                                          limit 50000])
            {
                if(!mapCustomerCreditEvaluation.isEmpty() && mapCustomerCreditEvaluation != null) {
                    if(mapCustomerCreditEvaluation.get(ce.Customer_Name__c).size() == 1) {
                        String strApprovalDate = ce.Current_Approval_date__c.format();
                        body += '<td>' +ce.Customer_Name__r.Name + '</td>';
                        body += '<td>' +ce.Customer_Sold_to_Party_Code__c + '</td>';
                        body += '<td>' +ce.Last_Approved_Credit_Limit_Customer__c + '</td>';
                        body += '<td>' + ce.Last_Approved_Credit_Limit_Internal__c + '</td>';
                        body += '<td>' + ce.Last_Approved_Credit_Term_Customer__c + '</td>';
                        body += '<td>' + ce.Insured_Credit_Limit__c  + '</td>';  
                        body += '<td>' +strApprovalDate+ '</td>';
                        body += '<td>' +ce.Customer_Owner_ID__c+ '</td></tr>';
                    }
                }
            }
            
            
            
            // setting the footer for Email body
            footer = '<p> The above customer'+ '\'' +'s credit evaluation is due for renewal. </p>';
            footer += '<p> Please do the evaluation in Salesforce within 14 days. </p>';
            footer += 'Thank you.';
            
            // Set the Email body content 
            if(body != null && body != '') {
                body += '</table>';
                body = body.replace('null' , '') ;
            }
            
            //Ishwar 08-Oct-15 Removed hardcoded name, get name from given Public Group
            String toNames='';
            for(User u : [SELECT Name FROM User WHERE Email in :toAddresses])
            {
                toNames = toNames + u.Name +', ';
            }
            //mail.setHtmlBody('<p> Hi ' + toNames + '</p>' +bodyHeader + body + footer);
            mail.setHtmlBody('<p> Dear Credit Controllers' + '</p>' +bodyHeader + body + footer);
            
            // set the subject Name
            
            Integer iMonth = System.today().month();
            String strMonth;
            if(iMonth == 1) strMonth = 'Jan';
            else if(iMonth == 2) strMonth = 'Feb';
            else if(iMonth == 3) strMonth = 'Mar';
            else if(iMonth == 4) strMonth = 'Apr';
            else if(iMonth == 5) strMonth = 'May';
            else if(iMonth == 6) strMonth = 'June';
            else if(iMonth == 7) strMonth = 'July';
            else if(iMonth == 8) strMonth = 'Aug';
            else if(iMonth == 9) strMonth = 'Sep';
            else if(iMonth == 10) strMonth = 'Oct';
            else if(iMonth == 11) strMonth = 'Nov';
            else if(iMonth == 12) strMonth = 'Dec';

            String strSubject = String.valueof((System.today().year())) + ' - ' + strMonth;
            mail.setSubject('Renewal of Credit Evaluation for ' +strSubject);
            if(body != null && body != '') {
                listMessageEmail.add(mail);
            }
            System.debug('@@ listMessageEmail @@' +listMessageEmail);
            // send the email to respective customer Owners with details of Credit Request
            if(!listMessageEmail.isEmpty() && listMessageEmail != null) {
                Messaging.sendEmail(listMessageEmail);
            }
        }
    }
}