<?php


namespace App\Helpers;

use Request;

use Auth;
use DB;
use Phanna\Converter\KhmerDatetime;
class get_att_for_old

{
public static function IsNullOrEmptyString($str){
    return (!isset($str) || trim($str) === '' || $str === null);
}

public static function get_not_sun_amount($year,$month){
    $first_day = "{$year}-{$month}-01";
    $last_day = getOne("select last_day(DATE_ADD('{$first_day}',INTERVAL 7 DAY))");
    $arr_date = \App\Helpers\IDate::createDateRange($first_day,$last_day);
    $total = 0;
    foreach ($arr_date as $_date_loop) {
        $s_day = getOne("SELECT DAYNAME ('".$_date_loop."')");
        if($s_day != 'Sunday'){
            $total = $total +1;
        }
    }
    return $total;
}


public static function calculateTax($salary,$exchange_rate=4000){
        $exchange_rate = App\Helpers\Helper::getTaxExRate();

        $sal_KH = $salary * $exchange_rate ;
        $re = 0;
        if($sal_KH <=1200000){
            $re = 0;
        }elseif ($sal_KH >1200000 && $sal_KH <= 2000000){
            $re = $sal_KH * 0.05 - 60000 ;
        }elseif ($sal_KH >2000000 && $sal_KH <= 8500000){
            $re = $sal_KH* 0.1 - 160000;
        }elseif ($sal_KH >8500000 && $sal_KH <= 12500000){
            $re = $sal_KH* 0.15 - 585000;
        }else{
            $re = $sal_KH* 0.2 - 1210000;
        }
        return number_format($re / $exchange_rate,2);
}
public static function nssf($salary,$exchange_rate=4000){

    $exchange_rate = App\Helpers\Helper::getNSSFRate();
    $bas_salary_nssf = ($salary) * $exchange_rate ;
    $nssf_bas = 0;
    $hearth = 0;
    if($bas_salary_nssf <200000){
        $nssf_bas  = 1600;
        $hearth  = 5200;
    }else if($bas_salary_nssf >= 200001 && $bas_salary_nssf <= 250000 ){
        $nssf_bas  = 1800;
        $hearth  = 5850;
    }else if($bas_salary_nssf >= 250001 && $bas_salary_nssf <= 300000){
        $nssf_bas = 2200;
        $hearth  = 7150;
    } else if ($bas_salary_nssf >= 300001 && $bas_salary_nssf <= 350000) {
        $nssf_bas = 2600;
        $hearth  = 8450;
    } else if ($bas_salary_nssf >= 350001 && $bas_salary_nssf <= 400000) {
        $nssf_bas = 3000;
        $hearth  = 9750;
    } else if ($bas_salary_nssf >= 400001 && $bas_salary_nssf <= 450000) {
        $nssf_bas = 3400;
        $hearth  = 11050;
    } else if ($bas_salary_nssf >= 450001 && $bas_salary_nssf <= 500000) {
        $nssf_bas = 3800;
        $hearth  = 12350;
    } else if ($bas_salary_nssf >= 500001 && $bas_salary_nssf <= 550000) {
        $nssf_bas = 4200;
        $hearth  = 13650;
    } else if ($bas_salary_nssf >= 550001 && $bas_salary_nssf <= 600000) {
        $nssf_bas = 4600;
        $hearth  = 14950;
    } else if ($bas_salary_nssf >= 600001 && $bas_salary_nssf <= 650000) {
        $nssf_bas = 5000;
        $hearth  = 16250;
    } else if ($bas_salary_nssf >= 650001 && $bas_salary_nssf <= 700000) {
        $nssf_bas = 5400;
        $hearth  = 17550;
    } else if ($bas_salary_nssf >= 700001 && $bas_salary_nssf <= 750000) {
        $nssf_bas = 5800;
        $hearth  = 18850;
    } else if ($bas_salary_nssf >= 750001 && $bas_salary_nssf <= 800000) {
        $nssf_bas = 6200;
        $hearth  = 20150;
    } else if ($bas_salary_nssf >= 800001 && $bas_salary_nssf <= 850000) {
        $nssf_bas = 6600;
        $hearth  = 21450;
    } else if ($bas_salary_nssf >= 850001 && $bas_salary_nssf <= 900000) {
        $nssf_bas = 7000;
        $hearth  = 22750;
    } else if ($bas_salary_nssf >= 900001 && $bas_salary_nssf <= 950000) {
        $nssf_bas = 7400;
        $hearth  = 24050;
    } else if ($bas_salary_nssf >= 950001 && $bas_salary_nssf <= 1000000) {
        $nssf_bas = 7800;
        $hearth  = 25350;
    } else if ($bas_salary_nssf >= 1000001 && $bas_salary_nssf <= 1050000) {
        $hearth  = 26650;
        $nssf_bas = 8200;
    } else if ($bas_salary_nssf >= 1050001 && $bas_salary_nssf <= 1100000) {
        $hearth = 27950;
        $nssf_bas = 8600;
    } else if ($bas_salary_nssf >= 1100001 && $bas_salary_nssf <= 1150000) {
        $hearth = 29250;
        $nssf_bas = 9000;
    } else if ($bas_salary_nssf >= 1150001 && $bas_salary_nssf <= 1200000) {
        $hearth = 30550;
        $nssf_bas = 9400;
    } else if ($bas_salary_nssf >= 1200001 ) {
        $hearth = 31200;
        $nssf_bas = 9600;
    }
    $nssf_bas= $nssf_bas>0?$nssf_bas:0;
    $hearth= $hearth>0?$hearth:0;
    return [$nssf_bas,$hearth] ;
}


public static function getIncomeTaxRate($salary,$exchange_rate=4000){
    $exchange_rate = App\Helpers\Helper::getTaxExRate();

    $sal_KH = $salary * $exchange_rate ;
    $re = 0;
    if($sal_KH <=1200000){
        $re = 0;
    }elseif ($sal_KH >1200000 && $sal_KH <= 2000000){
        $re = 5 ;
    }elseif ($sal_KH >2000000 && $sal_KH <= 8500000){
        $re = 10;
    }elseif ($sal_KH >8500000 && $sal_KH <= 12500000) {
        $re = 15;
    }else{
        $re = 20;
    }
    return $re;
}
public static function getIncomeTaxDeduction($salary,$exchange_rate=4000){
    $exchange_rate = App\Helpers\Helper::getTaxExRate();

    $sal_KH = $salary * $exchange_rate ;
    $re = 0;
    if($sal_KH <=1200000){
        $re = 0;
    }elseif ($sal_KH >1200000 && $sal_KH <= 2000000){
        $re = 60000 ;
    }elseif ($sal_KH >2000000 && $sal_KH <= 8500000){
        $re = 160000;
    }elseif ($sal_KH >8500000 && $sal_KH <= 12500000) {
        $re = 585000;
    }else{
        $re = 1210000;
    }
    return $re;
}
public static function get_seniority($num_year){
    if($num_year>10){
        $seniority = number_format(11,2);
    }else if($num_year>9){
        $seniority = number_format(10,2);
    }else if($num_year>8){
        $seniority = number_format(9,2);
    }else if($num_year>7){
        $seniority = number_format(8,2);
    }else if($num_year>6){
        $seniority = number_format(7,2);
    }else if($num_year>5){
        $seniority = number_format(6,2);
    }else if($num_year>4){
        $seniority = number_format(5,2);
    }else if($num_year>3){
        $seniority = number_format(4,2);
    }else if($num_year>2){
        $seniority = number_format(3,2);
    }else if($num_year>1){
        $seniority = number_format(2,2);
    }else{
        $seniority = 0;
    }
    return $seniority;
}

public static function getOpPolicy($op,$pol_select = 0){
    $p = '<option value="0"> - </option>';
    if(is_array($op) || is_object($op)){
        if(count($op)>0){
            foreach ($op as $row){
                $s = $row->id == $pol_select?' selected="selected" ' :'';
                $p .= '<option '.$s.' value="'.$row->id.'">'.$row->id.'.'.$row->policy_name.'</option>';
            }
        }
    }

    return $p;

}

public static function runSQL($query) {
   return DB::statement($query);
}

public static function getTable($query) {
    return collect(DB::select($query))->map(function ($x){return (array)$x;})->toArray();
}

public static function getOneRow($sql) {
    $getTable = getTable($sql);
    if(count($getTable)>0){
        return $getTable[0];
    }else{
        return [];
    }

}

public static function getOne($sql) {
    $getOneRow = getOneRow($sql);
    if(count($getOneRow)>0){
        return reset($getOneRow);
    }else{
        return '';
    }
}

public static function getOneRowA($sql) {
   return getOneRow($sql);
}
//================================

public static function getDatesFromRange($strDateFrom,$strDateTo)
{
    // takes two dates formatted as YYYY-MM-DD and creates an
    // inclusive array of the dates between the from and to dates.

    // could test validity of dates here but I'm already doing
    // that in the main script

    $aryRange=array();

    $iDateFrom=mktime(1,0,0,substr($strDateFrom,5,2),     substr($strDateFrom,8,2),substr($strDateFrom,0,4));
    $iDateTo=mktime(1,0,0,substr($strDateTo,5,2),     substr($strDateTo,8,2),substr($strDateTo,0,4));

    if ($iDateTo>=$iDateFrom)
    {
        array_push($aryRange,date('Y-m-d',$iDateFrom)); // first entry
        while ($iDateFrom<$iDateTo)
        {
            $iDateFrom+=86400; // add 24 hours
            array_push($aryRange,date('Y-m-d',$iDateFrom));
        }
    }
    return $aryRange;
}

//========================================================
//========================================================
public static function getTimDiffMinute2($_ftime,$_ttime){
   // echo ("SELECT TIME_TO_SEC(timediff('{$_ttime}', '{$_ftime}'))/60 as t </br>");
    //return getOne("SELECT TIME_TO_SEC(timediff('{$_ttime}', '{$_ftime}'))/60 as t");
    if(dateCompP($_ftime,$_ttime)) {
        $ftime = getCorrectTimIn($_ftime);
        $ttime = getCorrectTimeOut($_ttime);
    }else {
       $ftime = getCorrectTimeOut($_ftime);
       $ttime = getCorrectTimIn($_ttime);
   }
    return getOne("SELECT TIMESTAMPDIFF(MINUTE,'{$ftime}','{$ttime}'); ");
}

public static function getTotalTimeWork($_ftime,$_ttime){
// SELECT HOUR(NOW()),MINUTE(NOW())
    $DATE_in = getOne("SELECT DATE('{$_ftime}') as t");
    $HOUR_in = getOne("SELECT HOUR('{$_ftime}') as t");
    $MINUTE_in = getOne("SELECT MINUTE('{$_ftime}') as t");

    $in_min = 0;
    $in_h = 0;
    if($MINUTE_in<=8){
        $in_min = 0;
        $in_h = $HOUR_in;
    }else if($MINUTE_in >8 && $MINUTE_in <= 30){
        $in_min = 30;
        $in_h = $HOUR_in;
    }else if($MINUTE_in>30 && $MINUTE_in <60){
        $in_min = 0;
        $in_h = $HOUR_in + 1;
    }

    $_ftime = "{$DATE_in} {$in_h}:{$in_min}:00";
    //====================
    //====================
    $DATE_out = getOne("SELECT DATE ('{$_ttime}') as t");
    $HOUR_out = getOne("SELECT HOUR ('{$_ttime}') as t");
    $MINUTE_out = getOne("SELECT MINUTE ('{$_ttime}') as t");

    $out_min = 0;
    $out_h = 0;
    if($MINUTE_out < 30){
        $out_min = 0;
        $out_h = $HOUR_out;
    }else if($MINUTE_out>=30 && $MINUTE_out <60){
        $out_min = 30;
        $out_h = $HOUR_out;
    }
    $_ttime = "{$DATE_out} {$out_h}:{$out_min}:00";


    return getOne("SELECT TIME_TO_SEC(timediff('{$_ttime}', '{$_ftime}'))/60 as t");
}
public static function getTimDiffMinute($_ftime,$_ttime){
    // echo ("SELECT TIME_TO_SEC(timediff('{$_ttime}', '{$_ftime}'))/60 as t </br>");
    return getOne("SELECT TIME_TO_SEC(timediff('{$_ttime}', '{$_ftime}'))/60 as t");
    /* if(dateCompP($_ftime,$_ttime)) {
         $ftime = getCorrectTimIn($_ftime);
         $ttime = getCorrectTimeOut($_ttime);
     }else {
        $ftime = getCorrectTimeOut($_ftime);
        $ttime = getCorrectTimIn($_ttime);
    }
     return getOne("SELECT TIMESTAMPDIFF(MINUTE,'{$ftime}','{$ttime}'); ");*/
}

public static function getDateDiffMinute($st_date,$end_date){
    return getOne("SELECT DATEDIFF('{$st_date}', '{$end_date}')");

}

public static function getCorrectTimIn($date){
    //SELECT DATE(NOW()),HOUR(NOW()), MINUTE(NOW());
    $dd = getOne("SELECT DATE('{$date}') as d");
    $hh = getOne("SELECT HOUR('{$date}') as d");
    $mm = getOne("SELECT MINUTE('{$date}') as d");

    if($mm <= 8){
        $hhh = $hh<10?'0'.$hh:$hh;
        return "{$dd} {$hhh}:00:00";
    }else if($mm > 8 && $mm <= 38){

        $hhh = $hh<10?'0'.$hh:$hh;
        return "{$dd} {$hhh}:30:00";
    }else{

        $hh = $hh +1;
        $hhh = $hh<10?'0'.$hh:$hh;
        return "{$dd} {$hhh}:00:00";
    }
}

public static function getCorrectTimeOut($date){
    //SELECT DATE(NOW()),HOUR(NOW()), MINUTE(NOW());
    $dd = getOne("SELECT DATE('{$date}') as d");
    $hh = getOne("SELECT HOUR('{$date}') as d");
    $mm = getOne("SELECT MINUTE('{$date}') as d");

    if($mm < 30){
        $hhh = $hh<10?'0'.$hh:$hh;
        return "{$dd} {$hhh}:00:00";
    }else{
        $hhh = $hh<10?'0'.$hh:$hh;
        return "{$dd} {$hhh}:30:00";
    }
}

public static function dateCompP($fd,$td){
    $c = getOne("SELECT ('{$fd}' < '{$td}') as a");
    if($c > 0){
        return true;
    }else{
        return false;
    }
}

public static function dateAdd($date,$n){
    return getOne("SELECT DATE_ADD('{$date}', INTERVAL {$n} DAY);");
}

//=======================================================
public static function getRealMinute($l){
    return $l ;
    $l = $l +8;
    //$l = $l;
    //dd($l)
    $n = 0;
    if($l>=9 && $l<30){
        $n = 0;
    }elseif($l>=30 && $l<60){
        $n = 30;
    }elseif($l>=60 && $l<90){
        $n = 60;
    }elseif($l>=90 && $l<120){
        $n = 90;
    }elseif($l>=120 && $l<150){
        $n = 120;
    }elseif($l>=150 && $l<180){
        $n = 150;
    }elseif($l>=180 && $l<210){
        $n = 180;
    }elseif($l>=210 && $l<240){
        $n = 210;
    }elseif($l>=240 && $l<270){
        $n = 240;
    }elseif($l>=270 && $l<300){
        $n = 270;
    }elseif($l>=300 && $l<330){
        $n = 300;
    }elseif($l>=338 && $l<360){
        $n = 330;
    }elseif($l>=360 && $l<390){
        $n = 360;
    }elseif($l>=390 && $l<420){
        $n = 390;
    }elseif($l>=420 && $l<450){
        $n = 420;
    }elseif($l>=450 && $l<480){
        $n = 450;
    }elseif($l>=480 && $l<510){
        $n = 480;
    }elseif($l>=510 && $l<540){
        $n = 510;
    }elseif($l>=540 && $l<570){
        $n = 540;
    }elseif($l>=570 && $l<600){
        $n = 570;
    }elseif($l>=600 && $l<630){
        $n = 600;
    }elseif($l>=630 && $l<660){
        $n = 630;
    }elseif($l>=660 && $l<690){
        $n = 660;
    }elseif($l>=690 && $l<720){
        $n = 690;
    }elseif($l>=720 && $l<750){
        $n = 720;
    }elseif($l>=750 && $l<780){
        $n = 750;
    }elseif($l>=780 && $l<810){
        $n = 780;
    }elseif($l>=810 && $l<840){
        $n = 810;
    }elseif($l>=840 && $l<870){
        $n = 840;
    }elseif($l>=870 && $l<900){
        $n = 870;
    }elseif($l>=900 && $l<930){
        $n = 900;
    }else{
        $n = $l;
    }
    return $n;
}

public static function getRealMinuteOT($l){

   // $l = $l +8;

    $n = 0;
    if($l<30){
        $n = 0;
    }elseif($l>=30 && $l<60){
        $n = 30;
    }elseif($l>=60 && $l<90){
        $n = 60;
    }elseif($l>=90 && $l<120){
        $n = 90;
    }elseif($l>=120 && $l<150){
        $n = 120;
    }elseif($l>=150 && $l<180){
        $n = 150;
    }elseif($l>=180 && $l<210){
        $n = 180;
    }elseif($l>=210 && $l<240){
        $n = 210;
    }elseif($l>=240 && $l<270){
        $n = 240;
    }elseif($l>=270 && $l<300){
        $n = 270;
    }elseif($l>=300 && $l<330){
        $n = 300;
    }elseif($l>=338 && $l<360){
        $n = 330;
    }elseif($l>=360 && $l<390){
        $n = 360;
    }elseif($l>=390 && $l<420){
        $n = 390;
    }elseif($l>=420 && $l<450){
        $n = 420;
    }elseif($l>=450 && $l<480){
        $n = 450;
    }elseif($l>=480 && $l<510){
        $n = 480;
    }elseif($l>=510 && $l<540){
        $n = 510;
    }elseif($l>=540 && $l<570){
        $n = 540;
    }elseif($l>=570 && $l<600){
        $n = 570;
    }elseif($l>=600 && $l<630){
        $n = 600;
    }elseif($l>=630 && $l<660){
        $n = 630;
    }elseif($l>=660 && $l<690){
        $n = 660;
    }elseif($l>=690 && $l<720){
        $n = 690;
    }elseif($l>=720 && $l<750){
        $n = 720;
    }elseif($l>=750 && $l<780){
        $n = 750;
    }elseif($l>=780 && $l<810){
        $n = 780;
    }elseif($l>=810 && $l<840){
        $n = 810;
    }elseif($l>=840 && $l<870){
        $n = 840;
    }elseif($l>=870 && $l<900){
        $n = 870;
    }elseif($l>=900 && $l<930){
        $n = 900;
    }else{
        $n = $l;
    }

    return $n;
}

public static function getRealLate($l){
    $late = 0;

    if($l <= 8){
        $late = 0;
    }elseif ($l > 8 && $l <= 38){
        $late = 30;
    }elseif ($l > 38 && $l <= 68){
        $late = 60;
    }elseif ($l > 68 && $l <= 98){
        $late = 90;
    }
    elseif ($l > 98 && $l <= 128){
        $late = 120;
    }
    elseif ($l > 128 && $l <= 158){
        $late = 150;
    }elseif ($l > 158 && $l <= 188){
        $late = 180;
    }elseif ($l > 188 && $l <= 218){
        $late = 210;
    }elseif ($l > 218 && $l <= 248){
        $late = 240;
    }elseif ($l > 248 && $l <= 278){
        $late = 270;
    }elseif ($l > 278 && $l <= 348){
        $late = 340;
    }elseif ($l > 348 && $l <= 378){
        $late = 370;
    }


    return $late;

}


public static function get_checkin_Ot($getTable,$ot,$date,$is_night=0){

    //dd($ot);
    if(count($ot)>0){
        $_em_ot_checkin = "";
        foreach ($ot as $ot_array){
            if(count($getTable)>0){

                $_ot_out = $date .' '.  $ot_array['out'];
                $_ot_in =  $date .' '. $ot_array['in'];

                $c = getTimDiffMinute($_ot_in,$_ot_out);
                //  $is_night = isset($pol_detail['is_night'])?$pol_detail['is_night']:0;
                if($is_night ==1){
                    $_ot_out =dateAdd( $date,1). ' ' . $ot_array['out'];
                }


                $_start_in =  $date .' '.  $ot_array['start_in'];
                $_end_in =  $date .' '.  $ot_array['end_in'];

                $c = getTimDiffMinute($_start_in,$_end_in);
                if($is_night==1){
                    $_ot_out =dateAdd( $date,1). ' ' . $ot_array['end_in'];
                }

                $c_ot_in = $date .' '. '00:00';
                foreach ($getTable as $row){
                    $__fcheck = $row['fcheck'];

                    $_test1 = getTimDiffMinute($_start_in,$__fcheck);
                    $_test2 = getTimDiffMinute($_end_in,$__fcheck);


                    if($_test1 >= 0 && $_test2 <= 0 ){

                        $_em_ot_checkin = $__fcheck;
                        break;
                    }

                }
            }

            if($_em_ot_checkin != ''){
                break;
            }

        }

        return $_em_ot_checkin;

    }
}


public static function convertime ($seconds){
    return getOne("SELECT SEC_TO_TIME({$seconds}) as t");
}

public static function get_r_time($dt){
    $s = getOneRow("SELECT MINUTE('{$dt}') as m, DATE_ADD('{$dt}', INTERVAL - MINUTE('{$dt}') MINUTE) as d;");
    $m = $s['m'];
    $d = $s['d'];

    $re = $dt;
    if($m >8 && $m <30){
        $re = getOne("SELECT DATE_ADD('{$d}', INTERVAL + 30 MINUTE) as d;");
    }

    if($m >=30 && $m <59){
        $re = getOne("SELECT DATE_ADD('{$d}', INTERVAL + 60 MINUTE) as d;");
    }

    return $re;
}
public static function get_num_permission ($from_date, $to_date,$empcode){
    $a =  getOne("SELECT
        COUNT(isAmount) as cut_sal
    FROM
        em_vacation_accrual_detail
        WHERE empcode = '{$empcode}' AND abs_date BETWEEN '{$from_date}' AND '{$to_date}'
        AND `isHalfDay` = 0
        ")-0;

    $b =  getOne("SELECT
        COUNT(isAmount)/2 as cut_sal
    FROM
        em_vacation_accrual_detail
        WHERE empcode = '{$empcode}' AND abs_date BETWEEN '{$from_date}' AND '{$to_date}'
        AND `isHalfDay` = 1
        ")-0;

    return $a + $b;
}
}

