<?php
/*=======================================================================
 *
 * bwv.inc.php
 *
 * Autor: Marc Vollmann
 *
 =======================================================================*/


// Gets the long haul jobs according to the job state
function getLongHaulJobData($jbStatus = "9", $distance, $specialJob = "") {
  global $db, $PHP_SELF, $f_hq_id, $constLonghaulActive;
  
  // Try to connect request server because of performance
  global $db2;
  getDb2Connection();
  
  $retArray = array();
  
  if ($f_hq_id == "") : $f_hq_id = array(); endif;
  if (count($f_hq_id) == 0) : array_push($f_hq_id, $hq_id); endif;
  if ($distance == "" || !is_numeric($distance)) : $distance = 80; endif;  // minimum distance
  
  $specialJobClause = "";
  if ($specialJob != "" && is_numeric($specialJob)) :
     $specialJobClause .= " AND jb.jb_id = '" . $specialJob . "' ";
  endif;
  if ($jbStatus == "2") :
     $currentTime = getDateTime("0");
     $specialJobClause .= " AND jb.jb_taketime != '' AND jb.jb_taketime != '0000-00-00 00:00:00'";
     $specialJobClause .= " AND jb.jb_finishtime != '' AND jb.jb_finishtime != '0000-00-00 00:00:00'";
     $specialJobClause .= " AND jb.jb_finishtime >= DATE_SUB('" . $currentTime . "', INTERVAL 31 DAY)";
     $specialJobClause .= " AND DATE_ADD(jb.jb_finishtime, INTERVAL SEC_TO_TIME(UNIX_TIMESTAMP(jb.jb_finishtime)-UNIX_TIMESTAMP(jb.jb_taketime)) HOUR_SECOND) >= '" . $currentTime . "'";
  endif;  
  
  // $constLonghaulActive = getParameterValue("0", "LONGHAUL_ACTIVE", "0");
  // [Global definition requiered because of potential access to remote db ...]
  
  $fieldClauseLonghaul = "";
  $fromClauseLonghaul = "";
  $whereClauseLonghaul = "(NOT isnull(jb.jb_longhaul) AND jb.jb_longhaul != '0' AND jb.jb_longhaul != '2')";    // Longhaul mode activated
  if ($constLonghaulActive != "1") :
     // Longhaul mode deactivated, only viewmode according to mediation conditions
     // [1.]
     // $whereClauseLonghaul = "jb.jb_totalprice >= '150'";
     // [2.]
     $fieldClauseLonghaul .= "," . " SQRT(POW(ABS(srvp.srvp_latitude - srvp2.srvp_latitude),2) + POW(ABS(srvp.srvp_longitude - srvp2.srvp_longitude),2)) AS nb ";
     $fromClauseLonghaul = "serviceplz AS srvp, serviceplz AS srvp2, ";
     $whereClauseLonghaul = "ad.ad_zipcode = srvp.srvp_plz AND " .
                            "SUBSTRING_INDEX(SUBSTRING(jb.jb_tourdata, 1, (INSTR(jb.jb_tourdata, '|') - 1) ), ';', -1) = srvp2.srvp_plz AND " . 
                            "SQRT(POW(ABS(srvp.srvp_latitude - srvp2.srvp_latitude),2) + POW(ABS(srvp.srvp_longitude - srvp2.srvp_longitude),2)) >= ((" . $distance . " *  0.56720633650927) / 50.02)";
  endif;
/*  
  // [Live-Mode]
  $sqlStmt = "SELECT jb.jb_id, jb.cr_sid, jb.jb_totalprice, jb.jb_tourdata, jb.jb_longhaul, mt.mt_value, cr.vht_id, jb.vht_id AS vht_id_job, mt2.mt_value AS mt_value_job," 
             . "     crvh.crvh_payload, CONCAT(crvh.crvh_length,' x ',crvh.crvh_width,' x ',crvh.crvh_height) AS crvh_measures,"
             . "     cr.cr_gps_long, cr.cr_gps_lat, cr.cr_gps_time"
             . " FROM job AS jb "
             . "      LEFT JOIN couriervehicle AS crvh ON crvh.crvh_sid = jb.cr_sid"
             . "      LEFT JOIN courier AS cr ON cr.cr_id = jb.cr_id"
             . "      LEFT JOIN metatype AS mt ON mt.mt_type = 'vehicletype' AND mt.mt_sort = cr.vht_id"
             . "      LEFT JOIN metatype AS mt2 ON mt2.mt_type = 'vehicletype' AND mt2.mt_sort = jb.vht_id"
             . " WHERE " . $whereClauseLonghaul . " AND" 
             . "       jb.jb_status = '" . $jbStatus . "' AND" 
             . "       (isnull(jb.jb_storno) OR jb.jb_storno = '0') AND"
             . "       jb.hq_id IN " . getSQLMandatorArray($f_hq_id)
             . $specialJobClause;

  // [1. View-Mode]
  $sqlStmt = "SELECT jb.jb_id, jb.cr_sid, jb.jb_totalprice, jb.jb_tourdata, jb.jb_longhaul, mt.mt_value, cr.vht_id, jb.vht_id AS vht_id_job, mt2.mt_value AS mt_value_job," 
             . "     crvh.crvh_payload, CONCAT(crvh.crvh_length,' x ',crvh.crvh_width,' x ',crvh.crvh_height) AS crvh_measures,"
             . "     cr.cr_gps_long, cr.cr_gps_lat, cr.cr_gps_time"
             . " FROM tour AS tr, tour AS tr2, job AS jb "
             . "      LEFT JOIN couriervehicle AS crvh ON crvh.crvh_sid = jb.cr_sid"
             . "      LEFT JOIN courier AS cr ON cr.cr_id = jb.cr_id"
             . "      LEFT JOIN metatype AS mt ON mt.mt_type = 'vehicletype' AND mt.mt_sort = cr.vht_id"
             . "      LEFT JOIN metatype AS mt2 ON mt2.mt_type = 'vehicletype' AND mt2.mt_sort = jb.vht_id"
             . " WHERE " . $whereClauseLonghaul . " AND" 
             . "       jb.jb_status = '" . $jbStatus . "' AND" 
             . "       (isnull(jb.jb_storno) OR jb.jb_storno = '0') AND"
             . "       jb.hq_id IN " . getSQLMandatorArray($f_hq_id) . "AND"
             . "       (tr.jb_id = jb.jb_id AND tr.tr_sort = '1') AND "
             . "       (tr2.jb_id = jb.jb_id AND tr2.tr_sort = '2')"
             . $specialJobClause;
*/
  // [2. View-Mode]
  // SELECT IF(1>2,2,3);
  // WHERE CONVERT(SomeColumn, SIGNED INTEGER) IS NOT NULL 
  // where COL1 + 0 = COL1
  // where COL1 = concat( '', 0 + COL1 ) 
  // SELECT * FROM myTable WHERE myField REGEXP ('[0-9]') 
  $sqlStmt = "SELECT jb.jb_id, jb.cr_sid, jb.jb_totalprice, jb.jb_tourdata, jb.jb_longhaul, mt.mt_value, cr.vht_id, jb.vht_id AS vht_id_job, mt2.mt_value AS mt_value_job," 
             . "     crvh.crvh_payload, CONCAT(crvh.crvh_length,' x ',crvh.crvh_width,' x ',crvh.crvh_height) AS crvh_measures,"
             . "     cr.cr_gps_long, cr.cr_gps_lat, cr.cr_gps_time" . $fieldClauseLonghaul
             . " FROM " . $fromClauseLonghaul . " address AS ad, tour AS tr, tour AS tr2, job AS jb "
             . "      LEFT JOIN couriervehicle AS crvh ON crvh.crvh_sid = jb.cr_sid"
             . "      LEFT JOIN courier AS cr ON cr.cr_id = jb.cr_id"
             . "      LEFT JOIN metatype AS mt ON mt.mt_type = 'vehicletype' AND mt.mt_sort = cr.vht_id"
             . "      LEFT JOIN metatype AS mt2 ON mt2.mt_type = 'vehicletype' AND mt2.mt_sort = jb.vht_id"
             . " WHERE " . $whereClauseLonghaul . " AND" 
             . "       jb.jb_status = '" . $jbStatus . "' AND" 
             . "       (isnull(jb.jb_storno) OR jb.jb_storno = '0') AND"
             . "       jb.hq_id IN " . getSQLMandatorArray($f_hq_id) . "AND"
             . "       (tr.jb_id = jb.jb_id AND tr.tr_sort = '1') AND "
             . "       (tr2.jb_id = jb.jb_id AND tr2.tr_sort = '2') AND"
             . "       tr.ad_id = ad.ad_id"
             . $specialJobClause;             

// echo $sqlStmt . "<br>";
  $result = $db2->query($sqlStmt);
  if (DB::isError($result)) die ("$PHP_SELF: <br>$sqlStmt<br>" . $result->getMessage());
  while ($row = $result->fetch_assoc()):
        $retArray[] = array($row["jb_id"],$row["cr_sid"],$row["jb_totalprice"],$row["jb_tourdata"],$row["mt_value"],$row["vht_id"],$row["vht_id_job"],$row["mt_value_job"],$row["crvh_payload"],$row["crvh_measures"],$row["jb_longhaul"],$row["cr_gps_long"],$row["cr_gps_lat"],$row["cr_gps_time"]);
  endwhile;
  $result->free();  
     
  return $retArray;
}


// Checks special employee right to access remote database BWV2
function BWV2_checkEmpRemoteDBAccess($useRemoteDB, $empId) {

  $retBool = false;
  if ($useRemoteDB == "13") :

     // Check employee according to special right
     $constEmpRemoteDBRight = getParameterValue("0", "LONGHAUL_REMOTE_DB_EMPLOYEE_IDS", "0");
     if (!(strpos($constEmpRemoteDBRight, "," . $empId . ",") === FALSE)) :
        $retBool = true;
     endif;
  endif;
  return $retBool;
}

// Sets the remote database parameters
function BWV2_setRemoteDBAccessParameter() {
  global $db, $db2;
  global $dbhost,$dblogin,$dbpassword,$dbname;
  global $dbhost2,$dblogin2,$dbpassword2,$dbname2;

  $retBool = false;
  $constRemoteDBAccessData = getParameterValue("0", "LONGHAUL_REMOTE_DB_ACCESSDATA", "0");
  if ($constRemoteDBAccessData != "") :

     $constRemoteDBAccessData = str_replace("|", "-,-", $constRemoteDBAccessData);  
     $remoteDBAccessDataArray = spliti("-,-",$constRemoteDBAccessData);               
  
     $dbhost = $remoteDBAccessDataArray[0];
     $dblogin = $remoteDBAccessDataArray[1];
     $dbpassword = $remoteDBAccessDataArray[2];
     $dbname = $remoteDBAccessDataArray[3];

     // Because of "getDb2Connection()" ...
     $dbhost2 = $remoteDBAccessDataArray[0];
     $dblogin2 = $remoteDBAccessDataArray[1];
     $dbpassword2 = $remoteDBAccessDataArray[2];
     $dbname2 = $remoteDBAccessDataArray[3];
  
     $dsn = "mysql://$dblogin:$dbpassword@$dbhost/$dbname";
     $db = DB::connect($dsn, false);
     if (DB::isError($db))
         die ("$PHP_SELF: " . $db->getMessage());
     $db->setFetchMode(DB_FETCHMODE_ASSOC);
     $db2 = $db;
     
     // Check connection is established
     $countHeadquarters = getCountOfTable("headquarters", "");
     if ($countHeadquarters != "" && $countHeadquarters > 0) :
        $retBool = true;
     endif;
  endif;
  return $retBool;
}
?>