Spaces:
Sleeping
Sleeping
File size: 2,691 Bytes
55d0d9e | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | from sqlalchemy import select, cast, Integer
from sqlalchemy import select, or_
from sqlalchemy.ext.asyncio import AsyncSession
from app.models.models import Agencies, AgencyStandards, MapCodeStandard, CodesTable
import logging
class AgencyService:
def __init__(self):
self.logger = logging.getLogger(__name__)
async def get_agencies(self, session: AsyncSession):
query = select(Agencies).order_by(Agencies.agency.asc())
result = await session.execute(query)
return result.scalars().all()
async def get_agency_standards(self, session: AsyncSession):
query = select(AgencyStandards).order_by(AgencyStandards.agency.asc())
result = await session.execute(query)
return result.scalars().all()
async def list_codes_with_standards(self, session: AsyncSession, limit: int):
query = (
select(Agencies, AgencyStandards, MapCodeStandard)
.join(AgencyStandards, MapCodeStandard.standard_id == AgencyStandards.standard_id)
.join(Agencies, AgencyStandards.agency == Agencies.agency)
.limit(limit)
)
result = await session.execute(query)
return result.all()
async def get_codes_by_agency(self, session: AsyncSession, agency_name: str, limit: int):
query = (
select(CodesTable)
.join(MapCodeStandard, CodesTable.code == MapCodeStandard.code)
.join(AgencyStandards, MapCodeStandard.standard_id == AgencyStandards.standard_id)
.where(AgencyStandards.agency == agency_name.upper())
.distinct()
.order_by(CodesTable.sort_index.asc())
.limit(limit)
)
result = await session.execute(query)
return result.scalars().all()
async def get_standards_by_code(self, session: AsyncSession, code: str):
query = (
select(
MapCodeStandard.standard_id,
AgencyStandards.agency,
AgencyStandards.definition,
AgencyStandards.codes,
AgencyStandards.tables,
AgencyStandards.images,
AgencyStandards.raw_references,
Agencies.agency_info
)
.distinct()
.join(AgencyStandards, AgencyStandards.standard_id == MapCodeStandard.standard_id)
.outerjoin(Agencies, Agencies.agency == AgencyStandards.agency)
.where(MapCodeStandard.code == code)
.order_by(MapCodeStandard.standard_id.asc())
)
result = await session.execute(query)
return result.mappings().all()
agency_service = AgencyService() |