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]