<?php
/*=======================================================================
 *
 * pre_export.php
 *
 * Autor: Carsten Annacker
 *
 * Aufzurufen jeden Tag kurz nach Mitternacht als cron
 *
 * Schnelsen:
 * - Wöchentliche Rechnung über Sams (getrennt nach Lieferung und Montage)
 * - Wöchentliche Rechnung über Kurieraufträge
 * - Wöchentliche Rechnung über Businessaufträge
 * - Monatlich Gutschrift der Differenz Montagegrundpauschale s. Anlage (normalerweise monatlich)
 * - Monatlich Gutschrift Rückvergütung zu den Lieferungen und Montagen s. Anlage
 * 
 * Moorfleet:
 * - Wöchentliche Rechnung über Businessaufträge
 * - Wöchentliche Rechnung über EKS
 * - Monatlich Gutschrift Rückvergütung zu den Montagen Analog zu Schnelsen
 * 
 * Altona:
 * 
 * - Wöchentliche Rechnung über Businessaufträge
 * - Wöchentliche Rechnung über EKS
 * - Wöchentliche Rechnung Preisdifferenzen (LP zur Auftragspauschale+Gewicht)
 * - Wöchentliche Gutschrift Preisdifferenzen Montagegrundpauschale
 * - Wöchentliche Gutschriften zu den Rückvergütungen differenziert nach Küchenmontage, Möbelmontage, Sofamontage.
 *
 =======================================================================*/

$test = false;

if ($argv[1] != "acapella7890")
  die();

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");

$log_file_name = "../log/prepost_export_" . date("Ym") . ".log";
$temp_csv_folder = "../temp/edifact/";
//$paymentBC = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'payment_type' AND mt_objtype = 'grp' AND mt_objid = '11' AND mt_value = 'Businesscard'");
//$paymentSAMS = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'payment_type' AND mt_objtype = 'grp' AND mt_objid = '11' AND mt_value = 'Serviceaufträge'");
//$paymentCash = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'payment_type' AND mt_objtype = 'grp' AND mt_objid = '11' AND mt_value = 'Bar'");
//$paymentECPin = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'payment_type' AND mt_objtype = 'grp' AND mt_objid = '11' AND mt_value = 'EC-Pin'");
//$paymentECLast = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'payment_type' AND mt_objtype = 'grp' AND mt_objid = '11' AND mt_value = 'EC-Lastschrift'");
//$paymentFamily = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'payment_type' AND mt_objtype = 'grp' AND mt_objid = '11' AND mt_value = 'Familycard'");
$serviceLief = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'LI'");
$serviceMont = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'MO'");
$serviceNeuMont = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'NM'");
$serviceKueMont = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'KU'");
$serviceAbhol = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'AB'");
$serviceAust = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'AT'");

$from_date_week_timestamp = mktime(0, 0, 0, date("m"), date("d") - 7 - (date("w") - 1), date("Y"));
$to___date_week_timestamp = mktime(0, 0, 0, date("m"), date("d") - 2 - (date("w") - 1), date("Y"));
$from_date_week_txt = date("d.m.Y", $from_date_week_timestamp);
$from_date_week_sql = date("Y-m-d 00:00:00", $from_date_week_timestamp);
$to___date_week_txt = date("d.m.Y", $to___date_week_timestamp);
$to___date_week_sql = date("Y-m-d 23:59:59", $to___date_week_timestamp);

$from_date_month_timestamp = mktime(0, 0, 0, date("m") - 1, 1, date("Y"));
$to___date_month_timestamp = mktime(0, 0, 0, date("m"), 0, date("Y"));
$from_date_month_txt = date("d.m.Y", $from_date_month_timestamp);
$from_date_month_sql = date("Y-m-d 00:00:00", $from_date_month_timestamp);
$to___date_month_txt = date("d.m.Y", $to___date_month_timestamp);
$to___date_month_sql = date("Y-m-d 23:59:59", $to___date_month_timestamp);

//$sql_query = "SELECT jb_id FROM job WHERE jb_createtime >= '$from_date_week_sql' ORDER BY jb_createtime ASC";
//$res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
//$row = mysql_fetch_array($res);
//$jb_id_from_week = $row['jb_id'];
//mysql_free_result($res);
//$sql_query = "SELECT jb_id FROM job WHERE jb_createtime >= '$from_date_month_sql' ORDER BY jb_createtime ASC";
//$res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
//$row = mysql_fetch_array($res);
//$jb_id_from_month = $row['jb_id'];
//mysql_free_result($res);
//$sql_query = "SELECT jb_id FROM job WHERE jb_createtime <= '$to___date_week_sql' ORDER BY jb_createtime DESC";
//$res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
//$row = mysql_fetch_array($res);
//$jb_id___to_week = $row['jb_id'];
//mysql_free_result($res);
//$sql_query = "SELECT jb_id FROM job WHERE jb_createtime <= '$to___date_month_sql' ORDER BY jb_createtime DESC";
//$res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
//$row = mysql_fetch_array($res);
//$jb_id___to_month = $row['jb_id'];
//mysql_free_result($res);

$jb_invtextArr = array();
//$jb_id_from = 0;
//$jb_id_to = 0;
$from_date_sql = "";
$to___date_sql = "";

// pre_export für Servicekurier (Schnelsen), EKS (Moorfleet), Preisdifferenzen und Rückvergütungen (Altona)
myWriteLog("pre_export.php: date(\"d\") = " . date("d") . ", date(\"D\") = " . date("D"));

$jb_ids_auftragspauschale = array();

// Monatliche Rechnungen
if (date("d") == "01" || $argv[2] == "test"):
  init_jb_invtextArr("m");
//print_r($jb_invtextArr);
//echo  "\n";
//echo $from_date_sql . "\n";
//echo $to___date_sql . "\n";
//die();
  do_diff(1); // Schnelsen
  do_diff(2); // Moorfleet
endif;

// Wöchentliche Rechnungen
if (date("D") == "Mon" || $argv[2] == "test"):
  init_jb_invtextArr("w");
//print_r($jb_invtextArr);
//echo  "\n";
//echo $from_date_sql . "\n";
//echo $to___date_sql . "\n";
//die();
//  do_SAMS(1); // Schnelsen
  do_SAMScourier(1);
//  do_BC(1);
//  do_BC(2); // Moorfleet
  do_EKS(2);
//  do_BC(17051); // Altona
  do_EKS(17051);
  do_diff(17051); // Altona
endif;

if (date("d") == "01" || date("D") == "Mon" || $argv[2] == "test"):
  $cmd = "php ../export/cron_export.php acapella7890 today >> " . $log_file_name . " 2>/dev/null";
  myWriteLog($cmd);
  $ausgabe = exec($cmd);
endif;

if (date("d") == "01" || $argv[2] == "test") {
  myWriteLog("Angabe im Feld 'Anzahl' für die Verteilung der Kostenpauschale: " .
    $db->getOne("SELECT COUNT(*) FROM jobprice, job WHERE jobprice.jb_id = job.jb_id AND jbprc_remark = 'Auftragspauschale' and jbprc_reference != 0 AND mt_sort = 2 AND jb_createtime >= '$from_date_month_sql' AND jb_createtime <= '$to___date_month_sql'")
  );

  $anzahl = $db->getOne("SELECT COUNT(*) FROM jobprice, job WHERE jobprice.jb_id = job.jb_id AND jbprc_remark = 'Auftragspauschale' and jbprc_reference != 0 AND mt_sort = 2 AND jb_createtime >= '$from_date_month_sql' AND jb_createtime <= '$to___date_month_sql'");
  $csv_contents =
    "Kostenpauschale IKEA Altona vom " . formDate($from_date_sql) . " bis " . formDate($to___date_sql) . ";;;;;" .
    ";;;;;" .
    ";Anzahl;Einzelpreis;Gesamt;Gesamt brutto;" .
    "von KoSt 8050 (800020 AG) bitte umbuchen auf;;Netto;Netto;;" .
    ";;;;;" .
    "KoSt 80501 Kostenart 803300 (AG);" . $anzahl . ";23,50;" . str_replace(".", ",", ($anzahl * 23.50)) . ";" . str_replace(".", ",", round(($anzahl * 23.50) * 1.19, 2)) . ";Transportschalterbesetzung" .
    ";;;;;" .
    "KoSt 80502 Kostenart 803300 (AG);" . $anzahl . ";14,75;" . str_replace(".", ",", ($anzahl * 14.75)) . ";" . str_replace(".", ",", round(($anzahl * 14.75) * 1.19, 2)) . ";Transportraumbesetzung 2. OG/UG" .
    ";;;;;" .
    "KoSt 80503 Kostenart 803300 (AG);" . $anzahl . ";3,80;" . str_replace(".", ",", ($anzahl * 3.80)) . ";" . str_replace(".", ",", round(($anzahl * 3.80) * 1.19, 2)) . ";Umlage Invest+Vorlauf" .
    ";;;;;" .
    "Summe;;;" . str_replace(".", ",", ($anzahl * 23.50 + $anzahl * 24.75 + $anzahl * 3.80)) . ";" . str_replace(".", ",", (round(($anzahl * 23.50) * 1.19, 2) + round(($anzahl * 14.75) * 1.19, 2) + round(($anzahl * 3.80) * 1.19, 2))) . ";";
  $fileHandle = @fopen($temp_csv_folder . "Kostenpauschale", 'w');
  @fwrite($fileHandle, $csv_contents);
  @fclose($fileHandle);
}

function init_jb_invtextArr($mode) {
  global $jb_invtextArr, $from_date_week_txt, $from_date_month_txt, $to___date_week_txt, $to___date_month_txt,
  $from_date_sql, $to___date_sql, $from_date_week_sql, $to___date_week_sql, $from_date_month_sql, $to___date_month_sql;

  $from_date_txt = $from_date_week_txt;
  $to___date_txt = $to___date_week_txt;
  $from_date_sql = $from_date_week_sql;
  $to___date_sql = $to___date_week_sql;
  if ($mode == "m"):
    $from_date_txt = $from_date_month_txt;
    $to___date_txt = $to___date_month_txt;
    $from_date_sql = $from_date_month_sql;
    $to___date_sql = $to___date_month_sql;
  endif;
  
  $jb_invtextArr = array(
    "return_kueche" =>
      "Rückvergütung für die durchgeführten Küchenmontagen                   \n" .
      "vom $from_date_txt bis zum $to___date_txt gemäß beiliegender Anlage           \n",
    "return_moebel" =>
      "Rückvergütung für die durchgeführten Möbelmontagen                    \n" .
      "vom $from_date_txt bis zum $to___date_txt gemäß beiliegender Anlage           \n",
    "diff_moebel" =>
      "Wir schreiben Ihnen die Preisdifferenz für Möbelmontagen im Zeitraum  \n" .
      "vom $from_date_txt bis zum $to___date_txt gemäß beiliegender Anlage gut       \n",
    "diff_lief" =>
      "Wir berechnen Ihnen die Preisdifferenz für Lieferaufträge im Zeitraum \n" .
      "vom $from_date_txt bis zum $to___date_txt gemäß beiliegender Anlage           \n",
    "return_lief" =>
      "Rückvergütung für die durchgeführten Lieferungen                      \n" .
      "vom $from_date_txt bis zum $to___date_txt gemäß beiliegender Anlage           \n",
    "EKS" =>
      "Wir berechnen Ihnen für die Einkaufsservices                          \n" .
      "bis einschließlich $to___date_txt gemäß beiliegender Anlage               \n",
    "SAMScourier" =>
      "Wir berechnen Ihnen für die SAMS-Lieferungen per Kurier               \n" .
      "bis einschließlich $to___date_txt gemäß beiliegender Anlage               \n"
  );
}

function do_SAMScourier($csc_id_related)
{
  global $db, $jb_invtextArr, $from_date_sql, $to___date_sql;

  $do_SAMScourier = false;

  $sql_query = 
    "SELECT jb_id, jb_totalprice" .
    " FROM job AS jb, genericdatacontainer AS gdc1, genericdatacontainer AS gdc2 " .
    " WHERE gdc1.gdc_obj_id = gdc2.gdc_obj_id AND gdc2.gdc_gen_fieldname = 'jb_service_job' AND" .
    " gdc1.gdc_obj_type = 'jb' AND jb.jb_id = gdc1.gdc_obj_id AND (jb_storno IS NULL OR jb_storno != 2)" .
    " AND gdc1.gdc_gen_fieldname = 'jb_express' AND gdc1.gdc_context = '' " .
    " AND jb.csc_id_related = $csc_id_related AND jb_createtime >= '$from_date_sql' AND jb_createtime <= '$to___date_sql' AND jb_totalprice > 0.0001 ORDER BY jb.jb_id";
//    " AND jb.csc_id_related = $csc_id_related AND jb.jb_id >= 290922 AND jb.jb_id <= 292960 ORDER BY jb.jb_id";
//echo "\n" . $sql_query . "\n";
  $jb_ids = array();
  $sum = 0;
  $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    $jb_ids[] = $row["jb_id"];
    $sum += $row["jb_totalprice"];
  endwhile;
  mysql_free_result($res);
  
  myWriteLog("do_SAMScourier($csc_id_related): \$sum = " . $sum . ", count(\$jb_ids) = " . count($jb_ids));
  if (count($jb_ids) > 0)
    mk_reference_job($sum, implode(",", $jb_ids), $csc_id_related, $jb_invtextArr["SAMScourier"], "express");
}

function do_EKS($csc_id_related)
{
  global $db, $jb_invtextArr, $from_date_sql, $to___date_sql;

  $sql_query = 
    "SELECT jbprc_price, jobprice.jb_id FROM jobprice, job" .
    " WHERE jobprice.jb_id = job.jb_id AND csc_id_related = $csc_id_related AND mt_sort = 2 AND jbprc_remark = 'Einkaufsservice' AND jbprc_reference = 0" .
//    " AND job.jb_id >= 290922 AND job.jb_id <= $jb_id_to ORDER BY job.jb_id";
    " AND jb_createtime >= '$from_date_sql' AND jb_createtime <= '$to___date_sql' AND jb_storno IS NULL ORDER BY job.jb_id";

  $jb_ids = array();
  $sum = 0;
  $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    $jb_ids[] = $row["jb_id"];
    $sum += $row["jbprc_price"];
  endwhile;
  mysql_free_result($res);

  myWriteLog("do_EKS($csc_id_related): \$sum = " . $sum . ", count(\$jb_ids) = " . count($jb_ids));
  if (count($jb_ids) > 0)
    mk_reference_job($sum, implode(",", $jb_ids), $csc_id_related, $jb_invtextArr["EKS"], "eks");
}

function do_diff($csc_id_related)
{
  global $db, $serviceLief, $serviceMont, $serviceNeuMont, $serviceKueMont, $serviceAbhol, $serviceAust, $jbpc_id_sav, $jb_ids_auftragspauschale, $jb_invtextArr, $from_date_sql, $to___date_sql;

  $sql_query = 
    "SELECT jbprc_price, jobprice.jb_id, mt_sort, jb_service, jbprc_remark FROM jobprice, job" .
    " WHERE jobprice.jb_id = job.jb_id AND jb_storno IS NULL AND jb_offer = 0 AND csc_id_related = $csc_id_related AND mt_sort IN (1, 2, 3) AND jbprc_remark != 'Einkaufsservice' AND jbprc_reference = 0 AND jb_createtime >= '$from_date_sql' AND jb_createtime <= '$to___date_sql'";
//    " WHERE jobprice.jb_id = job.jb_id AND csc_id_related = $csc_id_related AND mt_sort IN (1, 2, 3) AND jbprc_remark != 'Einkaufsservice' AND jbprc_reference = 0 AND jobprice.jb_id >= 290922 AND jobprice.jb_id <= 292960";
  $mt_sort_sums = array(array(0,0,0), array(0,0,0), array(0,0,0), array(0,0,0));
  $jb_ids = array(array(array(), array(), array()), array(array(), array(), array()), array(array(), array(), array()), array(array(), array(), array()));
  $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    $jb_service = mcConvertNum2Sel($row["jb_service"]);
    $is_addmont = $db->getOne("SELECT gdc_obj_id FROM genericdatacontainer WHERE gdc_content = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_id_addmont'") != "";
    // "Auftragspauschale" kommt immer in die Lieferungstabelle
    if (in_array($serviceLief, $jb_service) || in_array($serviceAbhol, $jb_service) || in_array($serviceAust, $jb_service) || $row["jbprc_remark"] == "Auftragspauschale"):
      $sum_index = 0;
      // Rückvergütungen für "(Sofa-)Montage" und "Installation" in die Montageliste, wenn Altona
      if ($row["mt_sort"] == 3 && $csc_id_related == 17051 && (!(strpos($row["jbprc_remark"], "Montage") === false) || !(strpos($row["jbprc_remark"], "Installation") === false))):
        $sum_index = 2;
        if (in_array($serviceKueMont, $jb_service)):
          $sum_index = 3;
        endif;      
      endif;
    elseif ($is_addmont && (in_array($serviceNeuMont, $jb_service) || in_array($serviceKueMont, $jb_service))):
      $sum_index = 1;
    elseif (in_array($serviceNeuMont, $jb_service)):
      $sum_index = 2;
    elseif (in_array($serviceKueMont, $jb_service)):    
      if (!(strpos($row["jbprc_remark"], "lfd. Meter Montage") === false))
        $sum_index = 3;
      else
        $sum_index = 2;
    endif;
    $mt_sort_sums[$sum_index][$row["mt_sort"] - 1] += $row["jbprc_price"];
    if ($row["jbprc_remark"] == "Auftragspauschale")
      $jb_ids_auftragspauschale[] = $row["jb_id"];
    else
      $jb_ids[$sum_index][$row["mt_sort"] - 1][] = $row["jb_id"];
  endwhile;
  mysql_free_result($res);

  for ($i = 0; $i <= 3; $i++):
    myWriteLog("do_diff($csc_id_related): \$i=$i, \$sum_diff = " . ($mt_sort_sums[$i][0] - $mt_sort_sums[$i][1]) .
      ", \$sum_credit = " . $mt_sort_sums[$i][0] . ", \$sum_debit = " . $mt_sort_sums[$i][1] . ", \$sum_return = " . $mt_sort_sums[$i][2] .
      ", count(array_merge(\$jb_ids[$i][1], \$jb_ids[$i][2])) = " . count(array_merge($jb_ids[$i][1], $jb_ids[$i][2])) .
      ", count(\$jb_ids[$i][2] = " . count($jb_ids[$i][2]));
    $auftragspauschale = false;
    if ($i == 0):
      $auftragspauschale = true;
      $jb_invtext_diff = $jb_invtextArr["diff_lief"];
      $mode_diff = "diff_lief";
      if ($csc_id_related == 1):
        $jb_invtext_return = $jb_invtextArr["return_lief"];
        $mode_return = "return_lief";
      endif;
    elseif ($i == 2):
      $jb_invtext_diff = $jb_invtextArr["diff_moebel"];
      $mode_diff = "diff_moebel";
      $jb_invtext_return = $jb_invtextArr["return_moebel"];
      $mode_return = "return_moebel";
    elseif ($i == 3):
      $jb_invtext_return = $jb_invtextArr["return_kueche"];
      $mode_return = "return_kueche";
    endif;  
      
    if (count(array_merge($jb_ids[$i][1], $jb_ids[$i][2])) > 0 && ($csc_id_related == 17051 || ($mode_diff == "diff_moebel" && $csc_id_related == 1)))
      mk_reference_job($mt_sort_sums[$i][1] - $mt_sort_sums[$i][0], implode(",", array_unique(array_merge($jb_ids[$i][0], $jb_ids[$i][1]))), $csc_id_related, $jb_invtext_diff, $mode_diff, $auftragspauschale);
    else
      $jbpc_id_sav = 99999999;
    if (count($jb_ids[$i][2]) > 0 && ($csc_id_related == 17051 || $csc_id_related == 1 || ($mode_return == "return_moebel" && $csc_id_related == 2)))
      mk_reference_job(0 - $mt_sort_sums[$i][2], implode(",", array_unique($jb_ids[$i][2])), $csc_id_related, $jb_invtext_return, $mode_return);
  endfor;
}

function mk_reference_job($sum, $jb_ids, $csc_id_related, $jb_invtext, $mode = '', $auftragspauschale = false)
{
  global $db, $log_file_name, $jbpc_id_sav, $jb_ids_auftragspauschale, $to___date_sql;

  if ($sum == 0)
    return;
    
  $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());

//cr_sid?
//take_time?
//"jb_cr_price", $jb_cr_price, "jb_cr_subprice", $jb_cr_price
// Rechnungstext?

  $sql_query = "INSERT INTO job (hq_id, vht_id, vht_id_real, csc_id_payer, jb_payment, jb_ordertime, cr_id, cr_sid, cr_id_order, jb_invtext, jb_status, jb_type, jb_finishtime, jb_fixprice, jb_totalprice, jb_subtotalprice, jb_createtime)" .
    " VALUES (1020, 2, 2, " . ($csc_id_related == 17051 ? 32862 : $csc_id_related) . ", 4, '" . $to___date_sql . "', 148, 'HH666', 148, '$jb_invtext', 2, 4, '" . $to___date_sql . "', $sum, $sum, $sum, NOW())";
  mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  $res = mysql_query("SELECT LAST_INSERT_ID()");
  $row = mysql_fetch_array($res);
  $jb_id = $row[0];
  mysql_free_result($res);
  $sql_query = "INSERT INTO genericdatacontainer (gdc_obj_type, gdc_obj_id, gdc_gen_fieldname, gdc_content, gdc_context)" .
    " VALUES ('jb', $jb_id, 'collection_number', $jbpc_id, '')";
  mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  $sql_query = "INSERT INTO tour (jb_id, ad_id, tr_sort, tr_comp, tr_hsno, csc_id, tr_status, tr_ware_from_to, tr_finishtime)" .
    " VALUES($jb_id, 443082, 1, 'IKEA', '10', " . ($csc_id_related == 17051 ? 32862 : $csc_id_related) . ", 0, 0, '0000-00-00 00:00:00')";
  mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  $sql_query = "INSERT INTO tourservice (jb_id, tr_sort, srv_id, trs_srv_name, srvt_id, trs_srvt_name, trs_price, trs_discount, csc_id)" .
    " VALUES ($jb_id, 0, 0, 'Fixpreis', 0, '', $sum, 0, " . ($csc_id_related == 17051 ? 32862 : $csc_id_related) . ")";
  mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());

  if ($mode == "express"):
    $jb_idsArr = array_filter(explode(",", $jb_ids));
    foreach ($jb_idsArr as $jb_id_tmp){
      $sql_query =
        "INSERT INTO jobpayment (jb_id, tr_sort, csc_id, jbp_mode, jbp_price, jbp_bookingtime, jbp_counter, jbpc_id) VALUES " .
        "($jb_id_tmp, 2, $csc_id_related, 0, 0.0001, NOW(), 2, '$jbpc_id')";
      mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
   	}
    $sql_query =
      "UPDATE genericdatacontainer SET gdc_context = '$jbpc_id'" .
      " WHERE gdc_obj_type = 'jb' AND gdc_obj_id IN ($jb_ids) AND gdc_gen_fieldname = 'jb_express' AND gdc_context = '' ";
  elseif ($mode == "eks"):
    $sql_query =
      "SELECT gdc_content FROM genericdatacontainer, job" .
      " WHERE job.jb_id = genericdatacontainer.gdc_obj_id AND genericdatacontainer.gdc_gen_fieldname = 'jb_id_eks' AND job.jb_id IN ($jb_ids)";
    $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
    while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
      $sql_query =
        "INSERT INTO jobpayment (jb_id, tr_sort, csc_id, jbp_mode, jbp_price, jbp_bookingtime, jbp_counter, jbpc_id) VALUES " .
        "(" . $row['gdc_content'] . ", 2, $csc_id_related, 0, 0.0001, NOW(), 2, '$jbpc_id')";
      mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
    endwhile;
    mysql_free_result($res);

    $sql_query = 
      "UPDATE jobprice SET jbprc_reference = $jbpc_id" .
      " WHERE jb_id IN ($jb_ids) AND mt_sort = 2 AND jbprc_remark = 'Einkaufsservice'";
  elseif ($mode == "diff_lief" || $mode == "diff_moebel"):
    $where_clause = "";
    if ($mode == "diff_moebel")
      $where_clause = "AND jbprc_remark != 'lfd. Meter Montage'";
    $sql_query = 
      "UPDATE jobprice SET jbprc_reference = $jbpc_id" .
      " WHERE jb_id IN ($jb_ids) AND mt_sort IN (0,1,2,3) AND jbprc_remark != 'Einkaufsservice' AND jbprc_remark != 'Auftragspauschale' $where_clause";
    $jbpc_id_sav = $jbpc_id;
    $jbpc_id2 = 99999999;
  elseif ($mode == "return_moebel" || $mode == "return_kueche" || $mode == "return_lief"):
    $where_clause = "";
    if ($mode == "return_moebel")
      $where_clause = "AND jbprc_remark != 'lfd. Meter Montage'";
    if ($mode == "return_kueche")
      $where_clause = "AND jbprc_remark = 'lfd. Meter Montage'";
    $sql_query = 
      "UPDATE jobprice SET jbprc_reference = $jbpc_id" .
      " WHERE jb_id IN ($jb_ids) AND mt_sort IN (2,3) AND jbprc_remark != 'Einkaufsservice' AND jbprc_remark != 'Auftragspauschale' $where_clause";
    $jbpc_id2 = $jbpc_id_sav;
    if ($jbpc_id2 == "")    
      $jbpc_id2 = 99999999;
  endif;
//echo $sql_query . "\n";
  mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());

  if ($auftragspauschale && count($jb_ids_auftragspauschale) > 0):
    $sql_query =
      "SELECT DISTINCT (jb_id) FROM genericdatacontainer, tourarticle" .
      " WHERE gdc_obj_type = 'trat' AND gdc_gen_fieldname = 'packet' AND gdc_obj_id = trat_id AND jb_id IN (" . implode(",", $jb_ids_auftragspauschale) . ")";
    $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
    while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
      $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'] . ", 2, $csc_id_related, 0, 0.0001, NOW(), 2, '$jbpc_id')";
      mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
      // jb_totalprice für Statistiken reparieren
      $sql_query =
        "UPDATE job, jobprice SET jb_totalprice = jbprc_price WHERE jobprice.jb_id = job.jb_id AND mt_sort = 6 AND jbprc_remark = 'Gesamt' AND job.jb_id = " . $row['jb_id'];
      mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
    endwhile;
    mysql_free_result($res);

    $sql_query = 
      "UPDATE jobprice SET jbprc_reference = $jbpc_id" .
      " WHERE jb_id IN (" . implode(",", $jb_ids_auftragspauschale) . ") AND mt_sort IN (0,1,2,3) AND jbprc_remark = 'Auftragspauschale'";
    mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
    $jb_ids_auftragspauschale = array();
  endif;

  myWriteLog("mk_reference_job(): \$jbpc_id = " . $jbpc_id . ", \$jb_id = " . $jb_id . ", \$csc_id_related = " . $csc_id_related .  
    ", \$sum = $sum, \$jb_invtext = $jb_invtext, \$mode = $mode, \$auftragspauschale = $auftragspauschale");

  $cmd = "php ../export/post_export.php $mode $jbpc_id $jbpc_id2 >> " . $log_file_name . " 2>&1";
  myWriteLog($cmd);
  $ausgabe = exec($cmd);

//  $f_fileName = date("YmdHi") . ".101";
//  if ($sum >= 0):
//    $f_exp_category = "5";
//    $f_fileName .= ".abr";
//  endif;
//  if ($sum < 0):
//    $f_exp_category = "6";
//    $f_fileName .= ".gut";
//  endif;
//  $f_parname = "RETRANS Abrechnungen";
//  $f_parname_export = "RETRANS Abrechnungen";
//  $specialJbPayment = "4";

//  myWriteLog("mk_reference_job(): " . "\$f_fileName = " . $f_fileName . ", \$f_exp_category = " . $f_exp_category . ", \$f_parname = " . $f_parname . ", \$f_parname_export = " . $f_parname_export . ", \$specialJbPayment = " . $specialJbPayment . ", \$jbpc_id = " . $jbpc_id);

//  echo $f_fileName . "\n";
//  echo $f_exp_category . "\n";
//  echo $f_parname . "\n";
//  echo $f_parname_export . "\n";
//  echo $specialJbPayment . "\n";
//  echo $jbpc_id . "\n";
}

function formDate($sqlDate)
{
	if (trim($sqlDate) == "")
	  return "";
  return substr($sqlDate, 8, 2) . "." . substr($sqlDate, 5, 2) . "." . substr($sqlDate, 0, 4);
}

function formSum($sum)
{
	return str_replace(".", ",", $sum);
}

function myWriteLog($log_text) {
  global $log_file_name;

  $fileHandle = @fopen($log_file_name, 'a');
  @fwrite($fileHandle, "[" . date("Y-m-d H:i:s") . "] " . $log_text .  "\n");
  @fclose($fileHandle);
  return;
}