Teamspeak auto-verify through API — Guild Wars 2 Forums
Guildwars2.com Forums Support Wiki
  • Sign In With Your GW2 Account
  • Register
    • en fr es de
    ›

    Teamspeak auto-verify through API

    ClaraFee.2579ClaraFee.2579 Member ✭✭
    edited August 14, 2018 in API Development

    Hi friends! I recently played on a WvW Teamspeak that has an auto-verify feature that works with the GW2 API. This was Fort Aspenwood specifically, but I know Blackgate has something similar too.

    I want to add this to the Teamspeak server that I run. Do any of you have any links to this resource and/or instructions on how to do it? Thanks for your time.

    Comments

    • Dash.3179Dash.3179 Member ✭✭

      my guess would be they're using the mumble shared memory file to verify the players server and map:
      https://wiki.guildwars2.com/wiki/API:MumbleLink
      No Idea how to integrate that with something like teamspeak though; I've been using it with Qt C++ through a program I've made myself... that said if someone else has done it the tools must exist somewhere

    • Valshia.7652Valshia.7652 Member ✭

      Basically what you need to do is ask the player for an API key and use it to access their account endpoint: https://wiki.guildwars2.com/wiki/API:2/account

      "world" gives you the id number of their server. You can see the ids for each server listed here: https://wiki.guildwars2.com/wiki/API:2/worlds

    • ClaraFee.2579ClaraFee.2579 Member ✭✭

      Yeah, this is done with the API. Here's the instructions on Fort Aspenwood:

      http://www.fort-aspenwood.com/index.php?/topic/7420-teamspeak-autoverification/

      I can't tell if this the proprietary work of a single developer, or if they've installed some sort of Teamspeak addon. Anyone have any insight?

    • I Am Dansker.7105I Am Dansker.7105 Member ✭✭

      Probably proprietary, but not sure. Multiple verification systems exists, but i have not tried any of them, so can't tell you how well they work.
      A google search for "gw2 teamspeak verify bot github" revealed multiple projects you could try and use.

      If you have any experience with developing software here is the gist of how you will do it for teamspeak

      As others have mentioned, you need to get an API key from the user you want to verify
      Once you have it, you can make a GET request at https://api.guildwars2.com/v2/account?access_token= which will provide you with various information about the player, including world ID (e.g. 2007 which is Far Shiverpeaks). To match a world id to a world, take a look at https://api.guildwars2.com/v2/worlds?ids=all

      Once you know the user needs to have access on your teamspeak server, you need to put the user in the correct servergroup
      This can be done via the teamspeak serverquiry api. I have used https://github.com/TheHolyWaffle/TeamSpeak-3-Java-API for the Far Shiverpeaks ts bot, however you are free to use what ever wrapper you want for the language you use (assuming there is one available, just google for it).
      Specifically for this wrapper, a servergroup can be given using api.addClientToServerGroup(serverGroupId, tsDatabaseId); where api is an instance of TS3Api. There is an example on the github page on how to obtain an instance of it.

    • Chaba.5410Chaba.5410 Member ✭✭✭✭

      Ever consider asking the Fort Aspenwood TS administrators about it?

      This was written originally by an old Tarnished Coast TS admin iirc, from where we got it and then later passed it along to Sea of Sorrows TS admins.

      https://github.com/metalfiiish/ts-gw2-verifyBot

    • Akotun.1057Akotun.1057 Member ✭
      edited September 19, 2018

      Sorry for my bad english in advance :)
      I wrote some code in python 2 for that to give my guild members ts3/forum(WBB4) groups based on the guild ranks using my guild leader api key to get the guildlist and ranks and compare them to the account endpoint of the client/user.
      For the environment:
      we running our forum and ts3 on our own vserver so we have full root access to everything.
      i also moved the teamspeak to mysql instead of sqlite to simplyfy requests about groups ids and more stuff of the ts3 server. To be able to add clients to groups you will have to use the ts3 server querry which i implemented with a simple telnet connection on the localhost.
      for the forum i use customized profile fields and a wbb4 add in for teamspeak sync to enter the apikey and the ts3 client uid my script runs in the background every 5 minutes to give users rights or take them away
      for ts3 i use the ts3 serverbot https://www.stefan1200.de/forum/index.php?topic=2.0 giving me the oportunity to poke the user with a short url containing their client uid like http://theunbuffed.de/api/?TSUID=SNDbxuyJNYZrm0hZYivJoQtvpbs=
      then is simply wrote a html form to enter the apikey and saving both in my database then my script is triggerd automaticly

      Notes:
      I dont care if i get errors for users are already in a group
      i am new to python and gw2 api coding and but i work in it(not as programmer), with a little help of my it skilled guildmates it took us about 5 weeks to write this code and still strugling with some problems like to decide if api is down(like yesterday) or if the user has an deleted api key ( any help is welcome :) )
      the whole comments/documentation in the code is german and incomplete if you dont understand some parts ask me what im doing there (not remembering sometimes myself XD )
      it runs somehow but its not optimized, a more skilled programmer can code this way better hope it helps anyway to find a solution for you

      My Python Code:

                  # -*- coding: utf-8 -*-
            #Dokumentation 
            # Variablen:
            # A:
            # AccInfo       -   Enthaelt die Accountinfos des Forenbenutzer die fuer den hinterlegten API key abgefragt wurden
            # ApiData       -   Enthaelt die Gildenmitglieder als .json welche mit dem Gildenleiter Api Schluessel und der Gilden ID mit der Funktion ApiCall() abgeholt wird die uebergebene url ist https://api.guildwars2.com/v2/guild/0650168F-D3E9-E411-AEFB-AC162DC05865/members?access_token=D683045A-CA90-1940-87C1-63D45A43BD00B879D2A6-7F81-411D-B400-A414ADC18919 Felder sind name(accountname) rank(gildenrang) joined(Beitrittsdatum)
            #
            # C:
            #cursor         -   cursor Objekt welches zur SQL Abfrage verwendet wird
            #
            # D:
            # db            -   Datenbankverbindung Forum
            # dbname:       -   Name der Datenbank
            #
            # G:
            # glrow         - Zeile in Guildlist
            # Guildlist     - Funktion CompareAccInfo, Uebergebene Daten aus ApiData
            #
            # R:
            # rankrow       - Zeile in Ranks
            # Ranks:        -   Liste aller Raenge und deren ID im Forum
            #
            # S:
            # Sqldata       - Datensatz der aus dem Forum abgefragtwurde, enthaelt eine eindeutige tabelle mit user ids username accname und apikey
            # sqlrow        - Zeile in Sqldata
            # T:
            # Tsdb          -   Datenbankverbindung Teamspeak
            # tsdata        -   Liste aller Api Schluessel zu TSUIDs
            #
            # U:
            # UserId        - Funktion CompareAccInfo, Benutzer ID des Users im Forum dessen Accinfos uebergeben wurden
            #



            from urllib2 import urlopen
            import urllib2
            import json
            import MySQLdb
            import sys
            import telnetlib
            db = MySQLdb.connect("localhost","forumdbuser","password","nameofmyforumdatabase" )
            Tsdb = MySQLdb.connect("localhost","ts3dbuser","password","nameofmyts3database" )
            db.set_character_set('utf8')

            #Abgleich der Gildenliste mit den Accountinformationen des uebergebenen Benutzers
            def CompareAccInfo(Guildlist, AccInfo, UserId, Apikey):
                #Flag zum loeschen
                Rang = "nA"
                Rang_found=False
                #API Key konnte abgefragt werden
                #Fuer jede Zeile in Gildenliste(Apidata)
                for glrow in Guildlist:
                    #Wenn Name in der Gildenliste(Apidata) gleich Name in AccInfo
                    if glrow['name'] == AccInfo['name']:
                        #Forenbenutzer ist in der Gilde!
                        #Hole Liste der Gruppennamen und deren ID aus der Forendatenbank
                        Ranks = getRank(db)
                        #Hole Liste aller Ranggruppen aus TS
                        Ranggruppen_TS = getts_rank_groups(Tsdb)
                        #Fuer jede Zeile in Ranks
                        for rankrow in Ranks:
                            # fuer die der Rangname gleich dem Rangnamen in der Gildenliste
                            if rankrow[1] == glrow['rank'].encode("utf-8"):
                                print "user ist in der Gilde"
                                loeschen=False
                                Rang=rankrow[0]
                                Rang_Name =rankrow[1]
                                Rang_found=True
                                #print glrow['name']
                                #print AccInfo['name']
                                #print ("if") 
                                #print UserId


                            else:
                                if Rang_found==False:
                                    Rang="Ichbinbesonders"

                if Rang == "Ichbinbesonders":
                    print "Ich bin in der Gilde aber es wurde kein Rang gefunden"
                    setUserToGroup(db,UserId,16)
                    setUserToGroup(db,UserId,11)
                    tsuid = getTeamspeakUID(db,UserId)
                    for row in tsuid:
                        clientid= getClientID(Tsdb,row[0])
                        #Andere Ranggruppen entfernen
                        deltsrankgroups(Tsdb,row[0])
                        #Pruefen ob User andere Ranggruppen hat wenn ja entferne alle Ranggruppen
                        #TS Gruppe Gast setzen da Rang nicht gefunden evtl. Zweitacc
                        addtsgroupsquery(clientid,52)
                        #TS Gruppe Buff setzen da in der Gilde
                        addtsgroupsquery(clientid,67)
                elif Rang != "nA":
                    print "user ist in der gilde und hat einen rang"
                    #entferne den User aus allen Forenrruppen um Rangaenderungen sauber durchfuehren zu koennen
                    deleteUsertoGroup(db,UserId)
                    # Dem User die gefundene Ranggruppe anhand der ID zuordnen
                    setUserToGroup(db,UserId,Rang)
                    #dem User The Unbuffed Gruppe geben
                    setUserToGroup(db,UserId,11)
                    tsuid = getTeamspeakUID(db,UserId)
                    copyforum_to_ts3(Tsdb,tsuid,Apikey)
                    for row in tsuid:
                        zus_Ranggruppen = getunwantedts_client_rankgroups(Tsdb,row[0],Rang_Name)
                        if len(zus_Ranggruppen) > 0:
                            print "zu viele Ranggruppen"
                            deltsrankgroups(Tsdb, row[0])
                            print "Ranggruppen gelöscht"
                            client_id= getClientID(Tsdb,row[0])
                            print "Client ID geholt"
                            ts_rank = gettsgroupidbyname(Tsdb,Rang_Name)
                            addtsgroupsquery(client_id,ts_rank[0][0])
                        else:
                            print "keine weiteren Ranggruppen"
                            client_id= getClientID(Tsdb,row[0])
                            ts_rank = gettsgroupidbyname(Tsdb,Rang_Name)
                            addtsgroupsquery(client_id,ts_rank)



                else:
                    print "user ist nicht in der gilde"
                    deleteUsertoGroup(db,UserId)
                    print "User aus Gruppen geloescht"
                    tsuid = getTeamspeakUID(db,UserId)
                    print "TS IDs efolgreich abgerufen"
                    print tsuid
                    for tsrow in tsuid:
                        DeleteTSGroups(Tsdb, tsrow[0])
                    delapikey_forum(db,UserId)




            #Ruft von der Gw2 Api Url Informationen ab
            def ApiCall(url):
                try:
                    print "Ich versuche Daten abzurufen     "+url
                    response = urlopen(url)
                    returnvalue = json.loads(response.read())
                    print "ich habe folgende Daten erhalten:"
                    print returnvalue
                except urllib2.HTTPError as err:
                    print "function ApiCall"
                    print url
                    print err
                    print err.read()                                        #Under Construction still strugeling to find out how to differ if api is down/in trouble or if key was deleted
                    html = err.read() 
                    print type(html)
                    returnvalue = "error"
                    if err.code == int(403) or "invalid key" in html:
                        returnvalue = "403"


                return returnvalue


            #Ruft alle moeglichen raenge aus der Foren Datenbank ab
            def getRank(db):
                cursor = db.cursor()
                cursor.execute("select database()")
                db_name = cursor.fetchone()[0]
                if db_name == "TUB": #hier spaeter die variablen die den namen enthalten werden einfuegen
                    print "Gruppennamen werden aus Datenbank " + db_name + " abgerufen."
                    cursor.execute("SELECT groupID,groupName FROM wcf2_user_group")
                elif db_name == "ts3db": #hier spaeter die variablen die den namen enthalten werden einfuegen
                    print "Gruppennamen werden aus Datenbank " + db_name + " abgerufen."
                    cursor.execute("SELECT group_id,name FROM groups_server where server_id=1")
                else:
                    print "Unknown Database!"
                return cursor.fetchall()

            #In der Foren Datenbank die benutzergruppen zuweisen
            def setUserToGroup(db,userId, groupId):
                try:
                    cursor = db.cursor()
                    cursor.execute("INSERT INTO wcf2_user_to_group(userID, groupID) VALUES ({0},{1})".format(userId, groupId))
                except MySQLdb.Error, e:
                    print e
                finally:
                    cursor.close()
                    db.commit()

            #Gruppen von Benutzer im Forum loeschen
            def deleteUsertoGroup(db,userId):
                try:
                    cursor = db.cursor()
                    cursor.execute("DELETE FROM wcf2_user_to_group WHERE userID = {0} and (groupID = 10 or groupID = 11 or groupID = 12 or groupID = 14 or groupID = 51 or groupID = 16 or groupID= 50)".format(userId))
                except MySQLdb.Error, e:
                    print e
                finally:
                    cursor.close()
                    db.commit()


            #Ermittelt die Teamspeak UniqueID aus dem Forum
            def getTeamspeakUID(db, userId):
                try:
                    cursor = db.cursor()
                    cursor.execute("select ts3.teamspeakUID from wcf2_user_teamspeak as ts3 where ts3.userID = {0}".format(userId))
                except MySQLdb.Error, e:
                    print e
                finally:
                    return cursor.fetchall()

            #Ermittelt die Ts Client id zur UniqueID aus dem Ts
            def getClientID(db, TsUID):
                cursor = db.cursor()
                cursor.execute("SELECT client_id FROM clients WHERE client_unique_id = '{0}'".format(TsUID))
                TsInfo = cursor.fetchall()
                for user in TsInfo:
                    return user[0]

            #Ts Gruppen loeschen 
            def DeleteTSGroups(db, TsUID):
                clientID = getClientID(db, TsUID)
                cursor = db.cursor()
                if clientID is not None:
                    #User wurde in TS DB mit UID gefunden
                    cursor.execute("select * from group_server_to_client WHERE id1 = {0}".format(clientID))
                    print "Fuer die TSUID " + TsUID + " werden folgende Gruppen geloescht:"
                    for row in cursor.fetchall():
                        print row[0]
                    deltsgroupsquery(db,clientID)
                    #cursor.execute("delete from group_server_to_client WHERE id1 = {0}".format(clientID))
                    cursor.execute("DELETE FROM apikey_tsuid WHERE TSUID = '" + TsUID +"'" )
                    db.commit()


            #TSUID+API Schluessel aus TS Datenbank holen
            def gettsuid_api(db):
                cursor = db.cursor()
                cursor.execute("SELECT TSUID,APIKEY FROM apikey_tsuid")
                return cursor.fetchall()

            def getforumdata(db):
                cursor = db.cursor()  #Cursor auf Forendatenbanksetzen
                # execute SQL query using execute() method.
                cursor.execute("Select User.username Username, UserInfo.userOption45 as AccountName, UserInfo.userOption50 ApiKey, Gruppe.GroupID Gruppen_ID, UserGroup.groupName Gruppe from wcf2_user as User left join wcf2_user_option_value as UserInfo on User.userID = UserInfo.userID left join wcf2_user_to_group as Gruppe on User.userID = Gruppe.userID left join wcf2_user_group as UserGroup on Gruppe.groupID = UserGroup.groupID")  #Gesammte Liste Aller Benutzer im Forum mit jedem accountnamen dessen api schluessel und gruppen inkl deren Gruppen IDs von der DB abfragen
                # Fetch a single row using fetchone() method.
                Sqldata = cursor.fetchall() #Gesammte Liste Aller Benutzer im Forum mit jedem accountnamen dessen api schluessel und gruppen inkl deren Gruppen IDs 
                cursor.execute("Select DISTINCT User.userID as ID, User.username Username, UserInfo.userOption45 as AccountName, UserInfo.userOption50 as Apikey from wcf2_user as User left join wcf2_user_option_value as UserInfo on User.userID = UserInfo.userID left join wcf2_user_to_group as Gruppe on User.userID = Gruppe.userID left join wcf2_user_group as UserGroup on Gruppe.groupID = UserGroup.groupID") # Gruppenjoin? Wozu? Check ich null XD
                return cursor.fetchall()

            #Liste aller TS Gruppen die fuer Guild Wars 2 relevant sind Rest ausgenommen
            def gettsgroups(db):
                cursor =db.cursor()
                cursor.execute("SELECT group_id, name FROM `groups_server` WHERE `name` != 'Unknown' AND `name` != 'Server Admin' AND `name` != 'Admin Server Query' AND `name` != 'Guest Server Query' AND `name` != 'Guest' AND `name` != 'Normal' AND `name` != 'Musicbot' AND `name` != 'Mortal Kombat X' AND `name` != 'Rocket League' AND `name` != 'Borderlands 2' AND `name` != 'Dayz' AND `name` != 'The Elder Scrolls Online' AND `name` != 'Max'")
                return cursor.fetchall()

            #Per Telnet auf dem Teamspeak alle Gruppen fuer eine TS Client ID entfernen
            def deltsgroupsquery(db, clientid):
                HOST = "localhost"
                user = "serveradmin"
                port = "10011"
                password = "password"

                tn = telnetlib.Telnet(HOST,port)
                tn.write("login " + user + " " + password +"\n")
                tn.write("use 1\n")
                tsgruppen = gettsgroups(db)
                for row in tsgruppen:
                    tn.write("servergroupdelclient sgid=" + str(row[0]) + " cldbid=" + str(clientid) +"\n")
                tn.write("quit\n")
                tn.read_all()



            def addtsgroupsquery(clientid, groupID):
                HOST = "localhost"
                user = "serveradmin"
                port = "10011"
                password = "password"

                tn = telnetlib.Telnet(HOST,port)
                tn.write("login " + user + " " + password +"\n")
                tn.write("use 1\n")
                tn.write("servergroupaddclient sgid=" + str(groupID) + " cldbid=" + str(clientid) +"\n")
                tn.write("quit\n")
                tn.read_all()




            def copyforum_to_ts3(db,tsuid,apikey):
                for tsrow in tsuid:
                    try:
                        cursor = db.cursor()
                        cursor.execute("INSERT INTO `apikey_tsuid`(`TSUID`, `APIKEY`) VALUES ('"+ str(tsrow[0]) + "','" + str(apikey) + "')")
                    except MySQLdb.Error, e:
                        print e
                        try:
                            cursor = db.cursor()
                            cursor.execute("Update `apikey_tsuid` set APIKEY='" + str(apikey) + "' where TSUID ='" + str(tsrow[0]) + "'" )
                        except MySQLdb.Error, e:
                            print e
                        finally:
                            cursor.close()
                            db.commit()
                    finally:
                        cursor.close()
                        db.commit()


            #API Key aus dem Forum loeschen
            def delapikey_forum(db,UserID):
                cursor = db.cursor()
                cursor.execute("UPDATE wcf2_user_option_value set userOption50 = 'Gelöscht da ungültig bitte neuen Key eintragen' where userID='" + str(UserID) +"'")
                db.commit()

            #TS Gruppem eines Benutzers abfragen
            def getts_groups_user(db,tsuid):
                UserID = getClientID(db,tsuid)
                cursor = db.cursor()
                cursor.execute("SELECT group_client.group_ID as group_id, groups.name as Gruppenname, group_client.id1 as User_ID FROM `group_server_to_client` as group_client left join groups_server as groups on group_client.group_id = groups.group_id WHERE group_client.id1='" + str(UserID) +"'  And groups.name != 'Unknown' AND groups.name != 'Server Admin' AND groups.name != 'Admin Server Query' AND groups.name != 'Guest Server Query' AND groups.name != 'Guest' AND groups.name != 'Normal' AND groups.name != 'Musicbot' AND groups.name != 'Mortal Kombat X' AND groups.name != 'Rocket League' AND groups.name != 'Borderlands 2' AND groups.name != 'Dayz' AND groups.name != 'The Elder Scrolls Online' AND groups.name != 'Max'")
                return cursor.fetchall()

            #TS Ranggruppen ohne eine bestimmte Gruppe ausgeben - Liste unerwuenschter Ranggruppen
            def getunwantedts_client_rankgroups(db,tsuid, excl_rang):
                UserID = getClientID(db,tsuid)
                cursor = db.cursor()
                cursor.execute('SET NAMES utf8mb4')
                cursor.execute("SET CHARACTER SET utf8mb4")
                cursor.execute("SET character_set_connection=utf8mb4")
                cursor.execute("SELECT group_client.group_ID as group_id, groups.name as Gruppenname, group_client.id1 as User_ID FROM `group_server_to_client` as group_client left join groups_server as groups on group_client.group_id = groups.group_id WHERE group_client.id1='" + str(UserID) +"'  And groups.name != 'Unknown' AND groups.name != 'Server Admin' AND groups.name != 'Admin Server Query' AND groups.name != 'Guest Server Query' AND groups.name != 'Guest' AND groups.name != 'Normal' AND groups.name != 'Musicbot' AND groups.name != 'Mortal Kombat X' AND groups.name != 'Rocket League' AND groups.name != 'Borderlands 2' AND groups.name != 'Dayz' AND groups.name != 'The Elder Scrolls Online' AND groups.name != 'Max' AND groups.name != 'Krieger' AND groups.name != 'Wächter' AND groups.name != 'Waldläufer' AND groups.name != 'Ingenieur' AND groups.name != 'Dieb' AND groups.name != 'Elementarmagier' AND groups.name != 'Mesmer' AND groups.name != 'Nekromant' AND groups.name != 'BUFF' AND groups.name != 'Wiedergänger' AND groups.name != 'Herold' AND groups.name != 'Fraktale T1 - Eingeweihter' AND groups.name != 'Fraktal T2 - Adept' AND groups.name != 'Berserker' AND groups.name != 'Drachenjäger' AND groups.name != 'Schnitter' AND groups.name != 'Chronomant' AND groups.name != 'Sturmbote' AND groups.name != 'Schrotter' AND groups.name != 'Druide' AND groups.name != 'Raufbold' AND groups.name != 'Raid' AND groups.name != 'Raid auf Probe' AND groups.name != 'Guild Wars 2' AND groups.name != 'Fraktal T3 - Experte' AND groups.name != 'Fraktal T4 - Meister' AND groups.name != 'Raidgruppe #1' AND groups.name != 'Raidgruppe #2' AND groups.name != '" + str(excl_rang) + "'")
                return cursor.fetchall()

            def getts_rank_groups(db):
                cursor = db.cursor()
                cursor.execute("SELECT * from groups_server where name = 'Anführer' or name = 'Veteran' or name = 'Mitglied' or name ='Neu' or name ='Schreiber' or name ='GAST'") 
                return cursor.fetchall()

            def deltsrankgroups(db, tsuid):
                clientid = getClientID(db,tsuid)
                HOST = "localhost"
                user = "serveradmin"
                port = "10011"
                password = "password"

                tn = telnetlib.Telnet(HOST,port)
                tn.write("login " + user + " " + password +"\n")
                tn.write("use 1\n")
                tn.write("servergroupdelclient sgid=52  cldbid=" + str(clientid) +"\n") #GAST (Zweitacc oder andere unbekannte Ränge)
                tn.write("servergroupdelclient sgid=54  cldbid=" + str(clientid) +"\n") #Anführer
                tn.write("servergroupdelclient sgid=55  cldbid=" + str(clientid) +"\n") #Veteran
                tn.write("servergroupdelclient sgid=56  cldbid=" + str(clientid) +"\n") #Mitglied
                tn.write("servergroupdelclient sgid=93  cldbid=" + str(clientid) +"\n") #Neu
                tn.write("servergroupdelclient sgid=94  cldbid=" + str(clientid) +"\n") #Schreiber
                # tn.write("servergroupdelclient sgid=??  cldbid=" + str(clientid) +"\n") #Neu
                tn.write("quit\n")
                tn.read_all()


            def gettsgroupidbyname(db,name):
                cursor = db.cursor()
                cursor.execute("SELECT * from groups_server where name = '" + str(name) +"'")
                return cursor.fetchall()



            def compareClientInfo(ApiData, AccInfo, tsdata):
                #API Data= Gildenliste mit Leiter Schlüssel
                #Flag zum loeschen
                Rang = "nA"
                Rang_found=False
                #API Key konnte abgefragt werden
                #Fuer jede Zeile in Gildenliste(Apidata)
                for glrow in ApiData:
                    #Wenn Name in der Gildenliste(Apidata) gleich Name in AccInfo
                    if glrow['name'] == AccInfo['name']:
                        #Forenbenutzer ist in der Gilde!
                        #Hole Liste aller Ranggruppen aus TS
                        Ranggruppen_TS = getts_rank_groups(Tsdb)
                        #Fuer jede Zeile in Ranks
                        for rankrow in Ranggruppen_TS:
                            # fuer die der Rangname gleich dem Rangnamen in der Gildenliste
                            if rankrow[2] == glrow['rank'].encode("utf-8"):
                                loeschen=False
                                Rang=rankrow[2]
                                Rang_Name =rankrow[2]
                                Rang_found=True


                            else:
                                if Rang_found==False:
                                    Rang="Ichbinbesonders"

                if Rang == "Ichbinbesonders":
                            clientid= getClientID(Tsdb,tsdata[0])
                            #Andere Ranggruppen entfernen
                            #Pruefen ob User andere Ranggruppen hat wenn ja entferne alle Ranggruppen
                            other_ranks = getunwantedts_client_rankgroups(Tsdb,tsdata[0], "GAST")
                            if len(other_ranks) > 0:
                                print "Zusätzliche Ranggruppen werden gelöscht"
                                deltsrankgroups(Tsdb,tsdata[0])
                                print "Gast Rang setzen da Rang nicht zugeordnet werden konnte"
                                #TS Gruppe Gast setzen da Rang nicht gefunden evtl. Zweitacc
                                addtsgroupsquery(clientid,52)
                                #TS Gruppe Buff setzen da in der Gilde
                                addtsgroupsquery(clientid,67)
                            else:
                                print "Gast Rang setzen da Rang nicht zugeordnet werden konnte"
                                #TS Gruppe Gast setzen da Rang nicht gefunden evtl. Zweitacc
                                addtsgroupsquery(clientid,52)
                                #TS Gruppe Buff setzen da in der Gilde
                                addtsgroupsquery(clientid,67)

                elif Rang != "nA":
                    print "user ist in der gilde und hat einen rang"
                    other_ranks = getunwantedts_client_rankgroups(Tsdb,tsdata[0],Rang_Name)
                    if len(other_ranks) > 0:
                        print "Zusätzliche Ranggruppen werden gelöscht"
                        deltsrankgroups(Tsdb, tsdata[0])
                        print "Client ID wird geholt"
                        client_id= getClientID(Tsdb,tsdata[0])
                        ts_rank = gettsgroupidbyname(Tsdb,Rang_Name)
                        addtsgroupsquery(client_id,ts_rank[0][0])
                        addtsgroupsquery(client_id, "67")
                    else:
                        print "Client ID wird geholt"
                        client_id= getClientID(Tsdb,tsdata[0])
                        ts_rank = gettsgroupidbyname(Tsdb,Rang_Name)
                        addtsgroupsquery(client_id,ts_rank[0][0])
                        addtsgroupsquery(client_id, "67")

                else:
                    print "User nicht in der Gilde Ranggruppen werden gelöscht"
                    DeleteTSGroups(db, tsdata[0])


















            def main():
                #Ermittelt spezifische Informationen bezueglich der Gildenliste anhand von Akotuns Api-key
                print "Gildenliste von API abrufen"
                ApiData = ApiCall("https://api.guildwars2.com/v2/guild/0650168F-D3E9-E411-AEFB-AC162DC05865/members?access_token=090DA356-8DC5-3D42-A79F-DFA515553A04DB50660E-07C7-4AFF-A261-D5954D427E65")
                #15420DC2-BDDC-B140-9D87-4DCC4C85A85CC82CA0A5-2D1D-4B8B-86A4-FACD00BCFDAB NIls Key
                if ApiData == "error" or ApiData == "403":
                    ApiData = ApiCall("https://api.guildwars2.com/v2/guild/0650168F-D3E9-E411-AEFB-AC162DC05865/members?access_token=apikey_2nd_guildleader")

                #print ApiData

                if ApiData != "error" and ApiData != "403":
                    #**************************************** Forum **************************************************
                    Sqldata = getforumdata(db) #Gesammte Liste Aller Benutzer im Forum mit jedem accountnamen dessen api schluessel 
                    #Fuer jede Zeile in Sqldata
                    for sqlrow in Sqldata:
                        print ""
                        print "----------------------------------------Forum------------------------------------------"
                        print ""
                        if sqlrow[3] != "Gelöscht da ungültig bitte neuen Key eintragen" or "":
                            print "User hat API Key hinterlegt"
                            #Datensaetze in Datenbank mit der Gw2 Api vergleichen
                            #Accountinfo mit APIkey der Forenbenutzer abfragen wenn leer oder ungueltig als Fehler ausgegeben z.B. HTTP 403 weil kein key im forum
                            AccInfo = ApiCall("https://api.guildwars2.com/v2/account?access_token=" + sqlrow[3])
                            #Wenn AccInfo nicht leer
                            print AccInfo
                            if AccInfo != "error" or AccInfo != "403":
                                print "Api konnte abgefragt werden"
                                #Rufe Funtion CompareAccInfo auf und uebergebe ApiData als Guildlist, Accinfo als Accinfo und sqlrow[0] (User ID im Forum) als UserId
                                CompareAccInfo(ApiData, AccInfo, sqlrow[0], sqlrow[3])
                            #Elif fuer API down spaeter einfuegen
                            elif AccInfo == "403":
                                print "API Key ungültig Foren und TS Rechte werdenn geloescht"
                                deleteUsertoGroup(db,sqlrow[0])
                                TsId = getTeamspeakUID(db, sqlrow[0])
                                for id in TsId:
                                    DeleteTSGroups(Tsdb, id[0])
                                delapikey_forum(db,sqlrow[0])
                        else:
                            print "User hat keinen API Schluessel eingetragen"
                            deleteUsertoGroup(db,sqlrow[0])
                            TsId = getTeamspeakUID(db, sqlrow[0])
                            for id in TsId:
                                DeleteTSGroups(Tsdb, id[0])
                            delapikey_forum(db,sqlrow[0])
                    #Liste aller Apischluessel zu TSUIDs in der TS3DB abfragen


                    #************************************ TS ***********************************************************
                    tsdata = gettsuid_api(Tsdb)
                    #fuer jede Zeile in tsdata
                    for tsrow in tsdata:
                        print ""
                        print "-----------------------------------------------TS--------------------------------------------"
                        print ""
                        #Accountinformationen von der API abfragen wenn fehler ausgeben
                        print "TS API Abfrage mit APIKEY:"
                        print tsrow[1]
                        print "für TS UID"
                        print tsrow[0]
                        AccInfo = ApiCall("https://api.guildwars2.com/v2/account?access_token=" + tsrow[1])
                        print AccInfo
                        if AccInfo != "error" and AccInfo != "403":
                            compareClientInfo(ApiData, AccInfo,tsrow)

                        elif AccInfo == "403":
                            print "Apischluessel konnte nicht abgfragt werden, TS Rechte werden komplett entfernt"
                            #TSID anhand UID suchen und TS Rechte entfernen komplett
                            DeleteTSGroups(Tsdb, tsrow[0])
                            #Forum DB auf Eintrag mit dieser TSUID existiert

                            #Lookup in Forendatenbank nach USERID anhand der TSUID/API Key
                            #Forenrechte entfernen !Jeder Gruppe muss bleiben!




                    # disconnect from sqlserver
                    db.commit
                    db.close()
                    Tsdb.commit
                    Tsdb.close()
                else:
                    print "API Daten des Gildenleiters konnten nicht abgerufen werden"









            #Start
            main()

      My HTML Form:

      <!DOCTYPE html>
<html lang="de">
<head>
    <meta charset="utf-8" />
    <title></title>
</head>
<style>
    body {
    background-image: url("Concept20.jpg");
    background-size: 1920px 1080px;
    }
    #one {
    font-family:Arial,sans-serif; 
    font-size: 16px;
    background-color: white;    
    }
    #two {
    font-family:Arial,sans-serif; 
    font-size: 20px;
    color:red;
    background-color: black;
    }
</style>
<body>
    <img src="http://theunbuffed.de/wcf/images/guildwars2/logo.gif" alt="Bild">
<p id="one">Die automatische Rechte Vergabe funktioniert nur als Gildenmitglied(Zweitaccounts oder Gildenfreunde ebenfalls). <br />
Solltet ihr nur als Gast auf unserem TS sein, könnt ihr darauf verzichten, gebt dem Spieler bescheid der euch die Adresse gegeben hat um im TS3 gezogen zu werden.<br />
<br />
<br />
Schlüssel kann in der <a href="https://account.arena.net/login" target="_blank">Arena Net Accountverwaltung</a> erstellt werden. <br />
    Account Berechtigung reicht aus. Der Schlüssel wird beim Verlassen der Gilde oder wenn er in der Arena Net Accountverwaltung entfernt wird, automatisch aus unserer Datenbank entfernt. <br />
    #DSGVO und so<br />
    <p id="two">Achtung! 
    Wenn ihr bereits in unserem Foren registriert seid, bitte dort den API Schlüssel und die TSUID im Profil hinterlegen! <br />
    Die Daten werden automatisch ins Teamspeak übertragen.<br />

    <form action="TS3Api.php" method="post">
<p>
        <label for="apikey">API Key:</label>
        <input type="text" name="apikey" id="apikey" size="150px" required />
    </p>
    <p>
        <label for="ts3uid">TS3 UID:</label>
        <input type="text" name="ts3uid" id="ts3uid" size="150px" class="readonly" required /> 
    </p>
    <input type="submit" value="Abschicken" >
    </form>
</body>
</html>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script>
    var urlParams = new URLSearchParams(window.location.search);
    var Param = urlParams.get('TSUID');
    Param = Param.replace(/\s/g, "+");
    $('#ts3uid').val(Param);
</script>

<script>
    $(".readonly").keydown(function(e){
        e.preventDefault();
    });
</script>

      My PHP Site behind the form
      <!DOCTYPE html>






      body {
      background-image: url("Concept20.png");
      background-size: 1920px 1080px;
      }

              </style>
        <body>
            <img src="http://theunbuffed.de/wcf/images/guildwars2/logo.gif" alt="Bild">
        </body>
        </html>


        <?php
        $servername = "localhost";
        $username = "ts3user";
        $password = "password";
        $dbname = "ts3db";

        /* Attempt MySQL server connection. Assuming you are running MySQL
        server with default setting (user 'root' with no password) */
        $link = mysqli_connect($servername, $username, $password, $dbname);

        // Check connection
        if($link === false){
            die("ERROR: Could not connect. " . mysqli_connect_error());
        }

        // Escape user inputs for security
        $apikey = mysqli_real_escape_string($link, $_REQUEST['apikey']);
        $tsuid = mysqli_real_escape_string($link, $_REQUEST['ts3uid']);



        // Attempt insert query execution
        $sql = sprintf("INSERT INTO apikey_tsuid (TSUID, APIKEY)
        VALUES ('%s', '%s')", $tsuid, $apikey);
        if(mysqli_query($link, $sql)){
            exec('python /var/www/vhosts/theunbuffed.de/httpdocs/api/Gw2Api.py >> /var/www/vhosts/theunbuffed.de/httpdocs/api/log/"`date -u +\%d\%m\%y_\%H\%M\%S`"log.txt');              
            echo "API Schlüssel wurde erfolgreich eingetragen";
        } else{
            echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
        }

        // Close connection
        mysqli_close($link);
        ?>

      If you want me to explain some parts or test it out you can write me ingame Akotun.1057 better mail then whisper since im mostly semi afk fixing this code these days hope it will help a bit to get an idea how you can do this

      /edit: the beginning of the php is not formated as code don't know why maybe because of the html sorry for that

    Sign In With Your GW2 Account or Register to comment.
    • Follow Us:

    Buy Now
    ©2010–2018 ArenaNet, LLC. All rights reserved. Guild Wars, Guild Wars 2, Heart of Thorns, Guild Wars 2: Path of Fire, ArenaNet, NCSOFT, the Interlocking NC Logo, and all associated logos and designs are trademarks or registered trademarks of NCSOFT Corporation. All other trademarks are the property of their respective owners.
    Privacy Policy | Legal Documentation