Tag Archives: Uncategorized

NHANES R data parser

NHANES is a great ressource for doing epidemiological research. As the NIH website provides only data import for commercial software here is my rewrite in R. First load from their site

adult.exe
youth.exe
lab.exe
lab2.exe
exam.exe

put everything in one directory and expand the self-extracting archives. Then create from each SAS file a new variable content file that will only contain variable name and tab separated start position in the .dat file. Adult.var for example would read like this:

SEQN 1
DMPFSEQ 6
DMPSTAT 11
DMARETHN 12
DMARACER 13
...
HAZNOK5R 3345

Then start the following R job with the datasets and variables that you are interested in

 1:
 2:
 3:
 4:
 5:
 6:
 7:
 8:
 9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
70:
    separ  <- "--------------------------------------------------------------------------"
    ti     <- c("NHANES R data parser")
    au     <- c("WJST")
    # 07Feb06 revised data
    direct <- c("D:/data/0702work/")
    dt     <- paste("run",date())
    head   <- cat(separ,"\n",ti,"\n",au,"\n",dt,"\n",separ,"\n",sep="")

    #------------------------------------|        |-----------------------------------

    read.nhanes<-function(option1,option2) {
        # read variable positions
        var <- read.table(paste(direct,option1,".var",sep=""),header=FALSE,sep="\t",dec=",",na.strings="")

        # the variables we are interested in
        var.list <- option2

        # i holds the rownames where line positions may be found
        i <- as.numeric(row.names(var[var[,1] %in% var.list,]))

        # k holds the picklist where to find the variable content
        k <- NA
        for (j in 1:(length(i)-1)) {
            k[(j*2)-1] <- var[i[j]+1,2] - var[i[j],2]
            if ( i[j+1] == i[j]+1 ) {
                k[j*2] <- NA
            }
            else {
                k[j*2] <- -(var[i[j+1],2] - var[i[j],2] - k[(j*2)-1])
            }
        }
        j <- j+1
        k[(j*2)-1] <- var[i[j]+1,2] - var[i[j],2]
        k[(j*2)] <- -(var[dim(var)[1],2] - var[i[j],2])
        k <- k[complete.cases(k)]

        # now read data, label and save
        ds <- read.fwf(paste(direct,option1,".dat",sep=""), k, header = FALSE, sep = "\t", as.is = FALSE, skip = 0)
        colnames(ds) <- var[var[,1] %in% var.list,1]
        ds
    }

    #------------------------------------|        |-----------------------------------

    youth <- read.nhanes("youth",c("SEQN","HFC6D","HFC6D1","DMARACER","HSSEX","HSAITMOR","HYE1G","HYE1I","HYG8","HYG13A","HYG9","HSDOIMO"))
    adult <- read.nhanes("adult",c("SEQN","HFC6D","HFC6D1","DMARACER","HSSEX","HSAITMOR","HAC1E","HAC1H","HAL6","HAL11A","HAD1","HAD5R","HAL7","HSDOIMO"))

    nc <- c(colnames(adult),colnames(youth)[!colnames(youth) %in% colnames(adult)])
    both <- data.frame(matrix( nrow=nrow(adult)+nrow(youth), ncol=length(nc), byrow=TRUE ,NA))
    colnames(both) <- c(nc)

    for (i in nc) {
        if (i %in% colnames(adult)) {
            both[1:nrow(adult),i] <- adult[,i]
        }
        if (i %in% colnames(youth)) {
            both[(nrow(adult)+1):nrow(both),i] <- youth[,i]
        }
    }
    exam <- read.nhanes("exam",c("SEQN","ALPCATWL","ALPCATWW","ALPMITWL","ALPMITWW","ALPRYEWL","ALPRYEWW","ALPPEAWL","ALPPEAWW","ALPRAGWL","ALPRAGWW","MPPB2","BMPHT","BMPWT","BMPBMI"))
    nhanes <- merge( both, exam, by=c("SEQN"), all=TRUE)

    lab <- read.nhanes("lab",c("SEQN","WCPSI","LMPPCNT","MOPPCNT","GRPPCNT","LMP","MOP","LMPDIF","MOPDIF","EOP","FRP","SCP","SCPSI","PSP","PSPSI","DMPCREGN","WTPFQX6","TEP","AHP","HBP","SSP","SAP","HCP","DHP","H1P","H2P","RUP","RUPUNIT","VRP","TOP"))
    nhanes <- merge( nhanes, lab, by=c("SEQN"), all=TRUE)

    lab2 <- read.nhanes("lab2",c("SEQN","VDPSI"))
    nhanes <- merge( nhanes, lab2, by=c("SEQN"), all=TRUE)

    save(nhanes, file=paste(direct,"nhanes.Rdata",sep=""), compress=TRUE)

Quick database edit from webbrowser

Frequently, I need to change only one single field in a database. For that purpose I am using a short php script – it basically outputs all fields and highlights them with a javascript popup where you can do any changes (or even delete a row) while a “return” writes the change back to the database.

sc_edit.png

You will immediately see, where the script needs some adaptation – all the trick is in line 28.
line 2: your database
line 5,9…: your mytable
line 23: your columns

 1:
 2:
 3:
 4:
 5:
 6:
 7:
 8:
 9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
34:
<?php
$db 
= @mysql_connect('server','user','password');
if (
$_GET['change']!='') {
    if (
$_GET['value']=='d') {
        
$sql 'DELETE FROM mytable
            WHERE mytable.Index=' 
$_GET['id'] . ' LIMIT 1';
    }
    else {
        
$sql 'UPDATE mytable ';
        if (
is_numeric($_GET['value'])) {
            
$sql $sql 'SET mytable.' $_GET['field'] . '=' $_GET['value'];
        }
        else {
            
$sql $sql 'SET mytable.' $_GET['field'] . '="' $_GET['value'] . '"';
        }
        
$sql $sql ' WHERE mytable.Index=' $_GET['id'];
    }
    
$result mysql_db_query('test',$sql);
}
$sql 'SELECT DISTINCTROW *
    FROM mytable'
;
$result mysql_db_query('test',$sql);
$sc_vars = array("FirstCol","SecndCol","ThrdCol");
echo 
'<table>';
while(
$row mysql_fetch_array($result)){
    echo 
'<tr>';
    for (
$i=0;$i<9;$i++) {
        echo 
'<td><a href="javascript:void(str=prompt(\'(d)el\',\'' $row[$sc_vars[$i]] . '\'));if(str)void(window.location.href=\'liste.php?change=T&field=' $sc_vars[$i] . '&id=' $row['Index'] . '&value=\'+escape(str));">' $row[$sc_vars[$i]] . '</td></a>';
    }
    echo 
'</tr>';
}
echo 
'</table>';
?>

R parallel computing

Following several unsuccessful attempts to implement a parallel computing platform for R statistical software, I am showing here my current approach that is largely influenced by a recent paper on cluster programming in c’t 6/06 by Oliver Lau (sorry, no online version). My primary interest is with the R library snow (or snow-ft) that offers the function clusterApplyLB. This function is all I need for my R programs.
Now it gets more complicated: library(snow) depends on library(Rmpi): Hao Yu has an excellent description at www.stats.uwo.ca/faculty/yu/Rmpi how to set up the mpi layer with MPICH2. I am currently experimenting with DeinoMPI a closely related high performance Windows interface. According to its developer David Ashton it has the following advantages

First, DeinoMPI does not require MPI applications to be started by mpiexec in order to call MPI_Comm_spawn so you could load Rmpi from the Rgui.exe without having to bother with calling mpiexec. Second, DeinoMPI loads the user profile when starting applications so if you query the user’s temporary directory you will get the user specific path and not the Windows system temp directory. Third, DeinoMPI handles arguments with spaces correctly if you quote them so you can pass environment variables with spaces in them. Fourth, DeinoMPI allows you to use the MPI Info object to pass extra options to MPI_Comm_spawn like drive mappings. So you could create an MPI_Info object and set wdir=z:\ and map=z:\\server\share. Then pass this info object in with the MPI_Comm_spawn command and you could map a network drive and launch an executable from this drive.

So far the Rmpi package is compiled for MPICH2 (not DeinoMPI) so it won’t run with only DeinoMPI installed but there is a good chance that this will change in the near future.
Further useful references are in the R newsletter 2003, p21 cran.r-project.org/doc/Rnewsand a paper in the UW Biostatistics Working Paper Series on “Simple Parallel Statistical Computing in R” by Anthony Rossini and LukeTierney.
BTW, haplotypes of the hapmap project were computed on a 110 node cluster provided by both Peter Donnelly’s Mathematical Genetics Group www.stats.ox.ac.uk based at the Oxford Centre for Gene Function and by a 128 node compute cluster provided by the Oxford e-Science Centre e-science.ox.ac.uk as part of the National Grid Service[to be cont’d…].

mpi1.png

Geo IP identification

The Geo IP database is available at Maxmind and allows to trace your home city from IP addresses. Here is a quick and dirty script to upload the Geo IP data into MySQL:

 1:
 2:
 3:
 4:
 5:
 6:
 7:
 8:
 9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
23:
@echo off
rem set path to mysql binaries
set i=C:\Programme\XAMPP\xampp\mysql\bin\

rem SQL statement
echo CREATE TABLE GeoLiteCity (locId DOUBLE,country CHAR(15),region CHAR(15),city CHAR(15),postalCode CHAR(15),latitude DOUBLE,longitude DOUBLE,dmaCode DOUBLE,areaCode DOUBLE); >1.SQL
echo CREATE TABLE GeoLiteCityBlocks (startIpNum DOUBLE,endIpNum DOUBLE,locId DOUBLE); >1.SQL

rem create database
%i%mysql <1.SQL
%i%mysql <2.SQL

rem drop SQL statement
del 1.SQL>nul
del 2.SQL>nul

rem import data
%i%mysqlimport --fields-terminated-by="," --fields-optionally-enclosed-by="\"" --lines-terminated-by="\n" --host=localhost --user=root test d:\GeoLiteCity.csv
%i%mysqlimport --fields-terminated-by="," --fields-optionally-enclosed-by="\"" --lines-terminated-by="\n" --host=localhost --user=root test d:\GeoLiteCityBlocks.csv

pause
exit

I would also put an index on loc_id. Finally the database should be available as

SELECT city
FROM GeoLiteCity INNER JOIN GeoLiteCityBlocks ON GeoLiteCity.locID = GeoLiteCityBlocks.locID
WHERE $myIP >= startIpNum AND $myIP <= endIpNum;

where $myIP is calculated as

substr($_SERVER['REMOTE_ADDR'],0,3) * 16777216 +
substr($_SERVER['REMOTE_ADDR'],4,3) * 65536 +
substr($_SERVER['REMOTE_ADDR'],8,3) * 256 +
substr($_SERVER['REMOTE_ADDR'],12,3)

WordPress as CMS

I have read many useful (and also some less useful) comments how to squeeze WordPress to work as a CMS.

I did not want to make any major changes to scripts that would be lost after an upgrade. I did not want to have extra plugins to change home (for example by creating an overriding home.cfm). I did not want to have any new categories. I did not want to change permalink structure. I still need my directory plugin to work, I still need the blog (some redirects even loose the blog address!) and I wanted to keep the RSS feed.

After several hours I came up with an very simple solution: Take a standard page and rename its title and slug to “Home” – assign a special “Home” template – redirect htaccess to this page. The only trick is to make the “Home” template work: it is basically a copy of the index.cfm in your WordPress theme directory where the line calling loop.php is being replaced with a slightly modifed loop code.

myblog.php

 1:
 2:
 3:
 4:
 5:
 6:
 7:
 8:
 9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
28:
<?php
/*
Template Name:MyBlog
*/
?>

<?php get_header(); ?>
<?php get_sidebar
(); ?>

<div id="primary">
<?php query_posts('showposts=5'); ?>
<?php 
while(have_posts()) : the_post(); if(!($first_post == $post->ID)) : ?>
    <div class="entry">
        <div class="post-meta">
            <h2 class="post-title" id="post-<?php the_ID(); ?>"><?php if(!is_single()) { ?><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>"><?php the_title(); ?></a><?php } else { the_title(); } ?></h2>
            <p class="post-metadata"><?php the_time('l, F jS'?><?php if(!get_option('tarski_hide_categories')) { ?> in <?php the_category(', '); ?><?php ?><?php /* If there is more than one author, show author's name */ $count_users $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->usermeta WHERE `meta_key` = '" $table_prefix "user_level' AND `meta_value` > 1"); if ($count_users 1) { ?> by <?php the_author_posts_link(); } ?> | <?php comments_popup_link('No comments''1 comment''% comments''''Comments closed'); ?><?php edit_post_link('Edit',' (',')'); ?></p>
        </div>
        <div class="post-content">
            <?php the_content(' more &raquo;&raquo;&raquo;'); ?>
            <p class="tagdata"><?php _e('Tags: '); UTW_ShowTagsForCurrentPost("commalist"?> | <?php _e('Trackback: '); ?>
            <a href="<?php the_permalink() ?>trackback/">link</a></p>
        </div>
    </div>
<?php endif; endwhile; ?>
</div>

<?php get_footer(); ?>

A simple POP answering machine

A script that I used for many years…

popreply.cfm

  1:
  2:
  3:
  4:
  5:
  6:
  7:
  8:
  9:
 10:
 11:
 12:
 13:
 14:
 15:
 16:
 17:
 18:
 19:
 20:
 21:
 22:
 23:
 24:
 25:
 26:
 27:
 28:
 29:
 30:
 31:
 32:
 33:
 34:
 35:
 36:
 37:
 38:
 39:
 40:
 41:
 42:
 43:
 44:
 45:
 46:
 47:
 48:
 49:
 50:
 51:
 52:
 53:
 54:
 55:
 56:
 57:
 58:
 59:
 60:
 61:
 62:
 63:
 64:
 65:
 66:
 67:
 68:
 69:
 70:
 71:
 72:
 73:
 74:
 75:
 76:
 77:
 78:
 79:
 80:
 81:
 82:
 83:
 84:
 85:
 86:
 87:
 88:
 89:
 90:
 91:
 92:
 93:
 94:
 95:
 96:
 97:
 98:
 99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
127:
<!--
CF_POPREPLY 1.02
Cold Fusion 3.0 POP mail answering machine
(c) 1 Dec 97 by Matthias Wjst (wjst@gsf.de)
bugfixes in 1.01 by 15 Jan 98
 * time stamp of sender may wrong, therefore only absolute time checking
 * reading blacklist now in loop
 * added to blacklist listproc
 * better email recognition
-->

<CFIF #ParameterExists(Attributes.LastInterval)#
  AND #ParameterExists(Attributes.LastUnit)#
  AND #ParameterExists(Attributes.PopServer)#
  AND #ParameterExists(Attributes.PopUser)#
  AND #ParameterExists(Attributes.PopUserPassword)#
  AND #ParameterExists(Attributes.WorkingDir)#
  AND #ParameterExists(Attributes.Replytext)#>

    <CFIF #Attributes.LastUnit# is "Day">
        <CFSET #unit#="D">
    <CFELSEIF #Attributes.LastUnit# is "Hour">
        <CFSET #unit#="H">
    <CFELSEIF #Attributes.LastUnit# is "Minute">
        <CFSET #unit#="N">
    <CFELSEIF #Attributes.LastUnit# is "Second">
        <CFSET #unit#="N">
    <CFELSE>
        error: correct time interval needed
    <CFABORT>
    </CFIF>
<CFELSE>
error: more parameters needed
<CFABORT>
</CFIF>

<CFFILE Action="WRITE"
File="#Attributes.WorkingDir#popreply.txt"
OUTPUT="#Attributes.Replytext#">

<CFIF #FileExists("#Attributes.WorkingDir#popreply.cmd")#>
    <CFFILE Action="DELETE"    File="#Attributes.WorkingDir#popreply.cmd">
</CFIF>

<CFPOP SERVER=#Attributes.PopServer#
    USERNAME=#Attributes.PopUser#
    PASSWORD=#Attributes.PopUserPassword#
    ACTION=GETALL
    NAME="Sample">

<CFIF #ParameterExists(ACT_MAILS)#>

    <CFLOOP QUERY="Sample">

        <CFX_POP3DATE DateMail=#Sample.date#>

        <CFIF #DateDiff(unit,DateMail,Now())# lt #(int(Attributes.LastInterval)#>

            <CFIF #FindNoCase("gateway",Sample.from,1)# is "0"
              AND #FindNoCase("dispatch",Sample.from,1)# is "0"
              AND #FindNoCase("prts",Sample.from,1)# is "0"
              AND #FindNoCase("notesys",Sample.from,1)# is "0"
              AND #FindNoCase("netlib",Sample.from,1)# is "0"
              AND #FindNoCase("MAILER-DAEMON",Sample.from,1)# is "0"
              AND #FindNoCase("root",Sample.from,1)# is "0"
              AND #FindNoCase("0000-Admin",Sample.from,1)# is "0"
              AND #FindNoCase("digi-info",Sample.from,1)# is "0"
              AND #FindNoCase("digi-request",Sample.from,1)# is "0"
              AND #FindNoCase("ftp-list",Sample.from,1)# is "0"
              AND #FindNoCase("ftp-mail",Sample.from,1)# is "0"
              AND #FindNoCase("ftpadmin",Sample.from,1)# is "0"
              AND #FindNoCase("ip-addr",Sample.from,1)# is "0"
              AND #FindNoCase("ip-oper",Sample.from,1)# is "0"
              AND #FindNoCase("listproc",Sample.from,1)# is "0"
              AND #FindNoCase("listserv",Sample.from,1)# is "0"
              AND #FindNoCase("mail-server",Sample.from,1)# is "0"
              AND #FindNoCase("daemon",Sample.from,1)# is "0"
              AND #FindNoCase("mirror",Sample.from,1)# is "0"
              AND #FindNoCase("netserv",Sample.from,1)# is "0"
              AND #FindNoCase("responder",Sample.from,1)# is "0"
              AND #FindNoCase("win-ip-admins",Sample.from,1)# is "0"
              AND #FindNoCase("mirror",Sample.from,1)# is "0">

                <CFSET Start = REFind("[-a-zA-Z0-9\._]+@([-a-zA-Z0-9_]+\.)+([a-zA-Z]{2,3})", from, 1)>
                <CFIF Start eq "0">
                    <CFSET Start = "1">
                </CFIF>
                <CFSET Stop = REFind("[^-a-zA-Z0-9@_\.]", from, Start)>
                <CFIF #Stop# is not "0">
                    <CFLOOP CONDITION="REFind('[A-Za-z]', Mid(from, Stop, 1)) GT 0">
                        <CFSET Stop = Stop - 1>
                    </CFLOOP>
                    <CFSET email= Mid(from, Start, Stop - Start)>
                <CFELSE>
                    <CFSET email= mid(from, Start, len(from))>
                </CFIF>

                <CFIF #FileExists("#Attributes.WorkingDir#popreply.black")#>
                    <CFFILE Action="READ"
                    File="#Attributes.WorkingDir#popreply.black"
                    Variable="blacklist">
                <CFELSE>
                    <CFSET #blacklist#="">
                </CFIF>

                <CFIF #FindNoCase(email,blacklist,1)# is "0">

                    <CFFILE Action="APPEND"
                    File="#Attributes.WorkingDir#popreply.black"
                    OUTPUT="#email#">

                    <CFFILE Action="APPEND"
                    File="#Attributes.WorkingDir#popreply.log"
                    OUTPUT="blat popreply.txt -t #email# -s #chr(34)#Re: #left(Sample.subject,19)##chr(34)#">
                </CFIF>

            </CFIF>
    
        </CFIF>
    </CFLOOP>

    <CFIF #FileExists("#Attributes.WorkingDir#popreply.cmd")#>
        <CFX_SHELLEXEC FILE="popreply.cmd" DIRECTORY="#Attributes.WorkingDir#">
    </CFIF>

</CFIF>

A low-cost system for a PDF literature archiv II

With Google Desktop you will have the archiving capabilities that would have cost 10,000$ only a few years ago. Things become more tricky if want to share your archive on a workgroup level. Here is an idea that I have found in the German Laborjournal

  1. Shut down your firewall
  2. Install the freeware DNKA available from dnka.com. It will act as a web server by interacting as a layer between Google Desktop Search and the user
  3. use http://127.0.0.1:4664 to configure your webserver and offer http://yourIP to your working group.

dnka.png
Need more information? Check geekzone and a nice indexer interface at TweakGDS.

Snapshot of your working directory

We need two open source programs: Gzip is a compression utility with a high compression rate and free from patented algorithms. GnuPG is a complete and free encryption solution to protect confidential communication and digitally stored information. Create a /backup directory in each of your working directories.

backup.cmd

 1:
 2:
 3:
 4:
 5:
 6:
 7:
 8:
 9:
10:
11:
12:
13:
14:
15:
16:
18:
@echo off

rem where the zip file resides
set p=d:\programme\system\

rem where GnuPG resides
set g=c:\Programme\GnuPG\

rem compress files
set z=%date:~6,4%%date:~3,2%%date:~0,2%.zip
%p%zip %cd%\backup\%z% %cd%\*.*

rem sign backup
%g%gpg -b -armor %cd%\backup\%z%

pause
exit


Write the above code in a file, put it somewhere in your path and assign an icon (I am running this from the buttonbar of TotalCommander (R)). One click – and your current directory is being saved as zip file and signed with your key.

A database driven epidemiological questionnaire

During a past project I moved from a paper-based questionnaire directly to a database version. This has the advantage of generating at the same time an online version (with some interface scripts) and an offline version (by using a serial paper printing).

The whole package is written in standard HTML with some Cold Fusion(R) extension (where you need the Railo(R) or Blue Dragon(R) interpreter too).

Download (md5:1922125540), create a datasource “aerzte” and attached the included Access(R) database. Then run the following script in your browser.

frage.cfm

  1:
  2:
  3:
  4:
  5:
  6:
  7:
  8:
  9:
 10:
 11:
 12:
 13:
 14:
 15:
 16:
 17:
 18:
 19:
 20:
 21:
 22:
 23:
 24:
 25:
 26:
 27:
 28:
 29:
 30:
 31:
 32:
 33:
 34:
 35:
 36:
 37:
 38:
 39:
 40:
 41:
 42:
 43:
 44:
 45:
 46:
 47:
 48:
 49:
 50:
 51:
 52:
 53:
 54:
 55:
 56:
 57:
 58:
 59:
 60:
 61:
 62:
 63:
 64:
 65:
 66:
 67:
 68:
 69:
 70:
 71:
 72:
 73:
 74:
 75:
 76:
 77:
 78:
 79:
 80:
 81:
 82:
 83:
 84:
 85:
 86:
 87:
 88:
 89:
 90:
 91:
 92:
 93:
 94:
 95:
 96:
 97:
 98:
 99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
195:
<CFINCLUDE TEMPLATE="top.cfm">

<CFSET #cookie_id#=3>

<CFSET #group#=1>
<CFIF #ParameterExists(URL.page)#>
    <CFIF #IsNumeric(URL.page)#>
        <CFSET #group#=#URL.page#>
    </CFIF>
<CFELSE>
    <CFABORT>
</CFIF>

<CFOUTPUT>
<div id="subnav">
<h3 class="selected">=#cookie_id#</h3>
<ul>
    <li <CFIF #group# is 1>class=selected</CFIF>>
<a href=frage.cfm?page=1>Block 1</a></li>
    <li <CFIF #group# is 2>class=selected</CFIF>>
<a href=frage.cfm?page=2>Block 2</a></li>
    <li <CFIF #group# is 3>class=selected</CFIF>>
<a href=frage.cfm?page=3>Block 3</a></li>
</ul>
</div>
</CFOUTPUT>

<!--- ------------------------------------------ --->

<div id="content" class="afternav">

<CFQUERY NAME="Get" DATASOURCE="aerzte">
SELECT qry.*
FROM qry
WHERE qry.group=#group# AND qry.active=1
ORDER BY qry.id ASC;
</CFQUERY>

<CFQUERY NAME="Already" DATASOURCE="aerzte">
SELECT qry_answer.*
FROM qry_answer
WHERE qry_answer.partcp_id=#cookie_id# AND qry_answer.active=1;
</CFQUERY>

<CFSET #error#="">
<CFSET #blacklist#= "#chr(59)#,#chr(34)#,#chr(39)#,%22,--">

<CFOUTPUT QUERY="Get">
    <CFIF #PARAMETEREXISTS(fieldnames)#>
        <CFIF #ReFindNoCase(qryvar,fieldnames)# is not 0>
            <CFSET #sc#=#Evaluate("FORM.#qryvar#")#>
            <CFLOOP index="i" list="#blacklist#">
                <CFIF #Find(i,sc)# is not 0>
                    <CFSET #msg# = "not allowed">
                    <CFSET #error# = #error# & "|" & #qryvar# & ":" & #msg#>
                </CFIF>
            </CFLOOP>
            <CFIF #sc# is "" AND #qryvar# is not "terminator">
                <CFSET #msg# = "missing">
                <CFSET #error# = #error# & "|" & #qryvar# & ":" & #msg#>
            </CFIF>
            <CFIF #Find("int",qrytype)# is not 0 AND NOT #Isnumeric(sc)#>
                <CFSET #msg# = "not numeric">
                <CFSET #error# = #error# & "|" & #qryvar# & ":" & #msg#>
            </CFIF>
            <CFIF #Find("varchar",qrytype)# is not 0 AND NOT #IsSimpleValue(sc)#>
                <CFSET #msg# = "not character">
                <CFSET #error# = #error# & "|" & #qryvar# & ":" & #msg#>
            </CFIF>
            <CFIF #Find("datetime",qrytype)# is not 0 AND NOT #IsDate(sc)#>
                <CFSET #msg# = "not a date">
                <CFSET #error# = #error# & "|" & #qryvar# & ":" & #msg#>
            </CFIF>
            <CFIF #GetToken(valid,1,":")# is "range">
                <CFIF #sc# lt #GetToken(valid,2,":")# OR #sc# gt #GetToken(valid,3,":")#>
                    <CFSET #msg# = "out of range ">
                    <CFSET #error# = #error# & "|" & #qryvar# & ":" & #msg#>
                </CFIF>
            </CFIF>
        <CFELSE>
            <CFSET #msg#="missing">
            <CFSET #error# = #error# & "|" & #qryvar# & ":" & #msg#>
        </CFIF>
    </CFIF>
</CFOUTPUT>

<!--- ------------------------------------------ --->

<CFIF #error# is not "" OR NOT #PARAMETEREXISTS(fieldnames)#>

    <CFOUTPUT>
    <FORM method=POST action="frage.cfm?page=#URL.page#">
    
    Block #group#<BR>
    </CFOUTPUT>
    
    <table width=400 border=0 bgcolor="lightyellow">
    
    <CFOUTPUT QUERY="Get">
    <tr><td colspan=3><hr size=1></td></tr>
    <tr>
    <td width=200>#text#</td>
    <td width=200 align=right <CFIF ListContains(error,qryvar,"|") gt 0>bgcolor=orange</CFIF>>
    
    <CFSET #ar#=""> 
    <CFIF #ListFind(ValueList(Already.qry_id),id)# gt 0>
        <CFSET #ar#=#ListGetAt(ValueList(Already.response),ListFind(ValueList(Already.qry_id),id))#>
    </CFIF>

    <CFIF #PARAMETEREXISTS(fieldnames)#>
        <CFIF #ReFindNoCase(qryvar,fieldnames)# is not 0>
            <CFSET #ar#=#Evaluate("FORM.#qryvar#")#>
        </CFIF>
    </CFIF>
    <CFIF #www# is "radio">
        <CFLOOP INDEX="i" LIST="#response#" DELIMITERS=":">
            <CFSET #s#="">
            <CFIF #i# is #ar#>
                <CFSET #s#="checked">
            </CFIF>
            <input type="radio" #s# name="#qryvar#" value="#i#">#i# 
        </CFLOOP>
    <CFELSEIF #www# is "select">
        <select name=#qryvar# size=3 multiple>
        <CFLOOP INDEX="i" LIST="#response#" DELIMITERS=":">
            <CFSET #s#="">
            <CFIF #i# is #ar#>
                <CFSET #s#="selected">
            </CFIF>
            <option #s# value="#i#">#i#</option>
        </CFLOOP>
        </select>
    <CFELSEIF #www# is "checkbox">
        <CFLOOP INDEX="i" LIST="#response#" DELIMITERS=":">
            <CFSET #s#="">
            <CFIF #i# is #ar#>
                <CFSET #s#="checked">
            </CFIF>
            <input type="checkbox" #s# name=#qryvar# value=#i#>#i# 
        </CFLOOP>
    <CFELSEIF #www# is "text">
        <INPUT type=text NAME=#qryvar# VALUE="#ar#" align=right>
    <CFELSEIF #www# is "textarea">
        <textarea name=#qryvar# rows=1 wrap="soft">#ar#</textarea>
    <CFELSEIF #www# is "password">
        <INPUT TYPE=password NAME=#qryvar# VALUE="">
    <CFELSEIF #www# is "hidden">
        <INPUT TYPE=hidden NAME=#qryvar# VALUE="hidden">
    </CFIF>
    <CFIF ListContains(error,qryvar,"|") gt 0>
        <BR>#GetToken(GetToken(error,ListContains(error,qryvar,"|"),"|"),2,":")#
    </CFIF>
    </td>
    
    </tr>
    
    </CFOUTPUT>
    
    <tr>
    <td colspan=2 align=right><input type=submit value="weiter &gt;&gt;&gt;"</td>
    </tr>
    
    </table>
    </FORM>

<CFELSE>

    <CFTRANSACTION>
    <CFIF #ListLen(ValueList(Already.qry_id))# gt 0>
        <CFQUERY NAME="Save" DATASOURCE="aerzte">
            UPDATE qry_answer
            SET qry_answer.active=0
            WHERE qry_answer.qry_id IN (#ReReplaceNoCase(ValueList(Get.id),",{1,20}",",","ALL")#) AND qry_answer.partcp_id=#cookie_id#;
        </CFQUERY>
    </CFIF>

    <CFOUTPUT QUERY="Get">
        <CFQUERY NAME="Save" DATASOURCE="aerzte">
            INSERT INTO qry_answer (qry_id,partcp_id,response,active,date_entry)
            VALUES (#id#,#cookie_id#,'#Evaluate("FORM.#qryvar#")#',1,'#ACTIME#');
        </CFQUERY>
    </CFOUTPUT>
    </CFTRANSACTION>

    <CFIF #PARAMETEREXISTS(fieldnames)#>
        <CFIF #ReFindNoCase("terminator",fieldnames)# is not 0>
            done
        <CFELSE>
            <CFLOCATION URL="frage.cfm?page=#int(group+1)#">
        </CFIF>
    </CFIF>
</CFIF>
    
<CFINCLUDE TEMPLATE="bottom.cfm">

Convert external maps to OziExplorer

Many map software vendors like German TOP 50 do not include export functions. Printing, however, is usually not a problem.

I therefore suggest to use the print function for exporting map data. Just print your desired map area into a PDF file (TOP 50 nicely allows to set anchor points for that).

A PDF printer driver will be already installed on many systems (if not, please go to sourceforge and download pdfcreator). Write down the upper left and lower right GPS coordinates of your rectangle as you will need them later in OziExplorer.

You will need to download also two graphics packages: netpbm and xpdf.

Move your PDF print export and all downloaded executables into one directory. After running the following script you will see a bmp file that can be imported and calibrated in OziExplorer. Happy navigating!

ozi.cmd

 1:
 2:
 3:
 4:
 5:
 6:
 7:
 8:
 9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
27:
@echo off & setlocal enableDelayedExpansion

rem our working directory
set p=c:\Programme\OziExplorer\
rem our pdf with the map
set d=print.pdf
set w=ping 127.0.0.1 -n 2 -w 1000

:1
rem we extract the figures from the PDF
pdfimages %p%%d% %p%%d%

:2
rem we rename the first and concatenate all further pictures
rem note: the ping is only used to slow down the batch ;-) 
rename %d%-000.ppm block
for %%a in (%p%*.ppm) do %w%>nul & pnmcat -tb %%a block >tmp & %w%>nul & copy tmp block>nul & echo %%a done

:3
rem finally we create a bmp file and clear the space
ppmtobmp %p%block >%p%%d%.bmp
del *.ppm>NUL

:ende
pause
exit