exporter-ex/utils.py

import os

import json
from aliyunsdkcore.client import AcsClient
from aliyunsdkecs.request.v20140526.DescribeInstancesRequest import DescribeInstancesRequest
from model import InstanceList,IntfaceList
# from DBSession import session_maker
from model_longvideo import produce_video_task
from DBSession_longvideo import session_maker_longvideo
from aliyun.log.logclient import LogClient
from aliyun.log.getlogsrequest import GetLogsRequest
import  requests
import  time
import  datetime
import  pymysql
from sqlalchemy.sql import func
from model_longvideo import produce_video_project
from datetime import date, timedelta
from DBSession import  session_maker
import logging
from flask import current_app



client = AcsClient('LTAI4GBWbFvvXoXsSVBe1o9f', 'kRAikWitb4kDxaAyBqNrmLmllMEDO3', 'cn-hangzhou')
from prometheus_client import Gauge,Counter, generate_latest



def intances_list_update():

    request = DescribeInstancesRequest()
    request.set_accept_format('json')
    request.set_PageSize(100)
    request.set_InstanceNetworkType("vpc")
    request.set_Tags([
        {
            "Key": "ecs"
        }
    ])

    response = client.do_action_with_exception(request)
    instance_info = json.loads(response)
    intances_list_del()
    print(len(instance_info["Instances"]["Instance"]))
    for i in range(len(instance_info["Instances"]["Instance"])):
        instance_id = instance_info["Instances"]["Instance"][i]["InstanceId"]
        ipaddr = instance_info["Instances"]["Instance"][i]["VpcAttributes"]["PrivateIpAddress"]["IpAddress"][0]
        server_name = instance_info["Instances"]["Instance"][i]["Tags"]["Tag"][0]["TagValue"]
        status = instance_info["Instances"]["Instance"][i]["Status"]
        instance_name = instance_info["Instances"]["Instance"][i]["HostName"]
        if status == "Running":
            status = 1
            instance_insert(instance_id, ipaddr, instance_name, server_name, status)


    return "OK"


def intances_list_del():
    with session_maker() as session:
        session.query(InstanceList).delete()

def instance_insert(instance_id, ipadd, instance_name, server_name, status):
    InstanceInfo = None
    InstanceInfo = InstanceList()
    with session_maker() as session:
        InstanceInfo.instance_id = instance_id
        InstanceInfo.ipadd = ipadd
        InstanceInfo.instance_name = instance_name
        InstanceInfo.server_name = server_name
        InstanceInfo.status = status
        session.add(InstanceInfo)





def healthcheck(ipaddr,server_name):
    """返回容器upload时间，容器运行状态，健康坚持接口返回值，"""
    svc_name = server_name
    if svc_name == "longvideoapi.prod" :
        url = "http://{}:8080/longvideoapi/test".format(ipaddr)
        res = requests.post(url, timeout=5)
        print(res.status_code)
        return res.status_code
    elif svc_name == "commonapi.prod":
        url = "http://{}:8080/commonapi/test".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code
    elif svc_name == "speed.prod":
        url = "http://{}:8080/longvideoapi/test".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code
    elif svc_name == "clipapi.prod":
        url = "http://{}:8080/longvideoapi/test".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code
    elif svc_name == "distribution.prod":
        url = "http://{}:8080/healthcheck".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code
    elif svc_name == "manager.prod":
        url = "http://{}:8080/manager/healthcheck".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code
    elif svc_name == "recommend-queue.prod":
        url = "http://{}:8080/video-recommend-queue/healthcheck".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code


    elif svc_name == "recommend-rov.prod":
        url = "http://{}:8080/healthcheck".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code

    elif svc_name == "measure-queue.prod":
        url = "http://{}:8080/video-measure-queue/healthcheck".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code

    elif svc_name == "message-queue.prod":
        url = "http://{}:8080/video-message-queue/healthcheck".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code

    elif svc_name == "search-material.prod":
        url = "http://{}:8089/v1/search/test".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code

    elif svc_name == "spider.prod":
        url = "http://{}:9990/test/version".format(ipaddr)
        try:
            res = requests.post(url, timeout=5)
        except Exception as e:
            return  -1
        return res.status_code

    elif svc_name == "video-job.prod":
        url = "http://{}:8080/video-job/healthcheck".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code


    elif svc_name == "combine-job.prod":
        url = "http://{}:8080/video-combine-job/healthcheck".format(ipaddr)
        res = requests.post(url, timeout=5)
        return res.status_code

    elif svc_name == "recommend-server.prod":
        url = "http://{}:8080/healthcheck".format(ipaddr)
        # res = requests.post(url, timeout=5)
        return 200

    else:
        return 200

# def  healthcheck_metirc(instance_id,ipaddr,server_name,http_code):
#     healthcheck_status = Gauge("healthcheck_status", "http_code", ['instance_id', 'server_name', 'ipaddress'],
#                                registry=registry)
#
#     healthcheck_status.labels(instance_id, server_name, ipaddr).set(http_code)
#
#     return Response(generate_latest(registry),mimetype="text/plain")



def count_qps(appType, url):
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    accessKeyId = 'LTAIWYUujJAm7CbH'
    accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P'
    logstore = 'request-log'
    client = LogClient(endpoint, accessKeyId, accessKey)
    project = 'wqsd-longvideoapi'
    topic = ""
    start_time = int(time.time())
    end_time = start_time - 60
    req = GetLogsRequest(project, logstore, end_time, start_time, topic,
                         "params.appType={} and requestUri= {} |select requestUri,count(1) as cnt, date_format(logTimestamp/1000,'%Y-%m-%d %H:%i:%S') as date  group by requestUri, date order by cnt desc limit 1".format(appType, url)
                         , 30, 0, False)
    try:
        res = client.get_logs(req)
    except :
        qps = 0
    else:
        if res.body:
            qps = res.body[0]["cnt"]
        else:
            qps = 0
    print(int(time.time())-start_time)
    current_app.logger.info(qps)
    return  qps

def  count_avg_time(appType ,url):
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    accessKeyId = 'LTAIWYUujJAm7CbH'
    accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P'
    logstore = 'request-log'
    client = LogClient(endpoint, accessKeyId, accessKey)
    project = 'wqsd-longvideoapi'
    topic = ""
    start_time = int(time.time())
    end_time = start_time - 60
    # url = '/longvideoapi/user/idolsUpdated'
    req = GetLogsRequest(project, logstore, end_time, start_time, topic,
                         "params.appType=%s and requestUri=%s |select requestUri,round(avg(expendTime)) as  avgs,count(1) as cnt group by requestUri " %(appType, url), 30, 0, False)
    try:
        res = client.get_logs(req)
    except Exception as e:
        avgs = 0
    else:
        if res.body:
            avgs = res.body[0]["avgs"]
        else:
            avgs = 0
    return  avgs


def  slb_status_code_count(server_name):
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    accessKeyId = 'LTAIWYUujJAm7CbH'
    accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P'

    client = LogClient(endpoint, accessKeyId, accessKey)
    if server_name == "longvideoapi":
        project = 'longvideoapi-slb-log'
        logstore = 'slb-access-log'
    if server_name == "speed":
        project = 'speed-slb-log'
        logstore = 'access-log'
    if server_name == "clip":
        project = 'clips-slb-log'
        logstore = 'access-log'
    if server_name == "commonapi":
        project = 'commonapi-slb-log'
        logstore = 'slb-access-log'

    topic = ""
    end_time = int(time.time())
    start_time = end_time - (end_time - time.timezone)%86400
    # url = '/longvideoapi/user/idolsUpdated'
    req = GetLogsRequest(project, logstore, start_time, end_time,  topic,
                         "* |SELECT  status ,COUNT(*) as  cnt   group by status" , 30, 0, False)
    try:
        res = client.get_logs(req)
    except Exception as e:
        avgs = 0
    else:
        if res.body:
            return  res.body


def produce_video_task_status_count():
    with session_maker_longvideo() as  session:
        video_progress_count = session.query(produce_video_task).filter(produce_video_task.task_status == 1).count()
        video_success_count = session.query(produce_video_task).filter(produce_video_task.task_status == 2).count()
        video_fail_count = session.query(produce_video_task).filter(produce_video_task.task_status == 3).count()
        end_time = int(time.time())
        start_time = end_time - (end_time - time.timezone) % 86400
        res = session.query(produce_video_task).filter(produce_video_task.task_status==2).order_by(produce_video_task.id.desc()).first()

        duration = res.duration
        submit_timestamp = res.submit_timestamp
        complete_timestamp = res.complete_timestamp
        print(res.id,duration,submit_timestamp,complete_timestamp)
        rate = (duration / (complete_timestamp - submit_timestamp) / 1000)



def logs_tts_count(tts_channel, tts_status):
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    accessKeyId = 'LTAIWYUujJAm7CbH'
    accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P'
    client = LogClient(endpoint, accessKeyId, accessKey)
    project = 'wqsd-monitor'
    logstore = 'monitor-log'

    topic = ""
    end_time = int(time.time())
    start_time = end_time - (end_time - time.timezone)%86400
    req = GetLogsRequest(project, logstore, start_time, end_time,  topic,
                         "* and __topic__: producetts and channel:{} and  produceStatus:{}| select channel,produceStatus,count(*) as count group by channel,produceStatus".format(tts_channel, tts_status) , 30, 0, False)
    try:
        res = client.get_logs(req)
    except Exception as e:
        avgs = 0
    else:
        if res.body:
            return  res.body
def logs_tts_ratio(tts_channel, tts_status):
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    accessKeyId = 'LTAIWYUujJAm7CbH'
    accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P'
    client = LogClient(endpoint, accessKeyId, accessKey)
    project = 'wqsd-monitor'
    logstore = 'monitor-log'

    topic = ""
    end_time = int(time.time())
    start_time = end_time - 300
    print(start_time, end_time)
    req = GetLogsRequest(project, logstore, start_time, end_time,  topic,
                         "* and __topic__: producetts and channel:{} and  produceStatus:{}| select channel,produceStatus,count(*) as count group by channel,produceStatus".format(tts_channel, tts_status) , 30, 0, False)
    try:
        res = client.get_logs(req)
    except Exception as e:
        avgs = 0
    else:
        if res.body:
            return  res.body


def  count_recommend_null():
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    accessKeyId = 'LTAIWYUujJAm7CbH'
    accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P'
    logstore = 'app-recommend-log'
    client = LogClient(endpoint, accessKeyId, accessKey)
    project = 'wqsd-longvideoapi'
    topic = ""
    start_time = int(time.time())
    #监控获取时间间隔300s
    end_time = start_time - 300
    query_sql = "* and requestUri:'/longvideoapi/video/distribute/category/videoList/v2' | select count(DISTINCT json_extract(params, '$.machineCode')) as cnt where json_array_length(json_extract(result, '$.data')) = 0"
    req = GetLogsRequest(project, logstore, end_time, start_time, topic, query_sql, 30, 0, False)
    try:
        res = client.get_logs(req)
    except Exception as e:
        print(e)
        avgs = 0
    else:
        if res.body:
            cnt_300 = res.body[0]["cnt"]
        else:
            cnt_300 = 0
    return  cnt_300

def  db_query(sql):
    HOST = 'rr-bp1x9785e8h5452bi157.mysql.rds.aliyuncs.com'
    PORT = '3306'
    DATABASE = 'longvideo'
    USERNAME = 'devops'
    PASSWORD = 'devops@123456'
    conn = pymysql.connect(host=HOST, user=USERNAME, password=PASSWORD, database=DATABASE, charset="utf8")
    cursor = conn.cursor()
    cursor.execute(sql)
    res = cursor.fetchone()

    return  res

def db_query_devlop(sql):
    HOST = 'rm-bp1k5853td1r25g3n690.mysql.rds.aliyuncs.com'
    PORT = '3306'
    DATABASE = 'devops'
    USERNAME = 'devops'
    PASSWORD = 'devops@123456'
    conn = pymysql.connect(host=HOST, user=USERNAME, password=PASSWORD, database=DATABASE, charset="utf8")
    cursor = conn.cursor()
    cursor.execute(sql)
    res = cursor.fetchall()

    return  res
def  db_query_prod(sql):
    HOST = 'rr-bp1x9785e8h5452bi157.mysql.rds.aliyuncs.com'
    PORT = '3306'
    DATABASE = 'longvideo'
    USERNAME = 'devops'
    PASSWORD = 'devops@123456'
    conn = pymysql.connect(host=HOST, user=USERNAME, password=PASSWORD, database=DATABASE, charset="utf8")
    cursor = conn.cursor()
    cursor.execute(sql)
    res = cursor.fetchone()

    return  res

def  count_qps_avgtime(appType):
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    accessKeyId = 'LTAIWYUujJAm7CbH'
    accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P'
    logstore = 'request-log'
    client = LogClient(endpoint, accessKeyId, accessKey)
    project = 'wqsd-longvideoapi'
    topic = ""
    start_time = int(time.time())
    end_time = start_time - 60
    # url = '/longvideoapi/user/idolsUpdated'
    req = GetLogsRequest(project, logstore, end_time, start_time, topic,
                         "params.appType=%s | select requestUri,count(1) as cnt  , avg(expendTime)   as avg_time  group by requestUri  order by requestUri,cnt desc limit  1000" %(appType), 30, 0, False)
    try:
        res = client.get_logs(req)
    except Exception as e:
        avgs = 0

    return  res

def  count_avg_max(appType):
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    accessKeyId = 'LTAIWYUujJAm7CbH'
    accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P'
    logstore = 'request-log'
    client = LogClient(endpoint, accessKeyId, accessKey)
    project = 'wqsd-longvideoapi'
    topic = ""
    start_time = int(time.time())
    end_time = start_time - (60*15)
    # url = '/longvideoapi/user/idolsUpdated'
    req = GetLogsRequest(project, logstore, end_time, start_time, topic,
                         "params.appType=%s | select requestUri,count(1) as cnt  , max(expendTime)  as max_time ,round(avg(expendTime),2) as avg_time group by requestUri  order by requestUri,cnt desc limit  1000" %(appType), 30, 0, False)
    try:
        res = client.get_logs(req)
    except Exception as e:
        avgs = 0

    return  res


def  count_rt_less_time_count(appType, than, less):
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    accessKeyId = 'LTAIWYUujJAm7CbH'
    accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P'
    logstore = 'request-log'
    client = LogClient(endpoint, accessKeyId, accessKey)
    project = 'wqsd-longvideoapi'
    topic = ""
    start_time = int(time.time())
    end_time = start_time - 60
    # url = '/longvideoapi/user/idolsUpdated'
    req = GetLogsRequest(project, logstore, end_time, start_time, topic,
                         "params.appType=%s and expendtime >%s and  expendtime <%s| select requestUri,count(1) as cnt group by requestUri  order by requestUri,cnt desc limit  1000" %(appType,than,less), 30, 0, False)
    try:
        res = client.get_logs(req)
    except Exception as e:
        print(e)
    return  res

def  error_cnt(appType):
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    accessKeyId = 'LTAIWYUujJAm7CbH'
    accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P'
    logstore = 'request-log'
    client = LogClient(endpoint, accessKeyId, accessKey)
    project = 'wqsd-longvideoapi'
    topic = ""
    start_time = int(time.time())
    end_time = start_time - 60
    req = GetLogsRequest(project, logstore, end_time, start_time, topic,
                         "params.appType=%s and (resultCode:-111 or resultCode:-990  or resultCode:-994 or resultCode:-666 or resultCode:-222)| SELECT COUNT(*) as cnt, resultCode group by  resultCode" %(appType), 30, 0, False)
    try:
        res = client.get_logs(req)
    except Exception as e:
        print(e)

    return  res

def app_openapi_qps_avgtime_count():
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    accessKeyId = 'LTAIWYUujJAm7CbH'
    accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P'
    logstore = 'request-log'
    client = LogClient(endpoint, accessKeyId, accessKey)
    project = 'wqsd-longvideoapi'
    topic = ""
    start_time = int(time.time())
    end_time = start_time - 60
    url = '/longvideoapi/openapi/video/distribute/category/videoList/v2'
    req = GetLogsRequest(project, logstore, end_time, start_time, topic,
                         "requestUri=%s| select requestUri,count(1) as cnt  , avg(expendTime)  as avg_time group by requestUri  order by requestUri,cnt desc limit  1000"%(url) , 30, 0, False)
    try:
        res = client.get_logs(req)
    except Exception as e:
        avgs = 0

    return  res,url




def produce_video_data():
    start_time = (int(time.strftime("%Y%m%d", time.localtime()))) * 1000000000000000

    end_time = (int(time.strftime("%Y%m%d", time.localtime())) + 1) * 1000000000000000
    # start_time = (int(datetime.strftime("%Y%m%d",datetime.date.today()))
    print(start_time, end_time)
    sum_sql = ("select count(*) as totalCount "              
              "from produce_video_project t1 "
              " where t1.project_id > %s and t1.project_id < %s and t1.app_type not in (1,13,15) "% (start_time,end_time)
               )
    res = db_query(sum_sql)

    return

def  update_request_url_list():
    HOST = 'rm-bp1k5853td1r25g3n690.mysql.rds.aliyuncs.com'
    PORT = '3306'
    DATABASE = 'devops'
    USERNAME = 'devops'
    PASSWORD = 'devops@123456'
    conn = pymysql.connect(host=HOST, user=USERNAME, password=PASSWORD, database=DATABASE, charset="utf8")
    cursor = conn.cursor()
    current_app.logger.info("定时任务开始5m")
    appType = ['0', '4', '5', '6', '12', '15']
    for index in range(len(appType)):
        type = appType[index]
        res = count_avg_max(type)
        for i in range(len(res.body)):
            url = res.body[i]["requestUri"]
            cnt = res.body[i]["cnt"]
            max_time = res.body[i]["max_time"]
            avg_time = res.body[i]["avg_time"]
            sql = "UPDATE `reuqest_url_list`  set `qps`=%s, `max_time`=%s, `avg_time`=%s WHERE `request_url` ='%s' and `apptype`=%s" % (
            cnt, max_time, avg_time, url, type)
            cursor.execute(sql)
            conn.commit()
        for i in range(len(res.body)):
            url = res.body[i]["requestUri"]
            cnt = res.body[i]["cnt"]
            sql = "UPDATE `reuqest_url_list`  set `rt_1`=%s WHERE `request_url` ='%s' and `apptype`=%s" % (
            cnt, url, type)
            cursor.execute(sql)
            conn.commit()

        res = count_rt_less_time_count(type, 200, 500)
        for i in range(len(res.body)):
            url = res.body[i]["requestUri"]
            cnt = res.body[i]["cnt"]
            sql = "UPDATE `reuqest_url_list`  set `rt_2`=%s WHERE `request_url` ='%s' and `apptype`=%s" % (
            cnt, url, type)
            cursor.execute(sql)
            conn.commit()

        res = count_rt_less_time_count(type, 500, 1000)
        for i in range(len(res.body)):
            url = res.body[i]["requestUri"]
            cnt = res.body[i]["cnt"]
            sql = "UPDATE `reuqest_url_list`  set `rt_3`=%s WHERE `request_url` ='%s' and `apptype`=%s" % (
            cnt, url, type)
            cursor.execute(sql)
            conn.commit()

        res = count_rt_less_time_count(type, 1000, 10000)
        for i in range(len(res.body)):
            url = res.body[i]["requestUri"]
            cnt = res.body[i]["cnt"]
            sql = "UPDATE `reuqest_url_list`  set `rt_4`=%s WHERE `request_url` ='%s' and `apptype`=%s" % (
            cnt, url, type)
            cursor.execute(sql)
            conn.commit()


def  produce_video_task_cnt():
    start_time = int(time.strftime("%Y%m%d", time.localtime())) * 1000000000000000
    end_time = int(time.strftime("%Y%m%d%H%M", time.localtime())) * 100000000000
    query_sql = ("select count(*) as totalCount,"
                 "sum(case when produce_status in (5,6,7,8) then 1 else 0 end) as successCount,"
                 "sum(case when produce_status = 99 then 1 else 0 end) as failCount , "
                 "sum(case when produce_status in(0,1,2,3,4) then 1 else 0 end) as processingCount,"
                 "sum(case when produce_status in(5,6,7,8) and (rate < 0.5 or rate is null) then 1 else 0 end) processingCount1,"
                 "sum(case when produce_status in(5,6,7,8) and rate >= 0.5 and  rate < 0.7 then 1 else 0 end) processingCount2  ,"
                 "sum(case when produce_status in(5,6,7,8) and rate >= 0.7 and  rate < 1 then 1 else 0 end) processingCount3 ,"
                 "sum(case when produce_status in(5,6,7,8) and rate >= 1 and  rate < 1.5 then 1 else 0 end) processingCount4  ,"
                 "sum(case when produce_status in(5,6,7,8) and rate >= 1.5 and  rate < 2 then 2 else 0 end) processingCount5 ,"
                 "sum(case when produce_status in(5,6,7,8) and  rate >2  then 1 else 0 end) processingCount6  from  "
                 "(select t1.project_id, t1.produce_status, round((t2.last_connect_timestamp - t1.submit_timestamp) / (t1.video_duration/1000), 1) as rate  from produce_video_project t1  "
                 "left join produce_video_project_connect_time t2 on t1.project_id = t2.project_id "
                 "where t1.project_id > %s and t1.project_id < %s and t1.app_type not in (1,13,15)) s1 " %(start_time ,end_time)

                )
    res = db_query(query_sql)

    return  res



def  produce_video_ratio_cnt():
    HOST = 'rm-bp1k5853td1r25g3n690.mysql.rds.aliyuncs.com'
    PORT = '3306'
    DATABASE = 'devops'
    USERNAME = 'devops'
    PASSWORD = 'devops@123456'
    conn = pymysql.connect(host=HOST, user=USERNAME, password=PASSWORD, database=DATABASE, charset="utf8")
    cursor = conn.cursor()
    start_time = int((date.today() + timedelta(days=-1)).strftime("%Y%m%d")) * 1000000000000000
    # end_time = int(date.today().strftime("%Y%m%d")) * 1000000000000000
    end_time =   int((date.today() + timedelta(days=0)).strftime("%Y%m%d")) * 1000000000000000

    sql = ("select count(*) as totalCount, "
            "sum(case when rate < 0.5 then 1 else 0 end) as r1,"
            "sum(case when (rate >= 0.5 and rate < 0.7) then 1 else 0 end) as r2,"
            "sum(case when (rate >= 0.7 and rate < 1) then 1 else 0 end) as r3,"
            "sum(case when (rate >= 1 and rate < 1.5) then 1 else 0 end) as r4,"
            "sum(case when (rate >= 1.5 and rate < 2) then 1 else 0 end) as r5,"
            "sum(case when rate >= 2 then 1 else 0 end) as r6 from "
            "(select project_id, round( (produce_done_timestamp - submit_timestamp) / (video_duration/1000),1) as rate from "
            "(select project_id, draftbox_id,video_duration,submit_timestamp,produce_done_timestamp from "
            "(select project_id, draftbox_id,video_duration,submit_timestamp,produce_done_timestamp from "
            "produce_video_project where project_id > %s and project_id < %s and app_type not in (1,13,15) and produce_status in (5,6,7,8) order by video_duration desc limit 100000) t1 group by draftbox_id) t2) t3"%(start_time, end_time)

    )
    y_date = (date.today() + timedelta(days=-1)).strftime("%Y%m%d")
    res = db_query(sql)
    if res[0]:
        cnt = res[0]
    else:
        cnt = 0
    if res[1]:
        r1 = res[1]
    else:
        r1 = 0
    if res[2]:
        r2 = res[2]
    else:
        r2 = 0
    if res[3]:
        r3 = res[3]
    else:
        r3 = 0
    if res[4]:
        r4 = res[4]
    else:
        r4 = 0
    if res[5]:
        r5 = res[5]
    else:
        r5 = 0
    if res[6]:
        r6 = res[6]
    else:
        r6 = 0
    sql = ("insert into produce_video_ratio (`cnt`,`r1`,`r2`,`r3`,`r4`,`r5`,`r6`,`date`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"%(cnt, r1, r2, r3, r4, r5, r6, y_date)

    )
    cursor.execute(sql)
    conn.commit()

    return  res


def  create_log():
    LOG_PATH = os.path.abspath(os.path.join(os.path.join(basedir, os.path.pardir), 'logs'))
    # 创建handler
    handler = RotatingFileHandler(os.path.join(LOG_PATH, 'flask_demo.log'), maxBytes=100 * 1024 * 1024, backupCount=10,
                                  encoding="utf-8")
    # 设置handler日志级别
    handler.setLevel(logging.INFO)
    # 设置handler打印格式
    fmt_str = logging.Formatter(
        '时间:%(asctime)-15s 日志级别:%(levelname)s 文件名:%(filename)s 行数:%(lineno)d 进程号:%(process)d 当前线程号:%(thread)s 日志信息:%(message)s')
    handler.setFormatter(fmt_str)
# if __name__ == '__main__':
#     res = logs_tts_ratio("aliyun",1)
#     print(res)