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


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


// Check HTTP-Parameters
getSecHttpVars("1",array("f_act", "customerId", "cscIdRoot", "cscIdActual",
                  "f_street", "f_hsno", "f_zipcode", "f_city", "f_country",
                  "orderClauseDefault", "statusMessage", "tourno", "deactivateMenu"));

getLanguage(__FILE__);

$deactivateMenuStatic = "1";
$pageTitel = getLngt("ADRESSENLISTE");
include_once ("../admin/menu.php");
include_once ("../include/html.inc.php");
getCurrentScript(__FILE__);

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

$numOfRows = 0;
mcTrim($f_street); mcTrim($f_zipcode); mcTrim($f_city);
$searchValues = $f_street . $f_zipcode . $f_city;          // if called e.g. by the first time with empty search-fields
mcIsSet($f_district);

// if any search-field has content then take default-city of the headquarter
$hqAddress = getAddress($hq_id, "hq");
if ($searchValues == "") :   
   $f_city = mcArrTrim($hqAddress, "city");
   // $searchValues = $f_street . $f_zipcode . $f_city;    // define again if no address is associated to headquarter
endif;

// Initialize output
mcIsSet($tourno);
$out = "";

// Generate search-resultset
if ($f_act == "search" && $searchValues != "") :
        
   if (strlen($f_street) > 4 || (strlen($f_street) > 1 && (strlen($f_zipcode) > 1 || strlen($f_city) > 1))) :

      // *********************************************************************
      // * Selection of the costcenters of the current customer for the list *
      // *********************************************************************
      
      // This part only for headquarter employees
      if ($userTypeName == "hq") :

         $whereClause = "";
         if ($f_street != "") : $whereClause .= "ad_street LIKE '" . $f_street . "%'"; endif;      
         if ($whereClause != "" && $f_zipcode != "") : $whereClause .= " AND "; endif;
         if ($f_zipcode != "") : $whereClause .= "ad_zipcode LIKE '" . $f_zipcode . "%'"; endif;                  
         if ($whereClause != "" && $f_city != "") : $whereClause .= " AND "; endif;      
         if ($f_city != "") : $whereClause .= "ad_city LIKE '" . $f_city . "%'"; endif;      
         if ($whereClause != "" && $f_country != "") : $whereClause .= " AND "; endif;      
         if ($f_country != "") : $whereClause .= "ad_country LIKE '" . $f_country . "%'"; endif;
         
         // if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;
    
         if ($orderClauseDefault == "") : 
            $orderClause = "ad_street, ad_zipcode";
         else :
            $orderClause = "ad_" . $orderClauseDefault;
         endif;
    
    
         // *****************************************************************************************************
         // * TABLE "serviceplzareaaddress" Selection of the costcenter based on a search by companyname-prefix *
         // *****************************************************************************************************
         $sqlquery = "SELECT CONCAT('BEREICH: ',srvpa.srvpa_name,' (',srvpa.srvpa_longname,')') AS ad_district,"
                      . "    ad.ad_street, ad.ad_zipcode, ad.ad_city, ad.ad_country," 
                      . "    CONCAT(srvpaa.srvpaa_hsno_from,'-',srvpaa.srvpaa_hsno_to) AS srvpaa_hsno," 
                      . "    srvpaa_mode" 
                      . " FROM serviceplzareaaddress AS srvpaa, address AS ad, serviceplzarea AS srvpa"
                      . " WHERE ad.ad_id = srvpaa.ad_id            AND"
                      . "       srvpaa.srvpa_id = srvpa.srvpa_id"
                      .   ($whereClause != "" ? " AND " : "") . $whereClause
                      . " ORDER BY " . $orderClause;
    
         $result = $db->query($sqlquery);
         if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
    
         $lineToggler = 0;
         while ($row = $result->fetch_assoc()):            
               $numOfRows++;
    
   	           if ($lineToggler == 0) : $lineToggler = 1; else : $lineToggler = 0; endif;                
               $cellColor = getListColor($numOfRows, $lineToggler);
    
               $v_ad_district  = $row["ad_district"];
               $v_ad_street    = $row["ad_street"];
               $v_ad_zipcode   = $row["ad_zipcode"];
               $v_ad_city      = $row["ad_city"];
               $v_ad_country   = $row["ad_country"];
               
               $v_srvpaa_hsno  = $row["srvpaa_hsno"];            
               $v_srvpaa_mode  = "";
               if ($row["srvpaa_mode"] == "1") : $v_srvpaa_mode = ", " . getLngt("nur ungerade"); endif;
               if ($row["srvpaa_mode"] == "2") : $v_srvpaa_mode = ", " . getLngt("nur gerade"); endif;
    
               $out .= "<tr class=\"f10bp1\">";
               $out .= "<td bgcolor=\"" . $cellColor ."\">&nbsp;" . "<a href=\"javascript:finishPage('".my_str_check_js($v_ad_district)."','".my_str_check_js($v_ad_street)."','".my_str_check_js($v_ad_zipcode)."','".my_str_check_js($v_ad_city)."','".my_str_check_js($v_ad_country)."','".$tourno."',0);\">"
                                        . $v_ad_street . " (" . $v_srvpaa_hsno . $v_srvpaa_mode . ")</a>" . "&nbsp;</td>";            
               $out .= "<td bgcolor=\"" . $cellColor ."\"> $v_ad_zipcode &nbsp;</td> ";
               $out .= "<td bgcolor=\"" . $cellColor ."\"> $v_ad_city &nbsp;</td> ";            
               $out .= "<td bgcolor=\"" . $cellColor ."\"> $v_ad_country &nbsp;</td> ";
               $out .= "<td class=\"f8np1_blue\"  bgcolor=\"" . $cellColor ."\"> $v_ad_district &nbsp;</td> ";
    
               $out .= "</tr>\n";
         endwhile;
         $result->free();
    
         // Empty line if $out is not empty
         if ($out != "") :
            $out .= "<tr><td colspan=\"5\">&nbsp;</td></tr>";
         endif;
      endif;


      // **************************************************************************************
      // * TABLE "street" Selection of the costcenter based on a search by companyname-prefix *
      // **************************************************************************************

      $whereClause = "";
      if ($f_street != "") : $whereClause .= "st_street LIKE '" . $f_street . "%'"; endif;      
      if ($whereClause != "" && $f_zipcode != "") : $whereClause .= " AND "; endif;
      if ($f_zipcode != "") : $whereClause .= "st_zipcode LIKE '" . $f_zipcode . "%'"; endif;                  
      if ($whereClause != "" && $f_city != "") : $whereClause .= " AND "; endif;      
      if ($f_city != "") : $whereClause .= "st_city LIKE '" . $f_city . "%'"; endif;      
      if ($whereClause != "" && $f_country != "") : $whereClause .= " AND "; endif;      
      if ($f_country != "") : $whereClause .= "st_country LIKE '" . $f_country . "%'"; endif;
      
      // if ($whereClause != "") : $whereClause = " AND " . $whereClause; endif;

      if ($orderClauseDefault == "") : 
         $orderClause = "st_street, st_no_from, st_no_to, st_zipcode";
      else :
         $orderClause = "st_" . $orderClauseDefault;
      endif;      
      if ($orderClause == "st_street")
        $orderClause .= ", st_no_from, st_no_to";
      
      $sqlquery = "SELECT st_district, st_street, st_zipcode, st_city, st_country, st_no_from, st_no_to" 
                   . " FROM phoenix_special.street"
                   . " WHERE "    . $whereClause
                   . " ORDER BY " . $orderClause;

      // Straßen in den Rg.-PLZ-Bereichen nur aus der der Post-Tabelle holen
      $use_street_post_1 = ($hq_id == 3 && strpos($_SERVER["HTTP_REFERER"], "job_tour.php") !== false);
      if ($use_street_post_1) {
        $sqlquery = "
          SELECT DISTINCT srvp1.srvp_plz AS srvp1_plz, srvp2.srvp_plz AS srvp2_plz, srvsam.srvsam_name, srvsam.srva2_id
          FROM servicesubareamapping AS srvsam, serviceplz AS srvp1, serviceplz AS srvp2
          WHERE srvsam.srva_id = srvp1.srvp_id AND srvsam.srva2_id = srvp2.srvp_id AND LEFT(srvp1.srvp_plz, 1) = '1'
          ORDER BY srvp1_plz, srvsam_sort";
        $rg_plzs = array();
        $res = $db->query($sqlquery);
        if (DB::isError($res)) die ("$PHP_SELF: '$sqlquery'" . $res->getMessage());
        $srvsam_srvp_plz_sql = "";
        while ($row = $res->fetch_assoc()) {
          $srvsam_srvp_plz_sql .= $row["srvp1_plz"] . "','";
          $rg_plzs[$row["srvp1_plz"]][] = array($row["srvp2_plz"], $row["srvsam_name"], $row["srva2_id"]);
        }
        $res->free();
        $srvsam_srvp_plz_sql = substr($srvsam_srvp_plz_sql, 0, -3);
        
        $sqlquery = "(SELECT st_district, st_street, st_zipcode, st_city, st_country, st_no_from, st_no_to" 
                     . " FROM phoenix_special.street"
                     . " WHERE "    . $whereClause . " AND st_zipcode NOT IN ('" . $srvsam_srvp_plz_sql . "')"
                     . ") UNION "
                  . "(SELECT st_district, st_street, st_zipcode, st_city, '' AS st_country, '' AS st_no_from, '' AS st_no_to"
                     . " FROM phoenix_special.street_post_1"
                     . " WHERE "    . $whereClause . " AND st_zipcode IN ('" . $srvsam_srvp_plz_sql . "')"
                  . ") ORDER BY " . $orderClause;
      }
//echo $sqlquery;
      $result = $db->query($sqlquery);
      if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
      $numOfRows = 0;
      $lineToggler = 0;
      while ($row = $result->fetch_assoc()):            
            // Straßen in den Rg.-PLZ-Bereichen ggfs. die Rg.-PLZ zuordnen
            $mediation_area_id = 0;
            if ($use_street_post_1 && in_array($row["st_zipcode"], array_keys($rg_plzs))) {
  
              for ($i = count($rg_plzs[$row["st_zipcode"]]) - 1; $i >= 0; $i--) {
//echo $i.", ".$row["st_street"].", ".$row["st_district"].", ".$rg_plzs[$row["st_zipcode"]][$i][1] . "\n";
                if (strpos($row["st_district"], $rg_plzs[$row["st_zipcode"]][$i][1]) !== false) {
//echo "found\n";
                  $mediation_area_id = $rg_plzs[$row["st_zipcode"]][$i][2];
                  if (trim($row["st_city"]) != trim($row["st_district"]))
                    $row["st_city"] .= " - " . $row["st_district"];
                  $row["st_district"] .= " (Rg.-PLZ " . $rg_plzs[$row["st_zipcode"]][$i][0] . ")";
                  break;
                }
              }
              if ($mediation_area_id == 0) {
                for ($i = count($rg_plzs[$row["st_zipcode"]]) - 1; $i > 0; $i--) {
                  if (strpos($row["st_city"], $rg_plzs[$row["st_zipcode"]][$i][1]) !== false) {
                    $mediation_area_id = $rg_plzs[$row["st_zipcode"]][$i][2];
                    if (trim($row["st_city"]) != trim($row["st_district"]) && trim($row["st_district"]) != "")
                      $row["st_city"] .= " - " . $row["st_district"];
                    $row["st_district"] .= " (Rg.-PLZ " . $rg_plzs[$row["st_zipcode"]][$i][0] . ")";
                    break;
                  }
                }
              }
            }
            $numOfRows++;

            if ($lineToggler == 0) : $lineToggler = 1; else : $lineToggler = 0; endif;                
            $cellColor = getListColor($numOfRows, $lineToggler);

            $v_ad_district  = $row["st_district"];
            $v_ad_street    = $row["st_street"];
            $v_ad_no        = $row["st_no_from"] . "-" . $row["st_no_to"];
            $v_ad_zipcode   = $row["st_zipcode"];
            $v_ad_city      = $row["st_city"];
            $v_ad_country   = $row["st_country"];

            $out .= "<tr class=\"f10bp1\">";
            // $out .= "<td class=\"f10bp1_red\" bgcolor=\"" . $cellColor ."\"> $v_csc_name </td> ";
            $out .= "<td bgcolor=\"" . $cellColor ."\">&nbsp;" . "<a href=\"javascript:finishPage('".my_str_check_js($v_ad_district)."','".my_str_check_js($v_ad_street)."','".my_str_check_js($v_ad_zipcode)."','".my_str_check_js($v_ad_city)."','".my_str_check_js($v_ad_country)."','".$tourno."',".$mediation_area_id.");\">"
                               . $v_ad_street . "</a>" . " " . $v_ad_no . "&nbsp;</td>";            
            $out .= "<td bgcolor=\"" . $cellColor ."\"> $v_ad_zipcode &nbsp;</td> ";
            $out .= "<td bgcolor=\"" . $cellColor ."\"> $v_ad_city &nbsp;</td> ";            
            $out .= "<td bgcolor=\"" . $cellColor ."\"> $v_ad_country &nbsp;</td> ";
            $out .= "<td bgcolor=\"" . $cellColor ."\"> $v_ad_district &nbsp;</td> ";

            $out .= "</tr>\n";
      endwhile;
      $result->free();
   else :
      $statusMessage = getLngt("Eingabe entw. mind. 5 Zeichen bei Straße oder mind. 2 Zeichen bei Straße in Verbindung mit mind. 2 Zeichen bei Ort oder PLZ!");
   endif;
endif;   
?>

<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"); ?>
</style>

<?php include_once ("../include/js_framework.inc.php"); ?>

<script type="text/javascript">
<!--
// NAVIGATION
<?php echo $jsMenuOut; ?>

var tourNo = "<?php echo $tourno ?>";

var v_district = '';
var v_street = '';
var v_zipcode = '';
var v_city = '';
var v_country = '';


function finishPage(district,street,zipcode,city,country,tourno,mediation_area_id) {

//alert(district + ' ' + street + ' ' + zipcode + ' ' + city + ' ' + country + ' ' + tourno + ' ' + mediation_area_id);
  switch (tourno)
  {
    case "price_query1":
      opener.document.forms[0].zipcode1.value = zipcode;
      opener.document.forms[0].zipcode1.focus();      
      opener.updatePrice();
      break;
    case "price_query2":
      opener.document.forms[0].zipcode2.value = zipcode;
      opener.document.forms[0].zipcode2.focus();      
      opener.updatePrice();
      break;
    case "price_query3":
      opener.document.forms[0].zipcode3.value = zipcode;
      opener.document.forms[0].zipcode3.focus();      
      opener.updatePrice();
      break;
    case "price_query4":
      opener.document.forms[0].zipcode4.value = zipcode;
      opener.document.forms[0].zipcode4.focus();      
      opener.updatePrice();
      break;
    case "price_query5":
      opener.document.forms[0].zipcode5.value = zipcode;
      opener.document.forms[0].zipcode5.focus();      
      opener.updatePrice();
      break;
    case "price_query6":
      opener.document.forms[0].zipcode6.value = zipcode;
      opener.document.forms[0].zipcode6.focus();      
      opener.updatePrice();
      break;
    default:
      if (tourno == "") {
        // opener.document.forms[0].f_ad_district.value = district;
        opener.document.forms[0].f_ad_street.value = street;
        opener.document.forms[0].f_ad_zipcode.value = zipcode;
        opener.document.forms[0].f_ad_city.value = city;
        // opener.document.forms[0].f_ad_country.value = country;
      } else {
        opener.setJobList(tourno, opener.jl_ad_street, street);
        opener.setJobList(tourno, opener.jl_ad_zipcode, zipcode);
        opener.setJobList(tourno, opener.jl_ad_city, city);
        opener.setJobList(tourno, opener.jl_tr_mediationarea_id, mediation_area_id);
        opener.refreshFormFields();
        //opener.setCurTourNo(tourNo);
        //opener.parent.frames[1].document.forms[1].elements[0].focus();
        opener.checkSendTour();
      }
      break;
  }

  self.close();
};

function clearFields() {
  document.forms[0].f_street.value = '';
  document.forms[0].f_zipcode.value = '';
  document.forms[0].f_city.value = '<?php echo mcArrTrim($hqAddress, "city"); ?>';
  document.forms[0].f_country.value = '';
  document.forms[0].f_district.value = '';
};
-->
</script>
</head>

<body onLoad="<?php echo $phpCurrentNavigationOnLoad ?>displayStatusMessage();">

<?php echo $phpMenuOut ?>
<?php echo $phpReducedMenuOut ?>
<?php echo $phpPageTitelOut ?>

<div class="maincontent" name="maincontent" id="maincontent">

<form action="ad_list.php" method="post">
  <input type="hidden" name="f_act" value="">  
  <input type="hidden" name="customerId" value="<?php echo $customerId ?>">
  <input type="hidden" name="cscIdRoot" value="<?php echo $cscIdRoot ?>">
  <input type="hidden" name="cscIdActual" value="<?php echo $cscIdActual ?>">  
  <input type="hidden" name="orderClauseDefault" value="<?php echo $orderClauseDefault ?>">
  <input type="hidden" name="tourno" value="<?php echo $tourno ?>">
  <?php echo $phpCurrentNavigationInputHidden ?>
  <input type="hidden" name="deactivateMenu" value="<?php echo ec($deactivateMenu) ?>">

  <?php echo htmlDivLineSpacer("20px"); ?>

  <div>
    <div class="f12bp1_blue"><?php echo getLngt("Adressen") ?></div>
  </div>
  <?php echo htmlDivLineSpacer("20px"); ?>
  
  <div>
    <a href="javascript:document.forms[0].f_act.value='search';document.forms[0].submit();"><?php echo getLngt("Suchen") ?></a>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <a href="javascript:clearFields();"><?php echo getLngt("Felder zurücksetzen") ?></a>
  </div>
  <?php echo htmlDivLineSpacer("15px"); ?>
  
  <div>
<!--
<?php echo "'" . $_SERVER["HTTP_REFERER"] . "', " . mcIsSet($use_street_post_1); ?>
-->
    <table class="f8np1" border="0" cellpadding="0">
      <tr>
        <td><input type="text" name="f_street" value="<?php echo $f_street ?>" size="25"></td>
        <td><input type="text" name="f_zipcode" value="<?php echo $f_zipcode ?>" size="5"></td>
        <td><input type="text" name="f_city" value="<?php echo $f_city ?>" size="20"></td>                  
        <td><input type="text" name="f_country" value="<?php echo $f_country ?>" size="3"></td>
        <td><input type="text" name="f_district" value="<?php echo $f_district ?>" size="20"></td>
      </tr>             
      <tr>
        <td><a href="javascript:document.forms[0].orderClauseDefault.value='street';document.forms[0].f_act.value='search';document.forms[0].submit();"><?php echo getLngt("Straße") ?></a></td>
        <td><a href="javascript:document.forms[0].orderClauseDefault.value='zipcode';document.forms[0].f_act.value='search';document.forms[0].submit();"><?php echo getLngt("PLZ") ?></a></td>
        <td><a href="javascript:document.forms[0].orderClauseDefault.value='city';document.forms[0].f_act.value='search';document.forms[0].submit();"><?php echo getLngt("Ort") ?></a></td>                  
        <td><a href="javascript:document.forms[0].orderClauseDefault.value='country';document.forms[0].f_act.value='search';document.forms[0].submit();"><?php echo getLngt("Land") ?></a></td>
        <td><a href="javascript:document.forms[0].orderClauseDefault.value='district';document.forms[0].f_act.value='search';document.forms[0].submit();"><?php echo getLngt("Ortsteil") ?></a></td>
      </tr>                                
      <?php echo $out ?>
    </table>
  </div>
  <?php echo htmlDivLineSpacer("15px"); ?>

  <div>
    <?php echo getLngt("Anzahl Einträge:") ?> <?php echo $numOfRows ?><?php if ($numOfRows == "0" && $f_act == "search" && $statusMessage == "") : echo " " . getLngt("(Keine Einträge gefunden.)"); endif; ?>
  </div>

</form>
</div>
</body>

</html>