def format_Media():
cmd = """
SELECT
m.ID
, m.FullName
, mc.Name
, mt.Description
, CASE m.LongGame
WHEN 1
THEN 'Long'
ELSE 'Short'
END as Length
, CASE (
SELECT COUNT(0) as numberOut
FROM Transactions as t
WHERE
t.MediaID = m.ID
AND t.CheckIN is null
)
WHEN 0
THEN 'Available'
ELSE 'Checked Out'
END as Available
FROM Media as m
JOIN
MediaCategory as mc
ON m.MediaCategory = mc.ID
, MediaType as mt
ON m.MediaType = mt.ID;
"""
return sql.GET(cmd)
def getAvalableByType(mediaType):
pass
cmd = """
SELECT
m.ID
, m.FullName
, mc.Name
, mt.Description
, CASE m.LongGame
WHEN 1
THEN 'Long'
ELSE 'Short'
END as Length
FROM Media as m
JOIN
MediaCategory as mc
ON m.MediaCategory = mc.ID
, MediaType as mt
ON m.MediaType = mt.ID
WHERE (
SELECT COUNT(0) as numberOut
FROM Transactions as t
WHERE
t.MediaID = m.ID
AND t.CheckIN is null
) = 0
AND mt.Description like '{}';
""".format(mediaType)
try:
fin = sql.GET(cmd)
except:
fin = False
return fin
def getPets(): # returns the amount of love Snek gets
cmd = """
SELECT
aStatus, COUNT(*) as Amount
FROM
Interactions
GROUP BY
aStatus
ORDER BY
Amount DESC;
"""
results = sql.GET(cmd)
return results
def imSorry(): # an appology for the environment in which we operate
sqlCmd = "SELECT Link FROM Music;"
results = sql.GET(sqlCmd)
return results
# CREATE TABLE IF NOT EXISTS "Users" (
# 'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
# 'SlackID' TEXT NOT NULL DEFAULT 'NOID',
# 'UserName' TEXT NOT NULL DEFAULT 'NONAME'
# );
####
def isItemCheckedOut(mediaID):
cmd = """
SELECT COUNT(0) as numberOut
FROM Transactions as t
WHERE
t.MediaID = {0}
AND t.CheckIN is null;
""".format(mediaID)
numOut = sql.GET(cmd)[0][0]
if int(numOut) >= 1: # can't check ou an item twice... I hope
return True
return False
def tooManyOut(slackID):
cmd = """
SELECT COUNT(0) as numberOut
FROM Transactions as t
WHERE
t.SlackID = '{0}'
AND t.CheckIN is null;
""".format(slackID)
numOut = sql.GET(cmd)[0][0]
if numOut >= 2: # if they have 2 or more items out, they'll need to talk to an admin
return True
return False
def returnMedium(medium):
result = """
SELECT *
FROM
Media
WHERE
MediaType = {0};
""".format(medium)
try:
fin = sql.GET(result)
except:
fin = 0
return fin
def getSlackName(id):
cmd = """
SELECT
ID
FROM
Users
WHERE
slackID LIKE '{0}'
""".format(id)
try:
fin = sql.GET(cmd)[0][0]
except:
fin = 'No ID'
return fin
def isDirect(channelID):
result = """
SELECT
*
FROM
Users
WHERE
directID = '{0}';
""".format(channelID)
try:
fin = sql.GET(result)[0][0]
except:
fin = 0
return fin
def getMediaNameByID(ID):
result = """
SELECT
FullName
FROM
Media
WHERE
ID = {0};
""".format(ID)
try:
fin = sql.GET(result)[0][0]
except:
fin = 0
return fin
def get_MediaCategoryID(mediaType):
cmd = """
SELECT
ID
FROM
MediaCategory
WHERE
Name LIKE '{}';
""".format(mediaType)
try:
result = sql.GET(cmd)[0][0]
except:
result = 1 # defaults to 1, undefined
return result
def getMediaCategoryByID(ID):
result = """
SELECT
Name
FROM
MediaCategory
WHERE
ID = {0};
""".format(ID)
try:
fin = sql.GET(result)[0][0]
except:
fin = 0
return fin
def isAdmin(slackID):
result = """
SELECT
IsAdmin
FROM
Users
WHERE
slackID = '{0}';
""".format(slackID)
try:
fin = sql.GET(result)[0][0]
except:
fin = 0
return fin
def get_MediaTypeID(mediaType):
cmd = """
SELECT
ID
FROM
MediaType
WHERE
Description LIKE '{}';
""".format(mediaType)
try:
result = sql.GET(cmd)[0][0]
except:
result = 1 # defaults to 1, undefined
return result
def getMediaTypeByID(ID):
result = """
SELECT
Description
FROM
MediaType
WHERE
ID = {0};
""".format(ID)
try:
fin = sql.GET(result)[0][0]
except:
fin = 0
return fin
def getFactByID(ID):
result = """
SELECT
Line
FROM
Facts
WHERE
ID = {0};
""".format(ID)
try:
fin = sql.GET(result)[0][0]
except:
fin = 0
return fin
def getSlackID(name):
cmd = """
SELECT
slackID
FROM
Users
WHERE
userName LIKE '{0}'
""".format(name)
try:
fin = sql.GET(cmd)[0][0]
except:
fin = 'No ID'
return fin
def mikeReport(
date1, date2
): # Gets the time, VM number, and status reported across a date range
cmd = (("""
SELECT
TimeStamp
, ServerNumber
, ServerStatus
FROM
Issues
WHERE
date(TimeStamp) BETWEEN '{0}' AND '{1}'
AND ServerNumber IN (1, 2, 3, 4, 17, 40, 46, 47, 48 ,49) ;
""").format(date1, date2))
results = sql.GET(cmd)
return results
def singleDayReport(
aDate): # Gets a daily summary of the VM number and status reported
cmd = (("""
SELECT
ServerNumber
, ServerStatus
, count(ServerStatus)
FROM
Issues
WHERE
date(TimeStamp) IN ('{0}')
AND ServerNumber IN (1, 2, 3, 4, 17, 40, 46, 47, 48 ,49)
GROUP BY
ServerNumber
, ServerStatus;
""").format(aDate))
results = sql.GET(cmd)
return results
def multiDayReport(
date1,
date2): # Gets a range summary of the VM number and status reported
cmd = (("""
SELECT
ServerNumber
, ServerStatus
, count(ServerStatus) as AMT
FROM
Issues
WHERE
date(TimeStamp) BETWEEN '{0}' AND '{1}'
AND ServerNumber IN (1, 2, 3, 4, 17, 40, 46, 47, 48 ,49)
GROUP BY
ServerNumber
,ServerStatus
""").format(date1, date2))
results = sql.GET(cmd)
return results
def getMyStuff(SlackID):
cmd = """
SELECT t.MediaID
,m.fullName
, t.checkOUT
FROM Transactions as t
JOIN Media as m
ON m.ID = t.mediaID
WHERE
t. SlackID = '{}'
AND t.checkIN is null;
""".format(SlackID)
try:
fin = sql.GET(cmd)
except:
fin = "You haven't checked anything out!"
return fin
def reportCount(date1, date2):
cmd = (("""
SELECT
SUM(AMT)
FROM
(SELECT
ServerNumber
, ServerStatus
, count(ServerStatus) as AMT
FROM
Issues
WHERE
date(TimeStamp) BETWEEN '{0}' AND '{1}'
AND ServerNumber IN (1, 2, 3, 4, 17, 40, 46, 47, 48 ,49)
GROUP BY
ServerNumber
,ServerStatus
) src;
""").format(date1, date2))
results = sql.GET(cmd)
return results[0][0]
def format_Media_WhosGotIt():
cmd = """
SELECT
m.ID
, m.FullName
, t.slackID
, t.checkOUT
FROM Media as m JOIN
(SELECT t.mediaID
, t.checkout
, t.slackID
FROM Transactions as t
WHERE
t.CheckIN is null
) as t
ON m.ID = t.mediaID
JOIN
Users as u
ON u.slackID = t.slackID
ORDER BY m.ID ASC;
"""
return sql.GET(cmd)
def garyReport(
date1, date2
): # Gets the time, VM number, and status reported across a date range
cmd = (("""
SELECT
date(Week)
, ServerStatus
, count(0) as probs
FROM (
SELECT datetime([TimeStamp], 'start of day', 'weekday 1', '-7 day') as Week
, ServerStatus
FROM Issues
) as t
WHERE
Week BETWEEN '{0}' AND '{1}'
GROUP BY
Week, ServerStatus
ORDER BY
Week, ServerStatus;
""").format(date1, date2))
results = sql.GET(cmd)
return results
