USE [HRISDEV]

;DISABLE TRIGGER ALL ON DATABASE;

DECLARE @listcol TABLE ( colname VARCHAR(30))

-- tulis kolom-kolom yang akan diubah isinya disini
-- bisa tidak lengkap, karena menggunakan LIKE
INSERT INTO @listcol (colname) VALUES ('no_rekening')
INSERT INTO @listcol (colname) VALUES ('nm_keluarga')
INSERT INTO @listcol (colname) VALUES ('lahir')
INSERT INTO @listcol (colname) VALUES ('alamat')
INSERT INTO @listcol (colname) VALUES ('telp')
INSERT INTO @listcol (colname) VALUES ('handphone')
INSERT INTO @listcol (colname) VALUES ('induk_kependudukan')

DECLARE @colname VARCHAR(30)
DECLARE list_col CURSOR FAST_FORWARD FOR 
SELECT colname
FROM @listcol
OPEN list_col
FETCH NEXT FROM list_col INTO @colname
WHILE @@FETCH_STATUS = 0 
BEGIN
	DECLARE
		@table_schema VARCHAR(50), 
		@table_name VARCHAR(50), 
		@column_name VARCHAR(50), 
		@data_type VARCHAR(50),
		@character_maximum_length INT
	DECLARE list_col_tab CURSOR FAST_FORWARD FOR 
		SELECT
			c.TABLE_SCHEMA,
			c.TABLE_NAME,
			c.COLUMN_NAME,
			c.DATA_TYPE,
			c.CHARACTER_MAXIMUM_LENGTH 
		FROM
			INFORMATION_SCHEMA.COLUMNS AS c
			INNER JOIN
			SYSOBJECTS AS o ON c.TABLE_NAME = o.name
			INNER JOIN
			sys.partitions AS p ON o.id = p.object_id
		WHERE
			c.TABLE_CATALOG = 'HRISDEV'
			AND COLUMN_NAME LIKE '%' + @colname + '%'
			AND o.xtype = 'U'
			AND p.index_id < 2 -- 0:Heap, 1:Clustered 
			AND p.rows > 1

	OPEN list_col_tab
	FETCH NEXT FROM list_col_tab INTO @table_schema, @table_name, @column_name, @data_type, @character_maximum_length
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		DECLARE
			@numrow INT = 0,
			@ceknumrow NVARCHAR(400),
			@counter INT = 1
		IF @data_type = 'varchar' 
			SET @ceknumrow = N'SELECT @numrow = COUNT(*) FROM HRISDEV.dbo.['+@table_name+'] WHERE COALESCE(['+@column_name+'],'''') <> '''''
		ELSE
			SET @ceknumrow = N'SELECT @numrow = COUNT(*) FROM HRISDEV.dbo.['+@table_name+'] WHERE ['+@column_name+'] IS NOT NULL'
		EXEC sp_executesql @ceknumrow, N'@numrow INT OUTPUT', @numrow OUTPUT;
		WHILE @counter <= @numrow 
		BEGIN
			DECLARE @sql VARCHAR(400) = ''
			IF @data_type = 'varchar'
				BEGIN
					IF @column_name IN ('NM_KELUARGA', 'NM_IBU', 'NM_IBU_KDG', 'NM_KONTAK_DARURAT')
						BEGIN
							DECLARE 
								@jml_kata TINYINT = 0,
								@n_kata TINYINT = 1,
								@hasil VARCHAR(200) = ''

							SELECT @jml_kata = FLOOR(RAND() * 3) + 1

							WHILE ( @n_kata <= @jml_kata)
								BEGIN
									IF (@n_kata = 1)	-- first name
										SELECT TOP 1 @hasil = @hasil + nama FROM template_nama WHERE depan = 1 AND pria = 1 ORDER BY NEWID()
									ELSE IF (@n_kata = @jml_kata AND @jml_kata <> 1)	-- last name
										SELECT TOP 1 @hasil = @hasil + nama FROM template_nama WHERE belakang = 1 AND pria = 1 ORDER BY NEWID()
									ELSE
										SELECT TOP 1 @hasil = @hasil + nama FROM template_nama WHERE pria = 1 ORDER BY NEWID()

									IF (@n_kata <> @jml_kata) SET @hasil = @hasil + ' '
									
									SET @n_kata  = @n_kata  + 1
								END
							SET @hasil = REPLACE(@hasil, '''', '''''')

							SET @sql = 'UPDATE y SET ['+@column_name+'] = '''+@hasil+''' FROM (SELECT ['+@column_name+'], ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS x FROM HRISDEV.dbo.['+@table_name+'] WHERE COALESCE(['+@column_name+'],'''') <> '''') y WHERE y.x = '+CAST(@counter AS VARCHAR(10))


						END
					ELSE IF (@column_name LIKE '%telp%' OR @column_name LIKE '%phone%' OR @column_name LIKE '%no_rekening%' OR @column_name IN ('NPWP', 'NO_KTP', 'SIM', 'SIM_PASPORT', 'NO_KARTU_KELUARGA'))  
						BEGIN
							--SELECT dbo.func_mask_string_v2('083820996249', 'x')
							SET @sql = 'UPDATE y SET ['+@column_name+'] = dbo.func_mask_string_v2('+@column_name+', ''x'') FROM (SELECT ['+@column_name+'], ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS x FROM HRISDEV.dbo.['+@table_name+'] WHERE COALESCE(['+@column_name+'],'''') <> '''') y WHERE y.x = '+CAST(@counter AS VARCHAR(10))
						END
					ELSE
						BEGIN
							DECLARE
								@rand_str VARCHAR(20),
								@max_length INT,
								@retval_sp_randstr INT,
								@po_text VARCHAR(50)
							IF @character_maximum_length>20 SET @max_length = 20 -- batasin agar tidak terlalu panjang
								EXEC @retval_sp_randstr = dbo.proc_generate_random_string 
														@pi_min = 5,
														@pi_max = @max_length,
														@po_text = @rand_str OUTPUT
								SET @sql = 'UPDATE y SET ['+@column_name+'] = '''+@rand_str+''' FROM (SELECT ['+@column_name+'], ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS x FROM HRISDEV.dbo.['+@table_name+'] WHERE COALESCE(['+@column_name+'],'''') <> '''') y WHERE y.x = '+CAST(@counter AS VARCHAR(10))
						END
				END
			ELSE IF @data_type = 'date'
				BEGIN
					DECLARE
						@random_diff_date VARCHAR(10),
						@rand_upper INT = -100, -- batas min
						@rand_lower INT = 100 -- batas max
					SELECT @random_diff_date = CAST(ROUND(((@rand_upper - @rand_lower -1) * RAND() + @rand_lower), 0) AS VARCHAR(10))
					SET @sql = 'UPDATE y SET ['+@column_name+'] = DATEADD(DAY, '+@random_diff_date+', ['+@column_name+']) FROM (SELECT ['+@column_name+'], ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS x FROM HRISDEV.dbo.['+@table_name+'] WHERE COALESCE(['+@column_name+'],''1900-01-01'') <> ''1900-01-01'') y WHERE y.x = '+CAST(@counter AS VARCHAR(10))
				END
			--PRINT @sql  -- debug query
			PRINT @table_name + ' - ' + @column_name + ' - ' + CAST(@counter AS varchar(10))
			EXEC(@sql) -- run
			SET @counter = @counter + 1;
		END;
		FETCH NEXT FROM list_col_tab INTO @table_schema, @table_name, @column_name, @data_type, @character_maximum_length
	END
	CLOSE list_col_tab 
	DEALLOCATE list_col_tab

	FETCH NEXT FROM list_col INTO @colname 
END
CLOSE list_col 
DEALLOCATE list_col

;ENABLE TRIGGER ALL ON DATABASE;