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

class Mopsengines extends BaseController{
    private $dbHandle;
    
    public function matchandreplace(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        $dataToReturn['status'] = 1;
        
        $picktable		 	= trim($this->request->getVar('picktable'));
        $dumptable		 	= trim($this->request->getVar('dumptable'));
        $matches		    = explode(',',trim($this->request->getVar('matches')));
        $replacements	    = explode(',',trim($this->request->getVar('replacements')));
        
        $id_from			= 0;
        $id_to				= 0;
        
        //Collect and clean up strings
        $matches_arr = [];
        $matches_val_arr = [];
        foreach($matches as $m){
        	$m = trim($m); if($m=="") continue;
        	$matches_arr[] = $m;
        }
        
        $replacements_arr = [];
        $replacements_val_arr = [];
        foreach($replacements as $r){
        	$r = trim($r); if($r=="") continue;
        	$replacements_arr[] = $r;
        }
        
        //range clause
        $rangeclause = "";
        if($id_from>0){
        	if($id_to>0){
        		$rangeclause = "where id>='{$id_from}' and id<='{$id_to}' ";
        	}else{
        		$rangeclause = "where id>='{$id_from}' ";
        	}
        }
        
        $R = $this->dbHandle->query("select id,".$this->toCSL($matches_arr).",".$this->toCSL($replacements_arr)." from `{$picktable}` {$rangeclause}order by id ASC");
    	$A = $R->getResultArray();
    	
    	if(sizeof($A)>0){
    	    $dataToReturn['status'] = 0;
    	    $dataToReturn['load'] = $picktable;
    	    //try{ $this->dbHandle->query("ALTER TABLE `{$picktable}` ADD `p_s` INT NOT NULL FIRST"); }
    	    //catch(Exception $e){}
    	}
    	echo json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    	//fastcgi_finish_request();
    	
        if(sizeof($A)>0){
    		foreach($A as $W){
    			$id = $W['id'];
    			for($i=0; $i<sizeof($replacements_arr); $i++){
    				$replacements_val_arr[$i] = trim($W[$replacements_arr[$i]]);
    			}
    			for($i=0; $i<sizeof($matches_arr); $i++){
    				$matches_val_arr[$i] = trim($W[$matches_arr[$i]]);
    			}
    			$this->dbHandle->query("update `{$dumptable}` set ".$this->holePegStr($replacements_arr, $replacements_val_arr)." where ".$this->andCondition($matches_arr, $matches_val_arr));
    			if($this->dbHandle->error()['code'] == 0){
    			    $this->dbHandle->query("update `{$picktable}` set `p_s`='1' where id='{$id}'");
    			}
    		}
        }
    }
    
    public function minandmax(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        $dataToReturn['status'] = 1;
        
        $worktable		 	= trim($this->request->getVar('worktable'));
        $groupcolumn	 	= trim($this->request->getVar('groupcolumn'));
        $pickcolumn	        = trim($this->request->getVar('pickcolumn'));
        $dumpmincolumn	    = trim($this->request->getVar('dumpmincolumn'));
        $dumpmaxcolumn	    = trim($this->request->getVar('dumpmaxcolumn'));
        
        $R = $this->dbHandle->query("select `{$groupcolumn}` from `{$worktable}` group by `{$groupcolumn}` order by `{$groupcolumn}` ASC");
    	$A = $R->getResultArray();
    	
    	if(sizeof($A)>0){
    	    $dataToReturn['status'] = 0;
    	    $dataToReturn['load'] = $worktable;
    	    //try{ $this->dbHandle->query("ALTER TABLE `{$picktable}` ADD `p_s` INT NOT NULL FIRST"); }
    	    //catch(Exception $e){}
    	}
    	echo json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    	//fastcgi_finish_request();
    	
        if(sizeof($A)>0){
    		foreach($A as $W){
    			$group = $W[$groupcolumn];
    			
    			$R2 = $this->dbHandle->query("select MIN({$pickcolumn}) as minv, MAX({$pickcolumn}) as maxv from {$worktable} where `{$groupcolumn}`='{$group}'");
    			$A2 = $R2->getResultArray();
    			if(sizeof($A2)>0){
    			    $minv = $A2[0]['minv'];
			        $maxv = $A2[0]['maxv'];
			
    			    $this->dbHandle->query("update `{$worktable}` set {$dumpmincolumn} = '{$minv}', {$dumpmaxcolumn} = '{$maxv}' where `{$groupcolumn}`='{$group}'");
        // 			if($this->dbHandle->error()['code'] == 0){
        // 			    $this->dbHandle->query("update `{$picktable}` set `p_s`='1' where id='{$id}'");
        // 			}
    			}
    		}
        }
    }
    
    public function checkstatus(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        $dataToReturn['status'] = 1;
        
        $checkkey		 	= trim($this->request->getVar('checkkey'));
        
        $done = 0; $total = 1;
        
        $R = $this->dbHandle->query("select count(p_s) as 'donecount' from `{$checkkey}` where p_s='1'");
        if( $R->getNumRows()>0 ){ $done = (int)$R->getResultArray()[0]['donecount']; }
        
        $R = $this->dbHandle->query("select count(*) as 'totalcount' from `{$checkkey}`");
        if( $R->getNumRows()>0 ){
            $total = (int)$R->getResultArray()[0]['totalcount'];
            
            $dataToReturn['status'] = 0;
            $dataToReturn['load'] = (($done / $total)*100)."%";
        }

        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    private function holePegStr($holes, $pegs){
    	$hpstr = "";
    	for($i=0; $i<sizeof($holes); $i++){
    		//$hpstr .= ",`{$holes[$i]}`='".$this->dbHandle->escape($pegs[$i])."'";
    		$hpstr .= ",`{$holes[$i]}`='".$pegs[$i]."'";
    	}
    	$hpstr = substr($hpstr,1);
    	return $hpstr;
    }
    
    private function andCondition($names, $values){
    	//global $DB;
    	$cond = "";
    	for($i=0; $i<sizeof($names); $i++){
    		//$cond .= " and `{$names[$i]}`='".$this->dbHandle->escape($values[$i])."'";
    		$cond .= " and `{$names[$i]}`='".$values[$i]."'";
    	}
    	$cond = substr($cond, 5);
    	return $cond;
    }
    
    private function toCSL($arr){
    	//global $DB;
    	$ret = "";
    	//foreach($arr as $a){ $ret .= ",`".$this->dbHandle->escape($a)."`"; }
    	foreach($arr as $a){ $ret .= ",`".$a."`"; }
    	$ret = substr($ret,1);
    	return $ret;
    }




    public function index(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $ops = $this->request->getVar('ops');
        switch($ops){
            case "":
                break;
            
        }

        $R = $this->dbHandle->query("select `module` from `modules` where modulecat='{$modulecat}'");
        $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 len_vfam(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        $dataToReturn['status'] = 1;
        
        $vin = $this->request->getVar('vin');
        $module = strtolower($this->request->getVar('module'));
        switch($module){
            case "pref": case "mo8": case "mo9": case "mo10":
            $dataToReturn['status'] = 0;
            $dataToReturn['load'][] = [
                "len" => 8,
                "vfam" => substr($vin,0,8),
                "vinprefix" => substr($vin,0,8)."*".substr($vin,9,1)
            ];
            break;
            
            case "ma8": case "ma9": case "ma10":
            $dataToReturn['status'] = 0;
            $dataToReturn['load'][] = [
                "len" => 8,
                "vfam" => substr($vin,0,8),
                "vinprefix" => "N/A"
            ];
            break;
            
            case "mac":
            $dataToReturn['status'] = 0;
            $dataToReturn['load'][] = [
                "len" => 10,
                "vfam" => substr($vin,0,8)."*".substr($vin,9,1),
                "vinprefix" => "N/A"
            ];
            break;
            
            case "jp":
            $dataToReturn['status'] = 0;
            
            $p = strpos("-",$vin);
            $dataToReturn['load'][] = [
                "len" => $p,
                "vfam" => substr($vin,0,$p+1),
                "vinprefix" => "N/A"
            ];
            break;
            
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function vinprefix(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        $dataToReturn['status'] = 1;
        
        $vin = $this->request->getVar('vin');
        switch($module){
            case "pref": case "mo8": case "mo9": case "mo10":
            $dataToReturn['status'] = 0;
            $dataToReturn['load'][] = [
                "vinprefix" => substr($vin,0,8)."*".substr($vin,9,1)
            ];
            break;
            
            default:
            $dataToReturn['status'] = 0;
            
            $dataToReturn['load'][] = [
                "vinprefix" => "N/A"
            ];
            
        }
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function regions(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $R = $this->dbHandle->query("select `region` from `locations` group by `region` order by `region`");
        $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 districts(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $region = $this->request->getVar('region');
        
        $R = $this->dbHandle->query("select `mmda` as 'district' from `locations` where `region`='{$region}' group by `district` order by `district`");
        $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 towns(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $region = $this->request->getVar('region');
        $district = $this->request->getVar('district');
        
        $R = $this->dbHandle->query("select `town` from `locations` where `region`='{$region}' and `mmda`='{$district}' group by `town` order by `town`");
        $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 townsofregion(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $region = $this->request->getVar('region');
        
        $R = $this->dbHandle->query("select `town` from `locations` where `region`='{$region}' group by `town` order by `town`");
        $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 newclient(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $now = date('Y-m-d H:i:s', time());
        
        //get the district
        $region = $this->request->getVar('region');
        $town = $this->request->getVar('town');
        $mmda = '';
            
        $R = $this->dbHandle->query("select mmda from `locations` where region='{$region}' and town='{$town}' order by mmda DESC limit 1");
		$A = $R->getResultArray();
        if(sizeof($A)>0){
            $mmda = $A[0]['mmda'];
        }
        
        //continue
        $data = [
            'transactdate'      => $now,
            'firstname'         => $this->request->getVar('firstname'),
            'phone'             => $this->request->getVar('phone'),
            'whatsapp'          => $this->request->getVar('whatsapp'),
            'region'            => $region,
            'district'          => $mmda,
            'town'              => $town,
            'note'              => $this->request->getVar('note'),
            'actingvendor'      => $this->request->getVar('actingvendor'),
            'personnel'         => $this->request->getVar('personnel'),
            'password'          => '0000'
		];
		//tests
		if (preg_match('/^(0|\+233)[25]\d{8}$/', $data['phone'])) {
		    $this->dbHandle->table('registry_users')->insert($data);
		    $dataToReturn['status'] = $this->dbHandle->error()['code'];
		    if($dataToReturn['status']!=0){
		        $dataToReturn['extra'] = "Phone No is already being used by another account";
		    }
        }else {
            $dataToReturn['status'] = 2;
            $dataToReturn['extra'] = "Phone No was not recognized as valid";
        }
		
		return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    /*public function newclient(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];

        $now = time();
        
        //get the district
        $region = $this->request->getVar('region');
        $town = $this->request->getVar('town');
        $mmda = '';
            
        $R = $this->dbHandle->query("select mmda from `locations` where region='{$region}' and town='{$town}' order by mmda DESC limit 1");
		$A = $R->getResultArray();
        if(sizeof($A)>0){
            $mmda = $A[0]['mmda'];
        }
         
        $data = [
            'firstname'              => ucwords($this->request->getVar('firstname')),
            'phone'             => $this->request->getVar('phone'),
            'whatsapp'          => $this->request->getVar('whatsapp'),
            'region'            => $region,
            'town'              => $town,
            'district'          => $mmda,
            'address'           => $this->request->getVar('address'),
            'note'              => $this->request->getVar('note'),
            'personnel'         => $this->request->getVar('personnel'),
            'transactdate'      => date('Y-m-d H:i:s', $now)
        ];
        
        $this->dbHandle->table('clients')->insert($data);
        $E = $this->dbHandle->error();
        $dataToReturn['status'] = $E['code'];

        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }*/
    
    public function getproductmans(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];

        $R = $this->dbHandle->query("select `probrand` from `3P_plugmaster` group by `probrand` order by `probrand` ASC");
        $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 getproductmansbysupplier(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $supplierid = $this->request->getVar('supplierid');

        $R = $this->dbHandle->query("select `probrand` from `3P_plugmaster` where supplierid='{$supplierid}' group by `probrand` order by `probrand` ASC");
        $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 getproductpartnos(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $brand = $this->request->getVar('brand');

        // $R = $this->dbHandle->query("select concat(probrand,'_',partno) as eid, partno, prodes, price from `3P_plugmaster` where probrand='{$brand}' group by partno, prodes, price order by `partno` ASC");
        $R = $this->dbHandle->query("select concat(probrand,'_',afpartno) as eid, afpartno as 'partno', plugtype as 'prodes', price from `3P_plugmaster` where probrand='{$brand}' group by afpartno, plugtype, price order by `afpartno` ASC");
        $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);
    }
    
    /*** The Old Function ***/
    public function newcustomer(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];

        $now = time();
         
        $data = [
            'name'              => ucwords($this->request->getVar('name')),
            'phone'             => $this->request->getVar('phone'),
            'whatsapp'          => $this->request->getVar('whatsapp'),
            'address'           => $this->request->getVar('address'),
            'latitude'          => $this->request->getVar('latitude'),
            'longitude'         => $this->request->getVar('longitude'),
            'note'              => $this->request->getVar('note'),
            'personnel'         => $this->request->getVar('personnel'),
            'transactdate'      => date('Y-m-d H:i:s', $now)
        ];
        
        $this->dbHandle->table('customers')->insert($data);
        $E = $this->dbHandle->error();
        $dataToReturn['status'] = $E['code'];

        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function newstaff(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];

        $now = time();
         
        $data = [
            'uid'               => md5(uniqid(rand(), true)),
            'firstname'         => ucwords($this->request->getVar('firstname')),
            'phone'             => $this->request->getVar('phone'),
            'email'             => $this->request->getVar('email'),
            'department'        => $this->request->getVar('department'),
            'position'          => $this->request->getVar('position'),
            'address'           => $this->request->getVar('address'),
            'password'          => '0000',
            'level'             => '1',
            'staffid'           => strtoupper($this->randomStr(8)),
            'note'              => $this->request->getVar('note'),
            'transactdate'      => date('Y-m-d H:i:s', $now)
        ];
        
        $this->dbHandle->table('staff')->insert($data);
        $E = $this->dbHandle->error();
        $dataToReturn['status'] = $E['code'];

        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    private function randomStr($len){
        $random = mt_rand(0, (1 << ($len << 2)) - 1);
        $number = dechex($random);
        return str_pad($number, $len, '0', STR_PAD_LEFT);
    }
    
    public function price(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $plugbrand = $this->request->getVar('plugbrand');
        $partno = $this->request->getVar('partno');
        
        $R = $this->dbHandle->query("select `price` from `prices` where `effectivedate` <= NOW() order by `effectivedate` DESC limit 1");
        $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 bothprices(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $vfam = trim($this->request->getVar('vfam'));
        $plugbrand = $this->request->getVar('plugbrand');
        $partno = $this->request->getVar('partno');
        
        $R = $this->dbHandle->query("select `price` from `prices` where `plugbrand`='{$plugbrand}' and `partno`='{$partno}' and `effectivedate` <= NOW() order by `effectivedate` DESC limit 1");
        $A = $R->getResultArray();
        if(sizeof($A)>0){
            $dataToReturn['status'] = 0;
            $dataToReturn['load'] = $A;
            
            //get the specific one too
            $sp = 0;
            if($vfam != ''){
                $H = $this->dbHandle->query("select `specificprice` from `specificprices` where `vfam`='$vfam' and `plugbrand`='{$plugbrand}' and `partno`='{$partno}' and `effectivedate` <= NOW() order by `effectivedate` DESC limit 1")->getResultArray();
                if(sizeof($H)>0){
                    $sp = $H[0]['specificprice'];
                }
            }
            
            $dataToReturn['load'][0]['specificprice'] = $sp;
        }else{
            $dataToReturn['status'] = 1;
        }

        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function getautoplusvendor(){
        $this->dbHandle   = \Config\Database::connect();
        $dataToReturn = [];
        
        $R = $this->dbHandle->query("select * from `vendors` where companyid='0001' limit 1");
        $A = $R->getResultArray();
        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);
    }
}