<?php

namespace App\Helpers;

use App\HrExchangeRateModel;
use App\HrOvertimeRequestModel;
use App\Model\Hr\HrHolidayModel;
use App\TimeAttendanceModel;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;


class IDate

{

    public static function dateDiff($f_date, $t_date)
    {
        $sql = "SELECT DATEDIFF('{$t_date}', '{$f_date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }

    public static function dateTimeDiff($f_date, $t_date)
    {
        $sql = "SELECT TIMESTAMPDIFF(MINUTE,'{$f_date}', '{$t_date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }

    public static function dateDiffFromNow($date)
    {
        $sql = "SELECT DATEDIFF('{$date}', now()) as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }

    
    public static function getDayName($date)
    {
        $sql = "SELECT DAYNAME('{$date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }


    public static function getYear($date)
    {
        $sql = "SELECT YEAR('{$date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }

    public static function getMonth($date)
    {
        $sql = "SELECT MONTH('{$date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }

    public static function getLastDayMonth($date)
    {
        $sql = "SELECT LAST_DAY('{$date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }

    public static function getDayFullMonth($date)
    {
        $sql = "SELECT DAY(LAST_DAY('{$date}')) as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return 0;
        }
    }

    public static function getFirstYear($date)
    {
        $sql = "SELECT year('{$date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d . "/1/1";
        } else {
            return null;
        }
    }

    public static function getMonthName($date)
    {
        $sql = "SELECT MONTHNAME('{$date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }

    public static function getMonthNameS($date)
    {
        $sql = "SELECT DATE_FORMAT('{$date}', '%b-%y')  as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }

    public static function getLastYear($date)
    {
        $sql = "SELECT year('{$date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d . "/12/31";
        } else {
            return null;
        }
    }


    public static function getDayNameKH($date)
    {
        $days = array(
            'sunday' => 'អាទិត្យ',
            'monday' => 'ចន្ទ',
            'tuesday' => 'អង្គារ',
            'wednesday' => 'ពុធ',
            'thursday' => 'ព្រហស្បតិ៍',
            'friday' => 'សុក្រ',
            'saturday' => 'សៅរ៍'
        );

        $sql = "SELECT DAYNAME('{$date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $days[strtolower($d[0]->d)];
        } else {
            return null;
        }
    }
    public static function getMonthNameKH($date)
    {
        $days = array(
            'january' => 'មករា',
            'february' => 'កុម្ភះ',
            'march' => 'មីនា',
            'april' => 'មេសា',
            'may' => 'ឧសភា',
            'june' => 'មិថុនា',
            'july' => 'កក្កដា',
            'august' => 'សីហា',
            'september' => 'កញ្ញា',
            'october' => 'តុលា',
            'november' => 'វិច្ឆិកា',
            'december' => 'ធ្នូ'
        );

        $sql = "SELECT MONTHNAME('{$date}') as m";
        $m = DB::select($sql);
        if (count($m) > 0) {
            return $days[strtolower($m[0]->m)];
        } else {
            return null;
        }
    }

    public static function dateYMD($date)
    {
        if($date != ""){
            $d = Date('Y-m-d', strtotime($date));
            return $d;
        }else{
            return "";
        }
        
    }

    public static function createDateRange($startDate, $endDate, $format = "Y-m-d")
    {
        $begin = new \DateTime($startDate);

        $_end = self::dateAdd($endDate, UnitDay::DAY, 1);

        $end = new \DateTime($_end);

        $interval = new \DateInterval('P1D'); // 1 Day
        $dateRange = new \DatePeriod($begin, $interval, $end);
        $range = [];
        foreach ($dateRange as $date) {
            if ($format == null) {
                $range[] = $date;
            } else {
                $range[] = $date->format($format);
            }

        }
        return $range;
    }
    
    public static function dateAdd($date, $unit, $num_unit)
    {
        $dates  = date('Y-m-d', strtotime($date));
        $sql    = "SELECT DATE_ADD('{$dates}', INTERVAL {$num_unit} {$unit}) as d";
        $d      = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }

    public static function getHol($date, $sun_day)
    {
        
        $hol        = HrHolidayModel::get();
        $att_date   = \Carbon\Carbon::parse($date)->format('Y-m-d');

        $styles_hol = '';
        foreach ($hol as $row_hol) {
            $start_date = \Carbon\Carbon::parse($row_hol->from_date)->format('Y-m-d');
            $end_date   = \Carbon\Carbon::parse($row_hol->to_date)->format('Y-m-d');

            if ($att_date >= $start_date AND $att_date <= $end_date AND $sun_day != 'Sunday') {
                $styles_hol = 'color:green;';
            }
        }

        return $styles_hol;
    }

    public static function getHols($date, $sun_day)
    {
        //I'm not write more don't use this function.
        $hol        = HrHolidayModel::get();
        $att_date   = \Carbon\Carbon::parse($date)->format('Y-m-d');

        $styles_hol = '';
        foreach ($hol as $row_hol) 
        {
            $start_date = \Carbon\Carbon::parse($row_hol->start_date)->format('Y-m-d');
            $end_date   = \Carbon\Carbon::parse($row_hol->end_date)->format('Y-m-d');

            if ($att_date >= $start_date AND $att_date <= $end_date AND $sun_day != 'Sunday') 
            {
                $styles_hol = 'color:green;';
            }
        }

        return $styles_hol;
    }
    public static function createDateRangeIncHoliday($startDate, $endDate, $format = "Y-m-d")
    {
        // not write more don't use
        $_range = self::createDateRange($startDate, $endDate, null);
        $range = [];

        if (count($_range) > 0) {
            foreach ($_range as $date) {
                $type = 'n';

                if ($date->format('D') == 'Sun' || $date->format('D') == 'Sat') {
                    $type = 'w';
                } else {

                    $m = HrHolidayModel::where('end_date', '>=', $date->format('Y-m-d'))
                        ->where('start_date', '<=', $date->format('Y-m-d'))
                        ->orderBy('end_date', 'DESC')
                        ->first();

                    if ($m != null) {
                        $type = 'h';
                    }

                }
                $range[] = [
                    'date' => $format == null ? $date : $date->format($format),
                    'type' => $type
                ];
            }
        }

        return $range;
    }

    public static function createDateRangeIncHoliday_Sat_Sun($startDate, $endDate, $format = "Y-m-d")
    {
        // not write more don't use
        $_range = self::createDateRange($startDate, $endDate, null);
        $range = [];

        if (count($_range) > 0) {
            foreach ($_range as $date) {

                $type = 'n';

                if ($date->format('D') == 'Sun' || $date->formar('D') == "Sat") {
                    $type = 'w';
                } else {

                    $m = HrHolidayModel::where('end_date', '>=', $date->format('Y-m-d'))
                        ->where('start_date', '<=', $date->format('Y-m-d'))
                        ->orderBy('end_date', 'DESC')
                        ->first();

                    if ($m != null) {
                        $type = 'h';
                    }

                }
                $range[] = [
                    'date' => $format == null ? $date : $date->format($format),
                    'type' => $type
                ];
            }
        }

        return $range;
    }

    public static function getHolidayOnly($startDate, $endDate, $format = "Y-m-d")
    {
        // not write more don't use
        $_range = self::createDateRange($startDate, $endDate, null);
        $range = [];
        $hol_amt = 0;
        if (count($_range) > 0) {

            foreach ($_range as $date) {

                $type = 'n';

                if ($date->format('D') == 'Sun') {
                    $type = 'w';
                } else {

                    $m = HrHolidayModel::where('end_date', '>=', $date->format('Y-m-d'))
                        ->where('start_date', '<=', $date->format('Y-m-d'))
                        ->orderBy('end_date', 'DESC')
                        ->first();

                    if ($m != null) {
                        $type = 'h';
                        $hol_amt++;
                    }

                }
                $range[] = [
                    'date' => $format == null ? $date : $date->format($format),
                    'type' => $type
                ];
            }
        }

        return $hol_amt;
    }

    public static function getTotalMonth($from_date, $to_date)
    {
        // SELECT TIMESTAMPDIFF(MONTH, '2015-12-20', '2015-12-25') + 1 as t_m
        $sql = "SELECT TIMESTAMPDIFF(MONTH,'{$from_date}','{$to_date}') + 1 as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }

    public static function nextMonth($month, $num, $date)
    {
        $sql = "SELECT DATEADD({$month}, {$num}, {$date}) AS d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d;
        } else {
            return null;
        }
    }

    public static function dateNext($date, $num, $exDaya = [], $ifirst = 0)
    {
        // not write more don't use
        if (count($exDaya) == 0) {
            $exDaya = ['saturday', 'sunday'];
        }

        if ($ifirst > 0) {
            $d = self::dateAdd($date, UnitDay::DAY, $num);
        } else {
            $d = self::dateAdd($date, UnitDay::MONTH, $num);
        }

        $m = HrHolidayModel::where('end_date', '>=', $d)
            ->where('start_date', '<=', $d)
            ->orderBy('end_date', 'DESC')
            ->first();

        if ($m != null) {
            $d = self::dateNext($m->end_date, 1, $exDaya, 10);
        }

        $dn = strtolower(self::getDayName($d));

        if (in_array($dn, $exDaya)) {
            $d = self::dateNext($d, 1, $exDaya, 10);
        }

        return Carbon::parse($d);
    }

    public static function dateComp($f_date, $t_date)
    {
        $sql = "SELECT ('{$f_date}' <= '{$t_date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d > 0;
        } else {
            return false;
        }
    }

    public static function dateCompNe($f_date, $t_date)
    {
        $sql = "SELECT ('{$f_date}' < '{$t_date}') as d";
        $d = DB::select($sql);
        if (count($d) > 0) {
            return $d[0]->d > 0;
        } else {
            return false;
        }
    }

    public static function generateTimeAttend($emp_id = NULL, $date, $in, $out, $total)
    {
        // not write more don't use
        if (!empty($date)) {
            $m = TimeAttendanceModel::where('emp_id', $emp_id)
                ->whereRaw(" (date(cin) = '{$date}' OR  date(cout) = '{$date}')")
                ->first();

            if ($m == null) {
                $m = new TimeAttendanceModel();
            }

            $m->emp_id      = $emp_id;
            $m->att_date    = $date;
            if ($in) {
                $m->cin     = $in;
            }
            if ($out) {
                $m->cout    = $out;
            }
            if ($in && $out) {
                $m->total       = $total;
                $m->bot         = 0;// ($bot>=60?$bot:0);
                $m->ot          = 0;//($ot>=60?$ot:0);
                $m->total_ot    = 0;
            }

            if ($in == NULL || $out == NULL) {
                $m->missing = 1;
            }
            $m->save();
        }
    }

}
class UnitDay
{
    const DAY = 'DAY';
    const MONTH = 'MONTH';
    const QUARTER = 'QUARTER';
    const YEAR = 'YEAR';
}