#!/home/fipwiki/bin/perl  -I/var/www/twiki/bin -I/var/www/twiki/lib

# export PATH_INFO=/Tracking/ToplEoreIssueTracked
BEGIN {
  # Set default current working directory (needed for mod_perl)
  if ( $ENV{"SCRIPT_FILENAME"} && $ENV{"SCRIPT_FILENAME"} =~ /^(.+)\/[^\/]+$/ ) {
    chdir $1;
  }
  # Set library paths in @INC, at compile time
  unshift @INC, '.';
  require 'setlib.cfg';
}

use strict;
use CGI::Carp qw( fatalsToBrowser );
use CGI;
use Spreadsheet::ParseExcel;
use TWiki;
use TWiki::Render;
use TWiki::Meta;


my $log="";

my $query = new CGI;

my $thePathInfo = $query->path_info();
my $theRemoteUser = $query->remote_user();
my $theTopic = $query->param( 'topic' );
my $theUrl = $query->url;

my( $topic, $webName, $scriptUrlPath, $userName ) =
  TWiki::initialize( $thePathInfo, $theRemoteUser,
		     $theTopic, $theUrl, $query );


## TODO: BEGIN HACK
#my %usermap=(
#	     "FJ" => "FernandoJimenez",
#	     "RP" => "RobertPress",
#	     "JC" => "JamieCoombs",
#	     "SL" => "ShaunLee",
#	     "CL" => "ClausLanghans",
#	    );
## TODO: END HACK


my %config= (
	     "TEXTTOPIC" => "TEXT",
	     "DEBUG" => 0,
	     "TOPICCOLUMN" => "TOPIC",
	     "FORCCEOVERWRITE" => 0,
	     "UPLOADFILE" => "$topic.xls",
	    );

foreach my $key (qw(FORM TOPICPARENT UPLOADFILE NEWTOPICTEMPLATE FORCCEOVERWRITE TOPICCOLUMN DEBUG)) {
  my $value=&TWiki::Func::getPreferencesValue( $key  );
  if (defined $value and $value !~ /^\s*$/) {
    $config{$key} =$value;
    $config{$key} =~ s/^\s*//go;
    $config{$key} =~ s/\s*$//go;
  }
  $log.="  $key=$config{$key}\n";
}

my $xlsfile = $TWiki::pubDir."/$webName/$topic/$config{UPLOADFILE}";
$log.="  attachment file=$webName/$topic/$config{UPLOADFILE}\n";


my $Book = Spreadsheet::ParseExcel::Workbook->Parse($xlsfile);
if (not defined $Book) {
  print $query->header(-type=>'text/plain', -expire=>'now');
  print"Can't read $xlsfile";
}

my($row, $col, $WorkSheet, $cell);
my %colname;
foreach my $WorkSheet (@{$Book->{Worksheet}}) {
  #print "--------- SHEET:", $WorkSheet->{Name}, "\n";
  for (my $col = $WorkSheet->{MinCol} ;	defined $WorkSheet->{MaxCol} && $col <= $WorkSheet->{MaxCol} ; $col++) {
    $cell = $WorkSheet->{Cells}[$0][$col];
    if (defined $cell and $cell->Value ne '') {
      $colname{$col}=$cell->Value if($cell);
      $colname{$col}=~s/\s*//g;
      $log.="  Column $col = $colname{$col}\n";
    }
  }
  for (my $row = $WorkSheet->{MinRow} +1 ; defined $WorkSheet->{MaxRow} && $row <= $WorkSheet->{MaxRow} ; $row++) {
    # read the contents of each data row 
    my %data;			# contains the row
    my $content;
    for (my $col = $WorkSheet->{MinCol} ; defined $WorkSheet->{MaxCol} && $col <= $WorkSheet->{MaxCol} ; $col++) {
      $cell = $WorkSheet->{Cells}[$row][$col];
      # print "( $row , $col ) =>", $cell->Value, "\n" if($cell);
      if ($cell) {
	# print $colname{$col}."=>".$cell->Value, "\n";
	$data{$colname{$col}}=$cell->Value;
      }
    }
    my $newtopic=$data{$config{"TOPICCOLUMN"}};
    next if ($newtopic eq '') ; # Emtpy row

#    # TODO: BEGIN HACK
#    if ($config{"FORM"} eq 'FlowTrackingForm' ) {
#      $newtopic="FI".toTwikiWord($data{"SHORT_DESCRIPTION"}).$data{"NUM"};
#      delete $data{"TOPIC"};
#      if ($data{"REGION"} eq '') {
#	$data{"REGION"}="LDN";
#	$colname{-1}="REGION";
#      }
#      if ($data{"REGION_AFFECTED"} eq '') {
#	$data{"REGION_AFFECTED"}="LDN";
#	$colname{-2}="REGION_AFFECTED";
#      }
#      $data{"LOGGED_BY"}=      $usermap{$data{"LOGGED_BY"}};
#    }
#    # TODO: END HACK


    # Writing the topic
    my $sourceTopic;
    my $changed=0;
    if ( TWiki::Func::topicExists( $webName, $newtopic )) {
      $sourceTopic=$newtopic;
    } else {
      $sourceTopic=$config{"NEWTOPICTEMPLATE"};
      my $msg="$webName/$newtopic: new topic created based on $config{NEWTOPICTEMPLATE}";
      $config{DEBUG} && TWiki::Func::writeWarning($msg);
      $log.="$msg\n";
      $changed=1;
    }

    my ( $meta, $text ) = TWiki::Func::readTopic( $webName, $sourceTopic );
    if (not defined $meta or not defined $text) {
      die "Can't find $sourceTopic";
    }

    for my $key (qw(FORM TOPICPARENT)) {
      if (not defined( ($meta->find("$key"))[0]  ) or
	  not defined(($meta->find("$key"))[0]->{"name"}) or
	  ($meta->find("$key"))[0]->{"name"} ne $config{$key}) {
	my $msg="      $webName/$newtopic: $key     new value=$config{$key}";
	$config{DEBUG} && TWiki::Func::writeWarning($msg);
	$log.="$msg\n";
	$changed=1;
	my %meta=(
		  "name" =>  $config{$key}
		 );
	$meta->put($key,%meta);
      }
    }

    foreach my $colname (values %colname) {

      # Overwrite the text. As a safety measure only overwrite the text if it is not empty.
      if ($colname eq $config{"TEXTTOPIC"} 
	  and  not $data{$config{"TEXTTOPIC"}} =~ m/^\s*$/ 
	  and  $data{$config{"TEXTTOPIC"}} ne $text){
	my $msg="      $webName/$newtopic: topic text has changed";
	$config{DEBUG} && TWiki::Func::writeWarning($msg);
	$log.="$msg\n";
	$log.="vvvvvvvvvvvvvvvvvvv old vvvvvvvvvvvvvvvvvvv \n";
	$log.="$text\n";
	$log.="^^^^^^^^^^^^^^^^^^^ old ^^^^^^^^^^^^^^^^^^^ \n";
	$log.="vvvvvvvvvvvvvvvvvvv new vvvvvvvvvvvvvvvvvvv \n";
	$log.=$data{$config{"TEXTTOPIC"}}."\n";
	$log.="^^^^^^^^^^^^^^^^^^^ new ^^^^^^^^^^^^^^^^^^^ \n";

	$text=$data{$config{TEXTTOPIC}};
	$changed=1;
      }


      my %field;
      # search through all fields and find the field with the name $colname
      foreach my $field ($meta->find("FIELD")) {
	if ($$field{"name"} eq $colname) {
	  if ($$field{"value"} ne $data{$colname} ) {
	    my $msg="      $webName/$newtopic: $colname: old value=".$$field{"value"}." new value=$data{$colname}";
	    $config{DEBUG} && TWiki::Func::writeWarning($msg);
	    $log.="$msg\n";
	    $changed=1;
	    # replace CR/LF and "
	    $data{$colname} =~ s/(\r*\n|\r)/%_N_%/g;
	    $data{$colname} =~ s/\"/%_Q_%/g;
	    my %meta= (
		       "name" =>$colname,
		       "title"=>$colname,
		       "value"=>$data{$colname},
		      );
	    $meta->put( "FIELD", %meta);
	  }
	  last; # found the field
	}
      }
    }

    if ($changed) {		# only save if something has changed
      my ( $oopsUrl, $loginName, $unlockTime ) = TWiki::Func::checkTopicEditLock( $webName, $newtopic );
      if ($oopsUrl eq '' or $config{"FORCCEOVERWRITE"}) {
	TWiki::Store::saveTopic( $webName, $newtopic, $text, $meta, "", 'unlock', 'Notify', "LogSave", "");
	TWiki::Func::setTopicEditLock( $webName, $newtopic,0 );
	my $msg="### $webName/$newtopic written ###";
	$config{DEBUG} && TWiki::Func::writeWarning($msg);
	$log.="$msg\n";
      } else {
	my $msg="$webName/$newtopic locked and FORCCEOVERWRITE not on -> not overwritten";
	$config{DEBUG} && TWiki::Func::writeWarning($msg);
	$log.="$msg\n";
      }
    } else {
      my $msg="$webName/$newtopic not changed -> not written";
      $config{DEBUG} && TWiki::Func::writeWarning($msg);
      $log.="$msg\n";
    }
  }
  last; # only the first sheet
}

print $query->header(-type=>'text/plain', -expire=>'now');
print $log;

sub toTwikiWord {
  my $word=shift;
  $word=~s/\&quot;//g;

  $word =~ s/ a/ A/g;
  $word =~ s/ b/ B/g;
  $word =~ s/ c/ C/g;
  $word =~ s/ d/ D/g;
  $word =~ s/ e/ E/g;
  $word =~ s/ f/ F/g;
  $word =~ s/ g/ G/g;
  $word =~ s/ h/ H/g;
  $word =~ s/ i/ I/g;
  $word =~ s/ j/ J/g;
  $word =~ s/ k/ K/g;
  $word =~ s/ l/ L/g;
  $word =~ s/ m/ M/g;
  $word =~ s/ n/ N/g;
  $word =~ s/ o/ O/g;
  $word =~ s/ p/ P/g;
  $word =~ s/ q/ Q/g;
  $word =~ s/ r/ R/g;
  $word =~ s/ s/ S/g;
  $word =~ s/ t/ T/g;
  $word =~ s/ u/ U/g;
  $word =~ s/ v/ V/g;
  $word =~ s/ w/ W/g;
  $word =~ s/ x/ X/g;
  $word =~ s/ y/ Y/g;
  $word =~ s/ z/ Z/g;


  $word =~s/&amp;/And/g;
  $word =~s/ /_/g;
  $word =~s/\!/_/g;
  $word =~s/\%/_/g;
  $word =~s/\-/_/g;
  $word =~s/\'/_/g;
  $word =~s/\//And/g;
  $word =~s/\?//g;
  $word =~s/\226//g;
  $word =~s/:/_/g;
  $word =~s/\&/And/g;
  $word =~s/;/_/g;
  $word =~s/\\/_/g;
  $word =~s/\</_/g;
  $word =~s/\"/_/g;
  $word =~s/\|/_/g;
  $word =~s/\]/_/g;
  $word =~s/\[/_/g;
  $word =~s/\(/_/g;
  $word =~s/\)/_/g;
  $word =~s/,/_/g;
  $word =~s/\#/_/g;
  $word =~s/\>/_/g;
  $word =~s/\</_/g;
  $word =~s/\+/_/g;
  $word =~s/\t/_/g;
  $word =~s/\./Dot/g;


  # Make first Char always uppercase

  $word =~ s/^a/A/g;
  $word =~ s/^b/B/g;
  $word =~ s/^c/C/g;
  $word =~ s/^d/D/g;
  $word =~ s/^e/E/g;
  $word =~ s/^f/F/g;
  $word =~ s/^g/G/g;
  $word =~ s/^h/H/g;
  $word =~ s/^i/I/g;
  $word =~ s/^j/J/g;
  $word =~ s/^k/K/g;
  $word =~ s/^l/L/g;
  $word =~ s/^m/M/g;
  $word =~ s/^n/N/g;
  $word =~ s/^o/O/g;
  $word =~ s/^p/P/g;
  $word =~ s/^q/Q/g;
  $word =~ s/^r/R/g;
  $word =~ s/^s/S/g;
  $word =~ s/^t/T/g;
  $word =~ s/^u/U/g;
  $word =~ s/^v/V/g;
  $word =~ s/^w/W/g;
  $word =~ s/^x/X/g;
  $word =~ s/^y/Y/g;
  $word =~ s/^z/Z/g;

  # Create WikiWords


  $word =~s/_+//g;

  return $word;
}


