--- _22222_triana_test_13981204_one_record
-- 13981204
USE [db_triana]
GO
/****** Object:  StoredProcedure [dbo].[_22222_triana_test_13981204_one_record]    Script Date: 2020/05/04 15:37:55 ******/



declare @old_name varchar(200)
IF EXISTS (SELECT name FROM sysobjects
     WHERE name = '_22222_triana_test_13981204_one_record' AND type = 'P')
	 begin
	 set @old_name='_22222_triana_test_13981204_one_record'+'_old_'+[dbo].[Fn_Now_DateTime]()
	  EXEC sp_rename '_22222_triana_test_13981204_one_record',@old_name;
   end
Go


Create PROCEDURE [dbo].[_22222_triana_test_13981204_one_record]
-- by alich needsoft 1398/12/04
-- exec _22222_triana_test_13981204_
-- 'E:\_MyCam_Triana_'  every one read  and write
/*
create TRIGGER [dbo].[tb_order_insert_update_trigger]
ON [dbo].[tb_order]
AFTER update,insert
AS
BEGIN

  SET NOCOUNT ON;
  EXEC [dbo].[_22222_triana_test_13981204_one_record]
END
*/
/*

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

*/
/*
create FUNCTION [dbo].[FA_ID_MyCam](@id int)  
RETURNS nvarchar(500)  
AS  
BEGIN  
	 DECLARE @root_10000 nvarchar(500)
	 DECLARE @root_100 nvarchar(500)
     DECLARE @root_0 nvarchar(500)

	 set @root_10000=ltrim((@id / 10000) * 10000) + '-' +ltrim(((@id / 10000) * 10000) + 9999)
	 set @root_100=ltrim((@id / 100) * 100) + '-' +ltrim(((@id / 100) * 100) + 99)
	 set @root_0=@root_10000+'\'+@root_100+'\'+ltrim(@id) 

     RETURN @root_0 
END
*/


/*

CREATE TABLE [dbo].[__log](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[sdate] [datetime] NOT NULL,
	[descr] [varchar](2000) NOT NULL,
 CONSTRAINT [PK__log_] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

*/

AS
BEGIN

DECLARE @SaveTargetDir_file varchar(256)
DECLARE @SaveTargetDir varchar(256)
DECLARE @GUID varchar(64)
DECLARE @ID int
DECLARE @Count_Copy int=0
DECLARE @Remain_Record int=0


DECLARE @S_File nvarchar(256)
DECLARE @T_File nvarchar(256)

DECLARE @xp_cmdshell_result bit

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

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

-- for trace Sp
DECLARE @TP bit=0
DECLARE @RC int=0
set @TP=1

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 FORWARD_ONLY FOR
				SELECT TOP 3
				ID,
				[GUID],
				SaveTargetDir
				FROM tb_order
				WHERE (transfered = 0)
				OR (transfered IS NULL) order by ID DESC
				OPEN cursor_tb_order

				if @TP=1
				insert __log (sdate,descr) values (GETDATE(),' top 3 row count not transferd :  '+str(LTRIM(@@CURSOR_ROWS)))

				FETCH NEXT FROM cursor_tb_order INTO @ID,@GUID, @SaveTargetDir
				WHILE @@FETCH_STATUS = 0
				BEGIN
				     select @IdxStudy=IdxStudy from  tb_study  where [GUID]=@GUID
					 if @IdxStudy <> 0
					 BEGIN

					 	  if @TP=1
				          insert __log (sdate,descr) values (GETDATE(),+'IdxStudy = '+LTRIM(Rtrim(@IdxStudy)+'     [GUID] = '+@GUID))

					      -- 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 , no_output
						  -- copy from SaveTargetDir to maycam path
						  DELETE @list_file_SaveTargetDir
                          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 FORWARD_ONLY FOR
							   select namefile from @list_file_SaveTargetDir where (isfile = 1) AND RIGHT(namefile,4) = '.jpg'
							   OPEN cursor_list_file_SaveTargetDir

							   	if @TP=1
				                insert __log (sdate,descr) values (GETDATE(),' *.jpg file count found :  '+str(LTRIM(@@CURSOR_ROWS))+' for IdxStudy '+LTRIM(Rtrim(@IdxStudy)))

							   FETCH NEXT FROM cursor_list_file_SaveTargetDir INTO @SaveTargetDir_file
							   	WHILE @@FETCH_STATUS = 0
				                BEGIN

								     if @TP=1
				                     insert __log (sdate,descr) values (GETDATE(),' SaveTargetDir_file  :'+@SaveTargetDir_file)
								     
								     SET @S_File=@SaveTargetDir+'\'+@SaveTargetDir_file
									 SET @T_File=@mycam_path+'\'+@id_mycam_path+'\'+LTRIM(RTRIM(@IdxStudy))+'_'+@SaveTargetDir_file
									 if (([dbo].[FA_FileExists](@S_File)=1) and ([dbo].[FA_FileExists]( @T_File)=0))
									 begin
			                 		  SET @shell_string='Copy '+@S_File+' '+@T_File


								     if @TP=1
				                     insert __log (sdate,descr) values (GETDATE(),' Copy Condition Ok   :'+@shell_string)


					                  EXEC @xp_cmdshell_result=xp_cmdshell @shell_string ,no_output
									  if @xp_cmdshell_result=0 
									  begin
									  update tb_order set transfered=1 where ID=@ID
									  set @Count_Copy=@Count_Copy+1

									  if @TP=1
				                      insert __log (sdate,descr) values (GETDATE(),' Transfered update to 1 id   :'+LTRIM(@ID)+'  count copy :'+LTRIM(@Count_Copy))

									  end
									  end


							   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 @ID,@GUID, @SaveTargetDir
				END
				CLOSE cursor_tb_order
				DEALLOCATE cursor_tb_order
        END
		select @Remain_Record=COUNT(*)  from tb_order where (transfered is null) or (transfered=0)
		select @Count_Copy as s_result ,'Count Copy = '+LTRIM(@Count_Copy) +' Remain Record ='+LTRIM(@Remain_Record)+'   sp :' + OBJECT_NAME(@@PROCID)  as s_message, ' no param' as s_param
END
 /*

 exec _22222_triana_test_

 select count(*)  from tb_order where transfered is null

  -- select guid,(select IdxStudy from tb_study where guid=tb.guid) as IdxStudy,COUNT(*) as tedad  from tb_order tb group by guid order by IdxStudy

 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\'

/*
--  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

 exec _22222_triana_test_


*/





*/


Go

CREATE TABLE [dbo].[__log](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[sdate] [datetime] NOT NULL,
	[descr] [varchar](2000) NOT NULL,
 CONSTRAINT [PK__log_] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TRIGGER [dbo].[tb_order_insert_update_trigger]
ON [dbo].[tb_order]
AFTER update,insert
AS
BEGIN

  SET NOCOUNT ON;

  EXEC [dbo].[_22222_triana_test_13981204_one_record]

END

Go


create FUNCTION [dbo].[FA_FileExists](@path varchar(8000))  
RETURNS BIT  
AS  
BEGIN  
     DECLARE @result INT  
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT  
     RETURN cast(@result as bit)  
END
/*
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

*/


GO

CREATE FUNCTION [dbo].[FA_ID_MyCam](@id int)  
RETURNS nvarchar(500)  
AS  
BEGIN  
	 DECLARE @root_10000 nvarchar(500)
	 DECLARE @root_100 nvarchar(500)
     DECLARE @root_0 nvarchar(500)

	 set @root_10000=ltrim((@id / 10000) * 10000) + '-' +ltrim(((@id / 10000) * 10000) + 9999)
	 set @root_100=ltrim((@id / 100) * 100) + '-' +ltrim(((@id / 100) * 100) + 99)
	 set @root_0=@root_10000+'\'+@root_100+'\'+ltrim(@id) 

     RETURN @root_0 
END
/*
select [dbo].[FA_ID_MyCam](999000)
select [dbo].[FA_ID_MyCam](1)



 /*
DELPHI CODE

     root_10000 := Image_Path + '\' + IntToStr((p_id div 10000) * 10000) + '-' +
    IntToStr(((p_id div 10000) * 10000) + 9999);

  root_100 := root_10000 + '\' + IntToStr((p_id div 100) * 100) + '-' +
    IntToStr(((p_id div 100) * 100) + 99);

  root_0 := root_100 + '\' + IntToStr(p_id);

  if not DirectoryExists(root_10000) then
    CreateDir(root_10000);

  if not DirectoryExists(root_100) then
    CreateDir(root_100);

  if not DirectoryExists(root_0) then
    CreateDir(root_0);

   */


*/


GO




CREATE VIEW [dbo].[_bimaran_triana_]
AS
SELECT        IdxStudy AS code_triana, ID + '  ' + Name AS nam_triana
FROM            dbo.tb_study

GO




Go
