# -*- 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) @classmethod def by_id(cls, id): return DBSession.query(cls).filter(cls.uid == id).first() 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) area = relationship(TasksArea, backref=backref("tasks") ) @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.now) end_time = Column(DateTime, default=datetime.datetime.now) created = Column(DateTime, default=datetime.datetime.now) last_change = Column(DateTime, default=datetime.datetime.now) @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.now) last_logged = Column(DateTime, default=datetime.datetime.now) last_change = Column(DateTime, default=datetime.datetime.now) 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): 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.now) last_change = Column(DateTime, default=datetime.datetime.now) # 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.now) @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.now) last_change = Column(DateTime, default=datetime.datetime.now) @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.now) last_change = Column(DateTime, default=datetime.datetime.now) @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.now) last_change = Column(DateTime, default=datetime.datetime.now) # 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.now) end_time = Column(DateTime, default=datetime.datetime.now) # 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.now) last_change = Column(DateTime, default=datetime.datetime.now) # 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.now) last_change = Column(DateTime, default=datetime.datetime.now) 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.now) end_time = Column(DateTime, default=datetime.datetime.now) description = Column(UnicodeText) created = Column(DateTime, default=datetime.datetime.now) last_change = Column(DateTime, default=datetime.datetime.now) 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.now) edited = Column(DateTime, default=datetime.datetime.now) @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()