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

class Transfers extends BaseController{
    private $dbHandle;
    
    public function loadothervendors(){
    	$error = 1 ;
        $this->dbx   = \Config\Database::connect();
        
        $searchstr = $this->request->getVar('searchstr');
        $me     = $this->request->getVar('uid');
        $R      = $this->dbx->query("select companyid, companyname, streetname, streetno, companyphone, region, district, town from `vendors` where companyid != '{$me}' and companyid != '0001' and (`companyid` like '%{$searchstr}%' or `companyname` like '%{$searchstr}%') order by `companyname` ASC, `region` ASC limit 100");
        $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 savetransfer(){
    	$error = 1 ;
        $this->dbx   = \Config\Database::connect();
        
        $now = date('Y-m-d H:i:s', time());
        
        $post_transferername    = $this->request->getVar('transferername');
        $post_transfererid      = $this->request->getVar('personnel');
        $prn                    = $this->request->getVar('recipientname');
        $post_recipientname     = substr($prn, 0, strrpos($prn,'(')-1);
        $post_recipientid       = $this->request->getVar('recipientid');
        $post_note              = $this->request->getVar('note');
        
        $post_products          = explode("\n", $this->request->getVar('productpartnos'));  
        
        $invno = 100001;
        $R = $this->dbx->query("select count(distinct invoiceno) as 'all' from `transfers` where `transfererid`='{$post_transfererid}'");
        $A = $R->getResultArray();
        if(sizeof($A)>0){
            $invno = $invno + $A[0]['all'];
        }
        $invno = "TRF".$invno;
        
        for($i = 0; $i < count($post_products); $i++){
            $tarr       = explode(" ", $post_products[$i], 2);
            $theMan     = $tarr[0];
            
            $tarr2      = explode("=", $tarr[1]);
            $thePartNo  = trim($tarr2[0]);
            $theQty     = trim($tarr2[1]);
            
            //get the price
            $thePrice = 0;
            $pR = $this->dbx->query("select price from partdes where probrand = '{$theMan}' and partno = '{$thePartNo}'");
            $pA = $pR->getResultArray();
            if(sizeof($pA)>0){
                $thePrice = $pA[0]['price'];
            }
            
            $data = [
                'transactdate'      => $now,
                'invoiceno'         => $invno,
                'transferername'    => $post_transferername,
                'transfererid'      => $post_transfererid,
                'recipientname'     => $post_recipientname,
                'recipientid'       => $post_recipientid,
                'productman'        => $theMan,
                'productpartno'     => $thePartNo,
                'transferqty'       => $theQty,
                'itemprice'         => $thePrice,
                'note'              => $post_note,
                'status'            => "Posted"
    		];
    		
    		$this->dbx->table('transfers')->insert($data);
        }
        
		$W = [];
		$W['status'] = $this->dbx->error()['code'];
		$W['load'] = count($post_products)." item(s) successfully transferred to {$post_recipientname} ({$post_recipientid})";
		$W['extra'] = $invno;
		return json_encode($W, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function posttransfer(){
    	$error = 1 ;
        $this->dbx   = \Config\Database::connect();
        
        $products = json_decode($this->request->getVar('products'), true);
        $vendor = json_decode($this->request->getVar('vendor'), true);
        $recipient = json_decode($this->request->getVar('recipient'), true);
        $saletotalamount = $this->request->getVar('saletotalamount');
        
        $now = date('Y-m-d H:i:s', time());
        
        $invno = 100001;
        $R = $this->dbx->query("select count(distinct invoiceno) as 'all' from `transfers` where `transfererid`='{$vendor['rbun']['companyid']}'");
        $A = $R->getResultArray();
        if(sizeof($A)>0){
            $invno = $invno + $A[0]['all'];
        }
        
        for($i = 0; $i < count($products); $i++){
            $theProduct = $products[$i]['item']['raw'];
            $theProduct['probrand'] = explode('_',$theProduct['eid'])[0];
            
            //get the price
            $thePrice = 0;
            $pR = $this->dbx->query("select price from 3P_plugmaster where probrand = '{$theProduct['probrand']}' and afpartno = '{$theProduct['partno']}'");
            $pA = $pR->getResultArray();
            if(sizeof($pA)>0){
                $thePrice = $pA[0]['price'];
            }
            
            $data = [
                'transactdate'      => $now,
                'invoiceno'         => $invno,
                'transferername'    => $vendor['rbun']['companyname'],
                'transfererid'      => $vendor['rbun']['companyid'],
                'transfererloginname' => $vendor['firstname']." ".$vendor['lastname'],
                'transfererloginid' => $vendor['uid'],
                'recipientname'     => $recipient['companyname'],
                'recipientid'       => $recipient['companyid'],
                'productman'        => $theProduct['probrand'],
                'productpartno'     => $theProduct['partno'],
                'transferqty'       => $products[$i]['saleqty'],
                'itemprice'         => $thePrice,
                'status'            => 'Posted'
    		];
    		
    		$this->dbx->table('transfers')->insert($data);
        }
        
		$W = [];
		$W['status'] = $this->dbx->error()['code'];
		$W['extra'] = $invno;
		return json_encode($W, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function postoverseastransfer(){
    	$error = 1 ;
        $this->dbx   = \Config\Database::connect();
        
        $products = json_decode($this->request->getVar('products'), true);
        $vendor = json_decode($this->request->getVar('vendor'), true);
        $recipient = json_decode($this->request->getVar('recipient'), true);
        $officer = json_decode($this->request->getVar('officer'), true);
        $saletotalamount = $this->request->getVar('saletotalamount');
        
        $now = date('Y-m-d H:i:s', time());
        
        $invno = 100001;
        $R = $this->dbx->query("select count(distinct invoiceno) as 'all' from `transfers` where `transfererid`='{$vendor['supplierid']}'");
        $A = $R->getResultArray();
        if(sizeof($A)>0){
            $invno = $invno + $A[0]['all'];
        }
        
        for($i = 0; $i < count($products); $i++){
            $theProduct = $products[$i]['item']['raw'];
            $theProduct['probrand'] = explode('_',$theProduct['eid'])[0];
            
            //get the price
            $thePrice = 0;
            $pR = $this->dbx->query("select price from 3P_plugmaster where probrand = '{$theProduct['probrand']}' and afpartno = '{$theProduct['partno']}'");
            $pA = $pR->getResultArray();
            if(sizeof($pA)>0){
                $thePrice = $pA[0]['price'];
            }
            
            $data = [
                'transactdate'      => $now,
                'invoiceno'         => $invno,
                'transferername'    => $vendor['supplierid'],
                'transfererid'      => $vendor['supplierid'],
                'transfererloginname' => $officer['firstname']." ".$officer['lastname'],
                'transfererloginid' => $officer['uid'],
                'recipientname'     => $recipient['companyname'],
                'recipientid'       => $recipient['companyid'],
                'productman'        => $theProduct['probrand'],
                'productpartno'     => $theProduct['partno'],
                'transferqty'       => $products[$i]['saleqty'],
                'itemprice'         => $thePrice,
                'status'            => 'Posted'
    		];
    		
    		$this->dbx->table('transfers')->insert($data);
        }
        
		$W = [];
		$W['status'] = $this->dbx->error()['code'];
		$W['extra'] = $invno;
		return json_encode($W, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function getpostedtransfers(){
    	$error = 1 ;
        $this->dbx   = \Config\Database::connect();
        $dataToReturn = [];
        
        $vendorid = $this->request->getVar('vendorid');
        
        $R = $this->dbx->query("select distinct invoiceno as 'inv' from `transfers` where `transfererid`='{$vendorid}'");
        if( $R->getNumRows()>0 ){
            $dataToReturn['status'] = 0;
            $bun = [];

            foreach($R->getResultArray() as $W){
                $dataToReturn['load'][$W['inv']] = $this->dbx->query("select * from `transfers` where `transfererid`='{$vendorid}' and invoice='{$W['inv']}'")->getResultArray();
            }
        }else{
            $dataToReturn['status'] = 1;
        }
        
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
    
    public function getsuppliers(){
    	$error = 1 ;
        $this->dbx   = \Config\Database::connect();
        $dataToReturn = [];
        
        $R = $this->dbx->query("select supplierid from `3P_plugmaster` group by supplierid order by supplierid ASC");
        if( $R->getNumRows()>0 ){
            $dataToReturn['status'] = 0;
            $dataToReturn['load'] = $R->getResultArray();
        }else{
            $dataToReturn['status'] = 1;
        }
        
        return json_encode($dataToReturn, JSON_HEX_APOS | JSON_HEX_QUOT);
    }
}