Lecturfier/transfer.py at master · markbeep/Lecturfier · GitHub

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
import sqlite3

conn = sqlite3.connect("./data/discord.db")

cols = [
"MessagesSent",
"MessagesDeleted",
"MessagesEdited",
"CharactersSent",
"WordsSent",
"SpoilersSent",
"EmojisSent",
"FilesSent",
"FileSizeSent",
"ImagesSent",
"ReactionsAdded",
"ReactionsRemoved",
"ReactionsReceived",
"ReactionsTakenAway",
"VoteCount"
]

exec_str = f"SELECT UniqueMemberID, {','.join([f'SUM({x})' for x in cols])} FROM UserStatistics GROUP BY UniqueMemberID;"
print(exec_str)
data = conn.execute(exec_str).fetchall()

# create new table
conn.execute("""CREATE TABLE IF NOT EXISTS "UserStatisticsTEMP" (
	"UniqueMemberID"	integer NOT NULL,
	"MessagesSent"	integer DEFAULT 0,
	"MessagesDeleted"	integer DEFAULT 0,
	"MessagesEdited"	integer DEFAULT 0,
	"CharactersSent"	integer DEFAULT 0,
	"WordsSent"	integer DEFAULT 0,
	"SpoilersSent"	integer DEFAULT 0,
	"EmojisSent"	integer DEFAULT 0,
	"FilesSent"	integer DEFAULT 0,
	"FileSizeSent"	integer DEFAULT 0,
	"ImagesSent"	integer DEFAULT 0,
	"ReactionsAdded"	integer DEFAULT 0,
	"ReactionsRemoved"	integer DEFAULT 0,
	"ReactionsReceived"	integer DEFAULT 0,
	"ReactionsTakenAway"	integer DEFAULT 0,
	"VoteCount"	integer DEFAULT 0,
	FOREIGN KEY("UniqueMemberID") REFERENCES "DiscordMembers",
	PRIMARY KEY("UniqueMemberID")
);""")

exec_str = f"INSERT INTO UserStatisticsTEMP(UniqueMemberID, {','.join(cols)}) VALUES(?,{','.join(['?' for _ in cols])});"
print(exec_str)
conn.executemany(
    exec_str,
    data
)

conn.execute("DROP TABLE UserStatistics;")
conn.execute("ALTER TABLE UserStatisticsTEMP RENAME TO UserStatistics;")


# subjects/courses
Courses = """        CREATE TABLE IF NOT EXISTS Courses (
                        CourseId INTEGER PRIMARY KEY AUTOINCREMENT,
                        Abbreviation TEXT NOT NULL,
                        GuildId INTEGER NOT NULL,
                        Name TEXT NOT NULL,
                        DiscordRoleId INTEGER NOT NULL,
                        DiscordChannelId INTEGER NOT NULL,
                        Link TEXT,
                        UNIQUE (Abbreviation, GuildId)
                        );"""

Lectures = """       CREATE TABLE IF NOT EXISTS Lectures (
                        LectureId INTEGER PRIMARY KEY AUTOINCREMENT,
                        CourseId INTEGER NOT NULL,
                        DayId INTEGER NOT NULL,
                        HourFrom INTEGER NOT NULL,
                        MinuteFrom INTEGER NOT NULL,
                        StreamLink TEXT,
                        SecondaryLink TEXT,
                        OnSiteLocation TEXT,
                        FOREIGN KEY (CourseId) REFERENCES Courses(CourseId)
                        ON DELETE CASCADE ON UPDATE CASCADE,
                        UNIQUE (CourseId, DayId, HourFrom, MinuteFrom)
                        );"""
conn.execute(Courses)
conn.execute(Lectures)

data = conn.execute("SELECT SubjectID, SubjectName, SubjectAbbreviation, SubjectLink, SubjectSemester FROM Subjects").fetchall()
for l in data:
    if l[4] == 1:
        role_id = 773543051011555398
    elif l[4] == 3:
        role_id = 810241727800541194
    else:
        role_id = 810242456134221854
    channel_id = 756391202546384927
    guild_id = 747752542741725244
    conn.execute("INSERT INTO Courses(CourseId, Name, Abbreviation, Link, GuildId, DiscordRoleId, DiscordChannelId) VALUES(?,?,?,?,?,?,?)",
                 (l[0], l[1], l[2], l[3], guild_id, role_id, channel_id))

data = conn.execute("SELECT SubjectId, DayId, TimeFrom, StreamLink, OnSiteLocation FROM WeekDayTimes").fetchall()
for l in data:
    conn.execute("INSERT INTO Lectures(CourseId, DayId, HourFrom, MinuteFrom, StreamLink, OnSiteLocation) VALUES(?,?,?,?,?,?)",
                 (l[0], l[1], l[2], 0, l[3], l[4]))

conn.execute("DROP TABLE WeekDayTimes;")
conn.execute("DROP TABLE Subjects;")

conn.commit()
conn.close()