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))
def cal_elec_feature():
processed_rows = 0
session = session_make(engine=meta_engine)
mps: List[MeasurePoint] = fetch_mps(session=session)
for mp in mps: # Each measure points
data = fetch_data(
session=session,
station_id=mp.station_id,
inner_station_id=mp.inner_station_id,
)
if len(data) > 0:
feature_insert_value = []
for index, row in enumerate(data): # each row
fundamental, feature = calculate_feature_row(
row=row,
station_id=mp.station_id,
inner_station_id=mp.inner_station_id,
)
feature_insert_value.append(feature)
if index == len(data) - 1:
statu, hi = health_evaluate(feature, fundamental)
session.query(Motor).filter(
Motor.asset_id == mp.asset_id).update({
"statu":
statu,
"health_indicator":
hi,
})
session.commit()
processed_rows += insert_feature(session=session,
to_save=feature_insert_value)
session.close()
return processed_rows
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_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_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_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))
def cal_vib_feature():
processed_rows = 0
session = session_make(engine=meta_engine)
mps: List[MeasurePoint] = fetch_mps(session=session)
for mp in mps: # Each measure points
data = fetch_data(
session=session,
station_id=mp.station_id,
inner_station_id=mp.inner_station_id,
)
if len(data) > 0:
feature_insert_value = []
for row in data: # each row
feature_insert_value.append(
calculate_feature_row(
row=row,
station_id=mp.station_id,
inner_station_id=mp.inner_station_id,
)
)
processed_rows += insert_feature(
session=session, to_save=feature_insert_value
)
session.close()
return processed_rows
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_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(
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_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_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,
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_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 create(
data: MeasurePointInputSchema, session: Session = session_make(engine=None)
):
data = jsonable_encoder(data)
data_model = VibData if (data["type"] == 0) else ElecData
feature_model = VibFeature if (data["type"] == 0) else ElecFeature
id = 0
async with Database(META_URL) as conn:
id = await conn.execute(query=MeasurePoint.__table__.insert(), values=data)
if id:
async with Database(META_URL) as conn:
data_table = data_model.model(point_id=id)
feature_table = feature_model.model(point_id=id)
await conn.execute(
str(CreateTable(data_table.__table__).compile(meta_engine))
)
await conn.execute(
str(CreateTable(feature_table.__table__).compile(meta_engine))
)
mp_change_commit()
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_weathers(conn: Database,
session: Session = session_make(engine=None)):
query = session.query(Station.id, Station.name,
Station.weather).order_by(Station.id)
return await conn.fetch_all(query2sql(query))
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))
for base_data in base_data_list:
feature_row = [base_data["rms"]]
for mp in mps_info[1:]: # 某设备所拥有的测点inner_station_id的排序必须一致
query = "select rms from vib_data_{0}_{1} order by abs(datediff(time,'{2}')) limit 1".format(
mp["station_id"], mp["inner_station_id"],
str(base_data["time"]))
res = await db.fetch_all(query)
# feature_row.append(res[0]['rms'])
feature_row.append(res[0]["rms"] + random.random())
feature_matrix.append(feature_row)
await db.disconnect()
return feature_matrix
session = session_make(None)
query = (session.query(
MeasurePoint.station_id, MeasurePoint.inner_station_id).filter(
MeasurePoint.asset_id == asset_id,
MeasurePoint.type == 0).order_by(MeasurePoint.inner_station_id))
mps_info = asyncio.run(get_data(query))
base_data_model = VibData.model(station_id=mps_info[0]["station_id"],
inner_id=mps_info[0]["inner_station_id"])
base_data_query = session.query(base_data_model.time,
base_data_model.rms).order_by(
base_data_model.time.desc())
base_data_list = asyncio.run(get_data(base_data_query))
feature_matrix = asyncio.run(get_time_relate_data(mps_info, base_data_list))
import random
from db import meta_engine
from db.db_config import session_make
from db_model import Asset, Manufacturer
import datetime
session = session_make(meta_engine)
assets = session.query(Asset).all()
manufactures = session.query(Manufacturer).all()
for asset in assets:
Motor = Asset(
name="驱动电机#{0}".format(random.randint(100, 999)),
sn="".join(random.sample("zyxwvutsrqponmlkjihgfedcba", 8)),
asset_level=1,
asset_type=2,
st_time=datetime.datetime.fromisoformat("2014-09-09 10:24:25"),
memo="N/A",
station=asset.station,
admin=asset.admin,
repairs=random.randint(0, 5),
health_indicator=85,
parent_id=asset.id,
manufacturer=random.choice(manufactures),
)
Bearing1 = Asset(
name="驱动端轴承#{0}".format(random.randint(100, 999)),
sn="".join(random.sample("zyxwvutsrqponmlkjihgfedcba", 8)),
asset_level=2,
async def get(conn: Database,
id: int,
session: Session = session_make(engine=None)):
query = session.query(Station).filter(Station.id == id)
return await conn.fetch_one(query2sql(query))
