<?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\Counselor;
use app\models\Registration;
use app\models\Counselorreport;
use app\models\Ostmaster;
use app\models\Linkidlinkage;

/**
 * CounselorreportController implements the CRUD actions for Patientcrosstabreport model.
 */
class CounselorreportController 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 Counselorreport();

        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 Counselor;
                $services = array_merge($CModel->q1, $CModel->q2, $CModel->q3);
               
                // $nservices = [];
                // foreach ($services as $key => $value) {
                //     $nservices[$value] = '-';
                // }
                // print_r($services);
                $cnstable = Counselor::tableName();
                $regtable = Registration::tableName();
				$osttable=Ostmaster::tableName();
				$linkidtable = Linkidlinkage::tableName();
                
                //$model->frmdate = Converter::toStore($model->frmdate);
               //$model->todate = Converter::toStore($model->todate);
				
				$model->frmdate = Converter::toUnixTimeformat($model->frmdate);
                $model->todate = Converter::toUnixTimeformat($model->todate);				

                /*
				$qry = "SELECT Distinct counfrm_pid, counfrm_q1, counfrm_q2, counfrm_q3, $regtable.record_date AS reg_date, $cnstable.record_date AS date,ost_master_q1,link_idlinkage_q1,
                    CASE WHEN $regtable.corfrm_q5 = 1 THEN 'Male' WHEN $regtable.corfrm_q5 = 2 THEN 'Female' WHEN $regtable.corfrm_q5 = 3 THEN 'Hijrah' END AS Gender, corfrm_q4                    
                FROM $cnstable
                    INNER JOIN $regtable ON ($regtable.corfrm_pid = $cnstable.counfrm_pid)
					LEFT JOIN $osttable ON ($regtable.corfrm_pid = $osttable.ost_master_part_id AND $osttable.ost_master_q3 is null)
					LEFT JOIN $linkidtable ON ($linkidtable.link_idlinkage_part_id = $regtable.corfrm_pid)
                WHERE $cnstable.record_date BETWEEN '".$model->frmdate."' AND '".$model->todate."' ORDER BY $cnstable.record_date";
				*/
				
				$qry = "SELECT Distinct counfrm_pid, $regtable.record_date AS reg_date, $cnstable.record_date AS date,ost_master_q1,link_idlinkage_q1,
                    CASE WHEN $regtable.corfrm_q5 = 1 THEN 'Male' WHEN $regtable.corfrm_q5 = 2 THEN 'Female' WHEN $regtable.corfrm_q5 = 3 THEN 'Hijrah' END AS Gender, corfrm_q4,IF(FIND_IN_SET(1,counfrm_q1)>0,1,0) as counfrm_q1a,IF(FIND_IN_SET(2,counfrm_q1)>0,1,0) as counfrm_q1b,IF(FIND_IN_SET(1,counfrm_q2)>0,1,0) as counfrm_q2a,IF(FIND_IN_SET(2,counfrm_q2)>0,1,0) as counfrm_q2b,IF(FIND_IN_SET(3,counfrm_q2)>0,1,0) as counfrm_q2c,IF(FIND_IN_SET(1,counfrm_q3)>0,1,0) as counfrm_q3a,IF(FIND_IN_SET(2,counfrm_q3)>0,1,0) as counfrm_q3b,IF(FIND_IN_SET(3,counfrm_q3)>0,1,0) as counfrm_q3c,IF(FIND_IN_SET(4,counfrm_q3)>0,1,0) as counfrm_q3d,IF(FIND_IN_SET(5,counfrm_q3)>0,1,0) as counfrm_q3e,IF(FIND_IN_SET(6,counfrm_q3)>0,1,0) as counfrm_q3f,
					$cnstable.status,$cnstable.create_time,$cnstable.create_user,$cnstable.update_time,$cnstable.update_user,$cnstable.record_date                    
                FROM $cnstable
                    INNER JOIN $regtable ON ($regtable.corfrm_pid = $cnstable.counfrm_pid)
					LEFT JOIN $osttable ON ($regtable.corfrm_pid = $osttable.ost_master_part_id AND $osttable.ost_master_q3 is null)
					LEFT JOIN $linkidtable ON ($linkidtable.link_idlinkage_part_id = $regtable.corfrm_pid)
                WHERE $cnstable.record_date BETWEEN '".$model->frmdate."' AND '".$model->todate."' ORDER BY $cnstable.record_date";
				
               $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' => ['pagesize' => 10]
                ]);
                
                $model->frmdate = Converter::toDisplay($model->frmdate);
                $model->todate = Converter::toDisplay($model->todate);
               
                return $this->render('index', ['model' => $model, 'dataProvider' => $dataProvider]);
            }
        }
        return $this->render('index', ['model' => $model]);
    }
}
