<?php
class StoreRepository extends EntityRepository {

    private $table = 'stores';
    public $flashmessenger = null;
    
    private $options = array (
        'nombre'=>null,
        'calle'=>null,
        'numero_exterior'=>null,
        'numero_interior'=>null,
        'colonia'=>null,
        'ciudad'=>null,
        'estado'=>null,
        'pais'=>'MEXICO',
        'codigo_postal'=>null,
        'nombre_contacto'=>null,
        'telefono'=>null,
        'email'=>null,
        'serie_de_facturacion'=>null,
        'rango_de_facturacion'=>null,
        'comentarios'=>null,
        'status'=>null
        );
    
    private $options_aux = array(
        'statusName'=>null,
        'userName'=>null,
        'token_form'=>null
    );
    
    public function setOptions($data){
        foreach ($this->options as $option => $value){
            if(isset($data[$option])){
              $this->options[$option] = $data[$option];
            }
        }
        
        foreach ($this->options_aux as $option => $value){
            if(isset($data[$option])){
              $this->options_aux[$option] = $data[$option];
            }
        }
    }

    public function getOptions(){
        return $this->options;
    }
    
    public function getTokenForm(){
        return $this->options_aux['token_form'];
    }
    
   public function getNombre(){
        return $this->options['nombre'];
    }
    
    public function getRazonSocial(){
        return $this->options['racon_social'];
    }
    
    public function getRFC(){
        return $this->options['rfc'];
    }
    
    public function getCalle(){
        return $this->options['calle'];
    }
    
    public function getNumExterior(){
        return $this->options['numero_exterior'];
    }
    
    public function getNumInterior(){
        return $this->options['numero_interior'];
    }
    
    public function getCalleNum(){
        $direccion = $this->getCalle().' '.$this->getNumExterior();
        if(trim($this->getNumInterior())!=''){$direccion .= ' No. INT. '.$this->getNumInterior();}        
        return $direccion;
    }
    
    public function getColonia(){
        return $this->options['colonia'];
    }
    
    public function getCiudad(){
        return $this->options['ciudad'];
    }
    
    public function getEstado(){
        return $this->options['estado'];
    }
    
    public function getCodigoPostal(){
        return $this->options['codigo_postal'];
    }
    
    public function getTelefono(){
        return $this->options['telefono'];
    }
    
    public function __construct() {
        if(!$this->flashmessenger instanceof FlashMessenger){
            $this->flashmessenger = new FlashMessenger();
        }
    }
  
    public function _getTranslation($text){
        return $this->flashmessenger->_getTranslation($text);
    }

    public function save(array $data, $table = null) {           
        $data['status'] = '1';
        
        $this->startTransaction();        
        parent::save($data, $this->table);        
        
        $storeInDetailsTemp = new StoreDetailsTempRepository();
        $idCompra = $this->getInsertId();
        $this->setLastInsertId($idCompra);//Para utilizarlo en el Controller action insert
        
        if($storeInDetailsTemp->saveDetalles($idCompra, $this->getTokenForm())){   
            $this->commit();
            $storeInDetailsTemp->truncate($this->getTokenForm());

            return true;
        }
        
        $this->rollback();   
        $this->flashmessenger->addMessage(array(
            'error'=>$this->_getTranslation('Error. Intenta nuevamente o contacta a tu proveedor de sistemas.')));
        return null;        
    }
    
    public function delete($id, $table = null) {
        //return parent::delete($id, $this->table);
        return parent::update($id, array('status'=>'3'), $this->table);
    }

    public function update($id, $data, $table = null) {                     
        $this->startTransaction();      
        $result = parent::update($id, $data, $this->table);
        
        if($result){
            $repository = new StoreDetailsTempRepository();
            if($repository->updateDetalles($id, $this->getTokenForm())){                                
                $this->commit();
                $repository->truncate($this->getTokenForm());
                return true;
            }
        }
        
        $this->rollback();
        return null;
    }
    
    public function updateString($fields, $where, $table = null) {
        return parent::updateString($fields, $where, $this->table);
    }

    public function getById($id, $table = null,$selectAux = null) {
        $select = "SELECT *,"
                . "fxGetUserName(creado_por)as userName,"
                . "fxGetStatusName(status,'status','Sucursal')as statusName "
                . "FROM sucursales "
                . "WHERE id = '$id'";
        $result = $this->query($select);

        if ($result->num_rows>0) {
            $set = $this->resultToArray($result);
            return $set[0];
        }

        return false;
    }

    public function isUsedInRecord($id, array $buscarEn = null,$andWhere = null) {
        return null;
        return parent::isUsedInRecord($id, array('store-out' => 'requisition'));
    }
    
    public function crearTablaDetallesForUser(){
        $login = new Login();
        $query = "CREATE TABLE IF NOT EXISTS store_details_".$login->getId()." 
                 (  
                    `token_form` char(50) NOT NULL,
                    `id` int(11) NOT NULL AUTO_INCREMENT,
                    `id_detail` int(11) NULL,
                    `id_store` int(11) NULL,
                    `id_product` int(11) NOT NULL,
                    `days` text NOT NULL,
                    `min` double NOT NULL,
                    `max` double NOT NULL,
                    PRIMARY KEY (`id`)
                 )ENGINE=InnoDB DEFAULT CHARSET=utf8;";
        
       $result = $this->query($query);
    }
    
    public function insertDetalle($data){
        $storeInDetailsTemp = new StoreDetailsTempRepository();
        
        return $storeInDetailsTemp->save($data);
    }
    
    public function getStoreDetails($token_form){
        $login = new Login();
        $query = "SELECT v.*,
                v.id as idDetailTemp,
                p.id as product,
                p.code,
                p.description,
                fxGetSizeDescription(p.size)as size
                FROM store_details_".$login->getId()." v LEFT JOIN products p ON v.id_product = p.id
                WHERE token_form = '$token_form' ORDER BY v.id";
        $result = $this->query($query);
        
        if($result){
            $result = $this->resultToArray($result);
            return $result;
        }
        
        return null;
    }
    
    public function getStoreDetailsSaved($id){
        $query = "SELECT c.*,
                    p.code as code,
                    p.description as description,
                    fxGetSizeDescription(p.size)as size
                    FROM store_details c LEFT JOIN products p ON c.id_product = p.id
                    WHERE c.id_store = '$id'";
        $result = $this->query($query);
        
        if($result){
            $result = $this->resultToArray($result);
            return $result;
        }
        
        return null;
    }
    
    public function setStoreDetailsById($idCompra, $tokenForm){
        $repository = new StoreDetailsTempRepository();
        
        return $repository->setStoreDetailsById($idCompra, $tokenForm);
    }
    
    public function truncateIfIsEditInfo(){
        $repository = new StoreDetailsTempRepository();
        $repository->truncateIfIsEditInfo();
        
    }
    
    public function getProductById($idProducto){
        $query = "SELECT * FROM products WHERE id = '$idProducto' LIMIT 1";
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            $result = $this->resultToArray($result);
            return $result[0];
        }        
        return null;
    }
    
    public function getListStores(){          
        $query = "SELECT r.*,
                SUM(d.min)as quantity,
                fxGetStatusName(r.`status`,'status','Store')as statusName,
                fxGetUserName(r.creado_por) as user
                FROM stores r LEFT JOIN store_details d
                ON r.id = d.id_store " 
              . "GROUP BY r.id "
              . "ORDER BY r.id DESC ";
    
        $result = $this->query($query);

        if($result->num_rows > 0){
            return $this->resultToArray($result);
        }
        
        return null;
  }
  
  public function getListSelectStores(){          
        $query = "SELECT r.*,
                SUM(d.min)as quantity,
                fxGetStatusName(r.`status`,'status','Store')as statusName,
                fxGetUserName(r.creado_por) as user
                FROM sucursales r LEFT JOIN store_details d ON r.id = d.id_store " 
              . "GROUP BY r.id "
              . "ORDER BY r.nombre ASC ";
    
        $result = $this->query($query);
        
       if ($result) {
            $array = array();
            while ($row = $result->fetch_assoc()) {
                $array[$row['id']] = $row['nombre'];
            }
            return $array;
        }
        return null;
  }
  
  public function getListDays(){
      return array(
          '1'=>'Lun',
          '2'=>'Mar',
          '3'=>'Mie',
          '4'=>'Jue',
          '5'=>'Vie',
          '6'=>'Sab',
          '7'=>'Dom',
      );
  }
  
  public function daysArrayTostring($days){
      $stringDays = '';
      foreach($days as $day){
          $stringDays .= $day.",";
      }
      
      return trim($stringDays,',');
  }
  
    public function getMinimumStockForStoreByDay($days){ 
        if(is_array($days) && count($days) > 0){
            $array = array();
            foreach($days as $day){
                $query = "SELECT id_store,id_product,min FROM store_details WHERE FIND_IN_SET($day,days)";
                $result = $this->query($query);
                
                if($result->num_rows > 0){
                    $result = $this->resultToArray($result);
                    foreach($result as $row){
                        if(isset( $array[$row['id_store']][$row['id_product']])){
                             $array[$row['id_store']][$row['id_product']] += $row['min'];
                        }else{
                             $array[$row['id_store']][$row['id_product']] = $row['min'];
                        }
                       
                    }
                }
            }
            return $array;
            
        }else{
            $query = "SELECT id_store,id_product,min FROM store_details WHERE FIND_IN_SET($days,days)";
            $result = $this->query($query);

            if($result->num_rows > 0){
                $result = $this->resultToArray($result);
                foreach($result as $row){                   
                    $array[$row['id_store']][$row['id_product']] = $row['min'];
                }
                
                return $array;
            }
          
        }     
      return null;
  }
  
  public function getLastUpdateForStoreInventory(){
        $query = "SELECT * FROM inventory_updated";
        $result = $this->query($query);
        
        if($result){
            return $this->resultToArray($result);
        }
        
        return null;
    }
    
    public function createFilterFecha($options,$campoFecha = null ){
        if(!isset($options['startDate']) && !isset($options['endDate'])){return null;}        
        $startDate = $options['startDate'];
        $endDate = $options['endDate'];
        $fecha = null;
        $tools = new Tools();
        if($startDate!=null){
            $startDate = $tools->setFormatDateToDB($startDate);
            if($endDate!=null){
                $endDate = $tools->setFormatDateToDB($endDate);
                $fecha .=" AND $campoFecha BETWEEN '{$startDate}' AND '{$endDate}' ";
            }else{
                $fecha .=" AND $campoFecha BETWEEN '{$startDate}' AND '{$startDate}' ";
            }
        }elseif($endDate!=null){
            $fecha .=" AND $campoFecha BETWEEN '{$endDate}' AND '{$endDate}' ";
        }
        
        return $fecha;
    }
}