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