<?php

namespace App\Http\Controllers;

use App\Http\Common\DBUtil;
use App\Http\Common\Util;
use Illuminate\Http\Request;
use DB;
define('AES_METHOD', 'aes-256-cbc');
use Illuminate\Support\Facades\Cache;
use App\Http\Libraries\Birdeye;
use App\Http\Libraries\Cannaveya;
use App\Http\Classes\Aws\S3Bucket;
use App\Http\Libraries\CovaAPI;
use Illuminate\Support\Facades\Storage;
use Exception;

class DispensaryController extends Controller
{

    // To show grid
    public function show(Request $request)
    {
        $query = '';

        $type = $request->operate;
        $cmp = 'cmp' . $request->cmp;
        $params = $request->all();
        if ($type == 'grid') {
            $cmp = Util::makeSchema($request->cmp);
            //filter resolve filter
            $filterData = Util::filter($request->filter);

            $req = $request->filter;
            $where = Util::makeDataForFilter($req);
            $utype = $request['params'];
            $userid =$request['userid'];
            $isactive =$request['isactive'];

            $where = "";
            if ($req['inputFilter']['column_name'] == 'name' && $req['inputFilter']['keyword'] != '') {
                $where .= " and lower(d.name) LIKE '%" . strtolower($req['inputFilter']['keyword']) . "%' ";
            }
            if ($req['inputFilter']['column_name'] == 'email' && $req['inputFilter']['keyword'] != '') {
                $where .= " and lower(d.email) LIKE '%" . strtolower($req['inputFilter']['keyword']) . "%' ";
            }
            if ($req['inputFilter']['column_name'] == 'phone' && $req['inputFilter']['keyword'] != '') {
                $where .= " and d.phone LIKE '%" . $req['inputFilter']['keyword'] . "%' ";
            }
            if ($req['inputFilter']['column_name'] == 'isvirtual' && $req['inputFilter']['keyword'] != '') {
                $where .= " and d.isvirtual = '" . strtolower($req['inputFilter']['keyword']) . "' ";
            }
            if ($req['inputFilter']['column_name'] == 'isactive' && $req['inputFilter']['keyword'] != '') {
                $where .= " and d.isactive = '" . strtolower($req['inputFilter']['keyword']) . "' ";
            }
            if (!empty($isactive)) {
                $where .= " and d.isactive = " . $isactive . " ";
            } 
            //$where = substr($where, 0, -2);
            // $joins = "";
            // if($request->usertype == '2'){
            //     $joins .="inner join $cmp.usermaster u on d.id IN (select unnest(string_to_array(u.userdispensarymap, ',')) ";
            // }
          
            $sort = '';
            if (isset($req['multiSortMeta']) && $req['multiSortMeta'] != '') {
                foreach ($req['multiSortMeta'] as $val) {
                    $order = ($val['order'] == 1) ? 'asc' : 'desc';
                    $sort .= $val['field'] . " " . $order . ", ";
                }
                $sort = substr($sort, 0, -2);
            } else {
                $sort = "d.name asc";
            }


            $sql = DB::select("select d.id,d.name,d.email,d.phone,s.countryname,c.statename,c.cityname,d.address1,d.address2,d.domain,d.timezone,d.createdon,CASE WHEN d.isvirtual=true then 'Yes' ELSE 'No' end as isvirtual,CASE WHEN d.isactive=true then 'Yes' ELSE 'No' end as isactive from $cmp.dispensary d
            left join sys.view_cities c on d.city=c.cityid
            left join sys.view_states s on d.state=s.stateid 
            where true " . $where . " 
            and case when $utype  in(0,1) then true else 
            (d.id) :: text in(select unnest(string_to_array(userdispensarymap,',')) from $cmp.users where id='". $userid."') end
            
            order by " . $sort . " " . $filterData[3]);

            $totalRows = 0;
            if ($request->getTotalRecordsFlg == 1) {
                $totalRecords = DB::select("select count(1) as total from $cmp.dispensary d
            left join sys.view_cities c on d.city=c.cityid
            left join sys.view_states s on d.state=s.stateid 
            where true " . $where ." and case when $utype  in(0,1) then true else 
            (d.id) :: text in(select unnest(string_to_array(userdispensarymap,',')) from $cmp.users where id='". $userid."') end ")[0];
                $totalRows = $totalRecords->total;
            }
            $query = [$sql, $totalRows];
            $result = ['status' => 1, 'result' => $query, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result);
        } else if ($type == 'newid') {
            $guid = DB::select("select extensions.uuid_generate_v1() as guid")[0];
            $result = ['status' => 1, 'result' => $guid, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result);
        } 
        else if($type=='getdispensaryrow' || $type=='getdisplocation' || $type=='getdispensarylist' || $type=='dispguard') {
            $cache_key=Util::cacheKeyMaker($request, ['vir_dispensary', 't', 'custid','getcount']);
            if(Cache::has($cache_key))
            {
                $result = Util::getCacheUnserialize($cache_key);
            }
            else 
            {
                $sql = DBUtil::callFunction('fn_dispensary', Util::sysParams($request->cmp, $request->operate), json_encode($request->all()));
                Util::DispstoreCache($cache_key,$sql[0], 5);
                $result = $sql[0];
            }
            $result1 = ['status' => 1, 'result' => $result, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result1);
        }
        else if ($type=='loan_delivery_zones' || $type=='getrewards' || $type=='editdispzones' || $type=='gettemplate' || $type=='templateZn' || $type=='edittemplatezone')
        {
            $cache_key=Util::cacheKeyName($request->cmp,$request->dispensary,$request->operate);
            if(Cache::has($cache_key))
            {
                $result = Util::getCacheUnserialize($cache_key);
            }
            else 
            {
                $sql = DBUtil::callFunction('fn_dispensary', Util::sysParams($request->cmp, $request->operate, $request->disid), json_encode($request->all()));
                Util::storeCache($cache_key,$sql[0], 300);
                $result = $sql[0];
            }
            $result1 = ['status' => 1, 'result' => $result, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result1);
        }
        else if ($type=='getdispforcheckoutbyid')
        {
            $cache_key=Util::cacheKeyName($request->cmp,$request->dispid,$request->operate);
            if(Cache::has($cache_key))
            {
                $result = Util::getCacheUnserialize($cache_key);
            }
            else 
            {
                $sql = DBUtil::callFunction('fn_dispensary', Util::sysParams($request->cmp, $request->operate, $request->disid), json_encode($request->all()));
                Util::storeCache($cache_key,$sql[0], 300);
                $result = $sql[0];
            }
            $result1 = ['status' => 1, 'result' => $result, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result1);
        }        
        else if ($type=='getdispensarytimeid')
        {
            $cache_key=Util::cacheKeyName($request->cmp,$request->id,$request->operate);
            if(Cache::has($cache_key))
            {
                $result = Util::getCacheUnserialize($cache_key);
            }
            else 
            {
                $sql = DBUtil::callFunction('fn_dispensary', Util::sysParams($request->cmp, $request->operate, $request->disid), json_encode($request->all()));
                Util::storeCache($cache_key,$sql[0], 300);
                $result = $sql[0];
            }
            $result1 = ['status' => 1, 'result' => $result, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result1);
        }else if($type=='virtualEditDispTime'){
            $sql = DBUtil::callFunction('fn_dispensary', Util::sysParams($request->cmp, $request->operate, $request->disid), json_encode($request->all()));
            //Util::storeCache($cache_key,$sql[0], 300);
            $result = $sql[0];

            $result1 = ['status' => 1, 'result' => $result, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result1);
        }
        else if($type=='getAvailableDispensarylist'){
            $sql = DBUtil::callFunction('fn_dispensary', Util::sysParams($request->cmp, $request->operate, $request->disid), json_encode($request->all()));
            
            $result1 = ['status' => 1, 'result' => $sql[0], 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result1);
        }
        else if($type=='getdispensaryPickTimeSlot') {
            $sql = DB::select("select d.otherdata from $cmp.dispensary d where d.id = ('".$request->dispensary."')::uuid and d.isactive = true");
            $otherdata = $sql[0]->otherdata;
            if ($otherdata) {
                $otherdata_array = json_decode($otherdata, true);
                $pickup_time_slot = $otherdata_array['pickup_time_slot'] ?? false;
            } else {
                $pickup_time_slot = false;
            }
            $result1 = ['status' => 1, 'result' => $pickup_time_slot, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result1);
        }
        else {

            $sql = DBUtil::callFunction('fn_dispensary', Util::sysParams($request->cmp, $request->operate, $request->disid), json_encode($request->all()));

            $result = $sql[0];

            if (!empty($result)) {
                $webhook = '';
                $inventorywebhook = '';

                if ($type == 'edit') {
                    $integration = json_decode($result[0]->integration, true);
                    
                    $envcache = Util::envCredential($request->cmp);
                    if ((isset($envcache->pos_name) && $envcache->pos_name == 'treez') || (!isset($envcache->pos_name))) {
                        if(empty($envcache->webhookprefixurl) || empty($envcache->inventorywebhookurl)){
                            Util::wlog('error_syncInenv', 'webhook or inventory url is missing in disp ');
                            throw new Exception("webhook or inventory url is missing in disp ");
                        }
                        
                        $url = $envcache->webhookprefixurl;
                        //$url = env('WEBHOOK_URL');
                        $cmp = Util::Encrypt("VUHODTX1P6NZEDGBH1XP", $request->cmp);
                        if(isset($result[0]->isvirtual) && $result[0]->isvirtual){
                            $dispensaryid = Util::Encrypt("VUHODTX1P6NZEDGBH1XP", $result[0]->parentid);
                        }
                        else{
                            $dispensaryid = Util::Encrypt("VUHODTX1P6NZEDGBH1XP", $result[0]->id);
                        }
                        
                        $method = 'webhook';
                        @$integration['pos']['webhook'] = $url . "?method=" . $method . "&cmp=" . urlencode($cmp) . "&dispensaryid=" . urlencode($dispensaryid) . "&integration=" . $integration['pos']['name'];
                        $webhook = $url . "?method=" . $method . "&cmp=" . urlencode($cmp) . "&dispensaryid=" . urlencode($dispensaryid) . "&integration=" . $integration['pos']['name'];
                        
                        //$url = env('INVENTORY_WEBHOOK_URL');
                        $url = $envcache->inventorywebhookurl;
                        $cmp = $request->cmp;
                        $dispensaryid = $result[0]->id;
                        $method = 'webhook';
                        $storeid = $request->header('Vfi-Account-Id');
                        @$integration['pos']['invwebhook'] = $url . "/".$dispensaryid."/".$storeid; //$cmp;
                        $inventorywebhook = $url . "/".$dispensaryid."/".$storeid;
                    }

                    $result[0]->integration = json_encode($integration);
                }else if($type == 'getbanner'){
                    $sql = DBUtil::callFunction('fn_dispensary', Util::sysParams($request->cmp, $request->operate, $request->dispensary), json_encode($request->all()));
                    $result = $sql[0];
                }else if($type == 'editZn'){
                    $sql = DBUtil::callFunction('fn_dispensary', Util::sysParams($request->cmp, $request->operate, $request->dispensary), json_encode($request->all()));
                    $result = $sql[0];
                }

                if(isset($result[0]->isvirtual) && $result[0]->isvirtual){
                    $dispensary_id = $result[0]->parentid;
                }
                else{
                    $dispensary_id = $result[0]->id;
                }

                $dispensary_metadata = DB::table("cmp$request->cmp.dispensary_metadata")->where('dispensaryid', $dispensary_id)->get();

                $is_treez_disp = DB::table("cmp$request->cmp.dispensary_metadata")->where(['dispensaryid' => $dispensary_id, 'module' => 'pos_name', 'value' => 'treez'])->first();

                if($is_treez_disp){

                    $webhook_exist = $dispensary_metadata->where('module', 'webhook')->first();

                    if ($webhook_exist) {
                        $webhook_exist->value = $webhook;
                    } else {
                        $dispensary_metadata[] = (object) ['module' => 'webhook', 'value' => $webhook];
                    }

                    $invwebhook_exist = $dispensary_metadata->where('module', 'invwebhook')->first();

                    if ($invwebhook_exist) {
                        $invwebhook_exist->value = $inventorywebhook;
                    } else {
                        $dispensary_metadata[] = (object) ['module' => 'invwebhook', 'value' => $inventorywebhook];
                    }
                }

                $result[0]->metadata = $dispensary_metadata;

                $result1 = ['status' => 1, 'result' => $result, 'errorcode' => null, 'msg' => null];

                return Util::responseop(response(), $result1);
            } else {
                $result = ['status' => 0, 'result' => null, 'errorcode' => '001', 'msg' => 'record_not_found'];
                return Util::responseop(response(), $result);
            }
        }
    }

    public function post(Request $request)
    {
        $params = Util::EscapeApostrophe($request->all());
        \Log::info('dispensary params: ' . json_encode($params));
        \Log::info('dispensary sysParams: ' . json_encode(Util::sysParams($request->cmp, 'crud')));
        $sql = DBUtil::callProcedure('sp_dispensary', Util::sysParams($request->cmp, 'crud'), $params);
        \Log::info('dispensary post: ' . json_encode($sql));
        if (isset($request->updateInte) && $request->updateInte == true) {
            DBUtil::delRedisData($request->id);
        }
        $cache_label = 'cmp'.$request->cmp.'_'.$request->id.'_get_actual_dipid_for_order';
        Cache::forget($cache_label);
        Cache::flush();
        return Util::response_sp(response(), $sql);
    }

    public function delete(Request $request)
    {
        $params = json_encode($request->all());
        $sql = DBUtil::callProcedure('sp_dispensary', Util::sysParams($request->cmp, 'delete'), $params);
        Cache::flush();
        return Util::response_sp(response(), $sql);
    }

    public function active(Request $request)
    {
        $params = json_encode($request->all());
        $sql = DBUtil::callProcedure('sp_dispensary', Util::sysParams($request->cmp, 'active'), $params);
        Cache::flush();
        return Util::response_sp(response(), $sql);
    }


    private function encrypt($message, $password)
    {
        if (OPENSSL_VERSION_NUMBER <= 268443727) {
            throw new RuntimeException('OpenSSL Version too old, vulnerability to Heartbleed');
        }

        $iv_size        = openssl_cipher_iv_length(AES_METHOD);
        $iv             = openssl_random_pseudo_bytes($iv_size);
        $ciphertext     = openssl_encrypt($message, AES_METHOD, $password, OPENSSL_RAW_DATA, $iv);
        $ciphertext_hex = bin2hex($ciphertext);
        $iv_hex         = bin2hex($iv);
        return "$iv_hex:$ciphertext_hex";
    }

    public function virtualdispensary(Request $request)
    {
        $params = Util::EscapeApostrophe($request->all());
        $sql = DBUtil::callProcedure('sp_virtualdispensary', Util::sysParams($request->cmp, 'crud'), $params);
        if (isset($request->updateInte) && $request->updateInte == true) {
            DBUtil::delRedisData($request->id);
        }
        $cache_label = 'cmp'.$request->cmp.'_'.$request->id.'_get_actual_dipid_for_order';
        Cache::forget($cache_label);
        Cache::flush();
        return Util::response_sp(response(), $sql);
    }

    public function timedispensary(Request $request)
    {
        $params = Util::EscapeApostrophe($request->all());
        $sql = DBUtil::callProcedure('sp_virtualdispensary', Util::sysParams($request->cmp, 'timedispensary'), $params);
        return Util::response_sp(response(), $sql);
    }

    public function schedule(Request $request)
    {
        $params = Util::EscapeApostrophe($request->all());
        $sql = DBUtil::callProcedure('sp_dispensary', Util::sysParams($request->cmp, $request->operate), $params);
        return Util::response_sp(response(), $sql);
    }

    public function savezones(Request $request)
    {
        $params = Util::EscapeApostrophe($request->all());
        $sql = DBUtil::callProcedure('sp_dispensary', Util::sysParams($request->cmp, $request->operate), $params);
        return Util::response_sp(response(), $sql);
    }

    public function deliveryzones(Request $request)
    {   
        try{
            $params = Util::EscapeApostrophe($request->all());
            $sql = DBUtil::callProcedure('sp_dispensary', Util::sysParams($request->cmp, $request->operate), $params);
            return Util::response_sp(response(), $sql);
        } catch(Exception $e) {
            Util::wlog('delivery_zones_error', $e, __LINE__, true);
        }
        
    }

    public function getrating(Request $request)
    {   
        $result = Birdeye::getRating();
        $total_review = $result->reviewCount;
        $totrate = 0; 
        $arr = array();
        foreach($result->countByRating as $rating){
            $totrate += $rating->rating * $rating->count;
        }
        $total_rating = $totrate;
        $avg_rate = round($totrate / $total_review,2);

        $arr['total_review'] = $total_review;
        $arr['total_rating'] = $total_rating;
        $arr['avg_rate'] = $avg_rate;
        $arr['dispensary'] = $result->dispensaryid;
        
        $params = json_encode($arr); 
        $sql = DBUtil::callProcedure('sp_dispensary_configs', Util::sysParams('2', 'rating'), $params);
        return Util::response_sp(response(), $sql);
    }

    public function savesettings(Request $request)
    {
        $params = Util::EscapeApostrophe($request->all());
        $sql = DBUtil::callProcedure('sp_dispensary_configs', Util::sysParams($request->cmp, $request->operate), $params);
        // clear product cache
        $cache_label = 'cmp'.$request->cmp.'_'.$request->dispensary.'_get_actual_dipid_for_order';
        Cache::forget($cache_label);
        Cache::tags(["product_".$request->cmp])->flush();
        return Util::response_sp(response(), $sql);
    }

    public function getsettings(Request $request)
    {
        $result = array();
        $sql = DBUtil::callFunction('fn_dispensary', Util::sysParams($request->cmp, $request->operate), json_encode($request->all()));
        if(count($sql) > 0){
            $result = $sql[0];
        }
        $result1 = ['status' => 1, 'result' => $result, 'errorcode' => null, 'msg' => null];
        return Util::responseop(response(), $result1);
    }

    public function findExpressDelivery(Request $request){
        try
        {
            $sql = DBUtil::callFunction('fn_expressdelivery', Util::sysParams($request->cmp,'get_filter_dispensaries'), json_encode(['latitude'=>$request->lat,'longitude'=>$request->lng,'available_disp'=>$request->available_disp]));
            if(isset($sql[0]) && count($sql[0]) > 0) {
                $drivers_data=[];
                foreach($sql[0] as $a) {
                    $canna_host      = $a->cannveya_host;
                    $canna_user      = $a->cannveya_username;
                    $canna_api_key   = $a->cannveya_api_key;
                    $canna_password  = $a->cannveya_password;
                    $canna_proximity = isset($a->cannveya_proximity)?$a->cannveya_proximity:0;

                    $validate_zones  = Cannaveya::validate_zones([
                        'CANNAVEYA_URL'         => $canna_host,
                        'CANNAVEYA_API_KEY'     => $canna_api_key,
                        'CANNAVEYA_USERNAME'    => $canna_user,
                        'CANNAVEYA_PASSWORD'    => $canna_password,
                        'latitude'              => $request->lat,
                        'longitude'             => $request->lng,
                        'CANNAVEYA_PROXIMITY'   => $canna_proximity,
                    ]);

                    if(isset($validate_zones['status']) && $validate_zones['status']) {
                        $calldrivers=Cannaveya::get_nearby_drivers([
                            'CANNAVEYA_URL'         => $canna_host,
                            'CANNAVEYA_API_KEY'     => $canna_api_key,
                            'CANNAVEYA_USERNAME'    => $canna_user,
                            'CANNAVEYA_PASSWORD'    => $canna_password,
                            'latitude'              => $request->lat,
                            'longitude'             => $request->lng,
                            'CANNAVEYA_PROXIMITY'   => $canna_proximity,
                        ]);
    
                        foreach ($calldrivers['data'] as $b) {
                            $b = array_merge($b, (array) $a);
                            $drivers_data[] = $b;
                        }
                    }
                }

                /**
                 * Driver is active (API: �on_duty� : 1)
                 * User is a Driver (API: TD)
                 */
                $filterdata=[];
                foreach($drivers_data as $element){
                    @$treez_location = $element['assigned_to_vehicle']['custom_attributes']['treez_location'];
                    if($element['on_duty']==1 && $element['role_name']=='driver' && !empty($treez_location)){
                        array_push($filterdata,[
                            'distance_in_miles'  => $element['location']['distance_in_miles'],
                            'locationdata'       => ['location' => $treez_location, 'ordertype' => 'express', 'name' => $element['ordertype_label'], 'min_order_value' => $element['min_order_value']],
                            'id'                 => $element['id'],
                            'address'            => $element['address'],
                            'name'               => $element['name'],
                            'banner'             => $element['banner'],
                            'fromtime'           => $element['fromtime'],
                            'totime'             => $element['totime'],
                            'status'             => $element['status'],
                            'posname'            => $element['posname'],
                            'otherdata'          => $element['otherdata'],
                            'country'            => $element['country'],
                            'licenceno'          => $element['licenceno'],
                            'phone'              => $element['phone'],
                            'mobilebanner'       => $element['mobilebanner'],
                            'slug'               => $element['slug'],
                            'isvirtual'          => $element['isvirtual'],
                            'parentid'           => $element['parentid'],
                            'stronghold_pub_key' => $element['stronghold_pub_key'],
                            'express_text'       => $element['express_text']

                        ]);
                    }
                }

                // get near by driver from distance_in_miles
                usort($filterdata, function($a, $b) {
                    return $a['distance_in_miles'] <=> $b['distance_in_miles'];
                }); 
                if(count($filterdata) == 0) {
                    throw new Exception('Express delivery is not available');   
                }
                else {
                    $result = ['status' => 1, 'result' =>$filterdata[0], 'errorcode' => null, 'msg' => null]; 
                }
            }
            else {
                throw new Exception('Express delivery is not available');
            }
        }
        catch(Exception $e){
            $result = ['status' => 0 , 'result' => null, 'errorcode' => null, 'msg' => $e->getMessage()]; 
        }
        return Util::responseop(response(), $result);
    }

    public function validateExpressDelivery(Request $request){
        try
        {
            $sql = DBUtil::callFunction('fn_expressdelivery', Util::sysParams($request->cmp,'validate_dispensary'), json_encode(['latitude'=>$request->latitude,'longitude'=>$request->longitude, 'dispid' => $request->dispid, 'invlocation' => $request->invlocation]));
            if(isset($sql[0]) && count($sql[0]) > 0) {
                $drivers_data=[];
                foreach($sql[0] as $a) {
                    $canna_host      = $a->cannveya_host;
                    $canna_user      = $a->cannveya_username;
                    $canna_api_key   = $a->cannveya_api_key;
                    $canna_password  = $a->cannveya_password;
                    $canna_proximity = isset($a->cannveya_proximity)?$a->cannveya_proximity:0;
                    

                    $validate_zones  = Cannaveya::validate_zones([
                        'CANNAVEYA_URL'         => $canna_host,
                        'CANNAVEYA_API_KEY'     => $canna_api_key,
                        'CANNAVEYA_USERNAME'    => $canna_user,
                        'CANNAVEYA_PASSWORD'    => $canna_password,
                        'latitude'              => $request->latitude,
                        'longitude'             => $request->longitude,
                        'CANNAVEYA_PROXIMITY'   => $canna_proximity,
                    ]);

                    if(isset($validate_zones['status']) && $validate_zones['status']) {
                        $calldrivers=Cannaveya::get_nearby_drivers([
                            'CANNAVEYA_URL'         => $canna_host,
                            'CANNAVEYA_API_KEY'     => $canna_api_key,
                            'CANNAVEYA_USERNAME'    => $canna_user,
                            'CANNAVEYA_PASSWORD'    => $canna_password,
                            'latitude'              => $request->latitude,
                            'longitude'             => $request->longitude,
                            'CANNAVEYA_PROXIMITY'   => $canna_proximity,
                        ]);

                        if($calldrivers['status']) {
                            foreach ($calldrivers['data'] as $b) {
                                $b = array_merge($b, (array) $a);
                                $drivers_data[] = $b;
                            }
                        }
                    }
                }
                
                /**
                 * Driver is active (API: �on_duty� : 1)
                 * User is a Driver (API: TD)
                 */
                $drvdata=[];
                foreach($drivers_data as $element){
                    @$treez_location = $element['assigned_to_vehicle']['custom_attributes']['treez_location'];
                    if($element['on_duty']==1 && $element['role_name'] === 'driver' && $treez_location === $request->invlocation){
                        $drvdata[] = [
                                        'distance_in_miles'  => $element['location']['distance_in_miles'],
                                        'dispid'             => $element['id'],
                                        'treez_location'     => $treez_location,
                                        'min_order_value'    => $element['min_order_value'],
                                        'state'              => $element['state'],
                                        'city'               => $element['city'],
                                        'country'            => $element['country'],
                                        'state_code'         => $element['state_code'],
                                        'city_name'          => $element['city_name'],
                                        'country_name'       => $element['country_name']
                                     ];
                    }
                }
                
                usort($drvdata, function($a, $b) {
                    return $a['distance_in_miles'] <=> $b['distance_in_miles'];
                }); 
                if(count($drvdata) == 0) {
                    throw new Exception('Express delivery is not available');   
                }
                else {
                    $result = ['status' => 1, 'result' => 
                                                            [
                                                                'min_order_value'=> $drvdata[0]['min_order_value'],
                                                                'invlocation' => $drvdata[0]['treez_location'],
                                                                'state' => $drvdata[0]['state'],
                                                                'city' => $drvdata[0]['city'],
                                                                'country' => $drvdata[0]['country'],
                                                                'state_code'   => $drvdata[0]['state_code'],
                                                                'city_name'    => $drvdata[0]['city_name'],
                                                                'country_name' => $drvdata[0]['country_name']
                                                            ], 'errorcode' => null, 'msg' => null]; 
                }
            }
            else {
                throw new Exception('Express delivery is not available for this address');
            }
        }
        catch(Exception $e){
            $result = ['status' => 0 , 'result' => null, 'errorcode' => null, 'msg' => $e->getMessage()]; 
        }
        return Util::responseop(response(), $result);
    }

    public function findHomeDelivery(Request $request){
        try
        {
            $sql = DBUtil::callFunction('fn_homedelivery', Util::sysParams($request->cmp,'get_filter_dispensaries'), json_encode(['latitude'=>$request->lat,'longitude'=>$request->lng,'available_disp'=>$request->available_disp]));
            if(isset($sql[0]) && count($sql[0]) === 0) {
                throw new Exception('Home delivery is not available');
            }
            $nearby_dispensary = [];
            foreach($sql[0] as $a) {
                $canna_host      = $a->cannveya_host;
                $canna_user      = $a->cannveya_username;
                $canna_api_key   = $a->cannveya_api_key;
                $canna_password  = $a->cannveya_password;
                $canna_proximity = isset($a->cannveya_proximity)?$a->cannveya_proximity:0;
                $cannveya_enabled = $a->cannveya_enabled;
                if($cannveya_enabled == true) {
                    $validate_zones   = Cannaveya::validate_zones([
                        'CANNAVEYA_URL'         => $canna_host,
                        'CANNAVEYA_API_KEY'     => $canna_api_key,
                        'CANNAVEYA_USERNAME'    => $canna_user,
                        'CANNAVEYA_PASSWORD'    => $canna_password,
                        'latitude'              => $request->lat,
                        'longitude'             => $request->lng,
                        'CANNAVEYA_PROXIMITY'   => $canna_proximity,
                    ]);
                    if(isset($validate_zones['status']) && $validate_zones['status']) {
                        $nearby_dispensary = (array) $a;
                        break;
                    }
                    break;
                }
                else {
                    //near by zone..
                    $nearby_dispensary = (array) $a;
                    break;
                }
            }
            
            if(empty($nearby_dispensary)) {
                throw new Exception('Near by delivery is not available');
            }
            $locationdata = json_decode($nearby_dispensary['locationdata'], true);
            $ordertype = array_values(array_filter($locationdata, function($element) {
                return $element['ordertype'] == 'del';
            }));
            // remove credentials
            foreach(['cannveya_host','cannveya_username','cannveya_api_key','cannveya_password','cannveya_proximity','locationdata'] as $b) {
                if(isset($nearby_dispensary[$b])) {
                    unset($nearby_dispensary[$b]);
                }
            }
            if(count($ordertype) === 0) {
                throw new Exception('Dispensary delivery ordertype is not available');
            }
            $nearby_dispensary['locationdata'] = ['location' => $ordertype[0]['location'], 'ordertype' => 'del', 'name' => $nearby_dispensary['ordertype_label'], 'min_order_value' => $nearby_dispensary['min_order_value']];
            $result = ['status' => 1, 'result' => $nearby_dispensary, 'errorcode' => null, 'msg' => null];    
        }
        catch(Exception $e){
            $result = ['status' => 0 , 'result' => null, 'errorcode' => null, 'msg' => $e->getMessage()]; 
        }
        return Util::responseop(response(), $result);
    }

    // public function validateHomeDelivery(Request $request){
    //     try
    //     {
    //         $sql = DBUtil::callFunction('fn_homedelivery', Util::sysParams($request->cmp,'validate_dispensary'), json_encode(['latitude'=>$request->latitude,'longitude'=>$request->longitude, 'dispid' => $request->dispensary]));
    //         if(isset($sql[0]) && count($sql[0]) === 0) {
    //             throw new Exception('Home delivery is not available for this address');
    //         }
    //         $nearby_dispensary = false;
    //         foreach($sql[0] as $a) {
    //             $canna_host       = $a->cannveya_host;
    //             $canna_user       = $a->cannveya_username;
    //             $canna_api_key    = $a->cannveya_api_key;
    //             $canna_password   = $a->cannveya_password;
    //             $canna_proximity  = isset($a->cannveya_proximity)?$a->cannveya_proximity:0;
    //             $cannveya_enabled = $a->cannveya_enabled;
    //             $min_order_value  = $a->min_order_value;
    //             $zone_id          = $a->zone_id;
    //             if($cannveya_enabled == true) {
    //                 $validate_zones   = Cannaveya::validate_zones([
    //                     'CANNAVEYA_URL'         => $canna_host,
    //                     'CANNAVEYA_API_KEY'     => $canna_api_key,
    //                     'CANNAVEYA_USERNAME'    => $canna_user,
    //                     'CANNAVEYA_PASSWORD'    => $canna_password,
    //                     'latitude'              => $request->latitude,
    //                     'longitude'             => $request->longitude,
    //                     'CANNAVEYA_PROXIMITY'   => $canna_proximity,
    //                 ]);
    //                 if(isset($validate_zones['status']) && $validate_zones['status']) {
    //                     $nearby_dispensary = true;
    //                     break;
    //                 }
    //             }
    //             else {
    //                 $nearby_dispensary = true;
    //                 break; 
    //             }
    //         }
            
    //         if($nearby_dispensary === false) {
    //             throw new Exception('Home delivery is not available for this address');
    //         }
    //         $result = ['status' => 1, 'result' => ['min_order_value'=>$min_order_value,'zone_id' => $zone_id], 'errorcode' => null, 'msg' => 'Address is valid for Home delivery.'];   
    //     }
    //     catch(Exception $e){
    //         $result = ['status' => 0 , 'result' => null, 'errorcode' => null, 'msg' => $e->getMessage()]; 
    //     }
    //     return Util::responseop(response(), $result);
    // }

    public function validateHomeDelivery(Request $request){
        try
        {
            $sql = DBUtil::callFunction('fn_homedelivery', Util::sysParams($request->cmp,'validate_dispensary'), json_encode(['latitude'=>$request->latitude,'longitude'=>$request->longitude, 'dispid' => $request->dispid]));
            if(isset($sql[0]) && count($sql[0]) === 0) {
                throw new Exception('Home delivery is not available for this address');
            }
            $nearby_dispensary  = false;
            $zones              = [];
            foreach($sql[0] as $a) {
                $canna_host       = $a->cannveya_host;
                $canna_user       = $a->cannveya_username;
                $canna_api_key    = $a->cannveya_api_key;
                $canna_password   = $a->cannveya_password;
                $canna_proximity  = isset($a->cannveya_proximity)?$a->cannveya_proximity:0;
                $cannveya_enabled = $a->cannveya_enabled;
                $min_order_value  = $a->min_order_value;
                $zone_id          = $a->zone_id;
                array_push($zones, [
                    'min_order_value' => $min_order_value,
                    'zone_id'         => $zone_id
                ]);
                if($cannveya_enabled == true) {
                    $validate_zones   = Cannaveya::validate_zones([
                        'CANNAVEYA_URL'         => $canna_host,
                        'CANNAVEYA_API_KEY'     => $canna_api_key,
                        'CANNAVEYA_USERNAME'    => $canna_user,
                        'CANNAVEYA_PASSWORD'    => $canna_password,
                        'latitude'              => $request->latitude,
                        'longitude'             => $request->longitude,
                        'CANNAVEYA_PROXIMITY'   => $canna_proximity,
                    ]);
                    if(isset($validate_zones['status']) && $validate_zones['status']) {
                        $nearby_dispensary = true;
                    }
                }
                else {
                    $nearby_dispensary = true;
                }
            }
            
            if($nearby_dispensary === false) {
                throw new Exception('Home delivery is not available for this address');
            }
            $result = ['status' => 1, 'result' => $zones, 'errorcode' => null, 'msg' => 'Address is valid for Home delivery.'];   
        }
        catch(Exception $e){
            $result = ['status' => 0 , 'result' => null, 'errorcode' => null, 'msg' => $e->getMessage()]; 
        }
        return Util::responseop(response(), $result);
    }
    
    public function updateCSPWStatus(Request $request, $cmp, $status, $key)
    {
        $params = [
            'cmp' => $cmp,
            'status' => $status,
            'key' => $key
        ];
        $sql = DBUtil::callProcedure('sp_dispensary', Util::sysParams($cmp, 'updateCSPWStatus'), json_encode($params));
        return Util::response_sp(response(), $sql);
    }

    public function banner(Request $request)
    {
        $params = Util::EscapeApostrophe($request->all());
        $sql = DBUtil::callProcedure('sp_dispensary', Util::sysParams($request->cmp, 'banner'), $params);
        return Util::response_sp(response(), $sql);
    }

    public function saveZone(Request $request)
    {
        $params = Util::EscapeApostrophe($request->all());
        $sql = DBUtil::callProcedure('sp_dispensary', Util::sysParams($request->cmp, 'saveZone'), $params);
        
        return Util::response_sp(response(), $sql);
    }

    public function deliveryshow(Request $request)
    {
        $query = '';
        $type = $request->operate;
        $cmp = 'cmp' . $request->cmp;
        $params = $request->all();
        if ($type == 'grid') {
            $cmp = Util::makeSchema($request->cmp);
            //filter resolve filter
            $filterData = Util::filter($request->filter);

            $req = $request->filter;
            $where = Util::makeDataForFilter($req);
            $utype = $request['params'];
            $userid =$request['userid'];

            $where = "";
            if ($req['inputFilter']['column_name'] == 'title' && $req['inputFilter']['keyword'] != '') {
                $where .= " and lower(dz.title) LIKE '%" . strtolower($req['inputFilter']['keyword']) . "%', ";
            }
            if ($req['inputFilter']['column_name'] == 'min_order_value' && $req['inputFilter']['keyword'] != '') {
                $where .= " and dz.min_order_value = '" . $req['inputFilter']['keyword'] . "', ";
            }
            if ($req['inputFilter']['column_name'] == 'delivery_type' && $req['inputFilter']['keyword'] != '') {    
                $deltype = '0';
                if(strtolower($req['inputFilter']['keyword']) == strtolower("ALL")){
                    $deltype = '1';
                }else if(strtolower($req['inputFilter']['keyword']) == strtolower("Express Only")){
                    $deltype = '2';
                }else if(strtolower($req['inputFilter']['keyword']) == strtolower("Delivery Only")){
                    $deltype = '3';
                }
                $where .= " and dz.for_order_type = '".$deltype."' , ";
            }
            if ($req['inputFilter']['column_name'] == 'no_of_days' && $req['inputFilter']['keyword'] != '') {
                $where .= " and dz.no_of_days = '" . $req['inputFilter']['keyword'] . "', ";
            }
            if ($req['inputFilter']['column_name'] == 'zone_type' && $req['inputFilter']['keyword'] != '') {
                if(strtolower($req['inputFilter']['keyword']) == strtolower("Main")){
                    $where .= " and coalesce(dz.parent_id,0) = 0, ";
                }else{
                    $where .= " and coalesce(dz.parent_id,0) != 0, ";
                }
            }
            
            $where = substr($where, 0, -2);
          
            $sort = '';
            if (isset($req['multiSortMeta']) && $req['multiSortMeta'] != '') {
                foreach ($req['multiSortMeta'] as $val) {
                    $order = ($val['order'] == 1) ? 'asc' : 'desc';
                    $sort .= $val['field'] . " " . $order . ", ";
                }
                $sort = substr($sort, 0, -2);
            } else {
                $sort = "dz.name asc";
            }

            $sql = DB::select("select dz.id,dz.title,dz.min_order_value,dz.bgcolor,coalesce(extensions.ST_AsText(dz.polygon), ''),
            case when dz.for_order_type ='1' then 'ALL' when dz.for_order_type ='2' then 'Express Only' when dz.for_order_type ='3' then 'Delivery Only' else '' end as delivery_type,
            dz.for_order_type,
            dz.no_of_days,
            case when coalesce(dz.parent_id,0) = 0 then 'Main' else 'Sub' end as zone_type, 
            d.name,dz.parent_id,dz.is_asap,dz.asap_text,dz.delivery_end_time,asap_endtime,asap_starttime,dza.ids,d.id as dispid
                from $cmp.delivery_zones dz
                inner join $cmp.dispensary d on d.id=dz.dispensaryid and dz.isactive=true
                left join (select json_agg(dz2.id) as ids, dz2.parent_id, dz2.dispensaryid from $cmp.delivery_zones dz2 where dz2.isactive=true group by dz2.parent_id, dz2.dispensaryid) dza on dza.parent_id=dz.id and dza.dispensaryid=dz.dispensaryid
                where dz.dispensaryid = ('".$request->dispensary."')::uuid " . $where . " order by " . $sort . " " . $filterData[3]);

            $totalRows = 0;
            if ($request->getTotalRecordsFlg == 1) {
                $totalRecords = DB::select("select count(1) as total 
                from $cmp.delivery_zones dz
                inner join $cmp.dispensary d on d.id=dz.dispensaryid and dz.isactive=true
                left join (select json_agg(dz2.id) as ids, dz2.parent_id, dz2.dispensaryid from $cmp.delivery_zones dz2 where dz2.isactive=true group by dz2.parent_id, dz2.dispensaryid) dza on dza.parent_id=dz.id and dza.dispensaryid=dz.dispensaryid
                where dz.dispensaryid = ('".$request->dispensary."')::uuid " . $where)[0];
                $totalRows = $totalRecords->total;
            }
            $query = [$sql, $totalRows];
            $result = ['status' => 1, 'result' => $query, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result);
        }
    }

    public function deliverytemplate(Request $request)
    {
        $query = '';
        $type = $request->operate;
        $cmp = 'cmp' . $request->cmp;
        $params = $request->all();
        if ($type == 'grid') {
            $cmp = Util::makeSchema($request->cmp);
            //filter resolve filter
            $filterData = Util::filter($request->filter);

            $req = $request->filter;
            $where = Util::makeDataForFilter($req);
            $utype = $request['params'];
            $userid =$request['userid'];

            $where = "";
            if ($req['inputFilter']['column_name'] == 'title' && $req['inputFilter']['keyword'] != '') {
                $where .= " and lower(dz.title) LIKE '%" . strtolower($req['inputFilter']['keyword']) . "%', ";
            }
            if ($req['inputFilter']['column_name'] == 'min_order_value' && $req['inputFilter']['keyword'] != '') {
                $where .= " and dz.min_order_value = '" . $req['inputFilter']['keyword'] . "', ";
            }
            if ($req['inputFilter']['column_name'] == 'delivery_type' && $req['inputFilter']['keyword'] != '') {    
                $deltype = '0';
                if(strtolower($req['inputFilter']['keyword']) == strtolower("ALL")){
                    $deltype = '1';
                }else if(strtolower($req['inputFilter']['keyword']) == strtolower("Express Only")){
                    $deltype = '2';
                }else if(strtolower($req['inputFilter']['keyword']) == strtolower("Delivery Only")){
                    $deltype = '3';
                }
                $where .= " and dz.for_order_type = '".$deltype."' , ";
            }
            if ($req['inputFilter']['column_name'] == 'no_of_days' && $req['inputFilter']['keyword'] != '') {
                $where .= " and dz.no_of_days = '" . $req['inputFilter']['keyword'] . "', ";
            }
            if ($req['inputFilter']['column_name'] == 'zone_type' && $req['inputFilter']['keyword'] != '') {
                if(strtolower($req['inputFilter']['keyword']) == strtolower("Main")){
                    $where .= " and coalesce(dz.parent_id,0) = 0, ";
                }else{
                    $where .= " and coalesce(dz.parent_id,0) != 0, ";
                }
            }
            
            $where = substr($where, 0, -2);
          
            $sort = '';
            if (isset($req['multiSortMeta']) && $req['multiSortMeta'] != '') {
                foreach ($req['multiSortMeta'] as $val) {
                    $order = ($val['order'] == 1) ? 'asc' : 'desc';
                    $sort .= $val['field'] . " " . $order . ", ";
                }
                $sort = substr($sort, 0, -2);
            } else {
                $sort = "dz.name asc";
            }

            $sql = DB::select("select dz.id,dz.title,dz.min_order_value,dz.bgcolor,coalesce(extensions.ST_AsText(dz.polygon), ''),
            case when dz.for_order_type ='1' then 'ALL' when dz.for_order_type ='2' then 'Express Only' when dz.for_order_type ='3' then 'Delivery Only' else '' end as delivery_type,
            dz.for_order_type,
            dz.no_of_days,
            case when coalesce(dz.parent_id,0) = 0 then 'Main' else 'Sub' end as zone_type, 
            null as name,dz.parent_id,dz.is_asap,dz.asap_text,dz.delivery_end_time,asap_endtime,asap_starttime,null as ids,null as dispid
                from $cmp.delivery_zones_template dz
                where dz.isactive=true " . $where . " order by " . $sort . " " . $filterData[3]);

            $totalRows = 0;
            if ($request->getTotalRecordsFlg == 1) {
                $totalRecords = DB::select("select count(1) as total 
                from $cmp.delivery_zones_template dz
                left join (select json_agg(dz2.id) as ids, dz2.parent_id, dz2.dispensaryid from $cmp.delivery_zones dz2 group by dz2.parent_id, dz2.dispensaryid) dza on dza.parent_id=dz.id and dza.dispensaryid=dz.dispensaryid
                where dz.isactive=true " . $where)[0];
                $totalRows = $totalRecords->total;
            }
            $query = [$sql, $totalRows];
            $result = ['status' => 1, 'result' => $query, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result);
        }
    }

    public function getZones(Request $request) {
        $sql = DBUtil::callFunction('fn_dispensary', Util::sysParams($request->cmp,'getzones', $request->disid), json_encode($request->all()));
        $result = $sql[0];
        $result1 = ['status' => 1, 'result' => $result, 'errorcode' => null, 'msg' => null];
        return Util::responseop(response(), $result1);
    }

    public function deleteaddr(Request $request)
    {
        $params = Util::EscapeApostrophe($request->all());
        $sql = DBUtil::callProcedure('sp_dispensary', Util::sysParams($request->cmp, 'deleteaddr'), $params);
        
        return Util::response_sp(response(), $sql);
    }

    public function getCovaLocations(Request $request) {
        $request['dispensary'] = $request->disp_id; //to prevent the use of wrong dispensary id
        $getLocationsResponse = CovaAPI::getLocations();

        if ($getLocationsResponse && isset($getLocationsResponse['status']) && !$getLocationsResponse['status']) {
            $result = ['status' => 0, 'result' => null, 'errorcode' => $getLocationsResponse['error'], 'msg' => $getLocationsResponse['message']];
            return Util::responseop(response(), $result);
        }
        
        if ($getLocationsResponse) {
            $result = ['status' => 1, 'result' => $getLocationsResponse, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result);
        }
        
    }

    public function getCovaRoomsForLocations(Request $request) {
        $request['dispensary'] = $request->disp_id; //to prevent the use of wrong dispensary id
        $getRoomsForLocationsResponse = CovaAPI::getRoomsForLocations();
        
        if ($getRoomsForLocationsResponse && isset($getRoomsForLocationsResponse['status']) && !$getRoomsForLocationsResponse['status']) {
            $result = ['status' => 0, 'result' => null, 'errorcode' => $getRoomsForLocationsResponse['error'], 'msg' => $getRoomsForLocationsResponse['message']];
            return Util::responseop(response(), $result);
        }
        
        if ($getRoomsForLocationsResponse) {
            $result = ['status' => 1, 'result' => $getRoomsForLocationsResponse, 'errorcode' => null, 'msg' => null];
            return Util::responseop(response(), $result);
        }
        
    }

}