Auto Mail Sending Feature With CSV File Attachment

 Controller Function:

public function mslate_sync_report(){

// This function is used for fetching sync failed & success leads count and attaching failed leads and sending mail to managers using cron job

$automailer_details = $this->Cronjobs_model->get_automailer_details($module_name='mslate-lead-sync-status-report');

if(is_array($automailer_details) && count($automailer_details)>0){

$yesterday_date = date('Y-m-d', strtotime("-1 days"));

// Generating excel sheet for mail attachments

$this->load->dbutil();

        $this->load->helper('file');

        $this->load->helper('download');

        $delimiter = ",";

        $newline = "\r\n";

        

        $sql = "SELECT

  lead_id AS 'Lead Id',

  mfcw_id AS 'MFCWL Id',

  lead_cre_date AS 'Lead Created Date',

  cus_name AS 'Customer Name',

  cus_mobile AS 'Customer Mobile',

  appl_no  AS 'Appl No',

  (

    CASE WHEN lead_status = '2' THEN 'Open Leads' WHEN lead_status = '3' THEN 'Follow Up' WHEN lead_status = '4' THEN 'No Response' WHEN lead_status = '5' THEN 'In Process' WHEN lead_status = '6' THEN 'Cancelled' WHEN lead_status = '7' THEN 'Logged In'

  END

) AS 'Current Lead Status',

(

  CASE WHEN vertical = '0' THEN 'Used Car' WHEN vertical = '1' THEN 'Tractor' WHEN vertical = '2' THEN 'New Car' WHEN vertical = '3' THEN 'Mahindra New Car' WHEN vertical = '4' THEN '' WHEN vertical = '5' THEN 'DMT' WHEN vertical = '6' THEN 'Three Wheeler' WHEN vertical = '7' THEN 'Commercial Vehicle'

END

) AS 'Vertical',

emlap_request  AS 'Mslate Request',

emlap_response  AS 'Mslate Response'

FROM

  `table_name` WHERE source_request_id !='' AND response_code IS NULL AND lead_cre_date >= '$yesterday_date 00:00:00' AND lead_cre_date <= '$yesterday_date 23:59:59'";

        $query = $this->db->query($sql);

        $data = $this->dbutil->csv_from_result($query, $delimiter, $newline);

        file_put_contents("excel-reports/yesterdays-sync-failed-leads.csv",$data);

        //force_download("Previousdays_Reports".date('d-m-Y').".csv", $data);

        $url = base_url().'excel-reports/yesterdays-sync-failed-leads.csv';

    // For Success

$query1 = "SELECT

  count(lead_id) AS 'success_count'

FROM

  `table_name`

WHERE source_request_id !='' AND response_code = '200' AND lead_cre_date >= '$yesterday_date 00:00:00' AND lead_cre_date <= '$yesterday_date 23:59:59'";

$success_count = $this->db->query($query1)->result_array();

// For Failure

$query2 = "SELECT

  count(lead_id) AS 'failure_count'

FROM

  `table_name`

WHERE source_request_id !='' AND response_code IS NULL AND lead_cre_date >= '$yesterday_date 00:00:00' AND lead_cre_date <= '$yesterday_date 23:59:59'";

$failure_count = $this->db->query($query2)->result_array();

// Mail Functionalities Start  

  $ci = & get_instance();

      $ci->load->library('email');

      $config = array(

          'protocol'  => 'smtp',

          'smtp_host' => $automailer_details[0]['smtp_host'],

          'smtp_port' => $automailer_details[0]['smtp_port'],

          'smtp_crypto' => $automailer_details[0]['smtp_encryption'],

          'smtp_user' => $automailer_details[0]['smtp_user'],

          'smtp_pass' => $automailer_details[0]['smtp_pass'],

          'mailtype'  => 'html',

          'charset'   => 'utf-8'

      );

      $ci->email->initialize($config);

      $ci->email->set_mailtype("html");

      $ci->email->set_newline("\r\n");

      $ci->email->from($automailer_details[0]['smtp_user']);

      $ci->email->to($automailer_details[0]['receiver_mail_ids']);

      $ci->email->subject('Mslate Lead Sync Status Report');

      $body = "Dear Team,<br><br> Please find the yesterdays(".$yesterday_date.") sync status report.<br><br> Success Count: ".$success_count[0]['success_count']." <br><br> Failed Count: ".$failure_count[0]['failure_count']." ";

      $ci->email->message($body);

      $ci->email->set_header('X-Priority', '1 (Highest)\n');

      $ci->email->set_header('X-MSMail-Priority', 'High\n');

      $ci->email->set_header('Importance', 'High\n');

      $this->email->attach($url);

      return ($ci->email->send()) ? TRUE : FALSE;

  }else{

  echo "The requested module is currently inactive";

  }

}

Model Function:

public function get_automailer_details($module_name){

        $this->db->select('*');

        $this->db->from('automailer_config');

        $this->db->where('module_name',$module_name);

        $this->db->where('status','1');

        return $this->db->get()->result_array();

    }

Mysql Query:

CREATE TABLE `automailer_config` (

  `id` int(11) NOT NULL,

  `module_name` varchar(200) DEFAULT NULL,

  `receiver_mail_ids` text,

  `description` text,

  `smtp_user` varchar(200) DEFAULT NULL,

  `smtp_pass` varchar(200) DEFAULT NULL,

  `smtp_port` varchar(10) DEFAULT NULL COMMENT 'like tls->587, ssl->465, etc',

  `smtp_host` varchar(200) DEFAULT NULL COMMENT 'Like smtp.gmail.com, ssl://smtp.googlemail.com, etc ',

  `smtp_encryption` varchar(10) DEFAULT NULL COMMENT 'like tls, ssl, etc',

  `status` enum('1','2') DEFAULT '1' COMMENT '1-Active, 2- Inactive'

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `automailer_config` (`id`, `module_name`, `receiver_mail_ids`, `description`, `smtp_user`, `smtp_pass`, `smtp_port`, `smtp_host`, `smtp_encryption`, `status`) VALUES

(1, 'mslate-lead-sync-status-report', 'test@gmail.com,test1@gmail.com', 'It is used for fetching sync failed & success leads count and attaching failed leads and sending mail to managers', 'support@gmail.com', 'password', '587', 'smtp.gmail.com', 'tls', '1');