/**
  * Schedule of user
  * @return mixed
  */
 public function actionMyschedule()
 {
     $connection = Yii::$app->db;
     if (Yii::$app->request->post()) {
         $value = \Yii::$app->request->post();
         $value['congress'] = (int) $value['congress'];
         $value['userid'] = (int) $value['userid'];
         if ($value['userid']) {
             $sdaquery = "SELECT sda.*\n                            FROM  sda sda inner join `sda_schedules` s on sda.id=s.sda_id\n                            INNER JOIN user u ON u.attendee_id = s.attendee_id\n                            WHERE u.id ={$value['userid']} AND sda.congress_id = {$value['congress']} order by sda.date asc,sda.session_start_time asc";
             $boothquery = "SELECT ba.id,s.booth_id,ba.booth_name,ba.date,ba.from_time,ba.to_time,ba.Area,ba.congress_id,ba.location,s. * \n                            FROM  booths ba inner join `booth_schedules` s on ba.id=s.booth_id\n                            INNER JOIN user u ON u.attendee_id = s.attendee_id\n                            WHERE u.id ={$value['userid']} AND ba.congress_id = {$value['congress']} order by ba.date asc,ba.from_time asc";
             $meetingquery = "SELECT md.title,md.id,md.date,md.congress_id,md.start_time,md.stop_time,md.location,md.event_type\n                            FROM meeting_details md\n                            INNER JOIN meeting_schedules ms ON md.id = ms.meeting_id\n                            INNER JOIN meeting_types mt ON md.event_type = mt.id\n                            INNER JOIN user u ON u.attendee_id = ms.attendee_id\n                            WHERE u.id ={$value['userid']} AND md.congress_id = {$value['congress']} order by md.date asc,md.start_time asc";
             $modelsda = $connection->createCommand($sdaquery)->queryAll();
             $modelbooth = $connection->createCommand($boothquery)->queryAll();
             $meeting = $connection->createCommand($meetingquery)->queryAll();
             $finaldata = array();
             foreach ($modelsda as $value) {
                 $modelsda = $connection->createCommand("SELECT DISTINCT ms.attendee_id,a.Name as Name, a.title as title, wc.function_value AS Funame, wc.costcenter_value AS Cost_Center, a.phone as phone, a.speciality as speciality,'sdaattendee' as type\n                                                            FROM  sda_schedules ms\n                                                            INNER JOIN amgen_contact_list a ON ms.attendee_id = a.id\n                                                            LEFT JOIN workforce_contacts wc ON wc.id = a.workforce_id\n                                                            WHERE ms.sda_id = {$value['id']}\n                                                            ");
                 $attendee = $modelsda->queryAll();
                 $finaldata[$value['congress_id']][$value['date']][] = array('id' => $value['id'], 'title' => $value['title'], 'date' => $value['date'], 'start_time' => date("g:i a", strtotime($value['session_start_time'])), 'end_time' => date("g:i a", strtotime($value['session_end_time'])), 'location' => $value['location'], 'type' => 'SDA', 'abstract_id' => $value['abstract_id'], 'priority' => SdaPriority::findOne($value['priority'])->priority_name, 'abstract' => $value['abstract'], 'session_start_time' => date("g:i a", strtotime($value['session_start_time'])), 'session_end_time' => date("g:i a", strtotime($value['session_end_time'])), 'presentation_start_time' => date("g:i a", strtotime($value['presentation_start_time'])), 'presentation_end_time' => date("g:i a", strtotime($value['presentation_start_time'])), 'presenter' => $value['presenter'], 'sda_type' => \backend\models\SdaTypes::findOne($value['sda_type_id'])->sda_name, 'location' => $value['location'], 'session_id' => $value['session_id'], 'session' => $value['session'], 'authors' => $value['authors'], 'company' => \backend\models\CompanyDetails::findOne($value['company_id'])->name, 'team' => $value['team'], 'linktoposter' => $value['link_to_poster'], 'posterboadnno' => $value['poster_board_no'], 'presentation_no' => $value['presentation_no'], 'attendee' => $attendee);
             }
             foreach ($modelbooth as $value) {
                 $modelbooth = $connection->createCommand("SELECT DISTINCT ms.attendee_id,a.Name as Name, a.title as title, wc.function_value AS Funame, wc.costcenter_value AS Cost_Center, a.phone as phone, a.speciality as speciality,'sdaattendee' as type\n                                                                FROM  booth_schedules ms\n                                                                INNER JOIN amgen_contact_list a ON ms.attendee_id = a.id\n                                                                LEFT JOIN workforce_contacts wc ON wc.id = a.workforce_id\n                                                                WHERE ms.booth_id = {$value['id']}\n                                                                ");
                 $attendeebooth = $modelbooth->queryAll();
                 $finaldata[$value['congress_id']][$value['date']][] = array('id' => $value['booth_id'], 'congress' => CongressDetails::findOne($value['congress_id'])->name, 'title' => $value['booth_name'], 'date' => $value['date'], 'time' => date("g:i a", strtotime($value['from_time'])), 'start_time' => date("g:i a", strtotime($value['from_time'])), 'end_time' => date("g:i a", strtotime($value['to_time'])), 'location' => $value['location'], 'area' => $value['Area'], 'type' => 'BOOTH', 'attendee' => $attendeebooth);
             }
             foreach ($meeting as $value) {
                 $sql = "SELECT DISTINCT ms.attendee_id,a.Name as Name, a.title as title, wc.function_value AS Funame, wc.costcenter_value AS Cost_Center, a.phone as phone, a.speciality as speciality,'    sdaattendee' as type\n                            FROM  meeting_schedules ms\n                            INNER JOIN amgen_contact_list a ON ms.attendee_id = a.id\n                            LEFT JOIN workforce_contacts wc ON wc.id = a.workforce_id\n                            WHERE ms.meeting_id ={$value['id']}";
                 $modelolattendee = $connection->createCommand("SELECT ola.user_type_id,ola.first_name,ola.last_name,ola.name,ola.ol_bio,ola.ol_phone_no,ola.ol_email FROM `meeting_schedules` msd INNER JOIN ol_attendees ola ON msd.ol_attendee_id = ola.id  WHERE msd.meeting_id = {$value['id']}")->queryAll();
                 $attendee = $connection->createCommand($sql)->queryAll();
                 $finaldata[$value['congress_id']][$value['date']][] = array('meetingid' => $value['id'], 'title' => $value['title'], 'date' => $value['date'], 'start_time' => date("g:i a", strtotime($value['start_time'])), 'end_time' => date("g:i a", strtotime($value['stop_time'])), 'location' => $value['location'], 'eventtype' => \backend\models\MeetingTypes::findOne($value['event_type'])->type, 'type' => 'MEETING', 'attendee' => $attendee, 'olattendee' => $modelolattendee);
             }
             return $finaldata;
         }
     }
 }
?>

                </div></div>

            <div class="col-xs-6 col-sm-4 p5">
                <div class="ml20">
<?php 
echo $form->field($model, 'session_id', array('labelOptions' => array('class' => 'f18')))->textInput(['class' => 'form-control intp']);
?>
</div></div>


            <div class="col-xs-6 col-sm-4 p5">
                <div class="ml20">
                    <?php 
echo $form->field($model, 'sda_type_id', array('labelOptions' => array('class' => 'f18', 'label' => 'SDA Type')))->dropDownList(ArrayHelper::map(SdaTypes::find()->orderBy(['sda_name' => SORT_ASC])->asArray()->all(), 'id', 'sda_name'), ['class' => 'form-control intp', 'selection' => $model->sda_type_id]);
?>
                </div>
            </div>

            <div class="col-xs-6 col-sm-4 p5">
                <div class="ml20">
                    <?php 
echo $form->field($model, 'product', array('labelOptions' => array('class' => 'f18', 'label' => 'Product')))->dropDownList(ArrayHelper::map(Products::find()->orderBy(['name' => SORT_ASC])->asArray()->all(), 'id', 'name'), ['class' => 'form-control intp', 'selection' => $model->product]);
?>
                </div>
            </div>

            <div class="col-xs-6 col-sm-4 p5">
                <div class="ml20">
<?php 
 /**
  * @return \yii\db\ActiveQuery
  */
 public function getSdaType()
 {
     return $this->hasOne(SdaTypes::className(), ['id' => 'sda_type_id']);
 }
 /**
  * Advanced search in iPAd
  * @return mixed
  */
 public function actionSdasearch()
 {
     $connection = Yii::$app->db;
     if (Yii::$app->request->post()) {
         $value = \Yii::$app->request->post();
         $formData['title'] = $value['title'];
         $formData['date'] = $value['date'];
         $formData['authors'] = $value['authors'];
         $formData['session_id'] = $value['session_id'];
         $formData['poster_board_no'] = $value['poster_board_no'];
         $formData['session'] = $value['session'];
         $formData['abstract_id'] = $value['abstract_id'];
         $formData['abstract'] = $value['abstract'];
         $formData['presenter'] = $value['presenter'];
         $formData['sda_type'] = $value['sda_type'];
         $formData['presentation_no'] = $value['presentation_no'];
         $formData['team'] = $value['team'];
         $formData['congress_id'] = $value['congress'];
         $formData['product'] = $value['product'];
         //User id can be used for searching for data on the basis of user's id
         $formData['user_id'] = (int) isset($value['user_id']) ? $value['user_id'] : false;
         $finalvalue = array();
         $sql = "SELECT sda.* FROM sda ";
         if ($formData['user_id'] > 0) {
             $user = User::findOne(['id' => $formData['user_id']]);
             if ($user) {
                 $sql .= " INNER JOIN sda_schedules on sda_schedules.sda_id = sda.id and sda_schedules.attendee_id = " . $user->attendee_id;
             } else {
                 echo "Incorrect method";
             }
         }
         $sql .= " INNER JOIN products on products.id = sda.product";
         $sql .= " WHERE 1 ";
         $sql .= " AND sda.is_active = 1 ";
         $sql .= isset($formData['title']) && !empty($formData['title']) ? " AND sda.title LIKE '%" . $formData['title'] . "%'" : '';
         $sql .= isset($formData['date']) && !empty($formData['date']) ? " AND sda.date='" . $formData['date'] . "'" : '';
         $sql .= isset($formData['authors']) && !empty($formData['authors']) ? " AND sda.authors LIKE '%" . $formData['authors'] . "%'" : '';
         $sql .= isset($formData['session_id']) && !empty($formData['session_id']) ? " AND sda.session_id='" . $formData['session_id'] . "'" : '';
         $sql .= isset($formData['poster_board_no']) && !empty($formData['poster_board_no']) ? " AND sda.poster_board_no='" . $formData['poster_board_no'] . "'" : '';
         $sql .= isset($formData['session']) && !empty($formData['session']) ? " AND sda.session LIKE '%" . $formData['session'] . "%'" : '';
         $sql .= isset($formData['abstract_id']) && !empty($formData['abstract_id']) ? " AND sda.abstract_id='" . $formData['abstract_id'] . "'" : '';
         $sql .= isset($formData['abstract']) && !empty($formData['abstract']) ? " AND sda.abstract LIKE '%" . $formData['abstract'] . "%'" : '';
         $sql .= isset($formData['presenter']) && !empty($formData['presenter']) ? " AND sda.presenter LIKE '%" . $formData['presenter'] . "%'" : '';
         if ($sdaTypeRow = \backend\models\SdaTypes::find()->where(['sda_name' => $formData['sda_type']])->one()) {
             $sql .= isset($formData['sda_type']) && !empty($formData['sda_type']) ? " AND sda_type_id='" . $sdaTypeRow->id . "'" : '';
         }
         $sql .= isset($formData['product']) && !empty($formData['product']) ? " AND products.name='" . $formData['product'] . "'" : '';
         $sql .= isset($formData['presentation_no']) && !empty($formData['presentation_no']) ? " AND sda.presentation_no='" . $formData['presentation_no'] . "'" : '';
         $sql .= isset($formData['team']) && !empty($formData['team']) ? " AND sda.team LIKE '%" . $formData['team'] . "%'" : '';
         $sql .= isset($formData['congress_id']) && !empty($formData['congress_id']) ? " AND sda.congress_id='" . $formData['congress_id'] . "'" : '';
         $sql .= " order by date asc,session_start_time asc";
         $model = Sda::findBySql($sql)->all();
         if (!empty($model)) {
             foreach ($model as $key => $value) {
                 $modelsda = $connection->createCommand("SELECT DISTINCT ms.attendee_id,a.Name as Name, a.title as title, f.Name AS Funame, c.Group_name AS Cost_Center, a.phone as phone, a.speciality as speciality,'sdaattendee' as type\n                                                            FROM  sda_schedules ms\n                                                            INNER JOIN amgen_contact_list a ON ms.attendee_id = a.id\n                                                            LEFT JOIN functions f ON f.ID = a.function_id LEFT JOIN cost_center c ON c.id = a.group_id\n                                                            WHERE ms.sda_id = {$value->id}");
                 $attendee = $modelsda->queryAll();
                 $finalvalue[] = array('id' => $value['id'], 'congress' => CongressDetails::findOne($value['congress_id'])->name, 'product' => $value['product'] ? Products::findOne($value['product'])->name : null, 'title' => $value['title'], 'abstract_id' => $value['abstract_id'], 'priority' => SdaPriority::findOne($value['priority'])->priority_name, 'abstract' => $value['abstract'], 'date' => $value['date'], 'start_time' => date("g:i a", strtotime($value['session_start_time'])), 'end_time' => date("g:i a", strtotime($value['session_end_time'])), 'session_start_time' => !empty($value['session_start_time']) ? date("g:i a", strtotime($value['session_start_time'])) : "", 'session_end_time' => !empty($value['session_end_time']) ? date("g:i a", strtotime($value['session_end_time'])) : "", 'presentation_start_time' => !empty($value['presentation_start_time']) ? date("g:i a", strtotime($value['presentation_start_time'])) : "", 'presentation_end_time' => !empty($value['presentation_end_time']) ? date("g:i a", strtotime($value['presentation_end_time'])) : "", 'presenter' => $value['presenter'], 'sda_type' => \backend\models\SdaTypes::findOne($value['sda_type_id'])->sda_name, 'location' => $value['location'], 'session_id' => $value['session_id'], 'session' => $value['session'], 'authors' => $value['authors'], 'company' => \backend\models\CompanyDetails::findOne($value['company_id'])->name, 'team' => $value['team'], 'linktoposter' => $value['link_to_poster'], 'posterboadnno' => $value['poster_board_no'], 'presentation_no' => $value['presentation_no'], 'attendee' => $attendee, 'type' => 'SDA', 'category' => $value['category']);
             }
         }
         return $finalvalue;
     } else {
         echo "Incorrect method";
     }
 }
 /**
  * Import SDA in CSV format
  * @return mixed
  */
 public function actionExcel1()
 {
     $model = new Sda();
     if ($model->load(Yii::$app->request->post())) {
         $row = 1;
         $congid = $_POST['Sda']['id'];
         $model->fileName = UploadedFile::getInstance($model, 'fileName');
         $time = time();
         $model->fileName->saveAs('csv/' . $time . '.' . $model->fileName->extension);
         $model->fileName = 'csv/' . $time . '.' . $model->fileName->extension;
         $handle = fopen($model->fileName, "r");
         while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
             $row++;
             if ($row == 2) {
                 print_r($row);
             } else {
                 die;
                 // Getting Priority  ID
                 $priority_name = SdaPriority::find()->where(['priority_name' => $data[2]])->one();
                 $priority_name = $priority_name['id'];
                 // Getting Sda Type  ID
                 $sdatype = SdaTypes::find()->where(['sda_name' => $data[11]])->one();
                 $sda_type = $sdatype['id'];
                 // Getting companydetails Type  ID
                 $cmp = CompanyDetails::find()->where(['name' => $data[15]])->one();
                 $cmp = $cmp['id'];
                 $sql = "insert into sda(congress_id,title,abstract_id,priority,abstract,date,session_start_time,session_end_time,presentation_start_time,presentation_end_time,presenter,sda_type_id,location,session_id,authors,company_id,team,link_to_poster,poster_board_no,product) values ('{$congid}','{$data['0']}','{$data['1']}','{$priority_name}','{$data['3']}','{$data['5']}','{$data['6']}','{$data['7']}','{$data['8']}','{$data['9']}','{$data['10']}',{$sda_type},'{$data['12']}','{$data['13']}','{$data['14']}','2','{$data['16']}','{$data['17']}','{$data['18']}','{$data['19']}')";
                 $query = Yii::$app->db->createCommand($sql)->execute();
             }
         }
         fclose($handle);
         return $this->redirect(Url::home());
     }
     return $this->render('excel', ['model' => $model]);
 }