Initial version

2 files changed, 185 insertions, 0 deletions

diff --git a/acp-storage-report.py b/acp-storage-report.py
new file mode 100644
index 0000000..5d4ec22
--- /dev/null
+++ b/acp-storage-report.py
@@ -0,0 +1,167 @@
+# -*- coding: utf-8 -*-
+
+from datetime import datetime
+import json
+from urllib import request, parse
+from calendar import monthrange
+import smtplib
+from email.message import EmailMessage
+import argparse
+import configparser
+
+influx_query = "SELECT sum(diskusage) FROM acpdiskusage WHERE time >= '{}T22:00:00Z' AND time < '{}T22:16:00Z' AND host = 'se-tug-acp-storage01.nordu.net' AND share =~ /^{}/"
+
+quarters = {
+    1: ['01', '02', '03'],
+    2: ['04', '05', '06'],
+    3: ['07', '08', '09'],
+    4: ['10', '11', '12']
+}
+
+
+def extract_data(data, when, included_space, price_per_gb):
+    try:
+        usage = round(data["results"][0]["series"][0]["values"][0][1] / 1024.0 / 1024.0, 2)
+    except (KeyError, IndexError):
+        usage = 0
+        print('Could not get storage usage for {}, defaulting to 0'.format(when))
+    billed_usage = max(usage - included_space, 0)
+    cost = round(billed_usage * price_per_gb, 2)
+    return (usage, billed_usage, cost)
+
+
+def get_data(when, who, influx_base):
+    query = influx_query.format(when, when, who)
+    with request.urlopen(influx_base + parse.urlencode([('q', query)])) as f:
+        data = json.loads(f.read())
+    return data
+
+
+def end_of(year, month):
+    first_week_day, last_day = monthrange(int(year), int(month))
+    return "{}-{}-{}".format(year, month, last_day)
+
+
+def get_quarter(year, quarter, who, influx_base, included_space, price_per_gb):
+    results = []
+
+    for month in quarters[quarter]:
+        when = "{}-{}".format(year, month)
+        data = get_data(end_of(year, month), who, influx_base)
+        usage, billable, cost = extract_data(data, when, included_space, price_per_gb)
+        results.append((when, usage, billable, cost))
+    return results
+
+
+def format_report(customer, reference, quarter, year, lines, included_space, price_per_gb):
+    report = [
+        "",
+        ";Adobe connect storage volume billing Q{}, {}, for {}".format(quarter, year, customer),
+        ";Reference: {}".format(reference),
+        "",
+        ";Month;Storage (GB);Service inc.;Billable storage;Price per GB (eur);Monthly cost (eur)",
+    ]
+    total_cost = 0
+    for line in lines:
+        report.append(";{};{};{};{};{};{}".format(line[0], line[1], int(-included_space), line[2], price_per_gb, line[3]))
+        total_cost += line[3]
+    report.append("")
+    report.append(";Quarterly storage cost;;;;;{}".format(total_cost))
+    return '\n'.join(report)
+
+
+def create_report(customer, year, quarter, config):
+    match = customer.lower()
+    if config.has_option(customer, 'match'):
+        match = config[customer]['match']
+    included_space = config.getfloat(customer, 'included_space')
+    price_per_gb = config.getfloat(customer, 'price_per_gb')
+    influx_base = config[customer]['influx']
+    reference = config[customer]['contract_reference']
+
+    lines = get_quarter(year, quarter, match, influx_base, included_space, price_per_gb)
+    return format_report(customer, reference, quarter, year, lines, included_space, price_per_gb)
+
+
+def send_email(customer, subject, body, report_name, report, config):
+    # Check config
+    missing_options = [x for x in ['to', 'server', 'port', 'from'] if x not in config.options(customer)]
+    if missing_options:
+        print('Missing the following options:', missing_options)
+        return
+
+    msg = EmailMessage()
+    msg['From'] = config[customer]['from']
+    msg['To'] = config[customer]['to']
+    msg['Subject'] = subject
+
+    if config.has_option(customer, 'cc'):
+        msg['Cc'] = config[customer]['cc']
+    if config.has_option(customer, 'bcc'):
+        msg['Bcc'] = config[customer]['bcc']
+
+    msg.set_content(body)
+    msg.make_mixed()
+    msg.add_attachment(report, filename=report_name)
+
+    with smtplib.SMTP(config[customer]['server'], config[customer]['port']) as server:
+        server.send_message(msg)
+
+
+def cli():
+    utcnow = datetime.utcnow()
+    year = utcnow.year
+    last_quarter = (utcnow.month - 1) // 3
+    if last_quarter is 0:
+        year -= 1
+        last_quarter = 1
+
+    parser = argparse.ArgumentParser()
+    parser.add_argument('-q', '--quarter', help='Specify quarter 1-4', type=int, default=last_quarter)
+    parser.add_argument('-y', '--year', help='Specify year', default=year)
+    parser.add_argument('-n', '--dry', help='Dont send email, but print output', action='store_true')
+
+    parser.add_argument('-c', '--customer', help='Who is the report about?')
+    parser.add_argument('--config', help='Configuration file', required=True)
+    return parser.parse_args()
+
+
+def main():
+    utcnow = datetime.utcnow()
+    args = cli()
+    config = configparser.ConfigParser()
+    config.read(args.config)
+
+    if args.customer:
+        if not config.has_section(args.customer):
+            raise Exception('No configuration found for {}'.format(args.customer))
+        customers = [args.customer]
+    else:
+        customers = [x for x in config.sections() if x not in ['mail', 'influx']]
+
+    for customer in customers:
+        reference = config[customer]['contract_reference']
+        serviceid = config[customer]['service_id']
+
+        subject = 'Storage cost for {} Q{} {}'.format(reference, args.quarter, args.year)
+        body = '''
+        This is an automated Adobe Connect Storage report for service ID {}
+
+        Adobe connect storage volume billing Q{}, {}, for {}.
+
+        This report was gennerated on {} UTC.
+        '''.format(serviceid, args.quarter, args.year, customer, utcnow.strftime('%Y-%m-%d %H:%M'))
+
+        report = create_report(customer, args.year, args.quarter, config)
+
+        if args.dry:
+            print('============')
+            print(subject, body, report)
+        else:
+            filename = 'Storage cost for {} Q{} {}.csv'.format(reference, args.quarter, args.year)
+            send_email(customer, subject, body, filename, report, config)
+            print('Sent Q{} {} report for {}'.format(args.quarter, args.year, customer))
+
+
+if __name__ == '__main__':
+    main()
diff --git a/config.conf.dist b/config.conf.dist
new file mode 100644
index 0000000..0332646
--- /dev/null
+++ b/config.conf.dist
@@ -0,0 +1,18 @@
+[DEFAULT]
+included_space=1000.00
+price_per_gb=0.40
+server=127.0.0.1
+port=1025
+to=test@example.com
+;cc=test1@example.com, test2@example.com
+;bcc=test3@example.com
+from=reports@example.com
+influx=http://127.0.0.1:8086/query?db=hoststats&
+
+[CUSTOMER1]
+service_id=SERVICE_ID1
+contract_reference=Magic reference
+
+[CUSTOMER2]
+service_id=SERVICE_ID2
+contract_reference=Nice AdobeConnect