<?php

namespace App\Models;

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

class Journal extends Model
{

	public $table = 'journal';
	public $timestamps = false;

    /*
     * Author: Arsal
     * Parameter: none
     * Description: This function is using for dropdown only, Get journal title as value and journal_id as key for drop down list
     */
    public static function GetJournalTitle($param = null)
    {

        $journal = DB::table('journal');

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

        $journal = $journal->orderBy('subtitle')->pluck(DB::raw("CONCAT(`subtitle`, ' - ', `title`) as title"), 'journal_id');
        

        return $journal;
    }


    /*
     * Author: Arsal
     * Parameter: journal_id
     * Description: This function will return the journal subject list
     */

    public static function GetSubjectByJournalID($journal_id)
    {

        $journal = DB::table('subject_journal')
            ->where('journal_id', '=', $journal_id)
            ->get();

        if ($journal->isEmpty()) {
            return true;
        } else {
            return $journal;
        }
    }

    /*
     * Author: Arsal
     * Parameter: journal_id
     * Description: This function will return Only First Rows of Journals by journal_id
     */
    public static function GetJournalById($journal_id)
    {

        $journal = DB::table('journal')->where('journal_id', $journal_id)->first();
        return $journal;
    }

    /*
     * Date: 04-03-2018
     * Author: Imran
     * Parameter: Journal Code
     * Description: This function will return Only First Rows of Journals by Journal Code
     */
    public static function GetJournalByCode($joural_code)
    {

        $journal = DB::table('journal')->where('subtitle', $joural_code)->first();
        return $journal;
    }

    /*
     * Date: 03-09-2022
     * Author: Imran
     * Parameter: Journal title
     * Description: This function will return Only First Rows of Journals by Journal title
     */
    public static function GetJournalByTitle($journal_title)
    {

        $journal = DB::table('journal')->whereRaw('REPLACE(REPLACE(REPLACE(REPLACE(LOWER(title)," ","-"), "`", ""), "&", "and"), "---", "-") = "'.$journal_title.'"')->select('journal_id')->first();
        return $journal;
    }


    /*
     * Date: 20-08-2018
     * Date: 28-08-2018 Arsal
     * Author: Imran Khan
     * Parameter: id, Search
     * Description:  this function will return journal list, selected journal and search journal
     */
    public static function GetJournalList($search = "",$pagesize=10)
    {

        $journal = DB::table('journal as j');
        $journal = $journal->leftjoin('users as u', 'u.id', '=', 'j.created_by');
        $journal = $journal->select('*');

        if ($search != "") {

            $journal = $journal->where('j.title', 'like', '%' . $search . '%')
                ->orwhere('j.subtitle', 'like', '%' . $search . '%');

           if (strtolower($search) == "rental") {     
            $journal = $journal->orwhere('j.journal_rental', '=', 'Y');
            }
        }

        $journal = $journal->orderBy('j.title', 'ASC');
        $journal = $journal->paginate($pagesize);

        return $journal;
    }

    /*
     * Date: 20-08-2018
     * Author: Imran Khan
     * Parameter: None
     * Description:  this function will return the journal type
     */
    public static function JournalType()
    {
        $journal_type = DB::table('journal_type')->orderBy('journal_type_id', 'DESC')->get();
        return $journal_type;
    }

    /*
     * Date: 20-08-2018
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will return the pubilsher data
     */
    public static function Publisher($id = "")
    {
        if ($id != "") {
            $journal = DB::table('publisher')->where('publisher_id', $id)->get();
        } else {
            $publisher = DB::table('publisher')->get();
        }

        return $publisher;
    }

    /*
     * Date: 20-08-2018
     * Author: Imran Khan
     * Parameter: data[]
     * Description:  this function will return the pubilsher data
     */
    public static function Add($data)
    {
        $id = DB::table('journal')->insertGetId($data);
        return $id;
    }

    /*
     * Date: 28-08-2018
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will delete the selected journal
     */
    public static function DeleteJournal($id)
    {
        DB::table('journal')->where('journal_id', $id)->delete();
        return $id;
    }

    /*
     * Date: 28-08-2018
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will update the selected journal
     */
    public static function JournalUpdate($data)
    {
        DB::table('journal')->where('journal_id', $data['journal_id'])->update($data);
    }

    /*
     * Date: 28-08-2018
     * Author: Arsal Athter
     * Parameter: none
     * Description:  this function will return the year list
     */
    public static function GetYear()
    {
        $year = DB::table('year')->orderBy('year', 'ASC')->pluck('year', 'id');
        return $year;
    }
	
	 /*
     * Date: 01-11-2018
     * Author: Imran Khan
     * Parameter: lettersearch,journal_search,year
     * Description:  this function will return the jounal detail which search from bypublication page
     */
    public static function SearchJournalList($lettersearch="",$journal_search="",$year="",$subscribe=false,$ip =null)
    {
        if($subscribe){
            if (session()->has('ip_base_access') || session()->has('user_base_access')) 
			{
				
                $session_data = session()->get('access_user_data');                
				$user_ids = '';
				foreach($session_data as $a_k => $a_v)
				{
					$user_ids .= $a_v->user_id . ',';
					$access_type_on   = explode(",",$a_v->access_type_on);
				}
				
				$user_id = rtrim($user_ids,',');
				
                $journal_volume=DB::table('journal as ja')->where('journal_id','0');
                $journal_issue=DB::table('journal as ja')->where('journal_id','0');
                $journal_article=DB::table('journal as ja')->where('journal_id','0');

                if(in_array("v", $access_type_on))
				{ 
					$tbl = array();
					$ua_vol_tbl = array('trail','corporate','token');
					foreach($ua_vol_tbl as $v)
					{
						$journal_volume = DB::table('journal as jv');
						$journal_volume = $journal_volume->select('jv.*');
						if ($lettersearch != "") {
							$journal_volume = $journal_volume->where('jv.title', 'like', $lettersearch . '%');
						}
						if ($year != "") {
							$journal_volume = $journal_volume->whereRaw("DATE_FORMAT(FROM_UNIXTIME(created),'%Y')='$year'");
						}
						if ($journal_search != "") 
						{
							$journal_volume = $journal_volume->where('jv.title', 'like', '%' . $journal_search . '%')
								->orwhere('jv.subtitle', 'like', '%' . $journal_search . '%')
								->orwhere('jv.issn', 'like', '%' . $journal_search . '%')
								->orwhere('jv.eissn', 'like', '%' . $journal_search . '%');
						}        
						
						$journal_volume = $journal_volume->join('user_access_journal_volume_'.$v.' as uaj', 'uaj.journal_id', '=', 'jv.journal_id');
						
						if (!empty($user_id))
						{
							$journal_volume = $journal_volume->whereRaw('uaj.user_id in ('. $user_id.')');
						}
						if (!empty($ip))
						{
							$journal_volume = $journal_volume->whereRaw(' (from_ip <= '.ip2long($ip).' and to_ip >= '.ip2long($ip).' ) ');
						}
						
						$tbl[] = $journal_volume;
						$journal_volume = '';
					}
					
					$journal_volume = $tbl[0]->union($tbl[1])->union($tbl[2]);
					
					//$journal_volume = '('.$tbl[0].') union ('.$tbl[1].') union ('.$tbl[2].')';
				
                }
                if(in_array("i", $access_type_on)){   

                    $journal_issue = DB::table('journal as ji');
                    $journal_issue = $journal_issue->select('ji.*');
                    if ($lettersearch != "") {
                        $journal_issue = $journal_issue->where('ji.title', 'like', $lettersearch . '%');
                    }
                    if ($year != "") {
                        $journal_issue = $journal_issue->whereRaw("DATE_FORMAT(FROM_UNIXTIME(created),'%Y')='$year'");
                    }
                    if ($journal_search != "") {
                        $journal_issue = $journal_issue->where('ji.title', 'like', '%' . $journal_search . '%')
                            ->orwhere('ji.subtitle', 'like', '%' . $journal_search . '%')
                            ->orwhere('ji.issn', 'like', '%' . $journal_search . '%')
                            ->orwhere('ji.eissn', 'like', '%' . $journal_search . '%');
                    }         
                    $journal_issue = $journal_issue->join('user_access_issue as uai', 'uai.journal_id', '=', 'ji.journal_id');
					if (!empty($user_id))
					{
						$journal_issue = $journal_issue->whereRaw('uai.user_id in ('. $user_id.')');
					}
                    
                }
                if(in_array("a", $access_type_on)){  

                    $journal_article = DB::table('journal as ja');
                    $journal_article = $journal_article->select('ja.*');
                    if ($lettersearch != "") {
                        $journal_article = $journal_article->where('ja.title', 'like', $lettersearch . '%');
                    }
                    if ($year != "") {
                        $journal_article = $journal_article->whereRaw("DATE_FORMAT(FROM_UNIXTIME(created),'%Y')='$year'");
                    }
                    if ($journal_search != "") {
                        $journal_article = $journal_article->where('ja.title', 'like', '%' . $journal_search . '%')
                            ->orwhere('ja.subtitle', 'like', '%' . $journal_search . '%')
                            ->orwhere('ja.issn', 'like', '%' . $journal_search . '%')
                            ->orwhere('ja.eissn', 'like', '%' . $journal_search . '%');
                    }                 
                    $journal_article = $journal_article->join('user_access_article as uaa', 'uaa.journal_id', '=', 'ja.journal_id');
					if (!empty($user_id))
					{
						$journal_article = $journal_article->whereRaw('uaa.user_id in ('. $user_id.')');
					}
                    
                }

                $results = DB::table(DB::raw("(select * from ({$journal_volume->toSql()} union {$journal_issue->toSql()} union {$journal_article->toSql()}) as tmp group by tmp.journal_id order by tmp.title) as teemp"));

               
                if($journal_volume){
                    $results->mergeBindings( $journal_volume );
                }if($journal_issue){
                    $results->mergeBindings( $journal_issue );
                }if($journal_article){
                    $results->mergeBindings( $journal_article );
                }

                $results = $results->paginate(10);

                return $results;

            }else{
                //return abort(403, 'Unauthorized action.');
                return false;
            }
        }else{

            $journal = DB::table('journal as j');
            $journal = $journal->select('*');


            if ($lettersearch != "") {

                $journal = $journal->where('j.title', 'like', $lettersearch . '%');

            }
            if ($year != "") {
                $journal = $journal->whereRaw("DATE_FORMAT(FROM_UNIXTIME(created),'%Y')='$year'");
            }
            if ($journal_search != "") {

                $journal = $journal->where('j.title', 'like', '%' . $journal_search . '%')
                    ->orwhere('j.subtitle', 'like', '%' . $journal_search . '%')
                    ->orwhere('j.issn', 'like', '%' . $journal_search . '%')
                    ->orwhere('j.eissn', 'like', '%' . $journal_search . '%');

            }
            $journal =$journal->where('j.published', 1);
            $journal = $journal->orderBy('j.title', 'ASC');
            $journal = $journal->paginate(10);

            return $journal;
        }
    }

    /*
     * Date: 01-11-2018
     * Author: Imran Khan
     * Parameter: lettersearch,journal_search,year
     * Description:  this function will return the jounal detail which search from bypublication page
     */
    public static function SearchSubscriptionList($param)
	{
		if(!empty($param['user_id']))
		{
			$where = '((from_ip <= '.$param['ip_address'].' and to_ip >= '.$param['ip_address'].' ) 
							or 
							(from_ip is null and to_ip is null and user_id = '.$param['user_id'].')
                            or
                            (user_id = '.$param['user_id'].')
                            
                        )';
		}
		else
		{
			$where = '(from_ip <= '.$param['ip_address'].' and to_ip >= '.$param['ip_address'].' )';
			
		}
		
		$current_date = time();
		
		$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))';
		
		$t1 = DB::table('user_access_journal_volume_trail')->select('journal_id')->whereRaw($where);
		$t2 = DB::table('user_access_journal_volume_corporate')->select('journal_id')->whereRaw($where);
		$t3 = DB::table('user_access_journal_volume_token')->select('journal_id')->whereRaw($where);
		
		$t4 = DB::table('user_access_issue_trail')->select('journal_id')->whereRaw($where);
		$t5 = DB::table('user_access_issue_corporate')->select('journal_id')->whereRaw($where);
		$t6 = DB::table('user_access_issue_token')->select('journal_id')->whereRaw($where);
		
		$t7 = DB::table('user_access_article_trail')->select('journal_id')->whereRaw($where);
		$t8 = DB::table('user_access_article_corporate')->select('journal_id')->whereRaw($where);
		$t9 = DB::table('user_access_article_token')->select('journal_id')->whereRaw($where);
		
		
		$query = $t1->union($t2)->union($t3)->union($t4)->union($t5)->union($t6)->union($t7)->union($t8)->union($t9);
		$query = $query->get()->toArray();
		
		foreach($query as $k => $v)
		{
			$journal_ids[] =  $v->journal_id;
		}
		
		if (!empty($journal_ids))
		{
			$journal_volume = DB::table('journal')->select('*')->whereIn('journal_id',$journal_ids);

			if(!empty($param['year']))
			{
				$journal_volume = $journal_volume->whereRaw('DATE_FORMAT(FROM_UNIXTIME(created),"%Y") = '.$param['year']);
			}
			
			if(!empty($param['letter']))
			{
				$journal_volume = $journal_volume->orwhere('title', 'like', '%' . $param['letter'] . '%');
			}
			
			if(!empty($param['journal_name']))
			{
				$journal_volume = $journal_volume->where('subtitle', 'like', '%' . $param['journal_name'] . '%');
				$journal_volume = $journal_volume->orwhere('title', 'like', '%' . $param['journal_name'] . '%');
				$journal_volume = $journal_volume->orwhere('issn', 'like', '%' . $param['journal_name'] . '%');
				$journal_volume = $journal_volume->orwhere('eissn', 'like', '%' . $param['journal_name'] . '%');
			}			
            
            $journal_volume = $journal_volume->orderBy('title', 'ASC');

			return $journal_volume->paginate(10);
		}
		else
		{
			return false;	
		}
		
		
	}

   
    /*
     * Date: 22-11-2018
     * Author: Imran Khan
     * Parameter: journal_id
     * Description: This function will Return journal open access issue volumes
     */
    public static function getFreeOpenAccess_IssuesVolumes($journal_id) {

        // Free or Open access volume level. 
        // 1 for Free and 2 for Open Access and 0 for both (free and open access)
        
        $sql = "select distinct v.volume_name as volume_name,v.volume_id as volume_id, y.year 
                from journal_access j join issue i on i.issue_id = j.content_type_id  
                inner join volume v on v.volume_id = i.volume_id inner join month m on m.id = i.month_id inner join year y on y.id = v.year_id
                where j.content_type = 'i' and i.journal_id = $journal_id and  (content_type_access_id = 1 or content_type_access_id = 2)  order by (v.volume_name+0) desc";
        //print($sql);
        $result = DB::select($sql);
        return $result;
    }

    /*
     * Date: 22-11-2018
     * Author: Imran Khan
     * Parameter: journal_id
     * Description: This function will Return journal open access Issues
     */
   public static function getFreeOpenAccess_Issues($vl_id) {

        // Free or Open access volume level. 
        // 1 for Free and 2 for Open Access and 0 for both (free and open access)
        
        $sql = "select content_type_id ,content_type_access_id,
                if(content_type_access_id=1,'free','openaccess') as access_type ,
                i.issue_id as issue_id,
                i.title as title,
                v.volume_id as volume_id,
                y.year
                from journal_access j join issue i on i.issue_id = j.content_type_id  
                inner join volume v on v.volume_id = i.volume_id inner join month m on m.id = i.month_id inner join year y on y.id = v.year_id
                where j.content_type = 'i' 
                and v.volume_id = $vl_id and  (content_type_access_id = 1 or content_type_access_id = 2) order by i.title desc";
        //print($sql);
        $result = DB::select($sql);
        return $result;

    }

    /*
     * Date: 25-11-2018
     * Author: Imran Khan
     * Parameter: journal_id
     * Description: This function will Return journal free open access articles
     */
    public static function GetFreeOpenAccessArticles($journal_id, $access_type)
    {
        $conditions = "(((from_date Is Not Null and from_date  <= CURDATE()) and (to_date Is Not Null and to_date >= CURDATE()) and perpetual= 0) or (from_date is null and to_date is null and perpetual= 1))";

     $result = DB::table('article as a')
        ->selectRaw('a.*,a.article_id as article_id,v.volume_name as volume_name,i.title as issue,m.month,y.year,e.from_date,e.to_date,e.perpetual, at.type_name article_type_name')
        ->join('journal_access as e', 'a.article_id', '=', 'e.content_type_id')
        ->leftJoin('issue as i', 'a.issue_id', '=', 'i.issue_id')
        ->join('volume as v', 'a.volume_id', '=', 'v.volume_id')
        ->leftJoin('month as m', 'i.month_id', '=', 'm.id')
        ->leftJoin('article_type as at' , 'at.art_type_id', '=', 'a.art_type') 
        ->join('year as y', 'v.year_id', '=', 'y.id')
        ->whereRaw("e.content_type = 'a' and a.journal_id = $journal_id and content_type_access_id = $access_type and $conditions")
        ->orderByRaw('cast(v.volume_name+0 as signed) desc')
        ->orderByRaw('cast(i.title as signed) desc')
        ->orderBy("first_page","desc")->paginate(10);
        return $result;
        
    }

     /*
     * Date: 21-12-2018
     * Author: Arshad
     * Parameter: journal_id
     * Description: This function will Return journal most cited articles
     */
    public static function GetMostCitedArticles($journal_id,$pagesize=0)
    {
       
     $article = DB::table('article')
        ->select('article.*','article_type.type_name as article_type_name','month.month','year.year','volume.volume_name as volume_name', 'volume.volume_id as volume_id', 'issue.issue_id as issue_id' ,'issue.title as issue')
        ->leftJoin('article_type', 'article_type.art_type_id', '=', 'article.art_type')        
        ->leftjoin('journal_access', function($query) {
                        $query->on('article.article_id', 'journal_access.content_type_id')
                              ->on('journal_access.content_type', '=', DB::raw("'a'"));
                    })        
        ->leftJoin('issue', 'article.issue_id', '=', 'issue.issue_id')
        ->join('volume', 'article.volume_id', '=', 'volume.volume_id')
        ->leftJoin('month', 'issue.month_id', '=', 'month.id')
        ->join('year', 'volume.year_id', '=', 'year.id')
        ->whereRaw("article.is_uploaded = 1   and article.journal_id = $journal_id and article.citation > 0  and article.is_epub = 0 ")
        ->orderByRaw("cast(article.citation as SIGNED) desc");

        if($pagesize>0){
            $article = $article->paginate($pagesize);
        }else{
            $article =  $article->get();
        }
        
        return $article;
        
    }


    /*
     * Date: 24-12-2018
     * Author: Arshad
     * Parameter: journal_id
     * Description: This function will Return journal most downloaded articles
     */
    public static function getMostDownloadedArticles($journal_nid,$volumeno="",$issueno="",$pagesize=10)
    {
    
    $ga_url  = '/images/graphical-abstract/';
    $last_year = date('Y')-1;

    $article = DB::table('download_count_articles as d')
        ->selectraw('j.subtitle,v.volume_name,v.volume_name as volume,i.title as issue,d.article_id,a.issue_id,a.volume_id,a.journal_id,a.publish_on,a.title,a.title article_title,a.doi article_doi, a.pmid,a.art_type, a.nid,a.nid as article_nid,a.is_epub, first_page, last_page, page_count, a.doi, m.month,y.year,count(*), at.type_name as article_type_name,a.graph_abs_sm_filename,a.graph_abs_lg_filename,concat("'.$ga_url.'",LOWER(j.subtitle),"/",v.volume_name,"/",i.title,"/",a.graph_abs_sm_filename) as graph_abstract_small,concat("'.$ga_url.'",LOWER(j.subtitle),"/",v.volume_name,"/",i.title,"/",a.graph_abs_lg_filename) as graphical_abstract_large')
        ->Join('article as a', 'a.article_id', '=', 'd.article_id')              
        ->leftJoin('article_type as at', 'at.art_type_id', '=', 'a.art_type')              
        ->join('journal as j', 'j.journal_id', '=', 'a.journal_id')
        ->join('volume as v', 'v.volume_id', '=', 'a.volume_id')
        ->leftJoin('issue as i', 'i.issue_id', '=', 'a.issue_id')        
        ->leftJoin('month as m', 'i.month_id', '=', 'm.id')
        ->join('year as y', 'v.year_id', '=', 'y.id')
        ->whereRaw("a.is_uploaded = 1   and j.nid = $journal_nid and y.year >= $last_year and a.art_type not in (4,6,14,18,17,22,21,43,31,32,39,35,60,57,52,66) and FROM_UNIXTIME(timestamp) >= now()-interval 6 month ");

        if($volumeno != "" and $volumeno != "0") {
            $article = $article->whereRaw(" v.volume_name = $volumeno");
        }
        if($issueno != "" and $issueno != "0") {            
            $article = $article->whereRaw(" i.title = $issueno");
        }       
        $article->groupBy('d.article_id');
        $article->orderByRaw("count(*) desc");
        
        if($pagesize>=10){            
            if($pagesize > 20) $pagesize = 20;
            $article = $article->paginate($pagesize);
        }else{
            $article =  $article->get();
        }

        return $article;
    }

    /*
     * Date: 27-02-2019
     * Author: Imran Khan
     * Parameter: data[]
     * Description:  this function will return the pubilsher data
     */
    public static function AddLibraryRecommendation($data)
    {
        $id = DB::table('library_recommendation')->insertGetId($data);
        return $id;
    }

    /*
     * Date: 07-08-2019
     * Author: Imran Khan
     * Parameter: data[]
     * Description:  this function will add the Journal EBM/Review data
     */
    public static function Add_EBM_Review($data)
    {
        $id = DB::table('tbl_ebm_registration')->insertGetId($data);
        return $id;
    }

    /*
     * Date: 28-02-2019
     * Author: Imran Khan
     * Parameter: data[]
     * Description:  this function will return the pubilsher data
     */
    public static function AddTrialRequest($data)
    {
        $id = DB::table('trial_requests')->insertGetId($data);
        return $id;
    }


    /*
     * Date: 21-08-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will return the Society Name
     */
    public static function GetSocietyById($id)
    {

        $society = DB::table('tbl_society')->where('society_id', $id)->where('status', 'A')->first();
        return $society;
    }

    /*
     * Date: 21-08-2019
     * Author: Imran Khan
     * Parameter: data[]
     * Description:  this function will add the society member request data
     */
    public static function AddTrialSocietyRequest($data)
    {
        $id = DB::table('tbl_society_members')->insertGetId($data);
        return $id;
    }


    /*
     * Date: 24-08-2019
     * Author: Imran Khan
     * Parameter: id, column name
     * Description:  this function will return the list of  Rental details
     */

    public static function GetJournalRentalDetail($id, $column)
    {
        
        $rental_detail = DB::table('journal_rental as jr')
            ->select("jr.*")
            ->join("journal as j","jr.journal_id","=","j.journal_id")
            ->where("jr.".$column, '=', $id)->get();
/*
        $article_detail = DB::select( DB::raw("SELECT ad.* FROM article_abstract_detail ad where ad.$column = $id order by ad.article_id, ad.sequence") );
*/
        if ($rental_detail) {
             return $rental_detail;
        } else {
            return null;
        }
    }

    /*
     * Date: 24-08-2019
     * Author: Imran Khan
     * Parameter: data
     * Description:  this function will store the rental detail
     */


    public static function InsertJournalRentalDetail($data)
    {

        $id = DB::table('journal_rental')->insertGetId($data);
        return $id;
    }

    /*
     * Date: 24-08-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will update the selected rental detail
     */
    public static function InsertRentalDetail($data)
    {
        DB::table('journal_rental')->where('rental_id', '=', $data['rental_id'])->delete();
         $id = DB::table('journal_rental')->insertGetId($data);
         return $id;
    }

    /*
     * Date: 24-08-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will delete the selected rental detail
     */
    public static function RemoveJournalRentalDetail($id)
    {
        DB::table('journal_rental')->where('rental_id', '=', $id)->delete();
    }

    /*
     * Date: 25-06-2020
     * Author: Imran
     * Parameter: journal_id
     * Description: This function will Return journal subscription category
     */
    public static function GetSubsCategory($journal_id)
    {

    $journal = DB::table('tbl_subscription as s')
        ->selectraw('s.*,concat(s.type," - ","Volume ",s.volume_name,", ",s.frequency," Issue (",s.year,")") as subs_category')
        ->join('journal as j', 'j.journal_id', '=', 's.journal_id')
        ->whereRaw("s.journal_id= $journal_id order by subs_id desc")->get();
        return $journal;
    }

    /*
     * Date: 25-06-2020
     * Author: Imran
     * Parameter: type_id
     * Description:  this function will return the subscrition type detail 
    */
    public static function GetSubsCategoryByID($subs_category)
    {        
        $category = DB::table('tbl_subscription as s')
        ->join('journal as j', 'j.journal_id', '=', 's.journal_id')
        ->select('s.*')
        ->where('s.subs_id', '=', $subs_category)->first();
        
            return $category;
    }    

    /*
    * Date: 26-06-2020
    * Author: Imran Khan
    * Parameter: data
    * Description:  this function will add all Subscription Request Form and return last inserted Id
    */
    public static function AddSubscriptionOrder($data) 
    {
        return DB::table('tbl_subscription_detail')->insertGetId($data);
    }

    /*
     * Date: 27-06-2020
     * Author: Imran
     * Parameter: $param
     * Description:  this function will return the article rental detail 
     */
    public static function GetSubscritionOrderDetail($param = null)
    {  
        $subscrition = DB::table('tbl_subscription_detail as sd')->selectRaw('sd.*,concat(s.type," - ","volume ",s.volume_name,", ",s.frequency," issue (",s.year,")") as subs_category, s.type, s.price, s.volume_name, s.frequency, s.year, s.journal_id, j.title as journal_title ')
        ->join('tbl_subscription as s', 'sd.subs_id', '=', 's.subs_id')
        ->join('journal as j', 's.journal_id', '=', 'j.journal_id');

        if (!empty($param['subscrition_order_id'])) {
            $subscrition    = $subscrition->where('sd.subs_detail_id', '=', $param['subscrition_order_id']);
        }

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

        if (!empty($param['status'])) {
            $subscrition   = $subscrition->where('sd.status', '=', ''.$param['status'].'');
        }

        if (!empty($param['user_email'])) {
            $subscrition   = $subscrition->where('sd.email', '=', ''.$param['user_email'].'');
        }

        $subscrition = $subscrition->orderByRaw('sd.subs_detail_id desc');
        
        if (!empty($param['paginate'])) {
            $subscrition = $subscrition->paginate(10);
        }
        else
        {
            $subscrition = $subscrition->get();
        }


       return $subscrition;
    }

    /*
    * Date: 27-06-2020
    * Author: Imran
    * Parameter: data
    * Description:  this function will Update all Subscription Order 
    */
    
    public static function UpdateSubscritionOrder($subscrition_order_id,$data)
    {   
        DB::table('tbl_subscription_detail')->where('subs_detail_id', $subscrition_order_id)->update($data);
    }
	
	/*
    * Date: 03-03-2022
    * Author: Arshad
    * Parameter: Param
    * Description:  this function will get the continuous journal list
    */
	public static function getContinuedJournalList($param='')
    {

            $journal = DB::table('journal as j');
            $journal = $journal->select('*');


            if (!empty($param)) {

                $journal = $journal->where($param);

            }
            $journal =$journal->where('j.journal_status', 1);
            $journal =$journal->where('j.published', 1);
            $journal = $journal->orderBy('j.title', 'ASC');
            $journal = $journal->paginate(10);

            return $journal;

    }

    /*
    * Date: 03-03-2022
    * Author: Arshad
    * Parameter: Param
    * Description:  this function will get the Journal subscription Rates list
    */

    public static function GetJournalSubscriptionRate($year='')
    {

    $journal = DB::table('tbl_subscription as s')
        ->selectraw("j.title,j.issn,j.journal_id,s.price,s.volume_name,s.frequency,s.year,
        GROUP_CONCAT(CASE WHEN s.type='print-or-online-corporate-rates' THEN s.price END) AS 'poocr',
        GROUP_CONCAT(CASE WHEN s.type='print-and-online-corporate-rates' THEN s.price END) AS 'paocr',
        GROUP_CONCAT(CASE WHEN s.type='print-or-online-academic-rates' THEN s.price END) AS 'pooar',
        GROUP_CONCAT(CASE WHEN s.type='print-and-online-academic-rates' THEN s.price END) AS 'paoar',
        GROUP_CONCAT(CASE WHEN s.type='personal-rates' THEN s.price END) AS 'pr' ")
        ->join('journal as j', 'j.journal_id', '=', 's.journal_id')
        ->where("s.year", $year)->groupBy('j.journal_id')->get();
        return $journal;
    }
    public static function getLanguageByJournal($jid)
    {
        $result = DB::table('multilanguage_journal')
                  ->join('multilanguage','multilanguage.language_id','=','multilanguage_journal.language_id')
                  ->where('multilanguage_journal.journal_id',$jid)
                  ->select('multilanguage_journal.id','multilanguage.language_title','multilanguage_journal.title')
                  ->get();
        return $result;  
    }

    public static function GetAllContinuedJournal($param='')
    {
            $journal = DB::table('journal as j');
            $journal = $journal->select('*');


            if (!empty($param)) {

                $journal = $journal->where($param);

            }
            $journal =$journal->where('j.journal_status', 1);
            $journal =$journal->where('j.published', 1);
            $journal = $journal->orderBy('j.title', 'ASC');
            $journal = $journal->get();

            return $journal;
    }

    /*
     * Date: 08-06-2022
     * Author: Imran Khan
     * Parameter: data[]
     * Description:  this function will add the society member request data
     */
    public static function AddFreeOnlineSubsRequest($data)
    {
        $id = DB::table('free_online_subs_req')->insertGetId($data);
        return $id;
    }
    
    /*
     * Date: 27-06-2022
     * Author: Imran
     * Parameter: $param
     * Description:  this function will return the journal sections detail
     */
    public static function GetJournalSection($param = null)
    {
        $section = DB::table('journal_section as js')->selectRaw('j.subtitle, j.title,js.*')
        ->join('journal as j', 'js.journal_id', '=', 'j.journal_id');

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

        if (!empty($param['published'])) {
            $section    = $section->where('js.js_published', '=', $param['published']);
        }

        if (!empty($param['status'])) {
            $section   = $section->where('js.js_status', '=', ''.$param['status'].'');
        }

        $section = $section->orderBy('js.category_name');
        $section = $section->orderBy('js.js_title');

        if (!empty($param['paginate'])) {
            $section = $section->paginate($param['paginate']);
        }
        else
        {
            $section = $section->get();
        }


       return $section;
    }

    /*
    * Date: 27-06-2022
    * Author: Imran Khan
    * Parameter: data
    * Description:  this function will add all Bundle Request Form and return last inserted Id
    */
    public static function JournalSectionAdd($data) 
    {
        $js_id =  DB::table('journal_section')->insertGetId($data);

        $result = DB::table('journal_section')->where('js_id',$js_id)->update([ 'js_code' => $data['js_code']."-".$js_id]);

        return $js_id;

    }

    /*
     * Date: 27-06-2022
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will update the selected journal
     */
    public static function JournalSectionUpdate($data)
    {
        DB::table('journal_section')->where('js_id', $data['js_id'])->update($data);
    }

    /*
     * Date: 27-06-2022
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will update the selected journal
     */
    public static function JournalSectionDelete($data)
    {
        DB::table('journal_section')->where('js_id', $data['js_id'])->update($data);
    }

    /*
     * Date: 26-07-2022
     * Author: Imran Khan
     * Parameter: data[]
     * Description:  this function will add the Journal/eBook Proposal data
    */
    public static function Add_Journal_eBook_Proposal($data)
    {
        $id = DB::table('proposal_journal_ebook')->insertGetId($data);
        return $id;
    }

    /*
     * Date: 04-03-2018
     * Author: Imran
     * Parameter: Journal Code
     * Description: This function will return Only First Rows of Journals by Journal Code
     */
    public static function GetJournalByJMSCode($joural_code)
    {

        $journal = DB::table('journal')->where('jms_jcode', $joural_code)->first();
        return $journal;
    }

	
	/*
    * Date: 21-02-2023
    * Author: Arshad
    * Parameter: journal_id
    * Description:  this function will get the Journal wise reviewer list from MPS
    */

    public static function getReviewersByJournalID($journal_code)
    {
        $reviewers = DB::connection('jms')->table("tbl_reviewer_assignments_log as r")
            ->select("p.suffix","p.first_name","p.last_name","p.field_of_expertise","p.scopus_id","p.orcid","aff.institution")
            ->join('tbl_journals as j', 'j.journal_id', '=', 'r.journal_id')
            ->join('tbl_users as u', 'u.user_id', '=', 'r.reviewer_user_id')
            ->join('tbl_user_profile as p', 'p.user_id', '=', 'u.user_id')
			->join('tbl_user_affiliation as aff', 'aff.user_id', '=', 'u.user_id')
            ->whereRaw('from_unixtime(r.date_completed,"%Y-%m-%d") BETWEEN CURDATE() - INTERVAL 12 MONTH AND CURDATE()')
            ->where('j.journal_code',$journal_code)			
            ->whereRaw('p.scopus_id is not null')
			->where('u.status','A')
			->groupBy('r.reviewer_user_id');
            $reviewers = $reviewers->paginate(10);
			
        return $reviewers;
    }

	public static function GetArticleExplorerCounts($journal_id)
    {
		$q1 = DB::table('article as a')->selectRaw('"most_cited" as article, count(a.article_id) as total ');
		$q1 = $q1->where('journal_id',$journal_id)->where('citation','!=',0);
		
		$q2 = DB::table('download_count_articles as d')->selectRaw('"most_access" as article, count(a.article_id) as total ');
		$q2 = $q2->join('article as a','a.article_id','d.article_id');
		$q2 = $q2->join('article_type as at','at.art_type_id','a.art_type');
		$q2 = $q2->join('journal as j','j.journal_id','a.journal_id');
		$q2 = $q2->join('volume as v','v.volume_id','a.volume_id');
		$q2 = $q2->join('year as y','v.year_id','y.id');
		$q2 = $q2->where('a.is_uploaded',1);
		$q2 = $q2->where('j.journal_id',$journal_id);
		$q2 = $q2->where('y.year',2022);
		$q2 = $q2->whereIn('a.art_type',[4,6,14,18,17,22,21,43,31,32,39,35,60,57,52]);
		$q2 = $q2->whereRaw('FROM_UNIXTIME(TIMESTAMP) >= NOW()-INTERVAL 6 MONTH');
		
		$q3 = DB::table('article as a')->selectRaw('"editor_choice" as article, count(a.article_id) as total ');
		$q3 = $q3->join('journal AS j','j.journal_id','a.journal_id');
		$q3 = $q3->join('volume AS v','v.volume_id','a.volume_id');
		$q3 = $q3->join('issue AS i','i.issue_id','a.issue_id');
		$q3 = $q3->leftJoin('article_type AS at','at.art_type_id','a.art_type');
		$q3 = $q3->join('year AS y','y.id','v.year_id');
		$q3 = $q3->where('j.journal_id',$journal_id);
		$q3 = $q3->whereRaw('(i.is_uploaded = 1 OR i.is_uploaded IS NULL)');
		$q3 = $q3->whereRaw('(is_popular = "Y" OR altmetric_score IS NOT NULL)'); 
		$q3 = $q3->whereNotIn('at.art_type_id',[6, 18, 14, 66, 21, 22]);
		
		return $q1->union($q2)->union($q3);
		
	}

    /*
     * Date: 06-07-2023
     * Author: Zubair
     * Description: This function will return all impacting science journal
     */
    public static function GetImpactingScience()
    {
        $journal = DB::table('journal as j')
        ->select('j.*',DB::raw("(GROUP_CONCAT(ia.title SEPARATOR ', ')) as indexing_title"))
        ->join('indexing_journal as ij','ij.journal_id','j.journal_id')
        ->join('indexing_agencies as ia','ia.index_id','ij.index_id')
        ->where('impact_factor', '!=' , '')
        ->where('journal_status', 1)
        ->groupBy('j.journal_id')
        ->orderBy('j.org_impact_factor', 'DESC')
        ->get();

        return $journal;
    }	 
    public static function GetPublicationFeesbyJMSCode($jms_jcode)
    {
        $publication_fees = DB::table('tbl_publication_fees')->where('journal_code', $jms_jcode)->first();
        return $publication_fees;
	}

}
