255 lines
12 KiB
PHP
255 lines
12 KiB
PHP
<?php
|
|
|
|
error_reporting(E_ALL ^ E_NOTICE ^ E_DEPRECATED);
|
|
|
|
include_once ("../include/mcglobal.inc.php");
|
|
include_once ("../include/inc_job.inc.php");
|
|
include_once ("../include/inc_file.inc.php");
|
|
include_once ("../include/image.inc.php");
|
|
|
|
|
|
|
|
function special_export_MC ($objArr = array(), $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;
|
|
|
|
$objArrLen = count($objArr);
|
|
$doContinue = false;
|
|
if ($objArrLen > 0) :
|
|
$doContinue = true;
|
|
|
|
$crvhWhereClause = "";
|
|
$csWhereClause = "";
|
|
|
|
$objArrKeys = array_keys($objArr); // "crvh", "cs", ....
|
|
$objArrKeysLen = count($objArrKeys);
|
|
for ($i = 0; $i < $objArrKeysLen; $i++) :
|
|
$tmpCount = count($objArr[$objArrKeys[$i]]); // Number of objects of one type (e.g. $objArrKeys[0] = "crvh")
|
|
// CRVH
|
|
if ($objArrKeys[$i] == "crvh") :
|
|
$crvhWhereClause .= " (";
|
|
for ($j = 0; $j < $tmpCount; $j++) :
|
|
$tmpCrvh = $objArr[$objArrKeys[$i]][$j][0];
|
|
$tmpjbOrdertime = $objArr[$objArrKeys[$i]][$j][1];
|
|
$crvhWhereClause .= " (jb.cr_sid = '" . $tmpCrvh . "' AND jb.jb_ordertime >= '" . $tmpjbOrdertime . "') ";
|
|
if ($j < ($tmpCount - 1)) : $crvhWhereClause .= " OR "; endif;
|
|
endfor;
|
|
$crvhWhereClause .= ")";
|
|
endif;
|
|
// CS
|
|
if ($objArrKeys[$i] == "cs") :
|
|
// . ($isMetaCs == "1" ? " cs.cs_id_parent " : " cs.cs_id ") . " = '" . $customerId . "' AND"
|
|
$csWhereClause .= " (";
|
|
for ($j = 0; $j < $tmpCount; $j++) :
|
|
$tmpCrvh = $objArr[$objArrKeys[$i]][$j][0];
|
|
$tmpjbOrdertime = $objArr[$objArrKeys[$i]][$j][1];
|
|
$csWhereClause .= " (jb.cr_sid = '" . $tmpCrvh . "' AND jb.jb_ordertime >= '" . $tmpjbOrdertime . "') ";
|
|
if ($j < ($tmpCount - 1)) : $csWhereClause .= " OR "; endif;
|
|
endfor;
|
|
$csWhereClause .= ")";
|
|
endif;
|
|
endfor;
|
|
endif;
|
|
|
|
if ($doContinue) :
|
|
|
|
$whereClause = "";
|
|
if ($csWhereClause != "") : $whereClause .= $csWhereClause . " AND "; endif;
|
|
if ($crvhWhereClause != "") : $whereClause .= $crvhWhereClause . " AND "; endif;
|
|
|
|
// HARDCODED !!!!
|
|
$whereClause .= " (NOT ISNULL(jb.jb_export_time)) AND 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 ";
|
|
// echo "<br>" . $whereClause . "<br><br>";
|
|
|
|
// 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;
|
|
|
|
if ($isMetaCs == "0") :
|
|
$whereClause = " ((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;
|
|
*/
|
|
|
|
// $orderByClause = "jb.jb_id, tr.tr_sort";
|
|
$orderByClause = "jb.cr_sid, jb.jb_id, tr.tr_sort";
|
|
|
|
$sqlquery = "SELECT jb.jb_id, jb.jb_ordertime, jb.cr_sid, jb.hq_id, jb.jb_taketime, jb.jb_finishtime, jb.jb_totalprice,"
|
|
. " 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_status, tr.tr_finishtime, tr.tr_status,"
|
|
. " 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 "
|
|
. $whereClause
|
|
. " 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 " . $orderByClause;
|
|
// 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); die();
|
|
|
|
// 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 = "mc_" . md5($f_fileName);
|
|
$f_secretFileName = "mc_" . $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", "", "");
|
|
|
|
$remJbId = "";
|
|
|
|
$exportFileLineArray = array();
|
|
|
|
// HEADLINE
|
|
$exportFileLineArray[$i][] = "FAHRZEUG" . $f_delimiter . "AUFTRAG" . $f_delimiter . "PREIS" . $f_delimiter
|
|
. "AUFTRAGSZEIT" . $f_delimiter . "ANNAHMEZEIT" . $f_delimiter . "ERLEDIGUNGSZEIT" . $f_delimiter
|
|
. "STATIONS-NR." . $f_delimiter . "FIRMA" . $f_delimiter . "PERSON" . $f_delimiter
|
|
. "STATUS" . $f_delimiter . "ERLEDIGUNGSZEIT STATION" . $f_delimiter . "STRASSE UND HAUSNUMMER" . $f_delimiter
|
|
. "PLZ" . $f_delimiter . "ORT" . $f_delimiter . "LAND";
|
|
|
|
for ($i = 0; $i < $retArrayLen; $i++) :
|
|
|
|
$displayRow = true;
|
|
|
|
if ($displayRow) :
|
|
|
|
// JOB
|
|
$jbId = trim($retArray[$i]["jb_id"]);
|
|
if ($remJbId != $jbId) :
|
|
$remJbId = $jbId;
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["cr_sid"]); // FAHRZEUG
|
|
$exportFileLineArray[$i][] = $jbId; // AUFTRAG
|
|
$jbTotalPrice = number_format(round(trim($retArray[$i]["jb_totalprice"]),2), 2, ",", ".");
|
|
$exportFileLineArray[$i][] = $jbTotalPrice; // PREIS
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["jb_ordertime"]); // AUFTRAGSZEIT
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["jb_taketime"]); // ANNAHMEZEIT
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["jb_finishtime"]); // ERLEDIGUNGSZEIT
|
|
else :
|
|
$exportFileLineArray[$i][] = "";
|
|
$exportFileLineArray[$i][] = "";
|
|
$exportFileLineArray[$i][] = "";
|
|
$exportFileLineArray[$i][] = "";
|
|
$exportFileLineArray[$i][] = "";
|
|
$exportFileLineArray[$i][] = "";
|
|
endif;
|
|
|
|
// STATION
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_sort"]); // STATIONS-NR.
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_comp"]); // FIRMA
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_person"]); // PERSON
|
|
$trStatus = "Offen";
|
|
if (trim($retArray[$i]["tr_status"]) == "1") :
|
|
$trStatus = "Erledigt";
|
|
endif;
|
|
$exportFileLineArray[$i][] = $trStatus; // STATUS
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["tr_finishtime"]); // ERLEDIGUNGSZEIT STATION
|
|
$tmpVal = trim(trim($retArray[$i]["ad_street"]) . " " . trim($retArray[$i]["tr_hsno"]));
|
|
$exportFileLineArray[$i][] = $tmpVal; // STRASSE UND HAUSNUMMER
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["ad_zipcode"]); // PLZ
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["ad_city"]); // ORT
|
|
$exportFileLineArray[$i][] = trim($retArray[$i]["ad_country"]); // LAND
|
|
|
|
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 "[" . $i . ".] " . $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;
|
|
endif; // $doContinue
|
|
|
|
return $retArray;
|
|
}
|
|
|
|
|
|
/*
|
|
L1012 ab März 2020
|
|
L1009 ab Sept. 2020
|
|
L1006 ab Nov. 2020
|
|
L1004 ab Dez. 2020
|
|
L1002 ab März 2021
|
|
*/
|
|
|
|
$objArr = array();
|
|
$objArr["crvh"] = array();
|
|
$objArr["crvh"][0] = array('L1012','2020-03-01 00:00:00');
|
|
$objArr["crvh"][1] = array('L1009','2020-09-01 00:00:00');
|
|
$objArr["crvh"][2] = array('L1006','2020-11-01 00:00:00');
|
|
$objArr["crvh"][3] = array('L1004','2020-12-01 00:00:00');
|
|
$objArr["crvh"][4] = array('L1002','2021-03-01 00:00:00');
|
|
|
|
$globalExportPath = "../temp/download/";
|
|
$f_fileName = getDateTime("6") . ".csv";
|
|
$f_delimiter = ";";
|
|
|
|
$resArr = special_export_MC($objArr);
|
|
// print_r($resArr);
|
|
|
|
?>
|