digifi-mware/app/Controllers/BkoReports.php

<?php

namespace App\Controllers;

use CodeIgniter\API\ResponseTrait;
use CodeIgniter\HTTP\ResponseInterface;

class BkoReports extends BaseController
{
    use ResponseTrait;
    // protected $db;

    var $request;

    function __construct()
    {
        $this->request = \Config\Services::request();
    }

    public function startedApplication() :ResponseInterface
    {
        //SELECT uid,loan_amount,payment_month,sales_agent,gender,marital_status,email,address,state,country,status,added,updated FROM applications;
        $query = $this->db->query("SELECT c.firstname, c.lastname, a.uid,a.loan_amount,
                                   a.payment_month,a.sales_agent,a.gender,
                                   a.marital_status,a.email,a.address,
                                   a.state,a.country,a.status,a.added,
                                   a.updated 
                                   FROM applications a 
                                   LEFT JOIN customers c ON c.uid = a.customer_uid  WHERE a.status = 1 ");
        $row   = $query->getResult('array');
        $data = [
            'call_return' => '100',
            'records' =>  $row
        ];
        return $this->respond($data, 200);
      //  return $this->respond([], 200);
    }

    public function pendingApplication() :ResponseInterface
    {
        $pendingQuery  = $this->applicationQuery(1);
        //SELECT uid,loan_amount,payment_month,sales_agent,gender,marital_status,email,address,state,country,status,added,updated FROM applications;
        $query = $this->db->query("$pendingQuery ");
        $row   = $query->getResult('array');
        $data = [
            'call_return' => '100',
            'records' =>  $row
        ];
        return $this->respond($data, 200);
    }

    private function applicationQuery($status): string{
        return "
                SELECT c.firstname, c.lastname, a.uid,a.loan_amount,
                a.payment_month,a.sales_agent,a.gender,
                a.marital_status,a.email,a.address,
                a.state,a.country,a.status,a.added,
                a.updated,
                e.name AS employer_name ,
                e.uid AS employer_uid 
                FROM applications a 
                LEFT JOIN customers c ON c.uid::text = a.customer_uid::text    
                LEFT JOIN employers e ON e.uid::text =  a.employer_uid::text  
                WHERE a.status = $status ORDER BY a.id DESC 
        ";
   }

    public function readyApplication() :ResponseInterface
    {
        $readyQuery  = $this->applicationQuery(2);
        $query = $this->db->query("$readyQuery");
        $row   = $query->getResult('array');
        $data = [
            'call_return' => '100',
            'records' =>  $row
        ];
        return $this->respond($data, 200);
    }

    public function writingApplication() :ResponseInterface
    {
        $readyQuery  = $this->applicationQuery(4);
        $query = $this->db->query("$readyQuery");
        $row   = $query->getResult('array');
        $data = [
            'call_return' => '100',
            'records' =>  $row
        ];
        return $this->respond($data, 200);
    }


    public function approvedApplication() :ResponseInterface
    {
        $approvedQuery  = $this->applicationQuery(5);
        //SELECT uid,loan_amount,payment_month,sales_agent,gender,marital_status,email,address,state,country,status,added,updated FROM applications;
        $query = $this->db->query($approvedQuery);
        $row   = $query->getResult('array');
        $data = [
            'call_return' => '100',
            'records' =>  $row
        ];
        return $this->respond($data, 200);
    }

    public function rejectedApplication() :ResponseInterface
    {
        $rejectedQuery  = $this->applicationQuery(7);
        //SELECT uid,loan_amount,payment_month,sales_agent,gender,marital_status,email,address,state,country,status,added,updated FROM applications;
        $query = $this->db->query($rejectedQuery);
        $row   = $query->getResult('array');
        $data = [
            'call_return' => '100',
            'records' =>  $row
        ];
        return $this->respond($data, 200);
    }

public function employersList()  :ResponseInterface {
    $query = $this->db->query("SELECT uid,name FROM employer_sector");
    $employer_sector   = $query->getResult('array');

    $query = $this->db->query("SELECT uid,name FROM salary_sources");
    $salary_sources   = $query->getResult('array');

    $query = $this->db->query("SELECT * FROM employers");
    $row   = $query->getResult('array');
    $data = [
        'call_return' => '100',
        'salary_sources' => $salary_sources,
        'employer_sector' => $employer_sector,
        'records' =>  $row
    ];
    return $this->respond($data, 200);
}

    public function employerAdd():ResponseInterface{
        /*
            employer_uid:b41cefe6-47e6-4b85-a8de-67d186e3fe16
            name:OluSign ASign
            title:Mr.
            email:ameye@chiefsoft.com
            phone:6784574356
         */
        $sig_count = 0;
        $data = $this->request->getPost();
        $insert_data = $data;



        $result = $this-> insert_db('employers', $insert_data);
        $result_data = [
            'call_return' => '100',
            'signatory_count' => $sig_count,
            'result' =>$result
        ];
        return $this->respond($result_data, 200);
    }

    private function updateSignatoryCount($employer_uid): int
    {
        $sig_count=0;
        try {
                $sigCountQr = "SELECT count(s.id) AS sig_count from employers_signatory s LEFT JOIN employers e ON e.id=s.employer_id  WHERE e.uid='$employer_uid' ";
                log_message('error', "updateSignatoryCount   sigCountQr    -->" . $sigCountQr);
                $query = $this->db->query($sigCountQr);
                $row   = $query->getResult('array');
                $sig_count =  $row[0]["sig_count"];
                log_message('error', "updateSignatoryCount     $sig_count  -->" . $sig_count);

                $sigCountUpdateQr = "UPDATE employers SET signatory_count = $sig_count WHERE uid = '$employer_uid' ";
                log_message('error', "updateSignatoryCount     sigCountUpdateQr  -->" . $sigCountUpdateQr);
                $this->db->query($sigCountUpdateQr);
        } catch (Exception $e) {
            echo 'Caught exception: ',  $e->getMessage(), "\n";
        }
        return $sig_count;
    }

    public function signatoryList()  :ResponseInterface {

        $query = $this->db->query("SELECT e.name AS employer_name,  es.* FROM employers_signatory es LEFT JOIN employers e ON e.id =  es.employer_id");
        $row   = $query->getResult('array');
        $data = [
            'call_return' => '100',
            'records' =>  $row
        ];
        return $this->respond($data, 200);
    }

public function signatoryUpdate():ResponseInterface{

    $data = $this->request->getRawInputVar();
    $loc = 100;
    $signatory_uid = $data["signatory_uid"];
    $employer_id = $data["employer_id"];
    $result=[];
/*
 *  {
        "employer_id": "2",
        "signatory_uid": "6f2e4489-fce3-4c72-9077-fbc9f2a07f68",
        "name": "Olu Aupdate",
        "email": "ameye+update@chiefsoft.com",
        "title": "Director",
        "phone": "016784574356"
    },
 */
    if ( $signatory_uid !='') {
        $selSigQr = "SELECT id FROM employers_signatory WHERE uid = '" . $signatory_uid . "' AND employer_id = ".$employer_id;
        
        $query = $this->db->query($selSigQr);
        $row = $query->getResult('array');
        if ($row && $row[0]['id'] > 0) {
            $signatory_id = $row[0]['id'];
            $loc = 300;
            $update_data['name'] = $data['name'];
            $update_data['email'] = $data['email'];
            $update_data['title'] = $data['title'];
            $update_data['phone'] = $data['phone'];

            $sqlUpdtQr = "UPDATE employers_signatory 
                           SET name='".$update_data['name']."',
                               email='".$update_data['email']."',
                               title='".$update_data['title']."',
                               phone='".$update_data['phone']."'
                           WHERE uid='$signatory_uid' AND id=".$signatory_id;
            $this->db->query($sqlUpdtQr);
            //$this->db->where('id', $signatory_id);
           // $this->update_db('employers_signatory', $update_data, $signatory_id);
           // $this->db->update('employers_signatory', $update_data, array('id' => $signatory_id));

        } else {
            $result = ['fail' => 'Not Updated.'];
        }
    }

    $result_data = [
        'call_return' => '100',
        'loc' => $loc,
        'action' => 'patch',
        'data' =>  $data,
        'hh'=> $selSigQr,
        'result' =>$result
    ];
    return $this->respond($result_data, 200);
}
    public function signatoryAdd():ResponseInterface{
        /*
            $insert_data['employer_uid'] = $data['employer_uid'];
        $insert_data['name'] = $data['name'];
        $insert_data['title'] = $data['title'];
        $insert_data['email'] = $data['email'];
        $insert_data['phone'] = $data['phone'];

        if ( $insert_data['employer_uid'] !=''
            &&  $insert_data['name'] !=''
            &&  $insert_data['title'] !=''
            &&  $insert_data['email'] !=''
            &&  $insert_data['phone'] !=''  ){

        }
        else{
            $result =  [
                "error" => "Missing Required Data"
            ];
        }
         */
        $data = $this->request->getPost();
        $loc = 100;
        $employer_uid = $data["employer_uid"];
        if ( $employer_uid !=''){
            $query = $this->db->query("SELECT id FROM employers WHERE uid = '".$employer_uid."' ");
            $loc = $employer_uid;
            $row   = $query->getResult('array');
            if ($row && $row[0]['id']> 0){
                $loc = 300;
                $insert_data['employer_id'] =$row[0]['id'];
                $insert_data['name'] = $data['name'];
                $insert_data['email'] = $data['email'];
                $insert_data['title'] = $data['title'];
                $insert_data['phone'] = $data['phone'];
                $result = $this-> insert_db('employers_signatory', $insert_data);
                $sig_count = $this->updateSignatoryCount( $data['employer_uid'] );
            }
            else
            {
                $result = ['fail'=>'Not Added'];
            }
        }

     //   $insert_data = $data;

        $result_data = [
            'call_return' => '100',
            'loc' => $loc,
            'result' =>$result
        ];
        return $this->respond($result_data, 200);
    }

    public function listUsers():ResponseInterface{

        $query = $this->db->query("SELECT id,uid,username, email,firstname,lastname,status, added,updated FROM bko_users");
        $row   = $query->getResult('array');
        $data = [
            'call_return' => '100',
            'records' =>  $row
        ];
        return $this->respond($data, 200);
    }

    public function getCustomer($uid){

        $query = $this->db->query("SELECT * FROM customers WHERE uid::text ='".$uid."'");
        $rowC   = $query->getResult('array');

        $mySqA ="SELECT uid AS applications_uid, loan_amount, added, payment_month FROM applications WHERE customer_uid::text ='".$uid."'";
        $query = $this->db->query($mySqA);
        $rowA   = $query->getResult('array');

        $data = [
            'call_return' => '100',
            'customer' =>  $rowC,
            'applications' =>  $rowA
        ];
        return $this->respond($data, 200);


    }
            public function listCustomers():ResponseInterface{

                $query = $this->db->query("select * from customers order by id desc");
                $row   = $query->getResult('array');
                $data = [
                    'call_return' => '100',
                    'records' =>  $row
                ];
                return $this->respond($data, 200);
            }
}