import cx_Oracle
import jinja2
import os
import datetime
from datetime import datetime
cx_Oracle.init_oracle_client(lib_dir=r'D:\oracleclient\instantclient_23_8')

def get_oracle_connection(config):
    """建立Oracle數(shù)據(jù)庫連接"""
    try:
        dsn = cx_Oracle.makedsn(
            config['host'], 
            config['port'], 
            service_name=config['service_name']
        )
        connection = cx_Oracle.connect(
            user=config['username'],
            password=config['password'],
            dsn=dsn,
            encoding="UTF-8",
            nencoding="UTF-8"
        )
        return connection
    except cx_Oracle.Error as error:
        print(f"連接數(shù)據(jù)庫時出錯: {error}")
        return None

def fetch_user_details(connection):
    """獲取所有用戶及其默認表空間信息"""
    users = []
    try:
        cursor = connection.cursor()
        cursor.execute("""
            SELECT username, default_tablespace
            FROM dba_users
            WHERE username IN  ('EDA_PROD','PMS_CORE','PMS_PORTAL','PMS_SEATA','PMS_SYSTEM','PMS_USER','RPT_DW','RPT_DW_READ','RPT_ODS','SPC_PROD') 
            ORDER BY username
        """)
        for row in cursor:
            users.append({
                'username': row[0],
                'tablespace_name': row[1],
                'tables': [],
                'views': [],
                'indexes': [],
                'procedures': [],
                'triggers': [],
                'synonyms': [],
                'sequences': [],
                'functions': [],
                'packages': []
            })
        cursor.close()
    except cx_Oracle.Error as error:
        print(f"獲取用戶信息時出錯: {error}")
    return users

def fetch_object_details(connection, users):
    """獲取每個用戶的對象詳細信息及創(chuàng)建語句（從all_objects獲取創(chuàng)建時間）"""
    try:
        cursor = connection.cursor()
        
        # 定義對象類型及其查詢配置（從all_objects獲取創(chuàng)建時間）
        object_types = {
            'tables': {
                'base_query': """
                    SELECT t.table_name, o.created, t.comments,o.owner as zso
                    FROM all_tab_comments t
                    JOIN all_objects o ON t.owner = o.owner AND t.table_name = o.object_name AND o.object_type = 'TABLE'
                    WHERE t.owner = :owner
                """,
                'object_type': 'TABLE',
                'name_field': 'table_name',
                'comment_field': 'comments',
                'date_field': 'created'
            },
            'views': {
                'base_query': """
                    SELECT v.table_name as view_name, o.created, v.comments,o.owner as zso
                    FROM all_tab_comments v
                    JOIN all_objects o ON v.owner = o.owner AND v.table_name = o.object_name AND o.object_type = 'VIEW'
                    WHERE v.owner = :owner
                """,
                'object_type': 'VIEW',
                'name_field': 'view_name',
                'comment_field': 'comments',
                'date_field': 'created'
            },
            'indexes': {
                'base_query': """
                    SELECT i.index_name, o.created, i.uniqueness,o.owner as zso
                    FROM dba_indexes i
                    JOIN all_objects o ON i.owner = o.owner AND i.index_name = o.object_name AND o.object_type = 'INDEX'
                    WHERE i.owner = :owner
                """,
                'object_type': 'INDEX',
                'name_field': 'index_name',
                'comment_field': 'uniqueness',
                'date_field': 'created'
            },
            'procedures': {
                'base_query': """
                    SELECT o.object_name, o.created, p.authid as comments,o.owner as zso
                    FROM all_objects o
                    JOIN dba_procedures p ON o.owner = p.owner AND o.object_name = p.object_name  and o.object_type = 'PROCEDURE'
                    WHERE o.owner = :owner  
                """,
                'object_type': 'PROCEDURE',
                'name_field': 'object_name',
                'comment_field': 'comments',
                'date_field': 'created'
            },
            'triggers': {
                'base_query': """
                    SELECT t.trigger_name, o.created, t.trigger_type,o.owner as zso
                    FROM dba_triggers t
                    JOIN all_objects o ON t.owner = o.owner AND t.trigger_name = o.object_name AND o.object_type = 'TRIGGER'
                    WHERE t.owner = :owner
                """,
                'object_type': 'TRIGGER',
                'name_field': 'trigger_name',
                'comment_field': 'trigger_type',
                'date_field': 'created'
            },
            'synonyms': {
                'base_query': """
                    SELECT s.synonym_name, o.created, s.table_owner || '.' || s.table_name||'-'||s.owner  as comments,o.owner as zso
                    FROM dba_synonyms s
                    JOIN all_objects o ON  s.synonym_name = o.object_name AND o.object_type = 'SYNONYM'
                    WHERE s.table_owner = :owner 
                """,
                'object_type': 'SYNONYM',
                'name_field': 'synonym_name',
                'comment_field': 'comments',
                'date_field': 'created'
            },
            'sequences': {
                'base_query': """
                    SELECT s.sequence_name, o.created, s.increment_by,o.owner as zso
                    FROM dba_sequences s
                    JOIN all_objects o ON s.sequence_owner = o.owner AND s.sequence_name = o.object_name AND o.object_type = 'SEQUENCE'
                    WHERE o.owner = :owner
                """,
                'object_type': 'SEQUENCE',
                'name_field': 'sequence_name',
                'comment_field': 'increment_by',
                'date_field': 'created'
            },
            'functions': {
                'base_query': """
                    SELECT o.object_name, o.created, f.authid as comments ,o.owner as zso
                    FROM all_objects o
                    JOIN dba_procedures f ON o.owner = f.owner AND o.object_name = f.object_name  and o.object_type = 'FUNCTION'
                    WHERE o.owner = :owner  
                """,
                'object_type': 'FUNCTION',
                'name_field': 'object_name',
                'comment_field': 'comments',
                'date_field': 'created'
            },
            'packages': {
                'base_query': """
                    SELECT o.object_name, o.created, p.authid as comments ,o.owner as zso
                    FROM all_objects o
                    JOIN dba_procedures p ON o.owner = p.owner AND o.object_name = p.object_name  and o.object_type = 'PACKAGE'
                    WHERE o.owner = :owner  
                """,
                'object_type': 'PACKAGE',
                'name_field': 'object_name',
                'comment_field': 'comments',
                'date_field': 'created'
            }
        }
        
        # 為每個用戶獲取每種對象的詳細信息及DDL
        for user in users:
            owner = user['username']
            for obj_type, obj_config in object_types.items():
                try:
                    # 先獲取對象基本信息（從all_objects關聯(lián)獲取創(chuàng)建時間）
                    cursor.execute(obj_config['base_query'], {'owner': owner})
                    obj_list = []
                    for row in cursor:
                        obj = {
                            'name': row[0],
                            'created_at': row[1].strftime('%Y-%m-%d %H:%M:%S') if row[1] else '未知',
                            'comment': row[2] if row[2] else '無' ,
                            'zso': row[3] 
                        }
                        obj_list.append(obj)
                    # 批量獲取DDL以提高性能
                    if obj_list:
                        ddl_cursor = connection.cursor()
                        for obj in obj_list:
                            try:
                                # 使用DBMS_METADATA獲取DDL
                                ddl_cursor.execute(
                                    f"SELECT DBMS_METADATA.GET_DDL(:obj_type, :obj_name, :owner) FROM dual",
                                    {
                                        'obj_type': obj_config['object_type'],
                                        'obj_name': obj['name'],
                                        'owner': obj['zso']
                                    }
                                )
                                ddl_row = ddl_cursor.fetchone()
                                obj['create_statement'] = ddl_row[0] if ddl_row and ddl_row[0] else '無法獲取DDL'
                            except cx_Oracle.Error as ddl_error:
                                obj['create_statement'] = f"獲取DDL失敗: {str(ddl_error)}"
                        ddl_cursor.close()
                    
                    user[obj_type] = obj_list
                    
                except cx_Oracle.Error as error:
                    print(f"獲取{obj_type}信息時出錯 (用戶: {owner}): {error}")
        
        cursor.close()
    except cx_Oracle.Error as error:
        print(f"獲取對象詳細信息時出錯: {error}")
    return users

def fetch_object_counts(users):
    """根據(jù)對象詳細信息計算數(shù)量"""
    for user in users:
        user['table_count'] = len(user['tables'])
        user['view_count'] = len(user['views'])
        user['index_count'] = len(user['indexes'])
        user['procedure_count'] = len(user['procedures'])
        user['trigger_count'] = len(user['triggers'])
        user['synonym_count'] = len(user['synonyms'])
        user['sequence_count'] = len(user['sequences'])
        user['function_count'] = len(user['functions'])
        user['package_count'] = len(user['packages'])
    return users

def generate_report(users, template_path, output_path,dbname):
    """使用Jinja2模板生成HTML報告（包含DDL轉義處理）"""
    try:
        # 準備模板渲染數(shù)據(jù)
        report_data = {
            'dbname':dbname,
            'users': users,
            'generate_time': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            # 設置has_*標志，控制列顯示
            'has_tables': any(len(user['tables']) > 0 for user in users),
            'has_views': any(len(user['views']) > 0 for user in users),
            'has_indexes': any(len(user['indexes']) > 0 for user in users),
            'has_procedures': any(len(user['procedures']) > 0 for user in users),
            'has_triggers': any(len(user['triggers']) > 0 for user in users),
            'has_synonyms': any(len(user['synonyms']) > 0 for user in users),
            'has_sequences': any(len(user['sequences']) > 0 for user in users),
            'has_functions': any(len(user['functions']) > 0 for user in users),
            'has_packages': any(len(user['packages']) > 0 for user in users)
        }
        

        # 加載模板
        env = jinja2.Environment(
            loader=jinja2.FileSystemLoader(os.path.dirname(template_path)),
            # autoescape=True  # 啟用自動轉義，防止HTML注入
        )
        # 自定義過濾器：轉義DDL中的HTML特殊字符
        env.filters['escape_ddl'] = lambda ddl: ddl.replace('<', '<').replace('>', '>').replace('&', '&')
        template = env.get_template(os.path.basename(template_path))
        
        # 渲染模板并保存
        with open(output_path, 'w', encoding='utf-8') as f:
            f.write(template.render(report_data))
        
        print(f"報告已生成: {output_path}")
    except Exception as e:
        print(f"生成報告時出錯: {e}")

def main():
    """主函數(shù)"""
    # 數(shù)據(jù)庫連接配置（請修改為實際配置）
    db_config = {
        'host': '10.12.8.61',
        'port': 1521,
        'service_name': 'edadb',
        'username': 'system',
        'password': 'Zeta@xxxx#$'
    }
    
    # 模板和輸出文件路徑
    template_path = 'object_template.html'
    output_path = 'edadb_object.html'
    
    # 連接數(shù)據(jù)庫
    connection = get_oracle_connection(db_config)
    if not connection:
        return
    
    try:
        # 獲取用戶信息
        users = fetch_user_details(connection)
        if not users:
            print("未找到用戶信息")
            return
        
        # 獲取對象詳細信息及DDL
        users = fetch_object_details(connection, users)
        
        # 計算對象數(shù)量
        users = fetch_object_counts(users)
        
        # 生成報告
        generate_report(users, template_path, output_path,dbname='EDADB')
        
    finally:
        # 關閉數(shù)據(jù)庫連接
        if connection:
            connection.close()
            print("數(shù)據(jù)庫連接已關閉")

if __name__ == "__main__":
    main()

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, minimum-scale=0.8, maximum-scale=1.5">
    <title>{{ dbname }}對象統(tǒng)計報告</title>
    <style>
        /* 響應式核心樣式 */
        * {
            box-sizing: border-box;
            margin: 0;
            padding: 0;
        }

        body {
            font-family: "Inter", sans-serif;
            background-color: #f5f7fa;
            line-height: 1.6;
        }

        .container {
            max-width: 1200px;
            margin: 0 auto;
            padding: 20px;
            min-width: 320px;
        }

        .page-title {
            text-align: center;
            margin-bottom: 30px;
        }

        .main-table {
            width: 100%;
            overflow-x: auto; /* 小屏幕顯示橫向滾動條 */
            margin-bottom: 30px;
            box-shadow: 0 2px 12px rgba(0,0,0,0.05);
            border-radius: 10px;
            background-color: white;
        }

        .user-table {
            min-width: 800px;
            border-collapse: collapse;
        }

        .user-table th,
        .user-table td {
            padding: 12px 15px;
            text-align: left;
            border-bottom: 1px solid #e0e0e0;
        }

        .user-table th {
            background-color: #f8f9fa;
            font-weight: 600;
            white-space: nowrap;
        }

        .user-table tr:hover {
            background-color: #fafafa;
        }

        /* 響應式斷點 */
        @media (max-width: 768px) {
            .user-table th:nth-child(5),
            .user-table td:nth-child(5),
            .user-table th:nth-child(6),
            .user-table td:nth-child(6) {
                display: none; /* 手機端隱藏存儲過程和觸發(fā)器列 */
            }
        }

        @media (max-width: 576px) {
            .user-table th:nth-child(7),
            .user-table td:nth-child(7),
            .user-table th:nth-child(8),
            .user-table td:nth-child(8) {
                display: none; /* 超小屏隱藏同義詞和序列列 */
            }
        }

        /* 詳情模塊 */
        .user-details {
            margin-bottom: 30px;
            background-color: white;
            border-radius: 10px;
            box-shadow: 0 2px 12px rgba(0,0,0,0.05);
            padding: 20px;
        }

        .tab-nav {
            display: flex;
            overflow-x: auto; /* 選項卡橫向滾動 */
            padding-bottom: 15px;
            border-bottom: 1px solid #e0e0e0;
        }

        .tab {
            padding: 10px 18px;
            cursor: pointer;
            white-space: nowrap;
            margin-right: 15px;
            color: #666;
        }

        .tab.active {
            color: #165dff;
            border-bottom: 2px solid #165dff;
            font-weight: 500;
        }

        .tab-content {
            padding: 20px 0;
            display: none;
        }

        .tab-content.active {
            display: block;
        }

        .object-table {
            width: 100%;
            margin-bottom: 20px;
            border-collapse: collapse;
        }

        .object-table th,
        .object-table td {
            padding: 10px 15px;
            border-bottom: 1px solid #f0f0f0;
        }

        .object-table th {
            background-color: #f8f9fa;
            font-weight: 500;
            white-space: nowrap;
        }

        .code-block {
            background-color: #f5f7fa;
            padding: 15px;
            margin-top: 10px;
            border-radius: 6px;
            font-family: "Consolas", monospace;
            font-size: 0.9em;
            line-height: 1.4;
            overflow-x: auto;
        }

        /* 操作按鈕 */
        .action-btn {
            background-color: #165dff;
            color: white;
            border: none;
            padding: 6px 12px;
            border-radius: 4px;
            cursor: pointer;
            transition: opacity 0.3s;
        }

        .action-btn:hover {
            opacity: 0.9;
        }

        /* 隱藏空模塊 */
        .hidden {
            display: none !important;
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="page-title">
            <h1 class="text-2xl font-bold">{{ dbname }}對象統(tǒng)計報告</h1>
            <p class="text-gray-500 mt-2">生成時間: {{ generate_time }}</p>
        </div>

        <div class="main-table">
            <h2 class="text-lg font-bold mb-4">用戶對象統(tǒng)計信息</h2>
            <table class="user-table">
                <thead>
                    <tr>
                        <th>用戶名</th>
                        <th>默認表空間</th>
                        {% if has_tables %}<th>表數(shù)量</th>{% endif %}
                        {% if has_views %}<th>視圖數(shù)量</th>{% endif %}
                        {% if has_indexes %}<th>索引數(shù)量</th>{% endif %}
                        {% if has_procedures %}<th>存儲過程</th>{% endif %}
                        {% if has_triggers %}<th>觸發(fā)器數(shù)量</th>{% endif %}
                        {% if has_synonyms %}<th>同義詞數(shù)量</th>{% endif %}
                        {% if has_sequences %}<th>序列數(shù)量</th>{% endif %}
                        {% if has_functions %}<th>函數(shù)數(shù)量</th>{% endif %}
                        {% if has_packages %}<th>包數(shù)量</th>{% endif %}
                        <th>操作</th>
                    </tr>
                </thead>
                <tbody>
                    {% for user in users %}
                    <tr>
                        <td>{{ user.username }}</td>
                        <td>{{ user.tablespace_name }}</td>
                        {% if has_tables %}<td>{{ user.table_count }}</td>{% endif %}
                        {% if has_views %}<td>{{ user.view_count }}</td>{% endif %}
                        {% if has_indexes %}<td>{{ user.index_count }}</td>{% endif %}
                        {% if has_procedures %}<td>{{ user.procedure_count }}</td>{% endif %}
                        {% if has_triggers %}<td>{{ user.trigger_count }}</td>{% endif %}
                        {% if has_synonyms %}<td>{{ user.synonym_count }}</td>{% endif %}
                        {% if has_sequences %}<td>{{ user.sequence_count }}</td>{% endif %}
                        {% if has_functions %}<td>{{ user.function_count }}</td>{% endif %}
                        {% if has_packages %}<td>{{ user.package_count }}</td>{% endif %}
                        <td>
                            <button class="action-btn" onclick="toggleDetails('{{ user.username }}')">
                                查看詳情
                            </button>
                        </td>
                    </tr>
                    {% endfor %}
                </tbody>
            </table>
        </div>

        {% for user in users %}
        <div id="{{ user.username }}_details" class="user-details hidden">
            <div class="flex justify-between items-center mb-4">
                <h3 class="text-xl font-bold">用戶: {{ user.username }}</h3>
                <button class="action-btn" onclick="hideDetails('{{ user.username }}')">
                    收起
                </button>
            </div>

            <div class="tab-nav">
                {% set object_types = ['tables', 'views', 'indexes', 'procedures', 'triggers', 'synonyms', 'sequences', 'functions','packages'] %}
                {% for type in object_types %}
                {% set items = user[type] %}
                {% if items|length > 0 %}
                <div class="tab" data-target="{{ user.username }}_{{ type }}">
                    {{ type|capitalize|replace('_', ' ') }}
                </div>
                {% endif %}
                {% endfor %}
            </div>

            {% for type in object_types %}
            {% set items = user[type] %}
            {% if items|length > 0 %}
            <div id="{{ user.username }}_{{ type }}" class="tab-content">
                <h4 class="text-md font-semibold mb-3">
                    {{ type|capitalize|replace('_', ' ') }} 列表 (共 {{ items|length }} 個)
                </h4>
                <table class="object-table">
                    <thead>
                        <tr>
                            <th>對象名</th>
                            <th>創(chuàng)建時間</th>
                            <th>對象注釋</th>
                            <th>操作</th>
                        </tr>
                    </thead>
                    <tbody>
                        {% for item in items %}
                        <tr>
                            <td>{{ item.name }}</td>
                            <td>{{ item.created_at }}</td>
                            <td>{{ item.comment|default('無') }}</td>
                            <td>
                                <button class="action-btn" onclick="toggleCode('{{ user.username }}', '{{ type }}', '{{ item.name }}')">
                                    查看語句
                                </button>
                            </td>
                        </tr>
                        <tr id="{{ user.username }}_{{ type }}_{{ item.name }}_code" style="display: none;">
                            <td colspan="4">
                                <div class="code-block">
                                    {{ item.create_statement|replace('\n', '
')}}  
                                </div>
                            </td>
                        </tr>
                        {% endfor %}
                    </tbody>
                </table>
            </div>
            {% endif %}
            {% endfor %}
        </div>
        {% endfor %}

        <script>
            function toggleDetails(username) {
                const details = document.getElementById(`${username}_details`);
                details.classList.toggle('hidden');
                
                if (!details.classList.contains('hidden')) {
                    // 激活第一個存在的選項卡
                    const firstTab = details.querySelector('.tab');
                    if (firstTab) {
                        activateTab(firstTab);
                    }
                }
            }

            function hideDetails(username) {
                const details = document.getElementById(`${username}_details`);
                details.classList.add('hidden');
            }

            function toggleCode(username, type, name) {
                const codeBlock = document.getElementById(`${username}_${type}_${name}_code`);
                if (codeBlock.style.display === 'none') {
                    codeBlock.style.display = 'table-row';
                } else {
                    codeBlock.style.display = 'none';
                }
            }

            function activateTab(tab) {
                const target = tab.getAttribute('data-target');
                
                // 移除其他選項卡的激活狀態(tài)
                const tabs = tab.closest('.user-details').querySelectorAll('.tab');
                tabs.forEach(t => t.classList.remove('active'));
                tab.classList.add('active');
                
                // 隱藏所有內容
                const contents = tab.closest('.user-details').querySelectorAll('.tab-content');
                contents.forEach(c => c.classList.remove('active'));
                
                // 顯示目標內容
                document.getElementById(target).classList.add('active');
            }

            // 選項卡切換
            document.addEventListener('click', (e) => {
                if (e.target.classList.contains('tab')) {
                    activateTab(e.target);
                }
            });
        </script>
    </div>
</body>
</html>