<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Auth;

class EBM extends Model
{
    /*
    * Date: 07-04-2023
    * Author: Zubair
    * Parameter: param
    * Description: This function will return all editors list
    */

    public static function GetAllEditors($param)
    {

        $ebm = DB::table('tbl_editors as e')
            ->select('e.*')
            ->leftjoin('tbl_ebm as eb', 'eb.editor_id', '=', 'e.editor_id');

        if ($param['journal_id'] != "") {
            $journal_id = $param['journal_id'];
            $ebm = $ebm->where('eb.journal_id', '=', $journal_id);
        }
        if ($param['role_id'] != "") {
            $role_id = $param['role_id'];
            $ebm = $ebm->where('eb.ebm_role_id', '=', $role_id);
        }
        if ($param['name'] != "") {
            $name = $param['name'];
            $ebm = $ebm->whereRaw("(e.fname LIKE '%" . str_replace("'", "''", $name) . "%' OR e.mname LIKE '%" . str_replace("'", "''", $name) . "%' OR e.lname LIKE '%" . str_replace("'", "''", $name) . "%')");
        }
        if ($param['email'] != "") {
            $email = $param['email'];
            $ebm = $ebm->where('e.email', 'like', '%' . $email . '%');
            $ebm = $ebm->orwhere('e.alt_email', 'like', '%' . $email . '%');
        }
        if ($param['country'] != "") {
            $country = $param['country'];
            $ebm = $ebm->where('e.country', 'like', '%' . $country . '%');
        }
        if ($param['keyword'] != "") {
            $keyword = $param['keyword'];
            $ebm = $ebm->where('e.institute', 'like', '%' . $keyword . '%');
            $ebm = $ebm->orwhere('e.university', 'like', '%' . $keyword . '%');
            $ebm = $ebm->orwhere('e.pharmaceutical', 'like', '%' . $keyword . '%');
            $ebm = $ebm->orwhere('e.department', 'like', '%' . $keyword . '%');
            $ebm = $ebm->orwhere('e.laboratory', 'like', '%' . $keyword . '%');
            $ebm = $ebm->orwhere('e.library', 'like', '%' . $keyword . '%');
            $ebm = $ebm->orwhere('e.phone', 'like', '%' . $keyword . '%');
            $ebm = $ebm->orwhere('e.zip_code', 'like', '%' . $keyword . '%');
            $ebm = $ebm->orwhere('e.city', 'like', '%' . $keyword . '%');
            $ebm = $ebm->orwhere('e.state', 'like', '%' . $keyword . '%');
        }

        $ebm = $ebm->where('e.status', '!=', 'D');

        $ebm = $ebm->groupBy('e.email');
        $ebm = $ebm->orderBy('e.lname', 'ASC');
        $ebm = $ebm->paginate(15);

        return $ebm;
    }

    /*
    * Date: 07-04-2023
    * Author: Zubair
    * Description: This function will return all ebm_role
    */

    public static function GetAllEBMRole()
    {
        $ebm_role = DB::table('tbl_ebm_role as er');
        $ebm_role = $ebm_role->select('*');
        $ebm_role = $ebm_role->where('er.priority', '!=', null);
        $ebm_role = $ebm_role->orderBy('er.priority', 'ASC');
        $ebm_role = $ebm_role->get();

        return $ebm_role;
    }

    /*
    * Date: 07-04-2023
    * Author: Zubair
    * Parameter: data
    * Description: This function will add editor
    */

    public static function AddEditor($data)
    {
        $ebm_add = DB::table('tbl_editors')->insertGetId($data);

        return $ebm_add;
    }

    /*
    * Date: 07-04-2023
    * Author: Zubair
    * Parameter: data
    * Description: This function will add editor into tbl_editors_history
    */

    public static function AddEditorHistory($id, $action)
    {
        $select = DB::table('tbl_editors as e')
            ->selectRaw('editor_id,fname,mname,lname,degree,titles,salutation,suffix,institute,university,pharmaceutical, company,department, laboratory, library,expertise,h_index,orcid_id,scopus_id,biography, address,zip_code,city, state,country,phone,fax,mobile,email,alt_email, url,cv_content_type,cv_name,profile_pic,profile_pic_type,web_format, added_by,created_on,updated_on,status,institution_id,now(),"' . $action . '"')
            ->where('e.editor_id', $id);

        $rows_inserted = DB::table('tbl_editors_history')
            ->insertUsing(['editor_id', 'fname', 'mname', 'lname', 'degree', 'titles', 'salutation', 'suffix', 'institute', 'university', 'pharmaceutical', 'company', 'department', 'laboratory', 'library', 'expertise', 'h_index', 'orcid_id', 'scopus_id', 'biography', 'address', 'zip_code', 'city', 'state', 'country', 'phone', 'fax', 'mobile', 'email', 'alt_email', 'url', 'cv_content_type', 'cv_name', 'profile_pic', 'profile_pic_type', 'web_format', 'added_by', 'created_on', 'updated_on', 'status', 'institution_id', 'action_on', 'action'], $select);
    }

    /*
    * Date: 07-04-2023
    * Author: Zubair
    * Parameter: id, editorChanges
    * Description: This function will return editor by id or specific column from table
    */

    public static function GetEditorbyID($id, $editorChanges = "")
    {
        $editor = DB::table('tbl_editors');

        if (!empty($editorChanges)) {
            $editor = $editor->selectRaw($editorChanges);
        } else {
            $editor = $editor->select('*');
        }

        $editor = $editor->where('editor_id', '=', $id)->first();

        return $editor;
    }

    /*
    * Date: 10-04-2023
    * Author: Zubair
    * Parameter: data, id
    * Description: This function will update editor by id
    */

    public static function UpdateEditor($data, $id)
    {
        $ebm_update = DB::table('tbl_editors')->where('editor_id', $id)->update($data);

        return $ebm_update;
    }

    /*
    * Date: 11-04-2023
    * Author: Zubair
    * Parameter: data
    * Description: This function will Add Editor Role
    */

    public static function AddEditorRole($data)
    {
        $ebm_role_add = DB::table('tbl_ebm')->insertGetId($data);

        return $ebm_role_add;
    }

    /*
    * Date: 11-04-2023
    * Author: Zubair
    * Parameter: editor_id, ebm_status
    * Description: This function will return EBM roles by editor id
    */

    public static function GetEbmRolebyEditorID($editor_id, $ebm_status="")
    {
        $ebm_role = DB::table('tbl_ebm as e')
            ->select('e.*', 'ed.fname', 'ed.mname', 'ed.lname', 'er.*', 'j.title', 'j.subtitle')
            ->join('tbl_editors as ed', 'ed.editor_id', '=', 'e.editor_id')
            ->join('tbl_ebm_role as er', 'er.role_id', '=', 'e.ebm_role_id')
            ->join('journal as j', 'j.journal_id', '=', 'e.journal_id')
            ->where('e.editor_id', '=', $editor_id);

            if($ebm_status != ""){
                $ebm_role = $ebm_role->where('e.ebm_status', '=', $ebm_status);
            }

            $ebm_role = $ebm_role->orderByRaw('e.ebm_status = ? desc', 'active')
            ->paginate(10);

        return $ebm_role;
    }

    /*
    * Date: 11-04-2023
    * Author: Zubair
    * Parameter: id, status
    * Description: This function will change editor status by editor id
    */

    public static function ChangeEditorStatus($id, $status)
    {
        $editor_update = DB::table('tbl_editors')->where('editor_id', $id)->update(array('status' => $status, 'updated_on' => now(), 'updated_by' => Auth::user()->id));

        return $editor_update;
    }

    /*
    * Date: 13-04-2023
    * Author: Zubair
    * Parameter: ebm_id
    * Description: This function will return editor role by ebm_id
    */

    public static function GetEBMRolebyID($ebm_id)
    {
        $ebm_role = DB::table('tbl_ebm as e')
            ->select('e.*', 'ed.fname', 'ed.mname', 'ed.lname')
            ->join('tbl_editors as ed', 'ed.editor_id', '=', 'e.editor_id')
            ->where('e.ebm_id', '=', $ebm_id)
            ->first();

        return $ebm_role;
    }

    /*
    * Date: 13-04-2023
    * Author: Zubair
    * Parameter: ebm_id, ebmChanges
    * Description: This function will return ebm by ebm_id and specific column from table
    */

    public static function GetEBMbyID($ebm_id, $ebmChanges = "")
    {
        $ebm = DB::table('tbl_ebm as e');

        if (!empty($ebmChanges)) {
            $ebm = $ebm->selectRaw($ebmChanges);
        } else {
            $ebm = $ebm->select('*');
        }

        $ebm = $ebm->where('e.ebm_id', '=', $ebm_id)->first();

        return $ebm;
    }

    /*
    * Date: 13-04-2023
    * Author: Zubair
    * Parameter: data, id
    * Description: This function will update editor role by id
    */

    public static function UpdateEditorRole($data, $id)
    {
        $ebm_role_update = DB::table('tbl_ebm')->where('ebm_id', $id)->update($data);

        return $ebm_role_update;
    }

    /*
    * Date: 14-04-2023
    * Author: Zubair
    * Parameter: id
    * Description: This function will delete ebm_role by ID
    */

    public static function DeleteEBMRolebyID($id)
    {
        $ebm_role_delete = DB::table('tbl_ebm')->where('ebm_id', $id)->delete();

        return $ebm_role_delete;
    }

    /*
    * Date: 14-04-2023
    * Author: Zubair
    * Parameter: id, action
    * Description: This function will insert ebm role into ebm_history table
    */

    public static function InsertEBMRoleHistory($id, $action)
    {
        $select = DB::table('tbl_ebm as e')
            ->selectRaw('conditional_ebm,executive_ge,ebm_id,added_by,journal_id,editor_id,ebm_role_id,region_id,section,section_region,acceptance_date,acceptance_email_text,resign_date,resign_email_text, cv_content_type, cv_name, cv_updated_on,email_content_type,email_accp_name,email_accp_created_on, agreement_form_name,agreement_form_content_type,agreement_form_updated_on,
            eic_approval,eic_approval_content_type,eic_approval_updated_on,director_approval,director_approval_content_type,director_approval_updated_on,created_on,updated_by,updated_on,ebm_status,source,flag,now(),"' . $action . '"')
            ->where('e.ebm_id', $id);

        $rows_inserted = DB::table('tbl_ebm_history')
            ->insertUsing([
                'conditional_ebm', 'executive_ge', 'ebm_id', 'added_by', 'journal_id', 'editor_id', 'role_id', 'region_id', 'section', 'section_region', 'acceptance_date', 'acceptance_email_text', 'resign_date', 'resign_email_text', 'cv_content_type', 'cv_name', 'cv_updated_on', 'email_content_type', 'email_accp_name', 'email_accp_created_on', 'agreement_form_name', 'agreement_form_content_type', 'agreement_form_updated_on',
                'eic_approval', 'eic_approval_content_type', 'eic_approval_updated_on', 'director_approval', 'director_approval_content_type', 'director_approval_updated_on', 'created_on', 'updated_by', 'updated_on', 'status', 'source', 'flag', 'action_on', 'action'
            ], $select);
    }

    /*
    * Date: 14-04-2023
    * Author: Zubair
    * Parameter: id
    * Description: This function will delete editor by ID
    */

    public static function DeleteEditor($id)
    {
        $editor_delete = DB::table('tbl_editors')->where('editor_id', $id)->delete();

        return $editor_delete;
    }

    /*
    * Date: 17-04-2023
    * Author: Zubair
    * Parameter: param
    * Description: This function will Generate EBM Report
    */

    public static function GetEBMReport($param)
    {
        $ebm_report = DB::table('tbl_editors as e')
            ->selectRaw('j.title,r.nicename,eb.conditional_ebm,eb.executive_ge,eb.section_region,e.fname,e.mname,e.lname,eb.acceptance_date,e.degree,e.titles,e.salutation,e.suffix,e.institute,e.expertise,e.h_index,e.orcid_id,e.researcher_id,e.research_gate,e.google_scholar,e.scopus_id,e.university,e.pharmaceutical,e.department,e.laboratory,e.library,e.company,e.address,e.zip_code,e.city,e.state,e.country,e.phone,e.fax,e.mobile,e.email,e.alt_email,e.url,e.web_format,e.created_on as ebm_added_on,e.added_by as ebm_added_by')
            ->join('tbl_ebm as eb', 'eb.editor_id', '=', 'e.editor_id')
            ->join('journal as j', 'j.journal_id', '=', 'eb.journal_id')
            ->join('tbl_ebm_role as r', 'r.role_id', '=', 'eb.ebm_role_id');

        if ($param['journal_id'] != "") {
            $journal_id = $param['journal_id'];
            $ebm_report = $ebm_report->where('eb.journal_id', '=', $journal_id);
        }
        if ($param['role_id'] != "") {
            $role_id = $param['role_id'];
            $ebm_report = $ebm_report->where('eb.ebm_role_id', '=', $role_id);
        }
        if ($param['name'] != "") {
            $name = $param['name'];
            $ebm_report = $ebm_report->where('e.fname', 'like', '%' . $name . '%');
            $ebm_report = $ebm_report->orwhere('e.mname', 'like', '%' . $name . '%');
            $ebm_report = $ebm_report->orwhere('e.lname', 'like', '%' . $name . '%');
        }
        if ($param['email'] != "") {
            $email = $param['email'];
            $ebm_report = $ebm_report->where('e.email', 'like', '%' . $email . '%');
            $ebm_report = $ebm_report->orwhere('e.alt_email', 'like', '%' . $email . '%');
        }
        if ($param['country'] != "") {
            $country = $param['country'];
            $ebm_report = $ebm_report->where('e.country', 'like', '%' . $country . '%');
        }
        if ($param['keyword'] != "") {
            $keyword = $param['keyword'];
            $ebm_report = $ebm_report->where('e.institute', 'like', '%' . $keyword . '%');
            $ebm_report = $ebm_report->orwhere('e.university', 'like', '%' . $keyword . '%');
            $ebm_report = $ebm_report->orwhere('e.pharmaceutical', 'like', '%' . $keyword . '%');
            $ebm_report = $ebm_report->orwhere('e.department', 'like', '%' . $keyword . '%');
            $ebm_report = $ebm_report->orwhere('e.laboratory', 'like', '%' . $keyword . '%');
            $ebm_report = $ebm_report->orwhere('e.library', 'like', '%' . $keyword . '%');
            $ebm_report = $ebm_report->orwhere('e.phone', 'like', '%' . $keyword . '%');
            $ebm_report = $ebm_report->orwhere('e.zip_code', 'like', '%' . $keyword . '%');
            $ebm_report = $ebm_report->orwhere('e.city', 'like', '%' . $keyword . '%');
            $ebm_report = $ebm_report->orwhere('e.state', 'like', '%' . $keyword . '%');
        }

        $ebm_report = $ebm_report->where('e.status', '!=', 'D');
        $ebm_report = $ebm_report->where('eb.ebm_status', '=', 'active');
        $ebm_report = $ebm_report->where('j.journal_status', '=', '1');

        $ebm_report = $ebm_report->orderBy('e.lname', 'ASC');
        $ebm_report = $ebm_report->get();

        return $ebm_report;
    }

    /*
    * Date: 17-04-2023
    * Author: Zubair
    * Parameter: name
    * Description: This function will return city list by country name
    */

    public static function GetCityList($name)
    {
        $city_list = DB::table('tbl_institutes')
            ->select('*')
            ->where('country', $name)
            ->groupby('city')
            ->orderBy('city', 'ASC')
            ->get();

        return $city_list;
    }

    /*
    * Date: 17-04-2023
    * Author: Zubair
    * Parameter: name
    * Description: This function will return institute list by country and city name
    */

    public static function GetInstituteList($country_name, $city_name)
    {
        $institute_list = DB::table('tbl_institutes')
            ->select('*')
            ->where('country', $country_name)
            ->where('city', $city_name)
            ->orderBy('name', 'ASC')
            ->get();

        return $institute_list;
    }

    /*
    * Date: 19-04-2023
    * Author: Zubair
    * Parameter: editor_id
    * Description: This function will return institute name by editor_id
    */

    public static function GetEditorInstituteName($editor_id)
    {
        $institute_name = DB::table('tbl_editors as e')
            ->join('tbl_institutes as i', 'i.institution_id', '=', 'e.institution_id')
            ->select('e.editor_id', 'i.*')
            ->where('e.editor_id', $editor_id)
            ->first();

        return $institute_name;
    }

    /*
    * Date: 20-04-2023
    * Author: Zubair
    * Parameter: editor_id
    * Description: This function will return institute by institution_id
    */

    public static function GetInstitutebyID($institution_id)
    {
        $institute = DB::table('tbl_institutes')
            ->select('*')
            ->where('institution_id', $institution_id)
            ->first();

        return $institute;
    }

    /*
     * Author: Zubair
     * Parameter: none
     * Description: This function will return journal list
     */
    public static function GetJournalList()
    {
        $journal = DB::table('journal')
            ->selectRaw("concat(subtitle, ' - ', title) as title,journal_id")
            ->where('journal_status', '1')
            ->orderBy('subtitle')
            ->get();
        return $journal;
    }

    /*
     * Date: 08-05-2023
     * Author: Muhammad Zubair
     * Description:  this function will return the  country/list of countires
     */
    public static function GetCountryList($id = "", $iso = "")
    {
        $country = DB::table('country')->get();
        return $country;
    }

    /*
     * Author: Zubair
     * Parameter: none
     * Description: This function will return all regions
     */
    public static function GetAllRegions()
    {
        $region = DB::table('tbl_regions')
            ->select("*")
            ->orderBy('name')
            ->get();
        return $region;
    }

    /*
    * Date: 29-11-2023
    * Author: Zubair
    * Parameter: data
    * Description: This function will add data in editor_verification table
    */
    public static function AddEditorVerification($data)
    {
        $editor_add = DB::table('tbl_editors_verification')->insertGetId($data);

        return $editor_add;
    }

    /*
    * Date: 29-11-2023
    * Author: Zubair
    * Parameter: data
    * Description: This function will add data in ebm_verification table
    */
    public static function AddEditorRoleVerification($data)
    {
        $editor_role_add = DB::table('tbl_ebm_verification')->insertGetId($data);

        return $editor_role_add;
    }

    /*
    * Date: 29-11-2023
    * Author: Zubair
    * Parameter: param
    * Description: This function display pending,approve,reject list of ebm verification
    */
    public static function GetVerificationEditorsList($param = "")
    {
        $editors = DB::table('tbl_editors_verification as ev')
            ->select(DB::raw('ev.*, CONCAT_WS(" ", NULLIF(ed.fname, ""), NULLIF(ed.mname, ""), NULLIF(ed.lname, "")) as full_name, ed.email as editor_email, ebv.editor_id as ebm_editor_id, ebv.ebm_verification_id, ebv.ebm_verification_comment,ebv.ebm_verification_status, ebv.created_on as ebm_created_on, ebv.ebm_changes, ebv.ebm_id, ed.fname as editor_fname, ed.mname as editor_mname, ed.lname as editor_lname, ed.titles as editor_titles, ed.salutation as editor_salutation, ed.suffix as editor_suffix, ed.h_index as editor_h_index, ed.orcid_id as editor_orcid_id, ed.researcher_id as editor_researcher_id, ed.research_gate as editor_research_gate, ed.google_scholar as editor_google_scholar, ed.scopus_id as editor_scopus_id, ed.zip_code as editor_zip_code, ed.phone as editor_phone, ed.mobile as editor_mobile, ed.country as editor_country, ed.city as editor_city, ed.state as editor_state, ed.pharmaceutical as editor_pharmaceutical,ed.company as editor_company,ed.laboratory as editor_laboratory,ed.fax as editor_fax,ed.alt_email as editor_alt_email,ed.degree as editor_degree,ed.institute as editor_institute,ed.university as editor_university,ed.department as editor_department,ed.library as editor_library,ed.expertise as editor_expertise,ed.address as editor_address,ed.web_format as editor_web_format,ed.biography as editor_biography, editor_user.display_name as editor_add_user,ebm_user.display_name as ebm_add_user,ed.profile_pic as editor_profile_pic'))
            ->leftjoin('tbl_editors as ed', 'ed.editor_id', '=', 'ev.editor_id')
            ->leftjoin('tbl_ebm_verification as ebv', 'ebv.editor_id', '=', 'ed.editor_id')
            ->leftjoin('users as editor_user', 'editor_user.id', '=', 'ev.added_by')
            ->leftjoin('users as ebm_user', 'ebm_user.id', '=', 'ebv.added_by');

        if (!empty($param['verification_status'])) {
            $editors->where(function ($query) use ($param) {
                $query->where('ev.verification_status', '=', $param['verification_status'])
                    ->orWhere('ebv.ebm_verification_status', '=', $param['verification_status']);
            });
        }

        if (!empty($param['editor_id'])) {
            $editors = $editors->where('ev.editor_id', '=', $param['editor_id']);
        }

        if (!empty($param['email'])) {
            $editors->where(function ($query) use ($param) {
                $query->where('ev.email', '=', $param['email'])
                    ->orWhere('ed.email', '=', $param['email']);
            });
        }

        if (!empty($param['user_id'])) {
            $editors->where(function ($query) use ($param) {
                $query->where('editor_user.id', '=', $param['user_id'])
                    ->orWhere('ebm_user.id', '=', $param['user_id']);
            });
        }

        if ($param['verification_status'] == 'pending') {
            $editors = $editors->groupBy('ev.editor_id');
            $editors = $editors->groupBy('ebv.editor_id');
        } else {
            $editors = $editors->groupBy('ev.id');
            $editors = $editors->groupBy('ebv.ebm_verification_id');
        }

        //$editors = $editors->orderBy('ev.updated_on', 'DESC');
        //$editors = $editors->orderBy('ebv.updated_on', 'DESC');

        $editors = $editors->orderByRaw('COALESCE(UNIX_TIMESTAMP(ev.created_on), ebv.created_on) DESC');

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

        return $editors;
    }

    /*
    * Date: 29-11-2023
    * Author: Zubair
    * Parameter: verification_id, editorChanges
    * Description: This function return verification editor by id or specific column from table
    */
    public static function GetVerificationEditorbyID($verification_id, $editorChanges = "")
    {
        $editor = DB::table('tbl_editors_verification');

        if (!empty($editorChanges)) {
            $editor = $editor->selectRaw($editorChanges);
        } else {
            $editor = $editor->select('fname', 'mname', 'lname', 'degree', 'titles', 'salutation', 'suffix', 'institute', 'university', 'pharmaceutical', 'company', 'department', 'laboratory', 'library', 'expertise', 'h_index', 'orcid_id', 'researcher_id', 'research_gate', 'google_scholar', 'scopus_id', 'biography', 'address', 'zip_code', 'city', 'state', 'country', 'phone', 'fax', 'mobile', 'email', 'alt_email', 'url', 'cv_content_type', 'cv_name', 'profile_pic', 'profile_pic_type', 'web_format', 'added_by', 'created_on', 'updated_on', 'status', 'institution_id');
        }

        $editor = $editor->where('id', '=', $verification_id)->first();

        return $editor;
    }

    /*
    * Date: 29-11-2023
    * Author: Zubair
    * Parameter: verification_id, data
    * Description: This function update editor_verification table
    */
    public static function UpdateEditorVerification($verification_id, $data)
    {

        $verification_update = DB::table('tbl_editors_verification')
            ->where('id', $verification_id)
            ->update($data);

        return $verification_update;
    }

    /*
    * Date: 29-11-2023
    * Author: Zubair
    * Parameter: ebmverificationId, data
    * Description: This function update ebm_verification table
    */
    public static function UpdateEbmVerification($ebmverificationId, $data)
    {

        $ebm_verification_update = DB::table('tbl_ebm_verification')
            ->where('ebm_verification_id', $ebmverificationId)
            ->update($data);

        return $ebm_verification_update;
    }

    /*
    * Date: 29-11-2023
    * Author: Zubair
    * Parameter: ebmverificationId, ebmChanges
    * Description: This function return ebm verification by ebmverificationId or specific column from table
    */
    public static function GetEbmVerificationEditorbyID($ebmverificationId, $ebmChanges = "")
    {
        $ebm = DB::table('tbl_ebm_verification');

        if (!empty($ebmChanges)) {
            $ebm = $ebm->selectRaw($ebmChanges);
        } else {
            $ebm = $ebm->select('ebm_id', 'added_by', 'journal_id_jms', 'journal_id', 'editor_id', 'ebm_role_id', 'role_id', 'region_id', 'section', 'ebm_role_name', 'conditional_ebm', 'executive_ge', 'section_region', 'acceptance_date', 'acceptance_email_text', 'resign_date', 'resign_email_text', 'cv_content_type', 'cv_name', 'cv_updated_on', 'email_content_type', 'email_accp_name', 'email_accp_created_on', 'agreement_form_name', 'agreement_form_content_type', 'agreement_form_updated_on', 'eic_approval', 'eic_approval_content_type', 'eic_approval_updated_on', 'director_approval', 'director_approval_content_type', 'director_approval_updated_on', 'created_on', 'updated_by', 'updated_on', 'ebm_status', 'source', 'flag', 'editor_user_id');
        }

        $ebm = $ebm->where('ebm_verification_id', '=', $ebmverificationId)->first();

        return $ebm;
    }

    /*
    * Date: 29-11-2023
    * Author: Zubair
    * Parameter: ebmverificationId
    * Description: This function return pending add role request from ebm verification table
    */
    public static function GetVerificationEbmRolebyID($ebmverificationId)
    {
        $ebm_new_role = DB::table('tbl_ebm_verification as e')
            ->select('e.*', 'j.subtitle', 'er.nicename')
            ->join('tbl_ebm_role as er', 'er.role_id', '=', 'e.ebm_role_id')
            ->join('journal as j', 'j.journal_id', '=', 'e.journal_id')
            ->where('e.ebm_verification_id', '=', $ebmverificationId)
            ->where('e.ebm_verification_status', '=', 'pending')
            ->where('e.ebm_verification_comment', '=', 'Add Editor Role')
            ->first();

        return $ebm_new_role;
    }

    /*
    * Date: 08-12-2023
    * Author: Zubair
    * Parameter: param
    * Description: This function will return approve or reject list of ebm
    */
    public static function GetApproveorRejectListofEBM($param)
    {
        $query1 = DB::table('tbl_editors_verification')->select(
            'tbl_editors_verification.id AS verification_id',
            'verification_comment',
            'verification_status',
            'editor_changes',
            DB::raw('CONCAT_WS(" ", NULLIF(tbl_editors.fname, ""), NULLIF(tbl_editors.mname, ""), NULLIF(tbl_editors.lname, "")) AS full_name'),
            'tbl_editors.email AS editor_email',
            DB::raw('NULL AS ebm_editor_id'),
			'tbl_editors_verification.editor_id',
            DB::raw('NULL AS ebm_verification_id'),
            DB::raw('NULL AS ebm_verification_comment'),
            DB::raw('NULL AS ebm_verification_status'),
            DB::raw('NULL AS ebm_updated_on'),
            DB::raw('NULL AS ebm_created_on'),
            DB::raw('NULL AS ebm_changes'),
            DB::raw('NULL AS ebm_id'),
            'tbl_editors_verification.fname',
            'tbl_editors_verification.mname',
            'tbl_editors_verification.lname',
            'tbl_editors_verification.email',
            'tbl_editors_verification.updated_on as updated_on',
            'tbl_editors_verification.created_on as created_on',
            'tbl_editors_verification.editor_comment',
            DB::raw('NULL AS ebm_comment'),
            'users.display_name as editor_add_user',
            DB::raw('NULL AS ebm_add_user'),
            'editor_users.display_name as editor_updated_by',
            DB::raw('NULL AS ebm_updated_by')
        )
            ->leftJoin('tbl_editors', 'tbl_editors.editor_id', '=', 'tbl_editors_verification.editor_id')
            ->leftjoin('users', 'users.id', '=', 'tbl_editors_verification.added_by')
            ->leftjoin('users as editor_users', 'editor_users.id', '=', 'tbl_editors_verification.updated_by');

        if (!empty($param['verification_status'])) {
            $query1 = $query1->where('tbl_editors_verification.verification_status', '=', $param['verification_status']);
        } else {
            $query1 = $query1->whereRaw("(tbl_editors_verification.verification_status = 'approve' OR tbl_editors_verification.verification_status = 'reject')");
        }

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

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

        if (!empty($param['editor_id'])) {
            $query1 = $query1->where('tbl_editors_verification.editor_id', '=', $param['editor_id']);
        }

        if (isset($param['verification_status']) && $param['verification_status'] == 'reject') {
            $query1 = $query1->where('tbl_editors_verification.reject_request_status', '=', 0);
        }

        $query1 = $query1->groupBy('tbl_editors_verification.id');

        // Your second query
        $query2 = DB::table('tbl_ebm_verification')->select(
            DB::raw('NULL AS verification_id'),
            DB::raw('NULL AS verification_comment'),
            DB::raw('NULL AS verification_status'),
            DB::raw('NULL AS editor_changes'),
            DB::raw('CONCAT_WS(" ", NULLIF(tbl_editors.fname, ""), NULLIF(tbl_editors.mname, ""), NULLIF(tbl_editors.lname, "")) AS full_name'),
            'tbl_editors.email AS editor_email',
            'tbl_ebm_verification.editor_id AS ebm_editor_id',
			DB::raw('NULL AS editor_id'),
            'tbl_ebm_verification.ebm_verification_id',
            'tbl_ebm_verification.ebm_verification_comment',
            'tbl_ebm_verification.ebm_verification_status',
            'tbl_ebm_verification.updated_on AS ebm_updated_on',
            'tbl_ebm_verification.created_on AS ebm_created_on',
            'tbl_ebm_verification.ebm_changes',
            'tbl_ebm_verification.ebm_id',
            'tbl_editors_verification.fname',
            'tbl_editors_verification.mname',
            'tbl_editors_verification.lname',
            'tbl_editors_verification.email',
            DB::raw('NULL AS updated_on'),
            DB::raw('NULL AS created_on'),
            DB::raw('NULL AS editor_comment'),
            'tbl_ebm_verification.ebm_comment',
            DB::raw('NULL AS editor_add_user'),
            'users.display_name as ebm_add_user',
            DB::raw('NULL AS editor_updated_by'),
            'ebm_users.display_name as ebm_updated_by'
        )
            ->leftJoin('tbl_editors', 'tbl_editors.editor_id', '=', 'tbl_ebm_verification.editor_id')
            ->leftJoin('tbl_editors_verification', 'tbl_editors_verification.editor_id', '=', 'tbl_ebm_verification.editor_id')
            ->leftjoin('users', 'users.id', '=', 'tbl_ebm_verification.added_by')
            ->leftjoin('users as ebm_users', 'ebm_users.id', '=', 'tbl_ebm_verification.updated_by');

        if (!empty($param['verification_status'])) {
            $query2 = $query2->where('tbl_ebm_verification.ebm_verification_status', '=', $param['verification_status']);
        } else {
            $query2 = $query2->whereRaw("(tbl_ebm_verification.ebm_verification_status = 'approve' OR tbl_ebm_verification.ebm_verification_status = 'reject')");
        }

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

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

        if (!empty($param['editor_id'])) {
            $query2 = $query2->where('tbl_ebm_verification.editor_id', '=', $param['editor_id']);
        }

        if (isset($param['verification_status']) && $param['verification_status'] == 'reject') {
            $query2 = $query2->where('tbl_ebm_verification.reject_request_status', '=', 0);
        }

        $query2 = $query2->groupBy('tbl_ebm_verification.ebm_verification_id');

        $subquery = $query1->unionAll($query2);

        $result = DB::table(DB::raw("({$subquery->toSql()}) as combined"))
            ->mergeBindings($subquery)
            ->orderByRaw('COALESCE(updated_on, ebm_updated_on) DESC')
            ->paginate(10);

        return $result;
    }

    /*
    * Date: 26-12-2023
    * Author: Zubair
    * Parameter: ebm_id
    * Description: This function will return ebm detail information by ebm_id
    */
    public static function GetEBMListbyEBMId($ebm_id)
    {
        $ebm_list = DB::table('tbl_ebm as e')
            ->select('*')
            ->join('tbl_ebm_role as er', 'er.role_id', '=', 'e.ebm_role_id')
            ->join('journal as j', 'j.journal_id', '=', 'e.journal_id');

        $ebm_list = $ebm_list->where('e.ebm_id', '=', $ebm_id)->first();

        return $ebm_list;
    }

    /*
    * Date: 31-01-2024
    * Author: Zubair
    * Parameter: ebm_verification_id
    * Description: This function will return ebm_verification by ebm_verification_id
    */
    public static function GetEBMVerificationUser($ebm_verification_id)
    {
        $ebm_role = DB::table('tbl_ebm_verification as e')
            ->select('e.*', 'ed.fname', 'ed.mname', 'ed.lname')
            ->join('tbl_editors as ed', 'ed.editor_id', '=', 'e.editor_id')
            ->where('e.ebm_verification_id', '=', $ebm_verification_id)
            ->first();

        return $ebm_role;
    }

    /*
    * Date: 06-02-2024
    * Author: Zubair
    * Parameter: email, verification_comment
    * Description: This function update reject status of editor verification table
    */
    public static function UpdateEditorRejectStatus($email, $verification_comment)
    {
        $data = ['reject_request_status' => 1];

        $verification_update = DB::table('tbl_editors_verification')
            ->where('email', $email)
            ->where('verification_comment', $verification_comment)
            ->where('verification_status', 'reject')
            ->update($data);

        return $verification_update;
    }

        /*
    * Date: 06-02-2024
    * Author: Zubair
    * Parameter: editor_id, ebm_verification_comment
    * Description: This function update reject status of ebm verification table
    */
    public static function UpdateEditorRoleRejectStatus($editor_id, $ebm_verification_comment)
    {
        $data = ['reject_request_status' => 1];

        $ebm_update = DB::table('tbl_ebm_verification')
            ->where('editor_id', $editor_id)
            ->where('ebm_verification_comment', $ebm_verification_comment)
            ->where('ebm_verification_status', 'reject')
            ->update($data);

        return $ebm_update;
    }
}
