<?php

class AddressdbApicontroller extends mfBaseApicontroller {
    private $campaigns = [];
    private $filter_salescluster_ids = [];
    private $campaigns_by_scluster = [];
    private $allowed_preordertypes = [];
    private $district_is_city = false;
    private $hausnummer_add_zusatz = false;

    protected function init() {
        $db = $this->db(ADDRESSDB_DBHOST, ADDRESSDB_DBUSER, ADDRESSDB_DBPASS, ADDRESSDB_DBNAME);

        $this->addRoute("/addressdb/getClusters", "getClusters", "GET");
        $this->addRoute("/addressdb/findAddress", "find", "GET");
        $this->addRoute("/addressdb/findAddress", "find", "POST");
        $this->addRoute("/addressdb/searchAddress", "search", "GET");
        $this->addRoute("/addressdb/searchAddress", "search", "POST");
        $this->addRoute("/addressdb/findStreet", "findStreet", "GET");
        $this->addRoute("/addressdb/findStreet", "findStreet", "POST");
        $this->addRoute("/addressdb/findZip", "findZip", "GET");
        $this->addRoute("/addressdb/findZip", "findZip", "POST");
        $this->addRoute("/addressdb/findCity", "findCity", "GET");
        $this->addRoute("/addressdb/findCity", "findCity", "POST");
        $this->addRoute("/addressdb/findDistrict", "findDistrict", "GET");
        $this->addRoute("/addressdb/findDistrict", "findDistrict", "POST");
        $this->addRoute("/addressdb/fullexport", "exportAddresses", "GET");
        $this->addRoute("/addressdb/:oaid/pricing", "getAddressPricing", "GET");

        $this->allowMissingOrigin = true;
    }

    protected function authenticated() {

        if($this->me->is("preorderaddressreporting")) {
            $preorder_networks = json_decode($this->me->getFlag("preorder_networks"));

            if(!is_array($preorder_networks) || !count($preorder_networks)) {
                return mfResponse::Forbidden();
            }
            //$this->filter_salescluster_ids = $preorder_networks;

            foreach($preorder_networks as $network_id) {
                $network = new Network($network_id);
                $adb_netzgebiet_id = $network->adb_netzgebiet_id;
                foreach(PreordercampaignSalesclusterModel::search(["salescluster_id" => $adb_netzgebiet_id]) as $campaign_scluster) {
                    if(!in_array($campaign_scluster->salescluster_id, $this->filter_salescluster_ids)) {
                        $this->filter_salescluster_ids[] = $campaign_scluster->salescluster_id;
                    }
                    $campaign = new Preordercampaign($campaign_scluster->preordercampaign_id);
                    if(!$campaign->id) continue;
                    $this->campaigns_by_scluster[$adb_netzgebiet_id] = $campaign_scluster->preordercampaign_id;
                    $this->campaigns[$campaign->id] = $campaign;

                    // get allowed preordertypes
                    if(is_array($campaign->types) && count($campaign->types)) {
                        foreach($campaign->types as $type) {
                            $this->allowed_preordertypes[] = $type->type;
                        }
                    }

                    if($campaign->district_is_city == 1) {
                        $this->district_is_city = true;
                    }

                    if($campaign->hausnummer_add_zusatz == 1) {
                        $this->hausnummer_add_zusatz = true;
                    }

                    foreach(PreordercampaignOriginhostnameModel::search(['preordercampaign_id' => $campaign->id]) as $origin) {
                        $this->addAllowedOrigin($origin->hostname);
                    }

                }
            }

        } else {

            $campaignApiusers = PreordercampaignApiuserModel::search(["worker_id" => $this->me->id]);

            if(!$campaignApiusers) {
                $cau = new PreordercampaignApiuser();
                $cau->preordercampaign_id = 1;
                $campaignApiusers = [$cau];
            }


            foreach($campaignApiusers as $campaignApiuser) {
                $campaign = new Preordercampaign($campaignApiuser->preordercampaign_id);
                if($campaign) {
                    foreach(PreordercampaignSalesclusterModel::search(['preordercampaign_id' => $campaign->id]) as $campaign_scluster) {
                        if(!in_array($campaign_scluster->salescluster_id, $this->filter_salescluster_ids)) {
                            $this->filter_salescluster_ids[] = $campaign_scluster->salescluster_id;
                        }

                        $this->campaigns_by_scluster[$campaign_scluster->salescluster_id] = $campaign->id;

                    }
                    $this->campaigns[$campaign->id] = $campaign;

                    // get allowed preordertypes
                    if(is_array($campaign->types) && count($campaign->types)) {
                        foreach($campaign->types as $type) {
                            $this->allowed_preordertypes[] = $type->type;
                        }
                    }

                    if($campaign->district_is_city == 1) {
                        $this->district_is_city = true;
                    }

                    if($campaign->hausnummer_add_zusatz == 1) {
                        $this->hausnummer_add_zusatz = true;
                    }
                }

                foreach(PreordercampaignOriginhostnameModel::search(['preordercampaign_id' => $campaign->id]) as $origin) {
                    $this->addAllowedOrigin($origin->hostname);
                }
            }

            //var_dump($this->allowed_preordertypes);exit;
            //var_dump($this->filter_salescluster_ids, $this->campaigns_by_scluster);exit;
            //var_dump($this->campaigns, $this->allowed_origins);exit;
        }

        $this->allowed_preordertypes = array_unique($this->allowed_preordertypes);

    }

    protected function getClusters() {
        $cluster_search = [];
        if(count($this->filter_salescluster_ids)) {
            $cluster_search['id'] = $this->filter_salescluster_ids;
        }
        $clusters = [];
        foreach(ADBNetzgebietModel::search($cluster_search) as $cluster) {
            $c = [];
            $c['id'] = $cluster->extref;
            $c['name'] = $cluster->name;
            $clusters[] = $c;
        }

        return mfResponse::Ok(["clusters" => $clusters]);

    }

    protected function findCity() {
        // unofficially supporting GET and POST in v1
        $get = array_merge($this->post, $this->get);

        // search parameter is unofficially deprecated but still supported
        $search = trim($get['search']);
        $city = trim($get['city']);
        $district = trim($get['district']);
        $zip = trim($get['zip']);
        $search_cluster_id = trim($get['cluster_id']);

        if(!$city) {
            $city = $search;
        }

        if(!$city && !$zip && !$search_cluster_id) {
            return mfResponse::BadRequest(['message' => "No search parameters"]);
        }

        $city_search = ['name%' => $city];
        if($zip) {
            $city_search['plz%'] = $zip;
        }
        if($district) {
            $district_search['ortschaft%'] = $zip;
        }

        if(count($this->filter_salescluster_ids)) {
            $city_search['netzgebiet_id'] = $this->filter_salescluster_ids;
        }

        if($search_cluster_id) {
            $city_search['netzgebiet_extref'] = $search_cluster_id;
        }

        if($this->district_is_city) {
            $results = ADBOrtschaftModel::search($city_search);
        } else {
            $results = ADBGemeindeModel::search($city_search);
        }


        $cities = [];
        foreach($results as $result) {
            $cities[] = $result->name;
        }

        $cities = array_unique($cities);
        return mfResponse::Ok(['cities' => array_values($cities)]);
    }

    protected function findDistrict() {
        // unofficially supporting GET and POST in v1
        $get = array_merge($this->post, $this->get);

        // search parameter is unofficially deprecated but still supported
        $search = $this->db()->escape(trim($get['search']));
        $city = $this->db()->escape(trim($get['city']));
        $district = $this->db()->escape(trim($get['district']));
        $zip = $this->db()->escape(trim($get['zip']));
        $search_cluster_id = $this->db()->escape(trim($get['cluster_id']));

        if(!$district) {
            $district = $search;
        }
        /*
        if(!$city && !$zip && !$district) {
          return mfResponse::BadRequest(['message' => "No search parameters"]);
        }*/

        $where = "1=1";

        if($district && $district != "%") {
            $where .= " AND ortschaft like '$district%'";
        }
        if($city && $city != "%") {
            $where .= " AND gemeinde like '$city%'";
        }
        if($zip && $zip != "%") {
            $where .= " AND plz like '$zip%'";
        }

        if(count($this->filter_salescluster_ids)) {
            $where .= " AND netzgebiet_id IN (" . join(", ", $this->filter_salescluster_ids) . ")";
        }

        if($search_cluster_id) {
            $where .= " AND netzgebiet_extref='$search_cluster_id'";
        }

        $cities = [];

        $sql = "SELECT gemeinde, ortschaft FROM view_hausnummer WHERE $where GROUP BY gemeinde,ortschaft_id ORDER BY gemeinde, ortschaft";
        //echo $sql;exit;
        $res = $this->db()->query($sql);
        if($this->db()->num_rows($res)) {
            while($data = $this->db()->fetch_object($res)) {
                if(!array_key_exists($data->gemeinde, $cities)) {
                    $cities[$data->gemeinde] = [];
                }
                $cities[$data->gemeinde][] = $data->ortschaft;
            }
        }

        return mfResponse::Ok(['cities' => $cities]);
    }

    protected function findZip() {
        // unofficially supporting GET and POST in v1
        $get = array_merge($this->post, $this->get);

        // search parameter is unofficially deprecated but still supported
        $search = trim($get['search']);
        $zip = trim($get['zip']);
        $search_cluster_id = trim($get['cluster_id']);

        if(!$search && !$zip && !$search_cluster_id) {
            return mfResponse::BadRequest(['message' => "Searchstring cannot be empty!"]);
        }

        if(!$zip) {
            $zip = $search;
        }

        $zip_search = ['plzstring%' => $zip];
        //var_dump($this->filter_salescluster_ids);exit;
        if(count($this->filter_salescluster_ids)) {
            $zip_search['netzgebiet_id'] = $this->filter_salescluster_ids;
        }

        if($search_cluster_id) {
            $zip_search['netzgebiet_extref'] = $search_cluster_id;
        }

        //var_dump($zip_search);exit;
        $results = ADBPlzModel::search($zip_search);


        $zips = [];
        foreach($results as $result) {
            $zips[] = $result->plz;
        }

        $zips = array_unique($zips);
        return mfResponse::Ok(['zips' => array_values($zips)]);
    }

    protected function findStreet() {
        // unofficially supporting GET and POST in v1
        $get = array_merge($this->post, $this->get);

        // search parameter is unofficially deprecated but still supported
        $search = $this->db()->escape(trim($get['search']));
        $street = $this->db()->escape(trim($get['street']));
        $city = $this->db()->escape(trim($get['city']));
        $zip = $this->db()->escape(trim($get['zip']));
        $search_cluster_id = $this->db()->escape(trim($get['cluster_id']));

        if(!$search && !$street && !$city && !$zip && !$search_cluster_id) {
            return mfResponse::BadRequest(['message' => "No search parameters"]);
        }

        if(!$street) {
            $street = $search;
        }

        $where = "1=1";
        $where .= " AND strasse like '$street%'";
        if($city) {
            if($this->district_is_city) {
                $where .= " AND ortschaft like '$city%'";
            } else {
                $where .= " AND gemeinde like '$city%'";
            }
        }
        if($zip) {
            $where .= " AND plz like '$zip%'";
        }

        if(count($this->filter_salescluster_ids)) {
            $where .= " AND netzgebiet_id IN (" . join(", ", $this->filter_salescluster_ids) . ")";
        }

        if($search_cluster_id) {
            $where .= " AND netzgebiet_extref='$search_cluster_id'";
        }

        $streets = [];

        $sql = "SELECT strasse FROM view_hausnummer WHERE $where GROUP BY strasse ORDER BY strasse";
        //echo "$sql";exit;
        $res = $this->db()->query($sql);
        if($this->db()->num_rows($res)) {
            while($data = $this->db()->fetch_object($res)) {
                $streets[] = $data->strasse;
            }

        }

        $streets = array_unique($streets);
        sort($streets);
        return mfResponse::Ok(['streets' => array_values($streets)]);
    }

    protected function search() {
        $get = array_merge($this->post, $this->get);

        $search = $this->db()->escape(trim($get['search']));

        $format = "flat";
        if($get['format'] == "tree") {
            $format = "tree";
        }

        if(!$search) {
            return mfResponse::BadRequest(['message' => "Search string cannot be empty"]);
        }
        if($search == "%") {
            return mfResponse::BadRequest(['message' => "Search string cannot be empty"]);
        }

        $stati = ADBStatusModel::getAll(true);

        /*
         * do normal search
         */
        $search_parts = [];
        $search = str_replace(",", "", $search);
        $tmp_search_parts = explode(" ", $search);
        $ort_search = $strasse_search = $plz_search = $hausnummer_search = [];

        foreach($tmp_search_parts as $p) {
            $p = $this->db()->escape(trim($p));
            if(!$p) continue;
            if(in_array($p, $search_parts)) continue;
            $search_parts[] = $p;
            $gem_search[] = "Gemeinde.name like '$p%'";
            $ort_search[] = "Ortschaft.name like '$p%'";
            $strasse_search[] = "Strasse.name like '$p%'";
            $plz_search[] = "Plz.plz like '$p%'";
            $hausnummer_search[] = "Hausnummer.hausnummer like '$p%'";
        }

        $where = "1=1";
        if(count($this->filter_salescluster_ids)) {
            $where .= " AND netzgebiet_id IN (" . implode(",", $this->filter_salescluster_ids) . ")";
        }
        if(!$this->me->is("preorderaddressreporting")) {
            $where .= " AND Hausnummer.visibility='public'";
        }

        $sql = AddressDB::$wohneinheit_query;
        $sql .= "\n WHERE $where AND ((" . implode(" OR ", $ort_search) . ") OR (" . implode(" OR ", $strasse_search) . ") OR (" . implode(" OR ", $plz_search) . ") OR (" . implode(" OR ", $hausnummer_search) . ")) ORDER BY strasse, LENGTH(hausnummer), hausnummer, LENGTH(`Wohneinheit`.tuer),`Wohneinheit`.tuer, zusatz, LENGTH(num), num";

        //$sql = "SELECT * FROM view_wohneinheit WHERE $where AND ((".implode(" OR ", $ort_search).") OR (".implode(" OR ", $strasse_search).") OR (".implode(" OR ", $plz_search).") OR (".implode(" OR ", $hausnummer_search).")) ORDER BY strasse, LENGTH(hausnummer), hausnummer, LENGTH(tuer),tuer, zusatz, LENGTH(num), num";
        $this->log->debug($sql);

        $res = $this->db()->query($sql);


        if($this->db()->num_rows($res)) {
            $tmp_addresses = [];
            while($data = $this->db()->fetch_object($res)) {
                $address_key = $data->hausnummer_id;
                if($this->hausnummer_add_zusatz) {
                    $address_key = $data->hausnummer_id . "-" . $data->zusatz;
                }
                //$sort_key = $data->plz." ".$data->gemeinde." ".$data->ortschaft." ".$data->strasse." ".$data->hausnummer." ".$data->zusatz;
                $sort_key = $data->plz . " " . $data->gemeinde . " " . $data->ortschaft . " " . $data->strasse . " " . $data->hausnummer . " " . $data->zusatz;
                if(!array_key_exists($address_key, $tmp_addresses)) {

                    // get allowed preorderTypes
                    $ptypes = [];
                    if($data->freigabe) {
                        $freigaben = json_decode($data->freigabe);
                        if(is_array($freigaben) && count($freigaben)) {
                            foreach($freigaben as $freigabe) {
                                if(in_array($freigabe, $this->allowed_preordertypes)) {
                                    $ptypes[] = $freigabe;
                                }
                            }
                        }

                        // if FCP is on blacklist, don't allow any order types
                        $campaign = $this->campaigns[$this->campaigns_by_scluster[$data->netzgebiet_id]];
                        if(!$campaign) {
                            $this->log->error("Campaign not found for netzgebiet_id " . $data->netzgebiet_id);
                        } else {
                            if($data->rimo_fcp_name && is_array($campaign->banned_fcps) && count($campaign->banned_fcps) && in_array($data->rimo_fcp_name, $campaign->banned_fcps)) {
                                $ptypes = [];
                            }
                        }
                    }

                    $housenumber = $data->hausnummer;
                    if($this->hausnummer_add_zusatz) {
                        if($data->zusatz) {
                            $housenumber .= " (" . $data->zusatz . ")";
                        }
                    }

                    $tmp_addresses[$address_key] = [
                        'sort_key' => $sort_key,
                        'oaid' => $data->hausnummer_oaid,
                        'cluster_id' => $data->netzgebiet_extref,
                        'status' => [],
                        'rimo_external_id' => null,
                        'visibility' => null,
                        'adrcd' => null,
                        'subcd' => null,
                        'zip' => $data->plz,
                        'city' => $data->gemeinde,
                        "municipality" => "",
                        'district' => $data->ortschaft,
                        'street' => $data->strasse,
                        'housenumber' => $housenumber,
                        'stiege' => $data->hausnummer_stiege,
                        'lot_number' => $data->grund_nr,
                        'meridian' => $data->meridian,
                        'rw' => $data->rw,
                        'hw' => $data->hw,
                        'building_unit_count' => 0,
                        'gps_lat' => ($data->gps_lat) ? (float)$data->gps_lat : null,
                        'gps_long' => ($data->gps_long) ? (float)$data->gps_long : null,
                        'rollout_year' => ($data->rollout) ? (int)$data->rollout : null,
                        'rollout_info' => $data->rollout_info,
                        'preorderTypes' => $ptypes,
                        'units' => []
                    ];
                    if($this->district_is_city) {
                        $tmp_addresses[$address_key]['city'] = $data->ortschaft;
                        $tmp_addresses[$address_key]['municipality'] = $data->gemeinde;
                    } else {
                        unset($tmp_addresses[$address_key]['municipality']);
                    }
                    if($this->me->is("preorderaddressreporting")) {
                        $sflags = [];
                        foreach(ADBStatusflagModel::getAll() as $sflag) {
                            $sflag->hausnummer_id = $data->hausnummer_id;
                            $sflags[] = [
                                "code" => (int)$sflag->code,
                                "text" => $sflag->name,
                                "value" => ($sflag->value->value) ? true : false,
                            ];
                        }
                        $status = [
                            "code" => $stati[$data->status_id]->code,
                            "text" => $stati[$data->status_id]->name,
                            "flags" => $sflags,
                        ];
                        $tmp_addresses[$address_key]['status'] = $status;
                        $tmp_addresses[$address_key]['rimo_external_id'] = $data->hausnummer_rimo_id;
                        $tmp_addresses[$address_key]['visibility'] = $data->visibility;
                        $tmp_addresses[$address_key]['adrcd'] = $data->adrcd;
                        $tmp_addresses[$address_key]['subcd'] = $data->subcd;
                    } else {
                        unset($tmp_addresses[$address_key]['status']);
                        unset($tmp_addresses[$address_key]['rimo_external_id']);
                        unset($tmp_addresses[$address_key]["visibility"]);
                        unset($tmp_addresses[$address_key]["adrcd"]);
                        unset($tmp_addresses[$address_key]["subcd"]);
                    }
                }

                $unit_data = [
                    'oaid' => $data->wohneinheit_oaid,
                    'num' => (int)$data->num,
                    'block' => $data->block,
                    'stock' => $data->stock,
                    'tuer' => $data->tuer,
                    'zusatz' => $data->zusatz,
                ];

                if($this->me->is("preorderaddressreporting")) {
                    if($data->partner_company) {
                        $unit_data["ordered"] = true;
                        $unit_data["orderType"] = $data->order_type;
                        $unit_data["ispName"] = $data->partner_company;
                    } else {
                        $unit_data["ordered"] = false;
                        $unit_data["orderType"] = null;
                        $unit_data["ispName"] = null;
                    }
                }

                $tmp_addresses[$address_key]['units'][] = $unit_data;
                $tmp_addresses[$address_key]['building_unit_count']++;
                //if($data->hausnummer_oaid == "AT-8940-30400d5c") var_dump($data);
            }
            //exit;
            //var_dump($tmp_addresses);exit;
            // sort found addresses by count of found keywords
            // if there is an int we don't want to show more than one street
            $sorted_addresses = [];
            foreach($tmp_addresses as $temp_add) {
                $includes_int = false;
                $count = 0;
                $count_parts = 0;
                $uniq_parts_count = 0;
                $uniq_parts_found = [];
                /*if($temp_add['oaid'] == "AT-8940-30400d5c") {
                  var_dump($temp_add);
                }*/
                foreach($search_parts as $p) {
                    $p = $this->db->escape(trim($p));
                    if(strlen($p) === 0) continue;
                    $uniq_parts_count++;
                    if(is_numeric(($p))) {
                        $includes_int = true;
                        $count_parts++;
                        if(substr_count(strtolower($temp_add['sort_key']), strtolower($p))) {
                            $count++;
                        }
                        if(!in_array($p, $uniq_parts_found)) {
                            $uniq_parts_found[] = $p;
                        }
                    } else {
                        $p_count = substr_count(strtolower($temp_add['sort_key']), strtolower($p));
                        if($p_count) {
                            $count += $p_count;
                            $count_parts++;
                            if(!in_array($p, $uniq_parts_found)) {
                                $uniq_parts_found[] = $p;
                            }
                        }
                    }
                }

                unset($temp_add['sort_key']);
                if($includes_int && (($count + 1) - count($search_parts)) < 1) {
                    continue;
                }

                //var_dump(count($search_parts), $count, $count_parts, $temp_add);

                /*if($temp_add['oaid'] == "AT-8954-d6c41665") {
                  echo $temp_add['sort_key'].": AT-8954-d6c41665: count $count; uniq_parts_count $uniq_parts_count; count_parts $count_parts; search_parts ".count($search_parts)."<br /><br />\n";
                  //var_dump();
                  //exit;
                }*/

                if($count_parts == count($search_parts)) {
                    $count += 50;
                }

                if($uniq_parts_count <= count($uniq_parts_found)) {
                    if(!array_key_exists($count, $sorted_addresses)) {
                        $sorted_addresses[$count] = [];
                    }
                    $sorted_addresses[$count][] = $temp_add;
                }
            }
            //exit;


            ksort($sorted_addresses, SORT_NUMERIC);
            $sorted_addresses = array_reverse($sorted_addresses, true);
            //var_dump($sorted_addresses);exit;

            // sort / format
            if($format == "tree") {
                foreach($sorted_addresses as $counted_addresses) {
                    foreach($counted_addresses as $cadd) {
                        $addresses[] = $cadd;
                    }
                }
                //var_dump($addresses);exit;
                //$addresses = array_values($sorted_addresses);

                if(!$addresses) $addresses = [];
            } else {
                foreach($sorted_addresses as $counted_addresses) {
                    foreach($counted_addresses as $ta) {
                        foreach($ta['units'] as $u) {
                            $new_address = [];
                            if($this->me->is("preorderaddressreporting")) {
                                $new_address['ordered'] = $u['ordered'];
                                $new_address['orderType'] = $u['orderType'];
                                $new_address['ispName'] = $u['ispName'];
                                $new_address['status'] = $ta['status'];
                                $new_address['rimo_external_id'] = $ta['rimo_external_id'];
                                $new_address['visibility'] = $ta['visibility'];
                                $new_address['adrcd'] = $ta['adrcd'];
                                $new_address['subcd'] = $ta['subcd'];
                            }
                            $new_address['oaid'] = $u['oaid'];
                            $new_address['building_oaid'] = $ta['oaid'];
                            $new_address['cluster_id'] = $ta['cluster_id'];
                            $new_address['street'] = $ta['street'];
                            $new_address['housenumber'] = $ta['housenumber'];
                            $new_address['stiege'] = $ta['stiege'];
                            $new_address['zip'] = $ta['zip'];
                            $new_address['city'] = $ta['city'];
                            if(array_key_exists("municipality", $ta)) {
                                $new_address['municipality'] = $ta['municipality'];
                            }
                            $new_address['district'] = $ta['district'];
                            $new_address['lot_number'] = $ta['lot_number'];
                            $new_address['meridian'] = $ta['meridian'];
                            $new_address['rw'] = $ta['rw'];
                            $new_address['hw'] = $ta['hw'];
                            $new_address['building_unit_count'] = $ta['building_unit_count'];
                            $new_address['num'] = $u['num'];
                            $new_address['block'] = $u['block'];
                            $new_address['stock'] = $u['stock'];
                            $new_address['tuer'] = $u['tuer'];
                            $new_address['zusatz'] = $u['zusatz'];
                            $new_address['gps_lat'] = $ta['gps_lat'];
                            $new_address['gps_long'] = $ta['gps_long'];
                            $new_address['rollout_year'] = $ta['rollout_year'];
                            $new_address['rollout_info'] = $ta['rollout_info'];
                            $new_address['preorderTypes'] = $ta['preorderTypes'];

                            $addresses[] = $new_address;
                        }
                    }
                }
            }

        }

        return mfResponse::Ok(['addresses' => $addresses]);
    }

    protected function find() {
        // unofficially supporting GET and POST in v1
        $get = array_merge($this->post, $this->get);

        $search_cluster_id = $this->db()->escape(trim($get['cluster_id']));
        $search_street = $this->db()->escape(trim($get['street']));
        $search_zip = $this->db()->escape(trim($get['zip']));
        $search_district = $this->db()->escape(trim($get['district']));
        $search_city = $this->db()->escape(trim($get['city']));
        $search_housenumber = $this->db()->escape(trim($get['housenumber']));
        $search_stiege = $this->db()->escape(trim($get['stiege']));

        $format = "flat";
        if($get['format'] == "tree") {
            $format = "tree";
        }

        if(!$search_street && !$search_cluster_id) {
            return mfResponse::BadRequest(['message' => "Searchstring cannot be empty!"]);
        }

        $addresses = [];
        $netzgebiete = [];
        $netzgebiete_unindexed = ADBNetzgebietModel::getAll();
        foreach($netzgebiete_unindexed as $nu) {
            $netzgebiete[$nu->id] = $nu;
        }

        $stati = ADBStatusModel::getAll(true);

        $where = "1=1";

        if($search_zip && $search_zip != "%") {
            $where .= " AND Plz.plz like '$search_zip%'";
        }
        if($search_district && $search_district != "%") {
            $where .= " AND Ortschaft.name like '$search_district%'";
        }
        if($search_city && $search_city != "%") {
            if($this->district_is_city) {
                $where .= " AND Ortschaft.name like '$search_city%'";
            } else {
                $where .= " AND Gemeinde.name like '$search_city%'";
            }
        }
        if($search_street && $search_street != "%") {
            $where .= " AND Strasse.name like '$search_street%'";
        }
        if($search_housenumber && $search_housenumber != "%") {
            $where .= " AND Hausnummer.hausnummer like '$search_housenumber%'";
        }
        if($search_stiege && $search_stiege != "%") {
            $where .= " AND Hausnummer.stiege like '$search_stiege%'";
        }

        if(count($this->filter_salescluster_ids)) {
            $where .= " AND Hausnummer.netzgebiet_id IN (" . implode(",", $this->filter_salescluster_ids) . ")";
        }
        if($search_cluster_id) {
            $where .= " AND Netzgebiet.extref='$search_cluster_id'";
        }
        if(!$this->me->is("preorderaddressreporting")) {
            $where .= " AND Hausnummer.visibility='public'";
        }
        //echo $where;
        //var_dump($this->filter_salescluster_ids);exit;

        $sql = AddressDB::$wohneinheit_query;
        $sql .= "\n WHERE $where ORDER BY plz, gemeinde, ortschaft, strasse, LENGTH(hausnummer), hausnummer, LENGTH(hausnummer_stiege), hausnummer_stiege, `Wohneinheit`.block, `Wohneinheit`.stock, LENGTH(num), num, LENGTH(`Wohneinheit`.tuer), `Wohneinheit`.tuer";

        //$sql = "SELECT * FROM view_wohneinheit WHERE $where ORDER BY plz, gemeinde, ortschaft, strasse, LENGTH(hausnummer), hausnummer, block, stiege, stock, LENGTH(num), num, LENGTH(tuer), tuer";
        $this->log->debug($sql);
        $res = $this->db()->query($sql);

        if($this->db()->num_rows($res)) {
            $tmp_addresses = [];
            while($data = $this->db()->fetch_object($res)) {

                // never return addresses without OAID if OAID is required in Network
                if((!$data->hausnummer_oaid || !$data->wohneinheit_oaid) && $netzgebiete[$data->netzgebiet_id]->unit_create_oaid) continue;

                $address_key = $data->hausnummer_id;
                if($this->hausnummer_add_zusatz) {
                    $address_key = $data->hausnummer_id . "-" . $data->zusatz;
                }
                if(!array_key_exists($address_key, $tmp_addresses)) {

                    // get allowed preorderTypes
                    $ptypes = [];
                    if($data->freigabe) {
                        $freigaben = json_decode($data->freigabe);
                        if(is_array($freigaben) && count($freigaben)) {
                            foreach($freigaben as $freigabe) {
                                if(in_array($freigabe, $this->allowed_preordertypes)) {
                                    $ptypes[] = $freigabe;
                                }
                            }
                        }

                        // if FCP is on blacklist, don't allow any order types
                        $campaign = $this->campaigns[$this->campaigns_by_scluster[$data->netzgebiet_id]];
                        if(!$campaign) {
                            $this->log->error("Campaign not found for netzgebiet_id " . $data->netzgebiet_id);
                        } else {
                            if($data->rimo_fcp_name && is_array($campaign->banned_fcps) && count($campaign->banned_fcps) && in_array($data->rimo_fcp_name, $campaign->banned_fcps)) {
                                $ptypes = [];
                            }
                        }
                    }

                    $housenumber = $data->hausnummer;
                    if($this->hausnummer_add_zusatz) {
                        if($data->zusatz) {
                            $housenumber .= " (" . $data->zusatz . ")";
                        }
                    }

                    $tmp_addresses[$address_key] = [
                        'oaid' => $data->hausnummer_oaid,
                        'cluster_id' => $data->netzgebiet_extref,
                        'status' => [],
                        'rimo_external_id' => null,
                        'visibility' => null,
                        'adrcd' => null,
                        'subcd' => null,
                        'zip' => $data->plz,
                        'city' => $data->gemeinde,
                        "municipality" => "",
                        'district' => $data->ortschaft,
                        'street' => $data->strasse,
                        'housenumber' => $housenumber,
                        'stiege' => $data->hausnummer_stiege,
                        'lot_number' => $data->grund_nr,
                        'meridian' => $data->meridian,
                        'rw' => $data->rw,
                        'hw' => $data->hw,
                        'building_unit_count' => 0,
                        'gps_lat' => ($data->gps_lat) ? (float)$data->gps_lat : null,
                        'gps_long' => ($data->gps_long) ? (float)$data->gps_long : null,
                        'rollout_year' => ($data->rollout) ? (int)$data->rollout : null,
                        'rollout_info' => $data->rollout_info,
                        'preorderTypes' => $ptypes,
                        'units' => []
                    ];
                    if($this->district_is_city) {
                        $tmp_addresses[$address_key]['city'] = $data->ortschaft;
                        $tmp_addresses[$address_key]['municipality'] = $data->gemeinde;
                    } else {
                        unset($tmp_addresses[$address_key]['municipality']);
                    }
                    if($this->me->is("preorderaddressreporting")) {
                        $sflags = [];
                        foreach(ADBStatusflagModel::getAll() as $sflag) {
                            $sflag->hausnummer_id = $data->hausnummer_id;
                            $sflags[] = [
                                "code" => (int)$sflag->code,
                                "text" => $sflag->name,
                                "value" => ($sflag->value->value) ? true : false,
                            ];
                        }
                        $status = [
                            "code" => $stati[$data->status_id]->code,
                            "text" => $stati[$data->status_id]->name,
                            "flags" => $sflags,
                        ];
                        $tmp_addresses[$address_key]['status'] = $status;
                        $tmp_addresses[$address_key]['rimo_external_id'] = $data->hausnummer_rimo_id;
                        $tmp_addresses[$address_key]['visibility'] = $data->visibility;
                        $tmp_addresses[$address_key]['adrcd'] = $data->adrcd;
                        $tmp_addresses[$address_key]['subcd'] = $data->subcd;
                    } else {
                        unset($tmp_addresses[$address_key]["status"]);
                        unset($tmp_addresses[$address_key]['rimo_external_id']);
                        unset($tmp_addresses[$address_key]["visibility"]);
                        unset($tmp_addresses[$address_key]["adrcd"]);
                        unset($tmp_addresses[$address_key]["subcd"]);

                    }
                }

                $unit_data = [
                    'oaid' => $data->wohneinheit_oaid,
                    'num' => (int)$data->num,
                    'block' => $data->block,
                    'stock' => $data->stock,
                    'tuer' => $data->tuer,
                    'zusatz' => $data->zusatz,
                ];
                if($this->me->is("preorderaddressreporting")) {
                    if($data->partner_company) {
                        $unit_data["ordered"] = true;
                        $unit_data["orderType"] = $data->order_type;
                        $unit_data["ispName"] = $data->partner_company;
                    } else {
                        $unit_data["ordered"] = false;
                        $unit_data["orderType"] = null;
                        $unit_data["ispName"] = null;
                    }
                }
                $tmp_addresses[$address_key]['units'][] = $unit_data;
                $tmp_addresses[$address_key]['building_unit_count']++;


            }
            if($format == "tree") {
                /*foreach($tmp_addresses as $ta) {
                  $addresses[] = $ta;
                }*/
                $addresses = array_values($tmp_addresses);
            } else {
                foreach($tmp_addresses as $ta) {
                    foreach($ta['units'] as $u) {
                        $new_address = [];
                        if($this->me->is("preorderaddressreporting")) {
                            $new_address['ordered'] = $u['ordered'];
                            $new_address['orderType'] = $u['orderType'];
                            $new_address['ispName'] = $u['ispName'];
                            $new_address['status'] = $ta['status'];
                            $new_address['rimo_external_id'] = $ta['rimo_external_id'];
                            $new_address['visibility'] = $ta['visibility'];
                            $new_address['adrcd'] = $ta['adrcd'];
                            $new_address['subcd'] = $ta['subcd'];
                        }
                        $new_address['oaid'] = $u['oaid'];
                        $new_address['building_oaid'] = $ta['oaid'];
                        $new_address['cluster_id'] = $ta['cluster_id'];
                        $new_address['street'] = $ta['street'];
                        $new_address['housenumber'] = $ta['housenumber'];
                        $new_address['stiege'] = $ta['stiege'];
                        $new_address['zip'] = $ta['zip'];
                        $new_address['city'] = $ta['city'];
                        if(array_key_exists("municipality", $ta)) {
                            $new_address['municipality'] = $ta['municipality'];
                        }
                        $new_address['district'] = $ta['district'];
                        $new_address['lot_number'] = $ta['lot_number'];
                        $new_address['meridian'] = $ta['meridian'];
                        $new_address['rw'] = $ta['rw'];
                        $new_address['hw'] = $ta['hw'];
                        $new_address['building_unit_count'] = $ta['building_unit_count'];
                        $new_address['num'] = $u['num'];
                        $new_address['block'] = $u['block'];
                        $new_address['stock'] = $u['stock'];
                        $new_address['tuer'] = $u['tuer'];
                        $new_address['zusatz'] = $u['zusatz'];
                        $new_address['gps_lat'] = $ta['gps_lat'];
                        $new_address['gps_long'] = $ta['gps_long'];
                        $new_address['rollout_year'] = $ta['rollout_year'];
                        $new_address['rollout_info'] = $ta['rollout_info'];
                        $new_address['preorderTypes'] = $ta['preorderTypes'];

                        $addresses[] = $new_address;
                    }
                }
            }

        }

        return mfResponse::Ok(['addresses' => $addresses]);
    }

    protected function getAddressPricing($oaid) {
        if(!$oaid) {
            return mfResponse::BadRequest(['message' => "OAID missing"]);
        }

        $unit = ADBWohneinheitModel::getFirst(["oaid" => $oaid]);

        if($unit) {
            $hausnummer = $unit->hausnummer;
        } else {
            $hausnummer = ADBHausnummerModel::getFirst(["oaid" => $oaid]);
        }

        if(!$hausnummer) {
            return mfResponse::NotFound(['message' => "Address not found"]);
        }

        $network = NetworkModel::getFirst(["adb_netzgebiet_id" => $hausnummer->netzgebiet_id]);
        $netowner_id = $network->owner_id;

        $campaign = PreordercampaignModel::getFirst(["network_id" => $network->id]);
        $netop = PreordercampaignOperatorModel::getFirst(["isp_id" => $this->me->address_id, "preordercampaign_id" => $campaign->id]);

        if(!$netop) {
            $netop = PreordercampaignOperatorModel::getFirst(["operator_id" => $this->me->address_id, "preordercampaign_id" => $campaign->id]);
        }

        if(!$netop) {
            if($campaign->network->owner_id != $this->me->address_id) {
                return mfResponse::Unauthorized();
            }

            $netop = PreordercampaignOperatorModel::getFirst(["preordercampaign_id" => $campaign->id]);

        }

        $netoperator = $netop->operator;

        $prices = [];
        $enduser_setup_product_id = false;
        foreach(PreorderProduct::getWithTypes() as $product) {
            if($product->type == "enduser_setup") {
                $enduser_setup_product_id = $product->id;
            }
            $product->setNetownerId($netowner_id);
            if($netoperator) {
                $product->setNetoperatorId($netoperator->id);
            }

            $prices[$product->type] = $product->getCampaignPrice($campaign->id);
        }

        $prices_return = [
            "oaid" => $oaid,
            "enduser_setup_price_net" => round((float)$prices["enduser_setup"]->price_setup, 2),
            "enduser_setup_price_gross" => round((float)$prices["enduser_setup"]->price_setup * 1.2, 2),
            "enduser_setup_info" => $prices["enduser_setup"]->description,
            "enduser_setup_is_discount" => ($prices["enduser_setup"]->end_date) ? true : false,
            "enduser_setup_valid_until" => $prices["enduser_setup"]->end_date,
            "vatrate" => 20,
        ];


        if($unit->enduser_setup_invoice_date || PreorderBilling::getFirst(["adb_wohneinheit_id" => $unit->id, "product_id" => $enduser_setup_product_id, "invoice_id" => true])) {
            $prices_return["enduser_setup_price_net"] = 0;
            $prices_return["enduser_setup_price_gross"] = 0;
            $prices_return["enduser_setup_info"] = "paid";
            $prices_return["enduser_setup_is_discount"] = false;
            $prices_return["enduser_setup_valid_until"] = null;
        }

        if($hausnummer->oaid == "AT-8952-67570191" || $hausnummer->oaid == "AT-8952-622a69bb") {
            //var_dump(PreorderBilling::getFirst(["adb_wohneinheit_id" => $unit->id, "product_id" => $enduser_setup_product_id, "invoice_id" => true]));exit;
            $prices_return["enduser_setup_price_net"] = 600;
            $prices_return["enduser_setup_price_gross"] = 720;
            $prices_return["enduser_setup_info"] = "Aktion Kienach 9";
            $prices_return["enduser_setup_is_discount"] = true;
            $prices_return["enduser_setup_valid_until"] = "2025-04-30";
        }

        return mfResponse::Ok($prices_return);
    }

    protected function exportAddresses() {
        //if($this->me->username != 'r.eschner@rmlinfrastruktur.at') {
        //    return mfResponse::Forbidden();
        //}

        if($this->me->is("Admin")) {
            $my_networks = NetworkModel::getAll();
        } else {
            $my_networks = $this->me->myNetworks(["netowner", "salespartner"]);
        }

        $netzgebiet_ids = [];
        $adb_network_ids = [];
        foreach($my_networks as $network) {
            if($network->adb_netzgebiet_id && !in_array($network->adb_netzgebiet_id, $netzgebiet_ids)) {
                $netzgebiet_ids[] = $network->id;
                $adb_network_ids[] = $network->adb_netzgebiet_id;
            }
        }

        //var_dump($adb_network_ids);exit;
        $sql = "SELECT * FROM Hausnummer WHERE netzgebiet_id IN (".implode(",", $adb_network_ids).")";
        //echo "$sql\n";exit;
        $res = $this->db()->query($sql);

        $tpl = new Layout();
        $tpl->setTemplate("AddressDB/export.csv");
        $tpl->set("res", $res);
        $tpl->set("no_filename", true);
        $tpl->display();
        //$csv = $tpl->render();
        //echo $csv;
        exit;

    }
}