<?php
class CustomerRewardsRepository extends EntityRepository {
    
    private $table = 'rewards_customers';
    private $options = array (        
        'phone'=>null,
        'name' => null,
        'last_name' => null,
        'date_of_birth' => null,
        'rewards_total' => 0,
        'rewards' => 0,
        'acumulated_amount' => 0,
        'email' => null,
        'contact_type' => null,
        'register_store' => null,
        'code' => null,
        'status'=>null   
     );
    
    public function setOptions($data){
        foreach ($this->options as $option => $value){
            if(isset($data[$option])){
              $this->options[$option] = $data[$option];
            }
        }
    }
  
    public function getOptions(){
        return $this->options;
    }    

    public function save(array $data, $table = null) {   
        $tools = new Tools();
        $data['date_of_birth'] = $tools->setFormatDateToDB($data['date_of_birth']);
        
        return parent::save($data, $this->table);
    }

    public function update($id, $data, $table = null) {
        $tools = new Tools();
        $data['date_of_birth'] = $tools->setFormatDateToDB($data['date_of_birth']);        
        
        unset($data['rewards_total'],$data['rewards'],$data['acumulated_amount']);
        return parent::update($id, $data, $this->table);
    }

    public function getById($id, $table = null,$selectAux = null) {
        $select = "SELECT * "
                 . "FROM $this->table WHERE id = '$id'";
        $result = $this->query($select);

        if ($result->num_rows>0) {
            $set = $this->resultToArray($result);
            return $set[0];
        }
        return false;
    }
    
    public function getByPhone($phone){
        $query = "SELECT * FROM $this->table WHERE phone = '$phone'";
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            return $this->resultToArray($result)[0];
        }
        return null;
    }
    
    public function delete($id, $table = null) {        
        return parent::delete($id, $this->table);
    }

    public function isUsedInRecord($id, array $buscarEn = null,$andWhere= null) {
        return true;
        return parent::isUsedInRecord($id, array('rewards_records'=>'reward_customer_id'));   
    }
    
     public function getListCustomers($options = null){
        $phone = null;
        $name = null;       
        $email = null;
        $rewards_total = null;
        $rewards = null;
        $store_register = null;
        $status = null;
        $date_of_birth = null;
                
        //$date_of_birth = $this->createFilterFecha(array('fechaInicio'=>$options['date_of_birth_start'],'fechaFin'=>$options['date_of_birth_end']), 'c.date_of_birth');
        $creado_fecha = $this->createFilterFecha(array('fechaInicio'=>$options['creado_fecha_inicio'],'fechaFin'=>$options['creado_fecha_fin']), 'c.creado_fecha');
        if($options){
            if(!empty($options['phone'])){$phone = " AND c.phone = '{$options['phone']}'";}
            if(!empty($options['name'])){$name = " AND CONCAT(c.name,' ',c.last_name) like '%{$options['name']}%'";}
            if(!empty($options['email'])){$email = " AND c.email like '%{$options['email']}%'";}
            
            if(!empty($options['rewards_total'])){$rewards_total = " AND c.rewards_total >= '{$options['rewards_total']}'";}
            if(!empty($options['rewards'])){$rewards = " AND c.rewards = '{$options['rewards']}'";}
            
            if(!empty($options['register_store'])){$store_register = " AND c.register_store = '{$options['register_store']}'";}
            if(!empty($options['status'])){$status = " AND c.status = '{$options['status']}'";}
            
            
            if(!empty($options['date_of_birth_start'])){
                $dobArray = explode('/', $options['date_of_birth_start']);
                $mesInicio = $dobArray[0];
                $diaInicio = $dobArray[1];
                
                $mesFin = $dobArray[0];
                $diaFin = $dobArray[1];
                
                if(!empty($options['date_of_birth_end'])){
                    $dobArray = explode('/', $options['date_of_birth_end']);
                    $mesFin = $dobArray[0];
                    $diaFin = $dobArray[1];
                }
                
                $date_of_birth = " AND ( MONTH(date_of_birth) BETWEEN '$mesInicio' AND '$mesFin' AND DAY(date_of_birth) BETWEEN '$diaInicio' AND '$diaFin'  )";
            }
        }
 
        $query = "SELECT "
                . "c.id,"
                . "c.phone,"
                . "c.name,"
                . "c.last_name,"
                . "c.rewards_total,"
                . "c.rewards,"
                . "c.acumulated_amount,"
                . "c.email,"
                . "c.contact_type,"
                . "s.name as register_store_name,"
                . "DATE_FORMAT(c.date_of_birth,'%m/%d/%Y')as formated_date_of_birth,"
                . "DATE_FORMAT(c.creado_fecha,'%m/%d/%Y')as formated_creado_fecha,"
                . "fxGetStatusName(c.status,'RewardsCustomer')as status_name "
                . "FROM $this->table c "
                . "LEFT JOIN stores s ON c.register_store = s.id "
                . "WHERE 1 = 1 "
                . "$phone "
                . "$name "
                . "$email "
                . "$rewards_total "
                . "$rewards "
                . "$store_register "
                . "$status "
                . "$date_of_birth "
                . "$creado_fecha "
                 . "ORDER BY c.creado_fecha DESC ";
        //echo $query;exit;
        $result = $this->query($query);
        
        if($result){
            return $this->resultToArray($result);           
        }
        
        return null;
    }  
    
    public function getRewardsRecodByCustomerId($customerId){
        $query = "SELECT "
                . "r.sales_id,"
                . "s.name as store_name,"
                . "v.num_venta,"
                . "DATE_FORMAT(v.fecha,'%m/%d/%Y')as fecha,"
                . "GROUP_CONCAT(DISTINCT(CONCAT(p.descripcion,' ',IFNULL(t.descripcion,''))))as products,"
                . "v.total_venta,"
                . "IF(r.operation = 'assign','Asignados','Canjeados')as operation,"
                . "r.points,"
                . "r.amount "
                . "FROM rewards_records r "               
                . "LEFT JOIN ventas v ON r.sales_id = v.id "
                . "LEFT JOIN ventasdetalles d ON d.idVenta = v.id "
                . "LEFT JOIN stores s ON v.idSucursal = s.id "
                . "LEFT JOIN pos_productos p ON d.idProducto = p.id "
                . "LEFT JOIN pos_tamanos t ON p.tamano = t.id "
                . "WHERE r.reward_customer_id = $customerId "
                . "GROUP BY r.sales_id,r.operation ORDER BY r.id DESC ";
        //echo $query;
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            return $this->resultToArray($result);
        }
        
        return null;
    }
    
   public function getListStatus(){
        $query = "SELECT * FROM status_code WHERE operation = 'RewardsCustomer'";
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            $array = array();
            foreach($result as $status){
                $array[$status['code']] = $status['description'];
            }
            return $array;
        }
        return null;
    }

    public function createFilterFecha($options,$campoFecha = null ){
        $fechaInicio = $options['fechaInicio'];
        $fechaFin = $options['fechaFin'];
        $fecha = null;
        $tools = new Tools();
        if($fechaInicio!=null){
            $fechaInicio = $tools->setFormatDateToDB($fechaInicio);
            if($fechaFin!=null){
                $fechaFin = $tools->setFormatDateToDB($fechaFin);
                $fecha .=" AND $campoFecha BETWEEN '{$fechaInicio}' AND '{$fechaFin}' ";
            }else{
                $fecha .=" AND $campoFecha BETWEEN '{$fechaInicio}' AND '{$fechaInicio}' ";
            }
        }elseif($fechaFin!=null){
            $fecha .=" AND $campoFecha BETWEEN '{$fechaFin}' AND '{$fechaFin}' ";
        }
        
        return $fecha;
    }
}