#!/usr/bin/perl
########################################################################################################
#
# kvsinport.pl < CSV-Datei
#
# Vergleicht und importiert Schülergrunddaten aus KVS in TGASCH.DBF

use strict;
use DBI;
use Encode;

# KVS-Datenfelder im CSV File
my $ciKvsKbNummer= 4;
my $ciKvsLfdNr= 5;
my $ciKvsKursbeginn= 12;
my $ciKvsGeschlecht= 45;
my $ciKvsTitel= 47;
my $ciKvsVorname= 48;
my $ciKvsNachname= 49;
my $ciKvsStrasse= 50;
my $ciKvsPlz= 52;
my $ciKvsOrt= 53;
my $ciKvsGeburtsdatum= 55;
my $ciKvsSvNummer= 56;
my $ciKvsTelefonMobil= 66;
my $ciKvsEmailPrivat= 68;
my $ciKvsAnmeldedatum= 113;
my $ciKvsStornodatum= 125;

# Datenfelder in der Abfrage aus TGASCH
my $ciDatAnsprache= 0;
my $ciDatName= 1;
my $ciDatVorname= 2;
my $ciDatSvnr= 3;
my $ciDatKlasse= 4;
my $ciDatPlz= 5;
my $ciDatOrt= 6;
my $ciDatStrasse= 7;
my $ciDatTel= 8;
my $ciDatEmail= 9;
my @asTgaFields= ('ANSPRACHE', 'NAME', 'VORNAME', 'SVNR', 'KLASSE', 'PLZ', 'ORT', 'STRASSE', 'TEL_P', 'EMAIL');

# Variablen
my (@asTgaData, @asImpData, @asKvsData);                # TGA-Daten, CSV-Daten, aufbereitete KVS-Daten
my ($bAddRecord, $iDeltaKlasse, $bIsFirst);             # Insert notwendig, vor oder nach Abschluss, erste Zeile im CSV
my ($sTgaSemester, $iTgaMaxRec, $iTgaJahr);             # nur TGA-Daten
my ($sKvsAnmeldung, $sKvsKursbeitrag, $sKvsGeburtstag, $sKvsBeginn);    # nur KVS-Daten
my ($sKvsTmp, $sTgaTmp, $sUpdTmp);                  # Arbeitsstrings

# DBase-Verzeichnis (Netware über Nncp gemountet- darin TGASCH.DBF)
my $dbh = DBI->connect('DBI:XBase:/home/hms/work') or die $DBI::errstr;

#my $sth= $dbh->prepare ('select GEB from TGASCH');
#$sth->execute() or die $sth->errstr();
#if (@asTgaData = $sth->fetchrow_array()) {
#  print $asTgaData[0];
#}

# laufendes Semester (in allen TGA-Records) und maximale Stammnummer
my $sth= $dbh->prepare ('select STAMM_NR from TGASCH order by STAMM_NR');
$sth->execute() or die $sth->errstr();
$iTgaMaxRec= 0;
while (@asTgaData = $sth->fetchrow_array()) {
  $asTgaData[0]=~ s/^(.+)\///;
  $sTgaSemester= $1;
  $iTgaMaxRec= $asTgaData[0] if ($asTgaData[0] > $iTgaMaxRec);
}

# Basis Schuljahr (xS20xx, im SS 1 Jahr vorher)
$iTgaJahr= substr($sTgaSemester,2,4);
$iTgaJahr-- if (substr($sTgaSemester,0,1) eq 'S');

# wenn zumindest eine 0er-Klasse, vor Abschluss => eine Klassennummer niedriger
$iDeltaKlasse= 0;
my $sth= $dbh->prepare('select KLASSE from TGASCH where KLASSE like "%0%"');
$sth->execute() or die $sth->errstr();
if (@asTgaData = $sth->fetchrow_array()) {
  $iDeltaKlasse= 1;
}

# alle CSV Records einlesen
$bIsFirst= 1;
while (<>) {
  # erste Zeile (Feldnamen) überspringen
  if ($bIsFirst) {
    $bIsFirst= 0;
    next;
  }
  # mit ; getrennt
  @asImpData= split(/;/);
  # Klasse ergibt sich aus 2. Buchstaben Kursbezeichnung und 1. Zeichen der laufenden Nummer
  $asKvsData[$ciDatKlasse]= substr($asImpData[$ciKvsKbNummer],1,1 ).(substr($asImpData[$ciKvsLfdNr],0,1)-$iDeltaKlasse).'A';
  # Umwandlung Geschlecht (KVS) auf Ansprache (TGA)
  $asKvsData[$ciDatAnsprache]= ($asImpData[$ciKvsGeschlecht]eq 'M') ? 'HERR' : 'FRAU';
  # Namen etc. in Großschrift
  $asKvsData[$ciDatName]= myuc($asImpData[$ciKvsNachname]);
  $asKvsData[$ciDatVorname]= myuc($asImpData[$ciKvsVorname]);
  $asKvsData[$ciDatStrasse]= myuc($asImpData[$ciKvsStrasse]);
  $asKvsData[$ciDatPlz]= $asImpData[$ciKvsPlz];
  $asKvsData[$ciDatOrt]= myuc($asImpData[$ciKvsOrt]);
  $asKvsData[$ciDatTel]= $asImpData[$ciKvsTelefonMobil];
  $asKvsData[$ciDatEmail]= $asImpData[$ciKvsEmailPrivat];
  # SVNR mit Geburtsdatum ergänzen
  $asKvsData[$ciDatSvnr]= $asImpData[$ciKvsGeburtsdatum].substr($asImpData[$ciKvsGeburtsdatum],0,2).substr($asImpData[$ciKvsGeburtsdatum],3,2).
     substr($asImpData[$ciKvsGeburtsdatum],8,2);
  # Datumsangaben von TT.MM.JJJJ auf JJJJMMTT
  $sKvsGeburtstag= substr($asImpData[$ciKvsGeburtsdatum],6,4).substr($asImpData[$ciKvsGeburtsdatum],3,2).substr($asImpData[$ciKvsGeburtsdatum],0,2);
  $sKvsAnmeldung= substr($asImpData[$ciKvsAnmeldedatum],6,4).substr($asImpData[$ciKvsAnmeldedatum],3,2).substr($asImpData[$ciKvsAnmeldedatum],0,2);
  $sKvsBeginn= substr($asImpData[$ciKvsKursbeginn],6,4).substr($asImpData[$ciKvsKursbeginn],3,2).substr($asImpData[$ciKvsKursbeginn],0,2);

  # Vereinheitlichung der Abkürzungen für Straße und Gasse
  $asKvsData[$ciDatStrasse]=~ s/STRASSE */STR. /;
  $asKvsData[$ciDatStrasse]=~ s/GASSE */G. /;
  $asKvsData[$ciDatStrasse]=~ s/STRAßE */STR. /;
  $asKvsData[$ciDatStrasse]=~ s/G\. */G. /;
  $asKvsData[$ciDatStrasse]=~ s/STR\. */STR. /;
  # Vereingeitlichung der Telefonnummern
  $asKvsData[$ciDatTel]=~ s/^\+43 ?//;
  $asKvsData[$ciDatTel]=~ s/^0//;
  $asKvsData[$ciDatTel]=~ s/[ \/\-]//g;
  $asKvsData[$ciDatTel]=~ s/^/0/ if $asKvsData[$ciDatTel];

  # Abfrage der Grunddaten aus der TGASCH, Match auf Vorname, Name, Klasse und SVNR
  my $sth = $dbh->prepare('select '.join(',', @asTgaFields).' from TGASCH where NAME="'.encode("cp850", decode("utf8", $asKvsData[$ciDatName])).
            '" and VORNAME="'.encode("cp850", decode("utf8", $asKvsData[$ciDatVorname])).'" and KLASSE="'.$asKvsData[$ciDatKlasse].
            '" and SVNR="'.$asKvsData[$ciDatSvnr].'"') or die $dbh->errstr();
  $sth->execute() or die $sth->errstr();
  $bAddRecord= 1;

  # vergleich der Grunddaten bei vorhandenen Usern
  while (@asTgaData = $sth->fetchrow_array()) {
    $bAddRecord= 0;
    $sKvsTmp= $sTgaTmp= '';
    for (my $i= 0; $i < scalar(@asTgaFields); $i++) {
      $asTgaData[$i]= encode("utf8", decode("cp850", $asTgaData[$i]));
      # Vereinheitlichung wir oben
      if ($i == $ciDatStrasse) {
    $asTgaData[$i]=~ s/STRASSE */STR. /;
    $asTgaData[$i]=~ s/GASSE */G. /;
    $asTgaData[$i]=~ s/STRAßE */STR. /;
    $asTgaData[$i]=~ s/G\. */G. /;
    $asTgaData[$i]=~ s/STR\. */STR. /;
      }
      elsif ($i == $ciDatTel) {
    $asTgaData[$i]=~ s/^\+43 ?//;
    $asTgaData[$i]=~ s/^0//;
    $asTgaData[$i]=~ s/[ \/\-]//g;
    $asTgaData[$i]=~ s/^/0/ if $asTgaData[$i];
      }
      # Emails wechselseitig ergänzen
      elsif ($i == $ciDatEmail) {
        $asTgaData[$i]= $asKvsData[$i] unless $asTgaData[$i];
        $asKvsData[$i]= $asTgaData[$i] unless $asKvsData[$i];
      }
      # führende und endende Leerzeichen entfernen
      $asTgaData[$i]=~ s/^ +//;
      $asKvsData[$i]=~ s/^ +//;
      $asTgaData[$i]=~ s/ +$//;
      $asKvsData[$i]=~ s/ +$//;
      $sTgaTmp.= $asTgaData[$i].'…';
      $sKvsTmp.= $asKvsData[$i].'…';
    }
    # bei Unterschieden Anzeige
    if ($sTgaTmp ne $sKvsTmp) {
      print 'Unterschiede bei '.$asKvsData[$ciDatVorname].' '.$asKvsData[$ciDatName].' ('.$asKvsData[$ciDatKlasse].")\n";
      for (my $i= 0; $i < scalar(@asTgaFields); $i++) {
         if ($asTgaData[$i] ne $asKvsData[$i]) {
           print '  TGA: '.$asTgaData[$i]."\n  KVS: ".$asKvsData[$i]."\n";
         }
      }
    }
  }
  # Wenn kein Match gefunden, Record zu TGASCH hinzufügen
  if ($bAddRecord) {
    print 'Hinzufügen '.$asKvsData[$ciDatVorname].' '.$asKvsData[$ciDatName].' ('.$asKvsData[$ciDatKlasse].")\n";
    $sTgaTmp= 'insert into TGASCH ('join(',', @asTgaFields).',AUS_STAND,WERB1,WERB2,WERB3,ERST_PERS,SJ1,SJ2,STAMM_NR,SE_KLASSE,ANMELD,ERST_AUF,GEB) values (';
    for (my $i= 0; $i < scalar(@asTgaFields); $i++) {
      $sTgaTmp.= '"'.encode("cp850", decode("utf8", $asKvsData[$i])).'", ';
    }
    $sTgaTmp.= '"NE", ';                                # Schulabschluss
    $sTgaTmp.= '0, ';                                   # geworben durch
    $sTgaTmp.= '0, ';
    $sTgaTmp.= '0, ';
    $sTgaTmp.= '"P", ';                                 # Eingabe durch
    $sTgaTmp.= ($iTgaJahr-(substr(@asKvsData[$ciDatKlasse],1,1) eq 2 ? 1 : 0)).", ";    # Schuljahr 1. Klasse
    $sTgaTmp.= ($iTgaJahr-(substr(@asKvsData[$ciDatKlasse],1,1) eq 2 ? 1 : 0)+1).", ";  # Schuljahr 2. Klasse
    $sTgaTmp.= '"'.$sTgaSemester."/".++$iTgaMaxRec.'" ,';               # Stammnummer ([SW]S20xx/###)
    $sTgaTmp.= "$sKvsBeginn, ";                             # Schuljahresbeginn
    $sTgaTmp.= "$sKvsAnmeldung, ";                          # Datum der Anmeldung
    $sTgaTmp.= "$sKvsAnmeldung, ";                          # Datum der Erstanmeldung
    $sTgaTmp.= "$sKvsGeburtstag";                           # Geburtstag
    $sTgaTmp.= ')';

    # Spezialzeichen escapen
    $sTgaTmp=~ s/@/\\@/g;

    my $sth= $dbh->prepare($sTgaTmp);
    $sth->execute() or die $sth->errstr();
  }
}

# uc wandelt erweiterte Zeichen nicht um
sub myuc {
  my $sText= shift;
  $sText= uc($sText);
  $sText=~ tr/äöüéè/ÄÖÜÉÈ/;
  return $sText;
}