<?php
class PurchaseRepository extends EntityRepository {

    private $table = 'purchases';
    public $flashmessenger = null;    
    private $options = array(
        'purchase_date'=>null,       
        'purchase_number'=>null,
        'vendor_id'=>null,
        'total'=>null,
        'comments'=>null,
        'status'=>null,
    );
    
    /*Input double y que no son hide*/
    public $inputs_double = array(
        'total',
    );
    
    public function __construct() {
        if(!$this->flashmessenger instanceof FlashMessenger){
            $this->flashmessenger = new FlashMessenger();
        }
    }
    
    public function _getTranslation($text){
        $translator = new Translator();
        return $translator->_getTranslation($text);
    }
    
    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 getTable(){
        return $this->table;
    }

    public function save(array $data, $table = null) {      
        $data = parent::_rawNumber($data, $this->inputs_double);
        $tools = new Tools();
        $data['purchase_date'] = $tools->setFormatDateToDB($data['purchase_date']);
        $data['payments'] = 0;  
        $data['status'] = '1';   

        $this->startTransaction();
        $rs = parent::save($data, $this->table);
        
        if(!$rs){
            $this->rollback();
            return null;
        }
        $this->commit();
        return true;        
    }
    
    public function delete($id, $table = null) {
        $query = "UPDATE $this->table SET status = '3' where id = '$id'";
        $result = $this->query($query);
        
        if($result){
            return true;
        }
        
        return null;
    }
    
    public function update($id, $data, $table = null) {  
        $data = parent::_rawNumber($data, $this->inputs_double);
        $tools = new Tools();
        $data['purchase_date'] = $tools->setFormatDateToDB($data['purchase_date']); 
        
        unset($data['status']);
        return parent::update($id, $data, $this->table);        
    }

    public function getById($id, $table = null,$selectAux = null) {
        $query = "SELECT *,"
                . "(total - payments) as saldo_pendiente,"
                . "fxGetVendorName(vendor_id) as vendor_name,"
                . "fxGetStatusName(status,'Compra')as status_name,"
                . "DATE_FORMAT(purchase_date,'%d/%m/%Y')as purchase_date_formated "
                . "FROM $this->table WHERE id = '$id'";
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            return $this->resultToArray($result)[0];
        }
        return null;
    }

    public function isUsedInRecord($id, array $buscarEn = null,$andWhere = null) {
        $query = "SELECT id_factura FROM pagos_detaille WHERE id_factura = '$id'";
        $result = $this->query($query);
        if($result->num_rows > 0){
            return true;
        }
        
        return null;
    }
    
     public function getListPurchases($options = null){     
        $limit = null;
        $date = null;
        $vendor_id = null;
        $purchsae_number = null;
        $status = null;
        
        $date = $this->createFilterFecha($options, 'purchase_date');
        if($options){
            if(isset($options['vendor_id'])){
                if(is_array($options['vendor_id']) && count($options['vendor_id']) > 0){
                    $vendorIds = implode(',', $options['vendor_id']);
                    $vendor_id = " AND find_in_set(vendor_id,'{$vendorIds}')";
                }else{
                     $vendor_id = " AND find_in_set(vendor_id,'{$options['vendor_id']}')";
                }           
            } 
            if(isset($options['purchase_number']) && trim($options['purchase_number']) != ''){$purchsae_number = " AND  find_in_set(purchase_number,'{$options['purchase_number']}')";}
            if(isset($options['status']) && trim($options['status']) != ''){$status = " AND status = '{$options['status']}'";}
            
        }else{
            $limit = " LIMIT 300";
        }       
        
        $query = "SELECT *,"
                . "DATE_FORMAT(purchase_date,'%d/%m/%Y')as purchase_date_formated,"
                . "(total - payments) as balance,"
                . "fxGetVendorName(vendor_id)as vendor_name,"
                . "fxGetStatusName(status,'Purchase')as status_name "
                . "FROM $this->table "
                . "WHERE 1 = 1 "
                . "$purchsae_number "
                . "$date "
                . "$vendor_id "
                . "$status "
                . "ORDER BY purchase_date DESC"
                . "$limit ";
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            return $this->resultToArray($result);
        }
        
        return null;
    }
    
    public function getListStatus($options = null){        
        $query = "SELECT * FROM status_code WHERE operation = 'Purchase'";
        $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 getListFacturaPendientesByProveedor($options){                
        $store_id = null;        
        $login = new Login();
        
        if($login->getRole() != '1'){
            $store_id = " AND find_in_set(f.store_id,'{$login->getStoreId()}')";
        }       
        
        $idProveedor =  $options['proveedor'];
        $query = "SELECT f.*,
                  fxGetVendorName(f.vendor_id)as proveedorName,
                  DATE_FORMAT(convert(substring(f.purchase_date,1,10),date),'%d/%m/%Y')as fecha,
                  (total - IFNULL(payments,0))as saldo_pendiente
                  FROM purchases f
                  WHERE  1=1
                  AND status != '3'
                  AND (total - IFNULL(payments,0)) > '0'
                  AND vendor_id = '$idProveedor' "
                . "GROUP BY f.id "
                . "ORDER BY f.id ASC";

        $result = $this->query($query);
        
        if($result->num_rows > 0){
            return $this->resultToArray($result);
        }
        
        return null;
  }
  
   public function getPurchasesByDateRange($start,$end,$group = null,$orderBy = null){       
        if(is_null($group)){$group = " GROUP BY vendor_id";}else{$group = " GROUP BY $group";}
        if(is_null($orderBy)){$orderBy = " ORDER BY total_purchases DESC";}else{$orderBy = " ORDER BY $orderBy";}
        
        $date = $this->createFilterFecha(array('fechaInicio'=>$start,'fechaFin'=>$end),'purchase_date');
        $query = "SELECT "
                . "DATE_FORMAT(purchase_date,'%m/%d/%Y %h:%i %p')as formated_date,"   
                . "fxGetVendorName(vendor_id)as vendor_name, "
                . "SUM(total)as total_purchases "
                . "FROM $this->table "
                . "WHERE status = '1' $date $group $orderBy LIMIT 10 ";
        
        $result = $this->query($query);
        if($result->num_rows > 0){
            return $this->resultToArray($result);
        }
        return null;
    }
    
    public function existPurchase($purchase_num,$vendor_id,$purchase_id = null){
        if($purchase_id){$purchase_id = " AND id != '$purchase_id' ";}
        
        $query = "SELECT id "
                . "FROM $this->table p "
                . "WHERE purchase_number = '$purchase_num' "
                . "AND vendor_id = '$vendor_id' "
                . "AND status != '3' "
                . "$purchase_id";
        //echo $query;exit;
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            return true;
        }
        
        return null;
    }
    
     public function createFilterFecha($options,$campoFecha = null ){
        if(!isset($options['fechaInicio']) && !isset($options['fechaFin'])){return 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;
    }
}