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.CAEStatus.in_(CancelInvoice.valid_states))
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.CAEStatus.in_(Estimation.valid_states))
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.CAEStatus.in_(Invoice.valid_states),
Invoice.financial_year == year)))
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(project_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.statusDate))
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 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 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 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 get_next_official_number(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 _get_next_official_number(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 filter_notfilled(self, query, appstruct):
"""
Filter the workshops for which timeslots have not been filled
"""
notfilled = appstruct.get('notfilled')
if notfilled:
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:
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(self):
"""
Returns 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 = DBSESSION().query(distinct(self.model.id), self.model)
# 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)
already_added = []
for criterion in self.criteria:
# On génère le filtre
filter_ = criterion.gen_filter()
# si il n'y a pas de filtres ...
if filter_ is not None:
# On récupère l'objet lié
join_class = criterion.get_join_class()
# Si il y a un outerjoin à faire (le critère se fait sur un
# attribut de l'objet lié)
if join_class is not None:
# on ne va pas outerjoiné deux fois la classe pour le même
# critère
if criterion.key not in already_added:
query = query.outerjoin(join_class)
already_added.append(criterion.key)
query = query.filter(filter_)
return query
def query(self):
"""
Returns 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 = DBSESSION().query(distinct(self.model.id), self.model)
# 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)
already_added = []
for criterion in self.criteria:
# On génère le filtre
filter_ = criterion.gen_filter()
# si il n'y a pas de filtres ...
if filter_ is not None:
# On récupère l'objet lié
join_class = criterion.get_join_class()
# Si il y a un outerjoin à faire (le critère se fait sur un
# attribut de l'objet lié)
if join_class is not None:
# on ne va pas outerjoiné deux fois la classe pour le même
# critère
if criterion.key not in already_added:
query = query.outerjoin(join_class)
already_added.append(criterion.key)
query = query.filter(filter_)
return query
