public function exportQuestion(SS_HTTPRequest $request) { $qid = intval($request->requestVar('qid')); $template = $this->getCurrentSelectedSurveyTemplate(); $question = $template->getQuestionById($qid); $results_array = array(array($question->Label)); $column_labels = array(' '); foreach ($question->Columns() as $column) { $column_labels[] = $column->Label; } $results_array[] = $column_labels; foreach ($question->Rows() as $row) { $rows_array = array($row->Label); foreach ($row->Columns() as $row_column) { $rows_array[] = $this->SurveyBuilderMatrixCountAnswers($qid, $row->ID, $row_column->ID); } $results_array[] = $rows_array; } return CSVExporter::getInstance()->export('export_table.csv', $results_array, ','); }
public function exportMarketplaceAdmins() { $params = $this->owner->getRequest()->getVars(); if (!isset($params['marketplace_type']) || empty($params['marketplace_type'])) { return $this->owner->httpError('412', 'missing required param marketplace type'); } $marketplace_type = $params['marketplace_type']; $filters_string = implode("','", $marketplace_type); $fileDate = date('Ymdhis'); SangriaPage_Controller::generateDateFilters('s'); $sql = <<<SQL SELECT M.FirstName, M.Surname, M.Email, C.Name AS Company, GROUP_CONCAT(MT.Name ORDER BY MT.Name ASC SEPARATOR ' - ') AS Marketplace FROM Member AS M INNER JOIN ( SELECT MemberID, CompanyID, GroupID FROM Company_Administrators WHERE Company_Administrators.GroupID IN ('{$filters_string}') ) AS CA ON CA.MemberID = M.ID INNER JOIN Company AS C ON C.ID = CA.CompanyID INNER JOIN MarketPlaceType AS MT ON MT.AdminGroupID = CA.GroupID GROUP BY M.FirstName, M.Surname, M.Email, C.Name ORDER BY M.Email, C.Name ; SQL; $res = DB::query($sql); $fields = array('FirstName', 'Surname', 'Email', 'Company', 'Marketplace'); $data = array(); foreach ($res as $row) { $member = array(); foreach ($fields as $field) { $member[$field] = str_replace(',', ' ', $row[$field]); //commas tabs cell in excel } array_push($data, $member); } $filename = "Marketplace_Admins_" . $fileDate . ".csv"; return CSVExporter::getInstance()->export($filename, $data, ','); }
public function exportPresentationsCompanyReport(SS_HTTPRequest $request) { try { $query_string = $request->getVars(); $sort = isset($query_string['sort']) ? Convert::raw2sql($query_string['sort']) : 'name'; $sort_dir = isset($query_string['sort_dir']) ? Convert::raw2sql($query_string['sort_dir']) : 'ASC'; $search_term = isset($query_string['term']) ? Convert::raw2sql($query_string['term']) : ''; $summit_id = intval($request->param('SUMMIT_ID')); $summit = $this->summit_repository->getById($summit_id); if (is_null($summit)) { throw new NotFoundEntityException('Summit', sprintf(' id %s', $summit_id)); } $ext = 'csv'; $report_data = $this->presentation_repository->searchByCompanyPaged($summit_id, null, null, $sort, $sort_dir, $search_term); $filename = "presentations_company_report-" . date('Ymd') . "." . $ext; $delimiter = $ext == 'xls' ? "\t" : ","; return CSVExporter::getInstance()->export($filename, $report_data['Data'], $delimiter); } catch (NotFoundEntityException $ex2) { SS_Log::log($ex2->getMessage(), SS_Log::WARN); return $this->notFound($ex2->getMessage()); } catch (Exception $ex) { SS_Log::log($ex->getMessage(), SS_Log::ERR); return $this->serverError(); } }
function exportUsersPerRegion() { $params = $this->owner->getRequest()->getVars(); if (!isset($params['countries']) || empty($params['countries'])) { return $this->owner->httpError('412', 'missing required param countries'); } if (!isset($params['members']) || empty($params['members'])) { return $this->owner->httpError('412', 'missing required param members'); } $countries = $params['countries']; $members = $params['members']; $join_members = ''; $join_countries = ''; if (!count($countries)) { return $this->owner->httpError('412', 'missing required param countries'); } else { foreach ($countries as $country) { $join_countries .= "'" . $country . "',"; } } $join_countries = rtrim($join_countries, ","); if (!count($members)) { return $this->owner->httpError('412', 'missing required param members'); } else { foreach ($members as $member) { $join_members .= "'" . $member . "',"; } } $join_members = rtrim($join_members, ","); $query = new SQLQuery(); $select_fields = array('Member.FirstName', 'Member.Surname', 'Member.Email', 'Member.City', 'Member.State', 'Member.Country'); $query->setFrom('Member'); $query->setSelect($select_fields); $query->addInnerJoin('Group_Members', 'Group_Members.MemberID = Member.ID'); $query->addInnerJoin('Group', "Group.ID = Group_Members.GroupID AND Group.Code IN (" . $join_members . ")"); $query->setWhere("Member.Country IN (" . $join_countries . ")"); $query->setOrderBy('SurName,FirstName'); $result = $query->execute(); $data = array(); foreach ($result as $row) { $member = array('FirstName' => $row['FirstName'], 'Surname' => $row['Surname'], 'Email' => $row['Email'], 'City' => $row['City'], 'State' => $row['State'], 'Country' => CountryCodes::$iso_3166_countryCodes[$row['Country']]); array_push($data, $member); } $filename = "UsersPerCountry" . date('Ymd') . ".csv"; return CSVExporter::getInstance()->export($filename, $data, ','); }
public function exportReport(SS_HTTPRequest $request) { try { $query_string = $request->getVars(); $sort = isset($query_string['sort']) ? Convert::raw2sql($query_string['sort']) : 'name'; $sort_dir = isset($query_string['sort_dir']) ? Convert::raw2sql($query_string['sort_dir']) : 'ASC'; $event_type = isset($query_string['event_type']) ? Convert::raw2sql($query_string['event_type']) : 'all'; $venues = isset($query_string['venues']) ? $query_string['venues'] : ''; $tracks = isset($query_string['tracks']) ? html_entity_decode($query_string['tracks']) : 'all'; $start_date = isset($query_string['start_date']) && $query_string['start_date'] ? date('Y-m-d', strtotime($query_string['start_date'])) : ''; $end_date = isset($query_string['end_date']) && $query_string['end_date'] ? date('Y-m-d', strtotime($query_string['end_date'])) : ''; $search_term = isset($query_string['search_term']) ? $query_string['search_term'] : ''; $report = $request->param('REPORT'); $summit_id = intval($request->param('SUMMIT_ID')); $summit = $this->summit_repository->getById($summit_id); if (is_null($summit)) { throw new NotFoundEntityException('Summit', sprintf(' id %s', $summit_id)); } $ext = 'csv'; switch ($report) { case 'speaker_report': $filter = isset($query_string['filter']) ? $query_string['filter'] : 'all'; $report_data = $this->assistance_repository->getAssistanceBySummit($summit_id, null, null, $sort, $sort_dir, $filter); $data = $report_data['Data']; $results = array(); foreach ($data as $row) { array_push($results, $row); } $filename = "speaker_report-" . date('Ymd') . "." . $ext; $delimiter = $ext == 'xls' ? "\t" : ","; return CSVExporter::getInstance()->export($filename, $results, $delimiter); break; case 'room_report': $filename = "room_report-" . date('Ymd') . ".xlsx"; $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("OpenStack"); $objPHPExcel->getProperties()->setTitle("Speaker Per Room Report"); $objPHPExcel->setActiveSheetIndex(0); // sheet 1, key codes $categories = $summit->Categories()->toArray(); $active_sheet = $objPHPExcel->getActiveSheet(); $active_sheet->setTitle("Key Codes"); $active_sheet->fromArray(array('Code', 'Category'), NULL, 'A1'); foreach ($categories as $key => $category) { $row = $key + 2; $active_sheet->SetCellValue('A' . $row, $category->Code); $active_sheet->SetCellValue('B' . $row, $category->Title); } // day sheets $days = $summit->getDates(); foreach ($days as $day) { $active_sheet = $objPHPExcel->createSheet(); $active_sheet->setTitle(date('n-d', strtotime($day->Date))); $active_sheet->fromArray(array('Date', 'Time', 'Code', 'Event', 'Room', 'Venue', 'Capacity', 'Speakers', 'Headcount', 'Total', 'Speaker Names'), NULL, 'A1'); $day_report = $this->assistance_repository->getRoomsBySummitAndDay($summit_id, $day->Date, $event_type, $venues); foreach ($day_report as $key2 => $val) { $row = $key2 + 2; $start_time = $summit->convertDateFromUTC2TimeZone($val['start_date'], 'g:ia'); $end_time = $summit->convertDateFromUTC2TimeZone($val['end_date'], 'g:ia'); $date = $summit->convertDateFromUTC2TimeZone($val['start_date'], 'm/d/Y'); $time = $start_time . ' - ' . $end_time; unset($val['start_date']); unset($val['end_date']); unset($val['id']); $val['date'] = $date; $val['time'] = $time; $active_sheet->fromArray($val, NULL, 'A' . $row); } } $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); header('Content-type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="' . $filename . '"'); $objWriter->save('php://output'); return; break; case 'presentation_report': $search_term = isset($query_string['term']) ? Convert::raw2sql($query_string['term']) : ''; $filter = isset($query_string['filter']) ? $query_string['filter'] : 'all'; $report_data = $this->assistance_repository->getPresentationsAndSpeakersBySummit($summit_id, null, null, $sort, $sort_dir, $search_term, $filter); $data = $report_data['Data']; $results = array(); foreach ($data as $row) { $row['start_date'] = $summit->convertDateFromUTC2TimeZone($row['start_date'], 'm/d/Y g:ia'); unset($row['presentation_id']); unset($row['assistance_id']); array_push($results, $row); } $filename = "presentations_report-" . date('Ymd') . "." . $ext; $delimiter = $ext == 'xls' ? "\t" : ","; return CSVExporter::getInstance()->export($filename, $results, $delimiter); break; case 'video_report': $filename = "video_report-" . date('Ymd') . ".xlsx"; $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("OpenStack"); $objPHPExcel->getProperties()->setTitle("Video Output List"); // day sheets $days = $summit->getDates(); foreach ($days as $day) { $active_sheet = $objPHPExcel->createSheet(); $active_sheet->setTitle(date('n-d', strtotime($day->Date))); $active_sheet->fromArray(array('Date', 'Time', 'Tags', 'Event', 'Description', 'Room', 'Venue', 'Display', 'YoutubeID'), NULL, 'A1'); $day_report = $this->assistance_repository->getPresentationMaterialBySummitAndDay($summit_id, $day->Date, $tracks, $venues, $start_date, $end_date, $search_term); foreach ($day_report as $key2 => $val) { $row = $key2 + 2; $start_time = $summit->convertDateFromUTC2TimeZone($val['start_date'], 'g:ia'); $end_time = $summit->convertDateFromUTC2TimeZone($val['end_date'], 'g:ia'); $date = $summit->convertDateFromUTC2TimeZone($val['start_date'], 'm/d/Y'); $time = $start_time . ' - ' . $end_time; unset($val['start_date']); unset($val['end_date']); unset($val['id']); $val['date'] = $date; $val['time'] = $time; $val['tags'] .= ',' . $val['speakers'] . ',OpenStack Summit Austin'; unset($val['speakers']); $active_sheet->fromArray($val, NULL, 'A' . $row); } } $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); header('Content-type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="' . $filename . '"'); $objWriter->save('php://output'); return; break; case 'rsvp_report': $search_term = isset($query_string['term']) ? Convert::raw2sql($query_string['term']) : ''; $events = $this->event_repository->searchBySummitTermAndHasRSVP($summit, $search_term); if (count($events)) { if (count($events) == 1) { $results = array(); $event = array_pop($events); list($rsvps, $total) = $this->rsvp_repository->getByEventPaged($event->ID, null, null); $rsvp_array_template = array(); foreach ($event->RSVPTemplate()->Questions()->sort('Order') as $question) { if ($question->Label) { $rsvp_array_template[$question->Label] = ''; } } if (count($rsvps)) { foreach ($rsvps as $rsvp) { $rsvp_array = $rsvp_array_template; foreach ($rsvp->Answers() as $answer) { $rsvp_array[$answer->Question()->Label] = $answer->getFormattedAnswer(); } $results[] = $rsvp_array; } } $filename = "rsvp_report-" . date('Ymd') . "." . $ext; $delimiter = $ext == 'xls' ? "\t" : ","; return CSVExporter::getInstance()->export($filename, $results, $delimiter); } } return $this->notFound(); break; } return $this->notFound(); } catch (NotFoundEntityException $ex2) { SS_Log::log($ex2->getMessage(), SS_Log::WARN); return $this->notFound($ex2->getMessage()); } catch (Exception $ex) { SS_Log::log($ex->getMessage(), SS_Log::ERR); return $this->serverError(); } }
/** * Implement this method in the task subclass to * execute via the TaskRunner */ public function run($request) { set_time_limit(0); $api = new TrackChairAPI(); $api->handleRestoreOrders(); $type = intval($request->getVar('type')); if (empty($type)) { $type = 1; } $speakers = PresentationSpeaker::get()->filter('SummitID', Summit::get_active()->ID); $only_accepted = array(); $accepted_rejected = array(); $only_rejected = array(); $only_alternate = array(); $accepted_alternate = array(); $alternate_rejected = array(); foreach ($speakers as $speaker) { $rejected = $speaker->UnacceptedPresentations(); $alternate = $speaker->AlternatePresentations(); $accepted = $speaker->AcceptedPresentations(); if ($accepted->count() > 0 && $rejected->count() === 0 && $alternate->count() === 0) { // only accepted if (!$speaker->Member()) { continue; } $email = $speaker->Member()->Email; if (empty($email)) { continue; } foreach ($accepted as $row) { $only_accepted[] = array('PresentationID' => $row->ID, 'PresentationTitle' => $row->Title, 'SpeakerEmail' => $email, 'SpeakerID' => $speaker->Member()->ID, 'Status' => 'ACCEPTED'); } continue; } if ($accepted->count() === 0 && $rejected->count() === 0 && $alternate->count() > 0) { // only alternated if (!$speaker->Member()) { continue; } $email = $speaker->Member()->Email; if (empty($email)) { continue; } foreach ($alternate as $row) { $only_alternate[] = array('PresentationID' => $row->ID, 'PresentationTitle' => $row->Title, 'SpeakerEmail' => $speaker->Member()->Email, 'SpeakerID' => $speaker->Member()->ID, 'Status' => 'ALTERNATE'); } continue; } if ($accepted->count() === 0 && $rejected->count() > 0 && $alternate->count() === 0) { // only rejected if (!$speaker->Member()) { continue; } $email = $speaker->Member()->Email; if (empty($email)) { continue; } foreach ($rejected as $row) { $only_rejected[] = array('PresentationID' => $row->ID, 'PresentationTitle' => $row->Title, 'SpeakerEmail' => $speaker->Member()->Email, 'SpeakerID' => $speaker->Member()->ID, 'Status' => 'REJECTED'); } continue; } if ($accepted->count() > 0 && $rejected->count() === 0 && $alternate->count() > 0) { if (!$speaker->Member()) { continue; } $email = $speaker->Member()->Email; if (empty($email)) { continue; } // only accepted foreach ($accepted as $row) { $accepted_alternate[] = array('PresentationID' => $row->ID, 'PresentationTitle' => $row->Title, 'SpeakerEmail' => $speaker->Member()->Email, 'SpeakerID' => $speaker->Member()->ID, 'Status' => 'ACCEPTED'); } // only alternated foreach ($alternate as $row) { $accepted_alternate[] = array('PresentationID' => $row->ID, 'PresentationTitle' => $row->Title, 'SpeakerEmail' => $speaker->Member()->Email, 'SpeakerID' => $speaker->Member()->ID, 'Status' => 'ALTERNATE'); } continue; } if ($accepted->count() > 0 && $rejected->count() > 0 && $alternate->count() === 0) { if (!$speaker->Member()) { continue; } $email = $speaker->Member()->Email; if (empty($email)) { continue; } // accepted and rejected foreach ($accepted as $row) { $accepted_rejected[] = array('PresentationID' => $row->ID, 'PresentationTitle' => $row->Title, 'SpeakerEmail' => $speaker->Member()->Email, 'SpeakerID' => $speaker->Member()->ID, 'Status' => 'ACCEPTED'); } foreach ($rejected as $row) { $accepted_rejected[] = array('PresentationID' => $row->ID, 'PresentationTitle' => $row->Title, 'SpeakerEmail' => $speaker->Member()->Email, 'SpeakerID' => $speaker->Member()->ID, 'Status' => 'REJECTED'); } continue; } if ($accepted->count() === 0 && $rejected->count() > 0 && $alternate->count() > 0) { if (!$speaker->Member()) { continue; } $email = $speaker->Member()->Email; if (empty($email)) { continue; } foreach ($alternate as $row) { $alternate_rejected[] = array('PresentationID' => $row->ID, 'PresentationTitle' => $row->Title, 'SpeakerEmail' => $speaker->Member()->Email, 'SpeakerID' => $speaker->Member()->ID, 'Status' => 'ALTERNATE'); } foreach ($rejected as $row) { $alternate_rejected[] = array('PresentationID' => $row->ID, 'PresentationTitle' => $row->Title, 'SpeakerEmail' => $speaker->Member()->Email, 'SpeakerID' => $speaker->Member()->ID, 'Status' => 'REJECTED'); } continue; } } switch ($type) { case 1: $filename = 'only_accepted_speakers.cvs'; $result = $only_accepted; break; case 2: $filename = 'only_alternate_speakers.cvs'; $result = $only_alternate; break; case 3: $filename = 'only_rejected_speakers.cvs'; $result = $only_rejected; break; case 4: $filename = 'accepted_alternate_speakers.cvs'; $result = $accepted_alternate; break; case 5: $filename = 'accepted_rejected_speakers.cvs'; $result = $accepted_rejected; break; case 6: $filename = 'alternate_rejected_speakers.cvs'; $result = $alternate_rejected; break; } CSVExporter::getInstance()->export($filename, $result); }
public function exportRegistrationCodes(SS_HTTPRequest $request) { try { $query_string = $request->getVars(); $code_type = isset($query_string['type']) ? $query_string['type'] : ''; $term = isset($query_string['term']) ? $query_string['term'] : ''; $summit_id = intval($request->param('SUMMIT_ID')); $summit = $this->summit_repository->getById($summit_id); if (is_null($summit)) { throw new NotFoundEntityException('Summit', sprintf(' id %s', $summit_id)); } $ext = 'csv'; list($page, $page_size, $count, $codes) = $this->code_repository->searchByTermAndSummitPaginated($summit_id, $code_type, 1, 0, $term); $results = array(); foreach ($codes as $row) { $code_array = array('Code' => $row['code'], 'Type' => $row['type'], 'Owner' => $row['owner'], 'Email' => $row['owner_email'], 'Sponsor' => $row['sponsor'], 'Emailed' => $row['email_sent'], 'Redeemed' => $row['redeemed']); if (!$code_type) { $this_type = $row['type']; if (!isset($results[$this_type])) { $results[$this_type] = array(); } $results[$this_type][] = $code_array; } else { array_push($results, $code_array); } } $filename = "promocodes_report-" . date('Ymd') . "." . $ext; $delimiter = $ext == 'xls' ? "\t" : ","; if (!$code_type) { // if we export all codes we put each type on a different sheet $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("OpenStack"); $objPHPExcel->getProperties()->setTitle("PromoCodes"); $sheet_no = 0; foreach ($results as $type => $codes) { if ($sheet_no > 0) { $active_sheet = $objPHPExcel->createSheet(); } else { $objPHPExcel->setActiveSheetIndex(0); $active_sheet = $objPHPExcel->getActiveSheet(); } $sheet_title = $type ? $type : 'No Type'; $active_sheet->setTitle($sheet_title); $active_sheet->fromArray(array('Code', 'Type', 'Owner', 'Email', 'Sponsor', 'Emailed', 'Redeemed'), NULL, 'A1'); foreach ($codes as $key => $code) { $row = $key + 2; $active_sheet->fromArray($code, NULL, 'A' . $row); } $sheet_no++; } $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); header('Content-type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="' . $filename . '"'); $objWriter->save('php://output'); return; } else { return CSVExporter::getInstance()->export($filename, $results, $delimiter); } } catch (NotFoundEntityException $ex2) { SS_Log::log($ex2->getMessage(), SS_Log::WARN); return $this->notFound($ex2->getMessage()); } catch (Exception $ex) { SS_Log::log($ex->getMessage(), SS_Log::ERR); return $this->serverError(); } }