<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\MessageFrom;
use App\About;
use App\News;
use App\Slider;
use App\Service;
use DB;
use App\Event;
use App\FileData;
use App\Testimonial;
use App\BoardOfDirector;
use App\TeamMember;
use App\Gallery;
use App\Report;
use App\Pp;
use App\Ir;
use App\Career;
use App\Download;
use App\Today;
use App\Affiliation;
use App\UsefulLink;
use App\Video;
use Mail;
use App\Mail\SendMail;
class PublicController extends Controller
{
    public static function NepaliSelector($column,$lng){
if($lng=="np")
    return $column."_nepali as ".$column;
else
    return $column;


    }
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function getIndex($lng="en")

    {
        if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
      //  session('lng',$lng);
        $chairman=MessageFrom::select($this->NepaliSelector("description",$lng),$this->NepaliSelector("name",$lng),"token")->where('message_from','=','Chairperson')->first();
        $manager=MessageFrom::select($this->NepaliSelector("description",$lng),$this->NepaliSelector("name",$lng),"token")->where('message_from','=','CEO')->first();
        $about=About::select($this->NepaliSelector("introduction",$lng),"token")->first();
        $data=About::select($this->NepaliSelector("introduction",$lng),$this->NepaliSelector("mission",$lng),$this->NepaliSelector("vision",$lng),$this->NepaliSelector("objective",$lng),"token")->first();
        $saving=Service::select("service_type","token",DB::raw("(SELECT file_name from file_datas where file_datas.token=services.token ORDER By rand() LIMIT 1) as file_name"))->where('service_type','=','Saving')->get();
        $loan=Service::select("service_type","token",DB::raw("(SELECT file_name from file_datas where file_datas.token=services.token ORDER By rand() LIMIT 1) as file_name"))->where('service_type','=','Loan')->get();
        $remittance=Service::select("service_type","token",DB::raw("(SELECT file_name from file_datas where file_datas.token=services.token ORDER By rand() LIMIT 1) as file_name"))->where('service_type','=','Remittance')->get();
        $slider=Slider::select($this->NepaliSelector("title",$lng),"token")->orderBy('id', 'desc')->take(8)->get();
        // $service=Service::select("token","service_type")->get();
        $news=News::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"date","token")->orderBy('date','desc')->take(3)->get();
        $events=Event::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"date","token","location",DB::raw("(SELECT file_name from file_datas where file_datas.token=events.token LIMIT 1) as file_name"))->orderBy('date','desc')->take(2)->get();
        $successstory=Testimonial::select($this->NepaliSelector("full_name",$lng),$this->NepaliSelector("post",$lng),$this->NepaliSelector("description",$lng),"token")->orderBy('created_at','desc')->get();
        $link=UsefulLink::selectRaw('*,(SELECT file_name from file_datas where file_datas.token=useful_links.token LIMIT 1) as file_name')->get();
        $today=DB::table('todays')->get();
        $affiliation=DB::table('affiliations')->get();

      


        return view('Client.Home.Index')->with('chairman',$chairman)->with('manager',$manager)->with('about',$about)->with('slider',$slider)->with('saving',$saving)->with('loan',$loan)->with('remittance',$remittance)->with('new',$news)->with('events',$events)->with('data',$data)->with('successstory',$successstory)->with('links',$link)->with('sss',$today)->with('ss',$affiliation);
    }


    public function getNewsAll($lng="en")
    {
        if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
      //  session('lng',$lng);
        $news=News::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"date","token")->get();
        return view('Client.News.NewsAll')->with('news',$news);
    }
    
    

     public function getSingleNews($lng="en",Request $request,$title)
    {     if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
      //  session('lng',$lng);
        $news=News::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"date","token")->where('token','=',$request->id)->first();

        $relates=News::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"date","token")->where('token','<>',$request->id)->get();
        return view('Client.News.SingleNews')->with('new',$news)->with('relates',$relates);
    }



    public function getEventAll($lng="en")
    {
        if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
      //  session('lng',$lng);
        $events=Event::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"date","location","token",DB::raw("(SELECT file_name from file_datas where file_datas.token=events.token LIMIT 1) as file_name"))->orderBy('date','desc')->get();

        return view('Client.Event.EventAll')->with('events',$events);

    }


     public function getSingleEvent($lng="en",Request $request,$title)
    {     if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
      //  session('lng',$lng);
        $event=Event::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"date","location","token")->where('token','=',$request->id)->first();

        $image=FileData::where('token','=',$request->id)->get();

        // $relates=DB::select("SELECT (".$this->NepaliSelector("title",$lng)."),'date',(select file_name FROM file_datas where token=e.token limit 1) as file_name from events e where token<> '".$request->id."' limit 3");

        $relates=Event::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"date","token",DB::raw("(SELECT file_name from file_datas where file_datas.token=events.token LIMIT 1) as file_name"))->where('token','<>',$request->id)->orderBy('date','desc')->get();

        return view('Client.Event.SingleEvent')->with('event',$event)->with('relates',$relates)->with('images',$image);
    }




    public function getAboutUs($lng="en")
    {
          if($lng=="en"||$lng=="np")
            session(['lng' => $lng]);
    $data=About::select($this->NepaliSelector("introduction",$lng),$this->NepaliSelector("mission",$lng),$this->NepaliSelector("vision",$lng),$this->NepaliSelector("objective",$lng),"token")->first();
        return view('Client.About Us.About Us')->with('data',$data);
    }


     public function getMessageFrom($lng="en")
    {    if($lng=="en"||$lng=="np")
        session(['lng' => $lng]);
    $chairman=MessageFrom::select($this->NepaliSelector("description",$lng),$this->NepaliSelector("name",$lng),"token","message_from")->get();
        // $manager=MessageFrom::select($this->NepaliSelector("description",$lng),$this->NepaliSelector("name",$lng),"token")->where('message_from','=','Manager')->first();
        return view('Client.About Us.MessageFrom')->with('message',$chairman);
    }

    public function getContact($lng="en")
    {  
      if($lng=="en"||$lng=="np")
            session(['lng' => $lng]);   
      return view('Client.Contact.Contact');
    }

     public function postContact($lng="en")
    {  
         Mail::send(new SendMail);
        return redirect::back()->with('status', 'Message submitted successfully!');
    }



    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
     public function getboard_of_directors($lng="en")
    {
        if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
     
     $bm=BoardOfDirector::select($this->NepaliSelector("post",$lng),$this->NepaliSelector("name",$lng),"token")->where('member_type','Board Member')->orderBy('position_order','asc')->get();
     $acm=BoardOfDirector::select($this->NepaliSelector("post",$lng),$this->NepaliSelector("name",$lng),"token")->where('member_type','Account Supervisiory Committee')->orderBy('position_order','asc')->get();
     $ad=BoardOfDirector::select($this->NepaliSelector("post",$lng),$this->NepaliSelector("name",$lng),"token")->where('member_type','Advisor')->orderBy('position_order','asc')->get();
     $lm=BoardOfDirector::select($this->NepaliSelector("post",$lng),$this->NepaliSelector("name",$lng),"token")->where('member_type','Loan Management Sub-Committee')->orderBy('position_order','asc')->get();
     $es=BoardOfDirector::select($this->NepaliSelector("post",$lng),$this->NepaliSelector("name",$lng),"token")->where('member_type','Education Sub-Committee')->orderBy('position_order','asc')->get();
     $mf=BoardOfDirector::select($this->NepaliSelector("post",$lng),$this->NepaliSelector("name",$lng),"token")->where('member_type','Microfinance Sub-Committee')->orderBy('position_order','asc')->get();
     $wd=BoardOfDirector::select($this->NepaliSelector("post",$lng),$this->NepaliSelector("name",$lng),"token")->where('member_type','Woman Development Sub-Committee')->orderBy('position_order','asc')->get();

        return view('Client.About Us.Boardofdirectors')->with('bm',$bm)->with('acm',$acm)->with('ad',$ad)->with('lm',$lm)->with('es',$es)->with('mf',$mf)->with('wd',$wd);

    }


    public function getmanagement_team($lng="en")
    {  
        if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
        
       $tm=TeamMember::select($this->NepaliSelector("position",$lng),$this->NepaliSelector("name",$lng),"token")->orderBy('position_order','asc')->get();

      return view('Client.About Us.Team')->with('tm',$tm);
    }




    public function getgallery($lng="en")
    {  
        if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
        
     $gallery=Gallery::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"token",DB::raw("(SELECT file_name from file_datas where file_datas.token=galleries.token ORDER By rand() LIMIT 1)  as file_name"))->paginate(10);

      return view('Client.Gallery.GalleryAll')->with('galleries',$gallery);
    }



     public function getSingleGallery(Request $request,$lng="en")
    {  
        if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
        
     $gallery=Gallery::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"token")->where('token','=',$request->id)->first();

        $image=FileData::where('token','=',$request->id)->get();

      return view('Client.Gallery.SingleGallery')->with('gallery',$gallery)->with('images',$image);
    }


    public function getService(Request$request,$lng="en")
    {  
        if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
        
     $service=Service::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"token",DB::raw("(SELECT file_name from file_datas where file_datas.token=services.token  LIMIT 1)  as file_name"))->where('service_type','=',$request->service)->paginate(10);


     $title=Service::where('service_type','=',$request->service)->first();

      return view('Client.Service.SavingLoanRemittanceAll')->with('services',$service)->with('title',$title);
    }


    public function getSingleService(Request $request,$lng="en")
    {  
        if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
        
     $service=Service::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"service_type","token","interest",$this->NepaliSelector("feature",$lng))->where('token','=',$request->id)->first();


     $relate=Service::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"service_type","token","interest",$this->NepaliSelector("feature",$lng))->where('token','<>',$request->id)->where('service_type','=',$service->service_type)->take(5)->get();

      return view('Client.Service.SavingLoan')->with('service',$service)->with('relates',$relate);
    }


    public function getReport(Request $request,$lng="en")
    { 

         if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
         
         $report_type = '';
         switch($request->report){
             case 'Pearl Ratio':
                 $report_type = 'Pearl Report';
                 break;
             case 'AGM Report':
                 $report_type = 'AGM Report';
                 break;
             case 'Trial Balance':
                 $report_type = 'Trial Balance';
                 break;
             case 'Balance Sheet':
                 $report_type = 'Balance Sheet';
                 break;
                 
            
         }

      $report=Report::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"report_type","token","date","file_name")->where('report_type','=',$report_type)->get();
      $title=Report::where('report_type','=',$report_type)->first();  

      return view('Client.Report.Report')->with('reports',$report)->with('title',$title);
    }



    public function getPp($lng="en")
    {
        if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
      //  session('lng',$lng);
        $pp=Pp::select($this->NepaliSelector("policy",$lng),$this->NepaliSelector("p_procedure",$lng),"token")->first();

        return view('Client.pp&Ir.Pp')->with('pp',$pp);

    }


    public function getIr($lng="en")
    {
        if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
     

       $ff=Ir::select($this->NepaliSelector("product",$lng),"interest_rate","token")->where('type','Fixed Deposit')->get();

         $sir=Ir::select($this->NepaliSelector("product",$lng),"interest_rate","token")->where('type','SIR')->get();

           $lir=Ir::select($this->NepaliSelector("product",$lng),"interest_rate","token")->where('type','LIR')->get();

        return view('Client.pp&Ir.Ir')->with('fds',$ff)->with('sir',$sir)->with('lir',$lir);

    }



    public function getCareer($lng="en")
    {
          if($lng=="en"||$lng=="np")
            session(['lng' => $lng]);
    $data=Career::select($this->NepaliSelector("title",$lng),$this->NepaliSelector("description",$lng),"f_date","t_date","token")->get();
        return view('Client.Career.Career')->with('careers',$data);
    }


    public function getDownload($lng="en")
    {
          if($lng=="en"||$lng=="np")
            session(['lng' => $lng]);
    $data=Download::select($this->NepaliSelector("title",$lng),"file_name","token")->get();
        return view('Client.Home.Download')->with('downloads',$data);
    }
    
     public function videos($lng="en")
     
     {  if($lng=="en"||$lng=="np")
         session(['lng' => $lng]);
      //  session('lng',$lng);
        $video=Video::select($this->NepaliSelector("title",$lng),"link","created_at","title_nepali")->get();
         return view('Client.Videos.video')->with('video',$video);
     }
     
      public function press()
     {
         return view('Client.Press.press');
     }
     public function singlepress()
     {
         return view('Client.Press.singlepress');
     }
   

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }
}
