+<?
+/*
+Funktionsbibliothek für den Datenimport in Lx-Office ERP
+
+Copyright (C) 2005
+Author: Holger Lindemann
+Email: hli@lx-system.de
+Web: http://lx-system.de
+
+*/
+
+require_once "DB.php";
+
+$address = array(
+ "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",
+ "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");
+
+$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");
+
+$parts = array(
+ "partnumber" => "Artikelnummer",
+ "description" => "Artikeltext",
+ "unit" => "Einheit",
+ "weight" => "Gewicht (kg)",
+ "onhand" => "Lagerbestand",
+ "notes" => "Beschreibung",
+ "makemodel" => "Hersteller",
+ "model" => "Modellbezeichnung",
+ "income_accno_id" => "Erlöskonto",
+ "expense_accno_id" => "Konto Umsatzkosten",
+ "bin" => "Lagerort",
+ "image" => "Pfad/Dateiname",
+ "drawing" => "Pfad/Dateiname",
+ "microfiche" => "Pfad/Dateiname",
+ "partsgroup_id" => "Name Warengruppe",
+ "listprice" => "Listenpreis",
+ "sellprice" => "Verkaufspreis",
+ "lastcost" => "letzter EK",
+ "art" => "Ware/Dienstleistung (*/d)");
+
+$contactscrm = 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_street" => "Strasse",
+ "cp_zipcode" => "PLZ",
+ "cp_city" => "Ort",
+ "cp_notes" => "Bemerkungen",
+ "cp_country" => "Land",
+ "cp_stichwort1" => "Stichwort(e)",
+ "katalog" => "Katalog",
+ "inhaber" => "Inhaber",
+ "contact_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",
+ "katalog" => "Katalog",
+ "inhaber" => "Inhaber",
+ "contact_id" => "Kontakt ID"
+ );
+
+function checkCRM() {
+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; };
+}
+
+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->lock();
+ $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;
+ }
+}
+
+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"];
+}
+
+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"]);
+ }
+}
+
+$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"));
+ $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"];
+ }
+}
+
+class myDB extends DB {
+// Datenbankklasse
+
+ var $rc = false;
+ var $showErr = false;
+ var $db = false;
+ var $debug = false;
+
+/****************************************************
+* uudecode
+* in: string
+* out: string
+* dekodiert Perl-UU-kodierte Passwort-Strings
+* http://de3.php.net/base64_decode (bug #171)
+*****************************************************/
+ function uudecode($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 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->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->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; };
+ }
+}
+
+?>
projects / kivitendo-erp.git / commitdiff