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 "ustid" => "Umsatzsteuer-ID",
35 "account_number" => "Kontonummer",
36 "bank_code" => "Bankleitzahl",
38 "branche" => "Branche",
39 "business_id" => "BranchenID",
40 "salesman_id" => "VerkäuferID",
41 //"language" => "Sprache (de,en,fr)",
43 "creditlimit" => "Kreditlimit (nnnnnn.nn)"); /*,
44 "hierarchie" => "Hierarchie",
45 "potenzial" => "Potenzial",
46 "ar" => "Debitorenkonto",
47 "ap" => "Kreditorenkonto",
48 "matchcode" => "Matchcode",
49 "customernumber2" => "Kundennummer 2");
53 "firma" => "Firmenname",
54 "shiptoname" => "Liefername",
55 "shiptodepartment_1" => "Abteilung",
56 "shiptodepartment_2" => "Abteilung",
57 "shiptostreet" => "Strasse + Nr",
58 "shiptozipcode" => "Plz",
59 "shiptocity" => "Ort",
60 "shiptocountry" => "Land",
61 "shiptocontact" => "Ansprechpartner",
62 "shiptophone" => "Telefon",
64 "shiptoemail" => "eMail",
65 "customernumber" => "Kundennummer",
66 "vendornumber" => "Lieferantennummer");
69 "partnumber" => "Artikelnummer",
71 "description" => "Artikeltext",
73 "weight" => "Gewicht in Benutzerdefinition",
74 "notes" => "Beschreibung",
75 "notes1" => "Beschreibung",
77 "makemodel" => "Hersteller",
78 "model" => "Modellbezeichnung",
79 "image" => "Pfad/Dateiname",
80 "drawing" => "Pfad/Dateiname",
81 "microfiche" => "Pfad/Dateiname",
82 "listprice" => "Listenpreis",
83 "sellprice" => "Verkaufspreis",
84 "lastcost" => "letzter EK",
85 "art" => "Ware/Dienstleistung (*/d), muß vor den Konten kommen",
86 "inventory_accno" => "Bestandskonto",
87 "income_accno" => "Erlöskonto",
88 "expense_accno" => "Konto Umsatzkosten",
89 "obsolete" => "Gesperrt (Y/N)",
90 "lastcost" => "letzer EK-Preis",
91 "rop" => "Mindestbestand",
92 "shop" => "Shopartikel (Y/N)",
93 "assembly" => "Stückliste (Y/N); wird noch nicht unterstützt",
94 "partsgroup" => "Warengruppenbezeichnung",
95 "partsgroup1" => "2.Warengruppenbezeichnung",
96 "partsgroup2" => "3.Warengruppenbezeichnung",
97 "partsgroup3" => "4.Warengruppenbezeichnung",
98 "partsgroup4" => "5.Warengruppenbezeichnung",
99 "shop" => "Shopexport vorghesehen",
102 $contactscrm = array(
103 "customernumber" => "Kundennummer",
104 "vendornumber" => "Lieferantennummer",
105 "cp_cv_id" => "FirmenID in der db",
106 "firma" => "Firmenname",
107 "cp_abteilung" => "Abteilung",
108 "cp_position" => "Position/Hierarchie",
109 "cp_gender" => "Geschlecht (m/f)",
110 "cp_title" => "Titel",
111 "cp_givenname" => "Vorname",
112 "cp_name" => "Nachname",
113 "cp_email" => "eMail",
114 "cp_phone1" => "Telefon 1",
115 "cp_phone2" => "Telefon 2",
116 "cp_mobile1" => "Mobiltelefon 1",
117 "cp_mobile2" => "Mobiltelefon 2",
118 "cp_homepage" => "Homepage",
119 "cp_street" => "Strasse",
120 "cp_country" => "Land",
121 "cp_zipcode" => "PLZ",
123 "cp_privatphone" => "Privattelefon",
124 "cp_privatemail" => "private eMail",
125 "cp_notes" => "Bemerkungen",
126 "cp_stichwort1" => "Stichwort(e)",
127 "cp_id" => "Kontakt ID"
131 "customernumber" => "Kundennummer",
132 "vendornumber" => "Lieferantennummer",
133 "cp_cv_id" => "FirmenID in der db",
134 "firma" => "Firmenname",
135 "cp_greeting" => "Anrede",
136 "cp_title" => "Titel",
137 "cp_givenname" => "Vorname",
138 "cp_greeting" => "Anrede",
139 "cp_name" => "Nachname",
140 "cp_email" => "eMail",
141 "cp_phone1" => "Telefon 1",
142 "cp_phone2" => "Telefon 2",
143 "cp_mobile1" => "Mobiltelefon 1",
144 "cp_mobile2" => "Mobiltelefon 2",
145 "cp_privatphone" => "Privattelefon",
146 "cp_privatemail" => "private eMail",
147 "cp_homepage" => "Homepage",
148 "cp_id" => "Kontakt ID"
151 function checkCRM() {
153 $sql="select * from crm";
154 $rs=$db->getAll($sql);
162 function chkUsr($usr) {
163 // ist es ein gültiger ERP-Benutzer? Er muß mindestens 1 x angemeldet gewesen sein.
165 $sql="select * from employee where login = '$usr'";
166 $rs=$db->getAll($sql);
167 if ($rs[0]["id"]) { return $rs[0]["id"]; }
168 else { return false; };
172 // die nächste freie Kunden-/Lieferantennummer holen
173 global $db,$file,$test;
174 if ($test) { return "#####"; }
175 $sql1="select * from defaults";
176 $sql2="update defaults set ".$file."number = '%s'";
178 $rs=$db->getAll($sql1);
179 $nr=$rs[0][$file."number"];
180 preg_match("/^([^0-9]*)([0-9]+)/",$nr,$hits);
181 if ($hits[2]) { $nr=$hits[2]+1; $nnr=$hits[1].$nr; }
182 else { $nr=$hits[1]+1; $nnr=$nr; };
183 $rc=$db->query(sprintf($sql2,$nnr));
193 function chkKdId($data) {
194 // gibt es die Nummer schon?
195 global $db,$file,$test;
196 $sql="select * from $file where ".$file."number = '$data'";
197 $rs=$db->getAll($sql);
198 if ($rs[0][$file."number"]==$data) {
199 // ja, eine neue holen
206 function chkContact($id) {
208 $sql="select * from contact where cp_id = $id";
209 $rs=$db->getAll($sql);
210 if ($rs[0]["cp_id"]==$id) {
217 function getKdRefId($data) {
218 // gibt es die Nummer schon?
219 global $db,$file,$test;
220 if (empty($data) or !$data) {
223 $sql="select * from $file where ".$file."number = '$data'";
224 $rs=$db->getAll($sql);
228 * TODO: short description.
237 function insertExtra($tab,$own,$fld,$val) {
239 $sql = "insert into extra_felder (fkey,fval,tab,owner) ";
240 $sql.= "values ('$fld','$val','$tab','$own')";
241 $rc = $db->query($sql);
244 function suchFirma($tab,$data) {
247 if (empty($data) or !$data) {
250 $data=strtoupper($data);
251 $sql="select * from $tab where upper(name) like '%$data%'";
252 $rs=$db->getAll($sql);
255 while(strpos($data," ")>0) {
256 $data=str_replace(" "," ",$data);
258 $data=preg_replace("/[^A-Z0-9]/ ",".*",trim($data));
259 $sql="select * from $tab where upper(name) ~ '$data'";
260 $rs=$db->getAll($sql);
262 return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
266 return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
271 //Suche Nach Kunden-/Lieferantenummer
272 function getFirma($nummer,$tabelle) {
274 $nummer=strtoupper($nummer);
275 $sql="select id from $tabelle where upper(".$tabelle."number) = '$nummer'";
276 $rs=$db->getAll($sql);
278 $nr=str_replace(" ","%",$nummer);
279 $sql="select id,".$tabelle."number from $tabelle where upper(".$tabelle."number) like '$nr'";
280 $rs=$db->getAll($sql);
282 $nr=str_replace(" ","",$nummer);
283 foreach ($rs as $row) {
284 $tmp=str_replace(" ","",$row[$tabelle."number"]);
285 if ($tmp==$nr) return $row["id"];
295 function getAllBG($db) {
296 $sql = "select * from buchungsgruppen order by description";
297 $rs=$db->getAll($sql);
301 function getAllUnits($db,$type) {
302 $sql = "select * from units where type = '$type' order by sortkey";
303 $rs=$db->getAll($sql);
307 function anmelden($login=false,$pwd=false) {
308 ini_set("gc_maxlifetime","3600");
309 if (file_exists("../config/lx_office.conf")) {
310 $lxo = fopen("../config/lx_office.conf","r");
311 } else if (file_exists("../config/lx_office.conf.default")) {
312 $lxo = fopen("../config/lx_office.conf.default","r");
317 $tmp = fgets($lxo,512);
318 while (!feof($lxo)) {
319 if (preg_match("/^[\s]*#/",$tmp)) {
320 $tmp = fgets($lxo,512);
324 preg_match("/db[ ]*= (.+)/",$tmp,$hits);
325 if ($hits[1]) $dbname=$hits[1];
326 preg_match("/password[ ]*= (.+)/",$tmp,$hits);
327 if ($hits[1]) $dbpasswd=$hits[1];
328 preg_match("/user[ ]*= (.+)/",$tmp,$hits);
329 if ($hits[1]) $dbuser=$hits[1];
330 preg_match("/host[ ]*= (.+)/",$tmp,$hits);
331 if ($hits[1]) $dbhost=($hits[1])?$hits[1]:"localhost";
332 preg_match("/port[ ]*= (.+)/",$tmp,$hits);
333 if ($hits[1]) $dbport=($hits[1])?$hits[1]:"5432";
334 if (preg_match("/\[[a-z]+/",$tmp)) break;
335 $tmp = fgets($lxo,512);
338 preg_match("/[ ]*cookie_name[ ]*=[ ]*(.+)/",$tmp,$hits);
339 if ($hits[1]) $cookiename=$hits[1];
340 if (preg_match("!\[authentication/database\]!",$tmp)) $dbsec = true;
341 $tmp = fgets($lxo,512);
343 if (!$cookiename) $cookiename='lx_office_erp_session_id';
344 $cookie=$_COOKIE[$cookiename];
346 $auth=authuser($dbhost,$dbport,$dbuser,$dbpasswd,$dbname,false,$login,$pwd);
348 $cookie=$_COOKIE[$cookiename];
349 if (!$cookie) header("location: ups.html");
350 $auth=authuser($dbhost,$dbport,$dbuser,$dbpasswd,$dbname,$cookie,false,false);
352 if (!$auth) { return false; };
353 $_SESSION["sessid"]=$cookie;
354 $_SESSION["cookie"]=$cookiename;
355 $_SESSION["employee"]=$auth["login"];
356 $_SESSION["mansel"]=$auth["dbname"];
357 $_SESSION["dbname"]=$auth["dbname"];
358 $_SESSION["dbhost"]=(!$auth["dbhost"])?"localhost":$auth["dbhost"];
359 $_SESSION["dbport"]=(!$auth["dbport"])?"5432":$auth["dbport"];
360 $_SESSION["dbuser"]=$auth["dbuser"];
361 $_SESSION["dbpasswd"]=$auth["dbpasswd"];
362 $_SESSION["db"]=new myDB($_SESSION["dbhost"],$_SESSION["dbuser"],$_SESSION["dbpasswd"],$_SESSION["dbname"],$_SESSION["dbport"],$showErr);
363 $_SESSION["authcookie"]=$authcookie;
364 $sql="select * from employee where login='".$auth["login"]."'";
365 $rs=$_SESSION["db"]->getAll($sql);
371 $_SESSION["termbegin"]=(($tmp["termbegin"]>=0)?$tmp["termbegin"]:8);
372 $_SESSION["termend"]=($tmp["termend"])?$tmp["termend"]:19;
373 $_SESSION["Pre"]=$tmp["pre"];
374 $_SESSION["interv"]=($tmp["interv"]>0)?$tmp["interv"]:60;
375 $_SESSION["loginCRM"]=$tmp["id"];
376 $_SESSION["lang"]=$tmp["countrycode"]; //"de";
377 $_SESSION["kdview"]=$tmp["kdview"];
378 $sql="select * from defaults";
379 $rs=$_SESSION["db"]->getAll($sql);
380 $_SESSION["ERPver"]=$rs[0]["version"];
381 return true;$_SESSION;
388 function authuser($dbhost,$dbport,$dbuser,$dbpasswd,$dbname,$cookie,$login,$pwd) {
389 $db=new myDB($dbhost,$dbuser,$dbpasswd,$dbname,$dbport,true);
391 $sql="select sc.session_id,u.id from auth.session_content sc left join auth.user u on ";
392 $sql.="('--- ' || u.login || E'\\n')=sc.sess_value left join auth.session s on s.id=sc.session_id ";
393 $sql.="where session_id = '$cookie' and sc.sess_key='login'";// order by s.mtime desc";
394 $rs=$db->getAll($sql,"authuser_1");
395 if (!$rs) return false;
398 foreach($rs as $row) {
399 $stmp.=$row["session_id"].",";
401 $sql1="delete from session where id in (".substr($stmp,-1).")";
402 $sql2="delete from session_content where session_id in (".substr($stmp,-1).")";
403 $db->query($sql1,"authuser_A");
404 $db->query($sql2,"authuser_B");
405 header("location:../login.pl?action=logout");
407 $sql="update auth.session set mtime = '".date("Y-M-d H:i:s.100001")."' where id = '".$rs[0]["session_id"]."'";
408 $db->query($sql,"authuser_3");
409 $sql="select * from auth.user where id=".$rs[0]["id"];
411 $sql = "select * from auth.user where login = '$login' and password = '$pwd'";
413 $rs1=$db->getAll($sql,"authuser_1");
414 if (!$rs1) return false;
416 $auth["login"]=$rs1[0]["login"];
417 $sql="select * from auth.user_config where user_id=".$rs1[0]["id"];
418 $rs1=$db->getAll($sql,"authuser_2");
419 $keys=array("dbname","dbpasswd","dbhost","dbport","dbuser");
420 foreach ($rs1 as $row) {
421 if (in_array($row["cfg_key"],$keys)) {
422 $auth[$row["cfg_key"]]=$row["cfg_value"];
429 * Zeichencode übersetzen
433 function translate(&$txt) {
435 $encoding = mb_detect_encoding($data,"UTF-8,ISO-8859-1,ISO-8859-15,Windows-1252,ASCII");
436 $txt = iconv("$encoding",ServerCode."//TRANSLIT",$txt);
437 //$txt = mb_convert_encoding($txt, ServerCode,"$encoding");
439 $txt = iconv(FileCode,ServerCode."//TRANSLIT",$txt);
440 //$txt = mb_convert_encoding($txt, ServerCode,FileCode);