#!/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 mysql.connector as mydb                     #Python 2x database driver
import pymysql as mydb                              #Python 3x database driver
import sys                                          #sys module to accept arguments
sys.path.insert(0,'/home/s/sultans/web/python/demo/analytics')
import Analytics as Ana                             #import the Analytics module

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):
     
    try:    
        conn  = mydb.connect(host='localhost',db='demodw',user='demodw',password='demodw')     #connect to db
     
        cursor = conn.cursor()                      #create a cursor
        result = cursor.execute(sql)                #execute the query
     
        row = cursor.fetchone()                     #get row from cursor
        while row is not None:                      #loop until no more rows
            rawData.append(row)                     #append row to 2dim array
            row = cursor.fetchone()                 #get next row from cursor
 
    except mydb.Error as e:
        errorNum = e.args[0]
        errorMsg = e.args[1]
        error = 'Database Error - ' + str(errorNum) + errorMsg
        print(error)

    cursor.close()                          #close the cursor/buffer
    conn.close()                            #close the connection

    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     = Ana.normalize(row)                 #normalize array - clean up bad/no data  
        rowCount  = Ana.count(array)                   #count elements  
        rowSum    = sum(array)                         #sum elements
        rowAvg    = Ana.avg(array)                     #average
        rowMedian = Ana.median(array)                  #compute median
        rowMode   = Ana.mode(array)                    #compute mode(s)
        rowMin    = min(array)                         #determine min
        rowMax    = max(array)                         #determine max
        rowRange  = Ana.range(array)                   #compute the range
        rowStdDev = Ana.stdDev(array)                  #compute standard deviation

        print(" | 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 


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