3
* © Copyright 2007, 2008 IntraHealth International, Inc.
5
* This File is part of iHRIS
7
* iHRIS is free software; you can redistribute it and/or modify
8
* it under the terms of the GNU General Public License as published by
9
* the Free Software Foundation; either version 3 of the License, or
10
* (at your option) any later version.
12
* This program is distributed in the hope that it will be useful,
13
* but WITHOUT ANY WARRANTY; without even the implied warranty of
14
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15
* GNU General Public License for more details.
17
* You should have received a copy of the GNU General Public License
18
* along with this program. If not, see <http://www.gnu.org/licenses/>.
23
* This page loads the main HTML template for the home page of the site.
25
* @subpackage DemoManage
27
* @author Carl Leitner <litlfred@ibiblio.org>
28
* @copyright Copyright © 2007, 2008 IntraHealth International, Inc.
36
* setting system wide variables just like in index.php
41
$i2ce_site_user_access_init = null;
42
$i2ce_site_user_database = null;
43
require_once( getcwd() . DIRECTORY_SEPARATOR . 'config.values.php');
45
$local_config = getcwd() . DIRECTORY_SEPARATOR .'local' . DIRECTORY_SEPARATOR . 'config.values.php';
46
if (file_exists($local_config)) {
47
require_once($local_config);
50
if(!isset($i2ce_site_i2ce_path) || !is_dir($i2ce_site_i2ce_path)) {
51
echo "Please set the \$i2ce_site_i2ce_path in $local_config";
55
require_once ($i2ce_site_i2ce_path . DIRECTORY_SEPARATOR . 'I2CE_config.inc.php');
57
I2CE::raiseMessage("Connecting to DB");
59
if (isset($i2ce_site_dsn)) {
60
@I2CE::initializeDSN($i2ce_site_dsn, $i2ce_site_user_access_init, $i2ce_site_module_config);
61
} else if (isset($i2ce_site_database_user)) {
62
I2CE::initialize($i2ce_site_database_user,
63
$i2ce_site_database_password,
65
$i2ce_site_user_database,
66
$i2ce_site_module_config
69
die("Do not know how to configure system\n");
72
I2CE::raiseMessage("Connected to DB");
74
require_once($i2ce_site_i2ce_path . DIRECTORY_SEPARATOR . 'tools' . DIRECTORY_SEPARATOR . 'CLI.php');
78
/*************************************************************************
80
* Classes to handle reading headers and rows from data files
82
************************************************************************/
85
abstract class DataFile {
88
* @var protected string $file
92
abstract public function getDataRow();
93
abstract public function hasDataRow();
94
abstract public function getHeaders();
95
public function __construct($file) {
100
* get the file name for the file we are going to deal with
103
public function getFileName() {
108
* closes a file that was open
110
public function close() {
116
class CSVDataFile extends DataFile {
118
protected $in_file_sep = false;
119
protected $file_size = false;
120
public function __construct($file) {
121
parent::__construct($file);
122
$this->filesize = filesize($file);
123
if ( ($this->fp = fopen($this->file,"r")) === false) {
124
usage("Please specify the name of a spreadsheet to import: " . $file . " is not openable");
129
* checks to confirm if the file has rows of data
132
public function hasDataRow() {
133
$currpos = ftell($this->fp);
134
if ($currpos === false) {
137
return ($currpos < $this->filesize);
142
* reads all the column headers from the CSV file
145
public function getHeaders() {
146
$this->in_file_sep = false;
148
foreach (array("\t",",",";") as $sep) {
149
$headers = fgetcsv($this->fp, 4000, $sep);
150
if ( $headers === FALSE|| count($headers) < 2 || !simple_prompt("Do these look like the correct headers?\n". print_r($headers,true))) {
154
$this->in_file_sep = $sep;
157
if (!$this->in_file_sep) {
158
die("Could not get headers\n");
160
foreach ($headers as &$header) {
161
$header = trim($header);
167
public function getDataRow() {
168
return $data = fgetcsv($this->fp, 4000, $this->in_file_sep);
172
* closes the open CSV file
174
public function close() {
180
class ExcelDataFile extends DataFile {
182
protected $rowIterator;
184
public function __construct($file) {
185
parent::__construct($file);
186
include_once('PHPExcel/PHPExcel.php');
187
if (!class_exists('PHPExcel',false)) {
188
usage("You must have PHPExcel installed to load excel spreadsheets");
190
$readerType = PHPExcel_IOFactory::identify($this->file);
191
$reader = PHPExcel_IOFactory::createReader($readerType);
192
$reader->setReadDataOnly(false);
193
$excel = $reader->load($this->file);
194
$worksheet = $excel->getActiveSheet();
195
$this->rowIterator = $worksheet->getRowIterator();
200
* confirms if the excel file we are reading has rows with data
203
public function hasDataRow() {
204
return $this->rowIterator->valid();
208
* reads the file to get the headers
211
public function getHeaders() {
212
$this->rowIterator->rewind();
213
$row = $this->rowIterator->current();
214
if (!$this->rowIterator->valid()) {
215
I2CE::raiseMessage("Could not find header row");
218
return $this->_readRow($row);
222
* reads one data row at a time
225
public function getDataRow() {
226
$this->rowIterator->next();
227
if (!$this->rowIterator->valid()) {
230
return $this->_readRow($this->rowIterator->current());
234
* read the entire row and parse for data
235
* @param string $row. If not an excel worksheet row, issue a message and return false
238
protected function _readRow($row) {
239
if (!$row instanceof PHPExcel_Worksheet_Row) {
240
I2CE::raiseMessage("Invalid row object" . get_class($row));
243
$cellIterator = $row->getCellIterator();
244
$cellIterator->setIterateOnlyExistingCells(false);
246
foreach ($cellIterator as $cell) {
247
$data[] = $cell->getValue();
257
/*********************************************
261
*********************************************/
263
function convert($size)
265
$unit=array('b','kb','mb','gb','tb','pb');
266
return @round($size/pow(1024,($i=floor(log($size,1024)))),2).' '.$unit[$i];
271
abstract class Processor {
272
abstract protected function _processRow();
273
abstract protected function getExpectedHeaders();
275
protected $mapped_data;
278
protected $testmode = true;
282
protected $processRows =null;
286
public function __construct($file) {
288
$file_ext = strtolower(substr($file, strrpos($file, '.') + 1));
289
if ($file_ext == 'csv') {
290
//although CSV can be processed by PHPExcel, we keep this separate in case PHPExcel cannot be installed, we can still export the file as a CSV and process it
291
$this->dataFile = new CSVDataFile($file);
293
$this->dataFile = new ExcelDataFile($file);
295
$this->user = new I2CE_User();
296
$this->db= MDB2::singleton();
297
$this->ff = I2CE_FormFactory::instance();
299
$this->initBadFile();
300
$this->testmode = simple_prompt("Is this a test run?");
301
$this->createLogger();
305
* read the current row
308
public function getCurrentRow() {
313
* check if the file has data
316
public function hasDataRow() {
317
return $this->dataFile->hasDataRow();
321
protected $success = 0;
323
protected $blank_lines = 0;
326
* process the import giving the user choices to run in test mode or in production mode
327
* every unsuccessful processing for a row is recorded into a log file
329
public function run() {
330
if (simple_prompt("Skip rows?")) {
331
$this->skip = ask("Skip to which row? Start row =2 (b/c row 1 is header)");
334
while ( $this->hasDataRow()) {
335
if ($this->blank_lines > 10) {
336
if (simple_prompt("Encounted 10 consective blank rows ending at row " . $this->row . ". Should we stop processing?")) {
339
$this->blank_lines = 0;
342
if ($this->processRow()) {
344
if ($this->testmode) {
345
//$this->addBadRecord("SUCCESS ON TEST");
352
* collect statistics for the import process:total attempts and successes
355
public function getStats() {
356
return array('success'=>$this->success,'attempts'=>($this->row -1)); //this may be off by one.
357
$row = $processor->getCurrentRow();
361
* processes each row encountered from the file
364
public function processRow() {
365
if (!$this->dataFile->hasDataRow()) {
368
//starts off with $row= 1, but we do a row++ below to get to row 2
369
$this->data = $this->dataFile->getDataRow();
370
if ($this->row < ($this->skip - 1)) { //if skip =2, the default, then 1 < (2-1) is false so we don't skup
374
if (!is_array($this->data)) {
375
$this->blank_lines++;
379
foreach ($this->data as $cell) {
380
if (is_string($cell) && strlen(trim($cell)) != 0) {
382
$this->blank_lines = 0;
387
$this->blank_lines++;
390
if ( ! ($this->mapped_data = $this->mapData())) {
393
if (($hash_val = $this->alreadyProcessed()) === true) {
396
if (!prompt("Process row $this->row ?",$this->processRows,print_r(array('mapped'=>$this->mapped_data,'raw'=>$this->data),true))) {
399
if ( $this->_processRow()) {
400
$this->markProcessed($hash_val);
409
* sets up the mode to run the script: test or production
410
* @param string $testmode defaults yes
412
public function setTestMode($testmode) {
413
$this->testmode = $testmode;
416
protected $continue_save = null;
419
* save the data into the database
420
* @param string $obj, the form object,
421
* @param bool $cleanup defaults
424
protected function save($obj,$cleanup = true) {
425
if (!$obj instanceof I2CE_Form) {
428
echo "Row {$this->row}, Form " . $obj->getName() . " Used Memory = " . convert(memory_get_usage(true)) . "\n";
429
if ($this->testmode) {
430
echo "Saving " . $obj->getName() . "\n";
431
foreach ($obj as $fieldName=>$fieldObj) {
432
echo "\t$fieldName=>" . $fieldObj->getDBValue() . "\n";
439
$obj->save($this->user);
444
prompt("Saved id $id. Continue?",$this->continue_save);
451
protected $header_map;
455
function getHeaderMap(&$headers,$expected_headers) {
456
foreach ($headers as &$header) {
457
$header = strtolower(trim($header));
461
$header_map = array();
462
foreach ($expected_headers as $expected_header_ref => $expected_header) {
463
$expected_header = strtolower(trim($expected_header));
464
if (($header_col = array_search($expected_header,$headers)) === false) {
465
I2CE::raiseError("Could not find $expected_header in the following headers:\n\t" . implode(" ", $headers). "\nFull list of found headers is:\n\t" . implode(" ", $headers));
468
$header_map[$expected_header_ref] = $header_col;
475
* map expected header references to header columns in the data file
477
protected function mapHeaders() {
478
$this->headers = $this->dataFile->getHeaders();
479
$expected_headers = $this->getExpectedHeaders();
480
$this->header_map = $this->getHeaderMap($this->headers,$expected_headers);
486
* Utility function to remap data in an associative array to an associative array based on a map.
487
* @param array $data the source data
488
* @param array $header_map, the mapping array of data. Keys are keys for the source data. Values will be the keys for the mapped data
489
* @param boolean $normalize. Defaults to false. If true we trim and upcase the data that is being remapped
490
* @returns array, data mapped
492
function mapByHeaderData($data,$header_map,$normalize = false) {
493
$mapped_data = array();
494
foreach ($header_map as $header_ref=>$header_col) {
495
if (!array_key_exists($header_col,$data)) {
496
$mapped_data[$header_ref] = false;
497
} else if ($normalize) {
498
$mapped_data[$header_ref] = strtoupper(trim($data[$header_col]));
500
$mapped_data[$header_ref] = $data[$header_col];
509
* map the data found from a column in the data file to a specific
510
* header reference for saving into the database
511
* @returns array, data mapped into header references
513
protected function mapData() {
514
return $this->mapByHeaderData($this->data,$this->header_map);
519
protected $bad_headers;
521
protected $bad_file_name;
524
* initialize a file into which we record all the bad data/unsuccessful row imports
527
protected function initBadFile() {
528
$info = pathinfo($this->file);
530
if ($this->testmode) {
531
$append = 'test_bad_';
535
$this->bad_file_name = dirname($this->file) . DIRECTORY_SEPARATOR . basename($this->file,'.'.$info['extension']) . '.' . $append .date('d-m-Y_G:i') .'.csv';
536
I2CE::raiseMessage("Will put any bad records in $this->bad_file_name");
537
$this->bad_headers = $this->headers;
538
$this->bad_headers[] = "Row In " . basename($this->file);
539
$this->bad_headers[] = "Reasons for failure";
545
* add a bad record to the file holding all unsuccessful imports
546
* @param string $reason, the reason for the failure of import of this record
548
function addBadRecord($reason) {
549
if (!is_resource($this->bad_fp)) {
550
$this->bad_fp = fopen($this->bad_file_name,"w");
551
if (!is_resource($this->bad_fp)) {
552
I2CE::raiseMessage("Could not open $this->bad_file_name for writing.", E_USER_ERROR);
555
fputcsv($this->bad_fp, $this->bad_headers);
557
I2CE::raiseMessage("Skipping processing of row $this->row: $reason");
558
$raw_data = $this->data;
559
$raw_data[] = $this->row;
560
$raw_data[] = $reason;
561
fputcsv($this->bad_fp, $raw_data);
563
protected $log_table;
566
* creating a logger table in the database to track every import process timing
569
protected function createLogger() {
570
$this->log_table = 'import_logger_' . get_class($this);
571
$sql = "CREATE TABLE IF NOT EXISTS `" . $this->log_table . "` (`hash` BINARY(16) NOT NULL, UNIQUE KEY `hash` (`hash`))";
572
if (I2CE::pearError($this->db->exec($sql),"Cannot create logging table", E_USER_ERROR)) {
578
* checks to see if a row from the data file has been processed
579
* @returns string $hash_val. value of the hash from logger table
581
protected function alreadyProcessed() {
583
$expected_headers = $this->getExpectedHeaders();
584
foreach (array_keys($expected_headers) as $header_ref) {
585
$hash_data .= $this->mapped_data[$header_ref];
587
if (strlen($hash_data) == 0) {
588
I2CE::raiseError("No data");
591
$hash_val = md5($hash_data,true);
592
if (!is_string($hash_val) || strlen($hash_val) == 0) {
593
die("bad has val:\n" . print_r($this->mapped_data,true) . "\n$hash_data\n" . self::raw2hex($hash_val) );
595
$esc_hash_val = mysql_real_escape_string($hash_val);
596
$hash_check = 'SELECT hash FROM `'. $this->log_table . '` WHERE hash = \''. $esc_hash_val .'\'';
597
if (I2CE::pearError(($res = $this->db->query($hash_check)),"Cannot check value in logging table", E_USER_ERROR)) {
600
if ( $res->fetchRow()) {
601
$this->addBadRecord('Row has already been processed');
608
public static function raw2hex($s) {
609
//thanks to: functionifelse at gmail dot com 09-Dec-2004 10:34 on http://theserverpages.com/php/manual/en/function.md5.php
611
for($i = 0; $i < strlen($s); $i++){
612
$op .= str_pad(dechex(ord($s[$i])),2,"0",STR_PAD_LEFT);
618
* marks every record that is processed inserting its $hash_val to the logger table
619
* @param string $hash_val. the hash value generated from the import process
621
protected function markProcessed($hash_val) {
622
if ($this->testmode) {
625
$esc_hash_val = mysql_real_escape_string($hash_val);
626
$hash_insert = 'INSERT into `' . $this->log_table . '` VALUES (\''. $esc_hash_val .'\')';
627
if (I2CE::pearError($this->db->exec($hash_insert),"Cannot add value " . self::raw2hex($hash_val). "to logging table from\n" . print_r($this->mapped_data,true) . "\n" . strlen($hash_val), E_USER_ERROR)) {
635
* gets a date value from records read from the datafile
636
* @param date $date, a date value as read from the data file
637
* @returns date. formatted
639
protected function getDate($date,$date_format = 'm/d/Y' , $excel_date_format = 'DD/MM/YYYY') {
640
//first check the date e.g 16/05/2011
642
if (is_numeric($date) && class_exists('PHPExcel',false)) {
643
//in case we are reading it from excel which returns 40777 instead of 22/08/2011 for example
644
$date = PHPExcel_Style_NumberFormat::toFormattedString($date, $excel_date_format);
646
if (($datetime = DateTime::createFromFormat($date_format,$date)) === false) {
647
$this->addBadRecord("Bad date format [$date] for $date_format");
650
$date = I2CE_Date::now(I2CE_Date::DATE, $datetime->getTimestamp(),true);
652
$this->addBadRecord("Invalid date ($date)");
659
* gets a year value from records read from the datafile
660
* @param date $date, a date value as read from the data file
661
* @returns date. formatted
663
protected function getYear($date,$year_format = 'Y' , $excel_date_format = 'YYYY') {
664
//first check the date e.g 16/05/2011
666
if (is_numeric($date) && class_exists('PHPExcel',false)) {
667
//in case we are reading it from excel which returns 40777 instead of 22/08/2011 for example
668
$date = PHPExcel_Style_NumberFormat::toFormattedString($date, $excel_date_format);
670
if (($datetime = DateTime::createFromFormat($year_format,$date)) === false) {
671
$this->addBadRecord("Bad date format [$date] for $date_format");
674
$date = I2CE_Date::now(I2CE_Date::DATE, $datetime->getTimestamp(),true);
676
$this->addBadRecord("Invalid date ($date)");
691
# c-default-style: "bsd"
692
# indent-tabs-mode: nil