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

	function camp_alkem_cumulativemail()
	{
		$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 bgcolor='#FFCC99'><th><font color='#RRGGBB'>Zone</font></th>
												<th><font color='#RRGGBB'>Region</font></th>
												<th><font color='#RRGGBB'>HeadQuarters</font></th>
												<th><font color='#RRGGBB'>Doctor Name</font></th>
												<th><font color='#RRGGBB'>Camp Type</font></th>												
												<th><font color='#RRGGBB'>Camp Date & Time</font></th>
												<th><font color='#RRGGBB'>Venue Address</font></th>
												<th><font color='#RRGGBB'>Phlebotomist Details</font></th>
										</tr>";
				foreach($this->data_grid as $k=>$rdata)
				{
					$zone=$rdata['zone'];
					$region=$rdata['region'];
					$headquarters=$rdata['headquarters'];
					$doctor_name=$rdata['doctor_name'];
						$test_type=$rdata['test_type'];
					
					$camp_date=$rdata['camp_date'];
					$venue_address=$rdata['venue_address'];
					$phlebotomist_name=$rdata['phle'];
					$phlebotomist_number=$rdata['phlebotomist_number'];
					$contact_no_of_ram=$rdata['contact_no_of_ram'];
					$name_of_asm=$rdata['name_of_asm'];
					$contact_no_of_asm=$rdata['contact_no_of_asm'];
					$email_id_of_asm=$rdata['email_id_of_asm'];
					$me_name=$rdata['me_name'];
					$contact_no_of_me=$rdata['contact_no_of_me'];
					$email_id_of_me=$rdata['email_id_of_me'];
							$table.="<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'>$test_type</td>
											<td align='center'>$camp_date</td>
											
											<td align='center'>$venue_address</td>
											<td align='center'>$phlebotomist_name</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. Urmila Kadam<br/>
											  - Clinical Research<br/>
											 02230840740 /02233143288</p>";}
			
				
				$table.=$format;
				
				return $table;
	}

	function camp_alkem_intimation()
	{
		$sql="SELECT DISTINCT b.headquarters AS headquarters,a.headquarters as hq_id
					FROM alkem_camps a
					INNER JOIN masters_alkem b ON (a.headquarters=b.hq_id)
					WHERE a.freeze=1 
					and date_format(camp_date,'%Y-%m-%d') = CURDATE() + INTERVAL 1 DAY ";
		$rec=$this->fetchAll($sql);
		foreach($rec as $k=>$row)
		{
			$this->to=array();
			$this->cc=array();
			$camp_id=$row['camp_id'];
		
			$headquarters=$row['headquarters'];
//			$to=$this->getFTM($camp_id);
			$sql="SELECT DISTINCT 
							a.camp_id,
							c.zone,
							c.region,
							c.headquarters,
								Case 
								when a.test_type = 1 then 'HbA1C' 
								when a.test_type = 2 then 'Lipid' 							
								END As test_type,
							a.name_of_asm,
							a.contact_no_of_asm,
							a.email_id_of_asm,
							a.me_name,
							a.contact_no_of_me,
							a.email_id_of_me,
							a.doctor_name,
							a.doctor_contact_number,
							a.doctor_email,
							date_format(a.camp_date,'%d-%M-%Y %h:%i %p') as camp_date,
							CONCAT(a.phlebotomist_name,'-',a.phlebotomist_number) as phle,
						
							a.venue_address
						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.pfreeze=1 
					
						and date_format(camp_date,'%Y-%m-%d') = CURDATE() + INTERVAL 1 DAY";
			$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 CUMULATIVE MAIL ";
							$bodyhtml="<p>Dear Team,</p>
													 <p>Please be informed that the below listed ALKEM camps have been scheduled for tomorrow.
</p>";			
							$new_body=$this->preparetable();
							$bodyhtml.=$new_body;
							array_push($this->cc,"himanshu.bhatia@metropolisindia.com");
						//	array_push($this->to,"snehal.bhosale@metropolisindia.com");
						}	
					

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

	
	

}

$lm=new camp_alkem_cumulativemail();
$lm->camp_alkem_intimation();

//$lm->camp_kartavya_intimation();
//$lm->camp_remainder();
//$lm->camp_last_remainder();
//$lm->camp_last_remainder1();
echo "End Ok...!";
?>