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