#!/usr/bin/python
#===============================================================================
# Wrapper to the SamDB so it can be executed via a web form
#===============================================================================
import subprocess                                       #import subprocess
import cgi                                              #import cgi
import re                                               #import regex

print("Content-Type: text/plain \n")                    #required cgi header

formData = cgi.FieldStorage()                           #access the form data                           

user  = formData.getvalue('user') or ""                 #get HTML form field value
pswd  = formData.getvalue('pswd') or ""
db    = formData.getvalue('db')   or "data"             #'data' is the default database name
sql   = formData.getvalue('sql')  or ""

DBsql = 'db='+ db +' '+ sql                             #add db=dbname before the sql command

program = '/home/sultans/web/sql/demo/SamDB/SamDB' 
output  = subprocess.check_output([program, DBsql])     #call the SamDB.py program and pass it the SQL
output  = output.decode()                               #convert byte array to string

#==========================================================================================
# Convert the output from text format to json format
# the output will be a proper JSON format as in: 
#   [ 
#       {"colName1", "colName2", "colName3", "etc."},
#       {"colValu1", "colValu2", "colValu3", "etc."} 
#   ]
#==========================================================================================
def json_response(output):
        
    output = output.rstrip()                                #strip trailing \n
    lines = output.split('\n')
    if len(lines) == 1:
        if 'Error' in lines[0]: err_msg = 'Error'
        else:                   err_msg = 'Message'
        print('[ {"' +err_msg+ '":"' +lines[0]+ '"} ]')     #print/return   [ {"err_msg":"msg_text"} ]
        return       

    line1 = lines[0].rstrip()                               #line1 is the column headers
    colNames = line1.split("\t")

    jsonString = "[ \n"
    for i in range(1, len(lines)):                          #loop thru the lines (exclude line1 and last line)
        line = lines[i]
        if "Total number of rows" in line:
            continue                                        #skip it        
        jsonString += "    {"
        line = line.rstrip()
        cols = line.split("\t")
        for j in range(len(colNames)):                      #loop thru the columns
            colName  = colNames[j]
#           colName  = colName.replace(' ','_')             #if column alias has ' ' 
            colValue = cols[j]
            if colValue == None:  colValue = ''             #if no value, Python puts None, change to ''
            colValue = str(colValue)
            jsonString +=  '"'+ colName +'":"'+ colValue +'", '

        jsonString = re.sub(r", $", " ", jsonString)        #strip off the last comma for each object
        jsonString += "}, \n";

    jsonString = re.sub(r", \n$", " \n", jsonString)        #strip off the last comma for the array 

    jsonString += "]";
    print(jsonString)
#==========================================================================================

json_response(output)