ks_automation/common/sql_help.py

import datetime
import os
import random
import sys
from datetime import timedelta

sys.path.append(os.getcwd())
from datetime import datetime
from common import MysqlHelper
from common import Common


class sqlHelp():

    @classmethod
    def get_count_list(cls, name_list):
        count_list = []
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        for name in name_list:

            count = f"""SELECT COUNT(*) AS agc_video_deposit FROM ( SELECT audio, account_id FROM agc_video_deposit WHERE time = '{formatted_time}' AND mark = '{name["mark"]}' GROUP BY audio, account_id) AS subquery;"""
            count = MysqlHelper.get_values(count, "prod")
            if count == None:
                count = 0
            count = str(count).replace('(', '').replace(')', '').replace(',', '')
            count_list.append(f"{name['mark_name']}生成条数为:{count}条 \n")
        return count_list

    """
    获取未使用的视频链接
    """
    @classmethod
    def get_url_list(cls, user_list, mark, limit_count):
        for i in range(8):
            user = str(random.choice(user_list))
            user = user.replace('(', '').replace(')', '').replace(',', '')
            current_time = datetime.now()
            three_days_ago = current_time - timedelta(days=1)
            formatted_current_time = current_time.strftime("%Y-%m-%d")
            formatted_three_days_ago = three_days_ago.strftime("%Y-%m-%d")
            url_list = f"""SELECT a.video_id, a.account_id, a.oss_object_key 
                                                 FROM agc_video_url a 
                                                 LEFT JOIN agc_video_deposit b 
                                                 ON a.oss_object_key = b.oss_object_key 
                                                 AND b.time >= '{formatted_three_days_ago}' 
                                                 AND b.time <= '{formatted_current_time}' 
                                                 WHERE b.video_id IS NULL 
                                                 AND a.account_id = {user} 
                                                 AND a.status = 1 
                                                 AND a.mark = '{mark}' 
                                                 LIMIT {limit_count};"""
            url_list = MysqlHelper.get_values(url_list, "prod")
            if url_list:
                if len(url_list) >= 35:
                    return url_list, user
        return None, None

    """
    获取已入库的用户id
    """
    @classmethod
    def get_user_id(cls, channel_type, mark):
        account_id = f"""select account_id from agc_video_url where mark = '{mark}' and  oss_object_key LIKE '%{channel_type}%' group by account_id ;"""
        account_id = MysqlHelper.get_values(account_id, "prod")
        return account_id

    """
    获取已入库的用户id
    """
    @classmethod
    def get_link_count(cls, mark, platform):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        count = f"""SELECT COUNT(*) AS total_count FROM ( SELECT audio, account_id FROM agc_video_deposit WHERE time = '{formatted_time}' AND platform = '{platform}' and mark = '{mark}' GROUP BY audio, account_id) AS subquery;"""
        count = MysqlHelper.get_values(count, "prod")
        if count == None:
            count = 0
        count = str(count).replace('(', '').replace(')', '').replace(',', '')
        return int(count)

    """
    获取跟随脚本已入库数量
    """
    @classmethod
    def get_link_gs_count(cls, mark):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        count = f"""SELECT COUNT(*) AS total_count FROM ( SELECT audio, account_id FROM agc_video_deposit WHERE time = '{formatted_time}' and mark LIKE '%{mark}%' GROUP BY audio, account_id) AS subquery;"""
        count = MysqlHelper.get_values(count, "prod")
        if count == None:
            count = 0
        count = str(count).replace('(', '').replace(')', '').replace(',', '')
        return int(count)

    """
    获取跟随脚本站外已入库数量
    """
    @classmethod
    def get_link_zw_count(cls, mark, platform):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        count = f"""SELECT COUNT(*) AS total_count FROM ( SELECT audio, account_id FROM agc_video_deposit WHERE time = '{formatted_time}' and mark = '{mark}' GROUP BY audio, account_id) AS subquery;"""
        count = MysqlHelper.get_values(count, "prod")
        if count == None:
            count = 0
        count = str(count).replace('(', '').replace(')', '').replace(',', '')
        return int(count)

    """
    获取跟随脚本站内已入库数量
    """
    @classmethod
    def get_link_zn_count(cls, mark, platform):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        count = f"""SELECT COUNT(*) AS total_count FROM ( SELECT audio, account_id FROM agc_video_deposit WHERE time = '{formatted_time}' AND platform = '{platform}' and mark = '{mark}' GROUP BY audio, account_id) AS subquery;"""
        count = MysqlHelper.get_values(count, "prod")
        if count == None:
            count = 0
        count = str(count).replace('(', '').replace(')', '').replace(',', '')
        return int(count)

    """
    已使用视频链接存表
    """
    @classmethod
    def insert_videoAudio(cls, video_files, uid, platform, mark):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        for j in video_files:
            insert_sql = f"""INSERT INTO agc_video_deposit (audio, video_id, account_id, oss_object_key, time, platform, mark) values ('{uid}', '{j[0]}', '{j[1]}', '{j[2]}', '{formatted_time}', '{platform}', '{mark}')"""
            MysqlHelper.update_values(
                sql=insert_sql,
                env="prod",
                machine="",
            )