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(): logger = logging.getLogger("alembic.autonomie") from autonomie_base.models.base import DBSESSION session = DBSESSION() from autonomie.models.config import Config import json from autonomie.models.files import ( File, FileType, ) json_str = Config.get_value("attached_filetypes", "[]") try: configured_filetypes = json.loads(json_str) except: logger.exception(u"Error in json str : %s" % json_str) configured_filetypes = [] if configured_filetypes: result = [] for filetype_label in configured_filetypes: if filetype_label: filetype = FileType(label=filetype_label) session.add(filetype) session.flush() result.append(filetype) for typ_ in result: query = File.query().filter_by(label=typ_.label) for file_ in query: file_.file_type_id = typ_.id session.merge(file_) session.flush()
def upgrade(): disable_listeners() op.add_column('task', sa.Column('date', sa.Date())) from autonomie.models.task import Task from autonomie_base.models.base import DBSESSION session = DBSESSION() for task in Task.query().filter(Task.type_!='manualinvoice'): task.date = task.taskDate session.merge(task) session.flush() op.execute("alter table groups modify label VARCHAR(255);") op.execute("alter table payment modify remittance_amount VARCHAR(255);") from autonomie.models.user import User, Group for group_id, group_name, group_label in GROUPS: group = session.query(Group).filter(Group.name==group_name).first() if group is None: group = Group(name=group_name, label=group_label) session.add(group) session.flush() users = session.query(User).filter(User.primary_group==group_id) for user in users: user._groups.append(group) session.merge(user) label = u"Peut saisir/modifier/supprimer les paiements de ses factures" group_name = "payment_admin" group = Group.query().filter(Group.name==group_name).first() if group is not None: group.label = label session.merge(group)
def upgrade(): from autonomie.models import user from autonomie_base.models.base import DBSESSION db = DBSESSION() for u in db.query(user.User)\ .filter(user.User.userdatas==None)\ .filter(user.User.primary_group==3): situation = "sortie" if u.email: userdata = user.UserDatas( situation_situation=situation, coordonnees_firstname=u.firstname, coordonnees_lastname=u.lastname, coordonnees_email1=u.email, coordonnees_civilite=u'?', ) userdata.user_id = u.id for company in u.companies: companydata = user.CompanyDatas( title=company.goal, name=company.name, ) userdata.activity_companydatas.append(companydata) db.add(userdata) db.flush()
def upgrade(): from autonomie_base.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 migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from autonomie.models.accounting.operations import AccountingOperationUpload for entry in AccountingOperationUpload.query(): entry.filetype = "analytical_balance" session.merge(entry) session.flush()
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 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 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 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 upgrade(): from autonomie.models.task import ( TaskLine, TaskLineGroup, Task, Estimation, CancelInvoice, Invoice, ) from autonomie_base.models.base import ( DBSESSION, ) session = DBSESSION() index = 0 query = Task.query() query = query.with_polymorphic([Invoice, CancelInvoice, Estimation]) query = query.filter( Task.type_.in_(['invoice', 'estimation', 'cancelinvoice']) ) for task in query: group = TaskLineGroup(task_id=task.id, order=0) for line in task.lines: tline = TaskLine( group=group, order=line.rowIndex, description=line.description, cost=line.cost, tva=line.tva, quantity=line.quantity, ) if hasattr(line, 'product_id'): tline.product_id = line.product_id session.add(tline) if index % 100 == 0: session.flush() op.alter_column( table_name='estimation_payment', column_name='rowIndex', new_column_name='order', type_=sa.Integer, )
def 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(group) session = DBSESSION() session.add(user) session.flush() print "Added %s: %s/%s" % (group, login, password) return user
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 upgrade(): from autonomie.models.task import ( TaskLine, TaskLineGroup, Task, Estimation, CancelInvoice, Invoice, ) from autonomie_base.models.base import ( DBSESSION, ) session = DBSESSION() index = 0 query = Task.query() query = query.with_polymorphic([Invoice, CancelInvoice, Estimation]) query = query.filter( Task.type_.in_(['invoice', 'estimation', 'cancelinvoice']) ) for task in query: try: task_lines = task.default_line_group.lines except: continue for index, line in enumerate(task.lines): try: task_line = task_lines[index] task_line.unity = line.unity session.merge(task_line) except: pass index += 1 if index % 100 == 0: session.flush()
def 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() Config.query().filter_by( app='autonomie', name='invoice_number_template' ).delete() prefix = session.query(Config.value).filter_by( app='autonomie', name='invoiceprefix', ).scalar() or '' default_format = Config( app='autonomie', name='invoice_number_template', value=prefix + '{SEQYEAR}' ) session.add(default_format) session.flush()
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_activity_mode(label): session = DBSESSION() session.add(ActivityMode(label=label)) session.flush()
def add_activity_type(label): session = DBSESSION() session.add(ActivityType(label=label)) session.flush()
def add_unity(label): t = WorkUnit(label=label) session = DBSESSION() session.add(t) session.flush()
def add_tva(value, default=False): 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 migrate_datas(situations_ids, stages_ids): """ Migrate parcours's data from user_datas and related tables to career_path """ from autonomie.models.career_path import CareerPath session = DBSESSION() cnx = op.get_bind() userdatas = cnx.execute("SELECT \ id,\ parcours_contract_type,\ parcours_start_date,\ parcours_end_date,\ parcours_last_avenant,\ parcours_taux_horaire,\ parcours_taux_horaire_letters,\ parcours_num_hours,\ parcours_salary,\ parcours_salary_letters,\ parcours_employee_quality_id,\ sortie_date,\ sortie_motif_id,\ sortie_type_id \ FROM user_datas") for u in userdatas: # Diagnotic diagnotics = cnx.execute( "SELECT date FROM date_diagnostic_datas WHERE date>'2000-01-01' AND userdatas_id=%s" % u.id) for diagnotic in diagnotics: session.add( CareerPath(userdatas_id=u.id, career_stage_id=stages_ids[0], start_date=diagnotic.date)) # CAPE capes = cnx.execute( "SELECT date, end_date FROM date_convention_cape_datas WHERE date>'2000-01-01' AND userdatas_id=%s" % u.id) for cape in capes: session.add( CareerPath(userdatas_id=u.id, career_stage_id=stages_ids[1], start_date=cape.date, end_date=cape.end_date, cae_situation_id=situations_ids[0], stage_type="entry")) # DPAE dpaes = cnx.execute( "SELECT date FROM date_dpae_datas WHERE date>'2000-01-01' AND userdatas_id=%s" % u.id) for dpae in dpaes: session.add( CareerPath(userdatas_id=u.id, career_stage_id=stages_ids[2], start_date=dpae.date)) # Contrat if u.parcours_start_date and u.parcours_contract_type is not None: from autonomie.models.career_path import TypeContratOption cdi_type = session.query(TypeContratOption).filter( TypeContratOption.label == u.parcours_contract_type.upper()).first() if cdi_type: cdi_type_id = cdi_type.id else: cdi_type_id = None session.add( CareerPath(userdatas_id=u.id, career_stage_id=stages_ids[3], start_date=u.parcours_start_date, end_date=u.parcours_end_date, cae_situation_id=situations_ids[1], stage_type="contrat", type_contrat_id=cdi_type_id, employee_quality_id=u.parcours_employee_quality_id, taux_horaire=u.parcours_taux_horaire, num_hours=u.parcours_num_hours)) # Avenant contrat if u.parcours_last_avenant: avenants = cnx.execute( "SELECT date, number FROM contract_history WHERE date>'2000-01-01' AND userdatas_id=%s" % u.id) for avenant in avenants: model_avenant = CareerPath(userdatas_id=u.id, career_stage_id=stages_ids[4], start_date=avenant.date, stage_type="amendment", amendment_number=avenant.number) if u.parcours_last_avenant == avenant.date: model_avenant.taux_horaire = u.parcours_taux_horaire model_avenant.num_hours = u.parcours_num_hours session.add(model_avenant) # Sortie if u.sortie_date: session.add( CareerPath(userdatas_id=u.id, career_stage_id=stages_ids[5], start_date=u.sortie_date, cae_situation_id=situations_ids[2], stage_type="exit", type_sortie_id=u.sortie_type_id, motif_sortie_id=u.sortie_motif_id)) # Historique des situations changes = cnx.execute( "SELECT date, situation_id FROM cae_situation_change WHERE date>'2000-01-01' AND userdatas_id=%s" % u.id) for change in changes: session.add( CareerPath(userdatas_id=u.id, start_date=change.date, cae_situation_id=change.situation_id)) # Sauvegarde du parcours de l'utilisateur session.flush()
def upgrade(): # Ajout et modification de la structure de données existantes op.execute("alter table project modify archived BOOLEAN;") for name in ('ht', 'tva', 'ttc'): col = sa.Column(name, sa.Integer, default=0) op.add_column('task', col) for col in ( sa.Column("project_id", sa.Integer, sa.ForeignKey('project.id')), sa.Column("customer_id", sa.Integer, sa.ForeignKey('customer.id')), sa.Column("_number", sa.String(10)), sa.Column("sequence_number", sa.Integer), sa.Column("display_units", sa.Integer, default=0), sa.Column('expenses', sa.Integer, default=0), sa.Column('expenses_ht', sa.Integer, default=0), sa.Column('address', sa.Text, default=""), sa.Column('payment_conditions', sa.Text, default=""), sa.Column("official_number", sa.Integer, default=None), ): op.add_column("task", col) col = sa.Column("sortie_type_id", sa.Integer, sa.ForeignKey('type_sortie_option.id')) op.add_column("user_datas", col) op.execute( "alter table user_datas modify parcours_num_hours float DEFAULT NULL") op.execute( "alter table external_activity_datas modify hours float DEFAULT NULL") op.execute( "alter table external_activity_datas modify brut_salary float DEFAULT NULL" ) col = sa.Column("cgv", sa.Text, default="") op.add_column("company", col) col = sa.Column('_acl', sa.Text) op.add_column("job", col) # Migration des donnees vers la nouvelle structure from alembic.context import get_bind conn = get_bind() from autonomie_base.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(): from autonomie_base.models.base 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(situations_ids, stages_ids): """ Migrate parcours's data from user_datas and related tables to career_path """ from autonomie.models.career_path import CareerPath session = DBSESSION() cnx = op.get_bind() userdatas = cnx.execute("SELECT \ id,\ parcours_contract_type,\ parcours_start_date,\ parcours_end_date,\ parcours_last_avenant,\ parcours_taux_horaire,\ parcours_taux_horaire_letters,\ parcours_num_hours,\ parcours_salary,\ parcours_salary_letters,\ parcours_employee_quality_id,\ sortie_date,\ sortie_motif_id,\ sortie_type_id \ FROM user_datas") for u in userdatas: # Diagnotic diagnotics = cnx.execute("SELECT date FROM date_diagnostic_datas WHERE date>'2000-01-01' AND userdatas_id=%s" % u.id) for diagnotic in diagnotics: session.add(CareerPath(userdatas_id=u.id, career_stage_id=stages_ids[0], start_date=diagnotic.date)) # CAPE capes = cnx.execute("SELECT date, end_date FROM date_convention_cape_datas WHERE date>'2000-01-01' AND userdatas_id=%s" % u.id) for cape in capes: session.add(CareerPath( userdatas_id=u.id, career_stage_id=stages_ids[1], start_date=cape.date, end_date=cape.end_date, cae_situation_id=situations_ids[0], stage_type="entry" )) # DPAE dpaes = cnx.execute("SELECT date FROM date_dpae_datas WHERE date>'2000-01-01' AND userdatas_id=%s" % u.id) for dpae in dpaes: session.add(CareerPath(userdatas_id=u.id, career_stage_id=stages_ids[2], start_date=dpae.date)) # Contrat if u.parcours_start_date and u.parcours_contract_type is not None: from autonomie.models.career_path import TypeContratOption cdi_type = session.query(TypeContratOption).filter( TypeContratOption.label==u.parcours_contract_type.upper() ).first() if cdi_type: cdi_type_id = cdi_type.id else: cdi_type_id = None session.add(CareerPath( userdatas_id=u.id, career_stage_id=stages_ids[3], start_date=u.parcours_start_date, end_date=u.parcours_end_date, cae_situation_id=situations_ids[1], stage_type="contrat", type_contrat_id=cdi_type_id, employee_quality_id=u.parcours_employee_quality_id, taux_horaire=u.parcours_taux_horaire, num_hours=u.parcours_num_hours )) # Avenant contrat if u.parcours_last_avenant: avenants = cnx.execute("SELECT date, number FROM contract_history WHERE date>'2000-01-01' AND userdatas_id=%s" % u.id) for avenant in avenants: model_avenant = CareerPath( userdatas_id=u.id, career_stage_id=stages_ids[4], start_date=avenant.date, stage_type="amendment", amendment_number=avenant.number ) if u.parcours_last_avenant==avenant.date: model_avenant.taux_horaire=u.parcours_taux_horaire model_avenant.num_hours=u.parcours_num_hours session.add(model_avenant) # Sortie if u.sortie_date: session.add(CareerPath( userdatas_id=u.id, career_stage_id=stages_ids[5], start_date=u.sortie_date, cae_situation_id=situations_ids[2], stage_type="exit", type_sortie_id=u.sortie_type_id, motif_sortie_id=u.sortie_motif_id )) # Historique des situations changes = cnx.execute("SELECT date, situation_id FROM cae_situation_change WHERE date>'2000-01-01' AND userdatas_id=%s" % u.id) for change in changes: session.add(CareerPath(userdatas_id=u.id, start_date=change.date, cae_situation_id=change.situation_id)) # Sauvegarde du parcours de l'utilisateur session.flush()
def upgrade(): from autonomie.models.activity import Attendance, Activity from autonomie_base.models.base 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_action(label, **kw): session = DBSESSION() a = ActivityAction(label=label, **kw) session.add(a) session.flush() return a
def upgrade(): # Ajout et modification de la structure de données existantes op.execute("alter table project modify archived BOOLEAN;") for name in ('ht', 'tva', 'ttc'): col = sa.Column(name, sa.Integer, default=0) op.add_column('task', col) for col in ( sa.Column("project_id", sa.Integer, sa.ForeignKey('project.id')), sa.Column("customer_id", sa.Integer, sa.ForeignKey('customer.id')), sa.Column("_number", sa.String(10)), sa.Column("sequence_number", sa.Integer), sa.Column("display_units", sa.Integer, default=0), sa.Column('expenses', sa.Integer, default=0), sa.Column('expenses_ht', sa.Integer, default=0), sa.Column('address', sa.Text, default=""), sa.Column('payment_conditions', sa.Text, default=""), sa.Column("official_number", sa.Integer, default=None), ): op.add_column("task", col) col = sa.Column("sortie_type_id", sa.Integer, sa.ForeignKey('type_sortie_option.id')) op.add_column("user_datas", col) op.execute("alter table user_datas modify parcours_num_hours float DEFAULT NULL") op.execute("alter table external_activity_datas modify hours float DEFAULT NULL") op.execute("alter table external_activity_datas modify brut_salary float DEFAULT NULL") col = sa.Column("cgv", sa.Text, default="") op.add_column("company", col) col = sa.Column('_acl', sa.Text) op.add_column("job", col) # Migration des donnees vers la nouvelle structure from alembic.context import get_bind conn = get_bind() from autonomie_base.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(): from autonomie_base.models.base 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 populate_default_datas(): """ Populate the database with missing default entries if needed """ from autonomie.models.user.userdatas import CaeSituationOption from autonomie.models.career_stage import CareerStage from autonomie.models.career_path import TypeContratOption session = DBSESSION() # Populate contract types if session.query(TypeContratOption).filter(TypeContratOption.label=="CDD").count() == 0: session.add(TypeContratOption(label=u"CDD", order=0)) if session.query(TypeContratOption).filter(TypeContratOption.label=="CDI").count() == 0: session.add(TypeContratOption(label=u"CDI", order=0)) if session.query(TypeContratOption).filter(TypeContratOption.label=="CESA").count() == 0: session.add(TypeContratOption(label=u"CESA", order=0)) session.flush() # Populate CAE situations situation_cand = session.query(CaeSituationOption).filter(CaeSituationOption.label=="Candidat").first() if situation_cand is None: situation_cand = CaeSituationOption(label=u"Candidat", order=0) session.add(situation_cand) situation_conv = session.query(CaeSituationOption).filter(CaeSituationOption.label=="En convention").first() if situation_conv is None: situation_conv = CaeSituationOption(label=u"En convention", is_integration=True) session.add(situation_conv) situation_es = session.query(CaeSituationOption).filter(CaeSituationOption.label=="Entrepreneur salarié").first() if situation_es is None: situation_es = CaeSituationOption(label=u"Entrepreneur salarié", is_integration=True) session.add(situation_es) situation_out = session.query(CaeSituationOption).filter(CaeSituationOption.label=="Sortie").first() if situation_out is None: situation_out = CaeSituationOption(label=u"Sortie") session.add(situation_out) session.flush() # Populate Career Stages if CareerStage.query().count() == 0: stage_diag = CareerStage(active=True, name="Diagnostic", cae_situation_id=None, stage_type=None, ) stage_cape = CareerStage(active=True, name="Contrat CAPE", cae_situation_id=situation_conv.id, stage_type="entry", ) stage_dpae = CareerStage(active=True, name="Contrat DPAE", cae_situation_id=None, stage_type=None, ) stage_cesa = CareerStage(active=True, name="Contrat CESA", cae_situation_id=situation_es.id, stage_type="contract", ) stage_avct = CareerStage(active=True, name="Avenant contrat", cae_situation_id=None, stage_type="amendment", ) stage_out = CareerStage(active=True, name="Sortie", cae_situation_id=situation_out.id, stage_type="exit", ) session.add(stage_diag) session.add(stage_cape) session.add(stage_dpae) session.add(stage_cesa) session.add(stage_avct) session.add(stage_out) session.flush() return ( situation_conv.id, situation_es.id, situation_out.id ), ( stage_diag.id, stage_cape.id, stage_dpae.id, stage_cesa.id, stage_avct.id, stage_out.id, )
def populate_default_datas(): """ Populate the database with missing default entries if needed """ from autonomie.models.user.userdatas import CaeSituationOption from autonomie.models.career_stage import CareerStage from autonomie.models.career_path import TypeContratOption session = DBSESSION() # Populate contract types if session.query(TypeContratOption).filter( TypeContratOption.label == "CDD").count() == 0: session.add(TypeContratOption(label=u"CDD", order=0)) if session.query(TypeContratOption).filter( TypeContratOption.label == "CDI").count() == 0: session.add(TypeContratOption(label=u"CDI", order=0)) if session.query(TypeContratOption).filter( TypeContratOption.label == "CESA").count() == 0: session.add(TypeContratOption(label=u"CESA", order=0)) session.flush() # Populate CAE situations situation_cand = session.query(CaeSituationOption).filter( CaeSituationOption.label == "Candidat").first() if situation_cand is None: situation_cand = CaeSituationOption(label=u"Candidat", order=0) session.add(situation_cand) situation_conv = session.query(CaeSituationOption).filter( CaeSituationOption.label == "En convention").first() if situation_conv is None: situation_conv = CaeSituationOption(label=u"En convention", is_integration=True) session.add(situation_conv) situation_es = session.query(CaeSituationOption).filter( CaeSituationOption.label == "Entrepreneur salarié").first() if situation_es is None: situation_es = CaeSituationOption(label=u"Entrepreneur salarié", is_integration=True) session.add(situation_es) situation_out = session.query(CaeSituationOption).filter( CaeSituationOption.label == "Sortie").first() if situation_out is None: situation_out = CaeSituationOption(label=u"Sortie") session.add(situation_out) session.flush() # Populate Career Stages if CareerStage.query().count() == 0: stage_diag = CareerStage( active=True, name="Diagnostic", cae_situation_id=None, stage_type=None, ) stage_cape = CareerStage( active=True, name="Contrat CAPE", cae_situation_id=situation_conv.id, stage_type="entry", ) stage_dpae = CareerStage( active=True, name="Contrat DPAE", cae_situation_id=None, stage_type=None, ) stage_cesa = CareerStage( active=True, name="Contrat CESA", cae_situation_id=situation_es.id, stage_type="contract", ) stage_avct = CareerStage( active=True, name="Avenant contrat", cae_situation_id=None, stage_type="amendment", ) stage_out = CareerStage( active=True, name="Sortie", cae_situation_id=situation_out.id, stage_type="exit", ) session.add(stage_diag) session.add(stage_cape) session.add(stage_dpae) session.add(stage_cesa) session.add(stage_avct) session.add(stage_out) session.flush() return (situation_conv.id, situation_es.id, situation_out.id), ( stage_diag.id, stage_cape.id, stage_dpae.id, stage_cesa.id, stage_avct.id, stage_out.id, )
def user_add_command(arguments, env): """ Add a user in the database """ logger = logging.getLogger(__name__) login = get_value(arguments, 'user', 'admin.majerti') login = login.decode('utf-8') logger.debug(u"Adding a user {0}".format(login)) password = get_value(arguments, 'pwd', get_pwd()) password = password.decode('utf-8') login = Login(login=login) login.set_password(password) group = get_value(arguments, 'group', None) if group: try: login.groups.append(group) except NoResultFound: print( u""" ERROR : group %s doesn't exist, did you launched the syncdb command : autonomie-admin <fichier.ini> syncdb """ % ( group, ) ) return db = DBSESSION() db.add(login) db.flush() firstname = get_value(arguments, 'firstname', 'Admin') lastname = get_value(arguments, 'lastname', 'Majerti') email = get_value(arguments, 'email', '*****@*****.**') user = User( login=login, firstname=firstname, lastname=lastname, email=email ) db.add(user) db.flush() print(u""" User Account created : ID : {0.id} Login : {0.login.login} Firstname : {0.firstname} Lastname : {0.lastname} Email : {0.email} Groups : {0.login.groups} """.format(user)) if 'pwd' not in arguments: print(u""" Password : {0}""".format(password) ) logger.debug(u"-> Done") return user