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 updateViewerInfo():
sql.run_update(("""UPDATE Player
SET FirstName = "%s", LastName = "%s", Role = "%s", ProfileDescription = "%s", Email = "%s", GameHandle = "%s", Phone = "%s", VoP = "%s"
WHERE PlayerID= "%s"
""" % (playerfname, playerlname,playerole,plaerdesc,playeremail,playerhandle,playerphone,playervoip,playerID)))
updateAddress()
return
def updateRenewal():
if viewerRenewal:
sql.run_update("""UPDATE PremiumViewer
SET RenewalDATE = '%s'
WHERE ViewerID = '%s'
"""%(viewerRenewal, viewerID))
return
def renewSubscription():
if renewSub:
sql.run_update("""UPDATE PremiumViewer
SET RenewalDATE = DATE_ADD(RenewalDate, INTERVAL 1 MONTH)
WHERE ViewerID = '%s'
"""%viewerID)
return
def cancelSubscription():
sql.run_update("""UPDATE Viewer
SET ViewerType = "N"
WHERE ViewerID = "%s"
"""%(viewerID))
sql.run_remove("""DELETE FROM PremiumViewer WHERE ViewerID = "%s"
"""%(viewerID))
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 becomePremium():
if becomeSub:
sql.run_update("""UPDATE Viewer
SET ViewerType = 'P'
WHERE ViewerID = '%s'
"""%(viewerID))
sql.run_insert("""INSERT INTO PremiumViewer VALUES
(%s, CURDATE() + INTERVAL 1 MONTH)"""%(viewerID))
return
def updateVideoInfo(price):
sql.run_update(("""UPDATE Video
SET Name = "%s", URL = "%s", Price = "%s", VideoType = "%s"
WHERE Video.VideoID = "%s"
""" % (videoName, URL, price, videoType, videoID)))
sql.run_update(("""UPDATE InstanceRun
SET SupervisorID = "%s"
WHERE InstanceRunID = (SELECT InstanceRunID FROM Video WHERE VideoID = "%s")
""" % (supervisorID, videoID)))
return
def updateViewerInfo():
sql.run_update(("""UPDATE Viewer
SET DATEOfBirth = "%s", Email = "%s", ViewerType = "%s"
WHERE viewerID = "%s"
""" % (viewerDOB, viewerEmail, viewerType[0], viewerID)))
updateAddress()
# Updating, renewing and adding premium subscriptions
renewSubscription()
updateRenewal()
becomePremium()
return
def updateAddress():
#update address only if it has been changed
if detect_address_change():
sql.run_update("""
UPDATE PlayerAddress
SET EndDATE = CURDATE()
WHERE PlayerID = "%s" AND AddressID = "%s"
"""% (playerID, addressID))
next_val = sql.run_insert("""
INSERT Address VALUES
(DEFAULT, "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")
""" % (addNo, addName, addType, addSuburb, addCity, addState, addPost, addCountry))
sql.run_insert("""
INSERT INTO PlayerAddress VALUES
("%s","%s",CURDATE(),NULL)
""" % (playerID, next_val))
return
def updateAddress():
if detect_address_change():
sql.run_update("""
UPDATE ViewerAddress
SET EndDATE = CURDATE()
WHERE ViewerID = "%s" AND AddressID = "%s"
"""% (viewerID, addressID))
next_val = sql.run_insert("""
INSERT Address VALUES
(DEFAULT, "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")
""" % (viewerStNo, viewerStName, viewerStType, viewerSuburb, viewerCity, viewerState, viewerPCode, viewerCountry))
sql.run_insert("""
INSERT INTO ViewerAddress VALUES
("%s","%s",CURDATE(),NULL)
""" % (viewerID, next_val))
return
return
def renewSubscription():
sql.run_update("""UPDATE PremiumViewer
SET RenewalDATE = CURDATE() + INTERVAL 1 MONTH
WHERE ViewerID = "%s"
"""%viewerID)
return
def updateInstanceInfo():
sql.run_update(("""UPDATE InstanceRun
SET Name = "%s", SupervisorID= "%s", CategoryName = "%s"
WHERE InstanceRunID= "%s"
""" % (instancerunName, supervisorID,categoryName,instancerunID)))
return
def updateGameInfo(price):
sql.run_update(("""UPDATE Game
SET Genre = "%s", Review = "%s", StarRating = "%s", ClassificationRating = "%s", PlatformNotes= "%s", PromotionLink = "%s", Cost = "%s"
WHERE Game.gameID = "%s"
""" % (gameGenre, gameReview, gameRate, gameCRate, gamePlatform, gameLink, price, gameID)))
return