<h1>SOLICITUD DE CDP</h1>
<h2>Comisi&oacute;n <?php echo $comision->getId()?> - <?php echo $vigencia?></h2>

<?php if($sf_user->getFlash('error')): ?>
	<div class="error"><?php echo $sf_user->getFlash('error')?></div>
<?php endif;?>
        
<?php echo form_tag('solicitud/guardar','name=cxp')?> 
<?php echo input_hidden_tag('id',$comision->getId())?>
<?php echo input_hidden_tag('vigencia',$comision->getVigencia())?>
        
<table class="show">
  <tr>
    <th align="left"><strong>&Aacute;REA</strong></th>
    <td><?php echo $comision->getAreas()->getDescripcion()?></td>
  </tr>
  <tr>
    <th align="left"><strong>FECHA</strong></th>
    <td><?php echo $comision->getFecha()?></td>
  </tr>
  <tr>
    <th align="left"><strong>TIPO COMISI&Oacute;N</strong></th>
    <td><?php echo strtoupper($comision->getTiposComision()->getDescomi())?> <?php echo $comision->getInterrupcion() ? 'CON INTERRUPCION' : ''?></td>
  </tr>
  <tr>
    <th align="left"><strong>PROYECTO</strong></th>
    <td><?php echo strtoupper('<b>'.$comision->getCodpro().'</b> '.$comision->getProyectos()->getDescripcion())?></td>
  </tr> 
</table>
<br/>

<?
$con = Propel::getConnection();

  //SACO LOS OBJETOS DE LA COMISION
  
    $sql="select d.codobj as CODIGO,obj.descripcion AS DESCRIPCION,obj.id AS ID, sum(nvl(d.valor,0)) as VALOR
		from COMISIONADOS_OBJS d
		join OBJETOS_GASTO obj ON obj.codigo=d.codobj
		where d.id_comision=".$comision->getId()." and d.vigencia=".$vigencia."
		group by d.codobj,obj.descripcion,obj.id
		";

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

    $obj_comision=$sentencia->executeQuery();
    $obj_comision1=$sentencia->executeQuery();

?>
<br/>
<h4>DETALLE DE PROGRAMACION</h4>
<table class="show">
<thead>
<tr>
<th>OBJETO DE GASTO</th>
<th>TOTAL COMISION</th>
<th>VALOR RESERVADO</th>

</tr>
</thead>
<tbody>


<?
$total_1=0;
$total_2=0;
foreach($obj_comision1 as $resumen_comis){
$total_1+=$resumen_comis['VALOR'];
$total_2+=$resumen_comis['VALOR'];

echo "<tr>";
echo "<td>".$resumen_comis['CODIGO']." - ".$resumen_comis['DESCRIPCION']."</td>";
echo "<td><input style='text-align:right' readonly type='text'   id='valor_idO_" . $resumen_comis['ID']."' name='valor_idO_" . $resumen_comis['ID']."' value='".number_format($resumen_comis['VALOR'],2)."'/></td>";
echo "<td><input style='text-align:right' readonly type='text'   id='reserva_idO_" . $resumen_comis['ID']."' name='reserva_idO_" . $resumen_comis['ID']."' value='".number_format($resumen_comis['VALOR'],2)."'/></td>";
echo "</tr>";
}?>
<?
echo "<tr>";
echo "<td><b>TOTAL</b></td>";
echo "<td><b><input style='text-align:right' readonly type='text'   id='total_valor' name='total_valor' value='".number_format($total_1,2)."'/></b></td>";
echo "<td><b><input style='text-align:right' readonly type='text'    id='total_reserva' name='total_reserva' value='".number_format($total_2,2)."'/></b></td>";
echo "</tr>";

?>
</tbody>

</table>
<br/>
<h4>DETALLE DE SOLICITUD</h4>
<table class="show">
    <thead>
                            <tr>
                                <th>CTA<br/>PROG</th>
                                <th>SUBC<br/>SUBP</th>
                                <th>OBJG<br/>PROY</th>
                                <th>ORD<br/>SPROY</th>
                                <th>SUBORD<br/>---------</th>
                                <th>DESCRIPCIÓN</th>
                                <th>REC<br/></th>
                                <th>OBJETO DE GASTO</th>
                                <th>RESERVAR<br/></th>
                            </tr>
                        </thead>
                        <tbody> 
    
<?php


    $total=0;
    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 UNO,DOS,TRES,CUATRO,CINCO,DESCRIPCION,RECURSO,ID_CPO,ID_ASIGN,ID_OBJ_GASTO,OBJETO,SUM(ASIGNADO) AS ASIGNADO,SUM(CDPS) AS CDPS,SUM(SOLICITUDES) AS SOLICITUDES, 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 UNO,DOS,TRES,CUATRO,CINCO,DESCRIPCION,RECURSO,ID_CPO,ID_ASIGN,ID_OBJ_GASTO,OBJETO
ORDER BY UNO,DOS,TRES,CUATRO,CINCO,DESCRIPCION,RECURSO,ID_CPO,ID_ASIGN,ID_OBJ_GASTO,OBJETO";

    $sentencia = $con->prepareStatement($sql);
    $saldos=$sentencia->executeQuery();
    $bandera=0;	
   	
    foreach($saldos as $saldos){
        if($bandera==0){
	$valor=$valor;
	$bandera=1;
	}else{
	$valor=0;
	}
	$total+=$valor;
        echo "<tr>";
        
        echo "<td>".$saldos['UNO']."</td>";
        echo "<td>".$saldos['DOS']."</td>";
        echo "<td>".$saldos['TRES']."</td>";
        echo "<td>".$saldos['CUATRO']."</td>";
        echo "<td>".$saldos['CINCO']."</td>";
        echo "<td>".$saldos['DESCRIPCION']."</td>";
        echo "<td>".$saldos['RECURSO']."</td>";
        echo "<td>".$saldos['OBJETO']."</td>";
	if($valor==0){
	echo "<td><input style='text-align:right' type='text'   id='compromiso_idO_" . $saldos['ID_OBJ_GASTO'] . "_idA_" . $saldos['ID_ASIGN']."' name='compromiso_idO_" . $saldos['ID_ASIGN'] . "_idA_" . $saldos['ID_OBJ_GASTO']."' onblur='ActualizaValor(this.id)' /></td>";
	}else{
        echo "<td><input style='text-align:right' type='text'   id='compromiso_idO_" . $saldos['ID_OBJ_GASTO'] . "_idA_" . $saldos['ID_ASIGN']."' name='compromiso_idO_" . $saldos['ID_ASIGN'] . "_idA_" . $saldos['ID_OBJ_GASTO']."' onblur='ActualizaValor(this.id)' value='".number_format($valor,2)." ' /></td>";
        }
        echo "</tr>";
        
    }
     
  }?>
  <?
  echo "<tr>";
  echo "<td colspan='8'>TOTAL</td>";
  echo "<td><input style='text-align:right' type='text' readonly id='total' name='total' value='".number_format($total,2)."'/></td>";
        
  
  
  ?>                          
  </tbody>  
</table>

<input type="image" value="Guardar" title="Guardar" onclick="if(validar())return true;else return false;" src="<?php echo image_path('guardar') ?>" />
 &nbsp; | &nbsp;
                           
                                <a <?php echo isset($overlay) && $overlay ? 'class="simplemodal-close"' : '' ?> href="<?php echo url_for(isset($onCancel) ? urldecode($onCancel) : 'comisionar/filter_preview?id='.$id_comision.'&vigencia='.$vigencia) ?>"><?php echo image_tag('cancel', 'title="Cancelar"') ?></a>
                           

<script>

function ActualizaValor(id){
    
valor=eval(quitarDecimales(document.getElementById(id).value));

if(valor==null){
document.getElementById(id).value="";
}else{

array=id.split("_");
id_obj=array[2];

var elementos = document.cxp.elements.length;
                var suma=0;
                for(var i=0;i<elementos;i++){


                    var indice=(document.cxp.elements[i].id).search('compromiso_idO_'+id_obj);

                    if(indice!=-1){
                    var valor_campo=eval(quitarDecimales(document.cxp.elements[i].value));
                    if(!valor_campo){

                    valor_campo=0;

                    }
                    var suma=suma+valor_campo;
                    }

                }
		valor_comp=eval(quitarDecimales(document.getElementById('valor_idO_'+id_obj).value));
		
		
		if(suma<=valor_comp){
				
                var campo_asig=document.getElementById('reserva_idO_'+id_obj);
		campo_asig.value=formatearNumero(suma, '2', '.',',');
		document.getElementById(id).value=formatearNumero(valor, '2', '.',',');
		
		//RECALCULO EL TOTAL

		var elementos = document.cxp.elements.length;
                var suma=0;
                for(var i=0;i<elementos;i++){


                    var indice=(document.cxp.elements[i].id).search('compromiso_');

                    if(indice!=-1){
                    var valor_campo=eval(quitarDecimales(document.cxp.elements[i].value));
                    if(!valor_campo){

                    valor_campo=0;

                    }
                    var suma=suma+valor_campo;
                    }

                }
		var total_reserva=document.getElementById('total_reserva');
		var total=document.getElementById('total');

		total_reserva.value=formatearNumero(suma, '2', '.',',');
		total.value=formatearNumero(suma, '2', '.',',');
		

		
	

		}else{

		alert("El valor reservado no debe superar el valor programado a nivel de objeto de gasto");
		document.getElementById(id).value="";
		}
		
 }  
}

function validar(){

var elementos = document.cxp.elements.length;
                var suma=0;
                for(var i=0;i<elementos;i++){


                    var indice=(document.cxp.elements[i].id).search('valor_idO_');
		

                    if(indice!=-1){
		    id=document.cxp.elements[i].id;
	
                    var valor_1=eval(quitarDecimales(document.getElementById(id).value));
		    id_2=id.replace(/valor/,'reserva');
                    var valor_2=eval(quitarDecimales(document.getElementById(id_2).value));
			
                   if(valor_2!=valor_1){
			alert("No ha completado la distribucion de la comision. Favor Verificar");
			return false;

		   }
	
		   }		    	

                }

return false;


}

function formatearNumero(num, numDec, decSep, thousandSep){
            var arg;
            var Dec;
            Dec = Math.pow(10, numDec);
            if (typeof(num) == 'undefined') return;
            if (typeof(decSep) == 'undefined') decSep = ',';
            if (typeof(thousandSep) == 'undefined') thousandSep = '.';
            if (thousandSep == '.')
                arg=/./g;
            else
                if (thousandSep == ',') arg=/,/g;
            if (typeof(arg) != 'undefined') num = num.toString().replace(arg,'');
            num = num.toString().replace(/,/g, '.');
            if (isNaN(num)) num = "0";
            sign = (num == (num = Math.abs(num)));
            num = Math.floor(num * Dec + 0.50000000001);
            cents = num % Dec;
            num = Math.floor(num/Dec).toString();
            if (cents < (Dec / 10)) cents = "0" + cents;
            for (var i = 0; i < Math.floor((num.length - (1 + i)) / 3); i++)
                num = num.substring(0, num.length - (4 * i + 3)) + thousandSep + num.substring(num.length - (4 * i + 3));
            if (Dec == 1)
                return (((sign)? '': '-') + num);
            else
                return (((sign)? '': '-') + num + decSep + cents);
        }

        function quitarDecimales(num){
            if(num!=""){
                num=num.replace(/,/gi, '')
                var cents
                cents=num.split('.')
                if(cents[1]!=""){
                    if (cents[1]==00){
                        num=cents[0]
                    }
                }
            }
            return num
        }



</script>
