<?php

namespace App\Models;

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

class Indexing extends Model
{
    /*
    * Author: Zubair
    * Parameter: search
    * Description: This function return rows of indexing table
    */

    public static function getAllIndexing($search = "", $pagesize=10)
    {

        $indexing_list = DB::table('indexing_agencies as i');
        $indexing_list = $indexing_list->leftjoin('users as u', 'u.id', '=', 'i.created_by');
        $indexing_list = $indexing_list->select('*');
        $indexing_list = $indexing_list->where('i.status', '=' ,1);

        if ($search != "") {
            $indexing_list = $indexing_list->where('i.title', 'like', '%' . $search . '%');
        }
        $indexing_list = $indexing_list->orderBy('i.title', 'ASC');
        $indexing_list = $indexing_list->paginate($pagesize);

        return $indexing_list;
    }

    /*
    * Date: 10-11-2022
    * Author: Muhammad Zubair
    * Parameter: data
    * Description:  this function will add data in indexing
    */

    public static function AddIndexingAgencies($data)
    {
        $id = DB::table('indexing_agencies')->insert($data);
        return $id;
    }

    /*
    * Date: 10-11-2022
    * Author: Muhammad Zubair
    * Parameter: data,index_id
    * Description:  this function will update data in indexing
    */

    public static function UpdateIndexingbyID($data, $index_id)
    {
         $indexing_list = DB::table('indexing_agencies')->where('index_id', $index_id)->update($data);
    }

    /*
    * Date: 10-11-2022
    * Author: Muhammad Zubair
    * Parameter: index_id
    * Description:  this function will get row from indexing table where index id is matched
    */
    public static function GetIndexingbyID($index_id)
    {
        $indexing_list = DB::table('indexing_agencies')->where('index_id', $index_id)->first();
        return $indexing_list;
    }

    /*
    * Date: 10-11-2022
    * Author: Muhammad Zubair
    * Parameter: parent_id
    * Description:  this function will get row from indexing table where parent id is matched and status is 1
    */
    public static function CheckParentChildbyID($parent_id)
    {
        $indexing_list = DB::table('indexing_agencies')
        ->where('parent_id', $parent_id)
        ->where('status', 1)->first();
        return $indexing_list;
    }

    public static function display_parent()
    {
       $temp=''; 
       $level=0;
        $sql = "SELECT i.index_id, i.title, SubQry.Count FROM `indexing_agencies` i LEFT OUTER JOIN (SELECT parent_id, COUNT(*) AS COUNT FROM `indexing_agencies` GROUP BY parent_id) SubQry ON i.index_id = SubQry.parent_id WHERE i.parent_id= 0";
        //print($sql);
        $resultMainMenu = DB::select($sql);
        if($resultMainMenu)
        {
            foreach ($resultMainMenu as $mainrow ) 
            {
                $temp .= $mainrow->title . '=' . $mainrow->index_id . '_';
                $temp .= Indexing::display_children($mainrow->index_id, $level + 1); //get all childs by parent
            }
        }
        return $temp;
    }

    public static function display_children($parent, $level)
    {
        //get all children
       $temp=''; 
       $sql = "SELECT i.index_id, i.title, SubQry.Count FROM `indexing_agencies` i LEFT OUTER JOIN (SELECT parent_id, COUNT(*) AS Count FROM `indexing_agencies` GROUP BY parent_id) SubQry ON i.index_id = SubQry.parent_id WHERE i.parent_id= " . $parent . "";
        $resultChild = DB::select($sql);
        if($resultChild)
        {
            foreach ($resultChild as $row) 
            {
                $temp .= str_repeat('--', $level) . $row->title . '=' . $row->index_id . '_';
                $temp .= Indexing::display_children($row->index_id , $level + 1); //if child has sub childs repeatly call
            }
        }
        return $temp;
    }    

    /*
     * Date: 10-11-2022
     * Author: Muhammad Zubair
     * Parameter: journal_id, field
     * Description:  this function will return the journal indexing
     */
    public static function IndexingJournal($journal_id, $field)
    {
        $indexing_list = DB::table('indexing_journal as ij')            
        ->leftjoin('indexing_agencies as i', 'ij.index_id', '=', 'i.index_id')
        ->where('ij.' . $field, $journal_id)
        ->select('ij.index_id')
        ->get();
        //dd($indexing_list);
        return $indexing_list;
    }

    /*
     * Date: 10-11-2022
     * Author: Muhammad Zubair
     * Parameter: data
     * Description:  this function will inert the journal indexing
     */
    public static function AddIndexingJournal($data)
    {
        $id = DB::table('indexing_journal')->insert($data);
        return $id;
    }

    /*
     * Date: 10-11-2022
     * Author: Muhammad Zubair
     * Parameter: id
     * Description:  this function will delete selected journal indexing
    */
    public static function DeleteIndexingJournal($id)
    {
        DB::table('indexing_journal')->where('journal_id', $id)->delete();

    }

    /*
     * Date: 10-11-2022
     * Author: Muhammad Zubair
     * Parameter: data
     * Description:  this function will inert the ebook indexing
    */
    public static function AddIndexingEbook($data)
    {
        $id = DB::table('indexing_ebook')->insert($data);
        return $id;
    }

    /*
     * Date: 10-11-2022
     * Author: Muhammad Zubair
     * Parameter: ebook_id, field
     * Description:  this function will return selected ebook indexing
    */
    public static function IndexingEbook($ebook_id, $field)
    {
         //->join('ebook as e', 'se.ebook_id', '=', 'e.ebook_id')
        $indexing_list = DB::table('indexing_ebook as ie')
            
            ->join('indexing_agencies as i', 'ie.index_id', '=', 'i.index_id')
            ->where('ie.' . $field, $ebook_id)
            ->select('ie.index_id')
            ->get();
        // dd($indexing_list); die;
        return $indexing_list;
    }

    /*
     * Date: 10-11-2022
     * Author: Muhammad Zubair
     * Parameter: id
     * Description:  this function will delete selected ebook indexing
    */
    public static function DeleteIndexingEbook($id)
    {
        DB::table('indexing_ebook')->where('ebook_id', $id)->delete();

    }


    public static function GetIndexingJournal()
    {
       $sql = "SELECT * FROM 
        (
            SELECT ii.* FROM indexing_agencies ii WHERE ii.`index_id` 
            IN (
                SELECT i.parent_id FROM indexing_agencies i
                INNER JOIN indexing_journal iji ON i.`index_id` = iji.`index_id`
                WHERE i.`parent_id` != 0
                GROUP BY iji.`index_id` 
               ) 
               
            UNION
            SELECT iii.* FROM indexing_agencies iii
            INNER JOIN indexing_journal ij ON iii.`index_id` = ij.`index_id`
            GROUP BY ij.`index_id`
        ) temp 
        WHERE temp.status =1
        GROUP BY temp.index_id  ORDER BY temp.parent_id";
        $result = DB::select($sql);
        if($result)
        {
            return $result;
        }
        else
        {
            return null;
        }
    }  

    public static function GetIndexingJournalDetails() {

        $sql = "select ij.index_id, j.title,j.journal_id
                FROM indexing_journal ij
                inner join journal j on j.journal_id=ij.journal_id
                ";
        // print($sql);
        $result = DB::select($sql);
        return $result;
    }

    public static function GetIndexingEbook()
    {
       $sql = "SELECT * FROM 
        (
            SELECT ii.* FROM indexing_agencies ii WHERE ii.`index_id` 
            IN (
                SELECT i.parent_id FROM indexing_agencies i
                INNER JOIN indexing_ebook iei ON i.`index_id` = iei.`index_id`
                WHERE i.`parent_id` != 0
                GROUP BY iei.`index_id` 
               ) 
            UNION
            SELECT iii.* FROM indexing_agencies iii
            INNER JOIN indexing_ebook ie ON iii.`index_id` = ie.`index_id`
            GROUP BY ie.`index_id`
        ) temp 
        WHERE temp.status =1
        GROUP BY temp.index_id  ORDER BY temp.parent_id";
        $result = DB::select($sql);
        if($result)
        {
            return $result;
        }
        else
        {
            return null;
        }
    } 
    
    public static function GetIndexingEbookDetails() {

        $sql = "select ie.index_id, e.title,e.ebook_id,ev.ebook_volume_id
                FROM indexing_ebook ie
                inner join ebook e on e.ebook_id=ie.ebook_id
                left join ebook_volume ev on ev.ebook_id=ie.ebook_id
                ";
        // print($sql);
        $result = DB::select($sql);
        return $result;
    }


}
