#!/usr/bin/env python3
#=====================================================================================
# Get data from a database multiple rows (2 columns each: a string, and a number) 
# Perform analytics on each row
#=====================================================================================
import sys                                          #sys module to accept arguments
sys.path.insert(0,'/home/sultans/web/python/demo/oo/zAnalytics')
from DBio import DBio                               #import the DBio class
from Analytics import Analytics                     #import the Analytics class

print("Content-Type: text/html \n")                  #http header with newline char (required for web) 

rawData    = []                                     #2 dim array to hold result of the query
dataPoints = []                                     #2 dim array to hold all the data points

sql = "SELECT concat(lname,fname) name, grade \
       FROM   student natural join grade \
       ORDER BY 1" 

if (len(sys.argv) > 1):                             #if argument is provided (remember argv[0] is this pgm name) 
    sql = sys.argv[1];                              #use it as SQL code

#=====================================================================================
# readDB: Read data from a database
#=====================================================================================
def readDB(sql):

    obj = DBio('demodw','demodw','demodw','mysql')     #create a DBio object for mysql
#   obj = DBio('demodw','demodw','orcl','oracle')      #create a DBio object for oracle
    
    rawData = obj.execSQL(sql)                         #call the execSQL method                              

    return rawData    

#=====================================================================================
# display: Display the raw data retrieved from database
#=====================================================================================
def display(rawData):

    print("\nRAW DATA...")
     
    for array in rawData:                                    #loop thru the data rows
        for idx in range(len(array)):                        #loop thru each row
            col = array[idx]                                 #get the column
            if idx==0: print("{:15}".format(col),end='')     #if the first column  
            else:      print("{:>8.0f}".format(col),end='')  #if the other columns
        print()                                              #print(newline

#=====================================================================================
# flatten: Flatten out all rows belonging to the same subject on the same line
#=====================================================================================
def flatten(rawData):

    data = []                               #create temp array
    prev_name = ''                       
    for row in rawData:                     #loop thru all row
        (name, num) = row                   #each row is name , num
        if name != prev_name:               
            if prev_name:                   #if not the first iteration, eliminates first write
                dataPoints.append(data)     #append to data Points array
            data = []                       #delete all previous elements           
            data.append(name)               #append ther nam
            prev_name = name                          

        data.append(num)                    #appernd the num
     
    dataPoints.append(data)                 #append the last row

    return(dataPoints)

#=====================================================================================
# compute: Compute and print
#=====================================================================================
def compute(dataPoints):

    print("\nCOMPUTATION...")

    #Perform row oriented analytics ------------------ 

    for row in dataPoints:                              #loop thru the data rows
        name   = row[0]                                 #name is element 1
        values = row[1:]                                #numerics are the rest of elements
        print("{:15}".format(name),end='')
        print(values)

        array  = Analytics.normalize(row);              #normalize array - clean up bad/no data  

        obj    = Analytics(array)                       #Create an Analytics object

        rowCount  = obj.getCount()                      #get count of elements  
        rowSum    = obj.getSum()                        #get sum of elements
        rowAvg    = obj.getAvg()                        #get the average
        rowMedian = obj.getMedian()                     #get the median
        rowMode   = obj.getMode()                       #get the mode(s)
        rowMin    = obj.getMin()                        #get the minimum
        rowMax    = obj.getMax()                        #get the maximum
        rowRange  = obj.getRange()                      #get the range
        rowStdDev = obj.getStdDev()                     #get the standard deviation

        print("\t\t Count="   , rowCount, end='')       #print row count
        print("\tSum=%.2f"    % rowSum,   end='')       #print row sum   
        print("\tAvg=%.2f"    % rowAvg,   end='')       #print row average   
        print("\tMedian=%.2f" % rowMedian,end='')       #print row median   
        print("\tMode=%s"     % rowMode,  end='')       #print row mode(s)   
        print("\tMin=%.2f"    % rowMin,   end='')       #print min value   
        print("\tMax=%.2f"    % rowMax,   end='')       #print max value  
        print("\tRange=%.2f"  % rowRange, end='')       #print the range  
        print("\tstdDev=%.2f" % rowStdDev)              #print standard deviation
        print()


#=====================================================================================
# main code
#=====================================================================================

rawData = readDB(sql)                                  #read from database
display(rawData)                                       #display raw data
dataPoints = flatten(rawData)                          #flatten data across same name
compute(dataPoints)                                    #compute analytics




#=== 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
#=================================================================================