def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
for row in list(conn.execute('SELECT id, leaders FROM workshop')):
if not row.leaders:
continue
try:
leaders_list = json.loads(row.leaders)
except ValueError:
# This should not happen, but some dumps we use have a bare string
# in leaders field.
leaders_list = [row.leaders]
req = sa.text("""
UPDATE workshop SET
description=CONCAT(
'Formateurs: ',
IFNULL(:leaders, ''),
' ',
IFNULL(description, '')
)
WHERE id=:id_
""")
conn.execute(
req,
leaders=', '.join(leaders_list),
id_=row.id,
)
mark_changed(session)
session.flush()
def migrate_datas():
from autonomie_base.models.base import DBSESSION
from autonomie.models.config import Config
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
initial_values = [
('bookentry_facturation_label_template',
'{invoice.customer.label} {company.name}'),
('bookentry_contribution_label_template',
"{invoice.customer.label} {company.name}"),
('bookentry_rg_interne_label_template',
"RG COOP {invoice.customer.label} {company.name}"),
('bookentry_rg_client_label_template',
"RG {invoice.customer.label} {company.name}"),
('bookentry_expense_label_template',
"{beneficiaire}/frais {expense_date:%-m %Y}"),
('bookentry_payment_label_template',
"{company.name} / Rgt {invoice.customer.label}"),
('bookentry_expense_payment_main_label_template',
"{beneficiaire_LASTNAME} / REMB FRAIS {expense_date:%B/%Y}"),
('bookentry_expense_payment_waiver_label_template',
"Abandon de créance {beneficiaire_LASTNAME} {expense_date:%B/%Y}"),
]
for key, val in initial_values:
Config.set(key, val)
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
for row in list(conn.execute('SELECT id, leaders FROM workshop')):
if not row.leaders:
continue
try:
leaders_list = json.loads(row.leaders)
except ValueError:
# This should not happen, but some dumps we use have a bare string
# in leaders field.
leaders_list = [row.leaders]
req = sa.text("""
UPDATE workshop SET
description=CONCAT(
'Formateurs: ',
IFNULL(:leaders, ''),
' ',
IFNULL(description, '')
)
WHERE id=:id_
""")
conn.execute(
req,
leaders=', '.join(leaders_list),
id_=row.id,
)
mark_changed(session)
session.flush()
def upgrade():
from alembic.context import get_bind
conn = get_bind()
result = conn.execute("""
select invoice.IDTask, invoice.tva, invoice.discountHT, invoice.expenses, invoice.paymentMode, task.statusDate from coop_invoice as invoice join coop_task as task on task.IDTask=invoice.IDTask where task.CAEStatus='paid';
""").fetchall()
for i in result:
id_, tva, discountHT, expenses, paymentMode, statusDate = i
lines = conn.execute("""
select cost, quantity from coop_invoice_line where IDTask='%s'""" % id_).fetchall()
totalht = get_ht(lines, discountHT)
tva_amount = int(float(totalht) * (max(int(tva), 0) / 10000.0))
ttc = tva_amount + totalht
total = ttc + expenses
date = datetime.datetime.fromtimestamp(float(statusDate))
# Adding one payment for each invoice that has been marked as "paid"
conn.execute("""
insert into payment (mode, amount, date, task_id) VALUE ('%s', '%s', '%s', '%s')"""%(paymentMode, total, date, id_))
# Using direct sql allows to enforce CAEStatus modifications
# Ref #573
# Ref #551
op.execute("""
update coop_task as t join coop_invoice as inv on t.IDTask=inv.IDTask set CAEStatus='resulted' WHERE t.CAEStatus='paid';
""")
op.execute("""
update coop_task set CAEStatus='resulted' where CAEStatus='gencinv';
""")
op.execute("""
update coop_task set CAEStatus='valid' where CAEStatus='sent' OR CAEStatus='recinv';
""")
if table_exists("coop_cancel_invoice"):
op.execute("""
update coop_task as t join coop_cancel_invoice as est on t.IDTask=est.IDTask SET t.CAEStatus='valid' WHERE t.CAEStatus='paid';
""")
def migrate_datas():
from autonomie_base.models.base import DBSESSION
from alembic.context import get_bind
from autonomie.models.user.userdatas import SocialStatusDatas
session = DBSESSION()
connection = get_bind()
userdatas_helper = sa.Table(
'user_datas',
sa.MetaData(),
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('statut_social_status_id', sa.Integer()),
sa.Column('statut_social_status_today_id', sa.Integer()),
)
for userdata in connection.execute(userdatas_helper.select()):
if userdata.statut_social_status_id:
social_status_entry = SocialStatusDatas(
step='entry',
userdatas_id=userdata.id,
social_status_id=userdata.statut_social_status_id
)
session.add(social_status_entry)
if userdata.statut_social_status_today_id:
social_status_today = SocialStatusDatas(
step='today',
userdatas_id=userdata.id,
social_status_id=userdata.statut_social_status_today_id
)
session.add(social_status_today)
session.flush()
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():
# Only run this for somehow supported data types at the date we started naming constraints
# Among others, these will probably fail on MySQL
if context.get_bind().engine.name not in ('sqlite', 'postgresql'):
return
metadata = context.get_context().opts['target_metadata']
# Drop every constraint on every table
with op.batch_alter_table('alias', naming_convention=metadata.naming_convention) as batch_op:
batch_op.drop_constraint('alias_pkey', type_="primary")
batch_op.drop_constraint('alias_domain_name_fkey', type_="foreignkey")
with op.batch_alter_table('alternative', naming_convention=metadata.naming_convention) as batch_op:
batch_op.drop_constraint('alternative_pkey', type_="primary")
batch_op.drop_constraint('alternative_domain_name_fkey', type_="foreignkey")
with op.batch_alter_table('manager', naming_convention=metadata.naming_convention) as batch_op:
batch_op.drop_constraint('manager_domain_name_fkey', type_="foreignkey")
batch_op.drop_constraint('manager_user_email_fkey', type_="foreignkey")
with op.batch_alter_table('token', naming_convention=metadata.naming_convention) as batch_op:
batch_op.drop_constraint('token_pkey', type_="primary")
batch_op.drop_constraint('token_user_email_fkey', type_="foreignkey")
with op.batch_alter_table('fetch', naming_convention=metadata.naming_convention) as batch_op:
batch_op.drop_constraint('fetch_pkey', type_="primary")
batch_op.drop_constraint('fetch_user_email_fkey', type_="foreignkey")
with op.batch_alter_table('relay', naming_convention=metadata.naming_convention) as batch_op:
batch_op.drop_constraint('relay_pkey', type_="primary")
with op.batch_alter_table('config', naming_convention=metadata.naming_convention) as batch_op:
batch_op.drop_constraint('config_pkey', type_="primary")
with op.batch_alter_table('user', naming_convention=metadata.naming_convention) as batch_op:
batch_op.drop_constraint('user_pkey', type_="primary")
batch_op.drop_constraint('user_domain_name_fkey', type_="foreignkey")
with op.batch_alter_table('domain', naming_convention=metadata.naming_convention) as batch_op:
batch_op.drop_constraint('domain_pkey', type_="primary")
# Recreate constraints with proper names
with op.batch_alter_table('domain', naming_convention=metadata.naming_convention) as batch_op:
batch_op.create_primary_key('domain_pkey', ['name'])
with op.batch_alter_table('alias', naming_convention=metadata.naming_convention) as batch_op:
batch_op.create_primary_key('alias_pkey', ['email'])
batch_op.create_foreign_key('alias_domain_name_fkey', 'domain', ['domain_name'], ['name'])
with op.batch_alter_table('user', naming_convention=metadata.naming_convention) as batch_op:
batch_op.create_primary_key('user_pkey', ['email'])
batch_op.create_foreign_key('user_domain_name_fkey', 'domain', ['domain_name'], ['name'])
with op.batch_alter_table('alternative', naming_convention=metadata.naming_convention) as batch_op:
batch_op.create_primary_key('alternative_pkey', ['name'])
batch_op.create_foreign_key('alternative_domain_name_fkey', 'domain', ['domain_name'], ['name'])
with op.batch_alter_table('manager', naming_convention=metadata.naming_convention) as batch_op:
batch_op.create_foreign_key('manager_domain_name_fkey', 'domain', ['domain_name'], ['name'])
batch_op.create_foreign_key('manager_user_email_fkey', 'user', ['user_email'], ['email'])
with op.batch_alter_table('token', naming_convention=metadata.naming_convention) as batch_op:
batch_op.create_primary_key('token_pkey', ['id'])
batch_op.create_foreign_key('token_user_email_fkey', 'user', ['user_email'], ['email'])
with op.batch_alter_table('fetch', naming_convention=metadata.naming_convention) as batch_op:
batch_op.create_primary_key('fetch_pkey', ['id'])
batch_op.create_foreign_key('fetch_user_email_fkey', 'user', ['user_email'], ['email'])
with op.batch_alter_table('relay', naming_convention=metadata.naming_convention) as batch_op:
batch_op.create_primary_key('relay_pkey', ['name'])
with op.batch_alter_table('config', naming_convention=metadata.naming_convention) as batch_op:
batch_op.create_primary_key('config_pkey', ['name'])
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
connection = get_bind()
op.execute("UPDATE sale_product_group SET type_='base'")
from zope.sqlalchemy import mark_changed
mark_changed(session)
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
from autonomie.models.config import Config
Config.set('accounting_label_maxlength', 35)
session.flush()
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
op.execute('update customer set civilite="M. et Mme" where civilite="mr&mme"')
from zope.sqlalchemy import mark_changed
mark_changed(session)
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.company import Company
from autonomie.models.files import File
from autonomie_base.models.base import DBSESSION
from alembic.context import get_bind
from autonomie.models.config import ConfigFiles
for i in ('header_id', 'logo_id',):
col = sa.Column(i, sa.Integer, sa.ForeignKey('file.id'))
op.add_column('company', col)
query = "select id, header, logo from company;"
conn = get_bind()
result = conn.execute(query)
session = DBSESSION()
for id_, header, logo in result:
company = Company.get(id_)
basepath = u"%scompany/%s" % (BASEFILEPATH, id_,)
if header:
header_path = u"%s/header/%s" % (basepath, header)
try:
file_datas = load_file_struct(header_path, header)
except:
print("Error while loading a header")
print(id_)
file_datas = None
if file_datas:
company.header = file_datas
session.add(company.header_file)
session.flush()
if logo:
logo_path = u"%s/logo/%s" % (basepath, logo)
try:
file_datas = load_file_struct(logo_path, logo)
except:
print("Error while loading a logo")
print(id_)
file_datas = None
if file_datas:
company.logo = file_datas
company = session.merge(company)
session.flush()
filepath = u"%s/main/logo.png" % BASEFILEPATH
if os.path.isfile(filepath):
ConfigFiles.set('logo.png', load_file_struct(filepath, 'logo.png'))
filepath = u"%s/main/accompagnement_header.png" % BASEFILEPATH
if os.path.isfile(filepath):
ConfigFiles.set(
'accompagnement_header.png',
load_file_struct(filepath, 'accompagnement_header.png')
)
def upgrade():
from autonomie.models.company import Company
from autonomie.models.files import File
from autonomie.models import DBSESSION
from alembic.context import get_bind
from autonomie.models.config import ConfigFiles
for i in ('header_id', 'logo_id',):
col = sa.Column(i, sa.Integer, sa.ForeignKey('file.id'))
op.add_column('company', col)
query = "select id, header, logo from company;"
conn = get_bind()
result = conn.execute(query)
session = DBSESSION()
for id_, header, logo in result:
company = Company.get(id_)
basepath = u"%scompany/%s" % (BASEFILEPATH, id_,)
if header:
header_path = u"%s/header/%s" % (basepath, header)
try:
file_datas = load_file_struct(header_path, header)
except:
print("Error while loading a header")
print(id_)
file_datas = None
if file_datas:
company.header = file_datas
session.add(company.header_file)
session.flush()
if logo:
logo_path = u"%s/logo/%s" % (basepath, logo)
try:
file_datas = load_file_struct(logo_path, logo)
except:
print("Error while loading a logo")
print(id_)
file_datas = None
if file_datas:
company.logo = file_datas
company = session.merge(company)
session.flush()
filepath = u"%s/main/logo.png" % BASEFILEPATH
if os.path.isfile(filepath):
ConfigFiles.set('logo.png', load_file_struct(filepath, 'logo.png'))
filepath = u"%s/main/accompagnement_header.png" % BASEFILEPATH
if os.path.isfile(filepath):
ConfigFiles.set(
'accompagnement_header.png',
load_file_struct(filepath, 'accompagnement_header.png')
)
def lazy_load():
dialect = context.get_bind().dialect.name
module = globals()
# Lookup the type based on the dialect specific type, or fallback to the generic type
type_ = module.get(f'_{dialect}_{name}',
None) or module.get(f'_sa_{name}')
val = module[name] = type_()
return val
def _mssql_use_date_time2():
conn = context.get_bind()
result = conn.execute(
"""SELECT CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion'))
like '8%' THEN '2000' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion'))
like '9%' THEN '2005' ELSE '2005Plus' END AS MajorVersion""").fetchone(
)
mssql_version = result[0]
return mssql_version not in ("2000", "2005")
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
connection = get_bind()
from autonomie.models.user.login import Login
op.execute("update groups set editable=0;")
op.execute("update groups set `primary`=0;")
op.execute(
"update groups set `primary`=1 where name IN ('admin', 'contractor', 'manager')"
)
op.execute('update accounts set civilite="Monsieur"')
for user in connection.execute(user_helper.select()):
login = Login(
user_id=user.id,
login=user.login,
)
login.pwd_hash = user.password,
login.active = user.active == 'Y'
session.add(login)
session.flush()
op.execute(
'UPDATE user_groups set login_id="%s" where user_id=%s' % (
login.id, user.id
)
)
op.drop_column("accounts", "login")
op.drop_column("accounts", "password")
op.drop_column("accounts", "active")
from autonomie.models.user.user import User
for userdatas in connection.execute(userdatas_helper.select()):
if userdatas.user_id is None:
user = User(
lastname=userdatas.coordonnees_lastname,
firstname=userdatas.coordonnees_firstname,
email=userdatas.coordonnees_email1,
civilite=userdatas.coordonnees_civilite or 'Monsieur',
)
session.add(user)
session.flush()
connection.execute(
userdatas_helper.update().where(
userdatas_helper.c.id == userdatas.id
).values(user_id=user.id)
)
else:
user = User.get(userdatas.user_id)
user.civilite = userdatas.coordonnees_civilite or 'Monsieur'
session.merge(user)
session.flush()
op.execute('update accounts set civilite="Monsieur" where civilite is NULL')
def add_company_id(session, logger):
logger.warn("Adding company_id to Task")
from alembic.context import get_bind
conn = get_bind()
query = "select t.id, p.company_id from task t, project p where t.project_id = p.id"
for id, company_id in conn.execute(query):
req = "update task set company_id={0} where id={1}".format(company_id, id)
session.execute(req)
def add_company_id(session, logger):
logger.warn("Adding company_id to Task")
from alembic.context import get_bind
conn = get_bind()
query = "select t.id, p.company_id from task t, project p where t.project_id = p.id"
for id, company_id in conn.execute(query):
req = "update task set company_id={0} where id={1}".format(company_id, id)
session.execute(req)
def migrate_datas():
from autonomie.models.project.types import BusinessType
from autonomie.models.config import Config
from autonomie.models.task.mentions import TaskMention
from autonomie.models.project.mentions import BusinessTypeTaskMention
from autonomie_base.models.base import DBSESSION
from alembic.context import get_bind
session = DBSESSION()
conn = get_bind()
# Collect business type ids
business_type_ids = [b[0] for b in session.query(BusinessType.id)]
# for each fixed config key we now use mentions
for index, (doctype, key, label, title) in enumerate(((
'estimation',
'coop_estimationfooter',
u"Informations sur l'acceptation des devis",
u"Acceptation du devis",
), (
"invoice",
"coop_invoicepayment",
u"Informations de paiement pour les factures",
u"Mode de paiement",
), (
"invoice",
"coop_invoicelate",
u"Informations sur les retards de paiement",
u"Retard de paiement",
))):
# We retrieve the configurated value
value = Config.get_value(key, "")
mention = TaskMention(
order=index,
label=label,
title=title,
full_text=value.replace('%IBAN%', "{IBAN}").replace(
'%RIB%', "{RIB}").replace('%ENTREPRENEUR%', '{name}'))
session.add(mention)
session.flush()
for btype_id in business_type_ids:
rel = BusinessTypeTaskMention(
task_mention_id=mention.id,
business_type_id=btype_id,
doctype=doctype,
mandatory=True,
)
session.add(rel)
session.flush()
op.execute(
u"INSERT INTO mandatory_task_mention_rel (task_id, mention_id) \
SELECT task.id, {mention_id} from task join node on task.id=node.id where \
node.type_='{type_}'".format(mention_id=mention.id, type_=doctype))
def upgrade():
connection = context.get_bind()
metadata = MetaData(bind=connection)
resource_providers = Table('resource_providers', metadata, autoload=True)
query = select([sqlfunc.count()]).select_from(resource_providers).where(
resource_providers.c.root_provider_id == sa.null())
if connection.scalar(query):
raise Exception('There is at least one resource provider table '
'record which is missing its root provider id. '
'Run the "placement-manage db '
'online_data_migrations" command.')
def migrate_datas():
"""We tolerate duplicate invoice number for invoices issued so far.
That means that coops have to migrate to real unique invoice number
template before then.
"""
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
conn.execute("UPDATE task set legacy_number = 1")
mark_changed(session)
def __getattr__(name):
if name in ["TIMESTAMP", "StringID"]:
dialect = context.get_bind().dialect.name
module = globals()
# Lookup the type based on the dialect specific type, or fallback to the generic type
type_ = module.get(f'_{dialect}_{name}',
None) or module.get(f'_sa_{name}')
val = module[name] = type_()
return val
raise AttributeError(f"module {__name__} has no attribute {name}")
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
# Fill SequenceNumber for existing invoices
op.execute("""
INSERT INTO task_sequence_number (`task_id`, `index`, `sequence`)
SELECT task.id, official_number, 'invoice_year' from task
LEFT JOIN invoice on task.id = invoice.id
LEFT JOIN cancelinvoice on cancelinvoice.id = task.id
WHERE task.official_number IS NOT NULL
;""")
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
# Fill SequenceNumber for existing invoices
op.execute("""
INSERT INTO task_sequence_number (`task_id`, `index`, `sequence`)
SELECT task.id, official_number, 'invoice_year' from task
LEFT JOIN invoice on task.id = invoice.id
LEFT JOIN cancelinvoice on cancelinvoice.id = task.id
WHERE task.official_number IS NOT NULL
;""")
def upgrade():
from kotti.resources import DBSession
from alembic.context import get_bind
conn = get_bind()
if conn.engine.dialect.name == 'mysql':
update = "update nodes set path = concat(path, '/') where path not \
like '%/'"
else:
update = "update nodes set path = path || '/' where path not like '%/'"
DBSession.execute(update)
def upgrade():
op.add_column('bank_account',
sa.Column('code_journal', sa.String(120), nullable=False))
req = "select config_value from config where config_name='receipts_code_journal';"
from alembic.context import get_bind
from autonomie_base.models.base import DBSESSION
conn = get_bind()
res = conn.execute(req).scalar()
if res is not None:
req = "update bank_account set code_journal='%s'" % res
conn.execute(req)
session = DBSESSION()
from zope.sqlalchemy import mark_changed
mark_changed(session)
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
from autonomie.models.task import Task
op.execute("""
UPDATE task
LEFT JOIN invoice on task.id = invoice.id
LEFT JOIN cancelinvoice on cancelinvoice.id = task.id
SET official_number = CONCAT(IFNULL(prefix, ''), official_number)
WHERE (cancelinvoice.id IS NOT NULL) OR (invoice.id IS NOT NULL)
;""")
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
from autonomie.models.task import Task
op.execute("""
UPDATE task
LEFT JOIN invoice on task.id = invoice.id
LEFT JOIN cancelinvoice on cancelinvoice.id = task.id
SET official_number = CONCAT(IFNULL(prefix, ''), official_number)
WHERE (cancelinvoice.id IS NOT NULL) OR (invoice.id IS NOT NULL)
;""")
def upgrade():
from alembic.context import get_bind
conn = get_bind()
if conn.engine.dialect.name == 'mysql':
op.add_column('nodes', sa.Column('path', sa.Unicode(1000)))
else:
op.add_column('nodes', sa.Column('path', sa.Unicode(1000), index=True))
from kotti import DBSession
from kotti.resources import Node
for node in DBSession.query(Node).with_polymorphic([Node]):
reversed_lineage = reversed(tuple(lineage(node)))
node.path = '/'.join(node.__name__ for node in reversed_lineage) or '/'
def upgrade():
from kotti.resources import DBSession
from alembic.context import get_bind
conn = get_bind()
if conn.engine.dialect.name == 'mysql':
update = "update nodes set path = concat(path, '/') where path not \
like '%/'"
else:
update = "update nodes set path = path || '/' where path not like '%/'"
DBSession.execute(update)
def upgrade():
op.add_column(
'bank_account',
sa.Column('code_journal', sa.String(120), nullable=False)
)
req = "select config_value from config where config_name='receipts_code_journal';"
from alembic.context import get_bind
from autonomie_base.models.base import DBSESSION
conn = get_bind()
res = conn.execute(req).scalar()
if res is not None:
req = "update bank_account set code_journal='%s'" % res
conn.execute(req)
session = DBSESSION()
from zope.sqlalchemy import mark_changed
mark_changed(session)
def upgrade():
from alembic.context import get_bind
conn = get_bind()
if conn.engine.dialect.name == 'mysql':
op.add_column('nodes', sa.Column('path', sa.Unicode(1000)))
else:
op.add_column('nodes', sa.Column('path', sa.Unicode(1000), index=True))
from kotti import DBSession
from kotti.resources import Node
for node in DBSession.query(Node).with_polymorphic([Node]):
reversed_lineage = reversed(tuple(lineage(node)))
node.path = u'/'.join(
node.__name__ for node in reversed_lineage) or u'/'
def upgrade():
from kotti.resources import DBSession
from alembic.context import get_bind
conn = get_bind()
if conn.engine.dialect.name == 'mysql':
update = "UPDATE nodes " \
"SET path = concat(path, '/') " \
"WHERE path NOT LIKE '%/'"
else:
update = "UPDATE nodes " \
"SET path = path || '/' " \
"WHERE path NOT LIKE '%/'"
DBSession.execute(update)
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
op.alter_column(
"custom_invoice_book_entry_module",
"percentage",
type_=sa.Float(),
nullable=False,
)
op.alter_column(
"task",
"internal_number",
type_=sa.String(255),
nullable=False,
)
def upgrade():
connection = context.get_bind()
metadata = sa.MetaData(bind=connection)
consumers = sa.Table('consumers', metadata, autoload=True)
allocations = sa.Table('allocations', metadata, autoload=True)
alloc_to_consumer = sa.outerjoin(
allocations, consumers, allocations.c.consumer_id == consumers.c.uuid)
cols = [
sqlfunc.count(),
]
sel = sa.select(cols)
sel = sel.select_from(alloc_to_consumer)
sel = sel.where(consumers.c.id.is_(None))
if connection.scalar(sel):
raise Exception('There is at least one allocation record which is '
'missing a consumer record. Run the "placement-manage '
'db online_data_migrations" command.')
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
op.alter_column(
"custom_invoice_book_entry_module",
"percentage",
type_=sa.Float(),
nullable=False,
)
op.alter_column(
"task",
"internal_number",
type_=sa.String(255),
nullable=False,
)
def migrate_datas():
from autonomie_base.models.base import DBSESSION
from autonomie.models.config import Config
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
initial_values = [
(
'bookentry_facturation_label_template',
'{invoice.customer.label} {company.name}'
),
(
'bookentry_contribution_label_template',
"{invoice.customer.label} {company.name}"
),
(
'bookentry_rg_interne_label_template',
"RG COOP {invoice.customer.label} {company.name}"
),
(
'bookentry_rg_client_label_template',
"RG {invoice.customer.label} {company.name}"
),
(
'bookentry_expense_label_template',
"{beneficiaire}/frais {expense_date:%-m %Y}"
),
(
'bookentry_payment_label_template',
"{company.name} / Rgt {invoice.customer.label}"
),
(
'bookentry_expense_payment_main_label_template',
"{beneficiaire_LASTNAME} / REMB FRAIS {expense_date:%B/%Y}"
),
(
'bookentry_expense_payment_waiver_label_template',
"Abandon de créance {beneficiaire_LASTNAME} {expense_date:%B/%Y}"
),
]
for key, val in initial_values:
Config.set(key, val)
def upgrade():
from alembic.context import get_bind
op.add_column(
"user_datas",
sa.Column(
"situation_situation_id",
sa.Integer,
sa.ForeignKey("cae_situation_option.id"),
)
)
op.add_column(
"configurable_option",
sa.Column(
"order",
sa.Integer,
default=0
)
)
from autonomie.models.user import (
CaeSituationOption,
)
from autonomie_base.models.base import DBSESSION
temp_dict = {}
for key, value in SITUATION_OPTIONS:
if key == "integre":
option = CaeSituationOption(label=value, is_integration=True)
else:
option = CaeSituationOption(label=value)
DBSESSION().add(option)
DBSESSION().flush()
temp_dict[key] = option.id
conn = get_bind()
query = "select id, situation_situation from user_datas"
result = conn.execute(query)
for id, situation in result:
option_id = temp_dict.get(situation)
if option_id is None:
continue
query = "update user_datas set situation_situation_id='{0}' \
where id='{1}'".format(option_id, id)
op.execute(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 upgrade():
from autonomie.models import DBSESSION
session = DBSESSION()
from autonomie.models.activity import ActivityAction
from alembic.context import get_bind
for name in "subaction_id", "action_id":
col = sa.Column(name, sa.Integer, sa.ForeignKey("activity_action.id"))
op.add_column("activity", col)
label_request = "select id, action_label, subaction_label from activity"
conn = get_bind()
result = conn.execute(label_request)
already_added = {}
for id, action_label, subaction_label in result:
if (action_label, subaction_label) not in already_added.keys():
found = False
for key, value in already_added.items():
if action_label == key[0]:
action_id = value[0]
found = True
if not found:
action = ActivityAction(label=action_label)
session.add(action)
session.flush()
action_id = action.id
subaction = ActivityAction(label=subaction_label,
parent_id=action_id)
session.add(subaction)
session.flush()
subaction_id = subaction.id
already_added[(action_label, subaction_label)] = (action_id,
subaction_id)
else:
action_id, subaction_id = already_added[(action_label,
subaction_label)]
op.execute("update activity set action_id={0}, subaction_id={1} \
where id={2}".format(action_id, subaction_id, id))
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_base.models.base import DBSESSION
session = DBSESSION()
from autonomie.models.activity import ActivityAction
from alembic.context import get_bind
for name in "subaction_id", "action_id":
col = sa.Column(name, sa.Integer, sa.ForeignKey("activity_action.id"))
op.add_column("activity", col)
label_request = "select id, action_label, subaction_label from activity"
conn = get_bind()
result = conn.execute(label_request)
already_added = {}
for id, action_label, subaction_label in result:
if (action_label, subaction_label) not in already_added.keys():
found = False
for key, value in already_added.items():
if action_label == key[0]:
action_id = value[0]
found = True
if not found:
action = ActivityAction(label=action_label)
session.add(action)
session.flush()
action_id = action.id
subaction = ActivityAction(label=subaction_label, parent_id=action_id)
session.add(subaction)
session.flush()
subaction_id = subaction.id
already_added[(action_label, subaction_label)] = (action_id, subaction_id)
else:
action_id, subaction_id = already_added[(action_label, subaction_label)]
op.execute("update activity set action_id={0}, subaction_id={1} \
where id={2}".format(action_id, subaction_id, id))
def migrate_datas():
from autonomie_base.models.base import DBSESSION
from autonomie.models.config import Config
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
deprecated_conf_keys = [
'compte_cgscop',
'compte_cg_debiteur',
'compte_cg_organic',
'compte_cg_debiteur_organic',
'compte_cg_assurance',
'taux_assurance',
'taux_cgscop',
'taux_contribution_organic',
'sage_assurance',
'sage_cgscop',
'sage_organic',
]
q = Config.query().filter(Config.name.in_(deprecated_conf_keys))
q.delete(synchronize_session=False)
session.flush()
def migrate_datas():
from autonomie_base.models.base import DBSESSION
from autonomie.models.config import Config
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
deprecated_conf_keys = [
'compte_cgscop',
'compte_cg_debiteur',
'compte_cg_organic',
'compte_cg_debiteur_organic',
'compte_cg_assurance',
'taux_assurance',
'taux_cgscop',
'taux_contribution_organic',
'sage_assurance',
'sage_cgscop',
'sage_organic',
]
q = Config.query().filter(Config.name.in_(deprecated_conf_keys))
q.delete(synchronize_session=False)
session.flush()
def upgrade():
from alembic.context import get_bind
conn = get_bind()
result = conn.execute("""
select invoice.IDTask, invoice.tva, invoice.discountHT, invoice.expenses, invoice.paymentMode, task.statusDate from coop_invoice as invoice join coop_task as task on task.IDTask=invoice.IDTask where task.CAEStatus='paid';
""").fetchall()
for i in result:
id_, tva, discountHT, expenses, paymentMode, statusDate = i
lines = conn.execute("""
select cost, quantity from coop_invoice_line where IDTask='%s'""" %
id_).fetchall()
totalht = get_ht(lines, discountHT)
tva_amount = int(float(totalht) * (max(int(tva), 0) / 10000.0))
ttc = tva_amount + totalht
total = ttc + expenses
date = datetime.datetime.fromtimestamp(float(statusDate))
# Adding one payment for each invoice that has been marked as "paid"
conn.execute("""
insert into payment (mode, amount, date, task_id) VALUE ('%s', '%s', '%s', '%s')"""
% (paymentMode, total, date, id_))
# Using direct sql allows to enforce CAEStatus modifications
# Ref #573
# Ref #551
op.execute("""
update coop_task as t join coop_invoice as inv on t.IDTask=inv.IDTask set CAEStatus='resulted' WHERE t.CAEStatus='paid';
""")
op.execute("""
update coop_task set CAEStatus='resulted' where CAEStatus='gencinv';
""")
op.execute("""
update coop_task set CAEStatus='valid' where CAEStatus='sent' OR CAEStatus='recinv';
""")
if table_exists("coop_cancel_invoice"):
op.execute("""
update coop_task as t join coop_cancel_invoice as est on t.IDTask=est.IDTask SET t.CAEStatus='valid' WHERE t.CAEStatus='paid';
""")
def upgrade(active_plugins=None, options=None):
if not migration.should_run(active_plugins, migration_for_plugins):
return
if context.is_offline_mode():
tables = ['servicedefinitions', 'servicetypes']
else:
connection = context.get_bind()
tables = connection.engine.table_names()
op.create_table(
'providerresourceassociations',
sa.Column('provider_name', sa.String(length=255), nullable=False),
sa.Column('resource_id',
sa.String(length=36),
nullable=False,
unique=True),
)
# Tables are not created if grizzly didn't use neutron-db-manage before
# starting neutron service.
for table in ('servicedefinitions', 'servicetypes'):
if table in tables:
op.drop_table(table)
Revises: 27e81ea4d86
Create Date: 2014-02-27 02:00:25.694782
"""
from alembic import context
from alembic.op import (create_index, create_table,
drop_index, drop_table, execute)
from sqlalchemy.schema import Column, PrimaryKeyConstraint
from sqlalchemy.types import DateTime, Integer, String
# revision identifiers, used by Alembic.
revision = '2d8b17e13d1'
down_revision = '27e81ea4d86'
driver_name = context.get_bind().dialect.name
def upgrade():
create_table(
'teams',
Column('id', Integer, nullable=False),
Column('name', String),
Column('created_at', DateTime(timezone=True), nullable=False),
PrimaryKeyConstraint('id')
)
create_index('ix_teams_created_at', 'teams', ['created_at'])
create_index('ix_teams_name', 'teams', ['name'])
def downgrade():
def upgrade():
from autonomie.models import DBSESSION
from autonomie.models.workshop import WorkshopAction
from alembic.context import get_bind
session = DBSESSION()
conn = get_bind()
col = sa.Column("activity_id", sa.Integer(), sa.ForeignKey("company_activity.id"))
op.add_column("company_datas", col)
col = sa.Column("archived", sa.Boolean(), default=False, server_default="0")
op.add_column("customer", col)
# Migration de accompagnement_header.png en activity_header.png
op.execute(
'update config_files set config_files.key="activity_header_img.png" where \
config_files.key="accompagnement_header.png";'
)
# Le bas de page des pdfs est celui par defaut pour les ateliers et rdv
from autonomie.models.config import Config
val = Config.get("coop_pdffootertext").value
if val:
for key in ("activity", "workshop"):
config_key = "%s_footer" % key
config = Config.set(config_key, val)
# Migration de la taille des libelles pour les actions des rendez-vous
op.execute("alter table activity_action modify label VARCHAR(255)")
# Migration des intitules des ateliers
# 1- Ajout des nouvelles foreignkey
for name in "info1_id", "info2_id", "info3_id":
col = sa.Column(name, sa.Integer, sa.ForeignKey("workshop_action.id"))
op.add_column("workshop", col)
# 2- création des options en fonction des valeurs en durs
request = "select id, info1, info2, info3 from workshop"
result = conn.execute(request)
already_added = {}
for id, info1, info2, info3 in result:
info1 = info1.lower()
info2 = info2.lower()
info3 = info3.lower()
info1_id = info2_id = info3_id = None
if (info1, info2, info3) not in already_added.keys():
for key, value in already_added.items():
if key[0] == info1 and info1:
info1_id = value[0]
if key[1] == info2 and info2:
info2_id = value[1]
if info1_id is None and info1:
w = WorkshopAction(label=info1)
session.add(w)
session.flush()
info1_id = w.id
if info2_id is None and info2:
w = WorkshopAction(label=info2, parent_id=info1_id)
session.add(w)
session.flush()
info2_id = w.id
if info3:
w = WorkshopAction(label=info3, parent_id=info2_id)
session.add(w)
session.flush()
info3_id = w.id
already_added[(info1, info2, info3)] = (info1_id, info2_id, info3_id)
else:
info1_id, info2_id, info3_id = already_added[(info1, info2, info3)]
request = "update workshop "
if info1_id:
request += "set info1_id={0}".format(info1_id)
if info2_id:
request += ", info2_id={0}".format(info2_id)
if info3_id:
request += ", info3_id={0}".format(info3_id)
request += " where id={0}".format(id)
op.execute(request)
def migrate_datas():
from autonomie_base.models.base import DBSESSION
session = DBSESSION()
from alembic.context import get_bind
conn = get_bind()
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 migrate_datas():
from autonomie.models.project.types import BusinessType
from autonomie.models.config import Config
from autonomie.models.task.mentions import TaskMention
from autonomie.models.project.mentions import BusinessTypeTaskMention
from autonomie_base.models.base import DBSESSION
from alembic.context import get_bind
session = DBSESSION()
conn = get_bind()
# Collect business type ids
business_type_ids = [b[0] for b in session.query(BusinessType.id)]
# for each fixed config key we now use mentions
for index, (doctype, key, label, title) in enumerate((
(
'estimation',
'coop_estimationfooter',
u"Informations sur l'acceptation des devis",
u"Acceptation du devis",
),
(
"invoice",
"coop_invoicepayment",
u"Informations de paiement pour les factures",
u"Mode de paiement",
),
(
"invoice",
"coop_invoicelate",
u"Informations sur les retards de paiement",
u"Retard de paiement",
)
)):
# We retrieve the configurated value
value = Config.get_value(key, "")
mention = TaskMention(
order=index,
label=label,
title=title,
full_text=value.replace(
'%IBAN%', "{IBAN}").replace(
'%RIB%', "{RIB}").replace(
'%ENTREPRENEUR%', '{name}')
)
session.add(mention)
session.flush()
for btype_id in business_type_ids:
rel = BusinessTypeTaskMention(
task_mention_id=mention.id,
business_type_id=btype_id,
doctype=doctype,
mandatory=True,
)
session.add(rel)
session.flush()
op.execute(
u"INSERT INTO mandatory_task_mention_rel (task_id, mention_id) \
SELECT task.id, {mention_id} from task join node on task.id=node.id where \
node.type_='{type_}'".format(mention_id=mention.id, type_=doctype)
)
def upgrade():
from autonomie.models.activity import Attendance, Activity
from autonomie.models import DBSESSION
from alembic.context import get_bind
session = DBSESSION()
# Migrating attendance relationship
query = "select event.id, event.status, rel.account_id, rel.activity_id from activity_participant rel inner join activity on rel.activity_id=activity.id LEFT JOIN event on event.id=activity.id"
conn = get_bind()
result = conn.execute(query)
handled = []
for event_id, status, user_id, activity_id in result:
if status == 'planned':
user_status = 'registered'
elif status == 'excused':
user_status = 'excused'
status = 'cancelled'
elif status == 'closed':
user_status = 'attended'
elif status == 'absent':
user_status = 'absent'
status = 'cancelled'
# create attendance for each participant
if (user_id, activity_id) not in handled:
a = Attendance()
a.status = user_status
a.account_id = user_id
a.event_id = activity_id
session.add(a)
session.flush()
# Update the event's status regarding the new norm
query = "update event set status='{0}' where id='{1}';".format(
status, event_id,)
op.execute(query)
handled.append((user_id, activity_id,))
# Migrating activity to add duration and use datetimes
op.add_column('activity', sa.Column('duration', sa.Integer, default=0))
op.alter_column(
'event',
'date',
new_column_name='datetime',
type_=sa.DateTime()
)
query = "select id, conseiller_id from activity;"
result = conn.execute(query)
values = []
for activity_id, conseiller_id in result:
values.append("(%s, %s)" % (activity_id, conseiller_id))
if values != []:
query = "insert into activity_conseiller (`activity_id`, `account_id`) \
VALUES {0}".format(','.join(values))
op.execute(query)
op.execute("alter table activity drop foreign key `activity_ibfk_2`;")
op.drop_column('activity', 'conseiller_id')
op.drop_table('activity_participant')
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():
### commands auto generated by Alembic - please adjust! ##
bind = context.get_bind()
if bind.dialect.name == 'postgresql':
create_unique_constraint('users_email_key', 'users', ['email'])
def downgrade():
### commands auto generated by Alembic - please adjust! ###
bind = context.get_bind()
if bind.dialect.name == 'postgresql':
drop_constraint('users_email_key', 'users')
