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