<?php

namespace App\Models;

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

class Author extends Model
{
	
	  public $table = 'author';
    public $timestamps = false;

    public static function AddEbookVolumeAuthor($data)
    {

        $id = DB::table('author')->insertGetId($data);
        return $id;
    }
	
	
	public static function GetAuthors($where,$join=null,$select=null)
    {
         $author = DB::table('author as a')->select('a.*');
		 
		 if($join['author_affiliation'] == 'LEFT')
		 {
			$author = $author->leftjoin("author_affiliation as af","a.author_id","=","af.author_id");	 
			$author = $author->addSelect('af.aff_id','af.institution','af.department','af.address','af.country','af.city','af.postal_code','af.phone','af.fax');
			$author = $author->SelectRaw(' CASE WHEN af.aff_id IS NULL THEN "No" ELSE "Yes" END as affiliation ');
		 }
		 
		 if(!empty($where['article_id']))
		 {
			$author = $author->where('a.article_id',$where['article_id']);
		 }
		 	
		$author = $author->orderBy('a.article_id', 'asc');
		$author = $author->orderBy('a.sequence', 'asc');
		$author = $author->groupBy('a.author_id')->get();

        if ($author) {
             return $author;
        } else {
            return null;
        }
    }
	
	
    /*
     * Date: 12-09-2018
     * Author: Imran Khan
     * Parameter: data
     * Description:  this function will add authors
    */
    public static function AddAuthor($data)
    {        
        $id = DB::table('author')->insertGetId($data);
        return $id;
    }

    /*
     * Date: 11-09-2018
     * Author: Muhammad Arshad
     * Parameter: contentid, column name
     * Description:  this function will return the list of  authors
     */

    public static function getAuthorDataById($id, $column)
    {
         $author = DB::table('author as au')
            ->select(DB::raw("CONCAT_WS(' ',au.first_name, ' ', ifnull(au.initials,''),  ' ', au.last_name) as authors, CONCAT_WS(' ',au.last_name, ', ', ifnull(au.initials,''),  ' ', au.first_name) as authorsRIS, CONCAT_WS(' ',au.last_name, ' ', ifnull(au.initials,''),  ' ', au.first_name) as authorsCiteAs "),'au.*'
                ,DB::raw("(GROUP_CONCAT(TRIM(BOTH ', ' FROM 
                    CONCAT(ifnull(concat(af.institution,','),''), ifnull(concat(af.department,','),''), ifnull(concat(af.address,','),''), ifnull(concat(af.city,','),''), ifnull(concat(af.country,','),''), ifnull(concat(af.postal_code,','),''), ifnull(concat(af.phone,','),''), ifnull(concat(af.fax,','),''))) 
                    SEPARATOR '|')) as `author_affiliation`")
            ,DB::raw("(GROUP_CONCAT(TRIM(BOTH ', ' FROM 
                    CONCAT(ifnull(concat(af.web_view,','),''))) 
                    SEPARATOR '|')) as `web_view`"))
            ->leftjoin("author_affiliation as af","au.author_id","=","af.author_id")
            ->where("au.".$column, '=', $id)
            ->orderBy('au.article_id', 'asc')
            ->orderBy('au.sequence', 'asc')
            ->groupBy('au.author_id')->get();

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

    /*
     * Date: 23-09-2018
     * Author: Imran Khan
     * Parameter: id
     * Description:  this function will delete the authors
     */
    public static function DeleteAuthors($id,$field)
    {
        DB::table('author')->where($field, $id)->delete();
        return $id;
    }

    /*
     * Date: 02-11-2018
     * Author: Imran Khan
     * Parameter: issue_id
     * Description:  this function will return the complete list of issue authors
     */

    public static function GetIssueAuthors($issue_id)
    {
            $author = DB::select( "SELECT concat(au.first_name, ' ', ifnull(au.initials,''),  ' ', au.last_name) as authors, au.* FROM author au INNER join article a on a.article_id = au.article_id INNER JOIN issue i on i.issue_id = a.issue_id where i.issue_id = $issue_id order by a.article_id, au.sequence" );

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

    }

     /*
     * Date: 02-11-2018
     * Author: Arshad
     * Parameter: issue_id
     * Description:  this function will return the complete list of issue authors
     */

    public static function GetAuthorsByContentId($content_id,$content_field)
    {
            

        $author = DB::select("select CONCAT(ifnull(au.first_name,''),' ',IFNULL(CONCAT(au.initials,' '),''), au.last_name) as authors, au.* FROM author au where  ".$content_field." = '".$content_id."' order by au.sequence");

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

    }

     /*
     * Date: 15-04-2021
     * Author: Imran
     * Parameter: Institude_id
     * Description:  this function will return the complete institute detail with institude id
     */
    public static function GetInstitudeById($institude_id) 
    {
         $institude = DB::table('tbl_institutes')
        ->where('institution_id',$institude_id)
        ->first();
        return $institude;
    }
    /*
     * Date: 19-10-2022
     * Author: Imran
     * Parameter: article_id, journal_id, author_id
     * Description: This function return article by parameter
     */
    public static function GetArticleByAuthor($journal_id,$article_id,$author_id)
    {
        $article = DB::table('article as a')
        ->selectRaw('a.article_id, a.title article_title, j.journal_id, j.title journal_title, au.author_id, CONCAT(
        IFNULL(CONCAT(au.first_name,""),""),
        IFNULL(CONCAT(au.initials,"")," "),
        IFNULL(CONCAT(au.last_name,""),"")
      ) AS author, au.email')
        ->join('journal as j', 'a.journal_id', '=', 'j.journal_id')
        ->join('author as au', 'a.article_id', '=', 'au.article_id')
        ->where('a.article_id', '=', $article_id)->where('j.journal_id', '=', $journal_id)->where('au.author_id','=', $author_id)->first();
        return $article;
    }

    /*
     * Date: 19-10-2022
     * Author: Imran Khan
     * Parameter: data
     * Description:  this function will add data in Author Feed Back Form
     */
    public static function AddAuthorFeedBack($data) 
    {        //dd($data);
        $id = DB::table('feedback')->insertGetId($data);
        return $id;
    }

    /*
     * Date: 19-10-2022
     * Author: Imran
     * Parameter: $param
     * Description: This function return all rows of author feedback
     */

    public static function GetAuthorFeedBack($param = null)
    {  
        $article = DB::table('feedback as f')
        ->selectRaw('f.sub_datetime, f.sub_ip, a.article_id, a.title article_title, j.journal_id, j.title journal_title, au.author_id, CONCAT(
            IFNULL(CONCAT(au.first_name,""),""),
            IFNULL(CONCAT(au.initials,"")," "),
            IFNULL(CONCAT(au.last_name,""),"")
          ) AS author, au.email, f.experience, f.improvement, f.services')
        ->join('article as a', 'f.article_id', '=', 'a.article_id')
        ->join('journal as j', 'f.journal_id', '=', 'j.journal_id')
        ->join('author as au', 'f.author_id', '=', 'au.author_id');

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

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

        if (!empty($param['author_id'])) 
        {
            $article = $article->where('f.author_id', '=', $param['author_id']);
    }
        if (!empty($param['journal_id'])) 
        {
            $article = $article->where('f.journal_id', '=', $param['journal_id']);
        }
        if (!empty($param['paginate'])) 
        {
            $article = $article->orderBy('f.id', 'asc');
            $perPage    =   $article->count();
            $article = $article->paginate($perPage);
        }
        else
        {
            $article = $article->orderBy('f.id', 'DESC');
            $article = $article->paginate(10);
        }

        return $article;
}

    public static function GetBookAuthorbyEbookVolumeId($ebook_volume_id)
    {
        $authors = DB::table('author as a')
        ->selectraw("
        GROUP_CONCAT(CASE WHEN a.iseditorcoeditor='a' THEN CONCAT(COALESCE(a.first_name, ''), ' ', COALESCE(a.initials, ''), ' ', COALESCE(a.last_name, '')) END) AS 'author',
        GROUP_CONCAT(CASE WHEN a.iseditorcoeditor='ce' THEN CONCAT(COALESCE(a.first_name, ''), ' ', COALESCE(a.initials, ''), ' ', COALESCE(a.last_name, '')) END) AS 'co_editor',
        GROUP_CONCAT(CASE WHEN a.iseditorcoeditor='e' THEN CONCAT(COALESCE(a.first_name, ''), ' ', COALESCE(a.initials, ''), ' ', COALESCE(a.last_name, '')) END) AS 'editor'
        ")
        ->where("a.ebook_volume_id", $ebook_volume_id)
        ->first();
        return $authors;

    }

}
