xtcom/shopimport_db.php

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