<?php
/*=======================================================================
 *
 * auto_trigger_crvh_fields.php
 *
 * Autor: Marc Vollmann
 *
 =======================================================================*/

include_once ("../include/mcglobal.inc.php");

$autoMode = false;
if (!isset($argv[1]) || $argv[1] == "") :
   include_once ("../include/auth.inc.php");
   $autoMode = false;
else :
   if ($argv[1] == "acapella7890") :
       $autoMode = true;
   endif;
endif;

include_once ('../include/email/htmlMimeMail.php');
// include_once ('../include/inc_vehicle.inc.php');


// Returns the statement for a vehicle list
function getStmtCrCrvhList ($whereClause = "", $orderByClause = "") {
  $retStmt = "";
  if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
  if ($orderByClause != "") : $orderByClause = " ORDER BY " . $orderByClause; endif;
  $retStmt = "SELECT cr.cr_id, cr.cr_eid, usr.usr_name, usr.usr_firstname,"
             . "     crvh.crvh_id, crvh.vht_id, crvh.cr_id, crvh.crvh_id_inv, crvh.crvh_sort, crvh.crvh_sid, crvh.crvh_comm_no,"
             . "     crvh.crvh_vh_sign, crvh.crvh_vh_name, crvh.crvh_licence_date, crvh.crvh_owner, crvh.crvh_env_badge_level, crvh.crvh_prov,"
             . "     crvh.crvh_payload, crvh.crvh_totalweight, crvh.crvh_length, crvh.crvh_width,"
             . "     crvh.crvh_height, crvh.crvh_position, crvh.crvh_aperture_height_side, crvh.crvh_aperture_height_rear, crvh.crvh_mobile, crvh.crvh_insurance_name,"
             . "     crvh.crvh_insurance_no, crvh.crvh_filter, crvh.crvh_service, crvh.crvh_remark, crvh.crvh_freetext_1, crvh.crvh_special,"
             . "     crvh.crvh_funding_state, crvh.crvh_funding_final_installment, crvh.crvh_funding_residual_value, crvh.crvh_pda_usage, crvh.crvh_system_fee, crvh.crvh_drivetype,"
             . "     crvh.crvh_partner_premium, crvh.crvh_partner_commission, crvh.crvh_advertising_allowance, crvh.crvh_transport_insurance_amount,"
             . "     crvh.crvh_business_liability_insurance_amount, crvh.crvh_business_liability_insurance_date, crvh.crvh_system_fee_amount"
             . " FROM couriervehicle AS crvh, courier AS cr, company AS cmp, user AS usr"
             . " WHERE cr.cr_id = crvh.cr_id AND cr.cmp_id = cmp.cmp_id AND cmp.cmp_archived = '0' AND cmp.cmp_authenticated = '1' AND cr.usr_id = usr.usr_id " .$whereClause
             .   $orderByClause;
  return $retStmt;
}


// Check for headquarter ID in $argv[2]
$hqIdMail = trim($argv[2]);

// Check for receiver (courier or truck)
$courierOrTruckOnly = strtoupper(trim($argv[3]));
if ($courierOrTruckOnly != "K" && $courierOrTruckOnly != "L") : $courierOrTruckOnly = ""; endif;


if ($hqIdMail != "" && (is_numeric($hqIdMail) || $hqIdMail == "HQ_ALL")) :

   if ($autoMode || $usr_id == "00000") :
     
      // Get user
      if (!$autoMode) :
         $usrName = getFieldValueFromId("user","usr_id",$usr_id,"usr_name");
         $usrFirstname = getFieldValueFromId("user","usr_id",$usr_id,"usr_firstname");
      endif;

      // Get headquarters
      $hqName = "[ALLE]";
      if (is_numeric($hqIdMail) && $hqIdMail > "0") :
         $hqName = getFieldValueFromId("headquarters","hq_id",$hqIdMail,"hq_name");
      endif;

      // Init
      $currentDateTime = getDateTime("0");
      $sendPerMail = false;
      $path = "../temp/download/";
      $f_secretFileName = "CRVH_LIST_" . getDateTime("6") . ".csv";

      // List of all vehicles with special fields
      if (true) :

         $typeArr = defineOutputFormats();

         $triggerArray = array();
         $csvArray = array();

         // Insert header
         $headerArray = array("SID", "EID", "Name", "Vorname", "Partnerprämie", "Partnerprovision", "Werbevergütung", "Transportversicherung", "Betriebshaftpflichtversicherung", "BHV-Datum", "Systemgebühr");
         $fieldArray = array("crvh_sid", "cr_eid", "usr_name", "usr_firstname", "crvh_partner_premium", "crvh_partner_commission", "crvh_advertising_allowance", "crvh_transport_insurance_amount", "crvh_business_liability_insurance_amount", "crvh_business_liability_insurance_date", "crvh_system_fee_amount");
         $triggerArray[] = $headerArray;
         $csvArray[] = implode(";", $headerArray);

         // Check for headquaters
         $whereClause = "cr.hq_id = '" . $hqIdMail . "'";
         if ($hqIdMail == "HQ_ALL") :
            $whereClause = "";
         endif;
         // Check for split regarding vehicle type
         if ($courierOrTruckOnly == "K" || $courierOrTruckOnly == "L") :
            if ($whereClause != "") : $whereClause .= " AND "; endif;
            if ($courierOrTruckOnly == "K") :
               $whereClause .= "crvh.vht_id < '7'";
            else :
               $whereClause .= "crvh.vht_id >= '7' AND crvh.vht_id < '999'";
            endif;
         endif;
         $orderByClause = "crvh.crvh_sid";

         $sqlquery = getStmtCrCrvhList($whereClause, $orderByClause);
// echo $sqlquery . "\n\n"; die();
         $result = $db->query($sqlquery);   
         if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
         while ($row = $result->fetch_assoc()):

               $lineArr = array($row[$fieldArray[0]], $row[$fieldArray[1]], $row[$fieldArray[2]], $row[$fieldArray[3]], $row[$fieldArray[4]], $row[$fieldArray[5]], 
                                $row[$fieldArray[6]], $row[$fieldArray[7]], $row[$fieldArray[8]], $row[$fieldArray[9]], $row[$fieldArray[10]]);
               $triggerArray[] = $lineArr;
               $csvArray[] = implode(";", $lineArr);
         endwhile;
         $result->free();

         $triggerArrayLen = count($triggerArray);
         $triggerOut = "";
         // 66CCFF, FF0000, FF0044, FF6633, FFCC33, FFFF33
         if ($triggerArrayLen > 0) :

            // 
            $sendPerMail = true;  // At least one event to be mailed
            
            // HTML output
            $triggerOut .= "<html>\n";
            $triggerOut .= "<body>\n";
            /*
            $triggerOut .= "<br>";
            $triggerOut .= "<table>\n";
            $triggerOut .= "<tr>\n";
            $triggerOut .= "<td style=\"background-color: #66CCFF;\">&nbsp;&nbsp;KEIN EINTRAG&nbsp;&nbsp;</td>";
            $triggerOut .= "<td style=\"background-color: #FFFF33;\">&nbsp;&nbsp;&nbsp;< 6 Monate&nbsp;&nbsp;&nbsp;</td>";
            $triggerOut .= "<td style=\"background-color: #FFCC33;\">&nbsp;&nbsp;&nbsp;< 3 Monate&nbsp;&nbsp;&nbsp;</td>";
            $triggerOut .= "<td style=\"background-color: #FF6633;\">&nbsp;&nbsp;&nbsp;< 2 Monate&nbsp;&nbsp;&nbsp;</td>";
            $triggerOut .= "<td style=\"background-color: #FF0033;\">&nbsp;&nbsp;&nbsp;< 1 Monat&nbsp;&nbsp;&nbsp;&nbsp;</td>";
            $triggerOut .= "</tr>\n";
            $triggerOut .= "</table>\n";
            */
            $triggerOut .= "<br><br>";
            $triggerOut .= "<table>\n";
            // Headline
            $triggerOut .= "<tr>\n";
            
            $tmpArray = $triggerArray[0];
            $tmpArrayLen = count($tmpArray);
            // $rowColor = $tmpArray[0];
            $rowColor = "66CCFF";  // Here a static value
            for ($j = 0; $j < $tmpArrayLen; $j++) :
                $triggerOut .= "<td style=\"background-color: #" . $rowColor . ";\">&nbsp;" . $tmpArray[$j] . "&nbsp;&nbsp;&nbsp;</td>\n";
            endfor;
            $triggerOut .= "</tr>\n";
            // Body
            for ($i = 1; $i < $triggerArrayLen; $i++) :
                $tmpArray = $triggerArray[$i];
                $tmpArrayLen = count($tmpArray);
                // $rowColor = $tmpArray[0];
                $rowColor = "66CCFF";  // Here a static value
                $triggerOut .= "<tr>\n";
                for ($j = 0; $j < $tmpArrayLen; $j++) :
                    if ($typeArr[$fieldArray[$j]] == "float") : 
                       $formattedOutput = formatOutput($tmpArray[$j], $typeArr[$fieldArray[$j]], "2");
                       $textAlign = "right";
                    elseif ($typeArr[$fieldArray[$j]] == "datetime") :
                       $formattedOutput = formatOutput($tmpArray[$j], $typeArr[$fieldArray[$j]], "4");
                       $textAlign = "center";
                    else :
                       $formattedOutput = formatOutput($tmpArray[$j]);
                       $textAlign = "left";
                    endif;
                    $triggerOut .= "<td style=\"text-align: " . $textAlign . "; background-color: #" . $rowColor . ";\">&nbsp;" . $formattedOutput . ($rowColor == "FF0000" && $j == "6" ? "&nbsp;[überschritten]" : "") . "&nbsp;&nbsp;&nbsp;</td>\n";
                endfor;
                $triggerOut .= "</tr>\n";
            endfor;
            $triggerOut .= "</table>\n";

            $triggerOut .= "</body>\n";
            $triggerOut .= "</html>\n";
            
            // CSV attachment
            $csvArrayLen = count($csvArray);
            for ($i = 0; $i < $csvArrayLen; $i++) :
                writeToFile($path . $f_secretFileName, $csvArray[$i]);
            endfor;
         endif;

         // Send via email
         if ($sendPerMail) :  

            $mailSenderAddress = "support@assecutor.de";
           
            // Receiver of the mail
            $mailReceiverAddressArray = array("support@assecutor.de");
            if ($hqIdMail != "" && is_numeric($hqIdMail)) :
               $mailReceiverAddress = getParameterValue("0", "MAIL_CRON_201_TO_ADDRESS", $hqIdMail);
               // Check for split regarding vehicle type
               if ($courierOrTruckOnly == "K" || $courierOrTruckOnly == "L") :
                  if ($courierOrTruckOnly == "K") :
                     $mailReceiverAddress = getParameterValue("0", "MAIL_CRON_201_A_TO_ADDRESS", $hqIdMail);
                  else :
                     $mailReceiverAddress = getParameterValue("0", "MAIL_CRON_201_B_TO_ADDRESS", $hqIdMail);
                  endif;
               endif;
            endif;
            if ($mailReceiverAddress == "") :
               $mailReceiverAddress = getParameterValue("0", "MAIL_CRON_201_TO_ADDRESS", "0");
            endif;
            if ($mailReceiverAddress != "") :
               $mailReceiverAddressArray = spliti(",",$mailReceiverAddress);
            endif;
// echo "mailReceiverAddress = " . $mailReceiverAddress . "\n\n";
// $mailReceiverAddressArray = array("mv@assecutor.de", "ingo.kublenz@stadtbote.de");

            $mailSubject = "Fahrzeuggebühren [" . $hqName . "]";
            if ($courierOrTruckOnly == "K" || $courierOrTruckOnly == "L") :               
               if ($courierOrTruckOnly == "L") :
                  $mailSubject .= " [" . "LKW" . "]";
               else :
                  $mailSubject .= " [" . "PKW" . "]";
               endif;
            endif;

            $mailCcAddress = "";
            $mailBccAddress = "";

            $mailtext = "FAHRZEUGGEBÜHREN";
            if ($courierOrTruckOnly == "K" || $courierOrTruckOnly == "L") :               
               if ($courierOrTruckOnly == "L") :
                  $mailtext .= " [" . "LKW" . "]";
               else :
                  $mailtext .= " [" . "PKW" . "]";
               endif;
            endif;
            $mailtext .= "<br><br>";
            if (!$autoMode) :
               $mailtext .= "Ausgeführt von: " . $usrFirstname . " " . $usrName;
            endif;
            $mailtext .= $triggerOut;

            $mailObj = new htmlMimeMail();

            // Set From address
            $mailObj->setFrom($mailSenderAddress);

            // Set Cc address
            if ($mailCcAddress != "") :
               $mailObj->setCc($mailCcAddress);
            endif;

            // Set Bcc address
            if ($mailBccAddress != "") :
               $mailObj->setBcc($mailBccAddress);
            endif;

            // Subject of the mail
            $mailObj->setSubject($mailSubject);

            // Mail text
            $mailObj->setHtml($mailtext, null, "./");

            // Attachment
            if ($f_secretFileName != "") :
               if (file_exists($path . $f_secretFileName)) :
                  $attachment = $mailObj->getFile($path . $f_secretFileName);
                  // $mailObj->addAttachment($attachment, $f_secretFileName, $f_mimetype . $f_charset);
                  $mailObj->addAttachment($attachment, $f_secretFileName, 'text/csv');
               endif;
            endif;

            $mailResult = $mailObj->send($mailReceiverAddressArray, 'smtp');

            if ($mailResult) :
               // $mailsendStatus = getLngt("Die Nachricht wurde versandt!");
               // Write logdata into log database
               // writeToLogDB("22",$currentHqId,$job_id,$currentSessionUsrId,"","","",$f_email . "|STATION");
            else :
               // $mailsendStatus = getLngt("Die Nachricht konnte nicht gesendet werden!");
               // Write logdata into log database
               // writeToLogDB("23",$currentHqId,$job_id,$currentSessionUsrId,"","","",$f_email . "|STATION");
            endif;

            $mailObj = NULL;
            if (file_exists($path . $f_secretFileName)) :
               unlink($path . $f_secretFileName);
            endif;

            $out = "Der Sendevorgang wurde abgeschlossen! Bitte prüfen Sie Ihren Email-Eingangsordner!";
         endif;
      endif;
   else :   
      $out = "Leider haben Sie zur Ausführung keine Berechtigung!";   
   endif;
endif;
?>

<?php if (!$autoMode) : ?>

<html>

<head>

</head>
	
	
<body>

<center>
	
<br><br><br>

<b><?php echo $triggerOut ?></b>

</center>

</body>	
	
</html>

<?php endif; ?>