You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
256 lines
11 KiB
256 lines
11 KiB
1 month ago
|
from sqlalchemy import and_, or_, desc, func
|
||
|
|
from sqlalchemy.orm import Session
|
||
|
|
from sqlalchemy.sql.expression import text
|
||
|
|
from module_admin.entity.do.dassetdict_do import SysDassetdict
|
||
|
|
from module_admin.entity.do.dasset_do import SysDasset
|
||
|
|
from module_admin.entity.vo.dassetdict_vo import DassetdictModel, CurrentDassetdictInfo, DassetdictQueryModel
|
||
|
|
from utils.time_format_util import object_format_datetime, list_format_datetime, format_datetime_dict_list
|
||
|
|
from datetime import datetime, time
|
||
|
|
from typing import Union, List
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
class DassetdictDao:
|
||
|
|
"""
|
||
|
|
数据资产字典管理模块数据库操作层
|
||
|
|
"""
|
||
|
|
|
||
|
|
@classmethod
|
||
|
|
def get_dassetdict_by_name(cls, db: Session, dict_fn: str):
|
||
|
|
"""
|
||
|
|
根据字段名获取字段信息
|
||
|
|
:param db: orm对象
|
||
|
|
:param dict_fn: 字段名称
|
||
|
|
:return: 当前字段名的字段信息对象
|
||
|
|
"""
|
||
|
|
query_dassetdict_info = db.query(SysDassetdict) \
|
||
|
|
.filter(SysDassetdict.status == 0, SysDassetdict.del_flag == 0, SysDassetdict.dict_fn == dict_fn) \
|
||
|
|
.order_by(SysDassetdict.order_asc).distinct().first()
|
||
|
|
#.order_by(desc(SysDassetdict.create_time)).distinct().first()
|
||
|
|
|
||
|
|
|
||
|
|
return query_dassetdict_info
|
||
|
|
|
||
|
|
@classmethod
|
||
|
|
def get_dassetdict_by_info(cls, db: Session, dassetdict: DassetdictModel):
|
||
|
|
"""
|
||
|
|
根据字段参数获取字段信息
|
||
|
|
:param db: orm对象
|
||
|
|
:param dassetdict: 字段参数
|
||
|
|
:return: 当前字段参数的字段信息对象
|
||
|
|
"""
|
||
|
|
query_dassetdict_info = db.query(SysDassetdict) \
|
||
|
|
.filter(SysDassetdict.del_flag == 0,
|
||
|
|
SysDassetdict.dict_fn == dassetdict.dict_fn) \
|
||
|
|
.order_by(SysDassetdict.order_asc).distinct().first()
|
||
|
|
#.order_by(desc(SysDassetdict.create_time)).distinct().first()
|
||
|
|
|
||
|
|
return query_dassetdict_info
|
||
|
|
|
||
|
|
@classmethod
|
||
|
|
def get_dassetdict_by_id(cls, db: Session, dict_id: int):
|
||
|
|
"""
|
||
|
|
根据dict_id获取字段信息
|
||
|
|
:param db: orm对象
|
||
|
|
:param dict_id: 字段id
|
||
|
|
:return: 当前dict_id的字段信息对象
|
||
|
|
"""
|
||
|
|
query_dassetdict_basic_info = db.query(SysDassetdict) \
|
||
|
|
.filter(SysDassetdict.status == 0, SysDassetdict.del_flag == 0, SysDassetdict.dict_id == dict_id) \
|
||
|
|
.distinct().first()
|
||
|
|
query_dassetdict_dasset_info = db.query(SysDasset).select_from(SysDassetdict) \
|
||
|
|
.filter( SysDassetdict.status == 0, SysDassetdict.del_flag == 0, SysDassetdict.dict_id == dict_id) \
|
||
|
|
.join(SysDasset, and_(SysDassetdict.dasset_id == SysDasset.dasset_id, SysDasset.status == 0, SysDasset.del_flag == 0)) \
|
||
|
|
.distinct().first()
|
||
|
|
|
||
|
|
results = dict(
|
||
|
|
dassetdict_basic_info=object_format_datetime(query_dassetdict_basic_info),
|
||
|
|
dassetdict_dasset_info=object_format_datetime(query_dassetdict_dasset_info)
|
||
|
|
)
|
||
|
|
|
||
|
|
return CurrentDassetdictInfo(**results)
|
||
|
|
|
||
|
|
@classmethod
|
||
|
|
def get_dassetdict_detail_by_id(cls, db: Session, dict_id: int):
|
||
|
|
"""
|
||
|
|
根据dict_id获取字段详细信息
|
||
|
|
:param db: orm对象
|
||
|
|
:param dict_id: 字段id
|
||
|
|
:return: 当前dict_id的字段信息对象
|
||
|
|
"""
|
||
|
|
query_dassetdict_basic_info = db.query(SysDassetdict) \
|
||
|
|
.filter(SysDassetdict.del_flag == 0, SysDassetdict.dict_id == dict_id) \
|
||
|
|
.distinct().first()
|
||
|
|
query_dassetdict_dasset_info = db.query(SysDasset).select_from(SysDassetdict) \
|
||
|
|
.filter(SysDassetdict.del_flag == 0, SysDassetdict.dict_id == dict_id) \
|
||
|
|
.join(SysDasset, and_(SysDassetdict.dasset_id == SysDasset.dasset_id, SysDasset.status == 0, SysDasset.del_flag == 0)) \
|
||
|
|
.distinct().first()
|
||
|
|
|
||
|
|
results = dict(
|
||
|
|
dassetdict_basic_info=object_format_datetime(query_dassetdict_basic_info),
|
||
|
|
dassetdict_dasset_info=object_format_datetime(query_dassetdict_dasset_info)
|
||
|
|
)
|
||
|
|
return CurrentDassetdictInfo(**results)
|
||
|
|
|
||
|
|
@classmethod
|
||
|
|
def get_dassetdict_list(cls, db: Session, query_object: DassetdictQueryModel, data_scope_sql: str):
|
||
|
|
"""
|
||
|
|
根据查询参数获取字段列表信息
|
||
|
|
:param db: orm对象
|
||
|
|
:param query_object: 查询参数对象
|
||
|
|
:param data_scope_sql: 数据权限对应的查询sql语句
|
||
|
|
:return: 字段列表信息对象
|
||
|
|
"""
|
||
|
|
dassetdict_list = db.query(SysDassetdict, SysDasset) \
|
||
|
|
.filter(SysDassetdict.del_flag == 0,
|
||
|
|
or_(SysDassetdict.dasset_id == query_object.dasset_id, SysDassetdict.dasset_id.in_(
|
||
|
|
db.query(SysDasset.dasset_id).filter(func.find_in_set(query_object.dasset_id, SysDasset.dasset_ancestors))
|
||
|
|
)) if query_object.dasset_id else True,
|
||
|
|
SysDassetdict.dict_fn.like(f'%{query_object.dict_fn}%') if query_object.dict_fn else True,
|
||
|
|
SysDassetdict.dict_fccn.like(f'%{query_object.dict_fccn}%') if query_object.dict_fccn else True,
|
||
|
|
SysDassetdict.dict_pk.like(f'%{query_object.dict_pk}%') if query_object.dict_pk else True,
|
||
|
|
SysDassetdict.dict_dft.like(f'%{query_object.dict_dft}%') if query_object.dict_dft else True,
|
||
|
|
SysDassetdict.dict_ft.like(f'%{query_object.dict_ft}%') if query_object.dict_ft else True,
|
||
|
|
SysDassetdict.data_dict_no.like(f'%{query_object.data_dict_no}%') if query_object.data_dict_no else True,
|
||
|
|
SysDassetdict.dict_al.like(f'%{query_object.dict_al}%') if query_object.dict_al else True,
|
||
|
|
SysDassetdict.dict_sg.like(f'%{query_object.dict_sg}%') if query_object.dict_sg else True,
|
||
|
|
# SysDassetdict.dict_sg.like(f'%{query_object.dict_sg}%') if query_object.dict_sg else True,
|
||
|
|
SysDassetdict.status == query_object.status if query_object.status else True,
|
||
|
|
SysDassetdict.create_time.between(
|
||
|
|
datetime.combine(datetime.strptime(query_object.create_time_start, '%Y-%m-%d'), time(00, 00, 00)),
|
||
|
|
datetime.combine(datetime.strptime(query_object.create_time_end, '%Y-%m-%d'), time(23, 59, 59)))
|
||
|
|
if query_object.create_time_start and query_object.create_time_end else True,
|
||
|
|
eval(data_scope_sql)
|
||
|
|
) \
|
||
|
|
.outerjoin(SysDasset, and_(SysDassetdict.dasset_id == SysDasset.dasset_id, SysDasset.status == 0, SysDasset.del_flag == 0)) \
|
||
|
|
.distinct().all()
|
||
|
|
|
||
|
|
result_list: List[Union[dict, None]] = []
|
||
|
|
if dassetdict_list:
|
||
|
|
for item in dassetdict_list:
|
||
|
|
obj = dict(
|
||
|
|
dasset_id=item[0].dasset_id,
|
||
|
|
dict_id=item[0].dict_id,
|
||
|
|
tab_name=item[0].tab_name,
|
||
|
|
dasset_name=item[1].dasset_name if item[1] else '',
|
||
|
|
dict_fn=item[0].dict_fn,
|
||
|
|
tab_cn_name=item[0].tab_cn_name,
|
||
|
|
data_dict_no=item[0].data_dict_no,
|
||
|
|
dict_fccn=item[0].dict_fccn,
|
||
|
|
dict_pk=item[0].dict_pk,
|
||
|
|
dict_dft=item[0].dict_dft,
|
||
|
|
dict_ft=item[0].dict_ft,
|
||
|
|
dict_bc=item[0].dict_bc,
|
||
|
|
dict_al=item[0].dict_al,
|
||
|
|
memo=item[0].memo,
|
||
|
|
dict_sg=item[0].dict_sg,
|
||
|
|
status=item[0].status,
|
||
|
|
del_flag=item[0].del_flag,
|
||
|
|
create_by=item[0].create_by,
|
||
|
|
create_time=item[0].create_time,
|
||
|
|
update_by=item[0].update_by,
|
||
|
|
update_time=item[0].update_time,
|
||
|
|
remark=item[0].remark,
|
||
|
|
order_asc=item[0].order_asc
|
||
|
|
)
|
||
|
|
result_list.append(obj)
|
||
|
|
return format_datetime_dict_list(result_list)
|
||
|
|
|
||
|
|
@classmethod
|
||
|
|
def add_dassetdict_dao(cls, db: Session, dassetdict: DassetdictModel):
|
||
|
|
"""
|
||
|
|
新增字段数据库操作
|
||
|
|
:param db: orm对象
|
||
|
|
:param dassetdict: 字段对象
|
||
|
|
:return: 新增校验结果
|
||
|
|
"""
|
||
|
|
db_dassetdict = SysDassetdict(**dassetdict.dict())
|
||
|
|
db.add(db_dassetdict)
|
||
|
|
db.commit()
|
||
|
|
db.flush()
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
# 构建原生SQL更新语句
|
||
|
|
update_sql = """
|
||
|
|
update sys_dasset_dict a
|
||
|
|
join (
|
||
|
|
select dasset_id,dasset_name,dasset_remark from sys_dasset
|
||
|
|
) b on a.dasset_id = b.dasset_id
|
||
|
|
set a.tab_name = b.dasset_name ,
|
||
|
|
a.tab_cn_name = b.dasset_remark
|
||
|
|
"""
|
||
|
|
|
||
|
|
# 构建原生SQL更新语句
|
||
|
|
update_sql2 = """
|
||
|
|
update sys_dasset_dict a
|
||
|
|
join (
|
||
|
|
select row_number() over(PARTITION BY a.tab_name order by dict_id ) as s1, a.* from sys_dasset_dict a
|
||
|
|
) b on a.dict_id = b.dict_id
|
||
|
|
set a.order_asc = b.s1
|
||
|
|
"""
|
||
|
|
|
||
|
|
# 执行原生SQL
|
||
|
|
db.execute(text(update_sql))
|
||
|
|
db.execute(text(update_sql2))
|
||
|
|
db.commit()
|
||
|
|
db.flush()
|
||
|
|
|
||
|
|
|
||
|
|
return db_dassetdict
|
||
|
|
|
||
|
|
@classmethod
|
||
|
|
def edit_dassetdict_dao(cls, db: Session, dassetdict: dict):
|
||
|
|
"""
|
||
|
|
编辑字段数据库操作
|
||
|
|
:param db: orm对象
|
||
|
|
:param dassetdict: 需要更新的字段字典
|
||
|
|
:return: 编辑校验结果
|
||
|
|
"""
|
||
|
|
db.query(SysDassetdict) \
|
||
|
|
.filter(SysDassetdict.dict_id == dassetdict.get('dict_id')) \
|
||
|
|
.update(dassetdict)
|
||
|
|
|
||
|
|
db.commit()
|
||
|
|
db.flush()
|
||
|
|
|
||
|
|
@classmethod
|
||
|
|
def delete_dassetdict_dao(cls, db: Session, dassetdict: DassetdictModel):
|
||
|
|
"""
|
||
|
|
删除字段数据库操作
|
||
|
|
:param db: orm对象
|
||
|
|
:param dassetdict: 字段对象
|
||
|
|
:return:
|
||
|
|
"""
|
||
|
|
db.query(SysDassetdict) \
|
||
|
|
.filter(SysDassetdict.dict_id == dassetdict.dict_id) \
|
||
|
|
.delete()
|
||
|
|
#.update({SysDassetdict.del_flag: '2'})
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
@classmethod
|
||
|
|
def get_dassetdict_dasset_info(cls, db: Session, dasset_id: int):
|
||
|
|
dasset_basic_info = db.query(SysDasset) \
|
||
|
|
.filter(SysDasset.dasset_id == dasset_id,
|
||
|
|
SysDasset.status == 0,
|
||
|
|
SysDasset.del_flag == 0 ) \
|
||
|
|
.first()
|
||
|
|
return dasset_basic_info
|
||
|
|
|
||
|
|
|
||
|
|
@classmethod
|
||
|
|
def get_dassetdict_by_info(cls, db: Session, dassetdict: DassetdictModel):
|
||
|
|
"""
|
||
|
|
根据字段信息参数获取dassetdict信息
|
||
|
|
:param db: orm对象
|
||
|
|
:param user: dassetdict参数
|
||
|
|
:return: 当前dassetdict参数的dassetdict信息对象
|
||
|
|
"""
|
||
|
|
query_dassetdict_info = db.query(SysDassetdict) \
|
||
|
|
.filter(SysDassetdict.status == 0,
|
||
|
|
SysDassetdict.dict_id == dassetdict.dict_id) \
|
||
|
|
.order_by(SysDassetdict.dict_id,SysDassetdict.order_asc).distinct().first()
|
||
|
|
#增加排序字段的增序
|
||
|
|
|
||
|
|
return query_dassetdict_info
|