/**
* Reads the file into a PHPExcel object.
*
* @param string $filename The file to parse.
*
* @return \PHPExcel Returns the PHPExcel object for the file.
*
* @throws ParseException when the reader encounters an error.
*/
private static function readFile(string $filename) : \PHPExcel
{
try {
$reader = new \PHPExcel_Reader_Excel2007();
$reader->setReadDataOnly(true);
$xlsx = $reader->load($filename);
} catch (\Exception $e) {
throw new ParseException($e->getMessage());
}
return $xlsx;
}
/**
* Loads source data from local file into memory.
*/
private function load_source_data_from_file()
{
require_once DOC_ROOT . '/vendor/PHPExcel/Classes/PHPExcel.php';
// NOTE: In development it takes 24 Seconds to load and uses 318 MB of memory.
$start = microtime(true);
debug('Loading source file.');
try {
$reader = new \PHPExcel_Reader_Excel2007();
$reader->setReadDataOnly(true);
$this->source_loaded = $reader->load($this->source_file_path);
unset($reader);
} catch (\Exception $e) {
throw new \Exception('Error loading source data from ' . "{$this->source_file_path}: {$e->getMessage()}");
}
$this->profile($start);
}
/**
* @desc 读取excel数据
* @param string $excelFilePath excel的保存文件路径
*/
public function readExcel($excelFilePath)
{
$return = array('status' => 0);
$PHPReader = new PHPExcel_Reader_Excel2007();
$PHPReader->setReadDataOnly(true);
//设为只读
//如果没有选择excel文件
if (!$PHPReader->canRead($excelFilePath)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($excelFilePath)) {
$return['msg'] = 'no Excel';
return $return;
}
}
$PHPExcel = $PHPReader->load($excelFilePath);
//传入excel路径，载入该excel
$currentSheet = $PHPExcel->getSheet(0);
//sheet 0
$allColumn = $currentSheet->getHighestColumn();
//总列 //column Count
$allRow = $currentSheet->getHighestRow();
//总行//Row Count
$blockStartEndRow = $this->getBloclStartEndRow($allColumn, $allRow, $currentSheet);
$excelData = $this->getExcelData($blockStartEndRow, $allColumn, $allRow, $currentSheet);
if (ISPRINTEXCELDATAARR) {
echo '<pre>';
echo '开始-结束行<br/>';
print_r($blockStartEndRow);
echo '<hr/>';
echo 'excel数据数组<br/>';
print_r($excelData);
exit;
}
unlink($excelFilePath);
//删除excel文件
$return['data'] = $excelData;
return $return;
}
public function upload_report_listing($file_name)
{
// Include PHPExcel_IOFactory
// include 'PHPExcel/IOFactory.php';
// include 'PHPExcel/PHPExcel.php';
// $inputFileName = 'excel_files/garissa_sms_recepients_updated.xlsx';
// echo $category;exit;
$inputFileName = 'print_docs/excel/uploaded_files/' . $file_name;
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
// echo "<pre>";print_r($inputFileName);exit;
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow() + 1;
$highestColumn = $sheet->getHighestColumn();
// echo "<pre>";print_r($highestRow);echo "</pre>";exit;
$rowData = array();
for ($row = 4; $row < $highestRow; $row++) {
// Read a row of data into an array
$rowData_ = $sheet->rangeToArray('A' . $row . ':F' . $row);
// echo "<pre>";print_r($rowData_);echo "</pre>";
array_push($rowData, $rowData_[0]);
// Insert row data array into your database of choice here
}
// echo "<pre>";print_r($rowData);exit;//echo's array
foreach ($rowData as $r_data) {
// echo "<pre>";print_r($r_data);echo "</pre>";
/*
Result array key
0 = name
1 = phone
2 = email
3 = county
4 = subcounty
5 = mfl
*/
$status = 1;
$county_id = $district_id = $facility_code = 0;
$new_county_id = $new_district_id = $new_facility_code = $usertype = null;
$county_name = strtolower($r_data[3]);
//lower case
$county_name = str_replace(" ", "", $county_name);
$county_name = str_replace("-", " ", $county_name);
$county_name = ucwords($county_name);
//upper first character
$district = strtolower($r_data[4]);
$district = ucfirst($district);
$phone = preg_replace('/\\s+/', '', $r_data[1]);
// echo "<pre>";print_r($phone);
// echo "<pre>";print_r($county_name);
$facility_code = !empty($r_data[5]) ? $r_data[5] : NULL;
$name = !empty($r_data[0]) ? $r_data[0] : NULL;
$email = !empty($r_data[2]) ? $r_data[2] : NULL;
$date_uploaded = date('Y-m-d h:i:s');
$fault_index = NULL;
// echo $district;
$query = "SELECT * FROM facilities WHERE facility_code = '{$facility_code}'";
$result = $this->db->query($query)->result_array();
//FACILITY CODE SEARCH
// echo "<pre>";print_r($result);echo "</pre>";
$sql = null;
if (empty($result)) {
//if no facility code then district
$queryy = "SELECT * FROM districts WHERE district = '{$district}'";
$resultt = $this->db->query($queryy)->result_array();
if (empty($resultt)) {
//no district then county
$queryyy = "SELECT * FROM counties WHERE county = '{$county_name}'";
$resulttt = $this->db->query($queryyy)->result_array();
if (empty($resulttt)) {
echo "Empty county,subcounty and facility";
} else {
$county_id = $resulttt[0]['id'];
// echo "<pre>\t Only County ".$phone;
$new_county_id = $county_id;
$usertype = 10;
}
} else {
//if district matches
$district_id = $resultt[0]['id'];
$county_id = $this->get_county_id_for_district($district_id);
// echo "<pre>\t District ".$phone;
$new_county_id = $county_id;
$new_district_id = $district_id;
$usertype = 3;
}
//district name match
} else {
//if facility_code_match
// echo "<pre>";print_r($result);exit;
$district_id = $result[0]['district'];
$county_id = $this->get_county_id_for_district($district_id);
// echo "<pre>\t Facility: ".$phone;
$new_county_id = $county_id;
$new_district_id = $district_id;
$new_facility_code = $facility_code;
$usertype = 5;
// echo "<pre>"; print_r($county_id);exit;
}
/*
//code for appending 254 to phone numbers
if (isset($phone)) {
$phone = preg_replace('/\s+/', '', $phone);
$phone = ltrim($phone, '0');
// echo "<pre>".substr($phone, 0,3);
if (substr($phone, 0,3) != '254') {
$phone = '254'.$phone;
}
}else{
$phone = NULL;
}
*/
$number_length = isset($phone) ? strlen($phone) : 0;
// echo "Number Length: ".$number_length;
if ($number_length != 12) {
if (isset($fault_index)) {
$fault_index = 3;
//both error in phone and district
// $status = 2;
} else {
$fault_index = 2;
}
$fault_index = 2;
//overriding both district and phone error as district is not necessarily necessary
$status = 2;
}
/*commented out insertion below*/
$listing = array();
$listing_data = array('name' => $name, 'email' => $email, 'phone_number' => $phone, 'facility_code' => $new_facility_code, 'sub_county' => $new_district_id, 'county' => $new_county_id, 'usertype' => $usertype, 'date_uploaded' => $date_uploaded, 'status' => '0');
array_push($listing, $listing_data);
// echo "<pre>";print_r($inv);
$similarity_query = "SELECT * FROM email_listing_new WHERE phone_number = '{$phone}'";
$similarity = $this->db->query($similarity_query)->result_array();
//FACILITY CODE SEARCH
if (empty($similarity)) {
$insertion = $this->db->insert_batch('email_listing_new', $listing);
// echo "QUERY SUCCESSFUL. ".$insertion." ".mysql_insert_id()."</br>";
} else {
// echo "<pre> Dab on em";
}
}
// echo "<pre>";print_r($inv);
// unlink($inputFileName);
// echo "QUERY SUCCESSFUL. LAST ID INSERTED: ".mysql_insert_id(); exit;
// redirect(base_url().'admin/report_listing');
// exit;
}
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('*/*/');
}
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>";
}
/**
* The possible scopes for $plugin_param are: 'table', 'database', and 'server'
*/
if (isset($plugin_list)) {
$plugin_list['xlsx'] = array('text' => 'strImportXLSX', 'extension' => 'xlsx', 'options' => array(array('type' => 'bool', 'name' => 'col_names', 'text' => 'strImportColNames')), 'options_text' => 'strOptions');
/* We do not define function when plugin is just queried for information above */
return;
}
ini_set('memory_limit', '256M');
set_time_limit(120);
/* Append the PHPExcel directory to the include path variable */
set_include_path(get_include_path() . PATH_SEPARATOR . getcwd() . '/libraries/PHPExcel/');
require_once './libraries/PHPExcel/PHPExcel.php';
require_once './libraries/PHPExcel/PHPExcel/Reader/Excel2007.php';
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objReader->setLoadAllSheets();
$objPHPExcel = $objReader->load($import_file);
$sheet_names = $objPHPExcel->getSheetNames();
$num_sheets = count($sheet_names);
$tables = array();
$tempRow = array();
$rows = array();
$col_names = array();
for ($s = 0; $s < $num_sheets; ++$s) {
$current_sheet = $objPHPExcel->getSheet($s);
$num_rows = $current_sheet->getHighestRow();
$num_cols = PMA_getColumnNumberFromName($current_sheet->getHighestColumn());
if ($num_rows != 1 && $num_cols != 1) {
for ($r = 1; $r <= $num_rows; ++$r) {
for ($c = 0; $c < $num_cols; ++$c) {
public function upload_recepients($file_name = NULL)
{
// Include PHPExcel_IOFactory
// include 'PHPExcel/IOFactory.php';
// include 'PHPExcel/PHPExcel.php';
$inputFileName = 'uploads/excel/' . $file_name;
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
// echo "<pre>";print_r($inputFileName);exit;
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow() + 1;
$highestColumn = $sheet->getHighestColumn();
// echo "<pre>";print_r($highestRow);echo "</pre>";exit;
$rowData = array();
for ($row = 3; $row < $highestRow; $row++) {
// Read a row of data into an array
$rowData_ = $sheet->rangeToArray('A' . $row . ':H' . $row);
// echo "<pre>";print_r($rowData_);echo "</pre>";
array_push($rowData, $rowData_[0]);
// Insert row data array into your database of choice here
}
// echo "<pre>";print_r($rowData);echo "</pre>";exit;
/*
names
facility_name
mfl
district
id_number
mobile
email
trainingsite
*/
foreach ($rowData as $r_data) {
// echo "<pre>";print_r($r_data);echo "</pre>";
$status = 1;
$district = strtolower($r_data[3]);
$district = ucfirst($district);
$fault_index = NULL;
// echo $district;
$facility_code = $r_data[2];
$query = "SELECT * FROM facilities WHERE facility_code = '{$facility_code}'";
// $query = "SELECT * FROM districts WHERE district = '$district'";
$result = $this->db->query($query)->result_array();
// $district_name = $result[0]['district'];
// echo "<pre>";print_r($result);echo "</pre>";
if (empty($result)) {
$queryy = "SELECT * FROM districts WHERE district = '{$district}'";
$resultt = $this->db->query($queryy)->result_array();
// echo $r_data[0]."</br>";
// $query = "";
if (empty($resultt)) {
$fault_index = 1;
$status = 2;
$district_id = NULL;
} else {
$district_id = $result[0]['id'];
$fault_index = 0;
// echo "<pre>";print_r($resultt); echo "</pre>";
}
} else {
$district_id = $result[0]['district'];
}
$names = $r_data[0];
$phone = $r_data[5];
if (isset($phone)) {
$phone = preg_replace('/\\s+/', '', $phone);
$phone = ltrim($phone, '0');
$phone = '254' . $phone;
} else {
$phone = NULL;
}
$email = $r_data[6];
$number_length = isset($phone) ? strlen($phone) : 0;
// echo $phone;
echo "Number Length: " . $number_length;
if ($number_length != 12) {
if (isset($fault_index)) {
$fault_index = 3;
//both error in phone and district
// $status = 2;
} else {
$fault_index = 2;
}
$status = 2;
}
$sms_status = isset($status) ? $status : 1;
$rec = array();
$rec_data = array('fname' => $names, 'email' => $email, 'phone_no' => $phone, 'sms_status' => $sms_status, 'user_type' => 1, 'category_id' => 1, 'district_id' => $district_id, 'fault_index' => $fault_index);
array_push($rec, $rec_data);
$insertion = $this->db->insert_batch('recepients', $rec);
echo "QUERY SUCCESSFUL. " . $insertion . " " . mysql_insert_id() . "</br>";
}
// };
// echo "QUERY SUCCESSFUL. LAST ID INSERTED: ".mysql_insert_id(); exit;
redirect(base_url() . 'users/recipients');
}
public function upload_redistribution($file_name)
{
$inputFileName = 'print_docs/excel/uploaded_files/' . $file_name;
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow() + 1;
$highestColumn = $sheet->getHighestColumn();
$sending_facility = $sheet->getCell('C8')->getValue();
$facility_code = $this->session->userdata('facility_id');
$current_date = date($format = "Y-m-d h:i:s", PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell('C9')->getValue()));
// echo "$current_date";die;
$today = date('Y-m-d h:i:s');
$rowData = array();
for ($row = 12; $row < $highestRow; $row++) {
// Read a row of data into an array
$rowData_ = $sheet->rangeToArray('B' . $row . ':G' . $row);
array_push($rowData, $rowData_[0]);
}
foreach ($rowData as $r_data) {
$commodity_name = $r_data[0];
$batch_no = $r_data[1];
$manufacturer = $r_data[2];
$expiry_date = date($format = "Y-m-d h:i:s", PHPExcel_Shared_Date::ExcelToPHP($r_data[3]));
// $expiry_date = date($format = "Y-m-d h:i:s", PHPExcel_Shared_Date::ExcelToPHP($expiry_date));
$quantity_packs = $r_data[4];
$quantity_units = $r_data[5];
//Get commodity details from the name
$commodity_details = commodities::get_details_name($commodity_name);
foreach ($commodity_details as $key => $value) {
$commodity_id = $value['id'];
$commodity_code = $value['commodity_code'];
$unit_size = $value['pack_size'];
$commodity_source_id = $value['commodity_source_id'];
//Convert the Packs to Units
$received_quantity = null;
if ($quantity_units != '' || $quantity_units != null) {
$received_quantity = $quantity_units;
} else {
$unit_size = $unit_size == 0 ? 1 : $unit_size;
$received_quantity = $quantity_packs * $unit_size;
}
//Save the Data in a temporary table
$received_data = array(array('facility_code' => $facility_code, 'sending_facility' => $sending_facility, 'commodity_id' => $commodity_id, 'batch_no' => $batch_no, 'manufacturer' => $manufacturer, 'quantity_received' => $received_quantity, 'expiry_date' => $expiry_date, 'date_received' => $current_date, 'status' => 1));
//Save to receive redistributions table
$this->db->insert_batch('receive_redistributions', $received_data);
}
}
$this->session->set_flashdata('system_success_message', "File Upload Successful");
redirect('issues/confirm_external_issue_offline');
}
/**
* Reads the data from file in the DB and generates a wpDataTable
*/
public function readFileData($table_data)
{
$columnTypes = array();
if (!empty($table_data['file'])) {
$xls_url = urldecode($table_data['file']);
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$xls_url = str_replace(site_url(), str_replace('\\', '/', ABSPATH), $xls_url);
} else {
$xls_url = str_replace(site_url(), ABSPATH, $xls_url);
}
} else {
return _('Empty file', 'wpdatatables');
}
for ($i = 0; $i < count($table_data['columns']); $i++) {
if ($table_data['columns'][$i]['orig_header'] == '%%NEW_COLUMN%%') {
$table_data['columns'][$i]['orig_header'] = 'column' . $i;
}
$columnTypes[$table_data['columns'][$i]['orig_header']] = $table_data['columns'][$i]['type'];
}
$this->_id = $this->generateManualTable($table_data);
require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
if (strpos(strtolower($xls_url), '.xlsx')) {
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.xls')) {
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.ods')) {
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.csv')) {
$objReader = new PHPExcel_Reader_CSV();
} else {
return _('File format not supported!', 'wpdatatables');
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$insertArray = array();
// Insert statement default beginning
$insert_statement_beginning = "INSERT INTO " . $this->_name . " (" . implode(', ', array_values($this->_column_headers)) . ") ";
$insert_blocks = array();
for ($row = 2; $row <= $highestRow; ++$row) {
// Set all cells in the row to their defaults
foreach ($table_data['columns'] as $column) {
$insertArray[$this->_column_headers[$column['orig_header']]] = "'" . esc_sql($column['default_value']) . "'";
}
$dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
if (isset($dataRow[$row]['A']) && $dataRow[$row]['A'] > '') {
++$r;
foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
if (!in_array($dataColumnHeading, array_keys($this->_column_headers))) {
continue;
}
if ($columnTypes[$dataColumnHeading] != 'date') {
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . esc_sql($dataRow[$row][$dataColumnIndex]) . "'";
} else {
if ($objReader instanceof PHPExcel_Reader_CSV) {
$date = strtotime(str_replace('/', '-', $dataRow[$row][$dataColumnIndex]));
} else {
$date = esc_sql(PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]));
}
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . date('Y-m-d', $date) . "'";
}
}
}
$insert_blocks[] = '(' . implode(', ', $insertArray) . ')';
if ($row % 100 == 0) {
$this->insertRowsChunk($insert_statement_beginning, $insert_blocks);
$insert_blocks = array();
}
}
$this->insertRowsChunk($insert_statement_beginning, $insert_blocks);
}
public function excelBasedConstruct($xls_url, $wdtParameters = array())
{
ini_set("memory_limit", "2048M");
if (!$xls_url) {
throw new WDTException('Excel file not found!');
}
if (!file_exists($xls_url)) {
throw new WDTException('Provided file ' . stripcslashes($xls_url) . ' does not exist!');
}
require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
if (strpos(strtolower($xls_url), '.xlsx')) {
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.xls')) {
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.ods')) {
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.csv')) {
$objReader = new PHPExcel_Reader_CSV();
} else {
throw new WDTException('File format not supported!');
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
$dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
if (isset($dataRow[$row]['A']) && $dataRow[$row]['A'] > '') {
++$r;
foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
$namedDataArray[$r][$dataColumnHeading] = $dataRow[$row][$dataColumnIndex];
if (WDT_DETECT_DATES_IN_EXCEL) {
$cellID = $dataColumnIndex . $row;
if (PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell($cellID))) {
$namedDataArray[$r][$dataColumnHeading] = PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]);
}
}
}
}
}
$namedDataArray = apply_filters('wpdatatables_filter_excel_array', $namedDataArray, $this->getWpId(), $xls_url);
return $this->arrayBasedConstruct($namedDataArray, $wdtParameters);
}
