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
"""
logger.debug("Queryiing")
query = DBSESSION().query(distinct(User.id), User)
return query.outerjoin(User.companies)
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 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.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
"""
if __current_test_ini_file().endswith('travis.ini'):
return
options = get_test_options_from_settings(settings)
cmd = "echo \"echo 'drop database {db};' | {mysql_cmd}\" | at now"
launch_cmd(options, cmd)
request.addfinalizer(drop_db)
return _connection
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.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
"""
if __current_test_ini_file().endswith('travis.ini'):
return
options = get_test_options_from_settings(settings)
cmd = "echo \"echo 'drop database {db};' | {mysql_cmd}\" | at now"
launch_cmd(options, cmd)
request.addfinalizer(drop_db)
return _connection
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 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 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))
return 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.CAEStatus.in_(CancelInvoice.valid_states))
return 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 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 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 initialize_sql(engine):
"""
Initialize the database engine
"""
DBSESSION.configure(bind=engine)
DBBASE.metadata.bind = engine
if not engine.table_names():
fetch_head()
DBBASE.metadata.create_all(engine)
return DBSESSION
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 _find_one(cls, name_or_id):
"""
Used as a creator for the initialization proxy
"""
with DBSESSION.no_autoflush:
res = DBSESSION.query(cls).get(name_or_id)
if res is None:
# We try with the id
res = DBSESSION.query(cls).filter(cls.name==name_or_id).one()
return res
def expenseyears():
"""
return distinct expense years available in the database
"""
query = DBSESSION().query(distinct(ExpenseSheet.year))
query = query.order_by(ExpenseSheet.year)
years = [year[0] for year in query]
current = datetime.date.today().year
if current not in years:
years.append(current)
return years
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 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 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))
query = query.order_by(ExpenseSheet.year)
years = [year[0] for year in query]
current = datetime.date.today().year
if current not in years:
years.append(current)
return years
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 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 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 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 upgrade():
from autonomie.models.task import (
TaskLine,
TaskLineGroup,
Task,
Estimation,
CancelInvoice,
Invoice,
)
from autonomie.models.base import (
DBSESSION,
)
session = DBSESSION()
index = 0
query = Task.query()
query = query.with_polymorphic([Invoice, CancelInvoice, Estimation])
query = query.filter(
Task.type_.in_(['invoice', 'estimation', 'cancelinvoice'])
)
for task in query:
group = TaskLineGroup(task_id=task.id, order=0)
for line in task.lines:
tline = TaskLine(
group=group,
order=line.rowIndex,
description=line.description,
cost=line.cost,
tva=line.tva,
quantity=line.quantity,
)
if hasattr(line, 'product_id'):
tline.product_id = line.product_id
session.add(tline)
if index % 100 == 0:
session.flush()
op.alter_column(
table_name='estimation_payment',
column_name='rowIndex',
new_column_name='order',
type_=sa.Integer,
)
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 set(cls, key, value):
instance = cls.get(key)
if instance is None:
instance = cls(name=key)
instance.value = value
DBSESSION().merge(instance)
def get_next_index(cls, project, factory):
query = DBSESSION.query(func.max(factory.project_index))
query = query.filter(factory.project_id == project.id)
max_num = query.first()[0]
if max_num is None:
max_num = 0
return max_num + 1
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_index(cls, company, factory):
query = DBSESSION.query(func.max(factory.company_index))
query = query.filter(factory.company_id == company.id)
max_num = query.first()[0]
if max_num is None:
max_num = 0
return max_num + 1
def get_next_index(cls, project, factory):
query = DBSESSION.query(func.max(factory.project_index))
query = query.filter(factory.project_id == project.id)
max_num = query.first()[0]
if max_num is None:
max_num = 0
return max_num + 1
def initialize_sql(engine):
"""
Initialize the database engine
"""
DBSESSION.configure(bind=engine)
DBBASE.metadata.bind = engine
if not engine.table_names():
fetch_head()
print("Setting the metadatas")
DBBASE.metadata.create_all(engine)
from transaction import commit
commit()
print("Populating the config")
populate_config(DBSESSION())
return DBSESSION
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 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_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 initialize_sql(engine):
"""
Initialize the database engine
"""
DBSESSION.configure(bind=engine)
DBBASE.metadata.bind = engine
if not engine.table_names():
fetch_head()
print("Setting the metadatas")
DBBASE.metadata.create_all(engine)
from transaction import commit
commit()
print("Populating the config")
populate_config(DBSESSION())
return DBSESSION
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 record_failure(job_model, job_id, task_id, e):
"""
Record a job's failure
"""
transaction.begin()
# We fetch the job again since we're in a new transaction
from autonomie.models.base import DBSESSION
job = DBSESSION().query(job_model).filter(
job_model.id==job_id
).first()
job.jobid = task_id
job.status = "failed"
# We append an error
if hasattr(job, 'error_messages'):
if job.error_messages is None:
job.error_messages = []
job.error_messages.append(u"%s" % e)
transaction.commit()
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 record_failure(job_model, job_id, task_id, e):
"""
Record a job's failure
"""
transaction.begin()
# We fetch the job again since we're in a new transaction
from autonomie.models.base import DBSESSION
job = DBSESSION().query(job_model).filter(
job_model.id==job_id
).first()
job.jobid = task_id
job.status = "failed"
# We append an error
if hasattr(job, 'error_messages'):
if job.error_messages is None:
job.error_messages = []
job.error_messages.append(u"%s" % e)
transaction.commit()
def add_custom_datas_headers(writer, query):
"""
Add custom headers that are not added through automation
"""
# Compte analytique
query = DBSESSION().query(
func.count(COMPANY_EMPLOYEE.c.company_id).label('nb'))
query = query.group_by(COMPANY_EMPLOYEE.c.account_id)
code_compta_count = query.order_by(desc("nb")).first()
if code_compta_count:
code_compta_count = code_compta_count[0]
for index in range(0, code_compta_count):
new_header = {
'label': "Compte_analytique {0}".format(index + 1),
'name': "code_compta_{0}".format(index + 1),
}
writer.add_extra_header(new_header)
return writer
def add_custom_datas_headers(writer, query):
"""
Add custom headers that are not added through automation
"""
# Compte analytique
query = DBSESSION().query(
func.count(COMPANY_EMPLOYEE.c.company_id).label('nb')
)
query = query.group_by(COMPANY_EMPLOYEE.c.account_id)
code_compta_count = query.order_by(desc("nb")).first()
if code_compta_count:
code_compta_count = code_compta_count[0]
for index in range(0, code_compta_count):
new_header = {
'label': "Compte_analytique {0}".format(index + 1),
'name': "code_compta_{0}".format(index + 1),
}
writer.add_extra_header(new_header)
return writer
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_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 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 upgrade():
op.add_column('node', sa.Column('_acl', sa.Text()))
from autonomie.models.base import DBSESSION
from autonomie.models.node import Node
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
req = "select max(id) from node"
result = conn.execute(req).fetchall()
max_id = result[0][0]
print("The new max_id is : %s" % max_id)
request = "select id, coordonnees_lastname from user_datas"
result = conn.execute(request)
op.execute("SET FOREIGN_KEY_CHECKS=0;")
for index, (id, lastname) in enumerate(result):
max_id += 1
new_id = max_id
node = Node(
id=new_id,
name=lastname,
type_='userdata',
)
session.add(node)
# Update des relations
for table in "userdatas_socialdocs", "external_activity_datas", \
"company_datas", "date_diagnostic_datas", \
"date_convention_cape_datas", "date_dpae_datas":
op.execute("update {0} set userdatas_id={1} where userdatas_id={2}".format(table, new_id, id))
# Update de la table node
op.execute("update user_datas set id={0} where id={1};".format(new_id, id))
if index % 50 == 0:
session.flush()
op.execute("SET FOREIGN_KEY_CHECKS=1;")
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 upgrade():
from autonomie.models.task import (
TaskLine,
TaskLineGroup,
Task,
Estimation,
CancelInvoice,
Invoice,
)
from autonomie.models.base import (
DBSESSION,
)
session = DBSESSION()
index = 0
query = Task.query()
query = query.with_polymorphic([Invoice, CancelInvoice, Estimation])
query = query.filter(
Task.type_.in_(['invoice', 'estimation', 'cancelinvoice'])
)
for task in query:
try:
task_lines = task.default_line_group.lines
except:
continue
for index, line in enumerate(task.lines):
try:
task_line = task_lines[index]
task_line.unity = line.unity
session.merge(task_line)
except:
pass
index += 1
if index % 100 == 0:
session.flush()
def get_job(celery_request, job_model, job_id):
"""
Return the current executed job (in autonomie's sens)
:param obj job_model: The Job model
:param obj celery_request: The current celery request object
:param int job_id: The id of the job
:returns: The current job
:raises sqlalchemy.orm.exc.NoResultFound: If the job could not be found
"""
from autonomie.models.base import DBSESSION
# We sleep a bit to wait for the current request to be finished : since we
# use a transaction manager, the delay call launched in a view is done
# before the job element is commited to the bdd (at the end of the request)
# if we query for the job too early, the session will not be able to
# retrieve the newly created job
time.sleep(10)
job = DBSESSION().query(job_model).filter(
job_model.id==job_id
).one()
job.jobid = celery_request.id
return job
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 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 upgrade():
now = time.time()
logger = logging.getLogger("autonomie")
logger.addHandler(logging.StreamHandler(sys.stdout))
logger.setLevel(logging.INFO)
from depot.fields.upload import UploadedFile
from sqlalchemy import bindparam
from autonomie.models.base import DBSESSION, METADATA
session = DBSESSION()
def process(thing, store):
id, data, filename, mimetype = thing
logger.debug("Handling file with id %s" % id)
uploaded_file = UploadedFile({"depot_name": "local", "files": []})
uploaded_file._thaw()
uploaded_file.process_content(data, filename=filename, content_type=mimetype)
store.append({"nodeid": thing.id, "depot_datas": uploaded_file.encode()})
logger.info("Saved data for node id {}".format(id))
window_size = 10
window_idx = 0
logger.info("# Starting migration of blob datas #")
from alembic.context import get_bind
conn = get_bind()
# Processing the file table
logger.debug(" + Processing files")
files = sa.Table("file", METADATA)
processed_files = []
count = session.query(files.c.id).count()
logger.debug(u" + Moving the files on disk")
while True:
start = window_size * window_idx
if start >= count:
break
logger.debug("Slicing from %s" % (start,))
req = (
"select distinct(file.id), data, node.name, mimetype from file join node on file.id=node.id LIMIT %s, %s"
% (start, window_size)
)
things = conn.execute(req)
if things is None:
break
for thing in things:
process(thing, processed_files)
window_idx += 1
logger.debug(u"-> Done")
logger.debug(u"Migrating the 'data' column")
op.drop_column("file", "data")
op.add_column("file", sa.Column("data", sa.Unicode(4096)))
files.c.data.type = sa.Unicode(4096)
update = files.update().where(files.c.id == bindparam("nodeid")).values({files.c.data: bindparam("depot_datas")})
def chunks(l, n):
for i in xrange(0, len(l), n):
yield l[i : i + n]
for cdata in chunks(processed_files, 10):
session.execute(update, cdata)
logger.debug(" + Processing config files")
logger.debug(u" + Moving the files on disk")
config_files = sa.Table("config_files", METADATA)
processed_config_files = []
req = "select id, data, name, mimetype from config_files"
for thing in conn.execute(req):
process(thing, processed_config_files)
op.drop_column("config_files", "data")
op.add_column("config_files", sa.Column("data", sa.Unicode(4096)))
config_files.c.data.type = sa.Unicode(4096)
update = (
config_files.update()
.where(config_files.c.id == bindparam("nodeid"))
.values({config_files.c.data: bindparam("depot_datas")})
)
session.execute(update, processed_config_files)
logger.debug(u"-> Done")
from zope.sqlalchemy import mark_changed
mark_changed(session)
logger.info("Blob migration completed in {} seconds".format(int(time.time() - now)))
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():
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 upgrade():
# Ajout et modification de la structure de données existantes
op.execute("alter table project modify archived BOOLEAN;")
for name in ('ht', 'tva', 'ttc'):
col = sa.Column(name, sa.Integer, default=0)
op.add_column('task', col)
for col in (
sa.Column("project_id", sa.Integer, sa.ForeignKey('project.id')),
sa.Column("customer_id", sa.Integer, sa.ForeignKey('customer.id')),
sa.Column("_number", sa.String(10)),
sa.Column("sequence_number", sa.Integer),
sa.Column("display_units", sa.Integer, default=0),
sa.Column('expenses', sa.Integer, default=0),
sa.Column('expenses_ht', sa.Integer, default=0),
sa.Column('address', sa.Text, default=""),
sa.Column('payment_conditions', sa.Text, default=""),
sa.Column("official_number", sa.Integer, default=None),
):
op.add_column("task", col)
col = sa.Column("sortie_type_id", sa.Integer, sa.ForeignKey('type_sortie_option.id'))
op.add_column("user_datas", col)
op.execute("alter table user_datas modify parcours_num_hours float DEFAULT NULL")
col = sa.Column("cgv", sa.Text, default="")
op.add_column("company", col)
# Migration des donnees vers la nouvelle structure
from alembic.context import get_bind
conn = get_bind()
from autonomie.models.base import DBSESSION
session = DBSESSION()
# Expenses will be nodes
make_expense_nodes(conn, session)
from autonomie.models.task import (
Invoice,
CancelInvoice,
Estimation,
)
# Migration des customer_id et project_id au niveau de la table Task
index = 0
for type_ in "invoice", "cancelinvoice", "estimation":
conditions = "paymentConditions"
if type_ == "cancelinvoice":
conditions = "reimbursementConditions"
request = "select id, customer_id, project_id, number, \
sequenceNumber, displayedUnits, expenses, expenses_ht, address, %s \
from %s;" % (conditions, type_)
result = conn.execute(request)
for index, (id, c_id, p_id, number, seq_number, display, expenses,
expenses_ht, address, conditions) in enumerate(result):
request = sa.text(u"update task set \
project_id=:p_id, \
customer_id=:c_id, \
_number=:number, \
sequence_number=:seq_number, \
display_units=:display, \
expenses=:expenses, \
expenses_ht=:expenses_ht, \
address=:address, \
payment_conditions=:conditions \
where id=:id;"
)
conn.execute(
request,
p_id=p_id,
c_id=c_id,
number=number,
seq_number=seq_number,
display=display,
expenses=expenses,
expenses_ht=expenses_ht,
address=address,
conditions=conditions,
id=id,
)
if index % 50 == 0:
session.flush()
for type_ in ('invoice', 'cancelinvoice'):
request = "select id, officialNumber from %s" % (type_,)
result = conn.execute(request)
for index, (id, official_number) in enumerate(result):
request = sa.text(u"update task set \
official_number=:official_number \
where id=:id;"
)
conn.execute(
request,
official_number=official_number,
id=id,
)
if index % 50 == 0:
session.flush()
for factory in (Invoice, CancelInvoice, Estimation,):
for document in factory.query().options(undefer_group('edit')):
document.ttc = document.total()
document.ht = document.total_ht()
document.tva = document.tva_amount()
session.merge(document)
index += 1
if index % 50 == 0:
session.flush()
# Drop old constraints
for table in ('estimation', 'invoice', 'cancelinvoice'):
for num in [2,3,4]:
key = "%s_ibfk_%s" % (table, num,)
cmd = "ALTER TABLE %s DROP FOREIGN KEY %s;" % (table, key)
try:
print(cmd)
conn.execute(cmd)
except:
print("Error while droping a foreignkey : %s %s" % (table, key))
for column in ('customer_id', 'project_id', 'number', \
'sequenceNumber', 'displayedUnits', 'expenses', \
'expenses_ht', 'address'):
op.drop_column(table, column)
op.drop_column('cancelinvoice', 'reimbursementConditions')
op.drop_column('estimation', 'paymentConditions')
op.drop_column('invoice', 'paymentConditions')
for table in ('invoice', 'cancelinvoice'):
op.drop_column(table, 'officialNumber')
