X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=lxo-import%2Fparts_import.php;h=94f2b6f15fc9db8ef8a77bf68808154b32a3f998;hb=829f6742c99fdf3b3ef78713c3ce1c1266f67d98;hp=a764ee717b271640ba35f5179945d7b6592a5d8a;hpb=c4f960a5ae395e5594ae9063945f5f0b16e3d053;p=kivitendo-erp.git diff --git a/lxo-import/parts_import.php b/lxo-import/parts_import.php index a764ee717..94f2b6f15 100644 --- a/lxo-import/parts_import.php +++ b/lxo-import/parts_import.php @@ -41,18 +41,27 @@ function insertParts($db,$insert,$show,$data,$pricegroup) { 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,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; } @@ -119,11 +128,13 @@ function updParts($db,$insert,$show,$partnumber,$lastcost,$sellprice,$listprice, 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); } } @@ -203,7 +214,7 @@ function getStdUnit($db,$type) { function insPrices($db,$pid,$prices) { $rc = $db->query("BEGIN"); - $sql="delete from prices where parts_id = ".$pid; + $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) { @@ -217,7 +228,24 @@ function insPrices($db,$pid,$prices) { return true; } -function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $insert, $show ,$maske) { +/** + * 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"]; @@ -225,9 +253,13 @@ 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); @@ -242,28 +274,39 @@ function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $ins $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("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("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("\""=>"","'"=>"")))); @@ -300,10 +343,11 @@ function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $ins $listprice = str_replace(",", ".", $zeile[$fldpos["listprice"]]); $lastcost = str_replace(",", ".", $zeile[$fldpos["lastcost"]]); if ($prices) { - foreach ($prices as $pkey=>$val) { - $pricegroup[$val] = str_replace(",", ".", $zeile[$fldpos[$pkey]]); - } - } + 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; } @@ -343,31 +387,39 @@ function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $ins $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 @@ -472,18 +524,20 @@ function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $ins "description"=>$description,"notes"=>$notes, "ean"=>$zeile[$fldpos["ean"]],"unit"=>$unit, "weight"=>$weight,"image"=>$zeile[$fldpos["image"]], - "sellprice"=>$sellprice,"lastcost"=>$lastcost,"listprice"=>$listprice, + "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),$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);