403 lines
17 KiB
PHP
403 lines
17 KiB
PHP
<?php
|
|
/*=======================================================================
|
|
*
|
|
* auto_trigger_jobs_threshold.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 getStmtJbThresholdList ($whereClause = "", $orderByClause = "", $fieldClause = "", $fromClause = "", $forceIndexClause = "") {
|
|
// global $crPriceThresholdValue, $crPriceBlockValue, $crPriceMarginValue;
|
|
|
|
$retStmt = "";
|
|
if ($fieldClause != "") : $fieldClause = ", " . $fieldClause; endif;
|
|
if ($fromClause != "") : $fromClause = ", " . $fromClause; endif;
|
|
if ($forceIndexClause != "") : $fromClause .= " " . $forceIndexClause; endif;
|
|
if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
|
|
if ($orderByClause != "") : $orderByClause = " ORDER BY " . $orderByClause; endif;
|
|
|
|
/*
|
|
- in der Spalte Auszahlung wird die Partnerprovision zu der Frachtvergütung (nicht mehr Fuhrlohn) addiert und als Eurowert ausgewiesen
|
|
(exemplarisch: 50,00 € Frachtvergütung plus 3% Partnerprovi = 51,50 € Auszahlung).
|
|
- die Spalte EaU weist den Preis minus Auszahlung aus (Exemplarisch: 60,00 € Preis minus 51,50 € Auszahlung = 8,50 € EaU)
|
|
- die Spalte BSG weist dann abschliessend das Ergebnis des Bruttospeditionsgewinns aus, der sich ermittelt aus dem EaU,
|
|
als Anteil des Preis in Prozent (Exemplarisch 8,50 € EaU = 14,17% BSG von 60,00 € Preis).
|
|
*/
|
|
$retStmt = "SELECT jb.jb_id, cs.cs_eid, cmp.cmp_comp, jb.jb_ordertime, jb.jb_finishtime, jb.cr_id, cr.cr_eid, jb.cr_sid,"
|
|
. " jb.jb_totalprice, jb.jb_cr_price, ROUND(100 - ((jb.jb_cr_price / jb.jb_totalprice) * 100), 2) AS jb_percent,"
|
|
. " crvh.crvh_partner_commission, ROUND(jb.jb_cr_price * (1 + (crvh.crvh_partner_commission / 100)), 2) AS payout,"
|
|
. " ROUND(jb.jb_totalprice - (jb.jb_cr_price * (1 + (crvh.crvh_partner_commission / 100))), 2) AS eau,"
|
|
. " ROUND(((jb.jb_totalprice - (jb.jb_cr_price * (1 + (crvh.crvh_partner_commission / 100)))) / jb.jb_totalprice) * 100, 2) AS bsg"
|
|
. $fieldClause
|
|
. " FROM job AS jb LEFT JOIN courier AS cr ON jb.cr_id = cr.cr_id"
|
|
. " LEFT JOIN couriervehicle AS crvh ON jb.cr_id = crvh.cr_id AND jb.cr_sid = crvh.crvh_sid,"
|
|
. " costcenter AS csc, customer AS cs, company AS cmp" . $fromClause
|
|
. " WHERE jb.csc_id_payer = csc.csc_id AND cs.cs_id = csc.cs_id AND cmp.cmp_id = cs.cmp_id AND cmp.cmp_archived = '0'" . $whereClause
|
|
. $orderByClause;
|
|
return $retStmt;
|
|
}
|
|
|
|
|
|
// Check für 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");
|
|
$yesterdayDate = getDateTime("date_yesterday_currentmonth_currentyear");
|
|
$dateLastWeek = date("Y-m-d",mktime(0,0,0,date("m"),date("d")-7,date("Y")));
|
|
|
|
$sendPerMail = false;
|
|
$path = "../temp/download/";
|
|
$f_secretFileName = "JB_THRESHOLD_LIST_" . getDateTime("6") . ".csv";
|
|
|
|
// List of all vehicles with special fields
|
|
if (true) :
|
|
|
|
$typeArr = defineOutputFormats();
|
|
// Special extensions
|
|
$typeArr["jb_totalprice"] = "float";
|
|
$typeArr["jb_percent"] = "float";
|
|
$typeArr["crvh_partner_commission"] = "float";
|
|
$typeArr["payout"] = "float";
|
|
$typeArr["eau"] = "float";
|
|
$typeArr["bsg"] = "float";
|
|
|
|
// Ger parameter for threshold value
|
|
$crPriceMarginValue = 0;
|
|
if ($hqIdMail != "" && is_numeric($hqIdMail)) :
|
|
$crPriceThresholdValue = getParameterValue("0", "JB_CR_PRICE_THRESHOLD", $hqIdMail);
|
|
$crPriceBlockValue = getParameterValue("0", "JB_CR_PRICE_BLOCK", $hqIdMail);
|
|
$crPriceMarginValue = getParameterValue("0", "JB_CR_PRICE_THRESHOLD_MARGIN", $hqIdMail);
|
|
endif;
|
|
if ($crPriceThresholdValue == "") :
|
|
$crPriceThresholdValue = getParameterValue("0", "JB_CR_PRICE_THRESHOLD", "0");
|
|
$crPriceBlockValue = getParameterValue("0", "JB_CR_PRICE_BLOCK", "0");
|
|
$crPriceMarginValue = getParameterValue("0", "JB_CR_PRICE_THRESHOLD_MARGIN", "0");
|
|
endif;
|
|
if ($crPriceThresholdValue == "" || $crPriceBlockValue == "") :
|
|
sendInternalMail("Mindestens einer der Parameter JB_CR_PRICE_THRESHOLD bzw. JB_CR_PRICE_BLOCK existiert nicht!");
|
|
die();
|
|
endif;
|
|
|
|
// ATTENTION: Margin value ($crPriceMarginValue) regarding the threshold value (Here static "1" <=> JB_CR_PRICE_THRESHOLD + $crPriceMarginValue = 85 + 1 = 86)
|
|
|
|
$triggerArray = array();
|
|
$csvArray = array();
|
|
|
|
// Insert header
|
|
$headerArray = array("Auftrag", "Kunde", "Firma", "Auftragszeit", "Erledigungszeit", "Kurier", "Fahrzeug", "Preis", "Frachtvergütung", "Prozentsatz", "Partnerprovision", "Auszahlung", "EAU", "BSG");
|
|
$fieldArray = array("jb_id", "cs_eid", "cmp_comp", "jb_ordertime", "jb_finishtime", "cr_eid", "cr_sid", "jb_totalprice", "jb_cr_price", "jb_percent", "crvh_partner_commission", "payout", "eau", "bsg");
|
|
$triggerArray[] = $headerArray;
|
|
$csvArray[] = implode(";", $headerArray);
|
|
|
|
// Special FIELD clause
|
|
$fieldClause = "";
|
|
|
|
// Special FROM clause
|
|
$fromClause = "";
|
|
|
|
// Force Index Clause
|
|
$forceIndexClause = "";
|
|
// $forceIndexClause = "FORCE INDEX (jb_status_3)";
|
|
|
|
// Special WHERE clause
|
|
// jb.jb_cr_price > 0 AND
|
|
$whereClause = "jb.jb_ordertime >= '" . $dateLastWeek . " 00:00:00" . "' AND jb.jb_ordertime <= '" . $yesterdayDate . " 23:59:59" . "' AND "
|
|
. "jb.jb_status = '2' AND "
|
|
. "jb.jb_incomplete = '0' AND "
|
|
. "(isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1') AND "
|
|
. "jb.jb_totalprice > 0 AND "
|
|
. "(jb.jb_cr_price > ((jb.jb_totalprice * " . ($crPriceThresholdValue + $crPriceMarginValue) . ") / 100) OR ROUND(((jb.jb_cr_price / jb.jb_totalprice) * 100), 2) <= 1)";
|
|
|
|
// Check for headquaters
|
|
$whereClauseHq = " AND jb.hq_id = '" . $hqIdMail . "'";
|
|
if ($hqIdMail == "HQ_ALL") : $whereClauseHq = ""; endif;
|
|
$whereClause .= $whereClauseHq;
|
|
|
|
// Check for split regarding vehicle type
|
|
if ($courierOrTruckOnly == "K" || $courierOrTruckOnly == "L") :
|
|
if ($courierOrTruckOnly == "K") :
|
|
$whereClause .= " AND jb.vht_id < '7'";
|
|
else :
|
|
$whereClause .= " AND jb.vht_id >= '7' AND jb.vht_id < '999'";
|
|
endif;
|
|
endif;
|
|
|
|
// Special ORDER BY clause
|
|
$orderByClause = "jb.jb_id";
|
|
|
|
$sqlquery = getStmtJbThresholdList($whereClause, $orderByClause, $fieldClause, $fromClause, $forceIndexClause);
|
|
// 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]], $row[$fieldArray[11]],
|
|
$row[$fieldArray[12]], $row[$fieldArray[13]]);
|
|
$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;\"> KEIN EINTRAG </td>";
|
|
$triggerOut .= "<td style=\"background-color: #FFFF33;\"> < 6 Monate </td>";
|
|
$triggerOut .= "<td style=\"background-color: #FFCC33;\"> < 3 Monate </td>";
|
|
$triggerOut .= "<td style=\"background-color: #FF6633;\"> < 2 Monate </td>";
|
|
$triggerOut .= "<td style=\"background-color: #FF0033;\"> < 1 Monat </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 . ";\"> " . $tmpArray[$j] . " </td>\n";
|
|
endfor;
|
|
$triggerOut .= "</tr>\n";
|
|
// Body
|
|
for ($i = 1; $i < $triggerArrayLen; $i++) :
|
|
$tmpArray = $triggerArray[$i];
|
|
$tmpArrayLen = count($tmpArray);
|
|
$percentValue = $triggerArray[$i][9];
|
|
|
|
// Color
|
|
// $rowColor = $tmpArray[0];
|
|
$rowColor = "66CCFF"; // Here a static value
|
|
if ($percentValue <= (100 - ($crPriceThresholdValue + $crPriceMarginValue))) :
|
|
$rowColor = "FFCC33";
|
|
endif;
|
|
if ($percentValue <= (100 - $crPriceBlockValue)) :
|
|
$rowColor = "FF6633";
|
|
endif;
|
|
|
|
$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 . ";\"> " . $formattedOutput . ($rowColor == "FF0000" && $j == "6" ? " [überschritten]" : "") . " </td>\n";
|
|
endfor;
|
|
$triggerOut .= "</tr>\n";
|
|
endfor;
|
|
$triggerOut .= "</table>\n";
|
|
|
|
if ($crPriceMarginValue > 0) :
|
|
$triggerOut .= "</br></br></br></br>\n";
|
|
$triggerOut .= getLngt("Die Schwellwerttoleranz beträgt derzeit") . " " . $crPriceMarginValue . "%.";
|
|
endif;
|
|
|
|
$triggerOut .= "<br><br>";
|
|
$triggerOut .= getLngt("Die Berechnungen stellen Prognosen vor dem Export der Daten dar. Sie haben vor dem Export noch die Möglichkeit, korrigierend in das Ergebnis einzugreifen.");
|
|
|
|
$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_202_TO_ADDRESS", $hqIdMail);
|
|
// Check for split regarding vehicle type
|
|
if ($courierOrTruckOnly == "K" || $courierOrTruckOnly == "L") :
|
|
if ($courierOrTruckOnly == "K") :
|
|
$mailReceiverAddress = getParameterValue("0", "MAIL_CRON_202_A_TO_ADDRESS", $hqIdMail);
|
|
else :
|
|
$mailReceiverAddress = getParameterValue("0", "MAIL_CRON_202_B_TO_ADDRESS", $hqIdMail);
|
|
endif;
|
|
endif;
|
|
endif;
|
|
if ($mailReceiverAddress == "") :
|
|
$mailReceiverAddress = getParameterValue("0", "MAIL_CRON_202_TO_ADDRESS", "0");
|
|
endif;
|
|
if ($mailReceiverAddress != "") :
|
|
$mailReceiverAddressArray = spliti(",",$mailReceiverAddress);
|
|
endif;
|
|
// echo "mailReceiverAddress = " . $mailReceiverAddress . "\n\n";
|
|
// $mailReceiverAddressArray = array("mv@assecutor.de", "ingo.kublenz@stadtbote.de");
|
|
// $mailReceiverAddressArray = array("admin@assecutor.de", "ingo.kublenz@stadtbote.de");
|
|
// $mailReceiverAddressArray = array("mv@assecutor.de");
|
|
|
|
$mailSubject = "Schwellwertaufträge [" . $hqName . "]";
|
|
if ($courierOrTruckOnly == "K" || $courierOrTruckOnly == "L") :
|
|
if ($courierOrTruckOnly == "L") :
|
|
$mailSubject .= " [" . "LKW" . "]";
|
|
else :
|
|
$mailSubject .= " [" . "PKW" . "]";
|
|
endif;
|
|
endif;
|
|
|
|
$mailCcAddress = "";
|
|
$mailBccAddress = "";
|
|
|
|
$mailtext = "SCHWELLWERTAUFTRÄGE";
|
|
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; ?>
|