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

class Misc extends BaseController{
    private $dbHandle;

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

        $searchstr = $this->request->getVar('searchstr');
        //$title = $this->request->getVar('title');
        $source = $this->request->getVar('source');
        $picks = explode('|', $this->request->getVar('picks'));
        
        $parnames_raw = $this->request->getVar('parnames');
        $parnames = explode('|', $parnames_raw);
        
        $parvalues = explode('|', $this->request->getVar('parvalues'));
        $orderby = $this->request->getVar('orderby');
        //$preload = $this->request->getVar('preload');
        
        $whereclause = '';
        $picksInCSL = $this->toCSL($picks);
        
        if(trim($searchstr) == ''){
            if($parnames_raw != ''){
                $whereclause = " where (".$this->andCondition($parnames, $parvalues).")";
            }
            $qry = "select ".$picksInCSL." from `{$source}`{$whereclause} group by {$picksInCSL} order by `{$orderby}` limit 50";
        }else{
            if($parnames_raw != ''){
                $whereclause = " where (".$this->andCondition($parnames, $parvalues).") and (".$this->orCondition_like($picks, $searchstr).")";
            }
            $qry = "select ".$picksInCSL." from `{$source}`{$whereclause} group by {$picksInCSL} order by `{$orderby}` limit 50";
        }
        $R = $this->dbHandle->query($qry);
        if( $R->getNumRows()>0 ){
            $A = $R->getResultArray();

            $dataToReturn['status'] = 0;
            $dataToReturn['load'] = $A;
        }else{
            $dataToReturn['status'] = 1;
        }

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

        $searchstr = $this->request->getVar('searchstr');
        $R = $this->dbHandle->query("select * from `clients` where (`phone` like '%{$searchstr}%' or `whatsapp` like '%{$searchstr}%') order by `firstname` ASC limit 100");
        if( $R->getNumRows()>0 ){
            $A = $R->getResultArray();

            $dataToReturn['status'] = 0;
            $dataToReturn['load'] = $A;
        }else{
            $dataToReturn['status'] = 1;
        }

        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    private function andCondition($names, $values){
    	$cond = "";
    	for($i=0; $i<sizeof($names); $i++){
    		$cond .= " and `{$names[$i]}`='".$this->dbEscape($values[$i])."'";
    	}
    	return substr($cond, 5);
    }
    
    private function orCondition_like($names, $sstr){
    	$cond = "";
    	for($i=0; $i<sizeof($names); $i++){
    		$cond .= " or `{$names[$i]}` like '%".$sstr."%'";
    	}
    	return substr($cond, 4);
    }
    
    private function toCSL($arr){
    	$ret = "";
    	foreach($arr as $a){ $ret .= ",`".$this->dbEscape($a)."`"; }
    	$ret = substr($ret,1);
    	return $ret;
    }
    
    private function dbEscape($str){
        return $str;
    }
    
    // private function holePegStr($holes, $pegs){
    // 	global $DB;
    // 	$hpstr = "";
    // 	for($i=0; $i<sizeof($holes); $i++){
    // 		$hpstr .= ",`{$holes[$i]}`='".$DB->escape_string($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]}`='".$DB->escape_string($values[$i])."'";
    // 	}
    // 	$cond = substr($cond, 5);
    // 	return $cond;
    // }

    // private function toCSL($arr){
    // 	global $DB;
    // 	$ret = "";
    // 	foreach($arr as $a){ $ret .= ",`".$DB->escape_string($a)."`"; }
    // 	$ret = substr($ret,1);
    // 	return $ret;
    // }
}