def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind() for row in list(conn.execute('SELECT id, leaders FROM workshop')): if not row.leaders: continue try: leaders_list = json.loads(row.leaders) except ValueError: # This should not happen, but some dumps we use have a bare string # in leaders field. leaders_list = [row.leaders] req = sa.text(""" UPDATE workshop SET description=CONCAT( 'Formateurs: ', IFNULL(:leaders, ''), ' ', IFNULL(description, '') ) WHERE id=:id_ """) conn.execute( req, leaders=', '.join(leaders_list), id_=row.id, ) mark_changed(session) session.flush()
def get_types(cls, active=True, keys=()): query = DBSESSION().query(cls) if keys: query = query.options(load_only(*keys)) if active: query = query.filter_by(active=True) return query
def authenticate(cls, user_cls, login, password): result = DBSESSION().query(user_cls).filter( user_cls.login == login ).first() if result is not None and result.auth(password): return result.id return None
def query(self): """ Return the main query for our list view """ logger.debug("Queryiing") query = DBSESSION().query(distinct(User.id), User) return query.outerjoin(User.companies)
def connection(request, settings): """ sets up a SQLAlchemy engine and returns a connection to the database. :param settings: the settings of the test (given by the testing fixture) :returns: a sqlalchemy connection object """ # the following setup is based on `kotti.resources.initialize_sql`, # except that it explicitly binds the session to a specific connection # enabling us to use savepoints independent from the orm, thus allowing # to `rollback` after using `transaction.commit`... initialize_test_database(settings) from autonomie_base.models.base import DBSESSION, DBBASE engine = engine_from_config(settings, prefix='sqlalchemy.') _connection = engine.connect() DBSESSION.registry.clear() DBSESSION.configure(bind=_connection) DBBASE.metadata.bind = engine def drop_db(): """ drop the test database """ print("DROPPING DB") if __current_test_ini_file().endswith('travis.ini'): return db_settings = get_test_options_from_settings(settings) launch_cmd(db_settings['drop']) request.addfinalizer(drop_db) return _connection
def deferred_categories_widget(node, kw): query = DBSESSION().query( IncomeStatementMeasureTypeCategory.label, IncomeStatementMeasureTypeCategory.label, ) choices = query.filter_by(active=True).all() return deform.widget.CheckboxChoiceWidget(values=choices)
def find_by_login(cls, login, active=True): query = DBSESSION().query(cls) query = query.options(load_only('pwd_hash')) query = query.filter_by(login=login) if active: query = query.filter_by(active=True) return query.first()
def get_cancelinvoices(cls, instance, valid=False): from autonomie.models.task import CancelInvoice query = DBSESSION().query(CancelInvoice) query = query.filter(CancelInvoice.company_id == instance.id) if valid: query = query.filter(CancelInvoice.status == 'valid') return query
def query_existing_business_indicators( cls, business_id, file_type_id, task_id=None ): """ Build a query for indicators related to a given business Excludes indicators related to task_id :param int business_id: The business id :param int file_type_id: The type of file the indicators are related to :param int task_id: The id of the task to exclude from the query """ from autonomie.models.task import Task tasks_id_query = DBSESSION().query(Task.id).filter_by( business_id=business_id ) if task_id is not None: tasks_id_query = tasks_id_query.filter(Task.id != task_id) query = SaleFileRequirement.query().filter_by( file_type_id=file_type_id ) return query.filter( or_( SaleFileRequirement.node_id == business_id, SaleFileRequirement.node_id.in_(tasks_id_query) ) )
def upgrade(): op.add_column('payment', sa.Column('created_at', sa.DateTime())) op.add_column('payment', sa.Column('updated_at', sa.DateTime())) op.add_column('payment', sa.Column('bank_id', sa.Integer(), nullable=True)) op.add_column('payment', sa.Column('exported', sa.Boolean(), default=False)) op.add_column('payment', sa.Column( 'tva_id', sa.Integer(), sa.ForeignKey('tva.id'), nullable=True, )) op.add_column( 'payment', sa.Column('remittance_amount', sa.Integer(), nullable=True) ) op.add_column( 'tva', sa.Column('compte_a_payer', sa.String(125), default='') ) from autonomie_base.models.base import DBSESSION from autonomie.models.task import Payment session = DBSESSION() for payment in Payment.query(): payment.remittance_amount = payment.amount payment.exported = True payment.created_at = payment.updated_at = payment.date session.merge(payment)
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind() from autonomie.models.config import Config Config.set('accounting_label_maxlength', 35) session.flush()
def get_estimations(cls, instance, valid=False): from autonomie.models.task import Estimation query = DBSESSION().query(Estimation) query = query.filter(Estimation.company_id == instance.id) if valid: query = query.filter(Estimation.status == 'valid') return query
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from autonomie.models.accounting.operations import AccountingOperationUpload for entry in AccountingOperationUpload.query(): entry.filetype = "analytical_balance" session.merge(entry) session.flush()
def query(self): company = self.request.context # We can't have projects without having customers if not company.customers: redirect_to_customerslist(self.request, company) main_query = DBSESSION().query(distinct(Project.id), Project) main_query = main_query.outerjoin(Project.customers) return main_query.filter(Project.company_id == company.id)
def query_for_select(cls): """ Query project types for selection purpose """ query = DBSESSION().query(cls).options( load_only('id', 'label', 'private') ) query = query.filter_by(active=True) return query
def get_years(cls, grid_class, company_id=None): query = DBSESSION().query(distinct(grid_class.year)) if company_id is not None: query = query.filter_by(company_id=company_id) result = [a[0] for a in query.all()] result.sort() return result
def active_company_ids(self): """ Return only enabled companies ids """ from autonomie.models.company import Company query = DBSESSION().query(COMPANY_EMPLOYEE.c.company_id) query = query.filter(COMPANY_EMPLOYEE.c.account_id == self.id) query = query.join(Company).filter(Company.active == True) return [c[0] for c in query]
def has_userdatas(self): """ Return True if the current object has userdatas associated to it """ from autonomie.models.user.userdatas import UserDatas query = DBSESSION().query(UserDatas.id) query = query.filter(UserDatas.user_id == self.id) count = query.count() return count >= 1
def get_by_internal_id(cls, internal_id): query = DBSESSION().query(cls.id).filter_by(internal_id=internal_id) query = query.first() if query is not None: result = query[0] else: result = None return result
def is_used(self): query = DBSESSION().query(File).filter_by(file_type_id=self.id) file_exists = DBSESSION().query(query.exists()).scalar() from autonomie.models.indicators import SaleFileRequirement indicator_query = DBSESSION().query(SaleFileRequirement).filter_by( file_type_id=self.id ) indicator_exists = DBSESSION().query(indicator_query.exists()).scalar() return file_exists or indicator_exists
def get_invoices(cls, instance, valid=False, not_paid=False): from autonomie.models.task import Invoice query = DBSESSION().query(Invoice) query = query.filter(Invoice.company_id == instance.id) if valid: query = query.filter(Invoice.status == 'valid') elif not_paid: query = query.filter(Invoice.status == 'valid') query = query.filter(Invoice.paid_status.in_(('paid', 'waiting'))) return query
def migrate_datas(): logger = logging.getLogger("alembic.autonomie") from autonomie_base.models.base import DBSESSION session = DBSESSION() from autonomie.models.config import Config import json from autonomie.models.files import ( File, FileType, ) json_str = Config.get_value("attached_filetypes", "[]") try: configured_filetypes = json.loads(json_str) except: logger.exception(u"Error in json str : %s" % json_str) configured_filetypes = [] if configured_filetypes: result = [] for filetype_label in configured_filetypes: if filetype_label: filetype = FileType(label=filetype_label) session.add(filetype) session.flush() result.append(filetype) for typ_ in result: query = File.query().filter_by(label=typ_.label) for file_ in query: file_.file_type_id = typ_.id session.merge(file_) session.flush()
def upgrade(): from autonomie.models.task import Invoice, CancelInvoice, ManualInvoice from autonomie_base.models.base import DBSESSION for table in "invoice", "cancelinvoice", "manualinv": if not column_exists(table, "financial_year"): op.add_column(table, sa.Column("financial_year", sa.Integer, nullable=False)) for type_ in (Invoice, CancelInvoice, ManualInvoice): for document in type_.query(): document.financial_year = document.taskDate.year DBSESSION.merge(document)
def taskyears(): """ return the distinct financial years available in the database """ query = DBSESSION().query(distinct(Invoice.financial_year)) query = query.order_by(Invoice.financial_year) years = [year[0] for year in query] current = datetime.date.today().year if current not in years: years.append(current) return years
def deferred_company_id_widget(node, kw): """ Defer the company id selection widget """ datas = DBSESSION().query( distinct(AccountingOperation.company_id) ).all() datas = zip(*datas)[0] values = DBSESSION().query(Company.id, Company.name).all() values.insert(0, ('', u"Toutes les entreprises")) return deform.widget.Select2Widget(values=values)
def deferred_company_choices(node, kw): """ return a deferred company selection widget """ values = DBSESSION().query(Company.id, Company.name).all() if default_option: values.insert(0, default_option) return deform.widget.Select2Widget( values=values, **widget_options )
def _query_mentions(cls, btype_id, doctype): query = DBSESSION().query(TaskMention) query = query.outerjoin(TaskMention.business_type_rel) query = query.filter(TaskMention.active == True) query = query.filter( BusinessTypeTaskMention.business_type_id == btype_id ) query = query.filter( BusinessTypeTaskMention.doctype == doctype ) return query
def add_customer(**kw): #company, customer_name, customer_code, customer_lastname): customer = Customer(**kw) session = DBSESSION() session.add(customer) session.flush() print u"Added customer to %s: %s" % ( customer.company.name, customer.name) return customer
def get_tasks(cls, instance, offset=None, limit=None): from autonomie.models.task import Task query = DBSESSION().query(Task) query = query.filter(Task.company_id == instance.id) query = query.filter( Task.type_.in_(('invoice', 'estimation', 'cancelinvoice'))) query = query.order_by(desc(Task.status_date)) if offset is not None: query = query.offset(offset) if limit is not None: query = query.limit(limit) return query
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind() from autonomie.models.task import Task op.execute(""" UPDATE task LEFT JOIN invoice on task.id = invoice.id LEFT JOIN cancelinvoice on cancelinvoice.id = task.id SET official_number = CONCAT(IFNULL(prefix, ''), official_number) WHERE (cancelinvoice.id IS NOT NULL) OR (invoice.id IS NOT NULL) ;""")
def query_existing_business_indicators(cls, business_id, file_type_id, task_id=None): """ Build a query for indicators related to a given business Excludes indicators related to task_id :param int business_id: The business id :param int file_type_id: The type of file the indicators are related to :param int task_id: The id of the task to exclude from the query """ from autonomie.models.task import Task tasks_id_query = DBSESSION().query( Task.id).filter_by(business_id=business_id) if task_id is not None: tasks_id_query = tasks_id_query.filter(Task.id != task_id) query = SaleFileRequirement.query().filter_by( file_type_id=file_type_id) return query.filter( or_(SaleFileRequirement.node_id == business_id, SaleFileRequirement.node_id.in_(tasks_id_query)))
def migrate_datas(): from autonomie_base.models.base import DBSESSION from autonomie.models.config import Config session = DBSESSION() from alembic.context import get_bind conn = get_bind() deprecated_conf_keys = [ 'compte_cgscop', 'compte_cg_debiteur', 'compte_cg_organic', 'compte_cg_debiteur_organic', 'compte_cg_assurance', 'taux_assurance', 'taux_cgscop', 'taux_contribution_organic', 'sage_assurance', 'sage_cgscop', 'sage_organic', ] q = Config.query().filter(Config.name.in_(deprecated_conf_keys)) q.delete(synchronize_session=False) session.flush()
def upgrade(): op.add_column('bank_account', sa.Column('code_journal', sa.String(120), nullable=False)) req = "select config_value from config where config_name='receipts_code_journal';" from alembic.context import get_bind from autonomie_base.models.base import DBSESSION conn = get_bind() res = conn.execute(req).scalar() if res is not None: req = "update bank_account set code_journal='%s'" % res conn.execute(req) session = DBSESSION() from zope.sqlalchemy import mark_changed mark_changed(session)
def update_invoicing_status(cls, business, invoice=None): """ Update the invoicing status of the deadline associated to this invoice :param obj business: The Business instance :param obj invoice: The validated Invoice instance :returns: The Business instance :rtype: obj """ if invoice: deadline = business.find_deadline_from_invoice(invoice) deadline.invoiced = True DBSESSION().merge(deadline) cls.update_invoicing_indicator(business)
def get_users_options(roles=None): """ Return the list of active users from the database formatted as choices: [(user_id, user_label)...] :param role: roles of the users we want default: all values : ('contractor', 'manager', 'admin')) """ query = DBSESSION().query(User).options( load_only('id', 'firstname', 'lastname') ) # Only User accounts with logins query = query.join(Login).filter(Login.active == True) query = query.order_by(User.lastname) if roles and not hasattr(roles, "__iter__"): roles = [roles] query = _filter_by_group(query, roles) return [(unicode(u.id), format_account(u)) for u in query]
def deferred_label_validator(node, kw): """ Deffered label validator, check whether a type or a category has the same label """ context = kw['request'].context category_query = DBSESSION().query( IncomeStatementMeasureTypeCategory.label) category_query.filter_by(active=True) if isinstance(context, IncomeStatementMeasureTypeCategory): category_query = category_query.filter( IncomeStatementMeasureTypeCategory.id != context.id) category_labels = [i[0] for i in category_query] type_query = DBSESSION().query(IncomeStatementMeasureType.label) type_query.filter_by(active=True) if isinstance(context, IncomeStatementMeasureType): type_query = type_query.filter( IncomeStatementMeasureType.id != context.id) type_labels = [i[0] for i in type_query] def label_validator(node, value): if ':' in value or '!' in value: raise colander.Invalid( node, u"Erreur de syntax (les caractères ':' et '!' sont interdits") if value in category_labels: raise colander.Invalid(node, u"Une catégories porte déjà ce nom") if value in type_labels: raise colander.Invalid(node, u"Un type d'indicateurs porte déjà ce nom") return label_validator
def set_file(self, file_id, validated=False): """ Attach a file_id to this indicator :param int file_id: An id of a file persisted to database :param bool validated: True if this file been validated in another indicator """ self.file_id = file_id if not validated and self.validation: self.validation_status = 'wait' self.status = 'warning' else: self.status = 'success' return DBSESSION().merge(self)
def translate_invoices(invoicequery, from_point): """ Translate invoice numbers to 'from_point' :param iter invoicequery: An iterable :param int from_point: from_point The first invoice will get from_point as official_number """ for invoice in invoicequery: invoice.official_number = from_point from_point += 1 DBSESSION().merge(invoice) return from_point
def upgrade(): from alembic.context import get_bind op.add_column( "user_datas", sa.Column( "situation_situation_id", sa.Integer, sa.ForeignKey("cae_situation_option.id"), )) op.add_column("configurable_option", sa.Column("order", sa.Integer, default=0)) from autonomie.models.user import ( CaeSituationOption, ) from autonomie_base.models.base import DBSESSION temp_dict = {} for key, value in SITUATION_OPTIONS: if key == "integre": option = CaeSituationOption(label=value, is_integration=True) else: option = CaeSituationOption(label=value) DBSESSION().add(option) DBSESSION().flush() temp_dict[key] = option.id conn = get_bind() query = "select id, situation_situation from user_datas" result = conn.execute(query) for id, situation in result: option_id = temp_dict.get(situation) if option_id is None: continue query = "update user_datas set situation_situation_id='{0}' \ where id='{1}'".format(option_id, id) op.execute(query)
def gen_invoice(self, payment_line, user): """ Generate an invoice based on a payment line :param obj payment_line: The payment line we ask an Invoice for :param obj user: User instance, the user generating the document :rtype: `class:Invoice` """ if payment_line == self.payment_lines[-1]: self.geninv = True DBSESSION().merge(self) return self._invoicing_service.gen_sold_invoice(self, user) else: return self._invoicing_service.gen_intermediate_invoice( self, payment_line, user)
def insert(cls, item, new_order): """ Place the item at the given index :param obj item: The item to move :param int new_order: The new index of the item """ query = cls._query_active_items() items = query.filter(cls.id != item.id).order_by(cls.order).all() items.insert(new_order, item) for index, item in enumerate(items): item.order = index DBSESSION().merge(item)
def persist_pdf(self, filename, pdf_buffer): """ Persist the pdf output of this task to the database :param obj pdf_buffer: A buffer (file, StringIO) :param str filename: The name of the pdf file """ from autonomie.models.files import File pdf_buffer.seek(0) self.pdf_file = File( name=filename, mimetype="application/pdf", ) self.pdf_file.data = pdf_buffer.read() DBSESSION().merge(self)
def add_expense_type(type_, **kwargs): if type_ == 'km': e = ExpenseKmType(**kwargs) elif type_ == 'tel': e = ExpenseTelType(**kwargs) else: e = ExpenseType(**kwargs) session = DBSESSION() session.add(e) session.flush()
def add_customer( **kw): #company, customer_name, customer_code, customer_lastname): customer = Customer(**kw) session = DBSESSION() session.add(customer) session.flush() print u"Added customer to %s: %s" % (customer.company.name, customer.name) return customer
def _get_customer_id(cls, business): """ Find the customer associated to this bussiness :param obj business: The business instance this service is attached to :returns: A Customer id :rtype: int """ from autonomie.models.task import Task result = DBSESSION().query(Task.customer_id).filter_by( business_id=business.id ).first() if result: return result[0] else: return None
def get_phase_acl(self): """ Return acl for a phase """ acl = DEFAULT_PERM[:] perms = ("edit.phase",) if DBSESSION().query(Task.id).filter_by(phase_id=self.id).count() == 0: perms += ('delete.phase',) else: acl.insert(0, (Deny, Everyone, ('delete.phase',))) for user in self.project.company.employees: acl.append((Allow, user.login.login, perms)) return acl
def get_phase_acl(self): """ Return acl for a phase """ acl = DEFAULT_PERM[:] perms = ("edit.phase", ) if DBSESSION().query(Task.id).filter_by(phase_id=self.id).count() == 0: perms += ('delete.phase', ) else: acl.insert(0, (Deny, Everyone, ('delete.phase', ))) company_id = FindCompanyService.find_company_id_from_node(self) acl.append((Allow, "company:{}".format(company_id), perms)) return acl
def get_turnover(cls, company, year): """ Compute the annual turnover for a given company """ from autonomie.models.task import ( Task, ) query = DBSESSION.query(func.sum(Task.ht)) query = query.filter(Task.company_id == company.id) query = query.filter(func.year(Task.date) == year) query = query.filter(Task.status == 'valid') invoice_sum = query.filter(Task.type_.in_( ('invoice', 'cancelinvoice'))).first()[0] if invoice_sum is None: invoice_sum = 0 return invoice_sum
def set_configuration(args, env): print("Adding configuration elements") add_payment_mode(u"par chèque") add_payment_mode(u"par virement") add_tva(0) add_tva(700) add_tva(1960, True) add_unity(u"heure(s)") add_unity(u"jour(s)") add_unity(u"mois") add_unity(u"forfait") add_expense_type("", label=u"Restauration", code='0001') add_expense_type("", label=u"Transport", code='0002') add_expense_type("", label=u"Matériel", code="0003") add_expense_type("km", label=u"Scooter", code="0004", amount='0.124') add_expense_type("km", label=u"Voiture", code="0005", amount="0.235") add_expense_type("tel", label=u"Adsl-Tel fix", code="0006", percentage="80") add_expense_type("tel", label=u"Mobile", code="0007", percentage="80") for i in (u'Rendez-vous mensuel', u'Entretien individuel'): add_activity_type(i) for i in ( u'par skype', u'en direct', u'par mail', u'par téléphone', ): add_activity_mode(i) a = add_activity_action( u"Projet FSE 2014 - Passerelle pour l'entreprenariat collectif", ) add_activity_action( u"Module 3 : Accompagnement renforcé - Etape : Business model \ commercial, économique et social", parent=a) session = DBSESSION() from autonomie.models import populate populate.populate_situation_options(session) populate.populate_groups(session)
def add_phase(project, phase_name): phase = Phase(name=phase_name) phase.project = project session = DBSESSION() session.add(phase) session.flush() print u"Added phase to %s: %s" % (project.name, phase_name) return phase
def upgrade(): op.execute(u"Alter table tva modify name VARCHAR(15)") op.execute(u"Alter table tva modify active tinyint(1)") op.add_column('tva', sa.Column('mention', sa.Text(), default='')) from autonomie.models.tva import Tva from autonomie_base.models.base import DBSESSION session = DBSESSION() for tva in session.query(Tva): if tva.value <= 0: tva.mention = u"TVA non applicable selon l'article 259b du CGI." session.merge(tva) else: tva.mention = u"TVA {0} %".format(tva.value / 100.0) session.merge(tva)
def add_project(customer, company, project_name, project_code): project = Project(name=project_name, code=project_code) project.customers.append(customer) project.company = company session = DBSESSION() session.add(project) session.flush() print u"Added project to %s for %s: %s" % (company.name, customer.name, project_name) return project
def dbsession(config, content, connection, request): """ returns a db session object and sets up a db transaction savepoint, which will be rolled back after the test. :returns: a SQLA session """ from transaction import abort trans = connection.begin() # begin a non-orm transaction def rollback(): print("ROLLING BACK") trans.rollback() print("ABORTING") abort() request.addfinalizer(rollback) from autonomie_base.models.base import DBSESSION return DBSESSION()
def populate_database(): """ Populate the database with default values """ logger.debug("Populating the database") session = DBSESSION() for func in ( populate_situation_options, populate_groups, populate_accounting_treasury_measure_types, ): try: func(session) except sqlalchemy.exc.OperationalError as e: print("The seem to be an error in the population process") print(e) commit()
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind() op.alter_column( "custom_invoice_book_entry_module", "percentage", type_=sa.Float(), nullable=False, ) op.alter_column( "task", "internal_number", type_=sa.String(255), nullable=False, )
def update_database_structure(): for table, col, default in ( ('workshop_action', 'active', True), ('tva', 'active', True), ('tva', 'default', False), ('product', 'active', True), ('project', 'archived', False), ('templates', 'active', True), ('statistic_sheet', 'active', True), ('cae_situation_option', 'is_integration', False), ('userdatas_socialdocs', 'status', False), ('external_activity_datas', 'employer_visited', False), ('task', 'round_floor', False), ('payment_conditions', 'default', False), ('invoice', 'exported', False), ('cancelinvoice', 'exported', False), ('payment', 'exported', False), ('bank_account', 'default', False), ('activity_type', 'active', True), ('activity_action', 'active', True), ('configurable_option', 'active', True), ('custom_invoice_book_entry_module', 'active', True), ('custom_invoice_book_entry_module', 'enabled', True), ('customer', 'archived', False), ('expense_type', 'active', True), ('expense_type', 'contribution', False), ('expensetel_type', 'initialize', False), ('expense_sheet', 'exported', False), ('baseexpense_line', 'valid', False), ('expense_payment', 'waiver', False), ('expense_payment', 'exported', False), ): print("Converting the %s.%s to boolean" % (table, col)) change_bool_column(table, col, default) for line in ALTER_LINES.splitlines(): line = line.strip() if line: op.execute(line) from zope.sqlalchemy import mark_changed from autonomie_base.models.base import DBSESSION session = DBSESSION() mark_changed(session)
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from autonomie.models.populate import populate_project_types populate_project_types(session) from autonomie.models.project.types import ( ProjectType, BusinessType, ) from autonomie.models.populate import populate_project_types populate_project_types(session) default_ptype_id = ProjectType.get_default().id default_btype_id = BusinessType.get_default().id course_ptype_id = ProjectType.query().filter_by(name='training').first().id course_btype_id = BusinessType.query().filter_by( name='training').first().id op.execute("update project set project_type_id=%s" % default_ptype_id) op.execute("update task set version='4.1'") for typ_ in ('estimation', 'invoice'): query = "update task join {type_} on {type_}.id=task.id set \ business_type_id={btype_id} where {type_}.course={course}" op.execute( query.format(type_=typ_, btype_id=default_btype_id, course=0)) op.execute(query.format(type_=typ_, btype_id=course_btype_id, course=1)) query2 = "update project set project_type_id={ptype_id} where \ (select count(task.id) from task join {type_} on {type_}.id=task.id \ where {type_}.course=1 and task.project_id=project.id ) > 0;" op.execute(query2.format( type_=typ_, ptype_id=course_ptype_id, )) _add_business_to_all_invoices(session)