def getPhotos(filter='A', userId=''):
photoList = []
photos = None
if filter == 'U': #photos uploaded by a specific user
photos = execute_sql(
'SELECT gallery.photoId, gallery.photoTitle, gallery.photoFileName, gallery.uploadBy, '
'gallery.uploadDate, flagusers.firstName, flagusers.lastName, flagusers.website '
'FROM gallery, flagusers Where gallery.uploadBy = flagusers.email And gallery.uploadBy = ? Order By uploadDate desc',
(userId, ))
else: #all photos
photos = execute_sql(
'SELECT gallery.photoId, gallery.photoTitle, gallery.photoFileName, gallery.uploadBy, '
'gallery.uploadDate, flagusers.firstName, flagusers.lastName, flagusers.website '
'FROM gallery, flagusers Where gallery.uploadBy = flagusers.email Order By uploadDate desc'
)
for row in photos:
photo = Photo()
photo.photoId = row['photoId']
photo.photoTitle = row['photoTitle']
photo.photoFileName = row['photoFileName']
photo.uploadDate = row['uploadDate']
photo.uploadBy = row['uploadBy']
photo.userFullName = row['firstName'] + ' ' + row['lastName']
photo.userWebsite = row['website']
#photo.photoImg = b64encode(row['photo']).decode("utf-8")
photoList.append(photo)
return photoList
def getEvent(event_id):
sql = 'SELECT * from event where id = ?'
row = execute_sql(sql, (event_id, ), False, True)
event = Event()
event.eventTitle = row['title']
event.eventDesc = row['description']
event.eventLocation = row['location']
event.eventDate = datetime.datetime.strptime(
row['eventdate'], '%Y-%m-%d').strftime('%m/%d/%Y')
event.startTime = row['startTime']
event.endTime = row['endTime']
return event
def getUpcomingEvent():
sql = "SELECT * from event where eventDate >= date('now') Order by eventDate LIMIT 1"
row = execute_sql(sql, (), False, True)
if not row == None:
event = Event()
event.eventTitle = row['title']
event.eventDesc = row['description']
event.eventLocation = row['location']
event.eventDate = datetime.datetime.strptime(
row['eventdate'], '%Y-%m-%d').strftime('%m/%d/%Y')
event.startTime = row['startTime']
event.endTime = row['endTime']
return event
def getPhoto(photoId):
sql = 'SELECT gallery.photoId, gallery.photoTitle, gallery.photoFileName, gallery.uploadDate, flagusers.firstName, flagusers.lastName, flagusers.website FROM gallery, flagusers WHERE gallery.photoId = ? And gallery.uploadBy = flagusers.email'
row = execute_sql(sql, (photoId, ), False, True)
photo = Photo()
photo.photoId = row['photoId']
photo.photoTitle = row['photoTitle']
photo.photoFileName = row['photoFileName']
photo.uploadDate = row['uploadDate']
photo.uploadBy = row['firstName'] + ' ' + row['lastName']
photo.userWebsite = row['website']
return photo
def getSiteContents():
siteContentList = []
siteContents = execute_sql(
"SELECT sitecontent.id, sitecontent.title, sitecontent.description, sitecontent.isHtml From sitecontent"
)
for row in siteContents:
siteContent = SiteContent()
siteContent.siteContentId = row['id']
siteContent.siteContentTitle = row['title']
siteContent.isHtml = row['isHtml']
if siteContent.isHtml == 'Y':
siteContent.siteContentDesc = row['description']
else:
siteContent.siteContentDesc = row['description'].split(
'\n') #.replace("\n", "<br/>")
siteContentList.append(siteContent)
return siteContentList
def getEvents():
eventList = []
events = execute_sql(
"SELECT event.id, event.title, event.description, event.location, event.eventDate, event.startTime, event.endTime FROM event WHERE eventDate >= date('now') ORDER BY eventDate"
)
for row in events:
event = Event()
event.eventId = row['id']
event.eventTitle = row['title']
event.eventDesc = row['description'].split(
'\n') #.replace("\n", "<br/>")
event.eventLocation = row['location']
event.eventDate = datetime.datetime.strptime(
row['eventdate'], '%Y-%m-%d').strftime('%d-%b-%Y')
event.startTime = row['startTime']
event.endTime = row['endTime']
eventList.append(event)
return eventList
def deletePhoto(photoId):
sql = 'DELETE FROM gallery WHERE photoId = ?'
execute_sql(sql, (photoId, ), True)
def savePhoto(photo):
sql = 'INSERT into gallery (photoTitle, photoFileName, uploadDate, uploadBy) VALUES ( ?, ?, ?, ?)' #, photo
execute_sql(sql, (photo.photoTitle, photo.photoFileName,
datetime.datetime.now(), photo.uploadBy),
True) #, lite.Binary(photo.photoImg)
def updateEvent(event):
sql = 'UPDATE event set title = ? , description = ?, location = ? , eventDate = ?, startTime = ?, endTime = ?, status = ? , createdBy = ?, createdOn = ? WHERE id = ?'
execute_sql(sql,
(event.eventTitle, event.eventDesc, event.eventLocation,
event.eventDate, event.startTime, event.endTime, 'A', 'David',
datetime.datetime.utcnow(), event.eventId), True)
def deleteEvent(event_id):
sql = 'DELETE from event where id = ?'
execute_sql(sql, (event_id, ), True, False)
def saveEvent(event):
sql = 'INSERT into event (title, description, location, eventDate, startTime, endTime, status, createdBy, createdOn) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)'
execute_sql(sql,
(event.eventTitle, event.eventDesc, event.eventLocation,
event.eventDate, event.startTime, event.endTime, 'A', 'David',
datetime.datetime.utcnow()), True)