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