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

class Reports extends BaseController{
    private $dbHandle;
    // private $shortMonths = [
    //     "Jan" => "01",
    //     "Feb" => "02",
    //     "Mar" => "03",
    //     "Apr" => "04",
    //     "May" => "05",
    //     "Jun" => "06",
    //     "Jul" => "07",
    //     "Aug" => "08",
    //     "Sep" => "09",
    //     "Oct" => "10",
    //     "Nov" => "11",
    //     "Dec" => "12"
    // ];
    
    public function getallvendors(){
    	$error = 1 ;
        $this->dbx = \Config\Database::connect();
        
        $R = $this->dbx->query("select companyid, companyname, streetname, streetno, companyphone, region, district, town from `vendors` order by `companyname` ASC, `region` ASC limit 500");
        $A = $R->getResultArray();
        
        $W = [];
        $W['status'] = 1;
        
        if(sizeof($A)>0){
            $W['status'] = 0;
            $W['load'] = $A;
        }
		return json_encode($W, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function getdailysales(){
    	$error = 1 ;
        $this->dbx = \Config\Database::connect();
        
        $vendor = $this->request->getVar('vendor');
        $saledate = substr($this->request->getVar('saledate'),0,10);
        
        $dataToReturn = [];
        $dataToReturn['status'] = 1;
        $dataToReturn['load'] = [];
        
        $R = $this->dbx->query("select sal_invoiceno from sales where vnd_companyid='{$vendor}' and sal_date like '{$saledate} %' group by sal_invoiceno order by `sal_invoiceno` ASC");
        $dataToReturn['status'] = $this->dbx->error()['code'];
        
        foreach($R->getResultArray() as $W){
            $R2 = $this->dbx->query("select * from sales where sal_invoiceno='{$W['sal_invoiceno']}'");
            $W2 = $R2->getResultArray();
            $itemcount = count($W2);
            $W2 = $W2[0];
            
            $DS = [];
            $DS['invoice']['no'] = $W2['sal_invoiceno'];
            $DS['invoice']['amount'] = $W2['sal_totalamount'];
            $DS['invoice']['itemcount'] = $itemcount;
            $DS['vendor']['id'] = $W2['vnd_companyid'];
            $DS['vendor']['name'] = $W2['vnd_companyname'];
            $DS['vendor']['region'] = $W2['vnd_region'];
            $DS['vendor']['town'] = $W2['vnd_town'];
            $DS['vendor']['phone'] = $W2['vnd_companyphone'];
            $DS['customer']['id'] = $W2['cli_phone'];
            $DS['customer']['name'] = $W2['cli_firstname'];
            $DS['customer']['region'] = $W2['cli_region'];
            $DS['customer']['town'] = $W2['cli_town'];
            $DS['customer']['phone'] = $W2['cli_phone'];
            $DS['customer']['whatsapp'] = $W2['cli_whatsapp'];
            $DS['time'] = date('H:i', strtotime($W2['sal_date']));
                
            $dataToReturn['load'][] = $DS;
        }
        
		return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function getplugapplicables(){
    	$error = 1 ;
        $this->dbx = \Config\Database::connect();
        
        $partno = $this->request->getVar('partno');
        
        $dataToReturn = [];
        $dataToReturn['status'] = 1;
        $dataToReturn['load'] = [];
        
        $R_plugs = $this->dbx->query("select vfam, probrand, plugtype, afpartno from 3P_plugmaster where afpartno='{$partno}' group by vfam, probrand, plugtype, afpartno order by `vfam` ASC");
        $dataToReturn['status'] = $this->dbx->error()['code'];
        $t = 0;
        
        $this->dbx->query("CREATE TEMPORARY TABLE plugappl (make varchar(32), model varchar(32), displacementL varchar(32), genfr varchar(32), gento varchar(32))");
        $this->dbx->query("TRUNCATE TABLE plugappl");

        foreach($R_plugs->getResultArray() as $W_plug){
            $t++;
            if($t==1){ $dataToReturn['load']['plugs'][] = $W_plug; }
            
            $this->dbx->query("insert into plugappl select make, model, displacementL, genfr, gento from 3V_supplementary where vfam='{$W_plug['vfam']}'");
        }
        
        $R_vehicles = $this->dbx->query("select * from plugappl where vfam='{$W_plug['vfam']}' order by make ASC, model ASC, displacementL ASC, genfr ASC, gento ASC");
        foreach($R_vehicles->getResultArray() as $W_vehicle){
            $dataToReturn['load']['vehicles'][] = $W_vehicle;
        }
        
		return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    /*public function getplugapplicables(){
    	$error = 1 ;
        $this->dbx = \Config\Database::connect();
        
        $partno = $this->request->getVar('partno');
        $uniqueKeys = [];
        $uniqueFams = [];
        
        $dataToReturn = [];
        $dataToReturn['status'] = 1;
        $dataToReturn['load'] = [];
        
        $R_plugs = $this->dbx->query("select vfam, probrand, plugtype, afpartno from 3P_plugmaster where afpartno='{$partno}' group by vfam, probrand, plugtype, afpartno order by `vfam` ASC");
        $dataToReturn['status'] = $this->dbx->error()['code'];
        foreach($R_plugs->getResultArray() as $W_plug){
            $v = $W_plug['probrand']."_".$W_plug['afpartno'];
            
            if(!in_array($v, $uniqueKeys, true)){
                $uniqueKeys[$v] = $v;
                $dataToReturn['load']['plugs'][] = $W_plug;
            }
            
            if(!in_array($W_plug['vfam'], $uniqueFams, true)){
                $uniqueFams[$W_plug['vfam']] = $W_plug['vfam'];
                
                $R_vehicles = $this->dbx->query("select * from 3V_supplementary where vfam='{$W_plug['vfam']}'");
                foreach($R_vehicles->getResultArray() as $W_vehicle){
                    $dataToReturn['load']['vehicles'][] = $W_vehicle;
                }
            }
        }
        
		return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }*/
}