<?
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_alkem_mails
{
	var $mysqlconfig;
	var $mysqldb;
	var $data_grid;
		var $from;

	function camp_alkem_mails()
	{
		$this->from="alkemcamps@metropoliscustomers.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 getFTM($camp_id)
	{
			$validator = new Zend_Validate_EmailAddress();
			$sql="SELECT DISTINCT email_id_of_me FROM alkem_camps WHERE camp_id='$camp_id';";
			$me_rec=$this->fetchAll($sql);
			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 alkem_camps WHERE camp_id='$camp_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;
				
	}
	function getZone($zone)
	{
			
			$validator = new Zend_Validate_EmailAddress();
		//	$sql="SELECT email_id FROM users_zone a,users b WHERE a.uid=b.uid and zone='$zone';";
			$sql="SELECT email_id FROM sponcerer_heads WHERE SPON_ID='5' and zone='$zone' and uid not in ('6','27')";
			$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;
				
	}
	
	
//	function getLab($data_grid)
//	{
//		$validator = new Zend_Validate_EmailAddress();
//		foreach($data_grid as $k=>$rdata)
//				{
//					$hq_id=$rdata['hq_id'];
//					$sql="SELECT a.uid,b.email_id FROM users_hq_id a, users b WHERE a.uid=b.uid and a.hq_id='$hq_id';";
//					$hq_id_users=$this->fetchAll($sql);
//					foreach($hq_id_users as $k=>$email)
//					{
//						if ($validator->isValid(trim($email['email_id']))) 
//						{
//							array_push($this->to,$email['email_id']);
//						}
//					}
//				}
//	}
	
	
		function getLab($headquarters)
		{
			
			$validator = new Zend_Validate_EmailAddress();
			$sql="SELECT a.email_id from alkem_sisterlabusers a
															inner join alkemsister_hq_id b on (a.uid=b.uid) WHERE b.hq_id='$headquarters'";			
			$lab_users=$this->fetchAll($sql);
			foreach($lab_users as $k=>$email)
				{
					if ($validator->isValid(trim($email['email_id']))) 
					{
						array_push($this->cc,$email['email_id']);
					}
				}
			return $this->cc;
				
		}

	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) 
		{
			//var_dump($to);
			//var_dump($cc);
			
			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 preparetable()
	{
		
		$table="<table border='1'><tr><th>Zone</th>
												<th>HeadQuarters</th>
												<th>Doctor Name</th>
												<th>Doctor Degree</th>												
												<th>Camp Date</th>
												<th>Sample pick up Time</th>
												<th>Venue Address</th>
												<th>FTM Name</th>
												<th>RAM Name</th>
																							
										</tr>";
				foreach($this->data_grid as $k=>$rdata)
				{
					$zone=$rdata['zone'];
					$headquarters=$rdata['headquarters'];
					$doctor_name=$rdata['doctor_name'];
					$doctor_degree=$rdata['doctor_degree'];
					$camp_date=$rdata['camp_date'];
					$sample_picup_time=$rdata['sample_picup_time'];
					$venue_address=$rdata['venue_address'];
					$contact_no_of_ftm=$rdata['contact_no_of_ftm'];
					$ftm_name=$rdata['ftm_name']."-".$contact_no_of_ftm;
					$contact_no_of_ram=$rdata['contact_no_of_ram'];
					$name_of_ram=$rdata['name_of_ram']."-".$contact_no_of_ram;
							$table.="<tr>
											<td align='center'>$zone</td>
											<td align='center'>$headquarters</td>
											<td align='center'>$doctor_name</td>
											<td align='center'>$doctor_degree</td>
											<td align='center'>$camp_date</td>
											<td align='center'>$sample_picup_time</td>
											<td align='center'>$venue_address</td>
											<td align='center'>$ftm_name</td>
											<td align='center'>$name_of_ram</td>
											</tr>";
				}
				$table.="</table>";
				$table.="<p>In case you do not receive it kindly get back to us on the below mentioned numbers.</p>";
				if($zone=='WEST'){$format="<p>Ms. Swati Shinde<br/>
											 Deputy Manager - Clinical Research<br/>
											 02230840738/ 02233143266</p>";}
				if($zone=='NORTH'){$format="<p>Ms. Sayli Rane<br/>
											  - Clinical Research<br/>
											 02230840737 /02233143255</p>";}
				if($zone=='EAST'){$format="<p>Ms. Kamakshi Desai<br/>
											  - Clinical Research<br/>
											 02230840740 /02233143288</p>";}
				$format.="<p>Ms. Mugdha Hule<br/>
											 Deputy Manager -Clinical Research<br/>
									 02230840739 /02233143277
									 </p><p><b>This is Auto Generated Email. Kindly Do Not reply</b></p>";
				
				$table.=$format;
				
				return $table;
	}

	function camp_alkem_intimation()
	{
		$sql="SELECT DISTINCT c.zone,b.headquarters AS headquarters,a.headquarters as hq_id
					FROM alkem_camps a
					INNER JOIN masters_alkem b ON (a.headquarters=b.hq_id)
					INNER JOIN alkem_headquarters c ON (b.headquarters=c.headquarters)
					WHERE a.freeze=1 
					AND a.camp_intimation_mail=0 
					and date(a.camp_date)= ADDDATE(CURDATE(),INTERVAL 3 DAY) 
					ORDER BY b.headquarters ";
		$rec=$this->fetchAll($sql);
		foreach($rec as $k=>$row)
		{
			$this->to=array();
			$this->cc=array();
			$camp_id=$row['camp_id'];
			$zone=$row['zone'];
			$headquarters=$row['headquarters'];
//			$to=$this->getFTM($camp_id);
			$sql="SELECT DISTINCT 
							a.camp_id,
							b.zone,
							b.hq  as headquarters,
							a.ftm_name,
							a.contact_no_of_ftm,
							a.name_of_ram,
							a.contact_no_of_ram,
							date_format(a.camp_date,'%d-%M-%Y') as camp_date,
							a.sample_picup_time,
							a.venue_address,
						concat(c.doc_fname,' ',c.doc_lname) as doctor_name,
							c.qualification as doctor_degree,
							c.doc_mob_no as doctor_contact_number
						FROM alkem_camps a 
						INNER JOIN hq_mast b ON (a.headquarters=b.hq_id) 
						left join doctor_master	c on a.doc_code=c.doc_code

						WHERE a.freeze=1 
						AND a.camp_intimation_mail=0 
						AND b.zone='$zone'
						and date(a.camp_date)= ADDDATE(CURDATE(),INTERVAL 1 DAY) 
						ORDER BY b.hq";
			$this->data_grid=$this->fetchAll($sql);
						if(count($this->data_grid)>0)
						{
							$zone=$this->data_grid[0]['zone'];
							$cc=$this->getZone($zone);
							$cc=$this->getLab($headquarters);

							$subject="New Camp Intimation - ".$zone;
							$bodyhtml="<p>Dear Abbott Team,</p>
													 <p>We have received intimation for the following Abbott Clinic programme. </p>";			
							$new_body=$this->preparetable();
							$bodyhtml.=$new_body;
							array_push($this->cc,"sneha.anaokar@metropolisindia.com");
						}	
						$this->send_mail($subject,$this->from,$this->to,$this->cc,$bodyhtml,$textbody);

		}		
	
	}
	

	function send_mail1($subject,$from,$to,$cc,$htmlbody,$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,"aquil.faruqui@metropolisindia.com");
//	$mail->addBcc("tina.makhija@kartavyahealtheon.com");
//	$mail->addBcc("manpreetkaur.gill@kartavyahealtheon.com");
//	$mail->addBcc("harshada.haldankar@kartavyahealtheon.com");
	$mail->setSubject((string)$subject);
	$mail->setBodyText((string)$textbody);
	$mail->setBodyHtml((string)$htmlbody);
	try {
	$mail->send($tr);   
	} catch (Exception $e) 
	{
	 echo 'Caught exception: ',  $e->getMessage(), "\n";
	 }	
	}
	

}

$lm=new camp_alkem_mails();
$lm->camp_alkem_intimation();
$lm->camp_logistic_reminder();
//$lm->camp_kartavya_intimation();
//$lm->camp_remainder();
//$lm->camp_last_remainder();
//$lm->camp_last_remainder1();
echo "End Ok...!";
?>