<?php

define("LOG_NAME", "finalize_job");
include_once("../tools/edi.inc.php");
mysql_select_db("phoenix", $mysql_db) or die (mysql_error($mysql_db));

$hq_prefixes = array(
  11 => "ST",
//  11 => "STS",
   4 => "STH",
   8 => "STL",
   7 => "STE",
  99 => "STL"
);

$jb_idA = array();
$tr_sortA = array();
//$fake_id = 90000001;

//********************************************************************************
// Einzelaufträge nach Kurier-Zuteilung (scan.php) generieren
function finalize_jobs()
{
	global $mysql_db, $hq_id, $hq_prefixes;
	
  $sql_query = "SELECT COUNT(*) FROM tourarticle AS ta, tourarticleprocess AS tp" .
    " WHERE tratp_type = 412 AND trat_state != 2 AND ta.trat_id = tp.trat_id AND tratp_state = 1 AND trat_serialno LIKE '" . $hq_prefixes[$hq_id] . "0%'";
  writeLog($sql_query);
  $row = get_first_row($sql_query);
  writeLog("new tracking ids with type '412': " . $row[0]);
  
  $sql_query = "SELECT COUNT(*) FROM tourarticle AS ta, tourarticleprocess AS tp" .
    " WHERE tratp_type = 411 AND trat_state != 2 AND ta.trat_id = tp.trat_id AND tratp_state = 1 AND trat_serialno LIKE '" . $hq_prefixes[$hq_id] . "0%'";
  writeLog($sql_query);
  $row = get_first_row($sql_query);
  writeLog("new tracking ids with type '411': " . $row[0]);

//  $sql_query = "SELECT COUNT(*) FROM job, tour WHERE jb_status = 998 AND job.jb_id = tour.jb_id AND tr_sort > 1 AND tr_ware_from_to = 0";
//  $res = mysql_query($sql_query, $mysql_db) or die ($sql_query . ": " . mysql_error($mysql_db));
//  $row = mysql_fetch_row($res);
//  $dirty_addresses = $row[0];
//  mysql_free_result($res);
//  if ($dirty_addresses > 0):
//    exec_query("DELETE tourarticleprocess FROM job, tourarticle, tourarticleprocess WHERE jb_status = 998 AND hq_id = " . $hq_id . " AND job.jb_id = tourarticle.jb_id AND tratp_type = 411 AND tourarticle.trat_id = tourarticleprocess.trat_id;");
//    exec_query("UPDATE job SET jb_status = 999 WHERE jb_status = 998 AND hq_id = " . $hq_id);
//  else:
    $sql_query = "SELECT jb_id FROM job WHERE jb_status = 998 AND hq_id = " . $hq_id;
    $res = mysql_query($sql_query, $mysql_db) or die ($sql_query . ": " . mysql_error($mysql_db));
    while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
      finalize_job($row["jb_id"]);  
    endwhile;
    mysql_free_result($res);  
//  endif;
  check_orphans();
  get_prices();
}

function finalize_job($jb_id)
{
  global $mysql_db, $jb_idA, $tr_sortA, $csc_id_payerA, $hq_id, $zipcodes;

  $row1 = get_first_row("SELECT csc_id_payer FROM job WHERE jb_id = $jb_id");
  $csc_id_payer = $row1[0];
  $jb_ordertime = date("Y-m-d H:i:s");
  $i = 1;
  do {
    $jb_ordertime_next = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") + $i++, date("Y")));
    $ph_name = "";
    $sql_query = "SELECT ph_name FROM phoenix.publicholiday WHERE hq_id = $hq_id AND ph_year = '" . substr($jb_ordertime_next, 0, 4) . "' AND ph_month = '" . substr($jb_ordertime_next, 5, 2) . "' AND ph_day =  '" . substr($jb_ordertime_next, 8, 2) . "'";
    $res1 = mysql_query($sql_query, $mysql_db) or die ($sql_query . ": " . mysql_error($mysql_db));
    if ($row1 = mysql_fetch_row($res1)):
      $ph_name = $row1[0];
    endif;
    mysql_free_result($res1);
 	} while ($ph_name != "" && $ph_name != "Samstag");

  $tr_sort = 2;
  writeLog("finalizing job: jb_id = '$jb_id'");
  $sql_query = "SELECT tr.jb_id, tr.tr_sort, tr_comp, tr_person, tr_comp2, tr_hsno, tr_cs_freetext, trat_id, trat_serialno, ad_zipcode, ad_city, ad_street" .
    "  FROM tour AS tr, tourarticle AS ta, address AS ad" .
    "  WHERE tr.jb_id = $jb_id AND tr.jb_id = ta.jb_id AND tr.tr_sort = ta.tr_sort AND tr.tr_sort > 1 AND tr.ad_id = ad.ad_id" .
    "  ORDER BY jb_id, tr_sort";
//echo $sql_query . "\n";
  $res = mysql_query($sql_query, $mysql_db) or die ($sql_query . ": " . mysql_error($mysql_db));
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    $cr_sid = trim($row["tr_cs_freetext"]); // $cr_sid = "S1999";
    if ($cr_sid != "" && substr($cr_sid, -4) != "1555"):
      $jb_id_new = get_job($csc_id_payer, $hq_id, $jb_ordertime, $cr_sid, false);
      $tr_sort_new = $tr_sortA[$cr_sid]++;
    else:   
      if (substr($cr_sid, -4) == "1555"):
        $row1 = get_first_row("SELECT trat_serialno, tourarticle.trat_id FROM tourarticle, tourarticleprocess WHERE jb_id = $jb_id AND tr_sort = 1 AND trat_serialno = '" . $row["trat_serialno"] . "' AND tourarticle.trat_id = tourarticleprocess.trat_id AND tratp_type = '201'");
//writeLog(var_export($row1, true));
        if ($row1[0] == $row["trat_serialno"] && substr($row['tr_comp'], 0, 1) != "!"):
          exec_query ("INSERT INTO tourarticleprocess (trat_id, tratp_type, tratp_state, tratp_createtime, tratp_remark)" .
            " VALUES (" . $row1[1] . ", '328', 1, '" . $jb_ordertime . "', '')");
          $row['tr_comp'] = "!" . $row['tr_comp'];
          $row['tr_cs_freetext'] = $zipcodes[$row['ad_zipcode']];
          exec_query("UPDATE tour SET tr_comp = '" . $row['tr_comp'] . "', tr_cs_freetext = '" . $row['tr_cs_freetext'] . "' WHERE jb_id = $jb_id AND tr_sort = " . $row["tr_sort"]);
        endif;
      endif;
      $jb_id_new = $jb_id;
      $tr_sort_new = $tr_sort++;
    endif;
//writeLog("rewriting station: " . $row["trat_serialno"] . ", jb_id/tr_sort old: $jb_id, " . $row["tr_sort"] . " -> new: " . $jb_id_new . ", " . $tr_sort_new);
    exec_query("UPDATE tour SET jb_id = " . $jb_id_new . ", tr_sort = " . $tr_sort_new . " WHERE jb_id = $jb_id AND tr_sort = " . $row["tr_sort"]);
    exec_query("UPDATE tourarticle SET jb_id = " . $jb_id_new . ", tr_sort = " . $tr_sort_new . " WHERE jb_id = $jb_id AND tr_sort = " . $row["tr_sort"]);
    exec_query("UPDATE tourarticle SET jb_id = " . $jb_id_new . " WHERE jb_id = $jb_id AND tr_sort = 1 AND trat_serialno = '" . $row["trat_serialno"] . "'");
  endwhile;
  mysql_free_result($res);

  // Nextdays nach 14:00 Uhr auf den nächsten Arbeitstag legen
  foreach($jb_idA AS $cr_sid => $jb_id_newA) {
    foreach($jb_id_newA AS $jb_id_new) {
      $row1 = get_first_row("SELECT csc_id_payer FROM job WHERE jb_id = $jb_id_new");
      if ($csc_id_payerA[$row1[0]] != 'STBX_SAME'):
    	  write_tour($jb_id_new, $tr_sortA[$cr_sid]++, "Endstation", '2');
        if (time() > mktime(14, 0, 0, date("m"), date("d"), date("Y")))
          exec_query("UPDATE job SET jb_ordertime = '$jb_ordertime_next 08:00:00' WHERE jb_id = $jb_id_new");        
    	endif;
      sort_planTour($jb_id_new);
    }
  }
  
  foreach($jb_idA AS $cr_sid => $jb_id_newA) {
    foreach($jb_id_newA AS $jb_id_new) {    	
      $row = get_first_row("SELECT cr_sid FROM job WHERE jb_id = $jb_id_new");
      if ($row[0] == "S1999" || $row[0] == "H1999" || $row[0] == "L1999" || $row[0] == "E1888"):
        writeLog("cancelling job because of cr_sid ='" . $row[0] . "': jb_id = '$jb_id_new', jb_status = 2, jb_storno = 2");
        exec_query("UPDATE job SET jb_status = 2, jb_storno = 2, jb_finishtime = '" . date("Y-m-d H:i:s") . "' WHERE jb_id = $jb_id_new");
      else:
        writeLog("sending job to VPA: jb_id = '$jb_id_new', jb_status = 1");
        exec_query("UPDATE job SET jb_status = 1 WHERE jb_id = $jb_id_new");
      endif;
      check_missing_scans($jb_id_new);
    	send_csv_mail($jb_id_new);
    }
  }

  if ($tr_sort == 2):
//    writeLog("finishing job: jb_id = '$jb_id'");
//    exec_query("UPDATE job SET hq_id = 9999, jb_storno = 2, jb_status = 2 WHERE jb_id = $jb_id");
  else:
    writeLog("unassigned stations left: jb_id = '$jb_id'");
    // Ereignis 411 löschen, falls schon geschrieben    
    writeLog("deleting event 411: jb_id = '$jb_id'");
    exec_query("DELETE tourarticleprocess FROM tourarticle, tourarticleprocess WHERE jb_id = $jb_id AND tratp_type = 411 AND tourarticle.trat_id = tourarticleprocess.trat_id;");
    exec_query("UPDATE job SET jb_status = 999 WHERE jb_id = $jb_id");
  endif;
  $jb_idA = array();
  $tr_sortA = array();
}

function sort_planTour($jb_id_sort)
{
  global $mysql_db, $end_start_zipcodes, $tr_sort_maxA;

  $row = get_first_row("SELECT cr_sid FROM job WHERE jb_id = $jb_id_sort");
  $end_coordinates = $end_start_zipcodes[$row[0]][0];
  $start_coordinates = $end_start_zipcodes[$row[0]][1];
  
  if ($row[0] == "S1999" || $row[0] == "H1999" || $row[0] == "L1999" || $row[0] == "E1888"):
    writeLog("tour not planned: jb_id = '$jb_id_sort' because of cr_sid = '" . $row[0] . "'");
    return;
  endif;

  writeLog("planning tour: jb_id = '$jb_id_sort', cr_sid = $row[0] ...");
  XSplanTourJob($jb_id_sort, $end_coordinates, $start_coordinates);
  writeLog("...finished: jb_id = '$jb_id_sort', cr_sid = $row[0]"); 

  $trat_idA = array();
  $sql_query = "SELECT tr_sort, trat_serialno FROM tourarticle WHERE jb_id = $jb_id_sort AND tr_sort > 1";
  $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    $trat_idA[$row["tr_sort"]] = $row["trat_serialno"];
  endwhile;
  mysql_free_result($res);
//print_r($trat_idA); die();

  $row = get_first_row("SELECT csc_id_payer, hq_id, jb_ordertime, cr_sid, MAX(tr_sort) FROM job, tour WHERE job.jb_id = $jb_id_sort AND job.jb_id = tour.jb_id GROUP BY job.jb_id");
  $csc_id_payer = $row[0];
  $hq_id = $row[1];
  $jb_ordertime = $row[2];
  $cr_sid = $row[3];
  $max_tr_sort = $row[4];
  $tr_sort_old = $tr_sort_maxA[$hq_id] + 1;
  $tr_sort_new = $tr_sort_maxA[$hq_id] + 1;
  while ($tr_sort_old <= $max_tr_sort):
    if ($tr_sort_new > $tr_sort_maxA[$hq_id]):
      $jb_id_new = get_job($csc_id_payer, $hq_id, $jb_ordertime, $cr_sid, true);
      $tr_sort_new = 2;
    endif;
//writeLog("rewriting station: " . $trat_idA[$tr_sort_old] . ", jb_id/tr_sort old: $jb_id_sort/" . $tr_sort_old . " -> new: " . $jb_id_new . ", " . $tr_sort_new);
    exec_query("UPDATE tour SET jb_id = " . $jb_id_new . ", tr_sort = " . $tr_sort_new . " WHERE jb_id = $jb_id_sort AND tr_sort = " . $tr_sort_old);
    exec_query("UPDATE tourarticle SET jb_id = " . $jb_id_new . ", tr_sort = " . $tr_sort_new++ . " WHERE jb_id = $jb_id_sort AND tr_sort = " . $tr_sort_old);
    exec_query("UPDATE tourarticle SET jb_id = " . $jb_id_new . " WHERE jb_id = $jb_id_sort AND tr_sort = 1 AND trat_serialno = '" . $trat_idA[$tr_sort_old++] . "'");
  endwhile;
}

function get_job($csc_id_payer, $hq_id, $jb_ordertime, $cr_sid, $force_new = false)
{
	global $mysql_db, $jb_idA, $tr_sortA, $csc_id_payerA;//, $fake_id;

  if (!array_key_exists($cr_sid, $jb_idA) || $force_new):
    $row1 = get_first_row("SELECT cr_id FROM couriervehicle WHERE crvh_sid = '$cr_sid'");
    $cr_id = $row1[0];
    // end customer Auftrag schreiben
    exec_query("INSERT INTO job (hq_id, csc_id_payer, jb_payment, jb_ordertime, jb_reserv, cr_id, cr_sid, " .
      "cr_id_order, jb_taketime, jb_status, jb_autoranking, jb_type, jb_incomplete, jb_globaljob, jb_tourname, emp_id, " .
      "jb_fixprice, jb_totalprice, jb_postage, jb_invmode, jb_freetext_1, jb_locktime, jb_export_time) VALUES (" .
      ($hq_id != 99 ? $hq_id : 8) . ", $csc_id_payer, '0', '$jb_ordertime', '1', $cr_id, '$cr_sid', $cr_id, '', '997', '0', '1', '0', '0', '', '0', '0', '0'," .
      "'0', '0', '', '9999-12-31 23:59:59', '9999-12-31 23:59:59')");
    $jb_id_new = mysql_insert_id($mysql_db);
//    $jb_id_new = $fake_id++;
    $jb_idA[$cr_sid][] = $jb_id_new;
    $tr_sortA[$cr_sid] = 2;
    writeLog("creating job: jb_id = '" . $jb_id_new . "', cr_sid=" . $cr_sid);
    // Station für Abholung in Niederlassung
    write_tour($jb_id_new, 1, $csc_id_payerA[$csc_id_payer] , '1');
    exec_query("INSERT INTO tourservice (jb_id, csc_id, tr_sort, srv_id, trs_srv_name, srvt_id, trs_srvt_name, trs_price, trs_discount) VALUES (" .
    $jb_id_new . ", " . $csc_id_payer . ", '0', '0', 'Fixpreis', '0', '', '0', '0')");
  else:
   $jb_id_new = $jb_idA[$cr_sid][count($jb_idA[$cr_sid]) - 1];
  endif;
  return $jb_id_new;
}

function check_orphans()
{
	global $mysql_db, $mailaddr;
	
  $sql_query = "SELECT trat_serialno, hq_id FROM tourarticle AS ta, tourarticleprocess AS tp, job AS jb" .
    " WHERE tratp_type = 412 AND trat_state != 2 AND ta.trat_id = tp.trat_id AND tratp_state = 1 AND trat_serialno LIKE 'ST%' AND ta.jb_id = jb.jb_id";
  $res = mysql_query($sql_query, $mysql_db) or die ($sql_query . ": " . mysql_error($mysql_db));
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    exec_query("INSERT INTO phoenix_log.edi_status (ec_track_id, es_msg_no, es_ic_ref, es_date, es_status) VALUES ('" . $row["trat_serialno"] . "','0','0','" . date("Y-m-d H:i:s") . "', '0')");
    exec_query("UPDATE tourarticle SET trat_state = 2 WHERE trat_serialno = '" . $row["trat_serialno"] . "'");
    $mailObj = new htmlMimeMail();
    $mailObj->setFrom($mailaddr[$row["hq_id"]]["from"]);
//    $mailObj->setCc("leier@stadtbote.de");
    $mailObj->setBcc($mailaddr[$row["hq_id"]]["bcc"]);
    $mailObj->setSubject($row["trat_serialno"] . " - Paket ohne Auftrag");
    $mailObj->setText(      
      "Ein Paket mit der Tracking-Nummer '" . $row["trat_serialno"] . "' wurde gescannt, obwohl kein elektronischer Auftrag vorliegt.\n\n".
      "Dieses Paket kann nicht ausgeliefert werden, sondern wird mit dem nächsten Linienauftrag zurück geschickt.\n".
      "Für diese Tracking-Nummer wird demnächst die Meldung 'Received shipment - did not receive paperwork' automatisch an Amazon gesendet.\n");
    $mailResult = $mailObj->send($mailaddr[$row["hq_id"]]["sendto"], 'smtp');
    writeLog("result of sending mail for track_id = '" . $row["trat_serialno"] . "': '" . $mailResult . "'");
  endwhile;
  mysql_free_result($res);
}

function check_missing_scans($jb_id)
{
	global $mysql_db, $mailaddr;
	
  $sql_query = "SELECT trat_serialno, hq_id FROM tourarticle AS ta, tourarticleprocess AS tp, job AS jb" .
    " WHERE tratp_type = 411 AND ta.trat_id = tp.trat_id AND trat_serialno LIKE 'ST%' AND ta.jb_id = jb.jb_id AND (jb.jb_storno != 2 OR jb.jb_storno IS NULL) AND jb.jb_id = $jb_id";
  $res = mysql_query($sql_query, $mysql_db) or die ($sql_query . ": " . mysql_error($mysql_db));
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
    $mailObj = new htmlMimeMail();
    $mailObj->setFrom($mailaddr[$row["hq_id"]]["from"]);
    $mailObj->setBcc($mailaddr[$row["hq_id"]]["bcc"]);
    $mailObj->setSubject($row["trat_serialno"] . " - Auftrag ohne Paket");
    $mailObj->setText(      
      "Ein Paket mit der Tracking-Nummer '" . $row["trat_serialno"] . "' wurde in Auftrag " . $jb_id . " eingestellt, obwohl der Eingansscan fehlt.\n\n".
      "Sollte der Scan irrtümlich fehlen, muss dieser manuell im System nachgetragen werden.\n".
      "Ansonsten wird für diese Tracking-Nummer demnächst die Meldung 'Paperwork received - did not receive shipment' automatisch an Amazon gesendet.\n");
    $mailResult = $mailObj->send($mailaddr[$row["hq_id"]]["sendto"], 'smtp');
    writeLog("result of sending mail for track_id = '" . $row["trat_serialno"] . "': '" . $mailResult . "'");
  endwhile;
  mysql_free_result($res);
}

function get_prices() {
  global $price_ranges, $hq_id, $hq_prefixes;

  $pre_len = strlen($hq_prefixes[$hq_id]) + 2;
  if (date("d") <= 15)
    $last_inv_date = date("Y-m-d", mktime(0, 0, 0, date("m"), 1, date("Y")));
  else
    $last_inv_date = date("Y-m-d", mktime(0, 0, 0, date("m"), 16, date("Y")));
$last_inv_date = "2013-04-16";
  $i = 0;
  while ($i == 0 || $cur_date != $last_inv_date):
    $cur_date = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - $i++, date("Y")));
//echo $cur_date . "\n";
    // Preise ermitteln und eintragen
    foreach(array("('" . $hq_prefixes[$hq_id] . "01')", "('" . $hq_prefixes[$hq_id] . "02','" . $hq_prefixes[$hq_id] . "03')") AS $type_clause) {
      $sql_query = //"SELECT count(*) AS cnt" .
        //" FROM phoenix.tourarticle AS ta, phoenix.tourarticleprocess AS tp " .
        //" WHERE ta.trat_id = tp.trat_id AND tp.tratp_type = '201' AND LEFT(tratp_createtime, 10) = '" . $cur_date . "' AND LEFT(trat_serialno, $pre_len) IN " . $type_clause;
        "SELECT count(*) AS cnt" .
        " FROM phoenix.tourarticle AS ta1, phoenix.tourarticleprocess AS tp1, phoenix.tourarticle AS ta2, phoenix.tourarticleprocess AS tp2" .
        " WHERE ta1.trat_id = tp1.trat_id AND (tp1.tratp_type = '102' OR tp1.tratp_type = '101') AND ta2.trat_id = tp2.trat_id AND tp2.tratp_type = '201'" .
        " AND ta1.trat_serialno = ta2.trat_serialno AND LEFT(tp1.tratp_createtime, 10) = '" . $cur_date . "' AND LEFT(ta1.trat_serialno, $pre_len) IN "  . $type_clause;
     $res = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
      while ($row = mysql_fetch_array($res, MYSQL_ASSOC)):
//echo $row["cnt"] . "\n";
        $es_amount = get_price($row["cnt"]);
        $sql_query = //"SELECT trat_serialno" .
          //" FROM phoenix.tourarticle AS ta, phoenix.tourarticleprocess AS tp " .
          //" WHERE ta.trat_id = tp.trat_id AND tp.tratp_type = '201' AND LEFT(tratp_createtime, 10) = '" . $cur_date . "' AND LEFT(trat_serialno, $pre_len) IN " . $type_clause;
          "SELECT ta1.trat_serialno" .
          " FROM phoenix.tourarticle AS ta1, phoenix.tourarticleprocess AS tp1, phoenix.tourarticle AS ta2, phoenix.tourarticleprocess AS tp2" .
          " WHERE ta1.trat_id = tp1.trat_id AND (tp1.tratp_type = '102' OR tp1.tratp_type = '101') AND ta2.trat_id = tp2.trat_id AND tp2.tratp_type = '201'" .
          " AND ta1.trat_serialno = ta2.trat_serialno AND LEFT(tp1.tratp_createtime, 10) = '" . $cur_date . "' AND LEFT(ta1.trat_serialno, $pre_len) IN "  . $type_clause;
        $res1 = mysql_query($sql_query) or die ($sql_query . ": " . mysql_error());
        while ($row1 = mysql_fetch_array($res1, MYSQL_ASSOC)):      
          exec_query("UPDATE phoenix_log.edi_status SET es_amount = $es_amount WHERE es_status IN (0,1) AND ec_track_id = '" . $row1["trat_serialno"] . "'");
        endwhile;
      endwhile;
      mysql_free_result($res);
    }
  endwhile;
}

function write_tour($jb_id, $tr_sort, $tr_comp, $tr_ware_from_to)
{
	global $hq_id, $ad_id_hqA, $csc_id_externA;

  $tr_hsnoA = array (
    11 => "10",
     4 => "15",
     8 => "69-71",
     7 => "28",
    99 => "69-71"
  );

  $cmp_nameA = array(
    11 => "DKT / Amazon ",
     4 => "Stadtbote GmbH / Amazon ",
     8 => "Stadtbote GmbH / Amazon ",
     7 => "Stadtbote GmbH / Amazon ",
    99 => "Stadtbote GmbH / Amazon "
  );

  exec_query("INSERT INTO tour (jb_id, tr_sort, ad_id, tr_hsno, csc_id, tr_status, tr_comp, tr_comp2, tr_remark, tr_ware_from_to, tr_mediationarea_id) VALUES (" .
    "$jb_id, $tr_sort, " . $ad_id_hqA[$hq_id] . ", '" . $tr_hsnoA[$hq_id] . "', " . $csc_id_externA[$hq_id] . ", 0, '" . $cmp_nameA[$hq_id] . $tr_comp . "', '', '', $tr_ware_from_to, 0)");
  writeLog("creating tourstop: jb_id = '$jb_id', tr_sort = '$tr_sort'");
}

?>