/**
* Export to the Excel template using the PHPExcel library
*
* @param \GIB\GradingTool\Domain\Model\Project $project
*/
public function exportExcelGradingAction(\GIB\GradingTool\Domain\Model\Project $project)
{
// Write Grading results
$grading = $this->submissionService->getProcessedSubmission($project);
if ($grading['hasError']) {
// Don't export the Grading if is has errors
$message = new \TYPO3\Flow\Error\Message('The Grading has errors and therefore it cannot be exported. Review and correct the Grading.', \TYPO3\Flow\Error\Message::SEVERITY_ERROR);
$this->flashMessageContainer->addMessage($message);
$this->redirect('index', 'Standard');
}
// the uploaded export template
$templateFilePathAndFileName = \TYPO3\Flow\Utility\Files::concatenatePaths(array($this->settings['export']['excel']['templatePath'], $this->settings['export']['excel']['templateFileName']));
$excelReader = new \PHPExcel_Reader_Excel2007();
$excelReader->setLoadSheetsOnly($this->settings['export']['excel']['worksheetLabel']);
$phpExcel = $excelReader->load($templateFilePathAndFileName);
$worksheet = $phpExcel->getSheetByName($this->settings['export']['excel']['worksheetLabel']);
$firstSectionColumn = $this->settings['export']['excel']['firstSectionColumn'];
// we need to subtract 1 because of https://github.com/PHPOffice/PHPExcel/issues/307
$columnNumber = \PHPExcel_Cell::columnIndexFromString($firstSectionColumn) - 1;
foreach ($grading['sections'] as $section) {
$row = $this->settings['export']['excel']['sectionLabelFirstRow'];
$column = \PHPExcel_Cell::stringFromColumnIndex($columnNumber);
$worksheet->getCell($column . $row)->setValue($section['label']);
foreach ($section['questions'] as $question) {
$row++;
if (isset($question['score'])) {
if ($question['score'] === 'N/A') {
// N/A is value 5 in Excel tool
$worksheet->getCell($column . $row)->setValue('5');
} else {
$worksheet->getCell($column . $row)->setValue($question['score']);
}
}
}
$columnNumber++;
}
// Write project title
$worksheet->getCell($this->settings['export']['excel']['projectTitleCell'])->setValue($project->getProjectTitle());
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="grading.xlsx"');
header('Cache-Control: max-age=0');
/** @var \PHPExcel_Writer_Excel2007 $excelWriter */
$excelWriter = \PHPExcel_IOFactory::createWriter($phpExcel, 'Excel2007');
$excelWriter->save('php://output');
}
function oadueslookup_options()
{
global $wpdb;
$dbprefix = $wpdb->prefix . "oalm_";
$hidden_field_name = 'oalm_submit_hidden';
if (!current_user_can('manage_options')) {
wp_die(__('You do not have sufficient permissions to access this page.'));
}
// =========================
// form processing code here
// =========================
if (isset($_FILES['oalm_file'])) {
#echo "<h3>Processing file upload</h3>";
#echo "<strong>Processing File:</strong> " . esc_html($_FILES['oalm_file']['name']) . "<br>";
#echo "<strong>Type:</strong> " . esc_html($_FILES['oalm_file']['type']) . "<br>";
if (preg_match('/\\.xlsx$/', $_FILES['oalm_file']['name'])) {
/** PHPExcel */
include plugin_dir_path(__FILE__) . 'PHPExcel-1.8.0/Classes/PHPExcel.php';
/** PHPExcel_Writer_Excel2007 */
include plugin_dir_path(__FILE__) . 'PHPExcel-1.8.0/Classes/PHPExcel/Writer/Excel2007.php';
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objReader->setLoadSheetsOnly(array("All"));
$objPHPExcel = $objReader->load($_FILES["oalm_file"]["tmp_name"]);
$objWorksheet = $objPHPExcel->getActiveSheet();
$columnMap = array('BSA ID' => 'bsaid', 'Dues Yr.' => 'max_dues_year', 'Dues Pd. Dt.' => 'dues_paid_date', 'Level' => 'level', 'Reg. Audit Date' => 'reg_audit_date', 'Reg. Audit Result' => 'reg_audit_result');
$complete = 0;
$recordcount = 0;
$error_output = "";
foreach ($objWorksheet->getRowIterator() as $row) {
$rowData = array();
if ($row->getRowIndex() == 1) {
# this is the header row, grab the headings
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(FALSE);
foreach ($cellIterator as $cell) {
$cellValue = $cell->getValue();
if (isset($columnMap[$cellValue])) {
$rowData[$columnMap[$cellValue]] = 1;
#echo "Found column " . htmlspecialchars($cell->getColumn()) . " with title '" . htmlspecialchars($cellValue) . "'<br>" . PHP_EOL;
} else {
#echo "Discarding unknown column " . htmlspecialchars($cell->getColumn()) . " with title '" . htmlspecialchars($cellValue) . "'<br>" . PHP_EOL;
}
}
$missingColumns = array();
foreach ($columnMap as $key => $value) {
if (!isset($rowData[$value])) {
$missingColumns[] = $key;
}
}
if ($missingColumns) {
?>
<div class="error"><p><strong>Import failed.</strong></p><p>Missing required columns: <?php
esc_html_e(implode(", ", $missingColumns));
?>
</div><?php
$complete = 1;
# Don't show "may have failed" box at the bottom
break;
} else {
#echo "<strong>Data format validated:</strong> Importing new data...<br>" . PHP_EOL;
# we just validated that we have a good data file, nuke the existing data
$wpdb->show_errors();
ob_start();
$wpdb->query("TRUNCATE TABLE {$dbprefix}dues_data");
update_option('oadueslookup_last_import', $wpdb->get_var("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')"));
# re-insert the test data
oadueslookup_insert_sample_data();
# now we're ready for the incoming from the rest of the file.
}
} else {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(FALSE);
foreach ($cellIterator as $cell) {
$columnName = $objWorksheet->getCell($cell->getColumn() . "1")->getValue();
$value = "";
if ($columnName == "Dues Pd. Dt.") {
# this is a date field, and we have to work miracles to turn it into a mysql-compatible date
$date = $cell->getValue();
$dateint = intval($date);
$dateintVal = (int) $dateint;
$value = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY-MM-DD");
} else {
if ($columnName == "Reg. Audit Date") {
# this is also a date field, but can be empty
$date = $cell->getValue();
if (!$date) {
$value = get_option('oadueslookup_last_import');
} else {
$dateint = intval($date);
$dateintVal = (int) $dateint;
$value = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY-MM-DD");
}
} else {
$value = $cell->getValue();
}
}
if (isset($columnMap[$columnName])) {
$rowData[$columnMap[$columnName]] = $value;
}
}
if ($wpdb->insert($dbprefix . "dues_data", $rowData, array('%s', '%s', '%s', '%s', '%s'))) {
$recordcount++;
}
}
}
$error_output = ob_get_clean();
if (!$error_output) {
?>
<div class="updated"><p><strong>Import successful. Imported <?php
esc_html_e($recordcount);
?>
records.</strong></p></div><?php
} else {
?>
<div class="error"><p><strong>Import partially successful. Imported <?php
esc_html_e($recordcount);
?>
of <?php
esc_html_e($row->getRowIndex() - 2);
?>
records.</strong></p>
<p>Errors follow:</p>
<?php
echo $error_output;
?>
</div><?php
}
update_option('oadueslookup_last_update', $wpdb->get_var("SELECT DATE_FORMAT(MAX(dues_paid_date), '%Y-%m-%d') FROM {$dbprefix}dues_data"));
} else {
?>
<div class="error"><p><strong>Invalid file upload.</strong> Not an XLSX file.</p></div><?php
}
}
//
// HANDLE SETTINGS SCREEN UPDATES
//
if (isset($_POST[$hidden_field_name]) && $_POST[$hidden_field_name] == 'oadueslookup-settings') {
$slug = $_POST['oadueslookup_slug'];
$dues_url = $_POST['oadueslookup_dues_url'];
$dues_register = $_POST['oadueslookup_dues_register'];
$dues_register_msg = $_POST['oadueslookup_dues_register_msg'];
$update_url = $_POST['oadueslookup_update_url'];
$update_link_text = $_POST['oadueslookup_update_option_link_text'];
$update_option_text = $_POST['oadueslookup_update_option_text'];
$help_email = $_POST['oadueslookup_help_email'];
# $help_email is the only one that throws an error if it doesn't
# validate. The others we just silently fix so they're something
# valid. The user will see the result on the form.
if (!is_email($help_email)) {
?>
<div class="error"><p><strong>'<?php
esc_html_e($help_email);
?>
' is not a valid email address.</strong></p></div><?php
} else {
$foundchanges = 0;
$slug = sanitize_title($slug);
if ($slug != get_option('oadueslookup_slug')) {
update_option('oadueslookup_slug', $slug);
$foundchanges = 1;
}
$dues_url = esc_url_raw($dues_url);
if ($dues_url != get_option('oadueslookup_dues_url')) {
update_option('oadueslookup_dues_url', $dues_url);
$foundchanges = 1;
}
if ($dues_register != get_option('oadueslookup_dues_register')) {
update_option('oadueslookup_dues_register', $dues_register);
$foundchanges = 1;
}
$dues_register_msg = sanitize_text_field($dues_register_msg);
if ($dues_register_msg != get_option('oadueslookup_dues_register_msg')) {
update_option('oadueslookup_dues_register_msg', $dues_register_msg);
$foundchanges = 1;
}
$update_url = esc_url_raw($update_url);
if ($update_url != get_option('oadueslookup_update_url')) {
update_option('oadueslookup_update_url', $update_url);
$foundchanges = 1;
}
$update_link_text = sanitize_text_field($update_link_text);
if ($update_link_text != get_option('oadueslookup_update_option_link_text')) {
update_option('oadueslookup_update_option_link_text', $update_link_text);
$foundchanges = 1;
}
$update_option_text = sanitize_text_field($update_option_text);
if ($update_option_text != get_option('oadueslookup_update_option_text')) {
update_option('oadueslookup_update_option_text', $update_option_text);
$foundchanges = 1;
}
$help_email = sanitize_email($help_email);
if ($help_email != get_option('oadueslookup_help_email')) {
update_option('oadueslookup_help_email', $help_email);
$foundchanges = 1;
}
if ($foundchanges) {
?>
<div class="updated"><p><strong>Changes saved.</strong></p></div><?php
}
}
}
// ============================
// screens and forms start here
// ============================
//
// MAIN SETTINGS SCREEN
//
echo '<div class="wrap">';
// header
echo "<h2>" . __('OA Dues Lookup Settings', 'oadueslookup') . "</h2>";
// settings form
?>
<h3 class="oalm">Import data from OALM</h3>
<p>Export file from OALM Must contain at least the following columns:<br>
BSA ID, Dues Yr., Dues Pd. Dt., Level, Reg. Audit Date, Reg. Audit Result<br>
Any additional columns will be ignored.</p>
<p><a href="http://github.com/justdave/oadueslookup/wiki">How to create the export file in OALM</a></p>
<form action="" method="post" enctype="multipart/form-data">
<label for="oalm_file">Click Browse, then select the xlsx file exported from OALM's "Export Members", then click "Upload":</label><br>
<input type="file" name="oalm_file" id="oalm_file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">
<input type="submit" class="button button-primary" name="submit" value="Upload"><br>
<p><strong>Last import:</strong> <?php
$last_import = get_option('oadueslookup_last_import');
if ($last_import == '1900-01-01') {
echo "Never";
} else {
esc_html_e($last_import);
}
?>
</p>
</form>
<h3 class="oalm">Lookup Page Settings</h3>
<form name="oadueslookup-settings" method="post" action="">
<input type="hidden" name="<?php
echo $hidden_field_name;
?>
" value="oadueslookup-settings">
<table class="form-table">
<tbody>
<tr>
<th scope="row"><label for="oadueslookup_slug">Dues Page Slug</label></th>
<td><code><?php
echo esc_html(get_option("home"));
?>
/</code><input id="oadueslookup_slug" name="oadueslookup_slug" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_slug"));
?>
">
<p class="description">The name appended to your Site URL to reach the lookup page.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_dues_url">Dues Payment URL</label></th>
<td><input id="oadueslookup_dues_url" name="oadueslookup_dues_url" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_dues_url"));
?>
">
<p class="description">The URL to send members to for actually paying their dues.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_dues_register">Registration Required?</label></th>
<td><input id="oadueslookup_dues_register" name="oadueslookup_dues_register" class="code" type="checkbox" value="1"<?php
checked(1 == esc_html(get_option('oadueslookup_dues_register')));
?>
">
<p class="description">Does the dues payment site require the user to register before paying?</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_dues_register_msg">Registration Required Message</label></th>
<td><input id="oadueslookup_dues_register_msg" name="oadueslookup_dues_register_msg" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_dues_register_msg"));
?>
">
<p class="description">The instruction text to display informing the user that they need to register before paying dues.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_update_url">Update Contact Info URL</label></th>
<td><input id="oadueslookup_update_url" name="oadueslookup_update_url" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_update_url"));
?>
">
<p class="description">The URL to send members to for updating their contact information.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_update_option_link_text">Update Contact Link Info Text</label></th>
<td><input id="oadueslookup_update_option_link_text" name="oadueslookup_update_option_link_text" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_update_option_link_text"));
?>
">
<p class="description">The text to appear in the hyperlink to the Update Contact Information URL.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_update_option_text">Section Label</label></th>
<td><input id="oadueslookup_update_option_text" name="oadueslookup_update_option_text" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_update_option_text"));
?>
">
<p class="description">The label or option on the Update Contact Information page to direct the user to.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_help_email">Help Email</label></th>
<td><input id="oadueslookup_help_email" name="oadueslookup_help_email" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_help_email"));
?>
">
<p class="description">The email address for members to ask questions.</p>
</td>
</tr>
</tbody>
</table>
<p class="submit"><input id="submit" class="button button-primary" type="submit" value="Save Changes" name="submit"></p>
</form>
<?php
echo "</div>";
}
public function uploadAction()
{
// $data = $this->getRequest()->getPost();
$file = $_FILES['xls'];
if ($file['error'] == 4) {
Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('Please choose file to upload.'));
$this->_redirect('*/*/');
return;
} elseif ($file['error']) {
Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('Upload error. Please try again.'));
$this->_redirect('*/*/');
return;
} elseif (!preg_match('/\\.xlsx{0,1}$/', $file['name'])) {
Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('You can only use XLS or XLSX files.'));
$this->_redirect('*/*/');
return;
}
$name = $file['name'];
$ext = preg_match('/xlsx$/', $name) ? 'xlsx' : 'xls';
$xls_file_name = Mage::getBaseDir() . "/custom/add_products/xls/import.{$ext}";
@unlink($xls_file_name);
move_uploaded_file($file['tmp_name'], $xls_file_name);
try {
require_once Mage::getBaseDir() . "/custom/include/PHPExcel/PHPExcel.php";
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
$cacheSettings = array('cacheTime' => 6000);
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
if ($ext == 'xls') {
$objReader = new PHPExcel_Reader_Excel5();
} else {
$objReader = new PHPExcel_Reader_Excel2007();
}
$objReader->setReadDataOnly(true);
$names = $objReader->listWorksheetNames($xls_file_name);
// ww($CUSTOM);
$DIR = Mage::getBaseDir() . "/custom/add_products/";
$files = array();
foreach ($names as $key => $name) {
$name = strtolower($name);
$name = preg_replace('/\\d$/', '', $name);
$files[$name] = $name;
}
foreach ($files as $file) {
@unlink($DIR . 'csv/' . $file . '.csv');
}
$i = 0;
foreach ($names as $key => $name) {
$file_name = strtolower($name);
$file_name = preg_replace('/\\d$/', '', $file_name);
$i++;
$file_path = $DIR . 'csv/' . $file_name . '.csv';
$skip_first_row = false;
if (file_exists($file_path)) {
// wlog("Appending: $file_name.csv", false);
$skip_first_row = true;
}
/* else wlog("Creating: $file_name.csv", false);*/
$fp = fopen($file_path, 'a');
$objReader->setLoadSheetsOnly(array($name));
$objPHPExcel = $objReader->load($xls_file_name);
$objWorksheet = $objPHPExcel->getActiveSheet();
$j = 0;
foreach ($objWorksheet->getRowIterator() as $row) {
$j++;
if ($j == 1 && $skip_first_row) {
continue;
}
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$fields = array();
foreach ($cellIterator as $cell) {
$value = $cell->getValue();
$fields[] = $value;
}
fputcsv($fp, $fields);
}
fclose($fp);
}
} catch (Exception $e) {
Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('Error processing file.<br/>%s', $e->getMessage()));
$this->_redirect('*/*/');
return;
}
Mage::getSingleton('core/session')->addSuccess(Mage::helper('maxfurniture')->__('File uploaded and conferted to CSV files successfully.'));
$this->_redirect('*/*/');
}
break;
default:
foreach ($files as $key => $filename) {
printf("%2d. {$filename}\n", $key + 1, $filename);
}
$choice = readline("Sélection du fichier à importer: ");
if (!array_key_exists(--$choice, $files)) {
exit_error("Fichier non trouvé");
}
$filename = $files[$choice];
echo "\n";
}
echo "Importation de '{$filename}'\n";
// Initialize Excel Reader
$xlReader = new PHPExcel_Reader_Excel2007();
$xlReader->setLoadSheetsOnly(IMPORT_COTI_SHEET_NAME)->setReadDataOnly(true);
// Retrieve header row
$xlReader->setReadFilter(new ReadFilterFirstRow());
$xl = $xlReader->load($filename);
$xlWS = $xl->getSheet(0);
$headers = array();
foreach ($xlWS->getRowIterator(1)->current()->getCellIterator() as $cell) {
$headers[$cell->getColumn()] = $cell->getValue();
}
// Read the rest of the data file
try {
$xlReader->setReadFilter(new ReadFilterEmailCoti($headers));
} catch (ColumnNotDefinedException $e) {
print "ERREUR: " . $e->getMessage() . "\n";
die(1);
}
