use CGI;
use CGI::Carp qw(fatalsToBrowser);
$cgi = new CGI;

print($cgi->header('text/html'));

my ($logon, $cursor, %resultHash);
my($id,$telefon,$name,$beschreibung,$gebaeude,$geschoss,$ort,$check_bauschrott,
$check_schrott,$check_sperrmuell,$check_altpapier,$check_geheimpapier,$check_gga,
$check_fluessig,$manuell,$menge_m3,$menge_t,$menge_lesa,$menge,$geld_kostenstelle,
$geld_psp_element,$geld_co_auftrag,$email,$amail,$bemerkung,$status,$ausfuehrender,$erfasst,$bearbeiter);

#MSSQL Logon
use DBI;
my $data_source = q/dbi:ODBC:DDB/;
my $user = q/USER/;
my $password = q/PASS/;

my $dbh = DBI->connect($data_source, $user, $password, { RaiseError => 1, odbc_cursortype => 2})
    or die "Can't connect to $data_source: $DBI::errstr";


my $sqldate = "SELECT GETDATE() AS SMALLDATETIME";
	my $sth = $dbh->prepare($sqldate);
		$sth->execute;
		my @date = $sth->fetchrow_array;
	$date= @date[0];
	$sth->finish();

my $sqlformat = "SET LANGUAGE us_english";
	my $sth1 = $dbh->prepare($sqlformat);
		$sth1->execute;

$wunschd = $cgi->param('wunschdatum');


$id			= $cgi->param('id');



if($cgi->param('status') eq 'e' or $cgi->param('status') eq 'a')
    {
	$datetime	= $date_timestamp; 	#$datetime	= ($date . substr($time,2,6).'000000');
    }
else
    {
	$datetime = '';
    }

$id			= $cgi->param('id');
$name			= $cgi->param('name');
$email			= $cgi->param('email');
$telefon		= $cgi->param('telefon');
$beschreibung		= $cgi->param('beschreibung');
$gebaeude		= $cgi->param('gebaeude');
$geschoss		= $cgi->param('geschoss');
$ort			= $cgi->param('ort');

$check_bauschrott	= check2bin($cgi->param('check_bauschrott'));
$check_schrott		= check2bin($cgi->param('check_schrott'));
$check_sperrmuell	= check2bin($cgi->param('check_sperrmuell'));
$check_altpapier	= check2bin($cgi->param('check_altpapier'));
$check_geheimpapier	= check2bin($cgi->param('check_geheimpapier'));
$check_gga		= check2bin($cgi->param('check_gga'));
$check_fluessig		= check2bin($cgi->param('check_fluessig'));

$manuell		= $cgi->param('manuell');
$menge_m3		= $cgi->param('menge_m3');
$menge_t		= $cgi->param('menge_t');
$menge_lesa		= $cgi->param('menge_lesa');
$menge			= $cgi->param('menge');
#$wunschdatum		= substr($wunschd,6,4).substr($wunschd,3,2).substr($wunschd,0,2);
my $wunschdatum		= substr($wunschd,6,4)."-".substr($wunschd,3,2)."-".substr($wunschd,0,2);
$erforderlich		= $cgi->param('erforderlich');
$geld_kostenstelle	= $cgi->param('geld_kostenstelle');
$geld_psp_element	= $cgi->param('geld_psp_element');
$geld_co_auftrag	= $cgi->param('geld_co_auftrag');

$bemerkung		= $cgi->param('bemerkung');
$status			= $cgi->param('status');
$ausfuehrender		= $cgi->param('ausfuehrender');
$amail			= $cgi->param('amail');

$erfasst		= $cgi->param('erfasstraw');
$bearbeiter		= $cgi->remote_user();


if($amail)
{
	if($datetime eq '')
	{
# EMAIL EMPFÄNGER

	print("E-Mail Versand an:<br>$amail");

	use Mail::Sendmail;

# EMAIL INHALT

	# Was muss entsorgt werden
	if($cgi->param('check_bauschrott')){$was = $was . "Bauschutt, ";}
	if($cgi->param('check_schrott')){$was = $was . "Schrott, ";}
	if($cgi->param('check_sperrmuell')){$was = $was . "Sperrmüll, ";}
	if($cgi->param('check_altpapier')){$was = $was . "Altpapier, ";}
	if($cgi->param('check_geheimpapier')){$was = $was . "Geheimpapier, ";}
	if($cgi->param('check_gga')){$was = $was . "Gemischter Gewerbe Abfall, ";}
	if($cgi->param('check_fluessig')){$was = $was . "Flüssige Abfälle, ";}
	$was = $was . $cgi->param('manuell');

	# Aufwand
	if($cgi->param('menge_m3')){$aufwand = $cgi->param('menge_m3')." m³, ";}
	if($cgi->param('menge_t')){$aufwand = $cgi->param('menge_t')." Tonnen, ";}
	if($cgi->param('menge_lesa')){$aufwand = $cgi->param('menge_lesa')." Lesabehälter, ";}


		# Message

		$message = "
Entsorgungsauftrag
-----------------

Auftragsnummer	\t = ".$cgi->param('id')."
Wunschdatum	\t = ".$cgi->param('wunschdatum')."
Name		\t = ".$cgi->param('name')."
E-Mail		\t = ".$cgi->param('email')."
Telefon		\t = ".$cgi->param('telefon')."
Gebäude		\t = ".$cgi->param('gebaeude')."
Geschoss	\t = ".$cgi->param('geschoss')."
Ort		\t = ".$cgi->param('ort')."
Was?		\t = ".$was."
Menge		\t = ".$menge.$cgi->param('menge')."
Beschreibung	\t = ".$cgi->param('beschreibung')."
Bemerkung	\t = ".$cgi->param('bemerkung')."
Ausführender	\t = ".$cgi->param('ausfuehrender')."
erforderlich, weil\t = ".$erforderlich."

Kostenstelle	\t = ".$cgi->param('geld_kostenstelle')."
PSP Element	\t = ".$cgi->param('geld_psp_element')."
CO-Auftrag	\t = ".$cgi->param('geld_co_auftrag')."

===========================================================================================



-----------------------      -------------------------      -------------------------------
Erledigt am:                 Name/Firma:                    Geprüft/Anerkannt Auftraggeber:
===========================================================================================
";

	# EMAIL HEADER

		%mail = (
			To      => $amail,
			From    => 'absender@mail.com',
			Subject => "Entsorgungsauftrag ".$cgi->param('id'),
			Message => $message
		);

	# EMAIL VERSAND

		sendmail(%mail) or die $Mail::Sendmail::error;

	# EMAIL LOG

		$log = $Mail::Sendmail::log;
		$log =~ s/\n/<br>/g;

		print "\n\n<br><br>E-Mail Versand Log:\n\n<br><br>", $log;
	}

}

# SQL
if($datetime eq '')
{
$sqlupdate="UPDATE DDBUSER.Entsorgung Set telefon='$telefon',name='$name',beschreibung='$beschreibung',
bemerkung='$bemerkung',gebaeude='$gebaeude',geschoss='$geschoss',ort='$ort',
check_bauschrott='$check_bauschrott',check_schrott='$check_schrott',check_sperrmuell='$check_sperrmuell',
check_altpapier='$check_altpapier',check_geheimpapier='$check_geheimpapier',check_gga='$check_gga',
check_fluessig='$check_fluessig',manuell='$manuell',menge_m3='$menge_m3',menge_t='$menge_t',
menge_lesa='$menge_lesa',menge='$menge',geld_kostenstelle='$geld_kostenstelle',
geld_psp_element='$geld_psp_element',geld_co_auftrag='$geld_co_auftrag',email='$email',
erfasst='$erfasst',status='$status',ausfuehrender='$ausfuehrender',amail='$amail',bearbeiter='$bearbeiter',
wunschdatum='$wunschdatum' WHERE ID=$id";
}
else
{
$sqlinsert=("INSERT INTO DDBUSER.Entsorgung_archiv(id,telefon,name,beschreibung,bemerkung,gebaeude,geschoss,ort,check_bauschrott,
check_schrott,check_sperrmuell,check_altpapier,check_geheimpapier,check_gga,check_fluessig,
manuell,menge_m3,menge_t,menge_lesa,menge,wunschdatum,geld_kostenstelle,
geld_psp_element,geld_co_auftrag,email,erfasst,erledigt,status,ausfuehrender,amail,bearbeiter) 
VALUES($id,'$telefon','$name','$beschreibung','$bemerkung','$gebaeude','$geschoss','$ort',
'$check_bauschrott','$check_schrott','$check_sperrmuell','$check_altpapier','$check_geheimpapier',
'$check_gga','$check_fluessig','$manuell','$menge_m3','$menge_t','$menge_lesa','$menge',
'$wunschdatum','$geld_kostenstelle','$geld_psp_element','$geld_co_auftrag','$email',
'$erfasst','$datetime','$status','$ausfuehrender','$amail','$bearbeiter')");
	my $sth2 = $dbh->prepare($sqlinsert);
	$sth2->execute;
	$sqlupdate = "DELETE FROM DDBUSER.Entsorgung WHERE ID=$id";
}
my $sth3 = $dbh->prepare($sqlupdate);
$sth3->execute;
$dbh->commit;

print($cgi->br().$cgi->br().$cgi->br().$cgi->br().$cgi->p({-align => 'center'},"Entsorgungsauftrag mit der laufenden Nummer $id wurde geändert!"));

#print($sql);

print $cgi->end_html();