DECLARE @sql NVARCHAR(MAX) = ''
DECLARE @dbname NVARCHAR(255)

-- Cursor to iterate over each database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb') AND name LIKE 'ERASOFT_%'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
	--tidak update batal
	SET @sql = @sql + '
	SELECT
		''Tidak Update Jadi Batal'' AS TYPE,
		(SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
		''' + @dbname + ''' AS DBPATHERA,
		a.TGL AS TGL_WEBHOOK,
		a.CUST,
		b.NO_BUKTI,
		b.NO_REFERENSI,
		b.STATUS_TRANSAKSI,
		b.PRINT_COUNT,
		a.JSON AS JSON
	FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a (nolock)
	INNER JOIN [' + @dbname + ']..SOT01A b(nolock) on a.ORDERID=NO_REFERENSI and a.CUST=b.CUST
	WHERE status_transaksi not in (''11'',''12'') and
		a.ORDER_STATUS in (''CANCELLED'') and b.NO_BUKTI like ''sp%''
	UNION ALL'

	--tidak update selesai
	SET @sql = @sql + '
	SELECT
		''Tidak Update Jadi Selesai'' AS TYPE,
		(SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
		''' + @dbname + ''' AS DBPATHERA,
		a.TGL AS TGL_WEBHOOK,
		a.CUST,
		b.NO_BUKTI,
		b.NO_REFERENSI,
		b.STATUS_TRANSAKSI,
		b.PRINT_COUNT,
		a.JSON AS JSON
	FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a(nolock)
	INNER JOIN [' + @dbname + ']..SOT01A b(nolock) on a.ORDERID=NO_REFERENSI and a.CUST=b.CUST
	WHERE ((ISNULL(al_cust, '''') <> '''' AND status_transaksi <> ''04'') OR (ISNULL(al_cust, '''') = '''' AND status_transaksi <> ''01'')) and
		a.ORDER_STATUS in (''COMPLETED'') and b.NO_BUKTI like ''sp%''
	UNION ALL'

	--tidak update siap kirim
	SET @sql = @sql + '
	SELECT
		''Tidak Update Jadi Siap Kirim'' AS TYPE,
		(SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
		''' + @dbname + ''' AS DBPATHERA,
		a.TGL AS TGL_WEBHOOK,
		a.CUST,
		b.NO_BUKTI,
		b.NO_REFERENSI,
		b.STATUS_TRANSAKSI,
		b.PRINT_COUNT,
		a.JSON AS JSON
	FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a(nolock)
	INNER JOIN [' + @dbname + ']..SOT01A b(nolock) on a.ORDERID=NO_REFERENSI and a.CUST=b.CUST
	WHERE isnull(b.STATUS_TRANSAKSI,'''') in (''0'') and
		a.ORDER_STATUS in (''READY_TO_SHIP'',''PROCESSED'',''SHIPPED'',''TO_CONFIRM_RECEIVE'',''READY'') and b.NO_BUKTI like ''sp%''
	UNION ALL'

	--tidak update sudah bayar
	SET @sql = @sql + '
	SELECT
		''Tidak Update Jadi Sudah Bayar'' AS TYPE,
		(SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
		''' + @dbname + ''' AS DBPATHERA,
		a.TGL AS TGL_WEBHOOK,
		a.CUST,
		b.NO_BUKTI,
		b.NO_REFERENSI,
		b.STATUS_TRANSAKSI,
		b.PRINT_COUNT,
		a.JSON AS JSON
	FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a(nolock)
	INNER JOIN [' + @dbname + ']..SOT01A b(nolock) on a.ORDERID=NO_REFERENSI and a.CUST=b.CUST
		WHERE isnull(STATUS_TRANSAKSI,'''') in (''0'') and
		a.ORDER_STATUS in (''READY_TO_SHIP'',''PROCESSED'',''SHIPPED'',''TO_CONFIRM_RECEIVE'',''READY'') and b.NO_BUKTI like ''sp%''
	UNION ALL'

	--tidak masuk
	SET @sql = @sql + '
	SELECT
		''Tidak Masuk'' AS TYPE,
		(SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
		''' + @dbname + ''' AS DBPATHERA,
		a.TGL AS TGL_WEBHOOK,
		a.CUST,
		b.NO_BUKTI,
		a.ORDERID,
		b.STATUS_TRANSAKSI,
		b.PRINT_COUNT,
		a.JSON AS JSON
	FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a(nolock)
	LEFT JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=b.NO_REFERENSI and a.CUST=b.CUST
	WHERE a.tgl > ''2024-08-01'' and a.ORDER_STATUS=''UNPAID'' and b.NO_BUKTI is null
	UNION ALL'

	--dobel
	SET @sql = @sql + '
	SELECT
		''Dobel'' AS TYPE,
		(SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
		''' + @dbname + ''' AS DBPATHERA,
		a.TGL AS TGL_WEBHOOK,
		a.CUST,
		NULL,
		c.NO_REFERENSI,
		NULL,
		NULL,
		a.JSON AS JSON
	FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a(nolock)
	INNER JOIN (
		SELECT CUST, NO_REFERENSI, COUNT(*) AS DUP
		FROM [' + @dbname + ']..SOT01A b(nolock)
		GROUP BY CUST, NO_REFERENSI
	) c ON a.ORDERID=c.NO_REFERENSI and a.CUST=c.CUST
	WHERE a.tgl > ''2024-08-01'' and a.ORDER_STATUS=''UNPAID'' AND c.DUP > 1

	UNION ALL'
	FETCH NEXT FROM db_cursor INTO @dbname
END

-- Remove the last 'UNION ALL'
SET @sql = LEFT(@sql, LEN(@sql) - 10)

-- Execute the dynamic SQL
EXEC sp_executesql @sql

-- Cleanup
CLOSE db_cursor
DEALLOCATE db_cursor