<?php

namespace App\Models;

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

class Chapter extends Model
{

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

    public static function GetChapterList($param = null, $join = null)
    {
        $chapter = DB::table('chapter')->select('chapter.*','ebook_volume.select_ebook_type','ebook_volume.volume_name');
		//$chapter = $chapter->leftjoin('ebook_volume', 'ebook_volume.ebook_volume_id', '=', 'chapter.ebook_volume_id');
		
		if (!empty($join['ebook_volume'])) 
		{
			$chapter = $chapter->join('ebook_volume', 'ebook_volume.ebook_volume_id', '=', 'chapter.ebook_volume_id');
			$chapter = $chapter->addSelect('ebook_volume.ebook_volume_id','ebook_volume.volume_name');
		}
		
		if (!empty($join['ebook'])) 
		{
			$chapter = $chapter->join('ebook', 'ebook.ebook_id', '=', 'chapter.ebook_id');
			$chapter = $chapter->addSelect('ebook.ebook_id','ebook.subtitle');
		}

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

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

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

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

        $chapter = $chapter->orderBy('chapter.last_modified', 'desc');
        
        if (isset($param['pagination']) && !empty($param['pagination'])) {
            $chapter = $chapter->get();
        }
        else
        {
            $chapter = $chapter->paginate(10);
        }    
        return $chapter;
    }

    public static function GetChapterListByEbookVolume($param = null, $paginate=10)
    {
        $chapter = DB::table('chapter')
            ->select('chapter.*',
                DB::raw("group_concat(a.first_name,' ',a.last_name) as author_name"));
        $chapter = $chapter->leftjoin('author as a', 'a.chapter_id', '=', 'chapter.chapter_id');

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

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

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

        $chapter = $chapter->where('chapter.is_uploaded','=',1);
        $chapter = $chapter->groupBy('chapter.chapter_id');

        if (!empty($param['view']) && $param['view']=='user') 
        {
            $chapter = $chapter->orderBy('chapter.chapter_no', 'ASC');
        }
        else
        {
            $chapter = $chapter->orderBy('chapter.created', 'DESC');
        }
        
        $chapter = $chapter->paginate($paginate);

        return $chapter;
    }

    public static function GetChapterDetail($chapter_id)
    {
        $chapter = DB::table('chapter')->select('chapter.*','ebook_volume.select_ebook_type','ebook_volume.volume_name','ebook.title as ebook_title');        
        $chapter = $chapter->join('ebook_volume', 'ebook_volume.ebook_volume_id', '=', 'chapter.ebook_volume_id');
        $chapter = $chapter->join('ebook', 'ebook.ebook_id', '=', 'chapter.ebook_id');
        $chapter = $chapter->where('chapter.chapter_id', $chapter_id);
        $chapter = $chapter->first();
        
        return $chapter;
    }

    public static function GetChapterById($id)
    {
        $chapter = DB::table('chapter')->where('chapter_id', $id)->first();
        return $chapter;
    }

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

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

    public static function GetChapterByTitle($title)
    {
        $chapter = DB::table('chapter')->where('title', 'like', '%'.$title.'%')->first();
        return $chapter;
    }

    public static function AddChapter($data)
    {
        $id = DB::table('chapter')->insertGetId($data);
        return $id;
    }

    /*
     * Date: 19-09-2018
     * Author: Imran Khan
     * Parameter: data
     * Description:  this function will update the selected chapter
     */
    public static function UpdateChapter($data)
    {
        DB::table('chapter')->where('chapter_id', $data['chapter_id'])->update($data);
    }

    /*
     * Date: 13-09-2018
     * Author: Imran Khan
     * Parameter: ebookVolId
     * Description:  this function will return the chapter file save path
     */
    public static function GetChapterFilePath($ebookVolId)
    {
        $chapter = DB::table('ebook as e')
            ->join('publisher as p', 'e.publisher_id', '=', 'p.publisher_id')
            ->join('ebook_volume as ev', 'e.ebook_id', '=', 'ev.ebook_id')
            ->where('ev.ebook_volume_id', $ebookVolId)
            ->select('p.title as title', 'ev.eisbn as eisbn', 'ev.volume_name as volume_name')->first();
        if ($chapter) {
            $chapter_path = "E-books/" . $chapter->title . "/" . $chapter->eisbn . "/" . $chapter->volume_name . "/";
            return $chapter_path;
        } else {
            return '';
        }
    }

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

    /*
     * Date: 03-12-2018
     * Author: Arshad
     * Parameter: ebook_volume_id, chapter_id is optional
     * Description:  this function will return the list of all chapters in a volume
     */
    public static function getAllChaptersUnderVolumes($volume_id, $chapter_id = "0")
    {
        $sql = "select v.ebook_volume_id as volume_id, v.volume_name volume, c.title ,  y.year year,c.abstract, c.nid, c.content_access_type_id,
                c.chapter_id as chapter_id, v.ebook_id,c.doi,ebook_price,chapter_price, c.supplementary_filename,
                c.first_page, c.last_page, c.page_count, c.epub_filename, c.prc_filename,
                c.is_packaged
                from ebook_volume v
                join chapter c on c.ebook_volume_id = v.ebook_volume_id
                join year y
                on y.id = v.year_id
                where v.ebook_volume_id = '$volume_id' ";

        if ($chapter_id != "0") {
            $sql .= " and c.chapter_id = '$chapter_id' ";
        }

        $sql .= " order by v.volume_name desc, c.chapter_no asc";
        //print($sql); exit;
        $result = DB::select($sql);
        return $result;
    }

    /*
     * Date: 26-12-2018
     * Author: Arshad
     * Description:
     * This function will return Chapters that are set to Free/openaccess and those ebooks chapter whose compete volume is set
    to Free/openaccess. Further parameters like ebook eisbn, can be passed
    to narrow down result. Parameter 1 is type which can be set either 1 or 2. 1 will display ebook name as well as chapter name.
    2 will display only ebook name not chapter information.
     */

    public static function GetFreeEbookChapters($type = "1", $ebook_eisbn = "", $pagesize = 20)
    {

        $condition = '';
        if ($ebook_eisbn != "" and $ebook_eisbn != "0") {

            $ebook_eisbn = str_replace("-", "", $ebook_eisbn);
            $condition   = " and replace(v.eisbn,'-','') = $ebook_eisbn";

        }

        $chapter = DB::table('ebook_access as e')
            ->selectRaw('b.nid, b.title as ebook_title,b.image_file_name, v.isbn,v.eisbn,v.volume_name as volume, v.doi as ebook_doi, c.ebook_volume_id, c.title,c.first_page,c.last_page,c.page_count,c.abstract,c.doi as chapter_doi,c.nid as chapter_nid,c.chapter_id as chapter_id,"c" as access_on')
            ->Join('chapter as c', 'c.chapter_id', '=', 'e.content_type_id')
            ->Join('ebook_volume as v', 'v.ebook_volume_id', '=', 'c.ebook_volume_id')
            ->Join('ebook as b', 'b.ebook_id', '=', 'v.ebook_id')
            ->whereRaw("e.content_type = 'c' and ( (from_date <= CURDATE() and to_date >= CURDATE() and perpetual= 0) or (from_date is null and to_date is null and perpetual= 1) ) " . $condition);

        $volume = DB::table('ebook_access as e')
            ->selectRaw('b.nid, b.title as ebook_title,b.image_file_name, v.isbn, v.eisbn,v.volume_name as volume, v.doi as ebook_doi, c.ebook_volume_id, c.title,c.first_page,c.last_page, c.page_count,c.abstract,c.doi as chapter_doi,c.nid as chapter_nid,c.chapter_id as chapter_id,"v" as access_on')
            ->Join('ebook_volume as v', 'v.ebook_volume_id', '=', 'e.content_type_id')
            ->Join('chapter as c', 'c.ebook_volume_id', '=', 'v.ebook_volume_id')
            ->Join('ebook as b', 'b.ebook_id', '=', 'v.ebook_id')
            ->whereRaw("e.content_type = 'v' and ( (from_date <= CURDATE() and to_date >= CURDATE() and perpetual= 0) or (from_date is null and to_date is null and perpetual= 1) ) " . $condition);

        $result = $chapter->union($volume);

        if ($type == "2") {
            $result = $result->groupBy('eisbn');
        }
        $result = $result->orderBy('ebook_title');
        $result = $result->orderBy('title');

        $result = $result->get();

        return $result;

    }
    /*
     * Date: 19-01-2018
     * Author: Imran
     * Parameter: $param
     * Description:  this function will return the downloaded chapter count 
     */
    public static function GetDownloadedChapter($param = null)
    {
        $chapter = DB::table('chapter as c')->selectRaw('e.title as ebook,v.volume_name as volume, c.title as title, u.name as deo, dcc.ip_address, count(c.chapter_id) as total_download')
        ->join('ebook as e', 'c.ebook_id', '=', 'e.ebook_id')
        ->join('ebook_volume as v', 'c.ebook_volume_id', '=', 'v.ebook_volume_id')
        ->join('download_count_chapter as dcc', 'c.chapter_id', '=', 'dcc.chapter_id')
        ->leftJoin('users as u', 'dcc.user_id', '=', 'u.id')
        ->whereNotNull('dcc.id');

        if (!empty($param['ebook_id'])) {
            $ebook_id = $param['ebook_id'];
            $chapter  = $chapter->where('c.ebook_id', '=', $ebook_id);
        }

        if (!empty($param['ebook_volume_id'])) {
            $ebook_volume_id = $param['ebook_volume_id'];
            $chapter   = $chapter->where('c.ebook_volume_id', '=', $ebook_volume_id);
        }

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

        if (!empty($param['user_name'])) {
            $chapter   = $chapter->where('u.name', 'like', '%'.$param['user_name'].'%');
        }
        $chapter = $chapter->groupBy('c.title');
        
        if (!empty($param['user_name'])) {
        $chapter = $chapter->groupBy('u.name');
        }
        $chapter = $chapter->orderByRaw('e.title asc,v.volume_name asc,c.title asc,count(c.chapter_id) desc');
        $chapter = $chapter->paginate(10);

        return $chapter;
    }

 /*
 * Date: 11-12-2018
 * Author: Imran Khan
 * Parameter: user_id, chapter_id, SecondsGap
 * Description:  this function will return the download article file gap
 */
    public static function ChapterDownloadGap($user_id,$chapter_id,$SecondsGap) {

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

        return $result;
    }

 /*
 * Date: 11-02-2018
 * Author: Imran Khan
 * Parameter: data
 * Description:  this function will add all files download activities
 */
    public static function AddChapterDownload($data) 
    {
       $id = DB::table('download_count_chapter')->insertGetId($data);

       return $id;
    }

/*
 * Date: 09-06-2022
 * Author: Imran Khan
 * Parameter: id
 * Description:  this function will delete the selected chapter_citation
 */
    public static function DeleteReferences($id) {
        DB::table('chapter_citation')->where('chapter_id', $id)->delete();

    }

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

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

    /*
     * Date: 13-09-2023
     * Author: Zubair
     * Parameter: start, end
     * Description:  this function will retuen publish chapter of one month
     */
    public static function GetPublishChapterList($param)
    {
        $chapter = DB::table("chapter as c")
            ->select("c.*", "e.title as ebook_title")
            ->join("ebook_volume as ev", "ev.ebook_volume_id", "=", "c.ebook_volume_id")
            ->join("ebook as e", "e.ebook_id", "=", "c.ebook_id")
            ->whereRaw("ev.publication_date BETWEEN '" . $param['start'] . "' AND '" . $param['end'] . "'")
            ->orderBy("c.ebook_id", "ASC")
            ->get();

        return $chapter;
    }

    /*
     * Date: 02-10-2023
     * Author: Zubair
     * Parameter: ebook_volume_id
     * Description:  this function will count all pages of chapter of ebook volume by ebook_volume_id
     */

    public static function GetTotalPagesbyEbookVolumeId($ebook_volume_id)
    {
        $pages = DB::table('chapter')->where('ebook_volume_id', $ebook_volume_id)->sum('page_count');
        return $pages;
    }
}
