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         "partsgroup1" => "2.Warengruppenbezeichnung",
  92         //"income_accno_0" => "?Nummer? für Erlöse Inland",
  93         //"income_accno_1" => "?Nummer? für Erlöse EG",
  94         //"income_accno_3" => "?Nummer? für Erlöse Ausland",
  95         );
  96
  97 $contactscrm = array(
  98         "customernumber" => "Kundennummer",
  99         "vendornumber" => "Lieferantennummer",
 100         "cp_cv_id" => "FirmenID in der db",
 101         "firma" => "Firmenname",
 102         "cp_abteilung" => "Abteilung",
 103         "cp_position" => "Position/Hierarchie",
 104         "cp_greeting" => "Anrede",
 105         "cp_title" => "Titel",
 106         "cp_givenname" => "Vorname",
 107         "cp_name" => "Nachname",
 108         "cp_email" => "eMail",
 109         "cp_phone1" => "Telefon 1",
 110         "cp_phone2" => "Telefon 2",
 111         "cp_mobile1" => "Mobiltelefon 1",
 112         "cp_mobile2" => "Mobiltelefon 2",
 113         "cp_homepage" => "Homepage",
 114         "cp_street" => "Strasse",
 115         "cp_country" => "Land",
 116         "cp_zipcode" => "PLZ",
 117         "cp_city" => "Ort",
 118         "cp_privatphone" => "Privattelefon",
 119         "cp_privatemail" => "private eMail",
 120         "cp_notes" => "Bemerkungen",
 121         "cp_stichwort1" => "Stichwort(e)",
 122         "katalog" => "Katalog",
 123         "inhaber" => "Inhaber",
 124         "contact_id" => "Kontakt ID"
 125         );
 126
 127 $contacts = array(
 128         "customernumber" => "Kundennummer",
 129         "vendornumber" => "Lieferantennummer",
 130         "cp_cv_id" => "FirmenID in der db",
 131         "firma" => "Firmenname",
 132         "cp_greeting" => "Anrede",
 133         "cp_title" => "Titel",
 134         "cp_givenname" => "Vorname",
 135         "cp_greeting" => "Anrede",
 136         "cp_name" => "Nachname",
 137         "cp_email" => "eMail",
 138         "cp_phone1" => "Telefon 1",
 139         "cp_phone2" => "Telefon 2",
 140         "cp_mobile1" => "Mobiltelefon 1",
 141         "cp_mobile2" => "Mobiltelefon 2",
 142         "cp_privatphone" => "Privattelefon",
 143         "cp_privatemail" => "private eMail",
 144         "cp_homepage" => "Homepage",
 145         "katalog" => "Katalog",
 146         "inhaber" => "Inhaber",
 147         "contact_id" => "Kontakt ID"
 148         );
 149
 150 function checkCRM() {
 151 global $db;
 152         $sql="select * from crm";
 153         $rs=$db->getAll($sql);
 154         if ($rs) {
 155                 return true;
 156         } else {
 157                 return false;
 158         }
 159 }
 160
 161 function chkUsr($usr) {
 162 // ist es ein gültiger ERP-Benutzer? Er muß mindestens 1 x angemeldet gewesen sein.
 163 global $db;
 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; };
 168 }
 169
 170 function getKdId() {
 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'";
 176         $db->lock();
 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));
 183         if ($rc) {
 184                 $db->commit();
 185                 return $nnr;
 186         } else {
 187                 $db->rollback();
 188                 return false;
 189         };
 190 }
 191
 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
 199                 return getKdId();
 200         } else {
 201                 return $data;
 202         }
 203 }
 204
 205 function getKdRefId($data) {
 206 // gibt es die Nummer schon?
 207 global $db,$file,$test;
 208         if (empty($data) or !$data) {
 209                 return false;
 210         }
 211         $sql="select * from $file where ".$file."number = '$data'";
 212         $rs=$db->getAll($sql);
 213         return $rs[0]["id"];
 214 }
 215
 216 function suchFirma($tab,$data) {
 217 // gibt die Firma ?
 218 global $db;
 219         if (empty($data) or !$data) {
 220                 return false;
 221         }
 222         $data=strtoupper($data);
 223         $sql="select * from $tab where upper(name) like '%$data%'";
 224         $rs=$db->getAll($sql);
 225         if (!$rs) {
 226                 $org=$data;
 227                 while(strpos($data,"  ")>0) {
 228                         $data=ereg_replace("  "," ",$data);
 229                 }
 230                 $data=preg_replace("/[^A-Z0-9]/ ",".*",trim($data));
 231                 $sql="select * from $tab where upper(name) ~ '$data'";
 232                 $rs=$db->getAll($sql);
 233                 if (count($rs)==1) {
 234                         return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
 235                 }
 236                 return false;
 237         } else {
 238                 return array("cp_cv_id"=>$rs[0]["id"],"Firma"=>$rs[0]["name"]);
 239         }
 240 }
 241
 242 $land=array("DEUTSC"=>"D","FRANKR"=>"F","SPANIE"=>"ES","ITALIE"=>"I","HOLLAN"=>"NL","NIEDER"=>"NL",
 243         "BELGIE"=>"B","LUXEMB"=>"L","NORWEG"=>"N","FINNLA"=>"","GRIECH"=>"GR","OESTER"=>"A",
 244         "SCHWEI"=>"CH","SCHWED"=>"S","AUSTRI"=>"A");
 245
 246 function mkland($data) {
 247 global $land;
 248         $data=strtr($data,array("Ö"=>"OE","Ä"=>"AE","Ü"=>"UE","ö"=>"OE","ä"=>"AE","ü"=>"UE","ß"=>"SS"));
 249         $data=strtoupper(substr($data,0,6));
 250         $cntr=$land[$data];
 251         return (strlen($cntr)>0)?$cntr:substr($data,0,3);
 252 }
 253
 254 //Suche Nach Kunden-/Lieferantenummer
 255 function getFirma($nummer,$tabelle) {
 256 global $db;
 257         $nummer=strtoupper($nummer);
 258         $sql="select id from $tabelle where upper(".$tabelle."number) = '$nummer'";
 259         $rs=$db->getAll($sql);
 260         if (!$rs) {
 261                 $nr=ereg_replace(" ","%",$nummer);
 262                 $sql="select id,".$tabelle."number from $tabelle where upper(".$tabelle."number) like '$nr'";
 263                 $rs=$db->getAll($sql);
 264                 if ($rs) {
 265                         $nr=ereg_replace(" ","",$nummer);
 266                         foreach ($rs as $row) {
 267                                 $tmp=ereg_replace(" ","",$row[$tabelle."number"]);
 268                                 if ($tmp==$nr) return $row["id"];
 269                         }
 270                 } else {
 271                         return false;
 272                 }
 273         } else {
 274                 return $rs[0]["id"];
 275         }
 276 }
 277
 278 function getAllBG($db) {
 279         $sql  = "select * from buchungsgruppen order by description";
 280         $rs=$db->getAll($sql);
 281         return $rs;
 282 }
 283
 284 class myDB extends DB {
 285 // Datenbankklasse
 286
 287  var $rc = false;
 288  var $showErr = false;
 289  var $db = false;
 290  var $debug = false;
 291  var $logsql = false;
 292  var $errfile = false;
 293  var $logfile = false;
 294
 295
 296 /****************************************************
 297 * uudecode
 298 * in: string
 299 * out: string
 300 * dekodiert Perl-UU-kodierte Passwort-Strings
 301 * http://de3.php.net/base64_decode (bug #171)
 302 *****************************************************/
 303         function uudecode($encode) {
 304           $b64chars="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
 305
 306           $encode = preg_replace("/^./m","",$encode);
 307           $encode = preg_replace("/\n/m","",$encode);
 308           for($i=0; $i<strlen($encode); $i++) {
 309             if ($encode[$i] == '')
 310               $encode[$i] = ' ';
 311             $encode[$i] = $b64chars[ord($encode[$i])-32];
 312           }
 313
 314           while(strlen($encode) % 4)
 315             $encode .= "=";
 316
 317           return base64_decode($encode);
 318         }
 319
 320         function dbFehler($sql,$err) {
 321                 if ($this->showErr)
 322                         echo "$sql : $err\n";
 323         }
 324
 325         function showDebug($sql) {
 326                 echo $sql."\n";
 327                 if ($this->debug==2) {
 328                         print_r($this->rc);
 329                 };
 330         }
 331
 332         function logSql($sql) {
 333                 if (!$this->logfile)  $this->logfile=fopen("import.sql","a");
 334                 fputs($this->logfile,$sql."\n");
 335         }
 336         function myDB($usr) {
 337                 // Datenbankparameter des ERP-Users benutzen.
 338                 $tmp = file_get_contents("../users/$usr.conf");
 339                 preg_match("/dbname => '(.+)'/",$tmp,$hits);
 340                 $dbname=$hits[1];
 341                 preg_match("/dbpasswd => '(.+)'/",$tmp,$hits);
 342                 if ($hits[1]) {
 343                         $dbpasswd=$this->uudecode($hits[1]);
 344                 } else {
 345                         $dbpasswd="";
 346                 };
 347                 preg_match("/dbuser => '(.+)'/",$tmp,$hits);
 348                 $dbuser=$hits[1];
 349                 preg_match("/dbhost => '(.+)'/",$tmp,$hits);
 350                 $dbhost=$hits[1];
 351                 if (!$dbhost) $dbhost="localhost";
 352                 if ($dbpasswd) {
 353                         $dns=$dbuser.":".$dbpasswd."@".$dbhost."/".$dbname;
 354                 } else {
 355                         $dns=$dbuser."@".$dbhost."/".$dbname;
 356                 };
 357                 $dns="pgsql://".$dns;
 358                 $this->db=DB::connect($dns);
 359                 if (!$this->db) DB::dbFehler("oh oh oh",$this->db->getDebugInfo());
 360                 if (DB::isError($this->db)) {
 361                         $this->dbFehler("Connect",$this->db->getDebugInfo());
 362                         die ($this->db->getDebugInfo());
 363                 }
 364                 return $this->db;
 365         }
 366
 367         function query($sql) {
 368                 $this->rc=@$this->db->query($sql);
 369                 if ($this->logsql) $this->logSql($sql);
 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         function getAll($sql) {
 379                 $this->rc=@$this->db->getAll($sql,DB_FETCHMODE_ASSOC);
 380                 if ($this->logsql) $this->logSql($sql);
 381                 if ($this->debug) $this->showDebug($sql);
 382                 if(DB::isError($this->rc)) {
 383                         $this->dbFehler($sql,$this->rc->getMessage());
 384                         return false;
 385                 } else {
 386                         return $this->rc;
 387                 }
 388         }
 389
 390         function lock() {
 391                 $this->query("BEGIN");
 392         }
 393         function commit() {
 394                 $this->query("COMMIT");
 395         }
 396         function rollback() {
 397                 $this->query("ROLLBACK");
 398         }
 399         function chkcol($tbl) {
 400         // gibt es die Spalte import schon?
 401                 $rc=$this->db->query("select import from $tbl limit 1");
 402                 if(DB::isError($rc)) {
 403                         $rc=$this->db->query("alter table $tbl add column import int4");
 404                         if(DB::isError($rc)) { return false; }
 405                         else { return true; }
 406
 407                 } else { return true; };
 408         }
 409 }
 410
 411 ?>