#!/usr/bin/python3
############################################################################################
# Perform analytics on....
# Part1: a single dim list, if list is provided as arguments, take it, else hardcoded list 
# Part2: Read database table (analytics) into a 2 dimensional list
#        Perform analytics on the data (row oriented analytics)
# Part3: Perform analytics on the data (column oriented analytics)
############################################################################################
import sys
import pymysql as mydb                                      #Mysql 3x database driver
import statistics

print("Content-Type: text/html \n")                         #required http response header
print('<br>')

print("READ DATA FROM A DATABASE, AND PERFROM ANALYTICS <br>")
print('<br>')

### FUNCTION TO READ A DATABASE INTO A 2DIM LIST #########
def readDB():      
    try:    
        conn   = mydb.connect(host='localhost',db='demo2',user='demo2',password='demo2')  #connect to db    
        cursor = conn.cursor()                                  #create a cursor     
        sql    = "SELECT * from analytics"                      #query      
        result = cursor.execute(sql);                           #execute the query     
    except mydb.Error as e:
        error = 'Database Error - ' + e
        return  

    list2 = cursor.fetchall()                			#get all the rows at once 
    return(list2)

### FUNCTION TO PERFORM ANALYTICS #################
def analytics(list):
    analytics = {}                                      #create an empty dictionary
    analytics['count']    = len(list)
    analytics['sum']      = sum(list)
    analytics['avg']      = statistics.mean(list)
    analytics['min']      = min(list)
    analytics['max']      = max(list)
    analytics['median']   = statistics.median(list)
    try:
        analytics['mode'] = statistics.mode(list)       #single mode
    except:
        analytics['mode'] = float('-inf')               #multiple mode. Set to -infinity
    analytics['stdDev']   = statistics.stdev(list)
    return(analytics)

### FUNCTION TO SLICE A 2DIM LIST ################
def slice(list2, idx):                                  #column slice of a 2 dimensional list
    slice = []                                          #create an empty list
    for row in list2:
        slice.append(row[idx])                          #add element to the list
    return(slice)

##################################################

print("Part I ================================================================== <br>")
print("SINGLE DIMENSION LIST - ANALYTICS... <br>")

list1 = [5.0,6.5,-44,0,88,102,216,131,-55,67,0.6,0,13,99,150,67,0,32.3]

if (len(sys.argv) > 1):                                 #if arguments are entered
    list1 = []                                          #create an empty list                   
    for elem in sys.argv:
        try: 
            elem = float(elem)                          #convert string to float
            list1.append(elem)                          #append to list
        except:
            pass                                        #element is not numeric, do nothing
            
srtList = sorted(list1)
dict1   = analytics(srtList)                            #call the analytics function above
print(srtList, '<br>')
print(dict1,   '<br>')
print('<br>')


print("\nPart II ================================================================= <br>")
print("READ CSV FILE INTO 2 DIMENSIONAL LIST <br>")
print("2 DIMENSIONAL LIST - ROW ORIENTED ANALYTICS... <br>")

list2 = readDB()                                            #read a database

def prt(dictionary):                                        #optional better print
    for key in dictionary:                                  #loop thru the keys
        print('%s=%.2f ' % (key,dictionary[key]), end='')   #print formatted key=value

for row in list2:                                           #loop thru the numeric 2 dimensional list                                           
    dict1 = analytics(row)                                  #perform analytics on each row
    print(row, '--> ', end='')                              #print the row
    prt(dict1)                                              #print the resulting analytics dictionary 
    print('<br>')
print('<br>')


print("\nPart III ================================================================ <br>")
print("SAME CSV FILE - COLUMN ORIENTED ANALYTICS... <br>")
    
col_width = len(list2[0])               #length of the first row

for idx in range(col_width):            #loop thru number of columns
    col = slice(list2, idx)             #slice the 2dim list by column index
    dict1 = analytics(col)              #perform analytics on each column
    print(col, '\t --> ', end='')       #print the column
    prt(dict1)                          #print the resulting analytics dictionary
    print('<br>')
print('<br>')
print()    
   
    
    

#=== link to see the python code =================================================
import os, sys
sys.path.insert(0,'/home/s/sultans/web/python/demo')
import zCode                          #import func to display the Python code
filename = os.path.abspath(__file__)  #get absolute file name 
zCode.display(filename)               #call it
#=================================================================================