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) {
14 $sql="select id from partsgroup where partsgroup = '$value'";
15 $rs=$db->getAll($sql);
16 if (empty($rs[0]["id"]) && $add) {
17 $sql="insert into partsgroup (partsgroup) values ('$value')";
21 return getPartsgroupId($db, $value, 0);
26 function getMakemodel($db,$hersteller,$model,$partsid,$add=true) {
27 $sql="select * from makemodel where make like '$hersteller' and model like = '$model'";
28 $rs=$db->getAll($sql);
29 if (empty($rs[0]["id"]) && $add) {
30 $sql="insert into makemodel (parts_id,make,model) values ($partsid,'$hersteller','$model')";
33 return getMakemodel($db,$hersteller,$model,$partsid,false);
35 if ($rs[0]["parts_id"]==$partsid) { return "t"; }
39 function getAccnoId($db, $accno) {
40 $sql = "select id from chart where accno='$accno'";
41 $rs=$db->getAll($sql);
45 function chkPartNumber($db,$number,$check) {
47 $sql = "select * from parts where partnumber = '$number'";
48 $rs=$db->getAll($sql);
50 //echo $sql; print_r($rs);
51 if ($rs[0]["id"]>0 or $number=="") {
52 if ($check) return "check";
53 $rc=$db->query("BEGIN");
54 $sql = "select articlenumber from defaults";
55 $rs=$db->getAll($sql);
56 if ($rs[0]["articlenumber"]) {
57 preg_match("/([^0-9]+)?([0-9]+)([^0-9]+)?/", $rs[0]["articlenumber"] , $regs);
58 $number=$regs[1].($regs[2]+1).$regs[3];
60 $sql = "update defaults set articlenumber = '$number'";
62 $rc=$db->query("COMMIT");
63 $sql = "select * from parts where partnumber = '$number'";
64 $rs=$db->getAll($sql);
65 if ($rs[0]["id"]>0) return "";
69 function chkPartNumberUpd($db,$sellprice,$partnumber,$descript,$note,$check,$shop='n'){
70 if ($partnumber=="") {
71 $nummer=chkPartNumber($db,$partnumber,$check);
72 if ($nummer=="") { return -99; }
73 else { return $nummer; };
75 $sql = "select * from parts where partnumber = '$partnumber'";
76 $rs=$db->getAll($sql);
78 $sql="update parts set sellprice = $sellprice, shop='$shop'";
79 if ($descript) $sql.=",description='$descript',notes='$note'";
80 $sql.=" where partnumber = '$partnumber'";
85 $nummer=chkPartNumber($db,$partnumber,$check);
86 if ($nummer=="") { return -99; }
87 else { return $nummer; };
90 function getBuchungsgruppe($db, $income, $expense) {
92 $income_id = getAccnoId($db, $income);
93 $expense_id = getAccnoId($db, $expense);
94 //$accno0_id = getAccnoId($db, $accno0);
95 //$accno1_id = getAccnoId($db, $accno1);
96 //$accno3_id = getAccnoId($db, $accno3);
98 $sql = "select id from buchungsgruppen where ";
99 $sql .= "income_accno_id_0 = $income_id and ";
100 $sql .= "expense_accno_id_0 = $expense_id ";
101 //$sql .= "income_accno_id_0 = '$accno0_id' ";
102 //$sql .= "and income_accno_id_1 = '$accno1_id' ";
103 //$sql .= "and income_accno_id_3 = '$accno3_id'";
104 $rs=$db->getAll($sql);
109 function getFromBG($db, $bg_id, $name) {
111 $sql = "select $name from buchungsgruppen where id='$bg_id'";
112 $rs=$db->getAll($sql);
113 return $rs[0][$name];
116 function existUnit($db, $value) {
117 $sql="select name from units where name = '$value'";
118 $rs=$db->getAll($sql);
119 if (empty($rs[0]["name"]))
124 function show($show, $things) {
129 function getStdUnit($db,$type) {
130 $sql="select * from units where type='$type' order by sortkey limit 1";
131 $rs=$db->getAll($sql);
132 if (empty($rs[0]["name"])) return "Stck";
133 return $rs[0]["name"];
137 function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $insert, $show,$maske) {
142 $precision=$maske["precision"];
143 $quotation=$maske["quotation"];
144 $quottype=$maske["quottype"];
145 $shop=$maske["shop"];
146 $wgtrenner=$maske["wgtrenner"];
147 $UpdText=($maske["TextUpd"]=="1")?true:false;
149 $Update=($maske["update"]=="U")?true:false;
150 /* field description */
151 $parts_fld = array_keys($fields);
154 $f=fopen("$file.csv","r");
157 * read first line with table descriptions
159 show( $show, "<table border='1'><tr><td>#</td>\n");
160 if ($trenner=="other") $trenner=trim($trennzeichen);
161 if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1));
162 $infld=fgetcsv($f,1200,$trenner);
163 foreach ($infld as $fld) {
164 $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>""))));
166 if (in_array(trim($fld),$parts_fld)) {
167 if (substr($fld,0,10)=="partsgroup") {
169 } else if ($fld=="notes" || $fld=="notes1" ) {
172 show( $show, "<td>$fld</td>\n");
176 if (!in_array("unit",$infld)) {
177 $stdunitW=getStdUnit($db,"dimension");
178 $stdunitD=getStdUnit($db,"service");
180 show( $show, "<td>unit</td>\n");
182 if ($pgshow) show( $show, "<td>partsgroup</td>\n");
183 if ($note2show) show( $show, "<td>notes</td>\n");
190 foreach ($infld as $value) {
191 if ($infld[$j]=="sellprice") $posprice=$j;
192 if ($infld[$j]=="partnumber") $posnumber=$j;
193 if ($infld[$j]=="description") $posdescript=$j;
194 if ($infld[$j]=="notes") $posnotes=$j;
195 if ($infld[$j]=="notes1") $posnotes1=$j;
199 $errors=0; /* number of errors detected */
204 $sql="update parts set shop = 'n' where partnumber like '______'";
205 $rc=$db->query($sql);
206 //echo $sql; print_r($rc); echo "<br>";
208 echo "Fehler: Artikel nicht aus dem Shop genommen";
211 if ($quottype=="P") $quotation=($quotation+100)/100;
212 while ( ($zeile=fgetcsv($f,120000,$trenner)) != FALSE) {
214 $m++; /* increase line */
217 $sellprice=$zeile[$posprice];
218 $partnumber=$zeile[$posnumber];
219 $sellprice = str_replace(",", ".", $sellprice);
221 if ($quottype=="A") { $sellprice += $quotation; }
222 else { $sellprice = $sellprice * $quotation; }
224 if ($precision>=0) $sellprice = round($sellprice,$precision);
226 $description=$zeile[$posdescript];
227 $note=$zeile[$posnotes];
228 $note1=$zeile[$posnotes1];
229 $note=mb_convert_encoding($note,"ISO-8859-15","ISO-8859-15");
230 $note=preg_replace('/""[^ ]/','"',$note);
231 $note=" \n".addslashes($note);
232 $note1=mb_convert_encoding($note1,"ISO-8859-15","ISO-8859-15");
233 $note1=preg_replace('/""[^ ]/','"',$note1);
234 $note.=" \n".addslashes($note1);
235 $rc=chkPartNumberUpd($db,$sellprice,$partnumber,$description,$note,$check);
237 $rc=chkPartNumberUpd($db,$sellprice,$partnumber,false,false,$check);
240 show($show,"<tr><td>Update </td><td>$partnumber:$sellprice</td></tr>\n");
242 } else if ($rc==-99) {
243 show($show,"<tr><td>Fehler Zeile $m</td></tr>\n");
246 $zeile[$posnumber]=$rc;
250 $sql="insert into $file ";
253 show( $show, "<tr><td>$m</td>\n");
255 /* for each column */
256 $dienstleistung=false;
259 $pg_name_val=array();
263 foreach($zeile as $data) {
264 /* check if column will be imported */
265 if (!in_array(trim($in_fld[$i]),$parts_fld)) {
271 /* add key and data */
273 /* special case partsgroup1 */
274 if (substr($key,0,10) == "partsgroup") {
275 if (strlen($key)==10) { $pgnr=0; }
276 else { $pgnr=substr($key,-1); }
277 $pg_name_val[$pgnr]=$data;
280 } else if ($key == "lastcost" ||
281 $key == "sellprice") {
283 /* convert 0,0 numeric into 0.0 */
284 $data = str_replace(",", ".", $data);
285 if ($key == "sellprice") {
287 if ($quottype=="A") { $data += $quotation; }
288 else { $data = $data * $quotation; }
290 if ($precision>=0) $data = round($data,$precision);
292 } else if ($key == "partnumber") {
294 $partnumber=chkPartNumber($db,$data,$check);
295 if ($partnumber=="") {
296 show( $show, "<td>NULL</td>\n");
300 //$keys.="partnumber, ";
302 //show( $show, "<td>$partnumber</td>\n");
304 } else if ($key == "description") {
305 $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
306 $data=preg_replace('/""[^ ]/','"',$data);
307 $data=addslashes($data);
308 } else if ($key == "notes") {
309 $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
310 $data=preg_replace('/""[^ ]/','"',$data);
311 $notesval=addslashes($data);
314 } else if ($key == "notes1") {
315 $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
316 $data=preg_replace('/""[^ ]/','"',$data);
317 $notesval.=" \n".addslashes($data);
321 /*} else if ($key == "makemodel") {
322 $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
323 $hersteller=addslashes($data);
326 } else if ($key == "model") {
327 $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
328 $model=addslashes($data);
331 } else if ($key == "unit") {
333 if ($maske["ware"]=="W") { $data=$stdunitW; }
334 else if ($maske["ware"]=="D") { $data=$stdunitD; }
335 //else if ($maske["ware"]=="G") { $data=$stdunitD; //Noch machen!}
336 else { $data=$stdunitW; };
338 /* convert stück and Stunde */
339 if (preg_match("/^st..?ck$/i", $data))
341 else if ($data == "Stunde")
343 /* check if unit exists */
345 if (!existUnit($db, $data)) {
346 echo "Error in line $m: ";
347 echo "Einheit <b>$data</b> existiert nicht ";
348 echo "Bitte legen Sie diese Einheit an<br>";
351 } else if ($key == "art") {
352 if ($maske["ware"]=="G" and strtoupper($data)=="D") { $artikel=false; }
353 else if ($maske["ware"]=="G") { $artikel=true; };
356 } else if ($key == "income_accno") {
357 $income_accno = $data;
359 show( $show, "<td>$data</td>\n");
361 } else if ($key == "expense_accno") {
362 $expense_accno = $data;
364 show( $show, "<td>$data</td>\n");
367 if ($data==false or empty($data) or !$data) {
368 show( $show, "<td>NULL</td>\n");
372 /* convert JA to Yes */
373 if ($data === "J" || $data === "j") $data = "Y";
374 $vals.="'".$data."',";
375 show( $show, "<td>".htmlentities($data)."</td>\n");
380 if ($maske["ware"]=="D") { $einh=$stdunitD; }
381 else { $einh=$stdunitW; }
384 show( $show,"<td>$einh</td>\n");
386 /* special case partsgroup */
387 //Mehrere Warengruppennamen werden mit einem Trennzeichen zu einen Namen zusammengefürt.
389 foreach($pg_name_val as $pg) {
390 if ($pg>"") $pgname.=$pg.$wgtrenner;
393 if ($pgname and $pgname <> "!") {
394 $pgname=substr($pgname,0,-1);
395 /* get ID of partsgroup or add new
397 $ID = getPartsgroupId($db, $pgname, $insert);
398 $keys.= "partsgroup_id,";
400 show( $show, "<td>".htmlentities($pgname).":$ID</td>\n");
402 show( $show,"<td>NULL</td>\n");
408 $vals.="'$notesval',";
409 if (strlen($notesval)>255) {
410 show( $show, "<td>".substr($notesval,0,25)." . . . ".htmlentities(substr($notesval,-25))."</td>\n");
412 show( $show, "<td>$notesval</td>\n");
415 show( $show,"<td>NULL</td>\n");
419 if ($maske["ware"]=="D") { $artikel=false; }
420 else { $artikel=true; };
422 if ($maske["bugrufix"]==1) {
423 $bg = $maske["bugru"];
425 if ($income_accno<>"" and $expense_accno<>"") {
426 /* search for buchungsgruppe */
427 $bg = getBuchungsgruppe($db, $income_accno, $expense_accno);
428 if ($bg == "" and $maske["bugrufix"]==2 and $maske["bugru"]<>"") {
429 $bg = $maske["bugru"];
431 } else if ($maske["bugru"]<>"" and $maske["bugrufix"]==2) {
432 $bg = $maske["bugru"];
434 /* nothing found? user must create one */
435 echo "Error in line $m: ";
436 echo "Keine Buchungsgruppe gefunden für <br>";
437 echo "Erlöse Inland: $income_accno<br>";
438 echo "Bitte legen Sie eine an oder geben Sie eine vor.<br>";
444 /* found one, add income_accno_id etc from buchungsgr.
446 $keys.="buchungsgruppen_id, ";
448 /* XXX nur bei artikel!!! */
450 $keys.="inventory_accno_id, ";
451 $vals.=getFromBG($db, $bg, "inventory_accno_id")." ,";
453 $keys.="income_accno_id, ";
454 $vals.=getFromBG($db, $bg, "income_accno_id_0")." ,";
455 $keys.="expense_accno_id,";
456 $vals.=getFromBG($db, $bg, "expense_accno_id_0")." ,";
458 if ($partNr==false) {
459 $partnumber=chkPartNumber($db,"",$check);
460 if ($partnumber=="") {
461 show( $show, "<td>NULL</td>\n");
464 $keys.="partnumber, ";
465 $vals.="'$partnumber',";
466 show( $show, "<td>$partnumber</td>\n");
470 $sql.=$keys."shop,import)";
471 $sql.="$vals'$shop',".time().")";
474 show( $show, "<td>");
476 $rc=$db->query($sql);
481 show( $show, "</td>\n");
484 show( $show, "</tr>\n");
487 show( $show, "</table>\n");
489 echo "$m Zeilen bearbeitet. ($fehler : Fehler) ";