DROP VIEW IF EXISTS `prefactura_consolidado_pedidos`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW 
`prefactura_consolidado_pedidos`  AS  
select `dp`.`id_pedido` AS `id_pedido`,
`dp`.`posicion` AS `posicion`,`dp`.`orden_compra` AS `orden_compra`,
`cc`.`cebe` AS `cebe`,`dp`.`estado_pedido` AS `estado_pedido`,
`dp`.`usuario_aprueba` AS `usuario_aprueba`,`dp`.`usuario_rechaza` AS `usuario_rechaza`,
`dp`.`fecha_aprobacion` AS `fecha_aprobacion`,`dp`.`fecha_rechazo` AS `fecha_rechazo`,
`dp`.`motivo_rechazo_prefactura` AS `motivo_rechazo_prefactura`,
`dp`.`nombre_factura` AS `nombre_factura`,`dp`.`id` AS `id`,cast(`dp`.`created` as date) AS `fecha`,
`dp`.`mes` AS `mes`,`dp`.`ano` AS `ano`,`dp`.`usuario` AS `usuario`,
`cc`.`nombre` AS `dependencia`,`em`.`nombre` AS `empresa`,`dp`.`estado` AS `estado`,
 (
   (
      select sum(`prefactura_dispositivo`.`ftes`)
      from `prefactura_dispositivo`
      where (
        (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
          and
        (`prefactura_dispositivo`.`tipo` = 'fijo')
      )
    ) 
    + 
    (
      (
        select coalesce(sum(`prefactura_dispositivo`.`ftes`),0)
        from `prefactura_dispositivo`
        where (
          (`prefactura_dispositivo`.`tipo_servicio` NOT IN ('No Prestado','Recargo Nocturno No Prestado'))
          and 
          (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
          and
          (`prefactura_dispositivo`.`tipo` = 'variable')
        )
      )
    - (
        select coalesce(sum(`prefactura_dispositivo`.`ftes`),0)
        from `prefactura_dispositivo`
        where (
          (`prefactura_dispositivo`.`tipo_servicio` IN ('No Prestado','Recargo Nocturno No Prestado'))
          and 
          (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`) 
          and 
          (`prefactura_dispositivo`.`tipo` = 'variable')
        )
      )
    )
  ) AS `total_ftes`,
  (
    (
      select sum(`prefactura_dispositivo`.`valor_mes`)
      from
      `prefactura_dispositivo`
      where (
        (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`) 
        and 
        (`prefactura_dispositivo`.`tipo` = 'fijo')
      )
    ) 
    +
    (
      ( 
        select coalesce(sum(`prefactura_dispositivo`.`valor_mes`),0)
        from `prefactura_dispositivo`
        where (
          (`prefactura_dispositivo`.`tipo_servicio` NOT IN ('No Prestado','Recargo Nocturno No Prestado'))
          and
          (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
          and
          (`prefactura_dispositivo`.`tipo` = 'variable')
        )
      )
      -
      (
        select coalesce(sum(`prefactura_dispositivo`.`valor_mes`),0)
        from
        `prefactura_dispositivo`
        where (
          (`prefactura_dispositivo`.`tipo_servicio` IN ('No Prestado','Recargo Nocturno No Prestado'))
          and
          (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
          and
          (`prefactura_dispositivo`.`tipo` = 'variable')
        )
      )
    )
  ) AS `total_mes`,`em`.`nit` AS `nit`,`dp`.`numero_factura` AS `numero_factura`,
  `dp`.`created` AS `created`,`cc`.`ceco` AS `ceco`,
  (
    select count(`prefactura_dispositivo`.`id`)
    from `prefactura_dispositivo`
    where (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
  ) AS `cantidad_servicios`,
  (
    select sec_to_time(sum(time_to_sec(`prefactura_dispositivo`.`horas`))) AS `hours`
    from `prefactura_dispositivo`
    where (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
  ) AS `horas`,
  (
    (
      select sum(`prefactura_dispositivo`.`ftes_diurno`)
      from `prefactura_dispositivo`
      where (
        (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
        and
        (`prefactura_dispositivo`.`tipo` = 'fijo')
      )
    )
    +
    (
      (
        select coalesce(sum(`prefactura_dispositivo`.`ftes_diurno`),0)
        from `prefactura_dispositivo`
        where (
          (`prefactura_dispositivo`.`tipo_servicio` NOT IN ('No Prestado','Recargo Nocturno No Prestado'))
          and
          (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
          and
          (`prefactura_dispositivo`.`tipo` = 'variable')
        )
      )
      -
      (
        select coalesce(sum(`prefactura_dispositivo`.`ftes_diurno`),0)
        from `prefactura_dispositivo`
        where (
          (`prefactura_dispositivo`.`tipo_servicio` IN ('No Prestado','Recargo Nocturno No Prestado')) 
          and 
          (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`) 
          and
          (`prefactura_dispositivo`.`tipo` = 'variable')
        )
      )
    )
  ) AS `ftes_diurnos`,
  (
    (
      select sum(`prefactura_dispositivo`.`ftes_nocturno`)
      from `prefactura_dispositivo`
      where (
        (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
        and
        (`prefactura_dispositivo`.`tipo` = 'fijo')
      )
    )
    +
    (
      (
        select coalesce(sum(`prefactura_dispositivo`.`ftes_nocturno`),0)
        from `prefactura_dispositivo`
        where (
          (`prefactura_dispositivo`.`tipo_servicio` NOT IN ('No Prestado','Recargo Nocturno No Prestado'))
          and
          (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
          and
          (`prefactura_dispositivo`.`tipo` = 'variable')
        )
      )
      -
      (
        select coalesce(sum(`prefactura_dispositivo`.`ftes_nocturno`),0)
        from `prefactura_dispositivo`
        where (
          (`prefactura_dispositivo`.`tipo_servicio` IN ('No Prestado','Recargo Nocturno No Prestado'))
          and
          (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
          and
          (`prefactura_dispositivo`.`tipo` = 'variable')
        )
      )
    )
  ) AS `ftes_nocturnos`,`dp`.`marca` AS `marca`,
  (
    select sum(`prefactura_dispositivo`.`ftes`)
    from `prefactura_dispositivo`
    where (
      (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
      and
      (`prefactura_dispositivo`.`tipo` = 'fijo')
    )
  ) AS `ftes_fijos`,
  (
    (
      select coalesce(sum(`prefactura_dispositivo`.`ftes`),0)
      from `prefactura_dispositivo`
      where (
        (`prefactura_dispositivo`.`tipo_servicio` NOT IN ('No Prestado','Recargo Nocturno No Prestado'))
        and
        (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
        and
        (`prefactura_dispositivo`.`tipo` = 'variable')
      )
    )
    -
    (select coalesce(sum(`prefactura_dispositivo`.`ftes`),0)
    from `prefactura_dispositivo`
    where (
      (`prefactura_dispositivo`.`tipo_servicio` IN ('No Prestado','Recargo Nocturno No Prestado'))
      and
      (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
      and
      (`prefactura_dispositivo`.`tipo` = 'variable')
    )
  )
) AS `ftes_variables`,
(
  select sum(`prefactura_dispositivo`.`valor_mes`)
  from `prefactura_dispositivo`
  where (
    (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
    and
    (`prefactura_dispositivo`.`tipo` = 'fijo')
  )
) AS `total_fijo`,
(
  (
    select coalesce(sum(`prefactura_dispositivo`.`valor_mes`),0)
    from `prefactura_dispositivo`
    where (
      (`prefactura_dispositivo`.`tipo_servicio` NOT IN ('No Prestado','Recargo Nocturno No Prestado'))
      and
      (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
      and 
      (`prefactura_dispositivo`.`tipo` = 'variable')
    )
  )
  -
  (
    select coalesce(sum(`prefactura_dispositivo`.`valor_mes`),0)
    from `prefactura_dispositivo`
    where (
      (`prefactura_dispositivo`.`tipo_servicio` IN ('No Prestado','Recargo Nocturno No Prestado'))
      and
      (`prefactura_dispositivo`.`id_prefactura_fija` = `dp`.`id`)
      and
      (`prefactura_dispositivo`.`tipo` = 'variable')
    )
  )
) AS `total_variable`,`dp`.`numero_factura` AS `Factura_numero`,`dp`.`fecha_factura` AS `fecha_factura`,
`dp`.`regional` AS `regional`,`dp`.`ciudad` AS `ciudad`,`em`.`nit` AS `Nit_empresa`
from (
  (`prefactura_fija` `dp` join `centro_costo` `cc`) join `empresa` `em`)
where ((convert(`dp`.`centro_costo_codigo` using utf8) = `cc`.`codigo`)
and (convert(`dp`.`empresa` using utf8) = `em`.`nit`))