<?php
namespace App\Http\Controllers;
use Yajra\Datatables\Datatables as Datatables;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Facades\Excel;
use Validator;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Input;
use App\Helper\MailHelper as MailHelper;
use App\Helper\RegisterInvitationHelper as RegisterInvitationHelper;
use Notification;
use Auth;
use DatePeriod;
use DateTime;
use DateInterval;
use App\Notifications\OffersNotification;
use App\Models\User;
use App\Models\Settings\Organisation;
use App\Models\Settings\Department;
use App\Models\Settings\Section;
use App\Models\Settings\MaterialType;
use App\Models\Settings\MaterialSubType;
use App\Models\Settings\Topic;
use App\Models\Settings\ContentType;
use App\Models\Settings\MimeType;
use App\Models\Settings\Roles;
use App\Models\Settings\Permissions;
use App\Models\Settings\RolePermission;
use App\Models\Settings\UserRoles;
use App\Models\Content\Content;
use App\Models\Content\ContentDocument;
use App\Models\Content\ContentAssignment;
use App\Models\Content\RejectClarify;
use App\Models\Content\Marks;
use App\Models\Training\Schedule;
use App\Models\Training\ScheduleRoles;
use App\Models\Training\Participant;
use App\Models\Training\Certificate;
use App\Models\Training\Attendance;
use App\Models\Training\ScheduleTopics;
use Request as req;

class TrainingController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');
    }
    public function get_participants($id)
    {
        $id_arr = explode("~", $id);
        $organisation_id = $id_arr[0];
        $department_id = $id_arr[1];
        $section_id = $id_arr[2];
        $schedule_id = $id_arr[3];
        $user_id_arr = [];
        $role_id_arr = [];
        $schedule_roles = ScheduleRoles::where('schedule_id',$schedule_id)->get();
        foreach ($schedule_roles as $schedule_role) 
        {
           $role_id_arr[] = $schedule_role->role_id;
        }
        $participants = Participant::where('schedule_id',$schedule_id)->get();
        foreach ($participants as $participant) 
        {
           $user_id_arr[] = $participant->user_id;
        }
        //echo "organisation_id=$organisation_id...department_id=$department_id..section_id=$section_id...schedule_id=$schedule_id...<br>";
        //dd($user_id_arr);
        if($organisation_id)
        {
            $users = User::where('organisation_id',$organisation_id)->whereNotIn('id',$user_id_arr)->with('userrole')->whereHas('userrole',function($usr) use($role_id_arr) { $usr->whereIn('role_id',$role_id_arr); })->get();
        }
        if($organisation_id && $department_id)
        {
            $users = User::where('organisation_id',$organisation_id)->where('department_id',$department_id)->whereNotIn('id',$user_id_arr)->with('userrole')->whereHas('userrole',function($usr) use($role_id_arr) { $usr->whereIn('role_id',$role_id_arr); })->get();
        }
        if($organisation_id && $department_id && $section_id)
        {
            $users = User::where('organisation_id',$organisation_id)->where('department_id',$department_id)->where('section_id',$section_id)->whereNotIn('id',$user_id_arr)->with('userrole')->whereHas('userrole',function($usr) use($role_id_arr) {$usr->whereIn('role_id',$role_id_arr); })->get();
        }
        //dd($users);
        return $users->pluck('name', 'id');
    }
    /////training schedule /////
    public function schedule()
    {
        $organisations = Organisation::all();
        $roles = Roles::all();
        return view('Training/schedule', compact('organisations','roles'));
    }
    
    public function schedule_post(Request $request)
    {
        
        $insert_id="";
        $organisation_id = $request->organisation_id;
        $department_id = $request->department_id;
        $section_id = $request->section_id;
        $material_type_id = $request->material_type_id;
        $role_id =$request->role_id;
        $training_title = $request->training_title;
        $venue = $request->venue;
        $period_from = $request->period_from;
        $period_to = $request->period_to;
        $min_seat_training = $request->min_seat_training;
        $max_seat_training = $request->max_seat_training;
        $registration_status = $request->registration_status;
        if(!$registration_status)
        {
            $registration_status = 2;
        }
        $question_answer_status = $request->question_answer_status;
        if(!$question_answer_status)
        {
            $question_answer_status = 2;
        }
        $review_status = $request->review_status;
        if(!$review_status)
        {
            $review_status = 2;
        }
        $certificate_status = $request->certificate_status;
        if(!$certificate_status)
        {
            $certificate_status = 2;
        }
        $training_fees = $request->training_fees;
        $training_allowance = $request->training_allowance;
        $remarks = $request->remarks;   
        $roles = $request->role_id;     
        $id = Auth::user()->id;
        $schedule_count = Schedule::where('organisation_id',$organisation_id)->where('training_title',$training_title)->where('material_type_id',$material_type_id)->count();
        if($schedule_count==0)
        {
            $insert_id = Schedule::create([
                'organisation_id' => $organisation_id,
                'department_id' => $department_id,
                'section_id' => $section_id,
                'material_type_id' => $material_type_id,
                'training_title' => $training_title,
                'venue' => $venue,
                'period_from' => $period_from,
                'period_to' => $period_to,
                'registration_status' => $registration_status,
                'min_seat_training' => $min_seat_training,
                'max_seat_training' => $max_seat_training,
                'question_answer_status' => $question_answer_status,
                'review_status' => $review_status,
                'training_certificates' => $certificate_status,
                'training_fees' => $training_fees,
                'training_allowance' => $training_allowance,
                'remarks' => $remarks,
                'created_by' => $id,
            ]);
            $schedule_id = $insert_id->id;
            if($roles!=null)
            {
                foreach($roles as $role)
                {
                    $schedule_role_count = ScheduleRoles::where('schedule_id',$schedule_id)->where('role_id',$role)->count();
                    if($schedule_role_count==0)
                    {
                        $insert_schedule_role_id = ScheduleRoles::create([
                            'schedule_id' => $schedule_id,
                            'role_id' => $role,
                        ]);
                    }
                }
            }
            $schedule_roles = ScheduleRoles::where('schedule_id',$schedule_id)->with('roles','schedule')->get();
            foreach($schedule_roles as $schedule_role)
            {
                $role_id = $schedule_role->role_id;
                $userSchema = UserRoles::where('role_id',$role_id)->with('user')->get();
                foreach($userSchema as $users)
                {
                    $username = User::where('id',$users->user_id)->first();
                    //
                    $training_title = $schedule_role->schedule->training_title;
                    $venue = $schedule_role->schedule->venue;
                    $period_from = $schedule_role->schedule->period_from;
                    $period_to = $schedule_role->schedule->period_to;
                    if($period_from)
                    {
                        $period_from = date("d-M-Y", strtotime($period_from));
                    }
                    if($period_to)
                    {
                        $period_to = date("d-M-Y", strtotime($period_to));
                    }
                    $title = $training_title." at ".$venue;
                    $training_period = $period_from." to ".$period_to;
                    $body = 'You received a training schedule for '.$title.' from '.$training_period;
                    $offerData = [
                                'name' => "das",
                                'body' => $body,
                                'thanks' => 'Thank you',
                                'offerText' => 'View Schedule',
                                'offerUrl' => url('/'),
                                'offer_id' => 007
                            ];
                    //
                    Notification::send($username, new OffersNotification($offerData));
                }          
            }
        }
        if($insert_id)
            notify()->success('Training Schedule has been added');
        else
            smilify('Error', '');

        //return redirect()->back();
    }

    public function get_schedule_details(Request $request)
    {
        $columns = array(
            0=> 'id',
            1=>'org_name',
            2=>'roles',
            3=>'title',
            4=>'training_period',
            5=> 'action',
        );
        $totalData = Schedule::count();
        $totalFiltered = $totalData; 
        $limit = $request->input('length');
        $start = $request->input('start');
        $order = $columns[$request->input('order.0.column')];
        $dir = $request->input('order.0.dir');
        if(empty($request->input('search.value')))
        {       
            $posts = Schedule::with('organisation','department','section','material_type')
                         ->offset($start)
                         ->limit($limit)
                         ->orderBy('id','desc')
                         ->get();
        }
        else 
        {
            $search = $request->input('search.value'); 
            $posts =  Schedule::with('organisation','department','section','material_type')
                            ->where('training_title', 'LIKE',"%{$search}%")
                            ->orWhere('venue', 'LIKE',"%{$search}%")
                            ->offset($start)
                            ->limit($limit)
                            ->orderBy($order,$dir)
                            ->get();
            $totalFiltered = Schedule::with('organisation','department','section','material_type')
                            ->where('training_title', 'LIKE',"%{$search}%")
                            ->orWhere('venue', 'LIKE',"%{$search}%")
                            ->count();
        }
        $data = array();
        if(!empty($posts))
        {
            $org_name = "";
            $title ="";
            $section_name = "";
            $department_name = "";
            $material_type_name = "";
            $organisation_name = "";
            $training_title ="";
            $venue = "";
            $roles_name = "";
            $role_name = "";
            $reg_url = "";
            $reg_required_status = "";
            $assign_topic_url= "";
            $assign_participant_url ="";
            foreach ($posts as $post)
            {   
                $schedule_id = $post->id;
                if(isset($post->organisation_id))
                    $organisation_name = $post->organisation->organisation_name;
                if(isset($post->material_type_id))
                    $material_type_name = $post->material_type->material_type_name;
                if(isset($post->department_id))
                    $department_name = $post->department->department_name;
                if(isset($post->section_id))
                    $section_name = $post->section->section_name;

                $training_title = $post->training_title;
                $venue = $post->venue;
                $period_from = $post->period_from;
                $period_to = $post->period_to;
                if($period_from)
                {
                    $period_from = date("d-M-Y", strtotime($period_from));
                }
                if($period_to)
                {
                    $period_to = date("d-M-Y", strtotime($period_to));
                }
                $org_name = $organisation_name."~".$department_name."~".$section_name."~".$material_type_name;
                $title = $training_title."~".$venue;
                $training_period = $period_from." to ".$period_to;
                $schedule_roles = ScheduleRoles::where('schedule_id',$schedule_id)->with('roles')->get();
                foreach($schedule_roles as $schedule_role)
                {
                    $role_name = $schedule_role->roles->name;
                    if($roles_name)
                    {
                        $roles_name = $roles_name.",".$role_name;
                    }
                    else
                    {
                        $roles_name = $role_name;
                    }
                }
                $reg_required_status = $post->registration_status;
                $tokenval = csrf_field();
                if($reg_required_status==1)
                {
                    $reg_url = '<form class="form-horizontal" action="send_invitation" method="post" autocomplete="off">'.$tokenval.'<input type="hidden" name="schedule_id" id="schedule_id" value="'.$schedule_id.'"><button type="submit"><span style="color:blue;"><i class="fa fa-envelope" title="Send Invitation"></i></span></button></form>';
                }
                $assign_topic_url = '<form class="form-horizontal" action="assign_topic" method="post" autocomplete="off">'.$tokenval.'<input type="hidden" name="schedule_id" id="schedule_id" value="'.$schedule_id.'"><button type="submit" class="btn btn-xs btn-warning" title="Assign Topic">Assign Topic</button></form>';
                //$assign_participant_url = '&nbsp;&nbsp;<a href="'.url('view_schedule').'/'.$post->id.'" title="Assign Participant" class="btn btn-xs btn-info">Assign Participant</a>';
                $nestedData['DT_RowIndex'] = $post->id;
                $nestedData['org_name'] = $organisation_name;
                $nestedData['roles'] = $roles_name;
                $nestedData['title'] = $training_title;
                $nestedData['training_period'] = $training_period;
                $nestedData['action'] = '<a href="'.url('show_schedule').'/'.$post->id.'" title="Update Schedule"><span style="color:blue;"><i class="fa fa-edit"></i></span></a>'.$assign_participant_url.$assign_topic_url.$reg_url;
                $data[] = $nestedData;
            }
        }
        $json_data = array(
                    "draw"            => intval($request->input('draw')),  
                    "recordsTotal"    => intval($totalData),  
                    "recordsFiltered" => intval($totalFiltered), 
                    "data"            => $data   
                    );
        echo json_encode($json_data); 
    } 

    public function show_schedule($id)
    {
        $schedules = Schedule::where('id',$id)->with('organisation','department','section','material_type','schedule_role')->first();
        if(isset($schedules))
        {
            $organisation_id = $schedules->organisation_id;
            $roles = Roles::all();
            $departments = Department::where('organisation_id',$organisation_id)->get();
            $material_types = MaterialType::where('organisation_id',$organisation_id)->get();
        }
        return view('Training/show_schedule', compact('schedules','departments','material_types','roles'));
    }
    public function show_schedule_post(Request $request)
    {
        //dd($request);
        $upd_flg = "";
        $insert_id="";
        $registration_status = "";
        $question_answer_status = "";
        $review_status = "";
        $certificate_status = "";
        $organisation_id = $request->organisation_id;
        $department_id = $request->department_id;
        $section_id = $request->section_id;
        $material_type_id = $request->material_type_id;
        $role_id =$request->role_id;
        $training_title = $request->training_title;
        $venue = $request->venue;
        $period_from = $request->period_from;
        $period_to = $request->period_to;
        $min_seat_training = $request->min_seat_training;
        $max_seat_training = $request->max_seat_training;
        $registration_status = $request->registration_status;
        if(!$registration_status)
        {
            $registration_status = 2;
        }
        $question_answer_status = $request->question_answer_status;
        if(!$question_answer_status)
        {
            $question_answer_status = 2;
        }
        //echo "question_answer_status=$question_answer_status..<br>";
        $review_status = $request->review_status;
        if(!$review_status)
        {
            $review_status = 2;
        }
        $certificate_status = $request->certificate_status;
        if(!$certificate_status)
        {
            $certificate_status = 2;
        }
        $training_fees = $request->training_fees;
        $training_allowance = $request->training_allowance;
        $remarks = $request->remarks;   
        $schedule_id = $request->schedule_id;
        $roles = $request->role_id; 
        $period_to_plus_oneday = date( "Y-m-d", strtotime( $period_to ."+1 day" ) );
        $period_training =  new DatePeriod(
                                new DateTime($period_from),
                                new DateInterval('P1D'),
                                new DateTime($period_to_plus_oneday)
                             );
        $i=0;
        $training_date = [];
        foreach ($period_training as $period_training_value) 
        {
            $training_date[$i]= $period_training_value->format('Y-m-d');
            $i++;      
        }
        //dd($training_date);    
        $id = Auth::user()->id;        
        $schedules = Schedule::where('id',$schedule_id)->first();
        if(isset($schedules))
        {
            ////if period from and period to are changed then the topic 
            $schedule_topics_count =0;
            $schedule_topics_upd = ScheduleTopics::where('schedule_id',$schedule_id)->whereNotIn('topic_schedule_date',$training_date)->select('topic_schedule_date')->get();
            foreach($schedule_topics_upd as $schedule_topics_upds)
            {
                $schedule_topic_id = $schedule_topics_upds->id;
                $topic_schedule_date = $schedule_topics_upds->topic_schedule_date;
                $schedule_topics_delete = ScheduleTopics::where('schedule_id',$schedule_id)->where('topic_schedule_date',$topic_schedule_date)->delete();
            }
            //////////////////////////////////////////////////////////////// 

            $schedules->department_id=$department_id;
            $schedules->section_id=$section_id;
            $schedules->organisation_id=$organisation_id;
            $schedules->material_type_id=$material_type_id;
            $schedules->training_title=$training_title;
            $schedules->venue=$venue;
            $schedules->period_from=$period_from;
            $schedules->period_to=$period_to;
            $schedules->registration_status=$registration_status;
            $schedules->min_seat_training=$min_seat_training;
            $schedules->max_seat_training=$max_seat_training;
            $schedules->question_answer_status=$question_answer_status;
            $schedules->review_status=$review_status;
            $schedules->training_certificates=$certificate_status;
            $schedules->training_fees=$training_fees;
            $schedules->training_allowance=$training_allowance;
            $schedules->remarks=$remarks;
            $schedules->updated_by=$id;
            $schedules->save();
            $upd_flg  =1;
            $schedule_roles_deletes = ScheduleRoles::where('schedule_id',$schedule_id)->delete();
            if($roles!=null)
            {
                foreach($roles as $role)
                {
                    $schedule_role_count = ScheduleRoles::where('schedule_id',$schedule_id)->where('role_id',$role)->count();
                    if($schedule_role_count==0)
                    {
                        $insert_schedule_role_id = ScheduleRoles::create([
                            'schedule_id' => $schedule_id,
                            'role_id' => $role,
                        ]);
                    }
                }
            }
        }
        
        if($upd_flg)
            notify()->success('Schedule has been updated');
        else
            smilify('Error', '');

        return redirect('schedule');
    } 

    public function send_invitation(Request $request)
    {
        $schedule_id = $request->schedule_id;
        $invitation_obj = new RegisterInvitationHelper();
        $created_by = Auth::user()->id;
        $result=$invitation_obj->send_invitation($schedule_id,$created_by);
        
        return redirect('schedule');
    } 

    /////assign topics////////////////
    public function assign_topic(Request $request)
    {
        $training_date = [];
        $schedule_id = $request->schedule_id;
        $schedules = Schedule::where('id',$schedule_id)->with('organisation','department','section','material_type','schedule_role','schedule_participants')->first();
        if(isset($schedules))
        {
            $material_type_id = $schedules->material_type_id;
            $period_from = $schedules->period_from;
            $period_to = $schedules->period_to;
            $period_to_plus_oneday = date( "Y-m-d", strtotime( $period_to ."+1 day" ) );
            $period_training = new DatePeriod(
                            new DateTime($period_from),
                            new DateInterval('P1D'),
                            new DateTime($period_to_plus_oneday)
                         );
            $i=0;
            foreach ($period_training as $period_training_value) 
            {
                $training_date[$i]= $period_training_value->format('d-m-Y');
                $i++;      
            }
        }
        $topics = Topic::where('material_type_id',$material_type_id)->get();
        $schedule_topics = ScheduleTopics::where('schedule_id',$schedule_id)->get();
        return view('Training/assign_topic', compact('schedules','training_date','topics','schedule_topics'));
    }
    public function assign_topic_post(Request $request)
    {
        $training_date = [];
        $insert_id="";
        $schedule_id = $request->schedule_id;
        $schedules = Schedule::where('id',$schedule_id)->first();
        if(isset($schedules))
        {
            $material_type_id = $schedules->material_type_id;
            $period_from = $schedules->period_from;
            $period_to = $schedules->period_to;
            $period_to_plus_oneday = date( "Y-m-d", strtotime( $period_to ."+1 day" ) );
            $period_training = new DatePeriod(
                            new DateTime($period_from),
                            new DateInterval('P1D'),
                            new DateTime($period_to_plus_oneday)
                         );
            $i=0;
            foreach ($period_training as $period_training_value) 
            {
                $training_date[$i]= $period_training_value->format('Y-m-d');
                $i++;      
            }
        }
        foreach($training_date as $training_date_value)
        {
            $topic_obj = $training_date_value."topic_id";
            $topic_id = $request->$topic_obj;

            foreach($topic_id as $topic_id_value)
            {
                $schedule_topic_count = ScheduleTopics::where('schedule_id',$schedule_id)->where('topic_id',$topic_id_value)->where('topic_schedule_date',$training_date_value)->count();
                if($schedule_topic_count==0)
                {
                    $insert_id = ScheduleTopics::create([
                            'schedule_id' => $schedule_id,
                            'topic_id' => $topic_id_value,
                            'topic_schedule_date' => $training_date_value,
                        ]);
                }
                else
                {
                    $schedule_topics = ScheduleTopics::where('schedule_id',$schedule_id)->get();
                    foreach($schedule_topics as $schedule_topic)
                    {
                        $schedule_topic_id = $schedule_topic->id;
                        $schedule_topics_count = ScheduleTopics::where('schedule_id',$schedule_id)->where('topic_id',$topic_id_value)->count();
                        if($schedule_topics_count==0)
                        {
                            $schedule_topic_arr = ScheduleTopics::where('id',$schedule_topic_id)->first();
                            $schedule_topic_arr->topic_schedule_date = $training_date_value;
                            $schedule_topic_arr->save();
                        }
                    }
                }
            }
        }
        
        if($insert_id)
            notify()->success('Training Schedule Topic has been added');
        else
            smilify('Error', '');

        return redirect('schedule');
    }

    /////training participants /////
    public function participants()
    {
        return view('Training/participants');
    }    
    public function get_participant_details(Request $request)
    {
        $columns = array(
            0=> 'id',
            1=>'org_name',
            2=>'roles',
            3=>'title',
            4=>'training_period',
            5=> 'action',
        );
        $totalData = Schedule::count();
        $totalFiltered = $totalData; 
        $limit = $request->input('length');
        $start = $request->input('start');
        $order = $columns[$request->input('order.0.column')];
        $dir = $request->input('order.0.dir');
        if(empty($request->input('search.value')))
        {       
            $posts = Schedule::with('organisation','department','section','material_type')
                         ->offset($start)
                         ->limit($limit)
                         ->orderBy('id','desc')
                         ->get();
        }
        else 
        {
            $search = $request->input('search.value'); 
            $posts =  Schedule::with('organisation','department','section','material_type')
                            ->where('training_title', 'LIKE',"%{$search}%")
                            ->orWhere('venue', 'LIKE',"%{$search}%")
                            ->offset($start)
                            ->limit($limit)
                            ->orderBy($order,$dir)
                            ->get();
            $totalFiltered = Schedule::with('organisation','department','section','material_type')
                            ->where('training_title', 'LIKE',"%{$search}%")
                            ->orWhere('venue', 'LIKE',"%{$search}%")
                            ->count();
        }
        $data = array();
        if(!empty($posts))
        {
            $org_name = "";
            $title ="";
            $section_name = "";
            $department_name = "";
            $material_type_name = "";
            $organisation_name = "";
            $training_title ="";
            $venue = "";
            $roles_name = "";
            $role_name = "";
            $reg_url = "";
            $reg_required_status = "";
            foreach ($posts as $post)
            {   
                $schedule_id = $post->id;
                if(isset($post->organisation_id))
                    $organisation_name = $post->organisation->organisation_name;
                if(isset($post->material_type_id))
                    $material_type_name = $post->material_type->material_type_name;
                if(isset($post->department_id))
                    $department_name = $post->department->department_name;
                if(isset($post->section_id))
                    $section_name = $post->section->section_name;

                $training_title = $post->training_title;
                $venue = $post->venue;
                $period_from = $post->period_from;
                $period_to = $post->period_to;
                if($period_from)
                {
                    $period_from = date("d-M-Y", strtotime($period_from));
                }
                if($period_to)
                {
                    $period_to = date("d-M-Y", strtotime($period_to));
                }
                $org_name = $organisation_name."~".$department_name."~".$section_name."~".$material_type_name;
                $title = $training_title."~".$venue;
                $training_period = $period_from." to ".$period_to;
                $schedule_roles = ScheduleRoles::where('schedule_id',$schedule_id)->with('roles')->get();
                foreach($schedule_roles as $schedule_role)
                {
                    $role_name = $schedule_role->roles->name;
                    if($roles_name)
                    {
                        $roles_name = $roles_name.",".$role_name;
                    }
                    else
                    {
                        $roles_name = $role_name;
                    }
                }
                $reg_required_status = $post->registration_status;
                
                $nestedData['DT_RowIndex'] = $post->id;
                $nestedData['org_name'] = $organisation_name;
                $nestedData['roles'] = $roles_name;
                $nestedData['title'] = $training_title;
                $nestedData['training_period'] = $training_period;
                $nestedData['action'] = '<a href="'.url('view_schedule').'/'.$post->id.'" title="View Details" class="btn btn-xs btn-primary">View Details</a>';
                $data[] = $nestedData;
            }
        }
        $json_data = array(
                    "draw"            => intval($request->input('draw')),  
                    "recordsTotal"    => intval($totalData),  
                    "recordsFiltered" => intval($totalFiltered), 
                    "data"            => $data   
                    );
        echo json_encode($json_data); 
    }

    public function view_schedule($id)
    {
        $organisations = Organisation::all();
        $schedules = Schedule::where('id',$id)->with('organisation','department','section','material_type','schedule_role','schedule_participants')->first();
        //dd($schedules);
        if(isset($schedules))
        {
            $organisation_id = $schedules->organisation_id;
            $department_id = $schedules->department_id;
            $section_id = $schedules->section_id;
        }
        $user_id_arr = [];
        $role_id_arr = [];
        $schedule_roles = ScheduleRoles::where('schedule_id',$id)->get();
        foreach ($schedule_roles as $schedule_role) 
        {
           $role_id_arr[] = $schedule_role->role_id;
        }
        $participants = Participant::where('schedule_id',$id)->get();
        foreach ($participants as $participant) 
        {
           $user_id_arr[] = $participant->user_id;
        }
        if($organisation_id)
        {
            $users = User::where('organisation_id',$organisation_id)->whereNotIn('id',$user_id_arr)->with('userrole')->whereHas('userrole',function($usr) use($role_id_arr) { $usr->whereIn('role_id',$role_id_arr); })->get();
        }
        if($department_id)
        {
            $users = User::where('organisation_id',$organisation_id)->where('department_id',$department_id)->whereNotIn('id',$user_id_arr)->with('userrole')->whereHas('userrole',function($usr) use($role_id_arr) { $usr->whereIn('role_id',$role_id_arr); })->get();
        }
        if($section_id)
        {
            $users = User::where('organisation_id',$organisation_id)->where('department_id',$department_id)->where('section_id',$section_id)->whereNotIn('id',$user_id_arr)->with('userrole')->whereHas('userrole',function($usr) use($role_id_arr) { $usr->whereIn('role_id',$role_id_arr); })->get();
        }
        //dd($role_id_arr);
        return view('Training/view_schedule', compact('schedules','users','organisations'));
    }

    public function participant_add_training(Request $request)
    {
        $schedule_id = $request->schedule_id_new;
        $user_id = $request->user_id;
        $invitation_obj = new RegisterInvitationHelper();
        $created_by = Auth::user()->id;
        $result=$invitation_obj->send_invitation_add($schedule_id,$user_id,$created_by);
        return redirect('participants');
    }

    ////attendance///////////////////
    public function attendance()
    {
        return view('Training/attendance');
    }    
    public function get_training_schedule_details(Request $request)
    {
        $columns = array(
            0=> 'id',
            1=>'org_name',
            2=>'roles',
            3=>'title',
            4=>'training_period',
            5=> 'action',
        );
        $totalData = Schedule::count();
        $totalFiltered = $totalData; 
        $limit = $request->input('length');
        $start = $request->input('start');
        $order = $columns[$request->input('order.0.column')];
        $dir = $request->input('order.0.dir');
        if(empty($request->input('search.value')))
        {       
            $posts = Schedule::with('organisation','department','section','material_type')
                         ->offset($start)
                         ->limit($limit)
                         ->orderBy('id','desc')
                         ->get();
        }
        else 
        {
            $search = $request->input('search.value'); 
            $posts =  Schedule::with('organisation','department','section','material_type')
                            ->where('training_title', 'LIKE',"%{$search}%")
                            ->orWhere('venue', 'LIKE',"%{$search}%")
                            ->offset($start)
                            ->limit($limit)
                            ->orderBy($order,$dir)
                            ->get();
            $totalFiltered = Schedule::with('organisation','department','section','material_type')
                            ->where('training_title', 'LIKE',"%{$search}%")
                            ->orWhere('venue', 'LIKE',"%{$search}%")
                            ->count();
        }
        $data = array();
        if(!empty($posts))
        {
            $org_name = "";
            $title ="";
            $section_name = "";
            $department_name = "";
            $material_type_name = "";
            $organisation_name = "";
            $training_title ="";
            $venue = "";
            $roles_name = "";
            $role_name = "";
            $reg_url = "";
            $reg_required_status = "";
            foreach ($posts as $post)
            {   
                $schedule_id = $post->id;
                if(isset($post->organisation_id))
                    $organisation_name = $post->organisation->organisation_name;
                if(isset($post->material_type_id))
                    $material_type_name = $post->material_type->material_type_name;
                if(isset($post->department_id))
                    $department_name = $post->department->department_name;
                if(isset($post->section_id))
                    $section_name = $post->section->section_name;

                $training_title = $post->training_title;
                $venue = $post->venue;
                $period_from = $post->period_from;
                $period_to = $post->period_to;
                if($period_from)
                {
                    $period_from = date("d-M-Y", strtotime($period_from));
                }
                if($period_to)
                {
                    $period_to = date("d-M-Y", strtotime($period_to));
                }
                $org_name = $organisation_name."~".$department_name."~".$section_name."~".$material_type_name;
                $title = $training_title."~".$venue;
                $training_period = $period_from." to ".$period_to;
                $schedule_roles = ScheduleRoles::where('schedule_id',$schedule_id)->with('roles')->get();
                foreach($schedule_roles as $schedule_role)
                {
                    $role_name = $schedule_role->roles->name;
                    if($roles_name)
                    {
                        $roles_name = $roles_name.",".$role_name;
                    }
                    else
                    {
                        $roles_name = $role_name;
                    }
                }
                $reg_required_status = $post->registration_status;
                
                $nestedData['DT_RowIndex'] = $post->id;
                $nestedData['org_name'] = $organisation_name;
                $nestedData['roles'] = $roles_name;
                $nestedData['title'] = $training_title;
                $nestedData['training_period'] = $training_period;
                $nestedData['action'] = '<a href="'.url('view_schedule_participants').'/'.$post->id.'" title="View Participant Details" class="btn btn-xs btn-primary">Participant Details</a>';
                $data[] = $nestedData;
            }
        }
        $json_data = array(
                    "draw"            => intval($request->input('draw')),  
                    "recordsTotal"    => intval($totalData),  
                    "recordsFiltered" => intval($totalFiltered), 
                    "data"            => $data   
                    );
        echo json_encode($json_data); 
    }

    public function view_schedule_participants($id)
    {
        $training_date = [];
        $attendance_status =[];
        $schedules = Schedule::where('id',$id)->with('organisation','department','section','material_type','schedule_role','schedule_participants')->first();
        if(isset($schedules))
        {
            $organisation_id = $schedules->organisation_id;
            $department_id = $schedules->department_id;
            $section_id = $schedules->section_id;
            $period_from = $schedules->period_from;
            $period_to = $schedules->period_to;
            $period_to_plus_oneday = date( "Y-m-d", strtotime( $period_to ."+1 day" ) );
            $period_training = new DatePeriod(
                            new DateTime($period_from),
                            new DateInterval('P1D'),
                            new DateTime($period_to_plus_oneday)
                         );
            $i=0;
            foreach ($period_training as $period_training_value) 
            {
                $training_date[$i]= $period_training_value->format('d-m-Y');
                $i++;      
            }
            
        }
        $user_id_arr = [];
        $role_id_arr = [];
        $participants = Participant::where('schedule_id',$id)->get();
        foreach ($participants as $participant) 
        {
           $user_id_arr[] = $participant->user_id;
           $role_id_arr[] = $participant->role_id;
        }
        if($organisation_id)
        {
            $users = User::where('organisation_id',$organisation_id)->whereNotIn('id',$user_id_arr)->with('userrole')->whereHas('userrole',function($usr) use($role_id_arr) { $usr->whereIn('role_id',$role_id_arr); })->get();
        }
        if($department_id)
        {
            $users = User::where('organisation_id',$organisation_id)->where('department_id',$department_id)->whereNotIn('id',$user_id_arr)->with('userrole')->whereHas('userrole',function($usr) use($role_id_arr) { $usr->whereIn('role_id',$role_id_arr); })->get();
        }
        if($section_id)
        {
            $users = User::where('organisation_id',$organisation_id)->where('department_id',$department_id)->where('section_id',$section_id)->whereNotIn('id',$user_id_arr)->with('userrole')->whereHas('userrole',function($usr) use($role_id_arr) { $usr->whereIn('role_id',$role_id_arr); })->get();
        }
        foreach($schedules->schedule_participants as $schedule_participant)
        {
            $user_id = $schedule_participant->user_id;
            foreach ($training_date as $training_date_val) 
            {
                $date_training_value = date("Y-m-d", strtotime($training_date_val));
                $attendance_status[$user_id][$date_training_value] = Attendance::where('schedule_id',$id)->where('user_id',$user_id)->where('attendance_date',$date_training_value)->where('attendance_status',1)->count();
            }
        }   
        //dd($attendance_status);
        return view('Training/view_schedule_participants', compact('schedules','users','training_date','attendance_status'));
    }
    public function schedule_participants_attendance_post(Request $request)
    {
        $schedule_id = $request->schedule_id;
        $training_date = [];
        $schedules = Schedule::where('id',$schedule_id)->first();
        if(isset($schedules))
        {
            $organisation_id = $schedules->organisation_id;
            $department_id = $schedules->department_id;
            $section_id = $schedules->section_id;
            $period_from = $schedules->period_from;
            $period_to = $schedules->period_to;
            $period_to_plus_oneday = date( "Y-m-d", strtotime( $period_to ."+1 day" ) );
            $period_training = new DatePeriod(
                            new DateTime($period_from),
                            new DateInterval('P1D'),
                            new DateTime($period_to_plus_oneday)
                         );
            $i=0;
            foreach ($period_training as $period_training_value) 
            {
                $training_date[$i]= $period_training_value->format('d-m-Y');
                $i++;      
            }
        }
        $attendance_delete = Attendance::where('schedule_id',$schedule_id)->delete();
        $participants = Participant::where('schedule_id',$schedule_id)->get();
        foreach ($participants as $participant) 
        {
            $user_id = $participant->user_id;
            foreach($training_date as $training_date_val)
            {
                $attendance_date = date("Y-m-d", strtotime($training_date_val));
                $attendance_val = $user_id."attendance_status".$training_date_val;
                $attendance_status = $request->$attendance_val;
                if($attendance_status==1)
                {
                    $attendance_status_value = 1;
                }
                else
                {
                    $attendance_status_value = 2;
                }
                $id = Auth::user()->id;
                $attendance_count = Attendance::where('schedule_id',$schedule_id)->where('user_id',$user_id)->where('attendance_date',$attendance_date)->count();
                if($attendance_count==0)
                {
                    $insert_id = Attendance::create([
                        'schedule_id' => $schedule_id,
                        'user_id' => $user_id,
                        'attendance_date' => $attendance_date,
                        'created_by' => $id,
                        'attendance_status' => $attendance_status_value,
                    ]);
                }
            }
        }
        if($insert_id)
            notify()->success('Attendance has been marked');
        else
            smilify('Error', '');

        return redirect()->back();
    }

    ////certificate///////////////////
    public function certificate()
    {
        return view('Training/certificate');
    } 
    public function get_training_participant_details(Request $request)
    {
        $columns = array(
            0=> 'id',
            1=>'org_name',
            2=>'roles',
            3=>'title',
            4=>'training_period',
            5=> 'action',
        );
        $totalData = Schedule::count();
        $totalFiltered = $totalData; 
        $limit = $request->input('length');
        $start = $request->input('start');
        $order = $columns[$request->input('order.0.column')];
        $dir = $request->input('order.0.dir');
        if(empty($request->input('search.value')))
        {       
            $posts = Schedule::with('organisation','department','section','material_type')
                         ->offset($start)
                         ->limit($limit)
                         ->orderBy('id','desc')
                         ->get();
        }
        else 
        {
            $search = $request->input('search.value'); 
            $posts =  Schedule::with('organisation','department','section','material_type')
                            ->where('training_title', 'LIKE',"%{$search}%")
                            ->orWhere('venue', 'LIKE',"%{$search}%")
                            ->offset($start)
                            ->limit($limit)
                            ->orderBy($order,$dir)
                            ->get();
            $totalFiltered = Schedule::with('organisation','department','section','material_type')
                            ->where('training_title', 'LIKE',"%{$search}%")
                            ->orWhere('venue', 'LIKE',"%{$search}%")
                            ->count();
        }
        $data = array();
        if(!empty($posts))
        {
            $org_name = "";
            $title ="";
            $section_name = "";
            $department_name = "";
            $material_type_name = "";
            $organisation_name = "";
            $training_title ="";
            $venue = "";
            $roles_name = "";
            $role_name = "";
            $reg_url = "";
            $reg_required_status = "";
            foreach ($posts as $post)
            {   
                $schedule_id = $post->id;
                if(isset($post->organisation_id))
                    $organisation_name = $post->organisation->organisation_name;
                if(isset($post->material_type_id))
                    $material_type_name = $post->material_type->material_type_name;
                if(isset($post->department_id))
                    $department_name = $post->department->department_name;
                if(isset($post->section_id))
                    $section_name = $post->section->section_name;

                $training_title = $post->training_title;
                $venue = $post->venue;
                $period_from = $post->period_from;
                $period_to = $post->period_to;
                if($period_from)
                {
                    $period_from = date("d-M-Y", strtotime($period_from));
                }
                if($period_to)
                {
                    $period_to = date("d-M-Y", strtotime($period_to));
                }
                $org_name = $organisation_name."~".$department_name."~".$section_name."~".$material_type_name;
                $title = $training_title."~".$venue;
                $training_period = $period_from." to ".$period_to;
                $schedule_roles = ScheduleRoles::where('schedule_id',$schedule_id)->with('roles')->get();
                foreach($schedule_roles as $schedule_role)
                {
                    $role_name = $schedule_role->roles->name;
                    if($roles_name)
                    {
                        $roles_name = $roles_name.",".$role_name;
                    }
                    else
                    {
                        $roles_name = $role_name;
                    }
                }
                $reg_required_status = $post->registration_status;
                
                $nestedData['DT_RowIndex'] = $post->id;
                $nestedData['org_name'] = $organisation_name;
                $nestedData['roles'] = $roles_name;
                $nestedData['title'] = $training_title;
                $nestedData['training_period'] = $training_period;
                $nestedData['action'] = '<a href="'.url('view_participants_details').'/'.$post->id.'" title="View Participant Details" class="btn btn-xs btn-primary">Participant Details</a>';
                $data[] = $nestedData;
            }
        }
        $json_data = array(
                    "draw"            => intval($request->input('draw')),  
                    "recordsTotal"    => intval($totalData),  
                    "recordsFiltered" => intval($totalFiltered), 
                    "data"            => $data   
                    );
        echo json_encode($json_data); 
    }
    public function view_participants_details($id)
    {
        $training_date = [];
        $participant_present_attendance = [];
        $participant_absent_attendance = [];
        $certificate_status_marked= [];
        $schedules = Schedule::where('id',$id)->with('organisation','department','section','material_type','schedule_role','schedule_participants')->first();
        if(isset($schedules->schedule_participants))
        {
            foreach($schedules->schedule_participants as $schedule_participant)
            {
                $user_id = $schedule_participant->user_id;
                $participant_present_attendance[$user_id] = Attendance::where('schedule_id',$id)->where('user_id',$user_id)->where('attendance_status',1)->count();
                $participant_absent_attendance[$user_id] = Attendance::where('schedule_id',$id)->where('user_id',$user_id)->where('attendance_status',2)->count();
                $certificate_status_marked[$user_id] = Certificate::where('schedule_id',$id)->where('user_id',$user_id)->where('certificate_status',1)->count();
            }
        }
        return view('Training/view_participants_details', compact('participant_present_attendance','participant_absent_attendance','schedules','certificate_status_marked'));
    }
    public function particiapant_certificate_post(Request $request)
    {
        $schedule_id = $request->schedule_id;
        $training_date = [];
        $certificate_delete = Certificate::where('schedule_id',$schedule_id)->delete();
        $schedules = Schedule::where('id',$schedule_id)->first();
        $participants = Participant::where('schedule_id',$schedule_id)->get();
        foreach ($participants as $participant) 
        {
            $user_id = $participant->user_id;
            $certificate_status = $user_id."certificate";
            $certificate_status = $request->$certificate_status;
            if($certificate_status==1)
            {
                $certificate_status_value = 1;
            }
            else
            {
                $certificate_status_value = 2;
            }
            $certificate_date = date("Y-m-d");
            $id = Auth::user()->id;
            $certificate_count = Certificate::where('schedule_id',$schedule_id)->where('user_id',$user_id)->where('certificate_date',$certificate_date)->count();
            if($certificate_count==0)
            {
                $insert_id = Certificate::create([
                    'schedule_id' => $schedule_id,
                    'user_id' => $user_id,
                    'certificate_date' => $certificate_date,
                    'created_by' => $id,
                    'certificate_status' => $certificate_status_value,
                ]);
            }
        }
        if($insert_id)
            notify()->success('Certificate has been generated');
        else
            smilify('Error', '');

        return redirect('certificate');
    }

    //////cerificate preview/////////////////////////////////////
    public function certificatepreview($schedule_participant_id)
    {
        $schedule_participants = explode("~", $schedule_participant_id);
        $schedule_id = $schedule_participants[0];
        $participant_id = $schedule_participants[1];
        $certificate = Certificate::where('schedule_id',$schedule_id)->where('user_id',$participant_id)->with('users','schedule')->where('certificate_status',1)->first();
        return view('Training/certificatepreview', compact('schedule_id','certificate'));
        /*$view1 = \View::make('certificatepreview', compact('schedule_id','certificate'));
        $html1 = $view1->render();
        $pdf = new PDF();
        $pdf::SetTitle('Certificate');
        $pdf::AddPage('P', 'A4');
        $pdf::writeHTML($html1, true, false, true, false, '');
        $pdf::Output(uniqid().'Certificate.pdf');
        $pdf::reset();*/
    }

}