2 /***************************************************************
3 *Author: Holger Lindemann
4 *Copyright: (c) 2004 Lx-System
6 *eMail: info@lx-system.de
8 *Shop: xt:Commerce 3.04
9 *ERP: Lx-Office ERP 2.4.0
10 ***************************************************************/
11 require_once "shoplib.php";
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");
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) {
46 createCategoryLang($id,$shop,$name);
49 return ($rc !== -99)?$id:false;
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);
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");
68 $maingroup=$rs[0]["categories_id"];
73 } while ($rs and $found and $i<count($tmp));
74 for (;$i<count($tmp); $i++) {
75 $maingroup=createCategory($tmp[$i],$maingroup,$Lang,$Languages);
80 /*******************************************
81 * getCategoryLang($name,$Lang,$defLang,$Languages
83 *******************************************/
84 function getCategoryLang($name,$Lang,$defLang,$Languages) {
85 if (empty($name)) $name="Default";
86 $tmp=split("!",$name);
87 $tmpname=$tmp[count($tmp)-1];
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");
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);
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; };
115 if (!function_exists("imagick_readimage")) { echo "Imagick-Extention nicht installiert"; return false; };
116 $handle=imagick_readimage("./tmp/tmp.file_org");
118 $reason = imagick_failedreason( $handle ) ;
119 print "Lesen: $reason<BR>\n" ; flush();
122 if (!imagick_resize( $handle, $width, $height, IMAGICK_FILTER_UNKNOWN, 0)) {
123 $reason = imagick_failedreason( $handle ) ;
124 print "Resize: $reason<BR>\n" ; flush();
127 if (!imagick_writeimage( $handle,"./tmp/tmp.file_$dest")) {
128 $reason = imagick_failedreason( $handle ) ;
129 print "Schreiben: $reason<BR>\n" ; flush();
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");
145 if ($ERPftphost=="localhost")
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>"; };
152 echo "[Downloadfehler: $ERPimgdir/$image nicht gefunden]";
156 $conn_id = ftp_connect($ERPftphost);
157 if ($conn_id==false) {
158 echo "[Kein FTP-Verbindung ERP]";
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>";};
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;
179 $rc3=@exec("cp ./tmp/tmp.file_$key $src",$aus,$rc2);
181 if ($rc2>0) { $ok=false; echo "[Uploadfehler: $src]"; };
183 echo "[Uploadfehler: $val nicht gefunden]";
188 $conn_id = ftp_connect($SHOPftphost);
189 if ($conn_id==false) {
190 echo "[Kein FTP-Verbindung Shop]";
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>";};
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;
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;
230 updartikel($data,$rs[0]["products_id"],$defLang);
231 return $rs[0]["products_id"];
234 /*******************************************
237 *******************************************/
238 function updartikel($data,$id,$defLang) {
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");
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);
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);
263 $rc=query("shop",$sql,"updartikel_4");
264 if ($rc === -99) return false;
267 /*******************************************
268 * personal_offer ($personal_offer,$products_id)
269 * Spezialangebote anlegen
270 *******************************************/
271 function personal_offer ($personal_offer,$products_id) {
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");
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"];
282 $sql="delete from personal_offers_by_customers_status_$KDGrp where price_id = ".$rs[0]["price_id"];
285 $rc=query("shop",$sql,"personal_offer_2");
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;
298 /*******************************************
299 * chkartikellang($data,$Lang)
300 * Gibt es den Artikel und hat er sich geändert
301 *******************************************/
302 function chkartikellang($data,$Lang) {
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");
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");
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;
321 echo $rs[0]["products_id"]."<br>\n";
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"];
335 $data["picname"]=(strrpos($nopic,"/")>0)?substr($nopic,strrpos($nopic,"/")+1):$nopic;
336 $data["image"]=$nopic;
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"];
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"]);
347 $id=insartikel($data,$defLang);
348 if ($data["image"]) uploadImage($data["image"],$id);
353 /*******************************************
355 *******************************************/
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);
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"];
367 /*******************************************
369 *******************************************/
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");
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;
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");
388 $GeoZone=$rs[0]["geo_zone_id"];
390 echo "Steuerzone nicht gefunden";
395 $sql="select * from tax_rates where tax_zone_id=$GeoZone";
396 $rs=getAll("shop",$sql,"tax_rates");
398 foreach ($rs as $zeile) {
399 $tax[$zeile["tax_rate"]]=$zeile["tax_class_id"];
405 /*******************************************
407 *******************************************/
408 if (empty($Language) || !$Language) {
409 echo "Keine Sprachzuordnung definiert!";
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");
419 $ShopdefaultLang=$rs[0]["languages_id"];
420 if ($SHOPdefaultlang<>$ShopdefaultLang) {
421 echo "Defaultsprache im Shop wurde geändert ($SHOPdefaultlang<>$ShopdefaultLang)";
425 echo "Keine Defaultsprache im Shop eingestellt.";
430 foreach ($Language as $Langrow) {
431 if ($Langrow["SHOP"]>0 and $Langrow["ERP"]>0) $Languages[$Langrow["ERP"]]=$Langrow["SHOP"];
434 /*******************************************
436 *******************************************/
437 $artikel=shopartikel(); //array_keys($Languages));
439 echo "Artikelexport ERP -> xt:Commerce (Standardsprache $ShopdefaultLang): ".count($artikel)." Artikel markiert.<br>";
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);
448 foreach ($Languages as $erplang=>$shoplang) { //Mit jeder weiteren Sprache
449 $artikel=shopartikellang($erplang,$SpracheAlle);
450 echo "Shopsprache: $shoplang<br>";
452 foreach ($artikel as $data) {
454 $data["categories_id"]=getCategory($data["partsgroup"],$shoplang,$Languages);
456 if ($data["translation"]=="") $data["translation"]=$data["description"];
457 if ($data["longdescription"]=="") $data["longdescription"]=$data["notes"];
459 chkartikellang($data,$shoplang);
465 $log=fopen("tmp/shop.log","a");
466 fputs($log,$nun.": Fehler\n");
469 require ("diff.php");