<?php

class ReciboController extends Controller
{
	/**
	 * Declares class-based actions.
	 */
	public function filters()
                {
	return array(
			'accessControl', 
			
		);
}
	
	public function accessRules()
	{
		return array(
			
			array('allow', 
				'actions'=>array('index','Selectdos','selecttres','Constanciapdf','Recibohtml','Recibopdf','Pruebarecibopdf','arcpdf','arcpdf2016','arcpdf2017','arcpdf2018','reporteminci'),
				'users'=>array('@'),
			),
			
		
			array('deny',  
				'users'=>array('*'),
			),
		);
	}


	public function actionPruebarecibopdf()
	{
            $anio= substr($_GET['periodo_anual'], 7);
              $codnom= $_GET['codnom'];
                  $periodo= $_GET['periodo'];
            
             $cedula=$this->Obtener_cedula();
            
             
          
		
		$this->render('pruebarecibopdf',array('cedula'=>$cedula,'anio'=>$anio,'periodo'=>$periodo,'codnom'=>$codnom));
	}
	public function actionIndex()
	{
		
		$this->render('index');
	}
        
        public function actionSelectdos()
        { 
           
            $cedula=$this->Obtener_cedula();
            $ano = $_GET['periodo'];
         
            if($ano=='enterprise_2017'){
                $periodo_anual= substr($ano, 11); 
            }else if($ano=='tves_2018'){
                $periodo_anual= substr($ano, 5);
            }else if($ano=='rcm_2018'){
                $periodo_anual= substr($ano, 4);
            }else if($ano=='tves_2019'){
                $periodo_anual= substr($ano, 5);
	    }else{
                $periodo_anual= substr($ano, 7);
            }
                   
            //$lista = NivelDos::model()->findAll('id_nivel_uno = :id_uno',array(':id_uno'=>$id_uno));
            $sentencia="select distinct a.codnom, a.desnom
     
                from 
                    public.sno_hsalida c, 
                    public.sno_hpersonalnomina b, 
                    public.sno_nomina a 

                where 
                    b.codemp = c.codemp AND
                    b.codnom= c.codnom AND
                    b.codper = c.codper AND
                    b.anocur = c.anocur AND
                    b.codperi = c.codperi AND
                    a.codemp = b.codemp AND
                    a.codnom= b.codnom AND
                    c.valsal != 0 AND 
                    c.valsal IS NOT NULL  AND 
                    (c.tipsal in('A', 'D', 'A ', 'D ', 'V1', 'W1', 'V2', 'W2', 'P1', 'V3', 'W3')) AND 
                    b.codper like '%".$cedula."' AND
                    b.anocur = "."'$periodo_anual'"."
                    order by a.codnom, a.desnom";
            
            $con=Yii::app()->$ano->createCommand($sentencia);
            $datareader=$con->queryAll();
                        
            echo CHtml::tag('option', array('value' => ''), 'Seleccione', true);
                    
            foreach ($datareader as $nom){
            //original 
            //echo CHtml::tag('option',array('value'=>$nom['codnom']),CHtml::encode($nom['desnom']), true );
            //Agregado por JEYSUS FARIÑAS
//                if($nom['codnom']=='0038'){
//                    $nom['codnom']='';
//                    $nom['desnom']='';
//                    CHtml::tag('option',array('value'=>$nom['codnom']),CHtml::encode($nom['desnom']), true );
//                }else{
//                    echo CHtml::tag('option',array('value'=>$nom['codnom']),CHtml::encode($nom['desnom']), true );
//                }
            
                if($nom['codnom'] != '0001' && $nom['codnom'] != '0003' && $nom['codnom'] != '0005' && $nom['codnom'] != '0014' && 
                   $nom['codnom'] != '0015' && $nom['codnom'] != '0016' ){
                    $nom['codnom']='';
                    $nom['desnom']='';
                    CHtml::tag('option',array('value'=>$nom['codnom']),CHtml::encode($nom['desnom']), true );
                } else {
                    echo CHtml::tag('option',array('value'=>$nom['codnom']),CHtml::encode($nom['desnom']), true );
                }
                
                
                
                
            //endcode    
            }
            
        }
        public function actionSelecttres()
        {   
            $cedula=$this->Obtener_cedula();
            $ano =  $_GET['periodo'];
            if($ano=='enterprise_2017'){
                $periodo_anual= substr($ano, 11); 
            }else if($ano=='tves_2018'){
                $periodo_anual= substr($ano, 5);
            }else if($ano=='rcm_2018'){
                $periodo_anual= substr($ano, 4);
            }else if($ano=='tves_2019'){
                $periodo_anual= substr($ano, 5);
            }else{
                $periodo_anual= substr($ano, 7);
            }
            
            $codnom =  $_GET['tipo'];
            
            /*$m = ['ced'=>$cedula,'p'=>$periodo_anual,'c'=>$codnom];
            
            var_dump($m);die;*/
            
//$sentencia="select distinct on (codperi) codperi from sno_hsalida where codper like '%".$cedula."' and
//codnom = '".$codnom."' and anocur='".$periodo_anual."'";
 
//sentencia original 24/03/2018            
/*$sentencia="select distinct on (h.codperi) h.codperi 
from sno_hsalida h
inner join sno_periodo p
on p.codperi = h.codperi
and p.codnom = h.codnom
where h.codper like '%".$cedula."' and
h.codnom = '".$codnom."' and h.anocur='".$periodo_anual."' and p.estatus_periodo = '1'";*/
//fin sentencia            

$sentencia = "select DISTINCT codperi as codperi "
           . "from sno_hsalida "
           . "where codper like '%".$cedula."' "
           . "and codnom = '".$codnom."' and anocur = '".$periodo_anual."'";
            
 
 
  $con=Yii::app()->$ano->createCommand($sentencia);
  $datareader=$con->queryAll();    
//            $lista = NivelTres::model()->findAll('id_nivel_dos = :id_dos',array(':id_dos'=>$id_dos));
//            $lista = CHtml::listData($lista,'id_nivel_tres','descripcion');
  
        
  echo CHtml::tag('option', array('value' => ''), 'Seleccione', true);
            
foreach ($datareader  as $row){
/*$sentencia4="select * from sno_periodo where codperi = '".$row["codperi"]."' and
codnom ='".$codnom."' order by fecdesper desc ";
*/
$sentencia4="select *, codperi::integer as correlativo from sno_periodo where codperi = '".$row["codperi"]."' and
codnom ='".$codnom."' order by fecdesper desc";

$con4 = Yii::app()->$ano->createCommand($sentencia4);
        

$row4=$con4->queryRow();

   $fecha=$row4["fecdesper"];
$fecha_m = explode("-", $fecha);
$dia_m =$fecha_m[2];
$mes_m =$fecha_m[1];
$anio_m=$fecha_m[0];
$desde= $dia_m.'-'.$mes_m.'-'.$anio_m;   

 
   $fecha2=$row4["fechasper"];
$fecha_m = explode("-", $fecha2);
$dia_m =$fecha_m[2];
$mes_m =$fecha_m[1];
$anio_m=$fecha_m[0];
$hasta= $dia_m.'-'.$mes_m.'-'.$anio_m;  


$codperi[]=$row4['codperi'];
$fecdesper[]=$row4["fecdesper"];
$fechasper[]=$row4["fechasper"];

//echo CHtml::tag('option',array('value'=> $row4['codperi']),CHtml::encode($row4['codperi']. " fecha: ".$desde." / ".$hasta), true );
}   


$periodos=count($codperi);
rsort($codperi);
rsort($fecdesper);
rsort($fechasper);

//
for($i=0; $i<$periodos;$i++){
  echo CHtml::tag('option',array('value'=> $codperi[$i]),CHtml::encode($codperi[$i]. " fecha: ".$fecdesper[$i]." / ".$fechasper[$i]), true );
}


                   
            
        }
        
         public function Obtener_db_actual()
	{
//             $mes_actual=date('M');
//         
//             if($mes_actual=='Jan'){
//                 $fecha=date('Y');
//                 $nuevafecha = strtotime ( '-1 year' , strtotime ( $fecha ) ) ;
//                 $nuevafecha = date ( 'Y' , $nuevafecha );
// 
//             $db_actual="db_tves".$nuevafecha;
//             }else{
             // $db_actual="db_tves".date('Y');   
		  $db_actual="tves_2018";   
                 
                 
            // }
             return $db_actual;
        
         }
        
        public function Obtener_cedula()
	{
            
          $userid= Yii::app()->user->id;
          $consulta=MUsuario::model()->findByPK($userid);
          $cedula=$consulta->usuario;
          
          
		
		return $cedula;
			
	}
        
        public function Obtener_periodo_actual()
	{
            $db_actual=$this->Obtener_db_actual();
            
            $ano_actual=date('Y');
            $mes_actual=date('M');
            
            if($mes_actual=='nada'){
                $ano_pasado=$ano_actual-1;
                $select="select to_char(to_number(max(s.codperi),'099'),'099') as codigo_periodo 
                 from 
                 public.sno_hpersonalnomina s, 
                 public.sno_personal t, 
                 public.sno_hperiodo per
                 where t.cedper='".$this->Obtener_cedula()."' and  s.codper = t.codper
                 and per.codnom in('0001','0002','0003','0004','0005')
                 and s.codnom= per.codnom
                 and (current_date - per.fecdesper) <=31 and s.anocur='".$ano_pasado."'
                 and s.staper='1' ;"; 
                 $con=Yii::app()->$db_actual->createCommand($select);
        }else{
            $select="select to_char(to_number(max(s.codperi),'099'),'099') as codigo_periodo 
                 from 
                 public.sno_hpersonalnomina s, 
                 public.sno_personal t, 
                 public.sno_hperiodo per
                 where t.cedper='".$this->Obtener_cedula()."' and  s.codper = t.codper
                 and per.codnom in('0001','0002','0003','0004','0005')
                 and s.codnom= per.codnom
                 and (current_date - per.fecdesper) <=31 and s.anocur='".$ano_actual."' 
                 and s.staper='1' ;";
             $con=Yii::app()->$db_actual->createCommand($select);
            }
            
            
             $datareader=$con->queryRow();
		
		return  trim($datareader['codigo_periodo']);
			
	}
        
         public function Obtener_perfil()
	{
             //cambio en enero a año anterior
              $ano='2019';
             
              ///////////////
             $db_actual=$this->Obtener_db_actual();
             $select="SELECT  
                        pn.codper, 
                        pn.obsrecper as cargo_observacion,
                         p.rifper as rif,
                         p.dirper as direccion,
                           pnn.desnom as tipo_nomina, 
                        p.cedper as usuario, 
                        p.nomper AS nombre, 
                         p.apeper AS apellido,
                        to_number( pn.codper, '99999999999') AS cedula,
                        p.sexper as sexo,
                        p.fecingper as fecha_ingreso,
                        p.fecegrper as fecha_egreso,
                        p.fecjubper as fecha_jubilacion,
                        p.fecnacper as nacimiento,
                        hc.descar AS cargo,
                        u.desuniadm AS unidad_administrativa,
                        tp.dentippersss AS condicion_trabajador,
                        pn.codcueban AS cuenta_bancaria
FROM
                        public.sno_hpersonalnomina pn,
                                       public.sno_nomina pnn,
                        public.sno_personal p, 
                        public.sno_tipopersonalsss tp, 
                        public.sno_hresumen r, 
                        public.sno_hunidadadmin u, 
                        public.sno_hcargo hc, 
                        public.sno_hasignacioncargo hrac, 
                        public.sno_hperiodo hp 

WHERE
                        pnn.codnom = pn.codnom AND                        
                     pn.codemp = u.codemp  AND
                        pn.anocur = u.anocur AND
                        pn.codperi= u.codperi AND
                        pn.codnom = u.codnom AND
                        pn.minorguniadm = u.minorguniadm AND
                        pn.ofiuniadm = u.ofiuniadm AND
                        pn.uniuniadm = u.uniuniadm AND
                        pn.depuniadm = u.depuniadm AND
                        pn.prouniadm = u.prouniadm AND
                        pn.codemp = hc.codemp AND
                        pn.codnom = hc.codnom AND
                        pn.anocur = hc.anocur AND
                        pn.codcar = hc.codcar AND
                        pn.codperi = hc.codperi AND
                        pn.codnom = hrac.codnom AND
                        pn.anocur = hrac.anocur AND
                        pn.codasicar = hrac.codasicar AND
                        pn.codperi = hrac.codperi AND
                        pn.codemp = hrac.codemp AND
                        pn.codemp = hp.codemp AND
                        pn.codnom = hp.codnom AND
                        pn.anocur = hp.anocur AND
                        pn.codperi = hp.codperi AND
                        p.codemp= pn.codemp AND
                        p.codper= pn.codper AND
                        tp.codemp= p.codemp AND
                        tp.codtippersss = p.codtippersss AND
                        r.codemp = pn.codemp AND
                        r.codnom = pn.codnom AND
                        r.anocur = pn.anocur AND
                        r.codperi = pn.codperi AND
                        r.codper = pn.codper AND
                           pn.sueintper != '0' and
                        hp.codnom in('0001','0002','0003','0004','0005','0006','0007','0008','0009','0010') and
                        p.cedper = '".$this->Obtener_cedula()."' AND 

                        
                        pn.anocur='".$ano."' and
                        pn.codperi= '".$this->Obtener_periodo_actual()."';" ;
             
                        $con=Yii::app()->$db_actual->createCommand($select);
                        $data=$con->queryRow();
                       
                        return  $data;

        }
        
        
        
         public function Obtener_perfil2($empleado)
    {
             //cambio en enero a año anterior
              $ano='2018';
           
              ///////////////
             $db_actual=$this->Obtener_db_actual();
             $select="SELECT  
                        pn.codper, 
                        pn.obsrecper as cargo_observacion,
                         p.rifper as rif,
                         p.dirper as direccion,
                           pnn.desnom as tipo_nomina, 
                        p.cedper as usuario, 
                        p.nomper AS nombre, 
                         p.apeper AS apellido,
                        to_number( pn.codper, '99999999999') AS cedula,
                        p.sexper as sexo,
                        p.fecingper as fecha_ingreso,
                        p.fecegrper as fecha_egreso,
                        p.fecjubper as fecha_jubilacion,
                        p.fecnacper as nacimiento,
                        hc.descar AS cargo,
                        u.desuniadm AS unidad_administrativa,
                        tp.dentippersss AS condicion_trabajador,
                        pn.codcueban AS cuenta_bancaria
FROM
                        public.sno_hpersonalnomina pn,
                                       public.sno_nomina pnn,
                        public.sno_personal p, 
                        public.sno_tipopersonalsss tp, 
                        public.sno_hresumen r, 
                        public.sno_hunidadadmin u, 
                        public.sno_hcargo hc, 
                        public.sno_hasignacioncargo hrac, 
                        public.sno_hperiodo hp 

WHERE
                        pnn.codnom = pn.codnom AND                        
                     pn.codemp = u.codemp  AND
                        pn.anocur = u.anocur AND
                        pn.codperi= u.codperi AND
                        pn.codnom = u.codnom AND
                        pn.minorguniadm = u.minorguniadm AND
                        pn.ofiuniadm = u.ofiuniadm AND
                        pn.uniuniadm = u.uniuniadm AND
                        pn.depuniadm = u.depuniadm AND
                        pn.prouniadm = u.prouniadm AND
                        pn.codemp = hc.codemp AND
                        pn.codnom = hc.codnom AND
                        pn.anocur = hc.anocur AND
                        pn.codcar = hc.codcar AND
                        pn.codperi = hc.codperi AND
                        pn.codnom = hrac.codnom AND
                        pn.anocur = hrac.anocur AND
                        pn.codasicar = hrac.codasicar AND
                        pn.codperi = hrac.codperi AND
                        pn.codemp = hrac.codemp AND
                        pn.codemp = hp.codemp AND
                        pn.codnom = hp.codnom AND
                        pn.anocur = hp.anocur AND
                        pn.codperi = hp.codperi AND
                        p.codemp= pn.codemp AND
                        p.codper= pn.codper AND
                        tp.codemp= p.codemp AND
                        tp.codtippersss = p.codtippersss AND
                        r.codemp = pn.codemp AND
                        r.codnom = pn.codnom AND
                        r.anocur = pn.anocur AND
                        r.codperi = pn.codperi AND
                        r.codper = pn.codper AND
                           pn.sueintper != '0' and
                        hp.codnom in('0001','0002','0003','0004','0005','0006','0007','0008','0009','0010') and
                        p.cedper = '".$empleado."' AND 
                        pn.anocur='".$ano."' and
                        pn.codperi= '".$this->Obtener_periodo_actual()."';" ;
             
                        $con=Yii::app()->$db_actual->createCommand($select);
                        $data2=$con->queryRow();
                       
                        return  $data2;

        }
        
        
        
        
        
        
        
          public function actionConstanciapdf()
        {
             
	     

	//Yii:: app () ->cache->flush() ;die;
	$tipo= $_GET['tipoc'];
             $cedula=$this->Obtener_cedula();
             $mPDF1 = Yii::app()->ePdf->mpdf();
             $mPDF1 = Yii::app()->ePdf->mpdf('', 'A5');
             //$mPDF1->SetHTMLFooter('<div id="logo"><img src="'.Yii::app()->request->baseUrl.'/images/footerlogo.png"/></div>');
             $mPDF1->WriteHTML($this->renderPartial('constanciapdf', array('cedula'=>$cedula,'tipo'=>$tipo), true));
             $mPDF1->Output('constancia_','I');
          
            
        }
         public function actionRecibopdf()
        {
            if($_GET['periodo_anual'] == 'rcm_2018')
                $anio= substr($_GET['periodo_anual'], 6);
            else                
               $anio= substr($_GET['periodo_anual'], 7);
             
             
             
             $codnom= $_GET['codnom'];
             $periodo= $_GET['periodo'];
            
             $cedula=$this->Obtener_cedula();
             $mPDF1 = Yii::app()->ePdf->mpdf();
             $mPDF1 = Yii::app()->ePdf->mpdf('', 'A5');
             $mPDF1->WriteHTML($this->renderPartial('recibopdf', array('cedula'=>$cedula,'anio'=>$anio,'periodo'=>$periodo,'codnom'=>$codnom), true));
             $mPDF1->Output('recibo_'.$periodo.".pdf",'I');
          
            
        }
          public function actionRecibohtml()
        {
            if($_GET['periodo_anual'] == 'rcm_2018')
                $anio= substr($_GET['periodo_anual'], 6);
            else                
               $anio= substr($_GET['periodo_anual'], 7);
            
            $codnom= $_GET['codnom'];
            
            $periodo= $_GET['periodo'];
                         
            $userid= Yii::app()->user->id;
            
            $cedula=$this->Obtener_cedula();
          
            $this->render('recibohtml',array(
			'cedula'=>$cedula,'anio'=>$anio,'periodo'=>$periodo,'codnom'=>$codnom
		));
        }
         public function actionArcpdf()
        {
            
             $anio= date('Y');
             $cedula=$this->Obtener_cedula();
             $mPDF1 = Yii::app()->ePdf->mpdf();
             $mPDF1 = Yii::app()->ePdf->mpdf('', 'A5');
             //$mPDF1->ignore_invalid_utf8 = true;
             $mPDF1->WriteHTML($this->renderPartial('arcpdf', array('cedula'=>$cedula,'anio'=>$anio), true));
             $mPDF1->Output('arc','I');
          
            
        }
         public function actionArcpdf2016()
        {
            
             $anio= date('Y');
             $cedula=$this->Obtener_cedula();
             $mPDF1 = Yii::app()->ePdf->mpdf();
             $mPDF1 = Yii::app()->ePdf->mpdf('', 'A5');
             //$mPDF1->ignore_invalid_utf8 = true;
             $mPDF1->WriteHTML($this->renderPartial('arcpdf2016', array('cedula'=>$cedula,'anio'=>$anio), true));
             $mPDF1->Output('arc','I');
          
            
        }

         public function actionArcpdf2017()
        {
            
             $anio= date('Y');
             $cedula=$this->Obtener_cedula();
             $mPDF1 = Yii::app()->ePdf->mpdf();
             $mPDF1 = Yii::app()->ePdf->mpdf('', 'A5');
             //$mPDF1->ignore_invalid_utf8 = true;
             $mPDF1->WriteHTML($this->renderPartial('arcpdf2017', array('cedula'=>$cedula,'anio'=>$anio), true));
             $mPDF1->Output('arc','I');
          
            
        }



          public function actionArcpdf2018()
        {
            
             $anio= date('Y');
             $cedula=$this->Obtener_cedula();
             $mPDF1 = Yii::app()->ePdf->mpdf();
             $mPDF1 = Yii::app()->ePdf->mpdf('', 'A5');
             //$mPDF1->ignore_invalid_utf8 = true;
             $mPDF1->WriteHTML($this->renderPartial('arcpdf2018', array('cedula'=>$cedula,'anio'=>$anio), true));
             $mPDF1->Output('arc','I');
          
            
        }
         public function obtener_nomina_alimentacion()
                 
        { 
           
          $cedula=$this->Obtener_cedula();
            $db_actual=$this->Obtener_db_actual();
            $ano=date('Y');

            $sentencia="select distinct a.codnom, a.desnom
     
       from 
                    public.sno_hsalida c, 
                    public.sno_hpersonalnomina b, 
                    public.sno_nomina a 

where 
                    b.codemp = c.codemp AND
                    b.codnom= c.codnom AND
                    b.codper = c.codper AND
                    b.anocur = c.anocur AND
                    b.codperi = c.codperi AND
                    a.codemp = b.codemp AND
                    a.codnom= b.codnom AND
                    c.valsal != 0 AND 
                    c.valsal IS NOT NULL  AND 
                    (c.tipsal in('A', 'D', 'A ', 'D ', 'V1', 'W1', 'V2', 'W2', 'P1', 'V3', 'W3')) AND 
                    b.codper like '%".$cedula."%' AND
                    b.codnom='0013' AND
                     b.anocur = '".$ano."'
   order by a.codnom, a.desnom";
             $con=Yii::app()->$db_actual->createCommand($sentencia);
             $datareader=$con->queryRow();
           
             return  $datareader;
            
        }
            //para reporte minci
         public function actionReporteminci()
        {
             //$mo=Perfil::model()->findAll();
            $contenido=$this->renderPartial('excel',array(),true);
             Yii::app()->request->sendFile("test.xls",$contenido);
             //$this->render('reporteminci');
        }
        //para reporte minci
         public function obtenerConceptos($codigoconcepto,$cedula,$nomina)
        {
             $sentencia="select c2.nomcon,hs.codconc, hs.valsal 
from sno_hsalida hs
inner join sno_conceptopersonal c
on c.codconc=hs.codconc
INNER JOIN sno_concepto c2 ON c2.codconc = hs.codconc 
where hs.codconc ='".$codigoconcepto."' and hs.anocur='2016' and hs.codnom='".$nomina."' and hs.codper like '%".$cedula."' and hs.codperi='021' and  hs.tipsal='A' and valsal!='0' and c.aplcon='1' and c.codnom='".$nomina."' and c.codper like '%".$cedula."' and c2.codnom='".$nomina."'";
$con=Yii::app()->db_tves2016->createCommand($sentencia);
$datareader=$con->queryRow();

return $datareader['valsal'];
        }
        
      
}