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 . "
\n"; //echo $to___date_sql_next . "
\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'))" . "
\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] . */ "\n", "", str_replace("\n", "\n

" . str_replace("
", str_replace(";", "	", $csv_txtArr[1]))) . "\n

\n"; $csv_txtArr[1] = str_replace("\n", "", str_replace("", " ", $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: Polster pro Sitzfläche" define ("FUNCPAR_MATR", "0006:par_01:1005"); // "IKEA: Matratze pro Stück" define ("FUNCPAR_SOFA", "ident_0027:par_01"); // "Sofa-Montage" // define ("FUNCPAR_SOEDER", "ident_0012:par_01"); // "Montage Söderhamn" define ("FUNCPAR_MONT", "0004:par_01"); // Warenwertabhängiger "Montagepreis" define ("FUNCPAR_MONTGRUND", "ident_0009:par_01"); // "Grundpauschale 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 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(); } ?>