CVS Update: fgo.ca

[Dan Brosemer <odin@cleannorth.org>]

Log Message:
-----------
Understand new format
Drop appendix 5

Some fields in appendix 4 appear to be filled out with 'x' in the sample data.
I don't know what to do with that.  Waiting to hear back from KB.

Modified Files:
--------------
    fgo.ca/public_html:
        butternut.pl

Revision Data
-------------
Index: butternut.pl
===================================================================
RCS file: /cvs/fgo.ca/public_html/butternut.pl,v
retrieving revision 1.8
retrieving revision 1.9
diff -Lpublic_html/butternut.pl -Lpublic_html/butternut.pl -u -r1.8 -r1.9
--- public_html/butternut.pl
+++ public_html/butternut.pl
@@ -87,7 +87,7 @@
 				D4 => 'plotno2',
 				E4 => 'plotno3',
 		
-				H4 => 'plotname',
+				G4 => 'plotname',
 		
 				C7 => 'day1',
 				D7 => 'day2',
@@ -101,61 +101,59 @@
 				C10 => 'evaluators',
 				C12 => 'propertyowner',
 		
-				C15 => 'location1',
-				C17 => 'location2',
-				C19 => 'location3',
-		
-				E22 => 'UTM1',
-				F22 => 'UTM2',
-		
-				D26 => 'easting1',
-				E26 => 'easting2',
-				F26 => 'easting3',
-				G26 => 'easting4',
-				H26 => 'easting5',
-				I26 => 'easting6',
-		
-				D30 => 'northing1',
-				E30 => 'northing2',
-				F30 => 'northing3',
-				G30 => 'northing4',
-				H30 => 'northing5',
-				I30 => 'northing6',
-				J30 => 'northing7',
-		
-				D33 => 'spbearing1',
-				E33 => 'spbearing2',
-				F33 => 'spbearing3',
-		
-				D36 => 'spdist1',
-				E36 => 'spdist2',
-				F36 => 'spdist3',
-		
-				E39 => 'spUTM1',
-				F39 => 'spUTM2',
-		
-				D43 => 'speasting1',
-				E43 => 'speasting2',
-				F43 => 'speasting3',
-				G43 => 'speasting4',
-				H43 => 'speasting5',
-				I43 => 'speasting6',
-		
-				D47 => 'spnorthing1',
-				E47 => 'spnorthing2',
-				F47 => 'spnorthing3',
-				G47 => 'spnorthing4',
-				H47 => 'spnorthing5',
-				I47 => 'spnorthing6',
-				J47 => 'spnorthing7',
-		
-				D51 => 'tree1bearing1',
-				E51 => 'tree1bearing2',
-				F51 => 'tree1bearing3',
-		
-				D54 => 'tree1distance1',
-				E54 => 'tree1distance2',
-				F54 => 'tree1distance3',
+				D15 => 'location1',
+		
+				E21 => 'UTM1',
+				F21 => 'UTM2',
+		
+				D25 => 'easting1',
+				E25 => 'easting2',
+				F25 => 'easting3',
+				G25 => 'easting4',
+				H25 => 'easting5',
+				I25 => 'easting6',
+		
+				D29 => 'northing1',
+				E29 => 'northing2',
+				F29 => 'northing3',
+				G29 => 'northing4',
+				H29 => 'northing5',
+				I29 => 'northing6',
+				J29 => 'northing7',
+		
+				D32 => 'spbearing1',
+				E32 => 'spbearing2',
+				F32 => 'spbearing3',
+		
+				D35 => 'spdist1',
+				E35 => 'spdist2',
+				F35 => 'spdist3',
+		
+				E38 => 'spUTM1',
+				F38 => 'spUTM2',
+		
+				D42 => 'speasting1',
+				E42 => 'speasting2',
+				F42 => 'speasting3',
+				G42 => 'speasting4',
+				H42 => 'speasting5',
+				I42 => 'speasting6',
+		
+				D46 => 'spnorthing1',
+				E46 => 'spnorthing2',
+				F46 => 'spnorthing3',
+				G46 => 'spnorthing4',
+				H46 => 'spnorthing5',
+				I46 => 'spnorthing6',
+				J46 => 'spnorthing7',
+		
+				D50 => 'tree1bearing1',
+				E50 => 'tree1bearing2',
+				F50 => 'tree1bearing3',
+		
+				D53 => 'tree1distance1',
+				E53 => 'tree1distance2',
+				F53 => 'tree1distance3',
 			);
 		
 		my %appendix3 = 
@@ -164,7 +162,7 @@
 				D5 => 'plotno2',
 				E5 => 'plotno3',
 		
-				I5 => 'plotname',
+				G5 => 'plotname',
 		
 				C8 => 'day1',
 				D8 => 'day2',
@@ -178,80 +176,70 @@
 				C11 => 'evaluators',
 				C13 => 'propertyowner',
 				
-				D16 => 'tree1bearing1',
-				E16 => 'tree1bearing2',
-				F16 => 'tree1bearing3',
-		
-				K16 => 'tree1distance1',
-				L16 => 'tree1distance2',
-				M16 => 'tree1distance3',
-		
-				E19 => 'BASbutternut',
-				G19 => 'BASother1',
-				I19 => 'BASother2',
-				K19 => 'BASother3',
-				M19 => 'BASother4',
-				O19 => 'BASother5',
-				Q19 => 'BASother6',
-		
-				C21 => 'prism1',
-				C22 => 'prism2',
-		
-				D23 => 'tree1-20',
-				E23 => 'tree1-21',
-				F23 => 'tree1-22',
-				G23 => 'tree1-23',
-				H23 => 'tree1-24',
-				I23 => 'tree1-25',
-				J23 => 'tree1-26',
-				K23 => 'tree1-27',
-				L23 => 'tree1-28',
-				M23 => 'tree1-29',
-				N23 => 'tree1-30',
-				O23 => 'tree1-31',
-				P23 => 'tree1-32',
-				Q23 => 'tree1-33',
-				S23 => 'tree1-recentdownedbutternut',
-		
-				D25 => 'midpoint-20',
-				E25 => 'midpoint-21',
-				F25 => 'midpoint-22',
-				G25 => 'midpoint-23',
-				H25 => 'midpoint-24',
-				I25 => 'midpoint-25',
-				J25 => 'midpoint-26',
-				K25 => 'midpoint-27',
-				L25 => 'midpoint-28',
-				M25 => 'midpoint-29',
-				N25 => 'midpoint-30',
-				O25 => 'midpoint-31',
-				P25 => 'midpoint-32',
-				Q25 => 'midpoint-33',
-				S25 => 'midpoint-recentdownedbutternut',
-		
-				D27 => 'lasttree-20',
-				E27 => 'lasttree-21',
-				F27 => 'lasttree-22',
-				G27 => 'lasttree-23',
-				H27 => 'lasttree-24',
-				I27 => 'lasttree-25',
-				J27 => 'lasttree-26',
-				K27 => 'lasttree-27',
-				L27 => 'lasttree-28',
-				M27 => 'lasttree-29',
-				N27 => 'lasttree-30',
-				O27 => 'lasttree-31',
-				P27 => 'lasttree-32',
-				Q27 => 'lasttree-33',
-				S27 => 'lasttree-recentdownedbutternut',
-		
-				E31 => 'regeneration',
-				E33 => 'leafsample',
-				E35 => 'twigcanker',
-		
-				J31 => 'remarks1',
-				H33 => 'remarks2',
-				H35 => 'remarks3',
+				D17 => 'tree1bearing1',
+				E17 => 'tree1bearing2',
+				F17 => 'tree1bearing3',
+		
+				K17 => 'tree1distance1',
+				L17 => 'tree1distance2',
+				M17 => 'tree1distance3',
+		
+				Q17 => 'prism1',
+				R17 => 'prism2',
+		
+				D21 => 'tree1-20',
+				E21 => 'tree1-21',
+				F21 => 'tree1-22',
+				G21 => 'tree1-23',
+				H21 => 'tree1-24',
+				I21 => 'tree1-25',
+				J21 => 'tree1-26',
+				K21 => 'tree1-27',
+				L21 => 'tree1-28',
+				M21 => 'tree1-29',
+				N21 => 'tree1-30',
+				O21 => 'tree1-31',
+				P21 => 'tree1-32',
+				Q21 => 'tree1-33',
+				S21 => 'tree1-recentdownedbutternut',
+		
+				D22 => 'midpoint-20',
+				E22 => 'midpoint-21',
+				F22 => 'midpoint-22',
+				G22 => 'midpoint-23',
+				H22 => 'midpoint-24',
+				I22 => 'midpoint-25',
+				J22 => 'midpoint-26',
+				K22 => 'midpoint-27',
+				L22 => 'midpoint-28',
+				M22 => 'midpoint-29',
+				N22 => 'midpoint-30',
+				O22 => 'midpoint-31',
+				P22 => 'midpoint-32',
+				Q22 => 'midpoint-33',
+				S22 => 'midpoint-recentdownedbutternut',
+		
+				D23 => 'lasttree-20',
+				E23 => 'lasttree-21',
+				F23 => 'lasttree-22',
+				G23 => 'lasttree-23',
+				H23 => 'lasttree-24',
+				I23 => 'lasttree-25',
+				J23 => 'lasttree-26',
+				K23 => 'lasttree-27',
+				L23 => 'lasttree-28',
+				M23 => 'lasttree-29',
+				N23 => 'lasttree-30',
+				O23 => 'lasttree-31',
+				P23 => 'lasttree-32',
+				Q23 => 'lasttree-33',
+				S23 => 'lasttree-recentdownedbutternut',
+		
+				E27 => 'regeneration',
+				E29 => 'leafsample',
+				E31 => 'twigcanker',
+		
+				J27 => 'remarks1',
 			);
 		
 		my %nontab = ( appendix2 => \%appendix2, appendix3 => \%appendix3 );
@@ -286,7 +274,7 @@
 				T => 'northing5',
 				U => 'northing6',
 				V => 'northing7',
-				W => 'northing8',
+				W => 'companion',
 		
 				X => 'DBH1',
 				Y => 'DBH2',
@@ -298,49 +286,42 @@
 				AD => 'dieback2',
 				AE => 'dieback3',
 				AF => 'terrain',
-			);
-		
-		my %appendix5 =
-			(
-				-begin => 5,
-				-hasdata => 'A',
-		
-				A => 'tree',
 		
-				B => 'root-oozing',
-				C => 'root-perenneal',
-				D => 'root-callus',
-		
-				E => 'under-oozing',
-				F => 'under-perenneal',
-				G => 'under-callus',
-		
-				H => 'over-oozing',
-				I => 'over-perenneal',
-				J => 'over-callus',
-		
-				K => 'crown-young',
-				L => 'crown-oozing',
-				M => 'crown-perenneal',
-				N => 'crown-callus',
-		
-				O => 'healthy',
-				P => 'cankerno',
-				Q => 'girdling',
-		
-				R => 'eipcormic-stump',
-				S => 'epicormic-stem',
-				T => 'epicormic-crown',
-				U => 'bark',
-		
-				V => 'pests-P1',
-				W => 'pests-L1',
-				X => 'pests-P2',
-				Y => 'pests-L2',
-				Z => 'remarks',
+				AH => 'root-oozing',
+				AI => 'root-perenneal',
+				AJ => 'root-callus',
+		
+				AK => 'under-oozing',
+				AL => 'under-perenneal',
+				AM => 'under-callus',
+		
+				AN => 'cankerno',
+				AO => 'girdling',
+		
+				AP => 'over-oozing',
+				AQ => 'over-perenneal',
+				AR => 'over-callus',
+		
+				AS => 'crown-young',
+				AT => 'crown-oozing',
+				AU => 'crown-perenneal',
+				AV => 'crown-callus',
+		
+				AX => 'healthy',
+
+				AY => 'eipcormic-stump',
+				AZ => 'epicormic-stem',
+				BA => 'epicormic-crown',
+				BB => 'bark',
+		
+				BC => 'pests-P1',
+				BD => 'pests-L1',
+				BE => 'pests-P2',
+				BF => 'pests-L2',
+				BG => 'remarks',
 			);
 		
-		my %tab = ( appendix4 => \%appendix4, appendix5 => \%appendix5 );
+		my %tab = ( appendix4 => \%appendix4 );
 
     my $contents = "";
 		my ($zfh, $ztmp) = Archive::Zip::tempFile();
@@ -384,18 +365,18 @@
 							{
 								my $SQL = <<EOT;
 insert into bnut_appendix2 ( plotno1, plotno2, plotno3, plotname, day1, day2, month1, month2, year1, year2, year3, year4,
-  evaluators, propertyowner, location1, location2, location3, UTM1, UTM2, easting1, easting2, easting3, easting4, easting5, easting6,
+  evaluators, propertyowner, location1, UTM1, UTM2, easting1, easting2, easting3, easting4, easting5, easting6,
   northing1, northing2, northing3, northing4, northing5, northing6, northing7, spbearing1, spbearing2, spbearing3,
   spdist1, spdist2, spdist3, spUTM1, spUTM2, speasting1, speasting2, speasting3, speasting4, speasting5, speasting6,
   spnorthing1, spnorthing2, spnorthing3, spnorthing4, spnorthing5, spnorthing6, spnorthing7, tree1bearing1, tree1bearing2, tree1bearing3,
   tree1distance1, tree1distance2, tree1distance3)
 values
-(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
+(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
 EOT
 
 								my $cursor = $dbh->prepare($SQL);
 								$cursor->execute(map {($_ eq '')?undef:$_} map {$data->{$_}} qw(plotno1 plotno2 plotno3 plotname day1 day2 month1 month2 year1 year2 year3 year4
- evaluators propertyowner location1 location2 location3 UTM1 UTM2 easting1 easting2 easting3 easting4 easting5 easting6
+ evaluators propertyowner location1 UTM1 UTM2 easting1 easting2 easting3 easting4 easting5 easting6
  northing1 northing2 northing3 northing4 northing5 northing6 northing7 spbearing1 spbearing2 spbearing3
  spdist1 spdist2 spdist3 spUTM1 spUTM2 speasting1 speasting2 speasting3 speasting4 speasting5 speasting6
  spnorthing1 spnorthing2 spnorthing3 spnorthing4 spnorthing5 spnorthing6 spnorthing7 tree1bearing1 tree1bearing2 tree1bearing3
@@ -409,30 +390,30 @@
 								my $SQL = <<EOT;
 insert into bnut_appendix3 ( plotno1, plotno2, plotno3, plotname, day1, day2, month1, month2, year1, year2, year3, year4,
   evaluators, propertyowner, tree1bearing1, tree1bearing2, tree1bearing3, tree1distance1, tree1distance2, tree1distance3,
-  BASbutternut, BASother1, BASother2, BASother3, BASother4, BASother5, BASother6, prism1, prism2,
+  prism1, prism2,
   "tree1-20", "tree1-21", "tree1-22", "tree1-23", "tree1-24", "tree1-25", "tree1-26", "tree1-27",
   "tree1-28", "tree1-29", "tree1-30", "tree1-31", "tree1-32", "tree1-33", "tree1-recentdownedbutternut",
   "midpoint-20", "midpoint-21", "midpoint-22", "midpoint-23", "midpoint-24", "midpoint-25", "midpoint-26", "midpoint-27",
   "midpoint-28", "midpoint-29", "midpoint-30", "midpoint-31", "midpoint-32", "midpoint-33", "midpoint-recentdownedbutternut",
   "lasttree-20", "lasttree-21", "lasttree-22", "lasttree-23", "lasttree-24", "lasttree-25", "lasttree-26", "lasttree-27",
   "lasttree-28", "lasttree-29", "lasttree-30", "lasttree-31", "lasttree-32", "lasttree-33", "lasttree-recentdownedbutternut",
-  "regeneration", "leafsample", "twigcanker", "remarks1", "remarks2", "remarks3")
+  "regeneration", "leafsample", "twigcanker", "remarks1")
 values
-(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
+(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
 ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
 EOT
 
 								my $cursor = $dbh->prepare($SQL);
 								$cursor->execute(map {($_ eq '')?undef:$_} map {$data->{$_}} qw(plotno1 plotno2 plotno3 plotname day1 day2 month1 month2 year1 year2 year3 year4
  evaluators propertyowner tree1bearing1 tree1bearing2 tree1bearing3 tree1distance1 tree1distance2 tree1distance3
- BASbutternut BASother1 BASother2 BASother3 BASother4 BASother5 BASother6 prism1 prism2
+ prism1 prism2
  tree1-20 tree1-21 tree1-22 tree1-23 tree1-24 tree1-25 tree1-26 tree1-27
  tree1-28 tree1-29 tree1-30 tree1-31 tree1-32 tree1-33 tree1-recentdownedbutternut
  midpoint-20 midpoint-21 midpoint-22 midpoint-23 midpoint-24 midpoint-25 midpoint-26 midpoint-27
  midpoint-28 midpoint-29 midpoint-30 midpoint-31 midpoint-32 midpoint-33 midpoint-recentdownedbutternut
  lasttree-20 lasttree-21 lasttree-22 lasttree-23 lasttree-24 lasttree-25 lasttree-26 lasttree-27
  lasttree-28 lasttree-29 lasttree-30 lasttree-31 lasttree-32 lasttree-33 lasttree-recentdownedbutternut
- regeneration leafsample twigcanker remarks1 remarks2 remarks3) );
+ regeneration leafsample twigcanker remarks1) );
 
 								push @errors, {message => $member->fileName().": ".$dbh->errstr} if $dbh->errstr;
 
@@ -453,41 +434,26 @@
 insert into bnut_appendix4 ( "treebearing1", "treebearing2", "treebearing3", "treedistance1", "treedistance2", "treedistance3",
   "tree", "UTM1", "UTM2", "easting1", "easting2", "easting3", "easting4", "easting5", "easting6",
   "northing1", "northing2", "northing3", "northing4", "northing5", "northing6", "northing7", "northing8", "DBH1", "DBH2", "DBH3",
-  "crownclass", "vigor", "dieback1", "dieback2", "dieback3", "terrain")
-values
-(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
-EOT
-
-								my $cursor = $dbh->prepare($SQL);
-								foreach my $row (@{$data})
-									{
-										$cursor->execute(map {($_ eq '')?undef:$_} map {$row->{$_}} qw(treebearing1 treebearing2 treebearing3 treedistance1 treedistance2 treedistance3
- tree UTM1 UTM2 easting1 easting2 easting3 easting4 easting5 easting6
- northing1 northing2 northing3 northing4 northing5 northing6 northing7 northing8 DBH1 DBH2 DBH3
- crownclass vigor dieback1 dieback2 dieback3 terrain));
-
-										push @errors, {message => $member->fileName().": ".$dbh->errstr} if $dbh->errstr;
-									};
-
-							}
-						elsif ($type eq 'appendix5')
-							{
-								my $SQL = <<EOT;
-insert into bnut_appendix5 ( "tree", "root-oozing", "root-perenneal", "root-callus", "under-oozing", "under-perenneal", "under-callus",
+  "crownclass", "vigor", "dieback1", "dieback2", "dieback3", "terrain",
+  "root-oozing", "root-perenneal", "root-callus", "under-oozing", "under-perenneal", "under-callus",
   "over-oozing", "over-perenneal", "over-callus", "crown-young", "crown-oozing", "crown-perenneal", "crown-callus",
   "healthy", "cankerno", "girdling", "eipcormic-stump", "epicormic-stem", "epicormic-crown", "bark",
   "pests-P1", "pests-L1", "pests-P2", "pests-L2", remarks)
 values
-(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
+(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
 EOT
 
 								my $cursor = $dbh->prepare($SQL);
 								foreach my $row (@{$data})
 									{
-										$cursor->execute(map {($_ eq '')?undef:$_} map {$row->{$_}} qw(tree root-oozing root-perenneal root-callus under-oozing under-perenneal under-callus
-over-oozing over-perenneal over-callus crown-young crown-oozing crown-perenneal crown-callus
-healthy cankerno girdling eipcormic-stump epicormic-stem epicormic-crown bark
-pests-P1 pests-L1 pests-P2 pests-L2 remarks));
+										$cursor->execute(map {($_ eq '')?undef:$_} map {$row->{$_}} qw(treebearing1 treebearing2 treebearing3 treedistance1 treedistance2 treedistance3
+ tree UTM1 UTM2 easting1 easting2 easting3 easting4 easting5 easting6
+ northing1 northing2 northing3 northing4 northing5 northing6 northing7 northing8 DBH1 DBH2 DBH3
+ crownclass vigor dieback1 dieback2 dieback3 terrain
+ root-oozing root-perenneal root-callus under-oozing under-perenneal under-callus
+ over-oozing over-perenneal over-callus crown-young crown-oozing crown-perenneal crown-callus
+ healthy cankerno girdling eipcormic-stump epicormic-stem epicormic-crown bark
+ pests-P1 pests-L1 pests-P2 pests-L2 remarks));
 
 										push @errors, {message => $member->fileName().": ".$dbh->errstr} if $dbh->errstr;
 									};
@@ -625,21 +591,17 @@
 			};
 		
 		my $fn = shift;
-		if ($sheet[0][0] =~ m/appendix 2/i)
+		if ($sheet[0][0] =~ m/Butternut Survey Location and Map/i)
 			{
 				return 'appendix2';
 			}
-		elsif ($sheet[0][0] =~ m/appendix 3/i)
+		elsif ($sheet[0][0] =~ m/Butternut Health Survey Data Sheet - Header/i)
 			{
 				return 'appendix3';
 			}
-		elsif ($sheet[0][6] =~ m/appenxix 4/i)
+		elsif ($sheet[0][0] =~ m/TREE INFO/i)
 			{
 				return 'appendix4';
-			}
-		elsif ($sheet[0][0] =~ m/appendix 5/i)
-			{
-				return 'appendix5';
 			};
 		return undef;
 	};