forked from itucsdb1616/itucsdb1616
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathlist.py
169 lines (158 loc) · 7.57 KB
/
list.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
from flask import current_app
import psycopg2 as dbapi2
from flask_login import current_user
from twit import Twit
class List:
def __init__(self,name,creatorname):
self.name=name
self.creatorname=creatorname
connection = dbapi2.connect(current_app.config['dsn'])
cursor = connection.cursor()
cursor.execute("""SELECT ID FROM USERS WHERE USERNAME=%s""",(creatorname,))
temp=cursor.fetchone()
self.creatorid=temp[0]
cursor.close()
connection.close()
def addSubscriber(self,membername):
connection = dbapi2.connect(current_app.config['dsn'])
cursor = connection.cursor()
cursor.execute("""SELECT LISTID FROM LISTS WHERE NAME=%s AND CreatorID=%s""",(self.name,self.creatorid))
temp=cursor.fetchone()
listid=temp[0]
cursor.close()
cursor=connection.cursor()
cursor.execute("""SELECT ID FROM USERS WHERE USERNAME=%s""",(membername,))
temp=cursor.fetchone()
memberid=temp[0]
cursor.execute("""INSERT INTO LISTMEMBERS (LISTID,USERID,USERTYPE) VALUES (%s, %s,%s)""", (listid,memberid,'Subscriber'))
cursor.execute("""UPDATE LISTS SET MEMBERS=MEMBERS+1 WHERE LISTID =%s""",(listid,))
connection.commit()
cursor.close()
connection.close()
return
def addInsider(self,membername):
try:
connection = dbapi2.connect(current_app.config['dsn'])
cursor = connection.cursor()
cursor.execute("""SELECT LISTID FROM LISTS WHERE NAME=%s AND CreatorID=%s""",(self.name,self.creatorid))
temp=cursor.fetchone()
listid=temp[0]
cursor.close()
cursor=connection.cursor()
cursor.execute("""SELECT ID FROM USERS WHERE USERNAME=%s""",(membername,))
temp1=cursor.fetchone()
memberid=temp1[0]
cursor.execute("""INSERT INTO LISTMEMBERS (LISTID,USERID,USERTYPE) VALUES (%s, %s,%s)""", (listid,memberid,'Insider'))
cursor.execute("""UPDATE LISTS SET MEMBERS=MEMBERS+1 WHERE LISTID =%s""",(listid,))
connection.commit()
cursor.close()
connection.close()
return 1
except:
return 0
def deleteSubscriber(self, membername):
connection=dbapi2.connect(current_app.config['dsn'])
cursor=connection.cursor()
cursor.execute("""SELECT ID FROM USERS WHERE USERNAME=%s""",(membername,))
tempmemberid=cursor.fetchone()
memberid=tempmemberid[0]
cursor.close()
cursor=connection.cursor()
cursor.execute("""SELECT LISTID FROM LISTS WHERE NAME=%s AND CREATORID=%s""",(self.name,self.creatorid))
temp=cursor.fetchone()
listid=temp[0]
cursor.execute("""DELETE FROM LISTMEMBERS WHERE LISTID = %s AND USERID =%s AND USERTYPE = %s""",(listid,memberid,'Subscriber'))
cursor.execute("""UPDATE LISTS SET MEMBERS=MEMBERS-1 WHERE LISTID =%s""",(listid,))
connection.commit()
cursor.close()
connection.close()
return
def deleteInsider(self,membername):
connection=dbapi2.connect(current_app.config['dsn'])
cursor=connection.cursor()
cursor.execute("""SELECT ID FROM USERS WHERE USERNAME=%s""",(membername,))
tempmemberid=cursor.fetchone()
memberid=tempmemberid[0]
cursor.close()
cursor=connection.cursor()
cursor.execute("""SELECT LISTID FROM LISTS WHERE NAME=%s AND CREATORID=%s""",(self.name,self.creatorid))
temp=cursor.fetchone()
listid=temp[0]
cursor.execute("""DELETE FROM LISTMEMBERS WHERE LISTID = %s AND USERID =%s AND USERTYPE""",(listid,memberid,'Insider'))
cursor.execute("""UPDATE LISTS SET MEMBERS=MEMBERS-1 WHERE LISTID =%s""",(listid,))
connection.commit()
cursor.close()
connection.close()
return
def updateName(self,newName):
connection = dbapi2.connect(current_app.config['dsn'])
cursor = connection.cursor()
cursor.execute("""UPDATE LISTS SET NAME=%s WHERE NAME=%s AND CREATORID=%s""",(newName,self.name,self.creatorid) )
self.name=newName
connection.commit()
cursor.close()
connection.close()
def getTweets(self):
connection=dbapi2.connect(current_app.config['dsn'])
cursor=connection.cursor()
cursor.execute("""SELECT LISTID FROM LISTS WHERE NAME=%s AND CreatorID=%s""",(self.name,self.creatorid))
temp=cursor.fetchone()
listid=temp[0]
cursor.close()
cursor=connection.cursor()
cursor.execute("""SELECT TWEETID,R.USERID,CONTEXT,TITLE,NUMBEROFLIKES,NUMBEROFRTS,isRT,RTOWNERID,
TWEETOWNERNAME,RTOWNERNAME FROM (SELECT USERNAME,USERID FROM USERS JOIN LISTMEMBERS ON USERS.ID=LISTMEMBERS.USERID
WHERE ((USERTYPE='Insider' OR USERTYPE='Owner') AND LISTID=%s )) AS R JOIN
(SELECT TWEETID,USERID,CONTEXT,TITLE,NUMBEROFLIKES,NUMBEROFRTS,isRT,RTOwnerID,U1.USERNAME AS TWEETOWNERNAME,
U2.USERNAME AS RTOWNERNAME FROM TWEETS
JOIN USERS AS U1 ON U1.ID=TWEETS.USERID JOIN USERS AS U2 ON U2.ID=TWEETS.RTOWNERID ) AS Y
ON R.USERID=Y.USERID
UNION
SELECT TWEETID,E.USERID,CONTEXT,TITLE,NUMBEROFLIKES,NUMBEROFRTS,isRT,RTOWNERID,
TWEETOWNERNAME,RTOWNERNAME
FROM
(SELECT USERNAME,USERID FROM USERS JOIN LISTMEMBERS ON USERS.ID=LISTMEMBERS.USERID
WHERE ((USERTYPE='Insider' OR USERTYPE='Owner') AND LISTID=%s )) AS E
JOIN
(SELECT TWEETID,USERID,CONTEXT,TITLE,NUMBEROFLIKES,NUMBEROFRTS,isRT,RTOwnerID,U3.USERNAME AS TWEETOWNERNAME,U4.USERNAME AS RTOWNERNAME FROM TWEETS
JOIN USERS AS U3 ON U3.ID=TWEETS.USERID JOIN USERS AS U4 ON U4.ID=TWEETS.RTOWNERID ) AS Z
ON E.USERID=Z.RTOwnerID
ORDER BY TWEETID DESC""",(listid,listid))
twit = [(Twit(title, context, twitid, userhandle, numberoflikes, numberofrts, isrt, rtowner))
for twitid,userid,context,title, numberoflikes,numberofrts,isrt, rtownerid,userhandle,rtowner in cursor]
cursor.close()
connection.close()
return twit
def getSubscribers(self):
connection=dbapi2.connect(current_app.config['dsn'])
cursor=connection.cursor()
cursor.execute("""SELECT LISTID FROM LISTS WHERE NAME = %s AND CREATORID=%s""",(self.name,self.creatorid) )
temp=cursor.fetchone()
listid=temp[0]
cursor.execute("""SELECT USERNAME FROM LISTMEMBERS JOIN USERPROFILE ON LISTMEMBERS.ID = USERPROFILE.ID WHERE LISTID=%s AND USERTYPE=%s""",(listid,'Subscriber'))
subscriberarray=[member[0] for member in cursor.fetchall()]
connection.commit()
cursor.close()
connection.close()
return subscriberarray
def isSubscriber(self,membername):
connection=dbapi2.connect(current_app.config['dsn'])
cursor=connection.cursor()
cursor.execute("""SELECT ID FROM USERS WHERE USERNAME=%s""",(membername,))
tempmemberid=cursor.fetchone()
memberid=tempmemberid[0]
cursor.close()
cursor=connection.cursor()
cursor.execute("""SELECT LISTID FROM LISTS WHERE NAME=%s AND CreatorID=%s""",(self.name,self.creatorid))
temp=cursor.fetchone()
if temp is None:
print("bigif")
return 0
else:
listid=temp[0]
cursor.execute("""SELECT USERID FROM LISTMEMBERS WHERE USERID=%s AND LISTID=%s AND USERTYPE=%s """,(memberid,listid,'Subscriber'))
temp2=cursor.fetchone()
if temp2 is None:
return 0
else:
return 1