from pot_libs.mysql_util.mysql_util import MysqlUtil from unify_api.modules.elec_charge.components.elec_statistics_cps import SlotValue from unify_api.constants import SLOTS from unify_api.utils import time_format async def query_dust_fogcan_run_state(pids, now_ymd): """根据pids查询最新雾炮结果""" async with MysqlUtil() as conn: sql = "select * from dust_fogcan_run_state where pid in %s " \ "and quarter_time >= %s order by pid, quarter_time" dust_list = await conn.fetchall(sql, args=(tuple(pids), now_ymd)) return dust_list async def query_dust_water_run_state(water_id, now_ymd): """根据pids查询最新雾炮结果""" async with MysqlUtil() as conn: sql = "select * from dust_water_run_state where water_id in %s " \ "and quarter_time >= %s order by water_id, quarter_time" water_list = await conn.fetchall(sql, args=(tuple(water_id), now_ymd)) return water_list async def dust_water_run_state_by_time(start, end, water_id): """根据pids查询最新雾炮结果""" async with MysqlUtil() as conn: sql = "select * from dust_water_run_state where water_id = %s " \ "and quarter_time>=%s and quarter_time<=%s" water_list = await conn.fetchall(sql, args=(water_id, start, end)) return water_list async def dust_water_run_day_sum_water(day_str=None): """总用水""" sql = "SELECT sum(water_total) from dust_water_run_day" args = None if day_str: sql = "SELECT sum(water_total) from dust_water_run_day " \ "where cal_day = %s" args = (day_str,) async with MysqlUtil() as conn: sum_water = await conn.fetchone(sql, args=args) return sum_water async def dust_fogcan_run_day_sum_kwh(day_str=None): """总用电""" sql = "SELECT sum(kwh_total) from dust_fogcan_run_day" args = None if day_str: sql = "SELECT sum(kwh_total) from dust_fogcan_run_day " \ "where cal_day = %s" args = (day_str,) async with MysqlUtil() as conn: sum_kwh = await conn.fetchone(sql, args=args) return sum_kwh async def sum_water_runts_group(start, end): """根据water_id分组聚合, 求总用水和运行时间""" async with MysqlUtil() as conn: sql = "SELECT water_id, SUM(water_total), SUM(run_ts) " \ "from dust_water_run_day where cal_day >= %s and " \ "cal_day <= %s group by water_id" sum_info = await conn.fetchall(sql, args=(start, end)) return sum_info async def sum_kwh_runts_group(start, end): """根据pid分组聚合, 求总用电和运行时间""" async with MysqlUtil() as conn: sql = "SELECT pid, SUM(kwh_total), SUM(run_ts) " \ "from dust_fogcan_run_day where cal_day >= %s and " \ "cal_day <= %s group by pid" sum_info = await conn.fetchall(sql, args=(start, end)) return sum_info async def sum_water_group(start, end, date_type): async with MysqlUtil() as conn: if date_type == "day": sql = "SELECT DATE_FORMAT(quarter_time, '%%Y-%%m-%%d %%H:00:00')" \ " AS time, SUM(qr_water_diff) AS water " \ "FROM dust_water_run_state " \ "WHERE quarter_time BETWEEN %s and %s " \ "GROUP BY time ORDER BY time" elif date_type == "month": sql = """SELECT cal_day, SUM(water_total) as water FROM `dust_water_run_day` where cal_day BETWEEN %s and %s GROUP BY cal_day ORDER BY cal_day""" sum_info = await conn.fetchall(sql, args=(start, end)) return sum_info