<?php

namespace App\Models;

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

class Article extends Model
{
    public $table      = 'article';
    public $timestamps = false;

    /*
     * Author: Arsal
     * Parameter: journal_id, volume_id, issue_id, search
     * Description: This function return all rows of article
     */

    public static function GetArticleList($param = null,$join = null)
    {  
        $article = DB::table('article');
			
		$article = $article->join('journal', 'journal.journal_id', '=', 'article.journal_id');
		$article = $article->addSelect('journal.journal_id','journal.subtitle','journal.journal_type','journal.title as journal_title');
	
		
		if (!empty($join['volume'])) 
		{
			$article = $article->join('volume', 'volume.volume_id', '=', 'article.volume_id');	
			$article = $article->addSelect('volume.volume_id','volume.volume_name');
		}
		
		if (!empty($join['issue'])) 
		{
			$article = $article->leftjoin('issue', 'issue.issue_id', '=', 'article.issue_id');	
			$article = $article->addSelect('issue.issue_id','issue.title as issue_title');
		}
		
		$article = $article->addSelect('article.*');
        
        if (!empty($param['journal_id'])) {
            $journal_id = $param['journal_id'];
            $article    = $article->where('article.journal_id', '=', $journal_id);
        }

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

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

        if (isset($param['article_type']) && $param['article_type'] <> 7) {
            $article_type = $param['article_type'];
            $article = $article->where('article.is_epub', '=', $article_type);
        }

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

        if (!empty($param['title_search'])) {
            $search  = $param['title_search'];
            $article = $article->whereRaw('lower(article.title) like (?)',["%{$search}%"]);
        }

        if (!empty($param['doi_search'])) {
            $doi  = $param['doi_search'];
            $article = $article->where('article.doi', 'like', '%' . $doi . '%');
        }

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

        if(!empty($param['fromdate']) && !empty($param['todate'])){
            $from_date = strtotime($param['fromdate']." 00:00:00");
            $to_date = strtotime($param['todate']." 23:59:59");            
            $article = $article->whereRaw("(article.created >= '".$from_date."' and article.created <= '".$to_date."')" );
        }

        //$article = $article->orderBy('article.last_modified', 'DESC');
        
        if (!empty($param['paginate'])) 
        {
            $article = $article->orderBy('article.first_page', 'asc');
            $perPage    =   $article->count();
            $article = $article->paginate($perPage);
        }
        else
        {
            $article = $article->orderBy('article.last_modified', 'DESC');
            $article = $article->paginate(10);
        }

        return $article;
    }

    /*
     * Author: Arsal
     * Parameter: article_id
     * Description: This function return all rows of article by article_id
     */
    public static function GetArticleById($id)
    {
        $article = DB::table('article')->where('article_id', '=', $id)->first();
        return $article;
    }

     public static function GetArticleByNID($nid)
    {
        $article = DB::table('article')->where('nid', '=', $nid)->first();
        return $article;
    }

    public static function GetArticleByDOI($doi)
    {
        $article = DB::table('article')->where('doi', '=', $doi)->first();
        return $article;
    }

    /*
     * Author: Arsal
     * Parameter: article_id
     * Description: This function return all rows of article_type by is_abstract
     */
    public static function GetArticleTypeList()
    {
        $article_type = DB::table('article_type')->where('is_abstract', 'y')->orderBy('type_name', 'asc')->get();
        return $article_type;
    }

    /*
     * Date: 12-09-2018
     * Author: Muhammad Arshad
     * Parameter: artcile type id
     * Description:  this function will return the name of article type
     */
    public static function GetArticleTypeById($type_id)
    {
        $article_type = DB::table('article_type')->where('art_type_id', $type_id)->get();
        return $article_type;
    }

    /*
     * Author: Arsal
     * Parameter: journal_id, journal_id(column name)
     * Description: This function return all rows of article by journal_id
     * Date: 02-11-2018
     * Modified: Imran Khan
     * Parameter: id, field(column name)
     * Description: This function return all article by issue id
     */
    public static function GetarticleByJournalId($id, $field)
    {
        $article = DB::table('article')->where($field, '=', $id)
        ->orderBy('article_id', 'desc')
        ->get();
        return $article;

    }

    /*
     * Author: Arsal
     * Parameter: article_id
     * Description: This function is using for dropdown only,
        Get title as value and article_id as key for drop down list
     */
    public static function GetarticleTitleById($id)
    {
        $journal = DB::table('article')->where('article_id', '=', $id)
            ->pluck('title', 'article_id');
        return $journal;
    }

    /*
     * Author: Arsal
     * Parameter: article_id
     * Description: This function is using for dropdown only,
    Get title as value and article_id as key for drop down list
     */
    public static function GetarticleByVolumeId($id)
    {
        $journal = DB::table('article')->where('volume_id', '=', $id)
            ->pluck('title', 'article_id');
        return $journal;
    }

    public static function RemovearticleById($id)
    {

        DB::table('article')->where('article_id', '=', $id)->delete();
    }

    public static function save_meta($data)
    {

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

   
    /*
     * Author: Arsal
     * Parameter: journal_id, volume_id, issue_id
     * Description: This function return article rows
     */
    public static function GetJournalArticleList($param)
    {

		$journal_id = isset($param['journal_id']) ? $param['journal_id'] : '';
		$volume_id = isset($param['volume_id']) ? $param['volume_id'] : '';
		$issue_id = isset($param['issue_id']) ? $param['issue_id'] : '';
		$is_epub = isset($param['is_epub']) ? $param['is_epub'] : '';
		

        $conditions = " where 1";

        if ($journal_id) {

            $conditions .= " and a.journal_id =" . $journal_id;
        }
        if ($volume_id) {

            $conditions .= " and a.volume_id =" . $volume_id;
        }

        if ($issue_id) {

            $conditions .= " and a.issue_id =" . $issue_id;
        }

        if (isset($is_epub)) {

            $conditions .= " and a.is_epub =" . $is_epub;
        }

        if ($is_epub == 4) {
            $order = "epub_abstract_date";
        } elseif ($is_epub == 1) {
            $order = "epub_full_text_date";
        } else {
            $order = "epub_normal_date";
        }

        $sql = "select * from article a " . $conditions . " order by " . $order . " desc";

        $articles = DB::select($sql);
        return $articles;

    }

    /*
     * Author: Arsal
     * Parameter: journal_id, volume_id, issue_id
     * Description: This function return article rows
     */
    public static function GetJournalArticleMetadata($param)
    {

        $journal_id = $param['journal_id'];
        $volume_id  = $param['volume_id'];
        $issue_id   = $param['issue_id'];

        $articles = DB::table('article');

        if (!empty($journal_id)) {
            $articles = $articles->where('journal_id', '=', $journal_id);
        }
        if (!empty($volume_id)) {
            $articles = $articles->where('volume_id', '=', $volume_id);
        }
        if (!empty($issue_id)) {
            $articles = $articles->where('issue_id', '=', $issue_id);
        }

        $articles = $articles->get();

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

    }

     /*
     * Date: 24-09-2018
     * Author: Muhammad Arshad
     * Parameter: issue_id, journal_id
     * Description:  this function will return the  Article path 
     */

    public static function getArticlePath($issue_id, $journal_id)
    {
        //If issueid > 0 then article else epub
        if($issue_id > 0)
        {
            $sql = "select concat('JOURNALS', '/', p.title, '/', j.subtitle, '/', v.volume_name, '/', i.title, '/') as path FROM    issue i JOIN    journal j ON j.journal_id = i.journal_id JOIN    publisher p ON j.publisher_id = p.publisher_id join    volume v on v.volume_id = i.volume_id WHERE   i.issue_id = $issue_id";        
        }
        else
        {
            $sql = "select concat('JOURNALS', '/', p.title, '/', j.subtitle, '/') as path FROM    journal j  JOIN    publisher p ON j.publisher_id = p.publisher_id WHERE   j.journal_id = $journal_id";
        }

       $result = DB::select($sql);    
        
        if($result) 
            return $result[0]->path;
        else
            return '';
    }

    /*
     * Date: 24-09-2018
     * Author: Muhammad Arshad
     * Parameter: journal_id, volume_id, issue_id,article number, track
     * Description:  this function will return the  Article DOI 
     */

   public static  function GetArticleDOI($journal_id, $volume_id, $issue_id,$article_no,$track) {
    
        $doi = "";
        $query = "select j.issn,year,v.volume_name as volume_no,i.title as issue_no 
                    from journal j 
                    inner join volume v on j.journal_id = v.journal_id 
                    inner join year y on y.id = v.year_id 
                    left join issue i on i.volume_id = v.volume_id
                    where j.journal_id =".$journal_id." 
                    and v.volume_id = ".$volume_id;
                
        if($track=="0") {       
            $query .= "  and i.issue_id = ".$issue_id;
        }   
        
        $query .=" limit 1 ";
        
        
        $result = DB::select($query);           
        foreach($result as $row) {
            
            $issn = trim(str_replace("-","",$row->issn));
            $year = substr(trim($row->year),2,2);
            $volume_no = trim($row->volume_no);
            
            if($track=="0") $issue_no = trim($row->issue_no);
            if($track=="1") $issue_no = "999"; //epub track
            if($track=="2") $issue_no = "888"; //quick track
            if($track=="3") $issue_no = "777"; //express track
            if($track=="4") $issue_no = "666"; //Epub Abstract track
            if($track=="5") $issue_no = "555"; //Supplementary
            
            date_default_timezone_set("Asia/Karachi");
            $current_timestamp = date("ymdHis",time());
            //$first_page = $article_no;
            
            if($volume_no < 10) $volume_no = "0".$volume_no;
            if($issue_no < 10) $issue_no = "0".$issue_no;
                        
            $doi = "10.2174/".$issn.$volume_no.$issue_no.$current_timestamp;
            
            
            $doi = trim($doi);
            
        }

      // print("Doi = ".$doi); exit;
        return $doi;
    }

    /*
     * Date: 26-09-2018
     * Author: Imran
     * Parameter: article_id
     * Description:  this function will return the Article detail by article id 
     */
    public static function GetArticleDetail($article_id)
    {
        $sql = "SELECT at.type_name, p.copyright, p.publisher_location,m.month,y.year,
            a.publish_on, a.nid article_nid,a.article_id, a.pmid, a.issue_id, a.title, a.abstract,a.podcast,a.altmetric_score,a.altmetric_url,a.kudos_url, a.page_count, a.first_page, a.elocator,
            a.last_page, i.publication_date, a.file_name,
            a.file_path, a.doi,  a.is_epub,a.epub_type, a.volume_id, a.is_uploaded, a.editor_choice, a.is_epub, j.image_file_name,
            a.article_no, a.art_type AS article_type, a.supplementary_filename,a.html_filename,a.prc_filename,a.epub_filename, a.graph_abs_lg_filename, a.graph_abs_sm_filename, a.oa_image_lg_filename, a.oa_image_sm_filename, j.journal_banner,a.animated_abstract,a.erratum_in,a.erratum_for,
            (CASE WHEN a.issue_id =0 THEN j.epub_price ELSE j.price END) AS sell_price, j.epub_price, j.nid AS jour_nid, j.title AS journal_title, j.issn, j.eissn,j.journal_id, j.subtitle AS jour_subtitle,
            v.volume_id,v.volume_name AS volume, v.year_id, i.title AS issue, i.month_id ,i.type issue_type,  j.subtitle,j.formerly_title, ac.subtitle AS epub_title,a.crossmark_enabled,a.text_mining_urls,a.license_urls,a.created, a.receivedate, a.revisedate, a.acceptdate,js.js_title
            FROM article a
            LEFT JOIN article_section ac ON a.is_epub = ac.art_sec_id
            LEFT JOIN journal_section js ON a.js_id = js.js_id
            LEFT JOIN article_type at ON a.art_type = at.art_type_id
            LEFT JOIN issue i ON a.issue_id = i.issue_id
            LEFT JOIN month m ON m.id = i.month_id
            LEFT JOIN volume v ON v.volume_id = a.volume_id
            INNER JOIN year y ON y.id = v.year_id 
            INNER JOIN journal j ON j.journal_id = a.journal_id
            INNER JOIN publisher p ON p.publisher_id = j.publisher_id                                                  
            WHERE a.article_id=".$article_id;

           $result = DB::select($sql);    
           // echo $sql; die;
            if($result) 
                return $result[0];
            else
                return '';
    }       
	
	/*
     * Author: Arsal Akhter
     * Parameter: issue_id
     * Description: this function will return all articles by issue id
    */

     public static function getAllArticlesByIssueID($issue_id=0,$pagesize=0,$journal_id=0,$section_id=0)
    {

            $result =  DB::table('article as a')
            ->selectRaw('a.publish_on,a.article_id,a.elocator, a.nid,a.pmid, a.title,a.altmetric_score,a.altmetric_url,a.kudos_url, j.content_type_access_id, first_page, last_page, 
                page_count,abstract,ifnull(is_abstract,"y"), a.graph_abs_lg_filename,a.is_epub, a.is_epub as epub, a.graph_abs_sm_filename,a.journal_id,a.volume_id,a.issue_id,
                a.doi,m.month,y.year, at.type_name article_type_name,a.html_filename,a.prc_filename,a.epub_filename,v.volume_name,i.title as issue, i.type as issue_type, js.js_title, js.js_id, 
				(select GROUP_CONCAT(CONCAT(COALESCE(first_name, ""),"", last_name)) from author aa where aa.article_id = a.article_id) as authors')
				
            ->leftJoin('article_type as at' , 'at.art_type_id', '=', 'a.art_type') 
            ->leftJoin('journal_section as js' , 'js.js_id', '=', 'a.js_id') 
            ->leftjoin('journal_access as j', function($query) {
                        $query->on('a.article_id', 'j.content_type_id')
                              ->on('j.content_type', '=', DB::raw("'a'"));
                    })
            ->leftJoin('issue as i' , 'a.issue_id', '=', 'i.issue_id') 
            ->leftJoin('month as m' , 'i.month_id', '=', 'm.id') 
            ->join('volume as v' , 'a.volume_id', '=', 'v.volume_id')  
            ->join('year as y', 'v.year_id', '=', 'y.id');
            if($issue_id > 0)
            {
                $result = $result->whereRaw('a.article_status != "W" and a.is_uploaded = 1 and a.issue_id = '.$issue_id.'');
            }
            if($journal_id > 0 && $section_id > 0)
            {
    	        $result = $result->whereRaw('a.article_status != "W" and js.js_id = '.$section_id.' and a.journal_id = '.$journal_id.' and a.is_uploaded = 1 ');
            }

            $result = $result->groupBy('a.article_id')
            ->orderByRaw('cast(a.article_no as SIGNED) asc')
			->orderByRaw('a.publish_on asc');
            if($pagesize>0){
                $result = $result->paginate($pagesize);
            }else{
                $result = $result->get();
            }
            return $result;  
    }
	
	/*
     * Author: Arsal Akhter
     * Parameter: journal_id
     * Description: this function will return all articles by journal id
    */
	
	public static function GetTrackArticle($journal_id)
    {

       $query = "SELECT distinct a.article_id,a.doi,j.subtitle,v.volume_name,a.volume_id as VolumeId, a.title,first_page, abstract, last_page, page_count,abstract,ifnull(is_abstract,'y'),
					a.article_id as ArticleID ,is_epub,epub_price,a.publish_on
					FROM article a
					left join article_type at on at.art_type_id = art_type 
					inner join journal j on j.journal_id = a.journal_id  
					inner join volume v on v.volume_id = a.volume_id";
					
		$query .=" where a.journal_id = $journal_id and a.is_uploaded = 1"; 
		$query .= " order by a.last_modified  desc "; 
					

		$track_article = DB::select($query);
					
        return $track_article;
    }
	
	/*
     * Author: Arsal Akhter
     * Parameter: journal_id, track_id
     * Description: this function will return all articles Record
    */
	
	public static function GetTrackList($journal_id,$track_id,$pagesize)
    {
		$result =  DB::table('article as a')
		
            ->selectRaw('a.nid,a.publish_on, a.pmid,a.article_id,a.doi,j.journal_id,j.subtitle,v.volume_name,a.volume_id as volume_id, 
						a.issue_id,a.title,first_page, last_page, 
						page_count,abstract,ifnull(is_abstract,"y"),asec.title as article_section,at.type_name as article_type_name,
						a.article_id as ArticleID ,is_epub,epub_price,epub_type,a.created, y.year')
			
			->leftJoin('article_section as asec' , 'asec.art_sec_id', '=', 'a.is_epub') 
            ->leftJoin('article_type as at' , 'at.art_type_id', '=', 'a.art_type') 
			->leftJoin('journal as j' , 'j.journal_id', '=', 'a.journal_id') 
            ->join('volume as v' , 'a.volume_id', '=', 'v.volume_id')  
            ->join('year as y', 'v.year_id', '=', 'y.id')
            ->whereRaw('a.article_status != "W" and a.is_epub = '.$track_id.' and a.journal_id = '.$journal_id.' and a.is_uploaded = 1 ')
			->groupBy('a.article_id')
            ->orderBy('a.last_modified', 'DESC');
            if($pagesize>0)
			{
                $result = $result->paginate($pagesize);
            }else{
                $result = $result->get();
            }
	        return $result;  
    }

    /*
     * Date: 27-11-2018
     * Author: Arshad
     * Parameter: none
     * Description:  this function will return the Article detail by article id 
     */
    public static function getArticleSectionBySequenceOrder(){
    
     return DB::select('select * from article_section where art_sec_id <> 0 order by sequence_id');
     
    }

    /*
     * Date: 04-12-2018
     * Author: Arshad
     * Parameter: none
     * Description:  this function will return the Article in other languages
    */

    public static function getTranslatedlanguages($article_id){
    
        $query = "select * from multilanguage_article ma 
        inner join multilanguage m on m.language_id=ma.language_id  
        where ma.article_id=$article_id";
        
        $result = DB::select($query);
        $cnt    = 0;
        foreach($result as $data) {
            $arr[$cnt] = $data;
            $cnt++;
        }
        
        return $arr;
}

/*
     * Date: 05-10-2018
     * Author: Imran Khan
     * Parameter: journal_id
     * Description: this function will return the get track article count
    */
    public static function GetTrackwithArticleCount($journal_id) {

         $query = "SELECT s.art_sec_id,s.title,count(a.article_id) as total FROM article_section s 
         inner join article a on a.is_epub = s.art_sec_id
         where s.art_sec_id !=0 and journal_id = $journal_id and is_uploaded=1  group by s.art_sec_id order by s.sequence_id asc";
        $result = DB::select($query);
                    
        return $result;

    }

    /*
    Author: Imran Khan
    Date: 17 December,2018
    Description: 
    This function will include all special article like Editor Choice, Highlighted and Popular articles.
    
    param1 = Journal Id
    param2 = is type (1=editor chice, 2 = highlighted, 3 = Popular)
*/

    public static function GetSpecialArticles($journal_id,$type="1",$pagesize=0,$display=false){
    
    $type_text='';   
    $condition=''; 
    if($type=="1") {
    
        $type_text = "editor_choice";
        $condition = " and is_editor_choice = 'Y'";
    }           
    
    if($type=="2") {
        
        $type_text = "highlighted";
        $condition = " and is_highlighted = 'Y'";
    }           
    if($type=="3")
    {
        $type_text = "is_popular";
        $condition = " and ( is_popular = 'Y' or altmetric_score IS NOT NULL) ";
    }   
        
            $result =  DB::table('article as a')
            ->selectRaw('j.subtitle,v.volume_id,v.volume_name as volume_name,i.issue_id,i.title as issue,a.issue_id,a.volume_id,a.journal_id, y.year as volume_year, a.publish_on, a.article_id,a.title, a.pmid,a.abstract, a.nid as article_nid,a.is_epub,a.epub_type,first_page,last_page,page_count,a.doi, at.type_name as article_type_name, "'.$type_text.'" as type, a.graph_abs_lg_filename, a.graph_abs_sm_filename,j.image_file_name,a.nid,
			(select content_type_access_id from journal_access where content_type_id = a.article_id or content_type_id = a.issue_id or content_type_id = a.volume_id  limit 1) as content_type_access_id')
            ->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('article_type as at' , 'at.art_type_id', '=', 'a.art_type') 
            ->join('year as y', 'y.id', '=', 'v.year_id')
            ->whereRaw('j.journal_id = '.$journal_id.' and (i.is_uploaded = 1 or i.is_uploaded is null)  '.$condition. '');
            if($display == true){
                $result = $result->whereNotIn('at.art_type_id', [6,18,14,66,21,22]);
            }
            $result = $result->orderByRaw('cast(v.volume_name as signed) desc')
            ->orderByRaw('cast(i.title as signed) desc');
            if($pagesize>0){
                $result = $result->paginate($pagesize);
            }else{
                $result = $result->get();
            }
            return $result;    
    }
	
    /*
    Author: Arsal Akhter
    Date: 14 Jan,2019
    Description: 
    This function will Return Most Recited articles.
    
    param1 = journal_id
	*/
	
	public static function GetmostcitationArticles($journal_id,$pagesize=0)
	{
		$result =  DB::table('article as a')
		->selectRaw('a.article_id as article_id, a.nid,a.pmid, a.title,a.altmetric_score,a.altmetric_url,a.kudos_url, j.content_type_access_id, first_page, last_page, 
			page_count,abstract,ifnull(is_abstract,"y"), a.is_epub, a.graph_abs_sm_filename as graphical_abstract,
			a.doi,m.month,y.year,v.volume_name as volume, v.volume_id as volume_id , i.issue_id as issue_id , i.title as issue')
		->leftJoin('article_type as at' , 'at.art_type_id', '=', 'a.art_type') 
		->leftjoin('journal_access as j', function($query) {
					$query->on('a.article_id', 'j.content_type_id')
						  ->on('j.content_type', '=', DB::raw("'a'"));
				})
		->leftJoin('issue as i' , 'a.issue_id', '=', 'i.issue_id') 
		->leftJoin('volume as  v' , 'v.volume_id', '=', 'a.volume_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 a.journal_id = '.$journal_id.' and a.citation > 0  and is_epub = 0 ')
		->orderByRaw('cast(a.citation+0 as signed) desc');
		if($pagesize>0)
		{
			$result = $result->paginate($pagesize);
		}else{
			$result = $result->get();
		}
		return $result;    
	}
	
    /*
     * Date: 18-01-2018
     * Author: Imran
     * Parameter: $param
     * Description:  this function will return the downloaded article count 
     */
    public static function GetDownloadedArticle($param = null)
    {  
        $article = DB::table('article as a')->selectRaw('j.title as title, j.subtitle as subtitle,v.volume_name as volume, i.title as issue, a.title as article, u.name as deo, dca.ip_address, count(a.article_id) as total_download')
        ->join('journal as j', 'a.journal_id', '=', 'j.journal_id')
        ->join('download_count_articles as dca', 'a.article_id', '=', 'dca.article_id')
        ->leftJoin('volume as v', 'a.volume_id', '=', 'v.volume_id')
        ->leftJoin('issue as i', 'a.issue_id', '=', 'i.issue_id')
        ->leftJoin('users as u', 'dca.user_id', '=', 'u.id')
        ->whereNotNull('dca.id');

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

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

        if (!empty($param['issue_id'])) {
            $issue_id = $param['issue_id'];
            $article  = $article->where('a.issue_id', '=', $issue_id);
        }
    
        if (isset($param['content_access_type'])) {
            $content_access_type = $param['content_access_type'];
            $article  = $article->where('a.is_epub', '=', $content_access_type);
        }

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

        if (!empty($param['title_search'])) {
            $search  = $param['title_search'];
            $article = $article->where('a.title', 'like', '%' . $search . '%');
        }

        if (!empty($param['user_name'])) {
            $article   = $article->where('u.name', 'like', '%'.$param['user_name'].'%');
        }
        $article = $article->groupBy('a.article_id');

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

        $article = $article->orderByRaw('j.subtitle asc,v.volume_name asc,i.title asc,count(a.article_id) desc');
        $article = $article->paginate(10);

       return $article;
    }

        /*
     * Date: 18-01-2018
     * Author: Imran
     * Parameter: $param
     * Description:  this function will return the downloaded article count 
     */
    public static function GetArticleMetrics($article_id)
    {   
        $article = DB::table('article_metrics')
        ->selectRaw('pdf, html, epub, prc')
        ->where('article_id', '=', $article_id)->first();

       return $article;
    }

    public static function CheckArticleInDiseaseContent($content_id){

        $result = DB::select("select * from content_by_diseases 
                            where content_id = ".$content_id." and content_type = 'article' order by status desc limit 1");
       return $result;

    }

    public static function getDiseaseRalatedArticles($disease){
  
  
        $pagesize=20;
        $column=1;

        $query = "select a.nid article_nid,a.title article_title,j.title journal_title from content_by_diseases cd
                inner join article a on a.article_id=cd.content_id
                inner join journal j on j.journal_id=a.journal_id
                where cd.pid = ".$disease->pid." and cd.sub_disease_id = ".$disease->sub_disease_id." and content_id!=".$disease->content_id." and cd.content_type = 'article'  order by rand() 
                limit $pagesize ";
               
        $disease_result = DB::select($query);    

        return $disease_result;
    }

 /*
 * Date: 31-01-2018
 * Author: Imran Khan
 * Parameter: data
 * Description:  this function will add all files download activities and manage article matrics
 */
    public static function AddArticleDownload($data) {

      $id = DB::table('download_count_articles')->insertGetId($data);

       $time_now= time();
       $day=10;
       $checkquery="select *,to_days(now())-to_days(from_unixtime(timestamp)) as dif FROM article_metrics  WHERE article_id=".$data['article_id']."";
        $checkresult = DB::select($checkquery);   

        $article = DB::table('download_count_articles')
        ->selectRaw('count(CASE WHEN (download_file_type)="1" THEN article_id END) AS "pdf", 
        count(CASE WHEN (download_file_type)="2" THEN article_id END) AS "html",
        count(CASE WHEN (download_file_type)="3" THEN article_id END) AS "epub",
        count(CASE WHEN (download_file_type)="4" THEN article_id END) AS "prc"')
        ->where('article_id', '=', $data['article_id'])->first();
		
		
		  $update_details = array(
				'journal_id'=>$data['journal_id'],
				'volume_id'=>$data['volume_id'],
				'issue_id'=>$data['issue_id'],
				'article_id'=>$data['article_id'],
				'pdf'=>$article->pdf,
				'html'=>$article->html,
				'epub'=>$article->epub,
				'prc'=>$article->prc,
				'timestamp'=>$time_now
			);
		
        if($checkresult)
        {
            if($checkresult['0']->dif >= $day)
            {
                DB::table('article_metrics')
                    ->where('article_id', $data['article_id'])
                    ->update($update_details);
            }
        }
        else
        {
            DB::table('article_metrics')->insert($update_details);
        }

            return $id;
    }
    
 /*
 * Date: 31-01-2018
 * Author: Imran Khan
 * Parameter: user_id, article_id, SecondsGap
 * Description:  this function will return the download article file gap
 */
    public static function ArticleDownloadGap($user_id,$article_id,$SecondsGap) {

        $result = DB::table('download_count_articles')
        ->selectRaw('count(*) rcd,download_count_articles.*')
        ->whereRaw('(user_id = '.$user_id.' and article_id = '.$article_id.') and (unix_timestamp(CURRENT_TIMESTAMP())-timestamp) <= '.$SecondsGap.' 
                        order by timestamp desc')->first();

        return $result;
    }
	
	/*
	* Date: 07-03-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will add all Full Text Inquiry Order and return last inserted Id
	*/
    public static function AddFullTextInquiryOrder($data) 
	{
        return DB::table('tbl_epub_inquiry_order')->insertGetId($data);
    }
	
	/*
	* Date: 07-03-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will Update all Full Text Inquiry Order 
	*/
	
    public static function UpdateFullTextInquiryOrder($inquiry_id,$data)
	{
        return DB::table('tbl_epub_inquiry_order')
		->where('inquiry_id', $inquiry_id)
		->update($data);
    }
	
    /*
    * Date: 27-08-2019
    * Author: Imran Khan
    * Parameter: data
    * Description:  this function will add all Rental Request Form and return last inserted Id
    */
    public static function AddRentalOrder($data) 
    {
        return DB::table('tbl_rental_order')->insertGetId($data);
    }

    /*
     * Date: 27-08-2019
     * Author: Imran
     * Parameter: $param
     * Description:  this function will return the article rental detail 
     */
    public static function GetArticleRentalDetail($param = null)
    {  
        $article = DB::table('article as a')->selectRaw('(CASE 
                        WHEN ro.rental_date_from <= UNIX_TIMESTAMP() && ro.rental_date_to >= UNIX_TIMESTAMP() && ro.order_status="C" THEN "Started"
                        WHEN ro.rental_date_from < UNIX_TIMESTAMP() && ro.rental_date_to < UNIX_TIMESTAMP() && ro.order_status="C" THEN "Completed"
                        WHEN ro.rental_date_from <= UNIX_TIMESTAMP() && ro.rental_date_to >= UNIX_TIMESTAMP() && ro.order_status="I" THEN "In-Process"
                         ELSE "Not Completed" 
                       END) as rental_status,
            j.title as title, j.subtitle as subtitle, j.journal_id as journal_id, v.volume_name as volume, v.volume_id as volume_id, i.title as issue, i.issue_id as issue_id, a.title as article, a.article_id as article_id,a.nid as nid, u.name as name, u.display_name as display_name, u.email as email, u.country as country, jr.rental_id as rental_id, jr.rental_hours as rental_hours, jr.rental_price as rental_price, ro.order_status as order_status, ro.rental_order_id as rental_order_id, ro.user_email as user_email, ro.user_name as user_name, ro.user_country as user_country, ro.created_on as created_on')
        ->join('journal as j', 'a.journal_id', '=', 'j.journal_id')
        ->join('tbl_rental_order as ro', 'a.article_id', '=', 'ro.article_id')
        ->join('journal_rental as jr', 'ro.rental_id', '=', 'jr.rental_id')
        ->join('volume as v', 'a.volume_id', '=', 'v.volume_id')
        ->leftjoin('issue as i', 'a.issue_id', '=', 'i.issue_id')
        ->leftjoin('users as u', 'ro.user_id', '=', 'u.id');

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

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

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

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

        if (!empty($param['article_id'])) {
            $article_id = $param['article_id'];
            $article  = $article->where('a.article_id', '=', $article_id);
        }
    
        if (!empty($param['user_name'])) {
            $article   = $article->where('u.name', 'like', '%'.$param['user_name'].'%');
        }

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

        if (!empty($param['rental_status'])) {
            $article   = $article->whereRaw('(CASE 
                        WHEN ro.rental_date_from <= UNIX_TIMESTAMP() && ro.rental_date_to >= UNIX_TIMESTAMP() && ro.order_status="C" THEN "Started"
                        WHEN ro.rental_date_from < UNIX_TIMESTAMP() && ro.rental_date_to < UNIX_TIMESTAMP() && ro.order_status="C" THEN "Completed"
                        WHEN ro.rental_date_from <= UNIX_TIMESTAMP() && ro.rental_date_to >= UNIX_TIMESTAMP() && ro.order_status="I" THEN "In-Process"
                         ELSE "Not Completed" 
                       END) = "'.$param['rental_status'].'"');
        }

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

        $article = $article->orderByRaw('ro.rental_order_id desc');
        
        if (!empty($param['paginate'])) {
            $article = $article->paginate(10);
        }
        else
        {
            $article = $article->get();
        }


       return $article;
    }

    /*
     * Date: 05-09-2019
     * Author: Imran
     * Parameter: $param
     * Description:  this function will return the article rental access 
     */
    public static function CheckArticleRentalAccess($param = null)
    {  
        $article = DB::table('article as a')->selectRaw('from_unixtime(rental_date_from, "%b, %d, %Y, %H:%i:%s") as frm_date, from_unixtime(rental_date_to, "%b, %d, %Y, %H:%i:%s") as to_date, j.title as title, j.subtitle as subtitle, j.journal_id as journal_id, v.volume_name as volume, v.volume_id as volume_id, i.title as issue, i.issue_id as issue_id, a.title as article, a.article_id as article_id,a.nid as nid, u.name as name, u.display_name as display_name, u.email as email, u.country as country, jr.rental_id as rental_id, jr.rental_hours as rental_hours, jr.rental_price as rental_price, ro.order_status as order_status, ro.rental_order_id as rental_order_id, ro.user_email as user_email, ro.user_name as user_name, ro.user_country as user_country, ro.rental_date_to as rental_date_to, ro.rental_date_from as rental_date_from')
        ->join('journal as j', 'a.journal_id', '=', 'j.journal_id')
        ->join('tbl_rental_order as ro', 'a.article_id', '=', 'ro.article_id')
        ->join('journal_rental as jr', 'ro.rental_id', '=', 'jr.rental_id')
        ->join('volume as v', 'a.volume_id', '=', 'v.volume_id')
        ->leftjoin('issue as i', 'a.issue_id', '=', 'i.issue_id')
        ->join('users as u', 'ro.user_id', '=', 'u.id')
        ->where('ro.order_status', '=', 'C')
        ->where('ro.rental_date_from', '<=', now()->timestamp)
        ->where('ro.rental_date_to', '>=', now()->timestamp);

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

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

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

        $article = $article->orderByRaw('j.subtitle asc,v.volume_name asc,i.title,a.title asc');
        $article = $article->get();

       return $article;
    }

    /*
    * Date: 07-03-2019
    * Author: Arsal Akhter
    * Parameter: data
    * Description:  this function will Update all Full Text Inquiry Order 
    */
    
    public static function UpdateRentalOrder($rental_order_id,$data)
    {
        return DB::table('tbl_rental_order')
        ->where('rental_order_id', $rental_order_id)
        ->update($data);
    }

	/*
	* Date: 07-03-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will Update all Full Text Inquiry Order 
	*/
	
    public static function UpdateMultiUserOrder($inquiry_id,$data)
	{
        return DB::table('tbl_ebook_order')
		->where('inquiry_id', $inquiry_id)
		->update($data);
    }
	
	/*
	* Date: 07-03-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will Update all Full Text Inquiry Order 
	*/
	
    public static function UpdatePrintVersionOrder($poid,$data)
	{
        return DB::table('tbl_ebook_print_order')
		->where('poid', $poid)
		->update($data);
    }
	
		/*
	* Date: 07-03-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will Update all Full Text Inquiry Order 
	*/
	
    public static function UpdatePreOrder($oid,$data)
	{
        return DB::table('tbl_ebook_preorders')
		->where('oid', $oid)
		->update($data);
    }
	
	
	/*
	* Date: 08-27-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will Update all Reprint Order 
	*/
	
    public static function UpdateReprintOrder($reprint_id,$data)
	{
        return DB::table('reprint_order')
		->where('reprint_order_id', $reprint_id)
		->update($data);
    }
	
	/*
	* Date: 07-05-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will return Full Text Inquiry Order
	*/
    public static function GetPrintVersionOrder($param,$join = false) 
	{
		$poid = DB::table('tbl_ebook_print_order as  po');
		
		if ($join == true)
		{
			$poid = $poid->select('ev.ebook_volume_id as ebook_volume_id','ev.nid as ebook_volume_nid',
			'poid','response_message','po.print_promocode','po.discount_rate','po.actual_amount','po.shipment_amount','po.print_copies','po.comments',
			'order_id','order_type','book_title','fname','lname','email','phone','address','country','state','city','zip_code',
			'sfname','slname','semail','sphone','saddress','scountry','sstate','scity','szip_code','status',
			'ev.volume_name as ebook_volume_name','e.subtitle as ebook_subtitle','amount','last_updated');
			$poid = $poid->join('ebook_volume as ev', 'ev.ebook_volume_id', '=', 'po.ebook_volume_id');
			$poid = $poid->join('ebook as e', 'e.ebook_id', '=', 'ev.ebook_id');
		}	
		else
		{
			$poid = $poid->select('*');
		}

        $poid = $poid->where('po.status','!=', 'D');
		
        if (!empty($param['poid']))
		{
			$poid = $poid->where('poid', $param['poid']);
			$poid = $poid->orderBy('poid', 'DESC');
			$poid = $poid->limit(1);	
			$poid = $poid->get();
		}
		else
		{
			if (!empty($param['order_id']))
			{
				$poid = $poid->where('poid', $param['order_id']);
			}
			
			if (!empty($param['email']))
			{
				$poid = $poid->where('email', $param['email']);
			}
			
			if (!empty($param['ebook_id']))
			{
				$poid = $poid->where('e.ebook_id', $param['ebook_id']);
			}
			
			if (!empty($param['status']))
			{
				$poid = $poid->where('status', $param['status']);
			}
	
			if (!empty($param['from_date']))
			{
				$poid = $poid->where('last_updated', '>=', $param['from_date'].' 00:00:00');
			}

			if (!empty($param['to_date']))
			{
				$poid = $poid->where('last_updated', '<=', $param['to_date'].' 23:59:59');
			}
			
			$poid = $poid->orderBy('poid', 'DESC');
			
			if (!empty($param['limit']))
			{
				$poid = $poid->groupBy('po.poid');
				$poid = $poid->paginate($param['limit']);	
			}
			else
			{
				$poid = $poid->groupBy('po.poid');
				$poid = $poid->get();	
			}
		}
			
            return $poid;
    }
	
	/*
	* Date: 07-05-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will return Full Text Inquiry Order
	*/
    public static function GetPreOrder($param,$join = false) 
	{
		$preorder = DB::table('tbl_ebook_preorders as p');
		
		if ($join == true)
		{
			$preorder = $preorder->select('ev.nid as ebook_volume_nid','ev.volume_name',
			'e.subtitle as subtitle','e.title as ebook_title','ev.ebook_type','ev.ebook_status',
			'p.title as pre_order_title','p.first_name','p.last_name','p.address','p.city','p.province','p.country','p.oid','p.email','p.organization_name','p.user_category','p.order_type','p.no_of_copies','p.status','p.last_updated','p.comment','p.phone','p.fax');
			$preorder = $preorder->join('ebook_volume as ev', 'ev.nid', '=', 'p.volume_nid');
			$preorder = $preorder->join('ebook as e', 'e.ebook_id', '=', 'ev.ebook_id');
		}	
		else
		{
			$preorder = $preorder->select('*');
		}
		
		if (!empty($param['oid']))
		{
			$preorder = $preorder->where('oid', $param['oid']);
			$preorder = $preorder->orderBy('oid', 'DESC');
			$preorder = $preorder->limit(1);	
			$preorder = $preorder->get();
		}
		else
		{
			if (!empty($param['order_type']))
			{
				$preorder = $preorder->where('order_type', $param['order_type']);
			}
			
			if (!empty($param['email']))
			{
				$preorder = $preorder->where('email', $param['email']);
			}
			
			if (!empty($param['order_id'])) // Consider Order Id as oid For Search 
			{
				$preorder = $preorder->where('oid', $param['order_id']);
			}
			
			if (!empty($param['ebook_id']))
			{
				$preorder = $preorder->where('e.ebook_id', $param['ebook_id']);
			}
			
			if (!empty($param['ebook_type']))
			{
				if ($param['ebook_type'] == 'S')
				{
					$preorder = $preorder->where('ev.ebook_type', 2);	
					$preorder = $preorder->where('ev.ebook_status', 2);	
				}	
				if ($param['ebook_type'] == 'GE')
				{
					$preorder = $preorder->where('ev.ebook_type', 1);	
					$preorder = $preorder->where('ev.ebook_status', 2);	
				}
				if ($param['ebook_type'] == 'FS')
				{
					$preorder = $preorder->where('ev.ebook_type', 2);	
					$preorder = $preorder->where('ev.ebook_status', 3);	
				}
				if ($param['ebook_type'] == 'FGE')
				{
					$preorder = $preorder->where('ev.ebook_type', 1);	
					$preorder = $preorder->where('ev.ebook_status', 3);	
				}
							
			}
	
			if (!empty($param['from_date']))
			{
				$preorder = $preorder->where('last_updated', '>=', $param['from_date'].' 00:00:00');
			}

			if (!empty($param['to_date']))
			{
				$preorder = $preorder->where('last_updated', '<=', $param['to_date'].' 23:59:59');
			}
			
			$preorder = $preorder->orderBy('oid', 'DESC');
			
			if (!empty($param['limit']))
			{
				$preorder = $preorder->groupBy('p.oid');
				$preorder = $preorder->paginate($param['limit']);	
			}
			else
			{
				$preorder = $preorder->groupBy('p.oid');
				$preorder = $preorder->get();	
			}
		}
			
            return $preorder;
    }
	
	/*
	* Date: 08-27-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will return Reprint Order
	*/
    public static function GetReprintOrder($param,$join = false) 
	{
		$reprint = DB::table('reprint_order as r');
		
		if ($join == true)
		{
			$reprint = $reprint->select('r.*','a.page_count','i.title as issue_title','v.volume_name','j.title as journal_title','a.first_page','a.last_page');
			$reprint = $reprint->join('article as a', 'a.article_id', '=', 'r.article_id');
			$reprint = $reprint->join('issue as i', 'i.issue_id', '=', 'a.issue_id');
			$reprint = $reprint->join('volume as v', 'v.volume_id', '=', 'a.volume_id');
			$reprint = $reprint->join('journal as j', 'j.journal_id', '=', 'a.journal_id');
		}	
		else
		{
			$reprint = $reprint->select('*');
		}
		
		if (!empty($param['reprint_order_id']))
		{
			$reprint = $reprint->where('reprint_order_id', $param['reprint_order_id']);
			$reprint = $reprint->orderBy('reprint_order_id', 'DESC');
			$reprint = $reprint->limit(1);	
			$reprint = $reprint->get();
		}
		else
		{
			if (!empty($param['order_type']))
			{
				$reprint = $reprint->where('order_type', $param['order_type']);
			}
			
			if (!empty($param['email']))
			{
				$reprint = $reprint->where('email', $param['email']);
			}
			
			if (!empty($param['order_id'])) // Consider Order Id as reprint_order_id For Search 
			{
				$reprint = $reprint->where('reprint_order_id', $param['order_id']);
			}
			
			if (!empty($param['article_id']))
			{
				$reprint = $reprint->where('a.article_id', $param['article_id']);
			}
			
			if (!empty($param['from_date']))
			{
				$reprint = $reprint->where('last_updated', '>=', $param['from_date'].' 00:00:00');
			}

			if (!empty($param['to_date']))
			{
				$reprint = $reprint->where('last_updated', '<=', $param['to_date'].' 23:59:59');
			}
			
			$reprint = $reprint->orderBy('reprint_order_id', 'DESC');
			
			if (!empty($param['limit']))
			{
				$reprint = $reprint->groupBy('r.reprint_order_id');
				$reprint = $reprint->paginate($param['limit']);	
			}
			else
			{
				$reprint = $reprint->groupBy('r.reprint_order_id');
				$reprint = $reprint->get();	
			}
		}
			
            return $reprint;
    }
	
	/*
	* Date: 08-27-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will return Eprint Order
	*/
    public static function GetEprintOrder($param,$join = false) 
	{
		$reprint = DB::table('eprint_order as e');
		
		if ($join == true)
		{
			$reprint = $reprint->select('e.*','a.page_count','i.title as issue_title','v.volume_name','j.title as journal_title','a.first_page','a.last_page');
			$reprint = $reprint->join('article as a', 'a.article_id', '=', 'e.article_id');
			$reprint = $reprint->join('issue as i', 'i.issue_id', '=', 'a.issue_id');
			$reprint = $reprint->join('volume as v', 'v.volume_id', '=', 'a.volume_id');
			$reprint = $reprint->join('journal as j', 'j.journal_id', '=', 'a.journal_id');
		}	
		else
		{
			$reprint = $reprint->select('*');
		}
		
		if (!empty($param['eprint_order_id']))
		{
			$reprint = $reprint->where('eprint_order_id', $param['eprint_order_id']);
			$reprint = $reprint->orderBy('eprint_order_id', 'DESC');
			$reprint = $reprint->limit(1);	
			$reprint = $reprint->get();
		}
		else
		{
			if (!empty($param['order_type']))
			{
				$reprint = $reprint->where('order_type', $param['order_type']);
			}
			
			if (!empty($param['email']))
			{
				$reprint = $reprint->where('email', $param['email']);
			}
			
			if (!empty($param['order_id'])) // Consider Order Id as reprint_order_id For Search 
			{
				$reprint = $reprint->where('eprint_order_id', $param['order_id']);
			}
			
			if (!empty($param['article_id']))
			{
				$reprint = $reprint->where('a.article_id', $param['article_id']);
			}
			
			if (!empty($param['from_date']))
			{
				$reprint = $reprint->where('last_updated', '>=', $param['from_date'].' 00:00:00');
			}

			if (!empty($param['to_date']))
			{
				$reprint = $reprint->where('last_updated', '<=', $param['to_date'].' 23:59:59');
			}
			
			$reprint = $reprint->orderBy('eprint_order_id', 'DESC');
			
			if (!empty($param['limit']))
			{
				$reprint = $reprint->groupBy('e.eprint_order_id');
				$reprint = $reprint->paginate($param['limit']);	
			}
			else
			{
				$reprint = $reprint->groupBy('e.eprint_order_id');
				$reprint = $reprint->get();	
			}
		}
			
            return $reprint;
    }
	
	/*
	* Date: 07-03-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will return Full Text Inquiry Order
	*/
    public static function GetFullTextInquiryOrder($param,$join = false) 
	{
		$inquiry = DB::table('tbl_epub_inquiry_order');
		
		if ($join == true)
		{
			$inquiry = $inquiry->select('article.article_id as article_id','article.nid as article_nid','inquiry_id','response_message','order_id','name','email','phone','address','status','journal.title as journal_title','journal.subtitle as subtitle','volume_name','article_title','amount','created_on','last_updated');
			$inquiry = $inquiry->join('article', 'article.article_id', '=', 'tbl_epub_inquiry_order.article_id');
			$inquiry = $inquiry->join('volume', 'volume.volume_id', '=', 'article.volume_id');
			$inquiry = $inquiry->join('journal', 'journal.journal_id', '=', 'article.journal_id');
		}	
		else
		{
			$inquiry = $inquiry->select('*');
		}
        
            $inquiry = $inquiry->where('tbl_epub_inquiry_order.status','!=', 'D');
		
        if (!empty($param['inquiry_id']))
		{
			$inquiry = $inquiry->where('inquiry_id', $param['inquiry_id']);
			$inquiry = $inquiry->orderBy('inquiry_id', 'DESC');
			$inquiry = $inquiry->limit(1);	
			$inquiry = $inquiry->get();
		}
		else
		{
			if (!empty($param['order_id']))
			{
				$inquiry = $inquiry->where('inquiry_id', $param['order_id']);
			}
			
			if (!empty($param['email']))
			{
				$inquiry = $inquiry->where('email', $param['email']);
			}
			
			if (!empty($param['status']))
			{
				$inquiry = $inquiry->where('status', $param['status']);
			}
	
			if (!empty($param['from_date']))
			{
				$inquiry = $inquiry->where('created_on', '>=', $param['from_date'].' 00:00:00');
			}

			if (!empty($param['to_date']))
			{
				$inquiry = $inquiry->where('created_on', '<=', $param['to_date'].' 23:59:59');
			}
			
			$inquiry = $inquiry->orderBy('inquiry_id', 'DESC');
			
			if (!empty($param['limit']))
			{
				$inquiry = $inquiry->groupBy('inquiry_id');
				$inquiry = $inquiry->paginate($param['limit']);	
			}
			else
			{
				$inquiry = $inquiry->groupBy('inquiry_id');
				$inquiry = $inquiry->get();	
			}
		}
			
            return $inquiry;
    }
	
	/*
	* Date: 07-03-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will return Full Text Inquiry Order
	*/
    public static function GetMultiUserOrder($param,$join = false) 
	{
		$order = DB::table('tbl_ebook_order as o');
		
		if ($join == true)
		{
			$order = $order->select('ev.ebook_volume_id as ebook_volume_id','ev.nid as ebook_volume_nid',
			'oid','o.response_message','o.print_promocode','o.address','o.last_updated','o.actual_amount','o.estimated_price','o.discount_rate','order_id',
			'e.subtitle as ebook_subtitle','book_title','name','email','phone','country','city','zip','org_name','ip_address','remote_ip','comments','status',
			'ev.volume_name as ebook_volume_name');
			
			$order = $order->join('ebook_volume as ev', 'ev.nid', '=', 'o.nid');
			$order = $order->join('ebook as e', 'e.ebook_id', '=', 'ev.ebook_id');
		}	
		else
		{
			$order = $order->select('*');
		}
		
			if (!empty($param['order_id']))
			{
				$order = $order->where('oid', $param['order_id']);
			}
			if (!empty($param['email']))
			{
				$order = $order->where('email', $param['email']);
			}
			
			if (!empty($param['ebook_id']))
			{
				$order = $order->where('e.ebook_id', $param['ebook_id']);
			}
			
			if (!empty($param['status']))
			{
				$order = $order->where('status', $param['status']);
			}
	
			if (!empty($param['from_date']))
			{
				$order = $order->where('last_updated', '>=', $param['from_date'].' 00:00:00');
			}

			if (!empty($param['to_date']))
			{
				$order = $order->where('last_updated', '<=', $param['to_date'].' 23:59:59');
			}
			
			$order = $order->orderBy('oid', 'DESC');
			
			if (!empty($param['limit']))
			{
                $order = $order->groupBy('oid');
				$order = $order->paginate($param['limit']);	
			}
			else
			{
				$order = $order->groupBy('oid');
				$order = $order->get();	
			}
			
            return $order;
    }

/*
* Date: 18-01-2018
* Author: Imran
* Parameter: $param
* Description:  this function will return the downloaded article count 
*/
    public static function GetArticleReferences($article_id)
    {   
        $references = DB::table('article_citation')->where('article_id', $article_id)->get();
        return $references;
    }

/*
 * Date: 25-04-2019
 * Author: Imran Khan
 * Parameter: id
 * Description:  this function will delete the selected article_citation
 */
    public static function DeleteReferences($id) {
        DB::table('article_citation')->where('article_id', $id)->delete();

    }
/*
 * Date: 25-04-2019
 * Author: Imran Khan
 * Parameter: data
 * Description:  this function will add data in article_citation
 */
    public static function AddReferences($data) {
        //dd($data);
        $id = DB::table('article_citation')->insert($data);
        return $id;
    }

    /*
     * Date: 29-04-2019
     * Author: Imran Khan
     * Parameter: contentid, column name
     * Description:  this function will return the list of  abstract details
     */

    public static function GetArticleAbstractDetail($id, $column)
    {
        $article_detail = DB::select("SELECT ad.* FROM article_abstract_detail ad where ad.$column = $id order by ad.article_id, ad.sequence");

        if ($article_detail) {
             return $article_detail;
        } else {
            return null;
        }
    }

    /*
     * Date: 29-04-2019
     * Author: Imran Khan
     * Parameter: data
     * Description:  this function will store the abstract details
     */


    public static function InsertArticleAbstractDetail($data)
    {

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

    /*
     * Date: 29-04-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will update the selected journal
     */
    public static function UpdateAbstractDetail($data)
    {
        DB::table('article_abstract_detail')->where('abstract_id', $data['abstract_id'])->update($data);
    }

    /*
     * Date: 30-04-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will delete the selected journal
     */
    public static function RemoveAbstractDetail($id)
    {

        DB::table('article_abstract_detail')->where('abstract_id', '=', $id)->delete();
    }

    /*
     * Date: 02-05-2019
     * Author: Imran Khan
     * Parameter: contentid, column name
     * Description:  this function will return the list of  funder details
     */

    public static function GetArticleFunderDetail($id, $column)
    {
        
        $funder_detail = DB::table('article_funders as f')
            ->select("f.*"
                ,DB::raw("(GROUP_CONCAT(fa.award_number SEPARATOR ',')) as `award_number`"))
            ->leftjoin("article_funders_awards as fa","fa.funder_id","=","f.funder_id")
            ->where("f.".$column, '=', $id)
            ->groupBy('f.funder_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 ($funder_detail) {
             return $funder_detail;
        } else {
            return null;
        }
    }

    /*
     * Date: 02-05-2019
     * Author: Imran Khan
     * Parameter: data
     * Description:  this function will store the funder details
     */


    public static function InsertArticleFunderDetail($data)
    {

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

    /*
     * Date: 02-05-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will update the selected funder
     */
    public static function InsertFunderDetail($data)
    {
        DB::table('article_funders')->where('funder_id', '=', $data['funder_id'])->delete();
         $id = DB::table('article_funders')->insertGetId($data);
         return $id;
    }

    /*
     * Date: 03-05-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will update the selected Funder Awards
     */
    public static function InsertFunderAwards($data)
    {
        DB::table('article_funders_awards')->insert($data);
    }

    /*
     * Date: 30-04-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will delete the selected Funder Award
     */
    public static function RemoveFunderDetail($id)
    {
        DB::table('article_funders')->where('funder_id', '=', $id)->delete();
        DB::table('article_funders_awards')->where('funder_id', '=', $id)->delete();
    }

    /*
     * Date: 30-04-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will delete the Funder Awards
     */
    public static function RemoveFunderAwards($id)
    {

        DB::table('article_funders_awards')->where('funder_id', '=', $id)->delete();
    }
//-----------------Author Affiliation----------------------
    /*
     * Date: 15-05-2019
     * Author: Imran Khan
     * Parameter: data
     * Description:  this function will store the Autor Affiliation
     */

     public static function GetAuthorAffiliation($id, $column)
    {
        $affiliation = DB::select("SELECT af.* FROM author_affiliation af where af.$column = $id order by af.aff_id" );

        if ($affiliation) {
             return $affiliation;
        } else {
            return null;
        }
    }

    /*
     * Date: 15-05-2019
     * Author: Imran Khan
     * Parameter: data
     * Description:  this function will store the author affiliation
     */


    public static function InsertAuthorAffiliation($data)
    {

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

    /*
     * Date: 15-05-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will update the author affiliation
     */
    public static function UpdateAuthorAffiliation($data)
    {
        DB::table('author_affiliation')->where('aff_id', $data['aff_id'])->update($data);
    }

    /*
     * Date: 15-05-2019
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will delete the author affiliation
     */
    public static function RemoveAuthorAffiliation($id,$column)
    {

        DB::table('author_affiliation')->where($column, '=', $id)->delete();
    }  


    public static function getArticleCitationData($article_id){
    
        return DB::table('article_citation')->where('article_id', '=', $article_id)->get();

    }  
	
	public static function AddReprint($data,$type)
	{
		$table = array('eprint' => 'eprint_order', 'reprint' => 'reprint_order');
		
        $id = DB::table($table[$type])->insertGetId($data);
        return $id;
    }

    /*
     * Author: Imran
     * Parameter: article_id, Issue_id, Volume ID
     * Description: This function return previous article
     */
    public static function GetPreviousArticle($first_page,$volume_id,$issue_id)
    {
        $previous = DB::table('article')->where('first_page', '<', $first_page)->whereRaw('is_uploaded = 1 and issue_id = '.$issue_id.'')->orderByRaw('cast(first_page as SIGNED) desc')->first();
        
        return $previous;
    }

    /*
     * Author: Imran
     * Parameter: article_id, Issue_id, Volume ID
     * Description: This function return next article
     */
    public static function GetNextArticle($last_page,$volume_id,$issue_id)
    {
        $next = DB::table('article')->where('last_page', '>', $last_page)->whereRaw('is_uploaded = 1 and issue_id = '.$issue_id.'')->orderByRaw('cast(first_page as SIGNED) asc')->first();

        return $next;
    }

     /* Tempary Block if required any updation on existing function getAllArticlesByIssueID
     * Author: Imran Khan 
     * Parameter: journal_id, track_id
     * Description: this function will return all articles Record
    */
    
/*    public static function GetJournalSectionArticles($journal_id,$section_id,$pagesize)
    {
        $result =  DB::table('article as a')
        ->selectRaw('a.publish_on,a.article_id,a.elocator, a.nid,a.pmid, a.title,a.altmetric_score,a.altmetric_url,a.kudos_url, j.content_type_access_id, first_page, last_page, 
            page_count,abstract,ifnull(is_abstract,"y"), a.graph_abs_lg_filename,a.is_epub, a.is_epub as epub, a.graph_abs_sm_filename,a.journal_id,a.volume_id,a.issue_id,
            a.doi,m.month,y.year, at.type_name article_type_name,a.html_filename,a.prc_filename,a.epub_filename,v.volume_name,i.title as issue, js.js_title,
            (select GROUP_CONCAT(CONCAT(COALESCE(first_name, ""),"", last_name)) from author aa where aa.article_id = a.article_id) as authors')
            
        ->leftJoin('article_type as at' , 'at.art_type_id', '=', 'a.art_type') 
        ->leftJoin('journal_section as js' , 'js.js_id', '=', 'a.js_id') 
        ->leftjoin('journal_access as j', function($query) {
                    $query->on('a.article_id', 'j.content_type_id')
                          ->on('j.content_type', '=', DB::raw("'a'"));
                })
        ->leftJoin('issue as i' , 'a.issue_id', '=', 'i.issue_id') 
        ->leftJoin('month as m' , 'i.month_id', '=', 'm.id') 
        ->join('volume as v' , 'a.volume_id', '=', 'v.volume_id')  
        ->join('year as y', 'v.year_id', '=', 'y.id')
        ->whereRaw('a.article_status != "W" and js.js_id = '.$section_id.' and a.journal_id = '.$journal_id.' and a.is_uploaded = 1 ')
        ->groupBy('a.article_id')
        ->orderByRaw('cast(a.first_page as SIGNED) asc');
        if($pagesize>0){
            $result = $result->paginate($pagesize);
        }else{
            $result = $result->get();
        }
        return $result;  

    }*/

    /*
     * Date: 05-06-2019
     * Author: Imran Khan
     * Parameter: param
     * Description:  this function will return the institute user content
     */
    public static function GetInstituteContent($param = null)
    {  
        $article = DB::table("article as a")
        ->selectRaw("a.*,a.article_id as article_id,v.volume_name as volume,i.title as issue, m.month, y.year, j.subtitle, j.title journal_title, at.type_name article_type_name")
        ->join("journal as j", "a.journal_id", "=", "j.journal_id")
        ->join("volume as v", "a.volume_id", "=", "v.volume_id")
        ->join("year as y", "v.year_id", "=", "y.id")
        ->join("author as au","a.article_id","=","au.article_id")
        ->join("author_affiliation as af","au.author_id", "=","af.author_id")
        ->leftJoin("issue as i", "a.issue_id", "=", "i.issue_id")
        ->leftJoin("month as m", "i.month_id", "=", "m.id")
        ->leftJoin("article_type as at" , "at.art_type_id", "=", "a.art_type")
        ->whereRaw("af.web_view LIKE '%".$param['name']."%' 
                    AND (af.web_view LIKE '%".$param['city']."%' 
                    OR af.web_view LIKE '%".$param['country']."')
                    AND `a`.`is_uploaded` = 1")
        ->groupBy("a.article_id")
        ->orderBy("a.publish_on", "DESC")
        ->get();
        return $article;
    }

    /*
     * Date: 05-06-2022
     * Author: Imran Khan
     * Parameter: journal_id
     * Description: This function will Return consortia free open access articles
     */
    public static function GetConsortiaFreeOpenAccessArticles($journal_id, $access_type, $article_id)
    {
        $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,i.title as issue,m.month,y.year,e.from_date,e.to_date,e.perpetual, j.subtitle, j.title journal_title, at.type_name article_type_name')
        ->join('journal_access as e', 'a.article_id', '=', 'e.content_type_id')
        ->join('journal as j', 'a.journal_id', '=', 'j.journal_id')
        ->join('volume as v', 'a.volume_id', '=', 'v.volume_id')
        ->join('year as y', 'v.year_id', '=', 'y.id')
        ->leftJoin('issue as i', 'a.issue_id', '=', 'i.issue_id')
        ->leftJoin('month as m', 'i.month_id', '=', 'm.id')
        ->leftJoin('article_type as at' , 'at.art_type_id', '=', 'a.art_type')
        ->whereRaw("e.content_type = 'a' and a.journal_id = $journal_id and content_type_access_id = $access_type and $conditions and e.content_type_id = $article_id")
        ->orderByRaw('cast(v.volume_name+0 as signed) desc')
        ->orderByRaw('cast(i.title as signed) desc')->first();
        return $result;

    }
	
	/*
     * Date: 25-10-2022
	 * Author: Imran Khan
     * Parameter: article_id
     * Description:  this function will delete the selected article
	 */
    public static function DeleteAbstractByArticleID($article_id)
    {

        DB::table('article_abstract_detail')->where('article_id', '=', $article_id)->delete();
    }

    /*
     * Date: 25-10-2022
     * Author: Imran Khan
     * Parameter: article_id
     * Description:  this function will delete the selected Funder Award
     */
    public static function RemoveFunderByArticleID($article_id)
    {
        DB::table('article_funders')->where('article_id', '=', $article_id)->delete();
        DB::table('article_funders_awards')->where('article_id', '=', $article_id)->delete();
		}

    /*
     * Date: 07-02-2023
     * Author: Zubair
     * Description:  this function will retuen all continues publication journal
    */
    public static function GettAllContinuePublicationJournal()
    {
        $article = DB::table('journal')
        ->select('subtitle')
        ->where('continues_publication_journal', 1)
        ->get();

       return $article;
    }
    
    /*
     * Date: 12-09-2023
     * Author: Zubair
     * Parameter: start, end
     * Description:  this function will retuen publish articles of one month
    */
    public static function GetPublishArticleList($param)
    {
        $article = DB::table("article as a")
        ->select("a.*","j.subtitle")
        ->join("journal as j", "j.journal_id", "=", "a.journal_id")
        ->whereRaw("a.publish_on BETWEEN '".$param['start']."' AND '".$param['end']."'")
        ->where("a.article_status", '=', "A")
		->orderBy("a.article_id", "ASC")
        ->get();

       return $article;
    }

    public static function GetArticlebyReferenceNumber($ref_no)
    {
        $article = DB::table("article as a")
        ->select("a.*",)
        ->where("a.jms_ref_no", '=', $ref_no)
        ->first();

       return $article;
    }

}
