X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=lxo-import%2Fparts_import.php;h=0db931230016d76c2a41bb932b50a11e7cad2c28;hb=997c9f2390c5cf472b221b75d55e1c866612219a;hp=0107c39dbc1f3bd86d647e25514107f16919c76d;hpb=885e7d7601de767605f2938a69bae18258c62696;p=kivitendo-erp.git diff --git a/lxo-import/parts_import.php b/lxo-import/parts_import.php index 0107c39db..0db931230 100644 --- a/lxo-import/parts_import.php +++ b/lxo-import/parts_import.php @@ -21,10 +21,19 @@ function getPartsgroupId($db, $value, $add) { } return $rs[0]["id"]; } -function insertParts($db,$insert,$show,$data) { +function getPricegroup($db) { + $sql="SELECT * from pricegroup"; + $rs=$db->getAll($sql); + $data = false; + if ($rs) foreach ($rs as $row) { + $data["pg_".strtolower($row["pricegroup"])]=$row["id"]; + }; + return $data; +} +function insertParts($db,$insert,$show,$data,$pricegroup) { if ($show) { show('',false); - show($data["partnumber"]); show($data["lastcost"]); show($data["sellprice"]); + show($data["partnumber"]); show($data["lastcost"]); show($data["sellprice"]); show($data["listprice"]); show($data["description"]); show(substr($data["notes"],0,25));show($data["ean"]); show($data["weight"]); show($data["image"]); show($data["partsgroup_id"]); show($data["buchungsgruppen_id"]);show($data["income_accno"]); show($data["expense_accno"]); @@ -32,21 +41,31 @@ function insertParts($db,$insert,$show,$data) { show($data["rop"]); show($data["assembly"]); show($data["makemodel"]); show($data["shop"]); } + /*foreach ($data as $key=>$val) { echo $key.":".gettype($val).":".gettype($data[$key]).":".$val."
"; }*/ if ($insert) { + $data["import"]=time(); $sqlIa = 'INSERT INTO parts ('; $sqlIa .= 'partnumber,description,notes,ean,unit,'; - $sqlIa .= 'weight,image,sellprice,lastcost,partsgroup_id,'; + $sqlIa .= 'weight,image,sellprice,listprice,lastcost,partsgroup_id,'; $sqlIa .= 'buchungsgruppen_id,income_accno_id,expense_accno_id,inventory_accno_id,'; $sqlIa .= 'microfiche,drawing,rop,assembly,shop,makemodel,import) '; - $sqlIa .= 'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'; - $data["import"]=time(); - $rc=$db->execute($sqlIa,$data); + //$sqlIa .= 'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'; + //$rc=$db->execute($sqlIa,$data); + $sqlIa .= "VALUES ('%s','%s','%s','%s','%s',%0.5f,'%s',%0.5f,%0.5f,%0.5f,%d,%d,%d,%d,%d,'%s','%s',%.0f,'%s','%s','%s',%s)"; + $sql = sprintf($sqlIa,$data['partnumber'],$data['description'],$data['notes'],$data['ean'], + $data['unit'],$data['weight'],$data['image'],$data['sellprice'], + $data['listprice'],$data['lastcost'],$data['partsgroup_id'], + $data['buchungsgruppen_id'],$data['income_accno_id'],$data['expense_accno_id'], + $data['inventory_accno_id'],$data['microfiche'],$data['drawing'],$data['rop'], + $data['assembly'],$data['shop'],$data['makemodel'],$data['import']); + $rc = $db->query($sql); } else { $rc = true; } + if ($pricegroup) $ok = insPrices($db,$data["partnumber"],$pricegroup); if ($show) { if ($rc) show('ok'); @@ -56,19 +75,20 @@ function insertParts($db,$insert,$show,$data) { } return $rc; } -function updPrice($db,$insert,$show,$partnumber,$lastcost,$sellprice,$shop) { +function updPrice($db,$insert,$show,$partnumber,$lastcost,$sellprice,$listprice,$pricegroup,$shop) { if ($show) { show('',false); - show($partnumber); show($lastcost); show($sellprice); + show($partnumber); show($lastcost); show($sellprice); show($listprice); } if ($insert) { $sqlPr = 'UPDATE PARTS SET '; - $sqlPr .= 'sellprice = ?, lastcost = ?, shop = ? '; + $sqlPr .= 'sellprice = ?, listprice = ?, lastcost = ?, shop = ? '; $sqlPr .= 'WHERE partnumber = ?'; - $rc=$db->execute($sqlPr,array("sellprice"=>$sellprice,"lastcost"=>$lastcost,"shop"=>$shop,"partnumber"=>$partnumber)); + $rc=$db->execute($sqlPr,array("sellprice"=>$sellprice,"listprice"=>$listprice,"lastcost"=>$lastcost,"shop"=>$shop,"partnumber"=>$partnumber)); } else { $rc = true; } + if ($pricegroup) $ok = insPrices($db,$partnumber,$pricegroup); if ($show) { if ($rc) show('ok'); @@ -78,25 +98,26 @@ function updPrice($db,$insert,$show,$partnumber,$lastcost,$sellprice,$shop) { } return $rc; } -function updParts($db,$insert,$show,$partnumber,$lastcost,$sellprice, +function updParts($db,$insert,$show,$partnumber,$lastcost,$sellprice,$listprice, $description,$notes,$ean,$weight,$image, - $partsgroup_id, $shop) { + $partsgroup_id,$pricegroup, $shop) { if ($show) { show('',false); - show($partnumber); show($lastcost); show($sellprice); + show($partnumber); show($lastcost); show($sellprice); show($listprice); show($description); show(substr($notes,0,25));show($ean); show($weight); show($image); show($partsgroup_id); } if ($insert) { $sqlUa = 'UPDATE PARTS SET '; $sqlUa .= 'description = ?, notes = ?, ean = ?, weight = ?, image = ?, '; - $sqlUa .= 'sellprice = ?, lastcost = ?, partsgroup_id = ?, shop = ? '; + $sqlUa .= 'sellprice = ?, listprice = ?, lastcost = ?, partsgroup_id = ?, shop = ? '; $sqlUa .= 'WHERE partnumber = ?'; $rc=$db->execute($sqlUa,array($description,$notes,$ean,$weight,$image, - $sellprice,$lastcost,$partsgroup_id,$shop,$partnumber)); + $sellprice,$listprice,$lastcost,$partsgroup_id,$shop,$partnumber)); } else { $rc = true; } + if ($pricegroup) $ok = insPrices($db,$partnumber,$pricegroup); if ($show) { if ($rc) show('ok'); @@ -139,7 +160,6 @@ function newPartNumber($db,$check) { $rs=$db->getAll($sql); if ($rs[0]["articlenumber"]) { preg_match("/([^0-9]+)?([0-9]+)([^0-9]+)?/", $rs[0]["articlenumber"] , $regs); - print_r($regs); $number=$regs[1].($regs[2]+1).$regs[3]; } $sql = "update defaults set articlenumber = '$number'"; @@ -190,7 +210,40 @@ function getStdUnit($db,$type) { return $rs[0]["name"]; } -function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $insert, $show ,$maske) { +function insPrices($db,$pid,$prices) { + $rc = $db->query("BEGIN"); + $sql="delete from prices where parts_id = (select id from parts where partnumber = '$pid')"; + $rc = $db->query($sql); + $sql = "insert into prices (parts_id,pricegroup_id,price) values ((select id from parts where partnumber = '%s'),%d,%0.5f)"; + foreach ($prices as $key => $val) { + $rc = $db->query(sprintf($sql,$pid,$key,$val)); + if (!$rc) { + $db->query("ROLLBACK"); + return false; + } + } + $db->query("COMMIT"); + return true; +} + +/** + * TODO: short description. + * + * @param double $db + * @param mixed $file + * @param mixed $fields + * @param mixed $check + * @param int $insert + * @param string $show + * @param mixed $maske + * + * @return TODO + */ +function import_parts($db, $file, $fields, $check, $maske) { + $insert = !$maske["test"]; + $show = $maske["show"]; + $trennzeichen = ($maske["trennzeichen"])?$maske["trennzeichen"]:""; + $trenner = ($maske["trenner"])?$maske["trenner"]:","; $precision=$maske["precision"]; $quotation=$maske["quotation"]; $quottype=$maske["quottype"]; @@ -199,25 +252,48 @@ function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $ins $Update=($maske["update"]=="U")?true:false; $UpdText=($maske["TextUpd"]=="1")?true:false; - $stdunitW=getStdUnit($db,"dimension"); - $stdunitD=getStdUnit($db,"service"); + //$stdunitW=getStdUnit($db,"dimension"); + //$stdunitD=getStdUnit($db,"service"); + $stdunitW=$maske["dimensionunit"]; + $stdunitD=$maske["serviceunit"]; if ($quottype=="P") $quotation=($quotation+100)/100; if ($show && !$insert) show("Testimport",false); if ($show) show("\n",false); /* field description */ - $parts_fld = array_keys($fields); + $prices = getPricegroup($db); + if ($prices) { + $priceskey = array_keys($prices); + $parts_fld = array_merge(array_keys($fields),$priceskey); + } else { + $parts_fld = array_keys($fields); + } + + if ($trenner=="other") $trenner=trim($trennzeichen); + if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1)); /* open csv file */ - $f=fopen($file.'.csv',"r"); - + if (file_exists($file."head.csv")) { + $fh=fopen($file.'head.csv',"r"); + // Erst einmal die erste Zeile mit den richtigen Feldbezeichnungen einlesen. + $infld=fgetcsv($fh,1200,$trenner); + fclose($fh); + $f=fopen($file.'.csv',"r"); + // Erst einmal die erste Zeile mit den falschen Feldbezeichnungen einlesen. + $tmp=fgetcsv($f,1200,$trenner); + } else { + $f=fopen($file.'.csv',"r"); + // Erst einmal die erste Zeile mit den Feldbezeichnungen einlesen. + $infld=fgetcsv($f,1200,$trenner); + } + /* * read first line with table descriptions */ if ($show) { show('',false); - show("partnumber"); show("lastcost"); show("sellprice"); + show("partnumber"); show("lastcost"); show("sellprice"); show("listprice"); show("description");show("notes"); show("ean"); show("weight"); show("image"); show("partsgroup_id"); show("bg"); show("income_accno"); show("expense_accno"); @@ -226,11 +302,7 @@ function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $ins show("\n",false); } - if ($trenner=="other") $trenner=trim($trennzeichen); - if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1)); - // Erst einmal die erste Zeile mit den Feldbezeichnungen einlesen. - $infld=fgetcsv($f,1200,$trenner); $p=0; foreach ($infld as $fld) { $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>"")))); @@ -264,13 +336,21 @@ function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $ins /* VK-Preis bilden */ $sellprice = str_replace(",", ".", $zeile[$fldpos["sellprice"]]); + $listprice = str_replace(",", ".", $zeile[$fldpos["listprice"]]); $lastcost = str_replace(",", ".", $zeile[$fldpos["lastcost"]]); + if ($prices) { + foreach ($prices as $pkey=>$val) { + if (array_key_exists($pkey,$fldpos)) + $pricegroup[$val] = str_replace(",", ".", $zeile[$fldpos[$pkey]]); + } + } if ($quotation<>0) { if ($quottype=="A") { $sellprice += $quotation; } else { $sellprice = $sellprice * $quotation; } }; if ($lastcost=="") unset($lastcost); if ($sellprice=="") unset($sellprice); + if ($listprice=="") unset($listprice); /* Langtext zusammenbauen */ if ($zeile[$fldpos["notes"]]) { @@ -283,9 +363,7 @@ function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $ins //Kundenspezifisch: //$notes1 = preg_replace('/""[^ ]/','"',$zeile[$fldpos["notes1"]]); $notes1 = addslashes($zeile[$fldpos["notes1"]]); -echo "!".$notes1."!
"; if (Translate) translate($notes1); -echo "!".$notes1."!
"; if ($notes) { $notes .= "\n".$notes1; } else { @@ -307,6 +385,7 @@ echo "!".$notes1."!
"; /* sind Hersteller und Modelnummer hinterlegt wenn ja, erfolgt er insert später */ + $makemodel = 'f'; if (!empty($zeile[$fldpos["makemodel"]]) and !$artikel) { $mm = $zeile[$fldpos["makemodel"]]; if (Translate) translate($mm); @@ -402,7 +481,6 @@ echo "!".$notes1."!
"; } else { $shop = $maske["shop"]; } - // Artikel updaten if (getPartsid($db,trim($zeile[$fldpos["partnumber"]]))) { @@ -410,11 +488,11 @@ echo "!".$notes1."!
"; if ($Update) { /* Updates durchführen */ if ($UpdText=='1') { - $u += updParts($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice, + $u += updParts($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,$listprice, $description,$notes,$zeile[$fldpos["ean"]],$weight, - $zeile[$fldpos["image"]],$partsgroup_id, $shop); + $zeile[$fldpos["image"]],$partsgroup_id,$pricegroup, $shop); } else { - $u += updPrice($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,$shop); + $u += updPrice($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,$listprice,$pricegroup,$shop); } continue; // nächste Zeile @@ -435,13 +513,15 @@ echo "!".$notes1."!
"; "description"=>$description,"notes"=>$notes, "ean"=>$zeile[$fldpos["ean"]],"unit"=>$unit, "weight"=>$weight,"image"=>$zeile[$fldpos["image"]], - "sellprice"=>$sellprice,"lastcost"=>$lastcost, + "sellprice"=>$sellprice, + "lastcost"=>$lastcost, + "listprice"=>$listprice, "partsgroup_id"=>$partsgroup_id, - "buchungsgruppen_id"=>$bg,"income_accno"=>$income_accno, - "expense_accno"=>$expense_accno,"inventory_accno"=>$inventory_accno, + "buchungsgruppen_id"=>$bg,"income_accno_id"=>$income_accno, + "expense_accno_id"=>$expense_accno,"inventory_accno_id"=>$inventory_accno, "microfiche"=>$zeile[$fldpos["microfiche"]],"drawing"=>$zeile[$fldpos["drawing"]], "rop"=>$rop,"assembly"=>$assembly, - "shop"=>$shop,"makemodel"=>$makemodel) + "shop"=>$shop,"makemodel"=>$makemodel),$pricegroup ); if ($hersteller>0 && $model) { $partsid=getPartsid($db,$zeile[$fldpos["partnumber"]]);