USE [db_triana]
GO
/****** Object:  StoredProcedure [dbo].[__triana_test_]    Script Date: 2020/02/11 09:42:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[__triana_test_13981122_]
AS
BEGIN
/*
--  by alich needsoft  1398/09/05

--  exec sp_Ezdenti_e2_img_ExportImage_all

not install Ezden servor on program file

set D:\VATECH\EzDentFile\

check intrnal sp result hatman

set param path_imge_Ezdenti,path_imge_Ezdenti  Setting_Ezdenti
select * from Setting_Ezdenti

update Setting_Ezdenti 
set path_Bmp2Jpeg='C:\VATECH20\Common\FM\FMData\Files\Bmp2Jpeg.exe',
path_imge_Ezdenti='C:\VATECH20\Common\FM\FMData\Files'


EXEC sp_configure 'show advanced options', '1'
RECONFIGURE
-- this enables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '1' 
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
-- test 
EXEC xp_dirtree 'C:\', 1, 1

*/
DECLARE @SaveTargetDir_file varchar(256)
DECLARE @SaveTargetDir varchar(256)
DECLARE @GUID varchar(64)

DECLARE @IdxStudy int=0
DECLARE @mycam_path nvarchar(500)='C:\_MYCAMNS_'
DECLARE @id_mycam_path nvarchar(500)
DECLARE @shell_string nvarchar(500)

DECLARE @list_file_SaveTargetDir TABLE (
      namefile varchar(255),
      depth int,
      isfile bit
    )


SET NOCOUNT ON;

IF EXISTS (SELECT TOP 1
[GUID]
FROM tb_order
WHERE (transfered = 0)
OR (transfered IS NULL))
		BEGIN
		    	DECLARE cursor_tb_order CURSOR FAST_FORWARD FOR
				SELECT TOP 30
				[GUID],
				SaveTargetDir
				FROM tb_order
				WHERE (transfered = 0)
				OR (transfered IS NULL) order by ID DESC
				OPEN cursor_tb_order
				FETCH NEXT FROM cursor_tb_order INTO @GUID, @SaveTargetDir
				WHILE @@FETCH_STATUS = 0
				BEGIN
				     select @IdxStudy=IdxStudy from  tb_study  where [GUID]=@GUID
					 if @IdxStudy <> 0
					 BEGIN
					      -- create mycam DIR
						  SET @id_mycam_path=[dbo].[FA_ID_MyCam](@IdxStudy)
						  SET @shell_string='MD '+@mycam_path+'\'+@id_mycam_path
					      EXEC xp_cmdshell @shell_string
						  -- copy from SaveTargetDir to maycam path
                          INSERT INTO @list_file_SaveTargetDir EXEC xp_dirtree @SaveTargetDir,1, 1
						  if exists (select namefile from @list_file_SaveTargetDir where (isfile = 1) AND RIGHT(namefile,4) = '.jpg' )
						  begin
						       DECLARE cursor_list_file_SaveTargetDir CURSOR FAST_FORWARD FOR
							   select namefile from @list_file_SaveTargetDir where (isfile = 1) AND RIGHT(namefile,4) = '.jpg'
							   OPEN cursor_list_file_SaveTargetDir
							   FETCH NEXT FROM cursor_list_file_SaveTargetDir INTO @SaveTargetDir_file
							   	WHILE @@FETCH_STATUS = 0
				                BEGIN
									  SET @shell_string='Copy '+@SaveTargetDir+'\'+@SaveTargetDir_file+' '+@mycam_path+'\'+@id_mycam_path
					                  EXEC xp_cmdshell @shell_string


							   FETCH NEXT FROM cursor_list_file_SaveTargetDir INTO @SaveTargetDir_file
							   END
							    CLOSE cursor_list_file_SaveTargetDir
			                	DEALLOCATE cursor_list_file_SaveTargetDir


						  end
					 END


				FETCH NEXT FROM cursor_tb_order INTO @GUID, @SaveTargetDir
				END
				CLOSE cursor_tb_order
				DEALLOCATE cursor_tb_order
        END
END
 /*

 exec __triana_test_



 IF OBJECT_ID(‘tempdb..#DirectoryTree’)IS NOT NULL
      DROP TABLE#DirectoryTree;
CREATE TABLE #DirectoryTree (
       id int IDENTITY(1,1)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);
INSERT#DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree ‘D:BackupTRON4TEST2MyDb1’,1,1;
SELECT * FROM #DirectoryTree
WHERE isfile = 1 AND RIGHT(subdirectory,4) = ‘.BAK’
ORDER BYid;
GO


EXEC master..xp_cmdShell 'Copy C:\LuckyOld\*.*  C:\LuckyNew\'



*/