Discord Bot - Other Projects

Python SQL Discord.py

---

About the project

I have made and continue to make many other bots for Notify and all provide a unique and valueable function to the group. Most of the bots use the same technology like Python (Discord.py, psycopg, asyncio, etc.) and SQL (PostgreSQL). I will go over all the other bots I have made that don't have their own page in this write up. Some of the functions of the bots are:

• Keyword pinger
  • Allows staff to add/remove/list keywords that serves to ping various roles based on keywords added.
  • Another kw pinger is used to monitor shopify checkpoints and ping a role when they go up.
• Member dm command
  • Allows staff to mass dm members who win giveaways with the license keys and whatever else is needed.
• Moderator
  • Moderates a channel used for selling items on our marketplace inside Notify, if a post does not meet requirements it is removed.
• Website raffle entries commands
• Importing guides from GitHub via a command
  • Allows anyone to generate a nicely formatted discord message containing any of the guides on Notify's website.
• Role assigning command
  • Used to check if a member has reviewed Notify on Whop.com and if so then it will give the member a specific role.
• Discord channel archiving command
• Website item stock scraping command
• Tracker for new members
  • They are assigned a new member role and once 3 months are up the bot will automatically remove it.
  • there is also a command to remove the role at anytime as well.
• Bot tracker
  • Tracks the status of some bots to make sure they are online.
• Review tracker
  • Pulls reviews off of Whop.com and send an embed of the review via webhook to a Discord channel in Notify.
• Marketplace features
  • Ticket system for members to track deals with other members. 
  • Command for users to get the info on other members deals.

 

 

1. Notify Pinger

This bot is used mainly for pinging throughout Notify's server. Along with with staff can add/remove/list all the keywords currently being monitored, this helps ensure members don't miss anything from our site monitors.

 

 

The bot also has hard coded pingers for site checkpoints that will ping a role when a Shopify checkpoint goes up. The code for this can be found below.

 

Main function containing the Discord bot.

# encoding: utf-8
import os
import discord
import time
import io
import requests
from requests.exceptions import Timeout
import asyncio
import re
import string
from discord import app_commands
import psycopg
import datetime
from dotenv import load_dotenv
load_dotenv()

from global_vars import GlobalVariables
import dbaccess
import pinger_utils
import embeds

#Bot Token
TOKEN = os.environ.get('PINGER_TOKEN')
intents = discord.Intents().all()
intents.members = True
intents.guilds = True

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 pinger_utils.setchannelpinger()
        print(f'{self.user} has connected to Discord!')

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

alert = os.environ.get('TEAM_MONITOR_ALERT')
GUILD = int(os.environ.get('NOTIFYGUILDID'))
NOTIFYPINGERROLE = int(os.environ.get('NOTIFYPINGERROLE'))
MONITORBOTROLE = int(os.environ.get('MONITORBOTROLE'))
CHECKPOINTCHANNEL = int(os.environ.get('CHECKPOINTCHANNEL'))
MARKETPLACECHANNEL = int(os.environ.get('MARKETPLACECHANNEL'))
NOTIFYHELPERROLE = int(os.environ.get('NOTIFYHELPERROLE'))
KIANROLE = int(os.environ.get('KIANROLE'))
ADMINCATEGORY = int(os.environ.get('LOYALMEMBERUSCATID'))

# Used to add keywords for pings in Notify
@client.command(name = "pingeradd", description = "Allows staff to add/edit ping kw(s) for any channel. Input just ID for role and channel.")
@app_commands.checks.has_role("Staff")
async def pingeradd(interaction: discord.Interaction, keyword: str, roleid: str, channelid: str):
    try:
        await pinger_utils.add_kw(keyword.lower(), "<@&" + str(roleid) + ">", channelid)
        await interaction.response.send_message("Added the data successfully.")
    except:
        await interaction.response.send_message("Something went wrong, please retry.")

# Used to remove keywords for pings in Notify
@client.command(name = "pingerremove", description = "Allows staff to remove ping kw(s) for sneaker-important. Needs the correct kw and channel id.")
@app_commands.checks.has_role("Staff")
async def pingerremove(interaction: discord.Interaction, keyword: str, channelid: str):
    try:
        await pinger_utils.remove_kw(str(keyword.lower()), str(channelid.lower()))
        await interaction.response.send_message("Deleted: " + str(keyword.lower()))
    except:
        await interaction.response.send_message("Something went wrong, please retry.")

# Used to list keywords for pings in Notify
@client.command(name = "pingerlist", description = "Allows staff to see a list of kws.")
@app_commands.checks.has_role("Staff")
async def pingerlist(interaction: discord.Interaction):
    try:
        embedVar = await pinger_utils.list_kw()
        await interaction.response.send_message(embed=embedVar, ephemeral=True)
    except:
        await interaction.response.send_message("Something went wrong, please retry.")

@Client.event
async def on_message(msg):

    # Pinger for KWs
    if msg.channel.id in GlobalVariables().pingchannelslist and msg.author.id != NOTIFYPINGERROLE:
        channel_id = msg.channel.id

        pm = await pinger_utils.send_kw_ping(msg)

        if pm:
            channel_cooldowns = GlobalVariables().channel_cooldowns
            if channel_id not in channel_cooldowns or (datetime.datetime.now() - channel_cooldowns[channel_id]).total_seconds() >= 600:
                await msg.reply(pm)
                channel_cooldowns[channel_id] = datetime.datetime.now()

    # Used to ping when certain sites drop checkpoint.
    if msg.channel.id == CHECKPOINTCHANNEL and msg.author.id == MONITORBOTROLE:
        contents = ""
        channel = msg.channel
        embeds = msg.embeds

        mapping = {
            "shoepalace": "Checkpoint enabled for Shoepalace | <#1042880604234063912> <@&788278120700182538>",
            "snk": "Checkpoint enabled for ShopNiceKicks | <#1042880604234063912> <@&788278120700182538>",
            "kith": "Checkpoint enabled for Kith | <#1042880604234063912> <@&788278120700182538>",
            "DSMShopUS": "Checkpoint enabled for DSM | <#1042880604234063912> <@&788278120700182538>",
            "livestock": "Checkpoint enabled for Deadstock | <#754052922169622688> <@&570142927729328128>",
            "haven": "Checkpoint enabled for Haven | <#754052879006040064> <@&570142927729328128>",
            "sizeca": "Checkpoint enabled for Size | <@&570142927729328128>",
            "canaryyellow": "Checkpoint enabled for Off White | <#1042880604234063912> <@&788278120700182538>",
            "jdsportsca": "Checkpoint enabled for JD Canada | <@&570142927729328128>",
            "mambaandmambacita": "Checkpoint enabled for Mambacita | <#1042880604234063912> <@&788278120700182538>",
            "supremeus": "Checkpoint enabled for Supreme US | <@&788278120700182538>",
            "amamaniere": "Checkpoint enabled for A-Ma-Maniere | <#1042880604234063912> <@&788278120700182538>",
            "socialstatus": "Checkpoint enabled for Social Status | <#1042880604234063912> <@&788278120700182538>",
            "union": "Checkpoint enabled for Union | <#1042880604234063912> <@&788278120700182538>",
            "concepts": "Checkpoint enabled for CNCPTS | <#1042880604234063912> <@&788278120700182538>"
        }

        for embed in embeds:
            contentStringAuthor = str(embed.author)
            contentStringTitle = str(embed.title)

            for substring, message in mapping.items():
                if substring in contentStringAuthor.lower() and "checkpoint" in contentStringTitle.lower() and "enabled" in contentStringTitle.lower():
                    await channel.send(message)
                    break

Client.run(TOKEN)

 

The next file contains some functions used by the main file to handle the pings.

import discord

from global_vars import GlobalVariables
import dbaccess
import embeds

async def setchannelpinger():
    users_data = {}
    user_data_list = []
    query = "select * from pingerkw"
    records = await dbaccess.get_data(query, None)

    for row in records:
        pk_id = row[0]
        user_data = {
            "role": row[1],
            "channel": row[2],
            "keyword": row[3]
        }
        users_data[pk_id] = user_data
        user_data_list.append(int(row[2]))

    GlobalVariables().pingchannelslist = user_data_list
    GlobalVariables().pingchannels = users_data

async def add_kw(keyword, roleid, channelid):
    query = "INSERT INTO pingerkw (keyword, rolestr, channel) VALUES (%s, %s, %s)"

    await dbaccess.write_data(query, (keyword, roleid, channelid))
    await setchannelpinger()

async def remove_kw(keyword, channelid):
    query = "DELETE FROM pingerkw WHERE (keyword, channel) = (%s, %s)"

    await dbaccess.write_data(query, (keyword, channelid))
    await setchannelpinger()

async def list_kw():
    query = "select * from pingerkw"
    records = await dbaccess.get_data(query, None)

    desc = ""
    for row in records:
        desc = desc + "Keyword: " + row[3] + " | Message (Ping): " + row[1] + " | Channel: <#" + row[2] + ">\n"

    embedVar = await embeds.kw_list(desc)

    return embedVar

async def send_kw_ping(msg):

    pm = ""
    embeds = msg.embeds

    if msg.embeds:
        for embed in embeds:
            for key,value in GlobalVariables().pingchannels.items():
                if msg.channel.id != int(value["channel"]):
                    continue  # Skip iteration if channel doesn't match
                if value["keyword"] in (embed.description.lower() if embed.description else "") or value["keyword"] in (embed.title.lower() if embed.title else ""):
                    pm = f"Ping on kw {value['keyword']} {value['role']}"
                    break  # Exit loop since keyword found
    else:
        for key,value in GlobalVariables().pingchannels.items():
            if value["keyword"] in msg.content.lower() and msg.channel.id == int(value["channel"]):
                pm = f"Ping on kw {value['keyword']} {value['role']}"

    return pm

 

The next part contains code for the database access, any query from the main or pinger utils files will go through this.

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

from global_vars import GlobalVariables

load_dotenv()

log_file_path = os.path.join('logs', 'dblog.log')
logging.basicConfig(level=os.environ.get("LOGLEVEL", "DEBUG"), filename=log_file_path, 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. Set value to None if you aren't passing in data.
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:
                if val != None:
                    await curr.execute(query, val)
                else:
                    await curr.execute(query)
                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("Written 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("Written values and closed")

        except Exception as e:
            logging.error(e)

 

There is also an embed file and global variable file as well to manage some embeds and other variables but it is not going to be included.

 

 

2. Notify Utilities

This bot is mainly used for various commands that provide utility to members and staff. Some of the commands are:

• Mass member dms
• Website raffle entries
• Importing guides from GitHub
• Assigning roles to members
• Archiving channels
• Getting stock from various websites

As well as an automod that moderates a channel used for selling items on our marketplace inside Notify, if a post does not meet requirements it is removed.

 

Main function containing the Discord bot.

import os
import discord
import chat_exporter
import io
from discord import app_commands
from bs4 import BeautifulSoup
import requests
import asyncio
import requests_html
import json
from discord.utils import get
import random
from github import Github
from dotenv import load_dotenv
load_dotenv()

from global_vars import GlobalVariables
import dbaccess
import utilities_utils
import embeds

#Bot Token
TOKEN = os.environ.get('UTILITIES_TOKEN')
intents = discord.Intents().all()
intents.members = True

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

    async def on_ready(self):
        await self.wait_until_ready()
        if not self.synced:
            await client.sync()
            self.synced = True
        print(f'{self.user} has connected to Discord!')

        #sets some variables used in welcome.py
        await utilities_utils.set_vars(Client)

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

GUILD = int(os.environ.get('NOTIFYGUILDID'))
MARKETPLACECHANNEL = int(os.environ.get('MARKETPLACECHANNEL'))
NOTIFYHELPERROLE = int(os.environ.get('NOTIFYHELPERROLE'))
KIANROLE = int(os.environ.get('KIANROLE'))
ADMINCATEGORY = int(os.environ.get('LOYALMEMBERUSCATID'))
STAFFROLE = int(os.environ.get('STAFFROLE'))

# Used to mass send dms
@client.command(
    name = "memberdm", 
    description = "DMs winners of giveaways their bot key and information."
)
@app_commands.checks.has_role("Staff")
async def memberdm(interaction: discord.Interaction, m: str):
    await utilities_utils.send_member_dms(interaction, m)

# Used to get entries into ufc fight raffles. Formstack site.
@client.command(
    name = "ufcenrollmsg", 
    description = "100 proxies & 100 gmails or 1 catchall. Separate input with comma no spaces."
)
async def ufcenrollmsg(interaction: discord.Interaction, proxy: str, emails: str):

    await interaction.response.send_message("Submitting entries now...")

    loop = asyncio.get_running_loop()
    time, issues = await loop.run_in_executor(None, utilities_utils.msg, proxy, emails)
    i = ', '.join([str(item) for item in issues])
    await interaction.channel.send("Finished Site #1 in " + str(time) + " seconds.\nYou also had a failed entry on entries " + str(i) + ".")

# Used to get entries into ufc fight raffles. UFC site.
@client.command(
    name = "ufcenrollufc", 
    description = "100 proxies & 100 gmails or 1 catchall. Separate input with comma no spaces."
)
async def ufcenrollufc(interaction: discord.Interaction, proxy: str, emails: str):

    await interaction.response.send_message("Submitting entries now...")

    loop = asyncio.get_running_loop()
    time, issues = await loop.run_in_executor(None, utilities_utils.ufc, proxy, emails)
    i = ', '.join([str(item) for item in issues])
    await interaction.channel.send("Finished Site #2 in " + str(time) + " seconds.\nYou also had a failed entry on entries " + str(i) + ".")

# Converting the guides from the site stored on github and sending in discord.
@client.command(
    name = "importguide", 
    description = "Allows guides from the hub to be published to a channel."
)
@app_commands.describe(choices="Start typing some guide you want and options will show up above.")
@app_commands.autocomplete(choices=utilities_utils.guide_autocomplete)
async def importguide(interaction: discord.Interaction, choices: str):
    # Gets the wanted guide from the hub
    g = Github('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')
    repo = g.get_repo('vildzi/notify-site')
    contents = repo.get_contents('outstatic/content/guides/' + choices + '.md')
    decoded = str(contents.decoded_content.decode('utf-8'))

    # Cleaning the string up
    sep = decoded.lstrip().split('---')[2]
    cleaned_str = sep.replace(r"\n", "\n").replace("\n\n    <!-- -->\n\n    <!-- -->\n\n    <!-- -->", "").replace("\n\n  ", "\n").replace("\n\n-", "\n-").replace("\n\n<!-- -->\n\n<!-- -->", "").replace("[https://", "[").replace("png)", "png").replace("webp)", "webp").replace("jpg)", "jpg").replace("![](/images/", "https://notify.org/images/").replace(".png", ".png\n\n").replace(".jpg", ".jpg\n\n").replace(".webp", ".webp\n\n")

    # Splits the string by a delimiter but keeps the delimiter
    splitstring = [e+"\n\n" for e in cleaned_str.split("\n\n") if e]

    # Combine the list elements up to a specific length for discord max of 2000
    split = []
    buff = splitstring[0] if len(splitstring) > 0 else ""
    for i in range(1,len(splitstring)):
        t = splitstring[i]
        if "https://notify.org/images/" in t:
            if "https://notify.org/images/" not in splitstring[i-1]: 
                split.append(buff)
            split.append(t)
            buff = ""
        elif (len(buff) + len(t) + 1) <= 1975:
            buff += t
        else:
            split.append(buff)
            buff = t
    if len(buff) > 0:
        split.append(buff)

    # Sending each string in the list to make the full guide
    title = "# " + choices.replace("-", " ")
    await interaction.response.send_message(title)
    for i in split:
        await interaction.channel.send(i)

# Get reviews and member relation
@client.command(name = "reviews")
async def reviews(interaction: discord.Interaction):

    if interaction.user.id == KIANROLE:
        await interaction.response.send_message("checking now, may take a little since there is a 5s delay between each request.")

        reviewed, countdonthave, counthave, peopletotal, idDict, noacc = await utilities_utils.reviews_finder()

        await interaction.channel.send(reviewed + " Total reviewers according to Whop. " + countdonthave + " members were given the Reviewer role. " + counthave + " have the role already. ")
        await interaction.channel.send(peopletotal + " Total Notify customers according to Whop. " + idDict + " Total customers added that HAVE accounts. " + noacc + " DON'T have accounts.")

    else:
        await interaction.response.send_message("You don't have access to this command.")

# Get non-reviewers and make list
@client.command(name = "nonreviewers")
async def nonreviewers(interaction: discord.Interaction):

    if interaction.user.id == KIANROLE:
        await interaction.response.send_message("fetching now, may take a little since there is a 30s delay between each 100 members requested.")

        tempMembers, tempEmail = await utilities_utils.non_reviews_finder()

        with open("nonreviewer.txt", "w", encoding="utf-8") as file:
            file.write(tempMembers)

        with open("nonrevieweremail.txt", "w", encoding="utf-8") as file:
            file.write(tempEmail)

        with open("nonreviewer.txt", "rb") as file:
            await interaction.channel.send("Non-Reviewers: ", file=discord.File(file, "nonreviewer.txt"))

        with open("nonrevieweremail.txt", "rb") as file:
            await interaction.channel.send("Non-Reviewers Email Only: ", file=discord.File(file, "nonrevieweremail.txt"))
    else:
        await interaction.response.send_message("You don't have access to this command.")

# Archives channels in Notify so they can be saved before being deleted
@client.command(name = "archivechannel", description = "Archives Discord channels as an HTML file")
async def archivechannel(interaction: discord.Interaction, channel_id: str):
    ch = Client.get_channel(int(channel_id))

    await interaction.response.send_message("Archiving, please wait.")

    transcript = await chat_exporter.export(ch)

    transcript_embed = discord.Embed(
        description=f"**Transcript Name:** transcript-{ch.name}\n\n",
        colour=discord.Colour.blurple()
    )

    transcript_file = discord.File(io.BytesIO(transcript.encode()),filename=f"transcript-{ch.name}.html")

    await interaction.channel.send(embed=transcript_embed, file=transcript_file)

# Gets all the Notify Staff
@client.command(name = "getstaffroles")
async def getstaffroles(interaction: discord.Interaction):
    guild = Client.get_guild(GUILD)
    staff = guild.get_role(STAFFROLE)
    st = [str(m.id) for m in staff.members]
    separator = '", "'
    st = '"' + separator.join(st) + '"'

    await interaction.response.send_message(st)

# Member/Staff Commands
@client.command(name = "kian", description = "Gets Kian's PRs")
async def kian(interaction: discord.Interaction):
    embedVar = await embeds.kian()
    await interaction.response.send_message(embed=embedVar)

# Member/Staff Commands
@client.command(name = "cringe", description = "This is for Mike (aka. 18pairsonkith)")
async def cringe(interaction: discord.Interaction):
    embedVar = await embeds.mike()
    await interaction.response.send_message(embed=embedVar)

# Member/Staff Commands
@client.command(name = "p3bl3z", description = "Some classic P3bl3z Quotes")
async def p3bl3z(interaction: discord.Interaction):
    embedVar = await embeds.peblez()
    await interaction.response.send_message(embed=embedVar)

# Links embed for ease of access
@client.command(name = "guides", description = "Gets all the links to Notify guides")
async def guides(interaction: discord.Interaction):
    embedVar = await embeds.guides()
    await interaction.response.send_message(embed=embedVar, ephemeral=True)

# SP Variants and Stock
@client.command(name = "spstock", description = "Gets stock/vars of products on Shoe Palace", guild = discord.Object(id = 570142274902818816))
async def spstock(interaction: discord.Interaction, url: str):
    url = url + ".json"
    try:
        page = requests.get(url)
        page_json = page.json()
        titleJson = page_json["product"]
        title = titleJson['title']
        stock_embed = discord.Embed(
            title= title + f"\n\n",
            colour=0xDB0B23
        )
        jsonData = page_json["product"]["variants"]
        for x in jsonData:
            stock_embed.add_field(name = "Size, Variant, `Quantity`", value = str(x['title']) + ", " + str(x['id']) + ", `{}".format(str(x['inventory_quantity'])) + "`", inline = True)

        await interaction.response.send_message(embed=stock_embed)
    except:
        await interaction.response.send_message("Wrong Name/Doesn't Exist")

# JJ Variants and Stock
@client.command(name = "jjstock", description = "Gets stock/vars of products on Jimmy Jazz", guild = discord.Object(id = 570142274902818816))
async def jjstock(interaction: discord.Interaction, url: str):
    try:
        page = requests.get(url)
        soup = BeautifulSoup(page.content, "html.parser")
        s = soup.find_all('script')
        stock = str(s)
        stockList = []
        for item in stock.split("\n"):
            if "window.inventories['" in item:
                stockList.append(item[item.find("]["):].replace("[", "").replace("]", "").replace(";", "").replace(" = ", ", "))

        sizes = str(s)
        sizeList = []
        for item in sizes.split("\n"):
            if "window.productJSON" in item:
                sizeList.append(item.strip())

        sizestripped = sizeList[0].replace("window.productJSON = ", "").replace(";", "")
        sizeJson = json.loads(sizestripped)
        titleJson = sizeJson["title"]
        handleJson = sizeJson["handle"]
        stock_embed = discord.Embed(
            title = titleJson + " (" + handleJson + f")\n\n",
            colour = 0xDB0B23
        )
        jsonData = sizeJson["variants"]
        for x in jsonData:
            elem = str(x['id'])
            index_pos = [i for i, s in enumerate(stockList) if elem in s]
            stock_embed.add_field(name = "Size, Variant, Quantity", value = str(x['title']) + ", " + stockList[int(index_pos[0])], inline = True)

        await interaction.response.send_message(embed=stock_embed)
    except:
        await interaction.response.send_message("Wrong Name/Doesn't Exist or Some Code Issue/Change.")

# NB Variants
@client.command(name = "nbstock", description = "Gets vars of products on NB", guild = discord.Object(id = 570142274902818816))
async def nbstock(interaction: discord.Interaction, url: str):

    session = requests_html.HTMLSession()
    r = session.get(url)

    print(r.text)

    varBlock = ""
    varsFinal = ""
    varsFinalcomma = ""

    for item in r.html.xpath('//*[@id="maincontent"]/script[3]'):
        varBlock = item.text
    varSep = varBlock[varBlock.find('"variants":'):]
    varSepSep = varSep.split(', "master":',1)[0]
    varSepSepSep = varSepSep[varSepSep.find('{'):]
    varJson = json.loads(varSepSepSep)
    for x in varJson:
        varsFinal += x + "\n"
        varsFinalcomma += x + ","

    varsall = varsFinal

    stock_embed = discord.Embed(
        title= url + f"\n\n",
        description = varsFinal,
        colour=0xDB0B23
    )

    stock_embed_comma = discord.Embed(
        title= url + f"\n\n",
        description = varsFinalcomma,
        colour=0xDB0B23
    )

    await interaction.response.send_message(embed=stock_embed)
    await interaction.channel.send(embed=stock_embed_comma)

# Get members with no roles
@client.command(name = "hasnoroles")
async def hasnoroles(interaction: discord.Interaction):
    if interaction.user.id == KIANROLE:
        members = interaction.guild.members
        memberstr = ""
        for member in members:
            if len(member.roles) == 1:
                memberstr = memberstr + "<@!" + str(member.id) + "> "

        await interaction.response.send_message("List of members with no roles: " + memberstr)
    else:
        await interaction.response.send_message("You don't have access to this command.")

# Get x number of members with certain role
@client.command(name = "randomselect")
async def randomselect(interaction: discord.Interaction, role: str, number: int):
    guild = Client.get_guild(GUILD)
    notifyrole = discord.utils.find(lambda r: r.name == "Staff", interaction.channel.guild.roles)

    if notifyrole in interaction.user.roles:
        members = interaction.guild.members
        memberlist = []

        for member in members:
            for roles in member.roles:
                if role in roles.name:
                    memberlist.append(member.id)

        sample = random.sample(memberlist, number)
        strmem = ""

        for i in sample:
            strmem = strmem + "<@!" + str(i) + "> "

        await interaction.response.send_message("Random list of members:\n\n" + strmem)
    else:
        await interaction.response.send_message("You don't have access to this command.")

@Client.event
async def on_message(msg):
    # Used to remove and dm members if their wts posts don't contain monetary amounts
    if msg.channel.id == MARKETPLACECHANNEL:
        userId = str(msg.author.id)
        user = await Client.fetch_user(userId)

        if any(keyword in msg.content.lower() for keyword in ["$", "shipped", "shipping"]) or msg.author.id == NOTIFYHELPERROLE:
            pass
        else:
            await msg.delete()
            try:
                await user.send("Hey, please include the price in your WTS post, I check for the use of a '$', 'shipped', or 'shipping' to make sure there is a price so please include at least a '$', I have attached your deleted message for you to update.")
                await user.send("`" + msg.content + "`")
            except (discord.errors.Forbidden, discord.errors.HTTPException):
                staff = await Client.fetch_user(KIANROLE)
                guild = Client.get_guild(GUILD)
                user = msg.author
                cat = discord.utils.get(guild.categories, id = ADMINCATEGORY)

                overwrites = {
                    guild.default_role: discord.PermissionOverwrite(read_messages=False), #Make default not able to view this private channel
                    guild.me: discord.PermissionOverwrite(read_messages=True), #Add the bot to the channel
                    user: discord.PermissionOverwrite(read_messages=True),
                    staff: discord.PermissionOverwrite(read_messages=True, manage_channels=True)
                }

                ch = await guild.create_text_channel("WTS Issue-" + str(user), overwrites = overwrites, category = cat)
                await ch.send("Hey, please include the price in your WTS post, I check for the use of a '$', 'shipped', or 'shipping' to make sure there is a price so please include at least a '$', I have attached your deleted message for you to update.")
                await ch.send("`" + msg.content + "`")

Client.run(TOKEN)

 

The next file contains some functions used by the main file to make the bot more organized.

import discord
import names
import datetime
import os
import aiohttp
import asyncio
import json
from itertools import cycle, islice
from seleniumwire import webdriver
from selenium.webdriver.chrome.service import Service
from webdriver_manager.chrome import ChromeDriverManager
from selenium.webdriver.common.by import By
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import TimeoutException
from typing import List
from github import Github
from discord import app_commands

import embeds
import dbaccess

Client = None

GUILD = int(os.environ.get('NOTIFYGUILDID'))
WHOP_API_KEY = os.environ.get('WHOP_API_KEY')

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

    Client = c

# Used by the NotifyUtilities.py to send keys to members
async def send_member_dms(interaction, m):
    # ID retrieval
    message_parts = m.split('/')
    message_id = int(message_parts.pop())
    channel_id = int(message_parts.pop())
    channel = Client.get_channel(channel_id)
    msg = await channel.fetch_message(message_id)

    # Extract user IDs and mentions
    users = [mention.id for mention in msg.mentions]
    user_mentions = [mention.mention for mention in msg.mentions]
    user_string = ', '.join(user_mentions)

    # Replies saying it found members
    found_embed = await embeds.memberdm_found(str(len(msg.mentions)))
    await interaction.response.send_message(embed=found_embed)

    # Check before waiting response
    def check(mes):
        return mes.channel == interaction.channel and mes.author != Client.user

    # Ask for user response and wait for it
    keystring = await Client.wait_for("message", check = check)
    if keystring.content == "restart":
        return

    # Put the keys into a list
    keys = keystring.content.split(', ')

    # Check if same amount of keys as winners
    if len(keys) != len(users):
        await interaction.channel.send("Key to Winners is not equal, please restart.")
        return

    # What did they win
    what_embed = await embeds.memberdm_what()
    await interaction.channel.send(embed=what_embed)
    what = await Client.wait_for("message", check=check)
    if what.content == "restart":
        return

    # Ask for required URL
    url_embed = await embeds.memberdm_url()
    await interaction.channel.send(embed=url_embed)
    url = await Client.wait_for("message", check=check)
    if url.content == "restart":
        return

    # Ask for a message to send
    message_embed = await embeds.memberdm_message()
    await interaction.channel.send(embed=message_embed)
    dm = await Client.wait_for("message", check = check)
    if dm.content == "restart":
        return

    # Setup sent dm and show ex
    ex_embed = await embeds.memberdm_ex(userstring, str(url.content), str(dm.content))
    await interaction.channel.send(embed=ex_embed)

    # Confirm if correct and send/check/open ticket if no dm is successful
    yes_no = await Client.wait_for("message", check = check)

    if str(yes_no.content) == "n":
        await interaction.channel.send("Please restart.")
    elif str(yes_no.content) == "y":
        await interaction.channel.send("Dming members now.")

        outputstring = ""
        for i, key in enumerate(keys):
            send_embed = await embeds.memberdm_send(str(what.content), key, url.content, dm.content)
            temp = await Client.fetch_user(users[i])
            try:
                await temp.send(embed = send_embed)
                outputstring += f"DM to {temp} ✅ SUCCESSFUL\n"
            except (discord.errors.Forbidden, discord.errors.HTTPException):
                outputstring += f"DM to {temp} ❌ FAILED, Opening Ticket.\n"

                staffId = str(interaction.user.id)
                staff = await Client.fetch_user(staffId)
                guild = Client.get_guild(GUILD)
                user = temp
                cat = discord.utils.get(guild.categories, id = 806215492695883786)

                overwrites = {
                    guild.default_role: discord.PermissionOverwrite(read_messages=False), #Make default not able to view this private channel
                    guild.me: discord.PermissionOverwrite(read_messages=True), #Add the bot to the channel
                    user: discord.PermissionOverwrite(read_messages=True),
                    staff: discord.PermissionOverwrite(read_messages=True, manage_channels=True)
                }

                ch = await guild.create_text_channel(f"giveaway winner-{temp}", overwrites=overwrites, category=cat)
                await ch.send(embed=send_embed)

        output_embed = await embeds.memberdm_output(outputstring)

        await interaction.channel.send(embed = output_embed)
    else:
        await interaction.channel.send("Only answer with y/n. Restart please.")

# Used to fill in UFC entry forms
async def fill_form(browser, proxylist, emails, isgmail, form_url, field_ids):
    issues = []
    for i in range(25):
        try:
            proxy_parts = proxylist[i].split(":")
            browser.proxy = {
                'http': f'http://{proxy_parts[2]}:{proxy_parts[3]}@{proxy_parts[0]}:{proxy_parts[1]}',
                'https': f'https://{proxy_parts[2]}:{proxy_parts[3]}@{proxy_parts[0]}:{proxy_parts[1]}'
            }
            browser.get(form_url)
            wait = WebDriverWait(browser, 10)
            element = wait.until(EC.presence_of_element_located((By.ID, field_ids['first_name'])))
            first_name = browser.find_element(By.ID, field_ids['first_name'])
            last_name = browser.find_element(By.ID, field_ids['last_name'])
            email_field = browser.find_element(By.ID, field_ids['email'])
            submit_button = browser.find_element(By.ID, field_ids['submit'])

            first = names.get_first_name()
            last = names.get_last_name()
            first_name.send_keys(first)
            last_name.send_keys(last)
            email = emails.split(",")[i] if isgmail else first + last + emails
            email_field.send_keys(email)
            submit_button.click()

            wait.until(lambda driver: browser.current_url != form_url)
        except Exception as e:
            print(f"Exception: {str(e)}\nMay be due to a proxy being banned on the site.")
            issues.append(i)
    return issues

# Creates the browser
async def initialize_browser(headless):
    options = Options()
    if headless == 1:
        options.add_argument('--headless=new')
    options.add_argument('--no-sandbox')
    options.add_argument('--disable-logging')
    options.add_argument("--disable-blink-features=AutomationControlled")
    options.add_experimental_option("excludeSwitches", ["enable-automation"]) 
    options.add_experimental_option("useAutomationExtension", False) 
    options.binary_location = "C:\Program Files\Google\Chrome\Application\chrome.exe"
    return webdriver.Chrome(options=options, service=Service(ChromeDriverManager().install()))

# Used to send entries to Formstack for UFC
async def msg(proxy, emails):
    isgmail = "@gmail.com" in emails
    browser = await initialize_browser(0)
    proxylist = list(islice(cycle(proxy.split(",")), 25))
    time = datetime.datetime.now()
    issues = await fill_form(browser, proxylist, emails, isgmail, "https://msg-wmzqo.formstack.com/forms/ufc_295", 
                       {'first_name': "field147946152-first", 'last_name': "field147946152-last", 
                        'email': "field147946153", 'submit': "fsSubmitButton5363561"})
    browser.close()
    browser.quit()
    print(datetime.datetime.now() - time)
    return (datetime.datetime.now() - time), issues

# Used to send entries for UFC
async def ufc(proxy, emails):
    isgmail = "@gmail.com" in emails
    browser = await initialize_browser(1)
    proxylist = list(islice(cycle(proxy.split(",")), 25))
    time = datetime.datetime.now()
    issues = await fill_form(browser, proxylist, emails, isgmail, "https://form.jotform.com/231917397920161", 
                       {'first_name': "input_3", 'last_name': "input_4", 'email': "input_5", 'submit': "input_2"})
    browser.close()
    browser.quit()
    print(datetime.datetime.now() - time)
    return (datetime.datetime.now() - time), issues

# Gets all the guides you can pull from git.
async def get_guides():
    strippedlist = []
    g = Github('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')
    repo = g.get_repo('vildzi/notify-site')
    c = repo.get_contents("outstatic/content/guides")
    while len(c) >= 1:
        file_content = c.pop(0)
        if file_content.type == "dir":
            c.extend(repo.get_contents(file_content.path))
        else:
            stripped = file_content.path.lstrip().split('outstatic/content/guides/')[1].rstrip().split(".")[0]
            strippedlist.append(stripped)

    strippedlist.pop(0)

    return strippedlist

# Allows to have variable selection length in discord dropdown
async def guide_autocomplete(
    interaction: discord.Interaction, 
    current:str, 
) -> List[app_commands.Choice[str]]:
    choices = await get_guides()
    return [
        app_commands.Choice(name=choice, value=choice)
        for choice in choices if current.lower() in choice.lower()
    ]

async def reviews_finder():
    headers = {
        "accept": "application/json",
        "Authorization": "Bearer " + WHOP_API_KEY
    }
    async with aiohttp.ClientSession(headers=headers) as session:
        i = 1
        noacc = 0
        idDict = {}
        idList = []
        guild = Client.get_guild(GUILD)
        role = discord.utils.find(lambda r: r.name == 'Reviewer', guild.roles)

        # Gets the discord ids of each whop user and makes a dict with them
        pagestotal = 0
        peopletotal = 0
        url = "https://api.whop.com/api/v2/customers?page=1&per=50"
        async with session.get(url) as r:
            data = await r.read()
            page_json = json.loads(data)

            pagestotal = int(page_json["pagination"]["total_page"])
            peopletotal = int(page_json["pagination"]["total_count"])

        while i <= pagestotal:
            await asyncio.sleep(5)
            url = "https://api.whop.com/api/v2/customers?page=" + str(i) + "&per=50"

            async with session.get(url) as r:
                data = await r.read()
                page_json = json.loads(data)

                if not page_json["data"]:
                    break
                else:
                    j = 0
                    while j < 50:
                        try:
                            if page_json["data"][j]["social_accounts"]:
                                whopid = page_json["data"][j]["id"]
                                discid = page_json["data"][j]["social_accounts"][0]["id"]
                                idDict[whopid] = str(discid) 
                            else:
                                noacc = noacc + 1
                        except Exception as e:
                            print("End of list of users: " + str(e))
                        j = j + 1
            i = i + 1

        # Gets the whop ids of users that reviewed
        i = 1
        reviewed = 0
        while i <= 1000:
            url = "https://api.whop.com/api/v2/reviews?page=" + str(i) + "&per=50"

            async with session.get(url) as r:
                data = await r.read()
                page_json = json.loads(data)

                if not page_json["data"]:
                    break
                else:
                    j = 0
                    while j < 50:
                        try:
                            reviewed = int(page_json["pagination"]["total_count"])
                            whopid = page_json["data"][j]["user"]
                            idList.append(whopid)
                        except Exception as e:
                            print("End of list of reviews: " + str(e))
                        j = j + 1
            i = i + 1

        # Compares and assigns the new roles as needed.
        countdonthave = 0
        counthave = 0
        for k in idList:
            if k in idDict:
                try:
                    userid = idDict[k]
                    user = guild.get_member(int(userid))
                    if role not in user.roles:
                        await user.add_roles(role)
                        countdonthave = countdonthave + 1
                    else:
                        counthave = counthave + 1
                except Exception as e:
                    print("Exception: " + str(e))
                    print(k + " " + idDict[k])

    return str(reviewed), str(countdonthave), str(counthave), str(peopletotal), str(len(idDict)), str(noacc)

async def non_reviews_finder():
    guild = Client.get_guild(GUILD)
    headers = {
        "accept": "application/json",
        "Authorization": "Bearer " + WHOP_API_KEY
    }
    roleAllAccess = discord.utils.find(lambda r: r.name == 'All Access', guild.roles)
    roleReviewer = discord.utils.find(lambda r: r.name == 'Reviewer', guild.roles)
    tempMembers = ""
    tempEmail = ""
    timepause = 0
    async with aiohttp.ClientSession(headers=headers) as session:
        for member in guild.members:
            if not roleReviewer in member.roles and roleAllAccess in member.roles: 

                if timepause == 100:
                    await asyncio.sleep(30)
                    timepause = 0

                url = f"https://api.whop.com/api/v2/customers/{member.id}"
                email = ""
                try:
                    async with session.get(url) as r:
                        data = await r.read()
                        page_json = json.loads(data)
                        if page_json["email"]:
                            email = page_json["email"]
                        else:
                            email = f"No email for {member.name}"
                except Exception as e:
                    email = f"No Whop account for {member.name}"

                tempMembers = tempMembers + "ID: <@" + str(member.id) + ">  |  Email: " + email + "\n"
                tempEmail = tempEmail + email + "\n"

            timepause = timepause + 1

    return tempMembers, tempEmail

 

There is also an embed file as well to manage some embeds but it is not going to be included.

 

 

3. Notify Tracker

Along with the marketplace features you can find below, this bot also serves to check for new member roles and to remove it once members hit 3 months. They can also use a command to remove the role as well. The bot also tracks the status of other bots to make sure they are online.

 

Marketplace feature

Notify, like many other peer-to-peer (p2p) marketplaces, has encountered its fair share of scams. Unfortunately, scams permeate various platforms, and to address this issue, I developed a tool designed to track completed deals. The objective is to foster greater trust among members and provide staff with specific information to more efficiently handle potential issues.

 

Main function containing the Discord bot.

import os
import discord
from discord import app_commands
import asyncio
import datetime
from datetime import date
from dotenv import load_dotenv
load_dotenv()

from global_vars.global_vars import GlobalVariables
from database import dbaccess
from utils import tracker_utils
from embeds import embeds

#Bot Token
TOKEN = os.environ.get('TRACKER_TOKEN')
intents = discord.Intents().all()
intents.members = True
intents.guilds = True

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
        print(f'{self.user} has connected to Discord!')

        #sets some variables used in welcome.py
        await tracker_utils.set_vars(Client)

        await asyncio.gather(tracker_utils.tracker(), tracker_utils.checker(), tracker_utils.muted(), tracker_utils.reviews())

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

GUILD = int(os.environ.get('NOTIFYGUILDID'))
STAFFROLE = int(os.environ.get('STAFFROLE'))
NEWMEMBERROLE = int(os.environ.get('NEWMEMBERROLE'))
ADMINCATEGORY = int(os.environ.get('LOYALMEMBERUSCATID'))

# On member join give them @New Member Role
@Client.event
async def on_member_join(member):
    guild = Client.get_guild(GUILD)
    rolenewmember = guild.get_role(NEWMEMBERROLE)

    await member.add_roles(rolenewmember)

# Update member role to remove New Member if they get staff role
@Client.event
async def on_member_update(before, after):
    guild = Client.get_guild(GUILD)
    rolestaff = guild.get_role(STAFFROLE)
    rolenewmember = guild.get_role(NEWMEMBERROLE)

    if rolestaff in after.roles:
        await after.remove_roles(rolenewmember)

# Command to remove the of the user of the command
@client.command(name = "optout")
async def optout(interaction: discord.Interaction):
    try:
        guild = Client.get_guild(GUILD)
        rolenewmember = guild.get_role(NEWMEMBERROLE)

        await interaction.user.remove_roles(rolenewmember)
        await interaction.response.send_message("Removed the New Member role.")
    except:
        await interaction.response.send_message(f"Issue with the command, you must use the command inside of Notify for it to work.")

# Command to remove the role of a specific member
@client.command(name = "optoutid")
async def optoutid(interaction: discord.Interaction, memberid: str):
    try:
        guild = Client.get_guild(GUILD)
        rolenewmember = guild.get_role(NEWMEMBERROLE)
        member = guild.get_member(int(memberid))

        await member.remove_roles(rolenewmember)
        await interaction.response.send_message("Removed the New Member role.")
    except:
        await interaction.response.send_message(f"Issue with the command, you must use the command inside of Notify for it to work.")

# Command to allow members to check how much time is left
@client.command(name = "timeinnotify")
async def timeinnotify(interaction: discord.Interaction):
    try:
        now = datetime.datetime.now()
        then = interaction.user.joined_at.replace(tzinfo=None)
        diff = now - then

        await interaction.response.send_message(f"You have been in Notify for {diff}")
    except:
        await interaction.response.send_message(f"Issue with the command, you must use the command inside of Notify for it to work.")

# Marketplace command to get a users deals embed
@client.command(name = "marketinfo", description = "Used to get marketplace rating(s) of a user (format is userid: ex: 100108221280186368)")
async def marketinfo(interaction: discord.Interaction, user_id: str):
    try:
        buyer_stats, seller_stats = await tracker_utils.market_info(user_id)
        username = await Client.fetch_user(int(user_id))
        embedVar = await embeds.market_info(str(username.display_name), buyer_stats, seller_stats)

        await interaction.response.send_message(embed=embedVar)
    except Exception as e:
        await interaction.response.send_message("This is not a Discord ID number.\n\nPlease input the 18 digit Discord ID to retrieve the users marketplace stats.")

# Marketplace command to rate each other after a deal
@client.command(name = "donedeal", description = "Used for rating buyers/sellers in the Marketplace")
async def donedeal(interaction: discord.Interaction):
    if interaction.channel.category_id == ADMINCATEGORY:
        await tracker_utils.marketplace_done(interaction)
        await interaction.channel.send("Thanks for the ratings, if you would like to see your marketplace ratings please use `/marketinfo <discorduserid>`")
    else:
        await interaction.response.send_message("You must be in a marketplace ticket in order to use this command.")

# Marketplace command to add tracking
@client.command(name = "tracking", description = "Used to input a tracking number")
async def tracking(interaction: discord.Interaction, tracking_number: str):
    if interaction.channel.category_id == ADMINCATEGORY:
        ticketname = str(Client.get_channel(int(interaction.channel_id)))
        query = "INSERT INTO marketplacedeals (ticketname, trackingnumber) VALUES (%s, %s) ON CONFLICT (ticketname) DO UPDATE set trackingnumber = EXCLUDED.trackingnumber"
        data = (ticketname, tracking_number)

        await dbaccess.write_data(query, data)

        embedVar = await embeds.marketplace_tracking(tracking_number)
        await interaction.response.send_message(embed=embedVar)
    else:
        await interaction.response.send_message("You must be in a marketplace ticket in order to use this command.")

# Bot Tracker command to instantly get bot status
@client.command(name = "bottracker", description = "Gets bot status")
async def bottracker(interaction: discord.Interaction):
    guild = Client.get_guild(GUILD)
    botlistKian = ["NotifyRolePuller#5028", "Jarvis#6294", "Notify Tracker#8456", "Notify Utilities#9136", "Notify Pinger#8244", "Notify 1on1 Bot#7873", "Notify Toolbox#6012", "Whop Bot#6692"]
    embedVar = discord.Embed(title = "Bot Status", description = "Bot Statuses Displayed Here.", color=0xDB0B23)

    for i in botlistKian:
        try:
            bot = discord.utils.find(lambda m: str(m) == i, guild.members)
            if str(bot.status) == "offline":
                embedVar.add_field(name = i, value = "Offline", inline = False)
            elif str(bot.status) == "online":
                embedVar.add_field(name = i, value = "Online", inline = False)
        except Exception as e:
            embedVar.add_field(name = i, value = "Kicked From Server or Error: " + str(e), inline = False)
    await interaction.response.send_message(embed=embedVar)

@Client.event
async def on_message(msg):
    # Marketplace starting point, once a marketplace ticket opens this will start the flow
    if ('please respond to each prompt to properly **host and track** your transaction.' in msg.content) and (msg.channel.category_id == ADMINCATEGORY):
        await tracker_utils.marketplace_initial(msg)

Client.run(TOKEN)

#END

 

The next file contains some functions used by the main file to make the bot more organized.

import discord
import os
import asyncio
import datetime
from discord import Webhook
from dotenv import load_dotenv, find_dotenv
import requests
import json
import csv
import aiohttp
load_dotenv(find_dotenv())

from database import dbaccess
from embeds import embeds

GUILD = int(os.environ.get('NOTIFYGUILDID'))
NEWMEMBERROLE = int(os.environ.get('NEWMEMBERROLE'))
STAFFMODCHANNEL = int(os.environ.get('STAFFMODCHANNEL'))
WHOP_REVIEWS_WEBHOOK = os.environ.get("WHOP_REVIEWS_WEBHOOK")

Client = None

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

    Client = c

# Daily check if >= 3m then remove the role
async def checker():
    guild = Client.get_guild(GUILD)
    rolenewmember = guild.get_role(NEWMEMBERROLE)

    while True:
        await asyncio.sleep(180)
        now = datetime.datetime.now()
        then = now.replace(hour = 1, minute = 0)
        wait_time = (then - now).total_seconds()
        if wait_time < 0:
            wait_time = 86400 + wait_time

        print(f"Waiting {wait_time} before checking 3m old members.")

        await asyncio.sleep(wait_time)

        # Get the list of members with a specific role (New Member)
        memberlist = rolenewmember.members

        # check each member element for time in server
        for member in memberlist:
            timeIn = member.joined_at.replace(tzinfo=None)
            now = datetime.datetime.now()
            diff = now - timeIn
            if diff.days >= 90:
                await member.remove_roles(rolenewmember)

# Bot Tracker command to check if a bot is alive 
async def tracker():
    guild = Client.get_guild(GUILD)
    userKian = discord.utils.find(lambda m: str(m) == "smallpeenkeen#0", guild.members)
    userBanksy = discord.utils.find(lambda m: str(m) == "banksybanksy#0", guild.members)

    botlistKian = ["NotifyRolePuller#5028", "Jarvis#6294", "Notify Tracker#8456", "Notify Utilities#9136", "Notify Pinger#8244", "Notify 1on1 Bot#7873"]
    botlistBanksy = ["Notify Toolbox#6012", "Whop Bot#6692"]

    while True:
        await asyncio.sleep(3600)
        for i in botlistKian:
            try:
                bot = discord.utils.find(lambda m: str(m) == i, guild.members)

                if str(bot.status) == "offline":
                    await userKian.send("Bot " + i + " is offline.")
            except Exception as e:
                await userKian.send("Bot " + i + " has been kicked from the serveror Error: " + str(e))
        for i in botlistBanksy:
            try:
                bot = discord.utils.find(lambda m: str(m) == i, guild.members)

                if str(bot.status) == "offline":
                    await userBanksy.send("Bot " + i + " is offline.")
            except Exception as e:
                await userBanksy.send("Bot " + i + " has been kicked from the server or Error: " + str(e))

# automation to send muted members
async def muted():
    while True:
        await asyncio.sleep(180)
        now = datetime.datetime.now()
        then = now.replace(hour = 8, minute = 0)
        wait_time = (then - now).total_seconds()
        if wait_time < 0:
            wait_time = 86400 + wait_time

        print(f"Waiting {wait_time} before checking muted members.")

        await asyncio.sleep(wait_time)

        channel = Client.get_channel(STAFFMODCHANNEL)

        muted_role_list = [581499488901005313, 570142914173337600, 1018663023646363679]

        guild = Client.get_guild(GUILD)
        muted_members = ""

        for i in muted_role_list:
            if guild.get_role(i) != None:
                muted_members = muted_members + ["**Username:** " + m.name + " **ID:** " + str(m.id) for m in guild.get_role(i).members] + "\n"

        embedVar = discord.Embed(
            title="Muted Members", 
            description=f'{muted_members}', 
            color=0x000000
        )

        if guild.get_role(muted_role_list[0]) != None and guild.get_role(muted_role_list[1]) != None and guild.get_role(muted_role_list[2]) != None:
            await channel.send(embed=embedVar)

async def reviews():
    while True:
        await asyncio.sleep(180)
        now = datetime.datetime.now()
        then = now.replace(hour = 0, minute = 0)
        wait_time = (then - now).total_seconds()
        if wait_time < 0:
            wait_time = 86400 + wait_time

        print(f"Waiting {wait_time} before checking for new reviews.")

        await asyncio.sleep(wait_time)

        url = 'https://whop.com/api/graphql/fetchMarketplacePageReviews/'

        variables = {
            "id": "pge_bSKWtJeggxG147",
            "after": "MA==",
            "stars": None
        }

        headers = {
            'Referer': 'https://whop.com/marketplace/notify/'
        }

        query = '''
            query fetchMarketplacePageReviews($id: ID!, $after: String, $stars: Int) {
                publicPage(id: $id) {
                    ...PublicMarketplacePageReviewsData
                }
            }
            
            fragment PublicMarketplacePageReviewsData on PublicPage {
                reviewsAverage
                reviews(first: 20, after: $after, stars: $stars) {
                    nodes {
                        ...PublicMarketplacePageReview
                    }
                }
            }
            
            fragment PublicMarketplacePageReview on Review {
                user {
                    header
                    profilePic32: profilePicSrcset(style: s32, allowAnimation: true) {
                        original
                        double
                        isVideo
                    }
                }
                joinedAt
                createdAt
                stars
                description
            }
        '''

        payload = {'query': query, 'variables': variables, 'operationName': 'fetchMarketplacePageReviews'}

        response = requests.post(url, json=payload, headers=headers)

        if response.status_code == 200:
            data = response.json()
            reviews = data['data']['publicPage']['reviews']['nodes']
            avg_review = data['data']['publicPage']['reviewsAverage']

            async with aiohttp.ClientSession() as session:

                webhook = Webhook.from_url(WHOP_REVIEWS_WEBHOOK, session=session)
            
                for review in reversed(reviews):
                    name = review['user']['header']
                    created_at = datetime.datetime.utcfromtimestamp(review['createdAt']).strftime('%Y-%m-%d %H:%M:%S')
                    joined = datetime.datetime.utcfromtimestamp(review['joinedAt']).strftime('%Y-%m-%d')
                    rating = "⭐" * review['stars']
                    rev = review['description']
                    pfp = review['user']['profilePic32']['double']

                    current_time = datetime.datetime.now()
                    old_time = datetime.datetime.utcfromtimestamp(review['createdAt'])
                    time_diff = current_time - old_time

                    if time_diff <= datetime.timedelta(days=1):
                        embedVar = discord.Embed(
                            description=f"{rating}\n\n{rev}\n\n[Reviewed on {created_at}](https://whop.com/notify)", 
                            color=0xFF7673
                        )
                        embedVar.set_footer(
                            text = f"Rated {avg_review}/5 Based on {total_count} Reviews", 
                        )
                        embedVar.set_author(
                            name=f"{name} | Member Since {joined}", 
                            icon_url = pfp
                        )

                        await webhook.send(embed=embedVar)
                        await asyncio.sleep(1)

                        print("Added new review(s).")
        else:
            print('Request failed with status code:', response.status_code)

async def market_info(user_id):
    buyer_stats = {
        "success": 0,
        "fail": 0,
        "days": 0,
        "hours": 0,
        "minutes": 0,
        "money": 0
    }
    seller_stats = {
        "success": 0,
        "fail": 0,
        "days": 0,
        "hours": 0,
        "minutes": 0,
        "money": 0
    }

    buyer_query = f"SELECT * FROM marketplacebuyer WHERE buyerid = {int(user_id)}"
    seller_query = f"SELECT * FROM marketplaceseller WHERE sellerid = {int(user_id)}"

    result_buyer = await dbaccess.get_data(buyer_query, None)
    result_seller = await dbaccess.get_data(seller_query, None)

    if result_buyer:
        for row in result_buyer:
            buyer_stats = {
                "success": row[1],
                "fail": row[2],
                "days": row[3],
                "hours": row[4] // 3600,
                "minutes": (row[4] - (row[4] // 3600) * 3600) // 60,
                "money": row[5]
            }
    if result_seller:
        for row in result_seller:
            seller_stats = {
                "success": row[1],
                "fail": row[2],
                "days": row[3],
                "hours": row[4] // 3600,
                "minutes": (row[4] - (row[4] // 3600) * 3600) // 60,
                "money": row[5]
            }

    return buyer_stats, seller_stats

async def marketplace_done(interaction):
    starttime = ""
    buyerid = 0
    sellerid = 0
    dealvalue = 0
    buyerrating = 0
    sellerrating = 0

    query = "select * from marketplacedeals where channel_id = %s"
    data = (str(Client.get_channel(int(interaction.channel_id))),)

    # Gets the Buyer/Seller IDs and start time of deal
    records = await dbaccess.get_data(query, data)
    for row in records:
        buyerid = row[1]
        sellerid = row[2]
        dealvalue = row[4]
        starttime = row[6]

    # Get time difference for transaction to add
    avgtime = datetime.datetime.now() - datetime.datetime.strptime(starttime, "%Y-%m-%d %H:%M:%S.%f")
    days = avgtime.days
    seconds = avgtime.seconds  

    def checkseller(mes):
        return mes.channel == interaction.channel and mes.author != Client.user and mes.author.id == sellerid

    def checkbuyer(mes):
        return mes.channel == interaction.channel and mes.author != Client.user and mes.author.id == buyerid

    buyerEmbed = await embeds.marketplace_buyer()
    await interaction.response.send_message(embed = buyerEmbed)
    await interaction.followup.send('<@!' + str(sellerid) + '>')
    br = await Client.wait_for("message", check = checkseller)
    buyerrating = int(br.content)

    sellerEmbed = await embeds.marketplace_seller()
    await interaction.channel.send(embed = sellerEmbed)
    await interaction.followup.send('<@!' + str(buyerid) + '>')
    sr = await Client.wait_for("message", check = checkbuyer)
    sellerrating = int(sr.content)

    # Gets old buyer results from db then manipulates them and adds them back to the db
    query = "select * from marketplacebuyer where buyerid = %s"
    data = (buyerid,)
    buyer_records = await dbaccess.get_data(query, data)

    # Initialize default values
    succ = 1 if buyerrating == 1 else 0
    fail = 0 if buyerrating == 1 else 1
    bdays = days
    bseconds = seconds
    bmoney = dealvalue

    # Update values if the buyer's record exists
    if buyer_records:
        for row in buyer_records:
            succ = row[1] + (1 if buyerrating == 1 else 0)
            fail = row[2] + (0 if buyerrating == 1 else 1)
            bdays = (row[3] + days) / 2
            bseconds = (row[4] + seconds) / 2
            bmoney = dealvalue + row[5]

    # Adding them back to the db
    query = "INSERT INTO marketplacebuyer (buyerid, buyersuccess, buyerfails, days, seconds, dealstotal) VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT (buyerid) DO UPDATE set (buyersuccess, buyerfails, days, seconds, dealstotal) = (EXCLUDED.buyersuccess, EXCLUDED.buyerfails, EXCLUDED.days, EXCLUDED.seconds, EXCLUDED.dealstotal)"
    data = (buyerid, succ, fail, bdays, bseconds, bmoney)
    await dbaccess.write_data(query, data)

    # Gets old seller results from db then manipulates them and adds them back to the db
    query = "select * from marketplaceseller where sellerid = %s"
    data = (sellerid,)
    seller_records = await dbaccess.get_data(query, data)

    # Initialize default values
    succ = 1 if sellerrating == 1 else 0
    fail = 0 if sellerrating == 1 else 1
    sdays = days
    sseconds = seconds
    smoney = dealvalue

    # Update values if the seller's record exists
    if seller_records:
        for row in seller_records:
            succ = row[1] + (1 if sellerrating == 1 else 0)
            fail = row[2] + (0 if sellerrating == 1 else 1)
            sdays = (row[3] + days) / 2
            sseconds = (row[4] + seconds) / 2
            smoney = dealvalue + row[5]

    query = "INSERT INTO marketplaceseller (sellerid, sellersuccess, sellerfails, days, seconds, dealstotal) VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT (sellerid) DO UPDATE set (sellersuccess, sellerfails, days, seconds, dealstotal) = (EXCLUDED.sellersuccess, EXCLUDED.sellerfails, EXCLUDED.days, EXCLUDED.seconds, EXCLUDED.dealstotal)"
    data = (sellerid, succ, fail, sdays, sseconds, smoney)
    await dbaccess.write_data(query, data)

async def marketplace_initial(msg):
    marketplace_dict = {
        "ticketname": str(Client.get_channel(int(msg.channel.id))),
        "buyerid": 0,
        "sellerid": 0,
        "items": "",
        "dealprice": 0,
        "trackingnumber": "",
        "starttime": str(datetime.datetime.now()),
        "endtime": ""
    }

    for embed in msg.embeds:
        try:
            contentStringDesc = str(embed.description)
            contentStringDescTrim = contentStringDesc.split('> **Discord ID:** ', 1)[1]
            memberidstr = contentStringDescTrim
            member = await Client.fetch_user(int(memberidstr))
            overwrites = discord.PermissionOverwrite()
            overwrites.send_messages = True
            overwrites.read_messages = True
            await msg.channel.set_permissions(member, overwrite = overwrites)
        except:
            await msg.channel.send("This is not a Discord ID number.\n\nA staff member will add the user you've requested once you answer the questions. <@&575144399013543977>")

    # initial message
    initialEmbed = await embeds.marketplace_intial()
    await msg.channel.send(embed=initialEmbed)

    finished = "0"

    restartEmbed = await embeds.marketplace_restart()
    await msg.channel.send(embed=restartEmbed)

    while finished != "1":
        try:
            # Asks for Buyer id
            buyerEmbed = await embeds.marketplace_buyer_input()
            await msg.channel.send(embed=buyerEmbed)
            m = await Client.wait_for("message", check=check)
            if (m.content != "restart"):
                marketplace_dict["buyerid"] = int(m.content)
            else:
                await msg.channel.send("Restarting...")
                continue
        except:
            await msg.channel.send("This is not a Discord ID number, please restart.")
            continue

        try:
            # Asks for Seller id
            sellerEmbed = await embeds.marketplace_seller_input()
            await msg.channel.send(embed=sellerEmbed)
            m = await Client.wait_for("message", check=check)
            if (m.content != "restart"):
                marketplace_dict["sellerid"] = int(m.content)
            else:
                await msg.channel.send("Restarting...")
                continue
        except:
            await msg.channel.send("This is not a Discord ID number, please restart.")
            continue

        try:
            # Asks for Items
            itemEmbed = await embeds.marketplace_items()
            await msg.channel.send(embed=itemEmbed)
            m = await Client.wait_for("message", check=check)
            if (m.content != "restart"):
                marketplace_dict["items"] = m.content
            else:
                await msg.channel.send("Restarting...")
                continue
        except:
            await msg.channel.send("Item list entry is not correct, please restart.")
            continue

        try:
            # Asks for Deal Price
            priceEmbed = await embeds.marketplace_deal()
            await msg.channel.send(embed=priceEmbed)
            m = await Client.wait_for("message", check=check)
            if (m.content != "restart"):
                marketplace_dict["dealprice"] = int(m.content)
            else:
                await msg.channel.send("Restarting...")
                continue
        except:
            await msg.channel.send("Deal price was not entered correctly, please restart.")
            continue

        try:
            # Asks if info correct
            finalEmbed = await embeds.marketplace_final()
            await msg.channel.send(embed=finalEmbed)
            m = await Client.wait_for("message", check=check)
            if (m.content != "n"):
                await msg.channel.send("Great, you are done for now!")
            else:
                await msg.channel.send("Restarting...")
                continue
        except:
            await msg.channel.send("Make sure to only type `y` or `n`, please restart.")
            continue

        finished = "1"

    nextStepEmbed = await embeds.marketplace_next_step()
    await msg.channel.send(embed = nextStepEmbed)

    query = "INSERT INTO marketplacedeals (ticketname, buyerid, sellerid, items, dealprice, trackingnumber, starttime, endtime) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
    data = (marketplace_dict["ticketname"], marketplace_dict["buyerid"], marketplace_dict["sellerid"], marketplace_dict["items"], marketplace_dict["dealprice"], marketplace_dict["trackingnumber"], marketplace_dict["starttime"], marketplace_dict["endtime"])

    await dbaccess.write_data(query, data)

 

The next part contains code for the database access, any query from the main or tracker utils files will go through this.

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

from global_vars import GlobalVariables

load_dotenv()

log_file_path = os.path.join('logs', 'dblog.log')
logging.basicConfig(level=os.environ.get("LOGLEVEL", "DEBUG"), filename=log_file_path, 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):
    records = None
    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. Set value to None if you aren't passing in data.
async def get_data(query, val):
    records = None
    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:
                if val != None:
                    await curr.execute(query, val)
                else:
                    await curr.execute(query)
                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("Written 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("Written values and closed")

        except Exception as e:
            logging.error(e)

 

There is also an embed file as well to manage some embeds but it is not going to be included.