X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=lxo-import%2Fparts_import.php;h=a66e4049f704fa49f312a7ac6dbb0643e1e481d1;hb=75e721508b508648ffe2454f10cfe29ba8a359dd;hp=19e0f257d9252a1b1a94eaa665ed40c3a44855c5;hpb=bd10a269df8a3de2653d02b8771459a4e34165a9;p=kivitendo-erp.git diff --git a/lxo-import/parts_import.php b/lxo-import/parts_import.php index 19e0f257d..a66e4049f 100644 --- a/lxo-import/parts_import.php +++ b/lxo-import/parts_import.php @@ -23,12 +23,27 @@ function getPartsgroupId($db, $value, $add) { return $rs[0]["id"]; } +//Muß noch eingebunden werden +function getMakemodel($db,$hersteller,$model,$partsid,$add=true) { + $sql="select * from makemodel where make like '$hersteller' and model like = '$model'"; + $rs=$db->getAll($sql); + if (empty($rs[0]["id"]) && $add) { + $sql="insert into makemodel (parts_id,make,model) values ($partsid,'$hersteller','$model')"; + $rc=$db->query($sql); + if (!$rc) return "f"; + return getMakemodel($db,$hersteller,$model,$partsid,false); + } + if ($rs[0]["parts_id"]==$partsid) { return "t"; } + else { return "f"; } +} + function getAccnoId($db, $accno) { $sql = "select id from chart where accno='$accno'"; $rs=$db->getAll($sql); return $rs[0]["id"]; } +//Auf Artikelnummer testen, bzw. neue Nummer erzeugen function chkPartNumber($db,$number,$check) { if ($number<>"") { $sql = "select * from parts where partnumber = '$number'"; @@ -40,7 +55,10 @@ function chkPartNumber($db,$number,$check) { $rc=$db->query("BEGIN"); $sql = "select articlenumber from defaults"; $rs=$db->getAll($sql); - $number=$rs[0]["articlenumber"]+1; + if ($rs[0]["articlenumber"]) { + preg_match("/([^0-9]+)?([0-9]+)([^0-9]+)?/", $rs[0]["articlenumber"] , $regs); + $number=$regs[1].($regs[2]+1).$regs[3]; + } $sql = "update defaults set articlenumber = '$number'"; $rc=$db->query($sql); $rc=$db->query("COMMIT"); @@ -51,8 +69,29 @@ function chkPartNumber($db,$number,$check) { return $number; } +//Artikelnummer testen und wenn vorhanden Preis ändern +function chkPartNumberUpd($db,$sellprice,$partnumber,$check){ + if ($partnumber=="") { + $nummer=chkPartNumber($db,$partnumber,$check); + if ($nummer=="") { return -99; } + else { return $nummer; }; + } + $sql = "select * from parts where partnumber = '$partnumber'"; + $rs=$db->getAll($sql); + if ($rs[0]["id"]>0) { + if ($check) return -1; + $sql="update parts set sellprice = $sellprice where partnumber = '$partnumber'"; + $rc=$db->query($sql); + if ($rc) return -1; + return -99; + } + $nummer=chkPartNumber($db,$partnumber,$check); + if ($nummer=="") { return -99; } + else { return $nummer; }; +} + function getBuchungsgruppe($db, $income, $expense) { - + $income_id = getAccnoId($db, $income); $expense_id = getAccnoId($db, $expense); //$accno0_id = getAccnoId($db, $accno0); @@ -90,8 +129,23 @@ function show($show, $things) { echo $things; } -function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$maske) { +function getStdUnit($db,$type) { + $sql="select * from units where type='$type' order by sortkey limit 1"; + $rs=$db->getAll($sql); + if (empty($rs[0]["name"])) return "Stck"; + return $rs[0]["name"]; +} +function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $insert, $show,$maske) { + + $pgshow=false; + $note2show=false; + $fehler=0; + $precision=$maske["precision"]; + $quotation=$maske["quotation"]; + $quottype=$maske["quottype"]; + + $Update=($maske["update"]=="U")?true:false; /* field description */ $parts_fld = array_keys($fields); @@ -101,35 +155,77 @@ function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$mas /* * read first line with table descriptions */ - show( $show, "
# | \n"); + if ($trenner=="other") $trenner=trim($trennzeichen); + if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1)); $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, "$fld | \n"); + if ($fld=="partsgroup" || $fld=="partsgroup1" ) { + $pgshow=true; + } else { + show( $show, "$fld | \n"); + } } } - + if (!in_array("unit",$infld)) { + $stdunitW=getStdUnit($db,"dimension"); + $stdunitD=getStdUnit($db,"service"); + $unit=true; + show( $show, "unit | \n"); + }; + if ($pgshow) show( $show, "partsgroup | \n"); + $posprice=0; + $posnumber=0; + $j=0; + foreach ($infld as $value) { + if ($infld[$j]=="sellprice") $posprice=$j; + if ($infld[$j]=="partnumber") $posnumber=$j; + $j++; + } $m=0; /* line */ $errors=0; /* number of errors detected */ $income_accno = ""; $expense_accno = ""; - while ( ($zeile=fgetcsv($f,1200,$trenner)) != FALSE) { - + if ($quottype=="P") $quotation=($quotation+100)/100; + while ( ($zeile=fgetcsv($f,15000,$trenner)) != FALSE) { $i=0; /* column */ $m++; /* increase line */ + if ($Update) { + $sellprice=$zeile[$posprice]; + $partnumber=$zeile[$posnumber]; + $sellprice = str_replace(",", ".", $sellprice); + if ($quotation<>0) { + if ($quottype=="A") { $sellprice += $quotation; } + else { $sellprice = $sellprice * $quotation; } + }; + if ($precision>=0) $sellprice = round($sellprice,$precision); + $rc=chkPartNumberUpd($db,$sellprice,$partnumber,!$insert); + if ($rc==-1) { + show($show,"||||||
Update | $partnumber:$sellprice | |||||||||
Fehler Zeile $m | ||||||||||
$m | \n"); /* for each column */ $dienstleistung=false; $artikel=-1; $partNr=false; + $pg_name_val=""; foreach($zeile as $data) { /* check if column will be imported */ if (!in_array(trim($in_fld[$i]),$parts_fld)) { @@ -137,31 +233,35 @@ function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$mas 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, "NULL | \n"); + + /* special case partsgroup1 */ + if ($key == "partsgroup1") { + if ($pg_name_val<>"") { + if ($data<>"") $pg_name_val.="!".$data; + } else { + $pg_name_val=$data; + } + $i++; + continue; + } else if ($key == "partsgroup") { + /* special case partsgroup */ + $pg_name_val=$data; $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); - + if ($key == "sellprice") { + if ($quotation<>0) { + if ($quottype=="A") { $data += $quotation; } + else { $data = $data * $quotation; } + }; + if ($precision>=0) $data = round($data,$precision); + } } else if ($key == "partnumber") { $partNr=true; $partnumber=chkPartNumber($db,$data,$check); @@ -175,10 +275,18 @@ function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$mas //show( $show, "$partnumber | \n"); } } else if ($key == "description") { + $data=mb_convert_encoding($data,"ISO-8859-15","auto"); $data=addslashes($data); } else if ($key == "notes") { + $data=mb_convert_encoding($data,"ISO-8859-15","auto"); $data=addslashes($data); } else if ($key == "unit") { + if ($data=="") { + if ($maske["ware"]=="W") { $data=$stdunitW; } + else if ($maske["ware"]=="D") { $data=$stdunitD; } + //else if ($maske["ware"]=="G") { $data=$stdunitD; //Noch machen!} + else { $data=$stdunitW; }; + } /* convert stück and Stunde */ if (preg_match("/^st..?ck$/i", $data)) $data = "Stck"; @@ -207,16 +315,38 @@ function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$mas show( $show, "$data | \n"); continue; } + if ($data==false or empty($data) or !$data) { + show( $show, "NULL | \n"); + $i++; + continue; + } /* convert JA to Yes */ - if ($data == "J" ) - $data = "Y"; - + if ($data === "J" || $data === "j") $data = "Y"; $vals.="'".$data."',"; - show( $show, "$data | \n"); + show( $show, "".htmlentities($data)." | \n"); $keys.=$key.","; - $i++; } + if ($unit) { + if ($maske["ware"]=="D") { $einh=$stdunitD; } + else { $einh=$stdunitW; } + $keys.="unit,"; + $vals.="'$einh',"; + show( $show,"$einh | \n"); + } + /* special case partsgroup */ + if ($pgshow) { + if ($pg_name_val) { + /* get ID of partsgroup or add new + * partsgroup_id */ + $ID = getPartsgroupId($db, $pg_name_val, $insert); + $keys.= "partsgroup_id,"; + $vals.="'".$ID."',"; + show( $show, "".htmlentities($pg_name_val).":$ID | \n"); + } else { + show( $show,"NULL | \n"); + } + } if ($artikel==-1) { if ($maske["ware"]=="D") { $artikel=false; } else { $artikel=true; }; @@ -224,24 +354,24 @@ function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$mas 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) { + if ($income_accno<>"" and $expense_accno<>"") { + /* search for buchungsgruppe */ + $bg = getBuchungsgruppe($db, $income_accno, $expense_accno); + if ($bg == "" and $maske["bugrufix"]==2 and $maske["bugru"]<>"") { + $bg = $maske["bugru"]; + } + } else if ($maske["bugru"]<>"" and $maske["bugrufix"]==2) { $bg = $maske["bugru"]; } else { + /* nothing found? user must create one */ echo "Error in line $m: "; echo "Keine Buchungsgruppe gefunden für"); $db->showErr = TRUE; $rc=$db->query($sql); - if (!$rc) + if (!$rc) { echo "Fehler"; + $fehler++; + } show( $show, " | \n"); } @@ -286,6 +418,7 @@ function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$mas show( $show, "