def get_recording_job(self):
"""
Initialize a job for importation recording
"""
# We initialize a job record in the database
job = CsvImportJob()
job.set_owner(self.request.user.login)
DBSESSION().add(job)
DBSESSION().flush()
return job
def set(cls, key, appstruct):
"""
Set a file for the given key, if the key isn't field yet, add a new
instance
"""
instance = cls.get(key)
if instance is None:
instance = cls(key=key)
for attr_name, attr_value in appstruct.items():
setattr(instance, attr_name, attr_value)
if instance.id is not None:
DBSESSION().merge(instance)
else:
DBSESSION().add(instance)
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_associated_tasks_by_type(self, type_str):
"""
Return the tasks of type type_str associated to the current object
"""
from autonomie.models.task import Task
return DBSESSION().query(Task).filter_by(project_id=self.id,
type_=type_str).all()
def upgrade():
disable_listeners()
op.add_column('task', sa.Column('date', sa.Date()))
from autonomie.models.task import Task
from autonomie.models.base import DBSESSION
session = DBSESSION()
for task in Task.query().filter(Task.type_!='manualinvoice'):
task.date = task.taskDate
session.merge(task)
session.flush()
op.execute("alter table groups modify label VARCHAR(255);")
op.execute("alter table payment modify remittance_amount VARCHAR(255);")
from autonomie.models.user import User, Group
for group_id, group_name, group_label in GROUPS:
group = session.query(Group).filter(Group.name==group_name).first()
if group is None:
group = Group(name=group_name, label=group_label)
session.add(group)
session.flush()
users = session.query(User).filter(User.primary_group==group_id)
for user in users:
user._groups.append(group)
session.merge(user)
label = u"Peut saisir/modifier/supprimer les paiements de ses factures"
group_name = "payment_admin"
group = Group.query().filter(Group.name==group_name).first()
if group is not None:
group.label = label
session.merge(group)
def upgrade():
from autonomie.models import user
from autonomie.models.base import DBSESSION
db = DBSESSION()
for u in db.query(user.User)\
.filter(user.User.userdatas==None)\
.filter(user.User.primary_group==3):
situation = "sortie"
if u.email:
userdata = user.UserDatas(
situation_situation=situation,
coordonnees_firstname=u.firstname,
coordonnees_lastname=u.lastname,
coordonnees_email1=u.email,
coordonnees_civilite=u'?',
)
userdata.user_id = u.id
for company in u.companies:
companydata = user.CompanyDatas(
title=company.goal,
name=company.name,
)
userdata.activity_companydatas.append(companydata)
db.add(userdata)
db.flush()
def set(cls, key, value):
instance = cls.get(key)
if instance is None:
instance = cls(name=key)
instance.value = value
DBSESSION().merge(instance)
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.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 query(self):
"""
Return the main query for our list view
"""
log.debug("Queryiing")
query = DBSESSION().query(distinct(User.id), User)
return query.outerjoin(User.companies)
def upgrade():
import logging
logger = logging.getLogger('alembic.here')
op.add_column(
"user_datas",
sa.Column(
'statut_social_status_today_id',
sa.Integer,
sa.ForeignKey('social_status_option.id'),
))
op.add_column(
"user_datas",
sa.Column(
"parcours_employee_quality_id",
sa.Integer,
sa.ForeignKey('employee_quality_option.id'),
))
op.add_column(
"user_datas",
sa.Column("situation_antenne_id", sa.Integer,
sa.ForeignKey('antenne_option.id')))
op.add_column("task", sa.Column(
"internal_number",
sa.String(40),
))
op.add_column("task", sa.Column("company_index", sa.Integer))
op.execute("alter table task CHANGE sequence_number project_index int(11)")
op.add_column(
"task",
sa.Column(
"company_id",
sa.Integer,
sa.ForeignKey('company.id'),
))
from autonomie.models.base import (
DBSESSION, )
session = DBSESSION()
add_company_id(session, logger)
add_company_index(session, logger)
add_internal_number(session, logger)
logger.warn("Adding Contract Histories")
from autonomie.models.user import UserDatas, ContractHistory
for id_, last_avenant in UserDatas.query('id', 'parcours_last_avenant'):
if last_avenant:
session.add(
ContractHistory(userdatas_id=id_, date=last_avenant,
number=-1))
op.add_column("date_convention_cape_datas",
sa.Column('end_date', sa.Date(), nullable=True))
op.execute("alter table customer MODIFY code VARCHAR(4);")
op.execute("alter table project MODIFY code VARCHAR(4);")
create_custom_treasury_modules(session, logger)
from zope.sqlalchemy import mark_changed
mark_changed(session)
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 record_preference(request, name, association_dict):
"""
Record a field association in the request config
"""
config_obj = get_preferences_obj()
associations = load_preferences(config_obj)
associations[name] = association_dict
if config_obj.value is None:
# It's a new one
config_obj.value = json.dumps(associations)
DBSESSION().add(config_obj)
else:
# We edit it
config_obj.value = json.dumps(associations)
DBSESSION().merge(config_obj)
return associations
def get_official_number(cls):
"""
Return the greatest official_number actually used in the
ManualInvoice table
"""
current_year = datetime.date.today().year
return DBSESSION().query(func.max(CancelInvoice.official_number)).filter(
func.year(CancelInvoice.taskDate) == current_year)
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 upgrade():
from autonomie.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
request = "select id, type_, name, creationDate, updateDate from task"
conn = get_bind()
result = conn.execute(request)
index = 0
max_id = -1
for id_, type_, name, creationDate, updateDate in result:
creationDate = format_date(creationDate)
updateDate = format_date(updateDate)
index += 1
node = Node(id=id_,
created_at=creationDate,
updated_at=updateDate,
name=name,
type_=type_)
session.add(node)
if index % 50 == 0:
session.flush()
if id_ > max_id:
max_id = id_
request = "select id, name, creationDate, updateDate from project ORDER BY id DESC"
result = conn.execute(request).fetchall()
# We disable foreign key constraints check
op.execute("SET FOREIGN_KEY_CHECKS=0;")
index = 0
for id_, name, creationDate, updateDate in result:
new_id = id_ + max_id
creationDate = format_date(creationDate)
updateDate = format_date(updateDate)
index += 1
node = Node(id=new_id,
created_at=creationDate,
updated_at=updateDate,
name=name,
type_='project')
session.add(node)
# We update the foreignkeys
for table in ('estimation', 'invoice', 'cancelinvoice', 'phase',
'project_customer'):
op.execute(
"update {0} set project_id={1} where project_id={2}".format(
table, new_id, id_))
# We update the project id
op.execute("update project set id={0} where id={1};".format(
new_id, id_))
if index % 50 == 0:
session.flush()
op.execute("SET FOREIGN_KEY_CHECKS=1;")
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 query(self):
query = DBSESSION().query(Task)
query = query.with_polymorphic([Invoice, CancelInvoice])
from sqlalchemy.orm import joinedload
query = query.options(
joinedload(Invoice.payments).load_only(Payment.id, Payment.date,
Payment.mode))
query = query.options(
joinedload(Task.customer).load_only(Customer.name, Customer.code,
Customer.id))
return query
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_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 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 expenseyears():
"""
return distinct expense years available in the database
"""
query = DBSESSION().query(distinct(ExpenseSheet.year))\
.order_by(ExpenseSheet.year)
years = [year[0] for year in query]
current = date.today().year
if current not in years:
years.append(current)
return years
def get_official_number(cls):
"""
Return the next official_number available in the Invoice's table
Take the max of official Number
when taskDate startswith the current year
taskdate is a string (YYYYMMDD)
"""
current_year = datetime.date.today().year
return DBSESSION().query(func.max(Invoice.official_number)).filter(
Invoice.taskDate.between(current_year * 10000,
(current_year + 1) * 10000
))
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
request.addfinalizer(trans.rollback)
request.addfinalizer(abort)
from autonomie.models.base import DBSESSION
return DBSESSION()
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 upgrade():
from autonomie.models import competence
from autonomie.models.base import DBSESSION
for comp in competence.CompetenceOption.query():
req = comp.requirement
for deadline in competence.CompetenceDeadline.query():
comp.requirements.append(
competence.CompetenceRequirement(
deadline_id=deadline.id,
requirement=req,
))
DBSESSION().merge(comp)
def content(connection, settings):
"""
sets up some default content
"""
from transaction import commit
from autonomie.models.base import DBBASE, DBSESSION
metadata = DBBASE.metadata
metadata.drop_all(connection.engine)
metadata.create_all(connection.engine)
populate_db(DBSESSION())
commit()
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 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.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 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