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 getAccnoId($db, $accno) {
27 $sql = "select id from chart where accno='$accno'";
28 $rs=$db->getAll($sql);
32 function chkPartNumber($db,$number,$check) {
34 $sql = "select * from parts where partnumber = '$number'";
35 $rs=$db->getAll($sql);
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 $number=$rs[0]["articlenumber"]+1;
44 $sql = "update defaults set articlenumber = '$number'";
46 $rc=$db->query("COMMIT");
47 $sql = "select * from parts where partnumber = '$number'";
48 $rs=$db->getAll($sql);
49 if ($rs[0]["id"]>0) return "";
54 function getBuchungsgruppe($db, $income, $expense) {
56 $income_id = getAccnoId($db, $income);
57 $expense_id = getAccnoId($db, $expense);
58 //$accno0_id = getAccnoId($db, $accno0);
59 //$accno1_id = getAccnoId($db, $accno1);
60 //$accno3_id = getAccnoId($db, $accno3);
62 $sql = "select id from buchungsgruppen where ";
63 $sql .= "income_accno_id_0 = $income_id and ";
64 $sql .= "expense_accno_id_0 = $expense_id ";
65 //$sql .= "income_accno_id_0 = '$accno0_id' ";
66 //$sql .= "and income_accno_id_1 = '$accno1_id' ";
67 //$sql .= "and income_accno_id_3 = '$accno3_id'";
68 $rs=$db->getAll($sql);
73 function getFromBG($db, $bg_id, $name) {
75 $sql = "select $name from buchungsgruppen where id='$bg_id'";
76 $rs=$db->getAll($sql);
80 function existUnit($db, $value) {
81 $sql="select name from units where name = '$value'";
82 $rs=$db->getAll($sql);
83 if (empty($rs[0]["name"]))
88 function show($show, $things) {
93 function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$maske) {
95 /* field description */
96 $parts_fld = array_keys($fields);
99 $f=fopen("$file.csv","r");
102 * read first line with table descriptions
104 show( $show, "<table border='1'><tr>\n");
105 $infld=fgetcsv($f,1200,$trenner);
106 foreach ($infld as $fld) {
107 $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>""))));
109 if (in_array(trim($fld),$parts_fld)) {
110 show( $show, "<td>$fld</td>\n");
115 $errors=0; /* number of errors detected */
118 while ( ($zeile=fgetcsv($f,1200,$trenner)) != FALSE) {
121 $m++; /* increase line */
123 $sql="insert into $file ";
127 show( $show, "<tr>\n");
129 /* for each column */
130 $dienstleistung=false;
133 foreach($zeile as $data) {
134 /* check if column will be imported */
135 if (!in_array(trim($in_fld[$i]),$parts_fld)) {
140 $data=addslashes($data);
142 /* add key and data */
143 if ($data==false or empty($data) or !$data) {
144 show( $show, "<td>NULL</td>\n");
149 /* special case partsgroup */
150 if ($key == "partsgroup") {
152 /* get ID of partsgroup or add new
154 $data = getPartsgroupId($db, $data, $insert);
155 $key = "partsgroup_id";
157 /* TODO error handling */
159 } else if ($key == "lastcost" ||
160 $key == "sellprice") {
162 /* convert 0,0 numeric into 0.0 */
163 $data = str_replace(",", ".", $data);
165 } else if ($key == "partnumber") {
167 $partnumber=chkPartNumber($db,$data,$check);
168 if ($partnumber=="") {
169 show( $show, "<td>NULL</td>\n");
173 //$keys.="partnumber, ";
175 //show( $show, "<td>$partnumber</td>\n");
177 } else if ($key == "description") {
178 $data=addslashes($data);
179 } else if ($key == "notes") {
180 $data=addslashes($data);
181 } else if ($key == "unit") {
182 /* convert stück and Stunde */
183 if (preg_match("/^st..?ck$/i", $data))
185 else if ($data == "Stunde")
187 /* check if unit exists */
188 if (!existUnit($db, $data)) {
189 echo "Error in line $m: ";
190 echo "Einheit <b>$data</b> existiert nicht ";
191 echo "Bitte legen Sie diese Einheit an<br>";
194 } else if ($key == "art") {
195 if ($maske["ware"]=="G" and strtoupper($data)=="D") { $artikel=false; }
196 else if ($maske["ware"]=="G") { $artikel=true; };
199 } else if ($key == "income_accno") {
200 $income_accno = $data;
202 show( $show, "<td>$data</td>\n");
204 } else if ($key == "expense_accno") {
205 $expense_accno = $data;
207 show( $show, "<td>$data</td>\n");
210 /* convert JA to Yes */
214 $vals.="'".$data."',";
215 show( $show, "<td>$data</td>\n");
221 if ($maske["ware"]=="D") { $artikel=false; }
222 else { $artikel=true; };
224 if ($maske["bugrufix"]==1) {
225 $bg = $maske["bugru"];
227 /* search for buchungsgruppe */
228 $bg = getBuchungsgruppe($db, $income_accno, $expense_accno);
230 /* nothing found? user must create one */
232 if ($maske["bugrufix"]==2) {
233 $bg = $maske["bugru"];
235 echo "Error in line $m: ";
236 echo "Keine Buchungsgruppe gefunden für <br>";
237 echo "Erlöse Inland: $income_accno<br>";
238 //echo "Erlöse EU: $income_accno_1<br>";
239 //echo "Erlöse Ausland: $income_accno_3<br>";
240 echo "Bitte legen Sie eine an<br>";
246 /* found one, add income_accno_id etc from buchungsgr.
248 $keys.="buchungsgruppen_id, ";
250 /* XXX nur bei artikel!!! */
252 $keys.="inventory_accno_id, ";
253 $vals.=getFromBG($db, $bg, "inventory_accno_id")." ,";
255 $keys.="income_accno_id, ";
256 $vals.=getFromBG($db, $bg, "income_accno_id_0")." ,";
257 $keys.="expense_accno_id,";
258 $vals.=getFromBG($db, $bg, "expense_accno_id_0")." ,";
260 if ($partNr==false) {
261 $partnumber=chkPartNumber($db,"",$check);
262 if ($partnumber=="") {
263 show( $show, "<td>NULL</td>\n");
266 $keys.="partnumber, ";
267 $vals.="'$partnumber',";
268 show( $show, "<td>$partnumber</td>\n");
271 $sql.=$keys."import)";
272 $sql.=$vals.time().")";
273 //show( $show, "<td> $sql </td>\n");
276 show( $show, "<td>");
278 $rc=$db->query($sql);
281 show( $show, "</td>\n");
284 show( $show, "</tr>\n");
287 show( $show, "</table>\n");