CREATE TABLE SIPM.TradingTurnOverPerdaganganBEI
(
    TradeDate Date, 
	ID int null,
	Periode nvarchar (30) null,
	[Nilai TTO] float NULL
)
GO

CREATE TABLE [SIPM].[DPLE_PengawasanSRO_HargaSahamRataRataSahamReguler]
(
    TradeDate Date, 
	ID int null,
	Periode nvarchar (30) null,
	Tertimbang float null
)
GO

CREATE TABLE [SIPM].[DPLE_PengawasanSRO_NilaiKapitalisasiPasarReguler]
(
    TradeDate Date, 
        ID int null,
        Periode nvarchar (30) null,
        [Dalam Rupiah] numeric (30,0)  NULL,
        [Dalam USD] numeric (30,0)  NULL,
        kurs Int  NULL
)
GO

CREATE TABLE [SIPM].[DPLE_PengawasanSRO_RataRataTransaksiSahamHarian] (
[No] bigint,
[ID] varchar(1) NOT NULL,
[TradeDate] date,
[Rata-rata Transaksi] varchar(13) NOT NULL,
[Bulanan] float,
[Tahunan] float
)

CREATE TABLE [SIPM].[DPLE_PengawasanSRO_LaporanFasillitasIntraday]
(
	 [ReportDate] date
	,[ID] int null
	,[No] varchar (20) null
	,[Tanggal Transaksi] varchar (150) null
	,[Tanggal Penyelesaian] varchar (150) null
	,[Nama Bank]  varchar (150) null
	,[SubTotal]  numeric (30,0)  null
	,[Total] numeric (30,0) null
)
GO

CREATE TABLE [SIPM].[DPIV_PengawasanLaporanKeuangan_HasilKalkulasiPergerakanNABUPIndustriRD]
(
	[Type] int,
	Tahun varchar(4),
	Rasio varchar(50),
	[Equity Fund] decimal(10,6),
	[Sukuk Based Fund] decimal(10,6),
	[Fixed Income Fund] decimal(10,6),
	[Index Fund] decimal(10,6),
	[Mixed Asset Fund] decimal(10,6)
)
GO

CREATE TABLE [SIPM].[DPIV_TataKelolaManajerInvestasi_TabelHeaderTataKelolaMIARIA]
(
        [Kode LJK] nvarchar(25),
        [ID] int,
        [Nama] nvarchar(225),
        [Keterangan] nvarchar(225)
)
GO

CREATE TABLE [SIPM].[DPIV_RBSMI_MonitoringNilaiKompositRBSMI]
(
	[NamaPihak] nvarchar(250),
	[Nilai Tahun 2018] decimal(13,2),
	[Range Tahun 2018] nvarchar(100),
	[Nilai Tahun 2019] decimal(13,2),
	[Range Tahun 2019] nvarchar(100),
	[Nilai Tahun 2020] decimal(13,2),
	[Range Tahun 2020] nvarchar(100),
	[Nilai Tahun 2021] decimal(13,2),
	[Range Tahun 2021] nvarchar(100),
	[Nilai Tahun 2022] decimal(13,2),
	[Range Tahun 2022] nvarchar(100),
	[Rekomendasi] nvarchar(255)
)
GO



DECLARE @table_name VARCHAR(max)
DECLARE list_col CURSOR FAST_FORWARD FOR 
SELECT 
	[name]
FROM 
	SYSOBJECTS
WHERE
	crdate >= '2023-06-12'	-- mulai kapan dicreate
	AND xtype = 'U'	-- jenisnya table
OPEN list_col
FETCH NEXT FROM list_col INTO @table_name
WHILE @@FETCH_STATUS = 0 
BEGIN

	IF NOT EXISTS(
				SELECT
					*
				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_NAME = @table_name
					AND (
						c.COLUMN_NAME = 'ETLExecutionName'
						OR c.COLUMN_NAME = 'ETLExecutionDate'
						)
					AND o.xtype = 'U'
					AND p.index_id < 2 -- 0:Heap, 1:Clustered 
				)
		BEGIN
			PRINT 'ALTER TABLE [SIPM].['+@table_name+'] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL'
			--EXEC('ALTER TABLE [SIPM].['+@table_name+'] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL')
		END

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

--select * from INFORMATION_SCHEMA.TABLES
--select * from SYSOBJECTS
--SELECT * FROM SYS.TABLES
--SELECT * FROM INFORMATION_SCHEMA.TABLES
--WHERE TABLE_TYPE = 'BASE TABLE'

--ALTER TABLE [SIPM].[DPIV_PengawasanLaporanKeuangan_HasilKalkulasiPergerakanNABUPIndustriRD] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPIV_PengawasanLaporanKeuangan_HasilKalkulasiPergerakanUPIndustriRD] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPIV_TataKelolaManajerInvestasi_PenilaianTataKelolaPerPerusahaan] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPIV_TataKelolaManajerInvestasi_TabelHeaderTataKelolaMIARIA] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_DataTransaksiSahamBulananPerTipePemodal] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_DataTransaksiSahamTahunanPerTipePemodal] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_LaporanTransaksiBursaDanKliring] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_LaporanPenyelesaianTransaksiBursa] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_LaporanFasillitasIntraday] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_AbsensiLaporanBulananRutinBEI] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_AbsensiLaporanBulananKoreksiBEI] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_AbsensiLaporanBulananRutinKSEI] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_AbsensiLaporanBulananKoreksiKSEI] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_AbsensiLaporanBulananRutinKPEI] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_AbsensiLaporanBulananKoreksiKPEI] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_LaporanBulananRutinKSEI] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_LaporanBulananKoreksiKSEI] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_TradingTurnOverPerdaganganBEI] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_HargaSahamRataRataSahamReguler] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_NilaiKapitalisasiPasarReguler] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPLE_PengawasanSRO_RataRataTransaksiSahamHarian] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
ALTER TABLE [DSIM_AnalisisLaporanRutin_LaporanStatistikBulanan] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
ALTER TABLE [DPKM_DataLaporan_DaftarPihakDirekomendasikan] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL

--ALTER TABLE [SIPM].[DPLE_PengawasanPE_DataKantorCabangPerusahaanEfek] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPIV_PengawasanLaporanKeuangan_HasilKalkulasiKenaikanPenurunanInvestasiBersihNABIndustriRD] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPIV_TataKelolaManajerInvestasi_TabelKriteriaPenilaianTataKelolaMIARIA] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPIV_TataKelolaManajerInvestasi_TabelKuisionerPenilaianTataKelolaMIARIA] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPIV_TataKelolaManajerInvestasi_TabelSummaryKuisionerPenilaianTataKelolaMIARIA] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
--ALTER TABLE [SIPM].[DPIV_RBSMI_MonitoringNilaiKompositRBSMI] ADD [ETLExecutionName] [varchar](255) NULL, [ETLExecutionDate] [datetime] NULL
