72 lines
2.5 KiB
Python
72 lines
2.5 KiB
Python
import sys
|
||
import os
|
||
|
||
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
|
||
|
||
from model.database import AppliedJob, Job, async_session
|
||
import asyncio
|
||
|
||
from sqlalchemy import select, update, func
|
||
from sqlalchemy.orm import joinedload
|
||
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
|
||
# from model.database import AppliedJob, Job
|
||
from sqlalchemy.sql import and_
|
||
from sqlalchemy.orm import sessionmaker
|
||
|
||
from dotenv import load_dotenv
|
||
import os
|
||
|
||
# Загружаем переменные окружения
|
||
load_dotenv()
|
||
|
||
# Правильная строка подключения
|
||
DATABASE_URL = os.getenv('DATABASE_URL') # Должно быть "mysql+aiomysql://..." или "postgresql+asyncpg://..."
|
||
|
||
# Создаём асинхронный движок
|
||
# async_engine = create_async_engine(DATABASE_URL, echo=True)
|
||
async_engine = create_async_engine(DATABASE_URL, echo=True, pool_recycle=1800, pool_size=10, pool_pre_ping=True )
|
||
|
||
|
||
# Создаём фабрику сессий
|
||
async_session = sessionmaker(async_engine, class_=AsyncSession, expire_on_commit=False)
|
||
|
||
async def promote_backlogged_to_scheduled(db: async_session):
|
||
# Получаем всех уникальных client_id, у которых есть Backlogged
|
||
result = await db.execute(
|
||
select(AppliedJob.client_id)
|
||
.where(AppliedJob.status == "Backlogged")
|
||
.distinct()
|
||
)
|
||
client_ids = [row[0] for row in result.fetchall()]
|
||
|
||
for client_id in client_ids:
|
||
# Выбираем до 5 самых новых Backlogged вакансий для клиента
|
||
result = await db.execute(
|
||
select(AppliedJob)
|
||
.join(AppliedJob.job) # ⬅️ Явно присоединяем таблицу jobs
|
||
.options(joinedload(AppliedJob.job)) # Чтобы подгрузить Job в объект
|
||
.where(
|
||
and_(
|
||
AppliedJob.client_id == client_id,
|
||
AppliedJob.status == "Backlogged"
|
||
)
|
||
)
|
||
.order_by(Job.date_posted.desc())
|
||
.limit(5)
|
||
)
|
||
jobs_to_update = result.scalars().all()
|
||
|
||
for job in jobs_to_update:
|
||
job.status = "Scheduled" # Меняем статус
|
||
|
||
await db.commit()
|
||
return {"message": f"Обновлено клиентов: {len(client_ids)}"}
|
||
|
||
|
||
async def main():
|
||
async with async_session() as session:
|
||
await promote_backlogged_to_scheduled(session)
|
||
|
||
if __name__ == "__main__":
|
||
asyncio.run(main())
|