<?php
/*=======================================================================
 *
 * post_export.php
 *
 * Autor: Carsten Annacker
 *
 =======================================================================*/

// pre_export für Servicekurier (Schnelsen), EKS (Moorfleet), Preisdifferenzen und Rückvergütungen (Altona)
// Sammelnummer in gdc eintragen:  gdc_obj_type "jb" gdc_obj_id "$jb_id" gdc_gen_fieldname "collection_number" context $collection_number 

// JB_ID_ADDMONT - AUFTRÄGE FEHLEN NOCH BEI SAMS (ANALOG BUSINESSCARD!!!)  
// Lagerkosten in Businesscard extra ausweisen (in SAMS nicht vorhanden?)
// Zusatz-Nachmontage extra unter Montage ausweisen
// Mehrere job_payment beachten!

$do_xls = true;
$test = false;
$test_mail = false;

error_reporting(E_ALL ^ (E_DEPRECATED | E_NOTICE));
if ($test)
  include_once ("../include/inc_mtf_func.inc.test.php");
else
  include_once ("../include/inc_mtf_func.inc.php");
include_once("../include/email/htmlMimeMail.php");

$log_file_name = "../log/prepost_export_";
$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'");
$paymentSAMScourier = 91;
$paymentEKS = 92;
$paymentDiff = 93;
$paymentReturn = 94;

$cs_ids_nord = array("1", "2", "17093");
$cs_ids_sued = array("3534", "3540");
$cs_id_names = array(
     "1" => "Schnelsen",
     "2" => "Moorfleet",
 "17093" => "Altona"
);
//$sender = "no-reply@hansetrans.de";
$sender = "support@assecutor.de";

if ($test_mail):
  $recipients = array(
       "1" => array("ca@assecutor.de"),
       "2" => array("ca@assecutor.de"),
   "17093" => array("ca@assecutor.de"),
    "3534" => array("ca@assecutor.de"),
    "3540" => array("ca@assecutor.de")
  );
  $bcc = "";
else:
  $recipients = array(
  //     "1" => array("ikea_schnelsen-abr@hansetrans.de"),
  //     "2" => array("ikea-moorfleet-abr@hansetrans.de"),
  // "17093" => array("ikea-altona-abr@hansetrans.de"),
       "1" => array("Livia.Kroemer@hansetrans.de","stefanie.bruemmerstedt@hansetrans.de"),
       "2" => array("Livia.Kroemer@hansetrans.de","stefanie.bruemmerstedt@hansetrans.de"),
   "17093" => array("Livia.Kroemer@hansetrans.de","stefanie.bruemmerstedt@hansetrans.de"),
    "3534" => array("abrechnung-leonberg@walter-moebeltransport.de"),
    "3540" => array("abrechnung-leonberg@walter-moebeltransport.de")
  );
  $bcc = "ca@assecutor.de";
endif;

$subjects = array(
  $paymentSAMS => "Serviceaufträge Hansetrans",
  $paymentSAMScourier => "Serviceaufträge Kurier Hansetrans",
  $paymentBC => "Business Card Hansetrans",
  $paymentEKS => "EKS Hansetrans",
  $paymentDiff => "Preisdifferenzen Hansetrans",
  $paymentReturn => "Rückvergütung Hansetrans"
);
$mailtexts = array(
  $paymentSAMS => "Im Anhang finden Sie die Serviceaufträge",
  $paymentSAMScourier => "Im Anhang finden Sie die Serviceaufträge, die per Kurier ausgeführt wurden",
  $paymentBC => "Im Anhang finden Sie die per Business Card bezahlten Aufträge",
  $paymentEKS => "Im Anhang finden Sie die Einkaufsservices",
  $paymentDiff => "Im Anhang finden Sie die Liste der Preisdifferenzen",
  $paymentReturn => "Im Anhang finden Sie die Liste der Rückvergütungen"
);
$xlsfilename_prefixes = array(
  $paymentSAMS => "service",
  $paymentSAMScourier => "servicekurier",
  $paymentBC => "businesscard",
  $paymentEKS => "eks",
  $paymentDiff => "preisdifferenz",
  $paymentReturn => "rueckverguetung"
);

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

//$argv[1] = '563'; // SAMS
//$argv[1] = '564'; // SAMS
//$argv[1] = '565'; // BC
//$argv[1] = 'dummy';
//$argv[2] = '81001029'; // Diff und Return
//$argv[1] = '566'; // Service Lief 81001062, Service Mont 81001063
//$argv[2] = '567'; // BC 81001064
//$argv[2] = '81001065'; // servicecourier
//$argv[2] = '81001066'; // EKS
mcIsSet($argv[1]);
mcIsSet($argv[2]);
myWriteLog("post_export.php: \$argv[1] = " . $argv[1] . ", \$argv[2] = " . $argv[2]);
if (strlen($argv[2]) != 8):
  $exl_rows = $db->getOne("SELECT exl_rows FROM exportlog WHERE exl_id = " . $argv[1]);
  $exl_exporttime = $db->getOne("SELECT exl_exporttime FROM exportlog WHERE exl_id = " . $argv[1]);
  $exl_rowsArr = array_filter(explode(",", $exl_rows));
  foreach ($exl_rowsArr as $exl_row) {
    $sql_query = "SELECT jbpc_id, cs_id, jbpc_mode FROM jobpaymentcollection WHERE jbpc_id IN ($exl_row) AND jbpc_mode IN ($paymentSAMS, $paymentBC)";
    $res = $db->query($sql_query);
    if (DB::isError($res)) die ("$PHP_SELF: " . $res->getMessage());
    while ($row = $res->fetch_assoc()):
      $format = "sued";
      if (in_array($row["cs_id"], $cs_ids_nord))
        $format = "nord_service";
      if($row["jbpc_mode"] == $paymentSAMS)
//        send_csv_mail(do_SAMS($row["jbpc_id"]), $row["jbpc_id"], $paymentSAMS, $row["cs_id"]);
        send_csv_mail(do_SAMS($exl_row, $row["cs_id"]), $row["jbpc_id"], $paymentSAMS, $row["cs_id"], $format);
      if($row["jbpc_mode"] == $paymentBC)
//        send_csv_mail(do_BC($row["jbpc_id"]), $row["jbpc_id"], $paymentBC, $row["cs_id"]);
        send_csv_mail(do_BC($exl_row, $row["cs_id"]), $row["jbpc_id"], $paymentBC, $row["cs_id"], $format);
    endwhile;
  }
  $res->free();
else:
  $jb_id_export = $db->getOne("SELECT gdc_obj_id FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_gen_fieldname = 'collection_number' AND gdc_content = '" . $argv[2] . "'");
  $jb_exporttime = $db->getOne("SELECT jb_finishtime FROM job WHERE jb_id = $jb_id_export");
  $cs_id_export = $db->getOne("SELECT cs_id FROM costcenter WHERE csc_id = " . $db->getOne("SELECT csc_id_payer FROM job WHERE jb_id = $jb_id_export"));
  if ($cs_id_export == 32943)
    $cs_id_export = 17093;
  myWriteLog("post_export.php: \$jb_id_export = " . $jb_id_export . ", \$jb_exporttime = " . $jb_exporttime . ", \$cs_id_export = " . $cs_id_export);

  switch ($argv[1]) {
    case "express":
      send_csv_mail(do_SAMScourier($argv[2]), $argv[2], $paymentSAMScourier, $cs_id_export, "nord_service");
      break;
    case "eks":
      send_csv_mail(do_EKS($argv[2]), $argv[2], $paymentEKS, $cs_id_export, "nord_service");
      break;
    case "diff_lief":
    case "diff_moebel":
      if ($cs_id_export == 17093)
        send_csv_mail(do_diff($argv[2], "diff", $argv[3], $cs_id_export), $argv[2], $paymentDiff, $cs_id_export, "nord_diff");
      else
        send_csv_mail(do_diff($argv[2], "diff", $argv[3], $cs_id_export), $argv[2], $paymentDiff, $cs_id_export, "nord_diff_special");
      break;
    case "return_moebel":
    case "return_kueche":
    case "return_lief":
      if ($cs_id_export == 17093)
        send_csv_mail(do_diff($argv[2], "return", $argv[3], $cs_id_export), $argv[2], $paymentReturn, $cs_id_export, "nord_return");
      else
        send_csv_mail(do_diff($argv[2], "return", $argv[3], $cs_id_export), $argv[2], $paymentReturn, $cs_id_export, "nord_return_special");
  }
endif;

function send_csv_mail($csv_contentsArr, $jbpc_id, $paymentType, $cs_id, $format)
{
  global $temp_csv_folder, $sender, $bcc, $recipients, $subjects, $mailtexts, $log_file_name, $xlsfilename_prefixes, $cs_ids_nord, $do_xls, $cs_id_names,
    $paymentSAMS, $paymentBC, $paymentEKS, $paymentSAMScourier;

  $xls_csv_files = "";

  for ($i = 0; $i < count($csv_contentsArr); $i += 2) {
//    $csv_file_name = $jbpc_id . ".csv";
    if ($csv_contentsArr[$i+1] != ""):
      $file_name = $cs_id_names[$cs_id] . "_" . $xlsfilename_prefixes[$paymentType] . "_" . $csv_contentsArr[$i] . "_101" . $jbpc_id;
      $csv_file_name = $file_name . ".csv";
      if ($do_xls):
        $csv_file_name = $csv_contentsArr[$i] . ".csv";
        if($paymentType == $paymentSAMS || $paymentType == $paymentBC || $paymentType == $paymentEKS || $paymentType == $paymentSAMScourier)
          $file_name = $cs_id_names[$cs_id] . "_" . $xlsfilename_prefixes[$paymentType] . "_101" . $jbpc_id;
      endif;
      myWriteLog("post_export.php: writing " . $temp_csv_folder . $csv_file_name);
      $fileHandle = @fopen($temp_csv_folder . $csv_file_name, 'w');
      @fwrite($fileHandle, $csv_contentsArr[$i+1]);
      @fclose($fileHandle);
      $xls_csv_files .= $temp_csv_folder . $csv_file_name . " ";
    endif;
  }
//die();  

  $xls_file_name = "101" . $jbpc_id . ".xls";
  if (in_array($cs_id, $cs_ids_nord)):
    $xls_file_name = $file_name . ".xls";
    if (($format == "diff" || $format == "return") && ($cs_id == 1 || $cs_id == 2))
      $format .= "_special";
  endif;
  $cmd = "../tools/mycsv2xls.pl " . $xls_csv_files . " '$format' " . $temp_csv_folder . $xls_file_name . " >> " . $log_file_name . date("Ym") . ".log" . " 2>&1 &";
  myWriteLog($cmd);
  if ($do_xls):
    @unlink($temp_csv_folder . $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));
  endif;
  myWriteLog("sending mail for jbpc_id = '$jbpc_id' ... (paymentType = $paymentType, cs_id = $cs_id)");
  $mailObj = new htmlMimeMail();
  $mailObj->setFrom($sender);
//    $mailObj->setCc("leier@stadtbote.de");
  if (trim($bcc) != "")
    $mailObj->setBcc($bcc);
  $mailObj->setSubject($subjects[$paymentType]);
  $mailObj->setText($mailtexts[$paymentType]);
  if ($do_xls)
    $mailObj->addAttachment($mailObj->getFile($temp_csv_folder . $xls_file_name), $xls_file_name);
  else
    $mailObj->addAttachment($mailObj->getFile($temp_csv_folder . $csv_file_name), $csv_file_name);
  $mailResult = $mailObj->send($recipients[$cs_id], 'smtp');
  myWriteLog("result of sending mail for jbpc_id = '$jbpc_id': '" . $mailResult . "' (paymentType = $paymentType, cs_id = $cs_id)");
}

function do_SAMS($jbpc_id, $cs_id)
{
  global $db, $exl_exporttime, $serviceLief, $serviceMont, $serviceNeuMont, $serviceKueMont, $serviceAbhol, $serviceAust, $cs_ids_nord, $cs_ids_sued;

  if (in_array($cs_id, $cs_ids_sued))
    $csv_text =  
      "Anlage zur Auftrags-Nr. 101$jbpc_id;vom " . formDate($exl_exporttime) . ";Kunden-;Auftrags-;SAMS-;Auftrags-;Erfüll-;Lieferung;Lieferung;Lieferung;Lieferung;EKS;Sofa-;Abholung;SAMS-;SAMS-;Austausch;Abholung;Rücknahme;Lager-;Montage;Annehmer\n" .
      "Pos;KartenNr;name;Nr.;Nr.;datum;datum;bis WW 500,-;bis WW 1500,-;ab WW 1501,-;größer 3000kg;;montage;Austausch;Austausch;Lieferung;Palette;Palette;Matratze;geld; ;\n";
  if (in_array($cs_id, $cs_ids_nord)):
    $csv_textArr = array("Lieferung", "", "Montage", "");
    $csv_textArr[1] = $csv_textArr[3] = 
      "RgNr;Datum;Kaufvertrag;OrderID;BelegNr;KartenNr;Warenwert;Preis\n";
  endif;

  $sumMont = 0;
  $sumAbhol = 0;
  $sumAust = 0;
  $sumLief = 0;
  $cnt = 1;
  $sum1_jbp_price_sum = 0;
  $sum3_jbp_price_sum = 0;
  $sql_query = 
    "SELECT tr.tr_comp, jb.jb_id, jb.jb_id_parent, tr.tr_commission_no, jb.jb_createtime, jb.jb_ordertime, SUM(jbp.jbp_price) AS jbp_price_sum, jb.jb_service, usr.usr_name" .
    " FROM job AS jb, tour AS tr, jobpayment AS jbp LEFT JOIN user AS usr ON jb.emp_id = usr.usr_id" .
    " WHERE jb.jb_id = jbp.jb_id AND jbp.jbpc_id IN ($jbpc_id)" .
    " AND jb.jb_id = tr.jb_id AND tr.tr_sort = 2 AND NOT (tr.tr_comp LIKE '%Test%') GROUP BY jbp.jb_id";
//echo "\n" . $sql_query . "\n\n";die();
  $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    $jbp_price_sum = bruttoSum($row["jbp_price_sum"]);
    $jb_service = mcConvertNum2Sel($row["jb_service"]);
    $type_str = "";
    $warenwert = $db->getOne("SELECT SUM(trat_price) FROM tourarticle WHERE jb_id = " . $row["jb_id"]); // Nur Nord
    if (in_array($serviceAbhol, $jb_service)):
      $type_str = $jbp_price_sum . ";0;0;0;0;0;0;0";
      $sumAbhol += $jbp_price_sum;
    elseif (in_array($serviceAust, $jb_service)):
      $type_str = "0;" . $jbp_price_sum . ";0;0;0;0;0;0";
      $sumAust += $jbp_price_sum;
    elseif (in_array($serviceLief, $jb_service)):
      $type_str = "0;0;" . $jbp_price_sum . ";0;0;0;0;0";
      $sumLief += $jbp_price_sum;
    elseif (in_array($serviceMont, $jb_service)):      
//      if ($jbp_price_sum != 40 && $jbp_price_sum != 45):
        $type_str = "0;0;0;0;0;0;0;" . $jbp_price_sum;
        $sumMont += $jbp_price_sum;
//      endif;
    endif;
    if ($type_str != "" && in_array($cs_id, $cs_ids_sued)):
//      $log_createtime = $db->getOne("SELECT log_createtime FROM phoenix_log.log WHERE (log.jb_id = '" . $row["jb_id"] . "' OR log.jb_id = '" . $row["jb_id_parent"] . "') AND logo_id = 1");    
      $csv_text .= $cnt++ . ";;" . str_replace(";", "", $row["tr_comp"]) . ";" . $row["jb_id"] . ";" . check_str($row["tr_commission_no"]) . ";" . 
        formDate($row["jb_createtime"]) . ";" . formDate($row["jb_ordertime"]) . ";0;0;0;0;0;0;" . $type_str . ";" . $row["usr_name"] . "\n";
    endif;  
    if (in_array($cs_id, $cs_ids_nord)):
      $csv_text = "101" . $jbpc_id . ";" . formDate($row["jb_createtime"]) . ";" . $row["jb_id"] . ";" .
        check_str($row["tr_commission_no"]) . ";;;" . $warenwert . ";" . $jbp_price_sum . "\n";
      if (!in_array($serviceMont, $jb_service)):
        $csv_textArr[1] .= $csv_text;
        $sum1_jbp_price_sum += $jbp_price_sum;
      else:
        $csv_textArr[3] .= $csv_text;
        $sum3_jbp_price_sum += $jbp_price_sum;
      endif;
    endif;
  endwhile;
  mysql_free_result($res);
   
  myWriteLog("do_SAMS($jbpc_id, $cs_id)");
  if (in_array($cs_id, $cs_ids_sued)):
    $csv_text .= "Summe;;;;;;;0;0;0;0;0;0;" . $sumAbhol . ";" . $sumAust . ";" . $sumLief . ";0;0;0;0;" . $sumMont . ";\n";
    return array("Service", $csv_text);
  else:
    $csv_textArr[1] .= ";;;;;;;" . $sum1_jbp_price_sum . "\n";
    $csv_textArr[3] .= ";;;;;;;" . $sum3_jbp_price_sum . "\n";
    return $csv_textArr;
  endif;
}

function do_SAMScourier($jbpc_id)
{
  global $db;

  $csv_text_headline = 
    "RgNr;Datum;Kaufvertrag;OrderID;BelegNr;KartenNr;Warenwert;Preis\n";
  $csv_text = $csv_text_headline;
  $sql_query = 
    "SELECT tr.tr_commission_no, jb.jb_id, jb.jb_createtime, jb.jb_totalprice" .
    " FROM job AS jb, tour AS tr, genericdatacontainer AS gdc" .
    " WHERE jb.jb_id = gdc.gdc_obj_id AND gdc_obj_type = 'jb' AND gdc_gen_fieldname = 'jb_express' AND gdc.gdc_context IN ($jbpc_id) " .
    " AND jb.jb_id = tr.jb_id AND tr.tr_sort = 2 ORDER BY jb.jb_id";
//echo "\n" . $sql_query . "\n";
  
  $jb_totalprice_sum = 0;
  $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    $jb_totalprice = bruttoSum($row["jb_totalprice"]);
    $lieferwarenwert = $db->getOne("SELECT SUM(trat_price) FROM tourarticle WHERE jb_id = " . $row["jb_id"]);
    $csv_text .= "101" . $jbpc_id . ";" . formDate($row["jb_createtime"]) . ";" . $row["jb_id"] . ";;" .
      check_str($row["tr_commission_no"]) . ";;" . $lieferwarenwert . ";" . $jb_totalprice . "\n";
    $jb_totalprice_sum += $jb_totalprice;
  endwhile;
  mysql_free_result($res);
  $csv_text .= ";;;;;;;" . $jb_totalprice_sum . "\n";
 
  myWriteLog("do_SAMScourier($jbpc_id)");
//  return array("Lieferung", $csv_text, "Montage", $csv_text_headline);
  return array("Servicekurier", $csv_text);
}

function do_BC($jbpc_id, $cs_id)
{
  global $db, $exl_exporttime, $serviceLief, $serviceMont, $serviceNeuMont, $serviceKueMont, $serviceAbhol, $serviceAust, $cs_ids_nord, $cs_ids_sued;

  if (in_array($cs_id, $cs_ids_sued))
    $csv_text = 
      "Anlage zur Auftrags-Nr. 101$jbpc_id;vom " . formDate($exl_exporttime) . ";Kunden-;Auftrags-;SAMS-;Auftrags-;Erfüll-;Lieferung;Lieferung;Lieferung;Lieferung;EKS;Sofa-;Abholung;SAMS-;SAMS-;Austausch;Abholung;Rücknahme;Lager-;Montage;Annehmer\n" .
      "Pos;KartenNr;name;Nr.;Nr.;datum;datum;bis WW 500,-;bis WW 1500,-;ab WW 1501,-;größer 3000kg;;montage;Austausch;Austausch;Lieferung;Palette;Palette;Matratze;geld; ;\n";
  if (in_array($cs_id, $cs_ids_nord)):
    $csv_textArr = array("Lieferung", "", "Montage", "");
    $csv_textArr[1] = $csv_textArr[3] = 
      "RgNr;Datum;Kaufvertrag;OrderID;BelegNr;KartenNr;Warenwert;Preis\n";
  endif;

  $sumMont = 0;
  $sumAbhol = 0;
  $sumAust = 0;
  $sumLief500 = 0;
  $sumLief1500 = 0;
  $sumLief1501 = 0;
  $sumStock = 0;
  $cnt = 1;

// auch auf 'jb_family_card' prüfen, falls BC-Nummer im falschen Feld eingetragen wurde
  $sql_query = 
    "SELECT tr.tr_comp, tr.tr_commission_no, jb.jb_id, jb.jb_id_parent, gdc.gdc_content, jb.jb_createtime, jb.jb_ordertime, SUM(jbp.jbp_price) AS jbp_price_sum, jb.jb_service, usr.usr_name" .
    " FROM job AS jb, tour AS tr, jobpayment AS jbp, genericdatacontainer AS gdc LEFT JOIN user AS usr ON jb.emp_id = usr.usr_id" .
    " WHERE jb.jb_id = jbp.jb_id AND jbp.jbpc_id IN ($jbpc_id) AND gdc.gdc_obj_type = 'jb' AND (jb.jb_id = gdc.gdc_obj_id OR jb.jb_id_parent = gdc.gdc_obj_id) AND (gdc.gdc_gen_fieldname = 'jb_business_card' OR gdc.gdc_gen_fieldname = 'jb_family_card') " .
    " AND jb.jb_id = tr.jb_id AND tr.tr_sort = 2 GROUP BY jbp.jb_id";
//echo "\n" . $sql_query . "\n";
  
  $sum1_jbp_price_sum = 0;
  $sum3_jbp_price_sum = 0;
  $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    $jbp_price_sum = bruttoSum($row["jbp_price_sum"]);
    $stock_costs = $db->getOne("SELECT jbc_totalprice FROM jobcalculator WHERE jb_id = " . $row["jb_id"] . " AND srvt_name = '0003:par_01'");
    if ($stock_costs == "")
      $stock_costs = 0;
    if ($stock_costs > 0):
      $jbp_price_sum = bruttoSum(($row["jbp_price_sum"] - $stock_costs));
      $stock_costs = bruttoSum($stock_costs);
      $sumStock += $stock_costs;
    endif;
    $jb_service = mcConvertNum2Sel($row["jb_service"]);
    $type_str = "0;0;0;0;0;0;$stock_costs;0";
    if (in_array($serviceAbhol, $jb_service)):
      $type_str = $jbp_price_sum . ";0;0;0;0;0;$stock_costs;0";
      $sumAbhol += $jbp_price_sum;
    elseif (in_array($serviceAust, $jb_service)):
      $type_str = "0;" . $jbp_price_sum . ";0;0;0;0;$stock_costs;0";
      $sumAust += $jbp_price_sum;
    elseif (in_array($serviceMont, $jb_service)):
      $type_str = "0;0;0;0;0;0;$stock_costs;" . $jbp_price_sum;
      $sumMont += $jbp_price_sum;
    endif;
    $type_str1 = "0;0;0;0";
    if (in_array($serviceLief, $jb_service)):
      $lieferwarenwert = $db->getOne("SELECT SUM(trat_price) FROM tourarticle WHERE jb_id = " . $row["jb_id"]);
      if($lieferwarenwert <= 500) {
        $type_str1 = $jbp_price_sum . ";0;0";
        $sumLief500 += $jbp_price_sum;
      } elseif($lieferwarenwert <= 1500) {
        $type_str1 = "0;" . $jbp_price_sum . ";0";
        $sumLief1500 += $jbp_price_sum;
      } else {
        $type_str1 = "0;0;" . $jbp_price_sum;
        $sumLief1501 += $jbp_price_sum;
      }
      $lieferwarengewicht = $db->getOne("SELECT SUM(trat_weight) FROM tourarticle WHERE jb_id = " . $row["jb_id"]);
      if($lieferwarengewicht > 3000)
        $type_str1 .= ";" . $jbp_price_sum;
      else
        $type_str1 .= ";0";
    endif;

//    $log_createtime = $db->getOne("SELECT log_createtime FROM phoenix_log.log WHERE (log.jb_id = '" . $row["jb_id"] . "' OR log.jb_id = '" . $row["jb_id_parent"] . "') AND logo_id = 1");
    if (in_array($cs_id, $cs_ids_sued))
      $csv_text .= $cnt++ . ";" . $row["gdc_content"] . ";" . str_replace(";", "", $row["tr_comp"]) . ";" . $row["jb_id"] . ";;" . 
        formDate($row["jb_createtime"]) . ";" . formDate($row["jb_ordertime"]) . ";" . $type_str1 . ";0;0;" . $type_str . ";" . $row["usr_name"] . "\n";
    if (in_array($cs_id, $cs_ids_nord)):
      $csv_text = "101" . $jbpc_id . ";" . formDate($row["jb_createtime"]) . ";" . $row["jb_id"] . ";;" .
        check_str($row["tr_commission_no"]) . ";" . $row["gdc_content"] . ";" . $lieferwarenwert . ";" . bruttoSum($row["jbp_price_sum"]) . "\n";
      if (!in_array($serviceMont, $jb_service)):
        $csv_textArr[1] .= $csv_text;
        $sum1_jbp_price_sum += bruttoSum($row["jbp_price_sum"]);
      else:
        $csv_textArr[3] .= $csv_text;
        $sum3_jbp_price_sum += bruttoSum($row["jbp_price_sum"]);
      endif;
    endif;
  endwhile;
  mysql_free_result($res);
  
  myWriteLog("do_BC($jbpc_id, $cs_id)");
  if (in_array($cs_id, $cs_ids_sued)):
    $csv_text .= "Summe;;;;;;;" . $sumLief500 . ";" . $sumLief1500 . ";" . $sumLief1501 . ";0;0;0;" . $sumAbhol . ";" . $sumAust . ";0;0;0;0;" . $sumStock . ";" . $sumMont . ";\n";
    return array("BusinessCard", $csv_text);
  else:
    $csv_textArr[1] .= ";;;;;;;" . $sum1_jbp_price_sum . "\n";
    $csv_textArr[3] .= ";;;;;;;" . $sum3_jbp_price_sum . "\n";
//print_r($csv_textArr);die();
    return $csv_textArr;
  endif;
}

function do_EKS($jbpc_id)
{
  global $db;

  $csv_text = 
    "RgNr;Datum;Kaufvertrag;OrderID;BelegNr;KartenNr;Warenwert;Preis\n";

  $sql_query = 
    "SELECT tr.tr_commission_no, jb.jb_id, jb.jb_createtime, jbprc.jbprc_price" .
    " FROM job AS jb, tour AS tr, jobprice AS jbprc" .
    " WHERE jb.jb_id = jbprc.jb_id AND jbprc.jbprc_reference IN ($jbpc_id) " .
    " AND jb.jb_id = tr.jb_id AND tr.tr_sort = 2 AND jbprc.mt_sort = 2 AND jbprc.jbprc_remark = 'Einkaufsservice' ORDER BY jb.jb_id";
//echo "\n" . $sql_query . "\n";
  
  $jbprc_price_sum = 0;
  $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    $jbprc_price = bruttoSum($row["jbprc_price"]);
    $lieferwarenwert = $db->getOne("SELECT SUM(trat_price) FROM tourarticle WHERE jb_id = " . $row["jb_id"]);
    $csv_text .= "101" . $jbpc_id . ";" . formDate($row["jb_createtime"]) . ";" . $row["jb_id"] . ";;" .
      check_str($row["tr_commission_no"]) . ";;" . $lieferwarenwert . ";" . $jbprc_price . "\n";
    $jbprc_price_sum += $jbprc_price;
  endwhile;
  mysql_free_result($res);
  $csv_text .= ";;;;;;;" . $jbprc_price_sum . "\n";
  
  myWriteLog("do_EKS($jbpc_id)");
  return array("Eks", $csv_text);
}

function do_diff($jbpc_id, $what, $jbpc_id2, $cs_id)
{
  global $db, $jb_exporttime, $serviceLief, $serviceMont, $serviceNeuMont, $serviceKueMont, $serviceAbhol, $serviceAust, $paymentDiff;
  $csv_text_diff = array("Lieferung", "", "Montage", "", "Zusatzmontage", "", "Kuechenmontage", "");
  $csv_text_return = array("Lieferung", "", "Montage", "", "Zusatzmontage", "", "Kuechenmontage", "");
  $csv_text_start = "Anlage zur Auftrags-Nr. 101$jbpc_id;vom " . formDate($jb_exporttime) . "\n";
  $csv_text_diff[1] = $csv_text_diff[3] = $csv_text_diff[5] = $csv_text_diff[7] =
    $csv_text_start . "Name;Kaufvertrag;Leistung;Auftragsdatum;Lieferdatum;Endverbraucherpreis;Gutschrift IKEA;Preis IKEA\n";
  $csv_text_return[1] = $csv_text_return[3] = $csv_text_return[5] = $csv_text_return[7] =
    $csv_text_start . "Name;Kaufvertrag;Leistung;Auftragsdatum;Lieferdatum;Preis IKEA;Rückvergütung\n";
  if ($cs_id == 1 || $cs_id == 2):
    $csv_text_diff[1] = $csv_text_diff[3] = $csv_text_diff[5] = $csv_text_diff[7] =
      $csv_text_start . "Datum;Kaufvertrag;Service;OrderID;BelegNr;KartenNr;Warenwert;PreisIKEA;Vergütung\n";
    $csv_text_return[1] = $csv_text_return[3] = $csv_text_return[5] = $csv_text_return[7] =
      $csv_text_start . "Datum;Kaufvertrag;Service;OrderID;BelegNr;KartenNr;Warenwert;PreisIKEA;Vergütung\n";
  endif;

  $sql_query = 
    "SELECT tr.tr_comp, tr.tr_commission_no, jb.jb_id, jb.jb_createtime, jb.jb_ordertime, jb.jb_service, jbprc.mt_sort, jbprc.jbprc_price, jbprc.jbprc_remark" .
    " FROM job AS jb, tour AS tr, jobprice AS jbprc" .
    " WHERE jb.jb_id = jbprc.jb_id AND jbprc.jbprc_reference IN ($jbpc_id, $jbpc_id2)" .
    " AND jb.jb_id = tr.jb_id AND tr.tr_sort = 2 AND jbprc.mt_sort IN (0, 1, 2, 3) AND jbprc.jbprc_remark != 'Einkaufsservice' ORDER BY jbprc.jb_id, jbprc_remark, mt_sort";
//echo $sql_query . "\n";
  $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  $mt_sort_vals = array();
  $mt_sort_sums = array(1 => array(0,0,0,0,0), 3 => array(0,0,0,0,0), 5 => array(0,0,0,0,0), 7 => array(0,0,0,0,0));
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    $jb_service = mcConvertNum2Sel($row["jb_service"]);
    $jbprc_price = bruttoSum($row["jbprc_price"]);
    $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"):
      $csv_index = 1;
      // "Montage" und "Installation" niemals in Lieferliste in Altona
      if ($cs_id == 17093 && !(strpos($row["jbprc_remark"], "Montage") === false) || !(strpos($row["jbprc_remark"], "Installation") === false)):
        $csv_index = 3;
        if (in_array($serviceKueMont, $jb_service)):
          $csv_index = 7;
        endif;      
      endif;
    elseif ($is_addmont && (in_array($serviceNeuMont, $jb_service) || in_array($serviceKueMont, $jb_service))):
      $csv_index = 5;
    elseif (in_array($serviceNeuMont, $jb_service)):
      $csv_index = 3;
    elseif (in_array($serviceKueMont, $jb_service)):
      if (!(strpos($row["jbprc_remark"], "lfd. Meter Montage") === false))
        $csv_index = 7;
      else
        $csv_index = 3;
    endif;
    if ($db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_obj_id = $jb_id AND gdc_gen_fieldname = 'jb_service_job'") == "1"):
      $service_job = "Service";
      $order_id = check_str($row["tr_commission_no"]);
      $beleg_nr = "";
    else:
      $service_job = "";
      $order_id = "";
      $beleg_nr = check_str($row["tr_commission_no"]);
    endif;
    $lieferwarenwert = $db->getOne("SELECT SUM(trat_price) FROM tourarticle WHERE jb_id = " . $row["jb_id"]);
    $karten_nr = $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_business_card'");

    if ($row["mt_sort"] == 2 || $row["mt_sort"] == 3):
      if ($row["mt_sort"] == 2):
//print_r ($mt_sort_vals) . "\n";
        if (($mt_sort_vals[1] > 0 || $jbprc_price > 0) && $mt_sort_vals[1] != $jbprc_price):
          if ($cs_id != 1 && $cs_id != 2):
            $csv_text_diff[$csv_index] .= str_replace(";", "", $row["tr_comp"]) . ";" . $row["jb_id"] . ";" . $row["jbprc_remark"] . "" . ";" . formDate($row["jb_createtime"]) . ";" .
              formDate($row["jb_ordertime"]) . ";" . $mt_sort_vals[0] . ";" . $mt_sort_vals[1] . ";" . $jbprc_price . "\n";
          else:
            $jbprc_price = $mt_sort_vals[1] - $jbprc_price;
            $csv_text_diff[$csv_index] .= formDate($row["jb_createtime"]) . ";" . $row["jb_id"] . ";$service_job;$order_id;" .
              $beleg_nr . ";$karten_nr;" . $lieferwarenwert . ";" . $mt_sort_vals[1] . ";" . $jbprc_price . "\n";
          endif;
          $mt_sort_sums[$csv_index][0] += $mt_sort_vals[0];
          $mt_sort_sums[$csv_index][1] += $mt_sort_vals[1];
          $mt_sort_sums[$csv_index][2] += $jbprc_price;
        endif;
        $mt_sort_vals[2] = bruttoSum($row["jbprc_price"]);
      elseif ($row["mt_sort"] == 3 && $jbprc_price > 0):
        if ($cs_id != 1 && $cs_id != 2):
          $csv_text_return[$csv_index] .= str_replace(";", "", $row["tr_comp"]) . ";" . $row["jb_id"] . ";" . $row["jbprc_remark"] . "" . ";" . formDate($row["jb_createtime"]) . ";" .
            formDate($row["jb_ordertime"]) . ";" . $mt_sort_vals[2] . ";" . $jbprc_price . "\n";
        else:
//echo formDate($row["jb_createtime"]); echo ";" . $row["jb_id"]; echo ";$service_job;$order_id;"; echo $beleg_nr; echo ";$karten_nr;"; echo $lieferwarenwert; echo ";" . $mt_sort_vals[2]; echo ";" . $jbprc_price . "\n";
          $csv_text_return[$csv_index] .= formDate($row["jb_createtime"]) . ";" . $row["jb_id"] . ";$service_job;$order_id;" .
            $beleg_nr . ";$karten_nr;" . $lieferwarenwert . ";" . $mt_sort_vals[2] . ";" . $jbprc_price . "\n";
        endif;
        $mt_sort_sums[$csv_index][3] += $jbprc_price;
        $mt_sort_sums[$csv_index][4] += $mt_sort_vals[2];
      endif;
    else:
      $mt_sort_vals[$row["mt_sort"]] = $jbprc_price;
    endif;
  endwhile;
  mysql_free_result($res);
  foreach (array(1, 3, 5, 7) as $csv_index) {
    if ($$mt_sort_sums[$csv_index][1] == $mt_sort_sums[$csv_index][2]):
      $csv_text_diff[$csv_index] = "";
    else:
      if ($cs_id != 1 && $cs_id != 2)
        $csv_text_diff[$csv_index] .= ";;;;;" . $mt_sort_sums[$csv_index][0] . ";" . $mt_sort_sums[$csv_index][1] . ";" . $mt_sort_sums[$csv_index][2] . "\n";
      else
        $csv_text_diff[$csv_index] .= ";;;;;;;" . $mt_sort_sums[$csv_index][1] . ";" . $mt_sort_sums[$csv_index][2] . "\n";
    endif;
    if ($mt_sort_sums[$csv_index][3] == 0):
      $csv_text_return[$csv_index] = "";
    else:
      if ($cs_id != 1 && $cs_id != 2)
        $csv_text_return[$csv_index] .= ";;;;;" . $mt_sort_sums[$csv_index][4] . ";" . $mt_sort_sums[$csv_index][3] . "\n";
      else
        $csv_text_return[$csv_index] .= ";;;;;;;" . $mt_sort_sums[$csv_index][4] . ";" . $mt_sort_sums[$csv_index][3] . "\n";
    endif;
  }

  myWriteLog("do_diff($jbpc_id $what $jbpc_id2)");
  if ($what == "return"):
//print_r($csv_text_return);
    return $csv_text_return;
  endif;
//print_r($csv_text_diff);
  return $csv_text_diff;
}

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

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

function bruttoSum($sum)
{
	return round($sum * 1.19, 2);
}

function check_str($aStr)
{
	return str_replace(";", "", $aStr);
}

function myWriteLog($log_text) {
  global $log_file_name;

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