<?php
namespace App\Controllers;
use Config\Services;

class Plugs extends BaseController{
    private $dbHandle;

    public function getbrands(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];

        $plugbrands = $this->dbHandle->query('select probrand from `3P_plugdetails` group by probrand');
        $A = $plugbrands->getResultArray();
        if(sizeof($A)>0){
            $dataToReturn['status'] = 0;
            $dataToReturn['load'] = $A;
        }else{
            $dataToReturn['status'] = 1;
        }

        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }

    public function addprice(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];

        $now = time();
         
        $data = [
            'probrand' => $this->request->getVar('probrand'),
            'afpartno' => $this->request->getVar('partno'),
            'price' => $this->request->getVar('price'),
            'note' => $this->request->getVar('note'),
            'personnel' => $this->request->getVar('personnel'),
            'effectivedate' => date('Y-m-d H:i:s', $now),
            'transactdate' => date('Y-m-d H:i:s', $now)
        ];
        
        $updateoption =  $this->request->getVar('updateoption');
        switch($updateoption){
            case "now":
            $this->dbHandle->query("update 3P_plugmaster set price='{$data['price']}' where probrand='{$data['probrand']}' and afpartno='{$data['afpartno']}'");
            $this->dbHandle->table('3P_priceshistory')->insert($data);
            $E = $this->dbHandle->error();
            $dataToReturn['status'] = $E['code'];
    
            return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
            break;
            
            case "tomorrow":
            $efdate = date('Y-m-d 00:00:00', strtotime('tomorrow'));
            $qry = "CREATE EVENT `gpriceupdate_{$data['probrand']}_{$data['afpartno']}` ON SCHEDULE AT '{$efdate}' DO BEGIN update `3P_plugmaster` set price='{$data['price']}' where probrand='{$data['probrand']}' and afpartno='{$data['afpartno']}'; insert into 3P_priceshistory (probrand, afpartno, price, note, personnel, effectivedate, transactdate) values ('{$data['probrand']}', '{$data['afpartno']}', '{$data['price']}', '{$data['note']}', '{$data['personnel']}', '{$efdate}', '{$data['transactdate']}'); END";
            
            if($this->dbHandle->simpleQuery($qry)){
                $dataToReturn['status'] = 0;
            }else{
                $dataToReturn['status'] = 1;
            }
            return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
            break;
            
            case "date":
            $date = substr($this->request->getVar('updatedate'), 0, 10);
            $efdate = date('Y-m-d 00:00:00', strtotime($date));
            
            $qry = "CREATE EVENT `gpriceupdate_{$data['probrand']}_{$data['afpartno']}` ON SCHEDULE AT '{$efdate}' DO BEGIN update `3P_plugmaster` set price='{$data['price']}' where probrand='{$data['probrand']}' and afpartno='{$data['afpartno']}'; insert into 3P_priceshistory (probrand, afpartno, price, note, personnel, effectivedate, transactdate) values ('{$data['probrand']}', '{$data['afpartno']}', '{$data['price']}', '{$data['note']}', '{$data['personnel']}', '{$efdate}', '{$data['transactdate']}'); END";
            
            if($this->dbHandle->simpleQuery($qry)){
                $dataToReturn['status'] = 0;
            }else{
                $dataToReturn['status'] = 1;
            }
            return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
            break;
        }
    }
    
    public function addspecificprice(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $now = time();

        $method = $this->request->getVar('method');
        
        if($method == 'vin'){
            $vfam = trim($this->request->getVar('vfam'));
        
            $vin = trim($this->request->getVar('vin'));
            if($vin!=''){
                $decoded = $this->decodevin($vin);
                $vfam = $decoded['load'][0]['vfam'];
            }
        
            $data = [
                'vfam' => $vfam,
                'specificprice' => $this->request->getVar('price'),
                'note' => $this->request->getVar('note'),
                'personnel' => $this->request->getVar('personnel'),
                'effectivedate' => date('Y-m-d H:i:s', $now),
                'transactdate' => date('Y-m-d H:i:s', $now)
            ];
            
            $updateoption =  $this->request->getVar('updateoption');
            switch($updateoption){
                case "now":
                $this->dbHandle->query("update 3P_plugmaster set specificprice='{$data['specificprice']}' where vfam='{$data['vfam']}'");
                $this->dbHandle->table('3P_priceshistory')->insert($data);
                $E = $this->dbHandle->error();
                $dataToReturn['status'] = $E['code'];
        
                return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
                break;
                
                case "tomorrow":
                $efdate = date('Y-m-d 00:00:00', strtotime('tomorrow'));
                $qry = "CREATE EVENT `spriceupdate_{$data['vfam']}` ON SCHEDULE AT '{$efdate}' DO BEGIN update `3P_plugmaster` set specificprice='{$data['specificprice']}' where vfam='{$data['vfam']}'; insert into 3P_priceshistory (vfam, specificprice, note, personnel, effectivedate, transactdate) values ('{$data['vfam']}', '{$data['specificprice']}', '{$data['note']}', '{$data['personnel']}', '{$efdate}', '{$data['transactdate']}'); END";
                
                if($this->dbHandle->simpleQuery($qry)){
                    $dataToReturn['status'] = 0;
                }else{
                    $dataToReturn['status'] = 1;
                }
                return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
                break;
                
                case "date":
                $date = substr($this->request->getVar('updatedate'), 0, 10);
                $efdate = date('Y-m-d 00:00:00', strtotime($date));
                
                $qry = "CREATE EVENT `spriceupdate_{$data['vfam']}` ON SCHEDULE AT '{$efdate}' DO BEGIN update `3P_plugmaster` set specificprice='{$data['specificprice']}' where vfam='{$data['vfam']}'; insert into 3P_priceshistory (vfam, specificprice, note, personnel, effectivedate, transactdate) values ('{$data['vfam']}', '{$data['specificprice']}', '{$data['note']}', '{$data['personnel']}', '{$efdate}', '{$data['transactdate']}'); END";
                
                if($this->dbHandle->simpleQuery($qry)){
                    $dataToReturn['status'] = 0;
                }else{
                    $dataToReturn['status'] = 1;
                }
                return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
                break;
            }
        } else {
            $data = [
                'probrand' => $this->request->getVar('probrand'),
                'afpartno' => $this->request->getVar('partno'),
                'specificprice' => $this->request->getVar('price'),
                'note' => $this->request->getVar('note'),
                'personnel' => $this->request->getVar('personnel'),
                'effectivedate' => date('Y-m-d H:i:s', $now),
                'transactdate' => date('Y-m-d H:i:s', $now)
            ];
        
            $updateoption =  $this->request->getVar('updateoption');
            switch($updateoption){
                case "now":
                $this->dbHandle->query("update 3P_plugmaster set specificprice='{$data['specificprice']}' where probrand='{$data['probrand']}' and afpartno='{$data['afpartno']}'");
                $this->dbHandle->table('3P_priceshistory')->insert($data);
                $E = $this->dbHandle->error();
                $dataToReturn['status'] = $E['code'];
        
                return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
                break;
                
                case "tomorrow":
                $efdate = date('Y-m-d 00:00:00', strtotime('tomorrow'));
                $qry = "CREATE EVENT `spriceupdate_{$data['probrand']}_{$data['afpartno']}` ON SCHEDULE AT '{$efdate}' DO BEGIN update `3P_plugmaster` set specificprice='{$data['specificprice']}' where probrand='{$data['probrand']}' and afpartno='{$data['afpartno']}'; insert into 3P_priceshistory (probrand, afpartno, specificprice, note, personnel, effectivedate, transactdate) values ('{$data['probrand']}', '{$data['afpartno']}', '{$data['specificprice']}', '{$data['note']}', '{$data['personnel']}', '{$efdate}', '{$data['transactdate']}'); END";
                
                if($this->dbHandle->simpleQuery($qry)){
                    $dataToReturn['status'] = 0;
                }else{
                    $dataToReturn['status'] = 1;
                }
                return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
                break;
                
                case "date":
                $date = substr($this->request->getVar('updatedate'), 0, 10);
                $efdate = date('Y-m-d 00:00:00', strtotime($date));
                
                $qry = "CREATE EVENT `spriceupdate_{$data['probrand']}_{$data['afpartno']}` ON SCHEDULE AT '{$efdate}' DO BEGIN update `3P_plugmaster` set specificprice='{$data['specificprice']}' where probrand='{$data['probrand']}' and afpartno='{$data['afpartno']}'; insert into 3P_priceshistory (probrand, afpartno, specificprice, note, personnel, effectivedate, transactdate) values ('{$data['probrand']}', '{$data['afpartno']}', '{$data['specificprice']}', '{$data['note']}', '{$data['personnel']}', '{$efdate}', '{$data['transactdate']}'); END";
                
                if($this->dbHandle->simpleQuery($qry)){
                    $dataToReturn['status'] = 0;
                }else{
                    $dataToReturn['status'] = 1;
                }
                return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
                break;
            }
        }
    }
    
    public function getspecificprice(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $vfam = trim($this->request->getVar('vfam'));
        
        $vin = trim($this->request->getVar('vin'));
        if($vin!=''){
            $decoded = $this->decodevin($vin);
            $vfam = $decoded['load'][0]['vfam'];
        }
        
        $A = $this->dbHandle->query("select `specificprice` from `3P_plugmaster` where vfam='{$vfam}'")->getResultArray();
        if(sizeof($A)>0){
            $dataToReturn['status'] = 0;
            $dataToReturn['load'] = $A;
        }else{
            $dataToReturn['status'] = 1;
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    private function nextMidnight($nw){
        $nw = $nw + 86400;
        return date('Y-m-d 00:00:00', $nw);
    }
    
    public function getmatchedvehiclesbypartno(){
    	$error = 1 ;
        $this->dbx = \Config\Database::connect();
        
        $probrand = $this->request->getVar('probrand');
        $partno = $this->request->getVar('partno');
        
        $dataToReturn = [];
        $dataToReturn['status'] = 1;
        $dataToReturn['load'] = [];
        
        $R_vfams = $this->dbx->query("select vfam, price, specificprice from 3P_plugmaster where probrand='{$probrand}' and afpartno='{$partno}' group by vfam, price, specificprice order by `vfam` ASC");
        $dataToReturn['status'] = $this->dbx->error()['code'];
        
        $this->dbx->query("CREATE TEMPORARY TABLE matchedvehiclesbypartno (vfam varchar(32), make varchar(32), model varchar(32), genfr varchar(32), gento varchar(32), engine varchar(32), price varchar(32), specificprice varchar(32))");
        $this->dbx->query("TRUNCATE TABLE matchedvehiclesbypartno");

        foreach($R_vfams->getResultArray() as $R_vfam){
            $this->dbx->query("insert into matchedvehiclesbypartno select '{$R_vfam['vfam']}', make, model, genfr, gento, engdesc, '{$R_vfam['price']}', '{$R_vfam['specificprice']}' from 3V_supplementary where vfam='{$R_vfam['vfam']}'");
        }
        
        $R_vehicles = $this->dbx->query("select * from matchedvehiclesbypartno order by make ASC, model ASC, genfr ASC, gento ASC");
        $dataToReturn['load'] = $R_vehicles->getResultArray();
        
		return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    private function decodevin($v){
        $this->dbHandle = \Config\Database::connect();
        $dataToReturn = [];
        $dataToReturn['load'] = [];
        $modules = [];

        $vin = str_replace(['O','I','-'], ['0','1',''],strtoupper($v));
        $vl = strlen($vin);

        $dataToReturn['dvin'] = $vin;

        if( $vl == 17){

            //build the 4 types of the vin family
            $m10h = substr($vin,0,8)."*".substr($vin,9,1);
            $m10p = substr($vin,0,10);
            $m9p = substr($vin,0,9);
            $m8p = substr($vin,0,8);

            //find the first matching vfam
            $q10h = "(vfamlen=10 and vfam='".$m10h."')";
            $q10 = "(vfamlen=10 and vfam='".$m10p."')";
            $q9 = "(vfamlen=9 and vfam='".$m9p."')";
            $q8 = "(vfamlen=8 and vfam='".$m8p."')";
            
            //get the first matched vfam
            $R0 = $this->dbHandle->query("select vfam from `3V_supplementary` where {$q10h} or {$q10} or {$q9} or {$q8} order by vfam desc limit 1");
            if( $R0->getNumRows()>0 ){
                $matchfam = $R0->getResultArray()[0]['vfam'];
                
                $R = $this->dbHandle->query("select vfam, vfamlen, modulesub, module, make, makecode, model, modelcode, vinyear as 'year', engine, englayout, fueltypeprimary, displacementcc, enginecylinders, engdesc, genfr, gento, yearpos, bodytype, seats, doors 
                from `3V_supplementary` where vfam='{$matchfam}' group by `modulesub`, `module`, `vfam`, `vfamlen`, `make`, `makecode`, `model`, `modelcode`, `vinyear`, `engine`, `englayout`, `fueltypeprimary`, `displacementcc`, `enginecylinders`, `engdesc`, `genfr`, `gento`, `yearpos`, `bodytype`, `seats`, `doors` 
                order by vfamlen DESC, module ASC");
                
            }else{
                
                //ERROR - VIN NOT FOUND
                $dataToReturn['status'] = 3;
                $dataToReturn['extra'] = "na_vin";
                $dataToReturn['load'] = ["vin"=>$vin];
            }

        } else if( $vl >= 7 && $vl <= 14){

            //build the types of the vin family
            $m7p = substr($vin,0,7);
            $m6p = substr($vin,0,6);
            $m5p = substr($vin,0,5);
            $m4p = substr($vin,0,4);
            $m3p = substr($vin,0,3);

            //find the first matching vfam
            $q7 = "(vfamlen=7 and (vfam='".$m7p."'))";
            $q6 = "(vfamlen=6 and (vfam='".$m6p."'))";
            $q5 = "(vfamlen=5 and (vfam='".$m5p."'))";
            $q4 = "(vfamlen=4 and (vfam='".$m4p."'))";
            $q3 = "(vfamlen=3 and (vfam='".$m3p."'))";
            
            //get the first matched vfam
            $R0 = $this->dbHandle->query("select vfam from `3V_supplementary` where {$q7} or {$q6} or {$q5} or {$q4} or {$q3} order by vfam desc limit 1");
            if( $R0->getNumRows()>0 ){
                $matchfam = $R0->getResultArray()[0]['vfam'];
                
                $R = $this->dbHandle->query("select vfam, vfamlen, modulesub, module, make, model, vinyear as 'year', engine, englayout, fueltypeprimary, displacementcc, enginecylinders, engdesc, genfr, gento, yearpos, bodytype, seats, doors 
                from `3V_supplementary` where vfam='{$matchfam}' group by `modulesub`, `module`, `vfam`, `vfamlen`, `make`, `model`, `vinyear`, `engine`, `englayout`, `fueltypeprimary`, `displacementcc`, `enginecylinders`, `engdesc`, `genfr`, `gento`, `yearpos`, `bodytype`, `seats`, `doors` 
                order by vfamlen DESC, module ASC");
                
            }else{
                
                //ERROR - VIN NOT FOUND
                $dataToReturn['status'] = 3;
                $dataToReturn['extra'] = "na_vin";
                $dataToReturn['load'] = ["vin"=>$vin];
            }

        } else {

            //ERROR
            $dataToReturn['status'] = 1;
            $dataToReturn['extra'] = "Incorrect VIN Length";
        }
            
        if( $R->getNumRows()>0 ){
            $dataToReturn['status'] = 0;

            foreach($R->getResultArray() as $W){
                $modules[] = $W['module'];
                $module = $W['module'];
                $vfam = $W['vfam'];
                $yearpos = $W['yearpos'];
                $genfr = $W['genfr'];
                $gento = $W['gento'];
                $modulesub = $W['modulesub'];
                $W['years'] = [];
                
                $W['photo'] = '';
                //$W['photo'] = $this->getPhoto(strtolower($W['modulesub']), $W['vfam']);
                
                switch(strtolower($W['module'])){
                    case "mo":
                        $yearfound = false;
                        $yearsym = substr($vin, $yearpos-1, 1);
                        $R2 = $this->dbHandle->query("select `year` from `3V_moyear` where `yearsym` = '{$yearsym}' and (`year` >= '{$genfr}' and `year` <= '{$gento}')");
                        
                        if( $R2->getNumRows()>0 ){
                            $yearfound = true;
                            $W2 = $R2->getResultArray()[0];
                            $W['years'][] = ["year" => $W2['year']];
                        }
                        
                        if(!$yearfound){
                            //use the applicable range
                            for($i = $genfr; $i <= $gento; $i++){
                                $W['years'][] = ["year" => $i];
                            }
                        }
                        break;
                    
                    case "ma":
                        $yearfound = false;
                        $series = str_replace($vfam,"",$vin);
                        $series = str_replace("-","",$series);

                        if( substr($modulesub, 0, 3) === "shc") $dataToReturn['dvin'] = $vfam .'-'. $series;
                        
                        $R2 = $this->dbHandle->query("select `year` from `3V_mayt` where (`serialfr` <= '{$series}' and `serialto` >= '{$series}') and `vfam` = '{$vfam}' and `modulesub` = '{$modulesub}' and `year` > 0");
                        if( $R2->getNumRows()>0 ){
                            $yearfound = true;
                            $W2 = $R2->getResultArray()[0];
                            $W['years'][] = ["year" => $W2['year']];
                        }
                        
                        if(!$yearfound){
                            //get the applicable range
                            $R2 = $this->dbHandle->query("select `genfr`, `gento` from `3V_mayt` where `vfam` = '{$vfam}' and `genfr` > 0");
                            if( $R2->getNumRows()>0 ){
                                $yearfound = true;
                                $W2 = $R2->getResultArray()[0];
                                for($i = $W2['genfr']; $i <= $W2['gento']; $i++){
                                    $W['years'][] = ["year" => $i];
                                }
                            }
                        }
                        break;
                }
                
                $dataToReturn['load'][] = $W;
            }

            $dataToReturn['modules'] = $modules;
            
        }else{
            
            //ERROR - VIN NOT FOUND
            $dataToReturn['status'] = 3;
            $dataToReturn['extra'] = "na_vin";
            $dataToReturn['load'] = ["vin"=>$vin];
        }

        return $dataToReturn;
    }
    
    /*** QUICK FIX ***/
    public function qfgetvehicledetails(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $vfam = trim($this->request->getVar('vfam'));
        $vin = trim($this->request->getVar('vin'));
        if($vin!=''){
            $decoded = $this->decodevin($vin);
            $vfam = $decoded['load'][0]['vfam'];
        }
        
        $R = $this->dbHandle->query("select vfam,vfamref,make,model,bodytype,engdesc,genfr,gento,engineCylinders from `3V_supplementary` where `vfam`='{$vfam}' group by vfamref,vfam,make,model,bodytype,engdesc,genfr,gento,engineCylinders");
        $A = $R->getResultArray();
        if(sizeof($A) > 0){  
            $dataToReturn['status'] = 0;
            $dataToReturn['load'] = $A;
        }else{
            $dataToReturn['status'] = 1;
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function qfgetplugdetails(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $vfam = trim($this->request->getVar('vfam'));
        $vin = trim($this->request->getVar('vin'));
        $probrand = $this->request->getVar('probrand');
        $afpartno = $this->request->getVar('partno');
        
        if($vin!=''){
            $decoded = $this->decodevin($vin);
            $vfam = $decoded['load'][0]['vfam'];
        }
        
        $R = $this->dbHandle->query("select vfam,vfamref,make,model,bodytype,engdesc,genfr,gento,engineCylinders from `3V_supplementary` where `vfam`='{$vfam}' group by vfamref,vfam,make,model,bodytype,engdesc,genfr,gento,engineCylinders");
        $veh = $R->getResultArray();
        
        $R = $this->dbHandle->query("select * from `3P_plugdetails` where `probrand`='{$probrand}' and `afpartno`='{$afpartno}' limit 1");
        $A = $R->getResultArray();
        if(sizeof($A) > 0){  
            $dataToReturn['status'] = 0;
            $dataToReturn['load'] = $A[0];
            $dataToReturn['extra'] = $veh;
        }else{
            $dataToReturn['status'] = 1;
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function qfsavevehicledetailstoplugs(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = []; $errorsum = 0;
        
        $vfam = $this->request->getVar('vfam');
        $vfamrefs = $this->toCSLofValues(explode(',', $this->request->getVar('vfamrefs')));
        $vehs = $this->dbHandle->query("select * from `3V_supplementary` where `vfam`='{$vfam}' and vfamref in (".$vfamrefs.")");
        foreach($vehs->getResultArray() as $veh){
            $data = [
                'vin' => $veh['vin'],
                'vfam' => $veh['vfam'],
                'vfamref' => $veh['vfamref'],
                'vehiclemake' => $veh['make'],
                'model' => $veh['model'],
                'proqty' => $veh['engineCylinders']
            ];
            $this->dbHandle->query("delete from `3P_plugmaster` where `vfam`='{$data['vfam']}' and vfamref='{$veh['vfamref']}'");
        
                
            $this->dbHandle->table('3P_plugmaster')->insert($data);
            $errorsum = $errorsum + $this->dbHandle->error()['code'];
        }
        
        if($errorsum == 0){  
            $dataToReturn['status'] = 0;
        }else{
            $dataToReturn['status'] = 1;
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function qfsaveplugdetailstoplugs(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = []; $errorsum = 0;
        
        $vfam = trim($this->request->getVar('vfam'));
        $vfamrefs = explode(',', $this->request->getVar('vfamrefs'));
        $probrand = $this->request->getVar('probrand');
        $afpartno = $this->request->getVar('partno');
        
        $plug = $this->dbHandle->query("select * from `3P_plugdetails` where `probrand`='{$probrand}' and `afpartno`='{$afpartno}' limit 1")->getResultArray()[0];
        
        foreach($vfamrefs as $ref){
            $this->dbHandle->query("update `3P_plugmaster` set
                probrand = '{$plug['probrand']}',
                afpartno = '{$plug['afpartno']}',
                supplierid = '{$plug['supplier']}',
                plugtype = '{$plug['plugtype']}',
                material = '{$plug['material']}',
                oemno = '',
                SpannerSize = '{$plug['SpannerSize']}',
                Diameter_mm = '{$plug['Diameter_mm']}',
                ThreadLength_mm = '{$plug['ThreadLength_mm']}',
                threadsize = '{$plug['threadsize']}',
                localref = '{$plug['localref']}' where `vfam`='{$vfam}' and `vfamref`='{$ref}'");
            
            $errorsum = $errorsum + $this->dbHandle->error()['code'];
        }
        if($errorsum == 0){  
            $dataToReturn['status'] = 0;
        }else{
            $dataToReturn['status'] = 1;
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function qfsaveoemdetailtoplugs(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = []; $errorsum = 0;
        
        $vfam = trim($this->request->getVar('vfam'));
        $vfamrefs = explode(',', $this->request->getVar('vfamrefs'));
        $oemno = $this->request->getVar('oemno');
        
        foreach($vfamrefs as $ref){
            $this->dbHandle->query("update `3P_plugmaster` set oemno = '{$oemno}' where `vfam`='{$vfam}' and `vfamref`='{$ref}'");
            
            $errorsum = $errorsum + $this->dbHandle->error()['code'];
        }
        if($errorsum == 0){  
            $dataToReturn['status'] = 0;
        }else{
            $dataToReturn['status'] = 1;
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function qfsavepluginputstoplugs(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = []; $errorsum = 0;
        
        $vfam = trim($this->request->getVar('vfam'));
        $vfamrefs = explode(',', $this->request->getVar('vfamrefs'));
        $mileage = $this->request->getVar('mileage');
        $price = $this->request->getVar('price');
        $specificprice = $this->request->getVar('specificprice');
        $stock = $this->request->getVar('stock');
        
        foreach($vfamrefs as $ref){
            $this->dbHandle->query("update `3P_plugmaster` set mileage = '{$mileage}', price = '{$price}', specificprice = '{$specificprice}', stock = '{$stock}' where `vfam`='{$vfam}' and `vfamref`='{$ref}'");
            
            $errorsum = $errorsum + $this->dbHandle->error()['code'];
        }
        if($errorsum == 0){  
            $dataToReturn['status'] = 0;
        }else{
            $dataToReturn['status'] = 1;
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    private function toCSLofValues($arr){
        $ret = "";
    	foreach($arr as $a){ $ret .= ",'".$a."'"; }
    	$ret = substr($ret,1);
    	return $ret;
    }
    
    
    // private function dashesReplaced($s){
    //     return str_replace('-', '|', $s);
    // }
    
    // //get the general price
        // $R = $this->dbHandle->query("select `price` from `prices` where `plugbrand` = '{$data['plugbrand']}' and 
        //     `partno` = '{$data['partno']}'");
        // $A = $R->getResultArray();
        // if(sizeof($A) > 0){
        //     $data['price'] = $A[0]['price'];
        // }
        
        // //check for existence....'effectivedate' => $this->nextMidnight($now),
        // if( sizeof( $this->dbHandle->query("select * from `plugdescriptions` where 
        //     `plugbrand` = '{$data['plugbrand']}' and 
        //     `partno` = '{$data['partno']}'
        //   ")->getResultArray()) > 0){//exists
        //     $dataToReturn['status'] = 1;
        // }else{
        
    /*** QUICK FIX ***/
    /*public function qfgetvehicledetails_old(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $vfam = trim($this->request->getVar('vfam'));
        $vin = trim($this->request->getVar('vin'));
        $gens = (int) trim($this->request->getVar('gens'));
        if($vin!=''){
            $decoded = $this->decodevin($vin);
            $vfam = $decoded['load'][0]['vfam'];
        }
        
        $R = $this->dbHandle->query("select * from `3V_supplementary` where `vfam`='{$vfam}' limit 1");
        $A = $R->getResultArray(); $A[0]['gens'] = range(1, $gens);
        if(sizeof($A) > 0){  
            $dataToReturn['status'] = 0;
            $dataToReturn['load'] = $A[0];
        }else{
            $dataToReturn['status'] = 1;
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function qfsavevehicledetailstoplugs(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = []; $errorsum = 0;
        
        $vfam = $this->request->getVar('vfam');
        $genfroms = explode(',', $this->request->getVar('genfroms'));
        $gentos = explode(',', $this->request->getVar('gentos'));
        
        $veh = $this->dbHandle->query("select * from `3V_supplementary` where `vfam`='{$vfam}' limit 1")->getResultArray()[0];
        
        //do updates
        $data = [
            'vin' => $veh['vin'],
            'vfam' => $veh['vfam'],
            'vehiclemake' => $veh['make'],
            'model' => $veh['model'],
            'proqty' => 4
        ];
        $this->dbHandle->query("delete from `3P_plugmaster` where `vfam`='{$data['vfam']}'");
        for($i=0; $i<count($genfroms); $i++){
            $data['partgenfr'] = $genfroms[$i];
            $data['partgento'] = $gentos[$i];
            
            $this->dbHandle->table('3P_plugmaster')->insert($data);
            $errorsum = $errorsum + $this->dbHandle->error()['code'];
        }
        if($errorsum == 0){  
            $dataToReturn['status'] = 0;
        }else{
            $dataToReturn['status'] = 1;
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function qfsaveplugdetailstoplugs(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = []; $errorsum = 0;
        
        $vfam = trim($this->request->getVar('vfam'));
        $vin = trim($this->request->getVar('vin'));
        if($vin!=''){
            $decoded = $this->decodevin($vin);
            $vfam = $decoded['load'][0]['vfam'];
        }
        
        $probrand = $this->request->getVar('probrand');
        $afpartno = $this->request->getVar('partno');
        
        $plug = $this->dbHandle->query("select * from `3P_plugdetails` where `probrand`='{$probrand}' and `afpartno`='{$afpartno}' limit 1")->getResultArray()[0];
        
        //do updates
        // $data = [
        //     'probrand' => $plug['probrand'],
        //     'afpartno' => $plug['afpartno'],
        //     'supplierid' => $plug['supplier'],
        //     'plugtype' => $plug['plugtype'],
        //     'material' => $plug['material'],
        //     'oemno' => $plug['oemno'],
        //     'SpannerSize' => $plug['SpannerSize'],
        //     'Diameter_mm' => $plug['Diameter_mm'],
        //     'ThreadLength_mm' => $plug['ThreadLength_mm'],
        //     'threadSize' => $plug['threadSize'],
        //     'localref' => $plug['localref']
        // ];
        
        $this->dbHandle->query("update `3P_plugmaster` set
            probrand = '{$plug['probrand']}',
            afpartno = '{$plug['afpartno']}',
            supplierid = '{$plug['supplier']}',
            plugtype = '{$plug['plugtype']}',
            material = '{$plug['material']}',
            oemno = '',
            SpannerSize = '{$plug['SpannerSize']}',
            Diameter_mm = '{$plug['Diameter_mm']}',
            ThreadLength_mm = '{$plug['ThreadLength_mm']}',
            threadsize = '{$plug['threadsize']}',
            localref = '{$plug['localref']}' where `vfam`='{$vfam}'");
        
        $errorsum = $errorsum + $this->dbHandle->error()['code'];
        if($errorsum == 0){  
            $dataToReturn['status'] = 0;
        }else{
            $dataToReturn['status'] = 1;
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }*/
}