/**
* Uploads ol engagement data in CSV format
* @return mixed
*/
public function actionOlupload()
{
$model = new Olform();
$congress = Yii::$app->getRequest()->getQueryParam('congress');
if ($model->load(Yii::$app->request->post())) {
$row = 1;
$model->csvfile = UploadedFile::getInstance($model, 'csvfile');
$time = time();
$model->csvfile->saveAs('csv/' . $time . '.' . $model->csvfile->extension);
$model->csvfile = 'csv/' . $time . '.' . $model->csvfile->extension;
$requiredHeaders = array('Title', 'Meeting Type', 'Date', 'Start Time', 'End Time', 'Location', 'Amgen attendees', 'OL attendees', 'Commercial/Medical/Joint', 'Joint Meeting Approval Received', 'Restricted State Rules', 'Consultant', 'Catering', 'Notes');
$f = fopen($model->csvfile, 'r');
$firstLine = fgets($f);
//get first line of csv file
fclose($f);
$foundHeaders = array_map('trim', str_getcsv(trim($firstLine), ',', '"'));
//parse to array
$handle = fopen($model->csvfile, "r");
if ($foundHeaders == $requiredHeaders) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$approval = 0;
$rsr = 0;
$consultant = 0;
$row++;
$eventtype = \backend\models\MeetingTypes::find()->where(['type' => $data[1]])->one();
$eventtype = isset($eventtype['id']) ? $eventtype['id'] : '4';
$profile = OlProfiles::find()->where(['name' => $data[8]])->one();
$profiletype = isset($profile['id']) ? $profile['id'] : '1';
if ($data[9] == "Yes") {
$approval = 1;
}
if ($data[10] == "Yes") {
$rsr = 1;
}
if ($data[11] == "Yes") {
$consultant = 1;
}
if ($row == 2) {
//ignoring
} else {
//Save the row
$meetingdetails = new \backend\models\MeetingDetails();
$meetingdetails->congress_id = $congress;
$meetingdetails->title = $data[0];
$meetingdetails->date = date("Y-m-d H:i:s", strtotime($data[2]));
$meetingdetails->start_time = $data[3];
$meetingdetails->stop_time = $data[4];
$meetingdetails->location = $data[5];
$meetingdetails->event_type = $eventtype;
if ($eventtype == "3") {
$meetingdetails->ol_profile_id = $profiletype;
$meetingdetails->rsr = $rsr;
$meetingdetails->meeting_approval = $approval;
$meetingdetails->ol_consultant = $consultant;
$meetingdetails->ol_catering = $data[12];
$meetingdetails->notes = $data[13];
}
try {
$meetingdetails->save();
$amgenatten = explode(',', $data[6]);
$olattendee = explode(',', $data[7]);
if (count($amgenatten) > 0) {
foreach ($amgenatten as $value) {
$value = trim($value);
$amgenattendee = explode(' ', $value);
if (!empty($amgenattendee[0]) && !empty($amgenattendee[1])) {
$attendeeexits = \backend\models\AmgenContactList::find()->where(['first_name' => $amgenattendee[0], 'last_name' => $amgenattendee[1]])->one();
if ($attendeeexits) {
$olengagement = new OlEngagement();
$olengagement->congress_id = $congress;
$olengagement->meeting_id = $meetingdetails->id;
$olengagement->title = $data[0];
$olengagement->ol_date = date("Y-m-d H:i:s", strtotime($data[2]));
$olengagement->ol_start_time = $data[3];
$olengagement->ol_end_time = $data[4];
$olengagement->location = $data[5];
$olengagement->attendee_id = $attendeeexits->id;
$olengagement->ol_attendee_id = '';
$olengagement->ol_profile_id = $profiletype;
$olengagement->meeting_approval = $approval;
$olengagement->rsr = $rsr;
$olengagement->ol_consultant = $consultant;
$olengagement->ol_catering = $data[12];
$olengagement->notes = $data[13];
$olengagement->save(TRUE);
try {
$olengagement->save(FALSE);
} catch (\Exception $exc) {
echo $exc->getTraceAsString();
}
$meetingschedule = new \backend\models\MeetingSchedules();
$meetingschedule->congress_id = $congress;
$meetingschedule->meeting_id = $meetingdetails->id;
$meetingschedule->attendee_id = $attendeeexits->id;
$meetingschedule->ol_attendee_id = "";
$meetingschedule->save(FALSE);
}
}
}
}
if (count($olattendee) > 0) {
foreach ($olattendee as $value) {
$value = trim($value);
$oladata = explode(' ', $value);
if (!empty($oladata[0]) && !empty($oladata[1])) {
$olattendeeexits = \backend\models\OlAttendees::find()->where(['first_name' => $oladata[0], 'last_name' => $oladata[1]])->one();
if ($olattendeeexits) {
$olengagement = new OlEngagement();
$olengagement->congress_id = $congress;
$olengagement->meeting_id = $meetingdetails->id;
$olengagement->title = $data[0];
$olengagement->ol_date = date("Y-m-d H:i:s", strtotime($data[2]));
$olengagement->ol_start_time = $data[3];
$olengagement->ol_end_time = $data[4];
$olengagement->location = $data[5];
$olengagement->attendee_id = '';
$olengagement->ol_attendee_id = $olattendeeexits->id;
$olengagement->ol_profile_id = $profiletype;
$olengagement->meeting_approval = $approval;
$olengagement->rsr = $rsr;
$olengagement->ol_consultant = $consultant;
$olengagement->ol_catering = $data[12];
$olengagement->notes = $data[13];
$olengagement->save(TRUE);
try {
$olengagement->save(FALSE);
} catch (Exception $exc) {
echo $exc->getTraceAsString();
}
$meetingschedule = new \backend\models\MeetingSchedules();
$meetingschedule->congress_id = $congress;
$meetingschedule->meeting_id = $meetingdetails->id;
$meetingschedule->attendee_id = '';
$meetingschedule->ol_attendee_id = $olattendeeexits->id;
$meetingschedule->save(FALSE);
}
}
}
}
} catch (Exception $exc) {
echo $exc->getTraceAsString();
}
}
}
Yii::$app->getSession()->setFlash('success', 'All OL is Uploaded Successfully');
} else {
Yii::$app->getSession()->setFlash('error', 'CSV is in Incorrect format');
}
fclose($handle);
$this->redirect(['meeting/index', 'id' => $congress]);
} else {
return $this->render('csvupload', ['model' => $model]);
}
}
/**
* @return \yii\db\ActiveQuery
*/
public function getMeetingType()
{
return $this->hasOne(MeetingTypes::className(), ['id' => 'meeting_type_id']);
}
/**
* Display sda, booth and meetings
* @return mixed
*/
public function actionGetsdabooth()
{
$connection = Yii::$app->db;
$congressId = (int) \Yii::$app->request->getQueryParam('congressid');
$finaldata = array();
if (!empty($congressId)) {
$sdaquery = "SELECT sda.* FROM sda \n WHERE sda.congress_id = {$congressId} AND sda.is_active = 1 order by sda.date asc,sda.session_start_time asc";
$boothquery = "SELECT ba.id,ba.booth_name,ba.date,ba.from_time,ba.to_time,ba.Area,ba.congress_id,ba.location \n FROM booths ba WHERE ba.congress_id = {$congressId} AND ba.is_active = 1 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, md.rsr, md.ol_consultant,md.meeting_approval, md.ol_catering, md.notes, md.ol_profile_id\n FROM meeting_details md\n WHERE md.congress_id = {$congressId} AND md.is_active = 1 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, 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\n LEFT JOIN cost_center c ON c.id = a.group_id\n WHERE ms.sda_id = {$value['id']}");
$attendee = $modelsda->queryAll();
$finaldata[] = array('id' => $value['id'], 'title' => $value['title'], 'date' => $value['date'], 'time' => date("g:i a", strtotime($value['session_start_time'])), 'location' => $value['location'], 'type' => 'SDA', 'abstract_id' => $value['abstract_id'], 'priority' => SdaPriority::findOne($value['priority'])->priority_name, 'abstract' => $value['abstract'], '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, 'category' => $value['category']);
}
foreach ($modelbooth as $value) {
$modelbooth = $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 booth_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.booth_id = {$value['id']}\n ");
$attendeebooth = $modelbooth->queryAll();
$data = array('id' => $value['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);
$finaldata[] = $data;
}
foreach ($meeting as $value) {
$sql = "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 meeting_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.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();
$data = 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, 'rsr' => $value['rsr'], 'ol_consultant' => $value['ol_consultant'], 'meeting_approval' => $value['meeting_approval'], 'ol_catering' => $value['ol_catering'], 'notes' => $value['notes']);
if (!empty($value['ol_profile_id'])) {
$data['ol_profile'] = \backend\models\OlProfiles::findOne($value['ol_profile_id'])->name;
}
$finaldata[] = $data;
}
}
return $finaldata;
}
/**
* Display meeting assigned to a individual
* @return mixed
*/
public function actionMymeeting()
{
$connection = Yii::$app->db;
if (Yii::$app->request->post()) {
$value = \Yii::$app->request->post();
$user = (int) $value['userid'];
$congress = (int) $value['congress'];
$user = User::findOne(['id' => $value['userid']]);
$finaldata = array();
if ($user) {
$sql = "SELECT mds.id as id,msd.congress_id as congress_id,mds.title as title,mds.start_time as start_time ,mds.stop_time as stop_time, mds.date as date,mds.location,mds.event_type, mds.rsr, mds.ol_consultant,mds.meeting_approval, mds.ol_catering, mds.notes, mds.ol_profile_id FROM meeting_schedules as msd INNER JOIN meeting_details as mds ON msd.meeting_id = mds.id WHERE msd.congress_id = {$congress} AND msd.attendee_id = {$user->attendee_id} order by mds.date ASC,mds.start_time ASC";
$modelmeeting = $connection->createCommand($sql)->queryAll();
$finalvalue = array();
if ($modelmeeting) {
foreach ($modelmeeting as $value) {
$sql = "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\n FROM meeting_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.meeting_id ={$value['id']}";
$attendee = $connection->createCommand($sql)->queryAll();
$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();
$data = array('meetingid' => $value['id'], 'congress' => \backend\models\CongressDetails::findOne($value['congress_id'])->name, '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, 'attendee' => $attendee, 'olattendee' => $modelolattendee, 'rsr' => $value['rsr'], 'ol_consultant' => $value['ol_consultant'], 'meeting_approval' => $value['meeting_approval'], 'ol_catering' => $value['ol_catering'], 'notes' => $value['notes']);
if (!empty($value['ol_profile_id'])) {
$data['ol_profile'] = \backend\models\OlProfiles::findOne($value['ol_profile_id'])->name;
}
$finaldata[] = $data;
}
return $finaldata;
} else {
echo "Meeting is not found";
}
} else {
echo "Contact is not founnd in contact list";
}
}
}
/**
* @return \yii\db\ActiveQuery
*/
public function getEventType()
{
return $this->hasOne(MeetingTypes::className(), ['id' => 'event_type']);
}
/** Get the list of all attachments */
public function actionAllattachment()
{
if (Yii::$app->request->post()) {
$value = \Yii::$app->request->post();
$congress = $value['congress'];
$sda_id = isset($value['sda_id']) && $value['sda_id'] > 0 ? $value['sda_id'] : "";
if ($congress) {
if ($sda_id > 0) {
$records_sda = \backend\models\SdaAttachments::find()->where(['congress_id' => $congress, 'sda_id' => $sda_id, 'sda_feedback_id' => null])->all();
} else {
$records_sda = \backend\models\SdaAttachments::find()->where(['congress_id' => $congress])->all();
}
$records_meeting = \backend\models\MeetingAttachments::find()->where(['congress_id' => $_POST['congress']])->all();
$return = [];
$data = [];
foreach ($records_sda as $row) {
$protocol = stripos($_SERVER['SERVER_PROTOCOL'], 'https') === true ? 'https://' : 'https://';
$host = $_SERVER['HTTP_HOST'];
$congressname = CongressDetails::findOne($row['congress_id'])->name;
unset($row['sda_feedback_id']);
unset($row['session_id']);
$row = $row->toArray();
$filename = explode("/", $row['path']);
$filenames = end(explode(".", $filename[1]));
$data['filename'] = str_replace($filenames, "", $filename[1]);
$data['filename'] = str_replace(".", "", $filename[1]);
$data['filetype'] = $filenames;
$data['path'] = $protocol . $host . "/rest/backend/web/" . $row['path'];
$data['exits'] = file_exists($_SERVER['DOCUMENT_ROOT'] . '/rest/backend/web/' . $row['path']);
$return['sda'][] = $data;
}
$data2 = [];
foreach ($records_meeting as $row) {
$protocol = stripos($_SERVER['SERVER_PROTOCOL'], 'https') === true ? 'https://' : 'https://';
$host = $_SERVER['HTTP_HOST'];
$row = $row->toArray();
$type = \backend\models\MeetingTypes::findOne($row['meeting_type_id']);
$congressname = CongressDetails::findOne($row['congress_id'])->name;
unset($row['meeting_type_id']);
$filename = explode("/", $row['path']);
$filenames = end(explode(".", $filename[1]));
$data2['filename'] = str_replace($filenames, "", $filename[1]);
$data2['filename'] = str_replace(".", "", $filename[1]);
$data2['filetype'] = $filenames;
$data2['path'] = $protocol . $host . "/rest/backend/web/" . $row['path'];
$data2['exits'] = file_exists($_SERVER['DOCUMENT_ROOT'] . '/rest/backend/web/' . $row['path']);
$return['meeting'][] = $data2;
}
return $return;
} else {
return "parameter missing";
}
} else {
return 'invalid method';
}
}
