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():
op.execute("""
alter table coop_task add column type_ VARCHAR(30) NOT NULL;
""")
op.execute("""
update coop_task as t join coop_estimation as e on t.IDTask=e.IDTask set type_='estimation';
""")
op.execute("""
update coop_task as t join coop_invoice as i on t.IDTask=i.IDTask set type_='invoice';
""")
if table_exists("coop_cancel_invoice"):
op.execute("""
update coop_task as t join coop_cancel_invoice as c on t.IDTask=c.IDTask set type_='cancelinvoice';
""")
op.execute("""
update coop_task set type_='task' where type_='';
""")
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():
from autonomie.models.task.invoice import ManualInvoice
# Fix an error in table names for some installations
class OldManualInvoice(DBBASE):
"""
Modèle pour les factures manuelles (ancienne version)
"""
__tablename__ = 'manualinvoice'
id = Column('id', BigInteger, primary_key=True)
officialNumber = Column('sequence_id', BigInteger)
description = Column('libelle', String(255))
montant_ht = Column("montant_ht", Integer)
tva = Column("tva", Integer)
payment_ok = Column("paiement_ok", Integer)
statusDate = Column("paiement_date", Date())
paymentMode = Column("paiement_comment", String(255))
taskDate = Column("date_emission", Date(),
default=datetime.datetime.now)
created_at = Column("created_at", DateTime,
default=datetime.datetime.now)
updated_at = Column("updated_at", DateTime,
default=datetime.datetime.now,
onupdate=datetime.datetime.now)
client_id = Column('client_id', Integer,
ForeignKey('customer.code'))
company_id = Column('compagnie_id', Integer,
ForeignKey('company.id'))
if not table_exists("manualinvoice"):
force_rename_table('manual_invoice', 'manualinvoice')
from autonomie_base.models.base import DBSESSION
for manualinv in OldManualInvoice.query().all():
m = ManualInvoice()
m.montant_ht = manualinv.montant_ht
m.tva = manualinv.tva
m.client_id = manualinv.client_id
m.company_id = manualinv.company_id
m.description = manualinv.description
m.CAEStatus = 'valid'
if manualinv.payment_ok == '1' or manualinv.montant_ht < 0:
m.CAEStatus = "resulted"
if manualinv.montant_ht < 0:
if manualinv.paymentMode == u"chèque":
payment_mode = "CHEQUE"
elif manualinv.paymentMode == u"virement":
payment_mode = "VIREMENT"
else:
payment_mode = None
if payment_mode:
# We don't care about amounts since there is only one payment
payment = Payment(mode=payment_mode, date=manualinv.statusDate,
amount=0)
m.payments.append(payment)
m.statusDate = manualinv.statusDate
m.taskDate = manualinv.taskDate
m.creationDate = manualinv.created_at
m.updateDate = manualinv.updated_at
m.phase_id = 0
m.name = u"Facture manuelle %s" % manualinv.officialNumber
m.officialNumber = manualinv.officialNumber
m.owner_id = 0
DBSESSION.add(m)
def upgrade():
disable_constraints()
for table in OLD_TABLES:
if table_exists(table):
op.drop_table(table)
op.alter_column('accounts',
'active',
existing_type=mysql.VARCHAR(length=1),
nullable=False,
existing_server_default=sa.text(u"'Y'"))
op.alter_column('accounts',
'email',
existing_type=mysql.VARCHAR(length=100),
nullable=False)
op.alter_column('accounts',
'firstname',
existing_type=mysql.VARCHAR(length=50),
nullable=False)
op.alter_column('accounts',
'lastname',
existing_type=mysql.VARCHAR(length=50),
nullable=False)
op.alter_column('company',
'active',
existing_type=mysql.VARCHAR(length=1),
nullable=False,
existing_server_default=sa.text(u"'Y'"))
op.alter_column('company',
'creationDate',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('company',
'name',
existing_type=mysql.VARCHAR(length=150),
nullable=False)
op.alter_column('company',
'object',
existing_type=mysql.VARCHAR(length=255),
nullable=True)
op.alter_column('company',
'phone',
existing_type=mysql.VARCHAR(length=20),
nullable=True)
op.alter_column('company',
'updateDate',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('company_employee',
'account_id',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('company_employee',
'company_id',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('customer',
'address',
existing_type=mysql.TEXT(),
nullable=False)
op.alter_column('customer',
'city',
existing_type=mysql.VARCHAR(length=255),
nullable=False)
op.alter_column('customer',
'company_id',
existing_type=mysql.INTEGER(display_width=11),
nullable=True)
op.alter_column('configurable_option',
'label',
existing_type=mysql.VARCHAR(length=100),
nullable=False)
op.alter_column('customer',
'contactLastName',
existing_type=mysql.VARCHAR(length=255),
nullable=False)
op.alter_column('customer',
'creationDate',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('customer',
'name',
existing_type=mysql.VARCHAR(length=255),
nullable=False)
op.alter_column('customer',
'updateDate',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('customer',
'zipCode',
existing_type=mysql.VARCHAR(length=20),
nullable=False)
op.alter_column('estimation',
'deposit',
existing_type=mysql.INTEGER(display_width=11),
nullable=True,
existing_server_default=sa.text(u"'0'"))
# Clean table
for (table, column) in OLD_COLUMNS:
if column_exists(table, column):
op.drop_column(table, column)
enable_constraints()
def upgrade():
disable_constraints()
for table in OLD_TABLES:
if table_exists(table):
op.drop_table(table)
op.alter_column('accounts', 'active',
existing_type=mysql.VARCHAR(length=1),
nullable=False,
existing_server_default=sa.text(u"'Y'"))
op.alter_column('accounts', 'email',
existing_type=mysql.VARCHAR(length=100),
nullable=False)
op.alter_column('accounts', 'firstname',
existing_type=mysql.VARCHAR(length=50),
nullable=False)
op.alter_column('accounts', 'lastname',
existing_type=mysql.VARCHAR(length=50),
nullable=False)
op.alter_column('company', 'active',
existing_type=mysql.VARCHAR(length=1),
nullable=False,
existing_server_default=sa.text(u"'Y'"))
op.alter_column('company', 'creationDate',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('company', 'name',
existing_type=mysql.VARCHAR(length=150),
nullable=False)
op.alter_column('company', 'object',
existing_type=mysql.VARCHAR(length=255),
nullable=True)
op.alter_column('company', 'phone',
existing_type=mysql.VARCHAR(length=20),
nullable=True)
op.alter_column('company', 'updateDate',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('company_employee', 'account_id',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('company_employee', 'company_id',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('customer', 'address',
existing_type=mysql.TEXT(),
nullable=False)
op.alter_column('customer', 'city',
existing_type=mysql.VARCHAR(length=255),
nullable=False)
op.alter_column('customer', 'company_id',
existing_type=mysql.INTEGER(display_width=11),
nullable=True)
op.alter_column('configurable_option', 'label',
existing_type=mysql.VARCHAR(length=100),
nullable=False)
op.alter_column('customer', 'contactLastName',
existing_type=mysql.VARCHAR(length=255),
nullable=False)
op.alter_column('customer', 'creationDate',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('customer', 'name',
existing_type=mysql.VARCHAR(length=255),
nullable=False)
op.alter_column('customer', 'updateDate',
existing_type=mysql.INTEGER(display_width=11),
nullable=False)
op.alter_column('customer', 'zipCode',
existing_type=mysql.VARCHAR(length=20),
nullable=False)
op.alter_column('estimation', 'deposit',
existing_type=mysql.INTEGER(display_width=11),
nullable=True,
existing_server_default=sa.text(u"'0'"))
# Clean table
for (table, column) in OLD_COLUMNS:
if column_exists(table, column):
op.drop_column(table, column)
enable_constraints()