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> User Type</b></td>
<td width='100'><b> 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'> Users - Completed the course</td>
<td height='30' width='100'> ".$CompletedUser."</td>
</tr>
<tr bgcolor='#FFFFFF'>
<td height='30' width='400'> Users - Learning In Progress</td>
<td height='30' width='100'> ".$InprogressUser."</td>
</tr>
<tr bgcolor='#FFFFFF'>
<td height='30' width='400'> Users - Not Attempted</td>
<td height='30' width='100'> ".$NotAttempted."</td>
</tr>
<tr bgcolor='#FFFF00'>
<td height='30' width='400'> Total Active Users</td>
<td height='30' width='100'> ".$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:
~~~~~~~~~~
