X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=lxo-import%2Fparts_import.php;h=94f2b6f15fc9db8ef8a77bf68808154b32a3f998;hb=829f6742c99fdf3b3ef78713c3ce1c1266f67d98;hp=7b4d69bee31c103c2a7e95c3439a909ced1b8be2;hpb=996ddb70dc0d35be7a72e981ab38011a13d09484;p=kivitendo-erp.git diff --git a/lxo-import/parts_import.php b/lxo-import/parts_import.php index 7b4d69bee..94f2b6f15 100644 --- a/lxo-import/parts_import.php +++ b/lxo-import/parts_import.php @@ -1,4 +1,4 @@ - //Holger Lindemann @@ -10,485 +10,541 @@ * \returns partgroup id or "" in case of an error */ function getPartsgroupId($db, $value, $add) { - - $sql="select id from partsgroup where partsgroup = '$value'"; - $rs=$db->getAll($sql); - if (empty($rs[0]["id"]) && $add) { - $sql="insert into partsgroup (partsgroup) values ('$value')"; - $rc=$db->query($sql); - if (!$rc) - return ""; - return getPartsgroupId($db, $value, 0); - } - return $rs[0]["id"]; + $sql="select id from partsgroup where partsgroup = '$value'"; + $rs=$db->getAll($sql); + if (empty($rs[0]["id"]) && $add) { + $sql="insert into partsgroup (partsgroup) values ('$value')"; + $rc=$db->query($sql); + if (!$rc) + return ""; + return getPartsgroupId($db, $value, 0); + } + return $rs[0]["id"]; +} +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["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"]); + show($data["inventory_accno"]); show($data["microfiche"]); show($data["drawing"]); + show($data["rop"]); show($data["assembly"]); show($data["makemodel"]); + show($data["shop"]); + } -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"; } + /*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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'; + //$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'); + else + show('error'); + show(''."\n",false); + } + return $rc; +} +function updPrice($db,$insert,$show,$partnumber,$lastcost,$sellprice,$listprice,$pricegroup,$shop) { + if ($show) { + show('',false); + show($partnumber); show($lastcost); show($sellprice); show($listprice); + } + if ($insert) { + $sqlPr = 'UPDATE PARTS SET '; + $sqlPr .= 'sellprice = ?, listprice = ?, lastcost = ?, shop = ? '; + $sqlPr .= 'WHERE 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'); + else + show('error'); + show(''."\n",false); + } + return $rc; +} +function updParts($db,$insert,$show,$partnumber,$lastcost,$sellprice,$listprice, + $description,$notes,$ean,$weight,$image, + $partsgroup_id,$pricegroup, $shop) { + if ($show) { + show('',false); + 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 = ?, listprice = ?, lastcost = ?, partsgroup_id = ?, shop = ? '; + $sqlUa .= 'WHERE partnumber = ?'; + $rc=$db->execute($sqlUa,array($description,$notes,$ean,$weight,$image, + $sellprice,$listprice,$lastcost,$partsgroup_id,$shop,$partnumber)); + } else { + $rc = true; + } + if ($pricegroup) $ok = insPrices($db,$partnumber,$pricegroup); + if ($show) { + if ($rc) + show('ok'); + else + show('error'); + show(''."\n",false); + } + return $rc; +} + +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) { + 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); + } } function getAccnoId($db, $accno) { - $sql = "select id from chart where accno='$accno'"; - $rs=$db->getAll($sql); - return $rs[0]["id"]; + $sql = "select id from chart where accno='$accno'"; + $rs=$db->getAll($sql); + return $rs[0]["id"]; } -function chkPartNumber($db,$number,$check) { - if ($number<>"") { - $sql = "select * from parts where partnumber = '$number'"; - $rs=$db->getAll($sql); - } - //echo $sql; print_r($rs); - if ($rs[0]["id"]>0 or $number=="") { - if ($check) return "check"; - $rc=$db->query("BEGIN"); - $sql = "select articlenumber from defaults"; - $rs=$db->getAll($sql); - 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"); - $sql = "select * from parts where partnumber = '$number'"; - $rs=$db->getAll($sql); - if ($rs[0]["id"]>0) return ""; - } - return $number; +function getPartsid($db,$number) { + $sql = "select id from parts where partnumber = '$number'"; + $rs=$db->getAll($sql); + if ($rs[0]["id"]>0) { + return $rs[0]["id"]; + } else { + return false; + } } -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 newPartNumber($db,$check) { + if ($check) return "check"; + $rc=$db->query("BEGIN"); + $sql = "select articlenumber from defaults"; + $rs=$db->getAll($sql); + 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"); + //Prüfen ob die Nummer nicht doch schon vergeben ist. + $sql = "select * from parts where partnumber = '$number'"; + $rs=$db->getAll($sql); + if ($rs[0]["id"]>0) return ""; + return $number; } function getBuchungsgruppe($db, $income, $expense) { - - $income_id = getAccnoId($db, $income); - $expense_id = getAccnoId($db, $expense); - //$accno0_id = getAccnoId($db, $accno0); - //$accno1_id = getAccnoId($db, $accno1); - //$accno3_id = getAccnoId($db, $accno3); - - $sql = "select id from buchungsgruppen where "; - $sql .= "income_accno_id_0 = $income_id and "; - $sql .= "expense_accno_id_0 = $expense_id "; - //$sql .= "income_accno_id_0 = '$accno0_id' "; - //$sql .= "and income_accno_id_1 = '$accno1_id' "; - //$sql .= "and income_accno_id_3 = '$accno3_id'"; - $rs=$db->getAll($sql); - return $rs[0]["id"]; + $income_id = getAccnoId($db, $income); + $expense_id = getAccnoId($db, $expense); + $sql = "select id from buchungsgruppen where "; + $sql .= "income_accno_id_0 = $income and "; + $sql .= "expense_accno_id_0 = $expense "; + $sql .= "order by sortkey"; + $rs=$db->getAll($sql); + return $rs[0]["id"]; } - function getFromBG($db, $bg_id, $name) { - - $sql = "select $name from buchungsgruppen where id='$bg_id'"; - $rs=$db->getAll($sql); - return $rs[0][$name]; + $sql = "select $name from buchungsgruppen where id='$bg_id'"; + $rs=$db->getAll($sql); + return 1*$rs[0][$name]; } function existUnit($db, $value) { - $sql="select name from units where name = '$value'"; - $rs=$db->getAll($sql); - if (empty($rs[0]["name"])) - return FALSE; - return TRUE; + $sql="select name from units where name = '$value'"; + $rs=$db->getAll($sql); + if (empty($rs[0]["name"])) + return FALSE; + return TRUE; } -function show($show, $things) { - if ($show) - echo $things; +function show($things,$td=true) { + if ($td) + echo ''.$things.''; + else + echo $things; } 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"]; + $sql="select * from units where type='$type' order by sortkey limit 1"; + $rs=$db->getAll($sql); + 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); - - /* open csv file */ - $f=fopen("$file.csv","r"); - - /* - * 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)) { - 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 = ""; - - /*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)) { - $i++; - continue; - }; - $data=trim($data); - $key=$in_fld[$i]; - /* add key and data */ - - /* 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; - } 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); - if ($partnumber=="") { - show( $show, "\n"); - $i++; - continue; - } else { - //$keys.="partnumber, "; - $data=$partnumber; - //show( $show, "\n"); - } - } else if ($key == "description") { - $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","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 "; - echo "Bitte legen Sie diese Einheit an
"; - $errors++; - } - } else if ($key == "art") { - if ($maske["ware"]=="G" and strtoupper($data)=="D") { $artikel=false; } - else if ($maske["ware"]=="G") { $artikel=true; }; - $i++; - continue; - } else if ($key == "income_accno") { - $income_accno = $data; - $i++; - show( $show, "\n"); - continue; - } else if ($key == "expense_accno") { - $expense_accno = $data; - $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 === "j") $data = "Y"; - $vals.="'".$data."',"; - 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; }; - } - if ($maske["bugrufix"]==1) { - $bg = $maske["bugru"]; - } else { - 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
"; - echo "Erlöse Inland: $income_accno
"; - echo "Bitte legen Sie eine an oder geben Sie eine vor.
"; - echo "
"; - $errors++; - } - } - if ($bg > 0) { - /* found one, add income_accno_id etc from buchungsgr. - */ - $keys.="buchungsgruppen_id, "; - $vals.="'$bg', "; - /* XXX nur bei artikel!!! */ - if ($artikel) { - $keys.="inventory_accno_id, "; - $vals.=getFromBG($db, $bg, "inventory_accno_id")." ,"; - }; - $keys.="income_accno_id, "; - $vals.=getFromBG($db, $bg, "income_accno_id_0")." ,"; - $keys.="expense_accno_id,"; - $vals.=getFromBG($db, $bg, "expense_accno_id_0")." ,"; - } - if ($partNr==false) { - $partnumber=chkPartNumber($db,"",$check); - if ($partnumber=="") { - show( $show, "\n"); - $errors++; - } else { - $keys.="partnumber, "; - $vals.="'$partnumber',"; - show( $show, "\n"); - } - } - - $sql.=$keys."shop,import)"; - $sql.="$vals'$shop',".time().")"; - - if ($insert) { - show( $show, "\n"); - } - $pgname=""; - show( $show, "\n"); +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; } - - show( $show, "
#$fldunitpartsgroupnotes
Update $partnumber:$sellprice
Fehler Zeile $m
$mNULL$partnumber$data$dataNULL".htmlentities($data)."$einh".htmlentities($pgname).":$IDNULL".substr($notesval,0,25)." . . . ".htmlentities(substr($notesval,-25))."$notesvalNULLNULL$partnumber"); - $db->showErr = TRUE; - $rc=$db->query($sql); - if (!$rc) { - echo "Fehler"; - $fehler++; - } - show( $show, "
\n"); - fclose($f); - echo "$m Zeilen bearbeitet. ($fehler : Fehler) "; - return $errors; + } + $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"]; + $shop=$maske["shop"]; + $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=$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 */ + $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 */ + 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("\n",false); + } + + + $p=0; + foreach ($infld as $fld) { + $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>"")))); + if (in_array($fld,$parts_fld)) { + $fldpos[$fld]=$p; + } + $p++; + } + $i=0; + $u=0; + $m=0; /* line */ + $errors=0; /* number of errors detected */ + $income_accno = ""; + $expense_accno = ""; + $assembly = 'f'; + + while ( ($zeile=fgetcsv($f,120000,$trenner)) != FALSE) { + $m++; /* increase line */ + $unit=false; + unset($pgroup); + unset($partsgroup_id); + unset($notes); + unset($rop); + unset($weight); + unset($inventory_accno); + unset($income_accno); + unset($expense_accno); + unset($model); + unset($makemodel); + unset($hersteller); + + /* 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"]]) { + //Kundenspezifisch: + //$notes = preg_replace('/""[^ ]/','"',$zeile[$fldpos["notes"]]); + $notes = addslashes($zeile[$fldpos["notes"]]); + if (Translate) translate($notes); + } + if ($zeile[$fldpos["notes1"]]) { + //Kundenspezifisch: + //$notes1 = preg_replace('/""[^ ]/','"',$zeile[$fldpos["notes1"]]); + $notes1 = addslashes($zeile[$fldpos["notes1"]]); + if (Translate) translate($notes1); + if ($notes) { + $notes .= "\n".$notes1; + } else { + $notes = $notes1; + } + } + + /* Warengruppe bilden */ + if ($fldpos["partsgroup"]>0 and $zeile[$fldpos["partsgroup"]]) $pgroup[]=$zeile[$fldpos["partsgroup"]]; + if ($fldpos["partsgroup1"]>0 and $zeile[$fldpos["partsgroup1"]]) $pgroup[]=$zeile[$fldpos["partsgroup1"]]; + if ($fldpos["partsgroup2"]>0 and $zeile[$fldpos["partsgroup2"]]) $pgroup[]=$zeile[$fldpos["partsgroup2"]]; + if ($fldpos["partsgroup3"]>0 and $zeile[$fldpos["partsgroup3"]]) $pgroup[]=$zeile[$fldpos["partsgroup3"]]; + if ($fldpos["partsgroup4"]>0 and $zeile[$fldpos["partsgroup4"]]) $pgroup[]=$zeile[$fldpos["partsgroup4"]]; + if (count($pgroup)>0) { + $pgname = implode($wgtrenner,$pgroup); + if (Translate) translate($pgname); + $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 */ + $makemodel = 'f'; + if (!empty($zeile[$fldpos["makemodel"]]) and $artikel) { + $mm = $zeile[$fldpos["makemodel"]]; + if (Translate) translate($mm); + 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 if ($modnr and $hersteller>0) { + $model = ''; + $makemodel = 't'; + } else { + unset($hersteller); + $makemodel = 'f'; + } + } + + /* Einheit ermitteln */ + if ($zeile[$fldpos["unit"]]=="") { + //Keine Einheit mitgegeben + if ($maske["ware"]=="G") { + if ($artikel) { + $unit = $stdunitD; + } else { + $unit = $stdunitW; + } + } else if ($maske["ware"]=="D") { $unit = $stdunitD; } + else { $unit = $stdunitW; }; + } else { + if (existUnit($db,$zeile[$fldpos["unit"]])) { + $unit = $zeile[$fldpos["unit"]]; + } else { + $unit = ($artikel)?$stdunitD:$stdunitW; + } + } + + /* Buchungsgruppe ermitteln */ + if ($maske["bugrufix"]==1) { + $bg = $maske["bugru"]; + } else { + if ($zeile[$fldpos["income_accno"]]<>"" and $zeile[$fldpos["expense_accno"]]<>"") { + /* search for buchungsgruppe */ + $bg = getBuchungsgruppe($db, $zeile[$fldpos["income_accno"]],$zeile[$fldpos["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
"; + echo "Erlöse Inland: ".$zeile[$fldpos["income_accno"]]."
"; + echo "Aufwand Inland: ".$zeile[$fldpos["expense_accno"]]."
"; + echo "Bitte legen Sie eine an oder geben Sie eine vor.
"; + echo "
"; + $errors++; + } + } + if ($bg > 0) { + /* found one, add income_accno_id etc from buchungsgr. */ + /* XXX nur bei artikel!!! */ + if ($artikel) { + $inventory_accno = getFromBG($db, $bg, "inventory_accno_id"); + }; + $income_accno = getFromBG($db, $bg, "income_accno_id_0"); + $expense_accno = getFromBG($db, $bg, "expense_accno_id_0"); + $bg = $bg * 1; + } else { + echo "Error in line $m: "; + echo "Keine Buchungsgruppe angegeben/gefunden
"; + $errors++; + continue; + } + + $description = preg_replace('/""[^ ]/','"',$zeile[$fldpos["description"]]); + $description = addslashes($description); + if (Translate) translate($description); + + // rop und weight müssen null oder Zahl sein + if ($zeile[$fldpos["rop"]]) $rop = 1 * str_replace(",", ".",$zeile[$fldpos["rop"]]); + if ($zeile[$fldpos["weight"]]) $weight = 1 * str_replace(",", ".", $zeile[$fldpos["weight"]]); + + // Shop-Artikel + if ($zeile[$fldpos["shop"]]) { + $shop = (strtolower($zeile[$fldpos["shop"]]=='t'))?'t':'f'; + } else { + $shop = $maske["shop"]; + } + // Artikel updaten + + if (getPartsid($db,trim($zeile[$fldpos["partnumber"]]))) { + /* es gibt die Artikelnummer */ + if ($Update) { + /* Updates durchführen */ + if ($UpdText=='1') { + $u += updParts($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,$listprice, + $description,$notes,$zeile[$fldpos["ean"]],$weight, + $zeile[$fldpos["image"]],$partsgroup_id,$pricegroup, $shop); + } else { + $u += updPrice($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,$listprice,$pricegroup,$shop); + } + continue; + // nächste Zeile + } + } + + // Neuen Artikel einfügen + + if ($zeile[$fldpos["partnumber"]] == "") { + $zeile[$fldpos["partnumber"]] = newPartNumber($db,$check); + //Keine Artikelnummer bekommen + if ($zeile[$fldpos["partnumber"]] == "") { + continue; + } + } + $i += insertParts($db,$insert,$show,array( + "partnumber"=>$zeile[$fldpos["partnumber"]], + "description"=>$description,"notes"=>$notes, + "ean"=>$zeile[$fldpos["ean"]],"unit"=>$unit, + "weight"=>$weight,"image"=>$zeile[$fldpos["image"]], + "sellprice"=>$sellprice, + "lastcost"=>$lastcost, + "listprice"=>$listprice, + "partsgroup_id"=>$partsgroup_id, + "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) { + $partsid=getPartsid($db,$zeile[$fldpos["partnumber"]]); + if ($partsid) { + getMakemodel($db,$check,$hersteller,$model,$partsid,$lastcost,true); + } + } + unset($zeile); + } + + if ($show) show("
",false); + fclose($f); + echo "$m Zeilen bearbeitet. Importiert: $i Update: $u (".($m-$u-$i+$errors)." : Fehler) "; +} +?>