<?php

class Attendence {

    protected $info = array();
    private $db;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
    }

    public function setData($data) {
        $this->info = $data;

        return $this;
    }

    public function excelDatetoPHPDate($excel_date_time, $format = 'Y-m-d H:i:s') {
        $tmp_timezone = date_default_timezone_get();
        date_default_timezone_set('GMT');   //or it will add zone hours
        //25569 date diff between January 1, 1900 [excel] and 1970 [unix] (including 19 leap years)
        //86400 seconds per day
        //        $format_date_time = date($format, (($excel_date_time - 25569) * 86400));
        $format_date_time = date($format, $this->excelToPhpConvert($excel_date_time));
        date_default_timezone_set($tmp_timezone);
        return $format_date_time;
    }

    public function excelToPhpConvert($dateValue, $format = 'Y-m-d H:i:s') {
        //PHPExcel_Shared_Date::ExcelToPHP
        //        if (self::$_excelBaseDate == self::CALENDAR_WINDOWS_1900) {
        $my_excelBaseDate = 25569;
        //	Adjust for the spurious 29-Feb-1900 (Day 60)
        if ($dateValue < 60) {
            --$my_excelBaseDate;
        }
        //        } else {
        //            $my_excelBaseDate = 24107;
        //        }
        // Perform conversion
        if ($dateValue >= 1) {
            $utcDays = $dateValue - $my_excelBaseDate;
            $returnValue = round($utcDays * 86400);
            if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
                $returnValue = (integer) $returnValue;
            }
        } else {
            $hours = round($dateValue * 24);
            $mins = round($dateValue * 1440) - round($hours * 60);
            $secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
            $returnValue = (integer) gmmktime($hours, $mins, $secs);
        }
        return $returnValue;
    }

    public function check_php_date_time($date_time, $format = 'date_time') {
        $reg_date = "[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])";
        $reg_time = "(\d{2}):(\d{2}):(\d{2})";
        $reg_time2 = "(\d{2}):(\d{2})";
        if ($format == 'time')
            return (preg_match("/^{$reg_time}$/", $date_time) == 1 || preg_match("/^{$reg_time2}$/", $date_time) == 1);
        if ($format == 'date')
            return (preg_match("/^{$reg_date}$/", $date_time) == 1);
        return (preg_match("/^{$reg_date} {$reg_time}$/", $date_time) == 1 || preg_match("/^{$reg_date} {$reg_time2}$/", $date_time) == 1);
    }

    public function uploadExcelSheet() {
        $error = array();
        require dirname(__FILE__) . '/../PHPExcel/PHPExcel/IOFactory.php';
        $Fileextension = $_FILES['Document_File_Name']['tmp_name'];
        $excel = PHPExcel_IOFactory::load($Fileextension);
        $writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
        $writer->setDelimiter(";");
        $writer->setEnclosure("");
        $csv_file = tmp_dir_path . 'attendance.csv';
        self::convertxlsxtoCSV($Fileextension, $csv_file);
        $columns = json_decode($this->info['columns'], true);
        $tmp_data_arr = array();
        $new_data_arr = array();
        $employees_arr = array();
        $locations_arr = array();
        if (($getfile = fopen($csv_file, "r"))) {
            fgetcsv($getfile);
            $row_no = 1;
            //
            $excel_err_arr = array();
            $query_loc = $this->db->query('Select * from tbl_employee_location');
            while ($location_row = $this->db->assoc($query_loc))
                $locations_arr[$location_row['Location_ID']] = $location_row['Location_Name'];
            $employees_work_arr = array();
            $data = array();
            $work_id_indx = array_search('work_id', $columns);
            $date_time_in_indx = array_search('date_time_in', $columns);
            $date_time_out_indx = array_search('date_time_out', $columns);
            $date_out_indx = array_search('date_out', $columns);
            $date_in_indx = array_search('date_in', $columns);
            $time_in_indx = array_search('time_in', $columns);
            $time_out_indx = array_search('time_out', $columns);
            $date_time_indx = array_search('date_time', $columns);
            $location_id_indx = array_search('location_id', $columns);
            $location_name_indx = array_search('location_name', $columns);
            $attendance_type_indx = array_search('attendance_type', $columns);
            $time_in_method = '';
            //            echo $date_in_indx.'=='.$date_out_indx.'=='.$time_in_indx.'=='.$time_out_indx.'==';die();
            //            echo"<pre>";die(print_r($columns));
            if ($date_in_indx !== false && $date_out_indx !== false && $time_in_indx !== false && $time_out_indx !== false)
                $time_in_method = 'date_in_out';
            elseif ($date_time_in_indx !== false && $date_time_out_indx !== false)
                $time_in_method = 'date_time_in_out';
            elseif ($date_time_indx !== false)
                $time_in_method = 'date_time';
            if (empty($time_in_method))
                return CommonLanguage::getMessage('att_template_fields');
            if ($work_id_indx === false)
                return CommonLanguage::getMessage('att_work_id_alert');
            if ($location_id_indx === false && $location_name_indx === false && smc_session()['default_setting']['attendace_import_required_location'])
                return CommonLanguage::getMessage('loc_empty_alert');
            //
            while (($data = fgetcsv($getfile))) {
                $row_no++;
                $excel_date_time_in = '';
                $excel_date_time_out = '';
                $excel_date_time = '';
                if ($time_in_method === 'date_in_out') {
                    if (!empty($data[$date_in_indx]) && !empty($data[$date_out_indx]) && !empty($data[$time_in_indx]) && !empty($data[$time_out_indx])) {
                        if (is_numeric($data[$date_in_indx]) && is_numeric($data[$date_out_indx]) && is_numeric($data[$time_in_indx]) && is_numeric($data[$time_out_indx])) {
                            $excel_date_time_in = $this->excelDatetoPHPDate($data[$date_in_indx], 'Y-m-d') . ' ' . $this->excelDatetoPHPDate($data[$time_in_indx], 'H:i:s');
                            $excel_date_time_out = $this->excelDatetoPHPDate($data[$date_out_indx], 'Y-m-d') . ' ' . $this->excelDatetoPHPDate($data[$time_out_indx], 'H:i:s');
                        } elseif ($this->check_php_date_time($data[$date_in_indx] . ' ' . $data[$time_in_indx]) && $this->check_php_date_time($data[$date_out_indx] . ' ' . $data[$time_out_indx])) {
                            $excel_date_time_in = $data[$date_in_indx] . ' ' . $data[$time_in_indx];
                            $excel_date_time_out = $data[$date_out_indx] . ' ' . $data[$time_out_indx];
                        }
                    }
                } else if ($time_in_method === 'date_time_in_out') {
                    if (!empty($data[$date_time_in_indx]) && !empty($data[$date_time_out_indx])) {
                        if (is_numeric($data[$date_time_in_indx]) && is_numeric($data[$date_time_out_indx])) {
                            $excel_date_time_in = $this->excelDatetoPHPDate($data[$date_time_in_indx]);
                            $excel_date_time_out = $this->excelDatetoPHPDate($data[$date_time_out_indx]);
                        } elseif ($this->check_php_date_time($data[$date_time_in_indx]) && $this->check_php_date_time($data[$date_time_out_indx])) {
                            $excel_date_time_in = $data[$date_time_in_indx];
                            $excel_date_time_out = $data[$date_time_out_indx];
                        }
                    }
                } else if ($time_in_method === 'date_time') {
                    if (!empty($data[$date_time_indx])) {
                        if (is_numeric($data[$date_time_indx]))
                            $excel_date_time = $this->excelDatetoPHPDate($data[$date_time_indx]);
                        elseif ($this->check_php_date_time($data[$date_time_indx]))
                            $excel_date_time = $data[$date_time_indx];
                    }
                }
                $excel_attendance_type = '';
                if ($attendance_type_indx !== false) {
                    if (strpos(strtolower($data[$attendance_type_indx]), 'out') !== false)
                        $excel_attendance_type = 'out';
                    elseif (strpos(strtolower($data[$attendance_type_indx]), 'in') !== false)
                        $excel_attendance_type = 'in';
                }
                //
                if ((empty($excel_date_time_in) || empty($excel_date_time_out)) && empty($excel_date_time)) {
                    $excel_err_arr[$row_no] = CommonLanguage::getMessage('fill_id_time');
                    continue;
                }
                if (empty($data[$work_id_indx])) {
                    $excel_err_arr[$row_no] = CommonLanguage::getMessage('fill_id_time');
                    continue;
                }
                $employee_work_id = $data[$work_id_indx];
                $location_id = '';
                if ($location_name_indx === false)
                    $location_id = !empty($data[$location_id_indx]) && isset($locations_arr[$data[$location_id_indx]]) ? $data[$location_id_indx] : '';
                else
                    $location_id = !empty($data[$location_name_indx]) ? array_search($data[$location_name_indx], $locations_arr) : '';
                if (empty($location_id) && smc_session()['default_setting']['attendace_import_required_location']) {
                    $excel_err_arr[$row_no] = CommonLanguage::getMessage('loc_empty_alert');
                    continue;
                }
                //
                if (!isset($employees_work_arr[$employee_work_id])) {
                    $employees_work_arr[$employee_work_id] = array();
                    $query1 = $this->db->prepare_Stmt('Select Employee_ID, Employee_Name from view_employees where Employee_Work_ID = ?', [&$employee_work_id]);
                    $employee_row = $this->db->assoc($query1);
                    if (!empty($employee_row)) {
                        $employees_work_arr[$employee_work_id] = $employee_row;
                        $employees_arr[$employee_row['Employee_ID']] = $employee_row['Employee_Name'];
                    }
                }
                $employee_id = isset($employees_work_arr[$employee_work_id]['Employee_ID']) ? $employees_work_arr[$employee_work_id]['Employee_ID'] : '';
                if (empty($employee_id))
                    $excel_err_arr[$row_no] = CommonLanguage::getMessage('not_exist_emp');
                if (empty($excel_err_arr[$row_no])) {
                    if (empty($excel_date_time))
                        $new_data_arr[] = array('emp_id' => $employee_id, 'time_in' => $excel_date_time_in, 'time_out' => $excel_date_time_out, 'location_id' => $location_id, 'row_nb' => $row_no);
                    else
                        $tmp_data_arr[] = array($employee_id, $excel_date_time, $location_id, $row_no, $excel_attendance_type);
                }
            }
        }
        //
        if ($time_in_method === 'date_time') {
            //sort array by employee id then by date time
            usort($tmp_data_arr, function($a, $b) {
                if ($a[0] == $b[0]) //employee id
                    return ($a[1] == $b[1] ? 0 : ($a[1] > $b[1] ? 1 : -1)); //date time
                return ($a[0] > $b[0] ? 1 : -1);
            });
            for ($i = 0; $i < count($tmp_data_arr); $i++) {
                $tmp_row = $tmp_data_arr[$i];
                $tmp_time_in = $tmp_time_out = '';
                if ($tmp_row[4] == 'out')
                    $tmp_time_out = $tmp_row[1];
                else
                    $tmp_time_in = $tmp_row[1];
                $row = array('emp_id' => $tmp_row[0], 'time_in' => $tmp_time_in, 'time_out' => $tmp_time_out, 'location_id' => $tmp_row[2], 'row_nb' => $tmp_row[3]);
                if (empty($row['time_out'])) {
                    //check next row if for same employee so it is the out date-time so get it and jump  after this row in the loop
                    //here also we check that diff between in/out is not too far ex: in [05/08] then no out till [07/08]
                    $is_merge_row = true;
                    if (!empty($this->info['max_hrs_shift']) && !empty($tmp_data_arr[$i + 1]) && (strtotime($tmp_data_arr[$i + 1][1]) - strtotime($tmp_row[1])) > ($this->info['max_hrs_shift'] * 3600))
                        $is_merge_row = false;
                    //if specified row is with fixed specific check type out then put it in this current row
                    if (!empty($tmp_data_arr[$i + 1]) && $tmp_data_arr[$i + 1][4] == 'out')
                        $is_merge_row = true;
                    if (!empty($tmp_data_arr[$i + 1]) && $tmp_data_arr[$i + 1][0] == $tmp_row[0] && $is_merge_row) {
                        $row['time_out'] = $tmp_data_arr[$i + 1][1];
                        $i++;
                    }
                }
                $new_data_arr[] = $row;
            }
        }
        //        echo '<pre>' . print_r($excel_err_arr, true) . '<br/>========================================<br/>';
        //        echo '<pre>' . print_r($employees_arr, true) . '<br/>========================================<br/>';
        //        echo '<pre>' . print_r($locations_arr, true) . '<br/>========================================<br/>';
        //        echo '<pre>' . print_r($new_data_arr, true) . '<br/>========================================<br/>';
        //        exit;
        //        $row = '<table><thead><tr><th>Employee Name</th><th>IN</th><th>Out</th><th>Location</th></tr></thead><tbody>';
        //        $row .= '<tr data-id="' . $employee_id['Employee_ID'] . '"><td>' . $employee_id['Employee_Name'] . '</td><td>' . date('Y-m-d H:i:s', (($date_time - 25569) * 86400)) . '</td><td></td><td>' . $location_name['Location_Name'] . '</td></tr>';
        //        $row .= '</tbody></table>';
        return array("data" => $new_data_arr, "employee" => $employees_arr, "location" => $locations_arr, "error" => $excel_err_arr);
    }

    public function convertxlsxtoCSV($infile, $outfile) {
        $fileType = PHPExcel_IOFactory::identify($infile);
        $objReader = PHPExcel_IOFactory::createReader($fileType);

        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($infile);

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
        $objWriter->save($outfile);
    }

    public function insertAttendanceSheet() {
        $error_rows = array();
        $this->db->query('START TRANSACTION');
        foreach ($this->info['attendance_sheet'] as $item) {
            $query = $this->db->query("select * from tbl_employee_attendance_sheet
                            where '" . $item['Time_Out'] . "' > date_time_in and '" . $item['Time_In'] . "' < date_time_out 
                                and employee_id = '" . $item['Employee_ID'] . "' ");
            $row = $this->db->assoc($query);
            if (!empty($row)) {
                $error_rows[] = array($item['tr_index'], CommonLanguage::getMessage('duplicate_entry'));
//                $error[0] = CommonLanguage::getMessage('duplicate_entry');
//                return $error;
            } else {
                $item_data = [
                    &$item['Employee_ID'],
                    &$item['Location_ID'],
                    &$item['Time_In'],
                    &$item['Time_Out']
                ];

                $this->db->prepare_Stmt('INSERT INTO
                                    tbl_employee_attendance_sheet (
                                         employee_id,
                                         location_id,
                                         date_time_in,
                                         date_time_out
                                    ) 
                                    VALUES (?,?,?,?)', $item_data);
                $error = $this->db->error();
                if (!empty($error)) {
                    $error_rows[] = array($item['tr_index'], $error[0]);
                    $this->db->clear_error();
                }
            }
        }


//        $error = $this->db->error();
        if (empty($error_rows)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error_rows;
    }

    public function returnAttForEmp($from_date, $to_date, $emp_id, $dep_id, $posted, $emp_location_filter, $att_location_filter, $check_type_empty) {
        $data = array();
        $emp_arr = array();
        $shift_arr = array();
        $tmp_emp_cond = '';
        $setting_query = $this->db->query('SELECT * FROM `tbl_attendance_setting`');
        $sett_row = $this->db->assoc($setting_query);
        //
        $data_array = [&$from_date, &$to_date, &$from_date, &$to_date];
        if (!empty($emp_id))
            $tmp_emp_cond .= ' and emp.Employee_ID in(' . $emp_id . ')';
        if (!empty($dep_id))
            $tmp_emp_cond .= ' and emp.Branch_ID = "' . intval($dep_id) . '" ';
        if (!empty($emp_location_filter))
            $tmp_emp_cond .= ' and emp.Employee_Location = "' . $emp_location_filter . '"';
        //
        $query = $this->db->prepare_Stmt('Select emp.Employee_ID, emp.Shift_ID, concat(Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) as Employee_Name
                    ,emp.Employee_Location as emp_location_id,emp.holiday_is_fixed, l.Location_Name as emp_location_name,
                    s.round_bfr_post,emp.Shift_ID
                from tbl_hrm_employee emp
                    left join tbl_employee_location l on l.Location_ID = emp.Employee_Location
                    join tbl_hrm_shifts s on s.Shift_ID = emp.Shift_ID
                where true ' . $tmp_emp_cond . ' order by emp.Employee_ID asc');
        while ($row = $this->db->assoc($query)) {
            $row['day_nb'] = 0;
            $emp_arr[$row['Employee_ID']] = $row;
            if (!empty($row['Shift_ID']))
                $shift_arr[$row['Shift_ID']] = null;
        }
        //get shift rounds
        $round_time_arr = array();
        $shift_query = $this->db->query('select s.*,r.* from tbl_hrm_shifts s 
            inner join tbl_hrm_shift_rounds r 
            on r.shift_id = s.Shift_ID  where s.Shift_ID in (' . (implode(',', array_keys($shift_arr))) . ')');

        while ($shift_row = $this->db->assoc($shift_query)) {
            $indx = $shift_row['round_type'] == 0 ? 'in' : 'out';
            $round_time_arr[$shift_row['Shift_ID']]['shift_type'] = $shift_row['shift_type'];
            if ($shift_row['shift_type'] == 'fx') {
                $round_time_arr[$shift_row['Shift_ID']]['rounds'][$shift_row['day_indx']][$indx] = $shift_row;
            } else {
                $round_time_arr[$shift_row['Shift_ID']]['rounds'][$indx][] = $shift_row;
            }
        }
        //   
        $emp_ids = count($emp_arr) ? implode(',', array_keys($emp_arr)) : "";
        $att_cond = "";
        $emp_cond = empty($emp_ids) ? " and 1=2 " : " and atnd.employee_id in ({$emp_ids})";
        if (!empty($att_location_filter))
            $att_cond .= ' and atnd.location_id = "' . $att_location_filter . '"';
        //
        if ($posted == 1) {
            $check_type_empty = 0;

            //            die('Select emp.Employee_ID, emp.Shift_ID, concat(Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) as Employee_Name
            //                , atnd.attendance_id, date(if(isNULL(atnd.date_time_in), atnd.date_time_out, atnd.date_time_in)) as record_date, atnd.date_time_in, atnd.date_time_out,
            //                if(isnull(la.Location_ID),emp.Employee_Location,la.Location_ID) as location_id,
            //                if(isnull(la.Location_ID),l.Location_Name,if(isnull(la.Location_Name),"",la.Location_Name)) as location_name
            //                from tbl_hrm_employee emp
            //                    left join tbl_employee_attendance_sheet atnd on (emp.Employee_ID = atnd.employee_id and (date(date_time_in) between "'.$from_date.'" and "'.$to_date.'" || (isNULL(date_time_in) && date(date_time_out) between "'.$from_date.'" and "'.$to_date.'")))
            //                left join tbl_employee_location la on la.Location_ID = atnd.location_id
            //                left join tbl_employee_location l on l.Location_ID = emp.Employee_Location
            //                where true ' . $cond . ' order by emp.Employee_ID, date_time_in asc');
            $data_array = [&$from_date, &$to_date, &$from_date, &$to_date];
            $query = $this->db->prepare_Stmt('Select atnd.employee_id, atnd.attendance_id, la.Location_ID
                , date(if(isNULL(atnd.date_time_in), atnd.date_time_out, atnd.date_time_in)) as record_date, atnd.date_time_in, atnd.date_time_out,
                la.Location_ID as location_id, if(isnull(la.Location_Name),"",la.Location_Name) as location_name
                from tbl_employee_attendance_sheet atnd 
                    left join tbl_employee_location la on la.Location_ID = atnd.location_id
                where (date(date_time_in) between ? and ? || (isNULL(date_time_in) && date(date_time_out) between ? and ?)) 
                ' . $att_cond . ' ' . $emp_cond . '
                order by atnd.employee_id, date_time_in asc', $data_array);
            while ($row = $this->db->assoc($query)) {
                if (empty($row['location_id'])) {
                    $row['location_id'] = $emp_arr[$row['employee_id']]['emp_location_id'];
                    $row['location_name'] = $emp_arr[$row['employee_id']]['emp_location_name'];
                }
                if (!empty($row['attendance_id'])) {  //if empty we will fill it with the default shift time
                    $data[$row['employee_id']][$row['record_date']][] = array('date_time_in' => $row['date_time_in'], 'date_time_out' => $row['date_time_out']
                        , 'location_id' => $row['location_id'], 'location_name' => $row['location_name']);
                    $emp_arr[$row['employee_id']]['day_nb'] = count($data[$row['employee_id']]);
                }
            }
        } elseif ($posted == 0 || $posted == 2) {
            $last_post_arr = array();
            $posted_emp_ids = array();
            if ($posted == 2) {
                $query_last_post = $this->db->prepare_Stmt('Select employee_id, DATE_ADD(date(max(`date_time_in`)), INTERVAL 1 DAY) as max_date
                        from tbl_employee_attendance_sheet where true ' . (empty($emp_ids) ? " and 1=2 " : " and employee_id in ({$emp_ids})") . '  
                    group by `employee_id`');
                while ($row_last = $this->db->assoc($query_last_post)) {
                    //if posted date greater than to date no result will get, if less than from get from date
                    $posted_from_date = $row_last['max_date'];
                    if ($posted_from_date < $from_date)
                        $posted_from_date = $from_date;
                    $last_post_arr[$posted_from_date][$row_last['employee_id']] = $row_last['employee_id'];
                    $posted_emp_ids[$row_last['employee_id']] = $posted_from_date;
                }
            }
            //here not else since maybe employee not posted yet..
            foreach (array_keys($emp_arr) as $tmp_emp_id)
                if (!isset($posted_emp_ids[$tmp_emp_id]))
                    $last_post_arr[$from_date][$tmp_emp_id] = $tmp_emp_id;
            //
            $temp_data = array();
            foreach ($last_post_arr as $tmp_from_date => $tmp_emp_ids_arr) {
                $unposted_array = array(&$tmp_from_date, &$to_date);
                $query = $this->db->prepare_Stmt('Select atnd.entry_id, date(atnd.date_time) as record_date, atnd.date_time,atnd.check_type,
                        la.Location_ID as location_id, if(isnull(la.Location_Name),"",la.Location_Name) as location_name,atnd.employee_id
                    from tbl_checkinout atnd
                        left join tbl_employee_location la on la.Location_ID = atnd.location_id
                    where (date(date_time) between ? and ?) and atnd.employee_id in (' . implode(',', $tmp_emp_ids_arr) . ') ' . $att_cond . '
                    order by atnd.employee_id, date_time asc', $unposted_array);
                //$k = 1; // $k used to check first array to make sure it contains checkout and checkin
                $j = 0; //to ensure checkin and checkout in same array
                $emp_id = 0;
                while ($row = $this->db->assoc($query)) {
                    if ($emp_id != $row['employee_id'])
                        $j = 0;
                    $emp_id = $row['employee_id'];
                    if (empty($row['location_id'])) {
                        $row['location_id'] = $emp_arr[$row['employee_id']]['emp_location_id'];
                        $row['location_name'] = $emp_arr[$row['employee_id']]['emp_location_name'];
                    }
                    if (!empty($row['date_time'])) {
                        if ($posted == 2 && ($emp_arr[$row['employee_id']]['round_bfr_post'] == 1 || $emp_arr[$row['employee_id']]['round_bfr_post'] == 3 ))//round if not posted
                            $row['date_time'] = $this->round_att_times($round_time_arr, $emp_arr[$row['employee_id']]['Shift_ID'], $row['date_time'], $row['check_type']);
//                    echo $row['date_time'].'<br>';

                        if ($row['check_type'] == 0) {  //check in
                            //check datetime and round based o shift setting and round hrs
                            if (empty($temp_data[$emp_id][$j]['date_time_in'])) {
                                $temp_data[$emp_id][$j]['date_time_in'] = $row['date_time'];
                                $temp_data[$emp_id][$j]['location_id'] = $row['location_id'];
                                $temp_data[$emp_id][$j]['location_name'] = $row['location_name'];
                            } else {
                                $diff_min = abs((strtotime($temp_data[$emp_id][$j]['date_time_in']) - strtotime($row['date_time'])) / 60);
                                if ($diff_min <= $sett_row['duplicate_check_period']) {
                                    if ($sett_row['duplicate_check_type'] == 'last') {
                                        $temp_data[$emp_id][$j]['date_time_in'] = $row['date_time'];
                                        $temp_data[$emp_id][$j]['location_id'] = $row['location_id'];
                                        $temp_data[$emp_id][$j]['location_name'] = $row['location_name'];
                                    }
                                } elseif ($diff_min > ($sett_row['max_work_period'] * 60)) {
                                    $j++;   //new row
                                    $temp_data[$emp_id][$j]['date_time_in'] = $row['date_time'];
                                    $temp_data[$emp_id][$j]['location_id'] = $row['location_id'];
                                    $temp_data[$emp_id][$j]['location_name'] = $row['location_name'];
                                } else {
                                    $j++;   //new row
                                    $temp_data[$emp_id][$j]['date_time_in'] = $row['date_time'];
                                    $temp_data[$emp_id][$j]['location_id'] = $row['location_id'];
                                    $temp_data[$emp_id][$j]['location_name'] = $row['location_name'];
                                }
                            }
                        } else {    //check out
                            if (!empty($temp_data[$emp_id][$j]['date_time_in'])) {
                                $temp_data[$emp_id][$j]['date_time_out'] = $row['date_time'];
                                $j++;
                            } else {
                                if (empty($temp_data[$emp_id][$j - 1]['date_time_out'])) {
                                    $temp_data[$emp_id][$j]['date_time_out'] = $row['date_time'];
                                    $temp_data[$emp_id][$j]['location_id'] = $row['location_id'];
                                    $temp_data[$emp_id][$j]['location_name'] = $row['location_name'];
                                    $j++;
                                } else {
                                    $diff_min = abs((strtotime($temp_data[$emp_id][$j - 1]['date_time_out']) - strtotime($row['date_time'])) / 60);
                                    if ($diff_min <= $sett_row['duplicate_check_period']) {
                                        if ($sett_row['duplicate_check_type'] == 'last') {
                                            $temp_data[$emp_id][$j - 1]['date_time_out'] = $row['date_time'];
                                            if (empty($temp_data[$emp_id][$j - 1]['location_id'])) {
                                                $temp_data[$emp_id][$j - 1]['location_id'] = $row['location_id'];
                                                $temp_data[$emp_id][$j - 1]['location_name'] = $row['location_name'];
                                            }
                                        }
                                    } elseif ($diff_min > ($sett_row['max_work_period'] * 60)) {
                                        $temp_data[$emp_id][$j]['date_time_out'] = $row['date_time'];
                                        $temp_data[$emp_id][$j]['location_id'] = $row['location_id'];
                                        $temp_data[$emp_id][$j]['location_name'] = $row['location_name'];
                                        $j++;
                                    } else {
                                        $temp_data[$emp_id][$j]['date_time_out'] = $row['date_time'];
                                        $temp_data[$emp_id][$j]['location_id'] = $row['location_id'];
                                        $temp_data[$emp_id][$j]['location_name'] = $row['location_name'];
                                        $j++;
                                    }
                                }
                                //
                                if (!empty($temp_data[$emp_id][$j - 1]['date_time_in']) && (((strtotime($temp_data[$emp_id][$j - 1]['date_time_in']) - strtotime($temp_data[$emp_id][$j - 1]['date_time_out'])) / 60) > ($sett_row['max_work_period'] * 60))) {
                                    unset($temp_data[$emp_id][$j - 1]['date_time_out']);
                                    $temp_data[$emp_id][$j]['date_time_out'] = $temp_data[$emp_id][$j - 1]['date_time_out'];
                                    $j++;
                                }
                            }
                        }
                        $emp_arr[$row['employee_id']]['day_nb'] = count($temp_data[$emp_id]);
//                         echo"<pre>";print_r($temp_data);
                    }
                    if (!empty($row['Shift_ID']))
                        $shift_arr[$row['Shift_ID']] = null;
                }
//                echo"<pre>";die(print_r($temp_data));
            }
            //
            foreach ($temp_data as $emp_id => $temp) {
                foreach ($temp as $j) {
                    if (empty($check_type_empty) || ($check_type_empty == 1 && empty($j['date_time_in'])) || ($check_type_empty == 2 && empty($j['date_time_out'])) || ($check_type_empty == 3 && (empty($j['date_time_out']) || empty($j['date_time_in'])))) {
                        $date = empty($j['date_time_in']) ? date('Y-m-d', strtotime($j['date_time_out'])) : date('Y-m-d', strtotime($j['date_time_in']));
                        $data[$emp_id][$date][] = $j;
                    }
                }
            }
            //            echo "<pre>";die(print_r($temp_data));
            //            while ($row = $this->db->assoc($query)) {
            //                $emp_arr[$row['Employee_ID']] = array('Employee_ID' => $row['Employee_ID'], 'Employee_Name' => $row['Employee_Name'], 'Shift_ID' => $row['Shift_ID']);
            //                if (!empty($row['entry_id'])){  //if empty we will fill it with the default shift time
            //                if($row['date_time'] == 0)
            //                  $data[$row['Employee_ID']][$row['record_date']][]  
            //                }
            //                    $data[$row['Employee_ID']][$row['record_date']][] = array('date_time_in' => $row['date_time_in'], 'date_time_out' => $row['date_time_out']);
            //                if (!empty($row['Shift_ID']))
            //                    $shift_arr[$row['Shift_ID']] = null;
            //            }  
        }
        //$emp_ids = (empty($emp_arr) ? null : implode(',', array_keys($emp_arr)));
        //
        if (!empty($shift_arr)) {
            $shift_ids = implode(',', array_keys($shift_arr));
            $query = $this->db->prepare_Stmt('Select * from tbl_hrm_shifts where Shift_ID in (' . $shift_ids . ') ');
            while ($row = $this->db->assoc($query)) {
                $row['Working_Day_ID'] = json_decode($row['Working_Day_ID'], true);
                $row['Working_Days_Arr'] = array();
                if (!empty($row['Working_Day_ID'])) {
                    foreach ($row['Working_Day_ID'] as $Working_Day_Row)
                        $row['Working_Days_Arr'][$Working_Day_Row['day_id']] = array('from' => date('H:i', strtotime($Working_Day_Row['Shift_From'])), 'to' => date('H:i', strtotime($Working_Day_Row['Shift_To'])));
                }
                $shift_arr[$row['Shift_ID']] = $row;
            }
        }
        //
        $holiday_arr = array();
        $query = $this->db->prepare_Stmt('Select Holiday_From_Date, Holiday_To_Date, Holiday_Name from tbl_hrm_holidays
                where ((Holiday_From_Date between ? and ?) || (Holiday_To_Date between ? and ?)) ', [&$from_date, &$to_date, &$from_date, &$to_date]);
        while ($row = $this->db->assoc($query)) {
            if ($row['Holiday_From_Date'] <= $row['Holiday_To_Date']) {
                for ($i = $row['Holiday_From_Date']; $i <= $row['Holiday_To_Date']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i))))
                    $holiday_arr[$i] = $row['Holiday_Name'];
            }
        }
        //
//        $query_holiday = $this->db->query('select sum(e.nb_days),e.employee_id from tbl_hrm_emp_absence_setting e
//            join tbl_hrm_absence_reasons r on r.Absence_Reason_ID = e.absence_reason_id
//                     where is_holiday = 1 group by e.employee_id ');
//        while ($row = $this->db->assoc($query_holiday)) {
//            $emp_arr[$row['employee_id']]['has_fixed_holiday'] = 1;
//        }
        //
        $non_attendence_data = array();
        //
        if (!empty($emp_ids)) {
            $query = $this->db->prepare_Stmt('Select Employee_ID, Absence_From_Date, Absence_To_Date, Absence_Reason_Name from employee_absence
                where is_approved = "1" and ((Absence_From_Date between ? and ?) || (Absence_To_Date between ? and ?)) 
                and Employee_ID in (' . $emp_ids . ') ', [&$from_date, &$to_date, &$from_date, &$to_date]);
            while ($row = $this->db->assoc($query)) {
                if ($row['Absence_From_Date'] <= $row['Absence_To_Date']) {
                    for ($i = $row['Absence_From_Date']; $i <= $row['Absence_To_Date']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i))))
                        $non_attendence_data[$row['Employee_ID']][$i][] = $row['Absence_Reason_Name'];
                }
            }
            //
            $query = $this->db->prepare_Stmt('Select Employee_ID, Date_From, Date_To, Days_Number from tbl_hrm_taken_accrued 
                where is_approved = "1" and ((Date_From between ? and ?) || (Date_To between ? and ?)) 
                and Employee_ID in (' . $emp_ids . ') ', [&$from_date, &$to_date, &$from_date, &$to_date]);
            while ($row = $this->db->assoc($query)) {
                if ($row['Date_From'] <= $row['Date_To']) {
                    for ($i = $row['Date_From']; $i <= $row['Date_To']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i))))
                        $non_attendence_data[$row['Employee_ID']][$i][] = CommonLanguage::getMessage('accrued_leave') . ($row['Days_Number'] == 0.5 ? ' ' . CommonLanguage::getMessage('half_day_number') : '');
                }
            }
        }
        //

        if ($from_date <= $to_date && empty($check_type_empty)) {
            for ($i = $from_date; $i <= $to_date; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
                $day_index = date('N', strtotime($i));
                foreach ($emp_arr as $tmp_emp_id => $emp_row) {
                    //if day & there is posted date and day is before posted date then skip it
                    if (!empty($posted_emp_ids[$tmp_emp_id]) && $i < $posted_emp_ids[$tmp_emp_id])
                        continue;
                    if (isset($holiday_arr[$i]) && !$emp_row['holiday_is_fixed'])
                        $data[$tmp_emp_id][$i]['no_attendence'] = (empty($data[$tmp_emp_id][$i]['no_attendence']) ? "" : $data[$tmp_emp_id][$i]['no_attendence'] . " / ") . $holiday_arr[$i];
                    if (!empty($non_attendence_data[$tmp_emp_id][$i])) {
                        foreach ($non_attendence_data[$tmp_emp_id][$i] as $non_attendence_row)
                            $data[$tmp_emp_id][$i]['no_attendence'] = (empty($data[$tmp_emp_id][$i]['no_attendence']) ? "" : $data[$tmp_emp_id][$i]['no_attendence'] . " / ") . $non_attendence_row;
                    }
                    if (!empty($emp_row['Shift_ID']) && !empty($shift_arr[$emp_row['Shift_ID']]) && $shift_arr[$emp_row['Shift_ID']]['shift_type'] == 'fx') {
                        $shift_days = $shift_arr[$emp_row['Shift_ID']]['Working_Days_Arr'];
                        if (empty($shift_days[$day_index]))
                            $data[$tmp_emp_id][$i]['no_attendence'] = (empty($data[$tmp_emp_id][$i]['no_attendence']) ? "" : $data[$tmp_emp_id][$i]['no_attendence'] . " / ") . CommonLanguage::getMessage('non_working_days');
                        if (empty($data[$tmp_emp_id][$i]) && !empty($shift_days[$day_index]))
                            $data[$tmp_emp_id][$i][] = array('date_time_in' => $i . ' ' . $shift_days[$day_index]['from'], 'date_time_out' => $i . ' ' . $shift_days[$day_index]['to'], 'is_auto' => true);
                    }
                }
            }
        }
        //
        foreach ($emp_arr as $tmp_emp_id => $emp_row) {
            $emp_arr[$tmp_emp_id]['srch_from_date'] = empty($posted_emp_ids[$tmp_emp_id]) ? $from_date : $posted_emp_ids[$tmp_emp_id];
            $emp_arr[$tmp_emp_id]['srch_to_date'] = $to_date;
            if (empty($data[$tmp_emp_id]))
                ; //unset($emp_arr[$tmp_emp_id]);
            else
                ksort($data[$tmp_emp_id]);
        }
//        echo"<pre>";
//        die(print_r($emp_arr));
        return ['data' => $data, 'emp_arr' => $emp_arr];
    }

    public function exportAttForEmp($from_date, $to_date, $emp_id, $dep_id, $posted, $emp_location_filter, $att_location_filter, $check_type_empty) {
        $data = $this->returnAttForEmp($from_date, $to_date, $emp_id, $dep_id, $posted, $emp_location_filter, $att_location_filter, $check_type_empty);
//        echo"<pre>";die(print_r($data));
        $objPHPExcel = new PHPExcel();
        // Set the active Excel worksheet to sheet 0
        $objPHPExcel->setActiveSheetIndex(0);
        $rowCount = 1;
        $header_arr = ['EmployeeName', 'CheckIn', 'CheckOut', 'Location'];
        $excel_header = ['A', 'B', 'C', 'D'];
        for ($i = 0; $i < count($excel_header); $i++) {
            $objPHPExcel->getActiveSheet()->SetCellValue($excel_header[$i] . $rowCount, $header_arr[$i]);
        }

        $rowCount++;
        $exported_data = array();
        $attendance_data = $data['data'];
        $employee_data = $data['emp_arr'];
        $i = 0;
        foreach ($attendance_data as $key => $value) {
            $date_row = $attendance_data[$key];
//            echo"<pre>";print_r($date_row);
            foreach ($date_row as $key1 => $value1) {
                if (!array_key_exists('no_attendence', $date_row[$key1])) {
                    $attendance_row = $date_row[$key1];

                    foreach ($attendance_row as $key2 => $value2) {
                        $exported_data[$i]['EmployeeName'] = $employee_data[$key]['Employee_Name'];
                        $exported_data[$i]['CheckIn'] = $attendance_row[$key2]['date_time_in'];
                        $exported_data[$i]['CheckOut'] = $attendance_row[$key2]['date_time_out'];
                        $exported_data[$i]['Location'] = empty($attendance_row[$key2]['location_name']) ? '' : $attendance_row[$key2]['location_name'];
                        $i++;
                    }
                }
            }
        }


        foreach ($exported_data as $rows) {
            for ($i = 0; $i < count($excel_header); $i++) {
                $objPHPExcel->getActiveSheet()->SetCellValue($excel_header[$i] . $rowCount, $rows[$header_arr[$i]]);
            }
            $rowCount++;
        }
        $detWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        // Write the Excel file to filename some_excel_file.xlsx in the current directory
        //$objWriter->save('some_excel_file.xlsx'); 
        header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="EmployeeAttendanceManual.xlsx"');
        $detWriter->save('php://output');
        exit;
    }

    public function insertAttendanceManual($from_date, $to_date, $emp_id, $dep_id, $emp_location_filter, $att_location_filter) {
        $this->db->query('START TRANSACTION');
        $emp_cond = empty($this->info['emp_arr']) ? '' : ' where e.Employee_ID in (' . implode(',', $this->info['emp_arr']) . ') ';
        $emp_arr = array();
        $shift_arr = array();
        $query = $this->db->prepare_Stmt('Select e.Employee_ID, e.Shift_ID, s.round_bfr_post from tbl_hrm_employee e
                        inner join tbl_hrm_shifts s on s.Shift_ID = e.Shift_ID
                    ' . $emp_cond . ' order by e.Employee_ID asc');
        while ($row = $this->db->assoc($query)) {
            $emp_arr[$row['Employee_ID']] = $row;
            if (!empty($row['Shift_ID']))
                $shift_arr[$row['Shift_ID']] = null;
        }
        //get shift rounds
        $round_time_arr = array();
        $shift_query = $this->db->query('select s.*,r.* from tbl_hrm_shifts s 
                inner join tbl_hrm_shift_rounds r on r.shift_id = s.Shift_ID
            where s.Shift_ID in (' . (implode(',', array_keys($shift_arr))) . ') ');
        while ($shift_row = $this->db->assoc($shift_query)) {
            $indx = $shift_row['round_type'] == 0 ? 'in' : 'out';
            $round_time_arr[$shift_row['Shift_ID']]['shift_type'] = $shift_row['shift_type'];
            if ($shift_row['shift_type'] == 'fx')
                $round_time_arr[$shift_row['Shift_ID']]['rounds'][$shift_row['day_indx']][$indx] = $shift_row;
            else
                $round_time_arr[$shift_row['Shift_ID']]['rounds'][$indx][] = $shift_row;
        }
//        $shift_query = $this->db->query('select s.*,r.*,e.Employee_ID from tbl_hrm_employee e 
//            inner join tbl_hrm_shifts s 
//            on s.Shift_ID = e.Shift_ID
//            inner join tbl_hrm_shift_rounds r 
//            on r.shift_id = s.Shift_ID  ' . $emp_cond);
//
//        while ($shift_row = $this->db->assoc($shift_query)) {
//            $indx = $shift_row['round_type'] == 0 ? 'in' : 'out';
//            $round_time_arr[$shift_row['Employee_ID']]['shift_type'] = $shift_row['shift_type'];
//            if ($shift_row['shift_type'] == 'fx') {
//                $round_time_arr[$shift_row['Employee_ID']]['rounds'][$shift_row['day_indx']][$indx] = $shift_row;
//            } else {
//                $round_time_arr[$shift_row['Employee_ID']]['rounds'][$indx][] = $shift_row;
//            }
//        }
        //        if (smc_session()['default_setting']['activate_att_round'] == 1) {
        //            $round_time_arr = explode(',', smc_session()['default_setting']['round_hrs']);
        //        }
//        $cond = '';
        $cond_del = '';
//        $data_array = [&$from_date, &$to_date, &$from_date, &$to_date];
        if (!empty($emp_id)) {
            $cond_del .= ' and employee_id in (' . $emp_id . ') ';
//            $cond .= ' and employee_id = ?';
//            $data_array[] = $emp_id;
        }
        if (!empty($att_location_filter)) {
            $cond_del .= ' and location_id = "' . $att_location_filter . '"';
//            $cond .= ' and location_id = ?';
//            $data_array[] = $att_location_filter;
        }
        if (!empty($dep_id)) {
//            $cond .= ' and employee_id in (select Employee_ID from tbl_hrm_employee where Branch_ID = ?) ';
//            $data_array[] = $dep_id;
        }
        if (!empty($emp_location_filter)) {
//            $cond .= ' and employee_id in (select Employee_ID from tbl_hrm_employee where Employee_Location = ?) ';
//            $data_array[] = $emp_location_filter;
        }
        //
        if (!empty($this->info['del_arr'])) {
            foreach ($this->info['del_arr'] as $del_row) {
                $del_array = [&$del_row['Employee_ID'], &$del_row['from_date'], &$del_row['to_date'], &$del_row['from_date'], &$del_row['to_date']];
                $this->db->prepare_Stmt('Delete from tbl_employee_attendance_sheet 
                    where employee_id = ? and  (date(date_time_in) between ? and ? || (isNULL(date_time_in) && date(date_time_out) between ? and ?)) ' . $cond_del, $del_array);
            }
        }
        //$error = $this->db->error();
        //echo"<pre>";die(print_r($error));
        if (!empty($this->info['attendance_sheet'])) {
            foreach ($this->info['attendance_sheet'] as $item) {

                if (empty($item['Time_In']))
                    $item['Time_In'] = null;
                if (empty($item['Time_Out']))
                    $item['Time_Out'] = null;
                if ($emp_arr[$item['Employee_ID']]['round_bfr_post'] == 2 || $emp_arr[$item['Employee_ID']]['round_bfr_post'] == 3) {//round if not posted
                    $item['Time_In'] = $this->round_att_times($round_time_arr, $emp_arr[$item['Employee_ID']]['Shift_ID'], $item['Time_In'], 0);
                    $item['Time_Out'] = $this->round_att_times($round_time_arr, $emp_arr[$item['Employee_ID']]['Shift_ID'], $item['Time_Out'], 1);
                }

//                if (!empty($round_time_arr[$item['Employee_ID']]['shift_type']) && !empty($round_time_arr[$item['Employee_ID']]['rounds']) && $round_time_arr[$item['Employee_ID']]['shift_type'] == 'fx') {
//                    $day_indx = date('w', strtotime(date("Y-m-d", strtotime($item['Time_In']))));
//                    $day_indx_arr = $round_time_arr[$item['Employee_ID']]['rounds'][$day_indx];
//                    if (!empty($day_indx_arr['in'])) {
//                        $hr_in = date("H:i", strtotime($day_indx_arr['in']['hr']));
//                        $in_min_diff = (strtotime(date("H:i", strtotime($item['Time_In']))) - strtotime($hr_in)) / 60;
//                        $time_in_diff = abs($in_min_diff);
//                        if ($time_in_diff <= $day_indx_arr['in']['round_min'])
//                            $item['Time_In'] = date('Y-m-d H:i', strtotime("-" . $in_min_diff . " minutes", strtotime($item['Time_In'])));
//                    }
//                    if (!empty($day_indx_arr['out'])) {
//                        $hr_out = date("H:i", strtotime($day_indx_arr['out']['hr']));
//                        $out_min_diff = (strtotime(date("H:i", strtotime($item['Time_Out']))) - strtotime($hr_out)) / 60;
//                        $time_out_diff = abs($out_min_diff);
//                        if ($time_out_diff <= $day_indx_arr['out']['round_min'])
//                            $item['Time_Out'] = date('Y-m-d H:i', strtotime("-" . $out_min_diff . " minutes", strtotime($item['Time_Out'])));
//                    }
//                }else {
//                    if (!empty($round_time_arr[$item['Employee_ID']]['rounds']['in'])) {
//
//                        foreach ($round_time_arr[$item['Employee_ID']]['rounds']['in'] as $round) {
//                            $hr_in = date("H:i", strtotime($round['hr']));
//                            $in_min_diff = (strtotime(date("H:i", strtotime($item['Time_In']))) - strtotime($hr_in)) / 60;
//                            $time_in_diff = abs($in_min_diff);
//                            if ($time_in_diff <= $round['round_min'])
//                                $item['Time_In'] = date('Y-m-d H:i', strtotime("-" . $in_min_diff . " minutes", strtotime($item['Time_In'])));
//                        }
//                    }
//                    if (!empty($round_time_arr[$item['Employee_ID']]['rounds']['out'])) {
//
//                        foreach ($round_time_arr[$item['Employee_ID']]['rounds']['out'] as $round) {
//                            $hr_out = date("H:i", strtotime($round['hr']));
//                            $out_min_diff = (strtotime(date("H:i", strtotime($item['Time_Out']))) - strtotime($hr_out)) / 60;
//                            $time_out_diff = abs($out_min_diff);
//                            if ($time_out_diff <= $round['round_min'])
//                                $item['Time_Out'] = date('Y-m-d H:i', strtotime("-" . $out_min_diff . " minutes", strtotime($item['Time_Out'])));
//                        }
//                    }
//                }


                $query = $this->db->query("select * 
                                        from tbl_employee_attendance_sheet
                                    where '" . $item['Time_Out'] . "' > date_time_in and '" . $item['Time_In'] . "' < date_time_out 
                                   

                                    and employee_id = '" . $item['Employee_ID'] . "'

                                    ");

                $row = $this->db->assoc($query);
                if (!empty($row)) {
                    $error = $this->db->error();
                    $error[0] = CommonLanguage::getMessage('duplicate_entry');
                    return ['duplicate' => [$item['indx'], $error]];
                }
                $item_data = [
                    &$item['Employee_ID'],
                    &$item['Time_In'],
                    &$item['Time_Out'],
                    &$item['location_id']
                ];
                $this->db->prepare_Stmt('Insert Into tbl_employee_attendance_sheet (employee_id,date_time_in,date_time_out,location_id) values(?,?,?,?)', $item_data);
            }
        }
        $error = $this->db->error();

        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    //    public function returnComapreData($from_date, $to_date, $emp_id, $dep_id) {
    //        $data = array();
    //        $emp_arr = array();
    //        $shift_arr = array();
    //        $cond = '';
    //        $data_array = [&$from_date, &$to_date, &$from_date, &$to_date];
    //        if (!empty($emp_id)) {
    //            $cond .= ' and emp.Employee_ID = ?';
    //            $data_array[] = $emp_id;
    //        }
    //        if (!empty($dep_id)) {
    //            $cond .= ' and emp.Branch_ID = ?';
    //            $data_array[] = $dep_id;
    //        }
    //        $query = $this->db->prepare_Stmt('Select atnd.*,emp.Employee_ID, emp.Shift_ID, concat(Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) as Employee_Name
    //            , atnd.attendance_id, date(if(isNULL(atnd.date_time_in), atnd.date_time_out, atnd.date_time_in)) as record_date, atnd.date_time_in, atnd.date_time_out,atnd.data_type,atnd.employee_note from tbl_hrm_employee emp
    //                left join tbl_employee_attendance_sheet atnd on (emp.Employee_ID = atnd.employee_id and (date(date_time_in) between ? and ? || (isNULL(date_time_in) && date(date_time_out) between ? and ?)))
    //            where true ' . $cond . ' order by Employee_Name, date_time_in asc', $data_array);
    //        while ($row = $this->db->assoc($query)) {
    //            $emp_arr[$row['Employee_ID']] = array('Employee_ID' => $row['Employee_ID'], 'Employee_Name' => $row['Employee_Name'], 'Shift_ID' => $row['Shift_ID']);
    //            if (!empty($row['attendance_id']))  //if empty we will fill it
    //                $data[$row['Employee_ID']][$row['record_date']][$row['data_type']][] = array('attendance_id' => $row['attendance_id'], 'date_time_in' => $row['date_time_in'], 'date_time_out' => $row['date_time_out'], 'employee_note' => $row['employee_note']);
    //            if (!empty($row['Shift_ID']))
    //                $shift_arr[$row['Shift_ID']] = null;
    //        }
    //
    //
    //
    //        $emp_ids = (empty($emp_arr) ? null : implode(',', array_keys($emp_arr)));
    //
    //
    //        //
    //        if (!empty($shift_arr)) {
    //
    //            $shift_ids = implode(',', array_keys($shift_arr));
    //            $query = $this->db->prepare_Stmt('Select * from tbl_hrm_shifts where Shift_ID in (' . $shift_ids . ') ');
    //            while ($row = $this->db->assoc($query)) {
    //                $row['Working_Day_ID'] = json_decode($row['Working_Day_ID'], true);
    //                $row['Working_Days_Arr'] = array();
    //                if (!empty($row['Working_Day_ID'])) {
    //                    foreach ($row['Working_Day_ID'] as $Working_Day_Row)
    //                        $row['Working_Days_Arr'][$Working_Day_Row['day_id']] = $Working_Day_Row['day_id'];
    //                }
    //                $shift_arr[$row['Shift_ID']] = $row;
    //            }
    //        }
    //        //
    //        $holiday_arr = array();
    //        $query = $this->db->prepare_Stmt('Select Holiday_From_Date, Holiday_To_Date, Holiday_Name from tbl_hrm_holidays
    //                where ((Holiday_From_Date between ? and ?) || (Holiday_To_Date between ? and ?)) ', [&$from_date, &$to_date, &$from_date, &$to_date]);
    //        while ($row = $this->db->assoc($query)) {
    //            if ($row['Holiday_From_Date'] <= $row['Holiday_To_Date']) {
    //                for ($i = $row['Holiday_From_Date']; $i <= $row['Holiday_To_Date']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i))))
    //                    $holiday_arr[$i] = $row['Holiday_Name'];
    //            }
    //        }
    //        //
    //        $non_attendence_data = array();
    //        //
    //        if (!empty($emp_ids)) {
    //
    //            $query = $this->db->prepare_Stmt('Select Employee_ID, Absence_From_Date, Absence_To_Date, Absence_Reason_Name from employee_absence
    //                where is_approved = "1" and ((Absence_From_Date between ? and ?) || (Absence_To_Date between ? and ?)) 
    //                and Employee_ID in (' . $emp_ids . ') ', [&$from_date, &$to_date, &$from_date, &$to_date]);
    //            while ($row = $this->db->assoc($query)) {
    //                if ($row['Absence_From_Date'] <= $row['Absence_To_Date']) {
    //                    for ($i = $row['Absence_From_Date']; $i <= $row['Absence_To_Date']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i))))
    //                        $non_attendence_data[$row['Employee_ID']][$i][] = $row['Absence_Reason_Name'];
    //                }
    //            }
    //            //
    //            $query = $this->db->prepare_Stmt('Select Employee_ID, Date_From, Date_To, Days_Number from tbl_hrm_taken_accrued 
    //                where is_approved = "1" and ((Date_From between ? and ?) || (Date_To between ? and ?)) 
    //                and Employee_ID in (' . $emp_ids . ') ', [&$from_date, &$to_date, &$from_date, &$to_date]);
    //            while ($row = $this->db->assoc($query)) {
    //                if ($row['Date_From'] <= $row['Date_To']) {
    //                    for ($i = $row['Date_From']; $i <= $row['Date_To']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i))))
    //                        $non_attendence_data[$row['Employee_ID']][$i][] = CommonLanguage::getMessage('accrued_leave') . ($row['Days_Number'] == 0.5 ? ' ' . CommonLanguage::getMessage('half_day_number') : '');
    //                }
    //            }
    //        }
    //        //
    //        if ($from_date <= $to_date) {
    //            for ($i = $from_date; $i <= $to_date; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
    //                $day_index = date('N', strtotime($i));
    //
    //                foreach ($emp_arr as $tmp_emp_id => $emp_row) {
    //                    if (!isset($data[$tmp_emp_id][$i][1]))
    //                        $data[$tmp_emp_id][$i][1] = array();
    ////                    $data[$tmp_emp_id][$i][1-fin][] = array('attendance_id' => $row['attendance_id'], 'date_time_in' => $row['date_time_in'], 'date_time_out' => $row['date_time_out'], 'employee_note' => $row['employee_note']);
    //                    $tmp_final_row = &$data[$tmp_emp_id][$i][1];
    //
    //                    if (empty($tmp_final_row) && (!empty($data[$tmp_emp_id][$i][2]) || !empty($data[$tmp_emp_id][$i][3]))) {
    //                        $tmp_in = $tmp_out = null;
    //                        if (!empty($data[$tmp_emp_id][$i][2])) {
    //                            foreach ($data[$tmp_emp_id][$i][2] as $mnul_row) {
    //                                if (!empty($mnul_row['date_time_in']) && (empty($tmp_in) || strtotime(($mnul_row['date_time_in'])) < strtotime($tmp_in)))
    //                                    $tmp_in = $mnul_row['date_time_in'];
    //                                if (!empty($mnul_row['date_time_out']) && (empty($tmp_out) || strtotime(($mnul_row['date_time_out'])) > strtotime($tmp_out)))
    //                                    $tmp_out = $mnul_row['date_time_out'];
    //                            }
    //                        }
    //                        if (!empty($data[$tmp_emp_id][$i][3])) {
    //                            foreach ($data[$tmp_emp_id][$i][3] as $fngr_row) {
    //                                if (!empty($fngr_row['date_time_in']) && (empty($tmp_in) || strtotime(($fngr_row['date_time_in'])) < strtotime($tmp_in)))
    //                                    $tmp_in = $fngr_row['date_time_in'];
    //                                if (!empty($fngr_row['date_time_out']) && (empty($tmp_out) || strtotime(($fngr_row['date_time_out'])) > strtotime($tmp_out)))
    //                                    $tmp_out = $fngr_row['date_time_out'];
    //                            }
    //                        }
    //                        if (!empty($tmp_in) && !empty($tmp_out) && strtotime($tmp_in) < strtotime($tmp_out))
    //                            $tmp_final_row[] = array('attendance_id' => '', 'date_time_in' => $tmp_in, 'date_time_out' => $tmp_out, 'employee_note' => '');
    //                    }
    //
    //                    if (isset($holiday_arr[$i]))
    //                        $tmp_final_row['no_attendence'] = (empty($tmp_final_row['no_attendence']) ? "" : $tmp_final_row['no_attendence'] . " / ") . $holiday_arr[$i];
    //                    if (!empty($non_attendence_data[$tmp_emp_id][$i])) {
    //                        foreach ($non_attendence_data[$tmp_emp_id][$i] as $non_attendence_row)
    //                            $tmp_final_row['no_attendence'] = (empty($tmp_final_row['no_attendence']) ? "" : $tmp_final_row['no_attendence'] . " / ") . $non_attendence_row;
    //                    }
    //                    if (!empty($emp_row['Shift_ID']) && !empty($shift_arr[$emp_row['Shift_ID']])) {
    //                        $shift_days = $shift_arr[$emp_row['Shift_ID']]['Working_Days_Arr'];
    //                        if (empty($shift_days[$day_index]))
    //                            $tmp_final_row['no_attendence'] = (empty($tmp_final_row['no_attendence']) ? "" : $tmp_final_row['no_attendence'] . " / ") . CommonLanguage::getMessage('non_working_days');
    //                        if (empty($tmp_final_row) && !empty($shift_days[$day_index]))
    //                            $tmp_final_row['no_attendence'] = (empty($tmp_final_row['no_attendence']) ? "" : $tmp_final_row['no_attendence'] . " / ") . CommonLanguage::getMessage('not_come_to_work');
    //                    }
    //                }
    //            }
    //        }
    //        //
    //        foreach ($emp_arr as $tmp_emp_id => $emp_row)
    //            if (!empty($data[$tmp_emp_id]))
    //                ksort($data[$tmp_emp_id]);
    //
    //        return ['data' => $data, 'emp_arr' => $emp_arr];
    //    }

    public function attendanceReport($from_date, $to_date, $emp_id, $dep_id = null, $shift_type = null, $is_actual = 0) {
        $data = array();
        $emp_arr = array();
        $shift_arr = array();
        $total_hrs_wrks = 0.0;
        $total_hrs_arr = array();
        $cond = '';
        $data_array = [&$from_date, &$to_date, &$from_date, &$to_date];
        $from_month = explode('-', $from_date)[1];
        $from_year = explode('-', $from_date)[0];
        if (!empty($emp_id)) {
            $cond .= ' and emp.Employee_ID = ?';
            $data_array[] = $emp_id;
        }
        if (!empty($dep_id)) {
            $cond .= ' and emp.Branch_ID = ?';
            $data_array[] = $dep_id;
        }
        if (!is_null($shift_type)) {
            if ($shift_type == 0)
                $cond .= ' and s.shift_type = "fx"';
            elseif ($shift_type == 1)
                $cond .= ' and s.unit_time = 2 and s.shift_type = "hrm"';
            elseif ($shift_type == 2)
                $cond .= ' and s.unit_time = 1 and s.shift_type = "hrm"';
        }
        if (empty($is_actual))
            $cond .= ' and emp.Is_Hidden = 0 ';
        //        die('Select atnd.*,emp.Employee_ID, emp.Shift_ID, concat(Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) as Employee_Name
        //            , atnd.attendance_id, date(if(isNULL(atnd.date_time_in), atnd.date_time_out, atnd.date_time_in)) as record_date, atnd.date_time_in, atnd.date_time_out ,s.avg_hrs
        //            from tbl_hrm_employee emp
        //                left join tbl_employee_attendance_sheet atnd 
        //                on (emp.Employee_ID = atnd.employee_id and (date(date_time_in) between "'.$from_date.'" and "'.$to_date.'" || (isNULL(date_time_in) && date(date_time_out) between "'.$from_date.'" and "'.$to_date.'")))
        //                left join tbl_hrm_shifts s on s.Shift_ID = emp.Shift_ID
        //            where true ' . $cond . ' order by Employee_Name, date_time_in asc');
        $shift_hr_arr = array();
        $att_data = array();
        $query_shift = $this->db->query('select shift_id,
            nb_of_hours as avg_hrs,
            avg_hrs_day
            from  tbl_hrm_shift_hours 
            where month = "' . $from_month . '" and (year = 0 or year = "' . $from_year . '")
                order by year asc ');
        while ($row_shift = $this->db->assoc($query_shift)) {
            $shift_hr_arr[$row_shift['shift_id']]['avg_hrs'] = $row_shift['avg_hrs'];
            $shift_hr_arr[$row_shift['shift_id']]['avg_hrs_day'] = $row_shift['avg_hrs_day'];
        }
//echo"<pre>";die(print_r($data_array));
        $query = $this->db->prepare_Stmt('Select atnd.*,emp.Employee_ID, emp.Shift_ID, 
            concat(Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) as Employee_Name
            , atnd.attendance_id, date(if(isNULL(atnd.date_time_in), atnd.date_time_out, atnd.date_time_in)) as record_date,
            atnd.date_time_in, atnd.date_time_out ,s.avg_hrs,s.max_hrs_per_day,s.avg_hrs_per_day,emp.holiday_is_fixed
            from tbl_hrm_employee emp
                left join tbl_employee_attendance_sheet atnd 
                on (emp.Employee_ID = atnd.employee_id and (date(date_time_in) between ? and ? || (isNULL(date_time_in) && date(date_time_out) between ? and ?)))
                left join tbl_hrm_shifts s on s.Shift_ID = emp.Shift_ID
            where emp.Status_ID = 1 and (emp.leave_date > "' . $from_date . '" or emp.leave_date ="0000-00-00" or isnull(emp.leave_date))
                and Employee_StartDate <= "' . $to_date . '" 
                ' . $cond . ' order by Employee_Name, date_time_in asc', $data_array);
        while ($row = $this->db->assoc($query)) {
            $emp_arr[$row['Employee_ID']] = array('Employee_ID' => $row['Employee_ID'],
                'Employee_Name' => $row['Employee_Name'], 'Shift_ID' => $row['Shift_ID'],
                'avg_hrs' => (!empty($shift_hr_arr[$row['Shift_ID']]['avg_hrs']) ? $shift_hr_arr[$row['Shift_ID']]['avg_hrs'] : $row['avg_hrs']),
                'max_hrs_per_day' => $row['max_hrs_per_day'],
                'avg_hrs_per_day' => (!empty($shift_hr_arr[$row['Shift_ID']]['avg_hrs_day']) ? $shift_hr_arr[$row['Shift_ID']]['avg_hrs_day'] : $row['avg_hrs_per_day']),
                'holiday_is_fixed' => $row['holiday_is_fixed']);
//                                echo "<pre>";die(print_r($emp_arr));
            if (!empty($row['attendance_id'])) {
                if (empty($data[$row['Employee_ID']][$row['record_date']]))
                    $data[$row['Employee_ID']][$row['record_date']] = 0;
                $att_data[$row['Employee_ID']][$row['record_date']][] = $row;
                $data[$row['Employee_ID']][$row['record_date']] += (strtotime($row['date_time_out']) - strtotime($row['date_time_in']));
            }
            if (!empty($row['Shift_ID']))
                $shift_arr[$row['Shift_ID']] = null;
        }
        $emp_ids = (empty($emp_arr) ? null : implode(',', array_keys($emp_arr)));
        //
        if (!empty($shift_arr)) {
            $shift_ids = implode(',', array_keys($shift_arr));
            $query = $this->db->prepare_Stmt('Select * from tbl_hrm_shifts where Shift_ID in (' . $shift_ids . ') ');
            while ($row = $this->db->assoc($query)) {
                $row['Working_Day_ID'] = json_decode($row['Working_Day_ID'], true);
                $row['Working_Days_Arr'] = array();
                if (!empty($row['Working_Day_ID'])) {
                    foreach ($row['Working_Day_ID'] as $Working_Day_Row) {
                        $row['Working_Days_Arr'][$Working_Day_Row['day_id']] = (strtotime($Working_Day_Row['Shift_To']) - strtotime($Working_Day_Row['Shift_From']));
                    }
                }
                $shift_arr[$row['Shift_ID']] = $row;
            }
        }
        //
        $holiday_arr = array();
        $query = $this->db->prepare_Stmt('Select Holiday_From_Date, Holiday_To_Date, Holiday_Name from tbl_hrm_holidays
                where ((Holiday_From_Date between ? and ?) || (Holiday_To_Date between ? and ?)) ', [&$from_date, &$to_date, &$from_date, &$to_date]);
        while ($row = $this->db->assoc($query)) {
            if ($row['Holiday_From_Date'] <= $row['Holiday_To_Date']) {
                for ($i = $row['Holiday_From_Date']; $i <= $row['Holiday_To_Date']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i))))
                    $holiday_arr[$i] = 1;
            }
        }
//        echo"<pre>";die(print_r($emp_arr));
        //
//        $query_holiday = $this->db->query('select sum(e.nb_days),e.employee_id from tbl_hrm_emp_absence_setting e
//            join tbl_hrm_absence_reasons r on r.Absence_Reason_ID = e.absence_reason_id
//                     where is_holiday = 1 and  e.employee_id = "'.$emp_id.'" group by e.employee_id ');
//        while ($row = $this->db->assoc($query_holiday)) {
//            if(!empty($emp_arr[$row['employee_id']]))
//                $emp_arr[$row['employee_id']]['has_fixed_holiday'] = 1;
//        }
        //
        $non_attendence_data = array();
        $leave_days = array();
        $absence_days = array();
        //
        if (!empty($emp_ids)) {
            $query = $this->db->prepare_Stmt('Select Employee_ID, Absence_From_Date, Absence_To_Date, Absence_Reason_Name from employee_absence
                where is_approved = "1" and ((Absence_From_Date between ? and ?) || (Absence_To_Date between ? and ?)) 
                and Employee_ID in (' . $emp_ids . ') ', [&$from_date, &$to_date, &$from_date, &$to_date]);
            while ($row = $this->db->assoc($query)) {
                if ($row['Absence_From_Date'] <= $row['Absence_To_Date']) {
                    for ($i = $row['Absence_From_Date']; $i <= $row['Absence_To_Date']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
                        if (!isset($non_attendence_data[$row['Employee_ID']][$i]))
                            $non_attendence_data[$row['Employee_ID']][$i] = 0;
                        $non_attendence_data[$row['Employee_ID']][$i] += 1;
                        //
                        if (!isset($absence_days[$row['Employee_ID']]))
                            $absence_days[$row['Employee_ID']] = 0;
                        $absence_days[$row['Employee_ID']] += 1;
                    }
                }
            }
            //

            $query = $this->db->prepare_Stmt('Select Employee_ID, Date_From, Date_To, Days_Number from tbl_hrm_taken_accrued 
                where is_approved = "1" and ((Date_From between ? and ?) || (Date_To between ? and ?)) 
                and Employee_ID in (' . $emp_ids . ') ', [&$from_date, &$to_date, &$from_date, &$to_date]);
            while ($row = $this->db->assoc($query)) {
                if ($row['Date_From'] <= $row['Date_To']) {
                    for ($i = $row['Date_From']; $i <= $row['Date_To']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
                        if (!isset($non_attendence_data[$row['Employee_ID']][$i]))
                            $non_attendence_data[$row['Employee_ID']][$i] = 0;
                        $non_attendence_data[$row['Employee_ID']][$i] += ($row['Days_Number'] == 0.5 ? 0.5 : 1);
                        //
                        if (!isset($leave_days[$row['Employee_ID']]))
                            $leave_days[$row['Employee_ID']] = 0;
                        $leave_days[$row['Employee_ID']] += ($row['Days_Number'] == 0.5 ? 0.5 : 1);
                    }
                }
            }
        }
//        echo"<pre>";die(print_r($emp_arr));
        //
        if ($from_date <= $to_date) {
            for ($i = $from_date; $i <= $to_date; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
                $day_index = date('N', strtotime($i));
                //                echo $i.'===';
                foreach ($emp_arr as $tmp_emp_id => $emp_row) {
                    //                    if($tmp_emp_id==19){
//                                            echo"<pre>";
//                                            die(print_r($emp_row));
                    //                    }
                    //                        if(empty($non_attendence_data[$tmp_emp_id]))
                    //                            echo'true';
                    //                        else
                    //                            echo 'dales';
                    //                    echo ($emp_row['avg_hrs']-array_sum($non_attendence_data[$tmp_emp_id])*$emp_row['avg_hrs_per_day']).'==';
                    //                }
                    $emp_row['avg_hrs'] = /* empty($emp_row['max_hrs_per_day']) ? $emp_row['avg_hrs'] : */(empty($non_attendence_data[$tmp_emp_id]) ? $emp_row['avg_hrs'] : ($emp_row['avg_hrs'] - array_sum($non_attendence_data[$tmp_emp_id]) * $emp_row['avg_hrs_per_day']));
//                   echo array_sum($non_attendence_data[$tmp_emp_id]).'==='.$emp_row['max_hrs_per_day'].'===';
//                    echo"<pre>";print_r($emp_row);
                    if (!isset($total_hrs_arr[$tmp_emp_id]))
                        $total_hrs_arr[$tmp_emp_id] = array('emp_name' => $emp_row['Employee_Name'], 'days_no' => 0, 'must_work' => $emp_row['avg_hrs'], 'real_work' => 0, 'overtime' => 0,'total_deduction'=>0,'total_overtime'=>0,'cumulative'=>0, 'absence_days' => (empty($absence_days[$tmp_emp_id]) ? 0 : $absence_days[$tmp_emp_id]), 'leave_days' => (empty($leave_days[$tmp_emp_id]) ? 0 : $leave_days[$tmp_emp_id]));
                    //
                    $emp_hrs = &$total_hrs_arr[$tmp_emp_id];
                    if (!empty($emp_row['Shift_ID']) && !empty($shift_arr[$emp_row['Shift_ID']])) {
                        $shift_days = $shift_arr[$emp_row['Shift_ID']]['Working_Days_Arr'];
                        if (!empty($shift_days[$day_index])) {
                            if ((!isset($holiday_arr[$i]) || $emp_row['holiday_is_fixed']) && (!isset($non_attendence_data[$tmp_emp_id][$i]) || $non_attendence_data[$tmp_emp_id][$i] < 1)) {
                                $emp_hrs['must_work'] += ($shift_days[$day_index] / 3600);
                                if (isset($non_attendence_data[$tmp_emp_id][$i]) && $non_attendence_data[$tmp_emp_id][$i] < 1)
                                    $emp_hrs['must_work'] -= (($shift_days[$day_index] * $non_attendence_data[$tmp_emp_id][$i]) / 3600);
                            }
                        }
                    }
                    //
                    $att_days_arr = array();
                    //                    if ($emp_row['Employee_ID'] == 19)
//                                            echo"<pre>";print_r($non_attendence_data);
                    if (empty($emp_row['max_hrs_per_day'])) {
                        if (!empty($data[$tmp_emp_id][$i])) {
                            $emp_hrs['real_work'] += ($data[$tmp_emp_id][$i] / 3600);
                            $emp_hrs['days_no'] ++;
                        }
                    } else {
                        //                        if (!empty($non_attendence_data[$tmp_emp_id])){
                        ////                            echo (array_sum($non_attendence_data[$tmp_emp_id]))*$emp_row['max_hrs_per_day'].'=='.$emp_hrs['must_work'];
                        ////                            echo array_sum($non_attendence_data[$tmp_emp_id]).'=='.$emp_row['max_hrs_per_day'];
                        ////                            echo"<pre>";print_r($non_attendence_data[$tmp_emp_id]);
                        //                            $emp_hrs['must_work'] = $emp_hrs['must_work'] - (array_sum($non_attendence_data[$tmp_emp_id]))*$emp_row['max_hrs_per_day'];
//                                                    echo $emp_hrs['must_work'].'==';
                        //                        }
                        if (!empty($att_data[$tmp_emp_id][$i])) {
                            $rem = 0;
                            $att_days_arr[$i] = 0;
                            $emp_hrs['days_no'] ++;

                            foreach ($att_data[$tmp_emp_id][$i] as $att) {
                                $diff_att = (strtotime($att['date_time_out']) - strtotime($att['date_time_in'])) / 3600;
                                $rem = max(($emp_row['max_hrs_per_day'] - $att_days_arr[$i]), 0);

                                if (($diff_att + $att_days_arr[$i]) <= $emp_row['max_hrs_per_day']) {
                                    $emp_hrs['real_work'] += $diff_att;
                                    $att_days_arr[$i] += $diff_att;
                                } else {
                                    $emp_hrs['real_work'] += $rem;
                                    $att_days_arr[$i] += $rem;
                                    $emp_hrs['overtime'] += ($diff_att - $rem);
                                }
                            }
                            //                            echo "<pre>";(print_r($att_data[$tmp_emp_id][$i]));
                        }
                        //                       $temp_data_hrs = $att_data[$tmp_emp_id][$i]
                    }
                }
            }
        }
        //
        if (!empty($emp_ids)) {
            $query = $this->db->query('SELECT 
                                        SUM(Overtime_Duration) as Overtime_Duration,
                                        Employee_ID,is_deduction,type
                                    FROM 
                                        tbl_hrm_employee_overtime_hours
                                    WHERE 
                                        Employee_ID in (' . $emp_ids . ')
                                    AND OverTime_Date between  "' . $from_date . '" AND "' . $to_date . '"
                                    AND Is_Paid = 0 and is_approved = 1 and type in (0,1)
                                    group by Employee_ID,is_deduction
                ');
            while ($row = $this->db->assoc($query)) {
                if (!empty($row['Overtime_Duration'])) {
//                if ($row['type'] == 1) {
//                    $total_hrs_arr[$row['Employee_ID']]['cumulative'] = $row['is_deduction'] ? ((-1)*$row['Overtime_Duration'] + 0) : ($row['Overtime_Duration'] + 0);
//                } else {
                    $total_hrs_arr[$row['Employee_ID']][$row['is_deduction'] ? 'total_deduction' : 'total_overtime'] = $row['Overtime_Duration'] + 0;
//                }
                }
            }
            $query = $this->db->query('SELECT 
                                        SUM(if(type=1,Overtime_Duration,(Overtime_Duration*-1))) as Overtime_Duration,
                                        Employee_ID,is_deduction,type
                                    FROM 
                                        tbl_hrm_employee_overtime_hours
                                    WHERE 
                                        Employee_ID in (' . $emp_ids . ')
                                    AND Is_Paid = 0 and is_approved = 1 and type in (1,2)
                                    group by Employee_ID,is_deduction
                ');
            while ($row = $this->db->assoc($query)) {
                if (!empty($row['Overtime_Duration'])) {
//                if ($row['type'] == 1) {
//                    $total_hrs_arr[$row['Employee_ID']]['cumulative'] = $row['is_deduction'] ? ((-1)*$row['Overtime_Duration'] + 0) : ($row['Overtime_Duration'] + 0);
//                } else {
                    $total_hrs_arr[$row['Employee_ID']]['cumulative'] = $row['Overtime_Duration'] + 0;
//                }
                }
            }
        }

        //        echo"<pre>";die(print_r($total_hrs_arr));
        return ['data' => $total_hrs_arr];
    }

    public function get_attendance() {
        $data = array();
        $sync_ids = array();
        $work_ids = array();
        //
        $query_emp = $this->db->query('SELECT `Employee_Work_ID`,`Employee_ID` FROM `tbl_hrm_employee` WHERE `Status_ID`=1');
        while ($row = $this->db->assoc($query_emp))
            $work_ids[$row['Employee_Work_ID']] = $row['Employee_ID'];
        //
        $query = $this->db->query('SELECT `id`,  `pin`+0 as pin, `checktime`, `checktype` FROM `zkt`.`checkinout` order by pin+0');
        $k = 0; // $k used to check first array to make sure it contains checkout and checkin
        $j = 0; //to ensure checkin and checkout in same array
        while ($row = $this->db->assoc($query)) {
            $emp_id = $work_ids[$row['pin']];
            if ($k == 0) {
                //first row : if check out then will enter first array check in and checkout else will skip first row
                if ($row['checktype'] == 0 || $row['checktype'] == 4) {
                    $data[$emp_id][$j]['checkin'] = $row['checktime'];
                    $data[$emp_id][$j]['employee_id'] = $emp_id;
                    $data[$emp_id][$j]['entry_ids'][] = $row['id'];
                    $k = 1; //this means there is checkout and the next row will be check in
                } else
                    $k = 2; // to skip first row since it is not checkout
            }
            else if ($k == 1) {
                //this means first row is checkout and so the same array will include checkin for this checkout
                $data[$emp_id][$j]['checkout'] = $row['checktime'];
                $data[$emp_id][$j]['employee_id'] = $emp_id;
                $data[$emp_id][$j]['entry_ids'][] = $row['id'];
                $j++;
                $k = 2; // to proceed with the next rows
            } else {
                //after finishing first array
                $data[$emp_id][$j]['employee_id'] = $emp_id;
                $data[$emp_id][$j]['entry_ids'][] = $row['id'];
                if ($row['checktype'] == 0 || $row['checktype'] == 4) {
                    $data[$emp_id][$j]['checkin'] = $row['checktime'];
                } else {
                    $data[$emp_id][$j]['checkout'] = $row['checktime'];
                    $j++;
                }
            }
            $sync_ids[] = $row['id'];
        }
        //
        $result = array();
        foreach ($data as $emp) {
            if (count($emp) > 0) {

                $emp = array_values($emp);

                if (!array_key_exists('checkout', $emp[count($emp) - 1])) {
                    //to make sure the last array contains both 
                    //else it will be removed
                    $index = array_search($emp[count($emp) - 1]['entry_ids'][0], $sync_ids);
                    unset($sync_ids[$index]);

                    array_pop($emp);
                }
                foreach ($emp as $attendance) {
                    $result[] = $attendance;
                }
            }
        }

        echo "<pre>";
        print_r($sync_ids);
        die(print_r($result));
        die();
    }

    public function employee_absence_leave($emp_id, $from_date, $to_date) {
        $non_attendence_data = array();
        $reasons = array();
        $query_absence = $this->db->query('SELECT * FROM `tbl_hrm_absence_reasons`');
        while ($row_absence = $this->db->assoc($query_absence)) {
            $reasons[$row_absence['Absence_Reason_ID']] = $row_absence;
        }
        $query = $this->db->prepare_Stmt('Select Employee_ID, Absence_From_Date, Absence_To_Date, Absence_Reason_Name,Absence_Reason from employee_absence
                where is_approved = "1" and ((Absence_From_Date between ? and ?) || (Absence_To_Date between ? and ?)) 
                and Employee_ID = "' . $emp_id . '"  order by Absence_Reason', [&$from_date, &$to_date, &$from_date, &$to_date]);

//        $days_count = array();
//        $absence_result = array();
//        while ($row = $this->db->assoc($query)) {
//            if ($row['Absence_From_Date'] <= $row['Absence_To_Date']) {
//                $row_arr = ['from' => $row['Absence_From_Date'], 'to' => $row['Absence_To_Date'],
//                    'reason' => $row['Absence_Reason_Name'], 'days' => 0, 'status' => 0, 'reason_id' => $row['Absence_Reason']];
//                for ($i = $row['Absence_From_Date']; $i <= $row['Absence_To_Date']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
//                    $row_arr['days']+=1;
//                    if (!isset($days_count[$row['Absence_Reason']]))
//                        $days_count[$row['Absence_Reason']] = 0;
//                    $days_count[$row['Absence_Reason']]+=1;
//                    $row_arr['status'] = ($reasons[$row['Absence_Reason']]['Absence_Reason_Days'] - $days_count[$row['Absence_Reason']]);
//                    $absence_result[$row['Absence_Reason']] = $row_arr['status'];
//                }
//                $non_attendence_data[] = $row_arr;
//            }
//        }
//        $days_count = array();
//        $absence_result = array();
        while ($row = $this->db->assoc($query)) {
            if ($row['Absence_From_Date'] <= $row['Absence_To_Date']) {
                $row_arr = ['reason' => $row['Absence_Reason_Name'], 'from' => $row['Absence_From_Date'], 'to' => $row['Absence_To_Date'],
                    'days' => (CommonFunctions::calc_date_diff($row['Absence_From_Date'], $row['Absence_To_Date']) + 1)];

                $non_attendence_data[] = $row_arr;
            }
        }

        //
        $leave_count = 0;
        $query = $this->db->prepare_Stmt('Select Employee_ID, Date_From, Date_To, Days_Number,is_holiday from tbl_hrm_taken_accrued 
                where is_approved = "1" and ((Date_From between ? and ?) || (Date_To between ? and ?)) 
                and Employee_ID = "' . $emp_id . '" ', [&$from_date, &$to_date, &$from_date, &$to_date]);
        while ($row = $this->db->assoc($query)) {
            if ($row['Date_From'] <= $row['Date_To']) {
                if ($row['is_holiday'] == 1) {
                    $row_arr = ['reason' => CommonLanguage::getMessage('holiday'),
                        'from' => $row['Date_From'], 'to' => $row['Date_To'],
                        'days' => (CommonFunctions::calc_date_diff($row['Date_From'], $row['Date_To']) + 1)];
                } else {
                    $row_arr = ['reason' => CommonLanguage::getMessage('leave'),
                        'from' => $row['Date_From'], 'to' => $row['Date_To'],
                        'days' => (CommonFunctions::calc_date_diff($row['Date_From'], $row['Date_To']) + 1)];
                }
                $non_attendence_data[] = $row_arr;
            }
        }
//        while ($row = $this->db->assoc($query)) {
//            if ($row['Date_From'] <= $row['Date_To']) {
//                $row_arr = ['from' => $row['Date_From'], 'to' => $row['Date_To'],
//                    'reason' => 'leave', 'days' => 0, 'status' => 0];
//                for ($i = $row['Date_From']; $i <= $row['Date_To']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
//                    $row_arr['days']+=1;
//                    $leave_count+=1;
//                    $row_arr['status'] = $leave_count;
//                }
//                $non_attendence_data[] = $row_arr;
//            }
//        }


        $data = array();
        foreach ($non_attendence_data as $item) {
//            if (!empty($item['reason_id']) && array_key_exists($item['reason_id'], $absence_result))
//                $item['status'] = $absence_result[$item['reason_id']];
//            if ($item['days'] <= 0) {
//                $item['days'] = '<span class="bold red-star">-' . abs($item['days']) . '</span>';
//            } else {
//                $item['days'] = '<span class="bold green-star">+' . abs($item['days']) . '</span>';
//            }
//            unset($item['reason_id']);
//            ksort($item);
            $data[] = array_values($item);
        }
//                echo "<pre>";die(print_r($data));
        return $data;
    }

    public function add_edit_attendance_setting() {
        $this->db->query('START TRANSACTION');
        if ($this->info['is_edit'] == 0) {
            $data = [
                &$this->info['setting_name'],
                &$this->info['duplicate_check_type'],
                &$this->info['duplicate_check_period'],
                &$this->info['max_work_period']
            ];
            $this->db->prepare_Stmt('INSERT INTO `tbl_attendance_setting`( `setting_name`, `duplicate_check_type`,
                `duplicate_check_period`, `max_work_period`)
                values (?,?,?,?)', $data);
        } else {
            $data = [
                &$this->info['setting_name'],
                &$this->info['duplicate_check_type'],
                &$this->info['duplicate_check_period'],
                &$this->info['max_work_period'],
                &$this->info['setting_id']
            ];
            $this->db->prepare_Stmt('update `tbl_attendance_setting`
                set `setting_name`=?, `duplicate_check_type`=?, `duplicate_check_period`=?,
                `max_work_period`=? where att_setting_id =?', $data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_att_setting_by_id($id) {
        $data = array();
        $query = $this->db->query('select * from tbl_attendance_setting where att_setting_id = "' . $id . '"');
        $row = $this->db->assoc($query);
        if (!is_null($row)) {
            $data = $row;
        }
        return $data;
    }

    public function add_edit_att_template() {
        if ($this->info['is_edit'] == 0) {
            $data = [&$this->info['template_name'], &$this->info['columns'], $this->info['max_hrs_shift']];
            $this->db->prepare_Stmt('insert into tbl_employee_attendance_template 
            (template_name, template_columns,max_hrs_shift) values(?,?,?)', $data);
        } else {
            $data = [&$this->info['template_name'], &$this->info['columns'], $this->info['max_hrs_shift'],
                &$this->info['template_id']];
            $this->db->prepare_Stmt('update tbl_employee_attendance_template 
            set template_name = ?, template_columns = ?,max_hrs_shift=? where template_id = ?', $data);
        }
        $error = $this->db->error();
        return $error;
    }

    public function get_att_template_by_id($id) {
        $data = array();
        $query = $this->db->query('select * from tbl_employee_attendance_template where template_id = "' . $id . '"');
        $row = $this->db->assoc($query);
        if (!empty($row)) {
            $row['columns'] = json_decode($row['template_columns']);
            $data = $row;
        }
        return $data;
    }

    public function get_att_templates() {
        $data = array();
        $query = $this->db->query('select * from tbl_employee_attendance_template ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function attendance_location_report($from_date, $to_date, $dep_id = null) {
        $cond = ' ';
        if (!empty($dep_id))
            $cond .= ' and e.Branch_ID = "' . $dep_id . '"';
        $data = array();
        $total_hours = 0;
        $query = $this->db->query('select l.location_name,
            CAST((sum(TIME_TO_SEC(TIMEDIFF(`date_time_out` , `date_time_in`)))/3600) as DECIMAL(9,2)) as total_hours 
            from tbl_employee_attendance_sheet a
            join tbl_hrm_employee e on e.Employee_ID = a.employee_id
            left join tbl_employee_location l on l.location_id = a.location_id
            where date(date_time_in) between "' . $from_date . '" and "' . $to_date . '" ' . $cond . '
            group by a.location_id');
        while ($row = $this->db->assoc($query)) {
            $total_hours += $row['total_hours'];
            $data[] = array_values($row);
        }
        //        die(print_r($data));
        return ['html' => $data, 'total_hours' => $total_hours];
    }

    public function get_non_working_days($from_date, $to_date, $emp_arr, $dep_id, $day_count) {
        $dates_arr = array();
        for ($i = $from_date; $i <= $to_date; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
            $dates_arr[] = $i;
        }
        $cond_leave = ' ';
        $cond_att = ' ';
        $cond_emp = ' ';
        //        echo $emp_arr.'==';
        if (!empty($emp_arr)) {
            $cond_leave .= ' and Employee_ID in (' . $emp_arr . ') ';
            $cond_att .= ' and employee_id in (' . $emp_arr . ') ';
        }
        if (!empty($dep_id)) {
            $cond_emp .= ' and Branch_ID ="' . $dep_id . '" ';
        }
        $emp_data = array();
        $query = $this->db->query('select concat(Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) as Employee_Name,
            Employee_ID from tbl_hrm_employee where 1=1' . $cond_leave . $cond_emp);
        while ($row = $this->db->assoc($query)) {
            $emp_data[$row['Employee_ID']]['Name'] = $row['Employee_Name'];
            $emp_data[$row['Employee_ID']]['detail'] = array();
        }
        if (!empty($emp_data)) {
            $cond_leave .= ' and Employee_ID in (' . implode(',', array_keys($emp_data)) . ') ';
            $cond_att .= ' and employee_id in (' . implode(',', array_keys($emp_data)) . ') ';
        } else {
            $cond_leave .= ' and 1=2 ';
            $cond_att .= ' and 1=2 ';
        }
        $query_abs = $this->db->query('SELECT `Absence_From_Date`,`Absence_To_Date`,Employee_ID 
            FROM `tbl_hrm_absence` where is_approved=1 and ( Absence_From_Date between "' . $from_date . '" and "' . $to_date . '"
                or Absence_To_Date between  "' . $from_date . '" and "' . $to_date . '")' . $cond_leave);
        while ($row = $this->db->assoc($query_abs)) {
            for ($i = $row['Absence_From_Date']; $i <= $row['Absence_To_Date']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
                if ($i >= $from_date && $i <= $to_date) {
                    //                    echo 'here';
                    $emp_data[$row['Employee_ID']]['detail'][] = $i;
                }
            }
        }
        //                        echo"<pre>";die(print_r($emp_data));

        $query_abs = $this->db->query('SELECT  `Date_From`, `Date_To`, `Employee_ID`, `is_approved` 
            FROM `tbl_hrm_taken_accrued` where is_approved=1 and ( Date_From between "' . $from_date . '" and "' . $to_date . '"
                or Date_To between  "' . $from_date . '" and "' . $to_date . '")' . $cond_leave);
        while ($row = $this->db->assoc($query_abs)) {
            for ($i = $row['Date_From']; $i <= $row['Date_To']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
                if ($i >= $from_date && $i <= $to_date) {
                    $emp_data[$row['Employee_ID']]['detail'][] = $i;
                }
            }
        }

        $query_abs = $this->db->query('SELECT date(`date_time_in`) as in_date,`employee_id` FROM `tbl_employee_attendance_sheet` 
           where date(date_time_in) between "' . $from_date . '" and "' . $to_date . '"' . $cond_att);
        while ($row = $this->db->assoc($query_abs)) {
            $emp_data[$row['employee_id']]['detail'][] = $row['in_date'];
        }
        $data = array();
        foreach ($emp_data as $emp_id => $date_arr) {
            $data[$emp_id]['Name'] = $date_arr['Name'];
            $data[$emp_id]['dates'] = array_diff($dates_arr, $date_arr['detail']);
            if (!empty($day_count) && count($data[$emp_id]['dates']) <= $day_count) {
//                echo count($data[$emp_id]['dates']).'==';
                unset($data[$emp_id]);
            }
        }
//                   echo"<pre>".$day_count;
//                die(print_r($data));
//        echo'heree'.count($data).'===';
//        die();
        return $data;
        //        echo"<pre>";
        //        die(print_r($data));
    }

    public function att_location_details_total_hours() {
        $cond = '';
        if (!empty($this->info['location']))
            $cond .= ' and location_id = "' . $this->info['location'] . '" ';
        if (!empty($this->info['employee']))
            $cond .= ' and employee_id = "' . $this->info['employee'] . '" ';
        if (!empty($this->info['from_date']) && !empty($this->info['to_date']))
            $cond .= ' and date(date_time_in) between "' . $this->info['from_date'] . '" and "' . $this->info['to_date'] . '"';
        if (!empty($this->info['dep_id']))
            $cond .= ' and Branch_ID = "' . $this->info['dep_id'] . '"';
        $query = $this->db->query('SELECT SUM(TIMESTAMPDIFF(MINUTE,date_time_in,date_time_out)/60) AS DateDiff 
                                   from detail_attendance_locations
                                   where 1=1' . $cond);
        $total_hours = $this->db->assoc($query);
        return $total_hours['DateDiff'];
    }

    public function round_att_times($round_time_arr, $shift_id, $time, $check_type) {
//        echo"<pre>";
//        print_r($round_time_arr);
//        echo '==='.$shift_id.'==='.$time.'==='.$check_type;
//        $round_time_arr
//        $shift_id
//        $time_in
//        $time_out
//        $check_type
//        $time = '';
        if ($check_type == 0) {
            if (!empty($round_time_arr[$shift_id]['shift_type']) && !empty($round_time_arr[$shift_id]['rounds']) && $round_time_arr[$shift_id]['shift_type'] == 'fx') {
                $day_indx = date('w', strtotime(date("Y-m-d", strtotime($time))));
                $day_indx_arr = $round_time_arr[$shift_id]['rounds'][$day_indx];
                if (!empty($day_indx_arr['in'])) {
                    $hr_in = date("H:i", strtotime($day_indx_arr['in']['hr']));
                    $in_min_diff = (strtotime(date("H:i", strtotime($time))) - strtotime($hr_in)) / 60;
                    $time_in_diff = abs($in_min_diff);
                    if ($time_in_diff <= $day_indx_arr['in']['round_min'])
                        $time = date('Y-m-d H:i', strtotime("-" . $in_min_diff . " minutes", strtotime($time)));
                }
            }else {
                if (!empty($round_time_arr[$shift_id]['rounds']['in'])) {


                    foreach ($round_time_arr[$shift_id]['rounds']['in'] as $round) {
                        $hr_in = date("H:i", strtotime($round['hr']));
                        $in_min_diff = (strtotime(date("H:i", strtotime($time))) - strtotime($hr_in)) / 60;
                        $time_in_diff = abs($in_min_diff);
//                         echo $hr_in.'====='.$in_min_diff.'======'.$time_in_diff.'====';
                        if ($time_in_diff <= $round['round_min'])
                            $time = date('Y-m-d H:i', strtotime("-" . $in_min_diff . " minutes", strtotime($time)));
                    }
                }
            }
        }else {
            if (!empty($round_time_arr[$shift_id]['shift_type']) && !empty($round_time_arr[$shift_id]['rounds']) && $round_time_arr[$shift_id]['shift_type'] == 'fx') {
                $day_indx = date('w', strtotime(date("Y-m-d", strtotime($time))));
                $day_indx_arr = $round_time_arr[$shift_id]['rounds'][$day_indx];

                if (!empty($day_indx_arr['out'])) {
                    $hr_out = date("H:i", strtotime($day_indx_arr['out']['hr']));
                    $out_min_diff = (strtotime(date("H:i", strtotime($time))) - strtotime($hr_out)) / 60;
                    $time_out_diff = abs($out_min_diff);
                    if ($time_out_diff <= $day_indx_arr['out']['round_min'])
                        $time = date('Y-m-d H:i', strtotime("-" . $out_min_diff . " minutes", strtotime($time)));
                }
            }else {

                if (!empty($round_time_arr[$shift_id]['rounds']['out'])) {

                    foreach ($round_time_arr[$shift_id]['rounds']['out'] as $round) {
                        $hr_out = date("H:i", strtotime($round['hr']));
                        $out_min_diff = (strtotime(date("H:i", strtotime($time))) - strtotime($hr_out)) / 60;
                        $time_out_diff = abs($out_min_diff);
                        if ($time_out_diff <= $round['round_min'])
                            $time = date('Y-m-d H:i', strtotime("-" . $out_min_diff . " minutes", strtotime($time)));
                    }
                }
            }
        }
        return $time;
    }

    public function save_temp_att_data() {
        $json_data = json_encode($this->info['att']);
        $date_time = date('Y-m-d H:i:s');
        $logged_id = smc_session()['user_logged_id'];
        if (empty($this->info['temp_id'])) {
            $data = [$this->info['title'], $json_data, $date_time, $logged_id];
            $this->db->prepare_Stmt('INSERT INTO `tbl_hrm_temp_attendance`( `temp_title`, `temp_arr`, `date_time`,
            `Logged_ID`) values(?,?,?,?)', $data);
        } else {
            $data = [$this->info['title'], $json_data, $date_time, $logged_id, $this->info['temp_id']];
            $this->db->prepare_Stmt('update `tbl_hrm_temp_attendance` set `temp_title`=?, `temp_arr`=?,
                `date_time`=?, `Logged_ID`=? where temp_id=?', $data);
        }
        $error = $this->db->error();
        return $error;
    }

    public function delete_temp_att($id) {
        $this->db->prepare_Stmt('delete from tbl_hrm_temp_attendance where temp_id = ?', [$id]);
        $error = $this->db->error();
        return $error;
    }

    public function delete_att_template($id) {
        $this->db->prepare_Stmt('delete from tbl_employee_attendance_template where template_id = ?', [$id]);
        $error = $this->db->error();
        return $error;
    }

}
