Re: Sending out notification emails if contract expire

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
maramk
Veteran Member
Posts: 38
Joined: 2013-08-13 11:19

Re: Sending out notification emails if contract expire

Post by maramk » 2014-03-03 15:06

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

maramk
Veteran Member
Posts: 38
Joined: 2013-08-13 11:19

Re: Sending out notification emails if contract expire

Post by maramk » 2014-05-08 06:57

Dear All,

Do not worry, I did find the solution!

Thank you.

Kind regards,

Mara

albuchholz
Posts: 10
Joined: 2013-06-04 21:26

Re: Sending out notification emails if contract expire

Post by albuchholz » 2014-05-08 14:39

Great. Glad you found the solution.

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

maramk
Veteran Member
Posts: 38
Joined: 2013-08-13 11:19

Re: Sending out notification emails if contract expire

Post by maramk » 2014-05-08 14:57

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

albuchholz
Posts: 10
Joined: 2013-06-04 21:26

Re: Sending out notification emails if contract expire

Post by albuchholz » 2014-05-08 17:26

Thanks again.

maramk
Veteran Member
Posts: 38
Joined: 2013-08-13 11:19

Re: Sending out notification emails if contract expire

Post by maramk » 2014-05-09 05:46

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

SuperSwinka45
Posts: 1
Joined: 2014-06-30 13:43

Re:

Post by SuperSwinka45 » 2014-06-30 18:06

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.

Post Reply