<?php

namespace App\Services;

use App\FranchisePostCodes;
use App\Invoices;
use App\Members;

class NL implements \App\Services\Country {

    public function validatePostalCode($postcode) {
        /*
         * * This function takes in a string, cleans it up, and returns an array with the following information:
         * * ["validate"] => TRUE/FALSE
         * * ["prefix"] => first part of postcode
         * * ["suffix"] => second part of postcode
         */
        $postcode = str_replace(' ', '', $postcode); // remove any spaces;
        $postcode = strtoupper($postcode); // force to uppercase;
        $valid_postcode_exp = "/^[1-9][0-9]{3} ?(?!sa|sd|ss)[a-z]{2}$/i";
        // set default output results (assuming invalid postcode):
        $output = array();
        $output['validate'] = false;
        $output['prefix'] = '';
        $output['suffix'] = '';
        if (preg_match($valid_postcode_exp, strtoupper($postcode))) {
            $output['validate'] = true;
            $postcode = preg_replace('/\s+/', '', $postcode);
            $suffix = substr($postcode, -2);
            $prefix = str_replace($suffix, '', $postcode);


            $model = FranchisePostCodes::where("Code", $prefix)->first();

            if (isset($model->FKFranchiseID)) {
                $output['validate'] = true;
                $output['prefix'] = preg_replace('/\s+/', '', $prefix);
                $output['suffix'] = preg_replace('/\s+/', '', $suffix);
                $output['franchise_id'] = $model->FKFranchiseID;
            }
        }
        return $output;
    }

    public function getLatitudeLongitude($postcode) {
        $url = "https://api.everythinglocation.com/address/complete";
        //?geocode
        $q = '{ "lqtkey":"WD89-YZ91-JG68-PJ44", "query":"' . $postcode . '", "country":"nl", "filters": { "Locality": "Amsterdam", "AdministrativeArea": "Noord-Holland" } }';
        $response = $this->tookan_api_post_request($url, $q);
        $response = json_decode($response);
        //print_r($response->output);
        //die;
        if ($response->Status == "OK") {
            if (empty($response->output)) {
                return "";
            } else {
                return $response->output;
            }
        }
        return "";
    }

    public function getAreaOrders($from, $to) {

        //$from = "2019-10-01 00:00:00";
        //$to = "2019-10-31 23:59:59";

        $model = Invoices::whereBetween("ws_invoices.CreatedDateTime", [$from, $to])
                ->groupBy("PostalCode")
                ->where('OrderStatus', "!=", 'Cancel')
                ->leftJoin('ws_franchises as f', 'f.PKFranchiseID', '=', 'ws_invoices.FKFranchiseID')
                ->select(\DB::raw('count(PKINvoiceID) as orders'), \DB::raw('sum(GrandTotal) as total'), "PostalCode", "f.Title")
                ->get();

        $codes = array();
        $total = 0;
        $totalOrders = 0;
        foreach ($model as $row) {
            $postcode = preg_replace('/\s+/', '', $row->PostalCode);
            $suffix = substr($postcode, -3);
            $prefix = str_replace($suffix, '', $postcode);
            $prefix = trim($prefix);
            $prefix = strtoupper($prefix);

            if (key_exists($prefix, $codes)) {
                $codes[$prefix]["orders"] = $codes[$prefix]["orders"] + $row->orders;
                $codes[$prefix]["total"] = $codes[$prefix]["total"] + $row->total;
            } else {
                $codes[$prefix]["orders"] = $row->orders;
                $codes[$prefix]["total"] = $row->total;
            }

            $codes[$prefix]["franchise"] = $row->Title;
            $totalOrders += $row->orders;
            $total += $row->total;
        }
        $data['data'] = $codes;
        $data['view'] = "uk";
        $data['total'] = $total;
        $data['orders'] = $totalOrders;
        $data['text'] = "District";
        return $data;
    }

    public function getAreaMembers($from, $to) {

        $model = Members::whereBetween("CreatedDateTime", [$from, $to])
                ->groupBy("PostalCode")
                ->select(\DB::raw('count(PKMemberID) as members'), "PostalCode")
                ->get();
        $codes = array();
        $total = 0;
        foreach ($model as $row) {
            $postcode = preg_replace('/\s+/', '', $row->PostalCode);
            $suffix = substr($postcode, -3);
            $prefix = str_replace($suffix, '', $postcode);
            $prefix = trim($prefix);
            $prefix = strtoupper($prefix);

            if (key_exists($prefix, $codes)) {
                $codes[$prefix]["members"] = $codes[$prefix]["members"] + $row->members;
            } else {
                $codes[$prefix]["members"] = $row->members;
            }
            $total += $row->members;
        }

        $data['data'] = $codes;
        $data['view'] = "uk";
        $data['text'] = "District";
        $data['total'] = $total;
        return $data;
    }

    public function getAreasList() {
        $model = FranchisePostCodes::pluck("Code", "FKFranchiseId");

        $list = array();
        foreach ($model as $key => $val) {
            $val = trim($val);
            $list[$val] = $val;
        }
        
        $data["title"]="District";
        $data["list"]=$list;

        return $data;
    }
    
    public function ordersByLocations($from, $to, $locations) {

        $model = Invoices::whereBetween("ws_invoices.CreatedDateTime", [$from, $to]);
        $model = $model->where('OrderStatus', "!=", 'Cancel');


        if(!empty($locations)) {
            $model = $model->where(function ($query) use ($locations) {

                foreach($locations as $location) {
                    $query->orWhere('PostalCode', "Like", $location.'%');
                }
            });
        }
        return $model->get();
    }

}
