<?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','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'];
            
            
            $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){
               echo CHtml::tag('option',array('value'=>$nom['codnom']),CHtml::encode($nom['desnom']), true );
               
            }
            
        }
        public function actionSelecttres()
                 
        {   
            $cedula=$this->Obtener_cedula();
            $ano =  $_GET['periodo'];
            $periodo_anual= substr($ano, 7);
            $codnom =  $_GET['tipo'];
            

 //$sentencia="select distinct on (codperi) codperi from sno_hsalida where codper like '%".$cedula."' and
 //codnom = '".$codnom."' and anocur='".$periodo_anual."'";
 
 $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'";
 
 
  $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."' ";
  $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;  
    echo CHtml::tag('option',array('value'=>$row['codperi']),CHtml::encode($row['codperi']. " fecha: ".$desde." / ".$hasta), 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');   
                 
            // }
             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='2016';
             
              ///////////////
             $db_actual=$this->Obtener_db_actual();
             $select="SELECT  
                        pn.codper, 
                        pn.obsrecper as cargo_observacion,
                        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,
                        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_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
                        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 actionConstanciapdf()
        {
             $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()
        {
             $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."_".$ano,'I');
          
            
        }
          public function actionRecibohtml()
        {
              $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 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='013' 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'];
        }
        
      
}