use DBI();
use FileHandle();
use IO::File;

#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#Dekleration benötigter Variablen
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
my ($TicketNumber, $Year, $Month, $FiscalYear, $TicketTitle, $Category, $Problemtype, $Component,
	$SLA, $EscalationLevel, $Status, $Priority,	$Queue, $Customer, $CustomerUserID, $TicketOwner,
	$DirectSolve, $Hostname, $Created, $Solved);
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#DB Connection aufbauen
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
my $DB_NAME = "otrs_test";
my $DB_DSN = "DBI:mysql:database=$DB_NAME";
my $DB_USER = "******";
my $DB_PASSWD = "*****";

my $dbh = DBI->connect($DB_DSN, $DB_USER, $DB_PASSWD) or die "Fehler bei Datenbankverbindung";
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#Erstellen der Tabelle TicketData
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
my $SQL_CreateTable = "CREATE TABLE TicketData(
					 	TicketNumber VARCHAR(50) , 
						Year YEAR,
						Month INT,
						FiscalYear YEAR,
						TicketTitle VARCHAR(255),
						Category VARCHAR(50),
						Problemtype VARCHAR(50),
						Component VARCHAR(50),
						SLA VARCHAR(20),
						EscalationLevel TINYINT,
						Status VARCHAR(20),
						Priority VARCHAR(20),
						Queue VARCHAR(50),
						Customer VARCHAR(100),
						CustomerUserID VARCHAR(100),
						Ticketowner VARCHAR(50),
						Directsolve TINYINT,
						Hostname VARCHAR(20),
						Created DATETIME,
						Solved DATETIME,
						PRIMARY KEY (TicketNumber)
)";
my $Abfrage = $dbh->prepare($SQL_CreateTable);
$Abfrage->execute();
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#Export zum Lesen öffnen
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
open(FH, '<c:\ExportOtrsStammdaten.csv') || die "Datei wurde nicht gefunden\n";
my @Content =<FH>;
close(FH);

my $Zeile;
foreach $Zeile (@Content) { #bei diesem Aufruf steht jeweils des aktuelle Wert von Content in der skalaren Variable
	#print "$Zeile";
	($TicketNumber, $Year, $Month, $FiscalYear, $TicketTitle, $Category, $Problemtype, $Component,
	$SLA, $EscalationLevel, $Status, $Priority,	$Queue, $Customer, $CustomerUserID, $TicketOwner,
	$DirectSolve, $Hostname, $Created, $Solved) = split(/;/,$Zeile);
	
	#my $sql = "INSERT INTO tbl_bsp SET bsp_artID=$sorte, bsp_text=$bezeichnung";
	
	my $SQL_Insert = "INSERT INTO TicketData (
							TicketNumber,
							Year,
							Month,
							FiscalYear,
							Category,
							Problemtype,
							Component,
							SLA,
							EscalationLevel,
							Status,
							Priority,
							Queue,
							Customer,
							CustomerUserID,
							TicketOwner,
							DirectSolve,
							Hostname,
							Created,
							Solved
							)
					  VALUES (
							$TicketNumber,
							$Year,
							$Month,
							$FiscalYear,
							$Category,
							$Problemtype,
							$Component,
							$SLA,
							$EscalationLevel,
							$Status,
							$Priority,
							$Queue,
							$Customer,
							$CustomerUserID,
							$TicketOwner,
							$DirectSolve,
							$Hostname,
							$Created,
							$Solved
							)";

	my $Abfrage1 = $dbh->prepare($SQL_Insert);
	$Abfrage1->execute();
}
 
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#Schließen der Datenbankverbindung
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
if ($dbh) {
	$dbh->disconnect();
	#print "DB Verbindung geschlossen";
}

    my $SQL_Insert = "INSERT INTO TicketData (
                            TicketNumber,
                            Year,
                            Month,
                            FiscalYear,
                            Category,
                            Problemtype,
                            Component,
                            SLA,
                            EscalationLevel,
                            Status,
                            Priority,
                            Queue,
                            Customer,
                            CustomerUserID,
                            TicketOwner,
                            DirectSolve,
                            Hostname,
                            Created,
                            Solved
                            )
                      VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    my $Abfrage1 = $dbh->prepare($SQL_Insert);
    $Abfrage1->execute(
                            $TicketNumber,
                            $Year,
                            $Month,
                            $FiscalYear,
                            $Category,
                            $Problemtype,
                            $Component,
                            $SLA,
                            $EscalationLevel,
                            $Status,
                            $Priority,
                            $Queue,
                            $Customer,
                            $CustomerUserID,
                            $TicketOwner,
                            $DirectSolve,
                            $Hostname,
                            $Created,
                            $Solved
     );