Filterkriterium "Kunden-/Lieferantenname" in Berichten auch beim Umsortieren beibehal...
[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 function getAccnoId($db, $accno) {
27         $sql = "select id from chart where accno='$accno'";
28         $rs=$db->getAll($sql);
29         return $rs[0]["id"];
30 }
31
32 function chkPartNumber($db,$number,$check) {
33         if ($number<>"") {
34                 $sql = "select * from parts where partnumber = '$number'";
35                 $rs=$db->getAll($sql);
36         }
37         //echo $sql; print_r($rs);
38         if ($rs[0]["id"]>0 or $number=="") {
39                 if ($check) return "check";
40                 $rc=$db->query("BEGIN");
41                 $sql = "select  articlenumber from defaults";
42                 $rs=$db->getAll($sql);
43                 if ($rs[0]["articlenumber"]) {
44                         preg_match("/([^0-9]+)?([0-9]+)([^0-9]+)?/", $rs[0]["articlenumber"] , $regs);
45                         $number=$regs[1].($regs[2]+1).$regs[3];
46                 }
47                 $sql = "update defaults set articlenumber = '$number'";
48                 $rc=$db->query($sql);
49                 $rc=$db->query("COMMIT");
50                 $sql = "select * from parts where partnumber = '$number'";
51                 $rs=$db->getAll($sql);
52                 if ($rs[0]["id"]>0) return "";
53         }
54         return $number;
55 }
56
57 function getBuchungsgruppe($db, $income, $expense) {
58
59         $income_id = getAccnoId($db, $income);
60         $expense_id = getAccnoId($db, $expense);
61         //$accno0_id = getAccnoId($db, $accno0);
62         //$accno1_id = getAccnoId($db, $accno1);
63         //$accno3_id = getAccnoId($db, $accno3);
64
65         $sql  = "select id from buchungsgruppen where ";
66         $sql .= "income_accno_id_0 = $income_id and ";
67         $sql .= "expense_accno_id_0 = $expense_id ";
68         //$sql .= "income_accno_id_0 = '$accno0_id' ";
69         //$sql .= "and income_accno_id_1 = '$accno1_id' ";
70         //$sql .= "and income_accno_id_3 = '$accno3_id'";
71         $rs=$db->getAll($sql);
72         return $rs[0]["id"];
73 }
74
75
76 function getFromBG($db, $bg_id, $name) {
77         
78         $sql  = "select $name from buchungsgruppen where id='$bg_id'";
79         $rs=$db->getAll($sql);
80         return $rs[0][$name];
81 }
82
83 function existUnit($db, $value) {
84         $sql="select name from units where name = '$value'";
85         $rs=$db->getAll($sql);
86         if (empty($rs[0]["name"]))
87                 return FALSE;
88         return TRUE;
89 }
90
91 function show($show, $things) {
92         if ($show)
93                 echo $things;
94 }
95
96 function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$maske) {
97
98         /* field description */
99         $parts_fld = array_keys($fields);
100
101         /* open csv file */
102         $f=fopen("$file.csv","r");
103         
104         /*
105          * read first line with table descriptions
106          */
107         show( $show, "<table border='1'><tr><td>#</td>\n");
108         $infld=fgetcsv($f,1200,$trenner);
109         foreach ($infld as $fld) {
110                 $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>""))));
111                 $in_fld[]=$fld;
112                 if (in_array(trim($fld),$parts_fld)) {
113                         show( $show, "<td>$fld</td>\n");
114                 }
115         }
116
117         $m=0;           /* line */
118         $errors=0;      /* number of errors detected */
119         $income_accno = "";
120         $expense_accno = "";
121         while ( ($zeile=fgetcsv($f,1200,$trenner)) != FALSE) {
122                 $i=0;   /* column */
123                 $m++;   /* increase line */
124
125                 $sql="insert into $file ";
126                 $keys="(";
127                 $vals=" values (";
128
129                 show( $show, "<tr><td>$m</td>\n");
130
131                 /* for each column */
132                 $dienstleistung=false;
133                 $artikel=-1;
134                 $partNr=false;
135                 foreach($zeile as $data) {
136                         /* check if column will be imported */
137                         if (!in_array(trim($in_fld[$i]),$parts_fld)) {
138                                 $i++;
139                                 continue;
140                         };
141                         $data=trim($data);
142                         //$data=addslashes($data);
143                         $key=$in_fld[$i];
144                         /* add key and data */
145                         if ($data==false or empty($data) or !$data) {
146                                 show( $show, "<td>NULL</td>\n");
147                                 $i++;
148                                 continue;
149                         }
150
151                         /* special case partsgroup */
152                         if ($key == "partsgroup") {
153
154                                 /* get ID of partsgroup or add new 
155                                  * partsgroup_id */
156                                 $data = getPartsgroupId($db, $data, $insert);
157                                 $key  = "partsgroup_id";
158
159                                 /* TODO error handling */
160
161                         } else if ($key == "lastcost" || 
162                                    $key == "sellprice") {
163                                 
164                                 /* convert 0,0 numeric into 0.0 */
165                                 $data = str_replace(",", ".", $data);
166
167                         } else if ($key == "partnumber") {
168                                 $partNr=true;
169                                 $partnumber=chkPartNumber($db,$data,$check);
170                                 if ($partnumber=="") {
171                                         show( $show, "<td>NULL</td>\n");
172                                         $i++;
173                                         continue;
174                                 } else {
175                                         //$keys.="partnumber, ";
176                                         $data=$partnumber;
177                                         //show( $show, "<td>$partnumber</td>\n");
178                                 }
179                         } else if ($key == "description") {
180                                 $data=mb_convert_encoding($data,"ISO-8859-15","auto");
181                                 $data=addslashes($data);
182                         } else if ($key == "notes") {
183                                 $data=mb_convert_encoding($data,"ISO-8859-15","auto");
184                                 $data=addslashes($data);
185                         } else if ($key == "unit") {
186                                 /* convert stück and Stunde */
187                                 if (preg_match("/^st..?ck$/i", $data))
188                                         $data = "Stck";
189                                 else if ($data == "Stunde")
190                                         $data = "Std";
191                                 /* check if unit exists */
192                                 if (!existUnit($db, $data)) {
193                                         echo "Error in line $m: ";
194                                         echo "Einheit <b>$data</b> existiert nicht ";
195                                         echo "Bitte legen Sie diese Einheit an<br>";
196                                         $errors++;
197                                 }
198                         } else if ($key == "art") {
199                                 if ($maske["ware"]=="G" and strtoupper($data)=="D") { $artikel=false; }
200                                 else if ($maske["ware"]=="G") { $artikel=true; };
201                                 $i++;
202                                 continue;
203                         } else if ($key == "income_accno") {
204                                 $income_accno = $data;
205                                 $i++;
206                                 show( $show, "<td>$data</td>\n");
207                                 continue;
208                         } else if ($key == "expense_accno") {
209                                 $expense_accno = $data;
210                                 $i++;
211                                 show( $show, "<td>$data</td>\n");
212                                 continue;
213                         }
214                         /* convert JA to Yes */
215                         if ($data == "J" )
216                                 $data = "Y";
217
218                         $vals.="'".$data."',";
219                         show( $show, "<td>$data</td>\n");
220                         $keys.=$key.",";
221         
222                         $i++;
223                 }
224                 if ($artikel==-1) {
225                         if ($maske["ware"]=="D") {  $artikel=false; }
226                         else { $artikel=true; };                        
227                 }               
228                 if ($maske["bugrufix"]==1) {
229                         $bg = $maske["bugru"];
230                 } else {
231                         if ($income_accno<>"" and $expense_accno<>"") {
232                                 /* search for buchungsgruppe */
233                                 $bg = getBuchungsgruppe($db, $income_accno, $expense_accno);
234                                 if ($bg == "" and $maske["bugrufix"]==2 and $maske["bugru"]<>"") {
235                                         $bg = $maske["bugru"];
236                                 }
237                         } else if ($maske["bugru"]<>"" and $maske["bugrufix"]==2) {
238                                 $bg = $maske["bugru"];
239                         } else {
240                                 /* nothing found? user must create one */
241                                 echo "Error in line $m: ";
242                                 echo "Keine Buchungsgruppe gefunden für <br>";
243                                 echo "Erlöse Inland: $income_accno<br>";
244                                 echo "Bitte legen Sie eine an oder geben Sie eine vor.<br>";
245                                 echo "<br>";
246                                 $errors++;
247                         }
248                 }
249                 if ($bg > 0) {
250                         /* found one, add income_accno_id etc from buchungsgr.
251                          */
252                         $keys.="buchungsgruppen_id, ";
253                         $vals.="'$bg', ";
254                         /* XXX nur bei artikel!!! */
255                         if ($artikel) {
256                                 $keys.="inventory_accno_id, ";
257                                 $vals.=getFromBG($db, $bg, "inventory_accno_id")." ,";
258                         };
259                         $keys.="income_accno_id, ";
260                         $vals.=getFromBG($db, $bg, "income_accno_id_0")." ,";
261                         $keys.="expense_accno_id,";
262                         $vals.=getFromBG($db, $bg, "expense_accno_id_0")." ,";
263                 }
264                 if ($partNr==false) {
265                         $partnumber=chkPartNumber($db,"",$check);
266                         if ($partnumber=="") {
267                                 show( $show, "<td>NULL</td>\n");
268                                 $errors++;
269                         } else {
270                                 $keys.="partnumber, ";
271                                 $vals.="'$partnumber',";
272                                 show( $show, "<td>$partnumber</td>\n");
273                         }
274                 } 
275                 $sql.=$keys."import)";
276                 $sql.=$vals.time().")";         
277                 //show( $show, "<td> $sql </td>\n");
278
279                 if ($insert) {
280                         show( $show, "<td>");
281                         $db->showErr = TRUE;
282                         $rc=$db->query($sql);
283                         if (!$rc) {
284                                 echo "Fehler";
285                                 $fehler++;
286                         }
287                         show( $show, "</td>\n");
288                 }
289
290                 show( $show, "</tr>\n");
291         }
292
293         show( $show, "</table>\n");
294         fclose($f);
295         echo "$m Zeilen bearbeitet. ($fehler : Fehler) ";
296         return $errors;
297 }
298
299 ?>
300