Processing Client : BHARAT PETROLEUM CORPORATION LIMITED : 67
select  distinct
			 								 PV.VisitNumber,
			 								 Convert(CHAR(10), PV.VISITDATE , 103)  + ' ' + Convert(CHAR(5), PV.VISITDATE , 114)  as VISITDATE, 
			 								 P.Name AS PATIENTNAME,
			 								 Master.InvestigatorName
			 								 
			FROM Patient P WITH (NOLOCK)
			INNER JOIN PatientVisit PV WITH (NOLOCK) ON (P.PatientID = PV.PatientID and P.OrganizationID = PV.OrganizationID ) 
			INNER JOIN VisitClientMapping VCM WITH (NOLOCK) ON (VCM.VisitID = PV.PatientVisitId and VCM.IsActive is null and VCM.OrgID = PV.OrganizationID)
			INNER JOIN ClientMaster CM WITH (NOLOCK) ON (CM.ClientID = VCM.ClientID and Cm.OrgID = VCM.OrgID) 
			INNER JOIN [MyDbr].dbo.CTAcknowledgementMail_Dixit Master WITH (NOLOCK) ON (CM.ClientID = Master.ClientID and Cm.OrgID = Master.OrgID) 
			where 
			    CM.OrgID = '67'
			--AND Master.STUDY_NAME = 'BHARAT PETROLEUM CORPORATION LIMITED'
			AND cast(PV.VisitDate as Date )= Cast(getdate() -1 as date ); string(62) "BHARAT PETROLEUM CORPORATION LIMITED AcknowledgementMailSend 
"
object(Zend_Mail_Transport_Smtp)#16 (15) {
  ["EOL"]=>
  string(1) "
"
  ["_host":protected]=>
  string(13) "192.168.23.66"
  ["_port":protected]=>
  NULL
  ["_name":protected]=>
  string(9) "localhost"
  ["_auth":protected]=>
  string(5) "login"
  ["_config":protected]=>
  array(4) {
    ["auth"]=>
    string(5) "login"
    ["username"]=>
    string(30) "it.support@metropolisindia.com"
    ["password"]=>
    string(11) "ItDvlp@2015"
    ["ssl"]=>
    string(3) "tls"
  }
  ["_connection":protected]=>
  NULL
  ["body"]=>
  string(0) ""
  ["boundary"]=>
  string(0) ""
  ["header"]=>
  string(0) ""
  ["_headers":protected]=>
  array(0) {
  }
  ["_isMultipart":protected]=>
  bool(false)
  ["_mail":protected]=>
  bool(false)
  ["_parts":protected]=>
  array(0) {
  }
  ["recipients"]=>
  string(0) ""
}
select  distinct
			 								 PV.VisitNumber,
			 								 Convert(CHAR(10), PV.VISITDATE , 103)  + ' ' + Convert(CHAR(5), PV.VISITDATE , 114)  as VISITDATE, 
			 								 P.Name AS PATIENTNAME,
			 								 Convert(CHAR(10), N_P.CompletionTime , 103)  + ' ' + Convert(CHAR(5), N_P.CompletionTime , 114) as ReportReleaseDate,
			 								 Convert(CHAR(10), N_E.CompletionTime , 103)  + ' ' + Convert(CHAR(5), N_E.CompletionTime , 114) as EmailReleaseDate,
			 								 Master.InvestigatorName 
			 								 
			FROM Patient P WITH (NOLOCK)
			INNER JOIN PatientVisit PV WITH (NOLOCK) ON (P.PatientID = PV.PatientID and P.OrganizationID = PV.OrganizationID ) 
			INNER JOIN VisitClientMapping VCM WITH (NOLOCK) ON (VCM.VisitID = PV.PatientVisitId and VCM.IsActive is null and VCM.OrgID = PV.OrganizationID)
			INNER JOIN ClientMaster CM WITH (NOLOCK) ON (CM.ClientID = VCM.ClientID and Cm.OrgID = VCM.OrgID)
			INNER JOIN [Notifications] N_P With(nolock)   ON  N_P.OrgID = PV.OrganizationID AND PV.PatientVisitId = N_P.IdentityID
			Left JOIN [Notifications] N_E With(nolock)   ON  N_E.OrgID = PV.OrganizationID AND PV.PatientVisitId = N_E.IdentityID
			INNER JOIN [MyDbr].dbo.CTAcknowledgementMail_Dixit Master WITH (NOLOCK) ON (CM.ClientID = Master.ClientID and Cm.OrgID = Master.OrgID) 
			where 
			    CM.OrgID = '67'
			AND N_P.ActionType = 'PDF' 
			AND N_P.Category = 'Report'	
			AND N_P.Status = 'Completed' 
			AND N_E.ActionType = 'Email' 
			AND N_E.Category = 'Report'	
			AND N_E.Status = 'Completed' 
			--AND Master.STUDY_NAME = 'BHARAT PETROLEUM CORPORATION LIMITED'
			 AND cast(PV.VisitDate as Date )= Cast(getdate()-2 as date)
			--AND cast(N_P.CompletionTime as Date )= Cast(getdate() as date); string(29) "sukhadhans@bharatpetroleum.in"
string(33) "vandanavshinde@bharatpetroleum.in"
SELECT  DISTINCT    
							PV.VisitNumber,
							P.Name AS [SUBJECT DETAILS],
							P.Age , 
							P.Sex,
							Master.InvestigatorName, 
							Convert(CHAR(10), SampleReceiptDateTime , 103)  + ' ' + Convert(CHAR(5), SampleReceiptDateTime , 114)  as [Collection Date and Time],
							Convert(CHAR(10), PV.VISITDATE , 103)  + ' ' + Convert(CHAR(5), PV.VISITDATE , 114)  as [Registration Date and Time],
							Convert(CHAR(10), AuthorisationDateTime , 103)  + ' ' + Convert(CHAR(5), AuthorisationDateTime , 114)  as [AuthorisationDateTime],
							Convert(CHAR(10), EmailDateTime , 103)  + ' ' + Convert(CHAR(5), EmailDateTime , 114)  as [AUTO E-MAIL DATE AND TIME] 
							 FROM [LIMS_LIVE].[DBO].OrderedInvestigations ORD (nolock)
							 Inner Join [LIMS_LIVE].[DBO].PatientInvSampleMapping PISM with (NOLOCK)
							                   on PISM.VIsitID = ORD.VisitID
							                   and PISM.Orgid = ORD.OrgID  
							                   and PISM.Type = ORD.Type
											           and PISM.ID = ORD.ID
							 INNER JOIN [LIMS_LIVE].[DBO].[PatientVisit] PV (NOLOCK) ON    PV.[PatientVisitID] = ORD.VisitID
							 And  PV.OrganizationID = ORD.OrgID 
							 INNER JOIN [LIMS_LIVE].[dbo].[Patient] P (NOLOCK) ON 
							       P.[PatientID] = PV.PatientID
							       AND P.[OrganizationID] = PV.OrganizationID 
							 INNER join [LIMS_LIVE].[DBO].VisitClientMapping VCM With (nolock) 
												on VCM.VisitID = PV.PatientVisitId 
												and VCM.IsActive is null 
												and VCM.OrgID = PV.OrganizationID
							 inner join [LIMS_LIVE].[DBO].ClientMaster Cm With (nolock) 
												on CM.ClientID = VCM.ClientID 
												AND  CM.OrgID = PV.OrganizationID        
							 INNER JOIN [MyDbr].dbo.CTAcknowledgementMail_Dixit Master WITH (NOLOCK) ON (CM.ClientID = Master.ClientID and Cm.OrgID = Master.OrgID) 
							 OUTER APPLY ( SELECT Top 1 CollectedDateTime SampleReceiptDateTime FROM [LIMS_LIVE].[DBO].[PatientInvSample] (NOLOCK) PIS
							                 WHERE PIS.OrgID = PISM.OrgID  
							                  AND  PIS.PatientVisitID = PISM.VisitID ) SampleReceipt
							 OUTER APPLY ( SELECT Top 1 ApprovedAt AuthorisationDateTime  
							 FROM [LIMS_LIVE].[dbo].[PatientInvestigation] PI (NOLOCK)
							                 WHERE PI.OrgID = ORD.OrgID  
							                  AND  PI.PatientVisitID = ORD.VisitID
							 			           Order By ValidatedAt DESC) PI
							 
							 OUTER APPLY ( SELECT Top 1 N.CompletionTime EmailDateTime
							 FROM [LIMS_LIVE].[dbo].[Notifications] N (NOLOCK) 
							          WHERE n.OrgID = pv.OrganizationID  
							                AND  N.IdentityID = PV.PatientVisitID
							                ANd N.[ActionType] = 'Email'
							 								AND N.[Category] = 'Report'
							 AND N.[Status] = 'Completed'
							 AND N.CreatedAt >= PI.AuthorisationDateTime
							 			           Order By N.CreatedAt ASC) Email		
									           
							 WHERE  cast(PV.VISITDATE as Date )>= Cast(getdate()-8 as date)
							 AND cast(PV.VISITDATE as Date )<= Cast(getdate()-1 as date)
							 and PV.OrganizationID = '67'
							 AND Master.STUDY_NAME = 'BHARAT PETROLEUM CORPORATION LIMITED'
							 AND cm.ClientName NOT LIKE '%DUMMY%' ;