<?php

namespace App\Models;

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

class EbookVolume extends Model
{

    public $table = 'ebook_volume';

    public $timestamps = false;

    /*

     * Author: Arsal

     * Parameter: ebook_id, volume_id, author_id, year_id

     * Description: This function will return selected fields join with ebook, author, year

     */

    public static function GetEbookVolume($param = null)
    {

        $volume = DB::table('ebook_volume as ev')

            ->Join('ebook as e', 'e.ebook_id', '=', 'ev.ebook_id')

            ->leftJoin('author as a', function ($join) {

                $join->on('ev.ebook_volume_id', '=', 'a.ebook_volume_id');

                //$join->on('ev.ebook_id', '=', 'a.ebook_id');

            })

            ->join('year as y', 'y.id', '=', 'ev.year_id')

            ->select('ev.*', 'e.title', 'y.year as volume_year', 'a.first_name as first_name', 'a.last_name as last_name');

        if (!empty($param['ebook_id'])) {

            $ebook_id = $param['ebook_id'];

            $volume = $volume->where('e.ebook_id', '=', $ebook_id);

        }

        if (!empty($param['author_id'])) {

            $author_id = $param['author_id'];

            $volume = $volume->where('e.author_id', '=', $author_id);

        }

        if (!empty($param['volume_id'])) {

            $volume_id = $param['volume_id'];

            $volume = $volume->where('ev.ebook_volume_id', '=', $volume_id);

        }

        if (!empty($param['year_id'])) {

            $year_id = $param['year_id'];

            $volume = $volume->where('ev.year_id', '=', $year_id);

        }


        if (!empty($param['from']) && !empty($param['to'])) {

            $from = strtotime($param['from']);
            $to = strtotime($param['to']);

            $volume = $volume->whereBetween('ev.publication_date', [$from, $to]);

        }

        $volume = $volume->groupBy('ev.volume_name');

        $volume = $volume->groupBy('ev.ebook_id');

        //$volume = $volume->orderByRaw('cast(ev.volume_name  as signed) DESC');

        $volume = $volume->orderByRaw('ev.publication_date DESC');

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

        return $volume;

    }

    public static function GetEbookVolumeTitleById($param)
    {

        $volume = DB::table('ebook_volume');

        if (!empty($param['ebook_volume_id'])) {

            $volume = $volume->where('ebook_volume_id', '=', $param['ebook_volume_id']);

        }

        if (!empty($param['ebook_id'])) {

            $volume = $volume->where('ebook_id', '=', $param['ebook_id']);

        }

        if (!empty($param['ASC'])) {

            $volume = $volume->orderByRaw('cast(ebook_volume.volume_name  as signed) ASC');

        } else {

            $volume = $volume->orderByRaw('cast(ebook_volume.volume_name  as signed) DESC');

        }

        $volume = $volume->pluck('volume_name', 'ebook_volume_id');

        if ($volume->isEmpty()) {

            return null;

        } else {

            return $volume;

        }

    }

    public static function GetVolumesByEbookId($ebook_id)
    {

        $conditions = " and v.ebook_id =" . $ebook_id;

        $query = "select e.ebook_id ebook_id,v.ebook_volume_id as volume_id, v.volume_name name,e.title,v.doi

                from ebook_volume v

                inner join ebook e on e.ebook_id=v.ebook_id

                where v.ebook_id != 0 " . $conditions . "

                order by cast(v.volume_name as SIGNED) asc";

        $ebook_volume = DB::select($query);

        return $ebook_volume;

    }

    /*

     * Author: Arsal

     * Parameter: ebook_id

     * Description: This function is using for dropdown only, Get volume_name as value and ebook_volume_id as key for drop down list

     */

    public static function GetEbookVolumeByEbookId($id)
    {

        $ebook_volume = DB::table('ebook_volume')->where('ebook_id', '=', $id)

            ->select('*')->get();

        return $ebook_volume;

    }

    /*

     * Author: Arsal

     * Parameter: volume_name, ebook_id, ebook_volume_id

     * Description: This function will return ebook volume

     */

    public static function GetEbookVolumeById($param)
    {

        $volume = DB::table('ebook_volume');

        if (!empty($param['volume_name'])) {

            $volume_name = $param['volume_name'];

            $volume = $volume->where('volume_name', '=', $volume_name);

        }

        if (!empty($param['ebook_id'])) {

            $ebook_id = $param['ebook_id'];

            $volume = $volume->where('ebook_id', '=', $ebook_id);

        }

        if (!empty($param['ebook_volume_id'])) {

            $ebook_volume_id = $param['ebook_volume_id'];

            $volume = $volume->where('ebook_volume_id', '=', $ebook_volume_id);

        }

        $volume = $volume->get();

        if ($volume->isEmpty()) {

            return null;

        } else {

            return $volume;

        }

    }

    public static function GetEbookVolumeAndAuthorsById($id, $view = null)
    {

        $ebook = DB::table('ebook_volume as e')

            ->leftJoin('author as a', function ($join) {

                $join->on('e.ebook_volume_id', '=', 'a.ebook_volume_id');

                //$join->on('e.ebook_id', '=', 'a.ebook_id');

            })

            ->leftJoin('year as y', 'y.id', '=', 'e.year_id')

            ->where('e.ebook_volume_id', $id)

            ->select('e.*', 'a.*', 'y.year',

                'e.ebook_volume_id as ebook_volume_id',

                'a.ebook_volume_id as ebk_vol_id',

                'e.ebook_id as ebk_ebook_id'

            );

        if (!empty($view) && $view == 'front') {

            $ebook = $ebook->where('e.is_uploaded', '=', 1);

        }

        $ebook = $ebook->get();

        return $ebook;

    }

    public static function AddEbookVolume($data)
    {

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

        return $id;

    }

    public static function GetVolume($id = "", $field = "")
    {

        $chapter = DB::table('ebook_volume')->where($field, '=', $id)->orderBy('ebook_volume_id', 'ASC')->get();

        return $chapter;

    }

    /*

     * Author: Imran

     * Parameter: id, field

     * Description: This function will return the single record of selected ebook volume

     */

    public static function GetEbookVolumeByVolumeId($volume_id)
    {

        $ebook_volume = DB::table('ebook_volume as v')->select('v.*', 'e.title')->where('ebook_volume_id', '=', $volume_id)->join('ebook as e', 'e.ebook_id', '=', 'v.ebook_id')->first();

        return $ebook_volume;

    }

    public static function GetEbookVolumeByNID($nid)
    {

        $ebook_volume = DB::table('ebook_volume as v')->select('v.*', 'e.title')->where('v.nid', '=', $nid)

            ->join('ebook as e', 'e.ebook_id', '=', 'v.ebook_id')

            ->first();

        return $ebook_volume;

    }

    /*

     * Author: Arsal akhter

     * Parameter: ebook_volume_id

     * Description: This function will Remove Ebook Volume Record

     */

    public static function RemoveEbookVolumeById($id)
    {

        DB::table('ebook_volume')->where('ebook_volume_id', '=', $id)->delete();

    }

    public static function GetEbookMetaByNodeId($id)
    {

        $node = DB::table('node_meta')->where('nid', '=', $id)->get();

        if ($node->isEmpty()) {

            return null;

        } else {

            return $node;

        }

    }

    public static function AddEbookMeta($data)
    {

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

        return $id;

    }

    /*

     * Author: Arsal akhter

     * Parameter: ebook_volume_id, ebook_id, volume_name

     * Description: This function will Return First Row of Ebook Volume Record

     */

    public static function CheckEbookVolumeExist($ebook_volume_id, $ebook_id, $volume_name)
    {

        $ebook_volume = DB::table('ebook_volume')

            ->where('ebook_id', '=', $ebook_id)

            ->where('volume_name', '=', $volume_name)

            ->where('ebook_volume_id', '<>', $ebook_volume_id)

            ->get()->first();

        return $ebook_volume;

    }

    /*

     * Author: Arsal akhter

     * Parameter: title and Array (year,title,doi,volume,isbn,eisbn)

     * Description: This function will Return Ebook Volume list with Authors

     */

    public static function SearchEbookVolumeList($letter = null, $param = null, $subscribe = false)
    {

        $volume = DB::table('ebook_volume as ev');

        $flag = 0;

        $sample_types = array();

        if ($subscribe) {

            if (session()->has('ip_base_access') || session()->has('user_base_access')) {

                if (!empty($param['user_id'])) {

                    $where = '((from_ip <= ' . $param['ip_address'] . ' and ' . $param['ip_address'] . ' <= to_ip)

                                    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 ' . $param['ip_address'] . ' <= to_ip)';

                }

                $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_ebook_volume_trail')->select('volume_id', 'type as sample_type')->whereRaw($where);

                $t2 = DB::table('user_access_ebook_volume_corporate')->select('volume_id', 'type as sample_type')->whereRaw($where);

                $t3 = DB::table('user_access_ebook_volume_token')->select('volume_id', 'type as sample_type')->whereRaw($where);

                $t4 = DB::table('user_access_chapter_trail')->select('volume_id', 'type as sample_type')->whereRaw($where);

                $t5 = DB::table('user_access_chapter_corporate')->select('volume_id', 'type as sample_type')->whereRaw($where);

                $t6 = DB::table('user_access_chapter_token')->select('volume_id', 'type as sample_type')->whereRaw($where);

                $query = $t1->union($t2)->union($t3)->union($t4)->union($t5)->union($t6);

                $query = $query->get()->toArray();

                foreach ($query as $k => $v) {

                    $volume_ids[] = $v->volume_id;

                    $sample_types[$v->volume_id] = $v->sample_type;

                }

                if (!empty($volume_ids)) {

                    $volume = $volume->whereIn('ebook_volume_id', $volume_ids);

                    $flag = 1;

                }

            } else {

                return false;

            }

            if ($flag == 0) {

                $volume = DB::table('ebook_volume as ev')->where('ebook_volume_id', '0')->paginate(12);

                return $volume;

            }

        }

        $volume = $volume->select('ev.*', 'e.title', 'e.image_file_name as ebk_image_file_name', 'ea.content_type_id', 'ea.content_type_access_id',

            DB::raw("(select concat(first_name,' ',last_name) from author as a where a.ebook_volume_id = ev.ebook_volume_id limit 1) as authors")

        );

        $volume = $volume->Join('ebook as e', 'e.ebook_id', '=', 'ev.ebook_id');

        if (!empty($param['open_access'])) {

            $volume = $volume->Join('ebook_access as ea', function ($join) {

                $join->on('ea.content_type_id', '=', 'ev.ebook_volume_id')

                    ->whereRaw(' ((from_date <= CURDATE() and to_date >= CURDATE() and perpetual= 0) or (from_date is null and to_date is null and perpetual= 1)) and ea.content_type="v"');

            });

        } else {

            $volume = $volume->leftJoin('ebook_access as ea', function ($join) {

                $join->on('ea.content_type_id', '=', 'ev.ebook_volume_id')

                    ->whereRaw(' ((from_date <= CURDATE() and to_date >= CURDATE() and perpetual= 0) or (from_date is null and to_date is null and perpetual= 1)) and ea.content_type="v"');

            });
		}

        //add here	
        if(!empty($param['subject_id'])) 
        {
            $volume = $volume->Join('subject_ebook as se', 'se.ebook_id', '=', 'e.ebook_id');
            $volume = $volume->where('se.subject_id',$param['subject_id']);
        }

        if(!empty($param['pc_id'])) 
        {
            $volume = $volume->Join('pharmacy_collection_ebook as se', 'se.ebook_volume_id', '=', 'ev.ebook_volume_id');
            $volume = $volume->where('se.pc_id',$param['pc_id']);
        }


        $volume = $volume->join('year as y', 'y.id', '=', 'ev.year_id');

        if (!empty($param['generalebook']) && !empty($param['series']) && !empty($param['futuregeneralebook']) && !empty($param['futureseries'])) {

            $volume = $volume->where('ebook_status', 1);

        }

        if (!empty($letter)) {

            $volume = $volume->where('e.title', 'like', $letter . '%');

        }

        if (!empty($param['ebook_type'])) {

            $volume = $volume->where('ev.ebook_type', $param['ebook_type']);

        }

        if (!empty($param['select_ebook_type'])) {

            $volume = $volume->where('ev.select_ebook_type', $param['select_ebook_type']);

        }

        if (!empty($param['year'])) {

            $volume = $volume->where('y.year', $param['year']);

        }

        if (!empty($param['title'])) {

            $volume = $volume->where('e.title', 'like', '%' . $param['title'] . '%')
                       ->orwhere('ev.ebook_subtitle', 'like', '%' . $param['title'] . '%');  

        }

        if (!empty($param['doi'])) {

            $volume = $volume->where('ev.doi', '=', $param['doi']);

        }

        if (!empty($param['volume']) && !empty($param['isbn_eisbn'])) {
            $search  = str_replace("-", "", $param['isbn_eisbn']);
            $volume = $volume->where('ev.volume_name', '=', $param['volume'])

                ->whereRaw('replace(ev.isbn, "-", "") like (?)',["%{$search}%"])

                ->orwhereRaw('replace(ev.eisbn, "-", "") like (?)',["%{$search}%"]);

        }

        if (!isset($param['volume']) && !empty($param['isbn_eisbn'])) {
            $search  = str_replace("-", "", $param['isbn_eisbn']);
            $volume = $volume->whereRaw('replace(ev.isbn, "-", "") like (?)',["%{$search}%"])

                ->orwhereRaw('replace(ev.eisbn, "-", "") like (?)',["%{$search}%"]);

        }

        if (isset($param['All'])) {

            $volume = $volume->whereRaw('(ev.doi="' . $param['All'] . '" or e.title like "%' . $param['All'] . '%" or ev.ebook_subtitle like "%' . $param['All'] . '%" or ev.isbn like "%' . $param['All'] . '%" or ev.eisbn like "%' . $param['All'] . '%" or y.year like "%' . $param['All'] . '%")');

        }

        $volume = $volume->where('e.published', 1)->where('ev.is_uploaded', '=', 1);

        $volume = $volume->where('ebook_status', 1);

        $volume = $volume->orderByRaw('e.title, cast(ev.volume_name  as signed) ASC');

        $volume = $volume->paginate(12);

        return array($volume, $sample_types);

    }

    /*

     * Author: Arsal akhter

     * Parameter: NONE

     * Description: This function will Return ALL Ebook Volume List

     */

    public static function GetAllEbookVolumeList($pagesize = null)
    {

        $volume = DB::table('ebook_volume as v')

            ->Join('ebook as e', 'e.ebook_id', '=', 'v.ebook_id')

            ->select('v.ebook_volume_id', 'e.title', 'v.volume_name', 'e.ebook_id', 'e.image_file_name as ebk_image_file_name', 'e.index_on as index_on', 'v.ebook_subtitle', 'v.ebook_price', 'v.ebook_price_pod', 'v.ebook_price_podandpdf', 'v.isbn', 'v.issn', 'v.eisbn', 'v.eissn', 'v.doi', 'v.year_id', 'v.nid as vnid', 'v.introduction as introduction', 'v.expected_date as expected_date', DB::raw("concat(e.title,' VOL-',v.volume_name) as ebk_title"))

            ->where('e.published', 1);

        $volume = $volume->orderByRaw('e.title, cast(v.volume_name  as signed) ASC');

        if (!empty($pagesize)) {

            $volume = $volume->paginate($pagesize);

        } else {

            $volume = $volume->get();

        }

        if ($volume->isEmpty()) {

            return null;

        } else {

            return $volume;

        }

    }

    /*

     * Author: Arsal akhter

     * Parameter: title and Array (year,title,doi,volume,isbn,eisbn)

     * Description: This function will Return Ebook Volume list with Authors

     */

    public static function GetEbookVolumeForthcoming($param)
    {

        if (!empty($param['ebook_type'])) {

            $volume = DB::table('ebook_volume as ev')

                ->select('ev.*', 'e.title', 'e.image_file_name as ebk_image_file_name', 'ev.eisbn', 'ev.volume_name', 'ev.expected_date', 'ev.image_file_name',

                    DB::raw("(select group_concat(concat(' ',first_name,' ',last_name)) from author as a where a.ebook_volume_id = ev.ebook_volume_id limit 1) as authors")

                )

                ->Join('ebook as e', 'e.ebook_id', '=', 'ev.ebook_id')

                ->join('year as y', 'y.id', '=', 'ev.year_id');

            if (!empty($param['ebook_type'])) {

                $volume = $volume->where('ev.ebook_type', $param['ebook_type']);

            }

            if (!empty($param['ebook_status'])) {

                $volume = $volume->where('ev.ebook_status', $param['ebook_status']);

            }

            $volume = $volume->orderBy('e.title', 'ASC')->get();

            if ($volume->isEmpty()) {

                return null;

            } else {

                return $volume;

            }

        }

    }

    /*

     * Date: 26-12-2018

     * Author: Arshad

     * Parameter: pagesize(optional)

     * Description: This web service will show All free ebooks.

     */

    public static function GetFreeEbook($pagesize = 10)
    {

        $volume = DB::table('ebook_access as e')

            ->selectRaw('b.nid, content_type_id , b.title as ebook_title,b.image_file_name, v.isbn, v.eisbn,v.volume_name as volume,v.ebook_volume_id as ebook_volume_id')

            ->Join('ebook_volume as v', 'v.ebook_volume_id', '=', 'e.content_type_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) )")

            ->orderBy("b.title")

            ->orderBy("v.volume_name");

        $volume = $volume->paginate($pagesize);

        return $volume;

    }

    /*

     * Date: 22-01-2018

     * Author: Imran

     * Parameter: $param

     * Description:  this function will return the downloaded ebook volume count

     */

    public static function GetDownloadedVolume($param = null)
    {

        $volumes = DB::table('ebook_volume as v')->selectRaw('e.title as ebook,v.volume_name as volume, u.name as deo, dcv.ip_address, count(v.ebook_volume_id) as total_download')

            ->join('download_count_volume as dcv', 'v.ebook_volume_id', '=', 'dcv.volume_id')

            ->join('ebook as e', 'dcv.ebook_id', '=', 'e.ebook_id')

            ->leftJoin('users as u', 'dcv.user_id', '=', 'u.id')

            ->whereNotNull('dcv.id');

        if (!empty($param['ebook_id'])) {

            $ebook_id = $param['ebook_id'];

            $volumes = $volumes->where('v.ebook_id', '=', $ebook_id);

        }

        if (!empty($param['ebook_volume_id'])) {

            $ebook_volume_id = $param['ebook_volume_id'];

            $volumes = $volumes->where('v.ebook_volume_id', '=', $ebook_volume_id);

        }

        if (!empty($param['user_name'])) {

            $volumes = $volumes->where('u.name', 'like', '%' . $param['user_name'] . '%');

        }

        $volumes = $volumes->groupBy('e.title');

        if (!empty($param['user_name'])) {

            $volumes = $volumes->groupBy('u.name');

        }

        $volumes = $volumes->groupBy('e.title');

        $volumes = $volumes->orderByRaw('e.title asc,v.volume_name asc,dcv.ip_address asc,count(v.ebook_volume_id) desc');

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

        return $volumes;

    }

    /*

     * Author: Arsal akhter

     * Parameter: poid

     * Description: This function will Return Ebook Print Order

     */

    public static function GetEbookPrintOrder($poid)
    {

        $po_entry = DB::table('tbl_ebook_print_order')

            ->Join('node', 'node.nid', '=', 'tbl_ebook_print_order.nid')

            ->Join('ebook_volume', 'ebook_volume.nid', '=', 'node.nid')

            ->where('poid', $poid)->first();

        return $po_entry;

    }

    /*

     * Author: Arsal akhter

     * Parameter: oid

     * Description: This function will Return Ebook Order

     */

    public static function GetEbookOrder($oid)
    {

        $po_entry = DB::table('tbl_ebook_order')

            ->Join('node', 'node.nid', '=', 'tbl_ebook_order.nid')

            ->Join('ebook_volume', 'ebook_volume.nid', '=', 'node.nid')

            ->where('oid', $oid)->first();

        return $po_entry;

    }

    /*

     * Author: Arsal akhter

     * Parameter: oid

     * Description: This function will Update Ebook Order

     */

    public static function UpdateEbookOrder($oid, $data)
    {

        return DB::table('tbl_ebook_order')

            ->where('oid', $oid)

            ->update($data);

    }

    /*

     * Date: 16-05-2019

     * Author: Arshad

     * Parameter: user_id, ebook_volume_id, SecondsGap

     * Description:  this function will return the download ebook volume file gap

     */

    public static function VolumeDownloadGap($user_id, $volume_id, $SecondsGap)
    {

        $result = DB::table('download_count_volume')

            ->selectRaw('count(*) rcd,download_count_volume.*')

            ->whereRaw('(user_id = ' . $user_id . ' and volume_id = ' . $volume_id . ') and (unix_timestamp(CURRENT_TIMESTAMP())-timestamp) <= ' . $SecondsGap . '

                            order by timestamp desc')->first();

        return $result;

    }

    /*

     * Date: 16-05-2019

     * Author: Arshad

     * Parameter: data

     * Description:  this function will add all files download activities

     */

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

    /*

     * Date: 11-07-2019

     * Author: Arshad

     * Parameter: ebook_id, ebook_volume_id

     * Description:  this function will count the Volume of an ebook

     */

    public static function CountMultipleVolumeExist($ebook_id, $volume_id)
    {

        $count = DB::table('ebook_volume')

            ->selectRaw('count(*) rcd')

            ->where('ebook_id', $ebook_id)

            ->whereRaw('ebook_volume_id <> ' . $volume_id)

            ->first();

        return $count->rcd;

    }

}
