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();