Filterkriterium "Kunden-/Lieferantenname" in Berichten auch beim Umsortieren beibehal...
[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         //"income_accno_0" => "?Nummer? für Erlöse Inland",
92         //"income_accno_1" => "?Nummer? für Erlöse EG",
93         //"income_accno_3" => "?Nummer? für Erlöse Ausland",
94         );
95         
96 $contactscrm = array(
97         "customernumber" => "Kundennummer",
98         "vendornumber" => "Lieferantennummer",
99         "cp_cv_id" => "FirmenID in der db",
100         "firma" => "Firmenname",
101         "cp_abteilung" => "Abteilung",
102         "cp_position" => "Position/Hierarchie",
103         "cp_greeting" => "Anrede",
104         "cp_title" => "Titel",
105         "cp_givenname" => "Vorname",
106         "cp_name" => "Nachname",
107         "cp_email" => "eMail",
108         "cp_phone1" => "Telefon 1",
109         "cp_phone2" => "Telefon 2",
110         "cp_mobile1" => "Mobiltelefon 1",
111         "cp_mobile2" => "Mobiltelefon 2",
112         "cp_homepage" => "Homepage",
113         "cp_street" => "Strasse",
114         "cp_country" => "Land",
115         "cp_zipcode" => "PLZ",
116         "cp_city" => "Ort",
117         "cp_privatphone" => "Privattelefon",
118         "cp_privatemail" => "private eMail",
119         "cp_notes" => "Bemerkungen",
120         "cp_stichwort1" => "Stichwort(e)",
121         "katalog" => "Katalog",
122         "inhaber" => "Inhaber",
123         "contact_id" => "Kontakt ID"
124         );
125
126 $contacts = array(
127         "customernumber" => "Kundennummer",
128         "vendornumber" => "Lieferantennummer",
129         "cp_cv_id" => "FirmenID in der db",
130         "firma" => "Firmenname",
131         "cp_greeting" => "Anrede",
132         "cp_title" => "Titel",
133         "cp_givenname" => "Vorname",
134         "cp_greeting" => "Anrede",
135         "cp_name" => "Nachname",
136         "cp_email" => "eMail",
137         "cp_phone1" => "Telefon 1",
138         "cp_phone2" => "Telefon 2",
139         "cp_mobile1" => "Mobiltelefon 1",
140         "cp_mobile2" => "Mobiltelefon 2",
141         "cp_privatphone" => "Privattelefon",
142         "cp_privatemail" => "private eMail",
143         "cp_homepage" => "Homepage",
144         "katalog" => "Katalog",
145         "inhaber" => "Inhaber",
146         "contact_id" => "Kontakt ID"
147         );
148
149 function checkCRM() {
150 global $db;
151         $sql="select * from crm";
152         $rs=$db->getAll($sql);
153         if ($rs) {
154                 return true;
155         } else {
156                 return false;
157         }
158 }
159
160 function chkUsr($usr) {
161 // ist es ein gültiger ERP-Benutzer? Er muß mindestens 1 x angemeldet gewesen sein.
162 global $db;
163         $sql="select * from employee where login = '$usr'";
164         $rs=$db->getAll($sql);
165         if ($rs[0]["id"]) { return $rs[0]["id"]; } 
166         else { return false; };
167 }
168
169 function getKdId() {
170 // die nächste freie Kunden-/Lieferantennummer holen
171 global $db,$file,$test;
172         if ($test) { return "#####"; }
173         $sql1="select * from defaults";
174         $sql2="update defaults set ".$file."number = '%s'";
175         $db->lock();
176         $rs=$db->getAll($sql1);
177         $nr=$rs[0][$file."number"];
178         preg_match("/^([^0-9]*)([0-9]+)/",$nr,$hits);
179         if ($hits[2]) { $nr=$hits[2]+1; $nnr=$hits[1].$nr; }
180         else { $nr=$hits[1]+1; $nnr=$nr; };
181         $rc=$db->query(sprintf($sql2,$nnr));
182         if ($rc) { 
183                 $db->commit(); 
184                 return $nnr;
185         } else { 
186                 $db->rollback(); 
187                 return false;
188         };
189 }
190
191 function chkKdId($data) {
192 // gibt es die Nummer schon?
193 global $db,$file,$test;
194         $sql="select * from $file where ".$file."number = '$data'";
195         $rs=$db->getAll($sql);
196         if ($rs[0][$file."number"]==$data) {
197                 // ja, eine neue holen
198                 return getKdId();
199         } else {
200                 return $data;
201         }
202 }
203
204 function getKdRefId($data) {
205 // gibt es die Nummer schon?
206 global $db,$file,$test;
207         if (empty($data) or !$data) {   
208                 return false; 
209         } 
210         $sql="select * from $file where ".$file."number = '$data'";
211         $rs=$db->getAll($sql);
212         return $rs[0]["id"];
213 }
214
215 function suchFirma($tab,$data) {
216 // gibt die Firma ?
217 global $db;
218         if (empty($data) or !$data) {   
219                 return false; 
220         }
221         $data=strtoupper($data);
222         $sql="select * from $tab where upper(name) like '%$data%'";
223         $rs=$db->getAll($sql);
224         if (!$rs) {
225                 $org=$data;
226                 while(strpos($data,"  ")>0) {
227                         $data=ereg_replace("  "," ",$data);
228                 }
229                 $data=preg_replace("/[^A-Z0-9]/ ",".*",trim($data));
230                 $sql="select * from $tab where upper(name) ~ '$data'"; 
231                 $rs=$db->getAll($sql);
232                 if (count($rs)==1) {
233                         return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
234                 }
235                 return false;
236         } else {
237                 return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
238         }
239 }
240
241 $land=array("DEUTSC"=>"D","FRANKR"=>"F","SPANIE"=>"ES","ITALIE"=>"I","HOLLAN"=>"NL","NIEDER"=>"NL",
242         "BELGIE"=>"B","LUXEMB"=>"L","NORWEG"=>"N","FINNLA"=>"","GRIECH"=>"GR","OESTER"=>"A",
243         "SCHWEI"=>"CH","SCHWED"=>"S","AUSTRI"=>"A");
244
245 function mkland($data) {
246 global $land;
247         $data=strtr($data,array("Ö"=>"OE","Ä"=>"AE","Ü"=>"UE","ö"=>"OE","ä"=>"AE","ü"=>"UE","ß"=>"SS"));
248         $data=strtoupper(substr($data,0,6));
249         $cntr=$land[$data];
250         return (strlen($cntr)>0)?$cntr:substr($data,0,3);
251 }
252
253 //Suche Nach Kunden-/Lieferantenummer
254 function getFirma($nummer,$tabelle) {
255 global $db;
256         $nummer=strtoupper($nummer);
257         $sql="select id from $tabelle where upper(".$tabelle."number) = '$nummer'";
258         $rs=$db->getAll($sql);
259         if (!$rs) {
260                 $nr=ereg_replace(" ","%",$nummer);
261                 $sql="select id,".$tabelle."number from $tabelle where upper(".$tabelle."number) like '$nr'";
262                 $rs=$db->getAll($sql);
263                 if ($rs) {
264                         $nr=ereg_replace(" ","",$nummer);
265                         foreach ($rs as $row) {
266                                 $tmp=ereg_replace(" ","",$row[$tabelle."number"]);
267                                 if ($tmp==$nr) return $row["id"];
268                         }
269                 } else { 
270                         return false;
271                 }
272         } else {
273                 return $rs[0]["id"];
274         }
275 }
276
277 function getAllBG($db) {
278         $sql  = "select * from buchungsgruppen order by description";
279         $rs=$db->getAll($sql);
280         return $rs;
281 }
282
283 class myDB extends DB {
284 // Datenbankklasse
285
286  var $rc = false;
287  var $showErr = false;
288  var $db = false;
289  var $debug = false;
290
291 /****************************************************
292 * uudecode
293 * in: string
294 * out: string
295 * dekodiert Perl-UU-kodierte Passwort-Strings
296 * http://de3.php.net/base64_decode (bug #171)
297 *****************************************************/
298         function uudecode($encode) {
299           $b64chars="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
300
301           $encode = preg_replace("/^./m","",$encode);
302           $encode = preg_replace("/\n/m","",$encode);
303           for($i=0; $i<strlen($encode); $i++) {
304             if ($encode[$i] == '')
305               $encode[$i] = ' ';
306             $encode[$i] = $b64chars[ord($encode[$i])-32];
307           }
308    
309           while(strlen($encode) % 4)
310             $encode .= "=";
311
312           return base64_decode($encode);
313         }
314
315         function dbFehler($sql,$err) {
316                 if ($this->showErr)
317                         echo "$sql : $err\n";
318         }
319
320         function showDebug($sql) {
321                 echo $sql."\n";
322                 if ($this->debug==2) {
323                         print_r($this->rc);
324                 };
325         }
326
327         function myDB($usr) {
328                 // Datenbankparameter des ERP-Users benutzen.
329                 $tmp = file_get_contents("../users/$usr.conf");
330                 preg_match("/dbname => '(.+)'/",$tmp,$hits);
331                 $dbname=$hits[1];
332                 preg_match("/dbpasswd => '(.+)'/",$tmp,$hits);
333                 if ($hits[1]) {
334                         $dbpasswd=$this->uudecode($hits[1]);
335                 } else {
336                         $dbpasswd="";
337                 };
338                 preg_match("/dbuser => '(.+)'/",$tmp,$hits);
339                 $dbuser=$hits[1];
340                 preg_match("/dbhost => '(.+)'/",$tmp,$hits);
341                 $dbhost=$hits[1];
342                 if (!$dbhost) $dbhost="localhost";
343                 if ($dbpasswd) {
344                         $dns=$dbuser.":".$dbpasswd."@".$dbhost."/".$dbname;
345                 } else {
346                         $dns=$dbuser."@".$dbhost."/".$dbname;
347                 };
348                 $dns="pgsql://".$dns;
349                 $this->db=DB::connect($dns);
350                 if (!$this->db) DB::dbFehler("oh oh oh",$this->db->getDebugInfo());
351                 if (DB::isError($this->db)) {
352                         $this->dbFehler("Connect",$this->db->getDebugInfo());
353                         die ($this->db->getDebugInfo());
354                 }
355                 return $this->db;
356         }
357
358         function query($sql) {
359                 $this->rc=@$this->db->query($sql);
360                 if ($this->debug) $this->showDebug($sql);
361                 if(DB::isError($this->rc)) {
362                         $this->dbFehler($sql,$this->rc->getMessage());
363                         return false;
364                 } else {
365                         return $this->rc;
366                 }
367         }
368         function getAll($sql) {
369                 $this->rc=@$this->db->getAll($sql,DB_FETCHMODE_ASSOC);
370                 if ($this->debug) $this->showDebug($sql);
371                 if(DB::isError($this->rc)) {
372                         $this->dbFehler($sql,$this->rc->getMessage());
373                         return false;
374                 } else {
375                         return $this->rc;
376                 }
377         }       
378
379         function lock() {
380                 $this->query("BEGIN");
381         }
382         function commit() {
383                 $this->query("COMMIT");
384         }
385         function rollback() {
386                 $this->query("ROLLBACK");
387         }
388         function chkcol($tbl) {
389         // gibt es die Spalte import schon?
390                 $rc=$this->db->query("select import from $tbl limit 1");
391                 if(DB::isError($rc)) {
392                         $rc=$this->db->query("alter table $tbl add column import int4");
393                         if(DB::isError($rc)) { return false; }
394                         else { return true; }
395                 
396                 } else { return true; };
397         }
398 }
399
400 ?>