Le repo des sources pour le site web des JM2L
Du kannst nicht mehr als 25 Themen auswählen Themen müssen entweder mit einem Buchstaben oder einer Ziffer beginnen. Sie können Bindestriche („-“) enthalten und bis zu 35 Zeichen lang sein.
 
 
 
 
 

772 Zeilen
29 KiB

  1. # -*- coding: utf8 -*-
  2. import datetime
  3. import sqlalchemy as sa
  4. import hashlib
  5. from pyramid.security import unauthenticated_userid
  6. from sqlalchemy.orm import relationship, backref
  7. from sqlalchemy import func
  8. from sqlalchemy import or_
  9. from sqlalchemy import (
  10. Column,
  11. Integer,
  12. Unicode,
  13. UnicodeText,
  14. DateTime,
  15. Enum,
  16. Boolean,
  17. ForeignKey
  18. )
  19. from slugify import slugify
  20. # from webhelpers.text import urlify
  21. from paginate import Page # PageURL_WebOb
  22. from webhelpers2.date import time_ago_in_words
  23. from sqlalchemy.ext.declarative import declarative_base
  24. from sqlalchemy.orm import (
  25. scoped_session,
  26. sessionmaker
  27. )
  28. # from zope.sqlalchemy import ZopeTransactionExtension
  29. from zope.sqlalchemy import register
  30. from jm2l.const import CurrentYear
  31. from passlib.hash import argon2
  32. def urlify(in_string, in_string_length):
  33. return ''.join('%20' if c == ' ' else c for c in in_string[:in_string_length])
  34. DBSession = scoped_session(sessionmaker(autoflush=False))
  35. register(DBSession)
  36. # DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
  37. Base = declarative_base()
  38. class TasksArea(Base):
  39. __tablename__ = 'staff_tasks_area'
  40. uid = Column(Integer, primary_key=True)
  41. year_uid = Column(Integer, ForeignKey('jm2l_year.year_uid'), default=CurrentYear)
  42. name = Column(Unicode(80))
  43. description = Column(UnicodeText)
  44. @classmethod
  45. def by_id(cls, uid):
  46. return DBSession.query(cls).filter(cls.uid == uid).first()
  47. class Tasks(Base):
  48. __tablename__ = 'staff_tasks'
  49. uid = Column(Integer, primary_key=True)
  50. area_uid = Column(Integer, ForeignKey('staff_tasks_area.uid'))
  51. year_uid = Column(Integer, ForeignKey('jm2l_year.year_uid'), default=CurrentYear)
  52. due_date = Column(DateTime, default=None)
  53. closed_by = Column(Integer, ForeignKey('users.uid'))
  54. closed_date = Column(DateTime, default=None)
  55. closed = Column(Integer, default=0)
  56. name = Column(Unicode(80))
  57. description = Column(UnicodeText)
  58. area = relationship(TasksArea, backref=backref("tasks"))
  59. assignee = relationship('User', backref=backref("task_assoc"))
  60. @classmethod
  61. def by_id(cls, uid):
  62. return DBSession.query(cls).filter(cls.uid == uid).first()
  63. class User_Event(Base):
  64. """ Créer le lien entre la personne et l' évenement en fonction de l'année"""
  65. __tablename__ = 'user_event_link'
  66. uid = Column(Integer, primary_key=True)
  67. event_uid = Column(Integer, ForeignKey('events.uid'))
  68. # , primary_key=True)
  69. #
  70. user_uid = Column(Integer, ForeignKey('users.uid'))
  71. # , primary_key=True)
  72. #
  73. year_uid = Column(Integer, ForeignKey('jm2l_year.year_uid'), default=CurrentYear)
  74. role = Column(Unicode(80))
  75. # Define some relation
  76. # user = relationship('User', backref=backref("events_assoc") )
  77. # event = relationship('events', backref=backref("users_assoc") )
  78. class JM2L_Year(Base):
  79. __tablename__ = 'jm2l_year'
  80. year_uid = Column(Integer, primary_key=True)
  81. description = Column(UnicodeText)
  82. doss_presse = Column(UnicodeText)
  83. state = Column(Enum('Archived', 'Cancelled', 'Ongoing'))
  84. start_time = Column(DateTime, default=datetime.datetime.now)
  85. end_time = Column(DateTime, default=datetime.datetime.now)
  86. created = Column(DateTime, default=datetime.datetime.now)
  87. last_change = Column(DateTime, default=datetime.datetime.now)
  88. @classmethod
  89. def get_latest_jm2l_startdate(cls, fakeparam=None):
  90. """ This property will return latest JM2L planned event startdate from database """
  91. last_record = DBSession.query(JM2L_Year) \
  92. .filter(JM2L_Year.start_time ==
  93. DBSession.query(func.max(JM2L_Year.start_time))
  94. ).one()
  95. return last_record.start_time.strftime("%A %d %B %Y") # .decode('utf-8')
  96. @property
  97. def AvailableTimeSlots(self, TimeStep=30):
  98. available = self.end_time - self.start_time
  99. nb_minutes = available.total_seconds() / 60
  100. nb_steps = nb_minutes / TimeStep
  101. # Create the range of date each 30min
  102. date_list = [self.start_time + datetime.timedelta(minutes=TimeStep * x) for x in range(0, int(nb_steps))]
  103. # Remove out of range datetime
  104. # Remove hours > 19h
  105. date_list = filter(lambda x: x.hour < 19, date_list)
  106. # Remove hours < 10h
  107. date_list = filter(lambda x: x.hour >= 10, date_list)
  108. # Remove 12h < hours < 13h
  109. date_list = filter(lambda x: x.hour < 12 or x.hour >= 13, date_list)
  110. return list(date_list)
  111. @property
  112. def DocLinks(self):
  113. from .upload import MediaPath
  114. return sorted(MediaPath().get_all('presse', self.year_uid))
  115. class User(Base):
  116. __tablename__ = 'users'
  117. uid = Column(Integer, primary_key=True)
  118. user_id = Column(Integer)
  119. nom = Column(Unicode(80))
  120. prenom = Column(Unicode(80))
  121. pseudo = Column(Unicode(80))
  122. slug = Column(Unicode(164))
  123. mail = Column(Unicode(100))
  124. password = Column(Unicode(100), nullable=False)
  125. fonction = Column(Unicode(80))
  126. website = Column(Unicode(100))
  127. phone = Column(Unicode(10))
  128. created = Column(DateTime, default=datetime.datetime.now)
  129. last_logged = Column(DateTime, default=datetime.datetime.now)
  130. last_change = Column(DateTime, default=datetime.datetime.now)
  131. active = Column(Integer, default=1)
  132. bio = Column(UnicodeText)
  133. gpg_key = Column(UnicodeText)
  134. soc_link = Column(UnicodeText)
  135. Staff = Column(Integer, default=0)
  136. vote_logo = Column(Integer, default=0)
  137. wifi_user = Column(Unicode(80), nullable=True)
  138. wifi_pass = Column(Unicode(80), nullable=True)
  139. # relations
  140. tiers = relationship('Tiers', secondary='user_tiers_link')
  141. events = relationship('Event', secondary='user_event_link')
  142. tiersship = relationship('User_Tiers', backref="matching_users")
  143. @classmethod
  144. def by_id(cls, uid):
  145. return DBSession.query(cls).filter(cls.uid == uid).first()
  146. @classmethod
  147. def by_mail(cls, mail):
  148. return DBSession.query(cls).filter(cls.mail == mail).first()
  149. @classmethod
  150. def by_slug(cls, slug):
  151. return DBSession.query(cls).filter(cls.slug == slug).first()
  152. @classmethod
  153. def by_user_id(cls, user_id):
  154. return DBSession.query(cls).filter(cls.user_id == user_id).first()
  155. @classmethod
  156. def by_name(cls, name):
  157. return DBSession.query(cls).filter(cls.nom == name).first()
  158. @classmethod
  159. def by_hash(cls, tsthash):
  160. for u in DBSession.query(cls):
  161. if u.my_hash == tsthash:
  162. return u
  163. return None
  164. @property
  165. def is_Intervenant(self):
  166. """ This property will return if User do an event on specified year """
  167. return DBSession.query(Event).join(User_Event) \
  168. .filter(User_Event.user_uid == self.uid) \
  169. .filter(Event.for_year == CurrentYear).count()
  170. def is_IntervenantOnYear(self, year=CurrentYear):
  171. """ This property will return if User do an event on specified year """
  172. return DBSession.query(Event).join(User_Event) \
  173. .filter(User_Event.user_uid == self.uid) \
  174. .filter(Event.for_year == year).count()
  175. @property
  176. def is_crew(self, year=CurrentYear):
  177. """ This property will return if User subscribe orga task on specified year """
  178. orga_checked = DBSession.query(User, Sejour.orga_part) \
  179. .outerjoin(Sejour) \
  180. .filter(Sejour.for_year == year) \
  181. .filter(User.uid == self.uid).first()
  182. if orga_checked:
  183. return orga_checked
  184. else:
  185. return False
  186. def year_events(self, EventType='All', year=CurrentYear):
  187. if EventType == 'All':
  188. return list(filter(lambda e: e.for_year == year, self.events))
  189. else:
  190. return list(filter(lambda e: e.for_year == year and e.event_type == EventType, self.events))
  191. @property
  192. def my_hash(self):
  193. m = hashlib.sha1()
  194. m.update("Nobody inspects ".encode('utf-8'))
  195. if self.nom:
  196. m.update(self.nom.encode('utf-8')) # unicode.encode(self.nom, 'utf8'))
  197. if self.pseudo:
  198. m.update(self.pseudo.encode('utf-8')) # unicode.encode(self.pseudo, 'utf8'))
  199. if self.prenom:
  200. m.update(self.prenom.encode('utf-8')) # unicode.encode(self.prenom, 'utf8'))
  201. m.update(" the spammish repetition".encode('utf-8'))
  202. return m.hexdigest()
  203. @property
  204. def Photos(self):
  205. return DBSession.query(Media.filename) \
  206. .filter(Media.media_table == 'users') \
  207. .filter(Media.media_type == 'Image') \
  208. .filter(Media.link_id == self.user_id).all()
  209. @property
  210. def PhotosLinks(self):
  211. from .upload import MediaPath
  212. return MediaPath().get_list('users', self.uid)
  213. @property
  214. def PhotosThumb(self):
  215. from .upload import MediaPath
  216. return MediaPath().get_thumb('users', self.uid)
  217. def verify_password(self, password):
  218. if not argon2.identify(self.password): # Update existing clear-text password
  219. self.password = argon2.using(rounds=4).hash(self.password)
  220. DBSession.merge(self)
  221. return argon2.verify(password, self.password)
  222. class TiersOpt(Base):
  223. __tablename__ = 'tiers_opt'
  224. uid = Column(Integer, primary_key=True)
  225. entity_type = Column(Unicode(80), nullable=False)
  226. entity_subtype = Column(Unicode(80))
  227. entity_role = Column(Unicode(80))
  228. @property
  229. def slug_entity_type(self):
  230. return slugify(self.entity_type)
  231. @property
  232. def slug_entity_subtype(self):
  233. return slugify(self.entity_subtype)
  234. @classmethod
  235. def get_entity_type(cls):
  236. return DBSession.query(cls, func.count(Tiers.ent_type).label('count')) \
  237. .outerjoin(Tiers) \
  238. .group_by(cls.entity_type).all()
  239. @classmethod
  240. def get_entity_sub_type(cls, entity_type):
  241. return DBSession.query(cls, func.count(Tiers.ent_type).label('count')) \
  242. .outerjoin(Tiers) \
  243. .filter(cls.entity_type == entity_type) \
  244. .group_by(cls.entity_subtype).all()
  245. @classmethod
  246. def by_id(cls, uid):
  247. return DBSession.query(cls).filter(cls.uid == uid).first()
  248. class Tiers(Base):
  249. __tablename__ = 'tiers'
  250. uid = Column(Integer, primary_key=True)
  251. tiers_id = Column(Integer)
  252. name = Column(Unicode(100), nullable=False)
  253. slug = Column(Unicode(100))
  254. description = Column(UnicodeText)
  255. website = Column(Unicode(100))
  256. tiers_type = Column(Integer, ForeignKey('tiers_opt.uid'), default=1)
  257. created = Column(DateTime, default=datetime.datetime.now)
  258. last_change = Column(DateTime, default=datetime.datetime.now)
  259. # relations
  260. ent_type = relationship('TiersOpt')
  261. # members = relationship('User', secondary='user_tiers_link' )
  262. members = relationship(User,
  263. secondary='user_tiers_link',
  264. backref=backref('associate', uselist=False),
  265. lazy='dynamic')
  266. creator_id = Column(Integer)
  267. membership = relationship('User_Tiers', backref="matching_tiers")
  268. roles = relationship('Role_Tiers', backref="roles_tiers") # secondary='role_tiers_link' )
  269. @classmethod
  270. def by_id(cls, uid):
  271. return DBSession.query(cls).filter(cls.uid == uid).first()
  272. @classmethod
  273. def by_slug(cls, slug):
  274. return DBSession.query(cls).filter(cls.slug == slug).first()
  275. @property
  276. def get_entity_type(self):
  277. return DBSession.query(TiersOpt) \
  278. .filter(TiersOpt.uid == self.tiers_type).first()
  279. @property
  280. def logo(self):
  281. return DBSession.query(Media) \
  282. .filter(Media.media_table == 'tiers') \
  283. .filter(Media.media_type == 'Image') \
  284. .filter(Media.link_id == self.uid)
  285. @property
  286. def DocLinks(self):
  287. from .upload import MediaPath
  288. return zip(sorted(MediaPath().get_list('tiers', self.uid, 'Other')),
  289. sorted(MediaPath().get_thumb('tiers', self.uid, 'Other'))
  290. )
  291. @property
  292. def PhotosLinks(self):
  293. from .upload import MediaPath
  294. return MediaPath().get_list('tiers', self.uid, 'Image')
  295. @property
  296. def ThumbLinks(self):
  297. from .upload import MediaPath
  298. return MediaPath().get_thumb('tiers', self.uid)
  299. class Role_Tiers(Base):
  300. """ Créer le lien entre le tiers et son rôle dans l'évenement en fonction de l'année"""
  301. __tablename__ = 'role_tiers_link'
  302. uid_role = Column(Integer, primary_key=True)
  303. year_uid = Column(Integer, ForeignKey('jm2l_year.year_uid'), default=CurrentYear)
  304. tiers_uid = Column(Integer, ForeignKey('tiers.uid'))
  305. tiers = relationship(Tiers, backref=backref("roles_assoc"))
  306. tiers_role = Column(Enum('Exposant', 'Sponsor', 'Donateur'))
  307. event_uid = Column(Integer, default=None) # Optionnal link with Event
  308. class User_Tiers(Base):
  309. """ Créer le lien entre la personne et le tiers en fonction de l'année"""
  310. __tablename__ = 'user_tiers_link'
  311. uid_tiers = Column(Integer, primary_key=True)
  312. year_uid = Column(Integer, ForeignKey('jm2l_year.year_uid'), default=CurrentYear)
  313. tiers_uid = Column(Integer, ForeignKey('tiers.uid'))
  314. tiers = relationship(Tiers, backref=backref("users_assoc"))
  315. user_uid = Column(Integer, ForeignKey('users.uid'))
  316. user = relationship(User, backref=backref("tiers_assoc"))
  317. role = Column(Unicode(80))
  318. class Media(Base):
  319. __tablename__ = 'medias'
  320. media_id = Column(Integer, primary_key=True)
  321. for_year = Column(Integer, ForeignKey('jm2l_year.year_uid'))
  322. media_table = Column(Enum('users', 'tiers', 'place', 'salle', 'RIB', 'Justif', 'event'))
  323. media_type = Column(Enum('Image', 'Video', 'Pres', 'Document'))
  324. link_id = Column(Integer)
  325. mime_type = Column(Unicode(20))
  326. size = Column(Integer)
  327. width = Column(Integer)
  328. height = Column(Integer)
  329. length = Column(Integer)
  330. filename = Column(UnicodeText)
  331. created = Column(DateTime, default=datetime.datetime.now)
  332. @property
  333. def get_path(self):
  334. # return '/upload/%s/%s/%s' % (self.media_type, self.media_table, self.filename)
  335. return '/resources/%s/%s/%s' % (self.for_year, self.media_type, self.filename)
  336. class SallePhy(Base):
  337. """ Représente une salle dans les locaux """
  338. __tablename__ = 'phy_salle'
  339. uid = Column(Integer, primary_key=True)
  340. name = Column(Unicode(40)) # Numéro de salle vu de polytech
  341. slug = Column(Unicode(40))
  342. description = Column(UnicodeText) # Description du matériel disponible
  343. nb_places = Column(Integer, default=0) # Nombre de places assises
  344. @classmethod
  345. def by_id(cls, uid):
  346. return DBSession.query(cls).filter(cls.uid == uid).first()
  347. @property
  348. def PhotosLinks(self):
  349. from .upload import MediaPath
  350. return MediaPath().get_list('salle', self.uid, 'Image')
  351. class Salles(Base):
  352. __tablename__ = 'salle'
  353. salle_id = Column(Integer, primary_key=True)
  354. phy_salle_id = Column(Integer, ForeignKey('phy_salle.uid'))
  355. year_uid = Column(Integer, ForeignKey('jm2l_year.year_uid'), default=CurrentYear)
  356. name = Column(Unicode(40))
  357. place_type = Column(Enum('Conference', 'Stand', 'Atelier', 'Table ronde', 'MAO', 'Repas', 'Autres'))
  358. description = Column(UnicodeText) # Description du matériel disponible
  359. created = Column(DateTime, default=datetime.datetime.now)
  360. last_change = Column(DateTime, default=datetime.datetime.now)
  361. phy = relationship(SallePhy)
  362. @classmethod
  363. def by_id(cls, uid):
  364. return DBSession.query(cls).filter(cls.salle_id == uid).first()
  365. class Place(Base):
  366. __tablename__ = 'place'
  367. place_id = Column(Integer, primary_key=True)
  368. usage = Column(Boolean, default=False) # By Default / Extended
  369. place_type = Column(Enum('Aeroport', 'Gare', 'JM2L', \
  370. 'Hotel', 'Habitant', 'Restaurant', 'Autres'))
  371. display_name = Column(Unicode(20))
  372. name = Column(Unicode(80))
  373. slug = Column(Unicode(80))
  374. specific = Column(Unicode(80)) # eg Terminal 2
  375. gps_coord = Column(Unicode(30))
  376. adresse = Column(Unicode(100))
  377. codePostal = Column(Unicode(5))
  378. ville = Column(Unicode(40))
  379. website = Column(Unicode(100))
  380. description = Column(UnicodeText)
  381. created_by = Column(Integer, ForeignKey('users.user_id'))
  382. created = Column(DateTime, default=datetime.datetime.now)
  383. last_change = Column(DateTime, default=datetime.datetime.now)
  384. @classmethod
  385. def by_id(cls, uid):
  386. return DBSession.query(cls).filter(cls.place_id == uid).first()
  387. @classmethod
  388. def get_list(cls, All=False):
  389. if All:
  390. return DBSession.query(cls).all()
  391. else:
  392. return DBSession.query(cls).filter(cls.usage == True).all()
  393. class Itineraire(Base):
  394. __tablename__ = 'itineraire'
  395. itin_id = Column(Integer, primary_key=True)
  396. start_place = Column(Integer, ForeignKey('place.place_id')) # Place link
  397. arrival_place = Column(Integer, ForeignKey('place.place_id')) # Place link
  398. distance = Column(Integer)
  399. duration = Column(Integer)
  400. price = Column(Integer)
  401. tr_pied = Column(Boolean, default=False)
  402. tr_velo = Column(Boolean, default=False)
  403. tr_moto = Column(Boolean, default=False)
  404. tr_voiture = Column(Boolean, default=False)
  405. tr_bus = Column(Boolean, default=False)
  406. tr_taxi = Column(Boolean, default=False)
  407. tr_avion = Column(Boolean, default=False)
  408. description = Column(UnicodeText)
  409. created_by = Column(Integer, ForeignKey('users.user_id')) # User link
  410. created = Column(DateTime, default=datetime.datetime.now)
  411. last_change = Column(DateTime, default=datetime.datetime.now)
  412. # relations
  413. start = relationship(Place, foreign_keys=[start_place])
  414. arrival = relationship(Place, foreign_keys=[arrival_place])
  415. class Exchange_Cat(Base):
  416. __tablename__ = 'exchange_category'
  417. cat_id = Column(Integer, primary_key=True)
  418. exch_type = Column(Enum('H', 'C', 'M')) # Heberg, Co-voit, Materiel
  419. exch_subtype = Column(Unicode(80))
  420. description = Column(UnicodeText)
  421. class Exchange(Base):
  422. __tablename__ = 'exchanges'
  423. exch_id = Column(Integer, primary_key=True)
  424. for_year = Column(Integer, ForeignKey('jm2l_year.year_uid')) # link JM2L_Year
  425. exch_done = Column(Boolean, default=False)
  426. exch_state = Column(Enum('Ask', 'Proposal'))
  427. exch_type = Column(Enum('H', 'C', 'M')) # Heberg, Co-Voit, Materiel
  428. exch_categ = Column(Integer, ForeignKey('exchange_category.cat_id')) # Exchange_Cat link
  429. # Users
  430. asker_id = Column(Integer, ForeignKey('users.uid')) # User link
  431. provider_id = Column(Integer, ForeignKey('users.uid')) # User link
  432. start_time = Column(DateTime, default=datetime.datetime.now)
  433. end_time = Column(DateTime, default=datetime.datetime.now)
  434. # Co-voiturage
  435. itin_id = Column(Integer, ForeignKey('itineraire.itin_id')) # Itineraire link
  436. # Hebergement
  437. place_id = Column(Integer, ForeignKey('place.place_id')) # Place link
  438. # Materiel
  439. duration = Column(Integer)
  440. description = Column(UnicodeText)
  441. pictures = Column(Unicode(80))
  442. created_by = Column(Integer) # User link
  443. created = Column(DateTime, default=datetime.datetime.now)
  444. last_change = Column(DateTime, default=datetime.datetime.now)
  445. # relations
  446. Category = relationship(Exchange_Cat, backref="exchanges")
  447. Itin = relationship(Itineraire, backref="exchanged")
  448. asker = relationship(User, foreign_keys=[asker_id], backref="asked")
  449. provider = relationship(User, foreign_keys=[provider_id], backref="provided")
  450. @classmethod
  451. def by_id(cls, uid):
  452. return DBSession.query(cls).filter(cls.exch_id == uid).first()
  453. @classmethod
  454. def get_counters(cls):
  455. return DBSession.query(cls.exch_state, cls.exch_type, cls.exch_done, func.count(cls.exch_id)) \
  456. .filter(cls.for_year == CurrentYear) \
  457. .group_by(cls.exch_state, cls.exch_type, cls.exch_done)
  458. @classmethod
  459. def get_my_counters(cls, uid):
  460. return DBSession.query(cls.exch_state, cls.exch_type, cls.exch_done, func.count(cls.exch_id)) \
  461. .filter(cls.for_year == CurrentYear) \
  462. .filter(or_(cls.asker_id == uid, cls.provider_id == uid)) \
  463. .group_by(cls.exch_state, cls.exch_type, cls.exch_done)
  464. @classmethod
  465. def get_overview(cls, uid):
  466. # Build a Dic with all exchange to save database access
  467. DicResult = {}
  468. for extype in ['F', 'C', 'H', 'M']:
  469. DicResult[extype] = {}
  470. for exstate in ['Ask', 'Proposal', 'Missing', 'Agree']:
  471. DicResult[extype][exstate] = []
  472. DicResult[extype]['Counters'] = {'AllAsk': 0, 'AllProp': 0, 'AllAgree': 0}
  473. Query = DBSession.query(cls) \
  474. .filter(cls.for_year == CurrentYear) \
  475. .order_by(cls.start_time).all()
  476. for item in Query:
  477. if item.exch_done:
  478. DicResult[item.exch_type]['Counters']['AllAgree'] += 1
  479. if item.exch_state == 'Ask':
  480. DicResult[item.exch_type]['Counters']['AllAsk'] += 1
  481. if item.exch_state == 'Proposal':
  482. DicResult[item.exch_type]['Counters']['AllProp'] += 1
  483. if item.asker_id == uid or item.provider_id == uid:
  484. if item.asker_id == uid and item.exch_state == 'Ask':
  485. DicResult[item.exch_type]['Ask'].append(item)
  486. if item.provider_id == uid and item.exch_state == 'Ask':
  487. DicResult[item.exch_type]['Proposal'].append(item)
  488. if item.asker_id == uid and item.exch_state == 'Proposal':
  489. DicResult[item.exch_type]['Ask'].append(item)
  490. if item.provider_id == uid and item.exch_state == 'Proposal':
  491. DicResult[item.exch_type]['Proposal'].append(item)
  492. if item.exch_done:
  493. DicResult[item.exch_type]['Agree'].append(item)
  494. else:
  495. DicResult[item.exch_type]['Missing'].append(item)
  496. return DicResult
  497. @classmethod
  498. def get_pub_list(cls, exch_type):
  499. return DBSession.query(cls).filter(cls.for_year == CurrentYear and cls.exch_state in ['Ask', 'Proposal']) \
  500. .filter(cls.exch_type == '%s' % exch_type) \
  501. .filter(cls.exch_done == False) \
  502. .all()
  503. @classmethod
  504. def get_my_list(cls, uid, exch_type):
  505. DicResult = {}
  506. DicResult['Ask'] = DBSession.query(cls) \
  507. .filter(cls.for_year == CurrentYear) \
  508. .filter(or_(cls.asker_id == uid, cls.provider_id == uid)) \
  509. .filter(cls.exch_type == '%s' % exch_type) \
  510. .filter(cls.exch_state == 'Ask') \
  511. .order_by(cls.start_time).all()
  512. DicResult['Proposal'] = DBSession.query(cls) \
  513. .filter(cls.for_year == CurrentYear) \
  514. .filter(or_(cls.asker_id == uid, cls.provider_id == uid)) \
  515. .filter(cls.exch_type == '%s' % exch_type) \
  516. .filter(cls.exch_state == 'Proposal') \
  517. .order_by(cls.start_time).all()
  518. return DicResult
  519. class Sejour(Base):
  520. __tablename__ = 'sejour'
  521. sej_id = Column(Integer, primary_key=True)
  522. user_id = Column(Integer, ForeignKey('users.uid')) # User link
  523. for_year = Column(Integer, ForeignKey('jm2l_year.year_uid')) # link JM2L_Year
  524. arrival_time = Column(DateTime)
  525. arrival_place = Column(Integer, ForeignKey('place.place_id')) # Place link
  526. arrival_check = Column(Integer, default=0)
  527. arrival_text = Column(Unicode(100))
  528. depart_time = Column(DateTime)
  529. depart_place = Column(Integer, ForeignKey('place.place_id')) # Place link
  530. depart_check = Column(Integer, default=0)
  531. depart_text = Column(Unicode(100))
  532. repas = Column(Integer)
  533. repas_allerg = Column(Unicode(100))
  534. repas_contr = Column(Unicode(100))
  535. orga_part = Column(Integer, default=0)
  536. travel_detail = Column(UnicodeText)
  537. created = Column(DateTime, default=datetime.datetime.now)
  538. last_change = Column(DateTime, default=datetime.datetime.now)
  539. @classmethod
  540. def by_user(cls, uid, year):
  541. return DBSession.query(cls) \
  542. .filter(cls.user_id == uid) \
  543. .filter(cls.for_year == year) \
  544. .first()
  545. class Event(Base):
  546. __tablename__ = 'events'
  547. uid = Column(Integer, primary_key=True)
  548. salle_uid = Column(Integer, ForeignKey('salle.salle_id'))
  549. event_uid = Column(Integer)
  550. for_year = Column(Integer, ForeignKey('jm2l_year.year_uid')) # link JM2L_Year
  551. name = Column(Unicode(100), nullable=False)
  552. slug = Column(Unicode(100))
  553. event_type = Column(Enum('Conference', 'Stand', 'Atelier', 'Table ronde', 'MAO', 'Repas', 'Autres'))
  554. start_time = Column(DateTime, default=datetime.datetime.now)
  555. end_time = Column(DateTime, default=datetime.datetime.now)
  556. description = Column(UnicodeText)
  557. created = Column(DateTime, default=datetime.datetime.now)
  558. last_change = Column(DateTime, default=datetime.datetime.now)
  559. intervenants = relationship(User,
  560. secondary='user_event_link',
  561. backref=backref('participate', uselist=False),
  562. lazy='subquery')
  563. interventions = relationship(User_Event, backref="matching_events")
  564. Salle = relationship(Salles, backref='allevents')
  565. @classmethod
  566. def by_id(cls, uid):
  567. return DBSession.query(cls) \
  568. .filter(cls.uid == uid).first()
  569. @classmethod
  570. def by_slug(cls, slug, year=None):
  571. if not year is None:
  572. return DBSession.query(cls) \
  573. .filter(cls.for_year == year) \
  574. .filter(cls.slug == slug).first()
  575. else:
  576. return DBSession.query(cls) \
  577. .filter(cls.slug == slug).first()
  578. def get_linked_tiers(self):
  579. ListLink = DBSession.query(Role_Tiers.tiers_uid) \
  580. .filter(Role_Tiers.year_uid == self.for_year) \
  581. .filter(Role_Tiers.tiers_role == "Exposant") \
  582. .filter(Role_Tiers.event_uid == self.uid)
  583. return DBSession.query(Tiers).filter(Tiers.uid.in_(ListLink))
  584. @property
  585. def video(self):
  586. return DBSession.query(Media) \
  587. .filter(Media.media_table == 'event') \
  588. .filter(Media.media_type == 'Video') \
  589. .filter(Media.link_id == self.uid)
  590. @property
  591. def presentation(self):
  592. return DBSession.query(Media) \
  593. .filter(Media.media_table == 'event') \
  594. .filter(Media.media_type == 'Pres') \
  595. .filter(Media.link_id == self.uid)
  596. @property
  597. def created_in_words(self):
  598. return time_ago_in_words(self.created)
  599. class Entry(Base):
  600. __tablename__ = 'entries'
  601. id = Column(Integer, primary_key=True)
  602. active = Column(Integer, default=True)
  603. title = Column(Unicode(255), unique=True, nullable=False)
  604. body = Column(UnicodeText, default=u'')
  605. created = Column(DateTime, default=datetime.datetime.now)
  606. edited = Column(DateTime, default=datetime.datetime.now)
  607. @classmethod
  608. def all(cls):
  609. return DBSession.query(Entry).order_by(sa.desc(Entry.created))
  610. @classmethod
  611. def by_id(cls, uid):
  612. return DBSession.query(Entry).filter(Entry.id == uid).first()
  613. @property
  614. def slug(self):
  615. return urlify(self.title)
  616. @property
  617. def created_in_words(self):
  618. return time_ago_in_words(self.created)
  619. @classmethod
  620. def get_paginator(cls, request, page=1):
  621. page_url = PageURL_WebOb(request)
  622. return Page(Entry.all(), page, url=page_url, items_per_page=5)
  623. # class Seances(Base):
  624. # __tablename__ = 'seances'
  625. def get_user(request):
  626. # the below line is just an example, use your own method of
  627. # accessing a database connection here (this could even be another
  628. # request property such as request.db, implemented using this same
  629. # pattern).
  630. userid = unauthenticated_userid(request)
  631. if userid is not None:
  632. # this should return None if the user doesn't exist
  633. # in the database
  634. return DBSession.query(User).filter(User.uid == userid).first()
  635. def get_sponsors(request, Year):
  636. if Year:
  637. return DBSession.query(Tiers) \
  638. .join(Role_Tiers, Role_Tiers.tiers_uid == Tiers.uid) \
  639. .filter(Role_Tiers.tiers_role == 'Sponsor') \
  640. .filter(Role_Tiers.year_uid == Year)
  641. else:
  642. return DBSession.query(Tiers) \
  643. .join(Role_Tiers, Role_Tiers.tiers_uid == Tiers.uid) \
  644. .filter(Role_Tiers.tiers_role == 'Sponsor')
  645. def get_exposants(request, Year):
  646. if Year:
  647. return DBSession.query(Tiers) \
  648. .join(Role_Tiers, Role_Tiers.tiers_uid == Tiers.uid) \
  649. .filter(Role_Tiers.tiers_role == 'Exposant') \
  650. .filter(Role_Tiers.year_uid == Year)
  651. else:
  652. return DBSession.query(Tiers) \
  653. .join(Role_Tiers, Role_Tiers.tiers_uid == Tiers.uid) \
  654. .filter(Role_Tiers.tiers_role == 'Exposant')