UEFA2

#! /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>
<!-- as from 
http://stackoverflow.com/questions/9370633/xslt-replacing-double-quotes-with-escape-sequence
-->
<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>

    // images
    var logoPath="http://www.it.hiof.no/~borres/allround/fotballogos/";
    // the XSL-dom we will use to make json
    var TransDOM;
    
    // fast load of xsldom
    function getXSLDOM(address){
        xhttp=new XMLHttpRequest();
        xhttp.open("GET",address,false);
        xhttp.send("");
        // report load
        elt=document.getElementById("dump");
        elt.innerHTML=elt.innerHTML+"\n"+address+" : "+xhttp.statusText;
        if(xhttp.status!=200)
            return null;
        return xhttp.responseXML;    
    }
    
    // make json-string from an XMLDom with a transfomation (transDOM)
    function getJsonStringFrom(XMLDom){
        if (window.ActiveXObject){// code for IE
                T=XML_DOM.transformNode(transDOM);
                return T;
                //document.getElementById(eltID).innerHTML=ex;
        }
        // Mozilla, Firefox, Opera, etc.
        else if (document.implementation && document.implementation.createDocument){
            xsltProcessor=new XSLTProcessor();
            try{      
                xsltProcessor.importStylesheet(transDOM);
                resultDocument = xsltProcessor.transformToFragment(XML_DOM,document);
                ser=new XMLSerializer();
                // ok since we have "rooted"-doc
                T=ser.serializeToString(resultDocument);
                return T;
              }
              catch(e){
                    document.getElementById("dump").innerHTML=e;
                    return e;
              }
        }    
    }
    // load xml from a database extract (sql)
    // transform it to json and disoplay the result
    function goGetIt(serie){
        var index=serie.selectedIndex;
        var value=serie.value;
        var liganame=document.querySelector('select option:nth-child('+(index+1)+')').innerHTML;
        //alert(""+index+" : "+value+" : "+liganame);
        var sql="select klubber.Name,klubber.Logo,arenaer.Name as 'ArenaName',arenaer.Wiki as 'ArenaWiki' from klubber "+
             "inner join arenaer on klubber.StadionID=arenaer.ID "+
             "where SerieID='"+value+"' order by klubber.Name;";
        var T=null;
      $.ajax({
            // the python job
            url:"pykode/select2XML.py",
            data:"connect=student|student|frigg.hiof.no|bsdiverse&sql="+sql,
            async:false,
            success:function(data, textStatus, jqXHR)
            {
              temp=jqXHR.responseText;
              // while debugging:$('#dump1').html(temp);
              if(temp.indexOf('error')!=0){
                XML_DOM=jqXHR.responseXML;
                
                T=getJsonStringFrom(XML_DOM);
                
                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 a selected team
    function hilite(ix){
        //alert(""+ix);
        S='<img class="logo" src="'+logoPath+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, load xslDOM, get selected serie 
    //and make an initial selection
    function init(){
        transDOM=getXSLDOM('pykode/tojson.xsl');
        goGetIt(document.forms["form1"].serie);
        hilite(0);
 }