<?php

namespace app\controllers;

use Yii;
use yii\web\Response;
use yii\web\Controller;
use yii\web\NotFoundHttpException;
use yii\widgets\ActiveForm;
use yii\filters\VerbFilter;
use yii\filters\AccessControl;
use yii\data\ArrayDataProvider;
use app\base\Converter;

use app\models\Ostmaster;
use app\models\Ostvisitnew;
use app\models\Linkidlinkage;
use app\models\Ostvisitsumreport;
use app\models\Attandance;
use app\models\Contact;
use app\models\Registration;


/**
 * OstvisitsumreportController implements the CRUD actions for Patientcrosstabreport model.
 */
class OstvisitsumreportController 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 models.
     * @return mixed
     */
    public function actionIndex()
    {
        $model = new Ostvisitsumreport();

       if ($model->load(Yii::$app->request->post()) || $model->load(Yii::$app->request->get())) {
            
            if(!$model->validate()) {
                return $this->render('index', ['model' => $model]);
            } else {
                $CModel = new Ostvisitnew;	
				
              $ostmastertable = Ostmaster::tableName();      
              $ostvisittable = Ostvisitnew::tableName(); 
	          $Linkidtable = Linkidlinkage::tableName(); 
              $Contacttable = Contact::tableName(); 
              $Regtable = Registration::tableName();     			  
              
			  $asondate=date('Y-m-d',Converter::toUnixTimeformat($model->repdate));
			  $model->repdate = Converter::toUnixTimeformat($model->repdate);
    		    
             //  sum(distinct ost_visit_q5)*count(distinct ost_visit_id)	
              	 			  
				 $qry = "SELECT Distinct $ostmastertable.ost_master_loc,$ostmastertable.ost_master_part_id,$ostmastertable.ost_master_q1,$Linkidtable.link_idlinkage_q1,v_ost_master_date.ostdate,max($ostmastertable.ost_master_q2),ostenddate,count(distinct ost_visit_id) as Daysmedtaken,DATEDIFF('".$asondate."',(cast((from_unixtime(v_ost_master_date.ostdate) - interval 1 day) as date))) as Totdays,CONCAT(Round(((count(distinct ost_visit_id))/(DATEDIFF('".$asondate."',(cast((from_unixtime(v_ost_master_date.ostdate) - interval 1 day) as date)))))*100),'%') as Perc,(select sum(ost_visit_q5) from $ostvisittable where $ostvisittable.ost_visit_part_id = $ostmastertable.ost_master_part_id and $ostvisittable.ost_visit_q1 = $ostmastertable.ost_master_q1 and $ostvisittable.record_date <= '".$model->repdate."') ,contact_q3,corfrm_q2
                 FROM $ostmastertable
                    LEFT JOIN $ostvisittable ON ($ostvisittable.ost_visit_part_id = $ostmastertable.ost_master_part_id and $ostvisittable.ost_visit_q1 = $ostmastertable.ost_master_q1 and $ostvisittable.record_date <= '".$model->repdate."')
					LEFT JOIN  $Linkidtable ON ($Linkidtable.link_idlinkage_part_id	 = $ostmastertable.ost_master_part_id )	
					LEFT JOIN  $Contacttable ON ($Contacttable.contact_part_id = $ostmastertable.ost_master_part_id )	
					LEFT JOIN  $Regtable ON ($Regtable.corfrm_pid = $ostmastertable.ost_master_part_id )
  					LEFT JOIN  v_ost_master_date ON (v_ost_master_date.ost_master_part_id = $ostmastertable.ost_master_part_id)
                    LEFT JOIN  v_ost_master_enddate ON (v_ost_master_enddate.ost_master_part_id = $ostmastertable.ost_master_part_id)	
                 Where $ostmastertable.record_date <= '".$model->repdate."'				
                 Group by ost_master_part_id ORDER BY Round(((count(distinct ost_visit_id))/(DATEDIFF('".$asondate."',(cast((from_unixtime(v_ost_master_date.ostdate) - interval 1 day )as date)))))*100) desc,$ostmastertable.ost_master_part_id  ";
				
				
				//DATEDIFF((cast(from_unixtime($ostmastertable.record_date) as date))-".$asondate.") as Totdays,
				//WHERE $ostmastertable.ost_master_q3 is null
				
                $QModel = Yii::$app->db->createCommand($qry)->queryAll();              
                $nmodel = [];
				$services=[];
                foreach ($QModel as $key => $value) {
                    foreach ($services as $skey => $svalue) {
                        $nservices[$svalue] = '-';
                    }                   
					
                    $xservices = [];
                    $index = 0;
                    foreach ($value as $key1 => $value1) {
                        $xservices[] = $value1;                        
                    }
                    
                    $nmodel[] = $xservices;
                   
                }

                $dataProvider = new ArrayDataProvider([
                    'allModels' => $nmodel,
					'pagination' => false
                    
                ]);
                
           //'pagination' => ['pagesize' => 20]
			
             $model->repdate= Converter::toDisplay($model->repdate);			   
               
                return $this->render('index', ['model' => $model, 'dataProvider' => $dataProvider]);
            }
        }
        return $this->render('index', ['model' => $model]);
    }

}
