2 //Henry Margies <h.margies@maxina.de>
3 //Holger Lindemann <hli@lx-system.de>
6 * Returns ID of a partgroup (or adds a new partgroup entry)
8 * \value is the partgroup name
9 * \add if true and partgroup does not exist yet, we will add it automatically
10 * \returns partgroup id or "" in case of an error
12 function getPartsgroupId($db, $value, $add) {
13 $sql="select id from partsgroup where partsgroup = '$value'";
14 $rs=$db->getAll($sql);
15 if (empty($rs[0]["id"]) && $add) {
16 $sql="insert into partsgroup (partsgroup) values ('$value')";
20 return getPartsgroupId($db, $value, 0);
24 function getPricegroup($db) {
25 $sql="SELECT * from pricegroup";
26 $rs=$db->getAll($sql);
28 if ($rs) foreach ($rs as $row) {
29 $data["pg_".strtolower($row["pricegroup"])]=$row["id"];
33 function insertParts($db,$insert,$show,$data,$pricegroup) {
36 show($data["partnumber"]); show($data["lastcost"]); show($data["sellprice"]); show($data["listprice"]);
37 show($data["description"]); show(substr($data["notes"],0,25));show($data["ean"]);
38 show($data["weight"]); show($data["image"]); show($data["partsgroup_id"]);
39 show($data["buchungsgruppen_id"]);show($data["income_accno"]); show($data["expense_accno"]);
40 show($data["inventory_accno"]); show($data["microfiche"]); show($data["drawing"]);
41 show($data["rop"]); show($data["assembly"]); show($data["makemodel"]);
45 /*foreach ($data as $key=>$val) {
46 echo $key.":".gettype($val).":".gettype($data[$key]).":".$val."<br>";
49 $data["import"]=time();
50 $sqlIa = 'INSERT INTO parts (';
51 $sqlIa .= 'partnumber,description,notes,ean,unit,';
52 $sqlIa .= 'weight,image,sellprice,listprice,lastcost,partsgroup_id,';
53 $sqlIa .= 'buchungsgruppen_id,income_accno_id,expense_accno_id,inventory_accno_id,';
54 $sqlIa .= 'microfiche,drawing,rop,assembly,shop,makemodel,import) ';
55 //$sqlIa .= 'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
56 //$rc=$db->execute($sqlIa,$data);
57 $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)";
58 $sql = sprintf($sqlIa,$data['partnumber'],$data['description'],$data['notes'],$data['ean'],
59 $data['unit'],$data['weight'],$data['image'],$data['sellprice'],
60 $data['listprice'],$data['lastcost'],$data['partsgroup_id'],
61 $data['buchungsgruppen_id'],$data['income_accno_id'],$data['expense_accno_id'],
62 $data['inventory_accno_id'],$data['microfiche'],$data['drawing'],$data['rop'],
63 $data['assembly'],$data['shop'],$data['makemodel'],$data['import']);
64 $rc = $db->query($sql);
68 if ($pricegroup) $ok = insPrices($db,$data["partnumber"],$pricegroup);
73 show('<font color="red">error</font>');
74 show('</tr>'."\n",false);
78 function updPrice($db,$insert,$show,$partnumber,$lastcost,$sellprice,$listprice,$pricegroup,$shop) {
81 show($partnumber); show($lastcost); show($sellprice); show($listprice);
84 $sqlPr = 'UPDATE PARTS SET ';
85 $sqlPr .= 'sellprice = ?, listprice = ?, lastcost = ?, shop = ? ';
86 $sqlPr .= 'WHERE partnumber = ?';
87 $rc=$db->execute($sqlPr,array("sellprice"=>$sellprice,"listprice"=>$listprice,"lastcost"=>$lastcost,"shop"=>$shop,"partnumber"=>$partnumber));
91 if ($pricegroup) $ok = insPrices($db,$partnumber,$pricegroup);
96 show('<font color="red">error</font>');
97 show('</tr>'."\n",false);
101 function updParts($db,$insert,$show,$partnumber,$lastcost,$sellprice,$listprice,
102 $description,$notes,$ean,$weight,$image,
103 $partsgroup_id,$pricegroup, $shop) {
106 show($partnumber); show($lastcost); show($sellprice); show($listprice);
107 show($description); show(substr($notes,0,25));show($ean);
108 show($weight); show($image); show($partsgroup_id);
111 $sqlUa = 'UPDATE PARTS SET ';
112 $sqlUa .= 'description = ?, notes = ?, ean = ?, weight = ?, image = ?, ';
113 $sqlUa .= 'sellprice = ?, listprice = ?, lastcost = ?, partsgroup_id = ?, shop = ? ';
114 $sqlUa .= 'WHERE partnumber = ?';
115 $rc=$db->execute($sqlUa,array($description,$notes,$ean,$weight,$image,
116 $sellprice,$listprice,$lastcost,$partsgroup_id,$shop,$partnumber));
120 if ($pricegroup) $ok = insPrices($db,$partnumber,$pricegroup);
125 show('<font color="red">error</font>');
126 show('</tr>'."\n",false);
131 function getMakemodel($db,$check,$hersteller,$model,$partsid,$add=true) {
132 $sql="select * from makemodel where make = $hersteller and model = '$model' and parts_id = $partsid";
133 $rs=$db->getAll($sql);
134 if (empty($rs[0]["id"]) && $add) {
135 $sql="insert into makemodel (parts_id,make,model) values ($partsid,'$hersteller','$model')";
136 $rc=$db->query($sql);
140 function getAccnoId($db, $accno) {
141 $sql = "select id from chart where accno='$accno'";
142 $rs=$db->getAll($sql);
146 function getPartsid($db,$number) {
147 $sql = "select id from parts where partnumber = '$number'";
148 $rs=$db->getAll($sql);
149 if ($rs[0]["id"]>0) {
156 function newPartNumber($db,$check) {
157 if ($check) return "check";
158 $rc=$db->query("BEGIN");
159 $sql = "select articlenumber from defaults";
160 $rs=$db->getAll($sql);
161 if ($rs[0]["articlenumber"]) {
162 preg_match("/([^0-9]+)?([0-9]+)([^0-9]+)?/", $rs[0]["articlenumber"] , $regs);
163 $number=$regs[1].($regs[2]+1).$regs[3];
165 $sql = "update defaults set articlenumber = '$number'";
166 $rc=$db->query($sql);
167 $rc=$db->query("COMMIT");
168 //Prüfen ob die Nummer nicht doch schon vergeben ist.
169 $sql = "select * from parts where partnumber = '$number'";
170 $rs=$db->getAll($sql);
171 if ($rs[0]["id"]>0) return "";
175 function getBuchungsgruppe($db, $income, $expense) {
176 $income_id = getAccnoId($db, $income);
177 $expense_id = getAccnoId($db, $expense);
178 $sql = "select id from buchungsgruppen where ";
179 $sql .= "income_accno_id_0 = $income and ";
180 $sql .= "expense_accno_id_0 = $expense ";
181 $sql .= "order by sortkey";
182 $rs=$db->getAll($sql);
186 function getFromBG($db, $bg_id, $name) {
187 $sql = "select $name from buchungsgruppen where id='$bg_id'";
188 $rs=$db->getAll($sql);
189 return 1*$rs[0][$name];
192 function existUnit($db, $value) {
193 $sql="select name from units where name = '$value'";
194 $rs=$db->getAll($sql);
195 if (empty($rs[0]["name"]))
200 function show($things,$td=true) {
202 echo '<td>'.$things.'</td>';
207 function getStdUnit($db,$type) {
208 $sql="select * from units where type='$type' order by sortkey limit 1";
209 $rs=$db->getAll($sql);
210 return $rs[0]["name"];
213 function insPrices($db,$pid,$prices) {
214 $rc = $db->query("BEGIN");
215 $sql="delete from prices where parts_id = (select id from parts where partnumber = '$pid')";
216 $rc = $db->query($sql);
217 $sql = "insert into prices (parts_id,pricegroup_id,price) values ((select id from parts where partnumber = '%s'),%d,%0.5f)";
218 foreach ($prices as $key => $val) {
219 $rc = $db->query(sprintf($sql,$pid,$key,$val));
221 $db->query("ROLLBACK");
225 $db->query("COMMIT");
230 * TODO: short description.
234 * @param mixed $fields
235 * @param mixed $check
237 * @param string $show
238 * @param mixed $maske
242 function import_parts($db, $file, $fields, $check, $maske) {
243 $insert = !$maske["test"];
244 $show = $maske["show"];
245 $trennzeichen = ($maske["trennzeichen"])?$maske["trennzeichen"]:"";
246 $trenner = ($maske["trenner"])?$maske["trenner"]:",";
247 $precision=$maske["precision"];
248 $quotation=$maske["quotation"];
249 $quottype=$maske["quottype"];
250 $shop=$maske["shop"];
251 $wgtrenner=$maske["wgtrenner"];
252 $Update=($maske["update"]=="U")?true:false;
253 $UpdText=($maske["TextUpd"]=="1")?true:false;
255 //$stdunitW=getStdUnit($db,"dimension");
256 //$stdunitD=getStdUnit($db,"service");
257 $stdunitW=$maske["dimensionunit"];
258 $stdunitD=$maske["serviceunit"];
259 if ($quottype=="P") $quotation=($quotation+100)/100;
261 if ($show && !$insert) show("<b>Testimport</b>",false);
262 if ($show) show("<table border='1'>\n",false);
264 /* field description */
265 $prices = getPricegroup($db);
267 $priceskey = array_keys($prices);
268 $parts_fld = array_merge(array_keys($fields),$priceskey);
270 $parts_fld = array_keys($fields);
273 if ($trenner=="other") $trenner=trim($trennzeichen);
274 if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1));
277 if (file_exists($file."head.csv")) {
278 $fh=fopen($file.'head.csv',"r");
279 // Erst einmal die erste Zeile mit den richtigen Feldbezeichnungen einlesen.
280 $infld=fgetcsv($fh,1200,$trenner);
282 $f=fopen($file.'.csv',"r");
283 // Erst einmal die erste Zeile mit den falschen Feldbezeichnungen einlesen.
284 $tmp=fgetcsv($f,1200,$trenner);
286 $f=fopen($file.'.csv',"r");
287 // Erst einmal die erste Zeile mit den Feldbezeichnungen einlesen.
288 $infld=fgetcsv($f,1200,$trenner);
292 * read first line with table descriptions
296 show("partnumber"); show("lastcost"); show("sellprice"); show("listprice");
297 show("description");show("notes"); show("ean");
298 show("weight"); show("image"); show("partsgroup_id");
299 show("bg"); show("income_accno"); show("expense_accno");
300 show("inventory_accno"); show("microfiche");show("drawing");show("rop");
301 show("assembly");show("makemodel"); show("shop"); show("");
302 show("</tr>\n",false);
307 foreach ($infld as $fld) {
308 $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>""))));
309 if (in_array($fld,$parts_fld)) {
317 $errors=0; /* number of errors detected */
322 while ( ($zeile=fgetcsv($f,120000,$trenner)) != FALSE) {
323 $m++; /* increase line */
326 unset($partsgroup_id);
330 unset($inventory_accno);
331 unset($income_accno);
332 unset($expense_accno);
337 /* VK-Preis bilden */
338 $sellprice = str_replace(",", ".", $zeile[$fldpos["sellprice"]]);
339 $listprice = str_replace(",", ".", $zeile[$fldpos["listprice"]]);
340 $lastcost = str_replace(",", ".", $zeile[$fldpos["lastcost"]]);
342 foreach ($prices as $pkey=>$val) {
343 if (array_key_exists($pkey,$fldpos))
344 $pricegroup[$val] = str_replace(",", ".", $zeile[$fldpos[$pkey]]);
348 if ($quottype=="A") { $sellprice += $quotation; }
349 else { $sellprice = $sellprice * $quotation; }
351 if ($lastcost=="") unset($lastcost);
352 if ($sellprice=="") unset($sellprice);
353 if ($listprice=="") unset($listprice);
355 /* Langtext zusammenbauen */
356 if ($zeile[$fldpos["notes"]]) {
358 //$notes = preg_replace('/""[^ ]/','"',$zeile[$fldpos["notes"]]);
359 $notes = addslashes($zeile[$fldpos["notes"]]);
360 if (Translate) translate($notes);
362 if ($zeile[$fldpos["notes1"]]) {
364 //$notes1 = preg_replace('/""[^ ]/','"',$zeile[$fldpos["notes1"]]);
365 $notes1 = addslashes($zeile[$fldpos["notes1"]]);
366 if (Translate) translate($notes1);
368 $notes .= "\n".$notes1;
374 /* Warengruppe bilden */
375 if ($fldpos["partsgroup"]>0 and $zeile[$fldpos["partsgroup"]]) $pgroup[]=$zeile[$fldpos["partsgroup"]];
376 if ($fldpos["partsgroup1"]>0 and $zeile[$fldpos["partsgroup1"]]) $pgroup[]=$zeile[$fldpos["partsgroup1"]];
377 if ($fldpos["partsgroup2"]>0 and $zeile[$fldpos["partsgroup2"]]) $pgroup[]=$zeile[$fldpos["partsgroup2"]];
378 if ($fldpos["partsgroup3"]>0 and $zeile[$fldpos["partsgroup3"]]) $pgroup[]=$zeile[$fldpos["partsgroup3"]];
379 if ($fldpos["partsgroup4"]>0 and $zeile[$fldpos["partsgroup4"]]) $pgroup[]=$zeile[$fldpos["partsgroup4"]];
380 if (count($pgroup)>0) {
381 $pgname = implode($wgtrenner,$pgroup);
382 if (Translate) translate($pgname);
383 $partsgroup_id = getPartsgroupId($db, $pgname, $insert);
386 /* sind Hersteller und Modelnummer hinterlegt
387 wenn ja, erfolgt er insert später */
389 if (!empty($zeile[$fldpos["makemodel"]]) and !$artikel) {
390 $mm = $zeile[$fldpos["makemodel"]];
391 if (Translate) translate($mm);
392 $hersteller=suchFirma("vendor",$mm);
393 $hersteller=$hersteller["cp_cv_id"];
394 if (!empty($zeile[$fldpos["model"]])) {
395 $mo = $zeile[$fldpos["model"]];
396 if (Translate) translate($mo);
405 /* Ware oder Dienstleistung */
406 if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="D") or $maske["ware"]=="D") {
408 } else if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="W") or $maske["ware"]=="W") {
412 /* Einheit ermitteln */
413 if ($zeile[$fldpos["unit"]]=="") {
414 //Keine Einheit mitgegeben
415 if ($maske["ware"]=="G") {
421 } else if ($maske["ware"]=="D") { $unit = $stdunitD; }
422 else { $unit = $stdunitW; };
424 if (existUnit($db,$zeile[$fldpos["unit"]])) {
425 $unit = $zeile[$fldpos["unit"]];
427 $unit = ($artikel)?$stdunitD:$stdunitW;
431 /* Buchungsgruppe ermitteln */
432 if ($maske["bugrufix"]==1) {
433 $bg = $maske["bugru"];
435 if ($zeile[$fldpos["income_accno"]]<>"" and $zeile[$fldpos["expense_accno"]]<>"") {
436 /* search for buchungsgruppe */
437 $bg = getBuchungsgruppe($db, $zeile[$fldpos["income_accno"]],$zeile[$fldpos["expense_accno"]]);
438 if ($bg == "" and $maske["bugrufix"]==2 and $maske["bugru"]<>"") {
439 $bg = $maske["bugru"];
441 } else if ($maske["bugru"]<>"" and $maske["bugrufix"]==2) {
442 $bg = $maske["bugru"];
444 /* nothing found? user must create one */
445 echo "Error in line $m: ";
446 echo "Keine Buchungsgruppe gefunden für <br>";
447 echo "Erlöse Inland: ".$zeile[$fldpos["income_accno"]]."<br>";
448 echo "Aufwand Inland: ".$zeile[$fldpos["expense_accno"]]."<br>";
449 echo "Bitte legen Sie eine an oder geben Sie eine vor.<br>";
455 /* found one, add income_accno_id etc from buchungsgr. */
456 /* XXX nur bei artikel!!! */
458 $inventory_accno = getFromBG($db, $bg, "inventory_accno_id");
460 $income_accno = getFromBG($db, $bg, "income_accno_id_0");
461 $expense_accno = getFromBG($db, $bg, "expense_accno_id_0");
464 echo "Error in line $m: ";
465 echo "Keine Buchungsgruppe angegeben/gefunden<br>";
470 $description = preg_replace('/""[^ ]/','"',$zeile[$fldpos["description"]]);
471 $description = addslashes($description);
472 if (Translate) translate($description);
474 // rop und weight müssen null oder Zahl sein
475 if ($zeile[$fldpos["rop"]]) $rop = 1 * str_replace(",", ".",$zeile[$fldpos["rop"]]);
476 if ($zeile[$fldpos["weight"]]) $weight = 1 * str_replace(",", ".", $zeile[$fldpos["weight"]]);
479 if ($zeile[$fldpos["shop"]]) {
480 $shop = (strtolower($zeile[$fldpos["shop"]]=='t'))?'t':'f';
482 $shop = $maske["shop"];
486 if (getPartsid($db,trim($zeile[$fldpos["partnumber"]]))) {
487 /* es gibt die Artikelnummer */
489 /* Updates durchführen */
491 $u += updParts($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,$listprice,
492 $description,$notes,$zeile[$fldpos["ean"]],$weight,
493 $zeile[$fldpos["image"]],$partsgroup_id,$pricegroup, $shop);
495 $u += updPrice($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,$listprice,$pricegroup,$shop);
502 // Neuen Artikel einfügen
504 if ($zeile[$fldpos["partnumber"]] == "") {
505 $zeile[$fldpos["partnumber"]] = newPartNumber($db,$check);
506 //Keine Artikelnummer bekommen
507 if ($zeile[$fldpos["partnumber"]] == "") {
511 $i += insertParts($db,$insert,$show,array(
512 "partnumber"=>$zeile[$fldpos["partnumber"]],
513 "description"=>$description,"notes"=>$notes,
514 "ean"=>$zeile[$fldpos["ean"]],"unit"=>$unit,
515 "weight"=>$weight,"image"=>$zeile[$fldpos["image"]],
516 "sellprice"=>$sellprice,
517 "lastcost"=>$lastcost,
518 "listprice"=>$listprice,
519 "partsgroup_id"=>$partsgroup_id,
520 "buchungsgruppen_id"=>$bg,"income_accno_id"=>$income_accno,
521 "expense_accno_id"=>$expense_accno,"inventory_accno_id"=>$inventory_accno,
522 "microfiche"=>$zeile[$fldpos["microfiche"]],"drawing"=>$zeile[$fldpos["drawing"]],
523 "rop"=>$rop,"assembly"=>$assembly,
524 "shop"=>$shop,"makemodel"=>$makemodel),$pricegroup
526 if ($hersteller>0 && $model) {
527 $partsid=getPartsid($db,$zeile[$fldpos["partnumber"]]);
529 getMakemodel($db,$check,$hersteller,$model,$partsid,true);
535 if ($show) show("</table>",false);
537 echo "$m Zeilen bearbeitet. Importiert: $i Update: $u (".($m-$u-$i+$errors)." : Fehler) ";