<?php

namespace App\Models;

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

class Issue extends Model
{
    protected $primaryKey = 'issue_id';
    public $table = 'issue';
    public $timestamps = false;

    /*
     * Date: 06-09-2018
     * Author: Muhammad Arshad
     * Parameter: journal_id , volume_id
     * Description:  this function will return the list of  issues
     */
    
    public static function GetIssue($param = null)
    {        
        $issue = DB::table('issue')

        ->join('journal', 'journal.journal_id', '=', 'issue.journal_id')
        ->join('volume', 'volume.volume_id', '=', 'issue.volume_id')
        ->join('year', 'volume.year_id', '=', 'year.id')
        ->join('month', 'month.id', '=', 'issue.month_id')
        
        ->select('*',
                //   'volume.volume_id as volume_id',
                //  'journal.journal_id as journal_id'
                    'journal.title as journal_title',
					'journal.subtitle as subtitle',
                    'volume.volume_name as volume_name',
                    'issue.title as issue_title',
					'issue.issue_title as issue_subtitle',                    
					'year.year as year',
                    'month.month as month'
                );

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

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

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

        if (!empty($param['search']))
        {
            
        }

        $issue = $issue->orderBy('issue.issue_id', 'DESC');
        $issue = $issue->paginate(10);


      
        return $issue;
    }

    /*
     * Date: 06-09-2018
     * Author: Muhammad Arshad
     * Parameter: param
     * Description:  this function will return the detail of  issues
     */
    
    public static function GetIssueById($param)
    {        
        $issue = DB::table('issue');
      
        if (!empty($param['volume_id']))
        {
            $volume_id = $param['volume_id'];
            $issue = $issue->where('volume_id', '=', $volume_id);
        }

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

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

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

        if (!empty($param['is_uploaded']))
        {
            $is_uploaded = $param['is_uploaded'];
            $issue = $issue->where('is_uploaded', '=', $is_uploaded);
        }
		//$issue = $issue->orderBy('issue.issue_id', 'DESC');
        $issue = $issue->orderByRaw('cast(issue.title  as signed) desc');
        
        $issue = $issue->get();
		
		 if ($issue->isEmpty()) 
		 {
            return null;
        } else {
            return $issue;
        }

    }

    /*
     * Date: 06-09-2018
     * Author: Muhammad Arshad
     * Parameter: param
     * Description:  this function will return list of Issue with selected options
     */
    
    public static function GetIssueTitleById($param)
    {        
        $issue = DB::table('issue');
        
        if (!empty($param['volume_id']))
        {
            $volume_id = $param['volume_id'];
            $issue = $issue->where('volume_id', '=', $volume_id);
        }

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

        if (!empty($param['issue_id']))
        {
            $issue_id = $param['issue_id'];
            $issue = $issue->where('issue_id', '=', $issue_id);
        }
        $issue = $issue->orderByRaw('cast(issue.title  as signed) desc');
       // $issue = $issue->pluck( 'title','issue_id');
        $issue = $issue->select('*')->get();

        

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

    /*
     * Date: 06-09-2018
     * Author: Muhammad Arshad
     * Parameter: issue id
     * Description:  this function will remove the issue
     */

    public static function RemoveIssueById($id)
    {        
      
        DB::table('issue')->where('issue_id', '=', $id)->delete();  
    }

    /*
     * Date: 06-09-2018
     * Author: Muhammad Arshad
     * Parameter: data
     * Description:  this function will save the issue meta data
     */
    
    public static function save_meta($data)
    {        
       
        $id = DB::table('node_meta')->insertGetId($data);
        return $id;
    }
    
    /*
     * Date: 06-09-2018
     * Author: Muhammad Arshad
     * Parameter: nid
     * Description:  this function will return the node meta detaill
     */
    
    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 CheckIssueExist($param)
    {

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

        $issue = DB::table('issue')
        ->where('title', '=', $title)
        ->where('volume_id', '=', $volume_id)
        ->where('journal_id', '=', $journal_id)
        ->where('issue_id', '<>', $issue_id)
        ->get()->first();
       
        return $issue;
       
    }

    /*
     * Date: 01-11-2018
     * Author: Imran Khan
     * Parameter: journal_id
     * Description:  this function will return the issue list journal wise
    */
    public static function GetAllIssueByJournalID($journal_id)
    {        
        $issue = DB::table('issue')
        ->join('journal', 'journal.journal_id', '=', 'issue.journal_id')
        ->join('volume', 'volume.volume_id', '=', 'issue.volume_id')
        ->join('year', 'volume.year_id', '=', 'year.id')
        ->join('month', 'month.id', '=', 'issue.month_id')
        ->select('*','journal.title as journal_title',
                    'issue.title as issue_title',
                    'issue.issue_title as issue_subtitle',
                    'issue.about_issue as about_issue','issue.type as issue_type', 'month.month as month', 'year.year as year'
                )
        ->where('issue.journal_id', '=', $journal_id)
        ->where('issue.is_uploaded', '=', 1)
        ->orderByRaw('cast(volume.volume_name as signed) desc,cast(issue.title  as signed) desc')
        
        ->get();

        return $issue;
    }
	

	/*
     * Author: Arsal akhter
     * Parameter: journal_id
     * Description: This function will Return Issue list
     */
    public static function getcurrentissue($journal_id)
    {

        $query = "Select j.subtitle, v.volume_id as volume_id,v.year_id,y.year as year,v.volume_name, v.total_no_of_issues, i.issue_id as issue_id,i.title as issue, i.publication_date as issue_pub_date from journal j
						join volume v on j.journal_id = v.journal_id
						join issue i on v.volume_id = i.volume_id
						join article a on a.issue_id = i.issue_id
						join year y on v.year_id = y.id
					     where j.journal_id = $journal_id					     order by v.volume_name+0 desc, i.title+0 desc limit 1";

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

        return $current_issue;

    }
    /*
     * Author: Imran Khan
     * Parameter: journal_id,is_thematic
     * Description: This function will Return thematic issue under the volume
     */
    public static function getAllIssuesUnderVolumes($volume_id,$is_thematic=0)
    {

		$issue = DB::table('volume as v')
        ->join('issue as i', 'i.volume_id', '=', 'v.volume_id')
        ->join('year as y', 'y.id', '=', 'v.year_id')
        ->join('month as m', 'm.id', '=', 'i.month_id')
        ->selectRaw('v.volume_id as volume_id, v.volume_name as volume_name, 
		i.title, i.about_issue, i.issue_title, i.type, 
		m.month month, y.year year, i.nid, i.issue_id, v.volume_id as volume_id')
		->where('v.volume_id', '=', $volume_id)
		->where('i.is_uploaded', '=', 1);
		if($is_thematic != 0)
		{
			$issue = $issue->where('i.type', '=', $is_thematic);
		}
        $issue = $issue->orderByRaw('cast(i.title  as signed) desc');	
		$issue = $issue->get();
	
        if ($issue->isEmpty()) 
		{
            return null;
        } else {
            return $issue;
        }
    }
	
	/*
	* Date: 21-02-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will add all Contact Details
	*/
    public static function AddContactDetails($data) 
	{
        return DB::table('tbl_issue_inquiry_order')->insertGetId($data);
    }
	
	
	
	/*
	* Date: 21-02-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will add all Contact Details
	*/
    public static function GetInquiryOrder($inquiry_id) 
	{
        $inquiry = DB::table('tbl_issue_inquiry_order')->select('*');
		
		$inquiry = $inquiry->where('inquiry_id', '=',$inquiry_id);
        $inquiry = $inquiry->orderBy('inquiry_id', 'DESC');
        $inquiry = $inquiry->limit(1);
		$inquiry = $inquiry->get();
      
		if ($inquiry->isEmpty()) 
		{
            return null;
        } else {
            return $inquiry;
        }
		
    }

    /*
     * Date: 08-01-2020
     * Author: Imran
     * Parameter: $param
     * Description:  this function will return the full issue purchase details 
     */
    public static function GetIssuePurchaseDetail($param = null)
    {  
        $issue_order = DB::table('tbl_issue_inquiry_order as iio')->selectRaw('
            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, u.name as name, u.display_name as display_name, u.email as email, u.country as country, iio.status as status, iio.inquiry_id as inquiry_id, iio.email as email, concat(iio.first_name," ",iio.last_name) as user_name, iio.amount as amount, iio.last_updated as created_on')
        ->join('issue as i', 'iio.nid', '=', 'i.nid')
        ->join('volume as v', 'i.volume_id', '=', 'v.volume_id')
        ->join('journal as j', 'i.journal_id', '=', 'j.journal_id')
        ->leftjoin('users as u', 'iio.userid', '=', 'u.id');

        if (!empty($param['inquiry_id'])) {
            $inquiry_id = $param['inquiry_id'];
            $issue_order    = $issue_order->where('iio.inquiry_id', '=', $inquiry_id);
        }

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

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

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

        if (!empty($param['user_email'])) {
            $issue_order   = $issue_order->where('iio.email', 'like', '%'.$param['user_email'].'%');
        }

        if (!empty($param['status'])) {
            if($param['status'] == 'F')
            {
                $issue_order= $issue_order->where('iio.status','=', 'F')->orwhere('iio.status','=', 'X');
            }
            else
            {
                $issue_order= $issue_order->where('iio.status','=',''.$param['status'].'');
            }
        }

        $issue_order = $issue_order->orderByRaw('iio.inquiry_id desc');
        
        if (!empty($param['paginate'])) {
            $issue_order = $issue_order->paginate(12);
        }
        else
        {
            $issue_order = $issue_order->get();
        }


       return $issue_order;
    }    
	
	/*
	* Date: 07-03-2019
	* Author: Arsal Akhter
	* Parameter: data
	* Description:  this function will Update all Issue Inquiry Order
	*/
	
    public static function UpdatePurchaseIssueInquiryOrder($inquiry_id,$data)
	{
        return DB::table('tbl_issue_inquiry_order')
		->where('inquiry_id', $inquiry_id)
		->update($data);
    }
}
