<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
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')
        ->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'])){
           $custname = $param['customer_name']; 
           $query =  $query->whereRaw("users.name like '%$custname%' OR users.display_name LIKE '%$custname%' OR users.org_name LIKE '%$custname%' ");
        }

        $result = $query->select('users.id','users.name','users.display_name','users.ingenta_id','users.org_name','country.name as country_name')->orderBy('country.name')
        // ->toSql();
        ->paginate(15);
        // dd($result);
        $result->appends($request->except(['page','_token']));
        return $result;
    }
    public static function getAgentWithUsageCount($request,$param)
    {
       $id = $param['user']->id;
       $year = $param['by_year'];
       $must_download = 50;

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


        $query = DB::table('tbl_agent_access')
        ->leftjoin('download_count_articles', function($join) use($year){
            $join->on("download_count_articles.user_id","=","tbl_agent_access.agent_id")
                ->on(DB::Raw("FROM_UNIXTIME(download_count_articles.timestamp,'%Y')"),"=", DB::Raw("$year"));
        })
        ->leftjoin('users','users.id','=','tbl_agent_access.agent_id')
        ->leftjoin('country','country.iso','=','users.country')
        ->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'])){
           $query =  $query->where('users.name','LIKE','%'.$param['customer_name'].'%');
        }
        $query->groupBy('tbl_agent_access.agent_id');
        $result = $query->select('users.id','users.name','users.org_name','country.name as country_name',DB::Raw("COUNT(CASE WHEN download_count_articles.article_id != '' THEN 1 ELSE null END) AS total_usage,({$must_download} * {$total_month}) as expected_usage"))
        ->havingRaw('total_usage < expected_usage')
        ->paginate(15);
        // ->toSql();
        // dd($result);
        $result->appends($request->except(['page','_token']));
        return $result;
    }
    public static function getAgentWithUsageCountByCountry($request,$param)
    {
       $id = $param['user']->id;
       $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('download_count_articles', function($join) use($year){
            $join->on("download_count_articles.user_id","=","users.id")
                ->on(DB::Raw("FROM_UNIXTIME(download_count_articles.timestamp,'%Y')"),"=", DB::Raw("$year"));
        })
       
        ->leftjoin('country','country.iso','=','users.country')
        ->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'])){
           $query =  $query->where('users.name','LIKE','%'.$param['customer_name'].'%');
        }
        $query->groupBy('users.id');
        $result = $query->select('users.id','users.name','users.org_name','country.name as country_name',DB::Raw("COUNT(CASE WHEN download_count_articles.article_id != '' THEN 1 ELSE null END) AS total_usage,({$must_download} * {$total_month}) as expected_usage"))
        ->havingRaw('total_usage < expected_usage')
        ->paginate(15);
        // ->toSql();
        // dd($result);
        $result->appends($request->except(['page','_token']));
        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(15);
        // ->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'];
      $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')
      ->selectRaw("tmp.userid,users.first_name as name,users.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'");
      }

      
      
      // ->toSql();
      $result = $result->paginate(15);
      // 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

                  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,
                    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  

                    

                  )
                  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` WHERE email != '' AND ( affiliation like '%$institute[0]%'";
      if (count($institute) > 0) {
                
                for ($i=1; $i < count($institute)  ; $i++) { 
                    
                    $sql .= "
                        OR
                        affiliation like '%$institute[$i]%'";     
                    }
            }    
        $sql .=")";
        if(empty($flag)){
          $sql .= " LIMIT 25";
        }
        return DB::select($sql); 

    }
}
