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 ?>