<?php
class ReportListRepository extends EntityRepository {  
    
    public function getAccountPayable($options = null){       
        $vendor = null;
        $date = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'],'DATE(purchase_date)');
        
        if(isset($options['vendor_id'])){
            if(is_array($options['vendor_id']) && count($options['vendor_id']) > 0){
                $vendorIds = implode(',', $options['vendor_id']);
                $vendor = " AND find_in_set(vendor_id,'{$vendorIds}')";
            }else{
                 $vendor = " AND find_in_set(vendor_id,'{$options['vendor_id']}')";
            }           
        } 
        
        $query = "SELECT "
                . "SUM(total - payments)as balance,"
                . "fxGetVendorName(vendor_id)as vendor_name,"
                . "count(id)as compras_pendientes "
                . "FROM purchases "
                . "WHERE status = '1' "
                . "$vendor "
                . "$date "
                . "GROUP BY vendor_id";
        
        $result = $this->query($query);      
        
        if($result->num_rows > 0){
            return array(
                'data'=>$this->resultToArray($result)
            );
        }else{
            null;
        }
    }
    
    public function getAccountPayableDetails($options = null){
        $vendor = null;
        $date = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'],'DATE(purchase_date)');
        
        if(isset($options['vendor_id'])){
            if(is_array($options['vendor_id']) && count($options['vendor_id']) > 0){
                $vendorIds = implode(',', $options['vendor_id']);
                $vendor = " AND find_in_set(vendor_id,'{$vendorIds}')";
            }else{
                 $vendor = " AND find_in_set(vendor_id,'{$options['vendor_id']}')";
            }           
        } 
        
        $query = "SELECT "
                . "DATE_FORMAT(purchase_date,'%d/%m/%Y')as purchase_date, "
                . "purchase_number, "
                . "total,"
                . "(total - payments)as balance,"
                . "fxGetVendorName(vendor_id)as vendor_name "
                . "FROM purchases "
                . "WHERE status = '1' "
                . "$date ";
        
        $result = $this->query($query);      
        
        if($result->num_rows > 0){
            return array(
                'data'=>$this->resultToArray($result)
            );
        }else{
            null;
        }
    }
  
    public function createFilterFecha($fechaInicio,$fechaFin,$campoFecha = null ){
        $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;
    }
}