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, " \ "c.province FROM tsp t LEFT JOIN company c on t.cid=c.cid" 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): sql = "select cid cid,group_concat(tsp_id) tsp_ids from tsp " \ "WHERE cid in %s GROUP BY cid" 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