<?php

namespace App\Http\Controllers;


use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class RequestsController extends Controller
{
  /**
   * Create a new controller instance.
   *
   * @return void
   */
  public function __construct()
  {
    $this->middleware('auth');
  }


  public function refuzokerkesen($vid, $uid, Request $request)
  {
    $pershkrimianulimit = $request->pershkrimianulimit;
    try {
      $crudpajisja = DB::update(
        "UPDATE `kerkesat` SET `statusi`='3' ,pranuesi_id=" . $uid . ",pershkrimianulimit='" . $pershkrimianulimit . "' WHERE visitid='$vid'"
      );

      $crudhistoriakerkeses = DB::insert(
        'INSERT INTO `kerkesat_historia`(`Komenti`, `VisitID`, `user_id`, `statusi`) VALUES (?,?,?,?)',
        [$pershkrimianulimit, $vid, $uid, 3]
      );

      return response('refuzuar me sukses', 200);
    } catch (\Illuminate\Database\QueryException $ex) {
      $res['success'] = false;
      //  $ex->getMessage() per me shfaq qfar gabimi 
      $res['message'] =   "Error";
      return response($res, 500);
    }
  }
  public function kthejepritje($vid, $uid, Request $request)
  {
    $pershkrimianulimit = $request->pershkrimianulimit;
    try {
      $crudpajisja = DB::update(
        "UPDATE `kerkesat` SET `statusi`='0', pranuesi_id=" . $uid . " WHERE visitid='$vid'"
      );

      $crudhistoriakerkeses = DB::insert(
        'INSERT INTO `kerkesat_historia`(`Komenti`, `VisitID`, `user_id`, `statusi`) VALUES (?,?,?,?)',
        [$pershkrimianulimit, $vid, $uid, 0]
      );
      return response('refuzuar me sukses', 200);
    } catch (\Illuminate\Database\QueryException $ex) {
      $res['success'] = false;
      //  $ex->getMessage() per me shfaq qfar gabimi 
      $res['message'] =   "Error";
      return response($res, 500);
    }
  }
  public function aprovokerkesen($vid, $uid)
  {
    try {
      $crudpajisja = DB::update(
        "UPDATE `kerkesat` SET `statusi`=1 ,pranuesi_id=" . $uid . " WHERE visitid='$vid'"
      );

      $crudhistoriakerkeses = DB::insert(
        'INSERT INTO `kerkesat_historia`(`Komenti`, `VisitID`, `user_id`, `statusi`) VALUES (?,?,?,?)',
        ['Kërkesa u pranua dhe po procesohet', $vid, $uid, 1]
      );

      return response('aprovuar me sukses', 200);
    } catch (\Illuminate\Database\QueryException $ex) {
      $res['success'] = false;
      //  $ex->getMessage() per me shfaq qfar gabimi 
      $res['message'] =   "Error";
      return response($res, 500);
    }
  }
  public function mbyllkerkesen($vid, $uid)
  {
    try {
      $crudpajisja = DB::update(
        "UPDATE `kerkesat` SET `statusi`=2 ,pranuesi_id=" . $uid . " WHERE visitid='$vid'"
      );
      $crudhistoriakerkeses = DB::insert(
        'INSERT INTO `kerkesat_historia`(`Komenti`, `VisitID`, `user_id`, `statusi`) VALUES (?,?,?,?)',
        ['Kërkesa u përfundua', $vid, $uid, 2]
      );
      return response('Kërkesa u përfundua me sukses', 200);
    } catch (\Illuminate\Database\QueryException $ex) {
      $res['success'] = false;
      //  $ex->getMessage() per me shfaq qfar gabimi 
      $res['message'] =   "Error";
      return response($res, 500);
    }
  }
  //Done by Arda
  public function updprocesuar($vid, $pid, Request $request)
  {
    //params tparat jane me ato qysh i kemi deklaru ne procedure
    // tdytat mujn me kon tmarrne nga inputi qe jane te deklarume ne ajax
    $Kontrate = $request->Kontrate;
    $Falas = $request->Falas;
    $Garancion = $request->Garancion;
    $Reagjentesh = $request->Reagjentesh;
    $Qera = $request->Qera;
    $problemi = $request->problemi;
    $vizita = $request->vizita;
    $datasot = $request->datasot;
    $kohaardhjes = $request->kohaardhjes;
    $kohalarg = $request->kohalarg;
    $trajnimdata = $request->trajnimdata;
    $instalimdata = $request->instalimdata;
    $tjeterdata = $request->tjeterdata;
    $Kohakompani = $request->Kohakompani;
    $Kohalab = $request->Kohalab;
    $Kohaudhe = $request->Kohaudhe;
    $Faturime = $request->Faturime;
    $TT = $request->TT;
    $Numrifatures = $request->Numrifatures;
    $pershkrimipunes = $request->pershkrimipunes;
    $Testetekryera = $request->Testetekryera;
    $kohapershkrim = $request->kohapershkrim;
    $Shenimevizit = $request->Shenimevizit;
    $materialekonsumi = $request->materialekonsumi;
    $Aparaticheck = $request->Aparaticheck;
    //    if($Kontrate == null || $Falas == null ||
    //    $Garancion == null || $Reagjentesh == null )
    //    {
    //      $res['success'] = false;
    //     //   $ex->getMessage() per me shfaq qfar gabimi 
    //      $res['message'] = 'Plotsoni te Gjitha fushat';
    //      return response($res, 400 );
    //    }
    try {
      $crudpajisja = DB::update(
        "UPDATE `kerkesat` SET `Kontrat`='$Kontrate',`Falas`='$Falas',`Garancion`='$Garancion',`Reagjentesh`='$Reagjentesh'
           ,`Qera`='$Qera',`pershkrimiproblemit`='$problemi',`llojivizites`='$vizita',`datasot`='$datasot',`kohaardhjes`='$kohaardhjes'
           ,`kohalargimit`='$kohalarg',`Trajnim`='$trajnimdata',`Instalim`='$instalimdata',`Tjetërdetaje`='$tjeterdata',`Kohakompani`='$Kohakompani'
           ,`Kohalab`='$Kohalab',`Kohaudhe`='$Kohaudhe',`Faturime`='$Faturime',`TT`='$TT',`Numrifatures`='$Numrifatures',`pershkrimipunes`='$pershkrimipunes'
           ,`Testetekryera`='$Testetekryera',`kohapershkrim`='$kohapershkrim',`Aparaticheck`='$Aparaticheck',`Shenimevizit`='$Shenimevizit',`materialekonsumi`='$materialekonsumi' WHERE visitid='$vid' AND paisje='$pid'"
      );
      $arr = $Aparaticheck;


      return response($arr, 200);
    } catch (\Illuminate\Database\QueryException $ex) {
      $res['success'] = false;
      //  $ex->getMessage() per me shfaq qfar gabimi 
      $res['message'] =   "Error";
      return response($res, 500);
    }
  }

  public function showrequests()
  {
    $kerkesat = DB::select('SELECT kps.id, kps.klient_id, kps.institut_id, kps.pajisja_id, kps.service_id, kps.komenti_klientit, kps.statusi, kps.data, kps.pranuar_nga, p.id, p.emri, ins.id, ins.emri_institutit, s.id, s.service_name, pjs.emri_pajisjes
            FROM kerkesa_per_servisim kps
            LEFT JOIN perdoruesit p ON kps.klient_id = p.id
            LEFT JOIN tbl_institucioni ins ON kps.institut_id = ins.id
            LEFT JOIN pajisja pjs ON kps.pajisja_id = pjs.id
            LEFT JOIN services s ON kps.service_id = s.id');
    if (empty($kerkesat)) {
      $arr = array(300);
      echo json_encode($arr);
    } else {
      $countallperdoruesit = count($kerkesat);
      if ($countallperdoruesit > 0) {
        return response()->json(

          $kerkesat,

        );
      }
    }
  }
  public function showkomentimin($vid)
  {
    $kerkesat = DB::select('SELECT k.* ,ss.emertimi,p.emri,p.mbiemri FROM `kerkesat_historia` k LEFT JOIN statuset_kerkesave ss on k.statusi=ss.statusi LEFT JOIN perdoruesit p on k.user_id=p.id WHERE   `VisitID`=' . $vid . ' ORDER BY Id ASC');
    if (empty($kerkesat)) {
      $arr = array(300);
      echo json_encode($arr);
    } else {
      $countallperdoruesit = count($kerkesat);
      if ($countallperdoruesit > 0) {
        return response()->json(

          $kerkesat,

        );
      }
    }
  }
  public function shtokomentimin(Request $request)
  {
    $pershkrimi = $request->pershkrimi;
    $visitid = $request->visitid;
    $emriperdorues = $request->emriperdorues;
    try {
      $crudhistoriakerkeses = DB::insert(
        'INSERT INTO `kerkesat_historia`(`Komenti`, `VisitID`, `user_id`, `statusi`) VALUES (?,?,?,?)',
        [$pershkrimi, $visitid, $emriperdorues, 0]
      );



      return response($crudhistoriakerkeses, 200);
    } catch (\Illuminate\Database\QueryException $ex) {
      $res['success'] = false;

      $res['message'] =   "Error";
      return response($res, 500);
    }
  }
  public function getbyvisit($id)
  {
    $kerkesat = DB::select('SELECT perd.emri,perd.mbiemri ,pran.emri as pranemri,pran.mbiemri as pranmbiemri , br.emri_prodhuesit,p.emri_pajisjes
        ,s.service_name,q.emri_qytetit, pr.emri as prioriteti,k.pershkrimi, k.visitid,ins.emri_institutit,k.perderues_id,k.institucioni_id,st.emertimi,
        k.data FROM kerkesat k LEFT JOIN perdoruesit perd on perd.id=k.perderues_id LEFT JOIN perdoruesit pran on pran.id=k.pranuesi_id
         LEFT JOIN firma_prodhuese_pajisjeve br ON br.id=k.brand LEFT JOIN pajisja p ON p.id=k.paisje LEFT JOIN services s ON s.id=k.service 
         LEFT JOIN tbl_institucioni ins on ins.id=k.institucioni_id LEFT JOIN prioritetet pr ON pr.id=k.prioriteti LEFT JOIN qyteti q ON q.id=perd.adresa
          LEFT JOIN statuset_kerkesave st ON st.statusi=k.statusi WHERE visitid=' . $id);
    if (empty($kerkesat)) {
      $arr = array(300);
      echo json_encode($arr);
    } else {
      $countallperdoruesit = count($kerkesat);
      if ($countallperdoruesit > 0) {
        return response()->json(

          $kerkesat,

        );
      }
    }
  }
  public function getpaisjevisit($id)
  {
    $kerkesat = DB::select('SELECT p.emri_pajisjes , p.id as paisjaid ,l.numri_serik,l.versioni_programit FROM kerkesat k  LEFT JOIN pajisja p ON p.id=k.paisje  LEFT JOIN perkatesia_pajisjes l ON l.pajisja_id=k.paisje where visitid=' . $id . ' AND l.instituti_id=k.institucioni_id  GROUP by p.id ;');
    if (empty($kerkesat)) {
      $arr = array(300);
      echo json_encode($arr);
    } else {
      $countallperdoruesit = count($kerkesat);
      if ($countallperdoruesit > 0) {
        return response()->json(

          $kerkesat,

        );
      }
    }
  }
  public function getservisit($id, $pid)
  {
    $kerkesat = DB::select('SELECT p.emri_pajisjes , p.id as paisjaid, ser.id as serid,ser.service_name , k.* FROM kerkesat k  LEFT JOIN pajisja p ON p.id=k.paisje LEFT JOIN services ser ON ser.id=k.service where visitid=' . $id . ' and p.id =' . $pid . ' ;');
    if (empty($kerkesat)) {
      $arr = array(300);
      echo json_encode($arr);
    } else {
      $countallperdoruesit = count($kerkesat);
      if ($countallperdoruesit > 0) {
        return response()->json(

          $kerkesat,

        );
      }
    }
  }
  public function kerkesa(Request $request)
  {
    $veprimi = $request->veprimi;
    $kerkesa = $request->kerkes_id;
    $statusi_k = $request->statusi_kerkeses;
    $pranoi_kerkesen = $request->pranuesi_id;
    $klienti = $request->kerkuesi_id;

    try {
      $crudkerkesa = DB::select(
        'CALL `Kerkesat`(?,?,?,?,?)',
        [$veprimi, $kerkesa, $statusi_k, $pranoi_kerkesen, $klienti]
      );
      if ($veprimi == 'prano') {
        $crudkerkesa = 'Kërkesa u pranua';
      }
      if ($veprimi == 'perfundo') {
        $crudkerkesa = 'Kërkesa u përfundua';
      }
      if ($veprimi == 'anulo') {
        $crudkerkesa = 'Refuzimi i kërkesës';
      }

      return response($crudkerkesa, 200);
    } catch (\Illuminate\Database\QueryException $ex) {
      $res['success'] = false;
      //  $ex->getMessage() per me shfaq qfar gabimi 
      $res['message'] =   "Error";
      return response($res, 500);
    }
  }

  public function getkerkesapritje($instituti)
  {

    $kerkesat = DB::select('SELECT perd.emri,perd.mbiemri ,pran.emri as pranemri,pran.mbiemri  as pranmbiemri ,br.emri_prodhuesit,k.statusi,p.emri_pajisjes,s.service_name, pr.emri,k.pershkrimi, st.emertimi,k.visitid,ins.emri_institutit,k.perderues_id,k.institucioni_id,k.statusi,k.data FROM kerkesat k LEFT JOIN firma_prodhuese_pajisjeve br ON br.id=k.brand LEFT JOIN tbl_institucioni ins on ins.id=k.institucioni_id LEFT JOIN pajisja p ON p.id=k.paisje LEFT JOIN services s ON s.id=k.service LEFT JOIN perdoruesit perd on perd.id=k.perderues_id LEFT JOIN perdoruesit pran on pran.id=k.pranuesi_id LEFT JOIN prioritetet pr 
    ON pr.id=k.prioriteti LEFT JOIN statuset_kerkesave st ON st.statusi=k.statusi WHERE k.statusi = 0   AND 
    (CASE
    WHEN ? = 1 THEN true
    ELSE k.institucioni_id =?
    END)
     GROUP by visitid', [$instituti, $instituti]);
    if (empty($kerkesat)) {
      $arr = array(300);
      echo json_encode($arr);
    } else {
      $countallperdoruesit = count($kerkesat);
      if ($countallperdoruesit > 0) {
        return response()->json(

          $kerkesat,

        );
      }
    }
  }
  public function getkerkesaproces($instituti)
  {

    $kerkesat = DB::select('SELECT perd.emri,perd.mbiemri ,pran.emri as pranemri,pran.mbiemri  as pranmbiemri ,k.pranuesi_id, br.emri_prodhuesit,p.emri_pajisjes,s.service_name, pr.emri as prioriteti,k.pershkrimi, k.visitid,k.perderues_id,ins.emri_institutit,k.institucioni_id,st.emertimi,k.data,k.statusi FROM kerkesat k
     LEFT JOIN firma_prodhuese_pajisjeve br ON br.id=k.brand
      LEFT JOIN pajisja p ON p.id=k.paisje 
    LEFT JOIN services s ON s.id=k.service 
    LEFT JOIN tbl_institucioni ins on ins.id=k.institucioni_id
     LEFT JOIN perdoruesit perd on perd.id=k.perderues_id
     LEFT JOIN perdoruesit pran on pran.id=k.pranuesi_id
     LEFT JOIN prioritetet pr ON pr.id=k.prioriteti 
     LEFT JOIN statuset_kerkesave st ON st.statusi=k.statusi
      WHERE k.statusi = 1 AND 
      (CASE
    WHEN ? = 1 THEN true
    ELSE k.institucioni_id =?
    END)
     GROUP by visitid', [$instituti, $instituti]);
    if (empty($kerkesat)) {
      $arr = array(300);
      echo json_encode($arr);
    } else {
      $countallperdoruesit = count($kerkesat);
      if ($countallperdoruesit > 0) {
        return response()->json(

          $kerkesat,

        );
      }
    }
  }
  public function delegouser(Request $request)
  {
    $deleguesi = $request->deleguesi;
    $id = $request->id;
   

    try {
      $crudclient = DB::table('kerkesat')->where('visitid', $id)  // find your user by their email
      ->update([
          'pranuesi_id' => $deleguesi,
      ]);

      return response('Sukses', 200);
    } catch (\Illuminate\Database\QueryException $ex) {
      $res['success'] = false;
      //  $ex->getMessage() per me shfaq qfar gabimi 
      $res['message'] =   "Error";
      return response($res, 500);
    }
  }
}
