dotfiles/dot_local/bin/executable_mjru-infa

#!/usr/bin/env bash

set -e
set -o pipefail

MYSQL_USER="${MYSQL_USER:-root}"
MYSQL_PASSWORD="${MYSQL_PASSWORD:-$(pass show majordomo/public/maxscale.intr/root)}"
MYSQL_SERVER="${MYSQL_SERVER:-maxscale.intr}"

help_main()
{
    echo "\
Usage: mjru-infa COMMANDS ARGS...
Run COMMANDS with ARGS

COMMAND must be one of the sub-commands listed below:

   server
   switch
   user
   site

Report bugs to: go.wigust@gmail.com."
}

case "$1" in
    --help)
        help_main
        ;;
    server)
        mysql -s -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_SERVER" billing <<EOF \
            | awk '{$NF = "https://billing2.intr/servers/edit/"$NF; print}' \
            | expand -t1 | column -t
SELECT equip_datacenters.name AS dc, equip_racks.name AS rack, equip_servers.name AS name, equip_servers.dcid AS dcid, equip_servers.datacenter_number AS dcnum, equip_servers.equip_server_id AS bil
FROM equip_servers, equip_racks, equip_datacenters
WHERE equip_servers.equip_rack_id = equip_racks.equip_rack_id AND equip_racks.equip_datacenter_id = equip_datacenters.equip_datacenter_id
ORDER BY equip_racks.name;
EOF
        ;;
    client)
        mysql -s -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_SERVER" billing <<EOF
SELECT equip_servers.name,clients.email
FROM colo_accounts, equip_servers, clients
WHERE colo_accounts.equip_server_id = equip_servers.equip_server_id AND clients.client_id = colo_accounts.client_id AND equip_servers.name LIKE "mj%";
EOF
        ;;
    disks)
        mysql -s -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_SERVER" billing <<EOF
SELECT equip_components.install_date,equip_components.capacity,equip_servers.name,equip_components.description
FROM equip_components,equip_servers
WHERE equip_servers.equip_server_id=equip_components.equip_server_id
 AND (equip_components.type='hdd' or equip_components.type='raid')
 AND (equip_servers.equip_server_type_id='5' or equip_servers.equip_server_type_id='3' OR equip_servers.equip_server_type_id='2')
ORDER BY equip_components.install_date;
EOF
        ;;
    switch)
        mysql -s -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_SERVER" billing <<EOF \
            | awk '{$NF = "https://billing2.intr/switch/edit/"$NF; print}' \
            | expand -t1 | column -t
SELECT equip_datacenters.name AS dc, equip_racks.name AS rack, equip_switches.snmp_ip, equip_switches.vendor, equip_switches.model AS name, equip_switches.speed, equip_switches.equip_switch_id FROM equip_switches, equip_racks, equip_datacenters WHERE equip_switches.equip_rack_id = equip_racks.equip_rack_id AND equip_racks.equip_datacenter_id = equip_datacenters.equip_datacenter_id ORDER BY equip_racks.name;
EOF
        ;;
    user)
        mysql -s -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_SERVER" billing <<EOF \
            | awk '{$NF = "https://billing2.intr/servers/edit/"$NF; print}' \
            | expand -t1 | column -t
SELECT equip_datacenters.name AS dc, equip_racks.name AS rack, equip_servers.name AS name, equip_servers.dcid AS dcid, equip_servers.datacenter_number AS dcnum, equip_servers.equip_server_id AS bil
FROM equip_servers, equip_racks, equip_datacenters
WHERE equip_servers.equip_rack_id = equip_racks.equip_rack_id AND equip_racks.equip_datacenter_id = equip_datacenters.equip_datacenter_id AND equip_servers.name LIKE 'mj%'
ORDER BY equip_racks.name;
EOF
        ;;
    site)
        curl --silent https://www.majordomo.ru/sitemap.xml | xq --raw-output '.urlset.url[] | .loc' | sort --unique
        ;;
    ip-addresses)
        while read line
        do
            echo $line
        done < <(
            mysql -N -s -q -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_SERVER" billing <<EOF
SELECT address,mask FROM equip_ip_networks WHERE public=1 AND address!='78.108.81.0' AND address!='78.108.84.0';
EOF
            )
        ;;
    ip-assigned)
        mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_SERVER" billing <<EOF
SELECT vds_accounts_ipaddresses.vds_account_id,equip_ip_addresses.address
FROM vds_accounts, vds_accounts_ipaddresses, equip_ip_addresses
WHERE vds_accounts_ipaddresses.equip_ip_address_id=equip_ip_addresses.equip_ip_address_id
GROUP BY vds_accounts_ipaddresses.equip_ip_address_id
LIMIT 350;
EOF
        ;;
    ip)
        mysql -s -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_SERVER" billing <<EOF | jq -s | jq 'reduce .[] as $d (null; .[$d.name] += [$d.address])'
SELECT CONCAT('{ "name": "', equip_servers.name, '", "address": "',equip_ip_addresses.address, '" }')
FROM equip_servers, equip_servers_ipaddresses, equip_ip_addresses
WHERE equip_servers.equip_server_id=equip_servers_ipaddresses.equip_server_id
AND equip_servers_ipaddresses.equip_ip_address_id=equip_ip_addresses.equip_ip_address_id;
EOF
        ;;
    vm)
        mysql -s -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_SERVER" billing <<EOF | sort --numeric-sort
SELECT vds_accounts.vds_account_id AS host, equip_ip_addresses.address AS ip
FROM equip_ip_addresses, vds_accounts
WHERE vds_accounts.client_id = ${2:-17959} AND equip_ip_addresses.equip_ip_address_id = vds_accounts.equip_ip_address_id;
EOF
        ;;
    *)
        help_main
        ;;
esac