moodleNotifer
subjectdatabase.py
1 import sqlite3
2 from .login import *
3 
4 from datetime import datetime
5 from pytz import timezone
6 import pytz
7 
8 # TABLE_NAME = "studentdetails"
9 # STUDENT_ID = "student_roll_no"
10 # STUDENT_TOCKEN="student_tocken"
11 # STUDENT_NAME = "student_name"
12 # SUBJECT = "subjects"
13 # STUDENT_PHONE = "student_phone"
14 
15 
16 SUBJECT_ID="subject_id"#1
17 SUBJECT_NAME="subject_name"#2
18 SUBJECT_MID="subject_mid"#3
19 ASSIGNMENT_NAME="assignment_name"#4
20 AUPLOAD_ON="aupload_on"#5
21 ADUE_DATE="adue_date"#6
22 AINTRO="aintro"#7
23 AATTACHMENTS_FILE_NAME="aattachments_file_name"#8
24 
25 
26 QUIZ_NAME="quiz_name"#9
27 QUPLOAD_ON="qupload_on"#10
28 QDUE_DATE="qdue_date"#11
29 GRADE="grade"#12
30 
31 
32 ANNOUNCENMENTS="announcements_id"#13
33 ANNAMES1="annames1"#14
34 ANUPLOAD_ON1="aNupload_on1"#15
35 ANINTRO1="anintro1"#16
36 ANNAMES2="annames2"#17
37 ANUPLOAD_ON2="aNupload_on2"#18
38 ANINTRO2="anintro2"#19
39 ANNAMES3="annames3"#20
40 ANUPLOAD_ON3="aNupload_on3"#21
41 ANINTRO3="anintro3"#22
42 ANNAMES4="annames4"#23
43 ANUPLOAD_ON4="aNupload_on4"#24
44 ANINTRO4="anintro4"#25
45 ANNAMES5="annames5"#26
46 ANUPLOAD_ON5="aNupload_on5"#27
47 ANINTRO5="anintro5"#28
48 
49 
50 LATEST_COURSE_CONTENTS="latest_course_contents"#29
51 
52 
53 connection=sqlite3.Connection("management.db")
54 cursor=connection.cursor()
55 
56 
59 
60 class moodle:
61  def ___init__(self):
62  pass
63  def create_table(self):
64  connection=sqlite3.Connection("management.db")
65  cursor=connection.cursor()
66  connection.execute(" CREATE TABLE IF NOT EXISTS moodle_data"+ " ( " + SUBJECT_ID +
67  " INTEGER PRIMARY KEY, " +
68  SUBJECT_NAME + " TEXT, " +
69  SUBJECT_MID + " INTEGER, " + ASSIGNMENT_NAME + " TEXT, "
70  + AUPLOAD_ON + " INTEGER, " + ADUE_DATE + " INTEGER, "
71  + AINTRO + " TEXT, " + AATTACHMENTS_FILE_NAME + " TEXT, "
72  + QUIZ_NAME + " TEXT, "
73  + QUPLOAD_ON + " INTEGER, "+ QDUE_DATE + " INTEGER, "+ GRADE + " INTEGER, "
74  + ANNOUNCENMENTS+ " INTEGER, "+ ANNAMES1 + " TEXT, "+ ANUPLOAD_ON1 + " INTEGER, "
75  + ANINTRO1 + " TEXT, "+ ANNAMES2 + " TEXT, "+ ANUPLOAD_ON2 + " INTEGER, "+ ANINTRO2
76  + " TEXT, "+ ANNAMES3 + " TEXT, "+ ANUPLOAD_ON3 + " INTEGER, "+ ANINTRO3 + " TEXT, "
77  + ANNAMES4 + " TEXT, "+ ANUPLOAD_ON4 + " INTEGER, "+ ANINTRO4 + " TEXT, "
78  + ANNAMES5 + " TEXT, "+ ANUPLOAD_ON5 + " INTEGER, "+ ANINTRO5 + " TEXT, "+ LATEST_COURSE_CONTENTS + " TEXT)")
79 
80  def insert(self,subject_id,subject_name):
81  connection=sqlite3.Connection("management.db")
82  cursor=connection.cursor()
83  cursor.execute("INSERT INTO moodle_data VALUES("+str(subject_id)+",'"+subject_name+"',0,'null',0,0,'null','null', 'null',0,0,0 ,0,'null',0,'null','null',0,'null','null',0,'null','null',0,'null','null',0,'null','null')")
84  connection.commit()
85 
86  def update(self,row,column,dictt):
87  connection=sqlite3.Connection("management.db")
88  cursor=connection.cursor()
89  if(column==1):#row is the subid dict is submid
90 
91  cursor.execute("UPDATE moodle_data SET subject_mid="+str(dictt)+ " WHERE subject_id="+str(row))
92  connection.commit()
93  if(column==2):#assignment row is subid dict is dict of assignment
94  i=dictt
95  if not i:
96  return
97  t2=i["name"]
98  t3=i["timemodified"]
99  t4=i["duedate"]
100  t5=i["intro"]
101  t6=i["introattachments"]
102  t7=""
103  if(len(t6)!=0):
104 
105  for k in t6:
106  t7=t7+k["filename"]+"\n"
107  t7=t7+k["fileurl"]+"\n"
108  cursor.execute("UPDATE moodle_data SET assignment_name="+"?"+" , aupload_on="+"?"
109  +" , adue_date="+"?"+" , aintro="+"?" +" , aattachments_file_name="+"?" +" WHERE subject_id="+"?",(t2,t3,t4,t5,t7,row))
110  connection.commit()
111  if(column==3):#
112  i=dictt
113  if not i:
114  return
115  t2=i["name"]
116  t3=i["timeopen"]
117  t4=i["timeclose"]
118  t5=i["grade"]
119  cursor.execute("UPDATE moodle_data SET quiz_name="+"?"+" , qupload_on="+"?"+
120  " , qdue_date="+"?"+" , grade="+"?" +" WHERE subject_id="+"?",(t2,t3,t4,t5,row))
121  connection.commit()
122  if(column==4):#annoncements
123  cursor.execute("UPDATE moodle_data SET announcements_id="+str(dictt["id"])+ " WHERE subject_id="+str(row))
124  connection.commit()
125  if(column==6):#latest course contents
126  t7=""
127  t7=t7+dictt["name"]+"\n"
128  dictt=dictt["modules"]
129  for k in dictt:
130  t7=t7+k["name"]+"\n"
131  try:
132  t7=t7+k["fileurl"]+"\n"
133  except KeyError:
134  pass
135  cursor.execute("UPDATE moodle_data SET latest_course_contents="+"?"+ " WHERE subject_id="+"?",(t7,row))
136  connection.commit()
137  if(column==5):
138  n=0
139  for k in dictt:
140  n+=1
141  t2=k["name"]
142  t3=k["created"]
143  t4=k["message"]
144  cursor.execute("UPDATE moodle_data SET annames%s=" %n+"?"+" , anupload_on%s=" %n+"?"+" , anintro%s=" %n+"?"+" WHERE subject_id=?",(t2,t3,t4,row))
145  connection.commit()
146  def printdb(self):
147  connection=sqlite3.Connection("management.db")
148  cursor=connection.cursor()
149  rows = cursor.execute("SELECT subject_id, subject_name,subject_mid,assignment_name,aupload_on,adue_date,aintro,aattachments_file_name,quiz_name,qupload_on,qdue_date,grade,announcements_id,annames1,anupload_on1,anintro1,annames2,anupload_on2,anintro2,annames3,anupload_on3,anintro3,annames4,anupload_on4,anintro4,annames5,anupload_on5,anintro5,latest_course_contents FROM moodle_data").fetchall()
150  print(rows)
151 
152  def get_courseid(self,sub_id):
153  connection=sqlite3.Connection("management.db")
154  cursor=connection.cursor()
155  rows = cursor.execute("SELECT subject_mid, announcements_id FROM moodle_data WHERE subject_id = " + sub_id).fetchall()
156  return rows[0][0]
157 
158  def get_all_courses(self):
159  self.create_table()
160  connection=sqlite3.Connection("management.db")
161  cursor=connection.cursor()
162  rows = cursor.execute("SELECT subject_id FROM moodle_data").fetchall()
163  l = []
164  for i in rows:
165  l.append(str(i[0]))
166  return l
167 
168  def delete_sub(self):
169  connection=sqlite3.Connection("management.db")
170  cursor=connection.cursor()
171  cursor.execute("DELETE FROM moodle_data WHERE subject_id = 725")
172  connection.commit()
173 
174 
175  def assignment_details(self,subid):
176  connection=sqlite3.Connection("management.db")
177  cursor=connection.cursor()
178  rows = cursor.execute("SELECT assignment_name,adue_date,aintro,aattachments_file_name FROM moodle_data WHERE subject_id = " + subid).fetchall()
179  s = ""
180 
181  j = 0
182  for i in rows[0]:
183  if(j == 1):
184  i = "Due Date : " + getDate(i)
185  if(j == 2):
186  i = "Details : " + i
187 
188  if(j == 3):
189  i = "PDFs/URLs : " + i
190  s = s+str(i)+"\n"
191  j += 1
192  return s
193 
194  def quiz_details(self,subid):
195  connection=sqlite3.Connection("management.db")
196  cursor=connection.cursor()
197  rows = cursor.execute("SELECT quiz_name,qdue_date, grade FROM moodle_data WHERE subject_id = "+ subid).fetchall()
198  s = ''
199  j = 0
200  for i in rows[0]:
201  if(j == 1):
202  i = "Due Date : " + getDate(i)
203 
204  if(j == 2):
205  i = "Grade : " + str(i)
206  s = s+str(i)+"\n"
207  j += 1
208  return s
209 
210  def course_contents(self, subid):
211  connection=sqlite3.Connection("management.db")
212  cursor=connection.cursor()
213  rows = cursor.execute("SELECT latest_course_contents FROM moodle_data WHERE subject_id = "+ subid).fetchall()
214  s=''
215  for i in rows[0]:
216  s=s+str(i)+"\n"
217  return s
218 
219  def get_announcements(self,subid):
220  connection=sqlite3.Connection("management.db")
221  cursor=connection.cursor()
222  rows = cursor.execute("SELECT annames1, aNupload_on1 , anintro1 , annames2 , aNupload_on2 , anintro2 FROM moodle_data WHERE subject_id = "+ subid).fetchall()
223  s=''
224  j = 0
225  for i in rows[0]:
226  if(j ==1):
227  i = "Date : " + getDate(i)
228  if(j == 4):
229  i = "Date : " + getDate(i)
230  s=s+str(i)+"\n"
231  j += 1
232  return s
233 
234 def getDate(unixdate):
235  ts = int(unixdate)
236  return str(datetime.utcfromtimestamp(ts).astimezone(timezone('Asia/Kolkata')).strftime('%Y-%m-%d'))
This is used for maintaining the Student moodle data.