From fa4a9859c57de6b7894ff4b84b75d242f2b796f5 Mon Sep 17 00:00:00 2001 From: tr4ck3ur Date: Fri, 13 Feb 2015 02:29:37 +0100 Subject: first drop --- jm2l/models.py | 616 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 616 insertions(+) create mode 100644 jm2l/models.py (limited to 'jm2l/models.py') diff --git a/jm2l/models.py b/jm2l/models.py new file mode 100644 index 0000000..2938f3a --- /dev/null +++ b/jm2l/models.py @@ -0,0 +1,616 @@ +# -*- coding: utf8 -*- +import datetime +import sqlalchemy as sa +import hashlib +from pyramid.security import unauthenticated_userid +from sqlalchemy.orm import relationship, backref +from sqlalchemy import func +from sqlalchemy import or_ +from sqlalchemy import ( + Column, + Integer, + Text, + Unicode, + UnicodeText, + DateTime, + Enum, + Boolean, + ForeignKey + ) + +from slugify import slugify +from webhelpers.text import urlify +from webhelpers.paginate import PageURL_WebOb, Page +from webhelpers.date import time_ago_in_words +from collections import namedtuple + + +from sqlalchemy.ext.declarative import declarative_base + +from sqlalchemy.orm import ( + scoped_session, + sessionmaker, + relation + ) + +from zope.sqlalchemy import ZopeTransactionExtension + +DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) +Base = declarative_base() + +CurrentYear = 2015 + +class TasksArea(Base): + __tablename__ = 'staff_tasks_area' + uid = Column(Integer, primary_key=True) + name = Column(Unicode(80)) + description = Column(UnicodeText) + +class Tasks(Base): + __tablename__ = 'staff_tasks' + uid = Column(Integer, primary_key=True) + area_uid = Column(Integer, ForeignKey('staff_tasks_area.uid') ) + due_date = Column(DateTime, default=None) + closed_by = Column(Integer, ForeignKey('users.uid') ) + closed_date = Column(DateTime, default=None) + closed = Column(Integer, default=0) + name = Column(Unicode(80)) + description = Column(UnicodeText) + + @classmethod + def by_id(cls, id): + return DBSession.query(cls).filter(cls.uid == id).first() + +class User_Event(Base): + """ Créer le lien entre la personne et l' évenement en fonction de l'année""" + __tablename__ = 'user_event_link' + uid = Column(Integer, primary_key=True) + event_uid = Column(Integer, ForeignKey('events.uid') ) + #, primary_key=True) + # + user_uid = Column(Integer, ForeignKey('users.uid') ) + #, primary_key=True) + # + year_uid = Column(Integer, ForeignKey('jm2l_year.year_uid'), default=CurrentYear) + role = Column(Unicode(80)) + # Define some relation + #user = relationship('User', backref=backref("events_assoc") ) + #event = relationship('events', backref=backref("users_assoc") ) + +class JM2L_Year(Base): + __tablename__ = 'jm2l_year' + year_uid = Column(Integer, primary_key=True) + description = Column(UnicodeText) + doss_presse = Column(UnicodeText) + state = Column(Enum('Archived', 'Cancelled', 'Ongoing')) + start_time = Column(DateTime, default=datetime.datetime.utcnow) + end_time = Column(DateTime, default=datetime.datetime.utcnow) + created = Column(DateTime, default=datetime.datetime.utcnow) + last_change = Column(DateTime, default=datetime.datetime.utcnow) + + @property + def AvailableTimeSlots(self, TimeStep=30): + Available = self.end_time - self.start_time + NbMinutes = Available.total_seconds()/60 + NbSteps = NbMinutes/TimeStep + # Create the range of date each 30min + date_list = [self.start_time + datetime.timedelta(minutes=TimeStep*x) for x in range(0, int(NbSteps))] + # Remove out of range datetime + # Remove hours > 19h + date_list = filter(lambda x:x.hour < 19, date_list) + # Remove hours < 10h + date_list = filter(lambda x:x.hour >= 10, date_list) + # Remove 12h < hours < 13h + date_list = filter(lambda x: x.hour<12 or x.hour>=13, date_list) + return date_list + +class User(Base): + __tablename__ = 'users' + uid = Column(Integer, primary_key=True) + user_id = Column(Integer) + nom = Column(Unicode(80)) + prenom = Column(Unicode(80)) + pseudo = Column(Unicode(80)) + slug = Column(Unicode(164)) + mail = Column(Unicode(100)) + password = Column(Unicode(100), nullable=False) + fonction = Column(Unicode(80)) + website = Column(Unicode(100)) + phone = Column(Unicode(10)) + created = Column(DateTime, default=datetime.datetime.utcnow) + last_logged = Column(DateTime, default=datetime.datetime.utcnow) + last_change = Column(DateTime, default=datetime.datetime.utcnow) + active = Column(Integer, default=1) + bio = Column(UnicodeText) + gpg_key = Column(UnicodeText) + soc_link = Column(UnicodeText) + Staff = Column(Integer, default=0) + # relations + tiers = relationship('Tiers', secondary='user_tiers_link' ) + events = relationship('Event', secondary='user_event_link' ) + tiersship = relationship('User_Tiers', backref="matching_users") + + @classmethod + def by_id(cls, id): + return DBSession.query(cls).filter(cls.uid == id).first() + + @classmethod + def by_slug(cls, slug): + return DBSession.query(cls).filter(cls.slug == slug).first() + + @classmethod + def by_user_id(cls, user_id): + return DBSession.query(cls).filter(cls.user_id == user_id).first() + + @classmethod + def by_name(cls, name): + return DBSession.query(cls).filter(cls.nom == name).first() + + @classmethod + def by_hash(cls, tsthash): + for u in DBSession.query(cls): + print u.nom, u.my_hash + if u.my_hash==tsthash: + return u + return None + + @property + def my_hash(self): + m = hashlib.sha1() + m.update("Nobody inspects ") + if self.nom: + m.update(unicode.encode(self.nom,'utf8')) + if self.pseudo: + m.update(unicode.encode(self.pseudo,'utf8')) + if self.prenom: + m.update(unicode.encode(self.prenom,'utf8')) + m.update(" the spammish repetition") + return m.hexdigest() + + @property + def Photos(self): + return DBSession.query(Media.filename) \ + .filter(Media.media_table=='users') \ + .filter(Media.media_type=='Image') \ + .filter(Media.link_id == self.user_id).all() + + @property + def PhotosLinks(self): + from .upload import MediaPath + return MediaPath().get_list('users', self.uid) + + @property + def PhotosThumb(self): + from .upload import MediaPath + return MediaPath().get_thumb('users', self.uid) + + def verify_password(self, password): + return self.password == password + +class TiersOpt(Base): + __tablename__ = 'tiers_opt' + uid = Column(Integer, primary_key=True) + entity_type = Column(Unicode(80), nullable=False) + entity_subtype = Column(Unicode(80)) + entity_role = Column(Unicode(80)) + + @property + def slug_entity_type(self): + return slugify(self.entity_type) + + @property + def slug_entity_subtype(self): + return slugify(self.entity_subtype) + + @classmethod + def get_entity_type(cls): + return DBSession.query(cls, func.count(Tiers.ent_type).label('count'))\ + .outerjoin(Tiers)\ + .group_by(cls.entity_type).all() + + @classmethod + def get_entity_sub_type(cls, entity_type): + return DBSession.query(cls, func.count(Tiers.ent_type).label('count'))\ + .outerjoin(Tiers)\ + .filter(cls.entity_type == entity_type)\ + .group_by(cls.entity_subtype).all() + + @classmethod + def by_id(cls, id): + return DBSession.query(cls).filter(cls.uid == id).first() + +class Tiers(Base): + __tablename__ = 'tiers' + uid = Column(Integer, primary_key=True) + tiers_id = Column(Integer) + name = Column(Unicode(100), nullable=False) + slug = Column(Unicode(100)) + description = Column(UnicodeText) + website = Column(Unicode(100)) + tiers_type = Column(Integer, ForeignKey('tiers_opt.uid'), default=1) + created = Column(DateTime, default=datetime.datetime.utcnow) + last_change = Column(DateTime, default=datetime.datetime.utcnow) + # relations + ent_type = relationship('TiersOpt') + #members = relationship('User', secondary='user_tiers_link' ) + members = relationship(User, + secondary='user_tiers_link', + backref=backref('associate', uselist=False), + lazy='dynamic') + creator_id = Column(Integer) + membership = relationship('User_Tiers', backref="matching_tiers") + roles = relationship('Tiers', secondary='role_tiers_link' ) + + @classmethod + def by_id(cls, id): + return DBSession.query(cls).filter(cls.uid == id).first() + + @classmethod + def by_slug(cls, slug): + return DBSession.query(cls).filter(cls.slug == slug).first() + + @property + def get_entity_type(self): + return DBSession.query(TiersOpt)\ + .filter(TiersOpt.uid == self.tiers_type).first() + + @property + def logo(self): + return DBSession.query(Media) \ + .filter(Media.media_table == 'tiers') \ + .filter(Media.media_type == 'Image') \ + .filter(Media.link_id == self.uid) + + @property + def PhotosLinks(self): + from .upload import MediaPath + return MediaPath().get_list('tiers', self.uid) + + @property + def ThumbLinks(self): + from .upload import MediaPath + return MediaPath().get_thumb('tiers', self.uid) + +class Role_Tiers(Base): + """ Créer le lien entre le tiers et son rôle dans l'évenement en fonction de l'année""" + __tablename__ = 'role_tiers_link' + uid_role = Column(Integer, primary_key=True) + year_uid = Column(Integer, ForeignKey('jm2l_year.year_uid'), default=CurrentYear) + tiers_uid = Column(Integer, ForeignKey('tiers.uid')) + tiers = relationship(Tiers, backref=backref("roles_assoc") ) + tiers_role = Column(Enum('Exposant', 'Sponsor', 'Donateur')) + +class User_Tiers(Base): + """ Créer le lien entre la personne et le tiers en fonction de l'année""" + __tablename__ = 'user_tiers_link' + uid_tiers = Column(Integer, primary_key=True) + year_uid = Column(Integer, ForeignKey('jm2l_year.year_uid'), default=CurrentYear) + tiers_uid = Column(Integer, ForeignKey('tiers.uid')) + tiers = relationship(Tiers, backref=backref("users_assoc") ) + user_uid = Column(Integer, ForeignKey('users.uid')) + user = relationship(User, backref=backref("tiers_assoc") ) + role = Column(Unicode(80)) + +class Media(Base): + __tablename__ = 'medias' + media_id = Column(Integer, primary_key=True) + for_year = Column(Integer, ForeignKey('jm2l_year.year_uid')) + media_table = Column(Enum('users', 'tiers', 'place', 'salle', 'RIB', 'Justif', 'event' )) + media_type = Column(Enum('Image', 'Video', 'Pres', 'Document')) + link_id = Column(Integer) + mime_type = Column(Unicode(20)) + size = Column(Integer) + width = Column(Integer) + height = Column(Integer) + length = Column(Integer) + filename = Column(UnicodeText) + created = Column(DateTime, default=datetime.datetime.utcnow) + + @property + def get_path(self): + return '/upload/%s/%s/%s' % (self.media_type, self.media_table, self.filename) + +class SallePhy(Base): + """ Représente une salle dans les locaux """ + __tablename__ = 'phy_salle' + uid = Column(Integer, primary_key=True) + name = Column(Unicode(40)) # Numéro de salle vu de polytech + slug = Column(Unicode(40)) + description = Column(UnicodeText) # Description du matériel disponible + nb_places = Column(Integer, default=0) # Nombre de places assises + + @classmethod + def by_id(cls, uid): + return DBSession.query(cls).filter(cls.uid == uid).first() + +class Salles(Base): + __tablename__ = 'salle' + salle_id = Column(Integer, primary_key=True) + phy_salle_id = Column(Integer, ForeignKey('phy_salle.uid')) + year_uid = Column(Integer, ForeignKey('jm2l_year.year_uid'), default=CurrentYear) + name = Column(Unicode(40)) + place_type = Column(Enum('Conference', 'Stand', 'Ateliers', 'Autres')) + description = Column(UnicodeText) # Description du matériel disponible + created = Column(DateTime, default=datetime.datetime.utcnow) + last_change = Column(DateTime, default=datetime.datetime.utcnow) + + @classmethod + def by_id(cls, uid): + return DBSession.query(cls).filter(cls.salle_id == uid).first() + +class Place(Base): + __tablename__ = 'place' + place_id = Column(Integer, primary_key=True) + usage = Column(Boolean, default=False) # By Default / Extended + place_type = Column(Enum('Aeroport', 'Gare', 'JM2L', \ + 'Hotel', 'Habitant', 'Restaurant', 'Autres')) + display_name = Column(Unicode(20)) + name = Column(Unicode(80)) + slug = Column(Unicode(80)) + specific = Column(Unicode(80)) # eg Terminal 2 + gps_coord = Column(Unicode(30)) + adresse = Column(Unicode(100)) + codePostal = Column(Unicode(5)) + ville = Column(Unicode(40)) + website = Column(Unicode(100)) + description = Column(UnicodeText) + created_by = Column(Integer, ForeignKey('users.user_id')) + created = Column(DateTime, default=datetime.datetime.utcnow) + last_change = Column(DateTime, default=datetime.datetime.utcnow) + + @classmethod + def by_id(cls, uid): + return DBSession.query(cls).filter(cls.place_id == uid).first() + + @classmethod + def get_list(cls, All=False): + if All: + return DBSession.query(cls).all() + else: + return DBSession.query(cls).filter(cls.usage==True).all() + +class Itineraire(Base): + __tablename__ = 'itineraire' + itin_id = Column(Integer, primary_key=True) + start_place = Column(Integer, ForeignKey('place.place_id')) # Place link + arrival_place = Column(Integer, ForeignKey('place.place_id')) # Place link + distance = Column(Integer) + duration = Column(Integer) + price = Column(Integer) + tr_pied = Column(Boolean, default=False) + tr_velo = Column(Boolean, default=False) + tr_moto = Column(Boolean, default=False) + tr_voiture = Column(Boolean, default=False) + tr_bus = Column(Boolean, default=False) + tr_taxi = Column(Boolean, default=False) + tr_avion = Column(Boolean, default=False) + description = Column(UnicodeText) + created_by = Column(Integer, ForeignKey('users.user_id')) # User link + created = Column(DateTime, default=datetime.datetime.utcnow) + last_change = Column(DateTime, default=datetime.datetime.utcnow) + # relations + start = relationship(Place, foreign_keys=[start_place]) + arrival = relationship(Place, foreign_keys=[arrival_place]) + +class Exchange_Cat(Base): + __tablename__ = 'exchange_category' + cat_id = Column(Integer, primary_key=True) + exch_type = Column(Enum('H', 'C', 'M')) # Heberg, Co-voit, Materiel + exch_subtype = Column(Unicode(80)) + description = Column(UnicodeText) + +class Exchange(Base): + __tablename__ = 'exchanges' + exch_id = Column(Integer, primary_key=True) + for_year = Column(Integer, ForeignKey('jm2l_year.year_uid')) # link JM2L_Year + exch_done = Column(Boolean, default=False) + exch_state = Column(Enum('Ask', 'Proposal')) + exch_type = Column(Enum('H', 'C', 'M')) # Heberg, Co-Voit, Materiel + exch_categ = Column(Integer, ForeignKey('exchange_category.cat_id')) # Exchange_Cat link + # Users + asker_id = Column(Integer, ForeignKey('users.uid')) # User link + provider_id = Column(Integer, ForeignKey('users.uid')) # User link + start_time = Column(DateTime, default=datetime.datetime.utcnow) + end_time = Column(DateTime, default=datetime.datetime.utcnow) + # Co-voiturage + itin_id = Column(Integer, ForeignKey('itineraire.itin_id')) # Itineraire link + # Hebergement + place_id = Column(Integer, ForeignKey('place.place_id')) # Place link + # Materiel + duration = Column(Integer) + description = Column(UnicodeText) + pictures = Column(Unicode(80)) + created_by = Column(Integer) # User link + created = Column(DateTime, default=datetime.datetime.utcnow) + last_change = Column(DateTime, default=datetime.datetime.utcnow) + # relations + Category = relationship(Exchange_Cat, backref="exchanges") + Itin = relationship(Itineraire, backref="exchanged") + asker = relationship(User, foreign_keys=[asker_id], backref="asked") + provider = relationship(User, foreign_keys=[provider_id], backref="provided") + + @classmethod + def by_id(cls, id): + return DBSession.query(cls).filter(cls.exch_id == id).first() + + @classmethod + def get_counters(cls): + return DBSession.query(cls.exch_state, cls.exch_type, cls.exch_done, func.count(cls.exch_id))\ + .filter(cls.for_year==2015)\ + .group_by(cls.exch_state, cls.exch_type, cls.exch_done) + + @classmethod + def get_my_counters(cls, uid): + return DBSession.query(cls.exch_state, cls.exch_type, cls.exch_done, func.count(cls.exch_id))\ + .filter(cls.for_year==2015)\ + .filter( or_(cls.asker_id==uid, cls.provider_id==uid) )\ + .group_by(cls.exch_state, cls.exch_type, cls.exch_done) + + @classmethod + def get_overview(cls, uid): + # Build a Dic with all exchange to save database access + DicResult= {} + for extype in ['F','C','H','M']: + DicResult[extype] = {} + for exstate in ['Ask','Proposal','Missing','Agree']: + DicResult[extype][exstate]=[] + DicResult[extype]['Counters']={'AllAsk':0, 'AllProp':0, 'AllAgree':0} + Query = DBSession.query(cls)\ + .filter(cls.for_year==2015)\ + .order_by(cls.start_time).all() + for item in Query: + if item.exch_done: + DicResult[item.exch_type]['Counters']['AllAgree']+=1 + if item.exch_state=='Ask': + DicResult[item.exch_type]['Counters']['AllAsk']+=1 + if item.exch_state=='Proposal': + DicResult[item.exch_type]['Counters']['AllProp']+=1 + if item.asker_id==uid or item.provider_id==uid: + if item.asker_id==uid and item.exch_state=='Ask': + DicResult[item.exch_type]['Ask'].append(item) + if item.provider_id==uid and item.exch_state=='Ask': + DicResult[item.exch_type]['Proposal'].append(item) + if item.asker_id==uid and item.exch_state=='Proposal': + DicResult[item.exch_type]['Ask'].append(item) + if item.provider_id==uid and item.exch_state=='Proposal': + DicResult[item.exch_type]['Proposal'].append(item) + if item.exch_done: + DicResult[item.exch_type]['Agree'].append(item) + else: + DicResult[item.exch_type]['Missing'].append(item) + + return DicResult + + @classmethod + def get_pub_list(cls, exch_type): + return DBSession.query(cls).filter(cls.for_year==2015 and exch_state in ['Ask','Proposal'])\ + .filter(cls.exch_type=='%s' % exch_type)\ + .filter(cls.exch_done==False)\ + .all() + + @classmethod + def get_my_list(cls, uid, exch_type): + DicResult = {} + DicResult['Ask']=DBSession.query(cls)\ + .filter(cls.for_year==2015)\ + .filter( or_(cls.asker_id==uid, cls.provider_id==uid) )\ + .filter(cls.exch_type=='%s' % exch_type)\ + .filter(cls.exch_state=='Ask')\ + .order_by(cls.start_time).all() + DicResult['Proposal']=DBSession.query(cls)\ + .filter(cls.for_year==2015)\ + .filter( or_(cls.asker_id==uid, cls.provider_id==uid) )\ + .filter(cls.exch_type=='%s' % exch_type)\ + .filter(cls.exch_state=='Proposal')\ + .order_by(cls.start_time).all() + return DicResult + +class Sejour(Base): + __tablename__ = 'sejour' + sej_id = Column(Integer, primary_key=True) + user_id = Column(Integer, ForeignKey('users.user_id')) # User link + for_year = Column(Integer, ForeignKey('jm2l_year.year_uid')) # link JM2L_Year + arrival_time = Column(DateTime) + arrival_place = Column(Integer, ForeignKey('place.place_id')) # Place link + depart_time = Column(DateTime) + depart_place = Column(Integer, ForeignKey('place.place_id')) # Place link + created = Column(DateTime, default=datetime.datetime.utcnow) + last_change = Column(DateTime, default=datetime.datetime.utcnow) + +class Event(Base): + __tablename__ = 'events' + uid = Column(Integer, primary_key=True) + salle_uid = Column(Integer, ForeignKey('salle.salle_id')) + event_uid = Column(Integer) + for_year = Column(Integer, ForeignKey('jm2l_year.year_uid')) # link JM2L_Year + name = Column(Unicode(100), nullable=False) + slug = Column(Unicode(100)) + event_type = Column(Enum('Stand', 'Table ronde', 'Atelier', 'Concert', 'Conference', 'Repas')) + start_time = Column(DateTime, default=datetime.datetime.utcnow) + end_time = Column(DateTime, default=datetime.datetime.utcnow) + description = Column(UnicodeText) + created = Column(DateTime, default=datetime.datetime.utcnow) + last_change = Column(DateTime, default=datetime.datetime.utcnow) + intervenants = relationship(User, + secondary='user_event_link', + backref=backref('participate', uselist=False), + lazy='dynamic') + interventions = relationship(User_Event, backref="matching_events") + Salle = relationship(Salles, backref='allevents') + + @classmethod + def by_id(cls, uid): + return DBSession.query(cls)\ + .filter(cls.uid == uid).first() + + + @classmethod + def by_slug(cls, slug, year=None): + if not year is None: + return DBSession.query(cls)\ + .filter(cls.for_year==year)\ + .filter(cls.slug == slug).first() + else: + return DBSession.query(cls)\ + .filter(cls.slug == slug).first() + + @property + def video(self): + return DBSession.query(Media) \ + .filter(Media.media_table == 'event') \ + .filter(Media.media_type == 'Video') \ + .filter(Media.link_id == self.uid) + + @property + def presentation(self): + return DBSession.query(Media) \ + .filter(Media.media_table == 'event') \ + .filter(Media.media_type == 'Pres') \ + .filter(Media.link_id == self.uid) + + @property + def created_in_words(self): + return time_ago_in_words(self.created) + +class Entry(Base): + __tablename__ = 'entries' + id = Column(Integer, primary_key=True) + active = Column(Integer, default=True) + title = Column(Unicode(255), unique=True, nullable=False) + body = Column(UnicodeText, default=u'') + created = Column(DateTime, default=datetime.datetime.utcnow) + edited = Column(DateTime, default=datetime.datetime.utcnow) + + @classmethod + def all(cls): + return DBSession.query(Entry).order_by(sa.desc(Entry.created)) + + @classmethod + def by_id(cls, id): + return DBSession.query(Entry).filter(Entry.id == id).first() + + @property + def slug(self): + return urlify(self.title) + + @property + def created_in_words(self): + return time_ago_in_words(self.created) + + @classmethod + def get_paginator(cls, request, page=1): + page_url = PageURL_WebOb(request) + return Page(Entry.all(), page, url=page_url, items_per_page=5) + +#class Seances(Base): +# __tablename__ = 'seances' +def get_user(request): + # the below line is just an example, use your own method of + # accessing a database connection here (this could even be another + # request property such as request.db, implemented using this same + # pattern). + userid = unauthenticated_userid(request) + if userid is not None: + # this should return None if the user doesn't exist + # in the database + return DBSession.query(User).filter(User.uid==userid).first() -- cgit v1.2.3-54-g00ecf