X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=lxo-import%2Fparts_import.php;h=7b4d69bee31c103c2a7e95c3439a909ced1b8be2;hb=f141166b1c9f6134c6898369da898f53e579f161;hp=d147a2ffa4f667070664f9ca3bf8f37cb4f2839b;hpb=7141c353c5f1e42775dff5ea9a982e5b1dac2af4;p=kivitendo-erp.git diff --git a/lxo-import/parts_import.php b/lxo-import/parts_import.php index d147a2ffa..7b4d69bee 100644 --- a/lxo-import/parts_import.php +++ b/lxo-import/parts_import.php @@ -23,6 +23,19 @@ function getPartsgroupId($db, $value, $add) { return $rs[0]["id"]; } +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); @@ -53,6 +66,26 @@ function chkPartNumber($db,$number,$check) { } return $number; } +function chkPartNumberUpd($db,$sellprice,$partnumber,$descript,$note,$check,$shop='n'){ + 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) { + $sql="update parts set sellprice = $sellprice, shop='$shop'"; + if ($descript) $sql.=",description='$descript',notes='$note'"; + $sql.=" 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) { @@ -93,8 +126,27 @@ 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"]; + $shop=$maske["shop"]; + $wgtrenner=$maske["wgtrenner"]; + $UpdText=($maske["TextUpd"]=="1")?true:false; + + $Update=($maske["update"]=="U")?true:false; /* field description */ $parts_fld = array_keys($fields); @@ -105,33 +157,109 @@ 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, "\n"); + if (substr($fld,0,10)=="partsgroup") { + $pgshow=true; + } else if ($fld=="notes" || $fld=="notes1" ) { + $note2show=true; + } else { + show( $show, "\n"); + } } } - + if (!in_array("unit",$infld)) { + $stdunitW=getStdUnit($db,"dimension"); + $stdunitD=getStdUnit($db,"service"); + $unit=true; + show( $show, "\n"); + }; + if ($pgshow) show( $show, "\n"); + if ($note2show) show( $show, "\n"); + $posprice=0; + $posnumber=0; + $posdescript=0; + $posnotes=0; + $posnotes1=0; + $j=0; + foreach ($infld as $value) { + if ($infld[$j]=="sellprice") $posprice=$j; + if ($infld[$j]=="partnumber") $posnumber=$j; + if ($infld[$j]=="description") $posdescript=$j; + if ($infld[$j]=="notes") $posnotes=$j; + if ($infld[$j]=="notes1") $posnotes1=$j; + $j++; + } $m=0; /* line */ $errors=0; /* number of errors detected */ $income_accno = ""; $expense_accno = ""; - while ( ($zeile=fgetcsv($f,1200,$trenner)) != FALSE) { + + /*if ($insert) { + $sql="update parts set shop = 'n' where partnumber like '______'"; + $rc=$db->query($sql); + //echo $sql; print_r($rc); echo "
"; + if (!$rc) { + echo "Fehler: Artikel nicht aus dem Shop genommen"; + } + };*/ + if ($quottype=="P") $quotation=($quotation+100)/100; + while ( ($zeile=fgetcsv($f,120000,$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); + if ($UpdText) { + $description=$zeile[$posdescript]; + $note=$zeile[$posnotes]; + $note1=$zeile[$posnotes1]; + $note=mb_convert_encoding($note,"ISO-8859-15","ISO-8859-15"); + $note=preg_replace('/""[^ ]/','"',$note); + $note=" \n".addslashes($note); + $note1=mb_convert_encoding($note1,"ISO-8859-15","ISO-8859-15"); + $note1=preg_replace('/""[^ ]/','"',$note1); + $note.=" \n".addslashes($note1); + $rc=chkPartNumberUpd($db,$sellprice,$partnumber,$description,$note,$check); + } else { + $rc=chkPartNumberUpd($db,$sellprice,$partnumber,false,false,$check); + } + if ($rc==-1) { + show($show,"\n"); + continue; + } else if ($rc==-99) { + show($show,"\n"); + continue; + } else { + $zeile[$posnumber]=$rc; + } + }; $sql="insert into $file "; $keys="("; $vals=" values ("; - show( $show, "\n"); /* for each column */ $dienstleistung=false; $artikel=-1; $partNr=false; + $pg_name_val=array(); + $note_val=""; + $model=""; + $hersteller=""; foreach($zeile as $data) { /* check if column will be imported */ if (!in_array(trim($in_fld[$i]),$parts_fld)) { @@ -139,31 +267,28 @@ 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, "\n"); + + /* special case partsgroup1 */ + if (substr($key,0,10) == "partsgroup") { + if (strlen($key)==10) { $pgnr=0; } + else { $pgnr=substr($key,-1); } + $pg_name_val[$pgnr]=$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" || + } 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); @@ -177,18 +302,46 @@ function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$mas //show( $show, "\n"); } } else if ($key == "description") { - $data=mb_convert_encoding($data,"ISO-8859-15","auto"); + $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15"); + $data=preg_replace('/""[^ ]/','"',$data); $data=addslashes($data); } else if ($key == "notes") { - $data=mb_convert_encoding($data,"ISO-8859-15","auto"); - $data=addslashes($data); + $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15"); + $data=preg_replace('/""[^ ]/','"',$data); + $notesval=addslashes($data); + $i++; + continue; + } else if ($key == "notes1") { + $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15"); + $data=preg_replace('/""[^ ]/','"',$data); + $notesval.=" \n".addslashes($data); + $i++; + continue; + //$key="notes"; + /*} else if ($key == "makemodel") { + $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15"); + $hersteller=addslashes($data); + $i++; + continue; + } else if ($key == "model") { + $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15"); + $model=addslashes($data); + $i++; + continue;*/ } 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"; else if ($data == "Stunde") $data = "Std"; /* check if unit exists */ + //echo "!$data!"; if (!existUnit($db, $data)) { echo "Error in line $m: "; echo "Einheit $data existiert nicht "; @@ -210,17 +363,58 @@ function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$mas $i++; show( $show, "\n"); continue; - } + } + if ($data==false or empty($data) or !$data) { + show( $show, "\n"); + $i++; + continue; + } /* convert JA to Yes */ - if ($data == "J" ) - $data = "Y"; - + if ($data === "J" || $data === "j") $data = "Y"; $vals.="'".$data."',"; - show( $show, "\n"); + show( $show, "\n"); $keys.=$key.","; - $i++; } + if ($unit) { + if ($maske["ware"]=="D") { $einh=$stdunitD; } + else { $einh=$stdunitW; } + $keys.="unit,"; + $vals.="'$einh',"; + show( $show,"\n"); + } + /* special case partsgroup */ + //Mehrere Warengruppennamen werden mit einem Trennzeichen zu einen Namen zusammengefürt. + if ($pgshow) { + foreach($pg_name_val as $pg) { + if ($pg>"") $pgname.=$pg.$wgtrenner; + }; + unset($pg_name_val); + if ($pgname and $pgname <> "!") { + $pgname=substr($pgname,0,-1); + /* get ID of partsgroup or add new + * partsgroup_id */ + $ID = getPartsgroupId($db, $pgname, $insert); + $keys.= "partsgroup_id,"; + $vals.="'".$ID."',"; + show( $show, "\n"); + } else { + show( $show,"\n"); + } + } + if ($note2show) { + if ($notesval) { + $keys.="notes,"; + $vals.="'$notesval',"; + if (strlen($notesval)>255) { + show( $show, "\n"); + } else { + show( $show, "\n"); + } + } else { + show( $show,"\n"); + } + } if ($artikel==-1) { if ($maske["ware"]=="D") { $artikel=false; } else { $artikel=true; }; @@ -272,9 +466,9 @@ function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$mas show( $show, "\n"); } } - $sql.=$keys."import)"; - $sql.=$vals.time().")"; - //show( $show, "\n"); + + $sql.=$keys."shop,import)"; + $sql.="$vals'$shop',".time().")"; if ($insert) { show( $show, "\n"); } - + $pgname=""; show( $show, "\n"); }
#$fld$fldunitpartsgroupnotes
Update $partnumber:$sellprice
Fehler Zeile $m
$mNULL$partnumber$dataNULL$data".htmlentities($data)."$einh".htmlentities($pgname).":$IDNULL".substr($notesval,0,25)." . . . ".htmlentities(substr($notesval,-25))."$notesvalNULL$partnumber $sql "); @@ -286,7 +480,7 @@ function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$mas } show( $show, "