[platal.git] / classes / csvimporter.php

<?php
/***************************************************************************
 *  Copyright (C) 2003-2006 Polytechnique.org                              *
 *  http://opensource.polytechnique.org/                                   *
 *                                                                         *
 *  This program is free software; you can redistribute it and/or modify   *
 *  it under the terms of the GNU General Public License as published by   *
 *  the Free Software Foundation; either version 2 of the License, or      *
 *  (at your option) any later version.                                    *
 *                                                                         *
 *  This program is distributed in the hope that it will be useful,        *
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
 *  GNU General Public License for more details.                           *
 *                                                                         *
 *  You should have received a copy of the GNU General Public License      *
 *  along with this program; if not, write to the Free Software            *
 *  Foundation, Inc.,                                                      *
 *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
 ***************************************************************************/

define('CSV_INSERT',  'insert'); // INSERT IGNORE
define('CSV_REPLACE', 'replace'); // REPLACE
define('CSV_UPDATE',  'update'); // INSERT and UPDATE on error

class CSVImporter
{
    private $table;
    private $key;
    private $do_sql;

    private $index;
    private $separator;
    private $data = array();

    private $user_functions = array();
    private $field_desc = array();
    private $field_value = array();

    public function CSVImporter($table, $key = 'id', $do_sql = true)
    {
        $this->table     = $table;
        $this->key       = $key;
        $this->do_sql    = $do_sql;
    }

    private function processLine($line)
    {
        $array = split($this->separator, $line);
        if (is_null($this->index)) {
            $this->index = array_map('strtolower', $array);
            return true;
        }

        if (count($array) != count($this->index)) {
            return false;
        }
        $assoc = array();
        $i     = 0;
        foreach ($this->index as $key) {
            $assoc[$key] = $array[$i];
            $i++;
        }
        $this->data[] = $assoc;
        return true;
    }

    private function getValue($line, $key, $action)
    {
        if (@array_key_exists($action, $line)) {
            $value = $line[$action];
        } elseif (is_callable($action, false)) {
            $value = call_user_func($action, $line, $key);
        } else {
            $value = $action;
        }
        if (is_null($value) || $value == 'NULL') {
            $value = 'NULL';
        }
        return $value;
    }

    private function makeAssoc($line, $relation)
    {
        $ops = array();
        foreach ($relation as $key=>$ref) {
            $ops[$key] = $this->getValue($line, $key, $ref);
        }
        return $ops;
    }

    private function makeRequestArgs($line, $relation)
    {
        $ops = array();
        foreach ($relation as $key=>$ref) {
            $value = $this->getValue($line, $key, $ref);
            if (!is_null($value) && $value != 'NULL') {
                $value = "'" . addslashes($value) . "'";
            }
            $ops[$key] = "$key = $value";
        }
        return $ops;
    }

    private function makeRelation()
    {
        $relation = array();
        foreach ($this->index as $title) {
            $relation[$title] = $title;
        }
        return $relation;
    }

    private function execute($query)
    {
        if (!$this->do_sql) {
            echo "$query;\n";
            return false;
        }
        return XDB::execute($query);
    }

    private function getFieldList()
    {
        $res = XDB::query("SHOW COLUMNS FROM {$this->table}");
        if ($res->numRows()) {
            return $res->fetchColumn();
        }
        return null;
    }

    public function setCSV($csv, $index = null, $separator = ';')
    {
        $this->index     = null;
        $this->separator = $separator;
        $csv   = preg_split("/(\r\n|\r|\n)/", $csv);

        foreach ($csv as $line) {
            $this->processLine($line);
        }
    }

    public function run($action = CSV_UPDATE, $insert_relation = null, $update_relation = null)
    {
        if (is_null($insert_relation)) {
            $insert_relation = $this->makeRelation();
        }
        if (is_null($update_relation)) {
            $update_relation = $insert_relation;
        }
        foreach ($this->data as $line) {
            $set = join(', ', $this->makeRequestArgs($line, $insert_relation));
            switch ($action) {
              case CSV_INSERT:
                $this->execute("INSERT IGNORE INTO {$this->table} SET $set");
                break;
              case CSV_REPLACE:
                $this->execute("REPLACE INTO {$this->table} SET $set");
                break;
              case CSV_UPDATE:
                if (!$this->execute("INSERT INTO {$this->table} SET $set")) {
                    $ops = $this->makeRequestArgs($line, $update_relation);
                    $set = join(', ', $ops);
                    $this->execute("UPDATE {$this->table} SET $set WHERE {$ops[$this->key]}");
                }
                break;
            }
        }
    }

    static public function dynamicCond($line, $key)
    {
        static $fields, $conds, $values, $thens, $elses;

        if (!isset($fields)) {
            $fields = Env::v('csv_cond_field');
            $conds  = Env::v('csv_cond');
            $values = Env::v('csv_cond_value');
            $thens  = Env::v('csv_cond_then');
            $elses  = Env::v('csv_cond_else');
        }
        $field = $line[$fields[$key]];
        $cond  = $conds[$key];
        $value = $values[$key];
        if (is_numeric($field) && is_numeric($value)) {
            $field = floatval($field);
            $value = floatval($value);
        }
        switch ($cond) {
            case 'defined':          $ok = (!empty($field)); break;
            case 'equals':           $ok = ($field == $value); break;
            case 'contains':         $ok = (strpos($field, $value) !== false); break;
            case 'contained':        $ok = (strpos($value, $field) !== false); break;
            case 'greater':          $ok = ($field > $value); break;
            case 'greater_or_equal': $ok ($field >= $value); break;
            case 'lower':            $ok = ($field < $value); break;
            case 'lower_or_equal':   $ok = ($field <= $value); break;
            default:                 $ok = false;
        }
        if ($ok) {
            return $thens[$key];
        } else {
            return $elses[$key];
        }
    }

    public function registerFunction($name, $desc, $callback)
    {
        if (is_callable($callback, false, $ref)) {
            $this->user_functions['func_' . $name] = array('desc' => $desc, 'callback' => $callback);
            return true;
        }
        return false;
    }

    public function describe($name, $desc)
    {
        $this->field_desc[$name] = $desc;
    }

    public function forceValue($name, $value)
    {
        $this->field_value[$name] = $value;
    }

    /** Handle insertion form
     * @param $page  PlatalPage to process
     * @param $url   URI of the page
     * @param $field Editable fields
     */
    public function apply(&$page, $url, $fields = null)
    {
        if (is_null($fields) || empty($fields)) {
            $fields = $this->getFieldList();
        }
        if (is_null($fields)) {
            return false;
        }
        foreach ($this->field_value as $key=>$value) {
            unset($fields[$key]);
        }

        $current = Env::v('csv_page');
        if (empty($current)) {
            $current = 'source';
        }
        $next = Env::v('csv_next_page');
        if (empty($next)) {
            $next = $current;
        }
        $csv  = Env::v('csv');
        if ($current == 'source' && Env::has('csv_valid')) {
            $csv = Env::v('csv_source');
            $next = 'values';
        }
        if ($csv) {
            $this->setCSV($csv);
        }
        if ($current == 'values' && Env::has('csv_valid')) {
            $next = 'valid';
        }    
        if (empty($csv)) {
            $next = 'source';
        }
        if ($next == 'valid') {
            $insert   = Env::v('csv_value');
            $values   = Env::v('csv_user_value');
            $update   = Env::v('csv_update');
            foreach ($insert as $key=>$value) {
                if (empty($value)) {
                    $insert[$key] = null;
                } elseif ($value == 'user_value') {
                    $insert[$key] = $values[$key];
                } elseif ($value == 'cond_value') {
                    $insert[$key] = array($this, 'dynamicCond');
                } elseif (array_key_exists($value, $this->user_functions)) {
                    $insert[$key] = $this->user_functions[$value]['callback'];
                }
                if (isset($update[$key])) {
                    $update[$key] = $insert[$key];
                }
            }
            foreach ($this->field_value as $key=>$value) {
                $insert[$key] = $value;
            }
            if ($current == 'valid' && Env::has('csv_valid')) {
                $this->run(Env::v('csv_action'), $insert, $update);
                $page->assign('csv_done', true);
            } else {
                $preview = array();
                foreach ($this->data as $line) {
                    $preview[] = $this->makeAssoc($line, $insert);
                }
                $page->assign('csv_preview', $preview);
            }
        }
        $page->assign('csv_index', $this->index);
        $page->assign('csv_functions', $this->user_functions);
        $page->assign('csv_field_desc', $this->field_desc);
        $page->assign('csv_page', $next);
        $page->assign('csv_path', $url);
        $page->assign('csv_fields', $fields);  
        $page->assign('csv', $csv);
    }
}

?>
Commit	Line	Data
31874f6f	1	<?php
212aae73	2	/***************************************************************************
	3	* Copyright (C) 2003-2006 Polytechnique.org *
	4	* http://opensource.polytechnique.org/ *
	5	* *
	6	* This program is free software; you can redistribute it and/or modify *
	7	* it under the terms of the GNU General Public License as published by *
	8	* the Free Software Foundation; either version 2 of the License, or *
	9	* (at your option) any later version. *
	10	* *
	11	* This program is distributed in the hope that it will be useful, *
	12	* but WITHOUT ANY WARRANTY; without even the implied warranty of *
	13	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
	14	* GNU General Public License for more details. *
	15	* *
	16	* You should have received a copy of the GNU General Public License *
	17	* along with this program; if not, write to the Free Software *
	18	* Foundation, Inc., *
	19	* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *
	20	***************************************************************************/
31874f6f	21
	22	define('CSV_INSERT', 'insert'); // INSERT IGNORE
	23	define('CSV_REPLACE', 'replace'); // REPLACE
	24	define('CSV_UPDATE', 'update'); // INSERT and UPDATE on error
	25
	26	class CSVImporter
	27	{
	28	private $table;
	29	private $key;
	30	private $do_sql;
	31
	32	private $index;
	33	private $separator;
	34	private $data = array();
	35
	36	private $user_functions = array();
a6613b2e	37	private $field_desc = array();
1f53925a	38	private $field_value = array();
31874f6f	39
	40	public function CSVImporter($table, $key = 'id', $do_sql = true)
	41	{
	42	$this->table = $table;
	43	$this->key = $key;
	44	$this->do_sql = $do_sql;
	45	}
	46
	47	private function processLine($line)
	48	{
	49	$array = split($this->separator, $line);
	50	if (is_null($this->index)) {
1f53925a	51	$this->index = array_map('strtolower', $array);
31874f6f	52	return true;
	53	}
	54
	55	if (count($array) != count($this->index)) {
	56	return false;
	57	}
	58	$assoc = array();
	59	$i = 0;
	60	foreach ($this->index as $key) {
	61	$assoc[$key] = $array[$i];
	62	$i++;
	63	}
	64	$this->data[] = $assoc;
	65	return true;
	66	}
	67
a6613b2e	68	private function getValue($line, $key, $action)
	69	{
	70	if (@array_key_exists($action, $line)) {
	71	$value = $line[$action];
	72	} elseif (is_callable($action, false)) {
	73	$value = call_user_func($action, $line, $key);
	74	} else {
	75	$value = $action;
	76	}
	77	if (is_null($value) \|\| $value == 'NULL') {
	78	$value = 'NULL';
	79	}
	80	return $value;
	81	}
	82
31874f6f	83	private function makeAssoc($line, $relation)
	84	{
	85	$ops = array();
	86	foreach ($relation as $key=>$ref) {
a6613b2e	87	$ops[$key] = $this->getValue($line, $key, $ref);
31874f6f	88	}
	89	return $ops;
	90	}
	91
	92	private function makeRequestArgs($line, $relation)
	93	{
	94	$ops = array();
	95	foreach ($relation as $key=>$ref) {
a6613b2e	96	$value = $this->getValue($line, $key, $ref);
a6613b2e	97	if (!is_null($value) && $value != 'NULL') {
31874f6f	98	$value = "'" . addslashes($value) . "'";
	99	}
	100	$ops[$key] = "$key = $value";
	101	}
	102	return $ops;
	103	}
	104
	105	private function makeRelation()
	106	{
	107	$relation = array();
	108	foreach ($this->index as $title) {
	109	$relation[$title] = $title;
	110	}
	111	return $relation;
	112	}
	113
	114	private function execute($query)
	115	{
	116	if (!$this->do_sql) {
	117	echo "$query;\n";
	118	return false;
	119	}
	120	return XDB::execute($query);
	121	}
	122
	123	private function getFieldList()
	124	{
	125	$res = XDB::query("SHOW COLUMNS FROM {$this->table}");
	126	if ($res->numRows()) {
	127	return $res->fetchColumn();
	128	}
	129	return null;
	130	}
	131
	132	public function setCSV($csv, $index = null, $separator = ';')
	133	{
	134	$this->index = null;
	135	$this->separator = $separator;
	136	$csv = preg_split("/(\r\n\|\r\|\n)/", $csv);
	137
	138	foreach ($csv as $line) {
	139	$this->processLine($line);
	140	}
	141	}
	142
	143	public function run($action = CSV_UPDATE, $insert_relation = null, $update_relation = null)
	144	{
	145	if (is_null($insert_relation)) {
	146	$insert_relation = $this->makeRelation();
	147	}
	148	if (is_null($update_relation)) {
	149	$update_relation = $insert_relation;
	150	}
	151	foreach ($this->data as $line) {
	152	$set = join(', ', $this->makeRequestArgs($line, $insert_relation));
	153	switch ($action) {
	154	case CSV_INSERT:
	155	$this->execute("INSERT IGNORE INTO {$this->table} SET $set");
	156	break;
	157	case CSV_REPLACE:
	158	$this->execute("REPLACE INTO {$this->table} SET $set");
	159	break;
	160	case CSV_UPDATE:
	161	if (!$this->execute("INSERT INTO {$this->table} SET $set")) {
162	$ops = $this->makeRequestArgs($line, $update_relation);
163	$set = join(', ', $ops);
164	$this->execute("UPDATE {$this->table} SET $set WHERE {$ops[$this->key]}");
165	}
166	break;
167	}
168	}
169	}
170
171	static public function dynamicCond($line, $key)
172	{
173	static $fields, $conds, $values, $thens, $elses;
174
175	if (!isset($fields)) {
176	$fields = Env::v('csv_cond_field');
177	$conds = Env::v('csv_cond');
178	$values = Env::v('csv_cond_value');
179	$thens = Env::v('csv_cond_then');
180	$elses = Env::v('csv_cond_else');
181	}
182	$field = $line[$fields[$key]];
183	$cond = $conds[$key];
184	$value = $values[$key];
185	if (is_numeric($field) && is_numeric($value)) {
186	$field = floatval($field);
187	$value = floatval($value);
188	}
189	switch ($cond) {
190	case 'defined': $ok = (!empty($field)); break;
191	case 'equals': $ok = ($field == $value); break;
192	case 'contains': $ok = (strpos($field, $value) !== false); break;
193	case 'contained': $ok = (strpos($value, $field) !== false); break;
194	case 'greater': $ok = ($field > $value); break;
195	case 'greater_or_equal': $ok ($field >= $value); break;
196	case 'lower': $ok = ($field < $value); break;
197	case 'lower_or_equal': $ok = ($field <= $value); break;
198	default: $ok = false;
199	}
200	if ($ok) {
201	return $thens[$key];
202	} else {
203	return $elses[$key];
204	}
205	}
206
207	public function registerFunction($name, $desc, $callback)
208	{
1f53925a	209	if (is_callable($callback, false, $ref)) {
31874f6f	210	$this->user_functions['func_' . $name] = array('desc' => $desc, 'callback' => $callback);
	211	return true;
	212	}
	213	return false;
	214	}
	215
a6613b2e	216	public function describe($name, $desc)
	217	{
	218	$this->field_desc[$name] = $desc;
	219	}
	220
1f53925a	221	public function forceValue($name, $value)
	222	{
	223	$this->field_value[$name] = $value;
	224	}
	225
31874f6f	226	/** Handle insertion form
	227	* @param $page PlatalPage to process
	228	* @param $url URI of the page
	229	* @param $field Editable fields
	230	*/
	231	public function apply(&$page, $url, $fields = null)
	232	{
a6613b2e	233	if (is_null($fields) \|\| empty($fields)) {
31874f6f	234	$fields = $this->getFieldList();
	235	}
	236	if (is_null($fields)) {
	237	return false;
	238	}
1f53925a	239	foreach ($this->field_value as $key=>$value) {
	240	unset($fields[$key]);
	241	}
31874f6f	242
	243	$current = Env::v('csv_page');
	244	if (empty($current)) {
	245	$current = 'source';
	246	}
	247	$next = Env::v('csv_next_page');
	248	if (empty($next)) {
	249	$next = $current;
	250	}
	251	$csv = Env::v('csv');
	252	if ($current == 'source' && Env::has('csv_valid')) {
	253	$csv = Env::v('csv_source');
	254	$next = 'values';
	255	}
	256	if ($csv) {
	257	$this->setCSV($csv);
	258	}
	259	if ($current == 'values' && Env::has('csv_valid')) {
	260	$next = 'valid';
	261	}
	262	if (empty($csv)) {
	263	$next = 'source';
	264	}
	265	if ($next == 'valid') {
	266	$insert = Env::v('csv_value');
	267	$values = Env::v('csv_user_value');
	268	$update = Env::v('csv_update');
	269	foreach ($insert as $key=>$value) {
	270	if (empty($value)) {
	271	$insert[$key] = null;
	272	} elseif ($value == 'user_value') {
	273	$insert[$key] = $values[$key];
	274	} elseif ($value == 'cond_value') {
	275	$insert[$key] = array($this, 'dynamicCond');
	276	} elseif (array_key_exists($value, $this->user_functions)) {
	277	$insert[$key] = $this->user_functions[$value]['callback'];
	278	}
	279	if (isset($update[$key])) {
	280	$update[$key] = $insert[$key];
	281	}
	282	}
1f53925a	283	foreach ($this->field_value as $key=>$value) {
	284	$insert[$key] = $value;
	285	}
31874f6f	286	if ($current == 'valid' && Env::has('csv_valid')) {
	287	$this->run(Env::v('csv_action'), $insert, $update);
	288	$page->assign('csv_done', true);
	289	} else {
	290	$preview = array();
	291	foreach ($this->data as $line) {
	292	$preview[] = $this->makeAssoc($line, $insert);
	293	}
	294	$page->assign('csv_preview', $preview);
	295	}
	296	}
	297	$page->assign('csv_index', $this->index);
1f53925a	298	$page->assign('csv_functions', $this->user_functions);
a6613b2e	299	$page->assign('csv_field_desc', $this->field_desc);
31874f6f	300	$page->assign('csv_page', $next);
	301	$page->assign('csv_path', $url);
	302	$page->assign('csv_fields', $fields);
	303	$page->assign('csv', $csv);
	304	}
	305	}
	306
	307	?>