From bd10a269df8a3de2653d02b8771459a4e34165a9 Mon Sep 17 00:00:00 2001 From: Holger Lindemann Date: Thu, 18 Jan 2007 13:44:53 +0000 Subject: [PATCH] Artikelimport, neu --- lxo-import/partsB.php | 123 +++++++++++++++ lxo-import/parts_import.php | 293 ++++++++++++++++++++++++++++++++++++ 2 files changed, 416 insertions(+) create mode 100644 lxo-import/partsB.php create mode 100644 lxo-import/parts_import.php diff --git a/lxo-import/partsB.php b/lxo-import/partsB.php new file mode 100644 index 000000000..2388fef7a --- /dev/null +++ b/lxo-import/partsB.php @@ -0,0 +1,123 @@ + + + + +Holger Lindemann +*/ + +/* get login via GET or POST */ +if ($_GET["login"]) { + $login=$_GET["login"]; +} else { + $login=$_POST["login"]; +}; + +require ("import_lib.php"); +/* get DB instance */ +$db=new myDB($login); + + +/* just display page or do real import? */ +if ($_POST["ok"]) { + + +require ("parts_import.php"); + +function ende($nr) { + echo "Abbruch: $nr
"; + echo "Fehlende oder falsche Daten."; + exit(1); +} + +/* display help */ +if ($_POST["ok"]=="Hilfe") { + echo "Importfelder:
"; + echo "Feldname => Bedeutung
"; + foreach($parts as $key=>$val) { + echo "$key => $val
"; + } + echo "Jeder Artikel muß einer Buchungsgruppe zugeordnet werden. "; + echo "Dazu muß entweder in der Maske eine Standardbuchungsgruppe gewählt werden
"; + echo "oder es wird ein gültiges Konto in 'income_accno_id' und 'expense_accno_id' eingegeben. "; + echo "Das Programm versucht dann eine passende Buchungsgruppe zu finden."; + exit(0); +}; + +clearstatcache (); + +$test = $_POST["test"]; +$trenner = ($_POST["trenner"])?$_POST["trenner"]:","; +$file = "parts"; + +/* no data? */ +if (empty($_FILES["Datei"]["name"])) + ende (2); + +/* copy file */ +if (!move_uploaded_file($_FILES["Datei"]["tmp_name"],$file.".csv")) { + echo "Upload von Datei fehlerhaft."; + echo $_FILES["Datei"]["error"], "
"; + ende (2); +} + +/* ??? */ +if (!file_exists("../users/$login.conf")) + ende(3); + +/* check if file is really there */ +if (!file_exists("$file.csv")) + ende(5); + +/* ??? */ +if (!$db->chkcol($file)) + ende(6); + +/* ??? */ +if (!chkUsr($login)) + ende(4); + +/* first check all elements */ +echo "Checking data:
"; +$err = import_parts($db, $file, $trenner, $parts, TRUE, FALSE, FALSE,$_POST); +echo "$err Errors found\n"; + + +if ($err!=0) + exit(0); + +/* just print data or insert it, if test is false */ +import_parts($db, $file, $trenner, $parts, FALSE, !$test, TRUE,$_POST); + +} else { + $bugrus=getAllBG($db); +?> + +

Artikelimport für die ERP

+
+

+ diff --git a/lxo-import/parts_import.php b/lxo-import/parts_import.php new file mode 100644 index 000000000..19e0f257d --- /dev/null +++ b/lxo-import/parts_import.php @@ -0,0 +1,293 @@ + +//Holger Lindemann + +/** + * Returns ID of a partgroup (or adds a new partgroup entry) + * \db is the database + * \value is the partgroup name + * \add if true and partgroup does not exist yet, we will add it automatically + * \returns partgroup id or "" in case of an error + */ +function getPartsgroupId($db, $value, $add) { + + $sql="select id from partsgroup where partsgroup = '$value'"; + $rs=$db->getAll($sql); + if (empty($rs[0]["id"]) && $add) { + $sql="insert into partsgroup (partsgroup) values ('$value')"; + $rc=$db->query($sql); + if (!$rc) + return ""; + return getPartsgroupId($db, $value, 0); + } + return $rs[0]["id"]; +} + +function getAccnoId($db, $accno) { + $sql = "select id from chart where accno='$accno'"; + $rs=$db->getAll($sql); + return $rs[0]["id"]; +} + +function chkPartNumber($db,$number,$check) { + if ($number<>"") { + $sql = "select * from parts where partnumber = '$number'"; + $rs=$db->getAll($sql); + } + //echo $sql; print_r($rs); + if ($rs[0]["id"]>0 or $number=="") { + if ($check) return "check"; + $rc=$db->query("BEGIN"); + $sql = "select articlenumber from defaults"; + $rs=$db->getAll($sql); + $number=$rs[0]["articlenumber"]+1; + $sql = "update defaults set articlenumber = '$number'"; + $rc=$db->query($sql); + $rc=$db->query("COMMIT"); + $sql = "select * from parts where partnumber = '$number'"; + $rs=$db->getAll($sql); + if ($rs[0]["id"]>0) return ""; + } + return $number; +} + +function getBuchungsgruppe($db, $income, $expense) { + + $income_id = getAccnoId($db, $income); + $expense_id = getAccnoId($db, $expense); + //$accno0_id = getAccnoId($db, $accno0); + //$accno1_id = getAccnoId($db, $accno1); + //$accno3_id = getAccnoId($db, $accno3); + + $sql = "select id from buchungsgruppen where "; + $sql .= "income_accno_id_0 = $income_id and "; + $sql .= "expense_accno_id_0 = $expense_id "; + //$sql .= "income_accno_id_0 = '$accno0_id' "; + //$sql .= "and income_accno_id_1 = '$accno1_id' "; + //$sql .= "and income_accno_id_3 = '$accno3_id'"; + $rs=$db->getAll($sql); + return $rs[0]["id"]; +} + + +function getFromBG($db, $bg_id, $name) { + + $sql = "select $name from buchungsgruppen where id='$bg_id'"; + $rs=$db->getAll($sql); + return $rs[0][$name]; +} + +function existUnit($db, $value) { + $sql="select name from units where name = '$value'"; + $rs=$db->getAll($sql); + if (empty($rs[0]["name"])) + return FALSE; + return TRUE; +} + +function show($show, $things) { + if ($show) + echo $things; +} + +function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$maske) { + + /* field description */ + $parts_fld = array_keys($fields); + + /* open csv file */ + $f=fopen("$file.csv","r"); + + /* + * read first line with table descriptions + */ + show( $show, "\n"); + $infld=fgetcsv($f,1200,$trenner); + foreach ($infld as $fld) { + $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>"")))); + $in_fld[]=$fld; + if (in_array(trim($fld),$parts_fld)) { + show( $show, "\n"); + } + } + + $m=0; /* line */ + $errors=0; /* number of errors detected */ + $income_accno = ""; + $expense_accno = ""; + while ( ($zeile=fgetcsv($f,1200,$trenner)) != FALSE) { + + $i=0; /* column */ + $m++; /* increase line */ + + $sql="insert into $file "; + $keys="("; + $vals=" values ("; + + show( $show, "\n"); + + /* for each column */ + $dienstleistung=false; + $artikel=-1; + $partNr=false; + foreach($zeile as $data) { + /* check if column will be imported */ + if (!in_array(trim($in_fld[$i]),$parts_fld)) { + $i++; + continue; + }; + $data=trim($data); + $data=addslashes($data); + $key=$in_fld[$i]; + /* add key and data */ + if ($data==false or empty($data) or !$data) { + show( $show, "\n"); + $i++; + continue; + } + + /* special case partsgroup */ + if ($key == "partsgroup") { + + /* get ID of partsgroup or add new + * partsgroup_id */ + $data = getPartsgroupId($db, $data, $insert); + $key = "partsgroup_id"; + + /* TODO error handling */ + + } else if ($key == "lastcost" || + $key == "sellprice") { + + /* convert 0,0 numeric into 0.0 */ + $data = str_replace(",", ".", $data); + + } else if ($key == "partnumber") { + $partNr=true; + $partnumber=chkPartNumber($db,$data,$check); + if ($partnumber=="") { + show( $show, "\n"); + $i++; + continue; + } else { + //$keys.="partnumber, "; + $data=$partnumber; + //show( $show, "\n"); + } + } else if ($key == "description") { + $data=addslashes($data); + } else if ($key == "notes") { + $data=addslashes($data); + } else if ($key == "unit") { + /* convert stück and Stunde */ + if (preg_match("/^st..?ck$/i", $data)) + $data = "Stck"; + else if ($data == "Stunde") + $data = "Std"; + /* check if unit exists */ + if (!existUnit($db, $data)) { + echo "Error in line $m: "; + echo "Einheit $data existiert nicht "; + echo "Bitte legen Sie diese Einheit an
"; + $errors++; + } + } else if ($key == "art") { + if ($maske["ware"]=="G" and strtoupper($data)=="D") { $artikel=false; } + else if ($maske["ware"]=="G") { $artikel=true; }; + $i++; + continue; + } else if ($key == "income_accno") { + $income_accno = $data; + $i++; + show( $show, "\n"); + continue; + } else if ($key == "expense_accno") { + $expense_accno = $data; + $i++; + show( $show, "\n"); + continue; + } + /* convert JA to Yes */ + if ($data == "J" ) + $data = "Y"; + + $vals.="'".$data."',"; + show( $show, "\n"); + $keys.=$key.","; + + $i++; + } + if ($artikel==-1) { + if ($maske["ware"]=="D") { $artikel=false; } + else { $artikel=true; }; + } + if ($maske["bugrufix"]==1) { + $bg = $maske["bugru"]; + } else { + /* search for buchungsgruppe */ + $bg = getBuchungsgruppe($db, $income_accno, $expense_accno); + } + /* nothing found? user must create one */ + if ($bg == "") { + if ($maske["bugrufix"]==2) { + $bg = $maske["bugru"]; + } else { + echo "Error in line $m: "; + echo "Keine Buchungsgruppe gefunden für
"; + echo "Erlöse Inland: $income_accno
"; + //echo "Erlöse EU: $income_accno_1
"; + //echo "Erlöse Ausland: $income_accno_3
"; + echo "Bitte legen Sie eine an
"; + echo "
"; + $errors++; + } + } + if ($bg > 0) { + /* found one, add income_accno_id etc from buchungsgr. + */ + $keys.="buchungsgruppen_id, "; + $vals.="'$bg', "; + /* XXX nur bei artikel!!! */ + if ($artikel) { + $keys.="inventory_accno_id, "; + $vals.=getFromBG($db, $bg, "inventory_accno_id")." ,"; + }; + $keys.="income_accno_id, "; + $vals.=getFromBG($db, $bg, "income_accno_id_0")." ,"; + $keys.="expense_accno_id,"; + $vals.=getFromBG($db, $bg, "expense_accno_id_0")." ,"; + } + if ($partNr==false) { + $partnumber=chkPartNumber($db,"",$check); + if ($partnumber=="") { + show( $show, "\n"); + $errors++; + } else { + $keys.="partnumber, "; + $vals.="'$partnumber',"; + show( $show, "\n"); + } + } + $sql.=$keys."import)"; + $sql.=$vals.time().")"; + //show( $show, "\n"); + + if ($insert) { + show( $show, "\n"); + } + + show( $show, "\n"); + } + + show( $show, "

$fld
NULL	NULL	$partnumber	$data	$data	$data	NULL	$partnumber	$sql	"); + $db->showErr = TRUE; + $rc=$db->query($sql); + if (!$rc) + echo "Fehler"; + show( $show, "

\n"); + fclose($f); + return $errors; +} + +?> + -- 2.20.1


Trennzeichen
Test	ja
Art	Ware + Dienstleistung + gemischt (Spalte 'art' vorhanden)
Default Bugru	+ nie + für alle Artikel verwenden + für Artikel ohne passende Bugru +
Daten