<?php
class ReportListasRepository extends EntityRepository {    
    public function getInventario(){      
        $repo = new SucursalRepository();
        $sucursales = $repo->getListSucursales();
        
        $case = '';
        foreach($sucursales as $sucursal){
            $case .= " sum(CASE WHEN id_store = '{$sucursal['id']}' THEN i.stock ELSE 0 END) as '{$sucursal['id']}',"; 
            $case .= " (CASE WHEN id_store = '{$sucursal['id']}' THEN i.notes ELSE '' END) as 'notes_{$sucursal['id']}',"; 
            $arraySucursales[$sucursal['id']] = $sucursal['nombre'];
        }
        
        $case = trim($case,',');
        $query = "SELECT "
                . "p.id as id_product,"
                . "p.codigo,"
                . "p.descripcion,"
                . "fxGetCategoriaDescripcion(p.categoria)as categoria,"
                . "fxGetMarcaDescripcion(p.marca)as marca,"
                . "i.notes,"
                . "$case "
                . "FROM productos p LEFT JOIN inventario i ON p.id = i.id_product "
                . "WHERE type = 'producto' "
                . "GROUP BY p.id";
        $result = $this->query($query);
        
        if($result->num_rows > 0){
           // echo "<pre>";var_dump($this->resultToArray($result));echo "</pre>";exit;
            return array(
                'data'=>$this->resultToArray($result),
                'sucursales'=>$arraySucursales
            );
        }else{
            null;
        }
    }
    
    public function getVentasPorFecha($options){       
        $fecha = null;
        $formaPago = null;
        $store_id = null;
        $status = null;
        
        $fecha = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'], 'v.fecha');
        
        if(isset($options['forma_pago']) && is_array($options['forma_pago']) && count($options['forma_pago']) > 0){
            $formaPagoIds = implode(',', $options['forma_pago']);
            $formaPago = " AND find_in_set(c.formaPago,'$formaPagoIds')";
        }   
        
        if(isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0){
            $store_ids = implode(',', $options['store_id']);
            $store_id = " AND find_in_set(idSucursal,'$store_ids')";
        }elseif(isset($options['store_id']) && trim($options['store_id'])!=='' && !is_null($options['store_id'])){
            $store_id = " AND find_in_set(idSucursal,'{$options['store_id']}')";
        }  
  
        if(isset($options['status']) && trim($options['status'])!=='null'){ $status = " AND v.status = '{$options['status']}'";}
        
        $query = "SELECT v.*,"
                . "v.id as idVenta,"
                . "DATE_FORMAT(v.`fecha`,'%m/%d/%Y')as fecha,"
                . "DATE_FORMAT(v.creado_fecha,'%r')as hora,"
                . "fxGetStoreName(idSucursal)as sucursalName,"
                . "ROUND(v.subTotal,2)as subTotal,"
                . "ROUND(SUM(v.descuento_items_monto + v.descuento_orden_monto),2)as descuento,"
                . "ROUND(v.total_impuestos,2)as impuestos,"
                . "fxGetStatusName(v.status,'status','venta')as statusName, "
                . "GROUP_CONCAT(DISTINCT(fxGetFormaPagoName(c.formaPago)))as formaPagoConcat, "
                . "c.formaPago,"
                . "c.monto as monto_con_fp,"
                . "GROUP_CONCAT(DISTINCT(last_4))as last4Concat, "
                . "fxGetUserName(v.creado_por)as usuarioName "
                . "FROM cobros c left join ventas v on v.id= c.idVenta "
                . "WHERE 1=1 $fecha $formaPago $status $store_id"
                . "GROUP BY c.id ORDER BY creado_fecha DESC";
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            $result = $this->resultToArray($result);
            $ventasPagadas = 0;
            $ventasPagadasMonto = 0;          
            $ventasCanceladas = 0;
            $ventasCanceladasMonto = 0;          
            
            $arrayVentasIds = array();
            $resultFormated = array();
            foreach ($result as $key => $row){
                if(key_exists($row['idVenta'], $arrayVentasIds)){
                    $row['subTotal'] = '';
                    $row['descuento'] = '';
                    $row['impuestos'] = '';
                    $row['total_venta'] = '';
                    $row['statusName'] = '';
                    $row['last4Concat'] = '';
                    $row['usuarioName'] = '';
                }
                $arrayVentasIds[$row['idVenta']] = $row['idVenta'];
                $resultFormated[] = $row;
                
                if($row['status']=='3'){
                    $ventasCanceladas += 1;
                    $ventasCanceladasMonto += round($row['total_venta'],2);
                }elseif($row['status']=='2'){
                    $ventasPagadas += 1;
                    $ventasPagadasMonto += round($row['monto_con_fp'],2);
                }
            }
            
            //echo $ventasPagadasMonto;exit;
            return array(
                'data'=>$resultFormated,
                'ventasPagadas'=>$ventasPagadas,
                'ventasPagadasMonto'=>$ventasPagadasMonto, 
                'ventasCanceladas'=>$ventasCanceladas,
                'ventasCanceladasMonto'=>$ventasCanceladasMonto
            );
            
        }
        return null;        
    }
    
    public function getDevolucionesPorFecha($options){
        $fecha = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'], 'fechaDevolucion');
        $idCaja = null;
        if(key_exists('idCaja', $options)){$idCaja = " AND idCaja = '{$options['idCaja']}'";}
         
         $query = "SELECT idDevolucion,idVenta,SUM(cantidad * devolucion)as total,
                    fxGetStatusName(status,'status','devolucion')as statusName 
                    FROM view_devoluciones
                    WHERE 1=1 $fecha $idCaja
                    GROUP BY idDevolucion";
         
         $result = $this->query($query);
         
         if($result->num_rows > 0){
             $result = $this->resultToArray($result);
             
             return  array('data'=>$result);
         }
         return null;
    }
    
    public function getVentasDetalles($options){
        $status = null;
        $store_id = null;
        $formaPago = null;
        $fecha = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'], 'v.fecha');
        
        if(isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0){
            $store_ids = implode(',', $options['store_id']);
            $store_id = " AND find_in_set(idSucursal,'$store_ids')";
        }elseif(isset($options['store_id']) && trim($options['store_id'])!=='' && !is_null($options['store_id'])){
            $store_id = " AND find_in_set(idSucursal,'{$options['store_id']}')";
        }  
        
        if(isset($options['forma_pago']) && is_array($options['forma_pago']) && count($options['forma_pago']) > 0){
            $formaPagoIds = implode(',', $options['forma_pago']);
            $formaPago = " AND find_in_set(c.formaPago,'$formaPagoIds')";
        }   
        
        if(trim($options['status'])!=='null'){$status = " AND v.status = '{$options['status']}'";}
        
        $query = "SELECT vd.*,"
                . "v.status,"
                . "fxGetStatusName(v.status,'status','venta')as statusName, "
                . "DATE_FORMAT(v.`fecha`,'%m/%d/%Y')as fecha,"
                . "DATE_FORMAT(v.creado_fecha,'%r')as hora,"
                . "v.num_venta,"
                . "fxGetStoreName(v.idSucursal)as sucursalName, "
                . "fxGetUserName(v.creado_por)as usuarioName,"
                . "vd.precio_sin_impuestos * vd.cantidad as subTotal,"
                . "vd.descuento_item_monto + vd.descuento_orden_monto as descuento,"
                . "vd.descripcion as desripcion,"
                . "fxGetTamanoDescripcion(p.tamano)as tamano "
                . "FROM ventas v "
                . "LEFT JOIN ventasdetalles vd ON v.id = vd.idVenta "
                . "LEFT JOIN productos p ON vd.idProducto = p.id "
                . "LEFT JOIN cobros c ON v.id = c.idVenta "
                . "WHERE  1= 1 "
                . "$fecha "
                . "$store_id "
                . "$formaPago "
                . "$status "
                . "ORDER BY v.creado_fecha DESC";
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            $result = $this->resultToArray($result);
            $ventasPagadas = array();
            $ventasPagadasMonto = 0;
            $ventasCanceladas = array();
            $ventasCanceladasMonto = 0;
            foreach ($result as $key => $row){
                $total = $row['subTotal'] - $row['descuento'] + $row['impuestos_monto'];              
                $result[$key]['total'] = $total;
                
                if($row['status'] === '2'){
                    $ventasPagadasMonto += $total;
                    $ventasPagadas[$row['idVenta']] = 1;
                }elseif($row['status'] === '3'){
                    $ventasCanceladasMonto += $total;
                    $ventasCanceladas[$row['idVenta']] = 1;
                }
                
            }
            
            return array(
                'data'=>$result,
                'ventasPagadas'=>count($ventasPagadas),
                'ventasPagadasMonto'=>$ventasPagadasMonto,
                'ventasCanceladas'=>count($ventasCanceladas),
                'ventasCanceladasMonto'=>$ventasCanceladasMonto,2
            );
        }
        return null;        
    }
    
    public function getVentasDiarias($options){
        $fecha = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'], 'date(v.creado_fecha)');
        #Obtengo total de ventas agrupado por formaDePago        
        $ventasPorFormaDePago = null;
        $query = "SELECT "
               . "fxGetFormaPagoName(formaPago)as formaPago,"
               . "SUM(c.monto)as total,"
               . "group_concat(DISTINCT(c.idVenta))as idVentas "
               . "FROM cobros c,ventas v "
               . "WHERE c.idVenta = v.id "
               . " $fecha "
               . "AND v.status != 3 "
               . "GROUP BY formaPago ORDER BY formaPago ASC";   
        
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            $ventasPorFormaDePago = $this->resultToArray($result);
        }
        
        $fecha = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'], 'date(v.creado_fecha)');
        #Obtener diferentes impuestos en las ventas dentro del rango
        $impuestosList = null;
        $query = "SELECT DISTINCT(impuesto) impuesto,fxGetImpuestoName(impuesto)as impuestoName "
                        . "FROM view_ventas_info_detalles v "
                        . "WHERE 1 = 1 $fecha AND status != 3 "
                        . "GROUP BY impuesto";
         
        $result = $this->query($query);
        if($result->num_rows > 0){
            $impuestosList = $this->resultToArray($result);
            $impuestosListTemp = array();
            foreach ($impuestosList as $impuesto){
                $impuestosListTemp[$impuesto['impuesto']] = $impuesto['impuestoName'];
            }
            $impuestosList = $impuestosListTemp;
        }
      
        if($impuestosList){
            #Asigno porcentaje de impuestos a cada formaDePago      
            if($ventasPorFormaDePago){
                foreach($ventasPorFormaDePago as $key => $formaPago){
                    foreach($impuestosList as $impuesto => $impuestoNombre){
                        $montoImpuesto = 0;
                        $subTotal = 0;
                        $query = "SELECT "
                            . "impuesto,"
                            . "fxGetImpuestoName(impuesto)as impuestoName, "
                            . "SUM(impuestos)as montoImpuesto, "
                            . "SUM(subTotal - (IFNULL(descuento,0) + IFNULL(devoluciones,0)))as subTotal "
                            . "FROM view_ventas_info_detalles v "
                            . "WHERE 1 = 1 $fecha "
                            . "AND status != 3 "
                            . "AND find_in_set(idVenta,'{$formaPago['idVentas']}') "
                            . "AND impuesto = '$impuesto' ";
                            
                        $result = $this->query($query);
                        if($result->num_rows > 0){
                            $result = $result->fetch_object();
                            $montoImpuesto = $result->montoImpuesto;
                            $subTotal = $result->subTotal;
                        }
                        
                        if(isset($ventasPorFormaDePago[$key]['impuestos'][$impuesto])){
                            $ventasPorFormaDePago[$key]['impuestos'][$impuesto]['montoImpuesto'] += $montoImpuesto;
                            $ventasPorFormaDePago[$key]['impuestos'][$impuesto]['subTotal'] += $subTotal;
                        }else{                            
                            $ventasPorFormaDePago[$key]['impuestos'][$impuesto]['nombre'] =  $impuestoNombre;
                            $ventasPorFormaDePago[$key]['impuestos'][$impuesto]['montoImpuesto'] = $montoImpuesto;
                            $ventasPorFormaDePago[$key]['impuestos'][$impuesto]['subTotal'] = $subTotal;
                        }
                    }
                }
            }
        }
        
        return array(
            'ventasPorFormaDePago'=>$ventasPorFormaDePago,
            'listaImpuestos'=>$impuestosList,
            'data'=>$this->getVentasPorFecha($options)
        );      
    }
    
    public function getVentasPorProducto($options){  
        $store_id = null;
        $selectFecha ="DATE_FORMAT(v.`fecha`,'%m/%d/%Y')as fecha,";
        $selectSucursalName = "fxGetStoreName(v.idSucursal) as sucursalName";
        $groupBySucursal = ',v.idSucursal';
        $groupByFecha = ',v.fecha';
        
        $fecha = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'], 'date(fecha)'); 

        if(isset($options['store_id']) && $options['store_id'] != ''){
            if(is_array($options['store_id'])){
                 $store_ids = implode(',', $options['store_id']);
            }else{
                 $store_ids = $options['store_id'];
            }           
            
            if($store_ids == 'all'){
                $store_id = "";
                $selectSucursalName = "'Todas' as sucursalName";
                $groupBySucursal = '';
                $groupByFecha = '';
                $selectFecha = "'{$options['fechaInicio']} - {$options['fechaFin']}' as fecha,";
            }else{
                $store_id = " AND find_in_set(idSucursal,'$store_ids') ";
            }             
        }else{          
            $store_id = "";
            $selectSucursalName = "'Todas' as sucursalName";
            $groupBySucursal = '';
            $groupByFecha = '';
            $selectFecha = "'{$options['fechaInicio']} - {$options['fechaFin']}' as fecha,";
        }
        
        
      
        $query = "SELECT "
                . "d.idProducto,"
                . "$selectFecha "
                . "d.descripcion as producto,"
                . "fxGetTamanoDescripcion(p.tamano)as 'tamano',"
                . "SUM(d.cantidad)as cantidad,"
                . "$selectSucursalName "
                . "FROM ventas v "
                . "LEFT JOIN ventasdetalles d ON v.id = d.idVenta "
                . "LEFT JOIN  productos p ON d.idProducto = p.id "
                . "WHERE 1 = 1 $fecha $store_id "
                . "AND v.status != '3' "
                . "GROUP BY d.idProducto$groupByFecha$groupBySucursal "
                . "ORDER BY FIELD (p.tamano,'10','1','2','3','4','5','6','7','8','9'),p.descripcion ASC, fecha ASC";    
         
        $result = $this->query($query);
         
        if($result->num_rows > 0){
            return array(
                'data'=>$this->resultToArray($result)
            );
        }
       
        return null;
    }
    
    public function getVentasPorProductoEnOrdenesEspeciales($options){
        $store_id = null;       
        $fecha = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'], 'date(v.delivery_date)'); 
        
        if(isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0){
            $store_ids = implode(',', $options['store_id']);
            $store_id = " AND find_in_set(v.store,'$store_ids')";
        }elseif(isset($options['store_id']) && trim($options['store_id'])!=='' && !is_null($options['store_id'])){
            $store_id = " AND find_in_set(v.store,'{$options['store_id']}')";
        }  
      
         $query = "SELECT "
                . "d.id_product as idProducto,"
                . "DATE(v.delivery_date) as fecha,"
                . "p.descripcion as producto,"
                . "fxGetTamanoDescripcion(p.tamano)as 'tamano',"
                . "SUM(d.quantity)as cantidad,"
                . "fxGetStoreName(v.store) as sucursalName "
                . "FROM special_requisitions v "
                . "LEFT JOIN special_requisitions_details d ON v.id = d.id_requisition "
                . "LEFT JOIN  productos p ON d.id_product = p.id "
                . "WHERE 1 = 1 $fecha $store_id "
                . "AND d.`type` = 'Line' " 
                . "AND v.status != '3' "
                . "GROUP BY d.id_product,DATE(v.delivery_date) "
                . "ORDER BY v.delivery_date ASC ";  
         
        $result = $this->query($query);
         
        if($result->num_rows > 0){
            return array(
                'data'=>$this->resultToArray($result)
            );
        }
       
        return null;
    }
    
    public function getSaldosPendientes($options = null){
        $date = null;
        $store_id = null;
        $limit = null;

        if($options != null){
            $date = $this->createFilterFecha($options['fechaInicio'],$options['fechaFin'], 'DATE(r.delivery_date)');      
            if(isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0){
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(r.store,'$store_ids')";
            }elseif(isset($options['store_id']) && trim($options['store_id'])!=='' && !is_null($options['store_id'])){
                $store_id = " AND find_in_set(r.store,'{$options['store_id']}')";
            }  
        }else{
          $limit = " LIMIT 150";
        }        

        $query = "SELECT r.req_number as 'Numero de orden',
               DATE_FORMAT(r.date,'%m/%d/%Y')as Fecha,                
               DATE_FORMAT(r.delivery_date,'%m/%d/%Y %h:%i %p')as 'Fecha de entrega',
               fxGetClienteName(r.customer) as Cliente,
               r.ammount as 'Importe factura',
               r.ammount_payments as 'Pagos',
               ROUND((r.ammount - r.ammount_payments), 0) as 'Saldo Pendiente',
                fxGetStatusName(r.`status`,'status','Special-requisition')as Status,
               group_concat(distinct v.id) as ticket
               FROM special_requisitions r
               left join ventas as v on r.id = v.id_special_requisition
               WHERE 1 = 1 
               $date "
             . "$store_id " 
             . "GROUP BY r.id "
             . "ORDER BY r.id asc $limit ";

       $result = $this->query($query);

       if($result->num_rows > 0){
           return $this->resultToArray($result);
       }        
       return null;
}

    public function getInventoryStores(){      
        $repo = new SucursalRepository();
        $sucursales = $repo->getListSucursales();
        
        $case = '';
        foreach($sucursales as $sucursal){            
            if($sucursal['id'] !== '5'){ // 4 es Almacen;
                $case .= " sum(CASE WHEN id_store = '{$sucursal['id']}' THEN i.stock ELSE 0 END) as '{$sucursal['id']}',"; 
                $arraySucursales[$sucursal['id']] = $sucursal['nombre'];
            }        
        }
        
        $case = trim($case,',');
        $query = "SELECT "
                . "i.id_product,"
                . "p.codigo as code,"
                . "p.descripcion as description,"
                . "fxGetTamanoDescripcion(p.tamano)as size,"
                . "$case FROM inventario i LEFT JOIN productos p  ON p.id = i.id_product "
                . "WHERE 1 = '1' "
                . "AND presentacion = 'Si' "
                . "GROUP BY p.id";
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            return array(
                'data'=>$this->resultToArray($result),
                'sucursales'=>$arraySucursales
            );
        }else{
            null;
        }
    }

    public function getBakedPlan($options){
        $detalles = array();
        $listRequisitions = null;
        $specialDetalles = array();
        $listSpecialRequisitions = null;
        $globalDetalles = null;
        
         if($options['requisitions']){
            $requisition = new RequisitionEntity();
            $idRequisitions = implode(',', $options['requisitions']);
            $detalles = $requisition->getBakedPlan($idRequisitions);
            $listRequisitions = $requisition->getListRequisitionsByIds($idRequisitions);
            
            if($detalles){ 
                $detallesTemp = null;
                foreach($detalles as $detalle){ 
                    $detallesTemp[$detalle['id_slice']] = $detalle;
                    $globalDetalles[$detalle['id_slice']] = $detalle;
                }
                $detalles = $detallesTemp;
            }else{
                $detalles = array();
            }            
        }
        
        if($options['special_requisitions']){
            $specialRequisition = new SpecialRequisitionEntity();
            $idRequisitions = implode(',', $options['special_requisitions']);
            $specialDetalles = $specialRequisition->getBakedPlan($idRequisitions);
            $listSpecialRequisitions = $specialRequisition->getListRequisitionsByIds($idRequisitions);
            
            if($specialDetalles){
                $specialDetallesTemp = null;
                foreach($specialDetalles as $specialDetalle){ 
                    $specialDetallesTemp[$specialDetalle['id_slice']] = $specialDetalle;
                     $globalDetalles[$specialDetalle['id_slice']] = $specialDetalle;
                }
                $specialDetalles = $specialDetallesTemp;
            }else{
                $specialDetalles = array();
            }
        }
        
        return array(
            'globalDetalles'=>$globalDetalles,
            'requisitionsDetalles'=>$detalles,
            'specialRequisitionsDetalles'=>$specialDetalles,
            'listSpecialRequisitions'=>$listSpecialRequisitions
        );
    }
    
    public function getTimeClock($options){     
        $user  = null;
        $date = null;
        $store_id = null;
        $limit = null;        

        if($options != null){         
            $date = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'], 't.date');
            if(isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0){
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(t.sucursal,'$store_ids')";
            }elseif(isset($options['store_id']) && trim($options['store_id'])!=='' && !is_null($options['store_id'])){
                $store_id = " AND find_in_set(t.sucursal,'{$options['store_id']}')";
            }  
            
            if(isset($options['id_user']) && is_array($options['id_user']) && count($options['id_user']) > 0){
                $userIds = implode(',', $options['id_user']);
                $user = " AND find_in_set(t.id_user,'$userIds')";
            }
            
        }else{
          $limit = " LIMIT 150 ";          
        }        
 
        $select = "SELECT 
                    t.id,
                    t.id_user,
                    date_format(t.date,'%c/%d/%Y')as date,
                    fxGetStoreName(t.sucursal)as sucursalName,
                    date_format(t.check_in,'%r')as check_in,
                    date_format(t.check_out,'%r')as check_out,
                    fxGetUserName(t.id_user)as userName,
                    timediff(t.check_out,t.check_in)as total_work,
                    ROUND(((time_to_sec(timediff(t.check_out,t.check_in)))/60)/60 ,2) as total
                    from timeclock t, usuarios u 
                    WHERE t.id_user = u.id
                    $date $user $store_id
                    ORDER BY u.apellido ASC, u.nombre ASC";
        
        $result = $this->query($select);

        if ($result) {
           $result = $this->resultToArray($result);
           
           $data = null;
           foreach($result as $row){
               $data[$row['id_user']][] = $row;
           }
           
           return array(
               'data'=>$data
           );
        }
        return null;
    }
    
    public function getSalesActivity($options){
        $fecha = null;
        $store_id = null;
        
        $total_sales = 0;
        $quantity_sales = 0;
        $total_discounts = 0;
        $total_sold_products = 0;
        $productosVendidos = array();
        
        $fecha = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'], 'v.fecha');
        if(isset($options['store_id'])){$store_id = " AND idSucursal = '{$options['store_id']}'";}
        
        $query = "SELECT "
                . "SUM(total_venta)as total_sales,"
                . "COUNT(id)as quantity_sales,"
                . "SUM(descuento_items_monto+descuento_orden_monto)as total_discounts "
                . "FROM ventas v "
                . "WHERE status != '3' "
                . "$fecha $store_id";
        
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            $result  = $result->fetch_object();
            $total_sales = $result->total_sales;
            $quantity_sales = $result->quantity_sales;
            $total_discounts = $result->total_discounts;    
            
            $productosVenta = $this->getVentasPorProducto($options);
            if($productosVenta['data']){
                foreach ($productosVenta['data'] as $producto){
                    $total_sold_products += $producto['cantidad'];
                    if(key_exists($producto['idProducto'], $productosVendidos)){
                        $productosVendidos[$producto['idProducto']]['cantidad'] += $producto['cantidad'];
                    }else{
                        $productosVendidos[$producto['idProducto']] = $producto;
                    }
                }
            }
            
            $productosRequisiciones = $this->getVentasPorProductoEnOrdenesEspeciales($options);
            if($productosRequisiciones){
                foreach ($productosRequisiciones as $producto){                    
                    $total_sold_products += $producto['Cantidad'];
                    if(key_exists($producto['idProducto'], $productosVendidos)){
                         $productosVendidos[$producto['idProducto']]['cantidad'] += $producto['Cantidad'];
                    }else{
                        $producto['cantidad'] = $producto['Cantidad'];
                        $producto['tamano'] = $producto['Tamano'];
                        $producto['producto'] = $producto['Producto'];
                        $productosVendidos[$producto['idProducto']] = $producto;
                    }
                }
            }
            
            $formasPago = array();
            $query = "SELECT "
                . "SUM(c.monto)as monto,"
                . "fxGetFormaPagoName(c.formaPago)as formaPago "
                . "FROM ventas v, cobros c "
                . "WHERE c.idVenta = v.id "
                . "AND c.status != '3' "
                . "$fecha $store_id "
                . "GROUP BY c.formaPago";
            
            $resultFormaPago = $this->query($query);
            if($resultFormaPago->num_rows > 0){
                $formasPago = $this->resultToArray($resultFormaPago);
            }
        }
        
        return array(
            'total_sales'=>$total_sales,
            'quantity_sales'=>$quantity_sales,
            'total_discounts'=>$total_discounts,
            'total_sold_products'=>$total_sold_products,
            'summary_sold_products'=>$productosVendidos,
            'payment_method'=>$formasPago
                
        );
    }
    
    public function getTransactions($options){
        return $this->getVentasPorFecha($options);
    }
    
    public function getMobileTimeClock($options){
        $date = null;
        $store_id = null;
        $limit = null;        
   
        $date = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'], 't.date');
        if(isset($options['store_id'])){$store_id = " AND t.sucursal = '{$options['store_id']}'";}          
 
        $select = "SELECT 
                    t.id,
                    t.id_user,
                    date_format(t.date,'%c/%d/%Y')as date,
                    fxGetStoreName(t.sucursal)as sucursalName,
                    date_format(t.check_in,'%h:%i%p')as check_in,
                    date_format(t.check_out,'%h:%i%p')as check_out,
                    fxGetUserName(t.id_user)as userName,
                    timediff(t.check_out,t.check_in)as total_work,
                    ((time_to_sec(timediff(t.check_out,t.check_in)))/60)/60 as total
                    from timeclock t, usuarios u 
                    WHERE t.id_user = u.id 
                    $date $store_id
                    ORDER BY u.apellido ASC, u.nombre ASC";
        
        $result = $this->query($select);

        if ($result) {
           $result = $this->resultToArray($result);
           
           return array(
               'data'=>$result
           );
        }
        return null;
    }
    
    public function getHistorialInventario($options){
        $store_id = null;
        $fecha = $this->createFilterFecha($options['fechaInicio'], $options['fechaFin'], 'DATE(date)');
        
        if(isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0){
            $store_ids = implode(',', $options['store_id']);
            $store_id = " AND find_in_set(id_store,'$store_ids')";
        }elseif(isset($options['store_id']) && trim($options['store_id'])!=='' && !is_null($options['store_id'])){
            $store_id = " AND find_in_set(id_store,'{$options['store_id']}')";
        }         
        
        $query = "SELECT 
                p.descripcion as Producto,
                fxGetTamanoDescripcion(p.tamano)AS 'Tamano',
                h.stock,
                h.date as Fecha,
                h.controller as Operacion
                FROM inventario_historial h, productos p
                WHERE h.id_product = p.id $store_id $fecha";
        
        $result = $this->query($query);
        
        if($result->num_rows > 0){
            return $this->resultToArray($result);
        }        
         
        return null;        
    }
    
     public function getSpecialProductionPlan($options){        
        $idsRequisitions = implode(',',$options['special_requisitions']);
        $repoSR = new SpecialRequisitionRepository();
        $requisitionsList = $repoSR->getListRequisitionsByIds($idsRequisitions);
        $requisitionsDetailsList = $repoSR->getSpecialProductionPlan($idsRequisitions);
        
        if($requisitionsList == null){
            return null;
        }
       
        return array(
            'requisitionsList'=>$requisitionsList,
            'requisitionsDetailsList'=>$requisitionsDetailsList
        );
    }
    
    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;
    }  
}