<?
require_once 'Zend/Loader/Autoloader.php';
$autoloader = Zend_Loader_Autoloader::getInstance();
$autoloader = new Zend_Application_Module_Autoloader(array(
            'namespace' => 'Default',
            'basePath'  => dirname(__FILE__)
));
class camp_ipca_mails
{
	var $mysqlconfig;
	var $mysqldb;
	var $data_grid;
	var $from;

	function camp_ipca_mails()
	{
		$this->from="it.support@metropolisindia.com";	
			$this->mysqlconfig = new Zend_Config(
           array(
               'database' => array(
                   'adapter' => 'Pdo_Mysql',
                   'params'  => array(
                       'host'     => 'localhost',
                       'dbname'   => 'ctcamps',
                       'username' => 'root',
                       'password' => 'metropolisitteam',
                   )
               )
           )
       );
			$this->mysqldb = Zend_Db::factory($this->mysqlconfig->database);
			$this->mysqldb->setFetchMode(Zend_Db::FETCH_ASSOC);	
	}
	function fetchAll($sql)
	{
		//var_dump($sql);
		
		$rec=$this->mysqldb->fetchAll($sql);
		return $rec;
	}
	function execute($sql)
	{
	//	var_dump($sql);
		$this->mysqldb->query($sql);
	}
	
	function get_asm_users($hq_id)
	{
			$validator = new Zend_Validate_EmailAddress();
			$sql1="SELECT DISTINCT email_id_of_me FROM masters_ipca WHERE hq_id='hq_id'";
			$me_rec=$this->fetchAll($sql1);
			foreach($me_rec as $k=>$email)
			{
				if ($validator->isValid(trim($email['email_id_of_me']))) 
				{
						array_push($this->to,$email['email_id_of_me']);
				}
			}
			$sql="SELECT DISTINCT email_id_of_asm FROM masters_ipca WHERE hq_id='$hq_id'";
			$asm_rec=$this->fetchAll($sql);
			foreach($asm_rec as $k=>$email)
			{
				if ($validator->isValid(trim($email['email_id_of_asm']))) 
				{
					  array_push($this->to,$email['email_id_of_asm']);
				}
			}
			return $this->to;
//     echo $this->to;	
//     exit;
				
	}
	
	function get_sister_users($hq_id)
	{
		
		$validator = new Zend_Validate_EmailAddress();
		$sql="SELECT a.email_id from users a
													inner join ipcasister_headquarters b on (a.uid=b.uid) WHERE b.headquarters='$hq_id'";
		$lab_users=$this->fetchAll($sql);
		foreach($lab_users as $k=>$email)
			{
				if ($validator->isValid(trim($email['email_id']))) 
				{
					array_push($this->to,$email['email_id']);
				}
			}
//	 echo $this->to;	
//   exit;	
		return $this->to;
	}
	
	function get_head_users()
	{
			$validator = new Zend_Validate_EmailAddress();
			$sql="SELECT email_id FROM sponcerer_heads WHERE SPON_ID='7' and uid not in ('4','54','2')";
			$mum_users=$this->fetchAll($sql);
			foreach($mum_users as $k=>$email)
				{
					if ($validator->isValid(trim($email['email_id']))) 
					{
						array_push($this->cc,$email['email_id']);
					}
				}
			return $this->cc;
//	 echo $this->cc;	
//   exit;
				
	}

	function send_mail($subject,$from,$to,$cc,$htmlbody,$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);
		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);
			}
		}
		
		$mail->setSubject((string)$subject);
//		$mail->setBodyText((string)$textbody);
		$mail->setBodyHtml((string)$htmlbody);
		try 
		{
			
			$mail->send($tr);   
		} 
		catch (Exception $e) 
		{			
			array_push($to,"aquil.faruqui@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 adddetails()
	{
			$details="<p>Kindly revert on the below mentioned numbers incase of any queries or clarifications</p>";
					
			$details.="<p>Ms. Swati Shinde<br/>
									 Deputy Manager - Clinical Research<br/>
									 02230840738 / 02233143266</p>";
			$details.="<p>Ms. Kamakshi Desai<br/>
								  Deputy Manager - Clinical Research<br/>
								  02230840740 / 02233143288</p>";
			$details.="<p>Ms. Mugdha Hule<br/>
									 Deputy Manager - Clinical Research<br/>
							     02230840739 / 02233143277";
			$details.="<p>Mr. Siddhesh Chavan<br/>
												 Team Lead<br/>
												 02230840737 / 02233143255</p>
								 <p>Ms. Sayli Rane<br/>
									       Deputy Manager - Clinical Research<br/>
									       02230840737 / 02233143255</p>
											       
								 <p><b>This Is An Auto Generated Email. Kindly Do Not Reply</b></p>";
		//var_dump($bodyhtml);exit;
		return $details;
	}
	function prepare_table_hqwise($phlebo, $reason)
	{	
		$bodyhtml="<p>Dear All,</p>
								 <p>Please note the following IPCA camps are Scheduled for Tomorrow. </p>";	
		$bodyhtml.="<table border='1' style='font-size: 1em;'><font face='Arial'>
						 <tr bgcolor='#BDFF9D'>
						 <th>ZONE</th>
						 <th>REGION</th>
						 <th>HEAD QUARTERS</th>
						 <th>DOCTOR NAME</th>
						 <th>CAMP DATE & TIME</th>
						 <th>INTIMATION DATE & TIME</th>
						 <th>INTIMATION DAYS</th>
						 <th>CAMP ADDRESS</th>";
		if($phlebo==1){$bodyhtml.="<th>PHLEBOTOMIST NAME & NUMBER</th>";}
		if($reason==1){$bodyhtml.="<th>STATUS</th>";}      // AND REASON FOR CHANGE IN SCHEDULE
	  $bodyhtml.="<th >TOTAL PATIENTS</th>
						 <th>ME NAME & NUMBER</th>
						 <th>ASM NAME & NUMBER</th>
						 
						 </tr>";
//			$sql="
//						SELECT 
//						c.zone,
//						c.region,
//						c.headquarters,
//						a.doctor_name,
//						
//						date_format(a.camp_date,'%d-%b-%Y %h:%i %p') as camp_date,
//						date_format(a.camp_intimation,'%d-%b-%Y %h:%i %p') as intimation_date,
//						DATEDIFF(a.camp_date,a.camp_intimation) as intimation_days,
//						a.venue_address as venue_address,
//						a.no_of_patients,
//						CONCAT(a.phlebotomist_name,'-','0',SUBSTRING(a.phlebotomist_number, -10)) as phle,
//						a.comments,
//						CONCAT(a.me_name,'-','0',SUBSTRING(a.contact_no_of_me, -10)) as pa,
//						CONCAT(a.name_of_asm,'-','0',SUBSTRING(a.contact_no_of_asm, -10)) as asm
//						FROM ipca_camps a
//						INNER JOIN masters_ipca b ON (a.headquarters=b.id)
//						INNER JOIN ipca_headquarters c ON (b.hq_id=c.hq_id)
//							
//						WHERE date(a.camp_date)= ADDDATE(CURDATE(),INTERVAL 1 DAY) 
//						ORDER BY c.headquarters  ";


	$sql=" SELECT 
						c.zone,
						c.region,
						c.headquarters,
						a.doctor_name,
						
						date_format(a.camp_date,'%d-%b-%Y %h:%i %p') as camp_date,
						date_format(a.camp_intimation,'%d-%b-%Y %h:%i %p') as intimation_date,
						DATEDIFF(a.camp_date,a.camp_intimation) as intimation_days,
						a.venue_address as venue_address,
						a.no_of_patients,
						CONCAT(a.phlebotomist_name,'-','0',SUBSTRING(a.phlebotomist_number, -10)) as phle,
							Case 
											when a.camp_status = 0 then 'SCHEDULED' 
											when a.camp_status = 1 then  CONCAT('CANCELED',' - ',a.comments) 
											when a.camp_status = 2 then  CONCAT('POSTPONED',' - ',a.comments) 
											when a.camp_status = 3 then  CONCAT('PREPONED',' - ',a.comments) 
							END As status,

						CONCAT(a.me_name,'-','0',SUBSTRING(a.contact_no_of_me, -10)) as pa,
						CONCAT(a.name_of_asm,'-','0',SUBSTRING(a.contact_no_of_asm, -10)) as asm
					FROM ipca_camps a
					INNER JOIN masters_ipca b ON (a.headquarters=b.id)
					INNER JOIN ipca_headquarters c ON (b.hq_id=c.hq_id)
						
					WHERE date(a.camp_date)= ADDDATE(CURDATE(),INTERVAL 1 DAY) 
					and a.camp_status not in ('1','4','5')
					ORDER BY c.headquarters     ";
									
				$newrec=$this->fetchAll($sql);
				
				if($newrec)
				{
					
				foreach($newrec as $m=>$data)
				{
					
					$zone=$data['zone'];
					$region=$data['region'];
					$headquarters=$data['headquarters'];
					$doctor_name=$data['doctor_name'];
					$camp_date=$data['camp_date'];
					$intimation_date=$data['intimation_date'];
					$intimation_days=$data['intimation_days'];
					$venue_address=$data['venue_address'];
					$no_of_patients=$data['no_of_patients'];
					if($phlebo==1){$phle=$data['phle'];}
					if($reason==1){$comments=$data['status'];}
					$pa=$data['pa'];
					$asm=$data['asm'];
					$bodyhtml.="<tr>
								<td align='center'>$zone</td>
								<td align='center'>$region</td>
								<td align='center'>$headquarters</td>
								<td align='center'>$doctor_name</td>
								<td align='center'>$camp_date</td>
								<td align='center'>$intimation_date</td>
								<td align='center'>$intimation_days</td>
								<td align='center'>$venue_address</td>
								";
				if($phlebo==1){$bodyhtml.="<td align='center'>$phle</td>";}
				if($reason==1){$bodyhtml.="<td align='center'>$comments</td>";}
				$bodyhtml.="<td align='center'>$no_of_patients</td>			
								<td align='center'>$pa</td>
								<td align='center'>$asm</td>
								</tr>";
				}
				$bodyhtml.="</font></table>";
			}
			else
			{
				$bodyhtml="Dear All,<br>No camps are scheduled for tomorrow.";
	
			}
			
									
		return $bodyhtml;
	}
	
	function camp_ipca_cumulative()
	{
		$sql="SELECT DISTINCT c.zone,c.headquarters AS headquarters,b.hq_id as hq_id
					FROM ipca_camps a
					INNER JOIN masters_ipca b ON (a.headquarters=b.id)
					INNER JOIN ipca_headquarters c ON (b.hq_id=c.hq_id)
					WHERE a.freeze=1 
					and date(a.camp_date)= ADDDATE(CURDATE(),INTERVAL 1 DAY) 
					ORDER BY c.headquarters ";
		
		$rec=$this->fetchAll($sql);

			$this->to=array();
			$this->cc=array();

			$subject="Reminder of IPCA Camp for Tommorrow";
			//added by himanshu.
//			$bodyhtml="<p>Dear All,</p>
					//			 <p>Please note the following IPCA camps are Scheduled for Tomorrow. </p>";			
			$campdtls.=$this->prepare_table_hqwise(1,1);
			$details.=$this->adddetails();
				$bodyhtml.=$campdtls;
				$bodyhtml.=$details;   		
				$this->get_head_users();
		array_push($this->to,"supratimgupta@lupinpharma.com","spratimgupta@gmail.com");
				

		//	array_push($this->cc,"himanshu.bhatia@metropolisindia.com");
		array_push($this->cc,"sayli.rane@metropolisindia.com","siddhesh.chavan@metropolisindia.com");
				
//	array_push($this->cc,"it.support@metropolisindia.com");

			$this->send_mail($subject,$this->from,$this->to,$this->cc,$bodyhtml,$textbody);
	
	}
	
}

$lm=new camp_ipca_mails();
$lm->camp_ipca_cumulative();

//echo "End Ok...!";
?>
//