async def get_data():
db = Database(META_URL)
await db.connect()
res = await db.fetch_all(query2sql(query))
modelName = await db.fetch_one(query2sql(model_name_query))
await db.disconnect()
return res, modelName
async def get_info(session: Session, conn: Database, id: int):
pre_query_res = await conn.fetch_one(
query2sql(session.query(Asset.asset_type).filter(Asset.id == id))
)
model = info_models_mapper[
enum_mapper[pre_query_res["asset_type"]]
] # int -> asset_type -> model class
query = session.query(model).filter(model.asset_id == id)
return await conn.fetch_one(query2sql(query))
async def get_tree(conn: Database,
session: Session = session_make(engine=None)):
assets = await conn.fetch_all(
query2sql(
session.query(Asset.id, Asset.name,
Asset.station_id.label("parent_id")).filter(
Asset.asset_level == 0)))
stations = await conn.fetch_all(
query2sql(
session.query(Station.id, Station.name,
Station.bc_id.label("parent_id"))))
bcs = await conn.fetch_all(
query2sql(
session.query(
BranchCompany.id,
BranchCompany.name,
BranchCompany.rc_id.label("parent_id"),
)))
rcs = await conn.fetch_all(
query2sql(session.query(RegionCompany.id, RegionCompany.name)))
tree = Tree()
tree.create_node(tag="root", identifier="root")
def item_maker(item, parent_type, self_type, color, value):
temp = dict(item)
if parent_type:
temp["parent_id"] = parent_type + str(temp["parent_id"])
if self_type == "asset":
temp["value"] = 1
temp["id"] = self_type + str(temp["id"])
temp["itemStyle"] = {"color": color}
temp["am_value"] = value
return temp
color = ["#1a8bff", "#51a2f7", "#79b8ff", "#93ccff"]
assets = [item_maker(row, "st", "asset", color[3], 40) for row in assets]
stations = [item_maker(row, "bc", "st", color[2], 60) for row in stations]
bcs = [item_maker(row, "rc", "bc", color[1], 80) for row in bcs]
rcs = [item_maker(row, None, "rc", color[0], 130) for row in rcs]
for item in rcs + bcs + stations + assets:
tree.create_node(
data=item,
identifier=item["id"],
parent=item["parent_id"] if "parent_id" in item else "root",
)
return tree.to_dict(with_data=True)["children"]
async def get_multi(conn: Database,
skip: int,
limit: int,
session: Session = session_make(engine=None)):
query = session.query(Pipeline).order_by(
Pipeline.id).offset(skip).limit(limit)
return await conn.fetch_all(query2sql(query))
async def get_cards(
conn: Database, skip: int, limit: int, session: Session = session_make(engine=None),
):
query = (
session.query(
Asset.id,
Asset.name,
Asset.sn,
Asset.st_time,
Asset.health_indicator,
Asset.statu,
Asset.repairs,
Asset.mp_configuration,
Station.name.label("station_name"),
PumpUnit.is_domestic,
PumpUnit.oil_type,
PumpUnit.design_output,
)
.join(Station, Station.id == Asset.station_id)
.join(PumpUnit, PumpUnit.asset_id == Asset.id)
.order_by(Asset.id)
.filter(Asset.asset_type == 0)
.offset(skip)
.limit(limit)
)
return await conn.fetch_all(query2sql(query))
async def get_rc(conn: Database,
skip: int,
limit: int,
session: Session = session_make(engine=None)):
query = (session.query(RegionCompany).order_by(
RegionCompany.id).offset(skip).limit(limit))
return await conn.fetch_all(query2sql(query))
async def get_multi(
conn: Database,
session: Session = session_make(engine=None),
):
query = session.query(Threshold)
return await conn.fetch_all(query2sql(query))
async def get_detail_by_id(
conn: Database, id: int, session: Session = session_make(engine=None),
):
query = (
session.query(
Asset.id,
Asset.name,
Asset.sn,
Asset.lr_time,
Asset.cr_time,
Asset.md_time,
Asset.st_time,
Asset.memo,
Asset.health_indicator,
Asset.statu,
Asset.asset_type,
Asset.repairs,
Station.name.label("station_name"),
Pipeline.name.label("pipeline_name"),
BranchCompany.name.label("branch_name"),
RegionCompany.name.label("region_name"),
PumpUnit.is_domestic,
PumpUnit.oil_type,
PumpUnit.design_output,
)
.join(Station, Station.id == Asset.station_id)
.join(PumpUnit, PumpUnit.asset_id == Asset.id)
.join(Pipeline, Pipeline.id == PumpUnit.pipeline_id)
.join(BranchCompany, BranchCompany.id == Station.bc_id)
.join(RegionCompany, RegionCompany.id == Station.rc_id)
.filter(Asset.id == id)
)
return await conn.fetch_one(query2sql(query))
async def get_data_join_feature_by_id(
conn: Database,
data_model,
feature_model,
mp_id: int,
require_mp_type: int,
data_id: int,
data_fileds: tuple,
feature_fileds: tuple,
session: Session = session_make(engine=None),
):
data_model = get_shard_model(
data_model, mp_id=mp_id, require_mp_type=require_mp_type
)
feature_model = get_shard_model(
feature_model, mp_id=mp_id, require_mp_type=require_mp_type
)
feature_attr = []
for feature in feature_fileds:
feature_attr.append(getattr(feature_model, feature))
data_attr = []
for data in data_fileds:
data_attr.append(getattr(data_model, data))
query = (
session.query(*tuple(data_attr), *tuple(feature_attr))
.select_from(data_model)
.join(feature_model, feature_model.data_id == data_model.id)
.filter(data_model.id == data_id)
)
return await conn.fetch_one(query2sql(query))
async def get_statu_stat(
conn: Database, session: Session = session_make(engine=meta_engine)
):
query = (
session.query(MaintenanceRecord.statu, func.count("*"))
.select_from(MaintenanceRecord)
.group_by(MaintenanceRecord.statu)
)
res = await conn.fetch_all(query2sql(query))
return format_single_grouped_result(res=res, group_names=MaintenanceRecord.STATUS)
async def get_by_id(
conn: Database,
orm_model,
mp_id: int,
require_mp_type: int,
data_id: int,
session: Session = session_make(engine=None),
):
model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type)
query = session.query(model).filter(model.id == data_id)
return await conn.fetch_one(query2sql(query))
async def get(
conn: Database, id: int, session: Session = session_make(engine=meta_engine)
):
query = (
session.query(MaintenanceRecord, Asset.name.label("asset_name"))
.join(Asset, Asset.id == MaintenanceRecord.asset_id)
.filter(MaintenanceRecord.id == id)
)
res = await conn.fetch_one(query2sql(query))
return res
async def get_estimated_value_by_id(
conn: Database,
asset_id: int,
data_id: int,
session: Session = session_make(engine=None),
):
hi_model = AssetHI.model(point_id=asset_id)
query = session.query(hi_model.id,
hi_model.est).filter(hi_model.id == data_id)
res = await conn.fetch_one(query2sql(query))
dic = {"id": res["id"], "est": json.loads(res["est"])}
return dic
async def get_latest(
conn: Database,
orm_model,
mp_id: int,
require_mp_type: int,
session: Session = session_make(engine=None),
):
model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type)
query = (
session.query(model).order_by(model.id.desc()).limit(1)
) # query all the defined fields
return await conn.fetch_one(query2sql(query))
async def get_mp_mapper():
if len(measure_point_router) == 0:
async with Database(META_URL) as conn:
session = session_make(engine=None)
query = session.query(MeasurePoint)
res = await conn.fetch_all(query2sql(query))
for row in res:
measure_point_router[row["id"]] = {
"sid": row["station_id"],
"iid": row["inner_station_id"],
"type": row["type"],
}
return measure_point_router
async def get_similarity_threshold_recently(
conn: Database,
asset_id: int,
limit: int,
session: Session = session_make(engine=None),
):
hi_model = AssetHI.model(point_id=asset_id)
query = (session.query(hi_model.id, hi_model.time, hi_model.similarity,
hi_model.threshold).order_by(
hi_model.time.desc()).limit(limit))
res = await conn.fetch_all(query2sql(query))
res.reverse()
dic = multi_result_to_array(res)
return dic
async def get_multi(
conn: Database,
mp_id: int,
require_mp_type: int,
orm_model,
fileds: List[str],
time_before: str,
time_after: str,
limit: int,
with_estimated: bool = False,
session: Session = session_make(engine=None),
):
model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type)
query = session.query(model)
for filed in fileds + ["id", "time", "data_id"]:
query = query.options(load_only(filed))
if with_estimated:
for filed in fileds:
query = (
query.options(load_only("est_" + filed))
if filed != "similarity"
else query
)
if time_before != "None":
query = query.filter(model.time.between(str(time_after), str(time_before)))
query = query.order_by(model.time.desc())
if limit:
query = query.order_by(model.time.desc()).limit(limit)
res = await conn.fetch_all(query2sql(query))
res.reverse()
if len(res) == 0:
raise HTTPException(
status_code=400, detail="No signal collected between the time range"
)
dic = {}
keys = res[0].keys()
for row in res:
for key in keys:
if key == "time":
dic.setdefault(key, []).append(str(row[key]))
else:
dic.setdefault(key, []).append(row[key])
return dic
async def get_similarity_threshold_during_time(
conn: Database,
asset_id: int,
time_before: str,
time_after: str,
session: Session = session_make(engine=None),
):
hi_model = AssetHI.model(point_id=asset_id)
query = session.query(hi_model.id, hi_model.time, hi_model.similarity,
hi_model.threshold).filter(
hi_model.time.between(str(time_after),
str(time_before)))
res = await conn.fetch_all(query2sql(query))
dic = multi_result_to_array(res)
return dic
async def get_multi(
conn: Database,
orm_model,
mp_id: int,
require_mp_type: int,
time_before: datetime,
time_after: datetime,
session: Session = session_make(engine=None),
):
model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type)
query = (
session.query(model.id, model.time)
.filter(model.time.between(str(time_before), str(time_after)))
.order_by(model.id)
)
return await conn.fetch_all(query2sql(query))
async def get_multi(
conn: Database,
skip: int,
limit: int,
brief: bool,
session: Session = session_make(engine=None),
**kwargs
):
if brief:
query = session.query(
MeasurePoint.id,
MeasurePoint.name,
MeasurePoint.type,
MeasurePoint.health_indicator,
)
else:
query = session.query(
MeasurePoint.id,
MeasurePoint.name,
MeasurePoint.type,
MeasurePoint.md_time,
MeasurePoint.statu,
MeasurePoint.health_indicator,
MeasurePoint.sample_freq,
MeasurePoint.sample_interval,
Station.id.label("staion_id"),
Station.name.label("station_name"),
Asset.id.label("asset_id"),
Asset.name.label("asset_name"),
)
query = (
query.order_by(MeasurePoint.id.asc())
.offset(skip)
.limit(limit)
.join(Station, MeasurePoint.station_id == Station.id)
.join(Asset, MeasurePoint.asset_id == Asset.id)
)
if kwargs["station_id"]:
query = query.filter(MeasurePoint.station_id == kwargs["station_id"])
if kwargs["asset_id"]:
query = query.filter(MeasurePoint.asset_id == kwargs["asset_id"])
if kwargs["type"] is not None:
query = query.filter(MeasurePoint.type == kwargs["type"])
query = query.order_by(
MeasurePoint.name
) # important, relate to the order display in asset detail page.
return await conn.fetch_all(query2sql(query))
async def get_multi(
conn: Database,
skip: int,
limit: int,
type: int,
station_name: str,
level: int,
station_id: int,
session: Session = session_make(engine=None),
):
if level is None:
query = (
session.query(
Asset.id,
Asset.name,
Asset.sn,
Asset.lr_time,
Asset.cr_time,
Asset.md_time,
Asset.st_time,
Asset.asset_level,
Asset.memo,
Asset.health_indicator,
Asset.statu,
Asset.parent_id,
Asset.station_id,
Asset.repairs,
Station.name.label("station_name"),
)
.join(Station, Station.id == Asset.station_id)
.order_by(Asset.id)
.offset(skip)
.limit(limit)
)
else:
query = session.query(Asset.id, Asset.name).filter(
Asset.asset_level == level
) # short query when the level filed is given, for relate asset dropdown
if station_id is not None:
query = query.filter(Asset.station_id == station_id)
if type is not None:
query = query.filter(Asset.asset_type == type)
if station_name is not None:
query = query.filter(Station.name == station_name)
return await conn.fetch_all(query2sql(query))
async def get_latest(
conn: Database,
mp_id: int,
require_mp_type: int,
orm_model,
fileds: List[str],
session: Session = session_make(engine=None),
):
model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type)
query = session.query(model)
for filed in fileds:
query = query.options(load_only(filed))
query = query.order_by(model.id.desc()).limit(1)
res = await conn.fetch_one(query2sql(query))
return res
async def get_multi(
conn: Database,
skip: int,
limit: int,
asset_id: int,
session: Session = session_make(engine=None),
):
query = (
session.query(MaintenanceRecord, Asset.name.label("asset_name"))
.join(Asset, Asset.id == MaintenanceRecord.asset_id)
.order_by(MaintenanceRecord.statu.desc())
.offset(skip)
.limit(limit)
)
if asset_id:
query = query.filter(Asset.id == asset_id)
return await conn.fetch_all(query2sql(query))
async def get(
conn: Database,
mp_id: int,
require_mp_type: int,
orm_model,
fileds: List[str],
data_id: int,
session: Session = session_make(engine=None),
):
model = get_shard_model(orm_model, mp_id=mp_id, require_mp_type=require_mp_type)
query = session.query(model)
for filed in fileds + ["id", "time"]:
query = query.options(load_only(filed))
query = query.filter(model.data_id == data_id)
res = await conn.fetch_one(query2sql(query))
return res
async def get_stat(
conn: Database, rule: str, session: Session = session_make(engine=None)
):
if rule == "station":
query = session.query(
MeasurePoint.station_id, func.count("*").label("cnt")
).group_by(MeasurePoint.station_id)
elif rule == "asset":
query = session.query(
MeasurePoint.asset_id, func.count("*").label("cnt")
).group_by(MeasurePoint.asset_id)
elif rule == "statu":
query = session.query(
MeasurePoint.statu, func.count("*").label("cnt")
).group_by(MeasurePoint.statu)
else:
return None
return await conn.fetch_all(query2sql(query))
async def get(conn: Database, id: int, session: Session = session_make(engine=None)):
query = (
session.query(
MeasurePoint.id,
MeasurePoint.name,
MeasurePoint.type,
MeasurePoint.md_time,
MeasurePoint.statu,
MeasurePoint.sample_sensitive,
MeasurePoint.sample_freq,
MeasurePoint.asset_id,
Station.id.label("staion_id"),
Station.name.label("station_name"),
MeasurePoint.sample_interval,
)
.join(Station, MeasurePoint.station_id == Station.id)
.order_by(MeasurePoint.id)
.filter(MeasurePoint.id == id)
)
return await conn.fetch_one(query2sql(query))
async def get(conn: Database, id: int, session: Session = session_make(engine=None)):
query = (
session.query(
Asset.id,
Asset.name,
Asset.sn,
Asset.lr_time,
Asset.cr_time,
Asset.md_time,
Asset.st_time,
Asset.asset_level,
Asset.memo,
Asset.health_indicator,
Asset.statu,
Station.name.label("station_name"),
)
.join(Station, Station.id == Asset.station_id)
.filter(Asset.id == id)
)
return await conn.fetch_one(query2sql(query))
async def get_estimated_value_multi(
conn: Database,
asset_id: int,
time_before: str,
time_after: str,
session: Session = session_make(engine=None),
):
hi_model = AssetHI.model(point_id=asset_id)
query = session.query(hi_model.id, hi_model.time, hi_model.est).filter(
hi_model.time.between(str(time_after), str(time_before)))
res = await conn.fetch_all(query2sql(query))
dic = {}
for row in res:
dic.setdefault("id", []).append(row["id"])
dic.setdefault("time", []).append(str(row["time"]))
serialized = json.loads(row["est"])
for index, fileds in enumerate(serialized["label"]):
dic.setdefault(fileds + "—原始值",
[]).append(serialized["raw"][index])
dic.setdefault(fileds + "-估计值",
[]).append(serialized["est"][index])
return dic
async def get_total_length(conn: Database,
session: Session = session_make(engine=None)):
query = session.query(func.sum(
Pipeline.length).label("value")).select_from(Pipeline)
return await conn.fetch_one(query2sql(query))
async def get_data(query):
db = Database(META_URL)
await db.connect()
res = await db.fetch_all(query2sql(query))
await db.disconnect()
return res
