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
7 *eMail: info@lx-system.de
9 *Shop: xt:Commerce 3.04
10 *ERP: kivitendo ERP 3.0.0
11 ***************************************************************/
12 require_once "shoplib.php";
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");
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) {
47 createCategoryLang($id,$shop,$name);
50 return ($rc !== -99)?$id:false;
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);
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");
69 $maingroup=$rs[0]["categories_id"];
74 } while ($rs and $found and $i<count($tmp));
75 for (;$i<count($tmp); $i++) {
76 $maingroup=createCategory($tmp[$i],$maingroup,$Lang,$Languages);
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");
90 $reason = imagick_failedreason( $handle ) ;
91 print "Lesen: $reason<BR>\n" ; flush();
94 if (!imagick_resize( $handle, $width, $height, IMAGICK_FILTER_UNKNOWN, 0)) {
95 $reason = imagick_failedreason( $handle ) ;
96 print "Resize: $reason<BR>\n" ; flush();
99 if (!imagick_writeimage( $handle,"./tmp/tmp.file_$dest")) {
100 $reason = imagick_failedreason( $handle ) ;
101 print "Schreiben: $reason<BR>\n" ; flush();
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");
117 if ($ERPftphost=="localhost")
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>"; };
124 echo "[Downloadfehler: $ERPimgdir/$image nicht gefunden]";
128 $conn_id = ftp_connect($ERPftphost);
129 if ($conn_id==false) {
130 echo "[Kein FTP-Verbindung ERP]";
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>";};
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;
151 $rc3=@exec("cp ./tmp/tmp.file_$key $src",$aus,$rc2);
152 if ($rc2>0) { $ok=false; echo "[Uploadfehler: $src]"; };
154 echo "[Uploadfehler: $val nicht gefunden]";
159 $conn_id = ftp_connect($SHOPftphost);
160 if ($conn_id==false) {
161 echo "[Kein FTP-Verbindung Shop]";
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>";};
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;
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;
202 updartikel($data,$rs[0]["products_id"],$defLang);
203 return $rs[0]["products_id"];
206 /*******************************************
209 *******************************************/
210 function updartikel($data,$id,$defLang) {
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");
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);
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);
235 $rc=query("shop",$sql,"updartikel_4");
236 if ($rc === -99) { return false;
237 } else { return true; }
240 /*******************************************
241 * personal_offer ($personal_offer,$products_id)
242 * Spezialangebote anlegen
243 *******************************************/
244 function personal_offer ($personal_offer,$products_id) {
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");
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"];
255 $sql="delete from ".PREFIX."personal_offers_by_customers_status_$KDGrp where price_id = ".$rs[0]["price_id"];
258 $rc=query("shop",$sql,"personal_offer_2");
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;
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");
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;
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;
296 echo $rs[0]["products_id"]."<br>\n";
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"];
310 $data["picname"]=(strrpos($nopic,"/")>0)?substr($nopic,strrpos($nopic,"/")+1):$nopic;
311 $data["image"]=$nopic;
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"];
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"]);
322 $id=insartikel($data,$defLang);
323 if ($data["image"]) uploadImage($data["image"],$id);
328 /*******************************************
330 *******************************************/
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);
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];
342 /*******************************************
344 *******************************************/
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");
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;
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");
363 $GeoZone=$rs[0]["geo_zone_id"];
365 echo "Steuerzone nicht gefunden";
370 $sql="select * from ".PREFIX."tax_rates where tax_zone_id=$GeoZone";
371 $rs=getAll("shop",$sql,"tax_rates");
373 foreach ($rs as $zeile) {
374 $tax[$zeile["tax_rate"]]=$zeile["tax_class_id"];
380 /*******************************************
382 *******************************************/
383 if (empty($Language) || !$Language) {
384 echo "Keine Sprachzuordnung definiert!";
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");
394 $ShopdefaultLang=$rs[0]["languages_id"];
395 if ($SHOPdefaultlang<>$ShopdefaultLang) {
396 echo "Defaultsprache im Shop wurde geändert";
400 echo "Keine Defaultsprache im Shop eingestellt.";
405 foreach ($Language as $Langrow) {
406 if ($Langrow["SHOP"]>0 and $Langrow["ERP"]>0) $Languages[$Langrow["ERP"]]=$Langrow["SHOP"];
409 /*******************************************
411 *******************************************/
412 $artikel=shopartikel(); //array_keys($Languages));
414 echo "Artikelexport ERP -> xt:Commerce (Standardsprache $ShopdefaultLang): ".count($artikel)." Artikel markiert.<br>";
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);
423 foreach ($Languages as $erplang=>$shoplang) { //Mit jeder weiteren Sprache
424 $artikel=shopartikellang($erplang,$SpracheAlle);
425 echo "Shopsprache: $shoplang<br>";
427 foreach ($artikel as $data) {
429 $data["categories_id"]=getCategory($data["partsgroup"],$shoplang,$Languages);
431 if ($data["translation"]=="") $data["translation"]=$data["description"];
432 if ($data["longdescription"]=="") $data["longdescription"]=$data["notes"];
434 chkartikellang($data,$shoplang);
439 if ($debug) writelog("Fehler\n");