#!/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() { $outform .= $_ ; } close(IN); } else { die "Error in form file $outformfile\n"; } } if ($execline ne "" ) { &sqlexec($execline); } #$sth->execute() || print "1 - Error in execution $prepline\n"; if ($DBI::err) { print $DBI::errstr; } my $fields = $sth->{'NUM_OF_FIELDS'}; my $names=$sth->{NAME}; while (my $hash = $sth->fetchrow_hashref) { %in = %${hash}; $client_id = $in{'client_id'}; $category_id = $in{'category_id'}; $skill_id = $in{'skill'}; if ($in{'status'} eq 'F') { print "

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:
:g; print $_ , "\n"; } } print "\n"; if (defined($postfile)) { if (open(CAT,"$postfile")) { while () { s:\$(\w*):$in{$1}:g; print $_; } } } if (open(CAT,"foot.html")) { while () { s:\$(\w*):$in{$1}:g; print $_; } } sub sqlexec { my ($prep) = @_; $prep =~ s:\n::g; $sth = $dbh->prepare($prep) || print "Prep failed"; print ""; $sth->execute() || print "2 - Error in execution of $prep
\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 "

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 () { print $_; } close(HEAD); } # $outform = '

  • $id # $email'; $outform = ' $category $title$start' . '$city $state'; $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.