<?php

namespace App\Models\UserAccess;

use DB;
use Illuminate\Database\Eloquent\Model;

class UserAccess extends Model
{
	
	public $table      = 'user_access_info';
    public $timestamps = false;
	private static $uc_type = array(111=>'trail',222=>'trail',888=>'trail',333=>'corporate',444=>'token',555=>'token',666=>'token',777=>'corporate');
    /*
     * Date: 08-04-2019
     * Author: Arsal Akhter
     * Parameter: table name, field name (PK), content id (PK) of Access Table
     * Description:  this function will delete User Access on Selected Access Table
     */

    public static function GetUserContentAccessById($table, $where, $content_access_range)
    {
        $content_access_list = DB::table($table);
        $content_access_list = $content_access_list->whereIn($where, $content_access_range);
        $content_access_list = $content_access_list->get();
        return $content_access_list;
    }


    /*
     * Date: 22-04-2019
     * Author: Arsal Akhter
     * Parameter: (1) volume_id, journal_id, content_type_id 
				  (2) volume, journal, year, journal_access 
     * Description:  this function will return Issues with Content Access and Issue without content Access (depending on condition content_type_id)
     */

	public static function GetConditionalVolumes($param, $join = null)
    {
        $volume = DB::table('volume as v');
		$volume = $volume->join('year as y', 'y.id', '=', 'v.year_id');
		
		$volume = $volume->addSelect('v.volume_id','v.volume_name','y.year');
		
		if (!empty($join['journal'])) 
		{
			$volume = $volume->join('journal as j', 'j.journal_id', '=', 'v.journal_id');
			$volume = $volume->addSelect('j.journal_id','j.subtitle','j.title');
		}
		
		// Get Issue with Content Access OR Issue without Content Access 
		//==============================================================
		if (!empty($join['journal_access']))
		{
			$volume = $volume->leftjoin('journal_access as ja', function ($join)
			{
				$join->on('ja.content_type_id', '=', 'v.volume_id')
				->whereRaw('((ja.from_date <= CURDATE() and ja.to_date >= CURDATE() and ja.perpetual= 0) or (ja.from_date is null and ja.to_date is null and ja.perpetual= 1)) and ja.content_type="v" ');
				
			});
			
			// Issue without Content Access 
			if (!empty($param['content_type_id'])) 
			{
				if ($param['content_type_id'] == 'null')
				{
					$volume = $volume->where('ja.content_type_id',null);
				}
			}
		}
		//==============================================================
		
		if (!empty($param['journal_id'])) 
		{
            $volume = $volume->where('v.journal_id', '=', $param['journal_id']);
        }
		
		// if (!empty($param)) 
		// {
			// $volume_access_list = $volume_access_list->groupBy('v.volume_id');
			// $volume_access_list = $volume_access_list->orderByRaw('j.journal_id , cast(v.volume_name  as signed) asc');
        // }
		
		if (isset($param['group']) && $param['group'] == true) 
		{
			if (isset($param['order']) && $param['order'] == true)
			{
				$volume = $volume->orderByRaw('j.subtitle asc');
			}
			$volume = $volume->orderByRaw('cast(v.volume_name as signed) desc');
			
			$viewList = DB::table(DB::raw('(' . $volume->toSql() . ') as tmp '))
			->mergeBindings($volume)
			->selectRaw('*,group_concat(concat(volume_id,"-",volume_name,"-",year) ORDER BY cast(volume_name as signed) ASC) AS volume_range ')
			->groupBy('tmp.journal_id')
			->orderBy('tmp.subtitle')
			->get();
			return $viewList;
			// $volume = $volume->selectRaw('j.journal_id, j.subtitle');
			// $volume = $volume->selectRaw('group_concat(concat(v.volume_id,"-",v.volume_name,"-",y.year) ORDER BY cast(v.volume_name as signed) ASC) AS volume_range');		
		}
		else
		{
			$volume = $volume->select('v.volume_id','v.volume_name');	
		
			if (isset($param['order']) && $param['order'] == true)
			{
				$volume = $volume->orderByRaw('j.subtitle asc');
			}
			
			//$volume = $volume->orderByRaw('cast(v.volume_name as signed) desc');
			$volume = $volume->get();
			return $volume;
		}
		

        
    }
	
	 /*
     * Date: 22-04-2019
     * Author: Arsal Akhter
     * Parameter: (1) volume_id, journal_id, content_type_id 
				  (2) volume, journal, year, journal_access 
     * Description:  this function will return Issues with Content Access and Issue without content Access (depending on condition content_type_id)
     */

	public static function GetConditionalEbookVolumes($param, $join = null)
    {
        $volume = DB::table('ebook_volume as v');
		$volume = $volume->join('ebook as e', 'e.ebook_id', '=', 'v.ebook_id');
		$volume = $volume->join('year as y', 'y.id', '=', 'v.year_id');
		
		$volume = $volume->addSelect('v.ebook_volume_id','v.volume_name','y.year','v.eisbn');
		
		if (!empty($join['ebook'])) 
		{
			$volume = $volume->addSelect('e.ebook_id','e.title');
		}
		
		// Get Issue with Content Access OR Issue without Content Access 
		//==============================================================
		if (!empty($join['ebook_access']))
		{
			$volume = $volume->leftjoin('ebook_access as ja', function ($join)
			{
				$join->on('ja.content_type_id', '=', 'v.ebook_volume_id')
				->whereRaw('((ja.from_date <= CURDATE() and ja.to_date >= CURDATE() and ja.perpetual= 0) or (ja.from_date is null and ja.to_date is null and ja.perpetual= 1))')
				->where('ja.content_type','v');
				
			});
			
			// Issue without Content Access 
			if (!empty($param['content_type_id'])) 
			{
				if ($param['content_type_id'] == 'null')
				{
					$volume = $volume->where('ja.content_type_id',null);
				}
			}
		}
		//==============================================================
		
		if (isset($param['group']) && $param['group'] == true) 
		{
			$volume = $volume->orderByRaw('e.title asc');
			$volume = $volume->orderByRaw('cast(v.volume_name as signed) desc');
			
			$viewList = DB::table(DB::raw('(' . $volume->toSql() . ') as tmp '))
			->mergeBindings($volume)
			->selectRaw('*,group_concat(concat(ebook_volume_id,"-",volume_name,"-",year) ORDER BY cast(volume_name as signed) ASC) AS volume_range ')
			->groupBy('tmp.ebook_volume_id')
			->orderByRaw('tmp.title asc')
			->orderByRaw('cast(tmp.volume_name as signed) desc')
			->get();
			return $viewList;
		}
		else
		{
			$volume = $volume->select('v.ebook_volume_id','e.title');		
			$volume = $volume->get();
			return $volume;
		}
		

        
    }
	
	public static function GetConditionalIssues($param, $join = null)
    {
		
		$issue = DB::table('issue')->select('issue.issue_id','issue.title as title');
		
		if (!empty($join['volume'])) 
		{
			$issue = $issue->join('volume', 'volume.volume_id', '=', 'issue.volume_id');
			$issue = $issue->addSelect('volume.volume_id as volume_id','volume.volume_name');
		}
		
		if (!empty($join['journal'])) 
		{
			$issue = $issue->join('journal', 'journal.journal_id', '=', 'issue.journal_id');
			$issue = $issue->addSelect('journal.journal_id as journal_id','journal.subtitle');
		}
		
		if (!empty($join['year'])) 
		{
			$issue = $issue->join('year', 'year.id', '=', 'volume.year_id');
			$issue = $issue->addSelect('year.year');
		}
	
		// Get Issue with Content Access OR Issue without Content Access 
		//==============================================================
		if (!empty($join['journal_access']))
		{
			$issue = $issue->leftjoin('journal_access as ja', function ($join) 
			{
				$join->on('ja.content_type_id', '=', 'issue.issue_id')
				->whereRaw('((ja.from_date <= CURDATE() and ja.to_date >= CURDATE() and ja.perpetual= 0) or (ja.from_date is null and ja.to_date is null and ja.perpetual= 1)) and ja.content_type="i" ');
				
			});
			$issue = $issue->addSelect('ja.id as journal_access_id',DB::raw('CASE ja.content_type_access_id WHEN 1 THEN "(Free Access)" WHEN 2 THEN "(Open Access)" ELSE "" END as content_status'));
			
			// Issue without Content Access 
			if (!empty($param['content_type_id'])) 
			{
				if ($param['content_type_id'] == 'null')
				{
					$issue = $issue->where('ja.content_type_id',null);
				}
			}
		}
		//==============================================================

        if (!empty($param['volume_id'])) 
		{
            $issue = $issue->where('issue.volume_id', '=', $param['volume_id']);
        }

        if (!empty($param['journal_id'])) 
		{
            $issue = $issue->where('issue.journal_id', '=', $param['journal_id']);
        }
		

		if (!empty($param['ASC'])) 
		{
			$issue = $issue->orderByRaw('cast(issue.title  as signed) asc');
        }
		else
		{
			$issue = $issue->orderByRaw('cast(issue.title  as signed) desc');
		}
		
		
        $issue = $issue->get();

        if ($issue->isEmpty()) {
            return null;
        } else {
            return $issue;
        }

    }
	
	
	public static function GetArticleWithOutContentAccess($param = null)
    {        
        $article = DB::table('article as a')

		->join('issue as i', 'i.issue_id', '=', 'a.issue_id')
		->join('volume as v', 'v.volume_id', '=', 'i.volume_id')
        ->join('journal as j', 'j.journal_id', '=', 'v.journal_id')
		
		->leftjoin('journal_access as ja', 'ja.content_type_id', '=', 'a.article_id')
        
        ->select(
					'ja.content_type_id as content_type_id',
					'ja.content_type_access_id as content_type_access_id',
					'j.journal_id as journal_id',
					'v.volume_id as volume_id',
					'i.issue_id as issue_id',
					'a.article_id as article_id',
                    'j.subtitle as subtitle',
                    'v.volume_name as volume_name',
					'i.title as issue_title',
					'a.title as article_title'
                );

        if (!empty($param['journal_id']))
        {
            $journal_id = $param['journal_id'];
            $article = $article->where('j.journal_id', '=', $journal_id);
        }

        if (!empty($param['volume_id']))
        {
            $volume_id = $param['volume_id'];
            $article = $article->where('v.volume_id', '=', $volume_id);
        }

        if (!empty($param['issue_id']))
        {
            $issue_id = $param['issue_id'];
            $article = $article->where('i.issue_id', '=', $issue_id);
        }
		
		// Articles without Content Access 
		if (!empty($param['content_type_id'])) 
		{
			if ($param['content_type_id'] == 'null')
			{
				$article = $article->where('ja.content_type_id',null);
			}
		}
		
        $article = $article->orderByRaw('a.title asc');
        $article = $article->orderByRaw('cast(a.title  as signed) desc');
        $article = $article->get();
      
        return $article;
    }
	
	public static function GetChapterWithOutContentAccess($param = null)
    {        
        $chapter = DB::table('chapter as a')

		->join('ebook_volume as v', 'v.ebook_volume_id', '=', 'a.ebook_volume_id')
        ->join('ebook as j', 'j.ebook_id', '=', 'v.ebook_id')
		
		->leftjoin('ebook_access as ja', 'ja.content_type_id', '=', 'a.ebook_volume_id')
        
        ->select(
					'ja.content_type_id as content_type_id',
					'ja.content_type_access_id as content_type_access_id',
					'j.ebook_id as ebook_id',
					'v.ebook_volume_id as ebook_volume_id',
					'a.chapter_id as chapter_id',
                    'j.title as title',
                    'v.volume_name as volume_name',
					'a.title as chapter_title'
                );

        if (!empty($param['ebook_id']))
        {
            $ebook_id = $param['ebook_id'];
            $chapter = $chapter->where('j.ebook_id', '=', $ebook_id);
        }

        if (!empty($param['ebook_volume_id']))
        {
            $ebook_volume_id = $param['ebook_volume_id'];
            $chapter = $chapter->where('v.ebook_volume_id', '=', $ebook_volume_id);
        }

		// Articles without Content Access 
		if (!empty($param['content_type_id'])) 
		{
			if ($param['content_type_id'] == 'null')
			{
				$chapter = $chapter->where('ja.content_type_id',null);
			}
		}
		
		if (isset($param['group']) && $param['group'] == true) 
		{
			$chapter = $chapter->groupBy('a.chapter_id');
		}
		
		
        $chapter = $chapter->orderByRaw('a.title asc');
        $chapter = $chapter->orderByRaw('cast(a.title  as signed) desc');
        $chapter = $chapter->get();
      
        return $chapter;
    }
	
	/*
     * Date: 5-11-2019
     * Author: Arsal Akhter
     * Parameter: param
     * Description:  this function will return the required Active Access data
     */
    public static function GetUserAccess($param = null, $join = null, $limit = null, $group = null, $order = null)
    {
        
        $org_list = DB::table('user_access_info as acs');
		
		if (!empty($join['users']))
		{
			$org_list = $org_list->join('users as u', 'u.id', '=', 'acs.user_id');
			
			if (!empty($param['user_id'])) // users
			{
				$org_list = $org_list->where('u.id', '=', $param['user_id']);
			}
			if (!empty($param['user_name'])) // users
			{
				$org_list = $org_list->where('u.name', '=', $param['user_name']);
			}
			
			$org_list = $org_list->addSelect('u.id as user_id','u.name','u.email','u.first_name','u.last_name','u.org_branch','u.display_name as display_name'); // users
		}
		
		if (!empty($join['access_type']))
		{
			$org_list = $org_list->join('access_types as type', 'type.access_type_id', '=', 'acs.access_type_id');
			$org_list = $org_list->addSelect('type.access_type_title');
		}
		
		if (!empty($join['user_access_organizations']))
		{
			$org_list = $org_list->join('user_access_organizations as org', 'org.access_organization_id', '=', 'u.access_organization_id');
		}
        
        if (!empty($param['user_access_id'])) // user_access_info
        {
            $org_list = $org_list->where('acs.user_access_id', '=', $param['user_access_id']);
        }
        if (!empty($param['access_org_id'])) // user_access_info
        {
            $org_list = $org_list->where('acs.access_org_id', '=', $param['access_org_id']);
        }
		if (!empty($param['user_id'])) // user_access_info
        {
            $org_list = $org_list->where('acs.user_id', '=', $param['user_id']);
        }
		if (!empty($param['access_type_id'])) // user_access_info
        {
            $org_list = $org_list->where('acs.access_type_id', '=', $param['access_type_id']);
        }
		if (!empty($param['access_type_on'])) // user_access_info
        {
            $org_list = $org_list->where('acs.access_type_on', '=', $param['access_type_on']);
        }
		if (!empty($param['access_base_type'])) // user_access_info
        {
            $org_list = $org_list->where('acs.access_base_type', '=', $param['access_base_type']);
        }
		if (!empty($param['access_base_type'])) // user_access_info
        {
            $org_list = $org_list->where('acs.access_base_type', '=', $param['access_base_type']);
        }
		if (!empty($param['user_access_status'])) // user_access_info
        {
            $org_list = $org_list->where('acs.user_access_status', '=', $param['user_access_status']);
        }
		
		if(!empty($group))
        {
			if (isset($group['access_type_id']))
			{
				$org_list = $org_list->groupBy('access_type_id');	
			}
			if (isset($group['access_type_on']))
			{
				$org_list = $org_list->groupBy('access_type_on');	
			}
        }
		
		if(!empty($order))
        {
			if (isset($order['user_access_id']))
			{
				$org_list = $org_list->orderByRaw('cast(acs.user_access_id as signed) desc');
			}
        }
		
		$org_list = $org_list->addSelect('acs.*'); // user access info
		
		if(!empty($limit))
		{
			if ($limit['multiple'] == true)
			{
				return $org_list->get(); 	
			}
		}
		else
		{
			return $org_list->first(); 	
		}
		
        
    }    

    
	/*
     * Date: 19-04-2019
     * Author: Imran Khan
     * Parameter: param
     * Description:  this function will return the required Active Access data
     */
    public static function GetUserAccessInfo($param = null)
    {
        
        $org_list = DB::table('user_access_info as acs');
        $org_list = $org_list->join('users as u', 'u.id', '=', 'acs.user_id');
        $org_list = $org_list->join('user_access_organizations as org', 'org.access_organization_id', '=', 'u.access_organization_id');
        $org_list = $org_list->join('access_types as type', 'type.access_type_id', '=', 'acs.access_type_id');
        
        $org_list = $org_list->select('acs.*'); // user_access_info
        $org_list = $org_list->addSelect('u.id as user_id','u.name','u.email','u.first_name','u.last_name','u.org_branch','u.display_name as display_name'); // users
        $org_list = $org_list->addSelect('org.organization_name','org.organization_website'); // user_access_organizations
        $org_list = $org_list->addSelect('type.access_type_title','org.organization_website'); // access_types
        
        if (!empty($param['user_access_id'])) // user_access_info
        {
            $org_list = $org_list->where('acs.user_access_id', '=', $param['user_access_id']);
        }
         
        if (!empty($param['access_organization_id'])) // user_access_info
        {
            $org_list = $org_list->where('acs.access_org_id', '=', $param['access_organization_id']);
        }
        
        if (!empty($param['user_id'])) // user_access_info
        {
            $org_list = $org_list->where('acs.user_id', '=', $param['user_id']);
        }
            $org_list = $org_list->whereIn('acs.user_access_status', [0, 1]);
            $org_list = $org_list->first(); 
            return $org_list;   
    }    


   

	
	 /*
     * Date: 08-29-2019
     * Author: Arsal Akhter
     * Parameter: 
     * Description:  this function will insert Article Access Denail 
     */

    public static function InsertArticleAccessDenail($access_denail_data)
    {
		
		$denail = DB::table('access_denied_report');
		$denail = $denail->where('userid',$access_denail_data['userid']);
		$denail = $denail->where('aid',$access_denail_data['aid']);
		$denail = $denail->whereRaw('(unix_timestamp(CURRENT_TIMESTAMP())-created_on) <= 40 ');
		$denail = $denail->count();
		
		if ($denail == 0)
		{
			DB::table('access_denied_report')->insert($access_denail_data);	
		}
        
    }
	
	 /*
     * Date: 08-29-2019
     * Author: Arsal Akhter
     * Parameter: 
     * Description:  this function will insert Chapter Access Denail 
     */

    public static function InsertChapterAccessDenail($access_denail_data)
    {
		
		$denail = DB::table('access_denied_chapter_report');
		$denail = $denail->where('user_id',$access_denail_data['user_id']);
		$denail = $denail->where('c_id',$access_denail_data['c_id']);
		$denail = $denail->whereRaw('(unix_timestamp(CURRENT_TIMESTAMP())-created_on) <= 40 ');
		$denail = $denail->count();
		
		if ($denail == 0)
		{
			DB::table('access_denied_chapter_report')->insert($access_denail_data);	
		}
        
    }
	
	/*
     * Date: 08-29-2019
     * Author: Arsal Akhter
     * Parameter: 
     * Description:  this function will insert Ebook Volume Access Denail 
     */

    public static function InsertEbookAccessDenail($access_denail_data)
    {
		
		$denail = DB::table('access_denied_ebook_report');
		$denail = $denail->where('user_id',$access_denail_data['user_id']);
		$denail = $denail->where('ev_id',$access_denail_data['ev_id']);
		$denail = $denail->whereRaw('(unix_timestamp(CURRENT_TIMESTAMP())-created_on) <= 40 ');
		$denail = $denail->count();
		
		if ($denail == 0)
		{
			DB::table('access_denied_ebook_report')->insert($access_denail_data);	
		}
        
    }

    /*
     * Date: 02-04-2019
     * Author: Arsal Akhter
     * Parameter: user_access_id
     * Description:  this function will update User Access data by PK Id
     */

    public static function updateUserAccessInfo($user_access_id, $data)
    {
        DB::table('user_access_info')->where('user_access_id', $user_access_id)->update($data);

        $results = (array) DB::table('user_access_info')->select(DB::raw('*'))->where('user_access_id', '=', $user_access_id)->first();
        $results['operation'] = 'U';

        DB::table('user_access_info_history')->insert($results);

    }
	

	/*
     * Date: 19-11-2019
     * Author: Arsal Akhter
     * Parameter: access_type_id, access_type_on, user_id
     * Description:  this function will update User Access Date od Selected User 
     */

    public static function UpdateUserAccessDate($param)
    {
		$access_type = static::$uc_type[$param['access_type_id']];
		
		$tbl = 'user_access_'.$param['access_on'].'_'.$access_type;

        $uc_date = DB::table($tbl.' as uac')->addSelect('*');
		
		$uc_date = $uc_date->where('user_id',$param['user_id']);
		$uc_date = $uc_date->where('type',$param['access_type_id']);
		
		if($param['access_base_type'] == 'ip_base_access')
		{
			$uc_date = $uc_date->whereRaw('from_ip is not null');
			$uc_date = $uc_date->whereRaw('to_ip is not null');
		}
		if($param['access_base_type'] == 'user_base_access')
		{
			$uc_date = $uc_date->where('from_ip',null);
			$uc_date = $uc_date->where('to_ip',null);
		}
		
		$uc_date = $uc_date->where('perpetual',0);
		
		$uc_date = $uc_date->SelectRaw("'".$param['sys_h_user_id']."' as sys_h_user_id ");
		$uc_date = $uc_date->SelectRaw("'".$param['sys_h_ip']."' as sys_h_ip ");
		$uc_date = $uc_date->SelectRaw("'".$param['sys_h_time']."' as sys_h_time ");
		
		// Get all Columns of table
		$cols = DB::getSchemaBuilder()->getColumnListing($tbl);
		$cols[] = 'sys_h_user_id';
		$cols[] = 'sys_h_ip';
		$cols[] = 'sys_h_time';
		
		// Insert All Selected Content into history
		DB::table($tbl .'_history')->insertUsing($cols, $uc_date);
		
		$uc_date = $uc_date->update(array('from_date' => strtotime($param['from_date'].' 00:00:00'),'to_date' => strtotime($param['to_date'].' 23:59:59')));
		
		if($param['access_base_type'] == 'ip_base_access')
		{
			$param['access_base_type'] = 1;
		}
		elseif($param['access_base_type'] == 'user_base_access')
		{
			$param['access_base_type'] = 0;
		}
		$tbl_cols = 'user_access_id, access_org_id, user_id, access_type_id, access_type_on, access_base_type, access_date_from, access_date_to, perpetual, restricted_download_limit, remarks, ip_data, content_data, user_access_status, system_ip, system_user_id, system_time, created_on, created_by';
		$h_tbl_cols = $tbl_cols.',operation';
		$tbl_cols = $tbl_cols.' , "U" as operation ';
		
        DB::select("Update user_access_info set access_date_from = ".strtotime($param['from_date'].' 00:00:00').", access_date_to = ".strtotime($param['to_date'].' 23:59:59')." where user_access_id = ".$param['user_access_id']."");

		DB::select('insert into user_access_info_history ('.$h_tbl_cols.') select '.$tbl_cols.' from user_access_info where user_id = '.$param['user_id'].' and access_type_id = '.$param['access_type_id'].' and access_base_type = '.$param['access_base_type'].'  and perpetual = 0 ');
		
		$tbl_ua_req_info_val = $param['request_id'].','.$param['user_access_id'].','.'"U"'.','.$param['sys_h_user_id'].','.$param['sys_h_time'].','.$param['sys_h_ip'];
		DB::select('insert into user_access_request_info (request_id, user_access_id, action, sys_user_id, sys_time, sys_ip) values ('.$tbl_ua_req_info_val.')');
    }


    /*
     * Date: 15-04-2019
     * Author: Muhammad Arshad
     * Parameter: IP address
     * Description:  this function will check the ip_address is exist in ip_rang table and user_access_info table whether access is enable/disable
     */

    public static function CheckUserAccessByIP($ip_address)
    {	

		$t1 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_journal_volume_trail WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t2 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_journal_volume_corporate WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t3 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_journal_volume_token WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t4 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_ebook_volume_trail WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t5 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_ebook_volume_corporate WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t6 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_ebook_volume_token WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t7 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_issue_trail WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t8 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_issue_corporate WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t9 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_issue_token WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t10 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_article_trail WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t11 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_article_corporate WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t12 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_article_token WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t13 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_chapter_trail WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t14 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_chapter_corporate WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t15 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_chapter_token WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$t16 = DB::query()->select('user_id','to_ip','from_ip')->fromSub(function ($query) use ($ip_address) 
		{
			$query->selectRaw('user_id, to_ip, from_ip FROM user_access_disease_corporate WHERE to_ip >= '.$ip_address.' ORDER BY to_ip ASC LIMIT 1');
		}, 'tjv')->where('from_ip', '<=' , $ip_address);

		$query = $t1->union($t2)->union($t3)->union($t4)->union($t5)->union($t6)->union($t7)->union($t8)->union($t9)->union($t10)->union($t11)->union($t12)->union($t13)->union($t14)->union($t15)->union($t16);
		$query = $query->first();
		
		if (!empty($query->user_id))
		{
			$query = 'select * from user_access_info where user_id = '.$query->user_id.' limit 1 ';
			return DB::select($query);
		}
		else
		{
			return false;	
		}

    }

    /*
     * Date: 16-04-2019
     * Author: Muhammad Arshad
     * Parameter: IP address
     * Description:  this function will check the user_id is exist in access info table and cehck whether access is enable/disable
     */

    public static function CheckUserAccessByUser($user_id)
    {

        $user_access = DB::table('user_access_info as ua')
            ->select('*')
            ->where('ua.user_id', '=', $user_id)
            ->whereRaw('
                    (
                      (ua.access_date_from <= UNIX_TIMESTAMP(now()) and ua.access_date_to >= UNIX_TIMESTAMP(now()) and ua.perpetual= 0) or
                      (ua.access_date_from is null and ua.access_date_to is null and ua.perpetual= 1)

                    )')
            ->where('ua.access_base_type', '=', 0) //User Base
            ->where('ua.user_access_status', '=', 1) //is active
            ->get()->toArray();
        return $user_access;

    }

    /*
     * Date: 08-11-2021
     * Author: Imran Khan
     * Parameter: User ID
     * Description:  this function will check the user_id is exist in access info table and cehck whether access is enable/disable
     */

    public static function CheckUserAccess($user_id)
    {

        $user_access = DB::table('user_access_info as ua')
            ->select('*')
            ->where('ua.user_id', '=', $user_id)
            ->whereRaw('
                    (
                      (ua.access_date_from <= UNIX_TIMESTAMP(now()) and ua.access_date_to >= UNIX_TIMESTAMP(now()) and ua.perpetual= 0) or
                      (ua.access_date_from is null and ua.access_date_to is null and ua.perpetual= 1)

                    )')
            ->where('ua.user_access_status', '=', 1) //is active
            ->get()->toArray();
        return $user_access;

    }    

    /*
     * Date: 15-04-2019
     * Author: Muhammad Arshad
     * Parameter: Param
     * Description:  this function will return an array of access type
     */

    // public static function getUserAccessData($param)
    // {
        // $user_access_id   = $param['session_data']->user_access_id;
        // $user_id          = $param['session_data']->user_id;
        // $access_type      = $param['session_data']->access_type_id;
        // $access_base_type = $param['session_data']->access_base_type;
        // $access_date_from = $param['session_data']->access_date_from;
        // $access_date_to   = $param['session_data']->access_date_to;
        // $perpetual        = $param['session_data']->perpetual;
        // $access_type_on   = explode(",", $param['session_data']->access_type_on);
        // $return['access_base_text'] = '';

        // if ($param['session_data']->access_type_id == 333) //corporate
        // {
            // $return['access_base_text'] = 'Subscribed';
            // $return['terms_popup']      = false;
            // $return['content_download_type'] = 'S';
            // $return['restricted_user_access_key'] = null;
            // $return['restricted_user_access_type'] = null;
        // }

        // if ($param['session_data']->access_type_id == 111 || $param['session_data']->access_type_id == 222) //trial
        // {
            // $return['access_base_text'] = 'Trial Access';
            // $return['terms_popup']      = true;
            // $return['content_download_type'] = 'S';
            // $return['restricted_user_access_key'] = null;
            // $return['restricted_user_access_type'] = null;
        // }

        // if ($param['session_data']->access_type_id == 444 || $param['session_data']->access_type_id == 555 || $param['session_data']->access_type_id == 666) //restricted Token
        // {
            // $return['access_base_text'] = 'Token Based Access';
            // $return['terms_popup']      = false;
            // $return['content_download_type'] = 'R';
            // $return['restricted_user_access_key'] = $user_access_id;
            // $return['restricted_user_access_type'] = $param['check_access_on'];
        // }


        // $return['DownloadAccess']        = false;
        // $return['free']                  = false;        
        // $return['tracksAccessByVolume']  = false;
        // $return['access_data']           = '';

        // //if (in_array($param['check_access_on'], $access_type_on)) {
           
            // $access_type_on = rtrim($param['check_access_on'],',');
			
            // $access_type_on   = explode(",", $access_type_on);
            // rsort($access_type_on);
            // for($k=0;$k<count($access_type_on);$k++){
                
            // switch ($access_type_on[$k]) {
                // case 'v': // journal volume
                    // if (isset($param['journal_id'])) {
						
                        // $access_data = UserAccess::CheckVolumeUserAccess($param);
                        // session(['access_data' => $access_data]);
                    // }

                    // if (!empty($access_data)) {
                        // if ($access_data[0]->tracks > 0) {
                            // $return['tracksAccessByVolume'] = true;
                        // } else {
                            // $return['tracksAccessByVolume'] = false;
                        // }
                        // $return['DownloadAccess']        = true;
                        // $return['free']                  = false;
                        // //$return['content_download_type'] = 'S';
                        // $return['access_data']           = $access_data;                        
                    // }

                    // break;
                // case 'i': // issue
                    // if (isset($param['journal_id'])) {
                        // $access_data = UserAccess::CheckIssueUserAccess($param);
                    // }
                    // if (!empty($access_data)) {
                        // $return['DownloadAccess']        = true;
                        // $return['free']                  = false;
                        // //$return['content_download_type'] = 'S';
                        // $return['access_data']           = $access_data;
                    // }
                    // break;
                // case 'a': // article
                    // if (isset($param['journal_id'])) {
                        // $access_data = UserAccess::CheckArticleUserAccess($param);
                    // }
                    // if (!empty($access_data)) {
                        // $return['DownloadAccess']        = true;
                        // $return['free']                  = false;
                        // //$return['content_download_type'] = 'S';
                        // $return['access_data']           = $access_data;
                    // }
                    // break;
                // case 'e': // ebook volume
                    // if (isset($param['ebook_id'])) {
                        // $access_data = UserAccess::CheckEbookVolumeUserAccess($param);
                                                
                    // }                    
                    // if (!empty($access_data)) {
                        // $return['DownloadAccess']        = true;
                        // $return['free']                  = false;
                        // //$return['content_download_type'] = 'S';
                        // $return['access_data']           = $access_data;
                    // }
                    // break;
                // case 'c': // chapter
                    // if (isset($param['chapter_id'])) {
                        // $access_data = UserAccess::CheckChapterUserAccess($param);
                    // }

                    // if (!empty($access_data)) {
                        // $return['DownloadAccess']        = true;
                        // $return['free']                  = false;
                        // //$return['content_download_type'] = 'S';
                        // $return['access_data']           = $access_data;
                    // }
                    // break;
                // default:
                    // $return['DownloadAccess']        = false;
                    // $return['free']                  = false;
                    // $return['content_download_type'] = '';
                    // $return['access_data']           = '';
                    // $return['access_base_text']      = '';
                    // $return['terms_popup']           = true;
                    // break;
            // }
        // //}
        // }
        
        // return $return;

    // }

    /*
     * Date: 28-08-2019
     * Author: Imran
     * Parameter: Param
     * Description:  this function will return an array of rental access
     */

    public static function getUserRentalAccessData($param)
    {

        $user_access_id   = $param['session_data']->user_access_id;
        $user_id          = $param['session_data']->user_id;
        $access_type      = $param['session_data']->access_type_id;
        $access_base_type = $param['session_data']->access_base_type;
        $access_date_from = $param['session_data']->access_date_from;
        $access_date_to   = $param['session_data']->access_date_to;
        $perpetual        = $param['session_data']->perpetual;
        $access_type_on   = explode(",", $param['session_data']->access_type_on);
        $return['access_base_text'] = '';

        if ($param['session_data']->access_type_id == 777) //Rental Access
        {
            $return['access_base_text'] = 'Rental';
            $return['terms_popup']      = false;
            $return['content_download_type'] = '';
            $return['restricted_user_access_key'] = null;
            $return['restricted_user_access_type'] = null;
        }

        $return['DownloadAccess']        = false;
        $return['free']                  = false;        
        $return['tracksAccessByVolume']  = false;
        $return['access_data']           = '';



        //if (in_array($param['check_access_on'], $access_type_on)) {
           
            $access_type_on = rtrim($param['check_access_on'],',');
            
            $access_type_on   = explode(",", $access_type_on);
            rsort($access_type_on);
            for($k=0;$k<count($access_type_on);$k++){
                
            switch ($access_type_on[$k]) {

                case 'a': // article
                    if (isset($param['journal_id'])) {
                        $access_data = UserAccess::CheckArticleUserAccess($param);
                    }
                    if (!empty($access_data)) {
                        $return['DownloadAccess']        = true;
                        $return['free']                  = false;
                        //$return['content_download_type'] = 'S';
                        $return['access_data']           = $access_data;
                    }
                    break;

                default:
                    $return['DownloadAccess']        = false;
                    $return['free']                  = false;
                    $return['content_download_type'] = '';
                    $return['access_data']           = '';
                    $return['access_base_text']      = '';
                    $return['terms_popup']           = true;
                    break;
            }
        //}
        }
        
        return $return;

    }
    /*
     * Date: 15-04-2019
     * Author: Muhammad Arshad
     * Parameter: Param
     * Description:  this function will check the access in journal volume table
     */

    // public static function CheckVolumeUserAccess($param)
    // {

        // $user_access = DB::table('user_access_journal_volume as v')
            // ->select('*')
            // ->where('v.user_access_id', '=', $param['session_data']->user_access_id)
            // ->where('v.user_id', '=', $param['session_data']->user_id)
            // ->where('v.journal_id', '=', $param['journal_id'])
            // ->where('v.volume_name_from', '<=', $param['volume_name'])
            // ->where('v.volume_name_to', '>=', $param['volume_name'])
            // ->get()->toArray();
        // return $user_access;
    // }
	
	/*
     * Date: 27-03-2019
     * Author: Arsal Akhter
     * Parameter: Param
     * Description:  this function will return the volume list journal wise
     */
    public static function GetAllContentUserAccess($param,$join = null)
    {	
		$ip = $param['ip_address'];
		$id = $param['user_id'];
		
		$current_date = time();
		
		$where = ' ((from_ip <= '.ip2long($ip).' and to_ip >='.ip2long($ip).' )'; 
		
		if (!empty($param['user_id']))
		{
			$where .= ' or ( from_ip is null and to_ip is null and user_id = '.$id.') or
                            (user_id = '.$param['user_id'].') ';
		}
		
		$where .= ') and ((from_date <= '.$current_date.' and to_date >= '.$current_date.' and perpetual= 0) or (from_date is null and to_date is null and perpetual= 1))';
		
		if (!empty($param['journal_id']))
		{
			$where .= ' and journal_id = '.$param['journal_id'];
		}
		elseif (!empty($param['volume_id'])  && empty($join['disease']) )
		{
			$where .= ' and volume_id = '.$param['volume_id'];
		}
		elseif (!empty($param['issue_id']))
		{
			$where .= ' and issue_id = '.$param['issue_id'];
		}
		elseif (!empty($param['article_id']) && empty($join['disease']))
		{
			$where .= ' and article_id = '.$param['article_id'];
		}
		elseif (!empty($param['chapter_id']) && empty($join['disease']))
		{
			$where .= ' and chapter_id = '.$param['chapter_id'];
		}
		
		//$s1 = $s2 = $s3 = '';
		
		$s1 = '"Trial Access" as access_base_text,
		CASE type WHEN 111 THEN true ELSE false END as terms_popup,
		"S" as content_download_type, 
		null as restricted_user_access_key, 
		null as restricted_user_access_type,
		CASE track WHEN 0 THEN false ELSE true END as tracksAccessByVolume,';
		
		$s2 = '"Subscribed" as access_base_text, 
		false as terms_popup, 
		"S" as content_download_type, 
		null as restricted_user_access_key, 
		null as restricted_user_access_type,
		CASE track WHEN 0 THEN false ELSE true END as tracksAccessByVolume,';
		
		$s3 = '"Token Based Access" as access_base_text, 
		false as terms_popup, 
		"R" as content_download_type, 
		uc_id as restricted_user_access_key, 
		"v" as restricted_user_access_type,
		CASE track WHEN 0 THEN false ELSE true END as tracksAccessByVolume,';
	
		
		if(empty($join['disease']))
		{
			$select = 'user_access_id,volume_id,user_id,from_ip,to_ip,type,track';
			
			$access_level_volume = '"volume" as access_level,"" as issue_id,"" as article_id ';
			$access_level_ebook_volume = '"ebook_volume" as access_level,"" as chapter_id ';
			$access_level_issue = '"issue" as access_level,issue_id,"" as article_id ';
			$access_level_article = '"article" as access_level,issue_id,article_id ';
			$access_level_chapter = '"chapter" as access_level, chapter_id ';
		}
		else
		{
			$select = 'user_access_id,user_id,from_ip,to_ip,type,track';

			$access_level_article = '"article" as access_level ';
		}

		if(!empty($join['volume']))
		{
			$t1 = DB::table('user_access_journal_volume_trail')->selectRaw($select.','.$s1.$access_level_volume)->whereRaw($where);
			$t2 = DB::table('user_access_journal_volume_corporate')->selectRaw($select.','.$s2.$access_level_volume)->whereRaw($where);
			$t3 = DB::table('user_access_journal_volume_token')->selectRaw($select.','.$s3.$access_level_volume)->whereRaw($where);
			
			$uc_list = $t1->union($t2)->union($t3);
		}
		
		if(!empty($join['ebook_volume']))
		{
			$t1 = DB::table('user_access_ebook_volume_trail')->selectRaw($select.','.$s1.$access_level_ebook_volume)->whereRaw($where);
			$t2 = DB::table('user_access_ebook_volume_corporate')->selectRaw($select.','.$s2.$access_level_ebook_volume)->whereRaw($where);
			$t3 = DB::table('user_access_ebook_volume_token')->selectRaw($select.','.$s3.$access_level_ebook_volume)->whereRaw($where);
			
			$uc_list = $t1->union($t2)->union($t3);
		}
		
		if(!empty($join['issue']))
		{
			$t4 = DB::table('user_access_issue_trail')->selectRaw($select.','.$s1.$access_level_issue)->whereRaw($where);
			$t5 = DB::table('user_access_issue_corporate')->selectRaw($select.','.$s2.$access_level_issue)->whereRaw($where);
			$t6 = DB::table('user_access_issue_token')->selectRaw($select.','.$s3.$access_level_issue)->whereRaw($where);
			
			if (isset($uc_list))
			{
				$uc_list = $uc_list->union($t4)->union($t5)->union($t6);
			}
			else
			{
				$uc_list = $t4->union($t5)->union($t6);
			}
			
		}
		
		if(!empty($join['article']))
		{
			$t7 = DB::table('user_access_article_trail')->selectRaw($select.','.$s1.$access_level_article)->whereRaw($where);
			$t8 = DB::table('user_access_article_corporate')->selectRaw($select.','.$s2.$access_level_article)->whereRaw($where);
			$t9 = DB::table('user_access_article_token')->selectRaw($select.','.$s3.$access_level_article)->whereRaw($where);
			
			if (isset($uc_list))
			{
				$uc_list = $uc_list->union($t7)->union($t8)->union($t9);
			}
			else
			{
				$uc_list = $t7->union($t8)->union($t9);
			}
		}

		if(!empty($join['chapter']))
		{
			
			$t7 = DB::table('user_access_chapter_trail')->selectRaw($select.','.$s1.$access_level_chapter)->whereRaw($where);
			$t8 = DB::table('user_access_chapter_corporate')->selectRaw($select.','.$s2.$access_level_chapter)->whereRaw($where);
			$t9 = DB::table('user_access_chapter_token')->selectRaw($select.','.$s3.$access_level_chapter)->whereRaw($where);
			
			if (isset($uc_list))
			{
				$uc_list = $uc_list->union($t7)->union($t8)->union($t9);
			}
			else
			{
				$uc_list = $t7->union($t8)->union($t9);
			}
		}

		if(!empty($join['disease']))
		{
			if(isset($param['article_id']))
			{
				$content_id = $param['article_id'];
				$content_as = "article_id";
			}
			if(isset($param['chapter_id']))
			{
				$content_id = $param['chapter_id'];
				$content_as = "chapter_id";
			}
			
			$uc_list = DB::table('user_access_disease_corporate as uda')->join('content_by_diseases as cd','uda.disease_id', '=', 'cd.pid')->selectRaw($select.', cd.content_id as '.$content_as.','.$s2.$access_level_article)->whereRaw($where .= " and cd.content_type = '".$param['disease_access']."' and cd.content_id = ".$content_id." limit 1");
		}

		//$uc_list = $t1->union($t2)->union($t3)->union($t4)->union($t5)->union($t6)->union($t7)->union($t8)->union($t9);
		
		$uc_list = $uc_list->get();
		
		return $uc_list;
		
    }
	
	public static function GetUserAccessIpBase($ip_ranges,$param)
    { 
		//DB::enableQueryLog();
		$where = '';
		foreach($ip_ranges as $k => $v)
		{	
			$where .= '(( to_ip >= '.$v['from_ip'] . ') and ( from_ip <= '. $v['to_ip'] . ')) or ';
		}

		$where = rtrim($where,'or ');

		if (!empty($param['user_id']))
		{
			$where .= ' and user_id = '.$param['user_id'].'';	
		}

		//dd($where); die;
		
		if (!empty($ip_ranges))
		{
			$t1 = DB::table('user_access_'.$param['access_on'].'_trail');
			$t2 = DB::table('user_access_'.$param['access_on'].'_corporate');
			$t3 = DB::table('user_access_'.$param['access_on'].'_token');
			
			if (!empty($where))
			{
				$t1 = $t1->whereRaw($where);
				$t2 = $t2->whereRaw($where);
				$t3 = $t3->whereRaw($where);
			}
			
			if ($param['access_on'] == 'article')
			{
				$t1 = $t1->select('user_access_id','uc_id','volume_id','issue_id','article_id','user_id','from_ip','to_ip');
				$t2 = $t2->select('user_access_id','uc_id','volume_id','issue_id','article_id','user_id','from_ip','to_ip');
				$t3 = $t3->select('user_access_id','uc_id','volume_id','issue_id','article_id','user_id','from_ip','to_ip');
			}
			elseif ($param['access_on'] == 'issue')
			{
				$t1 = $t1->select('user_access_id','uc_id','volume_id','issue_id','user_id','from_ip','to_ip');
				$t2 = $t2->select('user_access_id','uc_id','volume_id','issue_id','user_id','from_ip','to_ip');
				$t3 = $t3->select('user_access_id','uc_id','volume_id','issue_id','user_id','from_ip','to_ip');
			}
			elseif ($param['access_on'] == 'journal_volume')
			{
				$t1 = $t1->select('user_access_id','uc_id','volume_id','user_id','from_ip','to_ip');
				$t2 = $t2->select('user_access_id','uc_id','volume_id','user_id','from_ip','to_ip');
				$t3 = $t3->select('user_access_id','uc_id','volume_id','user_id','from_ip','to_ip');
			}
			elseif ($param['access_on'] == 'ebook_volume')
			{
				$t1 = $t1->select('user_access_id','uc_id','volume_id','user_id','from_ip','to_ip');
				$t2 = $t2->select('user_access_id','uc_id','volume_id','user_id','from_ip','to_ip');
				$t3 = $t3->select('user_access_id','uc_id','volume_id','user_id','from_ip','to_ip');
			}
			elseif ($param['access_on'] == 'chapter')
			{
				$t1 = $t1->select('user_access_id','uc_id','volume_id','chapter_id','user_id','from_ip','to_ip');
				$t2 = $t2->select('user_access_id','uc_id','volume_id','chapter_id','user_id','from_ip','to_ip');
				$t3 = $t3->select('user_access_id','uc_id','volume_id','chapter_id','user_id','from_ip','to_ip');
			}
			elseif ($param['access_on'] == 'disease')
			{
				$t2 = $t2->select('user_access_id','uc_id','user_id','disease_id','from_ip','to_ip');
			}
			
			if($param['access_on'] == 'disease')
			{
				$bound_ips = $t2;
			}
			else
			{
				$bound_ips = $t3->union($t2)->union($t1);
			}
			
			$bound_ips = $bound_ips->orderBy('from_ip');
			
			$bound_ips = $bound_ips->get();
			
			//dd(DB::getQueryLog());
			return $bound_ips;
			
		}
	}

	public static function GetUserAccessIpBaseDelete($ip_ranges,$param)
    { 
		$where = '';
		foreach($ip_ranges as $k => $v)
		{	
			$where .= '(( uac.to_ip = '.$v['to_ip'] . ') and ( uac.from_ip = '. $v['from_ip'] . ')) or ';
		}

		$where = rtrim($where,'or ');
		$where .= ' and ui.user_id = '.$param['user_id'].' and ui.access_type_id = '.$param['access_type_id'].' and ui.user_access_id =  '.$param['user_access_id'];	

		//dd($where); die;
		
		if (!empty($ip_ranges))
		{
		
			$access_type = static::$uc_type[$param['access_type_id']];
			
			$tbl = 'user_access_'.$param['access_on'].'_'.$access_type;

        	$content = DB::table($tbl.' as uac')->addSelect('*')->join('user_access_info as ui', 'uac.user_access_id', '=', 'ui.user_access_id')->whereRaw($where);

			$content = $content->orderBy('uac.from_ip');
			
			$content = $content->get();
			
			return $content;
			
		}
	}	
	public static function GetUserAccessUserBase($param)
    {	
		$t1 = DB::table('user_access_'.$param['access_on'].'_trail');
		$t2 = DB::table('user_access_'.$param['access_on'].'_corporate');
		$t3 = DB::table('user_access_'.$param['access_on'].'_token');
		
		if (!empty($param['user_id']))
		{
			$t1 = $t1->where('user_id',$param['user_id']);
			$t2 = $t2->where('user_id',$param['user_id']);
			$t3 = $t3->where('user_id',$param['user_id']);
		}
		if ($param['access_on'] == 'article')
		{
			$t1 = $t1->select('uc_id','volume_id','issue_id','article_id','user_id');
			$t2 = $t2->select('uc_id','volume_id','issue_id','article_id','user_id');
			$t3 = $t3->select('uc_id','volume_id','issue_id','article_id','user_id');
		}
		elseif ($param['access_on'] == 'issue')
		{
			$t1 = $t1->select('uc_id','volume_id','issue_id','user_id');
			$t2 = $t2->select('uc_id','volume_id','issue_id','user_id');
			$t3 = $t3->select('uc_id','volume_id','issue_id','user_id');
		}
		elseif ($param['access_on'] == 'journal_volume')
		{
			$t1 = $t1->select('uc_id','volume_id','user_id');
			$t2 = $t2->select('uc_id','volume_id','user_id');
			$t3 = $t3->select('uc_id','volume_id','user_id');
		}
		elseif ($param['access_on'] == 'ebook_volume')
		{
			$t1 = $t1->select('uc_id','volume_id','user_id');
			$t2 = $t2->select('uc_id','volume_id','user_id');
			$t3 = $t3->select('uc_id','volume_id','user_id');
		}
		elseif ($param['access_on'] == 'chapter')
		{
			$t1 = $t1->select('uc_id','volume_id','chapter_id','user_id');
			$t2 = $t2->select('uc_id','volume_id','chapter_id','user_id');
			$t3 = $t3->select('uc_id','volume_id','chapter_id','user_id');
		}
		elseif ($param['access_on'] == 'disease')
		{
			$t2 = $t2->select('uc_id','disease_id','user_id');
		}

		if ($param['access_on'] == 'disease')
		{
			$duplicate = $t2;	
		}
		else
		{
			$duplicate = $t1->union($t2)->union($t3);
		}
		
		
		$duplicate = $duplicate->get();
		return $duplicate;
	}
	
    /*
     * Date: 15-04-2019
     * Author: Muhammad Arshad
     * Parameter: Param
     * Description:  this function will check the access in journal issue table
     */

    // public static function CheckIssueUserAccess($param)
    // {

        // $user_access = DB::table('user_access_issue as i')
            // ->select('*')
            // ->where('i.user_access_id', '=', $param['session_data']->user_access_id)
            // ->where('i.user_id', '=', $param['session_data']->user_id)
            // ->where('i.journal_id', '=', $param['journal_id'])
            // ->where('i.issue_id', '=', $param['issue_id'])
            // ->where('i.volume_id', '=', $param['volume_id'])
            // ->get()->toArray();
        // return $user_access;
    // }

    /*
     * Date: 15-04-2019
     * Author: Muhammad Arshad
     * Parameter: Param
     * Description:  this function will check the access in journal article table
     */

    // public static function CheckArticleUserAccess($param)
    // {
        // $user_access = DB::table('user_access_article as a')
            // ->select('*')
            // ->where('a.user_access_id', '=', $param['session_data']->user_access_id)
            // ->where('a.user_id', '=', $param['session_data']->user_id)
			// // ->whereRaw('a.user_id in (78,79)')
			// // ->whereRaw('a.user_access_id in (42,43)')
            // ->where('a.journal_id', '=', $param['journal_id'])
            // ->where('a.article_id', '=', $param['article_id'])
            // ->where('a.volume_id', '=', $param['volume_id'])
            // ->get()->toArray();
        // return $user_access;
    // }

    /*
     * Date: 15-04-2019
     * Author: Muhammad Arshad
     * Parameter: Param
     * Description:  this function will check the access in ebook volume table
     */

    // public static function CheckEbookVolumeUserAccess($param)
    // {

        // $user_access = DB::table('user_access_ebook_volume as v')
            // ->select('*')
            // ->where('v.user_access_id', '=', $param['session_data']->user_access_id)
            // ->where('v.user_id', '=', $param['session_data']->user_id)
            // ->where('v.ebook_id', '=', $param['ebook_id'])
            // ->where('v.ebook_volume_name_from', '<=', $param['ebook_volume_name'])
            // ->where('v.ebook_volume_name_to', '>=', $param['ebook_volume_name'])
            // ->get()->toArray();
        // return $user_access;
    // }

    /*
     * Date: 15-04-2019
     * Author: Muhammad Arshad
     * Parameter: Param
     * Description:  this function will check the access in journal article table
     */

    // public static function CheckChapterUserAccess($param)
    // {

        // $user_access = DB::table('user_access_chapter as c')
            // ->select('*')
            // ->where('c.user_access_id', '=', $param['session_data']->user_access_id)
            // ->where('c.user_id', '=', $param['session_data']->user_id)
            // ->where('c.ebook_id', '=', $param['ebook_id'])
            // ->where('c.chapter_id', '=', $param['chapter_id']);
			// if (!empty($param['ebook_volume_id']))
			// {
			// $user_access = $user_access->where('c.ebook_volume_id', '=', $param['ebook_volume_id']);
			// }
            
            // $user_access = $user_access->get()->toArray();
			
        // return $user_access;
    // }

    /* Note: This function is not using in system
     * Date: 09-05-2019
     * Author: Imran Khan
     * Parameter: user_access_id, request_id
     * Description:  this function will delete the complete access of access_id
     */

    // public static function DeleteUserAccess($request_id,$user_access_id)
    // {
        // // Insert record user_access_article_history
        // $user_access_article = DB::table('user_access_article')->select(DB::raw('*'))->where('request_id', '=', $request_id)->where('user_access_id', '=', $user_access_id)->get();
        // if(count($user_access_article) > 0)
        // {
            // foreach($user_access_article as $key => $value)
            // {
                // $user_access[$key]=(array) $value;
                // $user_access[$key]['operation'] = 'D';
            // }

            // DB::table('user_access_article_history')->insert($user_access);
        // }

        // // Insert record token_access_history
        // $token_access = DB::table('token_access')->select(DB::raw('*'))->where('request_id', '=', $request_id)->where('user_access_id', '=', $user_access_id)->get();
        // if(count($token_access) > 0)
        // {
            // foreach($token_access as $key => $value)
            // {
                // $token[$key]=(array) $value;
                // $token[$key]['operation'] = 'D';
            // }

            // DB::table('token_access_history')->insert($token);
        // }


        // // Insert record user_access_chapter_history
        // $user_access_chapter = DB::table('user_access_chapter')->select(DB::raw('*'))->where('request_id', '=', $request_id)->where('user_access_id', '=', $user_access_id)->get();
        // if(count($user_access_chapter) > 0)
        // {
            // foreach($user_access_chapter as $key => $value)
            // {
                // $access_chapter[$key]=(array) $value;
                // $access_chapter[$key]['operation'] = 'D';
            // }

            // DB::table('user_access_chapter_history')->insert($access_chapter);
        // }

        // // Insert record user_access_ebook_volume_history
        // $user_access_ebook_volume = DB::table('user_access_ebook_volume')->select(DB::raw('*'))->where('request_id', '=', $request_id)->where('user_access_id', '=', $user_access_id)->get();
        // if(count($user_access_ebook_volume) > 0)
        // {
            // foreach($user_access_ebook_volume as $key => $value)
            // {
                // $access_ebook_volume[$key]=(array) $value;
                // $access_ebook_volume[$key]['operation'] = 'D';
            // }

            // DB::table('user_access_ebook_volume_history')->insert($access_ebook_volume);
        // }

        // // Insert record user_access_ip_range_history
        // // $user_access_ip_range = DB::table('user_access_ip_range')->select(DB::raw('*'))->where('request_id', '=', $request_id)->where('user_access_id', '=', $user_access_id)->get();
        // // if(count($user_access_ip_range) > 0)
        // // {
            // // foreach($user_access_ip_range as $key => $value)
            // // {
                // // $access_ip_range[$key]=(array) $value;
                // // $access_ip_range[$key]['operation'] = 'D';
            // // }

            // // DB::table('user_access_ip_range_history')->insert($access_ip_range);
        // // }

        // // Insert record user_access_issue_history
        // $user_access_issue = DB::table('user_access_issue')->select(DB::raw('*'))->where('request_id', '=', $request_id)->where('user_access_id', '=', $user_access_id)->get();
        // if(count($user_access_issue) > 0)
        // {
            // foreach($user_access_issue as $key => $value)
            // {
                // $access_issue[$key]=(array) $value;
                // $access_issue[$key]['operation'] = 'D';
            // }

            // DB::table('user_access_issue_history')->insert($access_issue);
        // }

        // // Insert record user_access_journal_volume_history
        // $user_access_journal_volume = DB::table('user_access_journal_volume')->select(DB::raw('*'))->where('request_id', '=', $request_id)->where('user_access_id', '=', $user_access_id)->get();
        // if(count($user_access_journal_volume) > 0)
        // {
            // foreach($user_access_journal_volume as $key => $value)
            // {
                // $journal_volume[$key]=(array) $value;
                // $journal_volume[$key]['operation'] = 'D';
            // }

            // DB::table('user_access_journal_volume_history')->insert($journal_volume);
        // }


        // // Delete all requested access data from the access table
        // DB::table('user_access_article')->where('request_id', '=', $request_id)->where('user_access_id', $user_access_id)->delete();
        
        // DB::table('user_access_chapter')->where('request_id', '=', $request_id)->where('user_access_id', $user_access_id)->delete();
        
        // DB::table('user_access_ebook_volume')->where('request_id', '=', $request_id)->where('user_access_id', $user_access_id)->delete();
        
        // //DB::table('user_access_ip_range')->where('request_id', '=', $request_id)->where('user_access_id', $user_access_id)->delete();
        
        // DB::table('user_access_issue')->where('request_id', '=', $request_id)->where('user_access_id', $user_access_id)->delete();
        
        // DB::table('user_access_journal_volume')->where('request_id', '=', $request_id)->where('user_access_id', $user_access_id)->delete();
        
        // // Check if only 1 request id then remove the access info
        // $user_access_data = DB::table('user_access_request')->select(DB::raw('*'))->where('user_access_id', '=', $user_access_id)->get();

        // if(count($user_access_data) == 1 && $user_access_data[0]->request_id == $request_id)
        // {

            // $user_access = (array) DB::table('user_access_info')->select(DB::raw('*'))->where('user_access_id', '=', $user_access_id)->first();
                // $user_access['operation'] = 'D';

                // DB::table('user_access_info_history')->insert($user_access);
            
            // DB::table('user_access_info')->where('user_access_id', $user_access_id)->delete();
        // }

    // }



    /*
     * Date: 14-05-2019
     * Author: Muhammad Arshad
     * Parameter: Param
     * Description:  this function will verify the Token base access credentials
     */

    public static function VerifyRestrictUser($restricted_access_key,$user_name,$password, $user_access)
    {
		$access_on = $user_access->access_type_on;
		$access_type = $user_access->access_type_id;
		$user_access_id = $user_access->user_access_id;
		$tbl = 'token_access_'.$access_on;
		
        $user_name = trim($user_name);
        $password = trim($password);
        
        $obj =  DB::table($tbl)->select('*')
                    ->where('user_name',$user_name)
                    ->where('password',$password)
                    ->where('user_access_id',$user_access_id);
        if($access_type==555){//multiple token access
            $obj  = $obj->where('download_count',0);
        }else{
            $obj  = $obj->where('download_count','<',$user_access->restricted_download_limit);
        }
                    
        $obj = $obj->count();
        
        return $obj;
    }

    public static function VerifyRestrictUserWithoutKey($restricted_access_key,$user_access)
    {  
		$access_on = $user_access->access_type_on;
		$user_access_id = $user_access->user_access_id;
		$tbl = 'token_access_'.$access_on;
        return  DB::table($tbl)->select('*')                   
                    ->where('user_access_id',$user_access_id)
                    ->where('download_count','<',$user_access->restricted_download_limit)
                    ->count();

    }

    public static function UpdateRestrictedUserInfo($restricted_user_access_key,$name,$ClientIP,$content_id,$content_type,$user_access)
    {
        $name = trim($name);
		$access_on = $user_access->access_type_on;
		$user_access_id = $user_access->user_access_id;
        $tbl = 'token_access_'.$access_on;
		$tbl_usage = 'token_access_'.$access_on.'_usage';
		
        DB::select("Update $tbl set download_count = (download_count+1) where  user_name = '$name' and user_access_id = $user_access_id");

        $tokendata = DB::table($tbl)->select('*')->where('user_name',$name)->where('user_access_id',$user_access_id)->first();

        $insertResData = array(
                        'token_id'=> $tokendata->token_id,
						'user_access_id'=> $user_access_id,
                        'user_name'=> $name,
                        'download_content_id'=> $content_id,
                        'download_content_type'=> $content_type,
                        'download_time'=> time(),
                        'download_ip'=> $ClientIP,
                        'system_time'=> time(),
                        'system_ip'=> $ClientIP
                        );
        DB::table($tbl_usage)->insert($insertResData);
    }

    public static function UpdateWithoutKeyRestrictedUserInfo($restricted_user_access_key,$ClientIP,$content_id,$content_type,$user_access)
	{
		
		$access_on = $user_access->access_type_on;
		$user_access_id = $user_access->user_access_id;
		$access_type_id = $user_access->access_type_id;
		$tbl = 'token_access_'.$access_on;
		$tbl_usage = 'token_access_'.$access_on.'_usage';
						
			DB::select("Update $tbl
						set download_count = (download_count+1)  
						where  user_access_id = $user_access_id");
			
			$tokendata = DB::table($tbl)->select('*')->where('user_access_id',$user_access_id)->first();

			$insertResData = array(
						'token_id'=> $tokendata->token_id,
						'user_access_id'=> $user_access_id,
						'user_name'=> $tokendata->user_name,
						'download_content_id'=> $content_id,
						'download_content_type'=> $content_type,
						'download_time'=> time(),
						'download_ip'=> $ClientIP,
						'system_time'=> time(),
						'system_ip'=> $ClientIP
						);
		DB::table($tbl_usage)->insert($insertResData);
									
            
    }
	
	
	    /*
     * Date: 10-15-2018
     * Author: Arsal Akhter
     * Parameter: (1) journal_id, access_type, from_date, to_date
				  (2) journal
     * Description:  this function will return the content access volume list
     */
	 
	
    public static function GetUserAccessOnContent($param = null, $join = null,$select = null)
    {
		$ip_base_access = $user_base_access = '';
		$access_type = static::$uc_type[$param['access_type_id']];
		
		$tbl = 'user_access_'.$param['access_on'].'_'.$access_type.' as uac';
		
		if (!empty($tbl))
		{
			$uc_list = DB::table($tbl);
			$uc_list = $uc_list->addSelect('uac.uc_id','uac.from_ip','uac.to_ip','uac.track','uac.from_date as uc_date_from','uac.to_date as uc_date_to','uac.perpetual as uc_perpetual');
			
			
			if($param['access_on'] == 'journal_volume')
			{
				$uc_list = $uc_list->addSelect('uac.journal_id','uac.volume_id');	
			}
			if($param['access_on'] == 'ebook_volume')
			{
				$uc_list = $uc_list->addSelect('uac.ebook_id','uac.volume_id');
			}
			if($param['access_on'] == 'issue')
			{
				$uc_list = $uc_list->addSelect('uac.journal_id','uac.volume_id','uac.issue_id');	
			}
			if($param['access_on'] == 'article')
			{
				$uc_list = $uc_list->addSelect('uac.journal_id','uac.volume_id','uac.issue_id','uac.article_id');	
			}
			if($param['access_on'] == 'chapter')
			{
				$uc_list = $uc_list->addSelect('uac.ebook_id','uac.volume_id','uac.chapter_id');
			}
			
			if(!empty($select))
			{
				$uc_list = $uc_list->addSelect('volume_id','type');
				$uc_list = $uc_list->selectRaw('"Trial Access" as access_base_text, true as terms_popup, "S" as content_download_type, null as restricted_user_access_key, null as restricted_user_access_type,
				CASE track WHEN 0 THEN false ELSE true END as tracksAccessByVolume');
				
			}
			
			if (!empty($join['user_access_info']))
			{
				$uc_list = $uc_list->join('user_access_info as ai', function($tbl_join)
				{
					$tbl_join->on('ai.user_id', '=', 'uac.user_id');
					$tbl_join->on('ai.access_type_id', '=','uac.type');
				});

				$uc_list = $uc_list->whereRaw('ai.user_access_status = 1');

				if (!empty($param['access_type_id'])) 
				{
					$uc_list = $uc_list->whereRaw('ai.access_type_id = "'.$param['access_type_id'].'"');
				}
				if (!empty($param['access_on'])) 
				{
					$uc_list = $uc_list->whereRaw('ai.access_type_on = "'.$param['access_on'].'"');
				}
				if (!empty($param['user_id'])) 
				{
					$uc_list = $uc_list->whereRaw('ai.user_id = "'.$param['user_id'].'"');
				}
				
				$uc_list = $uc_list->addSelect('ai.*');
			}
			
			if (!empty($join['journal'])) 
			{
				$uc_list = $uc_list->join('journal as j', 'j.journal_id', '=', 'uac.journal_id');
				$uc_list = $uc_list->addSelect('j.journal_id','j.subtitle','j.title');
			}
			
			if (!empty($join['ebook_volume'])) 
			{
				$uc_list = $uc_list->join('ebook_volume as v', 'v.ebook_volume_id', '=', 'uac.volume_id');
				$uc_list = $uc_list->join('ebook as e', 'e.ebook_id', '=', 'v.ebook_id');
				$uc_list = $uc_list->addSelect('v.ebook_id','e.title','v.volume_name');
			}
			
			if (!empty($join['volume'])) 
			{
				$uc_list = $uc_list->join('volume as v', 'v.volume_id', '=', 'uac.volume_id');
				$uc_list = $uc_list->addSelect('v.volume_id','v.volume_name');
				
				if (!empty($join['year'])) 
				{
					$uc_list = $uc_list->join('year as y', 'y.id', '=', 'v.year_id');
					$uc_list = $uc_list->addSelect('y.year');
				}
			}
			
			if (!empty($join['issue'])) 
			{
				$uc_list = $uc_list->join('issue as i', 'i.issue_id', '=', 'uac.issue_id');
				$uc_list = $uc_list->addSelect('i.title as issue_title');
			}
			
			if (!empty($join['article'])) 
			{
				$uc_list = $uc_list->join('article as a', 'a.article_id', '=', 'uac.article_id');
				$uc_list = $uc_list->addSelect('a.title as article_title');
			}
			
			if (!empty($join['chapter'])) 
			{
				$uc_list = $uc_list->join('chapter as a', 'a.chapter_id', '=', 'uac.chapter_id');
				$uc_list = $uc_list->addSelect('a.title as chapter_title');
			}
			
			if (!empty($join['user'])) 
			{
				$uc_list = $uc_list->join('users as u', 'u.id', '=', 'uac.user_id');
				$uc_list = $uc_list->addSelect('u.name','u.id');
				
				if (!empty($param['user_name'])) 
				{
					$uc_list = $uc_list->where('u.name', '=', $param['user_name']);
				}
			}
			
			if (!empty($join['disease'])) 
			{
				$uc_list = $uc_list->join('disease as d', 'd.disease_id', '=', 'uac.disease_id');
				$uc_list = $uc_list->addSelect('d.disease_id','d.disease_name');
			}

			if (!empty($param['journal_id'])) {
				$uc_list = $uc_list->where('uac.journal_id', '=', $param['journal_id']);
			}
			
			if (!empty($param['ebook_id'])) {
				$uc_list = $uc_list->where('uac.ebook_id', '=', $param['ebook_id']);
			}
			
			if (!empty($param['volume_id'])) {
				$uc_list = $uc_list->where('uac.volume_id', '=', $param['volume_id']);
			}
			
			if (!empty($param['issue_id'])) {
				$uc_list = $uc_list->where('uac.issue_id', '=', $param['issue_id']);
			}
			
			if (!empty($param['article_id'])) {
				$uc_list = $uc_list->where('uac.article_id', '=', $param['article_id']);
			}
			
			if (!empty($param['access_type_id'])) 
			{
				$uc_list = $uc_list->where('uac.type', '=', $param['access_type_id']);
			}
		
			if (!empty($param['user_access_id'])) // Single User Access Id
			{
				$uc_list = $uc_list->where('uac.user_access_id', '=', $param['user_access_id']);
			}
			
			if (!empty($param['user_access_ids'])) // Multiple User Access Ids
			{
				$uc_list = $uc_list->whereIn('uac.user_access_id',$param['user_access_ids']);
			}
			
			if (!empty($param['ip_address']) && !empty($param['user_id'])) 
			{
				
				$where = '((uac.from_ip <= '.ip2long($param['ip_address']).' and '.ip2long($param['ip_address']).' <= uac.to_ip) or (uac.from_ip is null and uac.to_ip is null and uac.user_id = '.$param['user_id'].'))';
				
				if (!empty($param['curr_date']))
				{
					$current_date = time();
					$where .= ' and ((uac.from_date <= '.$current_date.' and uac.to_date >= '.$current_date.' and uac.perpetual= 0) or (uac.from_date is null and uac.to_date is null and uac.perpetual= 1))';	
				}
				
				$uc_list = $uc_list->whereRaw($where);
				
			}
			elseif (!empty($param['ip_address'])) 
			{
				$where = '(uac.from_ip <= '.ip2long($param['ip_address']).' and '.ip2long($param['ip_address']).' <= uac.to_ip)';
				
				if (!empty($param['curr_date']))
				{
					$current_date = time();
					$where .= ' and ((uac.from_date <= '.$current_date.' and uac.to_date >= '.$current_date.' and uac.perpetual = 0) or (uac.from_date is null and uac.to_date is null and uac.perpetual= 1))';	
				}
				
				$uc_list = $uc_list->whereRaw($where);
			}
			elseif (!empty($param['user_id']))
			{
				$where = '(uac.from_ip is null and uac.to_ip is null and uac.user_id = '.$param['user_id'].')';
				
				if (!empty($param['curr_date']))
				{
					$current_date = time();
					$where .= ' and ((uac.from_date <= '.$current_date.' and uac.to_date >= '.$current_date.' and uac.perpetual= 0) or (uac.from_date is null and uac.to_date is null and uac.perpetual= 1))';	
				}
				
				$uc_list = $uc_list->whereRaw($where);
			}
			
			if(isset($param['group_by']) &&  $param['group_by'] == 'volume_id')
			{
				$uc_list = $uc_list->groupBy('uac.volume_id');
			}
			if(isset($param['group_by']) &&  $param['group_by'] == 'issue_id')
			{
				$uc_list = $uc_list->groupBy('uac.issue_id');
			}
			if(isset($param['group_by']) &&  $param['group_by'] == 'article_id')
			{
				$uc_list = $uc_list->groupBy('uac.article_id');
			}
			
			if (!empty($param['sort']) && $param['access_on']!="disease") 
			{
				$res = explode(',',$param['sort']);
				$column = $res[0];
				$sort = $res[1];
				if($column == 'v.volume_name')
				{
					$uc_list = $uc_list->orderByRaw(' cast(v.volume_name as signed) '.$sort.' ');
				}
				else
				{
					$uc_list = $uc_list->orderBy($column,$sort);	
				}
			}
			
			if (!empty($param['limit'])) 
			{
				$uc_list = $uc_list->paginate($param['limit']);
			}
			elseif (!empty($param['count']))
			{
				$uc_list = $uc_list->count();
			}
			else
			{
				$uc_list = $uc_list->get();
			}
			
			
			return $uc_list;
		}

    }
	
	 /*
     * Date: 02-04-2019
     * Author: Arsal Akhter
     * Parameter: journal volumes data
     * Description:  this function will insert User Access on Journal volumes, also generates histroy
     */

    public static function InsertUserAccessOnContent($user_access_insert,$user_access_info,$param)
    {	
		$user_id = $user_access_info->user_id;
		
		if($user_access_info->access_type_id == 111 || $user_access_info->access_type_id == 222 || $user_access_info->access_type_id == 888)
		{
			$table_name = 'user_access_'.$param['access_on'].'_trail';
		}
		if($user_access_info->access_type_id == 333)
		{
			$table_name = 'user_access_'.$param['access_on'].'_corporate';
		}
		if($user_access_info->access_type_id == 444 || $user_access_info->access_type_id == 555 || $user_access_info->access_type_id == 666)
		{
			$table_name = 'user_access_'.$param['access_on'].'_token';
		}
		if($user_access_info->access_type_id == 777)
		{
			$table_name = 'user_access_'.$param['access_on'].'_corporate';
		}
		
		foreach (array_chunk($user_access_insert,1000) as $t) 
		{
			DB::table($table_name)->insert($t);	
		}
		
		
		// $query = 'insert into '.$table_name_h.' 
		// (uc_id, volume_id, journal_id, user_id, from_date, to_date, from_ip, to_ip, type, track, sys_user_id, sys_ip, sys_time)
		// select * from '.$table_name.' where user_id  = '.$user_id;
		
		// DB::insert($query);
    }
	
	    /*
     * Date: 27-09-2019
     * Author: Arsal Akhter
     * Parameter: access_type_id, journals ids in array , user_id
     * Description: this function will Insert User Access Volume in history & delete
     */

    public static function DeleteUserAccessOnContent($content_ids,$param)
    {	
		$access_type = static::$uc_type[$param['access_type_id']];

		$tbl = 'user_access_' . $param['access_on'] .'_'.$access_type;
		$delete_list = DB::table($tbl)->addSelect('*');
		$delete_list = $delete_list->SelectRaw("'".$param['sys_h_user_id']."' as sys_h_user_id ");
		$delete_list = $delete_list->SelectRaw("'".$param['sys_h_ip']."' as sys_h_ip ");
		$delete_list = $delete_list->SelectRaw("'".$param['sys_h_time']."' as sys_h_time ");		
		$delete_list = $delete_list->whereIn('uc_id', $content_ids)->where('user_access_id',$param['user_access_id']);
		
		// Get all Columns of table
		$cols = DB::getSchemaBuilder()->getColumnListing($tbl);
		$cols[] = 'sys_h_user_id';
		$cols[] = 'sys_h_ip';
		$cols[] = 'sys_h_time';
		
		// Insert All Selected Content into history
		DB::table($tbl .'_history')->insertUsing($cols, $delete_list);
		
		// Delete All Selected User Access Volumes        
		DB::table($tbl)->whereIn('uc_id', $content_ids)->where('user_access_id',$param['user_access_id'])->delete();
    }


	public static function AddUserAccessRequest($p)
    {
		$access_type = static::$uc_type[$p['access_type_id']];
		
		$tbl = 'user_access_' . $p['access_on'] .'_'.$access_type;
		
		$q = 'INSERT IGNORE INTO user_access_request_info (request_id,user_access_id,action,sys_user_id,sys_time,sys_ip)';
		$q .= 'select '.$p['request_id'].' as request_id, user_access_id, "D" as action, '.$p['sys_user_id'].' as sys_user_id, ';		
		$q .= $p['sys_time'] . ' as sys_time,'.$p['sys_ip'].' as sys_ip from '.$tbl.' where user_access_id = '.$p['user_access_id'].' limit 1';
		
		DB::select($q);
		
	}
	
	public static function AddUserAccessWithRequest($p)
    {
		$query = 'INSERT IGNORE INTO user_access_request_info (request_id,user_access_id,action,sys_user_id,sys_time,sys_ip)';
		$query .= 'values ('.$p['request_id'].','.$p['user_access_id'].','.$p['action'].','.$p['sys_user_id'].','.$p['sys_time'].','.$p['sys_ip'].')';
		DB::select($query);
	}
	
	/*
     * Date: 03-04-2019
     * Author: Arsal Akhter
     * Parameter: token data
     * Description:  this function will insert token data, also generates history
     */
	
	public static function InsertToken($param,$token_access)
    {
		DB::table('token_access_'.$param['access_on'])->insertGetId($token_access);
	}
	
	/*
     * Date: 03-04-2019
     * Author: Arsal Akhter
     * Parameter: token data
     * Description:  this function will insert token data, also generates history
     */
	
	public static function GetTokenlimit($param)
    {
		if (!empty($param['access_on']) && !empty($param['user_name']) && !empty($param['access_type']))
		{
			$token = DB::table('user_access_info as i');
			
			if (!empty($param['select']))
			{
				$token = $token->select('*');	
			}
			else
			{
				$token = $token->select('restricted_download_limit as token');	
			}
			
			$token = $token->join('users as u', 'u.id', '=', 'i.user_id');
			$token = $token->where('u.name',$param['user_name']);
			$token = $token->where('i.access_type_on',$param['access_on']);
			$token = $token->where('i.access_type_id',$param['access_type'])->first();
			
			return $token;	
		}
	}
	
		/*
     * Date: 03-04-2019
     * Author: Arsal Akhter
     * Parameter: user_access_id, user_id, user_name, token_usage_id
     * Description:  this function will return Available Token or Unavailable Token
     */
	 
	public static function GetAccessTokenList($param = null,$join = null)
    {
		
		$tbl = 'token_access_'.$param['access_on'].' as t';
		$tbl_usage = 'token_access_'.$param['access_on'].'_usage as tau';
		
		$token = DB::table($tbl);
		//$token = $token->join('user_access_info as info', 'info.user_access_id', '=', 't.user_access_id');
		$token = $token->addSelect('t.*','t.user_name as available_user_name','t.system_time as access_assign_date');
		
		if (!empty($join['token_access_usage']))
		{
			$token = $token->leftjoin($tbl_usage, 'tau.token_id', '=', 't.token_id');
			$token = $token->addSelect('token_usage_id','tau.token_id','tau.user_access_id','tau.user_name as usage_user_name','download_content_id','download_time','download_ip','tau.system_time','tau.system_ip');
		}
		
		if (!empty($join['article']))
		{
			$token = $token->join('article as a', 'a.article_id', '=', 'tau.download_content_id');
			$token = $token->addSelect('a.title as article_title','a.doi as article_doi','a.first_page','a.last_page');
		}
		if (!empty($join['issue']))
		{
			$token = $token->join('issue as i', 'i.issue_id', '=', 'a.issue_id');
			$token = $token->addSelect('i.title as issue_title');
		}
		if (!empty($join['volume']))
		{
			$token = $token->join('volume as v', 'v.volume_id', '=', 'a.volume_id');
			$token = $token->join('year as y', 'y.id', '=', 'v.year_id');
			$token = $token->addSelect('v.volume_name as volume_name','y.year as year');
		}
		if (!empty($join['journal']))
		{
			$token = $token->join('journal as j', 'j.journal_id', '=', 'a.journal_id');
			$token = $token->addSelect('j.title as journal_title','j.eissn as journal_eissn','j.issn as journal_issn');
		}

		if (!empty($join['chapter']))
		{
			$token = $token->join('chapter as c', 'c.chapter_id', '=', 'tau.download_content_id');
			$token = $token->addSelect('c.title as chapter_title','c.doi as chapter_doi','c.first_page','c.last_page');
		}
		if (!empty($join['ebook_volume']))
		{
			$token = $token->join('ebook_volume as ev', 'ev.ebook_volume_id', '=', 'tau.download_content_id');
			$token = $token->join('ebook as e', 'e.ebook_id', '=', 'ev.ebook_id');
			$token = $token->join('year as yy', 'yy.id', '=', 'ev.year_id');
			$token = $token->addSelect('e.title as ebook_title','yy.year as year');
		}
		
		if (!empty($param['user_access_id'])) 
		{
			$token = $token->where('t.user_access_id', '=', $param['user_access_id']);
		}

		if (!empty($param['user_id'])) 
		{
			$token = $token->where('t.user_id', '=', $param['user_id']);
		}
		
		if (!empty($param['user_name'])) 
		{
			$token = $token->where('t.user_name', '=', $param['user_name']);
		}
		
		if (isset($param['download_count']))
		{
			$token = $token->where('t.download_count', '=', $param['download_count']);
		}
		
		if (isset($param['usage_id']) && $param['usage_id'] == false) 
		{
			$token = $token->where('tau.usage_id', '=', null);	
		}
		
		if (isset($param['usage_id']) && $param['usage_id'] == true) 
		{
			$token = $token->where('tau.usage_id', '<>', null);	
		}
		
		if (!empty($param)) 
		{
			if (isset($param['count'])) 
			{
				$token = $token->count();	
			}
			else
			{
				$token = $token->get();	
			}
		}
        return $token;
    }
	
	/*
     * Date: 03-04-2019
     * Author: Arsal Akhter
     * Parameter: user_access_id, token_id, user_name, usage_id
     * Description:  this function will return Used Token only
     */
	 
	public static function GetAccessUsedTokenList($param = null,$join = null)
    {
		$token = DB::table('token_access_'.$param['access_on'].'_usage as t')->addSelect('t.*',DB::raw("'".$param['access_on']."' as content_type"));
		
		if (!empty($join['article']))
		{
			$token = $token->join('article as a', 'a.article_id', '=', 't.download_content_id');
			$token = $token->addSelect('a.title as article_title','a.doi as article_doi','a.first_page','a.last_page',DB::raw("'article' as content_type"));
		}

		if (!empty($join['issue']))
		{
			$token = $token->leftjoin('issue as i', 'i.issue_id', '=', 'a.issue_id');
			$token = $token->addSelect('i.title as issue_title',DB::raw("'article' as content_type"));
		}
		if (!empty($join['volume']))
		{
			$token = $token->join('volume as v', 'v.volume_id', '=', 'a.volume_id');
			$token = $token->join('year as y', 'y.id', '=', 'v.year_id');
			$token = $token->addSelect('v.volume_name as volume_name','y.year as year',DB::raw("'article' as content_type"));
		}
		if (!empty($join['journal']))
		{
			$token = $token->join('journal as j', 'j.journal_id', '=', 'a.journal_id');
			$token = $token->addSelect('j.title as journal_title','j.eissn as journal_eissn','j.issn as journal_issn',DB::raw("'article' as content_type"));
		}

		/*if (!empty($join['chapter']))
		{
			$token = $token->join('chapter as c', 'c.chapter_id', '=', 't.download_content_id');
			$token = $token->addSelect('c.title as chapter_title','c.doi as chapter_doi','c.first_page','c.last_page',DB::raw("'chapter' as content_type"));
		}
		if (!empty($join['ebook_volume']))
		{
			// $token = $token->join('ebook_volume as ev', 'ev.ebook_volume_id', '=', 't.download_content_id');
			// $token = $token->join('chapter as cc', 'cc.chapter_id', '=', 't.download_content_id');
			$token = $token->join('ebook_volume  as ev', function ($join) {
                $join->on('ev.ebook_volume_id', '=', 't.download_content_id');
                $join->on('t.download_content_type', '=', DB::raw("'e'"));
            });
            $token = $token->join('chapter  as c', function ($join) {
                $join->on('c.chapter_id', '=', 't.download_content_id');
                $join->on('t.download_content_type', '=', DB::raw("'c'"));
            });			
			$token = $token->join('ebook as e', 'e.ebook_id', '=', 'ev.ebook_id');
			$token = $token->join('year as yy', 'yy.id', '=', 'ev.year_id');
			$token = $token->addSelect('e.title as ebook_title','yy.year as year',DB::raw("'ebook_volume' as content_type"));
		}*/
		
		
		if (!empty($param['usage_id'])) 
		{
			$token = $token->where('usage_id', '=', $param['usage_id']);
		}
		if (!empty($param['token_id'])) 
		{
			$token = $token->where('token_id', '=', $param['token_id']);
		}
		if (!empty($param['user_access_id'])) 
		{
			$token = $token->where('t.user_access_id', '=', $param['user_access_id']);
		}
		if (!empty($param['user_name'])) 
		{
			$token = $token->where('user_name', '=', $param['user_name']);
		}
		if (isset($param['download_content_id']))
		{
			$token = $token->where('download_content_id', '=', $param['download_content_id']);
		}
		$token = $token->orderBy('download_time', 'ASC');
		if (!empty($param)) 
		{
			$token = $token->get();
		}
		
        return $token;
    }

    /*
     * Date: 24-01-2020
     * Author: Arshad
     * Parameter: User id
     * Description:  this function will return User Token Stats
     */

    public static function GetUserTokenCount($userid)
    {
    	$result = DB::select("select sum(Total2)-sum(Total) as Used,sum(Total)as `available`,sum(Total2)`Total` from (

                                SELECT count(*)as Total,'' as Total2,'N' as Keyword FROM user_access_info r
                                left join token_access_issue_usage i on i.user_access_id=r.user_access_id
                                left join token_access_journal_volume_usage v on v.user_access_id=r.user_access_id
                                left join token_access_ebook_volume_usage ev on ev.user_access_id=r.user_access_id
                                left join token_access_article_usage a on a.user_access_id=r.user_access_id
                                left join token_access_chapter_usage c on c.user_access_id=r.user_access_id
                                where r.user_id='".$userid."' and v.download_ip is null and i.download_ip is null and c.download_ip is null and a.download_ip is null

                                Union

                                SELECT ''as Total ,sum(restricted_download_limit) as Total2, 'T' as Keyword FROM user_access_info r
                                where r.user_id='".$userid." and r.user_access_status=1'
                                
                                 )as temp;")[0];
    		return $result;
    }

    public static function GetUsedTokenList($userid)
    {
    	$result = DB::select("select i.token_usage_id, i.user_name,password,download_time, download_ip, 'article' as content_type,download_content_id, i.system_time FROM user_access_info r
			inner join token_access_issue_usage i on i.user_access_id=r.user_access_id
			inner join token_access_issue iu on iu.user_name=i.user_name
			where r.user_id='".$userid."' and i.download_ip is not null
			union
			SELECT v.token_usage_id, v.user_name,password,download_time, download_ip, 'article' as content_type, download_content_id, v.system_time FROM user_access_info r
			inner join token_access_journal_volume_usage v on v.user_access_id=r.user_access_id
			inner join token_access_journal_volume vu on vu.user_name=v.user_name
			where r.user_id='".$userid."' and v.download_ip is not null

			union
			SELECT ev.token_usage_id, ev.user_name,password,download_time, download_ip, 'ebook_volume' as content_type,  download_content_id, ev.system_time FROM user_access_info r
			inner join token_access_ebook_volume_usage ev on ev.user_access_id=r.user_access_id
			inner join token_access_ebook_volume evu on evu.user_name=ev.user_name
			where r.user_id='".$userid."' and ev.download_ip is not null

			union
			SELECT a.token_usage_id, a.user_name,password,download_time, download_ip, 'article' as content_type, download_content_id, a.system_time FROM user_access_info r
			inner join token_access_article_usage a on a.user_access_id=r.user_access_id
			inner join token_access_article au on au.user_name=a.user_name
			where r.user_id='".$userid."' and a.download_ip is not null
			union
			SELECT c.token_usage_id, c.user_name,password,download_time, download_ip, 'chapter' as content_type, download_content_id, c.system_time FROM user_access_info r
			inner join token_access_chapter_usage c on c.user_access_id=r.user_access_id
			inner join token_access_chapter cu on cu.user_name=c.user_name
			where r.user_id='".$userid."' and c.download_ip is not null");
    	
    	return $result;
    }

        /*
     * Date: 01-10-2021
     * Author: Imran Khan
     * Parameter:param
     * Description:  this function will return the all user access download article 
     */

    public static function GetUserAccessDownloadArticle($param = null)
    { 
        $article = DB::table('download_count_user_access_articles as d');
        
        if (!empty($param['user_access_id'])) {
            $article = $article->where('d.user_access_id', '=', $param['user_access_id']);
        }

        if (!empty($param['article_id'])) {
            $article = $article->where('d.article_id', '=', $param['article_id']);
        }

        if (!empty($param['user_id'])) {
            $article = $article->where('d.user_id', '=', $param['user_id']);
        }

        if (!empty($param['user_email'])) {
            $article = $article->where('d.user_email', '=', $param['user_email']);
        }
        
        $article = $article->whereNotIn('d.content_download_type', ['FO','F'] );

        $article = $article->orderBy('d.id', 'DESC');

        if (!empty($param['paginate'])) {

            $article = $article->paginate(10);
        }
        else
        {
             $article = $article->get();
        }

        return $article;
    }

    /*
     * Date: 03-09-2021
     * Author: Imran Khan
     * Parameter: data
     * Description:  this function will Add shareable details
     */

    public static function AddUserAccessDownloadChapter($data)
    {
       return DB::table('download_count_user_access_chapter')->insert($data);
    }


    /*
     * Date: 04-04-2022
     * Author: Imran Khan
     * Parameter: data
     * Description:  this function will Add shareable details
     */

    public static function AddUserAccessDownloadEbookVolume($data)
    {
       return DB::table('download_count_user_access_ebook_volume')->insert($data);
    }


    /*
     * Date: 03-09-2021
     * Author: Imran Khan
     * Parameter: data
     * Description:  this function will Add shareable details
     */

    public static function AddUserAccessDownloadArticle($data)
    {
       return DB::table('download_count_user_access_articles')->insert($data);
    }

     /*
     * Date: 01-10-2021
     * Author: Imran Khan
     * Parameter:param
     * Description:  this function will return the all user access download article 
     */

    public static function GetUserAccessDownloadChapter($param = null)
    { 
        $chapter = DB::table('download_count_user_access_chapter as c');
        
        if (!empty($param['user_access_id'])) {
            $chapter = $chapter->where('c.user_access_id', '=', $param['user_access_id']);
        }

        if (!empty($param['chapter_id'])) {
            $chapter = $chapter->where('c.chapter_id', '=', $param['chapter_id']);
        }

        if (!empty($param['user_id'])) {
            $chapter = $chapter->where('c.user_id', '=', $param['user_id']);
        }

        if (!empty($param['user_email'])) {
            $chapter = $chapter->where('c.user_email', '=', $param['user_email']);
        }
        
        $chapter = $chapter->whereNotIn('c.content_download_type', ['FO','F'] );

        $chapter = $chapter->orderBy('c.id', 'DESC');

        if (!empty($param['paginate'])) {

            $chapter = $chapter->paginate(10);
        }
        else
        {
             $chapter = $chapter->get();
        }

        return $chapter;
    }

        /*
     * Date: 04-04-2021
     * Author: Imran Khan
     * Parameter:param
     * Description:  this function will return the all user access download ebook Volume 
     */

    public static function GetUserAccessDownloadEbookVolume($param = null)
    { 
        $chapter = DB::table('download_count_user_access_ebook_volume as ev');
        
        if (!empty($param['user_access_id'])) {
            $chapter = $chapter->where('ev.user_access_id', '=', $param['user_access_id']);
        }

        if (!empty($param['ebook_volume_id'])) {
            $chapter = $chapter->where('ev.ebook_volume_id', '=', $param['ebook_volume_id']);
        }

        if (!empty($param['user_id'])) {
            $chapter = $chapter->where('ev.user_id', '=', $param['user_id']);
        }

        if (!empty($param['user_email'])) {
            $chapter = $chapter->where('ev.user_email', '=', $param['user_email']);
        }
        
        $chapter = $chapter->whereNotIn('ev.content_download_type', ['FO','F'] );

        $chapter = $chapter->orderBy('ev.id', 'DESC');

        if (!empty($param['paginate'])) {

            $chapter = $chapter->paginate(10);
        }
        else
        {
             $chapter = $chapter->get();
        }

        return $chapter;
    }    

     /*
     * Date: 04-04-2022
     * Author: Arshad
     * Parameter:param
     * Description:  this function will return the all user access download article
     */
    public static function getUserRegisteredIps($param)
    {

        if(!empty($param['access_type_id']))
		{
			if($param['access_type_id']==111 || $param['access_type_id']==222 || $param['access_type_id']==888){
                $access_type_id = 'trail';
            }elseif($param['access_type_id']==333){
                $access_type_id = 'corporate';
            }elseif($param['access_type_id']==444 || $param['access_type_id']==555 || $param['access_type_id']==666){
                $access_type_id = 'token';
            }elseif($param['access_type_id']==777){
                $access_type_id = 'disease';
            }
		}
        $table = "user_access_".$param['access_type_on'].'_'.$access_type_id;
        $user_registeredips = DB::table($table);
        $user_registeredips = $user_registeredips->join('user_access_info', 'user_access_info.user_id', '=', $table.'.user_id');
        $user_registeredips = $user_registeredips->join('users', 'users.id', '=', $table.'.user_id');
        $user_registeredips = $user_registeredips->join('user_access_organizations', 'user_access_organizations.access_organization_id', '=', 'user_access_info.access_org_id');
        $user_registeredips = $user_registeredips->where($table.'.user_id', '=' , $param['user_id']);
        $user_registeredips = $user_registeredips->groupBy($table.'.user_id', $table.'.from_ip', $table.'.to_ip');
        $user_registeredips = $user_registeredips->orderBy($table.'.user_access_id', 'DESC');

        if (!empty($param['paginate'])) {

            $user_registeredips = $user_registeredips->paginate(50);
        }
        else
        {
             $user_registeredips = $user_registeredips->get();
        }

        return $user_registeredips;




    }

    /*
     * Date: 04-04-2022
     * Author: Arshad
     * Parameter:param
     * Description:  this function will return the all user access download article
     */
    public static function GetUserSubscribedContent($param = null, $join = null,$select = null)
    {
		$ip_base_access = $user_base_access = '';
		$access_type = static::$uc_type[$param['access_type_id']];

		$tbl = 'user_access_'.$param['access_on'].'_'.$access_type.' as uac';

		if (!empty($tbl))
		{
			$uc_list = DB::table($tbl);
			$uc_list = $uc_list->addSelect('uac.uc_id','uac.from_ip','uac.to_ip','uac.track','uac.from_date as uc_date_from','uac.to_date as uc_date_to','uac.perpetual as uc_perpetual');

            /*
			if($param['access_on'] == 'journal_volume')
			{
				$uc_list = $uc_list->addSelect('uac.journal_id','uac.volume_id');
			}
			if($param['access_on'] == 'ebook_volume')
			{
				$uc_list = $uc_list->addSelect('uac.ebook_id','uac.volume_id');
			}
			if($param['access_on'] == 'issue')
			{
				$uc_list = $uc_list->addSelect('uac.journal_id','uac.volume_id','uac.issue_id');
			}
			if($param['access_on'] == 'article')
			{
				$uc_list = $uc_list->addSelect('uac.journal_id','uac.volume_id','uac.issue_id','uac.article_id');
			}
			if($param['access_on'] == 'chapter')
			{
				$uc_list = $uc_list->addSelect('uac.ebook_id','uac.volume_id','uac.chapter_id');
			}
            */

			if (!empty($join['user_access_info']))
			{
				$uc_list = $uc_list->join('user_access_info as ai', function($tbl_join)
				{
					$tbl_join->on('ai.user_id', '=', 'uac.user_id');
					$tbl_join->on('ai.access_type_id', '=','uac.type');
				});

				$uc_list = $uc_list->whereRaw('ai.user_access_status = 1');

				if (!empty($param['access_type_id']))
				{
					$uc_list = $uc_list->whereRaw('ai.access_type_id = "'.$param['access_type_id'].'"');
				}
				if (!empty($param['access_on']))
				{
					$uc_list = $uc_list->whereRaw('ai.access_type_on = "'.$param['access_on'].'"');
				}
				if (!empty($param['user_id']))
				{
					$uc_list = $uc_list->whereRaw('ai.user_id = "'.$param['user_id'].'"');
				}

				$uc_list = $uc_list->addSelect('ai.*');
			}

			if (!empty($join['journal']))
			{
				$uc_list = $uc_list->join('journal as j', 'j.journal_id', '=', 'uac.journal_id');
				$uc_list = $uc_list->addSelect('j.journal_id','j.subtitle','j.title');
			}

			if (!empty($join['ebook_volume']))
			{
				$uc_list = $uc_list->join('ebook_volume as v', 'v.ebook_volume_id', '=', 'uac.volume_id');
				$uc_list = $uc_list->join('ebook as e', 'e.ebook_id', '=', 'v.ebook_id');
				$uc_list = $uc_list->addSelect('v.ebook_id','e.title','v.volume_name');
			}

			if (!empty($join['volume']))
			{
				$uc_list = $uc_list->join('volume as v', 'v.volume_id', '=', 'uac.volume_id');
				$uc_list = $uc_list->addSelect('v.volume_id','v.volume_name');

				if (!empty($join['year']))
				{
					$uc_list = $uc_list->join('year as y', 'y.id', '=', 'v.year_id');
					$uc_list = $uc_list->addSelect('y.year');
				}
			}

			if (!empty($join['issue']))
			{
				$uc_list = $uc_list->join('issue as i', 'i.issue_id', '=', 'uac.issue_id');
				$uc_list = $uc_list->addSelect('i.title as issue_title');
			}

			if (!empty($join['article']))
			{
				$uc_list = $uc_list->join('article as a', 'a.article_id', '=', 'uac.article_id');
				$uc_list = $uc_list->addSelect('a.title as article_title');
			}

			if (!empty($join['chapter']))
			{
				$uc_list = $uc_list->join('chapter as a', 'a.chapter_id', '=', 'uac.chapter_id');
				$uc_list = $uc_list->addSelect('a.title as chapter_title');
			}

			if (!empty($join['user']))
			{
				$uc_list = $uc_list->join('users as u', 'u.id', '=', 'uac.user_id');
				$uc_list = $uc_list->addSelect('u.name','u.id');

				if (!empty($param['user_name']))
				{
					$uc_list = $uc_list->where('u.name', '=', $param['user_name']);
				}
			}

			if (!empty($join['disease']))
			{
				$uc_list = $uc_list->join('disease as d', 'd.disease_id', '=', 'uac.disease_id');
				$uc_list = $uc_list->addSelect('d.disease_id','d.disease_name');
			}

			if (!empty($param['journal_id'])) {
				$uc_list = $uc_list->where('uac.journal_id', '=', $param['journal_id']);
			}

			if (!empty($param['ebook_id'])) {
				$uc_list = $uc_list->where('uac.ebook_id', '=', $param['ebook_id']);
			}

			if (!empty($param['volume_id'])) {
				$uc_list = $uc_list->where('uac.volume_id', '=', $param['volume_id']);
			}

			if (!empty($param['issue_id'])) {
				$uc_list = $uc_list->where('uac.issue_id', '=', $param['issue_id']);
			}

			if (!empty($param['article_id'])) {
				$uc_list = $uc_list->where('uac.article_id', '=', $param['article_id']);
			}

			if (!empty($param['access_type_id']))
			{
				$uc_list = $uc_list->where('uac.type', '=', $param['access_type_id']);
			}

			if (!empty($param['user_access_id'])) // Single User Access Id
			{
				$uc_list = $uc_list->where('uac.user_access_id', '=', $param['user_access_id']);
			}

			if (!empty($param['user_access_ids'])) // Multiple User Access Ids
			{
				$uc_list = $uc_list->whereIn('uac.user_access_id',$param['user_access_ids']);
			}

			if (!empty($param['user_id']))
			{
				$where = '(uac.user_id = '.$param['user_id'].')';

				if (!empty($param['curr_date']))
				{
					$current_date = time();
					$where .= ' and ((uac.from_date <= '.$current_date.' and uac.to_date >= '.$current_date.' and uac.perpetual= 0) or (uac.from_date is null and uac.to_date is null and uac.perpetual= 1))';
				}

				$uc_list = $uc_list->whereRaw($where);
			}

			if(isset($param['group_by']) &&  $param['group_by'] == 'volume_id')
			{
				$uc_list = $uc_list->groupBy('uac.volume_id');
			}
			if(isset($param['group_by']) &&  $param['group_by'] == 'issue_id')
			{
				$uc_list = $uc_list->groupBy('uac.issue_id');
			}
			if(isset($param['group_by']) &&  $param['group_by'] == 'article_id')
			{
				$uc_list = $uc_list->groupBy('uac.article_id');
			}

			if (!empty($param['sort']) && $param['access_on']!="disease")
			{
				$res = explode(',',$param['sort']);
				$column = $res[0];
				$sort = $res[1];
				if($column == 'v.volume_name')
				{
					$uc_list = $uc_list->orderByRaw(' cast(v.volume_name as signed) '.$sort.' ');
				}
				else
				{
					$uc_list = $uc_list->orderBy($column,$sort);
				}
			}

			if (!empty($param['limit']))
			{
				$uc_list = $uc_list->paginate($param['limit']);
			}
			elseif (!empty($param['count']))
			{
				$uc_list = $uc_list->count();
			}
			else
			{
				$uc_list = $uc_list->get();
			}


			return $uc_list;
		}

    }


}
