'Default',
'basePath' => dirname(__FILE__)
));
class sop_monthly_mail
{
var $mysqlconfig;
var $mysqldb;
var $data_grid;
var $from;
var $to;
var $cc;
var $camp_id;
var $filter;
var $adddetails;
function sop_monthly_mail()
{
$this->from="portaladmin@metropolis.net.in";
$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);
}
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' => 'ItDvlp@2015',
// 'ssl' => 'tls');
// $tr = new Zend_Mail_Transport_Smtp('192.168.23.66' , $config);
$config = array('auth' => 'login',
'username' => 'it.support@metropolis.net.in',
'password' => 'metro@2015',
'ssl' => 'tls');
$tr = new Zend_Mail_Transport_Smtp('192.168.23.22' , $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_monthly_rem()
{
$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,
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,
i.fname as rep3_fname,
i.lname as rep3_lname,
i.emailid as rep3_emailid,
e.total_cnt ,
f.read_cnt
FROM
intranet.hr_new_newuser a
left join intranet.mail_department_mast b on a.department=b.dept_id
inner join intranet.hr_new_reportinghead_uid g on a.uid = g.uid and g.level =2
inner join intranet.users c on g.reporting_id=c.uid
left join intranet.hr_new_reportinghead_uid h on a.uid = h.uid and h.level =3
left join intranet.users d on h.reporting_id=d.uid
left join intranet.hr_new_reportinghead_uid j on a.uid = j.uid and j.level =4
left join intranet.users i on j.reporting_id=i.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) >21
and (e.total_cnt !=f.read_cnt or f.read_cnt IS NULL) ; ";
$rec=$this->mysqldb->fetchAll($sql);
$subject=" SOP Online Module Clearance- Monthly 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'];
//reporting head 3
$head3_name=$val['rep3_fname'].' '.$val['rep3_lname'];
$head3_email=$val['rep3_emailid'];
$body="Dear $head3_name ";
}
$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'];
//reporting head 3
$head3_name=$val['rep3_fname'].' '.$val['rep3_lname'];
$head3_email=$val['rep3_emailid'];
$body.="
| $c |
$emp_name |
$emp_dept |
$desg |
$doj |
";
}var_dump("in each ".$val['emp_fname']);
$body.="
Several reminders haven been sent previously. 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 You,
Human Resources Team ";
// var_dump($body);
$this->to=array();
$this->cc=array();
array_push($this->cc,"it.support@metropolisindia.com");
array_push($this->cc,"lotika.baruah@metropolisindia.com");
array_push($this->cc,$head1_email);
array_push($this->cc,$head2_email);
array_push($this->to,$head3_email);
array_push($this->cc,$emp_emailid);
//array_push($this->to,"himanshu.bhatia@metropolisindia.com");
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 monthly_sopmailfreeze=1,monthly_maildate=now() WHERE uid='$uid';";
$this->execute($sql);
}
}
class sop_tech_monthly_mail
{
var $mysqlconfig;
var $mysqldb;
var $data_grid;
var $from;
var $to;
var $cc;
var $camp_id;
var $filter;
var $adddetails;
function sop_tech_monthly_mail()
{
$this->from="portaladmin@metropolis.net.in";
$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);
}
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' => 'ItDvlp@2015',
// 'ssl' => 'tls');
// $tr = new Zend_Mail_Transport_Smtp('192.168.23.66' , $config);
$config = array('auth' => 'login',
'username' => 'it.support@metropolis.net.in',
'password' => 'metro@2015',
'ssl' => 'tls');
$tr = new Zend_Mail_Transport_Smtp('192.168.23.22' , $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_monthly_rem()
{
$sql1="SELECT DISTINCT
i.uid as rep3_uid,
i.fname as rep3_fname,
i.lname as rep3_lname,
i.emailid as rep3_emailid,
e.total_cnt ,
f.read_cnt
FROM
intranet.hr_new_newuser a
left join hrpms.users userdetails on a.uid= userdetails.uid
left join intranet.mail_department_mast b on a.department=b.dept_id
inner join intranet.hr_new_reportinghead_uid g on a.uid = g.uid and g.level =2
inner join intranet.users c on g.reporting_id=c.uid
left join intranet.hr_new_reportinghead_uid h on a.uid = h.uid and h.level =3
left join intranet.users d on h.reporting_id=d.uid
left join intranet.hr_new_reportinghead_uid j on a.uid = j.uid and j.level =4
left join intranet.users i on j.reporting_id=i.uid
left join(select a.uid as users_uid , count(a.id) as total_cnt
from
hrpms.techusers_sop a
group by a.uid
) e on a.uid = e.users_uid
left join(select users_uid , count(sop_id) as read_cnt
from
hrpms.sop_new_read_status
group by users_uid
) f on a.uid = f.users_uid
WHERE second_mailfreeze=1
and third_mailfreeze=1
and fourth_mailfreeze=1
and DATEDIFF(CURDATE(), userdetails.sop_freeze_date) =35
and ( e.total_cnt IS NOT NULL and( e.total_cnt !=f.read_cnt or f.read_cnt IS NULL)) ;";
$rec1=$this->mysqldb->fetchAll($sql1);
foreach($rec1 as $row1)
{
$subject=" SOP Online Module Clearance- Monthly Reminder";
$body="Dear ".$row1["rep3_fname"]. " " . $row1["rep3_lname"] ." ";
$sql="SELECT
a.uid,
a.firstname as emp_fname,
a.lastname as emp_lname,
a.userid ,
loc.location_id,
userdetails.email as empemailid ,
a.department,
DATE_FORMAT(a.second_mail_date,'%d %b %y') AS sec_maildate,
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,
i.fname as rep3_fname,
i.lname as rep3_lname,
i.emailid as rep3_emailid,
e.total_cnt ,
f.read_cnt
FROM
intranet.hr_new_newuser a
left join hrpms.users userdetails on a.uid= userdetails.uid
left join intranet.new_custsup_locationusers loc on a.uid = loc.users_uid
left join intranet.mail_department_mast b on a.department=b.dept_id
inner join intranet.hr_new_reportinghead_uid g on a.uid = g.uid and g.level =2
inner join intranet.users c on g.reporting_id=c.uid
left join intranet.hr_new_reportinghead_uid h on a.uid = h.uid and h.level =3
left join intranet.users d on h.reporting_id=d.uid
left join intranet.hr_new_reportinghead_uid j on a.uid = j.uid and j.level =4
left join intranet.users i on j.reporting_id=i.uid
left join(select a.uid as users_uid , count(a.id) as total_cnt
from
hrpms.techusers_sop a
group by a.uid
) e on a.uid = e.users_uid
left join(select users_uid , count(sop_id) as read_cnt
from
hrpms.sop_new_read_status
group by users_uid
) f on a.uid = f.users_uid
WHERE second_mailfreeze=1
and third_mailfreeze=1
and fourth_mailfreeze=1
and DATEDIFF(CURDATE(), userdetails.sop_freeze_date) =35
and ( e.total_cnt IS NOT NULL and( e.total_cnt !=f.read_cnt or f.read_cnt IS NULL))
and i.uid = '".$row1["uid"]."' ;";
$rec=$this->mysqldb->fetchAll($sql);
//employee details
$body.="
The following members of your team are yet to go through the departmental SOPs:
| Sr.no |
Name |
Department |
Designation |
Doj |
";
$this->to=array();
$this->cc=array();
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'];
//reporting head 3
$head3_name=$val['rep3_fname'].' '.$val['rep3_lname'];
$head3_email=$val['rep3_emailid'];
$body.="
| $c |
$emp_name |
$emp_dept |
$desg |
$doj |
";
array_push($this->cc,"it.support@metropolisindia.com");
array_push($this->cc,$head1_email);
array_push($this->cc,$head2_email);
array_push($this->cc,$emp_emailid);
$location_id=$val["location_id"];
$hr_rep = $this->getHR_Rep($location_id);
foreach($hr_rep as $em)
{
$key= array_search($em, $this->cc); // $key = 2;
if($key===false)
{
array_push($this->cc,$em);
}
}
//$sql="UPDATE hr_new_newuser SET fifth_mailfreeze=1,fifth_mail_date=now() WHERE uid='$uid';";
//$this->execute($sql);
}
$body.="
Several reminders haven been sent previously. 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.
Thank you.
Human Resources Team ";
/*
array_push($this->cc,"it.support@metropolisindia.com");
array_push($this->cc,$head1_email);
array_push($this->cc,$head2_email);
array_push($this->to,$head3_email);
array_push($this->cc,$emp_emailid);
array_push($this->cc,"supriya.sawant@metropolisindia.com");
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 fifth_mailfreeze=1,fifth_mail_date=now() WHERE uid='$uid';";
$this->execute($sql);
*/
array_push($this->to,$head3_email);
$this->send_mail($subject,$this->from,$this->to,$this->cc,$body,'');
}
}
function prev_sop_monthly_rem()
{
$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,
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,
i.fname as rep3_fname,
i.lname as rep3_lname,
i.emailid as rep3_emailid,
e.total_cnt ,
f.read_cnt
FROM
intranet.hr_new_newuser a
left join intranet.mail_department_mast b on a.department=b.dept_id
inner join intranet.hr_new_reportinghead_uid g on a.uid = g.uid and g.level =2
inner join intranet.users c on g.reporting_id=c.uid
left join intranet.hr_new_reportinghead_uid h on a.uid = h.uid and h.level =3
left join intranet.users d on h.reporting_id=d.uid
left join intranet.hr_new_reportinghead_uid j on a.uid = j.uid and j.level =4
left join intranet.users i on j.reporting_id=i.uid
inner join(select a.uid as users_uid , count(a.id) as total_cnt
from
hrpms.techusers_sop a
group by a.uid
) e on a.uid = e.users_uid
left join(select users_uid , count(sop_id) as read_cnt
from
hrpms.sop_new_read_status
group by 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) >21
and (e.total_cnt !=f.read_cnt or f.read_cnt IS NULL) ; ";
$rec=$this->mysqldb->fetchAll($sql);
$subject=" SOP Online Module Clearance- Monthly 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'];
//reporting head 3
$head3_name=$val['rep3_fname'].' '.$val['rep3_lname'];
$head3_email=$val['rep3_emailid'];
$body="Dear $head3_name ";
}
$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'];
//reporting head 3
$head3_name=$val['rep3_fname'].' '.$val['rep3_lname'];
$head3_email=$val['rep3_emailid'];
$body.="
| $c |
$emp_name |
$emp_dept |
$desg |
$doj |
";
}var_dump("in each ".$val['emp_fname']);
$body.="
Several reminders haven been sent previously. 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 You,
Human Resources Team ";
// var_dump($body);
$this->to=array();
$this->cc=array();
array_push($this->cc,"it.support@metropolisindia.com");
array_push($this->cc,"lotika.baruah@metropolisindia.com");
array_push($this->cc,$head1_email);
array_push($this->cc,$head2_email);
array_push($this->to,$head3_email);
array_push($this->cc,$emp_emailid);
//array_push($this->to,"himanshu.bhatia@metropolisindia.com");
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 monthly_sopmailfreeze=1,monthly_maildate=now() WHERE uid='$uid';";
$this->execute($sql);
}
function getHR_Rep($location)
{
$cc = array();
if($location == 6)
{
array_push($cc,"jenell.santos@metropolisindia.com");
array_push($cc,"kunal.kamble@metropolisindia.com");
array_push($cc,"supriya.sawant@metropolisindia.com");
}
elseif ($location == 42)
{
array_push($cc,"harshada.sonavne@metropolisindia.com");
array_push($cc,"kunal.kamble@metropolisindia.com");
array_push($cc,"nand.micron@metropolisindia.com");
array_push($cc,"supriya.sawant@metropolisindia.com");
}
elseif($location == 5 || $location == 8 || $location == 50 || $location == 64 || $location == 38 || $location == 41 || $location == 40 || $location == 9 || $location == 37 || $location == 62)
{
array_push($cc,"mani.lm@metropolisindia.com");
array_push($cc,"magesh.lm@metropolisindia.com");
array_push($cc,"supriya.sawant@metropolisindia.com");
}
return $cc;
}
}
$lm= new sop_monthly_mail();
$lm->sop_monthly_rem();
$lm1= new sop_tech_monthly_mail();
$lm1->sop_monthly_rem();
?>