Kosmetik
[kivitendo-erp.git] / lxo-import / import_lib.php
1 <?
2 /*
3 Funktionsbibliothek für den Datenimport in Lx-Office ERP
4
5 Copyright (C) 2005
6 Author: Holger Lindemann
7 Email: hli@lx-system.de
8 Web: http://lx-system.de
9
10 */
11
12 require_once "DB.php";
13
14 $address = array(
15         "name" => "Firmenname",
16         "department_1" => "Abteilung",
17         "department_2" => "Abteilung",
18         "street" => "Strasse + Nr",
19         "zipcode" => "Plz",
20         "city" => "Ort",
21         "country" => "Land",
22         "contact" => "Ansprechpartner",
23         "phone" => "Telefon",
24         "fax" => "Fax",
25         "homepage" => "Homepage",
26         "email" => "eMail",
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         "ustid" => "Umsatzsteuer-ID",
35         "account_number" => "Kontonummer",
36         "bank_code" => "Bankleitzahl",
37         "bank" => "Bankname",
38         "branche" => "Branche",
39         //"language" => "Sprache (de,en,fr)",
40         "sw" => "Stichwort",
41         "creditlimit" => "Kreditlimit (nnnnnn.nn)"); /*,
42         "hierarchie" => "Hierarchie",
43         "potenzial" => "Potenzial",
44         "ar" => "Debitorenkonto",
45         "ap" => "Kreditorenkonto",
46         "matchcode" => "Matchcode",
47         "customernumber2" => "Kundennummer 2"); 
48         Kundenspezifisch */
49         
50 $shiptos = array(
51         "shiptoname" => "Firmenname",
52         "shiptodepartment_1" => "Abteilung",
53         "shiptodepartment_2" => "Abteilung",
54         "shiptostreet" => "Strasse + Nr",
55         "shiptozipcode" => "Plz",
56         "shiptocity" => "Ort",
57         "shiptocountry" => "Land",
58         "shiptocontact" => "Ansprechpartner",
59         "shiptophone" => "Telefon",
60         "shiptofax" => "Fax",
61         "shiptoemail" => "eMail",
62         "customernumber" => "Kundennummer",
63         "vendornumber" => "Lieferantennummer");
64
65 $parts = array( 
66         "partnumber" => "Artikelnummer",
67         "description" => "Artikeltext",
68         "unit" => "Einheit",
69         "weight" => "Gewicht in Benutzerdefinition",
70         "onhand" => "Lagerbestand",
71         "notes" => "Beschreibung",
72         //"makemodel" => "Hersteller",
73         //"model" => "Modellbezeichnung",
74         "bin" => "Lagerort",
75         "image" => "Pfad/Dateiname",
76         "drawing" => "Pfad/Dateiname",
77         "microfiche" => "Pfad/Dateiname",
78         "listprice" => "Listenpreis",
79         "sellprice" => "Verkaufspreis",
80         "lastcost" => "letzter EK",
81         "art" => "Ware/Dienstleistung (*/d), mu&szlig; vor den Konten kommen",
82         "inventory_accno" => "Bestandskonto",
83         "income_accno" => "Erl&ouml;skonto",
84         "expense_accno" => "Konto Umsatzkosten",
85         "obsolete" => "Gesperrt (Y/N)",
86         "lastcost" => "letzer EK-Preis",
87         "rop" => "Mindestbestand",
88         "shop" => "Shopartikel (Y/N)",
89         "assembly" => "Stückliste (Y/N); wird noch nicht unterstützt",
90         "partsgroup" => "Warengruppenbezeichnung",
91         "partsgroup1" => "2.Warengruppenbezeichnung",
92         //"income_accno_0" => "?Nummer? für Erlöse Inland",
93         //"income_accno_1" => "?Nummer? für Erlöse EG",
94         //"income_accno_3" => "?Nummer? für Erlöse Ausland",
95         );
96         
97 $contactscrm = array(
98         "customernumber" => "Kundennummer",
99         "vendornumber" => "Lieferantennummer",
100         "cp_cv_id" => "FirmenID in der db",
101         "firma" => "Firmenname",
102         "cp_abteilung" => "Abteilung",
103         "cp_position" => "Position/Hierarchie",
104         "cp_greeting" => "Anrede",
105         "cp_title" => "Titel",
106         "cp_givenname" => "Vorname",
107         "cp_name" => "Nachname",
108         "cp_email" => "eMail",
109         "cp_phone1" => "Telefon 1",
110         "cp_phone2" => "Telefon 2",
111         "cp_mobile1" => "Mobiltelefon 1",
112         "cp_mobile2" => "Mobiltelefon 2",
113         "cp_homepage" => "Homepage",
114         "cp_street" => "Strasse",
115         "cp_country" => "Land",
116         "cp_zipcode" => "PLZ",
117         "cp_city" => "Ort",
118         "cp_privatphone" => "Privattelefon",
119         "cp_privatemail" => "private eMail",
120         "cp_notes" => "Bemerkungen",
121         "cp_stichwort1" => "Stichwort(e)",
122         "katalog" => "Katalog",
123         "inhaber" => "Inhaber",
124         "contact_id" => "Kontakt ID"
125         );
126
127 $contacts = array(
128         "customernumber" => "Kundennummer",
129         "vendornumber" => "Lieferantennummer",
130         "cp_cv_id" => "FirmenID in der db",
131         "firma" => "Firmenname",
132         "cp_greeting" => "Anrede",
133         "cp_title" => "Titel",
134         "cp_givenname" => "Vorname",
135         "cp_greeting" => "Anrede",
136         "cp_name" => "Nachname",
137         "cp_email" => "eMail",
138         "cp_phone1" => "Telefon 1",
139         "cp_phone2" => "Telefon 2",
140         "cp_mobile1" => "Mobiltelefon 1",
141         "cp_mobile2" => "Mobiltelefon 2",
142         "cp_privatphone" => "Privattelefon",
143         "cp_privatemail" => "private eMail",
144         "cp_homepage" => "Homepage",
145         "katalog" => "Katalog",
146         "inhaber" => "Inhaber",
147         "contact_id" => "Kontakt ID"
148         );
149
150 function checkCRM() {
151 global $db;
152         $sql="select * from crm";
153         $rs=$db->getAll($sql);
154         if ($rs) {
155                 return true;
156         } else {
157                 return false;
158         }
159 }
160
161 function chkUsr($usr) {
162 // ist es ein gültiger ERP-Benutzer? Er muß mindestens 1 x angemeldet gewesen sein.
163 global $db;
164         $sql="select * from employee where login = '$usr'";
165         $rs=$db->getAll($sql);
166         if ($rs[0]["id"]) { return $rs[0]["id"]; } 
167         else { return false; };
168 }
169
170 function getKdId() {
171 // die nächste freie Kunden-/Lieferantennummer holen
172 global $db,$file,$test;
173         if ($test) { return "#####"; }
174         $sql1="select * from defaults";
175         $sql2="update defaults set ".$file."number = '%s'";
176         $db->lock();
177         $rs=$db->getAll($sql1);
178         $nr=$rs[0][$file."number"];
179         preg_match("/^([^0-9]*)([0-9]+)/",$nr,$hits);
180         if ($hits[2]) { $nr=$hits[2]+1; $nnr=$hits[1].$nr; }
181         else { $nr=$hits[1]+1; $nnr=$nr; };
182         $rc=$db->query(sprintf($sql2,$nnr));
183         if ($rc) { 
184                 $db->commit(); 
185                 return $nnr;
186         } else { 
187                 $db->rollback(); 
188                 return false;
189         };
190 }
191
192 function chkKdId($data) {
193 // gibt es die Nummer schon?
194 global $db,$file,$test;
195         $sql="select * from $file where ".$file."number = '$data'";
196         $rs=$db->getAll($sql);
197         if ($rs[0][$file."number"]==$data) {
198                 // ja, eine neue holen
199                 return getKdId();
200         } else {
201                 return $data;
202         }
203 }
204
205 function getKdRefId($data) {
206 // gibt es die Nummer schon?
207 global $db,$file,$test;
208         if (empty($data) or !$data) {   
209                 return false; 
210         } 
211         $sql="select * from $file where ".$file."number = '$data'";
212         $rs=$db->getAll($sql);
213         return $rs[0]["id"];
214 }
215
216 function suchFirma($tab,$data) {
217 // gibt die Firma ?
218 global $db;
219         if (empty($data) or !$data) {   
220                 return false; 
221         }
222         $data=strtoupper($data);
223         $sql="select * from $tab where upper(name) like '%$data%'";
224         $rs=$db->getAll($sql);
225         if (!$rs) {
226                 $org=$data;
227                 while(strpos($data,"  ")>0) {
228                         $data=ereg_replace("  "," ",$data);
229                 }
230                 $data=preg_replace("/[^A-Z0-9]/ ",".*",trim($data));
231                 $sql="select * from $tab where upper(name) ~ '$data'"; 
232                 $rs=$db->getAll($sql);
233                 if (count($rs)==1) {
234                         return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
235                 }
236                 return false;
237         } else {
238                 return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
239         }
240 }
241
242 $land=array("DEUTSC"=>"D","FRANKR"=>"F","SPANIE"=>"ES","ITALIE"=>"I","HOLLAN"=>"NL","NIEDER"=>"NL",
243         "BELGIE"=>"B","LUXEMB"=>"L","NORWEG"=>"N","FINNLA"=>"","GRIECH"=>"GR","OESTER"=>"A",
244         "SCHWEI"=>"CH","SCHWED"=>"S","AUSTRI"=>"A");
245
246 function mkland($data) {
247 global $land;
248         $data=strtr($data,array("Ö"=>"OE","Ä"=>"AE","Ü"=>"UE","ö"=>"OE","ä"=>"AE","ü"=>"UE","ß"=>"SS"));
249         $data=strtoupper(substr($data,0,6));
250         $cntr=$land[$data];
251         return (strlen($cntr)>0)?$cntr:substr($data,0,3);
252 }
253
254 //Suche Nach Kunden-/Lieferantenummer
255 function getFirma($nummer,$tabelle) {
256 global $db;
257         $nummer=strtoupper($nummer);
258         $sql="select id from $tabelle where upper(".$tabelle."number) = '$nummer'";
259         $rs=$db->getAll($sql);
260         if (!$rs) {
261                 $nr=ereg_replace(" ","%",$nummer);
262                 $sql="select id,".$tabelle."number from $tabelle where upper(".$tabelle."number) like '$nr'";
263                 $rs=$db->getAll($sql);
264                 if ($rs) {
265                         $nr=ereg_replace(" ","",$nummer);
266                         foreach ($rs as $row) {
267                                 $tmp=ereg_replace(" ","",$row[$tabelle."number"]);
268                                 if ($tmp==$nr) return $row["id"];
269                         }
270                 } else { 
271                         return false;
272                 }
273         } else {
274                 return $rs[0]["id"];
275         }
276 }
277
278 function getAllBG($db) {
279         $sql  = "select * from buchungsgruppen order by description";
280         $rs=$db->getAll($sql);
281         return $rs;
282 }
283
284 class myDB extends DB {
285 // Datenbankklasse
286
287  var $rc = false;
288  var $showErr = false;
289  var $db = false;
290  var $debug = false;
291  var $logsql = false;
292  var $errfile = false;
293  var $logfile = false;
294
295
296 /****************************************************
297 * uudecode
298 * in: string
299 * out: string
300 * dekodiert Perl-UU-kodierte Passwort-Strings
301 * http://de3.php.net/base64_decode (bug #171)
302 *****************************************************/
303         function uudecode($encode) {
304           $encode=stripslashes($encode);
305           $b64chars="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
306
307           $encode = preg_replace("/^./m","",$encode);
308           $encode = preg_replace("/\n/m","",$encode);
309           for($i=0; $i<strlen($encode); $i++) {
310             if ($encode[$i] == '')
311               $encode[$i] = ' ';
312             $encode[$i] = $b64chars[ord($encode[$i])-32];
313           }
314    
315           while(strlen($encode) % 4)
316             $encode .= "=";
317
318           return base64_decode($encode);
319         }
320
321         function dbFehler($sql,$err) {
322                 if ($this->showErr)
323                         echo "$sql : $err\n";
324         }
325
326         function showDebug($sql) {      
327                 echo $sql."\n";
328                 if ($this->debug==2) {
329                         print_r($this->rc);
330                 };
331         }
332
333         function logSql($sql) {
334                 if (!$this->logfile)  $this->logfile=fopen("import.sql","a");
335                 fputs($this->logfile,$sql."\n");
336         }
337         function myDB($usr) {
338                 // Datenbankparameter des ERP-Users benutzen.
339                 $tmp = file_get_contents("../users/$usr.conf");
340                 preg_match("/dbname => '(.+)'/",$tmp,$hits);
341                 $dbname=$hits[1];
342                 preg_match("/dbpasswd => '(.+)'/",$tmp,$hits);
343                 if ($hits[1]) {
344                         $dbpasswd=$this->uudecode($hits[1]);
345                 } else {
346                         $dbpasswd="";
347                 };
348                 preg_match("/dbuser => '(.+)'/",$tmp,$hits);
349                 $dbuser=$hits[1];
350                 preg_match("/dbhost => '(.+)'/",$tmp,$hits);
351                 $dbhost=$hits[1];
352                 if (!$dbhost) $dbhost="localhost";
353                 if ($dbpasswd) {
354                         $dns=$dbuser.":".$dbpasswd."@".$dbhost."/".$dbname;
355                 } else {
356                         $dns=$dbuser."@".$dbhost."/".$dbname;
357                 };
358                 $dns="pgsql://".$dns;
359                 $this->db=DB::connect($dns);
360                 if (!$this->db) DB::dbFehler("oh oh oh",$this->db->getDebugInfo());
361                 if (DB::isError($this->db)) {
362                         $this->dbFehler("Connect",$this->db->getDebugInfo());
363                         die ($this->db->getDebugInfo());
364                 }
365                 return $this->db;
366         }
367
368         function query($sql) {
369                 $this->rc=@$this->db->query($sql);
370                 if ($this->logsql) $this->logSql($sql);
371                 if ($this->debug) $this->showDebug($sql);
372                 if(DB::isError($this->rc)) {
373                         $this->dbFehler($sql,$this->rc->getMessage());
374                         return false;
375                 } else {
376                         return $this->rc;
377                 }
378         }
379         function getAll($sql) {
380                 $this->rc=@$this->db->getAll($sql,DB_FETCHMODE_ASSOC);
381                 if ($this->logsql) $this->logSql($sql);
382                 if ($this->debug) $this->showDebug($sql);
383                 if(DB::isError($this->rc)) {
384                         $this->dbFehler($sql,$this->rc->getMessage());
385                         return false;
386                 } else {
387                         return $this->rc;
388                 }
389         }       
390
391         function lock() {
392                 $this->query("BEGIN");
393         }
394         function commit() {
395                 $this->query("COMMIT");
396         }
397         function rollback() {
398                 $this->query("ROLLBACK");
399         }
400         function chkcol($tbl) {
401         // gibt es die Spalte import schon?
402                 $rc=$this->db->query("select import from $tbl limit 1");
403                 if(DB::isError($rc)) {
404                         $rc=$this->db->query("alter table $tbl add column import int4");
405                         if(DB::isError($rc)) { return false; }
406                         else { return true; }
407                 
408                 } else { return true; };
409         }
410 }
411
412 ?>