Groovy Script as a Datasource for JasperReports
http://community.jaspersoft.com/questions/810425/python-script-datasource ?
#!/bin/env groovy
import groovy.sql.Sql
//jdbcURL= "jdbc:mysql://airs.cy9ejloydt1i.us-east-1.rds.amazonaws.com:3306/airs"
jdbcURL= "jdbc:mysql://advanta.sytes.net:3306/airs"
def mysqlconnection() {
Sql.newInstance("${jdbcURL}?useUnicode=true&characterEncoding=UTF-8",
'userairs', 'userairs', 'com.mysql.jdbc.Driver')
}
def printST(e){
Writer writer = new StringWriter()
e.printStackTrace(new PrintWriter(writer))
writer.toString()
}
try{sql=mysqlconnection()} catch(Exception ex) {
println "<ROWSET><![CDATA[${printST(ex)}]]></ROWSET>"
return -999
}
def REQUEST = "pType=m&pServ=2&pLevel=h2&pStamp=201104061100"
if (this.args) REQUEST = this.args[0]
def map = [:]
REQUEST.tokenize('&').each{
def xx=it.tokenize('=')
map[xx[0]]= xx[1] != null ? URLDecoder.decode(xx[1]) : null
}
def pType= map['pType'] ?: '?'
def pServ= map['pServ'] ?: '?'
def pLevel= map['pLevel'] ?: '?'
def pStamp="${map['pStamp'] ?: '?'}????????????"[0..11]
def a_val= 'a_val'; def a_stat= 'a_stat'
if(pLevel=='h0') {a_val= 'value'; a_stat= 'vf'}
if(pType=='d') a_val= 'a_ext'
String query= """
SELECT s.code, s.name, s.unit, h.$a_val a_val
FROM svod_code s
LEFT OUTER JOIN $pLevel h ON (s.code=h.code)
WHERE
h.${a_stat}='1' AND
s.type='$pType' AND s.nserv=$pServ AND h.tstamp='$pStamp'
ORDER BY 1"""
println "<ROWSET autor='is09-20'>"
println """<!--
pType= $pType
pServ= $pServ
pStamp= $pStamp
pLevel= $pLevel
$query
-->"""
def pLevelName= "Исторический уровень: " +
(pLevel=="h0" ? "1 минута" :
pLevel=="h1" ? "10 минут" :
pLevel=="h2" ? "1 час" :
pLevel=="h3" ? "1 смена" :
pLevel=="h4" ? "1 сутки" :
pLevel=="h5" ? "1 месяц" : "НЕИЗВЕСТНО")
def pTypeName= "Тип значений: " +
(pType=="a" ? "аналоговые сигналы" :
pType=="m" ? "расчетные значения" :
pType=="d" ? "дискретные сигналы" : "НЕИЗВЕСТНО")
def pServName= "Группа оборудования: " +
(pServ=="0" ? "общестанционное" :
pServ=="1" ? "энергоблок № 1" :
pServ=="2" ? "энергоблок № 2" :
pServ=="3" ? "энергоблок № 3" : "НЕИЗВЕСТНО")
def pStampName= pStamp[0..3]+"-"+pStamp[4,5]+"-"+pStamp[6,7]+" "+pStamp[8,9]+":"+pStamp[10,11]
println """<headers>
<pLevelName><![CDATA[$pLevelName]]></pLevelName>
<pTypeName><![CDATA[$pTypeName]]></pTypeName>
<pServName><![CDATA[$pServName]]></pServName>
<pStampName><![CDATA[$pStampName]]></pStampName>
</headers>"""
if ("$pType$pServ$pLevel$pStamp".contains('?')){
println """\
<!--
Требуется корректный набор параметров. Пример:
?pType=m&pServ=2&pLevel=h2&pStamp=201104061100
-->
"""
} else
try {
def rr=0
sql.rows(query).each{row->
println "<ROW>"
row.each {println "<$it.key><![CDATA[$it.value]]></$it.key>"}
println "</ROW>"
rr=rr+1
}
if(rr==0) println('<ROW />')
} catch(Exception ex) {
println "<error><![CDATA[${ex.getMessage()}]]></error>"
}
println "</ROWSET>"
////////
thirteen.php in Action
генерация XML-контента (посредством xmlServer.groovy)
генерация отчёта на основе удаленного XML-контента
генерация отчёта на основе локального XML-контента
<?php
$pLev= "1";
$pNumber= "0010";
$pH= "h2";
$pStart= "201104050000";
$pStop= "201104052300";
if (isset($_REQUEST['pLev'])) $pLev= $_REQUEST['pLev'];
if (isset($_REQUEST['pNumber'])) $pNumber= $_REQUEST['pNumber'];
if (isset($_REQUEST['pH'])) $pH= $_REQUEST['pH'];
if (isset($_REQUEST['pStart'])) $pStart= $_REQUEST['pStart'];
if (isset($_REQUEST['pStop'])) $pStop= $_REQUEST['pStop'];
/* **************************************************************** */
$res= "<ROWSET pLev='$pLev' pNumber='$pNumber' pH='$pH' pStart='$pStart' pStop='$pStop'>\n";
$sql= "SELECT level,number,name,descr,content1 FROM reports1 WHERE lev=$pLev AND number='$pNumber' ORDER BY 2;";
$res .= "<!-- $sql -->\n";
$link = mysql_connect('localhost', 'userairs', 'userairs');
mysql_select_db('airs');
mysql_set_charset("UTF8");
$row= mysql_fetch_row(mysql_query($sql));
if (!$row){
$res .= "<content1></content1>\n</ROWSET>\n";
echo $res;
return;
}
$res .= "<content1 level='" .$row[0]. "' number='" .$row[1]. "' name='" .$row[2].
"' descr='" .$row[3]. "' ><![CDATA[" .$row[4]. "]]>\n";
$cont1= explode(";",$row[4]);
$codeList= "";
$codeArr= array();
for($x0=0;$x0<13;$x0++){
$x1= $x0*11;
$content1[$x0]= array("ord"=>$cont1[$x1],
"code"=>$cont1[$x1+1], "name"=>$cont1[$x1+2], "unit"=>$cont1[$x1+3], "scale"=>$cont1[$x1+7] );
if ($codeList!="") $codeList .= ", ";
$codeList .= "'" .$content1[$x0]["code"]. "'";
$codeArr[$x0]= $content1[$x0]["code"];
$res .= "<signal$x0 ord='" . $content1[$x0]["ord"] .
"' code='" . $content1[$x0]["code"] .
"' nserv='" . $cont1[$x1+4] .
"' decp='" . $cont1[$x1+6] .
"' value='" . $cont1[$x1+8] .
"' total='" . $cont1[$x1+9] .
"' type='" . $cont1[$x1+10] .
"' >\n";
$res .= "<name><![CDATA[" . $content1[$x0]["name"] . "]]></name>\n";
$res .= "<unit><![CDATA[" . $content1[$x0]["unit"] . "]]></unit>\n";
$res .= "<scale><![CDATA[" . $content1[$x0]["scale"] . "]]></scale>\n";
$res .= "</signal$x0>\n";
};
$res .= "</content1>\n";
$stmt= <<<EOD
SELECT tstamp,code,a_val,a_stat,type,a_ext FROM $pH
WHERE tstamp BETWEEN '$pStart' and '$pStop' AND
code in ($codeList);
EOD;
$res .= "<!-- $stmt -->\n";
$dInt= array(
'h0' => new DateInterval('PT1M'),
'h1' => new DateInterval('PT10M'),
'h2' => new DateInterval('PT1H'),
'h3' => new DateInterval('PT12H'),
'h4' => new DateInterval('PT24H'),
'h5' => new DateInterval('P1M')
);
$dt= $dInt[$pH];
@$t1=date_create_from_format('YmdHi',$pStart);
@$t2=date_create_from_format('YmdHi',$pStop);
$result= array();
$empty= array("","-","-","-","-","-","-","-","-","-","-","-","-","-");
$scale= "<!-- ";
while($t1<=$t2){
$t1s= $t1->format('YmdHi');
$scale .= "$t1s\n";
$result[$t1s]= $empty;
switch ($pH) {
case "h2":
$result[$t1s][0]= substr($t1s,6,2) ."/". substr($t1s,8,2);
break;
default:
$result[$t1s][0]= $t1s;
}
$t1->add($dt);
}
$scale .= "-->\n";
$res .= $scale;
$res .= "<CELLS>\n";
$rows = mysql_query($stmt);
while($row = mysql_fetch_array($rows, MYSQL_ASSOC)) {
$res .= "<CELL tstamp='" .$row['tstamp']. "'>\n";
foreach($row as $key => $value) {
$tstamp= $row['tstamp']; $code= $row['code'];
for($i1=0; $i1<count($codeArr); $i1++){
if($codeArr[$i1]==$code){
$result[$tstamp][$i1+1]= $row['a_val'];
}
}
$res .= "<$key><![CDATA[$value]]></$key>\n";
}
$res .= "</CELL>\n";
}
$res .= "</CELLS>\n";
$rowset= "";
foreach($result as $key => $value){
$rowset .= "<ROW tstamp='$key'";
$rowset .= " time='" .$value[0]. "'";
for($i1=1; $i1<count($value); $i1++){
$rowset .= " val$i1='" .$value[$i1]. "'";
}
$rowset .= " />";
}
$res .= $rowset;
$res .= "</ROWSET>\n";
header ("Content-Type:text/xml; charset=UTF-8");
echo $res;
?>
#!/bin/env groovy
import groovy.sql.Sql
import com.sun.net.httpserver.*
if (System.getProperty("os.name")=="Linux"){
jdbcURL="jdbc:mysql://localhost:3306/airs"
jasperDir= "/var/www/html/jasper/"
} else {
jdbcURL="jdbc:mysql://ec2-54-208-78-75.compute-1.amazonaws.com:3306/airs"
jasperDir= "r:/DROPBOX/amazonaws/jasper/"
}
Object.metaClass.mysqlconnection= {->
Sql.newInstance("${jdbcURL}?"+
"useUnicode=true&characterEncoding=UTF-8",
'userairs', 'userairs', 'com.mysql.jdbc.Driver')
}
oldStdOut=System.out
def server = HttpServer.create(new InetSocketAddress(8000),0)
server.with{
createContext("/", new info(oldStdOut:oldStdOut, jasperDir:jasperDir))
createContext("/kill", new Kill(server:server,oldStdOut:oldStdOut))
createContext("/sql2xml", new sql2xml(jdbcURL:jdbcURL))
createContext("/sql2json", new sql2json(jdbcURL:jdbcURL))
new File(jasperDir).eachFileMatch(~/.*.groovy/) { file->
def fname=file.getName()[0..-8]
println fname
createContext("/${fname}", new groovy2xml(jasperDir:jasperDir,oldStdOut:oldStdOut,jasperScript:fname))
}
//
setExecutor(null)
start()
println "\nGroovy-xmlServer startded:"
}
class info implements HttpHandler {
def oldStdOut
def jasperDir
public void handle(HttpExchange exchange) throws IOException {
System.out=oldStdOut
exchange.responseHeaders['Content-Type'] = 'text/html; charset=UTF-8'
exchange.sendResponseHeaders(200, 0)
def out = new PrintWriter(exchange.getResponseBody())
out.println """
<H2>Groovy XML server (25-01-2014)</H2>
<H3>You can afford the following:</H3>
<div style="font-size:24">
<ui>
<li><a href='/sql2xml.php' target='_blank'>sql2xml.php?select ...</a>
<li><a href='/sql2json.php' target='_blank'>sql2json.php?select ...</a>
"""
new File(jasperDir).eachFileMatch(~/.*.groovy/) { file->
def fname=file.getName()[0..-8]
out.println "<li><a href='/${fname}.php' target='_blank'>${fname}.php?...</a>"
}
out.println """
<li><a href='/kill.php' ta-rget='_blank'>kill.php (с чудесным последующим воскрешением)</a>
<ui>
</div>
"""
println "Groovy-xmlServer is ready!\n"
out.close()
exchange.close()
}
}
class Kill implements HttpHandler {
def server
def oldStdOut
public void handle(HttpExchange exchange) throws IOException {
System.out=oldStdOut
exchange.responseHeaders['Content-Type'] = 'text/html; charset='+CharsetToolkit.defaultSystemCharset
exchange.sendResponseHeaders(200, 0)
def out = new PrintWriter(exchange.getResponseBody())
out.println "<H3>Groovy-xmlServer killed.</H3>"
out.close()
exchange.close()
server.stop(3)
println "Groovy-xmlServer killed.\n\n"
if (System.properties['sun.desktop']) return
System.exit(0)
}
}
class sql2xml implements HttpHandler {
def sql
def jdbcURL
public void handle(HttpExchange exchange) throws IOException {
try{sql=mysqlconnection()} catch(Exception ex) { ex.printStackTrace() }
def query = exchange.getRequestURI().getQuery()
if (!query) query="select 1 one;"
println "==> sql2xml:\n$query\n"
exchange.responseHeaders['Content-Type'] = 'text/xml; charset=UTF-8'
exchange.sendResponseHeaders(200, 0)
def out = new PrintWriter(exchange.getResponseBody())
out.println "<ROWSET autor='is09-20' jdbcURL='${jdbcURL}'>"
out.println "<SQL>$query</SQL>"
try{
sql.rows(query).each{row->
out.println "<ROW>"
row.each {out.println "<$it.key><![CDATA[$it.value]]></$it.key>"}
out.println "</ROW>"
}
} catch(Exception ex) {
out.println "<error><![CDATA[${ex.getMessage()}]]></error>"
println "sql2xml: $query\n ${ex.getMessage()}"
}
out.println "</ROWSET>"
out.close()
exchange.close()
}
}
class sql2json implements HttpHandler {
def sql
def jdbcURL
public void handle(HttpExchange exchange) throws IOException {
try{sql=mysqlconnection()} catch(Exception ex) { ex.printStackTrace() }
def query = exchange.getRequestURI().getQuery()
if (!query) query="select 1 one;"
println "==> sql2json:\n$query\n"
exchange.responseHeaders['Content-Type'] = 'application/json; charset=UTF-8'
exchange.sendResponseHeaders(200, 0)
def out = new PrintWriter(exchange.getResponseBody())
out.print "[{\"autor\":\"is09-20\", \"jdbcURL\":\"${jdbcURL}\", \"SQL\":\"$query\"},\n["
def rr=0
try {
sql.rows(query).each{row->
if(rr>0) out.print ",{" else out.print "{"; rr=1
row.each {
if(rr>1) out.print ","; rr=2
out.print "\"${it.key}\": \"${it.value}\""
}
out.println "}"
}
out.println "]]"
} catch(Exception ex) {
out.println "], {\"error\":\"${ex.getMessage()}\"}]"
println "sql2json: $query\n ${ex.getMessage()}"
}
out.close()
exchange.close()
}
}
class groovy2xml implements HttpHandler {
def jasperDir
def oldStdOut
def jasperScript
public void handle(HttpExchange exchange) throws IOException {
System.out = oldStdOut
def query = exchange.getRequestURI().getQuery() ?: ""
println "==> ${jasperScript} '${query}'"
exchange.responseHeaders['Content-Type'] = 'text/xml; charset=UTF-8'
exchange.sendResponseHeaders(200, 0)
def out = new PrintWriter(exchange.getResponseBody())
def groovyFile="${jasperDir}${jasperScript}.groovy"
def bufStr = new ByteArrayOutputStream()
println "run(new File('$groovyFile') '${URLDecoder.decode(query)}')"
System.out = new PrintStream(bufStr)
def shell = new GroovyShell()
shell.run(new File(groovyFile), URLDecoder.decode(query))
System.out = oldStdOut
def xml= bufStr.toString()
out.println xml
println "'${groovyFile}' finished.\n"
out.close()
exchange.close()
}
}
if (System.properties['sun.desktop']){
java.awt.Desktop.getDesktop().browse(new URI("http://localhost:8000/"))
}
thirteen Groovy snippets
import groovy.time.TimeCategory
def dInt
use (TimeCategory) {
dInt= [
'h2': 1.hour,
'h5': 1.month
]
}
def dt= dInt["h2"]
def fmt = new java.text.SimpleDateFormat("yyyyMMddHHmm")
def t1 = fmt.parse("201104050000")
def t2 = fmt.parse("201104052300")
def t1s
while(t1<=t2){
t1s=fmt.format(t1)
println "${t1s}->${t1s[6,7]}/${t1s[8,9]}"
use (TimeCategory) {t1=t1+dt}
}
201104050000->05/00
201104050100->05/01
201104050200->05/02
201104050300->05/03
201104050400->05/04
201104050500->05/05
201104050600->05/06
201104050700->05/07
201104050800->05/08
201104050900->05/09
201104051000->05/10
201104051100->05/11
201104051200->05/12
201104051300->05/13
201104051400->05/14
201104051500->05/15
201104051600->05/16
201104051700->05/17
201104051800->05/18
201104051900->05/19
201104052000->05/20
201104052100->05/21
201104052200->05/22
201104052300->05/23
import groovy.sql.Sql
mysql=Sql.newInstance("jdbc:mysql://advanta.sytes.net:3306/airs?useUnicode=true&characterEncoding=UTF-8",
'userairs', 'userairs', 'com.mysql.jdbc.Driver')
def sql="SELECT level,number,name,descr,content1 FROM reports1 WHERE lev=1 AND number='0111' ORDER BY 2"
def row = mysql.firstRow(sql)
def cont1= row[4].replace(';',' ;').tokenize(';')
row.each{println it}
println ""
for(int x0=0; x0<13; x0++){
for(int x1=0; x1<11; x1++) print("${cont1[11*x0+x1]}".trim()+'; ')
println ''
}
level=СУТКИ
number=0111
name=Показатели котлоагрегата
descr=Блок 1
content1=1.;1NA00GC02;Потери Q ух.газов, факт,взвеш.;%;1;Fixed;2;1;11;22;r;2.;1NA00GN02;Потери Q ух.газов, норм,взвеш.;%;1;Fixed;2;1;11;22;r;3.;1NA00GC06;Потери Q-окр.ср + пр, факт.;%;1;Fixed;2;1;11;22;r;4.;1NA00GN25;Потери Q окр.среды, норм.;%;1;Fixed;2;1;11;22;r;5.;1NA00BD10;П/Э топл.-пот Q в окр.ср.;кг/с&т;1;Fixed;2;3,6;2;1;m;6.;1NA00K802;KПД котла БРУТТО, обр,,взвеш.;%;1;Fixed;2;1;11;22;r;7.;1NA00KN02;KПД котла БРУТТО, норм,взв.;%;1;Fixed;2;1;11;22;r;8.;1HN10E831;Mощн.механ тяги, дутья;МВт&МВт*ч;1;Fixed;3;1;2;1;m;9.;1HN10EN31;Mощн.мех.тяги,дут, норм.;МВт&МВт*ч;1;Fixed;3;1;2;1;m;10.;1HN10BD11;П/Э топл.- эл.эн.т-д;кг/с&т;1;Fixed;2;3,6;2;1;m;11.;;;;;;;;;;;12.;;;;;;;;;;;13.;;;;;;;;;;1.; 1NA00GC02; Потери Q ух.газов, факт,взвеш.; %; 1; Fixed; 2; 1; 11; 22; r;
2.; 1NA00GN02; Потери Q ух.газов, норм,взвеш.; %; 1; Fixed; 2; 1; 11; 22; r;
3.; 1NA00GC06; Потери Q-окр.ср + пр, факт.; %; 1; Fixed; 2; 1; 11; 22; r;
4.; 1NA00GN25; Потери Q окр.среды, норм.; %; 1; Fixed; 2; 1; 11; 22; r;
5.; 1NA00BD10; П/Э топл.-пот Q в окр.ср.; кг/с&т; 1; Fixed; 2; 3,6; 2; 1; m;
6.; 1NA00K802; KПД котла БРУТТО, обр,,взвеш.; %; 1; Fixed; 2; 1; 11; 22; r;
7.; 1NA00KN02; KПД котла БРУТТО, норм,взв.; %; 1; Fixed; 2; 1; 11; 22; r;
8.; 1HN10E831; Mощн.механ тяги, дутья; МВт&МВт*ч; 1; Fixed; 3; 1; 2; 1; m;
9.; 1HN10EN31; Mощн.мех.тяги,дут, норм.; МВт&МВт*ч; 1; Fixed; 3; 1; 2; 1; m;
10.; 1HN10BD11; П/Э топл.- эл.эн.т-д; кг/с&т; 1; Fixed; 2; 3,6; 2; 1; m;
11.; ; ; ; ; ; ; ; ; ; ;
12.; ; ; ; ; ; ; ; ; ; ;
13.; ; ; ; ; ; ; ; ; ; null;