SET NOCOUNT ON DECLARE @poscode AS VARCHAR(10), @posdesc AS VARCHAR(100), @acctno AS VARCHAR(20), @provcode AS VARCHAR(20), @PatientID AS VARCHAR(25), @ChartNumber AS VARCHAR(30), @ChartID AS VARCHAR(30), @Last_Name AS VARCHAR(30), @First_Name AS VARCHAR(30), @Middle_Initial AS VARCHAR(5), @Sex AS CHAR(1), @DOB AS CHAR(10), @SSN AS CHAR(11), @ID AS VARCHAR(20), @LastName AS VARCHAR(30), @FirstName AS VARCHAR(30), @MiddleInitial AS VARCHAR(5), @TITLE AS VARCHAR(10), @Suffix AS VARCHAR(10), @UPIN AS VARCHAR(20), @NAME AS VARCHAR(100), @LocID VARCHAR(20), @OldDocDateTime AS VARCHAR(22), @OldSubject AS VARCHAR(80), @Filename AS VARCHAR(50), @PATH AS VARCHAR(50), @PAGE AS VARCHAR(10), @documentid AS VARCHAR(40), @SQL AS VARCHAR(8000), @COPYSCRIPT AS nvarchar(4000), @CREATEDIRSCRIPT AS nvarchar(4000), @DELETEDIRSCRIPT AS nvarchar(4000), @Export_FileName AS VARCHAR(100), @Merge_Script AS nvarchar(4000), @RTFScript AS nvarchar(4000), @Copy_Script AS nvarchar(4000), @PageCount AS INTEGER, @cmd AS varchar(1000), @SourceFileExists AS nvarchar(1), @TargetFileExists AS nvarchar(1), @ProcessDocument AS nvarchar(1), @FirstFile AS VARCHAR(100), @ImageRootDirectory AS VARCHAR(100), @ImageExportDirectory AS VARCHAR(100), @TiffMergerDirectory AS VARCHAR(100), @RTFConverterDirectory AS VARCHAR(100), @TreatMergeAsCopy AS nvarchar(1) -- SET @TreatMergeAsCopy = 'N' SET @TreatMergeAsCopy = 'Y' SET @ImageRootDirectory = 'G:\Fox_Meadows\Document_Conversion\ImpactMD\Images\' SET @ImageExportDirectory = 'G:\Fox_Meadows\Documents\' SET @TiffMergerDirectory = 'G:\Fox_Meadows\Document_Conversion\ImpactMD\Tiff\' SET @RTFConverterDirectory = 'G:\Fox_Meadows\Document_Conversion\ImpactMD\Tiff\' USE ImpactMD DECLARE @Charts (ChartId varchar(30), DocumentId varchar(40), [Path] varchar(50), PAGE varchar(10), [Filename] varchar(50), Id int IDENTITY(1,1) PRIMARY KEY) DECLARE @MinChart int DECLARE @MaxChart int INSERT INTO @Charts (ChartId, DocumentId, [Path], Page, [FileName]) SELECT DISTINCT (P.ChartID, H.DocumentId, Documents.[Path], Documents.Page, Documents.[FileName]) FROM IMPACTMD.dbo.Patients P INNER JOIN (SELECT Doc_Header.PatientID, Doc_Header.DocumentId, Doc_Detail.[Path], Doc_Detail.[FileName], Doc_Detail.[Page], FROM Doc_Header INNER JOIN Doc_Detail ON Doc_Header.DocumentId = Doc_Detail.DocumentId) Documents ON Documents.PatientID = P.PatientID SET @MinChart = 1 SET @MaxChart = SCOPE_IDENTITY() CREATE TABLE #FolderFiles(s varchar(1000), Prcessed bit DEFAULT(0)) WHILE @MinChart <= @MaxChart BEGIN -- Get the first ChartId SELECT @ChartId = ChartId @documentid = DocumentId @PATH = [Path], @Filename = [FilerName], @PAGE = [Page] FROM @Charts WHERE Id = @MinChart PRINT '' PRINT '---------------------------------------------------------------------' PRINT 'ChartID: ' + @ChartID PRINT '---------------------------------------------------------------------' SELECT @cmd = 'dir /B ' + '"' + @ImageRootDirectory + @Path + @FileName + '"' INSERT #FolderFiles (s) EXEC master..xp_cmdshell @cmd IF EXISTS (SELECT * FROM #FolderFiles WHERE s = @FileName AND Processed = 0) BEGIN SET @SourceFileExists = 'Y' --PRINT 'Source file ' + @ImageRootDirectory + @Path + @FileName + ' exists' END ELSE BEGIN SET @SourceFileExists = 'N' --PRINT 'Source file ' + @ImageRootDirectory + @Path + @FileName + ' does not exist' END UPDATE #FolderFiles SET Processed = 1 WHERE Processed = 0 IF @Filename COLLATE Latin1_General_CI_AI LIKE '%.TIF%' OR @Filename COLLATE Latin1_General_CI_AI LIKE '%.DOC%' BEGIN IF @PAGE = 1 BEGIN SET @PageCount = 1 SET @FirstFile = @ImageRootDirectory + @PATH + @Filename SET @Export_FileName = 'EXP_' + @Filename COLLATE Latin1_General_CI_AI -- Currently G:\TCimages and F:\Images1 on client box SET @Merge_Script = @ImageRootDirectory + @PATH + @Filename + ',' SET @Copy_Script = 'copy ' + '"' + @ImageRootDirectory + @PATH + @Filename + '" "' + @ImageExportDirectory + @Path + @Export_FileName + '"' SELECT @cmd = 'dir /B ' + '"' + @ImageExportDirectory + @Path + @Export_FileName + '"' INSERT INTO #FolderFiles (s) EXEC master..xp_cmdshell @cmd IF EXISTS (SELECT * FROM #FolderFiles WHERE s = @Export_FileName AND Processed = 0) BEGIN SET @TargetFileExists = 'Y' --PRINT 'Target file ' + @ImageRootDirectory + @Path + @FileName + ' exists' SET @ProcessDocument = 'N' -- Target File Family already exists, so do not process again END ELSE BEGIN SET @TargetFileExists = 'N' --PRINT 'Target file ' + @ImageRootDirectory + @Path + @FileName + ' does not exist' END UPDATE #FolderFiles SET Processed = 1 WHERE Processed = 0 END IF @PAGE <> 1 AND @Filename COLLATE Latin1_General_CI_AI LIKE '%.TIF%' BEGIN SET @PageCount = @PageCount + 1 SET @Merge_Script = @Merge_Script + @ImageRootDirectory + @PATH + @Filename + ',' END IF @ProcessDocument = 'Y' AND @TreatMergeAsCopy = 'Y' BEGIN SET @Copy_Script = 'copy ' + '"' + @ImageRootDirectory + @PATH + @Filename+ '" "' + @ImageExportDirectory + @Path +'EXP_' + @FileName + '"' ---PRINT 'COPY: ' + @Copy_Script EXEC MASTER.dbo.xp_cmdshell @Copy_Script, no_output END END SET @MinChart = @MinChart + 1 END DROP TABLE #FolderFiles SET NOCOUNT OFF