<?php 
dol_include_once('/core/lib/admin.lib.php');

class paiedolibarr_paies extends Commonobject{ 

    public $errors = array();
    public $rowid;
    public $period;
    public $fk_user;
    public $ref;
    public $label;
    public $comment;
    public $amounttype;
    public $amount;
    public $formule;
    public $total;
    public $salairebrut;
    public $salairenet;
    public $netapayer;
    public $entity;

    public $element='paiedolibarr_paies';
    public $table_element='paiedolibarr_paies';

    public function __construct($db){ 
        global $langs;

        $this->db = $db;
        $this->rulescategory = [
            'BRUT'              => $langs->trans('BRUT'),
            'RETENUES'          => $langs->trans('RETENUES'),
            'INDEMNITES'        => $langs->trans('INDEMNITES'),
            'AUTRESRETENUES'    => $langs->trans('AUTRESRETENUES')
        ];
        
        return 1;
    }





    public function editcalculatePaieRules($payrules=array())
    {
        global $user, $conf;

        $paierule    = new paiedolibarr_paiesrules($this->db);
        
        $substitutionarray = array();
        $substitutionarray['USER_SALARY'] = 0;
        $substitutionarray['YEARS_IN_COMPANY'] = 0;

        $employee = new User($this->db);
        $employee->fetch($this->fk_user);

        if($employee){
            $yearincomp = $this->getYearsInCompany($this, $employee->dateemployment);
            $substitutionarray['YEARS_IN_COMPANY'] = $yearincomp;
        }

        foreach ($payrules as $key => $rule) {
            if($rule['code'] == 'SALAIRE_BASE') {
                $substitutionarray['USER_SALARY'] = $rule['total'];
            }

            $total = round($rule['total'], 4);
            if($rule['amounttype'] == 'CALCULATED' && $rule['code'] != 'SALAIRE_BASE') {
                $str = make_substitutions($rule['formule'], $substitutionarray);
                if (!@eval('$result = (' . $str . ');')){
                    $total = round($result,4);
                }
            }
            $substitutionarray[$rule['code']] = $total;

            $dr = array(
                'label'             => addslashes($rule['label'])
                ,'total'            => $total
            );

            $isr = $paierule->update($key, $dr);
        }
        // $payrules = $this->getRules();

        if($payrules){
            $data = array();
            $data = $this->getNetBrutNetAPayer($fk_paie);
            $isvalid = $this->update($fk_paie, $data);
        }
    }





    public function calculatePaieRules($fk_paie=0)
    {
        global $user, $conf;

        $substitutionarray = array();
        $substitutionarray['USER_SALARY'] = 0;
        $substitutionarray['YEARS_IN_COMPANY'] = 0;

        $employee = new User($this->db);
        $employee->fetch($this->fk_user);

        if($employee){
            $substitutionarray['USER_SALARY'] = $employee->salary;
            $yearincomp = $this->getYearsInCompany($this, $employee->dateemployment);
            // echo $yearincomp;die;
            $substitutionarray['YEARS_IN_COMPANY'] = $yearincomp;
        }

        $payrules = $this->getRules();

        if($payrules){
            $substitutionarray = $this->setTotalFixedRules($payrules, $substitutionarray);
            $substitutionarray = $this->setTotalCalculatedRules($fk_paie, $substitutionarray);

            // print_r($substitutionarray);die;
            foreach ($payrules as $key => $rule) {
                $dr = array(
                    'code'              => $rule->code,
                    'label'             => addslashes($rule->label)
                    ,'amounttype'       => $rule->amounttype
                    ,'formule'          => $rule->formule
                    ,'fk_paie'          => $fk_paie
                    ,'category'         => $rule->category
                );

                $dr['total']    = $substitutionarray[$rule->code];

                $paierule    = new paiedolibarr_paiesrules($this->db);
                $isr = $paierule->create($dr);
            }

            $data = array();
            $data = $this->getNetBrutNetAPayer($fk_paie);
            $isvalid = $this->update($fk_paie, $data);
        }
    }

    public function setTotalFixedRules($payrules, $substitutionarray=array())
    {
        foreach ($payrules as $key => $rule) {
            $substitutionarray[$rule->code] = round($rule->amount,4);
        }

        return $substitutionarray;
    }

    public function setTotalCalculatedRules($fk_paie=0, $substitutionarray=array())
    {
        global $conf;

        $sql = "SELECT * FROM ".MAIN_DB_PREFIX."paiedolibarr_rules ";
        $sql .= " WHERE amounttype = 'CALCULATED'";
        $sql .= " AND entity=".$conf->entity;

        // $sql .= " ORDER BY ordercalcul ASC";
        $sql .= " ORDER BY rowid ASC";
        $resql = $this->db->query($sql);

        if($resql){
            while ($obj = $this->db->fetch_object($resql)) {
                $total = round($obj->amount, 4);
                $str = make_substitutions($obj->formule, $substitutionarray);
                if (!@eval('$result = (' . $str . ');')){
                    $total = round($result,4);
                }
                $substitutionarray[$obj->code] = $total;
            }
        }

        return $substitutionarray;
    }

    public function getYearsInCompany($paieobj, $dateemployment)
    {
        if($paieobj->period){
            $periods = explode('-', $paieobj->period);

            $periodyear = (($periods && is_array($periods)) ? $periods[0] : 0) + 0;
            $periodmonth = $periods[1];
            $countdays = $this->days_in_month($periodmonth,$periodyear);

            $query_date = $paieobj->period;
            $first = date('01/m/Y', strtotime($query_date));
            $last = date('t/m/Y', strtotime($query_date));
            $lastday = date('Y-m-t', strtotime($query_date));

            $date1 = strtotime($lastday);  
            $date2 = $dateemployment;  
              
            $diff = abs($date2 - $date1);
            $years = floor($diff / (365*60*60*24));
        }

        return $years;
    }




    
    
    public function getAvailableSubstitKey($filter = 0, $object = null)
    {
        global $langs;

        $langs->loadLangs(array('salaries'));

        $tmparray = array();

        $helpsubstit = '';


        $helpsubstit .= '<div class="paievariablesdisponibles">';

        $helpsubstit .= '<hr>';
        $helpsubstit .= '<b>'.$langs->trans("ExempleFormuleCalcul").'</b>:<br><br>';
        $helpsubstit .= '<span class="exemplecondition classfortooltip">';
        $helpsubstit .= ' 1 :  SALAIRE_BASE * 3/100';
        $helpsubstit .= '<br>';
        $helpsubstit .= ' 2 :  (SALAIRE_BASE > 80000) ? 2400 : SALAIRE_BASE * 3/100'.' '.info_admin($langs->trans('conditionexemple'), 1);;
        $helpsubstit .= '<br>';
        $helpsubstit .= ' 3 :  (SALAIRE_BASE > 80000) ? ((SALAIRE_BASE < 90000) ? 2400 : 3400) : SALAIRE_BASE * 3/100'.' '.info_admin($langs->trans('conditionexemple'), 1);
        $helpsubstit .= '</span>';
        $helpsubstit .= '</br>';
        $helpsubstit .= '<hr>';
        $helpsubstit .= '</br>';

        $helpsubstit .= '<b>'.$langs->trans("paieAvailableVariables").'</b>:<br><br>';

        $helpsubstit .= '<table class="noborder">';

        $helpsubstit .= '<tr class="impair">';
        $helpsubstit .= '<td class="nowrap">USER_SALARY</td>  <td>'.$langs->trans("Salary").'</td>';
        $helpsubstit .= '</tr>';
        $helpsubstit .= '<tr class="impair">';
        $helpsubstit .= '<td class="nowrap">YEARS_IN_COMPANY</td>  <td>'.$langs->trans("paieyearsincompany").'</td>';
        $helpsubstit .= '</tr>';
        $helpsubstit .= '<tr><td colspan="2"></td></tr>';


        $helpsubstit .= '<tr><td colspan="2"><b>'.$langs->trans("paieOthersAvailableVariables").'</b>:</td></tr>';

        $payrules = $this->getRules($filter);

        foreach ($payrules as $key => $rule)
        {
            $helpsubstit .= '<tr class="impair">';
            $helpsubstit .= '<td class="nowrap">'.$rule->code.'</td>';
            $helpsubstit .= '<td>';
            $helpsubstit .= '<a target="_blank" href="'.dol_buildpath('/paiedolibarr/rules/card.php?id='.$rule->rowid,2).'" >';
            $helpsubstit .= trim($rule->label);
            $helpsubstit .= '</a>';
            if($rule->amounttype == 'CALCULATED')
                $helpsubstit .= ' = '.$rule->formule;
            $helpsubstit .= '</td>';

            $helpsubstit .= '</tr>';
        }

        $helpsubstit .= '</table>';

        $helpsubstit .= '</div>';

        return $helpsubstit;
    }

    public function getNetBrutNetAPayer($fk_paie=0)
    {
        global $conf;

        $returned = array();
        $payrules = $this->getRulesOfPaie($this->id);

        $substitutionarray = array();
        foreach ($payrules as $key => $rule) {
            $substitutionarray[$rule->code] = round($rule->total,4);
        }

        $formsalairebrut    = $conf->global->PAIEDOLIBARR_FORM_CALCUL_SALAIREBRUT;
        $formsalairenet     = $conf->global->PAIEDOLIBARR_FORM_CALCUL_SALAIRENET;
        $formnetapayer      = $conf->global->PAIEDOLIBARR_FORM_CALCUL_NETAPAYER;

        $returned['salairebrut']    = 0;
        $returned['salairenet']     = 0;
        $returned['netapayer']      = 0;

        $str = make_substitutions($formsalairebrut, $substitutionarray);
        if (!@eval('$result = (' . $str . ');')){
            $returned['salairebrut'] = round($result,4);
        }
        $str = make_substitutions($formsalairenet, $substitutionarray);
        if (!@eval('$result = (' . $str . ');')){
            $returned['salairenet'] = round($result,4);
        }
        $str = make_substitutions($formnetapayer, $substitutionarray);
        if (!@eval('$result = (' . $str . ');')){
            $returned['netapayer'] = round($result,4);
        }

        return $returned;
    }

    public function getRulesOfPaie($fk_paie=0)
    {
        global $conf;
        $ftable = 'paiedolibarr_paiesrules';

        $sql = "SELECT * FROM ".MAIN_DB_PREFIX.$ftable." ";
        $sql .= " WHERE 1 = 1";

        $sql .= " AND fk_paie = ".$fk_paie;

        $sql .= " ORDER BY rowid ASC";
        $resql = $this->db->query($sql);

        $rows = array();
        if($resql){
            while ($obj = $this->db->fetch_object($resql)) {
                $line = new stdClass;
                $line->rowid            = $obj->rowid;
                $line->fk_paie          = $obj->fk_paie;
                $line->code             = $obj->code;
                $line->label            = $obj->label;
                $line->category         = $obj->category;
                $line->amounttype       = $obj->amounttype;
                $line->formule          = $obj->formule;
                $line->total            = $obj->total;

                $rows[] = $line;
            }
        }

        return $rows;
    }

    public function getRulesOfPaieByCateg($fk_paie=0)
    {
        global $conf;
        $ftable = 'paiedolibarr_paiesrules';

        $sql = "SELECT * FROM ".MAIN_DB_PREFIX.$ftable." ";
        $sql .= " WHERE 1=1";
        $sql .= " AND fk_paie = ".$fk_paie;

        $sql .= " ORDER BY category ASC";
        $resql = $this->db->query($sql);
        // echo $sql;
        $rows = array();

        if($resql){
            while ($obj = $this->db->fetch_object($resql)) {
                $line = new stdClass;
                $line->rowid            = $obj->rowid;

                if(!empty($fk_paie))
                    $line->fk_paie      = $obj->fk_paie;

                $line->code             = $obj->code;
                $line->label            = $obj->label;
                $line->category         = $obj->category;
                $line->amounttype       = $obj->amounttype;
                $line->formule          = $obj->formule;
                $line->total            = $obj->total;
                
                $rows[$obj->category][$obj->code] = $line;
            }
        }

        return $rows;
    }

    public function getRulesByCateg()
    {
        global $conf;
        $ftable = 'paiedolibarr_rules';

        $sql = "SELECT * FROM ".MAIN_DB_PREFIX.$ftable." ";
        $sql .= " WHERE 1=1";

        $sql .= " ORDER BY category ASC";
        $resql = $this->db->query($sql);
        // echo $sql;
        $rows = array();

        if($resql){
            while ($obj = $this->db->fetch_object($resql)) {
                $line = new stdClass;
                $line->rowid            = $obj->rowid;

                if(!empty($fk_paie))
                    $line->fk_paie      = $obj->fk_paie;

                $line->code             = $obj->code;
                $line->label            = $obj->label;
                $line->category         = $obj->category;
                $line->amounttype       = $obj->amounttype;
                $line->formule          = $obj->formule;
                
                $rows[$obj->category][] = $line;
            }
        }

        return $rows;
    }

    public function getRules($filter = null)
    {
        global $conf;
        $ftable = 'paiedolibarr_rules';

        $sql = "SELECT * FROM ".MAIN_DB_PREFIX.$ftable." ";
        $sql .= " WHERE 1 = 1";

        if($filter) $sql .= $filter;

        $sql .= " ORDER BY rowid ASC";
        $resql = $this->db->query($sql);

        $rows = array();
        if($resql){
            while ($obj = $this->db->fetch_object($resql)) {
                $line = new stdClass;
                $line->rowid            = $obj->rowid;
                // $line->fk_paie          = $obj->fk_paie;
                $line->code             = $obj->code;
                $line->label            = $obj->label;
                $line->category         = $obj->category;
                $line->amounttype       = $obj->amounttype;
                $line->amount           = $obj->amount;
                $line->formule          = $obj->formule;
                $line->total            = $obj->total;

                $rows[] = $line;
            }
        }

        return $rows;
    }
    public function create($echo_sql=0,$insert)
    {

        $sql  = "INSERT INTO " . MAIN_DB_PREFIX .get_class($this)." ( ";

        $notint = ['code'=>'code'];

        foreach ($insert as $column => $value) {
            $alias = (!isset($notint[$column]) && is_numeric($value)) ? "" : "'";
            if($value != ""){
                $sql_column .= " , `".$column."`";
                $sql_value .= " , ".$alias.$value.$alias;
            }
        }

        $sql .= substr($sql_column, 2)." ) VALUES ( ".substr($sql_value, 2)." )";
        // die($sql);
        $resql = $this->db->query($sql);

        if (!$resql) {
            $this->db->rollback();
            $this->errors[] = 'Error '.get_class($this).' '. $this->db->lasterror();
            
            return 0;
        } 
        // return $this->db->db->insert_id;
        return $this->db->last_insert_id(MAIN_DB_PREFIX.'paiedolibarr_paies');
    }

    public function update($id, array $data,$echo_sql=0)
    {
        dol_syslog(__METHOD__, LOG_DEBUG);

        if (!$id || $id <= 0)
            return false;

        $sql = 'UPDATE ' . MAIN_DB_PREFIX .get_class($this). ' SET ';

        $notint = ['code'=>'code'];

        if (count($data) && is_array($data))
            foreach ($data as $key => $val) {
                $val = (!isset($notint[$key]) && is_numeric($val)) ? $val : '"'. $val .'"';
                $val = ($val == '') ? 'NULL' : $val;
                $sql .= '`'. $key. '` = '. $val .',';
            }

        $sql  = substr($sql, 0, -1);
        $sql .= ' WHERE rowid = ' . $id;

        // die($sql);
        $resql = $this->db->query($sql);

        if (!$resql) {
            $this->db->rollback();
            $this->errors[] = 'Error '.get_class($this).' : '. $this->db->lasterror();
            print_r($this->errors);die();
            return -1;
        } 
        return 1;
    }

    public function delete($echo_sql=0)
    {
        global $conf;
        dol_syslog(__METHOD__, LOG_DEBUG);

        $fk_paie = $this->rowid;
        
        $sql   = 'DELETE FROM ' . MAIN_DB_PREFIX .get_class($this).' WHERE rowid = ' . $this->rowid;
        $resql  = $this->db->query($sql);
        
        if (!$resql) {
            $this->db->rollback();
            $this->errors[] = 'Error '.get_class($this).' : '.$this->db->lasterror();
            return -1;
        } 

        $sql    = 'DELETE FROM ' . MAIN_DB_PREFIX .'paiedolibarr_paiesrules WHERE fk_paie = ' . $fk_paie;
        $resql  = $this->db->query($sql);

        return 1;
    }

    
    public function fetchAll($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, $filter = '', $filtermode = 'AND')
    {
        global $conf;
        dol_syslog(__METHOD__, LOG_DEBUG);
        $sql = "SELECT * FROM ";
        $sql .= MAIN_DB_PREFIX .get_class($this);
        $sql .= " WHERE entity = ".$conf->entity;
        if (!empty($filter)) {
            $sql .= " ".$filter;
        }
        if (!empty($sortfield)) {
            $sql .= $this->db->order($sortfield, $sortorder);
        }
        if (!empty($limit)) {
            if($offset==1)
                $sql .= " limit ".$limit;
            else
                $sql .= " limit ".$offset.",".$limit;               
        }

        // echo $sql;
        // die;
        $this->rows = array();
        $resql = $this->db->query($sql);

        if ($resql) {
            $num = $this->db->num_rows($resql);

            while ($obj = $this->db->fetch_object($resql)) {
                $line = new stdClass;
                $line->id               = $obj->rowid;
                $line->rowid            = $obj->rowid;
                $line->period           = $obj->period;
                $line->fk_user          = $obj->fk_user;
                $line->ref              = $obj->ref;
                $line->label            = $obj->label;
                $line->salairebrut      = $obj->salairebrut;
                $line->salairenet       = $obj->salairenet;
                $line->netapayer        = $obj->netapayer;
                $line->comment          = $obj->comment;
                $line->entity           = $obj->entity;

                $this->rows[]   = $line;
            }
            $this->db->free($resql);

            return $num;
        } else {
            $this->errors[] = 'Error ' . $this->db->lasterror();
            dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);

            return -1;
        }
    }


    public function fetch($id)
    {
        global $conf;
        dol_syslog(__METHOD__, LOG_DEBUG);

        $sql = 'SELECT * FROM ' . MAIN_DB_PREFIX .get_class($this). ' WHERE rowid = ' . $id;
        $sql .= ' AND entity = '.$conf->entity;
        $resql = $this->db->query($sql);
        if ($resql) {
            $numrows = $this->db->num_rows($resql);
            
            if ($numrows) {
                $obj                    = $this->db->fetch_object($resql);
                $this->id               = $obj->rowid;
                $this->rowid            = $obj->rowid;
                $this->period           = $obj->period;
                $this->fk_user          = $obj->fk_user;
                $this->ref              = $obj->ref;
                $this->label            = $obj->label;
                $this->salairebrut      = $obj->salairebrut;
                $this->salairenet       = $obj->salairenet;
                $this->netapayer        = $obj->netapayer;
                $this->comment          = $obj->comment;
                $this->entity           = $obj->entity;
            }

            $this->db->free($resql);

            if ($numrows) {
                return 1 ;
            } else {
                return 0;
            }
        } else {
            $this->errors[] = 'Error ' . $this->db->lasterror();
            dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
            return -1;
        }
    }

    public function selectCategories($slctd='',$name='category',$showempty=0,$withbasic=true)
    {
        global $langs;
        $rulescateg = $this->rulescategory;
        $select ='<select class="select_'.$name.'" name="'.$name.'" >';

            if($showempty) $select .='<option value="0"></option>';

            foreach ($rulescateg as $keyr => $namer) {
                $slctdt = ($keyr == $slctd) ? 'selected' : '';
                $select .='<option value="'.$keyr.'" '.$slctdt.'>'.$namer.'</option>';
            }
        $select .='</select>';
        return $select;
    }

    public function days_in_month($month, $year)
    {
        return $month == 2 ? ($year % 4 ? 28 : ($year % 100 ? 29 : ($year % 400 ? 28 : 29))) : (($month - 1) % 7 % 2 ? 30 : 31);
    }

}