Kosmetik
[kivitendo-erp.git] / lxo-import / parts_import.php
1 <?
2 //Henry Margies <h.margies@maxina.de>
3 //Holger Lindemann <hli@lx-system.de>
4
5 /**
6  * Returns ID of a partgroup (or adds a new partgroup entry)
7  * \db is the database
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
11  */
12 function getPartsgroupId($db, $value, $add) {
13         
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')";
18                 $rc=$db->query($sql);
19                 if (!$rc)
20                         return "";
21                 return getPartsgroupId($db, $value, 0);
22         }
23         return $rs[0]["id"];
24 }
25
26 //Muß noch eingebunden werden
27 function getMakemodel($db,$hersteller,$model,$partsid,$add=true) {
28         $sql="select * from makemodel where make like '$hersteller' and model like = '$model'";
29         $rs=$db->getAll($sql);
30         if (empty($rs[0]["id"]) && $add) {
31                 $sql="insert into makemodel (parts_id,make,model) values ($partsid,'$hersteller','$model')";    
32                 $rc=$db->query($sql);
33                 if (!$rc) return "f";
34                 return getMakemodel($db,$hersteller,$model,$partsid,false);
35         }
36         if ($rs[0]["parts_id"]==$partsid) { return "t"; }
37         else { return "f"; }
38 }
39
40 function getAccnoId($db, $accno) {
41         $sql = "select id from chart where accno='$accno'";
42         $rs=$db->getAll($sql);
43         return $rs[0]["id"];
44 }
45
46 //Auf Artikelnummer testen, bzw. neue Nummer erzeugen
47 function chkPartNumber($db,$number,$check) {
48         if ($number<>"") {
49                 $sql = "select * from parts where partnumber = '$number'";
50                 $rs=$db->getAll($sql);
51         }
52         //echo $sql; print_r($rs);
53         if ($rs[0]["id"]>0 or $number=="") {
54                 if ($check) return "check";
55                 $rc=$db->query("BEGIN");
56                 $sql = "select  articlenumber from defaults";
57                 $rs=$db->getAll($sql);
58                 if ($rs[0]["articlenumber"]) {
59                         preg_match("/([^0-9]+)?([0-9]+)([^0-9]+)?/", $rs[0]["articlenumber"] , $regs);
60                         $number=$regs[1].($regs[2]+1).$regs[3];
61                 }
62                 $sql = "update defaults set articlenumber = '$number'";
63                 $rc=$db->query($sql);
64                 $rc=$db->query("COMMIT");
65                 $sql = "select * from parts where partnumber = '$number'";
66                 $rs=$db->getAll($sql);
67                 if ($rs[0]["id"]>0) return "";
68         }
69         return $number;
70 }
71
72 //Artikelnummer testen und wenn vorhanden Preis ändern
73 function chkPartNumberUpd($db,$sellprice,$partnumber,$check){
74         if ($partnumber=="") {
75                 $nummer=chkPartNumber($db,$partnumber,$check);
76                 if ($nummer=="") { return -99; }
77                 else { return $nummer; };
78         }
79         $sql = "select * from parts where partnumber = '$partnumber'";
80         $rs=$db->getAll($sql);
81         if ($rs[0]["id"]>0) {
82                 if ($check) return -1;
83                 $sql="update parts set sellprice = $sellprice where partnumber = '$partnumber'";
84                 $rc=$db->query($sql);
85                 if ($rc) return -1;
86                 return -99;
87         }
88         $nummer=chkPartNumber($db,$partnumber,$check);
89         if ($nummer=="") { return -99; }
90         else { return $nummer; };
91 }
92
93 function getBuchungsgruppe($db, $income, $expense) {
94
95         $income_id = getAccnoId($db, $income);
96         $expense_id = getAccnoId($db, $expense);
97         //$accno0_id = getAccnoId($db, $accno0);
98         //$accno1_id = getAccnoId($db, $accno1);
99         //$accno3_id = getAccnoId($db, $accno3);
100
101         $sql  = "select id from buchungsgruppen where ";
102         $sql .= "income_accno_id_0 = $income_id and ";
103         $sql .= "expense_accno_id_0 = $expense_id ";
104         //$sql .= "income_accno_id_0 = '$accno0_id' ";
105         //$sql .= "and income_accno_id_1 = '$accno1_id' ";
106         //$sql .= "and income_accno_id_3 = '$accno3_id'";
107         $rs=$db->getAll($sql);
108         return $rs[0]["id"];
109 }
110
111
112 function getFromBG($db, $bg_id, $name) {
113         
114         $sql  = "select $name from buchungsgruppen where id='$bg_id'";
115         $rs=$db->getAll($sql);
116         return $rs[0][$name];
117 }
118
119 function existUnit($db, $value) {
120         $sql="select name from units where name = '$value'";
121         $rs=$db->getAll($sql);
122         if (empty($rs[0]["name"]))
123                 return FALSE;
124         return TRUE;
125 }
126
127 function show($show, $things) {
128         if ($show)
129                 echo $things;
130 }
131
132 function getStdUnit($db,$type) {
133         $sql="select * from units where type='$type' order by sortkey limit 1";
134         $rs=$db->getAll($sql);
135         if (empty($rs[0]["name"])) return "Stck";
136         return $rs[0]["name"];
137 }
138
139 function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $insert, $show,$maske) {
140
141         $pgshow=false;
142         $note2show=false;
143         $fehler=0;
144         $precision=$maske["precision"];
145         $quotation=$maske["quotation"];
146         $quottype=$maske["quottype"];
147
148         $Update=($maske["update"]=="U")?true:false;
149         /* field description */
150         $parts_fld = array_keys($fields);
151
152         /* open csv file */
153         $f=fopen("$file.csv","r");
154         
155         /*
156          * read first line with table descriptions
157          */
158         show( $show, "<table border='1'><tr><td>#</td>\n");
159         if ($trenner=="other") $trenner=trim($trennzeichen);
160         if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1));
161         $infld=fgetcsv($f,1200,$trenner);
162         foreach ($infld as $fld) {
163                 $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>""))));
164                 $in_fld[]=$fld;
165                 if (in_array(trim($fld),$parts_fld)) {
166                         if ($fld=="partsgroup" || $fld=="partsgroup1" ) {
167                                 $pgshow=true;
168                         } else {
169                                 show( $show, "<td>$fld</td>\n");
170                         }
171                 }
172         }
173         if (!in_array("unit",$infld)) {
174                 $stdunitW=getStdUnit($db,"dimension");
175                 $stdunitD=getStdUnit($db,"service");
176                 $unit=true;
177                 show( $show, "<td>unit</td>\n");
178         };
179         if ($pgshow) show( $show, "<td>partsgroup</td>\n");
180         $posprice=0;
181         $posnumber=0;
182         $j=0;
183         foreach ($infld as $value) { 
184                 if ($infld[$j]=="sellprice") $posprice=$j;  
185                 if ($infld[$j]=="partnumber") $posnumber=$j;
186                 $j++; 
187         }
188         $m=0;           /* line */
189         $errors=0;      /* number of errors detected */
190         $income_accno = "";
191         $expense_accno = "";
192         if ($quottype=="P") $quotation=($quotation+100)/100;
193         while ( ($zeile=fgetcsv($f,15000,$trenner)) != FALSE) {
194                 $i=0;   /* column */
195                 $m++;   /* increase line */
196
197                 if ($Update) {
198                         $sellprice=$zeile[$posprice];
199                         $partnumber=$zeile[$posnumber];
200                         $sellprice = str_replace(",", ".", $sellprice);
201                         if ($quotation<>0) {
202                                 if ($quottype=="A") { $sellprice += $quotation; }
203                                 else { $sellprice = $sellprice * $quotation; }
204                         };
205                         if ($precision>=0) $sellprice = round($sellprice,$precision);
206                         $rc=chkPartNumberUpd($db,$sellprice,$partnumber,!$insert);
207                         if ($rc==-1) {
208                                 show($show,"<tr><td>Update </td><td>$partnumber:$sellprice</td></tr>\n");
209                                 continue;
210                         } else if ($rc==-99) {
211                                 show($show,"<tr><td>Fehler Zeile $m</td></tr>\n");
212                                 continue;
213                         } else {
214                                 $zeile[$posnumber]=$rc;
215                         }
216                 };
217
218                 $sql="insert into $file ";
219                 $keys="(";
220                 $vals=" values (";
221
222                 show( $show, "<tr><td>$m</td>\n");
223
224                 /* for each column */
225                 $dienstleistung=false;
226                 $artikel=-1;
227                 $partNr=false;
228                 $pg_name_val="";
229                 foreach($zeile as $data) {
230                         /* check if column will be imported */
231                         if (!in_array(trim($in_fld[$i]),$parts_fld)) {
232                                 $i++;
233                                 continue;
234                         };
235                         $data=trim($data);
236                         $key=$in_fld[$i];
237                         /* add key and data */
238
239                         /* special case partsgroup1 */
240                         if ($key == "partsgroup1") {
241                                 if ($pg_name_val<>"") {
242                                         if ($data<>"")  $pg_name_val.="!".$data;
243                                 } else {
244                                         $pg_name_val=$data;
245                                 }
246                                 $i++;
247                                 continue;
248                         } else if ($key == "partsgroup") {
249                                 /* special case partsgroup */
250                                 $pg_name_val=$data;
251                                 $i++;
252                                 continue;
253                         } else if ($key == "lastcost" || 
254                                    $key == "sellprice") {
255                                 
256                                 /* convert 0,0 numeric into 0.0 */
257                                 $data = str_replace(",", ".", $data);
258                                 if ($key == "sellprice") {
259                                         if ($quotation<>0) {
260                                                 if ($quottype=="A") { $data += $quotation; }
261                                                 else { $data = $data * $quotation; }
262                                         };
263                                         if ($precision>=0) $data = round($data,$precision);
264                                 }
265                         } else if ($key == "partnumber") {
266                                 $partNr=true;
267                                 $partnumber=chkPartNumber($db,$data,$check);
268                                 if ($partnumber=="") {
269                                         show( $show, "<td>NULL</td>\n");
270                                         $i++;
271                                         continue;
272                                 } else {
273                                         //$keys.="partnumber, ";
274                                         $data=$partnumber;
275                                         //show( $show, "<td>$partnumber</td>\n");
276                                 }
277                         } else if ($key == "description") {
278                                 $data=mb_convert_encoding($data,"ISO-8859-15","auto");
279                                 $data=addslashes($data);
280                         } else if ($key == "notes") {
281                                 $data=mb_convert_encoding($data,"ISO-8859-15","auto");
282                                 $data=addslashes($data);
283                         } else if ($key == "unit") {
284                                 if ($data=="") {
285                                         if ($maske["ware"]=="W") { $data=$stdunitW; }
286                                         else if ($maske["ware"]=="D") { $data=$stdunitD; }
287                                         //else if ($maske["ware"]=="G") { $data=$stdunitD; //Noch machen!}
288                                         else { $data=$stdunitW; };
289                                 }
290                                 /* convert stück and Stunde */
291                                 if (preg_match("/^st..?ck$/i", $data))
292                                         $data = "Stck";
293                                 else if ($data == "Stunde")
294                                         $data = "Std";
295                                 /* check if unit exists */
296                                 if (!existUnit($db, $data)) {
297                                         echo "Error in line $m: ";
298                                         echo "Einheit <b>$data</b> existiert nicht ";
299                                         echo "Bitte legen Sie diese Einheit an<br>";
300                                         $errors++;
301                                 }
302                         } else if ($key == "art") {
303                                 if ($maske["ware"]=="G" and strtoupper($data)=="D") { $artikel=false; }
304                                 else if ($maske["ware"]=="G") { $artikel=true; };
305                                 $i++;
306                                 continue;
307                         } else if ($key == "income_accno") {
308                                 $income_accno = $data;
309                                 $i++;
310                                 show( $show, "<td>$data</td>\n");
311                                 continue;
312                         } else if ($key == "expense_accno") {
313                                 $expense_accno = $data;
314                                 $i++;
315                                 show( $show, "<td>$data</td>\n");
316                                 continue;
317                         }
318                         if ($data==false or empty($data) or !$data) {
319                                 show( $show, "<td>NULL</td>\n");
320                                 $i++;
321                                 continue;
322                         }
323                         /* convert JA to Yes */
324                         if ($data === "J" || $data === "j")  $data = "Y";
325                         $vals.="'".$data."',";
326                         show( $show, "<td>".htmlentities($data)."</td>\n");
327                         $keys.=$key.",";
328                         $i++;
329                 }
330                 if ($unit) {
331                         if ($maske["ware"]=="D") { $einh=$stdunitD; }
332                         else  { $einh=$stdunitW; }
333                         $keys.="unit,";
334                         $vals.="'$einh',";
335                         show( $show,"<td>$einh</td>\n");
336                 }
337                 /* special case partsgroup */
338                 if ($pgshow) {
339                         if ($pg_name_val) {
340                                 /* get ID of partsgroup or add new 
341                                  * partsgroup_id */
342                                 $ID = getPartsgroupId($db, $pg_name_val, $insert);
343                                 $keys.= "partsgroup_id,";
344                                 $vals.="'".$ID."',";
345                                 show( $show, "<td>".htmlentities($pg_name_val).":$ID</td>\n");
346                         } else {
347                                 show( $show,"<td>NULL</td>\n");
348                         }
349                 }
350                 if ($artikel==-1) {
351                         if ($maske["ware"]=="D") {  $artikel=false; }
352                         else { $artikel=true; };                        
353                 }               
354                 if ($maske["bugrufix"]==1) {
355                         $bg = $maske["bugru"];
356                 } else {
357                         if ($income_accno<>"" and $expense_accno<>"") {
358                                 /* search for buchungsgruppe */
359                                 $bg = getBuchungsgruppe($db, $income_accno, $expense_accno);
360                                 if ($bg == "" and $maske["bugrufix"]==2 and $maske["bugru"]<>"") {
361                                         $bg = $maske["bugru"];
362                                 }
363                         } else if ($maske["bugru"]<>"" and $maske["bugrufix"]==2) {
364                                 $bg = $maske["bugru"];
365                         } else {
366                                 /* nothing found? user must create one */
367                                 echo "Error in line $m: ";
368                                 echo "Keine Buchungsgruppe gefunden für <br>";
369                                 echo "Erlöse Inland: $income_accno<br>";
370                                 echo "Bitte legen Sie eine an oder geben Sie eine vor.<br>";
371                                 echo "<br>";
372                                 $errors++;
373                         }
374                 }
375                 if ($bg > 0) {
376                         /* found one, add income_accno_id etc from buchungsgr.
377                          */
378                         $keys.="buchungsgruppen_id, ";
379                         $vals.="'$bg', ";
380                         /* XXX nur bei artikel!!! */
381                         if ($artikel) {
382                                 $keys.="inventory_accno_id, ";
383                                 $vals.=getFromBG($db, $bg, "inventory_accno_id")." ,";
384                         };
385                         $keys.="income_accno_id, ";
386                         $vals.=getFromBG($db, $bg, "income_accno_id_0")." ,";
387                         $keys.="expense_accno_id,";
388                         $vals.=getFromBG($db, $bg, "expense_accno_id_0")." ,";
389                 }
390                 if ($partNr==false) {
391                         $partnumber=chkPartNumber($db,"",$check);
392                         if ($partnumber=="") {
393                                 show( $show, "<td>NULL</td>\n");
394                                 $errors++;
395                         } else {
396                                 $keys.="partnumber, ";
397                                 $vals.="'$partnumber',";
398                                 show( $show, "<td>$partnumber</td>\n");
399                         }
400                 } 
401                 $sql.=$keys."import)";
402                 $sql.=$vals.time().")";         
403                 //show( $show, "<td> $sql </td>\n");
404
405                 if ($insert) {
406                         show( $show, "<td>");
407                         $db->showErr = TRUE;
408                         $rc=$db->query($sql);
409                         if (!$rc) {
410                                 echo "Fehler";
411                                 $fehler++;
412                         }
413                         show( $show, "</td>\n");
414                 }
415
416                 show( $show, "</tr>\n");
417         }
418
419         show( $show, "</table>\n");
420         fclose($f);
421         echo "$m Zeilen bearbeitet. ($fehler : Fehler) ";
422         return $errors;
423 }
424
425 ?>
426