Friday, May 17

Sending EMail with attachment (Excel sheet) using PHP

Hi All, 


Here I write a program for Email with an attached excel document using PHP. Also I design dynamic table for maintaining count of course completed user.


<?php

$hostname="localhost"; // Enter your System address
$dbname="sample";    // Database name
$dbusername="root"; // Enter username of database
$dbpassword="";        // Enter if you have password of your MySql db
$DOWNLOADPATH='C:/Documents and Settings/Skitech/Desktop'; // File Downloadable location

error_reporting(E_ALL);

$date=date('dS M Y'); // date format
$date1=date('d_M_Y'); // filename date format

$today=date('l');
//if($today=='Friday'){
   
    $ActiveUser=0;
    $CompletedUser=0;
    $NAcount=0;
    $NotAttempted=0;
    $InprogressUser=0;
    $font_type='calibri';
    $font_size='16px';
       
        $styleArray = array(
            'font' => array(
            'bold' => true,
            )
        );

    $my_subject="Test Report as on ".$date;
       
    $my_message="<span style='font-family:".$font_type."; font-size:".$font_size.";'><br>
        Hi All,<br><br>  
    Please enter the text you want to display in body of message.

    <br><br>

    </span>
    <table style='font-family:".$font_type."; font-size:".$font_size."; border: 1px solid #000000; border-collapse:collapse;' width='400' border='1'>
      <tr height='30' bgcolor='#BFBEBE'>
        <td width='400'><b>&nbsp;User Type</b></td>
        <td width='100'><b>&nbsp;Count</b></td>
      </tr>";
   
    //Database connection
    $con = mysql_connect($hostname,$dbusername,$dbpassword);
    mysql_select_db($dbname, $con);
   
    //Getting count of completed status count
    $qry1 ="select course.title AS coursename,course.username AS username    from test_course course;";
    $result1= mysql_query($qry1);
    $data=  mysql_fetch_array($result1);
    $ActiveUser=mysql_num_rows($result1);
   
    //Getting count of incompleted status count
    $qry2 = "select distinct ss.userid,ss.username from test_course_history ss;";
    $result2= mysql_query($qry2);
    $CompletedUser=mysql_num_rows($result2);
   
    //Getting count of Not attempted status count
    $qry3 = "select distinct ss.userid,user.username from test_course_history ss, test_users user
            where user.userid=ss.userid and user.`status`='Active'";
    $result3= mysql_query($qry3);
    $NAcount=mysql_num_rows($result3);
    $InprogressUser=$NAcount-$CompletedUser;
   
    $NotAttempted=$ActiveUser-($CompletedUser+$InprogressUser);   

    $my_message.="
        <tr bgcolor='#FFFFFF'>
            <td height='30' width='400'>&nbsp;Users - Completed the course</td>
            <td height='30' width='100'>&nbsp;".$CompletedUser."</td>
        </tr>
        <tr bgcolor='#FFFFFF'>
            <td height='30' width='400'>&nbsp;Users - Learning In Progress</td>
            <td height='30' width='100'>&nbsp;".$InprogressUser."</td>
        </tr>
        <tr bgcolor='#FFFFFF'>
            <td height='30' width='400'>&nbsp;Users - Not Attempted</td>
            <td height='30' width='100'>&nbsp;".$NotAttempted."</td>
        </tr>
        <tr bgcolor='#FFFF00'>
            <td height='30' width='400'>&nbsp;Total Active Users</td>
            <td height='30' width='100'>&nbsp;".$ActiveUser."</td>
        </tr>
    </table><br><br><span style='font-family:".$font_type."; font-size:".$font_size.";'>
    Thanks,<br>
    Regards,<br>
    Balamurugan M.R<br>
    SkiTech Ltd<br>
    <br>
    </span>";
    echo $my_message;

    $query="Select * from test_courses";
    $result=mysql_query($query);
   
    $filename='test_Report_as_on_'.$date1.'.xls';
       
    $file_headerdata= 'Username' . "\t" . 'Coursename' . "\t" . 'Completion Status' ."\t" . 'Total Attempts'."\n";
        $file_headerdata = getStyle($file_headerdata)->applyFromArray($styleArray);
    while($row=mysql_fetch_array($result))
    {
        if($row[1]=='Test Online Module')
            $row[1]='Test Online Course';
        if($row[3]==0 && $row[2]!='completed')
            $row[2]='Not Attempted';
        else if($row[3]!=0 && $row[2]!='completed')
            $row[2]='Incomplete';
       
        $file_data[] =$row[0]."\t".$row[1]."\t".$row[2]."\t".$row[3]. "\n";
    }
    mysql_close($con);
        $fname= writetofile($filename,$file_data,$file_headerdata);
   

    // Send mail functionality   
    $my_name = "SKiTECH SUPPORT";
    $my_mail = "Skitech.slm@gmail.com";
    $my_replyto = "skitech.support@gmail.com";
    $to = "skitech.slm@gmail.com";   
    $cc="krish.mr@gamil.com";       
    send_mail($to, $my_mail, $my_name, $my_replyto, $my_subject, $my_message,$cc,$fname);
//}

//Write all data in Excel file and return the File name with its location 
function writetofile($filename,$file_data,$file_headerdata) {
   
    global $DOWNLOADPATH;
    $handle = fopen($DOWNLOADPATH.$filename, 'wb') or die("ERR:Can't open file");
    fwrite($handle, $file_headerdata);
    foreach($file_data as $key => $val){
        echo "\n";
        fwrite($handle, $val);
    }
    fclose($handle);
    $fname=$DOWNLOADPATH.$filename;
    return $fname;
}

//Send mail concept to send a mail with Attachment
function send_mail($mailto, $from_mail, $from_name, $replyto, $subject, $message,$cc,$fname) {
    //File  open method
    $fp = fopen($fname, "rb");
    $upload_size=filesize($fname);
    $file = fread($fp, $upload_size);

    $file = chunk_split(base64_encode($file));
    $num = md5(time());
    $upload_type=filetype($fname);
   
     //Normal headers

    $headers  = "From: ".$from_name." <".$from_mail.">\r\n";
    $headers .= "Reply-To: ".$replyto."\r\n";
    $headers .= "Cc: ".$cc."\r\n";
    $headers  .= "MIME-Version: 1.0\r\n";
    $headers  .= "Content-Type: multipart/mixed; ";
    $headers  .= "boundary=".$num."\r\n";
    $headers  .= "--$num\r\n";  

    // With message
   
    $headers .= "Content-Type: text/html; charset=iso-8859-1\r\n";
    $headers .= "Content-Transfer-Encoding: 8bit\r\n";
    $headers .= $message."\r\n\r\n";
    $headers .= "--".$num."\n";

    // Attachment headers

    $headers  .= "Content-Type:".$upload_type." ";
    $headers  .= "name=\"".$fname."\"r\n";
    $headers  .= "Content-Transfer-Encoding: base64\r\n";
    $headers  .= "Content-Disposition: attachment; ";
    $headers  .= "filename=\"".basename($fname)."\"\r\n\n";
    $headers  .= "".$file."\r\n";
    $headers  .= "--".$num."--";



    //Send mail method

    if (mail($mailto, $subject, "", $headers)) {
    echo "mail send ... OK"; // or use booleans here
    } else {
    echo "mail send ... ERROR!";
    }
}

?>


OUTPUT:
~~~~~~~~~~

No comments:

Post a Comment

Backup files to google drive using PHP coding

 Dear All, To backup files to Google Drive using PHP coding, you can use the Google Drive API and the Google Client Library for PHP. Here...