from collections import defaultdict import lexorank import math import starlette.status from fastapi import HTTPException from sqlalchemy import select, func, update, delete, insert, and_, Select from sqlalchemy.orm import joinedload, selectinload, noload from card_attributes import CardAttributesCommandHandler from card_attributes.exceptions import CardAttributeException from models import * from schemas.auth import UserUnion from schemas.base import PaginationSchema from schemas.card import * from schemas.client import ClientDetailsSchema from services import card_group from services.auth import AuthService from services.base import BaseService from services.card_tag import CardTagService from services.client import ClientService from services.service import ServiceService from services.shipping_warehouse import ShippingWarehouseService from utils.auth import verify_user_deal_editor, verify_user_viewer class CardsService(BaseService): # region Card async def _get_card_by_id(self, card_id) -> Union[Card, None]: return await self.session.get(Card, card_id) async def _get_rank_for_card(self, status_id: int) -> str: stmt = await self.session.execute( select(Card) .where(Card.current_status_id == status_id) .order_by(Card.lexorank.desc()) .limit(1) ) card = stmt.scalar_one_or_none() if not card: prev = lexorank.middle(lexorank.Bucket.BUCEKT_0) return str(prev.next()) return str(lexorank.parse(card.lexorank).next()) async def change_status( self, card: Card, status_id: int, user: User, deadline: datetime = None, rank=None, comment: str = '' ): if not card.current_status_id == status_id: deadline = deadline status_change = CardStatusHistory( card_id=card.id, user_id=user.id, changed_at=datetime.now(), from_status_id=card.current_status_id, to_status_id=status_id, next_status_deadline=deadline, comment=comment ) self.session.add(status_change) card.current_status_id = status_id if not rank: rank = await self._get_rank_for_card(status_id) if rank: card.lexorank = rank await self.session.flush() async def delete(self, request: CardDeleteRequest) -> CardDeleteResponse: card = await self._get_card_by_id(request.card_id) if not card: return CardDeleteResponse(ok=False, message="Карточка не найдена") if card.group: await card_group.CardGroupService(self.session).delete_group(card.group.id) else: card.is_deleted = True await self.session.commit() return CardDeleteResponse(ok=True, message="Карточка успешно удалена") async def quick_create(self, request: CardQuickCreateRequest, user: User) -> CardQuickCreateResponse: card_status = await self.session.get(CardStatus, request.status_id) if not card_status: raise HTTPException(status_code=400, detail="Указан некорректный статус") client_service = ClientService(self.session) client = await client_service.get_by_name(request.client_name) if not client and request.client_name: client = await client_service.create_client_raw( user, request.client_name, ClientDetailsSchema() ) shipping_warehouse_service = ShippingWarehouseService(self.session) shipping_warehouse = await shipping_warehouse_service.get_by_name(name=request.shipping_warehouse) if not shipping_warehouse and request.shipping_warehouse: shipping_warehouse = await shipping_warehouse_service.create_by_name(name=request.shipping_warehouse) rank = await self._get_rank_for_card(request.status_id) card = Card( name=request.name, created_at=datetime.now(), client_id=client.id if client else None, current_status_id=request.status_id, board_id=card_status.board_id, lexorank=rank, shipping_warehouse_id=shipping_warehouse.id if shipping_warehouse else None, base_marketplace_key=request.base_marketplace.key if request.base_marketplace else None ) self.session.add(card) await self.session.flush() try: card_attrs_handler = CardAttributesCommandHandler(self.session) await card_attrs_handler.set_attributes_after_creation(card) await self.session.commit() except CardAttributeException: await self.session.rollback() return CardQuickCreateResponse(card_id=card.id) async def change_status_manual(self, request: CardChangeStatusRequest, user: User) -> CardChangeStatusResponse: card = await self._get_card_by_id(request.card_id) if not card: return CardChangeStatusResponse(ok=False) await self.change_status(card, request.new_status, user) await self.session.commit() return CardChangeStatusResponse(ok=True) def _get_price_subquery(self): card_services_subquery = ( select( CardService.card_id, func.sum(CardService.quantity * CardService.price).label( 'total_price') ) .join(Service) .group_by(CardService.card_id) ) product_services_subquery = select( select( CardProductService.card_id, func.sum(CardProduct.quantity * CardProductService.price).label('total_price') ) .join(CardProduct) .group_by(CardProductService.card_id) .subquery() ) union_subqueries = card_services_subquery.union_all(product_services_subquery).subquery() final_subquery = ( select( union_subqueries.c.card_id, func.sum(union_subqueries.c.total_price).label('total_price') ) .group_by(union_subqueries.c.card_id) .subquery() ) return final_subquery def _get_products_quantity_subquery(self): return ( select( CardProduct.card_id, func.sum(CardProduct.quantity).label('total_quantity') ) .group_by(CardProduct.card_id) .subquery() ) @staticmethod def _apply_pagination(query: Select, pagination: PaginationSchema) -> Select: offset = (pagination.page - 1) * pagination.items_per_page query = ( query .offset(offset) .limit(pagination.items_per_page) ) return query @staticmethod def _apply_summary_filters(query: Select, request: GetCardSummariesRequest) -> Select: if not request.full: return query.where(Card.is_completed == False) if request.card_id: query = query.where(Card.id == request.card_id) if request.card_name: query = query.where(Card.name.like(f"%{request.card_name}%")) if request.status_id: query = query.where(Card.current_status_id == request.status_id) elif request.board_id: query = query.where(Card.board_id == request.board_id) elif request.project_id: query = ( query .join(Board) .where(Board.project_id == request.project_id) ) if request.client_id: query = query.where(Card.client_id == request.client_id) if request.marketplace_key: query = query.where(Card.base_marketplace_key == request.marketplace_key) if request.shipping_warehouse_id: query = query.where(Card.shipping_warehouse_id == request.shipping_warehouse_id) query = query.order_by(Card.created_at.desc()) return query async def _summaries_pagination_info(self, query: Select, pagination: PaginationSchema) -> PaginationInfoSchema: if not pagination.items_per_page: return PaginationInfoSchema(total_pages=0, total_items=0) summaries = (await self.session.scalars(query)).all() total_items = len(summaries) return PaginationInfoSchema( total_pages=math.ceil(total_items / pagination.items_per_page), total_items=total_items, ) async def get_summary( self, request: GetCardSummariesRequest, pagination: PaginationSchema, user: UserUnion, ) -> CardSummaryResponse: price_subquery = self._get_price_subquery() products_quantity_subquery = self._get_products_quantity_subquery() q = ( select( Card, func.coalesce(price_subquery.c.total_price, 0), func.row_number().over( partition_by=Card.current_status_id, order_by=Card.lexorank ).label('rank'), func.coalesce(products_quantity_subquery.c.total_quantity, 0) ) .options( selectinload(Card.status_history), joinedload(Card.client), joinedload(Card.shipping_warehouse), selectinload(Card.bill_requests), joinedload(Card.status), joinedload(Card.board), joinedload(Card.group).selectinload(CardGroup.bill_requests) ) .outerjoin( price_subquery, Card.id == price_subquery.c.card_id, ) .outerjoin( products_quantity_subquery, Card.id == products_quantity_subquery.c.card_id ) .where( Card.is_deleted == False, ) ) if isinstance(user, dict) and "client_id" in user: q = q.where(Card.client_id == user["client_id"]) q = self._apply_summary_filters(q, request) pagination_info = await self._summaries_pagination_info(q, pagination) if pagination.page and pagination.items_per_page: q = CardsService._apply_pagination(q, pagination) cards_query = await self.session.execute(q) summaries = [] for card, total_price, rank, products_count in cards_query.all(): card: Card base_marketplace = None if card.base_marketplace: base_marketplace = BaseMarketplaceSchema.model_validate(card.base_marketplace) shipment_warehouse_name = card.shipping_warehouse.name if card.shipping_warehouse else None attributes = [attr for attr in card.attributes if attr.attribute.is_deleted == False] summaries.append( CardSummary( id=card.id, client_name=card.client.name if card.client else None, name=card.name, status=card.status, board=card.board, group=card.group, total_price=total_price, rank=rank, base_marketplace=base_marketplace, created_at=card.created_at, shipment_warehouse_id=card.shipping_warehouse_id, shipment_warehouse_name=shipment_warehouse_name, total_products=products_count, bill_requests=card.bill_requests, tags=card.tags, attributes=attributes, ) ) return CardSummaryResponse(summaries=summaries, pagination_info=pagination_info) async def get_all(self) -> CardGetAllResponse: cards_stmt = ( await self.session.scalars( select(Card) .options( joinedload(Card.shipping_warehouse), joinedload(Card.client) .joinedload(Client.details), selectinload(Card.services) .options( joinedload(CardService.service).joinedload(Service.category), selectinload(CardService.employees) ), selectinload(Card.products) .joinedload(CardProduct.product) .joinedload(Product.client), selectinload(Card.products) .joinedload(CardProduct.product) .joinedload(Product.barcodes), selectinload(Card.products) .joinedload(CardProduct.services) .options( joinedload(CardProductService.service), selectinload(CardProductService.employees) ), selectinload(Card.status_history) .joinedload(CardStatusHistory.user), selectinload(Card.status_history) .noload(CardStatusHistory.card), selectinload(Card.bill_requests), ) ) ) cards = cards_stmt.all() result = [] for card in cards: result.append(CardSchema.model_validate(card)) return CardGetAllResponse(cards=result) async def get_by_id(self, user: UserUnion, card_id: int, return_raw=False) -> Union[ CardSchema, Card ]: card: Optional[Card] = await self.session.scalar( select(Card) .options( joinedload(Card.status), joinedload(Card.board) .joinedload(Board.project), joinedload(Card.shipping_warehouse), joinedload(Card.client) .joinedload(Client.details), selectinload(Card.services) .options( joinedload(CardService.service).joinedload(Service.category), selectinload(CardService.employees) ), selectinload(Card.products) .joinedload(CardProduct.product) .joinedload(Product.client), selectinload(Card.products) .joinedload(CardProduct.product) .joinedload(Product.barcodes), selectinload(Card.products) .joinedload(CardProduct.services) .options( joinedload(CardProductService.service), selectinload(CardProductService.employees) ), selectinload(Card.status_history) .joinedload(CardStatusHistory.user), selectinload(Card.status_history) .noload(CardStatusHistory.card), selectinload(Card.pallets) .selectinload(Pallet.shipping_products) .selectinload(ShippingProduct.product) .noload(Product.barcodes), selectinload(Card.boxes) .selectinload(Box.shipping_products) .selectinload(ShippingProduct.product) .noload(Product.barcodes), selectinload(Card.employees) .joinedload(CardEmployees.user), ) .where(Card.id == card_id) ) if not card: raise HTTPException(status_code=404, detail="Карточка не найдена") if not verify_user_deal_editor(user, card_id, False) and \ not verify_user_viewer(user, card.client_id, False): raise HTTPException(status_code=starlette.status.HTTP_403_FORBIDDEN, detail='Forbidden') if return_raw: return card return CardSchema.model_validate(card) async def update_general_info( self, request: CardUpdateGeneralInfoRequest, user: User ) -> CardUpdateGeneralInfoResponse: try: card: Card = await self.session.scalar( select(Card) .options( selectinload(Card.group) .selectinload(CardGroup.cards), joinedload(Card.board), ) .where(Card.id == request.card_id) ) if not card: raise HTTPException(status_code=404, detail="Карточка не найдена") card.name = request.data.name card.comment = request.data.comment card.is_deleted = request.data.is_deleted card.is_completed = request.data.is_completed card.client_id = request.data.client_id if card.board_id != request.data.board_id or card.current_status_id != request.data.status_id: if card.group: for card in card.group.cards: card.board_id = request.data.board_id await self.change_status(card, request.data.status_id, user) else: card.board_id = request.data.board_id await self.change_status(card, request.data.status_id, user) # Updating manager if request.data.manager: card.manager_id = request.data.manager.id else: card.manager = None tag_service = CardTagService(self.session) await tag_service.apply_tags(card, request.data.tags) card_attrs_handler = CardAttributesCommandHandler(self.session) await card_attrs_handler.set_attributes(card, request.data.attributes) await self.session.commit() return CardUpdateGeneralInfoResponse(ok=True, message='Данные карточки успешно обновлены') except Exception as e: await self.session.rollback() return CardUpdateGeneralInfoResponse(ok=False, message=str(e)) async def update_products_and_services_general_info(self, request: ProductsAndServicesGeneralInfoRequest, user: UserUnion) -> ( ProductsAndServicesGeneralInfoResponse ): verify_user_deal_editor(user, request.card_id) card: Optional[Card] = await self.session.get(Card, request.card_id) if not card: return ProductsAndServicesGeneralInfoResponse(ok=False, message='Карточка не найдена') # Updating shipping warehouse shipping_warehouse_service = ShippingWarehouseService(self.session) shipping_warehouse = await shipping_warehouse_service.get_by_name(request.data.shipping_warehouse) if not shipping_warehouse and request.data.shipping_warehouse: shipping_warehouse = await shipping_warehouse_service.create_by_name(request.data.shipping_warehouse) if card.group: stmt_group = select(CardGroup).where(CardGroup.id == card.group.id).options(selectinload(CardGroup.cards)) group = (await self.session.scalars(stmt_group)).one_or_none() for c in group.cards: c.is_services_profit_accounted = request.data.is_services_profit_accounted else: card.is_services_profit_accounted = request.data.is_services_profit_accounted card.shipping_warehouse = shipping_warehouse await self.session.commit() return ProductsAndServicesGeneralInfoResponse(ok=True, message='Данные карточки успешно обновлены') async def update_card_manager(self, request: UpdateCardManagerRequest) -> UpdateCardManagerResponse: card: Optional[Card] = await self.session.get(Card, request.card_id) if not card: return UpdateCardManagerResponse(ok=False, message='Карточка не найдена') card.manager_id = request.manager_id await self.session.commit() return UpdateCardManagerResponse(ok=True, message='Данные карточки успешно обновлены') async def update_card_client(self, request: UpdateCardClientRequest) -> UpdateCardClientResponse: card: Optional[Card] = await self.session.get(Card, request.card_id) if not card: return UpdateCardClientResponse(ok=False, message='Карточка не найдена') card.client_id = request.client_id await self.session.commit() return UpdateCardClientResponse(ok=True, message='Данные карточки успешно обновлены') async def reorder(self, request: CardSummaryReorderRequest, user: User) -> ReorderResponse: card: Card = await self.session.scalar(select(Card).where(Card.id == request.card_id)) if request.index == 1: request.index = 0 is_first = request.index == 0 stmt = ( select(Card) .where( Card.current_status_id == request.status_id, Card.id != request.card_id, Card.is_deleted == False, Card.is_completed == False ) .order_by(Card.lexorank) .offset(max([request.index - 2, 0])) .limit(2 if not is_first else 1) ) query = await self.session.execute(stmt) boundaries = query.scalars().all() top_boundary: Union[Card, None] = boundaries[0] if not is_first else None bottom_boundary: Union[Card, None] = boundaries[1] if len(boundaries) == 2 else None # working when between two elements if top_boundary and bottom_boundary: top_lexorank = lexorank.parse(top_boundary.lexorank) bottom_lexorank = lexorank.parse(bottom_boundary.lexorank) new_rank = lexorank.between(top_lexorank, bottom_lexorank) # working when at the bottom elif top_boundary and not bottom_boundary: new_rank = lexorank.parse(top_boundary.lexorank).next() # working when at the top elif bottom_boundary and not top_boundary: new_rank = lexorank.parse(bottom_boundary.lexorank).prev() elif not top_boundary and not bottom_boundary and len(boundaries) > 0: new_rank = lexorank.parse(boundaries[0].lexorank).prev() else: new_rank = lexorank.middle(lexorank.Bucket.BUCEKT_0) await self.change_status(card, request.status_id, user, deadline=request.deadline, comment=request.comment, rank=str(new_rank)) await self.session.commit() return ReorderResponse(ok=True, message="Изменения сохранены") async def add_kit_to_card(self, request: CardAddKitRequest) -> CardAddKitResponse: try: card = await self._get_card_by_id(request.card_id) if not card: return CardAddKitResponse(ok=False, message="Указанная карточка не найдена") kit = await ServiceService(self.session).get_kit_by_id(request.kit_id) if not kit: return CardAddKitResponse(ok=False, message="Указанный набор услуг не найден") services: list[Service] = kit.services insert_data = [] for service in services: price = self.get_service_price(service, 1) insert_data.append({ 'card_id': card.id, 'service_id': service.id, 'quantity': 1, 'price': price }) if not insert_data: return CardAddKitResponse(ok=True, message="Набор услуг успешно добавлен") # Deleting previous services delete_stmt = ( delete( CardService ) .where( CardService.card_id == request.card_id ) ) await self.session.execute(delete_stmt) await self.session.flush() await self.session.execute( insert(CardService), insert_data ) await self.session.flush() await self.session.commit() return CardAddKitResponse(ok=True, message="Набор услуг успешно добавлен") except Exception as e: return CardAddKitResponse(ok=False, message=str(e)) async def create_guest_url(self, request: CardCreateGuestUrlRequest) -> CardCreateGuestUrlResponse: access_token = AuthService(self.session).create_deal_guest_token(request.card_id) url = f"deals/{request.card_id}?accessToken={access_token}" return CardCreateGuestUrlResponse(ok=True, message='Ссылка успешно создана!', url=url) async def _create_card_services_for_prefilling(self, old_card: Card, new_card: Card): for service in old_card.services: card_service = CardService( service_id=service.service_id, card_id=new_card.id, quantity=service.quantity, price=service.price, ) self.session.add(card_service) async def _create_card_products_for_prefilling(self, old_card: Card, new_card: Card): for old_card_product in old_card.products: card_product = CardProduct( card_id=new_card.id, product_id=old_card_product.product.id, quantity=old_card_product.quantity, comment=old_card_product.comment, ) self.session.add(card_product) await self.session.flush() for old_service in old_card_product.services: card_product_service = CardProductService( card_id=new_card.id, product_id=old_card_product.product.id, service_id=old_service.service.id, price=old_service.price ) self.session.add(card_product_service) async def prefill_card_products_and_services(self, user, request: CardPrefillRequest) -> CardPrefillResponse: old_card: Card = await self.get_by_id(user, request.old_card_id, return_raw=True) new_card: Card = await self.get_by_id(user, request.new_card_id, return_raw=True) await self._create_card_services_for_prefilling(old_card, new_card) await self._create_card_products_for_prefilling(old_card, new_card) await self.session.commit() return CardPrefillResponse(ok=True, message="Карточка успешно предзаполнена товарами и услугами") # endregion # region Card services async def add_services(self, request: CardAddServicesRequest): card: Card = await self.session.scalar( select(Card) .options(selectinload(Card.services)) .where(Card.id == request.card_id) ) if not card: raise HTTPException(status_code=404, detail="Card is not found") services_ids = [service.id for service in request.services] existing_service_ids = {service.service_id for service in card.services} request_services_dict = {service.id: service.quantity for service in request.services} services_query = await self.session.scalars(select(Service).where(Service.id.in_(services_ids))) services = services_query.all() if len(services) != len(services_ids): raise HTTPException(status_code=404, detail="Some of services is not found") # Adding quantity for card_service in card.services: card_service: CardService if card_service.service_id not in services_ids: continue card_service.quantity += request_services_dict[card_service.service_id] # Adding new services for service in services: if service.id in existing_service_ids: continue quantity = request_services_dict[service.id] card.services.append( CardService( service_id=service.id, card_id=card.id, quantity=quantity ) ) await self.session.commit() return CardAddServicesResponse(ok=True, message='Услуги успешно добавлены') async def update_service_quantity( self, user: UserUnion, request: CardUpdateServiceQuantityRequest ) -> CardUpdateServiceQuantityResponse: try: verify_user_deal_editor(user, request.card_id) card_service = await self.session.scalar( select(CardService) .where(CardService.card_id == request.card_id, CardService.service_id == request.service_id) ) if not card_service: raise HTTPException(status_code=404, detail="Карточка не найдена") card_service.quantity = request.quantity await self.session.commit() return CardUpdateServiceQuantityResponse(ok=True, message='Количество успешно обновлено') except Exception as e: await self.session.rollback() return CardUpdateServiceQuantityResponse(ok=False, message=str(e)) async def add_service( self, user: UserUnion, request: CardAddServiceRequest ) -> CardAddServiceResponse: try: verify_user_deal_editor(user, request.card_id) card = await self.session.scalar(select(Card).where(Card.id == request.card_id)) if not card: raise HTTPException(status_code=404, detail="Карточка не найдена") service: Service = await self.session.scalar(select(Service).where(Service.id == request.service_id)) if not service: raise HTTPException(status_code=404, detail="Услуга не найдена") # Preventing duplicates card_service = await self.session.scalar( select(CardService) .where(CardService.card_id == request.card_id, CardService.service_id == request.service_id) ) if card_service: raise HTTPException(status_code=400, detail="Услуга уже добавлена") card_service = CardService( card_id=request.card_id, service_id=request.service_id, quantity=request.quantity, price=request.price ) self.session.add(card_service) await self.session.commit() return CardAddServiceResponse(ok=True, message='Услуга успешно добавлена') except Exception as e: await self.session.rollback() return CardAddServiceResponse(ok=False, message=str(e)) async def delete_service( self, user: UserUnion, request: CardDeleteServiceRequest ) -> CardDeleteServiceResponse: try: verify_user_deal_editor(user, request.card_id) card_service = await self.session.scalar( select(CardService) .where(CardService.card_id == request.card_id, CardService.service_id == request.service_id) ) if not card_service: raise HTTPException(status_code=404, detail="Карточка не найдена") await self.session.delete(card_service) await self.session.commit() return CardDeleteServiceResponse(ok=True, message='Услуга успешно удалена') except Exception as e: await self.session.rollback() return CardDeleteServiceResponse(ok=False, message=str(e)) async def delete_services( self, user: UserUnion, request: CardDeleteServicesRequest ) -> CardDeleteServicesResponse: try: verify_user_deal_editor(user, request.card_id) card_services = await self.session.scalars( select(CardService) .where(CardService.card_id == request.card_id, CardService.service_id.in_(request.service_ids)) ) for card_service in card_services: await self.session.delete(card_service) await self.session.commit() return CardDeleteServicesResponse(ok=True, message='Услуги успешно удалены') except Exception as e: await self.session.rollback() return CardDeleteServicesResponse(ok=False, message=str(e)) async def update_service( self, user: UserUnion, request: CardUpdateServiceRequest ) -> CardUpdateServiceResponse: try: verify_user_deal_editor(user, request.card_id) card_service = await self.session.scalar( select(CardService) .where(CardService.card_id == request.card_id, CardService.service_id == request.service.service.id) ) if not card_service: raise HTTPException(status_code=404, detail="Карточка не найдена") service_dict = request.service.dict() del service_dict['service'] del service_dict['employees'] service_dict['service_id'] = request.service.service.id await self.session.execute( update(CardService) .where(CardService.card_id == request.card_id, CardService.service_id == request.service.service.id) .values(**service_dict) ) # Updating deleting previous employees delete_stmt = ( delete( card_service_employees ) .where( card_service_employees.c.card_id == request.card_id, card_service_employees.c.service_id == request.service.service.id, ) ) await self.session.execute(delete_stmt) await self.session.flush() insert_data = [] for employee in request.service.employees: insert_data.append({ 'card_id': request.card_id, 'service_id': request.service.service.id, 'user_id': employee.id }) if insert_data: await self.session.execute( insert(card_service_employees), insert_data ) await self.session.flush() await self.session.commit() return CardUpdateServiceQuantityResponse(ok=True, message='Услуга успешно обновлена') except Exception as e: await self.session.rollback() return CardUpdateServiceQuantityResponse(ok=False, message=str(e)) async def copy_services( self, user: UserUnion, request: CardServicesCopyRequest ) -> CardServicesCopyResponse: try: verify_user_deal_editor(user, request.card_id) source_services_stmt = ( select( CardProductService ) .where( CardProductService.product_id == request.source_product_id, CardProductService.card_id == request.card_id, ) ) card_product_services = ( ( await self.session.scalars( source_services_stmt ) ) .all() ) destination_card_products_stmt = ( select( CardProduct ) .where( CardProduct.product_id.in_(request.destination_product_ids), CardProduct.card_id == request.card_id ) ) destination_card_products = (await self.session.scalars(destination_card_products_stmt)).all() insert_data = [] for card_product in destination_card_products: for service in card_product_services: insert_data.append({ 'card_id': request.card_id, 'product_id': card_product.product_id, 'service_id': service.service.id, 'price': service.price, 'is_fixed_price': service.is_fixed_price }) if not insert_data: return CardServicesCopyResponse(ok=True, message='Услуги успешно перенесены') # Deleting previous CardProductService-s delete_stmt = ( delete( CardProductService ) .where( CardProductService.product_id.in_(request.destination_product_ids), CardProductService.card_id == request.card_id ) ) await self.session.execute(delete_stmt) await self.session.flush() await self.session.execute( insert(CardProductService), insert_data ) await self.session.flush() await self.session.commit() return CardServicesCopyResponse(ok=True, message='Услуги успешно перенесены') except Exception as e: return CardServicesCopyResponse(ok=False, message=str(e)) @staticmethod def get_service_price(service: Service, quantity: int): price = 0 price_ranges: list[ServicePriceRange] = service.price_ranges for price_range in price_ranges: if price_range.from_quantity <= quantity <= price_range.to_quantity: price = price_range.price break if not price and len(price_ranges) > 0: price = price_ranges[0].price if not price: price = service.price return price # endregion # region Card products async def update_product_quantity( self, user: UserUnion, request: CardUpdateProductQuantityRequest ) -> CardUpdateProductQuantityResponse: try: verify_user_deal_editor(user, request.card_id) # check if there is no card or no product with different exceptions card_product = await self.session.scalar( select(CardProduct) .where(CardProduct.card_id == request.card_id, CardProduct.product_id == request.product_id) ) if not card_product: raise HTTPException(status_code=404, detail="Карточка или товар не найден") card_product.quantity = request.quantity await self.session.commit() return CardUpdateProductQuantityResponse(ok=True, message='Количество успешно обновлено') except Exception as e: await self.session.rollback() return CardUpdateProductQuantityResponse(ok=False, message=str(e)) async def add_product( self, user: UserUnion, request: CardAddProductRequest ) -> CardAddProductResponse: try: verify_user_deal_editor(user, request.card_id) card = await self.session.scalar(select(Card).where(Card.id == request.card_id)) if not card: raise HTTPException(status_code=404, detail="Карточка не найдена") product = await self.session.scalar( select(Product).where(Product.id == request.product.product.id)) if not product: raise HTTPException(status_code=404, detail="Товар не найден") # Preventing duplicates card_product = await self.session.scalar( select(CardProduct) .where(CardProduct.card_id == request.card_id, CardProduct.product_id == request.product.product.id) ) if card_product: raise HTTPException(status_code=400, detail="Товар уже добавлен") card_product = CardProduct( card_id=request.card_id, product_id=request.product.product.id, quantity=request.product.quantity ) self.session.add(card_product) await self.session.flush() for service in request.product.services: card_product_service = CardProductService( card_id=request.card_id, product_id=request.product.product.id, service_id=service.service.id, price=service.price ) self.session.add(card_product_service) await self.session.commit() return CardAddProductResponse(ok=True, message='Товар успешно добавлен') except Exception as e: await self.session.rollback() return CardAddProductResponse(ok=False, message=str(e)) async def delete_product( self, user: UserUnion, request: CardDeleteProductRequest ) -> CardDeleteProductResponse: try: verify_user_deal_editor(user, request.card_id) card_product = await self.session.scalar( select(CardProduct) .where(CardProduct.card_id == request.card_id, CardProduct.product_id == request.product_id) ) if not card_product: raise HTTPException(status_code=404, detail="Карточка не найдена") await self.session.delete(card_product) await self.session.commit() return CardDeleteProductResponse(ok=True, message='Товар успешно удален') except Exception as e: await self.session.rollback() return CardDeleteProductResponse(ok=False, message=str(e)) async def delete_products( self, user: UserUnion, request: CardDeleteProductsRequest ) -> CardDeleteProductsResponse: try: verify_user_deal_editor(user, request.card_id) card_products = await self.session.scalars( select(CardProduct) .where(CardProduct.card_id == request.card_id, CardProduct.product_id.in_(request.product_ids)) ) for card_product in card_products: await self.session.delete(card_product) await self.session.commit() return CardDeleteProductsResponse(ok=True, message='Товары успешно удалены') except Exception as e: await self.session.rollback() return CardDeleteProductsResponse(ok=False, message=str(e)) async def update_product( self, user: UserUnion, request: CardUpdateProductRequest ): try: verify_user_deal_editor(user, request.card_id) card_product: CardProduct = await self.session.scalar( select(CardProduct) .where(CardProduct.card_id == request.card_id, CardProduct.product_id == request.product.product.id) ) if not card_product: raise HTTPException(status_code=404, detail="Указанный товар не найден") # getting new services and deleted database_services = set([service.service_id for service in card_product.services]) request_services = set([service.service.id for service in request.product.services]) new_services = request_services.difference(database_services) deleted_services = database_services.difference(request_services) services_dict = {service.service.id: service for service in request.product.services} # Deleting and updating existing services for service in card_product.services: service: CardProductService if service.service_id in deleted_services: await self.session.delete(service) await self.session.flush() continue request_service = services_dict[service.service_id] service.price = request_service.price service.is_fixed_price = request_service.is_fixed_price await self.session.flush() # Creating services for service in request.product.services: if service.service.id not in new_services: continue card_product_service = CardProductService( card_id=request.card_id, product_id=request.product.product.id, service_id=service.service.id, price=service.price ) self.session.add(card_product_service) await self.session.flush() # Updating product card_product.quantity = request.product.quantity card_product.comment = request.product.comment # Updating deleting old employees delete_stmt = ( delete( card_product_service_employees ) .where( card_product_service_employees.c.card_id == request.card_id, card_product_service_employees.c.service_id.in_(request_services.union(database_services)), card_product_service_employees.c.product_id == request.product.product.id ) ) await self.session.execute(delete_stmt) await self.session.flush() insert_data = [] for product_service in request.product.services: product_service: CardProductServiceSchema for employee in product_service.employees: insert_data.append({ 'card_id': request.card_id, 'service_id': product_service.service.id, 'product_id': request.product.product.id, 'user_id': employee.id }) if insert_data: await self.session.execute(insert(card_product_service_employees), insert_data) await self.session.flush() await self.session.commit() return CardUpdateProductResponse(ok=True, message='Товар успешно обновлен') except Exception as e: await self.session.rollback() return CardUpdateProductResponse(ok=False, message=str(e)) async def add_kit_to_card_product( self, user: UserUnion, request: CardProductAddKitRequest ) -> CardProductAddKitResponse: try: verify_user_deal_editor(user, request.card_id) service_service = ServiceService(self.session) kit = await service_service.get_kit_by_id(request.kit_id) if not kit: return CardProductAddKitResponse(ok=False, message='Указанный набор услуг не найден') services: list[Service] = kit.services card_product_stmt = ( select( CardProduct ) .where( CardProduct.card_id == request.card_id, CardProduct.product_id == request.product_id ) ) card_product: Optional[CardProduct] = await self.session.scalar(card_product_stmt) if not card_product: return CardProductAddKitResponse(ok=False, message='Указанный товар не найден') insert_data = [] for service in services: service_price = self.get_service_price(service, card_product.quantity) insert_data.append({ 'card_id': request.card_id, 'product_id': card_product.product_id, 'service_id': service.id, 'price': service_price, }) if not insert_data: return CardProductAddKitResponse(ok=True, message='Набор услуг успешно добавлен к товару') # Deleting previous CardProductService-s delete_stmt = ( delete( CardProductService ) .where( CardProductService.product_id == card_product.product_id, CardProductService.card_id == request.card_id ) ) await self.session.execute(delete_stmt) await self.session.flush() await self.session.execute( insert(CardProductService), insert_data ) await self.session.flush() await self.session.commit() return CardProductAddKitResponse(ok=True, message='Набор услуг успешно добавлен к товару') except Exception as e: return CardProductAddKitResponse(ok=False, message=str(e)) # endregion async def _get_final_status_for_card(self, card_id: int) -> Optional[CardStatus]: stmt = ( select(CardStatus) .join(Board) .join(Card) .where(Card.id == card_id, CardStatus.is_finishing == True) ) card_status: Optional[CardStatus] = (await self.session.scalars(stmt)).one_or_none() return card_status async def complete(self, user: User, request: CardCompleteRequest) -> CardCompleteResponse: try: # check for admin if not user.is_admin: return CardCompleteResponse(ok=False, message='Завершить может только администратор') card = await self._get_card_by_id(request.card_id) if not card: return CardCompleteResponse(ok=False, message="Карточка не найдена") if card.group: await card_group.CardGroupService(self.session).complete_group(card.group.id) else: card.is_completed = True final_status: Optional[CardStatus] = await self._get_final_status_for_card(card.id) if final_status: await self.change_status(card, final_status.id, user) await self.session.commit() return CardCompleteResponse(ok=True, message="Успешно завершена") except Exception as e: await self.session.rollback() return CardCompleteResponse(ok=False, message=str(e)) async def get_quantity_dict(self, cards: List[Card]): services_quantity = defaultdict(lambda: 0) for card in cards: for product in card.products: product: CardProduct for service in product.services: service: CardProductService services_quantity[service.service_id] += product.quantity for service in card.services: service: CardService services_quantity[service.service_id] += service.quantity return services_quantity async def _recalculate_price_single(self, card: Card, quantity_dict: dict): services_quantity = quantity_dict services_prices = {} for product in card.products: for service in product.services: if service.is_fixed_price: continue quantity = services_quantity[service.service_id] if service.service_id in services_prices: service.price = services_prices[service.service_id] continue price = self.get_service_price( service=service.service, quantity=quantity ) service.price = price services_prices[service.service_id] = price for service in card.services: service: CardService if service.is_fixed_price: continue quantity = services_quantity[service.service_id] price = self.get_service_price( service=service.service, quantity=quantity ) print(service.service_id, price) service.price = price async def _recalculate_price_group(self, group: CardGroup): cards = await self.session.scalars( select(Card) .join(card_relations, Card.id == card_relations.c.card_id) .options( selectinload(Card.services) .joinedload(CardService.service), selectinload(Card.products) .selectinload(CardProduct.services) .joinedload(CardProductService.service), noload(Card.group) ) .where(card_relations.c.group_id == group.id) ) cards = list(cards.all()) services_quantity = await self.get_quantity_dict(cards) for card in cards: await self._recalculate_price_single(card, services_quantity) async def recalculate_price(self, request: CardRecalculatePriceRequest) -> CardRecalculatePriceResponse: try: card_stmt = ( select( Card ) .options( selectinload(Card.services) .joinedload(CardService.service), selectinload(Card.products) .selectinload(CardProduct.services) .joinedload(CardProductService.service), joinedload(Card.group) ) .where(Card.id == request.card_id) ) card: Card = await self.session.scalar(card_stmt) if not card.group: quantity_dict = await self.get_quantity_dict([card]) await self._recalculate_price_single(card, quantity_dict) else: await self._recalculate_price_group(card.group) await self.session.commit() return CardRecalculatePriceResponse(ok=True, message="Цены успешно пересчитаны") except Exception as e: return CardRecalculatePriceResponse(ok=False, message=str(e)) async def _assign_employee(self, card: Card, user: User) -> tuple[bool, str]: assigned_employee_ids = [assignment.user_id for assignment in card.employees] if user.id in assigned_employee_ids: return False, "Работник уже назначен" assignment = CardEmployees(user_id=user.id, card_id=card.id, created_at=datetime.now()) self.session.add(assignment) await self.session.commit() return True, "Работник успешно назначен" async def _unassign_employee(self, card: Card, user: User) -> tuple[bool, str]: assigned_employee_ids = [assignment.user_id for assignment in card.employees] if user.id not in assigned_employee_ids: return False, "Работник еще не назначен" stmt = delete(CardEmployees).where(and_(CardEmployees.user_id == user.id, CardEmployees.card_id == card.id)) await self.session.execute(stmt) await self.session.commit() return True, "Работник успешно удален" async def manage_employee(self, request: ManageEmployeeRequest) -> ManageEmployeeResponse: card: Optional[Card] = await self._get_card_by_id(request.card_id) if not card: return ManageEmployeeResponse(ok=False, message=f"Карточка с ID {request.card_id} не найдена") user: Optional[User] = await self.session.get(User, request.user_id) if not user: return ManageEmployeeResponse(ok=False, message=f"Пользователь с ID {request.user_id} не найден") if request.is_assign: ok, message = await self._assign_employee(card, user) else: ok, message = await self._unassign_employee(card, user) return ManageEmployeeResponse(ok=ok, message=message) async def get_available_employees_to_assign(self, card_id: int) -> GetAvailableEmployeesToAssignResponse: assigned_users = select(CardEmployees.user_id).where(CardEmployees.card_id == card_id) stmt_free_employees = ( select(User) .where(and_( User.is_deleted == False, User.role_key == "employee", User.id.not_in(assigned_users), )) ) free_employees = (await self.session.execute(stmt_free_employees)).scalars().all() return GetAvailableEmployeesToAssignResponse(employees=free_employees) async def _create_card_from_excel( self, client: Client, card_status: CardStatus, breakdown: CityBreakdownFromExcelSchema, user: User, ) -> Card: rank = await self._get_rank_for_card(card_status.id) card = Card( name=f"{client.name} - {breakdown.base_marketplace.key.upper()} - {breakdown.shipping_warehouse.name}", created_at=datetime.now(), current_status_id=card_status.id, lexorank=rank, client_id=client.id, base_marketplace_key=breakdown.base_marketplace.key, shipping_warehouse_id=breakdown.shipping_warehouse.id, ) self.session.add(card) await self.session.flush() return card async def _get_or_create_warehouse( self, shipping_warehouse: OptionalShippingWarehouseSchema, ) -> OptionalShippingWarehouseSchema: if not shipping_warehouse.id: stmt = select(ShippingWarehouse).where(ShippingWarehouse.name == shipping_warehouse.name) row = (await self.session.execute(stmt)).first() warehouse_model: Optional[ShippingWarehouse] = row[0] if row else None if warehouse_model: shipping_warehouse.id = warehouse_model.id else: warehouse = await ShippingWarehouseService(self.session).create_by_name(shipping_warehouse.name) shipping_warehouse.id = warehouse.id return shipping_warehouse async def create_cards_from_excel( self, request: CreateCardsFromExcelRequest, user: User, ) -> CreateCardsFromExcelResponse: client: Optional[Client] = await self.session.get(Client, request.client_id) if not client: return CreateCardsFromExcelResponse(ok=False, message=f"Клиент с ID {request.client_id} не найден") card_status: Optional[CardStatus] = await self.session.get(CardStatus, request.status_id) if not card_status: return CreateCardsFromExcelResponse(ok=False, message=f"Статус с ID {request.status_id} не найден") cards_dict: dict[str, Card] = {} group = await card_group.CardGroupService(self.session).create_group_model() for product_data in request.products: for breakdown in product_data.cities_breakdown: breakdown.shipping_warehouse = await self._get_or_create_warehouse(breakdown.shipping_warehouse) key = f"{breakdown.shipping_warehouse.id} - {breakdown.base_marketplace.key}" card = cards_dict.get(key) if not card: card = await self._create_card_from_excel(client, card_status, breakdown, user) cards_dict[key] = card insert_stmt = insert(card_relations).values({ 'card_id': card.id, 'group_id': group.id }) await self.session.execute(insert_stmt) card_product = CardProduct( card_id=card.id, product_id=product_data.product_id, quantity=breakdown.quantity, ) self.session.add(card_product) await self.session.commit() return CreateCardsFromExcelResponse(ok=True, message="Карточки успешно созданы") async def get_cards_grouped(self, card: Card) -> List[Card]: if not card.group: return [card] cards = await self.session.scalars( select(Card) .options( selectinload(Card.services) .joinedload(CardService.service), selectinload(Card.products) .selectinload(CardProduct.services) .joinedload(CardProductService.service), ) .where(Card.group == card.group) ) cards = list(cards.all()) return cards