UEFA4

#! /usr/bin/python
import cgi,sys
from lxml import etree
import dbutils
import cgitb; cgitb.enable()
"""
select from database to json
using mysql in a subprocess as implemented in utils
"""
# transform xml -> json
XSLT_FILE='tojson.xsl'       
form=cgi.FieldStorage()
"""
 Need connect. | separated string
 Sequence is important:
 user|password|host|database|table
 sql
"""
print 'Content-type: text/plain; charset=utf-8 \n'
try:
    # always connect
    connect=form['connect'].value
    sql=form['sql'].value
    # set up parameters
    p=dbutils.prepareSelectParameters(connect,sql)
    if len(p) > 0:                           
        print dbutils.makeJson(p,'data/select.xml',XSLT_FILE)
    else:
        print 'error bad parameter list in connect'
except:
        res=sys.exc_info()
        print 'error '+str(res[1])

#! /usr/bin/python
import cgi,sys
import subprocess
from lxml import etree
"""
Run a subprocess, used to perform:
extract from database with mysqldump   or    mysql
return ok or error....
"""
def doProcess(params,targetfile):
    try:
        f=open(targetfile,'wb')
        p=subprocess.check_call(params,stdout=f,stderr=f,shell=False)
        f.flush()
        f.close()
        if p==0:            
            return 'ok'
        else:
            return 'error in subprocess: '+str(p)
    except:
        res=sys.exc_info()
        return 'error '+ str(res[1])
 
"""
 extract from database and
 make an xslt transform to legal JSON
 return the JSON string or error....
"""
def makeJson(params,xmlfile,xslfile):
    res=doProcess(params,xmlfile)
    if res.startswith('error'):
        return res
    try:
        tree=etree.parse(xmlfile)
        xsltree=etree.parse(xslfile)
        transform=etree.XSLT(xsltree)    
        resulttree=transform(tree)
        return str(resulttree)
    except:
        res=sys.exc_info()
        return 'error '+ str(res[1])
 
"""
 extract from database,
 make an xslt transform to legal JSON
 and save the string as a compact Javascript string variable
  var obj='....'
"""
def saveJSVariable(params,xslfile,targetfile):
    S=makeJson(params,"data/tmp",xslfile)
    if S.startswith('error'):
        S='{"message":"S"}'
    else:
        S=S.replace('\r','').replace('\n','')
    T="var obj='"+S+"'"
    res=storeTextFile(targetfile,T)
    return res[0]
 
"""
 Set up parameters for a use of mysqldump
 user|password|host|database|table
"""
def prepareDumpParameters(connect):
    p=[]
    parts=connect.split('|')
    if len(parts) == 5:                           
        p.append('mysqldump')
        p.append('--xml')
        p.append('--lock-tables=false')#when limited user rights        
        p.append('--user='+parts[0])
        p.append('--password='+parts[1])
        p.append('--default-character-set=utf8')
        p.append('--host='+parts[2])
        p.append(parts[3]) #database
        p.append(parts[4]) #table
    return p
"""
 Set up parameters for a use of mysql
 user|password|host|database
"""
def prepareSelectParameters(connect,sql):
    p=[]
    parts=connect.split('|')
    if len(parts) == 4:                           
        p.append('mysql')
        p.append('-X')
        p.append('--execute='+sql)
        p.append('--user='+parts[0])
        p.append('--password='+parts[1])
        p.append('--default-character-set=utf8')
        p.append('--host='+parts[2])
        p.append(parts[3]) #database
    return p
 
"""
Read / write text files
"""
def getTextFile(filename):
    try:
        file=open(filename,'r')
        res=file.read()
        file.close()
        return ('ok',res)
    except:
        res=sys.exc_info()
        return('error',res[1])
    
    
def storeTextFile(filename,txt):
    try:
        file=open(filename,'w')
        file.write(txt)
        file.close()
        return('ok','')
    except:
        res=sys.exc_info()
        return('error',res[1])

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="utf-8"/>
<!--
Producing strait json
either from a mysqldump of one table to xml
or from mysql extract to xmlfile  with sql
Limitation:
Replace " with /" in values,
as from:
http://stackoverflow.com/questions/9370633/xslt-replacing-double-quotes-with-escape-sequence
no other cleaning or escaping
alternativ stylesheet at:
http://code.google.com/p/xml2json-xslt/
-->
<xsl:template match="/">
{"list":[<xsl:apply-templates select="//row"/>]}
</xsl:template>
<xsl:template match="row">
{<xsl:apply-templates select="field"/>
}
<xsl:if test="position() &lt; last()">,</xsl:if>
</xsl:template>
<xsl:template match="//row/field">
"<xsl:value-of select="@name"/>":"<xsl:call-template name="escapeQuote"/>"<xsl:if test="position() &lt; last()">,</xsl:if>
</xsl:template>
<!-- replace quotes-->
<xsl:template name="escapeQuote">
  <xsl:param name="pText" select="."/>
  <xsl:if test="string-length($pText) >0">
   <xsl:value-of select="substring-before(concat($pText, '&quot;'), '&quot;')"/>
   <xsl:if test="contains($pText, '&quot;')">
    <xsl:text>\"</xsl:text>
    <xsl:call-template name="escapeQuote">
      <xsl:with-param name="pText" select="substring-after($pText, '&quot;')"/>
    </xsl:call-template>
   </xsl:if>
  </xsl:if>
</xsl:template>
</xsl:stylesheet>

// load a serie from database and produce table
//... and call sortAndShowHistogram as well
// serie is in the form: NO1, for Norway 1.level
function goGetIt(serie){
    var index=serie.selectedIndex; 
    var value=serie.value;//DE1
    var nation=value.substring(0,2);
    var level=value.substring(2);
    var liganame=document.querySelector('select option:nth-child('+(index+1)+')').innerHTML;
    var sql="select name,logo,lng,lat from clubs where level='"+level+"' and nationid='"+nation+"' order by name;";
    $.ajax({
        // the python job, extract and transform
        url:"pykode/select2Json.py",
        data:"connect=student|student| itstud.hiof.no|bsdiverse&sql="+sql,
        async:false,
        success:function(data, textStatus, jqXHR)
        {
            T=jqXHR.responseText;
            // while debugging:console.log(T);
            if(T.indexOf('error')==0){
            $('#result').html(T);
                return;
            }
            // its ok
            obj=JSON.parse(T);
            liste=obj.list;
            S='<table>';              
            // top row, th
            S+='<tr><th></th>';
            for(var ix2=0;ix2 < liste.length;ix2++){
                S+='<th>'+liste[ix2].name.substring(0,2)+'</th>';
            }
            S+='<th>Sum</th><tr>';
          
            // all other rows
            var totalSum=0.0;
            var maxSum=0.0;
            var minTeam=0;
            var maxTeam=0;
            var minSum=100000000.0;
            for(var ix1=0;ix1 < liste.length;ix1++)
            {
                S+='<tr>';
                S+='<td>'+liste[ix1].name+'</td>'
                var teamSum=0.0;
                for(var ix2=0;ix2 < liste.length;ix2++){
                    if(ix1==ix2){
                        // self, no distance
                        S+='<td>  </td>';
                    }
                    else{
                        // calculated distance goes here
                        var dist=getDistanceFromLatLonInKm(parseFloat(liste[ix1].lat),
                                                           parseFloat(liste[ix1].lng),
                                                           parseFloat(liste[ix2].lat),
                                                           parseFloat(liste[ix2].lng));
                        teamSum+=dist;
                        // reduse to mil and remove decimals
                        S+='<td>'+Math.floor(dist/10)+'</td>';
                    }
                }
                // add the property teamSum to a teamobject, in mil
                liste[ix1].sum=Math.floor(teamSum/10);
                if(teamSum > maxSum)
                {
                    maxSum=teamSum;
                    maxTeam=ix1;
                }
                if(teamSum < minSum)
                {
                    minSum=teamSum;
                    minTeam=ix1;
                }
                S+='<td>'+Math.floor(teamSum/10)+'</td>';
                S+='</tr>';
                totalSum+=teamSum;
          }
          
          // prepare table output              
          S+='</table>';              
          S+='<p>Totalt reiste mil i sesongen: '+Math.floor(totalSum/10)+
             '. Gjennomsnitt: '+Math.floor(totalSum/liste.length/10)+'</p>';
          S+='<p>Maks/min: '+liste[maxTeam].name+' : '+Math.floor(maxSum/10)+' / ';
          S+=liste[minTeam].name+' : '+Math.floor(minSum/10)+'</p>';
          // set the content of the Tabell tab
          $('#tabresult').html(S);
          
          // then we do the histogram
          sortAndShowHistogram(liste);
        },
      error:function(data)
      {
            $('#dump').html(data);
      }
   });
}
// handle the nhistogram from the (updated with sum) list
function sortAndShowHistogram(liste){
    // do a simple bubblesort, ascending
    var done=false;
    while (!done){
        done=true;
        for(var ix=0;ix < liste.length-1;ix++){
            if(liste[ix].sum > liste[ix+1].sum){
                done=false;
                tmp=liste[ix];
                liste[ix]=liste[ix+1];
                liste[ix+1]=tmp;
            }
        }
    }
    maxValue=liste[liste.length-1].sum;
    // show
    var S="<table>";        
    // adjust to window width
    var factor=(window.innerWidth-210)/maxValue;
    for(var ix=0;ix < liste.length;ix++){
            S+="<tr>";
            S+="<td>"+liste[ix].name+"</td>";
            // text align left or right ?
            S+='<td><div '
            S+='style="text-align:left;width:'+liste[ix].sum*factor+'px">'+liste[ix].sum
            S+='</div></td>';
            S+="</tr>";
    }
    S+="</table>";
    
    // set the content of the Histogram tab
    document.getElementById("histresult").innerHTML=S;
}

// when we start, get selected serie 
goGetIt(document.forms["form1"].serie);

// calculate the distance between two points in km
function getDistanceFromLatLonInKm(lat1,lon1,lat2,lon2) {
  var R = 6371; // Radius of the earth in km
  var dLat = deg2rad(lat2-lat1); 
  var dLon = deg2rad(lon2-lon1); 
  var a = 
    Math.sin(dLat/2) * Math.sin(dLat/2) +
    Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2)) * 
    Math.sin(dLon/2) * Math.sin(dLon/2); 
  var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
  return R * c; // Distance in km
}
function deg2rad(deg) {
  return deg * (Math.PI/180)
}