--- /dev/null
+<?php
+
+$login=$_GET["login"];
+$debug=false;
+require_once "DB.php";
+require_once "conf$login.php";
+
+if ($dbprefix<>"") { define("PREFIX",$dbprefix."_"); } else { define("PREFIX",""); };
+
+$landarray=array("DEUTSCHLAND"=>"D","STEREICH"=>"A","OESTEREICH"=>"A","SCHWEIZ"=>"CH");
+$taxarray=array("D"=>0,"A"=>1,"CH"=>2);
+$defaultland="D";
+$taxid=0;
+$log=false;
+$erp=false;
+$shop=false;
+
+
+$ERPdns= array('phptype' => 'pgsql',
+ 'username' => $ERPuser,
+ 'password' => $ERPpass,
+ 'hostspec' => $ERPhost,
+ 'database' => $ERPdbname,
+ 'port' => $ERPport);
+
+$SHOPdns=array('phptype' => 'mysql',
+ 'username' => $SHOPuser,
+ 'password' => $SHOPpass,
+ 'hostspec' => $SHOPhost,
+ 'database' => $SHOPdbname,
+ 'port' => $SHOPport);
+
+/****************************************************
+* Debugmeldungen in File schreiben
+****************************************************/
+if ($debug) { $log=fopen("tmp/shop.log","a"); } // zum Debuggen
+else { $log=false; };
+
+/****************************************************
+* Shopverbindung aufbauen
+****************************************************/
+$shop=DB::connect($SHOPdns);
+if (!$shop) shopFehler("",$shop->getDebugInfo());
+if (DB::isError($shop)) {
+ $nun=date("Y-m-d H:i:s");
+ if ($log) fputs($log,$nun.": Shop-Connect\n");
+ shopFehler("",$shop->getDebugInfo());
+ die ($shop->getDebugInfo());
+};
+
+/****************************************************
+* ERPverbindung aufbauen
+****************************************************/
+$erp=DB::connect($ERPdns);
+if (!$erp) shopFehler("",$erp->getDebugInfo());
+if (DB::isError($erp)) {
+ $nun=date("Y-m-d H:i:s");
+ if ($log) fputs($log,$nun.": ERP-Connect\n");
+ shopFehler("",$erp->getDebugInfo());
+ die ($erp->getDebugInfo());
+} else {
+ if ($erp->autocommit) $erp->autocommit();
+};
+
+/****************************************************
+* SQL-Befehle absetzen
+****************************************************/
+function query($db,$sql,$function="--") {
+ $nun=date("d.m.y H:i:s");
+ if ($GLOBALS["log"]) fputs($GLOBALS["log"],$nun.": ".$function."\n".$sql."\n");
+ $rc=$GLOBALS[$db]->query($sql);
+ if ($GLOBALS["log"]) fputs($GLOBALS["log"],print_r($rc,true)."\n");
+ if ($rc!==1) {
+ return -99;
+ } else {
+ return true;
+ }
+}
+
+/****************************************************
+* Datenbank abfragen
+****************************************************/
+function getAll($db,$sql,$function="--") {
+ $nun=date("d.m.y H:i:s");
+ if ($GLOBALS["log"]) fputs($GLOBALS["log"],$nun.": ".$function."\n".$sql."\n");
+ $rs=$GLOBALS[$db]->getAll($sql,DB_FETCHMODE_ASSOC);
+ if ($rs["message"]<>"") {
+ if ($GLOBALS["log"]) fputs($GLOBALS["log"],print_r($rs,true)."\n");
+ return false;
+ } else {
+ return $rs;
+ }
+}
+
+/****************************************************
+* shopFehler
+* in: sql,err = string
+* out:
+* Fehlermeldungen ausgeben
+*****************************************************/
+function shopFehler($sql,$err) {
+global $showErr;
+ if ($showErr)
+ echo "</td></tr></table><font color='red'>$sql : $err</font><br>";
+}
+
+/****************************************************
+* Nächste Auftragsnummer (ERP) holen
+****************************************************/
+function getNextAnr() {
+ $sql="select * from defaults";
+ $sql1="update defaults set sonumber=";
+ if ($rc === -99) {
+ echo "Kann keine Auftragsnummer erzeugen - Abbruch";
+ $rc=query("erp","ROLLBACK","getNextKnr");
+ exit();
+ }
+ $rs2=getAll("erp",$sql,"getNextAnr");
+ $auftrag=$rs2[0]["sonumber"]+1;
+ $rc=query("erp",$sql1.$auftrag,"getNextAnr");
+ if ($rc === -99) {
+ echo "Kann keine Auftragsnummer erzeugen - Abbruch";
+ $rc=query("erp","ROLLBACK","getNextKnr");
+ exit();
+ }
+ return $auftrag;
+}
+
+/****************************************************
+* Nächste Kundennummer (ERP) holen
+****************************************************/
+function getNextKnr() {
+ $sql="select * from defaults";
+ $sql1="update defaults set customernumber='";
+ if ($rc === -99) {
+ echo "Kann keine Kundennummer erzeugen - Abbruch";
+ exit();
+ }
+ $rs2=getAll("erp",$sql,"getNextKnr");
+ $kdnr=$rs2[0]["customernumber"]+1;
+ $rc=query("erp",$sql1.$kdnr."'","getNextKnr");
+ if ($rc === -99) {
+ echo "Kann keine Kundennummer erzeugen - Abbruch";
+ $rc=query("erp","ROLLBACK","getNextKnr");
+ exit();
+ }
+ return $kdnr;
+}
+
+
+//$shopdata=array("firma"=>"","abteilung"=>"","vorname"=>"","nachname"=>"","strasse"=>"","plz"=>"","ort"=>"","telefon"=>"","email"=>"","land"=>"","fax"=>"","notiz"=>"","postfach"=>"")
+$shopdata=array( "id"=>"customers_id","kdnr"=>"customers_cid","bid"=>"orders_id", "anrede"=>" customers_gender",
+ "firma"=>"customers_company", "nachname"=>"customers_lastname", "vorname"=>"customers_firstname",
+ "strasse"=>"customers_street_address","plz"=>"customers_postcode","ort"=>"customers_city","land"=>"customers_country",
+ "telefon"=>"customers_phone","email"=>"customers_email_address","fax"=>"Fax","notiz"=>"comments",
+
+ "netto"=>"ot_subtotal","steuer"=>"ot_tax","datum"=>" date_purchased","bemerkung"=>"comments",
+ "artnr"=>"products_id","preis"=>"final_price","artikeltxt"=>" products_name","menge"=>" products_quantity");
+
+$shopartikel=array( "id"=>"Artikel_ID","artnr"=>"Artikel_Nr","arttxt"=>"Name","artbeschr"=>"Beschreibung","gruppe"=>"Kategorie_ID",
+ "preis"=>"Preis","preis2"=>"Haendlerpreis","preis3"=>"Aktionspreis","gewicht"=>"Gewicht",
+ "bild"=>"Bild_gross","bestand"=>"Lagerbestand","minbestand"=>"Mindestlagermenge","steuer"=>"MwSt_Satz");
+
+
+/****************************************************
+* Ab hier Artikelexport aus ERP
+****************************************************/
+// Ab hier Artikelexport aus ERP nur eine Sprache
+function shopartikellang($lang,$alle) {
+ $sql="SELECT P.partnumber,L.translation,P.description,L.longdescription,P.notes,PG.partsgroup ";
+ $sql.="FROM parts P left join translation L on L.parts_id=P.id left join partsgroup PG on PG.id=P.partsgroup_id ";
+ $sql.="WHERE P.shop='t' and (L.language_id = $lang";
+ if ($alle) {
+ $sql.=" or L.language_id is Null)";
+ } else { $sql.=")"; };
+ $rs=getAll("erp",$sql,"shopartikellang");
+ $data=array();
+ if ($rs) foreach ($rs as $row) {
+ if (!$data[$row["partnumber"]]) $data[$row["partnumber"]]=$row;
+ }
+ return $data;
+}
+// Ab hier alle Artikelexport aus ERP Defaultsprache
+function shopartikel() {
+global $stdprice,$altprice;
+ if ($stdprice>0) {
+ $sql="SELECT P.partnumber,P.description,P.weight,(t.rate * 100) as rate,G.price as sellprice,P.sellprice as stdprice, ";
+ $sql.="PG.partsgroup,P.notes,P.image,P.onhand,G.pricegroup_id,P.buchungsgruppen_id as bugru FROM ";
+ $sql.="chart c left join tax t on c.taxkey_id=t.taxkey, parts P left join partsgroup PG on ";
+ $sql.="PG.id=P.partsgroup_id left join prices G on G.parts_id=P.id ";
+ $sql.="buchungsgruppen B on P.buchungsgruppen_id = B.id ";
+ $sql.="where P.shop='t' and c.id=p.income_accno_id and ";
+ $sql.="(G.pricegroup_id=$stdprice or pricegroup_id=$altprice or G.pricegroup_id is null) ";
+ $sql.="order by P.partnumber";
+ } else {
+ $sql="SELECT P.partnumber,P.description,P.weight,(t.rate * 100) as rate,P.sellprice,PG.partsgroup,";
+ $sql.="P.notes,P.image,P.onhand,P.buchungsgruppen_id as bugru FROM ";
+ $sql.="chart c left join tax t on c.taxkey_id=t.taxkey, parts P left join partsgroup PG on ";
+ $sql.="PG.id=P.partsgroup_id left join buchungsgruppen B on P.buchungsgruppen_id = B.id ";
+ $sql.="WHERE P.shop='t' and c.id=B.income_accno_id_0";
+ }
+ $rs=getAll("erp",$sql,"shopartikel");
+ $i=0;
+ $data=array();
+ if ($rs) foreach ($rs as $row) {
+ if (!$data[$row["partnumber"]]) $data[$row["partnumber"]]=$row;
+ if ($row["pricegroup_id"]==$altprice) {
+ $data[$row["partnumber"]]["altprice"]=($row["sellprice"])?$row["sellprice"]:$row["stdprice"];
+ } else {
+ $data[$row["partnumber"]]["sellprice"]=($row["sellprice"])?$row["sellprice"]:$row["stdprice"];
+ }
+ $i++;
+ }
+ return $data;
+}
+
+/****************************************************
+* Artikelexport in ERP importieren
+****************************************************/
+function insertArtikel($data) {
+global $shopartikel;
+ foreach ($data as $row) {
+ $pg=$GLOBALS["warengruppen"][$row[$shopartikel["gruppe"]]]["partsgroup"];
+ $bg=$GLOBALS["buchungsgruppen"][sprintf("%0.2f",$row[$shopartikel["steuer"]])];
+ $artnr=($row[$shopartikel["artnr"]])?$row[$shopartikel["artnr"]]:getArtnr();
+ $sqltmp="insert into parts (partnumber,description,notes,weight,onhand,rop,image,sellprice,unit,partsgroup_id,buchungsgruppen_id) ";
+ $sqltmp.="values ('%s','%s','%s',%0.5f,%0.5f,%0.5f,'%s',%0.5f,'%s',%d,%d)";
+ $sql=sprintf($sqltmp,$artnr,$row[$shopartikel["arttxt"]],$row[$shopartikel["artbeschr"]],
+ $row[$shopartikel["gewicht"]],$row[$shopartikel["bestand"]],$row[$shopartikel["minbestand"]],
+ $row[$shopartikel["bild"]],$row[$shopartikel["preis"]],$row[$shopartikel["einheit"]],$pg,$bg);
+ $rc=query("erp",$sql,"insertArtikel");
+ if ($rc === -99) {
+ echo $row[$shopartikel["id"]]." ".$row[$shopartikel["arttxt"]]." nicht importiert<br>";
+ } else {
+ echo "";
+ }
+ echo $sql."<br>";
+ }
+}
+
+/****************************************************
+* Nächste Artikelnummer (ERP) holen
+****************************************************/
+function getArtnr() {
+ $sql="select * from defaults";
+ $sql1="update defaults set articlenumber='";
+ $rc=query("erp","BEGIN","getArtnr");
+ if ($rc === -99) {
+ echo "Kann keine Artikelnummer erzeugen - Abbruch";
+ exit();
+ }
+ $rs2=getAll("erp",$sql,"getArtnr");
+ $artnr=$rs2[0]["articelnumber"]+1;
+ $rc=query("erp",$sql1.$artnr."'","getArtnr");
+ if ($rc === -99) {
+ echo "Kann keine Artikelnummer erzeugen - Abbruch";
+ $rc=query("erp","ROLLBACK","getArtnr");
+ exit();
+ }
+ $rc=query("erp","COMMIT","getArtnr");
+ return $artnr;
+}
+
+$buchungsgruppen=array();
+$warengruppen=array();
+
+function getBugru() {
+ $sql ="select B.id,tax.rate from buchungsgruppen B left join chart on income_accno_id_0=chart.id left join taxkeys T on ";
+ $sql.="T.chart_id=income_accno_id_0 left join tax on tax.id=T.tax_id where T.startdate<=now()";
+ $rs=getAll("erp",$sql,"getBugru");
+ if ($rs) foreach ($rs as $row) {
+ $steuer=sprintf("%0.2f",$row["rate"]*100);
+ $GLOBALS["buchungsgruppen"][$steuer]=$row["id"];
+ }
+}
+
+$wg=1000;
+
+function insPartgroup($kat) {
+ $sql="insert into partsgroup () value ()";
+ $GLOBALS["wg"]++;
+ //$rc=query("erp",$sql,"insPartgroup");
+ if ($rc === -99) { return false; }
+ else { return $GLOBALS["wg"]; }
+}
+getBugru();
+?>
projects / mfinanz.git / blobdiff