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 //"language" => "Sprache (de,en,fr)",
41 "creditlimit" => "Kreditlimit (nnnnnn.nn)"); /*,
42 "hierarchie" => "Hierarchie",
43 "potenzial" => "Potenzial",
44 "ar" => "Debitorenkonto",
45 "ap" => "Kreditorenkonto",
46 "matchcode" => "Matchcode",
47 "customernumber2" => "Kundennummer 2");
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",
61 "shiptoemail" => "eMail",
62 "customernumber" => "Kundennummer",
63 "vendornumber" => "Lieferantennummer");
66 "partnumber" => "Artikelnummer",
67 "description" => "Artikeltext",
69 "weight" => "Gewicht in Benutzerdefinition",
70 "onhand" => "Lagerbestand",
71 "notes" => "Beschreibung",
72 "notes1" => "Beschreibung",
73 //"makemodel" => "Hersteller",
74 //"model" => "Modellbezeichnung",
76 "image" => "Pfad/Dateiname",
77 "drawing" => "Pfad/Dateiname",
78 "microfiche" => "Pfad/Dateiname",
79 "listprice" => "Listenpreis",
80 "sellprice" => "Verkaufspreis",
81 "lastcost" => "letzter EK",
82 "art" => "Ware/Dienstleistung (*/d), muß vor den Konten kommen",
83 "inventory_accno" => "Bestandskonto",
84 "income_accno" => "Erlöskonto",
85 "expense_accno" => "Konto Umsatzkosten",
86 "obsolete" => "Gesperrt (Y/N)",
87 "lastcost" => "letzer EK-Preis",
88 "rop" => "Mindestbestand",
89 "shop" => "Shopartikel (Y/N)",
90 "assembly" => "Stückliste (Y/N); wird noch nicht unterstützt",
91 "partsgroup" => "Warengruppenbezeichnung",
92 "partsgroup1" => "2.Warengruppenbezeichnung",
93 "partsgroup2" => "3.Warengruppenbezeichnung",
94 "partsgroup3" => "4.Warengruppenbezeichnung",
95 "partsgroup4" => "5.Warengruppenbezeichnung",
96 //"income_accno_0" => "?Nummer? für Erlöse Inland",
97 //"income_accno_1" => "?Nummer? für Erlöse EG",
98 //"income_accno_3" => "?Nummer? für Erlöse Ausland",
101 $contactscrm = array(
102 "customernumber" => "Kundennummer",
103 "vendornumber" => "Lieferantennummer",
104 "cp_cv_id" => "FirmenID in der db",
105 "firma" => "Firmenname",
106 "cp_abteilung" => "Abteilung",
107 "cp_position" => "Position/Hierarchie",
108 "cp_greeting" => "Anrede",
109 "cp_title" => "Titel",
110 "cp_givenname" => "Vorname",
111 "cp_name" => "Nachname",
112 "cp_email" => "eMail",
113 "cp_phone1" => "Telefon 1",
114 "cp_phone2" => "Telefon 2",
115 "cp_mobile1" => "Mobiltelefon 1",
116 "cp_mobile2" => "Mobiltelefon 2",
117 "cp_homepage" => "Homepage",
118 "cp_street" => "Strasse",
119 "cp_country" => "Land",
120 "cp_zipcode" => "PLZ",
122 "cp_privatphone" => "Privattelefon",
123 "cp_privatemail" => "private eMail",
124 "cp_notes" => "Bemerkungen",
125 "cp_stichwort1" => "Stichwort(e)",
126 "cp_id" => "Kontakt ID"
130 "customernumber" => "Kundennummer",
131 "vendornumber" => "Lieferantennummer",
132 "cp_cv_id" => "FirmenID in der db",
133 "firma" => "Firmenname",
134 "cp_greeting" => "Anrede",
135 "cp_title" => "Titel",
136 "cp_givenname" => "Vorname",
137 "cp_greeting" => "Anrede",
138 "cp_name" => "Nachname",
139 "cp_email" => "eMail",
140 "cp_phone1" => "Telefon 1",
141 "cp_phone2" => "Telefon 2",
142 "cp_mobile1" => "Mobiltelefon 1",
143 "cp_mobile2" => "Mobiltelefon 2",
144 "cp_privatphone" => "Privattelefon",
145 "cp_privatemail" => "private eMail",
146 "cp_homepage" => "Homepage",
147 "cp_id" => "Kontakt ID"
150 function checkCRM() {
152 $sql="select * from crm";
153 $rs=$db->getAll($sql);
161 function chkUsr($usr) {
162 // ist es ein gültiger ERP-Benutzer? Er muß mindestens 1 x angemeldet gewesen sein.
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; };
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'";
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));
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
204 function chkContact($id) {
206 $sql="select * from contact where cp_id = $id";
207 $rs=$db->getAll($sql);
208 if ($rs[0]["cp_id"]==$id) {
214 function getKdRefId($data) {
215 // gibt es die Nummer schon?
216 global $db,$file,$test;
217 if (empty($data) or !$data) {
220 $sql="select * from $file where ".$file."number = '$data'";
221 $rs=$db->getAll($sql);
225 function suchFirma($tab,$data) {
228 if (empty($data) or !$data) {
231 $data=strtoupper($data);
232 $sql="select * from $tab where upper(name) like '%$data%'";
233 $rs=$db->getAll($sql);
236 while(strpos($data," ")>0) {
237 $data=ereg_replace(" "," ",$data);
239 $data=preg_replace("/[^A-Z0-9]/ ",".*",trim($data));
240 $sql="select * from $tab where upper(name) ~ '$data'";
241 $rs=$db->getAll($sql);
243 return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
247 return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
251 $land=array("DEUTSC"=>"D","FRANKR"=>"F","SPANIE"=>"ES","ITALIE"=>"I","HOLLAN"=>"NL","NIEDER"=>"NL",
252 "BELGIE"=>"B","LUXEMB"=>"L","NORWEG"=>"N","FINNLA"=>"","GRIECH"=>"GR","OESTER"=>"A",
253 "SCHWEI"=>"CH","SCHWED"=>"S","AUSTRI"=>"A");
255 function mkland($data) {
257 $data=strtr($data,array("Ö"=>"OE","Ä"=>"AE","Ü"=>"UE","ö"=>"OE","ä"=>"AE","ü"=>"UE","ß"=>"SS"));
258 $data=strtoupper(substr($data,0,6));
260 return (strlen($cntr)>0)?$cntr:substr($data,0,3);
263 //Suche Nach Kunden-/Lieferantenummer
264 function getFirma($nummer,$tabelle) {
266 $nummer=strtoupper($nummer);
267 $sql="select id from $tabelle where upper(".$tabelle."number) = '$nummer'";
268 $rs=$db->getAll($sql);
270 $nr=ereg_replace(" ","%",$nummer);
271 $sql="select id,".$tabelle."number from $tabelle where upper(".$tabelle."number) like '$nr'";
272 $rs=$db->getAll($sql);
274 $nr=ereg_replace(" ","",$nummer);
275 foreach ($rs as $row) {
276 $tmp=ereg_replace(" ","",$row[$tabelle."number"]);
277 if ($tmp==$nr) return $row["id"];
287 function getAllBG($db) {
288 $sql = "select * from buchungsgruppen order by description";
289 $rs=$db->getAll($sql);
292 function anmelden() {
293 ini_set("gc_maxlifetime","3600");
294 $tmp = @file_get_contents("../config/authentication.pl");
295 preg_match("/'db'[ ]*=> '(.+)'/",$tmp,$hits);
297 preg_match("/'password'[ ]*=> '(.+)'/",$tmp,$hits);
299 preg_match("/'user'[ ]*=> '(.+)'/",$tmp,$hits);
301 preg_match("/'host'[ ]*=> '(.+)'/",$tmp,$hits);
302 $dbhost=($hits[1])?$hits[1]:"localhost";
303 preg_match("/'port'[ ]*=> '(.+)'/",$tmp,$hits);
304 $dbport=($hits[1])?$hits[1]:"5432";
305 preg_match("/^[ ]*\$self->\{cookie_name\}[ ]*=[ ]*'(.+)'/",$tmp,$hits);
306 $cookiename=$hits[1];
307 if (!$cookiename) $cookiename='lx_office_erp_session_id';
308 $cookie=$_COOKIE[$cookiename];
309 if (!$cookie) header("location: ups.html");
310 $auth=authuser($dbhost,$dbport,$dbuser,$dbpasswd,$dbname,$cookie);
311 if (!$auth) { return false; };
312 $_SESSION["sessid"]=$cookie;
313 $_SESSION["cookie"]=$cookiename;
314 $_SESSION["employee"]=$auth["login"];
315 $_SESSION["mansel"]=$auth["dbname"];
316 $_SESSION["dbname"]=$auth["dbname"];
317 $_SESSION["dbhost"]=(!$auth["dbhost"])?"localhost":$auth["dbhost"];
318 $_SESSION["dbport"]=(!$auth["dbport"])?"5432":$auth["dbport"];
319 $_SESSION["dbuser"]=$auth["dbuser"];
320 $_SESSION["dbpasswd"]=$auth["dbpasswd"];
321 $_SESSION["db"]=new myDB($_SESSION["dbhost"],$_SESSION["dbuser"],$_SESSION["dbpasswd"],$_SESSION["dbname"],$_SESSION["dbport"],$showErr);
322 $_SESSION["authcookie"]=$authcookie;
323 $sql="select * from employee where login='".$auth["login"]."'";
324 $rs=$_SESSION["db"]->getAll($sql);
330 $_SESSION["termbegin"]=(($tmp["termbegin"]>=0)?$tmp["termbegin"]:8);
331 $_SESSION["termend"]=($tmp["termend"])?$tmp["termend"]:19;
332 $_SESSION["Pre"]=$tmp["pre"];
333 $_SESSION["interv"]=($tmp["interv"]>0)?$tmp["interv"]:60;
334 $_SESSION["loginCRM"]=$tmp["id"];
335 $_SESSION["lang"]=$tmp["countrycode"]; //"de";
336 $_SESSION["kdview"]=$tmp["kdview"];
337 $sql="select * from defaults";
338 $rs=$_SESSION["db"]->getAll($sql);
339 $_SESSION["ERPver"]=$rs[0]["version"];
347 function authuser($dbhost,$dbport,$dbuser,$dbpasswd,$dbname,$cookie) {
348 $db=new myDB($dbhost,$dbuser,$dbpasswd,$dbname,$dbport,true);
349 $sql="select sc.session_id,u.id from auth.session_content sc left join auth.user u on ";
350 $sql.="u.login=sc.sess_value left join auth.session s on s.id=sc.session_id ";
351 $sql.="where session_id = '$cookie' and sc.sess_key='login'";// order by s.mtime desc";
352 $rs=$db->getAll($sql,"authuser_1");
353 if (!$rs) return false;
356 header("location:../login.pl?action=logout");
357 /*foreach($rs as $row) {
358 $stmp.=$row["session_id"].",";
360 $sql1="delete from session where id in (".substr($stmp,-1).")";
361 $sql2="delete from session_content where session_id in (".substr($stmp,-1).")";
362 $db->query($sql1,"authuser_A");
363 $db->query($sql2,"authuser_B");
364 $sql3="insert into session ";*/
366 $sql="select * from auth.user where id=".$rs[0]["id"];
367 $rs1=$db->getAll($sql,"authuser_1");
368 if (!$rs1) return false;
370 $auth["login"]=$rs1[0]["login"];
371 $sql="select * from auth.user_config where user_id=".$rs[0]["id"];
372 $rs1=$db->getAll($sql,"authuser_2");
373 $keys=array("dbname","dbpasswd","dbhost","dbport","dbuser");
374 foreach ($rs1 as $row) {
375 if (in_array($row["cfg_key"],$keys)) {
376 $auth[$row["cfg_key"]]=$row["cfg_value"];
379 $sql="update auth.session set mtime = '".date("Y-M-d H:i:s.100001")."' where id = '".$rs[0]["session_id"]."'";
380 $db->query($sql,"authuser_3");