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

1343 lines
82 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.
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
<?php
/*=======================================================================
*
* cron_export.centiro.php
*
* Autor: Carsten Annacker
*
=======================================================================*/
$hq_id = 0;
if ($argv[1] == "1111" || $_GET["hq_id"] == "1111") {
$hq_id = 1111;
} elseif ($argv[1] == "1020" || $_GET["hq_id"] == "1020" || $argv[1] == "HL" || $_GET["hq_id"] == "HL") {
$hq_id = 1020;
}
if ($hq_id == 0) {
echo "hq_id is missing\n"; die();
}
$test = true;
$no_web = true;
if ($_GET["hq_id"] != "")
$no_web = false;
if ($argv[2] == "acapella7890") {
$test = false;
$no_web = true;
}
if ($argv[1] == "1020") {
$hq_name = "HH";
} elseif ($argv[1] == "1111") {
$hq_name = "Süd";
}
if ($argv[1] == "HL")
$hq_name = "HL";
error_reporting(E_ALL ^ (E_DEPRECATED | E_NOTICE));
//if ($test)
// include_once ("../include/inc_mtf_func.inc.test.php");
//else
include_once ("../include/inc_mtf_func.inc.php");
include_once ("../include/ftp.inc.php");
$ftp_server = $db->getOne("SELECT par_value FROM parameter WHERE par_key = 'FTP_SERVER' AND hq_id = 1020");
$ftp_user_name = $db->getOne("SELECT par_value FROM parameter WHERE par_key = 'FTP_USER' AND hq_id = 1020");
$ftp_user_pass = $db->getOne("SELECT par_value FROM parameter WHERE par_key = 'FTP_PASSWORD' AND hq_id = 1020");
$remotePath = "/stadtbote/";
define ("VAT", 0.19);
$temp_csv_folder = "../temp/edifact/";
if ($test)
$export_dir = $temp_csv_folder;
else
$export_dir = "../export/download/";
$export_filenames = array(); // "201504011256.144.abr"
$export_filenames_count = 0;
$serviceAust = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'AT'");
$to___date_sql = date("Y-m-d 23:59:59", mktime(0, 0, 0, date("m"), date("d") - 2 - (date("w") - 1), date("Y")));
$to___date_sql_next = date("Y-m-d 23:59:59", mktime(0, 0, 0, date("m"), date("d") - 2 - (date("w") - 1) + 7, date("Y")));
//echo $to___date_sql . "<br>\n";
//echo $to___date_sql_next . "<br>\n";
//echo "((((" . substr($to___date_sql, 5, 2) . " != " . substr($to___date_sql_next, 5, 2) . ") && (" . substr($to___date_sql, 5, 2) . " != " . date("m") . ") && " . substr($to___date_sql, 8, 2) . " != '01' && " . date('D') . " != 'Mon') || " . substr($to___date_sql, 8, 2) . " == '01'))" . "<br>\n";
if ((((substr($to___date_sql, 5, 2) != substr($to___date_sql_next, 5, 2)) && (substr($to___date_sql, 5, 2) != date("m")) && substr($to___date_sql, 8, 2) != "01" && date("D") != "Mon") || substr($to___date_sql, 8, 2) == "01") /* letzter Sonnabend war der 01. des Monats */)
{
// wenn ein Monatswechsel stattgefunden hat, dann nur bis zum letzten Tag des vorigen Monats abrechnen
$to___date_sql = date("Y-m-d 23:59:59", mktime(0, 0, 0, date("m"), 0, date("Y")));
}
//echo $to___date_sql . "\n"; die();
$log_file_name = $temp_csv_folder . "_cron_export.centiro_" . $hq_name . "_" . substr($to___date_sql, 0, 10) . ".log";
write_file($log_file_name, "", 'w');
$no_export_file_name = $temp_csv_folder . "Kein_Export_" . $hq_name . "_" . substr($to___date_sql, 0, 10) . ".csv";
write_file($no_export_file_name, "Auftrag;Typ;Order-Nr;Eingang;Kunde;Termin;Fahrer;Nettopreis;Nettofuhrlohn;Bemerkung\n", 'w');
$no_cr_price_file_name = $temp_csv_folder . "Kein_Fuhrlohn_" . $hq_name . "_" . substr($to___date_sql, 0, 10) . ".csv";
write_file($no_cr_price_file_name, "Auftrag;Typ;Order-Nr;Eingang;Kunde;Termin;Fahrer;Nettopreis;Nettofuhrlohn;Bemerkung\n", 'w');
if ($hq_id == 1111):
for ($i = 0; $i < 6; $i++) {
// Start filenames starting from 20 minutes minimum (i.e. not from 0) in order no to interfere with the filenames from the fuhrlohn-export (see do_cron_export_HTM);
// fuhrlohn-export-files always start from 0 minutes!
$export_filenames[] = date("YmdHi", mktime(date("H"), date("i") + (date("i") < 20 ? 20 : 0) + $i, 0, date("m"), date("d"), date("Y"))) . ".144.abr";
}
$csv_txtArrArrArr['WAST10224'] = do_LCD_OS(3512);
$csv_txtArrArrArr['WAST10225'] = do_LCD_OS(3506);
else:
for ($i = 0; $i < 9; $i++) {
$export_filenames[] = date("YmdHi", mktime(date("H"), date("i") + (date("i") < 20 ? 20 : 0) + $i, 0, date("m"), date("d"), date("Y"))) . ".101.abr";
}
// $csv_txtArrArrArr['HTHH71146'] = do_LCD_OS("1"); /*nur Einzelabrechnung*/
// $csv_txtArrArrArr['HTHH71325'] = do_LCD_OS("2"); /*nur Einzelabrechnung*/
// $csv_txtArrArrArr['HTHH71245'] = do_LCD_OS("17051"); /*nur Einzelabrechnung*/
// $csv_txtArrArrArr['HTHH72289'] = do_LCD_OS("250226"); /*nur Einzelabrechnung*/
if ($hq_name == "HH")
$csv_txtArrArrArr['HTHH71245'] = do_LCD_OS("1,2,17051"); /*nur Gesamtabrechnung*/
if ($hq_name == "HL")
$csv_txtArrArrArr['HTHH72289'] = do_LCD_OS("250226"); /*Einzelabrechnung Lübeck*/
endif;
//print_r($csv_txtArrArrArr);
$content = "";
$last_jbpc_id = 0;
foreach($csv_txtArrArrArr as $debitor => $csv_txtArrArr) { // Ludwigsburg, Sindelfingen
foreach($csv_txtArrArr as $csv_type => $csv_txtArr) {
if ($csv_type != "Inkasso" && $csv_type != "InkassoSpezial" && $csv_type != "Subvention") {
$jbpc_id = do_export($debitor, $csv_txtArr[2], $csv_txtArr[3], $csv_txtArr[4], $csv_txtArr[5]);
$jbpc_ids .= $jbpc_id . ",";
$last_jbpc_id = $jbpc_id;
myWriteLog($csv_txtArr[4] . $csv_txtArr[5]);
// wenn OS happy, jbpc_id in Inkasso-Liste eintragen
if ($csv_txtArr[4] . $csv_txtArr[5] == 'OSHappy') {
$csv_txtArrArr["Inkasso"][1] =
str_replace('|jbpc_id|', $jbpc_id, $csv_txtArrArr["Inkasso"][1]);
// $csv_txtArrArrArr[$debitor]["Inkasso"][1] = $csv_txtArrArr["Inkasso"][1];
myWriteLog("writing " . $csv_txtArrArr["Inkasso"][0]);
write_file($csv_txtArrArr["Inkasso"][0], $csv_txtArrArr["Inkasso"][1], 'w');
$csv_txtArrArr["InkassoSpezial"][1] =
str_replace('|jbpc_id|', $jbpc_id, $csv_txtArrArr["InkassoSpezial"][1]);
// $csv_txtArrArrArr[$debitor]["InkassoSpezial"][1] = $csv_txtArrArr["InkassoSpezial"][1];
myWriteLog("writing " . $csv_txtArrArr["InkassoSpezial"][0]);
write_file($csv_txtArrArr["InkassoSpezial"][0], $csv_txtArrArr["InkassoSpezial"][1], 'w');
myWriteLog("writing " . $csv_txtArrArr["Subvention"][0]);
write_file($csv_txtArrArr["Subvention"][0], $csv_txtArrArr["Subvention"][1], 'w');
if (!$test) {
// für den Fuhrlohnexport wieder die originale Zahlart einsetzen
// (wg. Kennzeichen "BZ" und Inkasso-Betrag)
// mode = 19 (OSsad) nicht updaten, weil SAMSe
$sql_query =
"UPDATE jobpayment SET jbp_mode = jbp_tan, jbp_tan = '18' WHERE jbp_mode = 18 AND jbpc_id = " . $jbpc_id;
mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
}
}
foreach($csv_txtArr[1] as $key => $txt) {
$txt = str_replace('|jbpc_id|', $jbpc_id, $txt);
myWriteLog("writing " . str_replace($temp_csv_folder, $temp_csv_folder . $key . "_", $csv_txtArr[0]));
write_file(str_replace($temp_csv_folder, $temp_csv_folder . $key . "_", $csv_txtArr[0]), $txt, 'w');
}
// myWriteLog("writing " . $csv_txtArr[0]);
// write_file($csv_txtArr[0], $csv_txtArr[1], 'w');
// Die Rechnung lautet zwar nur auf eine Kundennummer, aber die Märkte sollen in einzelnen Tabellen aufgeteilt sein
// exec("grep \";146 ;\" " . $csv_txtArr[0] . " > " . str_replace($temp_csv_folder, $temp_csv_folder . "146_", $csv_txtArr[0]));
// exec("grep \";325 ;\" " . $csv_txtArr[0] . " > " . str_replace($temp_csv_folder, $temp_csv_folder . "325_", $csv_txtArr[0]));
// exec("grep \";245 ;\" " . $csv_txtArr[0] . " > " . str_replace($temp_csv_folder, $temp_csv_folder . "245_", $csv_txtArr[0]));
}
$csv_txtArr[1] = /* $csv_txtArr[0] . */ "<table border=1 cellspacing=0 cellpadding=4 vspace=0 hspace=0>\n<tr><td>" .
str_replace("<td align=left></td>", "<td align=left>&nbsp;</td>", str_replace("\n", "</td></tr>\n<tr><td>", str_replace(";", "</td><td align=left>", $csv_txtArr[1]))) .
"\n</table><br><br>\n";
$csv_txtArr[1] = str_replace("<tr><td>\n", "", str_replace("<td></td>", "<td>&nbsp;</td>", $csv_txtArr[1]));
$content .= $csv_txtArr[1];
// $content .= str_replace(">#aa", " bgcolor=\"#DDDDDD\">", str_replace(">#ab", " bgcolor=\"#EEEEEE\">", str_replace(">#ba", " bgcolor=\"#BBBBFF\">", str_replace(">#bb", " bgcolor=\"#CCCCFF\">",
// str_replace(">#cc", " bgcolor=\"#DDBBBB\">", str_replace(">#cd", " bgcolor=\"#DDCCCC\">", str_replace(">#dc", " bgcolor=\"#FF9999\">", str_replace(">#dd", " bgcolor=\"#FFAAAA\">",
// $csv_txt))))))));
}
//print_r($csv_txtArrArr);
$xls_file_name = substr($csv_txtArrArr["Inkasso"][0], 0, -4) . ".xls";
$cmd = "../tools/mycsv2xls.pl " . $csv_txtArrArr["Inkasso"][0] . " 'inkasso' " . $xls_file_name . " >> " . $log_file_name . " 2>&1 &";
myWriteLog($cmd);
@unlink($xls_file_name);
$ausgabe = exec($cmd);
do {
$output = array();
//myWriteLog("ps -fA | grep $xls_file_name");
exec("ps -fA | grep $xls_file_name", $output);
for ($i = 0; $i < count($output); $i++):
//myWriteLog($output[$i]);
$pos = strpos ($output[$i], "mycsv2xls.pl");
if (!($pos === false))
break;
endfor;
usleep(100000);
} while (!($pos === false) && count($output) > 0);
@unlink($csv_txtArrArr["Inkasso"][0]);
send_mail("Abrechnung " . $hq_id, "[" . date("Y-m-d H:i:s") . "] " . "Abrechnung ist durch\n");
}
if (!$test) {
if ($hq_id != 1111) {
$jbpc_ids = substr($jbpc_ids, 0, -1);
$sum_ht = array(
1 => 0,
2 => 0,
17051 => 0,
250226 => 0
);
$sql_query =
"SELECT jbpc_id, csc_id_related, gdc_context" .
" FROM genericdatacontainer, job, jobpayment" .
" WHERE gdc_gen_fieldname = 'price_ht' AND LEFT(gdc_context, 6) =" .
" job.jb_id AND job.jb_id = jobpayment.jb_id" .
" AND jbpc_id IN (". $jbpc_ids . ")";
myWriteLog($sql_query . "\n");
myWriteLog("jbpc_id\tcsc_id_related\tgdc_context[2]");
$res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
$tmpArr = explode("|", $row["gdc_context"]);
myWriteLog($row["jbpc_id"] . "\t" . $row["csc_id_related"] . "\t" . $tmpArr[2]);
$sum_ht[$row["csc_id_related"]] += $tmpArr[2];
endwhile;
$sql_query =
"SELECT jbpc_id AS Sammelnummer, csc_id_related AS csc_id_related_real," .
" (CASE csc_id_related WHEN 1 THEN 'Schnelsen' WHEN 2 THEN 'Moorfleet' WHEN 17051 THEN 'Altona' WHEN 250226 THEN 'Lübeck' ELSE 'undefiniert' END) AS Markt," .
" SUM(ROUND(jb_totalprice, 2)) AS Rechnungssumme, CONCAT(REPLACE(SUM(ROUND(jb_cr_price, 2)), '.', ','), ' €') AS Fuhrlohnsumme" .
" FROM job, jobpayment" .
" WHERE job.jb_id = jobpayment.jb_id AND jbpc_id IN (" . $jbpc_ids . ")" .
" GROUP BY jbpc_id, csc_id_related";
myWriteLog($sql_query . "\n");
$jbpc_ids_file = $temp_csv_folder . "Gesamtaufstellung_" . $hq_name . "_" . substr($to___date_sql, 0, 10) . ".csv";
write_file($jbpc_ids_file, "Sammelnummer;Markt;Rechnungssumme;Fuhrlohnsumme\n", 'w');
$res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
$tmp_cnt = 1;
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
$sum_ht_tmp = 0;
if ($row["Sammelnummer"] == $last_jbpc_id)
$sum_ht_tmp = $sum_ht[$row["csc_id_related_real"]];
write_file($jbpc_ids_file, $row["Sammelnummer"] . ";" . $row["Markt"] . ";" . str_replace(".", ",", sprintf("%01.2f", ($row["Rechnungssumme"] + $sum_ht_tmp))) . " €;" . $row["Fuhrlohnsumme"] . "\n", "a");
endwhile;
} else {
echo "honk1\n";
exec("/root/votian_admin/do_cron_export_HTM");
echo "honk2\n";
exec("/root/votian_admin/do_cron_FTP_upload_HTM");
}
}
function do_export($debitor, $jb_ids, $sum_total, $lcd_or_os, $happy_or_sad)
{
global $db, $test, $no_web, $export_dir, $export_filenames, $export_filenames_count, $to___date_sql,
$ftp_server, $ftp_user_name, $ftp_user_pass, $remotePath;
// $store_no = array( /*nur Einzelabrechnung*/
// "WAST10224" => "224", // SiFi /*nur Einzelabrechnung*/
// "WAST10225" => "225", // LuBu /*nur Einzelabrechnung*/
// "HTHH71146" => "146", //– Schnelsen /*nur Einzelabrechnung*/
// "HTHH71325" => "325", //– Moorfleet /*nur Einzelabrechnung*/
// "HTHH71245" => "245", //– Hamburg Altona /*nur Einzelabrechnung*/
// "HTHH72289" => "289" // Lübeck /*nur Einzelabrechnung*/
// ); /*nur Einzelabrechnung*/
$ht_no = array(
"WAST10224" => "144",
"WAST10225" => "144", // LuBu
"HTHH71146" => "101", //– Schnelsen
"HTHH71325" => "101", //– Moorfleet
"HTHH71245" => "101", //– Hamburg Altona
"HTHH72289" => "101", //– Lübeck
"HTHH71245" => "101" /*nur Gesamtabrechnung*/
);
$rz = array(
"WAST10224" => "RZ",
"WAST10225" => "RZ",
"HTHH71146" => " ",
"HTHH71325" => " ",
"HTHH71245" => " ",
"HTHH72289" => " ",
"HTHH71245" => " " /*nur Gesamtabrechnung*/
);
$payment_types = array(
"LCD" => array(
"Happy" => $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'payment_type' AND mt_value = 'LCD happy'"),
"Sad" => $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'payment_type' AND mt_value = 'LCD sad'")),
"OS" => array(
"Happy" => $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'payment_type' AND mt_value = 'OS happy'"),
"Sad" => $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'payment_type' AND mt_value = 'OS happy'")
)
);
if (!$test) {
// $sql_query = "SHOW TABLE STATUS LIKE 'jobpaymentcollection'";
// $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
// $row = mysql_fetch_array($res);
// $jbpc_id = $row['Auto_increment'];
// mysql_free_result($res);
// $sql_query = "ALTER TABLE jobpaymentcollection AUTO_INCREMENT=" . ($jbpc_id + 1);
// mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
$cs_id = $db->getOne("SELECT cs_id FROM customer WHERE cs_eid = '" . $debitor . "'");
$sql_query =
"INSERT INTO jobpaymentcollection (cs_id, jbpc_mode, jbpc_price, jbpc_bookingdate, jbpc_export_time, jbpc_invtext)" .
" VALUES (" . $cs_id . ", " . $payment_types[$lcd_or_os][$happy_or_sad] . ", " . $sum_total . ", '" . substr($to___date_sql, 0, 10) . "', '" . date("Y-m-d H:i:s") . "', '')";
mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
$jbpc_id = mysql_insert_id();
$sql_query = "UPDATE jobpayment SET jbpc_id = " . $jbpc_id . ", jbp_export_time = '" . date("Y-m-d H:i:s") . "' WHERE jb_id IN (" . substr($jb_ids, 1) . ")";
mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
} else {
$jbpc_id = "test";
}
if ($no_web):
$add_text_start = ($lcd_or_os == "LCD" && $happy_or_sad == "Happy" ? "Andrei Disterhof" : "Andrei Disterhof"); //29.06.2017 Andrea Seegräber wurde bei den LCDs durch Andrei Disterhof ersetzt
myWriteLog($export_dir . $export_filenames[$export_filenames_count] . ", \$jbpc_id = " . $jbpc_id . ", \$jb_ids = " . substr($jb_ids, 1));
write_file($export_dir . $export_filenames[$export_filenames_count],
$ht_no[$debitor] .
" |M3|0000099999| 666|IKEA Abr|" .
str_replace('-', '', substr($to___date_sql, 0, 10)) .
"|00:00|" .
str_replace('-', '', substr($to___date_sql, 0, 10)) .
"|00:00| 666|0" .
$jbpc_id .
"|000| | |FP||| |" . $rz[$debitor] . "|ER||||" .
str_pad($sum_total * 100, 10, "0", STR_PAD_LEFT) .
// str_pad($db->getOne("SELECT ROUND(SUM(ROUND(jbp_price, 2) * 100)) FROM jobpayment WHERE jb_id IN (" . substr($jb_ids, 1) . ")"), 10, "0", STR_PAD_LEFT) .
"|0000000|0000000|0000000|0000000|0000000|0000000000|0000000000|0000000000|0000000000|0000000000||||||00000|00000|||||||0000|0000|00|0000|00000000|00000000|00000000|00000000|00000000||||||0" .
$debitor .
"|00000000000000|0" .
$debitor .
($lcd_or_os != "OS" ?
"|||" . $add_text_start . "|Wir berechnen Ihnen für die " . $lcd_or_os . " Aufträge im " . $happy_or_sad . " Flow gemäß|beiliegender Anlage bis einschließlich " . formDate($to___date_sql)
:
// "|||" . $add_text_start . "|Wir berechnen Ihnen für die OS Aufträge EH " . $store_no[$debitor] . " gemäß|beiliegender Anlage bis einschließlich " . formDate($to___date_sql) /*nur Einzelabrechnung*/
"|||" . $add_text_start . "|Wir berechnen Ihnen für die OS Aufträge gemäß beiliegender|Anlage bis einschließlich " . formDate($to___date_sql) /*nur Gesamtabrechnung*/
) .
"||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||0000|0000000|0000|0000000|0000000|0000|0000000||0000|0000000||0000|0000000||0000|0000000||0000|0000000|\n",
"w");
if (!$test) {
$opArray = ftpUpload($export_filenames[$export_filenames_count], $export_filenames[$export_filenames_count], $ftp_server, $ftp_user_name, $ftp_user_pass, $export_dir, $remotePath, "0");
myWriteLog("result of uploading '" . $export_filenames[$export_filenames_count] . "': errNo '" . $opArray[0] . "', errDesc = '" . $opArray[1] . "'");
}
$export_filenames_count++;
endif;
return $jbpc_id;
}
function do_LCD_OS($csc_id_related)
{
global $db, $temp_csv_folder, $to___date_sql, $test, $hq_id, $hq_name, $no_export_file_name, $no_cr_price_file_name;
/*
Tobedone:
- manual events berücksichtigen
- Zusatzmontagen berücksichtigen in der Extra-Liste (jb_id_addmont)
- REMOVAL_WHITEGOODS fehlen noch
//UPDATE jobpayment SET jbp_mode = jbp_tan, jbp_tan = '16' WHERE jbp_mode = '16' AND jbp_tan != '' AND jbp_export_time IS NULL;
//UPDATE jobpayment SET jbp_mode = jbp_tan, jbp_tan = '17' WHERE jbp_mode = '17' AND jbp_tan != '' AND jbp_export_time IS NULL;
UPDATE jobpayment SET jbp_mode = jbp_tan, jbp_tan = '18' WHERE jbpc_id IN (81002462,81002466)
//UPDATE jobpayment SET jbp_mode = jbp_tan, jbp_tan = '19' WHERE jbp_mode = '19' AND jbp_tan != '' AND jbp_export_time IS NULL;
Zu klären:
Doppelte Order-nr. für Lieferung und Abholung wenn Austausch
Fallnummer bei Montagen ist wahrscheinlich trat_serialno in tourarticle, könne aber mehrer pro Auftrag sein
Sofamontage ist bei Montage nicht bei Lieferungen
Zweitanlieferung
*/
// LCD Happy: erfolgreiche Lieferungen
// LCD Sad: SAMS-LCDs und gescheiterte Lieferungen wenn "FAIL IKEA"
// OS Happy: erfolgreiche SAMS-OS's und Möbel-/Küchenmontagen
// OS Sad: SAMS-Nachmontagen
//Lieferungen + Montagen für HF und SF -> Lubu = WAST10225
//Lieferungen + Montagen für HF und SF -> Sifi = WAST10224
$accounting_cs_eids = array(
"3512" => "WAST10224", // SiFi
"3506" => "WAST10225", // LuBu
"1" => "HTHH71146",
"2" => "HTHH71325",
"17051" => "HTHH71245",
"250226" => "HTHH72289",
"1,2,17051" => "HTHH71245" /*nur Gesamtabrechnung*/
);
$store_no = array(
"3512" => "224",
"3506" => "225",
"1" => "146",
"2" => "325",
"17051" => "245",
"250226" => "289",
"1,2,17051" => "" /*nur Gesamtabrechnung*/
);
$ht_no = array(
"3512" => "144",
"3506" => "144", // LuBu
"1" => "101", //– Schnelsen
"2" => "101", //– Moorfleet
"17051" => "101", //– Hamburg Altona
"250226" => "101", //- Lübeck
"1,2,17051" => "101" /*nur Gesamtabrechnung*/
);
$lsc_no = array(
3512 => "",
3506 => "",
1 => chr(160) . "053",
2 => chr(160) . "053",
17051 => chr(160) . "053",
250226 => chr(160) . "053"
);
$inkasso_file_name = $temp_csv_folder . "Inkasso_IKEA_" . $hq_name . "_" . substr($to___date_sql, 0, 10) . ".csv";
$inkassospezial_file_name = $temp_csv_folder . "Inkasso_IKEA_" . $hq_name . "_" . substr($to___date_sql, 0, 10) . "(1).csv";
$subventions_file_name = $temp_csv_folder . "Subventionen_Küche_" . $hq_name . "_" . substr($to___date_sql, 0, 10) . ".csv";
if ($hq_id == 1020)
$subventions_file_name = $temp_csv_folder . "Rückvergütungen_Küche_" . $hq_name . "_" . substr($to___date_sql, 0, 10) . ".csv";
$lcd_or_osArr = array("LCD", "OS");
$happy_or_sadArr = array("Happy", "Sad");
$areas = array(
"Core" => "1",
"Outside" => "4",
"Potential-NO" => "3",
"Potential-NW" => "3",
"Potential-SO" => "3",
"Potential-SW" => "3",
// "Core" => "1",
"Outside I" => "4",
"Outside II" => "4",
"Outside III" => "4",
"Outside IV" => "4",
"Insel Sylt" => "4"
);
$zones = array(
"Core" => "1",
"Outside" => "2",
"Potential-NO" => "3",
"Potential-NW" => "4",
"Potential-SO" => "5",
"Potential-SW" => "6",
// "Core" => "1",
"Outside I" => "2",
"Outside II" => "3",
"Outside III" => "4",
"Outside IV" => "5",
"Insel Sylt" => "6"
);
// $serviceLief = pow(2, $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'LI'"));
$serviceMont = pow(2, $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'MO'"));
// $serviceNeuMont = pow(2, $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'NM'"));
// $serviceKueMont = pow(2, $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'KU'"));
$serviceAbhol = pow(2, $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'AB'"));
$serviceAust = pow(2, $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'AT'"));
define ("FUNCPAR_LAGERK", "0003:par_01"); // "Lagerungskosten"
define ("FUNCPAR_POLST", "0006:par_01:1000"); // "IKEA:&nbsp;Polster&nbsp;pro&nbsp;Sitzfläche"
define ("FUNCPAR_MATR", "0006:par_01:1005"); // "IKEA:&nbsp;Matratze&nbsp;pro&nbsp;Stück"
define ("FUNCPAR_SOFA", "ident_0027:par_01"); // "Sofa-Montage"
// define ("FUNCPAR_SOEDER", "ident_0012:par_01"); // "Montage&nbsp;Söderhamn"
define ("FUNCPAR_MONT", "0004:par_01"); // Warenwertabhängiger "Montagepreis"
define ("FUNCPAR_MONTGRUND", "ident_0009:par_01"); // "Grundpauschale&nbsp;Montage"
define ("FUNCPAR_KUELFDMET", "0010:par_01"); // "lfd. Meter"
define ("FUNCPAR_KUEELEKTR", "ident_0005:par_01"); // "Elektroanschluss"
// define ("FUNCPAR_KUEWASSER", "ident_0006:par_01"); // "Wasseranschluss"
define ("FUNCPAR_INSTTV", "ident_0019:par_01"); // "Installation TV-Gerät"
define ("FUNCPAR_INSTLAUT", "ident_0020:par_01"); // "Installation Lautsprecher"
define ("FUNCPAR_WASSER", "ident_0011:par_01"); // "Wasseranschluss&nbsp;Bad"
define ("FUNCPAR_ENTSELEK", "ident_0021:par_01"); // "Entsorgung Elektrogerät"
define ("FUNCPAR_SONDER", "0000:par_01"); // Sondertransport (CARRY_HELP)
define ("FUNCPAR_ENTSMOEB", "0000:par_02"); // Altmöbelrückgabe
define ("FUNCPAR_SPLITTER", "0000:par_03"); // Splitterbox
define ("FUNCPAR_QUALITY", "0000:par_04"); // Begutachtung
$csv_text = array();
// $csv_text_end = array();
$to___date_timestamp = strtotime($to___date_sql);
$kw = (int)date('W', $to___date_timestamp);
$monat = date('n / Y', $to___date_timestamp);
$csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[0]]["146"] =
";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
" Anlage zur Rechnung IKEA;;;;;;Store;;;KW;;;Leistungsmonat;;;;;Rechnung;Rechnungsdatum;;;;;;;;;;;;;;;;\n" .
";;;;;;LCD;;;" . $kw . ";;;" . $monat . ";;;;; " . $ht_no[$csc_id_related] . "|jbpc_id|;" . formDate($to___date_sql) . ";;;;;;;;;;;;;;;;\n" .
";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
"Buchungsvorgabe;;;;;;;;;;;;5731 / 4071;5714 / 4071;;;5732 / 4071;5732 / 4071;5731 / 4071;;5732 / 4071;;5732 / 4071;;5731 / 4071;;;;;;;;;;\n" .
";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
"Basisdaten der Sendungen;;;;;;;;;;;;Basic Price;;;;Additional Service;;Zweitanl;;AV m. LV;;AV o. LV;;Lagergeld;;Sonderfahrten;;;;;GS Claims;;;\n" .
"S;DC;LSC;iSell Nr;MHS-Ordernr;Lieferdat;EH;Area;Zone;Flow;Warenwert;Gewicht;Hinsend;Shuttle;Same Day;Samstag;Abholung;Austausch;Anz;EUR;Anz;EUR;Anz;EUR;Tg;EUR;Anz;PLZ;Km;EUR;TR;Eur;Wert;KST;Wert ohne Shuttle\n";
$csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[0]]["325"] = $csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[0]]["146"];
$csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[0]]["245"] = $csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[0]]["146"];
$csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[0]]["289"] = $csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[0]]["146"];
// $csv_text_end[$lcd_or_osArr[0]][$happy_or_sadArr[0]] =
// ";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
// ";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
// ";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
// " Summen gesamt;;;;;;;;;;;;0,00 ;0,00 ;0,00 ;0,00 ;0,00 ;0,00 ;0;0;0;0;0;0;0;0;0;0;0;0;;0,00;;;;;\n" .
// ";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
// ";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
// " Gruppensumme LCD;;;;Hinsendungen;;;;;direct;;;=;0,00 ;0,00 ;0,00 ;0,00 ;0,00 ;;;;;;;;;0 ;;;0,00 ;;;;;;;\n" .
// " Gruppensumme LCD;;;;Hinsendungen;;;;;indirect;;;;;0,00 ;0,00 ;0,00 ;0,00 ;;;;;;;;;0 ;;;0,00 ;;;;;;;\n" .
// " Gruppensumme LCD;;;;Abholungen;0 ;;;;indirect;;0,00;0,00 ;0,00 ;0,00 ;0,00 ;0,00 ;0,00 ;0;0,00 ;0;0,00 ;0;0,00 ;0;0,00 ;0 ;;;0,00 ;;;;;;;\n" .
// " Gruppensumme LCD;;;;Austausch;0 ;;;;indirect;;0,00;0,00 ;0,00 ;0,00 ;0,00 ;0,00 ;0,00 ;0;0,00 ;0;0,00 ;0;0,00 ;0;0,00 ;0 ;;;0,00 ;;;;;;;\n";
$csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[1]]["146"] =
";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
" Anlage zur Rechnung IKEA;;;;;;Store;;;KW;;Leistungsmonat;;;;;Rechnung;;;;;;;Rechnungsdatum;;;;;;;Seite;;;;\n" .
" Detail: Versandkosten Sad Flow;;;;;;" . $store_no[$csc_id_related] . ";;;" . $kw . ";;" . $monat . ";;;;; " . $ht_no[$csc_id_related] . "|jbpc_id|;;;;;;;" . formDate($to___date_sql) . ";;;;;;;;;;;\n" .
";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
"Buchungsvorgabe;;;;;;;;;;;5731 / 4071;5714 / 4071;;;5732 / 4071;5732 / 4071;;;5731 / 4071;;;;5731 / 4071;;5732 / 4071;;5732 / 4071;;5731 / 4071;;;;;\n" .
";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
"Basisdaten der Sendungen;;;;;;;;;;;Basic Price;;Additional Service;;NN-Geb;;Polstermont;;Nachlieferung;;Zweitanl;;AV m. LV;;AV o. LV;;Lagergeld;;Sonderfahrten;;;;TrRaum;GS Claims\n" .
"S;DC;LSC;iSell Nr;MHS-Ordernr;Lieferdat;EH;Area;Zone;Flow;Gewicht;Hinsend;Shuttle;Abholung;Austausch;Anz;EUR;Anz;EUR;Anz;EUR;Anz;EUR;Anz;EUR;Anz;EUR;Tg;EUR;Anz;PLZ;Km;EUR;EUR;EUR\n";
$csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[1]]["325"] = $csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[1]]["146"];
$csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[1]]["245"] = $csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[1]]["146"];
$csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[1]]["289"] = $csv_text[$lcd_or_osArr[0]][$happy_or_sadArr[1]]["146"];
// $csv_text_end[$lcd_or_osArr[0]][$happy_or_sadArr[1]] =
// " Summen gesamt;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;";
$csv_text[$lcd_or_osArr[1]][$happy_or_sadArr[0]]["146"] =
// /*nur Einzelabrechnung*/
// ";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
// " Anlage zur Rechnung IKEA;;;;;;;;;;;;;;;Store;;;;KW;;;;Leistungsmonat;;;;;Rechnung;;;;;;;Rechnungsdatum;;;;;;;;;;;Seite ;;;;;;;;;;;;;;;;\n" .
// " Detail: Other Services;;;;;;;;;;;;;;;" . $store_no[$csc_id_related] . ";;;;" . $kw . ";;;;" . $monat . ";;;;; " . $ht_no[$csc_id_related] . "|jbpc_id|;;;;;;;" . formDate($to___date_sql) . ";;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
// ";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
// "Buchungsvorgabe;;;;;;;;;Forderung / 3511;;;;Forderung / 3511;;;Forderung / 3511;;;;Forderung / 3511;;Forderung / 3511;;Forderung / 3511;;;Forderung / 3511;;Forderung / 3511;;;;;;;;;;;;;;;3512 / 6150;;;;;;;;;;;;Forderung / 3511;;;;;;Forderung / 3511\n" .
// ";;;;;;;;;3512 / Forderung;;;;3512 / Forderung;;;3512 / Forderung;;;;3512 / Forderung;;3512 / Forderung;;3512 / Forderung;;;3512 / Forderung;;3512 / Forderung;;;;;;;;;;;;;;;;;;;;;;;;;;;3512 / Forderung;;;;;;3512 / Forderung\n" .
// ";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;\n" .
// "Basisdaten der Sendungen;;;;;;;;Ink.;Montage Möbel;;;;;Anschluß UPPLEVA;;;Küchen Komplettmont;;;;Bad (Wasser);;Anschl. E-Geräte;;Demontage;;;Aufmaß;;Arbeiten nach Stunden;;;;;Montage APL Holz;;;;;Montage APL Stein;;;;;Nachmontagen;;;;;Entsorgung;;;;;;;Polstermontage;;;;;Altmöbelrückgabe\n" .
// "S;;;;iSell Nr;;Lieferdat;EH;EUR;Fall;;WW15%;Kst EUR ;TV ;Sound;Kst EUR ;Fall;lfm ;Std;Kst EUR ;Fall;Kst EUR ;Fall;Kst EUR ;Fall;lfm ;Kst EUR ;Fall;Kst EUR ;Fall;MA;Std;Anf;Kst EUR ;Fall;MA;GP;Std;Kst EUR ;Fall;MA;GP;Std;Kst EUR ;Fall;MA;Std;Anf;Kst EUR ;Fall;AG ;Ma ;Po ;AG EUR;Ma EUR;Po EUR;C&C m L;C&C o L;SA m L;SA o L;DDS;Kst EUR;Kst EUR\n";
// $csv_text[$lcd_or_osArr[1]][$happy_or_sadArr[1]] = $csv_text[$lcd_or_osArr[1]][$happy_or_sadArr[0]];
/*nur Gesamtabrechnung*/
"";
$csv_text[$lcd_or_osArr[1]][$happy_or_sadArr[0]]["325"] = $csv_text[$lcd_or_osArr[1]][$happy_or_sadArr[0]]["146"];
$csv_text[$lcd_or_osArr[1]][$happy_or_sadArr[0]]["245"] = $csv_text[$lcd_or_osArr[1]][$happy_or_sadArr[0]]["146"];
$csv_text[$lcd_or_osArr[1]][$happy_or_sadArr[0]]["289"] = $csv_text[$lcd_or_osArr[1]][$happy_or_sadArr[0]]["146"];
// $csv_text_end[$lcd_or_osArr[1]][$happy_or_sadArr[0]] = $csv_text_end[$lcd_or_osArr[1]][$happy_or_sadArr[1]] = "";
$csv_text["Inkasso"] =
"Inkasso- Gutschrift IKEA;;;Kd.-Nr.;" . $accounting_cs_eids[$csc_id_related] . ";;;;;\n" .
";;;Sa-Auftrag;|jbpc_id|;;;;;\n" .
";;;;;;;;;\n" .
"IK-Nr.;Auftr.-Dat.;Durchf.-Dat.;Kunde;Bezahlart;Inkassobetrag;Kred.-Nr.;Fahrzg.". ";;Differenz IKEA\n";
$csv_text["InkassoSpezial"] = $csv_text["Inkasso"];
$sum_inkasso = 0.0;
$sumdiff_inkasso = 0.0;
$csv_text["Subvention"] =
"Order-Nr.;Auftragsnr.;Eingang;Ausführung;Kunde;lfd. Meter;Subvention\n";
if ($hq_id == 1020)
$csv_text["Subvention"] =
"Order-Nr.;Auftragsnr.;Eingang;Ausführung;Kunde;lfd. Meter;Rückvergütung;Differenz\n";
$sum_subv = 0.0;
$sumdiff_subv = 0.0;
$modeArr = array(
0 => "bar",
1 => "EC-PIN",
// 2 => "EC-Last",
10 => "Family",
// 11 => "Business",
15 => "Service",
// 16 => "LCD happy",
// 17 => "LCD sad",
// 18 => "OS happy",
// 19 => "OS sad",
20 => "Rechnung an IKEA"
);
$services = array(
"100" => array("ANSTD", "SA00000509", 0, 27), // Montage aller Art/Montagezusatzkosten,ADDITIONAL_INSTALL_SERVICE
"101" => array("MM", "SA60000525", 0, 12), // Möbelmontage,FURNITURE_ASSEMBLY
"102" => array("POM", "SA50000205", 0, 13), // Polstermöbelmontage,LEGS_FURNITURE_ASSEMBLY
// "103" => array("", "", 0), // Möbelmontage/Monteur vor Ort,FURNITURE_ASSEMBLY_EXPRESS
"104" => array("NM-MM", "SA50000502", 0, 4), // KuSe Möbelnachmontage,AFTER_SALES_FURNITURE_ASSEMBLY
"105" => array("MM-GRA", "SA50000578", 1, 25), // Großauftrag/Möbelmontage,FURNITURE_ASSEMBLY_SPECIAL
"106" => array("BADMM", "SA40000526", 0, 14), // Badmöbelmontage,BATHROOM_FURNITURE_INSTALL
"107" => array("NM-BADMM", "SA50000516", 0, 5), // KuSe Badmöbelnachmontage,AFTER_SALES_BATHROOM_FURNITURE_INSTALL
"108" => array("E-Ansch", "SA00000514", 0, 20), // Anschluss von IKEA E-Geräten,ELECTRIC_INSTALL_SERVICE
"109" => array("Uppleva TV", "SA40000550", 1, 21), // UPPLEVA TV Installation,INSTALLATION_TV
"110" => array("Uppleva Sound", "SA20000551", 1, 22), // UPPLEVA SOUND SYSTEM Installation,INSTALLATION_SOUND
"111" => array("Uppleva Fremd", "SA00000552", 1, 23), // UPPLEVA Anschluss Fremdgeräte,CONNECTION_TV
"112" => array("Uppleva Sound NM", "SA60000554", 1, 6), // KuSe UPPLEVA SOUND SYSTEM Installation,AFTER_SALES_SOUND
"113" => array("Uppleva TV NM", "SA80000553", 1, 7), // KuSe UPPLEVA TV Installation,AFTER_SALES_TV
"114" => array("NM-E-Ansch", "SA60000506", 0, 8), // Kuse E-Geräte Anschluss ,AFTER_SALES_ELECTRIC_INSTALL_SERVICE
// "115" => array("", "", 0), // Lagerkosten,STORAGE_HOME_DELIVERY
// "116" => array("", "", 0), // Verpackungskosten ,PACKING_IN_STORE
// "117" => array("", "", 0), // Einkaufservice,PICKING_IN_STORE_CUST
"118" => array("NM-KKM", "SA70000501", 0, 1), // KuSe Küchen-Nachmontage,AFTER_SALES_KITCHEN_INSTALL
"119" => array("NM-APLSTEIN", "SA70000520", 0, 2), // KuSe Nachmontage Steinarbeitsplatten,AFTER_SALES_KITCHEN_INSTALL_WORKTOP_CUSTOM_MADE
"120" => array("APLSTEIN", "SA20000495", 0, 10), // Montage Steinarbeitsplatte,KITCHEN_INSTALL_WORKTOP_CUSTOM_MADE
"121" => array("APLHOLZ", "SA00000533", 0, 11), // Montage Arbeitsplatten nach Maß,KITCHEN_INSTALL_WORKTOP
"122" => array("NM-APLHOLZ", "SA70000515", 0, 3), // KuSe Nachmontage Arbeitsplatten nach Maß,AFTER_SALES_KITCHEN_INSTALL_WORKTOP
"123" => array("KKM-GRA", "SA30000579", 1, 24), // Großauftrag/Küchen-Komplettmontage,KITCHEN_INSTALL_SPECIAL
"124" => array("KKM", "SA40000531", 0, 9), // Küchen-Komplettmontage,COMPLETE_KITCHEN_INSTALL
"125" => array("Aufmaß", "SA60000200", 0, 26), // Raumaufmaß,MEASURE_BEFORE_PLANNING
// "126" => array("", "", 0), // Arbeitsplattenaufmaß,WORKTOP_MEASURE
// "127" => array("", "", 0), // Einrichtungsberatung,HOME_PLANNING
// "128" => array("", "", 0), // Küchenplanungsservice,KITCHEN_PLANNING
"129" => array("BADWA", "SA00000496", 0, 15), // Wasseranschluss von Badmöbeln,BATHROOM_PLUMBING
// "130" => array("", "", 0), // KuSe Wasseranschluss von Badmöbeln,AFTER_SALES_PLUMBING
"131" => array("E-AG", "SA30000503", 0, 16), // Entsorgung und Abholung E-Geräte,REMOVAL_WHITE_GOODS
"132" => array("KDM", "SA50000210", 0, 17), // Altküchendemontage,DEASSEMBLY_KITCHEN
"133" => array("E-PO", "SA30000560", 0, 18), // Entsorgung und Abholung Sofas,REMOVAL_SOFA
"134" => array("E-MA", "SA30000517", 0, 19), // Altmatratzenrücknahme,REMOVAL_MATRESSES
// "135" => array("", "", 0), // Sondertransport,CARRY_HELP
// "136" => array("", "", 0), // Mietanhänger,RENT_A_TRAILER
// "137" => array("", "", 0), // Mietwagen,RENT_A_VAN
"138" => array("BEG", "SA60000511", 0, 28), // Begutachtung,QUALITY_APPRAISAL
// Splitterbox array("SPBOX", "SA50000521", 0) fehlt
// Arbeitsplattenzuschnitt("APLZUSCH", "SA80000119", 1) fehlt
// Entsorgung und Abholung von Altmöbeln ("E-ALTM", "SA30000522", 1) fehlt
);
/*------------------------------------------------------------------------------------------------------------------------------------------------*/
// Abrechnung
$sql_query =
"SELECT job.jb_id, jb_totalprice, jb_cr_price, jb_status, jb_incomplete, jb_service, cr_sid, LEFT(jb_ordertime, 10) AS ordertime, jb_mediationarea_name, LEFT(jb_createtime, 10) AS createtime, jb_finishtime, csc_id_related AS csc_id_related_real, tr_commission_no, tr_comp, jbp_price, jbp_mode, jbp_tan, gdc2.gdc_content AS info_0" .
" FROM genericdatacontainer AS gdc1, tour LEFT JOIN jobpayment ON tour.jb_id = jobpayment.jb_id, job LEFT JOIN genericdatacontainer AS gdc2 ON gdc2.gdc_obj_type = 'jb' AND gdc2.gdc_gen_fieldname = 'info_0' AND gdc2.gdc_obj_id = job.jb_id" .
" WHERE job.jb_id = gdc1.gdc_obj_id AND gdc1.gdc_gen_fieldname = 'acquisitiontime'" .
" AND (jb_storno IS NULL OR jb_storno = 0) AND csc_id_related IN ($csc_id_related)" .
" AND (jobpayment.jbpc_id IS NULL OR jobpayment.jbpc_id = 0)" .
// " AND jbpc_id IN (81003843)" .
" AND job.jb_id = tour.jb_id AND tour.tr_sort = 2 " .
" AND (jb_ordertime <= '" . $to___date_sql . "' AND NOT (job.jb_id IN (315462, 322139, 321777, 319985, 320185, 320946, 316291, 321335, 317036, 323325, 320208, 493099)))" . // 366316, 372264, 372265, 373658, 374814, 373108, 377627, 383578, 384570
" ORDER by ordertime ASC, info_0 ASC, jb_service ASC";
// $jb_ids = array("LCD" => array("Happy" => array(), "Sad" => array()), "OS" => array("Happy" => array(), "Sad" => array()));
// $sum = array("LCD" => array("Happy" => 0.0, "Sad" => 0.0), "OS" => array("Happy" => 0.0, "Sad" => 0.0));
//echo $sql_query; die();
myWriteLog($sql_query);
$cnt = array(
$lcd_or_osArr[0] => array(
$happy_or_sadArr[0] => 0,
$happy_or_sadArr[1] => 0),
$lcd_or_osArr[1] => array(
$happy_or_sadArr[0] => 0,
$happy_or_sadArr[1] => 0
)
);
$jb_ids = array(
$lcd_or_osArr[0] => array(
$happy_or_sadArr[0] => "",
$happy_or_sadArr[1] => ""),
$lcd_or_osArr[1] => array(
$happy_or_sadArr[0] => "",
$happy_or_sadArr[1] => ""
)
);
$sum_total = array(
$lcd_or_osArr[0] => array(
$happy_or_sadArr[0] => 0.0,
$happy_or_sadArr[1] => 0.0),
$lcd_or_osArr[1] => array(
$happy_or_sadArr[0] => 0.0,
$happy_or_sadArr[1] => 0.0
)
);
$sum_trat_price_lief_prev = 0;
$jb_total_price_prev = 0;
$func_lagerkCnt_prev = "";
$func_lagerk_rounded_prev = "";
$jb_id_prev = 0;
$tmp_csv_txt_prev = "";
$tr_sort = 2;
$res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
$man_event = $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_gen_fieldname = 'jb_events' AND gdc_content LIKE '%Failed Delivery IKEA%' AND gdc_obj_id = " . $row["jb_id"]);
$man_event_prev = "";
if ($man_event == "") {
//echo "SELECT gdc2.gdc_obj_id FROM genericdatacontainer AS gdc1, genericdatacontainer AS gdc2 WHERE gdc1.gdc_obj_type = 'jb' AND gdc1.gdc_gen_fieldname = 'jb_storno' AND gdc1.gdc_content = gdc2.gdc_obj_id AND gdc2.gdc_content LIKE '%Failed Delivery IKEA%' AND gdc1.gdc_obj_id = " . $row["jb_id"] . "\n";
$man_event_prev = $db->getOne("SELECT gdc2.gdc_obj_id FROM genericdatacontainer AS gdc1, genericdatacontainer AS gdc2 WHERE gdc1.gdc_obj_type = 'jb' AND gdc1.gdc_gen_fieldname = 'jb_storno' AND gdc1.gdc_content = gdc2.gdc_obj_id AND gdc2.gdc_content LIKE '%Failed Delivery IKEA%' AND gdc1.gdc_obj_id = " . $row["jb_id"]);
}
// $order_no = $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_gen_fieldname = 'info_0' AND gdc_obj_id = " . $row["jb_id"]);
$order_no = $row["info_0"];
$csc_id_related_real = $row["csc_id_related_real"];
$jb_id_addmont = $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_id_addmont'");
$jbp_tan_addmont = "";
if ($jb_id_addmont != "")
$jbp_tan_addmont = $db->getOne("SELECT jbp_tan FROM jobpayment WHERE jb_id = " . $jb_id_addmont);
// if ($db->getOne("SELECT jbpc_id FROM jobpayment WHERE jb_id = " . $jb_id_addmont) != "0")
// $jb_id_addmont = "";
// jb_id_addmont berücksichtigen
// $jb_id_clause = "jb_id = " . $row["jb_id"];
// if ($jb_id_addmont != "") {
// $jb_id_clause = "(jb_id = " . $row["jb_id"] . " OR jb_id = " . $jb_id_addmont . ")";
// }
// LCD oder OS
$lcd_or_os = $lcd_or_osArr[0];
if (($row["jb_service"] & $serviceMont) == $serviceMont) {
$lcd_or_os = $lcd_or_osArr[1];
$order_no = $row["tr_commission_no"];
}
// Happy oder Sad
$is_service_job = $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_service_job'") == '1';
$sams_nr = $db->getOne("SELECT gdc_context FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_service_job'");
if ($sams_nr == "nicht verfügbar") {
$sams_nr = "";
}
// $bo_obj_data = $db->getOne("SELECT bo_obj_data FROM phoenix_log.b2b_objects WHERE bo_type = 300 AND (bo_obj_data LIKE '" . $row["tr_commission_no"] . "|301%' OR bo_obj_data LIKE '" . $row["tr_commission_no"] . "|304%')");
// list ($dummy, $event_no) = explode("|", $bo_obj_data);
$happy_or_sad = $happy_or_sadArr[0];
if (/*$event_no == "304" || */ $is_service_job && $lcd_or_os == $lcd_or_osArr[0]) {
$happy_or_sad = $happy_or_sadArr[1];
// if ($event_no == "304" && $lcd_or_os == $lcd_or_osArr[1] && !$test) {
// $sql_query = "DELETE FROM jobpayment WHERE " . $jb_id_clause . " AND tr_sort = " . $tr_sort;
// mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
// }
}
// $jb_ids[$lcd_or_os][$happy_or_sad][] = $row["jb_id"];
// $sum[$lcd_or_os][$happy_or_sad] += $row["jb_totalprice"];
myWriteLog("");
myWriteLog($row["jb_id"] . ": " . $lcd_or_os . $happy_or_sad . ", Order-Nr.: " . $order_no . " vom " . formDate($row["createtime"]) . ", Kunde: " . $row["tr_comp"] . ", ausgeführt am " . formDate($row["ordertime"]) . " von " . $row["cr_sid"] . ", Nettopreis: " . formSum($row["jb_totalprice"]) . ", Nettofuhrlohn: " . formSum($row["jb_cr_price"]));
if ($man_event != "")
myWriteLog("Manuelles Event: " . $man_event);
if ($man_event_prev != "")
myWriteLog("Zweitanlieferung (vorheriger Auftrag 'failed IKEA': " . $man_event_prev . ")");
if ($is_service_job)
myWriteLog("SAMS-Nr.: " . $db->getOne("SELECT gdc_context FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_service_job'"));
// myWriteLog("Scan-Event: " . $bo_obj_data);
// myWriteLog("Events: " . $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_events'"));
// if (!$test) {
// // Überprüfen, ob jobpayment überhaupt existiert; wenn nicht, dann als bar einfügen
//// $jbp_price = $db->getOne("SELECT jbp_price FROM jobpayment WHERE jb_id = " . $row["jb_id"] . " AND tr_sort = " . $tr_sort);
// if ($row["jbp_price"] == "" && !($event_no == "304" && $lcd_or_os == $lcd_or_osArr[1])) {
// $sql_query =
// "INSERT INTO jobpayment (jb_id, tr_sort, csc_id, jbp_mode, jbp_price, jbp_bookingtime, jbp_counter, jbpc_id) VALUES " .
// "(" . $row["jb_id"] . ", " . $tr_sort . ", " . $accounting_csc_ids[$csc_id_related] . ", 0, " . $row["jb_totalprice"] . ", '" . $to___date_sql . "', 0, 0)";
// mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
// myWriteLog("\$jobpayment = hinzugefügt");
// }
//
// // jbp_mode in jbp_tan sichern, damit nachvollzogen werden kann, welcher Ursprungs-Mode vorhanden war
// $sql_query =
// "UPDATE jobpayment SET jbp_tan = CONCAT(jbp_mode) WHERE jbp_tan = '' AND " . $jb_id_clause . " AND tr_sort = " . $tr_sort;
// mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
// Jetzt den payment_type für die Abrechnung eintragen
// $sql_query =
// "UPDATE jobpayment SET csc_id = " . $accounting_csc_ids[$csc_id_related] . ", jbp_mode = " . $payment_types[$lcd_or_os][$happy_or_sad] . " WHERE " . $jb_id_clause . " AND tr_sort = " . $tr_sort . " AND jbpc_id = 0";
// mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
// }
//// Nach dem Umsatz-Export für den Fuhrlohn-Export ändern: jbp_mode = 0
// Prüfung, ob der Auftrag abgerechnet werden kann
$csv_log_entry = $row["jb_id"] . ";" . $lcd_or_os . $happy_or_sad . ";" . $order_no . ";" . formDate($row["createtime"]) . ";" . str_replace(";", "", $row["tr_comp"]) . ";" . formDate($row["ordertime"]) . ";" . $row["cr_sid"] . ";" . formSum($row["jb_totalprice"]) . ";" . formSum($row["jb_cr_price"]);
if ($lcd_or_os == $lcd_or_osArr[1] && trim($row["cr_sid"]) == "") {
myWriteLog("Kein Export: Fahrer fehlt in OS-Auftrag");
write_file($no_export_file_name, $csv_log_entry . ";Fahrer fehlt in OS Auftrag\n", "a");
} elseif ((in_array($row["jbp_tan"], array("0", "1", "10", "20")) || ($is_service_job && $lcd_or_os == $lcd_or_osArr[1])) AND ($row["jb_incomplete"] != "0" || $row["jb_status"] != "2")) {
myWriteLog("Kein Export: OS-Auftrag mit nicht erledigtem/unfertigen Auftrag, Zahlart: " . $modeArr[$row["jbp_tan"]]);
write_file($no_export_file_name, $csv_log_entry . ";Inkasso oder Nachmontage mit nicht erledigtem/unfertigen Auftrag\n", "a");
} elseif (
$db->getOne("SELECT gdc_gen_fieldname FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_addmont'") == "jb_addmont"
&& $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_addmont'") == "") {
myWriteLog("Kein Export: Montagestunden fehlen");
write_file($no_export_file_name, $csv_log_entry . ";Montagestunden fehlen\n", "a");
} elseif ($row["jb_finishtime"] == "0000-00-00 00:00:00" AND ($row["jb_incomplete"] == "0" && $row["jb_status"] == "2")) {
myWriteLog("Kein Export: Erledigungszeit des exportierbaren Auftrages ist '0000-00-00 00:00:00'");
write_file($no_export_file_name, $csv_log_entry . ";Erledigungszeit des exportierbaren Auftrages ist '0000-00-00 00:00:00'\n", "a");
} elseif ($row["jb_totalprice"] == 0 && ($row["jb_service"] & $serviceAust) != $serviceAust
&& $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_service_job'") != '1') {
myWriteLog("Kein Export: Kein Preis verfügbar");
write_file($no_export_file_name, $csv_log_entry . ";Kein Preis verfügbar\n", "a");
} else {
/*------------------------------------------------------------------------------------------------------------------------------------------------*/
// Auftrag wird abgerechnet
if ($row["jb_incomplete"] != "0" || $row["jb_status"] != "2") {
myWriteLog("Abrechnung an IKEA aber kein Fuhrlohnexport: nicht erledigter/unfertiger Auftrag");
write_file($no_cr_price_file_name, $csv_log_entry . ";nicht erledigter/unfertiger Auftrag\n", "a");
}
if ($db->getOne(
"SELECT jbc2.jb_id FROM jobcalculator AS jbc1, jobcalculator AS jbc2, job" .
" WHERE jbc1.srvt_name = IN (" . FUNCPAR_POLST . ", " . FUNCPAR_MATR . ", " . FUNCPAR_ENTSELEK . ") AND jbc1.jb_id = jbc2.jb_id AND jbc2.jb_id = job.jb_id AND jb_id_parent = 0 AND jb_storno IS NULL AND jbc2.jb_id = " . $row["jb_id"] .
" GROUP BY jbc2.jb_id HAVING COUNT(*) = 1") == $row["jb_id"]
&& $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_gen_fieldname = 'info_0' AND gdc_content = '" . $order_no . "'") != $order_no) {
myWriteLog("Abrechnung einer Entsorgung ohne Lieferung/Montage");
write_file($no_cr_price_file_name, $csv_log_entry . ";Entsorgung ohne Lieferung/Montage\n", "a");
}
// Ab hier geht's los
$sum_trat_price_lief = $db->getOne("SELECT SUM(trat_price) FROM tourarticle WHERE jb_id = " . $row["jb_id"]);
// $sum_trat_price_mont = $db->getOne("SELECT SUM(trat_price * " . (1 + VAT) . ") FROM tourarticle WHERE jb_id = " . $row["jb_id"]);
$func_sofaCnt = $db->getOne("SELECT jbc_amount FROM jobcalculator WHERE srvt_name = '" . FUNCPAR_SOFA . "' AND jb_id = " . $row["jb_id"]);
$func_sofa = $db->getOne("SELECT jbc_totalprice FROM jobcalculator WHERE srvt_name = '" . FUNCPAR_SOFA . "' AND jb_id = " . $row["jb_id"]);
if ($func_sofa == "0") {
$func_sofa = "";
}
/*------------------------------------------------------------------------------------------------------------------------------------------------*/
// LCD-Aufträge
if ($lcd_or_os == $lcd_or_osArr[0]) {
$trat_sum_weight = $db->getOne("SELECT gdc_content FROM tour, genericdatacontainer " .
" WHERE gdc_obj_type = 'tr' AND tr_id = gdc_obj_id AND gdc_gen_fieldname = 'trat_sum_weight' AND jb_id = " . $row["jb_id"] . " AND tr_sort = " . $tr_sort);
$func_lagerkCnt = $db->getOne("SELECT jbc_amount FROM jobcalculator WHERE srvt_name = '" . FUNCPAR_LAGERK . "' AND jb_id = " . $row["jb_id"]);
$func_lagerk = $db->getOne("SELECT jbc_totalprice FROM jobcalculator WHERE srvt_name = '" . FUNCPAR_LAGERK . "' AND jb_id = " . $row["jb_id"]);
// Wenn Lagerkosten vorhanden, dann Lagerkosten anhand der Scan-Events neu berechnen
// Für die Abrechnung soll der grundetete netto-Wert als Basis dienen
$func_lagerk_rounded = "";
if ($func_lagerkCnt != "") {
$row["jb_totalprice"] -= $func_lagerk; // Die alten Lagerkosten aus dem Endpreis herausrechnen
$func_lagerkCnt = "";
// Lagertage werden gezählt zwischen "Received at HUB" (201) und "Loaded on delivery truck" (202)
$received_date = $db->getOne("SELECT bo_createtime FROM phoenix_log.b2b_objects WHERE bo_type = 300 AND (bo_obj_data LIKE '" . $row["tr_commission_no"] . "|201%')");
$loaded_date = $db->getOne("SELECT bo_createtime FROM phoenix_log.b2b_objects WHERE bo_type = 300 AND (bo_obj_data LIKE '" . $row["tr_commission_no"] . "|202%')");
if ($received_date != "" && $loaded_date != "") {
$diffdays = ceil((strtotime($loaded_date) - strtotime ($received_date)) / (3600 * 24)) - 10;
if ($diffdays > 0) {
$func_lagerkSingle = $db->getOne("SELECT jbc_price FROM jobcalculator WHERE srvt_name = '" . FUNCPAR_LAGERK . "' AND jb_id = " . $row["jb_id"]);
$func_lagerk_rounded = $diffdays * $func_lagerkSingle;
$func_lagerkCnt = "" . (int) $diffdays;
}
}
myWriteLog("Received at HUB: '$received_date', Loaded on delivery truck: '$loaded_date', Lagertage: $func_lagerkCnt");
if (!$test) {
// Neu berechnete Werte schreiben
$sql_query =
"UPDATE jobcalculator SET jbc_amount = " . ($func_lagerkCnt == "" ? "0" : $func_lagerkCnt) . ", jbc_totalprice = " . ($func_lagerk_rounded == "" ? "0" : $func_lagerk_rounded) . ", jbc_modify = NOW() WHERE srvt_name = '" . FUNCPAR_LAGERK . "' AND jb_id = " . $row["jb_id"];
myWriteLog($sql_query);
mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
$jb_totalprice_new = $db->getOne("SELECT SUM(jbc_totalprice) FROM jobcalculator WHERE jb_id = " . $row["jb_id"]);
$sql_query = "UPDATE job SET jb_totalprice = " . $jb_totalprice_new . " WHERE jb_id = " . $row["jb_id"];
myWriteLog($sql_query);
mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
$sql_query = "UPDATE jobpayment SET jbp_price = " . $jb_totalprice_new . " WHERE jb_id = " . $row["jb_id"];
myWriteLog($sql_query);
mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
$sql_query = "UPDATE tourservice SET trs_price = " . $jb_totalprice_new . " WHERE jb_id = " . $row["jb_id"];
myWriteLog($sql_query);
mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
}
}
// fehlgeschlagener Versuch?
// 12.01.2016: Ein fehlgeschlagener Versuch wird wieder ganz normal als "Hinsend" aufgeführt
$AVmLV = "";
// if ($man_event != "")
// $AVmLV = "1";
// Zweitanlieferung nach fehlgeschlagenem Versuch?
$Zweitanl = "";
if ($man_event_prev != "")
$Zweitanl = "1";
// Abholung und/oder Austausch?
$Abholung = "";
if (($row["jb_service"] & $serviceAbhol) == $serviceAbhol) {
$Abholung = "1";
}
$Austausch = "";
if (($row["jb_service"] & $serviceAust) == $serviceAust) {
$Austausch = "1";
}
// Überprüfen, ob ein vorheriger Austausch-Lieferauftrag jetzt vom zugehörigen Austausch-Abholauftrag gefolgt wird
if ($jb_id_prev != 0 && !($Austausch == "1" && $Abholung == "1")) {
myWriteLog("ACHTUNG: Austausch-Lieferauftrag " . $jb_id_prev . " hat keinen nachfolgenden Austausch-Abholauftrag");
$csv_text["LCD"]["Sad"][$store_no[$csc_id_related_real]] .= $tmp_csv_txt;
$sum_total["LCD"]["Sad"] += netSum($jb_total_price_prev) + netSum($func_lagerk_rounded_prev);
$sum_trat_price_lief_prev = 0;
$jb_total_price_prev = 0;
$func_lagerkCnt_prev = "";
$func_lagerk_rounded_prev = "";
$jb_id_prev = 0;
$tmp_csv_txt_prev = "";
}
// Im Falle des Austausches müssen die beiden Aufträge zu einem zusammengefasst werden
if ($Austausch == "1") {
if ($Abholung == "1") {
// Warenwert und Preis des vorhergehenden Austausch-Lieferauftrages werden zum nächsten hinzuaddiert
if ($jb_id_prev == 0)
myWriteLog("ACHTUNG: Austausch-Abholung hat keinen vorhergehenden Austausch-Lieferauftrag");
else
myWriteLog("Austausch Abholung: Werte des vorhergehenden Austausch-Lieferauftrages " . $jb_id_prev . " wurden hinzuaddiert");
$sum_trat_price_lief += $sum_trat_price_lief_prev;
$row["jb_totalprice"] += $jb_total_price_prev;
$func_lagerkCnt = $func_lagerkCnt_prev;
$func_lagerk_rounded = $func_lagerk_rounded_prev;
$sum_trat_price_lief_prev = 0;
$jb_total_price_prev = 0;
$func_lagerkCnt_prev = "";
$func_lagerk_rounded_prev = "";
$jb_id_prev = 0;
$tmp_csv_txt_prev = "";
} else {
// der erste der beiden ist immer die Lieferung, dieser Auftrag muss "zurückgehalten" werden
$sum_trat_price_lief_prev = $sum_trat_price_lief;
$jb_total_price_prev = $row["jb_totalprice"];
$func_lagerkCnt_prev = $func_lagerkCnt;
$func_lagerk_rounded_prev = $func_lagerk_rounded;
$jb_id_prev = $row["jb_id"];
myWriteLog("Austausch Lieferung: Auftrag " . $row["jb_id"] . " wird im nachfolgenden Auftrag ausgegeben");
}
}
$shuttle_fee = 0;
if ($happy_or_sad == $happy_or_sadArr[0]) {
// if ($hq_name == "HL" && $row["createtime"] != $row["ordertime"]) {
// $shuttle_fee = 20;
// if (!$test) {
// $sql_query =
// "UPDATE job SET jb_cr_price = jb_cr_subprice + 20 WHERE jb_id = " . $row["jb_id"];
// mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
// myWriteLog($row["jb_id"] . ": Shuttle fee von " . $shuttle_fee . " € wurde dem Fuhrlohn hinzugerechnet");
// }
// }
$tmp_csv_txt =
";LCD;" . $lsc_no[$csc_id_related_real] . ";" . $order_no . ";" . $sams_nr . ";" . formDate($row["ordertime"]) . ";" . $store_no[$csc_id_related_real] . " ;" .
$areas[trim($row["jb_mediationarea_name"])] . ";" . $zones[trim($row["jb_mediationarea_name"])] . ";" . ($row["createtime"] == $row["ordertime"] ? "Direkt" : "Indirekt") . ";" .
formSum($sum_trat_price_lief) . ";" . formSum($trat_sum_weight, 3) . ";" .
($AVmLV == "" && $Zweitanl == "" ? formSum($row["jb_totalprice"]) : "") . ";" . formSum($shuttle_fee) . ";" .
($row["createtime"] == $row["ordertime"] ? "1" : "") . ";;" . $Abholung . ";" . $Austausch . ";" .
$Zweitanl . ";" . ($Zweitanl != "" ? formSum($row["jb_totalprice"]) : "") . ";" .
$AVmLV . ";" . ($AVmLV != "" ? formSum($row["jb_totalprice"]) : "") . ";;;" . formCntEmpty($func_lagerkCnt) . ";" . formSumEmpty($func_lagerk_rounded) . ";;;;;;;;;\n";
} else {
$tmp_csv_txt =
";LCD;" . $lsc_no[$csc_id_related_real] . ";" . $order_no . ";" . $sams_nr . ";" . formDate($row["ordertime"]) . ";" . $store_no[$csc_id_related_real] . " ;" .
$areas[trim($row["jb_mediationarea_name"])] . ";" . $zones[trim($row["jb_mediationarea_name"])] . ";" . ($row["createtime"] == $row["ordertime"] ? "Direkt" : "Indirekt") . ";" .
formSum($sum_trat_price_lief) . ";" .
($AVmLV == "" && $Zweitanl == "" ? formSum($row["jb_totalprice"]) : "") . ";0,00;" . $Abholung . ";" . $Austausch . ";;;" . formCntEmpty($func_sofaCnt) . ";" . formSumEmpty($func_sofa) . ";;;" .
$Zweitanl . ";" . ($Zweitanl != "" ? formSum($row["jb_totalprice"]) : "") . ";" .
$AVmLV . ";" . ($AVmLV != "" ? formSum($row["jb_totalprice"]) : "") . ";;;" . formCntEmpty($func_lagerkCnt) . ";" . formSumEmpty($func_lagerk_rounded) . ";;;;;;\n";
}
if (!($Austausch == "1" && $Abholung == "")) {
$csv_text[$lcd_or_os][$happy_or_sad][$store_no[$csc_id_related_real]] .= $tmp_csv_txt;
$sum_total[$lcd_or_os][$happy_or_sad] += netSum($row["jb_totalprice"]) + netSum($func_sofa) + netSum($func_lagerk_rounded) + $shuttle_fee;
} else { // Austausch-Lieferauftrag zurückhalten
$tmp_csv_txt_prev = $tmp_csv_txt;
}
}
/*------------------------------------------------------------------------------------------------------------------------------------------------*/
// OS-Aufträge
if (/*$event_no != "304" && */ $lcd_or_os == $lcd_or_osArr[1]) {
$jbAddedServiceInfosArray = array_filter(explode(',', $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_gen_fieldname = 'jb_add_service_info' AND gdc_obj_id = " . $row["jb_id"])));
$SA_Bezeichnung = "";
foreach($jbAddedServiceInfosArray as $jbAddedServiceInfo) {
if ($SA_Bezeichnung == "" || $services[$jbAddedServiceInfo][3] < $services[$SA_Bezeichnung_code][3]) {
$SA_Bezeichnung_code = $jbAddedServiceInfo;
$SA_Bezeichnung = $services[$SA_Bezeichnung_code][0];
}
}
myWriteLog("unique jb_add_service_info: " . json_encode(array_unique($jbAddedServiceInfosArray)));
myWriteLog("\$SA_Bezeichnung_code = " . $SA_Bezeichnung_code . ", \$SA_Bezeichnung = " . $SA_Bezeichnung);
// Rechnungsposten _mit_ hinterlegter Preisfunktion aus jobcalculator holen
$funcparArr = array();
foreach(array(FUNCPAR_POLST, FUNCPAR_MATR, FUNCPAR_MONTGRUND, FUNCPAR_MONT, FUNCPAR_KUELFDMET, FUNCPAR_KUEELEKTR, FUNCPAR_INSTTV, FUNCPAR_INSTLAUT, FUNCPAR_WASSER, FUNCPAR_ENTSELEK, FUNCPAR_SONDER, FUNCPAR_ENTSMOEB, FUNCPAR_SPLITTER, FUNCPAR_QUALITY) as $funcpar) {
$funcparArr[$funcpar][0] = $db->getOne("SELECT jbc_amount FROM jobcalculator WHERE srvt_name = '" . $funcpar . "' AND jb_id = " . $row["jb_id"]);
$funcparArr[$funcpar][1] = $db->getOne("SELECT jbc_totalprice FROM jobcalculator WHERE srvt_name = '" . $funcpar . "' AND jb_id = " . $row["jb_id"]);
// if ($funcpar == FUNCPAR_MONT && $row["jb_id"] == 312722) {
// $funcparArr[FUNCPAR_MONT][1] = 116.8487;
// }
if ($funcpar == FUNCPAR_SONDER && $funcparArr[FUNCPAR_SONDER][1] != "") { // Sondertransporte werden als Montagekosten abgerechnet
$funcparArr[FUNCPAR_MONTGRUND][1] += $funcparArr[FUNCPAR_SONDER][1];
}
}
// Rechnungsposten _ohne_ hinterlegte Preisfunktion aus jobcalculator holen
foreach(array("UPPLEVA Base Charge", "UPPLEVA Anschluss Fremdgeräte", "Subvention pro Küche < 4,30m", "Altküchendemontage") as $funcpar) {
$funcparArr[$funcpar][0] = $db->getOne("SELECT jbc_amount FROM jobcalculator WHERE srv_name = '" . $funcpar . "' AND jb_id = " . $row["jb_id"]);
$funcparArr[$funcpar][1] = $db->getOne("SELECT jbc_totalprice FROM jobcalculator WHERE srv_name = '" . $funcpar . "' AND jb_id = " . $row["jb_id"]);
}
// Anpassungen der einzelnen Posten für die Ausgabe
if ($funcparArr["UPPLEVA Base Charge"][1] != "") {
$uppleva_price = $funcparArr["UPPLEVA Base Charge"][1] + $funcparArr[FUNCPAR_INSTTV][1] + $funcparArr[FUNCPAR_INSTLAUT][1] + $funcparArr["UPPLEVA Anschluss Fremdgeräte"][1];
}
// Entsorgung Elektrogeräte: Abgerechneter Preis ist höher als Endkundenpreis
$inkasso_diff = 0;
if (formCntEmpty($funcparArr[FUNCPAR_ENTSELEK][0]) != "") {
$real_price_ENTSELEK = (30 / (1 + VAT)); // Endpreis in Süd und Nord
$funcparArrFUNCPAR_ENTSELEK_old = $funcparArr[FUNCPAR_ENTSELEK][0] * $funcparArr[FUNCPAR_ENTSELEK][1];
$funcparArr[FUNCPAR_ENTSELEK][1] = $funcparArr[FUNCPAR_ENTSELEK][0] * $real_price_ENTSELEK;
// if (!$test) {
// $sql_query =
// "UPDATE jobpayment SET jbp_price = " . ($row["jb_totalprice"] + $funcparArr[FUNCPAR_ENTSELEK][1] - $funcparArrFUNCPAR_ENTSELEK_old) . " WHERE jb_id = " . $row["jb_id"] . " AND tr_sort = " . $tr_sort;
// mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
// }
// $inkasso_diff += round(($funcparArr[FUNCPAR_ENTSELEK][1] - $funcparArrFUNCPAR_ENTSELEK_old) * (1 + VAT), 2);
if (!$test) {
$inkasso_diff_tmp = round($funcparArr[FUNCPAR_ENTSELEK][1] - $funcparArrFUNCPAR_ENTSELEK_old, 4);
$sql_query = "INSERT INTO genericdatacontainer (gdc_obj_type, gdc_obj_id, gdc_gen_fieldname, gdc_content, gdc_context)" .
" VALUES ('order_no', " . $order_no . ", 'price_ht', '" . round($row["jb_totalprice"] + $inkasso_diff_tmp, 4) . "', '" . $row["jb_id"] . "|" . round($row["jb_totalprice"], 4) . "|" . round($inkasso_diff_tmp, 4) . "')";
mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
myWriteLog($sql_query);
}
}
// ZUKÜNFTIG AUCH BEI KÜCHEN: 191,00 pro lfm AN IKEA!!!!
myWriteLog(formSumEmpty($funcparArr[FUNCPAR_KUELFDMET][0]));
if (formSumEmpty($funcparArr[FUNCPAR_KUELFDMET][0]) != "") {
$real_price_KUELFDMET = 199; // Endpreis in Nord // ab Abrechnung nach dem 01.04.2016 wieder 199,00 (vorher 179,00)
if ($hq_id == 1111) $real_price_KUELFDMET = 191; // Endpreis in Süd
myWriteLog($funcparArr[FUNCPAR_KUELFDMET][1]);
$funcparArrFUNCPAR_KUELFDMET_old = $funcparArr[FUNCPAR_KUELFDMET][1];
$funcparArr[FUNCPAR_KUELFDMET][1] = $funcparArr[FUNCPAR_KUELFDMET][0] * ($real_price_KUELFDMET / (1 + VAT));
myWriteLog($funcparArr[FUNCPAR_KUELFDMET][1]);
// if (!$test) {
// $sql_query =
// "UPDATE jobpayment SET jbp_price = " . ($row["jb_totalprice"] + $funcparArr[FUNCPAR_KUELFDMET][1] - $funcparArrFUNCPAR_KUELFDMET_old) . " WHERE jb_id = " . $row["jb_id"] . " AND tr_sort = " . $tr_sort;
// mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
// }
$inkasso_diff += round(($funcparArr[FUNCPAR_KUELFDMET][1] - $funcparArrFUNCPAR_KUELFDMET_old) * (1 + VAT), 2);
}
// Preisänderung für Endkunden am 31.11. (Grundgebühr Montage von 79,00€ auf 49,00€ und Montagegebür won WW15 auf WW20)
// Die Abrechnung an IKEA bleibt jedoch gleich, daher hier die Anpassung nötig
if (round(($funcparArr[FUNCPAR_MONTGRUND][1]) * (1 + VAT), 0) == 49) {
$price_ev = round($funcparArr[FUNCPAR_MONTGRUND][1] * (1 + VAT), 2) + round($funcparArr[FUNCPAR_MONT][1] * (1 + VAT), 2);
$funcparArrFUNCPAR_MONTGRUND_old = $funcparArr[FUNCPAR_MONTGRUND][1];
$funcparArrFUNCPAR_MONT_old = $funcparArr[FUNCPAR_MONT][1];
myWriteLog("Montagepreis Kunde = " . $price_ev);
$price_ht_MONTGRUND = 79 / (1 + VAT);
$price_ht_MONT = 15;
$funcparArr[FUNCPAR_MONTGRUND][1] = $price_ht_MONTGRUND;
$funcparArr[FUNCPAR_MONT][1] = ($funcparArr[FUNCPAR_MONT][1] * (1 + VAT) / 20 * $price_ht_MONT) / (1 + VAT);
$price_ikea = round($funcparArr[FUNCPAR_MONTGRUND][1] * (1 + VAT), 2) + round($funcparArr[FUNCPAR_MONT][1] * (1 + VAT), 2);
myWriteLog("Montagepreis IKEA = " . $price_ikea);
$inkasso_diff += $price_ikea - $price_ev;
myWriteLog("Inkasso-Differenz = " . $inkasso_diff);
}
if ($inkasso_diff == 0) {
$inkasso_diff = "";
}
// myWriteLog(var_export ($funcparArr, true));
// Montagestunden ermitteln und zuordnen (Montage oder SAMS)
$inkasso_price = 0;
if (in_array($row["jbp_tan"], array("0", "1", "10"))) {
$inkasso_price = $row["jbp_price"];
if ($is_service_job) {
$inkasso_price = $funcparArr[FUNCPAR_MONTGRUND][1];
}
}
$working_hours_sams = "";
$working_hours_price_sams = "";
if ($is_service_job) {
$working_hours_price_sams = $row["jbp_price"];
}
$working_hours = "";
$working_hours_price = "";
if ($jb_id_addmont != "") {
$working_hours = $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_addmont'");
$working_hours_price = $db->getOne("SELECT jb_totalprice FROM job WHERE jb_id = " . $jb_id_addmont);
$working_hours_inkasso = $db->getOne("SELECT jbp_price FROM jobpayment WHERE jb_id = " . $jb_id_addmont);
if (!$is_service_job) {
if (in_array($jbp_tan_addmont, array("0", "1", "10"))) {
$inkasso_price += $working_hours_inkasso;
}
} else {
$working_hours_sams = $working_hours;
$working_hours_price_sams += $working_hours_price;
$working_hours = "";
$working_hours_price = "";
}
myWriteLog("\$jb_id_addmont = $jb_id_addmont");
}
myWriteLog("\$inkasso_price = $inkasso_price");
if ($inkasso_price == 0)
$inkasso_price = "";
// Aufstellung der Rechnungsposten
$mont_gesamt = $funcparArr[FUNCPAR_MONTGRUND][1] + $funcparArr[FUNCPAR_MONT][1];
if ($mont_gesamt == 0)
$mont_gesamt = "";
// Tobedone: Unterschiedliche Zahlarten bzgl. Inkasso berücksichtigen (z.B. Auftrag bar, Montagestunden aber per EC)
// Derzeit darf nur eine Zahlung pro Auftrag existieren, da er sonst doppelt abgerechnet werden würde...
// In der Spalte "Inkasso" in der Excel-Tabelle für IKEA muss auch jbp_id statt jb_totalprice ausgewiesen werden
// Fuhrlohnexport haut für gesplitte Zahlung (bar/Inkasso IKEA) nicht hin, statt bar-Betrag wird der volle Betrag als abr exportiert
$csv_text[$lcd_or_os][$happy_or_sad][$store_no[$csc_id_related_real]] .=
/*nur Gesamtabrechnung*/
// ACHTUNG:
// - $SA_Bezeichnung enthält immer den übermittelten Service, also auch wenn mehrere übermittelt wurden
// - Wenn mehrere Services übermittelt werden, versuche ich in der SA-Bezeichnung eine sinnvolle Auswahl zu treffen (Bsp. Möbelmontage und Polstermöbelmontage zusammen ergibt Möbelmontage)
// - die Liste der SA-Bezeichnungen ist nicht vollständig ("CARRY_HELP fehlt 785558158,789316738 , Aufträge ohne Service 787579279 fehlen REMOVAL_WHITEGOODS")
// - Badmöbelmontage werden nicht extra ausgiewesen, da berechnet wie normale Möbelmontagen
// - In der letzten Spalte Sonstiges (Uppleva, Möbelmontage vor Ort, Großauftrag Küche und Möbelmontage, Arbeitsplattenaufmaß) werden Möbelmontage vor Ort, Großauftrag Küche und Möbelmontage nicht aufgeführt,
// weil zuvor ganz normal abgerechnet; Arbeitsplattenaufmaß haben wir nicht
// - wenn Uppleva und eine Möbelentsorgung zusammen vorkommen, dann crasht es weil die letzte Spalte "Sonstiges" nur eine Zeile aufnehmen kann
// UPPLEVA derzeit noch nicht berücksichtigt! Kontrolle durch Nachberechnen der csv-Summen durchführen!
";;" . $lsc_no[$csc_id_related_real] . ";" . $sams_nr . ";" . $order_no . ";" . $SA_Bezeichnung . ";" . formDate($row["ordertime"]) . ";" . $store_no[$csc_id_related_real] . " ;" . grossSumEmpty($inkasso_price) . ";" .
formCntEmpty($funcparArr[FUNCPAR_MONT][0]) . ";" . formSumEmpty($funcparArr[FUNCPAR_MONT][1]) . ";" . (!$is_service_job ? formSumEmpty($mont_gesamt) : "") . ";" .
($funcparArr[FUNCPAR_KUELFDMET][0] != "" ? "1" : "") . ";" . formSumEmpty($funcparArr[FUNCPAR_KUELFDMET][0]) . ";" . formSumEmpty($funcparArr[FUNCPAR_KUELFDMET][1]) . ";" .
($funcparArr[FUNCPAR_SPLITTER][0] != "" ? "1" : "") . ";" . formSumEmpty($funcparArr[FUNCPAR_SPLITTER][1]) . ";" .
formCntEmpty($funcparArr[FUNCPAR_WASSER][0]) . ";" . formSumEmpty($funcparArr[FUNCPAR_WASSER][1]) . ";" .
/*Badmöbelmontage1*/ ";" . /*Badmöbelmontage1*/ ";" .
formCntEmpty($funcparArr[FUNCPAR_KUEELEKTR][0]) . ";" . formSumEmpty($funcparArr[FUNCPAR_KUEELEKTR][1]) . ";" .
($funcparArr["Altküchendemontage"][0] != "" ? "1" : "") . ";" . formCntEmpty($funcparArr["Altküchendemontage"][0]) . ";" . formSumEmpty($funcparArr["Altküchendemontage"][1]) . ";" .
/*Aufmaß1*/ ";" . /*Aufmaß2*/ ";" .
($working_hours != "" ? "1" : "") . ";" . /*MA*/ ";" . formSumEmpty($working_hours) . ";" . /*Anf*/ ";" . formSumEmpty($working_hours_price) . ";" .
/*Montage_APL_Holz1*/ ";" . /*Montage_APL_Holz2*/ ";" . /*Montage_APL_Holz3*/ ";" . /*Montage_APL_Holz4*/ ";" . /*Montage_APL_Holz5*/ ";" .
/*Montage_APL_Stein1*/ ";" . /*Montage_APL_Stein2*/ ";" . /*Montage_APL_Stein3*/ ";" . /*Montage_APL_Stein4*/ ";" . /*Montage_APL_Stein5*/ ";" .
($SA_Bezeichnung_code == "104" ? ($working_hours_sams != "" ? "1" : "") . ";" . /*MA*/ ";" . formSumEmpty($working_hours_sams) . ";" . /*Anf*/ ";" . formSumEmpty($working_hours_price_sams) . ";" : ";;;;;") .
($SA_Bezeichnung_code == "118" ? ($working_hours_sams != "" ? "1" : "") . ";" . /*MA*/ ";" . formSumEmpty($working_hours_sams) . ";" . /*Anf*/ ";" . formSumEmpty($working_hours_price_sams) . ";" : ";;;;;") .
($SA_Bezeichnung_code == "114" ? ($working_hours_sams != "" ? "1" : "") . ";" . /*MA*/ ";" . formSumEmpty($working_hours_sams) . ";" . /*Anf*/ ";" . formSumEmpty($working_hours_price_sams) . ";" : ";;;;;") .
($SA_Bezeichnung_code == "119" ? ($working_hours_sams != "" ? "1" : "") . ";" . /*MA*/ ";" . formSumEmpty($working_hours_sams) . ";" . /*Anf*/ ";" . formSumEmpty($working_hours_price_sams) . ";" : ";;;;;") .
($SA_Bezeichnung_code == "122" ? ($working_hours_sams != "" ? "1" : "") . ";" . /*MA*/ ";" . formSumEmpty($working_hours_sams) . ";" . /*Anf*/ ";" . formSumEmpty($working_hours_price_sams) . ";" : ";;;;;") .
($SA_Bezeichnung_code == "107" ? ($working_hours_sams != "" ? "1" : "") . ";" . /*MA*/ ";" . formSumEmpty($working_hours_sams) . ";" . /*Anf*/ ";" . formSumEmpty($working_hours_price_sams) . ";" : ";;;;;") .
($funcparArr[FUNCPAR_ENTSELEK][0] != "" || $funcparArr[FUNCPAR_MATR][0] != "" || $funcparArr[FUNCPAR_POLST][0] != "" ? "1" : "") .
";" . formCntEmpty($funcparArr[FUNCPAR_ENTSELEK][0]) . ";" . formCntEmpty($funcparArr[FUNCPAR_MATR][0]) . ";" . formCntEmpty($funcparArr[FUNCPAR_POLST][0]) . ";" . formSumEmpty($funcparArr[FUNCPAR_ENTSELEK][1]) . ";" . formSumEmpty($funcparArr[FUNCPAR_MATR][1]) . ";" . formSumEmpty($funcparArr[FUNCPAR_POLST][1]) . ";" .
/*CCmL*/ ";" /*CCoL*/ . ";" /*SoCCmL*/ . ";" /*SoCCoL*/ . ";" . ($func_sofa != "" ? "1" : "") . ";" . formSumEmpty($func_sofa) . ";" .
($funcparArr[FUNCPAR_ENTSMOEB][0] != "" ? "1;E-ALTM;SA30000522;" . formSumEmpty($funcparArr[FUNCPAR_ENTSMOEB][1]) :
($funcparArr[FUNCPAR_QUALITY][0] != "" ? "1;BEG;SA60000511;" . formSumEmpty($funcparArr[FUNCPAR_QUALITY][1]) : ";;;")) . "\n";
/*nur Einzelabrechnung*/
// ";;;;" . $order_no . ";;" . formDate($row["ordertime"]) . ";" . $store_no[$csc_id_related_real] . " ;" . (!$is_service_job && $row["jbp_tan"] != "15" /* triftt nur auf nur 'verwaiste' Montagestunden zu */ && $row["jbp_tan"] != "20" ? grossSumEmpty($inkasso_price) : "") . ";" . formCntEmpty($funcparArr[FUNCPAR_MONT][0]) .
// ";;" . formSumEmpty($funcparArr[FUNCPAR_MONT][1]) . ";" . (!$is_service_job ? formSumEmpty($mont_gesamt) : "") . ";" . formCntEmpty($funcparArr[FUNCPAR_INSTTV][0]) . ";" .
// formCntEmpty($funcparArr[FUNCPAR_INSTLAUT][0]) . ";" . $uppleva_price . ";" . ($funcparArr[FUNCPAR_KUELFDMET][0] != "" ? "1" : "") . ";" . formSumEmpty($funcparArr[FUNCPAR_KUELFDMET][0]) . ";;" .
// formSumEmpty($funcparArr[FUNCPAR_KUELFDMET][1]) . ";" . formCntEmpty($funcparArr[FUNCPAR_WASSER][0]) . ";" . formSumEmpty($funcparArr[FUNCPAR_WASSER][1]) . ";" . formCntEmpty($funcparArr[FUNCPAR_KUEELEKTR][0]) .
// ";" . formSumEmpty($funcparArr[FUNCPAR_KUEELEKTR][1]) . ";" . formCntEmpty($funcparArr["Altküchendemontage"][0]) . ";;" . formSumEmpty($funcparArr["Altküchendemontage"][1]) . ";;;;;" .
// formSumEmpty($working_hours) . ";;" . formSumEmpty($working_hours_price) . ";;;;;;;;;;;;;" . formSumEmpty($working_hours_sams) . ";;" . formSumEmpty($working_hours_price_sams) . ";;" . formCntEmpty($funcparArr[FUNCPAR_ENTSELEK][0]) . ";" .
// formCntEmpty($funcparArr[FUNCPAR_MATR][0]) . ";" . formCntEmpty($funcparArr[FUNCPAR_POLST][0]) . ";" . formSumEmpty($funcparArr[FUNCPAR_ENTSELEK][1]) . ";" . formSumEmpty($funcparArr[FUNCPAR_MATR][1]) . ";" . formSumEmpty($funcparArr[FUNCPAR_POLST][1]) . ";;;;;;" . formSumEmpty($func_sofa) . ";" . formSumEmpty($funcparArr[FUNCPAR_ENTSMOEB][1]) . "\n";
$sum_total[$lcd_or_os][$happy_or_sad] +=
(!$is_service_job ? netSum($mont_gesamt) : 0) + $uppleva_price + netSum($funcparArr[FUNCPAR_KUELFDMET][1]) + netSum($funcparArr[FUNCPAR_WASSER][1]) + netSum($funcparArr[FUNCPAR_KUEELEKTR][1]) +
netSum($funcparArr["Altküchendemontage"][1]) + netSum($working_hours_price) + netSum($working_hours_price_sams) +
netSum($funcparArr[FUNCPAR_ENTSELEK][1]) + netSum($funcparArr[FUNCPAR_MATR][1]) + netSum($funcparArr[FUNCPAR_POLST][1]) + netSum($func_sofa) + netSum($funcparArr[FUNCPAR_ENTSMOEB][1]) + netSum($funcparArr[FUNCPAR_SPLITTER][1]) + netSum($funcparArr[FUNCPAR_QUALITY][1]);
// Kassierte Beträge in Inkasso-Liste aufnehmen (nicht wenn SAMS)
if (!$is_service_job) {
if (trim($row["cr_sid"]) == "") {
$row["cr_sid"] = "WAST666";
}
foreach (array($row["jb_id"] => $row["jbp_price"], $jb_id_addmont => $working_hours_inkasso) as $ink_jb_id => $totalprice) {
$tmp_jbp_tan = $row["jbp_tan"];
// $tmp_jbp_tan = $row["jbp_mode"];
if ($ink_jb_id == $jb_id_addmont) {
$tmp_jbp_tan = $jbp_tan_addmont;
$inkasso_diff = "";
}
$jbp_mode_str = "";
if ($tmp_jbp_tan == "0")
$jbp_mode_str = "bar";
if ($tmp_jbp_tan == "1")
$jbp_mode_str = "EC";
if ($tmp_jbp_tan == "10")
$jbp_mode_str = "Family";
// bei $tmp_jbp_tan == "20" passiert nix /* "Inkasso IKEA" */
//myWriteLog("\$ink_jb_id = $ink_jb_id, \$tmp_jbp_tan = $tmp_jbp_tan, \$jbp_mode_str = $jbp_mode_str");
if ($ink_jb_id != "" && $jbp_mode_str != "" /* wg. "Inkasso IKEA" */) {
if ($inkasso_diff != "") {
$sumdiff_inkasso += $inkasso_diff;
myWriteLog("\$sumdiff_inkasso = $sumdiff_inkasso");
}
$csv_text["Inkasso"] .=
$ink_jb_id . ";" . formDate($row["createtime"]) . ";" . formDate($row["ordertime"]) . ";" . str_replace(";", "", $row["tr_comp"]) . ";" . $jbp_mode_str . ";" . str_replace(",", ".", grossSum($totalprice)) . ";" .
$db->getOne("SELECT cr1.cr_eid FROM courier AS cr1, courier As cr2 WHERE cr1.cr_id = cr2.cr_id_parent AND cr2.cr_sid = '" . $row["cr_sid"] . "'") . ";" . substr($row["cr_sid"], -3) . ";;" . $inkasso_diff . "\n";
$csv_text["InkassoSpezial"] .=
$ink_jb_id . ";" . formDate($row["createtime"]) . ";" . formDate($row["ordertime"]) . ";" . str_replace(";", "", $row["tr_comp"]) . ";" . $jbp_mode_str . ";" . str_replace(",", ".", grossSum($totalprice)) . ";" .
$db->getOne("SELECT cr1.cr_eid FROM courier AS cr1, courier As cr2 WHERE cr1.cr_id = cr2.cr_id_parent AND cr2.cr_sid = '" . $row["cr_sid"] . "'") . ";" . substr($row["cr_sid"], -3) . ";;" . $order_no . "\n";
$sum_inkasso += round($totalprice * (1 + VAT), 2);
if ($row["jb_cr_price"] == 0 && !$test) {
$sql_query =
"UPDATE job SET jb_cr_price = 0.01 WHERE jb_id = " . $ink_jb_id;
mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
myWriteLog($ink_jb_id . ": Fuhrlohn auf 0.01 EUR gesetzt");
}
}
if ($inkasso_diff != "" && !$test) {
$sql_query = "INSERT INTO genericdatacontainer (gdc_obj_type, gdc_obj_id, gdc_gen_fieldname, gdc_content, gdc_context)" .
" VALUES ('order_no', " . $order_no . ", 'price_ht', '" . round($totalprice + ($inkasso_diff / 1.19), 2) . "', '" . $ink_jb_id . "|" . round($totalprice, 4) . "|" . round($inkasso_diff / 1.19, 4) . "')";
mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
myWriteLog($sql_query);
}
}
} elseif ($row["jbp_tan"] == "20") { // Rechnung an IKEA
myWriteLog($row["jb_id"] . ": Kein Inkasso erforderlich");
}
// Küchen-Subventions- (Süd) bzw. Rückvergütungsliste (Nord)
if (($funcparArr[FUNCPAR_KUELFDMET][0] < 4.30 || $hq_id == 1020 || $test) && trim($funcparArr[FUNCPAR_KUELFDMET][0]) != "") {
$csv_text["Subvention"] .=
$order_no . ";" . $row["jb_id"] . ";" . formDate($row["createtime"]) . ";" . formDate($row["ordertime"]) . ";" . $row["tr_comp"] . ";" . formSum($funcparArr[FUNCPAR_KUELFDMET][0]) . ";" .
($hq_id == 1111 ? "24,00" : formSum(netSum($funcparArr[FUNCPAR_KUELFDMET][1]) * 0.1) . ";" . formSum($funcparArr[FUNCPAR_KUELFDMET][0] * 20 / 1.19)) . "\n";
// im Falle von Hamburg kommt eine Spalte hinzu; hier wird die Differenz zwischen Preis pro lfd. Meter und dem entsprechenden Fuhrlohn ausgewiesen, aktuell brutto 20,00 € (199,00 € - 179,00 €) pro lfd. Meter
$sum_subv += ($hq_id == 1111 ? 24 : netSum($funcparArr[FUNCPAR_KUELFDMET][1]) * 0.1);
$sumdiff_subv += ($hq_id == 1111 ? 0 : netSum($funcparArr[FUNCPAR_KUELFDMET][0]) * 20 / 1.19);
}
}
/*------------------------------------------------------------------------------------------------------------------------------------------------*/
// OS- und LCD-Aufträge
$cnt[$lcd_or_os][$happy_or_sad]++;
$jb_ids[$lcd_or_os][$happy_or_sad] .= "," . $row["jb_id"];
if ($jb_id_addmont != "") {
$jb_ids[$lcd_or_os][$happy_or_sad] .= "," . $jb_id_addmont;
}
}
endwhile;
mysql_free_result($res);
if ($jb_id_prev != 0 && !($Austausch == "1" && $Abholung == "1")) {
myWriteLog("ACHTUNG: Auftrag " . $jb_id_prev . " hat keinen nachfolgenden Austausch-Abholauftrag");
$csv_text["LCD"]["Sad"][$store_no[$csc_id_related_real]] .= $tmp_csv_txt;
$sum_total["LCD"]["Sad"] += netSum($jb_total_price_prev) + netSum($func_lagerk_rounded_prev);
$sum_trat_price_lief_prev = 0;
$jb_total_price_prev = 0;
$func_lagerk_rounded_prev = 0;
$jb_id_prev = 0;
$tmp_csv_txt_prev = "";
}
$csv_text["Inkasso"] .=
";;;;;;;;;\n" . "Summe;;;;;" . str_replace(",", ".", formSum($sum_inkasso)) . ";;;;" . formSumEmpty($sumdiff_inkasso) . "\n";
$csv_text["InkassoSpezial"] .=
";;;;;;;;;\n" . "Summe;;;;;" . str_replace(",", ".", formSum($sum_inkasso)) . ";;;;\n";
$csv_text["Subvention"] .=
";;;;;;". formSum($sum_subv) . ($hq_id == 1111 ? "" : ";" . formSum($sumdiff_subv)) . "\n";
$returnArr = array();
foreach ($lcd_or_osArr as $lcd_or_os) {
foreach ($happy_or_sadArr as $happy_or_sad) {
if ($cnt[$lcd_or_os][$happy_or_sad] > 0) {
$returnArr[$lcd_or_os . $happy_or_sad] = array(
$temp_csv_folder . $lcd_or_os . "_" . $happy_or_sad . "flow_" . substr($to___date_sql, 0, 10) . ".csv",
$csv_text[$lcd_or_os][$happy_or_sad], $jb_ids[$lcd_or_os][$happy_or_sad], $sum_total[$lcd_or_os][$happy_or_sad], $lcd_or_os, $happy_or_sad);
}
}
}
$returnArr["Inkasso"] = array($inkasso_file_name, $csv_text["Inkasso"]);
$returnArr["InkassoSpezial"] = array($inkassospezial_file_name, $csv_text["InkassoSpezial"]);
$returnArr["Subvention"] = array($subventions_file_name, $csv_text["Subvention"]);
return $returnArr;
}
function formDate($sqlDate)
{
if (trim($sqlDate) == "")
return "";
return substr($sqlDate, 8, 2) . "." . substr($sqlDate, 5, 2) . "." . substr($sqlDate, 0, 4);
}
function formSum($sum, $digits = 2)
{
return str_replace(".", ",", round($sum, $digits));
}
function formSumEmpty($sum, $digits = 2)
{
if (trim($sum) == "")
return "";
return formSum($sum, $digits);
}
function grossSum($sum)
{
return (formSum($sum * (1 + VAT), 2));
}
function netSum($sum, $digits = 2)
{
return round($sum, $digits);
}
function grossSumEmpty($sum)
{
if (trim($sum) == "")
return "";
return grossSum($sum);
}
function formCntEmpty($sum)
{
if (trim($sum) == "")
return "";
return floor($sum);
}
function myWriteLog($log_text, $file_name = "") {
global $log_file_name, $temp_csv_folder;
if ($file_name == ""):
$file_name = $log_file_name;
write_file($file_name, "[" . date("Y-m-d H:i:s") . "] " . $log_text . "\n");
else:
write_file($temp_csv_folder . $file_name, $log_text . "\n");
endif;
}
function write_file($file_name, $text_to_write, $write_mode = "a") {
$fileHandle = @fopen($file_name, $write_mode);
@fwrite($fileHandle, $text_to_write);
@fclose($fileHandle);
return;
}
function send_mail($subj, $mail_text, $from = "auto-mailer@assecutor.de", $to = "admin@assecutor.de", $bcc = "", $atta = "") {
include_once("../include/email/htmlMimeMail.php");
$mailObj = new htmlMimeMail();
$mailObj->setFrom($from);
if ($bcc != "")
$mailObj->setBcc($bcc);
$mailObj->setSubject($subj);
$mailObj->setText($mail_text);
if ($atta != ""):
$attachment = $mailObj->getFile($atta);
$filenameArr = explode("/", $atta);
$mailObj->addAttachment($attachment, $filenameArr[count($filenameArr)-1]);
endif;
$mailResult = $mailObj->send(array($to), 'smtp');
return $mailResult;
}
if ($no_web) {
exit();
}
?>
<html>
<head>
<title>&Uuml;bersicht der Rechnungen</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 type="text/javascript">
<!--
function popupWindow(url,title,config) {
popup = window.open(url,title,config);
}
-->
</script>
</head>
<body>
<table border="0" width="100%" cellspacing="0" cellpadding="0" vspace="0" hspace="0">
<tr>
<td>
&nbsp;
</td>
<td align="left">
<table border="0" cellspacing="0" cellpadding="0" vspace="0" hspace="0">
<tr>
<td align="left">
<?php echo $content;?>
</td>
</tr>
</table>
</td>
<td>
&nbsp;
</td>
</tr>
</table>
</body>
</html>
Reference in New Issue View Git Blame Copy Permalink