olcom/shopimport_db.php

   1 <?php
   2 /***************************************************************
   3 * $Id: shopimport_db.php,v 2.0 2006/10/26 10:49:11 hli Exp $
   4 *Author: Holger Lindemann
   5 *Copyright: (c) 2004 Lx-System
   6 *License: non free
   7 *eMail: info@lx-system.de
   8 *Version: 2.0
   9 *Shop: xt:Commerce 3.04
  10 *ERP: kivitendo ERP 3.0.0
  11 ***************************************************************/
  12 require_once "shoplib.php";
  13
  14
  15 /*******************************************
  16 * createCategoryLang($id,$lang,$name)
  17 * Kategorie für eine Sprache anlegen. Ist immer
  18 * in der gleichen Sprache, da ERP nur eine hat.
  19 *******************************************/
  20 function createCategoryLang($id,$lang,$name) {
  21         $sql="insert into ".PREFIX."categories_description (categories_id,language_id,categories_name,categories_meta_title) ";
  22         $sql.="values ($id,$lang,'$name','$name')";
  23         $rc=query("shop",$sql,"createCategoryLang");
  24         return $rc;
  25 }
  26
  27 /*******************************************
  28 * createCategory($name,$maingroup,$Lang,$Lanuages)
  29 * Eine Kategorie in der default-Sprache anlegen
  30 *******************************************/
  31 function createCategory($name,$maingroup,$Lang,$Languages) {
  32         echo "Kategorie: $name<br>";
  33         //Kategorie nicht vorhanden, anlegen
  34         $newID=uniqid(rand());
  35         $sql="insert into ".PREFIX."categories (categories_image,parent_id,date_added) values ('$newID',$maingroup,now())";
  36         $rc=query("shop",$sql,"createCategory_1");
  37         if ($rc === -99) return false;
  38         $sql="select * from ".PREFIX."categories where categories_image = '$newID'";
  39         $rs=getAll("shop",$sql,"createCategory_2");
  40         $id=$rs[0]["categories_id"];
  41         $sql="update ".PREFIX."categories set categories_image = null where categories_id=$id";
  42         $rc=query("shop",$sql,"createCategory_3");
  43         if ($rc === -99) return false;
  44         createCategoryLang($id,$Lang,$name);
  45         if ($Languages) foreach ($Languages as $erp=>$shop) {
  46                 if ($Lang<>$shop) {
  47                         createCategoryLang($id,$shop,$name);
  48                 }
  49         }
  50         return ($rc !== -99)?$id:false;
  51 }
  52
  53 /*******************************************
  54 * getCategory($name,$Lang,$Languages)
  55 * gibt es die Kategorie schon?
  56 *******************************************/
  57 function getCategory($name,$Lang,$Languages) {
  58         if (empty($name)) $name="Default";
  59         $tmp=split("!",$name);
  60         $maingroup=0;
  61         $found=true;
  62         $i=0;
  63         do {
  64                 $sql="select D.*,C.parent_id from ".PREFIX."categories C left join ".PREFIX."categories_description D on C.categories_id=D.categories_id ";
  65                 $sql.="where (categories_name = '".$tmp[$i]."' or categories_meta_title ='".$tmp[$i]."') and ";
  66                 $sql.="C.parent_id=$maingroup and language_id=$Lang";
  67                 $rs=getAll("shop",$sql,"getCategory");
  68                 if ($rs) {
  69                         $maingroup=$rs[0]["categories_id"];
  70                         $i++;
  71                 } else {
  72                         $found=false;
  73                 }
  74         } while ($rs and $found and $i<count($tmp));
  75         for (;$i<count($tmp); $i++) {
  76                 $maingroup=createCategory($tmp[$i],$maingroup,$Lang,$Languages);
  77         }
  78         return $maingroup;
  79 }
  80
  81
  82 /*******************************************
  83 * bilder($width,$height,$dest
  84 * Bild in der gewünschten Größe erzeugen
  85 *******************************************/
  86 function bilder($width,$height,$dest) {
  87         if (!function_exists("imagick_readimage")) { echo "Imagick-Extention nicht installiert"; return false; };
  88         $handle=imagick_readimage("./tmp/tmp.file_org");
  89         if (!$handle) {
  90                 $reason      = imagick_failedreason( $handle ) ;
  91                 print "Lesen: $reason<BR>\n" ; flush();
  92                 return false;
  93         }
  94         if (!imagick_resize( $handle, $width, $height, IMAGICK_FILTER_UNKNOWN, 0)) {
  95                 $reason      = imagick_failedreason( $handle ) ;
  96                 print "Resize: $reason<BR>\n" ; flush();
  97                 return false;
  98         }
  99         if (!imagick_writeimage( $handle,"./tmp/tmp.file_$dest")) {
 100                 $reason      = imagick_failedreason( $handle ) ;
 101                 print "Schreiben: $reason<BR>\n" ;      flush();
 102                 return false;
 103         }
 104         return true;
 105 }
 106
 107 /*******************************************
 108 * uploadImage($image,$id)
 109 * Ein Bild zum Shop übertragen
 110 *******************************************/
 111 function uploadImage($image,$id) {
 112 global $ERPftphost,$ERPftpuser,$ERPftppwd,$ERPimgdir,$SHOPftphost,$SHOPftpuser,$SHOPftppwd,
 113                 $SHOPimgdir,$picsize;
 114         $picdest = array("thumb"=>"thumbnail_images","info"=>"info_images","popup"=>"popup_images","org"=>"original_images");
 115         $ok=true;
 116         // Bilder holen
 117         if ($ERPftphost=="localhost")
 118         {
 119                 $aus=""; $rc2=0;
 120                 if (is_file($ERPimgdir."/".$image)) {
 121                         $rc3=@exec("cp $ERPimgdir/$image ./tmp/tmp.file_org",$aus,$rc2);
 122                         if ($rc2>0) { $ok=false; echo "[Downloadfehler: $image]<br>"; };
 123                 } else {
 124                         echo "[Downloadfehler: $ERPimgdir/$image nicht gefunden]";
 125                         return false;
 126                 }
 127         } else {
 128                 $conn_id = ftp_connect($ERPftphost);
 129                 if ($conn_id==false) {
 130                         echo "[Kein FTP-Verbindung ERP]";
 131                         return false;
 132                 }
 133                 ftp_login($conn_id,$ERPftpuser,$ERPftppwd);
 134                 $src=$ERPimgdir."/".$image;
 135                 $upload=ftp_get($conn_id,"tmp/tmp.file_org","$src",FTP_BINARY);
 136                 if (!$upload) { $ok=false; echo "[Ftp Downloadfehler: $image]<br>";};
 137                 ftp_quit($conn_id);
 138         }
 139         if ($ok) {
 140                 //Bildergrößen erzeugen
 141                 if (!bilder($picsize["PRODUCT_IMAGE_THUMBNAIL_WIDTH"],$picsize["PRODUCT_IMAGE_THUMBNAIL_HEIGHT"],"thumb")) return false;
 142                 if (!bilder($picsize["PRODUCT_IMAGE_INFO_WIDTH"],$picsize["PRODUCT_IMAGE_INFO_HEIGHT"],"info")) return false;
 143                 if (!bilder($picsize["PRODUCT_IMAGE_POPUP_WIDTH"],$picsize["PRODUCT_IMAGE_POPUP_HEIGHT"],"popup")) return false;
 144                 $name=(strrpos($image,"/")>0)?substr($image,strrpos($image,"/")+1):$image;
 145                 //zum Shop übertragen
 146                 if ($SHOPftphost=="localhost") {
 147                         foreach ($picdest as $key => $val) {
 148                                 if (is_dir($SHOPimgdir."/".$val."/")) {
 149                                         $src=$SHOPimgdir."/".$val."/".$name;
 150                                         $rc2=0; $aus="";
 151                                         $rc3=@exec("cp ./tmp/tmp.file_$key $src",$aus,$rc2);
 152                                         if ($rc2>0) { $ok=false; echo "[Uploadfehler: $src]"; };
 153                                 } else {
 154                                         echo "[Uploadfehler: $val nicht gefunden]";
 155                                         return false;
 156                                 }
 157                         }
 158                 } else {
 159                         $conn_id = ftp_connect($SHOPftphost);
 160                         if ($conn_id==false) {
 161                                 echo "[Kein FTP-Verbindung Shop]";
 162                                 return false;
 163                         }
 164                         ftp_login($conn_id,$SHOPftpuser,$SHOPftppwd);
 165                         foreach ($picdest as $key => $val) {
 166                                 $src=$SHOPimgdir."/".$val."/".$name;
 167                                 $upload=ftp_put($conn_id,"$src","tmp/tmp.file_".$key,FTP_BINARY);
 168                                 if (!$upload) { $ok=false; echo $key."[FTP Uploadfehler $src]<br>";};
 169                         }
 170                         ftp_quit($conn_id);
 171                 }
 172                 if ($ok) {
 173                         $sql="update ".PREFIX."products set products_image='%s',products_last_modified=now() where products_id=%d";
 174                         $sql=sprintf($sql,$name,$id);
 175                         $rc=query("shop",$sql,"uploadImage");
 176                         if ($rc === -99) return false;
 177                         echo "i";
 178                 }
 179         }
 180         return true;
 181 }
 182
 183 /*******************************************
 184 * insartikel($data,$defLang
 185 * Einen neuen Artikel im Shop anlegen
 186 *******************************************/
 187 function insartikel($data,$defLang) {
 188         $newID=uniqid(rand());
 189         $sql="insert into ".PREFIX."products (products_model,products_image) values ('".$data["partnumber"]."','$newID')";
 190         $rc=query("shop",$sql,"insartikel_1");
 191         if ($rc === -99) return false;
 192         $sql="select * from ".PREFIX."products where products_image='$newID'";
 193         $rs=getAll("shop",$sql,"insartikel_2");
 194         $sql="update products set products_image=null where products_id=".$rs[0]["products_id"];
 195         $rc=query("shop",$sql,"insartikel_3");
 196         if ($rc === -99) return false;
 197         $sql="insert into ".PREFIX."products_to_categories (products_id,categories_id) values ";
 198         $sql.="(".$rs[0]["products_id"].",".$data["categories_id"].")";
 199         $rc=query("shop",$sql,"insartikel_4");
 200         if ($rc === -99) return false;
 201         echo " + ";
 202         updartikel($data,$rs[0]["products_id"],$defLang);
 203         return $rs[0]["products_id"];
 204 }
 205
 206 /*******************************************
 207 *
 208 *
 209 *******************************************/
 210 function updartikel($data,$id,$defLang) {
 211 global $tax,$KDGrp;
 212         echo $id." ";
 213         $sql="update ".PREFIX."products set products_status=1,products_price=%01.2f,products_weight=%01.2f,";
 214         $sql.="products_tax_class_id=%d,products_last_modified=now(),products_quantity=%d where products_id=%d";
 215         $price=($data["sellprice"]>0)?$data["sellprice"]:$data["stdprice"];
 216         $sql=sprintf($sql,$price,$data["weight"],$tax[sprintf("%1.4f",$data["rate"])],$data["onhand"],$id);
 217         $rc=query("shop",$sql,"updartikel_1");
 218         $sql="update ".PREFIX."products_to_categories set categories_id=".$data["categories_id"]." where products_id=$id";
 219         $rc=query("shop",$sql,"updartikel_2");
 220         echo "~";
 221         if ($KDGrp>0) personal_offer ($data["altprice"],$id);
 222         $sql="select * from ".PREFIX."products_description where products_id=$id and language_id=$defLang";
 223         $rs=getAll("shop",$sql,"updartikel_3");
 224         if ($rs) {  // bestehende Sprachen abgleichen
 225                 $sql="update ".PREFIX."products_description set products_name='%s',products_description='%s' where ";
 226                 $sql.="products_id=%d and language_id=$defLang";
 227                 $sql=sprintf($sql,$data["description"],$data["notes"],$id);
 228                 echo "l";
 229         } else {  // neue Sprache einfügen
 230                 $sql="insert into ".PREFIX."products_description (products_id,products_name,products_description,language_id) ";
 231                 $sql.="values (%d,'%s','%s',%d)";
 232                 $sql=sprintf($sql,$id,$data["description"],$data["notes"],$defLang);
 233                 echo "L";
 234         }
 235         $rc=query("shop",$sql,"updartikel_4");
 236         if ($rc === -99) { return false;
 237         } else { return true; }
 238 }
 239
 240 /*******************************************
 241 * personal_offer ($personal_offer,$products_id)
 242 * Spezialangebote anlegen
 243 *******************************************/
 244 function personal_offer ($personal_offer,$products_id) {
 245 global $KDGrp;
 246         $sql="select * from ".PREFIX."personal_offers_by_customers_status_$KDGrp where ";
 247         $sql.="products_id=$products_id order by quantity limit 1";
 248         $rs=getAll("shop",$sql,"personal_offer_1");
 249         if ($rs) {
 250                 if ($personal_offer) {
 251                         $sql="update ".PREFIX."personal_offers_by_customers_status_$KDGrp ";
 252                         $sql.="set personal_offer=$personal_offer where price_id = ".$rs[0]["price_id"];
 253                         echo "p";
 254                 } else {
 255                         $sql="delete from ".PREFIX."personal_offers_by_customers_status_$KDGrp where price_id = ".$rs[0]["price_id"];
 256                         echo "q";
 257                 }
 258                 $rc=query("shop",$sql,"personal_offer_2");
 259         } else {
 260                 if ($personal_offer) {
 261                         $sql="insert into ".PREFIX."personal_offers_by_customers_status_$KDGrp ";
 262                         $sql.="(price_id,products_id,quantity,personal_offer) ";
 263                         $sql.="values (0,$products_id,1,$personal_offer)";
 264                         $rc=query("shop",$sql,"personal_offer_3");
 265                         if ($rc === -99) return false;
 266                         echo "P";
 267                 }
 268         };
 269         return true;
 270 }
 271
 272 /*******************************************
 273 * chkartikellang($data,$Lang)
 274 * Gibt es den Artikel und hat er sich geändert
 275 *******************************************/
 276 function chkartikellang($data,$Lang) {
 277 global $tax,$KDGrp,$GeoZone;
 278         if ($data["partnumber"]=="") { echo "Artikelnummer fehlt!<br>"; return false;};
 279         echo $data["partnumber"]." ".$data["translation"]." -> ";
 280         $sql ="select P.products_id from ".PREFIX."products P left join ".PREFIX."products_description PD on P.products_id=PD.products_id where ";
 281         $sql.="products_model like '".$data["partnumber"]."' and language_id=$Lang";
 282         $rs=getAll("shop",$sql,"chkartikellang");
 283         if (count($rs)>0) {
 284                 $sql="update ".PREFIX."products_description set products_name='".$data["translation"]."', products_description='".$data["longdescription"]."' ";
 285                 $sql.="where products_id='".$rs[0]["products_id"]."' and language_id=$Lang";
 286                 $rc=query("shop",$sql,"chkartikellang_u");
 287                 if ($rc === -99) return false;
 288         } else {
 289                 $sql ="select products_id from ".PREFIX."products where products_model like '".$data["partnumber"]."'";
 290                 $rs=getAll("shop",$sql,"chkartikellang");
 291                 $sql="insert into ".PREFIX."products_description (products_id,language_id,products_name,products_description) values (";
 292                 $sql.=$rs[0]["products_id"].",$Lang,'".$data["translation"]."','".$data["longdescription"]."')";
 293                 $rc=query("shop",$sql,"chkartikellang_i");
 294                 if ($rc === -99) return false;
 295         }
 296         echo $rs[0]["products_id"]."<br>\n";
 297         return true;
 298 }
 299
 300 /*******************************************
 301 * chkartikel($data,$defLang)
 302 * Hat sich der Artikel verändert
 303 *******************************************/
 304 function chkartikel($data,$defLang) {
 305 global $tax,$erptax,$KDGrp,$GeoZone,$nopic;
 306         if ($data["partnumber"]=="") { echo "Artikelnummer fehlt!<br>"; return;};
 307         if ($data["image"]) {
 308                 $data["picname"]=(strrpos($data["image"],"/")>0)?substr($data["image"],strrpos($data["image"],"/")+1):$data["image"];
 309         } else if ($nopic) {
 310                 $data["picname"]=(strrpos($nopic,"/")>0)?substr($nopic,strrpos($nopic,"/")+1):$nopic;
 311                 $data["image"]=$nopic;
 312         }
 313         $data["onhand"]=floor($data["onhand"]);
 314         echo $data["partnumber"]." ".$data["description"]." -> ";
 315         $sql ="select * from ".PREFIX."products where products_model like '".$data["partnumber"]."'";
 316         $rs=getAll("shop",$sql,"chkartikel");
 317         $data["rate"]=$erptax[$data["bugru"]]["rate"];
 318         if ($rs) {
 319                 updartikel($data,$rs[0]["products_id"],$defLang);
 320                 if ($rs[0]["products_image"]<>$data["picname"] and $data["picname"]) uploadImage($data["image"],$rs[0]["products_id"]);
 321         } else {
 322                 $id=insartikel($data,$defLang);
 323                 if ($data["image"]) uploadImage($data["image"],$id);
 324         }
 325         echo "<br>\n";
 326 }
 327
 328 /*******************************************
 329 * Grafiken
 330 *******************************************/
 331 //Defaultwerte
 332 $picsize= array("PRODUCT_IMAGE_THUMBNAIL_WIDTH" => 120,"PRODUCT_IMAGE_THUMBNAIL_HEIGHT" => 80,
 333                 "PRODUCT_IMAGE_INFO_WIDTH" => 200,"PRODUCT_IMAGE_INFO_HEIGHT" => 160,
 334                 "PRODUCT_IMAGE_POPUP_WIDTH" => 300,"PRODUCT_IMAGE_POPUP_HEIGHT" => 240,"IMAGE_QUALITY" => 80);
 335 //persönliche Werte
 336 foreach ($picsize as $key => $val) {
 337         $sql=sprintf("select configuration_value from ".PREFIX."configuration where configuration_key='%s'",$key);
 338         $rs=getAll("shop",$sql,"Konfiguration");
 339         if ($rs[0][$key]) $picsize[$key]=$rs[0][$key];
 340 }
 341
 342 /*******************************************
 343 * Steuern
 344 *******************************************/
 345 //Steuertabelle ERP
 346 $sql ="select  BG.id as bugru,T.rate,TK.startdate from buchungsgruppen BG left join chart C ";
 347 $sql.="on BG.income_accno_id_0=C.id left join taxkeys TK on TK.chart_id=C.id left join tax T ";
 348 $sql.="on T.id=TK.tax_id where TK.startdate <= now()";
 349 $rs=getAll("erp",$sql,"Tax ERP");
 350 $erptax=array();
 351 foreach ($rs as $row) {
 352         if ($erptax[$row["bugru"]]["startdate"]<$row["startdate"]) {
 353                 $erptax[$row["bugru"]]["startdate"]=$row["startdate"];
 354                 $erptax[$row["bugru"]]["rate"]=$row["rate"]*100;
 355         }
 356 }
 357
 358 //Steuerzone Shop
 359 $sql="select GZ.geo_zone_id from ".PREFIX."configuration C, ".PREFIX."zones_to_geo_zones GZ ";
 360 $sql.="where C.configuration_key='STORE_COUNTRY' and GZ.zone_country_id=C.configuration_value";
 361 $rs=getAll("shop",$sql,"GZ");
 362 if ($rs) {
 363         $GeoZone=$rs[0]["geo_zone_id"];
 364 } else {
 365         echo "Steuerzone nicht gefunden";
 366         exit;
 367 }
 368
 369 //Steuersätze
 370 $sql="select * from ".PREFIX."tax_rates where tax_zone_id=$GeoZone";
 371 $rs=getAll("shop",$sql,"tax_rates");
 372 if ($rs) {
 373         foreach ($rs as $zeile) {
 374                 $tax[$zeile["tax_rate"]]=$zeile["tax_class_id"];
 375         }
 376 } else {
 377         $tax[0]="";
 378 }
 379
 380 /*******************************************
 381 * Sprache
 382 *******************************************/
 383 if (empty($Language) || !$Language) {
 384         echo "Keine Sprachzuordnung definiert!";
 385         exit;
 386 }
 387
 388 //Default Shopsprache ermitteln
 389 $sql="select * from ".PREFIX."languages L left join ".PREFIX."configuration C on L.code=C.configuration_value ";
 390 $sql.="where  configuration_key = 'DEFAULT_LANGUAGE'";
 391 $rs=getAll("shop",$sql,"DefaultLang");
 392
 393 if ($rs) {
 394         $ShopdefaultLang=$rs[0]["languages_id"];
 395         if ($SHOPdefaultlang<>$ShopdefaultLang) {
 396                 echo "Defaultsprache im Shop wurde geändert";
 397                 exit;
 398         }
 399 } else  {
 400         echo "Keine Defaultsprache im Shop eingestellt.";
 401         exit;
 402 }
 403
 404 $Languages=array();
 405 foreach ($Language as $Langrow) {
 406         if ($Langrow["SHOP"]>0 and $Langrow["ERP"]>0) $Languages[$Langrow["ERP"]]=$Langrow["SHOP"];
 407 }
 408
 409 /*******************************************
 410 * Import starten
 411 *******************************************/
 412 $artikel=shopartikel(); //array_keys($Languages));
 413
 414 echo "Artikelexport ERP -&gt; xt:Commerce (Standardsprache $ShopdefaultLang): ".count($artikel)." Artikel markiert.<br>";
 415
 416 if ($artikel) { //Mit jedem Artikel in der Defaultsprache:
 417         foreach ($artikel as $data) {
 418                 //Kategorie abfragen/anlegen
 419                 $data["categories_id"]=getCategory($data["partsgroup"],$ShopdefaultLang,$Languages);
 420                 chkartikel($data,$ShopdefaultLang,false);
 421         }
 422
 423         foreach ($Languages as $erplang=>$shoplang) { //Mit jeder weiteren Sprache
 424                 $artikel=shopartikellang($erplang,$SpracheAlle);
 425                 echo "Shopsprache: $shoplang<br>";
 426                 if ($artikel) {
 427                         foreach ($artikel as $data) {
 428                                 //Kategorie abfragen
 429                                 $data["categories_id"]=getCategory($data["partsgroup"],$shoplang,$Languages);
 430                                 if ($SpracheAlle) {
 431                                         if ($data["translation"]=="") $data["translation"]=$data["description"];
 432                                         if ($data["longdescription"]=="") $data["longdescription"]=$data["notes"];
 433                                 }
 434                                 chkartikellang($data,$shoplang);
 435                         }
 436                 }
 437         }
 438 } else {
 439         if ($debug) writelog("Fehler\n");
 440 }
 441
 442 ?>