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 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.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 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 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 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 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 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 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 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')
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")
op.execute(
"alter table external_activity_datas modify hours float DEFAULT NULL")
op.execute(
"alter table external_activity_datas modify brut_salary float DEFAULT NULL"
)
col = sa.Column("cgv", sa.Text, default="")
op.add_column("company", col)
col = sa.Column('_acl', sa.Text)
op.add_column("job", 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')
