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 getMakemodel($db,$hersteller,$model,$partsid,$add=true) {
  27         $sql="select * from makemodel where make like '$hersteller' and model like = '$model'";
  28         $rs=$db->getAll($sql);
  29         if (empty($rs[0]["id"]) && $add) {
  30                 $sql="insert into makemodel (parts_id,make,model) values ($partsid,'$hersteller','$model')";
  31                 $rc=$db->query($sql);
  32                 if (!$rc) return "f";
  33                 return getMakemodel($db,$hersteller,$model,$partsid,false);
  34         }
  35         if ($rs[0]["parts_id"]==$partsid) { return "t"; }
  36         else { return "f"; }
  37 }
  38
  39 function getAccnoId($db, $accno) {
  40         $sql = "select id from chart where accno='$accno'";
  41         $rs=$db->getAll($sql);
  42         return $rs[0]["id"];
  43 }
  44
  45 function chkPartNumber($db,$number,$check) {
  46         if ($number<>"") {
  47                 $sql = "select * from parts where partnumber = '$number'";
  48                 $rs=$db->getAll($sql);
  49         }
  50         //echo $sql; print_r($rs);
  51         if ($rs[0]["id"]>0 or $number=="") {
  52                 if ($check) return "check";
  53                 $rc=$db->query("BEGIN");
  54                 $sql = "select  articlenumber from defaults";
  55                 $rs=$db->getAll($sql);
  56                 if ($rs[0]["articlenumber"]) {
  57                         preg_match("/([^0-9]+)?([0-9]+)([^0-9]+)?/", $rs[0]["articlenumber"] , $regs);
  58                         $number=$regs[1].($regs[2]+1).$regs[3];
  59                 }
  60                 $sql = "update defaults set articlenumber = '$number'";
  61                 $rc=$db->query($sql);
  62                 $rc=$db->query("COMMIT");
  63                 $sql = "select * from parts where partnumber = '$number'";
  64                 $rs=$db->getAll($sql);
  65                 if ($rs[0]["id"]>0) return "";
  66         }
  67         return $number;
  68 }
  69 function chkPartNumberUpd($db,$sellprice,$partnumber,$descript,$note,$check,$shop='n'){
  70         if ($partnumber=="") {
  71                 $nummer=chkPartNumber($db,$partnumber,$check);
  72                 if ($nummer=="") { return -99; }
  73                 else { return $nummer; };
  74         }
  75         $sql = "select * from parts where partnumber = '$partnumber'";
  76         $rs=$db->getAll($sql);
  77         if ($rs[0]["id"]>0) {
  78                 $sql="update parts set sellprice = $sellprice, shop='$shop'";
  79                 if ($descript) $sql.=",description='$descript',notes='$note'";
  80                 $sql.=" where partnumber = '$partnumber'";
  81                 $rc=$db->query($sql);
  82                 if ($rc) return -1;
  83                 return -99;
  84         }
  85         $nummer=chkPartNumber($db,$partnumber,$check);
  86         if ($nummer=="") { return -99; }
  87         else { return $nummer; };
  88 }
  89
  90 function getBuchungsgruppe($db, $income, $expense) {
  91
  92         $income_id = getAccnoId($db, $income);
  93         $expense_id = getAccnoId($db, $expense);
  94         //$accno0_id = getAccnoId($db, $accno0);
  95         //$accno1_id = getAccnoId($db, $accno1);
  96         //$accno3_id = getAccnoId($db, $accno3);
  97
  98         $sql  = "select id from buchungsgruppen where ";
  99         $sql .= "income_accno_id_0 = $income_id and ";
 100         $sql .= "expense_accno_id_0 = $expense_id ";
 101         //$sql .= "income_accno_id_0 = '$accno0_id' ";
 102         //$sql .= "and income_accno_id_1 = '$accno1_id' ";
 103         //$sql .= "and income_accno_id_3 = '$accno3_id'";
 104         $rs=$db->getAll($sql);
 105         return $rs[0]["id"];
 106 }
 107
 108
 109 function getFromBG($db, $bg_id, $name) {
 110
 111         $sql  = "select $name from buchungsgruppen where id='$bg_id'";
 112         $rs=$db->getAll($sql);
 113         return $rs[0][$name];
 114 }
 115
 116 function existUnit($db, $value) {
 117         $sql="select name from units where name = '$value'";
 118         $rs=$db->getAll($sql);
 119         if (empty($rs[0]["name"]))
 120                 return FALSE;
 121         return TRUE;
 122 }
 123
 124 function show($show, $things) {
 125         if ($show)
 126                 echo $things;
 127 }
 128
 129 function getStdUnit($db,$type) {
 130         $sql="select * from units where type='$type' order by sortkey limit 1";
 131         $rs=$db->getAll($sql);
 132         if (empty($rs[0]["name"])) return "Stck";
 133         return $rs[0]["name"];
 134 }
 135
 136
 137 function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $insert, $show,$maske) {
 138
 139         $pgshow=false;
 140         $note2show=false;
 141         $fehler=0;
 142         $precision=$maske["precision"];
 143         $quotation=$maske["quotation"];
 144         $quottype=$maske["quottype"];
 145         $shop=$maske["shop"];
 146         $wgtrenner=$maske["wgtrenner"];
 147         $UpdText=($maske["TextUpd"]=="1")?true:false;
 148
 149         $Update=($maske["update"]=="U")?true:false;
 150         /* field description */
 151         $parts_fld = array_keys($fields);
 152
 153         /* open csv file */
 154         $f=fopen("$file.csv","r");
 155
 156         /*
 157          * read first line with table descriptions
 158          */
 159         show( $show, "<table border='1'><tr><td>#</td>\n");
 160         if ($trenner=="other") $trenner=trim($trennzeichen);
 161         if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1));
 162         $infld=fgetcsv($f,1200,$trenner);
 163         foreach ($infld as $fld) {
 164                 $fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>""))));
 165                 $in_fld[]=$fld;
 166                 if (in_array(trim($fld),$parts_fld)) {
 167                         if (substr($fld,0,10)=="partsgroup") {
 168                                 $pgshow=true;
 169                         } else if ($fld=="notes" || $fld=="notes1" ) {
 170                                 $note2show=true;
 171                         } else {
 172                                 show( $show, "<td>$fld</td>\n");
 173                         }
 174                 }
 175         }
 176         if (!in_array("unit",$infld)) {
 177                 $stdunitW=getStdUnit($db,"dimension");
 178                 $stdunitD=getStdUnit($db,"service");
 179                 $unit=true;
 180                 show( $show, "<td>unit</td>\n");
 181         };
 182         if ($pgshow) show( $show, "<td>partsgroup</td>\n");
 183         if ($note2show) show( $show, "<td>notes</td>\n");
 184         $posprice=0;
 185         $posnumber=0;
 186         $posdescript=0;
 187         $posnotes=0;
 188         $posnotes1=0;
 189         $j=0;
 190         foreach ($infld as $value) {
 191                 if ($infld[$j]=="sellprice") $posprice=$j;
 192                 if ($infld[$j]=="partnumber") $posnumber=$j;
 193                 if ($infld[$j]=="description") $posdescript=$j;
 194                 if ($infld[$j]=="notes") $posnotes=$j;
 195                 if ($infld[$j]=="notes1") $posnotes1=$j;
 196                 $j++;
 197         }
 198         $m=0;           /* line */
 199         $errors=0;      /* number of errors detected */
 200         $income_accno = "";
 201         $expense_accno = "";
 202
 203         /*if ($insert) {
 204                 $sql="update parts set shop = 'n' where partnumber like '______'";
 205                 $rc=$db->query($sql);
 206                 //echo $sql; print_r($rc); echo "<br>";
 207                 if (!$rc) {
 208                         echo "Fehler: Artikel nicht aus dem Shop genommen";
 209                 }
 210         };*/
 211         if ($quottype=="P") $quotation=($quotation+100)/100;
 212         while ( ($zeile=fgetcsv($f,120000,$trenner)) != FALSE) {
 213                 $i=0;   /* column */
 214                 $m++;   /* increase line */
 215
 216                 if ($Update) {
 217                         $sellprice=$zeile[$posprice];
 218                         $partnumber=$zeile[$posnumber];
 219                         $sellprice = str_replace(",", ".", $sellprice);
 220                         if ($quotation<>0) {
 221                                 if ($quottype=="A") { $sellprice += $quotation; }
 222                                 else { $sellprice = $sellprice * $quotation; }
 223                         };
 224                         if ($precision>=0) $sellprice = round($sellprice,$precision);
 225                         if ($UpdText) {
 226                                 $description=$zeile[$posdescript];
 227                                 $note=$zeile[$posnotes];
 228                                 $note1=$zeile[$posnotes1];
 229                                 $note=mb_convert_encoding($note,"ISO-8859-15","ISO-8859-15");
 230                                 $note=preg_replace('/""[^ ]/','"',$note);
 231                                 $note=" \n".addslashes($note);
 232                                 $note1=mb_convert_encoding($note1,"ISO-8859-15","ISO-8859-15");
 233                                 $note1=preg_replace('/""[^ ]/','"',$note1);
 234                                 $note.=" \n".addslashes($note1);
 235                                 $rc=chkPartNumberUpd($db,$sellprice,$partnumber,$description,$note,$check);
 236                         } else {
 237                                 $rc=chkPartNumberUpd($db,$sellprice,$partnumber,false,false,$check);
 238                         }
 239                         if ($rc==-1) {
 240                                 show($show,"<tr><td>Update </td><td>$partnumber:$sellprice</td></tr>\n");
 241                                 continue;
 242                         } else if ($rc==-99) {
 243                                 show($show,"<tr><td>Fehler Zeile $m</td></tr>\n");
 244                                 continue;
 245                         } else {
 246                                 $zeile[$posnumber]=$rc;
 247                         }
 248                 };
 249
 250                 $sql="insert into $file ";
 251                 $keys="(";
 252                 $vals=" values (";
 253                 show( $show, "<tr><td>$m</td>\n");
 254
 255                 /* for each column */
 256                 $dienstleistung=false;
 257                 $artikel=-1;
 258                 $partNr=false;
 259                 $pg_name_val=array();
 260                 $note_val="";
 261                 $model="";
 262                 $hersteller="";
 263                 foreach($zeile as $data) {
 264                         /* check if column will be imported */
 265                         if (!in_array(trim($in_fld[$i]),$parts_fld)) {
 266                                 $i++;
 267                                 continue;
 268                         };
 269                         $data=trim($data);
 270                         $key=$in_fld[$i];
 271                         /* add key and data */
 272
 273                         /* special case partsgroup1 */
 274                         if (substr($key,0,10) == "partsgroup") {
 275                                 if (strlen($key)==10) { $pgnr=0; }
 276                                 else { $pgnr=substr($key,-1); }
 277                                 $pg_name_val[$pgnr]=$data;
 278                                 $i++;
 279                                 continue;
 280                         } else  if ($key == "lastcost" ||
 281                                    $key == "sellprice") {
 282
 283                                 /* convert 0,0 numeric into 0.0 */
 284                                 $data = str_replace(",", ".", $data);
 285                                 if ($key == "sellprice") {
 286                                         if ($quotation<>0) {
 287                                                 if ($quottype=="A") { $data += $quotation; }
 288                                                 else { $data = $data * $quotation; }
 289                                         };
 290                                         if ($precision>=0) $data = round($data,$precision);
 291                                 }
 292                         } else if ($key == "partnumber") {
 293                                 $partNr=true;
 294                                 $partnumber=chkPartNumber($db,$data,$check);
 295                                 if ($partnumber=="") {
 296                                         show( $show, "<td>NULL</td>\n");
 297                                         $i++;
 298                                         continue;
 299                                 } else {
 300                                         //$keys.="partnumber, ";
 301                                         $data=$partnumber;
 302                                         //show( $show, "<td>$partnumber</td>\n");
 303                                 }
 304                         } else if ($key == "description") {
 305                                 $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
 306                                 $data=preg_replace('/""[^ ]/','"',$data);
 307                                 $data=addslashes($data);
 308                         } else if ($key == "notes") {
 309                                 $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
 310                                 $data=preg_replace('/""[^ ]/','"',$data);
 311                                 $notesval=addslashes($data);
 312                                 $i++;
 313                                 continue;
 314                         } else if ($key == "notes1") {
 315                                 $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
 316                                 $data=preg_replace('/""[^ ]/','"',$data);
 317                                 $notesval.=" \n".addslashes($data);
 318                                 $i++;
 319                                 continue;
 320                                 //$key="notes";
 321                         /*} else if ($key == "makemodel") {
 322                                 $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
 323                                 $hersteller=addslashes($data);
 324                                 $i++;
 325                                 continue;
 326                         } else if ($key == "model") {
 327                                 $data=mb_convert_encoding($data,"ISO-8859-15","ISO-8859-15");
 328                                 $model=addslashes($data);
 329                                 $i++;
 330                                 continue;*/
 331                         } else if ($key == "unit") {
 332                                 if ($data=="") {
 333                                         if ($maske["ware"]=="W") { $data=$stdunitW; }
 334                                         else if ($maske["ware"]=="D") { $data=$stdunitD; }
 335                                         //else if ($maske["ware"]=="G") { $data=$stdunitD; //Noch machen!}
 336                                         else { $data=$stdunitW; };
 337                                 }
 338                                 /* convert stück and Stunde */
 339                                 if (preg_match("/^st..?ck$/i", $data))
 340                                         $data = "Stck";
 341                                 else if ($data == "Stunde")
 342                                         $data = "Std";
 343                                 /* check if unit exists */
 344                                 //echo "!$data!";
 345                                 if (!existUnit($db, $data)) {
 346                                         echo "Error in line $m: ";
 347                                         echo "Einheit <b>$data</b> existiert nicht ";
 348                                         echo "Bitte legen Sie diese Einheit an<br>";
 349                                         $errors++;
 350                                 }
 351                         } else if ($key == "art") {
 352                                 if ($maske["ware"]=="G" and strtoupper($data)=="D") { $artikel=false; }
 353                                 else if ($maske["ware"]=="G") { $artikel=true; };
 354                                 $i++;
 355                                 continue;
 356                         } else if ($key == "income_accno") {
 357                                 $income_accno = $data;
 358                                 $i++;
 359                                 show( $show, "<td>$data</td>\n");
 360                                 continue;
 361                         } else if ($key == "expense_accno") {
 362                                 $expense_accno = $data;
 363                                 $i++;
 364                                 show( $show, "<td>$data</td>\n");
 365                                 continue;
 366                         }
 367                         if ($data==false or empty($data) or !$data) {
 368                                 show( $show, "<td>NULL</td>\n");
 369                                 $i++;
 370                                 continue;
 371                         }
 372                         /* convert JA to Yes */
 373                         if ($data === "J" || $data === "j")  $data = "Y";
 374                         $vals.="'".$data."',";
 375                         show( $show, "<td>".htmlentities($data)."</td>\n");
 376                         $keys.=$key.",";
 377                         $i++;
 378                 }
 379                 if ($unit) {
 380                         if ($maske["ware"]=="D") { $einh=$stdunitD; }
 381                         else  { $einh=$stdunitW; }
 382                         $keys.="unit,";
 383                         $vals.="'$einh',";
 384                         show( $show,"<td>$einh</td>\n");
 385                 }
 386                 /* special case partsgroup */
 387                 //Mehrere Warengruppennamen werden mit einem Trennzeichen zu einen Namen zusammengefürt.
 388                 if ($pgshow) {
 389                         foreach($pg_name_val as $pg) {
 390                                 if ($pg>"") $pgname.=$pg.$wgtrenner;
 391                         };
 392                         unset($pg_name_val);
 393                         if ($pgname and $pgname <> "!") {
 394                                 $pgname=substr($pgname,0,-1);
 395                                 /* get ID of partsgroup or add new
 396                                  * partsgroup_id */
 397                                 $ID = getPartsgroupId($db, $pgname, $insert);
 398                                 $keys.= "partsgroup_id,";
 399                                 $vals.="'".$ID."',";
 400                                 show( $show, "<td>".htmlentities($pgname).":$ID</td>\n");
 401                         } else {
 402                                 show( $show,"<td>NULL</td>\n");
 403                         }
 404                 }
 405                 if ($note2show) {
 406                         if ($notesval) {
 407                                 $keys.="notes,";
 408                                 $vals.="'$notesval',";
 409                                 if (strlen($notesval)>255) {
 410                                         show( $show, "<td>".substr($notesval,0,25)." . . . ".htmlentities(substr($notesval,-25))."</td>\n");
 411                                 } else {
 412                                         show( $show, "<td>$notesval</td>\n");
 413                                 }
 414                         } else {
 415                                 show( $show,"<td>NULL</td>\n");
 416                         }
 417                 }
 418                 if ($artikel==-1) {
 419                         if ($maske["ware"]=="D") {  $artikel=false; }
 420                         else { $artikel=true; };
 421                 }
 422                 if ($maske["bugrufix"]==1) {
 423                         $bg = $maske["bugru"];
 424                 } else {
 425                         if ($income_accno<>"" and $expense_accno<>"") {
 426                                 /* search for buchungsgruppe */
 427                                 $bg = getBuchungsgruppe($db, $income_accno, $expense_accno);
 428                                 if ($bg == "" and $maske["bugrufix"]==2 and $maske["bugru"]<>"") {
 429                                         $bg = $maske["bugru"];
 430                                 }
 431                         } else if ($maske["bugru"]<>"" and $maske["bugrufix"]==2) {
 432                                 $bg = $maske["bugru"];
 433                         } else {
 434                                 /* nothing found? user must create one */
 435                                 echo "Error in line $m: ";
 436                                 echo "Keine Buchungsgruppe gefunden für <br>";
 437                                 echo "Erlöse Inland: $income_accno<br>";
 438                                 echo "Bitte legen Sie eine an oder geben Sie eine vor.<br>";
 439                                 echo "<br>";
 440                                 $errors++;
 441                         }
 442                 }
 443                 if ($bg > 0) {
 444                         /* found one, add income_accno_id etc from buchungsgr.
 445                          */
 446                         $keys.="buchungsgruppen_id, ";
 447                         $vals.="'$bg', ";
 448                         /* XXX nur bei artikel!!! */
 449                         if ($artikel) {
 450                                 $keys.="inventory_accno_id, ";
 451                                 $vals.=getFromBG($db, $bg, "inventory_accno_id")." ,";
 452                         };
 453                         $keys.="income_accno_id, ";
 454                         $vals.=getFromBG($db, $bg, "income_accno_id_0")." ,";
 455                         $keys.="expense_accno_id,";
 456                         $vals.=getFromBG($db, $bg, "expense_accno_id_0")." ,";
 457                 }
 458                 if ($partNr==false) {
 459                         $partnumber=chkPartNumber($db,"",$check);
 460                         if ($partnumber=="") {
 461                                 show( $show, "<td>NULL</td>\n");
 462                                 $errors++;
 463                         } else {
 464                                 $keys.="partnumber, ";
 465                                 $vals.="'$partnumber',";
 466                                 show( $show, "<td>$partnumber</td>\n");
 467                         }
 468                 }
 469
 470                 $sql.=$keys."shop,import)";
 471                 $sql.="$vals'$shop',".time().")";
 472
 473                 if ($insert) {
 474                         show( $show, "<td>");
 475                         $db->showErr = TRUE;
 476                         $rc=$db->query($sql);
 477                         if (!$rc) {
 478                                 echo "Fehler";
 479                                 $fehler++;
 480                         }
 481                         show( $show, "</td>\n");
 482                 }
 483                 $pgname="";
 484                 show( $show, "</tr>\n");
 485         }
 486
 487         show( $show, "</table>\n");
 488         fclose($f);
 489         echo "$m Zeilen bearbeitet. ($fehler : Fehler) ";
 490         return $errors;
 491 }
 492
 493 ?>
 494