<?php

namespace App\Models;

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

class BundleOffer extends Model
{
    public $table = 'bundle_offer';

    /*
     * Date: 17-10-2019
     * Author: Imran Khan
     * Parameter: param
     * Description:  this function will return the name of article type
     */

    public static function GetBundleList($param = null)
    {
        $bundle = DB::table('bundle_offer as bo');
		
		$bundle = $bundle->addSelect('bo.*');

        if (!empty($param['bundle_id'])) {
            $bundle    = $bundle->where('bo.bundle_id', '=', $param['bundle_id']);
        }

        if (!empty($param['article_download_allow'])) {
            $bundle    = $bundle->where('bo.article_download_allow', '=', $param['article_download_allow']);
        }

        if (!empty($param['ebook_download_allow'])) {
            $bundle    = $bundle->where('bo.ebook_download_allow', '=', $param['ebook_download_allow']);
        }

        if (!empty($param['chapter_download_allow'])) {
            $bundle    = $bundle->where('bo.chapter_download_allow', '=', $param['chapter_download_allow']);
        }

        if (!empty($param['prince'])) {
            $bundle    = $bundle->where('bo.prince', '=', $param['prince']);
        }

        if (!empty($param['expiry'])) {
            $bundle    = $bundle->where('bo.expiry', '=', $param['expiry']);
        }
        
        $bundle = $bundle->where('bo.bundle_status', '=', 'A');
        $bundle = $bundle->orderBy('bo.bundle_id', 'DESC');
        
        if (!empty($param['paginate'])) {
            $bundle = $bundle->paginate(10);
        }
        else
        {
            $bundle = $bundle->get();
        }

        return $bundle;
    }

    /*
    * Date: 17-10-2019
    * Author: Imran Khan
    * Parameter: data
    * Description:  this function will add all Bundle Request Form and return last inserted Id
    */
    public static function AddBundleOffer($data) 
    {
        return DB::table('bundle_offer')->insertGetId($data);
    }

    /*
    * Date: 18-10-2019
    * Author: Imran Khan
    * Parameter: data
    * Description:  this function will update all Bundle Request Form and return last inserted Id
    */
    public static function UpdateBundleOffer($id,$data) 
    {
        return DB::table('bundle_offer')
        ->where('bundle_id', $id)
        ->update($data);
    }

    /*
    * Date: 19-10-2019
    * Author: Imran Khan
    * Parameter: data
    * Description:  this function will add all Bundle Offer Order Form and return last inserted Id
    */
    public static function AddBundleOfferOrder($data) 
    {
        return DB::table('bundle_offer_order')->insertGetId($data);
    }

    /*
     * Date: 19-10-2019
     * Author: Imran
     * Parameter: $param
     * Description:  this function will return the bundle offer order detail 
     */
    public static function GetBundleOfferOrderDetail($param = null)
    {  
        $bundle_order = DB::table('bundle_offer as bo')->selectRaw('(CASE 
                        WHEN boo.order_status = "C" THEN "Completed"
                        WHEN boo.order_status = "I" THEN "Inprocess"
                        WHEN boo.order_status = "N" THEN "Not Completed"
                        WHEN boo.order_status = "F" THEN "Fail"
                       END) as payment_status, bo.bundle_name as bundle_name, bo.bundle_description as bundle_description, boo.bundle_offer_order_id as bundle_offer_order_id, boo.article_download_allow as article_download_allow, boo.ebook_download_allow as ebook_download_allow, boo.chapter_download_allow as chapter_download_allow, boo.price as price, boo.expiry as expiry, boo.user_email as user_email, boo.user_name as user_name, boo.user_country as user_country, boo.created_on as created_on, boo.order_status as order_status, boo.bundle_offer_expiry as bundle_offer_expiry,u.name as name')
        ->join('bundle_offer_order as boo', 'bo.bundle_id', '=', 'boo.bundle_id')
        ->leftjoin('users as u', 'boo.user_id', '=', 'u.id');

        if (!empty($param['bundle_id'])) {
            $bundle_order    = $bundle_order->where('bo.bundle_id', '=', $param['bundle_id']);
        }

        if (!empty($param['bundle_offer_order_id'])) {
            $bundle_order    = $bundle_order->where('boo.bundle_offer_order_id', '=', $param['bundle_offer_order_id']);
        }

        if (!empty($param['article_download_allow'])) {
            $bundle_order    = $bundle_order->where('boo.article_download_allow', '=', $param['article_download_allow']);
        }

        if (!empty($param['ebook_download_allow'])) {
            $bundle_order    = $bundle_order->where('boo.ebook_download_allow', '=', $param['ebook_download_allow']);
        }

        if (!empty($param['chapter_download_allow'])) {
            $bundle_order    = $bundle_order->where('boo.chapter_download_allow', '=', $param['chapter_download_allow']);
        }

        if (!empty($param['prince'])) {
            $bundle_order    = $bundle_order->where('boo.prince', '=', $param['prince']);
        }

        if (!empty($param['expiry'])) {
            $bundle_order    = $bundle_order->where('boo.expiry', '=', $param['expiry']);
        }
        
        if (!empty($param['order_status'])) {
            $bundle_order    = $bundle_order->where('boo.order_status', '=', $param['order_status']);
        }
        
        if (!empty($param['bundle_order_status'])) {
            $bundle_order    = $bundle_order->where('boo.bundle_order_status', '=', $param['bundle_order_status']);
        }

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

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

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

        if (!empty($param['bundle_offer_expiry'])) {
            $bundle_order    = $bundle_order->where('boo.bundle_offer_expiry', '>', $param['bundle_offer_expiry']);
        }

        $bundle_order = $bundle_order->orderBy('boo.bundle_offer_order_id', 'DESC');
        
        if (!empty($param['paginate'])) {
            $bundle_order = $bundle_order->paginate(10);
        }
        else
        {
            $bundle_order = $bundle_order->get();
        }


       return $bundle_order;
    }    

    /*
    * Date: Date: 19-10-2019
    * Author: Imran
    * Parameter: data
    * Description:  this function will Update all Bundle Offer Orders
    */
    
    public static function UpdateBundleOfferOrder($bundle_offer_order_id,$data)
    {
        return DB::table('bundle_offer_order')
        ->where('bundle_offer_order_id', $bundle_offer_order_id)
        ->update($data);
    }

    /*
     * Date: 05-09-2019
     * Author: Imran
     * Parameter: $param
     * Description:  this function will return the article rental access 
     */
    public static function GetBundleOfferContentCount($param = null)
    {  
        $bundle = DB::table('bundle_offer_order_detail as bod')->selectRaw('COUNT(CASE WHEN bod.content_type = "a" THEN 0 END) AS article,
            COUNT(CASE WHEN bod.content_type = "c" THEN 0 END) AS chapter,
            COUNT(CASE WHEN bod.content_type = "e" THEN 0 END) AS ebook, boo.bundle_offer_order_id as bundle_offer_order_id
            ')
        ->join('bundle_offer_order as boo', 'bod.bundle_offer_order_id', '=', 'boo.bundle_offer_order_id');

        if (!empty($param['bundle_offer_order_id'])) {
            $bundle    = $bundle->where('bod.bundle_offer_order_id', '=', $param['bundle_offer_order_id']);
        }

        if (!empty($param['paginate'])) {
            $bundle = $bundle->paginate(10);
        }
        else
        {
            $bundle = $bundle->get();
        }

       return $bundle;
    }

    /*
    * Date: 23-10-2019
    * Author: Imran Khan
    * Parameter: data
    * Description:  this function will add all Bundle Offer Order Content
    */
    public static function AddBundleOfferOrderContent($data) 
    {
        return DB::table('bundle_offer_order_detail')->insert($data);
    }

    /*
    * Date: 24-10-2019
    * Author: Imran Khan
    * Parameter: data
    * Description:  this function will return the all Bundle Offer Order Content
    */
    public static function GetBundleOfferOrderContent($param="") 
    {
        $article = DB::table("article as a")
        ->selectRaw("a.article_id as content_id, CONCAT('<b>Article:</b>&nbsp; ', a.title, '<br /> <b>Journal:</b>&nbsp ', j.title, ', &nbsp<b>Volume:</b> &nbsp ', v.volume_name,', &nbsp <b>Issue:</b>&nbsp ', i.title) AS content_title, 'Article' as content_type, bood.bundle_offer_order_id as bundle_offer_order_id, downloaded_on as downloaded_on, bo.bundle_name as bundle_name")
        ->join('bundle_offer_order_detail as bood', 'a.article_id', '=', 'bood.content_id')
        ->join('issue as i', 'a.issue_id', '=', 'i.issue_id')
        ->join('volume as v', 'i.volume_id', '=', 'v.volume_id')
        ->join('journal as j', 'v.journal_id', '=', 'j.journal_id')
        ->join('bundle_offer_order as bod', 'bood.bundle_offer_order_id', '=', 'bod.bundle_offer_order_id')
        ->join('bundle_offer as bo', 'bod.bundle_id', '=', 'bo.bundle_id');

            $article    = $article->where('bood.content_type', '=', 'a');

        if (!empty($param['bundle_offer_order_id'])) {
            $article    = $article->where('bood.bundle_offer_order_id', '=', $param['bundle_offer_order_id']);
        }
       
        $ebook_volume = DB::table("ebook_volume as ev")
        ->selectRaw("ev.ebook_volume_id as content_id, CONCAT('<b>eBook:</b>&nbsp; ', e.title, '<br /> <b>Volume:</b> &nbsp; ', ev.volume_name) AS content_title, 'eBook Volume' as content_type, bood.bundle_offer_order_id as bundle_offer_order_id, downloaded_on as downloaded_on, bo.bundle_name as bundle_name")
        ->join('ebook as e', 'ev.ebook_id', '=', 'e.ebook_id')
        ->join('bundle_offer_order_detail as bood', 'ev.ebook_volume_id', '=', 'bood.content_id')
        ->join('bundle_offer_order as bod', 'bood.bundle_offer_order_id', '=', 'bod.bundle_offer_order_id')
        ->join('bundle_offer as bo', 'bod.bundle_id', '=', 'bo.bundle_id');
            $ebook_volume    = $ebook_volume->where('bood.content_type', '=', 'e');
        if (!empty($param['bundle_offer_order_id'])) {
            $ebook_volume    = $ebook_volume->where('bood.bundle_offer_order_id', '=', $param['bundle_offer_order_id']);
        }

        $chapter = DB::table("chapter as c")
        ->selectRaw("c.chapter_id as content_id, CONCAT('<b>Chapter</b>:&nbsp; ', c.title, '<br /> <b>eBook:</b>&nbsp ', e.title, ', &nbsp<b>Volume:</b> &nbsp ', ev.volume_name) AS content_title, 'Chapter' as content_type, bood.bundle_offer_order_id as bundle_offer_order_id, downloaded_on as downloaded_on, bo.bundle_name as bundle_name")
        ->join('bundle_offer_order_detail as bood', 'c.chapter_id', '=', 'bood.content_id')
        ->join('ebook_volume as ev', 'c.ebook_volume_id', '=', 'ev.ebook_volume_id')
        ->join('ebook as e', 'ev.ebook_id', '=', 'e.ebook_id')
        ->join('bundle_offer_order as bod', 'bood.bundle_offer_order_id', '=', 'bod.bundle_offer_order_id')
        ->join('bundle_offer as bo', 'bod.bundle_id', '=', 'bo.bundle_id');
            $chapter    = $chapter->where('bood.content_type', '=', 'c');
        if (!empty($param['bundle_offer_order_id'])) {
            $chapter    = $chapter->where('bood.bundle_offer_order_id', '=', $param['bundle_offer_order_id']);
        }

        $query = $article->unionAll($ebook_volume)->unionAll($chapter);
        $query = $query->get();

        return $query;
    }
}
