Revision e2903b0d
Von Holger Lindemann vor fast 17 Jahren hinzugefügt
| lxo-import/db.php | ||
|---|---|---|
|
<?
|
||
|
require_once "DB.php";
|
||
|
class myDB extends DB {
|
||
|
|
||
|
var $db = false;
|
||
|
var $rc = false;
|
||
|
var $showErr = false; // Browserausgabe
|
||
|
var $debug = false; // 1 = SQL-Ausgabe, 2 = zusätzlich Ergebnis
|
||
|
var $log = true; // Alle Abfragen mitloggen
|
||
|
var $errfile = "tmp/lxcrm.err";
|
||
|
var $logfile = "tmp/lxcrm.log";
|
||
|
var $lfh = false;
|
||
|
|
||
|
function dbFehler($sql,$err) {
|
||
|
$efh=fopen($this->errfile,"a");
|
||
|
fputs($efh,date("Y-m-d H:i:s ->"));
|
||
|
fputs($efh,$sql."\n");
|
||
|
fputs($efh,$err."\n");
|
||
|
fputs($efh,print_r($this->rc,true));
|
||
|
fputs($efh,"\n");
|
||
|
fclose($efh);
|
||
|
if ($this->showErr)
|
||
|
echo "</td></tr></table><font color='red'>$sql : $err</font><br>";
|
||
|
}
|
||
|
|
||
|
function showDebug($sql) {
|
||
|
echo $sql."<br>";
|
||
|
if ($this->debug==2) {
|
||
|
echo "<pre>";
|
||
|
print_r($this->rc);
|
||
|
echo "</pre>";
|
||
|
};
|
||
|
}
|
||
|
|
||
|
function writeLog($txt) {
|
||
|
if ($this->lfh===false)
|
||
|
$this->lfh=fopen($this->logfile,"a");
|
||
|
fputs($this->lfh,date("Y-m-d H:i:s ->"));
|
||
|
fputs($this->lfh,$txt."\n");
|
||
|
fputs($this->lfh,print_r($this->rc,true));
|
||
|
fputs($this->lfh,"\n");
|
||
|
}
|
||
|
|
||
|
function closeLogfile() {
|
||
|
fclose($this->lfh);
|
||
|
}
|
||
|
|
||
|
function myDB($host,$user,$pwd,$db,$port,$showErr=false) {
|
||
|
$dsn = array(
|
||
|
'phptype' => 'pgsql',
|
||
|
'username' => $user,
|
||
|
'password' => $pwd,
|
||
|
'hostspec' => $host,
|
||
|
'database' => $db,
|
||
|
'port' => $port
|
||
|
);
|
||
|
$this->showErr=$showErr;
|
||
|
$this->db=DB::connect($dsn);
|
||
|
if (!$this->db || DB::isError($this->db)) {
|
||
|
if ($this->log) $this->writeLog("Connect $dns");
|
||
|
$this->dbFehler("Connect ".print_r($dsn,true),$this->db->getMessage());
|
||
|
die ($this->db->getMessage());
|
||
|
}
|
||
|
if ($this->log) $this->writeLog("Connect: ok ");
|
||
|
return $this->db;
|
||
|
}
|
||
|
|
||
|
function query($sql) {
|
||
|
$this->rc=@$this->db->query($sql);
|
||
|
if ($this->debug) $this->showDebug($sql);
|
||
|
if ($this->log) $this->writeLog($sql);
|
||
|
if(DB::isError($this->rc)) {
|
||
|
$this->dbFehler($sql,$this->rc->getMessage());
|
||
|
$this->rollback();
|
||
|
return false;
|
||
|
} else {
|
||
|
return $this->rc;
|
||
|
}
|
||
|
}
|
||
|
|
||
|
function begin() {
|
||
|
$this->query("BEGIN");
|
||
|
}
|
||
|
function commit() {
|
||
|
$this->query("COMMIT");
|
||
|
}
|
||
|
function rollback() {
|
||
|
$this->query("ROLLBACK");
|
||
|
}
|
||
|
|
||
|
function getAll($sql) {
|
||
|
$this->rc=$this->db->getAll($sql,DB_FETCHMODE_ASSOC);
|
||
|
if ($this->debug) $this->showDebug($sql);
|
||
|
if ($this->log) $this->writeLog($sql);
|
||
|
if(DB::isError($this->rc)) {
|
||
|
$this->dbFehler($sql,$this->rc->getMessage());
|
||
|
return false;
|
||
|
} else {
|
||
|
return $this->rc;
|
||
|
}
|
||
|
}
|
||
|
|
||
|
function saveData($txt) {
|
||
|
if (get_magic_quotes_gpc()) {
|
||
|
return $txt;
|
||
|
} else {
|
||
|
return DB::quoteSmart($string);
|
||
|
}
|
||
|
}
|
||
|
|
||
|
function chkcol($tbl) {
|
||
|
// gibt es die Spalte import schon?
|
||
|
$rc=$this->db->query("select import from $tbl limit 1");
|
||
|
if(DB::isError($rc)) {
|
||
|
$rc=$this->db->query("alter table $tbl add column import int4");
|
||
|
if(DB::isError($rc)) { return false; }
|
||
|
else { return true; }
|
||
|
} else { return true; };
|
||
|
}
|
||
|
|
||
|
|
||
|
}
|
||
|
?>
|
||
| lxo-import/import_lib.php | ||
|---|---|---|
|
|
||
|
*/
|
||
|
|
||
|
require_once "DB.php";
|
||
|
require_once "db.php";
|
||
|
|
||
|
$address = array(
|
||
|
"name" => "Firmenname",
|
||
| ... | ... | |
|
"weight" => "Gewicht in Benutzerdefinition",
|
||
|
"onhand" => "Lagerbestand",
|
||
|
"notes" => "Beschreibung",
|
||
|
"notes1" => "Beschreibung",
|
||
|
//"makemodel" => "Hersteller",
|
||
|
//"model" => "Modellbezeichnung",
|
||
|
"bin" => "Lagerort",
|
||
| ... | ... | |
|
"assembly" => "St?ckliste (Y/N); wird noch nicht unterst?tzt",
|
||
|
"partsgroup" => "Warengruppenbezeichnung",
|
||
|
"partsgroup1" => "2.Warengruppenbezeichnung",
|
||
|
"partsgroup2" => "3.Warengruppenbezeichnung",
|
||
|
"partsgroup3" => "4.Warengruppenbezeichnung",
|
||
|
"partsgroup4" => "5.Warengruppenbezeichnung",
|
||
|
//"income_accno_0" => "?Nummer? f?r Erl?se Inland",
|
||
|
//"income_accno_1" => "?Nummer? f?r Erl?se EG",
|
||
|
//"income_accno_3" => "?Nummer? f?r Erl?se Ausland",
|
||
| ... | ... | |
|
}
|
||
|
|
||
|
function chkUsr($usr) {
|
||
|
// ist es ein gültiger ERP-Benutzer? Er muß mindestens 1 x angemeldet gewesen sein.
|
||
|
// ist es ein g?ltiger ERP-Benutzer? Er mu? mindestens 1 x angemeldet gewesen sein.
|
||
|
global $db;
|
||
|
$sql="select * from employee where login = '$usr'";
|
||
|
$rs=$db->getAll($sql);
|
||
| ... | ... | |
|
$rs=$db->getAll($sql);
|
||
|
return $rs;
|
||
|
}
|
||
|
function anmelden() {
|
||
|
ini_set("gc_maxlifetime","3600");
|
||
|
$tmp = @file_get_contents("../config/authentication.pl");
|
||
|
preg_match("/'db'[ ]*=> '(.+)'/",$tmp,$hits);
|
||
|
$dbname=$hits[1];
|
||
|
preg_match("/'password'[ ]*=> '(.+)'/",$tmp,$hits);
|
||
|
$dbpasswd=$hits[1];
|
||
|
preg_match("/'user'[ ]*=> '(.+)'/",$tmp,$hits);
|
||
|
$dbuser=$hits[1];
|
||
|
preg_match("/'host'[ ]*=> '(.+)'/",$tmp,$hits);
|
||
|
$dbhost=($hits[1])?$hits[1]:"localhost";
|
||
|
preg_match("/'port'[ ]*=> '(.+)'/",$tmp,$hits);
|
||
|
$dbport=($hits[1])?$hits[1]:"5432";
|
||
|
preg_match("/^[ ]*\$self->\{cookie_name\}[ ]*=[ ]*'(.+)'/",$tmp,$hits);
|
||
|
$cookiename=$hits[1];
|
||
|
if (!$cookiename) $cookiename='lx_office_erp_session_id';
|
||
|
$cookie=$_COOKIE[$cookiename];
|
||
|
if (!$cookie) header("location: ups.html");
|
||
|
$auth=authuser($dbhost,$dbport,$dbuser,$dbpasswd,$dbname,$cookie);
|
||
|
if (!$auth) { return false; };
|
||
|
$_SESSION["sessid"]=$cookie;
|
||
|
$_SESSION["cookie"]=$cookiename;
|
||
|
$_SESSION["employee"]=$auth["login"];
|
||
|
$_SESSION["mansel"]=$auth["dbname"];
|
||
|
$_SESSION["dbname"]=$auth["dbname"];
|
||
|
$_SESSION["dbhost"]=(!$auth["dbhost"])?"localhost":$auth["dbhost"];
|
||
|
$_SESSION["dbport"]=(!$auth["dbport"])?"5432":$auth["dbport"];
|
||
|
$_SESSION["dbuser"]=$auth["dbuser"];
|
||
|
$_SESSION["dbpasswd"]=$auth["dbpasswd"];
|
||
|
$_SESSION["db"]=new myDB($_SESSION["dbhost"],$_SESSION["dbuser"],$_SESSION["dbpasswd"],$_SESSION["dbname"],$_SESSION["dbport"],$showErr);
|
||
|
$_SESSION["authcookie"]=$authcookie;
|
||
|
$sql="select * from employee where login='".$auth["login"]."'";
|
||
|
$rs=$_SESSION["db"]->getAll($sql);
|
||
|
if(!$rs) {
|
||
|
return false;
|
||
|
} else {
|
||
|
if ($rs) {
|
||
|
$tmp=$rs[0];
|
||
|
$_SESSION["termbegin"]=(($tmp["termbegin"]>=0)?$tmp["termbegin"]:8);
|
||
|
$_SESSION["termend"]=($tmp["termend"])?$tmp["termend"]:19;
|
||
|
$_SESSION["Pre"]=$tmp["pre"];
|
||
|
$_SESSION["interv"]=($tmp["interv"]>0)?$tmp["interv"]:60;
|
||
|
$_SESSION["loginCRM"]=$tmp["id"];
|
||
|
$_SESSION["lang"]=$tmp["countrycode"]; //"de";
|
||
|
$_SESSION["kdview"]=$tmp["kdview"];
|
||
|
$sql="select * from defaults";
|
||
|
$rs=$_SESSION["db"]->getAll($sql);
|
||
|
$_SESSION["ERPver"]=$rs[0]["version"];
|
||
|
return true;
|
||
|
} else {
|
||
|
return false;
|
||
|
}
|
||
|
}
|
||
|
}
|
||
|
|
||
|
class myDB extends DB {
|
||
|
// Datenbankklasse
|
||
|
|
||
|
var $rc = false;
|
||
|
var $showErr = false;
|
||
|
var $db = false;
|
||
|
var $debug = false;
|
||
|
var $logsql = false;
|
||
|
var $errfile = false;
|
||
|
var $logfile = false;
|
||
|
|
||
|
|
||
|
/****************************************************
|
||
|
* uudecode
|
||
|
* in: string
|
||
|
* out: string
|
||
|
* dekodiert Perl-UU-kodierte Passwort-Strings
|
||
|
* http://de3.php.net/base64_decode (bug #171)
|
||
|
*****************************************************/
|
||
|
function uudecode($encode) {
|
||
|
$encode=stripslashes($encode);
|
||
|
$b64chars="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
|
||
|
|
||
|
$encode = preg_replace("/^./m","",$encode);
|
||
|
$encode = preg_replace("/\n/m","",$encode);
|
||
|
for($i=0; $i<strlen($encode); $i++) {
|
||
|
if ($encode[$i] == '')
|
||
|
$encode[$i] = ' ';
|
||
|
$encode[$i] = $b64chars[ord($encode[$i])-32];
|
||
|
}
|
||
|
|
||
|
while(strlen($encode) % 4)
|
||
|
$encode .= "=";
|
||
|
|
||
|
return base64_decode($encode);
|
||
|
}
|
||
|
|
||
|
function dbFehler($sql,$err) {
|
||
|
if ($this->showErr)
|
||
|
echo "$sql : $err\n";
|
||
|
}
|
||
|
|
||
|
function showDebug($sql) {
|
||
|
echo $sql."\n";
|
||
|
if ($this->debug==2) {
|
||
|
print_r($this->rc);
|
||
|
};
|
||
|
}
|
||
|
|
||
|
function logSql($sql) {
|
||
|
if (!$this->logfile) $this->logfile=fopen("import.sql","a");
|
||
|
fputs($this->logfile,$sql."\n");
|
||
|
}
|
||
|
function myDB($usr) {
|
||
|
// Datenbankparameter des ERP-Users benutzen.
|
||
|
$tmp = file_get_contents("../users/$usr.conf");
|
||
|
preg_match("/dbname => '(.+)'/",$tmp,$hits);
|
||
|
$dbname=$hits[1];
|
||
|
preg_match("/dbpasswd => '(.+)'/",$tmp,$hits);
|
||
|
if ($hits[1]) {
|
||
|
$dbpasswd=$this->uudecode($hits[1]);
|
||
|
} else {
|
||
|
$dbpasswd="";
|
||
|
};
|
||
|
preg_match("/dbuser => '(.+)'/",$tmp,$hits);
|
||
|
$dbuser=$hits[1];
|
||
|
preg_match("/dbhost => '(.+)'/",$tmp,$hits);
|
||
|
$dbhost=$hits[1];
|
||
|
if (!$dbhost) $dbhost="localhost";
|
||
|
if ($dbpasswd) {
|
||
|
$dns=$dbuser.":".$dbpasswd."@".$dbhost."/".$dbname;
|
||
|
} else {
|
||
|
$dns=$dbuser."@".$dbhost."/".$dbname;
|
||
|
};
|
||
|
$dns="pgsql://".$dns;
|
||
|
$this->db=DB::connect($dns);
|
||
|
if (!$this->db) DB::dbFehler("oh oh oh",$this->db->getDebugInfo());
|
||
|
if (DB::isError($this->db)) {
|
||
|
$this->dbFehler("Connect",$this->db->getDebugInfo());
|
||
|
die ($this->db->getDebugInfo());
|
||
|
}
|
||
|
return $this->db;
|
||
|
}
|
||
|
|
||
|
function query($sql) {
|
||
|
$this->rc=@$this->db->query($sql);
|
||
|
if ($this->logsql) $this->logSql($sql);
|
||
|
if ($this->debug) $this->showDebug($sql);
|
||
|
if(DB::isError($this->rc)) {
|
||
|
$this->dbFehler($sql,$this->rc->getMessage());
|
||
|
return false;
|
||
|
} else {
|
||
|
return $this->rc;
|
||
|
}
|
||
|
}
|
||
|
function getAll($sql) {
|
||
|
$this->rc=@$this->db->getAll($sql,DB_FETCHMODE_ASSOC);
|
||
|
if ($this->logsql) $this->logSql($sql);
|
||
|
if ($this->debug) $this->showDebug($sql);
|
||
|
if(DB::isError($this->rc)) {
|
||
|
$this->dbFehler($sql,$this->rc->getMessage());
|
||
|
return false;
|
||
|
} else {
|
||
|
return $this->rc;
|
||
|
}
|
||
|
}
|
||
|
|
||
|
function lock() {
|
||
|
$this->query("BEGIN");
|
||
|
}
|
||
|
function commit() {
|
||
|
$this->query("COMMIT");
|
||
|
}
|
||
|
function rollback() {
|
||
|
$this->query("ROLLBACK");
|
||
|
}
|
||
|
function chkcol($tbl) {
|
||
|
// gibt es die Spalte import schon?
|
||
|
$rc=$this->db->query("select import from $tbl limit 1");
|
||
|
if(DB::isError($rc)) {
|
||
|
$rc=$this->db->query("alter table $tbl add column import int4");
|
||
|
if(DB::isError($rc)) { return false; }
|
||
|
else { return true; }
|
||
|
|
||
|
} else { return true; };
|
||
|
}
|
||
|
function authuser($dbhost,$dbport,$dbuser,$dbpasswd,$dbname,$cookie) {
|
||
|
$db=new myDB($dbhost,$dbuser,$dbpasswd,$dbname,$dbport,true);
|
||
|
$sql="select sc.session_id,u.id from auth.session_content sc left join auth.user u on ";
|
||
|
$sql.="u.login=sc.sess_value left join auth.session s on s.id=sc.session_id ";
|
||
|
$sql.="where session_id = '$cookie' and sc.sess_key='login'";// order by s.mtime desc";
|
||
|
$rs=$db->getAll($sql,"authuser_1");
|
||
|
if (!$rs) return false;
|
||
|
$stmp="";
|
||
|
if (count($rs)>1) {
|
||
|
header("location:../login.pl?action=logout");
|
||
|
/*foreach($rs as $row) {
|
||
|
$stmp.=$row["session_id"].",";
|
||
|
}
|
||
|
$sql1="delete from session where id in (".substr($stmp,-1).")";
|
||
|
$sql2="delete from session_content where session_id in (".substr($stmp,-1).")";
|
||
|
$db->query($sql1,"authuser_A");
|
||
|
$db->query($sql2,"authuser_B");
|
||
|
$sql3="insert into session ";*/
|
||
|
}
|
||
|
$sql="select * from auth.user_config where user_id=".$rs[0]["id"];
|
||
|
$rs1=$db->getAll($sql,"authuser_2");
|
||
|
$auth=array();
|
||
|
$keys=array("login","dbname","dbpasswd","dbhost","dbport","dbuser");
|
||
|
foreach ($rs1 as $row) {
|
||
|
if (in_array($row["cfg_key"],$keys)) {
|
||
|
$auth[$row["cfg_key"]]=$row["cfg_value"];
|
||
|
}
|
||
|
}
|
||
|
$sql="update auth.session set mtime = '".date("Y-M-d H:i:s.100001")."' where id = '".$rs[0]["session_id"]."'";
|
||
|
$db->query($sql,"authuser_3");
|
||
|
return $auth;
|
||
|
}
|
||
|
|
||
|
?>
|
||
| lxo-import/partsB.php | ||
|---|---|---|
|
Holger Lindemann <hli@lx-system.de>
|
||
|
*/
|
||
|
|
||
|
/* get login via GET or POST */
|
||
|
if ($_GET["login"]) {
|
||
|
$login=$_GET["login"];
|
||
|
} else {
|
||
|
$login=$_POST["login"];
|
||
|
};
|
||
|
|
||
|
function ende($nr) {
|
||
|
echo "Abbruch: $nr<br>";
|
||
|
echo "Fehlende oder falsche Daten.";
|
||
|
exit(1);
|
||
|
}
|
||
|
|
||
|
print_r($_SESSION);
|
||
|
if (!$_SESSION["db"]) {
|
||
|
$conffile="../config/authentication.pl";
|
||
|
if (!is_file($conffile)) {
|
||
|
ende(4);
|
||
|
}
|
||
|
}
|
||
|
require ("import_lib.php");
|
||
|
|
||
|
if (!anmelden()) ende(5);
|
||
|
|
||
|
/* get DB instance */
|
||
|
$db=new myDB($login);
|
||
|
$db=$_SESSION["db"]; //new myDB($login);
|
||
|
|
||
|
|
||
|
/* just display page or do real import? */
|
||
|
if ($_POST["ok"]) {
|
||
|
|
||
|
|
||
|
require ("parts_import.php");
|
||
|
|
||
|
function ende($nr) {
|
||
|
echo "Abbruch: $nr<br>";
|
||
|
echo "Fehlende oder falsche Daten.";
|
||
|
exit(1);
|
||
|
}
|
||
|
|
||
|
/* display help */
|
||
|
if ($_POST["ok"]=="Hilfe") {
|
||
|
echo "Importfelder:<br>";
|
||
| ... | ... | |
|
echo "<br>Die erste Zeile enthält die Feldnamen der Daten in ihrer richtigen Reihenfolge<br>";
|
||
|
echo "Geben Sie das Trennzeichen der Datenspalten ein. Steuerzeichen können mit ihrem Dezimalwert geführt von einem "#" eingegebn werden (#11).<br><br>";
|
||
|
echo "Der "sellprice" kann um den eingegeben Wert geändert werden.<br><br>";
|
||
|
echo "Bei vorhandenen Artikelnummern (in der db), kann entweder ein Update auf den Preis durchgeführt werden oder der Artikel mit anderer Artikelnummer eingefügt werden.<br><br>";
|
||
|
echo "Bei vorhandenen Artikelnummern (in der db), kann entweder ein Update auf den Preis (und Text) durchgeführt werden oder der Artikel mit anderer Artikelnummer eingefügt werden.<br><br>";
|
||
|
echo "Jeder Artikel muß einer Buchungsgruppe zugeordnet werden. ";
|
||
|
echo "Dazu muß entweder in der Maske eine Standardbuchungsgruppe gewählt werden <br>";
|
||
|
echo "oder es wird ein gültiges Konto in 'income_accno_id' und 'expense_accno_id' eingegeben. ";
|
||
| ... | ... | |
|
clearstatcache ();
|
||
|
|
||
|
$test = $_POST["test"];
|
||
|
$TextUpd = $_POST["TextUpd"];
|
||
|
$trenner = ($_POST["trenner"])?$_POST["trenner"]:",";
|
||
|
$trennzeichen = ($_POST["trennzeichen"])?$_POST["trennzeichen"]:"";
|
||
|
$precision = $_POST["precision"];
|
||
| ... | ... | |
|
}
|
||
|
|
||
|
/* ??? */
|
||
|
if (!file_exists("../users/$login.conf"))
|
||
|
ende(3);
|
||
|
//if (!chkUsr($login))
|
||
|
// ende(4);
|
||
|
|
||
|
/* ??? */
|
||
|
//if (!file_exists("../users/$login.conf"))
|
||
|
// ende(3);
|
||
|
|
||
|
/* check if file is really there */
|
||
|
if (!file_exists("$file.csv"))
|
||
|
ende(5);
|
||
|
ende(3);
|
||
|
|
||
|
/* ??? */
|
||
|
if (!$db->chkcol($file))
|
||
|
ende(6);
|
||
|
|
||
|
/* ??? */
|
||
|
if (!chkUsr($login))
|
||
|
ende(4);
|
||
|
|
||
|
/* first check all elements */
|
||
|
echo "Checking data:<br>";
|
||
|
$_test=$_POST;
|
||
|
$_test["precision"]=-1;
|
||
|
$_test["quotation"]=0;
|
||
|
//$_test["shop"]="n";
|
||
|
//$_test["wgtrenner"]="!";
|
||
|
$err = import_parts($db, $file, $trenner, $trennzeichen, $parts, TRUE, FALSE, FALSE,$_test);
|
||
|
echo "$err Errors found\n";
|
||
|
|
||
| ... | ... | |
|
<p class="listtop">Artikelimport für die ERP<p>
|
||
|
<br>
|
||
|
<form name="import" method="post" enctype="multipart/form-data" action="partsB.php">
|
||
|
<input type="hidden" name="MAX_FILE_SIZE" value="2000000">
|
||
|
<input type="hidden" name="MAX_FILE_SIZE" value="20000000">
|
||
|
<input type="hidden" name="login" value="<?= $login ?>">
|
||
|
<table>
|
||
|
<tr><td></td><td><input type="submit" name="ok" value="Hilfe"></td></tr>
|
||
|
<tr><td><input type="submit" name="ok" value="Hilfe"></td><td></td></tr>
|
||
|
<tr><td>Trennzeichen</td><td>
|
||
|
<input type="radio" name="trenner" value=";" checked>Semikolon
|
||
|
<input type="radio" name="trenner" value=",">Komma
|
||
|
<input type="radio" name="trenner" value="#9">Tabulator
|
||
|
<input type="radio" name="trenner" value="#9" checked>Tabulator
|
||
|
<input type="radio" name="trenner" value=" ">Leerzeichen
|
||
|
<input type="radio" name="trenner" value="other">
|
||
|
<input type="text" size="2" name="trennzeichen" value="">
|
||
| ... | ... | |
|
<tr><td>Vorhandene<br>Artikelnummer:</td><td><input type="radio" name="update" value="U" checked>Preis update durchführen<br>
|
||
|
<input type="radio" name="update" value="I">mit neuer Nummer einfügen</td></tr>
|
||
|
<tr><td>Test</td><td><input type="checkbox" name="test" value="1">ja</td></tr>
|
||
|
<tr><td>Art</td><td><input type="Radio" name="ware" value="W">Ware
|
||
|
<tr><td>Textupdate</td><td><input type="checkbox" name="TextUpd" value="1">ja</td></tr>
|
||
|
<tr><td>Warengruppen<br>verbinder</td><td><input type="text" name="wgtrenner" value="!" size="3"></td></tr>
|
||
|
<tr><td>Shopartikel</td><td><input type="radio" name="shop" value="t">ja <input type="radio" name="shop" value="n" checked>nein</td></tr>
|
||
|
<tr><td>Art</td><td><input type="Radio" name="ware" value="W" checked>Ware
|
||
|
<input type="Radio" name="ware" value="D">Dienstleistung
|
||
|
<input type="Radio" name="ware" value="G" checked>gemischt (Spalte 'art' vorhanden)</td></tr>
|
||
|
<input type="Radio" name="ware" value="G">gemischt (Spalte 'art' vorhanden)</td></tr>
|
||
|
<tr><td>Default Bugru<br></td><td><select name="bugru">
|
||
|
<? if ($bugrus) foreach ($bugrus as $bg) { ?>
|
||
|
<option value="<?= $bg["id"] ?>"><?= $bg["description"] ?>
|
||
|
<? } ?>
|
||
|
</select>
|
||
|
<input type="radio" name="bugrufix" value="0" checked>nie<br>
|
||
|
<input type="radio" name="bugrufix" value="1">für alle Artikel verwenden
|
||
|
<input type="radio" name="bugrufix" value="0">nie<br>
|
||
|
<input type="radio" name="bugrufix" value="1" checked>für alle Artikel verwenden
|
||
|
<input type="radio" name="bugrufix" value="2">für Artikel ohne passende Bugru
|
||
|
</td></tr>
|
||
|
<tr><td>Daten</td><td><input type="file" name="Datei"></td></tr>
|
||
| lxo-import/parts_import.php | ||
|---|---|---|
|
return $rs[0]["id"];
|
||
|
}
|
||
|
|
||
|
//Mu? noch eingebunden werden
|
||
|
function getMakemodel($db,$hersteller,$model,$partsid,$add=true) {
|
||
|
$sql="select * from makemodel where make like '$hersteller' and model like = '$model'";
|
||
|
$rs=$db->getAll($sql);
|
||
| ... | ... | |
|
return $rs[0]["id"];
|
||
|
}
|
||
|
|
||
|
//Auf Artikelnummer testen, bzw. neue Nummer erzeugen
|
||
|
function chkPartNumber($db,$number,$check) {
|
||
|
if ($number<>"") {
|
||
|
$sql = "select * from parts where partnumber = '$number'";
|
||
| ... | ... | |
|
}
|
||
|
return $number;
|
||
|
}
|
||
|
|
||
|
//Artikelnummer testen und wenn vorhanden Preis ?ndern
|
||
|
function chkPartNumberUpd($db,$sellprice,$partnumber,$check){
|
||
|
function chkPartNumberUpd($db,$sellprice,$partnumber,$descript,$note,$check,$shop='n'){
|
||
|
if ($partnumber=="") {
|
||
|
$nummer=chkPartNumber($db,$partnumber,$check);
|
||
|
if ($nummer=="") { return -99; }
|
||
| ... | ... | |
|
$sql = "select * from parts where partnumber = '$partnumber'";
|
||
|
$rs=$db->getAll($sql);
|
||
|
if ($rs[0]["id"]>0) {
|
||
|
if ($check) return -1;
|
||
|
$sql="update parts set sellprice = $sellprice where partnumber = '$partnumber'";
|
||
|
$sql="update parts set sellprice = $sellprice, shop='$shop'";
|
||
|
if ($descript) $sql.=",description='$descript',notes='$note'";
|
||
|
$sql.=" where partnumber = '$partnumber'";
|
||
|
$rc=$db->query($sql);
|
||
|
if ($rc) return -1;
|
||
|
return -99;
|
||
| ... | ... | |
|
return $rs[0]["name"];
|
||
|
}
|
||
|
|
||
|
|
||
|
function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $insert, $show,$maske) {
|
||
|
|
||
|
$pgshow=false;
|
||
| ... | ... | |
|
$precision=$maske["precision"];
|
||
|
$quotation=$maske["quotation"];
|
||
|
$quottype=$maske["quottype"];
|
||
|
$shop=$maske["shop"];
|
||
|
$wgtrenner=$maske["wgtrenner"];
|
||
|
$UpdText=($maske["TextUpd"]=="1")?true:false;
|
||
|
|
||
|
$Update=($maske["update"]=="U")?true:false;
|
||
|
/* field description */
|
||
| ... | ... | |
|
$fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>""))));
|
||
|
$in_fld[]=$fld;
|
||
|
if (in_array(trim($fld),$parts_fld)) {
|
||
|
if ($fld=="partsgroup" || $fld=="partsgroup1" ) {
|
||
|
if (substr($fld,0,10)=="partsgroup") {
|
||
|
$pgshow=true;
|
||
|
} else if ($fld=="notes" || $fld=="notes1" ) {
|
||
|
$note2show=true;
|
||
|
} else {
|
||
|
show( $show, "<td>$fld</td>\n");
|
||
|
}
|
||
| ... | ... | |
|
show( $show, "<td>unit</td>\n");
|
||
|
};
|
||
|
if ($pgshow) show( $show, "<td>partsgroup</td>\n");
|
||
|
if ($note2show) show( $show, "<td>notes</td>\n");
|
||
|
$posprice=0;
|
||
|
$posnumber=0;
|
||
|
$posdescript=0;
|
||
|
$posnotes=0;
|
||
|
$posnotes1=0;
|
||
|
$j=0;
|
||
|
foreach ($infld as $value) {
|
||
|
if ($infld[$j]=="sellprice") $posprice=$j;
|
||
|
if ($infld[$j]=="partnumber") $posnumber=$j;
|
||
|
if ($infld[$j]=="description") $posdescript=$j;
|
||
|
if ($infld[$j]=="notes") $posnotes=$j;
|
||
|
if ($infld[$j]=="notes1") $posnotes1=$j;
|
||
|
$j++;
|
||
|
}
|
||
|
$m=0; /* line */
|
||
|
$errors=0; /* number of errors detected */
|
||
|
$income_accno = "";
|
||
|
$expense_accno = "";
|
||
|
|
||
|
/*if ($insert) {
|
||
|
$sql="update parts set shop = 'n' where partnumber like '______'";
|
||
|
$rc=$db->query($sql);
|
||
|
//echo $sql; print_r($rc); echo "<br>";
|
||
|
if (!$rc) {
|
||
|
echo "Fehler: Artikel nicht aus dem Shop genommen";
|
||
|
}
|
||
|
};*/
|
||
|
if ($quottype=="P") $quotation=($quotation+100)/100;
|
||
|
while ( ($zeile=fgetcsv($f,15000,$trenner)) != FALSE) {
|
||
|
while ( ($zeile=fgetcsv($f,120000,$trenner)) != FALSE) {
|
||
|
$i=0; /* column */
|
||
|
$m++; /* increase line */
|
||
|
|
||
|
|
||
|
if ($Update) {
|
||
|
$sellprice=$zeile[$posprice];
|
||
|
$partnumber=$zeile[$posnumber];
|
||
| ... | ... | |
|
else { $sellprice = $sellprice * $quotation; }
|
||
|
};
|
||
|
if ($precision>=0) $sellprice = round($sellprice,$precision);
|
||
|
$rc=chkPartNumberUpd($db,$sellprice,$partnumber,!$insert);
|
||
|
if ($UpdText) {
|
||
|
$description=$zeile[$posdescript];
|
||
|
$note=$zeile[$posnotes];
|
||
|
$note1=$zeile[$posnotes1];
|
||
|
$note=mb_convert_encoding($note,"ISO-8859-15","ISO-8859-15");
|
||
|
$note=preg_replace('/""[^ ]/','"',$note);
|
||
|
$note=" \n".addslashes($note);
|
||
|
$note1=mb_convert_encoding($note1,"ISO-8859-15","ISO-8859-15");
|
||
|
$note1=preg_replace('/""[^ ]/','"',$note1);
|
||
|
$note.=" \n".addslashes($note1);
|
||
|
$rc=chkPartNumberUpd($db,$sellprice,$partnumber,$description,$note,$check);
|
||
|
} else {
|
||
|
$rc=chkPartNumberUpd($db,$sellprice,$partnumber,false,false,$check);
|
||
|
}
|
||
|
if ($rc==-1) {
|
||
|
show($show,"<tr><td>Update </td><td>$partnumber:$sellprice</td></tr>\n");
|
||
|
continue;
|
||
| ... | ... | |
|
$sql="insert into $file ";
|
||
|
$keys="(";
|
||
|
$vals=" values (";
|
||
|
|
||
|
show( $show, "<tr><td>$m</td>\n");
|
||
|
|
||
|
/* for each column */
|
||
|
$dienstleistung=false;
|
||
|
$artikel=-1;
|
||
|
$partNr=false;
|
||
|
$pg_name_val="";
|
||
|
$pg_name_val=array();
|
||
|
$note_val="";
|
||
|
$model="";
|
||
|
$hersteller="";
|
||
|
foreach($zeile as $data) {
|
||
|
/* check if column will be imported */
|
||
|
if (!in_array(trim($in_fld[$i]),$parts_fld)) {
|
||
| ... | ... | |
|
/* add key and data */
|
||
|
|
||
|
/* special case partsgroup1 */
|
||
|
if ($key == "partsgroup1") {
|
||
|
if ($pg_name_val<>"") {
|
||
|
if ($data<>"") $pg_name_val.="!".$data;
|
||
|
} else {
|
||
|
$pg_name_val=$data;
|
||
|
}
|
||
|
$i++;
|
||
|
continue;
|
||
|
} else if ($key == "partsgroup") {
|
||
|
/* special case partsgroup */
|
||
|
$pg_name_val=$data;
|
||
|
if (substr($key,0,10) == "partsgroup") {
|
||
|
if (strlen($key)==10) { $pgnr=0; }
|
||
|
else { $pgnr=substr($key,-1); }
|
||
|
$pg_name_val[$pgnr]=$data;
|
||
|
$i++;
|
||
|
continue;
|
||
|
} else if ($key == "lastcost" ||
|
||
|
} else if ($key == "lastcost" ||
|
||
|
$key == "sellprice") {
|
||
|
|
||
|
/* convert 0,0 numeric into 0.0 */
|
||
| ... | ... | |
|
//show( $show, "<td>$partnumber</td>\n");
|
||
|
}
|
||
|
} else if ($key == "description") {
|
||
|
$data=mb_convert_encoding($data,"ISO-8859-15","auto");
|
||
|
$data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
|
||
|
$data=preg_replace('/""[^ ]/','"',$data);
|
||
|
$data=addslashes($data);
|
||
|
} else if ($key == "notes") {
|
||
|
$data=mb_convert_encoding($data,"ISO-8859-15","auto");
|
||
|
$data=addslashes($data);
|
||
|
$data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
|
||
|
$data=preg_replace('/""[^ ]/','"',$data);
|
||
|
$notesval=addslashes($data);
|
||
|
$i++;
|
||
|
continue;
|
||
|
} else if ($key == "notes1") {
|
||
|
$data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
|
||
|
$data=preg_replace('/""[^ ]/','"',$data);
|
||
|
$notesval.=" \n".addslashes($data);
|
||
|
$i++;
|
||
|
continue;
|
||
|
//$key="notes";
|
||
|
/*} else if ($key == "makemodel") {
|
||
|
$data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
|
||
|
$hersteller=addslashes($data);
|
||
|
$i++;
|
||
|
continue;
|
||
|
} else if ($key == "model") {
|
||
|
$data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
|
||
|
$model=addslashes($data);
|
||
|
$i++;
|
||
|
continue;*/
|
||
|
} else if ($key == "unit") {
|
||
|
if ($data=="") {
|
||
|
if ($maske["ware"]=="W") { $data=$stdunitW; }
|
||
| ... | ... | |
|
else if ($data == "Stunde")
|
||
|
$data = "Std";
|
||
|
/* check if unit exists */
|
||
|
//echo "!$data!";
|
||
|
if (!existUnit($db, $data)) {
|
||
|
echo "Error in line $m: ";
|
||
|
echo "Einheit <b>$data</b> existiert nicht ";
|
||
| ... | ... | |
|
$i++;
|
||
|
show( $show, "<td>$data</td>\n");
|
||
|
continue;
|
||
|
}
|
||
|
}
|
||
|
if ($data==false or empty($data) or !$data) {
|
||
|
show( $show, "<td>NULL</td>\n");
|
||
|
$i++;
|
||
|
continue;
|
||
|
}
|
||
|
}
|
||
|
/* convert JA to Yes */
|
||
|
if ($data === "J" || $data === "j") $data = "Y";
|
||
|
$vals.="'".$data."',";
|
||
| ... | ... | |
|
show( $show,"<td>$einh</td>\n");
|
||
|
}
|
||
|
/* special case partsgroup */
|
||
|
//Mehrere Warengruppennamen werden mit einem Trennzeichen zu einen Namen zusammengef?rt.
|
||
|
if ($pgshow) {
|
||
|
if ($pg_name_val) {
|
||
|
foreach($pg_name_val as $pg) {
|
||
|
if ($pg>"") $pgname.=$pg.$wgtrenner;
|
||
|
};
|
||
|
unset($pg_name_val);
|
||
|
if ($pgname and $pgname <> "!") {
|
||
|
$pgname=substr($pgname,0,-1);
|
||
|
/* get ID of partsgroup or add new
|
||
|
* partsgroup_id */
|
||
|
$ID = getPartsgroupId($db, $pg_name_val, $insert);
|
||
|
$ID = getPartsgroupId($db, $pgname, $insert);
|
||
|
$keys.= "partsgroup_id,";
|
||
|
$vals.="'".$ID."',";
|
||
|
show( $show, "<td>".htmlentities($pg_name_val).":$ID</td>\n");
|
||
|
show( $show, "<td>".htmlentities($pgname).":$ID</td>\n");
|
||
|
} else {
|
||
|
show( $show,"<td>NULL</td>\n");
|
||
|
}
|
||
|
}
|
||
|
if ($note2show) {
|
||
|
if ($notesval) {
|
||
|
$keys.="notes,";
|
||
|
$vals.="'$notesval',";
|
||
|
if (strlen($notesval)>255) {
|
||
|
show( $show, "<td>".substr($notesval,0,25)." . . . ".htmlentities(substr($notesval,-25))."</td>\n");
|
||
|
} else {
|
||
|
show( $show, "<td>$notesval</td>\n");
|
||
|
}
|
||
|
} else {
|
||
|
show( $show,"<td>NULL</td>\n");
|
||
|
}
|
||
| ... | ... | |
|
show( $show, "<td>$partnumber</td>\n");
|
||
|
}
|
||
|
}
|
||
|
$sql.=$keys."import)";
|
||
|
$sql.=$vals.time().")";
|
||
|
//show( $show, "<td> $sql </td>\n");
|
||
|
//Automatisch Bilder mit einbinden
|
||
|
//Die Bilder m?ssen manuell hier (bilder/) her kopiert werden.
|
||
|
//Names des Bildes: "bilder/" + Artikelnummer in Grossbuchstaben + ".jpg"
|
||
|
//$bild="bilder/".strtoupper($bild).".jpg";
|
||
|
//$sql.=$keys."image,shop,weight,import)";
|
||
|
//$sql.="$vals'$bild','$shop',3,".time().")";
|
||
|
|
||
|
//ohne Bilder
|
||
|
$sql.=$keys."shop,weight,import)";
|
||
|
$sql.="$vals'$shop',3,".time().")";
|
||
|
|
||
|
if ($insert) {
|
||
|
show( $show, "<td>");
|
||
| ... | ... | |
|
}
|
||
|
show( $show, "</td>\n");
|
||
|
}
|
||
|
|
||
|
$pgname="";
|
||
|
show( $show, "</tr>\n");
|
||
|
}
|
||
|
|
||
Auch abrufbar als: Unified diff
Neue Anmeldung berücksichtigt
Mehrere Warengruppenfelder im CSV für Import zusammenfassen