<?php
/*=======================================================================
 *
 * tool_vop.php
 *
 * Autor: Marc Vollmann
 *
 =======================================================================*/

include_once ("../include/mcglobal.inc.php");
include_once ("../include/auth.inc.php");


getSecHttpVars("1", array("f_act", "objId", "objType"));


/*
getLanguage(__FILE__);
$deactivateMenuStatic = "1";
$pageTitel = getLngt("Vorort-Prüfungen");
include_once ("../admin/menu.php");
include_once ("../include/html.inc.php");
include_once ("../include/inc_filters.inc.php");
include_once ("../include/inc_vehicle.inc.php");
include_once ("../include/inc_group.inc.php");
getCurrentScript(__FILE__);
*/

// Check for authentication access and granted rights
// $usrAccessArray["hq"] = "1";
// authCheckForAccess($hq_id, $usr_id, $emp_id, "1", $customerId, $cscIdRoot, $cscIdActual);
// authCheckEmployeeRights($emp_id, "1", "1");

// Check for authentication access and granted rights
$usrAccessArray["hq"] = "1";
$accessOK = authCheckForAccess($hq_id, $usr_id, $emp_id, "", $customerId, $cscIdRoot, $cscIdActual);

// $currentDate = getDateTime("1");
// $currentDateTime = getDateTime("6");

$triggerArray = array();

$whereClauseCrId = "";
if ($objId != "" && is_numeric($objId)) :
   $whereClauseCrId = " cr.cr_id = '" . $objId . "' AND ";
endif;

$constAmStarttimeInDays  = 180;  // 6 months
$constAmStarttimeInDays2 = 120;  // 4 months

$checkTime  = getDateTime("datetime_plus_offset", array(0,0,0,0,-($constAmStarttimeInDays),0), "Y-m-d") . " 00:00:00";
$checkTime2 = getDateTime("datetime_plus_offset", array(0,0,0,0,-($constAmStarttimeInDays2),0), "Y-m-d") . " 00:00:00";

$crvhRpAssocDoesExist = array();

// "Vorort-Prüfung Kundenberichte", Kategorie = mtfc_id = 102
$sqlquery = "SELECT rp.rp_id, rp.rp_createtime, rp.rp_objid, cr.cr_id, cr.cr_eid, crvh.crvh_id, crvh.crvh_sid, crvh.crvh_vh_sign, cs.cs_eid, cs.cmp_id AS cs_cmp_id, cr.cmp_id AS cr_cmp_id, usr.usr_name, usr.usr_firstname"
          . " FROM metafieldcategory AS mtfc, metafieldkey AS mtfk, metafieldcategorykey AS mtfck,"
          . "      metafieldvalue AS mtfv, courier AS cr, couriervehicle AS crvh, company AS cr_cmp, user AS usr, headquarters AS hq, customer AS cs,"
          . "      phoenix_group.report_process AS rp"
          . " WHERE mtfck.mtfc_id = '102' AND"
          . "       mtfck.mtfk_id = '426' AND"
          . "       mtfck.mtfc_id = mtfc.mtfc_id AND"
          . "       mtfck.mtfk_id = mtfk.mtfk_id AND"
          . "       LEFT(mtfk.mtfk_type, 11) = 'select:crvh' AND"
          . "       mtfv.mtfck_id = mtfck.mtfck_id AND"
          . "       mtfv.mtfv_id = rp.rp_id AND"
          . "       mtfv.mtfv_value != '' AND"
          . "       crvh.crvh_sid COLLATE latin1_general_ci = mtfv.mtfv_value COLLATE latin1_general_ci AND"
          . "       crvh.cr_id = cr.cr_id AND"
          .         $whereClauseCrId
          . "       cr.cmp_id = cr_cmp.cmp_id AND"
          . "       cr_cmp.cmp_authenticated = '1' AND"
          . "       cr_cmp.cmp_visible = '1' AND"
          . "       cr.hq_id = '" . $hq_id . "' AND"
          . "       cr.hq_id = hq.hq_id AND"
          . "       cr.usr_id = usr.usr_id AND"
          . "       rp.rp_objid = cs.cs_id"
          . " ORDER BY usr.usr_name, usr.usr_firstname, crvh.crvh_sid, rp.rp_id, mtfk.mtfk_name";
/*
$sqlquery = "SELECT cr.cr_id, cr.cr_eid, crvh.crvh_id, crvh.crvh_sid, crvh.crvh_vh_sign, cr.cmp_id AS cr_cmp_id, usr.usr_name, usr.usr_firstname, rp.rp_id, rp.rp_createtime, rp.rp_objid, cs.cs_eid, cs.cmp_id AS cs_cmp_id"
          . " FROM courier AS cr, user AS usr, headquarters AS hq, couriervehicle AS crvh"
          . "        LEFT JOIN metafieldvalue AS mtfv ON crvh.crvh_sid COLLATE latin1_general_ci = mtfv.mtfv_value COLLATE latin1_general_ci"
          . "        INNER JOIN metafieldcategorykey AS mtfck ON mtfv.mtfck_id = mtfck.mtfck_id AND mtfck.mtfc_id = '102' AND mtfck.mtfk_id = '426'"
          . "        INNER JOIN metafieldkey AS mtfk ON mtfck.mtfk_id = mtfk.mtfk_id AND LEFT(mtfk.mtfk_type, 11) = 'select:crvh'"
          . "        LEFT JOIN phoenix_group.report_process AS rp ON mtfv.mtfv_id = rp.rp_id"
          . "        LEFT JOIN customer AS cs ON rp.rp_objid = cs.cs_id"
          . " WHERE crvh.cr_id = cr.cr_id AND"
          .         $whereClauseCrId
          . "       cr.hq_id = '" . $hq_id . "' AND"
          . "       cr.hq_id = hq.hq_id AND"
          . "       cr.usr_id = usr.usr_id"
          . " ORDER BY rp.rp_id, mtfk.mtfk_name, usr.usr_name, usr.usr_firstname";
*/
$result = $db->query($sqlquery);   
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
while ($row = $result->fetch_assoc()):
      $crvhRpAssocDoesExist[$row["crvh_sid"]] = "1";
      $mtfvTime = $row["rp_createtime"];
      if ($mtfvTime == "") :
         $triggerArray[] = array("66CCFF", $row["rp_id"], $row["rp_createtime"], $row["crvh_sid"], $row["crvh_vh_sign"], $row["cs_eid"], $row["cs_cmp_id"], $row["cr_id"], $row["cr_eid"], $row["usr_name"], $row["usr_firstname"], $row["crvh_id"], $row["cr_cmp_id"]);
      elseif ($mtfvTime <= $checkTime2) :
         $triggerArray[] = array("FF0000", $row["rp_id"], $row["rp_createtime"], $row["crvh_sid"], $row["crvh_vh_sign"], $row["cs_eid"], $row["cs_cmp_id"], $row["cr_id"], $row["cr_eid"], $row["usr_name"], $row["usr_firstname"], $row["crvh_id"], $row["cr_cmp_id"]);
      elseif ($mtfvTime <= $checkTime) :
         $triggerArray[] = array("FFFF33", $row["rp_id"], $row["rp_createtime"], $row["crvh_sid"], $row["crvh_vh_sign"], $row["cs_eid"], $row["cs_cmp_id"], $row["cr_id"], $row["cr_eid"], $row["usr_name"], $row["usr_firstname"], $row["crvh_id"], $row["cr_cmp_id"]);
      else :
         $triggerArray[] = array("00FF00", $row["rp_id"], $row["rp_createtime"], $row["crvh_sid"], $row["crvh_vh_sign"], $row["cs_eid"], $row["cs_cmp_id"], $row["cr_id"], $row["cr_eid"], $row["usr_name"], $row["usr_firstname"], $row["crvh_id"], $row["cr_cmp_id"]);
      endif;
endwhile;
$result->free();


// GET ALL vehicle (of the selected courier)
$sqlquery = "SELECT '' AS rp_id, '' AS rp_createtime, '' AS rp_objid, cr.cr_id, cr.cr_eid, crvh.crvh_id, crvh.crvh_sid, crvh.crvh_vh_sign, '' AS cs_eid, '' AS cs_cmp_id, cr.cmp_id AS cr_cmp_id, usr.usr_name, usr.usr_firstname"
          . " FROM courier AS cr, couriervehicle AS crvh, user AS usr, headquarters AS hq, company AS cr_cmp"
          . " WHERE crvh.cr_id = cr.cr_id AND"
          . "       cr.hq_id = hq.hq_id AND"
          .         $whereClauseCrId
          . "       cr.hq_id = '" . $hq_id . "' AND"
          . "       cr.cmp_id = cr_cmp.cmp_id AND"
          . "       cr_cmp.cmp_authenticated = '1' AND"
          . "       cr_cmp.cmp_visible = '1' AND"
          . "       cr.hq_id = hq.hq_id AND"
          . "       cr.usr_id = usr.usr_id"
          . " ORDER BY usr.usr_name, usr.usr_firstname, crvh.crvh_sid";

$result = $db->query($sqlquery);   
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
while ($row = $result->fetch_assoc()):
      if ($crvhRpAssocDoesExist[$row["crvh_sid"]] == "") :
         $triggerArray[] = array("66CCFF", $row["rp_id"], $row["rp_createtime"], $row["crvh_sid"], $row["crvh_vh_sign"], $row["cs_eid"], $row["cs_cmp_id"], $row["cr_id"], $row["cr_eid"], $row["usr_name"], $row["usr_firstname"], $row["crvh_id"], $row["cr_cmp_id"]);
      endif;
endwhile;
$result->free();


$triggerArrayLen = count($triggerArray);
$triggerOut = "";
if ($triggerArrayLen > 0) :
   $triggerOut .= "<html>\n";
   $triggerOut .= "<body>\n";
   $triggerOut .= "<table>\n";
   $triggerOut .= "<tr>\n";
   $triggerOut .= "<td style=\"text-align:center;\">" . getLngt("Fahrzeug") . "&nbsp;&nbsp;&nbsp;</td>\n";
   $triggerOut .= "<td style=\"text-align:center;\">" . getLngt("Kennzeichen") . "&nbsp;&nbsp;&nbsp;</td>\n";
   $triggerOut .= "<td style=\"text-align:center;\">" . getLngt("EID") . "&nbsp;&nbsp;&nbsp;</td>\n";
   $triggerOut .= "<td style=\"text-align:center;\">" . getLngt("Name") . "&nbsp;&nbsp;&nbsp;</td>\n";
   $triggerOut .= "<td style=\"text-align:center;\">" . getLngt("Vorname") . "&nbsp;&nbsp;&nbsp;</td>\n";
   $triggerOut .= "<td style=\"text-align:center;\">" . getLngt("Bericht") . "&nbsp;&nbsp;&nbsp;</td>\n";
   $triggerOut .= "<td style=\"text-align:center;\">" . getLngt("Kunde") . "&nbsp;&nbsp;&nbsp;</td>\n";
   $triggerOut .= "<td style=\"text-align:center;\">" . getLngt("Datum") . "&nbsp;&nbsp;&nbsp;</td>\n";
   $triggerOut .= "</tr>\n";
   for ($i = 0; $i < $triggerArrayLen; $i++) :
       $tmpArray = $triggerArray[$i];
       $tmpArrayLen = count($tmpArray);
       $rowColor = $tmpArray[0];
       $triggerOut .= "<tr>\n";
       $triggerOut .= "<td style=\"text-align:center; background-color: #" . $rowColor . ";\"><a href=\"../admin/courier_vehicle.php?courierId=" . $tmpArray[7] . "&f_crvh_id=" . $tmpArray[11] . "\" target=\"_blank\">" . $tmpArray[3] . "</a>&nbsp;&nbsp;&nbsp;</td>\n";
       $triggerOut .= "<td style=\"text-align:center; background-color: #" . $rowColor . ";\">" . $tmpArray[4] . "&nbsp;&nbsp;&nbsp;</td>\n";
       $triggerOut .= "<td style=\"text-align:center; background-color: #" . $rowColor . ";\"><a href=\"../admin/courier_special.php?companyId=" . $tmpArray[12] . "\" target=\"_blank\">" . $tmpArray[8] . "</a>&nbsp;&nbsp;&nbsp;</td>\n";
       $triggerOut .= "<td style=\"text-align:center; background-color: #" . $rowColor . ";\">" . $tmpArray[9] . "&nbsp;&nbsp;&nbsp;</td>\n";
       $triggerOut .= "<td style=\"text-align:center; background-color: #" . $rowColor . ";\">" . $tmpArray[10] . "&nbsp;&nbsp;&nbsp;</td>\n";
       $triggerOut .= "<td style=\"text-align:center; background-color: #" . $rowColor . ";\"><a href=\"../groupware/report.php?rpObjType=cs&rpIdsFilter=" . $tmpArray[1] . "\" target=\"_blank\">" . $tmpArray[1] . "</a>&nbsp;&nbsp;&nbsp;</td>\n";
       $triggerOut .= "<td style=\"text-align:center; background-color: #" . $rowColor . ";\"><a href=\"../admin/customer_special.php?companyId=" . $tmpArray[6] . "\" target=\"_blank\">" . $tmpArray[5] . "</a>&nbsp;&nbsp;&nbsp;</td>\n";
       $triggerOut .= "<td style=\"text-align:center; background-color: #" . $rowColor . ";\">" . substr($tmpArray[2],8,2) . "." . substr($tmpArray[2],5,2) . "." . substr($tmpArray[2],0,4) . "</td>\n";

       $triggerOut .= "</tr>\n";
   endfor;
   $triggerOut .= "</table>\n";

   $triggerOut .= "<br><br>";
   $triggerOut .= "<table>\n";
   $triggerOut .= "<tr>\n";
   $triggerOut .= "<td style=\"background-color: #66CCFF;\">&nbsp;&nbsp;KEIN EINTRAG&nbsp;&nbsp;</td>";
   $triggerOut .= "<td style=\"background-color: #00FF00;\">&nbsp;&nbsp;&nbsp;<4 Monate&nbsp;&nbsp;&nbsp;</td>";
   $triggerOut .= "<td style=\"background-color: #FFFF33;\">&nbsp;&nbsp;&nbsp;4-6 Monate&nbsp;&nbsp;&nbsp;</td>";
   $triggerOut .= "<td style=\"background-color: #FF0033;\">&nbsp;&nbsp;&nbsp;>6 Monate&nbsp;&nbsp;&nbsp;&nbsp;</td>";
   $triggerOut .= "</tr>\n";
   $triggerOut .= "</table>\n";

   $triggerOut .= "</body>\n";
   $triggerOut .= "</html>\n";
endif;
?>

<html>

<head>
<link rel="stylesheet" type="text/css" href="../css/phoenix.css">
</head>
	
<body class="menu_bgcol" >

<center>
	
<br><br><br>

<b><?php echo $triggerOut ?></b>

</center>

</body>	
	
</html>