lxo-import/import_lib.php

   1 <?php
   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         "bin"   => "Lagerplatz",
  72         "notes" => "Beschreibung",
  73         "notes1" => "Beschreibung",
  74         //"makemodel" => "Hersteller",
  75         //"model" => "Modellbezeichnung",
  76         "bin" => "Lagerort",
  77         "image" => "Pfad/Dateiname",
  78         "drawing" => "Pfad/Dateiname",
  79         "microfiche" => "Pfad/Dateiname",
  80         "listprice" => "Listenpreis",
  81         "sellprice" => "Verkaufspreis",
  82         "lastcost" => "letzter EK",
  83         "art" => "Ware/Dienstleistung (*/d), mu&szlig; vor den Konten kommen",
  84         "inventory_accno" => "Bestandskonto",
  85         "income_accno" => "Erl&ouml;skonto",
  86         "expense_accno" => "Konto Umsatzkosten",
  87         "obsolete" => "Gesperrt (Y/N)",
  88         "lastcost" => "letzer EK-Preis",
  89         "rop" => "Mindestbestand",
  90         "shop" => "Shopartikel (Y/N)",
  91         "assembly" => "Stückliste (Y/N); wird noch nicht unterstützt",
  92         "partsgroup" => "Warengruppenbezeichnung",
  93         "partsgroup1" => "2.Warengruppenbezeichnung",
  94         "partsgroup2" => "3.Warengruppenbezeichnung",
  95         "partsgroup3" => "4.Warengruppenbezeichnung",
  96         "partsgroup4" => "5.Warengruppenbezeichnung",
  97         //"income_accno_0" => "?Nummer? für Erlöse Inland",
  98         //"income_accno_1" => "?Nummer? für Erlöse EG",
  99         //"income_accno_3" => "?Nummer? für Erlöse Ausland",
 100         );
 101
 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_greeting" => "Anrede",
 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",
 122         "cp_city" => "Ort",
 123         "cp_privatphone" => "Privattelefon",
 124         "cp_privatemail" => "private eMail",
 125         "cp_notes" => "Bemerkungen",
 126         "cp_stichwort1" => "Stichwort(e)",
 127         "cp_id" => "Kontakt ID"
 128         );
 129
 130 $contacts = array(
 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"
 149         );
 150
 151 function checkCRM() {
 152 global $db;
 153         $sql="select * from crm";
 154         $rs=$db->getAll($sql);
 155         if ($rs) {
 156                 return true;
 157         } else {
 158                 return false;
 159         }
 160 }
 161
 162 function chkUsr($usr) {
 163 // ist es ein gültiger ERP-Benutzer? Er muß mindestens 1 x angemeldet gewesen sein.
 164 global $db;
 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; };
 169 }
 170
 171 function getKdId() {
 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'";
 177         $db->begin();
 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));
 184         if ($rc) {
 185                 $db->commit();
 186                 return $nnr;
 187         } else {
 188                 $db->rollback();
 189                 return false;
 190         };
 191 }
 192
 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
 200                 return getKdId();
 201         } else {
 202                 return $data;
 203         }
 204 }
 205 function chkContact($id) {
 206 global $db;
 207         $sql="select * from contact where cp_id = $id";
 208         $rs=$db->getAll($sql);
 209         if ($rs[0]["cp_id"]==$id) {
 210                 return true;
 211         } else {
 212                 return false;
 213         }
 214 }
 215 function getKdRefId($data) {
 216 // gibt es die Nummer schon?
 217 global $db,$file,$test;
 218         if (empty($data) or !$data) {
 219                 return false;
 220         }
 221         $sql="select * from $file where ".$file."number = '$data'";
 222         $rs=$db->getAll($sql);
 223         return $rs[0]["id"];
 224 }
 225
 226 function suchFirma($tab,$data) {
 227 // gibt die Firma ?
 228 global $db;
 229         if (empty($data) or !$data) {
 230                 return false;
 231         }
 232         $data=strtoupper($data);
 233         $sql="select * from $tab where upper(name) like '%$data%'";
 234         $rs=$db->getAll($sql);
 235         if (!$rs) {
 236                 $org=$data;
 237                 while(strpos($data,"  ")>0) {
 238                         $data=ereg_replace("  "," ",$data);
 239                 }
 240                 $data=preg_replace("/[^A-Z0-9]/ ",".*",trim($data));
 241                 $sql="select * from $tab where upper(name) ~ '$data'";
 242                 $rs=$db->getAll($sql);
 243                 if (count($rs)==1) {
 244                         return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
 245                 }
 246                 return false;
 247         } else {
 248                 return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
 249         }
 250 }
 251
 252 $land=array("DEUTSC"=>"D","FRANKR"=>"F","SPANIE"=>"ES","ITALIE"=>"I","HOLLAN"=>"NL","NIEDER"=>"NL",
 253         "BELGIE"=>"B","LUXEMB"=>"L","NORWEG"=>"N","FINNLA"=>"","GRIECH"=>"GR","OESTER"=>"A",
 254         "SCHWEI"=>"CH","SCHWED"=>"S","AUSTRI"=>"A");
 255
 256 function mkland($data) {
 257 global $land;
 258         $data=strtr($data,array("Ö"=>"OE","Ä"=>"AE","Ü"=>"UE","ö"=>"OE","ä"=>"AE","ü"=>"UE","ß"=>"SS",
 259                         'Ö'=>'OE','Ä'=>'AE','Ü'=>'UE','ö'=>'OE','ä'=>'AE','ü'=>'UE','ß'=>'SS'));
 260         $data=strtoupper(substr($data,0,6));
 261         $cntr=$land[$data];
 262         return (strlen($cntr)>0)?$cntr:substr($data,0,3);
 263 }
 264
 265 //Suche Nach Kunden-/Lieferantenummer
 266 function getFirma($nummer,$tabelle) {
 267 global $db;
 268         $nummer=strtoupper($nummer);
 269         $sql="select id from $tabelle where upper(".$tabelle."number) = '$nummer'";
 270         $rs=$db->getAll($sql);
 271         if (!$rs) {
 272                 $nr=ereg_replace(" ","%",$nummer);
 273                 $sql="select id,".$tabelle."number from $tabelle where upper(".$tabelle."number) like '$nr'";
 274                 $rs=$db->getAll($sql);
 275                 if ($rs) {
 276                         $nr=ereg_replace(" ","",$nummer);
 277                         foreach ($rs as $row) {
 278                                 $tmp=ereg_replace(" ","",$row[$tabelle."number"]);
 279                                 if ($tmp==$nr) return $row["id"];
 280                         }
 281                 } else {
 282                         return false;
 283                 }
 284         } else {
 285                 return $rs[0]["id"];
 286         }
 287 }
 288
 289 function getAllBG($db) {
 290         $sql  = "select * from buchungsgruppen order by description";
 291         $rs=$db->getAll($sql);
 292         return $rs;
 293 }
 294 function anmelden() {
 295         ini_set("gc_maxlifetime","3600");
 296         $tmp = @file_get_contents("../config/authentication.pl");
 297         preg_match("/'db'[ ]*=> '(.+)'/",$tmp,$hits);
 298         $dbname=$hits[1];
 299         preg_match("/'password'[ ]*=> '(.+)'/",$tmp,$hits);
 300         $dbpasswd=$hits[1];
 301         preg_match("/'user'[ ]*=> '(.+)'/",$tmp,$hits);
 302         $dbuser=$hits[1];
 303         preg_match("/'host'[ ]*=> '(.+)'/",$tmp,$hits);
 304         $dbhost=($hits[1])?$hits[1]:"localhost";
 305         preg_match("/'port'[ ]*=> '?(.+)'?/",$tmp,$hits);
 306         $dbport=($hits[1])?$hits[1]:"5432";
 307         preg_match("/[ ]*\\\$self->\{cookie_name\}[ ]*=[ ]*'(.+)'/",$tmp,$hits);
 308         $cookiename=$hits[1];
 309         if (!$cookiename) $cookiename='lx_office_erp_session_id';
 310         $cookie=$_COOKIE[$cookiename];
 311         if (!$cookie) header("location: ups.html");
 312         $auth=authuser($dbhost,$dbport,$dbuser,$dbpasswd,$dbname,$cookie);
 313         if (!$auth) { return false; };
 314         $_SESSION["sessid"]=$cookie;
 315         $_SESSION["cookie"]=$cookiename;
 316         $_SESSION["employee"]=$auth["login"];
 317         $_SESSION["mansel"]=$auth["dbname"];
 318         $_SESSION["dbname"]=$auth["dbname"];
 319         $_SESSION["dbhost"]=(!$auth["dbhost"])?"localhost":$auth["dbhost"];
 320         $_SESSION["dbport"]=(!$auth["dbport"])?"5432":$auth["dbport"];
 321         $_SESSION["dbuser"]=$auth["dbuser"];
 322         $_SESSION["dbpasswd"]=$auth["dbpasswd"];
 323         $_SESSION["db"]=new myDB($_SESSION["dbhost"],$_SESSION["dbuser"],$_SESSION["dbpasswd"],$_SESSION["dbname"],$_SESSION["dbport"],$showErr);
 324         $_SESSION["authcookie"]=$authcookie;
 325         $sql="select * from employee where login='".$auth["login"]."'";
 326         $rs=$_SESSION["db"]->getAll($sql);
 327         if(!$rs) {
 328                 return false;
 329         } else {
 330                 if ($rs) {
 331                         $tmp=$rs[0];
 332                         $_SESSION["termbegin"]=(($tmp["termbegin"]>=0)?$tmp["termbegin"]:8);
 333                         $_SESSION["termend"]=($tmp["termend"])?$tmp["termend"]:19;
 334                         $_SESSION["Pre"]=$tmp["pre"];
 335                         $_SESSION["interv"]=($tmp["interv"]>0)?$tmp["interv"]:60;
 336                         $_SESSION["loginCRM"]=$tmp["id"];
 337                         $_SESSION["lang"]=$tmp["countrycode"]; //"de";
 338                         $_SESSION["kdview"]=$tmp["kdview"];
 339                         $sql="select * from defaults";
 340                         $rs=$_SESSION["db"]->getAll($sql);
 341                         $_SESSION["ERPver"]=$rs[0]["version"];
 342                         return true;
 343                 } else {
 344                         return false;
 345                 }
 346         }
 347 }
 348
 349 function authuser($dbhost,$dbport,$dbuser,$dbpasswd,$dbname,$cookie) {
 350         $db=new myDB($dbhost,$dbuser,$dbpasswd,$dbname,$dbport,true);
 351         $sql="select sc.session_id,u.id from auth.session_content sc left join auth.user u on ";
 352         $sql.="u.login=sc.sess_value left join auth.session s on s.id=sc.session_id ";
 353         $sql.="where session_id = '$cookie' and sc.sess_key='login'";// order by s.mtime desc";
 354         $rs=$db->getAll($sql,"authuser_1");
 355         if (!$rs) return false;
 356         $stmp="";
 357         if (count($rs)>1) {
 358                 header("location:../login.pl?action=logout");
 359                 /*foreach($rs as $row) {
 360                         $stmp.=$row["session_id"].",";
 361                 }
 362                 $sql1="delete from session where id in (".substr($stmp,-1).")";
 363                 $sql2="delete from session_content where session_id in (".substr($stmp,-1).")";
 364                 $db->query($sql1,"authuser_A");
 365                 $db->query($sql2,"authuser_B");
 366                 $sql3="insert into session ";*/
 367         }
 368         $sql="select * from auth.user where id=".$rs[0]["id"];
 369         $rs1=$db->getAll($sql,"authuser_1");
 370         if (!$rs1) return false;
 371         $auth=array();
 372         $auth["login"]=$rs1[0]["login"];
 373         $sql="select * from auth.user_config where user_id=".$rs[0]["id"];
 374         $rs1=$db->getAll($sql,"authuser_2");
 375         $keys=array("dbname","dbpasswd","dbhost","dbport","dbuser");
 376         foreach ($rs1 as $row) {
 377                 if (in_array($row["cfg_key"],$keys)) {
 378                         $auth[$row["cfg_key"]]=$row["cfg_value"];
 379                 }
 380         }
 381         $sql="update auth.session set mtime = '".date("Y-M-d H:i:s.100001")."' where id = '".$rs[0]["session_id"]."'";
 382         $db->query($sql,"authuser_3");
 383         return $auth;
 384 }
 385
 386 function getLager($db) {
 387         $sql="select w.description as ort,bin.id,bin.description as platz from  warehouse w left join bin  on w.id=bin.warehouse_id";
 388         $rs=$db->getAll($sql,"getLager");
 389         return $rs;
 390 }
 391 ?>