<?php

/**
 * solicitud actions.
 *
 * @package    comisiones
 * @subpackage solicitud
 * @author     Software House LTDA <gerencia@sofhouse.net>
 * @version    SVN: $Id: actions.class.php 9301 2008-05-27 01:08:46Z dwhittle $
 */
class solicitudActions extends sfActions
{
 /**
  * Executes index action
  *
  * @param sfRequest $request A request object
  */
  public function executeCrear($request)
  {
  $this->id_comision = $this->getRequestParameter('id');
  $vigencia = $this->getRequestParameter('vigencia');
  $this->vigencia=$vigencia;
  $this->comision = ComisionesPeer::retrieveByPK($this->id_comision,$vigencia);

  $id_cpo=$this->comision->getProyectos()->getId();
  $this->id_cpo=$id_cpo;
  $con = Propel::getConnection();

  //SACO LOS OBJETOS DE LA COMISION
  
    $sql="select d.codobj as CODIGO, sum(nvl(d.valor,0)) as VALOR
		from COMISIONADOS_OBJS d
		where d.id_comision=".$this->comision->getId()." and d.vigencia=".$vigencia."
		group by d.codobj
		";

    $sentencia = $con->prepareStatement($sql);

    $obj_comision=$sentencia->executeQuery();

foreach ($obj_comision as $objetos) {

    //SACO EL ID DEL OBJETO DE GASTO
    $sql="select d.id as ID, d.descripcion as DESCRIPCION
		from OBJETOS_GASTO d
		where d.codigo=".$objetos['CODIGO'];

    $sentencia = $con->prepareStatement($sql);
    $obj_gasto=$sentencia->executeQuery();

    foreach($obj_gasto as $obj_gasto){
    $id_obj_gasto=$obj_gasto['ID'];
    $descripcion=$obj_gasto['DESCRIPCION'];

    }

    $valor=$objetos['VALOR'];

     $sql="  select ID_CPO,ID_ASIGN,ID_OBJ_GASTO,SUM(ASIGNADO)-SUM(CDPS)-SUM(SOLICITUDES) as SALDO
FROM(
select gen.codigo AS UNO,cod.cod_rub2 AS DOS,cod.cod_rub3 AS TRES,cod.cod_rub4 AS CUATRO,cod.cod_rub5 AS CINCO,cod.descripcion,rec.codigo AS RECURSO,pro.codigo,asigna.id_cpo,asigna.id_asign,
asigna.id_obj_gasto,objetos.codigo||' '||objetos.descripcion AS OBJETO,asigna.id_vigencia,asigna.ano_fiscal, sum(nvl(asigna.valor_objeto,0)) + sum(nvl(novedades.valor,0)) as ASIGNADO, sum(nvl(cdps.valor,0))+sum(nvl(cdpscom.valor,0)) as CDPS, sum(NVL(solicitudes.valor,0)) as solicitudes

from PR_ASIGNA_OBJ_GASTO asigna
left join PROYECTOS pro ON asigna.id_cpo = pro.id
left join OBJETOS_GASTO objetos ON objetos.id = asigna.id_obj_gasto
left join PR_ASIG_PRESUP asip ON asigna.id_asign = asip.id
and asip.id_vigencia = asigna.id_vigencia
and asip.vigencia = asigna.ano_fiscal
left join PR_APROPIACION aprop ON asip.id_apropiacion = aprop.id
and asip.id_vigencia = aprop.id_vigencia
and asip.vigencia = aprop.vigencia
left join PR_CODIF_PRESUP cod ON aprop.id_rubro = cod.id_rubro
left join PR_PROG_GEN gen ON cod.cod_rub1 = gen.id
left join PR_TIPO_REC rec ON aprop.id_tipo_rec = rec.id

left join(

SELECT c.id_cpo,f.id_asign, g.id_obj_gasto, sum(nvl(g.credito,0)) - sum(nvl(g.contracredito,0)) valor
from PR_NOV_APO g
left join PR_APO_NPO f ON f.id_apo_npo = g.id_apo_npo and f.id_vigencia=g.id_vigencia and f.ano_fiscal=g.vigencia
left join PR_APO c ON c.id_apo=f.id_apo AND c.id_vigencia=f.id_vigencia and c.ano_fiscal=f.ano_fiscal
group by c.id_cpo, f.id_asign, g.id_obj_gasto

) novedades ON novedades.id_cpo=asigna.id_cpo
and novedades.id_asign=asigna.id_asign
and novedades.id_obj_gasto=asigna.id_obj_gasto

left join
(
select id_cpo,id_asign,id_obj_gasto,sum(valor) as valor from(

select pca.id_cpo,pca.id_asign,pca.id_obj_gasto,
case when hist.valor is null then sum(pca.valor) else ( nvl(sum(hist.valor),0)+nvl(sum(novedades.valor),0) ) end as VALOR
from PROYECTOS p
left join PR_CPO_ASIGN pca ON pca.id_cpo = p.id
join PR_CDP_CPO pcc ON pca.id_pr_cdp_cpo = pcc.id and pca.ano_fiscal = pcc.ano_fiscal and pca.id_vigencia = pcc.id_vigencia
join PR_CDP pcdp ON pcc.id_cdp = pcdp.id and pcc.id_vigencia = pcdp.id_vigencia and pcc.ano_fiscal = pcdp.ano_fiscal
left join PR_HISTORIAL_CDP hist ON pcdp.id = hist.id_cdp
and hist.id_vigencia = pcdp.id_vigencia and hist.vigencia = pcdp.ano_fiscal and hist.id_asign = pca.id_asign
and hist.id_obj_gasto = pca.id_obj_gasto and hist.id_cpo = pca.id_cpo

left join(
select nov.id_cdp,sum(nvl(cval.debito,0) - nvl(cval.credito,0)) as valor,
cval.id_asign, cval.id_obj_gasto
from PR_NOVEDAD_CDP_VAL cval
join PR_NOVEDAD_CDP nov ON cval.id_novedad_cdp = nov.id_novedad_cdp
group by nov.id_cdp,cval.id_asign, cval.id_obj_gasto
)
novedades ON novedades.id_asign=pca.id_asign
and novedades.id_obj_gasto = pca.id_obj_gasto
and novedades.id_cdp = pcdp.id

group by pca.id_cpo, pca.id_asign, pca.id_obj_gasto,hist.valor

)

group by id_cpo, id_asign, id_obj_gasto

)cdps ON cdps.id_cpo=asigna.id_cpo
and cdps.id_asign=asigna.id_asign
and cdps.id_obj_gasto=asigna.id_obj_gasto

left join
(
select id_cpo,id_asign,id_obj_gasto,sum(valor) as valor from(

select pca.id_cpo,pca.id_asign,pca.id_obj_gasto,
case when hist.valor is null then sum(pca.valor) else ( nvl(sum(hist.valor),0)+nvl(sum(novedades.valor),0) ) end as VALOR
from PROYECTOS p
left join PR_CPO_ASIGN pca ON pca.id_cpo = p.id
join PR_CDP_CPO pcc ON pca.id_pr_cdp_cpo = pcc.id and pca.ano_fiscal = pcc.ano_fiscal and pca.id_vigencia = pcc.id_vigencia
join PR_CDP_COMIS pcdp ON pcc.id_cdp = pcdp.id and pcc.id_vigencia = pcdp.id_vigencia and pcc.ano_fiscal = pcdp.ano_fiscal
left join PR_HISTORIAL_CDP hist ON pcdp.id = hist.id_cdp
and hist.id_vigencia = pcdp.id_vigencia and hist.vigencia = pcdp.ano_fiscal and hist.id_asign = pca.id_asign
and hist.id_obj_gasto = pca.id_obj_gasto and hist.id_cpo = pca.id_cpo

left join(
select nov.id_cdp,sum(nvl(cval.debito,0) - nvl(cval.credito,0)) as valor,
cval.id_asign, cval.id_obj_gasto
from PR_NOVEDAD_CDP_VAL cval
join PR_NOVEDAD_CDP nov ON cval.id_novedad_cdp = nov.id_novedad_cdp
group by nov.id_cdp,cval.id_asign, cval.id_obj_gasto
)
novedades ON novedades.id_asign=pca.id_asign
and novedades.id_obj_gasto = pca.id_obj_gasto
and novedades.id_cdp = pcdp.id

group by pca.id_cpo, pca.id_asign, pca.id_obj_gasto,hist.valor

)

group by id_cpo, id_asign, id_obj_gasto

)cdpscom ON cdpscom.id_cpo=asigna.id_cpo
and cdpscom.id_asign=asigna.id_asign
and cdpscom.id_obj_gasto=asigna.id_obj_gasto

left join (

select deta.id_cpo,deta.id_asign,deta.id_obj_gasto,sum(deta.valor) AS VALOR

from PR_SOLIC_CDP_DETA deta
join PR_SOLICITUD_CDP soli ON soli.id_solicitud_cdp=deta.id_solicitud_cdp and soli.id_vigencia=deta.id_vigencia and soli.vigencia=deta.vigencia
where soli.estado!=-1 and not exists (select pcdp.id_solicitud_cdp from PR_CDP pcdp WHERE pcdp.ano_fiscal=soli.vigencia and pcdp.id_vigencia=soli.id_vigencia and pcdp.id_solicitud_cdp=soli.id_solicitud_cdp)


group by deta.id_cpo, deta.id_asign, deta.id_obj_gasto

) solicitudes ON solicitudes.id_cpo=asigna.id_cpo
and solicitudes.id_asign=asigna.id_asign
and solicitudes.id_obj_gasto=asigna.id_obj_gasto



group by gen.codigo,cod.cod_rub2,cod.cod_rub3,cod.cod_rub4,cod.cod_rub5,cod.descripcion,rec.codigo,pro.codigo,asigna.id_cpo,asigna.id_asign,
asigna.id_obj_gasto,objetos.codigo||' '||objetos.descripcion,asigna.id_vigencia,asigna.ano_fiscal

)
WHERE ID_VIGENCIA=1 AND ANO_FISCAL=".$vigencia." AND ID_CPO=".$id_cpo." AND ID_OBJ_GASTO=".$id_obj_gasto."
GROUP BY ID_CPO,ID_ASIGN,ID_OBJ_GASTO
ORDER BY ID_CPO,ID_ASIGN,ID_OBJ_GASTO";

    $sentencia = $con->prepareStatement($sql);
    $saldos=$sentencia->executeQuery();
    foreach($saldos as $saldos){
    $saldo=$saldos['SALDO'];
    }

   
    if($saldo<$valor){

        $this->getUser()->setFlash('error', 'El valor para el objeto de gasto '.$objetos['CODIGO']."-".$descripcion.' del plan de comisión, supera el saldo asignado en apropiacion.'.$valor);
       // $this->redirect('comisionar/filter_preview?id='.$this->id_comision.'&vigencia='.$vigencia);

    }

  }


  }
  
  public function executeGuardar($request)
  {
      
      
      
  }
  
  
}
