<?php

namespace App\Models;

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

class Organization extends Model
{

    /*
     * Author   : Zubair
     * Parameter: param
     * Date     : 04-10-2023
     * Description: This function return lists of user access organization
     */
    public static function GetOrganizationList($param)
    {
        $organization = DB::table('user_access_organizations as o')
            ->select('o.*', 'c.printable_name');
        $organization = $organization->leftjoin('country as c', 'c.country_id', '=', 'o.country_id');

        if (!empty($param['search'])) {
            $search  = $param['search'];
            $organization = $organization->where('o.organization_name', 'like', '%' . $search . '%');
        }

        $organization = $organization->groupby('o.access_organization_id');
        $organization = $organization->orderBy('o.organization_name', 'DESC');

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

        return $organization;
    }

    /*
     * Author   : Zubair
     * Date     : 04-10-2023
     * Description: This function return lists of parent organization
     */
    public static function GetParentOrganizationList()
    {
        $organization = DB::table('user_access_organizations as o')
            ->select('o.*')
            ->where('o.parent_flag', 1)
            ->groupby('o.access_organization_id')
            ->orderBy('o.organization_name', 'DESC')
            ->get();

        return $organization;
    }

    /*
     * Author   : Zubair
     * Date     : 16-01-2024
     * parameter: param
     * Description: This function return lists of users whose parent organization_id and year of volume match
     */
    public static function GetUserListofOrganization($param)
    {
        $query = DB::table('user_access_organizations as uo')
            ->select('u.name', 'uo.organization_name', 'c.printable_name')
            ->selectSub(function ($query) use ($param){
                $query->selectRaw('COUNT(*)')
                    ->from('user_access_info as i')
                    ->join('user_access_journal_volume_corporate as uc', 'i.user_access_id', '=', 'uc.user_access_id')
                    ->join('volume as v', 'v.volume_id', '=', 'uc.volume_id')
                    ->join('year as yy', 'yy.id', '=', 'v.year_id')
                    ->whereColumn('u.id', 'uc.user_id')
                    ->where('yy.id', '=', $param['year_id']);
            }, 'total')
            ->join('users as u', 'u.access_organization_id', '=', 'uo.access_organization_id')
            ->join('user_access_info as ui', 'ui.user_id', '=', 'u.id')
            ->join('country as c', 'c.iso', '=', 'u.country')
            ->where('uo.parent_id', '=', $param['access_organization_id'])
            ->where('ui.access_type_id', '=', 333)
            ->groupBy('u.id')
            ->orderBy('u.name', 'asc')
            ->get();

        return $query;
    }
}
