DECLARE
  @no_polis VARCHAR(12),
  @tgl_konfirmasi DATETIME,
  @guid VARCHAR(40)

DECLARE list_cursor CURSOR FOR  
SELECT
  no_polis,
  tgl_konfirmasi,
  guid
FROM
  dbo.web_redemption
WHERE
  no_polis IN ('08.01330.1', '08.04198.2', '08.04951.6', '08.04247.4')

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_polis, @tgl_konfirmasi, @guid

WHILE @@FETCH_STATUS = 0  
BEGIN  
  DECLARE
    @server VARCHAR(50),
    @directory VARCHAR(150),
    @jml INT
    
  SELECT DISTINCT @server=server, @directory=directory FROM web_redemption_dokumen WHERE no_polis=@no_polis

  SELECT @jml=COUNT(*) FROM web_redemption_dokumen WHERE no_polis=@no_polis

  DECLARE @indeks INT
  SET @indeks = 1
  WHILE (@indeks <=42)
    BEGIN
      DECLARE
        @nama_file VARCHAR(60),
        @nama_file_asli VARCHAR(60)
      
      SET @nama_file = 'redemption_' + @no_polis + '_' + LEFT(CONVERT(VARCHAR(20),@tgl_konfirmasi,120),10) + '_' + CAST((@jml+@indeks) AS VARCHAR(2)) + '.jpg'
      SET @nama_file_asli = '2014_11_19_14_38_41_Page_' + RIGHT('0'+CAST(@indeks AS VARCHAR(2)),2) + '.jpg'
      
      PRINT @nama_file + ' --> ' + @nama_file_asli

      INSERT INTO dbo.web_redemption_dokumen
              (no_polis,
               tgl_konfirmasi,
               server,
               directory,
               nama_file,
               nama_file_asli,
               upload_date,
               upload_by,
               guid)
      VALUES
              (@no_polis, -- no_polis - varchar(11)
               @tgl_konfirmasi, -- tgl_konfirmasi - datetime
               @server, -- server - varchar(50)
               @directory, -- directory - varchar(150)
               @nama_file, -- nama_file - varchar(60)
               @nama_file_asli, -- nama_file_asli - varchar(60)
               GETDATE(), -- upload_date - datetime
               'SUP', -- upload_by - varchar(4)
               @guid  -- guid - varchar(40)
               )    
    
      SET @indeks = @indeks + 1
    END



  FETCH NEXT FROM list_cursor INTO @no_polis, @tgl_konfirmasi, @guid  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 


