LogAwp_attrib11Opasnet base connectionInterface for uploading data to and downloading from the Opasnet Base.
<a href="http://en.opasnet.org/w/Image:Opasnet_base_connection.ANA">Wiki description</a>Jouni Tuomisto9. maata 2008 10:42ktluser21. Augta 2010 13:28 48,241,0,0,1,1,1,0,0,0,01,20,108,798,696,172,102,90,476,316Arial, 150,Linkmodule Op_en2676,2,2,0,1,C:\temp\Opasnet base connectionb.ANA79,1,1,0,1,9,2794,2159,15,02,40,50,640,600Username0196,44,1188,121,0,0,1,0,0,0,206,0,152425,39321,65535Opasnet_usernameTabIndex:1 TextAlways2 Analytica model:396,204,-1124,121,0,0,1,0,1,0,,0,65535,65532,19661Add a list of variable identifiers to be downloaded at the same time. First, give the number of Analytica variables (nodes) you want to upload. Then, fill in the table Object info.396,316,-1124,1001,0,0,1,0,1,0,,0,N variables0396,372,1116,121,0,0,1,0,0,0,72,0,152425,39321,65535N_variables2Replace data?0264,140,1256,121,0,0,1,0,0,0,374,0,152425,39321,65535Replace_data_Data source0264,116,1256,121,0,0,1,0,0,0,374,0,1Data_sourceWriterpsswd0196,68,1188,121,0,0,1,0,0,0,206,0,152425,39321,65535WriterpsswdUpload data:264,476,-1256,121,0,0,1,0,1,0,,0,65535,65532,19661Upload all data directly to Opasnet Base. This includes
both object and explanation information (if not uploaded
already) and the actual observations.264,524,-1256,361,0,0,1,0,1,0,,0,Provide general information:264,20,-1256,121,0,0,1,0,1,0,,0,65535,65532,19661Help pages in Opasnet about
<a href="http://en.opasnet.org/w/Opasnet_Base">Opasnet Base</a>
<a href="http://en.opasnet.org/w/Uploading_to_Opasnet_Base">Uploading to Opasnet Base</a>
<a href="http://en.opasnet.org/w/Opasnet_base_connection">Opasnet Base connection</a>
<a href="http://en.opasnet.org/w/Opasnet_Base_UI">Opasnet Base user interface</a>
644,124,-1108,60Choose the format of input data.264,92,-1256,121,0,0,1,0,1,0,,0,65535,65532,19661Check that your data table looks sensible:156,436,-1148,12N_rows0132,349,1116,131,0,0,1,0,0,0,72,0,152425,39321,65535N_rowsTell the dimensions of the data table. First row must contain column names, not values!132,316,-1124,1001,0,0,1,0,1,0,,0,Analytica table0132,372,1116,131,0,0,1,0,0,0,72,0,152425,39321,65535Data_table31 Data table:132,204,-1124,121,0,0,1,0,1,0,,0,65535,65532,19661Object info0132,397,1116,131,0,0,1,0,0,0,72,0,152425,39321,65535Object_info1_3_4N cols0132,325,1116,131,0,0,1,0,0,0,72,0,152425,39321,65535N_colsModel detailsktluser4. Febta 2010 7:10 48,24688,32,148,241,0,0,1,1,1,0,,0,1,10,46,1124,528,17Password0736,352,1160,121,0,0,1,0,0,0,142,0,152425,39321,65535Opasnet_passwordTabIndex:2 TextAlwaysPlatform0736,16,1160,121,0,0,1,0,0,0,142,0,152425,39321,65535PlatformC) For very large variables: Upload only the object and location information. Create csv files of other data to c:\temp\ and upload them separately (you need a direct access to the Opasnet Base).832,212,-1256,681,0,0,1,0,1,0,,0,B) Upload so that the actual data is not visible without a password. Metadata is visible anyway.832,88,-1256,481,0,0,1,0,1,0,,0,2,693,146,476,224Writerjtue24. maata 2009 9:36 48,24184,40,148,241,298,13,707,463,17Columns1..N_cols64,136,148,12[1,2]W locvar obj:= Obj();
var a:= Locations;
var b:= a[.j='obj_id_i'];
var c:= cardinals1('loc');
c:= c+a[@.j=1];
a:= array(a.j,[c, c, findid(b,obj,'ident'), a, a, a]);
textify(a)512,160,148,162,84,125,476,2452,318,186,606,278,0,MIDM65535,45873,39321[Sys_localindex('J'),Sys_localindex('I')]2,D,4,2,0,0,4,0,$,0,"ABBREV",0100,1,1,1,1,9,2970,2100,15,0[Sys_localindex('I'),16,Sys_localindex('I'),1,Sys_localindex('J'),1]W loccellvar a:= cellulise(Indexified_table);
index temp:= ['cell_id', 'loc_id'];
index i:= 1..size(a);
a:= array(temp,[@a.i, a]);
a:= concatrows(a, a.j, a.i, i);
index j:= ['id', 'cell_id', 'loc_id'];
a:= array(j, [
i+cardinals1('loccell'),
a[temp='cell_id']+cardinalcell,
a[temp='loc_id'] ]);
textify(a)512,304,148,162,805,21,476,3992,33,106,561,537,0,MIDM65535,45873,39321[Sys_localindex('I'),Sys_localindex('J')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0W cellvar a:= Indexified_table;
index j:= ['id', 'actobj_id', 'mean', 'sd', 'n'];
a:= if 1- sum(a.j='obs', a.j) then array(j,[@a.i,'', a[.j='result'], '', 1]) else
(
var b:=cellulise(a);
b:= jointext(textify(b),b.j,',');
var c:= removecol(a, a.j);
c:= jointext(textify(c),c.j,',');
index temp:= ['mean','sd','n'];
var e:= for x[]:= b do (
var d:= if x=c then a[.j='result'] else null;
d:= array(temp,[mean(d,d.i), sdeviation(d,d.i), sum(d<>null,d.i)]) );
array(j, [@b.i, '', e[temp='mean'], e[temp='sd'], e[temp='n']])
);
a:= array(a.j, [
a+cardinalcell,
W_actobjs[@N_vars=lap], a, a, a]);
textify(a)512,208,148,162,703,140,476,3792,14,241,659,368,0,MIDM65535,45873,39321[Sys_localindex('J'),Sys_localindex('I')]2,D,4,2,0,0,4,0,$,0,"ABBREV",0[N_vars,1,Sys_localindex('I'),1,Sys_localindex('J'),1]W objvar obj:= Obj();
var a:= Objects;
var b:= if a[.j='ident'] = 0 then -1 else a[.j='ident'];
b:= findid(b, obj, 'ident');
var c:= cardinals1('obj');
b:= if b='0' then c+a[.j='id'] else b;
a:= if a.j='id' then b else a;
textify(a)512,24,148,162,372,300,476,3432,590,354,626,444,0,MIDM65535,45873,39321[Sys_localindex('J'),Sys_localindex('I')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0[]W actvar a:= Acts;
var c:= cardinals1('act');
a:= if a.j='id' then a+c else a;
a:= if a=null or a=0 then '' else a&'';
textify(a)512,64,148,162,66,82,476,3402,34,242,690,459,0,MIDM65535,45873,39321[Sys_localindex('J'),Sys_localindex('I')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0[Sys_localindex('J'),1,Sys_localindex('I'),1,Sys_localindex('J'),1]W resvar a:= Indexified_table;
var b:=cellulise(a);
index temp:= jointext(textify(b),b.j,',');
b:= @temp;
var c:= removecol(a, a.j);
c:= jointext(textify(c),c.j,',');
b:= b[temp=c];
a:= a[.j='result'];
index j:= ['id','cell_id','obs','result','restext'];
a:= array(j,[
@a.i+Cardinals1('res'),
b+Cardinalcell,
Indexified_table[.j='obs'],
(if istext(a) then '' else a) ,
(if istext(a) then a else '')] );
textify(if a=null then '' else a)512,256,148,162,652,144,582,3682,635,98,609,375,0,MIDM65535,45873,39321[Sys_localindex('J'),Sys_localindex('I')]2,D,4,2,0,0,4,0,$,0,"ABBREV",0[]Object info for lapObject_info[N_vars=Lap]176,256,148,202,140,217,476,2242,653,25,488,226,0,MIDM2,104,359,460,228,0,MIDM52425,39321,65535[N_vars,Info][N_vars,Info][0,1,1,0]Locationsindex expl:= slice(Standard_table.j, Standard_table.j, 1..explanations);
var a:= Standard_table[.j= expl];
a:= for x= expl do (
var b:= a[expl=x];
index temp:= unique(b, b.i);
b:= expl[expl=x]&'+++'&b[@.i=temp];
jointext(b, temp, '+-+') );
a:= jointext(a, expl, '+-+');
var d:= Standard_table.j;
var c:= Object_info_for_lap[Info='observation index'];
var node:= selecttext(data_source,1,1)='2';
var N_indices:= explanations+sum(d.j='obs', d.j);
c:= if c='' or c=0 or node then 'Observation' else c;
index e:= c&'+++'&d.j[@d.j=(N_indices+1)..size(d.j)];
c:= jointext(e, e, '+-+');
a:= splittext(a&'+-+'&c, '+-+');
index i:= 1..size(a);
a:= slice(a,i);
a:= splittext(a, '+++');
index j:= ['id', 'std_id', 'obj_id_i', 'location', 'description'];
a:= array(j,[@i, @i, slice(a, 1), slice(a, 2), ''])400,160,148,162,704,20,476,5812,97,88,495,259,0,MIDM[Sys_localindex('I'),Sys_localindex('J')]2,D,4,2,0,0,4,0,$,0,"ABBREV",0['','','','',''][Self,4,Sys_localindex('J'),1,Sys_localindex('I'),1]Indexified tablevar a:= Standard_table;
index itemp:= (block-1)*blocksize+1..min([size(a.i),(block*blocksize)]);
a:= a[.i=itemp];
index jtemp:= a.j[@.j=1..explanations];
var b:= a[.j=jtemp];
b:= b.jtemp&'+'&b;
b:= loc[.i=b];
b:= b[jtemp=a.j];
a:= if @b.j=size(b.j) then a else b;
/* INDEXIFY */
var N_indices:= explanations+sum(a.j='obs', a.j);
index h:= if N_indices=0 then [] else a.j[@a.j=1..N_indices];
var c:= Object_info_for_lap[Info='observation index'];
c:= if c='' or c=0 or data_source = '2 Analytica model' then 'Observation' else c;
index j:= concat(h,[c,'result']);
var e:= a.j[@a.j=(N_indices+1)..size(a.j)];
e:= c&'+'&e;
index obs:= loc[.i=e];
index temp:= 1..size(a.itemp)*size(obs);
var conv:= if j='result' then @obs+N_indices else @j;
a:= a[@.j=conv];
a:= if @j=size(j)-1 then obs else a;
a:= concatrows(a, obs, a.itemp, temp);
index i:= Subset(a[j='result']<>null);
a[temp=i]288,256,148,222,733,20,482,5892,85,245,698,376,0,MIDM[Sys_localindex('I'),Sys_localindex('J')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0[1,1,1,0]['observation'][Sys_localindex('I'),6,Sys_localindex('J'),1,Sys_localindex('OBS'),1]Info['Analytica identifier','ident','name','unit','# explanation cols','observation index','observation name','probabilistic?']280,48,148,132,102,90,476,3792,90,166,416,303,0,MIDM['Analytica identifier','ident','name','unit','# explanation cols','observation index','observation name','probabilistic?']ObjectsIndex j:= ['id','ident','name','unit','objtype_id','page','wiki_id'];
index i:= copyindex(N_vars);
var a:= Object_info[Info=j];
var Ident:= Object_info[Info='ident'];
a:= if a=null then 0 else a;
var b:= sum(findintext(wikis,ident)*@wikis,wikis);
var c:= if b=0 then '' else wikis[@wikis=b];
c:= if b=0 then '2664' else selecttext(ident,1+textlength(c));
a:= array(j,[@N_vars, ident, a, a, 1, c&'', if b=0 then '1' else b&'']);
a[@N_vars=@i]400,24,148,162,573,29,547,7242,11,31,656,283,0,MIDM[Sys_localindex('J'),Sys_localindex('I')][Indices,1,Sys_localindex('J'),1,Sys_localindex('I'),1]TestvariableTable(Time,Testindex)(
uniform(0,1),
uniform(1,2),
uniform(2,3)
)64,376,148,242,424,516,416,303,0,MIDM[Testindex,Time][Time,Testindex][1,0,0,0]Testindex['item 2']64,408,148,122,102,90,476,224['item 2']W cellsec(if w_cell.j = 'mean' then '' else W_cell)624,248,148,162,782,213,476,3792,41,191,618,368,0,MIDM65535,45873,39321[Sys_localindex('J'),Sys_localindex('I')]2,D,4,2,0,0,4,0,$,0,"ABBREV",0Probavar a:= Object_info_for_lap[Info='probabilistic?'];
(a=1 or a='Yes' or a='Y' or a='yes' or a='y')64,256,148,16Data sourceChoice(Self,2,False)176,104,148,162,102,90,476,260[Formnode Data_source1]52425,39321,65535['1 Data table','2 Analytica model'][1,1,0,0]N vars1.. (if selecttext(Data_source,1,1)='2' then N_variables2 else 1)280,72,148,12[1]Lap1176,312,148,16[0,1,0,1]Object infoTable(Info,N_vars)(
'Testvariable',
'Op_en2693',
'Testvariable',
'#',
2,
0,
'observation',
0
)280,24,148,162,140,217,476,2242,606,303,494,259,0,MIDM2,590,303,460,228,0,MIDM52425,39321,65535[N_vars,Info][N_vars,Info][1,1,1,0]Data tableTable(Columns1,Rows1)(
'Year',2000,
'# of deaths',615.3K
)64,80,148,242,38,23,716,390,0,MIDM2,248,258,639,303,0,MIDM[Formnode Analytica_table2]52425,39321,65535[Columns1,Rows1][Columns,Rows]2,D,4,2,0,0,4,0,$,0,"ABBREV",0Rowsconcat(['1 (Titles)'],2..N_rows)64,112,148,12['1 (Titles)',2]N rows264,160,148,131,1,1,1,1,1,0,0,0,0[Formnode N_rows1]52425,39321,65535ActsIndex j:= ['id', 'acttype_id','who','comments'];
index i:= 1.. size(N_vars);
array(j,[
@i,
if selecttext(replace_data_,1,1)='Y' then 4 else 5,
opasnet_username,
'Analytica '&Analyticaedition&', ('&Analyticaplatform&'), Version: '&textify(Analyticaversion)])400,64,148,162,29,37,525,7242,29,360,694,283,0,MIDM[Sys_localindex('J'),Sys_localindex('I')]['',''][Self,1,Sys_localindex('I'),1,Sys_localindex('J'),1]Upload type 1.64,108,-156,1001,0,0,1,0,1,0,,0,Upload type 2.120,276,-1112,601,0,0,1,0,1,0,,0,W actobjindex j:= ['id', 'act_id', 'obj_id', 'series_id'];
var a:= w_act;
var c:= cardinals1('actobj');
a:= array(j,[
@a.i+c,
W_acts[@N_vars=@a.i],
W_objs[@N_vars=@a.i],
if a[.j= 'acttype_id'] = '5' then Seriess[@N_vars = @a.i] else W_acts[@N_vars=@a.i]]);
textify(a)624,64,148,162,77,45,476,3402,422,292,690,285,0,MIDM65535,45873,39321[Sys_localindex('J'),Sys_localindex('I')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0[][W_actsi,1,Sys_localindex('I'),1,Sys_localindex('J'),1]Series9.6.2010 Jouni Tuomisto
Why is this not just simply Cardinal? Because this looks for the previous replace event of the same object. This is used in the case that this upload is an append, not a replace.for x[]:= W_obj.i do (
var a:= query('
SELECT MAX(act.id)
FROM actobj
LEFT JOIN act ON actobj.act_id = act.id
WHERE obj_id = '&chr(39)&W_obj[.j = 'id', .i = x]&chr(39)&'
AND acttype_id = 4
');
a[@.j = 1, @.i=1] )624,24,148,162,102,90,476,3742,258,57,416,303,0,MIDM39325,65535,39321[Sys_localindex('I'),Sys_localindex('J')]W indvar obj:= Obj();
var a:= Indices1;
var b:= if a[.j='ident'] = 0 then -1 else a[.j='ident'];
b:= findid(b, obj, 'ident');
var c:= cardinals1('obj');
b:= if b='0' then c+a[.j='id'] else b;
a:= if a.j='id' then b else a;
textify(a)512,112,148,162,372,300,476,3432,429,135,626,444,0,MIDM65535,45873,39321[Sys_localindex('J'),Sys_localindex('I')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0[]Indicesvar a:= locations[.j='obj_id_i'];
index i:= a[@.i=unique(a, a.i)];
index j:= ['id','ident','name','unit','objtype_id','page','wiki_id'];
a:= array(j,[@i, i, i, '', 6, 2664, 1])400,112,148,162,573,29,547,7242,11,31,656,283,0,MIDM[Sys_localindex('J'),Sys_localindex('I')][Indices,1,Sys_localindex('J'),1,Sys_localindex('I'),1]N variables1280,96,148,12[Formnode N_variables3]52425,39321,65535W objsTable(N_vars)(
'1292'
)624,144,148,162,372,300,476,3432,590,354,626,444,0,MIDM52427,56425,65535[Sys_localindex('J'),Sys_localindex('I')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0[]W actsTable(N_vars)(
'503'
)624,176,148,162,66,82,476,3402,38,26,208,200,0,MIDM52427,56425,65535[Sys_localindex('J'),Sys_localindex('I')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0[Sys_localindex('J'),1,Sys_localindex('I'),1,Sys_localindex('J'),1]W actobjsTable(N_vars)(
'966'
)624,208,148,162,77,45,476,3402,349,265,690,459,0,MIDM52427,56425,65535[Sys_localindex('J'),Sys_localindex('I')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0[][Sys_localindex('J'),1,Sys_localindex('I'),1,Sys_localindex('J'),1]SeriessTable(N_vars)(
502
)624,112,148,162,77,45,476,3402,349,265,690,459,0,MIDM52427,56425,65535[Sys_localindex('J'),Sys_localindex('I')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0[][Sys_localindex('J'),1,Sys_localindex('I'),1,Sys_localindex('J'),1]Standard tableif selecttext(Data_source,1,1) = '1' then (
index i:= 1..N_rows-1;
index j:= textreplace(Data_table3[@rows1=1],'.', '', true);
var a:= Data_table3[@rows1=@i+1, @columns1=@j];
observise(a) )
else (
var a:= Object_info_for_lap[Info='Analytica identifier'];
a:= if proba then sample(evaluate(a)) else evaluate(a);
var c:= Object_info_for_lap[Info='observation name'];
c:= if c='' or c=0 then 'result' else c;
index temp:= concat(indexnames(a),[c]);
index i:= 1..size(a);
a:= if size(a)=1 then array(i,temp,[a]) else
Mdarraytotable(a, i, temp);
index j:= textreplace( (if temp='Run' then 'obs' else temp), '.', '', true);
a[@temp=@j] )176,160,148,222,216,153,482,5012,685,327,698,233,0,MIDM[Formnode Data_table4][Sys_localindex('I'),Sys_localindex('J')]2,D,4,2,0,0,4,0,$,0,"ABBREV",0[1,1,1,0][Sys_localindex('OBS'),1,Sys_localindex('J'),1,Sys_localindex('ROWS'),1]Block1288,312,148,12N cols264,184,148,131,1,1,1,1,1,0,0,0,0[Formnode N_cols1]52425,39321,65535Locvar a:= locations;
var c:= a[.j='obj_id_i'];
var d:= a[.j='location'];
a:= for x:= a.i do (
var b:= query('
SELECT loc.id FROM loc
LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id
WHERE loc.location = '&chr(34)& d[.i=x]&chr(34)&' AND ind.ident = '&chr(34)& c[.i=x]&chr(34) );
b[@.j=1, @.i=1]);
index i:= c&'+'&d;
a[@.i=@i]288,184,148,162,9,224,476,4502,26,474,817,303,0,MIDM39325,65535,39321[Sys_localindex('J'),Sys_localindex('TEMP3')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0(a)observiseThe probabilistic approcah here might be problematic. The unique function becomes exponentially heavy when the size of the node increases. However, this problem may only occur when data table format is used in probabilistic mode. This is a rare situation. In any case, the model should work always.if 1-proba then (
index j:= concat(a.j,['obs']);
a:= a[.j=j];
a:= if j='obs' then @a.i else a)
else (
var N_indices:= Object_info_for_lap[Info='# explanation cols'];
N_Indices:= if data_source='2 Analytica model' then size(a.j)-1 else N_indices;
index expl:= 1..N_indices;
var b:= jointext(textify(a[@.j=expl]),expl,'+');
index cell:= unique(b, b.i);
var c:= b[@.i=cell];
b:= b=c;
b:= sum(cumulate(b,b.i)*b,cell);
b:= if N_indices=0 then @a.i else b;
index j:= concat(a.j,['obs']);
if j='obs' then b else a[.j=j]
)72,400,148,122,94,57,476,596a(a)CelluliseIf proba=0, a[.j='obs']<=1 will result in null, which is a non-zero value, and all cells will be selected by subset.index i:= subset(a[.j='obs']<=1);
a:= a[.i=i];
removecol(a, a.j)72,448,148,122,469,20,476,498aReaderktluser3. Augta 2008 18:31jtue9. lokta 2008 14:01 48,24312,40,148,241,1,1,1,1,1,0,0,0,01,601,119,477,429,17Arial, 15(vident:text, seriesid:optional)Read meanReads the mean data about the vident variable from the Opasnet Base. Uses the run with runid as run.id if specified; otherwise uses the newest run of that variable.
PARAMETERS:
* Vident: the ident of the variable in the Opasnet Base.
* Runid: the id of the run from which the results will be brought. If omitted, the newest result will be brought.
Change
run to actif isnotspecified(seriesid) or istext(seriesid) then seriesid:= Newest_series(vident);
query( '
SELECT obj.ident, obj.name, obj.unit, cell.id as cell_id, mean, sd, n, act_id, comments, time, std.location, ind.ident AS iident, ind.name AS iiname, actobj.series_id
FROM obj
LEFT JOIN actobj ON obj.id = actobj.obj_id
LEFT JOIN act ON act.id = actobj.act_id
LEFT JOIN cell ON cell.actobj_id = actobj.id
LEFT JOIN loccell ON loccell.cell_id = cell.id
LEFT JOIN loc ON loccell.loc_id = loc.id
LEFT JOIN loc as std ON loc.std_id = std.id
LEFT JOIN obj as ind ON std.obj_id_i = ind.id
WHERE obj.ident = '&chr(39)&vident&chr(39)&'
AND actobj.series_id = '&chr(39)&seriesid&chr(39)
)56,80,148,122,585,25,516,58939325,65535,39321vident,seriesid(vident:text)Newest seriesThis function checks for the newest result (according to run_id) of the variable. The function is used if the user does not define the run_id as an optional parameter in functions Read_mean and Read_sample.
PARAMETERS:
* Vident: the Ident of the variable in the Opasnet Base.var a:= query('
SELECT actobj.series_id, var.ident
FROM obj AS var
LEFT JOIN actobj ON var.id = actobj.obj_id
LEFT JOIN act ON actobj.act_id = act.id
WHERE var.ident = "'&vident&'"
');
max(a[@.j=1],a.i)56,22,148,222,678,59,476,56639325,65535,39321vident(vident:text, seriesid:optional)Read sampleReads the sample data about the vident variable from the Opasnet Base. Uses the runident run if specified; otherwise uses the newest run of that variable.
PARAMETERS:
* Vident: the name of the variable in the Opasnet Base.
* Runid: the id of the run from which the results will be brought. If omitted, the newest result will be brought.
chenge
run to act
add restextif isnotspecified(seriesid) or istext(seriesid) then seriesid:= Newest_series(vident);
query( '
SELECT obj.id AS obj_id, obj.ident, obj.unit, ind.ident as iident, cell.id AS cell_id, location, mean, n, obs, result, restext
FROM obj
LEFT JOIN actobj ON actobj.obj_id = obj.id
LEFT JOIN act ON actobj.act_id = act.id
LEFT JOIN cell ON cell.actobj_id = actobj.id
LEFT JOIN loccell ON loccell.cell_id = cell.id
LEFT JOIN loc ON loccell.loc_id = loc.id
LEFT JOIN obj as ind ON loc.obj_id_i = ind.id
LEFT JOIN res ON res.cell_id = cell.id
WHERE obj.ident = '&chr(39)&vident&chr(39)&'
AND actobj.series_id = '&chr(39)&seriesid&chr(39)
)56,112,148,222,55,35,516,61239325,65535,39321vident,seriesidEnter variable Ident'Op_en1912'168,83,148,27[Formnode Enter_variable1]52425,39321,65535Enter variable0288,24,1176,131,0,0,1,0,0,0,170,0,152425,39321,65535Enter_variableNewest seriesNewest_series(Enter_variable)288,61,148,222,200,56,416,303,0,MIDM[Sys_localindex('J'),Sys_localindex('I')]Var inforead_mean(Enter_variable)288,116,148,122,28,65,1144,438,0,MIDM[Sys_localindex('J'),Sys_localindex('I')](a,b,x)MakeindThe input table a must have a structure that is also used as input for MDTable function. The function removes one column with location information and makes a dimension (index) with the locations in the column. Inde is the (local) index that will be added. Note that unlike MDTable function, this can use local indices in the output.index inde / slice(b.m, x) := b[.n = unique(b[@.m = x], b.n), @b.m = x];
a:= if inde = a[@.m=1] then a else 0;
index m:= slice(a.m,(2..size(a.m)));
a:= a[.m=m]56,192,148,122,61,72,476,454a,b,x(a)Get cell_idMakes a multi-dimensional array with the same structure as the original variable that was stored into the Opasnet Base. The contents of the array are the cell_ids of the variable. The input parameter must be a 2D table with the structure that comes from the Read_mean function.
1) Slices the necessary columns from the input table and converts that to a 2D table that has the same structure as is used for input to the function MDTable.
2) Defines the local indices, and changes a location column to a dimension one at a time until all columns have been changed.
NOTE! There is a problem that if there are two or more cells with the exactly same locations, only the one with a largest cell_id will be taken.index k:= ['iident','location','cell_id', 'act_id'];
a:= a[.j=k];
a:= if a.k = 'iident' then textreplace(a, ' ', '_', true) else a;
index L:= a[@k=1]&'+'&textify(a[@k=3]);
index m:= concat(a[.i=unique(a[@k=1],a.i), @k=1],['act_id', 'result']);
index n:= a[.i=unique(a[@k=3],a.i), @k=3];
a:= a[@.i=@L];
var c:= a[L=(m)&'+'&textify(n), @k=4, @m=1];
a:= a[L=(m)&'+'&textify(n), @k=2];
a:= if m='result' then n else a;
a:= if m='act_id' then c else a;
var b:= a;
var x:= 1;
a:= while x< size(b.m) do (
a:= makeind(a,b,x);
x:= x+1;
a);
a:= max(a[@.m=1], a.n);
if size(a.act_id)=1 then a[@.act_id = 1] else a56,168,148,132,160,51,476,628aVar meanget_mean(Enter_variable)288,140,148,122,835,77,420,564,0,MIDM[Sys_localindex('ACT_ID'),Sys_localindex('MONTH')]2,I,4,2,0,0,4,0,$,0,"ABBREV",0[Sys_localindex('M'),1,Sys_localindex('N'),1,Sys_localindex('ACTION'),1](vident:text, runident:optional)Get meanGives the mean result of a (multidimensional) variable stored in the Opasnet Base. The procedure is simple because it utilises the variable structure (with res_ids) derived by the get_res_id function.var a:= read_mean(vident, runident);
index o:= a[.j='cell_id'];
index j:= ['mean','sd'];
var output:= a[@.i=@o, .j = j];
a:= Get_cell_id(a);
{output[o=a]}56,216,148,122,114,117,476,428vident,runident(vident:text, runident:optional)Get sampleGives the sample result of a (multidimensional) variable stored in the Opasnet Base. The procedure is simple because it utilises the variable structure (with res_ids) derived by the get_res_id function.
Note that if the Analytica samplesize is smaller than the samplesize stored in the Opasnet Base, the extra samples will be discarded. If the samplesize is larger, the remaining rows will be null.
1) Brings the data into the right structure.
2) Chooses whether the actual result is numerical (in the Result column) or text (in the Description column).var a:= read_sample(vident, runident);
var b:= textify(Get_cell_id(read_mean(vident,runident)));
index k:= textify(a[.j='cell_id'])&'+'&textify(a[.j='obs']);
index runn:= min(a[.j='obs'],a.i)..max(a[.j='obs'],a.i);
a:= if a[.j='restext'] = '' then a[.j='result'] else a[.j='restext'];
a:= a[@.i=@k];
a:= a[k=b&'+'&runn];
a:= if max(runn)=0 then a[@runn=1] else a[@runn=@run];56,240,148,122,613,48,476,556vident,runidentVar sampleget_sample(Enter_variable)288,164,148,122,86,111,476,2242,18,155,646,307,0,MEAN[Sys_localindex('OP_EN1899'),Sys_localindex('SALMON')][Sys_localindex('OP_EN1898'),1,Sys_localindex('YEAR'),1,Sys_localindex('SALMON'),1,Sys_localindex('OP_EN1899'),1]Var run infoDescribes the runs of the defined variable. This should be made a function.var_run_info(Enter_variable)288,92,148,122,41,152,1235,369,0,MIDM[Sys_localindex('J'),Sys_localindex('I')](vident:text)Var run infoThis function checks for the newest result (according to run_id) of the variable. The function is used if the user does not define the run_id as an optional parameter in functions Read_mean and Read_sample.
PARAMETERS:
* Vident: the Ident of the variable in the Opasnet Base.
Change:
objinfo to act
run to act
add objactquery( '
SELECT obj.id AS ovj_id, obj.ident, obj.name, obj.unit, actobj.series_id, actobj.act_id, comments, act.time, act.who
FROM obj
LEFT JOIN actobj ON obj.id = actobj.obj_id
LEFT JOIN act ON act.id = actobj.act_id
WHERE obj.ident = '&chr(39)&vident&chr(39)&'
')56,56,148,132,182,31,476,56639325,65535,39321videntUse these functions to retireve data from the Opasnet Base:
* Newest_series: finds the newest upload of the object.
* Obj_act_info: Finds the action information of the object.
* Read_mean: Reads the means of each cell.
* Get_mean: Makes read_mean table into an array.
* Read_sample: Reads the whole sample.
* Get_sample: Makes read_sample table into a probabilistic array.280,294,-1168,110(a:text)QueryPerforms a query and results the standard table with columns .j and rows .i.Lap;
index i:= DBquery(Odbc,a);
index j:= dblabels(i);
dbtable(i,j)56,144,148,132,100,154,476,56639325,65535,39321aWikisNames of different wikis used.Table(Self)(
'Op_en','Op_fi','Heande','En','Fit','Erac','Beneris','Intarese','Piltti','Kantiva','Bioher','Heimtsa')[1,2,3,4,5,8,9,10,11,13,14,15]184,168,148,1665535,52427,65534[Self](a)Textifyif a = null then '' else a&''72,376,148,122,309,205,559,372a2,F,4,14,0,0,4,0,$,0,"ABBREV",0(a; file:texttype)Tablefya:= '"'&a&'"';
a:= jointext(a,a.j,';');
Writetextfile('c:\temp\'&file, a)72,352,148,132,44,303,476,32465535,45873,39321a,file2,F,4,14,0,0,4,0,$,0,"ABBREV",0Concatenation UDFsThis library contains functions to make various instances of concatenation more convenient. Concat3 thru Concat10 are generalizations of the built-in Concat function which concatenate from 3 to 10 arrays in a single call (while the built-in Concat concatenates two arrays). ConcatRows concatenates all the rows of a single array.David Kendall & Lonnie ChrismanMon, Jan 26, 2004 8:49 AMLonnieWed, Sep 05, 2007 3:23 PM48,24488,40,168,201,0,0,1,1,1,0,0,0,01,50,200,488,454,23(A1, A2, A3: ArrayType; I1, I2, I3, J: IndexType )Concat3Concatenates three arrays, A1, A2, and A3. I1, I2, and I3 are the indexes that are joined; J is the index of the new array; J usually is the concatenation of I1, I2, and I3Index I12 := Concat(I1,I2);
Concat( Concat( A1,A2,I1,I2,I12 ), A3, I12, I3, J )88,64,148,262,56,56,986,596A1,A2,A3,I1,I2,I3,J(A1, A2, A3, A4: ArrayType; I1, I2, I3, I4, J: IndexType )Concat4Concatenates four arrays, A1, A2, A3, and A4. I1, I2, I3, and I4 are the indexes that are joined; J is the index of the new array; J usually is the concatenation of I1, I2, I3, and I4.Index I12 := Concat(I1,I2);
Index I123:= Concat(I12, I3);
Concat(
Concat(
Concat( A1,A2,I1,I2,I12 ),
A3, I12, I3, I123),
A4, I123, I4, J);
192,64,148,242,30,30,986,596A1,A2,A3,A4,I1,I2,I3,I4,J0(A1, A2, A3, A4, A5, A6, A7, A8, A9: ArrayType; I1, I2, I3, I4, I5, I6, I7, I8, I9, J: IndexType)Concat9Concatenates nine arrays, A1, ..., A9. I1, ..., I9 are the indexes joined; J is the index of the new array; J usually is the concatenation of I1, ..., I9.Index I12 := Concat(I1,I2);
Index I123 := Concat(I12, I3);
Index I1234 := Concat(I123, I4);
Index I12345 := Concat(I1234, I5);
Index I123456 := Concat(I12345, I6);
Index I1234567 := Concat(I123456, I7);
Index I12345678 := Concat(I1234567, I8);
Concat(
Concat(
Concat(
Concat(
Concat(
Concat(
Concat(
Concat( A1,A2,I1,I2,I12 ),
A3, I12, I3, I123),
A4, I123, I4, I1234),
A5, I1234, I5, I12345),
A6, I12345, I6, I123456),
A7, I123456, I7, I1234567),
A8, I1234567, I8, I12345678),
A9, I12345678, I9, J);88,232,148,242,27,120,469,638A1,A2,A3,A4,A5,A6,A7,A8,A9,I1,I2,I3,I4,I5,I6,I7,I8,I9,J0(A1, A2, A3, A4, A5: ArrayType; I1, I2, I3, I4, I5, J: IndexType )Concat5Concatenates five arrays, A1, ..., A5. I1, ..., I5 are the indexes joined; J is the index of the new array; J usually is the concatenation of I1, ..., I5.Index I12 := Concat(I1,I2);
Index I123:= Concat(I12, I3);
Index I1234 := Concat(I123, I4);
Concat(
Concat(
Concat(
Concat( A1,A2,I1,I2,I12 ),
A3, I12, I3, I123),
A4, I123, I4, I1234),
A5, I1234, I5, J);88,120,148,242,160,160,986,596A1,A2,A3,A4,A5,I1,I2,I3,I4,I5,J(A1, A2, A3, A4, A5, A6: ArrayType; I1, I2, I3, I4, I5, I6, J: IndexType )Concat6Concatenates six arrays, A1, ..., A6. I1, ..., I6 are the indexes joined; J is the index of the new array; J usually is the concatenation of I1, ..., I6.Index I12 := Concat(I1,I2);
Index I123:= Concat(I12, I3);
Index I1234 := Concat(I123, I4);
Index I12345 := Concat(I1234, I5);
Concat(
Concat(
Concat(
Concat(
Concat( A1,A2,I1,I2,I12 ),
A3, I12, I3, I123),
A4, I123, I4, I1234),
A5, I1234, I5, I12345),
A6, I12345, I6, J);192,120,148,242,644,94,602,712A1,A2,A3,A4,A5,A6,I1,I2,I3,I4,I5,I6,J0(A1, A2, A3, A4, A5, A6, A7: ArrayType; I1, I2, I3, I4, I5, I6, I7, J: IndexType )Concat7Concatenates seven arrays, A1, ..., A7. I1, ..., I7 are the indexes joined; J is the index of the new array; J usually is the concatenation of I1, ..., I7.Index I12 := Concat(I1,I2);
Index I123:= Concat(I12, I3);
Index I1234 := Concat(I123, I4);
Index I12345 := Concat(I1234, I5);
Index I123456 := Concat(I12345, I6);
Concat(
Concat(
Concat(
Concat(
Concat(
Concat( A1,A2,I1,I2,I12 ),
A3, I12, I3, I123),
A4, I123, I4, I1234),
A5, I1234, I5, I12345),
A6, I12345, I6, I123456),
A7, I123456, I7, J);88,176,148,242,580,98,551,565A1,A2,A3,A4,A5,A6,A7,I1,I2,I3,I4,I5,I6,I7,J(A1, A2, A3, A4, A5, A6, A7, A8: ArrayType; I1, I2, I3, I4, I5, I6, I7, I8, J: IndexType )Concat8Concatenates eight arrays, A1, ..., A8. I1, ..., I8 are the indexes joined; J is the index of the new array; J usually is the concatenation of I1, ..., I8.Index I12 := Concat(I1,I2);
Index I123:= Concat(I12, I3);
Index I1234 := Concat(I123, I4);
Index I12345 := Concat(I1234, I5);
Index I123456 := Concat(I12345, I6);
Index I1234567 := Concat(I123456, I7);
Concat(
Concat(
Concat(
Concat(
Concat(
Concat(
Concat( A1,A2,I1,I2,I12 ),
A3, I12, I3, I123),
A4, I123, I4, I1234),
A5, I1234, I5, I12345),
A6, I12345, I6, I123456),
A7, I123456, I7, I1234567),
A8, I1234567, I8, J);192,176,148,242,12,98,561,737A1,A2,A3,A4,A5,A6,A7,A8,I1,I2,I3,I4,I5,I6,I7,I8,J0(A1, A2, A3, A4, A5, A6, A7, A8, A9, A10: ArrayType; I1, I2, I3, I4, I5, I6, I7, I8, I9, I10, J: IndexType)Concat10Concatenates ten arrays, A1, ..., A10. I1, ..., I10 are the indexes joined; J is the index of the new array; J usually is the concatenation of I1, ..., I10.Index I12 := Concat(I1,I2);
Index I123 := Concat(I12, I3);
Index I1234 := Concat(I123, I4);
Index I12345 := Concat(I1234, I5);
Index I123456 := Concat(I12345, I6);
Index I1234567 := Concat(I123456, I7);
Index I12345678 := Concat(I1234567, I8);
Index I123456789 := Concat(I12345678, I9);
Concat(
Concat(
Concat(
Concat(
Concat(
Concat(
Concat(
Concat(
Concat( A1,A2,I1,I2,I12 ),
A3, I12, I3, I123),
A4, I123, I4, I1234),
A5, I1234, I5, I12345),
A6, I12345, I6, I123456),
A7, I123456, I7, I1234567),
A8, I1234567, I8, I12345678),
A9, I12345678, I9, I123456789),
A10, I123456789, I10, J);192,232,148,242,542,93,632,744A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,I1,I2,I3,I4,I5,I6,I7,I8,I9,I10,J0(A : ArrayType ; RowIndex,ColIndex,ResultIndex : IndexType)ConcatRows (A,I,J,K)Takes an array, A indexed by RowIndex & ColIndex, and concatenates each row, henceforth flattening the array by one dimension. The result is indexed by ResultIndex, which must be an index with size(RowIndex) * size(ColIndex) elements.index L := [ identifier of RowIndex, identifier of ColIndex, "val"];
slice(Mdarraytotable(A,ResultIndex,L),L,3)320,64,164,242,567,75,478,348A,RowIndex,ColIndex,ResultIndexODBC LibraryLonnieThu, Sep 11, 1997 2:15 PMLonnieTue, Feb 05, 2008 10:03 AM48,24496,96,152,201,1,1,1,1,1,0,0,0,01,20,272,499,462,17Arial, 13(A:ArrayType;I:IndexType;L:IndexType;row:IndexType;dbTableName)InsertRecSqlGenerates the SQL "INSERT INTO" statement for one line of table A. A is a 2-D table indexed by rows I and columns L. L's domain serves as the column names in the database table. dbTableName is the name of the table in the database. The result begins with two semi-colons, since it will be used with an SQL statement preceeding it.
29.8.2008 Jouni Tuomisto
I added the parameter IGNORE because it ignores rows that would cause duplicate-key violations. This way, there is no need to check for e.g. existing locations of new indices.
6.1.2009 Jouni Tuomisto
I changed the A[I=row] to A[@I=@row] because the original function does not work correctly, if there are non-unique rows in the index.(';;INSERT IGNORE INTO ' & dbTableName & '(' & JoinText(L,L,',') & ') VALUES (' & Vallist(A[@I=@row],L)) & ') '184,32,152,242,591,203,487,469A,I,L,row,dbTableName(V:ArrayType;I:IndexType)ValListTakes a list of values, and returns a string which the concatenation of each value, separated by commas, and with each value quoted.JoinText( '''' & V & '''', I, ',')72,32,052,242,642,360,476,224V,I1,F,4,14,0,0(Tabl:ArrayType;RowIndex:IndexType;LabelIndex:IndexType;dbTableName)WriteTableSql(Table,Rows,Labels,dbTableName)Returns the SQL that will write the table to the database table.
This can be used as the second argument to DBWrite.
This SQL statement replaces the entire contents of an existing table with the new data.'DELETE FROM '& Dbtablename & JoinText(Insertrecsql(Tabl, Rowindex, Labelindex, Rowindex, Dbtablename),RowIndex)328,32,188,242,728,341,510,476Tabl,RowIndex,LabelIndex,dbTableName(Tabl:ArrayType;RowIndex:IndexType;LabelIndex:IndexType;dbTableName)AppendTableSql(Table,Rows,Labels,dbTableName)Returns the SQL that will write the table to the database table.
This can be used as the second argument to DBWrite.
This SQL statement replaces the entire contents of an existing table with the new data.JoinText(Insertrecsql(Tabl, Rowindex, Labelindex, Rowindex, Dbtablename),RowIndex)328,88,188,242,559,127,510,476Tabl,RowIndex,LabelIndex,dbTableName(in, table; cond:texttype)Findidindex L:= in[.i=unique(in, in.i)];
var a:= if (L&' ') = (table[.j=cond]&' ') then table[.j='id'] else 0;
a:= textify(sum(a, table.i));
a[.L=in]72,280,148,122,636,101,494,519in,table,cond(var, table)Writeif size(var)>0 then dbwrite((if platform = 'Lumina AWP' then 'Driver={MySQL ODBC 3.51 Driver};Server=193.167.179.97' else 'Driver={MySQL ODBC 5.1 Driver};Server=10.66.10.102')&';Database=opasnet_base;User=result_writer; Password='&writerpsswd&';Option=3'
, appendtablesql(var,var.i, var.j, table&' '))72,304,148,122,751,65,501,45765535,45873,39321var,tableOpasnet usernameThe username for Opasnet wiki'Add your Opasnet username'72,176,148,221,1,1,1,1,1,0,0,0,02,102,90,476,398[Formnode Username1]52425,39321,65535Opasnet passwordThe user's password for Opasnet wiki.'Add password'72,232,148,221,1,1,1,1,1,0,0,0,02,102,90,476,520[Formnode Password1]52425,39321,65535ODBCvar a:= if platform='Lumina AWP' then 'Driver={MySQL ODBC 3.51 Driver};Server=193.167.179.97' else 'Driver={MySQL ODBC 5.1 Driver};Server=10.66.10.102';
a&';Database=opasnet_base;User=result_reader; Password=ora4ever;Option=3'184,136,148,121,1,0,1,1,1,0,,0,2,180,61,508,4202,56,66,918,303,0,MIDM(var, table)Write1if size(var)>0 then appendtablesql(var,var.i, var.j, table&' ')72,328,148,132,284,58,476,224var,table'Add the writer password'184,200,052,121,1,1,1,1,1,0,0,0,02,163,375,476,224[Formnode Writerpsswd1]52425,39321,65535PlatformChoice(Self,2,False,1)72,136,148,12[Formnode Platform1]52425,39321,65535['Lumina AWP','THL computer']Object infosubtable(Object_info[Info=Info_table, @n_vars=1])184,248,148,202,102,90,476,3732,174,220,402,256,0,MIDM2,599,363,416,303,0,MIDM[Formnode Object_info1]52425,39321,65535[N_vars,Info_table][Self]['','','']Info_table['ident','name','unit','# explanation cols','observation index','probabilistic?']184,280,148,131,1,1,1,1,1,0,0,0,0Do nextThis is a temporary node that is ovewritten when Upload_data and Upload_results are being run.''296,352,148,16Replace data?Choice(Self,1,False)296,256,148,222,102,90,476,384[Formnode Replace_data_1]52425,39321,65535['Yes, replace previous data (if any)','No, there is previous data and I want to append']Enter anacode"index vehicle_type:= ['Bus','Minibus','Car d','Car g'];
var Car_maintenance:= Triangular( 0.03, 0.058, 0.086 );
var Fuel_price:=
(var a:= 0.95*triangular(0.8,1,1.2);
var b:= 1.22*triangular(0.8,1,1.2);
array(Vehicle_type,[a,a,a,b]));
var Fuel_consumption:=
(var a:= (8.7/100)*Triangular(0.75,1,1.25);
var b:= (5.7/100)*Triangular(0.75,1,1.25);
var c:= (8/100)*Triangular(0.5,1,1.5);
a:= array(Vehicle_type,[a,a,b,c]);
);
fuel_price*fuel_consumption+car_maintenance"496,280,148,24[Formnode Enter_anacode1]52425,39321,65535Enter anacode0736,432,1160,561,0,0,1,0,0,0,182,0,152425,39321,65535Enter_anacodeExample codeindex vehicle_type:= ['Bus','Minibus','Car d','Car g'];
var Car_maintenance:= Triangular( 0.03, 0.058, 0.086 );
var Fuel_price:=
(var a:= 0.95*triangular(0.8,1,1.2);
var b:= 1.22*triangular(0.8,1,1.2);
array(Vehicle_type,[a,a,a,b]));
var Fuel_consumption:=
(var a:= (8.7/100)*Triangular(0.75,1,1.25);
var b:= (5.7/100)*Triangular(0.75,1,1.25);
var c:= (8/100)*Triangular(0.5,1,1.5);
a:= array(Vehicle_type,[a,a,b,c]);
);
fuel_price*fuel_consumption+car_maintenance496,216,148,24Code nodeevaluate(Enter_anacode)496,336,148,162,104,114,416,303,0,SAMP[Undefined,Sys_localindex('VEHICLE_TYPE'),Undefined,Undefined,Undefined,1][1,0,0,0](a; col:indextype)Removecolvar b:= jointext(col,col,',');
b:= Textreplace(b, 'obs,','');
index j:= splittext(Textreplace(b, ',result',''),',');
a:= a[col=j]72,472,148,122,372,94,476,296a,colBlocksize200k296,296,148,12[Formnode Blocksize1]CardinalcellThe largest id values for the cell table in Opasnet Base.var a:= query('SELECT MAX(id) AS id FROM cell ');
max(max(a,a.i),a.j)296,104,148,132,634,394,476,3322,193,270,416,303,0,MIDM2,70,351,229,303,0,MIDM39325,65535,393212,I,4,2,0,0,4,0,$,0,"ABBREV",0(param1: optional)Objquery('SELECT * FROM obj ')296,136,148,122,102,90,476,30539325,65535,39321param1(a)Cardinals1a:= query('SELECT MAX(id) AS id FROM '&a&' ');
max(max(a,a.i),a.j)296,160,148,1339325,65535,39321aBlocksize0784,304,196,131,0,0,1,0,0,0,72,0,1BlocksizeObject infosubtable(Object_info[Info=Info_model, @n_vars=1])184,328,148,202,102,90,476,3732,174,220,402,256,0,MIDM2,599,363,416,303,0,MIDM[Formnode Object_info2]52425,39321,65535[N_vars,Info_model][Self]['','','']Info_model['Analytica identifier','ident','name','unit','observation name','probabilistic?']184,360,148,131,1,1,1,1,1,0,0,0,0Explanationsvar node:= selecttext(data_source,1,1)='2';
var N_indices:= Object_info_for_lap[Info='# explanation cols'];
N_Indices:= if node then size(Standard_table.j)-1 else N_indices184,408,152,162,33,586,476,224Provide data in the format you selected.264,172,-1256,121,0,0,1,0,1,0,,0,65535,65532,19661Data table1412,436,1112,131,0,0,1,0,0,0,72,0,1Standard_tableObject info0396,396,1116,121,0,0,1,0,0,0,72,0,152425,39321,65535Object_info1_3_1