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()