1532 lines
90 KiB
PHP
1532 lines
90 KiB
PHP
<?php
|
|
/*=======================================================================
|
|
*
|
|
* exportrequests_special.inc.php
|
|
*
|
|
* Autor: Marc Vollmann
|
|
*
|
|
=======================================================================*/
|
|
|
|
|
|
include_once ("../include/inc_job.inc.php");
|
|
|
|
|
|
function special_export_01 ($dbConnection = "") {
|
|
global $db, $PHP_SELF;
|
|
global $hq_id, $usr_id, $customerId, $cscIdRoot, $cscIdActual, $cscNameActual, $cscPathActual, $fromDateRange, $toDateRange;
|
|
global $statusMessage, $f_delimiter, $f_fileName, $globalExportPath;
|
|
|
|
$retArray = array();
|
|
if (!is_object($dbConnection) || $dbConnection == "") : $dbConnection = $db; endif;
|
|
|
|
// Get status of the customer according to being meta customer
|
|
// If not then export only data of the current customer else export all data (group customer AND meta customer) and ignore costcenter hierarchy
|
|
$isMetaCs = "0";
|
|
if (existsEntry("customer",array("cs_id_parent",$customerId))) :
|
|
$isMetaCs = "1";
|
|
endif;
|
|
|
|
$where_clause = "";
|
|
if ($isMetaCs == "0") :
|
|
$where_clause = " ((jb.csc_id_payer = '" . $cscIdActual . "' AND jb.csc_id_payer = csc.csc_id) OR "
|
|
. " (jb.csc_id_payer = csc.csc_id AND csc.csc_path LIKE '%//" . $cscNameActual . "//%')) AND ";
|
|
endif;
|
|
|
|
$sqlquery = "SELECT jb.jb_id, jb.jb_ordertime, jb.cr_sid, jb.hq_id,"
|
|
. " tr.tr_id, tr.tr_sort, tr.tr_comp, tr.tr_person, tr.tr_commission_no, tr.tr_ware_from_to, tr.csc_id,"
|
|
. " ad.ad_street, tr.tr_hsno, ad.ad_zipcode, ad.ad_city, ad.ad_country,"
|
|
. " cs.cs_eid, cmp.cmp_id, cmp.cmp_comp, cmp.cmp_comp2, cmp.cmp_comp3, cmp.cmp_comp4,"
|
|
. " ad_hq.ad_zipcode AS hq_ad_zipcode, csc_tr.csc_name AS tr_csc_name, cmp_tr.cmp_comp4 AS tr_cmp_comp4,"
|
|
. " cs_tr.cs_id AS tr_cs_id, cs_tr.cs_eid AS tr_cs_eid"
|
|
. " FROM costcenter AS csc, customer AS cs, company AS cmp, address AS ad, job AS jb,"
|
|
. " tour AS tr LEFT JOIN costcenter AS csc_tr ON csc_tr.csc_id = tr.csc_id LEFT JOIN customer AS cs_tr ON cs_tr.cs_id = csc_tr.cs_id LEFT JOIN company AS cmp_tr ON cmp_tr.cmp_id = cs_tr.cmp_id,"
|
|
. " headquarters AS hq LEFT JOIN company AS cmp_hq ON cmp_hq.cmp_id = hq.cmp_id LEFT JOIN address AS ad_hq ON ad_hq.ad_id = cmp_hq.ad_id"
|
|
. " WHERE jb.jb_status = '2' AND"
|
|
. " (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND"
|
|
. " jb.jb_totalprice > '0' AND"
|
|
. " jb.csc_id_payer = csc.csc_id AND"
|
|
. " csc.cs_id = cs.cs_id AND " . ($isMetaCs == "1" ? " cs.cs_id_parent " : " cs.cs_id ") . " = '" . $customerId . "' AND"
|
|
. $where_clause
|
|
. " cs.cmp_id = cmp.cmp_id AND"
|
|
. ($fromDateRange != "" ? " jb.jb_finishtime >= '$fromDateRange' AND " : "")
|
|
. ($toDateRange != "" ? " jb.jb_finishtime <= '$toDateRange' AND " : "")
|
|
. " jb.jb_id = tr.jb_id AND "
|
|
. " tr.ad_id = ad.ad_id AND "
|
|
. " jb.hq_id = hq.hq_id"
|
|
. " ORDER BY jb.jb_id, tr.tr_sort";
|
|
// echo $sqlquery . "<br><br>";
|
|
$result = $dbConnection->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: <br>$sqlquery<br>" . $result->getMessage());
|
|
|
|
$count = 0;
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count] = $row;
|
|
// Add jobcalculator data
|
|
$retArray[$count]["jbc_data"] = getCalculatorData($row["jb_id"], $row["tr_sort"]);
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
$retArrayLen = count($retArray);
|
|
// print_r($retArray);
|
|
|
|
// Write file
|
|
if ($f_fileName != "") :
|
|
|
|
// Check existence of this name
|
|
if (!existsEntry("exportfiles",array("hq_id",$hq_id,"cs_id",$customerId,"expf_name",$f_fileName))) :
|
|
|
|
// Create secret filename
|
|
$f_secretFileName = "cs_se_01_" . md5($f_fileName);
|
|
|
|
// Set defined delimiter, may not be empty (!!!!)
|
|
if ($f_delimiter == "") : $f_delimiter = ";"; endif;
|
|
|
|
// Iterate complete array to substitute all existing fields having the delimiter (!!!!)
|
|
for ($i = 0; $i < $retArrayLen; $i++) :
|
|
$tmpArrKeys = array_keys($retArray[$i]);
|
|
$tmpArrKeysLen = count($tmpArrKeys);
|
|
for ($j = 0; $j < $tmpArrKeysLen; $j++) :
|
|
$retArray[$i][$tmpArrKeys[$j]] = str_replace ($f_delimiter, " ", $retArray[$i][$tmpArrKeys[$j]]);
|
|
endfor;
|
|
endfor;
|
|
|
|
// Get IDs of costcenters "EXTERN" of ALL headquarters (!!!!)
|
|
// $cscIdsExtern = getColVectorFromDB2ArrayByClause("parameter", "par_value", "par_key = 'CSC_ID_PAYER_EXTERN'", "hq_id", "", "");
|
|
|
|
// Mapping of zipcodes for special export function output fields
|
|
$parSpecialExportZipcodeMappingArray = array();
|
|
$parSpecialExportZipcodeMapping = getParameterValue("0", "EXPORT_SPECIAL_FUNCTION_ZIPCODE_MAPPING", "0");
|
|
if ($parSpecialExportZipcodeMapping != "") :
|
|
// E.g. "HTE45067=04159|HTE45227=04159|HTE43478=04159"
|
|
$parSpecialExportZipcodeMappingArray = getKeyValueArrayFromString($parSpecialExportZipcodeMapping);
|
|
endif;
|
|
|
|
$exportFileLineArray = array();
|
|
for ($i = 0; $i < $retArrayLen; $i++) :
|
|
|
|
$displayRow = true;
|
|
|
|
// Check "ware-from-to" to be set
|
|
$tmpTrWareFromTo = trim($retArray[$i]["tr_ware_from_to"]);
|
|
if (!($tmpTrWareFromTo != "" && is_numeric($tmpTrWareFromTo) && $tmpTrWareFromTo >= "1")) :
|
|
$displayRow = false;
|
|
endif;
|
|
|
|
// Check payer equals station customer
|
|
if ($displayRow) :
|
|
$trCmpComp4 = "";
|
|
if (strtoupper(trim($retArray[$i]["tr_csc_name"])) != "EXTERN" && substr(strtoupper(trim($retArray[$i]["tr_cmp_comp4"])),0,3) == "DEM") :
|
|
$trCmpComp4 = strtoupper(trim($retArray[$i]["tr_cmp_comp4"]));
|
|
if (strtoupper(trim($retArray[$i]["cmp_comp4"])) == $trCmpComp4) :
|
|
$displayRow = false;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
|
|
if ($displayRow) :
|
|
|
|
// Calculator data of the current station
|
|
$tmpCalc01Array = array();
|
|
$tmpCalc02Array = array();
|
|
$tmpExtraQM = 2;
|
|
$tmpExtraHK = 1;
|
|
$tmpExtraHours = 0.5;
|
|
$jbcDataArray = $retArray[$i]["jbc_data"];
|
|
$jbcDataArrayLen = count($jbcDataArray);
|
|
|
|
for ($j = 0; $j < $jbcDataArrayLen; $j++) :
|
|
$jbcVal = trim($jbcDataArray[$j][0]);
|
|
$jbcVal2 = trim($jbcDataArray[$j][5]);
|
|
|
|
// ARR 01
|
|
if ($jbcVal2 == "Zone 1 bis 2 qm") :
|
|
$tmpCalc01Array[] = "B2C1";
|
|
endif;
|
|
if ($jbcVal2 == "Zone 2 bis 2 qm") :
|
|
$tmpCalc01Array[] = "B2C2";
|
|
endif;
|
|
if ($jbcVal2 == "Zone 1 Pal. Abh.") :
|
|
$tmpCalc01Array[] = "B2CP1";
|
|
endif;
|
|
if ($jbcVal2 == "Zone 2 Pal. Abh.") :
|
|
$tmpCalc01Array[] = "B2CP2";
|
|
endif;
|
|
// ARR 02
|
|
if ($jbcVal2 == "Frühlief." || strtolower($jbcVal2) == "frühlieferung") :
|
|
$tmpCalc02Array[] = "E vor9";
|
|
endif;
|
|
|
|
if ($jbcVal2 == "Feierabendlief." || !(strpos(strtolower($jbcVal2), "feierabendlief") === false) || !(strpos(strtolower($jbcVal2), "feierabendanlief") === false)) :
|
|
$tmpCalc02Array[] = "E AbndZust";
|
|
endif;
|
|
if ($jbcVal2 == "Express 14 Uhr" || (!(strpos(strtolower($jbcVal2), "express") === false) && !(strpos($jbcVal2, "14") === false))) :
|
|
$tmpCalc02Array[] = "E NextDay";
|
|
endif;
|
|
if ($jbcVal2 == "Express 17 Uhr" || (!(strpos(strtolower($jbcVal2), "express") === false) && !(strpos($jbcVal2, "17") === false) && (strpos($jbcVal2, "14") === false))) :
|
|
$tmpCalc02Array[] = "E NachZust";
|
|
endif;
|
|
if ($jbcVal2 == "Zeitfenster 2 Std.") :
|
|
$tmpCalc02Array[] = "FIX";
|
|
endif;
|
|
// Quadratmeter (QM)
|
|
if ($jbcVal2 == "je weiterer qm") :
|
|
$tmpExtraQM += $jbcDataArray[$j][2];
|
|
endif;
|
|
// Hilfskraft
|
|
// if ($jbcVal2 == "Hilskraft kl.30 Min." || $jbcVal2 == "Hilfskraft kl.30 Min.") :
|
|
if (!(strpos($jbcVal2, "Hilfskraft") === false) || !(strpos($jbcVal2, "Hilskraft") === false)) :
|
|
$tmpExtraHK += $jbcDataArray[$j][2];
|
|
endif;
|
|
// Stunden
|
|
if ($jbcVal2 == "Stopp gr. 30 Min.") :
|
|
$tmpExtraHours += ($jbcDataArray[$j][2] * 0.5);
|
|
endif;
|
|
endfor;
|
|
$tmpCalc01 = implode(",", $tmpCalc01Array);
|
|
$tmpCalc02 = implode(",", $tmpCalc02Array);
|
|
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cmp_comp4"]); // [A] AUFTRAGGEBER_ADR
|
|
$tmpServiceProviderAdr = "Hanset_" . trim($retArray[$i]["hq_ad_zipcode"]);
|
|
if ($parSpecialExportZipcodeMappingArray[trim($retArray[$i]["tr_cs_eid"])] != "") :
|
|
$tmpServiceProviderAdr = "Hanset_" . $parSpecialExportZipcodeMappingArray[trim($retArray[$i]["tr_cs_eid"])];
|
|
endif;
|
|
$exportFileLineArray[$i][] = $tmpServiceProviderAdr; // [B] DIENSTLEISTER_ADR
|
|
|
|
if ($tmpTrWareFromTo == "1") :
|
|
$exportFileLineArray[$i][] = $trCmpComp4; // [C] ABGANGSADRESSE_ADR
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_comp"]); // [D] ABGANGSNAME
|
|
$tmpVal = trim(trim($retArray[$i]["ad_street"]) . " " . trim($retArray[$i]["tr_hsno"]));
|
|
$exportFileLineArray[$i][] = $tmpVal; // [E] ABGANGSSTRASSE
|
|
$tmpVal = trim($retArray[$i]["ad_country"]);
|
|
if ($tmpVal == "" || $tmpVal == "D") : $tmpVal = "DE"; endif;
|
|
$exportFileLineArray[$i][] = $tmpVal; // [F] ABGANGSLAND
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["ad_zipcode"]); // [G] ABGANGSPLZ
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["ad_city"]); // [H] ABGANGSORT
|
|
$exportFileLineArray[$i][] = $tmpCalc01; // [I] ABGANGSZONE
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cmp_comp4"]); // [J] EMPFANGSADRESSE_ADR
|
|
$exportFileLineArray[$i][] = ""; // [K] EMPFANGSNAME
|
|
$exportFileLineArray[$i][] = ""; // [L] EMPFANGSSTRASSE
|
|
$exportFileLineArray[$i][] = ""; // [M] EMPFANGSLAND
|
|
$exportFileLineArray[$i][] = ""; // [N] EMPFANGSPLZ
|
|
$exportFileLineArray[$i][] = ""; // [O] EMPFANGSORT
|
|
$exportFileLineArray[$i][] = ""; // [P] EMPFANGSZONE
|
|
else :
|
|
$tmpC = trim($retArray[$i]["cmp_comp4"]);
|
|
$tmpJ = "";
|
|
$tmpK = trim($retArray[$i]["tr_comp"]);
|
|
$tmpL = trim(trim($retArray[$i]["ad_street"]) . " " . trim($retArray[$i]["tr_hsno"]));
|
|
$tmpM = trim($retArray[$i]["ad_country"]);
|
|
if ($tmpM == "" || $tmpM == "D") : $tmpM = "DE"; endif;
|
|
$tmpN = trim($retArray[$i]["ad_zipcode"]);
|
|
$tmpO = trim($retArray[$i]["ad_city"]);
|
|
if ($trCmpComp4 != "") :
|
|
// $tmpC = $trCmpComp4;
|
|
// $tmpJ = trim($retArray[$i]["cmp_comp4"]);
|
|
$tmpC = trim($retArray[$i]["cmp_comp4"]);
|
|
$tmpJ = $trCmpComp4;
|
|
$tmpK = "";
|
|
$tmpL = "";
|
|
$tmpN = "";
|
|
$tmpO = "";
|
|
endif;
|
|
$exportFileLineArray[$i][] = $tmpC; // [C] ABGANGSADRESSE_ADR
|
|
$exportFileLineArray[$i][] = ""; // [D] ABGANGSNAME
|
|
$exportFileLineArray[$i][] = ""; // [E] ABGANGSSTRASSE
|
|
$exportFileLineArray[$i][] = ""; // [F] ABGANGSLAND
|
|
$exportFileLineArray[$i][] = ""; // [G] ABGANGSPLZ
|
|
$exportFileLineArray[$i][] = ""; // [H] ABGANGSORT
|
|
$exportFileLineArray[$i][] = ""; // [I] ABGANGSZONE
|
|
$exportFileLineArray[$i][] = $tmpJ; // [J] EMPFANGSADRESSE_ADR
|
|
$exportFileLineArray[$i][] = $tmpK; // [K] EMPFANGSNAME
|
|
$exportFileLineArray[$i][] = $tmpL; // [L] EMPFANGSSTRASSE
|
|
$exportFileLineArray[$i][] = $tmpM; // [M] EMPFANGSLAND
|
|
$exportFileLineArray[$i][] = $tmpN; // [N] EMPFANGSPLZ
|
|
$exportFileLineArray[$i][] = $tmpO; // [O] EMPFANGSORT
|
|
$exportFileLineArray[$i][] = $tmpCalc01; // [P] EMPFANGSZONE
|
|
endif;
|
|
|
|
$exportFileLineArray[$i][] = ""; // [Q] BELADESTELLE_ADR
|
|
$exportFileLineArray[$i][] = ""; // [R] ENTLADESTELLE_ADR
|
|
$exportFileLineArray[$i][] = "DAP"; // [S] FRANKATUR
|
|
$exportFileLineArray[$i][] = "B2C"; // [T] VERSANDART
|
|
$exportFileLineArray[$i][] = $tmpCalc02; // [U] MERKMAL
|
|
$exportFileLineArray[$i][] = ""; // [V] LADEMETER
|
|
$exportFileLineArray[$i][] = $tmpExtraQM; // [W] QUADRATMETER
|
|
$exportFileLineArray[$i][] = "25"; // [X] LAUFKENNZEICHEN
|
|
$exportFileLineArray[$i][] = $tmpExtraHK . "MH"; // [Y] SERVICE
|
|
$exportFileLineArray[$i][] = ""; // [Z] SERVICE_NR1
|
|
$exportFileLineArray[$i][] = ""; // [AA] SERVICE_NR2
|
|
$tmpExtraHours = str_replace (".", ",", $tmpExtraHours);
|
|
$exportFileLineArray[$i][] = $tmpExtraHours; // [AB] ZEITSTUNDEN
|
|
$exportFileLineArray[$i][] = ""; // [AC] WARENWERT
|
|
|
|
$tmpTime = trim($retArray[$i]["jb_ordertime"]);
|
|
$jbOrderdate = substr($tmpTime,8,2) . "." . substr($tmpTime,5,2) . "." . substr($tmpTime,0,4);
|
|
$exportFileLineArray[$i][] = $jbOrderdate; // [AD] ABGANGSDATUM
|
|
$exportFileLineArray[$i][] = $jbOrderdate; // [AE] EMPFANGSDATUM
|
|
|
|
$exportFileLineArray[$i][] = ""; // [AF] REISEDATUM
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cr_sid"]); // [AG] TOURNUMMER
|
|
|
|
$tmpKey = substr(trim($retArray[$i]["cmp_comp4"]),3) . "0" . trim($retArray[$i]["jb_id"]) . "0" . pad(trim($retArray[$i]["tr_sort"]),2);
|
|
$exportFileLineArray[$i][] = $tmpKey; // [AH] DIENSTLEISTERREFERENZ
|
|
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["jb_id"]); // [AI] ORDERCODE
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_commission_no"]); // [AJ] AUFTRAGGEBERREFERENZ
|
|
$exportFileLineArray[$i][] = $tmpKey; // [AK] SENDUNGSSCHLÜSSEL
|
|
$exportFileLineArray[$i][] = "PK"; // [AL] PACKMITTEL
|
|
$exportFileLineArray[$i][] = $tmpExtraQM; // [AM] ANZAHL
|
|
$exportFileLineArray[$i][] = ($tmpExtraQM * 150); // [AN] BRUTTO
|
|
$exportFileLineArray[$i][] = ""; // [AO] NETTO
|
|
$exportFileLineArray[$i][] = ""; // [AP] VOLUMEN
|
|
$exportFileLineArray[$i][] = ""; // [AQ] LAENGE
|
|
$exportFileLineArray[$i][] = ""; // [AR] BREITE
|
|
$exportFileLineArray[$i][] = ""; // [AS] HOEHE
|
|
$exportFileLineArray[$i][] = ""; // [AT] PACKINHALT
|
|
endif;
|
|
endfor;
|
|
$exportFileLineArrayLen = count($exportFileLineArray);
|
|
|
|
// Write export data
|
|
$exportFileLineArrayKeys = array_keys($exportFileLineArray);
|
|
$exportFileLineArrayKeysLen = count($exportFileLineArrayKeys);
|
|
if ($exportFileLineArrayKeysLen > 0) :
|
|
for ($i = 0; $i < $exportFileLineArrayKeysLen; $i++) :
|
|
if (is_array($exportFileLineArray[$exportFileLineArrayKeys[$i]])) :
|
|
$line = implode($f_delimiter, $exportFileLineArray[$exportFileLineArrayKeys[$i]]);
|
|
// echo $line . "<br>";
|
|
writeToFile($globalExportPath . $f_secretFileName, $line);
|
|
endif;
|
|
endfor;
|
|
|
|
// insert file-reference into the exportfiles
|
|
insertStmt("exportfiles", array("hq_id", $hq_id, "cs_id", $customerId, "expf_name", $f_fileName, "expf_cryptname",$f_secretFileName));
|
|
|
|
$statusMessage = "Die Daten wurden exportiert!";
|
|
endif;
|
|
endif;
|
|
endif;
|
|
|
|
return $retArray;
|
|
}
|
|
|
|
|
|
function special_export_02 ($dbConnection = "") {
|
|
global $db, $PHP_SELF;
|
|
global $hq_id, $usr_id, $customerId, $cscIdRoot, $cscIdActual, $cscNameActual, $cscPathActual, $fromDateRange, $toDateRange;
|
|
global $statusMessage, $f_delimiter, $f_fileName, $globalExportPath;
|
|
|
|
$retArray = array();
|
|
if (!is_object($dbConnection) || $dbConnection == "") : $dbConnection = $db; endif;
|
|
|
|
// Get status of the customer according to being meta customer
|
|
// If not then export only data of the current customer else export all data (group customer AND meta customer) and ignore costcenter hierarchy
|
|
$isMetaCs = "0";
|
|
if (existsEntry("customer",array("cs_id_parent",$customerId))) :
|
|
$isMetaCs = "1";
|
|
endif;
|
|
|
|
$where_clause = "";
|
|
if ($isMetaCs == "0") :
|
|
$where_clause = " ((jb.csc_id_payer = '" . $cscIdActual . "' AND jb.csc_id_payer = csc.csc_id) OR "
|
|
. " (jb.csc_id_payer = csc.csc_id AND csc.csc_path LIKE '%//" . $cscNameActual . "//%')) AND ";
|
|
endif;
|
|
|
|
$sqlquery = "SELECT jb.jb_id, jb.jb_ordertime, jb.cr_sid, jb.hq_id, jb.vht_id, mt.mt_value, jb.vht_id_real, mt2.mt_value AS mt_value_real,"
|
|
. " tr.tr_id, tr.tr_sort, tr.tr_comp, tr.tr_person, tr.tr_commission_no, tr.tr_ware_from_to, tr.csc_id, tr.tr_finishtime, tr.tr_remark,"
|
|
. " gdcTrVht.gdc_content AS tr_vht_id, mtTrVht.mt_value AS tr_vht_type,"
|
|
. " ad.ad_street, tr.tr_hsno, ad.ad_zipcode, ad.ad_city, ad.ad_country,"
|
|
. " cs.cs_eid, cmp.cmp_id, cmp.cmp_comp, cmp.cmp_comp2, cmp.cmp_comp3, cmp.cmp_comp4,"
|
|
. " ad_hq.ad_zipcode AS hq_ad_zipcode, csc_tr.csc_name AS tr_csc_name, cmp_tr.cmp_comp4 AS tr_cmp_comp4,"
|
|
. " cs_parent.cs_eid AS cs_parent_eid, cmp_parent.cmp_comp3 AS cmp_parent_comp3,"
|
|
. " jbc_tr.srv_name, jbc_tr.srv_id, jbc_tr.srvt_name, jbc_tr.srvt_id, "
|
|
. " jbc_tr.jbc_amount, jbc_tr.jbc_price, jbc_tr.jbc_totalprice,"
|
|
. " gdc_services.gdc_content AS gdc_services,"
|
|
. " LEFT(gdc_arr_tour.gdc_content,14) AS gdc_arr_tour_content,"
|
|
. " gdc_info_0.gdc_content AS tr_info_0,"
|
|
. " gdcTotalWeight.gdc_content AS trat_sum_weight,"
|
|
. " gdcTotalPieces.gdc_content AS trat_sum_pieces,"
|
|
. " gdcTotalVolume.gdc_content AS trat_sum_volume"
|
|
. " FROM costcenter AS csc,"
|
|
. " customer AS cs LEFT JOIN customer AS cs_parent ON cs.cs_id_parent = cs_parent.cs_id"
|
|
. " LEFT JOIN company AS cmp_parent ON cs_parent.cmp_id = cmp_parent.cmp_id,"
|
|
. " company AS cmp, address AS ad,"
|
|
. " job AS jb LEFT JOIN metatype AS mt ON mt.mt_type = 'vehicletype' AND mt.mt_sort = jb.vht_id"
|
|
. " LEFT JOIN metatype AS mt2 ON mt2.mt_type = 'vehicletype' AND mt2.mt_sort = jb.vht_id_real,"
|
|
. " tour AS tr LEFT JOIN costcenter AS csc_tr ON csc_tr.csc_id = tr.csc_id"
|
|
. " LEFT JOIN customer AS cs_tr ON cs_tr.cs_id = csc_tr.cs_id"
|
|
. " LEFT JOIN company AS cmp_tr ON cmp_tr.cmp_id = cs_tr.cmp_id"
|
|
. " LEFT JOIN jobcalculator AS jbc_tr ON jbc_tr.tr_id = tr.tr_id"
|
|
. " LEFT JOIN genericdatacontainer AS gdc_services ON gdc_services.gdc_obj_id = tr.tr_id AND gdc_services.gdc_obj_type = 'tr' AND gdc_services.gdc_gen_fieldname = 'services'"
|
|
. " LEFT JOIN genericdatacontainer AS gdc_arr_tour ON gdc_arr_tour.gdc_obj_id = tr.tr_id AND gdc_arr_tour.gdc_obj_type = 'tr' AND gdc_arr_tour.gdc_gen_fieldname = 'arr_tour'"
|
|
. " LEFT JOIN genericdatacontainer AS gdc_info_0 ON gdc_info_0.gdc_obj_id = tr.tr_id AND gdc_info_0.gdc_obj_type = 'tr' AND gdc_info_0.gdc_gen_fieldname = 'info_0'"
|
|
. " LEFT JOIN genericdatacontainer AS gdcTotalWeight ON tr.tr_id = gdcTotalWeight.gdc_obj_id AND gdcTotalWeight.gdc_obj_type = 'tr' AND gdcTotalWeight.gdc_gen_fieldname = 'trat_sum_weight'"
|
|
. " LEFT JOIN genericdatacontainer AS gdcTotalPieces ON tr.tr_id = gdcTotalPieces.gdc_obj_id AND gdcTotalPieces.gdc_obj_type = 'tr' AND gdcTotalPieces.gdc_gen_fieldname = 'trat_sum_pieces'"
|
|
. " LEFT JOIN genericdatacontainer AS gdcTotalVolume ON tr.tr_id = gdcTotalVolume.gdc_obj_id AND gdcTotalVolume.gdc_obj_type = 'tr' AND gdcTotalVolume.gdc_gen_fieldname = 'trat_sum_volume'"
|
|
. " LEFT JOIN genericdatacontainer AS gdcTrVht ON tr.tr_id = gdcTrVht.gdc_obj_id AND gdcTrVht.gdc_obj_type = 'tr' AND gdcTrVht.gdc_gen_fieldname = 'vht_id_booking'"
|
|
. " LEFT JOIN metatype AS mtTrVht ON mtTrVht.mt_type = 'vehicletype' AND mtTrVht.mt_sort = gdcTrVht.gdc_content,"
|
|
. " headquarters AS hq LEFT JOIN company AS cmp_hq ON cmp_hq.cmp_id = hq.cmp_id LEFT JOIN address AS ad_hq ON ad_hq.ad_id = cmp_hq.ad_id"
|
|
. " WHERE jb.jb_status = '2' AND"
|
|
. " (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND"
|
|
. " jb.csc_id_payer = csc.csc_id AND"
|
|
. " csc.cs_id = cs.cs_id AND " . ($isMetaCs == "1" ? " cs.cs_id_parent " : " cs.cs_id ") . " = '" . $customerId . "' AND"
|
|
. $where_clause
|
|
. " cs.cmp_id = cmp.cmp_id AND"
|
|
. ($fromDateRange != "" ? " jb.jb_finishtime >= '$fromDateRange' AND " : "")
|
|
. ($toDateRange != "" ? " jb.jb_finishtime <= '$toDateRange' AND " : "")
|
|
. " jb.jb_id = tr.jb_id AND "
|
|
. " tr.ad_id = ad.ad_id AND "
|
|
. " jb.hq_id = hq.hq_id"
|
|
. " ORDER BY jb.jb_id, tr.tr_sort";
|
|
// echo $sqlquery . "<br><br>"; die();
|
|
$result = $dbConnection->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: <br>$sqlquery<br>" . $result->getMessage());
|
|
|
|
$count = 0;
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count] = $row;
|
|
// Add jobcalculator data
|
|
// $retArray[$count]["jbc_data"] = getCalculatorData($row["jb_id"], $row["tr_sort"]); // OBSOLETE, because in statement
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
$retArrayLen = count($retArray);
|
|
// print_r($retArray);
|
|
|
|
// Write file
|
|
if ($f_fileName != "") :
|
|
|
|
// Check existence of this name
|
|
if (!existsEntry("exportfiles",array("hq_id",$hq_id,"cs_id",$customerId,"expf_name",$f_fileName))) :
|
|
|
|
// Create secret filename
|
|
$f_secretFileName = "cs_se_02_" . md5($f_fileName);
|
|
|
|
// Set defined delimiter, may not be empty (!!!!)
|
|
if ($f_delimiter == "") : $f_delimiter = ";"; endif;
|
|
|
|
// Iterate complete array to substitute all existing fields having the delimiter (!!!!)
|
|
for ($i = 0; $i < $retArrayLen; $i++) :
|
|
$tmpArrKeys = array_keys($retArray[$i]);
|
|
$tmpArrKeysLen = count($tmpArrKeys);
|
|
for ($j = 0; $j < $tmpArrKeysLen; $j++) :
|
|
$aStr = str_replace($f_delimiter, " ", $retArray[$i][$tmpArrKeys[$j]]);
|
|
$aStr = str_replace("'", "", $aStr);
|
|
// $aStr = removeFieldSigns($aStr);
|
|
if (substr($aStr, 0, 1) == "\"" && substr($aStr, -1) == "\"") :
|
|
$aStr = substr($aStr, 1, -1);
|
|
endif;
|
|
$aStr = str_replace("\"", '', $aStr);
|
|
$retArray[$i][$tmpArrKeys[$j]] = $aStr;
|
|
endfor;
|
|
endfor;
|
|
|
|
// Get IDs of costcenters "EXTERN" of ALL headquarters (!!!!)
|
|
// $cscIdsExtern = getColVectorFromDB2ArrayByClause("parameter", "par_value", "par_key = 'CSC_ID_PAYER_EXTERN'", "hq_id", "", "");
|
|
|
|
$remJbId = "";
|
|
$remTrId = "";
|
|
$currTrSort = 0;
|
|
$exportFileLineArray = array();
|
|
for ($i = 0; $i < $retArrayLen; $i++) :
|
|
|
|
$displayRow = true;
|
|
|
|
/*
|
|
// Check "ware-from-to" to be set
|
|
$tmpTrWareFromTo = trim($retArray[$i]["tr_ware_from_to"]);
|
|
if (!($tmpTrWareFromTo != "" && is_numeric($tmpTrWareFromTo) && $tmpTrWareFromTo >= "1")) :
|
|
// $displayRow = false;
|
|
endif;
|
|
|
|
// Check payer equals station customer
|
|
if ($displayRow) :
|
|
$trCmpComp4 = "";
|
|
if (strtoupper(trim($retArray[$i]["tr_csc_name"])) != "EXTERN" && trim($retArray[$i]["tr_cmp_comp4"]) != "") :
|
|
$trCmpComp4 = strtoupper(trim($retArray[$i]["tr_cmp_comp4"]));
|
|
if (strtoupper(trim($retArray[$i]["cmp_comp4"])) == $trCmpComp4) :
|
|
$displayRow = false;
|
|
// Check pick up with calculator item
|
|
if ($tmpTrWareFromTo == "1" || $tmpTrWareFromTo == "3") :
|
|
if (existsEntry("jobcalculator",array("jb_id",$retArray[$i]["jb_id"],"tr_id",$retArray[$i]["tr_id"]))) :
|
|
$displayRow = true;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
|
|
if (!$displayRow) :
|
|
if ($remJbId == "" || $remJbId != $retArray[$i]["jb_id"]) :
|
|
if ($retArray[$i]["tr_sort"] == "1") :
|
|
if (existsEntry("jobcalculator",array("jb_id",$retArray[$i]["jb_id"],"tr_id",$retArray[$i]["tr_id"]))) :
|
|
$displayRow = true;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
*/
|
|
|
|
if ($displayRow) :
|
|
|
|
// Generate Lfd of the single station
|
|
if ($remJbId == "" || $remJbId != $retArray[$i]["jb_id"]) :
|
|
$currTrSort = 0;
|
|
$remJbId = $retArray[$i]["jb_id"];
|
|
$remTrId = $retArray[$i]["tr_id"];
|
|
$currTrSort++;
|
|
endif;
|
|
if ($remTrId != $retArray[$i]["tr_id"]) :
|
|
$remTrId = $retArray[$i]["tr_id"];
|
|
$currTrSort++;
|
|
endif;
|
|
|
|
// Calculator data of the current station
|
|
$tmpCalc01Array = array();
|
|
$tmpCalc02Array = array();
|
|
$tmpExtraQM = 2;
|
|
$tmpExtraHK = 1;
|
|
$tmpExtraHours = 0.5;
|
|
// $jbcDataArray = $retArray[$i]["jbc_data"];
|
|
// $jbcDataArrayLen = count($jbcDataArray);
|
|
|
|
$vehicleCategory = trim($retArray[$i]["mt_value"]); // Init vehicle category with vehicle type by job and metatype
|
|
$zone = trim($retArray[$i]["ad_zipcode"]); // Init zone with zipcode
|
|
$fixTime = false;
|
|
$craneHours = 0;
|
|
$priceExtraKM = 0;
|
|
$sumPriceExtraKM = 0;
|
|
|
|
// array($row["srv_name"], $row["jbc_totalprice"], $row["jbc_amount"], $row["jbc_price"], $row["srv_id"], $row["srvt_name"]);
|
|
/*
|
|
for ($j = 0; $j < $jbcDataArrayLen; $j++) :
|
|
$jbcVal = trim($jbcDataArray[$j][0]);
|
|
$jbcVal2 = trim($jbcDataArray[$j][5]);
|
|
|
|
// ARR 01
|
|
if ($jbcVal == "Kran" || strtoupper(substr($jbcVal,0,3)) == "FZG") :
|
|
$tmpCalc01Array[] = $jbcVal;
|
|
$vehicleCategory = $jbcVal;
|
|
endif;
|
|
|
|
// ARR 02
|
|
if (strtoupper(substr($jbcVal2,-6)) == "RADIUS") :
|
|
$tmpCalc02Array[] = $jbcVal2;
|
|
$zone = $jbcVal2;
|
|
endif;
|
|
if (!(strpos(strtoupper($jbcVal2), "FIX") === false) && !(strpos(strtoupper($jbcVal2), "UHR") === false)) :
|
|
$tmpCalc02Array[] = $jbcVal2;
|
|
$fixTime = true;
|
|
endif;
|
|
if (strtoupper($jbcVal2) == "KRAN MEHRSTUNDE") :
|
|
$craneHours = $jbcDataArray[$j][2];
|
|
if ($craneHours > 0) :
|
|
$tmpCalc02Array[] = $jbcVal;
|
|
$craneHours += 4;
|
|
endif;
|
|
endif;
|
|
if (strtoupper($jbcVal2) == "MEHRKILOMETER") :
|
|
$priceExtraKM = $jbcDataArray[$j][3];
|
|
$sumPriceExtraKM = $jbcDataArray[$j][1];
|
|
if ($sumPriceExtraKM > 0) :
|
|
$tmpCalc02Array[] = $jbcVal;
|
|
endif;
|
|
endif;
|
|
endfor;
|
|
$tmpCalc01 = implode(",", $tmpCalc01Array);
|
|
$tmpCalc02 = implode(",", $tmpCalc02Array);
|
|
*/
|
|
|
|
// Services by import
|
|
$gdcServicesArray = getKeyValueArrayFromString($retArray[$i]["gdc_services"]);
|
|
|
|
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["jb_id"]); // [A] AUFTRAGSNUMMER
|
|
|
|
$tmpTime = trim($retArray[$i]["jb_ordertime"]);
|
|
$jbOrderdate = substr($tmpTime,8,2) . "." . substr($tmpTime,5,2) . "." . substr($tmpTime,0,4);
|
|
$exportFileLineArray[$i][] = $jbOrderdate; // [B] ZUSTELLDATUM
|
|
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cs_eid"]); // [C] KUNDENNUMMER
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cr_sid"]); // [D] FAHRZEUG
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cmp_comp4"]); // [E] VERS. KOSTENSTELLENNUMMER
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cmp_comp3"]); // [F] FIRMA_3 ("KOSTENSTELLE")
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cmp_comp2"]); // [G] FIRMA_2 (ZUSATZ)
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cmp_parent_comp3"]); // [H] PREISGRUPPE
|
|
$exportFileLineArray[$i][] = $currTrSort; // [I] ETAPPE_IN_AUFTRAG
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_comp"]); // [J] EMPFÄNGER_NAME
|
|
$streetHsno = trim(trim($retArray[$i]["ad_street"]) . " " . trim($retArray[$i]["tr_hsno"]));
|
|
$exportFileLineArray[$i][] = $streetHsno; // [K] EMPFÄNGER_STRASSE
|
|
$tmpZipcode = trim($retArray[$i]["ad_zipcode"]);
|
|
if (is_numeric($tmpZipcode)) :
|
|
$tmpZipcode = pad($tmpZipcode,5);
|
|
endif;
|
|
$exportFileLineArray[$i][] = $tmpZipcode; // [L] EMPFÄNGER_PLZ
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["ad_city"]); // [M] EMPFÄNGER_ORT
|
|
|
|
$tmpVal = trim($retArray[$i]["ad_country"]);
|
|
if ($tmpVal == "" || $tmpVal == "D") : $tmpVal = "DE"; endif;
|
|
$exportFileLineArray[$i][] = $tmpVal; // [N] EMPFÄNGER_LAND
|
|
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_commission_no"]); // [O] SENDUNGSNUMMER
|
|
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_vht_type"]); // [P] BESTELLTER FAHRZEUGTYP
|
|
|
|
$exportFileLineArray[$i][] = $vehicleCategory; // [Q] FAHRZEUGKATEGORIE
|
|
|
|
if (strtoupper(substr($retArray[$i]["srvt_name"],-6)) == "RADIUS") :
|
|
$zone = $retArray[$i]["srvt_name"];
|
|
$exportFileLineArray[$i][] = $zone; // [R] ZONE
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
|
|
$trRemark = trim($retArray[$i]["tr_remark"]);
|
|
$tmpPos = strpos($trRemark, "Zeitfenster");
|
|
if (!($tmpPos === false)) :
|
|
$tmpVal = substr($trRemark, ($tmpPos + 13), 3);
|
|
$tmpPosV = strpos(strtolower($tmpVal), "v");
|
|
$tmpPosN = strpos(strtolower($tmpVal), "n");
|
|
$tmpPosS = strpos(strtolower($tmpVal), "s");
|
|
$tmpPosG = strpos(strtolower($tmpVal), "g");
|
|
if (!($tmpPosV === false)) :
|
|
$exportFileLineArray[$i][] = "v"; // [S] GEBUCHTES_ZEITFENSTER
|
|
elseif (!($tmpPosN === false)) :
|
|
$exportFileLineArray[$i][] = "n"; // [S] GEBUCHTES_ZEITFENSTER
|
|
elseif (!($tmpPosS === false)) :
|
|
$exportFileLineArray[$i][] = "s"; // [S] GEBUCHTES_ZEITFENSTER
|
|
elseif (!($tmpPosG === false)) :
|
|
$exportFileLineArray[$i][] = "g"; // [S] GEBUCHTES_ZEITFENSTER
|
|
else:
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
|
|
/*
|
|
if ($fixTime) :
|
|
$exportFileLineArray[$i][] = substr($tmpTime,-8,5); // [T] GEBUCHTE_FIXUHRZEIT
|
|
*/
|
|
if (trim($gdcServicesArray["FU"]) != "") :
|
|
$exportFileLineArray[$i][] = trim($gdcServicesArray["FU"]); // [T] GEBUCHTE_FIXUHRZEIT
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
|
|
if (strtoupper($retArray[$i]["srvt_name"]) == "KRAN MEHRSTUNDE") :
|
|
$craneHours = $retArray[$i]["jbc_amount"];
|
|
if ($craneHours > 0) :
|
|
$craneHours += 4;
|
|
// $retArray[$i]["jbc_amount"] = $craneHours;
|
|
endif;
|
|
endif;
|
|
if ($craneHours > 0) :
|
|
$exportFileLineArray[$i][] = $craneHours; // [U] KRAN_MEHRSTUNDEN
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
|
|
$tmpVal = trim($retArray[$i]["gdc_arr_tour_content"]);
|
|
if ($tmpVal != "") :
|
|
// $tmpVal = substr($tmpVal,0,4) . "-" . substr($tmpVal,4,2) . "-" . substr($tmpVal,6,2) . " " . substr($tmpVal,8,2) . ":" . substr($tmpVal,10,2);
|
|
$tmpVal = substr($tmpVal,8,2) . ":" . substr($tmpVal,10,2);
|
|
endif;
|
|
$exportFileLineArray[$i][] = $tmpVal; // [V] ANKUNFTSZEIT
|
|
$exportFileLineArray[$i][] = substr($retArray[$i]["tr_finishtime"],-8,5);; // [W] ABFAHRTSZEIT
|
|
|
|
// $exportFileLineArray[$i][] = $tmpCalc01; // [X] PREISTYP
|
|
$exportFileLineArray[$i][] = $retArray[$i]["srv_name"]; // [X] PREISTYP
|
|
|
|
// $exportFileLineArray[$i][] = $tmpCalc02; // [Y] LEISTUNGSTYP
|
|
$exportFileLineArray[$i][] = $retArray[$i]["srvt_name"]; // [Y] LEISTUNGSTYP
|
|
|
|
// $tmpVal = trim($retArray[$i]["trat_sum_weight"]);
|
|
$tmpVal = $retArray[$i]["jbc_amount"];
|
|
if ($tmpVal != "" && $tmpVal > 0) :
|
|
$tmpVal = str_replace (".", ",", $tmpVal);
|
|
$exportFileLineArray[$i][] = $tmpVal; // [Z] MENGE
|
|
else :
|
|
$exportFileLineArray[$i][] = ""; // [Z] MENGE
|
|
endif;
|
|
|
|
/*
|
|
$tmpVal = trim($retArray[$i]["trat_sum_volume"]);
|
|
if ($tmpVal != "" && $tmpVal > 0) :
|
|
$tmpVal = str_replace (".", ",", $tmpVal);
|
|
$exportFileLineArray[$i][] = $tmpVal; // [AA] STELLPLÄTZE
|
|
else :
|
|
$exportFileLineArray[$i][] = ""; // [AA] STELLPLÄTZE
|
|
endif;
|
|
|
|
if ($priceExtraKM > 0) :
|
|
$priceExtraKM = str_replace (".", ",", $priceExtraKM);
|
|
$exportFileLineArray[$i][] = $priceExtraKM; // [AB] EINZELPREIS
|
|
else :
|
|
$exportFileLineArray[$i][] = ""; // [AB] EINZELPREIS
|
|
endif;
|
|
|
|
if ($sumPriceExtraKM > 0) :
|
|
$sumPriceExtraKM = str_replace (".", ",", $sumPriceExtraKM);
|
|
$exportFileLineArray[$i][] = $sumPriceExtraKM; // [AC] GESAMTPREIS
|
|
else :
|
|
$exportFileLineArray[$i][] = ""; // [AC] GESAMTPREIS
|
|
endif;
|
|
*/
|
|
|
|
$tmpVal = $retArray[$i]["jbc_price"];
|
|
if ($tmpVal > 0) :
|
|
$tmpVal = str_replace (".", ",", $tmpVal);
|
|
$exportFileLineArray[$i][] = $tmpVal; // [AA] EINZELPREIS
|
|
else :
|
|
$exportFileLineArray[$i][] = ""; // [AA] EINZELPREIS
|
|
endif;
|
|
|
|
$tmpVal = $retArray[$i]["jbc_totalprice"];
|
|
if ($tmpVal > 0) :
|
|
$tmpVal = str_replace (".", ",", $tmpVal);
|
|
$exportFileLineArray[$i][] = $tmpVal; // [AB] GESAMTPREIS
|
|
else :
|
|
$exportFileLineArray[$i][] = ""; // [AB] GESAMTPREIS
|
|
endif;
|
|
|
|
$trRemark = trim($retArray[$i]["tr_remark"]);
|
|
$trRemark = str_replace("\r\n", ",", $trRemark);
|
|
$trRemark = str_replace("\n\r", ",", $trRemark);
|
|
$trRemark = str_replace("\n", ",", $trRemark);
|
|
$trRemark = str_replace("\r", ",", $trRemark);
|
|
$trRemark = str_replace(",,,", ",", $trRemark);
|
|
$trRemark = str_replace(",,", ",", $trRemark);
|
|
$exportFileLineArray[$i][] = $trRemark; // [AC] BEMERKUNG
|
|
|
|
// if ($tmpTrWareFromTo == "1") :
|
|
// .......
|
|
// else :
|
|
// .......
|
|
// endif;
|
|
|
|
$tratRemark = "";
|
|
$tratRemarkArr = getColVectorFromDB2ArrayByClause("tourarticle", "trat_remark", "jb_id = '" . trim($retArray[$i]["jb_id"]) . "' AND tr_sort = '" . $currTrSort . "'", "", "", "");
|
|
$tratRemarkArrLen = count($tratRemarkArr);
|
|
if ($tratRemarkArrLen > 0) :
|
|
$tratRemarkArr2 = array();
|
|
for ($j = 0; $j < $tratRemarkArrLen; $j++) :
|
|
if (trim($tratRemarkArr[$j]) != "") :
|
|
$tratRemarkArr2[] = trim($tratRemarkArr[$j]);
|
|
endif;
|
|
endfor;
|
|
if (count($tratRemarkArr2) > 0) :
|
|
$tratRemark = implode(",", $tratRemarkArr2);
|
|
endif;
|
|
endif;
|
|
$exportFileLineArray[$i][] = $tratRemark; // [AD] TOURARTIKEL-BEMERKUNG
|
|
endif;
|
|
endfor;
|
|
$exportFileLineArrayLen = count($exportFileLineArray);
|
|
|
|
// Write export data
|
|
$exportFileLineArrayKeys = array_keys($exportFileLineArray);
|
|
$exportFileLineArrayKeysLen = count($exportFileLineArrayKeys);
|
|
if ($exportFileLineArrayKeysLen > 0) :
|
|
for ($i = 0; $i < $exportFileLineArrayKeysLen; $i++) :
|
|
if (is_array($exportFileLineArray[$exportFileLineArrayKeys[$i]])) :
|
|
$line = implode($f_delimiter, $exportFileLineArray[$exportFileLineArrayKeys[$i]]);
|
|
// echo $line . "<br>";
|
|
writeToFile($globalExportPath . $f_secretFileName, $line);
|
|
endif;
|
|
endfor;
|
|
|
|
// insert file-reference into the exportfiles
|
|
insertStmt("exportfiles", array("hq_id", $hq_id, "cs_id", $customerId, "expf_name", $f_fileName, "expf_cryptname",$f_secretFileName));
|
|
|
|
$statusMessage = "Die Daten wurden exportiert!";
|
|
endif;
|
|
endif;
|
|
endif;
|
|
|
|
return $retArray;
|
|
}
|
|
|
|
|
|
function special_export_03 ($dbConnection = "") {
|
|
global $db, $PHP_SELF;
|
|
global $hq_id, $usr_id, $customerId, $cscIdRoot, $cscIdActual, $cscNameActual, $cscPathActual, $fromDateRange, $toDateRange;
|
|
global $statusMessage, $f_delimiter, $f_fileName, $globalExportPath;
|
|
|
|
$retArray = array();
|
|
if (!is_object($dbConnection) || $dbConnection == "") : $dbConnection = $db; endif;
|
|
|
|
// Get status of the customer according to being meta customer
|
|
// If not then export only data of the current customer else export all data (group customer AND meta customer) and ignore costcenter hierarchy
|
|
$isMetaCs = "0";
|
|
if (existsEntry("customer",array("cs_id_parent",$customerId))) :
|
|
$isMetaCs = "1";
|
|
endif;
|
|
|
|
$where_clause = "";
|
|
if ($isMetaCs == "0") :
|
|
$where_clause = " ((jb.csc_id_payer = '" . $cscIdActual . "' AND jb.csc_id_payer = csc.csc_id) OR "
|
|
. " (jb.csc_id_payer = csc.csc_id AND csc.csc_path LIKE '%//" . $cscNameActual . "//%')) AND ";
|
|
endif;
|
|
|
|
$sqlquery = "SELECT jb.jb_id, jb.jb_ordertime, jb.jb_createtime, jb.cr_sid, jb.hq_id, jb.vht_id, mt.mt_value, jb.vht_id_real, mt2.mt_value AS mt_value_real,"
|
|
. " tr.tr_id, tr.tr_sort, tr.tr_comp, tr.tr_person, tr.tr_commission_no, tr.tr_ware_from_to, tr.csc_id, tr.tr_finishtime, tr.tr_remark,"
|
|
. " gdcTrVht.gdc_content AS tr_vht_id, mtTrVht.mt_value AS tr_vht_type,"
|
|
. " ad.ad_street, tr.tr_hsno, ad.ad_zipcode, ad.ad_city, ad.ad_country,"
|
|
. " cs.cs_eid, cmp.cmp_id, cmp.cmp_comp, cmp.cmp_comp2, cmp.cmp_comp3, cmp.cmp_comp4,"
|
|
. " ad_hq.ad_zipcode AS hq_ad_zipcode, csc_tr.csc_name AS tr_csc_name, cmp_tr.cmp_comp4 AS tr_cmp_comp4,"
|
|
. " cs_parent.cs_eid AS cs_parent_eid, cmp_parent.cmp_comp3 AS cmp_parent_comp3,"
|
|
. " jbc_tr.srv_name, jbc_tr.srv_id, jbc_tr.srvt_name, jbc_tr.srvt_id, "
|
|
. " jbc_tr.jbc_amount, jbc_tr.jbc_price, jbc_tr.jbc_totalprice,"
|
|
. " gdc_services.gdc_content AS gdc_services,"
|
|
. " LEFT(gdc_arr_tour.gdc_content,14) AS gdc_arr_tour_content,"
|
|
. " gdc_info_0.gdc_content AS tr_info_0,"
|
|
. " gdcTotalWeight.gdc_content AS trat_sum_weight,"
|
|
. " gdcTotalPieces.gdc_content AS trat_sum_pieces,"
|
|
. " gdcTotalVolume.gdc_content AS trat_sum_volume"
|
|
. " FROM costcenter AS csc,"
|
|
. " customer AS cs LEFT JOIN customer AS cs_parent ON cs.cs_id_parent = cs_parent.cs_id"
|
|
. " LEFT JOIN company AS cmp_parent ON cs_parent.cmp_id = cmp_parent.cmp_id,"
|
|
. " company AS cmp, address AS ad,"
|
|
. " job AS jb LEFT JOIN metatype AS mt ON mt.mt_type = 'vehicletype' AND mt.mt_sort = jb.vht_id"
|
|
. " LEFT JOIN metatype AS mt2 ON mt2.mt_type = 'vehicletype' AND mt2.mt_sort = jb.vht_id_real,"
|
|
. " tour AS tr LEFT JOIN costcenter AS csc_tr ON csc_tr.csc_id = tr.csc_id"
|
|
. " LEFT JOIN customer AS cs_tr ON cs_tr.cs_id = csc_tr.cs_id"
|
|
. " LEFT JOIN company AS cmp_tr ON cmp_tr.cmp_id = cs_tr.cmp_id"
|
|
. " LEFT JOIN jobcalculator AS jbc_tr ON jbc_tr.tr_id = tr.tr_id"
|
|
. " LEFT JOIN genericdatacontainer AS gdc_services ON gdc_services.gdc_obj_id = tr.tr_id AND gdc_services.gdc_obj_type = 'tr' AND gdc_services.gdc_gen_fieldname = 'services'"
|
|
. " LEFT JOIN genericdatacontainer AS gdc_arr_tour ON gdc_arr_tour.gdc_obj_id = tr.tr_id AND gdc_arr_tour.gdc_obj_type = 'tr' AND gdc_arr_tour.gdc_gen_fieldname = 'arr_tour'"
|
|
. " LEFT JOIN genericdatacontainer AS gdc_info_0 ON gdc_info_0.gdc_obj_id = tr.tr_id AND gdc_info_0.gdc_obj_type = 'tr' AND gdc_info_0.gdc_gen_fieldname = 'info_0'"
|
|
. " LEFT JOIN genericdatacontainer AS gdcTotalWeight ON tr.tr_id = gdcTotalWeight.gdc_obj_id AND gdcTotalWeight.gdc_obj_type = 'tr' AND gdcTotalWeight.gdc_gen_fieldname = 'trat_sum_weight'"
|
|
. " LEFT JOIN genericdatacontainer AS gdcTotalPieces ON tr.tr_id = gdcTotalPieces.gdc_obj_id AND gdcTotalPieces.gdc_obj_type = 'tr' AND gdcTotalPieces.gdc_gen_fieldname = 'trat_sum_pieces'"
|
|
. " LEFT JOIN genericdatacontainer AS gdcTotalVolume ON tr.tr_id = gdcTotalVolume.gdc_obj_id AND gdcTotalVolume.gdc_obj_type = 'tr' AND gdcTotalVolume.gdc_gen_fieldname = 'trat_sum_volume'"
|
|
. " LEFT JOIN genericdatacontainer AS gdcTrVht ON tr.tr_id = gdcTrVht.gdc_obj_id AND gdcTrVht.gdc_obj_type = 'tr' AND gdcTrVht.gdc_gen_fieldname = 'vht_id_booking'"
|
|
. " LEFT JOIN metatype AS mtTrVht ON mtTrVht.mt_type = 'vehicletype' AND mtTrVht.mt_sort = gdcTrVht.gdc_content,"
|
|
. " headquarters AS hq LEFT JOIN company AS cmp_hq ON cmp_hq.cmp_id = hq.cmp_id LEFT JOIN address AS ad_hq ON ad_hq.ad_id = cmp_hq.ad_id"
|
|
. " WHERE jb.jb_status = '2' AND"
|
|
. " (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND"
|
|
. " jb.csc_id_payer = csc.csc_id AND"
|
|
. " csc.cs_id = cs.cs_id AND " . ($isMetaCs == "1" ? " cs.cs_id_parent " : " cs.cs_id ") . " = '" . $customerId . "' AND"
|
|
. $where_clause
|
|
. " cs.cmp_id = cmp.cmp_id AND"
|
|
. ($fromDateRange != "" ? " jb.jb_finishtime >= '$fromDateRange' AND " : "")
|
|
. ($toDateRange != "" ? " jb.jb_finishtime <= '$toDateRange' AND " : "")
|
|
. " jb.jb_id = tr.jb_id AND "
|
|
. " tr.ad_id = ad.ad_id AND "
|
|
. " jb.hq_id = hq.hq_id"
|
|
. " ORDER BY jb.jb_id, tr.tr_sort";
|
|
// echo $sqlquery . "<br><br>"; die();
|
|
$result = $dbConnection->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: <br>$sqlquery<br>" . $result->getMessage());
|
|
|
|
$count = 0;
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count] = $row;
|
|
// Add jobcalculator data
|
|
// $retArray[$count]["jbc_data"] = getCalculatorData($row["jb_id"], $row["tr_sort"]); // OBSOLETE, because in statement
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
$retArrayLen = count($retArray);
|
|
// print_r($retArray);
|
|
|
|
// Write file
|
|
if ($f_fileName != "") :
|
|
|
|
// Check existence of this name
|
|
if (!existsEntry("exportfiles",array("hq_id",$hq_id,"cs_id",$customerId,"expf_name",$f_fileName))) :
|
|
|
|
// Create secret filename
|
|
$f_secretFileName = "cs_se_02_" . md5($f_fileName);
|
|
|
|
// Set defined delimiter, may not be empty (!!!!)
|
|
if ($f_delimiter == "") : $f_delimiter = ";"; endif;
|
|
|
|
// Iterate complete array to substitute all existing fields having the delimiter (!!!!)
|
|
for ($i = 0; $i < $retArrayLen; $i++) :
|
|
$tmpArrKeys = array_keys($retArray[$i]);
|
|
$tmpArrKeysLen = count($tmpArrKeys);
|
|
for ($j = 0; $j < $tmpArrKeysLen; $j++) :
|
|
$aStr = str_replace($f_delimiter, " ", $retArray[$i][$tmpArrKeys[$j]]);
|
|
$aStr = str_replace("'", "", $aStr);
|
|
// $aStr = removeFieldSigns($aStr);
|
|
if (substr($aStr, 0, 1) == "\"" && substr($aStr, -1) == "\"") :
|
|
$aStr = substr($aStr, 1, -1);
|
|
endif;
|
|
$aStr = str_replace("\"", '', $aStr);
|
|
$retArray[$i][$tmpArrKeys[$j]] = $aStr;
|
|
endfor;
|
|
endfor;
|
|
|
|
// Get IDs of costcenters "EXTERN" of ALL headquarters (!!!!)
|
|
// $cscIdsExtern = getColVectorFromDB2ArrayByClause("parameter", "par_value", "par_key = 'CSC_ID_PAYER_EXTERN'", "hq_id", "", "");
|
|
|
|
$remJbId = "";
|
|
$remTrId = "";
|
|
$currTrSort = 0;
|
|
$exportFileLineArray = array();
|
|
for ($i = 0; $i < $retArrayLen; $i++) :
|
|
|
|
$displayRow = true;
|
|
|
|
/*
|
|
// Check "ware-from-to" to be set
|
|
$tmpTrWareFromTo = trim($retArray[$i]["tr_ware_from_to"]);
|
|
if (!($tmpTrWareFromTo != "" && is_numeric($tmpTrWareFromTo) && $tmpTrWareFromTo >= "1")) :
|
|
// $displayRow = false;
|
|
endif;
|
|
|
|
// Check payer equals station customer
|
|
if ($displayRow) :
|
|
$trCmpComp4 = "";
|
|
if (strtoupper(trim($retArray[$i]["tr_csc_name"])) != "EXTERN" && trim($retArray[$i]["tr_cmp_comp4"]) != "") :
|
|
$trCmpComp4 = strtoupper(trim($retArray[$i]["tr_cmp_comp4"]));
|
|
if (strtoupper(trim($retArray[$i]["cmp_comp4"])) == $trCmpComp4) :
|
|
$displayRow = false;
|
|
// Check pick up with calculator item
|
|
if ($tmpTrWareFromTo == "1" || $tmpTrWareFromTo == "3") :
|
|
if (existsEntry("jobcalculator",array("jb_id",$retArray[$i]["jb_id"],"tr_id",$retArray[$i]["tr_id"]))) :
|
|
$displayRow = true;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
|
|
if (!$displayRow) :
|
|
if ($remJbId == "" || $remJbId != $retArray[$i]["jb_id"]) :
|
|
if ($retArray[$i]["tr_sort"] == "1") :
|
|
if (existsEntry("jobcalculator",array("jb_id",$retArray[$i]["jb_id"],"tr_id",$retArray[$i]["tr_id"]))) :
|
|
$displayRow = true;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
endif;
|
|
*/
|
|
|
|
if ($displayRow) :
|
|
|
|
// Generate Lfd of the single station
|
|
if ($remJbId == "" || $remJbId != $retArray[$i]["jb_id"]) :
|
|
$currTrSort = 0;
|
|
$remJbId = $retArray[$i]["jb_id"];
|
|
$remTrId = $retArray[$i]["tr_id"];
|
|
$currTrSort++;
|
|
endif;
|
|
if ($remTrId != $retArray[$i]["tr_id"]) :
|
|
$remTrId = $retArray[$i]["tr_id"];
|
|
$currTrSort++;
|
|
endif;
|
|
|
|
// Calculator data of the current station
|
|
$tmpCalc01Array = array();
|
|
$tmpCalc02Array = array();
|
|
$tmpExtraQM = 2;
|
|
$tmpExtraHK = 1;
|
|
$tmpExtraHours = 0.5;
|
|
// $jbcDataArray = $retArray[$i]["jbc_data"];
|
|
// $jbcDataArrayLen = count($jbcDataArray);
|
|
|
|
$vehicleCategory = trim($retArray[$i]["mt_value"]); // Init vehicle category with vehicle type by job and metatype
|
|
$zone = trim($retArray[$i]["ad_zipcode"]); // Init zone with zipcode
|
|
$fixTime = false;
|
|
$craneHours = 0;
|
|
$priceExtraKM = 0;
|
|
$sumPriceExtraKM = 0;
|
|
|
|
// array($row["srv_name"], $row["jbc_totalprice"], $row["jbc_amount"], $row["jbc_price"], $row["srv_id"], $row["srvt_name"]);
|
|
/*
|
|
for ($j = 0; $j < $jbcDataArrayLen; $j++) :
|
|
$jbcVal = trim($jbcDataArray[$j][0]);
|
|
$jbcVal2 = trim($jbcDataArray[$j][5]);
|
|
|
|
// ARR 01
|
|
if ($jbcVal == "Kran" || strtoupper(substr($jbcVal,0,3)) == "FZG") :
|
|
$tmpCalc01Array[] = $jbcVal;
|
|
$vehicleCategory = $jbcVal;
|
|
endif;
|
|
|
|
// ARR 02
|
|
if (strtoupper(substr($jbcVal2,-6)) == "RADIUS") :
|
|
$tmpCalc02Array[] = $jbcVal2;
|
|
$zone = $jbcVal2;
|
|
endif;
|
|
if (!(strpos(strtoupper($jbcVal2), "FIX") === false) && !(strpos(strtoupper($jbcVal2), "UHR") === false)) :
|
|
$tmpCalc02Array[] = $jbcVal2;
|
|
$fixTime = true;
|
|
endif;
|
|
if (strtoupper($jbcVal2) == "KRAN MEHRSTUNDE") :
|
|
$craneHours = $jbcDataArray[$j][2];
|
|
if ($craneHours > 0) :
|
|
$tmpCalc02Array[] = $jbcVal;
|
|
$craneHours += 4;
|
|
endif;
|
|
endif;
|
|
if (strtoupper($jbcVal2) == "MEHRKILOMETER") :
|
|
$priceExtraKM = $jbcDataArray[$j][3];
|
|
$sumPriceExtraKM = $jbcDataArray[$j][1];
|
|
if ($sumPriceExtraKM > 0) :
|
|
$tmpCalc02Array[] = $jbcVal;
|
|
endif;
|
|
endif;
|
|
endfor;
|
|
$tmpCalc01 = implode(",", $tmpCalc01Array);
|
|
$tmpCalc02 = implode(",", $tmpCalc02Array);
|
|
*/
|
|
|
|
// Services by import
|
|
$gdcServicesArray = getKeyValueArrayFromString($retArray[$i]["gdc_services"]);
|
|
|
|
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["jb_id"]); // [A] AUFTRAGSNUMMER
|
|
|
|
$alu = trim($retArray[$i]["tr_info_0"]);
|
|
if ($alu == "1") :
|
|
$exportFileLineArray[$i][] = "U"; // [B] A,L,U
|
|
elseif ($alu == "2") :
|
|
$exportFileLineArray[$i][] = "A"; // [B] A,L,U
|
|
else :
|
|
$exportFileLineArray[$i][] = "L"; // [B] A,L,U
|
|
endif;
|
|
|
|
$tmpTime = trim($retArray[$i]["jb_createtime"]);
|
|
$jbCreatedate = substr($tmpTime,8,2) . "." . substr($tmpTime,5,2) . "." . substr($tmpTime,0,4);
|
|
$exportFileLineArray[$i][] = $jbCreatedate; // [C] ERSTELLUNGSDATUM
|
|
|
|
$tmpTime = trim($retArray[$i]["jb_ordertime"]);
|
|
$jbOrderdate = substr($tmpTime,8,2) . "." . substr($tmpTime,5,2) . "." . substr($tmpTime,0,4);
|
|
$exportFileLineArray[$i][] = $jbOrderdate; // [D] ZUSTELLDATUM
|
|
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cs_eid"]); // [E] KUNDENNUMMER
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cr_sid"]); // [F] FAHRZEUG
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cmp_comp4"]); // [G] VERS. KOSTENSTELLENNUMMER
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cmp_comp3"]); // [H] FIRMA_3 ("KOSTENSTELLE")
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cmp_comp2"]); // [I] FIRMA_2 (ZUSATZ)
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cmp_parent_comp3"]); // [J] PREISGRUPPE
|
|
$exportFileLineArray[$i][] = $currTrSort; // [K] ETAPPE_IN_AUFTRAG
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_comp"]); // [L] EMPFÄNGER_NAME
|
|
$streetHsno = trim(trim($retArray[$i]["ad_street"]) . " " . trim($retArray[$i]["tr_hsno"]));
|
|
$exportFileLineArray[$i][] = $streetHsno; // [M] EMPFÄNGER_STRASSE
|
|
$tmpZipcode = trim($retArray[$i]["ad_zipcode"]);
|
|
if (is_numeric($tmpZipcode)) :
|
|
$tmpZipcode = pad($tmpZipcode,5);
|
|
endif;
|
|
$exportFileLineArray[$i][] = $tmpZipcode; // [N] EMPFÄNGER_PLZ
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["ad_city"]); // [O] EMPFÄNGER_ORT
|
|
|
|
$tmpVal = trim($retArray[$i]["ad_country"]);
|
|
if ($tmpVal == "" || $tmpVal == "D") : $tmpVal = "DE"; endif;
|
|
$exportFileLineArray[$i][] = $tmpVal; // [P] EMPFÄNGER_LAND
|
|
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_commission_no"]); // [Q] SENDUNGSNUMMER
|
|
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_vht_type"]); // [R] BESTELLTER FAHRZEUGTYP
|
|
|
|
$exportFileLineArray[$i][] = $vehicleCategory; // [S] FAHRZEUGKATEGORIE
|
|
|
|
if (strtoupper(substr($retArray[$i]["srvt_name"],0,3)) == "LKW") :
|
|
$zone = $retArray[$i]["srvt_name"];
|
|
$exportFileLineArray[$i][] = $zone; // [T] ZONE
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
|
|
/*
|
|
$trRemark = trim($retArray[$i]["tr_remark"]);
|
|
$tmpPos = strpos($trRemark, "Zeitfenster");
|
|
if (!($tmpPos === false)) :
|
|
$tmpVal = substr($trRemark, ($tmpPos + 13), 3);
|
|
$tmpPosV = strpos(strtolower($tmpVal), "v");
|
|
$tmpPosN = strpos(strtolower($tmpVal), "n");
|
|
$tmpPosS = strpos(strtolower($tmpVal), "s");
|
|
$tmpPosG = strpos(strtolower($tmpVal), "g");
|
|
if (!($tmpPosV === false)) :
|
|
$exportFileLineArray[$i][] = "v"; // [U] GEBUCHTES_ZEITFENSTER
|
|
elseif (!($tmpPosN === false)) :
|
|
$exportFileLineArray[$i][] = "n"; // [U] GEBUCHTES_ZEITFENSTER
|
|
elseif (!($tmpPosS === false)) :
|
|
$exportFileLineArray[$i][] = "s"; // [U] GEBUCHTES_ZEITFENSTER
|
|
elseif (!($tmpPosG === false)) :
|
|
$exportFileLineArray[$i][] = "g"; // [U] GEBUCHTES_ZEITFENSTER
|
|
else:
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
*/
|
|
|
|
if (trim($gdcServicesArray["GZ"]) != "") :
|
|
$exportFileLineArray[$i][] = trim($gdcServicesArray["GZ"]); // [U] GEBUCHTES_ZEITFENSTER
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
|
|
if (trim($gdcServicesArray["FU"]) != "") :
|
|
$exportFileLineArray[$i][] = trim($gdcServicesArray["FU"]); // [V] GEBUCHTE_FIXUHRZEIT
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
|
|
if (strtoupper($retArray[$i]["srvt_name"]) == "KRAN MEHRSTUNDE") :
|
|
$craneHours = $retArray[$i]["jbc_amount"];
|
|
if ($craneHours > 0) :
|
|
$craneHours += 4;
|
|
// $retArray[$i]["jbc_amount"] = $craneHours;
|
|
endif;
|
|
endif;
|
|
if ($craneHours > 0) :
|
|
$exportFileLineArray[$i][] = $craneHours; // [W] KRAN_MEHRSTUNDEN
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
|
|
$tmpVal = trim($retArray[$i]["gdc_arr_tour_content"]);
|
|
if ($tmpVal != "") :
|
|
// $tmpVal = substr($tmpVal,0,4) . "-" . substr($tmpVal,4,2) . "-" . substr($tmpVal,6,2) . " " . substr($tmpVal,8,2) . ":" . substr($tmpVal,10,2);
|
|
$tmpVal = substr($tmpVal,8,2) . ":" . substr($tmpVal,10,2);
|
|
endif;
|
|
$exportFileLineArray[$i][] = $tmpVal; // [X] ANKUNFTSZEIT
|
|
$exportFileLineArray[$i][] = substr($retArray[$i]["tr_finishtime"],-8,5);; // [Y] ABFAHRTSZEIT
|
|
|
|
// $exportFileLineArray[$i][] = $tmpCalc01; // [Z] PREISTYP
|
|
$exportFileLineArray[$i][] = $retArray[$i]["srv_name"]; // [Z] PREISTYP
|
|
|
|
// $exportFileLineArray[$i][] = $tmpCalc02; // [AA] LEISTUNGSTYP
|
|
$exportFileLineArray[$i][] = $retArray[$i]["srvt_name"]; // [AA] LEISTUNGSTYP
|
|
|
|
$tmpVal = trim($retArray[$i]["trat_sum_weight"]);
|
|
if ($tmpVal > 0) :
|
|
// $tmpVal = str_replace (".", ",", $tmpVal);
|
|
$exportFileLineArray[$i][] = $tmpVal; // [AB] GESAMTGEWICHT
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
|
|
$tmpVal = trim($retArray[$i]["trat_sum_pieces"]);
|
|
if ($tmpVal > 0) :
|
|
$tmpVal = str_replace (".", ",", $tmpVal);
|
|
$exportFileLineArray[$i][] = $tmpVal; // [AC] GESAMTSTELLPLÄTZE
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
|
|
$tmpVal = $retArray[$i]["jbc_amount"];
|
|
if ($tmpVal != "" && $tmpVal > 0) :
|
|
$tmpVal = str_replace (".", ",", $tmpVal);
|
|
$exportFileLineArray[$i][] = $tmpVal; // [AD] MENGE
|
|
else :
|
|
$exportFileLineArray[$i][] = ""; // [AD] MENGE
|
|
endif;
|
|
|
|
$tmpVal = $retArray[$i]["jbc_price"];
|
|
if ($tmpVal > 0) :
|
|
$tmpVal = str_replace (".", ",", $tmpVal);
|
|
$exportFileLineArray[$i][] = $tmpVal; // [AE] EINZELPREIS
|
|
else :
|
|
$exportFileLineArray[$i][] = ""; // [AE] EINZELPREIS
|
|
endif;
|
|
|
|
$tmpVal = $retArray[$i]["jbc_totalprice"];
|
|
if ($tmpVal > 0) :
|
|
$tmpVal = str_replace (".", ",", $tmpVal);
|
|
$exportFileLineArray[$i][] = $tmpVal; // [AF] GESAMTPREIS
|
|
else :
|
|
$exportFileLineArray[$i][] = ""; // [AF] GESAMTPREIS
|
|
endif;
|
|
|
|
$trRemark = trim($retArray[$i]["tr_remark"]);
|
|
$trRemark = str_replace("\r\n", ",", $trRemark);
|
|
$trRemark = str_replace("\n\r", ",", $trRemark);
|
|
$trRemark = str_replace("\n", ",", $trRemark);
|
|
$trRemark = str_replace("\r", ",", $trRemark);
|
|
$trRemark = str_replace(",,,", ",", $trRemark);
|
|
$trRemark = str_replace(",,", ",", $trRemark);
|
|
$exportFileLineArray[$i][] = $trRemark; // [AG] BEMERKUNG
|
|
|
|
// if ($tmpTrWareFromTo == "1") :
|
|
// .......
|
|
// else :
|
|
// .......
|
|
// endif;
|
|
|
|
$tratRemark = "";
|
|
$tratRemarkArr = getColVectorFromDB2ArrayByClause("tourarticle", "trat_remark", "jb_id = '" . trim($retArray[$i]["jb_id"]) . "' AND tr_sort = '" . $currTrSort . "'", "", "", "");
|
|
$tratRemarkArrLen = count($tratRemarkArr);
|
|
if ($tratRemarkArrLen > 0) :
|
|
$tratRemarkArr2 = array();
|
|
for ($j = 0; $j < $tratRemarkArrLen; $j++) :
|
|
if (trim($tratRemarkArr[$j]) != "") :
|
|
$tratRemarkArr2[] = trim($tratRemarkArr[$j]);
|
|
endif;
|
|
endfor;
|
|
if (count($tratRemarkArr2) > 0) :
|
|
$tratRemark = implode(",", $tratRemarkArr2);
|
|
endif;
|
|
endif;
|
|
$exportFileLineArray[$i][] = $tratRemark; // [AH] TOURARTIKEL-BEMERKUNG
|
|
endif;
|
|
endfor;
|
|
$exportFileLineArrayLen = count($exportFileLineArray);
|
|
|
|
// Write export data
|
|
$exportFileLineArrayKeys = array_keys($exportFileLineArray);
|
|
$exportFileLineArrayKeysLen = count($exportFileLineArrayKeys);
|
|
if ($exportFileLineArrayKeysLen > 0) :
|
|
for ($i = 0; $i < $exportFileLineArrayKeysLen; $i++) :
|
|
if (is_array($exportFileLineArray[$exportFileLineArrayKeys[$i]])) :
|
|
$line = implode($f_delimiter, $exportFileLineArray[$exportFileLineArrayKeys[$i]]);
|
|
// echo $line . "<br>";
|
|
writeToFile($globalExportPath . $f_secretFileName, $line);
|
|
endif;
|
|
endfor;
|
|
|
|
// insert file-reference into the exportfiles
|
|
insertStmt("exportfiles", array("hq_id", $hq_id, "cs_id", $customerId, "expf_name", $f_fileName, "expf_cryptname",$f_secretFileName));
|
|
|
|
$statusMessage = "Die Daten wurden exportiert!";
|
|
endif;
|
|
endif;
|
|
endif;
|
|
|
|
return $retArray;
|
|
}
|
|
|
|
|
|
// Special export function for any customer based on calculator functionality
|
|
function special_export_04 ($dbConnection = "") {
|
|
global $db, $PHP_SELF;
|
|
global $hq_id, $usr_id, $customerId, $cscIdRoot, $cscIdActual, $cscNameActual, $cscPathActual, $fromDateRange, $toDateRange;
|
|
global $statusMessage, $f_delimiter, $f_fileName, $globalExportPath;
|
|
|
|
$retArray = array();
|
|
if (!is_object($dbConnection) || $dbConnection == "") : $dbConnection = $db; endif;
|
|
|
|
// Get status of the customer according to being meta customer
|
|
// If not then export only data of the current customer else export all data (group customer AND meta customer) and ignore costcenter hierarchy
|
|
$isMetaCs = "0";
|
|
if (existsEntry("customer",array("cs_id_parent",$customerId))) :
|
|
$isMetaCs = "1";
|
|
endif;
|
|
|
|
$where_clause = "";
|
|
if ($isMetaCs == "0") :
|
|
if ($cscNameActual == "") :
|
|
$cscNameActual = getFieldValueFromId("costcenter","csc_id",$cscIdActual,"csc_name");
|
|
endif;
|
|
$where_clause = " ((jb.csc_id_payer = '" . $cscIdActual . "' AND jb.csc_id_payer = csc.csc_id) OR "
|
|
. " (jb.csc_id_payer = csc.csc_id AND csc.csc_path LIKE '%//" . $cscNameActual . "//%')) AND ";
|
|
endif;
|
|
|
|
// Get max number of stations
|
|
$s_max = getParameterValue("0", "EXPORT_SPECIAL_FUNCTION_MAX_NUM_OF_STATIONS_" . $customerId, "0");
|
|
if ($s_max == "" || !is_numeric($s_max)) : $s_max = 5; endif;
|
|
|
|
$sqlquery = "SELECT hq.hq_mnemonic, hq.hq_name, jb.hq_id, jb.jb_id, jb.jb_ordertime, jb.jb_warningtime, jb.jb_taketime, jb.jb_finishtime, jb.cr_sid, jb.jb_totalprice, jb.jb_freetext_1,";
|
|
for ($s = 1; $s <= $s_max; $s++) :
|
|
$sqlquery .= " tr" . $s . ".tr_finishtime AS tr" . $s . "_finishtime,"
|
|
. " tr" . $s . ".tr_commission_no AS tr" . $s . "_commission_no,"
|
|
. " CAST(LEFT(gdc" . $s . "_arr_tour.gdc_content,14) AS DATETIME) AS tr" . $s . "_arrivaltime,";
|
|
endfor;
|
|
$sqlquery .= " cs.cs_id, cs.cs_eid, cmp.cmp_id, cmp.cmp_comp, cmp.cmp_comp2, cmp.cmp_comp3, cmp.cmp_comp4,"
|
|
. " usr.usr_name, usr.usr_firstname, cr.cr_id_parent, cr_cmp.cmp_id AS cr_cmp_id, cr_cmp.cmp_comp AS cr_cmp_comp, cr_cmp.cmp_comp2 AS cr_cmp_comp2, cr_cmp.cmp_tax_idno AS cr_cmp_tax_idno, cr_cmp.cmp_stax_idno AS cr_cmp_stax_idno,"
|
|
. " cr_ad.ad_street AS cr_ad_street, cr_ad.ad_zipcode AS cr_ad_zipcode, cr_ad.ad_city AS cr_ad_city"
|
|
. " FROM headquarters AS hq, costcenter AS csc, customer AS cs, company AS cmp, courier AS cr, company AS cr_cmp, address AS cr_ad, user AS usr, job AS jb";
|
|
for ($s = 1; $s <= $s_max; $s++) :
|
|
$sqlquery .= " LEFT JOIN tour AS tr" . $s . " ON jb.jb_id = tr" . $s . ".jb_id AND tr" . $s . ".tr_sort = '" . $s . "'"
|
|
. " LEFT JOIN genericdatacontainer AS gdc" . $s . "_arr_tour ON gdc" . $s . "_arr_tour.gdc_obj_id = tr" . $s . ".tr_id AND gdc" . $s . "_arr_tour.gdc_obj_type = 'tr' AND gdc" . $s . "_arr_tour.gdc_gen_fieldname = 'arr_tour' "
|
|
. " LEFT JOIN genericdatacontainer AS gdc" . $s . "_tr_info_0 ON gdc" . $s . "_tr_info_0.gdc_obj_id = tr" . $s . ".tr_id AND gdc" . $s . "_tr_info_0.gdc_obj_type = 'tr' AND gdc" . $s . "_tr_info_0.gdc_gen_fieldname = 'info_0' "
|
|
. " LEFT JOIN genericdatacontainer AS gdc" . $s . "_tr_info_1 ON gdc" . $s . "_tr_info_1.gdc_obj_id = tr" . $s . ".tr_id AND gdc" . $s . "_tr_info_1.gdc_obj_type = 'tr' AND gdc" . $s . "_tr_info_1.gdc_gen_fieldname = 'info_1' ";
|
|
endfor;
|
|
$sqlquery .= " WHERE jb.jb_status = '2' AND"
|
|
. " (NOT isnull(jb.jb_export_time)) AND jb.jb_export_time != '0000-00-00 00:00:00' AND jb.jb_export_time != '9999-12-31 23:59:59' AND"
|
|
. " (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND"
|
|
. " jb.jb_totalprice > '0' AND"
|
|
. " jb.csc_id_payer = csc.csc_id AND"
|
|
. " csc.cs_id = cs.cs_id AND " . ($isMetaCs == "1" ? " cs.cs_id_parent " : " cs.cs_id ") . " = '" . $customerId . "' AND"
|
|
. $where_clause
|
|
. " cs.cmp_id = cmp.cmp_id AND"
|
|
. ($fromDateRange != "" ? " jb.jb_finishtime >= '$fromDateRange' AND " : "")
|
|
. ($toDateRange != "" ? " jb.jb_finishtime <= '$toDateRange' AND " : "")
|
|
. " jb.hq_id = hq.hq_id AND"
|
|
. " jb.cr_id = cr.cr_id AND"
|
|
. " cr.cmp_id = cr_cmp.cmp_id AND"
|
|
. " cr_cmp.ad_id = cr_ad.ad_id AND"
|
|
. " cr.usr_id = usr.usr_id";
|
|
$sqlquery .= " ORDER BY jb.jb_id";
|
|
// echo $sqlquery . "<br><br>"; die();
|
|
$result = $dbConnection->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: <br>$sqlquery<br>" . $result->getMessage());
|
|
|
|
$count = 0;
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count] = $row;
|
|
// Add jobcalculator data
|
|
$retArray[$count]["jbc_data"] = getCalculatorData($row["jb_id"], "0");
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
$retArrayLen = count($retArray);
|
|
// print_r($retArray);
|
|
|
|
// Write file
|
|
if ($f_fileName != "") :
|
|
|
|
// Check existence of this name
|
|
if (!existsEntry("exportfiles",array("hq_id",$hq_id,"cs_id",$customerId,"expf_name",$f_fileName))) :
|
|
|
|
// Create secret filename
|
|
$f_secretFileName = "cs_se_" . md5($f_fileName);
|
|
|
|
// Set defined delimiter, may not be empty (!!!!)
|
|
if ($f_delimiter == "") : $f_delimiter = ";"; endif;
|
|
|
|
// Iterate complete array to substitute all existing fields having the delimiter (!!!!)
|
|
for ($i = 0; $i < $retArrayLen; $i++) :
|
|
$tmpArrKeys = array_keys($retArray[$i]);
|
|
$tmpArrKeysLen = count($tmpArrKeys);
|
|
for ($j = 0; $j < $tmpArrKeysLen; $j++) :
|
|
$retArray[$i][$tmpArrKeys[$j]] = str_replace ($f_delimiter, " ", $retArray[$i][$tmpArrKeys[$j]]);
|
|
endfor;
|
|
endfor;
|
|
|
|
// Get IDs of costcenters "EXTERN" of ALL headquarters (!!!!)
|
|
// $cscIdsExtern = getColVectorFromDB2ArrayByClause("parameter", "par_value", "par_key = 'CSC_ID_PAYER_EXTERN'", "hq_id", "", "");
|
|
|
|
// Mapping of zipcodes for special export function output fields
|
|
/*
|
|
$parSpecialExportZipcodeMappingArray = array();
|
|
$parSpecialExportZipcodeMapping = getParameterValue("0", "EXPORT_SPECIAL_FUNCTION_ZIPCODE_MAPPING", "0");
|
|
if ($parSpecialExportZipcodeMapping != "") :
|
|
// E.g. "HTE45067=04159|HTE45227=04159|HTE43478=04159"
|
|
$parSpecialExportZipcodeMappingArray = getKeyValueArrayFromString($parSpecialExportZipcodeMapping);
|
|
endif;
|
|
*/
|
|
|
|
// Write headline
|
|
$line = "Date;Partner Name;Driver;Street;Zipcode;City;Tax-ID;Order No;Time;Delivery;Courier Flat;ad on km;ad on km EUR;Late;Weekend;Waiting Time;Stopps;Other charges;Comment;Special Business;Total";
|
|
writeToFile($globalExportPath . $f_secretFileName, $line);
|
|
|
|
$exportFileLineArray = array();
|
|
for ($i = 0; $i < $retArrayLen; $i++) :
|
|
|
|
$displayRow = true;
|
|
|
|
// Get last station number (tr_sort) of the job
|
|
$jbId = $retArray[$i]["jb_id"];
|
|
$numOfStations = getCountOfTable("job", $jbId);
|
|
if (!is_numeric($numOfStations)) :
|
|
$displayRow = false;
|
|
endif;
|
|
|
|
// ....
|
|
|
|
if ($displayRow) :
|
|
|
|
// Get all commission numbers of the job
|
|
$jbCommissionNumbers = getColVectorFromDB2ArrayByClause("tour", "tr_commission_no", "jb_id = '" . $jbId . "'", "", "", "");
|
|
$jbCommissionNumbers = implode(",", $jbCommissionNumbers);
|
|
|
|
// Init calculator output
|
|
$jbCalc_00 = 0; $jbCalc_01 = 0; $jbCalc_02 = 0; $jbCalc_03 = 0; $jbCalc_04 = 0; $jbCalc_05 = 0;
|
|
$jbCalc_06 = 0; $jbCalc_07 = 0; $jbCalc_08 = 0; $jbCalc_09 = 0; $jbCalc_10 = 0;
|
|
$jbCalc_99 = 0;
|
|
$jbCalc_T01 = "";
|
|
|
|
// Check for existence of any entry in "jobcalculator"
|
|
if (existsEntry("jobcalculator",array("jb_id",$jbId))) :
|
|
|
|
// Calculator data of the current station
|
|
$jbCalc_01 = array_sum(getColVectorFromDB2ArrayByClause("jobcalculator", "jbc_totalprice", "jb_id = '" . $jbId . "' AND srvt_name = 'Mindestpreis'", "", "", ""));
|
|
if ($jbCalc_01 == "") : $jbCalc_01 = 0; endif;
|
|
$jbCalc_01 = number_format(round($jbCalc_01,2), 2, ",", ".");
|
|
$jbCalc_02 = array_sum(getColVectorFromDB2ArrayByClause("jobcalculator", "jbc_amount", "jb_id = '" . $jbId . "' AND srvt_name = 'Mehrkilometer'", "", "", ""));
|
|
if ($jbCalc_02 == "") : $jbCalc_02 = 0; endif;
|
|
$jbCalc_02 = number_format(round($jbCalc_02,2), 2, ",", ".");
|
|
$jbCalc_03 = array_sum(getColVectorFromDB2ArrayByClause("jobcalculator", "jbc_totalprice", "jb_id = '" . $jbId . "' AND srvt_name = 'Mehrkilometer'", "", "", ""));
|
|
if ($jbCalc_03 == "") : $jbCalc_03 = 0; endif;
|
|
$jbCalc_03 = number_format(round($jbCalc_03,2), 2, ",", ".");
|
|
$jbCalc_04 = array_sum(getColVectorFromDB2ArrayByClause("jobcalculator", "jbc_totalprice", "jb_id = '" . $jbId . "' AND srvt_name = 'Nachtzuschlag'", "", "", ""));
|
|
if ($jbCalc_04 == "") : $jbCalc_04 = 0; endif;
|
|
$jbCalc_04 = number_format(round($jbCalc_04,2), 2, ",", ".");
|
|
$jbCalc_05 = array_sum(getColVectorFromDB2ArrayByClause("jobcalculator", "jbc_totalprice", "jb_id = '" . $jbId . "' AND srvt_name = 'Wochenendzuschlag'", "", "", ""));
|
|
if ($jbCalc_05 == "") : $jbCalc_05 = 0; endif;
|
|
$jbCalc_05 = number_format(round($jbCalc_05,2), 2, ",", ".");
|
|
$jbCalc_06 = array_sum(getColVectorFromDB2ArrayByClause("jobcalculator", "jbc_totalprice", "jb_id = '" . $jbId . "' AND srvt_name = 'Wartezeitzuschlag'", "", "", ""));
|
|
if ($jbCalc_06 == "") : $jbCalc_06 = 0; endif;
|
|
$jbCalc_06 = number_format(round($jbCalc_06,2), 2, ",", ".");
|
|
$jbCalc_07 = array_sum(getColVectorFromDB2ArrayByClause("jobcalculator", "jbc_totalprice", "jb_id = '" . $jbId . "' AND srvt_name = 'Stopps'", "", "", ""));
|
|
if ($jbCalc_07 == "") : $jbCalc_07 = 0; endif;
|
|
$jbCalc_07 = number_format(round($jbCalc_07,2), 2, ",", ".");
|
|
|
|
$jbcWhereClause = "jb_id = '" . $jbId . "' AND jbc_totalprice > 0 AND srvt_name NOT IN ('Mindestpreis','Mehrkilometer','Nachtzuschlag','Wochenendzuschlag','Wartezeitzuschlag','Stopps')";
|
|
$jbCalc_99 = array_sum(getColVectorFromDB2ArrayByClause("jobcalculator", "jbc_totalprice", $jbcWhereClause, "", "", ""));
|
|
if ($jbCalc_99 == "") : $jbCalc_99 = 0; endif;
|
|
$jbCalc_99 = number_format(round($jbCalc_99,2), 2, ",", ".");
|
|
// $jbCalc_T01 = trim(implode(", ", getColVectorFromDB2ArrayByClause("jobcalculator", "srvt_name", $jbcWhereClause, "", "", "")));
|
|
$jbCalc_T01 = trim(implode(", ", getColVectorFromDB2ArrayByClause("jobcalculator", "CONCAT('(',jbc_amount,' x) ',srvt_name) AS srvt_name", $jbcWhereClause, "", "", "")));
|
|
/*
|
|
echo $jbId; echo ":<br>";
|
|
echo "Mindestpreis: " . $jbCalc_01; echo "<br>";
|
|
echo "Mehrkilometer (Qnt.): " . $jbCalc_02; echo "<br>";
|
|
echo "Mehrkilometer: " . $jbCalc_03; echo "<br>";
|
|
echo "Nachtzuschlag: " . $jbCalc_04; echo "<br>";
|
|
echo "Wochenendzuschlag: " . $jbCalc_05; echo "<br>";
|
|
echo "Wartezeitzuschlag: " . $jbCalc_06; echo "<br>";
|
|
echo "Stopps: " . $jbCalc_07; echo "<br>";
|
|
echo "Sonstiges: " . $jbCalc_99; echo "<br>";
|
|
echo "SUMME: " . $jbCalc_00; echo "<br>";
|
|
echo "Kommentare: " . $jbCalc_A; echo "<br><br>";
|
|
*/
|
|
else :
|
|
$jbCalc_T01 = "Fixpreis";
|
|
endif;
|
|
$jbCalc_00 = $retArray[$i]["jb_totalprice"];
|
|
if ($jbCalc_00 == "") : $jbCalc_00 = 0; endif;
|
|
$jbCalc_00 = number_format(round($jbCalc_00,2), 2, ",", ".");
|
|
|
|
$jbcDataArray = $retArray[$i]["jbc_data"];
|
|
$jbcDataArrayLen = count($jbcDataArray);
|
|
|
|
for ($j = 0; $j < $jbcDataArrayLen; $j++) :
|
|
$jbcVal = trim($jbcDataArray[$j][0]);
|
|
$jbcVal2 = trim($jbcDataArray[$j][5]);
|
|
|
|
if ($jbcVal == "TOUR" && $jbcVal2 == "KM") :
|
|
$tmpExtraKm = $jbcDataArray[$j][2];
|
|
$tmpExtraKmEur = $jbcDataArray[$j][1];
|
|
endif;
|
|
// ....
|
|
|
|
endfor;
|
|
|
|
$tmpTime = trim($retArray[$i]["jb_ordertime"]);
|
|
$jbOrderdate = substr($tmpTime,8,2) . "." . substr($tmpTime,5,2) . "." . substr($tmpTime,0,4);
|
|
$exportFileLineArray[$i][] = $jbOrderdate; // [A] ABGANGSDATUM
|
|
|
|
$tmpHqShort = $retArray[$i]["hq_mnemonic"];
|
|
if ($tmpHqShort == "HB") :
|
|
// $exportFileLineArray[$i][] = "8231904"; // [B] PARTNER NO
|
|
$exportFileLineArray[$i][] = "Stadtbote Bremen"; // [B] PARTNER NAME
|
|
elseif ($tmpHqShort == "HH") :
|
|
// $exportFileLineArray[$i][] = "8231907"; // [B] PARTNER NO
|
|
$exportFileLineArray[$i][] = "Stadtbote Hamburg"; // [B] PARTNER NAME
|
|
elseif ($tmpHqShort == "B") :
|
|
// $exportFileLineArray[$i][] = "8231903"; // [B] PARTNER NO
|
|
$exportFileLineArray[$i][] = "Stadtbote Berlin"; // [B] PARTNER NAME
|
|
elseif ($tmpHqShort == "H") :
|
|
// $exportFileLineArray[$i][] = "8231908"; // [B] PARTNER NO
|
|
$exportFileLineArray[$i][] = "Stadtbote Hannover"; // [B] PARTNER NAME
|
|
elseif ($tmpHqShort == "F") :
|
|
// $exportFileLineArray[$i][] = "8231911"; // [B] PARTNER NO
|
|
$exportFileLineArray[$i][] = "Stadtbote Schwalbach"; // [B] PARTNER NAME
|
|
elseif ($tmpHqShort == "DD") :
|
|
// $exportFileLineArray[$i][] = "8231905"; // [B] PARTNER NO
|
|
$exportFileLineArray[$i][] = "Stadtbote Dresden"; // [B] PARTNER NAME
|
|
elseif ($tmpHqShort == "E") :
|
|
// $exportFileLineArray[$i][] = "8231906"; // [B] PARTNER NO
|
|
$exportFileLineArray[$i][] = "Stadtbote Essen"; // [B] PARTNER NAME
|
|
elseif ($tmpHqShort == "L") :
|
|
// $exportFileLineArray[$i][] = "8231909"; // [B] PARTNER NO
|
|
$exportFileLineArray[$i][] = "Stadtbote Leipzig"; // [B] PARTNER NAME
|
|
elseif ($tmpHqShort == "S") :
|
|
// $exportFileLineArray[$i][] = "8231910"; // [B] PARTNER NO
|
|
$exportFileLineArray[$i][] = "Stadtbote Leonberg"; // [B] PARTNER NAME
|
|
elseif ($tmpHqShort == "M") :
|
|
// $exportFileLineArray[$i][] = "8231912"; // [B] PARTNER NO
|
|
$exportFileLineArray[$i][] = "Stadtbote München"; // [B] PARTNER NAME
|
|
endif;
|
|
|
|
$tmpUsrFirstname = trim($retArray[$i]["usr_firstname"]);
|
|
$tmpUsrName = trim($retArray[$i]["usr_name"]);
|
|
if ($tmpUsrName != "" && $tmpUsrFirstname != "") : $tmpUsrName .= ", "; endif;
|
|
$tmpUsrName .= $tmpUsrFirstname;
|
|
$exportFileLineArray[$i][] = trim($tmpUsrName); // [C] DRIVER
|
|
|
|
$tmpAdArr = array(trim($retArray[$i]["cr_ad_street"]), trim($retArray[$i]["cr_ad_zipcode"]), trim($retArray[$i]["cr_ad_city"]));
|
|
$tmpCrIdParent = $retArray[$i]["cr_id_parent"];
|
|
if ($tmpCrIdParent != "" && is_numeric($tmpCrIdParent) && $tmpCrIdParent > 0) :
|
|
$tmpCmpIdParent = getFieldValueFromId("courier","cr_id",$tmpCrIdParent,"cmp_id");
|
|
$tmpAdId = getFieldValueFromId("company","cmp_id",$tmpCmpIdParent,"ad_id");
|
|
$tmpAdArr = getFieldsValueFromId("address","ad_id",$tmpAdId,array("ad_street","ad_zipcode","ad_city"));
|
|
endif;
|
|
$exportFileLineArray[$i][] = $tmpAdArr[0]; // [D] STREET
|
|
$exportFileLineArray[$i][] = $tmpAdArr[1]; // [E] ZIPCODE
|
|
$exportFileLineArray[$i][] = $tmpAdArr[2]; // [F] CITY
|
|
|
|
$tmpTaxIdNo = trim($retArray[$i]["cr_cmp_stax_idno"]);
|
|
if ($tmpTaxIdNo == "") :
|
|
$tmpTaxIdNo = trim($retArray[$i]["cr_cmp_tax_idno"]);
|
|
endif;
|
|
$exportFileLineArray[$i][] = $tmpTaxIdNo; // [G] TAX-ID
|
|
|
|
$exportFileLineArray[$i][] = $jbCommissionNumbers; // [H] KOMMISSIONS-NR.
|
|
|
|
$tmpTime = trim($retArray[$i]["tr1_arrivaltime"]);
|
|
if ($tmpTime == "") :
|
|
$tmpTime = trim($retArray[$i]["tr1_finishtime"]);
|
|
endif;
|
|
// $jbTime = substr($tmpTime,8,2) . "." . substr($tmpTime,5,2) . "." . substr($tmpTime,0,4);
|
|
$jbTime = substr($tmpTime,11,2) . ":" . substr($tmpTime,14,2);
|
|
$exportFileLineArray[$i][] = $jbTime; // [I] ANKUNFTSZEIT
|
|
|
|
$tmpTime = trim($retArray[$i]["tr" . $numOfStations . "_finishtime"]);
|
|
if ($tmpTime == "") :
|
|
$tmpTime = trim($retArray[$i]["jb_finishtime"]);
|
|
endif;
|
|
$exportFileLineArray[$i][] = substr($tmpTime,11,5); // [J] AUSLIEFERZEIT
|
|
|
|
$exportFileLineArray[$i][] = $jbCalc_01; // [K] MINDESTPREIS
|
|
|
|
$exportFileLineArray[$i][] = $jbCalc_02; // [L] MEHRKILOMETER
|
|
|
|
$exportFileLineArray[$i][] = $jbCalc_03; // [M] MEHRKILOMETERPREIS
|
|
|
|
$exportFileLineArray[$i][] = $jbCalc_04; // [N] NACHTZUSCHLAG
|
|
|
|
$exportFileLineArray[$i][] = $jbCalc_05; // [O] WOCHENENDZUSCHLAG
|
|
|
|
$exportFileLineArray[$i][] = $jbCalc_06; // [P] WARTEZEITZUSCHLAG
|
|
|
|
$exportFileLineArray[$i][] = $jbCalc_07; // [Q] STOPPS
|
|
|
|
$exportFileLineArray[$i][] = $jbCalc_99; // [R] SONSTIGER ZUSCHLAG
|
|
|
|
$tmpJbFreetext1 = trim($retArray[$i]["jb_freetext_1"]);
|
|
$tmpJbFreetext1 = str_replace("'", "", $tmpJbFreetext1);
|
|
$tmpJbFreetext1 = str_replace("\"", '', $tmpJbFreetext1);
|
|
$tmpJbFreetext1 = str_replace(";", ',', $tmpJbFreetext1);
|
|
$tmpJbFreetext1 = str_replace("\n", ' ', $tmpJbFreetext1);
|
|
$tmpJbFreetext1 = str_replace("\r", ' ', $tmpJbFreetext1);
|
|
$exportFileLineArray[$i][] = $tmpJbFreetext1; // [S] BEMERKUNG
|
|
|
|
$exportFileLineArray[$i][] = $jbCalc_T01; // [T] SPEZIELLES GESCHÄFT
|
|
|
|
$exportFileLineArray[$i][] = $jbCalc_00; // [U] GESAMTSUMME
|
|
endif;
|
|
endfor;
|
|
$exportFileLineArrayLen = count($exportFileLineArray);
|
|
|
|
// Write export data
|
|
$exportFileLineArrayKeys = array_keys($exportFileLineArray);
|
|
$exportFileLineArrayKeysLen = count($exportFileLineArrayKeys);
|
|
if ($exportFileLineArrayKeysLen > 0) :
|
|
for ($i = 0; $i < $exportFileLineArrayKeysLen; $i++) :
|
|
if (is_array($exportFileLineArray[$exportFileLineArrayKeys[$i]])) :
|
|
$line = implode($f_delimiter, $exportFileLineArray[$exportFileLineArrayKeys[$i]]);
|
|
// echo $line . "<br>";
|
|
writeToFile($globalExportPath . $f_secretFileName, $line);
|
|
endif;
|
|
endfor;
|
|
|
|
// insert file-reference into the exportfiles
|
|
insertStmt("exportfiles", array("hq_id", $hq_id, "cs_id", $customerId, "expf_name", $f_fileName, "expf_cryptname",$f_secretFileName));
|
|
|
|
$statusMessage = "Die Daten wurden exportiert!";
|
|
endif;
|
|
endif;
|
|
endif;
|
|
|
|
return $retArray;
|
|
}
|
|
?>
|