upd status, clean db, clean telegram queue

3 files changed, 48 insertions, 8 deletions

diff --git a/bot.py b/bot.py
index 5666987..7a22a35 100644
--- a/bot.py
+++ b/bot.py
@@ -34,6 +34,7 @@ class Bot:
         conn = getDbConn()
         cur = conn.cursor()
         for data in data['result']:
+            updateId = data['update_id']
             msg = data['message']
             msgId = msg['message_id']
             msgDate = msg['date']
@@ -41,7 +42,8 @@ class Bot:
             text = msg['text']
             res = cur.execute(f"SELECT id, is_read FROM incoming WHERE id = {msgId}")
             if not res.fetchall():
-                cur.execute(f"INSERT INTO incoming VALUES ({msgId}, {msgDate}, '{msgFrom}', '{text}', False)")
+                cur.execute(
+                    f"INSERT INTO incoming VALUES ({msgId}, {msgDate}, '{msgFrom}', '{text}', False, {updateId})")
                 self.hasNewMessages = True
         conn.commit()
         conn.close()
@@ -62,7 +64,7 @@ class Bot:
         self.isProcessingPolling = True
 
         try:
-            url = f'https://api.telegram.org/bot{self.token}/getUpdates'
+            url = f'https://api.telegram.org/bot{self.token}/getUpdates?offset=-10'
             response = requests.get(url)
             if response.status_code == 200:
                 self._dbUpdate(response.json())
@@ -91,6 +93,20 @@ class Bot:
         finally:
             return result
 
+    def cleanTelegramApiQueue(self):
+        try:
+            urlGetMaxId = f'https://api.telegram.org/bot{self.token}/getUpdates?offset=-1'
+            response = requests.get(urlGetMaxId)
+            data = response.json()
+            if data['result']:
+                maxId = data['result'][0]['update_id']
+                # dapet trik dari stackoverflow
+                # https://stackoverflow.com/questions/61976560/how-to-delete-queue-updates-in-telegram-api
+                urlRemoveApiQueue = f'https://api.telegram.org/bot{self.token}/getUpdates?offset={maxId + 1}'
+                requests.get(urlRemoveApiQueue)
+        except requests.exceptions.ConnectionError as e:
+            logger.error("Connection error during polling update")
+
 
 if __name__ == '__main__':
     bot = Bot()
diff --git a/main.py b/main.py
index 655fbeb..17d7bbc 100644
--- a/main.py
+++ b/main.py
@@ -4,7 +4,7 @@ from downloader import downloadCsv
 from csvReader import readCSV
 from bot import Bot
 from schedule import every, repeat, run_pending
-from util import dbFile, initDb
+from util import dbFile, initDb, getDbConn
 import time
 
 from util import setupLogger
@@ -51,13 +51,37 @@ def processMessages(messages):
         if msg == 'update':
             logger.info("EXPLICIT SCORE UPDATE REQUESTED")
             score = getScore()
-            msg = f"Score leantime bulan ini = {score}%" if score >= 0 else "Koneksi ke dashboard bermasalah"
-            bot.sendMessage(msg)
-        elif 'log' in msg:
+            msgSend = f"Score leantime bulan ini = {score}%" if score >= 0 else "Koneksi ke dashboard bermasalah"
+            bot.sendMessage(msgSend)
+        elif 'getlog' in msg:
             sendLog(msg)
-        elif msg == 'clean':
+        elif msg == 'cleanlog':
             open(os.getenv('LOGFILE'), 'w').close()
             bot.sendMessage("Log dihapus", log=False)
+        elif msg == 'cleandb':
+            connn = getDbConn()
+            cur = connn.cursor()
+            cur.execute("DELETE FROM incoming")
+            """
+            getUpdate di server telegram harus dibersihin juga, kalo ngga bakal ngeloop terus2an
+            db bersih -> ngirim konfirmasi ke gw -> polling dapet data baru -> ditandai belum dibaca 
+            -> ngeliat ada command cleandb di antrian -> cleandb -> ngirim konfirmasi -> polling dst
+            """
+            bot.cleanTelegramApiQueue()
+            connn.commit()
+            connn.close()
+            bot.sendMessage("DB dihapus")
+            logger.info("DB Cleaned")
+        elif msg == 'status':
+            fileStat = os.stat(os.getenv('LOGFILE'))
+            msgSend = f'Log file = {fileStat.st_size / 1024}kB'
+            connn = getDbConn()
+            cur = connn.cursor()
+            cur.execute("SELECT COUNT(*) FROM incoming")
+            res = cur.fetchone()
+            msgSend += f'\nDB = {res[0]} rows'
+            bot.sendMessage(msgSend)
+            connn.close()
 
 
 @repeat(every().day.at("09:00"))
diff --git a/util.py b/util.py
index 37f52c0..e7a0e9a 100644
--- a/util.py
+++ b/util.py
@@ -26,7 +26,7 @@ def initDb():
     try:
         conn = sqlite3.connect(dbFile)
         cur = conn.cursor()
-        cur.execute("CREATE TABLE incoming (id, msg_date, msg_from, text, is_read)")
+        cur.execute("CREATE TABLE incoming (id, msg_date, msg_from, text, is_read, update_id)")
         conn.commit()
         conn.close()
     except sqlite3.Error as e: