<?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");
include_once("../include/calendar.inc.php");

$temp_csv_folder = "../temp/edifact/";

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

//$from_date_month_timestamp = mktime(0, 0, 0, 1, 12, 2015);
//$to___date_month_timestamp = mktime(0, 0, 0, 1, 31, 2015);
//$from_date_month_timestamp = mktime(0, 0, 0, 2, 1, 2015);
//$to___date_month_timestamp = mktime(0, 0, 0, 2, 28, 2015);
$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_sql = date("Y-m-d 00:00:00", $from_date_month_timestamp);
$to___date_month_sql = date("Y-m-d 23:59:59", $to___date_month_timestamp);

foreach (array(1 => "Schnelsen" , 2 => "Moorfleet", 17051 => "Altona") as $csc_id_related => $marketname) {
  $sql_query = 
    "SELECT jb.jb_id, LEFT(jb_createtime, 10) AS jb_createdate, LEFT(jb_ordertime, 10) AS jb_orderdate, SUM(trat_weight) as sum_trat_weight, SUM(trat_price) as sum_trat_price, COUNT(gdc_content) as cnt_packet, jb_service" .
    " FROM job AS jb, jobpayment AS jbp LEFT JOIN tourarticle AS trat ON jb.jb_id = trat.jb_id LEFT JOIN genericdatacontainer AS gdc ON trat_id = gdc_obj_id AND gdc_gen_fieldname = 'packet'" .
    " WHERE jb_status = 2 AND jb_offer = 0 AND (jb_storno IS NULL OR jb_storno = 0) AND jb_service IN (1, 6, 18)" .
    " AND csc_id_related = " . $csc_id_related . " AND jb_ordertime > '$from_date_month_sql' AND jb_ordertime < '$to___date_month_sql'" .
    " AND jb.jb_id = jbp.jb_id AND jbp_id != 0" .
    " GROUP BY jb.jb_id ORDER BY jb_orderdate";
  
//echo $sql_query . "\n"; //die();
  $csv_contents = 
    "Auftragsnr.;Eingangsdatum;Lieferdatum;Gewicht;Warenwert;taggleich;Tag1;Andere;Paketfahrscheine\n";
  $csv_contents1 = "";
  $csv_contents6 = "";
  $csv_contents18 = "";
  $csv_contents_tourarticle = "";
  $sum_taggleich = 0;
  $sum_tag1 = 0;
  $sum_andere = 0;
  $sum_cnt_packet = 0;
  $cnt6 = 0;
  $cnt18 = 0;
  $tourarticles = array();
  $res = $db->query($sql_query);
  if (DB::isError($res)) reportDie ("$PHP_SELF: '$sqlq_uery': " . $res->getMessage());
  while ($row = $res->fetch_assoc()):
    if (
         $db->getOne("SELECT gdc_gen_fieldname FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'EKS'") != "EKS"
      && $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_content = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_id_addmont'") != $row["jb_id"]):
  //echo $row["jb_id"] . " ";
      if ($row["jb_service"] == 1):
        $diff_days = diff_workdays($row["jb_createdate"], $row["jb_orderdate"]);
        if ($row["sum_trat_weight"] == "")
          $row["sum_trat_weight"] = "0";
        if ($row["sum_trat_price"] == "")
          $row["sum_trat_price"] = "0";
        $csv_contents1 .= 
          $row["jb_id"] . ";" . $row["jb_createdate"] . ";" . $row["jb_orderdate"] . ";" . $row["sum_trat_weight"] . ";" . $row["sum_trat_price"] . ";" . ($diff_days == 0 ? "1" : "") . ";" . ($diff_days == 1 ? "1" : "") . ";" . ($diff_days > 1 ? "1" : "") . ";" . ($row["cnt_packet"] != 0 ? $row["cnt_packet"] : "") . "\n";
        $sum_taggleich += ($diff_days == 0 ? 1 : 0);
        $sum_tag1 += ($diff_days == 1 ? 1 : 0);
        $sum_andere += ($diff_days > 1 ? 1 : 0);
        $sum_cnt_packet += $row["cnt_packet"];
        
        // beliebteste Tourartikel      
        $sql_query = "SELECT trat_quantity, trat_serialno, trat_remark FROM tourarticle WHERE jb_id = " . $row["jb_id"];
        $res1 = $db->query($sql_query);
        if (DB::isError($res1)) reportDie ("$PHP_SELF: '$sql_query': " . $res1->getMessage());
        while ($row1 = $res1->fetch_assoc()):
          if (trim($row1["trat_serialno"]) != "" && $row1["trat_serialno"] != "0" && trim($row1["trat_remark"]) != "" && strtoupper(trim($row1["trat_remark"])) == trim($row1["trat_remark"]) && substr(strtoupper(trim($row1["trat_remark"])), 0, 4) != "EKS " && substr(strtoupper(trim($row1["trat_remark"])), 0, 3) != "TA ") {
            $trat_serialno = " " . $row1["trat_serialno"];
            if (!isset($tourarticles[$trat_serialno])) {
              $tourarticles[$trat_serialno] = array($row1["trat_remark"], $row1["trat_quantity"]);
            } else {
              $tourarticles[$trat_serialno][1] += $row1["trat_quantity"];
         	  }
         	}
        endwhile;
        $res1->free();
  
      elseif ($row["jb_service"] == 6):
        $csv_contents6 .= 
          $row["jb_id"] . ";" . $row["jb_createdate"] . ";" . $row["jb_orderdate"] . ";;;;;;\n";
        $cnt6++;
      elseif ($row["jb_service"] == 18):
        $csv_contents18 .= 
          $row["jb_id"] . ";" . $row["jb_createdate"] . ";" . $row["jb_orderdate"] . ";;;;;;\n";
        $cnt18++;
      endif;
    endif;
  endwhile;
  $res->free();
  
  //print_r($tourarticles);
  $tourarticlesSort = array();
  foreach($tourarticles as $trat_serialno => $tourarticle) {
  	$tourarticlesSort[] = array($trat_serialno, $tourarticle[0], $tourarticle[1]);
  }
  usort ($tourarticlesSort, "cmp_usort");
  //print_r($tourarticlesSort);
  
  while (list($idx, $tourarticle) = each($tourarticlesSort)) {
    $csv_contents_tourarticle .= $idx + 1 . ";" . $tourarticle[0] . ";" . $tourarticle[1] . ";;;" . $tourarticle[2] . ";;;\n";
    if ($idx >= 999)
     break;
  }
  
  $csv_contents .= 
    $csv_contents1 .
    "Gesamt;;;;;" . $sum_taggleich . ";" . $sum_tag1 . ";" . $sum_andere . ";" . $sum_cnt_packet . "\n" .
    ";;;;;;;;\n" .
    "Beliebteste;;Artikel:;;;;;;\n" .
    $csv_contents_tourarticle .
    ";;;;;;;;\n" .
//    $csv_contents6 .
    "Gesamt;Möbelmontagen:;;" . $cnt6 . ";;;;;\n" .
    ";;;;;;;;\n" .
    $csv_contents18 .
    "Gesamt;Küchenmontagen:;;" . $cnt18 . ";;;;;\n";
  
  $csv_file_name = substr($to___date_month_sql, 0, 10) . "_" . $marketname . ".csv";
  $fileHandle = @fopen($temp_csv_folder . $csv_file_name, 'w');
  @fwrite($fileHandle, $csv_contents);
  @fclose($fileHandle);
  
  $xls_file_name = substr($to___date_month_sql, 0, 10) . "_" . $marketname . ".xls";
  $cmd = "../tools/mycsv2xls.pl " . $temp_csv_folder . $csv_file_name . " '' " . $temp_csv_folder . $xls_file_name;
  @unlink($temp_csv_folder . $xls_file_name);
  $ausgabe = exec($cmd);
}

function diff_workdays($date1, $date2) {
  $diff_days = 0;
  
  $date1_timestamp = mktime(0, 0, 0, substr($date1, 5, 2), substr($date1, 8, 2), substr($date1, 0, 4));
  $date2_timestamp = mktime(0, 0, 0, substr($date2, 5, 2), substr($date2, 8, 2), substr($date2, 0, 4));
//echo $date1 . " (" . date("D", $date1_timestamp) . ") -> ". $date2 . " (" .  date("D", $date2_timestamp) . "): ";

  while ($date2_timestamp > $date1_timestamp) {
  	if (!feiertag(date("Y", $date1_timestamp), date("n", $date1_timestamp), date("j", $date1_timestamp)) || $diff_days == 0) {
  	  $diff_days++;
  	}
    $date1_timestamp += 3600 * 24;
  }
//echo $diff_days . "\n";

  return $diff_days;
}

function cmp_usort ($a, $b) {
	if ($a[2] != $b[2])
    return ($a[2] > $b[2] ? -1 : 1);
  return ($a[1] < $b[1] ? -1 : 1);
}

?>