<?php

namespace App\Services;

use App\FranchisePostCodes;
use App\Invoices;
use App\Members;
use DB;
use Carbon\Carbon;

class UK 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 = "/^(([A-PR-UW-Z]{1}[A-IK-Y]?)([0-9]?[A-HJKS-UW]?[ABEHMNPRVWXY]?|[0-9]?[0-9]?))\s?([0-9]{1}[ABD-HJLNP-UW-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))) {

            $postcode = preg_replace('/\s+/', '', $postcode);
            $suffix = substr($postcode, -3);
            $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;
            }
        }
        //d($output,1);
        return $output;
    }

    public function getLatitudeLongitude($postcode) {
        $url = "https://maps.googleapis.com/maps/api/geocode/json?address=" . urlencode($postcode) . "&key=" . env("GOOGLE_MAP_API_KEY") . "&sensor=false";
        $result_string = file_get_contents($url);
        $result = json_decode($result_string, true);
        $suffix = substr($postcode, -3);
        //var_dump($result);die();
        if (strtolower($result['status']) == "ok") {
            $result1[] = $result['results'][0];
            $result2[] = $result1[0]['geometry'];
            $result3[] = $result2[0]['location'];
            return $result3[0]['lat'] . ',' . $result3[0]['lng'];
        } elseif ($suffix) {
            $url = "https://maps.googleapis.com/maps/api/geocode/json?address=" . urlencode($suffix) . "&key=" . env("GOOGLE_MAP_API_KEY") . "&sensor=false";
            $result_string = file_get_contents($url);
            $result = json_decode($result_string, true);
            if (strtolower($result['status']) == "ok") {
                $result1[] = $result['results'][0];
                $result2[] = $result1[0]['geometry'];
                $result3[] = $result2[0]['location'];
                return $result3[0]['lat'] . ',' . $result3[0]['lng'];
            } else {
                return "";
            }
        } else {
            return "";
        }
    }

    public function getAreaOrders($from, $to) {

        $from = $from . "";
        $to = $to . "";
        $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::select("Code")->get();
        $list = array();
        foreach ($model as $val) {
            $val = trim($val->Code);
            $list[$val] = $val;
        }
        $data["title"] = "District";
        $data["list"] = $list;

        return $data;
    }

    public function ordersByLocations($from, $to, $locations) {

        $from = $from . " 00:00:00";
        $to = $to . " 23:59:59";
        //SELECT SUBSTRING(PostalCode,1,length(PostalCode)-3) as p,PostalCode,FKFranchiseID FROM `ws_invoices` where 1

        $subString = "SUBSTRING(PostalCode,1,length(PostalCode)-3)";

        $model = Invoices::whereBetween("ws_invoices.CreatedDateTime", [$from, $to]);
        $model = $model->select(\DB::raw('count(PKInvoiceId) as orders'), "FKFranchiseId", "ws_invoices.PostalCode", \DB::raw("$subString as district"), \DB::raw('date(ws_invoices.CreatedDateTime) as date'));
        $model = $model->where('ws_invoices.OrderStatus', "!=", 'Cancel');
        //$model = $model->leftJoin("ws_franchises as f", "f.PKFranchiseId", "ws_invoices.FKFranchiseId");
        //$model = $model->leftJoin("ws_franchise_services as fs", "fs.FKFranchiseId", "f.PKFranchiseId");
        $charts = array();
        if (!empty($locations)) {
            $model = $model->whereIn(\DB::raw($subString), $locations);
        } else {
            $model = $model->limit(5);
        }

        $model = $model->groupBy(\DB::raw('Date(ws_invoices.CreatedDateTime)'));
        $model = $model->groupBy(\DB::raw($subString));
        $model = $model->get();
        $i = 0;

        foreach ($model as $row) {
            $district = str_replace(" ", "", strtolower($row->district));
            $charts[$district]["dates"][strtotime($row->date)] = $row->orders;

            $i++;
        }

        $date = Carbon::parse($from);
        $now = Carbon::parse($to);
        $diff = $date->diffInDays($now);
        $categories = array();
        for ($i = 0; $i <= $diff; $i++) {
            $d = strtotime($from) + ( 86400 * $i );

            foreach ($charts as $key => $chart) {

                if (!isset($chart["dates"][$d])) {
                    $charts[$key]["dates"][$d] = 0;
                }
                ksort($charts[$key]["dates"]);
            }
            $categories[] = date("j M, y", $d);
        }
        $data = array();
        $data["series"] = array();
        $data["categories"] = array();
        $series = array();
        $i = 0;
        foreach ($charts as $key => $chart) {
            $series[$i]["name"] = $key;
            $series[$i]["data"] = array_values($chart["dates"]);
            $i++;
        }
        $data["categories"] = json_encode($categories);
        $data["series"] = json_encode($series);
        return $data;
    }

    public function productsAnalyticsView($range, $franchises) {

        $data = $range;
        $data["franchises"] = $franchises;

        $listOfValues = $franchises;
        $listOfValues[""] = "All";
        $franchises = $listOfValues->reverse();


        return view('/uk/products_analytics_view', $data);
    }

    public function getProductsAnatytics($input) {

        $from = $input["from"];
        $to = $input["to"];
        $model = Invoices::whereBetween("ws_invoices.CreatedDateTime", [$from, $to]);
        $model = $model->select("is.PKInvoiceServiceID as id", "is.Title as title", \DB::raw('count(PKInvoiceId) as orders'), \DB::raw('Date(ws_invoices.CreatedDateTime) as date'), \DB::raw('sum(GrandTotal) as total'), "FKServiceID");
        $model = $model->leftJoin("ws_invoice_services as is", "ws_invoices.PKInvoiceId", "is.FKInvoiceId");

        $model = $model->where('ws_invoices.OrderStatus', "!=", 'Cancel');
        $model = $model->where('is.FKServiceID', "!=", 0);

        if (!empty($input["franchise"])) {
            $model = $model->where('ws_invoices.FKFranchiseId', $input["franchise"]);
        }
        $model = $model->groupBy("is.FKServiceID");
        $model = $model->get();
        return $model;
    }

    public function nonCustomersView($range, $franchises) {
        $data = $range;
        $data["franchises"] = $franchises;
        return view('/uk/non_customers_view', $data);
    }

    public function nonCustomersAction($input) {
        
    }

}
