<?php

namespace App\Models\UserAccess;

use DB;
use Auth;
use Illuminate\Http\Request;
use Illuminate\Database\Eloquent\Model;

class UserAccessRequest extends Model
{
	public $table      = 'user_access_request';
    public $timestamps = false;

    /*
     * Date: 19-03-2019 
     * Author: Imran Khan
     * Parameter: id, Search
     * Description:  this function will return user access request list
     */    public static function GetAccessRequestList($search = "",$status="",$pagesize=10, $user_id="")
    {
        $access = DB::table('user_access_request as uar');
        $access = $access->select('uar.*','u.*','uspf.pd_file_id as pd_file_id')
		->join('users as u', 'u.id', '=', 'uar.created_by')
        ->leftjoin('user_access_pd_file as uspf', 'uar.request_id', '=', 'uspf.request_id');

        if ($status == "") {
              $access = $access->whereIn('uar.request_status', array('N','P','C'));
              }
              else {
              $access = $access->where('uar.request_status','=', $status);
              }

        if ($search != "") {

            $access = $access->whereRaw("(uar.order_number like '%" . $search . "%' or uar.access_type like '%" . $search . "%' or uar.client_name like '%" . $search . "%' or uar.contact_person_email like '%" . $search . "%' or uar.organization_name like '%" . $search . "%' or u.name like '%" . $search . "%' )");
        }        if ($user_id != "") {
            $access = $access->where('uar.created_by','=', $user_id);
        }

        $access = $access->groupBy('uar.request_id');
        $access = $access->orderBy('uar.request_id', 'ASC');
        $access = $access->paginate($pagesize);
		
		
        return $access;
    }

    /*
     * Date: 16-04-2019 
     * Author: Imran Khan
     * Parameter: id, Search
     * Description:  this function will return user access request complete detail list with user access
     */    public static function GetAccessRequestDetailList($search = "",$status="",$pagesize=10, $user_id="")
    {
        $access = DB::table('user_access_request as uar');
        $access = $access->select('uar.*','u.*','ua.name as access_user','uspf.pd_file_id as pd_file_id')
                  ->join('users as u', 'u.id', '=', 'uar.created_by')
                  ->leftjoin('user_access_info as uai', 'uai.user_access_id', '=', 'uar.user_access_id')
                  ->leftjoin('users as ua', 'ua.id', '=', 'uai.user_id')
                  ->leftjoin('user_access_pd_file as uspf', 'uar.request_id', '=', 'uspf.request_id');
        if ($status == "") {
              $access = $access->whereIn('uar.request_status', array('N','P','C'));
              }
              else {
              $access = $access->where('uar.request_status','=', $status);
              }

        if ($search != "") {

            $access = $access->whereRaw("(uar.access_type like '%" . $search . "%'  or uar.client_name like '%" . $search . "%' or uar.contact_person_email like '%" . $search . "%' or uar.organization_name like '%" . $search . "%' )");
        }        if ($user_id != "") {
            $access = $access->where('uar.created_by','=', $user_id);
        }
        
        $access = $access->groupBy('uar.request_id');
        $access = $access->orderBy('uar.request_id', 'DESC');
        $access = $access->paginate($pagesize);
        return $access;
    }

    /*
     * Date: 19-03-2019
     * Author: Imran Khan
     * Parameter: data[]
     * Description:  this function will insert the Access Request
     */
    public static function AddAccessRequest($data)
    {
        $id = DB::table('user_access_request')->insertGetId($data);

        $results = (array) DB::table('user_access_request')->select(DB::raw('*'))->where('request_id', '=', $id)->first();
        
        $results['operation'] = 'I';
		
        DB::table('user_access_request_history')->insert($results);

        return $id;
    }

    /*
     * Date: 10-04-2019
     * Author: Imran Khan
     * Parameter: data[]
     * Description:  this function will insert the Access Request pd file
     */
    
    public static function InserPDFile($data)
    {
        $id = DB::table('user_access_pd_file')->insert($data);
    }

    /*
     * Date: 20-03-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will update the selected Access Request
     */
    public static function UpdateAccessRequest($data)
    {
        DB::table('user_access_request')->where('request_id', $data['request_id'])->update($data);
        
        $results = (array) DB::table('user_access_request')->select(DB::raw('*'))->where('request_id', '=', $data['request_id'])->first();

        if(isset($data['request_status']) && ($data['request_status']=='N' || $data['request_status']=='D'))
        {
            $results['operation'] = 'D';
        }
        else
        {
          $results['operation'] = 'U';  
        }
        
        DB::table('user_access_request_history')->insert($results);

    }

    /*
     * Date: 17-05-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will delete the user access request
     */
    public static function DeleteUserAccessRequest($request_id)
    {
        $results = (array) DB::table('user_access_request')->select(DB::raw('*'))->where('request_id', '=', $request_id)->first();

        $user    = Auth::user();
        $results['operation'] = 'D';
        $results['updated_ip']= ip2long($_SERVER['REMOTE_ADDR']);
        $results['updated_on']= time();
        $results['updated_by']= $user->id;
        
        DB::table('user_access_request_history')->insert($results);

        DB::table('user_access_request')->where('request_id', $request_id)->delete();

    }
    /*
     * Date: 20-03-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will return the access request by field
     */
    public static function GetAccessRequestByField($field,$value)
    {

        $access = DB::table('user_access_request')->where($field, $value)->first();
        return $access;
    }

    /*
     * Date: 16-04-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will return the access request by field
     */
    public static function GetAccessRequestPDFiles($request_id)
    {
        $access = DB::table('user_access_pd_file')->where('request_id','=', $request_id)->get();
        return $access;
    }


}
