def LogVisit(self, username, filters):
i_thname = filters.get("i_thname")
i_coname = filters.get("i_coname")
i_visitdate = filters.get("i_visitdate")
i_username = username
i_visitdate = dateutil.parser.parse(i_visitdate).date()
connection = get_conn()
with connection.cursor() as cursor:
leng = "select visitID from UserVisitTheater"
cursor.execute(leng)
length = cursor.fetchall()
connection.commit()
new_id = len(length) + 1
info = """insert into UserVisitTheater (visitID, username, thName, comName, visitDate) values ((%s),(%s),(%s),(%s),(%s))"""
cursor.execute(
info, (new_id, i_username, i_thname, i_coname, i_visitdate))
data = cursor.fetchall()
connection.commit()
connection.close()
return True
def test_filter_user(self):
connection = get_conn()
filterz = {
'i_status': "Declined",
'i_sortBy': "username",
'i_sortDirection': "desc"
}
admin_service = AdminService()
Actual = admin_service.FilterUser(filterz)
Expected = [{
'username': '******',
'creditCardNum': 0,
'status': 'Declined',
'userType': 'Customer'
}, {
'username': '******',
'creditCardNum': 0,
'status': 'Declined',
'userType': 'User'
}, {
'username': '******',
'creditCardNum': 0,
'status': 'Declined',
'userType': 'User'
}, {
'username': '******',
'creditCardNum': 0,
'status': 'Declined',
'userType': 'User'
}]
print(Actual)
assert Actual == Expected
def registerUser(self, user) -> bool:
username = user['username']
firstname = user['firstname']
lastname = user['lastname']
password = user['password']
connection = get_conn()
with connection.cursor() as cursor:
#Checking for duplicates
sql = "SELECT `username` FROM `User` where username=(%s)"
cursor.execute(sql, (username))
userDatas = cursor.fetchall()
connection.commit()
success = False
if len(userDatas) < 1:
#Inserting the values to User
sql = """INSERT INTO User (username, status, firstname, lastname, password)
VALUES (%s,%s,%s,%s,MD5(%s))"""
dataTuple = (username, 'Pending', firstname, lastname,
password)
cursor.execute(sql, dataTuple)
connection.commit()
success = True
connection.close()
return success
def CreateTheater(self, filters):
i_thName = filters.get("i_thName")
i_comName = filters.get("i_comName")
i_thStreet = filters.get("i_thStreet")
i_thCity = filters.get("i_thCity")
i_thState = filters.get("i_thState")
i_thZipcode = filters.get("i_thZipcode")
i_capacity = filters.get("i_capacity")
i_manUsername = filters.get("i_manUsername")
if i_thState == "ALL":
raise Exception("State Cannot Be ALL")
if i_comName == "ALL":
raise Exception("Company Name Cannot Be ALL")
connection = get_conn()
with connection.cursor() as cursor:
query2 = "insert into Theater (thName, comName, capacity, thStreet, thCity, thState, thZipcode, manUsername) \
values ((%s), (%s), (%s), (%s), (%s), (%s), (%s), (%s))"
cursor.execute(query2,
(i_thName, i_comName, i_capacity, i_thStreet,
i_thCity, i_thState, i_thZipcode, i_manUsername))
connection.commit()
connection.close()
def CreateMovie(self, filters):
i_movName = filters.get("movieName")
i_movDuration = filters.get("duration")
i_movReleaseDate = (dateutil.parser.parse(
filters.get("releaseDate"))).date()
connection = get_conn()
with connection.cursor() as cursor:
query7 = "SELECT movName FROM Movie WHERE (movName=(%s)) AND (movReleaseDate=(%s))"
cursor.execute(query7, (i_movName, i_movReleaseDate))
data = cursor.fetchall()
connection.commit()
print(data)
if len(data) < 1:
query3 = "insert into Movie (movName, movReleaseDate, duration) \
values ((%s), (%s), (%s))"
cursor.execute(query3,
(i_movName, i_movReleaseDate, i_movDuration))
connection.commit()
else:
connection.close()
return (
"Movie name and release date combination already taken")
connection.close()
return ("Movie Registered")
def CompanyDetail(self, comName):
i_comName = comName
connection = get_conn()
with connection.cursor() as cursor:
#returns all employees and the company name
query1 = """select user.firstname, user.lastname, manager.comName from user
join manager on user.username=manager.username
where user.username in
(select manager.username from manager)
and manager.comName in
(select company.comName from company where company.comName = (%s))"""
cursor.execute(query1, (i_comName))
employees = cursor.fetchall()
connection.commit()
#returns theater details for the company
query2 = "select theater.thName, user.firstname, user.lastname, theater.thCity, theater.thState, theater.capacity \
from theater join user on user.username=theater.manUsername where theater.comName=(%s)"
cursor.execute(query2, (i_comName))
theaters = cursor.fetchall()
connection.commit()
return {"ok": True, "employees": employees, "theaters": theaters}
def VisitHistory(self, username, filters):
if (filters.get("selectedCompany") == None):
i_comName = None
else:
i_comName = filters.get("selectedCompany").get("value")
if (filters.get("visitDate1") == None):
i_minVisitDate = None
else:
i_minVisitDate = dateutil.parser.parse(filters.get("visitDate1"))
if (filters.get("visitDate2") == None):
i_maxVisitDate = None
else:
i_maxVisitDate = dateutil.parser.parse(filters.get("visitDate2"))
i_username = username
connection = get_conn()
with connection.cursor() as cursor:
info = "select Theater.thName, Theater.thStreet, Theater.thCity, Theater.thState, Theater.thZipcode, Theater.comName, UserVisitTheater.visitDate\
from UserVisitTheater natural join Theater \
where (username = (%s)) and ((%s) is null or UserVisitTheater.visitDate >= (%s)) and ((%s) is null or UserVisitTheater.visitDate <= (%s)) and ((%s) is null or Theater.comName = (%s))"
cursor.execute(
info, (i_username, i_minVisitDate, i_minVisitDate,
i_maxVisitDate, i_maxVisitDate, i_comName, i_comName))
data = cursor.fetchall()
connection.commit()
connection.close()
return data
def test_logVisit(self):
user_service = UserService()
filters = {
'i_thname': 'Cinema Star',
'i_coname': '4400 Theater Company',
'i_visitdate': '2019-11-24T01:38:50.493Z'
}
date = dateutil.parser.parse(filters['i_visitdate']).date()
user_name = 'georgep'
length = 0
connection = get_conn()
with connection.cursor() as cursor:
leng = "select visitID from UserVisitTheater"
cursor.execute(leng)
length = cursor.fetchall()
connection.commit()
connection.close()
new_id = len(length) + 1
user_service.LogVisit(user_name, filters)
connection = get_conn()
with connection.cursor() as cursor:
info = """select visitID, username, thName, comName, visitDate from UserVisitTheater where
visitID=(%s) and username=(%s)
and thName=(%s)
and comName=(%s)
and visitDate=(%s)"""
cursor.execute(info, (new_id, user_name, filters['i_thname'],
filters['i_coname'], date))
data = cursor.fetchall()
connection.commit()
sql_del = """delete From UserVisitTheater where visitID = (%s)"""
cursor.execute(sql_del, (new_id))
connection.commit()
connection.close()
assert len(data) == 1
def ApproveUser(self, filters):
i_username = filters.get('i_username')
connection = get_conn()
with connection.cursor() as cursor:
query1 = "update User set status = 'Approved' where username = (%s)"
cursor.execute(query1, (i_username))
data1 = cursor.fetchall()
connection.commit()
connection.close()
def test_Decline_already_approved_User(self):
connection = get_conn()
filters = {'i_username': '******'}
admin_service = AdminService()
admin_service.DeclineUser(filters)
with connection.cursor() as cursor:
query = "select * from user where username = '******'"
cursor.execute(query)
Actual = cursor.fetchall()
connection.commit()
connection.close()
assert Actual[0]['status'] == "Approved"
def CompanyDropDown(self):
connection = get_conn()
with connection.cursor() as cursor:
#Manager-Only Registration, Manager-Customer Registration, Manage Company, Create Theater
# Explore Movie, Explore Theater, Visit History
sql = """SELECT comName FROM Company"""
cursor.execute(sql)
data = cursor.fetchall()
connection.commit()
connection.close()
return data
def DeclineUser(self, filters):
i_username = filters.get('i_username')
connection = get_conn()
with connection.cursor() as cursor:
query4 = """update User set Status = 'Declined' where status='Pending'
and username = (%s)"""
cursor.execute(query4, (i_username))
data1 = cursor.fetchall()
connection.commit()
connection.close()
def test_Decline_User(self):
db_reset()
connection = get_conn()
filters = {'i_username': '******'}
admin_service = AdminService()
admin_service.DeclineUser(filters)
with connection.cursor() as cursor:
query = "select * from user where username = '******'"
cursor.execute(query)
Actual = cursor.fetchall()
connection.commit()
connection.close()
assert Actual[0]['status'] == "Declined"
def ScheduleMovie(self, username, filters):
i_manUsername = username
i_movName = filters.get("i_movName")
i_movReleaseDate = filters.get("i_movReleaseDate")
i_movPlayDate = filters.get("i_movPlayDate")
connection = get_conn()
i_movPlayDate = dateutil.parser.parse(i_movPlayDate).date()
i_movReleaseDate = dateutil.parser.parse(i_movReleaseDate).date()
if i_movPlayDate < i_movReleaseDate:
raise Exception(
"Movie play date Cannot be scheduled before Movie Release Date"
)
with connection.cursor() as cursor:
query = cursor.execute(
"select movName, movReleaseDate from Movie"
) # movieSchedule1 is the output for Schedule Movie
movieSchedule1 = cursor.fetchall()
connection.commit()
query = "select thName, comName, capacity from Theater where manUsername = (%s)"
cursor.execute(query, (i_manUsername))
data2 = cursor.fetchall()[0]
connection.commit()
query = """SELECT count(*) as 'numScheduled' FROM MoviePlay
where thName in (SELECT thName FROM Theater where Theater.manUsername = (%s))
and comName in (SELECT comName FROM Theater where Theater.manUsername = %s)
and movPlayDate=%s;"""
cursor.execute(query,
(i_manUsername, i_manUsername, i_movPlayDate))
data = cursor.fetchall()
numScheduled = data[0]['numScheduled']
print(numScheduled, data2, ['capacity'])
if data2['capacity'] <= numScheduled:
raise Exception(
"Scheduling this movie would exceed Theater capacity")
query = "insert into MoviePlay (thName, comName, movName, movReleaseDate, movPlayDate) values ((%s), (%s), (%s), (%s), (%s))"
cursor.execute(query, (data2['thName'], data2['comName'],
i_movName, i_movReleaseDate, i_movPlayDate))
data3 = cursor.fetchall()
connection.commit()
connection.close()
def MovieDropDown(self):
connection = get_conn()
data = {}
with connection.cursor() as cursor:
query = "select movName from Movie"
cursor.execute(query)
data = cursor.fetchall()
connection.commit()
connection.close()
return data
def TheaterDropDown(self):
connection = get_conn()
with connection.cursor() as cursor:
query = """select thName from Theater
"""
cursor.execute(query)
data = cursor.fetchall()
connection.commit()
connection.close()
return data
def getCreditCardNumbers(self, username):
connection = get_conn()
data = {}
with connection.cursor() as cursor:
query = """select creditCardNum from CustomerCreditCard
where username=(%s)"""
cursor.execute(query, (username))
data = cursor.fetchall()
connection.close()
return data
def registerCustomer(self, customer) -> bool:
response = ({'message': 'Credit Card taken'}, 402)
connection = get_conn()
with connection.cursor() as cursor:
credit_card_list = customer['creditCardsList']
if len(credit_card_list) > 5:
return ({'message': 'Too Many Credit Cards'}, 402)
for i in range(5 - len(credit_card_list)):
credit_card_list.append(None)
credit_card_list = tuple(credit_card_list)
sql = """SELECT count(`creditCardNum`) FROM `CustomerCreditCard`
where creditCardNum in (%s, %s, %s, %s, %s)"""
cursor.execute(sql, credit_card_list)
dup_count = cursor.fetchall()
connection.commit()
if dup_count[0].get(('count(`creditCardNum`)')) > 0:
return ({'message': 'Credit Card taken'}, 402)
response = ({'message': 'Username Taken'}, 402)
if self.registerUser(customer):
#Inserting the values to Customer
sql = """INSERT INTO Customer (username)
VALUES (%s)"""
dataTuple = (customer['username'])
cursor.execute(sql, dataTuple)
connection.commit()
#Inserting the values to CustomerCreditCard
creditCards = customer['creditCardsList']
for creditCard in creditCards:
if (creditCard != None):
sql = """INSERT INTO CustomerCreditCard (username, creditCardNum)
VALUES (%s, %s)"""
dataTuple = (customer['username'], creditCard)
cursor.execute(sql, dataTuple)
connection.commit()
response = ({'ok': True, 'data': customer}, 200)
connection.close()
return response
def ManagerDropDown(self):
connection = get_conn()
with connection.cursor() as cursor:
query = """select firstname, lastname, Manager.username from User
inner join Manager on Manager.username = User.username
where Manager.username not in
(select manUsername from Theater)"""
cursor.execute(query)
managers = cursor.fetchall()
connection.close()
return {'ok': True, 'managers': managers}
def registerManagerCustomer(self, managerCustomer) -> bool:
connection = get_conn()
with connection.cursor() as cursor:
if managerCustomer['selectedState']['value'] == "ALL":
return ({'message': 'State Cannot be ALL'}, 400)
address = (managerCustomer['address'], managerCustomer['city'],
managerCustomer['selectedState']['value'],
managerCustomer['zipCode'])
sql = "SELECT `username` FROM `Manager` WHERE manStreet=(%s) AND manCity=(%s) AND manState=(%s) AND manZipCode=(%s)"
cursor.execute(sql, address)
userDatas = cursor.fetchall()
connection.commit()
if len(userDatas) < 1:
response = ({'message': 'Username already taken'}, 400)
if self.registerCustomer(managerCustomer)[1] == 200:
#Inserting the values to Employee
sql = """INSERT INTO Employee (username)
VALUES (%s)"""
dataTuple = (managerCustomer['username'])
cursor.execute(sql, dataTuple)
connection.commit()
#Inserting the values to Manager
sql = """INSERT INTO Manager (username, manStreet, manCity, manState, manZipCode, comName)
VALUES (%s, %s, %s, %s, %s, %s)"""
dataTuple = (managerCustomer['username'],
managerCustomer['address'],
managerCustomer['city'],
managerCustomer['selectedState']['value'],
managerCustomer['zipCode'],
managerCustomer['selectedCompany']['value'])
cursor.execute(sql, dataTuple)
connection.commit()
response = ({'ok': True, 'data': managerCustomer}, 200)
else:
response = ({'message': 'Address already taken'}, 400)
connection.close()
return response
def ViewHistory(self, username):
i_cusUsername = username
connection = get_conn()
with connection.cursor() as cursor:
query2 = "select movName, thName, comName, creditCardNum, movPlayDate \
from CustomerViewMovie \
where CustomerViewMovie.creditCardNum in (select creditCardNum from CustomerCreditCard where CustomerCreditCard.username = (%s))"
cursor.execute(query2, i_cusUsername)
data3 = cursor.fetchall()
connection.commit()
connection.close()
return data3
def ExploreMovie(self, filters):
i_movName = filters.get("i_movName")
i_comName = filters.get("i_comName")
i_city = filters.get("i_city")
i_state = filters.get("i_state")
i_minMovPlayDate = filters.get("i_minMovPlayDate")
i_maxMovPlayDate = filters.get("i_maxMovPlayDate")
if i_movName == "ALL":
i_movName = None
if i_city == "":
i_city = None
if i_state == "" or i_state == "ALL":
i_state = None
if i_comName == "ALL":
i_comName = None
data_tuple = (i_movName, i_movName, i_comName, i_comName, i_city,
i_city, i_state, i_state, i_minMovPlayDate,
i_minMovPlayDate, i_maxMovPlayDate, i_maxMovPlayDate)
connection = get_conn()
data = {}
with connection.cursor() as cursor:
query = "SELECT MoviePlay.movName, MoviePlay.movReleaseDate, MoviePlay.comName,Theater.thName, Theater.thStreet, Theater.thCity, Theater.thState, Theater.thZipcode, MoviePlay.movPlayDate\
FROM MoviePlay INNER JOIN Theater ON Theater.thName = MoviePlay.thName AND Theater.comName = MoviePlay.comName\
WHERE (MoviePlay.movName = (%s) OR (%s) is NULL) AND \
(MoviePlay.comName = (%s) OR (%s) is NULL) AND \
(Theater.thCity = (%s) OR (%s) is NULL) AND \
(Theater.thState = (%s) OR (%s) is NULL) AND \
(MoviePlay.movPlayDate >= (%s) OR (%s) is NULL) AND \
(MoviePlay.movPlayDate <= (%s) OR (%s) is NULL)"
cursor.execute(query, data_tuple)
data = cursor.fetchall()
connection.commit()
connection.close()
return data
def test_visit_history_empty(self):
filterz = {}
connection = get_conn()
with connection.cursor() as cursor:
user_service = UserService()
user_service.VisitHistory('calcwizard', filterz)
cursor.execute(
"select * from UserVisitTheater where username='******'")
data = cursor.fetchall()
connection.commit()
connection.close()
assert len(data) == 3
def test_ViewHistory(self):
connection = get_conn()
with connection.cursor() as cursor:
customer_service = CustomerService()
customer_service.ViewHistory('georgep')
cursor.execute(
"select movName, thName, comName, creditCardNum, movPlayDate from CustomerViewMovie where CustomerViewMovie.creditCardNum in (select creditCardNum from CustomerCreditCard where CustomerCreditCard.username = '******')"
)
data = cursor.fetchall()
connection.commit()
connection.close()
assert len(data) == 0
def ViewMovie(self, username, filters):
i_username = username
i_creditCardNum = filters.get("i_creditCardNum")
i_movName = filters.get("i_movName")
i_movPlayDate = filters.get("i_movPlayDate")
i_thName = filters.get("i_thName")
i_comName = filters.get("i_comName")
i_movReleaseDate = filters.get("i_movReleaseDate")
i_movPlayDate = dateutil.parser.parse(i_movPlayDate)
i_movReleaseDate = dateutil.parser.parse(i_movReleaseDate)
connection = get_conn()
with connection.cursor() as cursor:
sql = """SELECT count(*) as 'numMoviesSeen' FROM CustomerViewMovie
WHERE movPlayDate=(%s)
AND creditCardNum in (SELECT creditCardNum FROM CustomerCreditCard WHERE username in
(SELECT username FROM CustomerCreditCard Where creditCardNum=(%s)))"""
cursor.execute(sql, (i_movPlayDate, i_creditCardNum))
data = cursor.fetchall()
connection.commit()
numMoviesSeen = data[0]["numMoviesSeen"]
if (numMoviesSeen >= 3):
connection.close()
return "Could not watch that movie, too many movies already viewed that day"
movReleaseDate = i_movReleaseDate
query2 = """insert into CustomerViewMovie (creditCardNum, thName, comName, movName, movReleaseDate, movPlayDate)
values ((%s), (%s), (%s), (%s), (%s), (%s))"""
print((i_creditCardNum, i_thName, i_comName, i_movName,
movReleaseDate, i_movPlayDate))
cursor.execute(query2, (i_creditCardNum, i_thName, i_comName,
i_movName, movReleaseDate, i_movPlayDate))
data3 = cursor.fetchall()
connection.commit()
connection.close()
return None
def test_ViewMovie(self):
db_reset()
customer_service = CustomerService()
filters = {
'i_thName': "ML Movies",
'i_comName': "AI Theater Company",
'i_movName': 'Spider-Man: Into the Spider-Verse',
'i_movPlayDate': 'Mon Sep 30 2019',
'i_creditCardNum': "1111111111110000"
}
date = dateutil.parser.parse(filters['i_movPlayDate']).date()
print(date)
user_name = 'georgep'
length = 0
customer_service.ViewMovie(user_name, filters)
connection = get_conn()
# with connection.cursor() as cursor:
# info = """select visitID, username, thName, comName, visitDate from UserVisitTheater where
# visitID=(%s) and username=(%s)
# and thName=(%s)
# and comName=(%s)
# and visitDate=(%s)"""
# cursor.execute(info, (new_id, user_name, filters['i_thname'], filters['i_coname'], date))
# data=cursor.fetchall()
# connection.commit()
# connection = get_conn()
with connection.cursor() as cursor:
leng = """select creditCardNum from CustomerViewMovie where creditCardNum='1111111111110000'"""
cursor.execute(leng)
length = cursor.fetchall()
connection.commit()
connection.close()
assert len(length) == 1
assert length[0]['creditCardNum'] == '1111111111110000'
def test_visit_history(self):
filterz = {
'i_comName': '4400 Theater Company',
'i_minVisitDate': datetime.date(2005, 11, 25),
'i_maxVisitDate': datetime.date(2012, 11, 30)
}
connection = get_conn()
with connection.cursor() as cursor:
user_service = UserService()
user_service.VisitHistory('imready', filterz)
cursor.execute(
"select * from UserVisitTheater where username='******'")
data = cursor.fetchall()
connection.commit()
connection.close()
assert len(data) == 1
def ExploreTheater(self, filters):
i_thname = filters.get("selectedTheater")
i_coname = filters.get("selectedCompany")
i_city = filters.get("city")
i_state = filters.get("selectedState")
if i_city == "":
i_city = None
if i_state == "ALL":
i_state = None
if i_thname == "ALL":
i_thname = None
if i_coname == "ALL":
i_coname = None
data_tuple = (i_thname, i_thname, i_coname, i_coname, i_city, i_city,
i_state, i_state)
print(data_tuple)
connection = get_conn()
with connection.cursor() as cursor:
info = """select distinct thName , thStreet , thCity , thState , thZipcode , comName from
Theater where ((%s) is NULL or thName = (%s))
and ((%s) is NULL or comName = (%s))
and ((%s) is NULL or thCity = (%s))
and ((%s) is NULL or thState = (%s))"""
cursor.execute(info, data_tuple)
data = cursor.fetchall()
connection.commit()
connection.close()
print(data)
return data
def test_CreateMovie(self):
connection = get_conn()
with connection.cursor() as cursor:
sql_del = """delete from Movie where movName = "4400 The Movie" and movReleaseDate = '20190812' """
cursor.execute(sql_del)
connection.commit()
admin_service = AdminService()
admin_service.CreateMovie('cool_class4400')
cursor.execute(
"insert into Movie (movName,movReleaseDate,duration) values (('4400 The Movie', '20190812', 130)"
)
data = cursor.fetchall()
connection.commit()
sql_del = """delete from Movie where movName = "4400 The Movie" and movReleaseDate = '20190812' """
cursor.execute(sql_del)
connection.commit()
connection.close()
assert len(data) == 1
def TheaterOverview(self, username, filters):
i_username = username
i_minReleaseDate = filters.get("i_minReleaseDate")
i_maxReleaseDate = filters.get("i_maxReleaseDate")
i_minPlayDate = filters.get("i_minPlayDate")
i_maxPlayDate = filters.get("i_maxPlayDate")
i_minDuration = filters.get("i_minDuration")
i_maxDuration = filters.get("i_maxDuration")
i_Movie = filters.get("i_Movie")
i_notplayed = filters.get("i_notplayed")
if i_minDuration == "":
i_minDuration = None
if i_maxDuration == "":
i_maxDuration = None
data_tuple = (
i_username, # line 61
i_minReleaseDate, # line 62
i_minReleaseDate,
i_maxReleaseDate, # line 63
i_maxReleaseDate,
i_maxPlayDate, # line 64
i_maxPlayDate,
i_minPlayDate, # line 65
i_minPlayDate,
i_maxDuration, # line 66
i_maxDuration,
i_minDuration, # line 67
i_minDuration,
i_Movie, # line 68
i_Movie,
i_notplayed, # line 69
i_notplayed,
i_username, #line 76
i_minReleaseDate, # line 77
i_minReleaseDate,
i_maxReleaseDate, # line 78
i_maxReleaseDate,
i_minDuration, # line 79
i_minDuration,
i_maxDuration, # line 80
i_maxDuration,
i_Movie,
i_Movie)
connection = get_conn()
with connection.cursor() as cursor:
info = """select distinct MoviePlay.movName as "Movie", MoviePlay.movReleaseDate as "Release_Date",
MoviePlay.movPlayDate as "Play_Date", Movie.duration as "Duration"
from MoviePlay join Movie on MoviePlay.movName = Movie.movName where MoviePlay.thName in
(select thName from Theater where Theater.manUsername = %s)
and (%s is NULL or Movie.movReleaseDate >= %s)
and (%s is NULL or Movie.movReleaseDate <= %s)
and (%s is NULL or MoviePlay.movPlayDate <= %s)
and (%s is NULL or MoviePlay.movPlayDate >= %s)
and (%s is NULL or Movie.duration <= %s)
and (%s is NULL or Movie.duration >= %s)
and (%s ="" or Movie.movName like %s)
and (%s is NULL or %s=False or MoviePlay.movPlayDate != NULL)
Union
select Movie.movName as "movName", Movie.movReleaseDate as "movReleaseDate",
cast(NULL as date) as "movPlayDate", Movie.duration as "movDuration" from Movie
where Movie.movName not in
(select MoviePlay.movName from MoviePlay where MoviePlay.thName in
(select thName from Theater where Theater.manUsername = %s))
and (%s is NULL or Movie.movReleaseDate >= %s)
and (%s is NULL or Movie.movReleaseDate <= %s)
and (%s is NULL or Movie.duration >= %s)
and (%s is NULL or Movie.duration <= %s)
and (%s ="" or Movie.movName like %s);"""
cursor.execute(info, data_tuple)
data = cursor.fetchall()
connection.commit()
connection.close()
return data
