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,
        a.SHOPID,
        c.TIDAK_HIT_UANG_R,
        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
    LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.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,
        a.SHOPID,
        c.TIDAK_HIT_UANG_R,
        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
    LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.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,
        a.SHOPID,
        c.TIDAK_HIT_UANG_R,
        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
    LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.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,
        a.SHOPID,
        c.TIDAK_HIT_UANG_R,
        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
    LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.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,
        a.SHOPID,
        c.TIDAK_HIT_UANG_R,
        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
    LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.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,
        a.SHOPID,
        NULL,
        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