<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Pagination\Paginator;
use DB;

class AgentModel extends Model
{
    use HasFactory;

    public static function getAgentCustomer($request,$param)
    {
        $id = $param['user']->id;
        $user_type = $param['user']->agent_access_type;
        $join_clm_with = ($user_type == 'C') ? 'users.country' : 'users.id';
        
        $query = DB::table('tbl_agent_access')
        ->join('users',$join_clm_with,'=','tbl_agent_access.agent_id')
        ->join('country','country.iso','=','users.country')
      ->join('user_access_info', 'user_access_info.user_id', '=', 'users.id')
	  ->leftjoin(DB::raw('user_access_info_history as h2'), function($query) {
                        $query->on('h2.user_id', 'users.id')
                              ->where(DB::raw('h2.access_type_id in (111,222) and null'))
							  ->where(DB::raw('h2.access_type_on in ("journal_volume", "ebook_volume") and null'))
							  ->where(DB::raw('h2.operation="D" and null'));
                    })					
      ->join("access_types", "access_types.access_type_id", "=", "user_access_info.access_type_id")   
      ->leftjoin('user_access_organizations', 'user_access_organizations.access_organization_id', '=', 'users.access_organization_id');
      // ->where('tbl_agent_access.user_id', '=', $id);
	 
      $query =  $query->whereRaw("tbl_agent_access.user_id = $id 
	  
	  AND NOT (user_access_info.access_type_on IN ('article', 'chapter') 
      AND user_access_info.access_type_id IN (111, 222) or (user_access_info.access_type_on IN ('ebook_volume') 
      AND user_access_info.access_type_id IN (888) ) )
	  AND NOT (user_access_info.access_type_id IN ('111', '222') AND user_access_info.perpetual = 1 
	  AND user_access_info.access_type_on IN ('journal_volume', 'ebook_volume') AND user_access_info.user_access_status = '3')");
        
      $query_history = DB::table('tbl_agent_access')
      ->join('users', $join_clm_with, '=', 'tbl_agent_access.agent_id')
      ->join('country', 'country.iso', '=', 'users.country')
      ->join('user_access_info_history', 'user_access_info_history.user_id', '=', 'users.id')
      ->join("access_types", "access_types.access_type_id", "=", "user_access_info_history.access_type_id")
      ->leftjoin('user_access_organizations', 'user_access_organizations.access_organization_id', '=', 'users.access_organization_id');
      // ->where('tbl_agent_access.user_id', '=', $id);
      $query_history =  $query_history->whereRaw("tbl_agent_access.user_id = $id 
      AND user_access_info_history.access_type_on NOT IN ('article', 'chapter') 
      AND user_access_info_history.access_type_id IN ('111', '222') AND user_access_info_history.operation = 'D' 
	  AND NOT (user_access_info_history.access_type_id IN ('111', '222') AND user_access_info_history.perpetual =1 
	  AND user_access_info_history.access_type_on IN ('journal_volume', 'ebook_volume') AND user_access_info_history.operation = 'D')");

        if(!empty($param['country_id'] )){
           $query =  $query->where('users.country','=',$param['country_id']);
      $query_history =  $query_history->where('users.country', '=', $param['country_id']);
        }
        
        if(!empty($param['customer_name'])){
           $custname = $param['customer_name']; 
           $query =  $query->whereRaw("(users.name like '%$custname%' OR users.display_name LIKE '%$custname%' OR users.org_name LIKE '%$custname%')");
      $query_history =  $query_history->whereRaw("(users.name like '%$custname%' OR users.display_name LIKE '%$custname%' OR users.org_name LIKE '%$custname%')");
    }

    if (!empty($param['organization_name'])) {
      $orgname = addslashes($param['organization_name']);
      $query =  $query->whereRaw('user_access_organizations.organization_name LIKE "%' . $orgname . '%" ');
      $query_history =  $query_history->whereRaw('user_access_organizations.organization_name LIKE "%' . $orgname . '%" ');
    }

    if (!empty($param['customer_type'])) {
		if($param['customer_type']=="consortiaorg"){
			$query =  $query->where('user_access_organizations.parent_id', '!=', 0);
			$query_history =  $query_history->where('user_access_organizations.parent_id', '!=', 0);
			$query =  $query->where('users.user_type', '=', 'Org');
			$query_history =  $query_history->where('users.user_type', '=', 'Org');
		}else{
			$query =  $query->where('users.user_type', '=', $param['customer_type']);
			$query_history =  $query_history->where('users.user_type', '=', $param['customer_type']);
			
		}
	  
    }
    //$query->groupBy('users.id');
    $query = $query->select('users.id', 'users.name', 'users.display_name', 'users.first_name', 'users.last_name', 'users.ingenta_id', 'users.org_name', 'user_access_organizations.organization_name', 'country.name as country_name', 'users.openathensOrganisationId', 'users.openathensrealmName', 'users.openathensderivedEduPersonScope', 'access_types.access_type_title', 'user_access_info.access_type_on', 'user_access_info.perpetual', 'user_access_info.access_date_from', 'user_access_info.access_date_to', 'users.access_organization_id', 'access_types.access_type_id', 'user_access_info.user_access_status', 'user_access_info.access_setup_on', 'user_access_info.user_access_id','user_access_organizations.parent_id','user_access_info.remarks',DB::Raw('"main" as tableflag'));

    $query_history = $query_history->select('users.id', 'users.name', 'users.display_name', 'users.first_name', 'users.last_name', 'users.ingenta_id', 'users.org_name', 'user_access_organizations.organization_name', 'country.name as country_name', 'users.openathensOrganisationId', 'users.openathensrealmName', 'users.openathensderivedEduPersonScope', 'access_types.access_type_title', 'user_access_info_history.access_type_on', 'user_access_info_history.perpetual', 'user_access_info_history.access_date_from', 'user_access_info_history.access_date_to', 'users.access_organization_id', 'access_types.access_type_id', DB::Raw('"3" as user_access_status'), 'user_access_info_history.access_setup_on','user_access_info_history.user_access_id','user_access_organizations.parent_id','user_access_info_history.remarks',DB::Raw('"history" as tableflag'));
	/* $query_history = $query_history->groupBy('users.id','user_access_info_history.access_type_on', 'user_access_info_history.perpetual', 'user_access_info_history.access_date_from', 'user_access_info_history.access_date_to', 'user_access_info_history.access_org_id');
 */
    $qq = $query->union($query_history);

    $groupby = DB::query()->fromSub($qq, 'tmp')
    ->select('*')
    ->groupBy('tmp.id','tmp.access_type_on','tmp.access_type_id', 'tmp.perpetual', 'tmp.access_date_from', 'tmp.access_date_to', 'tmp.access_organization_id')
    ->orderBy('name');

    $result = $groupby->get();
    //$result->appends($request->except(['page', '_token']));
        return $result;
    }
    public static function getAgentWithUsageCount($request,$param,$perPage='',$skip='',$flag='')
    {
       $id = $param['user']->id;
       $year = $param['by_year'];
       
       $query = DB::table('tbl_agent_access')
         ->join('users','users.id','=','tbl_agent_access.agent_id')
         ->leftjoin('user_access_organizations','user_access_organizations.access_organization_id','=','users.access_organization_id')
         ->join('country','country.iso','=','users.country')
         ->join('tbl_ingenta_usage_cache', function($join) use($year){
          $join->on("users.id","=","tbl_ingenta_usage_cache.user_id")
                ->on("tbl_ingenta_usage_cache.year","=", DB::Raw("$year"));
         })
         ->join('download_count_articles', function($join) use($year){
          $join->on("tbl_agent_access.agent_id","=","download_count_articles.user_id")
                ->on(DB::Raw("FROM_UNIXTIME(download_count_articles.timestamp,'%Y')"),"=", DB::Raw("$year"));
        })
        
        ->where('tbl_agent_access.user_id','=',$id);
        
        if(!empty($param['country_id'] )){
           $query =  $query->where('users.country','=',$param['country_id']);
        }
        
		if(!empty($param['customer_name'])){
		   $customer_name = $param['customer_name'];
		   $query = $query->whereRaw("(users.display_name like '%" . $customer_name . "%' or users.name like '%" . $customer_name . "%')");
        }
        $query->groupBy('tbl_agent_access.agent_id');

    $result = $query->select(
      'users.id',
      'users.name',
      'users.org_name',
      'user_access_organizations.organization_name',
      'country.name as country_name',
      DB::Raw("(COUNT(CASE WHEN download_count_articles.article_id != '' THEN 1 ELSE NULL END) + IFNULL(tbl_ingenta_usage_cache.total_count,0)) AS total_usage"),
      DB::Raw(
        "(SELECT (IFNULL((((SUM(IFNULL(journal_cost,0))*0.25)/10)/12),0) * MONTH(CURDATE())) AS expected_usage FROM 
                (
                  SELECT j.subtitle,(IF(p.subscription_cost = 'N/A',0,p.subscription_cost)) AS journal_cost 
                  FROM `user_access_journal_volume_corporate` d 
                  INNER JOIN journal j ON j.journal_id = d.journal_id 
                  INNER JOIN journal_price p ON j.subtitle = p.subtitle 
                  WHERE d.user_id = users.`id` GROUP BY d.user_id,d.journal_id

                ) AS t


                ) AS expected_usage"
            )
        
      )->havingRaw('total_usage < expected_usage');
      
      if(empty($flag)){
        $result = $result->take($perPage)->skip($skip)->get();
      }else{
         $result = $result->get()->toArray();
      }
      

      // ->paginate(10);
      
      return $result;
    }
    public static function getAgentWithUsageCountByCountry($request,$param,$perPage='',$skip='',$flag='')
    {
       $id = $param['user']->id;
       $year = $param['by_year'];
       
       $query = DB::table('tbl_agent_access')
         ->join('users','users.country','=','tbl_agent_access.agent_id')
         ->leftjoin('user_access_organizations','user_access_organizations.access_organization_id','=','users.access_organization_id')
         ->join('country','country.iso','=','users.country')
         ->join('tbl_ingenta_usage_cache', function($join) use($year){
          $join->on("users.id","=","tbl_ingenta_usage_cache.user_id")
                ->on("tbl_ingenta_usage_cache.year","=", DB::Raw("$year"));
         })
         ->join('download_count_articles', function($join) use($year){
          $join->on("users.id","=","download_count_articles.user_id")
                ->on(DB::Raw("FROM_UNIXTIME(download_count_articles.timestamp,'%Y')"),"=", DB::Raw("$year"));
         })
        
        ->where('tbl_agent_access.user_id','=',$id);
        
        if(!empty($param['country_id'] )){
           $query =  $query->where('users.country','=',$param['country_id']);
        }
        
        if(!empty($param['customer_name'])){
		   $customer_name = $param['customer_name'];
		   $query = $query->whereRaw("(users.display_name like '%" . $customer_name . "%' or users.name like '%" . $customer_name . "%')");
        }
        $query->groupBy('users.id');

    $result = $query->select(
      'users.id',
      'users.name',
      'users.org_name',
      'user_access_organizations.organization_name',
      'country.name as country_name',
      DB::Raw("(COUNT(CASE WHEN download_count_articles.article_id != '' THEN 1 ELSE NULL END) + IFNULL(tbl_ingenta_usage_cache.total_count,0)) AS total_usage"),
      DB::Raw(
        "(SELECT (IFNULL((((SUM(IFNULL(journal_cost,0))*0.25)/10)/12),0) * MONTH(CURDATE())) AS expected_usage FROM 
                (
                  SELECT j.subtitle,(IF(p.subscription_cost = 'N/A',0,p.subscription_cost)) AS journal_cost 
                  FROM `user_access_journal_volume_corporate` d 
                  INNER JOIN journal j ON j.journal_id = d.journal_id 
                  INNER JOIN journal_price p ON j.subtitle = p.subtitle 
                  WHERE d.user_id = users.`id` GROUP BY d.user_id,d.journal_id

                ) AS t


                ) AS expected_usage"
            )
        
      )->havingRaw('total_usage < expected_usage');
      
      if(empty($flag)){
        $result = $result->take($perPage)->skip($skip)->get();
      }else{
         $result = $result->get()->toArray();
      }
      

      // ->paginate(10);
      
      return $result;
       
    }

      

    public static function getDenailCountByCountry($request,$param)
    {
       $id = $param['user']->id;
       $agent_ids_array = $param['agent_ids_array'];
     
       $year = $param['by_year'];
       $must_download = 50;

       $total_month = ($year < date("Y")) ? 12 : ' Month(CURDATE()) ';


       $query = DB::table('tbl_agent_access')
         ->leftjoin('users','users.country','=','tbl_agent_access.agent_id')
        ->leftjoin('access_denied_report', function($join) use($year){
            $join->on("access_denied_report.userid","=","users.id")
                ->on(DB::Raw("FROM_UNIXTIME(access_denied_report.created_on,'%Y')"),"=", DB::Raw("$year"));
        })
       
        ->leftjoin('country','country.iso','=','users.country')
        ->whereIn('tbl_agent_access.agent_id',$agent_ids_array);
        
        if(!empty($param['country_id'] )){
           $query =  $query->where('users.country','=',$param['country_id']);
        }
        
        $query->selectRaw('country.iso,country.name as country_name,COUNT("access_denied_report.userid") AS total_denial');
        
        $result  = $query->groupBy('tbl_agent_access.agent_id')->orderBy('total_denial','DESC')
        
        ->paginate(10);
        // ->toSql();
        // dd($result);
        $result->appends($request->except(['page','_token']));
        return $result;
    }
    public static function getOpportunityUsers($request,$data)
    {
      $userids = join(',',$data['agent_ids_array']); 
      $by_year = $data['by_year'];
      $count_limit = $data['count_limit'];
	  $customer_name = $data['customer_name'];
      $result  = DB::query()->fromSub(function ($query) use($userids,$by_year) {
          $query->from('access_denied_report')
          ->whereRaw("FROM_UNIXTIME(access_denied_report.created_on,'%Y') = '".$by_year."' AND access_denied_report.userid IN (".$userids.") ")
          ->selectRaw("access_denied_report.userid,access_denied_report.jid,COUNT('access_denied_report.userid') AS total_count")
          ->groupBy('userid','jid');

      }, 'tmp')
      ->join('journal','journal.journal_id','tmp.jid')
      ->join('users','users.id','tmp.userid')
      ->leftjoin('country','country.iso','users.country')
      ->join('journal_price','journal_price.subtitle','=','journal.subtitle')
	  ->join('user_access_organizations','user_access_organizations.access_organization_id','=','users.access_organization_id');
	  
	  if(!empty($customer_name)){
        $result = $result->whereRaw("users.display_name like '%".$customer_name."%'");
      }
	  
      $result  = $result->selectRaw("tmp.userid,users.display_name as name,user_access_organizations.organization_name as org_name,country.name AS country_name, SUM(tmp.total_count) AS total_denial_count,SUM(journal_price.subscription_cost) AS total_price,(SUM(tmp.total_count)/SUM(journal_price.subscription_cost)) AS cost_per_denial")
      ->groupBy('tmp.userid');
      
	  
      if($count_limit == 100){
        $result = $result->havingRaw("total_denial_count <= '$count_limit'");
      }elseif ($count_limit == 300) {
        $result = $result->havingRaw("total_denial_count BETWEEN 101 AND  '300'");
      }elseif ($count_limit == 500) {
        $result = $result->havingRaw("total_denial_count BETWEEN 301 AND  '500'");
      }elseif ($count_limit == 501) {
        $result = $result->havingRaw("total_denial_count >  '500'");
      }
      $result = $result->orderBy('total_denial_count','desc');
      $result = $result->orderBy('total_price','desc');
      
      // ->toSql();
      $result = $result->simplePaginate(10);
      // $result = $result->get();
      // dd($result);
       $result->appends($request->except(['page','_token']));
        return $result;

       

    }
    public static function getMonthlyDenailCount($user_id,$year)
    {
       $sql = "SELECT FROM_UNIXTIME(created_on,'%M') as month, COUNT('user_id') AS toal_count
                FROM access_denied_report 
                WHERE FROM_UNIXTIME(created_on,'%Y') = '$year' 
                AND userid = '$user_id'
                GROUP BY FROM_UNIXTIME(created_on,'%m') ORDER BY FROM_UNIXTIME(created_on,'%m') ASC";


        return DB::select($sql);
    }
    public static function getBusinessOprtByJournal($user_id,$year)
    {
       $result = DB::table('access_denied_report as d')
                 ->join('journal as j','j.journal_id','=','d.jid')
                 ->join('journal_price','journal_price.subtitle','=','j.subtitle')
                 ->whereRaw("FROM_UNIXTIME(d.created_on,'%Y') = '$year' AND d.userid = '$user_id' ")
                 ->selectRaw("IFNULL((SELECT COUNT(dd.journal_id) FROM download_count_articles dd WHERE dd.journal_id=d.jid AND dd.user_id=".$user_id." AND FROM_UNIXTIME(dd.timestamp,'%Y') = ".$year."  GROUP BY dd.journal_id),0) AS subscribed_journal,d.userid,d.jid,j.subtitle,journal_price.subscription_cost as price,COUNT('d.userid') AS total_count, (COUNT('d.userid')/journal_price.subscription_cost) AS cost_per_denail")
                 ->groupBy("d.userid","d.jid")
                 ->paginate(15);
      return $result;
    }
    public static function getSubscribedJournalCount($user_id,$year,$journal_id)
    {
        $sql = " 
        select j.subtitle subtitle,v.volume_name volume,count(d.volume_id) as total_count, y.year as volume_year,'S' as report_type from download_count_articles d         
        left join journal j on j.journal_id=d.journal_id
        left join volume v on v.volume_id=d.volume_id
        left join year y on y.id=v.year_id
        where d.user_id=".$user_id." and j.journal_id=".$journal_id."  and from_unixtime(d.timestamp,'%Y') = ".$year." 
        group by d.volume_id 
        order by total_count desc";

        // echo $sql;die;
        return DB::select($sql);


    }
    public static function getUnSubscribedJournalCount($user_id,$year,$journal_id)
    {
       $sql = " 
        select j.subtitle subtitle,v.volume_name volume,count(v.volume_id) as total_count, y.year as volume_year, 'D' as report_type from access_denied_report d 
        left join article a on a.article_id=d.aid
        left join volume v on v.volume_id=a.volume_id
        left join year y on y.id=v.year_id
        left join journal j on j.journal_id=d.jid
        where d.userid=".$user_id." and j.journal_id=".$journal_id."  and from_unixtime(d.created_on,'%Y') = ".$year."
        group by v.volume_id  
        order by total_count desc";

        // echo $sql;die;
        return DB::select($sql);
    }
    public static function getPPVFromIngenta($identity_id,$year)
    {
      $sql = "SELECT (total_downlod - (sub_download+free_download+oa_download)) AS tot_ingenta_ppv FROM (

              SELECT SUM(total_downloads) AS total_downlod, SUM(subscriber_Downloads) AS sub_download,SUM(free_Downloads) AS free_download
              ,SUM(open_access_Downloads) AS oa_download
              FROM ingenta_views WHERE identity_id = '$identity_id' AND MONTH LIKE '$year%'
              
              ) AS t";  
      
      return DB::select($sql);
    }
    public static function getEurekaTotalUsage($user_name,$filter_year)
    {
      $sql = "  SELECT COUNT(a.`article_id`) AS eureka_usage FROM download_count_articles a 
                INNER JOIN users u ON u.id = a.`user_id`
                INNER JOIN journal j ON a.journal_id = j.journal_id  
                WHERE  u.name ='$user_name' AND FROM_UNIXTIME(timestamp,'%Y') = ".$filter_year." ";

      return DB::select($sql);
    }
    public static function eurekaDenialTotal($user_name,$filter_year)
    {
      $sql = "SELECT COUNT(*) AS eureka_denial FROM access_denied_report a INNER JOIN access_denied_report_category c ON c.id=a.catid 
              INNER JOIN users u ON u.id = a.userid INNER JOIN journal j ON j.journal_id = a.`jid`
              WHERE u.name ='$user_name' AND FROM_UNIXTIME(created_on,'%Y') = ".$filter_year." ";

       return DB::select($sql);        

    }
    public static function ingentaUsageTotal($identity_id,$filter_year)
    {
        $sql = "SELECT SUM(total_downloads) AS ingenta_usage FROM ingenta_views WHERE  identity_id='$identity_id' AND year(created_on) = ".$filter_year." ";

        return DB::select($sql);  
    }
    public static function ingentaDenialTotal($identity_id,$filter_year)
    {
      $sql = "SELECT sum(total) AS ingenta_denial  FROM ingenta_access_denied d 
              INNER join journal j on j.subtitle = d.jcode
              WHERE identity_id='$identity_id' AND year(created_on) = ".$filter_year."";

      return DB::select($sql); 
    }
    public static function getIpRang($user_name)
    {
      $sql = "
                    SELECT DISTINCT(from_ip) AS from_ip,to_ip FROM user_access_journal_volume_corporate c 
                    inner join users u on u.id=c.user_id
                    WHERE u.name='$user_name'
                    UNION 
                    SELECT DISTINCT(from_ip) AS from_ip,to_ip FROM user_access_journal_volume_trail c 
                    inner join users u on u.id=c.user_id
                    WHERE u.name='$user_name'
                    UNION
                    SELECT DISTINCT(from_ip) AS from_ip,to_ip FROM user_access_article_trail c
                    inner join users u on u.id=c.user_id
                    WHERE u.name='$user_name'                       
                  ";

      return DB::select($sql); 
    }
    public static function getPPVFromBSP($from_ip,$to_ip,$year)
    {
      $sql = "  select  sum(order_total) toal_order from uc_orders where 
                    FROM_UNIXTIME(created,'%Y') = '$year' AND order_status ='completed' 
                    and  
                    (
                    
                        ip_address BETWEEN '$from_ip[0]' and '$to_ip[0]' 
                     
                ";
        //  if there multiple no of Ip range then union this query

        if (count($from_ip) > 0) {
                
                for ($i=1; $i < count($from_ip)  ; $i++) { 
                    
                    $sql .= "  
                    OR
                    ip_address BETWEEN '$from_ip[$i]' and '$to_ip[$i]'
                ";     


                }
            }    

         $sql .=")"; 
         return DB::select($sql); 
    }
    public static function getJournalSubcription($key)
    {
       $sql = "
        SELECT * from user_access_journal_volume_corporate c 
        inner join users u on u.id=c.user_id 
        where u.name = '$key'";
       $jsubc_result = DB::select($sql); 
       $subcription_array = [];
       foreach ($jsubc_result as $row) {
             $subcription_array[] = $row->journal_id;
         }   
       return $subcription_array; 
    
    }
    public static function allJournalDenailExcell($user_name,$identity_id,$filter_year,$denial_order_by)
    {
      $sql = "
                SELECT journal_id,jcode,title,ingenta_total,eureka_total,ingenta_total+eureka_total as total_denial,p.subscription_cost,p.subscription_cost/(ingenta_total+eureka_total) as cost_per_denial   from (

                select j.journal_id, j.subtitle as jcode,j.title as title,

                IF( ingenta_total is  NULL, '0', ingenta_total ) as ingenta_total,
                IF( eureka_total is NULL , '0', eureka_total ) as eureka_total


                from journal j 

                left join (
                select i.jcode, sum(total) as ingenta_total 
                from ingenta_access_denied i
                where i.identity_id='$identity_id' 
                AND year(created_on) = '$filter_year' 
                GROUP by i.jcode  
                order by ingenta_total desc
                ) as i on j.subtitle = i.jcode


                left join (

                select d.jid,count(*) as eureka_total from access_denied_report d 
                inner join users u on u.id = d.userid     
                where u.name = '$user_name'  
                AND FROM_UNIXTIME(created_on,'%Y') = '$filter_year'
                group by d.jid
                order by eureka_total desc

                ) as e on e.jid = j.journal_id  

                ) as tmp 

                left JOIN journal_price p on p.subtitle=jcode
                ";
                $sql .= $denial_order_by;

               return DB::select($sql);  
    }
    public static function allJournalUsageExcell($user_name,$identity_id,$filter_year,$usage_order_by)
    {
          $sql = "    SELECT *,(ingenta_total_usage+journal_total_usage) as total_usage,title,(total_eureka_open_access_downloads+total_ingenta_open_access_downloads) as journal_open_access_usage

                  from
                  (
                    select j.journal_id,j.subtitle as jcode ,j.title as title,

                    IF( ingenta_total_usage is  NULL, '0', ingenta_total_usage ) as ingenta_total_usage,
                    
                    IF( journal_total_usage is NULL , '0', journal_total_usage ) as journal_total_usage,

                    IF( total_eureka_open_access_downloads is NULL , '0', total_eureka_open_access_downloads ) as total_eureka_open_access_downloads,
					
					IF( total_ingenta_open_access_downloads is NULL , '0', total_ingenta_open_access_downloads ) as total_ingenta_open_access_downloads,
                    total_abstract_views

                    


                    from journal j

                    left join(

                      select jcode, sum(total_downloads) as ingenta_total_usage,sum(article_abstract_views) as total_abstract_views  from ingenta_views i
                      where i.identity_id = '$identity_id' 
                      AND year(created_on) = '$filter_year'
                      group by jcode

                    ) i on j.subtitle=i.jcode 
                    

                    left join(

                      SELECT j.subtitle as jcode,COUNT(a.`article_id`) AS journal_total_usage FROM download_count_articles a 
                      INNER JOIN users u ON u.id = a.`user_id` inner join journal j on j.journal_id = a.journal_id
                      WHERE  u.name ='$user_name'  
                      AND FROM_UNIXTIME(timestamp,'%Y') = '$filter_year'
                      group by a.journal_id

                    )d on d.jcode=j.subtitle  


                    left join(

                      SELECT j.subtitle as jcode,COUNT(a.article_id) AS total_eureka_open_access_downloads FROM download_count_articles a 
                      INNER JOIN users u ON u.id = a.user_id inner join journal j on j.journal_id = a.journal_id
                      WHERE  u.name ='$user_name' AND a.content_download_type='FO'  
                      AND FROM_UNIXTIME(timestamp,'%Y') = '$filter_year'
                      group by a.journal_id

                    )o on o.jcode=j.subtitle
					
					
					left join(

                      select jcode, sum(open_access_downloads) as total_ingenta_open_access_downloads from ingenta_views i
                      where i.identity_id = '$identity_id' 
                      AND year(created_on) = '$filter_year'
                      group by jcode

                    ) oa on j.subtitle=oa.jcode 

                    

                  )
                  as tmp
                  left JOIN journal_price p on p.subtitle=jcode
                  ";
          $sql .= $usage_order_by; 

          return DB::select($sql);   
    }
    public static function getOpenAccess($jid,$user_name,$filter_year)
    {
        $sql = "select count(*) open_access_count from download_count_articles da 
                    inner join users u on u.id = da.user_id
                    inner join journal_access ja on ((ja.content_type ='a' and ja.content_type_id =da.article_id) or (ja.content_type ='i' and ja.content_type_id =da.issue_id) or (ja.content_type ='v' and ja.content_type_id =da.volume_id) ) and ja.content_type_access_id=2
                    where da.journal_id='$jid' and u.name='$user_name' and FROM_UNIXTIME(timestamp,'%Y') ='$filter_year'
                ";
        return DB::select($sql); 
    }
    public static function gettotalAuthors($institute,$flag)
    {
      $sql = "SELECT * FROM `author` a 
      inner join author_affiliation af on af.author_id = a.author_id

      WHERE a.email != '' AND ( af.web_view like '%$institute[0]%'";
      if (count($institute) > 0) {
                
                for ($i=1; $i < count($institute)  ; $i++) { 
                    
                    $sql .= "
                        OR
                        af.web_view like '%$institute[$i]%'";     
                    }
            }    
        $sql .=")";
        if(empty($flag)){
          $sql .= " LIMIT 25";
        }
        return DB::select($sql); 

    }
    public static function getSubcriptionArticle($jid,$user_name,$year)
    {
        $article_query="  SELECT j.subtitle,v.volume_name as volume  ,y.year,i.`title` AS issue,a.`title`as article_title,a.nid
        ,FROM_UNIXTIME(created_on) AS denial_date,ip_address
        FROM access_denied_report d 
        inner join article a on a.article_id=d.aid
        inner join volume v on v.volume_id=a.volume_id
        left  join issue i ON i.issue_id = a.`issue_id`
        inner join journal j on j.journal_id=v.journal_id
        inner join year y on y.id=v.year_id
        INNER JOIN users u ON u.`id` = d.`userid`
        WHERE FROM_UNIXTIME(created_on,'%Y')='$year' and d.jid ='$jid' AND u.name = '$user_name'  limit 100" ;


    return DB::select($article_query);
  }
  public static function getSubcriptionJournal($user_id, $access_type, $access_type_on)
  {
    if(($access_type == "111" || $access_type == "222") && ($access_type_on == "journal_volume")){
      $access_table = "user_access_journal_volume_trail";
    }elseif(($access_type == "444" || $access_type == "555" || $access_type == "666") && ($access_type_on == "journal_volume")){
      $access_table = "user_access_journal_volume_token";
    }elseif(($access_type == "111" || $access_type == "222") && ($access_type_on == "article")){
      $access_table = "user_access_article_trail";
    }elseif(($access_type == "333") && ($access_type_on == "article")){
      $access_table = "user_access_article_corporate";
    }elseif(($access_type == "444" || $access_type == "555" || $access_type == "666") && ($access_type_on == "article")){
      $access_table = "token_access_article";
    }else{
      $access_table = "user_access_journal_volume_corporate";
    }

    $query = "SELECT j.title,j.subtitle,IFNULL(p.subscription_cost,0) as cost,GROUP_CONCAT(DISTINCT(v.volume_name) ORDER BY CAST(v.volume_name AS SIGNED) DESC ) AS volume,GROUP_CONCAT(DISTINCT(y.year) ORDER BY y.year DESC) AS years FROM $access_table d
                  INNER JOIN volume v ON d.volume_id = v.volume_id
                  INNER JOIN journal j ON j.journal_id = d.journal_id
                  LEFT JOIN journal_price p  ON j.subtitle = p.subtitle
                  INNER JOIN `year` `y` ON y.id = v.year_id
                  WHERE d.user_id = '$user_id'
                  GROUP BY d.journal_id";

       return DB::select($query);           
  }
  public static function getOrganizatonList($request, $param)
  {
    $id = $param['user']->id;
    $user_type = $param['user']->agent_access_type;
    $join_clm_with = ($user_type == 'C') ? 'users.country' : 'users.id';

    $query = DB::table('tbl_agent_access')
      ->join('users', $join_clm_with, '=', 'tbl_agent_access.agent_id')
      ->join('country', 'country.iso', '=', 'users.country')
      ->join('user_access_info', 'user_access_info.user_id', '=', 'users.id')
      ->join("access_types", "access_types.access_type_id", "=", "user_access_info.access_type_id")
      ->leftjoin('user_access_organizations', 'user_access_organizations.access_organization_id', '=', 'users.access_organization_id');
    // ->where('tbl_agent_access.user_id', '=', $id);
    $query =  $query->whereRaw("tbl_agent_access.user_id = $id 	  
	  AND NOT (user_access_info.access_type_on IN ('article', 'chapter') 
      AND user_access_info.access_type_id IN (111, 222) or (user_access_info.access_type_on IN ('ebook_volume') 
      AND user_access_info.access_type_id IN (888) ) )
      AND NOT (user_access_info.access_type_id IN ('111', '222') AND user_access_info.perpetual = 1 AND user_access_info.access_type_on IN ('journal_volume', 'ebook_volume') AND user_access_info.user_access_status = '3')");
	
	$query_history = DB::table('tbl_agent_access')
      ->join('users', $join_clm_with, '=', 'tbl_agent_access.agent_id')
      ->join('country', 'country.iso', '=', 'users.country')
      ->join('user_access_info_history', 'user_access_info_history.user_id', '=', 'users.id')
      ->join("access_types", "access_types.access_type_id", "=", "user_access_info_history.access_type_id")
      ->join('user_access_organizations', 'user_access_organizations.access_organization_id', '=', 'users.access_organization_id');
    // ->where('tbl_agent_access.user_id', '=', $id);
    $query_history =  $query_history->whereRaw("tbl_agent_access.user_id = $id 	  
	  AND NOT (user_access_info_history.access_type_on IN ('article', 'chapter') 
      AND user_access_info_history.access_type_id IN (111, 222) or (user_access_info_history.access_type_on IN ('ebook_volume') 
      AND user_access_info_history.access_type_id IN (888) ) )
      AND NOT (user_access_info_history.access_type_id IN ('111', '222') AND user_access_info_history.perpetual = 1) AND user_access_info_history.access_type_on IN ('journal_volume', 'ebook_volume') AND user_access_info_history.operation = 'D'");


    if (!empty($param['country_id'])) {
      $query =  $query->where('users.country', '=', $param['country_id']);
	  $query_history =  $query_history->where('users.country', '=', $param['country_id']);
    }

    if (!empty($param['customer_name'])) {
      $custname = $param['customer_name'];
      $query =  $query->whereRaw("(users.name like '%$custname%' OR users.display_name LIKE '%$custname%' OR users.org_name LIKE '%$custname%')");
	  $query_history =  $query_history->whereRaw("(users.name like '%$custname%' OR users.display_name LIKE '%$custname%' OR users.org_name LIKE '%$custname%')");
	  
    }

    if (!empty($param['organization_name'])) {
      $orgname = addslashes($param['organization_name']);
      $query =  $query->whereRaw('user_access_organizations.organization_name LIKE "%' . $orgname . '%" ');
	  $query_history =  $query_history->whereRaw('user_access_organizations.organization_name LIKE "%' . $orgname . '%" ');
    }

    if (!empty($param['customer_type'])) {
			
		if($param['customer_type']=="consortiaorg"){
			$query =  $query->where('user_access_organizations.parent_id', '!=', 0);
			$query_history =  $query_history->where('user_access_organizations.parent_id', '!=', 0);
			$query =  $query->where('users.user_type', '=', 'Org');
			$query_history =  $query_history->where('users.user_type', '=', 'Org');
		}else{
			$query =  $query->where('users.user_type', '=', $param['customer_type']);
			$query_history =  $query_history->where('users.user_type', '=', $param['customer_type']);
			
		}
    }
	$query = $query->select('user_access_organizations.organization_name', 'user_access_organizations.access_organization_id','user_access_organizations.parent_id',DB::Raw('(select organization_name from  user_access_organizations po where po.access_organization_id=user_access_organizations.parent_id) as parent_name'));
	$query_history = $query_history->select('user_access_organizations.organization_name', 'user_access_organizations.access_organization_id','user_access_organizations.parent_id',DB::Raw('(select organization_name from  user_access_organizations po where po.access_organization_id=user_access_organizations.parent_id) as parent_name'));
	$qq = $query->union($query_history);

    $groupby = DB::query()->fromSub($qq, 'tmp')
    ->select('*')
    ->groupBy('tmp.organization_name')
    ->orderBy('organization_name');
    
    $result = $groupby->get();

    return $result;
  }

  public static function getUserCountry($user_id)
  {
      $result = DB::table("tbl_agent_access as ac")
      ->select("c.iso","c.printable_name")
      ->join("country as c", "c.iso", "=", "ac.agent_id")
      ->where("ac.user_id", "=", $user_id)
      ->orderBy("c.printable_name", "ASC")
      ->get();

     return $result;
  }
  
  public static function getSubcriptionEbook($user_id, $access_type, $access_type_on)
  {
    if($access_type == "111" || $access_type == "222" || $access_type == "888"){
      $access_table = "user_access_ebook_volume_trail";
    }elseif($access_type == "444" || $access_type == "555" || $access_type == "666"){
      $access_table = "user_access_ebook_volume_token";
    }else{
      $access_table = "user_access_ebook_volume_corporate";
    }

    $query = "SELECT e.title,GROUP_CONCAT(DISTINCT ev.volume_name) AS volume,GROUP_CONCAT(DISTINCT y.year) AS years FROM $access_table d
				  INNER JOIN ebook e ON e.ebook_id = d.ebook_id 
				  INNER JOIN ebook_volume ev ON ev.ebook_id  = e.ebook_id
                  INNER JOIN year y ON y.id = ev.year_id
                  WHERE d.user_id = '$user_id'
                  GROUP BY d.ebook_id
                  ORDER BY e.title";

    return DB::select($query);
  }

  public static function updatePerpetualDate($data)
  {
    $user_access_id  = $data['user_access_id'];
    $access_setup_on = $data['access_setup_on'];

    $query = "UPDATE user_access_info
    SET access_setup_on = '$access_setup_on'
    WHERE user_access_id = '$user_access_id'";

    $result = DB::select($query);

    return $result;
  }

  public static function updateUserAccessRemarks($data)
  {
    $user_access_id  = $data['user_access_id'];
    $remarks = $data['remarks'];

    if($data['tableflag'] == "main"){
      $table = "user_access_info";
    }elseif($data['tableflag'] == "history"){
      $table = "user_access_info_history";
    }

    $query = "UPDATE $table
    SET remarks = '$remarks'
    WHERE user_access_id = '$user_access_id'";

    $result = DB::select($query);

    return $result;
  }

  public static function GetEurekaIngentaUsageDenial($username, $year)
  {
    $result = DB::table("tbl_eureka_ingenta_analytics")
      ->select("*")
      ->where("username", "=", $username)
      ->where("year", "=", $year)
      ->first();

    return $result;
  }
}
