Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- registerPlugin({
- name: 'extremesupport',
- version: '1.0',
- description: 'Dieses Script unterstützt viele Support-Optionen.',
- author: 'Dennis Hoffmann',
- engines: '>= 0.9.16',
- vars: [
- //Supporter Groups (GroupId, GroupName, Permissions, Channels[ChannelId, timeToMessage]
- {
- name: 'groups',
- title: 'Groups',
- type: 'array',
- vars: [
- {name: 'id', title: 'Group Id', type: 'number'},
- {name: 'name', title: 'Group Name', type: 'string'},
- {name: 'permissions', title: 'Group Permissions', type: 'string'},
- {
- name: 'channels',
- title: 'Support Channels',
- type: 'array',
- vars: [
- {name: 'id', title: 'Channel Id', type: 'channel'},
- {name: 'time', title: 'Time to Info-Message', type: 'number'}
- ]
- }
- ]
- },
- //Supporter Users (UserUid, UserName, Permissions, Channels[ChannelId, timeToMessage]
- {
- name: 'users',
- title: 'Users',
- type: 'array',
- vars: [
- {name: 'uid', title: 'Users Uid', type: 'string'},
- {name: 'name', title: 'User Name', type: 'string'},
- {name: 'permissions', title: 'User Permissions', type: 'string'},
- {
- name: 'channels',
- title: 'Support Channels',
- type: 'array',
- vars: [
- {name: 'id', title: 'Channel Id', type: 'channel'},
- {name: 'time', title: 'Time to Info-Message', type: 'number'}
- ]
- }
- ]
- },
- //Support Log Channels (ChannelId)
- {
- name: 'logs',
- title: 'Log-Channels',
- type: 'array',
- vars: [
- {name: 'id', title: 'Channel Id', type: 'channel'}
- ]
- },
- //Support Info Channels (ChannelId, MessageType, Message)
- {
- name: 'infos',
- title: 'Info-Channels',
- type: 'array',
- vars: [
- {name: 'id', title: 'Channel Id', type: 'channel'},
- {name: 'type', title: 'Message Type', type: 'select', options: ['Poke', 'Chat']},
- {name: 'message', title: 'Message', type: 'multiline'}
- ]
- },
- {
- name: 'blockedchannels',
- title: 'Blocked Channels',
- type: 'array',
- vars: [
- {name: 'id', title: 'Channel Id', type: 'channel'}
- ]
- },
- { name: 'supportWarteraum', title: 'Support Warteraum', type: 'channel'},
- {
- name: 'autoSupports',
- title: 'Auto Support',
- type: 'array',
- vars: [
- {name: 'supmenuid', title: 'Support Chat Id', type: 'number'},
- {name: 'supchatmsg', title: 'Support Chat Message', type: 'multiline'},
- {name: 'supnewchannel', title: 'Move to Channel', type: 'channel'},
- {
- name: 'awnsers',
- title: 'Antworten',
- type: 'array',
- vars: [
- {name: 'value', title: 'Antwort', type: 'string'},
- {name: 'newmenuid', title: 'Neue Support Chat Id', type: 'number'}
- ]
- }
- ]
- }
- ]
- }, function(sinusbot, config) {
- /*
- Store:
- _0 Wartezeit
- _1 toga
- _2 menuid
- */
- var store = require('store');
- var event = require('event');
- var backend = require('backend');
- var db = require('db');
- var engine = require('engine');
- var helpers = require('helpers');
- var dbc = db.connect({ driver: 'mysql', host: '127.0.0.1', username: '******', password: '******', database: '******' }, function(err) {
- if (err) { engine.log(err); }
- });
- function getMenu(client){
- var value = store.get(client.uid() + "_2");
- if(value == null)value = -1;
- return value;
- }
- function setMenu(client, value){
- store.set(client.uid() + "_2", value);
- }
- function resetMenu(client){
- store.unset(client.uid() + "_2");
- }
- function getToga(client){
- var value = store.get(client.uid() + "_1");
- if(value == null)value = 1;
- return value;
- }
- function setToga(client, value){
- store.set(client.uid() + "_1", value);
- }
- function toggleToga(client){
- if(isInAnyGroup(client) == 0){
- return -1;
- }
- if(getToga(client) == 0){
- setToga(client, 1);
- return 1;
- }
- else {
- setToga(client, 0);
- return 0;
- }
- }
- function getGamesupport(client){
- var value = store.get(client.uid() + "_3");
- if(value == null)value = 0;
- return value;
- }
- function setGamesupport(client, value){
- store.set(client.uid() + "_3", value);
- }
- function toggleGamesupport(client){
- if(isInAnyGroup(client) == 0){
- return -1;
- }
- if(getGamesupport(client) == 0){
- setGamesupport(client, 1);
- return 1;
- }
- else {
- setGamesupport(client, 0);
- return 0;
- }
- }
- function ticketUnset(key){
- store.unset(key + "_0");
- }
- function ticketReset(key){
- store.set(key + "_0", 0);
- }
- function ticketGet(key){
- var value = store.get(key + "_0");
- if(value == null)value = -1;
- return value;
- }
- function ticketAdd(key){
- var value = ticketGet(key) + 1;
- store.set(key + "_0", value);
- return value;
- }
- function isSupportGroupChannel(channel){
- var bool = 0;
- if(channel != null && config.groups != null){
- config.groups.forEach(function(group) {
- if(group.channels != null && bool == 0){
- group.channels.forEach(function(ch) {
- if(channel.id() == ch.id)bool = 1;
- });
- }
- });
- }
- return bool;
- }
- function isSupportUserChannel(channel){
- var bool = 0;
- if(channel != null && config.users != null){
- config.users.forEach(function(user) {
- if(user.channels != null && bool == 0){
- user.channels.forEach(function(ch) {
- if(channel.id() == ch.id)bool = 1;
- });
- }
- });
- }
- return bool;
- }
- function isSupportLogChannel(channel){
- var bool = 0;
- if(channel != null && config.logs != null){
- config.logs.forEach(function(ch) {
- if(channel.id() == ch.id)bool = 1;
- });
- }
- return bool;
- }
- function isSupportInfoChannel(channel){
- var bool = 0;
- if(channel != null && config.infos != null){
- config.infos.forEach(function(ch) {
- if(channel.id() == ch.id)bool = 1;
- });
- }
- return bool;
- }
- function getUserConfigGroupName(client){
- var value = "";
- if(client != null && config.groups != null){
- client.getServerGroups().forEach(function(cgroup) {
- config.groups.forEach(function (group) {
- if(cgroup.id() == group.id)value = group.name;
- });
- });
- }
- return value;
- }
- function getUserConfigUserName(client){
- var value = "";
- if(client != null && config.users != null){
- config.users.forEach(function (user) {
- if(client.uid() == user.uid)value = user.name;
- });
- }
- return value;
- }
- function isInAnyGroup(client){
- var bool = 0;
- if(getUserConfigGroupName(client) != "")bool = 1;
- else if(getUserConfigUserName(client) != "")bool = 1;
- return bool;
- }
- function log(fromChannel,toChannel,client,invoker){
- if (dbc) dbc.exec("INSERT INTO log (supporterName,supporterUid,configGroup,userName,userUid,fromChannelName,fromChannelId,toChannelName,toChannelId) VALUES ('"+invoker.name()+"','"+invoker.uid()+"','"+getUserConfigGroupName(invoker)+"','"+client.name()+"','"+client.uid()+"','"+fromChannel.name()+"','"+fromChannel.id()+"','"+toChannel.name()+"','"+toChannel.id()+"')");
- else invoker.chat("Es ist ein Problem mit dem Bot aufgetreten, bitte leite dies an das Management weiter.");
- }
- function getClickableName(client){
- var name = client.name().replace('[', '%5C%5B').replace(']', '%5C%5D').replace(' ', '%20').replace('[', '%5C%5B').replace(']', '%5C%5D').replace(' ', '%20').replace('[', '%5C%5B').replace(']', '%5C%5D').replace(' ', '%20').replace('[', '%5C%5B').replace(']', '%5C%5D').replace(' ', '%20').replace('[', '%5C%5B').replace(']', '%5C%5D').replace(' ', '%20').replace(' ', '%20').replace(' ', '%20').replace(' ', '%20').replace(' ', '%20').replace(' ', '%20').replace(' ', '%20').replace(' ', '%20');
- name = "[URL=client://0/" + client.uid() + "~" + name + "]" + client.name() + "[/URL]";
- return name;
- }
- function getSupportMessage(client, channel){
- return "[b][color=blue][Support][/color] " + getClickableName(client) + " benötigt Hilfe im [color=green]" + channel.name() + "[/color]![/b]";
- }
- function sendGroupMessage(groupid, msg){
- backend.getClients().forEach(function(client) {
- client.getServerGroups().forEach(function(group) {
- if(group.id() == groupid && getToga(client) == 1 && !client.isDeaf() && (isInBlockedChannel(client) == 0 || getGamesupport(client) == 1))client.chat(msg);
- });
- });
- }
- function getPermissions(client){
- var perm = "";
- if(client != null){
- if(config.groups != null){
- client.getServerGroups().forEach(function(group) {
- config.groups.forEach(function(cgroup) {
- if(group.id() == cgroup.id){
- perm = cgroup.permissions;
- }
- });
- });
- }
- if(config.users != null) {
- config.users.forEach(function (user) {
- if (user.uid == client.uid()) {
- if (perm == "") perm = user.permissions;
- else perm = perm + "," + user.permissions;
- }
- });
- }
- }
- return perm;
- }
- function hasPermission(client, permission){
- value = 0;
- var perm = getPermissions(client);
- if(perm != "") {
- permis = perm.split(",");
- if(permis != null) {
- permis.forEach(function (per) {
- if (per == permission) value = 1;
- });
- }
- }
- return value;
- }
- function isInBlockedChannel(client){
- value = 0;
- if(config.blockedchannels != null){
- config.blockedchannels.forEach(function(channel) {
- client.getChannels().forEach(function(chan) {
- if (channel.id == chan.id()) value = 1;
- });
- });
- }
- return value;
- }
- function isABlockedChannel(channel){
- value = 0;
- if(channel != null && config.blockedchannels != null){
- config.blockedchannels.forEach(function(chan) {
- if (chan.id == channel.id()) value = 1;
- });
- }
- return value;
- }
- function hasGroup(client, groupid){
- var bool = 0;
- if(client != null){
- client.getServerGroups().forEach(function(group) {
- if(group.id() == groupid)bool = 1;
- });
- }
- return bool;
- }
- function callMenu(client){
- var menu = getMenu(client);
- if(menu == -1){
- resetMenu(client);
- }
- else if(config.autoSupports != null){
- config.autoSupports.forEach(function(autoSup) {
- if(autoSup.supmenuid != null && autoSup.supmenuid == menu){
- if(autoSup.supchatmsg != "") client.chat(autoSup.supchatmsg);
- if(autoSup.supnewchannel != -1) client.moveTo(autoSup.supnewchannel);
- }
- });
- }
- }
- function sendServerMessage(msg, poke){
- backend.getClients().forEach(function(client) {
- if(poke)client.poke(msg);
- else client.chat(msg);
- });
- }
- function sendTeamMessage(msg, poke){
- backend.getClients().forEach(function(client) {
- if(isInAnyGroup(client) == 1){
- if(poke)client.poke(msg);
- else client.chat(msg);
- }
- });
- }
- function sendOnlineInfo(client, username){
- var bool = 0;
- backend.getClients().forEach(function(user) {
- if(user.description() == '('+username+') '){
- bool = 1;
- client.chat("[b][color=blue][Information][/color] Der Spieler "+getClickableName(user)+" ("+username+") befindet sich im [color=green]"+user.getChannels()[0].name()+"[/color][/b] ");
- }
- });
- if(bool == 0){
- client.chat("[b][color=blue][Information][/color] [color=red]Es ist kein Spieler mit dem Forumnamen ("+username+") online.[/color][/b] ");
- }
- }
- function sendSecondChat(clientX){
- backend.getChannels().forEach(function(channel){
- if(isSupportUserChannel(channel) || isSupportGroupChannel(channel)) {
- if (isSupportGroupChannel(channel) == 1) {
- channel.getClients().forEach(function (client) {
- value = ticketGet(client.uid());
- if (config.groups != null) {
- config.groups.forEach(function (group) {
- if (group.channels != null && hasGroup(clientX, group.id) == 1) {
- group.channels.forEach(function (cchannel) {
- if (cchannel.id == channel.id() && cchannel.time <= value && getToga(clientX) == 1) clientX.chat(getSupportMessage(client, channel));
- });
- }
- });
- }
- });
- }
- if (isSupportUserChannel(channel) == 1) {
- channel.getClients().forEach(function (client) {
- var value = ticketGet(client.uid());
- if (config.users != null) {
- config.users.forEach(function (user) {
- if (user.channels != null && user.uid == clientC.uid()) {
- user.channels.forEach(function (cchannel) {
- if (cchannel.id == channel.id() && cchannel.time <= value && getToga(clientX) == 1) clientX.chat(getSupportMessage(client, channel));
- });
- }
- });
- }
- });
- }
- }
- });
- }
- setInterval(function() {
- backend.getChannels().forEach(function(channel){
- if(isSupportUserChannel(channel) || isSupportGroupChannel(channel)) {
- if (isSupportGroupChannel(channel) == 1) {
- channel.getClients().forEach(function (client) {
- value = ticketAdd(client.uid());
- if (config.groups != null) {
- config.groups.forEach(function (group) {
- if (group.channels != null) {
- group.channels.forEach(function (cchannel) {
- if (cchannel.id == channel.id() && cchannel.time == value) sendGroupMessage(group.id, getSupportMessage(client, channel));
- });
- }
- });
- }
- });
- }
- if (isSupportUserChannel(channel) == 1) {
- var bool = isSupportGroupChannel(channel);
- channel.getClients().forEach(function (client) {
- var value = ticketGet(client.uid());
- if(bool == 0)value = ticketAdd(client.uid());
- if (config.users != null) {
- config.users.forEach(function (user) {
- if (user.channels != null) {
- var sup = backend.getClientByUID(user.uid);
- if(sup != null && getToga(sup) == 1 && !sup.isDeaf() && (isInBlockedChannel(sup) == 0 || getGamesupport(sup) == 1)) {
- user.channels.forEach(function (cchannel) {
- if (cchannel.id == channel.id() && cchannel.time == value) sup.chat(getSupportMessage(client, channel));
- });
- }
- }
- });
- }
- });
- }
- }
- });
- }, 60000);
- event.on('clientUndeaf', function(client) {
- if(isInAnyGroup(client) == 1){
- sendSecondChat(client);
- }
- });
- event.on('clientMove', function(ev) {
- if(ev.invoker != null && isSupportLogChannel(ev.toChannel) == 1){
- log(ev.fromChannel, ev.toChannel, ev.client, ev.invoker);
- }
- if(isSupportGroupChannel(ev.toChannel) == 1 || isSupportUserChannel(ev.toChannel) == 1){
- ticketReset(ev.client.uid());
- }
- else {
- ticketUnset(ev.client.uid());
- }
- if(isSupportInfoChannel(ev.toChannel) == 1){
- if(config.infos != null){
- config.infos.forEach(function(info) {
- if(ev.toChannel.id() == info.id){
- if(info.type == 0)ev.client.poke(info.message);
- else if(info.type == 1)ev.client.chat(info.message);
- }
- });
- }
- }
- if(ev.fromChannel == null || isABlockedChannel(ev.fromChannel) == 1){
- if(isInAnyGroup(ev.client)){
- if(ev.toChannel != null && isABlockedChannel(ev.toChannel) == 0){
- sendSecondChat(ev.client);
- }
- }
- }
- if(ev.toChannel != null && ev.toChannel.id() == config.supportWarteraum){
- setMenu(ev.client, 1);
- callMenu(ev.client);
- }
- else {
- resetMenu(ev.client);
- }
- });
- event.on('chat', function(ev) {
- if (!ev.client.isSelf()) {
- var vars = ev.text.split(" ");
- var cmd = vars[0];
- var cmdmsg = "";
- for(var i = 1; i < vars.length; i++){
- if(i != 1) cmdmsg += " ";
- cmdmsg += vars[i];
- }
- if(ev.text == '!toga'){
- if(isInAnyGroup(ev.client) == 1){
- var value = toggleToga(ev.client);
- if(value == 0)ev.client.chat("[b][color=blue][Einstellungen][/color] Informationen: [color=red]deaktiviert[/color][/b]");
- else if(value == 1)ev.client.chat("[b][color=blue][Einstellungen][/color] Informationen: [color=green]aktiviert[/color][/b]");
- }
- }
- else if(ev.text == '!gamesupport'){
- if(isInAnyGroup(ev.client) == 1){
- var value = toggleGamesupport(ev.client);
- if(value == 0)ev.client.chat("[b][color=blue][Einstellungen][/color] GameSupport: [color=red]deaktiviert[/color][/b]");
- else if(value == 1)ev.client.chat("[b][color=blue][Einstellungen][/color] GameSupport: [color=green]aktiviert[/color][/b]");
- }
- }
- else if(ev.text == '!reload'){
- if(isInAnyGroup(ev.client) == 1){
- if(hasPermission(ev.client, "Reload")) {
- engine.reloadScripts();
- ev.client.chat("[b][color=blue][Info][/color] Du hast das Script [color=red]neugestartet[/color]![/b]");
- }
- }
- }
- else if(ev.text == '!getkeys'){
- if(isInAnyGroup(ev.client) == 1){
- if(hasPermission(ev.client, "Getkeys")) {
- var permissi = getPermissions(ev.client);
- if(permissi != ""){
- var permissions = permissi.split(",");
- var groups = "";
- permissions.forEach(function(permission) {
- if(permission != null)groups = groups + ",'" + permission + "' ";
- });
- groups = "(" + groups.replace(",","") + ")";
- var sql = "SELECT * FROM view_infolog WHERE lastGroup IN " + groups + " ORDER BY lastDate DESC";
- var permsk = hasPermission(ev.client, "GetAllKeys");
- if(permsk == 0)ev.client.chat("[b][color=blue][Info][/color] [color=red]last24h[/color] - [color=red]last7d[/color] - [color=green]Name[/color] - [color=purple]Gruppe[/color] - [color=gray]uid[/color][/b]");
- else ev.client.chat("[b][color=blue][Info][/color] [color=gray]lastDate[/color] - [color=red]last24h[/color] - [color=red]last7d[/color] - [color=red]lastMonth[/color] - [color=red]total[/color] - [color=green]Name[/color] - [color=purple]Gruppe[/color] - [color=gray]uid[/color][/b]");
- if (dbc) dbc.query(sql, function(err, res) {
- if (!err) {
- res.forEach(function(row) {
- if(permsk == 0)ev.client.chat("[b][color=blue][Info][/color] [color=red]" + helpers.toString(row.last24h) + "[/color] - [color=red]" + helpers.toString(row.last7d) + "[/color] - [color=green]" + helpers.toString(row.lastName) + "[/color] - [color=purple]" + helpers.toString(row.lastGroup) + "[/color] - [color=gray]" + helpers.toString(row.supporterUid) + "[/color][/b]");
- else ev.client.chat("[b][color=blue][Info][/color] [color=gray]" + helpers.toString(row.lastDate) + "[/color] - [color=red]" + helpers.toString(row.last24h) + "[/color] - [color=red]" + helpers.toString(row.last7d) + "[/color] - [color=red]" + helpers.toString(row.lastMonth) + "[/color] - [color=red]" + helpers.toString(row.total) + "[/color] - [color=green]" + helpers.toString(row.lastName) + "[/color] - [color=purple]" + helpers.toString(row.lastGroup) + "[/color] - [color=gray]" + helpers.toString(row.supporterUid) + "[/color][/b]");
- });
- }
- });
- }
- }
- }
- }
- else if(ev.text == '!resetoptions'){
- if(isInAnyGroup(ev.client) == 1){
- if(hasPermission(ev.client, "ResetOptions")) {
- store.getKeys().forEach(function(key) {
- store.unset(key);
- });
- ev.client.chat("[b][color=blue][Info][/color] Du hast alle Client-Einstellungen [color=red]zurückgesetzt[/color]![/b]");
- }
- }
- }
- else if(ev.text == '!help'){
- if(isInAnyGroup(ev.client) == 1){
- ev.client.chat("!help - zeigt diese List an");
- ev.client.chat("!toga - (de)aktiviert die Supportnachrichten");
- ev.client.chat("!gamesupport - (de)aktiviert die Supportnachrichten wenn man im Spiel ist. (Standartmäßig deaktiviert)");
- ev.client.chat("!getuser [Forumname] - Gibt den Spieler zurück, wenn er sich auf dem Teamspeak befindet");
- if(hasPermission(ev.client, "Getkeys")) {
- ev.client.chat("!getkeys - zeigt eine Liste der letzten Supporttätigkeiten eines Teammitgliedes");
- }
- if(hasPermission(ev.client, "Reload")) {
- ev.client.chat("!reload - lädt das Script neu (Notwendig, falls Beispielsweise keine Verbindung zur Datenbank besteht)");
- }
- if(hasPermission(ev.client, "ResetOptions")) {
- ev.client.chat("!resetoptions - schaltet bei allen Teammitgliedern wieder die Support-Benachrichtigungen an");
- }
- if(hasPermission(ev.client, "serverinfochat")) {
- ev.client.chat("!serverinfochat [Nachricht] - Schreibt alle Spieler auf den Server privat mit der [Nachricht] an");
- }
- if(hasPermission(ev.client, "serverinfopoke")) {
- ev.client.chat("!serverinfopoke [Nachricht] - Stubst alle Spieler auf den Server mit der [Nachricht] an");
- }
- if(hasPermission(ev.client, "teaminfochat")) {
- ev.client.chat("!teaminfochat [Nachricht] - Schreibt alle Teammitglieder auf den Server privat mit der [Nachricht] an");
- }
- if(hasPermission(ev.client, "teaminfopoke")) {
- ev.client.chat("!teaminfopoke [Nachricht] - Stubst alle Teammitglieder auf den Server mit der [Nachricht] an");
- }
- }
- }
- else if(cmd == "!serverinfochat"){
- if(isInAnyGroup(ev.client) == 1){
- if(hasPermission(ev.client, "serverinfochat")) {
- sendServerMessage(cmdmsg, false);
- }
- }
- }
- else if(cmd == "!serverinfopoke"){
- if(isInAnyGroup(ev.client) == 1){
- if(hasPermission(ev.client, "serverinfopoke")) {
- sendServerMessage(cmdmsg, true);
- }
- }
- }
- else if(cmd == "!teaminfochat"){
- if(isInAnyGroup(ev.client) == 1){
- if(hasPermission(ev.client, "teaminfochat")) {
- sendTeamMessage(cmdmsg, false);
- }
- }
- }
- else if(cmd == "!teaminfopoke"){
- if(isInAnyGroup(ev.client) == 1){
- if(hasPermission(ev.client, "teaminfopoke")) {
- sendTeamMessage(cmdmsg, true);
- }
- }
- }
- else if(cmd == "!getuser"){
- if(isInAnyGroup(ev.client) == 1){
- sendOnlineInfo(ev.client, cmdmsg);
- }
- }
- else {
- ev.client.getChannels().forEach(function (channel) {
- if (channel.id() == config.supportWarteraum) {
- var chatid = getMenu(ev.client);
- if (config.autoSupports != null) {
- config.autoSupports.forEach(function (autoSup) {
- if (chatid == autoSup.supmenuid) {
- var bool = 0;
- if(autoSup.awnsers != null){
- autoSup.awnsers.forEach(function(awnser) {
- if(awnser.value == ev.text){
- setMenu(ev.client, awnser.newmenuid);
- callMenu(ev.client);
- bool = 1;
- }
- });
- }
- if(bool == 0){
- ev.client.chat("[b][color=darkred]Ungültige Eingabe![/color] Als Antwort wird eine Zahl erwartet![/b]");
- }
- }
- });
- }
- }
- });
- }
- }
- });
- });
- /*
- ** VIEW INFOLOG **
- SELECT
- (SELECT datum FROM log WHERE supporterUid = A1.supporterUid ORDER BY datum DESC LIMIT 1) AS lastDate,
- (SELECT supporterName FROM log WHERE supporterUid = A1.supporterUid ORDER BY datum DESC LIMIT 1) AS lastName,
- (SELECT configGroup FROM log WHERE supporterUid = A1.supporterUid ORDER BY datum DESC LIMIT 1) AS lastGroup,
- (SELECT Count(*) FROM log WHERE supporterUid = A1.supporterUid AND TIMEDIFF(current_timestamp, datum) <= 240000 ORDER BY datum DESC LIMIT 1) AS last24h,
- (SELECT Count(*) FROM log WHERE supporterUid = A1.supporterUid AND DATEDIFF(current_timestamp, datum) <= 7 ORDER BY datum DESC LIMIT 1) AS last7d,
- (SELECT Count(*) FROM log WHERE supporterUid = A1.supporterUid AND DATEDIFF(current_timestamp, datum) <= 30 ORDER BY datum DESC LIMIT 1) AS lastMonth,
- Count(*) AS total,
- supporterUid
- FROM `log` A1 WHERE 1 GROUP BY supporterUid ORDER BY datum DESC
- ** VIEW GROUPINFO **
- SELECT
- configGroup,
- ROUND(100/(SELECT Count(*) FROM log WHERE TIMEDIFF(current_timestamp, datum) <= 240000)*(SELECT Count(*) FROM log WHERE TIMEDIFF(current_timestamp, datum) <= 240000 AND configGroup = A1.configGroup), 1) AS last24hProzent,
- (SELECT Count(*) FROM log WHERE TIMEDIFF(current_timestamp, datum) <= 240000 AND configGroup = A1.configGroup) AS last24hCount,
- (SELECT Count(*) FROM log WHERE TIMEDIFF(current_timestamp, datum) <= 240000) AS last24hCountAll,
- ROUND(100/(SELECT Count(*) FROM log WHERE DATEDIFF(current_timestamp, datum) <= 7)*(SELECT Count(*) FROM log WHERE DATEDIFF(current_timestamp, datum) <= 7 AND configGroup = A1.configGroup), 1) AS last7dProzent,
- (SELECT Count(*) FROM log WHERE DATEDIFF(current_timestamp, datum) <= 7 AND configGroup = A1.configGroup) AS last7dCount,
- (SELECT Count(*) FROM log WHERE DATEDIFF(current_timestamp, datum) <= 7) AS last7dCountAll,
- ROUND(100/(SELECT Count(*) FROM log WHERE DATEDIFF(current_timestamp, datum) <= 30)*(SELECT Count(*) FROM log WHERE DATEDIFF(current_timestamp, datum) <= 30 AND configGroup = A1.configGroup), 1) AS last30dProzent,
- (SELECT Count(*) FROM log WHERE DATEDIFF(current_timestamp, datum) <= 30 AND configGroup = A1.configGroup) AS last30dCount,
- (SELECT Count(*) FROM log WHERE DATEDIFF(current_timestamp, datum) <= 30) AS last30dCountAll,
- ROUND(100/(SELECT Count(*) FROM log)*Count(*), 1) AS totalProzent,
- Count(*) AS totalCount,
- (SELECT Count(*) FROM log) AS totalCountAll
- FROM log A1 GROUP BY configGroup
- ** VIEW GROUPCHANNELINFO **
- SELECT
- fromChannelName,
- configGroup,
- ROUND(100/(SELECT Count(*) FROM log WHERE fromChannelId = A1.fromChannelId AND TIMEDIFF(current_timestamp, datum) <= 240000)*(SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND TIMEDIFF(current_timestamp, datum) <= 240000),1) AS last24hProzent,
- (SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND TIMEDIFF(current_timestamp, datum) <= 240000) AS last24hCount,
- (SELECT Count(*) FROM log WHERE fromChannelId = A1.fromChannelId AND TIMEDIFF(current_timestamp, datum) <= 240000) AS last24hCountAll,
- ROUND(100/(SELECT Count(*) FROM log WHERE fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 7)*(SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 7),1) AS last7dProzent,
- (SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 7) AS last7dCount,
- (SELECT Count(*) FROM log WHERE fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 7) AS last7dCountAll,
- ROUND(100/(SELECT Count(*) FROM log WHERE fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 30)*(SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 30),1) AS last30dProzent,
- (SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 30) AS last30dCount,
- (SELECT Count(*) FROM log WHERE fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 30) AS last30dCountAll,
- ROUND(100/(SELECT Count(*) FROM log WHERE fromChannelId = A1.fromChannelId)*Count(*),1) AS totalProzent,
- Count(*) AS totalCount,
- (SELECT Count(*) FROM log WHERE fromChannelId = A1.fromChannelId) AS totalCountAll
- FROM log A1 WHERE fromChannelId IN ('1387','1389','1386','1390','1991') GROUP BY configGroup,fromChannelId ORDER BY fromChannelName, configGroup
- ** VIEW USERCHANNELINFO **
- SELECT
- supporterName,
- configGroup,
- fromChannelName,
- ROUND(100/(SELECT Count(*) FROM log WHERE fromChannelId IN ('1387','1389','1386','1390','1991') AND A1.fromChannelId AND TIMEDIFF(current_timestamp, datum) <= 240000 AND supporterUid = A1.supporterUid)*(SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND A1.fromChannelId AND TIMEDIFF(current_timestamp, datum) <= 240000 AND supporterUid = A1.supporterUid),1) AS last24hProzent,
- (SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND A1.fromChannelId AND TIMEDIFF(current_timestamp, datum) <= 240000 AND supporterUid = A1.supporterUid) AS last24hCount,
- (SELECT Count(*) FROM log WHERE fromChannelId IN ('1387','1389','1386','1390','1991') AND A1.fromChannelId AND TIMEDIFF(current_timestamp, datum) <= 240000 AND supporterUid = A1.supporterUid) AS last24hCountAll,
- ROUND(100/(SELECT Count(*) FROM log WHERE fromChannelId IN ('1387','1389','1386','1390','1991') AND DATEDIFF(current_timestamp, datum) <= 7 AND supporterUid = A1.supporterUid)*(SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 7 AND supporterUid = A1.supporterUid),1) AS last7dProzent,
- (SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 7 AND supporterUid = A1.supporterUid) AS last7dCount,
- (SELECT Count(*) FROM log WHERE fromChannelId IN ('1387','1389','1386','1390','1991') AND DATEDIFF(current_timestamp, datum) <= 7 AND supporterUid = A1.supporterUid) AS last7dCountAll,
- ROUND(100/(SELECT Count(*) FROM log WHERE fromChannelId IN ('1387','1389','1386','1390','1991') AND DATEDIFF(current_timestamp, datum) <= 30 AND supporterUid = A1.supporterUid)*(SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 30 AND supporterUid = A1.supporterUid),1) AS last30dProzent,
- (SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND DATEDIFF(current_timestamp, datum) <= 30 AND supporterUid = A1.supporterUid) AS last30dCount,
- (SELECT Count(*) FROM log WHERE fromChannelId IN ('1387','1389','1386','1390','1991') AND DATEDIFF(current_timestamp, datum) <= 30 AND supporterUid = A1.supporterUid) AS last30dCountAll,
- ROUND(100/(SELECT Count(*) FROM log WHERE fromChannelId IN ('1387','1389','1386','1390','1991') AND supporterUid = A1.supporterUid)*(SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND supporterUid = A1.supporterUid),1) AS totalProzent,
- (SELECT Count(*) FROM log WHERE configGroup = A1.configGroup AND fromChannelId = A1.fromChannelId AND supporterUid = A1.supporterUid) AS totalCount,
- (SELECT Count(*) FROM log WHERE fromChannelId IN ('1387','1389','1386','1390','1991') AND supporterUid = A1.supporterUid) AS totalCountAll
- FROM log A1 WHERE fromChannelId IN ('1387','1389','1386','1390','1991') GROUP BY supporterUid,fromChannelId ORDER BY supporterUid,last24hProzent DESC
- */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement