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