<?php

class Finance {

    private $db;
    private $info = array();
    public $permissions;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->permissions = new Permissions(smc_session()['user_logged_id']);
    }

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

    public function createBonusOrDeduction($tbl, $prefix) {
        $check = CommonFunctions::check_lock_data(['type' => 'deduction', 'start_date' => $this->info['Deduction_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        //die(print_r($_POST));
        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        if ($this->info['Entry_ID'] != '' && $this->info['Check_ID'] != 0) {
            $entry_id = $this->info['Entry_ID'];
            $this->db->prepare_Stmt('UPDATE 
                                                     tbl_employee_dedcution_note
                                                SET 
                                                     status_id = 1
                                                WHERE 
                                                    Entry_id = ? ', [&$entry_id]);
            $data = [&$this->info['Deduction_Date'],
                &$this->info['Deduction_Description'],
                &$this->info['Deduction_Amount'],
                &$this->info['Currency_ID'],
                &$this->info['is_actual'],
                &$rate,
                &$this->info['Employee_ID']];
            $this->db->prepare_Stmt('INSERT INTO 
                                    ' . $tbl . '(
                                    ' . $prefix . '_Date,
                                    ' . $prefix . '_Description,
                                    ' . $prefix . '_Amount,
                                    Currency_ID,
                                    is_actual,
                                    current_rate,
                                    Employee_ID
                            )
        Values(?,?,?,?,?,?,?)', $data);
        } else if ($this->info['Entry_ID'] != '' && $this->info['Check_ID'] == 0) {
            $data = [&$this->info['Deduction_Date'], &$this->info['Deduction_Description'],
                &$this->info['Deduction_Amount'], &$this->info['Currency_ID'], &$rate,
                &$this->info['Employee_ID'], &$this->info['Entry_ID']];
            $this->db->prepare_Stmt('UPDATE 
                                               ' . $tbl . '      
                                                SET 
                                                 ' . $prefix . '_Date = ?,
                                                 ' . $prefix . '_Description = ?,
                                                 ' . $prefix . '_Amount = ?,
                                                Currency_ID = ?,
                                                current_rate = ?,
                                                Employee_ID = ?
                                                WHERE 
                                                   ' . $prefix . '_id = ? ', $data);
        } else {
            $data = [&$this->info['Deduction_Date'],
                &$this->info['Deduction_Description'],
                &$this->info['Deduction_Amount'],
                &$this->info['Currency_ID'],
                &$this->info['is_actual'],
                &$rate,
                &$this->info['Employee_ID']];
            $this->db->prepare_Stmt('INSERT INTO 
                                    ' . $tbl . '(
                                    ' . $prefix . '_Date,
                                    ' . $prefix . '_Description,
                                    ' . $prefix . '_Amount,
                                    Currency_ID,
                                    is_actual,
                                    current_rate,
                                    Employee_ID
                            )
        Values(?,?,?,?,?,?,?)', $data);
        }

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

    public function addOverTime($flag = null) {
        $check = CommonFunctions::check_lock_data(['type' => 'overtime', 'start_date' => $this->info['OverTime_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
//        $this->db->query('START TRANSACTION');
        $rate = CurrencyFactory::getLastExRate($this->info['Overtime_Currency']);
        $is_approved = isset($this->info['is_approved']) ? $this->info['is_approved'] : 0;
        $data = [&$this->info['Overtime_Start_Time'],
            &$this->info['Overtime_End_Time'],
            &$this->info['Overtime_Duration'],
            &$this->info['Overtime_Amount'],
            &$this->info['hr_rate'],
            &$this->info['hr_unit_amount'],
            &$this->info['Overtime_Currency'],
            &$rate,
            &$this->info['OverTime_Date'],
            &$this->info['Employee_ID'],
            &$this->info['Is_Holiday'],
            &$this->info['is_actual'],
            &$this->info[0],
            &$this->info['is_deduction'],
            &$this->info['note'],
            &$this->info['type'],
            &$is_approved
        ];
        //overtime
        //(((basic salary/30 )/shifthrs)*1.5)*overtime
        $this->db->prepare_Stmt('INSERT INTO tbl_hrm_employee_overtime_hours( Overtime_Start_Time,
                                                                                Overtime_End_Time,
                                                                                Overtime_Duration,
                                                                                Overtime_Amount,
                                                                                hr_rate,
                                                                                hr_unit_amount,
                                                                                Currency_ID,
                                                                                current_rate,
                                                                                OverTime_Date,
                                                                                Employee_ID,
                                                                                Is_Holiday,
                                                                                is_actual,
                                                                                Logged_ID,is_deduction,note,type,is_approved
                                        )
                                                                VALUES( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
        if (!$is_approved) {
            $ded_id = $this->db->id();
            $emp_arr = CommonFunctions::get_emp_absence_leave_per('approve_overtime');
            $title = ($this->info['is_deduction'] ? 'Deduction' : 'Overtime') . ' for ' . (Employee::get_emp_name_by_id($this->info['Employee_ID']));
            foreach ($emp_arr as $emp_id) {
                $task_arr = array();
                $task_arr['emp_id'] = &$emp_id['User_ID'];
                $task_arr['task_desc'] = &$title;
                $task_arr['due_date'] = &$this->info['OverTime_Date'];
                $task_arr['main_type'] = $this->info['is_deduction'] ? "deduction" : "overtime";
                $task_arr['main_id'] = &$ded_id;
                $task_arr['task_type'] = $this->info['is_deduction'] ? "deduction" : "overtime";
                CommonFunctions::insertEmpTask($task_arr);
            }
        }
        $error = $this->db->error();
//                die(print_r($error));
//        if (empty($error)) {
//            $this->db->query('COMMIT');
//        } else {
//            $this->db->query('ROLLBACK');
//        }
        return array_unique($error);
    }

    public function edit_overtime() {
        $check = CommonFunctions::check_lock_data(['type' => 'overtime', 'start_date' => $this->info['OverTime_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $rate = CurrencyFactory::getLastExRate($this->info['Overtime_Currency']);
        $logged_id = smc_session()['user_logged_id'];
        $data = [&$this->info['Overtime_Start_Time'],
            &$this->info['Overtime_End_Time'],
            &$this->info['Overtime_Duration'],
            &$this->info['Overtime_Amount'],
            &$this->info['hr_rate'],
            &$this->info['hr_unit_amount'],
            &$this->info['Overtime_Currency'],
            &$rate,
            &$this->info['OverTime_Date'],
            &$this->info['Employee_ID'],
            &$this->info['Is_Holiday'],
            &$this->info['is_actual'],
            &$logged_id,
            &$this->info['is_deduction'],
            &$this->info['note'],
            &$this->info['type'],
            &$this->info['Overtime_ID']
        ];
        //overtime
        //(((basic salary/30 )/shifthrs)*1.5)*overtime
        $this->db->prepare_Stmt('update tbl_hrm_employee_overtime_hours set Overtime_Start_Time = ?,
                                                                                Overtime_End_Time = ?,
                                                                                Overtime_Duration = ?,
                                                                                Overtime_Amount = ?,
                                                                                hr_rate = ?,
                                                                                hr_unit_amount = ?,
                                                                                Currency_ID = ?,
                                                                                current_rate = ?,
                                                                                OverTime_Date = ?,
                                                                                Employee_ID = ?,
                                                                                Is_Holiday = ?,
                                                                                is_actual = ?,
                                                                                Logged_ID = ?,is_deduction = ?,note = ?,type=?
                                        where Overtime_ID = ?
                                                                ', $data);
        //        $error = $this->db->error();
        //        die(print_r($error));
        //        $ov_id = $this->db->id();
        //        if ($this->info['Is_Paid'] == 1 && $this->info['is_deduction'] == 0) {
        //            $desc = 'Paid OverTime for :' . $this->info['Employee_Name'];
        //            $ref = str_replace("-", "", $this->info['OverTime_Date']);
        //            $currency_id = $this->info['Overtime_Currency'];
        //            $jv = JV::returnJV($this->info['is_actual']);
        //            $codes = ["63180"];
        //            $ids_arr = JV::getAccountIDsByCodes($codes);
        //            $data_1 = array(
        //                "JV_ID" => $jv,
        //                "Transaction_Reference" => $ref,
        //                "Transaction_Account" => $ids_arr["63180"],
        //                "Transaction_Type" => "Debit",
        //                "Transaction_Amount" => $this->info['Overtime_Amount'],
        //                "Transaction_Date" => $this->info['OverTime_Date'],
        //                "Currency_ID" => $currency_id,
        //                "Logged_ID" => $this->info[0],
        //                "Transaction_Description" => $desc,
        //                "Current_Ex_Rate" => "0",
        //                "main_id" => $ov_id,
        //                "main_type" => "pov"
        //            );
        //            JV::addTransaction($data_1, $this->info['is_actual']);
        //
        //            $data_2 = array(
        //                "JV_ID" => $jv,
        //                "Transaction_Reference" => $ref,
        //                "Transaction_Account" => smc_session()['user_accounts']['cash_account_id'],
        //                "Transaction_Type" => "Credit",
        //                "Transaction_Amount" => $this->info['Overtime_Amount'],
        //                "Transaction_Date" => $this->info['OverTime_Date'],
        //                "Currency_ID" => $currency_id,
        //                "Logged_ID" => $this->info[0],
        //                "Transaction_Description" => $desc,
        //                "Current_Ex_Rate" => "0",
        //                "main_id" => $ov_id,
        //                "main_type" => "pov"
        //            );
        //            JV::addTransaction($data_2, $this->info['is_actual']);
        //        }
        $error = $this->db->error();
        //        die(print_r($error));
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function connectOvertimeToAccountingSystem($flag = 0) {
        $flag = $this->info['is_actual'];
        $this->db->query('START TRANSACTION');
        //need to add logged id,currency, payment_box
        $codes = ["63180"];
        $ids_arr = JV::getAccountIDsByCodes($codes);
        for ($i = 0; $i < count($this->info['OverTime']); $i++) {
            $currency_id = $this->info['OverTime'][$i]['Currency_ID'];
            $desc = 'Paid OverTime for :' . $this->info['OverTime'][$i]['OverTime_Date'];
            $ref = str_replace("-", "", $this->info['OverTime'][$i]['OverTime_Date']);
            $jv = JV::returnJV($this->info['OverTime'][$i]['is_actual']);
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $ids_arr["63180"],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $this->info['OverTime'][$i]['Overtime_Amount'],
                "Transaction_Date" => $this->info['OverTime'][$i]['OverTime_Date'],
                "Currency_ID" => $currency_id,
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => "0",
                "main_id" => $this->info['OverTime'][$i]['Overtime_ID'],
                "main_type" => "pov"
            );
            JV::addTransaction($data_1, $this->info['OverTime'][$i]['is_actual']);

            $data_2 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => smc_session()["user_accounts"]['cash_account_id'],
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $this->info['OverTime'][$i]['Overtime_Amount'],
                "Transaction_Date" => $this->info['OverTime'][$i]['OverTime_Date'],
                "Currency_ID" => $currency_id,
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => "0",
                "main_id" => $this->info['OverTime'][$i]['Overtime_ID'],
                "main_type" => "pov"
            );
            JV::addTransaction($data_2, $this->info['OverTime'][$i]['is_actual']);

            $this->db->prepare_Stmt('UPDATE 
                                                    tbl_hrm_employee_overtime_hours
                                                SET 
                                                    Is_Paid = 1
                                                WHERE 
                                                    Overtime_ID = ? ', [&$this->info['OverTime'][$i]['Overtime_ID']]);
        }


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

    //    public function viewOverTime($date = null, $year = null, $month = null, $is_hidden = false) {
    //        $condition = '';
    //        $data = array();
    //        if (!is_null($date))
    //            $condition .= ' AND o.OverTime_Date = "' . $date . '"';
    //        if (!$is_hidden)
    //            $condition .= ' AND e.Is_Hidden = 0';
    //        $query = $this->db->query('SELECT
    //										o.*,
    //                                        p.Paygrade_Currency,
    //                                        c.Currency_Symbol,
    //                                        c.Currency_ID,
    //										CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name
    //									FROM 
    //										tbl_hrm_employee_overtime_hours o
    //									LEFT JOIN
    //										tbl_hrm_employee e
    //									ON 
    //										e.Employee_ID = o.Employee_ID
    //                                    LEFT JOIN
    //                                        tbl_hrm_paygrade p
    //                                    ON
    //                                        p.Paygrade_ID = e.Paygrade_ID 
    //                                    LEFT JOIN
    //                                        tbl_common_currency c
    //                                    ON
    //
    //                                        c.Currency_ID =  p.Paygrade_Currency
    //									WHERE
    //										o.Is_Paid = 0
    //                                   AND 
    //										MONTH(o.OverTime_Date) = "' . $month . '"
    //                                    AND    
    //                                        YEAR(o.OverTime_Date) = "' . $year . '"
    //				' . $condition);
    //        while ($row = $this->db->assoc($query))
    //            $data[] = $row;
    //        return $data;
    //    }

    public function addAdvancePayment($flag = 0, $other = false) {
        $check = CommonFunctions::check_lock_data(['type' => 'advance', 'start_date' => $this->info['Advance_Payment_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        if (!$other)
            $this->db->query('START TRANSACTION');
        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);

        $data = [&$this->info['Advance_Payment_Date'],
            &$this->info['Advance_Payment_Amount'],
            &$this->info['Advance_Payment_Description'],
            &$this->info['Currency_ID'],
            &$rate,
            &$second_rate,
            &$this->info['Employee_ID'],
            &$this->info['Payment_ID'],
            &$this->info['is_actual'],
            &$this->info['Logged_ID']];
        $this->db->prepare_Stmt('INSERT INTO tbl_hrm_advance_payments( Advance_Payment_Date,
                                                                            Advance_Payment_Amount,
                                                                            Advance_Payment_Description,
                                                                            Currency_ID,
                                                                            current_rate,
                                                                            second_currency_rate,
                                                                            Employee_ID,
                                                                            Payment_ID,
                                                                            is_actual,
                                                                            Logged_ID
                                                                            )VALUES( ?,?,?,?,?,?,?,?,?,?)', $data);

        $advanceid = $this->db->id();
        $emp_arr = CommonFunctions::get_emp_absence_leave_per('approve_advance_payment');
        $title = 'Advance Payment for ' . (Employee::get_emp_name_by_id($this->info['Employee_ID']));
        foreach ($emp_arr as $emp_id) {
            $task_arr = array();
            $task_arr['emp_id'] = &$emp_id['User_ID'];
            $task_arr['task_desc'] = &$title;
            $task_arr['due_date'] = &$this->info['Advance_Payment_Date'];
            $task_arr['main_type'] = "advance";
            $task_arr['main_id'] = &$advanceid;
            $task_arr['task_type'] = "advance";
            CommonFunctions::insertEmpTask($task_arr);
        }
        if (!$other) {
            $error = $this->db->error();
            if (empty($error)) {
                $this->db->query('COMMIT');
            } else {
                $this->db->query('ROLLBACK');
            }
            return array_unique($error);
        }
    }

    public function submit_multi_advance_payment() {
        $this->db->query('START TRANSACTION');
        $all_employees = json_decode($this->info['employee_advance'], true);
        foreach ($all_employees as $employees) {
            $this->info['Employee_ID'] = $employees['Employee_ID'];
            $this->info['Advance_Payment_Amount'] = $employees['amount'];
            $this->info['Employee_Name'] = $employees['Employee_Name'];
            $this->info['Advance_Payment_Description'] = "Advance payment for {$employees['Employee_Name']}";
            $this->addAdvancePayment();
        }

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

    public function addLoan($flag = 0) {
        $check = CommonFunctions::check_lock_data(['type' => 'loan', 'start_date' => $this->info['Loan_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $data = [&$this->info['Loan_Description'],
            &$this->info['Loan_Amount'],
            &$this->info['Loan_Repayment_Start'],
            //            &$this->info['Loan_Repayment_End'],
            &$this->info['Loan_Repayment_Amount'],
            &$this->info['Loan_Date'],
            &$this->info['Currency_ID'],
            &$rate,
            &$second_rate,
            &$this->info['Employee_ID'],
            &$this->info['Payment_ID'],
            &$this->info['is_actual'],
            &$this->info[0]];
        $this->db->prepare_Stmt('INSERT INTO tbl_hrm_loans(	Loan_Description,
                                                                Loan_Amount,
                                                                Loan_Repayment_Start,

                                                                Loan_Repayment_Amount,
                                                                Loan_Date,
                                                                Currency_ID,
                                                                current_rate,second_currency_rate,
                                                                Employee_ID,
                                                                Payment_ID,
                                                                is_actual,
                                                                Logged_ID
                                                    )
                                                VALUES( ?,?,?,?,?,?,?,?,?,?,?,?)', $data);
        $loan_id = $this->db->id();

        $emp_arr = CommonFunctions::get_emp_absence_leave_per('approve_loan');
        $title = 'Loan for ' . (Employee::get_emp_name_by_id($this->info['Employee_ID']));
        foreach ($emp_arr as $emp_id) {
            $task_arr = array();
            $task_arr['emp_id'] = &$emp_id['User_ID'];
            $task_arr['task_desc'] = &$title;
            $task_arr['due_date'] = &$this->info['Loan_Repayment_Start'];
            $task_arr['main_type'] = "loan";
            $task_arr['main_id'] = &$loan_id;
            $task_arr['task_type'] = "loan";
            CommonFunctions::insertEmpTask($task_arr);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function approve_loan() {
        $this->db->query('START TRANSACTION');

        $query = $this->db->query('select * from view_loans  where Loan_ID = "' . $this->info['id'] . '"');
        $row = $this->db->assoc($query);


        $desc = 'Loan for :' . $row['Employee_Name'];
        $ref = str_replace("-", "", $row['Loan_Date']);
        $currency_id = $row['Currency_ID'];
        $rate = $row['current_rate'];
        $second_rate = !empty($row['second_currency_rate']) ? $row['second_currency_rate'] : CurrencyFactory::getSecondCurrencyLastExRate($currency_id);

        $this->db->prepare_Stmt('update tbl_hrm_loans set is_approved = ? where Loan_ID = ?', [&$this->info['status'], &$this->info['id']]);

        $jv = JV::returnJV($row['is_actual']);
        $codes = ["42810"];
        $ids_arr = JV::getAccountIDsByCodes($codes);
        $data_1 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $ids_arr["42810"],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $row['Loan_Amount'],
            "Transaction_Date" => $row['Loan_Date'],
            "Currency_ID" => $currency_id,
            "Logged_ID" => $row['Logged_ID'],
            "Transaction_Description" => $desc,
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $this->info['id'],
            "main_type" => "lon"
        );
        JV::addTransaction($data_1, $row['is_actual']);
        //  $error = $this->db->error();
        //        die(print_r($error));
        $data_2 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $row['Payment_ID'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $row['Loan_Amount'],
            "Transaction_Date" => $row['Loan_Date'],
            "Currency_ID" => $currency_id,
            "Logged_ID" => $row['Logged_ID'],
            "Transaction_Description" => $desc,
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $this->info['id'],
            "main_type" => "lon"
        );
        JV::addTransaction($data_2, $row['is_actual']);
        CommonFunctions::update_task_status($this->info['id'], 'loan');
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function getMaxDeducOrBonus($tbl, $prefix) {
        $data = '';
        $query = $this->db->query('SELECT * FROM ' . $tbl . ' ORDER BY ' . $prefix . '_ID DESC limit 1');
        while ($row = $this->db->assoc($query)) {
            $data = $row[$prefix . '_ID'];
        }
        return $data;
    }

    public function returnEmployeeInfoForOverTime($is_hidden = false, $date = null, $emp_id = null) {
        $data = [];
        $condition = '';
        $cond_join = '';
        $from_date = '';
        $to_date = '';
        if (!$is_hidden)
            $condition .= ' AND e.Is_Hidden = 0';
        if (!empty($date)) {
            $from_date = date("Y-m-d", strtotime(date("Y-m-d", strtotime($date)) . ", first day of this month"));
            $to_date = date("Y-m-d", strtotime(date("Y-m-d", strtotime($date)) . ", last day of this month"));

//                        echo $from_date.'=='.$to_date;
            //            die();
        }
//        echo $from_date.'=='.$to_date;
        if (!empty($emp_id)) {
            $condition .= ' AND e.Employee_id = "' . $emp_id . '"';
        }
//        $condition .= ' AND e.Employee_id = 63';
        $shift_hr_arr = array();
        $query_shift = $this->db->query('select shift_id,
            nb_of_hours as avg_hrs 
            from  tbl_hrm_shift_hours 
            where month = "' . explode('-', $from_date)[1] . '" and (year = 0 or year = "' . explode('-', $from_date)[0] . '")
                order by year asc ');
        while ($row_shift = $this->db->assoc($query_shift)) {
            $shift_hr_arr[$row_shift['shift_id']] = $row_shift['avg_hrs'];
        }
        $shifts_arr = array();
        $total_hrs = '';
        $query = $this->db->query('SELECT 
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                        e.Employee_ID,
                                        if(e.basic_salary = 0,p.Paygrade_Value,e.basic_salary) AS Basic_Salary,
                                        c.*,
                                        s.Shift_Duration,
                                        f.overtime_rate,
                                        f.deduction_rate,
                                        s.shift_type,
                                        pe.unit_time,
                                        s.avg_hrs,
                                        s.Working_Day_ID,f.hr_price_formula,e.Shift_ID
                                    FROM tbl_hrm_employee e
                                    LEFT JOIN tbl_hrm_paygrade p ON e.Paygrade_ID = p.Paygrade_ID
                                    LEFT JOIN tbl_common_currency c ON c.Currency_ID =  if(e.basic_salary = 0,p.Paygrade_Currency,e.basic_salary_currency) 
                                    inner JOIN tbl_hrm_shifts s ON s.Shift_ID = e.Shift_ID	
                                    inner join tbl_hrm_basic_salary_periods pe on pe.period_id = e.period_id
                                    inner join tbl_hrm_salary_unit_formulas f on f.formula_id = pe.formula_id
                                    WHERE 
                                            e.Status_ID=1 
				' . $condition);
        while ($row = $this->db->assoc($query)) {
            $shifts_arr = array();
            $row['avg_hrs'] = empty($shift_hr_arr[$row['Shift_ID']]) ? $row['avg_hrs'] : $shift_hr_arr[$row['Shift_ID']];
            $total_hrs = 0;
            //if basic salary is per month
            if ($row['unit_time'] == 1 && $row['shift_type'] == 'hrm') {
                //                $row['Basic_Salary'] = $row['Basic_Salary'] / $row['avg_hrs'];
                $row['nb_hrs'] = $row['avg_hrs'];
            } elseif ($row['shift_type'] == 'fx') {
                $shift_hrs_arr = json_decode($row['Working_Day_ID'], true);
                if (!empty($shift_hrs_arr)) {
                    foreach ($shift_hrs_arr as $Working_Day_Row) {
                        $shifts_arr[$Working_Day_Row['day_id']] = (strtotime($Working_Day_Row['Shift_To']) - strtotime($Working_Day_Row['Shift_From']));
                    }
                }
                if ($row['Employee_ID'] == 63)
//                                echo $total_hrs . '==';
                    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));
                            //
                            if (!empty($shifts_arr)) {

                                $shift_days = $shifts_arr;
                                if (!empty($shift_days[$day_index])) {
//                                 if($row['Employee_ID']==63)
//                            echo '==='.$i.'===';
                                    $total_hrs += ($shift_days[$day_index] / 3600);
//                                if($row['Employee_ID']==63)
//                                echo $total_hrs . '==';
                                }
                            }
                        }
                    }
                //                $row['Basic_Salary'] = $total_hrs == 0 ? 0 : $row['Basic_Salary'] / $total_hrs;
                $row['nb_hrs'] = $total_hrs;
//                if($row['Employee_ID']==63)
//                                echo $total_hrs . '==';
            } else {
                $row['unit_hr'] = $row['Basic_Salary'];
            }
//            echo $row['nb_hrs'].'===';
            if (($row['unit_time'] == 1 && $row['shift_type'] == 'hrm') || $row['shift_type'] == 'fx') {
                $unit_hr = 0;
                $formula = str_replace('bs', $row['Basic_Salary'], $row['hr_price_formula']);
                $formula = str_replace('hrs', $row['nb_hrs'], $formula);
                //                echo $formula.'<br>';
                if ($row['nb_hrs'])
                    try {
                        eval('$unit_hr = ' . $formula . ';');
                    } catch (Exception $e) {
                        $unit_hr = 0;
                    }
                $row['unit_hr'] = $unit_hr;
            }
//                        echo $unit_hr . '==';
            $data[] = $row;
        }
//                echo"<pre>";
//                die(print_r($data));
        return $data;
    }

    public function viewDeduction($id = null) {
        $cond = '';
        $data = [];
        if (!is_null($id)) {

            $cond = ' Where Deduction_ID = "' . $id . '"';
        }
        $query = $this->db->query('SELECT 
                                        d.Deduction_ID AS Entry_id,
                                        d.Deduction_Description,
                                        d.Deduction_Amount AS Amount,
                                        d.Employee_ID,
                                        d.Deduction_Date as Date,
										c.*,
										CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name
									FROM
										tbl_hrm_deductions d
									LEFT JOIN 
										tbl_hrm_employee e
									ON 
										d.Employee_ID = e.Employee_ID
									LEFT JOIN 
										tbl_common_currency c
									ON 
										d.Currency_ID = c.Currency_ID
				' . $cond);
        while ($row = $this->db->assoc($query))
            $data = $row;
        return $data;
    }

    public function viewsBonus($id = null) {
        $cond = '';
        $data = [];
        if (!is_null($id)) {

            $cond = ' Where Bonus_ID = "' . $id . '"';
        }
        $query = $this->db->query('SELECT 
                                        d.Bonus_ID AS Entry_id,
                                        d.Bonus_Description as Deduction_Description,
                                        d.Bonus_Amount AS Amount,
                                        d.Employee_ID,
                                        d.Bonus_Date as Date,
										c.*,
										CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name
									FROM
										tbl_hrm_bonuses d
									LEFT JOIN 
										tbl_hrm_employee e
									ON 
										d.Employee_ID = e.Employee_ID
									LEFT JOIN 
										tbl_common_currency c
									ON 
										d.Currency_ID = c.Currency_ID
				' . $cond);
        while ($row = $this->db->assoc($query))
            $data = $row;
        return $data;
    }

    public function deletededcution() {
        $Deduction_ID = $this->info['GetID'];
        $this->db->prepare_Stmt('DELETE FROM `tbl_hrm_deductions` WHERE Deduction_ID = ? ', [&$Deduction_ID]);
        $error = $this->db->error();
        return $error;
    }

    public function deletebonus() {
        $Bonus_ID = $this->info['GetID'];
        $this->db->prepare_Stmt('DELETE FROM `tbl_hrm_bonuses` WHERE Bonus_ID = ? ', [&$Bonus_ID]);
        $error = $this->db->error();
        return $error;
    }

    public function deleteAdvancePayment() {
        $Advance_ID = $this->info['GetID'];
        $query = $this->db->query('select Advance_Payment_Date,is_actual 
            from tbl_hrm_advance_payments WHERE Advance_Payment_ID = "' . $Advance_ID . '"');
        $row = $this->db->assoc($query);
        $date = $row['Advance_Payment_Date'];
        $is_actual = $row['is_actual'];
        $check = CommonFunctions::check_lock_data(['type' => 'advance', 'start_date' => $date, 'is_actual' => $is_actual]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');

        $this->db->prepare_Stmt('DELETE FROM `tbl_hrm_advance_payments` WHERE Advance_Payment_ID = ? ', [&$Advance_ID]);

        $this->db->prepare_Stmt('DELETE FROM `tbl_accounting_transactions` WHERE main_type= "eap" and main_id = ? ', [&$Advance_ID]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function viewAdvancePayment($id = null) {

        $cond = '';
        $data = [];
        if (!is_null($id)) {

            $cond = ' Where Advance_Payment_ID = "' . $id . '"';
        }
        $query = $this->db->query('SELECT 
                                        d.*,
                                        p.Payment_Type_Name,
                                        p.Account_Code,
                                        c.Currency_Name,
                                        c.Currency_Code,
                                        c.Currency_Symbol,
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                        concat(v.Paygrade_Value," ",v.Currency_Symbol) AS Basic_Salary
                                FROM
                                        tbl_hrm_advance_payments d
                                LEFT JOIN 
                                        tbl_hrm_employee e
                                ON 
                                        d.Employee_ID = e.Employee_ID
                                LEFT JOIN 
                                        tbl_common_currency c
                                ON 
                                        d.Currency_ID = c.Currency_ID
                                left Join
                                        tbl_payment_types p 
                                on
                                        p.Payment_Type_ID = d.Payment_ID
                                left join
                                        view_employees v
                                 on 
                                            v.Employee_ID = d.Employee_ID       
				' . $cond);
        while ($row = $this->db->assoc($query))
            $data = $row;
        return $data;
    }

    public function updateAdvancePayment($flag = 0) {
        $check = CommonFunctions::check_lock_data(['type' => 'advance', 'start_date' => $this->info['Advance_Payment_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }

        $this->db->query('START TRANSACTION');
        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);

        $data = [&$this->info['Advance_Payment_Date'],
            &$this->info['Advance_Payment_Amount'],
            &$this->info['Advance_Payment_Description'],
            &$this->info['Currency_ID'],
            &$this->info['Employee_ID'],
            &$this->info['Payment_ID'],
            &$this->info['is_actual'],
            &$this->info['Logged_ID'], &$rate, &$second_rate, &$this->info['Advance_Payment_ID']];
        $this->db->prepare_Stmt('update 
                                        tbl_hrm_advance_payments set
                                Advance_Payment_Date = ?,
                                Advance_Payment_Amount = ?,
                                Advance_Payment_Description = ?,
                                Currency_ID = ?,
                                Employee_ID = ?,
                                Payment_ID = ?,
                                is_actual=?,
                                Logged_ID = ?,current_rate=?,second_currency_rate=?
                                where Advance_Payment_ID = ?', $data);

        $desc = 'Paid Advance for :' . $this->info['Employee_Name'];
        $ref = str_replace("-", "", $this->info['Advance_Payment_Date']);
        $currency_id = $this->info['Currency_ID'];
        $ad_id = $this->info['Advance_Payment_ID'];

        $query_approved = $this->db->query('select is_approved from tbl_hrm_advance_payments 
            where Advance_Payment_ID = "' . $this->info['Advance_Payment_ID'] . '"');
        $is_approved = $this->db->assoc($query_approved)['is_approved'];
        if ($is_approved) {
            $this->db->prepare_Stmt('Delete from  
                                        tbl_accounting_transactions 
                                where main_type ="eap" and main_id = ?', [&$ad_id]);

            $jv = JV::returnJV($this->info['is_actual']);
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => "4281001",
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $this->info['Advance_Payment_Amount'],
                "Transaction_Date" => $this->info['Advance_Payment_Date'],
                "Currency_ID" => $currency_id,
                "Logged_ID" => $this->info['Logged_ID'],
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $ad_id,
                "main_type" => "eap"
            );
            JV::addTransaction($data_1, $this->info['is_actual']);

            $data_2 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $this->info['Payment_Account_Code'],
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $this->info['Advance_Payment_Amount'],
                "Transaction_Date" => $this->info['Advance_Payment_Date'],
                "Currency_ID" => $currency_id,
                "Logged_ID" => $this->info['Logged_ID'],
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $ad_id,
                "main_type" => "eap"
            );
            JV::addTransaction($data_2, $this->info['is_actual']);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function viewsLoan($id = null) {
        $cond = '';
        $data = [];
        if (!is_null($id)) {

            $cond = ' Where Loan_ID = "' . $id . '"';
        }
        $query = $this->db->query('SELECT 
                                       *
                                       from 
                                       view_loans
				' . $cond);
        while ($row = $this->db->assoc($query))
            $data = $row;
        return $data;
    }

    public function deleteloan() {
        $this->db->query('START TRANSACTION');
        $Loan_ID = $this->info['GetID'];
        $this->db->prepare_Stmt('DELETE FROM `tbl_hrm_loans` WHERE Loan_ID = ? ', [&$Loan_ID]);

        $this->db->prepare_Stmt('DELETE FROM `tbl_accounting_transactions` WHERE main_type= "lon" and main_id = ? ', [&$Loan_ID]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function updateLoan() {
        $check = CommonFunctions::check_lock_data(['type' => 'loan', 'start_date' => $this->info['Loan_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $data = [&$this->info['Loan_Description'],
            &$this->info['Loan_Amount'],
            &$this->info['Loan_Repayment_Start'],
            //            &$this->info['Loan_Repayment_End'],
            &$this->info['Loan_Repayment_Amount'],
            &$this->info['Loan_Date'],
            &$this->info['Currency_ID'],
            &$this->info['Employee_ID'],
            &$this->info['Payment_ID'], &$rate, &$second_rate,
            &$this->info[0], &$this->info['Loan_ID']];
        $this->db->prepare_Stmt('Update  tbl_hrm_loans set 	Loan_Description = ?,
                                                                Loan_Amount = ?,
                                                                Loan_Repayment_Start = ?,

                                                                Loan_Repayment_Amount = ?,
                                                                Loan_Date = ?,
                                                                Currency_ID = ?,
                                                                Employee_ID = ?,
                                                                Payment_ID = ?,current_rate=?,second_currency_rate=?,
                                                                Logged_ID = ?
                                                   where Loan_ID = ? ', $data);






        $loan_id = $this->info['Loan_ID'];
        $desc = 'Loan for :' . $this->info['Employee_Name'];
        $ref = str_replace("-", "", $this->info['Loan_Date']);
        $currency_id = $this->info['Currency_ID'];
        $query_is_approved = $this->db->query('select is_approved from tbl_hrm_loans 
            where Loan_ID = "' . $this->info['Loan_ID'] . '"');
        $is_approved = $this->db->assoc($query_is_approved)['is_approved'];
        if ($is_approved) {
            $this->db->prepare_Stmt('Delete from  
                                        tbl_accounting_transactions 
                                where main_type ="lon" and main_id = ?', [&$loan_id]);
            $jv = JV::returnJV($this->info['is_actual']);
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => "4281002",
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $this->info['Loan_Amount'],
                "Transaction_Date" => $this->info['Loan_Date'],
                "Currency_ID" => $currency_id,
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => "0",
                "main_id" => $loan_id,
                "main_type" => "lon"
            );
            JV::addTransaction($data_1, $this->info['is_actual']);

            $data_2 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $this->info['Payment_Account_Code'],
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $this->info['Loan_Amount'],
                "Transaction_Date" => $this->info['Loan_Date'],
                "Currency_ID" => $currency_id,
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => "0",
                "main_id" => $loan_id,
                "main_type" => "lon"
            );
            JV::addTransaction($data_2, $this->info['is_actual']);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function get_overtime_by_id($id) {
        $data = array();
        $query = $this->db->query("SELECT 
            o.*,concat(e.Employee_FirstName,' ',e.Employee_MiddleName,' ',e.Employee_LastName) as Employee_Name,
            c.* FROM `tbl_hrm_employee_overtime_hours` o join tbl_hrm_employee e 
            on e.Employee_ID = o.Employee_ID
            join tbl_common_currency c on c.Currency_ID = o.Currency_ID where o.Overtime_ID = '" . $id . "'");
        $row = $this->db->assoc($query);
        if (!empty($row)) {
            $row['duraton_time'] = ((floor($row['Overtime_Duration'] / 24) * 24) + gmdate('H', floor($row['Overtime_Duration'] * 3600))) . ':' . gmdate('i', floor($row['Overtime_Duration'] * 3600));
            $data = $row;
        }
        //        echo"<pre>";print_r($data);die();
        return $data;
    }

    public function submit_multi_deduction() {

        $this->db->query('START TRANSACTION');
        $all_employees = $this->info['deductions'];
        foreach ($all_employees as $employees) {
            $hr = abs($employees['hrs']);
            $this->info['Employee_ID'] = $employees['Employee_ID'];
            $this->info['Overtime_Currency'] = smc_session()['local_currency'];
            $this->info['Overtime_Start_Time'] = '';
            $this->info['Overtime_End_Time'] = '';
            $this->info['Overtime_Duration'] = $hr; // ((floor($hr / 24) * 24) + gmdate('H', floor($hr * 3600))) . ':' . gmdate('i', floor($hr * 3600));
            $hr_data = $this->returnEmployeeInfoForOverTime($employees['is_actual'], $employees['to_date'], $employees['Employee_ID']);
            $this->info['hr_unit_amount'] = $hr_data[0]['unit_hr'];
            $this->info['hr_rate'] = $hr_data[0]['deduction_rate'];
            $this->info['OverTime_Date'] = $employees['to_date'];
            $this->info['Is_Holiday'] = 0;
            $this->info['is_actual'] = $employees['is_actual'];
            $this->info[0] = smc_session()['user_logged_id'];
            $this->info['is_approved'] = 1;
            $this->info['type'] = $employees['is_cumulative'];
            $this->info['is_deduction'] = $employees['hrs'] > 0 ? 0 : 1;
            $this->info['note'] = 'Automatic Deduction for' . Employee::get_emp_name_by_id($employees['Employee_ID']);
            $this->info['Overtime_Amount'] = $this->info['hr_unit_amount'] * $this->info['hr_rate'] * $hr;
//            echo"<pre>";die(print_r($this->info));
            $this->addOverTime();
        }

        $error = $this->db->error();
//        echo"<pre>";die()print_r()
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function approve_advance_payment() {
        $this->db->query('START TRANSACTION');
        $query = $this->db->query('select * from advance_payment_list where Advance_Payment_ID = "' . $this->info['id'] . '"');
        $row = $this->db->assoc($query);
        $currency_id = $row['Currency_ID'];
        $second_rate = !empty($row['second_currency_rate']) ? $row['second_currency_rate'] : CurrencyFactory::getSecondCurrencyLastExRate($currency_id);

        $this->db->prepare_Stmt('update tbl_hrm_advance_payments set is_approved = ?,second_currency_rate=? where Advance_Payment_ID = ?', [&$this->info['status'], &$second_rate, &$this->info['id']]);
        $ad_id = $this->info['id'];
        $desc = 'Paid Advance for :' . $row['employee_name'];
        $ref = str_replace("-", "", $row['Advance_Payment_Date']);

        $jv = JV::returnJV($row['is_actual']);
        $codes = ["42810"];
        $ids_arr = JV::getAccountIDsByCodes($codes);
        $data_1 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $ids_arr["42810"],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $row['Advance_Payment_Amount'],
            "Transaction_Date" => $row['Advance_Payment_Date'],
            "Currency_ID" => $currency_id,
            "Logged_ID" => $row['Logged_ID'],
            "Transaction_Description" => $desc,
            "Current_Ex_Rate" => $row['current_rate'],
            "second_currency_rate" => $second_rate,
            "main_id" => $ad_id,
            "main_type" => "eap"
        );
        JV::addTransaction($data_1, $row['is_actual']);
        //  $error = $this->db->error();
        //        die(print_r($error));
        $data_2 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $row['Payment_ID'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $row['Advance_Payment_Amount'],
            "Transaction_Date" => $row['Advance_Payment_Date'],
            "Currency_ID" => $currency_id,
            "Logged_ID" => $row['Logged_ID'],
            "Transaction_Description" => $desc,
            "Current_Ex_Rate" => $row['current_rate'],
            "second_currency_rate" => $second_rate,
            "main_id" => $ad_id,
            "main_type" => "eap"
        );
        JV::addTransaction($data_2, $row['is_actual']);
        CommonFunctions::update_task_status($ad_id, 'advance');
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function get_loan_plan($loan_id) {
        $data = array();
        $query = $this->db->query('select * from view_loans where Loan_ID = "' . $loan_id . '"');
        $row = $this->db->assoc($query);
        $start_date = $row['Loan_Repayment_Start'];
        $repayment_amount = $row['Loan_Repayment_Amount'];
        $loan_amount = $row['Loan_Amount'];
        $first_payment = date("Y-m-d", strtotime(date("Y-m-d", strtotime($start_date)) . ", last day of this month"));
        $counter = $loan_amount;
//        die($repayment_amount.'('.$loan_amount.' - '.$counter.')');
        for ($i = 0; $counter > 0; $i++) {
            $pay_amount = min($repayment_amount, $counter);
//            echo $pay_amount.'<br>';
            $data[$i]['amount'] = $pay_amount;

            if ($i == 0) {
                $data[$i]['date'] = $first_payment;
            } else {
//                $data[$i]['amount'] = $repayment_amount;
                $data[$i]['date'] = date("Y-m-d", strtotime(date("Y-m-d", strtotime($data[$i - 1]['date'])) . ", last day of +1 month"));
            }
            $counter-=$pay_amount;
//            echo $counter.'<br>';
        }
        return ['data' => $data, 'emp' => $row];
    }

    public function delete_overtime($id) {

        $old_query = $this->db->query('select * from tbl_hrm_employee_overtime_hours 
            where Overtime_ID = "' . $id . '"');
        $row = $this->db->assoc($old_query);
        $check = CommonFunctions::check_lock_data(['type' => 'overtime', 'start_date' => $row['OverTime_Date'], 'is_actual' => $row['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $query = $this->db->query('select * from tbl_hrm_payroll
        where Employee_ID = "' . $row['Employee_ID'] . '" 
        and   "' . $row['OverTime_Date'] . '" between from_date and  Payroll_Date ');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            return [CommonLanguage::getMessage('payroll_overtime_overlap')];
        }
        $this->db->prepare_Stmt('delete from tbl_hrm_employee_overtime_hours 
            where Overtime_ID = ? ', [&$id]);
        $error = $this->db->error();
        return $error;
    }

    public function salesman_commission_report($commission_id, $from_date, $to_date, $emp_arr = '', $type = 'hrm') {
        $data = array();
        $final_data = array();
        $comm_arr = array();
        $query = $this->db->query('select * from tbl_commission_type_details 
            where commission_id = "' . $commission_id . '"');
        while ($row = $this->db->assoc($query)) {
            $comm_arr[] = $row;
        }
        $cond = ' ';
        if (!empty($emp_arr))
            $cond.=' and i.Sales_ID in (' . $emp_arr . ') ';
        $query = $this->db->query($x='select i.Sales_ID,
            CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
            sum((i.Invoice_Net_Total+i.Invoice_Shipping)*i.current_rate) as total_invoices,i.Invoice_Date
            from tbl_invoices i join tbl_hrm_employee e on e.Employee_ID = i.Sales_ID
            where i.Invoice_Date between "' . $from_date . '" and "' . $to_date . '" and i.is_actual = "' . $this->info['is_actual'] . '"
                ' . $cond . ' group by i.Sales_ID,i.Invoice_Date');
        while ($row = $this->db->assoc($query)) {
            if (empty($data[$row['Sales_ID']])) {
                $data[$row['Sales_ID']] = array('employee_name' => $row['Employee_Name'],
                    'total_invoices' => $row['total_invoices'],
                    'total_returns' => 0, 'balance' => $row['total_invoices'],
                    'commission' => 0, 'data' => [$row['Invoice_Date'] => $row['total_invoices']]);
            } else {
                $data[$row['Sales_ID']]['total_invoices']+= $row['total_invoices'];
                $data[$row['Sales_ID']]['balance']+= $row['total_invoices'];
                if (!empty($data[$row['Sales_ID']]['data'][$row['Invoice_Date']]))
                    $data[$row['Sales_ID']]['data'][$row['Invoice_Date']]+= $row['total_invoices'];
                else
                    $data[$row['Sales_ID']]['data'][$row['Invoice_Date']] = $row['total_invoices'];
            }
        }

        $query = $this->db->query('select i.Sales_ID,
            CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
            sum(i.Return_Net_Total*i.current_rate) as total_returns,i.Return_Invoice_Date
            from tbl_accounting_return_invoices i join tbl_hrm_employee e on e.Employee_ID = i.Sales_ID
            where i.Return_Invoice_Date between "' . $from_date . '" and "' . $to_date . '" and i.Is_Actual = "' . $this->info['is_actual'] . '"
                ' . $cond . ' group by i.Sales_ID,i.Return_Invoice_Date');
        while ($row = $this->db->assoc($query)) {
            if (empty($data[$row['Sales_ID']])) {
                $data[$row['Sales_ID']] = array('employee_name' => $row['Employee_Name'],
                    'total_invoices' => 0,
                    'total_returns' => $row['total_return'], 'balance' => $row['total_returns'],
                    'commission' => 0, 'data' => [$row['Return_Invoice_Date'] => $row['total_returns']]);
            } else {
                $data[$row['Sales_ID']]['total_returns']+= $row['total_returns'];
                $data[$row['Sales_ID']]['balance']-= $row['total_returns'];
                if (!empty($data[$row['Sales_ID']]['data'][$row['Return_Invoice_Date']]))
                    $data[$row['Sales_ID']]['data'][$row['Return_Invoice_Date']]-= $row['total_returns'];
                else
                    $data[$row['Sales_ID']]['data'][$row['Return_Invoice_Date']] = $row['total_returns'];
            }
        }
        $total_commission = 0;
        $row_arr =[ $comm_arr ,$data];
        foreach ($data as $sales_id => $row) {
            $commission = 0;
            if ($row['balance'] > 0) {
                foreach ($comm_arr as $com_row) {
                    //if daily option
                    if ($com_row['commission_option'] == 1) {
                        foreach ($row['data'] as $total_per_date) {
                            if (empty($com_row['condition_operation']) && empty($com_row['condition_value'])) {
                                //if type of commission is percent
                                if ($com_row['commission_type'] == 1)
                                    $commission+= ($com_row['commission_amount'] * $total_per_date / 100);
                                else//if type of commission is value
                                    $commission+= $com_row['commission_amount'];
                            } else {
                                // if cond operation < and total < cond value or cond operation > and total > cond value
                                if (($com_row['condition_operation'] == 2 && $total_per_date < $com_row['condition_value']) || ($com_row['condition_operation'] == 1 && $total_per_date > $com_row['condition_value']) || ($com_row['condition_operation'] == 3 && $total_per_date > $com_row['condition_value'] && $total_per_date <= $com_row['condition_value_to'])) {
                                    //if type of commission is percent
                                    if ($com_row['commission_type'] == 1) {
                                    //   echo $com_row['commission_amount'].'==='.$total_per_date.'===';
                                        $commission+= ($com_row['commission_amount'] * $total_per_date / 100);
                                    } else//if type of commission is value
                                        $commission+= $com_row['commission_amount'];
                                }
                            }
                        }
                        //if monthly option
                    }else if ($com_row['commission_option'] == 2) {
                        if (empty($com_row['condition_operation']) && empty($com_row['condition_value'])) {
                            //if type of commission is percent
                            if ($com_row['commission_type'] == 1)
                                $commission+= ($com_row['commission_amount'] * $row['balance'] / 100);
                            else//if type of commission is value
                                $commission+= $com_row['commission_amount'];
                        } else {
                            // if cond operation < and total < cond value or cond operation > and total > cond value
                            if (($com_row['condition_operation'] == 2 && $row['balance'] < $com_row['condition_value']) || ($com_row['condition_operation'] == 1 && $row['balance'] > $com_row['condition_value'])) {
                                //if type of commission is percent
                                if ($com_row['commission_type'] == 1)
                                    $commission+= ($com_row['commission_amount'] * $row['balance'] / 100);
                                else//if type of commission is value
                                    $commission+= $com_row['commission_amount'];
                            }
                        }
                        //if weekly option
                    }else if ($com_row['commission_option'] == 3) {

                        $count = 0; // to get the first element of an array 
                        $start_day_week = ''; 
                        $end_day_week = '';
                        $is_week = false;
                        $total_week = 0; // total invoice every week to get a commission
                        $date_count = count($row['data']);// every while loop like a week
                        $check_day = array();// skip any day we get their invoice
                        $next_week = false;//start a new week
                        $end_arr =  array_keys($row['data'])[count($row['data']) - 1];// get the last key on the array to finish the week
                        while ($date_count > 0) {// looping week by week to absolutely we don't skip any day
                            foreach ($row['data'] as $key => $total_per_date) {
                                //if we check day  skip it and go to the next day
                                if (in_array($key, $check_day)) {
                                    continue;
                                }
                                // if the first count modify start/end weekday or we do next week turn off to start a new week
                                if ($count == 0 || $next_week) {
                                    if ($next_week) {

                                        $is_week = false;
                                        $next_week = false;
                                    }
                                    $start_day_week = $key;
                                    $end_day_week = date('Y-m-d', strtotime($key . ' + 7 days'));
                                }
                                //check if current day(key) is same week if same week then we add to check day
                                // else so we start a new week and turn on next week to get their commission
                                if ($key >= $start_day_week && $key <= $end_day_week) {
                                    $check_day[] = $key;
                                    $total_week += $total_per_date;
                                } else {
                                    $count = 0;
                                    $is_week = true;
                                    $next_week = true;
                                }
                                // if is the end of the array so we turn on next week to get their commission
                                if ($end_arr == $key) {
                                    $count = 0;
                                    $is_week = true;
                                    $next_week = true;
                                }

                                if ($is_week) {
                                    if (empty($com_row['condition_operation']) && empty($com_row['condition_value'])) {
                                        //if type of commission is percent
                                        if ($com_row['commission_type'] == 1)
                                            $commission += ($com_row['commission_amount'] * $total_week / 100);
                                        else //if type of commission is value
                                            $commission += $com_row['commission_amount'];
                                    } else {
                                        // if cond operation < and total < cond value or cond operation > and total > cond value
                                        if (($com_row['condition_operation'] == 2 && $total_week < $com_row['condition_value']) || ($com_row['condition_operation'] == 1 && $total_per_date > $com_row['condition_value']) || ($com_row['condition_operation'] == 3 && $total_per_date > $com_row['condition_value'] && $total_per_date <= $com_row['condition_value_to'])) {
                                            //if type of commission is percent
                                            if ($com_row['commission_type'] == 1) {
                                                //   echo $com_row['commission_amount'].'==='.$total_per_date.'===';
                                                $commission += ($com_row['commission_amount'] * $total_week / 100);
                                            } else //if type of commission is value
                                                $commission += $com_row['commission_amount'];
                                        }
                                    }
                                    $is_week = false;
                                    $total_week = 0;
                                    break;
                                }
                                $count++;
                            }
                            $total_week = 0;
                            $date_count -= 1;
                        }

                    }
                }
            }
            $total_commission+=$commission;
            if ($this->permissions->hasPermission('submit_commission_bonus') && $type == 'hrm') {
                $data[$sales_id]['commission'] = '<input type="number" min="0" step="any" value="' . $commission . '" class="commission form-control" data-emp="' . $sales_id . '"/>';
            } else {
                $data[$sales_id]['commission'] = CommonFunctions::formatNumber($commission) . ' ' . smc_session()['local_currency_symbol'];
            }
            $data[$sales_id]['total_invoices'] = CommonFunctions::formatNumber($data[$sales_id]['total_invoices']) . ' ' . smc_session()['local_currency_symbol'];
            $data[$sales_id]['total_returns'] = CommonFunctions::formatNumber($data[$sales_id]['total_returns']) . ' ' . smc_session()['local_currency_symbol'];
            $data[$sales_id]['balance'] = CommonFunctions::formatNumber($data[$sales_id]['balance']) . ' ' . smc_session()['local_currency_symbol'];

            unset($data[$sales_id]['data']);
            $final_data[] = array_values($data[$sales_id]);
        }
        $total_commission = CommonFunctions::formatNumber($total_commission) . ' ' . smc_session()['local_currency_symbol'];
//        echo"<pre>";
//        die(print_r($final_data));
        return ['data' => $final_data, 'total' => $total_commission,'row_arr'=>$row_arr ];
    }

}
