from db import db from datetime import datetime, date, timezone from sqlalchemy.sql import func from utils.list_utils import chunks from workers.base_worker import BaseWorker from models.ccp.raw_ccp_budgets import RawCCPBudgets from models.ccp.raw_ccp_projects import RawCCPProjects from models.ccp.raw_ccp_purchase_orders import RawCCPPurchaseOrders from models.user import User from models.projects import Project, ProjectTargetItem from models.artists import Artist from models.images import Image from models.project_entity_type import ProjectEntityType, ProjectEntityAddType from models.profit_center import ProfitCenter from projects.constants import MAX_PRIMARY_ARTISTS, MAX_FEATURED_ARTISTS from projects.services.project_history_service import ProjectHistoryService from config import ARTIST_IMAGE_SERVICE_URL, EARLIEST_PROJECTS_IMPORT_START_DATE class Queries: @staticmethod def projects_query(): start_date = func.coalesce(func.min(RawCCPPurchaseOrders.creation_date).label("start_date"), date.today()) return ( db.session.query( RawCCPProjects.gras_project_code, RawCCPProjects.project_budget_code, RawCCPProjects.description.label("title"), RawCCPProjects.artist_name, RawCCPProjects.artist_code, RawCCPProjects.rep_owner_key, func.array_agg( func.distinct( func.coalesce( func.trim(RawCCPPurchaseOrders.paid_line_item_amount_currency), func.trim(RawCCPPurchaseOrders.line_item_total_amount_currency), func.trim(RawCCPBudgets.currency), ) ) ).label("currency"), start_date.label("start_date"), func.max( func.coalesce( RawCCPPurchaseOrders.last_modified_date, RawCCPPurchaseOrders.creation_date, date.today(), ) ).label("end_date"), ProfitCenter.label_id.label("label_id"), ) .select_from(RawCCPProjects) .outerjoin( RawCCPPurchaseOrders, RawCCPPurchaseOrders.project_budget_code == RawCCPProjects.project_budget_code ) .join(RawCCPBudgets, RawCCPBudgets.project_budget_code == RawCCPProjects.project_budget_code) .join(ProfitCenter, ProfitCenter.code == RawCCPProjects.profit_center_code) .having(start_date >= EARLIEST_PROJECTS_IMPORT_START_DATE) .group_by( RawCCPProjects.gras_project_code, RawCCPProjects.project_budget_code, RawCCPProjects.title, RawCCPProjects.artist_name, RawCCPProjects.artist_code, RawCCPProjects.rep_owner_key, ProfitCenter.label_id, ) ) @staticmethod def get_ccp_project_by_external_id(ccp_project_code: str): return db.session.query(Project).filter(Project.ccp_project_code == ccp_project_code) class CCPProjectsWorker(BaseWorker): worker_name = "CCPProjectsWorker" assigned_project_history_service = ProjectHistoryService() chunk_size = 500 def should_log_exceptions(self): return True def execute(self): projects = Queries.projects_query().all() ccp_user = db.session.query(User).filter(User.email == "ccp@ccp.com", User.is_service_account.is_(True)).one() self.logger.info("CCP Projects import", f"Importing {len(projects)} projects...") iteration = 1 for chunk in chunks(projects, self.chunk_size): self.__import_batch(ccp_user, chunk) iteration = iteration + 1 db.session.close() self.logger.success("CCP Projects import", "Finished!") def __import_batch(self, ccp_user, projects): for ccp_project in projects: existing_ccp_project = Queries.get_ccp_project_by_external_id(ccp_project.project_budget_code).one_or_none() if existing_ccp_project: self.__update_project(ccp_project, existing_ccp_project, ccp_user) else: new_project = self.__create_project(ccp_project, ccp_user) db.session.add(new_project) db.session.flush() self.assigned_project_history_service.log_project_created(new_project, ccp_user.id) db.session.flush() db.session.commit() def __create_project(self, project, ccp_user): artist = self.get_artist(project.artist_code, project.artist_name) current_timestamp = datetime.now(timezone.utc) external_project = Project() external_project.ccp_title = project.title external_project.ccp_project_code = project.project_budget_code external_project.gras_title = project.title external_project.gras_project_code = str(project.gras_project_code) external_project.target_items = [self.project_target_item(artist)] external_project.label_id = project.label_id external_project.created_at = current_timestamp external_project.updated_at = current_timestamp external_project.last_edit_at = current_timestamp external_project.initial_start_date = project.start_date external_project.end_date = project.end_date external_project.create_user_id = ccp_user.id external_project.edit_user_id = ccp_user.id external_project.updated_by_import_at = current_timestamp external_project.source = "CCP" external_project.currency = project.currency[0] return external_project def __update_project(self, ccp_project, project, ccp_user): state = self.assigned_project_history_service.get_project_state(project) current_timestamp = datetime.now(timezone.utc) project.updated_by_import_at = current_timestamp project.ccp_project_code = ccp_project.project_budget_code project.ccp_title = ccp_project.title project.label_id = ccp_project.label_id project.currency = ccp_project.currency[0] if db.session.is_modified(project): project.updated_at = current_timestamp project.last_edit_at = current_timestamp if not project.is_claimed: project.initial_start_date = project.start_date project.end_date = project.end_date project.target_items = self.resolve_artists_for_project(project, ccp_project) self.assigned_project_history_service.log_project_changes(project, state, ccp_user.id) Project.index_model(project) def get_artist(self, artist_external_id, artist_name): artist_id = f"GRAS_{artist_external_id}" artist = db.session.query(Artist).filter(Artist.external_id == artist_id).one_or_none() if artist: return artist else: artist = Artist(external_id=artist_id, name=artist_name) artist.images = [ Image(url=ARTIST_IMAGE_SERVICE_URL.format(id=artist_external_id)) ] db.session.add(artist) return artist def project_target_item(self, artist: Artist): project_artist = ProjectTargetItem(artist, ProjectEntityType.PRIMARY_ARTIST) project_artist.add_type = ProjectEntityAddType.LOCKED.value return project_artist def resolve_artists_for_project(self, project: Project, ccp_project): def __convert_to_featured(target_item): target_item.entity_type = ProjectEntityType.FEATURED_ARTIST.value return target_item artist = self.get_artist(ccp_project.artist_code, ccp_project.artist_name) # get CCP and GRAS artists from current project project_target_items = project.target_items non_ccp_project_artists = list(filter(lambda x: not x.is_locked(), project_target_items)) ccp_project_artists = list(filter(lambda x: x.is_locked(), project_target_items)) # get featured and primary artists for current project project_primary_artists = list(filter(lambda x: x.is_primary_artist(), non_ccp_project_artists)) project_featured_artists = list(filter(lambda x: x.is_featured_artist(), non_ccp_project_artists)) # filter primary artists to not include artists from ccp project_primary_artists = list(filter(lambda x: x.entity != artist, project_primary_artists)) # get GRAS artists that identical to the ccp artists to_delete_primary_artists = list(filter(lambda x: x.entity == artist, project_primary_artists)) to_delete_featured_artists = list(filter(lambda x: x.entity == artist, project_featured_artists)) ccp_target_items = [self.project_target_item(artist)] template_project_primary_artists = ccp_target_items + project_primary_artists new_primary_artists = template_project_primary_artists[:MAX_PRIMARY_ARTISTS] converted_primary = list(map(__convert_to_featured, template_project_primary_artists[MAX_PRIMARY_ARTISTS:])) new_featured_artists = converted_primary + project_featured_artists # remove existing ccp artists cause we re-adding them [db.session.delete(item) for item in ccp_project_artists] # remove artists from GRAS but identical to the ccp artists [db.session.delete(item) for item in to_delete_primary_artists] [db.session.delete(item) for item in to_delete_featured_artists] # remove extra featured artists if needed extra_items_start_index = MAX_FEATURED_ARTISTS - len(converted_primary) [db.session.delete(item) for item in new_featured_artists[extra_items_start_index:MAX_FEATURED_ARTISTS]] return new_primary_artists + new_featured_artists[:MAX_FEATURED_ARTISTS]