#!/usr/local/bin/perl5 # query.pl - version rf1.0 - taken from version bod1.0 # jobdisp.cgi - taken from query.pl # mscnjobdisp.cgi - taken from jobdisp.cgi for mscn sql job database # displays the jobs in the jobs table after they have been added #use strict; use DBI; use CGI; my $q = new CGI; my $user = $ENV{'REMOTE_USER'}; my $type = $q->param('type'); #my $table = $q->param('table'); my $table = "jobs"; my $orderby = $q->param('order'); my $onetomany = $q->param('onetomany'); my $where_clause = $q->param('where_clause'); my $maintable='jobs'; # id is in every table my $id = $q->param('id'); my $outformfile; my $outform; my $execline; $| =1 ; print $q->header(-type => 'text/html'); if (open(HEAD,"head.html")) { while (
) { print $_; } close(HEAD); } my $dbh = DBI->connect('DBI:mysql:jobs','mscn'); die "Cannot connect to server $DBI::errstr\n" unless $dbh; if ($id) { &display(); } else { &main(); } if (defined($outformfile)) { if ( open(IN,$outformfile)) { print "\n"; while(This job has been filled\n";
} elsif ($in{'active'} eq 'Y') {
# look up profile, skill and job_type text from pointers
$in{'profile'} = &gettext("select profile from profiles where profile_id = '$client_id'");
$in{'category'} = &gettext("select value from categories where cat_id = '$category_id'");
$in{'skill'}=&gettext("select value from categories where cat_id='$skill_id'");
$_ = $outform;
# look for local variables and substitute them in string
# eg. if $_ has $test in it and $test is set in this code
# see Cammel p 254
s:\$(\w*):${$1} || $&:ge;
# now look for names of fields that are in the table
s:\$(\w*):$in{$1}:g;
s:\r: insstr = $insstr";
# print " updstr = $updstr ";
# do the update and then redisplay
# first check is it update or insert
# todo - when updating on a multi value "where" must add
$num = $dbh->do(qq{select id from $table where job_id=$id});
# print "Num is $num\n";
if ($num == 0 || $onetomany eq "1" || $num == '0E0' ) {
$prestr= qq{$insstr};
} else {
$prestr= qq{$updstr};
}
&sqlexec($prestr);
}
}
sub main {
if (open(HEAD,"query.header")) {
while (
:g;
print $_ , "\n";
}
}
print "\n";
if (defined($postfile)) {
if (open(CAT,"$postfile")) {
while (
\n";
if ($DBI::err) {
print $DBI::errstr;
}
print "\n\n";
return $sth->rows;
}
sub display {
if (-f "disp.$table.form") {
$outformfile="disp.$table.form";
$postfile="disp.$table.post";
} else {
print "Missing disp.$table.form";
exit;
}
$execline = (qq{ select * \
from $maintable \
where job_id=$id });
}
# take the form passed and construct
# $insstr - an sql insert statement and $updstr - a sql update statement
# Then either update the row or insert a new row
# if updating use the $where_clause if present
# Note: if onetomany is set it will insert regardless
sub update {
if ($table =~ /^$/) {
print "Opps - error with table value";
die "Error with table value\n";
} else {
$updstr = "update $table set ";
$insstr = "insert into $table ( ";
$insval = "values (";
foreach (@params) {
if ($_ =~ /^type/ || $_ =~ /^table/ ||
$_ =~ /^onetomany/ || $_ =~ /^where_clause/ ) {
next;
}
$insstr .= $_ . ",";
$insval .= "'" . $q->param($_) . "',";
if ($_ =~ /^type/ || $_ =~ /^id/ ) {
next;
}
$updstr .= " $_='";
$updstr .= $q->param($_);
$updstr .= "',";
}
chop($updstr); ## remove extra ,
chop($insval);
chop($insstr);
$updstr .= " where job_id=$id ";
$insstr .= ") $insval )";
if ($where_clause ne "") {
$updstr .= $where_clause ;
}
# print "
';
$postfile="query.footer";
if (length($orderby) <= 2 ) {
$orderby = "order by category_id,title,state,city";
} else {
$orderby = "order by " . $orderby;
$orderby =~ s/start/start desc/;
}
print "\n";
$execline =(qq {
select * from
jobs
where active='Y'
});
}
sub delete {
$execline = qq { delete from $table where job_id=$id $where_clause };
&sqlexec($execline);
}
sub gettext {
my ($execstr) = @_;
my ($g1);
$g1 = $dbh->prepare(qq{$execstr});
$g1->execute;
($ret) = $g1->fetchrow_array;
return $ret;
}
# docs -
# without and id specified will run main and display with query.form
# Variables
# outform - the format that each row in the database will be displayed in
# - can contain $value for any field returned by select statement
# - for example $name would be replaced by value of field "name"
# execline - the sql line to be executed
# postfile - file displayed after last row displayed
# id - unique id for row
# type - type of query
# table - table to do update/edit on
# where_clause - clause to pass select statement when editing/updating
# onetomany - if set will cause contents to be inserted during an update
#
#
# FILES
# query.header - will print before query.form called from main
# query.form - loaded when id= valid id from table
#
#
#
# Flow - called without id set yields output from main()
# - called with id=1 outputs query.form which has type=disp and table
# information with the id
#
# - type=disp looks for disp.table.
$category
$title $start' .
' $city $state