3 Funktionsbibliothek für den Datenimport in Lx-Office ERP
6 Author: Holger Lindemann
7 Email: hli@lx-system.de
8 Web: http://lx-system.de
12 require_once "DB.php";
15 "name" => "Firmenname",
16 "department_1" => "Abteilung",
17 "department_2" => "Abteilung",
18 "street" => "Strasse + Nr",
22 "contact" => "Ansprechpartner",
25 "homepage" => "Homepage",
27 "notes" => "Bemerkungen",
28 "discount" => "Rabatt (nn.nn)",
29 "taxincluded" => "incl. Steuer? (t/f)",
30 "terms" => "Zahlungsziel (Tage)",
31 "customernumber" => "Kundennummer",
32 "vendornumber" => "Lieferantennummer",
33 "taxnumber" => "Steuernummer",
34 "account_number" => "Kontonummer",
35 "bank_code" => "Bankleitzahl",
37 "branche" => "Branche",
38 "language" => "Sprache (de,en,fr)",
40 "creditlimit" => "Kreditlimit (nnnnnn.nn)",
41 "hierarchie" => "Hierarchie",
42 "potenzial" => "Potenzial",
43 "ar" => "Debitorenkonto",
44 "ap" => "Kreditorenkonto",
45 "matchcode" => "Matchcode",
46 "customernumber2" => "Kundennummer 2");
49 "shiptoname" => "Firmenname",
50 "shiptodepartment_1" => "Abteilung",
51 "shiptodepartment_2" => "Abteilung",
52 "shiptostreet" => "Strasse + Nr",
53 "shiptozipcode" => "Plz",
54 "shiptocity" => "Ort",
55 "shiptocountry" => "Land",
56 "shiptocontact" => "Ansprechpartner",
57 "shiptophone" => "Telefon",
59 "shiptoemail" => "eMail",
60 "customernumber" => "Kundennummer",
61 "vendornumber" => "Lieferantennummer");
64 "partnumber" => "Artikelnummer",
65 "description" => "Artikeltext",
67 "weight" => "Gewicht (kg)",
68 "onhand" => "Lagerbestand",
69 "notes" => "Beschreibung",
70 "makemodel" => "Hersteller",
71 "model" => "Modellbezeichnung",
72 "income_accno_id" => "Erlöskonto",
73 "expense_accno_id" => "Konto Umsatzkosten",
75 "image" => "Pfad/Dateiname",
76 "drawing" => "Pfad/Dateiname",
77 "microfiche" => "Pfad/Dateiname",
78 "partsgroup_id" => "Name Warengruppe",
79 "listprice" => "Listenpreis",
80 "sellprice" => "Verkaufspreis",
81 "lastcost" => "letzter EK",
82 "art" => "Ware/Dienstleistung (*/d)");
85 "customernumber" => "Kundennummer",
86 "vendornumber" => "Lieferantennummer",
87 "cp_cv_id" => "FirmenID in der db",
88 "firma" => "Firmenname",
89 "cp_greeting" => "Anrede",
90 "cp_title" => "Titel",
91 "cp_givenname" => "Vorname",
92 "cp_greeting" => "Anrede",
93 "cp_name" => "Nachname",
94 "cp_email" => "eMail",
95 "cp_phone1" => "Telefon 1",
96 "cp_phone2" => "Telefon 2",
97 "cp_street" => "Strasse",
98 "cp_zipcode" => "PLZ",
100 "cp_notes" => "Bemerkungen",
101 "cp_country" => "Land",
102 "cp_stichwort1" => "Stichwort(e)",
103 "katalog" => "Katalog",
104 "inhaber" => "Inhaber",
105 "contact_id" => "Kontakt ID"
109 "customernumber" => "Kundennummer",
110 "vendornumber" => "Lieferantennummer",
111 "cp_cv_id" => "FirmenID in der db",
112 "firma" => "Firmenname",
113 "cp_greeting" => "Anrede",
114 "cp_title" => "Titel",
115 "cp_givenname" => "Vorname",
116 "cp_greeting" => "Anrede",
117 "cp_name" => "Nachname",
118 "cp_email" => "eMail",
119 "cp_phone1" => "Telefon 1",
120 "cp_phone2" => "Telefon 2",
121 "katalog" => "Katalog",
122 "inhaber" => "Inhaber",
123 "contact_id" => "Kontakt ID"
126 function checkCRM() {
128 $sql="select * from crm";
129 $rs=$db->getAll($sql);
137 function chkUsr($usr) {
138 // ist es ein gültiger ERP-Benutzer? Er muß mindestens 1 x angemeldet gewesen sein.
140 $sql="select * from employee where login = '$usr'";
141 $rs=$db->getAll($sql);
142 if ($rs[0]["id"]) { return $rs[0]["id"]; }
143 else { return false; };
147 // die nächste freie Kunden-/Lieferantennummer holen
148 global $db,$file,$test;
149 if ($test) { return "#####"; }
150 $sql1="select * from defaults";
151 $sql2="update defaults set ".$file."number = '%s'";
153 $rs=$db->getAll($sql1);
154 $nr=$rs[0][$file."number"];
155 preg_match("/^([^0-9]*)([0-9]+)/",$nr,$hits);
156 if ($hits[2]) { $nr=$hits[2]+1; $nnr=$hits[1].$nr; }
157 else { $nr=$hits[1]+1; $nnr=$nr; };
158 $rc=$db->query(sprintf($sql2,$nnr));
168 function chkKdId($data) {
169 // gibt es die Nummer schon?
170 global $db,$file,$test;
171 $sql="select * from $file where ".$file."number = '$data'";
172 $rs=$db->getAll($sql);
173 if ($rs[0][$file."number"]==$data) {
174 // ja, eine neue holen
181 function getKdRefId($data) {
182 // gibt es die Nummer schon?
183 global $db,$file,$test;
184 if (empty($data) or !$data) {
187 $sql="select * from $file where ".$file."number = '$data'";
188 $rs=$db->getAll($sql);
192 function suchFirma($tab,$data) {
195 if (empty($data) or !$data) {
198 $data=strtoupper($data);
199 $sql="select * from $tab where upper(name) like '%$data%'";
200 $rs=$db->getAll($sql);
203 while(strpos($data," ")>0) {
204 $data=ereg_replace(" "," ",$data);
206 $data=preg_replace("/[^A-Z0-9]/ ",".*",trim($data));
207 $sql="select * from $tab where upper(name) ~ '$data'";
208 $rs=$db->getAll($sql);
210 return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
214 return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
218 $land=array("DEUTSC"=>"D","FRANKR"=>"F","SPANIE"=>"ES","ITALIE"=>"I","HOLLAN"=>"NL","NIEDER"=>"NL",
219 "BELGIE"=>"B","LUXEMB"=>"L","NORWEG"=>"N","FINNLA"=>"","GRIECH"=>"GR","OESTER"=>"A",
220 "SCHWEI"=>"CH","SCHWED"=>"S","AUSTRI"=>"A");
222 function mkland($data) {
224 $data=strtr($data,array("Ö"=>"OE","Ä"=>"AE","Ü"=>"UE","ö"=>"OE","ä"=>"AE","ü"=>"UE","ß"=>"SS"));
225 $data=strtoupper(substr($data,0,6));
227 return (strlen($cntr)>0)?$cntr:substr($data,0,3);
230 //Suche Nach Kunden-/Lieferantenummer
231 function getFirma($nummer,$tabelle) {
233 $nummer=strtoupper($nummer);
234 $sql="select id from $tabelle where upper(".$tabelle."number) = '$nummer'";
235 $rs=$db->getAll($sql);
237 $nr=ereg_replace(" ","%",$nummer);
238 $sql="select id,".$tabelle."number from $tabelle where upper(".$tabelle."number) like '$nr'";
239 $rs=$db->getAll($sql);
241 $nr=ereg_replace(" ","",$nummer);
242 foreach ($rs as $row) {
243 $tmp=ereg_replace(" ","",$row[$tabelle."number"]);
244 if ($tmp==$nr) return $row["id"];
254 class myDB extends DB {
258 var $showErr = false;
262 /****************************************************
266 * dekodiert Perl-UU-kodierte Passwort-Strings
267 * http://de3.php.net/base64_decode (bug #171)
268 *****************************************************/
269 function uudecode($encode) {
270 $b64chars="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
272 $encode = preg_replace("/^./m","",$encode);
273 $encode = preg_replace("/\n/m","",$encode);
274 for($i=0; $i<strlen($encode); $i++) {
275 if ($encode[$i] == '')
277 $encode[$i] = $b64chars[ord($encode[$i])-32];
280 while(strlen($encode) % 4)
283 return base64_decode($encode);
286 function dbFehler($sql,$err) {
288 echo "$sql : $err\n";
291 function showDebug($sql) {
293 if ($this->debug==2) {
298 function myDB($usr) {
299 // Datenbankparameter des ERP-Users benutzen.
300 $tmp = file_get_contents("../users/$usr.conf");
301 preg_match("/dbname => '(.+)'/",$tmp,$hits);
303 preg_match("/dbpasswd => '(.+)'/",$tmp,$hits);
305 $dbpasswd=$this->uudecode($hits[1]);
309 preg_match("/dbuser => '(.+)'/",$tmp,$hits);
311 preg_match("/dbhost => '(.+)'/",$tmp,$hits);
313 if (!$dbhost) $dbhost="localhost";
315 $dns=$dbuser.":".$dbpasswd."@".$dbhost."/".$dbname;
317 $dns=$dbuser."@".$dbhost."/".$dbname;
319 $dns="pgsql://".$dns;
320 $this->db=DB::connect($dns);
321 if (!$this->db) DB::dbFehler("oh oh oh",$this->db->getDebugInfo());
322 if (DB::isError($this->db)) {
323 $this->dbFehler("Connect",$this->db->getDebugInfo());
324 die ($this->db->getDebugInfo());
329 function query($sql) {
330 $this->rc=@$this->db->query($sql);
331 if ($this->debug) $this->showDebug($sql);
332 if(DB::isError($this->rc)) {
333 $this->dbFehler($sql,$this->rc->getMessage());
339 function getAll($sql) {
340 $this->rc=@$this->db->getAll($sql,DB_FETCHMODE_ASSOC);
341 if ($this->debug) $this->showDebug($sql);
342 if(DB::isError($this->rc)) {
343 $this->dbFehler($sql,$this->rc->getMessage());
351 $this->query("BEGIN");
354 $this->query("COMMIT");
356 function rollback() {
357 $this->query("ROLLBACK");
359 function chkcol($tbl) {
360 // gibt es die Spalte import schon?
361 $rc=$this->db->query("select import from $tbl limit 1");
362 if(DB::isError($rc)) {
363 $rc=$this->db->query("alter table $tbl add column import int4");
364 if(DB::isError($rc)) { return false; }
365 else { return true; }
367 } else { return true; };