-- Step 1: Enable Ole Automation Procedures
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

-- Step 2: Write Text File
DECLARE @OLE INT
DECLARE @FileID INT
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, 'C:\Dragos\projects\openIMIS AI\data\nepal\FHIR\NepalPatient.json', 2, 1

-- starting the json
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, '{ "entry": ['

DECLARE @InsureeID INT;
DECLARE @RunningTotal BIGINT = 0;

DECLARE @JSON nvarchar(MAX);
DECLARE @JSON1 nvarchar(MAX);
DECLARE @JSON2 nvarchar(MAX);
 
DECLARE @InsureeUUID varchar(100)
DECLARE @InsureeLastName varchar(100)
DECLARE @CurrentAddress varchar(100)
DECLARE @GeoLocation varchar(100)
DECLARE @DOB varchar(100)
DECLARE @IsHead INT
DECLARE @ValidityFrom DATETIME
DECLARE @LocationUUID varchar(100)
DECLARE @Education varchar(2)
DECLARE @Gender varchar(2)
DECLARE @Profession varchar(2)
DECLARE @Poverty INT
DECLARE @Relationship varchar(2)
DECLARE @HFUUID varchar(100)
DECLARE @CHFID varchar(20)
DECLARE @Passport varchar(100)
DECLARE @HeadUUID varchar(100)
DECLARE @LastName varchar(100)
DECLARE @OtherNames varchar(100)
DECLARE @Photo varchar(500)
DECLARE @PhotoDate varchar(30)
DECLARE @Phone varchar(100)
DECLARE @Email varchar(100)

DECLARE CUR_TEST CURSOR FAST_FORWARD FOR
    SELECT I.InsureeUUID, I.CurrentAddress, I.GeoLocation, I.DOB, I.IsHead, I.ValidityFrom, I.Education, I.Profession, I.Gender, I.CHFID, I.passport, I.Relationship
	, I.LastName, I.OtherNames, I.PhotoDate, I.Phone, I.Email
	, REPLACE(P.PhotoFolder + P.PhotoFileName, '\', '\\')
	, F.Poverty
	, H.InsureeUUID
	, L.LocationUUID
	, HF.HfUUID
    FROM   tblInsuree I
	LEFT JOIN tblPhotos P ON I.PhotoID = P.PhotoID
	LEFT JOIN tblFamilies F ON I.FamilyID=F.FamilyID
	LEFT JOIN tblInsuree H ON F.InsureeID=H.InsureeID
	LEFT JOIN tblLocations L ON F.LocationId=L.LocationId
	LEFT JOIN tblHF HF ON I.HFID = HF.HfID
	WHERE I.ValidityTo IS NULL
    ORDER BY I.InsureeID;

OPEN CUR_TEST
FETCH NEXT FROM CUR_TEST INTO @InsureeUUID, @CurrentAddress, @GeoLocation, @DOB, @IsHead, @ValidityFrom, @Education, @Profession, @Gender, @CHFID, @Passport, @Relationship, @LastName, @OtherNames, 
	@PhotoDate, @Phone, @Email, @Photo, @Poverty, @HeadUUID, @LocationUUID, @HFUUID
 
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @JSON1 = '{"fullUrl": "http://127.0.0.1:8000/api_fhir_r4/Patient/'+ @InsureeUUID+ '",'+
            '"resource": {"resourceType": "Patient", "address": [{"text": "'+@CurrentAddress+'","type": "physical","use": "temp"},{"text": "'+@GeoLocation+'","type": "gps","use": "biling"}],'+
            '"birthDate": "'+@DOB+'","extension": [{"url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/960069653/isHead","valueBoolean": '+(CASE WHEN @IsHead=1 THEN 'true' ELSE 'false' END)+'},'+
				'{"url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/960331779/registrationDate","valueDateTime": "'+FORMAT(@ValidityFrom, 'yyyy-MM-ddTHH:mm:ss.fff00', 'en-GB')+'"},'+
                '{"url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/960495619/locationCode","valueReference": {"reference": "Location/'+@LocationUUID+'"}},'+ 
				CASE WHEN @Education is not NULL THEN '{"url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/960331788/educationCode","valueCoding": {"code": "'+@Education+'","display": "'+@Education+'"}},' ELSE '' END +
                CASE WHEN @Profession is not NULL THEN '{"url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/960135203/professionCode","valueCoding": {"code": "'+@Profession+'","display": "'+@Profession+'"}},' ELSE '' END +
                CASE WHEN @Poverty is not NULL THEN '{"url": "https://openimis.atlassian.net/wiki/spaces/OP/pages/1556643849/povertyStatus","valueBoolean": '+(CASE WHEN @Poverty=1 THEN 'true' ELSE 'false' END)+'}' ELSE ''END +
            '],"gender": "'+@Gender+'",' + CASE WHEN @HFUUID is not NULL THEN '"generalPractitioner": [{"reference": "HealthcareService/'+@HFUUID+'"}],' ELSE '' END +
			'"id": "' + @InsureeUUID + '",';

	SET @JSON2 = '"identifier": [{"type": {"coding": [{"code": "UUID","system": "https://hl7.org/fhir/valueset-identifier-type.html"}]},"use": "usual","value": "' + @InsureeUUID + '"},' +
                '{"type": {"coding": [{"code": "SB","system": "https://hl7.org/fhir/valueset-identifier-type.html"}]},"use": "usual","value": "'+@CHFID+'"},' +
                '{"type": {"coding": [{"code": "PPN","system": "https://hl7.org/fhir/valueset-identifier-type.html"}]},"use": "usual","value": "'+@Passport+'"}],' +
				'"link": [{"other": {"reference": "Patient/'+@HeadUUID+'"},"type": "'+@Relationship+'"}],' +
                '"name": [{"family": "'+@LastName+'","given": ["'+@OtherNames+'"],"use": "usual"}],"photo": [{"creation": "'+@PhotoDate+'","url": "'+@Photo+'"}],' +
                '"telecom": [{"system": "phone","use": "home","value": "'+@Phone+'"},{"system": "email","use": "home","value": "'+@Email+'"}]}},'
	
	SELECT @JSON = @JSON1 + @JSON2
	
	FETCH NEXT FROM CUR_TEST INTO @InsureeUUID, @CurrentAddress, @GeoLocation, @DOB, @IsHead, @ValidityFrom, @Education, @Profession, @Gender, @CHFID, @Passport, @Relationship, @LastName, @OtherNames, 
		@PhotoDate, @Phone, @Email, @Photo, @Poverty, @HeadUUID, @LocationUUID, @HFUUID

	SET @JSON = CASE WHEN @@FETCH_STATUS = 0 THEN @JSON ELSE LEFT(@JSON, LEN(@JSON) - 1) END

	EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @JSON


END
CLOSE CUR_TEST
DEALLOCATE CUR_TEST



-- ending the json
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, ']}'


EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @OLE
GO

-- Step 3: Disable Ole Automation Procedures
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO