#!/usr/bin/env python3
"""
从 ads 表中获取符合条件的记录
条件：tts 不为 1、且 ttscnt 等于 totsrts
其中 totsrts 从 srts 表中统计出来（按 adid 分组）
"""

import sqlite3
import traceback


def get_db_connection():
    """获取数据库连接"""
    try:
        conn = sqlite3.connect(r'\\ll\D\xtrssvjj\db\dpd.db', timeout=10.0)
        conn.row_factory = sqlite3.Row
        return conn
    except Exception as e:
        print(f"错误：连接数据库失败: {e}")
        traceback.print_exc()
        raise


def main():
    """主函数"""
    print("=== 开始获取符合条件的 ads 记录 ===")
    
    conn = None
    try:
        # 连接数据库
        conn = get_db_connection()
        
        # 构建查询语句
        # 使用 LEFT JOIN 确保所有 ads 记录都能显示，即使没有对应的 srts 记录
        # 筛选条件：tts != 1 且 ttscnt = totsrts 且 ttscnt > 0
        query = '''
        SELECT 
            a.rowid, 
            a.name, 
            COALESCE(s.tot_count, 0) as totsrts 
        FROM 
            ads a 
        LEFT JOIN (
            SELECT 
                adid, 
                COUNT(*) as tot_count 
            FROM 
                srts 
            GROUP BY 
                adid
        ) s ON a.rowid = s.adid 
        WHERE 
            a.tts != 1 
            AND a.ttscnt = COALESCE(s.tot_count, 0)
            AND a.ttscnt > 0
        ORDER BY 
            a.rowid DESC
        '''
        
        # 执行查询
        cursor = conn.execute(query)
        results = cursor.fetchall()
        
        # 打印结果
        print(f"找到 {len(results)} 条符合条件的记录：")
        print("-" * 80)
        print(f"{'rowid':<10} {'totsrts':<10} {'name'}")
        print("-" * 80)
        
        for row in results:
            rowid = row[0]
            name = row[1]
            totsrts = row[2]
            print(f"{rowid:<10} {totsrts:<10} {name}")
        
        print("-" * 80)
        print(f"总计：{len(results)} 条记录")
        
    except Exception as e:
        print(f"错误：{e}")
        traceback.print_exc()
    finally:
        if conn:
            conn.close()
            print("数据库连接已关闭")


if __name__ == "__main__":
    main()