Assecutor/votianng
3
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
votianng/html/tools/statistic_finishment.php
Sven Carstensen a1129565af 1. Import
2026-03-29 10:34:57 +02:00

1215 lines
75 KiB
PHP
Raw Permalink Blame History

This file contains invisible Unicode characters
This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
<?php
/*=======================================================================
*
* statistic_finishment.php
*
* Autor: Carsten Annacker
*
=======================================================================*/
//set_time_limit(0);$hq_id = 3; $usr_id = 300002;
include_once("../include/auth.inc.php");
// STB979291 Capital Baustoffe GmbH Bereich X
// STB902891 BÄR & OLLENROTH KG Bereich 1.00 ja X
// STB979905 STARK Deutschland GmbH Raab Karcher - Ausbau Bereich 2.00 ja X
// STB919283 Sto SE & Co. KGaA PZM X
//ssh root@172.16.0.158
//cd /data/0/votian-test/html/temp/download/
//URL="http://172.18.0.4/tools/statistic_finishment.php?cs_eid=STB979291&from=11.12.2024&to=11.12.2025&what=19"; wget "$URL"
//URL="http://172.18.0.4/tools/statistic_finishment.php?cs_eid=STB902891&from=11.12.2024&to=11.12.2025&what=19"; wget "$URL"
//URL="http://172.18.0.4/tools/statistic_finishment.php?cs_eid=STB979905&from=11.12.2024&to=11.12.2025&what=19"; wget "$URL"
//URL="http://172.18.0.4/tools/statistic_finishment.php?cs_eid=STB919283&from=30.11.2024&to=30.11.2025&what=19"; wget "$URL"
//URL="http://172.18.0.4/tools/statistic_finishment.php?cs_eid=STB979291&from=11.12.2024&to=11.12.2025&what=19"; wget "$URL"; URL="http://172.18.0.4/tools/statistic_finishment.php?cs_eid=STB902891&from=11.12.2024&to=11.12.2025&what=19"; wget "$URL";URL="http://172.18.0.4/tools/statistic_finishment.php?cs_eid=STB979905&from=11.12.2024&to=11.12.2025&what=19"; wget "$URL"; URL="http://172.18.0.4/tools/statistic_finishment.php?cs_eid=STB919283&from=30.11.2024&to=30.11.2025&what=19"; wget "$URL"
include_once("../include/global.inc.php");
$log_file_name = "../log/statistic_finishment_" . date("Ym") . ".log";
//myWriteLog(var_export($HTTP_POST_VARS, true));
$db_conn = getDbConnectionSpecial ("172.16.0.111:3711", $dbname, $dblogin, $dbpassword);
$dl = isset($_GET["dl"]) ? trim($_GET["dl"]) : 0;
$f_cs_eid = isset($_GET["cs_eid"]) ? trim($_GET["cs_eid"]) : (isset($_POST["f_cs_eid"]) ? trim($_POST["f_cs_eid"]) : "");
$max_date = date("Y-m-d", mktime(0, 0, 0, date("m"), 0, date("Y")));
$min_date = date("Y-m-d", mktime(0, 0, 0, date("m") - 1, 1, date("Y")));
$f_min_date = isset($_GET["from"]) ? $_GET["from"] : (isset($_POST["f_min_date"]) ? $_POST["f_min_date"] : "");
$f_max_date = isset($_GET["to"]) ? $_GET["to"] : (isset($_POST["f_max_date"]) ? $_POST["f_max_date"] : "");
$f_what = isset($_GET["what"]) ? $_GET["what"] : (isset($_POST["f_what"][0]) ? $_POST["f_what"][0] : "1");
$error_text = isset($_POST["error_text"]) ? $_POST["error_text"] : "";
$f_formsubmit = isset($_GET["formsubmit"]) ? $_GET["formsubmit"] : (isset($_POST["formsubmit"]) ? $_POST["formsubmit"] : "");
$f_fototype = isset($_GET["f_fototype"]) ? $_GET["f_fototype"] : (isset($_POST["f_fototype"][0]) ? $_POST["f_fototype"][0] : "");
$f_fotocount = isset($_GET["f_fotocount"]) ? $_GET["f_fotocount"] : (isset($_POST["f_fotocount"][0]) ? $_POST["f_fotocount"][0] : "");
if (trim($f_min_date) == ""):
$f_min_date = $min_date;
else:
$f_min_date = sqlDate($f_min_date);
endif;
if (trim($f_max_date) == ""):
$f_max_date = $max_date;
else:
$f_max_date = sqlDate($f_max_date);
endif;
//echo date('Y-m-d', strtotime($f_min_date)) . "<br>\n";
//echo $f_min_date . "<br>\n";
//echo date('Y-m-d', strtotime($f_max_date)) . "<br>\n";
//echo $f_max_date . "<br>\n";
$inv_print_srvprice = $db->getone("SELECT par_value FROM parameter WHERE par_key = 'INV_PRINT_SRVPRICE' AND hq_id = " . $hq_id);
$sql_query = "SELECT usr_account, usr_firstname, usr_name FROM user WHERE usr_id = " . $usr_id;
$res = $db->query($sql_query);
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sql_query'" . $res->getMessage());
$row = ($phpVersion >= "8.0" ? $res->fetch_assoc() : $res->fetchRow());
myWriteLog($row["usr_firstname"] . " " . $row["usr_name"] . " (" . $row["usr_account"] . ")");
$res->free();
if ($usr_id != 300002 && !authCheckEmployeeRights($emp_id, "46")) {
myWriteLog("Keine Berechtigung");
echo "Keine Berechtigung";
exit();
}
$content = "";
if ($f_what == 1 || $f_what == 2 || $f_what == 5 || $f_what == 6 || $f_what == 7 || $f_what == 8 || $f_what == 15 || $f_what == 16 || $f_what == 19) {
$error_text = "";
if (!(date('Y-m-d', strtotime($f_min_date)) == $f_min_date && date('Y-m-d', strtotime($f_max_date)) == $f_max_date))
$error_text = "<b>Ung&uuml;ltige Datumsangabe (Format <i>TT.MM.JJJJ</i>)</b>";
if ($f_cs_eid == "" && $f_what != 16)
$error_text = "<b>Kundennummer fehlt</b>";
myWriteLog("\$f_cs_eid = $f_cs_eid, \$f_min_date = $f_min_date, \$f_max_date = $f_max_date, \$error_text = $error_text, \$f_what = $f_what, \$dl = $dl");
if ($error_text == "") {
$csv_name = ($f_what == 1 ? "Erledigungsstatistik" : ($f_what == 2 ? "Servicezeiten" : ($f_what == 5 ? "Ziel-PLZs" : ($f_what == 6 ? "Ziel-PLZs_und_-Orte" : ($f_what == 7 ? "Preise" : ($f_what == 8 ? "getnow" : ($f_what == 15 ? "Konservennutzung" : ($f_what == 16 ? "Auftragsherkunft" : ($f_what == 19 ? "PZM-Preisvergleich" : ""))))))))) . "_" . ($f_cs_eid != "" ? $f_cs_eid . "_" : "") . formDate($f_min_date) . "-" . formDate($f_max_date) . ".csv";
$csv_txt = csv_statistic($f_cs_eid, $f_min_date, $f_max_date, $f_what);
// if ($dl == 1):
// mk_csv_download($csv_name, $csv_txt);
// exit();
// endif;
$fileHandle = @fopen("../temp/download/" . $csv_name, 'w');
fwrite($fileHandle, str_replace("<br>", "", str_replace("#aa", "", str_replace("#ab", "", str_replace("#ba", "", str_replace("#bb", "", $csv_txt))))));
@fclose($fileHandle);
// $content = "<a href=\"statistic_finishment.php?dl=1&cs_eid=" . $f_cs_eid . "&from=" . formDate($f_min_date) . "&to=" . formDate($f_max_date) . "&what=" . $f_what . "\"><br>" . $csv_name . "</a><br><br>\n";
$content = "<a href=\"" . "../temp/download/" . $csv_name . "\"><br>" . $csv_name . "</a><br><br>\n";
if ($f_what == 15 || $f_what == 16 || $f_what == 19)
$content .= csv_output($csv_txt, "left");
else
$content .= csv_output($csv_txt);
} else {
$error_text = "<br>" . $error_text;
if ($hq_id != "3" && $hq_id != "2")
$error_text = "";
}
}
if ($f_what == 3 || $f_what == 4 || $f_what == 9 || $f_what == 10 || $f_what == 11 || $f_what == 12 || $f_what == 13 || $f_what == 14 || $f_what == 17 || $f_what == 18) {
if (($f_what == 10 || $f_what == 12) && $f_formsubmit == "Hinzufügen") {
$f_cs_eid = trim($f_cs_eid);
if ($f_cs_eid != "") {
$sql_query = "SELECT cs_id FROM customer WHERE cs_eid = '" . $f_cs_eid . "'";
$res = $db->query($sql_query);
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sql_query'" . $res->getMessage());
if ($res->numRows() > 0) {
$row = ($phpVersion >= "8.0" ? $res->fetch_assoc() : $res->fetchRow());
$sql_query = "SELECT par_value FROM parameter WHERE par_key = 'INV_PRINT_SRVPRICE_" . $row["cs_id"] . "'";
if ($f_what == 10)
$sql_query = "SELECT par_value FROM parameter WHERE par_key = 'MASK_TR_PHOTO_CS_" . $row["cs_id"] . "'";
$res1 = $db->query($sql_query);
if (DB::isError($res1)) reportDie ("$PHP_SELF: '$sql_query'" . $res->getMessage());
if ($res1->numRows() == 0) {
$sql_query = "INSERT INTO parameter (par_key, md_id, hq_id, emp_id, par_value, par_text) VALUES ('INV_PRINT_SRVPRICE_" . $row["cs_id"] . "', 0, 0, 0, '1', '')";
if ($f_what == 10)
$sql_query = "INSERT INTO parameter (par_key, md_id, hq_id, emp_id, par_value, par_text) VALUES ('MASK_TR_PHOTO_CS_" . $row["cs_id"] . "', 0, 0, 0, '" . $f_fototype . "', '')";
$res2 = $db->query($sql_query);
if (DB::isError($res2)) reportDie ("$PHP_SELF: '$sql_query'" . $res2->getMessage());
if ($f_what == 10) {
$sql_query = "INSERT INTO parameter (par_key, md_id, hq_id, emp_id, par_value, par_text) VALUES ('MASK_MIN_MAX_TR_PHOTO_CS_" . $row["cs_id"] . "', 0, 0, 0, '" . ($f_fotocount == 1 ? "0|1" : "1|1") . "', '')";
$res2 = $db->query($sql_query);
if (DB::isError($res2)) reportDie ("$PHP_SELF: '$sql_query'" . $res2->getMessage());
}
$error_text = "<b>Ein neuer Eintrag wurde gespeichert für die Kundennummer " . $f_cs_eid . "</b>";
} else {
$error_text = "<b>Kundennummer " . $f_cs_eid . " hat bereits einen Eintrag</b>";
if ($f_what == 10)
$error_text = "<b>Kundennummer " . $f_cs_eid . " hat bereits einen Eintrag, bitte vorher löschen wenn neuer Eintrag gewünscht</b>";
}
$res1->free();
} else {
$error_text = "<b>Kundennummer " . $f_cs_eid . " ist unbekannt</b>";
}
$res->free();
} else {
$error_text = "<b>Kundennummer fehlt</b>";
}
$f_cs_eid = "";
} elseif (($f_what == 10 || $f_what == 12) && $error_text == "loeschen") {
$sql_query = "SELECT cs_id FROM customer WHERE cs_eid = '" . $f_cs_eid . "'";
$res = $db->query($sql_query);
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sql_query'" . $res->getMessage());
$row = ($phpVersion >= "8.0" ? $res->fetch_assoc() : $res->fetchRow());
$sql_query = "DELETE FROM parameter WHERE par_key = 'INV_PRINT_SRVPRICE_" . $row["cs_id"] . "' ";
if ($f_what == 10)
$sql_query = "DELETE FROM parameter WHERE par_key = 'MASK_TR_PHOTO_CS_" . $row["cs_id"] . "'";
$res1 = $db->query($sql_query);
if (DB::isError($res1)) reportDie ("$PHP_SELF: '$sql_query'" . $res1->getMessage());
if ($f_what == 10) {
$sql_query = "DELETE FROM parameter WHERE par_key = 'MASK_MIN_MAX_TR_PHOTO_CS_" . $row["cs_id"] . "'";
$res1 = $db->query($sql_query);
if (DB::isError($res1)) reportDie ("$PHP_SELF: '$sql_query'" . $res1->getMessage());
}
$res->free();
$error_text = "<b>Der Eintrag für die Kundennummer " . $f_cs_eid . " wurde gelöscht.</b>";
$f_cs_eid = "";
}
if (trim($error_text) != "")
myWriteLog("\$error_text = $error_text");
if ($error_text == "" && $f_what != 9 && $f_what != 10 && $f_what != 11 && $f_what != 12 && $f_what != 13 && $f_what != 14 && $f_what != 17 && $f_what != 18)
$error_text = "<b>ACHTUNG: Das Setzen oder Löschen eines Häkchens wird sofort gespeichert.</b>";
$error_text .= "<br><br>";
$cs_ids = array();
$cs_ids_sql = "";
if ($f_what == 4) {
$emp_ids = array();
$jb_tourcities = array();
}
$sql_query = "";
if ($f_what == 3)
$sql_query =
"SELECT par_key, par_text FROM parameter WHERE (par_key IN ('RANKING_JB2CRVH_MEASURE_CS_IDS', 'JB_DISPOINFO_ENABLED_CS_IDS') AND hq_id = " . $hq_id . ") OR par_key LIKE 'MASK_CUST_SET_PAYER_%' OR par_key LIKE 'CUSTOMER_MANUAL_DISPO_%'";
if ($f_what == 4)
$sql_query =
"SELECT emp_id, par_key, par_value FROM parameter WHERE par_key IN ('CUSTOMER_MASK_JOBLIST_ADD_ON_FIELDS') OR par_key LIKE 'MASK_INSERTADDRESS_DISTRICT_%' OR par_key LIKE 'MASK_JB_MAP_VIEW_ENABLED_%'";
if ($f_what == 9)
$sql_query = "SELECT cr_id, cscr_relation, cs_eid FROM customer, customercourier WHERE customer.cs_id = customercourier.cs_id AND customer.hq_id = " . $hq_id . " ORDER BY cr_id, cscr_relation, cs_eid";
if ($f_what == 10)
$sql_query =
"SELECT par_key, par_value FROM parameter WHERE par_key LIKE 'MASK_TR_PHOTO_CS_%' OR par_key LIKE 'MASK_MIN_MAX_TR_PHOTO_CS_%'";
if ($f_what == 13)
$sql_query =
"SELECT par_key FROM parameter WHERE par_key LIKE 'MAIL%' AND (par_value LIKE '%@%' OR par_value = '') AND hq_id in (0, " . $hq_id . ") GROUP BY par_key HAVING COUNT(*) > 1";
if ($f_what == 14) { // honk
$cr_sids = array();
$sql_query = "SELECT cs_eid, cr_sid, cscr_prov FROM customer, customercourier, courier WHERE cscr_prov > 0 AND customer.cs_id = customercourier.cs_id AND customercourier.cr_id = courier.cr_id AND customer.hq_id = " . $hq_id . " ORDER BY cs_eid, cr_sid";
myWriteLog($sql_query);
$res = $db2->query($sql_query);
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sql_query'" . $res->getMessage());
while ($row = ($phpVersion >= "8.0" ? $res->fetch_assoc() : $res->fetchRow())) {
if (isset($cr_sids[$row["cs_eid"]]))
$cr_sids[$row["cs_eid"]] .= ", ";
else
$cr_sids[$row["cs_eid"]] = "";
$cr_sids[$row["cs_eid"]] .= $row["cr_sid"] . " " . $row["cscr_prov"] . "%";
}
$res->free();
$sql_query =
"SELECT DISTINCT cs_eid, '0' AS cs_invmode FROM customerservice, service, customer " . // Calculator
"WHERE customerservice.cs_id > 0 AND customerservice.srv_id = service.srv_id AND customerservice.cs_id = customer.cs_id AND customer.hq_id = " . $hq_id . " " .
"UNION " .
"SELECT DISTINCT cs_eid, '0' AS cs_invmode FROM service, customer " . // Calculator
"WHERE service.cs_id > 0 AND service.cs_id = customer.cs_id AND customer.hq_id = " . $hq_id . " " .
"UNION " .
"SELECT DISTINCT cs_eid, '1' AS cs_invmode FROM servicecustomer, service, servicetype, customer " . // Services
"WHERE servicecustomer.srv_id = service.srv_id AND servicecustomer.srvt_id = servicetype.srvt_id AND servicecustomer.hq_id = " . $hq_id . " " .
"AND servicecustomer.cs_id > 0 AND srv_mode = 0 AND (srvc_price > 0 OR srvc_price > 0) AND customer.cs_id = servicecustomer.cs_id " .
"UNION " .
"SELECT DISTINCT cs_eid, '2' AS cs_invmode FROM serviceplzcustomer AS srvpc, customer AS cs " .
"WHERE srvpc_price > 0 AND srvpc.hq_id = " . $hq_id . " AND srvpc.cs_id = cs.cs_id AND cs.cs_invmode = 1 AND cs.hq_id = " . $hq_id . " " . // PLZ
"UNION " .
"SELECT DISTINCT cs_eid, '3' AS cs_invmode FROM serviceplzareacustomer AS srvpac, customer AS cs " .
"WHERE srvpac_price > 0 AND srvpac.hq_id = " . $hq_id . " AND srvpac.cs_id = cs.cs_id AND cs.cs_invmode = 2 " . // Bereich
"UNION " .
"SELECT DISTINCT cs_eid, '4' AS cs_invmode FROM customer, customercourier " . // customercourier
"WHERE customer.cs_id = customercourier.cs_id AND customer.hq_id = " . $hq_id . " AND cscr_prov > 0 " .
"ORDER BY cs_eid";
}
//echo $sql_query;
if ($sql_query != "") {
myWriteLog($sql_query);
$res = $db2->query($sql_query);
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sql_query'" . $res->getMessage());
while ($row = ($phpVersion >= "8.0" ? $res->fetch_assoc() : $res->fetchRow())) {
if ($f_what == 3) {
if (substr($row['par_key'], 0, 20) == 'MASK_CUST_SET_PAYER_') {
$cs_ids['MASK_CUST_SET_PAYER'][] = substr($row['par_key'], 20);
} elseif (substr($row['par_key'], 0, 22) == 'CUSTOMER_MANUAL_DISPO_') {
$cs_ids['CUSTOMER_MANUAL_DISPO'][] = substr($row['par_key'], 22);
} else {
$cs_ids[$row['par_key']] = explode(",", $row['par_text']);
}
}
if ($f_what == 4) {
if (substr($row['par_key'], 0, 25) == 'MASK_JB_MAP_VIEW_ENABLED_') {
$cs_ids['MASK_JB_MAP_VIEW_ENABLED'][] = substr($row['par_key'], 25);
}
if (substr($row['par_key'], 0, 28) == 'MASK_INSERTADDRESS_DISTRICT_') {
$cs_ids['MASK_INSERTADDRESS_DISTRICT'][] = substr($row['par_key'], 28);
$jb_tourcities[substr($row['par_key'], 28)] = $row['par_value'];
}
if ($row['par_key'] == 'CUSTOMER_MASK_JOBLIST_ADD_ON_FIELDS' && $row['par_value'] == 'jb_tourcities')
$emp_ids[] = $row['emp_id'];
}
if ($f_what == 9) {
if (isset($cs_ids[$row["cr_id"]][$row["cscr_relation"]]))
$cs_ids[$row["cr_id"]][$row["cscr_relation"]] .= ", ";
else
$cs_ids[$row["cr_id"]][$row["cscr_relation"]] = "";
$cs_ids[$row["cr_id"]][$row["cscr_relation"]] .= $row["cs_eid"];
}
if ($f_what == 10) {
if (substr($row['par_key'], 0, 17) == 'MASK_TR_PHOTO_CS_' AND $row['par_value'] == "1") {
$cs_ids['MASK_TR_PHOTO_CS_1'][] = substr($row['par_key'], 17);
} elseif (substr($row['par_key'], 0, 17) == 'MASK_TR_PHOTO_CS_' AND $row['par_value'] == "2") {
$cs_ids['MASK_TR_PHOTO_CS_2'][] = substr($row['par_key'], 17);
} elseif (substr($row['par_key'], 0, 25) == 'MASK_MIN_MAX_TR_PHOTO_CS_') {
$cs_ids['MASK_MIN_MAX_TR_PHOTO_CS'][substr($row['par_key'], 25)] = $row['par_value'];
}
}
if ($f_what == 13) {
$cs_ids_sql .= ($cs_ids_sql != "" ? "," : "") . "'" . $row['par_key'] . "'";
}
if ($f_what == 14) { // honk
$cs_ids[$row['cs_invmode']][] = $row['cs_eid'];
$cs_ids_sql .= ($cs_ids_sql != "" ? "," : "") . "'" . $row['cs_eid'] . "'";
}
}
$res->free();
}
//print_r($cs_ids['MASK_MIN_MAX_TR_PHOTO_CS']);
//print_r($cs_ids); //print_r($jb_tourcities);print_r($emp_ids);
// $unique_cs_ids = implode (",", array_unique(array_merge($cs_ids['RANKING_JB2CRVH_MEASURE_CS_IDS'], $cs_ids['JB_DISPOINFO_ENABLED_CS_IDS'])));
$sql_query =
"SELECT cs_eid, cmp_comp, cmp_comp2, cs_id FROM company, customer " .
"WHERE company.cmp_id = customer.cmp_id AND cs_jbedit = 1 AND hq_id = " . $hq_id . " ORDER BY cs_eid";
// "cs_id IN (" . $unique_cs_ids . ") ORDER BY cs_eid";
$ret_val = "Lfd_Nr.;Kundennr.;Name;Name2;Lademaße;Dispo-Info;Barzahler;Manuelle Dispo<br>\n";
$special_columns = 1;
if ($f_what == 4) {
$sql_query =
"SELECT cs_eid, cmp_comp, cmp_comp2, csc_name, usr_account, emp_id, customer.cs_id " .
"FROM company, customer, costcenter, employee, user " .
"WHERE company.cmp_id = customer.cmp_id AND customer.hq_id = " . $hq_id . " AND costcenter.cs_id = customer.cs_id AND costcenter.csc_id = employee.csc_id AND user.usr_id = employee.usr_id AND user.usr_account != '' " .
"ORDER BY cs_eid, csc_name, emp_id";
$ret_val = "Lfd_Nr.;Kundennr.;Name;Name2;Kostenstelle;Login;Kartenansicht;Orte;Stadtteile<br>\n";
$special_columns = 2;
}
if ($f_what == 9) {
$sql_query =
"SELECT crvh_sid, cr_eid, cmp_comp, cmp_comp2, usr_name, usr_firstname, ad_street, CONCAT(CHAR(160), cmp_hsno), CONCAT(CHAR(160), ad_zipcode), ad_city, usr_email, CONCAT(CHAR(160), cr_imei), CONCAT(CHAR(160), cr_mobile_pda), usr_inv_email, usr.usr_id, cr.cr_id " .
"FROM company AS cmp, courier AS cr, user AS usr, couriervehicle AS crvh, address AS ad " .
"WHERE cmp.cmp_id = cr.cmp_id AND cr.usr_id = usr.usr_id AND cr.cr_id = crvh.cr_id AND crvh_sid != '' AND cr.hq_id = " . $hq_id . " AND cmp.ad_id = ad.ad_id " .
"ORDER BY crvh_sid";
$ret_val = "Lfd_Nr.;TT-Nr.;ExtID;Firma;Zusatz;Name;Vorname;Straße;Nummer;PLZ;Ort;E-mail;IMEI-Nr.;Mobil-Nr. VPA/PDA;E-Mail-Adr. Rechnungen;APP-,_BS-Vers.,_Modell;gesperrt;bevorzugt<br>\n";
$special_columns = 2;
}
if ($f_what == 10) {
$sql_query =
"SELECT cs_eid, cmp_comp, cmp_comp2, cs_id FROM company, customer " .
"WHERE company.cmp_id = customer.cmp_id AND hq_id = " . $hq_id . " " .
"AND (cs_id IN (" . implode(",", array_unique(array_merge($cs_ids['MASK_TR_PHOTO_CS_1']))) . ") OR cs_id IN (" . implode(",", array_unique(array_merge($cs_ids['MASK_TR_PHOTO_CS_2']))) . ")) ORDER BY cs_eid";
$ret_val = "Lfd_Nr.;Kundennr.;Name;Name2;Beweisfoto Zentralenerfassung;Beweisfoto Kundenerfassung;Fotos mindestens;Fotos höchstens<br>\n";
$special_columns = 1;
}
if ($f_what == 11) {
$sql_query =
"SELECT cs_eid, cmp_comp, cmp_comp2, cmp_stax_idno, (CASE cmp_visible WHEN 1 THEN 'nein' ELSE 'ja' END) AS ausgeblendet, cscad_comp, cscad_comp2, cscad_comp3, cscad_comp4, ad_country, ad_zipcode, ad_city, ad_street, cscad_hsno " .
"FROM company AS cmp, customer AS cs, costcenter AS csc, costcenteraddress AS cscad, address AS ad " .
"WHERE cmp.cmp_id = cs.cmp_id AND cs.hq_id = " . $hq_id . " AND cs.csc_id = csc.csc_id AND cscad.csc_id = csc.csc_id AND cscad.ad_id = ad.ad_id AND cscad.adt_id = 2 AND ad.ad_country != 'DE' " .
"ORDER BY cs_eid";
$ret_val = "Lfd_Nr.;Kundennr.;Name;Name2;USt-IdNr.;Ausgeblendet;Rechnungsanschrift;;;;Land;PLZ;Ort;Straße;Nummer<br>\n";
$special_columns = 0;
}
if ($f_what == 12) {
$sql_query =
"SELECT cs_eid, cmp_comp, cmp_comp2, (CASE cmp_visible WHEN 1 THEN 'nein' ELSE 'ja' END) AS ausgeblendet, cs_id " .
"FROM parameter, company AS cmp, customer AS cs " .
"WHERE par_key LIKE 'INV_PRINT_SRVPRICE_%' AND RIGHT(par_key, 6) = cs.cs_id AND cs.hq_id = " . $hq_id . " AND cmp.cmp_id = cs.cmp_id " .
"ORDER BY cs_eid";
// $res = $db->query($sql_query);
$ret_val = "Lfd_Nr.;Kundennr.;Name;Name2;Ausgeblendet<br>\n";
$special_columns = 1;
}
if ($f_what == 13) {
$sql_query =
"SELECT par_text, (CASE hq_id WHEN 0 THEN 'alle' WHEN 1 THEN 'HB' WHEN 2 THEN 'HH' WHEN 3 THEN 'B' WHEN 4 THEN 'H' WHEN 5 THEN 'F' WHEN 6 THEN 'DD' WHEN 7 THEN 'E' WHEN 8 THEN 'L' WHEN 9 THEN 'M' WHEN 11 THEN 'S' ELSE 'unbekannt' END) AS NL, par_value FROM parameter
WHERE (par_value LIKE '%@%' OR par_value = '') AND par_key LIKE 'MAIL_%' AND hq_id IN (0," . $hq_id . ") AND NOT (hq_id = 0 AND par_key IN (" . $cs_ids_sql . "))
AND par_key != 'MAIL_BCC_STATION_ADDRESS' AND par_key != 'MAIL_TEXT_RECENSION' AND par_value != 'mail-cron@assecutor.de'
ORDER BY hq_id DESC, par_text";
$ret_val = "Lfd_Nr.;Mail;NL;Empfänger<br>\n";
$special_columns = 0;
}
if ($f_what == 14) {
if ($cs_ids_sql != "")
$cs_ids_sql = " OR cs_eid IN (" . $cs_ids_sql . ")";
$sql_query =
"SELECT cs_eid, cmp_comp, cmp_comp2, " .
"(CASE cmp_authenticated WHEN 1 THEN 'aktiv' ELSE 'gesperrt' END) AS authenticated, " .
"(CASE cs_invmode WHEN 0 THEN 'PZM' WHEN 1 THEN 'PLZ' WHEN 2 THEN 'Bereich' ELSE 'Sonstiges' END) AS cs_invmode_real, " .
"(CASE cs_markup WHEN 1 THEN '' ELSE 'nein' END) AS TSZ, " .
"IF(cs_markup_prov > 0, cs_markup_prov, '') AS TSZ_Satz, " .
"IF(cs_discount > 0, cs_discount, '') AS Rabattsatz, " .
"(CASE cs_fixprice_discount WHEN 1 THEN 'ja' ELSE '' END) AS Rabatt_auf_Festpreis " .
"FROM company AS cmp, customer AS cs " .
"WHERE cmp.cmp_id = cs.cmp_id AND cs.hq_id = " . $hq_id . " AND cs_eid != '' AND cmp_visible = 1 AND cmp_archived = 0 " .
"AND (cs_markup = 0 OR (cs_markup = 1 && cs_markup_prov > 0) OR cs_discount > 0 OR cs_fixprice_discount > 0" .
$cs_ids_sql . ")" .
"ORDER BY cs_eid";
$ret_val = "Lfd_Nr.;Kundennr.;Name;Name2;Freigabe;Abrechnungs_Typ;TSZ;TSZ_%;Rabatt_%;Rabatt_auf_Festpreis;Services;Calc;PLZ;Bezirke;Kurierprovision<br>\n"; // honk
$special_columns = 0;
}
if ($f_what == 17) {
$sql_query =
"SELECT cs_eid, cmp_comp, cmp_comp2, COUNT(*), cs_id FROM (" .
"SELECT cs_eid, cmp_comp, cmp_comp2, customer.cs_id, jb_id " .
"FROM job, costcenter, customer, company " .
"WHERE job.csc_id_payer = costcenter.csc_id AND costcenter.cs_id = customer.cs_id AND customer.hq_id = " . $hq_id . " AND customer.cmp_id = company.cmp_id AND LENGTH(jb_tourname) > 0 " .
"UNION SELECT cs_eid, cmp_comp, cmp_comp2, customer.cs_id, jb_id " .
"FROM job, costcenter, customer, company " .
"WHERE job.csc_id_payer_cash = costcenter.csc_id AND costcenter.cs_id = customer.cs_id AND customer.hq_id = " . $hq_id . " AND customer.cmp_id = company.cmp_id AND LENGTH(jb_tourname) > 0 AND csc_id_payer_cash != 0" .
") AS honk " .
"GROUP BY cs_eid, cmp_comp, cmp_comp2";
$ret_val = "Lfd_Nr.;Kundennr.;Name;Name2;Anzahl Konserven<br>\n"; // honk
$special_columns = 2;
}
if ($f_what == 18) {
$sql_query = "http://" . $dbhostPZM . "/api/zones/" . $hq_id;
$json_response = file_get_contents($sql_query);
$responseArr = json_decode($json_response, true);
// $ret_val = "PZM-Modus: " . (getParameterValue("0", "PZM_ROUNDTRIPKM", $hq_id) == "1" ? "Rundtour (Berechnung der Hin- und Rückfahrt)" : "Last-KM (Berechnung der kürzesten Anfahrt)") . "<br>\n" ."<br>\n";
$ret_val =
"<b>PZM-Modus</b>: " .
'<select id="select_PZM_ROUNDTRIPKM" onchange=' .
"\"set_attribute('', '" . $hq_id . "', 'PZM-Modus', document.getElementById('select_PZM_ROUNDTRIPKM').value)\">" .
'<option' . (getParameterValue("0", "PZM_ROUNDTRIPKM", $hq_id) == "0" ? ' selected' : '') . '>Last-KM</option>' .
'<option' . (getParameterValue("0", "PZM_ROUNDTRIPKM", $hq_id) == "1" ? ' selected' : '') . '>Rundtour</option>' .
'</select>' . "<br>\n" .
"'<b>Last-KM</b>': Berechnung der kürzesten Anfahrt<br>'<b>Rundtour</b>': Berechnung der Hin- und Rückfahrt<br>(wenn das Häkchen bei der betreffenden Zone unten gesetzt ist)" . "<br>\n";
$ret_val .=
"<b>Routenberechnung</b>: " .
'<select id="select_PZM_SHORTEST" onchange=' .
"\"set_attribute('', '" . $hq_id . "', 'Routenberechnung', document.getElementById('select_PZM_SHORTEST').value.replace(/ /g,'_').replace(/ü/g,'ue'))\">" .
'<option' . (getParameterValue("0", "PZM_SHORTEST", $hq_id) == "0" ? ' selected' : '') . '>Normal</option>' .
'<option' . (getParameterValue("0", "PZM_SHORTEST", $hq_id) == "1" ? ' selected' : '') . '>Kürzeste von drei</option>' .
'<option' . (getParameterValue("0", "PZM_SHORTEST", $hq_id) == "2" ? ' selected' : '') . '>Absolut kürzeste</option>' .
'</select>' . "<br>\n" .
"'<b>Normal</b>': Es wird nur eine Route angefragt<br>'<b>Kürzeste von drei</b>': Die kürzeste von drei von Google vorgeschlagenen realistischen Routen<br>'<b>Absolut kürzeste</b>': Google ermittelt die kürzest mögliche Route" . "<br>\n" .
"<br>\n";
$ret_val .= "Lfd_Nr.;Name;<b>Berechnung der " . (getParameterValue("0", "PZM_ROUNDTRIPKM", $hq_id) == "1" ? "Hin-/Rück" : "An") . "fahrt</b><br>\n";
$special_columns = 5;
}
myWriteLog($sql_query);
if ($f_what != 18) {
$res = $db->query($sql_query);
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sql_query'" . $res->getMessage());
}
$rowcnt = 1;
$colCnt = 0;
$responseCnt = 0;
while ($row = ($f_what != 18 ? ($phpVersion >= "8.0" ? $res->fetch_assoc() : $res->fetchRow()) : ($responseCnt < count($responseArr) ? $responseArr[$responseCnt++] : false))) {
$row = array_values($row);
if ($colCnt % 2 == 0) { $cC = "#aa"; $cC2 = "#ab"; }
if ($colCnt++ % 2 == 1) { $cC = "#ba"; $cC2 = "#bb"; }
$ret_val .= $cC . ($f_what == 10 || $f_what == 12 ? " <a href=\"javascript:set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", " . ($f_what == 10 ? "'Photo'" : "'Serviceprice'") . ", 0)\"><img src=\"../images/trash.jpg\" border=\"0\" height=\"13\" width=\"8\" alt=\"löschen\"></a> " : "") . $rowcnt++ . ";";
for ($i = 0; $i < count($row) - $special_columns; $i++) {
$ret_val .= ($i % 2 == 1 ? $cC : $cC2) . str_replace(";", "", $row[$i]) . ";";
}
if ($f_what == 3) {
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . "<input type=\"checkbox\" name=\"fm_" . $row[count($row) - 1] . "\" value=\"1\" " .
(in_array($row[count($row) - 1], $cs_ids['RANKING_JB2CRVH_MEASURE_CS_IDS']) ? "checked onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'Lademaße', 0)\"" : "onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'Lademaße', 1)\"") . ">" . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . "<input type=\"checkbox\" name=\"di_" . $row[count($row) - 1] . "\" value=\"1\" " .
(in_array($row[count($row) - 1], $cs_ids['JB_DISPOINFO_ENABLED_CS_IDS']) ? "checked onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'Dispo-Info', 0)\"" : "onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'Dispo-Info', 1)\"") . ">" . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . "<input type=\"checkbox\" name=\"sp_" . $row[count($row) - 2] . "\" value=\"1\" " .
(in_array($row[count($row) - 1], $cs_ids['MASK_CUST_SET_PAYER']) ? "checked onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'Barzahler', 0)\"" : "onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'Barzahler', 1)\"") . ">" . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . "<input type=\"checkbox\" name=\"md_" . $row[count($row) - 2] . "\" value=\"1\" " .
(in_array($row[count($row) - 1], $cs_ids['CUSTOMER_MANUAL_DISPO']) ? "checked onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'ManuelleDisposition', 0)\"" : "onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'ManuelleDisposition', 1)\"") . ">" . ";";
}
if ($f_what == 4) {
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . "<input type=\"checkbox\" name=\"mv_" . $row[count($row) - 2] . "\" value=\"1\" " .
(in_array($row[count($row) - 1], $cs_ids['MASK_JB_MAP_VIEW_ENABLED']) ? "checked onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'Kartenansicht', 0)\"" : "onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'Kartenansicht', 1)\"") . ">" . ";";
$district_txt = "";
if (isset($jb_tourcities[$row[count($row) - 1]]))
$district_txt = " (nur " . $jb_tourcities[$row[count($row) - 1]] . ")";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . "<input type=\"checkbox\" name=\"or_" . $row[count($row) - 2] . "\" value=\"1\" " .
(in_array($row[count($row) - 2], $emp_ids) ? "checked onclick=\"set_attribute('" . $row[4] . "', " . $row[count($row) - 2] . ", 'Orte', 0)\"" : "onclick=\"set_attribute('" . $row[4] . "', " . $row[count($row) - 2] . ", 'Orte', 1)\"") . ">" . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . "<input type=\"checkbox\" name=\"st_" . $row[count($row) - 2] . "\" value=\"1\" " .
(in_array($row[count($row) - 1], $cs_ids['MASK_INSERTADDRESS_DISTRICT']) ? "checked onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'Stadtteile', 0)\"" : "onclick=\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'Stadtteile', 1)\"") . ">" . $district_txt . ";";
}
if ($f_what == 9) {
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . getVPAInfo($row[count($row) - 2]) . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . $cs_ids[$row[count($row) - 1]][0] . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . $cs_ids[$row[count($row) - 1]][1] . ";";
}
if ($f_what == 10) {
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . (in_array($row[count($row) - 1], $cs_ids['MASK_TR_PHOTO_CS_1']) ? "ja" : "nein") . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . (in_array($row[count($row) - 1], $cs_ids['MASK_TR_PHOTO_CS_2']) ? "ja" : "nein") . ";";
$min_max_tr_photo_cs = explode("|", $cs_ids['MASK_MIN_MAX_TR_PHOTO_CS'][$row[count($row) - 1]]);
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) .
'<select id="minSelect_' . $row[count($row) - 1] . '" onchange=' .
"\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'AnzahlFotos', document.getElementById('minSelect_" . $row[count($row) - 1] . "').value + '|' + document.getElementById('maxSelect_" . $row[count($row) - 1] . "').value)\">" .
'<option' . ($min_max_tr_photo_cs[0] == 0 ? ' selected' : '') . '>0</option>' .
'<option' . ($min_max_tr_photo_cs[0] == 1 ? ' selected' : '') . '>1</option>' .
'<option' . ($min_max_tr_photo_cs[0] == 2 ? ' selected' : '') . '>2</option>' .
'<option' . ($min_max_tr_photo_cs[0] == 3 ? ' selected' : '') . '>3</option>' .
'<option' . ($min_max_tr_photo_cs[0] == 4 ? ' selected' : '') . '>4</option>' .
'<option' . ($min_max_tr_photo_cs[0] == 5 ? ' selected' : '') . '>5</option>' .
'</select>' . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) .
'<select id="maxSelect_' . $row[count($row) - 1] . '" onchange=' .
"\"set_attribute('" . $row[0] . "', " . $row[count($row) - 1] . ", 'AnzahlFotos', document.getElementById('minSelect_" . $row[count($row) - 1] . "').value + '|' + document.getElementById('maxSelect_" . $row[count($row) - 1] . "').value)\">" .
'<option' . ($min_max_tr_photo_cs[1] == 1 ? ' selected' : '') . '>1</option>' .
'<option' . ($min_max_tr_photo_cs[1] == 2 ? ' selected' : '') . '>2</option>' .
'<option' . ($min_max_tr_photo_cs[1] == 3 ? ' selected' : '') . '>3</option>' .
'<option' . ($min_max_tr_photo_cs[1] == 4 ? ' selected' : '') . '>4</option>' .
'<option' . ($min_max_tr_photo_cs[1] == 5 ? ' selected' : '') . '>5</option>' .
'</select>' . ";";
}
if ($f_what == 14) { // honk
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . (isset($cs_ids[1]) && in_array($row[0], $cs_ids[1]) ? "X" : "") . ";"; // Services
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . (isset($cs_ids[0]) && in_array($row[0], $cs_ids[0]) ? "X" : "") . ";"; // Calc
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . (isset($cs_ids[2]) && in_array($row[0], $cs_ids[2]) ? "X" : "") . ";"; // PLZ
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . (isset($cs_ids[3]) && in_array($row[0], $cs_ids[3]) ? "X" : "") . ";"; // Bezirke
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . (isset($cs_ids[4]) && in_array($row[0], $cs_ids[4]) ? $cr_sids[$row[0]] : "") . ";"; // Kurierprovision
}
if ($f_what == 17) {
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . "<a href=\"../jobs/jb_special.php?cs_id=" . $row[4] . "\" target=\"_blank\">" . $row[3] . "</a>" . ";";
}
if ($f_what == 18) {
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . str_replace("KM-Preis ", "", $srv_names_PZM[$hq_id][$row[0] . "_km"]) . /* " (\"" . $row[0] . "\")" . */ ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . "<input type=\"checkbox\" name=\"" . $row[0] . "_eap" . "\" value=\"1\" " .
($row[3] == "0" ? "checked onclick=\"set_attribute('', '" . $hq_id . "', '" . $row[0] . "_eap', 1)\"" : "onclick=\"set_attribute('', '" . $hq_id . "', '" . $row[0] . "_eap', 0)\"") . ">" . ";";
}
$ret_val .= "<br>\n";
}
if ($f_what != 18)
$res->free();
if ($f_what == 13) {
// $ret_val = str_replace("MAIL_SURVEY_ADDRESS_TO", "Auftragsdatum liegt mehr als ein Jahr in der Zukunft", $ret_val);
}
$csv_name = ($f_what == 3 ? "Kundenmasken-Erweiterungen" : ($f_what == 4 ? "Kundenzugang-Listenerweiterungen" : ($f_what == 9 ? "Transporteure" : ($f_what == 10 ? "Fotos" : ($f_what == 11 ? "Auslandsrechnungen" : ($f_what == 12 ? "Servicepreis-Ausweis" : ($f_what == 14 ? "Sonderkonditionen" : ($f_what == 17 ? "Kunden-Konserven" : "")))))))) . "_" . date("d.m.Y") . ".csv";
// if ($dl == 1 && ($f_what == 3 || $f_what == 9 || $f_what == 11 || $f_what == 14 || $f_what == 17)):
// if ($f_what == 3) {
// do {
// $pos = strpos($ret_val, "<input type=");
// if ($pos !== false) {
// $pos1 = strpos($ret_val, ">", $pos);
// if ($pos1 !== false) {
// $replace_str = "nein";
// $pos2 = strpos($ret_val, "checked", $pos);
// if ($pos2 !== false && $pos2 < $pos1) {
// $replace_str = "ja";
// }
// $ret_val = substr($ret_val, 0, $pos) . $replace_str . substr($ret_val, $pos1 + 1);
// }
// }
// } while ($pos !== false);
// }
// mk_csv_download($csv_name, $ret_val);
// exit();
// endif;
if ($f_what == 9 || $f_what == 11)
$error_text = "";
if ($f_what == 3 || $f_what == 9 || $f_what == 11 || $f_what == 14 || $f_what == 17) {
if ($f_what == 3) {
do {
$pos = strpos($ret_val, "<input type=");
if ($pos !== false) {
$pos1 = strpos($ret_val, ">", $pos);
if ($pos1 !== false) {
$replace_str = "nein";
$pos2 = strpos($ret_val, "checked", $pos);
if ($pos2 !== false && $pos2 < $pos1) {
$replace_str = "ja";
}
$ret_val = substr($ret_val, 0, $pos) . $replace_str . substr($ret_val, $pos1 + 1);
}
}
} while ($pos !== false);
}
$fileHandle = @fopen("../temp/download/" . $csv_name, 'w');
fwrite($fileHandle, str_replace("<br>", "", str_replace("#aa", "", str_replace("#ab", "", str_replace("#ba", "", str_replace("#bb", "", $ret_val))))));
@fclose($fileHandle);
// $error_text .= "<a href=\"statistic_finishment.php?dl=1&what=" . $f_what . "\">" . $csv_name . "</a><br><br>\n";
$error_text .= "<a href=\"" . "../temp/download/" . $csv_name . "\"><br>" . $csv_name . "</a><br><br>\n";
}
$content = csv_output($ret_val, "left");
}
//function mk_csv_download($csv_name, $csv_txt) {
// header('Content-Disposition: attachment; filename="' . $csv_name . '"');
// echo str_replace("<br>", "", str_replace("#aa", "", str_replace("#ab", "", str_replace("#ba", "", str_replace("#bb", "", $csv_txt)))));
//}
function csv_output($csv_txt, $align = "right") {
$csv_txt = "<table border=0 cellspacing=0 cellpadding=4 vspace=0 hspace=0>\n<tr><td align=" . $align . ">" .
str_replace("<br>\n", "</td></tr>\n<tr><td align=" . $align . ">", str_replace(";", "</td><td align=" . $align . ">", $csv_txt)) .
"</td></tr>\n</table>\n";
return str_replace(">#aa", " bgcolor=\"#DDDDDD\">", str_replace(">#ab", " bgcolor=\"#EEEEEE\">", str_replace(">#ba", " bgcolor=\"#BBBBFF\">", str_replace(">#bb", " bgcolor=\"#CCCCFF\">", $csv_txt))));
}
function csv_statistic($f_cs_eid, $f_min_date, $f_max_date, $f_what) {
global $db2, $hq_id, $phpVersion, $dbname, $dblogin, $dbpassword;
if ($f_what == 1) {
$ret_val = "Lfd._Nr;Auftragsnummer;TT_Nummer;Fahrzeug_angefordert;Vorbestellung;Bestellzeit;PLZ;Abholung;abgeholt_nach;Kommission;PLZ_1;Erledigung_1;erledigt_1_nach;Kommission_1;" .
"PLZ_2;Erledigung_2;erledigt_2_nach;Kommission_2;PLZ_3;Erledigung_3;erledigt_3_nach;Kommission_3;PLZ_4;Erledigung_4;erledigt_4_nach;Kommission_4;PLZ_5;Erledigung_5;erledigt_5_nach;Kommission_5;" .
"PLZ_6;Erledigung_6;erledigt_6_nach;Kommission_6;PLZ_7;Erledigung_7;erledigt_7_nach;Kommission_7;PLZ_8;Erledigung_8;erledigt_8_nach;Kommission_8;PLZ_9;Erledigung_9;erledigt_9_nach;Kommission_9;" .
"PLZ_10;Erledigung_10;erledigt_10_nach;Kommission_10;PLZ_11;Erledigung_11;erledigt_11_nach;Kommission_11;PLZ_12;Erledigung_12;erledigt_12_nach;Kommission_12;" .
"PLZ_13;Erledigung_13;erledigt_13_nach;Kommission_13;PLZ_14;Erledigung_14;erledigt_14_nach;Kommission_14;PLZ_15;Erledigung_15;erledigt_15_nach;Kommission_15;" .
"PLZ_16;Erledigung_16;erledigt_16_nach;Kommission_16;PLZ_17;Erledigung_17;erledigt_17_nach;Kommission_17;PLZ_18;Erledigung_18;erledigt_18_nach;Kommission_18;" .
"PLZ_19;Erledigung_19;erledigt_19_nach;Kommission_19" .
"<br>\n";
$extra_clauses = array("< 7", ">= 7");
} elseif ($f_what == 2) {
$ret_val = "Lfd._Nr;Auftragsnr;Datum;Servicepreis;Auftragspreis;Fahrzeugtyp<br>\n";
$extra_clauses = array("dummy");
} elseif ($f_what == 5) {
$ret_val = "Lfd._Nr;Zahler;PLZ;Monat;TT-Nummer;Anzahl_Stopps<br>\n";
$extra_clauses = array("dummy");
} elseif ($f_what == 6) {
$ret_val = "Lfd._Nr;Zahler;PLZ;Ort;Monat;TT-Nummer;Anzahl_Stopps<br>\n";
$extra_clauses = array("dummy");
} elseif ($f_what == 7) {
$ret_val = "Lfd._Nr;Etappenpreis;Anzahl_PLZ;Anzahl_Fix;Summe<br>\n";
$extra_clauses = array("dummy");
} elseif ($f_what == 8) {
$ret_val = "Lfd._Nr;Datum;Fahrer;Nummer;PLZ;Adresse;Fahrzeit;Ankunft;Abfahrt;Servicezeit<br>\n";
$extra_clauses = array("dummy");
} elseif ($f_what == 15) {
$ret_val = "Lfd._Nr;Tourname;Auftragsnr;Anzahl<br>\n";
$extra_clauses = array("dummy");
} elseif ($f_what == 16) {
$ret_val = "Lfd._Nr;Herkunft;Anzahl<br>\n";
$extra_clauses = array("dummy");
} elseif ($f_what == 19) {
$ret_val = "Lfd._Nr;Auftrag;Fahrzeug;Ausführung;Export;Preis_ohne_TSZ/Service;Festpreis;Festpreis-Typ;PZM-Preis;PZM-Km;PZM-Preisdetails<br>\n";
$extra_clauses = array("dummy");
}
$rowcnt = 1;
foreach ($extra_clauses as $extra_clause) {
if ($f_what == 1) {
$sql_query =
"SELECT job.jb_id AS Auftragsnummer," .
" cr_sid AS TT_Nummer," .
" (CASE vht_id" .
" WHEN 0 THEN '---'" .
" WHEN 1 THEN 'Fahrrad'" .
" WHEN 2 THEN 'PKW'" .
" WHEN 3 THEN 'Kombi'" .
" WHEN 4 THEN 'Kastenwagen'" .
" WHEN 5 THEN 'Bus'" .
" WHEN 6 THEN 'Großer Bus'" .
" WHEN 7 THEN 'LKW'" .
" WHEN 11 THEN 'LKW 12 To.'" .
" ELSE 'unbekannt' END) AS Fahrzeug_angefordert," .
// " (CASE vht_id_real" .
// " WHEN 0 THEN '---'" .
// " WHEN 1 THEN 'Fahrrad'" .
// " WHEN 2 THEN 'PKW'" .
// " WHEN 3 THEN 'Kombi'" .
// " WHEN 4 THEN 'Kastenwagen'" .
// " WHEN 5 THEN 'Bus'" .
// " WHEN 6 THEN 'Großer Bus'" .
// " WHEN 7 THEN 'LKW'" .
// " WHEN 11 THEN 'LKW 12 To.'" .
// " ELSE 'unbekannt' END) AS Fahrzeug_gefahren," .
// " log_createtime AS Erfassung," .
" (CASE jb_reserv WHEN 1 THEN 'ja' ELSE 'nein' END) AS Vorbestellung," .
" jb_ordertime AS Bestellzeit," .
" IFNULL(ad1.ad_zipcode, '') AS PLZ," .
" IFNULL(tr1.tr_finishtime, '') AS Abholung," .
" IFNULL(TIMEDIFF(tr1.tr_finishtime, jb_ordertime), '') AS abgeholt_nach," .
" tr1.tr_commission_no AS tr_commission_no," .
" IFNULL(ad2.ad_zipcode, '') AS PLZ_1," .
" IFNULL(tr2.tr_finishtime, '') AS Erledigung_1," .
" IFNULL(TIMEDIFF(tr2.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_1," .
" tr2.tr_commission_no AS tr_commission_no_1," .
" IFNULL(ad3.ad_zipcode, '') AS PLZ_2," .
" IFNULL(tr3.tr_finishtime, '') AS Erledigung_2," .
" IFNULL(TIMEDIFF(tr3.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_2," .
" tr3.tr_commission_no AS tr_commission_no_2," .
" IFNULL(ad4.ad_zipcode, '') AS PLZ_3," .
" IFNULL(tr4.tr_finishtime, '') AS Erledigung_3," .
" IFNULL(TIMEDIFF(tr4.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_3," .
" tr4.tr_commission_no AS tr_commission_no_3," .
" IFNULL(ad5.ad_zipcode, '') AS PLZ_4," .
" IFNULL(tr5.tr_finishtime, '') AS Erledigung_4," .
" IFNULL(TIMEDIFF(tr5.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_4," .
" tr5.tr_commission_no AS tr_commission_no_4," .
" IFNULL(ad6.ad_zipcode, '') AS PLZ_5," .
" IFNULL(tr6.tr_finishtime, '') AS Erledigung_5," .
" IFNULL(TIMEDIFF(tr6.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_5," .
" tr6.tr_commission_no AS tr_commission_no_5," .
" IFNULL(ad7.ad_zipcode, '') AS PLZ_6," .
" IFNULL(tr7.tr_finishtime, '') AS Erledigung_6," .
" IFNULL(TIMEDIFF(tr7.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_6," .
" tr7.tr_commission_no AS tr_commission_no_6," .
" IFNULL(ad8.ad_zipcode, '') AS PLZ_7," .
" IFNULL(tr8.tr_finishtime, '') AS Erledigung_7," .
" IFNULL(TIMEDIFF(tr8.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_7," .
" tr8.tr_commission_no AS tr_commission_no_7," .
" IFNULL(ad9.ad_zipcode, '') AS PLZ_8," .
" IFNULL(tr9.tr_finishtime, '') AS Erledigung_8," .
" IFNULL(TIMEDIFF(tr9.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_8," .
" tr9.tr_commission_no AS tr_commission_no_8," .
" IFNULL(ad10.ad_zipcode, '') AS PLZ_9," .
" IFNULL(tr10.tr_finishtime, '') AS Erledigung_9," .
" IFNULL(TIMEDIFF(tr10.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_9," .
" tr10.tr_commission_no AS tr_commission_no_9," .
" IFNULL(ad11.ad_zipcode, '') AS PLZ_10," .
" IFNULL(tr11.tr_finishtime, '') AS Erledigung_10," .
" IFNULL(TIMEDIFF(tr11.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_10," .
" tr11.tr_commission_no AS tr_commission_no_10," .
" IFNULL(ad12.ad_zipcode, '') AS PLZ_11," .
" IFNULL(tr12.tr_finishtime, '') AS Erledigung_11," .
" IFNULL(TIMEDIFF(tr12.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_11," .
" tr12.tr_commission_no AS tr_commission_no_11," .
" IFNULL(ad13.ad_zipcode, '') AS PLZ_12," .
" IFNULL(tr13.tr_finishtime, '') AS Erledigung_12," .
" IFNULL(TIMEDIFF(tr13.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_12," .
" tr13.tr_commission_no AS tr_commission_no_12," .
" IFNULL(ad14.ad_zipcode, '') AS PLZ_13," .
" IFNULL(tr14.tr_finishtime, '') AS Erledigung_13," .
" IFNULL(TIMEDIFF(tr14.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_13," .
" tr14.tr_commission_no AS tr_commission_no_13," .
" IFNULL(ad15.ad_zipcode, '') AS PLZ_14," .
" IFNULL(tr15.tr_finishtime, '') AS Erledigung_14," .
" IFNULL(TIMEDIFF(tr15.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_14," .
" tr15.tr_commission_no AS tr_commission_no_14," .
" IFNULL(ad16.ad_zipcode, '') AS PLZ_15," .
" IFNULL(tr16.tr_finishtime, '') AS Erledigung_15," .
" IFNULL(TIMEDIFF(tr16.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_15," .
" tr16.tr_commission_no AS tr_commission_no_15," .
" IFNULL(ad17.ad_zipcode, '') AS PLZ_16," .
" IFNULL(tr17.tr_finishtime, '') AS Erledigung_16," .
" IFNULL(TIMEDIFF(tr17.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_16," .
" tr17.tr_commission_no AS tr_commission_no_16," .
" IFNULL(ad18.ad_zipcode, '') AS PLZ_17," .
" IFNULL(tr18.tr_finishtime, '') AS Erledigung_17," .
" IFNULL(TIMEDIFF(tr18.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_17," .
" tr18.tr_commission_no AS tr_commission_no_17," .
" IFNULL(ad19.ad_zipcode, '') AS PLZ_18," .
" IFNULL(tr19.tr_finishtime, '') AS Erledigung_18," .
" IFNULL(TIMEDIFF(tr19.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_18," .
" tr19.tr_commission_no AS tr_commission_no_18," .
" IFNULL(ad20.ad_zipcode, '') AS PLZ_19," .
" IFNULL(tr20.tr_finishtime, '') AS Erledigung_19," .
" IFNULL(TIMEDIFF(tr20.tr_finishtime, jb_ordertime), '') AS Erledigung_Stopp_19," .
" tr20.tr_commission_no AS tr_commission_no_19" .
// " jb_finishtime AS Erledigung_im_System" .
" FROM costcenter, customer, job" .
// " LEFT JOIN phoenix_log.log ON phoenix_log.log.jb_id = job.jb_id AND phoenix_log.log.logo_id IN (1,25)" .
" LEFT JOIN tour AS tr1 ON job.jb_id = tr1.jb_id AND tr1.tr_sort = 1" .
" LEFT JOIN tour AS tr2 ON job.jb_id = tr2.jb_id AND tr2.tr_sort = 2" .
" LEFT JOIN tour AS tr3 ON job.jb_id = tr3.jb_id AND tr3.tr_sort = 3" .
" LEFT JOIN tour AS tr4 ON job.jb_id = tr4.jb_id AND tr4.tr_sort = 4" .
" LEFT JOIN tour AS tr5 ON job.jb_id = tr5.jb_id AND tr5.tr_sort = 5" .
" LEFT JOIN tour AS tr6 ON job.jb_id = tr6.jb_id AND tr6.tr_sort = 6" .
" LEFT JOIN tour AS tr7 ON job.jb_id = tr7.jb_id AND tr7.tr_sort = 7" .
" LEFT JOIN tour AS tr8 ON job.jb_id = tr8.jb_id AND tr8.tr_sort = 8" .
" LEFT JOIN tour AS tr9 ON job.jb_id = tr9.jb_id AND tr9.tr_sort = 9" .
" LEFT JOIN tour AS tr10 ON job.jb_id = tr10.jb_id AND tr10.tr_sort = 10" .
" LEFT JOIN tour AS tr11 ON job.jb_id = tr11.jb_id AND tr11.tr_sort = 11" .
" LEFT JOIN tour AS tr12 ON job.jb_id = tr12.jb_id AND tr12.tr_sort = 12" .
" LEFT JOIN tour AS tr13 ON job.jb_id = tr13.jb_id AND tr13.tr_sort = 13" .
" LEFT JOIN tour AS tr14 ON job.jb_id = tr14.jb_id AND tr14.tr_sort = 14" .
" LEFT JOIN tour AS tr15 ON job.jb_id = tr15.jb_id AND tr15.tr_sort = 15" .
" LEFT JOIN tour AS tr16 ON job.jb_id = tr16.jb_id AND tr16.tr_sort = 16" .
" LEFT JOIN tour AS tr17 ON job.jb_id = tr17.jb_id AND tr17.tr_sort = 17" .
" LEFT JOIN tour AS tr18 ON job.jb_id = tr18.jb_id AND tr18.tr_sort = 18" .
" LEFT JOIN tour AS tr19 ON job.jb_id = tr19.jb_id AND tr19.tr_sort = 19" .
" LEFT JOIN tour AS tr20 ON job.jb_id = tr20.jb_id AND tr20.tr_sort = 20" .
" LEFT JOIN address AS ad1 ON tr1.ad_id = ad1.ad_id" .
" LEFT JOIN address AS ad2 ON tr2.ad_id = ad2.ad_id" .
" LEFT JOIN address AS ad3 ON tr3.ad_id = ad3.ad_id" .
" LEFT JOIN address AS ad4 ON tr4.ad_id = ad4.ad_id" .
" LEFT JOIN address AS ad5 ON tr5.ad_id = ad5.ad_id" .
" LEFT JOIN address AS ad6 ON tr6.ad_id = ad6.ad_id" .
" LEFT JOIN address AS ad7 ON tr7.ad_id = ad7.ad_id" .
" LEFT JOIN address AS ad8 ON tr8.ad_id = ad8.ad_id" .
" LEFT JOIN address AS ad9 ON tr9.ad_id = ad9.ad_id" .
" LEFT JOIN address AS ad10 ON tr10.ad_id = ad10.ad_id" .
" LEFT JOIN address AS ad11 ON tr11.ad_id = ad11.ad_id" .
" LEFT JOIN address AS ad12 ON tr12.ad_id = ad12.ad_id" .
" LEFT JOIN address AS ad13 ON tr13.ad_id = ad13.ad_id" .
" LEFT JOIN address AS ad14 ON tr14.ad_id = ad14.ad_id" .
" LEFT JOIN address AS ad15 ON tr15.ad_id = ad15.ad_id" .
" LEFT JOIN address AS ad16 ON tr16.ad_id = ad16.ad_id" .
" LEFT JOIN address AS ad17 ON tr17.ad_id = ad17.ad_id" .
" LEFT JOIN address AS ad18 ON tr18.ad_id = ad18.ad_id" .
" LEFT JOIN address AS ad19 ON tr19.ad_id = ad19.ad_id" .
" LEFT JOIN address AS ad20 ON tr20.ad_id = ad20.ad_id" .
" WHERE job.csc_id_payer = costcenter.csc_id AND costcenter.cs_id = customer.cs_id AND cs_eid = '" . $f_cs_eid . "'" .
" AND jb_finishtime > '" . $f_min_date . " 00:00:00' AND jb_finishtime < '" . $f_max_date . " 23:59:59'" .
" AND jb_status = 2 AND jb_storno IS NULL" .
" AND vht_id " . $extra_clause .
" ORDER BY Erledigung_Stopp_1";
} elseif ($f_what == 2) {
$sql_query =
"SELECT jb_id AS Auftragsnr, jb_ordertime AS Datum," .
" CONCAT(REPLACE (ROUND(jb_serviceprice, 2) , '.', ','), ' €') AS Service_Preis," .
" CONCAT(REPLACE (ROUND(jb_totalprice, 2) , '.', ','), ' €') AS Auftragspreis," .
" mt_value AS Fahrzeugtyp" .
" FROM job, costcenter, customer, metatype" .
" WHERE NOT (jb_export_time IS NULL) AND jb_serviceprice > 0" .
" AND job.csc_id_payer = costcenter.csc_id AND costcenter.cs_id = customer.cs_id" .
" AND customer.cs_eid = '" . $f_cs_eid . "'" .
" AND jb_finishtime > '" . $f_min_date . " 00:00:00' AND jb_finishtime < '" . $f_max_date . " 23:59:59'" .
" AND mt_type = 'vehicletype' AND vht_id = mt_sort ORDER BY mt_value, jb_ordertime";
} elseif ($f_what == 5 || $f_what == 6) {
$sql_query =
"SELECT cs.cs_eid, ad.ad_zipcode, " . ($f_what == 6 ? "ad.ad_city, " : "") . "LEFT(jb_ordertime, 7), cr_sid, COUNT(*) AS num" .
" FROM job AS jb, costcenter AS csc, customer AS cs, company AS cmp, tour AS tr, address AS ad" .
" 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_ordertime >= '" . $f_min_date . " 00:00:00' AND jb.jb_ordertime <= '" . $f_max_date . " 23:59:59' AND jb.csc_id_payer = csc.csc_id AND csc.cs_id =" .
" cs.cs_id AND cs.cmp_id = cmp.cmp_id AND cs.cs_eid = '" . $f_cs_eid . "' AND tr.jb_id = jb.jb_id AND tr.ad_id = ad.ad_id AND tr_sort > 1" .
" GROUP BY cs.cs_id, ad.ad_zipcode, " . ($f_what == 6 ? "ad.ad_city, " : "") . "LEFT(jb_ordertime, 7), cr_sid" .
" ORDER BY cs.cs_eid, ad.ad_zipcode, " . ($f_what == 6 ? "ad.ad_city, " : "") . "LEFT(jb_ordertime, 7), cr_sid";
} elseif ($f_what == 7) {
$sql_query =
"(SELECT CONCAT(REPLACE (ROUND(trs.trs_price, 2) , '.', ','), ' €') AS Etappenpreis," .
" COUNT(IF(trs.trs_srv_name LIKE '% nach %', 1, NULL)) 'Anzahl_PLZ'," .
" COUNT(IF(trs.trs_srv_name = 'Fixpreis', 1, NULL)) 'Anzahl_Fix'," .
" COUNT(*) AS Summe" .
" FROM" .
" customer AS cs," .
" costcenter AS csc," .
" job AS jb," .
" tourservice AS trs" .
" WHERE" .
" cs.cs_eid = '" . $f_cs_eid . "' AND" .
" csc.cs_id = cs.cs_id AND" .
" jb.csc_id_payer = csc.csc_id AND" .
" jb.jb_finishtime >= '" . $f_min_date . " 00:00:00' AND" .
" jb.jb_finishtime <= '" . $f_max_date . " 23:59:59' 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" .
" trs.jb_id = jb.jb_id AND trs.trs_price > 0 AND" .
" (trs.trs_srv_name LIKE '% nach %' OR trs.trs_srv_name = 'Fixpreis')" .
" GROUP BY trs.trs_price)" .
" ORDER BY trs_price";
} elseif ($f_what == 8) {
$sql_query =
"SELECT jb_ordertime AS Datum, cr_sid AS Fahrer, job.jb_id AS Nummer, ad_zipcode AS PLZ, CONCAT(ad_street, ' ', tr_hsno) AS Adresse," .
" CONCAT(MID(genericdatacontainer.gdc_content, 9, 2), ':', MID(genericdatacontainer.gdc_content, 11, 2), ':00') AS Ankunft," .
" CONCAT(MID(tr_finishtime, 11, 6), ':00') AS Abfahrt, " .
" SEC_TO_TIME(UNIX_TIMESTAMP(CONCAT('1970-01-01 ', MID(tr_finishtime, 11, 6), ':00')) - ".
" UNIX_TIMESTAMP(CONCAT('1970-01-01 ', MID(genericdatacontainer.gdc_content, 9, 2), ':', MID(genericdatacontainer.gdc_content, 11, 2), ':00'))) AS Servicezeit" .
// " ROUND((UNIX_TIMESTAMP(CONCAT('1970-01-01 ', RIGHT (tr_finishtime, 8))) - ".
// " UNIX_TIMESTAMP(CONCAT('1970-01-01 ', MID(genericdatacontainer.gdc_content, 9, 2), ':', MID(genericdatacontainer.gdc_content, 11, 2), ':', MID(genericdatacontainer.gdc_content, 13, 2)))) / 60) AS Servicezeit" .
// " TIMEDIFF (UNIX_TIMESTAMP(CONCAT('1970-01-01 ', RIGHT (tr_finishtime, 8))), UNIX_TIMESTAMP(CONCAT('2070-01-01 ', MID(genericdatacontainer.gdc_content, 9, 2), ':', MID(genericdatacontainer.gdc_content, 11, 2), ':', MID(genericdatacontainer.gdc_content, 13, 2))) AS Servicezeit" .
" FROM address, genericdatacontainer, tour, job, customer, costcenter" .
" WHERE cs_eid = '" . $f_cs_eid . "'" .
" AND jb_ordertime >= '" . $f_min_date . " 00:00:00'" .
" AND jb_ordertime <= '" . $f_max_date . " 23:59:59'" .
" AND customer.cs_id = costcenter.cs_id AND costcenter.csc_id = job.csc_id_payer" .
" AND tour.jb_id = job.jb_id AND gdc_obj_id = tr_id AND gdc_obj_type = 'tr'" .
" AND tour.ad_id = address.ad_id AND NOT (tr_finishtime IS NULL)" .
" AND tr_sort = 2 AND cr_sid != ''" .
" ORDER BY LEFT(tr_finishtime, 10), cr_sid, jb_ordertime, tr_finishtime";
} elseif ($f_what == 15) {
$sql_query = "SELECT jb_id, jb_tourname FROM job, customer, costcenter WHERE cs_eid = '" . $f_cs_eid . "' AND customer.cs_id = costcenter.cs_id AND costcenter.csc_id = job.csc_id_payer AND NOT (jb_tourname IS NULL OR jb_tourname = '') ORDER BY jb_tourname";
myWriteLog($sql_query);
$res = $db2->query($sql_query);
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sql_query'" . $res->getMessage());
$jb_tournames = array();
while ($row = ($phpVersion >= "8.0" ? $res->fetch_assoc() : $res->fetchRow())) {
$jb_tournames[$row["jb_tourname"]] = array($row["jb_tourname"], $row["jb_id"], 0);
}
$res->free();
$sql_query =
"SELECT jb_id FROM job, customer, costcenter" .
" WHERE cs_eid = '" . $f_cs_eid . "'" .
" AND jb_ordertime >= '" . $f_min_date . " 00:00:00'" .
" AND jb_ordertime <= '" . $f_max_date . " 23:59:59'" .
" AND customer.cs_id = costcenter.cs_id AND costcenter.csc_id = job.csc_id_payer";
myWriteLog($sql_query);
$res = $db2->query($sql_query);
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sql_query'" . $res->getMessage());
$jb_ids_sql = "";
while ($row = ($phpVersion >= "8.0" ? $res->fetch_assoc() : $res->fetchRow())) {
$jb_ids_sql .= "," . $row["jb_id"];
}
$res->free();
if($jb_ids_sql == "")
$jb_ids_sql = "0";
else
$jb_ids_sql = substr($jb_ids_sql, 1);
$sql_query =
"SELECT SUBSTR(SUBSTR(logo_description, INSTR( logo_description, 'Konserve \"') + LENGTH( 'Konserve \"')), 1, INSTR(SUBSTR(logo_description, INSTR( logo_description, 'Konserve \"' ) + LENGTH( 'Konserve \"')) , '\"') - 1)" .
" AS jb_tourname, COUNT(*) AS cnt FROM phoenix_log.log WHERE logo_id IN (25, 1, 162, 163) AND jb_id IN (" . $jb_ids_sql . ") AND log_createtime > '2024-02-23 00:00:00' AND logo_description LIKE '%Konserve%' GROUP BY jb_tourname ORDER BY COUNT(*) DESC";
myWriteLog($sql_query);
$res = $db2->query($sql_query);
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sql_query'" . $res->getMessage());
while ($row = ($phpVersion >= "8.0" ? $res->fetch_assoc() : $res->fetchRow())) {
$jb_tournames[$row["jb_tourname"]][2] = $row["cnt"];
}
$res->free();
uasort($jb_tournames, "cmp");
$colCnt = 0;
foreach($jb_tournames as $jb_tourname => $values) {
if ($colCnt % 2 == 0) { $cC = "#aa"; $cC2 = "#ab"; }
if ($colCnt++ % 2 == 1) { $cC = "#ba"; $cC2 = "#bb"; }
$ret_val .= $cC . $rowcnt++ . ";";
$row = array($jb_tourname, $values[1], $values[2]);
for ($i = 0; $i < count($row); $i++) {
$ret_val .= ($i % 2 == 1 ? $cC : $cC2) . $row[$i] . ";";
}
$ret_val .= "<br>\n";
}
return $ret_val;
} elseif ($f_what == 16) {
$sql_query = "SELECT" .
" (CASE jb_origin" .
" WHEN 0 THEN 'ohne Angabe'" .
" WHEN 1 THEN 'Telefon'" .
" WHEN 2 THEN 'Mail'" .
" WHEN 3 THEN 'Schnittstelle'" .
" WHEN 4 THEN 'KAE'" .
" WHEN 5 THEN 'Dauerauftrag'" .
" WHEN 7 THEN 'Listenbuchung'" .
" WHEN 8 THEN 'Höherversicherung'" .
" WHEN 6 THEN 'Sonstiges'" .
" ELSE 'unbekannt' END) AS Auftragsherkunft," .
" COUNT(*) FROM job WHERE hq_id = " . $hq_id . " AND jb_ordertime >= '" . $f_min_date . " 00:00:00' AND jb_ordertime <= '" . $f_max_date . " 23:59:59' AND (jb_storno = 0 OR jb_storno IS NULL) GROUP BY jb_origin";
} elseif ($f_what == 19) {
$vht_ids = array (
2 => "PKW",
3 => "Kombi",
4 => "Kastenwagen",
5 => "Transporter",
6 => "XL-Transporter",
7 => "LKW"
);
$db_conn = getDbConnectionSpecial ("172.16.0.203:3391", $dbname, $dblogin, $dbpassword);
$sqlquery =
"SELECT customer.cs_id, job.jb_id, vht_id, jb_ordertime, jb_fixprice, jb_subtotalprice, jb_serviceprice, jb_export_time" .
" FROM costcenter, customer, job" .
" WHERE cs_eid = '" . $f_cs_eid . "'" .
" AND jb_ordertime >= '" . $f_min_date . " 00:00:00'" .
" AND jb_ordertime <= '" . $f_max_date . " 23:59:59'" .
" AND job.csc_id_payer = costcenter.csc_id AND costcenter.cs_id = customer.cs_id" .
" AND jb_status = 2 AND (jb_storno IS NULL OR jb_storno = 0) AND jb_incomplete = 0" .
" ORDER BY jb_ordertime, jb_id";
myWriteLog($sqlquery);
$rowcnt = 1;
$colCnt = 0;
$res = $db_conn->query($sqlquery);
if (DB::isError($res)) die ("$PHP_SELF: '$sqlquery': " . $res->getMessage());
while ($row = ($phpVersion >= "8.0" ? $res->fetch_assoc() : $res->fetchRow())):
$is_pzm = "";
if ($row["jb_fixprice"] != 0)
if ($db_conn->getOne("SELECT jb_id FROM tourservice WHERE jb_id = " . $row['jb_id'] . " AND trs_srvt_name = 'z'") == $row['jb_id'])
$is_pzm = "PZM";
if ($colCnt % 2 == 0) { $cC = "#aa"; $cC2 = "#ab"; }
if ($colCnt++ % 2 == 1) { $cC = "#ba"; $cC2 = "#bb"; }
$i = 0;
$ret_val .= $cC . $rowcnt++ . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . $row["jb_id"] . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . $vht_ids[$row["vht_id"]] . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . $row["jb_ordertime"] . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . $row["jb_export_time"] . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . str_replace(".", ",", sprintf("%01.2f", $row["jb_subtotalprice"] - $row["jb_serviceprice"])) . " " . chr(128) . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . ($row["jb_fixprice"] != 0 ? str_replace(".", ",", sprintf("%01.2f", $row["jb_fixprice"])) . " " . chr(128) : "") . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . $is_pzm . ";";
// if ($row["jb_fixprice"] == 0 || $is_pzm == "PZM") {
if ($row["vht_id"] != 7) { // Kein PZM-Preis für LKWs
$addr_params = "";
$sqlquery = "SELECT tr_hsno, ad_zipcode, ad_city, ad_street, ad_country FROM address AS ad, tour AS tr WHERE jb_id = " . $row["jb_id"] . " AND tr.ad_id = ad.ad_id ORDER BY tr_sort";
$res1 = $db_conn->query($sqlquery);
if (DB::isError($res1)) die ("$PHP_SELF: '$sqlquery': " . $res->getMessage());
while ($row1 = $res1->fetch_assoc()):
if ($row1['ad_country'] == "" || $row1['ad_country'] == "D")
$row1['ad_country'] = "DE";
$addr_params .= ($addr_params != "" ? "|" : "") . urlencode($row1['ad_street']) . ";" . urlencode($row1['tr_hsno']) . ";" . urlencode($row1['ad_zipcode']) . ";" . urlencode($row1['ad_city']) . ";" . urlencode($row1['ad_country']);
endwhile;
$res1->free();
$params = str_replace(" ", "%20", "hq_id=" . $hq_id . "&cs_id=" . $row["cs_id"] . "&vht_id=" . $row["vht_id"] . "&jb_markup=0&jb_ordertime=" . date("Y-m-d H:i:s") . "&mode=zone&a=" . $addr_params);
myWriteLog("https://bwv-test.assecutor.de/include/ajaxReqKmPrice.php?" . $params);
$result = file_get_contents("https://bwv-test.assecutor.de/include/ajaxReqKmPrice.php?" . $params);
eval("$" . $result);
$retValue[0] = str_replace(" [M]", "", $retValue[0]);
if ($db_conn->getOne("SELECT cs_fixprice_discount FROM customer WHERE cs_id = " . $row['cs_id']) == 1) {
$cs_discount = $db_conn->getOne("SELECT cs_fixprice_discount FROM customer WHERE cs_id = " . $row['cs_id']);
$retValue[0] = str_replace(".", ",", sprintf("%01.2f", (((float) str_replace(chr(128), "", $retValue[0])) * (100 - $cs_discount) / 100))) . " " . chr(128);
}
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . $retValue[0] . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . ($retValue[0] != "ERROR" ? $retValue[2] . ";" . ($i % 2 == 1 ? $cC : $cC2) . $retValue[3] : $retValue[1] . ";" . ($i % 2 == 1 ? $cC : $cC2)) . ";";
} else {
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . ";";
$ret_val .= ($i++ % 2 == 1 ? $cC : $cC2) . ";" . ($i % 2 == 1 ? $cC : $cC2) . ";";
}
$ret_val .= "<br>\n";
endwhile;
$res->free();
return $ret_val;
}
//echo $sql_query . "<br>\n";die();
myWriteLog("\$sql_query = [" . $sql_query . "]");
$res = $db2->query($sql_query);
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sql_query'" . $res->getMessage());
if ($f_what == 8) {
date_default_timezone_set('UTC');
$prev_cr_sid = "";
$prev_ordertime = "";
$prev_finishtime = "";
}
$colCnt = 0;
while ($row = ($phpVersion >= "8.0" ? $res->fetch_assoc() : $res->fetchRow())) {
$row = array_values($row);
//print_r($row); echo "<br>\n";
if ($f_what == 8) {
$driving_time = "";
if ($prev_cr_sid == $row[1] && $prev_ordertime == $row[0]) {
$driving_time = date("H:i", (strtotime($row[5]) - strtotime($prev_finishtime)) ) ;
} else {
$prev_cr_sid = $row[1];
$prev_ordertime = $row[0];
$ret_val .= "<br>\n";
}
$prev_finishtime = $row[6];
$row[0] = substr($row[0], 0, 10);
$row[5] = substr($row[5], 0, 5);
$row[6] = substr($row[6], 0, 6);
$row[7] = substr($row[7], 0, 5);
array_splice($row, 5, 0, $driving_time);
}
if ($colCnt % 2 == 0) { $cC = "#aa"; $cC2 = "#ab"; }
if ($colCnt++ % 2 == 1) { $cC = "#ba"; $cC2 = "#bb"; }
$ret_val .= $cC . $rowcnt++ . ";";
for ($i = 0; $i < count($row); $i++) {
$ret_val .= ($i % 2 == 1 ? $cC : $cC2) . $row[$i] . ";";
}
$ret_val .= "<br>\n";
}
$res->free();
}
return $ret_val;
}
function cmp($a, $b) {
if ($a[2] == $b[2]) {
if ($a[0] == $b[0]) {
return 0;
}
return ($a[0] < $b[0]) ? -1 : 1;
}
return ($a[2] > $b[2]) ? -1 : 1;
}
function formDate($sqlDate) {
return substr($sqlDate, 8, 2) . "." . substr($sqlDate, 5, 2) . "." . substr($sqlDate, 0, 4);
}
function sqlDate($germanDate) {
if (trim($germanDate) == "")
return "";
return substr($germanDate, 6, 4) . "-" . substr($germanDate, 3, 2) . "-" . substr($germanDate, 0, 2);
}
function myWriteLog($log_text) {
global $log_file_name;
$fileHandle = @fopen($log_file_name, 'a');
@fwrite($fileHandle, "[" . date("Y-m-d H:i:s") . "] " . $log_text . "\n");
@fclose($fileHandle);
}
function getVPAInfo($usr_id) {
// global $db_conn;
global $db;
$VPAInfoArr = array();
// $cc_appver = $db_conn->getone("SELECT cc_appver FROM temp.conn_context WHERE cust_id = '1' AND inst_id = '0' AND usr_id = " . $usr_id);
$cc_appver = $db->getone("SELECT cr_device_info FROM courier WHERE usr_id = " . $usr_id);
$cc_appverArr = explode("|", $cc_appver);
foreach($cc_appverArr as $cc_appver_item) {
$cc_appver_itemArr = explode("=", $cc_appver_item);
$VPAInfoArr[$cc_appver_itemArr[0]] = isset($cc_appver_itemArr[1]) ? $cc_appver_itemArr[1] : "";
}
return (isset($VPAInfoArr["app_ver"]) ? $VPAInfoArr["app_ver"] : "") . (isset($VPAInfoArr["os_ver"]) && trim($VPAInfoArr["os_ver"]) ? ", " : "") . (isset($VPAInfoArr["os_ver"]) ? $VPAInfoArr["os_ver"] : "") . (isset($VPAInfoArr["mod"]) && trim($VPAInfoArr["mod"]) ? ", " : "") . (isset($VPAInfoArr["mod"]) ? $VPAInfoArr["mod"] : "");
}
/**************************************************************************************************************************************************/
// HTML-Output
?>
<html>
<head>
<title>EIN KESSEL BUNTES</title>
<link rel="stylesheet" type="text/css" href="../css/phoenix.css">
<style type="text/css">
<!--
.verysmall
{ font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 2pt; font-weight: normal; padding: 0px}
-->
</style>
<script src="../js/jquery.js"></script>
<script type="text/javascript">
<!--
function ajaxRequestGet(url, data) {
$.ajax({
type: "GET",
url: url,
data: data,
async: false,
cache: false,
success: function(msg){eval(msg);}
});
}
function set_attribute(cs_eid, cs_id, attr, val) {
if ((attr == "Photo" || attr == "Serviceprice") && val == "0") {
if (!confirm("Eintrag von " + cs_eid + " wirklich löschen?"))
return;
document.statistic.error_text.value = "loeschen";
document.statistic.f_cs_eid.value = cs_eid;
mySubmit();
return;
}
//alert('../include/ajaxReqSetAttr.php?cs_id=' + cs_id + '&attr=' + attr + '&val=' + val + '|attr.indexOf("_eap")=' + attr.indexOf("_eap"));
ajaxRequestGet('../include/ajaxReqSetAttr.php', 'cs_id=' + cs_id + '&attr=' + attr + '&val=' + val);
//alert(retValue);
if (typeof retValue !== 'undefined' && retValue == "ok")
if (attr != "AnzahlFotos" && attr != "PZM-Modus" && attr != "Routenberechnung" && attr.indexOf("_eap") == -1) {
document.statistic.error_text.value = "<b>Das Attribut [" + attr + "] wurde bei " + cs_eid + (val == 1 ? " gesetzt.</b>" : " gelöscht.</b>");
} else {
if (attr.indexOf("_eap") != -1 && attr.indexOf("%C3%BC") != -1) {
document.statistic.error_text.value = "<b>Die Änderung wurde gespeichert.</b>";
} else {
document.statistic.error_text.value = "<b>Das Attribut [" + attr + "] wurde" + (attr == "AnzahlFotos" ? " bei " + cs_eid : "") + " auf (" + val + ") geändert.</b>";
}
}
else
document.statistic.error_text.value = "<b>Ein unbekannter Fehler ist aufgetreten.</b>";
mySubmit();
}
function myFunction() {
console.log('onsubmit');
document.getElementById('loader_inactive').style.display = 'none';
document.getElementById('loader_active').style.display = 'inline';
}
function mySubmit() {
myFunction();
document.statistic.submit();
}
-->
</script>
</head>
<body>
<table border="0" width="100%" cellspacing="0" cellpadding="0" vspace="0" hspace="0">
<tr>
<td>
&nbsp;
</td>
<td align="center">
<form name="statistic" action="statistic_finishment.php" method="post" onsubmit="myFunction()">
<div class="f10bp1_blue">
<table border="0" cellspacing="0" cellpadding="0" vspace="0" hspace="0">
<tr>
<td align="center"> <div class="f12bp1_blue">
<br>
<?php if ($hq_id != "3" && $hq_id != "2") echo "<!--"; ?>
<input type="radio" name="f_what[]" value="1"<?php if ($f_what == "1") echo " checked"; ?> onclick="if (document.statistic.f_cs_eid !== undefined) document.statistic.f_cs_eid.value='';mySubmit()">Erledigungsstatistik
<input type="radio" name="f_what[]" value="2"<?php if ($f_what == "2") echo " checked"; ?> onclick="if (document.statistic.f_cs_eid !== undefined) document.statistic.f_cs_eid.value='';mySubmit()">Servicezeiten
<input type="radio" name="f_what[]" value="5"<?php if ($f_what == "5") echo " checked"; ?> onclick="if (document.statistic.f_cs_eid !== undefined) document.statistic.f_cs_eid.value='';mySubmit()">Ziel-PLZs
<input type="radio" name="f_what[]" value="6"<?php if ($f_what == "6") echo " checked"; ?> onclick="if (document.statistic.f_cs_eid !== undefined) document.statistic.f_cs_eid.value='';mySubmit()">Ziel-PLZs und -Orte
<input type="radio" name="f_what[]" value="7"<?php if ($f_what == "7") echo " checked"; ?> onclick="if (document.statistic.f_cs_eid !== undefined) document.statistic.f_cs_eid.value='';mySubmit()">Preise
<input type="radio" name="f_what[]" value="8"<?php if ($f_what == "8") echo " checked"; ?> onclick="if (document.statistic.f_cs_eid !== undefined) document.statistic.f_cs_eid.value='';mySubmit()">getnow<br>
<?php if ($hq_id != "3" && $hq_id != "2") echo "-->"; ?>
<input type="radio" name="f_what[]" value="18"<?php if ($f_what == "18") echo " checked"; ?> onclick="mySubmit()">PZM-Zonen
<?php if (!in_array($usr_id, array(100001,200001,300002,400001,500001,600001,700001,800001,826762))) echo "<!--"; ?>
<input type="radio" name="f_what[]" value="19"<?php if ($f_what == "19") echo " checked"; ?> onclick="if (document.statistic.f_cs_eid !== undefined) document.statistic.f_cs_eid.value='';mySubmit()">PZM-Preisvergleich
<?php if (!in_array($usr_id, array(100001,200001,300002,400001,500001,600001,700001,800001,826762))) echo "-->"; ?>
<input type="radio" name="f_what[]" value="3"<?php if ($f_what == "3") echo " checked"; ?> onclick="mySubmit()">Kundenmasken-Erweiterungen
<input type="radio" name="f_what[]" value="4"<?php if ($f_what == "4") echo " checked"; ?> onclick="mySubmit()">Kundenzugang-Listenerweiterungen
<input type="radio" name="f_what[]" value="11"<?php if ($f_what == "11") echo " checked"; ?> onclick="mySubmit()">Auslandsrechnungen
<!--
<input type="radio" name="f_what[]" value="10"<?php if ($f_what == "10") echo " checked"; ?> onclick="mySubmit()">Fotos
-->
<input type="radio" name="f_what[]" value="9"<?php if ($f_what == "9") echo " checked"; ?> onclick="mySubmit()">Transporteursliste
<?php if ($inv_print_srvprice != 0) echo "<!--"; ?>
<input type="radio" name="f_what[]" value="12"<?php if ($f_what == "12") echo " checked"; ?> onclick="mySubmit()">Servicepreis-Ausweis
<?php if ($inv_print_srvprice != 0) echo "-->"; ?>
<input type="radio" name="f_what[]" value="13"<?php if ($f_what == "13") echo " checked"; ?> onclick="mySubmit()">Mail-Verteiler
<input type="radio" name="f_what[]" value="14"<?php if ($f_what == "14") echo " checked"; ?> onclick="mySubmit()">Sonderkonditionen
<input type="radio" name="f_what[]" value="17"<?php if ($f_what == "17") echo " checked"; ?> onclick="mySubmit()">Kunden-Konserven
<input type="radio" name="f_what[]" value="15"<?php if ($f_what == "15") echo " checked"; ?> onclick="if (document.statistic.f_cs_eid !== undefined) document.statistic.f_cs_eid.value='';mySubmit()">Konservennutzung
<input type="radio" name="f_what[]" value="16"<?php if ($f_what == "16") echo " checked"; ?> onclick="mySubmit()">Auftragsherkunft<br>
<br>
<br></div>
<input type="hidden" name="error_text" value="">
<?php if ($f_what == "3" || $f_what == "4" || $f_what == "9" || $f_what == "11" || ($hq_id != "3" && $hq_id != "2" && $f_what != "10" && $f_what != "12" && $f_what != "15") || $f_what == "13" || $f_what == "14" || $f_what == "16" || $f_what == "17" || $f_what == "18") echo "<!--"; ?>
Kundennummer (Format <i>STB999999</i>):
<input name="f_cs_eid" type="text" size="9" maxlength="10" class="input"
value=<?php echo ($f_cs_eid != "" ? "\"" . $f_cs_eid . "\"" : "\"\"") ?>>&nbsp;
<?php if ($f_what == "3" || $f_what == "4" || $f_what == "9" || $f_what == "11"|| ($hq_id != "3" && $hq_id != "2" && $f_what != "10" && $f_what != "12" && $f_what != "15") || $f_what == "13" || $f_what == "14" || $f_what == "16" || $f_what == "17" || $f_what == "18") echo "-->"; ?>
<?php if ($f_what != "10") echo "<!--"; ?>
<input type="radio" name="f_fototype[]" value="1" checked>Beweisfoto Zentralenerfassung
<input type="radio" name="f_fototype[]" value="2">Beweisfoto Kundenerfassung
&nbsp;
<input type="radio" name="f_fotocount[]" value="1" checked>Kann-Foto
<input type="radio" name="f_fotocount[]" value="2">Muss-Foto&nbsp;
<?php if ($f_what != "10") echo "--!>"; ?>
<?php if ($f_what == "3" || $f_what == "4" || $f_what == "9" || $f_what == "10" || $f_what == "11" || ($hq_id != "3" && $hq_id != "2" && $f_what != "15" && $f_what != "16" && $f_what != "19") || $f_what == "13" || $f_what == "14" || $f_what == "17" || $f_what == "18") echo "<!--"; ?>
Erledigungsdatum von:&nbsp;
<input name="f_min_date" type="text" size="9" maxlength="10" class="input"
value="<?php echo ($f_min_date != "" ? formDate($f_min_date) : "") ?>">&nbsp;
bis:&nbsp;
<input name="f_max_date" type="text" size="9" maxlength="10" class="input"
value="<?php echo ($f_max_date != "" ? formDate($f_max_date) : "") ?>">&nbsp;
<input type="submit" name="formsubmit" size="2" value="Anzeigen" class="submit">
<?php if ($f_what == "3" || $f_what == "4" || $f_what == "9" || $f_what == "10" || $f_what == "11" || ($hq_id != "3" && $hq_id != "2" && $f_what != "15" && $f_what != "16" && $f_what != "19") || $f_what == "13" || $f_what == "14" || $f_what == "17" || $f_what == "18") echo "-->"; ?>
<?php if ($f_what != "10" && $f_what != "12") echo "<!--"; ?>
<input type="submit" name="formsubmit" size="2" value="Hinzufügen" class="submit">
<?php if ($f_what != "10" && $f_what != "12") echo "--!>"; ?>
<?php if ($f_what != "15") echo "<!--"; ?>
<br><br><b>ACHTUNG: Daten zur Konservennutzung stehen erst ab dem 23.02.2024 zur Verfügung!</b>
<?php if ($f_what != "15") echo "--!>"; ?>
<?php if ($f_what != "18") echo "<!--"; ?>
<span style="color:red;font-size:17"><b>ACHTUNG: HIER DURCHGEFÜHRTE ÄNDERUNGEN WERDEN SOFORT WIRKSAM UND PREISRELEVANT!</b></span>
<?php if ($f_what != "18") echo "--!>"; ?>
<?php if ($f_what != "19") echo "<!--"; ?>
<br><br><b>ACHTUNG: Die Aufträge selbst werden vom Live-System gezogen, die Einstellungen für die Km-Berechnung und PZM-Servicepreise hingegen vom Testsystem!</b>
<?php if ($f_what != "19") echo "--!>"; ?>
</td>
</tr>
<tr>
<td align="center">
<div id="loader_active" style="display:none">
<img src="../images/ajax-loader.gif" height="16" width="16" border="0">
</div>
<div id="loader_inactive" style="display:inline">
<img src="../images/empty.gif" height="16" width="16" border="0">
</div><br>
<span style="color:red"> <?php echo $error_text; ?> </span>
<?php echo $content; ?>
</td>
</tr>
</table>
</form>
</td>
<td>
&nbsp;
</td>
</tr>
</table>
</body>
</html>
Reference in New Issue View Git Blame Copy Permalink