From fc47e4830133fb0841ae91a63f909ea7ce6d72fe Mon Sep 17 00:00:00 2001 From: Holger Lindemann Date: Tue, 20 Oct 2009 20:08:15 +0200 Subject: [PATCH] =?utf8?q?Code=20=C3=BCberarbeitet?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Beispielimportdateien zugefügt --- lxo-import/addressB.php | 220 ++++----- lxo-import/customer.bsp | 2 + lxo-import/customer_contact.bsp | 2 + lxo-import/customer_shipto.bsp | 2 + lxo-import/db.php | 200 ++++---- lxo-import/import_lib.php | 638 ++++++++++++------------ lxo-import/parts.bsp | 4 + lxo-import/partsB.php | 222 ++++----- lxo-import/parts_import.php | 832 +++++++++++++++----------------- lxo-import/shiptoB.php | 289 ++++++----- 10 files changed, 1164 insertions(+), 1247 deletions(-) create mode 100644 lxo-import/customer.bsp create mode 100644 lxo-import/customer_contact.bsp create mode 100644 lxo-import/customer_shipto.bsp create mode 100644 lxo-import/parts.bsp diff --git a/lxo-import/addressB.php b/lxo-import/addressB.php index a6a6c1906..2341fdd81 100644 --- a/lxo-import/addressB.php +++ b/lxo-import/addressB.php @@ -15,21 +15,23 @@ Web: http://lx-system.de require ("import_lib.php"); if (!$_SESSION["db"]) { - $conffile="../config/authentication.pl"; - if (!is_file($conffile)) { - ende(4); - } + $conffile="../config/authentication.pl"; + if (!is_file($conffile)) { + ende("authentication.pl nicht gefunden oder unlesbar"); + } } -if (!anmelden()) ende(5); +if (!anmelden()) ende("Anmeldung fehlgeschlagen"); if ($_POST["ok"]=="Hilfe") { - echo "Importfelder:
"; - echo "Feldname => Bedeutung
"; - foreach($address as $key=>$val) { - echo "$key => $val
"; - } - exit(0); + echo "Importfelder:
"; + echo "Feldname => Bedeutung
"; + foreach($address as $key=>$val) { + echo "$key => $val
"; + } + $header=implode(";",array_keys($address)); + echo $header; + exit(0); }; if ($_POST["ok"]) { @@ -42,38 +44,37 @@ $db=$_SESSION["db"]; //new myDB($login); $crm=checkCRM(); -function ende($nr) { - echo "Abbruch: $nr
"; - echo "Fehlende oder falsche Daten."; - exit(1); +function ende($txt) { + echo "Abbruch: $txt
"; + exit(1); } clearstatcache (); //print_r($_FILES); $test=$_POST["test"]; if (!empty($_FILES["Datei"]["name"])) { - $file=$_POST["ziel"]; - if (!move_uploaded_file($_FILES["Datei"]["tmp_name"],$file.".csv")) { - $file=false; - echo "Upload von ".$_FILES["Datei"]["name"]." fehlerhaft. (".$_FILES["Datei"]["error"].")
"; - } + $file=$_POST["ziel"]; + if (!move_uploaded_file($_FILES["Datei"]["tmp_name"],$file.".csv")) { + $file=false; + echo "Upload von ".$_FILES["Datei"]["name"]." fehlerhaft. (".$_FILES["Datei"]["error"].")
"; + } } else if (is_file($_POST["ziel"].".csv")) { - $file=$_POST["ziel"]; + $file=$_POST["ziel"]; } else { - $file=false; + $file=false; } -if (!$file) ende (2); +if (!$file) ende ("Kein Datenfile"); $trenner=($_POST["trenner"])?$_POST["trenner"]:","; -if (!file_exists("$file.csv")) ende(5); +if (!file_exists("$file.csv")) ende("$file.csv nicht im Ordner oder leer"); -if (!$db->chkcol($file)) ende(6); +if (!$db->chkcol($file)) ende("Importspalte kann nicht angelegt werden"); $employee=chkUsr($_SESSION["employee"]); -if (!$employee) ende(4); +if (!$employee) ende("Ungültiger User"); $kunde_fld = array_keys($address); @@ -83,103 +84,102 @@ $infld=split($trenner,strtolower($zeile)); $first=true; $ok=true; foreach ($infld as $fld) { - $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>"")))); - if ($fld=="branche" && !$crm) { $in_fld[]=""; continue; }; - if ($fld=="sw" && !$crm) { $in_fld[]=""; continue; }; - $in_fld[]=$fld; + $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>"")))); + if ($fld=="branche" && !$crm) { $in_fld[]=""; continue; }; + if ($fld=="sw" && !$crm) { $in_fld[]=""; continue; }; + $in_fld[]=$fld; } -//print_r($in_fld); echo "
"; + $j=0; $m=0; $zeile=fgetcsv($f,1200,$trenner); if ($ok) while (!feof($f)){ - $i=0; - //echo "Arbeite an $m "; - $m++; - $anrede=""; - $Matchcode=""; - $sql="insert into $file "; - $keys="("; - $vals=" values ("; - $number=false; - foreach($zeile as $data) { - if (!in_array(trim($in_fld[$i]),$kunde_fld)) { - if ($in_fld[$i]=="anrede") { $anrede=addslashes(trim($data)); } - $i++; - continue; - }; - $data=trim($data); - $data=mb_convert_encoding($data,"ISO-8859-15","auto"); - //$data=htmlentities($data); - $data=addslashes($data); - if ($in_fld[$i]==$file."number") { // customernumber || vendornumber - if (empty($data) or !$data) { - $data=getKdId(); - $number=true; - } else { - $data=chkKdId($data); - $number=true; - } - } else if ($in_fld[$i]=="taxincluded"){ - $data=strtolower(substr($data,0,1)); - if ($data!="f" && $data!="t") $data="f"; - } else if ($in_fld[$i]=="ustid"){ - $data=strtr(" ","",$data); - } /*else if ($in_fld[$i]=="matchcode") { + $i=0; + $m++; + $anrede=""; + $Matchcode=""; + $sql="insert into $file "; + $keys="("; + $vals=" values ("; + $number=false; + foreach($zeile as $data) { + if (!in_array(trim($in_fld[$i]),$kunde_fld)) { + if ($in_fld[$i]=="anrede") { $anrede=addslashes(trim($data)); } + $i++; + continue; + }; + $data=trim($data); + $data=mb_convert_encoding($data,"ISO-8859-15","auto"); + //$data=htmlentities($data); + $data=addslashes($data); + if ($in_fld[$i]==$file."number") { // customernumber || vendornumber + if (empty($data) or !$data) { + $data=getKdId(); + $number=true; + } else { + $data=chkKdId($data); + $number=true; + } + } else if ($in_fld[$i]=="taxincluded"){ + $data=strtolower(substr($data,0,1)); + if ($data!="f" && $data!="t") $data="f"; + } else if ($in_fld[$i]=="ustid"){ + $data=strtr(" ","",$data); + } /*else if ($in_fld[$i]=="matchcode") { $matchcode=$data; $i++; continue; if ($data==false or empty($data) or !$data) { - if (in_array($in_fld[$i],array("name"))) { - $data=$matchcode; - } - } - }*/ - - $keys.=$in_fld[$i].","; - if ($data==false or empty($data) or !$data) { - $vals.="null,"; - } else { - if ($in_fld[$i]=="contact"){ - if ($anrede) { - $vals.="'$anrede $data',"; - } else { - $vals.="'$data',"; - } - } else { - $vals.="'".$data."',"; - } - } - $i++; - } - if (!$number) { - $keys.=$file."number,"; - $vals.="'".getKdId()."',"; - } - if ($keys<>"(") { - if ($test) { - if ($first) { - echo "\n"; - echo "\n"; - $first=false; - }; - $vals=str_replace("',","'\n"; - //echo "Import $j
\n"; - flush(); - } else { - $sql.=$keys."taxzone_id,import)"; - $sql.=$vals."0,$nun)"; - $rc=$db->query($sql); - if (!$rc) echo "Fehler: ".$sql."
"; - } - $j++; - } else { + if (in_array($in_fld[$i],array("name"))) { + $data=$matchcode; + } + } + }*/ + + $keys.=$in_fld[$i].","; + if ($data==false or empty($data) or !$data) { + $vals.="null,"; + } else { + if ($in_fld[$i]=="contact"){ + if ($anrede) { + $vals.="'$anrede $data',"; + } else { + $vals.="'$data',"; + } + } else { + $vals.="'".$data."',"; + } + } + $i++; + } + if (!$number) { + $keys.=$file."number,"; + $vals.="'".getKdId()."',"; + } + if ($keys<>"(") { + if ($test) { + if ($first) { + echo "
".str_replace(",","",substr($keys,1,-1))."
",substr($vals,9,-1)); - echo "
".str_replace("null,","null",$vals)."
\n"; + echo "\n"; + $first=false; + }; + $vals=str_replace("',","'\n"; + //echo "Import $j
\n"; + flush(); + } else { + $sql.=$keys."taxzone_id,import)"; + $sql.=$vals."0,$nun)"; + $rc=$db->query($sql); + if (!$rc) echo "Fehler: ".$sql."
"; + } + $j++; + } else { $vals=str_replace("',","'\n"; flush(); } - $zeile=fgetcsv($f,1200,$trenner); + $zeile=fgetcsv($f,1200,$trenner); } fclose($f); if ($test) echo "
".str_replace(",","",substr($keys,1,-1))."
",substr($vals,9,-1)); + echo "
".str_replace("null,","null",$vals)."
",substr($vals,9,-1)); echo "
".str_replace("null,","null",$vals)."
\n ##### = Neue Kunden-/Lieferantennummer\n
"; diff --git a/lxo-import/customer.bsp b/lxo-import/customer.bsp new file mode 100644 index 000000000..574cfa247 --- /dev/null +++ b/lxo-import/customer.bsp @@ -0,0 +1,2 @@ +name;department_1;department_2;street;zipcode;city;country;contact;phone;fax;homepage;email;notes;discount;taxincluded;terms;customernumber;taxnumber;ustid;account_number;bank_code;bank;branche;sw;creditlimit +Import Kunde;;;Am Weg 3;12345;Sonstwo;D;Fr. Werner;01234123;;www.test.xx;info@test.xx;Hallole;;;;;;;2222;11223344;Meine Bank;Dienstleistung;; diff --git a/lxo-import/customer_contact.bsp b/lxo-import/customer_contact.bsp new file mode 100644 index 000000000..9bacbcee9 --- /dev/null +++ b/lxo-import/customer_contact.bsp @@ -0,0 +1,2 @@ +customernumber;cp_cv_id;firma;cp_abteilung;cp_position;cp_gender;cp_title;cp_givenname;cp_name;cp_email;cp_phone1;cp_phone2;cp_mobile1;cp_mobile2;cp_homepage;cp_street;cp_country;cp_zipcode;cp_city;cp_privatphone;cp_privatemail;cp_notes;cp_stichwort1;cp_id +;;Import Kunde;Verwaltung;Leiter;m;Dr.;Hans Heinrich;Import;;;;;;;;D;12345;Sonstwo;;;; diff --git a/lxo-import/customer_shipto.bsp b/lxo-import/customer_shipto.bsp new file mode 100644 index 000000000..b3ecdb53a --- /dev/null +++ b/lxo-import/customer_shipto.bsp @@ -0,0 +1,2 @@ +firma;shiptoname;shiptodepartment_1;shiptodepartment_2;shiptostreet;shiptozipcode;shiptocity;shiptocountry;shiptocontact;shiptophone;shiptofax;shiptoemail;customernumber +Import Kunde;Import Kunde;Lagerhaus;;Bahnhofstr. 4;12345;Sonstwo;D;Hr. Hansen;0293948;lager@firma.false; diff --git a/lxo-import/db.php b/lxo-import/db.php index f8ac7d319..aae192c0f 100644 --- a/lxo-import/db.php +++ b/lxo-import/db.php @@ -7,46 +7,45 @@ class myDB extends DB { 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 $path = "/tmp/"; 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 "$sql : $err
"; - } + function dbFehler($sql,$err) { + $efh=fopen($this->path."lxcrm".date("w").".err","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 "$sql : $err
"; + } - function showDebug($sql) { - echo $sql."
"; - if ($this->debug==2) { - echo "
";
-			print_r($this->rc);
-			echo "
"; - }; - } + function showDebug($sql) { + echo $sql."
"; + if ($this->debug==2) { + echo "
";
+            print_r($this->rc);
+            echo "
"; + }; + } - 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 writeLog($txt) { + if ($this->lfh===false) + $this->lfh=fopen($this->path."lxcrm".date("w").".log","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( + function closeLogfile() { + fclose($this->lfh); + } + + function myDB($host,$user,$pwd,$db,$port,$showErr=false) { + $dsn = array( 'phptype' => 'pgsql', 'username' => $user, 'password' => $pwd, @@ -54,69 +53,86 @@ class myDB extends DB { '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; - } + $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 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 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 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 saveData($txt) { + if (get_magic_quotes_gpc()) { + return $txt; + } else { + return DB::quoteSmart($string); + } + } - function chkcol($tbl) { + function execute($statement, $data){ + $sth = $this->db->prepare($statement); //Prepare + /*if (PEAR::isError($sth)) { + $this->dbFehler($statement,$sth->getMessage()); + $this->rollback(); + return false; + }*/ + $rc = $this->db->execute($sth,$data); + if(PEAR::isError($rc)) { + $this->dbFehler(print_r($data,true),$rc->getMessage()."\n".print_r($rc,true)); + $this->rollback(); + return false; + } + $this->db->commit(); + return true; + } + + 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; }; - } + $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; }; + } } diff --git a/lxo-import/import_lib.php b/lxo-import/import_lib.php index 9e25c3dc6..2abec363c 100644 --- a/lxo-import/import_lib.php +++ b/lxo-import/import_lib.php @@ -1,6 +1,6 @@ "Firmenname", - "department_1" => "Abteilung", - "department_2" => "Abteilung", - "street" => "Strasse + Nr", - "zipcode" => "Plz", - "city" => "Ort", - "country" => "Land", - "contact" => "Ansprechpartner", - "phone" => "Telefon", - "fax" => "Fax", - "homepage" => "Homepage", - "email" => "eMail", - "notes" => "Bemerkungen", - "discount" => "Rabatt (nn.nn)", - "taxincluded" => "incl. Steuer? (t/f)", - "terms" => "Zahlungsziel (Tage)", - "customernumber" => "Kundennummer", - "vendornumber" => "Lieferantennummer", - "taxnumber" => "Steuernummer", - "ustid" => "Umsatzsteuer-ID", - "account_number" => "Kontonummer", - "bank_code" => "Bankleitzahl", - "bank" => "Bankname", - "branche" => "Branche", - //"language" => "Sprache (de,en,fr)", - "sw" => "Stichwort", - "creditlimit" => "Kreditlimit (nnnnnn.nn)"); /*, - "hierarchie" => "Hierarchie", - "potenzial" => "Potenzial", - "ar" => "Debitorenkonto", - "ap" => "Kreditorenkonto", - "matchcode" => "Matchcode", - "customernumber2" => "Kundennummer 2"); - Kundenspezifisch */ + "name" => "Firmenname", + "department_1" => "Abteilung", + "department_2" => "Abteilung", + "street" => "Strasse + Nr", + "zipcode" => "Plz", + "city" => "Ort", + "country" => "Land", + "contact" => "Ansprechpartner", + "phone" => "Telefon", + "fax" => "Fax", + "homepage" => "Homepage", + "email" => "eMail", + "notes" => "Bemerkungen", + "discount" => "Rabatt (nn.nn)", + "taxincluded" => "incl. Steuer? (t/f)", + "terms" => "Zahlungsziel (Tage)", + "customernumber" => "Kundennummer", + "vendornumber" => "Lieferantennummer", + "taxnumber" => "Steuernummer", + "ustid" => "Umsatzsteuer-ID", + "account_number" => "Kontonummer", + "bank_code" => "Bankleitzahl", + "bank" => "Bankname", + "branche" => "Branche", + //"language" => "Sprache (de,en,fr)", + "sw" => "Stichwort", + "creditlimit" => "Kreditlimit (nnnnnn.nn)"); /*, + "hierarchie" => "Hierarchie", + "potenzial" => "Potenzial", + "ar" => "Debitorenkonto", + "ap" => "Kreditorenkonto", + "matchcode" => "Matchcode", + "customernumber2" => "Kundennummer 2"); + Kundenspezifisch */ $shiptos = array( - "shiptoname" => "Firmenname", - "shiptodepartment_1" => "Abteilung", - "shiptodepartment_2" => "Abteilung", - "shiptostreet" => "Strasse + Nr", - "shiptozipcode" => "Plz", - "shiptocity" => "Ort", - "shiptocountry" => "Land", - "shiptocontact" => "Ansprechpartner", - "shiptophone" => "Telefon", - "shiptofax" => "Fax", - "shiptoemail" => "eMail", - "customernumber" => "Kundennummer", - "vendornumber" => "Lieferantennummer"); + "firma" => "Firmenname", + "shiptoname" => "Liefername", + "shiptodepartment_1" => "Abteilung", + "shiptodepartment_2" => "Abteilung", + "shiptostreet" => "Strasse + Nr", + "shiptozipcode" => "Plz", + "shiptocity" => "Ort", + "shiptocountry" => "Land", + "shiptocontact" => "Ansprechpartner", + "shiptophone" => "Telefon", + "shiptofax" => "Fax", + "shiptoemail" => "eMail", + "customernumber" => "Kundennummer", + "vendornumber" => "Lieferantennummer"); $parts = array( - "partnumber" => "Artikelnummer", - "description" => "Artikeltext", - "unit" => "Einheit", - "weight" => "Gewicht in Benutzerdefinition", - "onhand" => "Lagerbestand", - "bin" => "Lagerplatz", - "notes" => "Beschreibung", - "notes1" => "Beschreibung", - //"makemodel" => "Hersteller", - //"model" => "Modellbezeichnung", - "bin" => "Lagerort", - "image" => "Pfad/Dateiname", - "drawing" => "Pfad/Dateiname", - "microfiche" => "Pfad/Dateiname", - "listprice" => "Listenpreis", - "sellprice" => "Verkaufspreis", - "lastcost" => "letzter EK", - "art" => "Ware/Dienstleistung (*/d), muß vor den Konten kommen", - "inventory_accno" => "Bestandskonto", - "income_accno" => "Erlöskonto", - "expense_accno" => "Konto Umsatzkosten", - "obsolete" => "Gesperrt (Y/N)", - "lastcost" => "letzer EK-Preis", - "rop" => "Mindestbestand", - "shop" => "Shopartikel (Y/N)", - "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", - ); - + "partnumber" => "Artikelnummer", + "ean" => "Barcode", + "description" => "Artikeltext", + "unit" => "Einheit", + "weight" => "Gewicht in Benutzerdefinition", + "notes" => "Beschreibung", + "notes1" => "Beschreibung", + "makemodel" => "Hersteller", + "model" => "Modellbezeichnung", + "image" => "Pfad/Dateiname", + "drawing" => "Pfad/Dateiname", + "microfiche" => "Pfad/Dateiname", + "listprice" => "Listenpreis", + "sellprice" => "Verkaufspreis", + "lastcost" => "letzter EK", + "art" => "Ware/Dienstleistung (*/d), muß vor den Konten kommen", + "inventory_accno" => "Bestandskonto", + "income_accno" => "Erlöskonto", + "expense_accno" => "Konto Umsatzkosten", + "obsolete" => "Gesperrt (Y/N)", + "lastcost" => "letzer EK-Preis", + "rop" => "Mindestbestand", + "shop" => "Shopartikel (Y/N)", + "assembly" => "Stückliste (Y/N); wird noch nicht unterstützt", + "partsgroup" => "Warengruppenbezeichnung", + "partsgroup1" => "2.Warengruppenbezeichnung", + "partsgroup2" => "3.Warengruppenbezeichnung", + "partsgroup3" => "4.Warengruppenbezeichnung", + "partsgroup4" => "5.Warengruppenbezeichnung", + ); + $contactscrm = array( - "customernumber" => "Kundennummer", - "vendornumber" => "Lieferantennummer", - "cp_cv_id" => "FirmenID in der db", - "firma" => "Firmenname", - "cp_abteilung" => "Abteilung", - "cp_position" => "Position/Hierarchie", - "cp_greeting" => "Anrede", - "cp_title" => "Titel", - "cp_givenname" => "Vorname", - "cp_name" => "Nachname", - "cp_email" => "eMail", - "cp_phone1" => "Telefon 1", - "cp_phone2" => "Telefon 2", - "cp_mobile1" => "Mobiltelefon 1", - "cp_mobile2" => "Mobiltelefon 2", - "cp_homepage" => "Homepage", - "cp_street" => "Strasse", - "cp_country" => "Land", - "cp_zipcode" => "PLZ", - "cp_city" => "Ort", - "cp_privatphone" => "Privattelefon", - "cp_privatemail" => "private eMail", - "cp_notes" => "Bemerkungen", - "cp_stichwort1" => "Stichwort(e)", - "cp_id" => "Kontakt ID" - ); + "customernumber" => "Kundennummer", + "vendornumber" => "Lieferantennummer", + "cp_cv_id" => "FirmenID in der db", + "firma" => "Firmenname", + "cp_abteilung" => "Abteilung", + "cp_position" => "Position/Hierarchie", + "cp_gender" => "Geschlecht (m/f)", + "cp_title" => "Titel", + "cp_givenname" => "Vorname", + "cp_name" => "Nachname", + "cp_email" => "eMail", + "cp_phone1" => "Telefon 1", + "cp_phone2" => "Telefon 2", + "cp_mobile1" => "Mobiltelefon 1", + "cp_mobile2" => "Mobiltelefon 2", + "cp_homepage" => "Homepage", + "cp_street" => "Strasse", + "cp_country" => "Land", + "cp_zipcode" => "PLZ", + "cp_city" => "Ort", + "cp_privatphone" => "Privattelefon", + "cp_privatemail" => "private eMail", + "cp_notes" => "Bemerkungen", + "cp_stichwort1" => "Stichwort(e)", + "cp_id" => "Kontakt ID" + ); $contacts = array( - "customernumber" => "Kundennummer", - "vendornumber" => "Lieferantennummer", - "cp_cv_id" => "FirmenID in der db", - "firma" => "Firmenname", - "cp_greeting" => "Anrede", - "cp_title" => "Titel", - "cp_givenname" => "Vorname", - "cp_greeting" => "Anrede", - "cp_name" => "Nachname", - "cp_email" => "eMail", - "cp_phone1" => "Telefon 1", - "cp_phone2" => "Telefon 2", - "cp_mobile1" => "Mobiltelefon 1", - "cp_mobile2" => "Mobiltelefon 2", - "cp_privatphone" => "Privattelefon", - "cp_privatemail" => "private eMail", - "cp_homepage" => "Homepage", - "cp_id" => "Kontakt ID" - ); + "customernumber" => "Kundennummer", + "vendornumber" => "Lieferantennummer", + "cp_cv_id" => "FirmenID in der db", + "firma" => "Firmenname", + "cp_greeting" => "Anrede", + "cp_title" => "Titel", + "cp_givenname" => "Vorname", + "cp_greeting" => "Anrede", + "cp_name" => "Nachname", + "cp_email" => "eMail", + "cp_phone1" => "Telefon 1", + "cp_phone2" => "Telefon 2", + "cp_mobile1" => "Mobiltelefon 1", + "cp_mobile2" => "Mobiltelefon 2", + "cp_privatphone" => "Privattelefon", + "cp_privatemail" => "private eMail", + "cp_homepage" => "Homepage", + "cp_id" => "Kontakt ID" + ); function checkCRM() { -global $db; - $sql="select * from crm"; - $rs=$db->getAll($sql); - if ($rs) { - return true; - } else { - return false; - } + global $db; + $sql="select * from crm"; + $rs=$db->getAll($sql); + if ($rs) { + return true; + } else { + return false; + } } function chkUsr($usr) { // 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); - if ($rs[0]["id"]) { return $rs[0]["id"]; } - else { return false; }; + global $db; + $sql="select * from employee where login = '$usr'"; + $rs=$db->getAll($sql); + if ($rs[0]["id"]) { return $rs[0]["id"]; } + else { return false; }; } function getKdId() { // die nächste freie Kunden-/Lieferantennummer holen -global $db,$file,$test; - if ($test) { return "#####"; } - $sql1="select * from defaults"; - $sql2="update defaults set ".$file."number = '%s'"; - $db->begin(); - $rs=$db->getAll($sql1); - $nr=$rs[0][$file."number"]; - preg_match("/^([^0-9]*)([0-9]+)/",$nr,$hits); - if ($hits[2]) { $nr=$hits[2]+1; $nnr=$hits[1].$nr; } - else { $nr=$hits[1]+1; $nnr=$nr; }; - $rc=$db->query(sprintf($sql2,$nnr)); - if ($rc) { - $db->commit(); - return $nnr; - } else { - $db->rollback(); - return false; - }; + global $db,$file,$test; + if ($test) { return "#####"; } + $sql1="select * from defaults"; + $sql2="update defaults set ".$file."number = '%s'"; + $db->begin(); + $rs=$db->getAll($sql1); + $nr=$rs[0][$file."number"]; + preg_match("/^([^0-9]*)([0-9]+)/",$nr,$hits); + if ($hits[2]) { $nr=$hits[2]+1; $nnr=$hits[1].$nr; } + else { $nr=$hits[1]+1; $nnr=$nr; }; + $rc=$db->query(sprintf($sql2,$nnr)); + if ($rc) { + $db->commit(); + return $nnr; + } else { + $db->rollback(); + return false; + }; } function chkKdId($data) { // gibt es die Nummer schon? -global $db,$file,$test; - $sql="select * from $file where ".$file."number = '$data'"; - $rs=$db->getAll($sql); - if ($rs[0][$file."number"]==$data) { - // ja, eine neue holen - return getKdId(); - } else { - return $data; - } + global $db,$file,$test; + $sql="select * from $file where ".$file."number = '$data'"; + $rs=$db->getAll($sql); + if ($rs[0][$file."number"]==$data) { + // ja, eine neue holen + return getKdId(); + } else { + return $data; + } } + function chkContact($id) { -global $db; - $sql="select * from contact where cp_id = $id"; - $rs=$db->getAll($sql); - if ($rs[0]["cp_id"]==$id) { - return true; - } else { - return false; - } + global $db; + $sql="select * from contact where cp_id = $id"; + $rs=$db->getAll($sql); + if ($rs[0]["cp_id"]==$id) { + return true; + } else { + return false; + } } + function getKdRefId($data) { // gibt es die Nummer schon? -global $db,$file,$test; - if (empty($data) or !$data) { - return false; - } - $sql="select * from $file where ".$file."number = '$data'"; - $rs=$db->getAll($sql); - return $rs[0]["id"]; + global $db,$file,$test; + if (empty($data) or !$data) { + return false; + } + $sql="select * from $file where ".$file."number = '$data'"; + $rs=$db->getAll($sql); + return $rs[0]["id"]; } function suchFirma($tab,$data) { // gibt die Firma ? -global $db; - if (empty($data) or !$data) { - return false; - } - $data=strtoupper($data); - $sql="select * from $tab where upper(name) like '%$data%'"; - $rs=$db->getAll($sql); - if (!$rs) { - $org=$data; - while(strpos($data," ")>0) { - $data=ereg_replace(" "," ",$data); - } - $data=preg_replace("/[^A-Z0-9]/ ",".*",trim($data)); - $sql="select * from $tab where upper(name) ~ '$data'"; - $rs=$db->getAll($sql); - if (count($rs)==1) { - return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]); - } - return false; - } else { - return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]); - } + global $db; + if (empty($data) or !$data) { + return false; + } + $data=strtoupper($data); + $sql="select * from $tab where upper(name) like '%$data%'"; + $rs=$db->getAll($sql); + if (!$rs) { + $org=$data; + while(strpos($data," ")>0) { + $data=ereg_replace(" "," ",$data); + } + $data=preg_replace("/[^A-Z0-9]/ ",".*",trim($data)); + $sql="select * from $tab where upper(name) ~ '$data'"; + $rs=$db->getAll($sql); + if (count($rs)==1) { + return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]); + } + return false; + } else { + return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]); + } } -$land=array("DEUTSC"=>"D","FRANKR"=>"F","SPANIE"=>"ES","ITALIE"=>"I","HOLLAN"=>"NL","NIEDER"=>"NL", - "BELGIE"=>"B","LUXEMB"=>"L","NORWEG"=>"N","FINNLA"=>"","GRIECH"=>"GR","OESTER"=>"A", - "SCHWEI"=>"CH","SCHWED"=>"S","AUSTRI"=>"A"); - -function mkland($data) { -global $land; - $data=strtr($data,array("Ö"=>"OE","Ä"=>"AE","Ü"=>"UE","ö"=>"OE","ä"=>"AE","ü"=>"UE","ß"=>"SS", - 'Ö'=>'OE','Ä'=>'AE','Ü'=>'UE','ö'=>'OE','ä'=>'AE','ü'=>'UE','ß'=>'SS')); - $data=strtoupper(substr($data,0,6)); - $cntr=$land[$data]; - return (strlen($cntr)>0)?$cntr:substr($data,0,3); -} //Suche Nach Kunden-/Lieferantenummer function getFirma($nummer,$tabelle) { -global $db; - $nummer=strtoupper($nummer); - $sql="select id from $tabelle where upper(".$tabelle."number) = '$nummer'"; - $rs=$db->getAll($sql); - if (!$rs) { - $nr=ereg_replace(" ","%",$nummer); - $sql="select id,".$tabelle."number from $tabelle where upper(".$tabelle."number) like '$nr'"; - $rs=$db->getAll($sql); - if ($rs) { - $nr=ereg_replace(" ","",$nummer); - foreach ($rs as $row) { - $tmp=ereg_replace(" ","",$row[$tabelle."number"]); - if ($tmp==$nr) return $row["id"]; - } - } else { - return false; - } - } else { - return $rs[0]["id"]; - } + global $db; + $nummer=strtoupper($nummer); + $sql="select id from $tabelle where upper(".$tabelle."number) = '$nummer'"; + $rs=$db->getAll($sql); + if (!$rs) { + $nr=ereg_replace(" ","%",$nummer); + $sql="select id,".$tabelle."number from $tabelle where upper(".$tabelle."number) like '$nr'"; + $rs=$db->getAll($sql); + if ($rs) { + $nr=ereg_replace(" ","",$nummer); + foreach ($rs as $row) { + $tmp=ereg_replace(" ","",$row[$tabelle."number"]); + if ($tmp==$nr) return $row["id"]; + } + } else { + return false; + } + } else { + return $rs[0]["id"]; + } } function getAllBG($db) { - $sql = "select * from buchungsgruppen order by description"; - $rs=$db->getAll($sql); - return $rs; + $sql = "select * from buchungsgruppen order by description"; + $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; + 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 { - 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; - } + return false; } + } } 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 ";*/ + $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"].","; } - $sql="select * from auth.user where id=".$rs[0]["id"]; - $rs1=$db->getAll($sql,"authuser_1"); - if (!$rs1) return false; - $auth=array(); - $auth["login"]=$rs1[0]["login"]; - $sql="select * from auth.user_config where user_id=".$rs[0]["id"]; - $rs1=$db->getAll($sql,"authuser_2"); - $keys=array("dbname","dbpasswd","dbhost","dbport","dbuser"); - foreach ($rs1 as $row) { - if (in_array($row["cfg_key"],$keys)) { - $auth[$row["cfg_key"]]=$row["cfg_value"]; - } + $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 where id=".$rs[0]["id"]; + $rs1=$db->getAll($sql,"authuser_1"); + if (!$rs1) return false; + $auth=array(); + $auth["login"]=$rs1[0]["login"]; + $sql="select * from auth.user_config where user_id=".$rs[0]["id"]; + $rs1=$db->getAll($sql,"authuser_2"); + $keys=array("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; + } + $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; } -function getLager($db) { - $sql="select w.description as ort,bin.id,bin.description as platz from warehouse w left join bin on w.id=bin.warehouse_id"; - $rs=$db->getAll($sql,"getLager"); - return $rs; -} ?> diff --git a/lxo-import/parts.bsp b/lxo-import/parts.bsp new file mode 100644 index 000000000..ccab0d083 --- /dev/null +++ b/lxo-import/parts.bsp @@ -0,0 +1,4 @@ +"partnumber" "ean" "description" "unit" "weight" "notes" "makemodel" "model" "image" "sellprice" "lastcost" "partsgroup" "partsgroup1" "partsgroup2" "partsgroup3" "rop" "partsgroup4" "income_accno" "expense_accno" "microfiche" "art" +"TI12346" 92384754671 "Importartikel 1" "Stck" 2 "Die Beschreibung zum Artikel" "Lx-System" "LX123" 10 6 "Hardware" "Test" 1.10 184 37 "Mfiche 1" "W" +"TI12345" 92384754672 "Importartikel 2" 3,1 "Die Beschreibung zum Artikel" "Lx-System" "LX124" 13 8,1 "Hardware" "Test" 184 37 "y" "W" +"TD2345" "Import Diestleistung" "Std" 125 184 37 "D" diff --git a/lxo-import/partsB.php b/lxo-import/partsB.php index 134880928..a1d206cc5 100644 --- a/lxo-import/partsB.php +++ b/lxo-import/partsB.php @@ -9,21 +9,20 @@ Holger Lindemann */ -function ende($nr) { - echo "Abbruch: $nr
"; - echo "Fehlende oder falsche Daten."; - exit(1); +function ende($txt) { + echo "Abbruch: $txt
"; + exit(1); } if (!$_SESSION["db"]) { - $conffile="../config/authentication.pl"; - if (!is_file($conffile)) { - ende(4); - } + $conffile="../config/authentication.pl"; + if (!is_file($conffile)) { + ende("authentication.pl nicht gefunden oder kein Leserecht."); + } } require ("import_lib.php"); -if (!anmelden()) ende(5); +if (!anmelden()) ende("Anmeldung fehlgeschlagen."); /* get DB instance */ $db=$_SESSION["db"]; //new myDB($login); @@ -32,88 +31,72 @@ $db=$_SESSION["db"]; //new myDB($login); /* just display page or do real import? */ if ($_POST["ok"]) { -require ("parts_import.php"); - -/* display help */ -if ($_POST["ok"]=="Hilfe") { - echo "Importfelder:
"; - echo "Feldname => Bedeutung
"; - foreach($parts as $key=>$val) { - echo "$key => $val
"; - } - echo "
Die erste Zeile enthält die Feldnamen der Daten in ihrer richtigen Reihenfolge
"; - echo "Geben Sie das Trennzeichen der Datenspalten ein. Steuerzeichen können mit ihrem Dezimalwert geführt von einem "#" eingegebn werden (#11).

"; - echo "Der "sellprice" kann um den eingegeben Wert geändert werden.

"; - 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.

"; - echo "Jeder Artikel muß einer Buchungsgruppe zugeordnet werden. "; - echo "Dazu muß entweder in der Maske eine Standardbuchungsgruppe gewählt werden
"; - echo "oder es wird ein gültiges Konto in 'income_accno_id' und 'expense_accno_id' eingegeben. "; - echo "Das Programm versucht dann eine passende Buchungsgruppe zu finden."; - exit(0); -}; - -clearstatcache (); - -$test = $_POST["test"]; -$lager = $_POST["lager"]; -$TextUpd = $_POST["TextUpd"]; -$trenner = ($_POST["trenner"])?$_POST["trenner"]:","; -$trennzeichen = ($_POST["trennzeichen"])?$_POST["trennzeichen"]:""; -$precision = $_POST["precision"]; -$quotation = $_POST["quotation"]; -$quottype = $_POST["quottype"]; -$file = "parts"; - -/* no data? */ -if (empty($_FILES["Datei"]["name"])) - ende (2); - -/* copy file */ -if (!move_uploaded_file($_FILES["Datei"]["tmp_name"],$file.".csv")) { - echo "Upload von Datei fehlerhaft."; - echo $_FILES["Datei"]["error"], "
"; - ende (2); -} - -/* ??? */ -//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(3); - -/* ??? */ -if (!$db->chkcol($file)) - ende(6); - - -/* first check all elements */ -echo "Checking data:
"; -$_test=$_POST; -$_test["precision"]=-1; -$_test["quotation"]=0; -$_test["lager"]=$_POST["lager"]; -$_test["lagerplatz"]=$_POST["lagerplatz"]; -//$_test["shop"]="n"; -//$_test["wgtrenner"]="!"; -$err = import_parts($db, $file, $trenner, $trennzeichen, $parts, TRUE, FALSE, FALSE,$_test); -echo "$err Errors found\n"; - - -if ($err!=0) - exit(0); - -/* just print data or insert it, if test is false */ -import_parts($db, $file, $trenner, $trennzeichen, $parts, FALSE, !$test, TRUE,$_POST); + require ("parts_import.php"); + + /* display help */ + if ($_POST["ok"]=="Hilfe") { + echo "Importfelder:
"; + echo "Feldname => Bedeutung
"; + foreach($parts as $key=>$val) { + echo "$key => $val
"; + } + $header=implode(";",array_keys($parts)); + echo $header; + echo "

Die erste Zeile enthält die Feldnamen der Daten in ihrer richtigen Reihenfolge
"; + echo "Geben Sie das Trennzeichen der Datenspalten ein. Steuerzeichen können mit ihrem Dezimalwert "; + echo "geführt von einem "#" eingegebn werden (#11).

"; + echo "Wird bei "Art" in der Maske "gemischt" gewählt, muss die Spalte "art" vor der Einheit stehen.

"; + echo "Der "sellprice" kann um den eingegeben Wert geändert werden.

"; + echo "Bei vorhandenen Artikelnummern (in der db), kann entweder ein Update auf den Preis (und Text) durchgeführt werden oder "; + echo "der Artikel mit anderer Artikelnummer eingefügt werden.

"; + echo "Jeder Artikel muß einer Buchungsgruppe zugeordnet werden. "; + echo "Dazu muß entweder in der Maske eine Standardbuchungsgruppe gewählt werden
"; + echo "oder es wird ein gültiges Konto in 'income_accno_id' und 'expense_accno_id' eingegeben. "; + echo "Das Programm versucht dann eine passende Buchungsgruppe zu finden."; + exit(0); + }; + + clearstatcache (); + + $test = $_POST["test"]; + $lager = $_POST["lager"]; + $TextUpd = $_POST["TextUpd"]; + $trenner = ($_POST["trenner"])?$_POST["trenner"]:","; + $trennzeichen = ($_POST["trennzeichen"])?$_POST["trennzeichen"]:""; + $precision = $_POST["precision"]; + $quotation = $_POST["quotation"]; + $quottype = $_POST["quottype"]; + $file = "parts"; + + /* no data? */ + if (empty($_FILES["Datei"]["name"])) + ende ("Kein Datenfile angegeben"); + + /* copy file */ + if (!move_uploaded_file($_FILES["Datei"]["tmp_name"],$file.".csv")) { + ende ("Upload von Datei fehlerhaft.".$_FILES["Datei"]["error"]); + } + + /* check if file is really there */ + if (!file_exists("$file.csv") or filesize("$file.csv")==0) + ende("Datenfile ($file.csv) nicht im Ordner gefunden oder leer"); + + /* Zu diesem Zeitpunkt wurde der Artikel Importiert */ + if (!$db->chkcol($file)) + ende("Importspalte konnte nicht angelegt werden"); + + /* first check all elements */ + $_test=$_POST; + $_test["precision"]=-1; + $_test["quotation"]=0; + $_test["lager"]=$_POST["lager"]; + $_test["lagerplatz"]=$_POST["lagerplatz"]; + + /* just print data or insert it, if test is false */ + import_parts($db, $file, $trenner, $trennzeichen, $parts, FALSE, !$test, $_POST["show"],$_POST); } else { - $bugrus=getAllBG($db); - $lagerplatz=getLager($db); + $bugrus=getAllBG($db); ?>

Artikelimport für die ERP

@@ -124,49 +107,42 @@ import_parts($db, $file, $trenner, $trennzeichen, $parts, FALSE, !$test, TRUE,$_ + 1 + 2 + 3 + 4 + 5 + + % + Absolut + mit neuer Nummer einfügen + - + + Dienstleistung + gemischt (Spalte 'art' vorhanden) - + +
Trennzeichen - Semikolon - Komma - Tabulator - Leerzeichen - - + Semikolon + Komma + Tabulator + Leerzeichen + +
VK-Preis
Nachkomma:		0 - 1 - 2 - 3 - 4 - 5 -
VK-Preis
Aufschlag:		 - % - Absolut
Vorhandene
Artikelnummer:		Preis update durchführen
- mit neuer Nummer einfügen
Kontollausgabeja
Testja
Textupdateja
Warengruppen
verbinder
Shopartikelja nein
Shopartikelja nein
ArtWare   - Dienstleistung - gemischt (Spalte 'art' vorhanden)
Default Bugru
 - nie
- für alle Artikel verwenden - für Artikel ohne passende Bugru -
Default Lager
 - für alle Artikel verwenden - für Artikel ohne passendes Lager -
Daten
diff --git a/lxo-import/parts_import.php b/lxo-import/parts_import.php index 7b4d69bee..9743f434e 100644 --- a/lxo-import/parts_import.php +++ b/lxo-import/parts_import.php @@ -10,485 +10,427 @@ * \returns partgroup id or "" in case of an error */ function getPartsgroupId($db, $value, $add) { - - $sql="select id from partsgroup where partsgroup = '$value'"; - $rs=$db->getAll($sql); - if (empty($rs[0]["id"]) && $add) { - $sql="insert into partsgroup (partsgroup) values ('$value')"; - $rc=$db->query($sql); - if (!$rc) - return ""; - return getPartsgroupId($db, $value, 0); - } - return $rs[0]["id"]; + $sql="select id from partsgroup where partsgroup = '$value'"; + $rs=$db->getAll($sql); + if (empty($rs[0]["id"]) && $add) { + $sql="insert into partsgroup (partsgroup) values ('$value')"; + $rc=$db->query($sql); + if (!$rc) + return ""; + return getPartsgroupId($db, $value, 0); + } + return $rs[0]["id"]; +} +function insertParts($db,$insert,$show,$data) { + if ($show) { + show('',false); + show($data["partnumber"]); show($data["lastcost"]); show($data["sellprice"]); + show($data["description"]); show(substr($data["notes"],0,25));show($data["ean"]); + show($data["weight"]); show($data["image"]); show($data["partsgroup_id"]); + show($data["buchungsgruppen_id"]);show($data["income_accno"]); show($data["expense_accno"]); + show($data["inventory_accno"]); show($data["microfiche"]); show($data["drawing"]); + show($data["rop"]); show($data["assembly"]); show($data["makemodel"]); + } + /*foreach ($data as $key=>$val) { + echo $key.":".gettype($val).":".gettype($data[$key]).":".$val."
"; + }*/ + if ($insert) { + $sqlIa = 'INSERT INTO parts ('; + $sqlIa .= 'partnumber,description,notes,ean,unit,'; + $sqlIa .= 'weight,image,sellprice,lastcost,partsgroup_id,'; + $sqlIa .= 'buchungsgruppen_id,income_accno_id,expense_accno_id,inventory_accno_id,'; + $sqlIa .= 'microfiche,drawing,rop,assembly,shop,makemodel,import) '; + $sqlIa .= 'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'; + $data["import"]=time(); + $rc=$db->execute($sqlIa,$data); + } else { + $rc = true; + } + if ($show) { + if ($rc) + show('ok'); + else + show('error'); + show(''."\n",false); + } + return $rc; +} +function updPrice($db,$insert,$show,$partnumber,$lastcost,$sellprice,$shop) { + if ($show) { + show('',false); + show($partnumber); show($lastcost); show($sellprice); + } + if ($insert) { + $sqlPr = 'UPDATE PARTS SET '; + $sqlPr .= 'sellprice = ?, lastcost = ?, shop = ? '; + $sqlPr .= 'WHERE partnumber = ?'; + $rc=$db->execute($sqlPr,array("sellprice"=>$sellprice,"lastcost"=>$lastcost,"shop"=>$shop,"partnumber"=>$partnumber)); + } else { + $rc = true; + } + if ($show) { + if ($rc) + show('ok'); + else + show('error'); + show(''."\n",false); + } + return $rc; +} +function updParts($db,$insert,$show,$partnumber,$lastcost,$sellprice, + $description,$notes,$ean,$weight,$image, + $partsgroup_id, $shop) { + if ($show) { + show('',false); + show($partnumber); show($lastcost); show($sellprice); + show($description); show(substr($notes,0,25));show($ean); + show($weight); show($image); show($partsgroup_id); + } + if ($insert) { + $sqlUa = 'UPDATE PARTS SET '; + $sqlUa .= 'description = ?, notes = ?, ean = ?, weight = ?, image = ?, '; + $sqlUa .= 'sellprice = ?, lastcost = ?, partsgroup_id = ?, shop = ? '; + $sqlUa .= 'WHERE partnumber = ?'; + $rc=$db->execute($sqlUa,array($description,$notes,$ean,$weight,$image, + $sellprice,$lastcost,$partsgroup_id,$shop,$partnumber)); + } else { + $rc = true; + } + if ($show) { + if ($rc) + show('ok'); + else + show('error'); + show(''."\n",false); + } + return $rc; } -function getMakemodel($db,$hersteller,$model,$partsid,$add=true) { - $sql="select * from makemodel where make like '$hersteller' and model like = '$model'"; - $rs=$db->getAll($sql); - if (empty($rs[0]["id"]) && $add) { - $sql="insert into makemodel (parts_id,make,model) values ($partsid,'$hersteller','$model')"; - $rc=$db->query($sql); - if (!$rc) return "f"; - return getMakemodel($db,$hersteller,$model,$partsid,false); - } - if ($rs[0]["parts_id"]==$partsid) { return "t"; } - else { return "f"; } +function getMakemodel($db,$check,$hersteller,$model,$partsid,$add=true) { + $sql="select * from makemodel where make = $hersteller and model = '$model' and parts_id = $partsid"; + $rs=$db->getAll($sql); + if (empty($rs[0]["id"]) && $add) { + $sql="insert into makemodel (parts_id,make,model) values ($partsid,'$hersteller','$model')"; + $rc=$db->query($sql); + } } function getAccnoId($db, $accno) { - $sql = "select id from chart where accno='$accno'"; - $rs=$db->getAll($sql); - return $rs[0]["id"]; + $sql = "select id from chart where accno='$accno'"; + $rs=$db->getAll($sql); + return $rs[0]["id"]; } -function chkPartNumber($db,$number,$check) { - if ($number<>"") { - $sql = "select * from parts where partnumber = '$number'"; - $rs=$db->getAll($sql); - } - //echo $sql; print_r($rs); - if ($rs[0]["id"]>0 or $number=="") { - if ($check) return "check"; - $rc=$db->query("BEGIN"); - $sql = "select articlenumber from defaults"; - $rs=$db->getAll($sql); - if ($rs[0]["articlenumber"]) { - preg_match("/([^0-9]+)?([0-9]+)([^0-9]+)?/", $rs[0]["articlenumber"] , $regs); - $number=$regs[1].($regs[2]+1).$regs[3]; - } - $sql = "update defaults set articlenumber = '$number'"; - $rc=$db->query($sql); - $rc=$db->query("COMMIT"); - $sql = "select * from parts where partnumber = '$number'"; - $rs=$db->getAll($sql); - if ($rs[0]["id"]>0) return ""; - } - return $number; +function getPartsid($db,$number) { + $sql = "select id from parts where partnumber = '$number'"; + $rs=$db->getAll($sql); + if ($rs[0]["id"]>0) { + return $rs[0]["id"]; + } else { + return false; + } } -function chkPartNumberUpd($db,$sellprice,$partnumber,$descript,$note,$check,$shop='n'){ - if ($partnumber=="") { - $nummer=chkPartNumber($db,$partnumber,$check); - if ($nummer=="") { return -99; } - else { return $nummer; }; - } - $sql = "select * from parts where partnumber = '$partnumber'"; - $rs=$db->getAll($sql); - if ($rs[0]["id"]>0) { - $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; - } - $nummer=chkPartNumber($db,$partnumber,$check); - if ($nummer=="") { return -99; } - else { return $nummer; }; + +function newPartNumber($db,$check) { + if ($check) return "check"; + $rc=$db->query("BEGIN"); + $sql = "select articlenumber from defaults"; + $rs=$db->getAll($sql); + if ($rs[0]["articlenumber"]) { + preg_match("/([^0-9]+)?([0-9]+)([^0-9]+)?/", $rs[0]["articlenumber"] , $regs); + print_r($regs); + $number=$regs[1].($regs[2]+1).$regs[3]; + } + $sql = "update defaults set articlenumber = '$number'"; + $rc=$db->query($sql); + $rc=$db->query("COMMIT"); + //Prüfen ob die Nummer nicht doch schon vergeben ist. + $sql = "select * from parts where partnumber = '$number'"; + $rs=$db->getAll($sql); + if ($rs[0]["id"]>0) return ""; + return $number; } function getBuchungsgruppe($db, $income, $expense) { - - $income_id = getAccnoId($db, $income); - $expense_id = getAccnoId($db, $expense); - //$accno0_id = getAccnoId($db, $accno0); - //$accno1_id = getAccnoId($db, $accno1); - //$accno3_id = getAccnoId($db, $accno3); - - $sql = "select id from buchungsgruppen where "; - $sql .= "income_accno_id_0 = $income_id and "; - $sql .= "expense_accno_id_0 = $expense_id "; - //$sql .= "income_accno_id_0 = '$accno0_id' "; - //$sql .= "and income_accno_id_1 = '$accno1_id' "; - //$sql .= "and income_accno_id_3 = '$accno3_id'"; - $rs=$db->getAll($sql); - return $rs[0]["id"]; + $income_id = getAccnoId($db, $income); + $expense_id = getAccnoId($db, $expense); + $sql = "select id from buchungsgruppen where "; + $sql .= "income_accno_id_0 = $income and "; + $sql .= "expense_accno_id_0 = $expense "; + $sql .= "order by sortkey"; + $rs=$db->getAll($sql); + return $rs[0]["id"]; } - function getFromBG($db, $bg_id, $name) { - - $sql = "select $name from buchungsgruppen where id='$bg_id'"; - $rs=$db->getAll($sql); - return $rs[0][$name]; + $sql = "select $name from buchungsgruppen where id='$bg_id'"; + $rs=$db->getAll($sql); + return 1*$rs[0][$name]; } function existUnit($db, $value) { - $sql="select name from units where name = '$value'"; - $rs=$db->getAll($sql); - if (empty($rs[0]["name"])) - return FALSE; - return TRUE; + $sql="select name from units where name = '$value'"; + $rs=$db->getAll($sql); + if (empty($rs[0]["name"])) + return FALSE; + return TRUE; } -function show($show, $things) { - if ($show) - echo $things; +function show($things,$td=true) { + if ($td) + echo ''.$things.''; + else + echo $things; } function getStdUnit($db,$type) { - $sql="select * from units where type='$type' order by sortkey limit 1"; - $rs=$db->getAll($sql); - if (empty($rs[0]["name"])) return "Stck"; - return $rs[0]["name"]; + $sql="select * from units where type='$type' order by sortkey limit 1"; + $rs=$db->getAll($sql); + return $rs[0]["name"]; } +function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $insert, $show ,$maske) { + $precision=$maske["precision"]; + $quotation=$maske["quotation"]; + $quottype=$maske["quottype"]; + $shop=$maske["shop"]; + $wgtrenner=$maske["wgtrenner"]; + $Update=($maske["update"]=="U")?true:false; + $UpdText=($maske["TextUpd"]=="1")?true:false; -function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $insert, $show,$maske) { + $stdunitW=getStdUnit($db,"dimension"); + $stdunitD=getStdUnit($db,"service"); + if ($quottype=="P") $quotation=($quotation+100)/100; - $pgshow=false; - $note2show=false; - $fehler=0; - $precision=$maske["precision"]; - $quotation=$maske["quotation"]; - $quottype=$maske["quottype"]; - $shop=$maske["shop"]; - $wgtrenner=$maske["wgtrenner"]; - $UpdText=($maske["TextUpd"]=="1")?true:false; + if ($show && !$insert) show("Testimport",false); + if ($show) show("\n",false); - $Update=($maske["update"]=="U")?true:false; - /* field description */ - $parts_fld = array_keys($fields); + /* field description */ + $parts_fld = array_keys($fields); - /* open csv file */ - $f=fopen("$file.csv","r"); - - /* - * read first line with table descriptions - */ - show( $show, "
\n"); - if ($trenner=="other") $trenner=trim($trennzeichen); - if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1)); - $infld=fgetcsv($f,1200,$trenner); - foreach ($infld as $fld) { - $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>"")))); - $in_fld[]=$fld; - if (in_array(trim($fld),$parts_fld)) { - if (substr($fld,0,10)=="partsgroup") { - $pgshow=true; - } else if ($fld=="notes" || $fld=="notes1" ) { - $note2show=true; - } else { - show( $show, "\n"); - } - } - } - if (!in_array("unit",$infld)) { - $stdunitW=getStdUnit($db,"dimension"); - $stdunitD=getStdUnit($db,"service"); - $unit=true; - show( $show, "\n"); - }; - if ($pgshow) show( $show, "\n"); - if ($note2show) show( $show, "\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 = ""; + /* open csv file */ + $f=fopen("$file.csv","r"); + + /* + * read first line with table descriptions + */ + if ($show) { + show('',false); + show("partnumber"); show("lastcost"); show("sellprice"); + show("description");show("notes"); show("ean"); + show("weight"); show("image"); show("partsgroup_id"); + show("bg"); show("income_accno"); show("expense_accno"); + show("inventory_accno"); show("microfiche");show("drawing");show("rop"); + show("assembly");show("makemodel"); show(""); + show("\n",false); + } - /*if ($insert) { - $sql="update parts set shop = 'n' where partnumber like '______'"; - $rc=$db->query($sql); - //echo $sql; print_r($rc); echo "
"; - if (!$rc) { - echo "Fehler: Artikel nicht aus dem Shop genommen"; - } - };*/ - if ($quottype=="P") $quotation=($quotation+100)/100; - while ( ($zeile=fgetcsv($f,120000,$trenner)) != FALSE) { - $i=0; /* column */ - $m++; /* increase line */ - - if ($Update) { - $sellprice=$zeile[$posprice]; - $partnumber=$zeile[$posnumber]; - $sellprice = str_replace(",", ".", $sellprice); - if ($quotation<>0) { - if ($quottype=="A") { $sellprice += $quotation; } - else { $sellprice = $sellprice * $quotation; } - }; - if ($precision>=0) $sellprice = round($sellprice,$precision); - 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,"\n"); - continue; - } else if ($rc==-99) { - show($show,"\n"); - continue; - } else { - $zeile[$posnumber]=$rc; - } - }; + if ($trenner=="other") $trenner=trim($trennzeichen); + if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1)); + + // Erst einmal die erste Zeile mit den Feldbezeichnungen einlesen. + $infld=fgetcsv($f,1200,$trenner); + $p=0; + foreach ($infld as $fld) { + $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>"")))); + if (in_array($fld,$parts_fld)) { + $fldpos[$fld]=$p; + } + $p++; + } + $i=0; + $u=0; + $m=0; /* line */ + $errors=0; /* number of errors detected */ + $income_accno = ""; + $expense_accno = ""; + $assembly = 'f'; - $sql="insert into $file "; - $keys="("; - $vals=" values ("; - show( $show, "\n"); + while ( ($zeile=fgetcsv($f,120000,$trenner)) != FALSE) { + $m++; /* increase line */ + $unit=false; + + unset($pgroup); + unset($notes); + unset($rop); + unset($weight); + unset($inventory_accno); + unset($income_accno); + unset($expense_accno); + unset($model); + unset($makemodel); + unset($hersteller); - /* for each column */ - $dienstleistung=false; - $artikel=-1; - $partNr=false; - $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)) { - $i++; - continue; - }; - $data=trim($data); - $key=$in_fld[$i]; - /* add key and data */ + /* VK-Preis bilden */ + $sellprice = str_replace(",", ".", $zeile[$fldpos["sellprice"]]); + $lastcost = str_replace(",", ".", $zeile[$fldpos["lastcost"]]); + if ($quotation<>0) { + if ($quottype=="A") { $sellprice += $quotation; } + else { $sellprice = $sellprice * $quotation; } + }; + if ($lastcost=="") unset($lastcost); + if ($sellprice=="") unset($sellprice); - /* special case partsgroup1 */ - 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" || - $key == "sellprice") { - - /* convert 0,0 numeric into 0.0 */ - $data = str_replace(",", ".", $data); - if ($key == "sellprice") { - if ($quotation<>0) { - if ($quottype=="A") { $data += $quotation; } - else { $data = $data * $quotation; } - }; - if ($precision>=0) $data = round($data,$precision); - } - } else if ($key == "partnumber") { - $partNr=true; - $partnumber=chkPartNumber($db,$data,$check); - if ($partnumber=="") { - show( $show, "\n"); - $i++; - continue; - } else { - //$keys.="partnumber, "; - $data=$partnumber; - //show( $show, "\n"); - } - } else if ($key == "description") { - $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","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 ($maske["ware"]=="D") { $data=$stdunitD; } - //else if ($maske["ware"]=="G") { $data=$stdunitD; //Noch machen!} - else { $data=$stdunitW; }; - } - /* convert stück and Stunde */ - if (preg_match("/^st..?ck$/i", $data)) - $data = "Stck"; - else if ($data == "Stunde") - $data = "Std"; - /* check if unit exists */ - //echo "!$data!"; - if (!existUnit($db, $data)) { - echo "Error in line $m: "; - echo "Einheit $data existiert nicht "; - echo "Bitte legen Sie diese Einheit an
"; - $errors++; - } - } else if ($key == "art") { - if ($maske["ware"]=="G" and strtoupper($data)=="D") { $artikel=false; } - else if ($maske["ware"]=="G") { $artikel=true; }; - $i++; - continue; - } else if ($key == "income_accno") { - $income_accno = $data; - $i++; - show( $show, "\n"); - continue; - } else if ($key == "expense_accno") { - $expense_accno = $data; - $i++; - show( $show, "\n"); - continue; - } - if ($data==false or empty($data) or !$data) { - show( $show, "\n"); - $i++; - continue; - } - /* convert JA to Yes */ - if ($data === "J" || $data === "j") $data = "Y"; - $vals.="'".$data."',"; - show( $show, "\n"); - $keys.=$key.","; - $i++; - } - if ($unit) { - if ($maske["ware"]=="D") { $einh=$stdunitD; } - else { $einh=$stdunitW; } - $keys.="unit,"; - $vals.="'$einh',"; - show( $show,"\n"); - } - /* special case partsgroup */ - //Mehrere Warengruppennamen werden mit einem Trennzeichen zu einen Namen zusammengefürt. - if ($pgshow) { - 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, $pgname, $insert); - $keys.= "partsgroup_id,"; - $vals.="'".$ID."',"; - show( $show, "\n"); - } else { - show( $show,"\n"); - } - } - if ($note2show) { - if ($notesval) { - $keys.="notes,"; - $vals.="'$notesval',"; - if (strlen($notesval)>255) { - show( $show, "\n"); - } else { - show( $show, "\n"); - } - } else { - show( $show,"\n"); - } - } - if ($artikel==-1) { - if ($maske["ware"]=="D") { $artikel=false; } - else { $artikel=true; }; - } - if ($maske["bugrufix"]==1) { - $bg = $maske["bugru"]; - } else { - if ($income_accno<>"" and $expense_accno<>"") { - /* search for buchungsgruppe */ - $bg = getBuchungsgruppe($db, $income_accno, $expense_accno); - if ($bg == "" and $maske["bugrufix"]==2 and $maske["bugru"]<>"") { - $bg = $maske["bugru"]; - } - } else if ($maske["bugru"]<>"" and $maske["bugrufix"]==2) { - $bg = $maske["bugru"]; - } else { - /* nothing found? user must create one */ - echo "Error in line $m: "; - echo "Keine Buchungsgruppe gefunden für
"; - echo "Erlöse Inland: $income_accno
"; - echo "Bitte legen Sie eine an oder geben Sie eine vor.
"; - echo "
"; - $errors++; - } - } - if ($bg > 0) { - /* found one, add income_accno_id etc from buchungsgr. - */ - $keys.="buchungsgruppen_id, "; - $vals.="'$bg', "; - /* XXX nur bei artikel!!! */ - if ($artikel) { - $keys.="inventory_accno_id, "; - $vals.=getFromBG($db, $bg, "inventory_accno_id")." ,"; - }; - $keys.="income_accno_id, "; - $vals.=getFromBG($db, $bg, "income_accno_id_0")." ,"; - $keys.="expense_accno_id,"; - $vals.=getFromBG($db, $bg, "expense_accno_id_0")." ,"; - } - if ($partNr==false) { - $partnumber=chkPartNumber($db,"",$check); - if ($partnumber=="") { - show( $show, "\n"); - $errors++; - } else { - $keys.="partnumber, "; - $vals.="'$partnumber',"; - show( $show, "\n"); - } - } + /* Langtext zusammenbauen */ + if ($zeile[$fldpos["notes"]]) { + $notes = preg_replace('/""[^ ]/','"',$zeile[$fldpos["notes"]]); + $notes = addslashes($notes); + } + if ($zeile[$fldpos["notes1"]]) { + $notes1 = preg_replace('/""[^ ]/','"',$zeile[$fldpos["notes1"]]); + if ($notes) { + $notes = "\n".addslashes($notes1); + } else { + $notes = addslashes($notes1); + } + } - $sql.=$keys."shop,import)"; - $sql.="$vals'$shop',".time().")"; + /* Warengruppe bilden */ + if ($fldpos["partsgroup"]>0 and $zeile[$fldpos["partsgroup"]]) $pgroup[]=$zeile[$fldpos["partsgroup"]]; + if ($fldpos["partsgroup1"]>0 and $zeile[$fldpos["partsgroup1"]]) $pgroup[]=$zeile[$fldpos["partsgroup1"]]; + if ($fldpos["partsgroup2"]>0 and $zeile[$fldpos["partsgroup2"]]) $pgroup[]=$zeile[$fldpos["partsgroup2"]]; + if ($fldpos["partsgroup3"]>0 and $zeile[$fldpos["partsgroup3"]]) $pgroup[]=$zeile[$fldpos["partsgroup3"]]; + if ($fldpos["partsgroup4"]>0 and $zeile[$fldpos["partsgroup4"]]) $pgroup[]=$zeile[$fldpos["partsgroup4"]]; + if (count($pgroup)>0) { + $pgname = implode($wgtrenner,$pgroup); + $partsgroup_id = getPartsgroupId($db, $pgname, $insert); + } - if ($insert) { - show( $show, "\n"); - } - $pgname=""; - show( $show, "\n"); - } + /* sind Hersteller und Modelnummer hinterlegt + wenn ja, erfolgt er insert später */ + if (!empty($zeile[$fldpos["makemodel"]]) and !$artikel) { + $hersteller=suchFirma("vendor",$zeile[$fldpos["makemodel"]]); + $hersteller=$hersteller["cp_cv_id"]; + if (!empty($zeile[$fldpos["model"]])) { + $model = $zeile[$fldpos["model"]]; + $makemodel = 't'; + } else { + unset($hersteller); + $makemodel = 'f'; + } + } - show( $show, "
#$fldunitpartsgroupnotes
Update $partnumber:$sellprice
Fehler Zeile $m
$mNULL$partnumber$data$dataNULL".htmlentities($data)."$einh".htmlentities($pgname).":$IDNULL".substr($notesval,0,25)." . . . ".htmlentities(substr($notesval,-25))."$notesvalNULLNULL$partnumber"); - $db->showErr = TRUE; - $rc=$db->query($sql); - if (!$rc) { - echo "Fehler"; - $fehler++; - } - show( $show, "
\n"); - fclose($f); - echo "$m Zeilen bearbeitet. ($fehler : Fehler) "; - return $errors; -} + /* Ware oder Dienstleistung */ + if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="D") or $maske["ware"]=="D") { + $artikel = false; + } else if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="W") or $maske["ware"]=="W") { + $artikel = true; + } -?> + /* Einheit ermitteln */ + if ($zeile[$fldpos["unit"]]=="") { + //Keine Einheit mitgegeben + if ($maske["ware"]=="G") { + if ($artikel) { + $unit = $stdunitD; + } else { + $unit = $stdunitW; + } + } else if ($maske["ware"]=="D") { $unit = $stdunitD; } + else { $unit = $stdunitW; }; + } else { + if (existUnit($db,$zeile[$fldpos["unit"]])) { + $unit = $zeile[$fldpos["unit"]]; + } else { + $unit = ($artikel)?$stdunitD:$stdunitW; + } + } + /* Buchungsgruppe ermitteln */ + if ($maske["bugrufix"]==1) { + $bg = $maske["bugru"]; + } else { + if ($zeile[$fldpos["income_accno"]]<>"" and $zeile[$fldpos["expense_accno"]]<>"") { + /* search for buchungsgruppe */ + $bg = getBuchungsgruppe($db, $zeile[$fldpos["income_accno"]],$zeile[$fldpos["expense_accno"]]); + if ($bg == "" and $maske["bugrufix"]==2 and $maske["bugru"]<>"") { + $bg = $maske["bugru"]; + } + } else if ($maske["bugru"]<>"" and $maske["bugrufix"]==2) { + $bg = $maske["bugru"]; + } else { + /* nothing found? user must create one */ + echo "Error in line $m: "; + echo "Keine Buchungsgruppe gefunden für
"; + echo "Erlöse Inland: ".$zeile[$fldpos["income_accno"]]."
"; + echo "Aufwand Inland: ".$zeile[$fldpos["expense_accno"]]."
"; + echo "Bitte legen Sie eine an oder geben Sie eine vor.
"; + echo "
"; + $errors++; + } + } + if ($bg > 0) { + /* found one, add income_accno_id etc from buchungsgr. */ + /* XXX nur bei artikel!!! */ + if ($artikel) { + $inventory_accno = getFromBG($db, $bg, "inventory_accno_id"); + }; + $income_accno = getFromBG($db, $bg, "income_accno_id_0"); + $expense_accno = getFromBG($db, $bg, "expense_accno_id_0"); + $bg = $bg * 1; + } else { + echo "Error in line $m: "; + echo "Keine Buchungsgruppe angegeben/gefunden
"; + $errors++; + continue; + } + + $description = preg_replace('/""[^ ]/','"',$zeile[$fldpos["description"]]); + $description = addslashes($description); + + // rop und weight müssen null oder Zahl sein + if ($zeile[$fldpos["rop"]]) $rop = 1 * $zeile[$fldpos["rop"]]; + if ($zeile[$fldpos["weight"]]) $weight = 1 * $zeile[$fldpos["weight"]]; + + if (getPartsid($db,trim($zeile[$fldpos["partnumber"]]))) { + /* es gibt die Artikelnummer */ + if ($Update) { + /* Updates durchführen */ + if ($UpdText=='1') { + $u += updParts($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice, + $description,$notes,$zeile[$fldpos["ean"]],$weight, + $zeile[$fldpos["image"]],$partsgroup_id, $shop); + } else { + $u += updPrice($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,$shop); + } + continue; + // nächste Zeile + } + } + + // Neuen Artikel einfügen + + if ($zeile[$fldpos["partnumber"]] == "") { + $zeile[$fldpos["partnumber"]] = newPartNumber($db,$check); + //Keine Artikelnummer bekommen + if ($zeile[$fldpos["partnumber"]] == "") { + continue; + } + } + $i += insertParts($db,$insert,$show,array( + "partnumber"=>$zeile[$fldpos["partnumber"]], + "description"=>$description,"notes"=>$notes, + "ean"=>$zeile[$fldpos["ean"]],"unit"=>$unit, + "weight"=>$weight,"image"=>$zeile[$fldpos["image"]], + "sellprice"=>$sellprice,"lastcost"=>$lastcost, + "partsgroup_id"=>$partsgroup_id, + "buchungsgruppen_id"=>$bg,"income_accno"=>$income_accno, + "expense_accno"=>$expense_accno,"inventory_accno"=>$inventory_accno, + "microfiche"=>$zeile[$fldpos["microfiche"]],"drawing"=>$zeile[$fldpos["drawing"]], + "rop"=>$rop,"assembly"=>$assembly, + "shop"=>$shop,"makemodel"=>$makemodel) + ); + if ($hersteller>0 && $model) { + $partsid=getPartsid($db,$zeile[$fldpos["partnumber"]]); + if ($partsid) { + getMakemodel($db,$check,$hersteller,$model,$partsid,true); + } + } + unset($zeile); + } + + if ($show) show("",false); + fclose($f); + echo "$m Zeilen bearbeitet. Importiert: $i Update: $u (".($m-$u-$i+$errors)." : Fehler) "; +} +?> diff --git a/lxo-import/shiptoB.php b/lxo-import/shiptoB.php index 14b23b946..bc79db100 100644 --- a/lxo-import/shiptoB.php +++ b/lxo-import/shiptoB.php @@ -6,169 +6,160 @@ Lieferanschriftimport mit Browser nach Lx-Office ERP Copyright (C) 2005 -Author: Philip Reetz -Email: p.reetz@linet-services.de -Web: http://www.linet-services.de +Author: Philip Reetz, Holger Lindemann +Email: p.reetz@linet-services.de, hli@lx-system.de +Web: http://www.linet-services.de, http://www.lx-system.de */ - function ende($nr) { - echo "Abbruch: $nr\n"; - exit($nr); - } - - if ($_POST["ok"]=="Hilfe") { - echo "Importfelder:
"; - echo "Feldname => Bedeutung
"; - foreach($shiptos as $key=>$val) { - echo "$key => $val
"; - } - exit(0); - }; + require ("import_lib.php"); + + function ende($nr) { + echo "Abbruch: $nr\n"; + exit($nr); + } + + if ($_POST["ok"]=="Hilfe") { + echo "Importfelder:
"; + echo "Feldname => Bedeutung
"; + foreach($shiptos as $key=>$val) { + echo "$key => $val
"; + } + $header=implode(";",array_keys($shiptos)); + echo $header; + exit(0); + }; if (!$_SESSION["db"]) { - $conffile="../config/authentication.pl"; - if (!is_file($conffile)) { - ende(4); - } + $conffile="../config/authentication.pl"; + if (!is_file($conffile)) { + ende("authentication.pl nicht gefunden oder unlesbar"); + } } -require ("import_lib.php"); -if (!anmelden()) ende(5); +if (!anmelden()) ende("Anmeldung fehlgeschlagen"); /* get DB instance */ $db=$_SESSION["db"]; //new myDB($login); - $crm=checkCRM(); if ($_POST["ok"] == "Import") { - $test=$_POST["test"]; - - $shipto_fld = array_keys($shiptos); - $shipto=$shiptos; - - $nun=time(); - - - clearstatcache (); - - $trenner=($_POST["trenner"])?$_POST["trenner"]:","; - - if (!empty($_FILES["Datei"]["name"])) { - $file=$_POST["ziel"]; - if (!move_uploaded_file($_FILES["Datei"]["tmp_name"],$file."_shipto.csv")) { - $file=false; - echo "Upload von ".$_FILES["Datei"]["name"]." fehlerhaft. (".$_FILES["Datei"]["error"].")
"; - } - } else if (is_file($_POST["ziel"]."_shipto.csv")) { - $file=$_POST["ziel"]; - } else { - $file=false; - } - if (!$file) ende (2); - - if (!file_exists($file."_shipto.csv")) ende(5); - - $employee=chkUsr($_SESSION["employee"]); - if (!$employee) ende(4); - - if (!$db->chkcol($file)) ende(6); - - $f=fopen($file."_shipto.csv","r"); - $zeile=fgetcsv($f,1000,$trenner); - $first=true; - - foreach ($zeile as $fld) { - $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>"")))); - $in_fld[]=$fld; - } - $j=0; - $prenumber=$_POST["prenumber"]; - $zeile=fgetcsv($f,1000,$trenner); - -while (!feof($f)){ - $i=-1; - $firma=""; - $name=false; - $id=false; - $sql="insert into shipto "; - $keys="("; - $vals=" values ("; - foreach($zeile as $data) { - $i++; - if (!in_array($in_fld[$i],$shipto_fld)) { - continue; - } - $data=addslashes(trim($data)); - if ($in_fld[$i]=="trans_id" && $data) { - $data=chkKdId($data); - if (!$id) $id = $data; - continue; - } else if ($in_fld[$i]=="trans_id") { - continue; - } - if ($in_fld[$i]==$file."number" && $data) { - $tmp=getFirma($data,$file); - if ($id<>$tmp) $id=$tmp; - continue; - } else if ($in_fld[$i]==$file."number") { - continue; - } - if ($in_fld[$i]=="firma") { - if ($id) continue; - $data=suchFirma($file,$firma); - if ($data) { - $id=$data["cp_cv_id"]; - } - continue; - } - $keys.=$in_fld[$i].","; - - if ($data==false or empty($data) or !$data) { - $vals.="null,"; - } else { - if (in_array($in_fld[$i],array("cp_fax","cp_phone1","cp_phone2"))) { - $data=$prenumber.$data; - } else if ($in_fld[$i]=="cp_country" && $data) { - $data=mkland($data); - } - if ($in_fld[$i]=="cp_name") $name=true; - $vals.="'".$data."',"; - // bei jedem gefuellten Datenfeld erhoehen - $val_count++; + $test=$_POST["test"]; + + $shipto_fld = array_keys($shiptos); + $shipto=$shiptos; + + $nun=time(); + + clearstatcache (); + + $trenner=($_POST["trenner"])?$_POST["trenner"]:","; + + if (!empty($_FILES["Datei"]["name"])) { + $file=$_POST["ziel"]; + if (!move_uploaded_file($_FILES["Datei"]["tmp_name"],$file."_shipto.csv")) { + $file=false; + echo "Upload von ".$_FILES["Datei"]["name"]." fehlerhaft. (".$_FILES["Datei"]["error"].")
"; + } + } else if (is_file($_POST["ziel"]."_shipto.csv")) { + $file=$_POST["ziel"]; + } else { + $file=false; + } + if (!$file) ende ("Kein Datenfile"); + + if (!file_exists($file."_shipto.csv")) ende($file."_shipto.csv nicht im Ordner gefunden oder leer"); + + $employee=chkUsr($_SESSION["employee"]); + if (!$employee) ende("Benutzer unbekannt"); + + if (!$db->chkcol($file)) ende("Importspalte konnte nicht angelegt werden"); + + $f=fopen($file."_shipto.csv","r"); + $zeile=fgetcsv($f,1000,$trenner); + $first=true; + + foreach ($zeile as $fld) { + $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>"")))); + $in_fld[]=$fld; + } + $j=0; + $n=0; + $prenumber=$_POST["prenumber"]; + $zeile=fgetcsv($f,1000,$trenner); + + while (!feof($f)){ + $i=-1; + $id=false; + $sql="insert into shipto "; + $keys=""; + $vals=""; + foreach($zeile as $data) { + $i++; + if (!in_array($in_fld[$i],$shipto_fld)) { + continue; + } + $data=addslashes(trim($data)); + if ($in_fld[$i]=="trans_id" && $data) { + $data=chkKdId($data); + if (!$id) $id = $data; + continue; + } else if ($in_fld[$i]=="trans_id") { + continue; + } + if ($in_fld[$i]==$file."number" && $data) { + $tmp=getFirma($data,$file); + if ($id<>$tmp) $id=$tmp; + continue; + } else if ($in_fld[$i]==$file."number") { + continue; + } + if ($in_fld[$i]=="firma") { + if ($id) continue; + $data=suchFirma($file,$data); + if ($data) { + $id=$data["cp_cv_id"]; } - } -// if (!$name) { -// $zeile=fgetcsv($f,1000,$trenner); -// continue; -// } - if ($keys<>"(" && $id) { - $vals.=$id.",'CT'"; - $keys.="trans_id,module"; - if ($test) { - if ($first) { - echo "\n"; - echo "\n"; - $first=false; - }; - $vals=str_replace("',","'\n"; - flush(); - } else { - $sql.=$keys.")"; - $sql.=$vals.")"; - $rc=$db->query($sql); - if (!$rc) echo "Fehler: ".$sql."\n"; - } - $j++; - } else { - echo $keys."
"; - echo $vals."
"; - }; - $zeile=fgetcsv($f,1000,$trenner); -} -fclose($f); -echo $j." $file importiert.\n";} else { + continue; + } + $keys.=$in_fld[$i].","; + + if ($data==false or empty($data) or !$data) { + $vals.="null,"; + } else { + if (in_array($in_fld[$i],array("shiptofax","shiptophone"))) { + $data=$prenumber.$data; + } + $vals.="'".$data."',"; + // bei jedem gefuellten Datenfeld erhoehen + $val_count++; + } + } + if ($keys<>"" && $id) { + $vals.=$id.",'CT'"; + $keys.="trans_id,module"; + if ($test) { + if ($first) { + echo "
".str_replace(",","",substr($keys,1,-1))."
",$vals); - echo "
".str_replace("null,","null",$vals)."
\n"; + echo "\n"; + $first=false; + }; + echo "\n"; + flush(); + } else { + $sql.="(".$keys.")"; + $sql.="values (".$vals.")"; + $rc=$db->query($sql); + if (!$rc) echo "Fehler: ".$sql."\n"; + } + $j++; + } + $n++; + $zeile=fgetcsv($f,1000,$trenner); + } + fclose($f); + echo "
".str_replace(",","",$keys)."
".str_replace(",","",$vals)."
".$j." $file shipto von $n importiert.\n"; +} else { ?>

Lieferanschriftimport für die ERP

-- 2.20.1