<?php

class Admin_RtrReporting {
    private $request;
    private $db;
    private $log;
    private $flash = [];
    private $systemowner_id = 1;
    private $systemowner_rtr_id = 3452;
    private $energie_bb_id = 209;

    private $netowner_is_systemowner = [
        1473,
        421,

    ];
    private $additional_owner_networks = [
        7, // mortantsch
    ];
    private $ort_replace = [
        "St.Ruprecht/Raab" => "Sankt Ruprecht an der Raab",
        "St. Ruprecht / Raab" => "Sankt Ruprecht an der Raab",
        "St. Ruprecht a.d. Raab" => "Sankt Ruprecht an der Raab",
        "St. Ruprecht a.d. R." => "Sankt Ruprecht an der Raab",
        "St. Ruprecht/Raab" => "Sankt Ruprecht an der Raab",
        "St. Ruprecht" => "Sankt Ruprecht an der Raab",
        "Seieersberg" => "Seiersberg",
        "St.Stefan im Rosental" => "Sankt Stefan im Rosental",
        "St Stefan im Rosental" => "Sankt Stefan im Rosental",
        "St. Stefan i.R." => "Sankt Stefan im Rosental",
        "St. Stefan i. R." => "Sankt Stefan im Rosental",
        "St.Stefan i. R." => "Sankt Stefan im Rosental",
        "St.Stefan i.R." => "Sankt Stefan im Rosental",
        "St. Stefab i. R." => "Sankt Stefan im Rosental",
        "St. Stefan i.R" => "Sankt Stefan im Rosental",
        "St. Stefan/R." => "Sankt Stefan im Rosental",
        "St.Stefan /Rosental" => "Sankt Stefan im Rosental",
        "Pölfing-Bunn" => "Pölfing-Brunn",
        "Pölfing Brunn" => "Pölfing-Brunn",
        "Eibsiwald" => "Eibiswald",
        "Wolfsberg" => "Schwarzautal",
        "Wolfsberg i. Sch." => "Schwarzautal",
        "Wolfsberg i. Schwarzautal" => "Schwarzautal",
        "Wolfsberg i.S." => "Schwarzautal",
        "Wolfsberg im Scharzautal" => "Schwarzautal",
        "Wolfsberg im Schwarzautal" => "Schwarzautal",
        "Wolfberg im Schwarzautal" => "Schwarzautal",
        "Schwarzau" => "Schwarzautal",
        "Sankt Radegund" => "Sankt Radegund bei Graz",
        "Sankt  Radegund" => "Sankt Radegund bei Graz",
        "St. Radegund" => "Sankt Radegund bei Graz",
        "Judendorf-Straßengel" => "Gratwein-Straßengel",
        "Judendorf-Strassengel" => "Gratwein-Straßengel",
        "Gartwein-Straßengel" => "Gratwein-Straßengel",
        "Gratwein" => "Gratwein-Straßengel",
        "Fladnitz" => "Fladnitz an der Teichalm",
        "Fladnitz a.d.T." => "Fladnitz an der Teichalm",
        "Fladnitz a. d. T." => "Fladnitz an der Teichalm",
        "Fladnitz/Teichalm" => "Fladnitz an der Teichalm",
        "Tulwitz" => "Fladnitz an der Teichalm",
        "Fladnitz/T." => "Fladnitz an der Teichalm",
        "Fladnitz a. d. Teichalm" => "Fladnitz an der Teichalm",
        //"Stübing" => "Deutschfeistritz",
        "Stübing" => "Gratkorn",
        "Deutsch Feistritz" => "Deutschfeistritz",
        "Minihof Liebau" => "Minihof-Liebau",
        "Windisch-Minihof" => "Minihof-Liebau",
        "Windisch Minihof" => "Minihof-Liebau",
        "St. Anna / Aigen" => "Sankt Anna am Aigen",
        "St.Anna" => "Sankt Anna am Aigen",
        "St.Anna/Aigen" => "Sankt Anna am Aigen",
        "St. Anna /Aigen" => "Sankt Anna am Aigen",
        "St. Anna/A." => "Sankt Anna am Aigen",
        "St. Margarethen" => "St. Margarethen an der Raab",
        "St. Margarethen a.d.Raab" => "St. Margarethen an der Raab",
        "St. Margarethen a.d.R." => "St. Margarethen an der Raab",
        "Passeil" => "Passail",
        "Neudorf b. Passail" => "Passail",
        "Neudorf bei Passail" => "Passail",
        "Edelsbach" => "Edelsbach bei Feldbach",
        "Neuhaus am Klausenb." => "Neuhaus am Klausenbach",
        "Kirchberg" => "Kirchberg an der Raab",
        "Kirchberg a.d. Raab" => "Kirchberg an der Raab",
        "Kirchberg a.d. R." => "Kirchberg an der Raab",
        "Kirchberg a. d. Raab" => "Kirchberg an der Raab",
        "Kirchberg a. d.  Raab" => "Kirchberg an der Raab",
        "Kalsdorf" => "Kalsdorf bei Graz",
        "Strass" => "Straß in Steiermark",
        "St.Oswald o.E." => "Eibiswald",
        "St. Peter i. S." => "Sankt Peter im Sulmtal",
        "St. Peter i. Sulmtal" => "Sankt Peter im Sulmtal",
        "St. Nikolai i.S." => "Sankt Nikolai im Sausal",
        "St. Nikoai im Sausal" => "Sankt Nikolai im Sausal",
        "Limberg B. Wies" => "Wies",
        "Limberg bei Wies" => "Wies",
        "Kitzeck" => "Kitzeck im Sausal",
        "Nikolai i. S." => "Sankt Nikolai im Sausal",
        "St.Nikolai i.S." => "Sankt Nikolai im Sausal",
        "St. Nikolai i. S." => "Sankt Nikolai im Sausal",
        "St. Nikolai i. Sausal" => "Sankt Nikolai im Sausal",
        "Braunau/Inn" => "Braunau am Inn",
        "Hohenbrugg" => "Fehring",
        "Seiersberg - Pirka" => "Seiersberg-Pirka",
        "Heiligenkreuz a. W." => "Heiligenkreuz am Waasen",
        "Heiligen Kreuz am Waasen" => "Heiligenkreuz am Waasen",
        "Heiligenkreuz a.W." => "Heiligenkreuz am Waasen",
        "Heiligenkreuz /W" => "Heiligenkreuz am Waasen",
        "Heiligenkreuz a/W" => "Heiligenkreuz am Waasen",
        "Rettenbach in der Oststeiermark" => "Pirching am Traubenberg",
        "Rettenbach" => "Pirching am Traubenberg",
        "Pirching" => "Pirching am Traubenberg",
        "St. Ulrich im Greith" => "Sankt Martin im Sulmtal",
        "St. Martin i. S." => "Sankt Martin im Sulmtal",
        "St. Georgen/Stiefing" => "Sankt Georgen an der Stiefing",
        "St. Georgen a. d. Stiefing" => "Sankt Georgen an der Stiefing",
        "St.Georgen a/d Stief." => "Sankt Georgen an der Stiefing",
        "St. Georgen" => "Sankt Georgen an der Stiefing",
        "St. Johann im Sausal" => "Sankt Johann im Saggautal",
        "Naas bei Weiz" => "Naas",
        "Nöstl bei Weiz" => "Weiz",
        "Weiz/Krottendorf" => "Weiz",
        "St. Andrä Höch" => "Sankt Andrä-Höch",
        "St.Andrä/Sausal" => "Sankt Andrä-Höch",
        "Sankt Andrä Höch" => "Sankt Andrä-Höch",
        "St. Andrä/Höch" => "Sankt Andrä-Höch",
        "St. Andrä - Höch" => "Sankt Andrä-Höch",
        "St. Andrä i/S" => "Sankt Andrä-Höch",
        "St. Andrä" => "Sankt Andrä-Höch",
        "St. Andrä /Höch" => "Sankt Andrä-Höch",
        "Hartl bei Kaindorf" => "Hartl",
        "Hartl/Tiefenbach" => "Hartl",
        "Tilmitsch" => "Tillmitsch",
        "St.Marein b. Graz" => "Sankt Marein bei Graz",
        "St. Marein b. Graz" => "Sankt Marein bei Graz",
        "St. Marein / Graz" => "Sankt Marein bei Graz",
        "St. Marein" => "Sankt Marein bei Graz",
        "St. MArein b. Graz" => "Sankt Marein bei Graz",
        "Weiz - Thannhausen" => "Thannhausen",
        "Hof bei Straden" => "Tieschen",
        "Kopfing bei Kaindorf" => "Kaindorf",
        "Wettmanstätten" => "Wettmannstätten",
        "Kirchbach" => "Kirchbach-Zerlach",
        "KIRCHBACH" => "Kirchbach-Zerlach",
        "Kirchbach - Zerlach" => "Kirchbach-Zerlach",
        "Kirchbach Zerlach" => "Kirchbach-Zerlach",
        "Kirchbach/Zerlach" => "Kirchbach-Zerlach",
        "Kirchbach in der Steiermark" => "Kirchbach-Zerlach",
        "Groß-St. Florian" => "Groß Sankt Florian",
        "Groß St. Florian" => "Groß Sankt Florian",
        "Lang/Lebring" => "Lang",
        "St.Stefan" => "Sankt Stefan ob Stainz",
        "St.Stefan/Stainz" => "Sankt Stefan ob Stainz",
        "St.Josef/Stainz" => "Sankt Josef (Weststeiermark)",
        "Rosental" => "Rosental an der Kainach",
        "Krottendorf" => "Krottendorf-Gaisfeld",
        "Breitenfeld" => "Riegersburg",
        "Mortanstch" => "Mortantsch",
        "Eggersdorf" => "Eggersdorf bei Graz",
        "Dobl - Zwaring" => "Dobl-Zwaring",
        "Steyr-Gleink" => "Dietach",

        //"" => "Sankt Ruprecht an der Raab",
        //"" => "Sankt Ruprecht an der Raab",
        //"" => "Sankt Ruprecht an der Raab",
        //"" => "Sankt Ruprecht an der Raab",
        //"" => "Sankt Ruprecht an der Raab",
        //"" => "Sankt Ruprecht an der Raab",
        //"" => "Sankt Ruprecht an der Raab",

    ];

    public function __construct($request = false) {
        $this->request = $request;
        $this->db = FronkDB::singleton();
        $this->log = mfLoghandler::singleton();

    }

    public function runRequest() {
        $action = $this->request->do;
        if(!$action) {
            return $this->indexAction();
        } else {
            $method = $action."Action";
            if(method_exists($this, $method)) {
                return $this->$method();
            } else {
                throw new Exception("Method not found", "404");
            }
        }
    }

    public function indexAction() {

        return [
            "template" => "Admin/RtrReporting/Index",
            "redirect" => "",
            "templateVars" => []
        ];
    }

    public function activeVoiceNumbersReportAction() {
        $csv = "number_start;number_end;date_reported;type;csp;note\n";
        $today = new DateTime();
        $today_string = $today->format('Y-m-d');

        foreach(VoicenumberModel::search(["contract_id" => true]) as $number) {
            //$ported = ($number->voicenumberblock_id) ? false : true;

            if(in_array($number->number, [4331528575200,4331528575400,4331528575500]) ) {
                continue;
            }
            if(substr($number->number, 0, 9) == "437208004") continue;
            if(substr($number->number, 0, 8) == "43720102") continue;
            if(substr($number->number, 0, 8) == "43720103") continue;
            if(substr($number->number, 0, 10) == "4331154520") continue;

            $csv .= $number->number.";";
            $csv .= $number->number.";";
            $csv .= $today_string.";";
            $csv .= "UA;";
            $csv .= ";";
            $csv .= ";\n";
        }

        header("Content-type: text/csv; charset=utf-8");
        header('Content-disposition: attachment; filename="rtr-active-numbers-report-'.date('Y-m-d_H-i-s').'.csv"');

        echo $csv;
        exit;

    }

    public function a10reportAction() {
        // alle anschlüsse

        $rasterpunkte = [];
        foreach(BuildingModel::getAll() as $building) {
            // limit to sytemowners networks
            if($building->network->owner_id != $this->systemowner_id && !in_array($building->network->owner_id, $this->additional_owner_networks)) continue;

            $raster = $building->laea;
            if(!$raster) continue;

            $prod_code = 14310;
            $bb = "1000,00"; // glas = 1000, funk = 50
            //$user_count = TerminationModel::count(["building_id" => $building->id]);
            $rtr100m = Rtr100mRaster::getFirst(["raster" => $raster]);
            if(!$rtr100m) {
                die("RTR Raster fehlt ".$raster);
            }
            $user_count = $rtr100m->unit_count;
            $unit_sum = $building->units;

            if(!array_key_exists($raster, $rasterpunkte)) {
                $rasterpunkte[$raster] = [];
            }
            if(!array_key_exists($prod_code, $rasterpunkte[$raster])) {
                $rasterpunkte[$raster][$prod_code] = [
                    "bb" => $bb,
                    "unit_sum" => 0,
                    "user_count" => 0
                ];
            }

            $rasterpunkte[$raster][$prod_code]["unit_sum"] += $unit_sum;
            $rasterpunkte[$raster][$prod_code]["user_count"] = $user_count;

        }

        $building = new Building();
        $eigen_product_ids = $this->getEigenProductIds();
        foreach(ContractModel::searchActive(["product_id" => $eigen_product_ids]) as $contract) {
            if($contract->termination_id) continue;

            $address = $contract->owner;
            if(!$address->gps_lat || !$address->gps_long || !$address->laea) {
                $address->save(); // gets gps and laea after saving
            }

            $raster = $address->laea;
            if(!$raster) continue;

            $rtr_code = $contract->product->attributes["rtr_tech_code"]->value;
            if(substr($rtr_code, 0, 4) == 1431) {
                $prod_code = 14310;
                $bb = "1000,00";
            } elseif(substr($rtr_code, 0, 4) == 1042) {
                $prod_code = 10420;
                $bb = "50,00";
            } elseif(substr($rtr_code, 0, 4) == 1222) {
                $prod_code = 12220;
                $bb = "400,00";
            } else {
                continue; // incompatible product
            }

            if(!array_key_exists($raster, $rasterpunkte)) {
                $rasterpunkte[$raster] = [];
            }
            if(!array_key_exists($prod_code, $rasterpunkte[$raster])) {
                $rasterpunkte[$raster][$prod_code] = [
                    "bb" => $bb,
                    "unit_sum" => 0,
                    "user_count" => 0
                ];
            }

            $rtr100m = Rtr100mRaster::getFirst(["raster" => $raster]);
            if(!$rtr100m) {
                $this->log->warn(__METHOD__.": RTR Raster fehlt ".$raster);
                continue;
            }

            $rasterpunkte[$raster][$prod_code]["unit_sum"]++;
            $rasterpunkte[$raster][$prod_code]["user_count"] = $rtr100m->unit_count;

        }
        $rasterpunkte["100mN26933E47445"] = [
            14310 => [
                "bb" => 1000,
                "unit_sum" => 26,
                "user_count" => 26
            ],
        ];

        $csv_header = "rasterid;code;dl_min_max_bb;dl_q25_max_bb;dl_avg_max_bb;dl_max_max_bb;ul_min_max_bb;ul_q25_max_bb;ul_avg_max_bb;ul_max_max_bb;dl_min_n_bb;dl_q25_n_bb;dl_avg_n_bb;dl_avg_n_bb;ul_min_n_bb;ul_q25_n_bb;ul_avg_n_bb;ul_avg_n_bb;anz_anschl_cov";
        $csv = $csv_header."\n";
        foreach($rasterpunkte as $rastercode => $raster) {
            foreach($raster as $prod_code => $data) {
                $csv .= $rastercode . ";";
                $csv .= $prod_code . ";";
                for($i = 0; $i < 4; $i++) {
                    if($prod_code == 12220) {
                        $csv .= "400;";
                    } else {
                        $csv .= $data["bb"] . ";";
                    }
                }
                for($i = 0; $i < 4; $i++) {
                    if($prod_code == 12220) {
                        $csv .= "100;";
                    } else {
                        $csv .= $data["bb"] . ";";
                    }
                }
                for($i = 0; $i < 4; $i++) {
                    if($prod_code == 12220) {
                        $csv .= "400;";
                    } else {
                        $csv .= $data["bb"] . ";";
                    }
                }
                for($i = 0; $i < 4; $i++) {
                    if($prod_code == 12220) {
                        $csv .= "100;";
                    } else {
                        $csv .= $data["bb"] . ";";
                    }
                }
                $csv .= ($data["user_count"]) ?: ($data["unit_sum"]) ?: 1;
                $csv .= "\n";
            }
        }

        header("Content-type: text/csv; charset=utf-8");
        header('Content-disposition: attachment; filename="rtr-A10-report-'.date('Y-m-d_H-i-s').'.csv"');

        echo $csv;
        exit;
    }

    protected function b10reportAction() {
        $data = [];
        $adb = FronkDB::singleton(ADDRESSDB_DBHOST, ADDRESSDB_DBUSER, ADDRESSDB_DBPASS, ADDRESSDB_DBNAME);

        $ort2gem = [];

        foreach(ContractModel::searchActive([]) as $contract) {
            if(!$contract->product->attributes["rtr_tech_code"]->value) continue;
            if($contract->price < 0.0000) continue; // netzbetreiber gutschrift
            if($contract->vatgroup_id == 4) continue;// vat code provisionen

            $prod_code = $contract->product->attributes["rtr_tech_code"]->value;

            $bb_down = $contract->product->attributes["bw_down"]->value;
            $bb_up = $contract->product->attributes["bw_up"]->value;

            if(!$bb_down && substr($prod_code,0,4) == "1042") {
                // funkprodukt
                $bb_down = 50;
                $bb_up = 10;
            }

            if(!$bb_down) {
                echo "Keine BB für ".$contract->product_name." ".$contract->id."\n";
                exit;
            }

            $is_sym = false;

            $bb_code = 0;
            $bb_code_down = 0;
            $bb_code_up = 0;

            if($bb_down == $bb_up) {
                $is_sym = true;
                $bb_code = $this->getRtrBBCode($bb_down, "sym");
            } else {
                $bb_code_down = $this->getRtrBBCode($bb_down, "down");
                $bb_code_up = $this->getRtrBBCode($bb_up, "up");
            }


            if($contract->termination_id) {
                $strasse = trim($contract->termination->building->street);
                $plz = trim($contract->termination->building->zip);
                $ort = trim($contract->termination->building->city);
            } else {
                $strasse = trim($contract->owner->street);
                $plz = trim($contract->owner->zip);
                $ort = trim($contract->owner->city);
            }

            if($contract->id == 13595 || $contract->id == 14855) { // kein Anschluss da OAN, Kunde in Deutschland
                $strasse = "Hatzendorf 79a";
                $plz = "8361";
                $ort = "Hatzendorf";
            }


            // get Gemeinde ID

            if(!$ort) {
                continue;
            }

            $ort = preg_replace('/\s+/', ' ', $ort);
            $orte = [$ort];

            if(substr($ort, 0, 4) == "St. " || substr($ort, 0, 4) == "St, ") {
                $orte[] = preg_replace('/^St[.,]/', "Sankt", $ort);
            }
            if(substr($ort, 0, 3) == "St." || substr($ort, 0, 3) == "St,") {
                $orte[] = preg_replace('/^St[.,]/', "Sankt ", $ort);
            }

            if(array_key_exists($ort, $this->ort_replace)) {
                $orte[] = $this->ort_replace[$ort];
            }

            if(!array_key_exists("$plz--$ort", $ort2gem)) {
                $sql = AddressDB::$wohneinheit_query;
                $where = " WHERE Plz.plz='$plz' AND Gemeinde.name IN ('".implode("','", $orte)."') LIMIT 1";
                $sql .= $where;

                //$this->log->debug("$sql\n$where");
                $res = $adb->query($sql);

                if(!$adb->num_rows($res)) {
                    $sql = AddressDB::$wohneinheit_query;
                    $where = " WHERE Plz.plz='$plz' AND Ortschaft.name IN ('".implode("','", $orte)."') LIMIT 1";
                    //$this->log->debug("$sql\n$where");
                    $sql .= $where;

                    $res = $adb->query($sql);

                    if(!$adb->num_rows($res)) {
                        echo "Geminde nicht gefunden Plz.plz='$plz' AND Ortschaft.name='$ort' Contract ".$contract->id."\n";
                        print_r($contract);
                        print_r($contract->owner);
                        exit;
                    }
                }

                $row = $adb->fetch_object($res);

                if(!$row->gemeinde_kennziffer) {
                    echo "Keine Gem KZ $plz $ort\n";
                    exit;
                }

                $gemid = $row->gemeinde_kennziffer;
                //$this->log->debug("Adding '$plz--$ort' to $gemid");
                $ort2gem["$plz--$ort"] = $gemid;
            } else {
                $gemid = $ort2gem["$plz--$ort"];
            }

            if($gemid == 61761 && preg_match('/^plenzengreith/i', $strasse)) {
                $gemid = 61763;
            }

            /*if($gemid == 62272 && $prod_code == 10421) {
                var_dump($contract);exit;
            }*/

            /*if($gemid == 61760 && $prod_code == 14312) {
                var_dump($contract);exit;
            }*/

            /*if($gemid == 60659 && $prod_code == 10421) {
                var_dump($contract);
            }*/

            /*if($gemid == 60642 && $prod_code == 10421) {
                var_dump($contract);
            }*/

            /*if($gemid == 60628 && $prod_code == 14312) {
                var_dump($contract);
            }*/

            /*if($gemid == 60326 && $prod_code == 10421) {
                var_dump($contract);
            }*/


            if($gemid == 90001) $gemid = 90101;

            if(!array_key_exists($gemid, $data)) {
                $data[$gemid] = [];
            }
            if(!array_key_exists($prod_code, $data[$gemid])) {
                $data[$gemid][$prod_code] = [];
            }

            if($is_sym) {
                if(!array_key_exists($bb_code, $data[$gemid][$prod_code])) {
                    $data[$gemid][$prod_code][$bb_code] = 0;
                }
                $data[$gemid][$prod_code][$bb_code]++;
            } else {
                if(!array_key_exists($bb_code_down, $data[$gemid][$prod_code])) {
                    $data[$gemid][$prod_code][$bb_code_down] = 0;
                }
                if(!array_key_exists($bb_code_up, $data[$gemid][$prod_code])) {
                    $data[$gemid][$prod_code][$bb_code_up] = 0;
                }

                $data[$gemid][$prod_code][$bb_code_down]++;
                $data[$gemid][$prod_code][$bb_code_up]++;
            }

            /*if($gemid == 62382) {
                echo "kirchberg an der raab ($gemid) Contract id ".$contract->id." product_name: '".$contract->product->name."' prod_code: '$prod_code' bb_code: '$bb_code' is_sym: ".($is_sym ? "true" : "false")." - '".$contract->matchcode."'\n<br />";
            }*/
        }
        //exit;

        $csv_header = "gemid;code;bb;anz_akt_anschl";
        $csv = $csv_header."\n";
        foreach($data as $gemid => $gem) {
            foreach($gem as $prod_code => $prod) {
                foreach($prod as $bb_code => $count) {
                    $csv .= $gemid . ";";
                    $csv .= $prod_code . ";";
                    $csv .= $bb_code . ";";
                    $csv .= $count;
                    $csv .= "\n";
                }
            }
        }


        header("Content-type: text/csv; charset=utf-8");
        header('Content-disposition: attachment; filename="rtr-B10-report-'.date('Y-m-d_H-i-s').'.csv"');

        echo $csv;
        exit;

    }

    protected function c10reportAction() {
        $data = [];

        $systemowner = new Address($this->systemowner_id);
        $energie_bb = new Address($this->energie_bb_id);
        if(!$energie_bb->id) {
            return [
                "error" => "Energie BB id nicht gesetzt",
                "template" => "Admin/RtrReporting/Index",
                "redirect" => "",
                "templateVars" => []
            ];
        }

        foreach(ContractModel::searchActive([]) as $contract) {
            if(!is_array($contract->product->attributes) || !array_key_exists("rtr_tech_code", $contract->product->attributes) || !$contract->product->attributes["rtr_tech_code"]->value) continue;

            $prov_id = false;
            $prod_code = $contract->product->attributes["rtr_tech_code"]->value;
            if(!$prod_code) continue;

            if($contract->termination_id) {
                $network_id = $contract->termination->building->network_id;
                $netowner_id = $contract->termination->building->network->owner_id;

                if(in_array($network_id, $this->additional_owner_networks)) {
                    $netowner_id = $this->systemowner_id;
                }

                if(in_array($netowner_id, $this->netowner_is_systemowner)) {
                    $netowner_id = $this->systemowner_id;
                }

                $netowner = new Address($netowner_id);
                if($netowner_id != $this->systemowner_id && ($prod_code == "14311" || $prod_code == "14312")) {
                    continue;
                }

                if($netowner->id && is_array($netowner->attributes) && array_key_exists("rtrcode", $netowner->attributes) && $netowner->attributes["rtrcode"]->value) {
                    $prov_id = $netowner->attributes["rtrcode"]->value;
                }
                if(!$prov_id) {
                    $this->log->debug(__METHOD__.": keine provider id trotz termination | netowner: $netowner_id ".$netowner->getCompanyOrName());
                }
            }

            $prod_code_start = substr($prod_code, 0, 4);
            $prod_code_a = $prod_code_start."0";
            $prod_code_p = $prod_code_start."1";
            $prod_code_b = $prod_code_start."2";

            if($prod_code_start == 2011) {
                $prov_id = 1522; // A1 in case of Vull
            }

            if(!$prov_id || $prov_id == $this->systemowner_rtr_id) {
                if(substr($prod_code_start,0, 3) == "343" || $prod_code_start == "1432") {
                    // is OAN product
                    $prov_id = $energie_bb->attributes["rtrcode"]->value;
                }
            }

            if(!$prov_id) {
                $prov_id = $systemowner->attributes["rtrcode"]->value;
            }

            if(!array_key_exists($prov_id, $data)) {
                $data[$prov_id] = [];
            }

            if($prov_id == $this->systemowner_rtr_id && (($netowner_id == $this->systemowner_id && ($prod_code_start == 1042 || $prod_code_start == 1431)) || $prod_code_start == 1222)) {
                if(!in_array($prod_code_a, $data[$prov_id])) {
                    $data[$prov_id][] = $prod_code_a;
                }
            }

            if(!in_array($prod_code_p, $data[$prov_id])) {
                $data[$prov_id][] = $prod_code_p;
            }
            if(!in_array($prod_code_b, $data[$prov_id]) && $prod_code != 12221) {
                $data[$prov_id][] = $prod_code_b;
            }
        }


        //var_dump($data);exit;

        $csv_header = "code;partnernetz_id;einschr_geo;einschr_dienst";
        $csv = $csv_header."\n";
        foreach($data as $prov_id => $prov) {
            foreach($prov as $prod_code) {
                $csv .= "$prod_code;";
                $csv .= "$prov_id;";
                $csv .= "0;";
                $csv .= substr($prod_code, -1, 1);
                $csv .= "\n";
            }
        }

        header("Content-type: text/csv; charset=utf-8");
        header('Content-disposition: attachment; filename="rtr-C10-report-'.date('Y-m-d_H-i-s').'.csv"');

        echo $csv;
        exit;
    }

    /*
     * 1042% = radio
     * 1431% = fiber
     */
    private function getEigenProductIds() {
        $product_ids = [];

        $sql = "SELECT product_id FROM `ProductAttribute`
                LEFT JOIN ProducttechAttribute ON (ProductAttribute.producttechattribute_id = ProducttechAttribute.id)
                WHERE ProducttechAttribute.name='rtr_tech_code'
                AND (ProductAttribute.value LIKE '1042%' OR ProductAttribute.value LIKE '1431%' OR ProductAttribute.value LIKE '1222%')";

        $res = $this->db->query($sql);
        while($data = $this->db->fetch_object($res)) {
            $product_ids[] = $data->product_id;
        }

        return $product_ids;

    }

    private function getRtrBBCode($bb, $direction = "down") {
        if($direction == "down") {
            if($bb < 1) return 1;
            if($bb >= 1 && $bb < 2) return 2;
            if($bb >= 2 && $bb < 10) return 3;
            if($bb >= 10 && $bb < 30) return 4;
            if($bb >= 30 && $bb < 50) return 5;
            if($bb >= 50 && $bb < 75) return 6;
            if($bb >= 75 && $bb < 100) return 7;
            if($bb >= 100 && $bb < 150) return 8;
            if($bb >= 150 && $bb < 300) return 9;
            if($bb >= 300 && $bb < 1000) return 10;
            if($bb >= 1000) return 11;
        }

        if($direction == "up") {
            if($bb < 0.5) return 51;
            if($bb >= 0.5 && $bb < 1) return 52;
            if($bb >= 1 && $bb < 2) return 53;
            if($bb >= 2 && $bb < 3) return 54;
            if($bb >= 3 && $bb < 5) return 55;
            if($bb >= 5 && $bb < 10) return 56;
            if($bb >= 10 && $bb < 20) return 57;
            if($bb >= 20 && $bb < 30) return 58;
            if($bb >= 30 && $bb < 40) return 59;
            if($bb >= 40 && $bb < 100) return 60;
            if($bb >= 100 && $bb < 150) return 61;
            if($bb >= 150 && $bb < 300) return 62;
            if($bb >= 300 && $bb < 1000) return 63;
            if($bb >= 1000) return 64;
        }

        if($direction == "sym") {
            if($bb < 1) return 21;
            if($bb >= 1 && $bb < 2) return 22;
            if($bb >= 2 && $bb < 10) return 23;
            if($bb >= 10 && $bb < 30) return 24;
            if($bb >= 30 && $bb < 50) return 25;
            if($bb >= 50 && $bb < 75) return 26;
            if($bb >= 75 && $bb < 100) return 27;
            if($bb >= 100 && $bb < 150) return 28;
            if($bb >= 150 && $bb < 300) return 29;
            if($bb >= 300 && $bb < 500) return 30;
            if($bb >= 500 && $bb < 750) return 31;
            if($bb >= 750 && $bb < 1000) return 32;
            if($bb >= 1000) return 33;
        }

        return "x";
    }

}