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_project_indicators(cls, project_id, file_type_id, task_id=None, business_id=None): """ Build a query for indicators related to a given project Excludes indicators related to task_id :param int project_id: The Project 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 :param int business_id: The id of the business to exclude from the query """ from autonomie.models.task import Task from autonomie.models.project.business import Business tasks_id_query = DBSESSION().query( Task.id).filter_by(project_id=project_id) if task_id: tasks_id_query = tasks_id_query.filter(Task.id != task_id) businesses_id_query = DBSESSION().query( Business.id).filter_by(project_id=project_id) if business_id: businesses_id_query = businesses_id_query.filter( Business.id != business_id) query = SaleFileRequirement.query().filter_by( file_type_id=file_type_id) return query.filter( or_( SaleFileRequirement.node_id.in_(businesses_id_query), SaleFileRequirement.node_id.in_(tasks_id_query), ))
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 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 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 _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 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 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 get_customers(cls, instance, year): from autonomie.models.task import Invoice from autonomie.models.customer import Customer query = DBSESSION().query(Customer) query = query.filter(Customer.company_id == instance.id) query = query.filter( Customer.invoices.any( and_(Invoice.status == 'valid', Invoice.financial_year == year))) return query
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 get_tasks(cls, instance, type_str=None): from autonomie.models.task import Task query = DBSESSION().query(Task) query = query.filter_by(customer_id=instance.id) if type_str is not None: query = query.filter(Task.type_ == type_str) else: query = query.filter( Task.type_.in_(('invoice', 'cancelinvoice', 'estimation'))) return query
def get_tasks(cls, instance, type_str=None): from autonomie.models.task import Task query = DBSESSION().query(Task) query = query.filter_by(customer_id=instance.id) if type_str is not None: query = query.filter(Task.type_ == type_str) else: query = query.filter( Task.type_.in_(('invoice', 'cancelinvoice', 'estimation')) ) return query
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 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 get_customer_codes_and_names(cls, company): """ Return a query for code and names of customers related to company :param company: the company we're working on :returns: an orm query loading Customer instances with only the columns we want :rtype: A Sqlalchemy query object """ from autonomie.models.customer import Customer query = DBSESSION().query(Customer) query = query.options(load_only('code', 'name')) query = query.filter(Customer.code != None) query = query.filter(Customer.company_id == company.id) return query.order_by(Customer.code)
def get_customer_codes_and_names(cls, company): """ Return a query for code and names of customers related to company :param company: the company we're working on :returns: an orm query loading Customer instances with only the columns we want :rtype: A Sqlalchemy query object """ from autonomie.models.customer import Customer query = DBSESSION().query(Customer) query = query.options(load_only('code', 'name')) query = query.filter(Customer.code != None) query = query.filter(Customer.company_id == company.id) return query.order_by(Customer.code)
def get_customers(cls, instance, year): from autonomie.models.task import Invoice from autonomie.models.customer import Customer query = DBSESSION().query(Customer) query = query.filter(Customer.company_id == instance.id) query = query.filter( Customer.invoices.any( and_( Invoice.status == 'valid', Invoice.financial_year == year ) ) ) return query
def query(self): """ Return the main query used to find objects e.g: query = DBSESSION().query(distinct(UserDatas.id), UserDatas) query = query.filter(UserDatas.name.startswith('test')) query = query.outerjoin(UserDatas.conseiller) query = query.filter(User.lastname=='A manager') query = query.filter( UserDatas.id.in_( [list of ids retrieved from independant queries] ) ) """ self.already_joined = [] if self.root: main_query = DBSESSION().query(distinct(self.model.id), self.model) else: main_query = DBSESSION().query(distinct(self.model.id)) # Pour chaque critère sur lesquels on va ajouter des filtres, on a # besoin d'être sûr que la classe concernée est bien requêtée, il faut # donc ajouter des outerjoins pour chaque classe liée. # NOTE: on ne gère pas les alias (les joins sur deux tables identiques # pour deux relations différentes) for criterion in self.query_helpers: # On génère le filtre filter_ = criterion.gen_filter() having = criterion.gen_having_clause() # si il y a un filtre ... if filter_ is not None: main_query = self.join(main_query, criterion) main_query = main_query.filter(filter_) elif having is not None: main_query = self.join(main_query, criterion) main_query = main_query.group_by(self.model.id) main_query = main_query.having(having) if self.query_factories: ids = list(self._get_ids_from_factories()) main_query = main_query.filter(self.model.id.in_(ids)) return main_query
def query(self): """ Return the main query used to find objects e.g: query = DBSESSION().query(distinct(UserDatas.id), UserDatas) query = query.filter(UserDatas.name.startswith('test')) query = query.outerjoin(UserDatas.conseiller) query = query.filter(User.lastname=='A manager') query = query.filter( UserDatas.id.in_( [list of ids retrieved from independant queries] ) ) """ self.already_joined = [] if self.root: main_query = DBSESSION().query(distinct(self.model.id), self.model) else: main_query = DBSESSION().query(distinct(self.model.id)) # Pour chaque critère sur lesquels on va ajouter des filtres, on a # besoin d'être sûr que la classe concernée est bien requêtée, il faut # donc ajouter des outerjoins pour chaque classe liée. # NOTE: on ne gère pas les alias (les joins sur deux tables identiques # pour deux relations différentes) for criterion in self.query_helpers: # On génère le filtre filter_ = criterion.gen_filter() having = criterion.gen_having_clause() # si il y a un filtre ... if filter_ is not None: main_query = self.join(main_query, criterion) main_query = main_query.filter(filter_) elif having is not None: main_query = self.join(main_query, criterion) main_query = main_query.group_by(self.model.id) main_query = main_query.having(having) if self.query_factories: ids = list(self._get_ids_from_factories()) main_query = main_query.filter(self.model.id.in_(ids)) return main_query
def get_company(self, cid): """ Retrieve the user's company with id cid :param int cid: The user's company id :returns: A Company instance :raises: `sqlalchemy.orm.exc.NoResultFound` if no company can be found """ from autonomie.models.company import Company if not isinstance(cid, int): cid = int(cid) query = DBSESSION().query(Company) query = query.filter(Company.employees.any(User.id == self.id)) query = query.filter(Company.id == cid) return query.one()
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 _query(cls, invoice): from autonomie.models.task import Task q = DBSESSION().query(func.Max(SequenceNumber.index)) q = q.filter(Task.type_.in_(('invoice', 'cancelinvoice'))) q = q.filter_by(sequence=cls.db_key) return q
def get_company(self, cid): """ Retrieve the user's company with id cid :param int cid: The user's company id :returns: A Company instance :raises: `sqlalchemy.orm.exc.NoResultFound` if no company can be found """ from autonomie.models.company import Company if not isinstance(cid, int): cid = int(cid) query = DBSESSION().query(Company) query = query.filter(Company.employees.any(User.id == self.id)) query = query.filter(Company.id == cid) return query.one()
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(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 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 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 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 check(cls, node): """ Check if all indicators are successfull :param obj node: The node for which we check the indicators """ query = DBSESSION().query(SaleFileRequirement.id) query = query.filter_by(node_id=node.id) query = query.filter( SaleFileRequirement.status != SaleFileRequirement.SUCCESS_STATUS) return query.count() == 0
def check(cls, node): """ Check if all indicators are successfull :param obj node: The node for which we check the indicators """ query = DBSESSION().query(SaleFileRequirement.id) query = query.filter_by(node_id=node.id) query = query.filter( SaleFileRequirement.status != SaleFileRequirement.SUCCESS_STATUS ) return query.count() == 0
def query(cls, keys=None, active=True): """ Return a query """ if keys: query = DBSESSION().query(*keys) else: query = super(Company, cls).query() if active: query = query.filter(cls.active == "Y") return query.order_by(cls.name)
def query(cls, keys=None, active=True): """ Return a query """ if keys: query = DBSESSION().query(*keys) else: query = super(Company, cls).query() if active: query = query.filter(cls.active == True) return query.order_by(cls.name)
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 query(self): query = DBSESSION().query(Task) query = query.with_polymorphic([Invoice, CancelInvoice]) query = query.outerjoin(Invoice.payments) query = query.outerjoin(Task.customer) query = query.options( contains_eager(Invoice.payments).load_only(Payment.id, Payment.date, Payment.mode)) query = query.options( contains_eager(Task.customer).load_only(Customer.name, Customer.code, Customer.id)) query = query.filter(Task.status == 'valid') return query
def query_existing_project_indicators( cls, project_id, file_type_id, task_id=None, business_id=None ): """ Build a query for indicators related to a given project Excludes indicators related to task_id :param int project_id: The Project 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 :param int business_id: The id of the business to exclude from the query """ from autonomie.models.task import Task from autonomie.models.project.business import Business tasks_id_query = DBSESSION().query(Task.id).filter_by( project_id=project_id ) if task_id: tasks_id_query = tasks_id_query.filter(Task.id != task_id) businesses_id_query = DBSESSION().query(Business.id).filter_by( project_id=project_id ) if business_id: businesses_id_query = businesses_id_query.filter( Business.id != business_id ) query = SaleFileRequirement.query().filter_by( file_type_id=file_type_id ) return query.filter( or_( SaleFileRequirement.node_id.in_(businesses_id_query), SaleFileRequirement.node_id.in_(tasks_id_query), ) )
def _get_next_official_number(self, year=None): """ Return the next available official number :param int year: The year we'd like to query a number for """ next_ = 1 if year is None: year = datetime.date.today().year query = DBSESSION().query(func.max(Task.official_number)) query = query.filter(extract('year', Task.date) == year) last = query.first()[0] if last: next_ = last + 1 return next_
def query(self): query = DBSESSION().query(Task) query = query.with_polymorphic([Invoice, CancelInvoice]) query = query.outerjoin(Invoice.payments) query = query.outerjoin(Task.customer) query = query.outerjoin(Task.company) query = query.options( contains_eager(Invoice.payments).load_only(Payment.id, Payment.date, Payment.mode)) query = query.options( contains_eager(Task.customer).load_only( Customer.name, Customer.code, Customer.id, Customer.firstname, Customer.lastname, Customer.civilite, Customer.type_, )) query = query.options( contains_eager(Task.company).load_only( Company.name, Company.id, )) query = query.options( load_only( "_acl", "name", "date", "id", "ht", "tva", "ttc", "company_id", "customer_id", "official_number", "internal_number", "prefix", "status", Invoice.paid_status, )) query = query.filter(Task.status == 'valid') return query
def filter_notfilled(self, query, appstruct): """ Filter the workshops for which timeslots have not been filled """ notfilled = appstruct.get('notfilled') if notfilled not in (None, colander.null, False): logger.debug(u"Filtering the workshop that where not filled") attendance_query = DBSESSION().query(distinct(Attendance.event_id)) attendance_query = attendance_query.filter( Attendance.status != 'registered') timeslot_ids = [item[0] for item in attendance_query] query = query.filter( not_( models.Workshop.timeslots.any( models.Timeslot.id.in_(timeslot_ids)))) return query
def filter_notfilled(self, query, appstruct): """ Filter the workshops for which timeslots have not been filled """ notfilled = appstruct.get('notfilled') if notfilled not in (None, colander.null, False): logger.debug(u"Filtering the workshop that where not filled") attendance_query = DBSESSION().query(distinct(Attendance.event_id)) attendance_query = attendance_query.filter( Attendance.status != 'registered' ) timeslot_ids = [item[0] for item in attendance_query] query = query.filter( not_( models.Workshop.timeslots.any( models.Timeslot.id.in_(timeslot_ids) ) ) ) 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 get_other_business_type_ids(self): query = DBSESSION().query(ProjectTypeBusinessType.c.business_type_id) query = query.filter( ProjectTypeBusinessType.c.project_type_id == self.id ) return [a[0] for a in query]
def get_other_business_type_ids(self): query = DBSESSION().query(ProjectTypeBusinessType.c.business_type_id) query = query.filter( ProjectTypeBusinessType.c.project_type_id == self.id) return [a[0] for a in query]