#!/usr/bin/perl
# Copyright Leslie Richardson 2001-2020
# This file is part of Open Admin for Schools

my %lex = ('Update Meta' => 'Update Meta Table',
	   'Name' => 'Name',
	   'Field' => 'Field',
	   'Index'=> 'Index',
	   'No Updates required' => 'No Updates required',
	   'Eoy' => 'Eoy',
	   'Main' => 'Main',
	   'Removing field' => 'Removing field',
	   'Error' => 'Error',

	   );

my @tables = ( staff, staff_multi, student, student_inac, announce );
# These are the tables that have meta support built.

use DBI;
use CGI;

my $viewsize = 20; # default value for viewsize.
my $formtype = 'text'; # default form type.

# Read config variables
eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}

my $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;


print qq{$doctype\n<html><head><title>$lex{'Update Meta'}</title>
<link rel="stylesheet" href="$css" type="text/css">
</head><body style="margin:2em;">\n};

print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{ <a href="$eoypage">$lex{Eoy}</a> ]\n};

print qq{<h1>$lex{'Update Meta'}</h1>\n};

my $dsn = "DBI:mysql:$dbase";
my $dbh = DBI->connect($dsn,$user,$password);



# Setup Checking for existence of a field record
$sth3 = $dbh->prepare("select id from meta 
  where tableid = ? and fieldid = ?");

# Loop through all tables
foreach my $table ( @tables ) {

    print qq{<h1>$table</h1>\n};

    my $index = 0;
    my $first = 1;

    my %fieldnames;
    # Get fieldnames from meta table for this table
    my $sth4 = $dbh->prepare("select fieldid from meta where tableid = ?");
    $sth4->execute( $table );
    while ( my $fieldname = $sth4->fetchrow ) {
	$fieldnames{$fieldname} = 1;
    }


    $sth1 = $dbh->prepare("show columns from $table");
    $sth1->execute;
    if ($DBI::errstr){print $DBI::errstr; die "$DBI::errstr for table $table show\n"; }

    print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
    print qq{<tr><th>$lex{Name}</th><th>$lex{Field}</th>};
    print qq{<th>$lex{Index}</th></tr>\n};

    
    while ( my @cols = $sth1->fetchrow ) {

	# Get size of the field
	my $size = 0;
	if ($cols[1] =~ /char\((.+)\)/ or $cols[1] =~ /varchar\((.+)\)/){
	    #print qq{The result is: $1 \n};
	    $size = $1;
	}

	# Check for presence in meta table...
	$sth3->execute( $table, $cols[0] );
	my $id = $sth3->fetchrow;

	if ( $id ){ # field exists, just update index.
	    $sth2 = $dbh->prepare("update meta set arrayidx = '$index' where id = '$id'");
	    $sth2->execute;
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    delete $fieldnames{$cols[0]};  # remove this fieldname from the list

	} else { # field doesn't exist in meta; add it.
	    print qq{<tr><td>$cols[0]</td><td>$cols[1]</td><td>$index</td></tr>\n};
	    $fieldname = "$cols[0]";
	    $sth2 = $dbh->prepare("insert into meta ( dbaseid, tableid, fieldid, 
              datatype, arrayidx, size, fieldname, formtype, viewsize ) 
              values('school','$table','$cols[0]','$cols[1]','$index','$size',
             '$fieldname','$formtype','$viewsize')");
	    $sth2->execute; 
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    $first = 0;
	}
	$index++;

    } # End of field loop.


    # Test for any existing values left in %fieldnames. If any, delete from meta table.
    foreach my $fieldname ( keys %fieldnames ) {
	my $sth5 = $dbh->prepare("delete from meta where tableid = ? and fieldid = ?");
	$sth5->execute( $table, $fieldname );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	print qq{<tr><td colspan="3"><b>$lex{'Removing field'} $fieldname</td></tr>\n};
	$first = 0;
    }


    if ($first) { 
	print qq{<tr><td colspan="3"><b>$lex{'No Updates required'}</b></td></tr>\n};
    } 
    print qq{</table><p></p>\n};


} # End of main loop.

print qq{</body></html>\n};
