//Holger Lindemann /** * Returns ID of a partgroup (or adds a new partgroup entry) * \db is the database * \value is the partgroup name * \add if true and partgroup does not exist yet, we will add it automatically * \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"]; } 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"]; } 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 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) { $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"]; } function getFromBG($db, $bg_id, $name) { $sql = "select $name from buchungsgruppen where id='$bg_id'"; $rs=$db->getAll($sql); return $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; } function show($show, $things) { if ($show) 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"]; } 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"); } 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; } ?>