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


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


$httpVarsArray = array("f_act", "crvhId", "orderClause", "statusMessage", "f_crvh_id", "f_csvh_day_times", "csIdUpdate",
                       "f_relationStatus", "f_del_customer_id", "g_cs_id", "f_csvh_description_new", "deactivateMenu");

getSecHttpVars("1", $httpVarsArray);

getLanguage(__FILE__);

$deactivateMenuStatic = "1";
$pageTitel = getLngt("ÜBERSICHT KAPAZITÄTEN FAHRZEUG-KUNDE");
include_once ("../admin/menu.php");
include_once ("../include/html.inc.php");
include_once ("../include/inc_vehicle.inc.php");
include_once ("../include/inc_calendar.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");


// Initializations
$doStatistic = true;
$statisticOutputMode = 1;  // 0 = HTML, 1 = CSV
if ($f_relationStatus == "") : $f_relationStatus = "0"; endif;     // 0 = bad relation between cs and cr (blocked), 1 = good relation (favoured)
$f_csvh_description_new = trim($f_csvh_description_new);           // Description related to an association between customer and courier


// Only for output
$htmlClass01 = "class=\"smaller\"";                // input,select
$html_td_01  = "valign=\"top\" align=\"left\"";    // input
$html_td_02  = "valign=\"top\" align=\"right\"";   // input


// Get array of weekdays (default, no mt_objtype and no mt_objid)
$weekDays = getColVectorFromDB2ArrayByClause("metatype", "mt_sort", "mt_type = 'cal_week_days' AND mt_objtype = '' AND mt_objid = '0'", "", "mt_sort", "");
$weekDayNames = getColVectorFromDB2ArrayByClause("metatype", "mt_value", "mt_type = 'cal_week_days' AND mt_objtype = '' AND mt_objid = '0'", "", "mt_sort", "");
$weekDaysLen = count($weekDays);
// Get array of day times (default, no mt_objtype and no mt_objid)
$dayTimes = getColVectorFromDB2ArrayByClause("metatype", "mt_sort", "mt_type = 'day_time' AND mt_objtype = '' AND mt_objid = '0'", "", "mt_sort", "");
$dayTimeNames = getColVectorFromDB2ArrayByClause("metatype", "mt_value", "mt_type = 'day_time' AND mt_objtype = '' AND mt_objid = '0'", "", "mt_sort", "");
$dayTimesLen = count($dayTimes);


// ***********************************************************************************************************************


// Get ALL customers (of ALL headquarters AND ALL mandators) belonging to the "STATIC" groups !!!!
$whereClause = " AND cs.cs_id_parent IN (40512, 40513, 40514) ";
$sqlquery = "SELECT cs.cs_id, cs.cs_eid, cs.cs_id_parent, cmp.cmp_comp, cmp.cmp_comp2, cmp.cmp_comp4"
          . "  FROM customer AS cs, company AS cmp"
          . "  WHERE (cs.cs_group LIKE '%,101,%' OR cs.cs_group LIKE '%,102,%' OR cs.cs_group LIKE '%,103,%' OR cs.cs_group LIKE '%,104,%' OR cs.cs_group LIKE '%,105,%' OR cs.cs_group LIKE '%,106,%' OR cs.cs_group LIKE '%,107,%' OR cs.cs_group LIKE '%,108,%' OR"
          . "         cs.cs_group LIKE '%,109,%' OR cs.cs_group LIKE '%,110,%' OR cs.cs_group LIKE '%,111,%' OR cs.cs_group LIKE '%,112,%' OR cs.cs_group LIKE '%,117,%') AND "
          . "         cmp.cmp_id = cs.cmp_id AND cmp.cmp_comp != 'EXTERN' AND cmp.cmp_visible = '1' AND cs.cs_id_parent != '0' AND NOT isnull(cs.cs_id_parent)" . $whereClause
          . "  ORDER BY cs.cs_eid";
// echo $sqlquery; die();
$result = $db->query($sqlquery);
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
$resultArray = array();
$csIdArray = array();
$csArray = array();
while ($row = $result->fetch_assoc()):
      $csIdArray[] = $row["cs_id"];
      $csArray[$row["cs_id"]] = array($row["cmp_comp"], $row["cmp_comp2"], $row["cmp_comp4"], $row["cs_id_parent"], $row["cs_eid"]);
      $resultArray[$row["cs_id"]] = array(0, 0, 0, 0, 0, 0);
endwhile;
$result->free();
$csIdArrayLen = count($csIdArray);

$customerOutput = "";

// Iterate all customers
for ($i = 0; $i < $csIdArrayLen; $i++) :

    $csId = $csIdArray[$i];

    // At least one entry per weekday
    $tmpCsWeekday = array(0, 0, 0, 0, 0, 0);

    // Get array of day times of the current customer
    $dayTimes = getColVectorFromDB2ArrayByClause("metatype", "mt_sort", "mt_type = 'day_time' AND mt_objtype = 'cs' AND mt_objid = '" . $csId . "'", "", "mt_sort", "");
    $dayTimeNames = getColVectorFromDB2ArrayByClause("metatype", "mt_value", "mt_type = 'day_time' AND mt_objtype = 'cs' AND mt_objid = '" . $csId . "'", "", "mt_sort", "");
    $dayTimeMappedValues = getColVectorFromDB2ArrayByClause("metatype", "mt_mapped_value", "mt_type = 'day_time' AND mt_objtype = 'cs' AND mt_objid = '" . $csId . "'", "", "mt_sort", "");
    $dayTimesLen = count($dayTimes);

    // Get the vehicles for the current customer
    $crvhList = array();
    $sqlquery = getStmtCustomerVehicleRelation("", $csId);
    $result = $db->query($sqlquery);
    if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
    while ($row = $result->fetch_assoc()):
          $crvhList[] = $row["crvh_id"];
    endwhile;
    $result->free();
    $crvhListLen = count($crvhList);


    // Check for each vehicle displayed the existence of entries in "vehicledisposition", "vehicleavailability" and "vehicleavailabilitytimeunits".
    // If it does not exist then generate it to handle the drag and dropping the jobs etc.
    for ($v = 0; $v < $crvhListLen; $v++) :

        $crvhId = $crvhList[$v];

        $count = 0;
        if ($crvhId != "") :
        
           // Get the vehicle-customer relations of a specified courier for selection
           $sqlquery = getStmtCustomerVehicleRelation($crvhId, $csId);  // THIS customer only !!!!
           $result = $db->query($sqlquery);
           if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
        
           while ($row = $result->fetch_assoc()):
                    $f_cmp_comp[$count]         = $row["cmp_comp"];
                    $f_cmp_comp2[$count]        = $row["cmp_comp2"];
                    $f_cs_id[$count]            = $row["cs_id"];
                    $f_cs_eid[$count]           = $row["cs_eid"];
                    $f_cs_hq_id[$count]         = $row["hq_id"];
                    $f_csvh_relation[$count]    = $row["csvh_relation"];
                    $f_csvh_day_times[$count]   = $row["csvh_day_times"];
                    $f_csvh_description[$count] = $row["csvh_description"];
                    $count++;
           endwhile;
           $result->free();      
        endif;
        
        
        // Only for output
        $tmpFields = getFieldsValueFromId("couriervehicle", "crvh_id", $crvhId, array("cr_id","crvh_sid"));
        $tmp_cr_id  = $tmpFields[0];
        $tmp_crvh_sid = $tmpFields[1];
        $tmp_usr_id = getFieldValueFromId("courier", "cr_id", $tmp_cr_id, "usr_id");
        $tmpFields = getFieldsValueFromId("user","usr_id",$tmp_usr_id,array("usr_name","usr_firstname"));
        $tmp_usr_name = $tmpFields[0];
        $tmp_usr_firstname = $tmpFields[1];


        // Output of the existing relations of the current selected area
        // $customerOutput = "";
        for ($j = 0; $j < $count; $j++) :

            // Overwrite array of day times if does exist for this special customer
            /*
            if (existsEntry("metatype",array("mt_type", "day_time", "mt_objtype", "cs", "mt_objid", $f_cs_id[$j]))) :
               $dayTimes = getColVectorFromDB2ArrayByClause("metatype", "mt_sort", "mt_type = 'day_time' AND mt_objtype = 'cs' AND mt_objid = '" . $f_cs_id[$j] . "'", "", "mt_sort", "");
               $dayTimeNames = getColVectorFromDB2ArrayByClause("metatype", "mt_value", "mt_type = 'day_time' AND mt_objtype = 'cs' AND mt_objid = '" . $f_cs_id[$j] . "'", "", "mt_sort", "");
            else :
               $dayTimes = getColVectorFromDB2ArrayByClause("metatype", "mt_sort", "mt_type = 'day_time' AND mt_objtype = '' AND mt_objid = '0'", "", "mt_sort", "");
               $dayTimeNames = getColVectorFromDB2ArrayByClause("metatype", "mt_value", "mt_type = 'day_time' AND mt_objtype = '' AND mt_objid = '0'", "", "mt_sort", "");
            endif;
            $dayTimesLen = count($dayTimes);
            */

            $customerOutput .= $f_cmp_comp[$j] . "<br>";
            if ($f_cmp_comp2[$j] != "") : $customerOutput .= $f_cmp_comp2[$j] . "<br>"; endif;
            $customerOutput .= $f_cs_eid[$j] . "&nbsp;&nbsp;-&nbsp;&nbsp;";
            $customerOutput .= $tmp_crvh_sid . "&nbsp;&nbsp;";

            $customerOutput .= $f_csvh_description[$j];
            $customerOutput .= "<br>\n";
            // Get day time values to be checked for the current customer regarding the current vehicle
            $dayTimesArrayOfTheWeek = getKeyValueArrayFromString($f_csvh_day_times[$j], "|", "=");
            $customerOutput .= "<table class=\"table1\">\n";
            // Table header with names of the weekdays
            $customerOutput .= "<tr>\n";
            $customerOutput .= "<td class=\"td1\">" . "&nbsp;" . "</td>\n";
            for ($l = 0; $l < $weekDaysLen; $l++) :
                $customerOutput .= "<td class=\"td1\"><b>" . $weekDayNames[$l] . "</b></td>\n";
            endfor;            
            // Table body
            $customerOutput .= "</tr>\n";
            for ($k = 0; $k < $dayTimesLen; $k++) :
                $customerOutput .= "<tr>\n";
                $customerOutput .= "<td class=\"td1\"><b>" . $dayTimeNames[$k] . "</b></td>\n";
                for ($l = 0; $l < $weekDaysLen; $l++) :
                    $tmpDayTimes = $dayTimesArrayOfTheWeek[$l + 1];
                    $tmpDayTimesArray = spliti(",", $tmpDayTimes);

                    // Select special zone for each day time of each week day
                    $tmpSelectedValue = "";
                    $tmpDayTimesArrayLen = count($tmpDayTimesArray);
                    for ($m = 0; $m < $tmpDayTimesArrayLen; $m++) :
                        $tmp2 = spliti("-", $tmpDayTimesArray[$m]);
                        if ($tmp2[0] == $dayTimes[$k]) :
                           $tmpSelectedValue = $tmp2[1];
                        endif;
                    endfor;
                    $customerOutput .= "<td class=\"td1\">\n";
                    /*
                    $customerOutput .= "  <select class=\"f7np1\" name=\"f_csvh_day_times_zone_" . $f_cs_id[$j] . "_" . $weekDays[$l] . "_" . $dayTimes[$k] . "\">";
                    $customerOutput .= "  <option value=\"\" " . ($tmpSelectedValue == "" ? "selected" : "") . ">" . getLngt("Alle Zonen") . "</option>";
                    $customerOutput .= "  <option value=\"9999\" " . ($tmpSelectedValue == "9999" ? "selected" : "") . ">" . getLngt("Nicht aktiv") . "</option>";
                    $customerOutput .=    addOptionsFromTable("servicezone", "srvz_no", "srvz_name", "srvz_no", "hq_id = '" . $f_cs_hq_id[$j] . "' AND cs_id = '" . $f_cs_id[$j] . "'", $tmpSelectedValue , "");
                    $customerOutput .= "  </select>";
                    */
                    $tmptext = getLngt("Alle Zonen");
                    if (is_numeric($tmpSelectedValue)) :
                       if ($tmpSelectedValue == 9999) :
                          $tmptext = getLngt("Nicht aktiv");
                       elseif ($tmpSelectedValue > 0) :
                          $tmptext = getLngt("Zone") . " " . $tmpSelectedValue;
                          $tmpCsWeekday[$l] = 1;
                       endif;
                    else :
                      $tmpCsWeekday[$l] = 1;
                    endif;
                    $customerOutput .= $tmptext . "\n";
                    $customerOutput .= "</td>\n";
                endfor;
                $customerOutput .= "</tr>\n";
            endfor;
            $customerOutput .= "</table>\n";
            $customerOutput .= "<br><br>\n";
        endfor;   

        if ($customerOutput == "") :
           $customerOutput = getLngt("Zur Zeit sind keine Verknüpfungen eingetragen!");
        endif;

    endfor;
    
   $tmpCsWeekdayLen = count($tmpCsWeekday);
   for ($z = 0; $z < $tmpCsWeekdayLen; $z++) :
       if ($tmpCsWeekday[$z] == 1) :
          $resultArray[$csId][$z]++;
       endif;
   endfor;
endfor;


// **** Statistic output ****

$statisticOutputCSV = "";
$statisticOutputHTML = "";
if ($doStatistic) :

   $customerOutput = "";  // Avoid standard HTML output of all vehicles as tables (!!!!)

   if ($statisticOutputMode == "1") :
      $statisticOutputCSV .= "KST;EID;Preisgruppe;Markt;Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Sonnabend;\n";
      
      for ($i = 0; $i < $csIdArrayLen; $i++) :
      
          $csId = $csIdArray[$i];
      
          $pg = "1";
          if ($csArray[$csId][3] == "40513") : $pg = "2"; endif;
          if ($csArray[$csId][3] == "40514") : $pg = "3"; endif;

          $statisticOutputCSV .= $csArray[$csId][2] . ";" . $csArray[$csId][4] . ";" . $pg  . ";" . $csArray[$csId][1] . ";"
                               . $resultArray[$csId][0] . ";" . $resultArray[$csId][1] . ";" . $resultArray[$csId][2] . ";" . $resultArray[$csId][3] . ";" . $resultArray[$csId][4] . ";" . $resultArray[$csId][5] . ";\n";

      endfor;
   
   else :
      $statisticOutputHTML .= "<table class=\"table1\">\n";
      $statisticOutputHTML .= "<tr>\n";
      $statisticOutputHTML .= "<td class=\"td1\"><b>" . "KST" . "</b></td>\n";
      $statisticOutputHTML .= "<td class=\"td1\"><b>" . "EID" . "</b></td>\n";
      $statisticOutputHTML .= "<td class=\"td1\"><b>" . "Preisgruppe" . "</b></td>\n";
      $statisticOutputHTML .= "<td class=\"td1\"><b>" . "Markt" . "</b></td>\n";
      $statisticOutputHTML .= "<td class=\"td1\"><b>" . "Montag" . "</b></td>\n";
      $statisticOutputHTML .= "<td class=\"td1\"><b>" . "Dienstag" . "</b></td>\n";
      $statisticOutputHTML .= "<td class=\"td1\"><b>" . "Mittwoch" . "</b></td>\n";
      $statisticOutputHTML .= "<td class=\"td1\"><b>" . "Donnerstag" . "</b></td>\n";
      $statisticOutputHTML .= "<td class=\"td1\"><b>" . "Freitag" . "</b></td>\n";
      $statisticOutputHTML .= "<td class=\"td1\"><b>" . "Sonnabend" . "</b></td>\n";
      $statisticOutputHTML .= "</tr>\n";
      
      // Iterate all customers
      for ($i = 0; $i < $csIdArrayLen; $i++) :
      
          $csId = $csIdArray[$i];
      
          $pg = "1";
          if ($csArray[$csId][3] == "40513") : $pg = "2"; endif;
          if ($csArray[$csId][3] == "40514") : $pg = "3"; endif;
      
          $statisticOutputHTML .= "<tr>\n";
          $statisticOutputHTML .= "<td class=\"td1\"><b>" . $csArray[$csId][2] . "</b></td>\n";  // cmp_comp4
          $statisticOutputHTML .= "<td class=\"td1\"><b>" . $csArray[$csId][4] . "</b></td>\n";  // cs_eid
          $statisticOutputHTML .= "<td class=\"td1\" align=\"center\"><b>" . $pg . "</b></td>\n";
          $statisticOutputHTML .= "<td class=\"td1\"><b>" . $csArray[$csId][1] . "</b></td>\n";  // cmp_comp2
          $statisticOutputHTML .= "<td class=\"td1\" align=\"right\"><b>" . $resultArray[$csId][0] . "</b></td>\n";
          $statisticOutputHTML .= "<td class=\"td1\" align=\"right\"><b>" . $resultArray[$csId][1] . "</b></td>\n";
          $statisticOutputHTML .= "<td class=\"td1\" align=\"right\"><b>" . $resultArray[$csId][2] . "</b></td>\n";
          $statisticOutputHTML .= "<td class=\"td1\" align=\"right\"><b>" . $resultArray[$csId][3] . "</b></td>\n";
          $statisticOutputHTML .= "<td class=\"td1\" align=\"right\"><b>" . $resultArray[$csId][4] . "</b></td>\n";
          $statisticOutputHTML .= "<td class=\"td1\" align=\"right\"><b>" . $resultArray[$csId][5] . "</b></td>\n";
          $statisticOutputHTML .= "</tr>\n";
      endfor;
      $statisticOutputHTML .= "</table>";
   endif;
endif;


if ($statisticOutputMode == "1") :
   echo $statisticOutputCSV;
endif;
?>
<?php if ($statisticOutputMode == "0") : ?>

  <html>
  
  <head>
  <title><?php echo $pageTitel ?></title>
  
  <link rel="stylesheet" type="text/css" href="../css/phoenix.css">
  <style type="text/css">
    <?php include_once ("../css/navigation.css.php"); ?>
    .table1 {border-collapse: collapse; border: 1px solid black;}
    .td1 {border: 1px solid black;
  </style>
  
  <?php include_once ("../include/js_framework.inc.php"); ?>
  
  <script type="text/javascript">
  <!--
  // NAVIGATION
  <?php echo $jsMenuOut; ?>
  
  
  -->
  </script>
  
  </head>
  
  <body class="menu_bgcol" onLoad="<?php echo $phpCurrentNavigationOnLoad ?>">
  
  <?php echo $phpMenuOut ?>
  <?php echo $phpReducedMenuOut ?>
  <?php echo $phpPageTitelOut ?>
  
  <div class="maincontent" name="maincontent" id="maincontent">
  
  <form action="../tools/customer_vehicle_capacities.php" method="post">
  
    <?php echo $phpCurrentNavigationInputHidden ?>
    <input type="hidden" name="deactivateMenu" value="<?php echo ec($deactivateMenu) ?>">
  
    <?php echo htmlDivLineSpacer("10px"); ?>
  
    <div class="f12bp1_blue">
      <?php echo $pageTitel ?>
    </div>
  
    <?php if ($customerOutput != "") : ?>
      <?php echo htmlDivLineSpacer("20px"); ?>
  
      <div class="f10bp1">
        <?php echo $customerOutput; ?>
      </div>
    <?php endif; ?>
  
    <?php if ($statisticOutputHTML != "") : ?>
      <?php echo htmlDivLineSpacer("20px"); ?>
  
      <div class="f10bp1">
        <?php echo $statisticOutputHTML; ?>
      </div>
    <?php endif; ?>
  
  </form>
  </div>
  </body>
  
  </html>

<?php endif; ?>