---Tables modified: tblTestSampling
alter table tblTestSampling add CollectedBy int, CollectedTime datetime, CollectedPlace int


-------------------------------------------------------------
create proc [dbo].[TestCollection_Insert]                        
@strEcounterId    nvarchar(50),                        
@strPatientId    nvarchar(50),                      
@intTestId    int,                        
@BillDetailsID    int,                        
@intmethodId    int,                        
@strSpecimen    nvarchar(50),                        
@strUserId    nvarchar(50),                        
@StrBillNo    nvarchar(50),                        
@strReferBy    nvarchar(50),                               
@intstatus int,                        
@ServiceCostId int,                        
@targetid int,  
@collectedPlace int  
as                        
begin                  
set @strPatientId=(select strPatientID from tblEncounter where ltrim(rtrim(strEncounterID))=ltrim(rtrim(@strEcounterId)))                  
                
 insert into tblTestSampling(strEcounterId,strPatientId,dtSampleDate,intTestId,BillDetailsID,intmethodId,strSpecimen,strUserId,StrBillNo,                        
 strSampleId,strReferBy,intstatus,ServiceCostId,targetid,resamplestatus,CollectedBy,CollectedTime,CollectedPlace)                        
 values(@strEcounterId,@strPatientId,null,@intTestId,@BillDetailsID,@intmethodId,@strSpecimen,@strUserId,@StrBillNo,                        
 null,@strReferBy,@intstatus,@ServiceCostId,@targetid,0,@strUserId,GETDATE(),@collectedPlace)                        
 select strSampleId  from tblTestSampling where intTestSampleId = scope_identity();                        
                        
IF not exists(select * from tbltestgroup tg inner join tbltarget tt on tt.ItemID=tg.intSCID WHERE tt.Billno=@StrBillNo and tt.BillDetailsID=@BillDetailsID                         
   and tg.intTestId not in(select intTestId from tblTestSampling where strBillNo=@StrBillNo and BillDetailsID=@BillDetailsID))                        
 BEGIN                        
   update tbltarget set Status=-2 where BillDetailsID=@BillDetailsID and  Billno=@StrBillNo and ID=@targetid                        
 END                        
END     
  

go
----------------------------------------------------------------------------------------------------------------------------
alter proc [dbo].[SpGetallPaitentinfo]     
 @patientID nvarchar(max)                          
 as                        
 begin                        
  select top 1 tcc.strname,tdb.strBed,tdb.BedID, tp.patientID,ISNULL(tp.UHID,'n/a') as UHID,           
  tu.SignatureImage,        
  tp.srtfname,tp.strlname,tp.strAddress,tm.Municipality, tp.strGender,                      
  te.ConsultDate,tdd.strDepartment,te.intConsultantID,te.strIsFollowUp,tp.dtmDOB,                      
  TP.strGender,(tm.Municipality +'-'+ tp.strAddress+','+td.DistrictName) as strFullAddress,                        
  te.policy_num,te.Claim_ID,te.consultdate,te.dtmFollowupdate,tp.intFileNumber,                      
  te.strencounterID,te.strencounterID as strEncounterID,                        
  tdd.strdepartment,tbm.strbillingmode,tpy.strpatienttype,te.strCurrentLocation,                      
  TP.strGender,(select dbo.GetpatientAge(te.strPatientID)) as age,                         
  (SELECT ROUND(Convert(decimal(10,2),                     
  DATEDIFF(DAY, CONVERT(date, dtmDOB),                       
  CONVERT(date, GETDATE()))*(12.0/365.25)),1))                    
  AS TotalMonths,       
 convert(varchar, tad.DOA,103) as DOA,      
 convert(varchar, getdate(),103) as DOD,      
  (datediff(day, tad.doa,Getdate())) as TotalDays,      
      
  tp.strEmail,isnull(tp.intmobile,'N/A') as 'intmobile',td.DistrictName,tp.strGuardian,tp.strRelation,                        
  tcd.strDoctorName               
  ,tcd.strProfile,tcd.strlicence,              
  (select dbo.GetpatientAge(te.strPatientID)) +'/'+ TP.strGender as dtmDOBB,                        
  tdd.strDepartment,tcd.strDoctorName,                  
  convert(nvarchar(max),convert(datetime, te.dtmDOR),100) as dtmDOR ,                  
    convert(nvarchar(max),convert(datetime, te.dtmDOD),100) as dtmDOD ,                 
 convert(nvarchar(max),convert(datetime, te.dtmDOR),111) as Regdate ,                 
  isnull(tad.Status,'Registered') Status,tdd.intDepartmentID as 'DepartmentId',            
  te.strencounterid                        
  from tblpaitentinfo tp inner join tblEncounter te on tp.patientID=te.strPatientID                        
  inner join tblDistrict td on td.ID=tp.strDistrict                        
  left join tblMunicipality tm on tm.mID= tp.[VDC/Municipality]                        
  left join tblConsaltantdoctr tcd on  tcd.ID=te.intConsultantID             
  left join tblDoctorMapping tdm on tdm.DoctorId=tcd.ID        
  left join tbluser tu on tu.intUserId=tdm.UserId        
  left join tblAdmission tad on tad.PatientID= tp.patientID and tad.strEncounterID=te.strEncounterID                        
  left join tblDepartment tdd on tdd.intDepartmentID=te.strCurrentLocation                        
  left join tblDepartmentbed tdb on tad.bednoID=tdb.BedID                        
  left join tblbillingmode tbm on tbm.intBillingModeId=te.intBillingModeID                        
  left join tblPatientType tpy on tpy.intPatientTypeId=te.strpatientType                        
  left join tblcorpo tcc on tcc.id=te.intrefer_corporateid                        
  where tp.patientID=@patientID order by te.id desc                        
end 
go
----------------------------------------------------------------------------------------------------------------
      
      
        
        
          
          
alter proc [dbo].[TestSamplingByPatientId]      --'3',2    
@strPaitentID nvarchar(max),            
@intTarget int            
as            
begin            
select temp.*,ts.*,IIF(ts.CollectedBy is null, 'Not Collected', 'Collected') as 'CollectedStatus' from            
(select * from tblTestSampling where strPatientId=@strPaitentID) as ts            
right outer join             
(            
SELECT tg.ID, ec.strPatientID as patientID, tg.BillDetailsID, tgp.intTarget, tgp.intMethodId, ec.strEncounterID, tgp.intTestId, bd.strBillNo,bd.bsentrydate as strBillingDatetime,             
ps.intspecId, ps.strSpecimen as Specimen,tgp.intTestGroupId, dt.ID AS doctorid, dt.strDoctorName, plt.strTestName, sc.strItemName, sc.intSCID as 'intservicecostid',tg.CreatedDate,pc.strCategory as 'category',pc.intPathoCatId as 'catid'          
FROM dbo.tblBillDetails AS bd             
LEFT  JOIN dbo.tblConsaltantdoctr AS dt ON dt.ID = bd.intrefer_drID             
INNER JOIN dbo.tbltarget AS tg ON bd.ID = tg.BillDetailsID            
INNER JOIN dbo.tblEncounter AS ec ON ec.strEncounterID = bd.strEncounterID             
INNER JOIN dbo.tblservicecost AS sc ON  tg.itemid  = sc.intSCID             
INNER JOIN dbo.tblTestGroup AS tgp ON tgp.intSCID = sc.intSCID             
INNER JOIN dbo.tblPathoLabTest AS plt ON plt.intTestId = tgp.intTestId             
inner join dbo.tblpathocategory pc on pc.intPathocatId = plt.intPathoCatId          
INNER JOIN dbo.tblPathoSpecimen AS ps ON plt.intspecId = ps.intspecId            
WHERE  (plt.Status = 'True') AND (tg.Status = '0' or tg.Status='-2')              
)            
temp on ts.intTestId =temp.intTestId and ts.StrBillNo=temp.strBillNo and ts.ServiceCostID=temp.intServiceCostID             
where TS.strSampleId is null              
and Temp.patientID=@strPaitentID and temp.intTarget=@intTarget  order by category          
end  
  
go

        
alter proc [dbo].[TestSamplingGetAllByPatientId]    --'3',2  
@strPaitentID nvarchar(max),        
@intTarget int        
as        
begin        
 select  TOP (100) PERCENT ts.intTestSampleId, ec.strPatientID as patientID, tg.BillDetailsID, tgp.intTarget, tgp.intMethodId, ec.strEncounterID, tgp.intTestId,        
 sc.strItemName, plt.strTestName, ts.strSampleId, CONVERT(nvarchar, ts.dtSampleDate, 107) AS dtSampleDate1, ps.strSpecimen, dt.strDoctorName,        
 bd.strBillNo,bd.bsentrydate as strBillingDatetime, tgp.intTestGroupId, bd.intServiceCostID,ts.intStatus as intstatus FROM  tblBillDetails AS bd         
 LEFT  JOIN dbo.tbltarget AS tg ON bd.ID = tg.BillDetailsID         
 INNER JOIN dbo.tblEncounter AS ec ON ec.strEncounterID = bd.strEncounterID         
 INNER JOIN dbo.tblservicecost AS sc ON bd.intServiceCostID = sc.intSCID         
 INNER JOIN dbo.tblTestGroup AS tgp ON tgp.intSCID = sc.intSCID         
 INNER JOIN dbo.tblPathoLabTest AS plt ON plt.intTestId = tgp.intTestId         
 INNER JOIN dbo.tblPathoSpecimen AS ps ON plt.intspecId = ps.intspecId         
 left  JOIN dbo.tblTestSampling AS ts ON  ts.intTestId = tgp.intTestId   and tg.ID=ts.targetid        
 LEFT  JOIN dbo.tblConsaltantdoctr AS dt ON dt.ID = ts.strReferBy OR dt.ID = bd.intrefer_drID        
 WHERE  (tg.Status <> - 1) AND (plt.Status = 'True') and tgp.intTarget=@intTarget and ts.strPatientId=@strPaitentID         
end        

go
------------------------------------------------------------------------------

alter proc [dbo].[TestSample_Insert]              
@strEcounterId    nvarchar(50),              
@strPatientId    nvarchar(50),              
@dtSampleDate    datetime,              
@intTestId    int,              
@BillDetailsID    int,              
@intmethodId    int    ,              
@strSpecimen    nvarchar(50),              
@strUserId    nvarchar(50),              
@StrBillNo    nvarchar(50),              
@strReferBy    nvarchar(50),              
@strSampleId nvarchar(50),              
@intstatus int,              
@ServiceCostId int,              
@targetid int              
as              
begin        
set @strPatientId=(select strPatientID from tblEncounter where ltrim(rtrim(strEncounterID))=ltrim(rtrim(@strEcounterId)))        
      
 if exists(select * from tblTestSampling where strEcounterId=@strEcounterId and strPatientId= @strPatientId and intTestId= @intTestId and BillDetailsID= @BillDetailsID and intmethodId=@intmethodId and strSpecimen= @strSpecimen and StrBillNo= @StrBillNo   
          
  and strReferBy= @strReferBy and ServiceCostId= @ServiceCostId and targetid= @targetid and intStatus=-2)  
 begin  
     update tblTestSampling set strSampleId=@strSampleId, dtSampleDate=getdate(), intStatus=0 where  
  strEcounterId=@strEcounterId and   
  strPatientId= @strPatientId and   
  intTestId= @intTestId and   
  BillDetailsID= @BillDetailsID and   
  intmethodId=@intmethodId and   
  strSpecimen= @strSpecimen and   
  StrBillNo= @StrBillNo             
  and strReferBy= @strReferBy and   
  ServiceCostId= @ServiceCostId and   
  targetid= @targetid and intStatus=-2    --minus two status is of collected sample (collected but not sampled)  
  
    
 end  
 else  
 begin   
  insert into tblTestSampling(strEcounterId,strPatientId,dtSampleDate,intTestId,BillDetailsID,intmethodId,strSpecimen,strUserId,StrBillNo,              
   strSampleId,strReferBy,intstatus,ServiceCostId,targetid,resamplestatus)              
   values(@strEcounterId,@strPatientId,getdate(),@intTestId,@BillDetailsID,@intmethodId,@strSpecimen,@strUserId,@StrBillNo,              
   @strSampleId,@strReferBy,@intstatus,@ServiceCostId,@targetid,0)              
   select strSampleId  from tblTestSampling where intTestSampleId = scope_identity();     
 end  
         
IF not exists(select * from tbltestgroup tg inner join tbltarget tt on tt.ItemID=tg.intSCID WHERE tt.Billno=@StrBillNo and tt.BillDetailsID=@BillDetailsID               
   and tg.intTestId not in(select intTestId from tblTestSampling where strBillNo=@StrBillNo and BillDetailsID=@BillDetailsID))              
 BEGIN              
   update tbltarget set Status=1 where BillDetailsID=@BillDetailsID and  Billno=@StrBillNo and ID=@targetid              
 END              
END 
go
------------------------------------------------------------------------------------------------------