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

	function ct_accession_registered_mail()
	{
		$this->from="ctinfo@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);	
	}
	function fetchAll($sql)
	{
		//var_dump($sql);
		
		$rec=$this->mysqldb->fetchAll($sql);
		return $rec;
	}

//	function get_coordinator_users($id)
//	{
//		
//		
//		$sql="Select b.email from ct_accession_manual_details a,ct_Protocol_Study_Master b,ct_protocol_details c where c.name=b.id and a.project_name=c.protocol_id and a.id='$id';";
//				$rec= $this->fetchAll($sql);
//				$tomail=$rec[0]['email'];
//		
//					//array_push($this->to,$tomail);
//				array_push($this->to,"laxmi.bhor@metropolisindia.com");
//			
////	 echo $this->to;	
////   exit;	
//		return $this->to;
//	}
	
	function send_mail($subject,$from,$to,$cc,$htmlbody)
	{
	
		$config = array('auth' => 'login',
										'username' => 'ctinfo@metropolisindia.com',
										'password' => 'Cti@#2017',
										'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) 
		{
			
			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 sisterlab_mail()
	{
		$today = date("Y-m-d");
		$htmlbody.="Dear All,<br/>Following Sample are registered/pending for registration which were received.</br></br> 
								<table cellspacing=\"4\" cellpadding=\"2\" border=\"1\" align=\"left\">
								<tr><th>Accession_num</th>
								<th>SID/VID</th>
								<th>Dispatch datetime</th>
								<th>Location</th>
								<th>Collection date_time</th>
								<th>Report_date_time</th>
								<th>Subject Details</th>
								<th>Gender</th>
								<th>Age/DOB</th> 
								<th>Project Name</th>
								<th>Doctor Name</th>
								<th>Trf Received</th>
								<th>Test name</th>
								<th>Payment received</th>
								<th>Remark</th>
								<th>Comments</th>
								<th>Sample status</th>
								</tr>
							";     
							
			
				$sql="Select distinct
							   c.location as location
						from accession_details as a
						inner join accessioning b on a.accession_num=b.accession_num
					 	left join pan_indiamaster c on b.location=c.location_id and c.id=b.pickupby
						where a.udate like '$today%' and b.location=c.location_id
						union 
						Select distinct
						e.location as location
						from ct_accession_manual_details  a
						inner join ct_protocol_details b on a.accession_num = b.accession_num and a.project_name = b.protocol_id
						inner join ct_Protocol_Study_Master c on c.id = b.name
						inner join accessioning d on d.accession_num=a.accession_num
						left join pan_indiamaster e on d.location=e.location_id and e.id=d.pickupby
						where  a.udate like '$today%' and a.sample_status=0 and d.location=e.location_id ;";
						$rec= $this->fetchAll($sql);
					
				//var_dump($sql);	
						
			
			foreach($rec as $k=>$row)
			{
				$to=array();
				$cc=array();
				$rowbody="";
				//$toemail=$row['toemail'];
				$location=$row['location'];
					$sql="Select a.accession_num,
								 a.sid as sid,
								 a.sample_type,
								 a.subject_details,
								 a.investigator,
								 '' as remark,
								 '' as project_name,
								 '' as sample_status,
							   c.location as location,
                 c.pickup_by as pickupby,
							   c.toemail as toemail,
                 a.cdate,
                 a.age as age,
                 a.gender as gender,
                 b.dispatch_datetime as dispatch_datetime,
                 a.collection_date_time as collection_date_time,
                 a.ReportDateTime as ReportDateTime,
                 a.investigator as investigator,
                 '' as trf_received,
                 a.TNAME as test_name
						from accession_details as a
						inner join accessioning b on a.accession_num=b.accession_num
					 	left join pan_indiamaster c on b.location=c.location_id and c.id=b.pickupby
						where a.udate like '$today%' and b.location=c.location_id and c.location='$location'
						union 
						Select  a.accession_num,
									'' as sid,
									a.sample_type ,
									a.subject_details,
									a.investigator,
									a.remark,
									c.name  as project_name,
									case when a.sample_status=0 then 'Pending' end  as sample_status,
									e.location as location,
                  e.pickup_by as pickupby,
									e.toemail as toemail,
                  a.cdate,
                  a.age as age,
                  a.gender as gender,  
                  d.dispatch_datetime as dispatch_datetime,
                	a.collection_date_time as collection_date_time,
                	'' as ReportDateTime,
                	a.investigator as investigator,
                	a.trf_received as trf_received,
                	'' as test_name
						from ct_accession_manual_details  a
						inner join ct_protocol_details b on a.accession_num = b.accession_num and a.project_name = b.protocol_id
						inner join ct_Protocol_Study_Master c on c.id = b.name
						inner join accessioning d on d.accession_num=a.accession_num
						left join pan_indiamaster e on d.location=e.location_id and e.id=d.pickupby
						where  a.udate like '$today%' and a.sample_status=0 and d.location=e.location_id and e.location='$location' ;";
						$rec= $this->fetchAll($sql);
						foreach($rec as $k=>$row)
					{
						
						//$toemail=$row['toemail'];
						$accession_num=$row['accession_num'];
						$sid=$row['sid'];
						$dispatch_datetime=$row['dispatch_datetime'];
						$location=$row['location'];
						$pickup_by=$row['pickup_by'];
						$udate=$row['udate'];
					 	$investigator=$row['investigator'];
						$subject_details=$row['subject_details'];
						$subject_contact_no=$row['subject_contact_no'];
						$age=$row['age'];
						$gender=$row['gender'];
						$project_name=$row['project_name'];
						$trf_received=$row['trf_received'];
						$payment_received=$row['payment_received'];
						$test_name=$row['test_name'];
						$sample_status=$row['sample_status'];
						$collection_date_time=$row['collection_date_time'];
						$report_date_time=$row['ReportDateTime'];
						$rowbody.="<tr><td>$accession_num</td>
													<td>$sid</td>
													<td>$dispatch_datetime</td>
													<td>$location</td>
													<td>$collection_date_time</td>
													<td>$report_date_time</td>
													<td>$subject_details</td>
													<td>$gender</td>
													<td>$age</td>
													<td>$project_name</td>
													<td>$investigator</td>
													<td>$trf_received</td>
													<td>$test_name</td>
													<td>$payment_received</td>
													<td>$remark</td>
													<td>$cordinator_comments</td>
													<td>$sample_status</td>
													
													</tr>";
						
					}
					//var_dump(count($rec));
			$sql="Select distinct a.toemail,b.location  from pan_indiamaster a inner join  accessioning  b on b.location=a.location_id and  b.pickupby = a.id where b.location='$location' and b.pickup_by='$pickup_by';";
			$toemail=$this->fetchAll($sql);
			
  		
		  if($toemail)
			{
			 foreach($toemail as $keys=>$row)
			 {
				 	$toemail=$row["toemail"];
				 	
				 	$toemail=explode(',',$toemail);
				 	$location=$row["location"];
			 }
		 
				$validator = new Zend_Validate_EmailAddress();
				
					for($i=0; $i<count($toemail);$i++)
				{
					if ($validator->isValid(trim($toemail[$i]))) 
						{
							
									array_push($to,$toemail[$i]);
									
									array_push($to,"vijaya.sonawane@metropolisindia.com");
									array_push($to,"aquil.faruqui@metropolisindia.com");
							
						}
						
				}
						
			}	
			
			$sql1="Select a.ccemail1,b.location from pan_indiamaster a inner join  accessioning  b on b.location=a.location_id and  b.pickupby = a.id where b.location='$location' and b.pickup_by='$pickup_by'";
		$ccemail=$this->fetchAll($sql1);
			  
		if($ccemail)
		{
			 foreach($ccemail as $keys=>$row)
			 {
				 	$ccemail=$row["ccemail1"];
				 	$location=$row["location"];
				 	$ccemail=explode(',',$ccemail);
				}
				 
				$validator = new Zend_Validate_EmailAddress();
				
				for($i=0; $i<count($ccemail);$i++)
				{
						if ($validator->isValid(trim($ccemail[$i]))) 
								{
										array_push($cc,$ccemail[$i]);		
								}
				}
				
		}	
				
			
				$tailbody="</table>
					<br/><br/><br/><br/><br/>Please feel free to revert on below mentioned numbers in case of any query or information required.
					<br/>Regards,<br/>CT Accession Team <br/>022-50560708<br/>";
			
				$subject="Acknowledgement of Sample Registered";
					//var_dump($htmlbody);
				
			//array_push($cc,"mhlct4@metropolisindia.com");
			array_push($cc,"it.support@metropolisindia.com");
				
				$this->send_mail($subject,$this->from,$to,$cc,$htmlbody.$rowbody.$tailbody);
			}
	
	
	}
}

$lm=new ct_accession_registered_mail();
$lm->sisterlab_mail();


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