---------------------------------------------------------------------------------------------------------------------------------------------------- #Program Listing for: agent_hearing_lookup.pl #Project: agentlookup #Namespace: perl ---------------------------------------------------------------------------------------------------------------------------------------------------- ############################################################### # agent_hearing_lookup.pl # sboe # scott # agent hearing lookup script ############################################################### # modded to use cgi.pm # lots of ancillary functions in scotmisc.lib and admin.lib ############################################################### # created with Tahoma 10, bold, will line up better if you select all and change to that font ############################################################### use strict; # this is redundant but for clarity package main; use CGI qw(:standard escapeHTML); require "admin.lib"; require "scotmisc.lib"; #this variable is global and required for admin.lib local ($::cgi); $::cgi = new CGI; GetCGIvars(); use Win32::ODBC; $::use_open = 0; local ($::rangestr, $::rangeshow); # Flush StdOut $| = 1; local ($::page_text, $::page_no, $::no_records); $::page_text = ""; $::no_records = 0; if ( $::agentch || $::agent_no) { if ($::agent_no) { $::agentch = $::agent_no; } $::page_no = $::page; $::page_no = int($::page_no); transRangeParams($::range); my $nopaging_cv = ""; $nopaging_cv = $::nopaging if $::nopaging; docookieheader( { "sboe_agent" => $::agentch , "sboe_nopaging" => $nopaging_cv } ); printPageHeader(); getData(); myPageFooter(); } else { if ($::button) { dispErrHdr("Please Fill in a Valid Agent Number."); } else { displayForm(); } } exit(0); ############################################################### sub getData ############################################################### { my ($rowcount, $db2); my ($dsn, $sql, $db); my ( $hd, $ht, $docket, $ad1, $ad2, $city, $ay, $decdt, $wy, $pet_id, $nbook, $nmap, $cparcel, $nrollnumber); $dsn = "VFPTEST"; $sql = "SELECT petition.dhearingdate, petition.nappealyear, petition.chearingtime, " . "petition.ctaxpayer, " . "strtran(str(petition.ndocketnum,5) + '-' + " . " str(petition.ncountycode,2) + '-' + " . " str(petition.nworkyear,2) ,' ','0') " . "as ndocket, petition.ddecisiondate, " . "location.clocationid, location.caddress1, location.caddress2, " . "location.ccity, petition.ncountycode, petition.petition_id, petition.nworkyear, " . "appeals.cAvClassCode as cclass, appeals.nCntyUseCode as nUse, " . "appeals.nbook, appeals.nmap, appeals.cparcel, appeals.nrollnumber " . " FROM AZSBOE!PETITION, AZSBOE!LOCATION, AZSBOE!APPEALS " . "WHERE nAgentId=$::agentch AND " . "petition.cHearingLocation = location.cLocationId AND " . "petition.dhearingdate > date() - $::rangestr AND " . "petition.petition_id = appeals.petition_id AND appeals.nmultiplenum = 1 " . "order by 1 desc, 3, 2"; $db = new Win32::ODBC($dsn); #print "</header><body>db:$db"; $db->Sql($sql); my ($count, $skip_cnt, $total_cnt); $count = 0; $skip_cnt = 0; $total_cnt = 0; # if using paging skip over previous pages results, in scotmisc.lib preSkip( $::nopaging, $::page_no, \$db, \$skip_cnt, \$total_cnt); my $print_cnt = 0; while ($db->FetchRow()) { $print_cnt++; $total_cnt++; if (! $::nopaging ) { last if $print_cnt == 21 ; } $count++; if ($count == 1) { printHeader(); } my $locid; my $ctaxpayer; my ($cclass, $nuse); ( $hd, $ht, $docket, $ad1, $ad2, $city, $ay, $decdt, $wy, $pet_id, $nbook, $nmap, $cparcel, $nrollnumber, $locid, $ctaxpayer, $cclass, $nuse) = $db->Data("dhearingdate", "chearingtime","ndocket", "caddress1", "caddress2", "ccity", "nappealyear", "ddecisiondate", "nworkyear", "petition_id", "nbook", "nmap", "cparcel", "nrollnumber", "clocationid", "ctaxpayer", "cclass", "nuse"); $docket =~ tr/ /0/; my $rowcount = quickSQLResult($dsn, "SELECT cnt(*) as ncnt from appeals where appeals.petition_id = $pet_id", "ncnt"); fixdate( \$decdt ); fixdate( \$hd ); print "<tr>"; col(qq(<font color="#004000">) . $hd); colNA( qq(<font color="#004000">) . convertMilitary($ht), "right"); if ($decdt eq "Pending") { if ($nbook) { col("<font color=purple>$nbook-$nmap-$cparcel</font>"); } else { col("<font color=purple>$nrollnumber</font>"); } } else { if ($nrollnumber) { $nrollnumber =~ tr/ //; print qq(<td><font face=\'Arial\'><a href='nodroll2.pl?parcelno=$nrollnumber&) . qq(button=Do+Lookup&appyr=$ay&workyr=$wy'>$nrollnumber</a></font></td>); } else { # remove trailing spaces, internal spaces shouldn't exist in # $parcel as they've been removed earlier, but an extra space # at end causes the href not to work, e.g. 205-14-150_ (space) # in the a href tag the space causes problems. $nbook =~ tr/ /0/; $nmap =~ tr/ /0/; $cparcel =~ tr/ //; my $parcel1 = $nbook . "-" . $nmap . "-" . $cparcel; print qq(<td><font face="Arial">) . qq(<a href="nod4.pl?parcelno=$parcel1&button=Do+Lookup) . qq(&appyr=$ay&workyr=$wy&docketnum=$docket" target="_blank">$parcel1</a></font></td>\n); } #endif $nrollnumber } #endif $decdt eq "Pending" my $cgitax = cgitrans($ctaxpayer); colNA( qq(<font face="Arial">) . qq(<a href="cgi-bin/name_lookup.pl?company=$cgitax") . qq( target="_blank">$ctaxpayer</a></font>\n) ); if ($ad1 && $ad2) { $ad1 = $ad1 . "<br>" . $ad2; } elsif ($ad2) { $ad1 = $ad2; } $ad1 =~ s/(\w+)/\u\L$1/g; # $ad1 =~ s/Suite 2323 Hearing//g; $ad1 =~ s/Suite 2323//g; $ad1 =~ s/Hearing//g; $ad1 =~ s/First/1st/g; $ad1 =~ s/North/N /g; if ($locid) { colNA( qq(<a href="/cgi-bin/hrooms.pl?loc=$locid#$locid" target="_blank"><b>$ad1</b></a>) ); } else { colNA(qq(<font color="#004000">) . $ad1); } colNA( ucfirst(lc($city)) ); my $docketnum = substr($docket, 0, 5); $wy =~ s/\40+//; col( qq(<a href="cgi-bin/docket_detail.pl?docketnum=$docketnum&work_year=$wy") . qq( target="_blank">$docket</a>) ); if ($::wide) { my $showloc; if ($locid) { $showloc = qq(<font face="Arial">) . qq(<a href="/cgi-bin/hrooms.pl?loc=$locid#$locid" target="_blank"><b>$locid</b></a></font>\n); } else { $showloc = "N/A"; } col($showloc); } if ($::wide) { my $showay; if (int($ay) < 90) { $showay = $ay + 2000; } else { $showay = $ay + 1900; } col($showay); my $dec_color = ""; $dec_color= "<font color=brown>" if $decdt eq "Pending"; col($dec_color . $decdt); } if ($::wide) { col($cclass); col($nuse); } if ($::wide) { col($rowcount); } print "</tr>\n"; } #end of while loop while ($db->FetchRow()) { $total_cnt++; } $db->Close(); if (! $::nopaging ) { $::page_text = getPageLinks($total_cnt, "cgi-bin/agent_hearing_lookup.pl?agentch=$::agentch&range=$::range&wide=$::wide", "page", $::page_no ); } if ($count != 0) { printDataFooter(); } else { printNoRecords(); $::no_records = 1; } } ############################################################### sub printPageHeader ############################################################### { defPageHeader("SBOE Agency Lookup Results"); } ############################################################### sub printHeader ############################################################### { # $::wide is a global passed via a parameter, it means we are using a wide grid and including an extra # column or two. if it's defined/true (usually set to "on" by cgi checkbox form via html if checked -- if they don't hit the checkbox the # cgi param is undefined in perl) then make appropriate table changes. my $widestr; $widestr = "<b><font color=blue>Wide Format</font></b><br>" if $::wide; my $this_hdr = <<MARKER; <b>Agent Hearing Time Report</b><br> <b>Search Period:<font color=blue>$::rangeshow</font></b><br> <b>Agent Number:<font color=blue>$::agentch</font></b><br> $widestr Click on the Docket Number Column for Full Parcel Listing for that Docket<br> Click on the Lead Parcel for Decisions on that Parcel (if Available) </b> <br> MARKER my $table_hdr; if ($::wide) { $table_hdr = qq(<table border=1 width="1000">); } else { $table_hdr = qq(<table border=1 width="750">); } my @columns; if ($::wide) { @columns = ("Hearing<br>Date", "Hearing<br>Time", "Lead<br>Parcel", "Owner", "Hearing<br> Address", "Hearing<br>City", "Docket", "SBOE Loc<br>Code", "Appeal<br>Year", "Decision<br>Date", "Legal<br>Class", "Use<br>Code", "Parcel<br>Count"); } else { @columns = ("Hearing<br>Date", "Hearing<br>Time", "Lead<br>Parcel", "Owner", "Hearing<br> Address", "Hearing<br>City", "Docket" ); } # print standard header and top of the table with columns, in scotmisc.lib showpagestart($this_hdr, $table_hdr, \@columns); } ############################################################### sub myPageFooter ############################################################### { if ($::no_records || $::nopaging) { print qq("</body></html>"); } else { #frame the page links in a table, in scotmisc.lib printPageChoices($::page_text); } } ############################################################### sub displayForm ############################################################### { print header(); my %vars; # get cookie values if available $vars{"__AGENT_VALUE__"} = qq(VALUE="$::sboe_agent") if $::sboe_agent; $vars{"__DISABLE_PAGING__"} = qq(CHECKED) if $::sboe_nopaging; $vars{"__MENU_TABLE__"} = slurpfile("../templates/menu.htm"); $vars{"__AGENT_LISTBOX__"} = createAgentListBox("agentch"); PrintParseTemplate("../templates/agent_hearing_lookup.htm", \%vars); } ############################################################### ############################################################### #####agent_hearing_lookup.pl########################################### ############################################################### ###############################################################