'Default',
'basePath' => dirname(__FILE__)
));
class sop_mails
{
var $mysqlconfig;
var $mysqldb;
var $data_grid;
var $from;
var $to;
var $cc;
var $camp_id;
var $filter;
var $adddetails;
function sop_mails()
{
$this->from="it.support@metropolisindia.com";
// $this->mysqlconfig = new Zend_Config(
// array(
// 'database' => array(
// 'adapter' => 'Pdo_Mysql',
// 'params' => array(
// 'host' => 'localhost',
// 'dbname' => 'intranet',
// 'username' => 'root',
// 'password' => 'metropolisitteam',
// )
// )
// )
// );
// $this->mysqldb = Zend_Db::factory($this->mysqlconfig->database);
// $this->mysqldb->setFetchMode(Zend_Db::FETCH_ASSOC);
$this->zmssqldb = DatabaseConfig::getMySqlDb();
}
function fetchAll($sql)
{
$rec=$this->mysqldb->fetchAll($sql);
return $rec;
}
function execute($sql)
{
$this->mysqldb->query($sql);
}
function send_mail($subject,$from,$to,$cc,$body,$textbody)
{
$config = array('auth' => 'login',
'username' => 'it.support@metropolisindia.com',
'password' => 'metropolis',
'ssl' => 'tls');
$tr = new Zend_Mail_Transport_Smtp('192.168.23.66' , $config);
Zend_Mail::setDefaultTransport($tr);
$mail = new Zend_Mail();
$mail->setFrom((string)$from);
foreach ($to as &$value)
{
$mail->addTo((string)$value);
}
if (isset($cc))
{
foreach ($cc as &$value)
{
$mail->addCc((string)$value);
}
// array_push($cc,"sneha.anaokar@metropolisindia.com");
}
$mail->setSubject((string)$subject);
//$mail->setBodyText((string)$textbody);
$mail->setBodyHtml((string)$body);
try
{
// var_dump($to);
// var_dump($subject,$from,$to,$cc,$body,$textbody);
// exit;
$mail->send($tr);
}
catch (Exception $e)
{
array_push($to,"it.support@metropolisindia.com");
foreach ($to as &$value)
{
$mail->addTo((string)$value);
}
$subject="Mail Not Sent Due to Incorrect Email";
$mail->send($tr);
echo 'Caught exception: ', $e->getMessage(), "\n";
}
}
function emp_email($uid)
{
// var_dump($uid);
$sql="SELECT emailid FROM users WHERE uid=$uid";
$rec=$this->mysqldb->fetchAll($sql);
$validator = new Zend_Validate_EmailAddress();
if ($validator->isValid(trim($rec[0]["emailid"])))
{
array_push($this->to,$rec[0]["emailid"]);
}
return $this->to;
}
function rep_head($uid)
{
// var_dump($uid);
$sql="SELECT a.emailid FROM users a,hr_new_newuser b WHERE a.uid=b.reporting_head1 and b.uid=$uid";
$rec=$this->mysqldb->fetchAll($sql);
$validator = new Zend_Validate_EmailAddress();
if ($validator->isValid(trim($rec[0]["emailid"])))
{
array_push($this->cc,$rec[0]["emailid"]);
var_dump($this->cc);
}
return $this->cc;
}
function sop_mail2()
{
$sql="SELECT
a.uid,
a.firstname as emp_fname,
a.lastname as emp_lname,
a.userid ,
a.department,
a.designation as emp_desg,
b.department as emp_dept,
c.fname as rep1_fname,
c.lname as rep1_lname,
c.emailid as rep1_emailid,
d.fname as rep2_fname,
d.lname as rep2_lname,
d.emailid as rep2_emailid,
e.total_cnt ,
f.read_cnt
FROM
hr_new_newuser a
left join mail_department_mast b on a.department=b.dept_id
left join users d on a.reporting_head2=d.uid
left join users c on a.reporting_head1=c.uid
inner join(select c.users_uid , count(b.sop_dept_id) as total_cnt
from
hrpms.sopnew_upload a inner join
hrpms.sop_department_mapping b on a.department_id = b.sop_dept_id
inner join intranet.new_custsup_departmentuser c on b.email_dept_id = c.dept_id
group by c.users_uid) e on a.uid = e.users_uid
left join(select c.users_uid , count(b.sop_dept_id) as read_cnt
from
hrpms.sopnew_upload a inner join
hrpms.sop_department_mapping b on a.department_id = b.sop_dept_id
inner join intranet.new_custsup_departmentuser c on b.email_dept_id = c.dept_id
inner join hrpms.sop_new_read_status d on a.id = d.sop_id and c.users_uid = d.users_uid
group by c.users_uid) f on a.uid = f.users_uid
WHERE `welcome_mail_freeze`=1 and DATEDIFF(CURDATE(), joindate) >7
and e.total_cnt !=f.read_cnt ; ";
$rec=$this->mysqldb->fetchAll($sql);
var_dump($rec);
// $ename=$rec[0]["empname"];
// $desg=$rec[0]["designation"];
// $uid=$rec[0]["uid"];
$subject="SOP Online Module Clearance- 1st Reminder";
foreach($rec as $k=>$val)
{
//employee details
$emp_name=$val["emp_fname"].' '.$val["emp_lname"];
$desg=$val["emp_desg"];
$emp_emailid=$val["userid"]."@metropolisindia.com";
$uid=$val["uid"];
//reporting head 1 details
$head1_name=$val["rep1_fname"].' '.$val["rep1_lname"];
$head1_email=$val["rep1_emailid"];
//reposrting head 2 details
$head2_name=$val["rep2_fname"].' '.$val["rep2_lname"];
$head2_email=$val["rep2_emailid"];
$body="Dear $head1_name,
As you are aware, every new recruit is required to read the departmental SOPs via � My Metropolis community�.
$emp_name who has recently joined your team as $desg is yet to go through the SOPs online and check the �Read & Understood� tick box accordingly.
Every new recruit is required to complete this in the first week of their joining. Please ensure that this is closed in the next 3 days to avoid any more reminders.
Please get in touch with your regional HR for any queries.
Thanks and Regards,
Human Resources Team ";
$this->to=array();
$this->cc=array();
array_push($this->cc,"it.support@metropolisindia.com");
array_push($this->to,$head1_email);
array_push($this->cc,$emp_emailid);
// array_push($this->to,"himanshu.bhatia@metropolisindia.com");
//var_dump($this->cc);
//var_dump($subject,$from,$this->to,$this->cc,$body,$textbody);
$this->send_mail($subject,$this->from,$this->to,$this->cc,$body,'');
$sql="UPDATE hr_new_newuser SET second_mailfreeze=1,second_mail_date=now() WHERE uid='$uid';";
$this->execute($sql);
}
}
function sop_mail3()
{
$sql="SELECT
a.uid,
a.firstname as emp_fname,
a.lastname as emp_lname,
a.userid ,
a.department,
DATE_FORMAT(a.second_mail_date,'%d %b %y') AS sec_maildate,
a.designation as emp_desg,
b.department as emp_dept,
c.fname as rep1_fname,
c.lname as rep1_lname,
c.emailid as rep1_emailid,
d.fname as rep2_fname,
d.lname as rep2_lname,
d.emailid as rep2_emailid,
e.total_cnt ,
f.read_cnt
FROM
hr_new_newuser a
left join mail_department_mast b on a.department=b.dept_id
left join users d on a.reporting_head2=d.uid
left join users c on a.reporting_head1=c.uid
inner join(select c.users_uid , count(b.sop_dept_id) as total_cnt
from
hrpms.sopnew_upload a inner join
hrpms.sop_department_mapping b on a.department_id = b.sop_dept_id
inner join intranet.new_custsup_departmentuser c on b.email_dept_id = c.dept_id
group by c.users_uid) e on a.uid = e.users_uid
left join(select c.users_uid , count(b.sop_dept_id) as read_cnt
from
hrpms.sopnew_upload a inner join
hrpms.sop_department_mapping b on a.department_id = b.sop_dept_id
inner join intranet.new_custsup_departmentuser c on b.email_dept_id = c.dept_id
inner join hrpms.sop_new_read_status d on a.id = d.sop_id and c.users_uid = d.users_uid
group by c.users_uid) f on a.uid = f.users_uid
WHERE welcome_mail_freeze=1
and second_mailfreeze=1
and DATEDIFF(CURDATE(), joindate) >14
and e.total_cnt !=f.read_cnt ; ";
$rec=$this->mysqldb->fetchAll($sql);
$subject="SOP Online Module Clearance- 2nd Reminder";
foreach($rec as $k=>$val)
{
//employee details
$emp_name=$val["emp_fname"].' '.$val["emp_lname"];
$desg=$val["emp_desg"];
$emp_emailid=$val["userid"]."@metropolisindia.com";
$uid=$val["uid"];
$sec_maildate=$val["sec_maildate"];
//reporting head 1 details
$head1_name=$val["rep1_fname"].' '.$val["rep1_lname"];
$head1_email=$val["rep1_emailid"];
//reposrting head 2 details
$head2_name=$val["rep2_fname"].' '.$val["rep2_lname"];
$head2_email=$val["rep2_emailid"];
var_dump($head2_name.$emp_emailid);
$body="Dear $head1_name ";
if($head2_name)
{
$body.="and $head2_name,"; }
else
{
$body.=",";
}$body.="
As you are aware, every new recruit is required to read the departmental SOPs via � My Metropolis community�.
$emp_name who has recently joined your team as $desg is yet to go through the SOPs online and check the �Read & Understood� tick box accordingly.
Every new recruit is required to complete this in the first week of their joining.The first reminder towards this was sent on $sec_maildate. Please ensure that this is closed in the next 3 days to avoid any more reminders.
Please get in touch with your regional HR for any queries.
Thanks and Regards,
Human Resources Team ";
$this->to=array();
$this->cc=array();
array_push($this->cc,"it.support@metropolisindia.com");
array_push($this->to,$head1_email);
array_push($this->to,$head2_email);
array_push($this->cc,$emp_emailid);
//array_push($this->to,"himanshu.bhatia@metropolisindia.com");
$this->send_mail($subject,$this->from,$this->to,$this->cc,$body,'');
$sql="UPDATE hr_new_newuser SET third_mailfreeze=1,third_mail_date=now() WHERE uid='$uid';";
$this->execute($sql);
}
}
function sop_mail4()
{
$sql="SELECT
a.uid,
a.firstname as emp_fname,
a.lastname as emp_lname,
a.userid ,
a.department,
a.designation as emp_desg,
b.department as emp_dept,
c.fname as rep1_fname,
c.lname as rep1_lname,
c.emailid as rep1_emailid,
d.fname as rep2_fname,
d.lname as rep2_lname,
d.emailid as rep2_emailid,
e.total_cnt ,
f.read_cnt
FROM
hr_new_newuser a
left join mail_department_mast b on a.department=b.dept_id
left join users d on a.reporting_head2=d.uid
left join users c on a.reporting_head1=c.uid
inner join(select c.users_uid , count(b.sop_dept_id) as total_cnt
from
hrpms.sopnew_upload a inner join
hrpms.sop_department_mapping b on a.department_id = b.sop_dept_id
inner join intranet.new_custsup_departmentuser c on b.email_dept_id = c.dept_id
group by c.users_uid) e on a.uid = e.users_uid
left join(select c.users_uid , count(b.sop_dept_id) as read_cnt
from
hrpms.sopnew_upload a inner join
hrpms.sop_department_mapping b on a.department_id = b.sop_dept_id
inner join intranet.new_custsup_departmentuser c on b.email_dept_id = c.dept_id
inner join hrpms.sop_new_read_status d on a.id = d.sop_id and c.users_uid = d.users_uid
group by c.users_uid) f on a.uid = f.users_uid
WHERE welcome_mail_freeze=1
and second_mailfreeze=1
and third_mailfreeze=1
and DATEDIFF(CURDATE(), joindate) >21
and e.total_cnt !=f.read_cnt ; ";
$rec=$this->mysqldb->fetchAll($sql);
var_dump($rec);
// $ename=$rec[0]["empname"];
// $desg=$rec[0]["designation"];
// $uid=$rec[0]["uid"];
$subject="SOP Online Module Clearance- 3rd Reminder";
foreach($rec as $k=>$val)
{
// var_dump("in each".$val["uid"]);
//employee details
$emp_name=$val["emp_fname"].' '.$val["emp_lname"];
$desg=$val["emp_desg"];
$emp_emailid=$val["userid"]."@metropolisindia.com";
$uid=$val["uid"];
//reporting head 1 details
$head1_name=$val["rep1_fname"].' '.$val["rep1_lname"];
$head1_email=$val["rep1_emailid"];
//reposrting head 2 details
$head2_name=$val["rep2_fname"].' '.$val["rep2_lname"];
$head2_email=$val["rep2_emailid"];
var_dump($head2_name.$emp_emailid);
$body="Dear $head1_name ";
if($head2_name)
{
$body.="and $head2_name,"; }
else
{
$body.=",";
}$body.="
$emp_name who has recently joined your team as $desg has still not gone through the departmental SOPs online via � My Metropolis Community�.
This is now the third reminder towards the same.
Please note that this is a Management mandate and compliance to this is compulsory. Requesting definite compliance to this reminder.
Please get in touch with your regional HR for any queries.
Thanks and Regards,
Human Resources Team ";
var_dump($body);
$this->to=array();
$this->cc=array();
array_push($this->cc,"it.support@metropolisindia.com");
array_push($this->to,$head1_email);
array_push($this->to,$head2_email);
array_push($this->cc,$emp_emailid);
// array_push($this->to,"himanshu.bhatia@metropolisindia.com");
$this->send_mail($subject,$this->from,$this->to,$this->cc,$body,'');
$sql="UPDATE hr_new_newuser SET fourth_mailfreeze=1,fourth_mail_date=now() WHERE uid='$uid';";
$this->execute($sql);
}
}
function sop_mail5()
{
$sql="SELECT
a.uid,
a.firstname as emp_fname,
a.lastname as emp_lname,
a.userid ,
a.department,
DATE_FORMAT(a.joindate,'%d %b %y') AS doj,
a.designation as emp_desg,
b.department as emp_dept,
c.fname as rep1_fname,
c.lname as rep1_lname,
c.emailid as rep1_emailid,
d.fname as rep2_fname,
d.lname as rep2_lname,
d.emailid as rep2_emailid,
e.total_cnt ,
f.read_cnt
FROM
hr_new_newuser a
left join mail_department_mast b on a.department=b.dept_id
left join users d on a.reporting_head2=d.uid
left join users c on a.reporting_head1=c.uid
inner join(select c.users_uid , count(b.sop_dept_id) as total_cnt
from
hrpms.sopnew_upload a inner join
hrpms.sop_department_mapping b on a.department_id = b.sop_dept_id
inner join intranet.new_custsup_departmentuser c on b.email_dept_id = c.dept_id
group by c.users_uid) e on a.uid = e.users_uid
left join(select c.users_uid , count(b.sop_dept_id) as read_cnt
from
hrpms.sopnew_upload a inner join
hrpms.sop_department_mapping b on a.department_id = b.sop_dept_id
inner join intranet.new_custsup_departmentuser c on b.email_dept_id = c.dept_id
inner join hrpms.sop_new_read_status d on a.id = d.sop_id and c.users_uid = d.users_uid
group by c.users_uid) f on a.uid = f.users_uid
WHERE `welcome_mail_freeze`=1
and second_mailfreeze=1
and third_mailfreeze=1
and fourth_mailfreeze=1
and DATEDIFF(CURDATE(), joindate) >28
and e.total_cnt !=f.read_cnt ; ";
$rec=$this->mysqldb->fetchAll($sql);
$subject="SOP Online Module Clearance- 4th Reminder";
// var_dump("in each".$val["uid"]);
//employee details
$c=0;
foreach($rec as $k=>$val)
{
$emp_name=$val['emp_fname'].' '.$val['emp_lname'];
$desg=$val['emp_desg'];
$emp_emailid=$val['userid'].'@metropolisindia.com';
$uid=$val['uid'];
$emp_dept=$val['emp_dept'];
$doj=$val['doj'];
//reporting head 1 details
$head1_name=$val['rep1_fname'].' '.$val['rep1_lname'];
$head1_email=$val['rep1_emailid'];
//reporting head 2 details
$head2_name=$val['rep2_fname'].' '.$val['rep2_lname'];
$head2_email=$val['rep2_emailid'];
$body="Dear $head1_name ";
if($head2_name)
{
$body.="and $head2_name,"; }
else
{
$body.=",";
}
}
$body.="
The following members of your team are yet to go through the departmental SOPs:
| Sr.no |
Name |
Department |
Designation |
Doj |
"; var_dump("in each ".$val['emp_fname']);
foreach($rec as $k=>$val)
{
$c++;
$emp_name=$val['emp_fname'].' '.$val['emp_lname'];
$desg=$val['emp_desg'];
$emp_emailid=$val['userid'].'@metropolisindia.com';
$uid=$val['uid'];
$emp_dept=$val['emp_dept'];
$doj=$val['doj'];
//reporting head 1 details
$head1_name=$val['rep1_fname'].' '.$val['rep1_lname'];
$head1_email=$val['rep1_emailid'];
//reporting head 2 details
$head2_name=$val['rep2_fname'].' '.$val['rep2_lname'];
$head2_email=$val['rep2_emailid'];
$body.="
| $c |
$emp_name |
$emp_dept |
$desg |
$doj |
";
}
$body.="
3 reminders have already been sent.This is an important mandate initiated by Top Management and compliance to this is compulsory.
Requesting you to kindly close the same in the next 2 days.
Please get in touch with your regional HR for any queries.
Thanks and Regards,
Human Resources Team ";
$this->to=array();
$this->cc=array();
array_push($this->cc,"it.support@metropolisindia.com");
array_push($this->to,$head1_email);
array_push($this->to,$head2_email);
array_push($this->cc,$emp_emailid);
$this->send_mail($subject,$this->from,$this->to,$this->cc,$body,'');
$sql="UPDATE hr_new_newuser SET fifth_mailfreeze=1,fifth_mail_date=now() WHERE uid='$uid';";
$this->execute($sql);
}
}
$lm=new sop_mails();
//$lm->sop_mail2();
//$lm->sop_mail3();
//$lm->sop_mail4();
//$lm->sop_mail5();
?>