<?php

namespace app\controllers;

use Yii;
use yii\web\Controller;
use yii\web\NotFoundHttpException;
use yii\filters\VerbFilter;
use yii\filters\AccessControl;
use app\base\Converter;
use yii\data\ActiveDataProvider;
use yii\data\SqlDataProvider;
use app\models\Missumconsreport;

use app\models\Registration;
use app\models\Ostmaster;
use app\models\Labsamplerequest;
use app\models\Hivlinkage;
use app\models\Screening;
use app\models\Nursevisit;
use app\models\Counselor;
use app\models\Attandance;
use app\models\Ostvisit;
use app\models\Hcvlinkage;
use app\models\Tb;
use app\models\Sti;
/**
 * MissumconsreportController implements the CRUD actions for Ostvisitreport model.
 */
class MissumconsreportController extends Controller
{
    /**
     * @inheritdoc
     */
    public function behaviors()
    {
        return [
            'access' => [
                'class' => AccessControl::className(),
                'rules' => [
                    [
                        'actions' => ['error'],
                        'allow' => true,
                    ],
                    [
                        'actions' => ['index'],
                        'allow' => true,
                        'roles' => ['@'],
                    ],
                ],
            ],
            'verbs' => [
                'class' => VerbFilter::className(),
                'actions' => [
                    'delete' => ['POST'],
                ],
            ],
        ];
    }
	
	
 
		 
    /**
     * Lists all Arvmaster models.
     * @return mixed
     */
    public function actionIndex()	
    {			
		
        $model = new Missumconsreport();
		
		/*
		if ($model->load(Yii::$app->request->post()) || $model->load(Yii::$app->request->get())) {
            if (Yii::$app->request->isAjax) {
                Yii::$app->response->format = Response::FORMAT_JSON;
                return ActiveForm::validate($model);
            }
          */  
            if(!$model->validate()) {
                return $this->render('index', ['model' => $model]);
            } else {
				
			    $osttable = Ostmaster::tableName();
			    $regtable = Registration::tableName();
     			$labtable = Labsamplerequest::tableName();
				$hivlinktable=Hivlinkage::tableName();
				$scrtable=Screening::tableName();	
                $nursetable= Nursevisit::tableName();
                $Counstable= Counselor::tableName();
                $attntable = Attandance::tableName();
                $ostvisittable = Ostvisit::tableName();	
                $hcvlinktable=Hcvlinkage::tableName();		
                $tbtable=Tb::tableName();	
                $stitable=Sti::tableName();					
 			/*						
				$frmdate = "01-".$model->month;
                $todate = self::last_day_of_the_month($frmdate);
                $frmdate = Converter::toUnixTimeformat($frmdate);
                if(date('m-Y') == date('m-Y', $frmdate))
                    $todate = strtotime(date('Y/m/d'));
                else
                    $todate = Converter::toUnixTimeformat($todate);
		*/
		
	   $Rmodel = Registration::find()->orderBy(['iccplus_date' => SORT_ASC])->one();
		if($Rmodel !== null) {	
			 
			 if(!is_integer($Rmodel->iccplus_date))
					$regstartdate = Converter::toStore($Rmodel->iccplus_date);
				else
				  $regstartdate  = $Rmodel->iccplus_date;		
				  	
		} else {
			 $regstartdate = strtotime(date("Y/m/d")); 
		  }
		  
		       $todaydate = strtotime(date('Y/m/d'));			   
               $date2 = date_create(Converter::toDisplay($regstartdate));               
	           $date1 = date_create(Converter::toDisplay($todaydate));
	           $diff = date_diff($date1, $date2);
	           $dtdiffdays = $diff->format("%a")+1;	          			 
								
					
 $dataProvider = new SqlDataProvider([
           'sql' => 'SELECT "Client Information" As KeyIndicators, "# New Registrations" As misdesc,(select COUNT(corfrm_pid) from' .$regtable.' WHERE icc_date is null) as Noofclients '.
           'Union '.		   
		    'SELECT "Client Information" ,"Average # Registration per day" ,(select COUNT(corfrm_pid) from' .$regtable.' WHERE icc_date is null)/'.$dtdiffdays.	
           ' Union '.            
		   'SELECT  "Client Information" ,"# Total old ICC clients" ,(select COUNT(corfrm_pid) from' .$regtable.' WHERE icc_date is not null) '.
           ' Union '.            
		   'SELECT  "Client Information" ,"# Existing ICC clients registered to ICC+" ,(select COUNT(corfrm_pid) from' .$regtable.' WHERE icc_date is not null and update_user is not null) '.		   
		   ' Union '.  
           'SELECT  "Client Information" ,"# Unique clients who visited the ICC+ " ,(select COUNT(distinct pid) from' .$attntable.' ) '.	         
           'Union '.  
           'SELECT "Client Information" , "# Client visits to the ICC+" ,(select COUNT(pid) from' .$attntable.' ) '.
           'Union '. 		   
		    'SELECT "HIV Services", "# HIV Tests Done" ,(select COUNT(lab_smple_part_id) from' .$labtable.' WHERE lab_smple_tst_id=1 ) '.
		   ' Union '.           
		   'SELECT  "HIV Services","TOTAL # of HIV positive clients " ,(select COUNT(corfrm_pid) from' .$regtable.' WHERE corfrm_q12=1 or corfrm_q12 =9) '.	
		   'Union '.		   
		   'SELECT "HIV Services", "TOTAL # clients linked to ART center " ,(select COUNT(hiv_linkage_part_id) from' .$hivlinktable.' WHERE' .$hivlinktable.'.hiv_linkage_q2a=1) '.
		   'Union '.
		   'SELECT "HIV Services", "TOTAL # of clients on ART" ,(select COUNT(hiv_linkage_part_id) from' .$hivlinktable.' WHERE  hiv_linkage_q2a=1 and hiv_linkage_q5 != "" ) '.
		   'Union '.
		   'SELECT "Harm Reduction Report", "TOTAL # Registered for OST " ,(select COUNT(ost_master_part_id) from' .$osttable.') '.
		   'Union '.		   
		   'SELECT "Harm Reduction Report", "# clients with an OST visit " ,(select COUNT(distinct ost_visit_part_id) from' .$ostvisittable.') '.
		   'Union '.		    		   		   
		  'SELECT "Harm Reduction Report", "# clients receiving needles by Counselor" ,(select Count(counfrm_pid) from' .$Counstable.'  WHERE counfrm_q6_no_needle>0 ) '.
		  'Union '.
		  'SELECT "Harm Reduction Report", "# clients receiving needles by Nurse" ,(select Count(nursefrm_pid) from' .$nursetable.'  WHERE nursefrm_q3_no_needle >0 ) '.	
           'Union '.		 
		   'SELECT "Harm Reduction Report", "# clients receiving condoms by Counselor" ,(select Count(counfrm_pid) from' .$Counstable.'  WHERE counfrm_q7_no_condom>0 ) '.           
		   'Union '.
		   'SELECT "Harm Reduction Report", "# clients receiving condoms by Nurse" ,(select Count(nursefrm_pid)  from' .$nursetable.' WHERE nursefrm_q4_no_condom>0 ) '.            
		   'Union '.		    
		   'SELECT "Co-morbidity", "# HCV Tests Done" ,(select COUNT(lab_smple_part_id) from' .$labtable.' WHERE lab_smple_tst_id=3 ) '.
		   'Union '.
		   'SELECT  "Co-morbidity","TOTAL HCV Positive" ,(select COUNT(corfrm_pid) from' .$regtable.' WHERE corfrm_q14=1 or corfrm_q14 =9) '.	
		   'Union '.
		   'SELECT "Co-morbidity", "TOTAL # linked to HCV care" ,(select COUNT(hcv_linkage_part_id) from' .$hcvlinktable.') '.
		   'Union '.		   
		   'SELECT "Co-morbidity", "# TB Tests Done" ,(select COUNT(lab_smple_part_id) from' .$labtable.' WHERE lab_smple_tst_id=4 ) '.
		   'Union '.
		   'SELECT "Co-morbidity", "TOTAL # TB Positive" ,(select COUNT(lab_smple_part_id) from' .$labtable.' WHERE lab_smple_tst_id=4 and lab_smple_result=1 ) '.
		   'Union '.
		   'SELECT "Co-morbidity", "# Started TB Treatment" ,(select COUNT(tb_test_part_id) from' .$tbtable.' WHERE tb_test_q9=1 ) '.		   
		   'Union '.  
		   'SELECT "Co-morbidity", "# Syphilis Tests Done" ,(select COUNT(lab_smple_part_id) from' .$labtable.' WHERE lab_smple_tst_id=5 ) '.
		   'Union '.
		   'SELECT "Co-morbidity", "# STI Referrals" ,(select COUNT(sti_test_part_id) from' .$stitable.' WHERE sti_test_q4=1 ) '.		   
		   'Union '.
		   'SELECT  "Co-morbidity","# TOTAL Deaths " ,(select COUNT(corfrm_pid) from' .$regtable.' WHERE corfrm_q13=3) '.	
		   'Union '.		   
		   'SELECT  "Cohort", "Total Cohort Screened" ,(select COUNT(mem_scrn_part_id) from ' .$scrtable.' ) ' ,		  
     'params' => [],'pagination' => false
 ]);
 
       // $dataProvider = new ActiveDataProvider(['query' => $query,'pagination' => false]);   	   
		
		 return $this->render('index', ['model' => $model, 'dataProvider' => $dataProvider]);		
		}
	//}  
     return $this->render('index', ['model' => $model]);  
    }
	
	function last_day_of_the_month($date = '')
    {
        $month  = date('m', strtotime($date));
        $year   = date('Y', strtotime($date));
        $result = strtotime("{$year}-{$month}-01");
        $result = strtotime('-1 second', strtotime('+1 month', $result));
        return Converter::toDisplay($result);
    }
		
      
}
