#!/usr/bin/python

import sys
import getopt
from pprint import pprint
from spacewalk.common.rhnConfig import PRODUCT_NAME

try:
    from spacewalk.server import rhnSQL
except:
    print "Couldn't load needed libs, Are you sure you are running this on a satellite?"
    sys.exit(1)


rhnSQL.initDB()


def main():
    printAcrossSatelliteUsage()
    list = listOfOrgs()
    for org in list:
        printOrgHeader(org)
        printSystemEnts(org['id'])
        printChannelEnts(org['id'])
    printOrgFooter()


def printAcrossSatelliteUsage():
    # This prints summary displayed on
    # https://satellite.fqdn/rhn/admin/multiorg/SystemEntitlements.do and
    # https://satellite.fqdn/rhn/admin/multiorg/SoftwareEntitlements.do which
    # is Channel and Entitlement usage across all organizations

    print ("Software Channel Entitlements Across " + PRODUCT_NAME + " :")
    query = "SELECT cfo.label,SUM(cfo.max_members) as total,SUM(cfo.current_members) as used,(SELECT (cfo2.max_members) - (cfo2.current_members) FROM rhnChannelFamilyOverview cfo2 WHERE cfo2.label = cfo.label AND cfo2.org_id = 1) as available,SUM(cfo.fve_max_members) as total_flex,SUM(cfo.fve_current_members) as used_flex,(SELECT (cfo2.fve_max_members) - (cfo2.fve_current_members) FROM rhnChannelFamilyOverview cfo2 WHERE cfo2.label = cfo.label AND cfo2.org_id = 1) as available_flex FROM rhnChannelFamilyOverview cfo WHERE cfo.max_members is not null and (cfo.current_members !=0 or cfo.fve_current_members!=0) GROUP BY cfo.label, cfo.name HAVING (SUM(cfo.max_members) is not null) ORDER by cfo.name DESC"
    list = run_query(query)
    if list == None:
        print("")
        return

    print("%35s %s %s %s %s %s %s" %
          ("Channel Label", "   Total  ", "Regular Used", "Available", "Total Flex", "Flex Used", "Available Flex"))
    print("%35s %s %s %s %s %s %s" %
          ("-------------", "----------", "------------", "---------", "----------", "---------", "--------------"))

    for item in list:
        print("%35s %8s %10s %12s %8s %10s %10s" % (item['label'], str(item['total']), str(item['used']), str(
            item['available']), str(item['total_flex']), str(item['used_flex']), str(item['available_flex'])))

    print ("\nSystem Entitlements Across " + PRODUCT_NAME + " :")
    query = "SELECT  sgt.label as label,SUM(sg.max_members) as total,SUM(sg.current_members) as used,(SELECT (sg2.max_members) - (sg2.current_members) FROM rhnServerGroup sg2 inner join  rhnServerGroupType sgt2 on sg2.group_type = sgt2.id WHERE sg2.org_ID = 1 AND sgt2.label = sgt.label) as available FROM rhnServerGroup sg inner join rhnServerGroupType sgt on sgt.id = sg.group_type WHERE    sgt.label != 'sw_mgr_entitled' GROUP BY sgt.label, sgt.id ORDER BY sgt.label"
    print("%35s %s %s %s" % ("System Entitlement", "  Total  ", "  Used  ", " Available "))
    print("%35s %s %s %s" % ("------------------", "  -----  ", "  ----  ", " --------- "))
    list = run_query(query)
    for item in list:
        print("%35s %7s %8s %10s" % (item['label'], str(item['total']), str(item['used']), str(item['available'])))


def printOrgHeader(org):
    print("\n")
    print("=" * 40)
    print(org['name'] + " (" + str(org['id']) + "):\n")


def printOrgFooter():
    print("=" * 40)
    print("\n")


def printChannelEnts(id):
    query = "SELECT CFO.label, CFO.current_members, CFO.max_members,CFO.fve_current_members as current_flex, CFO.fve_max_members as max_flex,(select count(distinct sc.server_id) from rhnServerChannel sc inner join rhnChannelFamilyMembers CFM on CFM.channel_id = sc.channel_id where CFM.channel_family_id = CFO.id) as subscribe_count FROM rhnChannelFamilyOverview CFO  WHERE CFO.org_id = %d  ORDER BY CFO.name ASC" % (
        id)
    list = run_query(query)
    print("%35s %s %s %s %s %s" %
          ("Channel Entitlement", "Regular Used", "Max Members", "Flex Used", "Flex Max Members", "Subscribed Count"))
    print("%35s %s %s %s %s %s" %
          ("-------------------", "------------", "-----------", "---------", "----------------", "----------------"))
    if list == None:
        print("")
        return
    for item in list:
        if item['subscribe_count'] != 0:
            print("%35s %8s %10s %12s %10s %16s" % (item['label'], str(item['current_members']), str(
                item['max_members']), str(item['current_flex']), str(item['max_flex']), str(item['subscribe_count'])))
    print("")
    return


def printSystemEnts(id):
    print("%35s %s %s" % ("System Entitlement", "Used", "Max Members"))
    print("%35s %s %s" % ("------------------", "----", "-----------"))
    query = """select T.label, G.current_members,G.max_members
                 from rhnServerGroup G inner join rhnServerGroupType T  on G.group_type = T.id
                 where org_id = %d order by T.label """ % (id)
    list = run_query(query)
    for item in list:
        print("%35s %3s %8s" % (item['label'], str(item['current_members']), str(item['max_members'])))
    print("")
    return


def listOfOrgs():
    query = "select id, name from web_customer"
    return run_query(query)


def run_query(query):
    _get_data_sql = rhnSQL.prepare(query)
    _get_data_sql.execute()
    return _get_data_sql.fetchall_dict()


if __name__ == "__main__":
    main()
