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_TOKPED a(nolock)
    INNER JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=substring(no_referensi,0, charindex('';'',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 (''0'',''3'',''5'',''6'',''10'',''15'') and b.NO_BUKTI like ''%TP%''
    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_TOKPED a(nolock)
    INNER JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=substring(no_referensi,0, charindex('';'',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 (''700'',''701'') and b.NO_BUKTI like ''%tp%''
    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_TOKPED a(nolock)
    INNER JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=substring(no_referensi,0, charindex('';'',no_referensi)) and a.CUST=b.CUST
    LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
    WHERE
        isnull(print_count,'''') not in (''1'',''2'') and
        a.ORDER_STATUS in (''400'',''450'') and b.NO_BUKTI like ''%tp%''
    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,
        b.NO_REFERENSI,
        b.STATUS_TRANSAKSI,
        b.PRINT_COUNT,
        a.JSON AS JSON
    FROM [' + @dbname + ']..TABEL_WEBHOOK_TOKPED a(nolock)
    LEFT JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=substring(no_referensi,0, charindex('';'',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=''220'' 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_TOKPED 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=substring(c.no_referensi,0, charindex('';'',c.no_referensi)) and a.CUST=c.CUST
    WHERE a.tgl > ''2024-08-01'' and a.ORDER_STATUS=''220'' 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