X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/72ef551aab3f6ff1c9f972c00cd8ae3018cc94e5..0a4891e33e1ee05dd0363d985fb1a2205e25c63c:/xtcom/shopimport_db.php diff --git a/xtcom/shopimport_db.php b/xtcom/shopimport_db.php new file mode 100644 index 000000000..377439def --- /dev/null +++ b/xtcom/shopimport_db.php @@ -0,0 +1,471 @@ +"; + //Kategorie nicht vorhanden, anlegen + $newID=uniqid(rand()); + $sql="insert into categories (categories_image,parent_id,date_added) values ('$newID',$maingroup,now())"; + $rc=query("shop",$sql,"createCategory_1"); + if ($rc === -99) return false; + $sql="select * from categories where categories_image = '$newID'"; + $rs=getAll("shop",$sql,"createCategory_2"); + $id=$rs[0]["categories_id"]; + $sql="update categories set categories_image = null where categories_id=$id"; + $rc=query("shop",$sql,"createCategory_3"); + if ($rc === -99) return false; + createCategoryLang($id,$Lang,$name); + if ($Languages) foreach ($Languages as $erp=>$shop) { + if ($Lang<>$shop) { + createCategoryLang($id,$shop,$name); + } + } + return ($rc !== -99)?$id:false; +} + +/******************************************* +* getCategory($name,$Lang,$Languages) +* gibt es die Kategorie schon? +*******************************************/ +function getCategory($name,$Lang,$Languages) { + if (empty($name)) $name="Default"; + $tmp=split("!",$name); + $maingroup=0; + $found=true; + $i=0; + do { + $sql="select D.*,C.parent_id from categories C left join categories_description D on C.categories_id=D.categories_id "; + $sql.="where (categories_name = '".$tmp[$i]."' or categories_meta_title ='".$tmp[$i]."') and "; + $sql.="C.parent_id=$maingroup and language_id=$Lang"; + $rs=getAll("shop",$sql,"getCategory"); + if ($rs) { + $maingroup=$rs[0]["categories_id"]; + $i++; + } else { + $found=false; + } + } while ($rs and $found and $i0) { echo "[Bildwandeln: $image.$dest]
"; return false; }; + + if (!function_exists("imagick_readimage")) { echo "Imagick-Extention nicht installiert"; return false; }; + $handle=imagick_readimage("./tmp/tmp.file_org"); + if (!$handle) { + $reason = imagick_failedreason( $handle ) ; + print "Lesen: $reason
\n" ; flush(); + return false; + } + if (!imagick_resize( $handle, $width, $height, IMAGICK_FILTER_UNKNOWN, 0)) { + $reason = imagick_failedreason( $handle ) ; + print "Resize: $reason
\n" ; flush(); + return false; + } + if (!imagick_writeimage( $handle,"./tmp/tmp.file_$dest")) { + $reason = imagick_failedreason( $handle ) ; + print "Schreiben: $reason
\n" ; flush(); + return false; + } + return true; +} + +/******************************************* +* uploadImage($image,$id) +* Ein Bild zum Shop übertragen +*******************************************/ +function uploadImage($image,$id) { +global $ERPftphost,$ERPftpuser,$ERPftppwd,$ERPimgdir,$SHOPftphost,$SHOPftpuser,$SHOPftppwd, + $SHOPimgdir,$picsize; + $picdest = array("thumb"=>"thumbnail_images","info"=>"info_images","popup"=>"popup_images","org"=>"original_images"); + $ok=true; + // Bilder holen + if ($ERPftphost=="localhost") + { + $aus=""; $rc2=0; + if (is_file($ERPimgdir."/".$image)) { + $rc3=@exec("cp $ERPimgdir/$image ./tmp/tmp.file_org",$aus,$rc2); + if ($rc2>0) { $ok=false; echo "[Downloadfehler: $image]
"; }; + } else { + echo "[Downloadfehler: $ERPimgdir/$image nicht gefunden]"; + return false; + } + } else { + $conn_id = ftp_connect($ERPftphost); + if ($conn_id==false) { + echo "[Kein FTP-Verbindung ERP]"; + return false; + } + ftp_login($conn_id,$ERPftpuser,$ERPftppwd); + $src=$ERPimgdir."/".$image; + $upload=ftp_get($conn_id,"tmp/tmp.file_org","$src",FTP_BINARY); + if (!$upload) { $ok=false; echo "[Ftp Downloadfehler: $image]
";}; + ftp_quit($conn_id); + } + if ($ok) { + //Bildergrößen erzeugen + if (!bilder($picsize["PRODUCT_IMAGE_THUMBNAIL_WIDTH"],$picsize["PRODUCT_IMAGE_THUMBNAIL_HEIGHT"],"thumb")) return false; + if (!bilder($picsize["PRODUCT_IMAGE_INFO_WIDTH"],$picsize["PRODUCT_IMAGE_INFO_HEIGHT"],"info")) return false; + if (!bilder($picsize["PRODUCT_IMAGE_POPUP_WIDTH"],$picsize["PRODUCT_IMAGE_POPUP_HEIGHT"],"popup")) return false; + $name=(strrpos($image,"/")>0)?substr($image,strrpos($image,"/")+1):$image; + //zum Shop übertragen + if ($SHOPftphost=="localhost") { + foreach ($picdest as $key => $val) { + if (is_dir($SHOPimgdir."/".$val."/")) { + $src=$SHOPimgdir."/".$val."/".$name; + $rc2=0; $aus=""; + $rc3=@exec("cp ./tmp/tmp.file_$key $src",$aus,$rc2); + print "!$rc2,$rc3!"; + if ($rc2>0) { $ok=false; echo "[Uploadfehler: $src]"; }; + } else { + echo "[Uploadfehler: $val nicht gefunden]"; + return false; + } + } + } else { + $conn_id = ftp_connect($SHOPftphost); + if ($conn_id==false) { + echo "[Kein FTP-Verbindung Shop]"; + return false; + } + ftp_login($conn_id,$SHOPftpuser,$SHOPftppwd); + foreach ($picdest as $key => $val) { + $src=$SHOPimgdir."/".$val."/".$name; + $upload=ftp_put($conn_id,"$src","tmp/tmp.file_".$key,FTP_BINARY); + if (!$upload) { $ok=false; echo $key."[FTP Uploadfehler $src]
";}; + } + ftp_quit($conn_id); + } + if ($ok) { + $sql="update products set products_image='%s',products_last_modified=now() where products_id=%d"; + $sql=sprintf($sql,$name,$id); + $rc=query("shop",$sql,"uploadImage"); + if ($rc === -99) return false; + echo "i"; + } + } + return true; +} + +/******************************************* +* insartikel($data,$defLang +* Einen neuen Artikel im Shop anlegen +*******************************************/ +function insartikel($data,$defLang) { + $newID=uniqid(rand()); + $sql="insert into products (products_model,products_image) values ('".$data["partnumber"]."','$newID')"; + $rc=query("shop",$sql,"insartikel_1"); + if ($rc === -99) return false; + $sql="select * from products where products_image='$newID'"; + $rs=getAll("shop",$sql,"insartikel_2"); + $sql="update products set products_image=null where products_id=".$rs[0]["products_id"]; + $rc=query("shop",$sql,"insartikel_3"); + $sql="insert into products_to_categories (products_id,categories_id) values "; + $sql.="(".$rs[0]["products_id"].",".$data["categories_id"].")"; + $rc=query("shop",$sql,"insartikel_4"); + if ($rc === -99) return false; + echo " + "; + updartikel($data,$rs[0]["products_id"],$defLang); + return $rs[0]["products_id"]; +} + +/******************************************* +* +* +*******************************************/ +function updartikel($data,$id,$defLang) { +global $tax,$KDGrp; + echo $id." "; + $sql="update products set products_status=1,products_price=%01.2f,products_weight=%01.2f,"; + $sql.="products_tax_class_id=%d,products_last_modified=now(),products_quantity=%d where products_id=%d"; + $price=($data["sellprice"]>0)?$data["sellprice"]:$data["stdprice"]; + $sql=sprintf($sql,$price,$data["weight"],$tax[sprintf("%1.4f",$data["rate"])],$data["onhand"],$id); + $rc=query("shop",$sql,"updartikel_1"); + $sql="update products_to_categories set categories_id=".$data["categories_id"]." where products_id=$id"; + $rc=query("shop",$sql,"updartikel_2"); + echo "~"; + if ($KDGrp>0) personal_offer ($data["altprice"],$id); + $sql="select * from products_description where products_id=$id and language_id=$defLang"; + $rs=getAll("shop",$sql,"updartikel_3"); + if ($rs) { // bestehende Sprachen abgleichen + $sql="update products_description set products_name='%s',products_description='%s' where "; + $sql.="products_id=%d and language_id=$defLang"; + $sql=sprintf($sql,$data["description"],$data["notes"],$id); + echo "l"; + } else { // neue Sprache einfügen + $sql="insert into products_description (products_id,products_name,products_description,language_id) "; + $sql.="values (%d,'%s','%s',%d)"; + $sql=sprintf($sql,$id,$data["description"],$data["notes"],$defLang); + echo "L"; + } + $rc=query("shop",$sql,"updartikel_4"); + if ($rc === -99) return false; +} + +/******************************************* +* personal_offer ($personal_offer,$products_id) +* Spezialangebote anlegen +*******************************************/ +function personal_offer ($personal_offer,$products_id) { +global $KDGrp; + $sql="select * from personal_offers_by_customers_status_$KDGrp where "; + $sql.="products_id=$products_id order by quantity limit 1"; + $rs=getAll("shop",$sql,"personal_offer_1"); + if ($rs) { + if ($personal_offer) { + $sql="update personal_offers_by_customers_status_$KDGrp "; + $sql.="set personal_offer=$personal_offer where price_id = ".$rs[0]["price_id"]; + echo "p"; + } else { + $sql="delete from personal_offers_by_customers_status_$KDGrp where price_id = ".$rs[0]["price_id"]; + echo "q"; + } + $rc=query("shop",$sql,"personal_offer_2"); + } else { + if ($personal_offer) { + $sql="insert into personal_offers_by_customers_status_$KDGrp "; + $sql.="(price_id,products_id,quantity,personal_offer) "; + $sql.="values (0,$products_id,1,$personal_offer)"; + $rc=query("shop",$sql,"personal_offer_3"); + if ($rc === -99) return false; + echo "P"; + } + }; +} + +/******************************************* +* chkartikellang($data,$Lang) +* Gibt es den Artikel und hat er sich geändert +*******************************************/ +function chkartikellang($data,$Lang) { +global $tax,$KDGrp; + if ($data["partnumber"]=="") { echo "Artikelnummer fehlt!
"; return;}; + echo $data["partnumber"]." ".$data["translation"]." -> "; + $sql ="select P.products_id from products P left join products_description PD on P.products_id=PD.products_id where "; + $sql.="products_model like '".$data["partnumber"]."' and language_id=$Lang"; + $rs=getAll("shop",$sql,"chkartikellang"); + if (count($rs)>0) { + $sql="update products_description set products_name='".$data["translation"]."', products_description='".$data["longdescription"]."' "; + $sql.="where products_id='".$rs[0]["products_id"]."' and language_id=$Lang"; + $rc=query("shop",$sql,"chkartikellang_u"); + } else { + $sql ="select products_id from products where products_model like '".$data["partnumber"]."'"; + $rs=getAll("shop",$sql,"chkartikellang"); + $sql="insert into products_description (products_id,language_id,products_name,products_description) values ("; + $sql.=$rs[0]["products_id"].",$Lang,'".$data["translation"]."','".$data["longdescription"]."')"; + $rc=query("shop",$sql,"chkartikellang_i"); + if ($rc === -99) return false; + } + echo $rs[0]["products_id"]."
\n"; + return true; +} + +/******************************************* +* chkartikel($data,$defLang) +* Hat sich der Artikel verändert +*******************************************/ +function chkartikel($data,$defLang) { +global $tax,$erptax,$shop2erp,$KDGrp,$GeoZone,$nopic; + if ($data["partnumber"]=="") { echo "Artikelnummer fehlt!
"; return;}; + if ($data["image"]) { + $data["picname"]=(strrpos($data["image"],"/")>0)?substr($data["image"],strrpos($data["image"],"/")+1):$data["image"]; + } else if ($nopic) { + $data["picname"]=(strrpos($nopic,"/")>0)?substr($nopic,strrpos($nopic,"/")+1):$nopic; + $data["image"]=$nopic; + } + $data["onhand"]=floor($data["onhand"]); + echo $data["partnumber"]." ".$data["description"]." -> "; + $sql ="select * from products where products_model like '".$data["partnumber"]."'"; + $rs=getAll("shop",$sql,"chkartikel"); + $data["rate"]=$erptax[$data["bugru"]]["rate"]; + if ($rs) { + updartikel($data,$rs[0]["products_id"],$defLang); + if ($rs[0]["products_image"]<>$data["picname"] and $data["picname"]) uploadImage($data["image"],$rs[0]["products_id"]); + } else { + $id=insartikel($data,$defLang); + if ($data["image"]) uploadImage($data["image"],$id); + } + echo "
\n"; +} + +/******************************************* +* Grafiken +*******************************************/ +//Defaultwerte +$picsize= array("PRODUCT_IMAGE_THUMBNAIL_WIDTH" => 120,"PRODUCT_IMAGE_THUMBNAIL_HEIGHT" => 80, + "PRODUCT_IMAGE_INFO_WIDTH" => 200,"PRODUCT_IMAGE_INFO_HEIGHT" => 160, + "PRODUCT_IMAGE_POPUP_WIDTH" => 300,"PRODUCT_IMAGE_POPUP_HEIGHT" => 240,"IMAGE_QUALITY" => 80); +//persönliche Werte +foreach ($picsize as $key => $val) { + $sql=sprintf("select configuration_value from configuration where configuration_key='%s'",$key); + $rs=getAll("shop",$sql,"Picsize"); + if ($rs[0]["configuration_value"]>0) $picsize[$key]=$rs[0]["configuration_value"]; +} + +/******************************************* +* Steuern +*******************************************/ +//Steuertabelle ERP +$sql ="select BG.id as bugru,T.rate,TK.startdate from buchungsgruppen BG left join chart C "; +$sql.="on BG.income_accno_id_0=C.id left join taxkeys TK on TK.chart_id=C.id left join tax T "; +$sql.="on T.id=TK.tax_id where TK.startdate <= now()"; +$rs=getAll("erp",$sql,"Tax ERP"); +$erptax=array(); +foreach ($rs as $row) { + if ($erptax[$row["bugru"]]["startdate"]<$row["startdate"]) { + $erptax[$row["bugru"]]["startdate"]=$row["startdate"]; + $erptax[$row["bugru"]]["rate"]=$row["rate"]*100; + } +} + +//Steuerzone Shop +$sql="select GZ.geo_zone_id from configuration C, zones_to_geo_zones GZ "; +$sql.="where C.configuration_key='STORE_COUNTRY' and GZ.zone_country_id=C.configuration_value"; +$rs=getAll("shop",$sql,"GZ"); +if ($rs) { + $GeoZone=$rs[0]["geo_zone_id"]; +} else { + echo "Steuerzone nicht gefunden"; + exit; +} + +//Steuersätze +$sql="select * from tax_rates where tax_zone_id=$GeoZone"; +$rs=getAll("shop",$sql,"tax_rates"); +if ($rs) { + foreach ($rs as $zeile) { + $tax[$zeile["tax_rate"]]=$zeile["tax_class_id"]; + } +} else { + $tax[0]=""; +} + +/******************************************* +* Sprache +*******************************************/ +if (empty($Language) || !$Language) { + echo "Keine Sprachzuordnung definiert!"; + exit; +} + +//Default Shopsprache ermitteln +$sql="select * from languages L left join configuration C on L.code=C.configuration_value "; +$sql.="where configuration_key = 'DEFAULT_LANGUAGE'"; +$rs=getAll("shop",$sql,"DefaultLang"); + +if ($rs) { + $ShopdefaultLang=$rs[0]["languages_id"]; + if ($SHOPdefaultlang<>$ShopdefaultLang) { + echo "Defaultsprache im Shop wurde geändert ($SHOPdefaultlang<>$ShopdefaultLang)"; + exit; + } +} else { + echo "Keine Defaultsprache im Shop eingestellt."; + exit; +} + +$Languages=array(); +foreach ($Language as $Langrow) { + if ($Langrow["SHOP"]>0 and $Langrow["ERP"]>0) $Languages[$Langrow["ERP"]]=$Langrow["SHOP"]; +} + +/******************************************* +* Import starten +*******************************************/ +$artikel=shopartikel(); //array_keys($Languages)); + +echo "Artikelexport ERP -> xt:Commerce (Standardsprache $ShopdefaultLang): ".count($artikel)." Artikel markiert.
"; + +if ($artikel) { //Mit jedem Artikel in der Defaultsprache: + foreach ($artikel as $data) { + //Kategorie abfragen/anlegen + $data["categories_id"]=getCategory($data["partsgroup"],$ShopdefaultLang,$Languages); + chkartikel($data,$ShopdefaultLang,false); + } + + foreach ($Languages as $erplang=>$shoplang) { //Mit jeder weiteren Sprache + $artikel=shopartikellang($erplang,$SpracheAlle); + echo "Shopsprache: $shoplang
"; + if ($artikel) { + foreach ($artikel as $data) { + //Kategorie abfragen + $data["categories_id"]=getCategory($data["partsgroup"],$shoplang,$Languages); + if ($SpracheAlle) { + if ($data["translation"]=="") $data["translation"]=$data["description"]; + if ($data["longdescription"]=="") $data["longdescription"]=$data["notes"]; + } + chkartikellang($data,$shoplang); + } + } + } +} else { + if ($debug) { + $log=fopen("tmp/shop.log","a"); + fputs($log,$nun.": Fehler\n"); + } +} +require ("diff.php"); + +?>