def downgrade(): for factory in (EstimationLine, InvoiceLine, CancelInvoiceLine): for line in factory.query(): line.unity = translate_inverse(line.unity) DBSESSION().merge(line) for value in WorkUnit.query(): DBSESSION().delete(value)
def upgrade(): logger = logging.getLogger("alembic.add_compte_cg") op.add_column("tva", sa.Column("compte_cg", sa.String(125), default="")) op.add_column("tva", sa.Column("code", sa.String(125), default="")) op.add_column("company", sa.Column("contribution", sa.Integer)) op.add_column("customer", sa.Column("compte_cg", sa.String(125), default="")) op.add_column("customer", sa.Column("compte_tiers", sa.String(125), default="")) # Ajout du code produit au ligne des factures et avoirs for table in ("invoice_line", "cancelinvoice_line"): op.add_column(table, sa.Column("product_id", sa.Integer, default="")) # Ajout d'un tag "exporte" aux factures et avoirs for table in ("invoice", "cancelinvoice"): op.add_column(table, sa.Column("exported", sa.Boolean())) # Les factures et avoirs deja validees sont considerees comme exportees logger.warn(u"On tag des factures comme exportees") for invoice in Invoice.query(): if invoice.CAEStatus in Invoice.valid_states: invoice.exported = True DBSESSION().merge(invoice) logger.warn(u"officialNumber : {0.officialNumber} \ {0.financial_year}".format(invoice)) for cinv in CancelInvoice.query(): if cinv.CAEStatus in CancelInvoice.valid_states: cinv.exported = True DBSESSION().merge(cinv) logger.warn(u"officialNumber : {0.officialNumber} \ {0.financial_year}".format(cinv))
def upgrade(): for table in "invoice", "cancelinvoice", "manualinv": if not column_exists(table, "financial_year"): op.add_column(table, sa.Column("financial_year", sa.Integer, nullable=False)) for type_ in (Invoice, CancelInvoice, ManualInvoice): for document in type_.query(): document.financial_year = document.taskDate.year DBSESSION.merge(document)
def add_customer(**kw): #company, customer_name, customer_code, customer_lastname): customer = Customer(**kw) session = DBSESSION() session.add(customer) session.flush() print u"Added customer to %s: %s" % ( customer.company.name, customer.name) return customer
def upgrade(): from autonomie.models.task import Invoice, CancelInvoice, ManualInvoice from autonomie.models import DBSESSION for table in "invoice", "cancelinvoice", "manualinv": if not column_exists(table, "financial_year"): op.add_column( table, sa.Column("financial_year", sa.Integer, nullable=False)) for type_ in (Invoice, CancelInvoice, ManualInvoice): for document in type_.query(): document.financial_year = document.taskDate.year DBSESSION.merge(document)
def upgrade(): # Adding some characters to the Lines for table in "estimation_line", "invoice_line", "cancelinvoice_line": op.alter_column(table, "unity", type_=sa.String(100)) for value in UNITS: unit = WorkUnit(label=value) DBSESSION().add(unit) for factory in (EstimationLine, InvoiceLine, CancelInvoiceLine): for line in factory.query(): line.unity = translate_unity(line.unity) DBSESSION().merge(line)
def downgrade(): from autonomie.models.task import WorkUnit from autonomie.models.task.estimation import EstimationLine from autonomie.models.task.invoice import InvoiceLine from autonomie.models.task.invoice import CancelInvoiceLine from autonomie.models import DBSESSION for factory in (EstimationLine, InvoiceLine, CancelInvoiceLine): for line in factory.query(): line.unity = translate_inverse(line.unity) DBSESSION().merge(line) for value in WorkUnit.query(): DBSESSION().delete(value)
def add_customer(company, customer_name, customer_code, customer_lastname): customer = Customer() customer.name = customer_name #u"Institut médical Dupont & Dupond" customer.contactLastName = customer_lastname # "Dupont" customer.code = customer_code #"IMDD" customer.company = company session = DBSESSION() session.add(customer) session.flush() print u"Added customer to %s: %s" % (company.name, customer_name) return customer
def downgrade(): for p in PaymentMode.query(): DBSESSION().delete(p) for p in Payment.query(): if p.mode == u"par chèque": p.mode = u"cheque" elif p.mode == u"par virement": p.mode = u"virement" elif p.mode == u"en liquide": p.mode = u"liquide" else: p.mode = "inconnu" DBSESSION().merge(p)
def migrate_projects_to_multiple_clients(): """ move project's client to the manytomany relationship """ from autonomie.models.client import Client for proj in DBSESSION().query(Project): try: client = Client.get(proj.client_id) if client is not None: proj.clients.append(client) DBSESSION().merge(proj) except: continue
def add_user(login, password, group, firstname="", lastname=""): user = User(login=login, firstname=firstname, lastname=lastname) user.set_password(password) user.primary_group = group session = DBSESSION() session.add(user) session.flush() group_name = GROUPS[group] print "Added %s: %s/%s" % (group_name, login, password) return user
def upgrade(): for payment in Payment.query(): if payment.mode in (u"cheque", u"CHEQUE"): payment.mode = u"par chèque" elif payment.mode in (u"virement", u"VIREMENT"): payment.mode = u"par virement" elif payment.mode in (u"liquide", u"LIQUIDE"): payment.mode = u"en liquide" else: payment.mode = "mode de paiement inconnu" DBSESSION().merge(payment) for mode in (u"par chèque", u"par virement", u"en liquide"): pmode = PaymentMode(label=mode) DBSESSION().add(pmode)
def upgrade(): logger = logging.getLogger("alembic.migrate_code_compta") op.add_column("company", sa.Column("code_compta", sa.String(30), default=0)) dbsession = DBSESSION() for user in User.query(): code_compta = user.code_compta companies = user.companies if code_compta not in [u"0", None, u""]: if len(companies) == 1: company = companies[0] company.code_compta = code_compta dbsession.merge(company) else: logger.warn(u"User {0} has a code_compta and multiple \ companies".format(user.id))
def force_rename_table(old, new): from autonomie.models import DBSESSION conn = DBSESSION.connection() if table_exists(old): if table_exists(new): op.drop_table(new) op.rename_table(old, new)
def run_migrations_online(): if DBSESSION.bind is None: raise ValueError( "\nYou must do Autonomie migrations using the 'autonomie-migrate' script" "\nand not through 'alembic' directly." ) transaction.begin() connection = DBSESSION.connection() context.configure( connection=connection, target_metadata=DBBASE.metadata, ) try: context.run_migrations() except: traceback.print_exc() transaction.abort() else: transaction.commit() finally: #connection.close() pass
def purge_line_type(factory): """ Supprimer les lignes orphelines pour le type factory """ for line in factory.query(): if line.task is None: DBSESSION().delete(line)
def file_delete_view(context, request): """ View for file deletion """ parent = context.parent DBSESSION().delete(context) return HTTPFound(request.route_path(parent.type_, id=parent.id))
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 do_stamp(rev, context, revision=revision): current = context._current_rev() if revision is None: revision = context.script.get_current_head() elif revision == 'None': revision = None context._update_current_rev(current, revision) mark_changed(DBSESSION()) return []
def upgrade(): from autonomie.models.client import Client for table in ("estimation", "invoice", "cancelinvoice"): op.add_column(table, sa.Column("address", sa.Text, default="")) op.add_column( table, sa.Column("client_id", sa.Integer, sa.ForeignKey("customer.id"))) for obj in (Invoice, CancelInvoice, Estimation): for doc in obj.query(): if doc.project is not None and doc.project.client_id is not None: client = Client.get(doc.project.client_id) if client is not None: doc.address = client.full_address doc.client_id = client.id if len(doc._number) > 10: doc._number = doc._number[10:] DBSESSION.merge(doc)
def table_exists(tbl): from autonomie.models import DBSESSION conn = DBSESSION.connection() ret = False try: conn.execute("select * from `%s`" % tbl) ret = True except: pass return ret
def column_exists(tbl, column_name): from autonomie.models import DBSESSION conn = DBSESSION.connection() ret = False try: conn.execute("select %s from %s" % (column_name, tbl)) ret = True except: pass return ret
def upgrade(): from autonomie.models.client import Client for table in ("estimation", "invoice", "cancelinvoice"): op.add_column(table, sa.Column("address", sa.Text, default="")) op.add_column(table, sa.Column("client_id", sa.Integer, sa.ForeignKey("customer.id"))) for obj in (Invoice, CancelInvoice, Estimation): for doc in obj.query(): if doc.project is not None and doc.project.client_id is not None: client = Client.get(doc.project.client_id) if client is not None: doc.address = client.full_address doc.client_id = client.id if len(doc._number) > 10: doc._number = doc._number[10:] DBSESSION.merge(doc)
def downgrade(): for table in ("estimation", "invoice", "cancelinvoice"): op.drop_column(table, "address") op.drop_column(table, "client_id") for obj in (Invoice, CancelInvoice, Estimation): for doc in obj.query(): if doc.project is not None and doc.client is not None: doc._number = "%s_%s_%s" % (doc.project.code, doc.client.code, doc._number) DBSESSION().merge(doc)
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 add_admin(arguments, env): """ Add an admin user to the database """ login = get_value(arguments, 'user', 'admin.majerti') password = get_value(arguments, 'pwd', get_pwd()) firstname = get_value(arguments, 'firstname', 'Admin') lastname = get_value(arguments, 'lastname', 'Majerti') email = get_value(arguments, 'email', '*****@*****.**') user = User(login=login, firstname=firstname, primary_group=1, #is an admin lastname=lastname, email=email ) user.set_password(password) db = DBSESSION() db.add(user) db.flush() print u"Creating account %s with password %s" % (login, unicode(password)) return user
def add_expense_type(type_, **kwargs): if type_ == 'km': e = ExpenseKmType(**kwargs) elif type_ == 'tel': e = ExpenseTelType(**kwargs) else: e = ExpenseType(**kwargs) session = DBSESSION() session.add(e) session.flush()
def add_customer( **kw): #company, customer_name, customer_code, customer_lastname): customer = Customer(**kw) session = DBSESSION() session.add(customer) session.flush() print u"Added customer to %s: %s" % (customer.company.name, customer.name) return customer
def set_configuration(): print("Adding configuration elements") add_payment_mode(u"par chèque") add_payment_mode(u"par virement") add_tva(0) add_tva(700) add_tva(1960, 1) add_unity(u"heure(s)") add_unity(u"jour(s)") add_unity(u"mois") add_unity(u"forfait") add_expense_type("", label=u"Restauration", code='0001') add_expense_type("", label=u"Transport", code='0002') add_expense_type("", label=u"Matériel", code="0003") add_expense_type("km", label=u"Scooter", code="0004", amount='0.124') add_expense_type("km", label=u"Voiture", code="0005", amount="0.235") add_expense_type("tel", label=u"Adsl-Tel fix", code="0006", percentage="80") add_expense_type("tel", label=u"Mobile", code="0007", percentage="80") for i in (u'Rendez-vous mensuel', u'Entretien individuel'): add_activity_type(i) for i in ( u'par skype', u'en direct', u'par mail', u'par téléphone', ): add_activity_mode(i) a = add_activity_action( u"Projet FSE 2014 - Passerelle pour l'entreprenariat collectif", ) add_activity_action( u"Module 3 : Accompagnement renforcé - Etape : Business model \ commercial, économique et social", parent=a) session = DBSESSION() from autonomie.models import initialize initialize.populate_situation_options(session) initialize.populate_groups(session)
def add_phase(project, phase_name): phase = Phase(name=phase_name) phase.project = project session = DBSESSION() session.add(phase) session.flush() print u"Added phase to %s: %s" % (project.name, phase_name) return phase
def add_project(customer, company, project_name, project_code): project = Project(name=project_name, code=project_code) project.customers.append(customer) project.company = company session = DBSESSION() session.add(project) session.flush() print u"Added project to %s for %s: %s" % (company.name, customer.name, project_name) return project
def add_company(user, company_name, goal=""): company = Company() company.name = company_name company.goal = goal or u"Entreprise de %s" % user.login user.companies.append(company) session = DBSESSION() session.add(company) session.flush() print "Added company for %s: %s" % (user.login, company_name) return company
def add_user(login, password, group, firstname="", lastname="", email=""): user = User(login=login, firstname=firstname, lastname=lastname, email=email) user.set_password(password) user.groups.append(GROUPS[group]) session = DBSESSION() session.add(user) session.flush() group_name = GROUPS[group] print "Added %s: %s/%s" % (group_name, login, password) return user
def run_migrations_online(): if DBSESSION.bind is None: raise ValueError( "\nYou must do Autonomie migrations using the 'autonomie-migrate' script" "\nand not through 'alembic' directly.") transaction.begin() connection = DBSESSION.connection() context.configure( connection=connection, target_metadata=DBBASE.metadata, ) try: context.run_migrations() except: traceback.print_exc() transaction.abort() else: transaction.commit() finally: #connection.close() pass
def user_add(arguments, env): """ Add a user in the database """ login = get_value(arguments, 'user', 'admin.majerti') login = login.decode('utf-8') password = get_value(arguments, 'pwd', get_pwd()) password = password.decode('utf-8') firstname = get_value(arguments, 'firstname', 'Admin') lastname = get_value(arguments, 'lastname', 'Majerti') email = get_value(arguments, 'email', '*****@*****.**') group = get_value(arguments, 'group', None) user = User( login=login, firstname=firstname, lastname=lastname, email=email ) if group: user.groups.append(group) user.set_password(password) db = DBSESSION() db.add(user) db.flush() print(u""" Account created : ID : {0.id} Login : {0.login} Firstname : {0.firstname} Lastname : {0.lastname} Email : {0.email} Groups : {0.groups} """.format(user)) if 'pwd' not in arguments: print(u""" Password : {0}""".format(password)) return user
def add_admin(arguments, env): """ Add an admin user to the database """ login = get_value(arguments, 'user', 'admin.majerti') password = get_value(arguments, 'pwd', get_pwd()) firstname = get_value(arguments, 'firstname', 'Admin') lastname = get_value(arguments, 'lastname', 'Majerti') email = get_value(arguments, 'email', '*****@*****.**') user = User( login=login, firstname=firstname, primary_group=1, #is an admin lastname=lastname, email=email) user.set_password(password) db = DBSESSION() db.add(user) db.flush() print u"Creating account %s with password %s" % (login, unicode(password)) return user
def add_activity_action(label, **kw): session = DBSESSION() a = ActivityAction(label=label, **kw) session.add(a) session.flush() return a
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 add_unity(label): t = WorkUnit(label=label) session = DBSESSION() session.add(t) session.flush()
def add_tva(value, default=0): t = Tva(name="%s %%" % (value/100.0), value=value, default=default) session = DBSESSION() session.add(t) session.flush()
def add_payment_mode(label): p = PaymentMode(label=label) session = DBSESSION() session.add(p) session.flush()
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 add_activity_type(label): session = DBSESSION() session.add(ActivityType(label=label)) session.flush()
def add_activity_mode(label): session = DBSESSION() session.add(ActivityMode(label=label)) session.flush()
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.models 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)