UEFA1

#! /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 xml transformed to json from a database extract (sql)
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,arenaname 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){
            obj=JSON.parse(T);
            liste=obj.list;
            S=""+ liste.length+' lag\n\n';
            
            S+='<ul>';
            for(var ix=0;ix < liste.length;ix++){
                S+='<li class="lstNormal" onclick="hilite('+ix+')">'+liste[ix].name+'</li>';
            }
            S+='</ul>';
            $('#result').html(S);
            hilite(0);
            $('#header').html(liganame);
            
        }
        else
            $('#result').html(T);
        },
      error:function(data)
      {
            $('#dump').html(data);
      }
   });
}

//hilite selected team
function hilite(ix){
    //alert(""+ix);
    S='<img class="logo" src="'+liste[ix].logo+'" alt="'+liste[ix].logo+'" />';
                    S+='<div class="name">'+liste[ix].name+'</div>';
                    S+='<div class="aname">'+liste[ix].arenaname+'</div';;
    $('#hilited').html(S);
    var elt=document.getElementsByClassName('lstHilited');
    if(elt[0]){
        elt[0].className='lstNormal';
    }
    elt=document.querySelector('ul li:nth-child('+(ix+1)+')');
    if(elt){
        elt.className='lstHilited';
    }
}

// when we start, get selected serie 
//and make an initial selection    
function init(){
    goGetIt(document.forms["form1"].serie);
    hilite(0);    
}