-<?
+<?php
/*
Funktionsbibliothek für den Datenimport in Lx-Office ERP
*/
-require_once "DB.php";
+require_once "db.php";
$address = array(
"name" => "Firmenname",
"customernumber" => "Kundennummer",
"vendornumber" => "Lieferantennummer",
"taxnumber" => "Steuernummer",
+ "ustid" => "Umsatzsteuer-ID",
"account_number" => "Kontonummer",
"bank_code" => "Bankleitzahl",
"bank" => "Bankname",
"branche" => "Branche",
- "language" => "Sprache (de,en,fr)",
+ //"language" => "Sprache (de,en,fr)",
"sw" => "Stichwort",
- "creditlimit" => "Kreditlimit (nnnnnn.nn)",
+ "creditlimit" => "Kreditlimit (nnnnnn.nn)"); /*,
"hierarchie" => "Hierarchie",
"potenzial" => "Potenzial",
"ar" => "Debitorenkonto",
"ap" => "Kreditorenkonto",
"matchcode" => "Matchcode",
- "customernumber2" => "Kundennummer 2");
+ "customernumber2" => "Kundennummer 2");
+ Kundenspezifisch */
$shiptos = array(
"shiptoname" => "Firmenname",
"partnumber" => "Artikelnummer",
"description" => "Artikeltext",
"unit" => "Einheit",
- "weight" => "Gewicht (kg)",
+ "weight" => "Gewicht in Benutzerdefinition",
"onhand" => "Lagerbestand",
+ "bin" => "Lagerplatz",
"notes" => "Beschreibung",
- "makemodel" => "Hersteller",
- "model" => "Modellbezeichnung",
- "income_accno_id" => "Erlöskonto",
- "expense_accno_id" => "Konto Umsatzkosten",
+ "notes1" => "Beschreibung",
+ //"makemodel" => "Hersteller",
+ //"model" => "Modellbezeichnung",
"bin" => "Lagerort",
"image" => "Pfad/Dateiname",
"drawing" => "Pfad/Dateiname",
"microfiche" => "Pfad/Dateiname",
- "partsgroup_id" => "Name Warengruppe",
"listprice" => "Listenpreis",
"sellprice" => "Verkaufspreis",
"lastcost" => "letzter EK",
- "art" => "Ware/Dienstleistung (*/d)");
+ "art" => "Ware/Dienstleistung (*/d), muß vor den Konten kommen",
+ "inventory_accno" => "Bestandskonto",
+ "income_accno" => "Erlöskonto",
+ "expense_accno" => "Konto Umsatzkosten",
+ "obsolete" => "Gesperrt (Y/N)",
+ "lastcost" => "letzer EK-Preis",
+ "rop" => "Mindestbestand",
+ "shop" => "Shopartikel (Y/N)",
+ "assembly" => "Stückliste (Y/N); wird noch nicht unterstützt",
+ "partsgroup" => "Warengruppenbezeichnung",
+ "partsgroup1" => "2.Warengruppenbezeichnung",
+ "partsgroup2" => "3.Warengruppenbezeichnung",
+ "partsgroup3" => "4.Warengruppenbezeichnung",
+ "partsgroup4" => "5.Warengruppenbezeichnung",
+ //"income_accno_0" => "?Nummer? für Erlöse Inland",
+ //"income_accno_1" => "?Nummer? für Erlöse EG",
+ //"income_accno_3" => "?Nummer? für Erlöse Ausland",
+ );
$contactscrm = array(
"customernumber" => "Kundennummer",
"vendornumber" => "Lieferantennummer",
"cp_cv_id" => "FirmenID in der db",
"firma" => "Firmenname",
+ "cp_abteilung" => "Abteilung",
+ "cp_position" => "Position/Hierarchie",
"cp_greeting" => "Anrede",
"cp_title" => "Titel",
"cp_givenname" => "Vorname",
- "cp_greeting" => "Anrede",
"cp_name" => "Nachname",
"cp_email" => "eMail",
"cp_phone1" => "Telefon 1",
"cp_phone2" => "Telefon 2",
+ "cp_mobile1" => "Mobiltelefon 1",
+ "cp_mobile2" => "Mobiltelefon 2",
+ "cp_homepage" => "Homepage",
"cp_street" => "Strasse",
+ "cp_country" => "Land",
"cp_zipcode" => "PLZ",
"cp_city" => "Ort",
+ "cp_privatphone" => "Privattelefon",
+ "cp_privatemail" => "private eMail",
"cp_notes" => "Bemerkungen",
- "cp_country" => "Land",
"cp_stichwort1" => "Stichwort(e)",
- "katalog" => "Katalog",
- "inhaber" => "Inhaber",
- "contact_id" => "Kontakt ID"
+ "cp_id" => "Kontakt ID"
);
$contacts = array(
"cp_email" => "eMail",
"cp_phone1" => "Telefon 1",
"cp_phone2" => "Telefon 2",
- "katalog" => "Katalog",
- "inhaber" => "Inhaber",
- "contact_id" => "Kontakt ID"
+ "cp_mobile1" => "Mobiltelefon 1",
+ "cp_mobile2" => "Mobiltelefon 2",
+ "cp_privatphone" => "Privattelefon",
+ "cp_privatemail" => "private eMail",
+ "cp_homepage" => "Homepage",
+ "cp_id" => "Kontakt ID"
);
function checkCRM() {
}
function chkUsr($usr) {
-// ist es ein gültiger ERP-Benutzer? Er muß mindestens 1 x angemeldet gewesen sein.
+// ist es ein gültiger ERP-Benutzer? Er muß mindestens 1 x angemeldet gewesen sein.
global $db;
$sql="select * from employee where login = '$usr'";
$rs=$db->getAll($sql);
}
function getKdId() {
-// die nächste freie Kunden-/Lieferantennummer holen
+// die nächste freie Kunden-/Lieferantennummer holen
global $db,$file,$test;
if ($test) { return "#####"; }
$sql1="select * from defaults";
$sql2="update defaults set ".$file."number = '%s'";
- $db->lock();
+ $db->begin();
$rs=$db->getAll($sql1);
$nr=$rs[0][$file."number"];
preg_match("/^([^0-9]*)([0-9]+)/",$nr,$hits);
return $data;
}
}
-
+function chkContact($id) {
+global $db;
+ $sql="select * from contact where cp_id = $id";
+ $rs=$db->getAll($sql);
+ if ($rs[0]["cp_id"]==$id) {
+ return true;
+ } else {
+ return false;
+ }
+}
function getKdRefId($data) {
// gibt es die Nummer schon?
global $db,$file,$test;
function mkland($data) {
global $land;
- $data=strtr($data,array("Ö"=>"OE","Ä"=>"AE","Ü"=>"UE","ö"=>"OE","ä"=>"AE","ü"=>"UE","ß"=>"SS"));
+ $data=strtr($data,array("Ö"=>"OE","Ä"=>"AE","Ü"=>"UE","ö"=>"OE","ä"=>"AE","ü"=>"UE","ß"=>"SS",
+ 'Ö'=>'OE','Ä'=>'AE','Ü'=>'UE','ö'=>'OE','ä'=>'AE','ü'=>'UE','ß'=>'SS'));
$data=strtoupper(substr($data,0,6));
$cntr=$land[$data];
return (strlen($cntr)>0)?$cntr:substr($data,0,3);
}
}
-class myDB extends DB {
-// Datenbankklasse
-
- var $rc = false;
- var $showErr = false;
- var $db = false;
- var $debug = false;
-
-/****************************************************
-* uudecode
-* in: string
-* out: string
-* dekodiert Perl-UU-kodierte Passwort-Strings
-* http://de3.php.net/base64_decode (bug #171)
-*****************************************************/
- function uudecode($encode) {
- $b64chars="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
-
- $encode = preg_replace("/^./m","",$encode);
- $encode = preg_replace("/\n/m","",$encode);
- for($i=0; $i<strlen($encode); $i++) {
- if ($encode[$i] == '')
- $encode[$i] = ' ';
- $encode[$i] = $b64chars[ord($encode[$i])-32];
- }
-
- while(strlen($encode) % 4)
- $encode .= "=";
-
- return base64_decode($encode);
- }
-
- function dbFehler($sql,$err) {
- if ($this->showErr)
- echo "$sql : $err\n";
- }
-
- function showDebug($sql) {
- echo $sql."\n";
- if ($this->debug==2) {
- print_r($this->rc);
- };
- }
-
- function myDB($usr) {
- // Datenbankparameter des ERP-Users benutzen.
- $tmp = file_get_contents("../users/$usr.conf");
- preg_match("/dbname => '(.+)'/",$tmp,$hits);
- $dbname=$hits[1];
- preg_match("/dbpasswd => '(.+)'/",$tmp,$hits);
- if ($hits[1]) {
- $dbpasswd=$this->uudecode($hits[1]);
- } else {
- $dbpasswd="";
- };
- preg_match("/dbuser => '(.+)'/",$tmp,$hits);
- $dbuser=$hits[1];
- preg_match("/dbhost => '(.+)'/",$tmp,$hits);
- $dbhost=$hits[1];
- if (!$dbhost) $dbhost="localhost";
- if ($dbpasswd) {
- $dns=$dbuser.":".$dbpasswd."@".$dbhost."/".$dbname;
- } else {
- $dns=$dbuser."@".$dbhost."/".$dbname;
- };
- $dns="pgsql://".$dns;
- $this->db=DB::connect($dns);
- if (!$this->db) DB::dbFehler("oh oh oh",$this->db->getDebugInfo());
- if (DB::isError($this->db)) {
- $this->dbFehler("Connect",$this->db->getDebugInfo());
- die ($this->db->getDebugInfo());
- }
- return $this->db;
- }
-
- function query($sql) {
- $this->rc=@$this->db->query($sql);
- if ($this->debug) $this->showDebug($sql);
- if(DB::isError($this->rc)) {
- $this->dbFehler($sql,$this->rc->getMessage());
- return false;
- } else {
- return $this->rc;
- }
- }
- function getAll($sql) {
- $this->rc=@$this->db->getAll($sql,DB_FETCHMODE_ASSOC);
- if ($this->debug) $this->showDebug($sql);
- if(DB::isError($this->rc)) {
- $this->dbFehler($sql,$this->rc->getMessage());
- return false;
- } else {
- return $this->rc;
- }
- }
+function getAllBG($db) {
+ $sql = "select * from buchungsgruppen order by description";
+ $rs=$db->getAll($sql);
+ return $rs;
+}
+function anmelden() {
+ ini_set("gc_maxlifetime","3600");
+ $tmp = @file_get_contents("../config/authentication.pl");
+ preg_match("/'db'[ ]*=> '(.+)'/",$tmp,$hits);
+ $dbname=$hits[1];
+ preg_match("/'password'[ ]*=> '(.+)'/",$tmp,$hits);
+ $dbpasswd=$hits[1];
+ preg_match("/'user'[ ]*=> '(.+)'/",$tmp,$hits);
+ $dbuser=$hits[1];
+ preg_match("/'host'[ ]*=> '(.+)'/",$tmp,$hits);
+ $dbhost=($hits[1])?$hits[1]:"localhost";
+ preg_match("/'port'[ ]*=> '(.+)'/",$tmp,$hits);
+ $dbport=($hits[1])?$hits[1]:"5432";
+ preg_match("/[ ]*\\\$self->\{cookie_name\}[ ]*=[ ]*'(.+)'/",$tmp,$hits);
+ $cookiename=$hits[1];
+ if (!$cookiename) $cookiename='lx_office_erp_session_id';
+ $cookie=$_COOKIE[$cookiename];
+ if (!$cookie) header("location: ups.html");
+ $auth=authuser($dbhost,$dbport,$dbuser,$dbpasswd,$dbname,$cookie);
+ if (!$auth) { return false; };
+ $_SESSION["sessid"]=$cookie;
+ $_SESSION["cookie"]=$cookiename;
+ $_SESSION["employee"]=$auth["login"];
+ $_SESSION["mansel"]=$auth["dbname"];
+ $_SESSION["dbname"]=$auth["dbname"];
+ $_SESSION["dbhost"]=(!$auth["dbhost"])?"localhost":$auth["dbhost"];
+ $_SESSION["dbport"]=(!$auth["dbport"])?"5432":$auth["dbport"];
+ $_SESSION["dbuser"]=$auth["dbuser"];
+ $_SESSION["dbpasswd"]=$auth["dbpasswd"];
+ $_SESSION["db"]=new myDB($_SESSION["dbhost"],$_SESSION["dbuser"],$_SESSION["dbpasswd"],$_SESSION["dbname"],$_SESSION["dbport"],$showErr);
+ $_SESSION["authcookie"]=$authcookie;
+ $sql="select * from employee where login='".$auth["login"]."'";
+ $rs=$_SESSION["db"]->getAll($sql);
+ if(!$rs) {
+ return false;
+ } else {
+ if ($rs) {
+ $tmp=$rs[0];
+ $_SESSION["termbegin"]=(($tmp["termbegin"]>=0)?$tmp["termbegin"]:8);
+ $_SESSION["termend"]=($tmp["termend"])?$tmp["termend"]:19;
+ $_SESSION["Pre"]=$tmp["pre"];
+ $_SESSION["interv"]=($tmp["interv"]>0)?$tmp["interv"]:60;
+ $_SESSION["loginCRM"]=$tmp["id"];
+ $_SESSION["lang"]=$tmp["countrycode"]; //"de";
+ $_SESSION["kdview"]=$tmp["kdview"];
+ $sql="select * from defaults";
+ $rs=$_SESSION["db"]->getAll($sql);
+ $_SESSION["ERPver"]=$rs[0]["version"];
+ return true;
+ } else {
+ return false;
+ }
+ }
+}
- function lock() {
- $this->query("BEGIN");
- }
- function commit() {
- $this->query("COMMIT");
- }
- function rollback() {
- $this->query("ROLLBACK");
- }
- function chkcol($tbl) {
- // gibt es die Spalte import schon?
- $rc=$this->db->query("select import from $tbl limit 1");
- if(DB::isError($rc)) {
- $rc=$this->db->query("alter table $tbl add column import int4");
- if(DB::isError($rc)) { return false; }
- else { return true; }
-
- } else { return true; };
- }
+function authuser($dbhost,$dbport,$dbuser,$dbpasswd,$dbname,$cookie) {
+ $db=new myDB($dbhost,$dbuser,$dbpasswd,$dbname,$dbport,true);
+ $sql="select sc.session_id,u.id from auth.session_content sc left join auth.user u on ";
+ $sql.="u.login=sc.sess_value left join auth.session s on s.id=sc.session_id ";
+ $sql.="where session_id = '$cookie' and sc.sess_key='login'";// order by s.mtime desc";
+ $rs=$db->getAll($sql,"authuser_1");
+ if (!$rs) return false;
+ $stmp="";
+ if (count($rs)>1) {
+ header("location:../login.pl?action=logout");
+ /*foreach($rs as $row) {
+ $stmp.=$row["session_id"].",";
+ }
+ $sql1="delete from session where id in (".substr($stmp,-1).")";
+ $sql2="delete from session_content where session_id in (".substr($stmp,-1).")";
+ $db->query($sql1,"authuser_A");
+ $db->query($sql2,"authuser_B");
+ $sql3="insert into session ";*/
+ }
+ $sql="select * from auth.user where id=".$rs[0]["id"];
+ $rs1=$db->getAll($sql,"authuser_1");
+ if (!$rs1) return false;
+ $auth=array();
+ $auth["login"]=$rs1[0]["login"];
+ $sql="select * from auth.user_config where user_id=".$rs[0]["id"];
+ $rs1=$db->getAll($sql,"authuser_2");
+ $keys=array("dbname","dbpasswd","dbhost","dbport","dbuser");
+ foreach ($rs1 as $row) {
+ if (in_array($row["cfg_key"],$keys)) {
+ $auth[$row["cfg_key"]]=$row["cfg_value"];
+ }
+ }
+ $sql="update auth.session set mtime = '".date("Y-M-d H:i:s.100001")."' where id = '".$rs[0]["session_id"]."'";
+ $db->query($sql,"authuser_3");
+ return $auth;
}
+function getLager($db) {
+ $sql="select w.description as ort,bin.id,bin.description as platz from warehouse w left join bin on w.id=bin.warehouse_id";
+ $rs=$db->getAll($sql,"getLager");
+ return $rs;
+}
?>