tsp_map_dao.py 3.83 KB
Newer Older
lcn's avatar
lcn committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
from pot_libs.mysql_util.mysql_util import MysqlUtil
from pot_libs.logger import log


async def get_predict_data_day_dao(tsp_id, start, end):
    sql = "SELECT tsp_id,quarter_time,pm10,pm25,tsp " \
          "FROM `tsp_15min_forecast` where tsp_id=%s and " \
          "quarter_time >= %s and quarter_time < %s"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(tsp_id, start, end))
    return datas


async def get_predict_data_month_dao(tsp_id, start, end):
    sql = f"SELECT  DATE_FORMAT(quarter_time, '%Y-%m-%d') quarter_time, " \
          f"AVG(pm10) pm10,AVG(pm25) pm25,AVG(tsp) tsp " \
          f"FROM `tsp_15min_forecast` where tsp_id={tsp_id} and " \
          f"quarter_time >= '{start}' and quarter_time < '{end}'  " \
          "GROUP BY DATE_FORMAT(quarter_time, '%Y-%m-%d')"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    return datas


async def get_contrast_data_day_dao(tsp_id, start, end):
    sql = "SELECT tsp_id,quarter_time,pm10,pm25 " \
          "FROM `tsp_15min_contrast` where tsp_id=%s and " \
          "quarter_time >= %s and quarter_time < %s"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql, args=(tsp_id, start, end))
    return datas


async def get_contrast_data_month_dao(tsp_id, start, end):
    sql = f"SELECT  DATE_FORMAT(quarter_time, '%Y-%m-%d') quarter_time, " \
          f"AVG(pm10) pm10,AVG(pm25) pm25 " \
          f"FROM `tsp_15min_forecast` where tsp_id={tsp_id} and " \
          f"quarter_time >= '{start}' and quarter_time < '{end}'  " \
          "GROUP BY DATE_FORMAT(quarter_time, '%Y-%m-%d')"
    async with MysqlUtil() as conn:
        datas = await conn.fetchall(sql)
    return datas


async def get_index_data_dao():
    sql = "SELECT DISTINCT c.cid, c.shortname, c.fullname, c.industry, " \
wang.wenrong's avatar
wang.wenrong committed
47
          "c.province FROM tsp t LEFT JOIN company c on t.cid=c.cid"
lcn's avatar
lcn committed
48 49 50 51 52 53 54 55 56 57 58 59 60 61
    async with MysqlUtil() as conn:
        companys = await conn.fetchall(sql)
    return companys


async def get_position_dao(company_list):
    sql = "SELECT cid, value FROM `company_extend` where " \
          "`key`= 'map_center_yangchen' and cid in %s;"
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql, args=(company_list,))
    return data


async def get_tsp_id_list_dao(company_list):
wang.wenrong's avatar
wang.wenrong committed
62 63
    sql = "select cid cid,group_concat(tsp_id) tsp_ids  from tsp " \
          "WHERE cid in %s GROUP BY cid"
lcn's avatar
lcn committed
64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql, args=(company_list,))
    return data


async def get_month_rank_dao(cids, start, end):
    sql = f"SELECT cid, SUM(is_valid) effect, count(id) measures " \
          f"FROM `dust_1hour_measure` where cid in %s and " \
          f"start_datetime >= %s and start_datetime < %s GROUP BY cid"
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql, args=(cids, start, end))
    return data


async def get_cid_tsp_dao(cid, start, end, measure_type):
    sql = f"SELECT SUM(is_valid) effect, count(id) measures " \
          f"FROM `dust_1hour_measure` where cid = %s and " \
          f"start_datetime >= %s and start_datetime < %s and measure_type=%s"
    async with MysqlUtil() as conn:
        data = await conn.fetchone(sql, args=(cid, start, end, measure_type))
    return data


async def get_page_data(cid, start, end, page_num, page_size, measure_type):
    sql = "SELECT cid, start_datetime, end_datetime, measure_msg, is_valid, " \
          "effect FROM `dust_1hour_measure` where cid=%s and " \
          "measure_type=%s and start_datetime>=%s and start_datetime<=%s " \
          "ORDER BY start_datetime desc limit %s, %s"
    async with MysqlUtil() as conn:
        data = await conn.fetchall(sql,
                                   args=(cid, measure_type, start, end,
                                         (page_num-1)*page_size, page_size))
    return data