X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=lxo-import%2Fparts_import.php;h=94f2b6f15fc9db8ef8a77bf68808154b32a3f998;hb=3a56098f9b80397fe27922f1661b2069183aa2d6;hp=0107c39dbc1f3bd86d647e25514107f16919c76d;hpb=885e7d7601de767605f2938a69bae18258c62696;p=kivitendo-erp.git diff --git a/lxo-import/parts_import.php b/lxo-import/parts_import.php index 0107c39db..94f2b6f15 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'); @@ -107,11 +128,13 @@ function updParts($db,$insert,$show,$partnumber,$lastcost,$sellprice, return $rc; } -function getMakemodel($db,$check,$hersteller,$model,$partsid,$add=true) { +function getMakemodel($db,$check,$hersteller,$model,$partsid,$lastcost,$add=true) { $sql="select * from makemodel where make = $hersteller and model = '$model' and parts_id = $partsid"; $rs=$db->getAll($sql); if (empty($rs[0]["id"]) && $add) { - $sql="insert into makemodel (parts_id,make,model) values ($partsid,'$hersteller','$model')"; + if (!$lastcost) $lastcost=0.00; + $sql="insert into makemodel (parts_id,make,model,lastcost,lastupdate,sortorder) "; + $sql.="values ($partsid,'$hersteller','$model',$lastcost,now(),1)"; $rc=$db->query($sql); } } @@ -139,7 +162,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 +212,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"]; @@ -198,39 +253,60 @@ function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $ins $wgtrenner=$maske["wgtrenner"]; $Update=($maske["update"]=="U")?true:false; $UpdText=($maske["TextUpd"]=="1")?true:false; + $vendnr=($maske["vendnr"]=="t")?true:false; + $modnr=($maske["modnr"]=="t")?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("description");show("notes"); show("ean"); - show("weight"); show("image"); show("partsgroup_id"); - show("bg"); show("income_accno"); show("expense_accno"); - show("inventory_accno"); show("microfiche");show("drawing");show("rop"); - show("assembly");show("makemodel"); show("shop"); show(""); + 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"); + show("inventory_accno"); show("microfiche"); show("drawing"); + show("rop"); show("assembly"); show("makemodel"); show("shop"); show(""); 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 +340,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 +367,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 { @@ -305,31 +387,39 @@ echo "!".$notes1."!
"; $partsgroup_id = getPartsgroupId($db, $pgname, $insert); } + /* Ware oder Dienstleistung */ + if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="D") or $maske["ware"]=="D") { + $artikel = false; + } else if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="W") or $maske["ware"]=="W") { + $artikel = true; + } + /* sind Hersteller und Modelnummer hinterlegt wenn ja, erfolgt er insert später */ - if (!empty($zeile[$fldpos["makemodel"]]) and !$artikel) { + $makemodel = 'f'; + if (!empty($zeile[$fldpos["makemodel"]]) and $artikel) { $mm = $zeile[$fldpos["makemodel"]]; if (Translate) translate($mm); - $hersteller=suchFirma("vendor",$mm); - $hersteller=$hersteller["cp_cv_id"]; - if (!empty($zeile[$fldpos["model"]])) { + if ($vendnr) { + $hersteller=getFirma($mm,"vendor"); + } else { + $hersteller=suchFirma("vendor",$mm); + $hersteller=$hersteller["cp_cv_id"]; + } + if (!empty($zeile[$fldpos["model"]]) and $hersteller>0) { $mo = $zeile[$fldpos["model"]]; if (Translate) translate($mo); $model = $mo; $makemodel = 't'; - } else { + } else if ($modnr and $hersteller>0) { + $model = ''; + $makemodel = 't'; + } else { unset($hersteller); $makemodel = 'f'; } } - /* Ware oder Dienstleistung */ - if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="D") or $maske["ware"]=="D") { - $artikel = false; - } else if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="W") or $maske["ware"]=="W") { - $artikel = true; - } - /* Einheit ermitteln */ if ($zeile[$fldpos["unit"]]=="") { //Keine Einheit mitgegeben @@ -402,7 +492,6 @@ echo "!".$notes1."!
"; } else { $shop = $maske["shop"]; } - // Artikel updaten if (getPartsid($db,trim($zeile[$fldpos["partnumber"]]))) { @@ -410,11 +499,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,18 +524,20 @@ 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) { + if ($hersteller>0 ) { // && $model) { $partsid=getPartsid($db,$zeile[$fldpos["partnumber"]]); - if ($partsid) { - getMakemodel($db,$check,$hersteller,$model,$partsid,true); + if ($partsid) { + getMakemodel($db,$check,$hersteller,$model,$partsid,$lastcost,true); } } unset($zeile);