<?php

use app\base\Common;
use app\models\Attandance;
use app\models\Registration;
use app\models\Incentivesmaster;
use yii\data\ActiveDataProvider;
use yii\data\ArrayDataProvider;
use yii\data\SqlDataProvider;
use yii\db\Query;
use app\models\VVisitstatus;
use app\models\Screening;
use app\models\Hivlinkage;


/* @var $this yii\web\View */

 /* <div class="col-md-6"><?= $this->render('attandance', ['dataProvider' => $dataProvider1]); ?></div>

  
$regtable = Registration::tableName();
$query = Attandance::find()->where("out_interviewer IS NULL AND visit_out IS NULL")->andWhere("pid IN (SELECT corfrm_pid FROM $regtable WHERE corfrm_q12 = 1)")->orderBy(["pid" => SORT_ASC]);
$dataProvider2 = new ActiveDataProvider(['query' => $query]);

$regtable = Registration::tableName();
$query = Attandance::find()->where("out_interviewer IS NULL AND visit_out IS NULL")->andWhere("pid IN (SELECT corfrm_pid FROM $regtable WHERE corfrm_q12 = 0)")->orderBy(["pid" => SORT_ASC]);
$dataProvider3 = new ActiveDataProvider(['query' => $query]);

<div class="col-md-12"><?= $this->render('ostvisitrep', ['dataProvider' => $dataProvider5]); ?></div>
    <div class="col-md-12"><?= $this->render('drugdispenserep', ['dataProvider' => $dataProvider6]); ?></div>


 */
 

$this->title = 'Dashboard\'s';

$today = strtotime(date("Y/m/d"));
// $query = Attandance::find()->with("registration")->where(["between", "visit_date", strtotime(date("Y/m/d")), date('U')]);
$query = Registration::find()->select('*, Count(corfrm_pid) As ClientCount')->where(["<>", "corfrm_q13", 0])->groupBy('corfrm_q12, corfrm_q13');
$dataProvider0 = new ActiveDataProvider(['query' => $query, 'sort' => false,'pagination' => ['pagesize' => 5]]);

//$query = Attandance::find()->with("registration")->where(["record_date" => $today])->andWhere(["IS", "out_interviewer", NULL]);
$query = Attandance::find()->with("registration")->where(["record_date" => $today]);
$dataProvider1 = new ActiveDataProvider(['query' => $query, 'sort' => false,'pagination' => ['pagesize' => 5]]);

$query = Attandance::find()->innerJoinWith("screening")->where(["visit_date" => $today])->andWhere(["mem_scrn_q28" => 1]);
$dataProvider11 = new ActiveDataProvider(['query' => $query, 'sort' => false,'pagination' => ['pagesize' => 5]]);

$attntable = Attandance::tableName();
$date = strtotime(date("Y/m/d"));
// $query = Registration::find()->where("corfrm_pid IN (SELECT pid FROM $attntable WHERE visit_date between $date AND ".date('U'). " And out_interviewer IS NULL AND visit_out IS NULL)")->orderBy(["corfrm_pid" => SORT_ASC]);
$query = Attandance::find()->innerJoinWith("registration")->where(["visit_date" => $today, "corfrm_q12" => 1])->orWhere(["visit_date" => $today, "corfrm_q12" => 9])->andWhere(["IS", "out_interviewer", NULL])->orderBy(["corfrm_pid" => SORT_ASC]);
$dataProvider2 = new ActiveDataProvider(['query' => $query, 'sort' => false,'pagination' => ['pagesize' => 5]]);


$regtable = Registration::tableName();
// $query = Attandance::find()->where("out_interviewer IS NULL AND visit_out IS NULL")->andWhere("pid IN (SELECT corfrm_pid FROM $regtable WHERE corfrm_q12 = 0)")->orderBy(["pid" => SORT_ASC]);
$query = Attandance::find()->innerJoinWith("registration")->where(["visit_date" => $today, "corfrm_q12" => 0])->andWhere(["IS", "out_interviewer", NULL]);
$dataProvider3 = new ActiveDataProvider(['query' => $query, 'sort' => false,'pagination' => ['pagesize' => 5]]);
$attntable = Attandance::tableName();
$query = Registration::find()->where("DATEDIFF(NOW(),FROM_UNIXTIME(iccplus_date))>70")->andWhere("corfrm_pid IN (SELECT pid FROM $attntable WHERE visit_date between $date AND ".date('U'). " And out_interviewer IS NULL AND visit_out IS NULL)")->orderBy(["corfrm_pid" => SORT_ASC]);
$dataProvider4 = new ActiveDataProvider(['query' => $query, 'sort' => false,'pagination' => ['pagesize' => 5]]);

$regtable = Registration::tableName();
// $query = Attandance::find()->where("out_interviewer IS NULL AND visit_out IS NULL")->andWhere("pid IN (SELECT corfrm_pid FROM $regtable WHERE corfrm_q12 = 0)")->orderBy(["pid" => SORT_ASC]);
$query = Attandance::find()->innerJoinWith("registration")->where(["visit_date" => $today, "corfrm_q12" => 2])->andWhere(["IS", "out_interviewer", NULL]);
$dataProvider5 = new ActiveDataProvider(['query' => $query, 'sort' => false,'pagination' => ['pagesize' => 5]]);

/*$dataProvider6 = new SqlDataProvider([
     'sql' => ' SELECT distinct (SELECT min(FROM_UNIXTIME(record_date)) FROM cms_screening) startdate,
(SELECT max(FROM_UNIXTIME(record_date)) FROM cms_screening) lastdate,
(SELECT Count(mem_scrn_part_id) FROM cms_screening) scrCnt,
               (SELECT Count(mem_scrn_part_id) FROM cms_screening where mem_scrn_q24=1) elgCnt,
               (SELECT Count(mem_scrn_part_id) FROM cms_screening where mem_scrn_q24=2) notelgCnt,
			    (SELECT Count(mem_scrn_part_id) FROM cms_screening where mem_scrn_q25=1) enrCnt,
				(SELECT Count(mem_scrn_part_id) FROM cms_screening where mem_scrn_q25=2 Or mem_scrn_q25 is null) notenrCnt '.
               ' FROM cms_screening ' ,		 
     'params' => [],
 ]);
 */
 
$query = Screening::find()->select('Count(mem_scrn_part_id)');
$dataProvider6 = new ActiveDataProvider(['query' => $query,'pagination' => false]);
 
	 
/* $dataProvider7 = new SqlDataProvider([
		 'sql' => ' SELECT distinct (SELECT Count(mem_scrn_part_id) FROM cms_screening where mem_scrn_q25=1) enrCnt,
					(SELECT Count(mem_scrn_part_id) FROM cms_screening where mem_scrn_q25=1 and mem_scrn_q19 !="") onartCnt,
					(SELECT Count(mem_scrn_part_id) FROM cms_screening where mem_scrn_q25=1 and mem_scrn_q16 !="" and (mem_scrn_q19 ="" or mem_scrn_q19 is null )) onpreartCnt,
					(SELECT Count(mem_scrn_part_id) FROM cms_screening where mem_scrn_q25=1 and ((mem_scrn_q16 ="" or mem_scrn_q16 is null) and (mem_scrn_q19 ="" or mem_scrn_q19 is null))) unlinkCnt '.
				   ' FROM cms_screening ' ,		 
		 'params' => [],
	 ]);
*/	 
		 
$query = VVisitstatus::find()->where("participant_id IN (SELECT pid FROM $attntable WHERE visit_date between $date AND ".date('U').")")->orderBy(["participant_id" => SORT_ASC,"visit" => SORT_ASC]);
$dataProvider8 = new ActiveDataProvider(['query' => $query, 'sort' => false, 'pagination' => ['pagesize' => 5]]); 
	 
 
/* $query = (new Query())
                ->select("cms_ostmaster.ost_master_part_id,cms_ostmaster.ost_master_q1,cms_ostvisit.ost_visit_q2,cms_ostvisit.ost_visit_q3,cms_ostvisit.ost_visit_q4,cms_ostvisit.ost_visit_q5")
                ->from('cms_ostmaster')
				->join('LEFT OUTER JOIN','cms_ostvisit','cms_ostvisit.ost_visit_part_id = cms_ostmaster.ost_master_part_id and cms_ostvisit.ost_visit_q1 = cms_ostmaster.ost_master_q1 and cms_ostvisit.record_date = '.$today)
		->Orderby('cms_ostmaster.ost_master_part_id');         
$dataProvider5 = new ActiveDataProvider(['query' => $query]);

$query = (new Query())
                ->select("sum(cms_ostvisit.ost_visit_q2) as sum_ost_visit_q2,sum(cms_ostvisit.ost_visit_q3) as sum_ost_visit_q3,sum(cms_ostvisit.ost_visit_q4) as sum_ost_visit_q4,sum(cms_ostvisit.ost_visit_q5) as sum_ost_visit_q5")
                ->from('cms_ostvisit')
				->where(["=", "record_date", $today]);
$dataProvider6 = new ActiveDataProvider(['query' => $query]);

*/

$dataProvider9 = Common::getIncentives();

$dataProvider10 = new SqlDataProvider([
 'sql' => 'SELECT Distinct "Currently on Followup" As ClientStatus,(SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=1 and corfrm_q12=1) PosCnt,
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=1 and corfrm_q12=2) IndCnt,
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=1 and corfrm_q12=3) UnkCnt,
			   (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=1 and corfrm_q12=9) PosstarCnt,
			   (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=1 and corfrm_q12=0) NegCnt,
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=1) TotCnt '.
               'FROM cms_registration '.
			   'Union '.
              ' SELECT Distinct "Migrated" ,(SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=2 and corfrm_q12=1),
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=2 and corfrm_q12=2),
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=2 and corfrm_q12=3),
			   (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=2 and corfrm_q12=9) ,
			   (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=2 and corfrm_q12=0),
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=2) '	.
               'FROM cms_registration '.
			   'Union '.
			   'SELECT Distinct "Dead" ,(SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=3 and corfrm_q12=1),
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=3 and corfrm_q12=2),
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=3 and corfrm_q12=3),
			   (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=3 and corfrm_q12=9) ,
			   (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=3 and corfrm_q12=0),
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=3) ' .
			   'FROM cms_registration '.
			   'Union '.
			   'SELECT Distinct "LTF" ,(SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=4 and corfrm_q12=1),
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=4 and corfrm_q12=2),
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=4 and corfrm_q12=3),
			   (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=4 and corfrm_q12=9),
			   (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=4 and corfrm_q12=0),
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q13=4) ' .
			   'FROM cms_registration '.			   		   
			   'Union '.
			   'SELECT Distinct "Total" ,(SELECT Count(corfrm_id) FROM cms_registration where corfrm_q12=1),
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q12=2),
               (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q12=3),
			   (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q12=9),
			   (SELECT Count(corfrm_id) FROM cms_registration where corfrm_q12=0),
               (SELECT Count(corfrm_id) FROM cms_registration ) TotCnt '.
			   'FROM cms_registration ' ,		 
     'params' => [],
 ]);
 
 $scrtable = Screening::tableName();
 $query = Registration::find()->where('corfrm_q12 = 1')->orWhere('corfrm_q12 = 9')->andWhere("corfrm_pid Not in (Select mem_scrn_part_id from $scrtable)")->orderBy(["corfrm_pid" => SORT_ASC]);
 $dataProvider12 = new ActiveDataProvider(['query' => $query, 'pagination' => ['pagesize' => 5]]);
 
 $query = Hivlinkage::find()->select('*, Count(hiv_linkage_part_id) As ClientCount')->where("hiv_linkage_part_id in (Select mem_scrn_part_id from $scrtable where mem_scrn_q25=1)"); 
 $dataProvider13 = new ActiveDataProvider(['query' => $query, 'pagination' => false]);
 

// $provider = new ArrayDataProvider([
//     'allModels' => $services
// ]);
/* 
   <div class="col-md-12"><?= $this->render('incentives', ['dataProvider' => $dataProvider11]); ?></div>
   <div class="col-md-12"><?= $this->render('allincentives', ['dataProvider' => $dataProvider9]); ?></div>
   <div class="col-md-12"><?= $this->render('allincentivesfortoday', ['dataProvider' => $dataProvider1]); ?></div> 
 */
?>

<div class="row">
    <div class="col-md-12"><?= $this->render('attandance', ['dataProvider' => $dataProvider1]); ?></div>
    <div class="col-md-12"><?= $this->render('allincentivesfortoday', ['dataProvider' => $dataProvider11]); ?></div>
    <div class="col-md-12"><?= $this->render('clientsummarynew', ['dataProvider' => $dataProvider10]); ?></div>
    <div class="col-md-12"><?= $this->render('cohortstatus', ['dataProvider' => $dataProvider6]); ?></div>
    <div class="col-md-12"><?= $this->render('enrollstatus', ['dataProvider' => $dataProvider6]); ?></div>
    <div class="col-md-12"><?= $this->render('currenthivlinkstatus', ['dataProvider' => $dataProvider13]); ?></div>	    
    <div class="col-md-12"><?= $this->render('notyetscreenedstatus', ['dataProvider' => $dataProvider12]); ?></div>
    <div class="col-md-12"><?= $this->render('hivpos', ['dataProvider' => $dataProvider2]); ?></div>
    <div class="col-md-12"><?= $this->render('hivneg', ['dataProvider' => $dataProvider3]); ?></div>
    <div class="col-md-12"><?= $this->render('hivind', ['dataProvider' => $dataProvider5]); ?></div>
    <div class="col-md-12"><?= $this->render('cohortformstatus', ['dataProvider' => $dataProvider8]); ?></div>          
    <div class="col-md-12"><?= $this->render('moblienoupdate', ['dataProvider' => $dataProvider4]); ?></div>
  

    
</div>