<?php

namespace App\Models;

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

class Manuscript extends Model
{
    /*
     * Date: 11-01-2023
     * Author: Zubair
     * Parameter: institute_id
     * Description:  this function will return total publish manuscript of each institute
    */

    public static function GetManuscriptbyInstituteID($institute_id)
    {
        $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', $institute_id);
        $manuscript = $manuscript->groupBy('i.institution_id');
        $manuscript = $manuscript->paginate(10);
        return $manuscript;
    }

    /*
     * Date: 12-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return invoice of manuscript
    */
    public static function GetManuscriptInvoice($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);

        if (!empty($param['pending'])) {
            $manuscript = $manuscript->whereRaw('i.transaction_status not in ("Complete","Cancel")');
        }
        if (!empty($param['paid'])) {
            $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->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: 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")
            ->select("*")
            ->where('m.manuscript_id', $manuscript_id)
            ->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;
    }

    /*
     * Date: 16-01-2023
     * Author: Zubair
     * Parameter: param
     * Description:  this function will return publish manuscript list of institute by institute id
    */

    public static function PublishManuscriptbyInstitutionID($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: 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) 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')
            ->where('m.status', 'A')
            ->where('m.submission_step', 0)
            ->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 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->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 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->orderBy('m.date_submitted', 'desc');

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



    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: society_id
     * Description:  this function will return total publish manuscript of each society
    */

    public static function GetManuscriptbySocietyID($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.society_id');
        $manuscript = $manuscript->get();
        return $manuscript;
    }

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

    public static function PublishManuscriptListbySocietyID($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: 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) 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->where('m.status', 'A');
        $manuscript = $manuscript->where('m.submission_step', 0);
        $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 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->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 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->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 invoice of manuscript of society by user_id
    */
    public static function GetSocietyManuscriptInvoice($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);

        if (!empty($param['pending'])) {
            $manuscript = $manuscript->whereRaw('i.transaction_status not in ("Complete","Cancel")');
        }
        if (!empty($param['paid'])) {
            $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->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 publish manuscript list of society by society_id
    */

    public static function PublishManuscriptbySocietyID($param)
    {
        $manuscript = DB::connection('jms')->table("tbl_author_society as ima")
            ->selectRaw('m.manuscript_id,m.reference_number,m.date_submitted,m.title,at.article_type_name,(select concat(first_name," ",last_name) from tbl_authors aa where aa.manuscript_id=m.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.society_id', '=', 'ima.society_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('s.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: 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($lettersearch="",$journal_search="",$year="",$param="")
    {
            $journal = DB::connection('jms')->table('tbl_eic_initial_scopic_log as l');
            $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', 'up.suffix', DB::raw("CONCAT(IFNULL(CONCAT(up.first_name,''),''),' ',IFNULL(CONCAT(up.last_name,''),'')) AS editor"), DB::raw("(SELECT GROUP_CONCAT(CONCAT(COALESCE(keyword_title))) FROM tbl_keywords k WHERE k.manuscript_id = m.manuscript_id) AS keywords"),  DB::Raw('(SELECT COUNT(*)
                FROM tbl_vip_submissions vip WHERE vip.manuscript_id = m.manuscript_id) AS vip'))
                ->join('tbl_manuscript as m', 'l.manuscript_id', '=', 'm.manuscript_id')
                ->join('tbl_hot_topic as ht', 'ht.ht_id', '=', 'm.ht_code')
                ->join('tbl_users as u', 'm.added_by', '=', 'u.user_id')
                ->join('tbl_user_profile as up', 'u.user_id', '=', 'up.user_id')
                ->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');

            if ($lettersearch != "") {

                $journal = $journal->where('j.journal_title', 'like', $lettersearch . '%');

            }

            if ($journal_search != "") {

                $journal = $journal->where('j.journal_title', 'like', '%' . $journal_search . '%')
                    ->orwhere('j.journal_code', 'like', '%' . $journal_search . '%')
                    ->orwhere('j.ISSN', 'like', '%' . $journal_search . '%')
                    ->orwhere('j.eISSN', 'like', '%' . $journal_search . '%');

            }

            $journal =$journal->where('j.status', '=', 'A')
            ->where('j.journal_type', '=', 'S')
            ->where('m.submission_step', '=', 0)
            ->where('m.reference_number', '!=', '')
            ->where('m.manuscript_type', '=', 'P')
            ->where('m.status', '=', 'A')
            ->where('l.initial_scopic_status', '=', 1013);

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

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

            if(!empty($param['journal_group']))
            {
                $journal =$journal->groupBy('j.journal_id')
                          ->orderBy('j.journal_title', 'ASC');
            }
            
            if(!empty($param['paginate']))
            {
                $journal =$journal->paginate(12);
            } 
            else
            {
                $journal =$journal->first();
            }

            return $journal;
    }

     /*
     * 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;
    }
	
	
}
