<?php

namespace App;

use DB;
use Illuminate\Database\Eloquent\Model;
use Auth;
class Login extends Model
{

    public $table = "login";
   
   //protected $fillable = ['reg_name','budget_ngo'];
    public static function getAllTeacher()
   {
   	 return DB::table('login')->where('email','!=',"")->select('email','fname','lname','picture','minibio','timezone')->get();
   }
   public static function getTeacherByid($email)
   {
     return DB::table('login')->where('email',$email)->get();
   }
 public static function lgin($user_id)
   {
          
       /* $user_email = Confirmedusers::where("secemail",$email)->get();
        
        if(count($user_email)>0)
        {
          
            //$request->email=$user_email[0]['email'];
             if (auth()->guard('web')->attempt(['email' => $user_email[0]->email, 'password' => $pass]))
            {

                  return "yes";
            } 
         
        }
       

            if(auth()->guard('web')->attempt(['email' => $email, 'password' => $pass]))
            { 
                 return "yes";
            }
           
                
            return "no";*/
              Auth::loginUsingId($user_id);

return "yes";
            die();  
   }
    public static function checkTeacherSchedule($teacher_email,$start,$lessonmins) {
   
    $end = $start+(30*60);
    $start++;
    $end--;
    $query = DB::select("SELECT * FROM `lessonschedule` WHERE `teacher` = '$teacher_email' AND ((`start_unix` BETWEEN '$start' AND '$end') OR (`end_unix` BETWEEN '$start' AND '$end'))");
    $count = count($query);
    return $count;
   }
   public static function NotPrivategetTeacher($learn,$sort_by,$search=NULL)
   {

    $prate=" group by l.id";
    if($sort_by!="")
    {
       $prate.=" order by privaterate ".$sort_by;
    }
    if($search !== NULL && $search !== "")
    {
       $search=" AND CONCAT_WS('',l.languageteach1,l.languageteach2,l.languageteach3,l.languageteach4,l.languageteach5,l.fname,l.lname) LIKE '%$search%'";
    }
  return DB::select("SELECT l.*,(SELECT t.fullbio 
       FROM teacher_bios as t
       WHERE l.email=t.email and t.lang in('$learn','English') order by FIELD(t.lang,'$learn','English')
       LIMIT 1) as fullbio FROM `login` l WHERE l.privaterate>0 and l.status_website = 'Yes'  $search $prate ");
 

    // return DB::select("SELECT t.minibio,t.fullbio,l.* FROM login l inner join teacher_bios t on l.email=t.email and t.lang in('$learn','English') WHERE l.privaterate>0 and l.status_website = 'Yes'  $search $prate ");
   }
   public static function PrivategetTeacher($sort_by,$search=NULL)
   {

     $prate="";
    if($sort_by!="")
    {
       $prate=" order by privaterate ".$sort_by;
    }
     if($search !== NULL && $search !== "")
    {
       $search=" AND CONCAT_WS('',languageteach1,languageteach2,languageteach3,languageteach4,languageteach5,fname,lname) LIKE '%$search%'";
    }
     return DB::select("SELECT * FROM `login` WHERE privaterate>0 and `status_website` = 'Yes' $search $prate");
   }
     public static function NonOctbTeacher($learn,$accountid,$sort_by,$search=NULL)
   {
   

     $prate="group by l.id";
    if($sort_by!="")
    {
       $prate.=" order by l.privaterate ".$sort_by;
    }
     if($search !== NULL && $search !== "")
    {
       $search=" AND CONCAT_WS('',l.languageteach1,l.languageteach2,l.languageteach3,l.languageteach4,l.languageteach5,l.fname,l.lname) LIKE '%$search%'";
    }

    return DB::select("SELECT l.*,(SELECT t.fullbio 
       FROM teacher_bios as t
       WHERE l.email=t.email and t.lang in('$learn','English') order by FIELD(t.lang,'$learn','English')
       LIMIT 1) as fullbio FROM `login` l inner join confirmedusers c on (l.email=c.teacher1 or l.email=c.teacher2 or l.email=c.teacher3) where c.accountid='".$accountid."' and l.privaterate>0 $search $prate");
    
     //return DB::select("SELECT l.*,t.minibio,t.fullbio FROM `login` l inner join confirmedusers c on (l.email=c.teacher1 or l.email=c.teacher2 or l.email=c.teacher3) inner join teacher_bios t on l.email=t.email and t.lang in('$learn','English')  where c.accountid='".$accountid."' and l.privaterate>0 $search $prate");
   }
   public static function minutesPurchasedPrivateOctb($accountid,$teacher)
   {
       $query= DB::select(" SELECT SUM(`minutes`) as `minutes` FROM `invoices` WHERE `accountid` = '".$accountid."' AND `teacher` = '".$teacher."'");
      return $time = $query[0]->minutes;
   }
    public static function minutesCompletedPrivateOctb($accountid,$teacher)
   {

       $query= DB::select(" SELECT SUM(`lessonmins`) as `minutes` FROM `lessonschedule` WHERE `accountid` = '".$accountid."' AND `teacher` = '".$teacher."' AND `company` = 'Private'");
      return $time = $query[0]->minutes;
   }

   public static function minutesPurchasedPrivateNonOctb($accountid,$teacher)
   {
       $query= DB::select(" SELECT SUM(`minutes`) as `minutes` FROM `invoices` WHERE `accountid` = '".$accountid."' AND `teacher` = '".$teacher."'");
      return $time = $query[0]->minutes;
   }
    public static function minutesCompletedPrivateNonOctb($accountid,$teacher)
   {

       $query= DB::select(" SELECT SUM(`lessonmins`) as `minutes` FROM `lessonschedule` WHERE `accountid` = '".$accountid."' AND `teacher` = '".$teacher."'");
      return $time = $query[0]->minutes;
   }

    public static function minutesPurchasedNonPrivateOctb($accountid,$company)
   {
       $query= DB::select("SELECT SUM(`timepurchased`) as `timepurchased` FROM `lessontotals` WHERE `accountid` = '".$accountid."'");
      return $time = $query[0]->timepurchased;
   }
    public static function minutesCompletedNonPrivateOctb($accountid,$company)
   {

       $query= DB::select(" SELECT SUM(`lessonmins`) as `lessonmins` FROM `lessonschedule` WHERE `accountid` ='".$accountid."' AND `company` = '".$company."'");
      return $time = $query[0]->lessonmins;
   }
   public static function ClientwalletBallance($accountid,$teacher)
   {
      $query= DB::select("SELECT wallet_balance FROM `confirmedusers` WHERE `accountid` ='".$accountid."'");
      return  $query[0]->wallet_balance;
   }
       public static function CheckTeacherAvilblaty($timezone,$teacher_email)
   {
      date_default_timezone_set($timezone);
      $start=strtotime(date('Y-m-d')." 00:00:00");
      $schedule_result = DB::select("SELECT count(*) as a FROM `schedule` WHERE `type`='exception' and `teacher` = '$teacher_email' AND ((`start_time`>='$start') OR (`end_time` >= '$start'))  order by start_time asc limit 1");
      return $schedule_result[0]->a;
   }

    public static function notificationAdd($accountid,$message) {
    date_default_timezone_set("UTC");
    $createdat = date('Y-m-d H:i:s');
    $time = date("c", time());

    DB::table('notification')->insert(array('accountid'=>$accountid,'message'=>$message,'time'=>$time,'status'=>0));
    }



  
}