Lx-ERP Export der Shopartikel
getDebugInfo());
if (DB::isError($db)) {
dbFehler("",$db->getDebugInfo());
die ($db->getDebugInfo());
};
/****************************************************
* dbFehler
* in: sql,err = string
* out:
* Fehlermeldungen ausgeben
*****************************************************/
function dbFehler($sql,$err) {
global $showErr;
if ($showErr)
echo "$sql : $err ";
}
function shopartikel_() {
global $db;
$sql ="SELECT P.partnumber,P.description,P.unit,P.weight,t.rate,P.sellprice,P.listprice,P.priceupdate,PG.partsgroup,P.notes,P.image,P.onhand FROM ";
$sql.="parts P left join partsgroup PG on PG.id=P.partsgroup_id ";
$sql.="left join chart c on c.id = P.income_accno_id ";
$sql.="left join tax T on T.taxkey=C.taxkey_id ";
$sql.="where shop = 't'";
$rs=$db->getAll($sql,DB_FETCHMODE_ASSOC);
return $rs;
}
function shopartikel() {
global $db,$pricegroup;
if ($pricegroup>0) {
$sql="SELECT P.partnumber,P.description,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.="parts P left join partsgroup PG on ";
$sql.="PG.id=P.partsgroup_id left join prices G on G.parts_id=P.id ";
$sql.="where P.shop='t' and ";
$sql.="(G.pricegroup_id=$pricegroup or G.pricegroup_id is null) ";
$sql.="order by P.partnumber";
} else {
$sql="SELECT P.partnumber,P.description,P.weight,P.sellprice,PG.partsgroup,";
$sql.="P.notes,P.image,P.onhand,P.buchungsgruppen_id as bugru ";
$sql.="FROM parts P left join partsgroup PG on PG.id=P.partsgroup_id ";
$sql.="left join buchungsgruppen B on P.buchungsgruppen_id = B.id ";
$sql.="WHERE P.shop='t'";
}
$rs=$db->getAll($sql,DB_FETCHMODE_ASSOC);
return $rs;
}
if ($_POST["export"]) {
$data=shopartikel();
$delim=($_POST["deli"])?$_POST["deli"]:",";
if (get_magic_quotes_gpc()) {
$crln = stripslashes($_POST["crln"]);
}
//Steuertabelle ERP
$sql ="select BG.id as bugru,T.rate,TK.startdate from buchungsgruppen BG left join chart C ";
$sql.="on BG.income_accno_id_0=C.id left join taxkeys TK on TK.chart_id=C.id left join tax T ";
$sql.="on T.id=TK.tax_id where TK.startdate <= now()";
$rs=$db->getAll($sql,DB_FETCHMODE_ASSOC);
$erptax=array();
foreach ($rs as $row) {
if ($erptax[$row["bugru"]]["startdate"]<$row["startdate"]) {
$erptax[$row["bugru"]]["startdate"]=$row["startdate"];
$erptax[$row["bugru"]]["rate"]=$row["rate"]*100;
}
}
$crln = str_replace('\\r', "\015", $crln);
$crln = str_replace('\\n', "\012", $crln);
$crln = str_replace('\\t', "\011", $crln);
$encl=$_POST["encl"];
$i=0;
$f=fopen($ERPdir,"w");
if ($_POST["partnumber"]) {$header.=$_POST["PN"].$delim; };
if ($_POST["desctiption"]) {$header.=$_POST["BEZ"].$delim; };
if ($_POST["unit"]) {$header.=$_POST["EINHEIT"].$delim; };
if ($_POST["weight"]) {$header.=$_POST["GEWICHT"].$delim; };
if ($_POST["onhand"]) {$header.=$_POST["LAGER"].$delim; };
if ($_POST["rate"]) {$header.=$_POST["MWST"].$delim; };
if ($_POST["sellprice"]) {$header.=$_POST["VK"].$delim; };
if ($_POST["listprice"]) {$header.=$_POST["EK"].$delim; };
if ($_POST["priceupdate"]) {$header.=$_POST["PDATE"].$delim; };
if ($_POST["partsgroup"]) {$header.=$_POST["PG"].$delim; };
if ($_POST["notes"]) {$header.=$_POST["BESCHR"].$delim; };
if ($_POST["image"]) {$header.=$_POST["IMAGE"].$delim; };
$header=substr($header,0,-1);
?>
$i=0;
$f=fopen($ERPdir,"w");
if ($_POST["head"]) fputs($f,$header.$crln);
foreach($data as $zeile) {
$file=""; $html="";
$PG=$zeile["partsgroup"];
$mwst=sprintf("%01.4f",$erptax[$zeile["bugru"]]["rate"]);
$LineCol = $bgcol[$i%2+1];
if ($_POST["partnumber"]) {$file.=$encl.$zeile["partnumber"].$encl.$delim; $html.="
".$zeile["partnumber"]."
";};
if ($_POST["desctiption"]) {$file.=$encl.strtr($zeile["description"],chr(13).chr(10)," ").$encl.$delim; $html.="
".$zeile["description"]."
";};
if ($_POST["unit"]) {$file.=$encl.$zeile["unit"].$encl.$delim; $html.="
".$zeile["unit"]."
";};
if ($_POST["weight"]) {$file.=$encl.$zeile["weight"].$encl.$delim; $html.="
".$zeile["weight"]."
";};
if ($_POST["onhand"]) {$file.=$encl.$zeile["onhand"].$encl.$delim; $html.="
".$zeile["onhand"]."
";};
if ($_POST["rate"]) {$file.=$encl.$mwst.$encl.$delim; $html.="
".$mwst."
";};
if ($_POST["sellprice"]) {$file.=$encl.(sprintf("%02.2f",$zeile["sellprice"])).$encl.$delim; $html.="
".(sprintf("%02.2f",$zeile["sellprice"]))."
";};
if ($_POST["listprice"]) {$file.=$encl.(sprintf("%02.2f",$zeile["listprice"])).$encl.$delim; $html.="
".(sprintf("%02.2f",$zeile["listprice"]))."
";};
if ($_POST["priceupdate"]) {$file.=$encl.$zeile["priceupdate"].$encl.$delim; $html.="
".$zeile["priceupdate"]."
";};
if ($_POST["partsgroup"]) {$file.=$encl.$PG.$encl.$delim; $html.="
".$zeile["partsgroup"]."
";};
if ($_POST["notes"]) {$file.=$encl.strtr($zeile["notes"],chr(13).chr(10)," ").$encl.$delim; $html.="
".$zeile["notes"]."
";};
if ($_POST["image"]) {$file.=$encl.$zeile["image"].$encl.$delim; $html.="
".$zeile["image"]."
";};
$i++;
fputs($f,substr($file,0,-1).$crln);
if ($_POST["show"]) {
?>
= $html ?>
}
}
?>
Anzahl der Artikel: = $i ?>
Export am : = date("d.m.Y : H:i") ?>
download Exportfile
fclose($f);
} // if ($export)
?>
Export der Shopartikel aus Lx-ERP
Mögliche Felder