AppGini Community Forums

Good day,

Can somebody give me some pointers please? I want to send out a notification email (alert e-mail) to the project manager if a contract expire in a months time and then in3,2 and in a weeks time in the table:

CREATE TABLE IF NOT EXISTS `procurement_reporting_below_r500` (
`procurement_reporting_below_r500k_id` int(11) NOT NULL AUTO_INCREMENT,
`contract_number` varchar(40) DEFAULT NULL,
`programme_name_as_per_budget` int(11) DEFAULT NULL,
`cost_centre_number` varchar(40) DEFAULT NULL,
`type_of_services` text,
`industrial_sector` int(11) DEFAULT NULL,
`planned_method_of_procurement` int(11) DEFAULT NULL,
`total_awarded_value` decimal(1,0) DEFAULT NULL,
`source_of_funding` int(11) DEFAULT NULL,
`advertisement_date` date DEFAULT NULL,
`briefing_session` int(11) NOT NULL,
`briefing_session_date` date DEFAULT NULL,
`closing_date` date DEFAULT NULL,
`evaluation_date` date DEFAULT NULL,
`date_of_adjudication` date DEFAULT NULL,
`contract_signoff_date` date DEFAULT NULL,
`contract_started` date DEFAULT NULL,
`contract_terminated` date DEFAULT NULL,
`contract_duration` varchar(40) DEFAULT NULL,
`project_manager` int(11) DEFAULT NULL,
`delivery_lead_time` varchar(40) DEFAULT NULL,
PRIMARY KEY (`procurement_reporting_below_r500k_id`),
KEY `programme_name_as_per_budget` (`programme_name_as_per_budget`),
KEY `industrial_sector` (`industrial_sector`),
KEY `planned_method_of_procurement` (`planned_method_of_procurement`),
KEY `source_of_funding` (`source_of_funding`),
KEY `briefing_session` (`briefing_session`),
KEY `project_manager` (`project_manager`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

in the hook procurement_planning_above_r500.php the following code is inserted both in before_insert and before_update function and the correct date is displayed

function procurement_planning_above_r500_before_insert(&$data, $memberInfo, &$args){

$std = strtotime($data['contract_started']);
$ste = strtotime($data['contract_terminated']);
$years = date('Y',$ste) - date('Y',$std);
$months = date('m',$ste) - date('m',$std);
if ($months < 0)
{
$years--;
$months = $months + 12;
}

$days = date('d',$ste) - date ('d',$std);

if ($days < 0)
{
$months--;
$days = $days + 30;
}
$data['contract_duration'] = $years . " Years " . $months . " Months " . $days . " Days";

return TRUE;
}

Now: What email code (if I create notifyemail.php) must be written in the notifyemail.php order to notify the project manager of the status of the contract or when budget plans are due? I also presume I must create a cron job as well?

Please assist.

Thank you.

Kind regards.

Mara

Dear All,

Do not worry, I did find the solution!

Thank you.

Kind regards,

Mara

Great. Glad you found the solution.

Please share the solution so the next traveler with a similar problem can learn.

Sure, here it is - it is connecting to the specific table so you must change your settings accordingly!

Call it notifyemail.php

<?php
$dbServer = "127.0.0.1";
$dbUsername = "username connect to db";
$dbPassword = "**********";
$dbDatabase = "Your dbname";
$db = mysql_connect($dbServer,$dbUsername,$dbPassword,$dbDatabase);
echo "Error: " . mysql_error() . "</br>";
$n = date("Y-m-d");
mysql_select_db($dbDatabase,$db);
$sql = "SELECT * FROM procurement_planning_above_r500";(table name)
$mq = mysql_query($sql,$db);
echo "Query Error: " . mysql_error() . "</br>";
while ($row = mysql_fetch_row($mq))
{
$email = $row[12]; ( count from 0 until your email adress column)
$subjectname = $row[0];
$exp = $row[15]; (the due date expire)
$dd = strtotime($exp);
$dn = strtotime($n);
$ee = $dn - $dd;
$ff = ceil(abs($ee) / 86400);
echo "Email: " . $email . "\n\r";
echo "Date: " . $exp . "\n\r";
echo "DateDiff: " . $ff;
$mes = "";
if ($ff == 30)
{
$mes = "Bid specific commitee approval date expires in 1 month"; ( Whatever message you have to state in email notification)
}
if ($ff == 21)
{
$mes = "Bid specific commitee approval date expires in 21 days";
}
if ($ff == 14)
{
$mes = "Bid specific commitee approval date expires in 14 days";
}
if ($ff == 7)
{
$mes = "Bid specific commitee approval date expires in 7 Days";
}
if ($ff == 3)
{
$mes = "Bid specific commitee approval date expires in 3 Days";
}


if ($mes != "")
{
$to = $email;
$subject = "Procurement Report";
$message = "Dear Project Manager:" . "\r\n";
$message = "Programme Unique Number:" . $row[2] ."\r\n";
$message .= $mes;
$message = "Thank you. Kind regards, Admin."\r\n";
$headers = "From: [email protected]";
mail($to,$subject,$message,$headers);
}
else
{
echo "No Email";
}
}

mysql_close($db);
?>

Now you must create the cronjob in your server set the settings and point to the notifyemail.php script

Thanks again.

Do apologise, it was the wrong notifyingemail.php with the wrong table, this is the correct one:

CREATE TABLE IF NOT EXISTS `procurement_planning_above_r500` (
`procurement_planning_above_r500k_id` int(11) NOT NULL AUTO_INCREMENT,
`programme_name_as_per_budget` int(11) DEFAULT NULL,
`institutional_or_programme_unique_number` int(11) DEFAULT NULL,
`description_of_requirement` text,
`projected_cost` int(11) DEFAULT NULL,
`planned_method_of_procurement` int(11) DEFAULT NULL,
`budget_available` int(11) NOT NULL,
`current_closing_budget_balance` int(11) DEFAULT NULL,
`contract_started` date DEFAULT NULL,
`contract_terminated` date DEFAULT NULL,
`contract_duration` varchar(40) DEFAULT NULL,
`contract_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`project_manager_email` varchar(40) DEFAULT NULL,
`contract_type` int(11) DEFAULT NULL,
`bid_number` varchar(40) DEFAULT NULL,
`bid_spec_approval_date` date DEFAULT NULL,
`bid_publication_date` date DEFAULT NULL,
`bid_closure_date` date DEFAULT NULL,
`bid_evaluation_date` date DEFAULT NULL,
`bid_adjudication_date` date DEFAULT NULL,
`award_date` date DEFAULT NULL,
`contract_signoff_date` date DEFAULT NULL,
`service_delivery_date` date DEFAULT NULL,
PRIMARY KEY (`procurement_planning_above_r500k_id`),
KEY `programme_name_as_per_budget` (`programme_name_as_per_budget`),
KEY `institutional_or_programme_unique_number` (`institutional_or_programme_unique_number`),
KEY `planned_method_of_procurement` (`planned_method_of_procurement`),
KEY `budget_available` (`budget_available`),
KEY `project_manager` (`project_manager_email`),
KEY `contract_type` (`contract_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

Sure, here it is - it is connecting to the specific table so you must change your settings accordingly!

Call it notifyemail.php

<?php
$dbServer = "127.0.0.1";
$dbUsername = "username connect to db";
$dbPassword = "**********";
$dbDatabase = "Your dbname";
$db = mysql_connect($dbServer,$dbUsername,$dbPassword,$dbDatabase);
echo "Error: " . mysql_error() . "</br>";
$n = date("Y-m-d");
mysql_select_db($dbDatabase,$db);
$sql = "SELECT * FROM procurement_planning_above_r500";(table name)
$mq = mysql_query($sql,$db);
echo "Query Error: " . mysql_error() . "</br>";
while ($row = mysql_fetch_row($mq))
{
$email = $row[12]; ( count from 0 until your email adress column)
$subjectname = $row[0];
$exp = $row[15]; (the due date expire)
$dd = strtotime($exp);
$dn = strtotime($n);
$ee = $dn - $dd;
$ff = ceil(abs($ee) / 86400);
echo "Email: " . $email . "\n\r";
echo "Date: " . $exp . "\n\r";
echo "DateDiff: " . $ff;
$mes = "";
if ($ff == 30)
{
$mes = "Bid specific commitee approval date expires in 1 month"; ( Whatever message you have to state in email notification)
}
if ($ff == 21)
{
$mes = "Bid specific commitee approval date expires in 21 days";
}
if ($ff == 14)
{
$mes = "Bid specific commitee approval date expires in 14 days";
}
if ($ff == 7)
{
$mes = "Bid specific commitee approval date expires in 7 Days";
}
if ($ff == 3)
{
$mes = "Bid specific commitee approval date expires in 3 Days";
}


if ($mes != "")
{
$to = $email;
$subject = "Procurement Report";
$message = "Dear Project Manager:" . "\r\n";
$message = "Programme Unique Number:" . $row[2] ."\r\n";
$message .= $mes;
$message = "Thank you. Kind regards, Admin."\r\n";
$headers = "From: [email protected]";
mail($to,$subject,$message,$headers);
}
else
{
echo "No Email";
}
}

mysql_close($db);
?>

Now you must create the cronjob in your server set the settings and point to the notifyemail.php script

Thank you very much for that topic. It is very informative and useful for me. BTW. Hello to everyone I am new here and it is my first post.