<?php
// accrual accounting = Abgrenzung

error_reporting(E_ALL ^ (E_DEPRECATED | E_NOTICE));
set_time_limit (0);
include_once ("../include/inc_mtf_func.inc.php");
include_once("../include/email/htmlMimeMail.php");

$temp_csv_folder = "../temp/edifact/";
$sender = "support@assecutor.de";
$bcc = "ca@assecutor.de";
//$bcc = "";
$recipients = array(
  "3506" => array("abrechnung-leonberg@walter-moebeltransport.de"),
  "3512" => array("abrechnung-leonberg@walter-moebeltransport.de"),
     "1" => array("petra.lehrke@hansetrans.de", "stefanie.bruemmerstedt@hansetrans.de"),
     "2" => array("petra.lehrke@hansetrans.de", "stefanie.bruemmerstedt@hansetrans.de"),
 "17051" => array("petra.lehrke@hansetrans.de", "stefanie.bruemmerstedt@hansetrans.de")
//  "3506" => array("ca@assecutor.de"),
//  "3512" => array("ca@assecutor.de"),
//     "1" => array("ca@assecutor.de"),
//     "2" => array("ca@assecutor.de"),
// "17051" => array("ca@assecutor.de")
);
$mailtext = "Im Anhang die Abgrenzungsliste ";

$csc_id_relatedArr = array(
 "3506" => "Ludwigsburg",
 "3512" => "Sindelfingen",
    "1" => "Schnelsen",
    "2" => "Moorfleet",
"17051" => "Altona"
);

$csc_ids_nord = array(1, 2, 17051);
$csc_ids_sued = array(3506, 3512);

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

if (date("d") < 15):
  $inv_timestamp = mktime(23, 59, 59, date("m"), 0, date("Y"));
else:
  $inv_timestamp = mktime(23, 59, 59, date("m") + 1, 0, date("Y"));
endif;
$inv_date = date("Y-m-d H:i:s", $inv_timestamp);
$subject = "Abgrenzungsliste und Lagergeld " . date("m/Y", $inv_timestamp) . " ";

// Lagerkosten ermitteln
$stock_costs = array();
$sqlquery = "SELECT csc_id_related, ROUND(SUM(jbc_totalprice), 2) AS sum_jbc_totalprice FROM job, jobcalculator" .
  " WHERE srvt_name = '0003:par_01' AND jobcalculator.jb_id = job.jb_id AND LEFT(jb_createtime, 7) = '" . date("Y-m", $inv_timestamp) . "'" . 
  " GROUP BY csc_id_related";
$res = $db->query($sqlquery);
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sqlquery': " . $res->getMessage());
while ($row = $res->fetch_assoc()):
  $stock_costs[$row["csc_id_related"]] = $row["sum_jbc_totalprice"];
endwhile;
$res->free();

// Abgrenzungsliste erstellen // Entsorgung fehlt noch!
foreach (array(3506,3512,1,2,17051) as $csc_id_related) {
//foreach (array(1,2,17051) as $csc_id_related) {

  $csv_contents = "Rückstellung für IKEA " . $csc_id_relatedArr[$csc_id_related] . " \n\n";
  if (in_array($csc_id_related, $csc_ids_sued)):
    $csv_contents .= 
      "LM;MoeveID;Eingang;Lieferdatum;Fzg;Service;Express;BruttoIKEA;BruttoZHT;NettoFulo;Rueckverg;NettoFulo\n";
    $sqlquery =
      "SELECT DISTINCT jb_id, jb_id_parent, jb_ordertime, jb_createtime, cr_sid, jb_totalprice, jb_cr_price, jb_service FROM job WHERE jb_ordertime > '$inv_date' AND csc_id_related = $csc_id_related AND jb_storno IS NULL AND jb_offer = 0 AND jb_cr_price > 0 AND (jb_export_time IS NULL OR jb_export_time > '$inv_date')" .
      " AND jb_id IN (SELECT jb_id FROM jobpayment WHERE jbp_price > 0 AND jbp_bookingtime < '$inv_date') ORDER BY jb_id";
  endif;
  if (in_array($csc_id_related, $csc_ids_nord)):
    $csv_contents .= 
      "LM;MoeveID;Eingang;Lieferdatum;Fzg;Service;Express;BruttoIKEA;BruttoRV;BruttoFulo;BruttoRV\n";
    $sqlquery = 
      "SELECT DISTINCT job.jb_id, jb_id_parent, jb_ordertime, jb_createtime, cr_sid, jb_totalprice, jb_cr_price, jb_service, mt_sort, sum(jbprc_price) AS sum_jbprc_price FROM job, jobprice" .
      " WHERE job.jb_id = jobprice.jb_id AND mt_sort IN (2, 3, 4, 5) AND jb_ordertime > '$inv_date' AND csc_id_related = $csc_id_related AND jb_storno IS NULL AND jb_offer = 0" .
      " AND jb_cr_price > 0 AND jbprc_remark != 'Einkaufsservice' AND (jb_export_time IS NULL OR jb_export_time > '$inv_date') AND job.jb_id IN (" .
      " SELECT jb_id FROM jobpayment WHERE jbp_price > 0 AND jbp_bookingtime < '$inv_date')" .
      " GROUP BY job.jb_id, mt_sort ORDER BY job.jb_id, mt_sort";
  endif; 
//echo $sqlquery . "\n";
  $mt_sort_vals = array();
  $res = $db->query($sqlquery);
  if (DB::isError($res)) reportDie ("$PHP_SELF: '$sqlquery': " . $res->getMessage());
  while ($row = $res->fetch_assoc()):
    $jb_service = mcConvertNum2Sel($row["jb_service"]);
    $type_str = "L";
    if (in_array($serviceMont, $jb_service)):
      $type_str = "M";
    endif;
    //  if (in_array($serviceLief, $jb_service)):
    //    $type_str = "L";
    //  endif;
    if (in_array($csc_id_related, $csc_ids_sued) || (in_array($csc_id_related, $csc_ids_nord) && $row["mt_sort"] == 5)):
//      $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 ($log_createtime == "" || strtotime($log_createtime) < $inv_timestamp):
      if ($jb_createtime == "" || strtotime($jb_createtime) < $inv_timestamp):
        $sams_str = "";
        if ($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_str = "X";  
        endif;
        if (in_array($csc_id_related, $csc_ids_sued)):
          $csv_contents .= $type_str . ";" . $row['jb_id'] . ";" . formDate($row['jb_createtime']) . ";" . formDate($row['jb_ordertime']) . ";" . $row['cr_sid'] . ";" . $sams_str . ";;" . formSum($row['jb_totalprice'] * 1.19) . ";;" . formSum($row['jb_cr_price']) . ";0;" . formSum($row['jb_cr_price']) . "\n";
        elseif (in_array($csc_id_related, $csc_ids_nord)):
          // Übergangsregelung bis jobprice richtig geschrieben wird
          if ($csc_id_related == 2 && $mt_sort_vals[2] == "0"):
            $mt_sort_vals[2] = $mt_sort_vals[4];
          endif;
          $csv_contents .= $type_str . ";" . $row['jb_id'] . ";" . formDate($row['jb_createtime']) . ";" . formDate($row['jb_ordertime']) . ";" . $row['cr_sid'] . ";" . $sams_str . ";;" . formSum($mt_sort_vals[2] * 1.19) . ";" . formSum($mt_sort_vals[3] * 1.19) . ";" . formSum($mt_sort_vals[4] * 1.19) . ";" . formSum($row['sum_jbprc_price'] * 1.19) . "\n";
          $mt_sort_vals = array();
        endif;
  //    else:
  //      echo $log_createtime . " zu jung!\n";
      endif;
    elseif(in_array($csc_id_related, $csc_ids_nord)):
      $mt_sort_vals[$row["mt_sort"]] = $row["sum_jbprc_price"];
    endif;
  endwhile;
  $res->free();
  
  $csv_file_name = substr($inv_date, 0, 10) . "_" . $csc_id_relatedArr[$csc_id_related] . ".csv";
  $fileHandle = @fopen($temp_csv_folder . $csv_file_name, 'w');
  @fwrite($fileHandle, $csv_contents);
  @fclose($fileHandle);

  $mailObj = new htmlMimeMail();
  $mailObj->setFrom($sender);
  if (trim($bcc) != "")
    $mailObj->setBcc($bcc);
  $mailObj->setSubject($subject . $csc_id_relatedArr[$csc_id_related]);
  $mailObj->setText($mailtext . $csc_id_relatedArr[$csc_id_related] . ".\n\nDas Lagergeld beträgt " . str_replace(".", ",", $stock_costs[$csc_id_related]) . " €.");
  if (in_array($csc_id_related, $csc_ids_nord))
    $mailObj->addAttachment($mailObj->getFile($temp_csv_folder . $csv_file_name), $csv_file_name);   
  $mailResult = $mailObj->send($recipients[$csc_id_related], 'smtp');
}

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(".", ",", sprintf("%01.2f", $sum));
}

?>