Discord Bot - Jarvis

Python SQL Discord.py

---

About the project

This project is one of my longest going projects in development. What started as a simple automated ticket bot, grew into a much more robust and improved version of itself over it's 3+ year lifespan. Below you will find the iterations of the project as it went trough it's changes as well as the code that was used each time. 

 

Jarvis is an automated Discord ticket bot with many custom features. The full range of features it has are the following:

• Automate member tickets, essentially reading user input and sending an embed with relevant information.
• Provides direct message survey after each ticket.
  • Old method which took them out of Discord was ~2% response.
  • New method which automatically generates and sends an interactive survey in Discord DMs has ~15% response.
• Gives owners of the Discord group the ability to generate reports of the survey responses in seconds in easily digestible way.

 

Recently, I did a full code rewrite, this was to not only fix my itch to clean it up but made it much smaller, modular, and more efficient. The code went from >1700 lines of code to just over 1100 lines. Below you can find some of the impact of the changes. This will hopefully be the last big rewrite, changes can be found in version 4 below.

• Modularity
  • Made the code more reusable by making new libraries that many other bots will also access.
• Efficiency with data
  • Lots of the data pulled from the database was done in an inefficient manner, so I used Pandas to handle and manipulate the data efficiently.
• Useless code
  • Removed useless code such as conn.close(), as AsyncConnection will close automatically when exited.
  • Removed redundant code.
• Packing more into queries
  • As some of my old code resorted to sorting data outside of a query, I made my queries more complex to extract what I wanted with simpler code.
• Cleanliness
  • Overall reduced the size of each bot by over 35%.
• Environment variable configuration
  • Moved any sensitive info and environment-specific variables into a .env which will also be used for all the bots used within a Discord server.

 

 

1. Automated Member Tickets

 

Version 1

The first iteration used discord emojis that members would react to to go to different categories of the bots flow. At the time, this was the fastest option if we wanted to have some sort of ticket flow where members could interact to go to where the helpful info would be. If you would like to see v1 of the flow of this bot you can go here.

 

The code for this iteration can be found on my GitHub here. Keep in mind this is a fairly rough piece of code but worked as well as it could given the limitations we had on Discord at the time.

 

Version 2

This version introduced buttons. Once Discord added buttons publicly I jumped on them and rewrote the whole code base to accommodate for the new feature. The overall flow would be the same however the speed improvement with buttons was I think 5x faster. The changes at this point to the flow was much different than what was in the initial code so the overall code was much smaller, but buttons also simplified the code as well.

 

The code for this can be found on my GitHub here. Keep in mind this iteration also has some cluttered code and nested functions. I initially was just playing with buttons when creating this update and once it was complete decided it was good enough as is to not need any changes and before I did I moved on to version 3.

 

Version 3

This version has been running now for almost a year, I have gone through a few iterations of this version but it is definitely the most improved version and will probably not have a huge overhaul again. Key features that were added in this verion.

 

• Moved from buttons to a more refined automated approach using text input from users to generate an embed with relevant support.
• Introduced welcome tickets for members which was just a small addition to automatically create tickets when members join.
• Addition of a direct message survey sent to members after a ticket is closed. This is discussed in the next section. (Section 2)
• Giving the owners of the group commands to generate reports from the completed surveys. (Section 2)

 

This version also introduced more async functionality, adding async to the postgresql connections (psycogp3). The code for this can be found on GitHub here. This version is much more cleaned up than the older version but since I personally like keeping an entire Discord bot in one python file it can be a little messy looking but functions very well without any issues. Lines 611-833 contains the main chunk of code for the first item in the list above. The second item in the list above is somewhat spread throughout the code but the bulk and where the initial ticket is created is in lines 1884-1984.

 

 

Version 4

This version was the full rewrite. No new features were added but the overhaul made the bot so much better and will make future updates/additions a breeze. 

 

Below is the main function, this contains the Discord bot which contains all the main functions of the bot.

import discord
import os
import time
import logging
import asyncio
from discord.ui import Button, View
import datetime
from datetime import date
import io
from discord import app_commands
from dotenv import load_dotenv

import dbaccess
import survey_utils
import embeds

logging.basicConfig(level=os.environ.get("LOGLEVEL", "DEBUG"), filename='jarvis.log', filemode='a', format='%(asctime)s - [Line:%(lineno)d - Function:%(funcName)s In:%(filename)s From:%(name)s] \n[%(levelname)s] - %(message)s \n', datefmt='%d-%b-%y %H:%M:%S')

#Bot Token
load_dotenv()
token = os.environ.get('JARVIS_TOKEN')
intents = discord.Intents().all()
intents.members = True
intents.guilds = True

KIANROLE = int(os.environ.get('KIANROLE'))
MICHAELROLE = int(os.environ.get('MICHAELROLE'))
DOOLEYROLE = int(os.environ.get('DOOLEYROLE'))

ALERT = os.environ.get('ALERT')
ADMIN = os.environ.get('ADMIN')

TICKETBOTID = int(os.environ.get('TICKETBOTID'))
LOYALMEMBERUSCATID = int(os.environ.get('LOYALMEMBERUSCATID'))
LOYALMEMBEREUCATID = int(os.environ.get('LOYALMEMBEREUCATID'))

TICKETCATEGORYID = int(os.environ.get('TICKETCATEGORYID'))
NEWMEMBERCATLIST = [int(os.environ.get('NEWMEMBERCATID1')),int(os.environ.get('NEWMEMBERCATID2')),int(os.environ.get('NEWMEMBERCATID3'))]

class Client(discord.Client):
    def __init__(self):
        super().__init__(intents = intents)
        self.synced = False
        asyncio.set_event_loop_policy(
            asyncio.WindowsSelectorEventLoopPolicy()
        )

    async def on_ready(self):
        await self.wait_until_ready()
        if not self.synced:
            await client.sync()
            self.synced = True

        await survey_utils.set_vars(Client)

        print(f'{self.user} has connected to Discord!')

Client = Client()
client = app_commands.CommandTree(Client)

# Buttons for Topup US
fourtyButton = Button(label = "40", style = discord.ButtonStyle.grey)
fourtyNineButton = Button(label = "49", style = discord.ButtonStyle.blurple)
fourty_threeButton = Button(label = "3 Months", style = discord.ButtonStyle.grey)
fourty_sixButton = Button(label = "6 Months", style = discord.ButtonStyle.blurple)
fourty_yearButton = Button(label = "12 Months", style = discord.ButtonStyle.blurple)
fourtyNine_threeButton = Button(label = "3 Months", style = discord.ButtonStyle.link, url = "https://notify.org/3m-top-up")
fourtyNine_sixButton = Button(label = "6 Months", style = discord.ButtonStyle.link, url = "https://notify.org/6m-top-up")
fourtyNine_yearButton = Button(label = "12 Months", style = discord.ButtonStyle.link, url = "https://notify.org/12m-top-up")

####################################################################################################
# 
# Commands for Survey Stuff
#
####################################################################################################
@client.command(name="surveyreport")
async def survey_report_all(interaction: discord.Interaction):
    if interaction.user.id in [KIANROLE, MICHAELROLE]:
        await survey_utils.generate_survey_report(
            interaction,
            "select q1, q2, q3, q4, q5, q6, timefinished from ticketsurvey where q2 != 0",
            ["Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Time"],
            "surveyreport.csv",
            "Q6: Is there anything you feel we can improve on regarding support?"
        )
    else:
        await interaction.response.send_message("You don't have access to this command.")

@client.command(name="surveyreportwelcomes")
async def survey_report_welcomes(interaction: discord.Interaction):
    if interaction.user.id in [KIANROLE, MICHAELROLE, DOOLEYROLE]:
        await survey_utils.generate_survey_report(
            interaction,
            "select q1, q2, q3, q4, q5, timefinished from ticketsurveywelcomes where q2 != 0",
            ["Q1", "Q2", "Q3", "Q4", "Q5", "Time"],
            "surveyreportwelcomes.csv"
        )
    else:
        await interaction.response.send_message("You don't have access to this command.")

@client.command(name = "surveyreport1w")
async def surveyreport1w(interaction: discord.Interaction):

    if interaction.user.id in [KIANROLE, MICHAELROLE]:
        await survey_utils.generate_survey_report(
            interaction,
            "select q1, q2, q3, q4, q5, q6, timefinished from ticketsurvey where q2 != 0 AND TO_TIMESTAMP(timefinished, 'YYYY-MM-DD HH24:MI:SS.US') >= NOW() - INTERVAL '{604800} seconds'",
            ["Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Time"],
            "surveyreport.csv",
            "Q6: Is there anything you feel we can improve on regarding support?",
        )
    else:
        await interaction.response.send_message("You don't have access to this command.")

@client.command(name = "surveyreport1m")
async def surveyreport1m(interaction: discord.Interaction):

    if interaction.user.id in [KIANROLE, MICHAELROLE]:
        await survey_utils.generate_survey_report(
            interaction,
            "select q1, q2, q3, q4, q5, q6, timefinished from ticketsurvey where q2 != 0 AND TO_TIMESTAMP(timefinished, 'YYYY-MM-DD HH24:MI:SS.US') >= NOW() - INTERVAL '{2630000} seconds'",
            ["Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Time"],
            "surveyreport.csv",
            "Q6: Is there anything you feel we can improve on regarding support?",
        )
    else:
        await interaction.response.send_message("You don't have access to this command.")

@client.command(name = "surveyreport3m")
async def surveyreport3m(interaction: discord.Interaction):

    if interaction.user.id in [KIANROLE, MICHAELROLE]:
        await survey_utils.generate_survey_report(
            interaction,
            "select q1, q2, q3, q4, q5, q6, timefinished from ticketsurvey where q2 != 0 AND TO_TIMESTAMP(timefinished, 'YYYY-MM-DD HH24:MI:SS.US') >= NOW() - INTERVAL '{7890000} seconds'",
            ["Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Time"],
            "surveyreport.csv",
            "Q6: Is there anything you feel we can improve on regarding support?",
        )
    else:
        await interaction.response.send_message("You don't have access to this command.")

####################################################################################################
# 
# on_message used to start many different functions of the bot
#
####################################################################################################
@Client.event
async def on_message(message):

    # Used 3x, so set to a variable for cleanliness
    is_ticket_bot = str(message.author) == str(Client.get_user(TICKETBOTID))

    # Initiates Jarvis For Topups US
    if ('thanks for being a loyal member!' in message.content) and message.channel.category_id == LOYALMEMBERUSCATID and is_ticket_bot:

        embedVar = await embeds.loyalMemberUS()

        view = View(timeout = None)
        view.add_item(fourtyButton)
        view.add_item(fourtyNineButton)

        fourtyButton.callback = fourty_callback
        fourtyNineButton.callback = fourtyNine_callback

        await message.channel.send(embed=embedVar, view=view)

    # Initiates Jarvis For Topups EU
    if ('thanks for being a loyal member.' in message.content) and message.channel.category_id == LOYALMEMBEREUCATID and is_ticket_bot:

        embedVar = await embeds.loyalMemberEU()

        view = View(timeout = None)
        view.add_item(Button(label = "3 Months", style = discord.ButtonStyle.link, url = "https://notify.org/eu-3m-top-up"))
        view.add_item(Button(label = "6 Months", style = discord.ButtonStyle.link, url = "https://notify.org/eu-6m-top-up"))

        await message.channel.send( embed=embedVar, view = view)

    # Initiates Jarvis
    if ('Initiating Jarvis...' in message.content) and is_ticket_bot:

        #Sets db vars.
        chid = message.channel.id
        mention = str(message.mentions[0])
        mentionID = str(message.mentions[0].id)

        t = str(datetime.datetime.now())

        query = """INSERT INTO ticketsurvey (id, name, q1, q2, q3, q4, q5, q6, timefinished) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (id) DO UPDATE set (name, q1, timefinished) = (EXCLUDED.name, EXCLUDED.q1, EXCLUDED.timefinished)"""
        data = (chid, mentionID, mention, 0, 0, 0, "Nothing", "Nothing", t)

        await dbaccess.write_data(query, data)

        for embed in message.embeds:

            content = str(embed.title).split("Support Topic:")[1]
            user_question = f"**{content}**"

            packagekw = ["package","shoes","item","stolen","missing","lost"]
            acckw = ["nike accounts", "nike"]
            cardkw = ["privacy","amex","slash","eno","mastercard","tradeshift","stripe","visa","citi","vcc"]
            memkw = ["cancel","break","vacation","pause","og role","membership"]
            otherkw = ["notify app","notify anywhere","emerging","notify tools","notify helper","aycd","aws","ebay","notify toolbox","automations","autos","aycd ai"]
            botkw = ["balko","cyber","prism","hayha","kylinbot","mekaio","ksr","valor","mek","chegg","cheggaio","refract"]
            sitekw = ["finishline","finish line","fnl","jd","footsites","foots","ftl","champs","hibbett","shopify","shop","supreme"," ys","yeezy supply","target","bestbuy","amazon","walmart","amd","gamestop","microsoft"]
            proxkw = ["proxies","proxy","resi","residential","isp","isps","subnet"]

            category_keywords = {
                "Bot": botkw,
                "Site": sitekw,
                "Membership": memkw,
                "Proxies": proxkw,
                "Package": packagekw,
                "Accounts": acckw,
                "Cards": cardkw,
                "Other": otherkw
            }

            # Main embeds to send in each ticket.
            embedVarFirst = await embeds.talkToSupport()
            embedVarLast = await embeds.ticketInfo(user_question)

            embeds_to_send = []
            addend = 0

            # Collects any extra needed embeds
            for category, keywords in category_keywords.items():
                if any(keyword in content.lower() for keyword in keywords):
                    if category == "Package":
                        if any(keyword in content.lower() for keyword in packagekw[:3]) and any(keyword in content.lower() for keyword in packagekw[3:]):
                            embedVar = await embeds.shipping()
                            embeds_to_send.append(embedVar)
                    if category == "Accounts":
                        embedVar = await embeds.nike()
                        embeds_to_send.append(embedVar)
                    if category == "Cards":
                        embedVar = await embeds.creditCards()
                        embeds_to_send.append(embedVar)
                    if category == "Membership":
                        embedVar = await embeds.membership()
                        embeds_to_send.append(embedVar)
                    if category == "Proxies":
                        embedVar = await embeds.proxy()
                        embeds_to_send.append(embedVar)
                    if category == "Other":
                        embedVar = await embeds.other(content, otherkw)
                        embeds_to_send.append(embedVar)
                    if category == "Bot":
                        embedVar = await embeds.bots(content, botkw)
                        embeds_to_send.append(embedVar)
                    if category == "Site":
                        embedVar = await embeds.sites(content, sitekw)
                        embeds_to_send.append(embedVar)

            # Sends initial embed to ping staff, then relevant embeds to the kws, then the final embed.
            # Initial
            await message.channel.send(embed=embedVarFirst)
            await message.channel.send(ALERT)

            # Relevant content
            for emb in embeds_to_send:
                await message.channel.send(embed=emb)

            # Final embed with FAQ and user question
            await message.channel.send(embed=embedVarLast)     

    if "Woohoo! You're now a part of the Notify community" in message.content and message.channel.category_id in NEWMEMBERCATLIST:

        chid = message.channel.id
        mention = message.mentions[0]
        mentionID = message.mentions[0].id

        t = str(datetime.datetime.now())

        query = """INSERT INTO ticketsurveywelcomes (id, name, q1, q2, q3, q4, q5, timefinished) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (id) DO UPDATE set (name, q1, timefinished) = (EXCLUDED.name, EXCLUDED.q1, EXCLUDED.timefinished)"""
        data = (chid, str(mentionID), str(mention), 0, 0, 0, "Nothing", t)

        await dbaccess.write_data(query, data)

@Client.event
async def on_guild_channel_delete(ch):

    GUILD = Client.get_guild(int(os.environ.get('NOTIFYGUILDID')))
    category_id = ch.category_id
    chid = ch.id

    # Gets the uid related to the channel id of the ticket, then attempts a dm, if successful it will send the survey to the user to complete.
    if category_id == TICKETCATEGORYID:

        uid = ""
        query = "select * from ticketsurvey where id = %s"

        ticketsurvey = await dbaccess.get_data(query, (chid,))
        
        for row in ticketsurvey:
            uid = row[1]

        print("Member: " + uid)

        await survey_utils.send_survey(uid, GUILD, 0)

    # Gets the uid related to the channel id of the welcome ticket, then attempts a dm, if successful it will send the survey to the user to complete.
    if category_id in NEWMEMBERCATLIST:

        uid = ""
        query = "select * from ticketsurveywelcomes where id = %s"

        ticketsurvey = await dbaccess.get_data(query, (chid,))
        
        for row in ticketsurvey:
            uid = row[1]

        print("Member (Welcomes): " + uid)

        await survey_utils.send_survey(uid, GUILD, 1)

# Creates channel with welcome ticket for member that just joined.
@Client.event
async def on_member_join(member):

    GUILD = Client.get_guild(int(os.environ.get('NOTIFYGUILDID')))

    dooley = await Client.fetch_user(str(DOOLEYROLE))
    endless = await Client.fetch_user(str(517856488652275714))
    kian = await Client.fetch_user(str(KIANROLE))

    overwrites={
                    GUILD.default_role: discord.PermissionOverwrite(read_messages=False),
                    GUILD.me: discord.PermissionOverwrite(read_messages=True, send_messages=True),
                    member: discord.PermissionOverwrite(read_messages=True, send_messages=True),
                    dooley: discord.PermissionOverwrite(read_messages=True, manage_channels=True),
                    endless: discord.PermissionOverwrite(read_messages=True, manage_channels=True),
                    kian: discord.PermissionOverwrite(read_messages=True, manage_channels=True),
                }

    try:
        ch = await GUILD.create_text_channel("welcome-" + str(member), overwrites=overwrites, category=discord.utils.get(GUILD.categories, id=1084368328673476608))
    except Exception as e:
        logging.info("Error, exception 1: " + str(e))
        if "Maximum number of channels in category reached (50)" in str(e):
            try:
                ch = await GUILD.create_text_channel("welcome-" + str(member), overwrites=overwrites, category=discord.utils.get(GUILD.categories, id=1086427580166590514))
            except Exception as e:
                logging.info("Error, exception 2: " + str(e))
                if "Maximum number of channels in category reached (50)" in str(e):
                    try:
                        ch = await GUILD.create_text_channel("welcome-" + str(member), overwrites=overwrites, category=discord.utils.get(GUILD.categories, id=1087054788300124311))
                    except Exception as e:
                        logging.info("Error, exception 3: " + str(e))
                        print("Error, final exception: " + str(e))

    embedVar = await embeds.welcome_ticket(member)

    view = View(timeout=None)
    view.add_item(Button(label="Customize Channels", style=discord.ButtonStyle.grey, emoji="⭐", url="https://discord.com/channels/570142274902818816/1091086648734916758"))
    view.add_item(Button(label="Personalized Support", style=discord.ButtonStyle.grey, emoji="🧠", url="https://discord.com/channels/570142274902818816/915302513127874611"))
    view.add_item(Button(label="Getting Started", style=discord.ButtonStyle.grey, url="https://discord.com/channels/570142274902818816/598105432103583744"))

    await ch.send("**Woohoo! You're now a part of the Notify community** <@!" + str(member.id) + ">!")
    await ch.send(embed=embedVar, view=view)

########################################################
#CALLBACKS FOR TOPUP US
########################################################
async def fourty_callback(interaction):
    await interaction.response.edit_message(view = None)

    embedVar = await embeds.topup_us_40()

    view = View(timeout = None)
    view.add_item(fourty_threeButton)
    view.add_item(fourty_sixButton)

    fourty_threeButton.callback = fourty_three_callback
    fourty_sixButton.callback = fourty_six_callback

    await interaction.channel.send( embed=embedVar, view = view)

async def fourty_three_callback(interaction):
    await interaction.response.edit_message(view = None)

    embedVar = await embeds.topup_us_40_final("3")

    await interaction.channel.send(embed=embedVar)
    await interaction.channel.send(ADMIN)

async def fourty_six_callback(interaction):
    await interaction.response.edit_message(view = None)

    embedVar = await embeds.topup_us_40_final("6")

    await interaction.channel.send(embed=embedVar)
    await interaction.channel.send(ADMIN)

async def fourtyNine_callback(interaction):
    await interaction.response.edit_message(view = None)

    embedVar = await embeds.topup_us_49()

    view = View(timeout = None)
    view.add_item(fourtyNine_threeButton)
    view.add_item(fourtyNine_sixButton)

    fourtyNine_threeButton.callback = fourtyNine_three_callback
    fourtyNine_sixButton.callback = fourtyNine_six_callback

    await interaction.channel.send( embed=embedVar, view = view)

async def fourtyNine_three_callback(interaction):
    await interaction.response.edit_message(view = None)

    embedVar = await embeds.topup_us_49_final("3", "https://notify.org/3m-top-up")

    await interaction.channel.send( embed=embedVar)

async def fourtyNine_six_callback(interaction):
    await interaction.response.edit_message(view = None)

    embedVar = await embeds.topup_us_49_final("6", "https://notify.org/6m-top-up")

    await interaction.channel.send( embed=embedVar)

Client.run(token)

 

The next part contains the utility functions/classes that the main bot uses to handle the surveys. Most of it is callbacks that go through each question of the survey, writing the responses to the database as they are answered. You can find more about the survey section along with a demo in section 2 below.

import io
import logging
import asyncio
from typing import List
import discord
from discord.ui import Button, View
import pandas as pd
import datetime

import dbaccess

Client = None

# Sets client var on bot start
async def set_vars(c):
    global Client

    Client = c

#############################################################
# Used to get the survey data given some input parameters
#############################################################
async def generate_survey_report(
    interaction: discord.Interaction,
    query: str,
    column_names: List[str],
    filename: str,
    extra_fields: str = None,
):
    try:
        df, total_rows = await dbaccess.get_data_as_dataframe(query)

        if len(df) == 0:
            await interaction.response.send_message("No survey data available.")
            return

        df.columns = column_names

        df_sorted = df.sort_values(by="Time")

        q2avg = df_sorted['Q2'].mean()
        q3avg = df_sorted['Q3'].mean()
        q4avg = df_sorted['Q4'].mean()
        countq1 = df_sorted['Q1'].ne("Redacted").sum()
        countq5 = df_sorted['Q5'].str.lower().isin(["na", "nothing"]).sum()
        rowcount = len(df_sorted)

        ratio = len(df_sorted) / total_rows * 100

        header = column_names
        buffer = io.StringIO()
        df_sorted.to_csv(buffer, index=False, header=header)

        embed = discord.Embed(title="Survey Summary", description="See below for a brief summary.", color=0xDB0B23)
        embed.add_field(name="Total surveys completed compared to tickets opened.", value=f"`{len(df_sorted)}/{total_rows}` Totalling {ratio:.2f}% of tickets.", inline=False)
        embed.add_field(name="Q1: Are you fine with sharing your name for this survey?", value=f"`{countq1}/{len(df_sorted)}` Gave their name.", inline=False)
        embed.add_field(name="Q2: How would you rate the speed of the support you received?", value=f"`{q2avg:.2f}` Was the average score of {len(df_sorted)} responses.", inline=False)
        embed.add_field(name="Q3: How would you rate the quality of the info received?", value=f"`{q3avg:.2f}` Was the average score of {len(df_sorted)} responses.", inline=False)
        embed.add_field(name="Q4: How likely are you to renew your Notify subscription?", value=f"`{q4avg:.2f}` Was the average score of {len(df_sorted)} responses.", inline=False)
        if extra_fields:
            embed.add_field(name="Q5: If you would like to commend a staff member for their assistance please write their name now.", value=f"`{len(df_sorted)-countq5}/{len(df_sorted)}` Gave staff commendations.", inline=False)
            countq6 = df_sorted['Q6'].str.lower().isin(["na", "nothing"]).sum()
            embed.add_field(name=extra_fields, value=f"`{len(df_sorted)-countq6}/{len(df_sorted)}` Suggested Improvements.", inline=False)
        else:
            embed.add_field(name="Q5: Is there anything you feel we can improve on regarding support?", value=f"`{len(df_sorted)-countq5}/{len(df_sorted)}` Suggested Improvements.", inline=False)

        await interaction.response.send_message(embed=embed)
        await interaction.channel.send(file=discord.File(io.BytesIO(buffer.getvalue().encode()), filename))
    except Exception as e:
        logging.error(f"Error in generating survey report: {e}")
        await interaction.response.send_message("An error occurred while processing the survey report.")

#############################################################
# Used to send the survey to the member of the closed ticket
#############################################################
async def send_survey(uid, GUILD, tickType):
    if uid and uid != "Nothing":
        try:
            u = int(uid)
        except ValueError:
            print("Invalid UID format:", uid)
            return
            
        if GUILD.get_member(u) is not None:
            user = GUILD.get_member(u)

            print("Member element: " + str(user) + ", DM attempt")

            try:
                await user.send("**Please help us improve our support by giving feedback.** \nIt will take just 30 seconds. Survey expires after 24 hours.")
                print("DM sent")
            except Exception as e:
                print("User cannot be DM'd.")
                print(e)
                return

            if tickType == 0:
                await initial_func(user, tickType)
            else:
                await initial_func(user, tickType)
        else:
            print("member is not in Notify anymore.")
    else:
        print("ticket made when bot/survey was not setup.")

#############################################################
# Classes and functions for survey flow
#############################################################
async def initial_func(user, tickType):
    view = SurveyViewMain(tickType)
    await user.send("**Question 1:** \nAre you fine with sharing your name for this survey?", view=view)

class SurveyViewMain(View):
    def __init__(self, tickType):
        super().__init__(timeout=86400)
        self.tickType = tickType

        if self.tickType == 0:
            self.query1 = "select * from ticketsurvey where name = %s"
            self.query2 = "INSERT INTO ticketsurvey (id, name, q1) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set q1 = EXCLUDED.q1"
        else:
            self.query1 = "select * from ticketsurveywelcomes where name = %s"
            self.query2 = "INSERT INTO ticketsurveywelcomes (id, name, q1) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set q1 = EXCLUDED.q1"

        self.question_2 = "**Question 2:** \nHow would you rate the speed of the support you received, with 1 being terrible and 5 being great?"

    # Define Buttons
    @discord.ui.button(label="Yes", style=discord.ButtonStyle.green, custom_id="Yes")
    async def yes_callback(self, interaction, button):
        view = SurveyViewSecond(self.tickType)
        await interaction.user.send(self.question_2, view=view)
        await interaction.response.edit_message(view = None)
    @discord.ui.button(label="No", style=discord.ButtonStyle.red, custom_id="No")
    async def no_callback(self, interaction, button):
        await dbaccess.write_to_db(self.query1, self.query2, "Redacted", (str(interaction.user.id),))
        view = SurveyViewSecond(self.tickType)
        await interaction.user.send(self.question_2, view=view)
        await interaction.response.edit_message(view = None)

class SurveyViewSecond(View):
    def __init__(self, tickType):
        super().__init__(timeout=86400)
        self.tickType = tickType

        if self.tickType == 0:
            self.query1 = "select * from ticketsurvey where name = %s"
            self.query2 = "INSERT INTO ticketsurvey (id, name, q2) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set q2 = EXCLUDED.q2"
        else:
            self.query1 = "select * from ticketsurveywelcomes where name = %s"
            self.query2 = "INSERT INTO ticketsurveywelcomes (id, name, q2) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set q2 = EXCLUDED.q2"

        self.question_3 = "**Question 3:** \nHow would you rate the quality of the info received, with 1 being terrible and 5 being great?"

    @discord.ui.button(label="1", style=discord.ButtonStyle.grey, custom_id="q2_1")
    async def one_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 1)
    @discord.ui.button(label="2", style=discord.ButtonStyle.grey, custom_id="q2_2")
    async def two_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 2)
    @discord.ui.button(label="3", style=discord.ButtonStyle.grey, custom_id="q2_3")
    async def three_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 3)
    @discord.ui.button(label="4", style=discord.ButtonStyle.grey, custom_id="q2_4")
    async def four_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 4)
    @discord.ui.button(label="5", style=discord.ButtonStyle.grey, custom_id="q2_5")
    async def five_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 5)

    async def manage_callbacks(self, interaction, rating):
        await dbaccess.write_to_db(self.query1, self.query2, rating, (str(interaction.user.id),))
        view = SurveyViewThird(self.tickType)
        await interaction.user.send(self.question_3, view = view)
        await interaction.response.edit_message(view = None)

class SurveyViewThird(View):
    def __init__(self, tickType):
        super().__init__(timeout=86400)
        self.tickType = tickType

        if self.tickType == 0:
            self.query1 = "select * from ticketsurvey where name = %s"
            self.query2 = "INSERT INTO ticketsurvey (id, name, q3) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set q3 = EXCLUDED.q3"
        else:
            self.query1 = "select * from ticketsurveywelcomes where name = %s"
            self.query2 = "INSERT INTO ticketsurveywelcomes (id, name, q3) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set q3 = EXCLUDED.q3"
        
        self.question_4 = "**Question 4:** \nHow likely are you to renew your Notify subscription, with 1 being very unlikely and 5 being very likely?"

    @discord.ui.button(label="1", style=discord.ButtonStyle.grey, custom_id="q3_1")
    async def one_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 1)
    @discord.ui.button(label="2", style=discord.ButtonStyle.grey, custom_id="q3_2")
    async def two_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 2)
    @discord.ui.button(label="3", style=discord.ButtonStyle.grey, custom_id="q3_3")
    async def three_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 3)
    @discord.ui.button(label="4", style=discord.ButtonStyle.grey, custom_id="q3_4")
    async def four_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 4)
    @discord.ui.button(label="5", style=discord.ButtonStyle.grey, custom_id="q3_5")
    async def five_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 5)

    async def manage_callbacks(self, interaction, rating):
        await dbaccess.write_to_db(self.query1, self.query2, rating, (str(interaction.user.id),))
        view = SurveyViewFourth(self.tickType)
        await interaction.user.send(self.question_4, view = view)
        await interaction.response.edit_message(view = None)

class SurveyViewFourth(View):
    def __init__(self, tickType):
        super().__init__(timeout=86400)
        self.tickType = tickType

        if self.tickType == 0:
            self.query1 = "select * from ticketsurvey where name = %s"
            self.query2 = "INSERT INTO ticketsurvey (id, name, q4) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set q4 = EXCLUDED.q4"
        else:
            self.query1 = "select * from ticketsurveywelcomes where name = %s"
            self.query2 = "INSERT INTO ticketsurveywelcomes (id, name, q4) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set q4 = EXCLUDED.q4"

    @discord.ui.button(label="1", style=discord.ButtonStyle.grey, custom_id="q4_1")
    async def one_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 1)
    @discord.ui.button(label="2", style=discord.ButtonStyle.grey, custom_id="q4_2")
    async def two_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 2)
    @discord.ui.button(label="3", style=discord.ButtonStyle.grey, custom_id="q4_3")
    async def three_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 3)
    @discord.ui.button(label="4", style=discord.ButtonStyle.grey, custom_id="q4_4")
    async def four_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 4)
    @discord.ui.button(label="5", style=discord.ButtonStyle.grey, custom_id="q4_5")
    async def five_callback(self, interaction, button):
        await self.manage_callbacks(interaction, 5)

    async def manage_callbacks(self, interaction, rating):
        await dbaccess.write_to_db(self.query1, self.query2, rating, (str(interaction.user.id),))
        await interaction.response.edit_message(view = None)

        user = interaction.user
        userID = interaction.user.id
        # Last two question answers
        def check(m):
            return user == m.author and isinstance(m.channel, discord.DMChannel)

        if self.tickType == 0:
            query5_1 = "select * from ticketsurvey where name = %s"
            query5_2 = "INSERT INTO ticketsurvey (id, name, q5) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set q5 = EXCLUDED.q5"

            query6_1 = "select * from ticketsurvey where name = %s"
            query6_2 = "INSERT INTO ticketsurvey (id, name, q6) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set q6 = EXCLUDED.q6"

            query6_1_time = "select * from ticketsurvey where name = %s"
            query6_2_time = "INSERT INTO ticketsurvey (id, name, timefinished) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set timefinished = EXCLUDED.timefinished"
        else:
            query5_1_welcome = "select * from ticketsurveywelcomes where name = %s"
            query5_2_welcome = "INSERT INTO ticketsurveywelcomes (id, name, q5) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set q5 = EXCLUDED.q5"

            query6_1_time = "select * from ticketsurvey where name = %s"
            query6_2_time = "INSERT INTO ticketsurvey (id, name, timefinished) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE set timefinished = EXCLUDED.timefinished"

        if self.tickType == 0:
            await user.send("**Question 5:** \nIf you would like to commend a staff member for their assistance please write their name now (if not just type NA).")
            try:
                q5 = await Client.wait_for("message", timeout = 300, check = check)
            except asyncio.TimeoutError:
                await dbaccess.write_to_db(query5_1, query5_2, "NA", (str(userID),))
                await user.send("Didn't get an input, so moving to the last question.")
            else:
                await dbaccess.write_to_db(query5_1, query5_2, q5.content, (str(userID),))

        if self.tickType == 0:
            await user.send("**Question 6:** \nIs there anything you feel we can improve on regarding support? (if not just type NA).")
            try:
                q6 = await Client.wait_for("message", timeout = 300, check = check)
            except asyncio.TimeoutError:
                await dbaccess.write_to_db(query6_1, query6_2, "NA", (str(userID),))
                await user.send("Didn't get an input.")
                t = str(datetime.datetime.now())
                await dbaccess.write_to_db(query6_1_time, query6_2_time, t, (str(userID),))
                query = "UPDATE ticketsurvey SET name = %s WHERE name = %s"
                data = ("Survey Completed", str(userID))
                await dbaccess.write_data(query, data)
                await user.send("Thanks for taking part in the feedback form!")
            else:
                await dbaccess.write_to_db(query6_1, query6_2, q6.content, (str(userID),))
                t = str(datetime.datetime.now())
                await dbaccess.write_to_db(query6_1_time, query6_2_time, t, (str(userID),))
                query = "UPDATE ticketsurvey SET name = %s WHERE name = %s"
                data = ("Survey Completed", str(userID))
                await dbaccess.write_data(query, data)
                await user.send("Thanks for taking part in the feedback form!")
        else:
            await user.send("**Question 5:** \nIs there anything you feel we can improve on regarding support? (if not just type NA).")
            try:
                q6 = await Client.wait_for("message", timeout = 300, check = check)
            except asyncio.TimeoutError:
                await dbaccess.write_to_db(query5_1_welcome, query5_2_welcome, "NA", (str(userID),))
                await user.send("Didn't get an input.")
                t = str(datetime.datetime.now())
                await dbaccess.write_to_db(query6_1_time, query6_2_time, t, (str(userID),))
                query = "UPDATE ticketsurveywelcomes SET name = %s WHERE name = %s"
                data = ("Survey Completed", str(userID))
                await dbaccess.write_data(query, data)
                await user.send("Thanks for taking part in the feedback form!")
            else:
                await dbaccess.write_to_db(query5_1_welcome, query5_2_welcome, q6.content, (str(userID),))
                t = str(datetime.datetime.now())
                await dbaccess.write_to_db(query6_1_time, query6_2_time, t, (str(userID),))
                query = "UPDATE ticketsurveywelcomes SET name = %s WHERE name = %s"
                data = ("Survey Completed", str(userID))
                await dbaccess.write_data(query, data)
                await user.send("Thanks for taking part in the feedback form!")

 

The next part contains code for the database access, any query from the main or the survey files will go through this. With the code rewrite I was able to reuse the same functions many times whereas in my old code I would remake new functions when I did not need to. 

import psycopg
from psycopg import sql
import pandas as pd
import logging
import os
from dotenv import load_dotenv

load_dotenv()

logging.basicConfig(level=os.environ.get("LOGLEVEL", "DEBUG"), filename='jarvis.log', filemode='a', format='%(asctime)s - [Line:%(lineno)d - Function:%(funcName)s In:%(filename)s From:%(name)s] \n[%(levelname)s] - %(message)s \n', datefmt='%d-%b-%y %H:%M:%S')

DBTable = os.environ.get('DBTable')
DBHost = os.environ.get('DBHost')
DBUsr = os.environ.get('DBUsr')
DBPass = os.environ.get('DBPass')
DBPort = os.environ.get('DBPort')

# Returns wanted information as a df from the database given a query.
async def get_data_as_dataframe(query):
    async with await psycopg.AsyncConnection.connect(
        dbname=DBTable,
        user=DBUsr,
        password=DBPass,
        host=DBHost,
        port=DBPort
    ) as conn:
        try:
            logging.info("Opened database successfully")
            async with conn.cursor() as curr:
                # Execute the query to fetch the specific data
                await curr.execute(query)
                records = await curr.fetchall()
                logging.info("Pulled values")

                # Execute a separate query to get the total number of rows in the table
                total_query = "SELECT COUNT(*) FROM ticketsurvey"
                await curr.execute(total_query)
                total_rows = await curr.fetchone()
                total_rows = total_rows[0] if total_rows else 0
                logging.info("Fetched total rows")

        except Exception as e:
            logging.error(e)
            return pd.DataFrame(), 0  # Return an empty DataFrame and total rows 0 in case of error

    # Convert fetched records into a DataFrame
    df = pd.DataFrame(records)  # Specify column names
    return df, total_rows

# Returns wanted information from the database given a query.
async def get_data(query, val):
    async with await psycopg.AsyncConnection.connect(
        dbname=DBTable,
        user=DBUsr,
        password=DBPass,
        host=DBHost,
        port=DBPort
    ) as conn:
        try:
            logging.info("Opened database successfully")
            async with conn.cursor() as curr:
                await curr.execute(query, val)
                records = await curr.fetchall()
                logging.info("Pulled values")

                logging.info("Fetched total rows")

        except Exception as e:
            logging.error(e)
            
    return records

async def write_data(query, data):
    async with await psycopg.AsyncConnection.connect(dbname = DBTable, user = DBUsr, password = DBPass, host = DBHost, port = DBPort) as conn:
        try:
            logging.info("Opened database successfully")
            async with conn.cursor() as curr:
                await curr.execute(query, data)
                await conn.commit()
                logging.info("Pulled values and closed")

        except Exception as e:
            logging.error(e)

async def write_to_db(query1, query2, payload, user):
    async with await psycopg.AsyncConnection.connect(dbname = DBTable, user = DBUsr, password = DBPass, host = DBHost, port = DBPort) as conn:
        try:
            logging.info("Opened database successfully")
            async with conn.cursor() as curr:

                await curr.execute(query1, user)
                ticketsurvey = await curr.fetchall()

                for row in ticketsurvey:
                    chid = row[0]

                #data for the %s value
                data = (chid, user, payload)
                await curr.execute(query2, data)
                await conn.commit()
                logging.info("Pulled values and closed")

        except Exception as e:
            logging.error(e)

 

The last piece of code is for Discord embeds, but since there is nothing too important to share with that file I am not going to include it but I just wanted to mention it as it is imported by the other files above.

 

 

2. Post Ticket Surveys

 

This addition was introduced with the intent to improve the response rate of survey completion. The old method would make members leave Discord and that is why I think the response rate was as low as 2%. For this reason I designed the survey to go to the DMs in Discord to hopfully improve the response rate and at this point the response rate is 14-15% which is a huge improvement. 

 

 

There is also the addition to the ticket survey that aids the owners in reviewing the surveys in a fast and orderly manner.