<?php
/*=======================================================================
 *
 * inc_automailer_related.inc.php
 *
 * Autor: Marc Vollmann
 *
  =======================================================================*/


// Get jobs for automailer
function getAutoMailerJobs($limit) {
  global $db, $logFile;

  // Try to connect request server because of performance
  global $db2;
  getDb2Connection();

  $retArray = array();

  // Limit    
  if (is_numeric($limit) && $limit > 0) :
     $limit = "LIMIT 0," . $limit;
  else :
     $limit = ""; 
  endif;

  // Exclude jobs with special vehicles (E.g.: Do not generate a mail if internal job is generated with vehicle SID=1888)
  $excludedVehicles = getParameterValue("0", "EMAIL_CRVHSID_NO_MAIL", "0");  // "Meta-Global" <=> hq_id = 0
  $whereClauseExcludedVehicles = "";
  if ($excludedVehicles != "") :
     $tmp = spliti(",",$excludedVehicles);
     $lenTmp = count($tmp);
     for ($i = 0; $i < $lenTmp; $i++) {  
        $whereClauseExcludedVehicles .= " jb.cr_sid != '" . $tmp[$i] . "' AND ";
     };
  endif;

  $constAmStarttimeInDays = getParameterValue("0", "AUTOMAILER_STARTTIME_IN_DAYS", "0");  // "Meta-Global" <=> hq_id = 0
  $startTime = getDateTime("datetime_plus_offset", array(0,0,0,0,-($constAmStarttimeInDays),0), "Y-m-d H:i:s");
  // $currentTime = getDateTime("0");

  // jb.jb_automailsent = '999' <=> Mail was sent correctly
  // jb.jb_automailsent = '998' <=> Mail was NOT sent correctly. Process iteration terminated!
  // jb.jb_automailsent = '997' <=> Mail will never be sent because it is a batch job
  // Otherwise number is equal to the number of faults.
  $sqlquery = "SELECT jb.jb_id, usr.usr_email, jb.cr_sid, jb.hq_id, cs.cs_jbstatusmail2csc, cscad.cscad_email"
            . " FROM job AS jb, costcenter AS csc, customer AS cs, costcenteraddress AS cscad, employee AS emp, user AS usr"
            . " WHERE jb.jb_status = '2'                                     AND"
            . "       jb.jb_finishtime > '" . $startTime . "'                AND"
            . "       jb.jb_automailsent != '997'                            AND"
            . "       jb.jb_automailsent != '998'                            AND"
            . "       jb.jb_automailsent != '999'                            AND"
            .         $whereClauseExcludedVehicles
            . "       (ISNULL(jb.jb_storno) OR jb.jb_storno = '0')           AND"            
            . "       jb.csc_id_related = csc.csc_id                         AND"
            . "       csc.cs_id = cs.cs_id                                   AND"
            . "       cs.cs_jbstatusmail = '1'                               AND"
            . "       cs.cs_admin = emp.emp_id                               AND"
            . "       emp.usr_id = usr.usr_id                                AND"
            . "       csc.csc_id = cscad.csc_id                              AND"
            . "       cscad.adt_id = '2'                                     "
            . " ORDER BY jb.jb_finishtime " . $limit;

  $result = $db2->query($sqlquery);
  if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
  while ($row = $result->fetch_assoc()):
        $retArray[] = array($row["jb_id"], $row["usr_email"], $row["cr_sid"], $row["hq_id"], $row["cs_jbstatusmail2csc"], $row["cscad_email"]);
  endwhile;
  $result->free();
  return $retArray;
}  


// Get jobs for automailer
function getAutoMailerJobs2($limit) {
  global $db, $logFile;
  
  // Try to connect request server because of performance
  global $db2;
  getDb2Connection();
  
  $retArray = array();

  // Limit    
  if (is_numeric($limit) && $limit > 0) :
     $limit = "LIMIT 0," . $limit;
  else :
     $limit = ""; 
  endif;

  // Exclude jobs with special vehicles (E.g.: Do not generate a mail if internal job is generated with vehicle SID=1888)
  $excludedVehicles = getParameterValue("0", "EMAIL_CRVHSID_NO_MAIL", "0");  // "Meta-Global" <=> hq_id = 0
  $whereClauseExcludedVehicles = "";
  if ($excludedVehicles != "") :
     $tmp = spliti(",",$excludedVehicles);
     $lenTmp = count($tmp);
     for ($i = 0; $i < $lenTmp; $i++) {  
        $whereClauseExcludedVehicles .= " jb.cr_sid != '" . $tmp[$i] . "' AND ";
     };
  endif;

  $constAmStarttimeInDays = getParameterValue("0", "AUTOMAILER_STARTTIME_IN_DAYS", "0");  // "Meta-Global" <=> hq_id = 0
  $startTime = getDateTime("datetime_plus_offset", array(0,0,0,0,-($constAmStarttimeInDays),0), "Y-m-d H:i:s");
  // $startTime = getDateTime("3") . " 00:00:00";

  // jb.jb_automailsent2 = '999' <=> Mail was sent correctly
  // jb.jb_automailsent2 = '998' <=> Mail was NOT sent correctly. Process iteration terminated!
  // Otherwise number is equal to the number of faults.
  $sqlquery = "SELECT jb.jb_id, usr.usr_email, jb.cr_sid, jb.hq_id, cs.cs_jbstatusmail2csc, cscad.cscad_email"
            . " FROM job AS jb, costcenter AS csc, customer AS cs, costcenteraddress AS cscad, employee AS emp, user AS usr"
            . " WHERE jb.jb_status = '1'                                     AND"
            . "       jb.jb_taketime > '" . $startTime . "'                  AND"
            . "       jb.jb_automailsent2 != '998'                           AND"
            . "       jb.jb_automailsent2 != '999'                           AND"
            .         $whereClauseExcludedVehicles
            . "       (ISNULL(jb.jb_storno) OR jb.jb_storno = '0')           AND"
            . "       jb.csc_id_related = csc.csc_id                         AND"
            . "       csc.cs_id = cs.cs_id                                   AND"
            . "       cs.cs_jbstatusmail2 = '1'                              AND"
            . "       cs.cs_admin = emp.emp_id                               AND"
            . "       emp.usr_id = usr.usr_id                                AND"
            . "       csc.csc_id = cscad.csc_id                              AND"
            . "       cscad.adt_id = '2'                                     AND"
            . "       ((usr.usr_email != '' AND cs.cs_jbstatusmail2csc = '0') OR"
            . "        (cscad.cscad_email != '' AND cs.cs_jbstatusmail2csc = '1'))"
            . " ORDER BY jb.jb_taketime " . $limit;
            
  $result = $db2->query($sqlquery);
  if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
  while ($row = $result->fetch_assoc()):
        $retArray[] = array($row["jb_id"], $row["usr_email"], $row["cr_sid"], $row["hq_id"], $row["cs_jbstatusmail2csc"], $row["cscad_email"]);
  endwhile;
  $result->free();
  return $retArray;
}  


// Get jobs for automailer
function getAutoMailerJobs3($limit) {
  global $db, $logFile;
  
  // Try to connect request server because of performance
  global $db2;
  getDb2Connection();
  
  $retArray = array();

  // Limit    
  if (is_numeric($limit) && $limit > 0) :
     $limit = "LIMIT 0," . $limit;
  else :
     $limit = ""; 
  endif;

  // Exclude jobs with special vehicles (E.g.: Do not generate a mail if internal job is generated with vehicle SID=1888)
  $whereClauseExcludedVehicles = "";
  /*
  $excludedVehicles = getParameterValue("0", "EMAIL_CRVHSID_NO_MAIL", "0");  // "Meta-Global" <=> hq_id = 0
  if ($excludedVehicles != "") :
     $tmp = spliti(",",$excludedVehicles);
     $lenTmp = count($tmp);
     for ($i = 0; $i < $lenTmp; $i++) {  
        $whereClauseExcludedVehicles .= " jb.cr_sid != '" . $tmp[$i] . "' AND ";
     };
  endif;
  */

  // $constAmStarttimeInDays = getParameterValue("0", "AUTOMAILER_STARTTIME_IN_DAYS", "0");  // "Meta-Global" <=> hq_id = 0
  $startTime = getDateTime("datetime_plus_offset", array(-2,0,0,0,0,0), "Y-m-d H:i:s");
  // $startTime = getDateTime("3") . " 00:00:00";

  // jb.jb_automailsent3 = '999' <=> Mail was sent correctly
  // jb.jb_automailsent3 = '998' <=> Mail was NOT sent correctly. Process iteration terminated!
  // Otherwise number is equal to the number of faults.
  $sqlquery = "SELECT jb.jb_id, usr.usr_email, jb.cr_sid, jb.hq_id, cs.cs_jbstatusmail2csc, cscad.cscad_email"
            . " FROM job AS jb, costcenter AS csc, customer AS cs, costcenteraddress AS cscad, employee AS emp, user AS usr"
            . " WHERE (jb.jb_status = '8' OR jb.jb_status = '9' OR jb.jb_status = '0' OR jb.jb_status = '1') AND"
            . "       jb.jb_ordertime > '" . $startTime . "'                 AND"
            . "       (ISNULL(jb.jb_offer) OR jb.jb_offer = '0')             AND"
            . "       jb.jb_automailsent3 != '998'                           AND"
            . "       jb.jb_automailsent3 != '999'                           AND"
            .         $whereClauseExcludedVehicles
            . "       (ISNULL(jb.jb_storno) OR jb.jb_storno = '0')           AND"
            . "       jb.csc_id_related = csc.csc_id                           AND"
            . "       csc.cs_id = cs.cs_id                                   AND"
            . "       cs.cs_jbstatusmail3 = '1'                              AND"
            . "       cs.cs_admin = emp.emp_id                               AND"
            . "       emp.usr_id = usr.usr_id                                AND"
            . "       csc.csc_id = cscad.csc_id                              AND"
            . "       cscad.adt_id = '2'                                     AND"
            . "       ((usr.usr_email != '' AND cs.cs_jbstatusmail2csc = '0') OR"
            . "        (cscad.cscad_email != '' AND cs.cs_jbstatusmail2csc = '1'))"
            . " ORDER BY jb.jb_ordertime " . $limit;
            
  $result = $db2->query($sqlquery);
  // $result = $db2->query($sqlquery . " UNION " . $sqlquery2);
  if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
  while ($row = $result->fetch_assoc()):
        $retArray[] = array($row["jb_id"], $row["usr_email"], $row["cr_sid"], $row["hq_id"], $row["cs_jbstatusmail2csc"], $row["cscad_email"]);
  endwhile;
  $result->free();
  return $retArray;
}


// Get jobs for automailer
function getAutoMailerStations($limit) {
  global $db, $logFile;

  // Try to connect request server because of performance
  global $db2;
  getDb2Connection();

  $retArray = array();

  // Limit    
  if (is_numeric($limit) && $limit > 0) :
     $limit = "LIMIT 0," . $limit;
  else :
     $limit = ""; 
  endif;

  // Exclude jobs with special vehicles (E.g.: Do not generate a mail if internal job is generated with vehicle SID=1888)
  $excludedVehicles = getParameterValue("0", "EMAIL_CRVHSID_NO_MAIL", "0");  // "Meta-Global" <=> hq_id = 0
  $whereClauseExcludedVehicles = "";
  if ($excludedVehicles != "") :
     $tmp = spliti(",",$excludedVehicles);
     $lenTmp = count($tmp);
     for ($i = 0; $i < $lenTmp; $i++) {  
        $whereClauseExcludedVehicles .= " jb.cr_sid != '" . $tmp[$i] . "' AND ";
     };
  endif;

  // $constAmStarttimeInDays = getParameterValue("0", "AUTOMAILER_STARTTIME_IN_DAYS", "0");  // "Meta-Global" <=> hq_id = 0
  $constAmStarttimeInDays = 1;
  $startTime = getDateTime("datetime_plus_offset", array(0,0,0,0,-($constAmStarttimeInDays),0), "Y-m-d H:i:s");
  // $currentTime = getDateTime("0");

  // gdc.gdc_content = '999' <=> Mail was sent correctly
  // gdc.gdc_content = '998' <=> Mail was NOT sent correctly. Process iteration terminated!
  // gdc.gdc_content = '997' <=> Mail will never be sent because it is a batch job
  // Otherwise number is equal to the number of faults.
  $sqlquery = "SELECT jb.jb_id, tr.tr_id, tr.tr_sort, usr.usr_email, jb.jb_ordertime, jb.cr_sid, jb.hq_id, cs.cs_jbstatusmail2csc, cscad.cscad_email, IFNULL(gdc.gdc_content,'') AS gdc_content"
            . " FROM job AS jb, costcenter AS csc, customer AS cs, costcenteraddress AS cscad, employee AS emp, user AS usr,"
            . "      tour AS tr LEFT JOIN genericdatacontainer AS gdc ON gdc.gdc_obj_id = tr.tr_id AND gdc.gdc_obj_type = 'tr' AND gdc.gdc_gen_fieldname = 'auto_mail_station'"
            . " WHERE jb.jb_status = '1'                             AND"
            . "       jb.jb_ordertime >= '" . $startTime . "'        AND"
            . "       jb.jb_automailsent != '997'                    AND"
            . "       jb.jb_automailsent != '998'                    AND"
            . "       jb.jb_automailsent != '999'                    AND"
            .         $whereClauseExcludedVehicles
            . "       (ISNULL(jb.jb_storno) OR jb.jb_storno = '0')   AND"            
            . "       jb.csc_id_related = csc.csc_id                   AND"
            . "       csc.cs_id = cs.cs_id                           AND"
            . "       SUBSTRING(cs.cs_jbstatusmail_fields,5,1) = '1' AND"
            . "       cs.cs_admin = emp.emp_id                       AND"
            . "       emp.usr_id = usr.usr_id                        AND"
            . "       csc.csc_id = cscad.csc_id                      AND"
            . "       cscad.adt_id = '2'                             AND"
            . "       jb.jb_id = tr.jb_id                            AND"
            . "       tr.tr_status = '1'                             AND"
            . "       tr.tr_finishtime > '" . $startTime . "'        AND"
            . "       (usr_email != '' OR cscad_email != '') AND"
            . "       (NOT EXISTS (SELECT * FROM genericdatacontainer AS gdc WHERE gdc.gdc_obj_id = tr.tr_id AND gdc.gdc_obj_type = 'tr' AND gdc.gdc_gen_fieldname = 'auto_mail_station'))"
            . " ORDER BY tr.tr_finishtime " . $limit;

  $result = $db2->query($sqlquery);
  // $result = $db2->query($sqlquery . " UNION " . $sqlquery2);
  if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
  while ($row = $result->fetch_assoc()):
        $retArray[] = array($row["jb_id"], $row["tr_sort"], $row["usr_email"], $row["cr_sid"], $row["hq_id"], $row["cs_jbstatusmail2csc"], $row["cscad_email"], $row["tr_id"], $row["jb_ordertime"]);
  endwhile;
  $result->free();
  return $retArray;
}


// Gets the (HTML) mail text for a special job and jb state
function getJobMailText ($currentHqId, $job_id, $job_status) {
  global $db, $hq_id, $currentHqId, $emp_id, $usr_id, $userTypeName, $outputFormatField, $f_act, $automailer;
	global $dbhistory, $dbh_jb, $dbh_tr, $dbh_trs, $dbh_cdt, $dbh_jbc;
	global $jobentry, $tourentry, $jobcosts, $courierentry, $employeeentry, $tourentryArray, $tourDisplayClassicMode;

  // Allow sending mail regarding jb_status = 1
  if ($automailer == "0" && $mailTextJobStatus == "") :    // Pay attention
     if ($job_status == "1") :
        $maskExtendJbStatusPickup = getParameterValue("0", "MASK_MAIL_JOB_STATE_PICKUP", $currentHqId);
        if ($maskExtendJbStatusPickup == "1") :
           $mailTextJobStatus = "1";
        endif;
     endif;
     if ($job_status == "9") :
        $maskExtendJbStatusPickup = getParameterValue("0", "MASK_MAIL_JOB_STATE_DISPOSITION", $currentHqId);
        if ($maskExtendJbStatusPickup == "1") :
           $mailTextJobStatus = "9";
        endif;
     endif;
  endif;

  if ($job_status == "2" || $mailTextJobStatus != "") :

     // Check existence of the job in history table space
     $dbhistory = checkHistoryLevel($dbhistory, "jb", $job_id);
     $dbhistory = getDBNames($dbhistory);
     // Decision to use the job archive or normal tables
     getDBNames($dbhistory);

     // If job ($job_id) is exported, then look for existing content in history field in table "job2"
     $takeRealJobData = checkForTakingRealOrHistoricJobData("1");
     if ($takeRealJobData) :
        $currentJobParameterState = getJobSnapshot($job_id);

     else :
        $tmpSqlquery = "SELECT tr.tr_sign, tr.tr_sort"
                       . " FROM " . $dbh_tr . " AS tr"
                       . " WHERE tr.jb_id = '" . $job_id . "'"
                       . " ORDER BY tr.tr_sort";
  
        $result = $db->query($tmpSqlquery);
        if (DB::isError($result)) die ("$PHP_SELF 5: {$tmpSqlquery} " . $result->getMessage() . ": " . $tmpSqlquery);
        
        $tmpSignPath = "../temp/signs/";
        if (SIGNS_PATH != "") : $tmpSignPath = SIGNS_PATH; endif;
        while ($row = $result->fetch_assoc()):
           $rawCoord = splitRawCoordinates ($row[tr_sign]);
           $maxCoord = checkMaxCoordinates($rawCoord);            
  
           $imgFilename = $tmpSignPath . $job_id . "_" . $row[tr_sort] . ".png";
           $imgFilenames[] = $imgFilename;
           $im = createSignImage($rawCoord);
           imagepng ($im, $imgFilename);
        endwhile;
        $result->free();
     endif;
    
     $mailCssFontType = getParameterValue("0", "MAIL_CSS_FONT_TYPE", $currentHqId);
     if ($mailCssFontType == "") : $mailCssFontType = "Verdana, Arial, Helvetica, sans-serif"; endif;
  
     $mailtext  = "<html><head><meta text/html>";
     $mailtext .= "<style type=\"text/css\">";
     $mailtext .= ".f8np1 { font-family: " . $mailCssFontType . "; font-size: 8pt; font-weight: normal; padding: 1px}";
     $mailtext .= ".f8bp1 { font-family: " . $mailCssFontType . "; font-size: 8pt; font-weight: bold; padding: 1px}";
     $mailtext .= ".f10np1 { font-family: " . $mailCssFontType . "; font-size: 10pt; font-weight: normal; padding: 1px}";
     $mailtext .= ".f10bp1 { font-family: " . $mailCssFontType . "; font-size: 10pt; font-weight: bold; padding: 1px}";
     $mailtext .= ".f8np1_red { font-family: " . $mailCssFontType . "; font-size: 8pt; font-weight: normal; padding: 1px; color: #FF0000;}";
     $mailtext .= "</style>";
     $mailtext .= "</head><body><h4>";
     
     // BEGIN Job data
     $jobtext = "";
     
     // Definition of the output place of the job data
     $mailJobData = getParameterValue("0", "MAIL_JOBDATA", "0");
     if ($mailJobData == "") : $mailJobData = "0"; endif;
  
     // Transport date or current date
     $mailJobDate = getParameterValue("0", "MAIL_JOBDATE", "0");
  
     if ($mailJobDate == "1") :
        $job_ordertime = getFieldValueFromId("job","jb_id",$job_id,"jb_ordertime");
        $jobtext .= "<div class=\"f10bp1\">" . getLngt("Transportdatum:") . " " . formatOutput($job_ordertime, "datetime", 4) . "</div>";
     else :
        $jobtext .= "<div class=\"f10bp1\">" . getLngt("Transportdatum:") . " " . getDateTime(5) . "</div>";
     endif;
     $jobtext .= "<div style=\"width:100%; height:10px;\"> </div>";
     
     $jobtext .= "<table><tr><td valign=\"top\">";
     $jobtext .= "<span class=\"f8bp1\">" . getLngt("AUFTRAG:") . "</span><br>";
     $jobtext .= "<table class=\"f8np1\">" . $jobentry . "</table><br>";
     $jobtext .= "</td><td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td><td valign=\"top\">";
     if ($courierentry != "" && $mailTextJobStatus != "0" && $mailTextJobStatus != "8" && $mailTextJobStatus != "9") :
        $jobtext .= "<span class=\"f8bp1\">" . getLngt("TRANSPORTEUR:") . "</span><br>";
        $jobtext .= "<table class=\"f8np1\">" . $courierentry . "</table><br>";
     endif;
     $jobtext .= "</td></tr></table>";
     if ($tourentry != "") :
        $jobtext .= "<span class=\"f8bp1\">" . getLngt("TOUR:") . "</span><br>";
        $jobtext .= "<table class=\"f8np1\">" . $tourentry . "</table><br>";
     endif;
     if ($jobcosts != "") :
        $csJbstatusmailPrice = getFieldValueFromId("customer","cs_id",$csIdPayer,"cs_jbstatusmail_price");
        if ($csJbstatusmailPrice == "1") :
           $jobtext .= "<span class=\"f8bp1\">" . getLngt("KOSTEN:") . "</span><br>";
           $jobtext .= "<table class=\"f8np1\">" . $jobcosts . "</table><br>";
        endif;
     endif;
     // END Job data
     
     $mailCssFontClass = getParameterValue("0", "MAIL_CSS_FONT_CLASS", $currentHqId);
     if ($mailCssFontClass == "") : $mailCssFontClass = "f8bp1"; endif;
     
     $mailPreSalutationText = getParameterValue("0", "MAIL_PRE_SALUTATION_TEXT", $currentHqId);
     if ($mailPreSalutationText == "") : $mailPreSalutationText = getLngt("Sehr geehrte Damen und Herren,"); endif;
     $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailPreSalutationText . "</div>";
     $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
     
     if ($mailTextJobStatus == "1") :
        $mailTextPickup = getParameterValue("0", "MAIL_TEXT_PICKUP", $currentHqId);
        if ($mailTextPickup == "") : $mailTextPickup = getLngt("Die Abholung der Ware erfolgte."); endif;
        $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailTextPickup . "</div>";
     elseif ($mailTextJobStatus == "8" || $mailTextJobStatus == "9") :
        $mailTextDisposition = getParameterValue("0", "MAIL_TEXT_DISPOSITION", $currentHqId);
        if ($mailTextDisposition == "") : $mailTextDisposition = getLngt("Ihr Transportauftrag befindet sich in der Vermittlung."); endif;
        $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailTextDisposition . "</div>";
     elseif ($mailTextJobStatus == "ALL") :
        $mailTextDisposition = getParameterValue("0", "MAIL_TEXT_ALL_STATIONS", $currentHqId);
        if ($mailTextDisposition == "") : $mailTextDisposition = getLngt("Die") . " " . $tour_sort . ". " . getLngt("Station Ihres Auftrags wurde erledigt."); endif;
        $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailTextDisposition . "</div>";
     elseif ($mailTextJobStatus == "CHANGE") :
        $mailTextDisposition = getParameterValue("0", "MAIL_TEXT_CHANGE", $currentHqId);
        if ($mailTextDisposition == "") : $mailTextDisposition = getLngt("Ihr Transportauftrag wurde ge�ndert."); endif;
        $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailTextDisposition . "</div>";
     else :
        $mailTextCompletion = getParameterValue("0", "MAIL_TEXT_COMPLETION", $currentHqId);
        if ($mailTextCompletion == "") : $mailTextCompletion = getLngt("Ihr Transportauftrag wurde ausgef�hrt."); endif;
        $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailTextCompletion . "</div>";
        // Extended text
        $mailTextCompletion2 = getParameterValue("0", "MAIL_TEXT_COMPLETION_2", $currentHqId);
        if ($mailTextCompletion2 != "") : 
           $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
           $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailTextCompletion2 . "</div>";
           $mailTextCompletion3 = getParameterValue("0", "MAIL_TEXT_COMPLETION_3", $currentHqId);
           if ($mailTextCompletion3 != "") : 
              $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailTextCompletion3 . "</div>"; 
              $mailTextCompletion4 = getParameterValue("0", "MAIL_TEXT_COMPLETION_4", $currentHqId);
              if ($mailTextCompletion4 != "") : 
                 $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailTextCompletion4 . "</div>";
                 $mailTextCompletion5 = getParameterValue("0", "MAIL_TEXT_COMPLETION_5", $currentHqId);
                 if ($mailTextCompletion5 != "") : 
                    $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailTextCompletion5 . "</div>";
                 endif;
              endif;
           endif;
           $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
        endif;
     endif;
     // $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
     
     $mailTextJobdata = getParameterValue("0", "MAIL_TEXT_JOBDATA", $currentHqId);
     if ($mailTextJobdata == "") : $mailTextJobdata = getLngt("Anbei �bersenden wir Ihnen die Auftragsdaten."); endif;
     $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailTextJobdata . "</div>";
     $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
  
     // Special usage for requesting state of the customer service
     $mailDisplayModeEmployeeData = getParameterValue("0", "MAIL_DISPLAY_MODE_EMPLOYEE_DATA", $currentHqId);
     if ($mailDisplayModeEmployeeData == "") : $mailDisplayModeEmployeeData = "0"; endif;
     if ($job_status == "2") :
        $mailSuccessRequest = getParameterValue("0", "MAIL_SUCCESS_REQUEST", $currentHqId);
        if ($mailSuccessRequest != "") :
           // Only if a mail success request does exist in the database (line above) then check if it is enabled for the payer of the job
           $csJbstatusmailEmp = getFieldValueFromId("customer","cs_id",$csIdPayer,"cs_jbstatusmail_emp");
           if ($csJbstatusmailEmp == "1") :
              // Get the user data of the current employee of the job
              $job_usr_id = getFieldValueFromId($dbh_jb,"jb_id",$job_id,"emp_id");
              if ($job_usr_id != "") :
                 $mailEmployeeData = getFieldsValueFromId("user", "usr_id", $job_usr_id, array("usr_firstname","usr_name","usr_phone","usr_email","usr_fax"));
                 if ($mailEmployeeData[0] == "" || $mailEmployeeData[1] == "" || $mailEmployeeData[2] == "" || $mailEmployeeData[3] == "") :
                    $mailDisplayModeEmployeeData = "";
                 endif;
                 if ($mailDisplayModeEmployeeData == "0") :
                    $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailSuccessRequest . "</div>";
                    $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailEmployeeData[0] . " " . $mailEmployeeData[1] . "</div>";
                    $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . getLngt("Telefon") . ": " . $mailEmployeeData[2] . "</div>";
                    // $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . getLngt("Fax") . ": " . $mailEmployeeData[4] . "</div>";
                    $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . getLngt("Email") . ": " . $mailEmployeeData[3] . "</div>";
                    $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
                 endif;
              endif;
           endif;
        endif;
     else :
        $mailDisplayModeEmployeeData = "";
     endif;
  
     if ($mailJobData == "1") :
        $mailtext .= "<div style=\"width:100%; height:30px;\"> </div>";
        $mailtext .= $jobtext;
        $mailtext .= "<div style=\"width:100%; height:20px;\"> </div>";
     endif;
     
     if ($mailDisplayModeEmployeeData == "1") :
        $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailSuccessRequest . "</div>";
        $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
     endif;
  
     // Signature
     $mailTextRegards = getParameterValue("0", "MAIL_TEXT_REGARDS", $currentHqId);
     if ($mailTextRegards == "") : $mailTextRegards = getLngt("Mit freundlichem Gru�,"); endif;
     $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailTextRegards . "</div>";
     if ($mailDisplayModeEmployeeData == "1" && $mailEmployeeData[0] != "" && $mailEmployeeData[1] != "") :
        $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
        $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . $mailEmployeeData[0] . " " . $mailEmployeeData[1] . "</div>";
     else :
        $mailSalutationText = getParameterValue("0", "MAIL_SALUTATION_TEXT", $currentHqId);
        $mailtext .= "<div class=\"" . $mailCssFontClass . "\">" . getLngt($mailSalutationText) . "</div>";
     endif;
     $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
  
     // HOMEPAGE
     $mailFooterWebPage = getParameterValue("0", "MAIL_FOOTER_WEB_PAGE", $currentHqId);
  
     // LOGO
     $logoName = getParameterValue("0", "IMG_LOGO_NAME", $currentHqId);
     $logoHeight = getParameterValue("0", "IMG_LOGO_HEIGHT", $currentHqId);
     $logoWidth = getParameterValue("0", "IMG_LOGO_WIDTH", $currentHqId);
     if ($logoName != "" && $logoHeight != "" && $logoWidth != "") :
        $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
        if ($mailFooterWebPage != "") : 
           $mailtext .= "<div><a href=\"http://" . $mailFooterWebPage . "\"><img src=\"../images/external/" . $logoName . "\" border=\"0\" height=\"" . $logoHeight . "\" width=\"" . $logoWidth . "\"></a></div>";
        else :
           $mailtext .= "<div><img src=\"../images/external/" . $logoName . "\" border=\"0\" height=\"" . $logoHeight . "\" width=\"" . $logoWidth . "\"></div>";
        endif;
        $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
     endif;
  
     
     $mailFooterEnabled = getParameterValue("0", "MAIL_FOOTER_ENABLED", $currentHqId);
     if ($mailFooterEnabled == "1") :
        $mailFooterAddress = getParameterValue("0", "MAIL_FOOTER_ADDRESS", $currentHqId);
        if ($mailFooterAddress != "") :
           $mailtext .= "<table style=\"font-family: Arial; font-size: 10pt; font-weight: normal; padding: 1px; color: #000000;\">";
           $mailtext .= $mailFooterAddress;
           $mailtext .= "</table>";
           $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
        endif;
        $mailFooterContact = getParameterValue("0", "MAIL_FOOTER_CONTACT", $currentHqId);
        if ($mailFooterContact != "") :                  
           if ($mailDisplayModeEmployeeData == "1" && ($mailEmployeeData[2] != "" || $mailEmployeeData[3] != "" || $mailEmployeeData[4] != "")) :
              $mailtext .= "<table>";
              if ($mailEmployeeData[2] != "") : 
                 $mailtext .= "<tr><td><span class=\"" . $mailCssFontClass . "\">" . getLngt("Telefon") . ": " . $mailEmployeeData[2] . "</span></td></tr>";
              endif;
              if ($mailEmployeeData[4] != "") : 
                 $mailtext .= "<tr><td><span class=\"" . $mailCssFontClass . "\">" . getLngt("Fax") . ": " . $mailEmployeeData[4] . "</span></td></tr>";
              endif;
              if ($mailEmployeeData[3] != "") : 
                 $mailtext .= "<tr><td><span class=\"" . $mailCssFontClass . "\">" . getLngt("Email") . ": " . $mailEmployeeData[3] . "</span></td></tr>";
              endif;
              // $mailFooterWebPage = getParameterValue("0", "MAIL_FOOTER_WEB_PAGE", $currentHqId);
              if ($mailFooterWebPage != "") : 
                 // $mailtext .= "<tr><td><span class=\"" . $mailCssFontClass . "\">" . $mailFooterWebPage . "</span></td></tr>";
                 $mailtext .= "<tr><td><span class=\"" . $mailCssFontClass . "\"><a href=\"http://" . $mailFooterWebPage . "\">" . $mailFooterWebPage . "</a></span></td></tr>";
              endif;
              $mailtext .= "</table>";
           else :
              $mailtext .= "<table style=\"font-family: Arial; font-size: 10pt; font-weight: normal; padding: 1px; color: #000000;\">";
              $mailtext .= $mailFooterContact;
              $mailtext .= "</table>";
           endif;                  
           $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
        endif;
        $mailFooterImpressum = getParameterValue("0", "MAIL_FOOTER_IMPRESSUM", $currentHqId);
        if ($mailFooterImpressum != "") :
           $mailtext .= "<table style=\"font-family: Arial; font-size: 7pt; font-weight: normal; padding: 1px; color: #000000;\">";
           $mailtext .= $mailFooterImpressum;
           $mailtext .= "</table>";
           $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
        endif;
        $mailFooterResponsibility = getParameterValue("0", "MAIL_FOOTER_RESPONSIBILITY", $currentHqId);
        if ($mailFooterResponsibility == "") : $mailFooterResponsibility = getParameterValue("0", "MAIL_FOOTER_RESPONSIBILITY", "0"); endif;
        if ($mailFooterResponsibility != "") : 
           $mailtext .= "<div style=\"font-family: Arial; font-size: 7pt; font-weight: normal; padding: 1px; color: #000000;\">" . $mailFooterResponsibility . "</div>";
           // Extended text
           $mailFooterResponsibility2 = getParameterValue("0", "MAIL_FOOTER_RESPONSIBILITY_2", $currentHqId);
           if ($mailFooterResponsibility2 == "") : $mailFooterResponsibility2 = getParameterValue("0", "MAIL_FOOTER_RESPONSIBILITY_2", "0"); endif;
           if ($mailFooterResponsibility2 != "") : 
              $mailtext .= "<div style=\"font-family: Arial; font-size: 7pt; font-weight: normal; padding: 1px; color: #000000;\">" . $mailFooterResponsibility2 . "</div>";
              $mailFooterResponsibility3 = getParameterValue("0", "MAIL_FOOTER_RESPONSIBILITY_3", $currentHqId);
              if ($mailFooterResponsibility3 == "") : $mailFooterResponsibility3 = getParameterValue("0", "MAIL_FOOTER_RESPONSIBILITY_3", "0"); endif;
              if ($mailFooterResponsibility3 != "") : 
                 $mailtext .= "<div style=\"font-family: Arial; font-size: 7pt; font-weight: normal; padding: 1px; color: #000000;\">" . $mailFooterResponsibility3 . "</div>"; 
                 $mailFooterResponsibility4 = getParameterValue("0", "MAIL_FOOTER_RESPONSIBILITY_4", $currentHqId);
                 if ($mailFooterResponsibility4 == "") : $mailFooterResponsibility4 = getParameterValue("0", "MAIL_FOOTER_RESPONSIBILITY_4", "0"); endif;
                 if ($mailFooterResponsibility4 != "") : 
                    $mailtext .= "<div style=\"font-family: Arial; font-size: 7pt; font-weight: normal; padding: 1px; color: #000000;\">" . $mailFooterResponsibility4 . "</div>";
                    $mailFooterResponsibility5 = getParameterValue("0", "MAIL_FOOTER_RESPONSIBILITY_5", $currentHqId);
                    if ($mailFooterResponsibility5 == "") : $mailFooterResponsibility5 = getParameterValue("0", "MAIL_FOOTER_RESPONSIBILITY_5", "0"); endif;
                    if ($mailFooterResponsibility5 != "") : 
                       $mailtext .= "<div class=\"" . $mailFooterImpressum . "\">" . $mailFooterResponsibility5 . "</div>";
                    endif;
                 endif;
              endif;
              $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
           endif;
        endif;
        $mailFooterThinkGreen = getParameterValue("0", "MAIL_FOOTER_THINK_GREEN", $currentHqId);
        if ($mailFooterThinkGreen != "") :
           $mailtext .= $mailFooterThinkGreen;
           $mailtext .= "<div style=\"width:100%; height:10px;\"> </div>";
        endif;
        $mailtext .= "<div style=\"width:100%; height:30px;\"> </div>";               
     endif;
  
     if ($mailJobData == "0") :
        $mailtext .= $jobtext;
     endif;
  
     $mailtext .= "</body></html>";
    
  endif;
  
  return $mailtext;
}
?>