def confirm_order():
if not html.check_logged_in(loggedIn):
return
viewerID = sql.run_sql("""SELECT ViewerID FROM ViewerLogin
WHERE UserName = '******'"""%username)[0][0]
videoids = sql.run_sql("""SELECT VideoID FROM ViewerOrderLine
WHERE ViewerOrderID IN (SELECT ViewerOrderID FROM ViewerOrder
WHERE ViewerID = %d)"""%viewerID)
for item in videoids:
if int(vidid) in item:
redirect.refresh("viewer_order.py?error=1", sess.cookie)
return
viewerorderID = sql.run_insert("""INSERT INTO ViewerOrder VALUES
(DEFAULT, CURDATE(), NULL, 'Pending', %d)"""%viewerID)
viewerType = sql.run_sql("""SELECT ViewerType FROM Viewer
WHERE ViewerID = %d"""%viewerID)[0][0]
if viewerType == 'C':
viewerType = 1
else:
viewerType = 0
sql.run_insert("""INSERT INTO ViewerOrderLine VALUES
(%s, %s, %s)"""%(vidid, viewerorderID, str(bool(viewerType)).upper()))
redirect.refresh("viewer_order.py", sess.cookie)
return
def viewer_login():
result = sql.run_sql(""" SELECT * FROM ViewerLogin
WHERE UserName = "******"
AND UserPwd = "%s"
""" %(form['username'].value, form['password'].value))
if result:
sess.data['loggedIn'] = 1
sess.data['userName'] = result[0][0]
else:
sess.data['loggedIn'] = -1
renew = sql.run_sql("""SELECT RenewalDATE FROM PremiumViewer
WHERE ViewerID = (SELECT ViewerID FROM Viewer
WHERE ViewerID = (SELECT ViewerID FROM ViewerLogin
WHERE UserName = "******"
AND UserPwd = "%s"))""" %(form['username'].value, form['password'].value))
if renew:
check_renew = sql.run_sql("""SELECT RenewalDATE FROM PremiumViewer
WHERE ViewerID = (SELECT ViewerID FROM Viewer
WHERE ViewerID = (SELECT ViewerID FROM ViewerLogin
WHERE UserName = "******"
AND UserPwd = "%s"))
AND RenewalDate < CURDATE()""" %(form['username'].value, form['password'].value))
if check_renew:
return False
return True
def print_body(username, error):
result = sql.run_sql(
(
"""SELECT * FROM Player WHERE PlayerID = (SELECT PlayerID FROM PlayerLogin WHERE userName = "******")
"""
% (username)
)
)
if result[0][1] == None:
supervisorName = "He is a BOSS"
else:
supervisorName = sql.run_sql(
(
"""SELECT * FROM Player WHERE PlayerID = "%s"
"""
% (result[0][1])
)
)
resultAdd = sql.run_sql(
(
"""SELECT * FROM Address
WHERE AddressID = (SELECT AddressID FROM PlayerAddress
WHERE PlayerID = (SELECT PlayerID FROM PlayerLogin WHERE userName = "******")
AND EndDate is NULL)
"""
% (username)
)
)
form.print_details(result[0], resultAdd[0], supervisorName, error, loggedIn)
return
def print_body(username, error):
viewerType = sql.run_sql("""SELECT ViewerType FROM Viewer
WHERE ViewerID = (SELECT ViewerID FROM ViewerLogin
WHERE ViewerLogin.UserName = "******")"""%username)[0][0]
if viewerType != 'P':
result = sql.run_sql("""SELECT Viewer.ViewerID, Viewer.ViewerType, Viewer.DATEOfBirth, Viewer.Email,
Address.StreetNumber, Address.StreetName, Address.StreetType, Address.MinorMunicipality, Address.MajorMunicipality, Address.GoverningDistrict, Address.PostalArea, Address.Country
FROM Viewer, ViewerLogin, Address, ViewerAddress
WHERE ViewerLogin.Username = "******"
AND ViewerAddress.viewerID = ViewerLogin.viewerID
AND Address.addressID = ViewerAddress.addressID
AND ViewerLogin.viewerID = Viewer.viewerID
AND ViewerAddress.endDATE is null""" % (username))[0]
else:
result = sql.run_sql("""SELECT Viewer.ViewerID, Viewer.ViewerType, PremiumViewer.RenewalDATE, Viewer.DATEOfBirth, Viewer.Email,
Address.StreetNumber, Address.StreetName, Address.StreetType, Address.MinorMunicipality, Address.MajorMunicipality, Address.GoverningDistrict, Address.PostalArea, Address.Country
FROM Viewer, ViewerLogin, Address, ViewerAddress, PremiumViewer
WHERE ViewerLogin.Username = "******"
AND ViewerAddress.viewerID = ViewerLogin.viewerID
AND Address.addressID = ViewerAddress.addressID
AND ViewerLogin.viewerID = Viewer.viewerID
AND ViewerAddress.endDATE is null""" % (username))[0]
form.print_details(result, error, loggedIn)
return
def remove_instance(instanceRunID):
#Get related achievements
sql.run_update(("""UPDATE Achievement
SET InstanceRunID = NULL, WhenAchieved = NULL
WHERE InstanceRunID = "%s"
""" % (instanceRunID)))
#Get related Videos
videoID = sql.run_sql(("""SELECT VideoID FROM Video WHERE InstanceRunID = "%s" """ % (instanceRunID)))
if videoID:
videoID = videoID[0][0]
#Remove all viewerOrders for that video
viewerOrders = sql.run_sql(("""SELECT ViewerOrderID FROM ViewerOrderLine WHERE VideoID = "%s" """% (videoID)))
sql.run_remove(("""DELETE FROM ViewerOrderLine WHERE VideoID = "%s"
""" % (videoID)))
for i in viewerOrders:
sql.run_remove(("""DELETE FROM ViewerOrder
WHERE ViewerOrderID = "%s"
""" % (i[0])))
#Delete Video
sql.run_remove(("""DELETE FROM Video WHERE VideoID = "%s"
""" % (videoID)))
#Delete instaceRun
sql.run_remove(("""DELETE FROM InstanceRun WHERE InstanceRunID= "%s"
""" % (instanceRunID)))
return
def remove_viewer(viewerID):
sql.run_remove(("""DELETE FROM CrowdFundingViewer WHERE ViewerID = "%s"
"""% (viewerID)))
sql.run_remove(("""DELETE FROM PremiumViewer WHERE ViewerID = "%s"
"""% (viewerID)))
addressIDs = sql.run_sql(("""SELECT AddressID FROM ViewerAddress
WHERE ViewerID = "%s"
AND AddressID NOT IN (SELECT AddressID FROM ViewerAddress WHERE ViewerID != "%s")
AND AddressID NOT IN (SELECT DISTINCT(AddressID) FROM PlayerAddress)
""" %(viewerID,viewerID)))
sql.run_remove(("""DELETE FROM ViewerAddress WHERE ViewerID = "%s"
""" % (viewerID)))
for i in addressIDs:
sql.run_remove(("""DELETE FROM Address WHERE AddressID = "%s"
""" % (i[0])))
orderIDs = sql.run_sql(("""SELECT ViewerOrderID FROM ViewerOrder WHERE ViewerID = "%s"
""" % (viewerID)))
for i in orderIDs:
sql.run_remove(("""DELETE FROM ViewerOrderLine WHERE ViewerOrderID = "%s"
""" % (i[0])))
sql.run_remove(("""DELETE FROM ViewerOrder WHERE ViewerID = "%s"
""" % (viewerID)))
sql.run_remove(("""DELETE FROM ViewerLogin WHERE ViewerID = "%s"
""" % (viewerID)))
sql.run_remove(("""DELETE FROM Viewer WHERE ViewerID = "%s"
""" % (viewerID)))
return
def remove_achievement(achievementID):
#Firstly, get videoIDs
videoID = sql.run_sql(("""SElECT VideoID FROM Video
WHERE InstanceRunID = (SELECT InstanceRunID From Achievement WHERE AchievementID = "%s")
""" %(achievementID)))
#If video exist for this achievement
if videoID:
videoID = videoID[0][0]
#Get all the viewerOrders for the video
viewerOrders = sql.run_sql(("""SELECT ViewerOrderID FROM ViewerOrderLine WHERE VideoID = "%s" """% (videoID)))
#Remove all ViewerOrderLine data with the VideoID
sql.run_remove(("""DELETE FROM ViewerOrderLine WHERE VideoID = "%s"
""" % (videoID)))
#Remove all ViewerOrders
for i in viewerOrders:
sql.run_remove(("""DELETE FROM ViewerOrder
WHERE ViewerOrderID = "%s"
""" % (i[0])))
#finall,y remove Video
sql.run_remove(("""DELETE FROM Video WHERE VideoID = "%s"
""" % (videoID)))
#Remove Achievement
sql.run_remove(("""DELETE FROM Achievement
WHERE AchievementID = "%s"
""" % (achievementID)))
#Remove InstanceRun to keep the logic we have decided
sql.run_remove(("""DELETE FROM InstanceRun WHERE InstanceRunID = (SELECT InstanceRunID FROM Achievement WHERE AchievementID = "%s")
""" % (achievementID)))
return
def addVideoInfo():
gameID = sql.run_sql("""SELECT GameID FROM Game WHERE Name = "%s" """ % (GameName))[0][0]
instancerunID = sql.run_sql("""SELECT InstanceRunID FROM InstanceRun WHERE Name = "%s" """ % (InstanceRunName))[0][0]
videoID= sql.run_insert("""INSERT INTO Video VALUES
(DEFAULT, '%s', '%s', '%s', '%s', '%s', '%s')
"""%(videoName,videoURL,videoPrice,videoType,instancerunID,gameID))
return videoID
def print_body():
print "<div>"
viewerID = sql.run_sql("""SELECT ViewerID FROM ViewerLogin
WHERE UserName = '******'"""%username)[0][0]
renewalDate = sql.run_sql("""SELECT RenewalDATE FROM PremiumViewer
WHERE ViewerID = %d"""%viewerID)[0][0]
form.view_form(viewerID, renewalDate)
print "</div>"
return
def print_fields():
#run sql to get gameNames and InstanceRun Names for dropdown menu
gameNames= sql.run_sql("""SELECT Name FROM Game""")
InstanceNames= sql.run_sql("""SELECT Name FROM InstanceRun""")
print_a_field("videoName", "Video Name", "Enter Video Name")
print_a_field("videoURL", "Video URL", "Enter URL of video")
print_a_field("videoPrice", "Video Price", "Enter Video Price")
print_drop_menu("videoType", "Video Type", (('Premium',),('Free',)))
print_drop_menu("InstanceName", "Instance Run", InstanceNames)
print_drop_menu("gameName", "Game Name", gameNames)
return
def load_page():
#Get Session from cookie and fieldstorage that has been passed on from previous page
sess = session.Session(expires=20*60, cookie_path='/')
username = sess.data.get('userName')
loggedIn = sess.data.get('loggedIn')
#Oepn html using session
html.open_html(sess.cookie, loggedIn)
#Is logged in as Viewer
if loggedIn == 1:
#get viewer type of the current user
viewer_type = sql.run_sql(
"""SELECT ViewerType FROM Viewer
WHERE Viewer.ViewerID = (SELECT ViewerLogin.ViewerID FROM ViewerLogin
WHERE ViewerLogin.UserName = "******")"""%(username))[0][0]
if viewer_type == 'C':
name = sql.run_sql("""SELECT FirstName, LastName FROM CrowdFundingViewer
WHERE CrowdFundingViewer.ViewerID = (SELECT ViewerLogin.ViewerID FROM ViewerLogin
WHERE ViewerLogin.UserName = "******")"""%(sess.data.get('userName')))[0]
html.print_welcome(name[0], name[1])
if loggedIn in (1, 2):
videos = sql.run_sql("""SELECT VideoID, Video.Name, Game.Name, VideoType, InstanceRun.Name, Player.GameHandle, Player.PlayerID, Equipment.ModelAndMake, Equipment.EquipmentID FROM Video, Game, InstanceRun, Player, Equipment
WHERE Video.InstanceRunID = InstanceRun.InstanceRunID
AND Video.GameID = Game.GameID
AND InstanceRun.SupervisorID = Player.PlayerID
AND Player.PlayerID = (SELECT Venue.SupervisorID FROM Venue
WHERE Venue.VenueID = (SELECT VenueEquipment.VenueID FROM VenueEquipment
WHERE VenueEquipment.EquipmentID = Equipment.EquipmentID))
ORDER BY VideoID""")
else:
videos = sql.run_sql("""SELECT VideoID, Video.Name, Game.Name, VideoType, InstanceRun.Name, Player.GameHandle, Player.PlayerID, Equipment.ModelAndMake, Equipment.EquipmentID FROM Video, Game, InstanceRun, Player, Equipment
WHERE Video.InstanceRunID = InstanceRun.InstanceRunID
AND Video.GameID = Game.GameID
AND InstanceRun.SupervisorID = Player.PlayerID
AND Player.PlayerID = (SELECT Venue.SupervisorID FROM Venue
WHERE Venue.VenueID = (SELECT VenueEquipment.VenueID FROM VenueEquipment
WHERE VenueEquipment.EquipmentID = Equipment.EquipmentID))
AND Video.VideoType = 'Free'
ORDER BY VideoID""")
html.print_videos("Videos", ("Name", "Game", "Type", "Instance", "Supervisor", "Equipment"), videos, loggedIn)
html.close_html()
sess.close()
return
def print_body(equipmentID, params):
print "<div>"
result = sql.run_sql("""SELECT * FROM Equipment
WHERE EquipmentID = %d"""%(int(equipmentID)))[0]
form.view_details(result)
print "</div>"
return
def print_body(playerID, paramsw):
print "<div>"
result = sql.run_sql("""SELECT * FROM Player
WHERE PlayerID = %d"""%(int(playerID)))[0]
form.view_details(result, loggedIn)
print "</div>"
return
def detect_address_change():
result = sql.run_sql("""
SELECT StreetNumber, StreetName, StreetType, MinorMunicipality, MajorMunicipality,
GoverningDistrict,PostalArea,Country FROM Address
WHERE Address.AddressID = "%s"
""" % (addressID))
if result:
address = result[0]
if address[0] != int(addNo):
return True
elif address[1] != addName:
return True
elif address[2] != addType:
return True
elif address[3] != addSuburb:
return True
elif address[4] != addCity:
return True
elif address[5] != addState:
return True
elif address[6] != addPost:
return True
elif address[7] != addCountry:
return True
else:
return False
return False
def edit_instancerun(IR):
players = sql.run_sql("""SELECT PlayerID FROM Player""")
blank_form_title("Edit Instance Run")
edit_instancerun_form(str(IR[0]))
edit_instancerun_fields(IR, players)
close_instancerun_form(str(IR[0]))
return
def addInstanceInfo():
superID = sql.run_sql(
"""SELECT playerID FROM Player WHERE GameHandle = "%s" """ %
(superName))[0][0]
instancerunID = sql.run_insert("""INSERT INTO InstanceRun VALUES
(DEFAULT, %s, '%s',NOW(), '%s')
""" % (superID, name, category))
return instancerunID
def print_fields():
#run SQL to get supervisor names for dropdown menu
supervisorNames= sql.run_sql("""SELECT GameHandle FROM Player""")
print_a_field("Name", "Instance Name", "Enter Instance Name")
print_a_field("Category", "Category", "Enter Category Tags")
print_drop_menu("supervisorName", "Supervisor Name", supervisorNames)
return
def updateAchievementInfo():
#Get date for new instanceRun
date = sql.run_sql(("""SELECT RecordedTime FROM InstanceRun WHERE InstanceRunID = "%s" """ %(instancerunID)))[0][0]
sql.run_update(("""UPDATE Achievement
SET InstanceRunID= "%s", WhenAchieved= "%s", Name= "%s", RewardBody = "%s"
WHERE AchievementID= "%s"
""" % (instancerunID, date,achievementName,rewardBody,achievementID)))
return
def print_fields(data):
instancerunIDs = sql.run_sql("""SELECT InstanceRunID FROM InstanceRun""")
print_disabled_field("achievementID", "Achievement ID", str(data[0]))
print_a_field("achievementName", "Achievement Name", str(data[1]))
print_drop_menu("instancerunID", "Instance Run ID", str(data[2]), instancerunIDs)
print_disabled_field("whenAchieved", "When Achieved", str(data[3]))
print_a_field("rewardBody", "Reward Body", str(data[4]))
return
def main():
if not html.check_player(loggedIn):
return
html.open_html(sess.cookie, loggedIn)
#Get data through SQL
achievements = sql.run_sql("""SELECT Achievement.AchievementID, Achievement.Name, Achievement.InstanceRunID, Achievement.WhenAchieved, Achievement.RewardBody
FROM Achievement""")
body.print_achievements("Achievements", ("Name", "Instance Run ID", "When Achieved", "Reward Body"), achievements, loggedIn)
html.close_html()
return
def checkAccount():
usernames = sql.run_sql("""SELECT UserName FROM ViewerLogin
WHERE UserName = '******'"""%viewerUserName)
if usernames:
error = 3
return error
if viewerPwd1 != viewerPwd2:
error = 4
return error
return
def main():
if not html.check_player(loggedIn):
return
html.open_html(sess.cookie, loggedIn)
#Get data through params and SQL
gameID = params['gameID'].value
game_data = sql.run_sql("""SELECT GameID, Name, Genre, Review, StarRating, ClassificationRating, PlatformNotes, PromotionLink, Cost FROM Game WHERE GameID = %s"""%(gameID))[0]
body.print_game(game_data)
html.close_html()
return
def main():
if not html.check_player(loggedIn):
return
html.open_html(sess.cookie, loggedIn)
#Get data through params and SQL
instance_runs = sql.run_sql("""SELECT IR.InstanceRunID, IR.Name, IR.RecordedTime, IR.CategoryName, P.GameHandle
FROM InstanceRun as IR, Player as P
WHERE P.PlayerID = IR.SupervisorID""")
body.print_instances("Instance Runs", ("Name", "Time", "Category Name", "Supervisor Handle"), instance_runs, loggedIn)
html.close_html()
return
def player_login():
result = sql.run_sql("""
SELECT * FROM PlayerLogin
WHERE UserName = "******"
AND UserPwd = "%s"
"""%(form['username'].value, form['password'].value))
if result:
sess.data['loggedIn'] = 2
sess.data['userName'] = result[0][0]
else:
sess.data['loggedIn'] = -1
return
def main():
if not html.check_player(loggedIn):
return
html.open_html(sess.cookie, loggedIn)
# Get data through params and SQL
achievementID = params["achievementID"].value
achievement_data = sql.run_sql(
"""SELECT AchievementID, Name, InstanceRunID, WhenAchieved, RewardBody FROM Achievement WHERE AchievementID = %s"""
% (achievementID)
)[0]
body.print_achievement(achievement_data)
html.close_html()
return
def print_body(video_id, params):
print "<div>"
viewer_type = 'C'
if loggedIn == 1:
viewer_type = sql.run_sql("""SELECT ViewerType FROM Viewer WHERE Viewer.ViewerID = (SELECT ViewerLogin.ViewerID FROM ViewerLogin WHERE ViewerLogin.UserName = "******")"""%(sess.data.get('userName')))[0][0]
paying = False
if viewer_type == 'N':
paying = True
if video_id == -10:
results = sql.run_sql("""SELECT Video.Name, InstanceRun.Name, Game.Name, Video.VideoType, Player.FirstName, Player.LastName, Video.Price, ViewerOrder.ViewedStatus, Video.URL, ViewerOrder.ViewerOrderID, ViewerOrder.ViewDate
FROM Video,ViewerOrderLine, InstanceRun, Game, Player,ViewerOrder
WHERE ViewerOrderLine.ViewerOrderID IN (SELECT ViewerOrder.ViewerOrderID FROM ViewerOrder WHERE ViewerOrder.ViewerID =
(SELECT ViewerID FROM ViewerLogin WHERE ViewerLogin.UserName = '******'))
AND Video.VideoID = ViewerOrderLine.VideoID
And Game.GameID = Video.GameID
And InstanceRun.InstanceRunID = Video.InstanceRunID
AND Player.PlayerID = InstanceRun.SupervisorID
AND ViewerOrder.ViewerOrderID = ViewerOrderLine.ViewerOrderID"""%(sess.data.get('userName')))
if params.has_key('error'):
error = True
else:
error = False
form.show_all_orders(results, paying, error)
else:
result = sql.run_sql("""
SELECT Video.Name, InstanceRun.Name, Game.Name, Video.VideoType, Player.FirstName, Player.LastName, Video.Price, Video.VideoID, Video.URL, Player.PlayerID
FROM Video, Game, InstanceRun, Player
WHERE Video.VideoID = "%s"
AND Game.GameID = Video.GameID
AND InstanceRun.InstanceRunID = Video.InstanceRunID
AND Player.PlayerID = InstanceRun.SupervisorID
"""% video_id)
if loggedIn == 1:
form.confirm_order(result[0], paying)
elif loggedIn == 2:
form.edit_video(result[0])
print "</div>"
return
def delete_video(videoID):
viewerOrders = sql.run_sql(("""SELECT ViewerOrderID FROM ViewerOrderLine WHERE VideoID = "%s" """
% (videoID)))
sql.run_remove(("""DELETE FROM ViewerOrderLine WHERE VideoID = "%s"
""" % (videoID)))
for i in viewerOrders:
sql.run_remove(("""DELETE FROM ViewerOrder
WHERE ViewerOrderID = "%s"
""" % (i[0])))
sql.run_remove(("""DELETE FROM Achievement
WHERE InstanceRunID = (SELECT InstanceRunID FROM Video WHERE VideoID = "%s")
""" % (videoID)))
sql.run_remove(("""DELETE FROM Video WHERE VideoID = "%s"
""" % (videoID)))
sql.run_remove(("""DELETE FROM InstanceRun WHERE InstanceRunID = (SELECT InstanceRunID FROM Video WHERE VideoID = "%s")
""" % (videoID)))
return
def main():
#Get Session from cookie and fieldstorage that has been passed on from previous page
sess = session.Session(expires=20*60, cookie_path='/')
username = sess.data.get('userName')
loggedIn = sess.data.get('loggedIn')
params= cgi.FieldStorage()
if not html.check_player(loggedIn):
return
#Get data through params and SQL
instanceRunID = params['instancerunID'].value
IR = sql.run_sql("""SELECT InstanceRun.InstanceRunID, InstanceRun.Name, InstanceRun.SupervisorId, InstanceRun.CategoryName FROM InstanceRun
WHERE InstanceRun.InstanceRunID = %s"""%instanceRunID)[0]
html.open_html(sess.cookie, loggedIn)
if IR:
body.edit_instancerun(IR)
html.close_html()
return
playeremail = params['email'].value
playerhandle= params['handle'].value
playerphone= params['phone'].value
playervoip= params['voip'].value
addID= params['aID'].value
addNo= params['stNo'].value
addName= params['stName'].value
addType= params['state'].value
addSuburb= params['stType'].value
addCity= params['city'].value
addState = params['state'].value
addPost = params['postcode'].value
addCountry = params['country'].value
username = sql.run_sql("""SELECT UserName FROM PlayerLogin WHERE PlayerLogin.PlayerID= %s"""%playerID)[0][0]
error = False
result = sql.run_sql("""SELECT PlayerAddress.AddressID FROM PlayerAddress, Player, Address
WHERE PlayerAddress.PlayerID= "%s"
AND PlayerAddress.EndDate is NULL
""" % (playerID))
addressID = result[0][0]
#######################################################################################################
def main():
#Print error if any exist
viewerRYear = params['ryear'].value
viewerRenewal = viewerRYear + "-"
viewerRenewal += viewerRMonth
viewerRenewal += "-"
viewerRenewal += viewerRDay
if params.has_key('renewSub'):
renewSub = True
else:
renewSub = False
if params.has_key('becomeSub'):
becomeSub = True
else:
becomeSub = False
username = sql.run_sql("""SELECT UserName FROM ViewerLogin WHERE ViewerLogin.ViewerID = %s"""%viewerID)[0][0]
isLeap = calendar.isleap(int(viewerDOBYear))
error = False
if ((int(viewerDOBMonth) in short_months) and (viewerDOBDay == 31)):
error = True
elif (int(viewerDOBMonth) not in long_months):
if (isLeap and (int(viewerDOBDay) > 29)):
error = True
elif (int(viewerDOBDay) > 28):
error = True
viewerDOB = viewerDOBYear + "-"