Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/php
- <?php
- //in the cron job crontab -e add the following to execute this script
- //every day at 8am
- //0 8 * * * /filepath/checkEvents.php
- $server='localhost';
- $username='root';
- $password='h0liday';
- $database='SS_mysite';
- mysql_connect($server, $username, $password);
- @mysql_select_db($database)or die; //could add errors to a log
- //get todays date
- $today = date('Y-m-d');
- //date 5days ago
- $emailDate=strtotime( '-5 days' , strtotime ( $today ) ) ;
- $emailDate=date('Y-m-d', $emailDate);
- //$reportDetails="";
- $reportDetails.='<html><body>';
- function addContract($string, $reportType){
- $query="SELECT Contract.ID, Contract.ContractType, Organisation.Name as Organisation, School.Name as School, Concat(Person.FirstName,' ', Person.Surname)as Person, Contract.ContractStarts, Contract.ContractEnds, Contract.Deadline FROM Contract
- LEFT JOIN Organisation ON Contract.OrganisationID=Organisation.ID
- LEFT JOIN School ON School.ID=Contract.SchoolID
- LEFT JOIN Person ON Person.ID=Contract.PersonID ".$string."";
- $result=mysql_query($query);
- $num =mysql_numrows($result);
- if($num!=0){
- //add to report
- $reportDetails.=$reportType;
- $reportDetails.="<table style='margin: 1em; border-collapse: collapse;'><tr>
- <th style='padding: .3em; border: 1px #ccc solid;'>ID</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>Type</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>Organisation</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>School</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>Person</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>Contract Starts</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>Contract Ends</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>Deadline</th></tr>";
- for($i=0;$i<count($result);$i++){
- $reportDetails.="<tr><td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "ID")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "ContractType")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "Organisation")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "School")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "Person")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "ContractStarts")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "ContractEnds")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "Deadline")."</td></tr>";
- }
- $reportDetails.="</table><br />";
- }
- return $reportDetails;
- }
- function addEvent($string, $reportType){
- $query="SELECT EventLog.ID, EventLog.Priority, EventLog.EventLogType, EventLog.Review, Organisation.Name as Organisation, School.Name as School, CONCAT(Person.FirstName,' ', Person.Surname)as Person FROM EventLog
- LEFT JOIN Organisation ON Organisation.ID=OrganisationID
- LEFT JOIN School ON School.ID=EventLog.SchoolID
- LEFT JOIN Person ON Person.ID=EventLog.PersonID ".$string."";
- $result=mysql_query($query);
- $num=mysql_numrows($result);
- if($num!=0){
- //add to report
- $reportDetails.=$reportType;
- $reportDetails.="<table style='margin: 1em; border-collapse: collapse;'><tr>
- <th style='padding: .3em; border: 1px #ccc solid;'>ID</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>Priority</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>Event Type</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>Review</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>Organisation</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>School</th>
- <th style='padding: .3em; border: 1px #ccc solid;'>Person</th></tr>";
- for($i=0;$i<count($result);$i++){
- $reportDetails.="<tr><td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "ID")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "Priority")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "EventLogType")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "Review")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "Organisation")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "School")."</td>";
- $reportDetails.="<td style='padding: .3em; border: 1px #ccc solid;'>".mysql_result($result, $i, "Person")."</td></tr>";
- }
- $reportDetails.="</table><br />";
- }
- return $reportDetails;}
- //get all events that have not closed
- $reportDetails.=addEvent("WHERE Closed=0","<b>Unclosed Events</b>");
- //what eventlogs need to be reviewed
- $reportDetails.=addEvent("Where Review='".$today."'","<b>Events to Review</b>");
- //email what contracts are not complete
- $reportDetails.=addContract("WHERE Complete=0","<b>Uncomplete Contracts</b>");
- //email mag contracts for deadlines
- $reportDetails.=addContract("WHERE ContractType='Magazine' AND Deadline BETWEEN '".$emailDate."' AND '".$today."'","<b>Magazine Deadlines about to End</b>");
- //email web contracts for deadlines
- $reportDetails.=addContract("WHERE ContractType='Web' AND Deadline BETWEEN '".$emailDate."' AND '".$today."'","<b>Website Deadlines about to End</b>");
- //email when web contract starts to put on the web
- $reportDetails.=addContract("WHERE ContractType='Web' AND ContractStarts='".$today."'","<b>Web Adverts Online Today</b>");
- //email when contract end for web
- $reportDetails.=addContract("WHERE ContractType='Web' AND ContractEnds='".$today."'","<b>Web Adverts Offline today</b>");
- //email when Magazine contract 5days before deadline
- $reportDetails.=addContract("WHERE ContractType='Magazine' AND Deadline BETWEEN '".$emailDate."' AND '".$today."'","<b>Magazine Deadline</b>");
- //email 5days before mag contract ends
- $reportDetails.=addContract("WHERE ContractType='Magazine' AND ContractEnds BETWEEN '".$emailDate."' AND '".$today."'","<b>Magazine Contracts about to end</b>");
- mysql_close;
- //mail out
- $to ="monica@localhost";
- $subject="Tearaway Daily DB report";
- $message="<H3>Tearaway DB Report For ".date('d F Y')."</H3><br />".$reportDetails."<br /> Cheers, <br />Tearaway Post Fairy";
- $headers.='FROM:<monica@localhost>'."\nContent-Type:Text/HTML; charset=iso-8859-1\n";
- mail($to, $subject, $message."</body></html>", $headers);
- ?>
Add Comment
Please, Sign In to add comment