<?php

namespace App\Models;

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

class Manuscript extends Model
{
    /*
     * Date: 23-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return awaiting manuscript list
    */
    public static function AwaitingManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_manuscript as m")
            ->select('m.*', 'i.*', 'at.article_type_name', DB::Raw('(select concat(first_name," ",last_name,"_",email_address,"_",institution) from tbl_authors aa join tbl_author_affiliation aaa on aaa.author_id=aa.author_id where aa.manuscript_id=m.manuscript_id and aa.is_principal=1 group by aa.author_id limit 1) as PrincipalAuthor'))

            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id')
            ->join('tbl_author_affiliation as af', 'af.author_id', '=', 'a.author_id')
            ->join('tbl_institutes as i', 'i.institution_id', '=', 'af.institution_id');
			
			$manuscript = $manuscript->whereRaw("((m.manuscript_type != 'P' and m.status = 'A' and m.submission_step = 0) OR (m.manuscript_type = 'M' and m.status = 'A' and m.submission_step > 0 and m.abstract_approved = 1))");

            $manuscript = $manuscript->where('m.lock_qa_opt', '!=', 2)
            ->where('m.publish', '!=', 2)
            ->whereNotIn('m.manuscript_id', DB::connection('jms')->table('tbl_institute_member_approval as ima')->select('ima.manuscript_id'));

        $institution = explode(",", $param['institution_associations']);
        $manuscript = $manuscript->whereIn('af.institution_id', $institution);

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

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

    /*
     * Date: 23-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return total awaiting manuscript
    */
    public static function TotalAwaitingManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_manuscript as m")
            ->selectRaw('count(distinct(m.`manuscript_id`)) AS total')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id')
            ->join('tbl_author_affiliation as af', 'af.author_id', '=', 'a.author_id')
            ->join('tbl_institutes as i', 'i.institution_id', '=', 'af.institution_id');
			
			$manuscript = $manuscript->whereRaw("((m.manuscript_type != 'P' and m.status = 'A' and m.submission_step = 0) OR (m.manuscript_type = 'M' and m.status = 'A' and m.submission_step > 0 and m.abstract_approved = 1))");

            $manuscript = $manuscript->where('m.lock_qa_opt', '!=', 2)
            ->where('m.publish', '!=', 2)
            ->whereNotIn('m.manuscript_id', DB::connection('jms')->table('tbl_institute_member_approval as ima')->select('ima.manuscript_id'));

        $institution = explode(",", $param['institution_associations']);
        $manuscript = $manuscript->whereIn('af.institution_id', $institution);

        $manuscript = $manuscript->first();

        return $manuscript;
    }

    /*
     * Date: 23-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return approved manuscript list
    */
    public static function ApprovedManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_institute_member_approval as ima")
            ->select('m.*', 'i.*', 'at.article_type_name', DB::Raw('(select concat(first_name," ",last_name,"_",email_address,"_",institution) from tbl_authors aa join tbl_author_affiliation aaa on aaa.author_id=aa.author_id where aa.manuscript_id=m.manuscript_id and aa.is_principal=1 group by aa.author_id) as PrincipalAuthor'))

            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'ima.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_institutes as i', 'i.institution_id', '=', 'ima.institution_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '!=', 2)
            ->where('m.publish', '!=', 2)
            ->where('ima.eurekaselect_user_id', '=', $param['user_id'])
            ->where('ima.manuscript_status', '=', 1);

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

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

    /*
     * Date: 23-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return total approved manuscript
    */
    public static function TotalApprovedManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_institute_member_approval as ima")

            ->selectRaw('count(distinct(m.`manuscript_id`)) AS total')
            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'ima.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_institutes as i', 'i.institution_id', '=', 'ima.institution_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '!=', 2)
            ->where('m.publish', '!=', 2)
            ->where('ima.eurekaselect_user_id', '=', $param['user_id'])
            ->where('ima.manuscript_status', '=', 1);

        $manuscript = $manuscript->first();

        return $manuscript;
    }

    /*
     * Date: 23-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return rejected manuscript list
    */
    public static function RejectedManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_institute_member_approval as ima")
            ->select('m.*', 'i.*', 'at.article_type_name', DB::Raw('(select concat(first_name," ",last_name,"_",email_address,"_",institution) from tbl_authors aa join tbl_author_affiliation aaa on aaa.author_id=aa.author_id where aa.manuscript_id=m.manuscript_id and aa.is_principal=1 group by aa.author_id) as PrincipalAuthor'))

            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'ima.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_institutes as i', 'i.institution_id', '=', 'ima.institution_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '!=', 2)
            ->where('m.publish', '!=', 2)
            ->where('ima.eurekaselect_user_id', '=', $param['user_id'])
            ->where('ima.manuscript_status', '=', 0);

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

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

    /*
     * Date: 23-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return total rejected manuscript
    */
    public static function TotalRejectedManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_institute_member_approval as ima")

            ->selectRaw('count(distinct(m.`manuscript_id`)) AS total')

            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'ima.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_institutes as i', 'i.institution_id', '=', 'ima.institution_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '!=', 2)
            ->where('m.publish', '!=', 2)
            ->where('ima.eurekaselect_user_id', '=', $param['user_id'])
            ->where('ima.manuscript_status', '=', 0);

        $manuscript = $manuscript->first();

        return $manuscript;
    }


    /*
     * Date: 12-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return pending invoice of manuscript
    */
    public static function GetManuscriptPendingInvoice($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_invoice as i")
            ->selectRaw('m.reference_number,m.date_submitted,m.title,ins.name,at.article_type_name,i.total_amount,i.invoice_id,i.invoice_number,i.invoice_date,i.transaction_status,(select concat(first_name," ",last_name,"_",email_address,"_",institution) from tbl_authors aa join tbl_author_affiliation aaa on aaa.author_id=aa.author_id where aa.manuscript_id=m.manuscript_id and aa.is_principal=1) as PrincipalAuthor')
            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'i.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id')
            ->join('tbl_author_affiliation as af', 'af.author_id', '=', 'a.author_id')
            ->join('tbl_institutes as ins', 'ins.institution_id', '=', 'af.institution_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '=', 2)
            ->where('m.publish', '!=', 2);
        $institution = explode(",", $param['institution_associations']);
        $manuscript = $manuscript->whereIn('af.institution_id', $institution);
        $manuscript = $manuscript->whereRaw('i.transaction_status not in ("Complete","Cancel")');

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

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

    /*
     * Date: 12-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return total pending invoice of manuscript
    */
    public static function TotalManuscriptPendingInvoice($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_invoice as i")

            ->selectRaw('count(distinct(m.`manuscript_id`)) AS total')
            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'i.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id')
            ->join('tbl_author_affiliation as af', 'af.author_id', '=', 'a.author_id')
            ->join('tbl_institutes as ins', 'ins.institution_id', '=', 'af.institution_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '=', 2)
            ->where('m.publish', '!=', 2);
        $institution = explode(",", $param['institution_associations']);
        $manuscript = $manuscript->whereIn('af.institution_id', $institution);
        $manuscript = $manuscript->whereRaw('i.transaction_status not in ("Complete","Cancel")');

        $manuscript = $manuscript->first();

        return $manuscript;
    }

    /*
     * Date: 12-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return paid invoice of manuscript
    */
    public static function GetManuscriptPaidInvoice($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_invoice as i")
            ->selectRaw('m.reference_number,m.date_submitted,m.title,ins.name,at.article_type_name,i.total_amount,i.invoice_id,i.invoice_number,i.invoice_date,i.transaction_status,(select concat(first_name," ",last_name,"_",email_address,"_",institution) from tbl_authors aa join tbl_author_affiliation aaa on aaa.author_id=aa.author_id where aa.manuscript_id=m.manuscript_id and aa.is_principal=1) as PrincipalAuthor')
            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'i.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id')
            ->join('tbl_author_affiliation as af', 'af.author_id', '=', 'a.author_id')
            ->join('tbl_institutes as ins', 'ins.institution_id', '=', 'af.institution_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '=', 2)
            ->where('m.publish', '!=', 2);
        $institution = explode(",", $param['institution_associations']);
        $manuscript = $manuscript->whereIn('af.institution_id', $institution);
        $manuscript = $manuscript->where('i.transaction_status', '=', 'Complete');

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

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

    /*
     * Date: 12-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return total paid invoice of manuscript
    */
    public static function TotalManuscriptPaidInvoice($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_invoice as i")

            ->selectRaw('count(distinct(m.`manuscript_id`)) AS total')
            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'i.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id')
            ->join('tbl_author_affiliation as af', 'af.author_id', '=', 'a.author_id')
            ->join('tbl_institutes as ins', 'ins.institution_id', '=', 'af.institution_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '=', 2)
            ->where('m.publish', '!=', 2);
        $institution = explode(",", $param['institution_associations']);
        $manuscript = $manuscript->whereIn('af.institution_id', $institution);
        $manuscript = $manuscript->where('i.transaction_status', '=', 'Complete');

        $manuscript = $manuscript->first();

        return $manuscript;
    }

    /*
     * Date: 11-01-2023
     * Author: Zubair
     * Parameter: institution_association
     * Description:  this function will return total publish manuscript of each institute
    */
    public static function PublishedManuscriptofEachInstitute($institution_association)
    {
        $manuscript = DB::connection('jms')->table("tbl_institutes as i")
            ->selectRaw('i.*,count(distinct m.manuscript_id) as total');

        $manuscript = $manuscript->leftjoin('tbl_author_affiliation as af', 'af.institution_id', '=', 'i.institution_id');
        $manuscript = $manuscript->leftjoin('tbl_authors as a', 'a.author_id', '=', 'af.author_id');

        $manuscript = $manuscript->leftjoin('tbl_manuscript as m', function ($query) {
            $query->on('m.manuscript_id', 'a.manuscript_id')
                ->where('m.manuscript_type', 'M')
                ->where('m.submission_step', 0)
                ->where('m.status', 'A')
                ->where('m.publish', '=', 2);
        });

        $manuscript = $manuscript->whereIn('i.institution_id', $institution_association);
        $manuscript = $manuscript->groupBy('i.institution_id');
        $manuscript = $manuscript->get();
        return $manuscript;
    }

    /*
     * Date: 16-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return publish manuscript list of institute by institute id
    */
    public static function PublishedManuscriptbyInstituteID($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_manuscript as m")->select('m.*', 'at.article_type_name', DB::Raw('(select concat(first_name," ",last_name) from tbl_authors aa where aa.manuscript_id=m.manuscript_id and aa.is_principal=1) as PrincipalAuthor'));

        $manuscript = $manuscript->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id');
        $manuscript = $manuscript->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id');
        $manuscript = $manuscript->join('tbl_author_affiliation as af', 'af.author_id', '=', 'a.author_id');

        $manuscript = $manuscript->where('m.manuscript_type', 'M');
        $manuscript = $manuscript->where('m.submission_step', 0);
        $manuscript = $manuscript->where('m.status', 'A');
        $manuscript = $manuscript->where('m.publish', '=', 2);

        if (!empty($param['institute_id'])) {
            $institute_id = $param['institute_id'];
            $manuscript = $manuscript->where('af.institution_id', $institute_id);
        }

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');


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

    /*
     * Date: 11-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return total publish manuscript of institute
    */
    public static function TotalPublishedManuscriptofInstitute($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_institutes as i")
            ->selectRaw('count(distinct m.manuscript_id) as total');

        $manuscript = $manuscript->leftjoin('tbl_author_affiliation as af', 'af.institution_id', '=', 'i.institution_id');
        $manuscript = $manuscript->leftjoin('tbl_authors as a', 'a.author_id', '=', 'af.author_id');

        $manuscript = $manuscript->leftjoin('tbl_manuscript as m', function ($query) {
            $query->on('m.manuscript_id', 'a.manuscript_id')
                ->where('m.manuscript_type', 'M')
                ->where('m.submission_step', 0)
                ->where('m.status', 'A')
                ->where('m.publish', '=', 2);
        });

        $institution_associations = explode(",", $param['institution_associations']);
        $manuscript = $manuscript->whereIn('i.institution_id', $institution_associations);
        $manuscript = $manuscript->first();
        return $manuscript;
    }

    /*
     * Date: 12-01-2023
     * Author: Zubair
     * Parameter: manuscript_id
     * Description:  this function will return authors affiliation
    */
    public static function GetAuthorsAffiliationbyMSID($manuscript_id)
    {
        $manuscript = DB::connection('jms')->table("tbl_authors as a")
            ->select("*")
            ->leftjoin('tbl_author_affiliation as af', 'af.author_id', '=', 'a.author_id')
            ->where('a.manuscript_id', $manuscript_id)
            ->get();
        return $manuscript;
    }

    /*
     * Date: 12-01-2023
     * Author: Zubair
     * Parameter: grid_id
     * Description:  this function will return vip institute by grid_id
    */
    public static function GetVIPInstitutebygridID($grid_id)
    {
        $institute = DB::connection('jms')->table("tbl_institutes as i")
            ->select("*")
            ->join('tbl_vip_institutions as vi', 'vi.grid_id', '=', 'i.grid_id')
            ->where('i.institution_id', $grid_id)
            ->first();
        return $institute;
    }

    /*
     * Date: 12-01-2023
     * Author: Zubair
     * Parameter: manuscript_id
     * Description:  this function will return manuscript by manuscript_id
    */
    public static function GetManuscriptbyID($manuscript_id)
    {
        $manuscript = DB::connection('jms')->table("tbl_manuscript as m")
            ->leftjoin('tbl_manuscript_files as f', 'f.manuscript_id', '=', 'm.manuscript_id')
            ->select("*")
            ->where('m.manuscript_id', $manuscript_id)
            ->where('f.file_type', 1110)
            ->where('f.status', 'A')
			->orderBy('f.manuscript_file_id', 'desc')
            ->first();
        return $manuscript;
    }

    /*
     * Date: 12-01-2023
     * Author: Zubair
     * Parameter: manuscript_id,grid_id
     * Description:  this function will return authors and their affiliation of manuscript by manuscript_id
    */
    public static function GetAuthorbyManuscriptID($manuscript_id, $grid_id)
    {
        $authors = DB::connection('jms')->table("tbl_authors as a")
            ->select("*")
            ->join('tbl_author_affiliation as af', 'af.author_id', '=', 'a.author_id')
            ->leftjoin('tbl_country as c', 'c.iso3', '=', 'af.country')
            ->where('a.manuscript_id', $manuscript_id)
            ->orderByRaw('af.institution_id = ? desc', [$grid_id])
            ->groupBy('a.author_id')
            ->get();
        return $authors;
    }

    /*
     * Date: 16-01-2023
     * Author: Zubair
     * Parameter: data
     * Description:  this function will add data in tbl_institute_member_approval
    */
    public static function AddManuscript($data)
    {
        $id = DB::connection('jms')->table('tbl_institute_member_approval')->insertGetId($data);
        return $id;
    }



    public static function GetUserCustomTheme($user_id)
    {
        $user = DB::table("user_customize_css as u")
            ->select("*")
            ->where('u.user_id', $user_id)
            ->first();
        return $user;
    }

    /*
     * Date: 26-01-2023
     * Author: Zubair
     * Parameter: institute_id
    */

    public static function GetUserbyInstituteID($institute_id)
    {
        $user = DB::table("users as u")
            ->select("*")
            ->join('tbl_institutes as i', 'i.institution_id', '=', 'u.institution_id')
            ->whereIn('u.institution_id', $institute_id)
            ->get();

        return $user;
    }

    /*
     * Date: 27-01-2023
     * Author: Zubair
     * Parameter: user_id
    */

    public static function GetUserbyUserID($user_id)
    {
        $user = DB::table("users as u")
            ->select("*")
            ->leftjoin('tbl_institutes as i', 'i.institution_id', '=', 'u.institution_id')
            ->where('u.id', $user_id)
            ->get();

        return $user;
    }

    /*
     * Date: 30-01-2023
     * Author: Zubair
     * Parameter: manuscript_id, grid_id
     * Description:  this function will return authors and their affiliation of manuscript by manuscript_id
    */
    public static function GetSocietyAuthorbyManuscriptID($manuscript_id, $grid_id)
    {
        $authors = DB::connection('jms')->table("tbl_author_society as aus")
            ->selectRaw("a.*,af.institution,af.department,af.address,af.country,af.state,af.city,af.postal_code,af.phone,c.nicename,s.grid_id as institution_id")
            // ->select("*")
            ->join('tbl_authors as a', 'a.author_id', '=', 'aus.author_id')
            ->join('tbl_author_affiliation as af', 'af.author_id', '=', 'a.author_id')
            ->leftjoin('tbl_country as c', 'c.iso3', '=', 'af.country')
            ->join('tbl_societies as s', 's.society_id', '=', 'aus.society_id')
            ->where('aus.manuscript_id', $manuscript_id)
            ->groupBy('aus.author_id')
            ->orderByRaw('s.grid_id = ? desc', [$grid_id])
            ->get();
        return $authors;
    }

    /*
     * Date: 30-01-2023
     * Author: Zubair
     * Parameter: manuscript_id
     * Description:  this function will return author and their societies by manuscript_id
    */
    public static function GetSocietiesbyManuscriptID($manuscript_id)
    {
        $society = DB::connection('jms')->table("tbl_author_society as aus")
            ->select("*")
            ->join('tbl_societies as s', 's.society_id', '=', 'aus.society_id')
            ->where('aus.manuscript_id', $manuscript_id)
            ->get();
        return $society;
    }

    /*
     * Date: 30-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return awaiting manuscript list of society by societies_associations
    */

    public static function SocietyAwaitingManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_manuscript as m")->select('m.*', 'at.article_type_name', 's.name', DB::Raw('(select concat(first_name," ",last_name) from tbl_authors aa where aa.manuscript_id=m.manuscript_id and aa.is_principal=1 limit 1) as PrincipalAuthor'));

        $manuscript = $manuscript->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id');
        $manuscript = $manuscript->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id');
        $manuscript = $manuscript->join('tbl_author_society as as', 'as.manuscript_id', '=', 'm.manuscript_id');
        $manuscript = $manuscript->join('tbl_societies as s', 's.society_id', '=', 'as.society_id');
		
		$manuscript = $manuscript->whereRaw("((m.manuscript_type != 'P' and m.status = 'A' and m.submission_step = 0) OR (m.manuscript_type = 'M' and m.status = 'A' and m.submission_step > 0 and m.abstract_approved = 1))");

        $manuscript = $manuscript->where('m.lock_qa_opt', '!=', 2);
        $manuscript = $manuscript->where('m.publish', '!=', 2);
        $manuscript = $manuscript->whereNotIn('m.manuscript_id', DB::connection('jms')->table('tbl_institute_member_approval as ima')->select('ima.manuscript_id'));

        $socities = explode(",", $param['societies_associations']);
        $manuscript = $manuscript->whereIn('s.grid_id', $socities);

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

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

        return $manuscript;
    }

    /*
     * Date: 30-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return total awaiting manuscript of society
    */

    public static function TotalSocietyAwaitingManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_manuscript as m")
            ->selectRaw('count(distinct(m.`manuscript_id`)) AS total');

        $manuscript = $manuscript->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id');
        $manuscript = $manuscript->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id');
        $manuscript = $manuscript->join('tbl_author_society as as', 'as.manuscript_id', '=', 'm.manuscript_id');
        $manuscript = $manuscript->join('tbl_societies as s', 's.society_id', '=', 'as.society_id');
		
		$manuscript = $manuscript->whereRaw("((m.manuscript_type != 'P' and m.status = 'A' and m.submission_step = 0) OR (m.manuscript_type = 'M' and m.status = 'A' and m.submission_step > 0 and m.abstract_approved = 1))");

        $manuscript = $manuscript->where('m.lock_qa_opt', '!=', 2);
        $manuscript = $manuscript->where('m.publish', '!=', 2);
        $manuscript = $manuscript->whereNotIn('m.manuscript_id', DB::connection('jms')->table('tbl_institute_member_approval as ima')->select('ima.manuscript_id'));

        $socities = explode(",", $param['societies_associations']);
        $manuscript = $manuscript->whereIn('s.grid_id', $socities);

        $manuscript = $manuscript->first();

        return $manuscript;
    }

    /*
     * Date: 30-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return approved manuscript list of society by user_id
    */
    public static function SocietyApprovedManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_institute_member_approval as ima")
            ->select('m.*', 's.*', 'at.article_type_name', DB::Raw('(select concat(first_name," ",last_name,"_",email_address,"_",institution) from tbl_authors aa join tbl_author_affiliation aaa on aaa.author_id=aa.author_id where aa.manuscript_id=ima.manuscript_id and aa.is_principal=1) as PrincipalAuthor'))

            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'ima.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_societies as s', 's.grid_id', '=', 'ima.society_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '!=', 2)
            ->where('m.publish', '!=', 2)
            ->where('ima.eurekaselect_user_id', '=', $param['user_id'])
            ->where('ima.manuscript_status', '=', 1);

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

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

    /*
     * Date: 30-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return total approved manuscript of society
    */
    public static function TotalSocietyApprovedManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_institute_member_approval as ima")
            ->selectRaw('count(distinct(m.`manuscript_id`)) AS total')

            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'ima.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_societies as s', 's.grid_id', '=', 'ima.society_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '!=', 2)
            ->where('m.publish', '!=', 2)
            ->where('ima.eurekaselect_user_id', '=', $param['user_id'])
            ->where('ima.manuscript_status', '=', 1);

        $manuscript = $manuscript->first();

        return $manuscript;
    }

    /*
     * Date: 31-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return rejected manuscript list of society by user_id
    */
    public static function SocietyRejectedManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_institute_member_approval as ima")
            ->select('m.*', 's.*', 'at.article_type_name', DB::Raw('(select concat(first_name," ",last_name,"_",email_address,"_",institution) from tbl_authors aa join tbl_author_affiliation aaa on aaa.author_id=aa.author_id where aa.manuscript_id=ima.manuscript_id and aa.is_principal=1) as PrincipalAuthor'))

            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'ima.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_societies as s', 's.grid_id', '=', 'ima.society_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '!=', 2)
            ->where('m.publish', '!=', 2)
            ->where('ima.eurekaselect_user_id', '=', $param['user_id'])
            ->where('ima.manuscript_status', '=', 0);

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

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

    /*
     * Date: 31-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return total rejected manuscript society
    */
    public static function TotalSocietyRejectedManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_institute_member_approval as ima")
            ->selectRaw('count(distinct(m.`manuscript_id`)) AS total')

            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'ima.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_societies as s', 's.grid_id', '=', 'ima.society_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '!=', 2)
            ->where('m.publish', '!=', 2)
            ->where('ima.eurekaselect_user_id', '=', $param['user_id'])
            ->where('ima.manuscript_status', '=', 0);

        $manuscript = $manuscript->first();

        return $manuscript;
    }

    /*
     * Date: 31-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return pending invoice of manuscript of society by user_id
    */
    public static function GetSocietyManuscriptPendingInvoice($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_invoice as i")
            ->selectRaw('m.reference_number,m.date_submitted,m.title,s.name,at.article_type_name,i.total_amount,i.invoice_id,i.invoice_number,i.invoice_date,i.transaction_status,(select concat(first_name," ",last_name,"_",email_address,"_",institution) from tbl_authors aa join tbl_author_affiliation aaa on aaa.author_id=aa.author_id where aa.manuscript_id=m.manuscript_id and aa.is_principal=1) as PrincipalAuthor
            ')
            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'i.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_author_society as as', 'as.manuscript_id', '=', 'm.manuscript_id')
            ->join('tbl_societies as s', 's.society_id', '=', 'as.society_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '=', 2)
            ->where('m.publish', '!=', 2);

        $socities = explode(",", $param['societies_associations']);
        $manuscript = $manuscript->whereIn('s.grid_id', $socities);
        $manuscript = $manuscript->whereRaw('i.transaction_status not in ("Complete","Cancel")');

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

        if (!empty($param['pagesize'])) {
            $pagesize = $param['pagesize'];
            $manuscript = $manuscript->paginate($pagesize);
        } elseif (!empty($param['count'])) {
            $manuscript = $manuscript->count();
        } else {
            $manuscript = $manuscript->get();
        }
        return $manuscript;
    }

    /*
     * Date: 31-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return total pending invoice of manuscript of society
    */
    public static function TotalSocietyManuscriptPendingInvoice($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_invoice as i")
            ->selectRaw('count(distinct(m.`manuscript_id`)) AS total')

            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'i.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_author_society as as', 'as.manuscript_id', '=', 'm.manuscript_id')
            ->join('tbl_societies as s', 's.society_id', '=', 'as.society_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '=', 2)
            ->where('m.publish', '!=', 2);

        $socities = explode(",", $param['societies_associations']);
        $manuscript = $manuscript->whereIn('s.grid_id', $socities);
        $manuscript = $manuscript->whereRaw('i.transaction_status not in ("Complete","Cancel")');

        $manuscript = $manuscript->first();

        return $manuscript;
    }

    /*
     * Date: 31-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return paid invoice of manuscript of society by user_id
    */
    public static function GetSocietyManuscriptPaidInvoice($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_invoice as i")
            ->selectRaw('m.reference_number,m.date_submitted,m.title,s.name,at.article_type_name,i.total_amount,i.invoice_id,i.invoice_number,i.invoice_date,i.transaction_status,(select concat(first_name," ",last_name,"_",email_address,"_",institution) from tbl_authors aa join tbl_author_affiliation aaa on aaa.author_id=aa.author_id where aa.manuscript_id=m.manuscript_id and aa.is_principal=1) as PrincipalAuthor
            ')
            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'i.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_author_society as as', 'as.manuscript_id', '=', 'm.manuscript_id')
            ->join('tbl_societies as s', 's.society_id', '=', 'as.society_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '=', 2)
            ->where('m.publish', '!=', 2);

        $socities = explode(",", $param['societies_associations']);
        $manuscript = $manuscript->whereIn('s.grid_id', $socities);
        $manuscript = $manuscript->where('i.transaction_status', '=', 'Complete');

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

        if (!empty($param['pagesize'])) {
            $pagesize = $param['pagesize'];
            $manuscript = $manuscript->paginate($pagesize);
        } elseif (!empty($param['count'])) {
            $manuscript = $manuscript->count();
        } else {
            $manuscript = $manuscript->get();
        }
        return $manuscript;
    }

    /*
     * Date: 31-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return total paid invoice of manuscript of society
    */
    public static function TotalSocietyManuscriptPaidInvoice($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_invoice as i")
            ->selectRaw('count(distinct(m.`manuscript_id`)) AS total')

            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'i.manuscript_id')
            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_author_society as as', 'as.manuscript_id', '=', 'm.manuscript_id')
            ->join('tbl_societies as s', 's.society_id', '=', 'as.society_id')

            ->where('m.manuscript_type', 'M')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->where('m.lock_qa_opt', '=', 2)
            ->where('m.publish', '!=', 2);

        $socities = explode(",", $param['societies_associations']);
        $manuscript = $manuscript->whereIn('s.grid_id', $socities);
        $manuscript = $manuscript->where('i.transaction_status', '=', 'Complete');

        $manuscript = $manuscript->first();

        return $manuscript;
    }

    /*
     * Date: 30-01-2023
     * Author: Zubair
     * Parameter: society_id
     * Description:  this function will return total publish manuscript of each society
    */

    public static function PublishedManuscriptofEachSociety($society_id)
    {
        $manuscript = DB::connection('jms')->table("tbl_societies as s")
            ->selectRaw('s.*,count(distinct m.manuscript_id) as total');

        $manuscript = $manuscript->leftjoin('tbl_author_society as as', 'as.society_id', '=', 's.society_id');

        $manuscript = $manuscript->leftjoin('tbl_manuscript as m', function ($query) {
            $query->on('m.manuscript_id', 'as.manuscript_id')
                ->where('m.manuscript_type', 'M')
                ->where('m.submission_step', 0)
                ->where('m.status', 'A')
                ->where('m.publish', '=', 2);
        });

        $manuscript = $manuscript->whereIn('s.grid_id', $society_id);
        $manuscript = $manuscript->groupBy('s.grid_id');
        $manuscript = $manuscript->get();
        return $manuscript;
    }

    /*
     * Date: 30-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return published manuscript list of society by society id
    */

    public static function PublishedManuscriptbySocietyID($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_manuscript as m")->select('m.*', 'at.article_type_name', DB::Raw('(select concat(first_name," ",last_name) from tbl_authors aa where aa.manuscript_id=m.manuscript_id and aa.is_principal=1) as PrincipalAuthor'));

        $manuscript = $manuscript->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id');
		$manuscript = $manuscript->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id');
        $manuscript = $manuscript->join('tbl_author_society as as', 'as.manuscript_id', '=', 'm.manuscript_id');

        $manuscript = $manuscript->where('m.manuscript_type', 'M');
        $manuscript = $manuscript->where('m.submission_step', 0);
        $manuscript = $manuscript->where('m.status', 'A');
        $manuscript = $manuscript->where('m.publish', '=', 2);

        if (!empty($param['society_id'])) {
            $society_id = $param['society_id'];
            $manuscript = $manuscript->where('as.society_id', $society_id);
        }

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');


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

    /*
     * Date: 30-01-2023
     * Author: Zubair
     * Parameter: society_id
     * Description:  this function will return total publish manuscript of society
    */

    public static function TotalPublishedManuscriptofSociety($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_societies as s")
            ->selectRaw('count(distinct m.manuscript_id) as total');

        $manuscript = $manuscript->leftjoin('tbl_author_society as as', 'as.society_id', '=', 's.society_id');

        $manuscript = $manuscript->leftjoin('tbl_manuscript as m', function ($query) {
            $query->on('m.manuscript_id', 'as.manuscript_id')
                ->where('m.manuscript_type', 'M')
                ->where('m.submission_step', 0)
                ->where('m.status', 'A')
                ->where('m.publish', '=', 2);
        });

        $societies_association = explode(",", $param['societies_associations']);
        $manuscript = $manuscript->whereIn('s.grid_id', $societies_association);

        $manuscript = $manuscript->first();

        return $manuscript;
    }

    /*
     * Date: 31-01-2023
     * Author: Zubair
     * Parameter: societies_associations
     * Description:  this function will return all manuscript reference number of society by society_association
    */

    public static function ManuscriptListofSociety($societies_associations)
    {
        $manuscript = DB::connection('jms')->table("tbl_author_society as ima")
            ->selectRaw('m.reference_number')
            ->join('tbl_societies as s', 's.society_id', '=', 'ima.society_id')
            ->join('tbl_manuscript as m', 'm.manuscript_id', '=', 'ima.manuscript_id')
            ->join('tbl_authors as a', 'a.author_id', '=', 'ima.author_id');

        $manuscript = $manuscript->where('m.manuscript_type', 'M');
        $manuscript = $manuscript->where('m.submission_step', 0);
        $manuscript = $manuscript->where('m.status', 'A');
        $manuscript = $manuscript->where('m.publish', '=', 2);
        $societies_association = explode(",", $societies_associations);
        $manuscript = $manuscript->whereIn('s.grid_id', $societies_association);

        $manuscript = $manuscript->groupBy('m.manuscript_id');

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

    /*
     * Date: 31-01-2023
     * Author: Zubair
     * Parameter: ref_no
     * Description: This function will return article by jms ref number
    */
    public static function GetArticlebyJMSReferenceNumber($reference_number_list)
    {
        $conditions = "(((from_date Is Not Null and from_date  <= CURDATE()) and (to_date Is Not Null and to_date >= CURDATE()) and perpetual= 0) or (from_date is null and to_date is null and perpetual= 1))";

        $result = DB::table('article as a')
            ->selectRaw('a.*,a.article_id as article_id,v.volume_name as volume,i.title as issue,m.month,y.year,e.from_date,e.to_date,e.perpetual, j.subtitle, j.title journal_title, at.type_name article_type_name')
            ->join('journal_access as e', 'a.article_id', '=', 'e.content_type_id')
            ->join('journal as j', 'a.journal_id', '=', 'j.journal_id')
            ->join('volume as v', 'a.volume_id', '=', 'v.volume_id')
            ->join('year as y', 'v.year_id', '=', 'y.id')
            ->leftJoin('issue as i', 'a.issue_id', '=', 'i.issue_id')
            ->leftJoin('month as m', 'i.month_id', '=', 'm.id')
            ->leftJoin('article_type as at', 'at.art_type_id', '=', 'a.art_type')
            ->whereIn('a.jms_ref_no', $reference_number_list)
            ->whereRaw("e.content_type = 'a' and content_type_access_id = 2 and $conditions")
            ->orderByRaw('cast(v.volume_name+0 as signed) desc')
            ->orderByRaw('cast(i.title as signed) desc')->get();
        return $result;
    }

    /*
     * Date: 01-02-2023
     * Author: Zubair
     * Parameter: grid_id
     * Description: This function will return vip society by grid_id
    */
    public static function GetVipSocietybyGridID($grid_id)
    {
        $society = DB::connection('jms')->table("tbl_societies as s")
            ->select("*")
            ->join('tbl_vip_society as vs', 'vs.society_id', '=', 's.society_id')
            ->where('s.grid_id', $grid_id)
            ->first();
        return $society;
    }

     /*
     * Date: 13-02-2023
     * Author: Imran Khan
     * Parameter: lettersearch,journal_search,year
     * Description:  this function will return the jounal detail which search from bypublication page
     */
    public static function SearchJMSJournalList($param="")
    {
        $j = DB::connection('jms')->table('tbl_manuscript as m');
        $j = $j->select('m.publish','m.manuscript_id','m.title as manuscript','m.abstract','m.reference_number','m.updated_on','m.date_submitted','m.proposal_closing_date','m.ht_code');
        $j = $j->addSelect(DB::raw("(SELECT GROUP_CONCAT(CONCAT(COALESCE(keyword_title))) FROM tbl_keywords k WHERE k.manuscript_id = m.manuscript_id) AS keywords"));
		$j = $j->addSelect(DB::raw(" CASE WHEN m.proposal_closing_date > 0 AND m.proposal_closing_date > unix_timestamp(NOW()) THEN m.proposal_closing_date ELSE null END AS closes_date "));

        $j=$j->join('tbl_eic_initial_scopic_log as l','l.manuscript_id','m.manuscript_id');
        $j=$j->join('tbl_hot_topic as ht','ht.ht_id','m.ht_code')->addSelect('ht.ht_status','ht.accept_date');

        $j=$j->join('tbl_users as u','m.added_by','u.user_id');
        $j=$j->join('tbl_user_profile as up', 'u.user_id', '=', 'up.user_id')->addSelect('up.suffix');
        $j=$j->addSelect(DB::raw("CONCAT(IFNULL(CONCAT(up.first_name,''),''),' ',IFNULL(CONCAT(up.last_name,''),'')) AS editor"));
        $j=$j->join('tbl_journals as j', 'j.journal_id', '=', 'm.journal_id');
        $j=$j->addSelect('j.journal_title as title', 'j.journal_code as subtitle', 'j.ISSN as issn', 'j.eISSN as eissn','j.impact_factor');
        $j=$j->leftjoin('tbl_article_type as t', 't.article_type_id', '=', 'm.article_type_id')->addSelect('t.article_type_name');
        
        $j = $j->addSelect(DB::raw("(SELECT GROUP_CONCAT(CONCAT(IFNULL(CONCAT(au.first_name,''),''),' ',IFNULL(CONCAT(au.last_name,''),'')) ORDER BY sequence) FROM tbl_authors au WHERE au.manuscript_id = m.manuscript_id) AS co_guest_editor"));


            if (isset($param['journal_search']) && $param['journal_search'] != "") {

            $j = $j->where('j.journal_code', 'like', '%' . $param['journal_search'] . '%');
            }

            if (isset($param['subject_search']) && $param['subject_search'] != "") {
            $j = $j->whereIn('j.journal_code', $param['subject_search']);
        }

        if (isset($param['journalwisecodes'])) 
        {
            $j = $j->whereIn('j.journal_code', $param['journalwisecodes']);
        }

        if (isset($param['subjectwisecodes'])) 
        {
            $j = $j->whereIn('j.journal_code', $param['subjectwisecodes']);
        }

        if (isset($param['sdgswisecodes'])) 
        {
            $j = $j->whereIn('j.journal_code', $param['sdgswisecodes']);
        }
        $j = $j->where('j.status', '=', 'A')
            ->whereRaw(' (j.journal_type = "S" OR j.journal_code in ("CIS","CSS","CMIM","CMP","TOOPHTJ","TODENTJ","TOCIEJ")) ')
            ->where('m.submission_step', '=', 0)
            ->where('m.reference_number', '!=', '')
            ->where('m.manuscript_type', '=', 'P')
            ->where('m.status', '=', 'A')
			->where('journal_code','<>','TJL')
            //->where('m.proposal_closing_date', '!=', 0)
            ->where('l.initial_scopic_status', '=', 1013);

		if(isset($param['sort']))
		{
			if($param['sort'] == 'closeDate')
			{
				if (isset($param['sub_query'])) 
				{
					$j = $j->addSelect(DB::RAW($param['sub_query']));
					$j = $j->having('articles','>',0);
				}

			$j = $j->whereRaw('(m.proposal_closing_date <> 0 AND TO_DAYS(FROM_UNIXTIME(m.proposal_closing_date)) <= TO_DAYS(NOW()))');
			}
			else
			{
                // Default Most Recent
                $j = $j->whereRaw('(m.proposal_closing_date = 0 OR TO_DAYS(FROM_UNIXTIME(m.proposal_closing_date)) >= TO_DAYS(NOW()))');
				$j = $j->where('m.publish','!=',2);
            }
        }


        if (!empty($param['subtitle'])) 
        {
            $j = $j->where(DB::raw('lower(journal_code)'), strtolower("" . $param['subtitle'] . ""));
            }

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

            if(!empty($param['journal_group']))
            {
            $j = $j->groupBy('j.journal_id');
			$j = $j->groupBy('m.ht_code');
			$j = $j->orderBy('closes_date', 'DESC');
            $j = $j->orderBy('j.journal_title', 'ASC');
        }
      
        if (isset($param['paginate']) && $param['paginate'] == 12) {
            $j = $j->paginate($param['paginate']);
        } elseif (isset($param['paginate']) && $param['paginate'] == "all") {
            $j = $j->get();
        } else {
            $j = $j->first();
        }

        return $j;
    }

     /*
     * Date: 16-02-2023
     * Author: Imran Khan
     * Parameter: lettersearch,journal_search,year
     * Description:  this function will return the jounal detail which search from bypublication page
     */
    public static function SearchHTArticleList($param="")
    {
            $journal = DB::connection('jms')->table('tbl_manuscript as m');
            $journal = $journal->select('m.manuscript_id', 'm.reference_number')
                ->join('tbl_journals as j', 'm.journal_id', '=', 'j.journal_id');

            $journal =$journal->where('j.status', '=', 'A')
            ->where('m.manuscript_type', '=', 'M')
            ->where('m.status', '=', 'A');

            if(!empty($param['htcode']))
            {
                $journal =$journal->where('m.ht_code', '=', $param['htcode']);
            }

                $journal =$journal->get();

            return $journal;
       // }
    }

    /*
     * Date: 16-02-2023
     * Author: Imran Khan
     * Parameter: ref_no
     * Description: This function will return article by jms ref number
    */
    public static function GetArticlebyJMSRefNumber($reference_number_list,$pagesize=0)
    {

        $result = DB::table('article as a')
            ->selectRaw('a.*,a.article_id as article_id,v.volume_name,i.title as issue,m.month,y.year, j.subtitle, j.title journal_title, j.IsIssue, at.type_name article_type_name')
            ->join('journal as j', 'a.journal_id', '=', 'j.journal_id')
            ->join('volume as v', 'a.volume_id', '=', 'v.volume_id')
            ->join('year as y', 'v.year_id', '=', 'y.id')
            ->leftJoin('issue as i', 'a.issue_id', '=', 'i.issue_id')
            ->leftJoin('month as m', 'i.month_id', '=', 'm.id')
            ->leftJoin('article_type as at', 'at.art_type_id', '=', 'a.art_type')
            ->whereIn('a.jms_ref_no', $reference_number_list)
            ->orderByRaw('cast(v.volume_name+0 as signed) desc')
            ->orderByRaw('cast(i.title as signed) desc');
            if($pagesize>0){
                $result = $result->paginate($pagesize);
            }else{
                $result = $result->get();
            }
        return $result;
    }
	
    /*
     * Date: 23-02-2023
     * Author: Imran Khan
     * Parameter: ref_no
     * Description: This function will return article by jms ref number
    */
    public static function GetJournalByHtCode($ht_code)
    {

        $result = DB::connection('jms')->table('tbl_journals as j')
            ->join('tbl_manuscript as m', 'j.journal_id', '=', 'm.journal_id')
            ->where('m.ht_code', '=', $ht_code)->where('m.manuscript_type', '=', 'P');
                $result = $result->first();
        return $result;
    }
	
    /*
     * Date: 28-02-2023
     * Author: Imran Khan
     * Parameter: ref_no
     * Description: This function will return article by jms HT number inprocess article
    */
    public static function InpHTArticleList($param="")
    {
            $journal = DB::connection('jms')->table('tbl_manuscript as m');
            $journal = $journal->select('j.journal_title as title', 'j.journal_code as subtitle', 'j.ISSN as issn', 'j.eISSN as eissn', 'ht.ht_status', 'm.publish', 'm.manuscript_id', 'm.title as manuscript', 'm.abstract', 'm.reference_number', 'm.updated_on', 'm.date_submitted', 't.article_type_name', 'm.proposal_closing_date', 'm.ht_code')
                ->join('tbl_hot_topic as ht', 'ht.ht_id', '=', 'm.ht_code')
                ->join('tbl_journals as j', 'j.journal_id', '=', 'm.journal_id')
                ->leftjoin('tbl_article_type as t', 't.article_type_id', '=', 'm.article_type_id');

        $journal = $journal->where('m.manuscript_type', '=', 'm')
            ->where('m.status', '=', 'A')
            ->where('m.submission_step', '=', 0)
            ->where('m.publish', '!=', 2);

            if(!empty($param['htcode']))
            {
                $journal =$journal->where('m.ht_code', '=', $param['htcode']);
            }

                $journal =$journal->get();

            return $journal;
    }
	
     /*
     * Date: 01-03-2023
     * Author: Arshad
     * Parameter: issue_id
     * Description:  this function will return the complete list of issue authors
     */

    public static function GetJMSAuthorsByContentId($content_id,$content_field)
    {
        $author = DB::connection('jms')->select("select CONCAT(ifnull(au.first_name,''),' ',IFNULL(CONCAT(au.middle_name,' '),''), au.last_name) as authors, au.* FROM tbl_authors au where  ".$content_field." = '".$content_id."' order by au.sequence");

        if ($author) {
             return $author;
        } else {
            return null;
        }
    }
	
    /*
     * Date: 24-03-2023
     * Author: Zubair
     * Parameter: journal_id
     * Description: this function will return active ebm role by journal_id
    */
    public static function GetJournalEbmRolebyJournalID($journal_id)
    {
        $ebm = DB::table("tbl_ebm as e")
            ->selectRaw("group_concat(distinct e.ebm_role_id) as ebm_role_id")
            ->join('tbl_editors as ed', 'ed.editor_id', '=', 'e.editor_id')
            ->join('journal as j', 'j.journal_id', '=', 'e.journal_id')
            ->where('j.journal_id', $journal_id)
            ->where('e.ebm_status', 'active')
            ->where('ed.status', '=', 'A')
            ->first();
        return $ebm;
    }

    /*
     * Date: 24-03-2023
     * Author: Zubair
     * Parameter: journal_id
     * Description: this function will return active ebm users by journal_id
    */
    public static function GetEbmListJournalID($journal_id)
    {
        $ebm = DB::table("tbl_ebm as e")
            ->select("*","r.name as region_name")
            ->join('tbl_editors as ed', 'ed.editor_id', '=', 'e.editor_id')
            ->leftjoin('tbl_regions as r', 'r.region_id', '=', 'e.region_id')
            ->where('e.ebm_status', 'active')
            ->where('ed.status', '=', 'A')
            ->where('e.journal_id', $journal_id)
            ->orderBy('ed.lname', 'asc')
            ->get();

        // Sort the result collection by region name when it's available
        $ebm = $ebm->sortBy('region_name');
        return $ebm;
    }

    /*
     * Date: 27-03-2023
     * Author: Zubair
     * Description: this function will return all EBM Role
    */
    public static function GetAllEbmRole()
    {
        $ebm = DB::table("tbl_ebm_role as e")
            ->select("*")
            ->where('e.priority', '!=', null)
            ->orderBy('e.priority', 'asc')
            ->get();
        return $ebm;
    }

    /*
     * Date: 28-03-2023
     * Author: Zubair
     * Parameter: journal_id
     * Description: this function will return journal data by journal_id
    */
    public static function GetJournalData($journal_id)
    {
        $ebm = DB::table("journal")
            ->select("*")
            ->where('journal_id', $journal_id)
            ->first();
        return $ebm;
    }

    /*
     * Date: 05-05-2023
     * Author: Zubair
     * Parameter: jms_jcode
     * Description: this function will return JMS journal data by jms_jcode
    */
    public static function GetJMSJournalData($jms_jcode)
    {
        $ebm = DB::connection('jms')->table("tbl_journals")
            ->select("*")
            ->where('journal_code', $jms_jcode)
            ->first();
        return $ebm;
    }

    /*
     * Date: 31-07-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return archived manuscript list
    */
    public static function ArchivedManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_manuscript as m")
            ->select('m.*', 'i.*', 'at.article_type_name', DB::Raw('(select concat(first_name," ",last_name,"_",email_address,"_",institution) from tbl_authors aa join tbl_author_affiliation aaa on aaa.author_id=aa.author_id where aa.manuscript_id=m.manuscript_id and aa.is_principal=1 group by aa.author_id) as PrincipalAuthor'))

            ->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id')
            ->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id')
            ->join('tbl_author_affiliation as af', 'af.author_id', '=', 'a.author_id')
            ->join('tbl_institutes as i', 'i.institution_id', '=', 'af.institution_id')

            ->where('m.manuscript_type', 'M')
            ->whereRaw('(m.status = "0" or m.status = "R" )');

        $manuscript = $manuscript->where('m.submission_step', 0)
            ->where('m.publish', '!=', 2);

        $institution = explode(",", $param['institution_associations']);
        $manuscript = $manuscript->whereIn('af.institution_id', $institution);

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

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

    /*
     * Date: 31-07-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return archived manuscript list
    */
    public static function SocietyArchivedManuscript($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_manuscript as m")->select('m.*', 'at.article_type_name', 's.name', DB::Raw('(select concat(first_name," ",last_name) from tbl_authors aa where aa.manuscript_id=m.manuscript_id and aa.is_principal=1) as PrincipalAuthor'));

        $manuscript = $manuscript->join('tbl_article_type as at', 'at.article_type_id', '=', 'm.article_type_id');
        $manuscript = $manuscript->join('tbl_authors as a', 'a.manuscript_id', '=', 'm.manuscript_id');
        $manuscript = $manuscript->join('tbl_author_society as as', 'as.manuscript_id', '=', 'm.manuscript_id');
        $manuscript = $manuscript->join('tbl_societies as s', 's.society_id', '=', 'as.society_id');

        $manuscript = $manuscript->where('m.manuscript_type', 'M');
        $manuscript = $manuscript->whereRaw('(m.status = "0" or m.status = "R" )');
        $manuscript = $manuscript->where('m.submission_step', 0);
        $manuscript = $manuscript->where('m.publish', '!=', 2);

        $socities = explode(",", $param['societies_associations']);
        $manuscript = $manuscript->whereIn('s.grid_id', $socities);

        if (!empty($param['reference_number'])) {
            $reference_number = $param['reference_number'];
            $manuscript = $manuscript->where('m.reference_number', $reference_number);
        }

        $manuscript = $manuscript->groupBy('m.manuscript_id');
        $manuscript = $manuscript->orderBy('m.date_submitted', 'desc');

        if (!empty($param['pagesize'])) {
            $pagesize = $param['pagesize'];
            $manuscript = $manuscript->paginate($pagesize);
        } elseif (!empty($param['count'])) {
            $manuscript = $manuscript->count();
        } else {
            $manuscript = $manuscript->get();
        }

        return $manuscript;
    }

    /*
     * Date: 09-10-2023
     * Author: Zubair
     * Parameter: manuscript_id
     * Description: this function will return sub_topics by manuscript_id
    */
    public static function GetSubTopicsbyManuscriptId($manuscript_id)
    {
        $manuscript = DB::connection('jms')->table("tbl_proposal_sub_topic as st")
            ->select("st.sub_topics")
            ->where("st.manuscript_id", $manuscript_id)
            ->get();
        return $manuscript;
    }

    public static function GetJMSOpenAccessJournal($journal_id)
    {
        $journal = DB::connection('jms')->table("tbl_journals")
            ->select("*")
            ->where('journal_id', $journal_id)
            ->where('journal_type', 'O')
            ->first();
        return $journal;
    }
}
