xtcom/xtcomexport.php

   1 <?
   2 /***************************************************************
   3 *Author: Holger Lindemann
   4 *Copyright: (c) 2004 Lx-System
   5 *License: non free
   6 *eMail: info@lx-system.de
   7 *Version: 1.6
   8 *Shop: xt::Commerce
   9 *ERP: kivitendo ERP 3.0.0
  10 ***************************************************************/
  11 /*
  12 * Noch einzubauen:
  13 */
  14 //echo <<<EOF
  15 echo "<html>";
  16 echo "  <head><title>Lx-ERP Export der Shopartikel</title>";
  17 echo '  <link type="text/css" REL="stylesheet" HREF="css/main.css"></link>';
  18 echo "<body>";
  19 //EOF;
  20
  21 define("ExportMode","2");
  22 require_once "shoplib.php";
  23 /**********************************************
  24 * getAttribut($oid,$pid)
  25 *
  26 **********************************************/
  27 function getAttribut($oid,$pid) {
  28         $sql="select * from orders_products_attributes where orders_id=$oid and orders_products_id=$pid";
  29         $rs=getAll("shop",$sql,"getAttribut");
  30         $txt="";
  31         foreach ($rs as $zeile) {
  32                 $txt.="\n - ".$zeile["products_options"].":".$zeile["products_options_values"];
  33         };
  34         return $txt;
  35 }
  36
  37 /**********************************************
  38 * getBrutto($id)
  39 *
  40 **********************************************/
  41 function getBrutto($id) {
  42         $sql="select * from orders_total where orders_id=$id and class='ot_total'";
  43         $rs=getAll("shop",$sql,"getBrutto");
  44         return $rs[0]["value"];
  45 }
  46
  47 /**********************************************
  48 * getMwst($id)
  49 *
  50 **********************************************/
  51 function getMwst($id) {
  52         $sql="select * from orders_total where orders_id=$id and class='ot_tax'";
  53         $rs=getAll("shop",$sql,"getMwst");
  54         $mwst=0;
  55         if ($rs) {
  56                 foreach ($rs as $zeile) {
  57                         $mwst+=$zeile["value"];
  58                 }
  59         }
  60         return $mwst;
  61 }
  62
  63 /**********************************************
  64 * getSonderkosten($id,$art)
  65 *
  66 **********************************************/
  67 function getSonderkosten($id,$art) {
  68         $sql="select * from orders_total where orders_id=$id and class='".$GLOBALS["skosten"][$art]."'";
  69         $rs=getAll("shop",$sql,"getSonderkosten");
  70         if ($rs[0]["value"]) {
  71                 $kosten=round($rs[0]["value"]/(100+$GLOBALS["versand"]["TAX"])*100,2);
  72         } else {
  73                 $kosten=false;
  74         }
  75         return $kosten;
  76 }
  77
  78 /**********************************************
  79 * insBestArtikel($zeile,$transID)
  80 *
  81 **********************************************/
  82 function insBestArtikel($ordersID,$transID) {
  83 global $div07,$div16;
  84         $sql="select * from orders_products where orders_id=$ordersID";
  85         $rs=getAll("shop",$sql,"insBestArtikel");
  86         $ok=true;
  87         if ($rs) foreach ($rs as $zeile) {
  88                 $sql="select * from parts where partnumber='".$zeile["products_model"]."'";
  89                 $rs2=getAll("erp",$sql,"insBestArtikel");
  90                 if ( $rs2[0]["id"]) {$artID=$rs2[0]["id"]; $artNr=$rs2[0]["partnumber"]; }
  91                 else {
  92                         if ($zeile["products_tax"]=="19.0000") {
  93                                 $artID=$div16["ID"];
  94                                 $artNr=$div16["NR"];
  95                         } else {
  96                                 $artID=$div07["ID"];
  97                                 $artNr=$div07["NR"];
  98                         };
  99                 }
 100                 $preis=round($zeile["products_price"]/(100+$zeile["products_tax"])*100,2);
 101                 $text=getAttribut($ordersID,$zeile["orders_products_id"]);
 102                 $sql="insert into orderitems (trans_id, parts_id, description, qty, sellprice, unit, ship, discount) values (";
 103                 $sql.=$transID.",".$artID.",'".$zeile["products_name"].$text."',".$zeile["products_quantity"].",".$preis.",'Stck',0,0)";
 104                 echo " - Artikel:[ BuNr.:$artID ArtNr:<b>$artNr</b> ".$zeile["products_name"]." ]<br>";
 105                 $rc=query("erp",$sql,"insBestArtikel");
 106                 if ($rc === -99) { $ok=false; break; };
 107         }
 108         return $ok;
 109 }
 110
 111 function insAuftrag($data) {
 112 global $ERPusr,$versand,$nachn,$minder,$paypal,$auftrnr;
 113         $Zahlmethode=array("authorizenet"=>"Authorize.net","banktransfer"=>"Lastschriftverfahren","cc"=>"Kreditkarte",
 114                 "cod"=>"Nachnahme","eustandardtransfer"=>"EU-Standard Bank Transfer","iclear"=>"iclear Rechnungskauf",
 115                 "invoice"=>"Rechnung","ipayment"=>"iPayment","liberecobanktransfer"=>"Lastschriftverfahren",
 116                 "liberecocc"=>"Kreditkarte","moneybookers"=>"Moneybookers.com","moneyorder"=>"Scheck/Vorkasse",
 117                 "nochex"=>"NOCHEX","paypal"=>"PayPal","pm2checkout"=>"2CheckOut","psigate"=>"PSiGate",
 118                 "qenta"=>"qenta.at","secpay"=>"SECPay");
 119         $brutto=getBrutto($data["orders_id"]);
 120         $mwst=getMwst($data["orders_id"]);
 121         $netto=$brutto-$mwst;
 122         $versandK=getSonderkosten($data["orders_id"],"Versand");
 123         $nachnK  =getSonderkosten($data["orders_id"],"NachName");
 124         $mindermK=getSonderkosten($data["orders_id"],"Minder");
 125         $paypalK =getSonderkosten($data["orders_id"],"Paypal");
 126         // Hier beginnt die Transaktion
 127         $rc=query("erp","BEGIN WORK","insAuftrag");
 128         if ($rc === -99) { echo "Probleme mit Transaktion. Abbruch!"; exit(); };
 129         if ($auftrnr) {
 130                 $auftrag=$GLOBALS["preA"].getNextAnr();
 131         } else {
 132                 $auftrag=$GLOBALS["preA"].$data["orders_id"];
 133         }
 134         $sql="select count(*) as cnt from oe where ordnumber = '$auftrag'";
 135         $rs=getAll("erp",$sql,"insAuftrag 1");
 136         if ($rs[0]["cnt"]>0) {
 137                 $auftrag=$GLOBALS["preA"].getNextAnr();
 138         }
 139         $newID=uniqid (rand());
 140         $sql="insert into oe (notes,ordnumber,cusordnumber) values ('$newID','$auftrag','".$data["kdnr"]."')";
 141         $rc=query("erp",$sql,"insAuftrag 2");
 142         if ($rc === -99) {
 143                 echo "Auftrag ".$data["orders_id"]." konnte nicht angelegt werden.<br>";
 144                 $rc=query("erp","ROLLBACK WORK","chkKunde");
 145                 echo "Auftrag ".$data["orders_id"]." konnte nicht angelegt werden.<br>";
 146                 return false;
 147         }
 148         $sql="select * from oe where notes = '$newID'";
 149         $rs2=getAll("erp",$sql,"insAuftrag 3");
 150         if (!$rs2>0) {
 151                 echo "Auftrag ".$data["orders_id"]." konnte nicht angelegt werden.<br>";
 152                 $rc=query("erp","ROLLBACK WORK","chkKunde");
 153                 echo "Auftrag ".$data["orders_id"]." konnte nicht angelegt werden.<br>";
 154                 return false;
 155         }
 156         $BEZAHLEN=$Zahlmethode[$data["payment_method"]]."\n";
 157         if ($data["cc_type"]) {
 158                 $BEZAHLEN.=$data["cc_type"]."\n".$data["cc_owner"]."\n".$data["cc_number"]."\n".$data["cc_expires"]."\n";
 159         } else if ($data["banktransfer_blz"]) {
 160                 $BEZAHLEN.="Kontoinhaber: ".$data["banktransfer_owner"]."\nBanknummer: ".$data["banktransfer_blz"];
 161                 $BEZAHLEN.="\nBank: ".$data["banktransfer_bankname"]."\nKontonummer: ".$data["banktransfer_number"]."\n";
 162         }
 163         $sql="update oe set cusordnumber=".$data["orders_id"].", transdate='".$data["date_purchased"]."', customer_id=".$data["kdnr"].", ";
 164         $sql.="amount=".$brutto.", netamount=".$netto.", reqdate='".$data["date_purchased"]."', taxincluded='f', ";
 165         if ($data["shipto"]>0) $sql.="shipto_id=".$data["shipto"].", ";
 166         $sql.="intnotes='".$data["comments"]."',notes='".$BEZAHLEN."', curr='EUR',employee_id=".$ERPusr["ID"].", vendor_id=0 ";
 167         $sql.="where id=".$rs2[0]["id"];
 168         $rc=query("erp",$sql,"insAuftrag 4");
 169         if ($rc === -99) {
 170                 echo "Auftrag ".$data["orders_id"]." konnte nicht angelegt werden.<br>";
 171                 $rc=query("erp","ROLLBACK WORK","chkKunde");
 172                 if ($rc === -99) { echo "Probleme mit Transaktion. Abbruch!"; exit(); };
 173                 return false;
 174         }
 175         echo "Auftrag:[ Buchungsnummer:".$rs2[0]["id"]." AuftrNr:<b>".$auftrag."</b> ]<br>";
 176         if (!insBestArtikel($data["orders_id"],$rs2[0]["id"])) {
 177                 echo "Auftrag ".$data["orders_id"]." konnte nicht angelegt werden.<br>";
 178                 $rc=query("erp","ROLLBACK WORK","chkKunde");
 179                 if ($rc === -99) { echo "Probleme mit Transaktion. Abbruch!"; exit(); };
 180                 return false;
 181         };
 182         if ($versandK) {
 183                 $sql="insert into orderitems (trans_id, parts_id, description, qty, sellprice, unit, ship, discount) values (";
 184                 $sql.=$rs2[0]["id"].",".$versand["ID"].",'".$versand["TXT"]."',1,".$versandK.",'Stck',0,0)";
 185                 $rc=query("erp",$sql,"insAuftrag 8");
 186                 if ($rc === -99) echo "Auftrag $auftrag : Fehler bei den Versandkosten<br>";
 187         }
 188         if ($nachnK) {
 189                 $sql="insert into orderitems (trans_id, parts_id, description, qty, sellprice, unit, ship, discount) values (";
 190                 $sql.=$rs2[0]["id"].",".$nachn["ID"].",'".$nachn["TXT"]."',1,".$nachnK.",'Stck',0,0)";
 191                 $rc=query("erp",$sql,"insAuftrag 9");
 192                 if ($rc === -99) echo "Auftrag $auftrag : Fehler bei den Nachnamekosten<br>";
 193         }
 194         if ($mindermK) {
 195                 $sql="insert into orderitems (trans_id, parts_id, description, qty, sellprice, unit, ship, discount) values (";
 196                 $sql.=$rs2[0]["id"].",".$minder["ID"].",'".$minder["TXT"]."',1,".$mindermK.",'Stck',0,0)";
 197                 $rc=query("erp",$sql,"insAuftrag 10");
 198                 if ($rc === -99) echo "Auftrag $auftrag : Fehler beim Mindermengenzuschlag<br>";
 199         }
 200         if ($paypalK) {
 201                 $sql="insert into orderitems (trans_id, parts_id, description, qty, sellprice, unit, ship, discount) values (";
 202                 $sql.=$rs2[0]["id"].",".$paypal["ID"].",'".$paypal["TXT"]."',1,".$paypalK.",'Stck',0,0)";
 203                 $rc=query("erp",$sql,"insAuftrag 11");
 204                 if ($rc === -99) echo "Auftrag $auftrag : Fehler bei den PayPal-Kosten<br>";
 205         }
 206         $sql="update orders set orders_status ='3' WHERE orders_id =".$data["orders_id"];
 207         $rc=query("shop",$sql,"insBestArtikel 12");
 208         if ($rc === -99) echo "Bestellung im Shop nicht geschlossen";
 209         $rc=query("erp","COMMIT WORK","chkKunde");
 210         if ($rc === -99) { echo "Probleme mit Transaktion. Abbruch!"; exit(); };
 211         return true;
 212 }
 213
 214 /**********************************************
 215 * getBestellung()
 216 *
 217 **********************************************/
 218 function getBestellung() {
 219         $sql="select b.*,h.comments,o.*,cn.kdnr from orders o left join orders_status_history h on h.orders_id=o.orders_id ";
 220         $sql.="left join banktransfer b on b.orders_id =o.orders_id left join customers_number cn on ";
 221         $sql.="cn.customers_id=o.customers_id where o.orders_status=1 order by o.orders_id";
 222         $rs=getAll("shop",$sql,"getBestellung");
 223         return $rs;
 224 }
 225
 226 /**********************************************
 227 * chkKdData()
 228 *
 229 **********************************************/
 230 function chkKunden() {
 231         $felder=array("firstname","lastname","company","street_address","city","postcode","country");
 232         foreach ($GLOBALS["bestellungen"] as $bestellung) {
 233                 $rc=query("erp","BEGIN WORK","chkKunden");
 234                 if ($rc === -99) { echo "Probleme mit Transaktion. Abbruch!"; exit(); };
 235                 if ($bestellung["kdnr"]>0) { // Bestandskunde; kdnr == ID in customers
 236                         $msg="update ";
 237                         $kdnr=chkOldKd($bestellung);
 238                         if ($kdnr == -1) { //Kunde nicht gefunden, neu anlegen.
 239                                 $msg="insert ";
 240                                 $kdnr=insNewKd($bestellung);
 241                                 $GLOBALS["neuKd"]++;
 242                         } else if (!$kdnr) {
 243                                 echo $msg." ".$bestellung["customers_name"]." fehlgeschlagen!<br>";
 244                                 $GLOBALS["gesKd"]++;
 245                                 continue;
 246                         }
 247                 } else { // Neukunde
 248                         $msg="insert ";
 249                         $kdnr=insNewKd($bestellung);
 250                         $GLOBALS["neuKd"]++;
 251                 }
 252                 echo $bestellung["customers_company"]." ".$bestellung["customers_name"]." $kdnr<br>";
 253                 $GLOBALS["bestellungen"][$GLOBALS["gesKd"]]["kdnr"]=$kdnr;
 254                 $sql="delete from customers_number where customers_id=".$bestellung["customers_id"];
 255                 $rc=query("shop",$sql,"chkKunde");
 256                 $sql="insert into customers_number (customers_id,kdnr) values(".$bestellung["customers_id"].",".$kdnr.")";
 257                 $rc=query("shop",$sql,"chkKunde");
 258                 if ($kdnr>0) {
 259                         foreach($felder as $feld) {
 260                                 if ($bestellung["delivery_$feld"]<>$bestellung["customers_$feld"]) {
 261                                         $rc=insShData($bestellung,$kdnr);
 262                                         if ($rc>0) $GLOBALS["bestellungen"][$GLOBALS["gesKd"]]["shipto"]=$rc;
 263                                         break;
 264                                 }
 265                         }
 266                 }
 267                 if (!$kdnr || $rc === -99) {
 268                         echo $msg." ".$bestellung["customers_name"]." fehlgeschlagen! ($kdnr,$rc)<br>";
 269                         $rc=query("erp","ROLLBACK WORK","chkKunde");
 270                         if ($rc === -99) { echo "Probleme mit Transaktion. Abbruch!"; exit(); };
 271                 } else {
 272                         $rc=query("erp","COMMIT WORK","chkKunde");
 273                         if ($rc === -99) { echo "Probleme mit Transaktion. Abbruch!"; exit(); };
 274                 }               $GLOBALS["gesKd"]++;
 275         }
 276         return true;
 277 }
 278
 279 function chkOldKd($data) {
 280         $sql="select * from customer where id = ".$data["kdnr"];
 281         $rs=getAll("erp",$sql,"chkKdData");
 282         if (!$rs || $rs[0]["id"]<>$data["kdnr"]) { return -1; }; // Kunde nicht gefunden
 283         if ($rs[0]["zipcode"]<>$data["customers_postcode"]) $set.="zipcode='".$data["customers_postcode"]."',";
 284         if ($rs[0]["city"]<>$data["customers_city"]) $set.="city='".$data["customers_city"]."',";
 285         if (in_array($data["customers_country"],$GLOBALS["LAND"])) {
 286                 if ($rs[0]["country"]<>$GLOBALS["LAND"][$data["customers_country"]]) $set.="country='".$GLOBALS["LAND"][$data["customers_country"]]."',";
 287         } else {
 288                 if ($rs[0]["country"]<>$data["customers_country"]) $set.="country='".$data["customers_country"]."',";
 289         }
 290         if ($rs[0]["phone"]<>$data["customers_phone"])$set.="phone='".$data["customers_phone"]."',";
 291         if ($rs[0]["email"]<>$data["customers_email_address"])$set.="email='".$data["customers_mail_address"]."',";
 292         if ($data["customers_company"]) {
 293                 if ($rs[0]["name"]<>$data["customers_company"]) $set.="name='".$data["customers_company"]."',";
 294                 if ($rs[0]["contact"]<>$data["customers_name"]) $set.="contact='".$data["customers_name"]."',";
 295         } else {
 296                 if ($rs[0]["name"]<>$data["customers_name"]) $set.="name='".$data["customers_lastname"].", ".$data["customers_firstname"]."',";
 297         }
 298         if ($rs[0]["street"]<>$data["customers_street_address"]) $set.="street='".$data["customers_street_address"]."',";
 299         if ($set) {
 300                 $sql="update customer set ".substr($set,0,-1)." where id=".$rs[0]["id"];
 301                 $rc=query("erp",$sql,"chkKdData");
 302                 if ($rc === -99) {
 303                         return false;
 304                 } else {
 305                         return $data["kdnr"];
 306                 }
 307         } else {
 308                 return $data["kdnr"];
 309         }
 310 }
 311
 312 /**********************************************
 313 * insShData($data,$id)
 314 *
 315 **********************************************/
 316 function insShData($data,$id) {
 317         $set=$id;
 318         if ($data["delivery_company"]) { $set.=",'".$data["delivery_company"]."','".$data["delivery_name"]."',"; }
 319         else { $set.=",'".$data["delivery_name"]."','',"; }
 320         $set.="'".$data["delivery_street_address"]."',";
 321         $set.="'".$data["delivery_postcode"]."',";
 322         $set.="'".$data["delivery_city"]."',";
 323         if (in_array($data["delivery_country"],$GLOBALS["LAND"])) {
 324                 $set.="'".$GLOBALS["LAND"][$data["delivery_country"]]."',";
 325         } else {
 326                 $set.="'".$data["delivery_country"]."',";
 327         }
 328         $set.="'".$data["customers_telephone"]."',";
 329         $set.="'".$data["customers_email_address"]."'";
 330         $sql="insert into shipto (trans_id,shiptoname,shiptodepartment_1,shiptostreet,shiptozipcode,shiptocity,";
 331         $sql.="shiptocountry,shiptophone,shiptoemail,module) values ($set,'CT')";
 332         $rc=query("erp",$sql,"insShData");
 333         if ($rc === -99) return false;
 334         $sql="select shipto_id from shipto where trans_id = $id and module='CT' order by itime desc limit 1";
 335         $rs=getAll("erp",$sql,"insKdData");
 336         if ($rs[0]["shipto_id"]>0) {
 337                 $sid=$rs[0]["shipto_id"];
 338                 $sql="update customers_number set shipto = $sid where kdnr = $id";
 339                 $rc2=query("shop",$sql,"insShData");
 340                 if ($rc2 === -99) {
 341                         $sql="delete from shipto where shipto_id=$sid";
 342                         $rc=query("shop",$sql,"insShData");
 343                         return false;
 344                 }
 345                 return $sid;
 346         } else  {
 347                 echo "Fehler bei abweichender Anschrift ".$data["delivery_name"];
 348                 $sql="delete from shipto where shipto_id=$sid";
 349                 $rc=query("shop",$sql,"insShData");
 350                 return false;
 351         }
 352 }
 353
 354 /**********************************************
 355 * insKdData($BID)
 356 *
 357 **********************************************/
 358 function insNewKd($data) {
 359         $taxid=array("DE"=>0,"CH"=>2,"AU"=>1,"FR"=>1,"IT"=>1,"ES"=>1,"NL"=>1); // Muß erweitert werden
 360         $taxidL=array("Germany"=>0,"Switzerland"=>2,"Austria"=>1,"France"=>1,"Italy"=>1,"Spain"=>1,"Netherlands"=>1); // Muß erweitert werden
 361         $newID=uniqid(rand(time(),1));
 362         //Kundennummer generieren
 363         if ($GLOBALS["kdnum"]==1) { // von der ERP
 364                 $kdnr=$GLOBALS["preK"].getNextKnr();
 365         } else {                    // durch Shop
 366                 $kdnr=$GLOBALS["preK"].$data["customers_id"];
 367         }
 368         $sql="select count(*) as cnt from customer where customernumber = '$kdnr'";
 369         $rs=getAll("erp",$sql,"insKdData");
 370         if ($rs[0]["cnt"]>0) {  // Kundennummer gibt es schon, eine neue aus ERP
 371                 $kdnr=$GLOBALS["preK"].getNextKnr();
 372         }
 373         $sql="insert into customer (name,customernumber) values ('$newID','$kdnr')";
 374         $rc=query("erp",$sql,"insKdData");
 375         if ($rc === -99) return false;
 376         $sql="select * from customer where name = '$newID'";
 377         $rs=getAll("erp",$sql,"insKdData");
 378         if (!$rs) return false;
 379         if ($data["customers_company"]) {
 380                 $set.="set name='".$data["customers_company"]."',contact='".$data["customers_name"]."',";
 381         }else {
 382                 $set.="set name='".$data["customers_lastname"].", ".$data["customers_firstname"]."',";
 383                 $set.="contact='".$data["customers_name"]."',";
 384         }
 385         $set.="street='".$data["customers_street_address"]."',";
 386         $set.="zipcode='".$data["customers_postcode"]."',";
 387         $set.="city='".$data["customers_city"]."',";
 388         $set.="country='".$data["delivery_country_iso_code_2"]."',";
 389         $set.="phone='".$data["customers_telephone"]."',";
 390         $set.="email='".$data["customers_email_address"]."',";
 391         if ($data["delivery_country_iso_code_2"]) {
 392                 $tid=(in_array($data["delivery_country_iso_code_2"],$taxid))?$taxid[$data["delivery_country_iso_code_2"]]:0;
 393         } else {
 394                 $tid=(in_array($data["delivery_country"],$taxidL))?$taxidL[$data["delivery_country"]]:0;
 395         }
 396         if (!$tid) $tid=0;
 397         $set.="taxzone_id=$tid,";
 398         $set.="taxincluded='f' ";
 399         $sql="update customer ".$set;
 400         $sql.="where id=".$rs[0]["id"];
 401         $rc=query("erp",$sql,"insKdData");
 402         if ($rc === -99) {
 403                 $sql="delete from customer where id=".$rs[0]["id"];
 404                 $rc=query("shop",$sql,"insNewKd");
 405                 return false;
 406         } else { return $rs[0]["id"]; }
 407 }
 408
 409 $LAND=array("Germany"=>"D","Austria"=>"A","Switzerland"=>"CH");
 410 $skosten=array("Versand"=>"ot_shipping","NachName"=>"ot_cod_fee","Paypal"=>"ot_paypal","Minder"=>"ot_loworderfee");
 411 $bestellungen=getBestellung();
 412 $ok=count($bestellungen);
 413 $gesKd=0;
 414 $neuKd=0;
 415 if ($ok) {
 416         echo "Es liegen $ok Bestellungen vor. <br>";
 417         chkKunden();
 418         echo $gesKd." Kunde(n), davon ".$neuKd." neue(r) Kunde(n).<br>";
 419         foreach ($bestellungen as $bestellung) {
 420                 insAuftrag($bestellung);
 421         }
 422 } else { echo "Es liegen keine Bestellungen vor!<br>"; };
 423 ?>
 424 <!--a href='trans.php'>zur&uuml;ck</a-->
 425 </body>
 426 </html>