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                 $number=$rs[0]["articlenumber"]+1;
  44                 $sql = "update defaults set articlenumber = '$number'";
  45                 $rc=$db->query($sql);
  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 "";
  50         }
  51         return $number;
  52 }
  53
  54 function getBuchungsgruppe($db, $income, $expense) {
  55
  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);
  61
  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);
  69         return $rs[0]["id"];
  70 }
  71
  72
  73 function getFromBG($db, $bg_id, $name) {
  74
  75         $sql  = "select $name from buchungsgruppen where id='$bg_id'";
  76         $rs=$db->getAll($sql);
  77         return $rs[0][$name];
  78 }
  79
  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"]))
  84                 return FALSE;
  85         return TRUE;
  86 }
  87
  88 function show($show, $things) {
  89         if ($show)
  90                 echo $things;
  91 }
  92
  93 function import_parts($db, $file, $trenner, $fields, $check, $insert, $show,$maske) {
  94
  95         /* field description */
  96         $parts_fld = array_keys($fields);
  97
  98         /* open csv file */
  99         $f=fopen("$file.csv","r");
 100
 101         /*
 102          * read first line with table descriptions
 103          */
 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("\""=>"","'"=>""))));
 108                 $in_fld[]=$fld;
 109                 if (in_array(trim($fld),$parts_fld)) {
 110                         show( $show, "<td>$fld</td>\n");
 111                 }
 112         }
 113
 114         $m=0;           /* line */
 115         $errors=0;      /* number of errors detected */
 116         $income_accno = "";
 117         $expense_accno = "";
 118         while ( ($zeile=fgetcsv($f,1200,$trenner)) != FALSE) {
 119
 120                 $i=0;   /* column */
 121                 $m++;   /* increase line */
 122
 123                 $sql="insert into $file ";
 124                 $keys="(";
 125                 $vals=" values (";
 126
 127                 show( $show, "<tr>\n");
 128
 129                 /* for each column */
 130                 $dienstleistung=false;
 131                 $artikel=-1;
 132                 $partNr=false;
 133                 foreach($zeile as $data) {
 134                         /* check if column will be imported */
 135                         if (!in_array(trim($in_fld[$i]),$parts_fld)) {
 136                                 $i++;
 137                                 continue;
 138                         };
 139                         $data=trim($data);
 140                         $data=addslashes($data);
 141                         $key=$in_fld[$i];
 142                         /* add key and data */
 143                         if ($data==false or empty($data) or !$data) {
 144                                 show( $show, "<td>NULL</td>\n");
 145                                 $i++;
 146                                 continue;
 147                         }
 148
 149                         /* special case partsgroup */
 150                         if ($key == "partsgroup") {
 151
 152                                 /* get ID of partsgroup or add new
 153                                  * partsgroup_id */
 154                                 $data = getPartsgroupId($db, $data, $insert);
 155                                 $key  = "partsgroup_id";
 156
 157                                 /* TODO error handling */
 158
 159                         } else if ($key == "lastcost" ||
 160                                    $key == "sellprice") {
 161
 162                                 /* convert 0,0 numeric into 0.0 */
 163                                 $data = str_replace(",", ".", $data);
 164
 165                         } else if ($key == "partnumber") {
 166                                 $partNr=true;
 167                                 $partnumber=chkPartNumber($db,$data,$check);
 168                                 if ($partnumber=="") {
 169                                         show( $show, "<td>NULL</td>\n");
 170                                         $i++;
 171                                         continue;
 172                                 } else {
 173                                         //$keys.="partnumber, ";
 174                                         $data=$partnumber;
 175                                         //show( $show, "<td>$partnumber</td>\n");
 176                                 }
 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))
 184                                         $data = "Stck";
 185                                 else if ($data == "Stunde")
 186                                         $data = "Std";
 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>";
 192                                         $errors++;
 193                                 }
 194                         } else if ($key == "art") {
 195                                 if ($maske["ware"]=="G" and strtoupper($data)=="D") { $artikel=false; }
 196                                 else if ($maske["ware"]=="G") { $artikel=true; };
 197                                 $i++;
 198                                 continue;
 199                         } else if ($key == "income_accno") {
 200                                 $income_accno = $data;
 201                                 $i++;
 202                                 show( $show, "<td>$data</td>\n");
 203                                 continue;
 204                         } else if ($key == "expense_accno") {
 205                                 $expense_accno = $data;
 206                                 $i++;
 207                                 show( $show, "<td>$data</td>\n");
 208                                 continue;
 209                         }
 210                         /* convert JA to Yes */
 211                         if ($data == "J" )
 212                                 $data = "Y";
 213
 214                         $vals.="'".$data."',";
 215                         show( $show, "<td>$data</td>\n");
 216                         $keys.=$key.",";
 217
 218                         $i++;
 219                 }
 220                 if ($artikel==-1) {
 221                         if ($maske["ware"]=="D") {  $artikel=false; }
 222                         else { $artikel=true; };
 223                 }
 224                 if ($maske["bugrufix"]==1) {
 225                         $bg = $maske["bugru"];
 226                 } else {
 227                         /* search for buchungsgruppe */
 228                         $bg = getBuchungsgruppe($db, $income_accno, $expense_accno);
 229                 }
 230                 /* nothing found? user must create one */
 231                 if ($bg == "") {
 232                         if ($maske["bugrufix"]==2) {
 233                                 $bg = $maske["bugru"];
 234                         } else {
 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>";
 241                                 echo "<br>";
 242                                 $errors++;
 243                         }
 244                 }
 245                 if ($bg > 0) {
 246                         /* found one, add income_accno_id etc from buchungsgr.
 247                          */
 248                         $keys.="buchungsgruppen_id, ";
 249                         $vals.="'$bg', ";
 250                         /* XXX nur bei artikel!!! */
 251                         if ($artikel) {
 252                                 $keys.="inventory_accno_id, ";
 253                                 $vals.=getFromBG($db, $bg, "inventory_accno_id")." ,";
 254                         };
 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")." ,";
 259                 }
 260                 if ($partNr==false) {
 261                         $partnumber=chkPartNumber($db,"",$check);
 262                         if ($partnumber=="") {
 263                                 show( $show, "<td>NULL</td>\n");
 264                                 $errors++;
 265                         } else {
 266                                 $keys.="partnumber, ";
 267                                 $vals.="'$partnumber',";
 268                                 show( $show, "<td>$partnumber</td>\n");
 269                         }
 270                 }
 271                 $sql.=$keys."import)";
 272                 $sql.=$vals.time().")";
 273                 //show( $show, "<td> $sql </td>\n");
 274
 275                 if ($insert) {
 276                         show( $show, "<td>");
 277                         $db->showErr = TRUE;
 278                         $rc=$db->query($sql);
 279                         if (!$rc)
 280                                 echo "Fehler";
 281                         show( $show, "</td>\n");
 282                 }
 283
 284                 show( $show, "</tr>\n");
 285         }
 286
 287         show( $show, "</table>\n");
 288         fclose($f);
 289         return $errors;
 290 }
 291
 292 ?>
 293