<?php

namespace App\Models;

use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Factories\HasFactory;

class TimeSheet extends Model
{
    use HasFactory;
    protected $primaryKey = 'id';
    protected $table =  'timesheet_yearly';
    
    public static function weekOfMonth($y,$m,$d){

        // current week, min 1
        $w = 1;
        // for each day since the start of the month
    	for ($i = 1; $i <= $d; ++$i) {
        // if that day was a sunday and is not the first day of month
	        if ($i > 1 && date('w', strtotime("$y-$m-$i")) == 0) {
	            // increment current week
	            ++$w;
	        }
    	}
    	// now return
    	return $w;
    }

    public static function yearly_sheets_single($id)
    {
        $value = explode('-',$id);
        if(count($value) > 1){
            $id = $value[0];
            $person_type = $value[1];
        }else{
            $id = $id; 
            $person_type = 'consultant';
        }

        $yearly_sheets_single = DB::table('timesheet_yearly')
                            ->select('timesheet_yearly.01 as day1','timesheet_yearly.02 as day2',
                                'timesheet_yearly.03 as day3','timesheet_yearly.04 as day4',
                                'timesheet_yearly.05 as day5','timesheet_yearly.06 as day6',
                                'timesheet_yearly.07 as day7','timesheet_yearly.08 as day8',
                                'timesheet_yearly.09 as day9','timesheet_yearly.10 as day10',
                                'timesheet_yearly.11 as day11','timesheet_yearly.12 as day12',
                                'timesheet_yearly.13 as day13','timesheet_yearly.14 as day14',
                                'timesheet_yearly.15 as day15','timesheet_yearly.16 as day16',
                                'timesheet_yearly.17 as day17','timesheet_yearly.18 as day18',
                                'timesheet_yearly.19 as day19','timesheet_yearly.20 as day20',
                                'timesheet_yearly.21 as day21','timesheet_yearly.22 as day22',
                                'timesheet_yearly.23 as day23','timesheet_yearly.24 as day24',
                                'timesheet_yearly.25 as day25','timesheet_yearly.26 as day26',
                                'timesheet_yearly.27 as day27','timesheet_yearly.28 as day28',
                                'timesheet_yearly.29 as day29','timesheet_yearly.30 as day30',
                                'timesheet_yearly.31 as day31','timesheet_yearly.month_value',
                                'timesheet_yearly.year_value')
                                ->where('timesheet_yearly.emp_id',$id)  
                                ->where('timesheet_yearly.person_type',$person_type)  
                                ->orderBy('month_value', 'asc')
                                ->orderBy('year_value', 'dsc')->get();
        return $yearly_sheets_single;
    }

    public static function employee_payrates_single($id)
    {        
        $payrates = DB::table('employee_payrates')
                        ->join('students','students.user_id','=','employee_payrates.employee_id')
                        ->join('vendors','employee_payrates.vendor_id','=','vendors.id')
                        ->join('vendor_company','vendors.vendor_company_id','=','vendor_company.id')
                        ->join('clients','clients.id','employee_payrates.client_id')
                        ->select('employee_payrates.*','vendor_company.id as vendor_company_id','clients.name as client_name','vendors.vendor_name','vendors.vendor_email','vendors.vendor_mobile',DB::raw("CONCAT(students.first_name,' ',ifnull(students.middle_name,''),' ',ifnull(students.last_name,'')) as name"))
                        ->where('employee_payrates.status','0')
                        ->where('students.user_id',$id)
                        ->first();

        return $payrates;
    }

    public static function employee_payrates_all()
    {
       $payrates_all = DB::table('employee_payrates')
                        ->leftjoin('students','students.user_id','=','employee_payrates.employee_id')
                        ->join('vendors','employee_payrates.vendor_id','=','vendors.id')
                        ->join('vendor_company','vendors.vendor_company_id','=','vendor_company.id')
                        ->join('clients','clients.id','employee_payrates.client_id')
                        ->select('employee_payrates.*','vendor_company.id as vendor_company_id','clients.name as client_name','vendors.vendor_name','vendors.vendor_email','vendors.vendor_mobile','students.user_id as user_id',DB::raw("CONCAT(students.first_name,' ',ifnull(students.middle_name,''),' ',ifnull(students.last_name,'')) as student_name"))
                        ->where('employee_payrates.status','0')
                        ->get();

        return $payrates_all;
    }
    public static function employees_weekly_data($year,$week,$status){

        $query =  DB::table('timesheet_weekly')
                        ->join('students','students.user_id','timesheet_weekly.emp_id')
                        ->select('timesheet_weekly.*',DB::raw("CONCAT(students.first_name,' ',ifnull(students.middle_name,''),' ',ifnull(students.last_name,'')) as name"),'students.user_id as user_id')
                        ->where('timesheet_weekly.year_value',$year)
                        ->where('timesheet_weekly.week_value',$week)
                        ->where('timesheet_weekly.person_type','consultant');
                        if($status==2){
                        $query =    $query->get();
                        }if($status==1){
                        $query =    $query->where('timesheet_weekly.approved_status',1)->get();
                        }if($status==0){
                        $query =    $query->where('timesheet_weekly.approved_status',0)->get();
                         }

        return $query;
    }
    
    public static function employees_weekly_data_customer($year,$week,$status,$employee_id)
    {
        $value = explode('-',$employee_id);

        if(count($value) > 1){
            $employee_id = $value[0];
            $person_type = $value[1];
        }else{
            $employee_id = $employee_id; 
            $person_type = 'consultant';
        }

        $query =  DB::table('timesheet_weekly')
                        ->join('ac_customers','ac_customers.consultant','timesheet_weekly.emp_id')
                        ->select('timesheet_weekly.*','ac_customers.consultant as user_id','ac_customers.name as name')
                        ->where('timesheet_weekly.year_value',$year)
                        ->where('timesheet_weekly.week_value',$week)
                        ->where('timesheet_weekly.person_type',$person_type) 
                        ->where('ac_customers.company_id',session('company_id'));
                        if($employee_id){
                            $query = $query->where('timesheet_weekly.emp_id',$employee_id);
                        }
                        if($status==2){
                        $query =    $query->get();
                        }if($status==1){
                        $query =    $query->where('timesheet_weekly.approved_status',1)->get();
                        }if($status==0){
                        $query =    $query->where('timesheet_weekly.approved_status',0)->get();
                         }

        return $query;
    }
    public static function employees_month_data($year,$month,$status){

        $query =  DB::table('timesheet_weekly')
                        ->join('students','students.user_id','timesheet_weekly.emp_id')
                        ->select('timesheet_weekly.*',DB::raw("CONCAT(students.first_name,' ',ifnull(students.middle_name,''),' ',ifnull(students.last_name,'')) as name"),'students.user_id as user_id')
                        ->where('timesheet_weekly.year_value',$year)
                        ->where('timesheet_weekly.month_value',$month);
                
                        if($status==2){
                            $query =    $query->orderBy('week_value','desc')->get()->groupby('year_value','month_value','emp_id');
                        }if($status==1){
                            $query =    $query->where('timesheet_weekly.approved_status',1)->get();
                        }if($status==0){
                            $query =    $query->where('timesheet_weekly.approved_status',0)->get();
                        }
        return $query;
    }
    public static function month_sheet($month,$year){

        $month_sheet = DB::table('timesheet_yearly')
                            ->select('timesheet_yearly.01 as day01','timesheet_yearly.02 as day02',
                                'timesheet_yearly.03 as day03','timesheet_yearly.04 as day04',
                                'timesheet_yearly.05 as day05','timesheet_yearly.06 as day06',
                                'timesheet_yearly.07 as day07','timesheet_yearly.08 as day08',
                                'timesheet_yearly.09 as day09','timesheet_yearly.10 as day10',
                                'timesheet_yearly.11 as day11','timesheet_yearly.12 as day12',
                                'timesheet_yearly.13 as day13','timesheet_yearly.14 as day14',
                                'timesheet_yearly.15 as day15','timesheet_yearly.16 as day16',
                                'timesheet_yearly.17 as day17','timesheet_yearly.18 as day18',
                                'timesheet_yearly.19 as day19','timesheet_yearly.20 as day20',
                                'timesheet_yearly.21 as day21','timesheet_yearly.22 as day22',
                                'timesheet_yearly.23 as day23','timesheet_yearly.24 as day24',
                                'timesheet_yearly.25 as day25','timesheet_yearly.26 as day26',
                                'timesheet_yearly.27 as day27','timesheet_yearly.28 as day28',
                                'timesheet_yearly.29 as day29','timesheet_yearly.30 as day30',
                                'timesheet_yearly.31 as day31','timesheet_yearly.month_value',
                                'timesheet_yearly.year_value')
                               ->where('month_value',$month)
                               ->where('year_value',$year)  
                                
                               ->first();
   

        return $month_sheet;
    } 
    public static function month_sheet_single($month,$year,$emp_id){

         $value = explode('-',$emp_id);

        if(count($value) > 1){
            $emp_id = $value[0];
            $person_type = $value[1];
        }else{
            $emp_id = $emp_id; 
            $person_type = 'consultant';
        }

        $month_sheet = DB::table('timesheet_yearly')
                            ->select('timesheet_yearly.01 as day01','timesheet_yearly.02 as day02',
                                'timesheet_yearly.03 as day03','timesheet_yearly.04 as day04',
                                'timesheet_yearly.05 as day05','timesheet_yearly.06 as day06',
                                'timesheet_yearly.07 as day07','timesheet_yearly.08 as day08',
                                'timesheet_yearly.09 as day09','timesheet_yearly.10 as day10',
                                'timesheet_yearly.11 as day11','timesheet_yearly.12 as day12',
                                'timesheet_yearly.13 as day13','timesheet_yearly.14 as day14',
                                'timesheet_yearly.15 as day15','timesheet_yearly.16 as day16',
                                'timesheet_yearly.17 as day17','timesheet_yearly.18 as day18',
                                'timesheet_yearly.19 as day19','timesheet_yearly.20 as day20',
                                'timesheet_yearly.21 as day21','timesheet_yearly.22 as day22',
                                'timesheet_yearly.23 as day23','timesheet_yearly.24 as day24',
                                'timesheet_yearly.25 as day25','timesheet_yearly.26 as day26',
                                'timesheet_yearly.27 as day27','timesheet_yearly.28 as day28',
                                'timesheet_yearly.29 as day29','timesheet_yearly.30 as day30',
                                'timesheet_yearly.31 as day31','timesheet_yearly.month_value',
                                'timesheet_yearly.year_value')
                               ->where('month_value',$month)
                               ->where('year_value',$year)  
                               ->where('emp_id',$emp_id) 
                               ->where('person_type',$person_type) 
                               ->first();
   

        return $month_sheet;
    }
}
